Revision 8577
Added by ben leinfelder almost 11 years ago
| src/upgrade-db-to-2.4.0-postgres.sql | ||
|---|---|---|
| 69 | 69 |
|
| 70 | 70 |
--STOP HERE WHEN TESTING |
| 71 | 71 |
|
| 72 |
/* Move xml_nodes_revisions back into xml_nodes for the affected docids |
|
| 73 |
*/ |
|
| 74 |
INSERT INTO xml_nodes |
|
| 75 |
(nodeid, nodeindex, nodetype, nodename, nodeprefix, |
|
| 76 |
nodedata, parentnodeid, rootnodeid, docid, date_created, |
|
| 77 |
date_updated, nodedatanumerical, nodedatadate) |
|
| 78 |
SELECT |
|
| 79 |
nodeid, nodeindex, nodetype, nodename, nodeprefix, |
|
| 80 |
nodedata, parentnodeid, x.rootnodeid, x.docid, date_created, |
|
| 81 |
date_updated, nodedatanumerical, nodedatadate |
|
| 82 |
FROM xml_nodes_revisions x, restore_documents rd |
|
| 83 |
WHERE x.rootnodeid = rd.rootnodeid; |
|
| 84 |
|
|
| 72 | 85 |
/* Move xml_revisions back into xml_documents for the affected docids |
| 73 | 86 |
*/ |
| 74 | 87 |
INSERT INTO xml_documents |
| ... | ... | |
| 76 | 89 |
user_owner, user_updated, date_created, date_updated, |
| 77 | 90 |
server_location, rev, public_access, catalog_id) |
| 78 | 91 |
SELECT |
| 79 |
docid, rootnodeid, docname, doctype,
|
|
| 92 |
x.docid, x.rootnodeid, docname, doctype,
|
|
| 80 | 93 |
user_owner, user_updated , date_created, date_updated, |
| 81 |
server_location, rev, public_access, catalog_id |
|
| 94 |
server_location, x.rev, public_access, catalog_id
|
|
| 82 | 95 |
FROM xml_revisions x, restore_documents rd |
| 83 | 96 |
WHERE x.rootnodeid = rd.rootnodeid; |
| 84 | 97 |
|
| 85 |
/* Move xml_nodes_revisions back into xml_nodes for the affected docids |
|
| 98 |
/* Remove the records from revisions |
|
| 99 |
* Order matters here because of foreign key constraints |
|
| 86 | 100 |
*/ |
| 87 |
INSERT INTO xml_nodes |
|
| 88 |
(nodeid, nodeindex, nodetype, nodename, nodeprefix, |
|
| 89 |
nodedata, parentnodeid, rootnodeid, docid, date_created, |
|
| 90 |
date_updated, nodedatanumerical, nodedatadate) |
|
| 91 |
SELECT |
|
| 92 |
nodeid, nodeindex, nodetype, nodename, nodeprefix, |
|
| 93 |
nodedata, parentnodeid, rootnodeid, docid, date_created, |
|
| 94 |
date_updated, nodedatanumerical, nodedatadate |
|
| 95 |
FROM xml_nodes_revisions x, restore_documents rd |
|
| 101 |
DELETE FROM xml_revisions x |
|
| 102 |
USING restore_documents rd |
|
| 96 | 103 |
WHERE x.rootnodeid = rd.rootnodeid; |
| 97 | 104 |
|
| 105 |
DELETE FROM xml_nodes_revisions x |
|
| 106 |
USING restore_documents rd |
|
| 107 |
WHERE x.rootnodeid = rd.rootnodeid; |
|
| 108 |
|
|
| 98 | 109 |
/* Ensure ALL previous revisions of docids that |
| 99 | 110 |
* have been obsoleted_by something else |
| 100 | 111 |
* do not also have archived=true flag set |
Also available in: Unified diff
tested the restore insertions - adjusted for FK constraints. I was able to delete a document locally, then restore it, then update the document with a new revision as expected.