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 aqbudget => {'aqbudgetid' => 'tinyint(4) auto_increment primary key'},
336 items => {'paidfor' => 'text'},
338 #added so that reference items are not available for reserves...
339 itemtypes => { 'notforloan' => 'smallint(6)' },
340 systempreferences => { 'explanation' => 'char(80)',
341 'type' => 'char(20)',
342 'options' => 'text' },
343 z3950servers => { 'syntax' => 'char(80)' },
344 marc_tag_structure =>{
345 'frameworkcode' => 'char(4) not NULL default \'\''},
346 marc_subfield_structure =>{'seealso' => 'char(255)',
347 'frameworkcode' => 'char(4) not NULL default \'\'',
348 'hidden' => 'tinyint(1)',
349 'isurl' => 'tinyint(1)',
351 bookshelf => {'owner' => 'char(80)',
352 'category' => 'char(1)',
354 marc_biblio => { 'frameworkcode' => 'char(4) not NULL default \'\'' },
357 my %dropable_table = (
358 classification => 'classification',
359 multipart => 'multipart',
360 multivolume => 'multivolume',
361 newitems => 'newitems',
362 procedures => 'procedures',
363 publisher => 'publisher',
364 searchstats => 'searchstats',
365 serialissues => 'serialissues',
368 # the other hash contains other actions that can't be done elsewhere. they are done
369 # either BEFORE of AFTER everything else, depending on "when" entry (default => AFTER)
371 # The tabledata hash contains data that should be in the tables.
372 # The uniquefieldrequired hash entry is used to determine which (if any) fields
373 # must not exist in the table for this row to be inserted. If the
374 # uniquefieldrequired entry is already in the table, the existing data is not
375 # modified, unless the forceupdate hash entry is also set. Fields in the
376 # anonymous "forceupdate" hash will be forced to be updated to the default
377 # values given in the %tabledata hash.
382 uniquefieldrequired => 'bit',
384 flag => 'superlibrarian',
385 flagdesc => 'Access to all librarian functions',
389 uniquefieldrequired => 'bit',
392 flagdesc => 'Circulate books',
396 uniquefieldrequired => 'bit',
399 flagdesc => 'View Catalogue (Librarian Interface)',
403 uniquefieldrequired => 'bit',
405 flag => 'parameters',
406 flagdesc => 'Set Koha system paramters',
410 uniquefieldrequired => 'bit',
413 flagdesc => 'Add or modify borrowers',
417 uniquefieldrequired => 'bit',
419 flag => 'permissions',
420 flagdesc => 'Set user permissions',
424 uniquefieldrequired => 'bit',
426 flag => 'reserveforothers',
427 flagdesc => 'Reserve books for patrons',
431 uniquefieldrequired => 'bit',
434 flagdesc => 'Borrow books',
438 uniquefieldrequired => 'bit',
440 flag => 'reserveforself',
441 flagdesc => 'Reserve books for self',
445 uniquefieldrequired => 'bit',
447 flag => 'editcatalogue',
448 flagdesc => 'Edit Catalogue (Modify bibliographic/holdings data)',
452 uniquefieldrequired => 'bit',
454 flag => 'updatecharges',
455 flagdesc => 'Update borrower charges',
459 systempreferences => [
461 uniquefieldrequired => 'variable',
462 forceupdate => { 'explanation' => 1,
464 variable => 'LibraryName',
465 value => '<i><b>Koha<br/>Free Software ILS<br/><br/></b>Koha : a gift, a contribution<br/> in Maori</i>',
466 explanation => 'Library name as shown on main opac page',
471 uniquefieldrequired => 'variable',
472 forceupdate => { 'explanation' => 1,
474 variable => 'autoMemberNum',
476 explanation => 'Member number is auto-calculated',
481 uniquefieldrequired => 'variable',
482 forceupdate => { 'explanation' => 1,
485 variable => 'acquisitions',
488 'Normal, budget-based acquisitions, or Simple bibliographic-data acquisitions',
490 options => 'simple|normal'
493 uniquefieldrequired => 'variable',
494 forceupdate => { 'explanation' => 1,
497 variable => 'dateformat',
500 'date format (us mm/dd/yyyy, metric dd/mm/yyy, ISO yyyy/mm/dd)',
502 options => 'metric|us|iso'
505 uniquefieldrequired => 'variable',
506 variable => 'template',
507 forceupdate => { 'explanation' => 1,
510 explanation => 'Preference order for intranet interface templates',
514 uniquefieldrequired => 'variable',
515 variable => 'autoBarcode',
516 forceupdate => { 'explanation' => 1,
519 explanation => 'Barcode is auto-calculated',
523 uniquefieldrequired => 'variable',
524 variable => 'insecure',
525 forceupdate => { 'explanation' => 1,
529 'If YES, no auth at all is needed. Be careful if you set this to yes!',
533 uniquefieldrequired => 'variable',
534 variable => 'authoritysep',
535 forceupdate => { 'explanation' => 1,
540 'the separator used in authority/thesaurus. Usually --',
545 uniquefieldrequired => 'variable',
546 variable => 'opaclanguages',
547 forceupdate => { 'explanation' => 1,
550 explanation => 'Set the preferred order for translations. The top language will be tried first.',
554 uniquefieldrequired => 'variable',
555 variable => 'opacthemes',
556 forceupdate => { 'explanation' => 1,
559 explanation => 'Set the preferred order for themes. The top theme will be tried first.',
563 uniquefieldrequired => 'variable',
564 variable => 'timeout',
565 forceupdate => { 'explanation' => 1,
568 explanation => 'Inactivity timeout for cookies authentication (in seconds)',
572 uniquefieldrequired => 'variable',
574 forceupdate => { 'explanation' => 1,
577 explanation => 'Turn on MARC support',
581 uniquefieldrequired => 'variable',
582 variable => 'marcflavour',
583 forceupdate => { 'explanation' => 1,
588 'your MARC flavor (MARC21 or UNIMARC) used for character encoding',
590 options => 'MARC21|UNIMARC'
593 uniquefieldrequired => 'variable',
594 variable => 'checkdigit',
596 forceupdate => { 'explanation' => 1,
599 explanation => 'Validity checks on membership number: none or "Katipo" style checks',
601 options => 'none|katipo'
604 uniquefieldrequired => 'variable',
605 variable => 'maxoutstanding',
606 forceupdate => { 'explanation' => 1,
610 'maximum amount withstanding to be able make reserves ',
614 uniquefieldrequired => 'variable',
615 variable => 'maxreserves',
616 forceupdate => { 'explanation' => 1,
620 'maximum number of reserves a member can make',
625 uniquefieldrequired => 'variable',
626 variable => 'noissuescharge',
627 forceupdate => { 'explanation' => 1,
631 'maximum amount withstanding to be able to check out an item',
636 uniquefieldrequired => 'variable',
637 variable => 'KohaAdminEmailAddress',
638 forceupdate => { 'explanation' => 1,
640 value => 'your.mail@here',
641 explanation => 'the email address where borrowers modifs are sent',
645 uniquefieldrequired => 'variable',
647 forceupdate => { 'explanation' => 1,
650 explanation => 'the gist rate. NOT in %, but in numeric form (0.12 for 12%)',
654 uniquefieldrequired => 'variable',
655 variable => 'ldapserver',
656 forceupdate => { 'explanation' => 1,
659 explanation => 'your ldap server',
663 uniquefieldrequired => 'variable',
664 variable => 'ldapinfos',
665 forceupdate => { 'explanation' => 1,
668 explanation => 'ldap info. The ldap will be used in dn : uid=xxx, <ldapinfos>',
672 uniquefieldrequired => 'variable',
673 variable => 'printcirculationslips',
674 forceupdate => { 'explanation' => 1,
677 explanation => 'if set to 1, print circulation slips. If set to 0, don\'t',
681 uniquefieldrequired => 'variable',
682 variable => 'suggestion',
683 forceupdate => { 'explanation' => 1,
686 explanation => 'if set to 1, suggestions are activated in OPAC',
690 uniquefieldrequired => 'variable',
692 forceupdate => { 'explanation' => 1,
694 value => 'Fill with appropriate value...',
695 explanation => 'ISBD',
699 uniquefieldrequired => 'variable',
700 variable => 'virtualshelves',
701 forceupdate => { 'explanation' => 1,
704 explanation => 'Set virtual shelves management ON or OFF',
711 my %fielddefinitions = (
714 field => 'printername',
723 field => 'bookfundid',
732 field => 'aqbudgetid',
733 type => 'tinyint(4)',
737 extra => 'auto_increment'
747 extra => 'auto_increment'
752 field => 'z3950random',
753 type => 'varchar(40)',
761 type => 'varchar(40)',
775 # Get version of MySQL database engine.
776 my $mysqlversion = `mysqld --version`;
777 $mysqlversion =~ /Ver (\S*) /;
779 if ( $mysqlversion ge '3.23' ) {
780 print "Could convert to MyISAM database tables...\n" unless $silent;
783 #---------------------------------
786 # Collect all tables into a list
787 $sth = $dbh->prepare("show tables");
789 while ( my ($table) = $sth->fetchrow ) {
790 $existingtables{$table} = 1;
794 # Now add any missing tables
795 foreach $table ( keys %requiretables ) {
796 unless ( $existingtables{$table} ) {
797 print "Adding $table table...\n" unless $silent;
798 my $sth = $dbh->prepare("create table $table $requiretables{$table}");
801 print "Error : $sth->errstr \n";
807 # now drop useless tables
808 foreach $table ( keys %dropable_table ) {
809 if ( $existingtables{$table} ) {
810 print "Dropping unused table $table\n" if $debug and not $silent;
811 $dbh->do("drop table $table");
813 print "Error : $dbh->errstr \n";
817 unless ( $existingtables{'z3950servers'} ) {
818 #MJR: added syntax entries to close bug 624
819 print "Adding z3950servers table...\n" unless $silent;
820 my $sti = $dbh->prepare( "create table z3950servers (
833 $sti = $dbh->prepare( "insert into z3950servers
834 values ('z3950.loc.gov',
838 'Library of Congress',
843 unless ( $existingtables{'issuingrules'} ) {
844 $dbh->do("alter table categoryitem rename issuingrules");
845 $dbh->do("ALTER TABLE issuingrules ADD maxissueqty int(4) default NULL");
846 $dbh->do("ALTER TABLE issuingrules ADD issuelength int(4) default NULL");
847 $dbh->do("ALTER TABLE issuingrules ADD branchcode varchar(4) NOT NULL default ''");
848 print "renaming categoryitem\n" unless $silent;
852 #---------------------------------
855 foreach $table ( keys %requirefields ) {
856 print "Check table $table\n" if $debug and not $silent;
857 $sth = $dbh->prepare("show columns from $table");
860 while ( ( $column, $type, $null, $key, $default, $extra ) = $sth->fetchrow )
862 $types{$column} = $type;
864 foreach $column ( keys %{ $requirefields{$table} } ) {
865 print " Check column $column [$types{$column}]\n" if $debug and not $silent;
866 if ( !$types{$column} ) {
868 # column doesn't exist
869 print "Adding $column field to $table table...\n" unless $silent;
870 $query = "alter table $table
871 add column $column " . $requirefields{$table}->{$column};
872 print "Execute: $query\n" if $debug;
873 my $sti = $dbh->prepare($query);
876 print "**Error : $sti->errstr \n";
883 foreach $table ( keys %fielddefinitions ) {
884 print "Check table $table\n" if $debug;
885 $sth = $dbh->prepare("show columns from $table");
888 while ( ( $column, $type, $null, $key, $default, $extra ) = $sth->fetchrow )
890 $definitions->{$column}->{type} = $type;
891 $definitions->{$column}->{null} = $null;
892 $definitions->{$column}->{key} = $key;
893 $definitions->{$column}->{default} = $default;
894 $definitions->{$column}->{extra} = $extra;
896 my $fieldrow = $fielddefinitions{$table};
897 foreach my $row (@$fieldrow) {
898 my $field = $row->{field};
899 my $type = $row->{type};
900 my $null = $row->{null};
901 my $key = $row->{key};
902 my $default = $row->{default};
903 $default="''" unless $default;
904 my $extra = $row->{extra};
905 my $def = $definitions->{$field};
906 unless ( $type eq $def->{type}
907 && $null eq $def->{null}
908 && $key eq $def->{key}
909 && $default eq $def->{default}
910 && $extra eq $def->{extra} )
916 if ( $key eq 'PRI' ) {
917 $key = 'PRIMARY KEY';
919 unless ( $extra eq 'auto_increment' ) {
922 # if it's a new column use "add", if it's an old one, use "change".
924 if ($definitions->{$field}->{type}) {
925 $action="change $field"
929 # if it's a primary key, drop the previous pk, before altering the table
931 if ($key ne 'PRIMARY KEY') {
932 $sth =$dbh->prepare("alter table $table $action $field $type $null $key $extra default ?");
934 $sth =$dbh->prepare("alter table $table drop primary key, $action $field $type $null $key $extra default ?");
936 $sth->execute($default);
937 print " Alter $field in $table\n" unless $silent;
942 # Get list of columns from borrowers table
945 $sth = $dbh->prepare("show columns from borrowers");
947 while ( my ( $column, $type, $null, $key, $default, $extra ) = $sth->fetchrow )
949 $itemtypes{$column} = $type;
950 $nullenabled{$column} = $null;
953 unless ( $itemtypes{'cardnumber'} eq 'varchar(20)' ) {
954 $itemtypes{'cardnumber'} =~ /varchar\((\d+)\)/;
956 if ( $oldlength < 16 ) {
957 print "Setting maximum cardnumber length to 16 (was $oldlength) and marking unique.\n" unless $silent;
960 "alter table borrowers change cardnumber cardnumber varchar(16)");
965 "alter table borrowers drop index cardnumber");
970 "alter table borrowers add unique(cardnumber)");
976 # Get list of columns from items table
977 $sth = $dbh->prepare("show columns from items");
979 while ( my ( $column, $type, $null, $key, $default, $extra ) = $sth->fetchrow )
981 $itemtypes{$column} = $type;
982 $nullenabled{$column} = $null;
985 unless ( $itemtypes{'barcode'} eq 'varchar(20)' ) {
986 $itemtypes{'barcode'} =~ /varchar\((\d+)\)/;
988 if ( $oldlength < 20 ) {
989 print "Setting maximum barcode length to 20 (was $oldlength).\n" unless $silent;
992 "alter table items change barcode barcode varchar(20)");
997 # dropping unique barcode index & setting barcode to null allowed.
999 $sth = $dbh->prepare("show index from items");
1001 while ( my ( $table, $non_unique, $key_name, $Seq_in_index, $Column_name, $Collation, $cardinality, $sub_part, $Packed, $comment ) = $sth->fetchrow )
1003 if ($key_name eq 'barcode' && $non_unique eq 0) {
1004 print "dropping BARCODE index to enable empty barcodes\n" unless $silent;
1005 $dbh->do("ALTER TABLE `items` DROP INDEX `barcode`");
1008 $dbh->do("ALTER TABLE `items` CHANGE `barcode` `barcode` VARCHAR( 20 )") unless ($nullenabled{barcode} eq 'YES');
1011 # creating fulltext index in bibliothesaurus if needed
1013 $sth = $dbh->prepare("show index from bibliothesaurus");
1016 while ( my ( $table, $non_unique, $key_name, $Seq_in_index, $Column_name, $Collation, $cardinality, $sub_part, $Packed, $comment ) = $sth->fetchrow )
1018 if ($key_name eq 'category_2') {
1022 print "Creating fulltext index on bibliothesaurus\n" unless $exists or $silent;
1023 $dbh->do('create fulltext index category_2 on bibliothesaurus (category,freelib)') unless $exists;
1026 # creating index in z3950results if needed
1028 $sth = $dbh->prepare("show index from z3950results");
1031 while ( my ( $table, $non_unique, $key_name, $Seq_in_index, $Column_name, $Collation, $cardinality, $sub_part, $Packed, $comment ) = $sth->fetchrow )
1033 if ($key_name eq 'query_server') {
1037 print "Creating index on z3950results\n" unless $exists or $silent;
1038 $dbh->do('create unique index query_server on z3950results (queryid,server)') unless $exists;
1040 # changing z3950daemon field to NULL in marc_breeding
1041 $dbh->do("ALTER TABLE `marc_breeding` CHANGE `z3950random` `z3950random` VARCHAR( 40 )");
1043 # making borrowernumber an auto_increment field
1044 $dbh->do("ALTER TABLE `borrowers` CHANGE `borrowernumber` `borrowernumber` INTEGER auto_increment");
1046 # changing indexes in marc_*_structure to use frameworkcode
1047 $dbh->do('alter table marc_subfield_structure drop index tab');
1048 $dbh->do('create index tab on marc_subfield_structure (frameworkcode,tab)');
1049 $dbh->do('alter table marc_subfield_structure drop index kohafield');
1050 $dbh->do('create index kohafield on marc_subfield_structure (frameworkcode,kohafield)');
1053 # extending the timestamp in branchtransfers...
1054 my %branchtransfers;
1056 $sth = $dbh->prepare("show columns from branchtransfers");
1058 while ( my ( $column, $type, $null, $key, $default, $extra ) = $sth->fetchrow )
1060 $branchtransfers{$column} = $type;
1063 unless ( $branchtransfers{'datesent'} eq 'datetime' ) {
1064 print "Setting type of datesent in branchtransfers to datetime.\n" unless $silent;
1067 "alter table branchtransfers change datesent datesent datetime");
1071 unless ( $branchtransfers{'datearrived'} eq 'datetime' ) {
1072 print "Setting type of datearrived in branchtransfers to datetime.\n" unless $silent;
1075 "alter table branchtransfers change datearrived datearrived datetime");
1079 # changing the branchcategories table around...
1080 my %branchcategories;
1082 $sth = $dbh->prepare("show columns from branchcategories");
1084 while ( my ( $column, $type, $null, $key, $default, $extra ) = $sth->fetchrow )
1086 $branchcategories{$column} = $type;
1089 unless ( $branchcategories{'categorycode'} eq 'varchar(4)' ) {
1091 "Setting type of categorycode in branchcategories to varchar(4),\n and making the primary key.\n" unless $silent;
1094 "alter table branchcategories change categorycode categorycode varchar(4) not null"
1099 "alter table branchcategories add primary key (categorycode)");
1103 unless ( $branchcategories{'categoryname'} eq 'text' ) {
1104 print "Changing branchcode in branchcategories to categoryname text.\n" unless $silent;
1107 "alter table branchcategories change branchcode categoryname text");
1111 unless ( $branchcategories{'codedescription'} eq 'text' ) {
1113 "Replacing branchholding in branchcategories with codedescription text.\n" unless $silent;
1116 "alter table branchcategories change branchholding codedescription text"
1121 # changing the items table around...
1124 $sth = $dbh->prepare("show columns from items");
1126 while ( my ( $column, $type, $null, $key, $default, $extra ) = $sth->fetchrow )
1128 $items{$column} = $type;
1131 if ($items{'bulk'} eq "varchar(30)") {
1132 print " Setting callnumber in items table\n" unless $silent;
1134 $dbh->prepare("ALTER TABLE `items` CHANGE `bulk` `itemcallnumber` VARCHAR( 30 ) DEFAULT NULL");
1136 $sti = $dbh->prepare("update marc_subfield_structure set kohafield=\"items.itemcallnumber\" where kohafield=\"items.bulk\"");
1140 # changing the marc_subfield_structure table around...
1141 my %marc_subfield_structure;
1143 $sth = $dbh->prepare("show columns from marc_subfield_structure");
1145 while ( my ( $column, $type, $null, $key, $default, $extra ) = $sth->fetchrow )
1147 $marc_subfield_structure{$column} = $type;
1150 if ($marc_subfield_structure{thesaurus_category}) {
1151 print " changing thesaurus_category in marc_subfield_structure table\n" unless $silent;
1153 $dbh->prepare("ALTER TABLE marc_subfield_structure CHANGE `thesaurus_category` `authtypecode` VARCHAR(10 ) DEFAULT NULL");
1158 # creating index in issuingrules if needed
1160 $sth = $dbh->prepare("show index from issuingrules");
1163 while ( my ( $table, $non_unique, $key_name, $Seq_in_index, $Column_name, $Collation, $cardinality, $sub_part, $Packed, $comment ) = $sth->fetchrow )
1165 if ($key_name eq 'PRIMARY') {
1169 print "Creating index on issuing rules\n" unless $exists or $silent;
1170 $dbh->do('ALTER TABLE issuingrules ADD PRIMARY KEY ( branchcode, categorycode, itemtype )') unless $exists;
1172 $dbh->do('ALTER TABLE marc_tag_structure drop primary key');
1173 $dbh->do('ALTER TABLE marc_tag_structure ADD PRIMARY KEY ( frameworkcode, tagfield )');
1175 $dbh->do('ALTER TABLE marc_subfield_structure drop primary key');
1176 $dbh->do('ALTER TABLE marc_subfield_structure ADD PRIMARY KEY ( frameworkcode, tagfield, tagsubfield )');
1178 # Get list of columns from marc_word table
1181 $sth = $dbh->prepare("show columns from marc_word");
1183 while ( my ( $column, $type, $null, $key, $default, $extra ) = $sth->fetchrow )
1185 $marc_word{$column} = $type;
1186 $nullenabled{$column} = $null;
1188 if ($marc_word{subfieldid}) {
1189 #create field tagsubfield, copy tag+subfieldid, then drop tag and subfieldid
1190 print "Modifying marc_word (concat on tag and subfield for better perfs)\n" unless $silent;
1191 $dbh->do("ALTER TABLE `marc_word` ADD `tagsubfield` CHAR( 4 ) NOT NULL AFTER `bibid`");
1192 $dbh->do("update marc_word set tagsubfield=concat(tag,subfieldid)");
1193 $dbh->do("alter table marc_word drop tag");
1194 $dbh->do("alter table marc_word drop subfieldid");
1195 $dbh->do("create index Search_Marc on marc_word (tagsubfield,word)");
1197 # Populate tables with required data
1199 foreach my $table ( keys %tabledata ) {
1200 print "Checking for data required in table $table...\n" unless $silent;
1201 my $tablerows = $tabledata{$table};
1202 foreach my $row (@$tablerows) {
1203 my $uniquefieldrequired = $row->{uniquefieldrequired};
1204 my $uniquevalue = $row->{$uniquefieldrequired};
1205 my $forceupdate = $row->{forceupdate};
1208 "select $uniquefieldrequired from $table where $uniquefieldrequired=?"
1210 $sth->execute($uniquevalue);
1212 foreach my $field (keys %$forceupdate) {
1213 if ($forceupdate->{$field}) {
1214 my $sth=$dbh->prepare("update systempreferences set $field=? where $uniquefieldrequired=?");
1215 $sth->execute($row->{$field}, $uniquevalue);
1219 print "Adding row to $table: " unless $silent;
1223 foreach my $field ( keys %$row ) {
1224 next if $field eq 'uniquefieldrequired';
1225 next if $field eq 'forceupdate';
1226 my $value = $row->{$field};
1227 push @values, $value;
1228 print " $field => $value" unless $silent;
1229 $fieldlist .= "$field,";
1230 $placeholders .= "?,";
1232 print "\n" unless $silent;
1233 $fieldlist =~ s/,$//;
1234 $placeholders =~ s/,$//;
1237 "insert into $table ($fieldlist) values ($placeholders)");
1238 $sth->execute(@values);
1248 # Revision 1.88 2004/06/26 23:34:26 rangi
1251 # Revision 1.87 2004/06/23 13:03:09 tipaul
1252 # fixes in DB structure
1254 # Revision 1.86 2004/06/22 11:30:57 tipaul
1255 # adding -s (silent) flag, to have a silent install.
1256 # only updater will be verbose
1258 # Revision 1.85 2004/06/17 15:19:44 tipaul
1259 # missing Marc_Search index on marc_word
1261 # Revision 1.84 2004/06/17 08:25:21 tipaul
1262 # DB modifs : merging tag & subfield in marc_word table
1264 # Revision 1.83 2004/06/10 08:32:02 tipaul
1265 # MARC authority management (continued)
1267 # Revision 1.82 2004/06/03 12:46:58 tipaul
1268 # * frameworks and itemtypes are independant
1270 # 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.
1272 # Revision 1.81 2004/05/28 09:56:21 tipaul
1275 # Revision 1.80 2004/05/28 08:32:00 tipaul
1277 # * MARC authority file
1278 # * seealso & hidden in MARC biblio structure.
1280 # Revision 1.79 2004/05/18 09:50:07 tipaul
1281 # *** empty log message ***
1283 # Revision 1.78 2004/05/10 09:29:33 tipaul
1284 # css is now the default theme for OPAC.
1285 # It will be the theme used for improvements and new things in OPAC.
1287 # Revision 1.77 2004/05/06 14:56:51 tipaul
1288 # adding table issuingrules (previously called categoryitem
1290 # Revision 1.76 2004/05/03 09:32:25 tipaul
1291 # adding printcirculationsplit parameter (already existed, but was not in systempref by defaul)
1293 # Revision 1.75 2004/04/14 19:49:00 tipaul
1294 # seealso field set to 255 chars
1296 # Revision 1.74 2004/03/11 16:10:16 tipaul
1297 # *** empty log message ***
1299 # Revision 1.73 2004/03/06 20:26:13 tipaul
1300 # adding seealso feature in MARC searches