Project

General

Profile

metacat / src / upgrade-db-to-1.6-postgres.sql @ 2769

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: jones $'
7
 *     '$Date: 2005-11-18 17:10:41 -0800 (Fri, 18 Nov 2005) $'
8
 * '$Revision: 2769 $'
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
 * Changes to the tables for handling identifiers.  Old table no longer needed,
28
 * new identifier table to be used to support LSIDs.
29
 */
30
DROP TABLE accession_number;
31
DROP SEQUENCE accession_number_id_seq;
32

    
33
/*
34
 * Table used to store all document identifiers in metacat.  Each identifier
35
 * consists of 4 subparts, an authority, namespace, object, and revision as
36
 * defined in the LSID specification.
37
 */
38
CREATE SEQUENCE identifier_id_seq;
39
CREATE TABLE identifier (
40
   id INT8 default nextval('identifier_id_seq') PRIMARY KEY, -- primary id
41
   authority VARCHAR(255),  -- the authority issuing the identifier
42
   namespace VARCHAR(255),  -- the namespace qualifying the identifier
43
   object    VARCHAR(255),  -- the local part of the identifier for a particular object
44
   revision  VARCHAR(255)   -- the revision part of the identifier
45
);
46

    
47
/*
48
 * Table for indexing the paths specified the administrator in metacat.properties
49
 */
50
                                                                                                                                                             
51
CREATE SEQUENCE xml_path_index_id_seq;
52
CREATE TABLE xml_path_index (
53
        nodeid INT8  default nextval('xml_path_index_id_seq'),
54
        docid VARCHAR(250),     -- the document id
55
        path VARCHAR(1000),     -- precomputed path through tree
56
        nodedata VARCHAR(4000), -- the data for this node (e.g.,
57
                                -- for TEXT it is the content)
58
        nodedatanumerical FLOAT8, -- the data for this node if
59
                                  -- if it is a number
60
        parentnodeid INT8,      -- id of the parent of the node represented
61
                                -- by this row
62
        CONSTRAINT xml_path_index_pk PRIMARY KEY (nodeid),
63
        CONSTRAINT xml_path_index_docid_fk
64
                FOREIGN KEY (docid) REFERENCES xml_documents
65
);
66
 
67
/*
68
 * Indexes of path, nodedata and nodedatanumerical in xml_path_index
69
 */
70
CREATE INDEX xml_path_index_idx1 ON xml_path_index (path);
71
CREATE INDEX xml_path_index_idx2 ON xml_path_index (nodedata);
72
CREATE INDEX xml_path_index_idx3 ON xml_path_index (nodedatanumerical);
73

    
74

    
75

    
76

    
77
/*
78
 * Table for storing the nodes for the old revisions of the document and the deleted documents
79
 */
80
CREATE TABLE xml_nodes_revisions (
81
        nodeid INT8,            -- the unique node id (pk)
82
        nodeindex INT8,         -- order of nodes within parent
83
        nodetype VARCHAR(20),   -- type (DOCUMENT, COMMENT, PI,
84
                                -- ELEMENT, ATTRIBUTE, TEXT)
85
        nodename VARCHAR(250),  -- the name of an element or attribute
86
        nodeprefix VARCHAR(50), -- the namespace prefix of the node
87
        nodedata VARCHAR(4000), -- the data for this node (e.g.,
88
                                -- for TEXT it is the content)
89
        parentnodeid INT8,      -- index of the parent of this node
90
        rootnodeid INT8,        -- index of the root node of this tree
91
        docid VARCHAR(250),     -- index to the document id
92
        date_created DATE,
93
        date_updated DATE,
94
        nodedatanumerical FLOAT8, -- the data for this node if
95
                                  -- if it is a number
96
        CONSTRAINT xml_nodes_revisions_pk PRIMARY KEY (nodeid),
97
        CONSTRAINT xml_nodes_revisions_root_fk
98
                FOREIGN KEY (rootnodeid) REFERENCES xml_nodes_revisions,
99
        CONSTRAINT xml_nodes_revisions_parent_fk
100
                FOREIGN KEY (parentnodeid) REFERENCES xml_nodes_revisions
101
);
102
                                                                                                                                                             
103
/*
104
 * Indexes of rootnodeid, parentnodeid, and nodename in xml_nodes_revisions
105
 */
106
CREATE INDEX xml_nodes_revisions_idx1 ON xml_nodes_revisions (rootnodeid);
107
CREATE INDEX xml_nodes_revisions_idx2 ON xml_nodes_revisions (parentnodeid);
108
CREATE INDEX xml_nodes_revisions_idx3 ON xml_nodes_revisions (nodename);
109

    
110

    
111

    
112
/**
113
 * Drop the constraint from xml_revisions which points to xml_nodes
114
 */
115
ALTER TABLE xml_revisions DROP CONSTRAINT xml_revisions_root_fk;
116

    
117

    
118

    
119
/**
120
 * Copy the nodes from xml_nodes to xml_nodes_revisions for old revisions
121
 * of the documents and deleted documents
122
 */
