1 |
81
|
bojilova
|
/*
|
2 |
2
|
jones
|
* xmltables.sql -- Create or replace tables for storing XML in the db
|
3 |
|
|
*
|
4 |
|
|
* Purpose: creates tables needed for XML database
|
5 |
|
|
*
|
6 |
|
|
* Created: 12 September 1999
|
7 |
|
|
* Author: Matt Jones
|
8 |
|
|
* Organization: National Center for Ecological Analysis and Synthesis
|
9 |
123
|
jones
|
* Copyright: 2000 Regents of the University of California and the
|
10 |
|
|
* National Center for Ecological Analysis and Synthesis
|
11 |
|
|
* For Details: http://www.nceas.ucsb.edu/
|
12 |
|
|
* File Info: '$Id$'
|
13 |
2
|
jones
|
*
|
14 |
|
|
*/
|
15 |
|
|
|
16 |
5
|
jones
|
/*
|
17 |
19
|
jones
|
* Drop all of the objects in proper order
|
18 |
2
|
jones
|
*/
|
19 |
72
|
bojilova
|
set echo off
|
20 |
|
|
|
21 |
20
|
jones
|
DROP SEQUENCE xml_nodes_id_seq;
|
22 |
203
|
jones
|
DROP SEQUENCE xml_revisions_id_seq;
|
23 |
123
|
jones
|
DROP SEQUENCE xml_catalog_id_seq;
|
24 |
472
|
berkley
|
DROP SEQUENCE xml_relation_id_seq;
|
25 |
517
|
berkley
|
DROP SEQUENCE xml_replication_id_seq;
|
26 |
616
|
bojilova
|
DROP SEQUENCE accnum_uniqueid_seq;
|
27 |
72
|
bojilova
|
|
28 |
203
|
jones
|
DROP TRIGGER xml_revisions_before_insert;
|
29 |
93
|
bojilova
|
DROP TRIGGER xml_catalog_before_insert;
|
30 |
472
|
berkley
|
DROP TRIGGER xml_relation_before_insert;
|
31 |
517
|
berkley
|
DROP TRIGGER xml_replication_before_insert;
|
32 |
72
|
bojilova
|
|
33 |
123
|
jones
|
DROP TABLE xml_index;
|
34 |
72
|
bojilova
|
DROP TABLE xml_catalog;
|
35 |
414
|
bojilova
|
DROP TABLE xml_access;
|
36 |
|
|
DROP TABLE xml_revisions;
|
37 |
5
|
jones
|
DROP TABLE xml_documents;
|
38 |
20
|
jones
|
DROP TABLE xml_nodes;
|
39 |
163
|
bojilova
|
DROP TABLE xml_acc_numbers;
|
40 |
472
|
berkley
|
DROP TABLE xml_relation;
|
41 |
517
|
berkley
|
DROP TABLE xml_replication;
|
42 |
3
|
jones
|
|
43 |
517
|
berkley
|
/*
|
44 |
|
|
*Replication -- table to store servers that metacat is replicated to
|
45 |
|
|
*/
|
46 |
|
|
CREATE TABLE xml_replication (
|
47 |
544
|
berkley
|
serverid NUMBER(20),
|
48 |
|
|
server VARCHAR2(512),
|
49 |
|
|
last_checked DATE,
|
50 |
629
|
berkley
|
replicate NUMBER(1),
|
51 |
544
|
berkley
|
CONSTRAINT xml_replication_pk PRIMARY KEY (serverid)
|
52 |
|
|
);
|
53 |
|
|
|
54 |
|
|
CREATE SEQUENCE xml_replication_id_seq;
|
55 |
|
|
CREATE TRIGGER xml_replication_before_insert
|
56 |
|
|
BEFORE INSERT ON xml_replication FOR EACH ROW
|
57 |
|
|
BEGIN
|
58 |
|
|
SELECT xml_replication_id_seq.nextval
|
59 |
|
|
INTO :new.serverid
|
60 |
|
|
FROM dual;
|
61 |
|
|
END;
|
62 |
|
|
/
|
63 |
517
|
berkley
|
|
64 |
629
|
berkley
|
INSERT INTO xml_replication (serverid, server, replicate) VALUES ('1', 'localhost', '0');
|
65 |
550
|
berkley
|
|
66 |
3
|
jones
|
/*
|
67 |
20
|
jones
|
* Nodes -- table to store XML Nodes (both elements and attributes)
|
68 |
3
|
jones
|
*/
|
69 |
20
|
jones
|
CREATE TABLE xml_nodes (
|
70 |
123
|
jones
|
nodeid NUMBER(20), -- the unique node id (pk)
|
71 |
|
|
nodeindex NUMBER(10), -- order of nodes within parent
|
72 |
|
|
nodetype VARCHAR2(20), -- type (DOCUMENT, COMMENT, PI,
|
73 |
|
|
-- ELEMENT, ATTRIBUTE, TEXT)
|
74 |
|
|
nodename VARCHAR2(250), -- the name of an element or attribute
|
75 |
|
|
nodedata VARCHAR2(4000), -- the data for this node (e.g.,
|
76 |
|
|
-- for TEXT it is the content)
|
77 |
|
|
parentnodeid NUMBER(20), -- index of the parent of this node
|
78 |
|
|
rootnodeid NUMBER(20), -- index of the root node of this tree
|
79 |
317
|
bojilova
|
docid VARCHAR2(250), -- index to the document id
|
80 |
3
|
jones
|
date_created DATE,
|
81 |
|
|
date_updated DATE,
|
82 |
20
|
jones
|
CONSTRAINT xml_nodes_pk PRIMARY KEY (nodeid),
|
83 |
123
|
jones
|
CONSTRAINT xml_nodes_root_fk
|
84 |
|
|
FOREIGN KEY (rootnodeid) REFERENCES xml_nodes,
|
85 |
20
|
jones
|
CONSTRAINT xml_nodes_parent_fk
|
86 |
|
|
FOREIGN KEY (parentnodeid) REFERENCES xml_nodes
|
87 |
3
|
jones
|
);
|
88 |
|
|
|
89 |
440
|
bojilova
|
/*
|
90 |
|
|
* Indexes of rootnodeid & parentnodeid in xml_nodes
|
91 |
|
|
*/
|
92 |
|
|
CREATE INDEX xml_nodes_idx1 ON xml_nodes (rootnodeid);
|
93 |
|
|
CREATE INDEX xml_nodes_idx2 ON xml_nodes (parentnodeid);
|
94 |
|
|
|
95 |
20
|
jones
|
CREATE SEQUENCE xml_nodes_id_seq;
|
96 |
19
|
jones
|
|
97 |
3
|
jones
|
/*
|
98 |
72
|
bojilova
|
* Documents -- table to store XML documents
|
99 |
5
|
jones
|
*/
|
100 |
|
|
CREATE TABLE xml_documents (
|
101 |
165
|
jones
|
docid VARCHAR2(250), -- the document id #
|
102 |
|
|
rootnodeid NUMBER(20), -- reference to root node of the DOM
|
103 |
123
|
jones
|
docname VARCHAR2(100), -- usually the root element name
|
104 |
|
|
doctype VARCHAR2(100), -- public id indicating document type
|
105 |
|
|
doctitle VARCHAR2(1000), -- title of document if exists
|
106 |
414
|
bojilova
|
user_owner VARCHAR2(100), -- the user owned the document
|
107 |
|
|
user_updated VARCHAR2(100), -- the user updated the document
|
108 |
544
|
berkley
|
server_location NUMBER(20), -- the server on which this document resides
|
109 |
575
|
berkley
|
rev NUMBER(10) DEFAULT 1, --the revision number of the document
|
110 |
5
|
jones
|
date_created DATE,
|
111 |
|
|
date_updated DATE,
|
112 |
445
|
bojilova
|
public_access NUMBER(1) DEFAULT 1, -- flag for public access
|
113 |
575
|
berkley
|
updated NUMBER(1) DEFAULT 0,
|
114 |
5
|
jones
|
CONSTRAINT xml_documents_pk PRIMARY KEY (docid),
|
115 |
544
|
berkley
|
CONSTRAINT xml_documents_rep_fk
|
116 |
|
|
FOREIGN KEY (server_location) REFERENCES xml_replication,
|
117 |
5
|
jones
|
CONSTRAINT xml_documents_root_fk
|
118 |
20
|
jones
|
FOREIGN KEY (rootnodeid) REFERENCES xml_nodes
|
119 |
5
|
jones
|
);
|
120 |
|
|
|
121 |
72
|
bojilova
|
/*
|
122 |
440
|
bojilova
|
* Index of <docid,doctype> in xml_document
|
123 |
|
|
*/
|
124 |
|
|
CREATE INDEX xml_documents_idx1 ON xml_documents (docid, doctype);
|
125 |
|
|
|
126 |
|
|
/*
|
127 |
414
|
bojilova
|
* ACL -- table to store ACL for XML documents by principals
|
128 |
|
|
*/
|
129 |
|
|
CREATE TABLE xml_access (
|
130 |
|
|
docid VARCHAR2(250), -- the document id #
|
131 |
644
|
bojilova
|
accessfileid VARCHAR2(250), -- the document id # for the access file
|
132 |
556
|
bojilova
|
principal_name VARCHAR2(100), -- name of user, group, etc.
|
133 |
|
|
permission NUMBER(1), -- "read", "write", "all"
|
134 |
560
|
bojilova
|
perm_type VARCHAR2(32), -- "allowed" or "denied"
|
135 |
565
|
jones
|
perm_order VARCHAR2(32), -- "allow first" or "deny first"
|
136 |
556
|
bojilova
|
begin_time DATE, -- the time that permission begins
|
137 |
|
|
end_time DATE, -- the time that permission ends
|
138 |
560
|
bojilova
|
ticket_count NUMBER(5), -- ticket counter for that permission
|
139 |
|
|
CONSTRAINT xml_access_pk UNIQUE (docid, principal_name, permission, perm_type),
|
140 |
414
|
bojilova
|
CONSTRAINT xml_access_ck CHECK (begin_time < end_time)
|
141 |
|
|
);
|
142 |
|
|
|
143 |
|
|
/*
|
144 |
203
|
jones
|
* Revised Documents -- table to store XML documents saved after an UPDATE
|
145 |
|
|
* or DELETE
|
146 |
|
|
*/
|
147 |
|
|
CREATE TABLE xml_revisions (
|
148 |
|
|
revisionid NUMBER(20), -- the revision number we are saving
|
149 |
|
|
docid VARCHAR2(250), -- the document id #
|
150 |
|
|
rootnodeid NUMBER(20), -- reference to root node of the DOM
|
151 |
|
|
docname VARCHAR2(100), -- usually the root element name
|
152 |
|
|
doctype VARCHAR2(100), -- public id indicating document type
|
153 |
|
|
doctitle VARCHAR2(1000), -- title of document if exists
|
154 |
414
|
bojilova
|
user_owner VARCHAR2(100),
|
155 |
|
|
user_updated VARCHAR2(100),
|
156 |
556
|
bojilova
|
server_location NUMBER(20),
|
157 |
575
|
berkley
|
rev NUMBER(10),
|
158 |
203
|
jones
|
date_created DATE,
|
159 |
|
|
date_updated DATE,
|
160 |
|
|
CONSTRAINT xml_revisions_pk PRIMARY KEY (revisionid),
|
161 |
552
|
berkley
|
CONSTRAINT xml_revisions_rep_fk
|
162 |
|
|
FOREIGN KEY (server_location) REFERENCES xml_replication,
|
163 |
203
|
jones
|
CONSTRAINT xml_revisions_root_fk
|
164 |
|
|
FOREIGN KEY (rootnodeid) REFERENCES xml_nodes
|
165 |
|
|
);
|
166 |
|
|
|
167 |
|
|
CREATE SEQUENCE xml_revisions_id_seq;
|
168 |
|
|
|
169 |
|
|
CREATE TRIGGER xml_revisions_before_insert
|
170 |
|
|
BEFORE INSERT ON xml_revisions FOR EACH ROW
|
171 |
|
|
BEGIN
|
172 |
|
|
SELECT xml_revisions_id_seq.nextval
|
173 |
|
|
INTO :new.revisionid
|
174 |
|
|
FROM dual;
|
175 |
|
|
END;
|
176 |
|
|
/
|
177 |
|
|
|
178 |
|
|
/*
|
179 |
93
|
bojilova
|
* XML Catalog -- table to store all external sources for XML documents
|
180 |
72
|
bojilova
|
*/
|
181 |
|
|
CREATE TABLE xml_catalog (
|
182 |
123
|
jones
|
catalog_id NUMBER(20), -- the id for this catalog entry
|
183 |
|
|
entry_type VARCHAR2(500), -- the type of this catalog entry
|
184 |
|
|
-- (e.g., DTD, XSD, XSL)
|
185 |
|
|
source_doctype VARCHAR2(500), -- the source public_id for transforms
|
186 |
|
|
target_doctype VARCHAR2(500), -- the target public_id for transforms
|
187 |
|
|
public_id VARCHAR2(500), -- the unique id for this type
|
188 |
|
|
system_id VARCHAR2(1000), -- the local location of the object
|
189 |
596
|
bojilova
|
CONSTRAINT xml_catalog_pk PRIMARY KEY (catalog_id),
|
190 |
|
|
CONSTRAINT xml_catalog_uk UNIQUE (entry_type, source_doctype, target_doctype, public_id)
|
191 |
72
|
bojilova
|
);
|
192 |
|
|
|
193 |
123
|
jones
|
CREATE SEQUENCE xml_catalog_id_seq;
|
194 |
72
|
bojilova
|
|
195 |
93
|
bojilova
|
CREATE TRIGGER xml_catalog_before_insert
|
196 |
|
|
BEFORE INSERT ON xml_catalog FOR EACH ROW
|
197 |
72
|
bojilova
|
BEGIN
|
198 |
123
|
jones
|
SELECT xml_catalog_id_seq.nextval
|
199 |
|
|
INTO :new.catalog_id
|
200 |
72
|
bojilova
|
FROM dual;
|
201 |
|
|
END;
|
202 |
|
|
/
|
203 |
123
|
jones
|
|
204 |
|
|
/*
|
205 |
|
|
* Index of Nodes -- table to store precomputed paths through tree for
|
206 |
|
|
* quick searching in structured searches
|
207 |
|
|
*/
|
208 |
|
|
CREATE TABLE xml_index (
|
209 |
|
|
nodeid NUMBER(20), -- the unique node id
|
210 |
177
|
jones
|
path VARCHAR2(200), -- precomputed path through tree
|
211 |
317
|
bojilova
|
docid VARCHAR2(250), -- index to the document id
|
212 |
|
|
doctype VARCHAR2(100), -- public id indicating document type
|
213 |
450
|
berkley
|
parentnodeid NUMBER(20), -- id of the parent of the node represented by this row
|
214 |
123
|
jones
|
CONSTRAINT xml_index_pk PRIMARY KEY (nodeid,path),
|
215 |
317
|
bojilova
|
CONSTRAINT xml_index_nodeid_fk FOREIGN KEY (nodeid) REFERENCES xml_nodes,
|
216 |
|
|
CONSTRAINT xml_index_docid_fk
|
217 |
|
|
FOREIGN KEY (docid) REFERENCES xml_documents
|
218 |
123
|
jones
|
);
|
219 |
163
|
bojilova
|
|
220 |
|
|
/*
|
221 |
177
|
jones
|
* Index of the paths in xml_index
|
222 |
|
|
*/
|
223 |
|
|
CREATE INDEX xml_index_idx1 ON xml_index (path);
|
224 |
|
|
|
225 |
|
|
/*
|
226 |
616
|
bojilova
|
* Sequence to get uniqueID for Accession #
|
227 |
163
|
bojilova
|
*/
|
228 |
616
|
bojilova
|
CREATE SEQUENCE accnum_uniqueid_seq;
|
229 |
472
|
berkley
|
|
230 |
|
|
CREATE TABLE xml_relation (
|
231 |
|
|
relationid NUMBER(20) PRIMARY KEY, -- unique id
|
232 |
633
|
berkley
|
docid VARCHAR2(250) , -- the docid of the package file that
|
233 |
|
|
-- this relation came from
|
234 |
517
|
berkley
|
subject VARCHAR2(512) NOT NULL, -- the subject of the relation
|
235 |
|
|
subdoctype VARCHAR2(128), -- the doctype of the subject
|
236 |
|
|
relationship VARCHAR2(128) NOT NULL, -- the relationship type
|
237 |
|
|
object VARCHAR2(512) NOT NULL, -- the object of the relation
|
238 |
|
|
objdoctype VARCHAR2(128), -- the doctype of the object
|
239 |
|
|
CONSTRAINT xml_relation_unk UNIQUE (subject, relationship, object)
|
240 |
472
|
berkley
|
);
|
241 |
|
|
|
242 |
|
|
CREATE SEQUENCE xml_relation_id_seq;
|
243 |
|
|
|
244 |
|
|
CREATE TRIGGER xml_relation_before_insert
|
245 |
|
|
BEFORE INSERT ON xml_relation FOR EACH ROW
|
246 |
|
|
BEGIN
|
247 |
|
|
SELECT xml_relation_id_seq.nextval
|
248 |
|
|
INTO :new.relationid
|
249 |
|
|
FROM dual;
|
250 |
|
|
END;
|
251 |
|
|
/
|