Revision 7387
Added by ben leinfelder almost 12 years ago
| upgrade-db-to-2.0.0-oracle.sql | ||
|---|---|---|
| 37 | 37 |
FOREIGN KEY (guid) REFERENCES systemMetadata DEFERRABLE |
| 38 | 38 |
); |
| 39 | 39 |
|
| 40 |
DROP TABLE IF EXISTS identifier; |
|
| 40 |
BEGIN |
|
| 41 |
EXECUTE IMMEDIATE 'DROP TABLE identifier'; |
|
| 42 |
EXCEPTION |
|
| 43 |
WHEN OTHERS THEN |
|
| 44 |
IF SQLCODE != -942 THEN |
|
| 45 |
RAISE; |
|
| 46 |
END IF; |
|
| 47 |
END; |
|
| 41 | 48 |
|
| 49 |
/* |
|
| 50 |
* Create the new one (DataONE features) |
|
| 51 |
*/ |
|
| 42 | 52 |
CREATE TABLE identifier ( |
| 43 | 53 |
guid VARCHAR2(2000), -- the globally unique string identifier |
| 44 | 54 |
docid VARCHAR2(250), -- the local document id # |
| 45 | 55 |
rev NUMBER(8) -- the revision part of the local identifier |
| 46 | 56 |
); |
| 47 | 57 |
|
| 58 |
/* |
|
| 59 |
* add the nodedatadate column to the tables that need it |
|
| 60 |
*/ |
|
| 61 |
ALTER TABLE xml_nodes ADD (nodedatadate TIMESTAMP); |
|
| 62 |
ALTER TABLE xml_nodes_revisions ADD (nodedatadate TIMESTAMP); |
|
| 63 |
ALTER TABLE xml_path_index ADD (nodedatadate TIMESTAMP); |
|
| 64 |
CREATE INDEX xml_path_index_idx5 ON xml_path_index (nodedatadate); |
|
| 65 |
|
|
| 48 | 66 |
/** |
| 49 | 67 |
* track the user-agent for the request |
| 50 | 68 |
*/ |
| 51 |
ALTER TABLE access_log ADD COLUMN user_agent VARCHAR2(512);
|
|
| 69 |
ALTER TABLE access_log ADD (user_agent VARCHAR(512));
|
|
| 52 | 70 |
|
| 53 | 71 |
/* |
| 54 |
* add the nodedatadate column to xml_nodes |
|
| 55 |
* TODO: load the data into it (java?) |
|
| 56 |
*/ |
|
| 57 |
ALTER TABLE xml_nodes ADD COLUMN nodedatadate TIMESTAMP; |
|
| 58 |
ALTER TABLE xml_nodes_revisions ADD COLUMN nodedatadate TIMESTAMP; |
|
| 59 |
ALTER TABLE xml_path_index ADD COLUMN nodedatadate TIMESTAMP; |
|
| 60 |
CREATE INDEX xml_path_index_idx4 ON xml_path_index (nodedatadate); |
|
| 61 |
|
|
| 62 |
/* |
|
| 63 | 72 |
* Register the new schema |
| 64 | 73 |
*/ |
| 65 | 74 |
INSERT INTO xml_catalog (entry_type, public_id, system_id) |
| 66 |
VALUES ('Schema', '@eml2_1_1namespace@', '/schema/eml-2.1.1/eml.xsd');
|
|
| 75 |
VALUES ('Schema', '@eml2_1_1namespace@', '/schema/eml-2.1.1/eml.xsd');
|
|
| 67 | 76 |
|
| 68 | 77 |
/** |
| 69 | 78 |
* Generate GUIDs for docid.rev |
| ... | ... | |
| 72 | 81 |
SELECT docid, rev, docid || '.' || rev FROM xml_documents |
| 73 | 82 |
UNION |
| 74 | 83 |
SELECT docid, rev, docid || '.' || rev FROM xml_revisions; |
| 75 |
/* |
|
| 76 |
* Allow guid in xml_access table (for system metadata) |
|
| 77 |
*/ |
|
| 78 |
ALTER TABLE xml_access ADD COLUMN guid VARCHAR2(2000); |
|
| 84 |
--INSERT 0 156644 |
|
| 85 |
|
|
| 79 | 86 |
/** |
| 87 |
* Add guid in xml_access table |
|
| 88 |
*/ |
|
| 89 |
ALTER TABLE xml_access ADD (guid VARCHAR2(2000)); |
|
| 90 |
/** |
|
| 80 | 91 |
* Expand accessfileid in xml_access table to hold guids |
| 81 | 92 |
*/ |
| 82 |
ALTER TABLE xml_access ALTER COLUMN accessfileid TYPE VARCHAR2(2000);
|
|
| 93 |
ALTER TABLE xml_access MODIFY (accessfileid VARCHAR2(2000));
|
|
| 83 | 94 |
|
| 84 |
|
|
| 85 | 95 |
/** |
| 86 | 96 |
* Upgrade xml_access records to use GUID from identifier table |
| 87 | 97 |
* NOTE: This duplicates existing access rules for every revision of a document |
| ... | ... | |
| 108 | 118 |
docid, accessfileid, begin_time, end_time, ticket_count, subtreeid, startnodeid, endnodeid |
| 109 | 119 |
FROM xml_access |
| 110 | 120 |
WHERE docid like '%.%.%.%'; |
| 121 |
--INSERT 0 18 |
|
| 111 | 122 |
|
| 123 |
|
|
| 112 | 124 |
/** |
| 113 |
* Update the accessfileid to use guid |
|
| 114 |
* NOTE: uses the last revision's guid as the new accessfileid |
|
| 115 |
*/ |
|
| 125 |
* Update the accessfileid to use their guid |
|
| 126 |
* NOTE: uses the last revision's guid as the new value for accessfileid |
|
| 127 |
* uses a temporary table |
|
| 128 |
**/ |
|
| 116 | 129 |
CREATE TABLE max_identifier (guid VARCHAR2(2000), docid VARCHAR2(250), rev NUMBER(8)); |
| 117 | 130 |
|
| 118 | 131 |
/** insert the max rev identifier for each document **/ |
| ... | ... | |
| 120 | 133 |
SELECT docid, MAX(rev), docid || '.' || MAX(rev) |
| 121 | 134 |
FROM identifier |
| 122 | 135 |
GROUP BY docid; |
| 136 |
--INSERT 0 57841 |
|
| 123 | 137 |
|
| 124 | 138 |
/** create some indexes to speed up the join **/ |
| 125 | 139 |
CREATE INDEX maxid_docid_index ON max_identifier (docid); |
| 126 |
CREATE INDEX maxid_guid_index ON max_identifier (docid);
|
|
| 140 |
CREATE INDEX maxid_guid_index ON max_identifier (guid);
|
|
| 127 | 141 |
CREATE INDEX accessfileid_index on xml_access (accessfileid); |
| 128 | 142 |
CREATE INDEX xml_access_guid_index on xml_access (guid); |
| 129 | 143 |
|
| 130 |
/** only need to join on docid since there is a single row for the max docid **/ |
|
| 131 | 144 |
UPDATE xml_access xa |
| 132 | 145 |
SET accessfileid = maxid.guid |
| 133 | 146 |
FROM max_identifier maxid |
| 134 | 147 |
WHERE xa.accessfileid = maxid.docid |
| 135 | 148 |
AND xa.guid IS NOT null; |
| 149 |
--UPDATE 310427 |
|
| 136 | 150 |
|
| 137 | 151 |
DROP INDEX maxid_docid_index; |
| 138 | 152 |
DROP INDEX maxid_guid_index; |
| ... | ... | |
| 145 | 159 |
* Remove old access rules |
| 146 | 160 |
*/ |
| 147 | 161 |
DELETE FROM xml_access WHERE guid is null; |
| 162 |
--DELETE 105432 |
|
| 148 | 163 |
|
| 149 | 164 |
/** |
| 150 | 165 |
* clean up the xml_access table |
| 151 | 166 |
*/ |
| 152 |
ALTER TABLE xml_access DROP COLUMN docid;
|
|
| 167 |
ALTER TABLE xml_access DROP (docid);
|
|
| 153 | 168 |
|
| 169 |
|
|
| 154 | 170 |
/** |
| 155 | 171 |
* expand xml_path_index 'path' column to hold larger strings |
| 156 | 172 |
*/ |
Also available in: Unified diff
correct Oracle syntax
http://bugzilla.ecoinformatics.org/show_bug.cgi?id=5717