Project

General

Profile

1
/*
2
 * 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
 *    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: xmltables.sql 633 2001-01-03 17:22:39Z berkley $'
13
 *
14
 */
15

    
16
/*
17
 * Drop all of the objects in proper order
18
 */
19
set echo off
20

    
21
DROP SEQUENCE xml_nodes_id_seq;
22
DROP SEQUENCE xml_revisions_id_seq;
23
DROP SEQUENCE xml_catalog_id_seq;
24
DROP SEQUENCE xml_relation_id_seq;
25
DROP SEQUENCE xml_replication_id_seq;
26
DROP SEQUENCE accnum_uniqueid_seq;
27

    
28
DROP TRIGGER xml_revisions_before_insert;
29
DROP TRIGGER xml_catalog_before_insert;
30
DROP TRIGGER xml_relation_before_insert;
31
DROP TRIGGER xml_replication_before_insert;
32

    
33
DROP TABLE xml_index;
34
DROP TABLE xml_catalog;
35
DROP TABLE xml_access;
36
DROP TABLE xml_revisions;
37
DROP TABLE xml_documents;
38
DROP TABLE xml_nodes;
39
DROP TABLE xml_acc_numbers;
40
DROP TABLE xml_relation;
41
DROP TABLE xml_replication;
42

    
43
/*
44
 *Replication -- table to store servers that metacat is replicated to
45
 */
