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 |
616
|
bojilova
|
DROP SEQUENCE accnum_uniqueid_seq;
|
36 |
749
|
bojilova
|
DROP SEQUENCE xml_documents_id_seq;
|
37 |
759
|
bojilova
|
DROP SEQUENCE accession_number_id_seq;
|
38 |
2099
|
jones
|
DROP SEQUENCE access_log_seq;
|
39 |
759
|
bojilova
|
|
40 |
|
|
/* Drop triggers are not necessary */
|
41 |
|
|
DROP TRIGGER xml_nodes_before_insert;
|
42 |
203
|
jones
|
DROP TRIGGER xml_revisions_before_insert;
|
43 |
93
|
bojilova
|
DROP TRIGGER xml_catalog_before_insert;
|
44 |
472
|
berkley
|
DROP TRIGGER xml_relation_before_insert;
|
45 |
517
|
berkley
|
DROP TRIGGER xml_replication_before_insert;
|
46 |
759
|
bojilova
|
DROP TRIGGER accession_number_before_insert;
|
47 |
2099
|
jones
|
DROP TRIGGER access_log_before_insert;
|
48 |
72
|
bojilova
|
|
49 |
123
|
jones
|
DROP TABLE xml_index;
|
50 |
414
|
bojilova
|
DROP TABLE xml_access;
|
51 |
1589
|
tao
|
DROP TABLE xml_accesssubtree;
|
52 |
414
|
bojilova
|
DROP TABLE xml_revisions;
|
53 |
689
|
bojilova
|
DROP TABLE xml_relation;
|
54 |
5
|
jones
|
DROP TABLE xml_documents;
|
55 |
20
|
jones
|
DROP TABLE xml_nodes;
|
56 |
517
|
berkley
|
DROP TABLE xml_replication;
|
57 |
698
|
bojilova
|
DROP TABLE xml_catalog;
|
58 |
759
|
bojilova
|
DROP TABLE accession_number;
|
59 |
2099
|
jones
|
DROP TABLE access_log;
|
60 |
2109
|
costa
|
DROP TABLE harvest_site_schedule;
|
61 |
|
|
DROP TABLE harvest_log;
|
62 |
|
|
DROP TABLE harvest_detail_log;
|
63 |
3
|
jones
|
|
64 |
517
|
berkley
|
/*
|
65 |
|
|
*Replication -- table to store servers that metacat is replicated to
|
66 |
|
|
*/
|
67 |
|
|
CREATE TABLE xml_replication (
|
68 |
544
|
berkley
|
serverid NUMBER(20),
|
69 |
|
|
server VARCHAR2(512),
|
70 |
|
|
last_checked DATE,
|
71 |
1292
|
tao
|
replicate NUMBER(1),
|
72 |
|
|
datareplicate NUMBER(1),
|
73 |
|
|
hub NUMBER(1),
|
74 |
544
|
berkley
|
CONSTRAINT xml_replication_pk PRIMARY KEY (serverid)
|
75 |
|
|
);
|
76 |
|
|
|
77 |
|
|
CREATE SEQUENCE xml_replication_id_seq;
|
78 |
|
|
CREATE TRIGGER xml_replication_before_insert
|
79 |
|
|
BEFORE INSERT ON xml_replication FOR EACH ROW
|
80 |
|
|
BEGIN
|
81 |
|
|
SELECT xml_replication_id_seq.nextval
|
82 |
|
|
INTO :new.serverid
|
83 |
|
|
FROM dual;
|
84 |
|
|
END;
|
85 |
|
|
/
|
86 |
517
|
berkley
|
|
87 |
1292
|
tao
|
INSERT INTO xml_replication (server, replicate, datareplicate, hub)
|
88 |
|
|
VALUES ('localhost', '0', '0', '0');
|
89 |
550
|
berkley
|
|
90 |
3
|
jones
|
/*
|
91 |
20
|
jones
|
* Nodes -- table to store XML Nodes (both elements and attributes)
|
92 |
3
|
jones
|
*/
|
93 |
759
|
bojilova
|
CREATE SEQUENCE xml_nodes_id_seq;
|
94 |
20
|
jones
|
CREATE TABLE xml_nodes (
|
95 |
123
|
jones
|
nodeid NUMBER(20), -- the unique node id (pk)
|
96 |
|
|
nodeindex NUMBER(10), -- order of nodes within parent
|
97 |
|
|
nodetype VARCHAR2(20), -- type (DOCUMENT, COMMENT, PI,
|
98 |
|
|
-- ELEMENT, ATTRIBUTE, TEXT)
|
99 |
|
|
nodename VARCHAR2(250), -- the name of an element or attribute
|
100 |
825
|
bojilova
|
nodeprefix VARCHAR2(50), -- the namespace prefix of an element
|
101 |
|
|
-- or attribute
|
102 |
123
|
jones
|
nodedata VARCHAR2(4000), -- the data for this node (e.g.,
|
103 |
|
|
-- for TEXT it is the content)
|
104 |
|
|
parentnodeid NUMBER(20), -- index of the parent of this node
|
105 |
|
|
rootnodeid NUMBER(20), -- index of the root node of this tree
|
106 |
317
|
bojilova
|
docid VARCHAR2(250), -- index to the document id
|
107 |
3
|
jones
|
date_created DATE,
|
108 |
|
|
date_updated DATE,
|
109 |
20
|
jones
|
CONSTRAINT xml_nodes_pk PRIMARY KEY (nodeid),
|
110 |
123
|
jones
|
CONSTRAINT xml_nodes_root_fk
|
111 |
|
|
FOREIGN KEY (rootnodeid) REFERENCES xml_nodes,
|
112 |
20
|
jones
|
CONSTRAINT xml_nodes_parent_fk
|
113 |
|
|
FOREIGN KEY (parentnodeid) REFERENCES xml_nodes
|
114 |
3
|
jones
|
);
|
115 |
759
|
bojilova
|
CREATE TRIGGER xml_nodes_before_insert
|
116 |
|
|
BEFORE INSERT ON xml_nodes FOR EACH ROW
|
117 |
|
|
BEGIN
|
118 |
|
|
SELECT xml_nodes_id_seq.nextval
|
119 |
|
|
INTO :new.nodeid
|
120 |
|
|
FROM dual;
|
121 |
|
|
END;
|
122 |
|
|
/
|
123 |
|
|
|
124 |
440
|
bojilova
|
/*
|
125 |
759
|
bojilova
|
* Indexes of rootnodeid, parentnodeid, and nodename in xml_nodes
|
126 |
440
|
bojilova
|
*/
|
127 |
|
|
CREATE INDEX xml_nodes_idx1 ON xml_nodes (rootnodeid);
|
128 |
|
|
CREATE INDEX xml_nodes_idx2 ON xml_nodes (parentnodeid);
|
129 |
755
|
bojilova
|
CREATE INDEX xml_nodes_idx3 ON xml_nodes (nodename);
|
130 |
440
|
bojilova
|
|
131 |
3
|
jones
|
/*
|
132 |
698
|
bojilova
|
* XML Catalog -- table to store all external sources for XML documents
|
133 |
|
|
*/
|
134 |
|
|
CREATE TABLE xml_catalog (
|
135 |
|
|
catalog_id NUMBER(20), -- the id for this catalog entry
|
136 |
|
|
entry_type VARCHAR2(500), -- the type of this catalog entry
|
137 |
|
|
-- (e.g., DTD, XSD, XSL)
|
138 |
|
|
source_doctype VARCHAR2(500), -- the source public_id for transforms
|
139 |
|
|
target_doctype VARCHAR2(500), -- the target public_id for transforms
|
140 |
|
|
public_id VARCHAR2(500), -- the unique id for this type
|
141 |
|
|
system_id VARCHAR2(1000), -- the local location of the object
|
142 |
|
|
CONSTRAINT xml_catalog_pk PRIMARY KEY (catalog_id),
|
143 |
|
|
CONSTRAINT xml_catalog_uk UNIQUE
|
144 |
|
|
(entry_type, source_doctype, target_doctype, public_id)
|
145 |
|
|
);
|
146 |
|
|
|
147 |
|
|
CREATE SEQUENCE xml_catalog_id_seq;
|
148 |
|
|
|
149 |
|
|
CREATE TRIGGER xml_catalog_before_insert
|
150 |
|
|
BEFORE INSERT ON xml_catalog FOR EACH ROW
|
151 |
|
|
BEGIN
|
152 |
|
|
SELECT xml_catalog_id_seq.nextval
|
153 |
|
|
INTO :new.catalog_id
|
154 |
|
|
FROM dual;
|
155 |
|
|
END;
|
156 |
|
|
/
|
157 |
|
|
|
158 |
|
|
/*
|
159 |
72
|
bojilova
|
* Documents -- table to store XML documents
|
160 |
5
|
jones
|
*/
|
161 |
|
|
CREATE TABLE xml_documents (
|
162 |
165
|
jones
|
docid VARCHAR2(250), -- the document id #
|
163 |
|
|
rootnodeid NUMBER(20), -- reference to root node of the DOM
|
164 |
123
|
jones
|
docname VARCHAR2(100), -- usually the root element name
|
165 |
|
|
doctype VARCHAR2(100), -- public id indicating document type
|
166 |
414
|
bojilova
|
user_owner VARCHAR2(100), -- the user owned the document
|
167 |
|
|
user_updated VARCHAR2(100), -- the user updated the document
|
168 |
825
|
bojilova
|
server_location NUMBER(20), -- the server on which this document
|
169 |
843
|
berkley
|
-- originates
|
170 |
825
|
bojilova
|
rev NUMBER(10) DEFAULT 1,--the revision number of the docume
|
171 |
5
|
jones
|
date_created DATE,
|
172 |
|
|
date_updated DATE,
|
173 |
698
|
bojilova
|
public_access NUMBER(1), -- flag for public access
|
174 |
|
|
catalog_id NUMBER(20), -- reference to xml_catalog
|
175 |
5
|
jones
|
CONSTRAINT xml_documents_pk PRIMARY KEY (docid),
|
176 |
544
|
berkley
|
CONSTRAINT xml_documents_rep_fk
|
177 |
689
|
bojilova
|
FOREIGN KEY (server_location) REFERENCES xml_replication,
|
178 |
5
|
jones
|
CONSTRAINT xml_documents_root_fk
|
179 |
698
|
bojilova
|
FOREIGN KEY (rootnodeid) REFERENCES xml_nodes,
|
180 |
|
|
CONSTRAINT xml_documents_catalog_fk
|
181 |
|
|
FOREIGN KEY (catalog_id) REFERENCES xml_catalog
|
182 |
5
|
jones
|
);
|
183 |
|
|
|
184 |
72
|
bojilova
|
/*
|
185 |
440
|
bojilova
|
* Index of <docid,doctype> in xml_document
|
186 |
|
|
*/
|
187 |
|
|
CREATE INDEX xml_documents_idx1 ON xml_documents (docid, doctype);
|
188 |
|
|
|
189 |
|
|
/*
|
190 |
203
|
jones
|
* Revised Documents -- table to store XML documents saved after an UPDATE
|
191 |
|
|
* or DELETE
|
192 |
|
|
*/
|
193 |
|
|
CREATE TABLE xml_revisions (
|
194 |
|
|
revisionid NUMBER(20), -- the revision number we are saving
|
195 |
|
|
docid VARCHAR2(250), -- the document id #
|
196 |
|
|
rootnodeid NUMBER(20), -- reference to root node of the DOM
|
197 |
|
|
docname VARCHAR2(100), -- usually the root element name
|
198 |
|
|
doctype VARCHAR2(100), -- public id indicating document type
|
199 |
414
|
bojilova
|
user_owner VARCHAR2(100),
|
200 |
|
|
user_updated VARCHAR2(100),
|
201 |
556
|
bojilova
|
server_location NUMBER(20),
|
202 |
689
|
bojilova
|
rev NUMBER(10),
|
203 |
203
|
jones
|
date_created DATE,
|
204 |
|
|
date_updated DATE,
|
205 |
698
|
bojilova
|
public_access NUMBER(1), -- flag for public access
|
206 |
|
|
catalog_id NUMBER(20), -- reference to xml_catalog
|
207 |
203
|
jones
|
CONSTRAINT xml_revisions_pk PRIMARY KEY (revisionid),
|
208 |
552
|
berkley
|
CONSTRAINT xml_revisions_rep_fk
|
209 |
689
|
bojilova
|
FOREIGN KEY (server_location) REFERENCES xml_replication,
|
210 |
203
|
jones
|
CONSTRAINT xml_revisions_root_fk
|
211 |
698
|
bojilova
|
FOREIGN KEY (rootnodeid) REFERENCES xml_nodes,
|
212 |
|
|
CONSTRAINT xml_revisions_catalog_fk
|
213 |
|
|
FOREIGN KEY (catalog_id) REFERENCES xml_catalog
|
214 |
203
|
jones
|
);
|
215 |
|
|
|
216 |
|
|
CREATE SEQUENCE xml_revisions_id_seq;
|
217 |
|
|
|
218 |
|
|
CREATE TRIGGER xml_revisions_before_insert
|
219 |
|
|
BEFORE INSERT ON xml_revisions FOR EACH ROW
|
220 |
|
|
BEGIN
|
221 |
|
|
SELECT xml_revisions_id_seq.nextval
|
222 |
|
|
INTO :new.revisionid
|
223 |
|
|
FROM dual;
|
224 |
|
|
END;
|
225 |
|
|
/
|
226 |
|
|
|
227 |
|
|
/*
|
228 |
698
|
bojilova
|
* ACL -- table to store ACL for XML documents by principals
|
229 |
72
|
bojilova
|
*/
|
230 |
698
|
bojilova
|
CREATE TABLE xml_access (
|
231 |
|
|
docid VARCHAR2(250), -- the document id #
|
232 |
|
|
accessfileid VARCHAR2(250), -- the document id # for the access file
|
233 |
|
|
principal_name VARCHAR2(100), -- name of user, group, etc.
|
234 |
|
|
permission NUMBER(1), -- "read", "write", "all"
|
235 |
|
|
perm_type VARCHAR2(32), -- "allowed" or "denied"
|
236 |
|
|
perm_order VARCHAR2(32), -- "allow first" or "deny first"
|
237 |
|
|
begin_time DATE, -- the time that permission begins
|
238 |
|
|
end_time DATE, -- the time that permission ends
|
239 |
|
|
ticket_count NUMBER(5), -- ticket counter for that permission
|
240 |
1420
|
tao
|
subtreeid VARCHAR2(32), -- sub tree id
|
241 |
|
|
startnodeid NUMBER(20), -- start node for sub tree
|
242 |
|
|
endnodeid NUMBER(20), -- end node for sub tree
|
243 |
698
|
bojilova
|
CONSTRAINT xml_access_ck CHECK (begin_time < end_time),
|
244 |
|
|
CONSTRAINT xml_access_accessfileid_fk
|
245 |
|
|
FOREIGN KEY (accessfileid) REFERENCES xml_documents
|
246 |
72
|
bojilova
|
);
|
247 |
|
|
|
248 |
123
|
jones
|
/*
|
249 |
|
|
* Index of Nodes -- table to store precomputed paths through tree for
|
250 |
|
|
* quick searching in structured searches
|
251 |
|
|
*/
|
252 |
|
|
CREATE TABLE xml_index (
|
253 |
|
|
nodeid NUMBER(20), -- the unique node id
|
254 |
177
|
jones
|
path VARCHAR2(200), -- precomputed path through tree
|
255 |
317
|
bojilova
|
docid VARCHAR2(250), -- index to the document id
|
256 |
|
|
doctype VARCHAR2(100), -- public id indicating document type
|
257 |
825
|
bojilova
|
parentnodeid NUMBER(20), -- id of the parent of the node
|
258 |
|
|
-- represented by this row
|
259 |
123
|
jones
|
CONSTRAINT xml_index_pk PRIMARY KEY (nodeid,path),
|
260 |
317
|
bojilova
|
CONSTRAINT xml_index_nodeid_fk FOREIGN KEY (nodeid) REFERENCES xml_nodes,
|
261 |
|
|
CONSTRAINT xml_index_docid_fk
|
262 |
|
|
FOREIGN KEY (docid) REFERENCES xml_documents
|
263 |
123
|
jones
|
);
|
264 |
163
|
bojilova
|
|
265 |
|
|
/*
|
266 |
177
|
jones
|
* Index of the paths in xml_index
|
267 |
|
|
*/
|
268 |
|
|
CREATE INDEX xml_index_idx1 ON xml_index (path);
|
269 |
|
|
|
270 |
472
|
berkley
|
CREATE TABLE xml_relation (
|
271 |
689
|
bojilova
|
relationid NUMBER(20) PRIMARY KEY, -- unique id
|
272 |
743
|
jones
|
docid VARCHAR2(250), -- the docid of the package file
|
273 |
689
|
bojilova
|
-- that this relation came from
|
274 |
743
|
jones
|
packagetype VARCHAR2(250), -- the type of the package
|
275 |
689
|
bojilova
|
subject VARCHAR2(512) NOT NULL, -- the subject of the relation
|
276 |
743
|
jones
|
subdoctype VARCHAR2(128), -- the doctype of the subject
|
277 |
689
|
bojilova
|
relationship VARCHAR2(128) NOT NULL,-- the relationship type
|
278 |
|
|
object VARCHAR2(512) NOT NULL, -- the object of the relation
|
279 |
|
|
objdoctype VARCHAR2(128), -- the doctype of the object
|
280 |
1604
|
tao
|
CONSTRAINT xml_relation_uk UNIQUE (docid, subject, relationship, object),
|
281 |
689
|
bojilova
|
CONSTRAINT xml_relation_docid_fk
|
282 |
|
|
FOREIGN KEY (docid) REFERENCES xml_documents
|
283 |
472
|
berkley
|
);
|
284 |
|
|
|
285 |
|
|
CREATE SEQUENCE xml_relation_id_seq;
|
286 |
|
|
|
287 |
|
|
CREATE TRIGGER xml_relation_before_insert
|
288 |
|
|
BEFORE INSERT ON xml_relation FOR EACH ROW
|
289 |
|
|
BEGIN
|
290 |
|
|
SELECT xml_relation_id_seq.nextval
|
291 |
|
|
INTO :new.relationid
|
292 |
|
|
FROM dual;
|
293 |
759
|
bojilova
|
END;
|
294 |
472
|
berkley
|
/
|
295 |
|
|
|
296 |
759
|
bojilova
|
/*
|
297 |
|
|
* Table used as Unique ID generator for the uniqueid part of Accession#
|
298 |
|
|
*/
|
299 |
|
|
CREATE SEQUENCE accession_number_id_seq;
|
300 |
|
|
CREATE TABLE accession_number (
|
301 |
|
|
uniqueid NUMBER(20) PRIMARY KEY,
|
302 |
|
|
site_code VARCHAR2(100),
|
303 |
|
|
date_created DATE
|
304 |
|
|
);
|
305 |
|
|
CREATE TRIGGER accession_number_before_insert
|
306 |
|
|
BEFORE INSERT ON accession_number FOR EACH ROW
|
307 |
|
|
BEGIN
|
308 |
|
|
SELECT accession_number_id_seq.nextval
|
309 |
|
|
INTO :new.uniqueid
|
310 |
|
|
FROM dual;
|
311 |
|
|
END;
|
312 |
|
|
/
|
313 |
|
|
|
314 |
1530
|
tao
|
/*
|
315 |
1589
|
tao
|
* accesssubtree -- table to store access subtree info
|
316 |
1530
|
tao
|
*/
|
317 |
|
|
CREATE TABLE xml_accesssubtree (
|
318 |
|
|
docid VARCHAR2(250), -- the document id #
|
319 |
|
|
rev NUMBER(10) DEFAULT 1, --the revision number of the docume
|
320 |
|
|
controllevel VARCHAR2(50), -- the level it control -- document or subtree
|
321 |
|
|
subtreeid VARCHAR2(250), -- the subtree id
|
322 |
|
|
startnodeid NUMBER(20), -- the start node id of access subtree
|
323 |
1625
|
tao
|
endnodeid NUMBER(20), -- the end node if of access subtree
|
324 |
|
|
CONSTRAINT xml_accesssubtree_docid_fk
|
325 |
|
|
FOREIGN KEY (docid) REFERENCES xml_documents
|
326 |
1530
|
tao
|
);
|
327 |
|
|
|
328 |
2099
|
jones
|
/*
|
329 |
|
|
* Logging -- table to store metadata and data access log
|
330 |
|
|
*/
|
331 |
|
|
CREATE TABLE access_log (
|
332 |
|
|
entryid NUMBER(20), -- the identifier for the log event
|
333 |
|
|
ip_address VARCHAR2(512), -- the ip address inititiating the event
|
334 |
|
|
principal VARCHAR2(512), -- the user initiiating the event
|
335 |
|
|
docid VARCHAR2(250), -- the document id #
|
336 |
|
|
event VARCHAR2(512), -- the code symbolizing the event type
|
337 |
|
|
date_logged DATE, -- the datetime on which the event occurred
|
338 |
|
|
CONSTRAINT access_log_pk PRIMARY KEY (entryid)
|
339 |
|
|
);
|
340 |
|
|
|
341 |
|
|
CREATE SEQUENCE access_log_id_seq;
|
342 |
|
|
CREATE TRIGGER access_log_before_insert
|
343 |
|
|
BEFORE INSERT ON access_log FOR EACH ROW
|
344 |
|
|
BEGIN
|
345 |
|
|
SELECT access_log_id_seq.nextval
|
346 |
|
|
INTO :new.entryid
|
347 |
|
|
FROM dual;
|
348 |
|
|
END;
|
349 |
2109
|
costa
|
|
350 |
|
|
/*
|
351 |
|
|
* harvest_site_schedule -- table to store harvest sites and schedule info
|
352 |
|
|
*/
|
353 |
|
|
CREATE TABLE harvest_site_schedule (
|
354 |
|
|
site_schedule_id NUMBER, -- unique id
|
355 |
|
|
documentlisturl VARCHAR2(255), -- URL of the site harvest document list
|
356 |
|
|
ldapdn VARCHAR2(255), -- LDAP distinguished name for site account
|
357 |
|
|
datenextharvest DATE, -- scheduled date of next harvest
|
358 |
|
|
datelastharvest DATE, -- recorded date of last harvest
|
359 |
|
|
updatefrequency NUMBER, -- the harvest update frequency
|
360 |
|
|
unit VARCHAR2(50), -- update unit -- days weeks or months
|
361 |
|
|
contact_email VARCHAR2(50), -- email address of the site contact person
|
362 |
|
|
ldappwd VARCHAR2(20), -- LDAP password for site account
|
363 |
|
|
CONSTRAINT harvest_site_schedule_pk PRIMARY KEY (site_schedule_id)
|
364 |
|
|
);
|
365 |
|
|
|
366 |
|
|
/*
|
367 |
|
|
* harvest_log -- table to log entries for harvest operations
|
368 |
|
|
*/
|
369 |
|
|
CREATE TABLE harvest_log (
|
370 |
|
|
harvest_log_id NUMBER, -- unique id
|
371 |
|
|
harvest_date DATE, -- date of the current harvest
|
372 |
|
|
status NUMBER, -- non-zero indicates an error status
|
373 |
|
|
message VARCHAR2(1000), -- text message for this log entry
|
374 |
|
|
harvest_operation_code VARCHAR2(30), -- the type of harvest operation
|
375 |
|
|
site_schedule_id NUMBER, -- foreign key
|
376 |
|
|
CONSTRAINT harvest_log_pk PRIMARY KEY (harvest_log_id),
|
377 |
|
|
CONSTRAINT harvest_log_site_schedule_id_fk
|
378 |
|
|
FOREIGN KEY (site_schedule_id) REFERENCES harvest_site_schedule
|
379 |
|
|
);
|
380 |
|
|
|
381 |
|
|
/*
|
382 |
|
|
* harvest_detail_log -- table to log detailed info about documents that
|
383 |
|
|
* generated errors during the harvest
|
384 |
|
|
*/
|
385 |
|
|
CREATE TABLE harvest_detail_log (
|
386 |
|
|
detail_log_id NUMBER, -- unique id
|
387 |
|
|
harvest_log_id NUMBER, -- ponter to the related log entry
|
388 |
|
|
scope VARCHAR2(50), -- document scope
|
389 |
|
|
identifier NUMBER, -- document identifier
|
390 |
|
|
revision NUMBER, -- document revision
|
391 |
|
|
document_url VARCHAR2(255), -- document URL
|
392 |
|
|
error_message VARCHAR2(1000), -- text error message
|
393 |
|
|
document_type VARCHAR2(100), -- document type
|
394 |
|
|
CONSTRAINT harvest_detail_log_pk PRIMARY KEY (detail_log_id),
|
395 |
|
|
CONSTRAINT harvest_detail_log_harvest_log_id_fk
|
396 |
|
|
FOREIGN KEY (harvest_log_id) REFERENCES harvest_log
|
397 |
|
|
);
|