Project

General

Profile

1
/**
2
 * Restore archived documents
3
 */
4

    
5
/* 
6
 * Gather most recent docids that:
7
 * have access_log event='delete' by the CN
8
 * are obsoleted by a newer version
9
 * Then we know the current version should be restored
10
 */
11

    
12
DROP TABLE IF EXISTS current_documents;
13
CREATE TABLE current_documents (
14
	guid text, 
15
	obsoleted_by text
16
);
17

    
18
/* Find the most recent version by traversing system metadata 
19
 * see: http://www.postgresql.org/docs/8.4/static/queries-with.html
20
 */
21
INSERT INTO current_documents (guid, obsoleted_by)
22
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
	SELECT newer.guid, newer.obsoleted_by
34
	FROM systemMetadata newer
35
	JOIN q
36
	ON q.obsoleted_by = newer.guid
37
)
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
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
FROM current_documents cd,
65
	xml_revisions x,
66
	identifier id
67
WHERE x.docid = id.docid
68
AND x.rev = id.rev
69
AND id.guid = cd.guid;
70

    
71
/* look at them */
72
/*
73
SELECT * 
74
FROM restore_documents;
75
*/
76

    
77
/* STOP HERE WHEN TESTING */
78

    
79
/* 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
/* 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
	x.docid, x.rootnodeid, docname, doctype,
100
	user_owner, user_updated , date_created, date_updated,
101
	server_location, x.rev, public_access, catalog_id
102
FROM xml_revisions x, restore_documents rd
103
WHERE x.rootnodeid = rd.rootnodeid;
104

    
105
/* Remove the records from revisions 
106
 * Order matters here because of foreign key constraints
107
 */
108
DELETE FROM xml_revisions x
109
USING restore_documents rd
110
WHERE x.rootnodeid = rd.rootnodeid;
111

    
112
DELETE FROM xml_nodes_revisions x
113
USING restore_documents rd
114
WHERE x.rootnodeid = rd.rootnodeid;
115

    
116
/* Ensure ALL previous revisions of docids 
117
 * that have been obsoleted_by something else
118
 * do not also have archived=true flag set
119
 * (Avoids encountering this issue again)
120
 */
121

    
122
/* Check the numbers in xml_revisions
123
 */
124
/*
125
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
AND sm.archived = 'true';
134
*/
135

    
136
/*Do the update on xml_revisions
137
 */
138
UPDATE systemMetadata sm
139
SET archived = false
140
FROM xml_revisions x,
141
	identifier id
142
WHERE x.docid = id.docid
143
AND x.rev = id.rev
144
AND id.guid = sm.guid
145
AND sm.obsoleted_by IS NOT null
146
AND sm.archived = 'true';
147

    
148
/** 
149
 * Check numbers in xml_documents
150
 */
151
/*
152
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
*/
162

    
163
/*Do the update on xml_documents
164
 */
165
UPDATE systemMetadata sm
166
SET archived = false
167
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
/* Clean up
176
 */
177
DROP TABLE IF EXISTS current_documents;
178
DROP TABLE IF EXISTS restore_documents;
179

    
180
/* 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
        AND system_id LIKE '%/dwc/%';
191
DELETE FROM xml_catalog 
192
      WHERE entry_type LIKE 'Schema'
193
        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
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

    
211
/*
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);
(85-85/89)