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

# Set C4 module directory

BEGIN {
    $intranetdir=`grep intranetdir /etc/koha.conf`;
    chomp $intranetdir;
    $intranetdir=~s/\s*intranetdir\s*=\s*//i;
    $::modulesdir=$intranetdir."/modules";
}

use lib $::modulesdir;


use strict;

# CPAN modules
use DBI;

# Koha modules
use C4::Database;

my $debug=1;

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)",
    userflags=>"( bit int not null,
	flag char(30),
	flagdesc char(255),
	defaulton 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 ''  )",
    itemtypesearchgroups=>"( groupname varchar(255) NOT NULL default '' primary key,
    		       itemtypes char(100) 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)',
		'flags' => 'int',},
    aqorders=>{'budgetdate' => 'date'},		   
    reserves=>{'id' => 'int auto_increment primary key',
	       'required_date' => 'date',
	       'expires_date' => 'date'},
    reserveconstraints=>{'reserveid' => 'int'},
    itemtypes=>{'publictype' => 'char(30)'},
);

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




# The uniquefieldrequired hash entry is used to determine which (if any) fields
# must not exist in the table for this row to be inserted.

my %tabledata=(
    userflags => [ 
    	{ uniquefieldrequired => 'bit', bit => 0, flag => 'superlibrarian', flagdesc => 'Access to all librarian functions', defaulton => 0 },
    	{ uniquefieldrequired => 'bit', bit => 1, flag => 'circulate', flagdesc => 'Circulate books', defaulton => 0 },
    	{ uniquefieldrequired => 'bit', bit => 2, flag => 'catalogue', flagdesc => 'View Catalogue (Librarian Interface)', defaulton => 0 },
    	{ uniquefieldrequired => 'bit', bit => 3, flag => 'parameters', flagdesc => 'Set Koha system paramters', defaulton => 0 },
    	{ uniquefieldrequired => 'bit', bit => 4, flag => 'borrowers', flagdesc => 'Add or modify borrowers', defaulton => 0 },
    	{ uniquefieldrequired => 'bit', bit => 5, flag => 'permissions', flagdesc => 'Set user permissions', defaulton => 0 },
    	{ uniquefieldrequired => 'bit', bit => 6, flag => 'reserveforothers', flagdesc => 'Reserve books for patrons', defaulton => 0 },
    	{ uniquefieldrequired => 'bit', bit => 7, flag => 'borrow', flagdesc => 'Borrow books', defaulton => 1 },
    	{ uniquefieldrequired => 'bit', bit => 8, flag => 'reserveforself', flagdesc => 'Reserve books for self', defaulton => 0 },
    	{ uniquefieldrequired => 'bit', bit => 9, flag => 'editcatalogue', flagdesc => 'Edit Catalogue (Modify bibliographic/holdings data)', defaulton => 0 },
    	{ uniquefieldrequired => 'bit', bit => 10, flag => 'updatecharges', flagdesc => 'Update borrower charges', defaulton => 0 },
    ],
    systempreferences => [
    	{ uniquefieldrequired => 'variable', variable => 'autoMemberNum', value => '1' },
    	{ uniquefieldrequired => 'variable', variable => 'acquisitions', value => 'simple' },
    	{ uniquefieldrequired => 'variable', variable => 'dateformat', value => 'metric' },
    ],

);


my %fielddefinitions=(
printers => [
	{ field => 'printername', type => 'char(40)', null => '', key => 'PRI', default => '' },
	],
aqbookfund => [
	{ field => 'bookfundid', type => 'char(5)', null => '', key => 'PRI', default => '' },
	],
z3950servers => [
	{ field => 'id', type => 'int', null => '', key => 'PRI', default => '', extra => 'auto_increment' },
	],
aqbooksellers => [
	{ field => 'id', type => 'int', null => '', key => 'PRI', default => '', extra => 'auto_increment' },
	],
);



#-------------------
# 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

