Index: src/xmltables-sqlserver.sql
===================================================================
--- src/xmltables-sqlserver.sql	(nonexistent)
+++ src/xmltables-sqlserver.sql	(revision 729)
@@ -0,0 +1,239 @@
+/*
+ * xmltables.sql -- Create or replace tables for storing XML in the db
+ *
+ *      Purpose: creates tables needed for XML database
+ * 
+ *      Created: 12 September 1999
+ *       Author: Matt Jones
+ * 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
+ *  For Details: http://www.nceas.ucsb.edu/
+ *    File Info: '$Id$'
+ *
+ */
+
+/*
+ * Drop all of the objects in proper order
+ */
+/*set echo off*/
+
+CREATE TRIGGER xml_replication_before_insert BEFORE INSERT
+ON xml_replication FOR EACH ROW
+BEGIN
+  SELECT xml_replication_id_seq.nextval
+    INTO :new.serverid
+    FROM dual;
+END;
+/
+
+/*CREATE SEQUENCE xml_replication_id_seq;*/
+/*
+ *Replication -- table to store servers that metacat is replicated to
+ */
+CREATE TABLE xml_replication (
+  serverid      NUMBER(20),
+  server        VARCHAR2(512),
+  last_checked  DATE,
+  replicate     NUMBER(1), 
+  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		NUMBER(20),	-- the unique node id (pk)
+	nodeindex	NUMBER(10),	-- order of nodes within parent
+	nodetype	VARCHAR2(20),	-- type (DOCUMENT, COMMENT, PI,
+					-- ELEMENT, ATTRIBUTE, TEXT)
+	nodename	VARCHAR2(250),	-- the name of an element or attribute
+	nodedata	VARCHAR2(4000), -- the data for this node (e.g., 
+					-- for TEXT it is the content)
+	parentnodeid	NUMBER(20),	-- index of the parent of this node
+	rootnodeid	NUMBER(20),	-- index of the root node of this tree
+	docid		VARCHAR2(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 TABLE xml_catalog (
+	catalog_id	NUMBER(20),	-- the id for this catalog entry
+	entry_type	VARCHAR2(500),	-- the type of this catalog entry
+					-- (e.g., DTD, XSD, XSL)
+	source_doctype	VARCHAR2(500),	-- the source public_id for transforms
+	target_doctype	VARCHAR2(500),	-- the target public_id for transforms
+	public_id	VARCHAR2(500),	-- the unique id for this type
+	system_id	VARCHAR2(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)
+);
+
+CREATE SEQUENCE xml_catalog_id_seq;
+
+CREATE TRIGGER xml_catalog_before_insert
+BEFORE INSERT ON xml_catalog FOR EACH ROW
+BEGIN
+  SELECT xml_catalog_id_seq.nextval
+    INTO :new.catalog_id
+    FROM dual;
+END;
+/
+
+/* 
+ * Documents -- table to store XML documents
+ */
+CREATE TABLE xml_documents (
+	docid		VARCHAR2(250),	-- the document id #
+	rootnodeid	NUMBER(20),	-- reference to root node of the DOM
+	docname		VARCHAR2(100),	-- usually the root element name
+	doctype		VARCHAR2(100),	-- public id indicating document type
+	user_owner	VARCHAR2(100),	-- the user owned the document
+	user_updated	VARCHAR2(100),	-- the user updated the document
+	server_location NUMBER(20),	-- the server on which this document resides
+	rev 		NUMBER(10) DEFAULT 1,--the revision number of the document
+	date_created	DATE,
+	date_updated	DATE,
+	public_access	NUMBER(1),	-- flag for public access
+        catalog_id      NUMBER(20),	-- 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 TABLE xml_revisions (
+	revisionid	NUMBER(20),	-- the revision number we are saving
+	docid		VARCHAR2(250),	-- the document id #
+	rootnodeid	NUMBER(20),	-- reference to root node of the DOM
+	docname		VARCHAR2(100),	-- usually the root element name
+	doctype		VARCHAR2(100),	-- public id indicating document type
+	user_owner	VARCHAR2(100),
+	user_updated	VARCHAR2(100),
+	server_location NUMBER(20),
+	rev		NUMBER(10),
+	date_created	DATE,
+	date_updated	DATE,
+	public_access	NUMBER(1),	-- flag for public access
+        catalog_id      NUMBER(20),	-- 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
+);
+
+CREATE SEQUENCE xml_revisions_id_seq;
+
+CREATE TRIGGER xml_revisions_before_insert
+BEFORE INSERT ON xml_revisions FOR EACH ROW
+BEGIN
+  SELECT xml_revisions_id_seq.nextval
+    INTO :new.revisionid
+    FROM dual;
+END;
+/
+
+/* 
+ * ACL -- table to store ACL for XML documents by principals
+ */
+CREATE TABLE xml_access (
+	docid		VARCHAR2(250),	-- the document id #
+	accessfileid	VARCHAR2(250),	-- the document id # for the access file
+	principal_name	VARCHAR2(100),	-- name of user, group, etc.
+	permission	NUMBER(1),	-- "read", "write", "all"
+	perm_type	VARCHAR2(32),	-- "allowed" or "denied"
+	perm_order	VARCHAR2(32),	-- "allow first" or "deny first"
+	begin_time	DATE,		-- the time that permission begins
+	end_time	DATE,		-- the time that permission ends
+	ticket_count	NUMBER(5),	-- 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		NUMBER(20),	-- the unique node id
+	path		VARCHAR2(200),	-- precomputed path through tree
+	docid		VARCHAR2(250),	-- index to the document id
+	doctype		VARCHAR2(100),	-- public id indicating document type
+        parentnodeid    NUMBER(20),     -- 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 TABLE xml_relation (
+	relationid    NUMBER(20) PRIMARY KEY, -- unique id
+	docid         VARCHAR2(250) ,         -- the docid of the package file
+	                                      -- that this relation came from
+	subject       VARCHAR2(512) NOT NULL, -- the subject of the relation
+	subdoctype    VARCHAR2(128),         	-- the doctype of the subject
+	relationship  VARCHAR2(128)  NOT NULL,-- the relationship type
+	object        VARCHAR2(512) NOT NULL, -- the object of the relation
+	objdoctype    VARCHAR2(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
+  );
+
+CREATE SEQUENCE xml_relation_id_seq;
+  
+CREATE TRIGGER xml_relation_before_insert
+BEFORE INSERT ON xml_relation FOR EACH ROW
+BEGIN
+  SELECT xml_relation_id_seq.nextval
+    INTO :new.relationid
+    FROM dual;
+END;
+/
+

Property changes on: src/xmltables-sqlserver.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
