1 |
2099
|
jones
|
/**
|
2 |
|
|
* '$RCSfile$'
|
3 |
|
|
* Copyright: 2004 Regents of the University of California and the
|
4 |
|
|
* National Center for Ecological Analysis and Synthesis
|
5 |
2
|
jones
|
*
|
6 |
2099
|
jones
|
* '$Author$'
|
7 |
|
|
* '$Date$'
|
8 |
|
|
* '$Revision$'
|
9 |
2
|
jones
|
*
|
10 |
2099
|
jones
|
* 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 |
2
|
jones
|
*/
|
24 |
|
|
|
25 |
5
|
jones
|
/*
|
26 |
19
|
jones
|
* Drop all of the objects in proper order
|
27 |
2
|
jones
|
*/
|
28 |
72
|
bojilova
|
set echo off
|
29 |
|
|
|
30 |
20
|
jones
|
DROP SEQUENCE xml_nodes_id_seq;
|
31 |
203
|
jones
|
DROP SEQUENCE xml_revisions_id_seq;
|
32 |
123
|
jones
|
DROP SEQUENCE xml_catalog_id_seq;
|
33 |
472
|
berkley
|
DROP SEQUENCE xml_relation_id_seq;
|
34 |
517
|
berkley
|
DROP SEQUENCE xml_replication_id_seq;
|
35 |
759
|
bojilova
|
DROP SEQUENCE accession_number_id_seq;
|
36 |
2430
|
sgarg
|
DROP SEQUENCE access_log_id_seq;
|
37 |
2422
|
sgarg
|
DROP SEQUENCE xml_returnfield_id_seq;
|
38 |
|
|
DROP SEQUENCE xml_queryresult_id_seq;
|
39 |
2614
|
tao
|
DROP SEQUENCE xml_path_index_id_seq;
|
40 |
2422
|
sgarg
|
|
41 |
759
|
bojilova
|
/* Drop triggers are not necessary */
|
42 |
|
|
DROP TRIGGER xml_nodes_before_insert;
|
43 |
203
|
jones
|
DROP TRIGGER xml_revisions_before_insert;
|
44 |
93
|
bojilova
|
DROP TRIGGER xml_catalog_before_insert;
|
45 |
472
|
berkley
|
DROP TRIGGER xml_relation_before_insert;
|
46 |
517
|
berkley
|
DROP TRIGGER xml_replication_before_insert;
|
47 |
759
|
bojilova
|
DROP TRIGGER accession_number_before_insert;
|
48 |
2099
|
jones
|
DROP TRIGGER access_log_before_insert;
|
49 |
2422
|
sgarg
|
DROP TRIGGER xml_returnfield_before_insert;
|
50 |
|
|
DROP TRIGGER xml_queryresult_before_insert;
|
51 |
72
|
bojilova
|
|
52 |
2422
|
sgarg
|
|
53 |
123
|
jones
|
DROP TABLE xml_index;
|
54 |
414
|
bojilova
|
DROP TABLE xml_access;
|
55 |
1589
|
tao
|
DROP TABLE xml_accesssubtree;
|
56 |
414
|
bojilova
|
DROP TABLE xml_revisions;
|
57 |
689
|
bojilova
|
DROP TABLE xml_relation;
|
58 |
2614
|
tao
|
DROP TABLE xml_documents CASCADE CONSTRAINTS;
|
59 |
20
|
jones
|
DROP TABLE xml_nodes;
|
60 |
2614
|
tao
|
DROP TABLE xml_nodes_revisions;
|
61 |
517
|
berkley
|
DROP TABLE xml_replication;
|
62 |
698
|
bojilova
|
DROP TABLE xml_catalog;
|
63 |
759
|
bojilova
|
DROP TABLE accession_number;
|
64 |
2099
|
jones
|
DROP TABLE access_log;
|
65 |
2109
|
costa
|
DROP TABLE harvest_site_schedule;
|
66 |
2276
|
jones
|
DROP TABLE harvest_detail_log;
|
67 |
2109
|
costa
|
DROP TABLE harvest_log;
|
68 |
2430
|
sgarg
|
DROP TABLE xml_queryresult;
|
69 |
2422
|
sgarg
|
DROP TABLE xml_returnfield;
|
70 |
2614
|
tao
|
DROP TABLE xml_path_index;
|
71 |
3
|
jones
|
|
72 |
517
|
berkley
|
/*
|
73 |
|
|
*Replication -- table to store servers that metacat is replicated to
|
74 |
|
|
*/
|
75 |
|
|
CREATE TABLE xml_replication (
|
76 |
544
|
berkley
|
serverid NUMBER(20),
|
77 |
|
|
server VARCHAR2(512),
|
78 |
|
|
last_checked DATE,
|
79 |
1292
|
tao
|
replicate NUMBER(1),
|
80 |
|
|
datareplicate NUMBER(1),
|
81 |
|
|
hub NUMBER(1),
|
82 |
544
|
berkley
|
CONSTRAINT xml_replication_pk PRIMARY KEY (serverid)
|
83 |
2422
|
sgarg
|
);
|
84 |
|
|
|
85 |
544
|
berkley
|
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 |
517
|
berkley
|
|
95 |
1292
|
tao
|
INSERT INTO xml_replication (server, replicate, datareplicate, hub)
|
96 |
|
|
VALUES ('localhost', '0', '0', '0');
|
97 |
550
|
berkley
|
|
98 |
2422
|
sgarg
|
/*
|
99 |
20
|
jones
|
* Nodes -- table to store XML Nodes (both elements and attributes)
|
100 |
3
|
jones
|
*/
|
101 |
759
|
bojilova
|
CREATE SEQUENCE xml_nodes_id_seq;
|
102 |
20
|
jones
|
CREATE TABLE xml_nodes (
|
103 |
123
|
jones
|
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 |
825
|
bojilova
|
nodeprefix VARCHAR2(50), -- the namespace prefix of an element
|
109 |
|
|
-- or attribute
|
110 |
2422
|
sgarg
|
nodedata VARCHAR2(4000), -- the data for this node (e.g.,
|
111 |
123
|
jones
|
-- 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 |
317
|
bojilova
|
docid VARCHAR2(250), -- index to the document id
|
115 |
3
|
jones
|
date_created DATE,
|
116 |
|
|
date_updated DATE,
|
117 |
2422
|
sgarg
|
nodedatanumerical NUMBER, -- the data for this node if
|
118 |
2359
|
sgarg
|
-- it is a number
|
119 |
20
|
jones
|
CONSTRAINT xml_nodes_pk PRIMARY KEY (nodeid),
|
120 |
2422
|
sgarg
|
CONSTRAINT xml_nodes_root_fk
|
121 |
123
|
jones
|
FOREIGN KEY (rootnodeid) REFERENCES xml_nodes,
|
122 |
2422
|
sgarg
|
CONSTRAINT xml_nodes_parent_fk
|
123 |
20
|
jones
|
FOREIGN KEY (parentnodeid) REFERENCES xml_nodes
|
124 |
3
|
jones
|
);
|
125 |
759
|
bojilova
|
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 |
2422
|
sgarg
|
FROM dual;
|
131 |
|
|
END;
|
132 |
759
|
bojilova
|
/
|
133 |
2422
|
sgarg
|
|
134 |
2516
|
sgarg
|
|
135 |
2422
|
sgarg
|
/*
|
136 |
759
|
bojilova
|
* Indexes of rootnodeid, parentnodeid, and nodename in xml_nodes
|
137 |
440
|
bojilova
|
*/
|
138 |
|
|
CREATE INDEX xml_nodes_idx1 ON xml_nodes (rootnodeid);
|
139 |
|
|
CREATE INDEX xml_nodes_idx2 ON xml_nodes (parentnodeid);
|
140 |
755
|
bojilova
|
CREATE INDEX xml_nodes_idx3 ON xml_nodes (nodename);
|
141 |
440
|
bojilova
|
|
142 |
2516
|
sgarg
|
|
143 |
2422
|
sgarg
|
/*
|
144 |
2519
|
sgarg
|
* xml_nodes_revisions -- table to store nodes from xml_nodes which are of old revisions and deleted document
|
145 |
2516
|
sgarg
|
*/
|
146 |
|
|
|
147 |
2519
|
sgarg
|
CREATE TABLE xml_nodes_revisions (
|
148 |
2516
|
sgarg
|
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 |
2519
|
sgarg
|
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 |
2516
|
sgarg
|
);
|
170 |
|
|
|
171 |
|
|
|
172 |
|
|
/*
|
173 |
|
|
* Indexes of rootnodeid, parentnodeid, and nodename in xml_nodes_revision
|
174 |
|
|
*/
|
175 |
2519
|
sgarg
|
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 |
2516
|
sgarg
|
|
179 |
|
|
/*
|
180 |
698
|
bojilova
|
* 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 |
2422
|
sgarg
|
CONSTRAINT xml_catalog_uk UNIQUE
|
192 |
698
|
bojilova
|
(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 |
2422
|
sgarg
|
/*
|
207 |
72
|
bojilova
|
* Documents -- table to store XML documents
|
208 |
5
|
jones
|
*/
|
209 |
|
|
CREATE TABLE xml_documents (
|
210 |
165
|
jones
|
docid VARCHAR2(250), -- the document id #
|
211 |
|
|
rootnodeid NUMBER(20), -- reference to root node of the DOM
|
212 |
123
|
jones
|
docname VARCHAR2(100), -- usually the root element name
|
213 |
|
|
doctype VARCHAR2(100), -- public id indicating document type
|
214 |
414
|
bojilova
|
user_owner VARCHAR2(100), -- the user owned the document
|
215 |
|
|
user_updated VARCHAR2(100), -- the user updated the document
|
216 |
2422
|
sgarg
|
server_location NUMBER(20), -- the server on which this document
|
217 |
843
|
berkley
|
-- originates
|
218 |
825
|
bojilova
|
rev NUMBER(10) DEFAULT 1,--the revision number of the docume
|
219 |
5
|
jones
|
date_created DATE,
|
220 |
|
|
date_updated DATE,
|
221 |
698
|
bojilova
|
public_access NUMBER(1), -- flag for public access
|
222 |
2422
|
sgarg
|
catalog_id NUMBER(20), -- reference to xml_catalog
|
223 |
5
|
jones
|
CONSTRAINT xml_documents_pk PRIMARY KEY (docid),
|
224 |
544
|
berkley
|
CONSTRAINT xml_documents_rep_fk
|
225 |
2422
|
sgarg
|
FOREIGN KEY (server_location) REFERENCES xml_replication,
|
226 |
|
|
CONSTRAINT xml_documents_root_fk
|
227 |
698
|
bojilova
|
FOREIGN KEY (rootnodeid) REFERENCES xml_nodes,
|
228 |
2422
|
sgarg
|
CONSTRAINT xml_documents_catalog_fk
|
229 |
698
|
bojilova
|
FOREIGN KEY (catalog_id) REFERENCES xml_catalog
|
230 |
5
|
jones
|
);
|
231 |
|
|
|
232 |
2422
|
sgarg
|
/*
|
233 |
440
|
bojilova
|
* Index of <docid,doctype> in xml_document
|
234 |
|
|
*/
|
235 |
|
|
CREATE INDEX xml_documents_idx1 ON xml_documents (docid, doctype);
|
236 |
|
|
|
237 |
2422
|
sgarg
|
/*
|
238 |
203
|
jones
|
* Revised Documents -- table to store XML documents saved after an UPDATE
|
239 |
|
|
* or DELETE
|
240 |
|
|
*/
|
241 |
|
|
CREATE TABLE xml_revisions (
|
242 |
|
|
revisionid NUMBER(20), -- the revision number we are saving
|
243 |
|
|
docid VARCHAR2(250), -- the document id #
|
244 |
|
|
rootnodeid NUMBER(20), -- reference to root node of the DOM
|
245 |
|
|
docname VARCHAR2(100), -- usually the root element name
|
246 |
|
|
doctype VARCHAR2(100), -- public id indicating document type
|
247 |
414
|
bojilova
|
user_owner VARCHAR2(100),
|
248 |
|
|
user_updated VARCHAR2(100),
|
249 |
556
|
bojilova
|
server_location NUMBER(20),
|
250 |
689
|
bojilova
|
rev NUMBER(10),
|
251 |
203
|
jones
|
date_created DATE,
|
252 |
|
|
date_updated DATE,
|
253 |
698
|
bojilova
|
public_access NUMBER(1), -- flag for public access
|
254 |
2422
|
sgarg
|
catalog_id NUMBER(20), -- reference to xml_catalog
|
255 |
203
|
jones
|
CONSTRAINT xml_revisions_pk PRIMARY KEY (revisionid),
|
256 |
552
|
berkley
|
CONSTRAINT xml_revisions_rep_fk
|
257 |
689
|
bojilova
|
FOREIGN KEY (server_location) REFERENCES xml_replication,
|
258 |
2422
|
sgarg
|
CONSTRAINT xml_revisions_root_fk
|
259 |
2516
|
sgarg
|
FOREIGN KEY (rootnodeid) REFERENCES xml_nodes_revisions,
|
260 |
2422
|
sgarg
|
CONSTRAINT xml_revisions_catalog_fk
|
261 |
698
|
bojilova
|
FOREIGN KEY (catalog_id) REFERENCES xml_catalog
|
262 |
203
|
jones
|
);
|
263 |
|
|
|
264 |
|
|
CREATE SEQUENCE xml_revisions_id_seq;
|
265 |
|
|
|
266 |
|
|
CREATE TRIGGER xml_revisions_before_insert
|
267 |
|
|
BEFORE INSERT ON xml_revisions FOR EACH ROW
|
268 |
|
|
BEGIN
|
269 |
|
|
SELECT xml_revisions_id_seq.nextval
|
270 |
|
|
INTO :new.revisionid
|
271 |
|
|
FROM dual;
|
272 |
|
|
END;
|
273 |
|
|
/
|
274 |
|
|
|
275 |
2422
|
sgarg
|
/*
|
276 |
698
|
bojilova
|
* ACL -- table to store ACL for XML documents by principals
|
277 |
72
|
bojilova
|
*/
|
278 |
698
|
bojilova
|
CREATE TABLE xml_access (
|
279 |
|
|
docid VARCHAR2(250), -- the document id #
|
280 |
|
|
accessfileid VARCHAR2(250), -- the document id # for the access file
|
281 |
|
|
principal_name VARCHAR2(100), -- name of user, group, etc.
|
282 |
|
|
permission NUMBER(1), -- "read", "write", "all"
|
283 |
|
|
perm_type VARCHAR2(32), -- "allowed" or "denied"
|
284 |
|
|
perm_order VARCHAR2(32), -- "allow first" or "deny first"
|
285 |
|
|
begin_time DATE, -- the time that permission begins
|
286 |
|
|
end_time DATE, -- the time that permission ends
|
287 |
|
|
ticket_count NUMBER(5), -- ticket counter for that permission
|
288 |
1420
|
tao
|
subtreeid VARCHAR2(32), -- sub tree id
|
289 |
|
|
startnodeid NUMBER(20), -- start node for sub tree
|
290 |
|
|
endnodeid NUMBER(20), -- end node for sub tree
|
291 |
698
|
bojilova
|
CONSTRAINT xml_access_ck CHECK (begin_time < end_time),
|
292 |
2422
|
sgarg
|
CONSTRAINT xml_access_accessfileid_fk
|
293 |
698
|
bojilova
|
FOREIGN KEY (accessfileid) REFERENCES xml_documents
|
294 |
72
|
bojilova
|
);
|
295 |
|
|
|
296 |
2614
|
tao
|
|
297 |
|
|
|
298 |
2422
|
sgarg
|
/*
|
299 |
|
|
* Index of Nodes -- table to store precomputed paths through tree for
|
300 |
123
|
jones
|
* quick searching in structured searches
|
301 |
|
|
*/
|
302 |
|
|
CREATE TABLE xml_index (
|
303 |
|
|
nodeid NUMBER(20), -- the unique node id
|
304 |
2276
|
jones
|
path VARCHAR2(1000), -- precomputed path through tree
|
305 |
317
|
bojilova
|
docid VARCHAR2(250), -- index to the document id
|
306 |
|
|
doctype VARCHAR2(100), -- public id indicating document type
|
307 |
2422
|
sgarg
|
parentnodeid NUMBER(20), -- id of the parent of the node
|
308 |
825
|
bojilova
|
-- represented by this row
|
309 |
123
|
jones
|
CONSTRAINT xml_index_pk PRIMARY KEY (nodeid,path),
|
310 |
317
|
bojilova
|
CONSTRAINT xml_index_nodeid_fk FOREIGN KEY (nodeid) REFERENCES xml_nodes,
|
311 |
2422
|
sgarg
|
CONSTRAINT xml_index_docid_fk
|
312 |
317
|
bojilova
|
FOREIGN KEY (docid) REFERENCES xml_documents
|
313 |
123
|
jones
|
);
|
314 |
163
|
bojilova
|
|
315 |
2422
|
sgarg
|
/*
|
316 |
|
|
* Index of the paths in xml_index
|
317 |
177
|
jones
|
*/
|
318 |
|
|
CREATE INDEX xml_index_idx1 ON xml_index (path);
|
319 |
|
|
|
320 |
2516
|
sgarg
|
|
321 |
|
|
/*
|
322 |
2614
|
tao
|
* Index of Paths - table to store nodes with paths specified by userst in metacat.properties
|
323 |
2516
|
sgarg
|
*/
|
324 |
|
|
CREATE TABLE xml_path_index (
|
325 |
|
|
nodeid NUMBER(20), -- the unique node id
|
326 |
|
|
docid VARCHAR2(250), -- index to the document id
|
327 |
|
|
path VARCHAR2(1000), -- precomputed path through tree
|
328 |
2614
|
tao
|
nodedata VARCHAR2(4000), -- the data for this node e.g.,
|
329 |
|
|
nodedatanumerical NUMBER(20), -- the data for this node if
|
330 |
2516
|
sgarg
|
parentnodeid NUMBER(20), -- index of the parent of this node
|
331 |
2614
|
tao
|
CONSTRAINT xml_path_index_pk PRIMARY KEY (nodeid),
|
332 |
|
|
CONSTRAINT xml_path_index_docid_fk FOREIGN KEY (docid) REFERENCES xml_documents
|
333 |
|
|
);
|
334 |
2516
|
sgarg
|
|
335 |
2614
|
tao
|
/*
|
336 |
|
|
* create sequence
|
337 |
|
|
*/
|
338 |
2516
|
sgarg
|
CREATE SEQUENCE xml_path_index_id_seq;
|
339 |
|
|
|
340 |
|
|
CREATE TRIGGER xml_path_index_before_insert
|
341 |
2614
|
tao
|
BEFORE INSERT ON xml_path_index FOR EACH ROW
|
342 |
2516
|
sgarg
|
BEGIN
|
343 |
|
|
SELECT xml_path_index_id_seq.nextval
|
344 |
|
|
INTO :new.relationid
|
345 |
|
|
FROM dual;
|
346 |
|
|
END;
|
347 |
|
|
/
|
348 |
|
|
|
349 |
|
|
|
350 |
|
|
/*
|
351 |
|
|
* Index of the path, nodedata, nodedatanumerical in xml_path_index
|
352 |
|
|
*/
|
353 |
|
|
CREATE INDEX xml_path_index_idx1 ON xml_path_index (path);
|
354 |
|
|
CREATE INDEX xml_path_index_idx2 ON xml_path_index (nodedata);
|
355 |
|
|
CREATE INDEX xml_path_index_idx3 ON xml_path_index (nodedatanumerical);
|
356 |
|
|
|
357 |
|
|
|
358 |
2614
|
tao
|
|
359 |
472
|
berkley
|
CREATE TABLE xml_relation (
|
360 |
689
|
bojilova
|
relationid NUMBER(20) PRIMARY KEY, -- unique id
|
361 |
743
|
jones
|
docid VARCHAR2(250), -- the docid of the package file
|
362 |
689
|
bojilova
|
-- that this relation came from
|
363 |
743
|
jones
|
packagetype VARCHAR2(250), -- the type of the package
|
364 |
689
|
bojilova
|
subject VARCHAR2(512) NOT NULL, -- the subject of the relation
|
365 |
743
|
jones
|
subdoctype VARCHAR2(128), -- the doctype of the subject
|
366 |
689
|
bojilova
|
relationship VARCHAR2(128) NOT NULL,-- the relationship type
|
367 |
|
|
object VARCHAR2(512) NOT NULL, -- the object of the relation
|
368 |
|
|
objdoctype VARCHAR2(128), -- the doctype of the object
|
369 |
1604
|
tao
|
CONSTRAINT xml_relation_uk UNIQUE (docid, subject, relationship, object),
|
370 |
2422
|
sgarg
|
CONSTRAINT xml_relation_docid_fk
|
371 |
689
|
bojilova
|
FOREIGN KEY (docid) REFERENCES xml_documents
|
372 |
472
|
berkley
|
);
|
373 |
|
|
|
374 |
|
|
CREATE SEQUENCE xml_relation_id_seq;
|
375 |
2422
|
sgarg
|
|
376 |
472
|
berkley
|
CREATE TRIGGER xml_relation_before_insert
|
377 |
|
|
BEFORE INSERT ON xml_relation FOR EACH ROW
|
378 |
|
|
BEGIN
|
379 |
|
|
SELECT xml_relation_id_seq.nextval
|
380 |
|
|
INTO :new.relationid
|
381 |
|
|
FROM dual;
|
382 |
2422
|
sgarg
|
END;
|
383 |
472
|
berkley
|
/
|
384 |
|
|
|
385 |
2422
|
sgarg
|
/*
|
386 |
759
|
bojilova
|
* Table used as Unique ID generator for the uniqueid part of Accession#
|
387 |
|
|
*/
|
388 |
|
|
CREATE SEQUENCE accession_number_id_seq;
|
389 |
|
|
CREATE TABLE accession_number (
|
390 |
|
|
uniqueid NUMBER(20) PRIMARY KEY,
|
391 |
|
|
site_code VARCHAR2(100),
|
392 |
|
|
date_created DATE
|
393 |
|
|
);
|
394 |
|
|
CREATE TRIGGER accession_number_before_insert
|
395 |
|
|
BEFORE INSERT ON accession_number FOR EACH ROW
|
396 |
|
|
BEGIN
|
397 |
|
|
SELECT accession_number_id_seq.nextval
|
398 |
|
|
INTO :new.uniqueid
|
399 |
|
|
FROM dual;
|
400 |
2422
|
sgarg
|
END;
|
401 |
759
|
bojilova
|
/
|
402 |
|
|
|
403 |
2422
|
sgarg
|
/*
|
404 |
|
|
* accesssubtree -- table to store access subtree info
|
405 |
1530
|
tao
|
*/
|
406 |
|
|
CREATE TABLE xml_accesssubtree (
|
407 |
|
|
docid VARCHAR2(250), -- the document id #
|
408 |
|
|
rev NUMBER(10) DEFAULT 1, --the revision number of the docume
|
409 |
|
|
controllevel VARCHAR2(50), -- the level it control -- document or subtree
|
410 |
2422
|
sgarg
|
subtreeid VARCHAR2(250), -- the subtree id
|
411 |
1530
|
tao
|
startnodeid NUMBER(20), -- the start node id of access subtree
|
412 |
1625
|
tao
|
endnodeid NUMBER(20), -- the end node if of access subtree
|
413 |
2422
|
sgarg
|
CONSTRAINT xml_accesssubtree_docid_fk
|
414 |
1625
|
tao
|
FOREIGN KEY (docid) REFERENCES xml_documents
|
415 |
1530
|
tao
|
);
|
416 |
|
|
|
417 |
2099
|
jones
|
/*
|
418 |
2422
|
sgarg
|
* Returnfields -- table to store combinations of returnfields requested
|
419 |
|
|
* and the number of times this table is accessed
|
420 |
|
|
*/
|
421 |
|
|
CREATE TABLE xml_returnfield (
|
422 |
|
|
returnfield_id NUMBER(20), -- the id for this returnfield entry
|
423 |
|
|
returnfield_string VARCHAR2(2000), -- the returnfield string
|
424 |
|
|
usage_count NUMBER(20), -- the number of times this string
|
425 |
|
|
-- has been requested
|
426 |
|
|
CONSTRAINT xml_returnfield_pk PRIMARY KEY (returnfield_id)
|
427 |
|
|
);
|
428 |
|
|
CREATE INDEX xml_returnfield_idx1 ON xml_returnfield(returnfield_string);
|
429 |
|
|
|
430 |
|
|
CREATE SEQUENCE xml_returnfield_id_seq;
|
431 |
|
|
|
432 |
|
|
CREATE TRIGGER xml_returnfield_before_insert
|
433 |
|
|
BEFORE INSERT ON xml_returnfield FOR EACH ROW
|
434 |
|
|
BEGIN
|
435 |
|
|
SELECT xml_returnfield_id_seq.nextval
|
436 |
|
|
INTO :new.returnfield_id
|
437 |
|
|
FROM dual;
|
438 |
|
|
END;
|
439 |
|
|
/
|
440 |
|
|
|
441 |
|
|
/*
|
442 |
|
|
* Queryresults -- table to store queryresults for a given docid
|
443 |
|
|
* and returnfield_id
|
444 |
|
|
*/
|
445 |
|
|
CREATE TABLE xml_queryresult(
|
446 |
|
|
queryresult_id NUMBER(20), -- id for this entry
|
447 |
|
|
returnfield_id NUMBER(20), -- id for the returnfield corresponding to this entry
|
448 |
|
|
docid VARCHAR2(250), -- docid of the document
|
449 |
|
|
queryresult_string VARCHAR2(4000), -- resultant text generated for this docid and given
|
450 |
|
|
-- returnfield
|
451 |
|
|
CONSTRAINT xml_queryresult_pk PRIMARY KEY (queryresult_id),
|
452 |
|
|
CONSTRAINT xml_queryresult_searchid_fk
|
453 |
|
|
FOREIGN KEY (returnfield_id) REFERENCES xml_returnfield
|
454 |
|
|
);
|
455 |
|
|
|
456 |
|
|
CREATE INDEX xml_queryresult_idx1 ON xml_queryresult (returnfield_id, docid);
|
457 |
|
|
|
458 |
|
|
CREATE SEQUENCE xml_queryresult_id_seq;
|
459 |
|
|
|
460 |
|
|
CREATE TRIGGER xml_queryresult_before_insert
|
461 |
|
|
BEFORE INSERT ON xml_queryresult FOR EACH ROW
|
462 |
|
|
BEGIN
|
463 |
|
|
SELECT xml_queryresult_id_seq.nextval
|
464 |
|
|
INTO :new.queryresult_id
|
465 |
|
|
FROM dual;
|
466 |
|
|
END;
|
467 |
|
|
/
|
468 |
|
|
|
469 |
2516
|
sgarg
|
|
470 |
|
|
|
471 |
|
|
|
472 |
2422
|
sgarg
|
/*
|
473 |
2099
|
jones
|
* Logging -- table to store metadata and data access log
|
474 |
|
|
*/
|
475 |
|
|
CREATE TABLE access_log (
|
476 |
|
|
entryid NUMBER(20), -- the identifier for the log event
|
477 |
|
|
ip_address VARCHAR2(512), -- the ip address inititiating the event
|
478 |
|
|
principal VARCHAR2(512), -- the user initiiating the event
|
479 |
|
|
docid VARCHAR2(250), -- the document id #
|
480 |
|
|
event VARCHAR2(512), -- the code symbolizing the event type
|
481 |
|
|
date_logged DATE, -- the datetime on which the event occurred
|
482 |
|
|
CONSTRAINT access_log_pk PRIMARY KEY (entryid)
|
483 |
|
|
);
|
484 |
|
|
|
485 |
|
|
CREATE SEQUENCE access_log_id_seq;
|
486 |
|
|
CREATE TRIGGER access_log_before_insert
|
487 |
|
|
BEFORE INSERT ON access_log FOR EACH ROW
|
488 |
|
|
BEGIN
|
489 |
|
|
SELECT access_log_id_seq.nextval
|
490 |
|
|
INTO :new.entryid
|
491 |
|
|
FROM dual;
|
492 |
|
|
END;
|
493 |
2112
|
costa
|
/
|
494 |
2109
|
costa
|
|
495 |
2422
|
sgarg
|
/*
|
496 |
2109
|
costa
|
* harvest_site_schedule -- table to store harvest sites and schedule info
|
497 |
|
|
*/
|
498 |
|
|
CREATE TABLE harvest_site_schedule (
|
499 |
|
|
site_schedule_id NUMBER, -- unique id
|
500 |
|
|
documentlisturl VARCHAR2(255), -- URL of the site harvest document list
|
501 |
|
|
ldapdn VARCHAR2(255), -- LDAP distinguished name for site account
|
502 |
|
|
datenextharvest DATE, -- scheduled date of next harvest
|
503 |
|
|
datelastharvest DATE, -- recorded date of last harvest
|
504 |
|
|
updatefrequency NUMBER, -- the harvest update frequency
|
505 |
|
|
unit VARCHAR2(50), -- update unit -- days weeks or months
|
506 |
|
|
contact_email VARCHAR2(50), -- email address of the site contact person
|
507 |
|
|
ldappwd VARCHAR2(20), -- LDAP password for site account
|
508 |
|
|
CONSTRAINT harvest_site_schedule_pk PRIMARY KEY (site_schedule_id)
|
509 |
|
|
);
|
510 |
|
|
|
511 |
2422
|
sgarg
|
/*
|
512 |
2109
|
costa
|
* harvest_log -- table to log entries for harvest operations
|
513 |
|
|
*/
|
514 |
|
|
CREATE TABLE harvest_log (
|
515 |
|
|
harvest_log_id NUMBER, -- unique id
|
516 |
|
|
harvest_date DATE, -- date of the current harvest
|
517 |
|
|
status NUMBER, -- non-zero indicates an error status
|
518 |
|
|
message VARCHAR2(1000), -- text message for this log entry
|
519 |
|
|
harvest_operation_code VARCHAR2(30), -- the type of harvest operation
|
520 |
2112
|
costa
|
site_schedule_id NUMBER, -- site schedule id, or 0 if no site
|
521 |
|
|
CONSTRAINT harvest_log_pk PRIMARY KEY (harvest_log_id)
|
522 |
2109
|
costa
|
);
|
523 |
|
|
|
524 |
2422
|
sgarg
|
/*
|
525 |
2109
|
costa
|
* harvest_detail_log -- table to log detailed info about documents that
|
526 |
|
|
* generated errors during the harvest
|
527 |
|
|
*/
|
528 |
|
|
CREATE TABLE harvest_detail_log (
|
529 |
|
|
detail_log_id NUMBER, -- unique id
|
530 |
|
|
harvest_log_id NUMBER, -- ponter to the related log entry
|
531 |
|
|
scope VARCHAR2(50), -- document scope
|
532 |
|
|
identifier NUMBER, -- document identifier
|
533 |
|
|
revision NUMBER, -- document revision
|
534 |
|
|
document_url VARCHAR2(255), -- document URL
|
535 |
|
|
error_message VARCHAR2(1000), -- text error message
|
536 |
|
|
document_type VARCHAR2(100), -- document type
|
537 |
|
|
CONSTRAINT harvest_detail_log_pk PRIMARY KEY (detail_log_id),
|
538 |
2422
|
sgarg
|
CONSTRAINT harvest_detail_log_fk
|
539 |
2109
|
costa
|
FOREIGN KEY (harvest_log_id) REFERENCES harvest_log
|
540 |
|
|
);
|