#!/usr/bin/perl -w

################################################################################
# Preparation:
#  - Dump opl_preference.var:
#    DIR=/tmp/opl_preference; \rm -rf $DIR; mkdir $DIR; PPP=`grep addsite_pass /etc/opals/opalsrc | cut -d= -f2`; for i in `ls /etc/opals/conf/`; do OUT=$DIR/opl_preference.var.$i; \rm -f $OUT; echo Dump opl_preference.var $i to $OUT; mysql -htethys -uopalsadmin -p$PPP $i < /tmp/opl_preference.var.sql > $OUT; done; unset PPP
#  - Dump SQL database (only if there's no nightly backup):
#    DIR=/tmp/db; \rm -rf $DIR; mkdir $DIR; PPP=`grep addsite_pass /etc/opals/opalsrc | cut -d= -f2`; for i in `ls /etc/opals/conf/`; do OUT=$DIR/db-$i.sql; \rm -f $OUT; echo Dump SQL database $i to $OUT; mysqldump -htethys -uopalsadmin -p$PPP $i > $OUT; done; unset PPP
#  - Find missing/unwanted preferences:
#    REF=4cls_fpl.opl_preference.var.sql; for i in /tmp/opl_preference/opl_preference.var.*; do diff -uN $REF $i; done | grep -e ^- -e ^+ | grep -v '^---' | grep -v '^+++' | sed -re 's/^[-+]//' | sort -g | uniq
################################################################################

BEGIN {
    sub print_help {
    print <<_STR_;
NAME:
    .
SYNOPSIS:
    PERL5LIB=/www/opals/module OPALS_CONF=/etc/opals/conf/\$SITECODE \\
        $0 [param1] [param2] [...]
DESCRIPTION:
    -param1  description 1...
_STR_
}
    if (!$ENV{'PERL5LIB'} || !$ENV{'OPALS_CONF'}) {
        print_help();
        exit 1;
    }
}

#use Getopt::Std;
#
#my %options = ();
#getopts("c:",\%options);
#my $configFile = $options{c};

use Opals::Context;

use strict;
my $dbh = Opals::Context->dbh();;
END {
    if ($dbh) {
        $dbh->disconnect();
    }
}

$| = 1;
# Codes start...

alter_opl_zDatabase(dbh => $dbh);
alter_table_opl_preference(dbh => $dbh);
update_table_opl_preference(dbh => $dbh);
delete_preferences(dbh => $dbh);
add_preferences(dbh => $dbh);
create_tables(dbh => $dbh);
create_new_permission(dbh => $dbh);
create_new_menu_item(dbh => $dbh);
update_user_permissions(dbh => $dbh);
alter_table_opl_marcImport(dbh => $dbh);
alter_table_opl_fineRate(dbh => $dbh);
alter_table_opl_hold(dbh => $dbh);
update_index_opl_item(dbh => $dbh);
alter_table_opl_itemType(dbh => $dbh);
update_index_opl_marcRecord(dbh => $dbh);
alter_table_opl_menuItem(dbh => $dbh);
alter_table_opl_odl(dbh => $dbh);
alter_table_opl_openHours(dbh => $dbh);
alter_table_opl_transactiondetail(dbh => $dbh);
alter_table_opl_transactions(dbh => $dbh);
alter_table_user_guardian(dbh => $dbh);
update_index_opl_transactions(dbh => $dbh);
alter_table_pf_bookRs(dbh => $dbh);
alter_table_pf_webRs(dbh => $dbh);
update_index_tb_courseList(dbh => $dbh);
update_index_tb_index_map(dbh => $dbh);
alter_table_tb_items(dbh => $dbh);
update_index_tb_items(dbh => $dbh);
alter_table_tb_records(dbh => $dbh);

update_index_eq_items(dbh => $dbh);

# Codes end.

exit 0;
################################################################################


sub check_column  {
    my (%param) = @_;

    my $dbh = $param{'dbh'};
    my $table = $param{'table'};
    my $column = $param{'column'};

    my $col = $dbh->selectrow_hashref("desc $table $column");

    if ($col && $col->{'Field'} eq $column) {
        print "$table.$column existed\n";

        return 1;
    }

    print "$table.$column not existed\n";

    return 0;
}
############################################################


sub check_row {
    my (%param) = @_;

    my $dbh = $param{'dbh'};
    my $sql;
    my $table = $param{'table'};
    my $column = $param{'column'};
    my $value = $param{'value'};

    $sql = <<_SQL_;
select  *
from    $table
where   $column = ?
_SQL_

    my $p = $dbh->selectrow_hashref($sql, undef, $value);
    if ($p) {
        print "$table: $value existed\n";

        return 1;
    }

    print "$table: $value not existed\n";

    return 0;
}
############################################################


sub dbh_do {
    my (%param) = @_;

    my $dbh = $param{'dbh'};
    my $sql = $param{'sql'};
    my @val;

    print <<_STR_;
$sql
_STR_

    my $rv;
    if (defined $param{'val'}) {
        @val = @{$param{'val'}};
        print join(', ', @val), "\n";
        $rv = $dbh->do($sql, undef, @val);
    }
    else {
        $rv = $dbh->do($sql);
    }

    if ($rv) {
        print "Execution succeeded.\n";
    }
    else {
        print "Execution failed.\n";
    }

    print <<_STR_;
############################################################
_STR_

    return $rv;
}
############################################################


sub alter_opl_zDatabase {
    my (%param) = @_;

    my $dbh = $param{'dbh'};

    # add circData
    if (check_column(
            dbh     => $dbh, 
            table   => 'opl_zDatabase', 
            column  => 'circData') != 1) {
        dbh_do(
            dbh => $dbh,
            sql => <<_SQL_
alter table opl_zDatabase
    add circData tinyint(1) unsigned not null default 0
_SQL_
        );
    }


    # add studentILL
    if (check_column(
            dbh     => $dbh, 
            table   => 'opl_zDatabase', 
            column  => 'studentILL') != 1) {
        dbh_do(
            dbh => $dbh,
            sql => <<_SQL_
alter table opl_zDatabase
    add studentILL tinyint(1) unsigned not null default 0
_SQL_
        );
    }


    # add locationId
    if (check_column(
            dbh     => $dbh, 
            table   => 'opl_zDatabase', 
            column  => 'locationId') != 1) {
        dbh_do(
            dbh => $dbh,
            sql => <<_SQL_
alter table opl_zDatabase
    add locationId varchar(50)
_SQL_
        );
    }
}
############################################################


sub insert_preference {
    my (%param) = @_;

    my $dbh = $param{'dbh'};
    my $var = $param{'var'};

    if (check_row(
            dbh => $dbh,
            table => 'opl_preference',
            column => 'var',
            value => $var) == 1) {
        return;
    }

    dbh_do(
        dbh => $dbh,
        sql => <<_SQL_,
insert 
into    opl_preference 
set     var = ?,
        val = ?,
        valShow = ?,
        opt = ?,
        varType = ?,
        description = ?,
        hidden = ?,
        gid = ?,
        gOrder = ?
_SQL_
        val => [
            $var,
            $param{'val'},
            $param{'valShow'},
            $param{'opt'},
            $param{'varType'},
            $param{'description'},
            $param{'hidden'},
            $param{'gid'},
            $param{'gOrder'}
        ]   
    );

    return 1;
}
############################################################


sub delete_preferences {
    my (%param) = @_;

    my $dbh = $param{'dbh'};

    dbh_do(
        dbh => $dbh,
        sql => <<_SQL_
delete
from    opl_preference
where   var not in (
            'allowOverdue',
            'amazonId',
            'amazonSecreteKey',
            'autoAdd2NewItemList',
            'autoUpdateUnion',
            'barcodeType',
            'bibformat',
            'bibTimeclear',
            'cerf',
            'charge_damage',
            'charge_lost',
            'charge_overdue',
            'circulationSound',
            'country',
            'cssTheme',
            'dateFirst',
            'dateLast',
            'dateProfile',
            'defaultSearchPage',
            'defCategory',
            'emailAddress',
            'emailPass',
            'emailSmtp',
            'emailUser',
            'equipmentMgmnt',
            'fdrtax',
            'fine',
            'iumap',
            'iuset',
            'lang',
            'libAddress',
            'libCity',
            'libcode',
            'libEmail',
            'libFax',
            'libname',
            'libPhone',
            'libState',
            'libZip',
            'link1_name',
            'link1_url',
            'link2_name',
            'link2_url',
            'lockingDatetime',
            'multilingual',
            'overwrittenByBarcode',
            'OWLBgSrchFields',
            'OWLsrchLocation',
            'patronItemPrivacy',
            'prvtax',
            'rating',
            'recordLock',
            'reportGroup',
            'searchAdv',
            'showARL',
            'showBeginnerSrch',
            'showLocation',
            'showPathfinder',
            'slogan',
            'syndeticsId',
            'textbookChargeDamage',
            'textbookChargeLost',
            'textbookChargeOverdue',
            'textbookFine',
            'textbookMgmnt',
            'theme',
            'timeclear',
            'timeout',
            'timezone',
            'union852',
            'unionCircUpdate',
            'unionCircUpdateDate',
            'unionId',
            'unionSvcUrl',
            'useAttOrder',
            'useHolidays',
            'validateBarcode',
            'warningDatetime'
        )
_SQL_
    );
#            'datesClosed',
#            'http://scsd.calypso.scool',
#            'lengthReserve',
#            'logo',
#            'reqToLimit',
#            'workdayFirst',
#            'workdayLast'
}
############################################################


