Project

General

Profile

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

1 2520 sgarg
2
/*
3 2769 jones
 * Changes to the tables for handling identifiers.  Old table no longer needed,
4
 * new identifier table to be used to support LSIDs.
5
 */
6
DROP TABLE accession_number;
7
DROP SEQUENCE accession_number_id_seq;
8
9
/*
10
 * Table used to store all document identifiers in metacat.  Each identifier
11
 * consists of 4 subparts, an authority, namespace, object, and revision as
12
 * defined in the LSID specification.
13
 */
14
CREATE SEQUENCE identifier_id_seq;
15
CREATE TABLE identifier (
16
   id INT8 default nextval('identifier_id_seq') PRIMARY KEY, -- primary id
17
   authority VARCHAR(255),  -- the authority issuing the identifier
18
   namespace VARCHAR(255),  -- the namespace qualifying the identifier
19
   object    VARCHAR(255),  -- the local part of the identifier for a particular object
20
   revision  VARCHAR(255)   -- the revision part of the identifier
21
);
22
23
/*
24 2520 sgarg
 * Table for indexing the paths specified the administrator in metacat.properties
25
 */
26
27
CREATE SEQUENCE xml_path_index_id_seq;
28
CREATE TABLE xml_path_index (
29 2530 sgarg
        nodeid INT8  default nextval('xml_path_index_id_seq'),
30 2520 sgarg
        docid VARCHAR(250),     -- the document id
31
        path VARCHAR(1000),     -- precomputed path through tree
32
        nodedata VARCHAR(4000), -- the data for this node (e.g.,
33
                                -- for TEXT it is the content)
34
        nodedatanumerical FLOAT8, -- the data for this node if
35
                                  -- if it is a number
36
        parentnodeid INT8,      -- id of the parent of the node represented
37
                                -- by this row
38 2530 sgarg
        CONSTRAINT xml_path_index_pk PRIMARY KEY (nodeid),
39
        CONSTRAINT xml_path_index_docid_fk
40 2520 sgarg
                FOREIGN KEY (docid) REFERENCES xml_documents
41
);
42
43
/*
44
 * Indexes of path, nodedata and nodedatanumerical in xml_path_index
45
 */
46
CREATE INDEX xml_path_index_idx1 ON xml_path_index (path);
47
CREATE INDEX xml_path_index_idx2 ON xml_path_index (nodedata);
48
CREATE INDEX xml_path_index_idx3 ON xml_path_index (nodedatanumerical);
49
50
51 2531 sgarg
52
53 2520 sgarg
/*
54
 * Table for storing the nodes for the old revisions of the document and the deleted documents
55
 */
56
CREATE TABLE xml_nodes_revisions (
57
        nodeid INT8,            -- the unique node id (pk)
58
        nodeindex INT8,         -- order of nodes within parent
59
        nodetype VARCHAR(20),   -- type (DOCUMENT, COMMENT, PI,
60
                                -- ELEMENT, ATTRIBUTE, TEXT)
61
        nodename VARCHAR(250),  -- the name of an element or attribute
62
        nodeprefix VARCHAR(50), -- the namespace prefix of the node
63
        nodedata VARCHAR(4000), -- the data for this node (e.g.,
64
                                -- for TEXT it is the content)
65
        parentnodeid INT8,      -- index of the parent of this node
66
        rootnodeid INT8,        -- index of the root node of this tree
67
        docid VARCHAR(250),     -- index to the document id
68
        date_created DATE,
69
        date_updated DATE,
70
        nodedatanumerical FLOAT8, -- the data for this node if
71
                                  -- if it is a number
72 2530 sgarg
        CONSTRAINT xml_nodes_revisions_pk PRIMARY KEY (nodeid),
73
        CONSTRAINT xml_nodes_revisions_root_fk
74 2520 sgarg
                FOREIGN KEY (rootnodeid) REFERENCES xml_nodes_revisions,
75 2530 sgarg
        CONSTRAINT xml_nodes_revisions_parent_fk
76 2520 sgarg
                FOREIGN KEY (parentnodeid) REFERENCES xml_nodes_revisions
77
);
78
79
/*
80
 * Indexes of rootnodeid, parentnodeid, and nodename in xml_nodes_revisions
81
 */
82
CREATE INDEX xml_nodes_revisions_idx1 ON xml_nodes_revisions (rootnodeid);
83
CREATE INDEX xml_nodes_revisions_idx2 ON xml_nodes_revisions (parentnodeid);
84
CREATE INDEX xml_nodes_revisions_idx3 ON xml_nodes_revisions (nodename);
85
86
87 2531 sgarg
88 2520 sgarg
/**
89
 * Drop the constraint from xml_revisions which points to xml_nodes
90
 */
91
ALTER TABLE xml_revisions DROP CONSTRAINT xml_revisions_root_fk;
92
93
94 2531 sgarg
95 2520 sgarg
/**
96
 * Copy the nodes from xml_nodes to xml_nodes_revisions for old revisions
97
 * of the documents and deleted documents
98
 */
