Project

General

Profile

Revision 741

Added by harris about 20 years ago

this is sql script does the same as the sql script named
xmltables.sql except that this script is to be use to
create the database tables on a Postgresql backend rather
than an Oracle Backend

the foreign-key constraints still need to be fixed

View differences:

src/xmltables_postgres.sql
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: 08 May 2001 
7
 *       Author: John Harris
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
 *
12
 *	this is sql script does the same as the sql script named 
13
 *	xmltables.sql except that this script is to be use to 
14
 *	create the database tables on a Postgresql backend rather
15
 *	than an Oracle Backend
16
 */
17

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

  
23
DROP SEQUENCE xml_nodes_id_seq;
24
DROP SEQUENCE xml_revisions_id_seq;
25
DROP SEQUENCE xml_catalog_id_seq;
26
DROP SEQUENCE xml_relation_id_seq;
27
DROP SEQUENCE xml_replication_id_seq;
28
DROP SEQUENCE accnum_uniqueid_seq;
29

  
30

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

  
40
/*
41
 *Replication -- table to store servers that metacat is replicated to
42
 */
43

  
44
CREATE SEQUENCE xml_replication_id_seq;
45

  
46
CREATE TABLE xml_replication (
47
  serverid     INT8  default nextval('xml_replication_id_seq'), 
48
  server        VARCHAR(512),
49
  last_checked  DATE,
50
  replicate     INT8, 
51
  CONSTRAINT xml_replication_pk PRIMARY KEY (serverid)
52
);  
53

  
54
INSERT INTO xml_replication (serverid, server, replicate) VALUES ('1', 'localhost', '0');
55

  
56
/* 
57
 * Nodes -- table to store XML Nodes (both elements and attributes)
58
 */
59
CREATE TABLE xml_nodes (
60
	nodeid		INT8, 		-- the unique node id (pk)
61
	nodeindex	INT8,		-- order of nodes within parent
62
	nodetype	VARCHAR(20),	-- type (DOCUMENT, COMMENT, PI,
63
					-- ELEMENT, ATTRIBUTE, TEXT)
64
	nodename	VARCHAR(250),	-- the name of an element or attribute
65
	nodedata	VARCHAR(4000), 	-- the data for this node (e.g., 
66
					-- for TEXT it is the content)
67
	parentnodeid	INT8,		-- index of the parent of this node
68
	rootnodeid	INT8,		-- index of the root node of this tree
69
	docid		VARCHAR(250),	-- index to the document id
70
	date_created	DATE,
71
	date_updated	DATE,
72
   CONSTRAINT xml_nodes_pk PRIMARY KEY (nodeid),
73
   CONSTRAINT xml_nodes_root_fk 
74
		FOREIGN KEY (rootnodeid) REFERENCES xml_nodes,
75
   CONSTRAINT xml_nodes_parent_fk 
76
		FOREIGN KEY (parentnodeid) REFERENCES xml_nodes
77
);
78

  
79
/* 
80
 * Indexes of rootnodeid & parentnodeid in xml_nodes
81
 */
82
CREATE INDEX xml_nodes_idx1 ON xml_nodes (rootnodeid);
83
CREATE INDEX xml_nodes_idx2 ON xml_nodes (parentnodeid);
84
CREATE SEQUENCE xml_nodes_id_seq;
85

  
86

  
87
/* 
88
 * XML Catalog -- table to store all external sources for XML documents
89
 */
90
CREATE SEQUENCE xml_catalog_id_seq;
91

  
92
CREATE TABLE xml_catalog (
93
	catalog_id INT8 default nextval('xml_catalog_id_seq'), -- the id for this catalog entry
94
	entry_type	VARCHAR(500),	-- the type of this catalog entry
95
					-- (e.g., DTD, XSD, XSL)
96
	source_doctype	VARCHAR(500),	-- the source public_id for transforms
97
	target_doctype	VARCHAR(500),	-- the target public_id for transforms
98
	public_id	VARCHAR(500),	-- the unique id for this type
99
	system_id	VARCHAR(1000),	-- the local location of the object
100
   CONSTRAINT xml_catalog_pk PRIMARY KEY (catalog_id),
101
   CONSTRAINT xml_catalog_uk UNIQUE  (entry_type, source_doctype, target_doctype, public_id)
102
);
103

  
104

  
105

  
106
/* 
107
 * Documents -- table to store XML documents
108
 */
109
CREATE TABLE xml_documents (
110
	docid		VARCHAR(250),	-- the document id #
111
	rootnodeid	INT8,	-- reference to root node of the DOM
112
	docname		VARCHAR(100),	-- usually the root element name
113
	doctype		VARCHAR(100),	-- public id indicating document type
114
	user_owner	VARCHAR(100),	-- the user owned the document
115
	user_updated	VARCHAR(100),	-- the user updated the document
116
	server_location INT8,	-- the server on which this document resides
117
	rev 		INT8,   --the revision number of the document
118
	date_created	DATE,
119
	date_updated	DATE,
120
	public_access	INT8,	-- flag for public access
121
        catalog_id      INT8,	-- reference to xml_catalog 
122
     CONSTRAINT xml_documents_pk PRIMARY KEY (docid),
123
     CONSTRAINT xml_documents_rep_fk
124
     		FOREIGN KEY (server_location) REFERENCES xml_replication, 
125
    CONSTRAINT xml_documents_root_fk 
126
		FOREIGN KEY (rootnodeid) REFERENCES xml_nodes,
127
   CONSTRAINT xml_documents_catalog_fk 
128
		FOREIGN KEY (catalog_id) REFERENCES xml_catalog
129
);
130

  
131
/* 
132
 * Index of <docid,doctype> in xml_document
133
 */
