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 414 2000-08-28 18:38:27Z bojilova $'
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

    
25
DROP TRIGGER xml_revisions_before_insert;
26
DROP TRIGGER xml_catalog_before_insert;
27

    
28
DROP TABLE xml_index;
29
DROP TABLE xml_catalog;
30
DROP TABLE xml_access;
31
DROP TABLE xml_revisions;
32
DROP TABLE xml_documents;
33
DROP TABLE xml_nodes;
34
DROP TABLE xml_acc_numbers;
35

    
36
/* 
37
 * Nodes -- table to store XML Nodes (both elements and attributes)
38
 */
39
CREATE TABLE xml_nodes (
40
	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
	docid		VARCHAR2(250),	-- index to the document id
50
	date_created	DATE,
51
	date_updated	DATE,
52
   CONSTRAINT xml_nodes_pk PRIMARY KEY (nodeid),
53
   CONSTRAINT xml_nodes_root_fk 
54
		FOREIGN KEY (rootnodeid) REFERENCES xml_nodes,
55
   CONSTRAINT xml_nodes_parent_fk 
56
		FOREIGN KEY (parentnodeid) REFERENCES xml_nodes
57
);
58

    
59
CREATE SEQUENCE xml_nodes_id_seq;
60

    
61
/* 
62
 * Documents -- table to store XML documents
63
 */
64
CREATE TABLE xml_documents (
65
	docid		VARCHAR2(250),	-- the document id #
66
	rootnodeid	NUMBER(20),	-- reference to root node of the DOM
67
	docname		VARCHAR2(100),	-- usually the root element name
68
	doctype		VARCHAR2(100),	-- public id indicating document type
69
	doctitle	VARCHAR2(1000),	-- title of document if exists
70
	user_owner	VARCHAR2(100),	-- the user owned the document
71
	user_updated	VARCHAR2(100),	-- the user updated the document
72
	date_created	DATE,
73
	date_updated	DATE,
74
	public_access	NUMBER(1),	-- flag for public access
75
   CONSTRAINT xml_documents_pk PRIMARY KEY (docid),
76
   CONSTRAINT xml_documents_root_fk 
77
		FOREIGN KEY (rootnodeid) REFERENCES xml_nodes
78
);
79

    
80
/* 
81
 * ACL -- table to store ACL for XML documents by principals
82
 */
83
CREATE TABLE xml_access (
84
	docid		VARCHAR2(250),	-- the document id #
85
	principal_name	VARCHAR2(100),	-- name of user, user group, etc.
86
	principal_type	VARCHAR2(20),	-- like "user", "group", etc.
87
	access_type	NUMBER(1),	-- "read", "write", "all"
88
	begin_time	DATE,		-- the time that access permission begins
89
	end_time	DATE,		-- the time that access permission ends
90
   CONSTRAINT xml_access_pk UNIQUE (docid, principal_name, principal_type, access_type, begin_time),
91
   CONSTRAINT xml_access_docid_fk 
92
		FOREIGN KEY (docid) REFERENCES xml_documents,
93
   CONSTRAINT xml_access_ck CHECK (begin_time < end_time)
94
);
95

    
96
/* 
97
 * Revised Documents -- table to store XML documents saved after an UPDATE
98
 *                    or DELETE
99
 */
100
CREATE TABLE xml_revisions (
101
	revisionid	NUMBER(20),	-- the revision number we are saving
102
	docid		VARCHAR2(250),	-- the document id #
103
	rootnodeid	NUMBER(20),	-- reference to root node of the DOM
104
	docname		VARCHAR2(100),	-- usually the root element name
105
	doctype		VARCHAR2(100),	-- public id indicating document type
106
	doctitle	VARCHAR2(1000),	-- title of document if exists
107
	user_owner	VARCHAR2(100),
108
	user_updated	VARCHAR2(100),
109
	date_created	DATE,
110
	date_updated	DATE,
111
   CONSTRAINT xml_revisions_pk PRIMARY KEY (revisionid),
112
   CONSTRAINT xml_revisions_root_fk 
113
		FOREIGN KEY (rootnodeid) REFERENCES xml_nodes
114
);
115

    
116
CREATE SEQUENCE xml_revisions_id_seq;
117

    
118
CREATE TRIGGER xml_revisions_before_insert
119
BEFORE INSERT ON xml_revisions FOR EACH ROW
120
BEGIN
121
  SELECT xml_revisions_id_seq.nextval
122
    INTO :new.revisionid
123
    FROM dual;
124
END;
125
/
126

    
127
/* 
128
 * XML Catalog -- table to store all external sources for XML documents
129
 */
130
CREATE TABLE xml_catalog (
131
	catalog_id	NUMBER(20),	-- the id for this catalog entry
132
	entry_type	VARCHAR2(500),	-- the type of this catalog entry
133
					-- (e.g., DTD, XSD, XSL)
134
	source_doctype	VARCHAR2(500),	-- the source public_id for transforms
135
	target_doctype	VARCHAR2(500),	-- the target public_id for transforms
136
	public_id	VARCHAR2(500),	-- the unique id for this type
137
	system_id	VARCHAR2(1000),	-- the local location of the object
138
   CONSTRAINT xml_catalog_pk PRIMARY KEY (catalog_id)
139
   -- CONSTRAINT xml_catalog_uk UNIQUE (entry_type, source_doctype, target_doctype, public_id)
140
);
141

    
142
CREATE SEQUENCE xml_catalog_id_seq;
143

    
144
CREATE TRIGGER xml_catalog_before_insert
145
BEFORE INSERT ON xml_catalog FOR EACH ROW
146
BEGIN
147
  SELECT xml_catalog_id_seq.nextval
148
    INTO :new.catalog_id
149
    FROM dual;
150
END;
151
/
152

    
153
/* 
154
 * Index of Nodes -- table to store precomputed paths through tree for 
155
 * quick searching in structured searches
156
 */
157
CREATE TABLE xml_index (
158
	nodeid		NUMBER(20),	-- the unique node id
159
	path		VARCHAR2(200),	-- precomputed path through tree
160
	docid		VARCHAR2(250),	-- index to the document id
161
	doctype		VARCHAR2(100),	-- public id indicating document type
162
   CONSTRAINT xml_index_pk PRIMARY KEY (nodeid,path),
163
   CONSTRAINT xml_index_nodeid_fk FOREIGN KEY (nodeid) REFERENCES xml_nodes,
164
   CONSTRAINT xml_index_docid_fk 
165
		FOREIGN KEY (docid) REFERENCES xml_documents
166
);
167

    
168
/* 
169
 * Index of the paths in xml_index 
170
 */
171
CREATE INDEX xml_index_idx1 ON xml_index (path);
172

    
173
/* 
174
 * table to store unique Accession # for every document in 2 parts
175
 */
176
CREATE TABLE xml_acc_numbers (
177
	global_name	VARCHAR2(32),	-- first part of acc #
178
	local_id	NUMBER(20),	-- second part - unique in global name
179
   CONSTRAINT xml_acc_numbers_pk PRIMARY KEY (global_name, local_id)
180
);
(9-9/9)