sub add_preferences {
    my (%param) = @_;

    my $dbh = $param{'dbh'};

    my @new_pref = (
        {
            var         => 'barcodeType',
            val         => '0',
            valShow     => 'default',
            opt         => '0|1|2|3',
            varType     => 'select',
            description => 'Barcode type',
            hidden      => 0,
            gid         => 2,
            gOrder      => 1,
        },
        {
            var         => 'cerf',
            val         => '0',
            valShow     => '0',
            opt         => '0|1',
            varType     => 'text',
            description => 'Enable/disable CERF search',
            hidden      => 1,
            gid         => 0,
            gOrder      => 0,
        },
        {
            var         => 'cssTheme',
            val         => '0',
            valShow     => '0',
            opt         => 'number',
            varType     => 'text',
            description => 'CSS Overlay Theme',
            hidden      => 1,
            gid         => 0,
            gOrder      => 0,
        },
        {
            var         => 'equipmentMgmnt',
            val         => '0',
            valShow     => 'Disable',
            opt         => '0|1',
            varType     => 'radio',
            description => 'Enable/Disable Equipment management module',
            hidden      => 0,
            gid         => 9,
            gOrder      => 1,
        },
        {
            var         => 'libAddress',
            val         => '',
            valShow     => '',
            opt         => '',
            varType     => 'text',
            description => '',
            hidden      => 1,
            gid         => 0,
            gOrder      => 0,
        },
        {
            var         => 'libCity',
            val         => '',
            valShow     => '',
            opt         => '',
            varType     => 'text',
            description => '',
            hidden      => 1,
            gid         => 0,
            gOrder      => 0,
        },
        {
            var         => 'libEmail',
            val         => '',
            valShow     => '',
            opt         => '',
            varType     => 'text',
            description => '',
            hidden      => 1,
            gid         => 0,
            gOrder      => 0,
        },
        {
            var         => 'libFax',
            val         => '',
            valShow     => '',
            opt         => '',
            varType     => 'text',
            description => '',
            hidden      => 1,
            gid         => 0,
            gOrder      => 0,
        },
        {
            var         => 'libPhone',
            val         => '',
            valShow     => '',
            opt         => '',
            varType     => 'text',
            description => '',
            hidden      => 1,
            gid         => 0,
            gOrder      => 0,
        },
        {
            var         => 'libState',
            val         => '',
            valShow     => '',
            opt         => '',
            varType     => 'text',
            description => '',
            hidden      => 1,
            gid         => 0,
            gOrder      => 0,
        },
        {
            var         => 'libZip',
            val         => '',
            valShow     => '',
            opt         => '',
            varType     => 'text',
            description => '',
            hidden      => 1,
            gid         => 0,
            gOrder      => 0,
        },
        {
            var         => 'OWLBgSrchFields',
            val         => 'keyword,title,author',
            valShow     => 'keyword,title,author',
            opt         => '',
            varType     => 'checkbox',
            description => 'Search fields display in OWL Search Page',
            hidden      => 0,
            gid         => 5,
            gOrder      => 6,
        },
        {
            var         => 'OWLsrchLocation',
            val         => '',
            valShow     => '',
            opt         => '',
            varType     => 'text',
            description => 'OWL Search Location (#852 c)',
            hidden      => 0,
            gid         => 5,
            gOrder      => 5,
        },
        {
            var         => 'patronItemPrivacy',
            val         => '0',
            valShow     => 'Off',
            opt         => '0|1',
            varType     => 'radio',
            description => 'set system allow to show or hide patron information',
            hidden      => 0,
            gid         => 2,
            gOrder      => 5,
        },
        {
            var         => 'rating',
            val         => '0',
            valShow     => 'disable the reader rating item',
            opt         => '0|1|2',
            varType     => 'select',
            description => 'Reader rating feature setting',
            hidden      => 0,
            gid         => 5,
            gOrder      => 23,
        },
        {
            var         => 'recordLock',
            val         => '',
            valShow     => '',
            opt         => '',
            varType     => 'text',
            description => 'Marc record lock/unlock',
            hidden      => 1,
            gid         => 0,
            gOrder      => 0,
        },
        {
            var         => 'showPathfinder',
            val         => '0',
            valShow     => 'Off',
            opt         => '0|1',
            varType     => 'radio',
            description => 'pathfinders setting',
            hidden      => 0,
            gid         => 5,
            gOrder      => 25,
        },
        {
            var         => 'textbookFine',
            val         => '0',
            valShow     => 'Off',
            opt         => 'Integer',
            varType     => 'radio',
            description => '',
            hidden      => 0,
            gid         => 9,
            gOrder      => 3,
        },
        {
            var         => 'textbookChargeLost',
            val         => '0',
            valShow     => 'Off',
            opt         => 'Integer',
            varType     => 'radio',
            description => '',
            hidden      => 0,
            gid         => 9,
            gOrder      => 4,
        },
        {
            var         => 'textbookChargeDamage',
            val         => '0',
            valShow     => 'Off',
            opt         => 'Integer',
            varType     => 'radio',
            description => '',
            hidden      => 0,
            gid         => 9,
            gOrder      => 5,
        },
        {
            var         => 'textbookChargeOverdue',
            val         => '0',
            valShow     => 'Off',
            opt         => 'Integer',
            varType     => 'radio',
            description => '',
            hidden      => 0,
            gid         => 9,
            gOrder      => 6,
        },
        {
            var         => 'textbookMgmnt',
            val         => '0',
            valShow     => 'Off',
            opt         => 'Integer',
            varType     => 'radio',
            description => '',
            hidden      => 0,
            gid         => 9,
            gOrder      => 2,
        },
        {
            var         => 'timezone',
            val         => 'America/Montreal',
            valShow     => '(GMT -5.0) Eastern Time (US &amp; Canada), Bogota, Lima',
            opt         => 'time zone',
            varType     => 'select',
            description => 'Time zone',
            hidden      => 0,
            gid         => 1,
            gOrder      => 9,
        },
        {
            var         => 'union852',
            val         => '',
            valShow     => '',
            opt         => 'integer',
            varType     => 'text',
            description => 'Union display: 0-code, 1-lib, 2-both',
            hidden      => 1,
            gid         => 8,
            gOrder      => 1,
        },
        {
            var         => 'unionCircUpdate',
            val         => '0',
            valShow     => 'Disable',
            opt         => '0|1',
            varType     => 'radio',
            description => '1:enable auto update circulation data to union; 0:disabled',
            hidden      => 0,
            gid         => 8,
            gOrder      => 5,
        },
        {
            var         => 'unionCircUpdateDate',
            val         => '0000-00-00 00:00:00',
            valShow     => '0000-00-00 00:00:00',
            opt         => 'datetime',
            varType     => 'text',
            description => 'date and time in international format (YYYY-mm-dd HH:MM:SS)',
            hidden      => 1,
            gid         => 8,
            gOrder      => 6,
        },
        {
            var         => 'useHolidays',
            val         => '0',
            valShow     => 'include holidays as working days in circulation',
            opt         => '0|1',
            varType     => 'radio',
            description => 'Include/Exclude holidays as working days in circulation  system.',
            hidden      => 0,
            gid         => 3,
            gOrder      => 3,
        }
    );
    foreach my $pref (@new_pref) {
        $pref->{'dbh'} = $dbh;
        insert_preference(%{$pref});# || return;
    }

    return 1;
}
############################################################


sub alter_table_opl_preference {
    my (%param) = @_;

    my $dbh = $param{'dbh'};

    # alter charset and collation
    dbh_do(
        dbh => $dbh,
        sql => <<_SQL_
alter table opl_preference
    CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci
_SQL_
    );

    # alter val
    dbh_do(
        dbh => $dbh,
        sql => <<_SQL_
alter table opl_preference
    change val val text
_SQL_
    );

    # alter description
    dbh_do(
        dbh => $dbh,
        sql => <<_SQL_
alter table opl_preference
    change description description text
_SQL_
    );

    # add column valShow
    if (check_column(
            dbh     => $dbh, 
            table   => 'opl_preference', 
            column  => 'valShow') != 1 &&
        check_column(
            dbh     => $dbh, 
            table   => 'opl_preference', 
            column  => 'val') == 1) {
        dbh_do(
            dbh => $dbh,
            sql => <<_SQL_
alter table opl_preference
    add valShow text after val
_SQL_
        );
    }


    # add column varType
    if (check_column(
            dbh     => $dbh, 
            table   => 'opl_preference', 
            column  => 'varType') != 1 &&
        check_column(
            dbh     => $dbh, 
            table   => 'opl_preference', 
            column  => 'opt') == 1) {
        dbh_do(
            dbh => $dbh,
            sql => <<_SQL_
alter table opl_preference
    add varType set('text','radio','checkbox','select','multipleSelect',
            'timeLength') default 'text' after opt
_SQL_
        );
    }

}
############################################################


