/* * 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 * Version: '$Id: xmltables.sql 93 2000-05-12 16:59:31Z bojilova $' * */ /* * Drop all of the objects in proper order */ set echo off DROP SEQUENCE xml_nodes_id_seq; DROP SEQUENCE xml_entity_id_seq; DROP SEQUENCE xml_documents_id_seq; DROP TRIGGER xml_nodes_before_insert; DROP TRIGGER xml_documents_before_insert; DROP TRIGGER xml_catalog_before_insert; DROP TABLE xml_catalog; DROP TABLE xml_documents; DROP TABLE xml_nodes; /* * Nodes -- table to store XML Nodes (both elements and attributes) */ CREATE TABLE xml_nodes ( nodeid NUMBER(20), parentnodeid NUMBER(20), nodeindex NUMBER(10), nodetype VARCHAR2(20), nodename VARCHAR2(100), nodedata VARCHAR2(4000), date_created DATE, date_updated DATE, CONSTRAINT xml_nodes_pk PRIMARY KEY (nodeid), CONSTRAINT xml_nodes_parent_fk FOREIGN KEY (parentnodeid) REFERENCES xml_nodes ); CREATE SEQUENCE xml_nodes_id_seq; CREATE TRIGGER xml_nodes_before_insert BEFORE INSERT ON xml_nodes FOR EACH ROW BEGIN SELECT xml_nodes_id_seq.nextval INTO :new.nodeid FROM dual; END; / /* * Documents -- table to store XML documents */ CREATE TABLE xml_documents ( docid NUMBER(20), rootnodeid NUMBER(20), docname VARCHAR2(100), doctype VARCHAR2(100), date_created DATE, date_updated DATE, CONSTRAINT xml_documents_pk PRIMARY KEY (docid), CONSTRAINT xml_documents_root_fk FOREIGN KEY (rootnodeid) REFERENCES xml_nodes ); CREATE SEQUENCE xml_documents_id_seq; CREATE TRIGGER xml_documents_before_insert BEFORE INSERT ON xml_documents FOR EACH ROW BEGIN SELECT xml_documents_id_seq.nextval INTO :new.docid FROM dual; END; / /* * XML Catalog -- table to store all external sources for XML documents */ CREATE TABLE xml_catalog ( entity_id NUMBER(20), entity_name VARCHAR2(100), entity_type VARCHAR2(20), source_doctype VARCHAR2(100), target_doctype VARCHAR2(100), public_id VARCHAR2(100), system_id VARCHAR2(1000), date_created DATE, date_updated DATE, CONSTRAINT xml_catalog_pk PRIMARY KEY (entity_id), CONSTRAINT xml_catalog_uk UNIQUE (entity_type, source_doctype, target_doctype, public_id) ); CREATE SEQUENCE xml_entity_id_seq; CREATE TRIGGER xml_catalog_before_insert BEFORE INSERT ON xml_catalog FOR EACH ROW BEGIN SELECT xml_entity_id_seq.nextval INTO :new.entity_id FROM dual; END; /