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 PRIMARY KEY (authtypecode)
217 biblio_framework => "(
218 frameworkcode char(4) not NULL,
219 frameworktext char(255) not NULL,
220 PRIMARY KEY (frameworkcode)
222 auth_subfield_structure => "(
223 authtypecode char(10) NOT NULL default '',
224 tagfield char(3) NOT NULL default '',
225 tagsubfield char(1) NOT NULL default '',
226 liblibrarian char(255) NOT NULL default '',
227 libopac char(255) NOT NULL default '',
228 repeatable tinyint(4) NOT NULL default '0',
229 mandatory tinyint(4) NOT NULL default '0',
230 tab tinyint(1) default NULL,
231 authorised_value char(10) default NULL,
232 value_builder char(80) default NULL,
233 seealso char(255) default NULL,
234 PRIMARY KEY (authtypecode,tagfield,tagsubfield),
235 KEY tab (authtypecode,tab)
237 auth_tag_structure => "(
238 authtypecode char(10) NOT NULL default '',
239 tagfield char(3) NOT NULL default '',
240 liblibrarian char(255) NOT NULL default '',
241 libopac char(255) NOT NULL default '',
242 repeatable tinyint(4) NOT NULL default '0',
243 mandatory tinyint(4) NOT NULL default '0',
244 authorised_value char(10) default NULL,
245 PRIMARY KEY (authtypecode,tagfield)
248 authid bigint(20) unsigned NOT NULL auto_increment,
249 datecreated date NOT NULL default '0000-00-00',
250 datemodified date default NULL,
251 origincode char(20) default NULL,
252 PRIMARY KEY (authid),
253 KEY origincode (origincode),
255 auth_subfield_table => "(
256 subfieldid bigint(20) unsigned NOT NULL auto_increment,
257 authid bigint(20) unsigned NOT NULL default '0',
258 tag char(3) NOT NULL default '',
259 tagorder tinyint(4) NOT NULL default '1',
260 tag_indicator char(2) NOT NULL default '',
261 subfieldcode char(1) NOT NULL default '',
262 subfieldorder tinyint(4) NOT NULL default '1',
263 subfieldvalue varchar(255) default NULL,
264 valuebloblink bigint(20) default NULL,
265 PRIMARY KEY (subfieldid),
268 KEY tag_indicator (tag_indicator),
269 KEY subfieldorder (subfieldorder),
270 KEY subfieldcode (subfieldcode),
271 KEY subfieldvalue (subfieldvalue),
272 KEY tagorder (tagorder)
275 authid bigint(20) NOT NULL default '0',
276 tag char(3) NOT NULL default '',
277 tagorder tinyint(4) NOT NULL default '1',
278 subfieldid char(1) NOT NULL default '',
279 subfieldorder tinyint(4) NOT NULL default '1',
280 word varchar(255) NOT NULL default '',
281 sndx_word varchar(255) NOT NULL default '',
284 KEY tagorder (tagorder),
285 KEY subfieldid (subfieldid),
286 KEY subfieldorder (subfieldorder),
288 KEY sndx_word (sndx_word)
292 my %requirefields = (
293 biblio => { 'abstract' => 'text' },
294 deletedbiblio => { 'abstract' => 'text', 'marc' => 'blob' },
295 deleteditems => { 'marc' => 'blob', 'paidfor' => 'text' },
297 'lccn' => 'char(25)',
298 'url' => 'varchar(255)',
301 deletedbiblioitems => {
302 'lccn' => 'char(25)',
303 'url' => 'varchar(255)',
306 branchtransfers => { 'datearrived' => 'datetime' },
307 statistics => { 'borrowernumber' => 'int(11)' },
309 'invoicedisc' => 'float(6,4)',
310 'nocalc' => 'int(11)'
313 'userid' => 'char(30)',
314 'password' => 'char(30)',
315 'flags' => 'int(11)',
316 'textmessaging' => 'varchar(30)',
317 'zipcode' => 'varchar(25)',
318 'homezipcode' => 'varchar(25)',
320 aqorders => { 'budgetdate' => 'date' },
321 aqbudget => {'aqbudgetid' => 'tinyint(4) auto_increment primary key'},
322 items => {'paidfor' => 'text'},
324 #added so that reference items are not available for reserves...
325 itemtypes => { 'notforloan' => 'smallint(6)' },
326 systempreferences => { 'explanation' => 'char(80)',
327 'type' => 'char(20)',
328 'options' => 'text' },
329 z3950servers => { 'syntax' => 'char(80)' },
330 marc_tag_structure =>{
331 'frameworkcode' => 'char(4) not NULL default \'\''},
332 marc_subfield_structure =>{'seealso' => 'char(255)',
333 'frameworkcode' => 'char(4) not NULL default \'\'',
334 'hidden' => 'tinyint(1)',
335 'isurl' => 'tinyint(1)',
337 bookshelf => {'owner' => 'char(80)',
338 'category' => 'char(1)',
340 marc_biblio => { 'frameworkcode' => 'char(4) not NULL default \'\'' },
343 my %dropable_table = (
344 classification => 'classification',
345 multipart => 'multipart',
346 multivolume => 'multivolume',
347 newitems => 'newitems',
348 procedures => 'procedures',
349 publisher => 'publisher',
350 searchstats => 'searchstats',
351 serialissues => 'serialissues',
354 # the other hash contains other actions that can't be done elsewhere. they are done
355 # either BEFORE of AFTER everything else, depending on "when" entry (default => AFTER)
357 # The tabledata hash contains data that should be in the tables.
358 # The uniquefieldrequired hash entry is used to determine which (if any) fields
359 # must not exist in the table for this row to be inserted. If the
360 # uniquefieldrequired entry is already in the table, the existing data is not
361 # modified, unless the forceupdate hash entry is also set. Fields in the
362 # anonymous "forceupdate" hash will be forced to be updated to the default
363 # values given in the %tabledata hash.
368 uniquefieldrequired => 'bit',
370 flag => 'superlibrarian',
371 flagdesc => 'Access to all librarian functions',
375 uniquefieldrequired => 'bit',
378 flagdesc => 'Circulate books',
382 uniquefieldrequired => 'bit',
385 flagdesc => 'View Catalogue (Librarian Interface)',
389 uniquefieldrequired => 'bit',
391 flag => 'parameters',
392 flagdesc => 'Set Koha system paramters',
396 uniquefieldrequired => 'bit',
399 flagdesc => 'Add or modify borrowers',
403 uniquefieldrequired => 'bit',
405 flag => 'permissions',
406 flagdesc => 'Set user permissions',
410 uniquefieldrequired => 'bit',
412 flag => 'reserveforothers',
413 flagdesc => 'Reserve books for patrons',
417 uniquefieldrequired => 'bit',
420 flagdesc => 'Borrow books',
424 uniquefieldrequired => 'bit',
426 flag => 'reserveforself',
427 flagdesc => 'Reserve books for self',
431 uniquefieldrequired => 'bit',
433 flag => 'editcatalogue',
434 flagdesc => 'Edit Catalogue (Modify bibliographic/holdings data)',
438 uniquefieldrequired => 'bit',
440 flag => 'updatecharges',
441 flagdesc => 'Update borrower charges',
445 systempreferences => [
447 uniquefieldrequired => 'variable',
448 forceupdate => { 'explanation' => 1,
450 variable => 'LibraryName',
451 value => '<i><b>Koha<br/>Free Software ILS<br/><br/></b>Koha : a gift, a contribution<br/> in Maori</i>',
452 explanation => 'Library name as shown on main opac page',
457 uniquefieldrequired => 'variable',
458 forceupdate => { 'explanation' => 1,
460 variable => 'autoMemberNum',
462 explanation => 'Member number is auto-calculated',
467 uniquefieldrequired => 'variable',
468 forceupdate => { 'explanation' => 1,
471 variable => 'acquisitions',
474 'Normal, budget-based acquisitions, or Simple bibliographic-data acquisitions',
476 options => 'simple|normal'
479 uniquefieldrequired => 'variable',
480 forceupdate => { 'explanation' => 1,
483 variable => 'dateformat',
486 'date format (us mm/dd/yyyy, metric dd/mm/yyy, ISO yyyy/mm/dd)',
488 options => 'metric|us|iso'
491 uniquefieldrequired => 'variable',
492 variable => 'template',
493 forceupdate => { 'explanation' => 1,
496 explanation => 'Preference order for intranet interface templates',
500 uniquefieldrequired => 'variable',
501 variable => 'autoBarcode',
502 forceupdate => { 'explanation' => 1,
505 explanation => 'Barcode is auto-calculated',
509 uniquefieldrequired => 'variable',
510 variable => 'insecure',
511 forceupdate => { 'explanation' => 1,
515 'If YES, no auth at all is needed. Be careful if you set this to yes!',
519 uniquefieldrequired => 'variable',
520 variable => 'authoritysep',
521 forceupdate => { 'explanation' => 1,
526 'the separator used in authority/thesaurus. Usually --',
531 uniquefieldrequired => 'variable',
532 variable => 'opaclanguages',
533 forceupdate => { 'explanation' => 1,
536 explanation => 'Set the preferred order for translations. The top language will be tried first.',
540 uniquefieldrequired => 'variable',
541 variable => 'opacthemes',
542 forceupdate => { 'explanation' => 1,
545 explanation => 'Set the preferred order for themes. The top theme will be tried first.',
549 uniquefieldrequired => 'variable',
550 variable => 'timeout',
551 forceupdate => { 'explanation' => 1,
554 explanation => 'Inactivity timeout for cookies authentication (in seconds)',
558 uniquefieldrequired => 'variable',
560 forceupdate => { 'explanation' => 1,
563 explanation => 'Turn on MARC support',
567 uniquefieldrequired => 'variable',
568 variable => 'marcflavour',
569 forceupdate => { 'explanation' => 1,
574 'your MARC flavor (MARC21 or UNIMARC) used for character encoding',
576 options => 'MARC21|UNIMARC'
579 uniquefieldrequired => 'variable',
580 variable => 'checkdigit',
582 forceupdate => { 'explanation' => 1,
585 explanation => 'Validity checks on membership number: none or "Katipo" style checks',
587 options => 'none|katipo'
590 uniquefieldrequired => 'variable',
591 variable => 'maxoutstanding',
592 forceupdate => { 'explanation' => 1,
596 'maximum amount withstanding to be able make reserves ',
600 uniquefieldrequired => 'variable',
601 variable => 'maxreserves',
602 forceupdate => { 'explanation' => 1,
606 'maximum number of reserves a member can make',
611 uniquefieldrequired => 'variable',
612 variable => 'noissuescharge',
613 forceupdate => { 'explanation' => 1,
617 'maximum amount withstanding to be able to check out an item',
622 uniquefieldrequired => 'variable',
623 variable => 'KohaAdminEmailAddress',
624 forceupdate => { 'explanation' => 1,
626 value => 'your.mail@here',
627 explanation => 'the email address where borrowers modifs are sent',
631 uniquefieldrequired => 'variable',
633 forceupdate => { 'explanation' => 1,
636 explanation => 'the gist rate. NOT in %, but in numeric form (0.12 for 12%)',
640 uniquefieldrequired => 'variable',
641 variable => 'ldapserver',
642 forceupdate => { 'explanation' => 1,
645 explanation => 'your ldap server',
649 uniquefieldrequired => 'variable',
650 variable => 'ldapinfos',
651 forceupdate => { 'explanation' => 1,
654 explanation => 'ldap info. The ldap will be used in dn : uid=xxx, <ldapinfos>',
658 uniquefieldrequired => 'variable',
659 variable => 'printcirculationslips',
660 forceupdate => { 'explanation' => 1,
663 explanation => 'if set to 1, print circulation slips. If set to 0, don\'t',
667 uniquefieldrequired => 'variable',
668 variable => 'suggestion',
669 forceupdate => { 'explanation' => 1,
672 explanation => 'if set to 1, suggestions are activated in OPAC',
676 uniquefieldrequired => 'variable',
678 forceupdate => { 'explanation' => 1,
680 value => 'Fill with appropriate value...',
681 explanation => 'ISBD',
688 my %fielddefinitions = (
691 field => 'printername',
700 field => 'bookfundid',
709 field => 'aqbudgetid',
710 type => 'tinyint(4)',
714 extra => 'auto_increment'
724 extra => 'auto_increment'
729 field => 'z3950random',
730 type => 'varchar(40)',
738 type => 'varchar(40)',
752 # Get version of MySQL database engine.
753 my $mysqlversion = `mysqld --version`;
754 $mysqlversion =~ /Ver (\S*) /;
756 if ( $mysqlversion ge '3.23' ) {
757 print "Could convert to MyISAM database tables...\n";
760 #---------------------------------
763 # Collect all tables into a list
764 $sth = $dbh->prepare("show tables");
766 while ( my ($table) = $sth->fetchrow ) {
767 $existingtables{$table} = 1;
771 # Now add any missing tables
772 foreach $table ( keys %requiretables ) {
773 unless ( $existingtables{$table} ) {
774 print "Adding $table table...\n";
775 my $sth = $dbh->prepare("create table $table $requiretables{$table}");
778 print "Error : $sth->errstr \n";
784 # now drop useless tables
785 foreach $table ( keys %dropable_table ) {
786 if ( $existingtables{$table} ) {
787 print "Dropping unused table $table\n" if $debug;
788 $dbh->do("drop table $table");
790 print "Error : $dbh->errstr \n";
794 unless ( $existingtables{'z3950servers'} ) {
795 #MJR: added syntax entries to close bug 624
796 print "Adding z3950servers table...\n";
797 my $sti = $dbh->prepare( "create table z3950servers (
810 $sti = $dbh->prepare( "insert into z3950servers
811 values ('z3950.loc.gov',
815 'Library of Congress',
820 unless ( $existingtables{'issuingrules'} ) {
821 $dbh->do("alter table categoryitem rename issuingrules");
822 print "renaming categoryitem\n";
826 #---------------------------------
829 foreach $table ( keys %requirefields ) {
830 print "Check table $table\n" if $debug;
831 $sth = $dbh->prepare("show columns from $table");
834 while ( ( $column, $type, $null, $key, $default, $extra ) = $sth->fetchrow )
836 $types{$column} = $type;
838 foreach $column ( keys %{ $requirefields{$table} } ) {
839 print " Check column $column [$types{$column}]\n" if $debug;
840 if ( !$types{$column} ) {
842 # column doesn't exist
843 print "Adding $column field to $table table...\n";
844 $query = "alter table $table
845 add column $column " . $requirefields{$table}->{$column};
846 print "Execute: $query\n" if $debug;
847 my $sti = $dbh->prepare($query);
850 print "**Error : $sti->errstr \n";
857 foreach $table ( keys %fielddefinitions ) {
858 print "Check table $table\n" if $debug;
859 $sth = $dbh->prepare("show columns from $table");
862 while ( ( $column, $type, $null, $key, $default, $extra ) = $sth->fetchrow )
864 $definitions->{$column}->{type} = $type;
865 $definitions->{$column}->{null} = $null;
866 $definitions->{$column}->{key} = $key;
867 $definitions->{$column}->{default} = $default;
868 $definitions->{$column}->{extra} = $extra;
870 my $fieldrow = $fielddefinitions{$table};
871 foreach my $row (@$fieldrow) {
872 my $field = $row->{field};
873 my $type = $row->{type};
874 my $null = $row->{null};
875 my $key = $row->{key};
876 my $default = $row->{default};
877 $default="''" unless $default;
878 my $extra = $row->{extra};
879 my $def = $definitions->{$field};
880 unless ( $type eq $def->{type}
881 && $null eq $def->{null}
882 && $key eq $def->{key}
883 && $default eq $def->{default}
884 && $extra eq $def->{extra} )
890 if ( $key eq 'PRI' ) {
891 $key = 'PRIMARY KEY';
893 unless ( $extra eq 'auto_increment' ) {
896 # if it's a new column use "add", if it's an old one, use "change".
898 if ($definitions->{$field}->{type}) {
899 $action="change $field"
903 # if it's a primary key, drop the previous pk, before altering the table
905 if ($key ne 'PRIMARY KEY') {
906 $sth =$dbh->prepare("alter table $table $action $field $type $null $key $extra default ?");
908 $sth =$dbh->prepare("alter table $table drop primary key, $action $field $type $null $key $extra default ?");
910 $sth->execute($default);
911 print " Alter $field in $table\n";
916 # Get list of columns from borrowers table
919 $sth = $dbh->prepare("show columns from borrowers");
921 while ( my ( $column, $type, $null, $key, $default, $extra ) = $sth->fetchrow )
923 $itemtypes{$column} = $type;
924 $nullenabled{$column} = $null;
927 unless ( $itemtypes{'cardnumber'} eq 'varchar(20)' ) {
928 $itemtypes{'cardnumber'} =~ /varchar\((\d+)\)/;
930 if ( $oldlength < 16 ) {
931 print "Setting maximum cardnumber length to 16 (was $oldlength) and marking unique.\n";
934 "alter table borrowers change cardnumber cardnumber varchar(16)");
939 "alter table borrowers drop index cardnumber");
944 "alter table borrowers add unique(cardnumber)");
950 # Get list of columns from items table
951 $sth = $dbh->prepare("show columns from items");
953 while ( my ( $column, $type, $null, $key, $default, $extra ) = $sth->fetchrow )
955 $itemtypes{$column} = $type;
956 $nullenabled{$column} = $null;
959 unless ( $itemtypes{'barcode'} eq 'varchar(20)' ) {
960 $itemtypes{'barcode'} =~ /varchar\((\d+)\)/;
962 if ( $oldlength < 20 ) {
963 print "Setting maximum barcode length to 20 (was $oldlength).\n";
966 "alter table items change barcode barcode varchar(20)");
971 # dropping unique barcode index & setting barcode to null allowed.
973 $sth = $dbh->prepare("show index from items");
975 while ( my ( $table, $non_unique, $key_name, $Seq_in_index, $Column_name, $Collation, $cardinality, $sub_part, $Packed, $comment ) = $sth->fetchrow )
977 if ($key_name eq 'barcode' && $non_unique eq 0) {
978 print "dropping BARCODE index to enable empty barcodes\n";
979 $dbh->do("ALTER TABLE `items` DROP INDEX `barcode`");
982 $dbh->do("ALTER TABLE `items` CHANGE `barcode` `barcode` VARCHAR( 20 )") unless ($nullenabled{barcode} eq 'YES');
985 # creating fulltext index in bibliothesaurus if needed
987 $sth = $dbh->prepare("show index from bibliothesaurus");
990 while ( my ( $table, $non_unique, $key_name, $Seq_in_index, $Column_name, $Collation, $cardinality, $sub_part, $Packed, $comment ) = $sth->fetchrow )
992 if ($key_name eq 'category_2') {
996 print "Creating fulltext index on bibliothesaurus\n" unless $exists;
997 $dbh->do('create fulltext index category_2 on bibliothesaurus (category,freelib)') unless $exists;
1000 # creating index in z3950results if needed
1002 $sth = $dbh->prepare("show index from z3950results");
1005 while ( my ( $table, $non_unique, $key_name, $Seq_in_index, $Column_name, $Collation, $cardinality, $sub_part, $Packed, $comment ) = $sth->fetchrow )
1007 if ($key_name eq 'query_server') {
1011 print "Creating index on z3950results\n" unless $exists;
1012 $dbh->do('create unique index query_server on z3950results (queryid,server)') unless $exists;
1014 # changing z3950daemon field to NULL in marc_breeding
1015 $dbh->do("ALTER TABLE `marc_breeding` CHANGE `z3950random` `z3950random` VARCHAR( 40 )");
1017 # making borrowernumber an auto_increment field
1018 $dbh->do("ALTER TABLE `borrowers` CHANGE `borrowernumber` `borrowernumber` INTEGER auto_increment");
1020 # changing indexes in marc_*_structure to use frameworkcode
1021 $dbh->do('alter table marc_subfield_structure drop index tab');
1022 $dbh->do('create index tab on marc_subfield_structure (frameworkcode,tab)');
1023 $dbh->do('alter table marc_subfield_structure drop index kohafield');
1024 $dbh->do('create index kohafield on marc_subfield_structure (frameworkcode,kohafield)');
1027 # extending the timestamp in branchtransfers...
1028 my %branchtransfers;
1030 $sth = $dbh->prepare("show columns from branchtransfers");
1032 while ( my ( $column, $type, $null, $key, $default, $extra ) = $sth->fetchrow )
1034 $branchtransfers{$column} = $type;
1037 unless ( $branchtransfers{'datesent'} eq 'datetime' ) {
1038 print "Setting type of datesent in branchtransfers to datetime.\n";
1041 "alter table branchtransfers change datesent datesent datetime");
1045 unless ( $branchtransfers{'datearrived'} eq 'datetime' ) {
1046 print "Setting type of datearrived in branchtransfers to datetime.\n";
1049 "alter table branchtransfers change datearrived datearrived datetime");
1053 # changing the branchcategories table around...
1054 my %branchcategories;
1056 $sth = $dbh->prepare("show columns from branchcategories");
1058 while ( my ( $column, $type, $null, $key, $default, $extra ) = $sth->fetchrow )
1060 $branchcategories{$column} = $type;
1063 unless ( $branchcategories{'categorycode'} eq 'varchar(4)' ) {
1065 "Setting type of categorycode in branchcategories to varchar(4),\n and making the primary key.\n";
1068 "alter table branchcategories change categorycode categorycode varchar(4) not null"
1073 "alter table branchcategories add primary key (categorycode)");
1077 unless ( $branchcategories{'categoryname'} eq 'text' ) {
1078 print "Changing branchcode in branchcategories to categoryname text.\n";
1081 "alter table branchcategories change branchcode categoryname text");
1085 unless ( $branchcategories{'codedescription'} eq 'text' ) {
1087 "Replacing branchholding in branchcategories with codedescription text.\n";
1090 "alter table branchcategories change branchholding codedescription text"
1095 # changing the items table around...
1098 $sth = $dbh->prepare("show columns from items");
1100 while ( my ( $column, $type, $null, $key, $default, $extra ) = $sth->fetchrow )
1102 $items{$column} = $type;
1105 if ($items{'bulk'} eq "varchar(30)") {
1106 print " Setting callnumber in items table\n";
1108 $dbh->prepare("ALTER TABLE `items` CHANGE `bulk` `itemcallnumber` VARCHAR( 30 ) DEFAULT NULL");
1110 $sti = $dbh->prepare("update marc_subfield_structure set kohafield=\"items.itemcallnumber\" where kohafield=\"items.bulk\"");
1115 # creating index in issuingrules if needed
1117 $sth = $dbh->prepare("show index from issuingrules");
1120 while ( my ( $table, $non_unique, $key_name, $Seq_in_index, $Column_name, $Collation, $cardinality, $sub_part, $Packed, $comment ) = $sth->fetchrow )
1122 if ($key_name eq 'PRIMARY') {
1126 print "Creating index on z3950results\n" unless $exists;
1127 $dbh->do('ALTER TABLE issuingrules ADD PRIMARY KEY ( branchcode, categorycode, itemtype )') unless $exists;
1129 $dbh->do('ALTER TABLE marc_tag_structure drop primary key');
1130 $dbh->do('ALTER TABLE marc_tag_structure ADD PRIMARY KEY ( frameworkcode, tagfield )');
1132 $dbh->do('ALTER TABLE marc_subfield_structure drop primary key');
1133 $dbh->do('ALTER TABLE marc_subfield_structure ADD PRIMARY KEY ( frameworkcode, tagfield, tagsubfield )');
1135 # Populate tables with required data
1137 foreach my $table ( keys %tabledata ) {
1138 print "Checking for data required in table $table...\n";
1139 my $tablerows = $tabledata{$table};
1140 foreach my $row (@$tablerows) {
1141 my $uniquefieldrequired = $row->{uniquefieldrequired};
1142 my $uniquevalue = $row->{$uniquefieldrequired};
1143 my $forceupdate = $row->{forceupdate};
1146 "select $uniquefieldrequired from $table where $uniquefieldrequired=?"
1148 $sth->execute($uniquevalue);
1150 foreach my $field (keys %$forceupdate) {
1151 if ($forceupdate->{$field}) {
1152 my $sth=$dbh->prepare("update systempreferences set $field=? where $uniquefieldrequired=?");
1153 $sth->execute($row->{$field}, $uniquevalue);
1157 print "Adding row to $table: ";
1161 foreach my $field ( keys %$row ) {
1162 next if $field eq 'uniquefieldrequired';
1163 next if $field eq 'forceupdate';
1164 my $value = $row->{$field};
1165 push @values, $value;
1166 print " $field => $value";
1167 $fieldlist .= "$field,";
1168 $placeholders .= "?,";
1171 $fieldlist =~ s/,$//;
1172 $placeholders =~ s/,$//;
1175 "insert into $table ($fieldlist) values ($placeholders)");
1176 $sth->execute(@values);
1186 # Revision 1.82 2004/06/03 12:46:58 tipaul
1187 # * frameworks and itemtypes are independant
1189 # 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.
1191 # Revision 1.81 2004/05/28 09:56:21 tipaul
1194 # Revision 1.80 2004/05/28 08:32:00 tipaul
1196 # * MARC authority file
1197 # * seealso & hidden in MARC biblio structure.
1199 # Revision 1.79 2004/05/18 09:50:07 tipaul
1200 # *** empty log message ***
1202 # Revision 1.78 2004/05/10 09:29:33 tipaul
1203 # css is now the default theme for OPAC.
1204 # It will be the theme used for improvements and new things in OPAC.
1206 # Revision 1.77 2004/05/06 14:56:51 tipaul
1207 # adding table issuingrules (previously called categoryitem
1209 # Revision 1.76 2004/05/03 09:32:25 tipaul
1210 # adding printcirculationsplit parameter (already existed, but was not in systempref by defaul)
1212 # Revision 1.75 2004/04/14 19:49:00 tipaul
1213 # seealso field set to 255 chars
1215 # Revision 1.74 2004/03/11 16:10:16 tipaul
1216 # *** empty log message ***
1218 # Revision 1.73 2004/03/06 20:26:13 tipaul
1219 # adding seealso feature in MARC searches