#!/usr/bin/perl

# $Id$

# Database Updater
# This script checks for required updates to the database.

# Part of the Koha Library Software www.koha.org
# Licensed under the GPL.

# Bugs/ToDo:
# - Would also be a good idea to offer to do a backup at this time...

# NOTE:  If you do something more than once in here, make it table driven.
use strict;

# CPAN modules
use DBI;
use Getopt::Long;
# Koha modules
use C4::Context;

use MARC::Record;
use MARC::File::XML ( BinaryEncoding => 'utf8' );
 
# FIXME - The user might be installing a new database, so can't rely
# on /etc/koha.conf anyway.

my $debug = 0;

my (
    $sth, $sti,
    $query,
    %existingtables,    # tables already in database
    %types,
    $table,
    $column,
    $type, $null, $key, $default, $extra,
    $prefitem,          # preference item in systempreferences table
);

my $silent;
GetOptions(
    's' =>\$silent
    );
my $dbh = C4::Context->dbh;
$|=1; # flushes output

=item
    Deal with virtualshelves
=cut

my $DBversion = "3.00.00.001";
if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
    # update virtualshelves table to
    # 
    $dbh->do("ALTER TABLE `bookshelf` RENAME `virtualshelves`");
    $dbh->do("ALTER TABLE `shelfcontents` RENAME `virtualshelfcontents`");
    $dbh->do("ALTER TABLE `virtualshelfcontents` ADD `biblionumber` INT( 11 ) NOT NULL");
    $dbh->do("UPDATE `virtualshelfcontents` SET biblionumber=(SELECT biblionumber FROM items WHERE items.itemnumber=virtualshelfcontents.itemnumber)");
    # drop all foreign keys : otherwise, we can't drop itemnumber field.
    DropAllForeignKeys('virtualshelfcontents');
    # create the new foreign keys (on biblionumber)
    $dbh->do("ALTER TABLE `virtualshelfcontents` ADD FOREIGN KEY biblionumber_fk (biblionumber) REFERENCES biblio (biblionumber) ON UPDATE CASCADE ON DELETE CASCADE");
    # re-create the foreign key on virtualshelf
    $dbh->do("ALTER TABLE `virtualshelfcontents` ADD FOREIGN KEY shelfnumber_fk (shelfnumber) REFERENCES virtualshelves (shelfnumber) ON UPDATE CASCADE ON DELETE CASCADE");
    # now we can drop the itemnumber column
    $dbh->do("ALTER TABLE `virtualshelfcontents` DROP `itemnumber`");
    print "Upgrade to $DBversion done (virtualshelves)\n";
    SetVersion ($DBversion);
}


