Revision 8578
Added by ben leinfelder almost 11 years ago
| src/upgrade-db-to-2.4.0-postgres.sql | ||
|---|---|---|
| 106 | 106 |
USING restore_documents rd |
| 107 | 107 |
WHERE x.rootnodeid = rd.rootnodeid; |
| 108 | 108 |
|
| 109 |
/* Ensure ALL previous revisions of docids that |
|
| 110 |
* have been obsoleted_by something else |
|
| 109 |
/* Ensure ALL previous revisions of docids |
|
| 110 |
* that have been obsoleted_by something else |
|
| 111 |
* but still have current revisions |
|
| 111 | 112 |
* do not also have archived=true flag set |
| 112 | 113 |
* (Avoids encountering this issue again) |
| 113 | 114 |
*/ |
| 115 |
|
|
| 116 |
/* Check the numbers |
|
| 117 |
*/ |
|
| 118 |
SELECT count(id.guid) |
|
| 119 |
FROM xml_revisions x, |
|
| 120 |
identifier id, |
|
| 121 |
systemMetadata sm |
|
| 122 |
WHERE x.docid = id.docid |
|
| 123 |
AND x.rev = id.rev |
|
| 124 |
AND id.guid = sm.guid |
|
| 125 |
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); |
|
| 128 |
|
|
| 129 |
/*Do the update |
|
| 130 |
*/ |
|
| 114 | 131 |
UPDATE systemMetadata sm |
| 115 | 132 |
SET sm.archived = false |
| 116 |
FROM xml_revisions x |
|
| 133 |
FROM xml_revisions x,
|
|
| 117 | 134 |
identifier id |
| 118 | 135 |
WHERE x.docid = id.docid |
| 136 |
AND x.rev = id.rev |
|
| 119 | 137 |
AND id.guid = sm.guid |
| 120 |
AND sm.obsoleted_by IS NOT null; |
|
| 138 |
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); |
|
| 121 | 141 |
|
| 122 | 142 |
/* Clean up |
| 123 | 143 |
*/ |
Also available in: Unified diff
restrict the archived=false update to revisions that still have current entries in the xml_documents table.