Metacat

/*

 * xmlreplicationtables.sql -- Add two columns to xml_replication tables 

 * in Production Metacat

 * 

 *      Created: 07/14/2002

 *       Author: Jing Tao

 * Organization: National Center for Ecological Analysis and Synthesis

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

 *               National Center for Ecological Analysis and Synthesis

 *  For Details: http://www.nceas.ucsb.edu/

 *    File Info: '$Id$'

 *

 */

/*

 * Add tow columns - datareplicate and hub to xml_replication 

 */

ALTER TABLE xml_replication ADD ( datareplicate NUMBER(1), hub NUMBER(1) );

/*

 * xmlreplicationtables.sql -- Add three columns to xml_access tables

 * in Production Metacat

 *

 *      Created: 07/14/2002

 *       Author: Jing Tao

 * Organization: National Center for Ecological Analysis and Synthesis

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

 *               National Center for Ecological Analysis and Synthesis

 *  For Details: http://www.nceas.ucsb.edu/

 *    File Info: '$Id$'

 *

 */

/*

 * Add three columns to xml_access

 */

ALTER TABLE xml_access ADD (subtreeid VARCHAR2(32), startnodeid NUMBER(20), endnodeid NUMBER(20) );

/*

 * accesssubtree -- table to store access subtree info

 */

CREATE TABLE xml_accesssubtree (

	docid		VARCHAR2(250),	-- the document id #

  rev 		NUMBER(10) DEFAULT 1, --the revision number of the docume

  controllevel VARCHAR2(50), -- the level it control -- document or subtree

  subtreeid VARCHAR2(250), -- the subtree id 

	startnodeid	NUMBER(20),	-- the start node id of access subtree

  endnodeid NUMBER(20), -- the end node if of access subtree

  CONSTRAINT xml_accesssubtree_docid_fk 

		FOREIGN KEY (docid) REFERENCES xml_documents

);

/*

 * Drop the constrain in xml_relation table for (subject, relationship, object)

 * Add the new constrain in xml_relation table for (docid, subject, relationship, object)

 */

ALTER TABLE xml_relation DROP CONSTRAINT xml_relation_uk;

ALTER TABLE xml_relation ADD  CONSTRAINT xml_relation_uk UNIQUE (docid, subject, relationship, object); 

/*

 * xmlreplicationtables_postgres.sql -- Add two columns to xml_replication tables 

 * in Production Metacat

 * 

 *      Created: 07/14/2002

 *       Author: Jing Tao

 * Organization: National Center for Ecological Analysis and Synthesis

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

 *               National Center for Ecological Analysis and Synthesis

 *  For Details: http://www.nceas.ucsb.edu/

 *    File Info: '$Id$'

 *

 */

/*

 * Add tow columns - datareplicate and hub to xml_replication 

 */

ALTER TABLE xml_replication ADD COLUMN datareplicate INT8;

ALTER TABLE xml_replication ADD COLUMN hub INT8;

/*

 * reviseformetacat13_postgres.sql -- Add three columns to xml_access tables

 * and create a new table in Production Metacat

 *

 *      Created: 07/14/2002

 *       Author: Jing Tao

 * Organization: National Center for Ecological Analysis and Synthesis

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

 *               National Center for Ecological Analysis and Synthesis

 *  For Details: http://www.nceas.ucsb.edu/

 *    File Info: '$Id$'

 *

 */

/*

 * Add tow columns - datareplicate and hub to xml_access

 */

ALTER TABLE xml_access ADD subtreeid VARCHAR(32);

ALTER TABLE xml_access ADD startnodeid INT8;

ALTER TABLE xml_access ADD endnodeid INT8;

/*

 * accesssubtree -- table to store access subtree info

 */

CREATE TABLE xml_accesssubtree (

	docid		VARCHAR(250),	-- the document id #

  rev 		INT8 default 1, --the revision number of the docume

  controllevel VARCHAR(50), -- the level it control -- document or subtree

  subtreeid VARCHAR(250), -- the subtree id 

	startnodeid	INT8,	-- the start node id of access subtree

  endnodeid INT8, -- the end node if of access subtree

  CONSTRAINT xml_accesssubtree_docid_fk 

		FOREIGN KEY (docid) REFERENCES xml_documents

);

