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 72 bojilova
25 203 jones
DROP TRIGGER xml_revisions_before_insert;
26 93 bojilova
DROP TRIGGER xml_catalog_before_insert;
27 72 bojilova
28 123 jones
DROP TABLE xml_index;
29 72 bojilova
DROP TABLE xml_catalog;
30 414 bojilova
DROP TABLE xml_access;
31
DROP TABLE xml_revisions;
32 5 jones
DROP TABLE xml_documents;
33 20 jones
DROP TABLE xml_nodes;
34 163 bojilova
DROP TABLE xml_acc_numbers;
35 3 jones
36
/*
37 20 jones
 * Nodes -- table to store XML Nodes (both elements and attributes)
38 3 jones
 */
39 20 jones
CREATE TABLE xml_nodes (
40 123 jones
	nodeid		NUMBER(20),	-- the unique node id (pk)
41
	nodeindex	NUMBER(10),	-- order of nodes within parent
42
	nodetype	VARCHAR2(20),	-- type (DOCUMENT, COMMENT, PI,
43
					-- ELEMENT, ATTRIBUTE, TEXT)
44
	nodename	VARCHAR2(250),	-- the name of an element or attribute
45
	nodedata	VARCHAR2(4000), -- the data for this node (e.g.,
46
					-- for TEXT it is the content)
47
	parentnodeid	NUMBER(20),	-- index of the parent of this node
48
	rootnodeid	NUMBER(20),	-- index of the root node of this tree
49 317 bojilova
	docid		VARCHAR2(250),	-- index to the document id
50 3 jones
	date_created	DATE,
51
	date_updated	DATE,
52 20 jones
   CONSTRAINT xml_nodes_pk PRIMARY KEY (nodeid),
53 123 jones
   CONSTRAINT xml_nodes_root_fk
54
		FOREIGN KEY (rootnodeid) REFERENCES xml_nodes,
55 20 jones
   CONSTRAINT xml_nodes_parent_fk
56
		FOREIGN KEY (parentnodeid) REFERENCES xml_nodes
57 3 jones
);
58
59 440 bojilova
/*
60
 * Indexes of rootnodeid & parentnodeid in xml_nodes
61
 */
62
CREATE INDEX xml_nodes_idx1 ON xml_nodes (rootnodeid);
63
CREATE INDEX xml_nodes_idx2 ON xml_nodes (parentnodeid);
64
65 20 jones
CREATE SEQUENCE xml_nodes_id_seq;
66 19 jones
67 3 jones
/*
68 72 bojilova
 * Documents -- table to store XML documents
69 5 jones
 */
70
CREATE TABLE xml_documents (
71 165 jones
	docid		VARCHAR2(250),	-- the document id #
72
	rootnodeid	NUMBER(20),	-- reference to root node of the DOM
73 123 jones
	docname		VARCHAR2(100),	-- usually the root element name
74
	doctype		VARCHAR2(100),	-- public id indicating document type
75
	doctitle	VARCHAR2(1000),	-- title of document if exists
76 414 bojilova
	user_owner	VARCHAR2(100),	-- the user owned the document
77
	user_updated	VARCHAR2(100),	-- the user updated the document
78 5 jones
	date_created	DATE,
79
	date_updated	DATE,
80 445 bojilova
	public_access	NUMBER(1) DEFAULT 1, -- flag for public access
81 5 jones
   CONSTRAINT xml_documents_pk PRIMARY KEY (docid),
82
   CONSTRAINT xml_documents_root_fk
83 20 jones
		FOREIGN KEY (rootnodeid) REFERENCES xml_nodes
84 5 jones
);
85
86 72 bojilova
/*
87 440 bojilova
 * Index of <docid,doctype> in xml_document
88
 */
89
CREATE INDEX xml_documents_idx1 ON xml_documents (docid, doctype);
90
91
/*
92 414 bojilova
 * ACL -- table to store ACL for XML documents by principals
93
 */
94
CREATE TABLE xml_access (
95
	docid		VARCHAR2(250),	-- the document id #
96
	principal_name	VARCHAR2(100),	-- name of user, user group, etc.
97
	principal_type	VARCHAR2(20),	-- like "user", "group", etc.
98
	access_type	NUMBER(1),	-- "read", "write", "all"
99
	begin_time	DATE,		-- the time that access permission begins
100
	end_time	DATE,		-- the time that access permission ends
101
   CONSTRAINT xml_access_pk UNIQUE (docid, principal_name, principal_type, access_type, begin_time),
102
   CONSTRAINT xml_access_ck CHECK (begin_time < end_time)
103
);
104
105
/*
106 203 jones
 * Revised Documents -- table to store XML documents saved after an UPDATE
107
 *                    or DELETE
108
 */
