DLXS Database Upgrade Utility

Overview

This document discusses strategies and specific procedures to accomplish a successful upgrade of the dlxs database. If you are using DLXS version 9, you will want to upgrade to version 2 of the metadata database. If you are using DLXS version 10, you will want to upgrade to version version 3 of the metadata database. The procedure you follow to upgrade the database depends on what version of the database you are starting with. If you have version 1 of the database and you want to upgrade to version 5, you will first have to run the script $DLXSROOT/bin/db/upgrade_1_2 to get to version 2, $DLXSROOT/bin/db/upgrade_2_3 to get to version 3, $DLXSROOT/bin/db/upgrade_3_4 to get to version 4, and finally $DLXSROOT/bin/db/upgrade_3_4 to get to version 5.

It is important to keep in mind that each version of the database is meant to be run with a specific version of DLXS. For example, version 2 of the database works with version 9 of DLXS, but will not work with other versions of DLXS. So, if you need to have two or more versions of DLXS running, you will need to setup a database with the appropriate version for each of your DLXS installations. If this is of interest to you, contact DLXS Help for guidance in doing this.

Below is a series of steps to follow when upgrading your database. In order to update to version 2 of the database, you must have a version 1 of the database. If you do not have a version 1 of the database, start at Step 1. If you have a version 1 of the database and want to update to version 2, go to Step 3. If you have a version 2 of the database and want to update to version 3, go to Step 4. If you have a version 3 of the database and want to update to version 4, go to Step 5. If you have a version 4 of the database and want to update to version 5, go to Step 6.

Step 1. To upgrade to Version 1 of the database

To upgrade to version 1 of the database, you need run $DLXRROOT/bin/db/dconv. This script will copy all the data in the colldb and groups files used by image, bib, and text class into a CSV database. The CSV database created will be in $DLXSROOT/misc/db directory.

Step 2. CVS vs MySQL

Now that you have copied all your data from the colldb and groups file into a CSV database, you can have the middleware access this metadata database. But if you have a MySQL installation at your site, you may wish to move the CSV installation to a MySQL database. To do this you can run the script $DLXSROOT/bin/db/dbmove -s CSV -t MySQL (the s stands for source, and the t for target). Before you do this you will need to create a database by the name of dlxs, and two users for this database with specific privileges, These users are dlxs, and dlxsadm. Refer to the section of the DLXS documentation on database users to get more information about setting up the privileges for these users.

If at any time you wish to move back to a CSV database, you can run the command $DLXSROOT/bin/db/dbmove -r all -s MySQL -t CSV. Just make sure that the $DLXSROOT/misc/db directory is empty and that the script has permissions to write to it. This database move script will not move the entire MySQL database to CSV, since the MySQL database may contain an enormous amount of data which could be potentially hazardous to place on a disk. To learn more about the dbmove command read the documentation section about the dbmove script.

Step 3. To upgrade to version 2 of the database from version 1

If you need to upgrade version 1 of the database to version 2, you will need to use the script $DLXSROOT/bin/db/upgrade_1_2. $DLXSROOT/bin/db/upgrade_1_2 can be run against either a MySQL database or a CSV database.

To upgrade a MySQL database enter $DLXSROOT/bin/db/upgrade_1_2 -d MySQL. Notice that the upgrade will try to backup the MySQL database into the directory $DLXSROOT/misc/MySQL_DB_Backup before performing the upgrade. This is done just in case something goes wrong, you'll have a backup of the database. From the backup, you should be able to use the MySQL mysqlimport script to recover your database if you need to.

To upgrade a CSV database enter $DLXSROOT/bin/db/upgrade_1_2 -d CSV. Notice that the upgrade will try to backup the CSV database into the directory $DLXSROOT/misc/CSV_DB_Backup before performing the upgrade. This is done just in case something goes wrong, you'll have a backup of the database.

Once the upgrade is completed you should have a version 2 of the metadata database that is compatible with version 9 of the DLXS.

The following is a list of changes that the upgrade script makes:

