Index: src/xmltables_postgres.sql
===================================================================
--- src/xmltables_postgres.sql	(nonexistent)
+++ src/xmltables_postgres.sql	(revision 741)
@@ -0,0 +1,227 @@
+/*
+ * xmltables.sql -- Create or replace tables for storing XML in the db
+ *
+ *      Purpose: creates tables needed for XML database
+ * 
+ *      Created: 08 May 2001 
+ *       Author: John Harris
+ * Organization: National Center for Ecological Analysis and Synthesis
+ *    Copyright: 2000 Regents of the University of California and the
+ *               National Center for Ecological Analysis and Synthesis
+ *
+ *	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
+ */
+
+/*
+ * Drop all of the objects in proper order
+ */
+set echo off
+
+DROP SEQUENCE xml_nodes_id_seq;
+DROP SEQUENCE xml_revisions_id_seq;
+DROP SEQUENCE xml_catalog_id_seq;
+DROP SEQUENCE xml_relation_id_seq;
+DROP SEQUENCE xml_replication_id_seq;
+DROP SEQUENCE accnum_uniqueid_seq;
+
+
+DROP TABLE xml_index;
+DROP TABLE xml_access;
+DROP TABLE xml_revisions;
+DROP TABLE xml_relation;
+DROP TABLE xml_documents;
+DROP TABLE xml_nodes;
+DROP TABLE xml_replication;
+DROP TABLE xml_catalog;
+
+/*
+ *Replication -- table to store servers that metacat is replicated to
+ */
+
+CREATE SEQUENCE xml_replication_id_seq;
+
+CREATE TABLE xml_replication (
+  serverid     INT8  default nextval('xml_replication_id_seq'), 
+  server        VARCHAR(512),
+  last_checked  DATE,
+  replicate     INT8, 
+  CONSTRAINT xml_replication_pk PRIMARY KEY (serverid)
+);  
+
+INSERT INTO xml_replication (serverid, server, replicate) VALUES ('1', 'localhost', '0');
+
+/* 
+ * Nodes -- table to store XML Nodes (both elements and attributes)
+ */
+CREATE TABLE xml_nodes (
+	nodeid		INT8, 		-- the unique node id (pk)
+	nodeindex	INT8,		-- order of nodes within parent
+	nodetype	VARCHAR(20),	-- type (DOCUMENT, COMMENT, PI,
+					-- ELEMENT, ATTRIBUTE, TEXT)
+	nodename	VARCHAR(250),	-- the name of an element or attribute
+	nodedata	VARCHAR(4000), 	-- the data for this node (e.g., 
+					-- for TEXT it is the content)
+	parentnodeid	INT8,		-- index of the parent of this node
+	rootnodeid	INT8,		-- index of the root node of this tree
+	docid		VARCHAR(250),	-- index to the document id
+	date_created	DATE,
+	date_updated	DATE,
+   CONSTRAINT xml_nodes_pk PRIMARY KEY (nodeid),
+   CONSTRAINT xml_nodes_root_fk 
+		FOREIGN KEY (rootnodeid) REFERENCES xml_nodes,
+   CONSTRAINT xml_nodes_parent_fk 
+		FOREIGN KEY (parentnodeid) REFERENCES xml_nodes
+);
+
+/* 
+ * Indexes of rootnodeid & parentnodeid in xml_nodes
+ */
+CREATE INDEX xml_nodes_idx1 ON xml_nodes (rootnodeid);
+CREATE INDEX xml_nodes_idx2 ON xml_nodes (parentnodeid);
+CREATE SEQUENCE xml_nodes_id_seq;
+
+
+/* 
+ * XML Catalog -- table to store all external sources for XML documents
+ */
+CREATE SEQUENCE xml_catalog_id_seq;
+
+CREATE TABLE xml_catalog (
+	catalog_id INT8 default nextval('xml_catalog_id_seq'), -- the id for this catalog entry
+	entry_type	VARCHAR(500),	-- the type of this catalog entry
+					-- (e.g., DTD, XSD, XSL)
+	source_doctype	VARCHAR(500),	-- the source public_id for transforms
+	target_doctype	VARCHAR(500),	-- the target public_id for transforms
+	public_id	VARCHAR(500),	-- the unique id for this type
+	system_id	VARCHAR(1000),	-- the local location of the object
+   CONSTRAINT xml_catalog_pk PRIMARY KEY (catalog_id),
+   CONSTRAINT xml_catalog_uk UNIQUE  (entry_type, source_doctype, target_doctype, public_id)
+);
+
+
+
+/* 
+ * Documents -- table to store XML documents
+ */
+CREATE TABLE xml_documents (
+	docid		VARCHAR(250),	-- the document id #
+	rootnodeid	INT8,	-- reference to root node of the DOM
+	docname		VARCHAR(100),	-- usually the root element name
+	doctype		VARCHAR(100),	-- public id indicating document type
+	user_owner	VARCHAR(100),	-- the user owned the document
+	user_updated	VARCHAR(100),	-- the user updated the document
+	server_location INT8,	-- the server on which this document resides
+	rev 		INT8,   --the revision number of the document
+	date_created	DATE,
+	date_updated	DATE,
+	public_access	INT8,	-- flag for public access
+        catalog_id      INT8,	-- reference to xml_catalog 
+     CONSTRAINT xml_documents_pk PRIMARY KEY (docid),
+     CONSTRAINT xml_documents_rep_fk
+     		FOREIGN KEY (server_location) REFERENCES xml_replication, 
+    CONSTRAINT xml_documents_root_fk 
+		FOREIGN KEY (rootnodeid) REFERENCES xml_nodes,
+   CONSTRAINT xml_documents_catalog_fk 
+		FOREIGN KEY (catalog_id) REFERENCES xml_catalog
+);
+
+/* 
+ * Index of <docid,doctype> in xml_document
+ */
+CREATE INDEX xml_documents_idx1 ON xml_documents (docid, doctype);
+
+/* 
+ * Revised Documents -- table to store XML documents saved after an UPDATE
+ *                    or DELETE
+ */
+
+
+CREATE SEQUENCE xml_revisions_id_seq;
+CREATE TABLE xml_revisions (
+	revisionid	INT8  default nextval('xml_revisions_id_seq'), -- the revision number we are saving
+	docid		VARCHAR(250),	-- the document id #
+	rootnodeid	INT8,	-- reference to root node of the DOM
+	docname		VARCHAR(100),	-- usually the root element name
+	doctype		VARCHAR(100),	-- public id indicating document type
+	user_owner	VARCHAR(100),
+	user_updated	VARCHAR(100),
+	server_location INT8,
+	rev		INT8,
+	date_created	DATE,
+	date_updated	DATE,
+	public_access	INT8,	-- flag for public access
+        catalog_id      INT8,	-- reference to xml_catalog 
+   CONSTRAINT xml_revisions_pk PRIMARY KEY (revisionid),
+   CONSTRAINT xml_revisions_rep_fk
+		FOREIGN KEY (server_location) REFERENCES xml_replication,
+   CONSTRAINT xml_revisions_root_fk 
+		FOREIGN KEY (rootnodeid) REFERENCES xml_nodes,
+   CONSTRAINT xml_revisions_catalog_fk 
+		FOREIGN KEY (catalog_id) REFERENCES xml_catalog
+);
+
+
+/* 
+ * ACL -- table to store ACL for XML documents by principals
+ */
+CREATE TABLE xml_access (
+	docid		VARCHAR(250),	-- the document id #
+	accessfileid	VARCHAR(250),	-- the document id # for the access file
+	principal_name	VARCHAR(100),	-- name of user, group, etc.
+	permission	INT8,		-- "read", "write", "all"
+	perm_type	VARCHAR(32),	-- "allowed" or "denied"
+	perm_order	VARCHAR(32),	-- "allow first" or "deny first"
+	begin_time	DATE,		-- the time that permission begins
+	end_time	DATE,		-- the time that permission ends
+	ticket_count	INT8,		-- ticket counter for that permission
+   CONSTRAINT xml_access_ck CHECK (begin_time < end_time),
+   CONSTRAINT xml_access_accessfileid_fk 
+		FOREIGN KEY (accessfileid) REFERENCES xml_documents
+);
+
+/* 
+ * Index of Nodes -- table to store precomputed paths through tree for 
+ * quick searching in structured searches
+ */
+CREATE TABLE xml_index (
+	nodeid		INT8,		-- the unique node id
+	path		VARCHAR(200),	-- precomputed path through tree
+	docid		VARCHAR(250),	-- index to the document id
+	doctype		VARCHAR(100),	-- public id indicating document type
+        parentnodeid    INT8,     -- id of the parent of the node represented
+					-- by this row
+   CONSTRAINT xml_index_pk PRIMARY KEY (nodeid,path),
+   CONSTRAINT xml_index_nodeid_fk FOREIGN KEY (nodeid) REFERENCES xml_nodes,
+   CONSTRAINT xml_index_docid_fk 
+		FOREIGN KEY (docid) REFERENCES xml_documents
+);
+
+/* 
+ * Index of the paths in xml_index 
+ */
+CREATE INDEX xml_index_idx1 ON xml_index (path);
+
+/* 
+ * Sequence to get uniqueID for Accession #
+ */
+CREATE SEQUENCE accnum_uniqueid_seq;
+CREATE SEQUENCE xml_relation_id_seq;
+
+CREATE TABLE xml_relation (
+	relationid    INT8 default nextval('xml_relation_id_seq')  PRIMARY KEY, -- unique id
+	docid         VARCHAR(250) ,         -- the docid of the package file
+	                                      -- that this relation came from
+	subject       VARCHAR(512) NOT NULL, -- the subject of the relation
+	subdoctype    VARCHAR(128),         	-- the doctype of the subject
+	relationship  VARCHAR(128)  NOT NULL,-- the relationship type
+	object        VARCHAR(512) NOT NULL, -- the object of the relation
+	objdoctype    VARCHAR(128),          -- the doctype of the object
+	CONSTRAINT xml_relation_uk UNIQUE (subject, relationship, object),
+	CONSTRAINT xml_relation_docid_fk 
+		FOREIGN KEY (docid) REFERENCES xml_documents
+  );
+
+

Property changes on: src/xmltables_postgres.sql
___________________________________________________________________
Added: svn:eol-style
## -0,0 +1 ##
+native
\ No newline at end of property
Added: svn:executable
## -0,0 +1 ##
+*
\ No newline at end of property
Added: svn:keywords
## -0,0 +1 ##
+Author Date Id Revision
\ No newline at end of property
