Project

General

Profile

« Previous | Next » 

Revision 8576

Added by ben leinfelder almost 11 years ago

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