Project

General

Profile

« Previous | Next » 

Revision 8574

Added by ben leinfelder almost 11 years ago

continue to work on the criteria for selecting documents to restore.
expanded the criteria for setting archived=false to include any revision that was already obsoleted_by somethign else.

View differences:

src/upgrade-db-to-2.4.0-postgres.sql
2 2 
 * Restore archived documents
3 3 
 */
4 4 

  		




  5
  
  
    
/* Gather most recent docids from xml_revisions that


  		




  6
  
  
    
 *  1. do not have systemMetadata.archived=true 


  		




  7
  
  
    
 *  2. do not exist in xml_documents


  		




  8
  
  
    
 *  TODO: hone the criteria for selecting documents to restore


  		




  
  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?


  		




  9
  12
  
    
 * */


  		




  10
  13
  
    
CREATE TABLE restore_documents (


  		




  11
  14
  
    
	docid VARCHAR(250),


  		




  ......




  33
  36
  
    
AND id.guid = sm.guid


  		




  34
  37
  
    
AND sm.archived = true


  		




  35
  38
  
    
AND sm.obsoleted_by is not null


  		




  36
  
  
    
AND NOT EXISTS (SELECT * from xml_documents xd WHERE x.docid = xd.docid)


  		




  
  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%')


  		




  37
  46
  
    
ORDER BY id.guid;


  		




  38
  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
  
    

  		




  39
  54
  
    
/* Move xml_revisions back into xml_documents for the affected docids 


  		




  40
  55
  
    
 */


  		




  41
  56
  
    
INSERT INTO xml_documents


  		




  ......




  62
  77
  
    
FROM xml_nodes_revisions x, restore_documents rd


  		




  63
  78
  
    
WHERE x.rootnodeid = rd.rootnodeid;


  		




  64
  79
  
    

  		




  65
  
  
    
/* Ensure previous revisions of docids do not have systemMetadata.archived=true


  		




  
  80
  
    
/* Ensure ALL previous revisions of docids that


  		




  
  81
  
    
 * have been obsoleted_by something else 


  		




  
  82
  
    
 * do not also have archived=true flag set


  		




  66
  83
  
    
 * (Avoids encountering this issue again)


  		




  67
  84
  
    
 */


  		




  68
  85
  
    
UPDATE systemMetadata sm


  		




  69
  86
  
    
SET sm.archived = false


  		




  70
  
  
    
FROM restore_documents rd


  		




  71
  
  
    
WHERE sm.guid = rd.guid;


  		




  
  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;


  		




  72
  92
  
    

  		




  73
  93
  
    
/* Clean up


  		




  74
  94
  
    
 */


  		












Also available in:  Unified diff