Project

General

Profile

metacat / src / upgrade-db-to-1.6-oracle.sql @ 8504

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: daigle $'
7
 *     '$Date: 2008-07-11 10:04:49 -0700 (Fri, 11 Jul 2008) $'
8
 * '$Revision: 4104 $'
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
 * Changes to the tables for handling identifiers.  Old table no longer needed,
27
 * new identifier table to be used to support LSIDs.
28
 */
29
DROP TABLE accession_number;
30
DROP SEQUENCE accession_number_id_seq;
31

    
32
/*
33
 * Table used to store all document identifiers in metacat.  Each identifier
34
 * consists of 4 subparts, an authority, namespace, object, and revision as
35
 * defined in the LSID specification.
36
 */
37
CREATE SEQUENCE identifier_id_seq;
38
CREATE TABLE identifier (
39
   id        NUMBER(20) PRIMARY KEY, -- primary key
40
   authority VARCHAR2(255),  -- the authority issuing the identifier
41
   namespace VARCHAR2(255),  -- the namespace qualifying the identifier
42
   object    VARCHAR2(255),  -- the local part of the identifier for a particular object
43
   revision  VARCHAR2(255)   -- the revision part of the identifier
44
);
45
CREATE TRIGGER identifier_before_insert
46
BEFORE INSERT ON identifier FOR EACH ROW
47
BEGIN
48
  SELECT identifier_id_seq.nextval
49
    INTO :new.id
50
    FROM dual;
51
END;
52
/
53

    
54
/*
55
 * Index of Paths - table to store nodes with paths specified by userst in metacat.properties
56
 */
57
CREATE TABLE xml_path_index (
58
        nodeid          NUMBER(20),     -- the unique node id
59
        docid           VARCHAR2(250),  -- index to the document id
60
        path            VARCHAR2(1000), -- precomputed path through tree
61
            nodedata        VARCHAR2(4000), -- the data for this node e.g.,
62
        nodedatanumerical NUMBER(20),   -- the data for this node if
63
        parentnodeid    NUMBER(20),     -- index of the parent of this node
64
        CONSTRAINT xml_path_index_pk PRIMARY KEY (nodeid),
65
        CONSTRAINT xml_path_index_docid_fk FOREIGN KEY (docid) REFERENCES xml_documents
66
 );                                                                                        
67

    
68

    
69
/*
70
 * create sequence an trigger
71
 */
72
CREATE SEQUENCE xml_path_index_id_seq;                                                                                                                                                             
73
CREATE TRIGGER xml_path_index_before_insert
74
BEFORE INSERT ON xml_path_index FOR EACH ROW
75
BEGIN
76
  SELECT xml_path_index_id_seq.nextval
77
    INTO :new.nodeid
78
    FROM dual;
79
END;
80
/
81

    
82

    
83
/**
84
 * Index of the path, nodedata, nodedatanumerical in xml_path_index
85
 */
86
CREATE INDEX xml_path_index_idx1 ON xml_path_index (path);
87
CREATE INDEX xml_path_index_idx2 ON xml_path_index (nodedata);
88
CREATE INDEX xml_path_index_idx3 ON xml_path_index (nodedatanumerical);
89

    
90

    
91

    
92
/**
93
 * Create the xml_nodes_revisions table
94
 * to store nodes from xml_nodes which 
95
 * are of old revisions and deleted document
96
 */
97

    
98
CREATE TABLE xml_nodes_revisions (
99
        nodeid          NUMBER(20),     -- the unique node id (pk)
100
        nodeindex       NUMBER(10),     -- order of nodes within parent
101
        nodetype        VARCHAR2(20),   -- type (DOCUMENT, COMMENT, PI,
102
                                        -- ELEMENT, ATTRIBUTE, TEXT)
103
        nodename        VARCHAR2(250),  -- the name of an element or attribute
104
        nodeprefix      VARCHAR2(50),   -- the namespace prefix of an element
105
                                        -- or attribute
106
        nodedata        VARCHAR2(4000), -- the data for this node (e.g.,
107
                                        -- for TEXT it is the content)
108
        parentnodeid    NUMBER(20),     -- index of the parent of this node
109
        rootnodeid      NUMBER(20),     -- index of the root node of this tree
110
        docid           VARCHAR2(250),  -- index to the document id
111
        date_created    DATE,
112
        date_updated    DATE,
113
        nodedatanumerical NUMBER,       -- the data for this node if
114
                                        -- it is a number
115
   CONSTRAINT xml_nodes_revisions_pk PRIMARY KEY (nodeid),
116
   CONSTRAINT xml_nodes_revisions_root_fk
117
                FOREIGN KEY (rootnodeid) REFERENCES xml_nodes_revisions,
118
   CONSTRAINT xml_nodes_revisions_parent_fk
119
                FOREIGN KEY (parentnodeid) REFERENCES xml_nodes_revisions
120
);
121

    
122

    
123
/**
124
 * Indexes of rootnodeid, parentnodeid, and nodename in xml_nodes_revision
125
 */
126
CREATE INDEX xml_nodes_revisions_idx1 ON xml_nodes_revisions (rootnodeid);
127
CREATE INDEX xml_nodes_revisions_idx2 ON xml_nodes_revisions (parentnodeid);
128
CREATE INDEX xml_nodes_revisions_idx3 ON xml_nodes_revisions (nodename);
129

    
130

    
131
/**
132
 * Drop the constraint from xml_revisions which points to xml_nodes
133
 */
134
ALTER TABLE xml_revisions DROP CONSTRAINT xml_revisions_root_fk;
135

    
136

    
137
/**
138
 * Copy the nodes from xml_nodes to xml_nodes_revisions for old revisions
139
 * of the documents and deleted documents
140
 */