46
CREATE TABLE xml_replication (
47
  serverid      NUMBER(20),
48
  server        VARCHAR2(512),
49
  last_checked  DATE,
50
  replicate     NUMBER(1), 
51
  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

    
64
INSERT INTO xml_replication (serverid, server, replicate) VALUES ('1', 'localhost', '0');
65

    
66
/* 
67
 * Nodes -- table to store XML Nodes (both elements and attributes)
68
 */
69
CREATE TABLE xml_nodes (
70
	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
	docid		VARCHAR2(250),	-- index to the document id
80
	date_created	DATE,
81
	date_updated	DATE,
82
   CONSTRAINT xml_nodes_pk PRIMARY KEY (nodeid),
83
   CONSTRAINT xml_nodes_root_fk 
84
		FOREIGN KEY (rootnodeid) REFERENCES xml_nodes,
85
   CONSTRAINT xml_nodes_parent_fk 
86
		FOREIGN KEY (parentnodeid) REFERENCES xml_nodes
87
);
88

    
89
/* 
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
CREATE SEQUENCE xml_nodes_id_seq;
96

    
97
/* 
98
 * Documents -- table to store XML documents
99
 */
100
CREATE TABLE xml_documents (
101
	docid		VARCHAR2(250),	-- the document id #
102
	rootnodeid	NUMBER(20),	-- reference to root node of the DOM
103
	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
	user_owner	VARCHAR2(100),	-- the user owned the document
107
	user_updated	VARCHAR2(100),	-- the user updated the document
108
  server_location NUMBER(20), -- the server on which this document resides
109
  rev NUMBER(10) DEFAULT 1,  --the revision number of the document
110
	date_created	DATE,
111
	date_updated	DATE,
112
	public_access	NUMBER(1) DEFAULT 1, -- flag for public access
113
  updated NUMBER(1) DEFAULT 0,
114
   CONSTRAINT xml_documents_pk PRIMARY KEY (docid),
115
   CONSTRAINT xml_documents_rep_fk
116
    FOREIGN KEY (server_location) REFERENCES xml_replication, 
117
   CONSTRAINT xml_documents_root_fk 
118
		FOREIGN KEY (rootnodeid) REFERENCES xml_nodes
119
);
120

    
121
/* 
122
 * Index of <docid,doctype> in xml_document
123
 */
124
CREATE INDEX xml_documents_idx1 ON xml_documents (docid, doctype);
125

    
126
/* 
127
 * ACL -- table to store ACL for XML documents by principals
128
 */
129
CREATE TABLE xml_access (
130
	docid		VARCHAR2(250),	-- the document id #
131
	principal_name	VARCHAR2(100),	-- name of user, group, etc.
132
	permission	NUMBER(1),	-- "read", "write", "all"
133
	perm_type	VARCHAR2(32),	-- "allowed" or "denied"
134
	perm_order	VARCHAR2(32),	-- "allow first" or "deny first"
135
	begin_time	DATE,		-- the time that permission begins
136
	end_time	DATE,		-- the time that permission ends
137
	ticket_count	NUMBER(5),	-- ticket counter for that permission
138
   CONSTRAINT xml_access_pk UNIQUE (docid, principal_name, permission, perm_type),
139
   CONSTRAINT xml_access_ck CHECK (begin_time < end_time)
140
);
141

    
142
/* 
143
 * Revised Documents -- table to store XML documents saved after an UPDATE
144
 *                    or DELETE
145
 */
146
CREATE TABLE xml_revisions (
147
	revisionid	NUMBER(20),	-- the revision number we are saving
148
	docid		VARCHAR2(250),	-- the document id #
149
	rootnodeid	NUMBER(20),	-- reference to root node of the DOM
150
	docname		VARCHAR2(100),	-- usually the root element name
151
	doctype		VARCHAR2(100),	-- public id indicating document type
152
	doctitle	VARCHAR2(1000),	-- title of document if exists
153
	user_owner	VARCHAR2(100),
154
	user_updated	VARCHAR2(100),
155
	server_location NUMBER(20),
156
  rev NUMBER(10),
157
	date_created	DATE,
158
	date_updated	DATE,
159
   CONSTRAINT xml_revisions_pk PRIMARY KEY (revisionid),
160
   CONSTRAINT xml_revisions_rep_fk
161
    FOREIGN KEY (server_location) REFERENCES xml_replication,
162
   CONSTRAINT xml_revisions_root_fk 
163
		FOREIGN KEY (rootnodeid) REFERENCES xml_nodes
164
);
165

    
166
CREATE SEQUENCE xml_revisions_id_seq;
167

    
168
CREATE TRIGGER xml_revisions_before_insert
169
BEFORE INSERT ON xml_revisions FOR EACH ROW
170
BEGIN
171
  SELECT xml_revisions_id_seq.nextval
172
    INTO :new.revisionid
173
    FROM dual;
174
END;
175
/
176

    
177
/* 
178
 * XML Catalog -- table to store all external sources for XML documents
179
 */
180
CREATE TABLE xml_catalog (
181
	catalog_id	NUMBER(20),	-- the id for this catalog entry
182
	entry_type	VARCHAR2(500),	-- the type of this catalog entry
183
					-- (e.g., DTD, XSD, XSL)
184
	source_doctype	VARCHAR2(500),	-- the source public_id for transforms
185
	target_doctype	VARCHAR2(500),	-- the target public_id for transforms
186
	public_id	VARCHAR2(500),	-- the unique id for this type
187
	system_id	VARCHAR2(1000),	-- the local location of the object
188
   CONSTRAINT xml_catalog_pk PRIMARY KEY (catalog_id),
189
   CONSTRAINT xml_catalog_uk UNIQUE (entry_type, source_doctype, target_doctype, public_id)
190
);
191

    
192
CREATE SEQUENCE xml_catalog_id_seq;
193

    
194
CREATE TRIGGER xml_catalog_before_insert
195
BEFORE INSERT ON xml_catalog FOR EACH ROW
196
BEGIN
197
  SELECT xml_catalog_id_seq.nextval
198
    INTO :new.catalog_id
199
    FROM dual;
200
END;
201
/
202

    
203
/* 
204
 * Index of Nodes -- table to store precomputed paths through tree for 
205
 * quick searching in structured searches
206
 */
207
CREATE TABLE xml_index (
208
	nodeid		NUMBER(20),	-- the unique node id
209
	path		VARCHAR2(200),	-- precomputed path through tree
210
	docid		VARCHAR2(250),	-- index to the document id
211
	doctype		VARCHAR2(100),	-- public id indicating document type
212
        parentnodeid    NUMBER(20),     -- id of the parent of the node represented by this row
213
   CONSTRAINT xml_index_pk PRIMARY KEY (nodeid,path),
214
   CONSTRAINT xml_index_nodeid_fk FOREIGN KEY (nodeid) REFERENCES xml_nodes,
215
   CONSTRAINT xml_index_docid_fk 
216
		FOREIGN KEY (docid) REFERENCES xml_documents
217
);
218

    
219
/* 
220
 * Index of the paths in xml_index 
221
 */
222
CREATE INDEX xml_index_idx1 ON xml_index (path);
223

    
224
/* 
225
 * Sequence to get uniqueID for Accession #
226
 */
227
CREATE SEQUENCE accnum_uniqueid_seq;
228

    
229
CREATE TABLE xml_relation (
230
  relationid    NUMBER(20) PRIMARY KEY,  -- unique id
231
  docid         VARCHAR2(250) ,         -- the docid of the package file that
232
                                        -- this relation came from
233
  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
  );
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
/
251

    
(11-11/11)