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 use strict;
  17
  18 # CPAN modules
  19 use DBI;
  20
  21 # Koha modules
  22 use C4::Context;
  23         # FIXME - /etc/koha.conf might not exist, so shouldn't use
  24         # C4::Context.
  25
  26 # FIXME - The user might be installing a new database, so can't rely
  27 # on /etc/koha.conf anyway.
  28
  29 my $debug=0;
  30
  31 my (
  32         $sth, $sti,
  33         $query,
  34         %existingtables,        # tables already in database
  35         %types,
  36         $table,
  37         $column,
  38         $type, $null, $key, $default, $extra,
  39         $prefitem,              # preference item in systempreferences table
  40 );
  41
  42 my $dbh = C4::Context->dbh;
  43
  44 #-------------------
  45 # Defines
  46
  47 # Tables to add if they don't exist
  48 my %requiretables=(
  49     shelfcontents=>"( shelfnumber int not null,
  50                                                         itemnumber int not null,
  51                                                         flags int)",
  52     bookshelf=>"( shelfnumber int auto_increment primary key,
  53                                                 shelfname char(255))",
  54     z3950queue=>"( id int auto_increment primary key,
  55                                                 term text,
  56                                                 type char(10),
  57                                                 startdate int,
  58                                                 enddate int,
  59                                                 done smallint,
  60                                                 results longblob,
  61                                                 numrecords int,
  62                                                 servers text,
  63                                                 identifier char(30))",
  64         z3950results=>"( id int auto_increment primary key,
  65                                                 queryid int,
  66                                                 server char(255),
  67                                                 startdate int,
  68                                                 enddate int,
  69                                                 results longblob,
  70                                                 numrecords int,
  71                                                 numdownloaded int,
  72                                                 highestseen int,
  73                                                 active smallint)",
  74         branchrelations=>"( branchcode varchar(4),
  75                                                         categorycode varchar(4))",
  76         websites=>"( websitenumber int(11) NOT NULL auto_increment,
  77                                                 biblionumber int(11) NOT NULL default '0',
  78                                                 title text,
  79                                                 description text,
  80                                                 url varchar(255),
  81                                                 PRIMARY KEY (websitenumber) )",
  82         marcrecorddone=>"( isbn char(40),
  83                                                                 issn char(40),
  84                                                                 lccn char(40),
  85                                                                 controlnumber char(40))",
  86     uploadedmarc=>"( id int(11) NOT NULL auto_increment PRIMARY KEY,
  87                                                         marc longblob,
  88                                                         hidden smallint(6) default NULL,
  89                                                         name varchar(255) default NULL)",
  90         ethnicity=>"( code varchar(10) NOT NULL default '',
  91                                         name varchar(255) default NULL,
  92                                         PRIMARY KEY  (code)   )",
  93         sessions=>"( sessionID varchar(255) NOT NULL default '',
  94                                                 userid varchar(255) default NULL,
  95                                                 ip varchar(16) default NULL,
  96                                                 lasttime int,
  97                                                 PRIMARY KEY (sessionID)   )",
  98     sessionqueries=>"( sessionID varchar(255) NOT NULL default '',
  99                                                                 userid char(100) NOT NULL default '',
 100                                                                 ip char(18) NOT NULL default '',
 101                                                                 url text NOT NULL default ''  )",
 102         bibliothesaurus=> "( code BIGINT not null AUTO_INCREMENT,
 103                                                 freelib CHAR (255) not null ,
 104                                                 stdlib CHAR (255) not null ,
 105                                                 type CHAR (80) not null ,
 106                                                 PRIMARY KEY (code),
 107                                                 INDEX (freelib),index(stdlib),index(type) )",
 108         marc_biblio => "(
 109                                                 bibid bigint(20) unsigned NOT NULL auto_increment,
 110                                                 biblionumber int(11) NOT NULL default '0',
 111                                                 datecreated date NOT NULL default '0000-00-00',
 112                                                 datemodified date default NULL,
 113                                                 origincode char(20) default NULL,
 114                                                 PRIMARY KEY  (bibid),
 115                                                 KEY origincode (origincode),
 116                                                 KEY biblionumber (biblionumber)
 117                                                 ) ",
 118         marc_blob_subfield => "(
 119                                         blobidlink bigint(20) NOT NULL auto_increment,
 120                                         subfieldvalue longtext NOT NULL,
 121                                         PRIMARY KEY  (blobidlink)
 122                                         ) ",
 123         marc_subfield_structure => "(
 124                                                 tagfield char(3) NOT NULL default '',
 125                                                 tagsubfield char(1) NOT NULL default '',
 126                                                 liblibrarian char(255) NOT NULL default '',
 127                                                 libopac char(255) NOT NULL default '',
 128                                                 repeatable tinyint(4) NOT NULL default '0',
 129                                                 mandatory tinyint(4) NOT NULL default '0',
 130                                                 kohafield char(40) NOT NULL default '',
 131                                                 tab tinyint(1),
 132                                                 PRIMARY KEY  (tagfield,tagsubfield),
 133                                                 KEY kohafield (kohafield),
 134                                                 KEY tab (tab)
 135                                                 )",
 136         marc_subfield_table => "(
 137                                                 subfieldid bigint(20) unsigned NOT NULL auto_increment,
 138                                                 bibid bigint(20) unsigned NOT NULL default '0',
 139                                                 tag char(3) NOT NULL default '',
 140                                                 tagorder tinyint(4) NOT NULL default '1',
 141                                                 tag_indicator char(2) NOT NULL default '',
 142                                                 subfieldcode char(1) NOT NULL default '',
 143                                                 subfieldorder tinyint(4) NOT NULL default '1',
 144                                                 subfieldvalue varchar(255) default NULL,
 145                                                 valuebloblink bigint(20) default NULL,
 146                                                 PRIMARY KEY  (subfieldid),
 147                                                 KEY bibid (bibid),
 148                                                 KEY tag (tag),
 149                                                 KEY tag_indicator (tag_indicator),
 150                                                 KEY subfieldorder (subfieldorder),
 151                                                 KEY subfieldcode (subfieldcode),
 152                                                 KEY subfieldvalue (subfieldvalue),
 153                                                 KEY tagorder (tagorder)
 154                                         )",
 155         marc_tag_structure => "(
 156                                         tagfield char(3) NOT NULL default '',
 157                                         liblibrarian char(255) NOT NULL default '',
 158                                         libopac char(255) NOT NULL default '',
 159                                         repeatable tinyint(4) NOT NULL default '0',
 160                                         mandatory tinyint(4) NOT NULL default '0',
 161                                         PRIMARY KEY  (tagfield)
 162                                         )",
 163         marc_word => "(
 164                                 bibid bigint(20) NOT NULL default '0',
 165                                 tag char(3) NOT NULL default '',
 166                                 tagorder tinyint(4) NOT NULL default '1',
 167                                 subfieldid char(1) NOT NULL default '',
 168                                 subfieldorder tinyint(4) NOT NULL default '1',
 169                                 word varchar(255) NOT NULL default '',
 170                                 sndx_word varchar(255) NOT NULL default '',
 171                                 KEY bibid (bibid),
 172                                 KEY tag (tag),
 173                                 KEY tagorder (tagorder),
 174                                 KEY subfieldid (subfieldid),
 175                                 KEY subfieldorder (subfieldorder),
 176                                 KEY word (word),
 177                                 KEY sndx_word (sndx_word)
 178                         )",
 179         marc_breeding =>"( file varchar(80) NOT NULL default '',
 180                                                                 isbn varchar(10) NOT NULL default '',
 181                                                                 marc text NOT NULL,
 182                                                                 PRIMARY KEY  (isbn)
 183                                                                 )",
 184         );
 185
 186
 187 my %requirefields=(
 188         biblio=>{ 'abstract' => 'text' },
 189         deletedbiblio=>{ 'abstract' => 'text' },
 190         biblioitems=>{ 'lccn' => 'char(25)',
 191                                                 'url' => 'varchar(255)',
 192                                                 'marc' => 'text' },
 193         deletedbiblioitems=>{ 'lccn' => 'char(25)',
 194                                                                 'url' => 'varchar(255)',
 195                                                                 'marc' => 'text' },
 196         branchtransfers=>{ 'datearrived' => 'datetime' },
 197         statistics=>{'borrowernumber' =>'int(11)'},
 198         aqbooksellers=>{'invoicedisc' =>'float(6,4)',
 199                                                 'nocalc' => 'int(11)'},
 200         borrowers=>{'userid' => 'char(30)',
 201                                         'password' => 'char(30)',},
 202         aqorders=>{'budgetdate' => 'date'},
 203         #added so that reference items are not available for reserves...
 204         itemtypes=>{'notforloan' => 'smallint(6)'},
 205         systempreferences =>{'explanation' => 'char(80)'},
 206 );
 207
 208 my %dropable_table=(
 209                     classification   =>'classification',
 210                     multipart        =>'multipart',
 211                     multivolume      =>'multivolume',
 212                     newitems         =>'newitems',
 213                     procedures       =>'procedures',
 214                     publisher        =>'publisher',
 215                     searchstats      =>'searchstats',
 216                     serialissues     =>'serialissues',
 217                     );
 218
 219 # Default system preferences
 220 my %defaultprefs=(
 221                 'autoMemberNum'=> ['1','1 or else. If 1, Barcode is auto-calculated'],
 222                 'acquisitions'=> ['simple','normal or simple : will use acquisition system found in directory acqui.simple or acquisition'],
 223                 'template' => ['default','template default name'],
 224                 'autoBarcode' => ['0','1 or else. If 1, Barcode is auto-calculated'],
 225                 'insecure' => ['no','if YES, no auth at all is needed. Be careful if you set this to yes !']
 226                   );
 227
 228
 229 # Start checking
 230
 231 # Get version of MySQL database engine.
 232 my $mysqlversion=`mysqld --version`;
 233 $mysqlversion=~/Ver (\S*) /;
 234 $mysqlversion=$1;
 235 if ($mysqlversion ge '3.23') {
 236         print "Could convert to MyISAM database tables...\n";
 237 }
 238
 239 #---------------------------------
 240 # Tables
 241
 242 # Collect all tables into a list
 243 $sth=$dbh->prepare("show tables");
 244 $sth->execute;
 245 while (my ($table) = $sth->fetchrow) {
 246     $existingtables{$table}=1;
 247 }
 248
 249 # Now add any missing tables
 250 foreach $table ( keys %requiretables ) {
 251         print "Checking $table table...\n" if $debug;
 252         unless ($existingtables{$table} ) {
 253                 print "Adding $table table...\n";
 254                 my $sth=$dbh->prepare(
 255                         "create table $table $requiretables{$table}" );
 256                 $sth->execute;
 257                 if ($sth->err) {
 258                                 print "Error : $sth->errstr \n";
 259                                 $sth->finish;
 260                 } # if error
 261         } # unless exists
 262 } # foreach
 263
 264 # now drop useless tables
 265 foreach $table ( keys %dropable_table) {
 266         print "Dropping unused tables...\n" if $debug;
 267         if ($existingtables{$table} ) {
 268                 $dbh->do("drop table $table");
 269                 if ($dbh->err) {
 270                 print "Error : $dbh->errstr \n";
 271                 }
 272         }
 273 }
 274 unless ($existingtables{'z3950servers'}) {
 275         print "Adding z3950servers table...\n";
 276         my $sti=$dbh->prepare("create table z3950servers (
 277                                                                                 host char(255),
 278                                                                                 port int,
 279                                                                                 db char(255),
 280                                                                                 userid char(255),
 281                                                                                 password char(255),
 282                                                                                 name text,
 283                                                                                 id int,
 284                                                                                 checked smallint,
 285                                                                                 rank int)");
 286         $sti->execute;
 287         $sti=$dbh->prepare("insert into z3950servers
 288                                                                 values ('z3950.loc.gov',
 289                                                                 7090,
 290                                                                 'voyager',
 291                                                                 '', '',
 292                                                                 'Library of Congress',
 293                                                                 1, 1, 1)");
 294         $sti->execute;
 295 }
 296
 297 #---------------------------------
 298 # Columns
 299
 300 foreach $table ( keys %requirefields ) {
 301         print "Check table $table\n" if $debug;
 302         $sth=$dbh->prepare("show columns from $table");
 303         $sth->execute();
 304         undef %types;
 305         while ( ($column, $type, $null, $key, $default, $extra)
 306                         = $sth->fetchrow) {
 307                 $types{$column}=$type;
 308         } # while
 309         foreach $column ( keys %{ $requirefields{$table} } )  {
 310                 print "  Check column $column\n" if $debug;
 311                 if ( ! $types{$column} ) {
 312                 # column doesn't exist
 313                 print "Adding $column field to $table table...\n";
 314                 $query="alter table $table
 315                         add column $column " . $requirefields{$table}->{$column} ;
 316                 print "Execute: $query\n" if $debug;
 317                 my $sti=$dbh->prepare($query);
 318                 $sti->execute;
 319                         if ($sti->err) {
 320                                         print "**Error : $sti->errstr \n";
 321                                         $sti->finish;
 322                         } # if error
 323                 } # if column
 324         } # foreach column
 325 } # foreach table
 326
 327 # Get list of columns from items table
 328 my %itemtypes;
 329
 330 $sth=$dbh->prepare("show columns from items");
 331 $sth->execute;
 332 while (my ($column, $type, $null, $key, $default, $extra) = $sth->fetchrow) {
 333         $itemtypes{$column}=$type;
 334 }
 335
 336 unless ($itemtypes{'barcode'} eq 'varchar(20)') {
 337         $itemtypes{'barcode'}=~/varchar\((\d+)\)/;
 338         my $oldlength=$1;
 339         if ($oldlength<20) {
 340                 print "Setting maximum barcode length to 20 (was $oldlength).\n";
 341                 my $sti=$dbh->prepare("alter table items change barcode barcode varchar(20) not null");
 342                 $sti->execute;
 343         }
 344 }
 345
 346 # extending the timestamp in branchtransfers...
 347 my %branchtransfers;
 348
 349 $sth=$dbh->prepare("show columns from branchtransfers");
 350 $sth->execute;
 351 while (my ($column, $type, $null, $key, $default, $extra) = $sth->fetchrow) {
 352     $branchtransfers{$column}=$type;
 353 }
 354
 355 unless ($branchtransfers{'datesent'} eq 'datetime') {
 356     print "Setting type of datesent in branchtransfers to datetime.\n";
 357     my $sti=$dbh->prepare("alter table branchtransfers change datesent datesent datetime");
 358     $sti->execute;
 359 }
 360
 361 unless ($branchtransfers{'datearrived'} eq 'datetime') {
 362     print "Setting type of datearrived in branchtransfers to datetime.\n";
 363     my $sti=$dbh->prepare("alter table branchtransfers change datearrived datearrived datetime");
 364     $sti->execute;
 365 }
 366
 367 # changing the branchcategories table around...
 368 my %branchcategories;
 369
 370 $sth=$dbh->prepare("show columns from branchcategories");
 371 $sth->execute;
 372 while (my ($column, $type, $null, $key, $default, $extra) = $sth->fetchrow) {
 373         $branchcategories{$column}=$type;
 374 }
 375
 376 unless ($branchcategories{'categorycode'} eq 'varchar(4)') {
 377         print "Setting type of categorycode in branchcategories to varchar(4),\n and making the primary key.\n";
 378         my $sti=$dbh->prepare("alter table branchcategories change categorycode categorycode varchar(4) not null");
 379         $sti->execute;
 380         $sti=$dbh->prepare("alter table branchcategories add primary key (categorycode)");
 381         $sti->execute;
 382 }
 383
 384 unless ($branchcategories{'categoryname'} eq 'text') {
 385         print "Changing branchcode in branchcategories to categoryname text.\n";
 386         my $sth=$dbh->prepare("alter table branchcategories change branchcode categoryname text");
 387         $sth->execute;
 388 }
 389
 390 unless ($branchcategories{'codedescription'} eq 'text') {
 391         print "Replacing branchholding in branchcategories with codedescription text.\n";
 392         my $sth=$dbh->prepare("alter table branchcategories change branchholding codedescription text");
 393         $sth->execute;
 394 }
 395
 396
 397 # Populate systempreferences if it is empty
 398
 399 foreach $prefitem ( keys %defaultprefs ) {
 400         $sth=$dbh->prepare("select value
 401                                                                 from systempreferences
 402                                                                 where variable=?");
 403         $sth->execute($prefitem);
 404         unless ($sth->rows) {
 405                 print "Adding system preference item $prefitem with value " .$defaultprefs{$prefitem}[0] ."\n";
 406                 $sti=$dbh->prepare("insert into systempreferences (variable, value,explanation) values (?,?,?)");
 407                 $sti->execute($prefitem,$defaultprefs{$prefitem}[0],$defaultprefs{$prefitem}[1]);
 408         } # unless
 409 } # foreach
 410
 411
 412 $sth->finish;
 413
 414 exit;
 415
 416 # $Log$
 417 # Revision 1.22  2002/10/15 10:08:19  tipaul
 418 # fixme corrected, re-indent and adding the marc_breeding table (see commit of marcimport.pl for more explanations about breeding)
 419 #
 420 # Revision 1.21  2002/10/14 11:48:59  tipaul
 421 # bugfix
 422 #
 423 # Revision 1.20  2002/10/10 04:49:41  arensb
 424 # Added some FIXME comments.
 425 #
 426 # Revision 1.19  2002/10/05 10:17:17  arensb
 427 # Merged with arensb-context branch: use C4::Context->dbh instead of
 428 # &C4Connect, and generally prefer C4::Context over C4::Database.
 429 #
 430 # Revision 1.18.2.2  2002/10/05 06:18:43  arensb
 431 # Added a whole mess of FIXME comments.
 432 #
 433 # Revision 1.18.2.1  2002/10/04 02:46:00  arensb
 434 # Use C4::Connect instead of C4::Database, C4::Connect->dbh instead
 435 # C4Connect.
 436 #
 437 # Revision 1.18  2002/09/24 13:50:55  tipaul
 438 # long WAS the road to 1.3.0...
 439 # coming VERY SOON NOW...
 440 # modifying installer and buildrelease to update the DB
 441 #
 442 # Revision 1.17  2002/09/24 12:57:35  tipaul
 443 # long WAS the road to 1.3.0...
 444 # coming VERY SOON NOW...
 445 # modifying installer and buildrelease to update the DB
 446 #
 447 # Revision 1.16  2002/07/31 02:34:27  finlayt
 448 #
 449 # added "notforloan" field to the itemtypes table.
 450 #
 451 # Revision 1.15  2002/07/20 22:30:06  rangi
 452 # Making sure fix makes it into the main branch as well
 453 # Fix for bug 69
 454 #
 455 # Revision 1.14  2002/07/08 16:20:26  tonnesen
 456 # Added sessionqueries table and password/userid fields to borrowers table
 457 #
 458 # Revision 1.13  2002/07/04 18:05:36  tonnesen
 459 # bug fix
 460 #
 461 # Revision 1.12  2002/07/04 16:41:06  tonnesen
 462 # Merged changes from rel-1-2.  Abstracted table structure changes by alan.
 463 #