99 2531 sgarg
INSERT INTO xml_nodes_revisions (nodeid, nodeindex, nodetype,
100
nodename, nodeprefix, nodedata, parentnodeid, rootnodeid, docid,
101
date_created, date_updated, nodedatanumerical) SELECT n.nodeid,
102
n.nodeindex, n.nodetype, n.nodename, n.nodeprefix, n.nodedata,
103
n.parentnodeid, n.rootnodeid, n.docid, n.date_created, n.date_updated,
104
n.nodedatanumerical FROM xml_nodes n LEFT JOIN xml_documents
105
ON n.rootnodeid = xml_documents.rootnodeid WHERE
106
xml_documents.rootnodeid IS NULL;
107 2520 sgarg
108
109 2531 sgarg
110 2520 sgarg
/**
111
 * Create the key constraint in xml_revisions which points to
112
 * xml_nodes_revisions
113
 */
114
ALTER TABLE xml_revisions ADD CONSTRAINT xml_revisions_root_fk
115
 FOREIGN KEY (rootnodeid) REFERENCES xml_nodes_revisions(nodeid);
116 2531 sgarg
117
118 2520 sgarg
119
/**
120
 * Delete the records from xml_index table which point to old revisions in xml_index
121
 * This is possible for documents for which the indexing thread failed during UPDATE
122
 */
123 4080 daigle
DELETE FROM xml_index WHERE nodeid IN (SELECT nodeid FROM xml_nodes_revisions);
124 2520 sgarg
125
126 2531 sgarg
127 2520 sgarg
/**
128
 * Delete the records from xml_nodes which were transfered to xml_nodes_revisions
129
 */
130 2531 sgarg
ALTER TABLE xml_nodes DROP CONSTRAINT xml_nodes_pk CASCADE;
131 2530 sgarg
132
133
134 2531 sgarg
/** rename xml_nodes to xml_nodes_2 */
135
ALTER TABLE xml_nodes RENAME TO xml_nodes_2;
136
137
138
/** Create a new xml_nodes table */
139 2530 sgarg
CREATE TABLE xml_nodes (
140
        nodeid INT8 default nextval('xml_nodes_id_seq'),
141
                                        -- the unique node id (pk)
142
        nodeindex INT8,         -- order of nodes within parent
143
        nodetype VARCHAR(20),   -- type (DOCUMENT, COMMENT, PI,
144
                                -- ELEMENT, ATTRIBUTE, TEXT)
145
        nodename VARCHAR(250),  -- the name of an element or attribute
146
        nodeprefix VARCHAR(50), -- the namespace prefix of the node
147
        nodedata VARCHAR(4000), -- the data for this node (e.g.,
148
                                -- for TEXT it is the content)
149
        parentnodeid INT8,      -- index of the parent of this node
150
        rootnodeid INT8,        -- index of the root node of this tree
151
        docid VARCHAR(250),     -- index to the document id
152
        date_created DATE,
153
        date_updated DATE,
154
        nodedatanumerical FLOAT8, -- the data for this node if
155
                                  -- if it is a number
156
   CONSTRAINT xml_nodes_pk PRIMARY KEY (nodeid),
157
   CONSTRAINT xml_nodes_root_fk
158
                FOREIGN KEY (rootnodeid) REFERENCES xml_nodes,
159
   CONSTRAINT xml_nodes_parent_fk
160
                FOREIGN KEY (parentnodeid) REFERENCES xml_nodes
161
);
162
163
164 2531 sgarg
/** copy data from xml_nodes_2 to xml_nodes */
165
INSERT INTO xml_nodes (nodeid, nodeindex, nodetype, nodename,
166
nodeprefix, nodedata, parentnodeid, rootnodeid, docid,
167
date_created, date_updated, nodedatanumerical) SELECT n.nodeid,
168
n.nodeindex, n.nodetype, n.nodename, n.nodeprefix, n.nodedata,
169
n.parentnodeid, n.rootnodeid, n.docid, n.date_created,
170
n.date_updated, n.nodedatanumerical FROM xml_nodes_2 n
171
LEFT JOIN xml_nodes_revisions r ON n.rootnodeid = r.rootnodeid
172
WHERE r.rootnodeid is NULL;
173 2530 sgarg
174 2531 sgarg
/** Drop old indexes **/
175
DROP INDEX xml_nodes_idx1;
176
DROP INDEX xml_nodes_idx2;
177
DROP INDEX xml_nodes_idx3;
178
179
/** Create new indexes **/
180 2530 sgarg
CREATE INDEX xml_nodes_idx1 ON xml_nodes (rootnodeid);
181
CREATE INDEX xml_nodes_idx2 ON xml_nodes (parentnodeid);
182
CREATE INDEX xml_nodes_idx3 ON xml_nodes (nodename);
183
184 2531 sgarg
/** Add constaints which were deleted before moving xml_nodes to xml_nodes_2 */
185 2530 sgarg
ALTER TABLE xml_documents ADD CONSTRAINT xml_documents_root_fk FOREIGN KEY (rootnodeid) REFERENCES xml_nodes;
186
ALTER TABLE xml_index ADD CONSTRAINT xml_index_nodeid_fk FOREIGN KEY (nodeid) REFERENCES xml_nodes;
187
188 2531 sgarg
/** Drop xml_nodes_2 table */
189
DROP TABLE xml_nodes_2;
190 2530 sgarg
191 2531 sgarg
/** done */