1 |
7181
|
leinfelder
|
/*
|
2 |
|
|
* Applies FK constraints that have been dropped. Fixes content that violates them
|
3 |
|
|
* NOTE: best to run these one by one.
|
4 |
|
|
*/
|
5 |
|
|
|
6 |
|
|
/*
|
7 |
|
|
* Nodes -- table to store XML Nodes (both elements and attributes)
|
8 |
|
|
*/
|
9 |
|
|
|
10 |
|
|
ALTER TABLE xml_nodes ADD
|
11 |
|
|
CONSTRAINT xml_nodes_root_fk
|
12 |
|
|
FOREIGN KEY (rootnodeid) REFERENCES xml_nodes;
|
13 |
|
|
|
14 |
|
|
ALTER TABLE xml_nodes ADD
|
15 |
|
|
CONSTRAINT xml_nodes_parent_fk
|
16 |
|
|
FOREIGN KEY (parentnodeid) REFERENCES xml_nodes;
|
17 |
|
|
|
18 |
|
|
/*
|
19 |
|
|
* Table for storing the nodes for the old revisions of the document and the deleted documents
|
20 |
|
|
*/
|
21 |
|
|
|
22 |
|
|
ALTER TABLE xml_nodes_revisions ADD
|
23 |
|
|
CONSTRAINT xml_nodes_revisions_root_fk
|
24 |
|
|
FOREIGN KEY (rootnodeid) REFERENCES xml_nodes_revisions;
|
25 |
|
|
|
26 |
|
|
ALTER TABLE xml_nodes_revisions ADD
|
27 |
|
|
CONSTRAINT xml_nodes_revisions_parent_fk
|
28 |
|
|
FOREIGN KEY (parentnodeid) REFERENCES xml_nodes_revisions;
|
29 |
|
|
|
30 |
|
|
/*
|
31 |
|
|
* Documents -- table to store XML documents
|
32 |
|
|
*/
|
33 |
|
|
-- fix xml_documents_rep_fk
|
34 |
|
|
--these borer and seabloom documents can be owned by KNB (they point to data with server_location=1)
|
35 |
|
|
UPDATE xml_documents
|
36 |
|
|
SET server_location = '1'
|
37 |
|
|
WHERE server_location = '5';
|
38 |
|
|
-- these LTER docs can be the LTER server
|
39 |
|
|
UPDATE xml_documents
|
40 |
|
|
SET server_location = '6'
|
41 |
|
|
WHERE server_location = '-2';
|
42 |
|
|
|
43 |
|
|
-- now apply the constraint
|
44 |
|
|
ALTER TABLE xml_documents ADD
|
45 |
|
|
CONSTRAINT xml_documents_rep_fk
|
46 |
|
|
FOREIGN KEY (server_location) REFERENCES xml_replication;
|
47 |
|
|
--ERROR: insert or update on table "xml_documents" violates foreign key constraint "xml_documents_rep_fk"
|
48 |
|
|
--DETAIL: Key (server_location)=(5) is not present in table "xml_replication".
|
49 |
|
|
|
50 |
|
|
|
51 |
|
|
ALTER TABLE xml_documents ADD
|
52 |
|
|
CONSTRAINT xml_documents_root_fk
|
53 |
|
|
FOREIGN KEY (rootnodeid) REFERENCES xml_nodes;
|
54 |
|
|
|
55 |
|
|
ALTER TABLE xml_documents ADD
|
56 |
|
|
CONSTRAINT xml_documents_catalog_fk
|
57 |
|
|
FOREIGN KEY (catalog_id) REFERENCES xml_catalog;
|
58 |
|
|
|
59 |
|
|
/*
|
60 |
|
|
* Revised Documents -- table to store XML documents saved after an UPDATE or DELETE
|
61 |
|
|
*/
|
62 |
|
|
|
63 |
|
|
-- fix xml_revisions_rep_fk
|
64 |
|
|
-- these LTER docs can be the LTER server
|
65 |
|
|
UPDATE xml_documents
|
66 |
|
|
SET server_location = '6'
|
67 |
|
|
WHERE server_location = '-2';
|
68 |
|
|
|
69 |
|
|
-- now apply the constraint
|
70 |
|
|
ALTER TABLE xml_revisions ADD
|
71 |
|
|
CONSTRAINT xml_revisions_rep_fk
|
72 |
|
|
FOREIGN KEY (server_location) REFERENCES xml_replication;
|
73 |
|
|
--ERROR: insert or update on table "xml_revisions" violates foreign key constraint "xml_revisions_rep_fk"
|
74 |
|
|
--DETAIL: Key (server_location)=(-2) is not present in table "xml_replication".
|
75 |
|
|
|
76 |
|
|
|
77 |
|
|
ALTER TABLE xml_revisions ADD
|
78 |
|
|
CONSTRAINT xml_revisions_root_fk
|
79 |
|
|
FOREIGN KEY (rootnodeid) REFERENCES xml_nodes_revisions;
|
80 |
|
|
|
81 |
|
|
-- fix xml_revisions_catalog_fk
|
82 |
|
|
-- this can be updated to the correct xml_catalog entry for "-//ecoinformatics.org//eml-software-2.0.0beta5//EN"
|
83 |
|
|
UPDATE xml_documents
|
84 |
|
|
SET catalog_id = '41'
|
85 |
|
|
WHERE catalog_id = '27'
|
86 |
|
|
|
87 |
|
|
-- now apply FK
|
88 |
|
|
ALTER TABLE xml_revisions ADD
|
89 |
|
|
CONSTRAINT xml_revisions_catalog_fk
|
90 |
|
|
FOREIGN KEY (catalog_id) REFERENCES xml_catalog;
|
91 |
|
|
--ERROR: insert or update on table "xml_revisions" violates foreign key constraint "xml_revisions_catalog_fk"
|
92 |
|
|
--DETAIL: Key (catalog_id)=(27) is not present in table "xml_catalog".
|
93 |
|
|
|
94 |
|
|
|
95 |
|
|
/*
|
96 |
|
|
* Index of Nodes -- table to store precomputed paths through tree for
|
97 |
|
|
* quick searching in structured searches
|
98 |
|
|
*/
|
99 |
|
|
|
100 |
|
|
--fix xml_index_nodeid_fk in steps:
|
101 |
|
|
select distinct nodeid into temp table missing_xml_index_nodeids from xml_index;
|
102 |
|
|
delete from missing_xml_index_nodeids where nodeid in (select nodeid from xml_nodes);
|
103 |
|
|
delete from xml_index where nodeid in (select nodeid from missing_xml_index_nodeids);
|
104 |
|
|
drop table missing_xml_index_nodeids;
|
105 |
|
|
|
106 |
|
|
-- now apply
|
107 |
|
|
ALTER TABLE xml_index ADD
|
108 |
|
|
CONSTRAINT xml_index_nodeid_fk FOREIGN KEY (nodeid) REFERENCES xml_nodes;
|
109 |
|
|
--ERROR: insert or update on table "xml_index" violates foreign key constraint "xml_index_nodeid_fk"
|
110 |
|
|
--DETAIL: Key (nodeid)=(471661167) is not present in table "xml_nodes".
|
111 |
|
|
|
112 |
|
|
|
113 |
|
|
--fix xml_index_docid_fk in steps:
|
114 |
|
|
select distinct docid into temp table missing_xml_index_docids from xml_index;
|
115 |
|
|
delete from missing_xml_index_docids where docid in (select docid from xml_documents);
|
116 |
|
|
delete from xml_index where docid in (select docid from missing_xml_index_docids);
|
117 |
|
|
drop table missing_xml_index_docids;
|
118 |
|
|
|
119 |
|
|
-- now apply
|
120 |
|
|
ALTER TABLE xml_index ADD
|
121 |
|
|
CONSTRAINT xml_index_docid_fk
|
122 |
|
|
FOREIGN KEY (docid) REFERENCES xml_documents;
|
123 |
|
|
--ERROR: insert or update on table "xml_index" violates foreign key constraint "xml_index_docid_fk"
|
124 |
|
|
--DETAIL: Key (docid)=(MV.7) is not present in table "xml_documents".
|
125 |
|
|
|
126 |
|
|
|
127 |
|
|
|
128 |
|
|
/*
|
129 |
|
|
* Index of the paths in xml_index
|
130 |
|
|
*/
|
131 |
|
|
|
132 |
|
|
ALTER TABLE xml_relation ADD
|
133 |
|
|
CONSTRAINT xml_relation_docid_fk
|
134 |
|
|
FOREIGN KEY (docid) REFERENCES xml_documents;
|
135 |
|
|
|
136 |
|
|
/*
|
137 |
|
|
* accesssubtree -- table to store access subtree info
|
138 |
|
|
*/
|
139 |
|
|
ALTER TABLE xml_accesssubtree ADD
|
140 |
|
|
CONSTRAINT xml_accesssubtree_docid_fk
|
141 |
|
|
FOREIGN KEY (docid) REFERENCES xml_documents;
|
142 |
|
|
|
143 |
|
|
/*
|
144 |
|
|
* Queryresults -- table to store queryresults for a given docid
|
145 |
|
|
* and returnfield_id
|
146 |
|
|
*/
|
147 |
|
|
|
148 |
|
|
ALTER TABLE xml_queryresult ADD
|
149 |
|
|
CONSTRAINT xml_queryresult_searchid_fk
|
150 |
|
|
FOREIGN KEY (returnfield_id) REFERENCES xml_returnfield;
|
151 |
|
|
|
152 |
|
|
/*
|
153 |
|
|
* Table for indexing the paths specified the administrator in metacat.properties
|
154 |
|
|
*/
|
155 |
|
|
|
156 |
|
|
--fix xml_path_index_docid_fk in steps:
|
157 |
|
|
select distinct docid into temp table missing_xml_path_index_docids from xml_path_index;
|
158 |
|
|
delete from missing_xml_path_index_docids where docid in (select docid from xml_documents);
|
159 |
|
|
delete from xml_path_index where docid in (select docid from missing_xml_path_index_docids);
|
160 |
|
|
drop table missing_xml_path_index_docids;
|
161 |
|
|
|
162 |
|
|
ALTER TABLE xml_path_index ADD
|
163 |
|
|
CONSTRAINT xml_path_index_docid_fk
|
164 |
|
|
FOREIGN KEY (docid) REFERENCES xml_documents;
|
165 |
|
|
--ERROR: insert or update on table "xml_path_index" violates foreign key constraint "xml_path_index_docid_fk"
|
166 |
|
|
--DETAIL: Key (docid)=(MV.7) is not present in table "xml_documents".
|
167 |
|
|
|
168 |
|
|
|
169 |
|
|
/*
|
170 |
|
|
* harvest_detail_log -- table to log detailed info about documents that
|
171 |
|
|
* generated errors during the harvest
|
172 |
|
|
*/
|
173 |
|
|
|
174 |
|
|
ALTER TABLE harvest_detail_log ADD
|
175 |
|
|
CONSTRAINT harvest_detail_log_fk
|
176 |
|
|
FOREIGN KEY (harvest_log_id) REFERENCES harvest_log;
|
177 |
|
|
|
178 |
|
|
/*
|
179 |
|
|
* db_version -- table to store the version history of this database
|
180 |
|
|
*/
|
181 |
|
|
ALTER TABLE db_version ADD
|
182 |
|
|
CONSTRAINT db_version_pk PRIMARY KEY (db_version_id);
|
183 |
|
|
|
184 |
|
|
/*
|
185 |
|
|
* scheduled_job_params -- table to store scheduled jobs
|
186 |
|
|
*/
|
187 |
|
|
|
188 |
|
|
ALTER TABLE scheduled_job_params ADD
|
189 |
|
|
CONSTRAINT scheduled_job_params_fk
|
190 |
|
|
FOREIGN KEY (job_id) REFERENCES scheduled_job(id);
|