sub update_table_opl_preference {
    my (%param) = @_;

    my $dbh = $param{'dbh'};

    dbh_do(
        dbh => $dbh,
        sql => <<_SQL_
update opl_preference set varType='text' where var in (
    'amazonId',
    'amazonSecreteKey',
    'cerf',
    'country',
    'dateFirst',
    'dateLast',
    'dateProfile',
    'defCategory',
    'emailAddress',
    'emailPass',
    'emailSmtp',
    'emailUser',
    'fdrtax',
    'iumap',
    'iuset',
    'libAddress',
    'libCity',
    'libcode',
    'libEmail',
    'libFax',
    'libname',
    'libPhone',
    'libState',
    'libZip',
    'link1_name',
    'link1_url',
    'link2_name',
    'link2_url',
    'lockingDatetime',
    'logo',
    'overwrittenByBarcode',
    'OWLsrchLocation',
    'prvtax',
    'recordLock',
    'reportGroup',
    'searchAdv',
    'slogan',
    'syndeticsId',
    'theme',
    'union852',
    'unionCircUpdateDate',
    'unionId',
    'unionSvcUrl',
    'useAttOrder',
    'warningDatetime',
    'workdayFirst',
    'workdayLast')
_SQL_
    );

    dbh_do(
        dbh => $dbh,
        sql => <<_SQL_
update opl_preference set varType='radio' where var in (
    'autoAdd2NewItemList',
    'autoUpdateUnion',
    'charge_damage',
    'charge_lost',
    'charge_overdue',
    'circulationSound',
    'equipmentMgmnt',
    'fine',
    'multilingual',
    'patronItemPrivacy',
    'showARL',
    'showLocation',
    'showPathfinder',
    'textbookChargeDamage',
    'textbookChargeLost',
    'textbookChargeOverdue',
    'textbookFine',
    'textbookMgmnt',
    'unionCircUpdate',
    'useHolidays',
    'validateBarcode')
_SQL_
    );

    dbh_do(
        dbh => $dbh,
        sql => <<_SQL_
update opl_preference set varType='checkbox' where var in (
    'OWLBgSrchFields')
_SQL_
    );

    dbh_do(
        dbh => $dbh,
        sql => <<_SQL_
update opl_preference set varType='select' where var in (
    'allowOverdue',
    'barcodeType',
    'bibformat',
    'defaultSearchPage',
    'lang',
    'rating',
    'showBeginnerSrch',
    'timezone')
_SQL_
    );

    dbh_do(
        dbh => $dbh,
        sql => <<_SQL_
update opl_preference set varType='timeLength' where var in (
    'bibTimeclear',
    'timeclear',
    'timeout')
_SQL_
    );

    dbh_do(
        dbh => $dbh,
        sql => <<_SQL_
update opl_preference set valShow=val
_SQL_
    );

    dbh_do(
        dbh => $dbh,
        sql => <<_SQL_
update opl_preference pref inner join opl_prefFormParam param using (var) 
    set valShow = param.name
    where pref.varType in ('radio','select')
        && pref.val = param.val
_SQL_
    );
}
############################################################


