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.
24 # FIXME - The user might be installing a new database, so can't rely
25 # on /etc/koha.conf anyway.
32 %existingtables, # tables already in database
36 $type, $null, $key, $default, $extra,
37 $prefitem, # preference item in systempreferences table
40 my $dbh = C4::Context->dbh;
41 print "connected to your DB. Checking & modifying it\n";
46 # Tables to add if they don't exist
48 shelfcontents => "( shelfnumber int not null,
49 itemnumber int not null,
51 bookshelf => "( shelfnumber int auto_increment primary key,
52 shelfname char(255))",
53 z3950queue => "( id int auto_increment primary key,
62 identifier char(30))",
63 z3950results => "( id int auto_increment primary key,
73 branchrelations => "( branchcode varchar(4),
74 categorycode varchar(4))",
75 websites => "( websitenumber int(11) NOT NULL auto_increment,
76 biblionumber int(11) NOT NULL default '0',
80 PRIMARY KEY (websitenumber) )",
81 marcrecorddone => "( isbn char(40),
84 controlnumber char(40))",
85 uploadedmarc => "( id int(11) NOT NULL auto_increment PRIMARY KEY,
87 hidden smallint(6) default NULL,
88 name varchar(255) default NULL)",
89 ethnicity => "( code varchar(10) NOT NULL default '',
90 name varchar(255) default NULL,
91 PRIMARY KEY (code) )",
92 sessions => "( sessionID varchar(255) NOT NULL default '',
93 userid varchar(255) default NULL,
94 ip varchar(16) default NULL,
96 PRIMARY KEY (sessionID) )",
97 sessionqueries => "( sessionID varchar(255) NOT NULL default '',
98 userid char(100) NOT NULL default '',
99 ip char(18) NOT NULL default '',
100 url text NOT NULL default '' )",
101 bibliothesaurus => "( id bigint(20) NOT NULL auto_increment,
102 freelib char(255) NOT NULL default '',
103 stdlib char(255) NOT NULL default '',
104 category char(10) NOT NULL default '',
105 level tinyint(4) NOT NULL default '1',
106 hierarchy char(80) NOT NULL default '',
107 father char(80) NOT NULL default '',
109 KEY freelib (freelib),
111 KEY category (category),
112 KEY hierarchy (hierarchy)
115 bibid bigint(20) unsigned NOT NULL auto_increment,
116 biblionumber int(11) NOT NULL default '0',
117 datecreated date NOT NULL default '0000-00-00',
118 datemodified date default NULL,
119 origincode char(20) default NULL,
121 KEY origincode (origincode),
122 KEY biblionumber (biblionumber)
124 marc_blob_subfield => "(
125 blobidlink bigint(20) NOT NULL auto_increment,
126 subfieldvalue longtext NOT NULL,
127 PRIMARY KEY (blobidlink)
129 marc_subfield_structure => "(
130 tagfield char(3) NOT NULL default '',
131 tagsubfield char(1) NOT NULL default '',
132 liblibrarian char(255) NOT NULL default '',
133 libopac char(255) NOT NULL default '',
134 repeatable tinyint(4) NOT NULL default '0',
135 mandatory tinyint(4) NOT NULL default '0',
136 kohafield char(40) default NULL,
137 tab tinyint(1) default NULL,
138 authorised_value char(10) default NULL,
139 thesaurus_category char(10) default NULL,
140 value_builder char(80) default NULL,
141 PRIMARY KEY (tagfield,tagsubfield),
142 KEY kohafield (kohafield),
145 marc_subfield_table => "(
146 subfieldid bigint(20) unsigned NOT NULL auto_increment,
147 bibid bigint(20) unsigned NOT NULL default '0',
148 tag char(3) NOT NULL default '',
149 tagorder tinyint(4) NOT NULL default '1',
150 tag_indicator char(2) NOT NULL default '',
151 subfieldcode char(1) NOT NULL default '',
152 subfieldorder tinyint(4) NOT NULL default '1',
153 subfieldvalue varchar(255) default NULL,
154 valuebloblink bigint(20) default NULL,
155 PRIMARY KEY (subfieldid),
158 KEY tag_indicator (tag_indicator),
159 KEY subfieldorder (subfieldorder),
160 KEY subfieldcode (subfieldcode),
161 KEY subfieldvalue (subfieldvalue),
162 KEY tagorder (tagorder)
164 marc_tag_structure => "(
165 tagfield char(3) NOT NULL default '',
166 liblibrarian char(255) NOT NULL default '',
167 libopac char(255) NOT NULL default '',
168 repeatable tinyint(4) NOT NULL default '0',
169 mandatory tinyint(4) NOT NULL default '0',
170 authorised_value char(10) default NULL,
171 PRIMARY KEY (tagfield)
174 bibid bigint(20) NOT NULL default '0',
175 tag char(3) NOT NULL default '',
176 tagorder tinyint(4) NOT NULL default '1',
177 subfieldid char(1) NOT NULL default '',
178 subfieldorder tinyint(4) NOT NULL default '1',
179 word varchar(255) NOT NULL default '',
180 sndx_word varchar(255) NOT NULL default '',
183 KEY tagorder (tagorder),
184 KEY subfieldid (subfieldid),
185 KEY subfieldorder (subfieldorder),
187 KEY sndx_word (sndx_word)
189 marc_breeding => "( id bigint(20) NOT NULL auto_increment,
190 file varchar(80) NOT NULL default '',
191 isbn varchar(10) NOT NULL default '',
192 title varchar(128) default NULL,
193 author varchar(80) default NULL,
195 encoding varchar(40) default NULL,
200 authorised_values => "(id int(11) NOT NULL auto_increment,
201 category char(10) NOT NULL default '',
202 authorised_value char(80) NOT NULL default '',
207 userflags => "( bit int(11) NOT NULL default '0',
208 flag char(30), flagdesc char(255),
213 my %requirefields = (
214 biblio => { 'abstract' => 'text' },
215 deletedbiblio => { 'abstract' => 'text', 'marc' => 'blob' },
216 deleteditems => { 'marc' => 'blob', 'paidfor' => 'text' },
218 'lccn' => 'char(25)',
219 'url' => 'varchar(255)',
222 deletedbiblioitems => {
223 'lccn' => 'char(25)',
224 'url' => 'varchar(255)',
227 branchtransfers => { 'datearrived' => 'datetime' },
228 statistics => { 'borrowernumber' => 'int(11)' },
230 'invoicedisc' => 'float(6,4)',
231 'nocalc' => 'int(11)'
234 'userid' => 'char(30)',
235 'password' => 'char(30)',
236 'flags' => 'int(11)',
237 'textmessaging' => 'varchar(30)',
238 'zipcode' => 'varchar(25)',
239 'homezipcode' => 'varchar(25)',
241 aqorders => { 'budgetdate' => 'date' },
242 aqbudget => {'aqbudgetid' => 'tinyint(4) auto_increment primary key'},
243 items => {'paidfor' => 'text'},
245 #added so that reference items are not available for reserves...
246 itemtypes => { 'notforloan' => 'smallint(6)' },
247 systempreferences => { 'explanation' => 'char(80)',
248 'type' => 'char(20)',
249 'options' => 'text' },
250 z3950servers => { 'syntax' => 'char(80)' },
251 marc_subfield_structure =>{'seealso' => 'char(80)'},
254 my %dropable_table = (
255 classification => 'classification',
256 multipart => 'multipart',
257 multivolume => 'multivolume',
258 newitems => 'newitems',
259 procedures => 'procedures',
260 publisher => 'publisher',
261 searchstats => 'searchstats',
262 serialissues => 'serialissues',
265 # the other hash contains other actions that can't be done elsewhere. they are done
266 # either BEFORE of AFTER everything else, depending on "when" entry (default => AFTER)
268 # The tabledata hash contains data that should be in the tables.
269 # The uniquefieldrequired hash entry is used to determine which (if any) fields
270 # must not exist in the table for this row to be inserted. If the
271 # uniquefieldrequired entry is already in the table, the existing data is not
272 # modified, unless the forceupdate hash entry is also set. Fields in the
273 # anonymous "forceupdate" hash will be forced to be updated to the default
274 # values given in the %tabledata hash.
279 uniquefieldrequired => 'bit',
281 flag => 'superlibrarian',
282 flagdesc => 'Access to all librarian functions',
286 uniquefieldrequired => 'bit',
289 flagdesc => 'Circulate books',
293 uniquefieldrequired => 'bit',
296 flagdesc => 'View Catalogue (Librarian Interface)',
300 uniquefieldrequired => 'bit',
302 flag => 'parameters',
303 flagdesc => 'Set Koha system paramters',
307 uniquefieldrequired => 'bit',
310 flagdesc => 'Add or modify borrowers',
314 uniquefieldrequired => 'bit',
316 flag => 'permissions',
317 flagdesc => 'Set user permissions',
321 uniquefieldrequired => 'bit',
323 flag => 'reserveforothers',
324 flagdesc => 'Reserve books for patrons',
328 uniquefieldrequired => 'bit',
331 flagdesc => 'Borrow books',
335 uniquefieldrequired => 'bit',
337 flag => 'reserveforself',
338 flagdesc => 'Reserve books for self',
342 uniquefieldrequired => 'bit',
344 flag => 'editcatalogue',
345 flagdesc => 'Edit Catalogue (Modify bibliographic/holdings data)',
349 uniquefieldrequired => 'bit',
351 flag => 'updatecharges',
352 flagdesc => 'Update borrower charges',
356 systempreferences => [
358 uniquefieldrequired => 'variable',
359 forceupdate => { 'explanation' => 1,
361 variable => 'LibraryName',
362 value => '<i><b>Koha<br/>Free Software ILS<br/><br/></b>Koha : a gift, a contribution<br/> in Maori</i>',
363 explanation => 'Library name as shown on main opac page',
368 uniquefieldrequired => 'variable',
369 forceupdate => { 'explanation' => 1,
371 variable => 'autoMemberNum',
373 explanation => 'Member number is auto-calculated',
378 uniquefieldrequired => 'variable',
379 forceupdate => { 'explanation' => 1,
382 variable => 'acquisitions',
385 'Normal, budget-based acquisitions, or Simple bibliographic-data acquisitions',
387 options => 'simple|normal'
390 uniquefieldrequired => 'variable',
391 forceupdate => { 'explanation' => 1,
394 variable => 'dateformat',
397 'date format (us mm/dd/yyyy, metric dd/mm/yyy, ISO yyyy/mm/dd)',
399 options => 'metric|us|iso'
402 uniquefieldrequired => 'variable',
403 variable => 'template',
404 forceupdate => { 'explanation' => 1,
407 explanation => 'Preference order for intranet interface templates',
411 uniquefieldrequired => 'variable',
412 variable => 'autoBarcode',
413 forceupdate => { 'explanation' => 1,
416 explanation => 'Barcode is auto-calculated',
420 uniquefieldrequired => 'variable',
421 variable => 'insecure',
422 forceupdate => { 'explanation' => 1,
426 'If YES, no auth at all is needed. Be careful if you set this to yes!',
430 uniquefieldrequired => 'variable',
431 variable => 'authoritysep',
432 forceupdate => { 'explanation' => 1,
437 'the separator used in authority/thesaurus. Usually --',
442 uniquefieldrequired => 'variable',
443 variable => 'opaclanguages',
444 forceupdate => { 'explanation' => 1,
447 explanation => 'Set the preferred order for translations. The top language will be tried first.',
451 uniquefieldrequired => 'variable',
452 variable => 'opacthemes',
453 forceupdate => { 'explanation' => 1,
456 explanation => 'Set the preferred order for themes. The top theme will be tried first.',
460 uniquefieldrequired => 'variable',
461 variable => 'timeout',
462 forceupdate => { 'explanation' => 1,
465 explanation => 'Inactivity timeout for cookies authentication (in seconds)',
469 uniquefieldrequired => 'variable',
471 forceupdate => { 'explanation' => 1,
474 explanation => 'Turn on MARC support',
478 uniquefieldrequired => 'variable',
479 variable => 'marcflavour',
480 forceupdate => { 'explanation' => 1,
485 'your MARC flavor (MARC21 or UNIMARC) used for character encoding',
487 options => 'MARC21|UNIMARC'
490 uniquefieldrequired => 'variable',
491 variable => 'checkdigit',
493 forceupdate => { 'explanation' => 1,
496 explanation => 'Validity checks on membership number: none or "Katipo" style checks',
498 options => 'none|katipo'
501 uniquefieldrequired => 'variable',
502 variable => 'maxoutstanding',
503 forceupdate => { 'explanation' => 1,
507 'maximum amount withstanding to be able make reserves ',
511 uniquefieldrequired => 'variable',
512 variable => 'maxreserves',
513 forceupdate => { 'explanation' => 1,
517 'maximum number of reserves a member can make',
522 uniquefieldrequired => 'variable',
523 variable => 'noissuescharge',
524 forceupdate => { 'explanation' => 1,
528 'maximum amount withstanding to be able to check out an item',
533 uniquefieldrequired => 'variable',
534 variable => 'KohaAdminEmailAddress',
535 forceupdate => { 'explanation' => 1,
537 value => 'your.mail@here',
538 explanation => 'the email address where borrowers modifs are sent',
542 uniquefieldrequired => 'variable',
544 forceupdate => { 'explanation' => 1,
547 explanation => 'the gist rate. NOT in %, but in numeric form (0.12 for 12%)',
551 uniquefieldrequired => 'variable',
552 variable => 'ldapserver',
553 forceupdate => { 'explanation' => 1,
556 explanation => 'your ldap server',
560 uniquefieldrequired => 'variable',
561 variable => 'ldapinfos',
562 forceupdate => { 'explanation' => 1,
565 explanation => 'ldap info. The ldap will be used in dn : uid=xxx, <ldapinfos>',
572 my %fielddefinitions = (
575 field => 'printername',
584 field => 'bookfundid',
593 field => 'aqbudgetid',
594 type => 'tinyint(4)',
598 extra => 'auto_increment'
608 extra => 'auto_increment'
613 field => 'z3950random',
614 type => 'varchar(40)',
622 type => 'varchar(40)',
636 # Get version of MySQL database engine.
637 my $mysqlversion = `mysqld --version`;
638 $mysqlversion =~ /Ver (\S*) /;
640 if ( $mysqlversion ge '3.23' ) {
641 print "Could convert to MyISAM database tables...\n";
644 #---------------------------------
647 # Collect all tables into a list
648 $sth = $dbh->prepare("show tables");
650 while ( my ($table) = $sth->fetchrow ) {
651 $existingtables{$table} = 1;
655 # Now add any missing tables
656 foreach $table ( keys %requiretables ) {
657 unless ( $existingtables{$table} ) {
658 print "Adding $table table...\n";
659 my $sth = $dbh->prepare("create table $table $requiretables{$table}");
662 print "Error : $sth->errstr \n";
668 # now drop useless tables
669 foreach $table ( keys %dropable_table ) {
670 if ( $existingtables{$table} ) {
671 print "Dropping unused table $table\n" if $debug;
672 $dbh->do("drop table $table");
674 print "Error : $dbh->errstr \n";
678 unless ( $existingtables{'z3950servers'} ) {
679 #MJR: added syntax entries to close bug 624
680 print "Adding z3950servers table...\n";
681 my $sti = $dbh->prepare( "create table z3950servers (
694 $sti = $dbh->prepare( "insert into z3950servers
695 values ('z3950.loc.gov',
699 'Library of Congress',
705 #---------------------------------
708 foreach $table ( keys %requirefields ) {
709 print "Check table $table\n" if $debug;
710 $sth = $dbh->prepare("show columns from $table");
713 while ( ( $column, $type, $null, $key, $default, $extra ) = $sth->fetchrow )
715 $types{$column} = $type;
717 foreach $column ( keys %{ $requirefields{$table} } ) {
718 print " Check column $column [$types{$column}]\n" if $debug;
719 if ( !$types{$column} ) {
721 # column doesn't exist
722 print "Adding $column field to $table table...\n";
723 $query = "alter table $table
724 add column $column " . $requirefields{$table}->{$column};
725 print "Execute: $query\n" if $debug;
726 my $sti = $dbh->prepare($query);
729 print "**Error : $sti->errstr \n";
736 foreach $table ( keys %fielddefinitions ) {
737 print "Check table $table\n" if $debug;
738 $sth = $dbh->prepare("show columns from $table");
741 while ( ( $column, $type, $null, $key, $default, $extra ) = $sth->fetchrow )
743 $definitions->{$column}->{type} = $type;
744 $definitions->{$column}->{null} = $null;
745 $definitions->{$column}->{key} = $key;
746 $definitions->{$column}->{default} = $default;
747 $definitions->{$column}->{extra} = $extra;
749 my $fieldrow = $fielddefinitions{$table};
750 foreach my $row (@$fieldrow) {
751 my $field = $row->{field};
752 my $type = $row->{type};
753 my $null = $row->{null};
754 my $key = $row->{key};
755 my $default = $row->{default};
756 $default="''" unless $default;
757 my $extra = $row->{extra};
758 my $def = $definitions->{$field};
759 unless ( $type eq $def->{type}
760 && $null eq $def->{null}
761 && $key eq $def->{key}
762 && $default eq $def->{default}
763 && $extra eq $def->{extra} )
769 if ( $key eq 'PRI' ) {
770 $key = 'PRIMARY KEY';
772 unless ( $extra eq 'auto_increment' ) {
775 # if it's a new column use "add", if it's an old one, use "change".
777 if ($definitions->{$field}->{type}) {
778 $action="change $field"
782 # if it's a primary key, drop the previous pk, before altering the table
784 if ($key ne 'PRIMARY KEY') {
785 $sth =$dbh->prepare("alter table $table $action $field $type $null $key $extra default ?");
787 $sth =$dbh->prepare("alter table $table drop primary key, $action $field $type $null $key $extra default ?");
789 $sth->execute($default);
790 print " Alter $field in $table\n";
795 # Get list of columns from borrowers table
798 $sth = $dbh->prepare("show columns from borrowers");
800 while ( my ( $column, $type, $null, $key, $default, $extra ) = $sth->fetchrow )
802 $itemtypes{$column} = $type;
803 $nullenabled{$column} = $null;
806 unless ( $itemtypes{'cardnumber'} eq 'varchar(20)' ) {
807 $itemtypes{'cardnumber'} =~ /varchar\((\d+)\)/;
809 if ( $oldlength < 16 ) {
810 print "Setting maximum cardnumber length to 16 (was $oldlength) and marking unique.\n";
813 "alter table borrowers change cardnumber cardnumber varchar(16)");
818 "alter table borrowers drop index cardnumber");
823 "alter table borrowers add unique(cardnumber)");
829 # Get list of columns from items table
830 $sth = $dbh->prepare("show columns from items");
832 while ( my ( $column, $type, $null, $key, $default, $extra ) = $sth->fetchrow )
834 $itemtypes{$column} = $type;
835 $nullenabled{$column} = $null;
838 unless ( $itemtypes{'barcode'} eq 'varchar(20)' ) {
839 $itemtypes{'barcode'} =~ /varchar\((\d+)\)/;
841 if ( $oldlength < 20 ) {
842 print "Setting maximum barcode length to 20 (was $oldlength).\n";
845 "alter table items change barcode barcode varchar(20)");
850 # dropping unique barcode index & setting barcode to null allowed.
852 $sth = $dbh->prepare("show index from items");
854 while ( my ( $table, $non_unique, $key_name, $Seq_in_index, $Column_name, $Collation, $cardinality, $sub_part, $Packed, $comment ) = $sth->fetchrow )
856 if ($key_name eq 'barcode' && $non_unique eq 0) {
857 print "dropping BARCODE index to enable empty barcodes\n";
858 $dbh->do("ALTER TABLE `items` DROP INDEX `barcode`");
861 $dbh->do("ALTER TABLE `items` CHANGE `barcode` `barcode` VARCHAR( 20 )") unless ($nullenabled{barcode} eq 'YES');
864 # creating fulltext index in bibliothesaurus if needed
866 $sth = $dbh->prepare("show index from bibliothesaurus");
869 while ( my ( $table, $non_unique, $key_name, $Seq_in_index, $Column_name, $Collation, $cardinality, $sub_part, $Packed, $comment ) = $sth->fetchrow )
871 if ($key_name eq 'category_2') {
875 print "Creating fulltext index on bibliothesaurus\n" unless $exists;
876 $dbh->do('create fulltext index category_2 on bibliothesaurus (category,freelib)') unless $exists;
879 # creating index in z3950results if needed
881 $sth = $dbh->prepare("show index from z3950results");
884 while ( my ( $table, $non_unique, $key_name, $Seq_in_index, $Column_name, $Collation, $cardinality, $sub_part, $Packed, $comment ) = $sth->fetchrow )
886 if ($key_name eq 'query_server') {
890 print "Creating index on z3950results\n" unless $exists;
891 $dbh->do('create unique index query_server on z3950results (queryid,server)') unless $exists;
893 # changing z3950daemon field to NULL in marc_breeding
894 $dbh->do("ALTER TABLE `marc_breeding` CHANGE `z3950random` `z3950random` VARCHAR( 40 )");
896 # making borrowernumber an auto_increment field
897 $dbh->do("ALTER TABLE `borrowers` CHANGE `borrowernumber` `borrowernumber` INTEGER auto_increment");
899 # extending the timestamp in branchtransfers...
902 $sth = $dbh->prepare("show columns from branchtransfers");
904 while ( my ( $column, $type, $null, $key, $default, $extra ) = $sth->fetchrow )
906 $branchtransfers{$column} = $type;
909 unless ( $branchtransfers{'datesent'} eq 'datetime' ) {
910 print "Setting type of datesent in branchtransfers to datetime.\n";
913 "alter table branchtransfers change datesent datesent datetime");
917 unless ( $branchtransfers{'datearrived'} eq 'datetime' ) {
918 print "Setting type of datearrived in branchtransfers to datetime.\n";
921 "alter table branchtransfers change datearrived datearrived datetime");
925 # changing the branchcategories table around...
926 my %branchcategories;
928 $sth = $dbh->prepare("show columns from branchcategories");
930 while ( my ( $column, $type, $null, $key, $default, $extra ) = $sth->fetchrow )
932 $branchcategories{$column} = $type;
935 unless ( $branchcategories{'categorycode'} eq 'varchar(4)' ) {
937 "Setting type of categorycode in branchcategories to varchar(4),\n and making the primary key.\n";
940 "alter table branchcategories change categorycode categorycode varchar(4) not null"
945 "alter table branchcategories add primary key (categorycode)");
949 unless ( $branchcategories{'categoryname'} eq 'text' ) {
950 print "Changing branchcode in branchcategories to categoryname text.\n";
953 "alter table branchcategories change branchcode categoryname text");
957 unless ( $branchcategories{'codedescription'} eq 'text' ) {
959 "Replacing branchholding in branchcategories with codedescription text.\n";
962 "alter table branchcategories change branchholding codedescription text"
967 # Populate tables with required data
969 foreach my $table ( keys %tabledata ) {
970 print "Checking for data required in table $table...\n";
971 my $tablerows = $tabledata{$table};
972 foreach my $row (@$tablerows) {
973 my $uniquefieldrequired = $row->{uniquefieldrequired};
974 my $uniquevalue = $row->{$uniquefieldrequired};
975 my $forceupdate = $row->{forceupdate};
978 "select $uniquefieldrequired from $table where $uniquefieldrequired=?"
980 $sth->execute($uniquevalue);
982 foreach my $field (keys %$forceupdate) {
983 if ($forceupdate->{$field}) {
984 my $sth=$dbh->prepare("update systempreferences set $field=? where $uniquefieldrequired=?");
985 $sth->execute($row->{$field}, $uniquevalue);
989 print "Adding row to $table: ";
993 foreach my $field ( keys %$row ) {
994 next if $field eq 'uniquefieldrequired';
995 next if $field eq 'forceupdate';
996 my $value = $row->{$field};
997 push @values, $value;
998 print " $field => $value";
999 $fieldlist .= "$field,";
1000 $placeholders .= "?,";
1003 $fieldlist =~ s/,$//;
1004 $placeholders =~ s/,$//;
1007 "insert into $table ($fieldlist) values ($placeholders)");
1008 $sth->execute(@values);
1018 # Revision 1.73 2004/03/06 20:26:13 tipaul
1019 # adding seealso feature in MARC searches