109
CREATE TABLE xml_revisions (
110
	revisionid	NUMBER(20),	-- the revision number we are saving
111
	docid		VARCHAR2(250),	-- the document id #
112
	rootnodeid	NUMBER(20),	-- reference to root node of the DOM
113
	docname		VARCHAR2(100),	-- usually the root element name
114
	doctype		VARCHAR2(100),	-- public id indicating document type
115
	doctitle	VARCHAR2(1000),	-- title of document if exists
116 414 bojilova
	user_owner	VARCHAR2(100),
117
	user_updated	VARCHAR2(100),
118 203 jones
	date_created	DATE,
119
	date_updated	DATE,
120
   CONSTRAINT xml_revisions_pk PRIMARY KEY (revisionid),
121
   CONSTRAINT xml_revisions_root_fk
122
		FOREIGN KEY (rootnodeid) REFERENCES xml_nodes
123
);
124
125
CREATE SEQUENCE xml_revisions_id_seq;
126
127
CREATE TRIGGER xml_revisions_before_insert
128
BEFORE INSERT ON xml_revisions FOR EACH ROW
129
BEGIN
130
  SELECT xml_revisions_id_seq.nextval
131
    INTO :new.revisionid
132
    FROM dual;
133
END;
134
/
135
136
/*
137 93 bojilova
 * XML Catalog -- table to store all external sources for XML documents
138 72 bojilova
 */
139
CREATE TABLE xml_catalog (
140 123 jones
	catalog_id	NUMBER(20),	-- the id for this catalog entry
141
	entry_type	VARCHAR2(500),	-- the type of this catalog entry
142
					-- (e.g., DTD, XSD, XSL)
143
	source_doctype	VARCHAR2(500),	-- the source public_id for transforms
144
	target_doctype	VARCHAR2(500),	-- the target public_id for transforms
145
	public_id	VARCHAR2(500),	-- the unique id for this type
146
	system_id	VARCHAR2(1000),	-- the local location of the object
147
   CONSTRAINT xml_catalog_pk PRIMARY KEY (catalog_id)
148
   -- CONSTRAINT xml_catalog_uk UNIQUE (entry_type, source_doctype, target_doctype, public_id)
149 72 bojilova
);
150
151 123 jones
CREATE SEQUENCE xml_catalog_id_seq;
152 72 bojilova
153 93 bojilova
CREATE TRIGGER xml_catalog_before_insert
154
BEFORE INSERT ON xml_catalog FOR EACH ROW
155 72 bojilova
BEGIN
156 123 jones
  SELECT xml_catalog_id_seq.nextval
157
    INTO :new.catalog_id
158 72 bojilova
    FROM dual;
159
END;
160
/
161 123 jones
162
/*
163
 * Index of Nodes -- table to store precomputed paths through tree for
164
 * quick searching in structured searches
165
 */
166
CREATE TABLE xml_index (
167
	nodeid		NUMBER(20),	-- the unique node id
168 177 jones
	path		VARCHAR2(200),	-- precomputed path through tree
169 317 bojilova
	docid		VARCHAR2(250),	-- index to the document id
170
	doctype		VARCHAR2(100),	-- public id indicating document type
171 450 berkley
        parentnodeid    NUMBER(20),     -- id of the parent of the node represented by this row
172 123 jones
   CONSTRAINT xml_index_pk PRIMARY KEY (nodeid,path),
173 317 bojilova
   CONSTRAINT xml_index_nodeid_fk FOREIGN KEY (nodeid) REFERENCES xml_nodes,
174
   CONSTRAINT xml_index_docid_fk
175
		FOREIGN KEY (docid) REFERENCES xml_documents
176 123 jones
);
177 163 bojilova
178
/*
179 177 jones
 * Index of the paths in xml_index
180
 */
181
CREATE INDEX xml_index_idx1 ON xml_index (path);
182
183
/*
184 163 bojilova
 * table to store unique Accession # for every document in 2 parts
185
 */
186
CREATE TABLE xml_acc_numbers (
187
	global_name	VARCHAR2(32),	-- first part of acc #
188 166 jones
	local_id	NUMBER(20),	-- second part - unique in global name
189 163 bojilova
   CONSTRAINT xml_acc_numbers_pk PRIMARY KEY (global_name, local_id)
190
);