Metacat

DROP TABLE xml_nodes_revision;

 * xml_nodes_revision -- table to store nodes from xml_nodes which are of old revisions and deleted document

 */

CREATE TABLE xml_nodes_revision (

        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

        nodeprefix      VARCHAR2(50),   -- the namespace prefix 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,

        nodedatanumerical NUMBER,       -- the data for this node if

                                        -- it is a number

   CONSTRAINT xml_nodes_revision_pk PRIMARY KEY (nodeid),

   CONSTRAINT xml_nodes_revision_root_fk

                FOREIGN KEY (rootnodeid) REFERENCES xml_nodes_revision,

   CONSTRAINT xml_nodes_revision_parent_fk

                FOREIGN KEY (parentnodeid) REFERENCES xml_nodes_revision

);

/*

 * Indexes of rootnodeid, parentnodeid, and nodename in xml_nodes_revision

 */

CREATE INDEX xml_nodes_revision_idx1 ON xml_nodes_revision (rootnodeid);

CREATE INDEX xml_nodes_revision_idx2 ON xml_nodes_revision (parentnodeid);

CREATE INDEX xml_nodes_revisions_idx3 ON xml_nodes_revision (nodename);

/*

		FOREIGN KEY (rootnodeid) REFERENCES xml_nodes_revisions,

/*

 * Index of Paths -- table to store nodes with paths specified by userst in metacat.properties

 */

CREATE TABLE xml_path_index (

        nodeid          NUMBER(20),     -- the unique node id

        docid           VARCHAR2(250),  -- index to the document id

        path            VARCHAR2(1000), -- precomputed path through tree

	nodedata        VARCHAR2(4000), -- the data for this node (e.g.,

                                        -- for TEXT it is the content)

        nodedatanumerical NUMBER,       -- the data for this node if

                                        -- it is a number

        parentnodeid    NUMBER(20),     -- index of the parent of this node

   CONSTRAINT xml_path_index_pk PRIMARY KEY (nodeid),

   CONSTRAINT xml_path_index_docid_fk

                FOREIGN KEY (docid) REFERENCES xml_documents

);

CREATE SEQUENCE xml_path_index_id_seq;

CREATE TRIGGER xml_path_index_before_insert

BEFORE INSERT ON xml_path_indexn FOR EACH ROW

BEGIN

  SELECT xml_path_index_id_seq.nextval

    INTO :new.relationid

    FROM dual;

END;

/

/*

 * Index of the path, nodedata, nodedatanumerical in xml_path_index

 */

CREATE INDEX xml_path_index_idx1 ON xml_path_index (path);

CREATE INDEX xml_path_index_idx2 ON xml_path_index (nodedata);

CREATE INDEX xml_path_index_idx3 ON xml_path_index (nodedatanumerical);