Revision 2530
Added by sgarg about 19 years ago
src/upgrade-db-to-1.6-postgres.sql | ||
---|---|---|
29 | 29 |
|
30 | 30 |
CREATE SEQUENCE xml_path_index_id_seq; |
31 | 31 |
CREATE TABLE xml_path_index ( |
32 |
nodeid INT8 default nextval('xml_path_index_id_seq'), |
|
32 |
nodeid INT8 default nextval('xml_path_index_id_seq'),
|
|
33 | 33 |
docid VARCHAR(250), -- the document id |
34 | 34 |
path VARCHAR(1000), -- precomputed path through tree |
35 | 35 |
nodedata VARCHAR(4000), -- the data for this node (e.g., |
... | ... | |
38 | 38 |
-- if it is a number |
39 | 39 |
parentnodeid INT8, -- id of the parent of the node represented |
40 | 40 |
-- by this row |
41 |
CONSTRAINT xml_path_index_pk PRIMARY KEY (nodeid), |
|
42 |
CONSTRAINT xml_path_index_docid_fk |
|
41 |
CONSTRAINT xml_path_index_pk PRIMARY KEY (nodeid),
|
|
42 |
CONSTRAINT xml_path_index_docid_fk
|
|
43 | 43 |
FOREIGN KEY (docid) REFERENCES xml_documents |
44 | 44 |
); |
45 | 45 |
|
... | ... | |
70 | 70 |
date_updated DATE, |
71 | 71 |
nodedatanumerical FLOAT8, -- the data for this node if |
72 | 72 |
-- if it is a number |
73 |
CONSTRAINT xml_nodes_revisions_pk PRIMARY KEY (nodeid), |
|
74 |
CONSTRAINT xml_nodes_revisions_root_fk |
|
73 |
CONSTRAINT xml_nodes_revisions_pk PRIMARY KEY (nodeid),
|
|
74 |
CONSTRAINT xml_nodes_revisions_root_fk
|
|
75 | 75 |
FOREIGN KEY (rootnodeid) REFERENCES xml_nodes_revisions, |
76 |
CONSTRAINT xml_nodes_revisions_parent_fk |
|
76 |
CONSTRAINT xml_nodes_revisions_parent_fk
|
|
77 | 77 |
FOREIGN KEY (parentnodeid) REFERENCES xml_nodes_revisions |
78 | 78 |
); |
79 | 79 |
|
... | ... | |
95 | 95 |
* Copy the nodes from xml_nodes to xml_nodes_revisions for old revisions |
96 | 96 |
* of the documents and deleted documents |
97 | 97 |
*/ |
98 |
INSERT INTO xml_nodes_revisions (nodeid, nodeindex, nodetype, |
|
99 |
nodename, nodeprefix, nodedata, parentnodeid, rootnodeid, |
|
100 |
docid, date_created, date_updated, nodedatanumerical) |
|
101 |
SELECT * FROM xml_nodes WHERE rootnodeid NOT IN |
|
102 |
(SELECT rootnodeid from xml_documents where rootnodeid is not NULL); |
|
103 | 98 |
|
99 |
INSERT INTO xml_nodes_revisions (nodeid, nodeindex, nodetype, nodename, nodeprefix, nodedata, parentnodeid, rootnodeid, docid, date_created, date_updated, nodedatanumerical) SELECT n.nodeid, n.nodeindex, n.nodetype, n.nodename, n.nodeprefix, n.nodedata, n.parentnodeid, n.rootnodeid, n.docid, n.date_created, n.date_updated, n.nodedatanumerical FROM xml_nodes n LEFT JOIN xml_documents ON n.rootnodeid = xml_documents.rootnodeid WHERE xml_documents.rootnodeid IS NULL; |
|
104 | 100 |
|
105 | 101 |
/** |
106 | 102 |
* Create the key constraint in xml_revisions which points to |
... | ... | |
114 | 110 |
* Delete the records from xml_index table which point to old revisions in xml_index |
115 | 111 |
* This is possible for documents for which the indexing thread failed during UPDATE |
116 | 112 |
*/ |
113 |
DELETE FROM xml_index WHERE nodeid = xml_nodes_revisions.nodeid; |
|
117 | 114 |
|
118 |
DELETE FROM xml_index WHERE nodeid IN (SELECT nodeid FROM xml_nodes WHERE |
|
119 |
rootnodeid NOT IN (SELECT rootnodeid FROM xml_documents WHERE rootnodeid IS NOT NULL)); |
|
120 | 115 |
|
121 | 116 |
/** |
122 | 117 |
* Delete the records from xml_nodes which were transfered to xml_nodes_revisions |
123 | 118 |
*/ |
124 |
DELETE FROM xml_nodes WHERE rootnodeid NOT IN |
|
125 |
(SELECT rootnodeid from xml_documents where rootnodeid is not NULL); |
|
119 |
|
|
120 |
// rename xml_nodes to xml_nodes_2 |
|
121 |
alter table xml_nodes rename to xml_nodes_2; |
|
122 |
|
|
123 |
|
|
124 |
// Create a new xml_nodes table |
|
125 |
CREATE TABLE xml_nodes ( |
|
126 |
nodeid INT8 default nextval('xml_nodes_id_seq'), |
|
127 |
-- the unique node id (pk) |
|
128 |
nodeindex INT8, -- order of nodes within parent |
|
129 |
nodetype VARCHAR(20), -- type (DOCUMENT, COMMENT, PI, |
|
130 |
-- ELEMENT, ATTRIBUTE, TEXT) |
|
131 |
nodename VARCHAR(250), -- the name of an element or attribute |
|
132 |
nodeprefix VARCHAR(50), -- the namespace prefix of the node |
|
133 |
nodedata VARCHAR(4000), -- the data for this node (e.g., |
|
134 |
-- for TEXT it is the content) |
|
135 |
parentnodeid INT8, -- index of the parent of this node |
|
136 |
rootnodeid INT8, -- index of the root node of this tree |
|
137 |
docid VARCHAR(250), -- index to the document id |
|
138 |
date_created DATE, |
|
139 |
date_updated DATE, |
|
140 |
nodedatanumerical FLOAT8, -- the data for this node if |
|
141 |
-- if it is a number |
|
142 |
CONSTRAINT xml_nodes_pk PRIMARY KEY (nodeid), |
|
143 |
CONSTRAINT xml_nodes_root_fk |
|
144 |
FOREIGN KEY (rootnodeid) REFERENCES xml_nodes, |
|
145 |
CONSTRAINT xml_nodes_parent_fk |
|
146 |
FOREIGN KEY (parentnodeid) REFERENCES xml_nodes |
|
147 |
); |
|
148 |
|
|
149 |
|
|
150 |
// copy data from xml_nodes_2 to xml_nodes |
|
151 |
INSERT INTO xml_nodes (nodeid, nodeindex, nodetype, nodename, nodeprefix, nodedata, parentnodeid, rootnodeid, docid, date_created, date_updated, nodedatanumerical) select n.nodeid, n.nodeindex, n.nodetype, n.nodename, n.nodeprefix, n.nodedata, n.parentnodeid, n.rootnodeid, n.docid, n.date_created, n.date_updated, n.nodedatanumerical from xml_nodes n LEFT JOIN xml_nodes_revisions r ON n.rootnodeid = r.rootnodeid where r.rootnodeid is NULL; |
|
152 |
|
|
153 |
// create indexes |
|
154 |
CREATE INDEX xml_nodes_idx1 ON xml_nodes (rootnodeid); |
|
155 |
CREATE INDEX xml_nodes_idx2 ON xml_nodes (parentnodeid); |
|
156 |
CREATE INDEX xml_nodes_idx3 ON xml_nodes (nodename); |
|
157 |
|
|
158 |
// add constaints which were deleted while moving xml_nodes to xml_nodes_2 |
|
159 |
ALTER TABLE xml_documents ADD CONSTRAINT xml_documents_root_fk FOREIGN KEY (rootnodeid) REFERENCES xml_nodes; |
|
160 |
ALTER TABLE xml_index ADD CONSTRAINT xml_index_nodeid_fk FOREIGN KEY (nodeid) REFERENCES xml_nodes; |
|
161 |
|
|
162 |
|
Also available in: Unified diff
Changes to the upgrade script so that 'IN' and 'NOT IN' are not used in the queries and hence the upgrade script is much faster.