6 # This script checks for required updates to the database.
8 # Part of the Koha Library Software www.koha.org
9 # Licensed under the GPL.
12 # - Would also be a good idea to offer to do a backup at this time...
14 # NOTE: If you do something more than once in here, make it table driven.
23 # FIXME - The user might be installing a new database, so can't rely
24 # on /etc/koha.conf anyway.
31 %existingtables, # tables already in database
35 $type, $null, $key, $default, $extra,
36 $prefitem, # preference item in systempreferences table
43 my $dbh = C4::Context->dbh;
44 print "connected to your DB. Checking & modifying it\n" unless $silent;
49 # Tables to add if they don't exist
51 categorytable => "(categorycode char(5) NOT NULL default '',
52 description text default '',
53 itemtypecodes text default '',
54 PRIMARY KEY (categorycode)
56 subcategorytable => "(subcategorycode char(5) NOT NULL default '',
57 description text default '',
58 itemtypecodes text default '',
59 PRIMARY KEY (subcategorycode)
61 mediatypetable => "(mediatypecode char(5) NOT NULL default '',
62 description text default '',
63 itemtypecodes text default '',
64 PRIMARY KEY (mediatypecode)
67 `timestamp` TIMESTAMP NOT NULL ,
68 `user` INT( 11 ) NOT NULL ,
69 `module` TEXT default '',
70 `action` TEXT default '' ,
71 `object` INT(11) default '' ,
72 `info` TEXT default '' ,
73 PRIMARY KEY ( `timestamp` , `user` )
76 module varchar(20) NOT NULL default '',
77 code varchar(20) NOT NULL default '',
78 name varchar(100) NOT NULL default '',
79 title varchar(200) NOT NULL default '',
81 PRIMARY KEY (module,code)
86 # tablename => { 'field' => 'fieldtype' },
89 my %dropable_table = (
90 # tablename => 'tablename',
94 # tablename => "field1,field2",
96 # the other hash contains other actions that can't be done elsewhere. they are done
97 # either BEFORE of AFTER everything else, depending on "when" entry (default => AFTER)
99 # The tabledata hash contains data that should be in the tables.
100 # The uniquefieldrequired hash entry is used to determine which (if any) fields
101 # must not exist in the table for this row to be inserted. If the
102 # uniquefieldrequired entry is already in the table, the existing data is not
103 # modified, unless the forceupdate hash entry is also set. Fields in the
104 # anonymous "forceupdate" hash will be forced to be updated to the default
105 # values given in the %tabledata hash.
109 # { uniquefielrequired => 'fieldname', # the primary key in the table
110 # fieldname => fieldvalue,
111 # fieldname2 => fieldvalue2,
114 systempreferences => [
116 uniquefieldrequired => 'variable',
117 variable => 'Activate_Log',
119 forceupdate => { 'explanation' => 1,
121 explanation => 'Turn Log Actions on DB On an Off',
125 uniquefieldrequired => 'variable',
126 variable => 'ReturnBeforeExpiry',
128 forceupdate => { 'explanation' => 1,
130 explanation => 'If Yes, Returndate on issuing can\'t be after borrower card expiry',
137 my %fielddefinitions = (
139 # { field => 'fieldname',
140 # type => 'fieldtype',
153 # Get version of MySQL database engine.
154 my $mysqlversion = `mysqld --version`;
155 $mysqlversion =~ /Ver (\S*) /;
157 if ( $mysqlversion ge '3.23' ) {
158 print "Could convert to MyISAM database tables...\n" unless $silent;
161 #---------------------------------
164 # Collect all tables into a list
165 $sth = $dbh->prepare("show tables");
167 while ( my ($table) = $sth->fetchrow ) {
168 $existingtables{$table} = 1;
172 # Now add any missing tables
173 foreach $table ( keys %requiretables ) {
174 unless ( $existingtables{$table} ) {
175 print "Adding $table table...\n" unless $silent;
176 my $sth = $dbh->prepare("create table $table $requiretables{$table}");
179 print "Error : $sth->errstr \n";
185 # now drop useless tables
186 foreach $table ( keys %dropable_table ) {
187 if ( $existingtables{$table} ) {
188 print "Dropping unused table $table\n" if $debug and not $silent;
189 $dbh->do("drop table $table");
191 print "Error : $dbh->errstr \n";
196 #---------------------------------
199 foreach $table ( keys %requirefields ) {
200 print "Check table $table\n" if $debug and not $silent;
201 $sth = $dbh->prepare("show columns from $table");
204 while ( ( $column, $type, $null, $key, $default, $extra ) = $sth->fetchrow )
206 $types{$column} = $type;
208 foreach $column ( keys %{ $requirefields{$table} } ) {
209 print " Check column $column [$types{$column}]\n" if $debug and not $silent;
210 if ( !$types{$column} ) {
212 # column doesn't exist
213 print "Adding $column field to $table table...\n" unless $silent;
214 $query = "alter table $table
215 add column $column " . $requirefields{$table}->{$column};
216 print "Execute: $query\n" if $debug;
217 my $sti = $dbh->prepare($query);
220 print "**Error : $sti->errstr \n";
227 foreach $table ( keys %fielddefinitions ) {
228 print "Check table $table\n" if $debug;
229 $sth = $dbh->prepare("show columns from $table");
232 while ( ( $column, $type, $null, $key, $default, $extra ) = $sth->fetchrow )
234 $definitions->{$column}->{type} = $type;
235 $definitions->{$column}->{null} = $null;
236 $definitions->{$column}->{key} = $key;
237 $definitions->{$column}->{default} = $default;
238 $definitions->{$column}->{extra} = $extra;
240 my $fieldrow = $fielddefinitions{$table};
241 foreach my $row (@$fieldrow) {
242 my $field = $row->{field};
243 my $type = $row->{type};
244 my $null = $row->{null};
245 my $key = $row->{key};
246 my $default = $row->{default};
247 $default="''" unless $default;
248 my $extra = $row->{extra};
249 my $def = $definitions->{$field};
250 unless ( $type eq $def->{type}
251 && $null eq $def->{null}
252 && $key eq $def->{key}
253 && $default eq $def->{default}
254 && $extra eq $def->{extra} )
260 if ( $key eq 'PRI' ) {
261 $key = 'PRIMARY KEY';
263 unless ( $extra eq 'auto_increment' ) {
266 # if it's a new column use "add", if it's an old one, use "change".
268 if ($definitions->{$field}->{type}) {
269 $action="change $field"
273 # if it's a primary key, drop the previous pk, before altering the table
275 if ($key ne 'PRIMARY KEY') {
276 $sth =$dbh->prepare("alter table $table $action $field $type $null $key $extra default ?");
278 $sth =$dbh->prepare("alter table $table drop primary key, $action $field $type $null $key $extra default ?");
280 $sth->execute($default);
281 print " Alter $field in $table\n" unless $silent;
287 # Populate tables with required data
288 foreach my $table ( keys %tabledata ) {
289 print "Checking for data required in table $table...\n" unless $silent;
290 my $tablerows = $tabledata{$table};
291 foreach my $row (@$tablerows) {
292 my $uniquefieldrequired = $row->{uniquefieldrequired};
293 my $uniquevalue = $row->{$uniquefieldrequired};
294 my $forceupdate = $row->{forceupdate};
297 "select $uniquefieldrequired from $table where $uniquefieldrequired=?"
299 $sth->execute($uniquevalue);
301 foreach my $field (keys %$forceupdate) {
302 if ($forceupdate->{$field}) {
303 my $sth=$dbh->prepare("update systempreferences set $field=? where $uniquefieldrequired=?");
304 $sth->execute($row->{$field}, $uniquevalue);
308 print "Adding row to $table: " unless $silent;
312 foreach my $field ( keys %$row ) {
313 next if $field eq 'uniquefieldrequired';
314 next if $field eq 'forceupdate';
315 my $value = $row->{$field};
316 push @values, $value;
317 print " $field => $value" unless $silent;
318 $fieldlist .= "$field,";
319 $placeholders .= "?,";
321 print "\n" unless $silent;
322 $fieldlist =~ s/,$//;
323 $placeholders =~ s/,$//;
326 "insert into $table ($fieldlist) values ($placeholders)");
327 $sth->execute(@values);
332 # at last, remove useless fields
333 foreach $table ( keys %uselessfields ) {
334 my @fields = split /,/,$uselessfields{$table};
337 foreach my $fieldtodrop (@fields) {
338 $fieldtodrop =~ s/\t//g;
339 $fieldtodrop =~ s/\n//g;
341 $sth = $dbh->prepare("show columns from $table");
343 while ( my ( $column, $type, $null, $key, $default, $extra ) = $sth->fetchrow )
345 $exists =1 if ($column eq $fieldtodrop);
348 print "deleting $fieldtodrop field in $table...\n" unless $silent;
349 my $sth = $dbh->prepare("alter table $table drop $fieldtodrop");
361 # Revision 1.115 2005/08/02 16:15:34 tipaul
362 # adding 2 fields to letter system :
363 # * module (acquisition, catalogue...) : it will be usefull to show the librarian only letters he may be interested by.
364 # * title, that will be used as mail subject.
366 # Revision 1.114 2005/07/28 15:10:13 tipaul
367 # Introducing new "Letters" system : Letters will be used everytime you want to sent something to someone (through mail or paper). For example, sending a mail for overdues use letter that you can put as parameters. Sending a mail to a borrower when a suggestion is validated uses a letter too.
368 # the letter table contains 3 fields :
369 # * code => the code of the letter
370 # * name => the complete name of the letter
371 # * content => the complete text. It's a TEXT field type, so has no limits.
373 # My next goal now is to work on point 2-I "serial issue alert"
374 # With this feature, in serials, a user can subscribe the "issue alert". For every issue arrived/missing, a mail is sent to all subscribers of this list. The mail warns the user that the issue is arrive or missing. Will be in head.
375 # (see mail on koha-devel, 2005/04/07)
377 # The "serial issue alert" will be the 1st to use this letter system that probably needs some tweaking ;-)
379 # Once it will be stabilised default letters (in any languages) could be added during installer to help the library begin with this new feature.
381 # Revision 1.113 2005/07/28 08:38:41 tipaul
382 # For instance, the return date does not rely on the borrower expiration date. A systempref will be added in Koha, to modify return date calculation schema :
383 # * ReturnBeforeExpiry = yes => return date can't be after expiry date
384 # * ReturnBeforeExpiry = no => return date can be after expiry date
386 # Revision 1.112 2005/07/26 08:19:47 hdl
387 # Adding IndependantBranches System preference variable in order to manage Branch independancy.
389 # Revision 1.111 2005/07/25 15:35:38 tipaul
390 # we have decided that moving to Koha 3.0 requires being already in Koha 2.2.x
391 # So, the updatedatabase script can highly be cleaned (90% removed).
392 # Let's play with the new Koha DB structure now ;-)
399 # This script checks for required updates to the database.
401 # Part of the Koha Library Software www.koha.org
402 # Licensed under the GPL.
405 # - Would also be a good idea to offer to do a backup at this time...
407 # NOTE: If you do something more than once in here, make it table driven.
416 # FIXME - The user might be installing a new database, so can't rely
417 # on /etc/koha.conf anyway.
424 %existingtables, # tables already in database
428 $type, $null, $key, $default, $extra,
429 $prefitem, # preference item in systempreferences table
436 my $dbh = C4::Context->dbh;
437 print "connected to your DB. Checking & modifying it\n" unless $silent;
442 # Tables to add if they don't exist
443 my %requiretables = (
444 categorytable => "(categorycode char(5) NOT NULL default '',
445 description text default '',
446 itemtypecodes text default '',
447 PRIMARY KEY (categorycode)
449 subcategorytable => "(subcategorycode char(5) NOT NULL default '',
450 description text default '',
451 itemtypecodes text default '',
452 PRIMARY KEY (subcategorycode)
454 mediatypetable => "(mediatypecode char(5) NOT NULL default '',
455 description text default '',
456 itemtypecodes text default '',
457 PRIMARY KEY (mediatypecode)
460 `timestamp` TIMESTAMP NOT NULL ,
461 `user` INT( 11 ) NOT NULL ,
462 `module` TEXT default '',
463 `action` TEXT default '' ,
464 `object` INT(11) default '' ,
465 `info` TEXT default '' ,
466 PRIMARY KEY ( `timestamp` , `user` )
470 my %requirefields = (
471 # tablename => { 'field' => 'fieldtype' },
474 my %dropable_table = (
475 # tablename => 'tablename',
478 my %uselessfields = (
479 # tablename => "field1,field2",
481 # the other hash contains other actions that can't be done elsewhere. they are done
482 # either BEFORE of AFTER everything else, depending on "when" entry (default => AFTER)
484 # The tabledata hash contains data that should be in the tables.
485 # The uniquefieldrequired hash entry is used to determine which (if any) fields
486 # must not exist in the table for this row to be inserted. If the
487 # uniquefieldrequired entry is already in the table, the existing data is not
488 # modified, unless the forceupdate hash entry is also set. Fields in the
489 # anonymous "forceupdate" hash will be forced to be updated to the default
490 # values given in the %tabledata hash.
494 # { uniquefielrequired => 'fieldname', # the primary key in the table
495 # fieldname => fieldvalue,
496 # fieldname2 => fieldvalue2,
499 systempreferences => [
501 uniquefieldrequired => 'variable',
502 variable => 'Activate_Log',
504 forceupdate => { 'explanation' => 1,
506 explanation => 'Turn Log Actions on DB On an Off',
510 uniquefieldrequired => 'variable',
511 variable => 'IndependantBranches',
513 forceupdate => { 'explanation' => 1,
515 explanation => 'Turn Branch independancy management On an Off',
523 my %fielddefinitions = (
525 # { field => 'fieldname',
526 # type => 'fieldtype',
539 # Get version of MySQL database engine.
540 my $mysqlversion = `mysqld --version`;
541 $mysqlversion =~ /Ver (\S*) /;
543 if ( $mysqlversion ge '3.23' ) {
544 print "Could convert to MyISAM database tables...\n" unless $silent;
547 #---------------------------------
550 # Collect all tables into a list
551 $sth = $dbh->prepare("show tables");
553 while ( my ($table) = $sth->fetchrow ) {
554 $existingtables{$table} = 1;
558 # Now add any missing tables
559 foreach $table ( keys %requiretables ) {
560 unless ( $existingtables{$table} ) {
561 print "Adding $table table...\n" unless $silent;
562 my $sth = $dbh->prepare("create table $table $requiretables{$table}");
565 print "Error : $sth->errstr \n";
571 # now drop useless tables
572 foreach $table ( keys %dropable_table ) {
573 if ( $existingtables{$table} ) {
574 print "Dropping unused table $table\n" if $debug and not $silent;
575 $dbh->do("drop table $table");
577 print "Error : $dbh->errstr \n";
582 #---------------------------------
585 foreach $table ( keys %requirefields ) {
586 print "Check table $table\n" if $debug and not $silent;
587 $sth = $dbh->prepare("show columns from $table");
590 while ( ( $column, $type, $null, $key, $default, $extra ) = $sth->fetchrow )
592 $types{$column} = $type;
594 foreach $column ( keys %{ $requirefields{$table} } ) {
595 print " Check column $column [$types{$column}]\n" if $debug and not $silent;
596 if ( !$types{$column} ) {
598 # column doesn't exist
599 print "Adding $column field to $table table...\n" unless $silent;
600 $query = "alter table $table
601 add column $column " . $requirefields{$table}->{$column};
602 print "Execute: $query\n" if $debug;
603 my $sti = $dbh->prepare($query);
606 print "**Error : $sti->errstr \n";
613 foreach $table ( keys %fielddefinitions ) {
614 print "Check table $table\n" if $debug;
615 $sth = $dbh->prepare("show columns from $table");
618 while ( ( $column, $type, $null, $key, $default, $extra ) = $sth->fetchrow )
620 $definitions->{$column}->{type} = $type;
621 $definitions->{$column}->{null} = $null;
622 $definitions->{$column}->{key} = $key;
623 $definitions->{$column}->{default} = $default;
624 $definitions->{$column}->{extra} = $extra;
626 my $fieldrow = $fielddefinitions{$table};
627 foreach my $row (@$fieldrow) {
628 my $field = $row->{field};
629 my $type = $row->{type};
630 my $null = $row->{null};
631 my $key = $row->{key};
632 my $default = $row->{default};
633 $default="''" unless $default;
634 my $extra = $row->{extra};
635 my $def = $definitions->{$field};
636 unless ( $type eq $def->{type}
637 && $null eq $def->{null}
638 && $key eq $def->{key}
639 && $default eq $def->{default}
640 && $extra eq $def->{extra} )
646 if ( $key eq 'PRI' ) {
647 $key = 'PRIMARY KEY';
649 unless ( $extra eq 'auto_increment' ) {
652 # if it's a new column use "add", if it's an old one, use "change".
654 if ($definitions->{$field}->{type}) {
655 $action="change $field"
659 # if it's a primary key, drop the previous pk, before altering the table
661 if ($key ne 'PRIMARY KEY') {
662 $sth =$dbh->prepare("alter table $table $action $field $type $null $key $extra default ?");
664 $sth =$dbh->prepare("alter table $table drop primary key, $action $field $type $null $key $extra default ?");
666 $sth->execute($default);
667 print " Alter $field in $table\n" unless $silent;
673 # Populate tables with required data
674 foreach my $table ( keys %tabledata ) {
675 print "Checking for data required in table $table...\n" unless $silent;
676 my $tablerows = $tabledata{$table};
677 foreach my $row (@$tablerows) {
678 my $uniquefieldrequired = $row->{uniquefieldrequired};
679 my $uniquevalue = $row->{$uniquefieldrequired};
680 my $forceupdate = $row->{forceupdate};
683 "select $uniquefieldrequired from $table where $uniquefieldrequired=?"
685 $sth->execute($uniquevalue);
687 foreach my $field (keys %$forceupdate) {
688 if ($forceupdate->{$field}) {
689 my $sth=$dbh->prepare("update systempreferences set $field=? where $uniquefieldrequired=?");
690 $sth->execute($row->{$field}, $uniquevalue);
694 print "Adding row to $table: " unless $silent;
698 foreach my $field ( keys %$row ) {
699 next if $field eq 'uniquefieldrequired';
700 next if $field eq 'forceupdate';
701 my $value = $row->{$field};
702 push @values, $value;
703 print " $field => $value" unless $silent;
704 $fieldlist .= "$field,";
705 $placeholders .= "?,";
707 print "\n" unless $silent;
708 $fieldlist =~ s/,$//;
709 $placeholders =~ s/,$//;
712 "insert into $table ($fieldlist) values ($placeholders)");
713 $sth->execute(@values);
718 # at last, remove useless fields
719 foreach $table ( keys %uselessfields ) {
720 my @fields = split /,/,$uselessfields{$table};
723 foreach my $fieldtodrop (@fields) {
724 $fieldtodrop =~ s/\t//g;
725 $fieldtodrop =~ s/\n//g;
727 $sth = $dbh->prepare("show columns from $table");
729 while ( my ( $column, $type, $null, $key, $default, $extra ) = $sth->fetchrow )
731 $exists =1 if ($column eq $fieldtodrop);
734 print "deleting $fieldtodrop field in $table...\n" unless $silent;
735 my $sth = $dbh->prepare("alter table $table drop $fieldtodrop");
747 # Revision 1.115 2005/08/02 16:15:34 tipaul
748 # adding 2 fields to letter system :
749 # * module (acquisition, catalogue...) : it will be usefull to show the librarian only letters he may be interested by.
750 # * title, that will be used as mail subject.
752 # Revision 1.114 2005/07/28 15:10:13 tipaul
753 # Introducing new "Letters" system : Letters will be used everytime you want to sent something to someone (through mail or paper). For example, sending a mail for overdues use letter that you can put as parameters. Sending a mail to a borrower when a suggestion is validated uses a letter too.
754 # the letter table contains 3 fields :
755 # * code => the code of the letter
756 # * name => the complete name of the letter
757 # * content => the complete text. It's a TEXT field type, so has no limits.
759 # My next goal now is to work on point 2-I "serial issue alert"
760 # With this feature, in serials, a user can subscribe the "issue alert". For every issue arrived/missing, a mail is sent to all subscribers of this list. The mail warns the user that the issue is arrive or missing. Will be in head.
761 # (see mail on koha-devel, 2005/04/07)
763 # The "serial issue alert" will be the 1st to use this letter system that probably needs some tweaking ;-)
765 # Once it will be stabilised default letters (in any languages) could be added during installer to help the library begin with this new feature.
767 # Revision 1.113 2005/07/28 08:38:41 tipaul
768 # For instance, the return date does not rely on the borrower expiration date. A systempref will be added in Koha, to modify return date calculation schema :
769 # * ReturnBeforeExpiry = yes => return date can't be after expiry date
770 # * ReturnBeforeExpiry = no => return date can be after expiry date
772 # Revision 1.112 2005/07/26 08:19:47 hdl
773 # Adding IndependantBranches System preference variable in order to manage Branch independancy.
775 # Revision 1.111 2005/07/25 15:35:38 tipaul
776 # we have decided that moving to Koha 3.0 requires being already in Koha 2.2.x
777 # So, the updatedatabase script can highly be cleaned (90% removed).
778 # Let's play with the new Koha DB structure now ;-)