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
/* Find the most recent version by traversing system metadata 
13
 * see: http://www.postgresql.org/docs/8.4/static/queries-with.html
14
 */
15
DROP TABLE IF EXISTS current_documents;
16
WITH RECURSIVE q AS
17
(
18
	SELECT  id.guid, sm.obsoleted_by
19
	FROM access_log al, identifier id, systemmetadata sm
20
	WHERE al.event = 'delete'
21
	AND al.date_logged >= '20140101'
22
	AND al.principal LIKE '%urn:node:CN%'
23
	AND al.docid = id.docid || '.' || id.rev 
24
	AND id.guid = sm.guid
25
	AND sm.obsoleted_by IS NOT null
26
UNION ALL
27
	SELECT newer.guid, newer.obsoleted_by
28
	FROM systemMetadata newer
29
	JOIN q
30
	ON q.obsoleted_by = newer.guid
31
)
32
SELECT guid, obsoleted_by
33
INTO current_documents
34
FROM q
35
WHERE obsoleted_by is null
36
ORDER BY guid;
37

    
38
/**
39
 * Gather the details of the documents to restore
40
 */
41
DROP TABLE IF EXISTS restore_documents;
42
CREATE TABLE restore_documents (
43
	docid VARCHAR(250),
44
	rev INT8,
45
	rootnodeid INT8,
46
	guid text
47
);
48
INSERT INTO restore_documents (
49
	docid, 
50
	rev, 
51
	rootnodeid, 
52
	guid
53
) 
54
SELECT 
55
	x.docid,
56
	x.rev,
57
	x.rootnodeid,
58
	id.guid
59
FROM current_documents cd,
60
	xml_revisions x,
61
	identifier id
62
WHERE x.docid = id.docid
63
AND x.rev = id.rev
64
AND id.guid = cd.guid;
65

    
66
/* look at them */
67
/*
68
SELECT * 
69
FROM restore_documents;
70
*/
71

    
72
/* STOP HERE WHEN TESTING */
73

    
74
/* Move xml_nodes_revisions back into xml_nodes for the affected docids 
75
 */
76
INSERT INTO xml_nodes
77
	(nodeid, nodeindex, nodetype, nodename, nodeprefix,
78
	nodedata, parentnodeid, rootnodeid, docid, date_created,
79
	date_updated, nodedatanumerical, nodedatadate)
80
SELECT 
81
	nodeid, nodeindex, nodetype, nodename, nodeprefix,  
82
	nodedata, parentnodeid, x.rootnodeid, x.docid, date_created,
83
	date_updated, nodedatanumerical, nodedatadate
84
FROM xml_nodes_revisions x, restore_documents rd
85
WHERE x.rootnodeid = rd.rootnodeid;
86

    
87
/* Move xml_revisions back into xml_documents for the affected docids 
88
 */
89
INSERT INTO xml_documents
90
	(docid, rootnodeid, docname, doctype,
91
	user_owner, user_updated, date_created, date_updated,
92
	server_location, rev, public_access, catalog_id) 
93
SELECT 
94
	x.docid, x.rootnodeid, docname, doctype,
95
	user_owner, user_updated , date_created, date_updated,
96
	server_location, x.rev, public_access, catalog_id
97
FROM xml_revisions x, restore_documents rd
98
WHERE x.rootnodeid = rd.rootnodeid;
99

    
100
/* Remove the records from revisions 
101
 * Order matters here because of foreign key constraints
102
 */
103
DELETE FROM xml_revisions x
104
USING restore_documents rd
105
WHERE x.rootnodeid = rd.rootnodeid;
106

    
107
DELETE FROM xml_nodes_revisions x
108
USING restore_documents rd
109
WHERE x.rootnodeid = rd.rootnodeid;
110

    
111
/* Ensure ALL previous revisions of docids 
112
 * that have been obsoleted_by something else
113
 * do not also have archived=true flag set
114
 * (Avoids encountering this issue again)
115
 */
116

    
117
/* Check the numbers in xml_revisions
118
 */
