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                                                 authorised_value char(10) default NULL,
 133                                                 PRIMARY KEY  (tagfield,tagsubfield),
 134                                                 KEY kohafield (kohafield),
 135                                                 KEY tab (tab)
 136                                                 )",
 137         marc_subfield_table => "(
 138                                                 subfieldid bigint(20) unsigned NOT NULL auto_increment,
 139                                                 bibid bigint(20) unsigned NOT NULL default '0',
 140                                                 tag char(3) NOT NULL default '',
 141                                                 tagorder tinyint(4) NOT NULL default '1',
 142                                                 tag_indicator char(2) NOT NULL default '',
 143                                                 subfieldcode char(1) NOT NULL default '',
 144                                                 subfieldorder tinyint(4) NOT NULL default '1',
 145                                                 subfieldvalue varchar(255) default NULL,
 146                                                 valuebloblink bigint(20) default NULL,
 147                                                 PRIMARY KEY  (subfieldid),
 148                                                 KEY bibid (bibid),
 149                                                 KEY tag (tag),
 150                                                 KEY tag_indicator (tag_indicator),
 151                                                 KEY subfieldorder (subfieldorder),
 152                                                 KEY subfieldcode (subfieldcode),
 153                                                 KEY subfieldvalue (subfieldvalue),
 154                                                 KEY tagorder (tagorder)
 155                                         )",
 156         marc_tag_structure => "(
 157                                         tagfield char(3) NOT NULL default '',
 158                                         liblibrarian char(255) NOT NULL default '',
 159                                         libopac char(255) NOT NULL default '',
 160                                         repeatable tinyint(4) NOT NULL default '0',
 161                                         mandatory tinyint(4) NOT NULL default '0',
 162                                         PRIMARY KEY  (tagfield)
 163                                         )",
 164         marc_word => "(
 165                                 bibid bigint(20) NOT NULL default '0',
 166                                 tag char(3) NOT NULL default '',
 167                                 tagorder tinyint(4) NOT NULL default '1',
 168                                 subfieldid char(1) NOT NULL default '',
 169                                 subfieldorder tinyint(4) NOT NULL default '1',
 170                                 word varchar(255) NOT NULL default '',
 171                                 sndx_word varchar(255) NOT NULL default '',
 172                                 KEY bibid (bibid),
 173                                 KEY tag (tag),
 174                                 KEY tagorder (tagorder),
 175                                 KEY subfieldid (subfieldid),
 176                                 KEY subfieldorder (subfieldorder),
 177                                 KEY word (word),
 178                                 KEY sndx_word (sndx_word)
 179                         )",
 180         marc_breeding =>"( file varchar(80) NOT NULL default '',
 181                                                                 isbn varchar(10) NOT NULL default '',
 182                                                                 marc text NOT NULL,
 183                                                                 PRIMARY KEY  (isbn)
 184                                                                 )",
 185         authorised_values => "(id int(11) NOT NULL auto_increment,
 186                                                                         category char(10) NOT NULL default '',
 187                                                                         authorised_value char(80) NOT NULL default '',
 188                                                                         PRIMARY KEY  (id),
 189                                                                         KEY name (category)
 190                                                                 )";
 191         );
 192
 193
 194 my %requirefields=(
 195         biblio=>{ 'abstract' => 'text' },
 196         deletedbiblio=>{ 'abstract' => 'text' },
 197         biblioitems=>{ 'lccn' => 'char(25)',
 198                                                 'url' => 'varchar(255)',
 199                                                 'marc' => 'text' },
 200         deletedbiblioitems=>{ 'lccn' => 'char(25)',
 201                                                                 'url' => 'varchar(255)',
 202                                                                 'marc' => 'text' },
 203         branchtransfers=>{ 'datearrived' => 'datetime' },
 204         statistics=>{'borrowernumber' =>'int(11)'},
 205         aqbooksellers=>{'invoicedisc' =>'float(6,4)',
 206                                                 'nocalc' => 'int(11)'},
 207         borrowers=>{'userid' => 'char(30)',
 208                                         'password' => 'char(30)',},
 209         aqorders=>{'budgetdate' => 'date'},
 210         #added so that reference items are not available for reserves...
 211         itemtypes=>{'notforloan' => 'smallint(6)'},
 212         systempreferences =>{'explanation' => 'char(80)'},
 213 );
 214
 215 my %dropable_table=(
 216                     classification   =>'classification',
 217                     multipart        =>'multipart',
 218                     multivolume      =>'multivolume',
 219                     newitems         =>'newitems',
 220                     procedures       =>'procedures',
 221                     publisher        =>'publisher',
 222                     searchstats      =>'searchstats',
 223                     serialissues     =>'serialissues',
 224                     );
 225
 226 # Default system preferences
 227 my %defaultprefs=(
 228                 'autoMemberNum'=> ['1','1 or else. If 1, Barcode is auto-calculated'],
 229                 'acquisitions'=> ['simple','normal or simple : will use acquisition system found in directory acqui.simple or acquisition'],
 230                 'template' => ['default','template default name'],
 231                 'autoBarcode' => ['0','1 or else. If 1, Barcode is auto-calculated'],
 232                 'insecure' => ['no','if YES, no auth at all is needed. Be careful if you set this to yes !']
 233                   );
 234
 235
 236 # Start checking
 237
 238 # Get version of MySQL database engine.
 239 my $mysqlversion=`mysqld --version`;
 240 $mysqlversion=~/Ver (\S*) /;
 241 $mysqlversion=$1;
 242 if ($mysqlversion ge '3.23') {
 243         print "Could convert to MyISAM database tables...\n";
 244 }
 245
 246 #---------------------------------
 247 # Tables
 248
 249 # Collect all tables into a list
 250 $sth=$dbh->prepare("show tables");
 251 $sth->execute;
 252 while (my ($table) = $sth->fetchrow) {
 253     $existingtables{$table}=1;
 254 }
 255
 256 # Now add any missing tables
 257 foreach $table ( keys %requiretables ) {
 258         print "Checking $table table...\n" if $debug;
 259         unless ($existingtables{$table} ) {
 260                 print "Adding $table table...\n";
 261                 my $sth=$dbh->prepare(
 262                         "create table $table $requiretables{$table}" );
 263                 $sth->execute;
 264                 if ($sth->err) {
 265                                 print "Error : $sth->errstr \n";
 266                                 $sth->finish;
 267                 } # if error
 268         } # unless exists
 269 } # foreach
 270
 271 # now drop useless tables
 272 foreach $table ( keys %dropable_table) {
 273         print "Dropping unused tables...\n" if $debug;
 274         if ($existingtables{$table} ) {
 275                 $dbh->do("drop table $table");
 276                 if ($dbh->err) {
 277                 print "Error : $dbh->errstr \n";
 278                 }
 279         }
 280 }
 281 unless ($existingtables{'z3950servers'}) {
 282         print "Adding z3950servers table...\n";
 283         my $sti=$dbh->prepare("create table z3950servers (
 284                                                                                 host char(255),
 285                                                                                 port int,
 286                                                                                 db char(255),
 287                                                                                 userid char(255),
 288                                                                                 password char(255),
 289                                                                                 name text,
 290                                                                                 id int,
 291                                                                                 checked smallint,
 292                                                                                 rank int)");
 293         $sti->execute;
 294         $sti=$dbh->prepare("insert into z3950servers
 295                                                                 values ('z3950.loc.gov',
 296                                                                 7090,
 297                                                                 'voyager',
 298                                                                 '', '',
 299                                                                 'Library of Congress',
 300                                                                 1, 1, 1)");
 301         $sti->execute;
 302 }
 303
 304 #---------------------------------
 305 # Columns
 306
 307 foreach $table ( keys %requirefields ) {
 308         print "Check table $table\n" if $debug;
 309         $sth=$dbh->prepare("show columns from $table");
 310         $sth->execute();
 311         undef %types;
 312         while ( ($column, $type, $null, $key, $default, $extra)
 313                         = $sth->fetchrow) {
 314                 $types{$column}=$type;
 315         } # while
 316         foreach $column ( keys %{ $requirefields{$table} } )  {
 317                 print "  Check column $column\n" if $debug;
 318                 if ( ! $types{$column} ) {
 319                 # column doesn't exist
 320                 print "Adding $column field to $table table...\n";
 321                 $query="alter table $table
 322                         add column $column " . $requirefields{$table}->{$column} ;
 323                 print "Execute: $query\n" if $debug;
 324                 my $sti=$dbh->prepare($query);
 325                 $sti->execute;
 326                         if ($sti->err) {
 327                                         print "**Error : $sti->errstr \n";
 328                                         $sti->finish;
 329                         } # if error
 330                 } # if column
 331         } # foreach column
 332 } # foreach table
 333
 334 # Get list of columns from items table
 335 my %itemtypes;
 336
 337 $sth=$dbh->prepare("show columns from items");
 338 $sth->execute;
 339 while (my ($column, $type, $null, $key, $default, $extra) = $sth->fetchrow) {
 340         $itemtypes{$column}=$type;
 341 }
 342
 343 unless ($itemtypes{'barcode'} eq 'varchar(20)') {
 344         $itemtypes{'barcode'}=~/varchar\((\d+)\)/;
 345         my $oldlength=$1;
 346         if ($oldlength<20) {
 347                 print "Setting maximum barcode length to 20 (was $oldlength).\n";
 348                 my $sti=$dbh->prepare("alter table items change barcode barcode varchar(20) not null");
 349                 $sti->execute;
 350         }
 351 }
 352
 353 # extending the timestamp in branchtransfers...
 354 my %branchtransfers;
 355
 356 $sth=$dbh->prepare("show columns from branchtransfers");
 357 $sth->execute;
 358 while (my ($column, $type, $null, $key, $default, $extra) = $sth->fetchrow) {
 359     $branchtransfers{$column}=$type;
 360 }
 361
 362 unless ($branchtransfers{'datesent'} eq 'datetime') {
 363     print "Setting type of datesent in branchtransfers to datetime.\n";
 364     my $sti=$dbh->prepare("alter table branchtransfers change datesent datesent datetime");
 365     $sti->execute;
 366 }
 367
 368 unless ($branchtransfers{'datearrived'} eq 'datetime') {
 369     print "Setting type of datearrived in branchtransfers to datetime.\n";
 370     my $sti=$dbh->prepare("alter table branchtransfers change datearrived datearrived datetime");
 371     $sti->execute;
 372 }
 373
 374 # changing the branchcategories table around...
 375 my %branchcategories;
 376
 377 $sth=$dbh->prepare("show columns from branchcategories");
 378 $sth->execute;
 379 while (my ($column, $type, $null, $key, $default, $extra) = $sth->fetchrow) {
 380         $branchcategories{$column}=$type;
 381 }
 382
 383 unless ($branchcategories{'categorycode'} eq 'varchar(4)') {
 384         print "Setting type of categorycode in branchcategories to varchar(4),\n and making the primary key.\n";
 385         my $sti=$dbh->prepare("alter table branchcategories change categorycode categorycode varchar(4) not null");
 386         $sti->execute;
 387         $sti=$dbh->prepare("alter table branchcategories add primary key (categorycode)");
 388         $sti->execute;
 389 }
 390
 391 unless ($branchcategories{'categoryname'} eq 'text') {
 392         print "Changing branchcode in branchcategories to categoryname text.\n";
 393         my $sth=$dbh->prepare("alter table branchcategories change branchcode categoryname text");
 394         $sth->execute;
 395 }
 396
 397 unless ($branchcategories{'codedescription'} eq 'text') {
 398         print "Replacing branchholding in branchcategories with codedescription text.\n";
 399         my $sth=$dbh->prepare("alter table branchcategories change branchholding codedescription text");
 400         $sth->execute;
 401 }
 402
 403
 404 # Populate systempreferences if it is empty
 405
 406 foreach $prefitem ( keys %defaultprefs ) {
 407         $sth=$dbh->prepare("select value
 408                                                                 from systempreferences
 409                                                                 where variable=?");
 410         $sth->execute($prefitem);
 411         unless ($sth->rows) {
 412                 print "Adding system preference item $prefitem with value " .$defaultprefs{$prefitem}[0] ."\n";
 413                 $sti=$dbh->prepare("insert into systempreferences (variable, value,explanation) values (?,?,?)");
 414                 $sti->execute($prefitem,$defaultprefs{$prefitem}[0],$defaultprefs{$prefitem}[1]);
 415         } # unless
 416 } # foreach
 417
 418
 419 $sth->finish;
 420
 421 exit;
 422
 423 # $Log$
 424 # Revision 1.23  2002/10/25 10:55:46  tipaul
 425 # Road to 1.3.2
 426 # * bugfixes and improvements
 427 # * manage mandatory MARC subfields
 428 # * new table : authorised_values. this table contains categories and authorised values for the category. On MARC management, you can map a subfield to a authorised_values category. If you do this, the subfield can only be filled with a authorised_value of the selected category.
 429 # this submit contains everything needed :
 430 # * updatedatabase
 431 # * admin screens
 432 # * "links" management
 433 # * creation of a html-list if a subfield is mapped to an authorised value.
 434 #
 435 # Note this is different from authorities support, which will come soon.
 436 # The authorised_values is supposed to contains a "small" number of authorised values for a category (less than 50-100). If you enter more authorised values than this, it should be hard to find what you want in a BIG list...
 437 #
 438 # Revision 1.22  2002/10/15 10:08:19  tipaul
 439 # fixme corrected, re-indent and adding the marc_breeding table (see commit of marcimport.pl for more explanations about breeding)
 440 #
 441 # Revision 1.21  2002/10/14 11:48:59  tipaul
 442 # bugfix
 443 #
 444 # Revision 1.20  2002/10/10 04:49:41  arensb
 445 # Added some FIXME comments.
 446 #
 447 # Revision 1.19  2002/10/05 10:17:17  arensb
 448 # Merged with arensb-context branch: use C4::Context->dbh instead of
 449 # &C4Connect, and generally prefer C4::Context over C4::Database.
 450 #
 451 # Revision 1.18.2.2  2002/10/05 06:18:43  arensb
 452 # Added a whole mess of FIXME comments.
 453 #
 454 # Revision 1.18.2.1  2002/10/04 02:46:00  arensb
 455 # Use C4::Connect instead of C4::Database, C4::Connect->dbh instead
 456 # C4Connect.
 457 #
 458 # Revision 1.18  2002/09/24 13:50:55  tipaul
 459 # long WAS the road to 1.3.0...
 460 # coming VERY SOON NOW...
 461 # modifying installer and buildrelease to update the DB
 462 #
 463 # Revision 1.17  2002/09/24 12:57:35  tipaul
 464 # long WAS the road to 1.3.0...
 465 # coming VERY SOON NOW...
 466 # modifying installer and buildrelease to update the DB
 467 #
 468 # Revision 1.16  2002/07/31 02:34:27  finlayt
 469 #
 470 # added "notforloan" field to the itemtypes table.
 471 #
 472 # Revision 1.15  2002/07/20 22:30:06  rangi
 473 # Making sure fix makes it into the main branch as well
 474 # Fix for bug 69
 475 #
 476 # Revision 1.14  2002/07/08 16:20:26  tonnesen
 477 # Added sessionqueries table and password/userid fields to borrowers table
 478 #
 479 # Revision 1.13  2002/07/04 18:05:36  tonnesen
 480 # bug fix
 481 #
 482 # Revision 1.12  2002/07/04 16:41:06  tonnesen
 483 # Merged changes from rel-1-2.  Abstracted table structure changes by alan.
 484 #