1 |
8565
|
leinfelder
|
/**
|
2 |
|
|
* Restore archived documents
|
3 |
|
|
*/
|
4 |
|
|
|
5 |
8574
|
leinfelder
|
/*
|
6 |
8576
|
leinfelder
|
* Gather most recent docids that:
|
7 |
8606
|
leinfelder
|
* have access_log event='delete' by the CN
|
8 |
|
|
* are obsoleted by a newer version
|
9 |
8576
|
leinfelder
|
* Then we know the current version should be restored
|
10 |
|
|
*/
|
11 |
|
|
|
12 |
8642
|
leinfelder
|
DROP TABLE IF EXISTS current_documents;
|
13 |
|
|
CREATE TABLE current_documents (
|
14 |
|
|
guid text,
|
15 |
|
|
obsoleted_by text
|
16 |
|
|
);
|
17 |
|
|
|
18 |
8576
|
leinfelder
|
/* Find the most recent version by traversing system metadata
|
19 |
|
|
* see: http://www.postgresql.org/docs/8.4/static/queries-with.html
|
20 |
|
|
*/
|
21 |
8642
|
leinfelder
|
INSERT INTO current_documents (guid, obsoleted_by)
|
22 |
8576
|
leinfelder
|
WITH RECURSIVE q AS
|
23 |
|
|
(
|
24 |
|
|
SELECT id.guid, sm.obsoleted_by
|
25 |
|
|
FROM access_log al, identifier id, systemmetadata sm
|
26 |
|
|
WHERE al.event = 'delete'
|
27 |
|
|
AND al.date_logged >= '20140101'
|
28 |
|
|
AND al.principal LIKE '%urn:node:CN%'
|
29 |
|
|
AND al.docid = id.docid || '.' || id.rev
|
30 |
|
|
AND id.guid = sm.guid
|
31 |
|
|
AND sm.obsoleted_by IS NOT null
|
32 |
|
|
UNION ALL
|
33 |
8581
|
leinfelder
|
SELECT newer.guid, newer.obsoleted_by
|
34 |
|
|
FROM systemMetadata newer
|
35 |
|
|
JOIN q
|
36 |
|
|
ON q.obsoleted_by = newer.guid
|
37 |
8576
|
leinfelder
|
)
|
38 |
|
|
SELECT guid, obsoleted_by
|
39 |
|
|
FROM q
|
40 |
|
|
WHERE obsoleted_by is null
|
41 |
|
|
ORDER BY guid;
|
42 |
|
|
|
43 |
|
|
/**
|
44 |
|
|
* Gather the details of the documents to restore
|
45 |
|
|
*/
|
46 |
|
|
DROP TABLE IF EXISTS restore_documents;
|
47 |
8565
|
leinfelder
|
CREATE TABLE restore_documents (
|
48 |
|
|
docid VARCHAR(250),
|
49 |
|
|
rev INT8,
|
50 |
|
|
rootnodeid INT8,
|
51 |
|
|
guid text
|
52 |
|
|
);
|
53 |
|
|
INSERT INTO restore_documents (
|
54 |
|
|
docid,
|
55 |
|
|
rev,
|
56 |
|
|
rootnodeid,
|
57 |
|
|
guid
|
58 |
|
|
)
|
59 |
|
|
SELECT
|
60 |
|
|
x.docid,
|
61 |
|
|
x.rev,
|
62 |
|
|
x.rootnodeid,
|
63 |
|
|
id.guid
|
64 |
8576
|
leinfelder
|
FROM current_documents cd,
|
65 |
8565
|
leinfelder
|
xml_revisions x,
|
66 |
8576
|
leinfelder
|
identifier id
|
67 |
8565
|
leinfelder
|
WHERE x.docid = id.docid
|
68 |
|
|
AND x.rev = id.rev
|
69 |
8576
|
leinfelder
|
AND id.guid = cd.guid;
|
70 |
8565
|
leinfelder
|
|
71 |
8606
|
leinfelder
|
/* look at them */
|
72 |
8604
|
leinfelder
|
/*
|
73 |
8576
|
leinfelder
|
SELECT *
|
74 |
|
|
FROM restore_documents;
|
75 |
8604
|
leinfelder
|
*/
|
76 |
8574
|
leinfelder
|
|
77 |
8606
|
leinfelder
|
/* STOP HERE WHEN TESTING */
|
78 |
8576
|
leinfelder
|
|
79 |
8577
|
leinfelder
|
/* Move xml_nodes_revisions back into xml_nodes for the affected docids
|
80 |
|
|
*/
|
81 |
|
|
INSERT INTO xml_nodes
|
82 |
|
|
(nodeid, nodeindex, nodetype, nodename, nodeprefix,
|
83 |
|
|
nodedata, parentnodeid, rootnodeid, docid, date_created,
|
84 |
|
|
date_updated, nodedatanumerical, nodedatadate)
|
85 |
|
|
SELECT
|
86 |
|
|
nodeid, nodeindex, nodetype, nodename, nodeprefix,
|
87 |
|
|
nodedata, parentnodeid, x.rootnodeid, x.docid, date_created,
|
88 |
|
|
date_updated, nodedatanumerical, nodedatadate
|
89 |
|
|
FROM xml_nodes_revisions x, restore_documents rd
|
90 |
|
|
WHERE x.rootnodeid = rd.rootnodeid;
|
91 |
|
|
|
92 |
8565
|
leinfelder
|
/* Move xml_revisions back into xml_documents for the affected docids
|
93 |
|
|
*/
|
94 |
|
|
INSERT INTO xml_documents
|
95 |
|
|
(docid, rootnodeid, docname, doctype,
|
96 |
|
|
user_owner, user_updated, date_created, date_updated,
|
97 |
|
|
server_location, rev, public_access, catalog_id)
|
98 |
|
|
SELECT
|
99 |
8577
|
leinfelder
|
x.docid, x.rootnodeid, docname, doctype,
|
100 |
8565
|
leinfelder
|
user_owner, user_updated , date_created, date_updated,
|
101 |
8577
|
leinfelder
|
server_location, x.rev, public_access, catalog_id
|
102 |
8565
|
leinfelder
|
FROM xml_revisions x, restore_documents rd
|
103 |
|
|
WHERE x.rootnodeid = rd.rootnodeid;
|
104 |
|
|
|
105 |
8577
|
leinfelder
|
/* Remove the records from revisions
|
106 |
|
|
* Order matters here because of foreign key constraints
|
107 |
8565
|
leinfelder
|
*/
|
108 |
8577
|
leinfelder
|
DELETE FROM xml_revisions x
|
109 |
|
|
USING restore_documents rd
|
110 |
8565
|
leinfelder
|
WHERE x.rootnodeid = rd.rootnodeid;
|
111 |
|
|
|
112 |
8577
|
leinfelder
|
DELETE FROM xml_nodes_revisions x
|
113 |
|
|
USING restore_documents rd
|
114 |
|
|
WHERE x.rootnodeid = rd.rootnodeid;
|
115 |
|
|
|
116 |
8578
|
leinfelder
|
/* Ensure ALL previous revisions of docids
|
117 |
|
|
* that have been obsoleted_by something else
|
118 |
8574
|
leinfelder
|
* do not also have archived=true flag set
|
119 |
8565
|
leinfelder
|
* (Avoids encountering this issue again)
|
120 |
|
|
*/
|
121 |
8578
|
leinfelder
|
|
122 |
8604
|
leinfelder
|
/* Check the numbers in xml_revisions
|
123 |
8578
|
leinfelder
|
*/
|
124 |
8604
|
leinfelder
|
/*
|
125 |
8578
|
leinfelder
|
SELECT count(id.guid)
|
126 |
|
|
FROM xml_revisions x,
|
127 |
|
|
identifier id,
|
128 |
|
|
systemMetadata sm
|
129 |
|
|
WHERE x.docid = id.docid
|
130 |
|
|
AND x.rev = id.rev
|
131 |
|
|
AND id.guid = sm.guid
|
132 |
|
|
AND sm.obsoleted_by IS NOT null
|
133 |
8601
|
leinfelder
|
AND sm.archived = 'true';
|
134 |
8604
|
leinfelder
|
*/
|
135 |
8578
|
leinfelder
|
|
136 |
8601
|
leinfelder
|
/*Do the update on xml_revisions
|
137 |
8578
|
leinfelder
|
*/
|
138 |
8565
|
leinfelder
|
UPDATE systemMetadata sm
|
139 |
8606
|
leinfelder
|
SET archived = false
|
140 |
8578
|
leinfelder
|
FROM xml_revisions x,
|
141 |
8574
|
leinfelder
|
identifier id
|
142 |
|
|
WHERE x.docid = id.docid
|
143 |
8578
|
leinfelder
|
AND x.rev = id.rev
|
144 |
8574
|
leinfelder
|
AND id.guid = sm.guid
|
145 |
8578
|
leinfelder
|
AND sm.obsoleted_by IS NOT null
|
146 |
8601
|
leinfelder
|
AND sm.archived = 'true';
|
147 |
8565
|
leinfelder
|
|
148 |
8601
|
leinfelder
|
/**
|
149 |
|
|
* Check numbers in xml_documents
|
150 |
|
|
*/
|
151 |
8604
|
leinfelder
|
/*
|
152 |
8601
|
leinfelder
|
SELECT count(id.guid)
|
153 |
|
|
FROM xml_documents x,
|
154 |
|
|
identifier id,
|
155 |
|
|
systemMetadata sm
|
156 |
|
|
WHERE x.docid = id.docid
|
157 |
|
|
AND x.rev = id.rev
|
158 |
|
|
AND id.guid = sm.guid
|
159 |
|
|
AND sm.obsoleted_by IS NOT null
|
160 |
|
|
AND sm.archived = 'true';
|
161 |
8604
|
leinfelder
|
*/
|
162 |
8601
|
leinfelder
|
|
163 |
|
|
/*Do the update on xml_documents
|
164 |
|
|
*/
|
165 |
|
|
UPDATE systemMetadata sm
|
166 |
8606
|
leinfelder
|
SET archived = false
|
167 |
8601
|
leinfelder
|
FROM xml_documents x,
|
168 |
|
|
identifier id
|
169 |
|
|
WHERE x.docid = id.docid
|
170 |
|
|
AND x.rev = id.rev
|
171 |
|
|
AND id.guid = sm.guid
|
172 |
|
|
AND sm.obsoleted_by IS NOT null
|
173 |
|
|
AND sm.archived = 'true';
|
174 |
|
|
|
175 |
8565
|
leinfelder
|
/* Clean up
|
176 |
|
|
*/
|
177 |
8576
|
leinfelder
|
DROP TABLE IF EXISTS current_documents;
|
178 |
|
|
DROP TABLE IF EXISTS restore_documents;
|
179 |
8565
|
leinfelder
|
|
180 |
8609
|
cjones
|
/* Register schemas
|
181 |
|
|
*/
|
182 |
|
|
DELETE FROM xml_catalog
|
183 |
|
|
WHERE entry_type LIKE 'Schema'
|
184 |
|
|
AND system_id LIKE '%/dataone/%';
|
185 |
|
|
DELETE FROM xml_catalog
|
186 |
|
|
WHERE entry_type LIKE 'Schema'
|
187 |
|
|
AND system_id LIKE '%/dc/%';
|
188 |
|
|
DELETE FROM xml_catalog
|
189 |
|
|
WHERE entry_type LIKE 'Schema'
|
190 |
8617
|
cjones
|
AND system_id LIKE '%/dwc/%';
|
191 |
|
|
DELETE FROM xml_catalog
|
192 |
|
|
WHERE entry_type LIKE 'Schema'
|
193 |
8609
|
cjones
|
AND system_id LIKE '%/dryad/%';
|
194 |
|
|
INSERT INTO xml_catalog (entry_type, public_id, system_id)
|
195 |
|
|
VALUES ('Schema', 'http://ns.dataone.org/service/types/v1', '/schema/dataone/dataoneTypes.xsd');
|
196 |
|
|
INSERT INTO xml_catalog (entry_type, public_id, system_id)
|
197 |
|
|
VALUES ('Schema', 'http://ns.dataone.org/service/types/v1.1', '/schema/dataone/dataoneTypes_v1.1.xsd');
|
198 |
|
|
INSERT INTO xml_catalog (entry_type, public_id, system_id)
|
199 |
|
|
VALUES ('Schema', 'http://purl.org/dryad/schema/terms/v3.1', '/schema/dryad/dryad.xsd');
|
200 |
|
|
INSERT INTO xml_catalog (entry_type, public_id, system_id)
|
201 |
|
|
VALUES ('Schema', 'http://purl.org/dryad/schema/dryad-bibo/v3.1', '/schema/dryad/dryad-bibo.xsd');
|
202 |
|
|
INSERT INTO xml_catalog (entry_type, public_id, system_id)
|
203 |
|
|
VALUES ('Schema', 'http://purl.org/dc/terms/', '/schema/dc/dcterms.xsd');
|
204 |
|
|
INSERT INTO xml_catalog (entry_type, public_id, system_id)
|
205 |
|
|
VALUES ('Schema', 'http://purl.org/dc/elements/1.1/', '/schema/dc/dc.xsd');
|
206 |
|
|
INSERT INTO xml_catalog (entry_type, public_id, system_id)
|
207 |
|
|
VALUES ('Schema', 'http://purl.org/dc/dcmitype/', '/schema/dc/dcmitype.xsd');
|
208 |
8617
|
cjones
|
INSERT INTO xml_catalog (entry_type, public_id, system_id)
|
209 |
|
|
VALUES ('Schema', 'http://rs.tdwg.org/dwc/terms/', '/schema/dwc/tdwg_dwcterms.xsd');
|
210 |
8609
|
cjones
|
|
211 |
8555
|
leinfelder
|
/*
|
212 |
|
|
* update the database version
|
213 |
|
|
*/
|
214 |
|
|
UPDATE db_version SET status=0;
|
215 |
|
|
|
216 |
|
|
INSERT INTO db_version (version, status, date_created)
|
217 |
|
|
VALUES ('2.4.0', 1, CURRENT_DATE);
|