Project

General

Profile

« Previous | Next » 

Revision 8601

Added by ben leinfelder almost 11 years ago

loosen the restriction on which archive flags we set to false -- if we have an obsoleted_by value then it need not be marked as archived.

View differences:

src/upgrade-db-to-2.4.0-postgres.sql
108 108 

  		




  109
  109
  
    
/* Ensure ALL previous revisions of docids 


  		




  110
  110
  
    
 * that have been obsoleted_by something else


  		




  111
  
  
    
 * but still have current revisions 


  		




  112
  111
  
    
 * do not also have archived=true flag set


  		




  113
  112
  
    
 * (Avoids encountering this issue again)


  		




  114
  113
  
    
 */


  		




  ......




  123
  122
  
    
AND x.rev = id.rev


  		




  124
  123
  
    
AND id.guid = sm.guid


  		




  125
  124
  
    
AND sm.obsoleted_by IS NOT null


  		




  126
  
  
    
AND sm.archived = 'true'


  		




  127
  
  
    
AND EXISTS (SELECT * from xml_documents xd WHERE xd.docid = x.docid);


  		




  
  125
  
    
AND sm.archived = 'true';


  		




  128
  126
  
    

  		




  129
  
  
    
/*Do the update


  		




  
  127
  
    
/*Do the update on xml_revisions


  		




  130
  128
  
    
 */


  		




  131
  129
  
    
UPDATE systemMetadata sm


  		




  132
  130
  
    
SET sm.archived = false


  		




  ......




  136
  134
  
    
AND x.rev = id.rev


  		




  137
  135
  
    
AND id.guid = sm.guid


  		




  138
  136
  
    
AND sm.obsoleted_by IS NOT null


  		




  139
  
  
    
AND sm.archived = 'true'


  		




  140
  
  
    
AND EXISTS (SELECT * from xml_documents xd WHERE xd.docid = x.docid);


  		




  
  137
  
    
AND sm.archived = 'true';


  		




  141
  138
  
    

  		




  
  139
  
    
/** 


  		




  
  140
  
    
 * Check numbers in xml_documents


  		




  
  141
  
    
 */


  		




  
  142
  
    
SELECT count(id.guid)


  		




  
  143
  
    
FROM xml_documents x,


  		




  
  144
  
    
	identifier id,


  		




  
  145
  
    
	systemMetadata sm


  		




  
  146
  
    
WHERE x.docid = id.docid


  		




  
  147
  
    
AND x.rev = id.rev


  		




  
  148
  
    
AND id.guid = sm.guid


  		




  
  149
  
    
AND sm.obsoleted_by IS NOT null


  		




  
  150
  
    
AND sm.archived = 'true';


  		




  
  151
  
    

  		




  
  152
  
    
/*Do the update on xml_documents


  		




  
  153
  
    
 */


  		




  
  154
  
    
UPDATE systemMetadata sm


  		




  
  155
  
    
SET sm.archived = false


  		




  
  156
  
    
FROM xml_documents x,


  		




  
  157
  
    
	identifier id


  		




  
  158
  
    
WHERE x.docid = id.docid


  		




  
  159
  
    
AND x.rev = id.rev


  		




  
  160
  
    
AND id.guid = sm.guid


  		




  
  161
  
    
AND sm.obsoleted_by IS NOT null


  		




  
  162
  
    
AND sm.archived = 'true';


  		




  
  163
  
    

  		




  142
  164
  
    
/* Clean up


  		




  143
  165
  
    
 */


  		




  144
  166
  
    
DROP TABLE IF EXISTS current_documents;


  		












Also available in:  Unified diff