DLXS Database Upgrade Utility

From DLXS Documentation

(Difference between revisions)

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.

[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

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.

The following is a list of changes that the upgrade_6_7 script makes in DLXS release 14:

SQL Command
UPDATE `Version` SET `version`='7.0'
ALTER TABLE Collection ADD `sponsor` text
UPDATE Collection set `sponsor`= WHERE class='bib'
UPDATE Collection set `sponsor`= WHERE class='text'
UPDATE Collection set `sponsor`= WHERE class='findaid'
UPDATE Collection set `sponsor`= WHERE class='image'
ALTER TABLE Collection ADD `oai` varchar(250)
UPDATE Collection set `oai`= WHERE class='bib'
UPDATE Collection set `oai`= WHERE class='text'
UPDATE Collection set `oai`= WHERE class='findaid'
UPDATE Collection set `oai`= WHERE class='image'
ALTER TABLE Collection MODIFY `host` varchar(250) DEFAULT 'localhost'
ALTER TABLE `BookBagDB` MODIFY `username` varchar(255) NOT NULL
ALTER TABLE ImageClass ADD `brwsadds` varchar(250)
UPDATE ImageClass set `brwsadds`= 'off' WHERE class='image'
ALTER TABLE ImageClass ADD `recordcount` varchar(250)
UPDATE ImageClass set `recordcount`= WHERE class='image'
ALTER TABLE ImageClass ADD `mediacount` varchar(250)
UPDATE ImageClass set `mediacount`= WHERE class='image'
CREATE TABLE `oaisets` (`id` varchar(150) NOT NULL default , `oaiset` varchar(32) NOT NULL default , PRIMARY KEY (`id`,`oaiset`)) ENGINE=MyISAM DEFAULT CHARSET=utf8
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
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
ALTER TABLE `idresolver` MODIFY `url` mediumtext NULL

Top

@@ Line 1: / Line 1: @@
 [[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 5 MySQL database used with DLXS release 12, 12a to a version 6 database MySQL for DLXS release 13.</p>
+<p>These instructions assume you are upgrading from a version N MySQL database to a version N+1 database.</p>
 ==Overview==
-     <p>This document describes the upgrade process for the dlxs database. This document assumes you are upgrading from a version 5 MySQL database used with DLXS release 12, 12a to a version 6 database MySQL for DLXS release 13. Please consult previous release documentation at http://docs.dlxs.org/ for upgrading from earlier database versions. Also, please note that the CSV database option is not supported in DLXS release 13, database version 6.  </p>
+     <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><font color="red">If you are currently running a version 5 '''CSV''' database you must create an empty MySQL database and run the script <i>$DLXSROOT/bin/db/dbmove -s CSV -t MySQL</i> to populate it before following the steps below.  Please refer to the [[dbmove|DBMOVE documentation]] for this step.</font></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>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 5 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 5 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_v5 &gt; dlxs_v5_dump.sql</code>
+     <code>% mysqldump -u dlxsadm -p dlxs_vN &gt; dlxs_vN_dump.sql</code>
 ==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 13 installation, e.g.  <code>dlxs_v6</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==
-     <p> Use the <code>mysql</code> command line client to load the dump of your version 5 DLXS database into the empty database that will become a version 6 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_v6 &lt; dlxs_v5_dump.sql</code>
+     <code>% mysql -u dlxsadm -p dlxs_vN+1 &lt; dlxs_vN_dump.sql</code>
-==Step 4.  Run the upgrade_5_6 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_5_6 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>
+     <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_5_6</code>
+      <code>% cd $DLXSROOT/bin/db; ./upgrade_N_N+1</code>
      <p><table border="1" width="50%">
@@ Line 34: / Line 33: @@
            </tr>
            <tr>
-             <td>Due to schema changes to the dynamic browse tables (ItemBrowse, ItemBrowseCounts, ItemColl) that are not backward compatible, the upgrade process will create new <b>empty</b> 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 <tt>sampletc_utf8</tt>, <tt>sampleic</tt>, and <tt>samplefa</tt>.  Please consult the [Setting_up_Dynamic_Browsing|dynamic browse database building documentation]</td>
+             <td>Due to schema changes to the dynamic browse tables (ItemBrowse, ItemBrowseCounts, ItemColl) that are not backward compatible, the upgrade process will create new <b>empty</b> 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 <tt>sampletc_utf8</tt>, <tt>sampleic</tt>, and <tt>samplefa</tt>.  Please consult the [[Setting_up_Dynamic_Browsing|dynamic browse database building documentation]]</td>
            </tr>
@@ Line 40: / Line 39: @@
-Once the upgrade is completed you will have a version 6 DLXS database compatible with release 13 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.
-The following is a list of changes that the upgrade script makes:
+The following is a list of changes that the upgrade_6_7 script makes in DLXS release 14:
        <table border="1">
            <tr style="bold">
              <th>SQL Command</th>
            </tr>
-          <tr><td>DROP TABLE `DbStructure`</td></tr>
+ <tr><td>UPDATE `Version` SET `version`='7.0'</td></tr>
-          <tr><td>DROP TABLE `Bkup_BibClass`, `Bkup_BibClassGroup`, `Bkup_Collection`, `Bkup_FindaidClass`, `Bkup_FindaidClassGroup`, `Bkup_GroupColl`, `Bkup_GroupData`, `Bkup_ImageClass`, `Bkup_ImageClassGroup`, `Bkup_TextClass`, `Bkup_TextClassGroup`</td></tr>
+<tr><td>ALTER TABLE Collection ADD `sponsor` text</td></tr>
-          <tr><td>ALTER TABLE `ImageClass` DROP `viewtabform`</td></tr>
+<tr><td>UPDATE Collection set `sponsor`= '' WHERE class='bib'</td></tr>
-          <tr><td>UPDATE `Collection` SET `appmodule`='ImageApp' WHERE `appmodule`='ImageApp/mysqlImageApp'</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>ALTER TABLE `Collection` ADD COLUMN `authsystems` text</td></tr>
+<tr><td>UPDATE Collection set `sponsor`= '' WHERE class='image'</td></tr>
-          <tr><td>UPDATE Collection SET authsystems='um'</td></tr>
+<tr><td>ALTER TABLE Collection ADD `oai` varchar(250)</td></tr>
-          <tr><td>ALTER TABLE `ImageClass` ADD COLUMN `range_srchflds` text NULL DEFAULT NULL</td></tr>
+<tr><td>UPDATE Collection set `oai`= '' WHERE class='bib'</td></tr>
-          <tr><td>ALTER TABLE `ImageClass` ADD COLUMN `privileged_users` text NULL DEFAULT NULL</td></tr>
+<tr><td>UPDATE Collection set `oai`= '' WHERE class='text'</td></tr>
-          <tr><td>ALTER TABLE `Collection` ADD COLUMN `browseupdatemodule` varchar(250) NULL DEFAULT NULL</td></tr>
+<tr><td>UPDATE Collection set `oai`= '' WHERE class='findaid'</td></tr>
-          <tr><td>UPDATE `Collection` SET `browseupdatemodule`='BrowseUpdate/ImageMysqlBU' WHERE `class`='image' AND (`browseupdatemodule` IS NULL OR `browseupdatemodule`='')</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>UPDATE `Collection` SET `browseupdatemodule`='BrowseUpdate/FindaidBU' WHERE `class`='findaid' AND (`browseupdatemodule` IS NULL OR `browseupdatemodule`='')</td></tr>
+<tr><td>ALTER TABLE `BookBagDB` MODIFY `username` varchar(255) NOT NULL</td></tr>
-          <tr><td>UPDATE `Collection`, `TextClass` SET `browseupdatemodule`= CASE WHEN `encodingtype`='monograph' THEN 'BrowseUpdate/MonographBU' WHEN `encodingtype`='serialissue' THEN 'BrowseUpdate/SerialIssueBU' WHEN `encodingtype`='serialarticle' THEN 'BrowseUpdate/SerialArticleBU' ELSE NULL END WHERE `Collection`.`collid`=`TextClass`.`collid` AND (`browseupdatemodule` IS NULL OR `browseupdatemodule`='')</td></tr>
+<tr><td>ALTER TABLE ImageClass ADD `brwsadds` varchar(250)</td></tr>
-          <tr><td>ALTER TABLE `GroupData` DROP COLUMN `ignoreon`</td></tr>
+<tr><td>UPDATE ImageClass set `brwsadds`= 'off' WHERE class='image'</td></tr>
-          <tr><td>DROP TABLE `ItemBrowse`</td></tr>
+<tr><td>ALTER TABLE ImageClass ADD `recordcount` varchar(250)</td></tr>
-          <tr><td>CREATE TABLE `ItemBrowse` (`idno` varchar(250) default NULL, `collid` varchar(250) default NULL, `field` varchar(250) default NULL, `value` varchar(250) default NULL, `rank` varchar(250) default NULL, KEY `idno` (`idno`), KEY `collid` (`collid`), KEY `field` (`field`), KEY `value` (`value`))</td></tr>
+<tr><td>UPDATE ImageClass set `recordcount`= '' WHERE class='image'</td></tr>
-          <tr><td>DROP TABLE `ItemBrowseCounts`</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 `ItemBrowseCounts` (`collid` varchar(250) default NULL, `field` varchar(250) default NULL, `value` varchar(250) default NULL, `count` int(11) default NULL, KEY `field` (`field`), KEY `value` (`value`), KEY `collid` (`collid`))</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>DROP TABLE `ItemColl`</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 `ItemColl` (`idno` varchar(250) NOT NULL default '', `collid` varchar(250) default NULL, `moddate` date default NULL, `xmlmeta` text, `pagesequence` int(11) default NULL, KEY `idno` (`idno`), KEY `collid` (`collid`))</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 `PageviewFig` ADD INDEX(`seq`)</td></tr>
+<tr><td>ALTER TABLE `idresolver` MODIFY `url` mediumtext NULL</td></tr>
-          <tr><td>DROP TABlE `sessions`</td></tr>
-          <tr><td>CREATE TABLE `sessions` (`id` varchar(32) NOT NULL default '', `a_session` longblob, PRIMARY KEY (`id`))</td></tr>
-          <tr><td>DROP TABLE IF EXISTS `BookBagDB_upgradetemp`</td></tr>
-          <tr><td>CREATE TABLE `BookBagDB_upgradetemp` DEFAULT CHARSET=utf8 SELECT * FROM `BookBagDB`</td></tr>
-          <tr><td>ALTER TABLE `BookBagDB_upgradetemp` MODIFY `id` int(10) unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY</td></tr>
-          <tr><td>ALTER TABLE `BookBagDB_upgradetemp` MODIFY `modified` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP</td></tr>
-          <tr><td>ALTER TABLE `BookBagDB_upgradetemp` ADD INDEX `username_2` (`username`,`class`)</td></tr>
-          <tr><td>ALTER TABLE `BookBagDB_upgradetemp` ADD INDEX(`shared`)</td></tr>
-          <tr><td>ALTER TABLE `BookBagDB_upgradetemp` ADD INDEX(`class`)</td></tr>
-          <tr><td>ALTER TABLE `BookBagDB_upgradetemp` ADD INDEX(`temp`)</td></tr>
-          <tr><td>DROP TABlE `BookBagDB`</td></tr>
-          <tr><td>ALTER TABLE `BookBagDB_upgradetemp` RENAME TO `BookBagDB`</td></tr>
        </table>
 [[#top|Top]]

DLXS Database Upgrade Utility

From DLXS Documentation

Current revision

Contents

[edit] Overview

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

[edit] Step 2. Create an empty MySQL database

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

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

Views

Personal tools

Navigation

Search

Toolbox