source_doctype, public_id, /* * 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 72 2000-05-04 22:30:15Z 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_cat_entities_before_insert; DROP TABLE xml_catalog_entities; 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 LONG, 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 -- tables to store all external sources for XML documents */ CREATE TABLE xml_catalog ( doctype VARCHAR2(100), date_created DATE, date_updated DATE, CONSTRAINT xml_catalog_pk PRIMARY KEY (doctype) ); CREATE TABLE xml_catalog_entities ( 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_entities_pk PRIMARY KEY (entity_id), CONSTRAINT xml_catalog_entities_uk UNIQUE (entity_type, source_doctype, target_doctype, public_id), CONSTRAINT xml_source_catalog_fk FOREIGN KEY (source_doctype) REFERENCES xml_catalog, CONSTRAINT xml_target_catalog_fk FOREIGN KEY (target_doctype) REFERENCES xml_catalog ); CREATE SEQUENCE xml_entity_id_seq; CREATE TRIGGER xml_cat_entities_before_insert BEFORE INSERT ON xml_catalog_entities FOR EACH ROW BEGIN SELECT xml_entity_id_seq.nextval INTO :new.entity_id FROM dual; END; /