134
CREATE INDEX xml_documents_idx1 ON xml_documents (docid, doctype);
135

  
136
/* 
137
 * Revised Documents -- table to store XML documents saved after an UPDATE
138
 *                    or DELETE
139
 */
140

  
141

  
142
CREATE SEQUENCE xml_revisions_id_seq;
143
CREATE TABLE xml_revisions (
144
	revisionid	INT8  default nextval('xml_revisions_id_seq'), -- the revision number we are saving
145
	docid		VARCHAR(250),	-- the document id #
146
	rootnodeid	INT8,	-- reference to root node of the DOM
147
	docname		VARCHAR(100),	-- usually the root element name
148
	doctype		VARCHAR(100),	-- public id indicating document type
149
	user_owner	VARCHAR(100),
150
	user_updated	VARCHAR(100),
151
	server_location INT8,
152
	rev		INT8,
153
	date_created	DATE,
154
	date_updated	DATE,
155
	public_access	INT8,	-- flag for public access
156
        catalog_id      INT8,	-- reference to xml_catalog 
157
   CONSTRAINT xml_revisions_pk PRIMARY KEY (revisionid),
158
   CONSTRAINT xml_revisions_rep_fk
159
		FOREIGN KEY (server_location) REFERENCES xml_replication,
160
   CONSTRAINT xml_revisions_root_fk 
161
		FOREIGN KEY (rootnodeid) REFERENCES xml_nodes,
162
   CONSTRAINT xml_revisions_catalog_fk 
163
		FOREIGN KEY (catalog_id) REFERENCES xml_catalog
164
);
165

  
166

  
167
/* 
168
 * ACL -- table to store ACL for XML documents by principals
169
 */
170
CREATE TABLE xml_access (
171
	docid		VARCHAR(250),	-- the document id #
172
	accessfileid	VARCHAR(250),	-- the document id # for the access file
173
	principal_name	VARCHAR(100),	-- name of user, group, etc.
174
	permission	INT8,		-- "read", "write", "all"
175
	perm_type	VARCHAR(32),	-- "allowed" or "denied"
176
	perm_order	VARCHAR(32),	-- "allow first" or "deny first"
177
	begin_time	DATE,		-- the time that permission begins
178
	end_time	DATE,		-- the time that permission ends
179
	ticket_count	INT8,		-- ticket counter for that permission
180
   CONSTRAINT xml_access_ck CHECK (begin_time < end_time),
181
   CONSTRAINT xml_access_accessfileid_fk 
182
		FOREIGN KEY (accessfileid) REFERENCES xml_documents
183
);
184

  
185
/* 
186
 * Index of Nodes -- table to store precomputed paths through tree for 
187
 * quick searching in structured searches
188
 */
189
CREATE TABLE xml_index (
190
	nodeid		INT8,		-- the unique node id
191
	path		VARCHAR(200),	-- precomputed path through tree
192
	docid		VARCHAR(250),	-- index to the document id
193
	doctype		VARCHAR(100),	-- public id indicating document type
194
        parentnodeid    INT8,     -- id of the parent of the node represented
195
					-- by this row
196
   CONSTRAINT xml_index_pk PRIMARY KEY (nodeid,path),
197
   CONSTRAINT xml_index_nodeid_fk FOREIGN KEY (nodeid) REFERENCES xml_nodes,
198
   CONSTRAINT xml_index_docid_fk 
199
		FOREIGN KEY (docid) REFERENCES xml_documents
200
);
201

  
202
/* 
203
 * Index of the paths in xml_index 
204
 */
205
CREATE INDEX xml_index_idx1 ON xml_index (path);
206

  
207
/* 
208
 * Sequence to get uniqueID for Accession #
209
 */
210
CREATE SEQUENCE accnum_uniqueid_seq;
211
CREATE SEQUENCE xml_relation_id_seq;
212

  
213
CREATE TABLE xml_relation (
214
	relationid    INT8 default nextval('xml_relation_id_seq')  PRIMARY KEY, -- unique id
215
	docid         VARCHAR(250) ,         -- the docid of the package file
216
	                                      -- that this relation came from
217
	subject       VARCHAR(512) NOT NULL, -- the subject of the relation
218
	subdoctype    VARCHAR(128),         	-- the doctype of the subject
219
	relationship  VARCHAR(128)  NOT NULL,-- the relationship type
220
	object        VARCHAR(512) NOT NULL, -- the object of the relation
221
	objdoctype    VARCHAR(128),          -- the doctype of the object
222
	CONSTRAINT xml_relation_uk UNIQUE (subject, relationship, object),
223
	CONSTRAINT xml_relation_docid_fk 
224
		FOREIGN KEY (docid) REFERENCES xml_documents
225
  );
226

  
227

  
0 228

  

Also available in: Unified diff