Revision 6947
Added by ben leinfelder over 12 years ago
| src/upgrade-db-to-2.0.0-oracle.sql | ||
|---|---|---|
| 117 | 117 |
* Update the accessfileid to use guid |
| 118 | 118 |
* NOTE: uses the last revision's guid as the new accessfileid |
| 119 | 119 |
*/ |
| 120 |
CREATE TABLE max_identifier (guid VARCHAR2(2000), docid VARCHAR2(250), rev NUMBER(8)); |
|
| 121 |
|
|
| 122 |
/** insert the max rev identifier for each document **/ |
|
| 123 |
INSERT INTO max_identifier (docid, rev, guid) |
|
| 124 |
SELECT docid, MAX(rev), docid || '.' || MAX(rev) |
|
| 125 |
FROM identifier |
|
| 126 |
GROUP BY docid |
|
| 127 |
|
|
| 128 |
/** only need to join on docid since there is a single row for the max docid **/ |
|
| 120 | 129 |
UPDATE xml_access |
| 121 |
SET accessfileid = id.guid |
|
| 122 |
FROM identifier id, xml_access xa |
|
| 123 |
WHERE xa.accessfileid = id.docid |
|
| 124 |
AND id.rev = |
|
| 125 |
(SELECT MAX(id2.rev) FROM identifier id2 WHERE id.docid = id2.docid); |
|
| 130 |
SET accessfileid = maxid.guid |
|
| 131 |
FROM max_identifier maxid, xml_access xa |
|
| 132 |
WHERE xa.accessfileid = maxid.docid |
|
| 133 |
AND xa.guid IS NOT null |
|
| 126 | 134 |
|
| 135 |
DROP TABLE max_identifier; |
|
| 136 |
|
|
| 127 | 137 |
/** |
| 128 | 138 |
* Remove old access rules |
| 129 | 139 |
*/ |
| src/upgrade-db-to-2.0.0-postgres.sql | ||
|---|---|---|
| 116 | 116 |
FROM xml_access |
| 117 | 117 |
WHERE docid like '%.%.%.%'; |
| 118 | 118 |
|
| 119 |
|
|
| 119 | 120 |
/** |
| 120 | 121 |
* Update the accessfileid to use their guid |
| 121 | 122 |
* NOTE: uses the last revision's guid as the new value for accessfileid |
| 122 |
*/ |
|
| 123 |
* uses a temporary table |
|
| 124 |
**/ |
|
| 125 |
|
|
| 126 |
CREATE TABLE max_identifier (guid text, docid VARCHAR(250), rev INT8); |
|
| 127 |
|
|
| 128 |
/** insert the max rev identifier for each document **/ |
|
| 129 |
INSERT INTO max_identifier (docid, rev, guid) |
|
| 130 |
SELECT docid, MAX(rev), docid || '.' || MAX(rev) |
|
| 131 |
FROM identifier |
|
| 132 |
GROUP BY docid |
|
| 133 |
|
|
| 134 |
/** only need to join on docid since there is a single row for the max docid **/ |
|
| 123 | 135 |
UPDATE xml_access |
| 124 |
SET accessfileid = id.guid |
|
| 125 |
FROM identifier id, xml_access xa
|
|
| 126 |
WHERE xa.accessfileid = id.docid |
|
| 136 |
SET accessfileid = maxid.guid
|
|
| 137 |
FROM max_identifier maxid, xml_access xa
|
|
| 138 |
WHERE xa.accessfileid = maxid.docid
|
|
| 127 | 139 |
AND xa.guid IS NOT null |
| 128 |
AND id.rev = |
|
| 129 |
(SELECT MAX(id2.rev) FROM identifier id2 WHERE id.docid = id2.docid); |
|
| 130 | 140 |
|
| 141 |
DROP TABLE max_identifier; |
|
| 142 |
|
|
| 131 | 143 |
/** |
| 132 | 144 |
* Remove old access rules |
| 133 | 145 |
*/ |
Also available in: Unified diff
use a temporary table to calculate the maximum revision for a given docid and use that when setting the accessfileid during upgrade. the query plan for the all-in-one statement must be brutal as it's been running for 4 hours at this point....