Metacat

/*

 * Applies FK constraints that have been dropped. Fixes content that violates them

 * NOTE: best to run these one by one.

 */

/*

 * Nodes -- table to store XML Nodes (both elements and attributes)

 */

ALTER TABLE xml_nodes ADD

CONSTRAINT xml_nodes_root_fk

FOREIGN KEY (rootnodeid) REFERENCES xml_nodes;

ALTER TABLE xml_nodes ADD

CONSTRAINT xml_nodes_parent_fk

FOREIGN KEY (parentnodeid) REFERENCES xml_nodes;

/*

 * Table for storing the nodes for the old revisions of the document and the deleted documents

 */

ALTER TABLE xml_nodes_revisions ADD

CONSTRAINT xml_nodes_revisions_root_fk

FOREIGN KEY (rootnodeid) REFERENCES xml_nodes_revisions;

ALTER TABLE xml_nodes_revisions ADD

CONSTRAINT xml_nodes_revisions_parent_fk

FOREIGN KEY (parentnodeid) REFERENCES xml_nodes_revisions;

/*

 * Documents -- table to store XML documents

 */

-- fix xml_documents_rep_fk

--these borer and seabloom documents can be owned by KNB (they point to data with server_location=1)

UPDATE xml_documents

SET server_location = '1'

WHERE server_location = '5';

-- these LTER docs can be the LTER server

UPDATE xml_documents

SET server_location = '6'

WHERE server_location = '-2';

-- now apply the constraint

ALTER TABLE xml_documents ADD

CONSTRAINT xml_documents_rep_fk

FOREIGN KEY (server_location) REFERENCES xml_replication;

--ERROR:  insert or update on table "xml_documents" violates foreign key constraint "xml_documents_rep_fk"

--DETAIL:  Key (server_location)=(5) is not present in table "xml_replication".

ALTER TABLE xml_documents ADD

CONSTRAINT xml_documents_root_fk

FOREIGN KEY (rootnodeid) REFERENCES xml_nodes;

ALTER TABLE xml_documents ADD

CONSTRAINT xml_documents_catalog_fk

FOREIGN KEY (catalog_id) REFERENCES xml_catalog;

/*

 * Revised Documents -- table to store XML documents saved after an UPDATE or DELETE

 */

-- fix xml_revisions_rep_fk

-- these LTER docs can be the LTER server

UPDATE xml_documents

SET server_location = '6'

WHERE server_location = '-2';

-- now apply the constraint

ALTER TABLE xml_revisions ADD

CONSTRAINT xml_revisions_rep_fk

FOREIGN KEY (server_location) REFERENCES xml_replication;

--ERROR:  insert or update on table "xml_revisions" violates foreign key constraint "xml_revisions_rep_fk"

--DETAIL:  Key (server_location)=(-2) is not present in table "xml_replication".

ALTER TABLE xml_revisions ADD

CONSTRAINT xml_revisions_root_fk

FOREIGN KEY (rootnodeid) REFERENCES xml_nodes_revisions;

-- fix xml_revisions_catalog_fk

-- this can be updated to the correct xml_catalog entry for "-//ecoinformatics.org//eml-software-2.0.0beta5//EN"

UPDATE xml_documents

SET catalog_id = '41'

WHERE catalog_id = '27'

-- now apply FK

ALTER TABLE xml_revisions ADD

CONSTRAINT xml_revisions_catalog_fk

FOREIGN KEY (catalog_id) REFERENCES xml_catalog;

--ERROR:  insert or update on table "xml_revisions" violates foreign key constraint "xml_revisions_catalog_fk"

--DETAIL:  Key (catalog_id)=(27) is not present in table "xml_catalog".

/*

 * Index of Nodes -- table to store precomputed paths through tree for

 * quick searching in structured searches

 */

--fix xml_index_nodeid_fk in steps:

select distinct nodeid into temp table missing_xml_index_nodeids from xml_index;

delete from missing_xml_index_nodeids where nodeid in (select nodeid from xml_nodes);

delete from xml_index where nodeid in (select nodeid from missing_xml_index_nodeids);

drop table missing_xml_index_nodeids;

-- now apply

ALTER TABLE xml_index ADD

CONSTRAINT xml_index_nodeid_fk FOREIGN KEY (nodeid) REFERENCES xml_nodes;

--ERROR:  insert or update on table "xml_index" violates foreign key constraint "xml_index_nodeid_fk"

--DETAIL:  Key (nodeid)=(471661167) is not present in table "xml_nodes".

--fix xml_index_docid_fk in steps:

select distinct docid into temp table missing_xml_index_docids from xml_index;

delete from missing_xml_index_docids where docid in (select docid from xml_documents);

delete from xml_index where docid in (select docid from missing_xml_index_docids);

drop table missing_xml_index_docids;

-- now apply

ALTER TABLE xml_index ADD

CONSTRAINT xml_index_docid_fk

FOREIGN KEY (docid) REFERENCES xml_documents;

--ERROR:  insert or update on table "xml_index" violates foreign key constraint "xml_index_docid_fk"

--DETAIL:  Key (docid)=(MV.7) is not present in table "xml_documents".

/*

 * Index of the paths in xml_index

 */

ALTER TABLE xml_relation ADD

CONSTRAINT xml_relation_docid_fk

FOREIGN KEY (docid) REFERENCES xml_documents;

/*

 * accesssubtree -- table to store access subtree info

 */

ALTER TABLE xml_accesssubtree ADD

CONSTRAINT xml_accesssubtree_docid_fk

FOREIGN KEY (docid) REFERENCES xml_documents;

/*

 * Queryresults -- table to store queryresults for a given docid

 * and returnfield_id

 */

ALTER TABLE xml_queryresult ADD

CONSTRAINT xml_queryresult_searchid_fk

FOREIGN KEY (returnfield_id) REFERENCES xml_returnfield;

/*

 * Table for indexing the paths specified the administrator in metacat.properties

 */

--fix xml_path_index_docid_fk in steps:

select distinct docid into temp table missing_xml_path_index_docids from xml_path_index;

delete from missing_xml_path_index_docids where docid in (select docid from xml_documents);

delete from xml_path_index where docid in (select docid from missing_xml_path_index_docids);

drop table missing_xml_path_index_docids;

ALTER TABLE xml_path_index ADD

CONSTRAINT xml_path_index_docid_fk

FOREIGN KEY (docid) REFERENCES xml_documents;

--ERROR:  insert or update on table "xml_path_index" violates foreign key constraint "xml_path_index_docid_fk"

--DETAIL:  Key (docid)=(MV.7) is not present in table "xml_documents".

/*

 * harvest_detail_log -- table to log detailed info about documents that

 *                       generated errors during the harvest

 */

ALTER TABLE harvest_detail_log ADD

CONSTRAINT harvest_detail_log_fk

FOREIGN KEY (harvest_log_id) REFERENCES harvest_log;

/*

 * db_version -- table to store the version history of this database

 */

ALTER TABLE db_version ADD

CONSTRAINT db_version_pk PRIMARY KEY (db_version_id);

/*

 * scheduled_job_params -- table to store scheduled jobs

 */

ALTER TABLE scheduled_job_params ADD

CONSTRAINT scheduled_job_params_fk

FOREIGN KEY (job_id) REFERENCES scheduled_job(id);