Metacat

/**

 * Generate GUIDs for docid.rev

 */

INSERT INTO identifier (docid, rev, guid) 

	SELECT docid, rev, docid || '.' || rev FROM xml_documents;

INSERT INTO identifier (docid, rev, guid)

	SELECT docid, revisionid, docid || '.' || revisionid FROM xml_revisions;

 *  Allow guid in xml_access table (for system metadata)

*/

ALTER TABLE xml_access ADD COLUMN guid VARCHAR2(2000);

/**

 * Upgrade xml_access records to use GUID from identifier table

 * NOTE: This duplicates existing access rules for every revision of a document

 */

INSERT INTO xml_access (

	guid, principal_name, permission, perm_type, perm_order, 

	docid, accessfileid, begin_time, end_time, ticket_count, subtreeid, startnodeid, endnodeid

)

	SELECT 

		id.guid, xa.principal_name, xa.permission, xa.perm_type, xa.perm_order, 

		xa.docid, xa.accessfileid, xa.begin_time, xa.end_time, xa.ticket_count, xa.subtreeid, xa.startnodeid, xa.endnodeid

	FROM identifier id, xml_access xa

	WHERE id.docid = xa.docid;

/**

 * Include inline data access rows -- they have a special guid: 'scope.docid.rev.index'

 */

INSERT INTO xml_access (

	guid, principal_name, permission, perm_type, perm_order, 

	docid, accessfileid, begin_time, end_time, ticket_count, subtreeid, startnodeid, endnodeid

)

	SELECT 

		docid, principal_name, permission, perm_type, perm_order, 

		docid, accessfileid, begin_time, end_time, ticket_count, subtreeid, startnodeid, endnodeid

	FROM xml_access

	WHERE docid like '%.%.%.%';

/**

 * Update the accessfileid to use guid

 * NOTE: uses the last revision's guid as the new accessfileid

 */

UPDATE xml_access

SET accessfileid = id.guid

FROM identifier id, xml_access xa

WHERE xa.accessfileid = id.docid

AND id.rev = 

	(SELECT MAX(id2.rev) FROM identifier id2 WHERE id.docid = id2.docid)

/**

 * Remove old access rules

 */

DELETE FROM xml_access WHERE guid is null;

/**

 * clean up the xml_access table

 */

ALTER TABLE xml_access DROP COLUMN docid;

/*