sub create_tables {
    my (%param) = @_;

    my $dbh = $param{'dbh'};
    my $sql;

    dbh_do(
        dbh => $dbh,
        sql => <<_SQL_
DROP TABLE IF EXISTS opl_studentILL
_SQL_
    );

    dbh_do(
        dbh => $dbh,
        sql => <<_SQL_
CREATE TABLE opl_studentILL (
  id int(10) unsigned NOT NULL auto_increment,
  uid int(10) unsigned NOT NULL default '0',
  zid tinyint(3) unsigned NOT NULL default '0',
  rid int(10) unsigned default NULL,
  title text,
  author text,
  pubDate varchar(20) NOT NULL default '',
  description text,
  dRequest datetime NOT NULL default '0000-00-00 00:00:00',
  dExpiry datetime NOT NULL default '0000-00-00 00:00:00',
  dApproval datetime default NULL,
  dRejection datetime default NULL,
  responderList varchar(255) NOT NULL default '',
  illAdmin int(10) unsigned NOT NULL default '0',
  illID int(10) unsigned NOT NULL default '0',
  timestamp timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  PRIMARY KEY  (id)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
_SQL_
    );

    dbh_do(
        dbh => $dbh,
        sql => <<_SQL_
DROP TABLE IF EXISTS opl_recordIdentifier
_SQL_
    );

    dbh_do(
        dbh => $dbh,
        sql => <<_SQL_
CREATE TABLE opl_recordIdentifier (
  id int(10) unsigned NOT NULL auto_increment,
  rid int(10) unsigned NOT NULL default '1',
  tag char(3) NOT NULL default '',
  code char(1) NOT NULL default '',
  data varchar(255) NOT NULL default '',
  PRIMARY KEY  (id),
  KEY i_tcd (tag,code,data)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
_SQL_
    );

    dbh_do(
        dbh => $dbh,
        sql => <<_SQL_
CREATE TABLE IF NOT EXISTS eq_odl (
  odl_id int(10) unsigned NOT NULL auto_increment,
  idloan int(10) unsigned default '0',
  type set('overdue','lost','damaged') default 'overdue',
  days_overdue tinyint(3) unsigned default '0',
  ondate datetime NOT NULL default '0000-00-00 00:00:00',
  settleDate datetime default NULL,
  PRIMARY KEY  (odl_id),
  KEY NewIndex (odl_id,idloan)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
_SQL_
    );

    dbh_do(
        dbh => $dbh,
        sql => <<_SQL_
CREATE TABLE IF NOT EXISTS opl_bookingFor (
  id int(10) unsigned NOT NULL auto_increment,
  bookingId int(10) NOT NULL default '0',
  uid int(10) unsigned NOT NULL default '0',
  notes varchar(150) default '',
  deleted tinyint(3) unsigned default '0',
  createdDate datetime default NULL,
  modDate timestamp NOT NULL default CURRENT_TIMESTAMP,
  PRIMARY KEY  (id),
  KEY std_id (uid),
  KEY bookingId (bookingId)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
_SQL_
    );

    dbh_do(
        dbh => $dbh,
        sql => <<_SQL_
CREATE TABLE IF NOT EXISTS opl_bookingHold (
  id int(10) unsigned NOT NULL auto_increment,
  bookingId int(10) unsigned NOT NULL,
  barcode varchar(50) default '',
  rid int(10) unsigned NOT NULL,
  itemCategory tinyint(3) unsigned NOT NULL default '1',
  notes varchar(150) default '',
  holdDate datetime default NULL,
  idloan int(10) unsigned NOT NULL default '0',
  modDate timestamp NOT NULL default CURRENT_TIMESTAMP,
  deleted tinyint(3) unsigned default '0',
  PRIMARY KEY  (id),
  KEY bookingId (bookingId),
  KEY barcode (barcode),
  KEY idloan (idloan)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
_SQL_
    );

    dbh_do(
        dbh => $dbh,
        sql => <<_SQL_
CREATE TABLE IF NOT EXISTS opl_bookingItem (
  id int(10) unsigned NOT NULL auto_increment,
  bookingId int(10) NOT NULL default '0',
  idReserve int(10) unsigned default NULL,
  rid int(10) unsigned NOT NULL default '0',
  itemCategory tinyint(3) unsigned default '1',
  numOfCopy int(10) unsigned NOT NULL default '0',
  notes varchar(150) default '',
  deleted tinyint(3) unsigned default '0',
  createdDate datetime default NULL,
  modDate datetime default NULL,
  PRIMARY KEY  (id),
  KEY bookingId (bookingId),
  KEY itemCategory (itemCategory),
  KEY idReserve (idReserve)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
_SQL_
    );

    dbh_do(
        dbh => $dbh,
        sql => <<_SQL_
CREATE TABLE IF NOT EXISTS opl_bookingRequest (
  bookingId int(10) NOT NULL auto_increment,
  responsible int(10) unsigned default NULL,
  achv_id int(10) unsigned default '0',
  reqUid int(10) unsigned default '0',
  reqFirstname varchar(150) default NULL,
  reqLastname varchar(150) default NULL,
  department varchar(150) default NULL,
  course varchar(150) default NULL,
  section varchar(150) default NULL,
  reserveFrom datetime default NULL,
  reserveTo datetime default NULL,
  status enum('waiting','processing','ready','cancel','filled') default 'waiting',
  notes varchar(150) default '',
  deleted tinyint(3) unsigned default '0',
  createdDate datetime default NULL,
  modDate timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  PRIMARY KEY  (bookingId),
  KEY requestorId (reqUid)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
_SQL_
    );

    dbh_do(
        dbh => $dbh,
        sql => <<_SQL_
CREATE TABLE IF NOT EXISTS opl_nystd (
  NY_ID varchar(20) default NULL,
  grade varchar(5) default NULL,
  level varchar(50) default NULL,
  category varchar(150) default NULL,
  subject varchar(150) default NULL,
  NY_Std_No int(3) unsigned default '0',
  NY_Bmrk_No int(3) unsigned default '0',
  NYStandard text,
  NYBmrk text,
  subjectStandard varchar(254) default NULL,
  cerfcode text,
  subjectAlphabet char(1) default NULL,
  FULLTEXT KEY NYstandard (NYStandard,subjectStandard,NYBmrk)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
_SQL_
    );

    dbh_do(
        dbh => $dbh,
        sql => <<_SQL_
CREATE TABLE IF NOT EXISTS opl_payRefundInfo (
  id int(10) unsigned NOT NULL auto_increment,
  tid int(10) unsigned default NULL,
  paid_tid int(10) unsigned default NULL,
  note varchar(60) default NULL,
  date datetime default '0000-00-00 00:00:00',
  PRIMARY KEY  (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
_SQL_
    );

    dbh_do(
        dbh => $dbh,
        sql => <<_SQL_
CREATE TABLE IF NOT EXISTS opl_teacherSchedule (
  id int(10) unsigned NOT NULL auto_increment,
  sectionId varchar(45) default NULL,
  courseId int(8) unsigned default NULL,
  courseCode varchar(45) default NULL,
  teacherId varchar(45) default NULL,
  createdDate datetime default NULL,
  PRIMARY KEY  (id),
  KEY Index_2 (sectionId,courseCode,teacherId,courseId)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
_SQL_
    );

    dbh_do(
        dbh => $dbh,
        sql => <<_SQL_
CREATE TABLE IF NOT EXISTS tb_csvDuplicate (
  mid int(10) NOT NULL auto_increment,
  iid int(10) unsigned NOT NULL default '0',
  content mediumtext NOT NULL,
  PRIMARY KEY  (mid),
  KEY i_iid (iid)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
_SQL_
    );

    dbh_do(
        dbh => $dbh,
        sql => <<_SQL_
CREATE TABLE IF NOT EXISTS tb_csvImport (
  iid int(10) unsigned NOT NULL auto_increment,
  sid int(10) unsigned default '0',
  lid int(10) unsigned default '0',
  uid int(10) unsigned NOT NULL default '0',
  sCode varchar(50) default NULL,
  lCode varchar(50) default NULL,
  itemType varchar(50) default NULL,
  filename varchar(255) default NULL,
  data mediumtext,
  header mediumtext,
  mapHeader mediumtext,
  dataDup mediumtext,
  cMerge tinyint(1) unsigned default '1',
  countImported int(10) unsigned default '0',
  countMerged int(10) unsigned default '0',
  countProcessed int(10) unsigned default '0',
  countTotal int(10) unsigned NOT NULL default '0',
  countTotalHolding int(10) default NULL,
  dateImport datetime default NULL,
  dateUpload datetime NOT NULL default '0000-00-00 00:00:00',
  mesgDigest varchar(128) default NULL,
  status enum('uploaded','rejected','processed','accepted','done') default NULL,
  addToNewitemList tinyint(1) unsigned default '0',
  newItemExpDate datetime default NULL,
  PRIMARY KEY  (iid),
  KEY i_uid (uid),
  KEY i_lid (lid),
  KEY i_msgDigest (mesgDigest)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
_SQL_
    );

    dbh_do(
        dbh => $dbh,
        sql => <<_SQL_
CREATE TABLE IF NOT EXISTS tb_hold (
  id int(10) NOT NULL auto_increment,
  idReserve int(10) unsigned NOT NULL default '0',
  barcode varchar(50) NOT NULL default '',
  dateHold datetime NOT NULL default '0000-00-00 00:00:00',
  dateExpiry datetime NOT NULL default '0000-00-00 00:00:00',
  dateLoan datetime default NULL,
  dateCancel datetime default NULL,
  PRIMARY KEY  (id),
  KEY i_hold (idReserve,barcode,dateLoan)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
_SQL_
    );

    dbh_do(
        dbh => $dbh,
        sql => <<_SQL_
CREATE TABLE IF NOT EXISTS tb_marcDuplicate (
  mid int(10) unsigned NOT NULL auto_increment,
  iid int(10) unsigned NOT NULL default '0',
  content mediumtext,
  PRIMARY KEY  (mid),
  KEY i_iid (iid)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
_SQL_
    );

    dbh_do(
        dbh => $dbh,
        sql => <<_SQL_
CREATE TABLE IF NOT EXISTS tb_marcImport (
  iid int(10) unsigned NOT NULL auto_increment,
  sid int(10) unsigned default '0',
  lid int(10) unsigned default '0',
  uid int(10) unsigned NOT NULL default '0',
  encoding enum('UTF-8','MARC-8') NOT NULL default 'UTF-8',
  sCode varchar(50) default NULL,
  lCode varchar(50) default NULL,
  itemType varchar(50) default NULL,
  cMerge tinyint(1) unsigned default '1',
  countImported int(10) unsigned default '0',
  countMerged int(10) unsigned default '0',
  countProcessed int(10) unsigned default '0',
  countTotal int(10) unsigned NOT NULL default '0',
  dateImport datetime default NULL,
  dateUpload datetime NOT NULL default '0000-00-00 00:00:00',
  mesgDigest varchar(128) default '',
  status enum('uploaded','rejected','accepted','indexing','done') NOT NULL default 'uploaded',
  addToNewitemList tinyint(1) unsigned default '0',
  newItemExpDate datetime default NULL,
  PRIMARY KEY  (iid),
  KEY i_uid (uid),
  KEY i_lid (lid),
  KEY i_mesgDigest (mesgDigest)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
_SQL_
    );

    dbh_do(
        dbh => $dbh,
        sql => <<_SQL_
CREATE TABLE IF NOT EXISTS tb_odl (
  odl_id int(10) unsigned NOT NULL auto_increment,
  idloan int(10) unsigned default '0',
  type set('overdue','lost','damaged') default 'overdue',
  days_overdue tinyint(3) unsigned default '0',
  ondate datetime NOT NULL default '0000-00-00 00:00:00',
  settleDate datetime default NULL,
  PRIMARY KEY  (odl_id),
  KEY NewIndex (odl_id,idloan)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
_SQL_
    );

    dbh_do(
        dbh => $dbh,
        sql => <<_SQL_
CREATE TABLE IF NOT EXISTS tb_reserve (
  id int(10) unsigned NOT NULL auto_increment,
  rid int(10) unsigned NOT NULL default '0',
  uid int(10) unsigned NOT NULL default '0',
  numCopyReserve tinyint(4) default '1',
  dateReserve datetime NOT NULL default '0000-00-00 00:00:00',
  dateExpiry datetime NOT NULL default '0000-00-00 00:00:00',
  dateCancel datetime default NULL,
  PRIMARY KEY  (id),
  KEY i_reserve (rid,uid)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
_SQL_
    );

    dbh_do(
        dbh => $dbh,
        sql => <<_SQL_
CREATE TABLE IF NOT EXISTS tb_transactiondetail (
  tid int(10) NOT NULL,
  odl_id int(10) unsigned NOT NULL default '0',
  responsible varchar(64) default '',
  materials set('book','video','DVD','reserve material') default 'book',
  rate float(10,2) default '0.00',
  overdueDays tinyint(3) unsigned default '0',
  fineAmount float(10,2) default '0.00',
  forgivenAmount float(10,2) default '0.00',
  amount float(10,2) default '0.00',
  taxes float(10,2) default '0.00',
  note varchar(254) default '',
  PRIMARY KEY  (tid),
  KEY NewIndex (tid,odl_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
_SQL_
    );

    dbh_do(
        dbh => $dbh,
        sql => <<_SQL_
CREATE TABLE IF NOT EXISTS tb_transactions (
  tid int(10) unsigned NOT NULL auto_increment,
  ptid int(10) unsigned NOT NULL default '0',
  code smallint(6) default NULL,
  uid int(10) unsigned NOT NULL default '0',
  amount float(10,2) default '0.00',
  balance float(10,2) NOT NULL default '0.00',
  date datetime default '0000-00-00 00:00:00',
  description varchar(50) default NULL,
  settleDate datetime default NULL,
  PRIMARY KEY  (tid),
  UNIQUE KEY tid (tid),
  KEY tid_2 (tid,uid,date,code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
_SQL_
    );
#
#    $sql = <<_SQL_;
#CREATE TABLE IF NOT EXISTS tbk_bookDistribution (
#  id int(10) unsigned NOT NULL auto_increment,
#  pid int(10) unsigned default NULL,
#  teacherId varchar(45) default NULL,
#  studentId varchar(45) default NULL,
#  source varchar(45) default NULL COMMENT 'receive source',
#  courseId int(10) unsigned default NULL,
#  sectionId varchar(45) default NULL,
#  ISBN varchar(45) NOT NULL,
#  barcode varchar(45) default NULL,
#  distDate datetime NOT NULL,
#  returnBy set('teacher','student') default 'teacher',
#  returnDate datetime default NULL,
#  returnStatus enum('ok','lost','damaged','found') NOT NULL default 'ok',
#  schoolYearId int(10) unsigned NOT NULL,
#  PRIMARY KEY  (id)
#) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci ROW_FORMAT=DYNAMIC
#_SQL_
#
#    dbh_do(
#        dbh => $dbh,
#        sql => $sql
#    );
#
#    $sql = <<_SQL_;
#CREATE TABLE IF NOT EXISTS tbk_courseBookAssignment (
#  id int(10) unsigned NOT NULL auto_increment,
#  courseId int(10) unsigned default NULL,
#  courseCode varchar(45) NOT NULL,
#  ISBN varchar(150) NOT NULL,
#  PRIMARY KEY  (id),
#  KEY Index_2 (courseCode,ISBN,courseId,id)
#) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
#_SQL_
#
#    dbh_do(
#        dbh => $dbh,
#        sql => $sql
#    );
#
#    $sql = <<_SQL_;
#CREATE TABLE IF NOT EXISTS tbk_courseList (
#  courseId int(10) unsigned NOT NULL auto_increment,
#  courseCode varchar(45) NOT NULL,
#  courseName varchar(150) default NULL,
#  descriptions text,
#  grade varchar(45) default NULL,
#  schoolYear varchar(45) default NULL,
#  level varchar(45) default NULL,
#  instructionHours varchar(45) default NULL,
#  prerequisite varchar(45) default NULL,
#  PRIMARY KEY  (courseId),
#  KEY Index_2 (courseCode,courseName,grade,level,schoolYear),
#  FULLTEXT KEY Index_3 (courseName)
#) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci ROW_FORMAT=DYNAMIC
#_SQL_
#
#    dbh_do(
#        dbh => $dbh,
#        sql => $sql
#    );
#
#    $sql = <<_SQL_;
#CREATE TABLE IF NOT EXISTS tbk_courseSection (
#  id int(10) unsigned NOT NULL auto_increment,
#  sectionId varchar(45) NOT NULL,
#  courseId int(10) unsigned default NULL,
#  courseCode varchar(45) NOT NULL,
#  hour varchar(45) default NULL,
#  location varchar(45) default NULL,
#  notes text,
#  building varchar(45) default NULL,
#  homeroom varchar(45) default NULL,
#  PRIMARY KEY  (id),
#  KEY Index_2 (sectionId,courseCode,building,homeroom)
#) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci ROW_FORMAT=DYNAMIC
#_SQL_
#
#    dbh_do(
#        dbh => $dbh,
#        sql => $sql
#    );
#
#    $sql = <<_SQL_;
#CREATE TABLE IF NOT EXISTS tbk_items (
#  id int(10) unsigned NOT NULL auto_increment,
#  rid int(11) default NULL,
#  barcode varchar(45) default NULL,
#  ISBN varchar(45) default NULL,
#  status enum('ok','lost','damaged','found') default 'ok',
#  colectedDate datetime default NULL,
#  modifiedDate datetime default NULL,
#  price varchar(45) default NULL,
#  PRIMARY KEY  (id)
#) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci ROW_FORMAT=DYNAMIC
#_SQL_
#
#    dbh_do(
#        dbh => $dbh,
#        sql => $sql
#    );
#
#    $sql = <<_SQL_;
#CREATE TABLE IF NOT EXISTS tbk_marcIn (
#  iid int(10) unsigned NOT NULL auto_increment,
#  uid int(10) unsigned NOT NULL default '0',
#  encoding enum('UTF-8','MARC-8') NOT NULL default 'UTF-8',
#  iImported int(10) unsigned default '0',
#  iProcessed int(10) unsigned default '0',
#  iTotal int(10) unsigned NOT NULL default '0',
#  importDate datetime default NULL,
#  uploadDate datetime NOT NULL default '0000-00-00 00:00:00',
#  mesgDigest varchar(128) default '',
#  status enum('uploaded','rejected','accepted','done') NOT NULL default 'uploaded',
#  PRIMARY KEY  (iid),
#  KEY i_uid (uid),
#  KEY i_mesgDigest (mesgDigest)
#) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
#_SQL_
#
#    dbh_do(
#        dbh => $dbh,
#        sql => $sql
#    );
#
#    $sql = <<_SQL_;
#CREATE TABLE IF NOT EXISTS tbk_marcRecord (
#  rid int(10) NOT NULL auto_increment,
#  title text,
#  title_sort varchar(255) default NULL,
#  callNumMin varchar(50) default NULL,
#  callNumMax varchar(50) default NULL,
#  author text,
#  pubPlace varchar(60) default NULL,
#  pubName varchar(60) default NULL,
#  pubDate varchar(20) default NULL,
#  lockExpire datetime default NULL,
#  editUid int(10) unsigned default NULL,
#  sessionid varchar(40) default NULL,
#  modDate datetime default '0000-00-00 00:00:00',
#  xml text NOT NULL,
#  ISBN varchar(45) default NULL,
#  PRIMARY KEY  (rid),
#  KEY index1 USING BTREE (modDate),
#  KEY Index_3 (ISBN)
#) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
#_SQL_
#
#    dbh_do(
#        dbh => $dbh,
#        sql => $sql
#    );
#
#    $sql = <<_SQL_;
#CREATE TABLE IF NOT EXISTS tbk_preference (
#  var varchar(25) NOT NULL default '',
#  val text,
#  opt varchar(10) default NULL,
#  description text,
#  hidden tinyint(3) unsigned default '0'
#) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
#_SQL_
#
#    dbh_do(
#        dbh => $dbh,
#        sql => $sql
#    );
#
#    $sql = <<_SQL_;
#CREATE TABLE IF NOT EXISTS tbk_preparation (
#  id int(10) unsigned NOT NULL auto_increment,
#  type enum('teacher','student','course','homeroom','request') NOT NULL,
#  expectedReadiness date default NULL,
#  expiryDate date default NULL,
#  preparedDate date NOT NULL,
#  preparedBy varchar(255) NOT NULL,
#  schoolYearId int(10) unsigned NOT NULL,
#  PRIMARY KEY  (id)
#) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci ROW_FORMAT=DYNAMIC
#_SQL_
#
#    dbh_do(
#        dbh => $dbh,
#        sql => $sql
#    );
#
#    $sql = <<_SQL_;
#CREATE TABLE IF NOT EXISTS tbk_preparationDetails (
#  id int(10) unsigned NOT NULL auto_increment,
#  pid int(10) unsigned NOT NULL,
#  ISBN varchar(20) default NULL,
#  courseId int(10) unsigned default NULL,
#  sectionId varchar(45) default NULL,
#  teacherId varchar(45) default NULL,
#  studentId varchar(45) default NULL,
#  homeroom varchar(45) default NULL,
#  nStudents int(10) unsigned default NULL,
#  allocationType enum('perStudent','perClass','perSection') default NULL,
#  nCopiesPerAllocation int(10) unsigned default NULL,
#  total int(10) unsigned default '0',
#  nDistributed int(10) unsigned default '0',
#  recipient enum('teacher','student') NOT NULL,
#  PRIMARY KEY  (id)
#) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci ROW_FORMAT=DYNAMIC
#_SQL_
#
#    dbh_do(
#        dbh => $dbh,
#        sql => $sql
#    );
#
#    $sql = <<_SQL_;
#CREATE TABLE IF NOT EXISTS tbk_recordindex (
#  rid int(10) unsigned NOT NULL,
#  attr_1003 text,
#  attr_1018 text,
#  attr_12 text,
#  attr_13 text,
#  attr_16 text,
#  attr_21 text,
#  attr_31 text,
#  attr_4 text,
#  attr_5 text,
#  attr_5000 text,
#  attr_5001 text,
#  attr_5002 text,
#  attr_59 text,
#  attr_6 text,
#  attr_63 text,
#  attr_7 text,
#  attr_8 text,
#  attr_9 text,
#  attr_s1003 text,
#  attr_s31 text,
#  attr_s4 text,
#  attr_s5001 text,
#  PRIMARY KEY  (rid),
#  FULLTEXT KEY index_a1003 (attr_1003),
#  FULLTEXT KEY index_a1016 (attr_4,attr_5,attr_6,attr_21,attr_31,attr_59,attr_63,attr_1003),
#  FULLTEXT KEY index_a1018 (attr_1018),
#  FULLTEXT KEY index_a12 (attr_12),
#  FULLTEXT KEY index_a13 (attr_13),
#  FULLTEXT KEY index_a16 (attr_16),
#  FULLTEXT KEY index_a21 (attr_21),
#  FULLTEXT KEY index_a31 (attr_31),
#  FULLTEXT KEY index_a4 (attr_4),
#  FULLTEXT KEY index_a5 (attr_5),
#  FULLTEXT KEY index_a5000 (attr_5000),
#  FULLTEXT KEY index_a5001 (attr_5001),
#  FULLTEXT KEY index_a5002 (attr_5002),
#  FULLTEXT KEY index_a59 (attr_59),
#  FULLTEXT KEY index_a6 (attr_6),
#  FULLTEXT KEY index_a63 (attr_63),
#  FULLTEXT KEY index_a7 (attr_7),
#  FULLTEXT KEY index_a8 (attr_8),
#  FULLTEXT KEY index_a9 (attr_9),
#  FULLTEXT KEY index_as1003 (attr_s1003),
#  FULLTEXT KEY index_as31 (attr_s31),
#  FULLTEXT KEY index_as4 (attr_s4),
#  FULLTEXT KEY index_as5001 (attr_s5001)
#) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
#_SQL_
#
#    dbh_do(
#        dbh => $dbh,
#        sql => $sql
#    );
#
#    $sql = <<_SQL_;
#CREATE TABLE IF NOT EXISTS tbk_request (
#  reqId int(10) unsigned NOT NULL auto_increment,
#  teacherId varchar(45) default NULL,
#  courseCode varchar(45) default NULL,
#  sectionId varchar(45) default NULL,
#  quantity int(3) default NULL,
#  requestDate timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
#  status set('completed','waiting','processing','cancel') default NULL,
#  PRIMARY KEY  (reqId)
#) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
#_SQL_
#
#    dbh_do(
#        dbh => $dbh,
#        sql => $sql
#    );
#
#    $sql = <<_SQL_;
#CREATE TABLE IF NOT EXISTS tbk_requestProcess (
#  id int(10) unsigned NOT NULL auto_increment,
#  reqId int(11) NOT NULL,
#  qty int(11) NOT NULL,
#  notes text NOT NULL,
#  processDate timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
#  PRIMARY KEY  (id)
#) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
#_SQL_
#
#    dbh_do(
#        dbh => $dbh,
#        sql => $sql
#    );
#
#    $sql = <<_SQL_;
#CREATE TABLE IF NOT EXISTS tbk_schoolYear (
#  id int(10) unsigned NOT NULL auto_increment,
#  schoolYear varchar(45) NOT NULL,
#  firstDate datetime default NULL,
#  lastDate datetime default NULL,
#  PRIMARY KEY  (id),
#  KEY Index_2 (firstDate,lastDate)
#) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
#_SQL_
#
#    dbh_do(
#        dbh => $dbh,
#        sql => $sql
#    );
#
#    $sql = <<_SQL_;
#CREATE TABLE IF NOT EXISTS tbk_studentSchedule (
#  id int(10) unsigned NOT NULL auto_increment,
#  sectionId varchar(45) NOT NULL,
#  studentId varchar(45) NOT NULL,
#  courseId int(10) unsigned default NULL,
#  courseCode varchar(45) NOT NULL,
#  notes text,
#  created timestamp NOT NULL default CURRENT_TIMESTAMP,
#  importId int(11) default NULL,
#  PRIMARY KEY  (id),
#  KEY Index_2 (sectionId,studentId,courseCode,courseId)
#) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci ROW_FORMAT=DYNAMIC
#_SQL_
#
#    dbh_do(
#        dbh => $dbh,
#        sql => $sql
#    );
#
#    $sql = <<_SQL_;
#CREATE TABLE IF NOT EXISTS tbk_students (
#  id int(10) unsigned NOT NULL auto_increment,
#  studentId varchar(18) default NULL,
#  lastName varchar(45) default NULL,
#  firstName varchar(45) default NULL,
#  addrLine1 varchar(150) default NULL,
#  addrLine2 varchar(150) default NULL,
#  city varchar(45) default NULL,
#  state varchar(25) default NULL,
#  zip varchar(25) default NULL,
#  country varchar(25) default 'USA',
#  grade varchar(25) default NULL,
#  homeroom varchar(150) default NULL,
#  phone varchar(45) default NULL,
#  userBarcode varchar(18) default NULL,
#  created datetime default NULL,
#  importId int(11) default NULL,
#  email varchar(255) default NULL,
#  birthday datetime default NULL,
#  gender tinyint(1) default NULL,
#  status tinyint(1) default NULL,
#  notes text,
#  yearGraduation smallint(5) default '0',
#  building varchar(45) default NULL,
#  teacher varchar(45) default NULL,
#  fax varchar(45) default NULL,
#  PRIMARY KEY  (id),
#  KEY Index_2 (studentId,lastName,firstName,userBarcode,building,teacher,homeroom,grade),
#  FULLTEXT KEY Index_3 (lastName,firstName)
#) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci ROW_FORMAT=DYNAMIC
#_SQL_
#
#    dbh_do(
#        dbh => $dbh,
#        sql => $sql
#    );
#
#    $sql = <<_SQL_;
#CREATE TABLE IF NOT EXISTS tbk_teacherSchedule (
#  id int(10) unsigned NOT NULL auto_increment,
#  sectionId varchar(45) default NULL,
#  courseId int(8) unsigned default NULL,
#  courseCode varchar(45) default NULL,
#  teacherId varchar(45) default NULL,
#  createdDate datetime default NULL,
#  PRIMARY KEY  (id),
#  KEY Index_2 (sectionId,courseCode,teacherId,courseId)
#) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
#_SQL_
#
#    dbh_do(
#        dbh => $dbh,
#        sql => $sql
#    );
#
#    $sql = <<_SQL_;
#CREATE TABLE IF NOT EXISTS tbk_teachers (
#  id int(10) unsigned NOT NULL auto_increment,
#  teacherId varchar(45) NOT NULL,
#  lastName varchar(45) default NULL,
#  firstName varchar(45) default NULL,
#  addrLine1 varchar(150) default NULL,
#  addrLine2 varchar(150) default NULL,
#  city varchar(45) default NULL,
#  state varchar(45) default NULL,
#  zip varchar(25) default NULL,
#  phone varchar(45) default NULL,
#  country varchar(45) default 'USA',
#  email varchar(150) default NULL,
#  birthday date default NULL,
#  importId int(11) default NULL,
#  gender tinyint(1) default NULL,
#  status tinyint(1) default NULL,
#  notes text,
#  created timestamp NOT NULL default CURRENT_TIMESTAMP,
#  PRIMARY KEY  (id),
#  KEY Index_2 USING BTREE (teacherId),
#  FULLTEXT KEY Index_3 (lastName,firstName)
#) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci ROW_FORMAT=DYNAMIC
#_SQL_
#
#    dbh_do(
#        dbh => $dbh,
#        sql => $sql
#    );
}
############################################################


sub create_new_permission {
    my (%param) = @_;

    my $dbh = $param{'dbh'};
    my $sql;

    if (check_row(
            dbh => $dbh,
            table => 'opl_permission',
            column => 'code',
            value => 'circ_ill_filter') == 1) {
        return;
    }

    dbh_do(
        dbh => $dbh,
        sql => <<_SQL_
insert into opl_permission 
    (grpId, name, code) 
    values 
    (4, 'ILL Filter', 'circ_ill_filter')
_SQL_
    );
}
############################################################


sub create_new_menu_item {
    my (%param) = @_;

    my $dbh = $param{'dbh'};
    my $sql;

    if (check_row(
            dbh => $dbh,
            table => 'opl_menuItem',
            column => 'path',
            value => '/bin/ill/list') == 1) {
        return;
    }

    dbh_do(
        dbh => $dbh,
        sql => <<_SQL_
insert into opl_menuItem 
    (PARENT_ID,orderValue,language,title,path,permission,permissions) 
    values 
    (12,3,'en','Student ILL Request List','/bin/ill/list','','')
_SQL_
    );
}
############################################################


sub update_user_permissions {
    my (%param) = @_;

    my $dbh = $param{'dbh'};
    my $sql;

    if (check_column(
            dbh     => $dbh, 
            table   => 'opl_user', 
            column  => 'permissions') == 1) {
        dbh_do(
            dbh => $dbh,
            sql => <<_SQL_
update  opl_user 
set     permissions = concat_ws(',', permissions, 'circ_ill_filter')
where   uid = 1
_SQL_
        );
    }
}
############################################################


sub alter_table_opl_marcImport {
    my (%param) = @_;

    my $dbh = $param{'dbh'};
    my $sql;

    # change column
    if (check_column(
            dbh     => $dbh, 
            table   => 'opl_marcImport', 
            column  => 'mode') != 1) {
        dbh_do(
            dbh => $dbh,
            sql => <<_SQL_
alter table opl_marcImport
    change cMerge mode enum('autoMerge','overlay','manualMerge','new') NOT NULL default 'autoMerge'
_SQL_
        );
    }

    # add column
    if (check_column(
            dbh     => $dbh, 
            table   => 'opl_marcImport', 
            column  => 'mergeMode') != 1) {
        dbh_do(
            dbh => $dbh,
            sql => <<_SQL_
alter table opl_marcImport
    add mergeMode enum('strict','loose') not null default 'strict' after mode
_SQL_
        );
    }

    # add column
    if (check_column(
            dbh     => $dbh, 
            table   => 'opl_marcImport', 
            column  => 'countMergedHolding') != 1) {
        dbh_do(
            dbh => $dbh,
            sql => <<_SQL_
alter table opl_marcImport
    add countMergedHolding int(10) unsigned default '0' after countMerged
_SQL_
        );
    }

    # add column
    if (check_column(
            dbh     => $dbh, 
            table   => 'opl_marcImport', 
            column  => 'holding_op') != 1) {
        dbh_do(
            dbh => $dbh,
            sql => <<_SQL_
alter table opl_marcImport
    add holding_op enum('update','preserve') NOT NULL default 'update'
_SQL_
        );
    }

    # add column
    if (check_column(
            dbh     => $dbh, 
            table   => 'opl_marcImport', 
            column  => 'recordSource') != 1) {
        dbh_do(
            dbh => $dbh,
            sql => <<_SQL_
alter table opl_marcImport
    add recordSource enum('generic','OCoLC') NOT NULL default 'generic'
_SQL_
        );
    }
}
############################################################


sub alter_table_opl_fineRate {
    my (%param) = @_;

    my $dbh = $param{'dbh'};
    my $sql;

    # itemType
    dbh_do(
        dbh => $dbh,
        sql => <<_SQL_
alter table opl_fineRate
    change itemType itemType varchar(100) default '0'
_SQL_
    );

    # overDue
    dbh_do(
        dbh => $dbh,
        sql => <<_SQL_
alter table opl_fineRate
    change overDue overdue float(10,2) default '0.00'
_SQL_
    );

    # maxOverdue
    dbh_do(
        dbh => $dbh,
        sql => <<_SQL_
alter table opl_fineRate
    change maxOverdue maxOverdue float(10,2) default '0.00'
_SQL_
    );

    # damage
    dbh_do(
        dbh => $dbh,
        sql => <<_SQL_
alter table opl_fineRate
    change damage damage float(10,2) default '0.00'
_SQL_
    );

    # add column
    if (check_column(
            dbh     => $dbh, 
            table   => 'opl_fineRate', 
            column  => 'overduePerHour') != 1 &&
        check_column(
            dbh     => $dbh, 
            table   => 'opl_fineRate', 
            column  => 'overdue') == 1) {
        dbh_do(
            dbh => $dbh,
            sql => <<_SQL_
alter table opl_fineRate
    add overduePerHour float(10,2) default '0.00' after overdue
_SQL_
        );
    }

    # add column
    if (check_column(
            dbh     => $dbh, 
            table   => 'opl_fineRate', 
            column  => 'itemCategory') != 1) {
        dbh_do(
            dbh => $dbh,
            sql => <<_SQL_
alter table opl_fineRate
    add itemCategory enum('1','2','3') NOT NULL default '1'
_SQL_
        );
    }
}
############################################################


sub alter_table_opl_hold {
    my (%param) = @_;

    my $dbh = $param{'dbh'};
    my $sql;

    # add column
    if (check_column(
            dbh     => $dbh, 
            table   => 'opl_hold', 
            column  => 'idloan') != 1 &&
        check_column(
            dbh     => $dbh, 
            table   => 'opl_hold', 
            column  => 'idReserve') == 1) {
        dbh_do(
            dbh => $dbh,
            sql => <<_SQL_
alter table opl_hold 
    add idloan int(10) unsigned default '0' after idReserve
_SQL_
        );
    }
}
############################################################


sub drop_index {
    my (%param) = @_;

    my $dbh = $param{'dbh'};
    my $sql;
    my $table = $param{'table'};
    my $name = $param{'name'}; # use PRIMARY to drop primary key

    my $sth = $dbh->prepare("show index from $table");
    $sth->execute();
    while (my $i = $sth->fetchrow_hashref) {
        if ($i->{'Key_name'} eq $name) {
            if ($name eq 'PRIMARY') {
                $sql = "alter table $table drop primary key";
            }
            else {
                $sql = "drop index `$name` on $table";
            }

            dbh_do(
                dbh => $dbh,
                sql => $sql
            );

            last;
        }
    }
    $sth->finish;
}
############################################################


sub create_index {
    my (%param) = @_;

    my $dbh = $param{'dbh'};
    my $sql;
    my $table = $param{'table'};
    my $type = $param{'type'};
    my $name = $param{'name'}; # use PRIMARY to create primary key
    my $columnList = $param{'columnList'};

    ($type) || ($type = '');

    my $colExisted = 1;
    foreach my $col (split(/,/, $columnList )) {
        if (check_column(
                dbh     => $dbh, 
                table   => $table, 
                column  => $col) != 1) {
            print "create_index: $table.$col not existed\n";
            $colExisted = 0;
        }
    }

    if ($colExisted == 0) {
        print "create_index: missing column(s) in table $table\n";
        return;
    }

    drop_index(
        dbh     => $dbh,
        table   => $table,
        name    => $name);

    if ($name eq 'PRIMARY') {
        $sql = "alter table $table add primary key ($columnList)";
    }
    else {
        $sql = "create $type index $name on $table ($columnList)";
    }

    dbh_do(
        dbh => $dbh,
        sql => $sql
    );    
}
############################################################


sub update_index_opl_item {
    my (%param) = @_;

    my $dbh = $param{'dbh'};

    drop_index(
        dbh     => $dbh,
        table   => 'opl_item',
        name    => 'location_ind');
    drop_index(
        dbh     => $dbh,
        table   => 'opl_item',
        name    => 'ind_location');
    drop_index(
        dbh     => $dbh,
        table   => 'opl_item',
        name    => 'callNumber');

    create_index(
        dbh         => $dbh,
        table       => 'opl_item',
        name        => 'i_location',
        columnList  => 'location');
    create_index(
        dbh         => $dbh,
        table       => 'opl_item',
        name        => 'i_callNumber',
        columnList  => 'callNumber');
}
############################################################


sub alter_table_opl_itemType {
    my (%param) = @_;

    my $dbh = $param{'dbh'};

    create_index(
        dbh         => $dbh,
        table       => 'opl_itemType',
        name        => 'PRIMARY',
        columnList  => 'id,itemCategory');
}
############################################################


sub update_index_opl_marcRecord {
    my (%param) = @_;

    my $dbh = $param{'dbh'};

    drop_index(
        dbh     => $dbh,
        table   => 'opl_marcRecord',
        name    => 'auth_ind');

    create_index(
        dbh         => $dbh,
        table       => 'opl_marcRecord',
        type        => 'fulltext',
        name        => 'ind_auth',
        columnList  => 'author');
}
############################################################


sub alter_table_opl_menuItem {
    my (%param) = @_;

    my $dbh = $param{'dbh'};
    my $sql;

    if (check_column(
            dbh     => $dbh, 
            table   => 'opl_menuItem', 
            column  => 'permissions') == 1) {
        dbh_do(
            dbh => $dbh,
            sql => <<_SQL_
alter table opl_menuItem 
    change permissions permissions varchar(255) default ''
_SQL_
        );    
    }
}
############################################################


sub alter_table_opl_odl {
    my (%param) = @_;

    my $dbh = $param{'dbh'};
    my $sql;

    if (check_column(
            dbh     => $dbh, 
            table   => 'opl_odl', 
            column  => 'hours_overdue') != 1 &&
        check_column(
            dbh     => $dbh, 
            table   => 'opl_odl', 
            column  => 'days_overdue') == 1) {
        dbh_do(
            dbh => $dbh,
            sql => <<_SQL_
alter table opl_odl 
    add hours_overdue tinyint(3) unsigned default '0' after days_overdue
_SQL_
        );
    }
}
############################################################


sub alter_table_opl_openHours {
    my (%param) = @_;

    my $dbh = $param{'dbh'};
    my $sql;

    drop_index(
        dbh         => $dbh,
        table       => 'opl_openHours',
        name        => 'PRIMARY'
    );

    create_index(
        dbh     => $dbh,
        table   => 'opl_openHours',
        name    => 'PRIMARY',
        columnList  => 'id'
    );
    
    dbh_do(
        dbh => $dbh,
        sql => <<_SQL_
alter table opl_openHours
    change id id tinyint(3) unsigned not null auto_increment
_SQL_
    );

    # add column open
    if (check_column(
            dbh     => $dbh, 
            table   => 'opl_openHours', 
            column  => 'open') != 1) {
        dbh_do(
            dbh => $dbh,
            sql => <<_SQL_
alter table opl_openHours
    add open varchar(32) default NULL after weekday
_SQL_
        );
    }

    my ($count) = $dbh->selectrow_array(<<_SQL_);
select  count(*)
from    opl_openHours
where   open is null
_SQL_

    if ($count &&
        check_column(
            dbh     => $dbh, 
            table   => 'opl_openHours', 
            column  => 'open1') == 1) {
        ($count) = $dbh->selectrow_array(<<_SQL_);
select  count(*)
from    opl_openHours
where   open1 regexp ' - '
_SQL_

        if ($count == 0) {
            dbh_do(
                dbh => $dbh,
                sql => <<_SQL_
update  opl_openHours
set     open = open1
_SQL_
            );
        }
        else {
            # set open1 to open and close
            dbh_do(
                dbh => $dbh,
                sql => <<_SQL_
update  opl_openHours
set     open = open1,
        close= open1
_SQL_
            );
            # set open2 to open and close
            dbh_do(
                dbh => $dbh,
                sql => <<_SQL_
insert
into    opl_openHours 
(weekday,open,close) 
select  weekday, open2, open2
from    opl_openHours 
where   open2 <> '' 
     && open2 is not null
_SQL_
            );
            # trim old data
            dbh_do(
                dbh => $dbh,
                sql => <<_SQL_
update  opl_openHours 
set     open = trim(replace(substr(open, 1, instr(open, '-')), '-', '')),
        close= substr(close, instr(close, '-') + 2)
_SQL_
            );
#set     open = substr(open, 1, instr(replace(open, ' ', ''), '-')),
#        close= substr(close, instr(replace(close, ' ', ''), '-') + 2)

            # convert hour to 24-hour format
            dbh_do(
                dbh => $dbh,
                sql => <<_SQL_
update  opl_openHours 
set     close = concat_ws(':', close + 12, substr(close, instr(close, ':'))) 
where   close + 0 < open + 0
     && close + 0 < 12
_SQL_
            );
        }

        # drop open1 and open2
        dbh_do(
            dbh => $dbh,
            sql => <<_SQL_
alter
table   opl_openHours
drop    open1,
drop    open2
_SQL_
        );
    }
}
############################################################


sub alter_table_opl_transactiondetail {
    my (%param) = @_;

    my $dbh = $param{'dbh'};
    my $sql;

    if (check_column(
            dbh     => $dbh, 
            table   => 'opl_transactiondetail', 
            column  => 'hourlyRate') != 1 &&
        check_column(
            dbh     => $dbh, 
            table   => 'opl_transactiondetail', 
            column  => 'rate') == 1) {
        dbh_do(
            dbh => $dbh,
            sql => <<_SQL_
alter table opl_transactiondetail
    add hourlyRate float(10,2) default '0.00' after rate
_SQL_
        );
    }

    if (check_column(
            dbh     => $dbh, 
            table   => 'opl_transactiondetail', 
            column  => 'overdueHours') != 1 &&
        check_column(
            dbh     => $dbh, 
            table   => 'opl_transactiondetail', 
            column  => 'overdueDays') == 1) {
        dbh_do(
            dbh => $dbh,
            sql => <<_SQL_
alter table opl_transactiondetail
    add overdueHours tinyint(3) unsigned default '0' after overdueDays
_SQL_
        );
    }
}
############################################################


sub alter_table_opl_transactions {
    my (%param) = @_;

    my $dbh = $param{'dbh'};
    my $sql;

    if (check_column(
            dbh     => $dbh, 
            table   => 'opl_transactions', 
            column  => 'amount') == 1) {
        dbh_do(
            dbh => $dbh,
            sql => <<_SQL_
alter table opl_transactions
    change amount amount float(10,2) NOT NULL default '0.00'
_SQL_
        );
    }

    if (check_column(
            dbh     => $dbh, 
            table   => 'opl_transactions', 
            column  => 'date') == 1) {
        dbh_do(
            dbh => $dbh,
            sql => <<_SQL_
alter table opl_transactions
    change date date datetime NOT NULL default '0000-00-00 00:00:00'
_SQL_
        );
    }
}
############################################################


sub alter_table_user_guardian {
    my (%param) = @_;

    my $dbh = $param{'dbh'};
    my $sql;
    my $action;

    if (check_column(
            dbh     => $dbh, 
            table   => 'opl_user', 
            column  => 'cellphone') == 1) {
        $action = 'change cellphone';
    }
    else {
        $action = 'add';
    }

    dbh_do(
        dbh => $dbh,
        sql => <<_SQL_
alter table opl_user
    $action cellphone varchar(50) default NULL after phone
_SQL_
    );

    if (check_column(
            dbh     => $dbh, 
            table   => 'opl_guardian', 
            column  => 'gcellphone') == 1) {
        $action = 'change gcellphone';
    }
    else {
        $action = 'add';
    }

    dbh_do(
        dbh => $dbh,
        sql => <<_SQL_
alter table opl_guardian
    $action gcellphone varchar(50) default NULL after gphone
_SQL_
    );
}
############################################################


sub update_index_opl_transactions {
    my (%param) = @_;

    my $dbh = $param{'dbh'};

    create_index(
        dbh         => $dbh,
        table       => 'opl_transactions',
        name        => 'tid_2',
        columnList  => 'tid,uid,date,code');
}
############################################################


sub alter_table_pf_bookRs {
    my (%param) = @_;

    my $dbh = $param{'dbh'};
    my $sql;

    if (check_column(
            dbh     => $dbh, 
            table   => 'pf_bookRs', 
            column  => 'rid') != 1 &&
        check_column(
            dbh     => $dbh, 
            table   => 'pf_bookRs', 
            column  => 'id') == 1) {
        dbh_do(
            dbh => $dbh,
            sql => <<_SQL_
alter table pf_bookRs 
    add rid int(10) unsigned NOT NULL default '0' after id
_SQL_
        );
    }
}
############################################################


sub alter_table_pf_webRs {
    my (%param) = @_;

    my $dbh = $param{'dbh'};
    my $sql;

    if (check_column(
            dbh     => $dbh, 
            table   => 'pf_webRs', 
            column  => 'description') == 1) {
        dbh_do(
            dbh => $dbh,
            sql => <<_SQL_
alter table pf_webRs 
    change description description text
_SQL_
        );
    }
}
############################################################


sub update_index_tb_courseList {
    my (%param) = @_;

    my $dbh = $param{'dbh'};

    create_index(
        dbh         => $dbh,
        table       => 'tb_courseList',
        name        => 'Index_2',
        columnList  => 'courseCode,courseName,grade,level,schoolYear');
}
############################################################


sub update_index_tb_index_map {
    my (%param) = @_;

    my $dbh = $param{'dbh'};

    create_index(
        dbh         => $dbh,
        table       => 'tb_index_map',
        name        => 'i_fId',
        columnList  => 'fId');
}
############################################################


sub alter_table_tb_items {
    my (%param) = @_;

    my $dbh = $param{'dbh'};
    my $sql;

    if (check_column(
            dbh     => $dbh, 
            table   => 'tb_items', 
            column  => 'acquisitionDate') == 1) {
        dbh_do(
            dbh => $dbh,
            sql => <<_SQL_
alter table tb_items 
    change acquisitionDate acquisitionDate date default NULL
_SQL_
        );
    }

    if (check_column(
            dbh     => $dbh, 
            table   => 'tb_items', 
            column  => 'budgetCategory') != 1 &&
        check_column(
            dbh     => $dbh, 
            table   => 'tb_items', 
            column  => 'buildingCode') == 1) {
        dbh_do(
            dbh => $dbh,
            sql => <<_SQL_
alter table tb_items 
    add budgetCategory varchar(255) default NULL after buildingCode
_SQL_
        );
    }

    if (check_column(
            dbh     => $dbh, 
            table   => 'tb_items', 
            column  => 'vendorCatalogNumber') != 1 &&
        check_column(
            dbh     => $dbh, 
            table   => 'tb_items', 
            column  => 'deleted') == 1) {
        dbh_do(
            dbh => $dbh,
            sql => <<_SQL_
alter table tb_items 
    add vendorCatalogNumber varchar(255) default NULL after deleted
_SQL_
        );
    }

    if (check_column(
            dbh     => $dbh, 
            table   => 'tb_items', 
            column  => 'updating') == 1) {
        dbh_do(
            dbh => $dbh,
            sql => <<_SQL_
alter table tb_items 
    change updating updating enum('1','0') NOT NULL default '0' after modDate
_SQL_
        );
    }
}
############################################################


sub update_index_tb_items {
    my (%param) = @_;

    my $dbh = $param{'dbh'};

    create_index(
        dbh         => $dbh,
        table       => 'tb_items',
        name        => 'r_index1',
        columnList  => 'rid,modDate,importDate,available');
}
############################################################


sub alter_table_tb_records {
    my (%param) = @_;

    my $dbh = $param{'dbh'};
    my $sql;
    my $action;

    if (check_column(
            dbh     => $dbh, 
            table   => 'tb_records', 
            column  => 'updating') == 1) {
        $action = 'change updating';
    }
    else {
        $action = 'add';
    }

    dbh_do(
        dbh => $dbh,
        sql => <<_SQL_
alter table tb_records 
    $action updating enum('0','1') NOT NULL default '0' after fVal
_SQL_
    );
}
############################################################


sub update_index_eq_items {
    my (%param) = @_;

    my $dbh = $param{'dbh'};

    drop_index(
        dbh     => $dbh,
        table   => 'eq_items',
        name    => 'u_rid-iid');
    drop_index(
        dbh     => $dbh,
        table   => 'eq_items',
        name    => 'u_barcode');
    drop_index(
        dbh     => $dbh,
        table   => 'eq_items',
        name    => 'f_barcode');

    create_index(
        dbh         => $dbh,
        table       => 'eq_items',
        type        => 'unique',
        name        => 'u_rid_iid',
        columnList  => 'iid,rid');
    create_index(
        dbh         => $dbh,
        table       => 'eq_items',
        type        => 'unique',
        name        => 'u_barcode',
        columnList  => 'barcode');
    create_index(
        dbh         => $dbh,
        table       => 'eq_items',
        type        => 'fulltext',
        name        => 'f_barcode',
        columnList  => 'barcode');
}