#!/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;

# Koha modules
use C4::Database;

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
);

#-------------------
# Defines

# Tables to add if they don't exist
my %requiretables=(
    shelfcontents=>"( shelfnumber int not null, 
	itemnumber int not null, 
	flags int)",
    bookshelf=>"( shelfnumber int auto_increment primary key, 
	shelfname char(255))",
    z3950queue=>"( id int auto_increment primary key, 
	term text, 
	type char(10), 
	startdate int, 
	enddate int, 
	done smallint, 
	results longblob, 
	numrecords int, 
	servers text, 
	identifier char(30))",
    z3950results=>"( id int auto_increment primary key, 
	queryid int, 
	server char(255), 
	startdate int, 
	enddate int, 
	results longblob, 
	numrecords int, 
	numdownloaded int, 
	highestseen int, 
	active smallint)",
    branchrelations=>"( branchcode varchar(4), 
	categorycode varchar(4))",
    websites=>"( websitenumber int(11) NOT NULL auto_increment,
  	biblionumber int(11) NOT NULL default '0',
  	title text,
  	description text,
  	url varchar(255),
  	PRIMARY KEY (websitenumber) )",
    marcrecorddone=>"( isbn char(40),
		      issn char(40),
		      lccn char(40),
		      controlnumber char(40))",
    uploadedmarc=>"( id int(11) NOT NULL auto_increment PRIMARY KEY,
		    marc longblob,
		    hidden smallint(6) default NULL,
		    name varchar(255) default NULL)",
    ethnicity=>"( code varchar(10) NOT NULL default '',
  		name varchar(255) default NULL,
  		PRIMARY KEY  (code)   )",
    sessions=>"( sessionID varchar(255) NOT NULL default '',
  		userid varchar(255) default NULL,
  		ip varchar(16) default NULL,
		lasttime int,
		PRIMARY KEY (sessionID)   )",
    sessionqueries=>"( sessionID varchar(255) NOT NULL default '',
    		       userid char(100) NOT NULL default '',
    		       ip char(18) NOT NULL default '',
		       url text NOT NULL default ''  )",
);


my %requirefields=(
    biblio=>{ 'abstract' => 'text' },
    deletedbiblio=>{ 'abstract' => 'text' },
    biblioitems=>{ 'lccn' => 'char(25)',
		'url' => 'varchar(255)',
		'marc' => 'text' },
    deletedbiblioitems=>{ 'lccn' => 'char(25)',
		'url' => 'varchar(255)',
		'marc' => 'text' },
    branchtransfers=>{ 'datearrived' => 'datetime' },
    statistics=>{'borrowernumber' =>'int(11)'},
    aqbooksellers=>{'invoicedisc' =>'float(6,4)',
    		     'nocalc' => 'int(11)'},
    borrowers=>{'userid' => 'char(30)',
    		'password' => 'char(30)',},
    aqorders=>{'budgetdate' => 'date'},		   
);

# Default system preferences
my %defaultprefs=(
    'autoMemberNum'=> '1',
    'acquisitions'=> 'simple',
);

#-------------------
# Initialize
my $dbh=C4Connect;

# Start checking

# Get version of MySQL database engine.
my $mysqlversion=`mysqld --version`;
$mysqlversion=~/Ver (\S*) /;
$mysqlversion=$1;
if ($mysqlversion ge '3.23') {
    print "Could convert to MyISAM database tables...\n";
}

#---------------------------------
# Tables

# Collect all tables into a list
$sth=$dbh->prepare("show tables");
$sth->execute;
while (my ($table) = $sth->fetchrow) {
    $existingtables{$table}=1;
}

# Now add any missing tables
foreach $table ( keys %requiretables ) {
    print "Checking $table table...\n" if $debug;
    unless ($existingtables{$table} ) {
	print "Adding $table table...\n";
	my $sth=$dbh->prepare(
		"create table $table $requiretables{$table}" );
	$sth->execute;
        if ($sth->err) {
                print "Error : $sth->errstr \n";
                $sth->finish;
        } # if error
    } # unless exists
} # foreach