Field Table Description
shortrecordfilter BibClass Deleted
longrecordfilter BibClass Deleted
downloadrecordfilter BibClass Deleted
emailrecordfilter BibClass Deleted
pageviewport TextClass Deleted
browseable Collection Added
orgtext Collection Added
orglink Collection Added
lel2divtype TextClass Added
idno Pageview Changed to varchar(32) NOT NULL
image Pageview Changed to varchar(32) NOT NULL
seq Pageview Changed to varchar(16) NOT NULL
n Pageview Changed to varchar(16) DEFAULT NULL
ftr Pageview Changed to varchar(16) DEFAULT NULL

The sessions table is redefined to contain id varchar(32) default null, a_session long blob, and a key by the name of sessions_index composed of id is created.

Step 4. To upgrade to version 3 of the database from version 2

If you need to upgrade version 2 of the database to version 3, you will need to use the script $DLXSROOT/bin/db/upgrade_2_3. $DLXSROOT/bin/db/upgrade_2_3 can be run against either a MySQL database or a CSV database.

To upgrade a MySQL database enter $DLXSROOT/bin/db/upgrade_2_3 -d MySQL. Notice that the upgrade will try to backup the MySQL database into the directory $DLXSROOT/misc/MySQL_DB_Backup before performing the upgrade. This is done just in case something goes wrong, you'll have a backup of the database. From the backup, you should be able to use the MySQL mysqlimport script to recover your database if you need to.

To upgrade a CSV database enter $DLXSROOT/bin/db/upgrade_2_3 -d CSV. Notice that the upgrade will try to backup the CSV database into the directory $DLXSROOT/misc/CSV_DB_Backup before performing the upgrade. This is done just in case something goes wrong, you'll have a backup of the database.

Once the upgrade is completed you should have a version 3 of the metadata database that is compatible with version 10 of the DLXS.

The following is a list of changes that the upgrade script makes:

Field Table Description
defaultgroupid Collection Added
groupdescr GroupData Added
ignoreon GroupData Added
viewtabform ImageClass Added
detailviewtype TextClass Added
encodingtype TextClass Added
detailviewlayout TextClass Added
sortflds TextClass Added
lel2divtype TextClass Deleted
rankfile TextClass Deleted
resultsviewlength TextClass Deleted
longviewtype TextClass Deleted
language TextClass Deleted
gender TextClass Deleted
genre TextClass Deleted
period TextClass Deleted
primarytitle ImageClassGroup Deleted
groupdescr ImageClassGroup Deleted
ignoreon Collection Changed to text type
cnf Pageview Changed to int(11)
bpp Pageview Added

A new table is added:PageviewFig. This table consist of idno varchar(32) with default value of '', seq int (11) with default value of 0, image varchar(32) with default value of '', and a key by the name of idno composed of idno and seq is created. The sessions table is redefined to contain id varchar(32) default null, a_session long blob, and a key by the name of sessions_index composed of id is created.

Step 5. To upgrade to version 4 of the database from version 3

If you need to upgrade version 3 of the database to version 4, you will need to use the script $DLXSROOT/bin/db/upgrade_3_4. $DLXSROOT/bin/db/upgrade_3_4 can be run against either a MySQL database or a CSV database.

To upgrade a MySQL database enter $DLXSROOT/bin/db/upgrade_3_4 -d MySQL. Notice that the upgrade will try to backup the MySQL database into the directory $DLXSROOT/misc/MySQL_DB_Backup before performing the upgrade. This is done just in case something goes wrong, you'll have a backup of the database. From the backup, you should be able to use the MySQL mysqlimport script to recover your database if you need to.

To upgrade a CSV database enter $DLXSROOT/bin/db/upgrade_3_4 -d CSV. Notice that the upgrade will try to backup the CSV database into the directory $DLXSROOT/misc/CSV_DB_Backup before performing the upgrade. This is done just in case something goes wrong, you'll have a backup of the database.

Once the upgrade is completed you should have a version 4 of the metadata database that is compatible with version 11 of the DLXS.

The following is a list of changes that the upgrade script makes:

