Project

General

Profile

metacat / src / upgrade-db-to-2.4.0-postgres.sql @ 8574

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

    
5
/* 
6
 * Gather most recent docids from xml_revisions that
7
 * TODO: hone the criteria for selecting documents to restore
8
 *  1. have systemMetadata.archived=true
9
 *         2. have non-null obsoleted_by (they were updated by a newer version)
10
 *  3. do not exist in xml_documents (they were incorrectly archived)
11
 *  4. have access_log event='delete' by the CN?
12
 * */
13
CREATE TABLE restore_documents (
14
        docid VARCHAR(250),
15
        rev INT8,
16
        rootnodeid INT8,
17
        guid text
18
);
19
INSERT INTO restore_documents (
20
        docid, 
21
        rev, 
22
        rootnodeid, 
23
        guid
24
) 
25
SELECT 
26
        x.docid,
27
        x.rev,
28
        x.rootnodeid,
29
        id.guid
30
FROM 
31
        xml_revisions x,
32
        identifier id,
33
        systemMetadata sm
34
WHERE x.docid = id.docid
35
AND x.rev = id.rev
36
AND id.guid = sm.guid
37
AND sm.archived = true
38
AND sm.obsoleted_by is not null
39
AND NOT EXISTS (SELECT * FROM xml_documents xd WHERE x.docid = xd.docid)
40
AND x.docid || '.' || x.rev IN 
41
(SELECT docid
42
FROM access_log al
43
WHERE al.event = 'delete'
44
AND al.date_logged >= '20140101'
45
AND al.principal LIKE '%CNORC%')
46
ORDER BY id.guid;
47

    
48
SELECT docid
49
FROM access_log al
50
WHERE al.event = 'delete'
51
AND al.date_logged >= '20140101'
52
AND al.principal LIKE '%CNORC%';
53

    
54
/* Move xml_revisions back into xml_documents for the affected docids 
55
 */
56
INSERT INTO xml_documents
57
        (docid, rootnodeid, docname, doctype,
58
        user_owner, user_updated, date_created, date_updated,
59
        server_location, rev, public_access, catalog_id) 
60
SELECT 
61
        docid, rootnodeid, docname, doctype,
62
        user_owner, user_updated , date_created, date_updated,
63
        server_location, rev, public_access, catalog_id
64
FROM xml_revisions x, restore_documents rd
65
WHERE x.rootnodeid = rd.rootnodeid;
66

    
67
/* Move xml_nodes_revisions back into xml_nodes for the affected docids 
68
 */
69
INSERT INTO xml_nodes
70
        (nodeid, nodeindex, nodetype, nodename, nodeprefix,
71
        nodedata, parentnodeid, rootnodeid, docid, date_created,
72
        date_updated, nodedatanumerical, nodedatadate)
73
SELECT 
74
        nodeid, nodeindex, nodetype, nodename, nodeprefix,  
75
        nodedata, parentnodeid, rootnodeid, docid, date_created,
76
        date_updated, nodedatanumerical, nodedatadate
77
FROM xml_nodes_revisions x, restore_documents rd
78
WHERE x.rootnodeid = rd.rootnodeid;
79

    
80
/* Ensure ALL previous revisions of docids that
81
 * have been obsoleted_by something else 
82
 * do not also have archived=true flag set
83
 * (Avoids encountering this issue again)
84
 */
85
UPDATE systemMetadata sm
86
SET sm.archived = false
87
FROM xml_documents x
88
        identifier id
89
WHERE x.docid = id.docid
90
AND id.guid = sm.guid
91
AND sm.obsoleted_by IS NOT null;
92

    
93
/* Clean up
94
 */
95
DROP TABLE restore_documents;
96

    
97
/*
98
 * update the database version
99
 */
100
UPDATE db_version SET status=0;
101

    
102
INSERT INTO db_version (version, status, date_created) 
103
  VALUES ('2.4.0', 1, CURRENT_DATE);