unless ($existingtables{'z3950servers'}) {
    print "Adding z3950servers table...\n";
    my $sti=$dbh->prepare("create table z3950servers (
	host char(255), 
	port int, 
	db char(255), 
	userid char(255), 
	password char(255), 
	name text, 
	id int, 
	checked smallint, 
	rank int)");
    $sti->execute;
    $sti=$dbh->prepare("insert into z3950servers 
	values ('z3950.loc.gov', 
	7090, 
	'voyager', 
	'', '', 
	'Library of Congress', 
	1, 1, 1)");
    $sti->execute;
}

#---------------------------------
# Columns

foreach $table ( keys %requirefields ) {
    print "Check table $table\n" if $debug;
    $sth=$dbh->prepare("show columns from $table");
    $sth->execute();
    undef %types;
    while ( ($column, $type, $null, $key, $default, $extra) 
		= $sth->fetchrow) {
        $types{$column}=$type;
    } # while 
    foreach $column ( keys %{ $requirefields{$table} } )  {
	print "  Check column $column\n" if $debug;
	if ( ! $types{$column} ) {
	    # column doesn't exist
	    print "Adding $column field to $table table...\n";
    	    $query="alter table $table
		add column $column " . $requirefields{$table}->{$column} ;
	    print "Execute: $query\n" if $debug;
    	    my $sti=$dbh->prepare($query);
    	    $sti->execute;
            if ($sti->err) {
                    print "**Error : $sti->errstr \n";
                    $sti->finish;
            } # if error
	} # if column
    } # foreach column
} # foreach table

# Get list of columns from items table
my %itemtypes;

my $sth=$dbh->prepare("show columns from items");
$sth->execute;
while (my ($column, $type, $null, $key, $default, $extra) = $sth->fetchrow) {
    $itemtypes{$column}=$type;
}

unless ($itemtypes{'barcode'} eq 'varchar(20)') {
    $itemtypes{'barcode'}=~/varchar\((\d+)\)/;
    my $oldlength=$1;
    if ($oldlength<20) {
	print "Setting maximum barcode length to 20 (was $oldlength).\n";
	my $sti=$dbh->prepare("alter table items change barcode barcode varchar(20) not null");
	$sti->execute;
    }
}

# extending the timestamp in branchtransfers...
my %branchtransfers;

my $sth=$dbh->prepare("show columns from branchtransfers");
$sth->execute;
while (my ($column, $type, $null, $key, $default, $extra) = $sth->fetchrow) {
    $branchtransfers{$column}=$type;
}

unless ($branchtransfers{'datesent'} eq 'datetime') {
    print "Setting type of datesent in branchtransfers to datetime.\n";
    my $sti=$dbh->prepare("alter table branchtransfers change datesent datesent datetime");
    $sti->execute;
}

unless ($branchtransfers{'datearrived'} eq 'datetime') {
    print "Setting type of datearrived in branchtransfers to datetime.\n";
    my $sti=$dbh->prepare("alter table branchtransfers change datearrived datearrived datetime");
    $sti->execute;
}

# changing the branchcategories table around...
my %branchcategories;

my $sth=$dbh->prepare("show columns from branchcategories");
$sth->execute;
while (my ($column, $type, $null, $key, $default, $extra) = $sth->fetchrow) {
    $branchcategories{$column}=$type;
}

unless ($branchcategories{'categorycode'} eq 'varchar(4)') {
    print "Setting type of categorycode in branchcategories to varchar(4),\n and making the primary key.\n";
    my $sti=$dbh->prepare("alter table branchcategories change categorycode categorycode varchar(4) not null");
    $sti->execute;
    $sti=$dbh->prepare("alter table branchcategories add primary key (categorycode)");
    $sti->execute;
}

unless ($branchcategories{'categoryname'} eq 'text') {
    print "Changing branchcode in branchcategories to categoryname text.\n";
    my $sth=$dbh->prepare("alter table branchcategories change branchcode categoryname text");
    $sth->execute;
}

unless ($branchcategories{'codedescription'} eq 'text') {
    print "Replacing branchholding in branchcategories with codedescription text.\n";
    my $sth=$dbh->prepare("alter table branchcategories change branchholding codedescription text");
    $sth->execute;
}


# Populate systempreferences if it is empty

foreach $prefitem ( keys %defaultprefs ) {
    $sth=$dbh->prepare("select value 
	from systempreferences 
	where variable=?");
    $sth->execute($prefitem);
    unless ($sth->rows) {
	print "Adding system preference item $prefitem with value " .
        	$defaultprefs{$prefitem} ."\n";
        $sti=$dbh->prepare("
	  insert into systempreferences (variable, value) 
	  values (?,?)");
        $sti->execute($prefitem,$defaultprefs{$prefitem});
    } # unless
} # foreach


$sth->finish;
$dbh->disconnect;

exit;

# $Log$
# Revision 1.15  2002/07/20 22:30:06  rangi
# Making sure fix makes it into the main branch as well
# Fix for bug 69
#
# Revision 1.14  2002/07/08 16:20:26  tonnesen
# Added sessionqueries table and password/userid fields to borrowers table
#
# Revision 1.13  2002/07/04 18:05:36  tonnesen
# bug fix
#
# Revision 1.12  2002/07/04 16:41:06  tonnesen
# Merged changes from rel-1-2.  Abstracted table structure changes by alan.
#