/* * 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);