Revision 4692
Added by daigle about 16 years ago
src/scripts/metacat-db-versions/1.8.1_loaddtdschema.sql | ||
---|---|---|
1 |
DELETE FROM xml_catalog |
|
2 |
WHERE entry_type LIKE 'DTD' |
|
3 |
AND public_id LIKE '%2.0.0beta6%'; |
|
4 |
DELETE FROM xml_catalog |
|
5 |
WHERE entry_type LIKE 'Schema' |
|
6 |
AND system_id LIKE '%eml%'; |
|
7 |
INSERT INTO xml_catalog (entry_type, public_id, system_id) |
|
8 |
VALUES ('DTD', '-//ecoinformatics.org//eml-access-2.0.0beta6//EN', |
|
9 |
'/dtd/eml-access-2.0.0beta6.dtd'); |
|
10 |
INSERT INTO xml_catalog (entry_type, public_id, system_id) |
|
11 |
VALUES ('DTD', '-//ecoinformatics.org//eml-attribute-2.0.0beta6//EN', |
|
12 |
'/dtd/eml-attribute-2.0.0beta6.dtd'); |
|
13 |
INSERT INTO xml_catalog (entry_type, public_id, system_id) |
|
14 |
VALUES ('DTD', '-//ecoinformatics.org//eml-constraint-2.0.0beta6//EN', |
|
15 |
'/dtd/eml-constraint-2.0.0beta6.dtd'); |
|
16 |
INSERT INTO xml_catalog (entry_type, public_id, system_id) |
|
17 |
VALUES ('DTD', '-//ecoinformatics.org//eml-coverage-2.0.0beta6//EN', |
|
18 |
'/dtd/eml-coverage-2.0.0beta6.dtd'); |
|
19 |
INSERT INTO xml_catalog (entry_type, public_id, system_id) |
|
20 |
VALUES ('DTD', '-//ecoinformatics.org//eml-dataset-2.0.0beta6//EN', |
|
21 |
'/dtd/eml-dataset-2.0.0beta6.dtd'); |
|
22 |
INSERT INTO xml_catalog (entry_type, public_id, system_id) |
|
23 |
VALUES ('DTD', '-//ecoinformatics.org//eml-entity-2.0.0beta6//EN', |
|
24 |
'/dtd/eml-entity-2.0.0beta6.dtd'); |
|
25 |
INSERT INTO xml_catalog (entry_type, public_id, system_id) |
|
26 |
VALUES ('DTD', '-//ecoinformatics.org//eml-literature-2.0.0beta6//EN', |
|
27 |
'/dtd/eml-literature-2.0.0beta6.dtd'); |
|
28 |
INSERT INTO xml_catalog (entry_type, public_id, system_id) |
|
29 |
VALUES ('DTD', '-//ecoinformatics.org//eml-physical-2.0.0beta6//EN', |
|
30 |
'/dtd/eml-physical-2.0.0beta6.dtd'); |
|
31 |
INSERT INTO xml_catalog (entry_type, public_id, system_id) |
|
32 |
VALUES ('DTD', '-//ecoinformatics.org//eml-project-2.0.0beta6//EN', |
|
33 |
'/dtd/eml-project-2.0.0beta6.dtd'); |
|
34 |
INSERT INTO xml_catalog (entry_type, public_id, system_id) |
|
35 |
VALUES ('DTD', '-//ecoinformatics.org//eml-protocol-2.0.0beta6//EN', |
|
36 |
'/dtd/eml-protocol-2.0.0beta6.dtd'); |
|
37 |
INSERT INTO xml_catalog (entry_type, public_id, system_id) |
|
38 |
VALUES ('DTD', '-//ecoinformatics.org//eml-software-2.0.0beta6//EN', |
|
39 |
'/dtd/eml-software-2.0.0beta6.dtd'); |
|
40 |
INSERT INTO xml_catalog (entry_type, public_id, system_id) |
|
41 |
VALUES ('Schema', 'eml://ecoinformatics.org/eml-2.0.1', '/schema/eml-2.0.1/eml.xsd'); |
|
42 |
INSERT INTO xml_catalog (entry_type, public_id, system_id) |
|
43 |
VALUES ('Schema', 'eml://ecoinformatics.org/eml-2.0.0', '/schema/eml-2.0.0/eml.xsd'); |
|
44 |
INSERT INTO xml_catalog (entry_type, public_id, system_id) |
|
45 |
VALUES ('Schema', 'http://www.xml-cml.org/schema/stmml', '/schema/eml-2.0.1/stmml.xsd'); |
|
46 |
INSERT INTO xml_catalog (entry_type, public_id, system_id) |
|
47 |
VALUES ('Schema', 'metadata', '/schema/fgdc-std-001/fgdc-std-001-1998.xsd'); |
src/scripts/metacat-db-versions/1.4_xmltables-postgres.sql | ||
---|---|---|
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: 2004-09-15 08:56:46 -0700 (Wed, 15 Sep 2004) $' |
|
8 |
* '$Revision: 2276 $' |
|
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 |
* this is sql script does the same as the sql script named |
|
27 |
* xmltables.sql except that this script is to be use to |
|
28 |
* create the database tables on a Postgresql backend rather |
|
29 |
* than an Oracle Backend |
|
30 |
*/ |
|
31 |
|
|
32 |
/* |
|
33 |
*Replication -- table to store servers that metacat is replicated to |
|
34 |
*/ |
|
35 |
CREATE SEQUENCE xml_replication_id_seq; |
|
36 |
CREATE TABLE xml_replication ( |
|
37 |
serverid INT8 default nextval('xml_replication_id_seq'), |
|
38 |
server VARCHAR(512), |
|
39 |
last_checked DATE, |
|
40 |
replicate INT8, |
|
41 |
datareplicate INT8, |
|
42 |
hub INT8, |
|
43 |
CONSTRAINT xml_replication_pk PRIMARY KEY (serverid) |
|
44 |
); |
|
45 |
|
|
46 |
INSERT INTO xml_replication (server, replicate, datareplicate, hub) VALUES ('localhost', '0', '0', '0'); |
|
47 |
|
|
48 |
/* |
|
49 |
* Nodes -- table to store XML Nodes (both elements and attributes) |
|
50 |
*/ |
|
51 |
CREATE SEQUENCE xml_nodes_id_seq; |
|
52 |
CREATE TABLE xml_nodes ( |
|
53 |
nodeid INT8 default nextval('xml_nodes_id_seq'), |
|
54 |
-- the unique node id (pk) |
|
55 |
nodeindex INT8, -- order of nodes within parent |
|
56 |
nodetype VARCHAR(20), -- type (DOCUMENT, COMMENT, PI, |
|
57 |
-- ELEMENT, ATTRIBUTE, TEXT) |
|
58 |
nodename VARCHAR(250), -- the name of an element or attribute |
|
59 |
nodeprefix VARCHAR(50), -- the namespace prefix of the node |
|
60 |
nodedata VARCHAR(4000), -- the data for this node (e.g., |
|
61 |
-- for TEXT it is the content) |
|
62 |
parentnodeid INT8, -- index of the parent of this node |
|
63 |
rootnodeid INT8, -- index of the root node of this tree |
|
64 |
docid VARCHAR(250), -- index to the document id |
|
65 |
date_created DATE, |
|
66 |
date_updated DATE, |
|
67 |
CONSTRAINT xml_nodes_pk PRIMARY KEY (nodeid), |
|
68 |
CONSTRAINT xml_nodes_root_fk |
|
69 |
FOREIGN KEY (rootnodeid) REFERENCES xml_nodes, |
|
70 |
CONSTRAINT xml_nodes_parent_fk |
|
71 |
FOREIGN KEY (parentnodeid) REFERENCES xml_nodes |
|
72 |
); |
|
73 |
/* |
|
74 |
* Indexes of rootnodeid, parentnodeid, and nodename in xml_nodes |
|
75 |
*/ |
|
76 |
CREATE INDEX xml_nodes_idx1 ON xml_nodes (rootnodeid); |
|
77 |
CREATE INDEX xml_nodes_idx2 ON xml_nodes (parentnodeid); |
|
78 |
CREATE INDEX xml_nodes_idx3 ON xml_nodes (nodename); |
|
79 |
|
|
80 |
/* |
|
81 |
* XML Catalog -- table to store all external sources for XML documents |
|
82 |
*/ |
|
83 |
CREATE SEQUENCE xml_catalog_id_seq; |
|
84 |
CREATE TABLE xml_catalog ( |
|
85 |
catalog_id INT8 default nextval('xml_catalog_id_seq'), |
|
86 |
-- the id for this catalog entry |
|
87 |
entry_type VARCHAR(500), -- the type of this catalog entry |
|
88 |
-- (e.g., DTD, XSD, XSL) |
|
89 |
source_doctype VARCHAR(500), -- the source public_id for transforms |
|
90 |
target_doctype VARCHAR(500), -- the target public_id for transforms |
|
91 |
public_id VARCHAR(500), -- the unique id for this type |
|
92 |
system_id VARCHAR(1000), -- the local location of the object |
|
93 |
CONSTRAINT xml_catalog_pk PRIMARY KEY (catalog_id), |
|
94 |
CONSTRAINT xml_catalog_uk UNIQUE |
|
95 |
(entry_type, source_doctype, target_doctype, public_id) |
|
96 |
); |
|
97 |
|
|
98 |
/* |
|
99 |
* Sequence to get uniqueID for Accession # |
|
100 |
*/ |
|
101 |
CREATE SEQUENCE xml_documents_id_seq; |
|
102 |
/* |
|
103 |
* Documents -- table to store XML documents |
|
104 |
*/ |
|
105 |
CREATE TABLE xml_documents ( |
|
106 |
docid VARCHAR(250), -- the document id # |
|
107 |
rootnodeid INT8, -- reference to root node of the DOM |
|
108 |
docname VARCHAR(100), -- usually the root element name |
|
109 |
doctype VARCHAR(100), -- public id indicating document type |
|
110 |
user_owner VARCHAR(100), -- the user owned the document |
|
111 |
user_updated VARCHAR(100), -- the user updated the document |
|
112 |
server_location INT8, -- the server on which this document resides |
|
113 |
rev INT8 default 1, -- the revision number of the document |
|
114 |
date_created DATE, |
|
115 |
date_updated DATE, |
|
116 |
public_access INT8, -- flag for public access |
|
117 |
catalog_id INT8, -- reference to xml_catalog |
|
118 |
CONSTRAINT xml_documents_pk PRIMARY KEY (docid), |
|
119 |
CONSTRAINT xml_documents_rep_fk |
|
120 |
FOREIGN KEY (server_location) REFERENCES xml_replication, |
|
121 |
CONSTRAINT xml_documents_root_fk |
|
122 |
FOREIGN KEY (rootnodeid) REFERENCES xml_nodes, |
|
123 |
CONSTRAINT xml_documents_catalog_fk |
|
124 |
FOREIGN KEY (catalog_id) REFERENCES xml_catalog |
|
125 |
); |
|
126 |
|
|
127 |
/* |
|
128 |
* Index of <docid,doctype> in xml_document |
|
129 |
*/ |
|
130 |
CREATE INDEX xml_documents_idx1 ON xml_documents (docid, doctype); |
|
131 |
|
|
132 |
/* |
|
133 |
* Revised Documents -- table to store XML documents saved after an UPDATE |
|
134 |
* or DELETE |
|
135 |
*/ |
|
136 |
CREATE SEQUENCE xml_revisions_id_seq; |
|
137 |
CREATE TABLE xml_revisions ( |
|
138 |
revisionid INT8 default nextval('xml_revisions_id_seq'), |
|
139 |
-- the revision number we are saving |
|
140 |
docid VARCHAR(250), -- the document id # |
|
141 |
rootnodeid INT8, -- reference to root node of the DOM |
|
142 |
docname VARCHAR(100), -- usually the root element name |
|
143 |
doctype VARCHAR(100), -- public id indicating document type |
|
144 |
user_owner VARCHAR(100), |
|
145 |
user_updated VARCHAR(100), |
|
146 |
server_location INT8, |
|
147 |
rev INT8, |
|
148 |
date_created DATE, |
|
149 |
date_updated DATE, |
|
150 |
public_access INT8, -- flag for public access |
|
151 |
catalog_id INT8, -- reference to xml_catalog |
|
152 |
CONSTRAINT xml_revisions_pk PRIMARY KEY (revisionid), |
|
153 |
CONSTRAINT xml_revisions_rep_fk |
|
154 |
FOREIGN KEY (server_location) REFERENCES xml_replication, |
|
155 |
CONSTRAINT xml_revisions_root_fk |
|
156 |
FOREIGN KEY (rootnodeid) REFERENCES xml_nodes, |
|
157 |
CONSTRAINT xml_revisions_catalog_fk |
|
158 |
FOREIGN KEY (catalog_id) REFERENCES xml_catalog |
|
159 |
); |
|
160 |
|
|
161 |
|
|
162 |
/* |
|
163 |
* ACL -- table to store ACL for XML documents by principals |
|
164 |
*/ |
|
165 |
CREATE TABLE xml_access ( |
|
166 |
docid VARCHAR(250), -- the document id # |
|
167 |
accessfileid VARCHAR(250), -- the document id # for the access file |
|
168 |
principal_name VARCHAR(100), -- name of user, group, etc. |
|
169 |
permission INT8, -- "read", "write", "all" |
|
170 |
perm_type VARCHAR(32), -- "allowed" or "denied" |
|
171 |
perm_order VARCHAR(32), -- "allow first" or "deny first" |
|
172 |
begin_time DATE, -- the time that permission begins |
|
173 |
end_time DATE, -- the time that permission ends |
|
174 |
ticket_count INT8, -- ticket counter for that permission |
|
175 |
subtreeid VARCHAR(32), |
|
176 |
startnodeid INT8, |
|
177 |
endnodeid INT8, |
|
178 |
CONSTRAINT xml_access_ck CHECK (begin_time < end_time), |
|
179 |
CONSTRAINT xml_access_accessfileid_fk |
|
180 |
FOREIGN KEY (accessfileid) REFERENCES xml_documents |
|
181 |
); |
|
182 |
|
|
183 |
/* |
|
184 |
* Index of Nodes -- table to store precomputed paths through tree for |
|
185 |
* quick searching in structured searches |
|
186 |
*/ |
|
187 |
CREATE TABLE xml_index ( |
|
188 |
nodeid INT8, -- the unique node id |
|
189 |
path VARCHAR(1000), -- precomputed path through tree |
|
190 |
docid VARCHAR(250), -- index to the document id |
|
191 |
doctype VARCHAR(100), -- public id indicating document type |
|
192 |
parentnodeid INT8, -- id of the parent of the node represented |
|
193 |
-- by this row |
|
194 |
CONSTRAINT xml_index_pk PRIMARY KEY (nodeid,path), |
|
195 |
CONSTRAINT xml_index_nodeid_fk FOREIGN KEY (nodeid) REFERENCES xml_nodes, |
|
196 |
CONSTRAINT xml_index_docid_fk |
|
197 |
FOREIGN KEY (docid) REFERENCES xml_documents |
|
198 |
); |
|
199 |
|
|
200 |
/* |
|
201 |
* Index of the paths in xml_index |
|
202 |
*/ |
|
203 |
CREATE INDEX xml_index_idx1 ON xml_index (path); |
|
204 |
|
|
205 |
|
|
206 |
CREATE SEQUENCE xml_relation_id_seq; |
|
207 |
CREATE TABLE xml_relation ( |
|
208 |
relationid INT8 default nextval('xml_relation_id_seq') PRIMARY KEY, |
|
209 |
-- unique id |
|
210 |
docid VARCHAR(250) , -- the docid of the package file |
|
211 |
-- that this relation came from |
|
212 |
packagetype VARCHAR(250), -- the type of the package |
|
213 |
subject VARCHAR(512) NOT NULL, -- the subject of the relation |
|
214 |
subdoctype VARCHAR(128), -- the doctype of the subject |
|
215 |
relationship VARCHAR(128) NOT NULL,-- the relationship type |
|
216 |
object VARCHAR(512) NOT NULL, -- the object of the relation |
|
217 |
objdoctype VARCHAR(128), -- the doctype of the object |
|
218 |
CONSTRAINT xml_relation_uk UNIQUE (docid, subject, relationship, object), |
|
219 |
CONSTRAINT xml_relation_docid_fk |
|
220 |
FOREIGN KEY (docid) REFERENCES xml_documents |
|
221 |
); |
|
222 |
|
|
223 |
/* |
|
224 |
* Table used as Unique ID generator for the uniqueid part of Accession# |
|
225 |
*/ |
|
226 |
CREATE SEQUENCE accession_number_id_seq; |
|
227 |
CREATE TABLE accession_number ( |
|
228 |
uniqueid INT8 default nextval('accession_number_id_seq') PRIMARY KEY, |
|
229 |
site_code VARCHAR(100), |
|
230 |
date_created DATE |
|
231 |
); |
|
232 |
|
|
233 |
/* |
|
234 |
* accesssubtree -- table to store access subtree info |
|
235 |
*/ |
|
236 |
CREATE TABLE xml_accesssubtree ( |
|
237 |
docid VARCHAR(250), -- the document id # |
|
238 |
rev INT8 default 1, --the revision number of the docume |
|
239 |
controllevel VARCHAR(50), -- the level it control -- document or subtree |
|
240 |
subtreeid VARCHAR(250), -- the subtree id |
|
241 |
startnodeid INT8, -- the start node id of access subtree |
|
242 |
endnodeid INT8, -- the end node if of access subtree |
|
243 |
CONSTRAINT xml_accesssubtree_docid_fk |
|
244 |
FOREIGN KEY (docid) REFERENCES xml_documents |
|
245 |
); |
|
246 |
|
|
247 |
/* |
|
248 |
* Logging -- table to store metadata and data access log |
|
249 |
*/ |
|
250 |
CREATE SEQUENCE access_log_id_seq; |
|
251 |
CREATE TABLE access_log ( |
|
252 |
entryid INT8 default nextval ('access_log_id_seq'), -- the identifier for the log event |
|
253 |
ip_address VARCHAR(512), -- the ip address inititiating the event |
|
254 |
principal VARCHAR(512), -- the user initiiating the event |
|
255 |
docid VARCHAR(250), -- the document id # |
|
256 |
event VARCHAR(512), -- the code symbolizing the event type |
|
257 |
date_logged TIMESTAMP, -- the datetime on which the event occurred |
|
258 |
CONSTRAINT access_log_pk PRIMARY KEY (entryid) |
|
259 |
); |
|
260 |
|
|
261 |
/* |
|
262 |
* harvest_site_schedule -- table to store harvest sites and schedule info |
|
263 |
*/ |
|
264 |
CREATE TABLE harvest_site_schedule ( |
|
265 |
site_schedule_id INT8, -- unique id |
|
266 |
documentlisturl VARCHAR(255), -- URL of the site harvest document list |
|
267 |
ldapdn VARCHAR(255), -- LDAP distinguished name for site account |
|
268 |
datenextharvest DATE, -- scheduled date of next harvest |
|
269 |
datelastharvest DATE, -- recorded date of last harvest |
|
270 |
updatefrequency INT8, -- the harvest update frequency |
|
271 |
unit VARCHAR(50), -- update unit -- days weeks or months |
|
272 |
contact_email VARCHAR(50), -- email address of the site contact person |
|
273 |
ldappwd VARCHAR(20), -- LDAP password for site account |
|
274 |
CONSTRAINT harvest_site_schedule_pk PRIMARY KEY (site_schedule_id) |
|
275 |
); |
|
276 |
|
|
277 |
/* |
|
278 |
* harvest_log -- table to log entries for harvest operations |
|
279 |
*/ |
|
280 |
CREATE TABLE harvest_log ( |
|
281 |
harvest_log_id INT8, -- unique id |
|
282 |
harvest_date DATE, -- date of the current harvest |
|
283 |
status INT8, -- non-zero indicates an error status |
|
284 |
message VARCHAR(1000), -- text message for this log entry |
|
285 |
harvest_operation_code VARCHAR(30), -- the type of harvest operation |
|
286 |
site_schedule_id INT8, -- site schedule id, or 0 if no site |
|
287 |
CONSTRAINT harvest_log_pk PRIMARY KEY (harvest_log_id) |
|
288 |
); |
|
289 |
|
|
290 |
/* |
|
291 |
* harvest_detail_log -- table to log detailed info about documents that |
|
292 |
* generated errors during the harvest |
|
293 |
*/ |
|
294 |
CREATE TABLE harvest_detail_log ( |
|
295 |
detail_log_id INT8, -- unique id |
|
296 |
harvest_log_id INT8, -- ponter to the related log entry |
|
297 |
scope VARCHAR(50), -- document scope |
|
298 |
identifier INT8, -- document identifier |
|
299 |
revision INT8, -- document revision |
|
300 |
document_url VARCHAR(255), -- document URL |
|
301 |
error_message VARCHAR(1000), -- text error message |
|
302 |
document_type VARCHAR(100), -- document type |
|
303 |
CONSTRAINT harvest_detail_log_pk PRIMARY KEY (detail_log_id), |
|
304 |
CONSTRAINT harvest_detail_log_fk |
|
305 |
FOREIGN KEY (harvest_log_id) REFERENCES harvest_log |
|
306 |
); |
|
307 |
|
src/scripts/metacat-db-versions/1.5_xmltables-oracle.sql | ||
---|---|---|
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-04-04 09:12:31 -0800 (Mon, 04 Apr 2005) $' |
|
8 |
* '$Revision: 2442 $' |
|
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 |
* Drop all of the objects in proper order |
|
27 |
*/ |
|
28 |
set echo off |
|
29 |
|
|
30 |
DROP SEQUENCE xml_nodes_id_seq; |
|
31 |
DROP SEQUENCE xml_revisions_id_seq; |
|
32 |
DROP SEQUENCE xml_catalog_id_seq; |
|
33 |
DROP SEQUENCE xml_relation_id_seq; |
|
34 |
DROP SEQUENCE xml_replication_id_seq; |
|
35 |
DROP SEQUENCE xml_documents_id_seq; |
|
36 |
DROP SEQUENCE accession_number_id_seq; |
|
37 |
DROP SEQUENCE access_log_id_seq; |
|
38 |
DROP SEQUENCE xml_returnfield_id_seq; |
|
39 |
DROP SEQUENCE xml_queryresult_id_seq; |
|
40 |
|
|
41 |
/* Drop triggers are not necessary */ |
|
42 |
DROP TRIGGER xml_nodes_before_insert; |
|
43 |
DROP TRIGGER xml_revisions_before_insert; |
|
44 |
DROP TRIGGER xml_catalog_before_insert; |
|
45 |
DROP TRIGGER xml_relation_before_insert; |
|
46 |
DROP TRIGGER xml_replication_before_insert; |
|
47 |
DROP TRIGGER accession_number_before_insert; |
|
48 |
DROP TRIGGER access_log_before_insert; |
|
49 |
DROP TRIGGER xml_returnfield_before_insert; |
|
50 |
DROP TRIGGER xml_queryresult_before_insert; |
|
51 |
|
|
52 |
|
|
53 |
DROP TABLE xml_index; |
|
54 |
DROP TABLE xml_access; |
|
55 |
DROP TABLE xml_accesssubtree; |
|
56 |
DROP TABLE xml_revisions; |
|
57 |
DROP TABLE xml_relation; |
|
58 |
DROP TABLE xml_documents; |
|
59 |
DROP TABLE xml_nodes; |
|
60 |
DROP TABLE xml_replication; |
|
61 |
DROP TABLE xml_catalog; |
|
62 |
DROP TABLE accession_number; |
|
63 |
DROP TABLE access_log; |
|
64 |
DROP TABLE harvest_site_schedule; |
|
65 |
DROP TABLE harvest_detail_log; |
|
66 |
DROP TABLE harvest_log; |
|
67 |
DROP TABLE xml_queryresult; |
|
68 |
DROP TABLE xml_returnfield; |
|
69 |
|
|
70 |
/* |
|
71 |
*Replication -- table to store servers that metacat is replicated to |
|
72 |
*/ |
|
73 |
CREATE TABLE xml_replication ( |
|
74 |
serverid NUMBER(20), |
|
75 |
server VARCHAR2(512), |
|
76 |
last_checked DATE, |
|
77 |
replicate NUMBER(1), |
|
78 |
datareplicate NUMBER(1), |
|
79 |
hub NUMBER(1), |
|
80 |
CONSTRAINT xml_replication_pk PRIMARY KEY (serverid) |
|
81 |
); |
|
82 |
|
|
83 |
CREATE SEQUENCE xml_replication_id_seq; |
|
84 |
CREATE TRIGGER xml_replication_before_insert |
|
85 |
BEFORE INSERT ON xml_replication FOR EACH ROW |
|
86 |
BEGIN |
|
87 |
SELECT xml_replication_id_seq.nextval |
|
88 |
INTO :new.serverid |
|
89 |
FROM dual; |
|
90 |
END; |
|
91 |
/ |
|
92 |
|
|
93 |
INSERT INTO xml_replication (server, replicate, datareplicate, hub) |
|
94 |
VALUES ('localhost', '0', '0', '0'); |
|
95 |
|
|
96 |
/* |
|
97 |
* Nodes -- table to store XML Nodes (both elements and attributes) |
|
98 |
*/ |
|
99 |
CREATE SEQUENCE xml_nodes_id_seq; |
|
100 |
CREATE TABLE xml_nodes ( |
|
101 |
nodeid NUMBER(20), -- the unique node id (pk) |
|
102 |
nodeindex NUMBER(10), -- order of nodes within parent |
|
103 |
nodetype VARCHAR2(20), -- type (DOCUMENT, COMMENT, PI, |
|
104 |
-- ELEMENT, ATTRIBUTE, TEXT) |
|
105 |
nodename VARCHAR2(250), -- the name of an element or attribute |
|
106 |
nodeprefix VARCHAR2(50), -- the namespace prefix of an element |
|
107 |
-- or attribute |
|
108 |
nodedata VARCHAR2(4000), -- the data for this node (e.g., |
|
109 |
-- for TEXT it is the content) |
|
110 |
parentnodeid NUMBER(20), -- index of the parent of this node |
|
111 |
rootnodeid NUMBER(20), -- index of the root node of this tree |
|
112 |
docid VARCHAR2(250), -- index to the document id |
|
113 |
date_created DATE, |
|
114 |
date_updated DATE, |
|
115 |
nodedatanumerical NUMBER, -- the data for this node if |
|
116 |
-- it is a number |
|
117 |
CONSTRAINT xml_nodes_pk PRIMARY KEY (nodeid), |
|
118 |
CONSTRAINT xml_nodes_root_fk |
|
119 |
FOREIGN KEY (rootnodeid) REFERENCES xml_nodes, |
|
120 |
CONSTRAINT xml_nodes_parent_fk |
|
121 |
FOREIGN KEY (parentnodeid) REFERENCES xml_nodes |
|
122 |
); |
|
123 |
CREATE TRIGGER xml_nodes_before_insert |
|
124 |
BEFORE INSERT ON xml_nodes FOR EACH ROW |
|
125 |
BEGIN |
|
126 |
SELECT xml_nodes_id_seq.nextval |
|
127 |
INTO :new.nodeid |
|
128 |
FROM dual; |
|
129 |
END; |
|
130 |
/ |
|
131 |
|
|
132 |
/* |
|
133 |
* Indexes of rootnodeid, parentnodeid, and nodename in xml_nodes |
|
134 |
*/ |
|
135 |
CREATE INDEX xml_nodes_idx1 ON xml_nodes (rootnodeid); |
|
136 |
CREATE INDEX xml_nodes_idx2 ON xml_nodes (parentnodeid); |
|
137 |
CREATE INDEX xml_nodes_idx3 ON xml_nodes (nodename); |
|
138 |
|
|
139 |
/* |
|
140 |
* XML Catalog -- table to store all external sources for XML documents |
|
141 |
*/ |
|
142 |
CREATE TABLE xml_catalog ( |
|
143 |
catalog_id NUMBER(20), -- the id for this catalog entry |
|
144 |
entry_type VARCHAR2(500), -- the type of this catalog entry |
|
145 |
-- (e.g., DTD, XSD, XSL) |
|
146 |
source_doctype VARCHAR2(500), -- the source public_id for transforms |
|
147 |
target_doctype VARCHAR2(500), -- the target public_id for transforms |
|
148 |
public_id VARCHAR2(500), -- the unique id for this type |
|
149 |
system_id VARCHAR2(1000), -- the local location of the object |
|
150 |
CONSTRAINT xml_catalog_pk PRIMARY KEY (catalog_id), |
|
151 |
CONSTRAINT xml_catalog_uk UNIQUE |
|
152 |
(entry_type, source_doctype, target_doctype, public_id) |
|
153 |
); |
|
154 |
|
|
155 |
CREATE SEQUENCE xml_catalog_id_seq; |
|
156 |
|
|
157 |
CREATE TRIGGER xml_catalog_before_insert |
|
158 |
BEFORE INSERT ON xml_catalog FOR EACH ROW |
|
159 |
BEGIN |
|
160 |
SELECT xml_catalog_id_seq.nextval |
|
161 |
INTO :new.catalog_id |
|
162 |
FROM dual; |
|
163 |
END; |
|
164 |
/ |
|
165 |
|
|
166 |
/* |
|
167 |
* Documents -- table to store XML documents |
|
168 |
*/ |
|
169 |
CREATE TABLE xml_documents ( |
|
170 |
docid VARCHAR2(250), -- the document id # |
|
171 |
rootnodeid NUMBER(20), -- reference to root node of the DOM |
|
172 |
docname VARCHAR2(100), -- usually the root element name |
|
173 |
doctype VARCHAR2(100), -- public id indicating document type |
|
174 |
user_owner VARCHAR2(100), -- the user owned the document |
|
175 |
user_updated VARCHAR2(100), -- the user updated the document |
|
176 |
server_location NUMBER(20), -- the server on which this document |
|
177 |
-- originates |
|
178 |
rev NUMBER(10) DEFAULT 1,--the revision number of the docume |
|
179 |
date_created DATE, |
|
180 |
date_updated DATE, |
|
181 |
public_access NUMBER(1), -- flag for public access |
|
182 |
catalog_id NUMBER(20), -- reference to xml_catalog |
|
183 |
CONSTRAINT xml_documents_pk PRIMARY KEY (docid), |
|
184 |
CONSTRAINT xml_documents_rep_fk |
|
185 |
FOREIGN KEY (server_location) REFERENCES xml_replication, |
|
186 |
CONSTRAINT xml_documents_root_fk |
|
187 |
FOREIGN KEY (rootnodeid) REFERENCES xml_nodes, |
|
188 |
CONSTRAINT xml_documents_catalog_fk |
|
189 |
FOREIGN KEY (catalog_id) REFERENCES xml_catalog |
|
190 |
); |
|
191 |
|
|
192 |
/* |
|
193 |
* Index of <docid,doctype> in xml_document |
|
194 |
*/ |
|
195 |
CREATE INDEX xml_documents_idx1 ON xml_documents (docid, doctype); |
|
196 |
|
|
197 |
/* |
|
198 |
* Revised Documents -- table to store XML documents saved after an UPDATE |
|
199 |
* or DELETE |
|
200 |
*/ |
|
201 |
CREATE TABLE xml_revisions ( |
|
202 |
revisionid NUMBER(20), -- the revision number we are saving |
|
203 |
docid VARCHAR2(250), -- the document id # |
|
204 |
rootnodeid NUMBER(20), -- reference to root node of the DOM |
|
205 |
docname VARCHAR2(100), -- usually the root element name |
|
206 |
doctype VARCHAR2(100), -- public id indicating document type |
|
207 |
user_owner VARCHAR2(100), |
|
208 |
user_updated VARCHAR2(100), |
|
209 |
server_location NUMBER(20), |
|
210 |
rev NUMBER(10), |
|
211 |
date_created DATE, |
|
212 |
date_updated DATE, |
|
213 |
public_access NUMBER(1), -- flag for public access |
|
214 |
catalog_id NUMBER(20), -- reference to xml_catalog |
|
215 |
CONSTRAINT xml_revisions_pk PRIMARY KEY (revisionid), |
|
216 |
CONSTRAINT xml_revisions_rep_fk |
|
217 |
FOREIGN KEY (server_location) REFERENCES xml_replication, |
|
218 |
CONSTRAINT xml_revisions_root_fk |
|
219 |
FOREIGN KEY (rootnodeid) REFERENCES xml_nodes, |
|
220 |
CONSTRAINT xml_revisions_catalog_fk |
|
221 |
FOREIGN KEY (catalog_id) REFERENCES xml_catalog |
|
222 |
); |
|
223 |
|
|
224 |
CREATE SEQUENCE xml_revisions_id_seq; |
|
225 |
|
|
226 |
CREATE TRIGGER xml_revisions_before_insert |
|
227 |
BEFORE INSERT ON xml_revisions FOR EACH ROW |
|
228 |
BEGIN |
|
229 |
SELECT xml_revisions_id_seq.nextval |
|
230 |
INTO :new.revisionid |
|
231 |
FROM dual; |
|
232 |
END; |
|
233 |
/ |
|
234 |
|
|
235 |
/* |
|
236 |
* ACL -- table to store ACL for XML documents by principals |
|
237 |
*/ |
|
238 |
CREATE TABLE xml_access ( |
|
239 |
docid VARCHAR2(250), -- the document id # |
|
240 |
accessfileid VARCHAR2(250), -- the document id # for the access file |
|
241 |
principal_name VARCHAR2(100), -- name of user, group, etc. |
|
242 |
permission NUMBER(1), -- "read", "write", "all" |
|
243 |
perm_type VARCHAR2(32), -- "allowed" or "denied" |
|
244 |
perm_order VARCHAR2(32), -- "allow first" or "deny first" |
|
245 |
begin_time DATE, -- the time that permission begins |
|
246 |
end_time DATE, -- the time that permission ends |
|
247 |
ticket_count NUMBER(5), -- ticket counter for that permission |
|
248 |
subtreeid VARCHAR2(32), -- sub tree id |
|
249 |
startnodeid NUMBER(20), -- start node for sub tree |
|
250 |
endnodeid NUMBER(20), -- end node for sub tree |
|
251 |
CONSTRAINT xml_access_ck CHECK (begin_time < end_time), |
|
252 |
CONSTRAINT xml_access_accessfileid_fk |
|
253 |
FOREIGN KEY (accessfileid) REFERENCES xml_documents |
|
254 |
); |
|
255 |
|
|
256 |
/* |
|
257 |
* Index of Nodes -- table to store precomputed paths through tree for |
|
258 |
* quick searching in structured searches |
|
259 |
*/ |
|
260 |
CREATE TABLE xml_index ( |
|
261 |
nodeid NUMBER(20), -- the unique node id |
|
262 |
path VARCHAR2(1000), -- precomputed path through tree |
|
263 |
docid VARCHAR2(250), -- index to the document id |
|
264 |
doctype VARCHAR2(100), -- public id indicating document type |
|
265 |
parentnodeid NUMBER(20), -- id of the parent of the node |
|
266 |
-- represented by this row |
|
267 |
CONSTRAINT xml_index_pk PRIMARY KEY (nodeid,path), |
|
268 |
CONSTRAINT xml_index_nodeid_fk FOREIGN KEY (nodeid) REFERENCES xml_nodes, |
|
269 |
CONSTRAINT xml_index_docid_fk |
|
270 |
FOREIGN KEY (docid) REFERENCES xml_documents |
|
271 |
); |
|
272 |
|
|
273 |
/* |
|
274 |
* Index of the paths in xml_index |
|
275 |
*/ |
|
276 |
CREATE INDEX xml_index_idx1 ON xml_index (path); |
|
277 |
|
|
278 |
CREATE TABLE xml_relation ( |
|
279 |
relationid NUMBER(20) PRIMARY KEY, -- unique id |
|
280 |
docid VARCHAR2(250), -- the docid of the package file |
|
281 |
-- that this relation came from |
|
282 |
packagetype VARCHAR2(250), -- the type of the package |
|
283 |
subject VARCHAR2(512) NOT NULL, -- the subject of the relation |
|
284 |
subdoctype VARCHAR2(128), -- the doctype of the subject |
|
285 |
relationship VARCHAR2(128) NOT NULL,-- the relationship type |
|
286 |
object VARCHAR2(512) NOT NULL, -- the object of the relation |
|
287 |
objdoctype VARCHAR2(128), -- the doctype of the object |
|
288 |
CONSTRAINT xml_relation_uk UNIQUE (docid, subject, relationship, object), |
|
289 |
CONSTRAINT xml_relation_docid_fk |
|
290 |
FOREIGN KEY (docid) REFERENCES xml_documents |
|
291 |
); |
|
292 |
|
|
293 |
CREATE SEQUENCE xml_relation_id_seq; |
|
294 |
|
|
295 |
CREATE TRIGGER xml_relation_before_insert |
|
296 |
BEFORE INSERT ON xml_relation FOR EACH ROW |
|
297 |
BEGIN |
|
298 |
SELECT xml_relation_id_seq.nextval |
|
299 |
INTO :new.relationid |
|
300 |
FROM dual; |
|
301 |
END; |
|
302 |
/ |
|
303 |
|
|
304 |
/* |
|
305 |
* Table used as Unique ID generator for the uniqueid part of Accession# |
|
306 |
*/ |
|
307 |
CREATE SEQUENCE accession_number_id_seq; |
|
308 |
CREATE TABLE accession_number ( |
|
309 |
uniqueid NUMBER(20) PRIMARY KEY, |
|
310 |
site_code VARCHAR2(100), |
|
311 |
date_created DATE |
|
312 |
); |
|
313 |
CREATE TRIGGER accession_number_before_insert |
|
314 |
BEFORE INSERT ON accession_number FOR EACH ROW |
|
315 |
BEGIN |
|
316 |
SELECT accession_number_id_seq.nextval |
|
317 |
INTO :new.uniqueid |
|
318 |
FROM dual; |
|
319 |
END; |
|
320 |
/ |
|
321 |
|
|
322 |
/* |
|
323 |
* accesssubtree -- table to store access subtree info |
|
324 |
*/ |
|
325 |
CREATE TABLE xml_accesssubtree ( |
|
326 |
docid VARCHAR2(250), -- the document id # |
|
327 |
rev NUMBER(10) DEFAULT 1, --the revision number of the docume |
|
328 |
controllevel VARCHAR2(50), -- the level it control -- document or subtree |
|
329 |
subtreeid VARCHAR2(250), -- the subtree id |
|
330 |
startnodeid NUMBER(20), -- the start node id of access subtree |
|
331 |
endnodeid NUMBER(20), -- the end node if of access subtree |
|
332 |
CONSTRAINT xml_accesssubtree_docid_fk |
|
333 |
FOREIGN KEY (docid) REFERENCES xml_documents |
|
334 |
); |
|
335 |
|
|
336 |
/* |
|
337 |
* Returnfields -- table to store combinations of returnfields requested |
|
338 |
* and the number of times this table is accessed |
|
339 |
*/ |
|
340 |
CREATE TABLE xml_returnfield ( |
|
341 |
returnfield_id NUMBER(20), -- the id for this returnfield entry |
|
342 |
returnfield_string VARCHAR2(2000), -- the returnfield string |
|
343 |
usage_count NUMBER(20), -- the number of times this string |
|
344 |
-- has been requested |
|
345 |
CONSTRAINT xml_returnfield_pk PRIMARY KEY (returnfield_id) |
|
346 |
); |
|
347 |
CREATE INDEX xml_returnfield_idx1 ON xml_returnfield(returnfield_string); |
|
348 |
|
|
349 |
CREATE SEQUENCE xml_returnfield_id_seq; |
|
350 |
|
|
351 |
CREATE TRIGGER xml_returnfield_before_insert |
|
352 |
BEFORE INSERT ON xml_returnfield FOR EACH ROW |
|
353 |
BEGIN |
|
354 |
SELECT xml_returnfield_id_seq.nextval |
|
355 |
INTO :new.returnfield_id |
|
356 |
FROM dual; |
|
357 |
END; |
|
358 |
/ |
|
359 |
|
|
360 |
/* |
|
361 |
* Queryresults -- table to store queryresults for a given docid |
|
362 |
* and returnfield_id |
|
363 |
*/ |
|
364 |
CREATE TABLE xml_queryresult( |
|
365 |
queryresult_id NUMBER(20), -- id for this entry |
|
366 |
returnfield_id NUMBER(20), -- id for the returnfield corresponding to this entry |
|
367 |
docid VARCHAR2(250), -- docid of the document |
|
368 |
queryresult_string VARCHAR2(4000), -- resultant text generated for this docid and given |
|
369 |
-- returnfield |
|
370 |
CONSTRAINT xml_queryresult_pk PRIMARY KEY (queryresult_id), |
|
371 |
CONSTRAINT xml_queryresult_searchid_fk |
|
372 |
FOREIGN KEY (returnfield_id) REFERENCES xml_returnfield |
|
373 |
); |
|
374 |
|
|
375 |
CREATE INDEX xml_queryresult_idx1 ON xml_queryresult (returnfield_id, docid); |
|
376 |
|
|
377 |
CREATE SEQUENCE xml_queryresult_id_seq; |
|
378 |
|
|
379 |
CREATE TRIGGER xml_queryresult_before_insert |
|
380 |
BEFORE INSERT ON xml_queryresult FOR EACH ROW |
|
381 |
BEGIN |
|
382 |
SELECT xml_queryresult_id_seq.nextval |
|
383 |
INTO :new.queryresult_id |
|
384 |
FROM dual; |
|
385 |
END; |
|
386 |
/ |
|
387 |
|
|
388 |
/* |
|
389 |
* Logging -- table to store metadata and data access log |
|
390 |
*/ |
|
391 |
CREATE TABLE access_log ( |
|
392 |
entryid NUMBER(20), -- the identifier for the log event |
|
393 |
ip_address VARCHAR2(512), -- the ip address inititiating the event |
|
394 |
principal VARCHAR2(512), -- the user initiiating the event |
|
395 |
docid VARCHAR2(250), -- the document id # |
|
396 |
event VARCHAR2(512), -- the code symbolizing the event type |
|
397 |
date_logged DATE, -- the datetime on which the event occurred |
|
398 |
CONSTRAINT access_log_pk PRIMARY KEY (entryid) |
|
399 |
); |
|
400 |
|
|
401 |
CREATE SEQUENCE access_log_id_seq; |
|
402 |
CREATE TRIGGER access_log_before_insert |
|
403 |
BEFORE INSERT ON access_log FOR EACH ROW |
|
404 |
BEGIN |
|
405 |
SELECT access_log_id_seq.nextval |
|
406 |
INTO :new.entryid |
|
407 |
FROM dual; |
|
408 |
END; |
|
409 |
/ |
|
410 |
|
|
411 |
/* |
|
412 |
* harvest_site_schedule -- table to store harvest sites and schedule info |
|
413 |
*/ |
|
414 |
CREATE TABLE harvest_site_schedule ( |
|
415 |
site_schedule_id NUMBER, -- unique id |
|
416 |
documentlisturl VARCHAR2(255), -- URL of the site harvest document list |
|
417 |
ldapdn VARCHAR2(255), -- LDAP distinguished name for site account |
|
418 |
datenextharvest DATE, -- scheduled date of next harvest |
|
419 |
datelastharvest DATE, -- recorded date of last harvest |
|
420 |
updatefrequency NUMBER, -- the harvest update frequency |
|
421 |
unit VARCHAR2(50), -- update unit -- days weeks or months |
|
422 |
contact_email VARCHAR2(50), -- email address of the site contact person |
|
423 |
ldappwd VARCHAR2(20), -- LDAP password for site account |
|
424 |
CONSTRAINT harvest_site_schedule_pk PRIMARY KEY (site_schedule_id) |
|
425 |
); |
|
426 |
|
|
427 |
/* |
|
428 |
* harvest_log -- table to log entries for harvest operations |
|
429 |
*/ |
|
430 |
CREATE TABLE harvest_log ( |
|
431 |
harvest_log_id NUMBER, -- unique id |
|
432 |
harvest_date DATE, -- date of the current harvest |
|
433 |
status NUMBER, -- non-zero indicates an error status |
|
434 |
message VARCHAR2(1000), -- text message for this log entry |
|
435 |
harvest_operation_code VARCHAR2(30), -- the type of harvest operation |
|
436 |
site_schedule_id NUMBER, -- site schedule id, or 0 if no site |
|
437 |
CONSTRAINT harvest_log_pk PRIMARY KEY (harvest_log_id) |
|
438 |
); |
|
439 |
|
|
440 |
/* |
|
441 |
* harvest_detail_log -- table to log detailed info about documents that |
|
442 |
* generated errors during the harvest |
|
443 |
*/ |
|
444 |
CREATE TABLE harvest_detail_log ( |
|
445 |
detail_log_id NUMBER, -- unique id |
|
446 |
harvest_log_id NUMBER, -- ponter to the related log entry |
|
447 |
scope VARCHAR2(50), -- document scope |
|
448 |
identifier NUMBER, -- document identifier |
|
449 |
revision NUMBER, -- document revision |
|
450 |
document_url VARCHAR2(255), -- document URL |
|
451 |
error_message VARCHAR2(1000), -- text error message |
|
452 |
document_type VARCHAR2(100), -- document type |
|
453 |
CONSTRAINT harvest_detail_log_pk PRIMARY KEY (detail_log_id), |
|
454 |
CONSTRAINT harvest_detail_log_fk |
|
455 |
FOREIGN KEY (harvest_log_id) REFERENCES harvest_log |
|
456 |
); |
|
457 |
|
src/scripts/metacat-db-versions/1.7_loaddtdschema.sql | ||
---|---|---|
1 |
DELETE FROM xml_catalog |
|
2 |
WHERE entry_type LIKE 'DTD' |
|
3 |
AND public_id LIKE '%2.0.0beta6%'; |
|
4 |
DELETE FROM xml_catalog |
|
5 |
WHERE entry_type LIKE 'Schema' |
|
6 |
AND system_id LIKE '%eml%'; |
|
7 |
INSERT INTO xml_catalog (entry_type, public_id, system_id) |
|
8 |
VALUES ('DTD', '-//ecoinformatics.org//eml-access-2.0.0beta6//EN', |
|
9 |
'/dtd/eml-access-2.0.0beta6.dtd'); |
|
10 |
INSERT INTO xml_catalog (entry_type, public_id, system_id) |
|
11 |
VALUES ('DTD', '-//ecoinformatics.org//eml-attribute-2.0.0beta6//EN', |
|
12 |
'/dtd/eml-attribute-2.0.0beta6.dtd'); |
|
13 |
INSERT INTO xml_catalog (entry_type, public_id, system_id) |
|
14 |
VALUES ('DTD', '-//ecoinformatics.org//eml-constraint-2.0.0beta6//EN', |
|
15 |
'/dtd/eml-constraint-2.0.0beta6.dtd'); |
|
16 |
INSERT INTO xml_catalog (entry_type, public_id, system_id) |
|
17 |
VALUES ('DTD', '-//ecoinformatics.org//eml-coverage-2.0.0beta6//EN', |
|
18 |
'/dtd/eml-coverage-2.0.0beta6.dtd'); |
|
19 |
INSERT INTO xml_catalog (entry_type, public_id, system_id) |
|
20 |
VALUES ('DTD', '-//ecoinformatics.org//eml-dataset-2.0.0beta6//EN', |
|
21 |
'/dtd/eml-dataset-2.0.0beta6.dtd'); |
|
22 |
INSERT INTO xml_catalog (entry_type, public_id, system_id) |
|
23 |
VALUES ('DTD', '-//ecoinformatics.org//eml-entity-2.0.0beta6//EN', |
|
24 |
'/dtd/eml-entity-2.0.0beta6.dtd'); |
|
25 |
INSERT INTO xml_catalog (entry_type, public_id, system_id) |
|
26 |
VALUES ('DTD', '-//ecoinformatics.org//eml-literature-2.0.0beta6//EN', |
|
27 |
'/dtd/eml-literature-2.0.0beta6.dtd'); |
|
28 |
INSERT INTO xml_catalog (entry_type, public_id, system_id) |
|
29 |
VALUES ('DTD', '-//ecoinformatics.org//eml-physical-2.0.0beta6//EN', |
|
30 |
'/dtd/eml-physical-2.0.0beta6.dtd'); |
|
31 |
INSERT INTO xml_catalog (entry_type, public_id, system_id) |
|
32 |
VALUES ('DTD', '-//ecoinformatics.org//eml-project-2.0.0beta6//EN', |
|
33 |
'/dtd/eml-project-2.0.0beta6.dtd'); |
|
34 |
INSERT INTO xml_catalog (entry_type, public_id, system_id) |
|
35 |
VALUES ('DTD', '-//ecoinformatics.org//eml-protocol-2.0.0beta6//EN', |
|
36 |
'/dtd/eml-protocol-2.0.0beta6.dtd'); |
|
37 |
INSERT INTO xml_catalog (entry_type, public_id, system_id) |
|
38 |
VALUES ('DTD', '-//ecoinformatics.org//eml-software-2.0.0beta6//EN', |
|
39 |
'/dtd/eml-software-2.0.0beta6.dtd'); |
|
40 |
INSERT INTO xml_catalog (entry_type, public_id, system_id) |
|
41 |
VALUES ('Schema', 'eml://ecoinformatics.org/eml-2.0.1', '/schema/eml-2.0.1/eml.xsd'); |
|
42 |
INSERT INTO xml_catalog (entry_type, public_id, system_id) |
|
43 |
VALUES ('Schema', 'eml://ecoinformatics.org/eml-2.0.0', '/schema/eml-2.0.0/eml.xsd'); |
|
44 |
INSERT INTO xml_catalog (entry_type, public_id, system_id) |
|
45 |
VALUES ('Schema', 'http://www.xml-cml.org/schema/stmml', '/schema/eml-2.0.1/stmml.xsd'); |
|
46 |
INSERT INTO xml_catalog (entry_type, public_id, system_id) |
|
47 |
VALUES ('Schema', 'metadata', '/schema/fgdc-std-001/fgdc-std-001-1998.xsd'); |
src/scripts/metacat-db-versions/1.8_loaddtdschema.sql | ||
---|---|---|
1 |
DELETE FROM xml_catalog |
|
2 |
WHERE entry_type LIKE 'DTD' |
|
3 |
AND public_id LIKE '%2.0.0beta6%'; |
|
4 |
DELETE FROM xml_catalog |
|
5 |
WHERE entry_type LIKE 'Schema' |
|
6 |
AND system_id LIKE '%eml%'; |
|
7 |
INSERT INTO xml_catalog (entry_type, public_id, system_id) |
|
8 |
VALUES ('DTD', '-//ecoinformatics.org//eml-access-2.0.0beta6//EN', |
|
9 |
'/dtd/eml-access-2.0.0beta6.dtd'); |
|
10 |
INSERT INTO xml_catalog (entry_type, public_id, system_id) |
|
11 |
VALUES ('DTD', '-//ecoinformatics.org//eml-attribute-2.0.0beta6//EN', |
|
12 |
'/dtd/eml-attribute-2.0.0beta6.dtd'); |
|
13 |
INSERT INTO xml_catalog (entry_type, public_id, system_id) |
|
14 |
VALUES ('DTD', '-//ecoinformatics.org//eml-constraint-2.0.0beta6//EN', |
|
15 |
'/dtd/eml-constraint-2.0.0beta6.dtd'); |
|
16 |
INSERT INTO xml_catalog (entry_type, public_id, system_id) |
|
17 |
VALUES ('DTD', '-//ecoinformatics.org//eml-coverage-2.0.0beta6//EN', |
|
18 |
'/dtd/eml-coverage-2.0.0beta6.dtd'); |
|
19 |
INSERT INTO xml_catalog (entry_type, public_id, system_id) |
|
20 |
VALUES ('DTD', '-//ecoinformatics.org//eml-dataset-2.0.0beta6//EN', |
|
21 |
'/dtd/eml-dataset-2.0.0beta6.dtd'); |
|
22 |
INSERT INTO xml_catalog (entry_type, public_id, system_id) |
|
23 |
VALUES ('DTD', '-//ecoinformatics.org//eml-entity-2.0.0beta6//EN', |
|
24 |
'/dtd/eml-entity-2.0.0beta6.dtd'); |
|
25 |
INSERT INTO xml_catalog (entry_type, public_id, system_id) |
|
26 |
VALUES ('DTD', '-//ecoinformatics.org//eml-literature-2.0.0beta6//EN', |
|
27 |
'/dtd/eml-literature-2.0.0beta6.dtd'); |
|
28 |
INSERT INTO xml_catalog (entry_type, public_id, system_id) |
|
29 |
VALUES ('DTD', '-//ecoinformatics.org//eml-physical-2.0.0beta6//EN', |
|
30 |
'/dtd/eml-physical-2.0.0beta6.dtd'); |
|
31 |
INSERT INTO xml_catalog (entry_type, public_id, system_id) |
|
32 |
VALUES ('DTD', '-//ecoinformatics.org//eml-project-2.0.0beta6//EN', |
|
33 |
'/dtd/eml-project-2.0.0beta6.dtd'); |
|
34 |
INSERT INTO xml_catalog (entry_type, public_id, system_id) |
|
35 |
VALUES ('DTD', '-//ecoinformatics.org//eml-protocol-2.0.0beta6//EN', |
|
36 |
'/dtd/eml-protocol-2.0.0beta6.dtd'); |
|
37 |
INSERT INTO xml_catalog (entry_type, public_id, system_id) |
|
38 |
VALUES ('DTD', '-//ecoinformatics.org//eml-software-2.0.0beta6//EN', |
|
39 |
'/dtd/eml-software-2.0.0beta6.dtd'); |
|
40 |
INSERT INTO xml_catalog (entry_type, public_id, system_id) |
|
41 |
VALUES ('Schema', 'eml://ecoinformatics.org/eml-2.0.1', '/schema/eml-2.0.1/eml.xsd'); |
|
42 |
INSERT INTO xml_catalog (entry_type, public_id, system_id) |
|
43 |
VALUES ('Schema', 'eml://ecoinformatics.org/eml-2.0.0', '/schema/eml-2.0.0/eml.xsd'); |
|
44 |
INSERT INTO xml_catalog (entry_type, public_id, system_id) |
|
45 |
VALUES ('Schema', 'http://www.xml-cml.org/schema/stmml', '/schema/eml-2.0.1/stmml.xsd'); |
|
46 |
INSERT INTO xml_catalog (entry_type, public_id, system_id) |
|
47 |
VALUES ('Schema', 'metadata', '/schema/fgdc-std-001/fgdc-std-001-1998.xsd'); |
src/scripts/metacat-db-versions/1.5_xmltables-postgres.sql | ||
---|---|---|
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-04-04 09:12:31 -0800 (Mon, 04 Apr 2005) $' |
|
8 |
* '$Revision: 2442 $' |
|
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 |
* this is sql script does the same as the sql script named |
|
27 |
* xmltables.sql except that this script is to be use to |
|
28 |
* create the database tables on a Postgresql backend rather |
|
29 |
* than an Oracle Backend |
|
30 |
*/ |
|
31 |
|
|
32 |
/* |
|
33 |
*Replication -- table to store servers that metacat is replicated to |
|
34 |
*/ |
|
35 |
CREATE SEQUENCE xml_replication_id_seq; |
|
36 |
CREATE TABLE xml_replication ( |
|
37 |
serverid INT8 default nextval('xml_replication_id_seq'), |
|
38 |
server VARCHAR(512), |
|
39 |
last_checked DATE, |
|
40 |
replicate INT8, |
|
41 |
datareplicate INT8, |
|
42 |
hub INT8, |
|
43 |
CONSTRAINT xml_replication_pk PRIMARY KEY (serverid) |
|
44 |
); |
|
45 |
|
|
46 |
INSERT INTO xml_replication (server, replicate, datareplicate, hub) VALUES ('localhost', '0', '0', '0'); |
|
47 |
|
|
48 |
/* |
|
49 |
* Nodes -- table to store XML Nodes (both elements and attributes) |
|
50 |
*/ |
|
51 |
CREATE SEQUENCE xml_nodes_id_seq; |
|
52 |
CREATE TABLE xml_nodes ( |
|
53 |
nodeid INT8 default nextval('xml_nodes_id_seq'), |
|
54 |
-- the unique node id (pk) |
|
55 |
nodeindex INT8, -- order of nodes within parent |
|
56 |
nodetype VARCHAR(20), -- type (DOCUMENT, COMMENT, PI, |
|
57 |
-- ELEMENT, ATTRIBUTE, TEXT) |
|
58 |
nodename VARCHAR(250), -- the name of an element or attribute |
|
59 |
nodeprefix VARCHAR(50), -- the namespace prefix of the node |
|
60 |
nodedata VARCHAR(4000), -- the data for this node (e.g., |
|
61 |
-- for TEXT it is the content) |
|
62 |
parentnodeid INT8, -- index of the parent of this node |
|
63 |
rootnodeid INT8, -- index of the root node of this tree |
|
64 |
docid VARCHAR(250), -- index to the document id |
|
65 |
date_created DATE, |
|
66 |
date_updated DATE, |
|
67 |
nodedatanumerical FLOAT8, -- the data for this node if |
|
68 |
-- if it is a number |
|
69 |
CONSTRAINT xml_nodes_pk PRIMARY KEY (nodeid), |
|
70 |
CONSTRAINT xml_nodes_root_fk |
|
71 |
FOREIGN KEY (rootnodeid) REFERENCES xml_nodes, |
|
72 |
CONSTRAINT xml_nodes_parent_fk |
|
73 |
FOREIGN KEY (parentnodeid) REFERENCES xml_nodes |
|
74 |
); |
|
75 |
/* |
|
76 |
* Indexes of rootnodeid, parentnodeid, and nodename in xml_nodes |
|
77 |
*/ |
|
78 |
CREATE INDEX xml_nodes_idx1 ON xml_nodes (rootnodeid); |
|
79 |
CREATE INDEX xml_nodes_idx2 ON xml_nodes (parentnodeid); |
|
80 |
CREATE INDEX xml_nodes_idx3 ON xml_nodes (nodename); |
|
81 |
|
|
82 |
/* |
|
83 |
* XML Catalog -- table to store all external sources for XML documents |
|
84 |
*/ |
|
85 |
CREATE SEQUENCE xml_catalog_id_seq; |
|
86 |
CREATE TABLE xml_catalog ( |
|
87 |
catalog_id INT8 default nextval('xml_catalog_id_seq'), |
|
88 |
-- the id for this catalog entry |
|
89 |
entry_type VARCHAR(500), -- the type of this catalog entry |
|
90 |
-- (e.g., DTD, XSD, XSL) |
|
91 |
source_doctype VARCHAR(500), -- the source public_id for transforms |
|
92 |
target_doctype VARCHAR(500), -- the target public_id for transforms |
|
93 |
public_id VARCHAR(500), -- the unique id for this type |
|
94 |
system_id VARCHAR(1000), -- the local location of the object |
|
95 |
CONSTRAINT xml_catalog_pk PRIMARY KEY (catalog_id), |
|
96 |
CONSTRAINT xml_catalog_uk UNIQUE |
|
97 |
(entry_type, source_doctype, target_doctype, public_id) |
|
98 |
); |
|
99 |
|
|
100 |
/* |
|
101 |
* Sequence to get uniqueID for Accession # |
|
102 |
*/ |
|
103 |
CREATE SEQUENCE xml_documents_id_seq; |
|
104 |
/* |
|
105 |
* Documents -- table to store XML documents |
|
106 |
*/ |
|
107 |
CREATE TABLE xml_documents ( |
|
108 |
docid VARCHAR(250), -- the document id # |
|
109 |
rootnodeid INT8, -- reference to root node of the DOM |
|
110 |
docname VARCHAR(100), -- usually the root element name |
|
111 |
doctype VARCHAR(100), -- public id indicating document type |
|
112 |
user_owner VARCHAR(100), -- the user owned the document |
|
113 |
user_updated VARCHAR(100), -- the user updated the document |
|
114 |
server_location INT8, -- the server on which this document resides |
|
115 |
rev INT8 default 1, -- the revision number of the document |
|
116 |
date_created DATE, |
|
117 |
date_updated DATE, |
|
118 |
public_access INT8, -- flag for public access |
|
119 |
catalog_id INT8, -- reference to xml_catalog |
|
120 |
CONSTRAINT xml_documents_pk PRIMARY KEY (docid), |
|
121 |
CONSTRAINT xml_documents_rep_fk |
|
122 |
FOREIGN KEY (server_location) REFERENCES xml_replication, |
|
123 |
CONSTRAINT xml_documents_root_fk |
|
124 |
FOREIGN KEY (rootnodeid) REFERENCES xml_nodes, |
|
125 |
CONSTRAINT xml_documents_catalog_fk |
|
126 |
FOREIGN KEY (catalog_id) REFERENCES xml_catalog |
|
127 |
); |
|
128 |
|
|
129 |
/* |
|
130 |
* Index of <docid,doctype> in xml_document |
|
131 |
*/ |
|
132 |
CREATE INDEX xml_documents_idx1 ON xml_documents (docid, doctype); |
|
133 |
|
|
134 |
/* |
|
135 |
* Revised Documents -- table to store XML documents saved after an UPDATE |
|
136 |
* or DELETE |
|
137 |
*/ |
|
138 |
CREATE SEQUENCE xml_revisions_id_seq; |
|
139 |
CREATE TABLE xml_revisions ( |
|
140 |
revisionid INT8 default nextval('xml_revisions_id_seq'), |
|
141 |
-- the revision number we are saving |
|
142 |
docid VARCHAR(250), -- the document id # |
|
143 |
rootnodeid INT8, -- reference to root node of the DOM |
|
144 |
docname VARCHAR(100), -- usually the root element name |
|
145 |
doctype VARCHAR(100), -- public id indicating document type |
|
146 |
user_owner VARCHAR(100), |
|
147 |
user_updated VARCHAR(100), |
|
148 |
server_location INT8, |
|
149 |
rev INT8, |
|
150 |
date_created DATE, |
|
151 |
date_updated DATE, |
|
152 |
public_access INT8, -- flag for public access |
|
153 |
catalog_id INT8, -- reference to xml_catalog |
|
154 |
CONSTRAINT xml_revisions_pk PRIMARY KEY (revisionid), |
|
155 |
CONSTRAINT xml_revisions_rep_fk |
|
156 |
FOREIGN KEY (server_location) REFERENCES xml_replication, |
|
157 |
CONSTRAINT xml_revisions_root_fk |
|
158 |
FOREIGN KEY (rootnodeid) REFERENCES xml_nodes, |
|
159 |
CONSTRAINT xml_revisions_catalog_fk |
|
160 |
FOREIGN KEY (catalog_id) REFERENCES xml_catalog |
|
161 |
); |
|
162 |
|
|
163 |
|
|
164 |
/* |
|
165 |
* ACL -- table to store ACL for XML documents by principals |
|
166 |
*/ |
|
167 |
CREATE TABLE xml_access ( |
|
168 |
docid VARCHAR(250), -- the document id # |
|
169 |
accessfileid VARCHAR(250), -- the document id # for the access file |
|
170 |
principal_name VARCHAR(100), -- name of user, group, etc. |
|
171 |
permission INT8, -- "read", "write", "all" |
|
172 |
perm_type VARCHAR(32), -- "allowed" or "denied" |
|
173 |
perm_order VARCHAR(32), -- "allow first" or "deny first" |
|
174 |
begin_time DATE, -- the time that permission begins |
|
175 |
end_time DATE, -- the time that permission ends |
|
176 |
ticket_count INT8, -- ticket counter for that permission |
|
177 |
subtreeid VARCHAR(32), |
|
178 |
startnodeid INT8, |
|
179 |
endnodeid INT8, |
|
180 |
CONSTRAINT xml_access_ck CHECK (begin_time < end_time), |
|
181 |
CONSTRAINT xml_access_accessfileid_fk |
|
182 |
FOREIGN KEY (accessfileid) REFERENCES xml_documents |
|
183 |
); |
|
184 |
|
|
185 |
/* |
|
186 |
* Index of Nodes -- table to store precomputed paths through tree for |
|
187 |
* quick searching in structured searches |
|
188 |
*/ |
|
189 |
CREATE TABLE xml_index ( |
|
190 |
nodeid INT8, -- the unique node id |
|
191 |
path VARCHAR(1000), -- precomputed path through tree |
|
192 |
docid VARCHAR(250), -- index to the document id |
|
193 |
doctype VARCHAR(100), -- public id indicating document type |
|
194 |
parentnodeid INT8, -- id of the parent of the node represented |
|
195 |
-- by this row |
|
196 |
CONSTRAINT xml_index_pk PRIMARY KEY (nodeid,path), |
|
197 |
CONSTRAINT xml_index_nodeid_fk FOREIGN KEY (nodeid) REFERENCES xml_nodes, |
|
198 |
CONSTRAINT xml_index_docid_fk |
|
199 |
FOREIGN KEY (docid) REFERENCES xml_documents |
|
200 |
); |
|
201 |
|
|
202 |
/* |
|
203 |
* Index of the paths in xml_index |
|
204 |
*/ |
|
205 |
CREATE INDEX xml_index_idx1 ON xml_index (path); |
|
206 |
|
|
207 |
CREATE SEQUENCE xml_relation_id_seq; |
|
208 |
CREATE TABLE xml_relation ( |
|
209 |
relationid INT8 default nextval('xml_relation_id_seq') PRIMARY KEY, |
|
210 |
-- unique id |
|
211 |
docid VARCHAR(250) , -- the docid of the package file |
|
212 |
-- that this relation came from |
|
213 |
packagetype VARCHAR(250), -- the type of the package |
|
214 |
subject VARCHAR(512) NOT NULL, -- the subject of the relation |
|
215 |
subdoctype VARCHAR(128), -- the doctype of the subject |
|
216 |
relationship VARCHAR(128) NOT NULL,-- the relationship type |
|
217 |
object VARCHAR(512) NOT NULL, -- the object of the relation |
|
218 |
objdoctype VARCHAR(128), -- the doctype of the object |
|
219 |
CONSTRAINT xml_relation_uk UNIQUE (docid, subject, relationship, object), |
|
220 |
CONSTRAINT xml_relation_docid_fk |
|
221 |
FOREIGN KEY (docid) REFERENCES xml_documents |
|
222 |
); |
|
223 |
|
|
224 |
/* |
|
225 |
* Table used as Unique ID generator for the uniqueid part of Accession# |
|
226 |
*/ |
|
227 |
CREATE SEQUENCE accession_number_id_seq; |
|
228 |
CREATE TABLE accession_number ( |
|
229 |
uniqueid INT8 default nextval('accession_number_id_seq') PRIMARY KEY, |
|
230 |
site_code VARCHAR(100), |
|
231 |
date_created DATE |
|
232 |
); |
|
233 |
|
|
234 |
/* |
|
235 |
* accesssubtree -- table to store access subtree info |
|
236 |
*/ |
|
237 |
CREATE TABLE xml_accesssubtree ( |
|
238 |
docid VARCHAR(250), -- the document id # |
|
239 |
rev INT8 default 1, --the revision number of the docume |
|
240 |
controllevel VARCHAR(50), -- the level it control -- document or subtree |
|
241 |
subtreeid VARCHAR(250), -- the subtree id |
|
242 |
startnodeid INT8, -- the start node id of access subtree |
|
243 |
endnodeid INT8, -- the end node if of access subtree |
|
244 |
CONSTRAINT xml_accesssubtree_docid_fk |
|
245 |
FOREIGN KEY (docid) REFERENCES xml_documents |
|
246 |
); |
|
247 |
|
|
248 |
/* |
|
249 |
* Returnfields -- table to store combinations of returnfields requested |
|
250 |
* and the number of times this table is accessed |
|
251 |
*/ |
|
252 |
CREATE TABLE xml_returnfield ( |
|
253 |
returnfield_id INT8 default nextval('xml_returnfield_id_seq'), -- the id for this returnfield entry |
|
254 |
returnfield_string VARCHAR(2000), -- the returnfield string |
|
255 |
usage_count INT8, -- the number of times this string has been requested |
|
256 |
CONSTRAINT xml_returnfield_pk PRIMARY KEY (returnfield_id) |
|
257 |
); |
|
258 |
CREATE INDEX xml_returnfield_idx1 ON xml_returnfield(returnfield_string); |
|
259 |
|
|
260 |
CREATE SEQUENCE xml_returnfield_id_seq; |
|
261 |
|
|
262 |
/* |
|
263 |
* Queryresults -- table to store queryresults for a given docid |
|
264 |
* and returnfield_id |
|
265 |
*/ |
|
266 |
CREATE TABLE xml_queryresult( |
|
267 |
queryresult_id INT8 default nextval('xml_queryresult_id_seq'), -- id for this entry |
|
268 |
returnfield_id INT8, -- id for the returnfield corresponding to this entry |
|
269 |
docid VARCHAR(250), -- docid of the document |
|
270 |
queryresult_string VARCHAR(4000), -- resultant text generated for this docid and given |
|
271 |
-- returnfield |
|
272 |
CONSTRAINT xml_queryresult_pk PRIMARY KEY (queryresult_id), |
|
273 |
CONSTRAINT xml_queryresult_searchid_fk |
|
274 |
FOREIGN KEY (returnfield_id) REFERENCES xml_returnfield |
|
275 |
); |
|
276 |
|
|
277 |
CREATE INDEX xml_queryresult_idx1 ON xml_queryresult (returnfield_id, docid); |
|
278 |
|
|
279 |
CREATE SEQUENCE xml_queryresult_id_seq; |
|
280 |
|
|
281 |
/* |
|
282 |
* Logging -- table to store metadata and data access log |
|
283 |
*/ |
|
284 |
CREATE SEQUENCE access_log_id_seq; |
|
285 |
CREATE TABLE access_log ( |
|
286 |
entryid INT8 default nextval ('access_log_id_seq'), -- the identifier for the log event |
|
287 |
ip_address VARCHAR(512), -- the ip address inititiating the event |
|
288 |
principal VARCHAR(512), -- the user initiiating the event |
|
289 |
docid VARCHAR(250), -- the document id # |
|
290 |
event VARCHAR(512), -- the code symbolizing the event type |
|
291 |
date_logged TIMESTAMP, -- the datetime on which the event occurred |
|
292 |
CONSTRAINT access_log_pk PRIMARY KEY (entryid) |
|
293 |
); |
|
294 |
|
|
295 |
/* |
|
296 |
* harvest_site_schedule -- table to store harvest sites and schedule info |
|
297 |
*/ |
|
298 |
CREATE TABLE harvest_site_schedule ( |
|
299 |
site_schedule_id INT8, -- unique id |
|
300 |
documentlisturl VARCHAR(255), -- URL of the site harvest document list |
|
301 |
ldapdn VARCHAR(255), -- LDAP distinguished name for site account |
|
302 |
datenextharvest DATE, -- scheduled date of next harvest |
|
303 |
datelastharvest DATE, -- recorded date of last harvest |
|
304 |
updatefrequency INT8, -- the harvest update frequency |
|
305 |
unit VARCHAR(50), -- update unit -- days weeks or months |
|
306 |
contact_email VARCHAR(50), -- email address of the site contact person |
|
307 |
ldappwd VARCHAR(20), -- LDAP password for site account |
|
308 |
CONSTRAINT harvest_site_schedule_pk PRIMARY KEY (site_schedule_id) |
|
309 |
); |
|
310 |
|
|
311 |
/* |
|
312 |
* harvest_log -- table to log entries for harvest operations |
|
313 |
*/ |
|
314 |
CREATE TABLE harvest_log ( |
|
315 |
harvest_log_id INT8, -- unique id |
|
316 |
harvest_date DATE, -- date of the current harvest |
|
317 |
status INT8, -- non-zero indicates an error status |
|
318 |
message VARCHAR(1000), -- text message for this log entry |
|
319 |
harvest_operation_code VARCHAR(30), -- the type of harvest operation |
|
320 |
site_schedule_id INT8, -- site schedule id, or 0 if no site |
|
321 |
CONSTRAINT harvest_log_pk PRIMARY KEY (harvest_log_id) |
|
322 |
); |
|
323 |
|
|
324 |
/* |
|
325 |
* harvest_detail_log -- table to log detailed info about documents that |
|
326 |
* generated errors during the harvest |
|
327 |
*/ |
|
328 |
CREATE TABLE harvest_detail_log ( |
|
329 |
detail_log_id INT8, -- unique id |
|
330 |
harvest_log_id INT8, -- ponter to the related log entry |
|
331 |
scope VARCHAR(50), -- document scope |
|
332 |
identifier INT8, -- document identifier |
|
333 |
revision INT8, -- document revision |
|
334 |
document_url VARCHAR(255), -- document URL |
|
335 |
error_message VARCHAR(1000), -- text error message |
|
336 |
document_type VARCHAR(100), -- document type |
|
337 |
CONSTRAINT harvest_detail_log_pk PRIMARY KEY (detail_log_id), |
|
338 |
CONSTRAINT harvest_detail_log_fk |
|
339 |
FOREIGN KEY (harvest_log_id) REFERENCES harvest_log |
|
340 |
); |
|
341 |
|
src/scripts/metacat-db-versions/1.6_xmltables-oracle.sql | ||
---|---|---|
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 |
* Drop all of the objects in proper order |
|
27 |
*/ |
|
28 |
set echo off |
|
29 |
|
|
30 |
DROP SEQUENCE xml_nodes_id_seq; |
|
31 |
DROP SEQUENCE xml_revisions_id_seq; |
|
32 |
DROP SEQUENCE xml_catalog_id_seq; |
|
33 |
DROP SEQUENCE xml_relation_id_seq; |
|
34 |
DROP SEQUENCE xml_replication_id_seq; |
|
35 |
DROP SEQUENCE identifier_id_seq; |
|
36 |
DROP SEQUENCE access_log_id_seq; |
|
37 |
DROP SEQUENCE xml_returnfield_id_seq; |
|
38 |
DROP SEQUENCE xml_queryresult_id_seq; |
|
39 |
DROP SEQUENCE xml_path_index_id_seq; |
|
40 |
|
|
41 |
/* Drop triggers are not necessary */ |
|
42 |
DROP TRIGGER xml_nodes_before_insert; |
|
43 |
DROP TRIGGER xml_revisions_before_insert; |
|
44 |
DROP TRIGGER xml_catalog_before_insert; |
|
45 |
DROP TRIGGER xml_relation_before_insert; |
|
46 |
DROP TRIGGER xml_replication_before_insert; |
|
47 |
DROP TRIGGER identifier_before_insert; |
|
48 |
DROP TRIGGER access_log_before_insert; |
|
49 |
DROP TRIGGER xml_returnfield_before_insert; |
|
50 |
DROP TRIGGER xml_queryresult_before_insert; |
|
51 |
|
|
52 |
|
|
53 |
DROP TABLE xml_index; |
|
54 |
DROP TABLE xml_access; |
|
55 |
DROP TABLE xml_accesssubtree; |
|
56 |
DROP TABLE xml_revisions; |
|
57 |
DROP TABLE xml_relation; |
|
58 |
DROP TABLE xml_documents CASCADE CONSTRAINTS; |
|
59 |
DROP TABLE xml_nodes; |
|
60 |
DROP TABLE xml_nodes_revisions; |
|
61 |
DROP TABLE xml_replication; |
|
62 |
DROP TABLE xml_catalog; |
|
63 |
DROP TABLE identifier; |
|
64 |
DROP TABLE access_log; |
|
65 |
DROP TABLE harvest_site_schedule; |
|
66 |
DROP TABLE harvest_detail_log; |
|
67 |
DROP TABLE harvest_log; |
|
68 |
DROP TABLE xml_queryresult; |
|
69 |
DROP TABLE xml_returnfield; |
|
70 |
DROP TABLE xml_path_index; |
|
71 |
|
|
72 |
/* |
|
73 |
*Replication -- table to store servers that metacat is replicated to |
|
74 |
*/ |
|
75 |
CREATE TABLE xml_replication ( |
|
76 |
serverid NUMBER(20), |
|
77 |
server VARCHAR2(512), |
|
78 |
last_checked DATE, |
|
79 |
replicate NUMBER(1), |
|
80 |
datareplicate NUMBER(1), |
|
81 |
hub NUMBER(1), |
|
82 |
CONSTRAINT xml_replication_pk PRIMARY KEY (serverid) |
|
83 |
); |
|
84 |
|
|
85 |
CREATE SEQUENCE xml_replication_id_seq; |
|
86 |
CREATE TRIGGER xml_replication_before_insert |
|
87 |
BEFORE INSERT ON xml_replication FOR EACH ROW |
|
88 |
BEGIN |
|
89 |
SELECT xml_replication_id_seq.nextval |
|
90 |
INTO :new.serverid |
|
91 |
FROM dual; |
|
92 |
END; |
|
93 |
/ |
|
94 |
|
|
95 |
INSERT INTO xml_replication (server, replicate, datareplicate, hub) |
|
96 |
VALUES ('localhost', '0', '0', '0'); |
|
97 |
|
|
98 |
/* |
|
99 |
* Nodes -- table to store XML Nodes (both elements and attributes) |
|
100 |
*/ |
|
101 |
CREATE SEQUENCE xml_nodes_id_seq; |
|
102 |
CREATE TABLE xml_nodes ( |
|
103 |
nodeid NUMBER(20), -- the unique node id (pk) |
|
104 |
nodeindex NUMBER(10), -- order of nodes within parent |
|
105 |
nodetype VARCHAR2(20), -- type (DOCUMENT, COMMENT, PI, |
|
106 |
-- ELEMENT, ATTRIBUTE, TEXT) |
|
107 |
nodename VARCHAR2(250), -- the name of an element or attribute |
|
108 |
nodeprefix VARCHAR2(50), -- the namespace prefix of an element |
|
109 |
-- or attribute |
|
110 |
nodedata VARCHAR2(4000), -- the data for this node (e.g., |
|
111 |
-- for TEXT it is the content) |
|
112 |
parentnodeid NUMBER(20), -- index of the parent of this node |
|
113 |
rootnodeid NUMBER(20), -- index of the root node of this tree |
|
114 |
docid VARCHAR2(250), -- index to the document id |
|
115 |
date_created DATE, |
|
116 |
date_updated DATE, |
|
117 |
nodedatanumerical NUMBER, -- the data for this node if |
|
118 |
-- it is a number |
|
119 |
CONSTRAINT xml_nodes_pk PRIMARY KEY (nodeid), |
|
120 |
CONSTRAINT xml_nodes_root_fk |
|
121 |
FOREIGN KEY (rootnodeid) REFERENCES xml_nodes, |
|
122 |
CONSTRAINT xml_nodes_parent_fk |
|
123 |
FOREIGN KEY (parentnodeid) REFERENCES xml_nodes |
|
124 |
); |
|
125 |
CREATE TRIGGER xml_nodes_before_insert |
|
126 |
BEFORE INSERT ON xml_nodes FOR EACH ROW |
|
127 |
BEGIN |
|
128 |
SELECT xml_nodes_id_seq.nextval |
|
129 |
INTO :new.nodeid |
|
130 |
FROM dual; |
|
131 |
END; |
|
132 |
/ |
|
133 |
|
|
134 |
|
|
135 |
/* |
|
136 |
* Indexes of rootnodeid, parentnodeid, and nodename in xml_nodes |
|
137 |
*/ |
|
138 |
CREATE INDEX xml_nodes_idx1 ON xml_nodes (rootnodeid); |
|
139 |
CREATE INDEX xml_nodes_idx2 ON xml_nodes (parentnodeid); |
|
140 |
CREATE INDEX xml_nodes_idx3 ON xml_nodes (nodename); |
|
141 |
|
|
142 |
|
|
143 |
/* |
|
144 |
* xml_nodes_revisions -- table to store nodes from xml_nodes which are of old revisions and deleted document |
|
145 |
*/ |
|
146 |
|
|
147 |
CREATE TABLE xml_nodes_revisions ( |
|
148 |
nodeid NUMBER(20), -- the unique node id (pk) |
|
149 |
nodeindex NUMBER(10), -- order of nodes within parent |
|
150 |
nodetype VARCHAR2(20), -- type (DOCUMENT, COMMENT, PI, |
|
151 |
-- ELEMENT, ATTRIBUTE, TEXT) |
|
152 |
nodename VARCHAR2(250), -- the name of an element or attribute |
|
153 |
nodeprefix VARCHAR2(50), -- the namespace prefix of an element |
|
154 |
-- or attribute |
|
155 |
nodedata VARCHAR2(4000), -- the data for this node (e.g., |
|
156 |
-- for TEXT it is the content) |
|
157 |
parentnodeid NUMBER(20), -- index of the parent of this node |
|
158 |
rootnodeid NUMBER(20), -- index of the root node of this tree |
|
159 |
docid VARCHAR2(250), -- index to the document id |
|
160 |
date_created DATE, |
|
161 |
date_updated DATE, |
|
162 |
nodedatanumerical NUMBER, -- the data for this node if |
|
163 |
-- it is a number |
|
164 |
CONSTRAINT xml_nodes_revisions_pk PRIMARY KEY (nodeid), |
|
165 |
CONSTRAINT xml_nodes_revisions_root_fk |
|
166 |
FOREIGN KEY (rootnodeid) REFERENCES xml_nodes_revisions, |
|
167 |
CONSTRAINT xml_nodes_revisions_parent_fk |
|
168 |
FOREIGN KEY (parentnodeid) REFERENCES xml_nodes_revisions |
|
169 |
); |
|
170 |
|
|
171 |
|
|
172 |
/* |
|
173 |
* Indexes of rootnodeid, parentnodeid, and nodename in xml_nodes_revision |
|
174 |
*/ |
|
175 |
CREATE INDEX xml_nodes_revisions_idx1 ON xml_nodes_revisions (rootnodeid); |
|
176 |
CREATE INDEX xml_nodes_revisions_idx2 ON xml_nodes_revisions (parentnodeid); |
|
177 |
CREATE INDEX xml_nodes_revisions_idx3 ON xml_nodes_revisions (nodename); |
|
178 |
|
|
179 |
/* |
|
180 |
* XML Catalog -- table to store all external sources for XML documents |
|
181 |
*/ |
|
182 |
CREATE TABLE xml_catalog ( |
|
183 |
catalog_id NUMBER(20), -- the id for this catalog entry |
|
184 |
entry_type VARCHAR2(500), -- the type of this catalog entry |
|
185 |
-- (e.g., DTD, XSD, XSL) |
|
186 |
source_doctype VARCHAR2(500), -- the source public_id for transforms |
|
187 |
target_doctype VARCHAR2(500), -- the target public_id for transforms |
|
188 |
public_id VARCHAR2(500), -- the unique id for this type |
|
189 |
system_id VARCHAR2(1000), -- the local location of the object |
|
190 |
CONSTRAINT xml_catalog_pk PRIMARY KEY (catalog_id), |
|
191 |
CONSTRAINT xml_catalog_uk UNIQUE |
|
192 |
(entry_type, source_doctype, target_doctype, public_id) |
|
193 |
); |
|
194 |
|
|
195 |
CREATE SEQUENCE xml_catalog_id_seq; |
|
196 |
|
|
197 |
CREATE TRIGGER xml_catalog_before_insert |
|
198 |
BEFORE INSERT ON xml_catalog FOR EACH ROW |
|
199 |
BEGIN |
|
200 |
SELECT xml_catalog_id_seq.nextval |
|
201 |
INTO :new.catalog_id |
|
202 |
FROM dual; |
|
203 |
END; |
|
204 |
/ |
|
205 |
|
|
206 |
/* |
|
207 |
* Documents -- table to store XML documents |
|
208 |
*/ |
|
209 |
CREATE TABLE xml_documents ( |
|
210 |
docid VARCHAR2(250), -- the document id # |
|
211 |
rootnodeid NUMBER(20), -- reference to root node of the DOM |
|
212 |
docname VARCHAR2(100), -- usually the root element name |
|
213 |
doctype VARCHAR2(100), -- public id indicating document type |
|
214 |
user_owner VARCHAR2(100), -- the user owned the document |
|
215 |
user_updated VARCHAR2(100), -- the user updated the document |
|
216 |
server_location NUMBER(20), -- the server on which this document |
|
217 |
-- originates |
|
218 |
rev NUMBER(10) DEFAULT 1,--the revision number of the docume |
|
219 |
date_created DATE, |
|
220 |
date_updated DATE, |
Also available in: Unified diff
Added files to create any version of database schema from scratch