Metacat

/**

 *  '$RCSfile$'

 *  Copyright: 2004 Regents of the University of California and the

 *             National Center for Ecological Analysis and Synthesis

 *

 *   '$Author: sgarg $'

 *     '$Date: 2005-07-25 11:45:45 -0700 (Mon, 25 Jul 2005) $'

 * '$Revision: 2520 $'

 *

 * This program is free software; you can redistribute it and/or modify

 * it under the terms of the GNU General Public License as published by

 * the Free Software Foundation; either version 2 of the License, or

 * (at your option) any later version.

 *

 * This program is distributed in the hope that it will be useful,

 * but WITHOUT ANY WARRANTY; without even the implied warranty of

 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the

 * GNU General Public License for more details.

 *

 * You should have received a copy of the GNU General Public License

 * along with this program; if not, write to the Free Software

 * Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307  USA

 */

/*

 * 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_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_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);

/**

 * Drop the constraint from xml_revisions which points to xml_nodes

 */

ALTER TABLE xml_revisions DROP CONSTRAINT xml_revisions_root_fk;

/**

 * Copy the nodes from xml_nodes to xml_nodes_revisions for old revisions

 * of the documents and deleted documents

 */

INSERT INTO xml_nodes_revisions (nodeid, nodeindex, nodetype,

nodename, nodeprefix, nodedata, parentnodeid, rootnodeid,

docid, date_created, date_updated, nodedatanumerical)

SELECT * FROM xml_nodes WHERE rootnodeid NOT IN

(SELECT rootnodeid from xml_documents where rootnodeid is not NULL);

/**

 * Create the key constraint in xml_revisions which points to 

 * xml_nodes_revisions

 */

ALTER TABLE xml_revisions ADD CONSTRAINT xml_revisions_root_fk

 FOREIGN KEY (rootnodeid) REFERENCES xml_nodes_revisions(nodeid);

/**

 * Delete the records from xml_index table which point to old revisions in xml_index

 * This is possible for documents for which the indexing thread failed during UPDATE

 */

DELETE FROM xml_index WHERE nodeid IN (SELECT nodeid FROM xml_nodes WHERE

rootnodeid NOT IN (SELECT rootnodeid FROM xml_documents WHERE rootnodeid IS NOT NULL)); 

/**

 * Delete the records from xml_nodes which were transfered to xml_nodes_revisions 

 */

DELETE FROM xml_nodes WHERE rootnodeid NOT IN

(SELECT rootnodeid from xml_documents where rootnodeid is not NULL);