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 datecreated date NOT NULL default '0000-00-00',
255 datemodified date default NULL,
256 origincode char(20) default NULL,
257 PRIMARY KEY (authid),
258 KEY origincode (origincode),
260 auth_subfield_table => "(
261 subfieldid bigint(20) unsigned NOT NULL auto_increment,
262 authid bigint(20) unsigned NOT NULL default '0',
263 tag char(3) NOT NULL default '',
264 tagorder tinyint(4) NOT NULL default '1',
265 tag_indicator char(2) NOT NULL default '',
266 subfieldcode char(1) NOT NULL default '',
267 subfieldorder tinyint(4) NOT NULL default '1',
268 subfieldvalue varchar(255) default NULL,
269 PRIMARY KEY (subfieldid),
272 KEY subfieldcode (subfieldcode),
273 KEY subfieldvalue (subfieldvalue)
276 authid bigint(20) NOT NULL default '0',
277 tagsubfield char(4) NOT NULL default '',
278 tagorder tinyint(4) NOT NULL default '1',
279 subfieldorder tinyint(4) NOT NULL default '1',
280 word varchar(255) NOT NULL default '',
281 sndx_word varchar(255) NOT NULL default '',
283 KEY marc_search (tagsubfield,word),
285 KEY sndx_word (sndx_word)
289 my %requirefields = (
290 biblio => { 'abstract' => 'text' },
291 deletedbiblio => { 'abstract' => 'text', 'marc' => 'blob' },
292 deleteditems => { 'marc' => 'blob', 'paidfor' => 'text' },
294 'lccn' => 'char(25)',
295 'url' => 'varchar(255)',
298 deletedbiblioitems => {
299 'lccn' => 'char(25)',
300 'url' => 'varchar(255)',
303 branchtransfers => { 'datearrived' => 'datetime' },
304 statistics => { 'borrowernumber' => 'int(11)' },
306 'invoicedisc' => 'float(6,4)',
307 'nocalc' => 'int(11)'
310 'userid' => 'char(30)',
311 'password' => 'char(30)',
312 'flags' => 'int(11)',
313 'textmessaging' => 'varchar(30)',
314 'zipcode' => 'varchar(25)',
315 'homezipcode' => 'varchar(25)',
317 aqorders => { 'budgetdate' => 'date' },
318 aqbudget => {'aqbudgetid' => 'tinyint(4) auto_increment primary key'},
319 items => {'paidfor' => 'text'},
321 #added so that reference items are not available for reserves...
322 itemtypes => { 'notforloan' => 'smallint(6)' },
323 systempreferences => { 'explanation' => 'char(80)',
324 'type' => 'char(20)',
325 'options' => 'text' },
326 z3950servers => { 'syntax' => 'char(80)' },
327 marc_tag_structure =>{
328 'frameworkcode' => 'char(4) not NULL default \'\''},
329 marc_subfield_structure =>{'seealso' => 'char(255)',
330 'frameworkcode' => 'char(4) not NULL default \'\'',
331 'hidden' => 'tinyint(1)',
332 'isurl' => 'tinyint(1)',
334 bookshelf => {'owner' => 'char(80)',
335 'category' => 'char(1)',
337 marc_biblio => { 'frameworkcode' => 'char(4) not NULL default \'\'' },
340 my %dropable_table = (
341 classification => 'classification',
342 multipart => 'multipart',
343 multivolume => 'multivolume',
344 newitems => 'newitems',
345 procedures => 'procedures',
346 publisher => 'publisher',
347 searchstats => 'searchstats',
348 serialissues => 'serialissues',
351 # the other hash contains other actions that can't be done elsewhere. they are done
352 # either BEFORE of AFTER everything else, depending on "when" entry (default => AFTER)
354 # The tabledata hash contains data that should be in the tables.
355 # The uniquefieldrequired hash entry is used to determine which (if any) fields
356 # must not exist in the table for this row to be inserted. If the
357 # uniquefieldrequired entry is already in the table, the existing data is not
358 # modified, unless the forceupdate hash entry is also set. Fields in the
359 # anonymous "forceupdate" hash will be forced to be updated to the default
360 # values given in the %tabledata hash.
365 uniquefieldrequired => 'bit',
367 flag => 'superlibrarian',
368 flagdesc => 'Access to all librarian functions',
372 uniquefieldrequired => 'bit',
375 flagdesc => 'Circulate books',
379 uniquefieldrequired => 'bit',
382 flagdesc => 'View Catalogue (Librarian Interface)',
386 uniquefieldrequired => 'bit',
388 flag => 'parameters',
389 flagdesc => 'Set Koha system paramters',
393 uniquefieldrequired => 'bit',
396 flagdesc => 'Add or modify borrowers',
400 uniquefieldrequired => 'bit',
402 flag => 'permissions',
403 flagdesc => 'Set user permissions',
407 uniquefieldrequired => 'bit',
409 flag => 'reserveforothers',
410 flagdesc => 'Reserve books for patrons',
414 uniquefieldrequired => 'bit',
417 flagdesc => 'Borrow books',
421 uniquefieldrequired => 'bit',
423 flag => 'reserveforself',
424 flagdesc => 'Reserve books for self',
428 uniquefieldrequired => 'bit',
430 flag => 'editcatalogue',
431 flagdesc => 'Edit Catalogue (Modify bibliographic/holdings data)',
435 uniquefieldrequired => 'bit',
437 flag => 'updatecharges',
438 flagdesc => 'Update borrower charges',
442 systempreferences => [
444 uniquefieldrequired => 'variable',
445 forceupdate => { 'explanation' => 1,
447 variable => 'LibraryName',
448 value => '<i><b>Koha<br/>Free Software ILS<br/><br/></b>Koha : a gift, a contribution<br/> in Maori</i>',
449 explanation => 'Library name as shown on main opac page',
454 uniquefieldrequired => 'variable',
455 forceupdate => { 'explanation' => 1,
457 variable => 'autoMemberNum',
459 explanation => 'Member number is auto-calculated',
464 uniquefieldrequired => 'variable',
465 forceupdate => { 'explanation' => 1,
468 variable => 'acquisitions',
471 'Normal, budget-based acquisitions, or Simple bibliographic-data acquisitions',
473 options => 'simple|normal'
476 uniquefieldrequired => 'variable',
477 forceupdate => { 'explanation' => 1,
480 variable => 'dateformat',
483 'date format (us mm/dd/yyyy, metric dd/mm/yyy, ISO yyyy/mm/dd)',
485 options => 'metric|us|iso'
488 uniquefieldrequired => 'variable',
489 variable => 'template',
490 forceupdate => { 'explanation' => 1,
493 explanation => 'Preference order for intranet interface templates',
497 uniquefieldrequired => 'variable',
498 variable => 'autoBarcode',
499 forceupdate => { 'explanation' => 1,
502 explanation => 'Barcode is auto-calculated',
506 uniquefieldrequired => 'variable',
507 variable => 'insecure',
508 forceupdate => { 'explanation' => 1,
512 'If YES, no auth at all is needed. Be careful if you set this to yes!',
516 uniquefieldrequired => 'variable',
517 variable => 'authoritysep',
518 forceupdate => { 'explanation' => 1,
523 'the separator used in authority/thesaurus. Usually --',
528 uniquefieldrequired => 'variable',
529 variable => 'opaclanguages',
530 forceupdate => { 'explanation' => 1,
533 explanation => 'Set the preferred order for translations. The top language will be tried first.',
537 uniquefieldrequired => 'variable',
538 variable => 'opacthemes',
539 forceupdate => { 'explanation' => 1,
542 explanation => 'Set the preferred order for themes. The top theme will be tried first.',
546 uniquefieldrequired => 'variable',
547 variable => 'timeout',
548 forceupdate => { 'explanation' => 1,
551 explanation => 'Inactivity timeout for cookies authentication (in seconds)',
555 uniquefieldrequired => 'variable',
557 forceupdate => { 'explanation' => 1,
560 explanation => 'Turn on MARC support',
564 uniquefieldrequired => 'variable',
565 variable => 'marcflavour',
566 forceupdate => { 'explanation' => 1,
571 'your MARC flavor (MARC21 or UNIMARC) used for character encoding',
573 options => 'MARC21|UNIMARC'
576 uniquefieldrequired => 'variable',
577 variable => 'checkdigit',
579 forceupdate => { 'explanation' => 1,
582 explanation => 'Validity checks on membership number: none or "Katipo" style checks',
584 options => 'none|katipo'
587 uniquefieldrequired => 'variable',
588 variable => 'maxoutstanding',
589 forceupdate => { 'explanation' => 1,
593 'maximum amount withstanding to be able make reserves ',
597 uniquefieldrequired => 'variable',
598 variable => 'maxreserves',
599 forceupdate => { 'explanation' => 1,
603 'maximum number of reserves a member can make',
608 uniquefieldrequired => 'variable',
609 variable => 'noissuescharge',
610 forceupdate => { 'explanation' => 1,
614 'maximum amount withstanding to be able to check out an item',
619 uniquefieldrequired => 'variable',
620 variable => 'KohaAdminEmailAddress',
621 forceupdate => { 'explanation' => 1,
623 value => 'your.mail@here',
624 explanation => 'the email address where borrowers modifs are sent',
628 uniquefieldrequired => 'variable',
630 forceupdate => { 'explanation' => 1,
633 explanation => 'the gist rate. NOT in %, but in numeric form (0.12 for 12%)',
637 uniquefieldrequired => 'variable',
638 variable => 'ldapserver',
639 forceupdate => { 'explanation' => 1,
642 explanation => 'your ldap server',
646 uniquefieldrequired => 'variable',
647 variable => 'ldapinfos',
648 forceupdate => { 'explanation' => 1,
651 explanation => 'ldap info. The ldap will be used in dn : uid=xxx, <ldapinfos>',
655 uniquefieldrequired => 'variable',
656 variable => 'printcirculationslips',
657 forceupdate => { 'explanation' => 1,
660 explanation => 'if set to 1, print circulation slips. If set to 0, don\'t',
664 uniquefieldrequired => 'variable',
665 variable => 'suggestion',
666 forceupdate => { 'explanation' => 1,
669 explanation => 'if set to 1, suggestions are activated in OPAC',
673 uniquefieldrequired => 'variable',
675 forceupdate => { 'explanation' => 1,
677 value => 'Fill with appropriate value...',
678 explanation => 'ISBD',
682 uniquefieldrequired => 'variable',
683 variable => 'virtualshelves',
684 forceupdate => { 'explanation' => 1,
687 explanation => 'Set virtual shelves management ON or OFF',
694 my %fielddefinitions = (
697 field => 'printername',
706 field => 'bookfundid',
715 field => 'aqbudgetid',
716 type => 'tinyint(4)',
720 extra => 'auto_increment'
730 extra => 'auto_increment'
735 field => 'z3950random',
736 type => 'varchar(40)',
744 type => 'varchar(40)',
758 # Get version of MySQL database engine.
759 my $mysqlversion = `mysqld --version`;
760 $mysqlversion =~ /Ver (\S*) /;
762 if ( $mysqlversion ge '3.23' ) {
763 print "Could convert to MyISAM database tables...\n" unless $silent;
766 #---------------------------------
769 # Collect all tables into a list
770 $sth = $dbh->prepare("show tables");
772 while ( my ($table) = $sth->fetchrow ) {
773 $existingtables{$table} = 1;
777 # Now add any missing tables
778 foreach $table ( keys %requiretables ) {
779 unless ( $existingtables{$table} ) {
780 print "Adding $table table...\n" unless $silent;
781 my $sth = $dbh->prepare("create table $table $requiretables{$table}");
784 print "Error : $sth->errstr \n";
790 # now drop useless tables
791 foreach $table ( keys %dropable_table ) {
792 if ( $existingtables{$table} ) {
793 print "Dropping unused table $table\n" if $debug and not $silent;
794 $dbh->do("drop table $table");
796 print "Error : $dbh->errstr \n";
800 unless ( $existingtables{'z3950servers'} ) {
801 #MJR: added syntax entries to close bug 624
802 print "Adding z3950servers table...\n" unless $silent;
803 my $sti = $dbh->prepare( "create table z3950servers (
816 $sti = $dbh->prepare( "insert into z3950servers
817 values ('z3950.loc.gov',
821 'Library of Congress',
826 unless ( $existingtables{'issuingrules'} ) {
827 $dbh->do("alter table categoryitem rename issuingrules");
828 print "renaming categoryitem\n" unless $silent;
832 #---------------------------------
835 foreach $table ( keys %requirefields ) {
836 print "Check table $table\n" if $debug and not $silent;
837 $sth = $dbh->prepare("show columns from $table");
840 while ( ( $column, $type, $null, $key, $default, $extra ) = $sth->fetchrow )
842 $types{$column} = $type;
844 foreach $column ( keys %{ $requirefields{$table} } ) {
845 print " Check column $column [$types{$column}]\n" if $debug and not $silent;
846 if ( !$types{$column} ) {
848 # column doesn't exist
849 print "Adding $column field to $table table...\n" unless $silent;
850 $query = "alter table $table
851 add column $column " . $requirefields{$table}->{$column};
852 print "Execute: $query\n" if $debug;
853 my $sti = $dbh->prepare($query);
856 print "**Error : $sti->errstr \n";
863 foreach $table ( keys %fielddefinitions ) {
864 print "Check table $table\n" if $debug;
865 $sth = $dbh->prepare("show columns from $table");
868 while ( ( $column, $type, $null, $key, $default, $extra ) = $sth->fetchrow )
870 $definitions->{$column}->{type} = $type;
871 $definitions->{$column}->{null} = $null;
872 $definitions->{$column}->{key} = $key;
873 $definitions->{$column}->{default} = $default;
874 $definitions->{$column}->{extra} = $extra;
876 my $fieldrow = $fielddefinitions{$table};
877 foreach my $row (@$fieldrow) {
878 my $field = $row->{field};
879 my $type = $row->{type};
880 my $null = $row->{null};
881 my $key = $row->{key};
882 my $default = $row->{default};
883 $default="''" unless $default;
884 my $extra = $row->{extra};
885 my $def = $definitions->{$field};
886 unless ( $type eq $def->{type}
887 && $null eq $def->{null}
888 && $key eq $def->{key}
889 && $default eq $def->{default}
890 && $extra eq $def->{extra} )
896 if ( $key eq 'PRI' ) {
897 $key = 'PRIMARY KEY';
899 unless ( $extra eq 'auto_increment' ) {
902 # if it's a new column use "add", if it's an old one, use "change".
904 if ($definitions->{$field}->{type}) {
905 $action="change $field"
909 # if it's a primary key, drop the previous pk, before altering the table
911 if ($key ne 'PRIMARY KEY') {
912 $sth =$dbh->prepare("alter table $table $action $field $type $null $key $extra default ?");
914 $sth =$dbh->prepare("alter table $table drop primary key, $action $field $type $null $key $extra default ?");
916 $sth->execute($default);
917 print " Alter $field in $table\n" unless $silent;
922 # Get list of columns from borrowers table
925 $sth = $dbh->prepare("show columns from borrowers");
927 while ( my ( $column, $type, $null, $key, $default, $extra ) = $sth->fetchrow )
929 $itemtypes{$column} = $type;
930 $nullenabled{$column} = $null;
933 unless ( $itemtypes{'cardnumber'} eq 'varchar(20)' ) {
934 $itemtypes{'cardnumber'} =~ /varchar\((\d+)\)/;
936 if ( $oldlength < 16 ) {
937 print "Setting maximum cardnumber length to 16 (was $oldlength) and marking unique.\n" unless $silent;
940 "alter table borrowers change cardnumber cardnumber varchar(16)");
945 "alter table borrowers drop index cardnumber");
950 "alter table borrowers add unique(cardnumber)");
956 # Get list of columns from items table
957 $sth = $dbh->prepare("show columns from items");
959 while ( my ( $column, $type, $null, $key, $default, $extra ) = $sth->fetchrow )
961 $itemtypes{$column} = $type;
962 $nullenabled{$column} = $null;
965 unless ( $itemtypes{'barcode'} eq 'varchar(20)' ) {
966 $itemtypes{'barcode'} =~ /varchar\((\d+)\)/;
968 if ( $oldlength < 20 ) {
969 print "Setting maximum barcode length to 20 (was $oldlength).\n" unless $silent;
972 "alter table items change barcode barcode varchar(20)");
977 # dropping unique barcode index & setting barcode to null allowed.
979 $sth = $dbh->prepare("show index from items");
981 while ( my ( $table, $non_unique, $key_name, $Seq_in_index, $Column_name, $Collation, $cardinality, $sub_part, $Packed, $comment ) = $sth->fetchrow )
983 if ($key_name eq 'barcode' && $non_unique eq 0) {
984 print "dropping BARCODE index to enable empty barcodes\n" unless $silent;
985 $dbh->do("ALTER TABLE `items` DROP INDEX `barcode`");
988 $dbh->do("ALTER TABLE `items` CHANGE `barcode` `barcode` VARCHAR( 20 )") unless ($nullenabled{barcode} eq 'YES');
991 # creating fulltext index in bibliothesaurus if needed
993 $sth = $dbh->prepare("show index from bibliothesaurus");
996 while ( my ( $table, $non_unique, $key_name, $Seq_in_index, $Column_name, $Collation, $cardinality, $sub_part, $Packed, $comment ) = $sth->fetchrow )
998 if ($key_name eq 'category_2') {
1002 print "Creating fulltext index on bibliothesaurus\n" unless $exists or $silent;
1003 $dbh->do('create fulltext index category_2 on bibliothesaurus (category,freelib)') unless $exists;
1006 # creating index in z3950results if needed
1008 $sth = $dbh->prepare("show index from z3950results");
1011 while ( my ( $table, $non_unique, $key_name, $Seq_in_index, $Column_name, $Collation, $cardinality, $sub_part, $Packed, $comment ) = $sth->fetchrow )
1013 if ($key_name eq 'query_server') {
1017 print "Creating index on z3950results\n" unless $exists or $silent;
1018 $dbh->do('create unique index query_server on z3950results (queryid,server)') unless $exists;
1020 # changing z3950daemon field to NULL in marc_breeding
1021 $dbh->do("ALTER TABLE `marc_breeding` CHANGE `z3950random` `z3950random` VARCHAR( 40 )");
1023 # making borrowernumber an auto_increment field
1024 $dbh->do("ALTER TABLE `borrowers` CHANGE `borrowernumber` `borrowernumber` INTEGER auto_increment");
1026 # changing indexes in marc_*_structure to use frameworkcode
1027 $dbh->do('alter table marc_subfield_structure drop index tab');
1028 $dbh->do('create index tab on marc_subfield_structure (frameworkcode,tab)');
1029 $dbh->do('alter table marc_subfield_structure drop index kohafield');
1030 $dbh->do('create index kohafield on marc_subfield_structure (frameworkcode,kohafield)');
1033 # extending the timestamp in branchtransfers...
1034 my %branchtransfers;
1036 $sth = $dbh->prepare("show columns from branchtransfers");
1038 while ( my ( $column, $type, $null, $key, $default, $extra ) = $sth->fetchrow )
1040 $branchtransfers{$column} = $type;
1043 unless ( $branchtransfers{'datesent'} eq 'datetime' ) {
1044 print "Setting type of datesent in branchtransfers to datetime.\n" unless $silent;
1047 "alter table branchtransfers change datesent datesent datetime");
1051 unless ( $branchtransfers{'datearrived'} eq 'datetime' ) {
1052 print "Setting type of datearrived in branchtransfers to datetime.\n" unless $silent;
1055 "alter table branchtransfers change datearrived datearrived datetime");
1059 # changing the branchcategories table around...
1060 my %branchcategories;
1062 $sth = $dbh->prepare("show columns from branchcategories");
1064 while ( my ( $column, $type, $null, $key, $default, $extra ) = $sth->fetchrow )
1066 $branchcategories{$column} = $type;
1069 unless ( $branchcategories{'categorycode'} eq 'varchar(4)' ) {
1071 "Setting type of categorycode in branchcategories to varchar(4),\n and making the primary key.\n" unless $silent;
1074 "alter table branchcategories change categorycode categorycode varchar(4) not null"
1079 "alter table branchcategories add primary key (categorycode)");
1083 unless ( $branchcategories{'categoryname'} eq 'text' ) {
1084 print "Changing branchcode in branchcategories to categoryname text.\n" unless $silent;
1087 "alter table branchcategories change branchcode categoryname text");
1091 unless ( $branchcategories{'codedescription'} eq 'text' ) {
1093 "Replacing branchholding in branchcategories with codedescription text.\n" unless $silent;
1096 "alter table branchcategories change branchholding codedescription text"
1101 # changing the items table around...
1104 $sth = $dbh->prepare("show columns from items");
1106 while ( my ( $column, $type, $null, $key, $default, $extra ) = $sth->fetchrow )
1108 $items{$column} = $type;
1111 if ($items{'bulk'} eq "varchar(30)") {
1112 print " Setting callnumber in items table\n" unless $silent;
1114 $dbh->prepare("ALTER TABLE `items` CHANGE `bulk` `itemcallnumber` VARCHAR( 30 ) DEFAULT NULL");
1116 $sti = $dbh->prepare("update marc_subfield_structure set kohafield=\"items.itemcallnumber\" where kohafield=\"items.bulk\"");
1120 # changing the marc_subfield_structure table around...
1121 my %marc_subfield_structure;
1123 $sth = $dbh->prepare("show columns from marc_subfield_structure");
1125 while ( my ( $column, $type, $null, $key, $default, $extra ) = $sth->fetchrow )
1127 $marc_subfield_structure{$column} = $type;
1130 if ($marc_subfield_structure{thesaurus_category}) {
1131 print " changing thesaurus_category in marc_subfield_structure table\n" unless $silent;
1133 $dbh->prepare("ALTER TABLE marc_subfield_structure CHANGE `thesaurus_category` `authtypecode` VARCHAR(10 ) DEFAULT NULL");
1138 # creating index in issuingrules if needed
1140 $sth = $dbh->prepare("show index from issuingrules");
1143 while ( my ( $table, $non_unique, $key_name, $Seq_in_index, $Column_name, $Collation, $cardinality, $sub_part, $Packed, $comment ) = $sth->fetchrow )
1145 if ($key_name eq 'PRIMARY') {
1149 print "Creating index on issuing rules\n" unless $exists or $silent;
1150 $dbh->do('ALTER TABLE issuingrules ADD PRIMARY KEY ( branchcode, categorycode, itemtype )') unless $exists;
1152 $dbh->do('ALTER TABLE marc_tag_structure drop primary key');
1153 $dbh->do('ALTER TABLE marc_tag_structure ADD PRIMARY KEY ( frameworkcode, tagfield )');
1155 $dbh->do('ALTER TABLE marc_subfield_structure drop primary key');
1156 $dbh->do('ALTER TABLE marc_subfield_structure ADD PRIMARY KEY ( frameworkcode, tagfield, tagsubfield )');
1158 # Get list of columns from marc_word table
1161 $sth = $dbh->prepare("show columns from marc_word");
1163 while ( my ( $column, $type, $null, $key, $default, $extra ) = $sth->fetchrow )
1165 $marc_word{$column} = $type;
1166 $nullenabled{$column} = $null;
1168 if ($marc_word{subfieldid}) {
1169 #create field tagsubfield, copy tag+subfieldid, then drop tag and subfieldid
1170 print "Modifying marc_word (concat on tag and subfield for better perfs)\n" unless $silent;
1171 $dbh->do("ALTER TABLE `marc_word` ADD `tagsubfield` CHAR( 4 ) NOT NULL AFTER `bibid`");
1172 $dbh->do("update marc_word set tagsubfield=concat(tag,subfieldid)");
1173 $dbh->do("alter table marc_word drop tag");
1174 $dbh->do("alter table marc_word drop subfieldid");
1175 $dbh->do("create index Search_Marc on marc_word (tagsubfield,word)");
1177 # Populate tables with required data
1179 foreach my $table ( keys %tabledata ) {
1180 print "Checking for data required in table $table...\n" unless $silent;
1181 my $tablerows = $tabledata{$table};
1182 foreach my $row (@$tablerows) {
1183 my $uniquefieldrequired = $row->{uniquefieldrequired};
1184 my $uniquevalue = $row->{$uniquefieldrequired};
1185 my $forceupdate = $row->{forceupdate};
1188 "select $uniquefieldrequired from $table where $uniquefieldrequired=?"
1190 $sth->execute($uniquevalue);
1192 foreach my $field (keys %$forceupdate) {
1193 if ($forceupdate->{$field}) {
1194 my $sth=$dbh->prepare("update systempreferences set $field=? where $uniquefieldrequired=?");
1195 $sth->execute($row->{$field}, $uniquevalue);
1199 print "Adding row to $table: " unless $silent;
1203 foreach my $field ( keys %$row ) {
1204 next if $field eq 'uniquefieldrequired';
1205 next if $field eq 'forceupdate';
1206 my $value = $row->{$field};
1207 push @values, $value;
1208 print " $field => $value" unless $silent;
1209 $fieldlist .= "$field,";
1210 $placeholders .= "?,";
1212 print "\n" unless $silent;
1213 $fieldlist =~ s/,$//;
1214 $placeholders =~ s/,$//;
1217 "insert into $table ($fieldlist) values ($placeholders)");
1218 $sth->execute(@values);
1228 # Revision 1.86 2004/06/22 11:30:57 tipaul
1229 # adding -s (silent) flag, to have a silent install.
1230 # only updater will be verbose
1232 # Revision 1.85 2004/06/17 15:19:44 tipaul
1233 # missing Marc_Search index on marc_word
1235 # Revision 1.84 2004/06/17 08:25:21 tipaul
1236 # DB modifs : merging tag & subfield in marc_word table
1238 # Revision 1.83 2004/06/10 08:32:02 tipaul
1239 # MARC authority management (continued)
1241 # Revision 1.82 2004/06/03 12:46:58 tipaul
1242 # * frameworks and itemtypes are independant
1244 # 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.
1246 # Revision 1.81 2004/05/28 09:56:21 tipaul
1249 # Revision 1.80 2004/05/28 08:32:00 tipaul
1251 # * MARC authority file
1252 # * seealso & hidden in MARC biblio structure.
1254 # Revision 1.79 2004/05/18 09:50:07 tipaul
1255 # *** empty log message ***
1257 # Revision 1.78 2004/05/10 09:29:33 tipaul
1258 # css is now the default theme for OPAC.
1259 # It will be the theme used for improvements and new things in OPAC.
1261 # Revision 1.77 2004/05/06 14:56:51 tipaul
1262 # adding table issuingrules (previously called categoryitem
1264 # Revision 1.76 2004/05/03 09:32:25 tipaul
1265 # adding printcirculationsplit parameter (already existed, but was not in systempref by defaul)
1267 # Revision 1.75 2004/04/14 19:49:00 tipaul
1268 # seealso field set to 255 chars
1270 # Revision 1.74 2004/03/11 16:10:16 tipaul
1271 # *** empty log message ***
1273 # Revision 1.73 2004/03/06 20:26:13 tipaul
1274 # adding seealso feature in MARC searches