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 (authtype,tagfield,tagsubfield),
229 KEY kohafield (kohafield),
232 auth_tag_structure => "(
233 authtypecode char(10) NOT NULL default '',
234 tagfield char(3) NOT NULL default '',
235 liblibrarian char(255) NOT NULL default '',
236 libopac char(255) NOT NULL default '',
237 repeatable tinyint(4) NOT NULL default '0',
238 mandatory tinyint(4) NOT NULL default '0',
239 authorised_value char(10) default NULL,
240 PRIMARY KEY (authtype,tagfield)
243 authid bigint(20) unsigned NOT NULL auto_increment,
244 datecreated date NOT NULL default '0000-00-00',
245 datemodified date default NULL,
246 origincode char(20) default NULL,
247 PRIMARY KEY (authid),
248 KEY origincode (origincode),
250 marc_subfield_table => "(
251 subfieldid bigint(20) unsigned NOT NULL auto_increment,
252 authid bigint(20) unsigned NOT NULL default '0',
253 tag char(3) NOT NULL default '',
254 tagorder tinyint(4) NOT NULL default '1',
255 tag_indicator char(2) NOT NULL default '',
256 subfieldcode char(1) NOT NULL default '',
257 subfieldorder tinyint(4) NOT NULL default '1',
258 subfieldvalue varchar(255) default NULL,
259 valuebloblink bigint(20) default NULL,
260 PRIMARY KEY (subfieldid),
263 KEY tag_indicator (tag_indicator),
264 KEY subfieldorder (subfieldorder),
265 KEY subfieldcode (subfieldcode),
266 KEY subfieldvalue (subfieldvalue),
267 KEY tagorder (tagorder)
270 authid bigint(20) NOT NULL default '0',
271 tag char(3) NOT NULL default '',
272 tagorder tinyint(4) NOT NULL default '1',
273 subfieldid char(1) NOT NULL default '',
274 subfieldorder tinyint(4) NOT NULL default '1',
275 word varchar(255) NOT NULL default '',
276 sndx_word varchar(255) NOT NULL default '',
279 KEY tagorder (tagorder),
280 KEY subfieldid (subfieldid),
281 KEY subfieldorder (subfieldorder),
283 KEY sndx_word (sndx_word)
287 my %requirefields = (
288 biblio => { 'abstract' => 'text' },
289 deletedbiblio => { 'abstract' => 'text', 'marc' => 'blob' },
290 deleteditems => { 'marc' => 'blob', 'paidfor' => 'text' },
292 'lccn' => 'char(25)',
293 'url' => 'varchar(255)',
296 deletedbiblioitems => {
297 'lccn' => 'char(25)',
298 'url' => 'varchar(255)',
301 branchtransfers => { 'datearrived' => 'datetime' },
302 statistics => { 'borrowernumber' => 'int(11)' },
304 'invoicedisc' => 'float(6,4)',
305 'nocalc' => 'int(11)'
308 'userid' => 'char(30)',
309 'password' => 'char(30)',
310 'flags' => 'int(11)',
311 'textmessaging' => 'varchar(30)',
312 'zipcode' => 'varchar(25)',
313 'homezipcode' => 'varchar(25)',
315 aqorders => { 'budgetdate' => 'date' },
316 aqbudget => {'aqbudgetid' => 'tinyint(4) auto_increment primary key'},
317 items => {'paidfor' => 'text'},
319 #added so that reference items are not available for reserves...
320 itemtypes => { 'notforloan' => 'smallint(6)' },
321 systempreferences => { 'explanation' => 'char(80)',
322 'type' => 'char(20)',
323 'options' => 'text' },
324 z3950servers => { 'syntax' => 'char(80)' },
325 marc_tag_structure =>{
326 'itemtype' => 'char(4) not NULL default \'\''},
327 marc_subfield_structure =>{'seealso' => 'char(255)',
328 'itemtype' => 'char(4) not NULL default \'\'',
329 'hidden' => 'tinyint(1)',
330 'isurl' => 'tinyint(1)',
332 bookshelf => {'owner' => 'char(80)',
333 'category' => 'char(1)',
337 my %dropable_table = (
338 classification => 'classification',
339 multipart => 'multipart',
340 multivolume => 'multivolume',
341 newitems => 'newitems',
342 procedures => 'procedures',
343 publisher => 'publisher',
344 searchstats => 'searchstats',
345 serialissues => 'serialissues',
348 # the other hash contains other actions that can't be done elsewhere. they are done
349 # either BEFORE of AFTER everything else, depending on "when" entry (default => AFTER)
351 # The tabledata hash contains data that should be in the tables.
352 # The uniquefieldrequired hash entry is used to determine which (if any) fields
353 # must not exist in the table for this row to be inserted. If the
354 # uniquefieldrequired entry is already in the table, the existing data is not
355 # modified, unless the forceupdate hash entry is also set. Fields in the
356 # anonymous "forceupdate" hash will be forced to be updated to the default
357 # values given in the %tabledata hash.
362 uniquefieldrequired => 'bit',
364 flag => 'superlibrarian',
365 flagdesc => 'Access to all librarian functions',
369 uniquefieldrequired => 'bit',
372 flagdesc => 'Circulate books',
376 uniquefieldrequired => 'bit',
379 flagdesc => 'View Catalogue (Librarian Interface)',
383 uniquefieldrequired => 'bit',
385 flag => 'parameters',
386 flagdesc => 'Set Koha system paramters',
390 uniquefieldrequired => 'bit',
393 flagdesc => 'Add or modify borrowers',
397 uniquefieldrequired => 'bit',
399 flag => 'permissions',
400 flagdesc => 'Set user permissions',
404 uniquefieldrequired => 'bit',
406 flag => 'reserveforothers',
407 flagdesc => 'Reserve books for patrons',
411 uniquefieldrequired => 'bit',
414 flagdesc => 'Borrow books',
418 uniquefieldrequired => 'bit',
420 flag => 'reserveforself',
421 flagdesc => 'Reserve books for self',
425 uniquefieldrequired => 'bit',
427 flag => 'editcatalogue',
428 flagdesc => 'Edit Catalogue (Modify bibliographic/holdings data)',
432 uniquefieldrequired => 'bit',
434 flag => 'updatecharges',
435 flagdesc => 'Update borrower charges',
439 systempreferences => [
441 uniquefieldrequired => 'variable',
442 forceupdate => { 'explanation' => 1,
444 variable => 'LibraryName',
445 value => '<i><b>Koha<br/>Free Software ILS<br/><br/></b>Koha : a gift, a contribution<br/> in Maori</i>',
446 explanation => 'Library name as shown on main opac page',
451 uniquefieldrequired => 'variable',
452 forceupdate => { 'explanation' => 1,
454 variable => 'autoMemberNum',
456 explanation => 'Member number is auto-calculated',
461 uniquefieldrequired => 'variable',
462 forceupdate => { 'explanation' => 1,
465 variable => 'acquisitions',
468 'Normal, budget-based acquisitions, or Simple bibliographic-data acquisitions',
470 options => 'simple|normal'
473 uniquefieldrequired => 'variable',
474 forceupdate => { 'explanation' => 1,
477 variable => 'dateformat',
480 'date format (us mm/dd/yyyy, metric dd/mm/yyy, ISO yyyy/mm/dd)',
482 options => 'metric|us|iso'
485 uniquefieldrequired => 'variable',
486 variable => 'template',
487 forceupdate => { 'explanation' => 1,
490 explanation => 'Preference order for intranet interface templates',
494 uniquefieldrequired => 'variable',
495 variable => 'autoBarcode',
496 forceupdate => { 'explanation' => 1,
499 explanation => 'Barcode is auto-calculated',
503 uniquefieldrequired => 'variable',
504 variable => 'insecure',
505 forceupdate => { 'explanation' => 1,
509 'If YES, no auth at all is needed. Be careful if you set this to yes!',
513 uniquefieldrequired => 'variable',
514 variable => 'authoritysep',
515 forceupdate => { 'explanation' => 1,
520 'the separator used in authority/thesaurus. Usually --',
525 uniquefieldrequired => 'variable',
526 variable => 'opaclanguages',
527 forceupdate => { 'explanation' => 1,
530 explanation => 'Set the preferred order for translations. The top language will be tried first.',
534 uniquefieldrequired => 'variable',
535 variable => 'opacthemes',
536 forceupdate => { 'explanation' => 1,
539 explanation => 'Set the preferred order for themes. The top theme will be tried first.',
543 uniquefieldrequired => 'variable',
544 variable => 'timeout',
545 forceupdate => { 'explanation' => 1,
548 explanation => 'Inactivity timeout for cookies authentication (in seconds)',
552 uniquefieldrequired => 'variable',
554 forceupdate => { 'explanation' => 1,
557 explanation => 'Turn on MARC support',
561 uniquefieldrequired => 'variable',
562 variable => 'marcflavour',
563 forceupdate => { 'explanation' => 1,
568 'your MARC flavor (MARC21 or UNIMARC) used for character encoding',
570 options => 'MARC21|UNIMARC'
573 uniquefieldrequired => 'variable',
574 variable => 'checkdigit',
576 forceupdate => { 'explanation' => 1,
579 explanation => 'Validity checks on membership number: none or "Katipo" style checks',
581 options => 'none|katipo'
584 uniquefieldrequired => 'variable',
585 variable => 'maxoutstanding',
586 forceupdate => { 'explanation' => 1,
590 'maximum amount withstanding to be able make reserves ',
594 uniquefieldrequired => 'variable',
595 variable => 'maxreserves',
596 forceupdate => { 'explanation' => 1,
600 'maximum number of reserves a member can make',
605 uniquefieldrequired => 'variable',
606 variable => 'noissuescharge',
607 forceupdate => { 'explanation' => 1,
611 'maximum amount withstanding to be able to check out an item',
616 uniquefieldrequired => 'variable',
617 variable => 'KohaAdminEmailAddress',
618 forceupdate => { 'explanation' => 1,
620 value => 'your.mail@here',
621 explanation => 'the email address where borrowers modifs are sent',
625 uniquefieldrequired => 'variable',
627 forceupdate => { 'explanation' => 1,
630 explanation => 'the gist rate. NOT in %, but in numeric form (0.12 for 12%)',
634 uniquefieldrequired => 'variable',
635 variable => 'ldapserver',
636 forceupdate => { 'explanation' => 1,
639 explanation => 'your ldap server',
643 uniquefieldrequired => 'variable',
644 variable => 'ldapinfos',
645 forceupdate => { 'explanation' => 1,
648 explanation => 'ldap info. The ldap will be used in dn : uid=xxx, <ldapinfos>',
652 uniquefieldrequired => 'variable',
653 variable => 'printcirculationslips',
654 forceupdate => { 'explanation' => 1,
657 explanation => 'if set to 1, print circulation slips. If set to 0, don\'t',
661 uniquefieldrequired => 'variable',
662 variable => 'suggestion',
663 forceupdate => { 'explanation' => 1,
666 explanation => 'if set to 1, suggestions are activated in OPAC',
673 my %fielddefinitions = (
676 field => 'printername',
685 field => 'bookfundid',
694 field => 'aqbudgetid',
695 type => 'tinyint(4)',
699 extra => 'auto_increment'
709 extra => 'auto_increment'
714 field => 'z3950random',
715 type => 'varchar(40)',
723 type => 'varchar(40)',
737 # Get version of MySQL database engine.
738 my $mysqlversion = `mysqld --version`;
739 $mysqlversion =~ /Ver (\S*) /;
741 if ( $mysqlversion ge '3.23' ) {
742 print "Could convert to MyISAM database tables...\n";
745 #---------------------------------
748 # Collect all tables into a list
749 $sth = $dbh->prepare("show tables");
751 while ( my ($table) = $sth->fetchrow ) {
752 $existingtables{$table} = 1;
756 # Now add any missing tables
757 foreach $table ( keys %requiretables ) {
758 unless ( $existingtables{$table} ) {
759 print "Adding $table table...\n";
760 my $sth = $dbh->prepare("create table $table $requiretables{$table}");
763 print "Error : $sth->errstr \n";
769 # now drop useless tables
770 foreach $table ( keys %dropable_table ) {
771 if ( $existingtables{$table} ) {
772 print "Dropping unused table $table\n" if $debug;
773 $dbh->do("drop table $table");
775 print "Error : $dbh->errstr \n";
779 unless ( $existingtables{'z3950servers'} ) {
780 #MJR: added syntax entries to close bug 624
781 print "Adding z3950servers table...\n";
782 my $sti = $dbh->prepare( "create table z3950servers (
795 $sti = $dbh->prepare( "insert into z3950servers
796 values ('z3950.loc.gov',
800 'Library of Congress',
805 unless ( $existingtables{'issuingrules'} ) {
806 $dbh->do("alter table categoryitem rename issuingrules");
807 print "renaming categoryitem\n";
811 #---------------------------------
814 foreach $table ( keys %requirefields ) {
815 print "Check table $table\n" if $debug;
816 $sth = $dbh->prepare("show columns from $table");
819 while ( ( $column, $type, $null, $key, $default, $extra ) = $sth->fetchrow )
821 $types{$column} = $type;
823 foreach $column ( keys %{ $requirefields{$table} } ) {
824 print " Check column $column [$types{$column}]\n" if $debug;
825 if ( !$types{$column} ) {
827 # column doesn't exist
828 print "Adding $column field to $table table...\n";
829 $query = "alter table $table
830 add column $column " . $requirefields{$table}->{$column};
831 print "Execute: $query\n" if $debug;
832 my $sti = $dbh->prepare($query);
835 print "**Error : $sti->errstr \n";
842 foreach $table ( keys %fielddefinitions ) {
843 print "Check table $table\n" if $debug;
844 $sth = $dbh->prepare("show columns from $table");
847 while ( ( $column, $type, $null, $key, $default, $extra ) = $sth->fetchrow )
849 $definitions->{$column}->{type} = $type;
850 $definitions->{$column}->{null} = $null;
851 $definitions->{$column}->{key} = $key;
852 $definitions->{$column}->{default} = $default;
853 $definitions->{$column}->{extra} = $extra;
855 my $fieldrow = $fielddefinitions{$table};
856 foreach my $row (@$fieldrow) {
857 my $field = $row->{field};
858 my $type = $row->{type};
859 my $null = $row->{null};
860 my $key = $row->{key};
861 my $default = $row->{default};
862 $default="''" unless $default;
863 my $extra = $row->{extra};
864 my $def = $definitions->{$field};
865 unless ( $type eq $def->{type}
866 && $null eq $def->{null}
867 && $key eq $def->{key}
868 && $default eq $def->{default}
869 && $extra eq $def->{extra} )
875 if ( $key eq 'PRI' ) {
876 $key = 'PRIMARY KEY';
878 unless ( $extra eq 'auto_increment' ) {
881 # if it's a new column use "add", if it's an old one, use "change".
883 if ($definitions->{$field}->{type}) {
884 $action="change $field"
888 # if it's a primary key, drop the previous pk, before altering the table
890 if ($key ne 'PRIMARY KEY') {
891 $sth =$dbh->prepare("alter table $table $action $field $type $null $key $extra default ?");
893 $sth =$dbh->prepare("alter table $table drop primary key, $action $field $type $null $key $extra default ?");
895 $sth->execute($default);
896 print " Alter $field in $table\n";
901 # Get list of columns from borrowers table
904 $sth = $dbh->prepare("show columns from borrowers");
906 while ( my ( $column, $type, $null, $key, $default, $extra ) = $sth->fetchrow )
908 $itemtypes{$column} = $type;
909 $nullenabled{$column} = $null;
912 unless ( $itemtypes{'cardnumber'} eq 'varchar(20)' ) {
913 $itemtypes{'cardnumber'} =~ /varchar\((\d+)\)/;
915 if ( $oldlength < 16 ) {
916 print "Setting maximum cardnumber length to 16 (was $oldlength) and marking unique.\n";
919 "alter table borrowers change cardnumber cardnumber varchar(16)");
924 "alter table borrowers drop index cardnumber");
929 "alter table borrowers add unique(cardnumber)");
935 # Get list of columns from items table
936 $sth = $dbh->prepare("show columns from items");
938 while ( my ( $column, $type, $null, $key, $default, $extra ) = $sth->fetchrow )
940 $itemtypes{$column} = $type;
941 $nullenabled{$column} = $null;
944 unless ( $itemtypes{'barcode'} eq 'varchar(20)' ) {
945 $itemtypes{'barcode'} =~ /varchar\((\d+)\)/;
947 if ( $oldlength < 20 ) {
948 print "Setting maximum barcode length to 20 (was $oldlength).\n";
951 "alter table items change barcode barcode varchar(20)");
956 # dropping unique barcode index & setting barcode to null allowed.
958 $sth = $dbh->prepare("show index from items");
960 while ( my ( $table, $non_unique, $key_name, $Seq_in_index, $Column_name, $Collation, $cardinality, $sub_part, $Packed, $comment ) = $sth->fetchrow )
962 if ($key_name eq 'barcode' && $non_unique eq 0) {
963 print "dropping BARCODE index to enable empty barcodes\n";
964 $dbh->do("ALTER TABLE `items` DROP INDEX `barcode`");
967 $dbh->do("ALTER TABLE `items` CHANGE `barcode` `barcode` VARCHAR( 20 )") unless ($nullenabled{barcode} eq 'YES');
970 # creating fulltext index in bibliothesaurus if needed
972 $sth = $dbh->prepare("show index from bibliothesaurus");
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 'category_2') {
981 print "Creating fulltext index on bibliothesaurus\n" unless $exists;
982 $dbh->do('create fulltext index category_2 on bibliothesaurus (category,freelib)') unless $exists;
985 # creating index in z3950results if needed
987 $sth = $dbh->prepare("show index from z3950results");
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 'query_server') {
996 print "Creating index on z3950results\n" unless $exists;
997 $dbh->do('create unique index query_server on z3950results (queryid,server)') unless $exists;
999 # changing z3950daemon field to NULL in marc_breeding
1000 $dbh->do("ALTER TABLE `marc_breeding` CHANGE `z3950random` `z3950random` VARCHAR( 40 )");
1002 # making borrowernumber an auto_increment field
1003 $dbh->do("ALTER TABLE `borrowers` CHANGE `borrowernumber` `borrowernumber` INTEGER auto_increment");
1005 # extending the timestamp in branchtransfers...
1006 my %branchtransfers;
1008 $sth = $dbh->prepare("show columns from branchtransfers");
1010 while ( my ( $column, $type, $null, $key, $default, $extra ) = $sth->fetchrow )
1012 $branchtransfers{$column} = $type;
1015 unless ( $branchtransfers{'datesent'} eq 'datetime' ) {
1016 print "Setting type of datesent in branchtransfers to datetime.\n";
1019 "alter table branchtransfers change datesent datesent datetime");
1023 unless ( $branchtransfers{'datearrived'} eq 'datetime' ) {
1024 print "Setting type of datearrived in branchtransfers to datetime.\n";
1027 "alter table branchtransfers change datearrived datearrived datetime");
1031 # changing the branchcategories table around...
1032 my %branchcategories;
1034 $sth = $dbh->prepare("show columns from branchcategories");
1036 while ( my ( $column, $type, $null, $key, $default, $extra ) = $sth->fetchrow )
1038 $branchcategories{$column} = $type;
1041 unless ( $branchcategories{'categorycode'} eq 'varchar(4)' ) {
1043 "Setting type of categorycode in branchcategories to varchar(4),\n and making the primary key.\n";
1046 "alter table branchcategories change categorycode categorycode varchar(4) not null"
1051 "alter table branchcategories add primary key (categorycode)");
1055 unless ( $branchcategories{'categoryname'} eq 'text' ) {
1056 print "Changing branchcode in branchcategories to categoryname text.\n";
1059 "alter table branchcategories change branchcode categoryname text");
1063 unless ( $branchcategories{'codedescription'} eq 'text' ) {
1065 "Replacing branchholding in branchcategories with codedescription text.\n";
1068 "alter table branchcategories change branchholding codedescription text"
1073 # changing the items table around...
1076 $sth = $dbh->prepare("show columns from items");
1078 while ( my ( $column, $type, $null, $key, $default, $extra ) = $sth->fetchrow )
1080 $items{$column} = $type;
1083 if ($items{'bulk'} eq "varchar(30)") {
1084 print " Setting callnumber in items table\n";
1086 $dbh->prepare("ALTER TABLE `items` CHANGE `bulk` `itemcallnumber` VARCHAR( 30 ) DEFAULT NULL");
1088 $sti = $dbh->prepare("update marc_subfield_structure set kohafield=\"items.itemcallnumber\" where kohafield=\"items.bulk\"");
1093 # creating index in issuingrules if needed
1095 $sth = $dbh->prepare("show index from issuingrules");
1098 while ( my ( $table, $non_unique, $key_name, $Seq_in_index, $Column_name, $Collation, $cardinality, $sub_part, $Packed, $comment ) = $sth->fetchrow )
1100 if ($key_name eq 'PRIMARY') {
1104 print "Creating index on z3950results\n" unless $exists;
1105 $dbh->do('ALTER TABLE issuingrules ADD PRIMARY KEY ( branchcode, categorycode, itemtype )') unless $exists;
1107 $dbh->do('ALTER TABLE marc_tag_structure drop primary key');
1108 $dbh->do('ALTER TABLE marc_tag_structure ADD PRIMARY KEY ( itemtype, tagfield )');
1110 $dbh->do('ALTER TABLE marc_subfield_structure drop primary key');
1111 $dbh->do('ALTER TABLE marc_subfield_structure ADD PRIMARY KEY ( itemtype, tagfield, tagsubfield )');
1113 # Populate tables with required data
1115 foreach my $table ( keys %tabledata ) {
1116 print "Checking for data required in table $table...\n";
1117 my $tablerows = $tabledata{$table};
1118 foreach my $row (@$tablerows) {
1119 my $uniquefieldrequired = $row->{uniquefieldrequired};
1120 my $uniquevalue = $row->{$uniquefieldrequired};
1121 my $forceupdate = $row->{forceupdate};
1124 "select $uniquefieldrequired from $table where $uniquefieldrequired=?"
1126 $sth->execute($uniquevalue);
1128 foreach my $field (keys %$forceupdate) {
1129 if ($forceupdate->{$field}) {
1130 my $sth=$dbh->prepare("update systempreferences set $field=? where $uniquefieldrequired=?");
1131 $sth->execute($row->{$field}, $uniquevalue);
1135 print "Adding row to $table: ";
1139 foreach my $field ( keys %$row ) {
1140 next if $field eq 'uniquefieldrequired';
1141 next if $field eq 'forceupdate';
1142 my $value = $row->{$field};
1143 push @values, $value;
1144 print " $field => $value";
1145 $fieldlist .= "$field,";
1146 $placeholders .= "?,";
1149 $fieldlist =~ s/,$//;
1150 $placeholders =~ s/,$//;
1153 "insert into $table ($fieldlist) values ($placeholders)");
1154 $sth->execute(@values);
1164 # Revision 1.80 2004/05/28 08:32:00 tipaul
1166 # * MARC authority file
1167 # * seealso & hidden in MARC biblio structure.
1169 # Revision 1.79 2004/05/18 09:50:07 tipaul
1170 # *** empty log message ***
1172 # Revision 1.78 2004/05/10 09:29:33 tipaul
1173 # css is now the default theme for OPAC.
1174 # It will be the theme used for improvements and new things in OPAC.
1176 # Revision 1.77 2004/05/06 14:56:51 tipaul
1177 # adding table issuingrules (previously called categoryitem
1179 # Revision 1.76 2004/05/03 09:32:25 tipaul
1180 # adding printcirculationsplit parameter (already existed, but was not in systempref by defaul)
1182 # Revision 1.75 2004/04/14 19:49:00 tipaul
1183 # seealso field set to 255 chars
1185 # Revision 1.74 2004/03/11 16:10:16 tipaul
1186 # *** empty log message ***
1188 # Revision 1.73 2004/03/06 20:26:13 tipaul
1189 # adding seealso feature in MARC searches