foreach $table ( keys %fielddefinitions ) {
    print "Check table $table\n" if $debug;
    $sth=$dbh->prepare("show columns from $table");
    $sth->execute();
    my $definitions;
    while ( ($column, $type, $null, $key, $default, $extra) = $sth->fetchrow) {
	$definitions->{$column}->{type}=$type;
	$definitions->{$column}->{null}=$null;
	$definitions->{$column}->{key}=$key;
	$definitions->{$column}->{default}=$default;
	$definitions->{$column}->{extra}=$extra;
    } # while 
    my $fieldrow=$fielddefinitions{$table};
    foreach my $row ( @$fieldrow )  {
	my $field = $row->{field};
	my $type = $row->{type};
	my $null = $row->{null};
	my $key = $row->{key};
	my $default = $row->{default};
	my $extra = $row->{extra};
	my $def=$definitions->{$field};
	unless ($type eq $def->{type} && $null eq $def->{null} && $key eq $def->{key} && $default eq $def->{default} && $extra eq $def->{extra}) {
	    if ($null eq '') {
		$null='NOT NULL';
	    }
	    if ($key eq 'PRI') {
		$key ='PRIMARY KEY';
	    }
	    unless ($extra eq 'auto_increment') {
		$extra='';
	    }
	    my $sth=$dbh->prepare("alter table $table change $field $field $type $null $key $extra default ?");
	    $sth->execute($default);
	    print "  Alter $field in $table\n";
	}
    }
}

# 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 tables with required data
 
foreach my $table (keys %tabledata) {
    print "Checking for data required in table $table...\n";
    my $tablerows=$tabledata{$table};
    foreach my $row (@$tablerows) {
	my $uniquefieldrequired=$row->{uniquefieldrequired};
	my $uniquevalue=$row->{$uniquefieldrequired};
	my $sth=$dbh->prepare("select $uniquefieldrequired from $table where $uniquefieldrequired=?");
	$sth->execute($uniquevalue);
	unless ($sth->rows) {
	    print "Adding row to $table: ";
	    my @values;
	    my $fieldlist;
	    my $placeholders;
	    foreach my $field (keys %$row) {
		(next) if ($field eq 'uniquefieldrequired');
		my $value=$row->{$field};
		push @values, $value;
		print "  $field => $value";
		$fieldlist.="$field,";
		$placeholders.="?,";
	    }
	    print "\n";
	    $fieldlist=~s/,$//;
	    $placeholders=~s/,$//;
	    my $sth=$dbh->prepare("insert into $table ($fieldlist) values ($placeholders)");
	    $sth->execute(@values);
	}
    }
}

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

exit;

# $Log$
# Revision 1.4.2.31  2003/01/17 18:18:48  tonnesen
# aqbooksellers 'id' field needs to be an auto_increment field, otherwise id is
# left NULL when new sellers are added.
#
# Revision 1.4.2.30  2002/11/26 04:42:33  finlayt
#
# Added the publictype field to the itemtypes table. This is used in the hlt opac to assign images to each of the types.
#
# Revision 1.4.2.29  2002/11/12 17:30:35  tonnesen
# change bookfundid to primary key in aqbookfund
#
# Revision 1.4.2.28  2002/11/05 22:53:28  tonnesen
# Removed set_date (reservedate already provides that functionality).
#
# Revision 1.4.2.27  2002/11/05 22:38:12  tonnesen
# Added numerical id keys to reserves and reserveconstraints.
# Added set_date,required_date,expires_date fields to reserves.
#
# Revision 1.4.2.26  2002/11/05 19:47:12  tonnesen
# Adding an admin script for setting up the 'class' list in the advanced search
# pages.
#
# Revision 1.4.2.25  2002/10/29 20:22:43  tonnesen
# buildrelease now puts the test scripts in $intranetdir/scripts/t/
#
# Revision 1.4.2.24  2002/10/25 18:52:47  tonnesen
# Moved bulkmracimport.pl to scripts directory (through buildrelease script)
# Made bulkmarcimport.pl and updatedatabase able to run from the command line
# without specifying the location of the C4 module directory (determines the
# directory from /etc/koha.conf).
#
# Revision 1.4.2.23  2002/10/25 18:28:39  tonnesen
# Added admin script for editing z39.50 server list.
#
# Revision 1.4.2.22  2002/10/25 17:34:33  tonnesen
# Initialize the dateformat systempreference variable to 'metric' if it doesn't
# exist.
#
# Revision 1.4.2.21  2002/09/17 22:01:36  tonnesen
# Added a routine to check the definitions of individual fields in a table.
#
# Revision 1.4.2.20  2002/09/09 19:45:40  uid41696
# Adding authentication calls to intranet scripts
#
# Revision 1.4.2.19  2002/07/26 18:23:54  tonnesen
# Added flags to borrowers table and userflags table.  Also, OPAC scripts now
# check for the existence of a file named 'opac' in the directory they are run
# from and set the $type variable to 'opac' if it exists.  No longer have to
# "trust" that the type query parameter is correct.
#
# Revision 1.4.2.18  2002/07/20 22:28:13  rangi
# Fixing missing column (budgetdate) in aqorders
# re bug 69
#
# Revision 1.4.2.17  2002/07/11 18:36:39  tonnesen
# New fields and tables for authentication module.
#
# 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.
#