Field Table Description
hold Collection Added
querylanguage Collection Added
rendering Collection Added
perlpifillermodule Collection Added
xsltpifillermodule Collection Added
sortflds Collection Added
primarytitle Collection Added
secondarytitle Collection Added
field_labels ImageClass Added
field_xcoll_maps ImageClass Added
field_admin_maps ImageClass Added
data_table ImageClass Added
media_table ImageClass Added
mediahost ImageClass Added
field_labels ImageClassGroup Added
printondemand TextClass Added
pageviewhost TextClass Added
primarytitle BibClass Deleted
secondarytitle BibClass Deleted
banner ImageClass Deleted
sortflds ImageClass Deleted
sortflds TextClass Deleted
standalonename TextClass Deleted
maxdepthitems TextClass Deleted
bbagcontent BookBagDB Deleted

The tables: BookBagItems, idresolver, FindaidClass, and FindaidClassGroup were added. Below is a list of the fields in these tables:

Field Table
bbid BokBagItems
itemid BokBagItems
collid BokBagItems
class BokBagItems
userorder BokBagItems
usernotes BokBagItems
id idresolver
url idresolver
modified idresolver
userid FindAidClass
class FindAidClass
collid FindAidClass
termsearch FindAidClass
regionsearch FindAidClass
containerdepth FindAidClass
userid FindAidClassGroup
class FindAidClassGroup
groupid FindAidClassGroup

Step 6. To upgrade to version 5 of the database from version 4

If you need to upgrade version 4 of the database to version 5, you will need to use the script $DLXSROOT/bin/db/upgrade_4_5. $DLXSROOT/bin/db/upgrade_4_5 can be run against either a MySQL database or a CSV database.

To upgrade a MySQL database enter $DLXSROOT/bin/db/upgrade_4_5 -d MySQL. Notice that the upgrade will try to backup the MySQL database into the directory $DLXSROOT/misc/MySQL_DB_Backup before performing the upgrade. This is done just in case something goes wrong, you'll have a backup of the database. From the backup, you should be able to use the MySQL mysqlimport script to recover your database if you need to.

To upgrade a CSV database enter $DLXSROOT/bin/db/upgrade_4_5 -d CSV. Notice that the upgrade will try to backup the CSV database into the directory $DLXSROOT/misc/CSV_DB_Backup before performing the upgrade. This is done just in case something goes wrong, you'll have a backup of the database.

Once the upgrade is completed you should have a version 5 of the metadata database that is compatible with version 12 of the DLXS.

The following is a list of changes that the upgrade script makes:

Field Table Description
colldescr Collection Added
devhost Collection Added
languagepriority Collection Added
browsefields Collection Added
perlpifillermodule Collection Deleted
pvstoredformatprioritymap TextClass Added
pvinputoutputmap TextClass Added
browsenav TextClass Added
defaultpageviewsize TextClass Added
displaynotesinline TextClass Added
pageviewimageformat TextClass Deleted
storedimageformat TextClass Deleted
charfilters TextClass Deleted
notesfilters TextClass Deleted
wwrealms TextClass Deleted
wwrealmseng TextClass Deleted
field_load_maps ImageClass Added
searchable_items ImageClass Added
entryauth ImageClass Added
browsenav ImageClass Added
mediadevhost ImageClass Added
brosenav FindaidClass Added
sortpriority GroupData Added
languagepriority GroupData Added
groupdescr GroupData Change to be type text
DbStructure type Change to be type text
BibClass userid Change to be type varchar(100)
BibClass collid Change to be type varchar(100)
BibClassGroup userid Change to be type varchar(100)
BibClassGroup groupid Change to be type varchar(100)
Collection userid Change to be type varchar(100)
Collection collid Change to be type varchar(100)
FindaidClass userid Change to be type varchar(100)
FindaidClass collid Change to be type varchar(100)
FindaidClassGroup userid Change to be type varchar(100)
FindaidClassGroup groupid Change to be type varchar(100)
GroupData userid Change to be type varchar(100)
GroupData groupid Change to be type varchar(100)
ImageClass userid Change to be type varchar(100)
ImageClass collid Change to be type varchar(100)
ImageClassGroup userid Change to be type varchar(100)
ImageClassGroup groupid Change to be type varchar(100)
DbStructure tablename Change to be type varchar(100)
DbStructure fieldname Change to be type varchar(100)

The following tables were added: ItemBrowse, ItemBroseCounts, ItemColl, RestrictedItems, ArticleClips. Also, the following image tables were added for the MySQL installation: sampleic, sampleic_media, workshopic, and workshopic_media. The database was made utf8.