Metacat

		FOREIGN KEY (rootnodeid) REFERENCES xml_nodes_revisions,

 * Table for indexing the paths specified the administrator in metacat.properties

 */

CREATE SEQUENCE xml_path_index_id_seq;

CREATE TABLE xml_path_index (

    nodeid INT8  default nextval('xml_path_index_id_seq'),

        docid VARCHAR(250),     -- the document id

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

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

                                -- for TEXT it is the content)

        nodedatanumerical FLOAT8, -- the data for this node if

                                  -- if it is a number

        parentnodeid INT8,      -- id of the parent of the node represented

                                -- by this row

   CONSTRAINT xml_path_index_pk PRIMARY KEY (nodeid),

   CONSTRAINT xml_path_index_upper_docid_fk

                FOREIGN KEY (docid) REFERENCES xml_documents

);

/*

 * Indexes of path, nodedata and 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);

/*

 * Table for storing the nodes for the old revisions of the document and the deleted documents

 */

CREATE TABLE xml_nodes_revisions (

        nodeid INT8,            -- the unique node id (pk)

        nodeindex INT8,         -- order of nodes within parent

        nodetype VARCHAR(20),   -- type (DOCUMENT, COMMENT, PI,

                                -- ELEMENT, ATTRIBUTE, TEXT)

        nodename VARCHAR(250),  -- the name of an element or attribute

        nodeprefix VARCHAR(50), -- the namespace prefix of the node

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

                                -- for TEXT it is the content)

        parentnodeid INT8,      -- index of the parent of this node

        rootnodeid INT8,        -- index of the root node of this tree

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

        date_created DATE,

        date_updated DATE,

        nodedatanumerical FLOAT8, -- the data for this node if

                                  -- if it is a number

   CONSTRAINT xml_nodes_revisions_pk PRIMARY KEY (nodeid),

   CONSTRAINT xml_nodes_revisions_root_fk

                FOREIGN KEY (rootnodeid) REFERENCES xml_nodes_revisions,

   CONSTRAINT xml_nodes_revisions_parent_fk

                FOREIGN KEY (parentnodeid) REFERENCES xml_nodes_revisions

);

/*

 * Indexes of rootnodeid, parentnodeid, and nodename in xml_nodes_revisions

 */

CREATE INDEX xml_nodes_revision_idx1 ON xml_nodes_revisions (rootnodeid);

CREATE INDEX xml_nodes_revision_idx2 ON xml_nodes_revisions (parentnodeid);

CREATE INDEX xml_nodes_revision_idx3 ON xml_nodes_revisions (nodename);

/*