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 98 2000-05-13 01:29:51Z jones $'

 *

 */

/*

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

	doctitle	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

);

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;

/