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,
216 auth_subfield_structure => "(
217 authtypecode char(10) NOT NULL default '',
218 tagfield char(3) NOT NULL default '',
219 tagsubfield char(1) NOT NULL default '',
220 liblibrarian char(255) NOT NULL default '',
221 libopac char(255) NOT NULL default '',
222 repeatable tinyint(4) NOT NULL default '0',
223 mandatory tinyint(4) NOT NULL default '0',
224 tab tinyint(1) default NULL,
225 authorised_value char(10) default NULL,
226 value_builder char(80) default NULL,
227 seealso char(255) default NULL,
228 PRIMARY KEY (authtypecode,tagfield,tagsubfield),
231 auth_tag_structure => "(
232 authtypecode char(10) NOT NULL default '',
233 tagfield char(3) NOT NULL default '',
234 liblibrarian char(255) NOT NULL default '',
235 libopac char(255) NOT NULL default '',
236 repeatable tinyint(4) NOT NULL default '0',
237 mandatory tinyint(4) NOT NULL default '0',
238 authorised_value char(10) default NULL,
239 PRIMARY KEY (authtypecode,tagfield)
242 authid bigint(20) unsigned NOT NULL auto_increment,
243 datecreated date NOT NULL default '0000-00-00',
244 datemodified date default NULL,
245 origincode char(20) default NULL,
246 PRIMARY KEY (authid),
247 KEY origincode (origincode),
249 marc_subfield_table => "(
250 subfieldid bigint(20) unsigned NOT NULL auto_increment,
251 authid bigint(20) unsigned NOT NULL default '0',
252 tag char(3) NOT NULL default '',
253 tagorder tinyint(4) NOT NULL default '1',
254 tag_indicator char(2) NOT NULL default '',
255 subfieldcode char(1) NOT NULL default '',
256 subfieldorder tinyint(4) NOT NULL default '1',
257 subfieldvalue varchar(255) default NULL,
258 valuebloblink bigint(20) default NULL,
259 PRIMARY KEY (subfieldid),
262 KEY tag_indicator (tag_indicator),
263 KEY subfieldorder (subfieldorder),
264 KEY subfieldcode (subfieldcode),
265 KEY subfieldvalue (subfieldvalue),
266 KEY tagorder (tagorder)
269 authid bigint(20) NOT NULL default '0',
270 tag char(3) NOT NULL default '',
271 tagorder tinyint(4) NOT NULL default '1',
272 subfieldid char(1) NOT NULL default '',
273 subfieldorder tinyint(4) NOT NULL default '1',
274 word varchar(255) NOT NULL default '',
275 sndx_word varchar(255) NOT NULL default '',
278 KEY tagorder (tagorder),
279 KEY subfieldid (subfieldid),
280 KEY subfieldorder (subfieldorder),
282 KEY sndx_word (sndx_word)
286 my %requirefields = (
287 biblio => { 'abstract' => 'text' },
288 deletedbiblio => { 'abstract' => 'text', 'marc' => 'blob' },
289 deleteditems => { 'marc' => 'blob', 'paidfor' => 'text' },
291 'lccn' => 'char(25)',
292 'url' => 'varchar(255)',
295 deletedbiblioitems => {
296 'lccn' => 'char(25)',
297 'url' => 'varchar(255)',
300 branchtransfers => { 'datearrived' => 'datetime' },
301 statistics => { 'borrowernumber' => 'int(11)' },
303 'invoicedisc' => 'float(6,4)',
304 'nocalc' => 'int(11)'
307 'userid' => 'char(30)',
308 'password' => 'char(30)',
309 'flags' => 'int(11)',
310 'textmessaging' => 'varchar(30)',
311 'zipcode' => 'varchar(25)',
312 'homezipcode' => 'varchar(25)',
314 aqorders => { 'budgetdate' => 'date' },
315 aqbudget => {'aqbudgetid' => 'tinyint(4) auto_increment primary key'},
316 items => {'paidfor' => 'text'},
318 #added so that reference items are not available for reserves...
319 itemtypes => { 'notforloan' => 'smallint(6)' },
320 systempreferences => { 'explanation' => 'char(80)',
321 'type' => 'char(20)',
322 'options' => 'text' },
323 z3950servers => { 'syntax' => 'char(80)' },
324 marc_tag_structure =>{
325 'itemtype' => 'char(4) not NULL default \'\''},
326 marc_subfield_structure =>{'seealso' => 'char(255)',
327 'itemtype' => 'char(4) not NULL default \'\'',
328 'hidden' => 'tinyint(1)',
329 'isurl' => 'tinyint(1)',
331 bookshelf => {'owner' => 'char(80)',
332 'category' => 'char(1)',
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',
672 my %fielddefinitions = (
675 field => 'printername',
684 field => 'bookfundid',
693 field => 'aqbudgetid',
694 type => 'tinyint(4)',
698 extra => 'auto_increment'
708 extra => 'auto_increment'
713 field => 'z3950random',
714 type => 'varchar(40)',
722 type => 'varchar(40)',
736 # Get version of MySQL database engine.
737 my $mysqlversion = `mysqld --version`;
738 $mysqlversion =~ /Ver (\S*) /;
740 if ( $mysqlversion ge '3.23' ) {
741 print "Could convert to MyISAM database tables...\n";
744 #---------------------------------
747 # Collect all tables into a list
748 $sth = $dbh->prepare("show tables");
750 while ( my ($table) = $sth->fetchrow ) {
751 $existingtables{$table} = 1;
755 # Now add any missing tables
756 foreach $table ( keys %requiretables ) {
757 unless ( $existingtables{$table} ) {
758 print "Adding $table table...\n";
759 my $sth = $dbh->prepare("create table $table $requiretables{$table}");
762 print "Error : $sth->errstr \n";
768 # now drop useless tables
769 foreach $table ( keys %dropable_table ) {
770 if ( $existingtables{$table} ) {
771 print "Dropping unused table $table\n" if $debug;
772 $dbh->do("drop table $table");
774 print "Error : $dbh->errstr \n";
778 unless ( $existingtables{'z3950servers'} ) {
779 #MJR: added syntax entries to close bug 624
780 print "Adding z3950servers table...\n";
781 my $sti = $dbh->prepare( "create table z3950servers (
794 $sti = $dbh->prepare( "insert into z3950servers
795 values ('z3950.loc.gov',
799 'Library of Congress',
804 unless ( $existingtables{'issuingrules'} ) {
805 $dbh->do("alter table categoryitem rename issuingrules");
806 print "renaming categoryitem\n";
810 #---------------------------------
813 foreach $table ( keys %requirefields ) {
814 print "Check table $table\n" if $debug;
815 $sth = $dbh->prepare("show columns from $table");
818 while ( ( $column, $type, $null, $key, $default, $extra ) = $sth->fetchrow )
820 $types{$column} = $type;
822 foreach $column ( keys %{ $requirefields{$table} } ) {
823 print " Check column $column [$types{$column}]\n" if $debug;
824 if ( !$types{$column} ) {
826 # column doesn't exist
827 print "Adding $column field to $table table...\n";
828 $query = "alter table $table
829 add column $column " . $requirefields{$table}->{$column};
830 print "Execute: $query\n" if $debug;
831 my $sti = $dbh->prepare($query);
834 print "**Error : $sti->errstr \n";
841 foreach $table ( keys %fielddefinitions ) {
842 print "Check table $table\n" if $debug;
843 $sth = $dbh->prepare("show columns from $table");
846 while ( ( $column, $type, $null, $key, $default, $extra ) = $sth->fetchrow )
848 $definitions->{$column}->{type} = $type;
849 $definitions->{$column}->{null} = $null;
850 $definitions->{$column}->{key} = $key;
851 $definitions->{$column}->{default} = $default;
852 $definitions->{$column}->{extra} = $extra;
854 my $fieldrow = $fielddefinitions{$table};
855 foreach my $row (@$fieldrow) {
856 my $field = $row->{field};
857 my $type = $row->{type};
858 my $null = $row->{null};
859 my $key = $row->{key};
860 my $default = $row->{default};
861 $default="''" unless $default;
862 my $extra = $row->{extra};
863 my $def = $definitions->{$field};
864 unless ( $type eq $def->{type}
865 && $null eq $def->{null}
866 && $key eq $def->{key}
867 && $default eq $def->{default}
868 && $extra eq $def->{extra} )
874 if ( $key eq 'PRI' ) {
875 $key = 'PRIMARY KEY';
877 unless ( $extra eq 'auto_increment' ) {
880 # if it's a new column use "add", if it's an old one, use "change".
882 if ($definitions->{$field}->{type}) {
883 $action="change $field"
887 # if it's a primary key, drop the previous pk, before altering the table
889 if ($key ne 'PRIMARY KEY') {
890 $sth =$dbh->prepare("alter table $table $action $field $type $null $key $extra default ?");
892 $sth =$dbh->prepare("alter table $table drop primary key, $action $field $type $null $key $extra default ?");
894 $sth->execute($default);
895 print " Alter $field in $table\n";
900 # Get list of columns from borrowers table
903 $sth = $dbh->prepare("show columns from borrowers");
905 while ( my ( $column, $type, $null, $key, $default, $extra ) = $sth->fetchrow )
907 $itemtypes{$column} = $type;
908 $nullenabled{$column} = $null;
911 unless ( $itemtypes{'cardnumber'} eq 'varchar(20)' ) {
912 $itemtypes{'cardnumber'} =~ /varchar\((\d+)\)/;
914 if ( $oldlength < 16 ) {
915 print "Setting maximum cardnumber length to 16 (was $oldlength) and marking unique.\n";
918 "alter table borrowers change cardnumber cardnumber varchar(16)");
923 "alter table borrowers drop index cardnumber");
928 "alter table borrowers add unique(cardnumber)");
934 # Get list of columns from items table
935 $sth = $dbh->prepare("show columns from items");
937 while ( my ( $column, $type, $null, $key, $default, $extra ) = $sth->fetchrow )
939 $itemtypes{$column} = $type;
940 $nullenabled{$column} = $null;
943 unless ( $itemtypes{'barcode'} eq 'varchar(20)' ) {
944 $itemtypes{'barcode'} =~ /varchar\((\d+)\)/;
946 if ( $oldlength < 20 ) {
947 print "Setting maximum barcode length to 20 (was $oldlength).\n";
950 "alter table items change barcode barcode varchar(20)");
955 # dropping unique barcode index & setting barcode to null allowed.
957 $sth = $dbh->prepare("show index from items");
959 while ( my ( $table, $non_unique, $key_name, $Seq_in_index, $Column_name, $Collation, $cardinality, $sub_part, $Packed, $comment ) = $sth->fetchrow )
961 if ($key_name eq 'barcode' && $non_unique eq 0) {
962 print "dropping BARCODE index to enable empty barcodes\n";
963 $dbh->do("ALTER TABLE `items` DROP INDEX `barcode`");
966 $dbh->do("ALTER TABLE `items` CHANGE `barcode` `barcode` VARCHAR( 20 )") unless ($nullenabled{barcode} eq 'YES');
969 # creating fulltext index in bibliothesaurus if needed
971 $sth = $dbh->prepare("show index from bibliothesaurus");
974 while ( my ( $table, $non_unique, $key_name, $Seq_in_index, $Column_name, $Collation, $cardinality, $sub_part, $Packed, $comment ) = $sth->fetchrow )
976 if ($key_name eq 'category_2') {
980 print "Creating fulltext index on bibliothesaurus\n" unless $exists;
981 $dbh->do('create fulltext index category_2 on bibliothesaurus (category,freelib)') unless $exists;
984 # creating index in z3950results if needed
986 $sth = $dbh->prepare("show index from z3950results");
989 while ( my ( $table, $non_unique, $key_name, $Seq_in_index, $Column_name, $Collation, $cardinality, $sub_part, $Packed, $comment ) = $sth->fetchrow )
991 if ($key_name eq 'query_server') {
995 print "Creating index on z3950results\n" unless $exists;
996 $dbh->do('create unique index query_server on z3950results (queryid,server)') unless $exists;
998 # changing z3950daemon field to NULL in marc_breeding
999 $dbh->do("ALTER TABLE `marc_breeding` CHANGE `z3950random` `z3950random` VARCHAR( 40 )");
1001 # making borrowernumber an auto_increment field
1002 $dbh->do("ALTER TABLE `borrowers` CHANGE `borrowernumber` `borrowernumber` INTEGER auto_increment");
1004 # extending the timestamp in branchtransfers...
1005 my %branchtransfers;
1007 $sth = $dbh->prepare("show columns from branchtransfers");
1009 while ( my ( $column, $type, $null, $key, $default, $extra ) = $sth->fetchrow )
1011 $branchtransfers{$column} = $type;
1014 unless ( $branchtransfers{'datesent'} eq 'datetime' ) {
1015 print "Setting type of datesent in branchtransfers to datetime.\n";
1018 "alter table branchtransfers change datesent datesent datetime");
1022 unless ( $branchtransfers{'datearrived'} eq 'datetime' ) {
1023 print "Setting type of datearrived in branchtransfers to datetime.\n";
1026 "alter table branchtransfers change datearrived datearrived datetime");
1030 # changing the branchcategories table around...
1031 my %branchcategories;
1033 $sth = $dbh->prepare("show columns from branchcategories");
1035 while ( my ( $column, $type, $null, $key, $default, $extra ) = $sth->fetchrow )
1037 $branchcategories{$column} = $type;
1040 unless ( $branchcategories{'categorycode'} eq 'varchar(4)' ) {
1042 "Setting type of categorycode in branchcategories to varchar(4),\n and making the primary key.\n";
1045 "alter table branchcategories change categorycode categorycode varchar(4) not null"
1050 "alter table branchcategories add primary key (categorycode)");
1054 unless ( $branchcategories{'categoryname'} eq 'text' ) {
1055 print "Changing branchcode in branchcategories to categoryname text.\n";
1058 "alter table branchcategories change branchcode categoryname text");
1062 unless ( $branchcategories{'codedescription'} eq 'text' ) {
1064 "Replacing branchholding in branchcategories with codedescription text.\n";
1067 "alter table branchcategories change branchholding codedescription text"
1072 # changing the items table around...
1075 $sth = $dbh->prepare("show columns from items");
1077 while ( my ( $column, $type, $null, $key, $default, $extra ) = $sth->fetchrow )
1079 $items{$column} = $type;
1082 if ($items{'bulk'} eq "varchar(30)") {
1083 print " Setting callnumber in items table\n";
1085 $dbh->prepare("ALTER TABLE `items` CHANGE `bulk` `itemcallnumber` VARCHAR( 30 ) DEFAULT NULL");
1087 $sti = $dbh->prepare("update marc_subfield_structure set kohafield=\"items.itemcallnumber\" where kohafield=\"items.bulk\"");
1092 # creating index in issuingrules if needed
1094 $sth = $dbh->prepare("show index from issuingrules");
1097 while ( my ( $table, $non_unique, $key_name, $Seq_in_index, $Column_name, $Collation, $cardinality, $sub_part, $Packed, $comment ) = $sth->fetchrow )
1099 if ($key_name eq 'PRIMARY') {
1103 print "Creating index on z3950results\n" unless $exists;
1104 $dbh->do('ALTER TABLE issuingrules ADD PRIMARY KEY ( branchcode, categorycode, itemtype )') unless $exists;
1106 $dbh->do('ALTER TABLE marc_tag_structure drop primary key');
1107 $dbh->do('ALTER TABLE marc_tag_structure ADD PRIMARY KEY ( itemtype, tagfield )');
1109 $dbh->do('ALTER TABLE marc_subfield_structure drop primary key');
1110 $dbh->do('ALTER TABLE marc_subfield_structure ADD PRIMARY KEY ( itemtype, tagfield, tagsubfield )');
1112 # Populate tables with required data
1114 foreach my $table ( keys %tabledata ) {
1115 print "Checking for data required in table $table...\n";
1116 my $tablerows = $tabledata{$table};
1117 foreach my $row (@$tablerows) {
1118 my $uniquefieldrequired = $row->{uniquefieldrequired};
1119 my $uniquevalue = $row->{$uniquefieldrequired};
1120 my $forceupdate = $row->{forceupdate};
1123 "select $uniquefieldrequired from $table where $uniquefieldrequired=?"
1125 $sth->execute($uniquevalue);
1127 foreach my $field (keys %$forceupdate) {
1128 if ($forceupdate->{$field}) {
1129 my $sth=$dbh->prepare("update systempreferences set $field=? where $uniquefieldrequired=?");
1130 $sth->execute($row->{$field}, $uniquevalue);
1134 print "Adding row to $table: ";
1138 foreach my $field ( keys %$row ) {
1139 next if $field eq 'uniquefieldrequired';
1140 next if $field eq 'forceupdate';
1141 my $value = $row->{$field};
1142 push @values, $value;
1143 print " $field => $value";
1144 $fieldlist .= "$field,";
1145 $placeholders .= "?,";
1148 $fieldlist =~ s/,$//;
1149 $placeholders =~ s/,$//;
1152 "insert into $table ($fieldlist) values ($placeholders)");
1153 $sth->execute(@values);
1163 # Revision 1.81 2004/05/28 09:56:21 tipaul
1166 # Revision 1.80 2004/05/28 08:32:00 tipaul
1168 # * MARC authority file
1169 # * seealso & hidden in MARC biblio structure.
1171 # Revision 1.79 2004/05/18 09:50:07 tipaul
1172 # *** empty log message ***
1174 # Revision 1.78 2004/05/10 09:29:33 tipaul
1175 # css is now the default theme for OPAC.
1176 # It will be the theme used for improvements and new things in OPAC.
1178 # Revision 1.77 2004/05/06 14:56:51 tipaul
1179 # adding table issuingrules (previously called categoryitem
1181 # Revision 1.76 2004/05/03 09:32:25 tipaul
1182 # adding printcirculationsplit parameter (already existed, but was not in systempref by defaul)
1184 # Revision 1.75 2004/04/14 19:49:00 tipaul
1185 # seealso field set to 255 chars
1187 # Revision 1.74 2004/03/11 16:10:16 tipaul
1188 # *** empty log message ***
1190 # Revision 1.73 2004/03/06 20:26:13 tipaul
1191 # adding seealso feature in MARC searches