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 suggestionid 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 volumedesc char(255) default NULL,
300 publicationyear smallint(6) default NULL,
301 place char(255) default NULL,
302 isbn char(10) default NULL,
303 mailoverseeing smallint(1) default 0,
304 biblionumber int(11) default NULL,
305 PRIMARY KEY (suggestionnumber),
306 KEY suggestedby (suggestedby),
307 KEY managedby (managedby)
309 aqbasket => "(basketno int(11) NOT NULL auto_increment,
312 booksellerid varchar(10),
313 authorisedby varchar(10),
314 booksellerinvoicenumber text,
315 PRIMARY KEY (basketno)
317 serial => "(serialid int(11) NOT NULL auto_increment,
318 biblionumber varchar(100) NOT NULL default '',
319 subscriptionid varchar(100) NOT NULL default '',
320 serialseq varchar(100) NOT NULL default '',
321 status tinyint(4) NOT NULL default '0',
322 planneddate date NOT NULL default '0000-00-00',
323 PRIMARY KEY (serialid)
325 subscription => "(biblionumber int(11) NOT NULL default '0',
326 subscriptionid int(11) NOT NULL auto_increment,
327 librarian varchar(100) default '',
328 startdate date default '0000-00-00',
329 aqbooksellerid int(11) default '0',
330 cost int(11) default '0',
331 aqbudgetid int(11) default '0',
332 weeklength tinyint(4) default '0',
333 monthlength tinyint(4) default '0',
334 numberlength tinyint(4) default '0',
335 periodicity tinyint(4) default '0',
336 dow varchar(100) default '',
337 numberingmethod varchar(100) default '',
339 status varchar(100) NOT NULL default '',
340 add1 int(11) default 0,
341 every1 int(11) default 0,
342 whenmorethan1 int(11) default 0,
345 add2 int(11) default 0,
346 every2 int(11) default 0,
347 whenmorethan2 int(11) default 0,
350 add3 int(11) default 0,
351 every3 int(11) default 0,
352 innerloop1 int(11) default 0,
353 innerloop2 int(11) default 0,
354 innerloop3 int(11) default 0,
355 whenmorethan3 int(11) default 0,
358 PRIMARY KEY (subscriptionid)
360 subscriptionhistory => "(biblionumber int(11) NOT NULL default '0',
361 subscriptionid int(11) NOT NULL default '0',
362 histstartdate date NOT NULL default '0000-00-00',
363 enddate date default '0000-00-00',
364 missinglist longtext NOT NULL,
365 recievedlist longtext NOT NULL,
366 opacnote varchar(150) NOT NULL default '',
367 librariannote varchar(150) NOT NULL default '',
368 PRIMARY KEY (subscriptionid),
369 KEY biblionumber (biblionumber)
373 my %requirefields = (
374 biblio => { 'abstract' => 'text' },
375 deletedbiblio => { 'abstract' => 'text', 'marc' => 'blob' },
376 deleteditems => { 'marc' => 'blob', 'paidfor' => 'text' },
378 'lccn' => 'char(25)',
379 'url' => 'varchar(255)',
382 deletedbiblioitems => {
383 'lccn' => 'char(25)',
384 'url' => 'varchar(255)',
387 branchtransfers => { 'datearrived' => 'datetime' },
388 statistics => { 'borrowernumber' => 'int(11)' },
390 'invoicedisc' => 'float(6,4)',
391 'nocalc' => 'int(11)'
394 'userid' => 'char(30)',
395 'password' => 'char(30)',
396 'flags' => 'int(11)',
397 'textmessaging' => 'varchar(30)',
398 'zipcode' => 'varchar(25)',
399 'homezipcode' => 'varchar(25)',
400 'sort1' => 'char(80)',
401 'sort2' => 'char(80)',
403 aqorders => { 'budgetdate' => 'date',
404 'sort1' => 'char(80)',
405 'sort2' => 'char(80)', },
406 aqbudget => {'aqbudgetid' => 'tinyint(4) auto_increment primary key'},
407 items => {'paidfor' => 'text'},
409 #added so that reference items are not available for reserves...
410 itemtypes => { 'notforloan' => 'smallint(6)' },
411 systempreferences => { 'explanation' => 'char(80)',
412 'type' => 'char(20)',
413 'options' => 'text' },
414 z3950servers => { 'syntax' => 'char(80)' },
415 marc_tag_structure =>{
416 'frameworkcode' => 'char(4) not NULL default \'\''},
417 marc_subfield_structure =>{'seealso' => 'char(255)',
418 'frameworkcode' => 'char(4) not NULL default \'\'',
419 'hidden' => 'tinyint(1)',
420 'isurl' => 'tinyint(1)',
422 bookshelf => {'owner' => 'char(80)',
423 'category' => 'char(1)',
425 marc_biblio => { 'frameworkcode' => 'char(4) not NULL default \'\'' },
428 my %dropable_table = (
429 classification => 'classification',
430 multipart => 'multipart',
431 multivolume => 'multivolume',
432 newitems => 'newitems',
433 procedures => 'procedures',
434 publisher => 'publisher',
435 searchstats => 'searchstats',
436 serialissues => 'serialissues',
439 my %uselessfields = (
440 aqorders => "requisitionedby,authorisedby,booksellerid,
441 deliverydays,followupdays,
442 numberfollowupsallowed,numberfollowupssent,
443 dateprinted,sourced,quantityreceiveddamaged,
444 subscriptionfrom,subscriptionto
447 # the other hash contains other actions that can't be done elsewhere. they are done
448 # either BEFORE of AFTER everything else, depending on "when" entry (default => AFTER)
450 # The tabledata hash contains data that should be in the tables.
451 # The uniquefieldrequired hash entry is used to determine which (if any) fields
452 # must not exist in the table for this row to be inserted. If the
453 # uniquefieldrequired entry is already in the table, the existing data is not
454 # modified, unless the forceupdate hash entry is also set. Fields in the
455 # anonymous "forceupdate" hash will be forced to be updated to the default
456 # values given in the %tabledata hash.
461 uniquefieldrequired => 'bit',
463 flag => 'superlibrarian',
464 flagdesc => 'Access to all librarian functions',
468 uniquefieldrequired => 'bit',
471 flagdesc => 'Circulate books',
475 uniquefieldrequired => 'bit',
478 flagdesc => 'View Catalogue (Librarian Interface)',
482 uniquefieldrequired => 'bit',
484 flag => 'parameters',
485 flagdesc => 'Set Koha system paramters',
489 uniquefieldrequired => 'bit',
492 flagdesc => 'Add or modify borrowers',
496 uniquefieldrequired => 'bit',
498 flag => 'permissions',
499 flagdesc => 'Set user permissions',
503 uniquefieldrequired => 'bit',
505 flag => 'reserveforothers',
506 flagdesc => 'Reserve books for patrons',
510 uniquefieldrequired => 'bit',
513 flagdesc => 'Borrow books',
517 uniquefieldrequired => 'bit',
519 flag => 'reserveforself',
520 flagdesc => 'Reserve books for self',
524 uniquefieldrequired => 'bit',
526 flag => 'editcatalogue',
527 flagdesc => 'Edit Catalogue (Modify bibliographic/holdings data)',
531 uniquefieldrequired => 'bit',
533 flag => 'updatecharges',
534 flagdesc => 'Update borrower charges',
538 systempreferences => [
540 uniquefieldrequired => 'variable',
541 forceupdate => { 'explanation' => 1,
543 variable => 'LibraryName',
544 value => '<i><b>Koha<br/>Free Software ILS<br/><br/></b>Koha : a gift, a contribution<br/> in Maori</i>',
545 explanation => 'Library name as shown on main opac page',
550 uniquefieldrequired => 'variable',
551 forceupdate => { 'explanation' => 1,
553 variable => 'autoMemberNum',
555 explanation => 'Member number is auto-calculated',
560 uniquefieldrequired => 'variable',
561 forceupdate => { 'explanation' => 1,
564 variable => 'acquisitions',
567 'Normal, budget-based acquisitions, or Simple bibliographic-data acquisitions',
569 options => 'simple|normal'
572 uniquefieldrequired => 'variable',
573 forceupdate => { 'explanation' => 1,
576 variable => 'dateformat',
579 'date format (us mm/dd/yyyy, metric dd/mm/yyy, ISO yyyy/mm/dd)',
581 options => 'metric|us|iso'
584 uniquefieldrequired => 'variable',
585 variable => 'template',
586 forceupdate => { 'explanation' => 1,
589 explanation => 'Preference order for intranet interface templates',
593 uniquefieldrequired => 'variable',
594 variable => 'autoBarcode',
595 forceupdate => { 'explanation' => 1,
598 explanation => 'Barcode is auto-calculated',
602 uniquefieldrequired => 'variable',
603 variable => 'insecure',
604 forceupdate => { 'explanation' => 1,
608 'If YES, no auth at all is needed. Be careful if you set this to yes!',
612 uniquefieldrequired => 'variable',
613 variable => 'authoritysep',
614 forceupdate => { 'explanation' => 1,
619 'the separator used in authority/thesaurus. Usually --',
624 uniquefieldrequired => 'variable',
625 variable => 'opaclanguages',
626 forceupdate => { 'explanation' => 1,
629 explanation => 'Set the preferred order for translations. The top language will be tried first.',
633 uniquefieldrequired => 'variable',
634 variable => 'opacthemes',
635 forceupdate => { 'explanation' => 1,
638 explanation => 'Set the preferred order for themes. The top theme will be tried first.',
642 uniquefieldrequired => 'variable',
643 variable => 'timeout',
644 forceupdate => { 'explanation' => 1,
647 explanation => 'Inactivity timeout for cookies authentication (in seconds)',
651 uniquefieldrequired => 'variable',
653 forceupdate => { 'explanation' => 1,
656 explanation => 'Turn on MARC support',
660 uniquefieldrequired => 'variable',
661 variable => 'marcflavour',
662 forceupdate => { 'explanation' => 1,
667 'your MARC flavor (MARC21 or UNIMARC) used for character encoding',
669 options => 'MARC21|UNIMARC'
672 uniquefieldrequired => 'variable',
673 variable => 'checkdigit',
675 forceupdate => { 'explanation' => 1,
678 explanation => 'Validity checks on membership number: none or "Katipo" style checks',
680 options => 'none|katipo'
683 uniquefieldrequired => 'variable',
684 variable => 'maxoutstanding',
685 forceupdate => { 'explanation' => 1,
689 'maximum amount withstanding to be able make reserves ',
693 uniquefieldrequired => 'variable',
694 variable => 'maxreserves',
695 forceupdate => { 'explanation' => 1,
699 'maximum number of reserves a member can make',
704 uniquefieldrequired => 'variable',
705 variable => 'noissuescharge',
706 forceupdate => { 'explanation' => 1,
710 'maximum amount withstanding to be able to check out an item',
715 uniquefieldrequired => 'variable',
716 variable => 'KohaAdminEmailAddress',
717 forceupdate => { 'explanation' => 1,
719 value => 'your.mail@here',
720 explanation => 'the email address where borrowers modifs are sent',
724 uniquefieldrequired => 'variable',
726 forceupdate => { 'explanation' => 1,
729 explanation => 'the gist rate. NOT in %, but in numeric form (0.12 for 12%)',
733 uniquefieldrequired => 'variable',
734 variable => 'ldapserver',
735 forceupdate => { 'explanation' => 1,
738 explanation => 'your ldap server',
742 uniquefieldrequired => 'variable',
743 variable => 'ldapinfos',
744 forceupdate => { 'explanation' => 1,
747 explanation => 'ldap info. The ldap will be used in dn : uid=xxx, <ldapinfos>',
751 uniquefieldrequired => 'variable',
752 variable => 'printcirculationslips',
753 forceupdate => { 'explanation' => 1,
756 explanation => 'if set to 1, print circulation slips. If set to 0, don\'t',
760 uniquefieldrequired => 'variable',
761 variable => 'suggestion',
762 forceupdate => { 'explanation' => 1,
765 explanation => 'if set to 1, suggestions are activated in OPAC',
769 uniquefieldrequired => 'variable',
771 forceupdate => { 'explanation' => 1,
773 value => 'Fill with appropriate value...',
774 explanation => 'ISBD',
778 uniquefieldrequired => 'variable',
779 variable => 'virtualshelves',
780 forceupdate => { 'explanation' => 1,
783 explanation => 'Set virtual shelves management ON or OFF',
790 my %fielddefinitions = (
793 field => 'printername',
802 field => 'bookfundid',
811 field => 'aqbudgetid',
812 type => 'tinyint(4)',
816 extra => 'auto_increment'
826 extra => 'auto_increment'
831 field => 'z3950random',
832 type => 'varchar(40)',
840 type => 'varchar(40)',
854 # Get version of MySQL database engine.
855 my $mysqlversion = `mysqld --version`;
856 $mysqlversion =~ /Ver (\S*) /;
858 if ( $mysqlversion ge '3.23' ) {
859 print "Could convert to MyISAM database tables...\n" unless $silent;
862 #---------------------------------
865 # Collect all tables into a list
866 $sth = $dbh->prepare("show tables");
868 while ( my ($table) = $sth->fetchrow ) {
869 $existingtables{$table} = 1;
873 # Now add any missing tables
874 foreach $table ( keys %requiretables ) {
875 unless ( $existingtables{$table} ) {
876 print "Adding $table table...\n" unless $silent;
877 my $sth = $dbh->prepare("create table $table $requiretables{$table}");
880 print "Error : $sth->errstr \n";
886 # now drop useless tables
887 foreach $table ( keys %dropable_table ) {
888 if ( $existingtables{$table} ) {
889 print "Dropping unused table $table\n" if $debug and not $silent;
890 $dbh->do("drop table $table");
892 print "Error : $dbh->errstr \n";
896 unless ( $existingtables{'z3950servers'} ) {
897 #MJR: added syntax entries to close bug 624
898 print "Adding z3950servers table...\n" unless $silent;
899 my $sti = $dbh->prepare( "create table z3950servers (
912 $sti = $dbh->prepare( "insert into z3950servers
913 values ('z3950.loc.gov',
917 'Library of Congress',
922 unless ( $existingtables{'issuingrules'} ) {
923 $dbh->do("alter table categoryitem rename issuingrules");
924 $dbh->do("ALTER TABLE issuingrules ADD maxissueqty int(4) default NULL");
925 $dbh->do("ALTER TABLE issuingrules ADD issuelength int(4) default NULL");
926 $dbh->do("ALTER TABLE issuingrules ADD branchcode varchar(4) NOT NULL default ''");
927 print "renaming categoryitem\n" unless $silent;
931 #---------------------------------
934 foreach $table ( keys %requirefields ) {
935 print "Check table $table\n" if $debug and not $silent;
936 $sth = $dbh->prepare("show columns from $table");
939 while ( ( $column, $type, $null, $key, $default, $extra ) = $sth->fetchrow )
941 $types{$column} = $type;
943 foreach $column ( keys %{ $requirefields{$table} } ) {
944 print " Check column $column [$types{$column}]\n" if $debug and not $silent;
945 if ( !$types{$column} ) {
947 # column doesn't exist
948 print "Adding $column field to $table table...\n" unless $silent;
949 $query = "alter table $table
950 add column $column " . $requirefields{$table}->{$column};
951 print "Execute: $query\n" if $debug;
952 my $sti = $dbh->prepare($query);
955 print "**Error : $sti->errstr \n";
962 foreach $table ( keys %fielddefinitions ) {
963 print "Check table $table\n" if $debug;
964 $sth = $dbh->prepare("show columns from $table");
967 while ( ( $column, $type, $null, $key, $default, $extra ) = $sth->fetchrow )
969 $definitions->{$column}->{type} = $type;
970 $definitions->{$column}->{null} = $null;
971 $definitions->{$column}->{key} = $key;
972 $definitions->{$column}->{default} = $default;
973 $definitions->{$column}->{extra} = $extra;
975 my $fieldrow = $fielddefinitions{$table};
976 foreach my $row (@$fieldrow) {
977 my $field = $row->{field};
978 my $type = $row->{type};
979 my $null = $row->{null};
980 my $key = $row->{key};
981 my $default = $row->{default};
982 $default="''" unless $default;
983 my $extra = $row->{extra};
984 my $def = $definitions->{$field};
985 unless ( $type eq $def->{type}
986 && $null eq $def->{null}
987 && $key eq $def->{key}
988 && $default eq $def->{default}
989 && $extra eq $def->{extra} )
995 if ( $key eq 'PRI' ) {
996 $key = 'PRIMARY KEY';
998 unless ( $extra eq 'auto_increment' ) {
1001 # if it's a new column use "add", if it's an old one, use "change".
1003 if ($definitions->{$field}->{type}) {
1004 $action="change $field"
1008 # if it's a primary key, drop the previous pk, before altering the table
1010 if ($key ne 'PRIMARY KEY') {
1011 $sth =$dbh->prepare("alter table $table $action $field $type $null $key $extra default ?");
1013 $sth =$dbh->prepare("alter table $table drop primary key, $action $field $type $null $key $extra default ?");
1015 $sth->execute($default);
1016 print " Alter $field in $table\n" unless $silent;
1021 # Get list of columns from borrowers table
1024 $sth = $dbh->prepare("show columns from borrowers");
1026 while ( my ( $column, $type, $null, $key, $default, $extra ) = $sth->fetchrow )
1028 $itemtypes{$column} = $type;
1029 $nullenabled{$column} = $null;
1032 unless ( $itemtypes{'cardnumber'} eq 'varchar(20)' ) {
1033 $itemtypes{'cardnumber'} =~ /varchar\((\d+)\)/;
1035 if ( $oldlength < 16 ) {
1036 print "Setting maximum cardnumber length to 16 (was $oldlength) and marking unique.\n" unless $silent;
1039 "alter table borrowers change cardnumber cardnumber varchar(16)");
1044 "alter table borrowers drop index cardnumber");
1049 "alter table borrowers add unique(cardnumber)");
1055 # Get list of columns from items table
1056 $sth = $dbh->prepare("show columns from items");
1058 while ( my ( $column, $type, $null, $key, $default, $extra ) = $sth->fetchrow )
1060 $itemtypes{$column} = $type;
1061 $nullenabled{$column} = $null;
1064 unless ( $itemtypes{'barcode'} eq 'varchar(20)' ) {
1065 $itemtypes{'barcode'} =~ /varchar\((\d+)\)/;
1067 if ( $oldlength < 20 ) {
1068 print "Setting maximum barcode length to 20 (was $oldlength).\n" unless $silent;
1071 "alter table items change barcode barcode varchar(20)");
1076 # dropping unique barcode index & setting barcode to null allowed.
1078 $sth = $dbh->prepare("show index from items");
1080 while ( my ( $table, $non_unique, $key_name, $Seq_in_index, $Column_name, $Collation, $cardinality, $sub_part, $Packed, $comment ) = $sth->fetchrow )
1082 if ($key_name eq 'barcode' && $non_unique eq 0) {
1083 print "dropping BARCODE index to enable empty barcodes\n" unless $silent;
1084 $dbh->do("ALTER TABLE `items` DROP INDEX `barcode`");
1087 $dbh->do("ALTER TABLE `items` CHANGE `barcode` `barcode` VARCHAR( 20 )") unless ($nullenabled{barcode} eq 'YES');
1090 # creating fulltext index in bibliothesaurus if needed
1092 $sth = $dbh->prepare("show index from bibliothesaurus");
1095 while ( my ( $table, $non_unique, $key_name, $Seq_in_index, $Column_name, $Collation, $cardinality, $sub_part, $Packed, $comment ) = $sth->fetchrow )
1097 if ($key_name eq 'category_2') {
1101 print "Creating fulltext index on bibliothesaurus\n" unless $exists or $silent;
1102 $dbh->do('create fulltext index category_2 on bibliothesaurus (category,freelib)') unless $exists;
1105 # creating index in z3950results if needed
1107 $sth = $dbh->prepare("show index from z3950results");
1110 while ( my ( $table, $non_unique, $key_name, $Seq_in_index, $Column_name, $Collation, $cardinality, $sub_part, $Packed, $comment ) = $sth->fetchrow )
1112 if ($key_name eq 'query_server') {
1116 print "Creating index on z3950results\n" unless $exists or $silent;
1117 $dbh->do('create unique index query_server on z3950results (queryid,server)') unless $exists;
1119 # changing z3950daemon field to NULL in marc_breeding
1120 $dbh->do("ALTER TABLE `marc_breeding` CHANGE `z3950random` `z3950random` VARCHAR( 40 )");
1122 # making borrowernumber an auto_increment field
1123 $dbh->do("ALTER TABLE `borrowers` CHANGE `borrowernumber` `borrowernumber` INTEGER auto_increment");
1125 # changing indexes in marc_*_structure to use frameworkcode
1126 $dbh->do('alter table marc_subfield_structure drop index tab');
1127 $dbh->do('create index tab on marc_subfield_structure (frameworkcode,tab)');
1128 $dbh->do('alter table marc_subfield_structure drop index kohafield');
1129 $dbh->do('create index kohafield on marc_subfield_structure (frameworkcode,kohafield)');
1132 # extending the timestamp in branchtransfers...
1133 my %branchtransfers;
1135 $sth = $dbh->prepare("show columns from branchtransfers");
1137 while ( my ( $column, $type, $null, $key, $default, $extra ) = $sth->fetchrow )
1139 $branchtransfers{$column} = $type;
1142 unless ( $branchtransfers{'datesent'} eq 'datetime' ) {
1143 print "Setting type of datesent in branchtransfers to datetime.\n" unless $silent;
1146 "alter table branchtransfers change datesent datesent datetime");
1150 unless ( $branchtransfers{'datearrived'} eq 'datetime' ) {
1151 print "Setting type of datearrived in branchtransfers to datetime.\n" unless $silent;
1154 "alter table branchtransfers change datearrived datearrived datetime");
1158 # changing the branchcategories table around...
1159 my %branchcategories;
1161 $sth = $dbh->prepare("show columns from branchcategories");
1163 while ( my ( $column, $type, $null, $key, $default, $extra ) = $sth->fetchrow )
1165 $branchcategories{$column} = $type;
1168 unless ( $branchcategories{'categorycode'} eq 'varchar(4)' ) {
1170 "Setting type of categorycode in branchcategories to varchar(4),\n and making the primary key.\n" unless $silent;
1173 "alter table branchcategories change categorycode categorycode varchar(4) not null"
1178 "alter table branchcategories add primary key (categorycode)");
1182 unless ( $branchcategories{'categoryname'} eq 'text' ) {
1183 print "Changing branchcode in branchcategories to categoryname text.\n" unless $silent;
1186 "alter table branchcategories change branchcode categoryname text");
1190 unless ( $branchcategories{'codedescription'} eq 'text' ) {
1192 "Replacing branchholding in branchcategories with codedescription text.\n" unless $silent;
1195 "alter table branchcategories change branchholding codedescription text"
1200 # changing the items table around...
1203 $sth = $dbh->prepare("show columns from items");
1205 while ( my ( $column, $type, $null, $key, $default, $extra ) = $sth->fetchrow )
1207 $items{$column} = $type;
1210 if ($items{'bulk'} eq "varchar(30)") {
1211 print " Setting callnumber in items table\n" unless $silent;
1213 $dbh->prepare("ALTER TABLE `items` CHANGE `bulk` `itemcallnumber` VARCHAR( 30 ) DEFAULT NULL");
1215 $sti = $dbh->prepare("update marc_subfield_structure set kohafield=\"items.itemcallnumber\" where kohafield=\"items.bulk\"");
1219 # changing the marc_subfield_structure table around...
1220 my %marc_subfield_structure;
1222 $sth = $dbh->prepare("show columns from marc_subfield_structure");
1224 while ( my ( $column, $type, $null, $key, $default, $extra ) = $sth->fetchrow )
1226 $marc_subfield_structure{$column} = $type;
1229 if ($marc_subfield_structure{thesaurus_category}) {
1230 print " changing thesaurus_category in marc_subfield_structure table\n" unless $silent;
1232 $dbh->prepare("ALTER TABLE marc_subfield_structure CHANGE `thesaurus_category` `authtypecode` VARCHAR(10 ) DEFAULT NULL");
1237 # creating index in issuingrules if needed
1239 $sth = $dbh->prepare("show index from issuingrules");
1242 while ( my ( $table, $non_unique, $key_name, $Seq_in_index, $Column_name, $Collation, $cardinality, $sub_part, $Packed, $comment ) = $sth->fetchrow )
1244 if ($key_name eq 'PRIMARY') {
1248 print "Creating index on issuing rules\n" unless $exists or $silent;
1249 $dbh->do('ALTER TABLE issuingrules ADD PRIMARY KEY ( branchcode, categorycode, itemtype )') unless $exists;
1251 $dbh->do('ALTER TABLE marc_tag_structure drop primary key');
1252 $dbh->do('ALTER TABLE marc_tag_structure ADD PRIMARY KEY ( frameworkcode, tagfield )');
1254 $dbh->do('ALTER TABLE marc_subfield_structure drop primary key');
1255 $dbh->do('ALTER TABLE marc_subfield_structure ADD PRIMARY KEY ( frameworkcode, tagfield, tagsubfield )');
1257 # Get list of columns from marc_word table
1260 $sth = $dbh->prepare("show columns from marc_word");
1262 while ( my ( $column, $type, $null, $key, $default, $extra ) = $sth->fetchrow )
1264 $marc_word{$column} = $type;
1265 $nullenabled{$column} = $null;
1267 if ($marc_word{subfieldid}) {
1268 #create field tagsubfield, copy tag+subfieldid, then drop tag and subfieldid
1269 print "Modifying marc_word (concat on tag and subfield for better perfs)\n" unless $silent;
1270 $dbh->do("ALTER TABLE `marc_word` ADD `tagsubfield` CHAR( 4 ) NOT NULL AFTER `bibid`");
1271 $dbh->do("update marc_word set tagsubfield=concat(tag,subfieldid)");
1272 $dbh->do("alter table marc_word drop tag");
1273 $dbh->do("alter table marc_word drop subfieldid");
1274 $dbh->do("create index Search_Marc on marc_word (tagsubfield,word)");
1276 # Populate tables with required data
1278 # fill aqbasket if it's empty and aqorder is not
1279 # => it means it has just been created & must be filled
1280 $sth = $dbh->prepare("select count(*) from aqbasket");
1282 if ($sth->fetchrow == 0) {
1283 $sth = $dbh->prepare("select count(*) from aqorders");
1285 if ($sth->fetchrow >0) {
1286 print "Populating new table aqbasket\n";
1287 print "IMPORTANT NOTE: error message \"Duplicate entry 'X' for key 1\" may appear. it should not be a real trouble\n";
1288 $sth=$dbh->prepare("select distinct basketno,booksellerid,authorisedby,entrydate,booksellerinvoicenumber from aqorders");
1290 my ($basketno,$booksellerid,$authorisedby,$entrydate,$booksellerinvoicenumber);
1291 my $sth2 = $dbh->prepare("insert into aqbasket (basketno,creationdate,booksellerid,authorisedby,booksellerinvoicenumber) values (?,?,?,?,?)");
1292 while (($basketno,$booksellerid,$authorisedby,$entrydate,$booksellerinvoicenumber) = $sth->fetchrow) {
1293 print "$basketno,$entrydate,$booksellerid,$authorisedby,$booksellerinvoicenumber\n";
1294 $sth2->execute($basketno,$entrydate,$booksellerid,$authorisedby,$booksellerinvoicenumber);
1298 foreach my $table ( keys %tabledata ) {
1299 print "Checking for data required in table $table...\n" unless $silent;
1300 my $tablerows = $tabledata{$table};
1301 foreach my $row (@$tablerows) {
1302 my $uniquefieldrequired = $row->{uniquefieldrequired};
1303 my $uniquevalue = $row->{$uniquefieldrequired};
1304 my $forceupdate = $row->{forceupdate};
1307 "select $uniquefieldrequired from $table where $uniquefieldrequired=?"
1309 $sth->execute($uniquevalue);
1311 foreach my $field (keys %$forceupdate) {
1312 if ($forceupdate->{$field}) {
1313 my $sth=$dbh->prepare("update systempreferences set $field=? where $uniquefieldrequired=?");
1314 $sth->execute($row->{$field}, $uniquevalue);
1318 print "Adding row to $table: " unless $silent;
1322 foreach my $field ( keys %$row ) {
1323 next if $field eq 'uniquefieldrequired';
1324 next if $field eq 'forceupdate';
1325 my $value = $row->{$field};
1326 push @values, $value;
1327 print " $field => $value" unless $silent;
1328 $fieldlist .= "$field,";
1329 $placeholders .= "?,";
1331 print "\n" unless $silent;
1332 $fieldlist =~ s/,$//;
1333 $placeholders =~ s/,$//;
1336 "insert into $table ($fieldlist) values ($placeholders)");
1337 $sth->execute(@values);
1342 # at last, remove useless fields
1343 foreach $table ( keys %uselessfields ) {
1344 my @fields = split /,/,$uselessfields{$table};
1347 foreach my $fieldtodrop (@fields) {
1348 $fieldtodrop =~ s/\t//g;
1349 $fieldtodrop =~ s/\n//g;
1351 $sth = $dbh->prepare("show columns from $table");
1353 while ( my ( $column, $type, $null, $key, $default, $extra ) = $sth->fetchrow )
1355 $exists =1 if ($column eq $fieldtodrop);
1358 print "deleting $fieldtodrop field in $table...\n" unless $silent;
1359 my $sth = $dbh->prepare("alter table $table drop $fieldtodrop");
1371 # Revision 1.93 2004/08/12 14:50:50 tipaul
1374 # Revision 1.92 2004/08/06 16:38:42 tipaul
1375 # changing DB structure to calculate next issue number.
1376 # Seems to work fine.
1378 # Still misses the date calculation & the test of end of subscription (maybe for monday ?)
1380 # Revision 1.91 2004/07/15 09:52:28 tipaul
1381 # Acquisition & Suggestion :
1382 # * acquisition rewritte : create a aqbasket table to deal with "bookseller order header".
1383 # * add "close basket" feature : a closed basket can't be modified
1384 # * suggestion feature : manage suggestions in acquisition (after suggestion filled in OPAC)
1386 # Revision 1.90 2004/07/06 08:24:18 tipaul
1387 # adding 2 free fields that can be used for sorting purposes
1389 # Revision 1.89 2004/07/02 15:55:08 tipaul
1390 # Adding 2 new fields, called "sort1" and "sort2"
1391 # They can be used for sorting & statistics reasons by the library.
1393 # Revision 1.88 2004/06/26 23:34:26 rangi
1396 # Revision 1.87 2004/06/23 13:03:09 tipaul
1397 # fixes in DB structure
1399 # Revision 1.86 2004/06/22 11:30:57 tipaul
1400 # adding -s (silent) flag, to have a silent install.
1401 # only updater will be verbose
1403 # Revision 1.85 2004/06/17 15:19:44 tipaul
1404 # missing Marc_Search index on marc_word
1406 # Revision 1.84 2004/06/17 08:25:21 tipaul
1407 # DB modifs : merging tag & subfield in marc_word table
1409 # Revision 1.83 2004/06/10 08:32:02 tipaul
1410 # MARC authority management (continued)
1412 # Revision 1.82 2004/06/03 12:46:58 tipaul
1413 # * frameworks and itemtypes are independant
1415 # 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.
1417 # Revision 1.81 2004/05/28 09:56:21 tipaul
1420 # Revision 1.80 2004/05/28 08:32:00 tipaul
1422 # * MARC authority file
1423 # * seealso & hidden in MARC biblio structure.
1425 # Revision 1.79 2004/05/18 09:50:07 tipaul
1426 # *** empty log message ***
1428 # Revision 1.78 2004/05/10 09:29:33 tipaul
1429 # css is now the default theme for OPAC.
1430 # It will be the theme used for improvements and new things in OPAC.
1432 # Revision 1.77 2004/05/06 14:56:51 tipaul
1433 # adding table issuingrules (previously called categoryitem
1435 # Revision 1.76 2004/05/03 09:32:25 tipaul
1436 # adding printcirculationsplit parameter (already existed, but was not in systempref by defaul)
1438 # Revision 1.75 2004/04/14 19:49:00 tipaul
1439 # seealso field set to 255 chars
1441 # Revision 1.74 2004/03/11 16:10:16 tipaul
1442 # *** empty log message ***
1444 # Revision 1.73 2004/03/06 20:26:13 tipaul
1445 # adding seealso feature in MARC searches