Project

General

Profile

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