Project

General

Profile

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);