$DBversion = "3.00.00.002";
if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
    $dbh->do("DROP TABLE sessions");
    $dbh->do("CREATE TABLE `sessions` (
  `id` char(32) NOT NULL,
  `a_session` text NOT NULL,
  UNIQUE KEY `id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;");
    print "Upgrade to $DBversion done (sessions uses CGI::session, new table structure for sessions)\n";
    SetVersion ($DBversion);
}


$DBversion = "3.00.00.003";
if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
    if (C4::Context->preference("opaclanguage") eq "fr") {
        $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('ReservesNeedReturns','0','Si ce paramètre est mis à 1, une réservation posée sur un exemplaire présent sur le site devra être passée en retour pour être disponible. Sinon, elle sera automatiquement disponible, Koha considère que le bibliothécaire place la réservation en ayant le document en mains','','YesNo')");
    } else {
        $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('ReservesNeedReturns','0','If set, a reserve done on an item available in this branch need a check-in, otherwise, a reserve on a specific item, that is on the branch & available is considered as available','','YesNo')");
    }
    print "Upgrade to $DBversion done (adding ReservesNeedReturns systempref, in circulation)\n";
    SetVersion ($DBversion);
}


$DBversion = "3.00.00.004";
if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
	$dbh->do("INSERT INTO `systempreferences` VALUES ('DebugLevel','2','set the level of error info sent to the browser. 0=none, 1=some, 2=most','0|1|2','Choice')");    
    print "Upgrade to $DBversion done (adding DebugLevel systempref, in 'Admin' tab)\n";
    SetVersion ($DBversion);
}

$DBversion = "3.00.00.005";
if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
	$dbh->do("CREATE TABLE `tags` (
                    `entry` varchar(255) NOT NULL default '',
                    `weight` bigint(20) NOT NULL default 0,
                    PRIMARY KEY  (`entry`)
                    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
                ");
        $dbh->do("CREATE TABLE `nozebra` (
                `server` varchar(20)     NOT NULL,
                `indexname` varchar(40)  NOT NULL,
                `value` varchar(250)     NOT NULL,
                `biblionumbers` longtext NOT NULL,
                KEY `indexname` (`server`,`indexname`),
                KEY `value` (`server`,`value`))
                ENGINE=InnoDB DEFAULT CHARSET=utf8;
                ");
    print "Upgrade to $DBversion done (adding tags and nozebra tables )\n";
    SetVersion ($DBversion);
}

$DBversion = "3.00.00.006";
if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
	$dbh->do("UPDATE issues SET issuedate=timestamp WHERE issuedate='0000-00-00'");
    print "Upgrade to $DBversion done (filled issues.issuedate with timestamp)\n";
    SetVersion ($DBversion);
}

$DBversion = "3.00.00.007";
if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
	$dbh->do("INSERT INTO systempreferences (variable,value,explanation,options,type) VALUES ('SessionStorage','mysql','Use mysql or a temporary file for storing session data','mysql|tmp','Choice')");
    print "Upgrade to $DBversion done (set SessionStorage variable)\n";
    SetVersion ($DBversion);
}

$DBversion = "3.00.00.008";
if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
	$dbh->do("ALTER TABLE `biblio` ADD `datecreated` DATE NOT NULL AFTER `timestamp` ;");
	$dbh->do("UPDATE biblio SET datecreated=timestamp");
    print "Upgrade to $DBversion done (biblio creation date)\n";
    SetVersion ($DBversion);
}

$DBversion = "3.00.00.009";
if (C4::Context->preference("Version") < TransformToNum($DBversion)) {

    # Create backups of call number columns
    # in case default migration needs to be customized
    #
    # UPGRADE NOTE: temp_upg_biblioitems_call_num should be dropped 
    #               after call numbers have been transformed to the new structure
    #
    # Not bothering to do the same with deletedbiblioitems -- assume
    # default is good enough.
    $dbh->do("CREATE TABLE `temp_upg_biblioitems_call_num` AS 
              SELECT `biblioitemnumber`, `biblionumber`,
                     `classification`, `dewey`, `subclass`,
                     `lcsort`, `ccode`
              FROM `biblioitems`");

    # biblioitems changes
    $dbh->do("ALTER TABLE `biblioitems` CHANGE COLUMN `volumeddesc` `volumedesc` TEXT");
    $dbh->do("ALTER TABLE `biblioitems` ADD `cn_source` VARCHAR(10) DEFAULT NULL AFTER `ccode`");
    $dbh->do("ALTER TABLE `biblioitems` ADD `cn_class` VARCHAR(30) DEFAULT NULL AFTER `cn_source`");
    $dbh->do("ALTER TABLE `biblioitems` ADD `cn_item` VARCHAR(10) DEFAULT NULL AFTER `cn_class`");
    $dbh->do("ALTER TABLE `biblioitems` ADD `cn_suffix` VARCHAR(10) DEFAULT NULL AFTER `cn_item`");
    $dbh->do("ALTER TABLE `biblioitems` ADD `cn_sort` VARCHAR(30) DEFAULT NULL AFTER `cn_suffix`");
    $dbh->do("ALTER TABLE `biblioitems` ADD `totalissues` INT(10) AFTER `cn_sort`");

    # default mapping of call number columns:
    #   cn_class = concatentation of classification + dewey, 
    #              trimmed to fit -- assumes that most users do not
    #              populate both classification and dewey in a single record
    #   cn_item  = subclass
    #   cn_source = left null 
    #   cn_sort = lcsort 
    #
    # After upgrade, cn_sort will have to be set based on whatever
    # default call number scheme user sets as a preference.  Misc
    # script will be added at some point to do that.
    #
    $dbh->do("UPDATE `biblioitems` 
              SET cn_class = SUBSTR(TRIM(CONCAT_WS(' ', `classification`, `dewey`)), 1, 30)");
    $dbh->do("UPDATE `biblioitems` SET cn_item = subclass");
    $dbh->do("UPDATE `biblioitems` SET `cn_sort` = `lcsort`");

    # Now drop the old call number columns
    $dbh->do("ALTER TABLE `biblioitems` DROP COLUMN `classification`");
    $dbh->do("ALTER TABLE `biblioitems` DROP COLUMN `dewey`");
    $dbh->do("ALTER TABLE `biblioitems` DROP COLUMN `subclass`");
    $dbh->do("ALTER TABLE `biblioitems` DROP COLUMN `lcsort`");
    $dbh->do("ALTER TABLE `biblioitems` DROP COLUMN `ccode`");

    # deletedbiblio changes
    $dbh->do("ALTER TABLE `deletedbiblio` ALTER COLUMN `frameworkcode` SET DEFAULT ''");
    $dbh->do("ALTER TABLE `deletedbiblio` DROP COLUMN `marc`");
    $dbh->do("ALTER TABLE `deletedbiblio` ADD `datecreated` DATE NOT NULL AFTER `timestamp`");
    $dbh->do("UPDATE deletedbiblio SET datecreated = timestamp");

    # deletedbiblioitems changes
    $dbh->do("ALTER TABLE `deletedbiblioitems` MODIFY `publicationyear` TEXT");
    $dbh->do("ALTER TABLE `deletedbiblioitems` CHANGE `volumeddesc` `volumedesc` TEXT");
    $dbh->do("ALTER TABLE `deletedbiblioitems` MODIFY `collectiontitle` MEDIUMTEXT DEFAULT NULL AFTER `volumedesc`");
    $dbh->do("ALTER TABLE `deletedbiblioitems` MODIFY `collectionissn` TEXT DEFAULT NULL AFTER `collectiontitle`");
    $dbh->do("ALTER TABLE `deletedbiblioitems` MODIFY `collectionvolume` MEDIUMTEXT DEFAULT NULL AFTER `collectionissn`");
    $dbh->do("ALTER TABLE `deletedbiblioitems` MODIFY `editionstatement` TEXT DEFAULT NULL AFTER `collectionvolume`");
    $dbh->do("ALTER TABLE `deletedbiblioitems` MODIFY `editionresponsibility` TEXT DEFAULT NULL AFTER `editionstatement`");
    $dbh->do("ALTER TABLE `deletedbiblioitems` MODIFY `place` VARCHAR(255) DEFAULT NULL AFTER `size`");
    $dbh->do("ALTER TABLE `deletedbiblioitems` MODIFY `marc` BLOB");
    $dbh->do("ALTER TABLE `deletedbiblioitems` ADD `cn_source` VARCHAR(10) DEFAULT NULL AFTER `url`");
    $dbh->do("ALTER TABLE `deletedbiblioitems` ADD `cn_class` VARCHAR(30) DEFAULT NULL AFTER `cn_source`");
    $dbh->do("ALTER TABLE `deletedbiblioitems` ADD `cn_item` VARCHAR(10) DEFAULT NULL AFTER `cn_class`");
    $dbh->do("ALTER TABLE `deletedbiblioitems` ADD `cn_suffix` VARCHAR(10) DEFAULT NULL AFTER `cn_item`");
    $dbh->do("ALTER TABLE `deletedbiblioitems` ADD `cn_sort` VARCHAR(30) DEFAULT NULL AFTER `cn_suffix`");
    $dbh->do("ALTER TABLE `deletedbiblioitems` ADD `totalissues` INT(10) AFTER `cn_sort`");
    $dbh->do("ALTER TABLE `deletedbiblioitems` ADD KEY `isbn` (`isbn`)");
    $dbh->do("ALTER TABLE `deletedbiblioitems` ADD KEY `publishercode` (`publishercode`)");

    $dbh->do("UPDATE `deletedbiblioitems` 
              SET cn_class = SUBSTR(TRIM(CONCAT_WS(' ', `classification`, `dewey`)), 1, 30)");
    $dbh->do("UPDATE `deletedbiblioitems` SET cn_item = subclass");
    $dbh->do("UPDATE `deletedbiblioitems` SET `cn_sort` = `lcsort`");
    $dbh->do("ALTER TABLE `deletedbiblioitems` DROP COLUMN `classification`");
    $dbh->do("ALTER TABLE `deletedbiblioitems` DROP COLUMN `dewey`");
    $dbh->do("ALTER TABLE `deletedbiblioitems` DROP COLUMN `subclass`");
    $dbh->do("ALTER TABLE `deletedbiblioitems` DROP COLUMN `lcsort`");
    $dbh->do("ALTER TABLE `deletedbiblioitems` DROP COLUMN `ccode`");

    # deleteditems changes
    $dbh->do("ALTER TABLE `deleteditems` MODIFY `barcode` VARCHAR(20) DEFAULT NULL");
    $dbh->do("ALTER TABLE `deleteditems` MODIFY `price` DECIMAL(8,2) DEFAULT NULL");
    $dbh->do("ALTER TABLE `deleteditems` MODIFY `replacementprice` DECIMAL(8,2) DEFAULT NULL");
    $dbh->do("ALTER TABLE `deleteditems` DROP `bulk`");
    $dbh->do("ALTER TABLE `deleteditems` MODIFY `itemcallnumber` VARCHAR(30) DEFAULT NULL AFTER `wthdrawn`");
    $dbh->do("ALTER TABLE `deleteditems` MODIFY `holdingbranch` VARCHAR(10) DEFAULT NULL");
    $dbh->do("ALTER TABLE `deleteditems` DROP `interim`");
    $dbh->do("ALTER TABLE `deleteditems` MODIFY `timestamp` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP AFTER `paidfor`");
    $dbh->do("ALTER TABLE `deleteditems` DROP `cutterextra`");
    $dbh->do("ALTER TABLE `deleteditems` ADD `cn_source` VARCHAR(10) DEFAULT NULL AFTER `onloan`");
    $dbh->do("ALTER TABLE `deleteditems` ADD `cn_sort` VARCHAR(30) DEFAULT NULL AFTER `cn_source");
    $dbh->do("ALTER TABLE `deleteditems` ADD `ccode` VARCHAR(10) DEFAULT NULL AFTER `cn_sort`");
    $dbh->do("ALTER TABLE `deleteditems` ADD `materials` VARCHAR(10) DEFAULT NULL AFTER `ccode`");
    $dbh->do("ALTER TABLE `deleteditems` ADD `uri` VARCHAR(255) DEFAULT NULL AFTER `materials`");
    $dbh->do("ALTER TABLE `deleteditems` MODIFY `marc` LONGBLOB AFTER `uri`");
    $dbh->do("ALTER TABLE `deleteditems` DROP KEY `barcode`");
    $dbh->do("ALTER TABLE `deleteditems` DROP KEY `itembarcodeidx`");
    $dbh->do("ALTER TABLE `deleteditems` DROP KEY `itembinoidx`");
    $dbh->do("ALTER TABLE `deleteditems` DROP KEY `itembibnoidx`");
    $dbh->do("ALTER TABLE `deleteditems` ADD UNIQUE KEY `delitembarcodeidx` (`barcode`)");
    $dbh->do("ALTER TABLE `deleteditems` ADD KEY `delitembinoidx` (`biblioitemnumber`)");
    $dbh->do("ALTER TABLE `deleteditems` ADD KEY `delitembibnoidx` (`biblionumber`)");
    $dbh->do("ALTER TABLE `deleteditems` ADD KEY `delhomebranch` (`homebranch`)");
    $dbh->do("ALTER TABLE `deleteditems` ADD KEY `delholdingbranch` (`holdingbranch`)");
    $dbh->do("UPDATE `deleteditems` SET `ccode` = `itype`");
    $dbh->do("ALTER TABLE `deleteditems` DROP `itype`");
    $dbh->do("UPDATE `deleteditems` SET `cn_sort` = `itemcallnumber`");

    # items changes
    $dbh->do("ALTER TABLE `items` ADD `cn_source` VARCHAR(10) DEFAULT NULL AFTER `onloan`");
    $dbh->do("ALTER TABLE `items` ADD `cn_sort` VARCHAR(30) DEFAULT NULL AFTER `cn_source");
    $dbh->do("ALTER TABLE `items` ADD `ccode` VARCHAR(10) DEFAULT NULL AFTER `cn_sort`");
    $dbh->do("ALTER TABLE `items` ADD `materials` VARCHAR(10) DEFAULT NULL AFTER `ccode`");
    $dbh->do("ALTER TABLE `items` ADD `uri` VARCHAR(255) DEFAULT NULL AFTER `materials`");
    $dbh->do("ALTER TABLE `items` DROP KEY `itembarcodeidx`");
    $dbh->do("ALTER TABLE `items` ADD UNIQUE KEY `itembarcodeidx` (`barcode`)");

    # map items.itype to items.ccode
    $dbh->do("UPDATE `items` SET `ccode` = `itype`");

    # set cn_sort to itemcallnumber -- as with biblioitems.cn_sort,
    # will have to be subsequently updated per user's default 
    # classification scheme
    $dbh->do("UPDATE `items` SET `cn_sort` = `itemcallnumber`");

    $dbh->do("ALTER TABLE `items` DROP `cutterextra`");
    $dbh->do("ALTER TABLE `items` DROP `itype`");

    print "Upgrade to $DBversion done (major changes to biblio, biblioitems, items, and deleted* versions of same\n";
    SetVersion ($DBversion);
}

