Project

General

Profile

1
/**
2
 *  '$RCSfile$'
3
 *  Copyright: 2004 Regents of the University of California and the
4
 *             National Center for Ecological Analysis and Synthesis
5
 *
6
 *   '$Author: sgarg $'
7
 *     '$Date: 2005-08-01 10:28:32 -0700 (Mon, 01 Aug 2005) $'
8
 * '$Revision: 2531 $'
9
 *
10
 * This program is free software; you can redistribute it and/or modify
11
 * it under the terms of the GNU General Public License as published by
12
 * the Free Software Foundation; either version 2 of the License, or
13
 * (at your option) any later version.
14
 *
15
 * This program is distributed in the hope that it will be useful,
16
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
17
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
18
 * GNU General Public License for more details.
19
 *
20
 * You should have received a copy of the GNU General Public License
21
 * along with this program; if not, write to the Free Software
22
 * Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307  USA
23
 */
24
 
25

    
26
/*
27
 * Table for indexing the paths specified the administrator in metacat.properties
28
 */
29
                                                                                                                                                             
30
CREATE SEQUENCE xml_path_index_id_seq;
31
CREATE TABLE xml_path_index (
32
        nodeid INT8  default nextval('xml_path_index_id_seq'),
33
        docid VARCHAR(250),     -- the document id
34
        path VARCHAR(1000),     -- precomputed path through tree
35
        nodedata VARCHAR(4000), -- the data for this node (e.g.,
36
                                -- for TEXT it is the content)
37
        nodedatanumerical FLOAT8, -- the data for this node if
38
                                  -- if it is a number
39
        parentnodeid INT8,      -- id of the parent of the node represented
40
                                -- by this row
41
        CONSTRAINT xml_path_index_pk PRIMARY KEY (nodeid),
42
        CONSTRAINT xml_path_index_docid_fk
43
                FOREIGN KEY (docid) REFERENCES xml_documents
44
);
45
 
46
/*
47
 * Indexes of path, nodedata and nodedatanumerical in xml_path_index
48
 */
49
CREATE INDEX xml_path_index_idx1 ON xml_path_index (path);
50
CREATE INDEX xml_path_index_idx2 ON xml_path_index (nodedata);
51
CREATE INDEX xml_path_index_idx3 ON xml_path_index (nodedatanumerical);
52

    
53

    
54

    
55

    
56
/*
57
 * Table for storing the nodes for the old revisions of the document and the deleted documents
58
 */
59
CREATE TABLE xml_nodes_revisions (
60
        nodeid INT8,            -- the unique node id (pk)
61
        nodeindex INT8,         -- order of nodes within parent
62
        nodetype VARCHAR(20),   -- type (DOCUMENT, COMMENT, PI,
63
                                -- ELEMENT, ATTRIBUTE, TEXT)
64
        nodename VARCHAR(250),  -- the name of an element or attribute
65
        nodeprefix VARCHAR(50), -- the namespace prefix of the node
66
        nodedata VARCHAR(4000), -- the data for this node (e.g.,
67
                                -- for TEXT it is the content)
68
        parentnodeid INT8,      -- index of the parent of this node
69
        rootnodeid INT8,        -- index of the root node of this tree
70
        docid VARCHAR(250),     -- index to the document id
71
        date_created DATE,
72
        date_updated DATE,
73
        nodedatanumerical FLOAT8, -- the data for this node if
74
                                  -- if it is a number
75
        CONSTRAINT xml_nodes_revisions_pk PRIMARY KEY (nodeid),
76
        CONSTRAINT xml_nodes_revisions_root_fk
77
                FOREIGN KEY (rootnodeid) REFERENCES xml_nodes_revisions,
78
        CONSTRAINT xml_nodes_revisions_parent_fk
79
                FOREIGN KEY (parentnodeid) REFERENCES xml_nodes_revisions
80
);
81
                                                                                                                                                             
82
/*
83
 * Indexes of rootnodeid, parentnodeid, and nodename in xml_nodes_revisions
84
 */
85
CREATE INDEX xml_nodes_revisions_idx1 ON xml_nodes_revisions (rootnodeid);
86
CREATE INDEX xml_nodes_revisions_idx2 ON xml_nodes_revisions (parentnodeid);
87
CREATE INDEX xml_nodes_revisions_idx3 ON xml_nodes_revisions (nodename);
88

    
89

    
90

    
91
/**
92
 * Drop the constraint from xml_revisions which points to xml_nodes
93
 */
94
ALTER TABLE xml_revisions DROP CONSTRAINT xml_revisions_root_fk;
95

    
96

    
97

    
98
/**
99
 * Copy the nodes from xml_nodes to xml_nodes_revisions for old revisions
100
 * of the documents and deleted documents
101
 */
