Project

General

Profile

« Previous | Next » 

Revision 7181

script for re-applying missing FK constraints on KNB production DB.
http://bugzilla.ecoinformatics.org/show_bug.cgi?id=5608

View differences:

src/apply-xmlconstraints-postgres-knb.sql
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);
0 191

  

Also available in: Unified diff