123
INSERT INTO xml_nodes_revisions (nodeid, nodeindex, nodetype, 
124
nodename, nodeprefix, nodedata, parentnodeid, rootnodeid, docid, 
125
date_created, date_updated, nodedatanumerical) SELECT n.nodeid, 
126
n.nodeindex, n.nodetype, n.nodename, n.nodeprefix, n.nodedata, 
127
n.parentnodeid, n.rootnodeid, n.docid, n.date_created, n.date_updated, 
128
n.nodedatanumerical FROM xml_nodes n LEFT JOIN xml_documents 
129
ON n.rootnodeid = xml_documents.rootnodeid WHERE 
130
xml_documents.rootnodeid IS NULL;
131

    
132

    
133

    
134
/**
135
 * Create the key constraint in xml_revisions which points to 
136
 * xml_nodes_revisions
137
 */
138
ALTER TABLE xml_revisions ADD CONSTRAINT xml_revisions_root_fk
139
 FOREIGN KEY (rootnodeid) REFERENCES xml_nodes_revisions(nodeid);
140

    
141

    
142
 
143
/**
144
 * Delete the records from xml_index table which point to old revisions in xml_index
145
 * This is possible for documents for which the indexing thread failed during UPDATE
146
 */
147
DELETE FROM xml_index WHERE nodeid = xml_nodes_revisions.nodeid;
148

    
149

    
150

    
151
/**
152
 * Delete the records from xml_nodes which were transfered to xml_nodes_revisions 
153
 */
154
ALTER TABLE xml_nodes DROP CONSTRAINT xml_nodes_pk CASCADE;
155

    
156

    
157

    
158
/** rename xml_nodes to xml_nodes_2 */
159
ALTER TABLE xml_nodes RENAME TO xml_nodes_2;
160

    
161

    
162
/** Create a new xml_nodes table */
163
CREATE TABLE xml_nodes (
164
        nodeid INT8 default nextval('xml_nodes_id_seq'),
165
                                        -- the unique node id (pk)
166
        nodeindex INT8,         -- order of nodes within parent
167
        nodetype VARCHAR(20),   -- type (DOCUMENT, COMMENT, PI,
168
                                -- ELEMENT, ATTRIBUTE, TEXT)
169
        nodename VARCHAR(250),  -- the name of an element or attribute
170
        nodeprefix VARCHAR(50), -- the namespace prefix of the node
171
        nodedata VARCHAR(4000), -- the data for this node (e.g.,
172
                                -- for TEXT it is the content)
173
        parentnodeid INT8,      -- index of the parent of this node
174
        rootnodeid INT8,        -- index of the root node of this tree
175
        docid VARCHAR(250),     -- index to the document id
176
        date_created DATE,
177
        date_updated DATE,
178
        nodedatanumerical FLOAT8, -- the data for this node if
179
                                  -- if it is a number
180
   CONSTRAINT xml_nodes_pk PRIMARY KEY (nodeid),
181
   CONSTRAINT xml_nodes_root_fk
182
                FOREIGN KEY (rootnodeid) REFERENCES xml_nodes,
183
   CONSTRAINT xml_nodes_parent_fk
184
                FOREIGN KEY (parentnodeid) REFERENCES xml_nodes
185
);
186

    
187

    
188
/** copy data from xml_nodes_2 to xml_nodes */
189
INSERT INTO xml_nodes (nodeid, nodeindex, nodetype, nodename, 
190
nodeprefix, nodedata, parentnodeid, rootnodeid, docid, 
191
date_created, date_updated, nodedatanumerical) SELECT n.nodeid, 
192
n.nodeindex, n.nodetype, n.nodename, n.nodeprefix, n.nodedata, 
193
n.parentnodeid, n.rootnodeid, n.docid, n.date_created, 
194
n.date_updated, n.nodedatanumerical FROM xml_nodes_2 n 
195
LEFT JOIN xml_nodes_revisions r ON n.rootnodeid = r.rootnodeid 
196
WHERE r.rootnodeid is NULL;
197

    
198
/** Drop old indexes **/
199
DROP INDEX xml_nodes_idx1;
200
DROP INDEX xml_nodes_idx2;
201
DROP INDEX xml_nodes_idx3;
202

    
203
/** Create new indexes **/
204
CREATE INDEX xml_nodes_idx1 ON xml_nodes (rootnodeid);
205
CREATE INDEX xml_nodes_idx2 ON xml_nodes (parentnodeid);
206
CREATE INDEX xml_nodes_idx3 ON xml_nodes (nodename);
207

    
208
/** Add constaints which were deleted before moving xml_nodes to xml_nodes_2 */
209
ALTER TABLE xml_documents ADD CONSTRAINT xml_documents_root_fk FOREIGN KEY (rootnodeid) REFERENCES xml_nodes;
210
ALTER TABLE xml_index ADD CONSTRAINT xml_index_nodeid_fk FOREIGN KEY (nodeid) REFERENCES xml_nodes;
211

    
212
/** Drop xml_nodes_2 table */
213
DROP TABLE xml_nodes_2;
214

    
215
/** done */