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)
84 alertid int(11) NOT NULL auto_increment,
85 borrowernumber int(11) NOT NULL default '0',
86 type varchar(10) NOT NULL default '',
87 externalid varchar(20) NOT NULL default '',
88 PRIMARY KEY (alertid),
89 KEY borrowernumber (borrowernumber),
90 KEY type (type,externalid)
95 subscription => { 'letter' => 'char(20) NULL'},
96 # tablename => { 'field' => 'fieldtype' },
99 my %dropable_table = (
100 # tablename => 'tablename',
103 my %uselessfields = (
104 # tablename => "field1,field2",
106 # the other hash contains other actions that can't be done elsewhere. they are done
107 # either BEFORE of AFTER everything else, depending on "when" entry (default => AFTER)
109 # The tabledata hash contains data that should be in the tables.
110 # The uniquefieldrequired hash entry is used to determine which (if any) fields
111 # must not exist in the table for this row to be inserted. If the
112 # uniquefieldrequired entry is already in the table, the existing data is not
113 # modified, unless the forceupdate hash entry is also set. Fields in the
114 # anonymous "forceupdate" hash will be forced to be updated to the default
115 # values given in the %tabledata hash.
119 # { uniquefielrequired => 'fieldname', # the primary key in the table
120 # fieldname => fieldvalue,
121 # fieldname2 => fieldvalue2,
124 systempreferences => [
126 uniquefieldrequired => 'variable',
127 variable => 'Activate_Log',
129 forceupdate => { 'explanation' => 1,
131 explanation => 'Turn Log Actions on DB On an Off',
135 uniquefieldrequired => 'variable',
136 variable => 'ReturnBeforeExpiry',
138 forceupdate => { 'explanation' => 1,
140 explanation => 'If Yes, Returndate on issuing can\'t be after borrower card expiry',
147 my %fielddefinitions = (
149 # { field => 'fieldname',
150 # type => 'fieldtype',
163 # Get version of MySQL database engine.
164 my $mysqlversion = `mysqld --version`;
165 $mysqlversion =~ /Ver (\S*) /;
167 if ( $mysqlversion ge '3.23' ) {
168 print "Could convert to MyISAM database tables...\n" unless $silent;
171 #---------------------------------
174 # Collect all tables into a list
175 $sth = $dbh->prepare("show tables");
177 while ( my ($table) = $sth->fetchrow ) {
178 $existingtables{$table} = 1;
182 # Now add any missing tables
183 foreach $table ( keys %requiretables ) {
184 unless ( $existingtables{$table} ) {
185 print "Adding $table table...\n" unless $silent;
186 my $sth = $dbh->prepare("create table $table $requiretables{$table}");
189 print "Error : $sth->errstr \n";
195 # now drop useless tables
196 foreach $table ( keys %dropable_table ) {
197 if ( $existingtables{$table} ) {
198 print "Dropping unused table $table\n" if $debug and not $silent;
199 $dbh->do("drop table $table");
201 print "Error : $dbh->errstr \n";
206 #---------------------------------
209 foreach $table ( keys %requirefields ) {
210 print "Check table $table\n" if $debug and not $silent;
211 $sth = $dbh->prepare("show columns from $table");
214 while ( ( $column, $type, $null, $key, $default, $extra ) = $sth->fetchrow )
216 $types{$column} = $type;
218 foreach $column ( keys %{ $requirefields{$table} } ) {
219 print " Check column $column [$types{$column}]\n" if $debug and not $silent;
220 if ( !$types{$column} ) {
222 # column doesn't exist
223 print "Adding $column field to $table table...\n" unless $silent;
224 $query = "alter table $table
225 add column $column " . $requirefields{$table}->{$column};
226 print "Execute: $query\n" if $debug;
227 my $sti = $dbh->prepare($query);
230 print "**Error : $sti->errstr \n";
237 foreach $table ( keys %fielddefinitions ) {
238 print "Check table $table\n" if $debug;
239 $sth = $dbh->prepare("show columns from $table");
242 while ( ( $column, $type, $null, $key, $default, $extra ) = $sth->fetchrow )
244 $definitions->{$column}->{type} = $type;
245 $definitions->{$column}->{null} = $null;
246 $definitions->{$column}->{key} = $key;
247 $definitions->{$column}->{default} = $default;
248 $definitions->{$column}->{extra} = $extra;
250 my $fieldrow = $fielddefinitions{$table};
251 foreach my $row (@$fieldrow) {
252 my $field = $row->{field};
253 my $type = $row->{type};
254 my $null = $row->{null};
255 my $key = $row->{key};
256 my $default = $row->{default};
257 $default="''" unless $default;
258 my $extra = $row->{extra};
259 my $def = $definitions->{$field};
260 unless ( $type eq $def->{type}
261 && $null eq $def->{null}
262 && $key eq $def->{key}
263 && $default eq $def->{default}
264 && $extra eq $def->{extra} )
270 if ( $key eq 'PRI' ) {
271 $key = 'PRIMARY KEY';
273 unless ( $extra eq 'auto_increment' ) {
276 # if it's a new column use "add", if it's an old one, use "change".
278 if ($definitions->{$field}->{type}) {
279 $action="change $field"
283 # if it's a primary key, drop the previous pk, before altering the table
285 if ($key ne 'PRIMARY KEY') {
286 $sth =$dbh->prepare("alter table $table $action $field $type $null $key $extra default ?");
288 $sth =$dbh->prepare("alter table $table drop primary key, $action $field $type $null $key $extra default ?");
290 $sth->execute($default);
291 print " Alter $field in $table\n" unless $silent;
297 # Populate tables with required data
298 foreach my $table ( keys %tabledata ) {
299 print "Checking for data required in table $table...\n" unless $silent;
300 my $tablerows = $tabledata{$table};
301 foreach my $row (@$tablerows) {
302 my $uniquefieldrequired = $row->{uniquefieldrequired};
303 my $uniquevalue = $row->{$uniquefieldrequired};
304 my $forceupdate = $row->{forceupdate};
307 "select $uniquefieldrequired from $table where $uniquefieldrequired=?"
309 $sth->execute($uniquevalue);
311 foreach my $field (keys %$forceupdate) {
312 if ($forceupdate->{$field}) {
313 my $sth=$dbh->prepare("update systempreferences set $field=? where $uniquefieldrequired=?");
314 $sth->execute($row->{$field}, $uniquevalue);
318 print "Adding row to $table: " unless $silent;
322 foreach my $field ( keys %$row ) {
323 next if $field eq 'uniquefieldrequired';
324 next if $field eq 'forceupdate';
325 my $value = $row->{$field};
326 push @values, $value;
327 print " $field => $value" unless $silent;
328 $fieldlist .= "$field,";
329 $placeholders .= "?,";
331 print "\n" unless $silent;
332 $fieldlist =~ s/,$//;
333 $placeholders =~ s/,$//;
336 "insert into $table ($fieldlist) values ($placeholders)");
337 $sth->execute(@values);
342 # at last, remove useless fields
343 foreach $table ( keys %uselessfields ) {
344 my @fields = split /,/,$uselessfields{$table};
347 foreach my $fieldtodrop (@fields) {
348 $fieldtodrop =~ s/\t//g;
349 $fieldtodrop =~ s/\n//g;
351 $sth = $dbh->prepare("show columns from $table");
353 while ( my ( $column, $type, $null, $key, $default, $extra ) = $sth->fetchrow )
355 $exists =1 if ($column eq $fieldtodrop);
358 print "deleting $fieldtodrop field in $table...\n" unless $silent;
359 my $sth = $dbh->prepare("alter table $table drop $fieldtodrop");
371 # Revision 1.116 2005/08/04 08:55:54 tipaul
372 # Letters / alert system, continuing...
374 # * adding a package Letters.pm, that manages Letters & alerts.
375 # * adding feature : it's now possible to define a "letter" for any subscription created. If a letter is defined, users in OPAC can put an alert on the subscription. When an issue is marked "arrived", all users in the alert will recieve a mail (as defined in the "letter"). This last part (= send the mail) is not yet developped. (Should be done this week)
376 # * adding feature : it's now possible to "put to an alert" in OPAC, for any serial subscription. The alert is stored in a new table, called alert. An alert can be put only if the librarian has activated them in subscription (and they activate it just by choosing a "letter" to sent to borrowers on new issues)
377 # * adding feature : librarian can see in borrower detail which alerts they have put, and a user can see in opac-detail which alert they have put too.
379 # Note that the system should be generic enough to manage any type of alert.
380 # I plan to extend it soon to virtual shelves : a borrower will be able to put an alert on a virtual shelf, to be warned when something is changed in the virtual shelf (mail being sent once a day by cron, or manually by the shelf owner. Anyway, a mail won't be sent on every change, users would be spammed by Koha ;-) )
382 # Revision 1.115 2005/08/02 16:15:34 tipaul
383 # adding 2 fields to letter system :
384 # * module (acquisition, catalogue...) : it will be usefull to show the librarian only letters he may be interested by.
385 # * title, that will be used as mail subject.
387 # Revision 1.114 2005/07/28 15:10:13 tipaul
388 # 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.
389 # the letter table contains 3 fields :
390 # * code => the code of the letter
391 # * name => the complete name of the letter
392 # * content => the complete text. It's a TEXT field type, so has no limits.
394 # My next goal now is to work on point 2-I "serial issue alert"
395 # 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.
396 # (see mail on koha-devel, 2005/04/07)
398 # The "serial issue alert" will be the 1st to use this letter system that probably needs some tweaking ;-)
400 # Once it will be stabilised default letters (in any languages) could be added during installer to help the library begin with this new feature.
402 # Revision 1.113 2005/07/28 08:38:41 tipaul
403 # 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 :
404 # * ReturnBeforeExpiry = yes => return date can't be after expiry date
405 # * ReturnBeforeExpiry = no => return date can be after expiry date
407 # Revision 1.112 2005/07/26 08:19:47 hdl
408 # Adding IndependantBranches System preference variable in order to manage Branch independancy.
410 # Revision 1.111 2005/07/25 15:35:38 tipaul
411 # we have decided that moving to Koha 3.0 requires being already in Koha 2.2.x
412 # So, the updatedatabase script can highly be cleaned (90% removed).
413 # Let's play with the new Koha DB structure now ;-)
420 # This script checks for required updates to the database.
422 # Part of the Koha Library Software www.koha.org
423 # Licensed under the GPL.
426 # - Would also be a good idea to offer to do a backup at this time...
428 # NOTE: If you do something more than once in here, make it table driven.
437 # FIXME - The user might be installing a new database, so can't rely
438 # on /etc/koha.conf anyway.
445 %existingtables, # tables already in database
449 $type, $null, $key, $default, $extra,
450 $prefitem, # preference item in systempreferences table
457 my $dbh = C4::Context->dbh;
458 print "connected to your DB. Checking & modifying it\n" unless $silent;
463 # Tables to add if they don't exist
464 my %requiretables = (
465 categorytable => "(categorycode char(5) NOT NULL default '',
466 description text default '',
467 itemtypecodes text default '',
468 PRIMARY KEY (categorycode)
470 subcategorytable => "(subcategorycode char(5) NOT NULL default '',
471 description text default '',
472 itemtypecodes text default '',
473 PRIMARY KEY (subcategorycode)
475 mediatypetable => "(mediatypecode char(5) NOT NULL default '',
476 description text default '',
477 itemtypecodes text default '',
478 PRIMARY KEY (mediatypecode)
481 `timestamp` TIMESTAMP NOT NULL ,
482 `user` INT( 11 ) NOT NULL ,
483 `module` TEXT default '',
484 `action` TEXT default '' ,
485 `object` INT(11) default '' ,
486 `info` TEXT default '' ,
487 PRIMARY KEY ( `timestamp` , `user` )
491 my %requirefields = (
492 # tablename => { 'field' => 'fieldtype' },
495 my %dropable_table = (
496 # tablename => 'tablename',
499 my %uselessfields = (
500 # tablename => "field1,field2",
502 # the other hash contains other actions that can't be done elsewhere. they are done
503 # either BEFORE of AFTER everything else, depending on "when" entry (default => AFTER)
505 # The tabledata hash contains data that should be in the tables.
506 # The uniquefieldrequired hash entry is used to determine which (if any) fields
507 # must not exist in the table for this row to be inserted. If the
508 # uniquefieldrequired entry is already in the table, the existing data is not
509 # modified, unless the forceupdate hash entry is also set. Fields in the
510 # anonymous "forceupdate" hash will be forced to be updated to the default
511 # values given in the %tabledata hash.
515 # { uniquefielrequired => 'fieldname', # the primary key in the table
516 # fieldname => fieldvalue,
517 # fieldname2 => fieldvalue2,
520 systempreferences => [
522 uniquefieldrequired => 'variable',
523 variable => 'Activate_Log',
525 forceupdate => { 'explanation' => 1,
527 explanation => 'Turn Log Actions on DB On an Off',
531 uniquefieldrequired => 'variable',
532 variable => 'IndependantBranches',
534 forceupdate => { 'explanation' => 1,
536 explanation => 'Turn Branch independancy management On an Off',
544 my %fielddefinitions = (
546 # { field => 'fieldname',
547 # type => 'fieldtype',
560 # Get version of MySQL database engine.
561 my $mysqlversion = `mysqld --version`;
562 $mysqlversion =~ /Ver (\S*) /;
564 if ( $mysqlversion ge '3.23' ) {
565 print "Could convert to MyISAM database tables...\n" unless $silent;
568 #---------------------------------
571 # Collect all tables into a list
572 $sth = $dbh->prepare("show tables");
574 while ( my ($table) = $sth->fetchrow ) {
575 $existingtables{$table} = 1;
579 # Now add any missing tables
580 foreach $table ( keys %requiretables ) {
581 unless ( $existingtables{$table} ) {
582 print "Adding $table table...\n" unless $silent;
583 my $sth = $dbh->prepare("create table $table $requiretables{$table}");
586 print "Error : $sth->errstr \n";
592 # now drop useless tables
593 foreach $table ( keys %dropable_table ) {
594 if ( $existingtables{$table} ) {
595 print "Dropping unused table $table\n" if $debug and not $silent;
596 $dbh->do("drop table $table");
598 print "Error : $dbh->errstr \n";
603 #---------------------------------
606 foreach $table ( keys %requirefields ) {
607 print "Check table $table\n" if $debug and not $silent;
608 $sth = $dbh->prepare("show columns from $table");
611 while ( ( $column, $type, $null, $key, $default, $extra ) = $sth->fetchrow )
613 $types{$column} = $type;
615 foreach $column ( keys %{ $requirefields{$table} } ) {
616 print " Check column $column [$types{$column}]\n" if $debug and not $silent;
617 if ( !$types{$column} ) {
619 # column doesn't exist
620 print "Adding $column field to $table table...\n" unless $silent;
621 $query = "alter table $table
622 add column $column " . $requirefields{$table}->{$column};
623 print "Execute: $query\n" if $debug;
624 my $sti = $dbh->prepare($query);
627 print "**Error : $sti->errstr \n";
634 foreach $table ( keys %fielddefinitions ) {
635 print "Check table $table\n" if $debug;
636 $sth = $dbh->prepare("show columns from $table");
639 while ( ( $column, $type, $null, $key, $default, $extra ) = $sth->fetchrow )
641 $definitions->{$column}->{type} = $type;
642 $definitions->{$column}->{null} = $null;
643 $definitions->{$column}->{key} = $key;
644 $definitions->{$column}->{default} = $default;
645 $definitions->{$column}->{extra} = $extra;
647 my $fieldrow = $fielddefinitions{$table};
648 foreach my $row (@$fieldrow) {
649 my $field = $row->{field};
650 my $type = $row->{type};
651 my $null = $row->{null};
652 my $key = $row->{key};
653 my $default = $row->{default};
654 $default="''" unless $default;
655 my $extra = $row->{extra};
656 my $def = $definitions->{$field};
657 unless ( $type eq $def->{type}
658 && $null eq $def->{null}
659 && $key eq $def->{key}
660 && $default eq $def->{default}
661 && $extra eq $def->{extra} )
667 if ( $key eq 'PRI' ) {
668 $key = 'PRIMARY KEY';
670 unless ( $extra eq 'auto_increment' ) {
673 # if it's a new column use "add", if it's an old one, use "change".
675 if ($definitions->{$field}->{type}) {
676 $action="change $field"
680 # if it's a primary key, drop the previous pk, before altering the table
682 if ($key ne 'PRIMARY KEY') {
683 $sth =$dbh->prepare("alter table $table $action $field $type $null $key $extra default ?");
685 $sth =$dbh->prepare("alter table $table drop primary key, $action $field $type $null $key $extra default ?");
687 $sth->execute($default);
688 print " Alter $field in $table\n" unless $silent;
694 # Populate tables with required data
695 foreach my $table ( keys %tabledata ) {
696 print "Checking for data required in table $table...\n" unless $silent;
697 my $tablerows = $tabledata{$table};
698 foreach my $row (@$tablerows) {
699 my $uniquefieldrequired = $row->{uniquefieldrequired};
700 my $uniquevalue = $row->{$uniquefieldrequired};
701 my $forceupdate = $row->{forceupdate};
704 "select $uniquefieldrequired from $table where $uniquefieldrequired=?"
706 $sth->execute($uniquevalue);
708 foreach my $field (keys %$forceupdate) {
709 if ($forceupdate->{$field}) {
710 my $sth=$dbh->prepare("update systempreferences set $field=? where $uniquefieldrequired=?");
711 $sth->execute($row->{$field}, $uniquevalue);
715 print "Adding row to $table: " unless $silent;
719 foreach my $field ( keys %$row ) {
720 next if $field eq 'uniquefieldrequired';
721 next if $field eq 'forceupdate';
722 my $value = $row->{$field};
723 push @values, $value;
724 print " $field => $value" unless $silent;
725 $fieldlist .= "$field,";
726 $placeholders .= "?,";
728 print "\n" unless $silent;
729 $fieldlist =~ s/,$//;
730 $placeholders =~ s/,$//;
733 "insert into $table ($fieldlist) values ($placeholders)");
734 $sth->execute(@values);
739 # at last, remove useless fields
740 foreach $table ( keys %uselessfields ) {
741 my @fields = split /,/,$uselessfields{$table};
744 foreach my $fieldtodrop (@fields) {
745 $fieldtodrop =~ s/\t//g;
746 $fieldtodrop =~ s/\n//g;
748 $sth = $dbh->prepare("show columns from $table");
750 while ( my ( $column, $type, $null, $key, $default, $extra ) = $sth->fetchrow )
752 $exists =1 if ($column eq $fieldtodrop);
755 print "deleting $fieldtodrop field in $table...\n" unless $silent;
756 my $sth = $dbh->prepare("alter table $table drop $fieldtodrop");
768 # Revision 1.116 2005/08/04 08:55:54 tipaul
769 # Letters / alert system, continuing...
771 # * adding a package Letters.pm, that manages Letters & alerts.
772 # * adding feature : it's now possible to define a "letter" for any subscription created. If a letter is defined, users in OPAC can put an alert on the subscription. When an issue is marked "arrived", all users in the alert will recieve a mail (as defined in the "letter"). This last part (= send the mail) is not yet developped. (Should be done this week)
773 # * adding feature : it's now possible to "put to an alert" in OPAC, for any serial subscription. The alert is stored in a new table, called alert. An alert can be put only if the librarian has activated them in subscription (and they activate it just by choosing a "letter" to sent to borrowers on new issues)
774 # * adding feature : librarian can see in borrower detail which alerts they have put, and a user can see in opac-detail which alert they have put too.
776 # Note that the system should be generic enough to manage any type of alert.
777 # I plan to extend it soon to virtual shelves : a borrower will be able to put an alert on a virtual shelf, to be warned when something is changed in the virtual shelf (mail being sent once a day by cron, or manually by the shelf owner. Anyway, a mail won't be sent on every change, users would be spammed by Koha ;-) )
779 # Revision 1.115 2005/08/02 16:15:34 tipaul
780 # adding 2 fields to letter system :
781 # * module (acquisition, catalogue...) : it will be usefull to show the librarian only letters he may be interested by.
782 # * title, that will be used as mail subject.
784 # Revision 1.114 2005/07/28 15:10:13 tipaul
785 # 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.
786 # the letter table contains 3 fields :
787 # * code => the code of the letter
788 # * name => the complete name of the letter
789 # * content => the complete text. It's a TEXT field type, so has no limits.
791 # My next goal now is to work on point 2-I "serial issue alert"
792 # 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.
793 # (see mail on koha-devel, 2005/04/07)
795 # The "serial issue alert" will be the 1st to use this letter system that probably needs some tweaking ;-)
797 # Once it will be stabilised default letters (in any languages) could be added during installer to help the library begin with this new feature.
799 # Revision 1.113 2005/07/28 08:38:41 tipaul
800 # 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 :
801 # * ReturnBeforeExpiry = yes => return date can't be after expiry date
802 # * ReturnBeforeExpiry = no => return date can be after expiry date
804 # Revision 1.112 2005/07/26 08:19:47 hdl
805 # Adding IndependantBranches System preference variable in order to manage Branch independancy.
807 # Revision 1.111 2005/07/25 15:35:38 tipaul
808 # we have decided that moving to Koha 3.0 requires being already in Koha 2.2.x
809 # So, the updatedatabase script can highly be cleaned (90% removed).
810 # Let's play with the new Koha DB structure now ;-)