=item DropAllForeignKeys($table)

  Drop all foreign keys of the table $table
  
=cut

sub DropAllForeignKeys {
    my ($table) = @_;
    # get the table description
    my $sth = $dbh->prepare("SHOW CREATE TABLE $table");
    $sth->execute;
    my $vsc_structure = $sth->fetchrow;
    # split on CONSTRAINT keyword
    my @fks = split /CONSTRAINT /,$vsc_structure;
    # parse each entry
    foreach (@fks) {
        # isolate what is before FOREIGN KEY, if there is something, it's a foreign key to drop
        $_ = /(.*) FOREIGN KEY.*/;
        my $id = $1;
        if ($id) {
            # we have found 1 foreign, drop it
            $dbh->do("ALTER TABLE $table DROP FOREIGN KEY $id");
            $id="";
        }
    }
}







=item TransformToNum

  Transform the Koha version from a 4 parts string
  to a number, with just 1 .
  
=cut

sub TransformToNum {
    my $version = shift;
    # remove the 3 last . to have a Perl number
    $version =~ s/(.*\..*)\.(.*)\.(.*)/$1$2$3/;
    return $version;
}

=item SetVersion
    set the DBversion in the systempreferences
=cut

sub SetVersion {
    my $kohaversion = TransformToNum(shift);
    if (C4::Context->preference('Version')) {
      my $finish=$dbh->prepare("UPDATE systempreferences SET value=? WHERE variable='Version'");
      $finish->execute($kohaversion);
    } else {
      my $finish=$dbh->prepare("INSERT into systempreferences (variable,value,explanation) values ('Version',?,'The Koha database version. Don t change this value manually, it s holded by the webinstaller')");
      $finish->execute($kohaversion);
    }
}
exit;

# $Log$
# Revision 1.172  2007/07/19 10:21:22  hdl