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
SELECT * 
68
FROM restore_documents;
69

    
70
--STOP HERE WHEN TESTING
71

    
72
/* Move xml_revisions back into xml_documents for the affected docids 
73
 */
74
INSERT INTO xml_documents
75
	(docid, rootnodeid, docname, doctype,
76
	user_owner, user_updated, date_created, date_updated,
77
	server_location, rev, public_access, catalog_id) 
78
SELECT 
79
	docid, rootnodeid, docname, doctype,
80
	user_owner, user_updated , date_created, date_updated,
81
	server_location, rev, public_access, catalog_id
82
FROM xml_revisions x, restore_documents rd
83
WHERE x.rootnodeid = rd.rootnodeid;
84

    
85
/* Move xml_nodes_revisions back into xml_nodes for the affected docids 
86
 */
87
INSERT INTO xml_nodes
88
	(nodeid, nodeindex, nodetype, nodename, nodeprefix,
89
	nodedata, parentnodeid, rootnodeid, docid, date_created,
90
	date_updated, nodedatanumerical, nodedatadate)
91
SELECT 
92
	nodeid, nodeindex, nodetype, nodename, nodeprefix,  
93
	nodedata, parentnodeid, rootnodeid, docid, date_created,
94
	date_updated, nodedatanumerical, nodedatadate
95
FROM xml_nodes_revisions x, restore_documents rd
96
WHERE x.rootnodeid = rd.rootnodeid;
97

    
98
/* Ensure ALL previous revisions of docids that
99
 * have been obsoleted_by something else 
100
 * do not also have archived=true flag set
101
 * (Avoids encountering this issue again)
102
 */
103
UPDATE systemMetadata sm
104
SET sm.archived = false
105
FROM xml_revisions x
106
	identifier id
107
WHERE x.docid = id.docid
108
AND id.guid = sm.guid
109
AND sm.obsoleted_by IS NOT null;
110

    
111
/* Clean up
112
 */
113
DROP TABLE IF EXISTS current_documents;
114
DROP TABLE IF EXISTS restore_documents;
115

    
116
/*
117
 * update the database version
118
 */
119
UPDATE db_version SET status=0;
120

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