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
44 my $dbh = C4::Context->dbh;
45 print "connected to your DB. Checking & modifying it\n" unless $silent;
50 # Tables to add if they don't exist
52 shelfcontents => "( shelfnumber int not null,
53 itemnumber int not null,
55 bookshelf => "( shelfnumber int auto_increment primary key,
56 shelfname char(255))",
57 z3950queue => "( id int auto_increment primary key,
66 identifier char(30))",
67 z3950results => "( id int auto_increment primary key,
77 branchrelations => "( branchcode varchar(4),
78 categorycode varchar(4))",
79 websites => "( websitenumber int(11) NOT NULL auto_increment,
80 biblionumber int(11) NOT NULL default '0',
84 PRIMARY KEY (websitenumber) )",
85 marcrecorddone => "( isbn char(40),
88 controlnumber char(40))",
89 uploadedmarc => "( id int(11) NOT NULL auto_increment PRIMARY KEY,
91 hidden smallint(6) default NULL,
92 name varchar(255) default NULL)",
93 ethnicity => "( code varchar(10) NOT NULL default '',
94 name varchar(255) default NULL,
95 PRIMARY KEY (code) )",
96 sessions => "( sessionID varchar(255) NOT NULL default '',
97 userid varchar(255) default NULL,
98 ip varchar(16) default NULL,
100 PRIMARY KEY (sessionID) )",
101 sessionqueries => "( sessionID varchar(255) NOT NULL default '',
102 userid char(100) NOT NULL default '',
103 ip char(18) NOT NULL default '',
104 url text NOT NULL default '' )",
105 bibliothesaurus => "( id bigint(20) NOT NULL auto_increment,
106 freelib char(255) NOT NULL default '',
107 stdlib char(255) NOT NULL default '',
108 category char(10) NOT NULL default '',
109 level tinyint(4) NOT NULL default '1',
110 hierarchy char(80) NOT NULL default '',
111 father char(80) NOT NULL default '',
113 KEY freelib (freelib),
115 KEY category (category),
116 KEY hierarchy (hierarchy)
119 bibid bigint(20) unsigned NOT NULL auto_increment,
120 biblionumber int(11) NOT NULL default '0',
121 datecreated date NOT NULL default '0000-00-00',
122 datemodified date default NULL,
123 origincode char(20) default NULL,
125 KEY origincode (origincode),
126 KEY biblionumber (biblionumber)
128 marc_blob_subfield => "(
129 blobidlink bigint(20) NOT NULL auto_increment,
130 subfieldvalue longtext NOT NULL,
131 PRIMARY KEY (blobidlink)
133 marc_subfield_structure => "(
134 tagfield char(3) NOT NULL default '',
135 tagsubfield char(1) NOT NULL default '',
136 liblibrarian char(255) NOT NULL default '',
137 libopac char(255) NOT NULL default '',
138 repeatable tinyint(4) NOT NULL default '0',
139 mandatory tinyint(4) NOT NULL default '0',
140 kohafield char(40) default NULL,
141 tab tinyint(1) default NULL,
142 authorised_value char(10) default NULL,
143 thesaurus_category char(10) default NULL,
144 value_builder char(80) default NULL,
145 PRIMARY KEY (tagfield,tagsubfield),
146 KEY kohafield (kohafield),
149 marc_subfield_table => "(
150 subfieldid bigint(20) unsigned NOT NULL auto_increment,
151 bibid bigint(20) unsigned NOT NULL default '0',
152 tag char(3) NOT NULL default '',
153 tagorder tinyint(4) NOT NULL default '1',
154 tag_indicator char(2) NOT NULL default '',
155 subfieldcode char(1) NOT NULL default '',
156 subfieldorder tinyint(4) NOT NULL default '1',
157 subfieldvalue varchar(255) default NULL,
158 valuebloblink bigint(20) default NULL,
159 PRIMARY KEY (subfieldid),
162 KEY tag_indicator (tag_indicator),
163 KEY subfieldorder (subfieldorder),
164 KEY subfieldcode (subfieldcode),
165 KEY subfieldvalue (subfieldvalue),
166 KEY tagorder (tagorder)
168 marc_tag_structure => "(
169 tagfield char(3) NOT NULL default '',
170 liblibrarian char(255) NOT NULL default '',
171 libopac char(255) NOT NULL default '',
172 repeatable tinyint(4) NOT NULL default '0',
173 mandatory tinyint(4) NOT NULL default '0',
174 authorised_value char(10) default NULL,
175 PRIMARY KEY (tagfield)
178 bibid bigint(20) NOT NULL default '0',
179 tag char(3) NOT NULL default '',
180 tagorder tinyint(4) NOT NULL default '1',
181 subfieldid char(1) NOT NULL default '',
182 subfieldorder tinyint(4) NOT NULL default '1',
183 word varchar(255) NOT NULL default '',
184 sndx_word varchar(255) NOT NULL default '',
187 KEY tagorder (tagorder),
188 KEY subfieldid (subfieldid),
189 KEY subfieldorder (subfieldorder),
191 KEY sndx_word (sndx_word)
193 marc_breeding => "( id bigint(20) NOT NULL auto_increment,
194 file varchar(80) NOT NULL default '',
195 isbn varchar(10) NOT NULL default '',
196 title varchar(128) default NULL,
197 author varchar(80) default NULL,
199 encoding varchar(40) default NULL,
204 authorised_values => "(id int(11) NOT NULL auto_increment,
205 category char(10) NOT NULL default '',
206 authorised_value char(80) NOT NULL default '',
211 userflags => "( bit int(11) NOT NULL default '0',
212 flag char(30), flagdesc char(255),
216 authtypecode char(10) not NULL,
217 authtypetext char(255) not NULL,
218 auth_tag_to_report char(3) not NULL,
219 summary text not NULL,
220 PRIMARY KEY (authtypecode)
222 biblio_framework => "(
223 frameworkcode char(4) not NULL,
224 frameworktext char(255) not NULL,
225 PRIMARY KEY (frameworkcode)
227 auth_subfield_structure => "(
228 authtypecode char(10) NOT NULL default '',
229 tagfield char(3) NOT NULL default '',
230 tagsubfield char(1) NOT NULL default '',
231 liblibrarian char(255) NOT NULL default '',
232 libopac char(255) NOT NULL default '',
233 repeatable tinyint(4) NOT NULL default '0',
234 mandatory tinyint(4) NOT NULL default '0',
235 tab tinyint(1) default NULL,
236 authorised_value char(10) default NULL,
237 value_builder char(80) default NULL,
238 seealso char(255) default NULL,
239 PRIMARY KEY (authtypecode,tagfield,tagsubfield),
240 KEY tab (authtypecode,tab)
242 auth_tag_structure => "(
243 authtypecode char(10) NOT NULL default '',
244 tagfield char(3) NOT NULL default '',
245 liblibrarian char(255) NOT NULL default '',
246 libopac char(255) NOT NULL default '',
247 repeatable tinyint(4) NOT NULL default '0',
248 mandatory tinyint(4) NOT NULL default '0',
249 authorised_value char(10) default NULL,
250 PRIMARY KEY (authtypecode,tagfield)
253 authid bigint(20) unsigned NOT NULL auto_increment,
254 authtypecode char(10) NOT NULL default '',
255 datecreated date NOT NULL default '0000-00-00',
256 datemodified date default NULL,
257 origincode char(20) default NULL,
258 PRIMARY KEY (authid),
259 KEY origincode (origincode),
261 auth_subfield_table => "(
262 subfieldid bigint(20) unsigned NOT NULL auto_increment,
263 authid bigint(20) unsigned NOT NULL default '0',
264 tag char(3) NOT NULL default '',
265 tagorder tinyint(4) NOT NULL default '1',
266 tag_indicator char(2) NOT NULL default '',
267 subfieldcode char(1) NOT NULL default '',
268 subfieldorder tinyint(4) NOT NULL default '1',
269 subfieldvalue varchar(255) default NULL,
270 PRIMARY KEY (subfieldid),
273 KEY subfieldcode (subfieldcode),
274 KEY subfieldvalue (subfieldvalue)
277 authid bigint(20) NOT NULL default '0',
278 tagsubfield char(4) NOT NULL default '',
279 tagorder tinyint(4) NOT NULL default '1',
280 subfieldorder tinyint(4) NOT NULL default '1',
281 word varchar(255) NOT NULL default '',
282 sndx_word varchar(255) NOT NULL default '',
284 KEY marc_search (tagsubfield,word),
286 KEY sndx_word (sndx_word)
289 suggestionnumber int(8) NOT NULL auto_increment,
290 suggestedby int(11) NOT NULL default '0',
291 managedby int(11) default NULL,
292 status varchar(10) NOT NULL default '',
294 author varchar(80) default NULL,
295 title varchar(80) default NULL,
296 copyrightdate smallint(6) default NULL,
297 publishercode varchar(255) default NULL,
298 date timestamp(8) NOT NULL,
299 mailoverseeing smallint(1) default 0,
300 PRIMARY KEY (suggestionnumber),
301 KEY suggestedby (suggestedby),
302 KEY managedby (managedby)
306 my %requirefields = (
307 biblio => { 'abstract' => 'text' },
308 deletedbiblio => { 'abstract' => 'text', 'marc' => 'blob' },
309 deleteditems => { 'marc' => 'blob', 'paidfor' => 'text' },
311 'lccn' => 'char(25)',
312 'url' => 'varchar(255)',
315 deletedbiblioitems => {
316 'lccn' => 'char(25)',
317 'url' => 'varchar(255)',
320 branchtransfers => { 'datearrived' => 'datetime' },
321 statistics => { 'borrowernumber' => 'int(11)' },
323 'invoicedisc' => 'float(6,4)',
324 'nocalc' => 'int(11)'
327 'userid' => 'char(30)',
328 'password' => 'char(30)',
329 'flags' => 'int(11)',
330 'textmessaging' => 'varchar(30)',
331 'zipcode' => 'varchar(25)',
332 'homezipcode' => 'varchar(25)',
334 aqorders => { 'budgetdate' => 'date',
335 'sort1' => 'char(80)',
336 'sort2' => 'char(80)', },
337 aqbudget => {'aqbudgetid' => 'tinyint(4) auto_increment primary key'},
338 items => {'paidfor' => 'text'},
340 #added so that reference items are not available for reserves...
341 itemtypes => { 'notforloan' => 'smallint(6)' },
342 systempreferences => { 'explanation' => 'char(80)',
343 'type' => 'char(20)',
344 'options' => 'text' },
345 z3950servers => { 'syntax' => 'char(80)' },
346 marc_tag_structure =>{
347 'frameworkcode' => 'char(4) not NULL default \'\''},
348 marc_subfield_structure =>{'seealso' => 'char(255)',
349 'frameworkcode' => 'char(4) not NULL default \'\'',
350 'hidden' => 'tinyint(1)',
351 'isurl' => 'tinyint(1)',
353 bookshelf => {'owner' => 'char(80)',
354 'category' => 'char(1)',
356 marc_biblio => { 'frameworkcode' => 'char(4) not NULL default \'\'' },
359 my %dropable_table = (
360 classification => 'classification',
361 multipart => 'multipart',
362 multivolume => 'multivolume',
363 newitems => 'newitems',
364 procedures => 'procedures',
365 publisher => 'publisher',
366 searchstats => 'searchstats',
367 serialissues => 'serialissues',
370 # the other hash contains other actions that can't be done elsewhere. they are done
371 # either BEFORE of AFTER everything else, depending on "when" entry (default => AFTER)
373 # The tabledata hash contains data that should be in the tables.
374 # The uniquefieldrequired hash entry is used to determine which (if any) fields
375 # must not exist in the table for this row to be inserted. If the
376 # uniquefieldrequired entry is already in the table, the existing data is not
377 # modified, unless the forceupdate hash entry is also set. Fields in the
378 # anonymous "forceupdate" hash will be forced to be updated to the default
379 # values given in the %tabledata hash.
384 uniquefieldrequired => 'bit',
386 flag => 'superlibrarian',
387 flagdesc => 'Access to all librarian functions',
391 uniquefieldrequired => 'bit',
394 flagdesc => 'Circulate books',
398 uniquefieldrequired => 'bit',
401 flagdesc => 'View Catalogue (Librarian Interface)',
405 uniquefieldrequired => 'bit',
407 flag => 'parameters',
408 flagdesc => 'Set Koha system paramters',
412 uniquefieldrequired => 'bit',
415 flagdesc => 'Add or modify borrowers',
419 uniquefieldrequired => 'bit',
421 flag => 'permissions',
422 flagdesc => 'Set user permissions',
426 uniquefieldrequired => 'bit',
428 flag => 'reserveforothers',
429 flagdesc => 'Reserve books for patrons',
433 uniquefieldrequired => 'bit',
436 flagdesc => 'Borrow books',
440 uniquefieldrequired => 'bit',
442 flag => 'reserveforself',
443 flagdesc => 'Reserve books for self',
447 uniquefieldrequired => 'bit',
449 flag => 'editcatalogue',
450 flagdesc => 'Edit Catalogue (Modify bibliographic/holdings data)',
454 uniquefieldrequired => 'bit',
456 flag => 'updatecharges',
457 flagdesc => 'Update borrower charges',
461 systempreferences => [
463 uniquefieldrequired => 'variable',
464 forceupdate => { 'explanation' => 1,
466 variable => 'LibraryName',
467 value => '<i><b>Koha<br/>Free Software ILS<br/><br/></b>Koha : a gift, a contribution<br/> in Maori</i>',
468 explanation => 'Library name as shown on main opac page',
473 uniquefieldrequired => 'variable',
474 forceupdate => { 'explanation' => 1,
476 variable => 'autoMemberNum',
478 explanation => 'Member number is auto-calculated',
483 uniquefieldrequired => 'variable',
484 forceupdate => { 'explanation' => 1,
487 variable => 'acquisitions',
490 'Normal, budget-based acquisitions, or Simple bibliographic-data acquisitions',
492 options => 'simple|normal'
495 uniquefieldrequired => 'variable',
496 forceupdate => { 'explanation' => 1,
499 variable => 'dateformat',
502 'date format (us mm/dd/yyyy, metric dd/mm/yyy, ISO yyyy/mm/dd)',
504 options => 'metric|us|iso'
507 uniquefieldrequired => 'variable',
508 variable => 'template',
509 forceupdate => { 'explanation' => 1,
512 explanation => 'Preference order for intranet interface templates',
516 uniquefieldrequired => 'variable',
517 variable => 'autoBarcode',
518 forceupdate => { 'explanation' => 1,
521 explanation => 'Barcode is auto-calculated',
525 uniquefieldrequired => 'variable',
526 variable => 'insecure',
527 forceupdate => { 'explanation' => 1,
531 'If YES, no auth at all is needed. Be careful if you set this to yes!',
535 uniquefieldrequired => 'variable',
536 variable => 'authoritysep',
537 forceupdate => { 'explanation' => 1,
542 'the separator used in authority/thesaurus. Usually --',
547 uniquefieldrequired => 'variable',
548 variable => 'opaclanguages',
549 forceupdate => { 'explanation' => 1,
552 explanation => 'Set the preferred order for translations. The top language will be tried first.',
556 uniquefieldrequired => 'variable',
557 variable => 'opacthemes',
558 forceupdate => { 'explanation' => 1,
561 explanation => 'Set the preferred order for themes. The top theme will be tried first.',
565 uniquefieldrequired => 'variable',
566 variable => 'timeout',
567 forceupdate => { 'explanation' => 1,
570 explanation => 'Inactivity timeout for cookies authentication (in seconds)',
574 uniquefieldrequired => 'variable',
576 forceupdate => { 'explanation' => 1,
579 explanation => 'Turn on MARC support',
583 uniquefieldrequired => 'variable',
584 variable => 'marcflavour',
585 forceupdate => { 'explanation' => 1,
590 'your MARC flavor (MARC21 or UNIMARC) used for character encoding',
592 options => 'MARC21|UNIMARC'
595 uniquefieldrequired => 'variable',
596 variable => 'checkdigit',
598 forceupdate => { 'explanation' => 1,
601 explanation => 'Validity checks on membership number: none or "Katipo" style checks',
603 options => 'none|katipo'
606 uniquefieldrequired => 'variable',
607 variable => 'maxoutstanding',
608 forceupdate => { 'explanation' => 1,
612 'maximum amount withstanding to be able make reserves ',
616 uniquefieldrequired => 'variable',
617 variable => 'maxreserves',
618 forceupdate => { 'explanation' => 1,
622 'maximum number of reserves a member can make',
627 uniquefieldrequired => 'variable',
628 variable => 'noissuescharge',
629 forceupdate => { 'explanation' => 1,
633 'maximum amount withstanding to be able to check out an item',
638 uniquefieldrequired => 'variable',
639 variable => 'KohaAdminEmailAddress',
640 forceupdate => { 'explanation' => 1,
642 value => 'your.mail@here',
643 explanation => 'the email address where borrowers modifs are sent',
647 uniquefieldrequired => 'variable',
649 forceupdate => { 'explanation' => 1,
652 explanation => 'the gist rate. NOT in %, but in numeric form (0.12 for 12%)',
656 uniquefieldrequired => 'variable',
657 variable => 'ldapserver',
658 forceupdate => { 'explanation' => 1,
661 explanation => 'your ldap server',
665 uniquefieldrequired => 'variable',
666 variable => 'ldapinfos',
667 forceupdate => { 'explanation' => 1,
670 explanation => 'ldap info. The ldap will be used in dn : uid=xxx, <ldapinfos>',
674 uniquefieldrequired => 'variable',
675 variable => 'printcirculationslips',
676 forceupdate => { 'explanation' => 1,
679 explanation => 'if set to 1, print circulation slips. If set to 0, don\'t',
683 uniquefieldrequired => 'variable',
684 variable => 'suggestion',
685 forceupdate => { 'explanation' => 1,
688 explanation => 'if set to 1, suggestions are activated in OPAC',
692 uniquefieldrequired => 'variable',
694 forceupdate => { 'explanation' => 1,
696 value => 'Fill with appropriate value...',
697 explanation => 'ISBD',
701 uniquefieldrequired => 'variable',
702 variable => 'virtualshelves',
703 forceupdate => { 'explanation' => 1,
706 explanation => 'Set virtual shelves management ON or OFF',
713 my %fielddefinitions = (
716 field => 'printername',
725 field => 'bookfundid',
734 field => 'aqbudgetid',
735 type => 'tinyint(4)',
739 extra => 'auto_increment'
749 extra => 'auto_increment'
754 field => 'z3950random',
755 type => 'varchar(40)',
763 type => 'varchar(40)',
777 # Get version of MySQL database engine.
778 my $mysqlversion = `mysqld --version`;
779 $mysqlversion =~ /Ver (\S*) /;
781 if ( $mysqlversion ge '3.23' ) {
782 print "Could convert to MyISAM database tables...\n" unless $silent;
785 #---------------------------------
788 # Collect all tables into a list
789 $sth = $dbh->prepare("show tables");
791 while ( my ($table) = $sth->fetchrow ) {
792 $existingtables{$table} = 1;
796 # Now add any missing tables
797 foreach $table ( keys %requiretables ) {
798 unless ( $existingtables{$table} ) {
799 print "Adding $table table...\n" unless $silent;
800 my $sth = $dbh->prepare("create table $table $requiretables{$table}");
803 print "Error : $sth->errstr \n";
809 # now drop useless tables
810 foreach $table ( keys %dropable_table ) {
811 if ( $existingtables{$table} ) {
812 print "Dropping unused table $table\n" if $debug and not $silent;
813 $dbh->do("drop table $table");
815 print "Error : $dbh->errstr \n";
819 unless ( $existingtables{'z3950servers'} ) {
820 #MJR: added syntax entries to close bug 624
821 print "Adding z3950servers table...\n" unless $silent;
822 my $sti = $dbh->prepare( "create table z3950servers (
835 $sti = $dbh->prepare( "insert into z3950servers
836 values ('z3950.loc.gov',
840 'Library of Congress',
845 unless ( $existingtables{'issuingrules'} ) {
846 $dbh->do("alter table categoryitem rename issuingrules");
847 $dbh->do("ALTER TABLE issuingrules ADD maxissueqty int(4) default NULL");
848 $dbh->do("ALTER TABLE issuingrules ADD issuelength int(4) default NULL");
849 $dbh->do("ALTER TABLE issuingrules ADD branchcode varchar(4) NOT NULL default ''");
850 print "renaming categoryitem\n" unless $silent;
854 #---------------------------------
857 foreach $table ( keys %requirefields ) {
858 print "Check table $table\n" if $debug and not $silent;
859 $sth = $dbh->prepare("show columns from $table");
862 while ( ( $column, $type, $null, $key, $default, $extra ) = $sth->fetchrow )
864 $types{$column} = $type;
866 foreach $column ( keys %{ $requirefields{$table} } ) {
867 print " Check column $column [$types{$column}]\n" if $debug and not $silent;
868 if ( !$types{$column} ) {
870 # column doesn't exist
871 print "Adding $column field to $table table...\n" unless $silent;
872 $query = "alter table $table
873 add column $column " . $requirefields{$table}->{$column};
874 print "Execute: $query\n" if $debug;
875 my $sti = $dbh->prepare($query);
878 print "**Error : $sti->errstr \n";
885 foreach $table ( keys %fielddefinitions ) {
886 print "Check table $table\n" if $debug;
887 $sth = $dbh->prepare("show columns from $table");
890 while ( ( $column, $type, $null, $key, $default, $extra ) = $sth->fetchrow )
892 $definitions->{$column}->{type} = $type;
893 $definitions->{$column}->{null} = $null;
894 $definitions->{$column}->{key} = $key;
895 $definitions->{$column}->{default} = $default;
896 $definitions->{$column}->{extra} = $extra;
898 my $fieldrow = $fielddefinitions{$table};
899 foreach my $row (@$fieldrow) {
900 my $field = $row->{field};
901 my $type = $row->{type};
902 my $null = $row->{null};
903 my $key = $row->{key};
904 my $default = $row->{default};
905 $default="''" unless $default;
906 my $extra = $row->{extra};
907 my $def = $definitions->{$field};
908 unless ( $type eq $def->{type}
909 && $null eq $def->{null}
910 && $key eq $def->{key}
911 && $default eq $def->{default}
912 && $extra eq $def->{extra} )
918 if ( $key eq 'PRI' ) {
919 $key = 'PRIMARY KEY';
921 unless ( $extra eq 'auto_increment' ) {
924 # if it's a new column use "add", if it's an old one, use "change".
926 if ($definitions->{$field}->{type}) {
927 $action="change $field"
931 # if it's a primary key, drop the previous pk, before altering the table
933 if ($key ne 'PRIMARY KEY') {
934 $sth =$dbh->prepare("alter table $table $action $field $type $null $key $extra default ?");
936 $sth =$dbh->prepare("alter table $table drop primary key, $action $field $type $null $key $extra default ?");
938 $sth->execute($default);
939 print " Alter $field in $table\n" unless $silent;
944 # Get list of columns from borrowers table
947 $sth = $dbh->prepare("show columns from borrowers");
949 while ( my ( $column, $type, $null, $key, $default, $extra ) = $sth->fetchrow )
951 $itemtypes{$column} = $type;
952 $nullenabled{$column} = $null;
955 unless ( $itemtypes{'cardnumber'} eq 'varchar(20)' ) {
956 $itemtypes{'cardnumber'} =~ /varchar\((\d+)\)/;
958 if ( $oldlength < 16 ) {
959 print "Setting maximum cardnumber length to 16 (was $oldlength) and marking unique.\n" unless $silent;
962 "alter table borrowers change cardnumber cardnumber varchar(16)");
967 "alter table borrowers drop index cardnumber");
972 "alter table borrowers add unique(cardnumber)");
978 # Get list of columns from items table
979 $sth = $dbh->prepare("show columns from items");
981 while ( my ( $column, $type, $null, $key, $default, $extra ) = $sth->fetchrow )
983 $itemtypes{$column} = $type;
984 $nullenabled{$column} = $null;
987 unless ( $itemtypes{'barcode'} eq 'varchar(20)' ) {
988 $itemtypes{'barcode'} =~ /varchar\((\d+)\)/;
990 if ( $oldlength < 20 ) {
991 print "Setting maximum barcode length to 20 (was $oldlength).\n" unless $silent;
994 "alter table items change barcode barcode varchar(20)");
999 # dropping unique barcode index & setting barcode to null allowed.
1001 $sth = $dbh->prepare("show index from items");
1003 while ( my ( $table, $non_unique, $key_name, $Seq_in_index, $Column_name, $Collation, $cardinality, $sub_part, $Packed, $comment ) = $sth->fetchrow )
1005 if ($key_name eq 'barcode' && $non_unique eq 0) {
1006 print "dropping BARCODE index to enable empty barcodes\n" unless $silent;
1007 $dbh->do("ALTER TABLE `items` DROP INDEX `barcode`");
1010 $dbh->do("ALTER TABLE `items` CHANGE `barcode` `barcode` VARCHAR( 20 )") unless ($nullenabled{barcode} eq 'YES');
1013 # creating fulltext index in bibliothesaurus if needed
1015 $sth = $dbh->prepare("show index from bibliothesaurus");
1018 while ( my ( $table, $non_unique, $key_name, $Seq_in_index, $Column_name, $Collation, $cardinality, $sub_part, $Packed, $comment ) = $sth->fetchrow )
1020 if ($key_name eq 'category_2') {
1024 print "Creating fulltext index on bibliothesaurus\n" unless $exists or $silent;
1025 $dbh->do('create fulltext index category_2 on bibliothesaurus (category,freelib)') unless $exists;
1028 # creating index in z3950results if needed
1030 $sth = $dbh->prepare("show index from z3950results");
1033 while ( my ( $table, $non_unique, $key_name, $Seq_in_index, $Column_name, $Collation, $cardinality, $sub_part, $Packed, $comment ) = $sth->fetchrow )
1035 if ($key_name eq 'query_server') {
1039 print "Creating index on z3950results\n" unless $exists or $silent;
1040 $dbh->do('create unique index query_server on z3950results (queryid,server)') unless $exists;
1042 # changing z3950daemon field to NULL in marc_breeding
1043 $dbh->do("ALTER TABLE `marc_breeding` CHANGE `z3950random` `z3950random` VARCHAR( 40 )");
1045 # making borrowernumber an auto_increment field
1046 $dbh->do("ALTER TABLE `borrowers` CHANGE `borrowernumber` `borrowernumber` INTEGER auto_increment");
1048 # changing indexes in marc_*_structure to use frameworkcode
1049 $dbh->do('alter table marc_subfield_structure drop index tab');
1050 $dbh->do('create index tab on marc_subfield_structure (frameworkcode,tab)');
1051 $dbh->do('alter table marc_subfield_structure drop index kohafield');
1052 $dbh->do('create index kohafield on marc_subfield_structure (frameworkcode,kohafield)');
1055 # extending the timestamp in branchtransfers...
1056 my %branchtransfers;
1058 $sth = $dbh->prepare("show columns from branchtransfers");
1060 while ( my ( $column, $type, $null, $key, $default, $extra ) = $sth->fetchrow )
1062 $branchtransfers{$column} = $type;
1065 unless ( $branchtransfers{'datesent'} eq 'datetime' ) {
1066 print "Setting type of datesent in branchtransfers to datetime.\n" unless $silent;
1069 "alter table branchtransfers change datesent datesent datetime");
1073 unless ( $branchtransfers{'datearrived'} eq 'datetime' ) {
1074 print "Setting type of datearrived in branchtransfers to datetime.\n" unless $silent;
1077 "alter table branchtransfers change datearrived datearrived datetime");
1081 # changing the branchcategories table around...
1082 my %branchcategories;
1084 $sth = $dbh->prepare("show columns from branchcategories");
1086 while ( my ( $column, $type, $null, $key, $default, $extra ) = $sth->fetchrow )
1088 $branchcategories{$column} = $type;
1091 unless ( $branchcategories{'categorycode'} eq 'varchar(4)' ) {
1093 "Setting type of categorycode in branchcategories to varchar(4),\n and making the primary key.\n" unless $silent;
1096 "alter table branchcategories change categorycode categorycode varchar(4) not null"
1101 "alter table branchcategories add primary key (categorycode)");
1105 unless ( $branchcategories{'categoryname'} eq 'text' ) {
1106 print "Changing branchcode in branchcategories to categoryname text.\n" unless $silent;
1109 "alter table branchcategories change branchcode categoryname text");
1113 unless ( $branchcategories{'codedescription'} eq 'text' ) {
1115 "Replacing branchholding in branchcategories with codedescription text.\n" unless $silent;
1118 "alter table branchcategories change branchholding codedescription text"
1123 # changing the items table around...
1126 $sth = $dbh->prepare("show columns from items");
1128 while ( my ( $column, $type, $null, $key, $default, $extra ) = $sth->fetchrow )
1130 $items{$column} = $type;
1133 if ($items{'bulk'} eq "varchar(30)") {
1134 print " Setting callnumber in items table\n" unless $silent;
1136 $dbh->prepare("ALTER TABLE `items` CHANGE `bulk` `itemcallnumber` VARCHAR( 30 ) DEFAULT NULL");
1138 $sti = $dbh->prepare("update marc_subfield_structure set kohafield=\"items.itemcallnumber\" where kohafield=\"items.bulk\"");
1142 # changing the marc_subfield_structure table around...
1143 my %marc_subfield_structure;
1145 $sth = $dbh->prepare("show columns from marc_subfield_structure");
1147 while ( my ( $column, $type, $null, $key, $default, $extra ) = $sth->fetchrow )
1149 $marc_subfield_structure{$column} = $type;
1152 if ($marc_subfield_structure{thesaurus_category}) {
1153 print " changing thesaurus_category in marc_subfield_structure table\n" unless $silent;
1155 $dbh->prepare("ALTER TABLE marc_subfield_structure CHANGE `thesaurus_category` `authtypecode` VARCHAR(10 ) DEFAULT NULL");
1160 # creating index in issuingrules if needed
1162 $sth = $dbh->prepare("show index from issuingrules");
1165 while ( my ( $table, $non_unique, $key_name, $Seq_in_index, $Column_name, $Collation, $cardinality, $sub_part, $Packed, $comment ) = $sth->fetchrow )
1167 if ($key_name eq 'PRIMARY') {
1171 print "Creating index on issuing rules\n" unless $exists or $silent;
1172 $dbh->do('ALTER TABLE issuingrules ADD PRIMARY KEY ( branchcode, categorycode, itemtype )') unless $exists;
1174 $dbh->do('ALTER TABLE marc_tag_structure drop primary key');
1175 $dbh->do('ALTER TABLE marc_tag_structure ADD PRIMARY KEY ( frameworkcode, tagfield )');
1177 $dbh->do('ALTER TABLE marc_subfield_structure drop primary key');
1178 $dbh->do('ALTER TABLE marc_subfield_structure ADD PRIMARY KEY ( frameworkcode, tagfield, tagsubfield )');
1180 # Get list of columns from marc_word table
1183 $sth = $dbh->prepare("show columns from marc_word");
1185 while ( my ( $column, $type, $null, $key, $default, $extra ) = $sth->fetchrow )
1187 $marc_word{$column} = $type;
1188 $nullenabled{$column} = $null;
1190 if ($marc_word{subfieldid}) {
1191 #create field tagsubfield, copy tag+subfieldid, then drop tag and subfieldid
1192 print "Modifying marc_word (concat on tag and subfield for better perfs)\n" unless $silent;
1193 $dbh->do("ALTER TABLE `marc_word` ADD `tagsubfield` CHAR( 4 ) NOT NULL AFTER `bibid`");
1194 $dbh->do("update marc_word set tagsubfield=concat(tag,subfieldid)");
1195 $dbh->do("alter table marc_word drop tag");
1196 $dbh->do("alter table marc_word drop subfieldid");
1197 $dbh->do("create index Search_Marc on marc_word (tagsubfield,word)");
1199 # Populate tables with required data
1201 foreach my $table ( keys %tabledata ) {
1202 print "Checking for data required in table $table...\n" unless $silent;
1203 my $tablerows = $tabledata{$table};
1204 foreach my $row (@$tablerows) {
1205 my $uniquefieldrequired = $row->{uniquefieldrequired};
1206 my $uniquevalue = $row->{$uniquefieldrequired};
1207 my $forceupdate = $row->{forceupdate};
1210 "select $uniquefieldrequired from $table where $uniquefieldrequired=?"
1212 $sth->execute($uniquevalue);
1214 foreach my $field (keys %$forceupdate) {
1215 if ($forceupdate->{$field}) {
1216 my $sth=$dbh->prepare("update systempreferences set $field=? where $uniquefieldrequired=?");
1217 $sth->execute($row->{$field}, $uniquevalue);
1221 print "Adding row to $table: " unless $silent;
1225 foreach my $field ( keys %$row ) {
1226 next if $field eq 'uniquefieldrequired';
1227 next if $field eq 'forceupdate';
1228 my $value = $row->{$field};
1229 push @values, $value;
1230 print " $field => $value" unless $silent;
1231 $fieldlist .= "$field,";
1232 $placeholders .= "?,";
1234 print "\n" unless $silent;
1235 $fieldlist =~ s/,$//;
1236 $placeholders =~ s/,$//;
1239 "insert into $table ($fieldlist) values ($placeholders)");
1240 $sth->execute(@values);
1250 # Revision 1.89 2004/07/02 15:55:08 tipaul
1251 # Adding 2 new fields, called "sort1" and "sort2"
1252 # They can be used for sorting & statistics reasons by the library.
1254 # Revision 1.88 2004/06/26 23:34:26 rangi
1257 # Revision 1.87 2004/06/23 13:03:09 tipaul
1258 # fixes in DB structure
1260 # Revision 1.86 2004/06/22 11:30:57 tipaul
1261 # adding -s (silent) flag, to have a silent install.
1262 # only updater will be verbose
1264 # Revision 1.85 2004/06/17 15:19:44 tipaul
1265 # missing Marc_Search index on marc_word
1267 # Revision 1.84 2004/06/17 08:25:21 tipaul
1268 # DB modifs : merging tag & subfield in marc_word table
1270 # Revision 1.83 2004/06/10 08:32:02 tipaul
1271 # MARC authority management (continued)
1273 # Revision 1.82 2004/06/03 12:46:58 tipaul
1274 # * frameworks and itemtypes are independant
1276 # WARNING : will work only if applied to a 2.0 base. some modifs have been done since last commit that will NOT be applied if you run updatedatabase again.
1278 # Revision 1.81 2004/05/28 09:56:21 tipaul
1281 # Revision 1.80 2004/05/28 08:32:00 tipaul
1283 # * MARC authority file
1284 # * seealso & hidden in MARC biblio structure.
1286 # Revision 1.79 2004/05/18 09:50:07 tipaul
1287 # *** empty log message ***
1289 # Revision 1.78 2004/05/10 09:29:33 tipaul
1290 # css is now the default theme for OPAC.
1291 # It will be the theme used for improvements and new things in OPAC.
1293 # Revision 1.77 2004/05/06 14:56:51 tipaul
1294 # adding table issuingrules (previously called categoryitem
1296 # Revision 1.76 2004/05/03 09:32:25 tipaul
1297 # adding printcirculationsplit parameter (already existed, but was not in systempref by defaul)
1299 # Revision 1.75 2004/04/14 19:49:00 tipaul
1300 # seealso field set to 255 chars
1302 # Revision 1.74 2004/03/11 16:10:16 tipaul
1303 # *** empty log message ***
1305 # Revision 1.73 2004/03/06 20:26:13 tipaul
1306 # adding seealso feature in MARC searches