119
/*
120
SELECT count(id.guid)
121
FROM xml_revisions x,
122
	identifier id,
123
	systemMetadata sm
124
WHERE x.docid = id.docid
125
AND x.rev = id.rev
126
AND id.guid = sm.guid
127
AND sm.obsoleted_by IS NOT null
128
AND sm.archived = 'true';
129
*/
130

    
131
/*Do the update on xml_revisions
132
 */
133
UPDATE systemMetadata sm
134
SET archived = false
135
FROM xml_revisions x,
136
	identifier id
137
WHERE x.docid = id.docid
138
AND x.rev = id.rev
139
AND id.guid = sm.guid
140
AND sm.obsoleted_by IS NOT null
141
AND sm.archived = 'true';
142

    
143
/** 
144
 * Check numbers in xml_documents
145
 */
146
/*
147
SELECT count(id.guid)
148
FROM xml_documents x,
149
	identifier id,
150
	systemMetadata sm
151
WHERE x.docid = id.docid
152
AND x.rev = id.rev
153
AND id.guid = sm.guid
154
AND sm.obsoleted_by IS NOT null
155
AND sm.archived = 'true';
156
*/
157

    
158
/*Do the update on xml_documents
159
 */
160
UPDATE systemMetadata sm
161
SET archived = false
162
FROM xml_documents x,
163
	identifier id
164
WHERE x.docid = id.docid
165
AND x.rev = id.rev
166
AND id.guid = sm.guid
167
AND sm.obsoleted_by IS NOT null
168
AND sm.archived = 'true';
169

    
170
/* Clean up
171
 */
172
DROP TABLE IF EXISTS current_documents;
173
DROP TABLE IF EXISTS restore_documents;
174

    
175
/* Register schemas
176
*/
177
DELETE FROM xml_catalog 
178
      WHERE entry_type LIKE 'Schema'
179
        AND system_id LIKE '%/dataone/%';
180
DELETE FROM xml_catalog 
181
      WHERE entry_type LIKE 'Schema'
182
        AND system_id LIKE '%/dc/%';
183
DELETE FROM xml_catalog 
184
      WHERE entry_type LIKE 'Schema'
185
        AND system_id LIKE '%/dryad/%';                
186
INSERT INTO xml_catalog (entry_type, public_id, system_id)
187
  VALUES ('Schema', 'http://ns.dataone.org/service/types/v1', '/schema/dataone/dataoneTypes.xsd');
188
INSERT INTO xml_catalog (entry_type, public_id, system_id)
189
  VALUES ('Schema', 'http://ns.dataone.org/service/types/v1.1', '/schema/dataone/dataoneTypes_v1.1.xsd');
190
INSERT INTO xml_catalog (entry_type, public_id, system_id)
191
  VALUES ('Schema', 'http://purl.org/dryad/schema/terms/v3.1', '/schema/dryad/dryad.xsd');
192
INSERT INTO xml_catalog (entry_type, public_id, system_id)
193
  VALUES ('Schema', 'http://purl.org/dryad/schema/dryad-bibo/v3.1', '/schema/dryad/dryad-bibo.xsd');
194
INSERT INTO xml_catalog (entry_type, public_id, system_id)
195
  VALUES ('Schema', 'http://purl.org/dc/terms/', '/schema/dc/dcterms.xsd');
196
INSERT INTO xml_catalog (entry_type, public_id, system_id)
197
  VALUES ('Schema', 'http://purl.org/dc/elements/1.1/', '/schema/dc/dc.xsd');
198
INSERT INTO xml_catalog (entry_type, public_id, system_id)
199
  VALUES ('Schema', 'http://purl.org/dc/dcmitype/', '/schema/dc/dcmitype.xsd');
200

    
201
/*
202
 * update the database version
203
 */
204
UPDATE db_version SET status=0;
205

    
206
INSERT INTO db_version (version, status, date_created) 
207
  VALUES ('2.4.0', 1, CURRENT_DATE);
(85-85/89)