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 550 2000-11-16 23:32:12Z 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

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

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

    
42
/*
43
 *Replication -- table to store servers that metacat is replicated to
44
 */
45
CREATE TABLE xml_replication (
46
  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

    
62
INSERT INTO xml_replication (serverid, server) VALUES ('1', 'localhost');
63

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

    
87
/* 
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
CREATE SEQUENCE xml_nodes_id_seq;
94

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

    
117
/* 
118
 * Index of <docid,doctype> in xml_document
119
 */
120
CREATE INDEX xml_documents_idx1 ON xml_documents (docid, doctype);
121

    
122
/* 
123
 * ACL -- table to store ACL for XML documents by principals
124
 */
125
CREATE TABLE xml_access (
126
	docid		VARCHAR2(250),	-- the document id #
127
	principal_name	VARCHAR2(100),	-- name of user, user group, etc.
128
	principal_type	VARCHAR2(20),	-- like "user", "group", etc.
129
	access_type	NUMBER(1),	-- "read", "write", "all"
130
	begin_time	DATE,		-- the time that access permission begins
131
	end_time	DATE,		-- the time that access permission ends
132
   CONSTRAINT xml_access_pk UNIQUE (docid, principal_name, principal_type, access_type, begin_time),
133
   CONSTRAINT xml_access_ck CHECK (begin_time < end_time)
134
);
135

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

    
156
CREATE SEQUENCE xml_revisions_id_seq;
157

    
158
CREATE TRIGGER xml_revisions_before_insert
159
BEFORE INSERT ON xml_revisions FOR EACH ROW
160
BEGIN
161
  SELECT xml_revisions_id_seq.nextval
162
    INTO :new.revisionid
163
    FROM dual;
164
END;
165
/
166

    
167
/* 
168
 * XML Catalog -- table to store all external sources for XML documents
169
 */
170
CREATE TABLE xml_catalog (
171
	catalog_id	NUMBER(20),	-- the id for this catalog entry
172
	entry_type	VARCHAR2(500),	-- the type of this catalog entry
173
					-- (e.g., DTD, XSD, XSL)
174
	source_doctype	VARCHAR2(500),	-- the source public_id for transforms
175
	target_doctype	VARCHAR2(500),	-- the target public_id for transforms
176
	public_id	VARCHAR2(500),	-- the unique id for this type
177
	system_id	VARCHAR2(1000),	-- the local location of the object
178
   CONSTRAINT xml_catalog_pk PRIMARY KEY (catalog_id)
179
   -- CONSTRAINT xml_catalog_uk UNIQUE (entry_type, source_doctype, target_doctype, public_id)
180
);
181

    
182
CREATE SEQUENCE xml_catalog_id_seq;
183

    
184
CREATE TRIGGER xml_catalog_before_insert
185
BEFORE INSERT ON xml_catalog FOR EACH ROW
186
BEGIN
187
  SELECT xml_catalog_id_seq.nextval
188
    INTO :new.catalog_id
189
    FROM dual;
190
END;
191
/
192

    
193
/* 
194
 * Index of Nodes -- table to store precomputed paths through tree for 
195
 * quick searching in structured searches
196
 */
197
CREATE TABLE xml_index (
198
	nodeid		NUMBER(20),	-- the unique node id
199
	path		VARCHAR2(200),	-- precomputed path through tree
200
	docid		VARCHAR2(250),	-- index to the document id
201
	doctype		VARCHAR2(100),	-- public id indicating document type
202
        parentnodeid    NUMBER(20),     -- id of the parent of the node represented by this row
203
   CONSTRAINT xml_index_pk PRIMARY KEY (nodeid,path),
204
   CONSTRAINT xml_index_nodeid_fk FOREIGN KEY (nodeid) REFERENCES xml_nodes,
205
   CONSTRAINT xml_index_docid_fk 
206
		FOREIGN KEY (docid) REFERENCES xml_documents
207
);
208

    
209
/* 
210
 * Index of the paths in xml_index 
211
 */
212
CREATE INDEX xml_index_idx1 ON xml_index (path);
213

    
214
/* 
215
 * table to store unique Accession # for every document in 2 parts
216
 */
217
CREATE TABLE xml_acc_numbers (
218
	global_name	VARCHAR2(32),	-- first part of acc #
219
	local_id	NUMBER(20),	-- second part - unique in global name
220
   CONSTRAINT xml_acc_numbers_pk PRIMARY KEY (global_name, local_id)
221
);
222

    
223
CREATE TABLE xml_relation (
224
  relationid    NUMBER(20) PRIMARY KEY,  -- unique id
225
  subject       VARCHAR2(512) NOT NULL, -- the subject of the relation
226
  subdoctype    VARCHAR2(128),           -- the doctype of the subject
227
  relationship  VARCHAR2(128)  NOT NULL, -- the relationship type
228
  object        VARCHAR2(512) NOT NULL, -- the object of the relation
229
  objdoctype    VARCHAR2(128),            -- the doctype of the object
230
  CONSTRAINT xml_relation_unk UNIQUE (subject, relationship, object)
231
  );
232

    
233
CREATE SEQUENCE xml_relation_id_seq;
234
  
235
CREATE TRIGGER xml_relation_before_insert
236
BEFORE INSERT ON xml_relation FOR EACH ROW
237
BEGIN
238
  SELECT xml_relation_id_seq.nextval
239
    INTO :new.relationid
240
    FROM dual;
241
END;
242
/
243

    
(10-10/10)