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