Project

General

Profile

« Previous | Next » 

Revision 8576

use 'with' query to find the most recent revision of an object that was archived. still want more feedback on the criteria.

View differences:

src/upgrade-db-to-2.4.0-postgres.sql
3 3
 */
4 4

  
5 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
 * */
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;
13 42
CREATE TABLE restore_documents (
14 43
	docid VARCHAR(250),
15 44
	rev INT8,
......
27 56
	x.rev,
28 57
	x.rootnodeid,
29 58
	id.guid
30
FROM 
59
FROM current_documents cd,
31 60
	xml_revisions x,
32
	identifier id,
33
	systemMetadata sm
61
	identifier id
34 62
WHERE x.docid = id.docid
35 63
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;
64
AND id.guid = cd.guid;
47 65

  
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%';
66
-- look at them
67
SELECT * 
68
FROM restore_documents;
53 69

  
70
--STOP HERE WHEN TESTING
71

  
54 72
/* Move xml_revisions back into xml_documents for the affected docids 
55 73
 */
56 74
INSERT INTO xml_documents
......
84 102
 */
85 103
UPDATE systemMetadata sm
86 104
SET sm.archived = false
87
FROM xml_documents x
105
FROM xml_revisions x
88 106
	identifier id
89 107
WHERE x.docid = id.docid
90 108
AND id.guid = sm.guid
......
92 110

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

  
97 116
/*
98 117
 * update the database version

Also available in: Unified diff