102
INSERT INTO xml_nodes_revisions (nodeid, nodeindex, nodetype, 
103
nodename, nodeprefix, nodedata, parentnodeid, rootnodeid, docid, 
104
date_created, date_updated, nodedatanumerical) SELECT n.nodeid, 
105
n.nodeindex, n.nodetype, n.nodename, n.nodeprefix, n.nodedata, 
106
n.parentnodeid, n.rootnodeid, n.docid, n.date_created, n.date_updated, 
107
n.nodedatanumerical FROM xml_nodes n LEFT JOIN xml_documents 
108
ON n.rootnodeid = xml_documents.rootnodeid WHERE 
109
xml_documents.rootnodeid IS NULL;
110

    
111

    
112

    
113
/**
114
 * Create the key constraint in xml_revisions which points to 
115
 * xml_nodes_revisions
116
 */
117
ALTER TABLE xml_revisions ADD CONSTRAINT xml_revisions_root_fk
118
 FOREIGN KEY (rootnodeid) REFERENCES xml_nodes_revisions(nodeid);
119

    
120

    
121
 
122
/**
123
 * Delete the records from xml_index table which point to old revisions in xml_index
124
 * This is possible for documents for which the indexing thread failed during UPDATE
125
 */
126
DELETE FROM xml_index WHERE nodeid = xml_nodes_revisions.nodeid;
127

    
128

    
129

    
130
/**
131
 * Delete the records from xml_nodes which were transfered to xml_nodes_revisions 
132
 */
133
ALTER TABLE xml_nodes DROP CONSTRAINT xml_nodes_pk CASCADE;
134

    
135

    
136

    
137
/** rename xml_nodes to xml_nodes_2 */
138
ALTER TABLE xml_nodes RENAME TO xml_nodes_2;
139

    
140

    
141
/** Create a new xml_nodes table */
142
CREATE TABLE xml_nodes (
143
        nodeid INT8 default nextval('xml_nodes_id_seq'),
144
                                        -- the unique node id (pk)
145
        nodeindex INT8,         -- order of nodes within parent
146
        nodetype VARCHAR(20),   -- type (DOCUMENT, COMMENT, PI,
147
                                -- ELEMENT, ATTRIBUTE, TEXT)
148
        nodename VARCHAR(250),  -- the name of an element or attribute
149
        nodeprefix VARCHAR(50), -- the namespace prefix of the node
150
        nodedata VARCHAR(4000), -- the data for this node (e.g.,
151
                                -- for TEXT it is the content)
152
        parentnodeid INT8,      -- index of the parent of this node
153
        rootnodeid INT8,        -- index of the root node of this tree
154
        docid VARCHAR(250),     -- index to the document id
155
        date_created DATE,
156
        date_updated DATE,
157
        nodedatanumerical FLOAT8, -- the data for this node if
158
                                  -- if it is a number
159
   CONSTRAINT xml_nodes_pk PRIMARY KEY (nodeid),
160
   CONSTRAINT xml_nodes_root_fk
161
                FOREIGN KEY (rootnodeid) REFERENCES xml_nodes,
162
   CONSTRAINT xml_nodes_parent_fk
163
                FOREIGN KEY (parentnodeid) REFERENCES xml_nodes
164
);
165

    
166

    
167
/** copy data from xml_nodes_2 to xml_nodes */
168
INSERT INTO xml_nodes (nodeid, nodeindex, nodetype, nodename, 
169
nodeprefix, nodedata, parentnodeid, rootnodeid, docid, 
170
date_created, date_updated, nodedatanumerical) SELECT n.nodeid, 
171
n.nodeindex, n.nodetype, n.nodename, n.nodeprefix, n.nodedata, 
172
n.parentnodeid, n.rootnodeid, n.docid, n.date_created, 
173
n.date_updated, n.nodedatanumerical FROM xml_nodes_2 n 
174
LEFT JOIN xml_nodes_revisions r ON n.rootnodeid = r.rootnodeid 
175
WHERE r.rootnodeid is NULL;
176

    
177
/** Drop old indexes **/
178
DROP INDEX xml_nodes_idx1;
179
DROP INDEX xml_nodes_idx2;
180
DROP INDEX xml_nodes_idx3;
181

    
182
/** Create new indexes **/
183
CREATE INDEX xml_nodes_idx1 ON xml_nodes (rootnodeid);
184
CREATE INDEX xml_nodes_idx2 ON xml_nodes (parentnodeid);
185
CREATE INDEX xml_nodes_idx3 ON xml_nodes (nodename);
186

    
187
/** Add constaints which were deleted before moving xml_nodes to xml_nodes_2 */
188
ALTER TABLE xml_documents ADD CONSTRAINT xml_documents_root_fk FOREIGN KEY (rootnodeid) REFERENCES xml_nodes;
189
ALTER TABLE xml_index ADD CONSTRAINT xml_index_nodeid_fk FOREIGN KEY (nodeid) REFERENCES xml_nodes;
190

    
191
/** Drop xml_nodes_2 table */
192
DROP TABLE xml_nodes_2;
193

    
194
/** done */
(22-22/28)