Metacat

/*

 * 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 20 2000-04-11 18:22:35Z jones $'

 *

 */

/*

 * Drop all of the objects in proper order

 */

DROP SEQUENCE xml_nodes_id_seq;

DROP TRIGGER xml_nodes_before_insert;

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

	nodetype	VARCHAR2(2000),

	nodename	VARCHAR2(2000),

	nodedata	VARCHAR2(2000),

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

 */

CREATE TABLE xml_documents (

	docid		NUMBER(20),

	rootnodeid	NUMBER(20),

	docname		VARCHAR2(1000),

	doctype		VARCHAR2(1000),

	date_created	DATE,

	date_updated	DATE,

   CONSTRAINT xml_documents_pk PRIMARY KEY (docid),

   CONSTRAINT xml_documents_root_fk 

		FOREIGN KEY (rootnodeid) REFERENCES xml_nodes

);