Metacat

 * along with this program; if not, write to the Free Software

 * Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307  USA

 */

/*

 *	create the database tables on a Postgresql backend rather

 *	than an Oracle Backend

 */

 */

CREATE SEQUENCE xml_replication_id_seq;

CREATE TABLE xml_replication (

  server VARCHAR(512),

  last_checked DATE,

  replicate INT8,

  datareplicate INT8,

  hub INT8,

  CONSTRAINT xml_replication_pk PRIMARY KEY (serverid)

INSERT INTO xml_replication (server, replicate, datareplicate, hub) VALUES ('localhost', '0', '0', '0');

 * Nodes -- table to store XML Nodes (both elements and attributes)

 */

CREATE SEQUENCE xml_nodes_id_seq;

				-- ELEMENT, ATTRIBUTE, TEXT)

	nodename VARCHAR(250),	-- the name of an element or attribute

        nodeprefix VARCHAR(50), -- the namespace prefix of the node

				-- 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

        nodedatanumerical FLOAT8, -- the data for this node if

				  -- if it is a number

   CONSTRAINT xml_nodes_pk PRIMARY KEY (nodeid),

		FOREIGN KEY (rootnodeid) REFERENCES xml_nodes,

		FOREIGN KEY (parentnodeid) REFERENCES xml_nodes

);

 * Indexes of rootnodeid, parentnodeid, and nodename in xml_nodes

 */

CREATE INDEX xml_nodes_idx1 ON xml_nodes (rootnodeid);

CREATE INDEX xml_nodes_idx2 ON xml_nodes (parentnodeid);

CREATE INDEX xml_nodes_idx3 ON xml_nodes (nodename);

 * XML Catalog -- table to store all external sources for XML documents

 */

CREATE SEQUENCE xml_catalog_id_seq;

	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),

              (entry_type, source_doctype, target_doctype, public_id)

);

 * Sequence to get uniqueID for Accession #

 */

CREATE SEQUENCE xml_documents_id_seq;

 * Documents -- table to store XML documents

 */

CREATE TABLE xml_documents (

	date_created DATE,

	date_updated DATE,

	public_access INT8,	-- flag for public access

     CONSTRAINT xml_documents_pk PRIMARY KEY (docid),

     CONSTRAINT xml_documents_rep_fk

		FOREIGN KEY (rootnodeid) REFERENCES xml_nodes,

		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

 */

	date_created DATE,

	date_updated DATE,

	public_access INT8,	-- flag for public access

   CONSTRAINT xml_revisions_pk PRIMARY KEY (revisionid),

   CONSTRAINT xml_revisions_rep_fk

		FOREIGN KEY (server_location) REFERENCES xml_replication,

		FOREIGN KEY (rootnodeid) REFERENCES xml_nodes,

		FOREIGN KEY (catalog_id) REFERENCES xml_catalog

);

 * ACL -- table to store ACL for XML documents by principals

 */

CREATE TABLE xml_access (

  startnodeid INT8,

  endnodeid INT8,

   CONSTRAINT xml_access_ck CHECK (begin_time < end_time),

		FOREIGN KEY (accessfileid) REFERENCES xml_documents

);

 * quick searching in structured searches

 */

CREATE TABLE xml_index (

	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,

		FOREIGN KEY (docid) REFERENCES xml_documents

);

 */

CREATE INDEX xml_index_idx1 ON xml_index (path);

CREATE INDEX xml_index_idx2 ON xml_index (docid);

	object VARCHAR(512) NOT NULL, -- the object of the relation

	objdoctype VARCHAR(128),          -- the doctype of the object

	CONSTRAINT xml_relation_uk UNIQUE (docid, subject, relationship, object),

		FOREIGN KEY (docid) REFERENCES xml_documents

);

 * Table used as Unique ID generator for the uniqueid part of Accession#

 */

CREATE SEQUENCE accession_number_id_seq;

   date_created DATE

);

 */

CREATE TABLE xml_accesssubtree (

	docid		VARCHAR(250),	-- the document id #

  rev 		INT8 default 1, --the revision number of the docume

  controllevel VARCHAR(50), -- the level it control -- document or subtree

	startnodeid	INT8,	-- the start node id of access subtree

  endnodeid INT8, -- the end node if of access subtree

		FOREIGN KEY (docid) REFERENCES xml_documents

);

/*

 * Logging -- table to store metadata and data access log

 */

CREATE SEQUENCE access_log_id_seq;

  CONSTRAINT access_log_pk PRIMARY KEY (entryid)

);

 * harvest_site_schedule -- table to store harvest sites and schedule info

 */

CREATE TABLE harvest_site_schedule (

  CONSTRAINT harvest_site_schedule_pk PRIMARY KEY (site_schedule_id)

);

 * harvest_log -- table to log entries for harvest operations

 */

CREATE TABLE harvest_log (

  CONSTRAINT harvest_log_pk PRIMARY KEY (harvest_log_id)

);

 * harvest_detail_log -- table to log detailed info about documents that

 *                       generated errors during the harvest

 */

  error_message          VARCHAR(1000), -- text error message

  document_type          VARCHAR(100),  -- document type

  CONSTRAINT harvest_detail_log_pk PRIMARY KEY (detail_log_id),

        FOREIGN KEY (harvest_log_id) REFERENCES harvest_log

);