141
INSERT INTO xml_nodes_revisions (nodeid, nodeindex, nodetype,
142
nodename, nodeprefix, nodedata, parentnodeid, rootnodeid,
143
docid, date_created, date_updated, nodedatanumerical)
144
SELECT * FROM xml_nodes WHERE rootnodeid NOT IN
145
(SELECT rootnodeid from xml_documents where rootnodeid is not NULL);
146

    
147

    
148
/**
149
 * Create the key constraint in xml_revisions which points to 
150
 * xml_nodes_revisions
151
 */
152
ALTER TABLE xml_revisions ADD CONSTRAINT xml_revisions_root_fk
153
 FOREIGN KEY (rootnodeid) REFERENCES xml_nodes_revisions (nodeid);
154
 
155

    
156
/**
157
 * Delete the records from xml_index table which point to old revisions in xml_index
158
 * This is possible for documents for which the indexing thread failed during UPDATE
159
 */
160

    
161
DELETE FROM xml_index WHERE nodeid IN (SELECT nodeid FROM xml_nodes WHERE
162
rootnodeid NOT IN (SELECT rootnodeid FROM xml_documents WHERE rootnodeid IS NOT NULL)); 
163

    
164
/**
165
 * Delete the records from xml_nodes which were transfered to xml_nodes_revisions 
166
 */
167

    
168
/**
169
 * Below given statement takes a lot of time to excute
170
 *
171
 * DELETE FROM xml_nodes WHERE rootnodeid NOT IN
172
 * (SELECT rootnodeid from xml_documents where rootnodeid is not NULL);
173
 *
174
 * Hence.....
175
 */
176

    
177

    
178
/**
179
* Drop the xml_nodes primark key....
180
*/
181
ALTER TABLE xml_nodes DROP CONSTRAINT xml_nodes_pk CASCADE;
182

    
183

    
184
/** rename xml_nodes to xml_nodes_2 */
185
ALTER TABLE xml_nodes RENAME TO xml_nodes_2;
186

    
187
/** create a new xml_nodes table with new primary and foreign keys*/
188
CREATE TABLE xml_nodes (
189
       nodeid          NUMBER(20),     -- the unique node id (pk)
190
       nodeindex       NUMBER(10),     -- order of nodes within parent
191
       nodetype        VARCHAR2(20),   -- type (DOCUMENT, COMMENT, PI,
192
                                       -- ELEMENT, ATTRIBUTE, TEXT)
193
       nodename        VARCHAR2(250),  -- the name of an element or attribute
194
       nodeprefix      VARCHAR2(50),   -- the namespace prefix of an element
195
                                       -- or attribute
196
       nodedata        VARCHAR2(4000), -- the data for this node (e.g.,
197
                                       -- for TEXT it is the content)
198
       parentnodeid    NUMBER(20),     -- index of the parent of this node
199
       rootnodeid      NUMBER(20),     -- index of the root node of this tree
200
       docid           VARCHAR2(250),  -- index to the document id
201
       date_created    DATE,
202
       date_updated    DATE,
203
       nodedatanumerical NUMBER,       -- the data for this node if
204
                                       -- it is a number
205
  CONSTRAINT xml_nodes_pk PRIMARY KEY (nodeid),
206
  CONSTRAINT xml_nodes_root_fk
207
               FOREIGN KEY (rootnodeid) REFERENCES xml_nodes,
208
  CONSTRAINT xml_nodes_parent_fk
209
               FOREIGN KEY (parentnodeid) REFERENCES xml_nodes
210
);
211

    
212
/** copy nodes from xml_nodes_2  to xml_nodes */
213
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_2 n, xml_nodes_revisions r WHERE n.rootnodeid = r.rootnodeid(+) AND r.rootnodeid is NULL;
214

    
215
/** Drop old indexes **/
216
DROP INDEX xml_nodes_idx1;
217
DROP INDEX xml_nodes_idx2;
218
DROP INDEX xml_nodes_idx3;
219

    
220
/** Create new indexes **/
221
CREATE INDEX xml_nodes_idx1 ON xml_nodes (rootnodeid);
222
CREATE INDEX xml_nodes_idx2 ON xml_nodes (parentnodeid);
223
CREATE INDEX xml_nodes_idx3 ON xml_nodes (nodename);
224

    
225

    
226
/** Re-add trigger to xml_node **/
227
DROP TRIGGER xml_nodes_before_insert;
228
CREATE TRIGGER xml_nodes_before_insert
229
BEFORE INSERT ON xml_nodes FOR EACH ROW
230
BEGIN
231
  SELECT xml_nodes_id_seq.nextval
232
    INTO :new.nodeid
233
    FROM dual;
234
END;
235
/
236

    
237
/** Add constaints which were deleted before moving xml_nodes to xml_nodes_2 */
238
ALTER TABLE xml_documents ADD CONSTRAINT xml_documents_root_fk FOREIGN KEY (rootnodeid) REFERENCES xml_nodes;
239
ALTER TABLE xml_index ADD CONSTRAINT xml_index_nodeid_fk FOREIGN KEY (nodeid) REFERENCES xml_nodes;
240

    
241
/** Drop xml_nodes_2 table */
242
DROP TABLE xml_nodes_2;
243

    
244

    
245

    
246
/** Update xml_catalog so that eml-2.0.1 stylesheets are used for displaying eml-2.0.0 documents */
247
UPDATE xml_catalog SET system_id='http://knb.msi.ucsb.edu/knb/schema/eml-2.0.0/eml.xsd' WHERE public_id = 'eml://ecoinformatics.org/eml-2.0.0';
248

    
249

    
250
/** Done */