Revision 6744
Added by ben leinfelder over 12 years ago
| upgrade-db-to-2.0.0-postgres.sql | ||
|---|---|---|
| 61 | 61 |
* Replication changes to support DataONE System Metadata replication |
| 62 | 62 |
*/ |
| 63 | 63 |
ALTER TABLE xml_replication ADD COLUMN systemmetadatareplicate INT8; |
| 64 |
/* |
|
| 65 |
* Allow guid in xml_access table (for system metadata) |
|
| 66 |
*/ |
|
| 67 |
ALTER TABLE xml_access ADD COLUMN guid text; |
|
| 68 | 64 |
|
| 69 | 65 |
/** |
| 70 | 66 |
* track the user-agent for the request |
| ... | ... | |
| 76 | 72 |
*/ |
| 77 | 73 |
INSERT INTO xml_catalog (entry_type, public_id, system_id) |
| 78 | 74 |
VALUES ('Schema', '@eml2_1_1namespace@', '/schema/eml-2.1.1/eml.xsd');
|
| 75 |
|
|
| 76 |
/** |
|
| 77 |
* Generate GUIDs for docid.rev |
|
| 78 |
*/ |
|
| 79 |
INSERT INTO identifier (docid, rev, guid) |
|
| 80 |
SELECT docid, rev, docid || '.' || rev FROM xml_documents; |
|
| 81 |
INSERT INTO identifier (docid, rev, guid) |
|
| 82 |
SELECT docid, revisionid, docid || '.' || revisionid FROM xml_revisions; |
|
| 83 |
|
|
| 84 |
/** |
|
| 85 |
* Allow guid in xml_access table (for system metadata) |
|
| 86 |
*/ |
|
| 87 |
ALTER TABLE xml_access ADD COLUMN guid text; |
|
| 88 |
|
|
| 89 |
/** |
|
| 90 |
* Upgrade xml_access records to use GUID from identifier table |
|
| 91 |
*/ |
|
| 92 |
INSERT INTO xml_access ( |
|
| 93 |
guid, principal_name, permission, perm_type, perm_order, |
|
| 94 |
docid, accessfileid, begin_time, end_time, ticket_count, subtreeid, startnodeid, endnodeid |
|
| 95 |
) |
|
| 96 |
SELECT |
|
| 97 |
id.guid, xa.principal_name, xa.permission, xa.perm_type, xa.perm_order, |
|
| 98 |
xa.docid, xa.accessfileid, xa.begin_time, xa.end_time, xa.ticket_count, xa.subtreeid, xa.startnodeid, xa.endnodeid |
|
| 99 |
FROM identifier id, xml_access xa |
|
| 100 |
WHERE id.docid = xa.docid; |
|
| 101 |
|
|
| 102 |
/** |
|
| 103 |
* Include inline access rows -- they have a special guid: 'scope.docid.rev.index' |
|
| 104 |
*/ |
|
| 105 |
INSERT INTO xml_access ( |
|
| 106 |
guid, principal_name, permission, perm_type, perm_order, |
|
| 107 |
docid, accessfileid, begin_time, end_time, ticket_count, subtreeid, startnodeid, endnodeid |
|
| 108 |
) |
|
| 109 |
SELECT |
|
| 110 |
docid, principal_name, permission, perm_type, perm_order, |
|
| 111 |
docid, accessfileid, begin_time, end_time, ticket_count, subtreeid, startnodeid, endnodeid |
|
| 112 |
FROM xml_access |
|
| 113 |
WHERE docid like '%.%.%.%'; |
|
| 114 |
|
|
| 115 |
/** |
|
| 116 |
* Update the accessfileid to use guid |
|
| 117 |
* TODO: specific revision should be used |
|
| 118 |
*/ |
|
| 119 |
UPDATE xml_access |
|
| 120 |
SET accessfileid = id.guid |
|
| 121 |
FROM identifier id, xml_access xa |
|
| 122 |
WHERE xa.accessfileid = id.docid |
|
| 123 |
|
|
| 124 |
|
|
| 125 |
/** |
|
| 126 |
* Remove old access rules |
|
| 127 |
*/ |
|
| 128 |
DELETE FROM xml_access WHERE guid is null; |
|
| 129 |
|
|
| 130 |
/** |
|
| 131 |
* clean up the xml_access table |
|
| 132 |
*/ |
|
| 133 |
ALTER TABLE xml_access DROP COLUMN docid; |
|
| 134 |
|
|
| 79 | 135 |
/* |
| 80 | 136 |
* update the database version |
| 81 | 137 |
*/ |
Also available in: Unified diff
refactor Metacat access handling to be on a per-revision basis so that it more closely aligns with the DataONE approach
http://bugzilla.ecoinformatics.org/show_bug.cgi?id=5560