/*

 * We need to drop constraint(subject, relationship, object) and create new

 * new (docid, subject, relationship, object). Unfortunately, progres doesn't

 * remove the constrain directly and we should create a new one and copy the

 * old data to new one, then rename them.

 */

ALTER TABLE xml_relation RENAME TO old_xml_relation;

DROP INDEX xml_relation_pkey;

/*DROP SEQUENCE xml_relation_id_seq;

*CREATE SEQUENCE xml_relation_id_seq;

*/

CREATE TABLE xml_relation (

        relationid INT8 default nextval('xml_relation_id_seq') PRIMARY KEY,

                                             -- unique id

        docid VARCHAR(250) ,         -- the docid of the package file

                                             -- that this relation came from

        packagetype VARCHAR(250),          -- the type of the package

        subject VARCHAR(512) NOT NULL, -- the subject of the relation

        subdoctype VARCHAR(128),                -- the doctype of the subject

        relationship VARCHAR(128)  NOT NULL,-- the relationship type

        object VARCHAR(512) NOT NULL, -- the object of the relation

        objdoctype VARCHAR(128),          -- the doctype of the object

        CONSTRAINT xml_relation_uk1 UNIQUE (docid, subject, relationship, object),

        CONSTRAINT xml_relation_docid_fk1

                FOREIGN KEY (docid) REFERENCES xml_documents

);

INSERT INTO xml_relation SELECT * FROM old_xml_relation;

   You now need to set up the table structure in your database.  You can do

   either do this using the ant build system, or by manually running the

   scripts using a sql utility.

  </p>

  <p>To run the scripts using ant, type "ant installdb".

  </p>

  <p>To run the scripts manually, change to the

  </p>

  <p>Either way, 

   you should see a bunch of output showing the creation of the Metacat table

   xmltalbes_postgres.sql and for Microsoft SQL server is 

   xmltables-sqlserver.sql.

  <p class="header"><h2>Upgrading SQL Scripts</h2></p>

  <p>

    If you have an existing metacat installation, you should not run the install

    script because it will replace all of the older tables with new, empty 

    copies of the tables.  Thus you would lose your data!  Instead, you can 

    run some upgrade scripts that will change the table structure as needed for

    the new version.  If you are skipping versions, run each upgrade script

    for the intermediate versions as well.  Currently the upgrade scripts are:

   </p>

    <ul>

      <li>upgrade-db-to-1.2.sql</li>

      <li>upgrade-db-to-1.3.sql</li>

    </ul>

   <p>

    So, if you had an existing metacat 1.0 installation and you were upgrading 

    to 1.3, you would need to run both upgrade-db-to-1.2.sql and 

    upgrade-db-to-1.3.sql. Howver, if you were starting from a Metacat 1.2.x

    installation, you would only need to run the 1.3 upgrade script.

   </p>

  </p>

      <property name="webapps" value="${tomcat}/webapps" />

      <property name="systemidserver" value="http://${server}" />

      <property name="debug" value="on" />

              debug="${debug}"

   <target name="installdb" depends="prepare">

     <echo/>

     <echo>Installing the database tables...</echo>

     <sql driver="${dbDriver}"

          classpath="${jdbc}:${jdbc-add1}:${jdbc-add2}"

          url="${jdbc-connect}"

          userid="${user}"

          password="${password}"

          src="${build.src}/xmltables.sql" />

   </target>

   <target name="upgrade12" depends="prepare">

     <echo/>

     <echo>Updating the database tables to upgrade to version 1.2...</echo>

     <sql driver="${dbDriver}"

          classpath="${jdbc}:${jdbc-add1}:${jdbc-add2}"

          url="${jdbc-connect}"

          userid="${user}"

          password="${password}"

          src="${build.src}/upgrade-db-to-1.2.sql" />

   </target>

   <target name="upgrade13" depends="prepare">

     <echo/>

     <echo>Updating the database tables to upgrade to version 1.3...</echo>

     <sql driver="${dbDriver}"

          classpath="${jdbc}:${jdbc-add1}:${jdbc-add2}"

          url="${jdbc-connect}"

          userid="${user}"

          password="${password}"

          src="${build.src}/upgrade-db-to-1.3.sql" />

   </target>