[metacat-cvs] metacat/src upgrade-db-to-1.6.sql

[Saurabh Garg]

sgarg       05/11/01 11:31:31

  Modified:    src      upgrade-db-to-1.6.sql
  Log:
  Faster script for upgrading oracle database
  
  Revision  Changes    Path
  1.4       +72 -5     metacat/src/upgrade-db-to-1.6.sql
  
  Index: upgrade-db-to-1.6.sql
  ===================================================================
  RCS file: /cvs/metacat/src/upgrade-db-to-1.6.sql,v
  retrieving revision 1.3
  retrieving revision 1.4
  diff -u -r1.3 -r1.4
  --- upgrade-db-to-1.6.sql	9 Oct 2005 01:45:20 -0000	1.3
  +++ upgrade-db-to-1.6.sql	1 Nov 2005 19:31:31 -0000	1.4
  @@ -3,9 +3,9 @@
    *  Copyright: 2004 Regents of the University of California and the
    *             National Center for Ecological Analysis and Synthesis
    *
  - *   '$Author: tao $'
  - *     '$Date: 2005/10/09 01:45:20 $'
  - * '$Revision: 1.3 $'
  + *   '$Author: sgarg $'
  + *     '$Date: 2005/11/01 19:31:31 $'
  + * '$Revision: 1.4 $'
    *
    * 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
  @@ -136,5 +136,72 @@
   /**
    * 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);
  +
  +/**
  + * Below given statement takes a lot of time to excute
  + *
  + * DELETE FROM xml_nodes WHERE rootnodeid NOT IN
  + * (SELECT rootnodeid from xml_documents where rootnodeid is not NULL);
  + *
  + * Hence.....
  + */
  +
  +
  +/**
  +* Drop the xml_nodes primark key....
  +*/
  +ALTER TABLE xml_nodes DROP CONSTRAINT xml_nodes_pk CASCADE;
  +
  +
  +/** rename xml_nodes to xml_nodes_2 */
  +ALTER TABLE xml_nodes RENAME TO xml_nodes_2;
  +
  +/** create a new xml_nodes table with new primary and foreign keys*/
  +CREATE TABLE xml_nodes (
  +       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_pk PRIMARY KEY (nodeid),
  +  CONSTRAINT xml_nodes_root_fk
  +               FOREIGN KEY (rootnodeid) REFERENCES xml_nodes,
  +  CONSTRAINT xml_nodes_parent_fk
  +               FOREIGN KEY (parentnodeid) REFERENCES xml_nodes
  +);
  +
  +/** copy nodes from xml_nodes_2  to xml_nodes */
  +INSERT INTO xml_nodes (nodeid, nodeindex, nodetype, nodename, nodeprefix, nodedata, parentnodeid, rootnodeid, docid, date_created, date_updated, nodedatanumerical) SELECT n.nodeid, n.nodeindex, n.nodetype, n.nodename, n.nodeprefix, n.nodedata, n.parentnodeid, n.rootnodeid, n.docid, n.date_created, n.date_updated, n.nodedatanumerical FROM xml_nodes_2 n, xml_nodes_revisions r WHERE n.rootnodeid = r.rootnodeid(+) AND r.rootnodeid is NULL;
  +
  +
  +/** Drop old indexes **/
  +DROP INDEX xml_nodes_idx1;
  +DROP INDEX xml_nodes_idx2;
  +DROP INDEX xml_nodes_idx3;
  +
  +/** Create new indexes **/
  +CREATE INDEX xml_nodes_idx1 ON xml_nodes (rootnodeid);
  +CREATE INDEX xml_nodes_idx2 ON xml_nodes (parentnodeid);
  +CREATE INDEX xml_nodes_idx3 ON xml_nodes (nodename);
  +
  +/** Add constaints which were deleted before moving xml_nodes to xml_nodes_2 */
  +ALTER TABLE xml_documents ADD CONSTRAINT xml_documents_root_fk FOREIGN KEY (rootnodeid) REFERENCES xml_nodes;
  +ALTER TABLE xml_index ADD CONSTRAINT xml_index_nodeid_fk FOREIGN KEY (nodeid) REFERENCES xml_nodes;
  +
  +/** Drop xml_nodes_2 table */
  +DROP TABLE xml_nodes_2;
  +
  +/** Done */
  +
  +