/* * 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: xmltables.sql 440 2000-09-11 20:36:58Z bojilova $' * */ /* * 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 TRIGGER xml_revisions_before_insert; DROP TRIGGER xml_catalog_before_insert; DROP TABLE xml_index; DROP TABLE xml_catalog; DROP TABLE xml_access; DROP TABLE xml_revisions; DROP TABLE xml_documents; DROP TABLE xml_nodes; DROP TABLE xml_acc_numbers; /* * 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; /* * 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 doctitle VARCHAR2(1000), -- title of document if exists user_owner VARCHAR2(100), -- the user owned the document user_updated VARCHAR2(100), -- the user updated the document date_created DATE, date_updated DATE, public_access NUMBER(1), -- flag for public access CONSTRAINT xml_documents_pk PRIMARY KEY (docid), CONSTRAINT xml_documents_root_fk FOREIGN KEY (rootnodeid) REFERENCES xml_nodes ); /* * Index of in xml_document */ CREATE INDEX xml_documents_idx1 ON xml_documents (docid, doctype); /* * ACL -- table to store ACL for XML documents by principals */ CREATE TABLE xml_access ( docid VARCHAR2(250), -- the document id # principal_name VARCHAR2(100), -- name of user, user group, etc. principal_type VARCHAR2(20), -- like "user", "group", etc. access_type NUMBER(1), -- "read", "write", "all" begin_time DATE, -- the time that access permission begins end_time DATE, -- the time that access permission ends CONSTRAINT xml_access_pk UNIQUE (docid, principal_name, principal_type, access_type, begin_time), CONSTRAINT xml_access_ck CHECK (begin_time < end_time) ); /* * 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 doctitle VARCHAR2(1000), -- title of document if exists user_owner VARCHAR2(100), user_updated VARCHAR2(100), date_created DATE, date_updated DATE, CONSTRAINT xml_revisions_pk PRIMARY KEY (revisionid), CONSTRAINT xml_revisions_root_fk FOREIGN KEY (rootnodeid) REFERENCES xml_nodes ); 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; / /* * 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; / /* * 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 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); /* * table to store unique Accession # for every document in 2 parts */ CREATE TABLE xml_acc_numbers ( global_name VARCHAR2(32), -- first part of acc # local_id NUMBER(20), -- second part - unique in global name CONSTRAINT xml_acc_numbers_pk PRIMARY KEY (global_name, local_id) );