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 code varchar(20) NOT NULL,
77 name varchar(100) NOT NULL,
84 # tablename => { 'field' => 'fieldtype' },
87 my %dropable_table = (
88 # tablename => 'tablename',
92 # tablename => "field1,field2",
94 # the other hash contains other actions that can't be done elsewhere. they are done
95 # either BEFORE of AFTER everything else, depending on "when" entry (default => AFTER)
97 # The tabledata hash contains data that should be in the tables.
98 # The uniquefieldrequired hash entry is used to determine which (if any) fields
99 # must not exist in the table for this row to be inserted. If the
100 # uniquefieldrequired entry is already in the table, the existing data is not
101 # modified, unless the forceupdate hash entry is also set. Fields in the
102 # anonymous "forceupdate" hash will be forced to be updated to the default
103 # values given in the %tabledata hash.
107 # { uniquefielrequired => 'fieldname', # the primary key in the table
108 # fieldname => fieldvalue,
109 # fieldname2 => fieldvalue2,
112 systempreferences => [
114 uniquefieldrequired => 'variable',
115 variable => 'Activate_Log',
117 forceupdate => { 'explanation' => 1,
119 explanation => 'Turn Log Actions on DB On an Off',
123 uniquefieldrequired => 'variable',
124 variable => 'ReturnBeforeExpiry',
126 forceupdate => { 'explanation' => 1,
128 explanation => 'If Yes, Returndate on issuing can\'t be after borrower card expiry',
135 my %fielddefinitions = (
137 # { field => 'fieldname',
138 # type => 'fieldtype',
151 # Get version of MySQL database engine.
152 my $mysqlversion = `mysqld --version`;
153 $mysqlversion =~ /Ver (\S*) /;
155 if ( $mysqlversion ge '3.23' ) {
156 print "Could convert to MyISAM database tables...\n" unless $silent;
159 #---------------------------------
162 # Collect all tables into a list
163 $sth = $dbh->prepare("show tables");
165 while ( my ($table) = $sth->fetchrow ) {
166 $existingtables{$table} = 1;
170 # Now add any missing tables
171 foreach $table ( keys %requiretables ) {
172 unless ( $existingtables{$table} ) {
173 print "Adding $table table...\n" unless $silent;
174 my $sth = $dbh->prepare("create table $table $requiretables{$table}");
177 print "Error : $sth->errstr \n";
183 # now drop useless tables
184 foreach $table ( keys %dropable_table ) {
185 if ( $existingtables{$table} ) {
186 print "Dropping unused table $table\n" if $debug and not $silent;
187 $dbh->do("drop table $table");
189 print "Error : $dbh->errstr \n";
194 #---------------------------------
197 foreach $table ( keys %requirefields ) {
198 print "Check table $table\n" if $debug and not $silent;
199 $sth = $dbh->prepare("show columns from $table");
202 while ( ( $column, $type, $null, $key, $default, $extra ) = $sth->fetchrow )
204 $types{$column} = $type;
206 foreach $column ( keys %{ $requirefields{$table} } ) {
207 print " Check column $column [$types{$column}]\n" if $debug and not $silent;
208 if ( !$types{$column} ) {
210 # column doesn't exist
211 print "Adding $column field to $table table...\n" unless $silent;
212 $query = "alter table $table
213 add column $column " . $requirefields{$table}->{$column};
214 print "Execute: $query\n" if $debug;
215 my $sti = $dbh->prepare($query);
218 print "**Error : $sti->errstr \n";
225 foreach $table ( keys %fielddefinitions ) {
226 print "Check table $table\n" if $debug;
227 $sth = $dbh->prepare("show columns from $table");
230 while ( ( $column, $type, $null, $key, $default, $extra ) = $sth->fetchrow )
232 $definitions->{$column}->{type} = $type;
233 $definitions->{$column}->{null} = $null;
234 $definitions->{$column}->{key} = $key;
235 $definitions->{$column}->{default} = $default;
236 $definitions->{$column}->{extra} = $extra;
238 my $fieldrow = $fielddefinitions{$table};
239 foreach my $row (@$fieldrow) {
240 my $field = $row->{field};
241 my $type = $row->{type};
242 my $null = $row->{null};
243 my $key = $row->{key};
244 my $default = $row->{default};
245 $default="''" unless $default;
246 my $extra = $row->{extra};
247 my $def = $definitions->{$field};
248 unless ( $type eq $def->{type}
249 && $null eq $def->{null}
250 && $key eq $def->{key}
251 && $default eq $def->{default}
252 && $extra eq $def->{extra} )
258 if ( $key eq 'PRI' ) {
259 $key = 'PRIMARY KEY';
261 unless ( $extra eq 'auto_increment' ) {
264 # if it's a new column use "add", if it's an old one, use "change".
266 if ($definitions->{$field}->{type}) {
267 $action="change $field"
271 # if it's a primary key, drop the previous pk, before altering the table
273 if ($key ne 'PRIMARY KEY') {
274 $sth =$dbh->prepare("alter table $table $action $field $type $null $key $extra default ?");
276 $sth =$dbh->prepare("alter table $table drop primary key, $action $field $type $null $key $extra default ?");
278 $sth->execute($default);
279 print " Alter $field in $table\n" unless $silent;
285 # Populate tables with required data
286 foreach my $table ( keys %tabledata ) {
287 print "Checking for data required in table $table...\n" unless $silent;
288 my $tablerows = $tabledata{$table};
289 foreach my $row (@$tablerows) {
290 my $uniquefieldrequired = $row->{uniquefieldrequired};
291 my $uniquevalue = $row->{$uniquefieldrequired};
292 my $forceupdate = $row->{forceupdate};
295 "select $uniquefieldrequired from $table where $uniquefieldrequired=?"
297 $sth->execute($uniquevalue);
299 foreach my $field (keys %$forceupdate) {
300 if ($forceupdate->{$field}) {
301 my $sth=$dbh->prepare("update systempreferences set $field=? where $uniquefieldrequired=?");
302 $sth->execute($row->{$field}, $uniquevalue);
306 print "Adding row to $table: " unless $silent;
310 foreach my $field ( keys %$row ) {
311 next if $field eq 'uniquefieldrequired';
312 next if $field eq 'forceupdate';
313 my $value = $row->{$field};
314 push @values, $value;
315 print " $field => $value" unless $silent;
316 $fieldlist .= "$field,";
317 $placeholders .= "?,";
319 print "\n" unless $silent;
320 $fieldlist =~ s/,$//;
321 $placeholders =~ s/,$//;
324 "insert into $table ($fieldlist) values ($placeholders)");
325 $sth->execute(@values);
330 # at last, remove useless fields
331 foreach $table ( keys %uselessfields ) {
332 my @fields = split /,/,$uselessfields{$table};
335 foreach my $fieldtodrop (@fields) {
336 $fieldtodrop =~ s/\t//g;
337 $fieldtodrop =~ s/\n//g;
339 $sth = $dbh->prepare("show columns from $table");
341 while ( my ( $column, $type, $null, $key, $default, $extra ) = $sth->fetchrow )
343 $exists =1 if ($column eq $fieldtodrop);
346 print "deleting $fieldtodrop field in $table...\n" unless $silent;
347 my $sth = $dbh->prepare("alter table $table drop $fieldtodrop");
359 # Revision 1.114 2005/07/28 15:10:13 tipaul
360 # 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.
361 # the letter table contains 3 fields :
362 # * code => the code of the letter
363 # * name => the complete name of the letter
364 # * content => the complete text. It's a TEXT field type, so has no limits.
366 # My next goal now is to work on point 2-I "serial issue alert"
367 # 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.
368 # (see mail on koha-devel, 2005/04/07)
370 # The "serial issue alert" will be the 1st to use this letter system that probably needs some tweaking ;-)
372 # Once it will be stabilised default letters (in any languages) could be added during installer to help the library begin with this new feature.
374 # Revision 1.113 2005/07/28 08:38:41 tipaul
375 # 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 :
376 # * ReturnBeforeExpiry = yes => return date can't be after expiry date
377 # * ReturnBeforeExpiry = no => return date can be after expiry date
379 # Revision 1.112 2005/07/26 08:19:47 hdl
380 # Adding IndependantBranches System preference variable in order to manage Branch independancy.
382 # Revision 1.111 2005/07/25 15:35:38 tipaul
383 # we have decided that moving to Koha 3.0 requires being already in Koha 2.2.x
384 # So, the updatedatabase script can highly be cleaned (90% removed).
385 # Let's play with the new Koha DB structure now ;-)
392 # This script checks for required updates to the database.
394 # Part of the Koha Library Software www.koha.org
395 # Licensed under the GPL.
398 # - Would also be a good idea to offer to do a backup at this time...
400 # NOTE: If you do something more than once in here, make it table driven.
409 # FIXME - The user might be installing a new database, so can't rely
410 # on /etc/koha.conf anyway.
417 %existingtables, # tables already in database
421 $type, $null, $key, $default, $extra,
422 $prefitem, # preference item in systempreferences table
429 my $dbh = C4::Context->dbh;
430 print "connected to your DB. Checking & modifying it\n" unless $silent;
435 # Tables to add if they don't exist
436 my %requiretables = (
437 categorytable => "(categorycode char(5) NOT NULL default '',
438 description text default '',
439 itemtypecodes text default '',
440 PRIMARY KEY (categorycode)
442 subcategorytable => "(subcategorycode char(5) NOT NULL default '',
443 description text default '',
444 itemtypecodes text default '',
445 PRIMARY KEY (subcategorycode)
447 mediatypetable => "(mediatypecode char(5) NOT NULL default '',
448 description text default '',
449 itemtypecodes text default '',
450 PRIMARY KEY (mediatypecode)
453 `timestamp` TIMESTAMP NOT NULL ,
454 `user` INT( 11 ) NOT NULL ,
455 `module` TEXT default '',
456 `action` TEXT default '' ,
457 `object` INT(11) default '' ,
458 `info` TEXT default '' ,
459 PRIMARY KEY ( `timestamp` , `user` )
463 my %requirefields = (
464 # tablename => { 'field' => 'fieldtype' },
467 my %dropable_table = (
468 # tablename => 'tablename',
471 my %uselessfields = (
472 # tablename => "field1,field2",
474 # the other hash contains other actions that can't be done elsewhere. they are done
475 # either BEFORE of AFTER everything else, depending on "when" entry (default => AFTER)
477 # The tabledata hash contains data that should be in the tables.
478 # The uniquefieldrequired hash entry is used to determine which (if any) fields
479 # must not exist in the table for this row to be inserted. If the
480 # uniquefieldrequired entry is already in the table, the existing data is not
481 # modified, unless the forceupdate hash entry is also set. Fields in the
482 # anonymous "forceupdate" hash will be forced to be updated to the default
483 # values given in the %tabledata hash.
487 # { uniquefielrequired => 'fieldname', # the primary key in the table
488 # fieldname => fieldvalue,
489 # fieldname2 => fieldvalue2,
492 systempreferences => [
494 uniquefieldrequired => 'variable',
495 variable => 'Activate_Log',
497 forceupdate => { 'explanation' => 1,
499 explanation => 'Turn Log Actions on DB On an Off',
503 uniquefieldrequired => 'variable',
504 variable => 'IndependantBranches',
506 forceupdate => { 'explanation' => 1,
508 explanation => 'Turn Branch independancy management On an Off',
516 my %fielddefinitions = (
518 # { field => 'fieldname',
519 # type => 'fieldtype',
532 # Get version of MySQL database engine.
533 my $mysqlversion = `mysqld --version`;
534 $mysqlversion =~ /Ver (\S*) /;
536 if ( $mysqlversion ge '3.23' ) {
537 print "Could convert to MyISAM database tables...\n" unless $silent;
540 #---------------------------------
543 # Collect all tables into a list
544 $sth = $dbh->prepare("show tables");
546 while ( my ($table) = $sth->fetchrow ) {
547 $existingtables{$table} = 1;
551 # Now add any missing tables
552 foreach $table ( keys %requiretables ) {
553 unless ( $existingtables{$table} ) {
554 print "Adding $table table...\n" unless $silent;
555 my $sth = $dbh->prepare("create table $table $requiretables{$table}");
558 print "Error : $sth->errstr \n";
564 # now drop useless tables
565 foreach $table ( keys %dropable_table ) {
566 if ( $existingtables{$table} ) {
567 print "Dropping unused table $table\n" if $debug and not $silent;
568 $dbh->do("drop table $table");
570 print "Error : $dbh->errstr \n";
575 #---------------------------------
578 foreach $table ( keys %requirefields ) {
579 print "Check table $table\n" if $debug and not $silent;
580 $sth = $dbh->prepare("show columns from $table");
583 while ( ( $column, $type, $null, $key, $default, $extra ) = $sth->fetchrow )
585 $types{$column} = $type;
587 foreach $column ( keys %{ $requirefields{$table} } ) {
588 print " Check column $column [$types{$column}]\n" if $debug and not $silent;
589 if ( !$types{$column} ) {
591 # column doesn't exist
592 print "Adding $column field to $table table...\n" unless $silent;
593 $query = "alter table $table
594 add column $column " . $requirefields{$table}->{$column};
595 print "Execute: $query\n" if $debug;
596 my $sti = $dbh->prepare($query);
599 print "**Error : $sti->errstr \n";
606 foreach $table ( keys %fielddefinitions ) {
607 print "Check table $table\n" if $debug;
608 $sth = $dbh->prepare("show columns from $table");
611 while ( ( $column, $type, $null, $key, $default, $extra ) = $sth->fetchrow )
613 $definitions->{$column}->{type} = $type;
614 $definitions->{$column}->{null} = $null;
615 $definitions->{$column}->{key} = $key;
616 $definitions->{$column}->{default} = $default;
617 $definitions->{$column}->{extra} = $extra;
619 my $fieldrow = $fielddefinitions{$table};
620 foreach my $row (@$fieldrow) {
621 my $field = $row->{field};
622 my $type = $row->{type};
623 my $null = $row->{null};
624 my $key = $row->{key};
625 my $default = $row->{default};
626 $default="''" unless $default;
627 my $extra = $row->{extra};
628 my $def = $definitions->{$field};
629 unless ( $type eq $def->{type}
630 && $null eq $def->{null}
631 && $key eq $def->{key}
632 && $default eq $def->{default}
633 && $extra eq $def->{extra} )
639 if ( $key eq 'PRI' ) {
640 $key = 'PRIMARY KEY';
642 unless ( $extra eq 'auto_increment' ) {
645 # if it's a new column use "add", if it's an old one, use "change".
647 if ($definitions->{$field}->{type}) {
648 $action="change $field"
652 # if it's a primary key, drop the previous pk, before altering the table
654 if ($key ne 'PRIMARY KEY') {
655 $sth =$dbh->prepare("alter table $table $action $field $type $null $key $extra default ?");
657 $sth =$dbh->prepare("alter table $table drop primary key, $action $field $type $null $key $extra default ?");
659 $sth->execute($default);
660 print " Alter $field in $table\n" unless $silent;
666 # Populate tables with required data
667 foreach my $table ( keys %tabledata ) {
668 print "Checking for data required in table $table...\n" unless $silent;
669 my $tablerows = $tabledata{$table};
670 foreach my $row (@$tablerows) {
671 my $uniquefieldrequired = $row->{uniquefieldrequired};
672 my $uniquevalue = $row->{$uniquefieldrequired};
673 my $forceupdate = $row->{forceupdate};
676 "select $uniquefieldrequired from $table where $uniquefieldrequired=?"
678 $sth->execute($uniquevalue);
680 foreach my $field (keys %$forceupdate) {
681 if ($forceupdate->{$field}) {
682 my $sth=$dbh->prepare("update systempreferences set $field=? where $uniquefieldrequired=?");
683 $sth->execute($row->{$field}, $uniquevalue);
687 print "Adding row to $table: " unless $silent;
691 foreach my $field ( keys %$row ) {
692 next if $field eq 'uniquefieldrequired';
693 next if $field eq 'forceupdate';
694 my $value = $row->{$field};
695 push @values, $value;
696 print " $field => $value" unless $silent;
697 $fieldlist .= "$field,";
698 $placeholders .= "?,";
700 print "\n" unless $silent;
701 $fieldlist =~ s/,$//;
702 $placeholders =~ s/,$//;
705 "insert into $table ($fieldlist) values ($placeholders)");
706 $sth->execute(@values);
711 # at last, remove useless fields
712 foreach $table ( keys %uselessfields ) {
713 my @fields = split /,/,$uselessfields{$table};
716 foreach my $fieldtodrop (@fields) {
717 $fieldtodrop =~ s/\t//g;
718 $fieldtodrop =~ s/\n//g;
720 $sth = $dbh->prepare("show columns from $table");
722 while ( my ( $column, $type, $null, $key, $default, $extra ) = $sth->fetchrow )
724 $exists =1 if ($column eq $fieldtodrop);
727 print "deleting $fieldtodrop field in $table...\n" unless $silent;
728 my $sth = $dbh->prepare("alter table $table drop $fieldtodrop");
740 # Revision 1.114 2005/07/28 15:10:13 tipaul
741 # 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.
742 # the letter table contains 3 fields :
743 # * code => the code of the letter
744 # * name => the complete name of the letter
745 # * content => the complete text. It's a TEXT field type, so has no limits.
747 # My next goal now is to work on point 2-I "serial issue alert"
748 # 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.
749 # (see mail on koha-devel, 2005/04/07)
751 # The "serial issue alert" will be the 1st to use this letter system that probably needs some tweaking ;-)
753 # Once it will be stabilised default letters (in any languages) could be added during installer to help the library begin with this new feature.
755 # Revision 1.113 2005/07/28 08:38:41 tipaul
756 # 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 :
757 # * ReturnBeforeExpiry = yes => return date can't be after expiry date
758 # * ReturnBeforeExpiry = no => return date can be after expiry date
760 # Revision 1.112 2005/07/26 08:19:47 hdl
761 # Adding IndependantBranches System preference variable in order to manage Branch independancy.
763 # Revision 1.111 2005/07/25 15:35:38 tipaul
764 # we have decided that moving to Koha 3.0 requires being already in Koha 2.2.x
765 # So, the updatedatabase script can highly be cleaned (90% removed).
766 # Let's play with the new Koha DB structure now ;-)