updater/updatedatabase

   1 #!/usr/bin/perl
   2
   3 # $Id$
   4
   5 # Database Updater
   6 # This script checks for required updates to the database.
   7
   8 # Part of the Koha Library Software www.koha.org
   9 # Licensed under the GPL.
  10
  11 # Bugs/ToDo:
  12 # - Would also be a good idea to offer to do a backup at this time...
  13
  14 # NOTE:  If you do something more than once in here, make it table driven.
  15
  16 # Set C4 module directory
  17
  18 BEGIN {
  19     $intranetdir=`grep intranetdir /etc/koha.conf`;
  20     chomp $intranetdir;
  21     $intranetdir=~s/\s*intranetdir\s*=\s*//i;
  22     $::modulesdir=$intranetdir."/modules";
  23 }
  24
  25 use lib $::modulesdir;
  26
  27
  28 use strict;
  29
  30 # CPAN modules
  31 use DBI;
  32
  33 # Koha modules
  34 use C4::Database;
  35
  36 my $debug=1;
  37
  38 my (
  39         $sth, $sti,
  40         $query,
  41         %existingtables,        # tables already in database
  42         %types,
  43         $table,
  44         $column,
  45         $type, $null, $key, $default, $extra,
  46         $prefitem,              # preference item in systempreferences table
  47 );
  48
  49 #-------------------
  50 # Defines
  51
  52 # Tables to add if they don't exist
  53 my %requiretables=(
  54     shelfcontents=>"( shelfnumber int not null,
  55         itemnumber int not null,
  56         flags int)",
  57     userflags=>"( bit int not null,
  58         flag char(30),
  59         flagdesc char(255),
  60         defaulton int
  61     )",
  62     bookshelf=>"( shelfnumber int auto_increment primary key,
  63         shelfname char(255))",
  64     z3950queue=>"( id int auto_increment primary key,
  65         term text,
  66         type char(10),
  67         startdate int,
  68         enddate int,
  69         done smallint,
  70         results longblob,
  71         numrecords int,
  72         servers text,
  73         identifier char(30))",
  74     z3950results=>"( id int auto_increment primary key,
  75         queryid int,
  76         server char(255),
  77         startdate int,
  78         enddate int,
  79         results longblob,
  80         numrecords int,
  81         numdownloaded int,
  82         highestseen int,
  83         active smallint)",
  84     branchrelations=>"( branchcode varchar(4),
  85         categorycode varchar(4))",
  86     websites=>"( websitenumber int(11) NOT NULL auto_increment,
  87         biblionumber int(11) NOT NULL default '0',
  88         title text,
  89         description text,
  90         url varchar(255),
  91         PRIMARY KEY (websitenumber) )",
  92     marcrecorddone=>"( isbn char(40),
  93                       issn char(40),
  94                       lccn char(40),
  95                       controlnumber char(40))",
  96     uploadedmarc=>"( id int(11) NOT NULL auto_increment PRIMARY KEY,
  97                     marc longblob,
  98                     hidden smallint(6) default NULL,
  99                     name varchar(255) default NULL)",
 100     ethnicity=>"( code varchar(10) NOT NULL default '',
 101                 name varchar(255) default NULL,
 102                 PRIMARY KEY  (code)   )",
 103     sessions=>"( sessionID varchar(255) NOT NULL default '',
 104                 userid varchar(255) default NULL,
 105                 ip varchar(16) default NULL,
 106                 lasttime int,
 107                 PRIMARY KEY (sessionID)   )",
 108     sessionqueries=>"( sessionID varchar(255) NOT NULL default '',
 109                        userid char(100) NOT NULL default '',
 110                        ip char(18) NOT NULL default '',
 111                        url text NOT NULL default ''  )",
 112 );
 113
 114
 115 my %requirefields=(
 116     biblio=>{ 'abstract' => 'text' },
 117     deletedbiblio=>{ 'abstract' => 'text' },
 118     biblioitems=>{ 'lccn' => 'char(25)',
 119                 'url' => 'varchar(255)',
 120                 'marc' => 'text' },
 121     deletedbiblioitems=>{ 'lccn' => 'char(25)',
 122                 'url' => 'varchar(255)',
 123                 'marc' => 'text' },
 124     branchtransfers=>{ 'datearrived' => 'datetime' },
 125     statistics=>{'borrowernumber' =>'int(11)'},
 126     aqbooksellers=>{'invoicedisc' =>'float(6,4)',
 127                      'nocalc' => 'int(11)'},
 128     borrowers=>{'userid' => 'char(30)',
 129                 'password' => 'char(30)',
 130                 'flags' => 'int',},
 131     aqorders=>{'budgetdate' => 'date'},
 132 );
 133
 134 # Default system preferences
 135 my %defaultprefs=(
 136     'autoMemberNum'=> '1',
 137     'acquisitions'=> 'simple',
 138 );
 139
 140
 141
 142
 143 # The uniquefieldrequired hash entry is used to determine which (if any) fields
 144 # must not exist in the table for this row to be inserted.
 145
 146 my %tabledata=(
 147     userflags => [
 148         { uniquefieldrequired => 'bit', bit => 0, flag => 'superlibrarian', flagdesc => 'Access to all librarian functions', defaulton => 0 },
 149         { uniquefieldrequired => 'bit', bit => 1, flag => 'circulate', flagdesc => 'Circulate books', defaulton => 0 },
 150         { uniquefieldrequired => 'bit', bit => 2, flag => 'catalogue', flagdesc => 'View Catalogue (Librarian Interface)', defaulton => 0 },
 151         { uniquefieldrequired => 'bit', bit => 3, flag => 'parameters', flagdesc => 'Set Koha system paramters', defaulton => 0 },
 152         { uniquefieldrequired => 'bit', bit => 4, flag => 'borrowers', flagdesc => 'Add or modify borrowers', defaulton => 0 },
 153         { uniquefieldrequired => 'bit', bit => 5, flag => 'permissions', flagdesc => 'Set user permissions', defaulton => 0 },
 154         { uniquefieldrequired => 'bit', bit => 6, flag => 'reserveforothers', flagdesc => 'Reserve books for patrons', defaulton => 0 },
 155         { uniquefieldrequired => 'bit', bit => 7, flag => 'borrow', flagdesc => 'Borrow books', defaulton => 1 },
 156         { uniquefieldrequired => 'bit', bit => 8, flag => 'reserveforself', flagdesc => 'Reserve books for self', defaulton => 0 },
 157         { uniquefieldrequired => 'bit', bit => 9, flag => 'editcatalogue', flagdesc => 'Edit Catalogue (Modify bibliographic/holdings data)', defaulton => 0 },
 158         { uniquefieldrequired => 'bit', bit => 10, flag => 'updatecharges', flagdesc => 'Update borrower charges', defaulton => 0 },
 159     ],
 160     systempreferences => [
 161         { uniquefieldrequired => 'variable', variable => 'autoMemberNum', value => '1' },
 162         { uniquefieldrequired => 'variable', variable => 'acquisitions', value => 'simple' },
 163         { uniquefieldrequired => 'variable', variable => 'dateformat', value => 'metric' },
 164     ],
 165
 166 );
 167
 168
 169 my %fielddefinitions=(
 170 printers => [
 171         { field => 'printername', type => 'char(40)', null => '', key => 'PRI', default => '' },
 172         ],
 173 z3950servers => [
 174         { field => 'id', type => 'int', null => '', key => 'PRI', default => '', extra => 'auto_increment' },
 175         ],
 176 );
 177
 178
 179
 180 #-------------------
 181 # Initialize
 182 my $dbh=C4Connect;
 183
 184 # Start checking
 185
 186 # Get version of MySQL database engine.
 187 my $mysqlversion=`mysqld --version`;
 188 $mysqlversion=~/Ver (\S*) /;
 189 $mysqlversion=$1;
 190 if ($mysqlversion ge '3.23') {
 191     print "Could convert to MyISAM database tables...\n";
 192 }
 193
 194 #---------------------------------
 195 # Tables
 196
 197 # Collect all tables into a list
 198 $sth=$dbh->prepare("show tables");
 199 $sth->execute;
 200 while (my ($table) = $sth->fetchrow) {
 201     $existingtables{$table}=1;
 202 }
 203
 204 # Now add any missing tables
 205 foreach $table ( keys %requiretables ) {
 206     print "Checking $table table...\n" if $debug;
 207     unless ($existingtables{$table} ) {
 208         print "Adding $table table...\n";
 209         my $sth=$dbh->prepare(
 210                 "create table $table $requiretables{$table}" );
 211         $sth->execute;
 212         if ($sth->err) {
 213                 print "Error : $sth->errstr \n";
 214                 $sth->finish;
 215         } # if error
 216     } # unless exists
 217 } # foreach
 218
 219 unless ($existingtables{'z3950servers'}) {
 220     print "Adding z3950servers table...\n";
 221     my $sti=$dbh->prepare("create table z3950servers (
 222         host char(255),
 223         port int,
 224         db char(255),
 225         userid char(255),
 226         password char(255),
 227         name text,
 228         id int,
 229         checked smallint,
 230         rank int)");
 231     $sti->execute;
 232     $sti=$dbh->prepare("insert into z3950servers
 233         values ('z3950.loc.gov',
 234         7090,
 235         'voyager',
 236         '', '',
 237         'Library of Congress',
 238         1, 1, 1)");
 239     $sti->execute;
 240 }
 241
 242 #---------------------------------
 243 # Columns
 244
 245 foreach $table ( keys %requirefields ) {
 246     print "Check table $table\n" if $debug;
 247     $sth=$dbh->prepare("show columns from $table");
 248     $sth->execute();
 249     undef %types;
 250     while ( ($column, $type, $null, $key, $default, $extra)
 251                 = $sth->fetchrow) {
 252         $types{$column}=$type;
 253     } # while
 254     foreach $column ( keys %{ $requirefields{$table} } )  {
 255         print "  Check column $column\n" if $debug;
 256         if ( ! $types{$column} ) {
 257             # column doesn't exist
 258             print "Adding $column field to $table table...\n";
 259             $query="alter table $table
 260                 add column $column " . $requirefields{$table}->{$column} ;
 261             print "Execute: $query\n" if $debug;
 262             my $sti=$dbh->prepare($query);
 263             $sti->execute;
 264             if ($sti->err) {
 265                     print "**Error : $sti->errstr \n";
 266                     $sti->finish;
 267             } # if error
 268         } # if column
 269     } # foreach column
 270 } # foreach table
 271
 272 foreach $table ( keys %fielddefinitions ) {
 273     print "Check table $table\n" if $debug;
 274     $sth=$dbh->prepare("show columns from $table");
 275     $sth->execute();
 276     my $definitions;
 277     while ( ($column, $type, $null, $key, $default, $extra) = $sth->fetchrow) {
 278         $definitions->{$column}->{type}=$type;
 279         $definitions->{$column}->{null}=$null;
 280         $definitions->{$column}->{key}=$key;
 281         $definitions->{$column}->{default}=$default;
 282         $definitions->{$column}->{extra}=$extra;
 283     } # while
 284     my $fieldrow=$fielddefinitions{$table};
 285     foreach my $row ( @$fieldrow )  {
 286         my $field = $row->{field};
 287         my $type = $row->{type};
 288         my $null = $row->{null};
 289         my $key = $row->{key};
 290         my $default = $row->{default};
 291         my $extra = $row->{extra};
 292         my $def=$definitions->{$field};
 293         unless ($type eq $def->{type} && $null eq $def->{null} && $key eq $def->{key} && $default eq $def->{default} && $extra eq $def->{extra}) {
 294             if ($null eq '') {
 295                 $null='NOT NULL';
 296             }
 297             if ($key eq 'PRI') {
 298                 $key ='PRIMARY KEY';
 299             }
 300             unless ($extra eq 'auto_increment') {
 301                 $extra='';
 302             }
 303             my $sth=$dbh->prepare("alter table $table change $field $field $type $null $key $extra default ?");
 304             $sth->execute($default);
 305             print "  Alter $field in $table\n";
 306         }
 307     }
 308 }
 309
 310 # Get list of columns from items table
 311 my %itemtypes;
 312
 313 my $sth=$dbh->prepare("show columns from items");
 314 $sth->execute;
 315 while (my ($column, $type, $null, $key, $default, $extra) = $sth->fetchrow) {
 316     $itemtypes{$column}=$type;
 317 }
 318
 319 unless ($itemtypes{'barcode'} eq 'varchar(20)') {
 320     $itemtypes{'barcode'}=~/varchar\((\d+)\)/;
 321     my $oldlength=$1;
 322     if ($oldlength<20) {
 323         print "Setting maximum barcode length to 20 (was $oldlength).\n";
 324         my $sti=$dbh->prepare("alter table items change barcode barcode varchar(20) not null");
 325         $sti->execute;
 326     }
 327 }
 328
 329 # extending the timestamp in branchtransfers...
 330 my %branchtransfers;
 331
 332 my $sth=$dbh->prepare("show columns from branchtransfers");
 333 $sth->execute;
 334 while (my ($column, $type, $null, $key, $default, $extra) = $sth->fetchrow) {
 335     $branchtransfers{$column}=$type;
 336 }
 337
 338 unless ($branchtransfers{'datesent'} eq 'datetime') {
 339     print "Setting type of datesent in branchtransfers to datetime.\n";
 340     my $sti=$dbh->prepare("alter table branchtransfers change datesent datesent datetime");
 341     $sti->execute;
 342 }
 343
 344 unless ($branchtransfers{'datearrived'} eq 'datetime') {
 345     print "Setting type of datearrived in branchtransfers to datetime.\n";
 346     my $sti=$dbh->prepare("alter table branchtransfers change datearrived datearrived datetime");
 347     $sti->execute;
 348 }
 349
 350 # changing the branchcategories table around...
 351 my %branchcategories;
 352
 353 my $sth=$dbh->prepare("show columns from branchcategories");
 354 $sth->execute;
 355 while (my ($column, $type, $null, $key, $default, $extra) = $sth->fetchrow) {
 356     $branchcategories{$column}=$type;
 357 }
 358
 359 unless ($branchcategories{'categorycode'} eq 'varchar(4)') {
 360     print "Setting type of categorycode in branchcategories to varchar(4),\n and making the primary key.\n";
 361     my $sti=$dbh->prepare("alter table branchcategories change categorycode categorycode varchar(4) not null");
 362     $sti->execute;
 363     $sti=$dbh->prepare("alter table branchcategories add primary key (categorycode)");
 364     $sti->execute;
 365 }
 366
 367 unless ($branchcategories{'categoryname'} eq 'text') {
 368     print "Changing branchcode in branchcategories to categoryname text.\n";
 369     my $sth=$dbh->prepare("alter table branchcategories change branchcode categoryname text");
 370     $sth->execute;
 371 }
 372
 373 unless ($branchcategories{'codedescription'} eq 'text') {
 374     print "Replacing branchholding in branchcategories with codedescription text.\n";
 375     my $sth=$dbh->prepare("alter table branchcategories change branchholding codedescription text");
 376     $sth->execute;
 377 }
 378
 379
 380 # Populate tables with required data
 381
 382 foreach my $table (keys %tabledata) {
 383     print "Checking for data required in table $table...\n";
 384     my $tablerows=$tabledata{$table};
 385     foreach my $row (@$tablerows) {
 386         my $uniquefieldrequired=$row->{uniquefieldrequired};
 387         my $uniquevalue=$row->{$uniquefieldrequired};
 388         my $sth=$dbh->prepare("select $uniquefieldrequired from $table where $uniquefieldrequired=?");
 389         $sth->execute($uniquevalue);
 390         unless ($sth->rows) {
 391             print "Adding row to $table: ";
 392             my @values;
 393             my $fieldlist;
 394             my $placeholders;
 395             foreach my $field (keys %$row) {
 396                 (next) if ($field eq 'uniquefieldrequired');
 397                 my $value=$row->{$field};
 398                 push @values, $value;
 399                 print "  $field => $value";
 400                 $fieldlist.="$field,";
 401                 $placeholders.="?,";
 402             }
 403             print "\n";
 404             $fieldlist=~s/,$//;
 405             $placeholders=~s/,$//;
 406             my $sth=$dbh->prepare("insert into $table ($fieldlist) values ($placeholders)");
 407             $sth->execute(@values);
 408         }
 409     }
 410 }
 411
 412 $sth->finish;
 413 $dbh->disconnect;
 414
 415 exit;
 416
 417 # $Log$
 418 # Revision 1.4.2.25  2002/10/29 20:22:43  tonnesen
 419 # buildrelease now puts the test scripts in $intranetdir/scripts/t/
 420 #
 421 # Revision 1.4.2.24  2002/10/25 18:52:47  tonnesen
 422 # Moved bulkmracimport.pl to scripts directory (through buildrelease script)
 423 # Made bulkmarcimport.pl and updatedatabase able to run from the command line
 424 # without specifying the location of the C4 module directory (determines the
 425 # directory from /etc/koha.conf).
 426 #
 427 # Revision 1.4.2.23  2002/10/25 18:28:39  tonnesen
 428 # Added admin script for editing z39.50 server list.
 429 #
 430 # Revision 1.4.2.22  2002/10/25 17:34:33  tonnesen
 431 # Initialize the dateformat systempreference variable to 'metric' if it doesn't
 432 # exist.
 433 #
 434 # Revision 1.4.2.21  2002/09/17 22:01:36  tonnesen
 435 # Added a routine to check the definitions of individual fields in a table.
 436 #
 437 # Revision 1.4.2.20  2002/09/09 19:45:40  uid41696
 438 # Adding authentication calls to intranet scripts
 439 #
 440 # Revision 1.4.2.19  2002/07/26 18:23:54  tonnesen
 441 # Added flags to borrowers table and userflags table.  Also, OPAC scripts now
 442 # check for the existence of a file named 'opac' in the directory they are run
 443 # from and set the $type variable to 'opac' if it exists.  No longer have to
 444 # "trust" that the type query parameter is correct.
 445 #
 446 # Revision 1.4.2.18  2002/07/20 22:28:13  rangi
 447 # Fixing missing column (budgetdate) in aqorders
 448 # re bug 69
 449 #
 450 # Revision 1.4.2.17  2002/07/11 18:36:39  tonnesen
 451 # New fields and tables for authentication module.
 452 #
 453 # Revision 1.14  2002/07/08 16:20:26  tonnesen
 454 # Added sessionqueries table and password/userid fields to borrowers table
 455 #
 456 # Revision 1.13  2002/07/04 18:05:36  tonnesen
 457 # bug fix
 458 #
 459 # Revision 1.12  2002/07/04 16:41:06  tonnesen
 460 # Merged changes from rel-1-2.  Abstracted table structure changes by alan.
 461 #