DLXS Database Upgrade Utility

From DLXS Documentation

(Difference between revisions)
Jump to: navigation, search
(→Step 4. Run the upgrade_6_7 script)
Current revision (10:48, 25 October 2010) (edit) (undo)
(→OAI Database Upgrade)
 
(12 intermediate revisions not shown.)
Line 1: Line 1:
[[DLXS Wiki|Main Page]] > [[Working with DLXS Components]] > [[Working with DLXS Utilities and Applications]] > DLXS Database Upgrade Utility
[[DLXS Wiki|Main Page]] > [[Working with DLXS Components]] > [[Working with DLXS Utilities and Applications]] > DLXS Database Upgrade Utility
-
<p>These instructions assume you are upgrading from a version 6 MySQL database used with DLXS release 13 to a version 7 database MySQL for DLXS release 14.</p>
+
<p>These instructions assume you are upgrading from a version N MySQL database to a version N+1 database.</p>
==Overview==
==Overview==
-
 
+
     <p>This document describes the upgrade process for the dlxs database. This document assumes you are upgrading from a version N MySQL database to a version N+1 database. See the table [[Installing_and_Upgrading_the_Database#Database_Versions|Database Versions]]. </p>  
-
     <p>This document describes the upgrade process for the dlxs database. This document assumes you are upgrading from a version 6 MySQL database used with DLXS release 13 to a version 7 database MySQL for DLXS release 14. Please consult previous release documentation at http://docs.dlxs.org/ for upgrading from earlier database versions.  </p>  
+
      
      
     <p>Each version of the database is meant to be run with a specific version of DLXS.  For example, version 5 of the database works only with versions 12 and 12a of DLXS.  If you need to have two or more versions of DLXS running, you will need to set up a database with the appropriate version for each DLXS installation.  If this is of interest to you, contact DLXS Help for guidance in doing this.</p>
     <p>Each version of the database is meant to be run with a specific version of DLXS.  For example, version 5 of the database works only with versions 12 and 12a of DLXS.  If you need to have two or more versions of DLXS running, you will need to set up a database with the appropriate version for each DLXS installation.  If this is of interest to you, contact DLXS Help for guidance in doing this.</p>
Line 11: Line 10:
     <p>Below is a series of steps to follow to upgrade your database.  You should upgrade the database after installing the DLXS middleware since the upgrade script is part of the installation and depends on values you supply during installation.</p>
     <p>Below is a series of steps to follow to upgrade your database.  You should upgrade the database after installing the DLXS middleware since the upgrade script is part of the installation and depends on values you supply during installation.</p>
-
==Step 1. Dump a copy of your version 6 MySQL database==
+
==Step 1. Dump a copy of your version N MySQL database==
     <p>Use the <code>mysqldump</code> command line client to dump a copy of your current version 6 MySQL DLXS database. For example:</p>
     <p>Use the <code>mysqldump</code> command line client to dump a copy of your current version 6 MySQL DLXS database. For example:</p>
-
     <code>% mysqldump -u dlxsadm -p dlxs_v6 &gt; dlxs_v6_dump.sql</code>
+
     <code>% mysqldump -u dlxsadm -p dlxs_vN &gt; dlxs_vN_dump.sql</code>
==Step 2. Create an empty MySQL database==
==Step 2. Create an empty MySQL database==
-
     <p>Create an empty MySQL database specifying the name you want it to have for your DLXS release 14 installation, e.g.  <code>dlxs_v7</code>.  Please refer to this section of the installation instructions for information on creating a MySQL database.</p>
+
     <p>Create an empty MySQL database specifying the name you want it to have for your new DLXS installation, e.g.  <code>dlxs_vN+1</code>.  Please refer to this section of the installation instructions for information on creating a MySQL database.</p>
==Step 3. Populate the empty MySQL database with the dump==
==Step 3. Populate the empty MySQL database with the dump==
-
     <p> Use the <code>mysql</code> command line client to load the dump of your version 6 DLXS database into the empty database that will become a version 7 DLXS database after you upgrade it.</p>
+
     <p> Use the <code>mysql</code> command line client to load the dump of your version N DLXS database into the empty database that will become a version N+1 DLXS database after you upgrade it.</p>
-
     <code>% mysql -u dlxsadm -p dlxs_v7 &lt; dlxs_v6_dump.sql</code>
+
     <code>% mysql -u dlxsadm -p dlxs_vN+1 &lt; dlxs_vN_dump.sql</code>
-
==Step 4.  Run the upgrade_6_7 script==
+
==Step 4.  Run the upgrade_N_N+1 script==
-
    <p>Bring the database into which you loaded the dump up to version 6 using the $DLXSROOT/bin/db/upgrade_6_7 script.  There are no command line parameters to the script.  It reads the server name, database name, username, and password out of $DLXSROOT/lib/LibGlobals.cfg as supplied by you at install time.  </p>
+
'''NOTE:''' When running upgrade scripts from the command line make sure your DLXSROOT environment variable is set to the root of the correct install tree for the given script.  For example, to run '''upgrade_7_8''' for release 15, if the root of your release 15 install tree is '''/usr/local/dlxs15''' your DLXSROOT value should be '''/usr/local/dlxs15'''.  On the other hand, if you are running '''upgrade_6_7''' you would set DLXSROOT to '''/usr/local/dlxs14''', assuming that that is the root of your 14 install tree.
-
    <code>% cd $DLXSROOT/bin/db; ./upgrade_6_7</code>
+
-
     <p><table border="1" width="50%">
+
    <p>Bring the database into which you loaded the dump up to version N+1 using the $DLXSROOT/bin/db/upgrade_N_N+1 script.  There are no command line parameters to the script.  It reads the server name, database name, username, and password out of $DLXSROOT/lib/LibGlobals.cfg as supplied by you at install time.  </p>
 +
    <code>% cd $DLXSROOT/bin/db; ./upgrade_N_N+1</code>
 +
 
 +
     <p><table border="1" width="100%">
         <tr style="bold">
         <tr style="bold">
Line 39: Line 40:
        
        
-
Once the upgrade is completed you will have a version 7 DLXS database compatible with release 14 of the DLXS middleware.
+
Once the upgrade is completed you will have a version N+1 DLXS database compatible with you new installation of DLXS middleware.
 +
==Release 15 changes==
 +
<div class="release_15">'''Release_15''' database changes that would be applies to a release 14 version 7 database to upgrade it to version 8:</div>
 +
<center>
 +
<table border="1" width="75%">
 +
<tr><td>ALTER TABLE ImageClass ADD `field_rel_weight` text</td><td>ADD</td></tr>
 +
<tr><td>UPDATE ImageClass set `field_rel_weight`= '' WHERE class='image'</td><td>UPDATE</td></tr>
 +
<tr><td>ALTER TABLE GroupData ADD `hold` varchar(250)</td><td>ADD</td></tr>
 +
<tr><td>UPDATE GroupData set `hold`= '' WHERE class='bib'</td><td>UPDATE</td></tr>
 +
<tr><td>UPDATE GroupData set `hold`= '' WHERE class='text'</td><td>UPDATE</td></tr>
 +
<tr><td>UPDATE GroupData set `hold`= '' WHERE class='findaid'</td><td>UPDATE</td></tr>
 +
<tr><td>UPDATE GroupData set `hold`= '' WHERE class='image'</td><td>UPDATE</td></tr>
 +
<tr><td>ALTER TABLE Collection ADD `hlbtop` text</td><td>ADD</td></tr>
 +
<tr><td>UPDATE Collection set `hlbtop`= '' WHERE class='bib'</td><td>UPDATE</td></tr>
 +
<tr><td>UPDATE Collection set `hlbtop`= '' WHERE class='text'</td><td>UPDATE</td></tr>
 +
<tr><td>UPDATE Collection set `hlbtop`= '' WHERE class='findaid'</td><td>UPDATE</td></tr>
 +
<tr><td>UPDATE Collection set `hlbtop`= '' WHERE class='image'</td><td>UPDATE</td></tr>
 +
<tr><td>ALTER TABLE Collection ADD `hlbsecond` text</td><td>ADD</td></tr>
 +
<tr><td>UPDATE Collection set `hlbsecond`= '' WHERE class='bib'</td><td>UPDATE</td></tr>
 +
<tr><td>UPDATE Collection set `hlbsecond`= '' WHERE class='text'</td><td>UPDATE</td></tr>
 +
<tr><td>UPDATE Collection set `hlbsecond`= '' WHERE class='findaid'</td><td>UPDATE</td></tr>
 +
<tr><td>UPDATE Collection set `hlbsecond`= '' WHERE class='image'</td><td>UPDATE</td></tr>
 +
<tr><td>ALTER TABLE Collection DROP `hlb2nd`</td><td>DROP</td></tr>
 +
<tr><td>ALTER TABLE ImageClass ADD `searchtypes` text</td><td>ADD</td></tr>
 +
<tr><td>UPDATE ImageClass set `searchtypes`= '' WHERE class='image'</td><td>UPDATE</td></tr>
 +
<tr><td>ALTER TABLE ImageClass ADD `valuelistflds` text</td><td>ADD</td></tr>
 +
<tr><td>UPDATE ImageClass set `valuelistflds`= '' WHERE class='image'</td><td>UPDATE</td></tr>
 +
<tr><td>ALTER TABLE TextClass ADD `imagedisplaysizes` text</td><td>ADD</td></tr>
 +
<tr><td>UPDATE TextClass set `imagedisplaysizes`= '' WHERE class='text'</td><td>UPDATE</td></tr>
 +
<tr><td>ALTER TABLE TextClass DROP `imagedisplaysizes`</td><td>DROP</td></tr>
 +
<tr><td>ALTER TABLE Collection ADD `customfallbackwebdirs` text</td><td>ADD</td></tr>
 +
<tr><td>UPDATE Collection set `customfallbackwebdirs`= '' WHERE class='bib'</td><td>UPDATE</td></tr>
 +
<tr><td>UPDATE Collection set `customfallbackwebdirs`= '' WHERE class='text'</td><td>UPDATE</td></tr>
 +
<tr><td>UPDATE Collection set `customfallbackwebdirs`= '' WHERE class='findaid'</td><td>UPDATE</td></tr>
 +
<tr><td>UPDATE Collection set `customfallbackwebdirs`= '' WHERE class='image'</td><td>UPDATE</td></tr>
 +
<tr><td>ALTER TABLE TextClass ADD `quickbrowse` varchar(250)</td><td>ADD</td></tr>
 +
<tr><td>UPDATE TextClass set `quickbrowse`= '1' WHERE class='text'</td><td>UPDATE</td></tr>
 +
<tr><td>CREATE TABLE `ItemUsage` (`itemid` varchar(255) NOT NULL default '', `collid` varchar(50) NOT NULL default '', `class` varchar(64) NOT NULL default '', `dt` datetime default NULL, `sid` varchar(32) NOT NULL default '', PRIMARY KEY  (`class`,`collid`,`itemid`(63),`sid`)) ENGINE=MyISAM DEFAULT CHARSET=utf8</td><td>CREATE</td></tr>
 +
</table></center>
 +
==OAI Database Upgrade==
 +
<div class="release_15">'''DLXS_15''' This section describes a manual upgrade procedure for DLXS OAI functionality.</div> Prior to release 15 this functionality was based on two tables ('''oai''', '''oaisets''') in the '''dlxs''' database.
-
The following is a list of changes that the upgrade script makes:
+
As of release 15, several new tables have been added and are part of the '''oai''' database instead of the '''dlxs''' database:
-
      <table border="1">
+
-
          <tr style="bold">
+
-
            <th>SQL Command</th>
+
-
          </tr>
+
-
<tr><td>UPDATE `Version` SET `version`='7.0'</td></tr>
+
-
<tr><td>ALTER TABLE Collection ADD `sponsor` text</td></tr>
+
-
<tr><td>UPDATE Collection set `sponsor`= '' WHERE class='bib'</td></tr>
+
-
<tr><td>UPDATE Collection set `sponsor`= '' WHERE class='text'</td></tr>
+
-
<tr><td>UPDATE Collection set `sponsor`= '' WHERE class='findaid'</td></tr>
+
-
<tr><td>UPDATE Collection set `sponsor`= '' WHERE class='image'</td></tr>
+
-
<tr><td>ALTER TABLE Collection ADD `oai` varchar(250)</td></tr>
+
-
<tr><td>UPDATE Collection set `oai`= '' WHERE class='bib'</td></tr>
+
-
<tr><td>UPDATE Collection set `oai`= '' WHERE class='text'</td></tr>
+
-
<tr><td>UPDATE Collection set `oai`= '' WHERE class='findaid'</td></tr>
+
-
<tr><td>UPDATE Collection set `oai`= '' WHERE class='image'</td></tr>
+
-
<tr><td>ALTER TABLE Collection MODIFY `host` varchar(250) DEFAULT 'localhost'</td></tr>
+
-
<tr><td>ALTER TABLE `BookBagDB` MODIFY `username` varchar(255) NOT NULL</td></tr>
+
-
<tr><td>ALTER TABLE ImageClass ADD `brwsadds` varchar(250)</td></tr>
+
-
<tr><td>UPDATE ImageClass set `brwsadds`= 'off' WHERE class='image'</td></tr>
+
-
<tr><td>ALTER TABLE ImageClass ADD `recordcount` varchar(250)</td></tr>
+
-
<tr><td>UPDATE ImageClass set `recordcount`= '' WHERE class='image'</td></tr>
+
-
<tr><td>ALTER TABLE ImageClass ADD `mediacount` varchar(250)</td></tr>
+
-
<tr><td>UPDATE ImageClass set `mediacount`= '' WHERE class='image'</td></tr>
+
-
<tr><td>CREATE TABLE `oaisets` (`id` varchar(150) NOT NULL default '', `oaiset` varchar(32) NOT NULL default '', PRIMARY KEY  (`id`,`oaiset`)) ENGINE=MyISAM DEFAULT CHARSET=utf8</td></tr>
+
-
<tr><td>CREATE TABLE `oai` (`id` varchar(150) NOT NULL default '', `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, `oai_dc` mediumblob, `mods` mediumblob, `marc21` mediumblob, PRIMARY KEY  (`id`), KEY `timestamp` (`timestamp`)) ENGINE=MyISAM DEFAULT CHARSET=utf8</td></tr>
+
-
<tr><td>CREATE TABLE `nameresolver` (`id` tinytext NOT NULL, `coll` tinytext, `url` tinytext, `modified` timestamp NOT NULL default CURRENT_TIMESTAMP, PRIMARY KEY `id` (`id`(300),`coll`(10))) ENGINE=MyISAM DEFAULT CHARSET=utf8</td></tr>
+
-
<tr><td>ALTER TABLE `idresolver` MODIFY `url` mediumtext NULL</td></tr>
+
 +
*oai_backup
 +
*oai_marc
 +
*oai_marc_or
 +
*oai_mods
 +
*oai_test
 +
*oaisets_backup
 +
 +
To upgrade the OAI database from release 14 to 15 follow these steps:
 +
# Create the '''oai''' database with the same permissions as the '''dlxs''' database
 +
# Dump '''oai''', '''oaisets''' from the '''dlxs''' database
 +
# Create empty tables using '''$DLXSROOT/misc/db/db-dump-OAI-release15.sql'''
 +
# Load the dump of '''oai, oaisets''' into the '''oai''' database
 +
# Drop '''oai, oaisets''' from the '''dlxs''' database
-
         
 
-
      </table>
 
[[#top|Top]]
[[#top|Top]]

Current revision

Main Page > Working with DLXS Components > Working with DLXS Utilities and Applications > DLXS Database Upgrade Utility

These instructions assume you are upgrading from a version N MySQL database to a version N+1 database.

Contents

[edit] Overview

This document describes the upgrade process for the dlxs database. This document assumes you are upgrading from a version N MySQL database to a version N+1 database. See the table Database Versions.

Each version of the database is meant to be run with a specific version of DLXS. For example, version 5 of the database works only with versions 12 and 12a of DLXS. If you need to have two or more versions of DLXS running, you will need to set up a database with the appropriate version for each DLXS installation. If this is of interest to you, contact DLXS Help for guidance in doing this.

Below is a series of steps to follow to upgrade your database. You should upgrade the database after installing the DLXS middleware since the upgrade script is part of the installation and depends on values you supply during installation.

[edit] Step 1. Dump a copy of your version N MySQL database

Use the mysqldump command line client to dump a copy of your current version 6 MySQL DLXS database. For example:

   % mysqldump -u dlxsadm -p dlxs_vN > dlxs_vN_dump.sql

[edit] Step 2. Create an empty MySQL database

Create an empty MySQL database specifying the name you want it to have for your new DLXS installation, e.g. dlxs_vN+1. Please refer to this section of the installation instructions for information on creating a MySQL database.

[edit] Step 3. Populate the empty MySQL database with the dump

Use the mysql command line client to load the dump of your version N DLXS database into the empty database that will become a version N+1 DLXS database after you upgrade it.

   % mysql -u dlxsadm -p dlxs_vN+1 < dlxs_vN_dump.sql

[edit] Step 4. Run the upgrade_N_N+1 script

NOTE: When running upgrade scripts from the command line make sure your DLXSROOT environment variable is set to the root of the correct install tree for the given script. For example, to run upgrade_7_8 for release 15, if the root of your release 15 install tree is /usr/local/dlxs15 your DLXSROOT value should be /usr/local/dlxs15. On the other hand, if you are running upgrade_6_7 you would set DLXSROOT to /usr/local/dlxs14, assuming that that is the root of your 14 install tree.

Bring the database into which you loaded the dump up to version N+1 using the $DLXSROOT/bin/db/upgrade_N_N+1 script. There are no command line parameters to the script. It reads the server name, database name, username, and password out of $DLXSROOT/lib/LibGlobals.cfg as supplied by you at install time. 

    % cd $DLXSROOT/bin/db; ./upgrade_N_N+1

IMPORTANT NOTE
Due to schema changes to the dynamic browse tables (ItemBrowse, ItemBrowseCounts, ItemColl) that are not backward compatible, the upgrade process will create new empty versions of these tables. Following the upgrade process it will be necessary to re-generate the data rows for these tables for collections other than the sample data collections sampletc_utf8, sampleic, and samplefa. Please consult the dynamic browse database building documentation


Once the upgrade is completed you will have a version N+1 DLXS database compatible with you new installation of DLXS middleware.

[edit] Release 15 changes

Release_15 database changes that would be applies to a release 14 version 7 database to upgrade it to version 8:
ALTER TABLE ImageClass ADD `field_rel_weight` textADD
UPDATE ImageClass set `field_rel_weight`= WHERE class='image'UPDATE
ALTER TABLE GroupData ADD `hold` varchar(250)ADD
UPDATE GroupData set `hold`= WHERE class='bib'UPDATE
UPDATE GroupData set `hold`= WHERE class='text'UPDATE
UPDATE GroupData set `hold`= WHERE class='findaid'UPDATE
UPDATE GroupData set `hold`= WHERE class='image'UPDATE
ALTER TABLE Collection ADD `hlbtop` textADD
UPDATE Collection set `hlbtop`= WHERE class='bib'UPDATE
UPDATE Collection set `hlbtop`= WHERE class='text'UPDATE
UPDATE Collection set `hlbtop`= WHERE class='findaid'UPDATE
UPDATE Collection set `hlbtop`= WHERE class='image'UPDATE
ALTER TABLE Collection ADD `hlbsecond` textADD
UPDATE Collection set `hlbsecond`= WHERE class='bib'UPDATE
UPDATE Collection set `hlbsecond`= WHERE class='text'UPDATE
UPDATE Collection set `hlbsecond`= WHERE class='findaid'UPDATE
UPDATE Collection set `hlbsecond`= WHERE class='image'UPDATE
ALTER TABLE Collection DROP `hlb2nd`DROP
ALTER TABLE ImageClass ADD `searchtypes` textADD
UPDATE ImageClass set `searchtypes`= WHERE class='image'UPDATE
ALTER TABLE ImageClass ADD `valuelistflds` textADD
UPDATE ImageClass set `valuelistflds`= WHERE class='image'UPDATE
ALTER TABLE TextClass ADD `imagedisplaysizes` textADD
UPDATE TextClass set `imagedisplaysizes`= WHERE class='text'UPDATE
ALTER TABLE TextClass DROP `imagedisplaysizes`DROP
ALTER TABLE Collection ADD `customfallbackwebdirs` textADD
UPDATE Collection set `customfallbackwebdirs`= WHERE class='bib'UPDATE
UPDATE Collection set `customfallbackwebdirs`= WHERE class='text'UPDATE
UPDATE Collection set `customfallbackwebdirs`= WHERE class='findaid'UPDATE
UPDATE Collection set `customfallbackwebdirs`= WHERE class='image'UPDATE
ALTER TABLE TextClass ADD `quickbrowse` varchar(250)ADD
UPDATE TextClass set `quickbrowse`= '1' WHERE class='text'UPDATE
CREATE TABLE `ItemUsage` (`itemid` varchar(255) NOT NULL default , `collid` varchar(50) NOT NULL default , `class` varchar(64) NOT NULL default , `dt` datetime default NULL, `sid` varchar(32) NOT NULL default , PRIMARY KEY (`class`,`collid`,`itemid`(63),`sid`)) ENGINE=MyISAM DEFAULT CHARSET=utf8CREATE

[edit] OAI Database Upgrade

DLXS_15 This section describes a manual upgrade procedure for DLXS OAI functionality.
Prior to release 15 this functionality was based on two tables (oai, oaisets) in the dlxs database.

As of release 15, several new tables have been added and are part of the oai database instead of the dlxs database:

  • oai_backup
  • oai_marc
  • oai_marc_or
  • oai_mods
  • oai_test
  • oaisets_backup

To upgrade the OAI database from release 14 to 15 follow these steps:

  1. Create the oai database with the same permissions as the dlxs database
  2. Dump oai, oaisets from the dlxs database
  3. Create empty tables using $DLXSROOT/misc/db/db-dump-OAI-release15.sql
  4. Load the dump of oai, oaisets into the oai database
  5. Drop oai, oaisets from the dlxs database


Top

Retrieved from "http://webservices.itcs.umich.edu/mediawiki/dlxs15/index.php/DLXS_Database_Upgrade_Utility"
Personal tools