5 # This script checks for required updates to the database.
7 # Part of the Koha Library Software www.koha.org
8 # Licensed under the GPL.
11 # - Would also be a good idea to offer to do a backup at this time...
13 # NOTE: If you do something more than once in here, make it table driven.
23 use MARC::File::XML ( BinaryEncoding => 'utf8' );
25 # FIXME - The user might be installing a new database, so can't rely
26 # on /etc/koha.conf anyway.
33 %existingtables, # tables already in database
37 $type, $null, $key, $default, $extra,
38 $prefitem, # preference item in systempreferences table
45 my $dbh = C4::Context->dbh;
46 $|=1; # flushes output
48 my $DBversion = "3.00.00.000";
49 # if we are upgrading from Koha 2.2, then we need to run the complete & long updatedatabase
50 # Tables to add if they don't exist
53 `timestamp` TIMESTAMP NOT NULL ,
54 `user` INT( 11 ) NOT NULL default '0' ,
55 `module` TEXT default '',
56 `action` TEXT default '' ,
57 `object` INT(11) NULL ,
58 `info` TEXT default '' ,
59 PRIMARY KEY ( `timestamp` , `user` )
62 module varchar(20) NOT NULL default '',
63 code varchar(20) NOT NULL default '',
64 name varchar(100) NOT NULL default '',
65 title varchar(200) NOT NULL default '',
67 PRIMARY KEY (module,code)
70 alertid int(11) NOT NULL auto_increment,
71 borrowernumber int(11) NOT NULL default '0',
72 type varchar(10) NOT NULL default '',
73 externalid varchar(20) NOT NULL default '',
74 PRIMARY KEY (alertid),
75 KEY borrowernumber (borrowernumber),
76 KEY type (type,externalid)
79 `idnew` int(10) unsigned NOT NULL auto_increment,
80 `title` varchar(250) NOT NULL default '',
82 `lang` varchar(4) NOT NULL default '',
83 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP,
86 repeatable_holidays => "(
87 `id` int(11) NOT NULL auto_increment,
88 `branchcode` varchar(4) NOT NULL default '',
89 `weekday` smallint(6) default NULL,
90 `day` smallint(6) default NULL,
91 `month` smallint(6) default NULL,
92 `title` varchar(50) NOT NULL default '',
93 `description` text NOT NULL,
96 special_holidays => "(
97 `id` int(11) NOT NULL auto_increment,
98 `branchcode` varchar(4) NOT NULL default '',
99 `day` smallint(6) NOT NULL default '0',
100 `month` smallint(6) NOT NULL default '0',
101 `year` smallint(6) NOT NULL default '0',
102 `isexception` smallint(1) NOT NULL default '1',
103 `title` varchar(50) NOT NULL default '',
104 `description` text NOT NULL,
107 overduerules =>"(`branchcode` varchar(255) NOT NULL default '',
108 `categorycode` char(2) NOT NULL default '',
109 `delay1` int(4) default '0',
110 `letter1` varchar(20) default NULL,
111 `debarred1` char(1) default '0',
112 `delay2` int(4) default '0',
113 `debarred2` char(1) default '0',
114 `letter2` varchar(20) default NULL,
115 `delay3` int(4) default '0',
116 `letter3` varchar(20) default NULL,
117 `debarred3` int(1) default '0',
118 PRIMARY KEY (`branchcode`,`categorycode`)
120 cities => "(`cityid` int auto_increment,
121 `city_name` char(100) NOT NULL,
122 `city_zipcode` char(20),
123 PRIMARY KEY (`cityid`)
125 roadtype => "(`roadtypeid` int auto_increment,
126 `road_type` char(100) NOT NULL,
127 PRIMARY KEY (`roadtypeid`)
131 labelid int(11) NOT NULL auto_increment,
132 itemnumber varchar(100) NOT NULL default '',
133 timestamp timestamp(14) NOT NULL,
134 PRIMARY KEY (labelid)
138 id int(4) NOT NULL auto_increment,
139 barcodetype char(100) default '',
140 title tinyint(1) default '0',
141 isbn tinyint(1) default '0',
142 itemtype tinyint(1) default '0',
143 barcode tinyint(1) default '0',
144 dewey tinyint(1) default '0',
145 class tinyint(1) default '0',
146 author tinyint(1) default '0',
147 papertype char(100) default '',
148 startrow int(2) default NULL,
152 reviewid integer NOT NULL auto_increment,
153 borrowernumber integer,
154 biblionumber integer,
157 datereviewed datetime,
158 PRIMARY KEY (reviewid)
160 subscriptionroutinglist=>"(
161 routingid integer NOT NULL auto_increment,
162 borrowernumber integer,
164 subscriptionid integer,
165 PRIMARY KEY (routingid)
169 notify_id int(11) NOT NULL default '0',
170 `borrowernumber` int(11) NOT NULL default '0',
171 `itemnumber` int(11) NOT NULL default '0',
172 `notify_date` date NOT NULL default '0000-00-00',
173 `notify_send_date` date default NULL,
174 `notify_level` int(1) NOT NULL default '0',
175 `method` varchar(20) NOT NULL default ''
179 `charge_id` varchar(5) NOT NULL default '',
180 `description` text NOT NULL,
181 `amount` decimal(28,6) NOT NULL default '0.000000',
182 `min` int(4) NOT NULL default '0',
183 `max` int(4) NOT NULL default '0',
184 `level` int(1) NOT NULL default '0',
185 PRIMARY KEY (`charge_id`)
188 `entry` varchar(255) NOT NULL default '',
189 `weight` bigint(20) NOT NULL default '0',
190 PRIMARY KEY (`entry`)
194 `id` int NOT NULL auto_increment,
195 `biblio_auth_number` int NOT NULL,
196 `operation` char(20) NOT NULL,
197 `server` char(20) NOT NULL ,
199 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci AUTO_INCREMENT=1",
203 my %requirefields = (
204 subscription => { 'letter' => 'char(20) NULL', 'distributedto' => 'text NULL', 'firstacquidate'=>'date NOT NULL','irregularity'=>'TEXT NULL default \'\'','numberpattern'=>'TINYINT(3) NULL default 0', 'callnumber'=>'text NULL', 'hemisphere' =>'TINYINT(3) NULL default 0', 'issuesatonce'=>'TINYINT(3) NOT NULL default 1', 'branchcode' =>'varchar(12) NOT NULL default \'\'', 'manualhistory'=>'TINYINT(1) NOT NULL default 0','internalnotes'=>'LONGTEXT NULL default \'\''},
205 itemtypes => { 'imageurl' => 'char(200) NULL'},
206 aqbookfund => { 'branchcode' => 'varchar(4) NULL'},
207 aqbudget => { 'branchcode' => 'varchar(4) NULL'},
208 auth_header => { 'marc' => 'BLOB NOT NULL', 'linkid' => 'BIGINT(20) NULL'},
209 auth_subfield_structure =>{ 'hidden' => 'TINYINT(3) NOT NULL default 0', 'kohafield' => 'VARCHAR(45) NULL', 'linkid' => 'TINYINT(1) NOT NULL default 0', 'isurl' => 'TINYINT(1)', 'frameworkcode'=>'VARCHAR(8) NOT NULL'},
210 marc_breeding => { 'isbn' => 'varchar(13) NOT NULL'},
211 serial =>{ 'publisheddate' => 'date', 'claimdate' => 'date', 'itemnumber'=>'text NULL','routingnotes'=>'text NULL',},
212 statistics => { 'associatedborrower' => 'integer'},
213 z3950servers =>{ "name" =>"text", "description" => "text NOT NULL",
214 "position" =>"enum('primary','secondary','') NOT NULL default 'primary'", "icon" =>"text",
215 "type" =>"enum('zed','opensearch') NOT NULL default 'zed'",
217 issues =>{ 'issuedate'=>"date NOT NULL default '0000-00-00'", },
219 # tablename => { 'field' => 'fieldtype' },
222 # Enter here the table to delete.
223 my @TableToDelete = qw(
230 my %uselessfields = (
231 # tablename => "field1,field2",
232 borrowers => "suburb,altstreetaddress,altsuburb,altcity,studentnumber,school,area,preferredcont,altcp",
233 deletedborrowers=> "suburb,altstreetaddress,altsuburb,altcity,studentnumber,school,area,preferredcont,altcp",
235 # the other hash contains other actions that can't be done elsewhere. they are done
236 # either BEFORE of AFTER everything else, depending on "when" entry (default => AFTER)
238 # The tabledata hash contains data that should be in the tables.
239 # The uniquefieldrequired hash entry is used to determine which (if any) fields
240 # must not exist in the table for this row to be inserted. If the
241 # uniquefieldrequired entry is already in the table, the existing data is not
242 # modified, unless the forceupdate hash entry is also set. Fields in the
243 # anonymous "forceupdate" hash will be forced to be updated to the default
244 # values given in the %tabledata hash.
248 # { uniquefielrequired => 'fieldname', # the primary key in the table
249 # fieldname => fieldvalue,
250 # fieldname2 => fieldvalue2,
253 systempreferences => [
255 uniquefieldrequired => 'variable',
256 variable => 'useDaysMode',
258 forceupdate => { 'explanation' => 1,
260 explanation => 'How to calculate return dates : Calendar means holidays will be controled, Days means the return date don\'t depend on holidays',
262 options => 'Calendar|Days'
265 uniquefieldrequired => 'variable',
266 variable => 'DebugLevel',
268 forceupdate => { 'explanation' => 1,
270 explanation => 'Set the level of error info sent to the browser. 0=none, 1=some, 2=most',
275 uniquefieldrequired => 'variable',
276 variable => 'BorrowersTitles',
277 value => 'Mr|Mrs|Miss|Ms',
278 forceupdate => { 'explanation' => 1,
280 explanation => 'List all Titles for borrowers',
284 uniquefieldrequired => 'variable',
285 variable => 'BorrowerMandatoryField',
286 value => 'cardnumber|surname|address',
287 forceupdate => { 'explanation' => 1,
289 explanation => 'List all mandatory fields for borrowers',
293 uniquefieldrequired => 'variable',
294 variable => 'borrowerRelationship',
295 value => 'father|mother,grand-mother',
296 forceupdate => { 'explanation' => 1,
298 explanation => 'The relationships between a guarantor & a guarantee (separated by | or ,)',
302 uniquefieldrequired => 'variable',
303 variable => 'ReservesMaxPickUpDelay',
305 forceupdate => { 'explanation' => 1,
307 explanation => 'Maximum delay to pick up a reserved document',
311 uniquefieldrequired => 'variable',
312 variable => 'TransfersMaxDaysWarning',
314 forceupdate => { 'explanation' => 1,
316 explanation => 'Max delay before considering the transfer has potentialy a problem',
320 uniquefieldrequired => 'variable',
321 variable => 'memberofinstitution',
323 forceupdate => { 'explanation' => 1,
325 explanation => 'Are your patrons members of institutions',
329 uniquefieldrequired => 'variable',
330 variable => 'ReadingHistory',
332 forceupdate => { 'explanation' => 1,
334 explanation => 'Allow reading record info retrievable from issues and oldissues tables',
338 uniquefieldrequired => 'variable',
339 variable => 'IssuingInProcess',
341 forceupdate => { 'explanation' => 1,
343 explanation => 'Allow no debt alert if the patron is issuing item that accumulate debt',
347 uniquefieldrequired => 'variable',
348 variable => 'AutomaticItemReturn',
350 forceupdate => { 'explanation' => 1,
352 explanation => 'This Variable allow or not to return automaticly to his homebranch',
356 uniquefieldrequired => 'variable',
357 variable => 'reviewson',
359 forceupdate => { 'explanation' => 1,
361 explanation => 'Allows patrons to submit reviews from the opac',
365 uniquefieldrequired => 'variable',
366 variable => 'intranet_includes',
368 forceupdate => { 'explanation' => 1,
370 explanation => 'The includes directory you want for specific look of Koha (includes or includes_npl for example)',
374 uniquefieldrequired => 'variable',
375 variable => 'AutoLocation',
377 forceupdate => { 'explanation' => 1,
379 explanation => 'switch to activate or not Autolocation, if Yes, the Librarian can\'t change his location, it\'s defined by branchip',
383 uniquefieldrequired => 'variable',
384 variable => 'serialsadditems',
390 explanation => 'If set, a new item will be automatically added when receiving an issue',
394 uniquefieldrequired => 'variable',
395 variable => 'expandedSearchOption',
401 explanation => 'search among marc field',
405 uniquefieldrequired => 'variable',
406 variable => 'RequestOnOpac',
408 forceupdate => { 'explanation' => 1,
410 explanation => 'option to allow reserves on opac',
414 uniquefieldrequired => 'variable',
415 variable => 'OpacCloud',
417 forceupdate => { 'explanation' => 1,
419 explanation => 'Enable / Disable cloud link on OPAC (Require to run misc/cronjobs/build_browser_and_cloud.pl on the server)',
423 uniquefieldrequired => 'variable',
424 variable => 'OpacBrowser',
426 forceupdate => { 'explanation' => 1,
428 explanation => 'Enable/Disable browser link on OPAC (Require to run misc/cronjobs/build_browser_and_cloud.pl on the server)',
432 uniquefieldrequired => 'variable',
433 variable => 'OpacTopissue',
435 forceupdate => { 'explanation' => 1,
437 explanation => 'Enable / Disable the top issue link on OPAC',
441 uniquefieldrequired => 'variable',
442 variable => 'OpacAuthorities',
444 forceupdate => { 'explanation' => 1,
446 explanation => 'Enable / Disable the search authority link on OPAC',
450 uniquefieldrequired => 'variable',
451 variable => 'CataloguingLog',
453 forceupdate => {'explanation' => 1, 'type' => 1},
454 explanation => 'Active this if you want to log cataloguing action.',
458 uniquefieldrequired => 'variable',
459 variable => 'BorrowersLog',
461 forceupdate => {'explanation' => 1, 'type' => 1},
462 explanation => 'Active this if you want to log borrowers edition/creation/deletion...',
466 uniquefieldrequired => 'variable',
467 variable => 'SubscriptionLog',
469 forceupdate => {'explanation' => 1, 'type' => 1},
470 explanation => 'Active this if you want to log Subscription action',
474 uniquefieldrequired => 'variable',
475 variable => 'IssueLog',
477 forceupdate => {'explanation' => 1, 'type' => 1},
478 explanation => 'Active this if you want to log issue.',
482 uniquefieldrequired => 'variable',
483 variable => 'ReturnLog',
485 forceupdate => {'explanation' => 1, 'type' => 1},
486 explanation => 'Active this if you want to log the circulation return',
490 uniquefieldrequired => 'variable',
491 variable => 'Version',
493 forceupdate => {'explanation' => 1, 'type' => 1},
494 explanation => 'Koha Version',
498 uniquefieldrequired => 'variable',
499 variable => 'LetterLog',
501 forceupdate => {'explanation' => 1, 'type' => 1},
502 explanation => 'Active this if you want to log all the letter sent',
506 uniquefieldrequired => 'variable',
507 variable => 'FinesLog',
509 forceupdate => {'explanation' => 1, 'type' => 1},
510 explanation => 'Active this if you want to log fines',
514 uniquefieldrequired => 'variable',
515 variable => 'NoZebra',
517 forceupdate => {'explanation' => 1, 'type' => 1},
518 explanation => 'Active this if you want NOT to use zebra (large libraries should avoid this parameters)',
522 uniquefieldrequired => 'variable',
523 variable => 'NoZebraIndexes',
525 forceupdate => {'explanation' => 1, 'type' => 1},
526 explanation => "Enter a specific hash for NoZebra indexes. Enter : 'indexname' => '100a,245a,500*','index2' => '...'",
530 uniquefieldrequired => 'variable',
531 variable => 'uppercasesurnames',
533 forceupdate => {'explanation' => 1, 'type' => 1},
534 explanation => "Force Surnames to be uppercase",
540 uniquefieldrequired => 'bit',
542 flag => 'editauthorities',
543 flagdesc => 'allow to edit authorities',
547 uniquefieldrequired => 'bit',
550 flagdesc => 'allow to manage serials subscriptions',
554 uniquefieldrequired => 'bit',
557 flagdesc => 'allow to access to the reports module',
561 authorised_values => [
563 uniquefieldrequired => 'id',
564 category => 'SUGGEST',
565 authorised_value => 'Not enough budget',
566 lib => 'This book it too much expensive',
571 my %fielddefinitions = (
573 # { field => 'fieldname',
574 # type => 'fieldtype',
582 field => 'booksellerid',
592 field => 'bookfundid',
593 type => 'varchar(5)',
608 extra => 'auto_increment',
611 field => 'listprice',
612 type => 'varchar(10)',
619 field => 'invoiceprice',
620 type => 'varchar(10)',
627 field => 'invoicedisc',
628 type => 'float(6,4)',
639 field => 'bookfundid',
640 type => 'varchar(5)',
650 field => 'notify_id',
658 field => 'notify_level',
666 field => 'accountno',
667 type => 'smallint(6)',
674 field => 'description',
675 type => 'mediumtext',
680 type => 'mediumtext',
687 { field => 'firstname',
691 { field => 'initials',
695 { field => 'B_email',
698 after => 'B_zipcode',
701 field => 'streetnumber', # street number (hidden if streettable table is empty)
707 field => 'streettype', # street table, list builded from a system table
710 after => 'streetnumber',
717 field => 'B_streetnumber', # street number (hidden if streettable table is empty)
723 field => 'B_streettype', # street table, list builded from a system table
726 after => 'B_streetnumber',
735 field => 'address2', # complement address
747 field => 'contactfirstname', # contact's firstname
750 after => 'contactname',
753 field => 'contacttitle', # contact's title
756 after => 'contactfirstname',
759 field => 'branchcode',
760 type => 'varchar(10)',
766 field => 'categorycode',
767 type => 'varchar(10)',
777 type => 'varchar(10)',
785 type => 'varchar(25)',
793 type => 'varchar(4)',
801 type => 'varchar(30)',
807 field => 'publicationyear',
814 field => 'collectiontitle',
815 type => 'mediumtext',
821 field => 'collectionissn',
822 type => 'mediumtext',
828 field => 'collectionvolume',
829 type => 'mediumtext',
835 field => 'editionstatement',
842 field => 'editionresponsibility',
850 deletedbiblioitems => [
853 type => 'varchar(10)',
860 type => 'varchar(30)',
869 type => 'varchar(15)',
876 field => 'branchprinter',
877 type => 'varchar(100)',
884 field => 'branchcode',
885 type => 'varchar(10)',
893 field => 'frombranch',
894 type => 'VARCHAR(10)',
902 type => 'VARCHAR(10)',
911 field => 'category_type',
919 field => 'categorycode',
920 type => 'varchar(10)',
928 deletedborrowers => [
929 { field => 'firstname',
933 { field => 'initials',
937 { field => 'B_email',
940 after => 'B_zipcode',
943 field => 'streetnumber', # street number (hidden if streettable table is empty)
949 field => 'streettype', # street table, list builded from a system table
952 after => 'streetnumber',
959 field => 'B_streetnumber', # street number (hidden if streettable table is empty)
965 field => 'B_streettype', # street table, list builded from a system table
968 after => 'B_streetnumber',
977 field => 'address2', # complement address
989 field => 'contactfirstname', # contact's firstname
992 after => 'contactname',
995 field => 'contacttitle', # contact's title
998 after => 'contactfirstname',
1004 field => 'borrowernumber',
1006 null => 'NULL', # can be null when a borrower is deleted and the foreign key rule executed
1012 field => 'itemnumber',
1014 null => 'NULL', # can be null when a borrower is deleted and the foreign key rule executed
1020 field => 'branchcode',
1021 type => 'varchar(10)',
1028 field => 'issuedate',
1032 default => '0000-00-00',
1038 field => 'categorycode',
1039 type => 'varchar(10)',
1045 field => 'branchcode',
1046 type => 'varchar(10)',
1052 field => 'itemtype',
1053 type => 'varchar(10)',
1066 default => '0000-00-00',
1070 field => 'cutterextra',
1071 type => 'varchar(45)',
1078 field => 'homebranch',
1079 type => 'varchar(10)',
1086 field => 'holdingbranch',
1087 type => 'varchar(10)',
1095 type => 'varchar(10)',
1104 field => 'itemtype',
1105 type => 'varchar(10)',
1128 marc_subfield_structure => [
1130 field => 'defaultvalue',
1139 field => 'expirationdate',
1156 field => 'waitingdate',
1176 field => 'dateadded',
1177 type => 'timestamp',
1184 type => 'varchar(10)',
1188 field => 'itemtype',
1189 type => 'varchar(10)',
1193 systempreferences => [
1203 field => 'explanation',
1225 # { indexname => 'index detail'
1229 { indexname => 'PRIMARY',
1235 { indexname => 'booksellerid',
1236 content => 'booksellerid',
1240 { indexname => 'basketno',
1241 content => 'basketno',
1244 aqorderbreakdown => [
1245 { indexname => 'ordernumber',
1246 content => 'ordernumber',
1248 { indexname => 'bookfundid',
1249 content => 'bookfundid',
1253 { indexname => 'isbn',
1256 { indexname => 'publishercode',
1257 content => 'publishercode',
1262 indexname => 'branchcode',
1263 content => 'branchcode',
1267 branchrelations => [
1269 indexname => 'PRIMARY',
1270 content => 'categorycode',
1274 branchrelations => [
1275 { indexname => 'PRIMARY',
1276 content => 'branchcode,categorycode',
1279 { indexname => 'branchcode',
1280 content => 'branchcode',
1282 { indexname => 'categorycode',
1283 content => 'categorycode',
1287 { indexname => 'PRIMARY',
1288 content => 'currency',
1294 indexname => 'categorycode',
1295 content => 'categorycode',
1299 { indexname => 'homebranch',
1300 content => 'homebranch',
1302 { indexname => 'holdingbranch',
1303 content => 'holdingbranch',
1308 indexname => 'itemtype',
1309 content => 'itemtype',
1313 { indexname => 'shelfnumber',
1314 content => 'shelfnumber',
1316 { indexname => 'itemnumber',
1317 content => 'itemnumber',
1321 { indexname => 'PRIMARY',
1328 my %foreign_keys = (
1330 # { key => 'the key in table' (must be indexed)
1331 # foreigntable => 'the foreigntable name', # (the parent)
1332 # foreignkey => 'the foreign key column(s)' # (in the parent)
1333 # onUpdate => 'CASCADE|SET NULL|NO ACTION| RESTRICT',
1334 # onDelete => 'CASCADE|SET NULL|NO ACTION| RESTRICT',
1337 branchrelations => [
1338 { key => 'branchcode',
1339 foreigntable => 'branches',
1340 foreignkey => 'branchcode',
1341 onUpdate => 'CASCADE',
1342 onDelete => 'CASCADE',
1344 { key => 'categorycode',
1345 foreigntable => 'branchcategories',
1346 foreignkey => 'categorycode',
1347 onUpdate => 'CASCADE',
1348 onDelete => 'CASCADE',
1352 { key => 'shelfnumber',
1353 foreigntable => 'virtualshelf',
1354 foreignkey => 'shelfnumber',
1355 onUpdate => 'CASCADE',
1356 onDelete => 'CASCADE',
1358 { key => 'itemnumber',
1359 foreigntable => 'items',
1360 foreignkey => 'itemnumber',
1361 onUpdate => 'CASCADE',
1362 onDelete => 'CASCADE',
1365 # onDelete is RESTRICT on reference tables (branches, itemtype) as we don't want items to be
1366 # easily deleted, but branches/itemtype not too easy to empty...
1368 { key => 'biblionumber',
1369 foreigntable => 'biblio',
1370 foreignkey => 'biblionumber',
1371 onUpdate => 'CASCADE',
1372 onDelete => 'CASCADE',
1374 { key => 'itemtype',
1375 foreigntable => 'itemtypes',
1376 foreignkey => 'itemtype',
1377 onUpdate => 'CASCADE',
1378 onDelete => 'RESTRICT',
1382 { key => 'biblioitemnumber',
1383 foreigntable => 'biblioitems',
1384 foreignkey => 'biblioitemnumber',
1385 onUpdate => 'CASCADE',
1386 onDelete => 'CASCADE',
1388 { key => 'homebranch',
1389 foreigntable => 'branches',
1390 foreignkey => 'branchcode',
1391 onUpdate => 'CASCADE',
1392 onDelete => 'RESTRICT',
1394 { key => 'holdingbranch',
1395 foreigntable => 'branches',
1396 foreignkey => 'branchcode',
1397 onUpdate => 'CASCADE',
1398 onDelete => 'RESTRICT',
1402 { key => 'booksellerid',
1403 foreigntable => 'aqbooksellers',
1405 onUpdate => 'CASCADE',
1406 onDelete => 'RESTRICT',
1410 { key => 'basketno',
1411 foreigntable => 'aqbasket',
1412 foreignkey => 'basketno',
1413 onUpdate => 'CASCADE',
1414 onDelete => 'CASCADE',
1416 { key => 'biblionumber',
1417 foreigntable => 'biblio',
1418 foreignkey => 'biblionumber',
1419 onUpdate => 'SET NULL',
1420 onDelete => 'SET NULL',
1424 { key => 'listprice',
1425 foreigntable => 'currency',
1426 foreignkey => 'currency',
1427 onUpdate => 'CASCADE',
1428 onDelete => 'CASCADE',
1430 { key => 'invoiceprice',
1431 foreigntable => 'currency',
1432 foreignkey => 'currency',
1433 onUpdate => 'CASCADE',
1434 onDelete => 'CASCADE',
1437 aqorderbreakdown => [
1438 { key => 'ordernumber',
1439 foreigntable => 'aqorders',
1440 foreignkey => 'ordernumber',
1441 onUpdate => 'CASCADE',
1442 onDelete => 'CASCADE',
1444 { key => 'bookfundid',
1445 foreigntable => 'aqbookfund',
1446 foreignkey => 'bookfundid',
1447 onUpdate => 'CASCADE',
1448 onDelete => 'CASCADE',
1451 branchtransfers => [
1452 { key => 'frombranch',
1453 foreigntable => 'branches',
1454 foreignkey => 'branchcode',
1455 onUpdate => 'CASCADE',
1456 onDelete => 'CASCADE',
1458 { key => 'tobranch',
1459 foreigntable => 'branches',
1460 foreignkey => 'branchcode',
1461 onUpdate => 'CASCADE',
1462 onDelete => 'CASCADE',
1464 { key => 'itemnumber',
1465 foreigntable => 'items',
1466 foreignkey => 'itemnumber',
1467 onUpdate => 'CASCADE',
1468 onDelete => 'CASCADE',
1471 issues => [ # constraint is SET NULL : when a borrower or an item is deleted, we keep the issuing record
1473 { key => 'borrowernumber',
1474 foreigntable => 'borrowers',
1475 foreignkey => 'borrowernumber',
1476 onUpdate => 'SET NULL',
1477 onDelete => 'SET NULL',
1479 { key => 'itemnumber',
1480 foreigntable => 'items',
1481 foreignkey => 'itemnumber',
1482 onUpdate => 'SET NULL',
1483 onDelete => 'SET NULL',
1487 { key => 'borrowernumber',
1488 foreigntable => 'borrowers',
1489 foreignkey => 'borrowernumber',
1490 onUpdate => 'CASCADE',
1491 onDelete => 'CASCADE',
1493 { key => 'biblionumber',
1494 foreigntable => 'biblio',
1495 foreignkey => 'biblionumber',
1496 onUpdate => 'CASCADE',
1497 onDelete => 'CASCADE',
1499 { key => 'itemnumber',
1500 foreigntable => 'items',
1501 foreignkey => 'itemnumber',
1502 onUpdate => 'CASCADE',
1503 onDelete => 'CASCADE',
1505 { key => 'branchcode',
1506 foreigntable => 'branches',
1507 foreignkey => 'branchcode',
1508 onUpdate => 'CASCADE',
1509 onDelete => 'CASCADE',
1512 borrowers => [ # foreign keys are RESTRICT as we don't want to delete borrowers when a branch is deleted
1513 # but prevent deleting a branch as soon as it has 1 borrower !
1514 { key => 'categorycode',
1515 foreigntable => 'categories',
1516 foreignkey => 'categorycode',
1517 onUpdate => 'RESTRICT',
1518 onDelete => 'RESTRICT',
1520 { key => 'branchcode',
1521 foreigntable => 'branches',
1522 foreignkey => 'branchcode',
1523 onUpdate => 'RESTRICT',
1524 onDelete => 'RESTRICT',
1527 deletedborrowers => [ # foreign keys are RESTRICT as we don't want to delete borrowers when a branch is deleted
1528 # but prevent deleting a branch as soon as it has 1 borrower !
1529 { key => 'categorycode',
1530 foreigntable => 'categories',
1531 foreignkey => 'categorycode',
1532 onUpdate => 'RESTRICT',
1533 onDelete => 'RESTRICT',
1535 { key => 'branchcode',
1536 foreigntable => 'branches',
1537 foreignkey => 'branchcode',
1538 onUpdate => 'RESTRICT',
1539 onDelete => 'RESTRICT',
1543 { key => 'borrowernumber',
1544 foreigntable => 'borrowers',
1545 foreignkey => 'borrowernumber',
1546 onUpdate => 'CASCADE',
1547 onDelete => 'CASCADE',
1549 { key => 'itemnumber',
1550 foreigntable => 'items',
1551 foreignkey => 'itemnumber',
1552 onUpdate => 'SET NULL',
1553 onDelete => 'SET NULL',
1556 auth_tag_structure => [
1557 { key => 'authtypecode',
1558 foreigntable => 'auth_types',
1559 foreignkey => 'authtypecode',
1560 onUpdate => 'CASCADE',
1561 onDelete => 'CASCADE',
1564 # FIXME : don't constraint auth_*_table and auth_word, as they may be replaced by zebra
1569 my %column_change = (
1573 from => 'emailaddress',
1578 from => 'streetaddress',
1580 after => 'initials',
1583 from => 'faxnumber',
1588 from => 'textmessaging',
1594 to => 'contactnote',
1595 after => 'opacnote',
1598 from => 'physstreet',
1603 from => 'streetcity',
1605 after => 'B_address',
1618 from => 'homezipcode',
1625 after => 'B_zipcode',
1630 after => 'dateenrolled',
1633 from => 'guarantor',
1634 to => 'guarantorid',
1635 after => 'contactname',
1638 from => 'altrelationship',
1639 to => 'relationship',
1640 after => 'borrowernotes',
1644 deletedborrowers => [
1646 from => 'emailaddress',
1651 from => 'streetaddress',
1653 after => 'initials',
1656 from => 'faxnumber',
1661 from => 'textmessaging',
1667 to => 'contactnote',
1668 after => 'opacnote',
1671 from => 'physstreet',
1676 from => 'streetcity',
1678 after => 'B_address',
1691 from => 'homezipcode',
1698 after => 'B_zipcode',
1703 after => 'dateenrolled',
1706 from => 'guarantor',
1707 to => 'guarantorid',
1708 after => 'contactname',
1711 from => 'altrelationship',
1712 to => 'relationship',
1713 after => 'borrowernotes',
1719 # MOVE all tables TO UTF-8 and innoDB
1720 $sth = $dbh->prepare("show table status");
1722 while ( my $table = $sth->fetchrow_hashref ) {
1723 next if $table->{Name} eq 'marc_word';
1724 next if $table->{Name} eq 'marc_subfield_table';
1725 next if $table->{Name} eq 'auth_word';
1726 next if $table->{Name} eq 'auth_subfield_table';
1727 if ($table->{Engine} ne 'InnoDB') {
1728 print "moving $table->{Name} to InnoDB\n";
1729 $dbh->do("ALTER TABLE $table->{Name} TYPE = innodb");
1731 unless ($table->{Collation} =~ /^utf8/) {
1732 print "moving $table->{Name} to utf8\n";
1733 $dbh->do("ALTER TABLE $table->{Name} CONVERT TO CHARACTER SET utf8");
1734 $dbh->do("ALTER TABLE $table->{Name} DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci");
1735 # FIXME : maybe a ALTER TABLE tbl_name CONVERT TO CHARACTER SET utf8 would be better, def char set seems to work fine. If any problem encountered, let's try with convert !
1741 foreach my $table (keys %column_change) {
1742 $sth = $dbh->prepare("show columns from $table");
1745 while ( ( $column, $type, $null, $key, $default, $extra ) = $sth->fetchrow )
1747 $types{$column}->{type} ="$type";
1748 $types{$column}->{null} = "$null";
1749 $types{$column}->{key} = "$key";
1750 $types{$column}->{default} = "$default";
1751 $types{$column}->{extra} = "$extra";
1753 my $tablerows = $column_change{$table};
1754 foreach my $row ( @$tablerows ) {
1755 if ($types{$row->{from}}->{type}) {
1756 print "altering $table $row->{from} to $row->{to}\n";
1757 # ALTER TABLE `borrowers` CHANGE `faxnumber` `fax` TEXT CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL
1758 # alter table `borrowers` change `faxnumber` `fax` type text null after phone
1760 "alter table `$table` change `$row->{from}` `$row->{to}` $types{$row->{from}}->{type} ".
1761 ($types{$row->{from}}->{null} eq 'YES'?" NULL":" NOT NULL").
1762 ($types{$row->{from}}->{default}?" default ".$types{$row->{from}}->{default}:"").
1763 "$types{$row->{from}}->{extra} after $row->{after} ";
1770 # Enter here the field you want to delete from DB.
1771 # FIXME :: there is a %uselessfield before which seems doing the same things.
1772 my %fieldtodelete = (
1773 # tablename => [fieldname1,fieldname2,...]
1777 print "removing some unused fields...\n";
1778 foreach my $table ( keys %fieldtodelete ) {
1779 foreach my $field ( @{$fieldtodelete{$table}} ){
1780 print "removing ".$field." from ".$table;
1781 my $sth = $dbh->prepare("ALTER TABLE $table DROP $field");
1784 print "Error : $sth->errstr \n";
1789 # Enter here the line you want to remove from DB.
1790 my %linetodelete = (
1791 # table name => where clause.
1792 userflags => "bit = 8", # delete the 'reserveforself' flags
1796 #-------------------
1801 # Get version of MySQL database engine.
1802 my $mysqlversion = `mysqld --version`;
1803 $mysqlversion =~ /Ver (\S*) /;
1805 if ( $mysqlversion ge '3.23' ) {
1806 print "Could convert to MyISAM database tables...\n" unless $silent;
1809 #---------------------------------
1812 # Collect all tables into a list
1813 $sth = $dbh->prepare("show tables");
1815 while ( my ($table) = $sth->fetchrow ) {
1816 $existingtables{$table} = 1;
1820 # Now add any missing tables
1821 foreach $table ( keys %requiretables ) {
1822 unless ( $existingtables{$table} ) {
1823 print "Adding $table table...\n" unless $silent;
1824 my $sth = $dbh->prepare("create table $table $requiretables{$table} ENGINE=InnoDB DEFAULT CHARSET=utf8");
1827 print "Error : $sth->errstr \n";
1833 #---------------------------------
1836 foreach $table ( keys %requirefields ) {
1837 print "Check table $table\n" if $debug and not $silent;
1838 $sth = $dbh->prepare("show columns from $table");
1841 while ( ( $column, $type, $null, $key, $default, $extra ) = $sth->fetchrow )
1843 $types{$column} = $type;
1845 foreach $column ( keys %{ $requirefields{$table} } ) {
1846 print " Check column $column [$types{$column}]\n" if $debug and not $silent;
1847 if ( !$types{$column} ) {
1849 # column doesn't exist
1850 print "Adding $column field to $table table...\n" unless $silent;
1851 $query = "alter table $table
1852 add column $column " . $requirefields{$table}->{$column};
1853 print "Execute: $query\n" if $debug;
1854 my $sti = $dbh->prepare($query);
1857 print "**Error : $sti->errstr \n";
1864 foreach $table ( keys %fielddefinitions ) {
1865 print "Check table $table\n" if $debug;
1866 $sth = $dbh->prepare("show columns from $table");
1869 while ( ( $column, $type, $null, $key, $default, $extra ) = $sth->fetchrow )
1871 $definitions->{$column}->{type} = $type;
1872 $definitions->{$column}->{null} = $null;
1873 $definitions->{$column}->{null} = 'NULL' if $null eq 'YES';
1874 $definitions->{$column}->{key} = $key;
1875 $definitions->{$column}->{default} = $default;
1876 $definitions->{$column}->{extra} = $extra;
1878 my $fieldrow = $fielddefinitions{$table};
1879 foreach my $row (@$fieldrow) {
1880 my $field = $row->{field};
1881 my $type = $row->{type};
1882 my $null = $row->{null};
1883 # $null = 'YES' if $row->{null} eq 'NULL';
1884 my $key = $row->{key};
1885 my $default = $row->{default};
1886 # $default="''" unless $default;
1887 my $extra = $row->{extra};
1888 my $def = $definitions->{$field};
1889 my $after = ($row->{after}?" after ".$row->{after}:"");
1891 unless ( $type eq $def->{type}
1892 && $null eq $def->{null}
1893 && $key eq $def->{key}
1894 && $extra eq $def->{extra} )
1896 if ( $null eq '' ) {
1899 if ( $key eq 'PRI' ) {
1900 $key = 'PRIMARY KEY';
1902 unless ( $extra eq 'auto_increment' ) {
1906 # if it's a new column use "add", if it's an old one, use "change".
1908 if ($definitions->{$field}->{type}) {
1909 $action="change $field"
1913 # if it's a primary key, drop the previous pk, before altering the table
1914 print " alter or create $field in $table\n" unless $silent;
1916 if ($key ne 'PRIMARY KEY') {
1917 # warn "alter table $table $action $field $type $null $key $extra default $default $after";
1918 $query = "alter table $table $action $field $type $null $key $extra ".($default?"default ".$dbh->quote($default):"")." $after";
1920 # warn "alter table $table drop primary key, $action $field $type $null $key $extra default $default $after";
1921 # something strange : for indexes UNIQUE, they are reported as primary key here.
1922 # but if you try to run with drop primary key, it fails.
1923 # thus, we run the query twice, one will fail, one will succeed.
1925 $query="alter table $table drop primary key, $action $field $type $null $key $extra ".($default?"default ".$dbh->quote($default):"")." $after";
1926 $query="alter table $table $action $field $type $null $key $extra ".($default?"default ".$dbh->quote($default):"")." $after";
1933 print "removing some unused data...\n";
1934 foreach my $table ( keys %linetodelete ) {
1935 foreach my $where ( @{linetodelete{$table}} ){
1936 print "DELETE FROM ".$table." where ".$where;
1938 my $sth = $dbh->prepare("DELETE FROM $table where $where");
1941 print "Error : $sth->errstr \n";
1946 # Populate tables with required data
1948 # synch table and deletedtable.
1949 foreach my $table (('borrowers','items','biblio','biblioitems')) {
1950 my %deletedborrowers;
1951 print "synch'ing $table and deleted$table\n";
1952 $sth = $dbh->prepare("show columns from deleted$table");
1954 while ( my ( $column, $type, $null, $key, $default, $extra ) = $sth->fetchrow ) {
1955 $deletedborrowers{$column}=1;
1957 $sth = $dbh->prepare("show columns from $table");
1960 while ( my ( $column, $type, $null, $key, $default, $extra ) = $sth->fetchrow ) {
1961 unless ($deletedborrowers{$column}) {
1962 my $newcol="alter table deleted$table add $column $type";
1963 if ($null eq 'YES') {
1964 $newcol .= " NULL ";
1966 $newcol .= " NOT NULL ";
1968 $newcol .= "default ".$dbh->quote($default) if $default;
1969 $newcol .= " after $previous" if $previous;
1971 print "creating column $column\n";
1977 # update publisheddate
1979 $sth = $dbh->prepare("select count(*) from serial where publisheddate is NULL");
1981 my ($emptypublished) = $sth->fetchrow;
1982 if ($emptypublished) {
1983 print "Updating publisheddate\n";
1984 $dbh->do("update serial set publisheddate=planneddate where publisheddate is NULL");
1986 foreach my $table ( keys %tabledata ) {
1987 print "Checking for data required in table $table...\n" unless $silent;
1988 my $tablerows = $tabledata{$table};
1989 foreach my $row (@$tablerows) {
1990 my $uniquefieldrequired = $row->{uniquefieldrequired};
1991 my $uniquevalue = $row->{$uniquefieldrequired};
1992 my $forceupdate = $row->{forceupdate};
1995 "select $uniquefieldrequired from $table where $uniquefieldrequired=?"
1997 $sth->execute($uniquevalue);
1999 foreach my $field (keys %$forceupdate) {
2000 if ($forceupdate->{$field}) {
2001 my $sth=$dbh->prepare("update systempreferences set $field=? where $uniquefieldrequired=?");
2002 $sth->execute($row->{$field}, $uniquevalue);
2006 print "Adding row to $table: " unless $silent;
2010 foreach my $field ( keys %$row ) {
2011 next if $field eq 'uniquefieldrequired';
2012 next if $field eq 'forceupdate';
2013 my $value = $row->{$field};
2014 push @values, $value;
2015 print " $field => $value" unless $silent;
2016 $fieldlist .= "$field,";
2017 $placeholders .= "?,";
2019 print "\n" unless $silent;
2020 $fieldlist =~ s/,$//;
2021 $placeholders =~ s/,$//;
2022 print "insert into $table ($fieldlist) values ($placeholders)";
2025 "insert into $table ($fieldlist) values ($placeholders)");
2026 $sth->execute(@values);
2032 # check indexes and create them when needed
2034 print "Checking for index required...\n" unless $silent;
2035 foreach my $table ( keys %indexes ) {
2037 # read all indexes from $table
2039 $sth = $dbh->prepare("show index from $table");
2041 my %existingindexes;
2042 while ( my ( $table, $non_unique, $key_name, $Seq_in_index, $Column_name, $Collation, $cardinality, $sub_part, $Packed, $comment ) = $sth->fetchrow ) {
2043 $existingindexes{$key_name} = 1;
2045 # read indexes to check
2046 my $tablerows = $indexes{$table};
2047 foreach my $row (@$tablerows) {
2048 my $key_name=$row->{indexname};
2049 if ($existingindexes{$key_name} eq 1) {
2050 # print "$key_name existing";
2052 print "\tCreating index $key_name in $table\n";
2054 if ($row->{indexname} eq 'PRIMARY') {
2055 $sql = "alter table $table ADD PRIMARY KEY ($row->{content})";
2057 $sql = "alter table $table ADD INDEX $key_name ($row->{content}) $row->{type}";
2060 print "Error $sql : $dbh->err \n" if $dbh->err;
2066 # check foreign keys and create them when needed
2068 print "Checking for foreign keys required...\n" unless $silent;
2069 foreach my $table ( keys %foreign_keys ) {
2071 # read all indexes from $table
2073 $sth = $dbh->prepare("show table status like '$table'");
2075 my $stat = $sth->fetchrow_hashref;
2076 # read indexes to check
2077 my $tablerows = $foreign_keys{$table};
2078 foreach my $row (@$tablerows) {
2079 my $foreign_table=$row->{foreigntable};
2080 if ($stat->{'Comment'} =~/$foreign_table/) {
2081 # print "$foreign_table existing\n";
2083 print "\tCreating foreign key $foreign_table in $table\n";
2084 # first, drop any orphan value in child table
2085 if ($row->{onDelete} ne "RESTRICT") {
2086 my $sql = "delete from $table where $row->{key} not in (select $row->{foreignkey} from $row->{foreigntable})";
2088 print "SQL ERROR: $sql : $dbh->err \n" if $dbh->err;
2090 my $sql="alter table $table ADD FOREIGN KEY $row->{key} ($row->{key}) REFERENCES $row->{foreigntable} ($row->{foreignkey})";
2091 $sql .= " on update ".$row->{onUpdate} if $row->{onUpdate};
2092 $sql .= " on delete ".$row->{onDelete} if $row->{onDelete};
2095 print "====================
2096 An error occured during :
2098 It probably means there is something wrong in your DB : a row ($table.$row->{key}) refers to a value in $row->{foreigntable}.$row->{foreignkey} that does not exist. solve the problem and run updater again (or just the previous SQL statement).
2099 You can find those values with select
2100 \t$table.* from $table where $row->{key} not in (select $row->{foreignkey} from $row->{foreigntable})
2101 ====================\n
2107 # now drop useless tables
2108 foreach $table ( @TableToDelete ) {
2109 if ( $existingtables{$table} ) {
2110 print "Dropping unused table $table\n" if $debug and not $silent;
2111 $dbh->do("drop table $table");
2113 print "Error : $dbh->errstr \n";
2122 # create frameworkcode row in biblio table & fill it with marc_biblio.frameworkcode.
2125 # 1st, get how many biblio we will have to do...
2126 $sth = $dbh->prepare('select count(*) from marc_biblio');
2128 my ($totaltodo) = $sth->fetchrow;
2130 $sth = $dbh->prepare("show columns from biblio");
2133 my $bibliofwexist=0;
2134 while ( ( $column, $type, $null, $key, $default, $extra ) = $sth->fetchrow ){
2135 $bibliofwexist=1 if $column eq 'frameworkcode';
2137 unless ($bibliofwexist) {
2138 print "moving biblioframework to biblio table\n";
2139 $dbh->do('ALTER TABLE `biblio` ADD `frameworkcode` VARCHAR( 4 ) NOT NULL AFTER `biblionumber`');
2140 $sth = $dbh->prepare('select biblionumber,frameworkcode from marc_biblio');
2142 my $sth_update = $dbh->prepare('update biblio set frameworkcode=? where biblionumber=?');
2144 while (my ($biblionumber,$frameworkcode) = $sth->fetchrow) {
2145 $sth_update->execute($frameworkcode,$biblionumber);
2147 print "\r$totaldone / $totaltodo" unless ($totaldone % 100);
2152 # at last, remove useless fields
2153 foreach $table ( keys %uselessfields ) {
2154 my @fields = split /,/,$uselessfields{$table};
2157 foreach my $fieldtodrop (@fields) {
2158 $fieldtodrop =~ s/\t//g;
2159 $fieldtodrop =~ s/\n//g;
2161 $sth = $dbh->prepare("show columns from $table");
2163 while ( my ( $column, $type, $null, $key, $default, $extra ) = $sth->fetchrow )
2165 $exists =1 if ($column eq $fieldtodrop);
2168 print "deleting $fieldtodrop field in $table...\n" unless $silent;
2169 my $sth = $dbh->prepare("alter table $table drop $fieldtodrop");
2176 # Changing aqbookfund's primary key
2178 $sth=$dbh->prepare("ALTER TABLE `aqbookfund` DROP PRIMARY KEY , ADD PRIMARY KEY ( `bookfundid` , `branchcode` ) ;");
2182 print "upgrade to Koha 3.0 done\n";
2183 SetVersion ($DBversion);
2186 =item TransformToNum
2188 Transform the Koha version from a 4 parts string
2189 to a number, with just 1 .
2193 sub TransformToNum {
2194 my $version = shift;
2195 # remove the 3 last . to have a Perl number
2196 $version =~ s/(.*\..*)\.(.*)\.(.*)/$1$2$3/;
2201 set the DBversion in the systempreferences
2205 my $kohaversion = TransformToNum(shift);
2206 if (C4::Context->preference('Version')) {
2207 my $finish=$dbh->prepare("UPDATE systempreferences SET value=? WHERE variable='Version'");
2208 $finish->execute($kohaversion);
2210 my $finish=$dbh->prepare("INSERT into systempreferences (variable,value,explanation) values ('Version',?,'The Koha database version. Don t change this value manually, it s holded by the webinstaller')");
2211 $finish->execute($kohaversion);
2216 # Revision 1.172 2007/07/19 10:21:22 hdl