Metacat

/**

 * Restore archived documents

 */

/* 

 * Gather most recent docids that:

 * have access_log event='delete' by the CN

 * are obsoleted by a newer version

 * Then we know the current version should be restored

 */

/* Find the most recent version by traversing system metadata 

 * see: http://www.postgresql.org/docs/8.4/static/queries-with.html

 */

DROP TABLE IF EXISTS current_documents;

WITH RECURSIVE q AS

(

	SELECT  id.guid, sm.obsoleted_by

	FROM access_log al, identifier id, systemmetadata sm

	WHERE al.event = 'delete'

	AND al.date_logged >= '20140101'

	AND al.principal LIKE '%urn:node:CN%'

	AND al.docid = id.docid || '.' || id.rev 

	AND id.guid = sm.guid

	AND sm.obsoleted_by IS NOT null

UNION ALL

	SELECT newer.guid, newer.obsoleted_by

	FROM systemMetadata newer

	JOIN q

	ON q.obsoleted_by = newer.guid

)

SELECT guid, obsoleted_by

INTO current_documents

FROM q

WHERE obsoleted_by is null

ORDER BY guid;

/**

 * Gather the details of the documents to restore

 */

DROP TABLE IF EXISTS restore_documents;

CREATE TABLE restore_documents (

	docid VARCHAR(250),

	rev INT8,

	rootnodeid INT8,

	guid text

);

INSERT INTO restore_documents (

	docid, 

	rev, 

	rootnodeid, 

	guid

) 

SELECT 

	x.docid,

	x.rev,

	x.rootnodeid,

	id.guid

FROM current_documents cd,

	xml_revisions x,

	identifier id

WHERE x.docid = id.docid

AND x.rev = id.rev

AND id.guid = cd.guid;

/* look at them */

/*

SELECT * 

FROM restore_documents;

*/

/* STOP HERE WHEN TESTING */

/* Move xml_nodes_revisions back into xml_nodes for the affected docids 

 */

INSERT INTO xml_nodes

	(nodeid, nodeindex, nodetype, nodename, nodeprefix,

	nodedata, parentnodeid, rootnodeid, docid, date_created,

	date_updated, nodedatanumerical, nodedatadate)

SELECT 

	nodeid, nodeindex, nodetype, nodename, nodeprefix,  

	nodedata, parentnodeid, x.rootnodeid, x.docid, date_created,

	date_updated, nodedatanumerical, nodedatadate

FROM xml_nodes_revisions x, restore_documents rd

WHERE x.rootnodeid = rd.rootnodeid;

/* Move xml_revisions back into xml_documents for the affected docids 

 */

INSERT INTO xml_documents

	(docid, rootnodeid, docname, doctype,

	user_owner, user_updated, date_created, date_updated,

	server_location, rev, public_access, catalog_id) 

SELECT 

	x.docid, x.rootnodeid, docname, doctype,

	user_owner, user_updated , date_created, date_updated,

	server_location, x.rev, public_access, catalog_id

FROM xml_revisions x, restore_documents rd

WHERE x.rootnodeid = rd.rootnodeid;

/* Remove the records from revisions 

 * Order matters here because of foreign key constraints

 */

DELETE FROM xml_revisions x

USING restore_documents rd

WHERE x.rootnodeid = rd.rootnodeid;

DELETE FROM xml_nodes_revisions x

USING restore_documents rd

WHERE x.rootnodeid = rd.rootnodeid;

/* Ensure ALL previous revisions of docids 

 * that have been obsoleted_by something else

 * do not also have archived=true flag set

 * (Avoids encountering this issue again)

 */

/* Check the numbers in xml_revisions

 */

/*

SELECT count(id.guid)

FROM xml_revisions x,

	identifier id,

	systemMetadata sm

WHERE x.docid = id.docid

AND x.rev = id.rev

AND id.guid = sm.guid

AND sm.obsoleted_by IS NOT null

AND sm.archived = 'true';

*/

/*Do the update on xml_revisions

 */

UPDATE systemMetadata sm

SET archived = false

FROM xml_revisions x,

	identifier id

WHERE x.docid = id.docid

AND x.rev = id.rev

AND id.guid = sm.guid

AND sm.obsoleted_by IS NOT null

AND sm.archived = 'true';

/** 

 * Check numbers in xml_documents

 */

/*

SELECT count(id.guid)

FROM xml_documents x,

	identifier id,

	systemMetadata sm

WHERE x.docid = id.docid

AND x.rev = id.rev

AND id.guid = sm.guid

AND sm.obsoleted_by IS NOT null

AND sm.archived = 'true';

*/

/*Do the update on xml_documents

 */

UPDATE systemMetadata sm

SET archived = false

FROM xml_documents x,

	identifier id

WHERE x.docid = id.docid

AND x.rev = id.rev

AND id.guid = sm.guid

AND sm.obsoleted_by IS NOT null

AND sm.archived = 'true';

/* Clean up

 */

DROP TABLE IF EXISTS current_documents;

DROP TABLE IF EXISTS restore_documents;

/* Register schemas

*/

DELETE FROM xml_catalog 

      WHERE entry_type LIKE 'Schema'

        AND system_id LIKE '%/dataone/%';

DELETE FROM xml_catalog 

      WHERE entry_type LIKE 'Schema'

        AND system_id LIKE '%/dc/%';

DELETE FROM xml_catalog 

      WHERE entry_type LIKE 'Schema'

        AND system_id LIKE '%/dwc/%';

DELETE FROM xml_catalog 

      WHERE entry_type LIKE 'Schema'

        AND system_id LIKE '%/dryad/%';                

INSERT INTO xml_catalog (entry_type, public_id, system_id)

  VALUES ('Schema', 'http://ns.dataone.org/service/types/v1', '/schema/dataone/dataoneTypes.xsd');

INSERT INTO xml_catalog (entry_type, public_id, system_id)

  VALUES ('Schema', 'http://ns.dataone.org/service/types/v1.1', '/schema/dataone/dataoneTypes_v1.1.xsd');

INSERT INTO xml_catalog (entry_type, public_id, system_id)

  VALUES ('Schema', 'http://purl.org/dryad/schema/terms/v3.1', '/schema/dryad/dryad.xsd');

INSERT INTO xml_catalog (entry_type, public_id, system_id)

  VALUES ('Schema', 'http://purl.org/dryad/schema/dryad-bibo/v3.1', '/schema/dryad/dryad-bibo.xsd');

INSERT INTO xml_catalog (entry_type, public_id, system_id)

  VALUES ('Schema', 'http://purl.org/dc/terms/', '/schema/dc/dcterms.xsd');

INSERT INTO xml_catalog (entry_type, public_id, system_id)

  VALUES ('Schema', 'http://purl.org/dc/elements/1.1/', '/schema/dc/dc.xsd');

INSERT INTO xml_catalog (entry_type, public_id, system_id)

  VALUES ('Schema', 'http://purl.org/dc/dcmitype/', '/schema/dc/dcmitype.xsd');

INSERT INTO xml_catalog (entry_type, public_id, system_id)

  VALUES ('Schema', 'http://rs.tdwg.org/dwc/terms/', '/schema/dwc/tdwg_dwcterms.xsd');

/*

 * update the database version

 */

UPDATE db_version SET status=0;

INSERT INTO db_version (version, status, date_created) 

  VALUES ('2.4.0', 1, CURRENT_DATE);