Metacat

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

CREATE TABLE xml_nodes_revisions (

   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

CREATE INDEX xml_nodes_revisions_idx1 ON xml_nodes_revisions (rootnodeid);

CREATE INDEX xml_nodes_revisions_idx2 ON xml_nodes_revisions (parentnodeid);

CREATE INDEX xml_nodes_revisions_idx3 ON xml_nodes_revisions (nodename);

 * 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_revisions_idx1 ON xml_nodes_revisions (rootnodeid);

CREATE INDEX xml_nodes_revisions_idx2 ON xml_nodes_revisions (parentnodeid);

CREATE INDEX xml_nodes_revisions_idx3 ON xml_nodes_revisions (nodename);

/*