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),
212 authtypecode char(10) not NULL,
213 authtypetext char(255) not NULL,
214 auth_tag_to_report char(3) not NULL,
215 summary text not NULL,
216 PRIMARY KEY (authtypecode)
218 biblio_framework => "(
219 frameworkcode char(4) not NULL,
220 frameworktext char(255) not NULL,
221 PRIMARY KEY (frameworkcode)
223 auth_subfield_structure => "(
224 authtypecode char(10) NOT NULL default '',
225 tagfield char(3) NOT NULL default '',
226 tagsubfield char(1) NOT NULL default '',
227 liblibrarian char(255) NOT NULL default '',
228 libopac char(255) NOT NULL default '',
229 repeatable tinyint(4) NOT NULL default '0',
230 mandatory tinyint(4) NOT NULL default '0',
231 tab tinyint(1) default NULL,
232 authorised_value char(10) default NULL,
233 value_builder char(80) default NULL,
234 seealso char(255) default NULL,
235 PRIMARY KEY (authtypecode,tagfield,tagsubfield),
236 KEY tab (authtypecode,tab)
238 auth_tag_structure => "(
239 authtypecode char(10) NOT NULL default '',
240 tagfield char(3) NOT NULL default '',
241 liblibrarian char(255) NOT NULL default '',
242 libopac char(255) NOT NULL default '',
243 repeatable tinyint(4) NOT NULL default '0',
244 mandatory tinyint(4) NOT NULL default '0',
245 authorised_value char(10) default NULL,
246 PRIMARY KEY (authtypecode,tagfield)
249 authid bigint(20) unsigned NOT NULL auto_increment,
250 datecreated date NOT NULL default '0000-00-00',
251 datemodified date default NULL,
252 origincode char(20) default NULL,
253 PRIMARY KEY (authid),
254 KEY origincode (origincode),
256 auth_subfield_table => "(
257 subfieldid bigint(20) unsigned NOT NULL auto_increment,
258 authid bigint(20) unsigned NOT NULL default '0',
259 tag char(3) NOT NULL default '',
260 tagorder tinyint(4) NOT NULL default '1',
261 tag_indicator char(2) NOT NULL default '',
262 subfieldcode char(1) NOT NULL default '',
263 subfieldorder tinyint(4) NOT NULL default '1',
264 subfieldvalue varchar(255) default NULL,
265 PRIMARY KEY (subfieldid),
268 KEY subfieldcode (subfieldcode),
269 KEY subfieldvalue (subfieldvalue)
272 authid bigint(20) NOT NULL default '0',
273 tagsubfield char(4) NOT NULL default '',
274 tagorder tinyint(4) NOT NULL default '1',
275 subfieldorder tinyint(4) NOT NULL default '1',
276 word varchar(255) NOT NULL default '',
277 sndx_word varchar(255) NOT NULL default '',
279 KEY marc_search (tagsubfield,word),
281 KEY sndx_word (sndx_word)
285 my %requirefields = (
286 biblio => { 'abstract' => 'text' },
287 deletedbiblio => { 'abstract' => 'text', 'marc' => 'blob' },
288 deleteditems => { 'marc' => 'blob', 'paidfor' => 'text' },
290 'lccn' => 'char(25)',
291 'url' => 'varchar(255)',
294 deletedbiblioitems => {
295 'lccn' => 'char(25)',
296 'url' => 'varchar(255)',
299 branchtransfers => { 'datearrived' => 'datetime' },
300 statistics => { 'borrowernumber' => 'int(11)' },
302 'invoicedisc' => 'float(6,4)',
303 'nocalc' => 'int(11)'
306 'userid' => 'char(30)',
307 'password' => 'char(30)',
308 'flags' => 'int(11)',
309 'textmessaging' => 'varchar(30)',
310 'zipcode' => 'varchar(25)',
311 'homezipcode' => 'varchar(25)',
313 aqorders => { 'budgetdate' => 'date' },
314 aqbudget => {'aqbudgetid' => 'tinyint(4) auto_increment primary key'},
315 items => {'paidfor' => 'text'},
317 #added so that reference items are not available for reserves...
318 itemtypes => { 'notforloan' => 'smallint(6)' },
319 systempreferences => { 'explanation' => 'char(80)',
320 'type' => 'char(20)',
321 'options' => 'text' },
322 z3950servers => { 'syntax' => 'char(80)' },
323 marc_tag_structure =>{
324 'frameworkcode' => 'char(4) not NULL default \'\''},
325 marc_subfield_structure =>{'seealso' => 'char(255)',
326 'frameworkcode' => 'char(4) not NULL default \'\'',
327 'hidden' => 'tinyint(1)',
328 'isurl' => 'tinyint(1)',
330 bookshelf => {'owner' => 'char(80)',
331 'category' => 'char(1)',
333 marc_biblio => { 'frameworkcode' => 'char(4) not NULL default \'\'' },
336 my %dropable_table = (
337 classification => 'classification',
338 multipart => 'multipart',
339 multivolume => 'multivolume',
340 newitems => 'newitems',
341 procedures => 'procedures',
342 publisher => 'publisher',
343 searchstats => 'searchstats',
344 serialissues => 'serialissues',
347 # the other hash contains other actions that can't be done elsewhere. they are done
348 # either BEFORE of AFTER everything else, depending on "when" entry (default => AFTER)
350 # The tabledata hash contains data that should be in the tables.
351 # The uniquefieldrequired hash entry is used to determine which (if any) fields
352 # must not exist in the table for this row to be inserted. If the
353 # uniquefieldrequired entry is already in the table, the existing data is not
354 # modified, unless the forceupdate hash entry is also set. Fields in the
355 # anonymous "forceupdate" hash will be forced to be updated to the default
356 # values given in the %tabledata hash.
361 uniquefieldrequired => 'bit',
363 flag => 'superlibrarian',
364 flagdesc => 'Access to all librarian functions',
368 uniquefieldrequired => 'bit',
371 flagdesc => 'Circulate books',
375 uniquefieldrequired => 'bit',
378 flagdesc => 'View Catalogue (Librarian Interface)',
382 uniquefieldrequired => 'bit',
384 flag => 'parameters',
385 flagdesc => 'Set Koha system paramters',
389 uniquefieldrequired => 'bit',
392 flagdesc => 'Add or modify borrowers',
396 uniquefieldrequired => 'bit',
398 flag => 'permissions',
399 flagdesc => 'Set user permissions',
403 uniquefieldrequired => 'bit',
405 flag => 'reserveforothers',
406 flagdesc => 'Reserve books for patrons',
410 uniquefieldrequired => 'bit',
413 flagdesc => 'Borrow books',
417 uniquefieldrequired => 'bit',
419 flag => 'reserveforself',
420 flagdesc => 'Reserve books for self',
424 uniquefieldrequired => 'bit',
426 flag => 'editcatalogue',
427 flagdesc => 'Edit Catalogue (Modify bibliographic/holdings data)',
431 uniquefieldrequired => 'bit',
433 flag => 'updatecharges',
434 flagdesc => 'Update borrower charges',
438 systempreferences => [
440 uniquefieldrequired => 'variable',
441 forceupdate => { 'explanation' => 1,
443 variable => 'LibraryName',
444 value => '<i><b>Koha<br/>Free Software ILS<br/><br/></b>Koha : a gift, a contribution<br/> in Maori</i>',
445 explanation => 'Library name as shown on main opac page',
450 uniquefieldrequired => 'variable',
451 forceupdate => { 'explanation' => 1,
453 variable => 'autoMemberNum',
455 explanation => 'Member number is auto-calculated',
460 uniquefieldrequired => 'variable',
461 forceupdate => { 'explanation' => 1,
464 variable => 'acquisitions',
467 'Normal, budget-based acquisitions, or Simple bibliographic-data acquisitions',
469 options => 'simple|normal'
472 uniquefieldrequired => 'variable',
473 forceupdate => { 'explanation' => 1,
476 variable => 'dateformat',
479 'date format (us mm/dd/yyyy, metric dd/mm/yyy, ISO yyyy/mm/dd)',
481 options => 'metric|us|iso'
484 uniquefieldrequired => 'variable',
485 variable => 'template',
486 forceupdate => { 'explanation' => 1,
489 explanation => 'Preference order for intranet interface templates',
493 uniquefieldrequired => 'variable',
494 variable => 'autoBarcode',
495 forceupdate => { 'explanation' => 1,
498 explanation => 'Barcode is auto-calculated',
502 uniquefieldrequired => 'variable',
503 variable => 'insecure',
504 forceupdate => { 'explanation' => 1,
508 'If YES, no auth at all is needed. Be careful if you set this to yes!',
512 uniquefieldrequired => 'variable',
513 variable => 'authoritysep',
514 forceupdate => { 'explanation' => 1,
519 'the separator used in authority/thesaurus. Usually --',
524 uniquefieldrequired => 'variable',
525 variable => 'opaclanguages',
526 forceupdate => { 'explanation' => 1,
529 explanation => 'Set the preferred order for translations. The top language will be tried first.',
533 uniquefieldrequired => 'variable',
534 variable => 'opacthemes',
535 forceupdate => { 'explanation' => 1,
538 explanation => 'Set the preferred order for themes. The top theme will be tried first.',
542 uniquefieldrequired => 'variable',
543 variable => 'timeout',
544 forceupdate => { 'explanation' => 1,
547 explanation => 'Inactivity timeout for cookies authentication (in seconds)',
551 uniquefieldrequired => 'variable',
553 forceupdate => { 'explanation' => 1,
556 explanation => 'Turn on MARC support',
560 uniquefieldrequired => 'variable',
561 variable => 'marcflavour',
562 forceupdate => { 'explanation' => 1,
567 'your MARC flavor (MARC21 or UNIMARC) used for character encoding',
569 options => 'MARC21|UNIMARC'
572 uniquefieldrequired => 'variable',
573 variable => 'checkdigit',
575 forceupdate => { 'explanation' => 1,
578 explanation => 'Validity checks on membership number: none or "Katipo" style checks',
580 options => 'none|katipo'
583 uniquefieldrequired => 'variable',
584 variable => 'maxoutstanding',
585 forceupdate => { 'explanation' => 1,
589 'maximum amount withstanding to be able make reserves ',
593 uniquefieldrequired => 'variable',
594 variable => 'maxreserves',
595 forceupdate => { 'explanation' => 1,
599 'maximum number of reserves a member can make',
604 uniquefieldrequired => 'variable',
605 variable => 'noissuescharge',
606 forceupdate => { 'explanation' => 1,
610 'maximum amount withstanding to be able to check out an item',
615 uniquefieldrequired => 'variable',
616 variable => 'KohaAdminEmailAddress',
617 forceupdate => { 'explanation' => 1,
619 value => 'your.mail@here',
620 explanation => 'the email address where borrowers modifs are sent',
624 uniquefieldrequired => 'variable',
626 forceupdate => { 'explanation' => 1,
629 explanation => 'the gist rate. NOT in %, but in numeric form (0.12 for 12%)',
633 uniquefieldrequired => 'variable',
634 variable => 'ldapserver',
635 forceupdate => { 'explanation' => 1,
638 explanation => 'your ldap server',
642 uniquefieldrequired => 'variable',
643 variable => 'ldapinfos',
644 forceupdate => { 'explanation' => 1,
647 explanation => 'ldap info. The ldap will be used in dn : uid=xxx, <ldapinfos>',
651 uniquefieldrequired => 'variable',
652 variable => 'printcirculationslips',
653 forceupdate => { 'explanation' => 1,
656 explanation => 'if set to 1, print circulation slips. If set to 0, don\'t',
660 uniquefieldrequired => 'variable',
661 variable => 'suggestion',
662 forceupdate => { 'explanation' => 1,
665 explanation => 'if set to 1, suggestions are activated in OPAC',
669 uniquefieldrequired => 'variable',
671 forceupdate => { 'explanation' => 1,
673 value => 'Fill with appropriate value...',
674 explanation => 'ISBD',
678 uniquefieldrequired => 'variable',
679 variable => 'virtualshelves',
680 forceupdate => { 'explanation' => 1,
683 explanation => 'Set virtual shelves management ON or OFF',
690 my %fielddefinitions = (
693 field => 'printername',
702 field => 'bookfundid',
711 field => 'aqbudgetid',
712 type => 'tinyint(4)',
716 extra => 'auto_increment'
726 extra => 'auto_increment'
731 field => 'z3950random',
732 type => 'varchar(40)',
740 type => 'varchar(40)',
754 # Get version of MySQL database engine.
755 my $mysqlversion = `mysqld --version`;
756 $mysqlversion =~ /Ver (\S*) /;
758 if ( $mysqlversion ge '3.23' ) {
759 print "Could convert to MyISAM database tables...\n";
762 #---------------------------------
765 # Collect all tables into a list
766 $sth = $dbh->prepare("show tables");
768 while ( my ($table) = $sth->fetchrow ) {
769 $existingtables{$table} = 1;
773 # Now add any missing tables
774 foreach $table ( keys %requiretables ) {
775 unless ( $existingtables{$table} ) {
776 print "Adding $table table...\n";
777 my $sth = $dbh->prepare("create table $table $requiretables{$table}");
780 print "Error : $sth->errstr \n";
786 # now drop useless tables
787 foreach $table ( keys %dropable_table ) {
788 if ( $existingtables{$table} ) {
789 print "Dropping unused table $table\n" if $debug;
790 $dbh->do("drop table $table");
792 print "Error : $dbh->errstr \n";
796 unless ( $existingtables{'z3950servers'} ) {
797 #MJR: added syntax entries to close bug 624
798 print "Adding z3950servers table...\n";
799 my $sti = $dbh->prepare( "create table z3950servers (
812 $sti = $dbh->prepare( "insert into z3950servers
813 values ('z3950.loc.gov',
817 'Library of Congress',
822 unless ( $existingtables{'issuingrules'} ) {
823 $dbh->do("alter table categoryitem rename issuingrules");
824 print "renaming categoryitem\n";
828 #---------------------------------
831 foreach $table ( keys %requirefields ) {
832 print "Check table $table\n" if $debug;
833 $sth = $dbh->prepare("show columns from $table");
836 while ( ( $column, $type, $null, $key, $default, $extra ) = $sth->fetchrow )
838 $types{$column} = $type;
840 foreach $column ( keys %{ $requirefields{$table} } ) {
841 print " Check column $column [$types{$column}]\n" if $debug;
842 if ( !$types{$column} ) {
844 # column doesn't exist
845 print "Adding $column field to $table table...\n";
846 $query = "alter table $table
847 add column $column " . $requirefields{$table}->{$column};
848 print "Execute: $query\n" if $debug;
849 my $sti = $dbh->prepare($query);
852 print "**Error : $sti->errstr \n";
859 foreach $table ( keys %fielddefinitions ) {
860 print "Check table $table\n" if $debug;
861 $sth = $dbh->prepare("show columns from $table");
864 while ( ( $column, $type, $null, $key, $default, $extra ) = $sth->fetchrow )
866 $definitions->{$column}->{type} = $type;
867 $definitions->{$column}->{null} = $null;
868 $definitions->{$column}->{key} = $key;
869 $definitions->{$column}->{default} = $default;
870 $definitions->{$column}->{extra} = $extra;
872 my $fieldrow = $fielddefinitions{$table};
873 foreach my $row (@$fieldrow) {
874 my $field = $row->{field};
875 my $type = $row->{type};
876 my $null = $row->{null};
877 my $key = $row->{key};
878 my $default = $row->{default};
879 $default="''" unless $default;
880 my $extra = $row->{extra};
881 my $def = $definitions->{$field};
882 unless ( $type eq $def->{type}
883 && $null eq $def->{null}
884 && $key eq $def->{key}
885 && $default eq $def->{default}
886 && $extra eq $def->{extra} )
892 if ( $key eq 'PRI' ) {
893 $key = 'PRIMARY KEY';
895 unless ( $extra eq 'auto_increment' ) {
898 # if it's a new column use "add", if it's an old one, use "change".
900 if ($definitions->{$field}->{type}) {
901 $action="change $field"
905 # if it's a primary key, drop the previous pk, before altering the table
907 if ($key ne 'PRIMARY KEY') {
908 $sth =$dbh->prepare("alter table $table $action $field $type $null $key $extra default ?");
910 $sth =$dbh->prepare("alter table $table drop primary key, $action $field $type $null $key $extra default ?");
912 $sth->execute($default);
913 print " Alter $field in $table\n";
918 # Get list of columns from borrowers table
921 $sth = $dbh->prepare("show columns from borrowers");
923 while ( my ( $column, $type, $null, $key, $default, $extra ) = $sth->fetchrow )
925 $itemtypes{$column} = $type;
926 $nullenabled{$column} = $null;
929 unless ( $itemtypes{'cardnumber'} eq 'varchar(20)' ) {
930 $itemtypes{'cardnumber'} =~ /varchar\((\d+)\)/;
932 if ( $oldlength < 16 ) {
933 print "Setting maximum cardnumber length to 16 (was $oldlength) and marking unique.\n";
936 "alter table borrowers change cardnumber cardnumber varchar(16)");
941 "alter table borrowers drop index cardnumber");
946 "alter table borrowers add unique(cardnumber)");
952 # Get list of columns from items table
953 $sth = $dbh->prepare("show columns from items");
955 while ( my ( $column, $type, $null, $key, $default, $extra ) = $sth->fetchrow )
957 $itemtypes{$column} = $type;
958 $nullenabled{$column} = $null;
961 unless ( $itemtypes{'barcode'} eq 'varchar(20)' ) {
962 $itemtypes{'barcode'} =~ /varchar\((\d+)\)/;
964 if ( $oldlength < 20 ) {
965 print "Setting maximum barcode length to 20 (was $oldlength).\n";
968 "alter table items change barcode barcode varchar(20)");
973 # dropping unique barcode index & setting barcode to null allowed.
975 $sth = $dbh->prepare("show index from items");
977 while ( my ( $table, $non_unique, $key_name, $Seq_in_index, $Column_name, $Collation, $cardinality, $sub_part, $Packed, $comment ) = $sth->fetchrow )
979 if ($key_name eq 'barcode' && $non_unique eq 0) {
980 print "dropping BARCODE index to enable empty barcodes\n";
981 $dbh->do("ALTER TABLE `items` DROP INDEX `barcode`");
984 $dbh->do("ALTER TABLE `items` CHANGE `barcode` `barcode` VARCHAR( 20 )") unless ($nullenabled{barcode} eq 'YES');
987 # creating fulltext index in bibliothesaurus if needed
989 $sth = $dbh->prepare("show index from bibliothesaurus");
992 while ( my ( $table, $non_unique, $key_name, $Seq_in_index, $Column_name, $Collation, $cardinality, $sub_part, $Packed, $comment ) = $sth->fetchrow )
994 if ($key_name eq 'category_2') {
998 print "Creating fulltext index on bibliothesaurus\n" unless $exists;
999 $dbh->do('create fulltext index category_2 on bibliothesaurus (category,freelib)') unless $exists;
1002 # creating index in z3950results if needed
1004 $sth = $dbh->prepare("show index from z3950results");
1007 while ( my ( $table, $non_unique, $key_name, $Seq_in_index, $Column_name, $Collation, $cardinality, $sub_part, $Packed, $comment ) = $sth->fetchrow )
1009 if ($key_name eq 'query_server') {
1013 print "Creating index on z3950results\n" unless $exists;
1014 $dbh->do('create unique index query_server on z3950results (queryid,server)') unless $exists;
1016 # changing z3950daemon field to NULL in marc_breeding
1017 $dbh->do("ALTER TABLE `marc_breeding` CHANGE `z3950random` `z3950random` VARCHAR( 40 )");
1019 # making borrowernumber an auto_increment field
1020 $dbh->do("ALTER TABLE `borrowers` CHANGE `borrowernumber` `borrowernumber` INTEGER auto_increment");
1022 # changing indexes in marc_*_structure to use frameworkcode
1023 $dbh->do('alter table marc_subfield_structure drop index tab');
1024 $dbh->do('create index tab on marc_subfield_structure (frameworkcode,tab)');
1025 $dbh->do('alter table marc_subfield_structure drop index kohafield');
1026 $dbh->do('create index kohafield on marc_subfield_structure (frameworkcode,kohafield)');
1029 # extending the timestamp in branchtransfers...
1030 my %branchtransfers;
1032 $sth = $dbh->prepare("show columns from branchtransfers");
1034 while ( my ( $column, $type, $null, $key, $default, $extra ) = $sth->fetchrow )
1036 $branchtransfers{$column} = $type;
1039 unless ( $branchtransfers{'datesent'} eq 'datetime' ) {
1040 print "Setting type of datesent in branchtransfers to datetime.\n";
1043 "alter table branchtransfers change datesent datesent datetime");
1047 unless ( $branchtransfers{'datearrived'} eq 'datetime' ) {
1048 print "Setting type of datearrived in branchtransfers to datetime.\n";
1051 "alter table branchtransfers change datearrived datearrived datetime");
1055 # changing the branchcategories table around...
1056 my %branchcategories;
1058 $sth = $dbh->prepare("show columns from branchcategories");
1060 while ( my ( $column, $type, $null, $key, $default, $extra ) = $sth->fetchrow )
1062 $branchcategories{$column} = $type;
1065 unless ( $branchcategories{'categorycode'} eq 'varchar(4)' ) {
1067 "Setting type of categorycode in branchcategories to varchar(4),\n and making the primary key.\n";
1070 "alter table branchcategories change categorycode categorycode varchar(4) not null"
1075 "alter table branchcategories add primary key (categorycode)");
1079 unless ( $branchcategories{'categoryname'} eq 'text' ) {
1080 print "Changing branchcode in branchcategories to categoryname text.\n";
1083 "alter table branchcategories change branchcode categoryname text");
1087 unless ( $branchcategories{'codedescription'} eq 'text' ) {
1089 "Replacing branchholding in branchcategories with codedescription text.\n";
1092 "alter table branchcategories change branchholding codedescription text"
1097 # changing the items table around...
1100 $sth = $dbh->prepare("show columns from items");
1102 while ( my ( $column, $type, $null, $key, $default, $extra ) = $sth->fetchrow )
1104 $items{$column} = $type;
1107 if ($items{'bulk'} eq "varchar(30)") {
1108 print " Setting callnumber in items table\n";
1110 $dbh->prepare("ALTER TABLE `items` CHANGE `bulk` `itemcallnumber` VARCHAR( 30 ) DEFAULT NULL");
1112 $sti = $dbh->prepare("update marc_subfield_structure set kohafield=\"items.itemcallnumber\" where kohafield=\"items.bulk\"");
1116 # changing the marc_subfield_structure table around...
1117 my %marc_subfield_structure;
1119 $sth = $dbh->prepare("show columns from marc_subfield_structure");
1121 while ( my ( $column, $type, $null, $key, $default, $extra ) = $sth->fetchrow )
1123 $marc_subfield_structure{$column} = $type;
1126 if ($marc_subfield_structure{thesaurus_category}) {
1127 print " changing thesaurus_category in marc_subfield_structure table\n";
1129 $dbh->prepare("ALTER TABLE marc_subfield_structure CHANGE `thesaurus_category` `authtypecode` VARCHAR(10 ) DEFAULT NULL");
1134 # creating index in issuingrules if needed
1136 $sth = $dbh->prepare("show index from issuingrules");
1139 while ( my ( $table, $non_unique, $key_name, $Seq_in_index, $Column_name, $Collation, $cardinality, $sub_part, $Packed, $comment ) = $sth->fetchrow )
1141 if ($key_name eq 'PRIMARY') {
1145 print "Creating index on z3950results\n" unless $exists;
1146 $dbh->do('ALTER TABLE issuingrules ADD PRIMARY KEY ( branchcode, categorycode, itemtype )') unless $exists;
1148 $dbh->do('ALTER TABLE marc_tag_structure drop primary key');
1149 $dbh->do('ALTER TABLE marc_tag_structure ADD PRIMARY KEY ( frameworkcode, tagfield )');
1151 $dbh->do('ALTER TABLE marc_subfield_structure drop primary key');
1152 $dbh->do('ALTER TABLE marc_subfield_structure ADD PRIMARY KEY ( frameworkcode, tagfield, tagsubfield )');
1154 # Get list of columns from marc_word table
1157 $sth = $dbh->prepare("show columns from marc_word");
1159 while ( my ( $column, $type, $null, $key, $default, $extra ) = $sth->fetchrow )
1161 $marc_word{$column} = $type;
1162 $nullenabled{$column} = $null;
1164 if ($marc_word{subfieldid}) {
1165 #create field tagsubfield, copy tag+subfieldid, then drop tag and subfieldid
1166 print "Modifying marc_word (concat on tag and subfield for better perfs)\n";
1167 $dbh->do("ALTER TABLE `marc_word` ADD `tagsubfield` CHAR( 4 ) NOT NULL AFTER `bibid`");
1168 $dbh->do("update marc_word set tagsubfield=concat(tag,subfieldid)");
1169 $dbh->do("alter table marc_word drop tag");
1170 $dbh->do("alter table marc_word drop subfieldid");
1172 # Populate tables with required data
1174 foreach my $table ( keys %tabledata ) {
1175 print "Checking for data required in table $table...\n";
1176 my $tablerows = $tabledata{$table};
1177 foreach my $row (@$tablerows) {
1178 my $uniquefieldrequired = $row->{uniquefieldrequired};
1179 my $uniquevalue = $row->{$uniquefieldrequired};
1180 my $forceupdate = $row->{forceupdate};
1183 "select $uniquefieldrequired from $table where $uniquefieldrequired=?"
1185 $sth->execute($uniquevalue);
1187 foreach my $field (keys %$forceupdate) {
1188 if ($forceupdate->{$field}) {
1189 my $sth=$dbh->prepare("update systempreferences set $field=? where $uniquefieldrequired=?");
1190 $sth->execute($row->{$field}, $uniquevalue);
1194 print "Adding row to $table: ";
1198 foreach my $field ( keys %$row ) {
1199 next if $field eq 'uniquefieldrequired';
1200 next if $field eq 'forceupdate';
1201 my $value = $row->{$field};
1202 push @values, $value;
1203 print " $field => $value";
1204 $fieldlist .= "$field,";
1205 $placeholders .= "?,";
1208 $fieldlist =~ s/,$//;
1209 $placeholders =~ s/,$//;
1212 "insert into $table ($fieldlist) values ($placeholders)");
1213 $sth->execute(@values);
1223 # Revision 1.84 2004/06/17 08:25:21 tipaul
1224 # DB modifs : merging tag & subfield in marc_word table
1226 # Revision 1.83 2004/06/10 08:32:02 tipaul
1227 # MARC authority management (continued)
1229 # Revision 1.82 2004/06/03 12:46:58 tipaul
1230 # * frameworks and itemtypes are independant
1232 # 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.
1234 # Revision 1.81 2004/05/28 09:56:21 tipaul
1237 # Revision 1.80 2004/05/28 08:32:00 tipaul
1239 # * MARC authority file
1240 # * seealso & hidden in MARC biblio structure.
1242 # Revision 1.79 2004/05/18 09:50:07 tipaul
1243 # *** empty log message ***
1245 # Revision 1.78 2004/05/10 09:29:33 tipaul
1246 # css is now the default theme for OPAC.
1247 # It will be the theme used for improvements and new things in OPAC.
1249 # Revision 1.77 2004/05/06 14:56:51 tipaul
1250 # adding table issuingrules (previously called categoryitem
1252 # Revision 1.76 2004/05/03 09:32:25 tipaul
1253 # adding printcirculationsplit parameter (already existed, but was not in systempref by defaul)
1255 # Revision 1.75 2004/04/14 19:49:00 tipaul
1256 # seealso field set to 255 chars
1258 # Revision 1.74 2004/03/11 16:10:16 tipaul
1259 # *** empty log message ***
1261 # Revision 1.73 2004/03/06 20:26:13 tipaul
1262 # adding seealso feature in MARC searches