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 use MARC::File::XML ( BinaryEncoding => 'utf8' );
26 # FIXME - The user might be installing a new database, so can't rely
27 # on /etc/koha.conf anyway.
34 %existingtables, # tables already in database
38 $type, $null, $key, $default, $extra,
39 $prefitem, # preference item in systempreferences table
46 my $dbh = C4::Context->dbh;
47 $|=1; # flushes output
49 my $DBversion = "3.00.00.000";
50 # if we are upgrading from Koha 2.2, then we need to run the complete & long updatedatabase
51 # Tables to add if they don't exist
54 `timestamp` TIMESTAMP NOT NULL ,
55 `user` INT( 11 ) NOT NULL default '0' ,
56 `module` TEXT default '',
57 `action` TEXT default '' ,
58 `object` INT(11) NULL ,
59 `info` TEXT default '' ,
60 PRIMARY KEY ( `timestamp` , `user` )
63 module varchar(20) NOT NULL default '',
64 code varchar(20) NOT NULL default '',
65 name varchar(100) NOT NULL default '',
66 title varchar(200) NOT NULL default '',
68 PRIMARY KEY (module,code)
71 alertid int(11) NOT NULL auto_increment,
72 borrowernumber int(11) NOT NULL default '0',
73 type varchar(10) NOT NULL default '',
74 externalid varchar(20) NOT NULL default '',
75 PRIMARY KEY (alertid),
76 KEY borrowernumber (borrowernumber),
77 KEY type (type,externalid)
80 `idnew` int(10) unsigned NOT NULL auto_increment,
81 `title` varchar(250) NOT NULL default '',
83 `lang` varchar(4) NOT NULL default '',
84 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP,
87 repeatable_holidays => "(
88 `id` int(11) NOT NULL auto_increment,
89 `branchcode` varchar(4) NOT NULL default '',
90 `weekday` smallint(6) default NULL,
91 `day` smallint(6) default NULL,
92 `month` smallint(6) default NULL,
93 `title` varchar(50) NOT NULL default '',
94 `description` text NOT NULL,
97 special_holidays => "(
98 `id` int(11) NOT NULL auto_increment,
99 `branchcode` varchar(4) NOT NULL default '',
100 `day` smallint(6) NOT NULL default '0',
101 `month` smallint(6) NOT NULL default '0',
102 `year` smallint(6) NOT NULL default '0',
103 `isexception` smallint(1) NOT NULL default '1',
104 `title` varchar(50) NOT NULL default '',
105 `description` text NOT NULL,
108 overduerules =>"(`branchcode` varchar(255) NOT NULL default '',
109 `categorycode` char(2) NOT NULL default '',
110 `delay1` int(4) default '0',
111 `letter1` varchar(20) default NULL,
112 `debarred1` char(1) default '0',
113 `delay2` int(4) default '0',
114 `debarred2` char(1) default '0',
115 `letter2` varchar(20) default NULL,
116 `delay3` int(4) default '0',
117 `letter3` varchar(20) default NULL,
118 `debarred3` int(1) default '0',
119 PRIMARY KEY (`branchcode`,`categorycode`)
121 cities => "(`cityid` int auto_increment,
122 `city_name` char(100) NOT NULL,
123 `city_zipcode` char(20),
124 PRIMARY KEY (`cityid`)
126 roadtype => "(`roadtypeid` int auto_increment,
127 `road_type` char(100) NOT NULL,
128 PRIMARY KEY (`roadtypeid`)
132 labelid int(11) NOT NULL auto_increment,
133 itemnumber varchar(100) NOT NULL default '',
134 timestamp timestamp(14) NOT NULL,
135 PRIMARY KEY (labelid)
139 id int(4) NOT NULL auto_increment,
140 barcodetype char(100) default '',
141 title tinyint(1) default '0',
142 isbn tinyint(1) default '0',
143 itemtype tinyint(1) default '0',
144 barcode tinyint(1) default '0',
145 dewey tinyint(1) default '0',
146 class tinyint(1) default '0',
147 author tinyint(1) default '0',
148 papertype char(100) default '',
149 startrow int(2) default NULL,
153 reviewid integer NOT NULL auto_increment,
154 borrowernumber integer,
155 biblionumber integer,
158 datereviewed datetime,
159 PRIMARY KEY (reviewid)
161 subscriptionroutinglist=>"(
162 routingid integer NOT NULL auto_increment,
163 borrowernumber integer,
165 subscriptionid integer,
166 PRIMARY KEY (routingid)
170 notify_id int(11) NOT NULL default '0',
171 `borrowernumber` int(11) NOT NULL default '0',
172 `itemnumber` int(11) NOT NULL default '0',
173 `notify_date` date NOT NULL default '0000-00-00',
174 `notify_send_date` date default NULL,
175 `notify_level` int(1) NOT NULL default '0',
176 `method` varchar(20) NOT NULL default ''
180 `charge_id` varchar(5) NOT NULL default '',
181 `description` text NOT NULL,
182 `amount` decimal(28,6) NOT NULL default '0.000000',
183 `min` int(4) NOT NULL default '0',
184 `max` int(4) NOT NULL default '0',
185 `level` int(1) NOT NULL default '0',
186 PRIMARY KEY (`charge_id`)
189 `entry` varchar(255) NOT NULL default '',
190 `weight` bigint(20) NOT NULL default '0',
191 PRIMARY KEY (`entry`)
195 `id` int NOT NULL auto_increment,
196 `biblio_auth_number` int NOT NULL,
197 `operation` char(20) NOT NULL,
198 `server` char(20) NOT NULL ,
200 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci AUTO_INCREMENT=1",
204 my %requirefields = (
205 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 \'\''},
206 itemtypes => { 'imageurl' => 'char(200) NULL'},
207 aqbookfund => { 'branchcode' => 'varchar(4) NULL'},
208 aqbudget => { 'branchcode' => 'varchar(4) NULL'},
209 auth_header => { 'marc' => 'BLOB NOT NULL', 'linkid' => 'BIGINT(20) NULL'},
210 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'},
211 marc_breeding => { 'isbn' => 'varchar(13) NOT NULL'},
212 serial =>{ 'publisheddate' => 'date', 'claimdate' => 'date', 'itemnumber'=>'text NULL','routingnotes'=>'text NULL',},
213 statistics => { 'associatedborrower' => 'integer'},
214 z3950servers =>{ "name" =>"text", "description" => "text NOT NULL",
215 "position" =>"enum('primary','secondary','') NOT NULL default 'primary'", "icon" =>"text",
216 "type" =>"enum('zed','opensearch') NOT NULL default 'zed'",
218 issues =>{ 'issuedate'=>"date NOT NULL default '0000-00-00'", },
220 # tablename => { 'field' => 'fieldtype' },
223 # Enter here the table to delete.
224 my @TableToDelete = qw(
231 my %uselessfields = (
232 # tablename => "field1,field2",
233 borrowers => "suburb,altstreetaddress,altsuburb,altcity,studentnumber,school,area,preferredcont,altcp",
234 deletedborrowers=> "suburb,altstreetaddress,altsuburb,altcity,studentnumber,school,area,preferredcont,altcp",
236 # the other hash contains other actions that can't be done elsewhere. they are done
237 # either BEFORE of AFTER everything else, depending on "when" entry (default => AFTER)
239 # The tabledata hash contains data that should be in the tables.
240 # The uniquefieldrequired hash entry is used to determine which (if any) fields
241 # must not exist in the table for this row to be inserted. If the
242 # uniquefieldrequired entry is already in the table, the existing data is not
243 # modified, unless the forceupdate hash entry is also set. Fields in the
244 # anonymous "forceupdate" hash will be forced to be updated to the default
245 # values given in the %tabledata hash.
249 # { uniquefielrequired => 'fieldname', # the primary key in the table
250 # fieldname => fieldvalue,
251 # fieldname2 => fieldvalue2,
254 systempreferences => [
256 uniquefieldrequired => 'variable',
257 variable => 'useDaysMode',
259 forceupdate => { 'explanation' => 1,
261 explanation => 'How to calculate return dates : Calendar means holidays will be controled, Days means the return date don\'t depend on holidays',
263 options => 'Calendar|Days'
266 uniquefieldrequired => 'variable',
267 variable => 'DebugLevel',
269 forceupdate => { 'explanation' => 1,
271 explanation => 'Set the level of error info sent to the browser. 0=none, 1=some, 2=most',
276 uniquefieldrequired => 'variable',
277 variable => 'BorrowersTitles',
278 value => 'Mr|Mrs|Miss|Ms',
279 forceupdate => { 'explanation' => 1,
281 explanation => 'List all Titles for borrowers',
285 uniquefieldrequired => 'variable',
286 variable => 'BorrowerMandatoryField',
287 value => 'cardnumber|surname|address',
288 forceupdate => { 'explanation' => 1,
290 explanation => 'List all mandatory fields for borrowers',
294 uniquefieldrequired => 'variable',
295 variable => 'borrowerRelationship',
296 value => 'father|mother,grand-mother',
297 forceupdate => { 'explanation' => 1,
299 explanation => 'The relationships between a guarantor & a guarantee (separated by | or ,)',
303 uniquefieldrequired => 'variable',
304 variable => 'ReservesMaxPickUpDelay',
306 forceupdate => { 'explanation' => 1,
308 explanation => 'Maximum delay to pick up a reserved document',
312 uniquefieldrequired => 'variable',
313 variable => 'TransfersMaxDaysWarning',
315 forceupdate => { 'explanation' => 1,
317 explanation => 'Max delay before considering the transfer has potentialy a problem',
321 uniquefieldrequired => 'variable',
322 variable => 'memberofinstitution',
324 forceupdate => { 'explanation' => 1,
326 explanation => 'Are your patrons members of institutions',
330 uniquefieldrequired => 'variable',
331 variable => 'ReadingHistory',
333 forceupdate => { 'explanation' => 1,
335 explanation => 'Allow reading record info retrievable from issues and oldissues tables',
339 uniquefieldrequired => 'variable',
340 variable => 'IssuingInProcess',
342 forceupdate => { 'explanation' => 1,
344 explanation => 'Allow no debt alert if the patron is issuing item that accumulate debt',
348 uniquefieldrequired => 'variable',
349 variable => 'AutomaticItemReturn',
351 forceupdate => { 'explanation' => 1,
353 explanation => 'This Variable allow or not to return automaticly to his homebranch',
357 uniquefieldrequired => 'variable',
358 variable => 'reviewson',
360 forceupdate => { 'explanation' => 1,
362 explanation => 'Allows patrons to submit reviews from the opac',
366 uniquefieldrequired => 'variable',
367 variable => 'intranet_includes',
369 forceupdate => { 'explanation' => 1,
371 explanation => 'The includes directory you want for specific look of Koha (includes or includes_npl for example)',
375 uniquefieldrequired => 'variable',
376 variable => 'AutoLocation',
378 forceupdate => { 'explanation' => 1,
380 explanation => 'switch to activate or not Autolocation, if Yes, the Librarian can\'t change his location, it\'s defined by branchip',
384 uniquefieldrequired => 'variable',
385 variable => 'serialsadditems',
391 explanation => 'If set, a new item will be automatically added when receiving an issue',
395 uniquefieldrequired => 'variable',
396 variable => 'expandedSearchOption',
402 explanation => 'search among marc field',
406 uniquefieldrequired => 'variable',
407 variable => 'RequestOnOpac',
409 forceupdate => { 'explanation' => 1,
411 explanation => 'option to allow reserves on opac',
415 uniquefieldrequired => 'variable',
416 variable => 'OpacCloud',
418 forceupdate => { 'explanation' => 1,
420 explanation => 'Enable / Disable cloud link on OPAC (Require to run misc/cronjobs/build_browser_and_cloud.pl on the server)',
424 uniquefieldrequired => 'variable',
425 variable => 'OpacBrowser',
427 forceupdate => { 'explanation' => 1,
429 explanation => 'Enable/Disable browser link on OPAC (Require to run misc/cronjobs/build_browser_and_cloud.pl on the server)',
433 uniquefieldrequired => 'variable',
434 variable => 'OpacTopissue',
436 forceupdate => { 'explanation' => 1,
438 explanation => 'Enable / Disable the top issue link on OPAC',
442 uniquefieldrequired => 'variable',
443 variable => 'OpacAuthorities',
445 forceupdate => { 'explanation' => 1,
447 explanation => 'Enable / Disable the search authority link on OPAC',
451 uniquefieldrequired => 'variable',
452 variable => 'CataloguingLog',
454 forceupdate => {'explanation' => 1, 'type' => 1},
455 explanation => 'Active this if you want to log cataloguing action.',
459 uniquefieldrequired => 'variable',
460 variable => 'BorrowersLog',
462 forceupdate => {'explanation' => 1, 'type' => 1},
463 explanation => 'Active this if you want to log borrowers edition/creation/deletion...',
467 uniquefieldrequired => 'variable',
468 variable => 'SubscriptionLog',
470 forceupdate => {'explanation' => 1, 'type' => 1},
471 explanation => 'Active this if you want to log Subscription action',
475 uniquefieldrequired => 'variable',
476 variable => 'IssueLog',
478 forceupdate => {'explanation' => 1, 'type' => 1},
479 explanation => 'Active this if you want to log issue.',
483 uniquefieldrequired => 'variable',
484 variable => 'ReturnLog',
486 forceupdate => {'explanation' => 1, 'type' => 1},
487 explanation => 'Active this if you want to log the circulation return',
491 uniquefieldrequired => 'variable',
492 variable => 'Version',
494 forceupdate => {'explanation' => 1, 'type' => 1},
495 explanation => 'Koha Version',
499 uniquefieldrequired => 'variable',
500 variable => 'LetterLog',
502 forceupdate => {'explanation' => 1, 'type' => 1},
503 explanation => 'Active this if you want to log all the letter sent',
507 uniquefieldrequired => 'variable',
508 variable => 'FinesLog',
510 forceupdate => {'explanation' => 1, 'type' => 1},
511 explanation => 'Active this if you want to log fines',
515 uniquefieldrequired => 'variable',
516 variable => 'NoZebra',
518 forceupdate => {'explanation' => 1, 'type' => 1},
519 explanation => 'Active this if you want NOT to use zebra (large libraries should avoid this parameters)',
523 uniquefieldrequired => 'variable',
524 variable => 'NoZebraIndexes',
526 forceupdate => {'explanation' => 1, 'type' => 1},
527 explanation => "Enter a specific hash for NoZebra indexes. Enter : 'indexname' => '100a,245a,500*','index2' => '...'",
531 uniquefieldrequired => 'variable',
532 variable => 'uppercasesurnames',
534 forceupdate => {'explanation' => 1, 'type' => 1},
535 explanation => "Force Surnames to be uppercase",
541 uniquefieldrequired => 'bit',
543 flag => 'editauthorities',
544 flagdesc => 'allow to edit authorities',
548 uniquefieldrequired => 'bit',
551 flagdesc => 'allow to manage serials subscriptions',
555 uniquefieldrequired => 'bit',
558 flagdesc => 'allow to access to the reports module',
562 authorised_values => [
564 uniquefieldrequired => 'id',
565 category => 'SUGGEST',
566 authorised_value => 'Not enough budget',
567 lib => 'This book it too much expensive',
572 my %fielddefinitions = (
574 # { field => 'fieldname',
575 # type => 'fieldtype',
583 field => 'booksellerid',
593 field => 'bookfundid',
594 type => 'varchar(5)',
609 extra => 'auto_increment',
612 field => 'listprice',
613 type => 'varchar(10)',
620 field => 'invoiceprice',
621 type => 'varchar(10)',
628 field => 'invoicedisc',
629 type => 'float(6,4)',
640 field => 'bookfundid',
641 type => 'varchar(5)',
651 field => 'notify_id',
659 field => 'notify_level',
667 field => 'accountno',
668 type => 'smallint(6)',
675 field => 'description',
676 type => 'mediumtext',
681 type => 'mediumtext',
688 { field => 'firstname',
692 { field => 'initials',
696 { field => 'B_email',
699 after => 'B_zipcode',
702 field => 'streetnumber', # street number (hidden if streettable table is empty)
708 field => 'streettype', # street table, list builded from a system table
711 after => 'streetnumber',
718 field => 'B_streetnumber', # street number (hidden if streettable table is empty)
724 field => 'B_streettype', # street table, list builded from a system table
727 after => 'B_streetnumber',
736 field => 'address2', # complement address
748 field => 'contactfirstname', # contact's firstname
751 after => 'contactname',
754 field => 'contacttitle', # contact's title
757 after => 'contactfirstname',
760 field => 'branchcode',
761 type => 'varchar(10)',
767 field => 'categorycode',
768 type => 'varchar(10)',
778 type => 'varchar(10)',
786 type => 'varchar(25)',
794 type => 'varchar(4)',
802 type => 'varchar(30)',
808 field => 'publicationyear',
815 field => 'collectiontitle',
816 type => 'mediumtext',
822 field => 'collectionissn',
823 type => 'mediumtext',
829 field => 'collectionvolume',
830 type => 'mediumtext',
836 field => 'editionstatement',
843 field => 'editionresponsibility',
851 deletedbiblioitems => [
854 type => 'varchar(10)',
861 type => 'varchar(30)',
870 type => 'varchar(15)',
877 field => 'branchprinter',
878 type => 'varchar(100)',
885 field => 'branchcode',
886 type => 'varchar(10)',
894 field => 'frombranch',
895 type => 'VARCHAR(10)',
903 type => 'VARCHAR(10)',
912 field => 'category_type',
920 field => 'categorycode',
921 type => 'varchar(10)',
929 deletedborrowers => [
930 { field => 'firstname',
934 { field => 'initials',
938 { field => 'B_email',
941 after => 'B_zipcode',
944 field => 'streetnumber', # street number (hidden if streettable table is empty)
950 field => 'streettype', # street table, list builded from a system table
953 after => 'streetnumber',
960 field => 'B_streetnumber', # street number (hidden if streettable table is empty)
966 field => 'B_streettype', # street table, list builded from a system table
969 after => 'B_streetnumber',
978 field => 'address2', # complement address
990 field => 'contactfirstname', # contact's firstname
993 after => 'contactname',
996 field => 'contacttitle', # contact's title
999 after => 'contactfirstname',
1005 field => 'borrowernumber',
1007 null => 'NULL', # can be null when a borrower is deleted and the foreign key rule executed
1013 field => 'itemnumber',
1015 null => 'NULL', # can be null when a borrower is deleted and the foreign key rule executed
1021 field => 'branchcode',
1022 type => 'varchar(10)',
1029 field => 'issuedate',
1033 default => '0000-00-00',
1039 field => 'categorycode',
1040 type => 'varchar(10)',
1046 field => 'branchcode',
1047 type => 'varchar(10)',
1053 field => 'itemtype',
1054 type => 'varchar(10)',
1067 default => '0000-00-00',
1071 field => 'cutterextra',
1072 type => 'varchar(45)',
1079 field => 'homebranch',
1080 type => 'varchar(10)',
1087 field => 'holdingbranch',
1088 type => 'varchar(10)',
1096 type => 'varchar(10)',
1105 field => 'itemtype',
1106 type => 'varchar(10)',
1129 marc_subfield_structure => [
1131 field => 'defaultvalue',
1140 field => 'expirationdate',
1157 field => 'waitingdate',
1177 field => 'dateadded',
1178 type => 'timestamp',
1185 type => 'varchar(10)',
1189 field => 'itemtype',
1190 type => 'varchar(10)',
1194 systempreferences => [
1204 field => 'explanation',
1226 # { indexname => 'index detail'
1230 { indexname => 'PRIMARY',
1236 { indexname => 'booksellerid',
1237 content => 'booksellerid',
1241 { indexname => 'basketno',
1242 content => 'basketno',
1245 aqorderbreakdown => [
1246 { indexname => 'ordernumber',
1247 content => 'ordernumber',
1249 { indexname => 'bookfundid',
1250 content => 'bookfundid',
1254 { indexname => 'isbn',
1257 { indexname => 'publishercode',
1258 content => 'publishercode',
1263 indexname => 'branchcode',
1264 content => 'branchcode',
1268 branchrelations => [
1270 indexname => 'PRIMARY',
1271 content => 'categorycode',
1275 branchrelations => [
1276 { indexname => 'PRIMARY',
1277 content => 'branchcode,categorycode',
1280 { indexname => 'branchcode',
1281 content => 'branchcode',
1283 { indexname => 'categorycode',
1284 content => 'categorycode',
1288 { indexname => 'PRIMARY',
1289 content => 'currency',
1295 indexname => 'categorycode',
1296 content => 'categorycode',
1300 { indexname => 'homebranch',
1301 content => 'homebranch',
1303 { indexname => 'holdingbranch',
1304 content => 'holdingbranch',
1309 indexname => 'itemtype',
1310 content => 'itemtype',
1314 { indexname => 'shelfnumber',
1315 content => 'shelfnumber',
1317 { indexname => 'itemnumber',
1318 content => 'itemnumber',
1322 { indexname => 'PRIMARY',
1329 my %foreign_keys = (
1331 # { key => 'the key in table' (must be indexed)
1332 # foreigntable => 'the foreigntable name', # (the parent)
1333 # foreignkey => 'the foreign key column(s)' # (in the parent)
1334 # onUpdate => 'CASCADE|SET NULL|NO ACTION| RESTRICT',
1335 # onDelete => 'CASCADE|SET NULL|NO ACTION| RESTRICT',
1338 branchrelations => [
1339 { key => 'branchcode',
1340 foreigntable => 'branches',
1341 foreignkey => 'branchcode',
1342 onUpdate => 'CASCADE',
1343 onDelete => 'CASCADE',
1345 { key => 'categorycode',
1346 foreigntable => 'branchcategories',
1347 foreignkey => 'categorycode',
1348 onUpdate => 'CASCADE',
1349 onDelete => 'CASCADE',
1353 { key => 'shelfnumber',
1354 foreigntable => 'virtualshelf',
1355 foreignkey => 'shelfnumber',
1356 onUpdate => 'CASCADE',
1357 onDelete => 'CASCADE',
1359 { key => 'itemnumber',
1360 foreigntable => 'items',
1361 foreignkey => 'itemnumber',
1362 onUpdate => 'CASCADE',
1363 onDelete => 'CASCADE',
1366 # onDelete is RESTRICT on reference tables (branches, itemtype) as we don't want items to be
1367 # easily deleted, but branches/itemtype not too easy to empty...
1369 { key => 'biblionumber',
1370 foreigntable => 'biblio',
1371 foreignkey => 'biblionumber',
1372 onUpdate => 'CASCADE',
1373 onDelete => 'CASCADE',
1375 { key => 'itemtype',
1376 foreigntable => 'itemtypes',
1377 foreignkey => 'itemtype',
1378 onUpdate => 'CASCADE',
1379 onDelete => 'RESTRICT',
1383 { key => 'biblioitemnumber',
1384 foreigntable => 'biblioitems',
1385 foreignkey => 'biblioitemnumber',
1386 onUpdate => 'CASCADE',
1387 onDelete => 'CASCADE',
1389 { key => 'homebranch',
1390 foreigntable => 'branches',
1391 foreignkey => 'branchcode',
1392 onUpdate => 'CASCADE',
1393 onDelete => 'RESTRICT',
1395 { key => 'holdingbranch',
1396 foreigntable => 'branches',
1397 foreignkey => 'branchcode',
1398 onUpdate => 'CASCADE',
1399 onDelete => 'RESTRICT',
1403 { key => 'booksellerid',
1404 foreigntable => 'aqbooksellers',
1406 onUpdate => 'CASCADE',
1407 onDelete => 'RESTRICT',
1411 { key => 'basketno',
1412 foreigntable => 'aqbasket',
1413 foreignkey => 'basketno',
1414 onUpdate => 'CASCADE',
1415 onDelete => 'CASCADE',
1417 { key => 'biblionumber',
1418 foreigntable => 'biblio',
1419 foreignkey => 'biblionumber',
1420 onUpdate => 'SET NULL',
1421 onDelete => 'SET NULL',
1425 { key => 'listprice',
1426 foreigntable => 'currency',
1427 foreignkey => 'currency',
1428 onUpdate => 'CASCADE',
1429 onDelete => 'CASCADE',
1431 { key => 'invoiceprice',
1432 foreigntable => 'currency',
1433 foreignkey => 'currency',
1434 onUpdate => 'CASCADE',
1435 onDelete => 'CASCADE',
1438 aqorderbreakdown => [
1439 { key => 'ordernumber',
1440 foreigntable => 'aqorders',
1441 foreignkey => 'ordernumber',
1442 onUpdate => 'CASCADE',
1443 onDelete => 'CASCADE',
1445 { key => 'bookfundid',
1446 foreigntable => 'aqbookfund',
1447 foreignkey => 'bookfundid',
1448 onUpdate => 'CASCADE',
1449 onDelete => 'CASCADE',
1452 branchtransfers => [
1453 { key => 'frombranch',
1454 foreigntable => 'branches',
1455 foreignkey => 'branchcode',
1456 onUpdate => 'CASCADE',
1457 onDelete => 'CASCADE',
1459 { key => 'tobranch',
1460 foreigntable => 'branches',
1461 foreignkey => 'branchcode',
1462 onUpdate => 'CASCADE',
1463 onDelete => 'CASCADE',
1465 { key => 'itemnumber',
1466 foreigntable => 'items',
1467 foreignkey => 'itemnumber',
1468 onUpdate => 'CASCADE',
1469 onDelete => 'CASCADE',
1473 { key => 'categorycode',
1474 foreigntable => 'categories',
1475 foreignkey => 'categorycode',
1476 onUpdate => 'CASCADE',
1477 onDelete => 'CASCADE',
1480 issues => [ # constraint is SET NULL : when a borrower or an item is deleted, we keep the issuing record
1482 { key => 'borrowernumber',
1483 foreigntable => 'borrowers',
1484 foreignkey => 'borrowernumber',
1485 onUpdate => 'SET NULL',
1486 onDelete => 'SET NULL',
1488 { key => 'itemnumber',
1489 foreigntable => 'items',
1490 foreignkey => 'itemnumber',
1491 onUpdate => 'SET NULL',
1492 onDelete => 'SET NULL',
1496 { key => 'borrowernumber',
1497 foreigntable => 'borrowers',
1498 foreignkey => 'borrowernumber',
1499 onUpdate => 'CASCADE',
1500 onDelete => 'CASCADE',
1502 { key => 'biblionumber',
1503 foreigntable => 'biblio',
1504 foreignkey => 'biblionumber',
1505 onUpdate => 'CASCADE',
1506 onDelete => 'CASCADE',
1508 { key => 'itemnumber',
1509 foreigntable => 'items',
1510 foreignkey => 'itemnumber',
1511 onUpdate => 'CASCADE',
1512 onDelete => 'CASCADE',
1514 { key => 'branchcode',
1515 foreigntable => 'branches',
1516 foreignkey => 'branchcode',
1517 onUpdate => 'CASCADE',
1518 onDelete => 'CASCADE',
1521 borrowers => [ # foreign keys are RESTRICT as we don't want to delete borrowers when a branch is deleted
1522 # but prevent deleting a branch as soon as it has 1 borrower !
1523 { key => 'categorycode',
1524 foreigntable => 'categories',
1525 foreignkey => 'categorycode',
1526 onUpdate => 'RESTRICT',
1527 onDelete => 'RESTRICT',
1529 { key => 'branchcode',
1530 foreigntable => 'branches',
1531 foreignkey => 'branchcode',
1532 onUpdate => 'RESTRICT',
1533 onDelete => 'RESTRICT',
1536 deletedborrowers => [ # foreign keys are RESTRICT as we don't want to delete borrowers when a branch is deleted
1537 # but prevent deleting a branch as soon as it has 1 borrower !
1538 { key => 'categorycode',
1539 foreigntable => 'categories',
1540 foreignkey => 'categorycode',
1541 onUpdate => 'RESTRICT',
1542 onDelete => 'RESTRICT',
1544 { key => 'branchcode',
1545 foreigntable => 'branches',
1546 foreignkey => 'branchcode',
1547 onUpdate => 'RESTRICT',
1548 onDelete => 'RESTRICT',
1552 { key => 'borrowernumber',
1553 foreigntable => 'borrowers',
1554 foreignkey => 'borrowernumber',
1555 onUpdate => 'CASCADE',
1556 onDelete => 'CASCADE',
1558 { key => 'itemnumber',
1559 foreigntable => 'items',
1560 foreignkey => 'itemnumber',
1561 onUpdate => 'SET NULL',
1562 onDelete => 'SET NULL',
1565 auth_tag_structure => [
1566 { key => 'authtypecode',
1567 foreigntable => 'auth_types',
1568 foreignkey => 'authtypecode',
1569 onUpdate => 'CASCADE',
1570 onDelete => 'CASCADE',
1573 # FIXME : don't constraint auth_*_table and auth_word, as they may be replaced by zebra
1578 my %column_change = (
1582 from => 'emailaddress',
1587 from => 'streetaddress',
1589 after => 'initials',
1592 from => 'faxnumber',
1597 from => 'textmessaging',
1603 to => 'contactnote',
1604 after => 'opacnote',
1607 from => 'physstreet',
1612 from => 'streetcity',
1614 after => 'B_address',
1627 from => 'homezipcode',
1634 after => 'B_zipcode',
1639 after => 'dateenrolled',
1642 from => 'guarantor',
1643 to => 'guarantorid',
1644 after => 'contactname',
1647 from => 'altrelationship',
1648 to => 'relationship',
1649 after => 'borrowernotes',
1653 deletedborrowers => [
1655 from => 'emailaddress',
1660 from => 'streetaddress',
1662 after => 'initials',
1665 from => 'faxnumber',
1670 from => 'textmessaging',
1676 to => 'contactnote',
1677 after => 'opacnote',
1680 from => 'physstreet',
1685 from => 'streetcity',
1687 after => 'B_address',
1700 from => 'homezipcode',
1707 after => 'B_zipcode',
1712 after => 'dateenrolled',
1715 from => 'guarantor',
1716 to => 'guarantorid',
1717 after => 'contactname',
1720 from => 'altrelationship',
1721 to => 'relationship',
1722 after => 'borrowernotes',
1728 # MOVE all tables TO UTF-8 and innoDB
1729 $sth = $dbh->prepare("show table status");
1731 while ( my $table = $sth->fetchrow_hashref ) {
1732 next if $table->{Name} eq 'marc_word';
1733 next if $table->{Name} eq 'marc_subfield_table';
1734 next if $table->{Name} eq 'auth_word';
1735 next if $table->{Name} eq 'auth_subfield_table';
1736 if ($table->{Engine} ne 'InnoDB') {
1737 print "moving $table->{Name} to InnoDB\n";
1738 $dbh->do("ALTER TABLE $table->{Name} TYPE = innodb");
1740 unless ($table->{Collation} =~ /^utf8/) {
1741 print "moving $table->{Name} to utf8\n";
1742 $dbh->do("ALTER TABLE $table->{Name} CONVERT TO CHARACTER SET utf8");
1743 $dbh->do("ALTER TABLE $table->{Name} DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci");
1744 # 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 !
1750 foreach my $table (keys %column_change) {
1751 $sth = $dbh->prepare("show columns from $table");
1754 while ( ( $column, $type, $null, $key, $default, $extra ) = $sth->fetchrow )
1756 $types{$column}->{type} ="$type";
1757 $types{$column}->{null} = "$null";
1758 $types{$column}->{key} = "$key";
1759 $types{$column}->{default} = "$default";
1760 $types{$column}->{extra} = "$extra";
1762 my $tablerows = $column_change{$table};
1763 foreach my $row ( @$tablerows ) {
1764 if ($types{$row->{from}}->{type}) {
1765 print "altering $table $row->{from} to $row->{to}\n";
1766 # ALTER TABLE `borrowers` CHANGE `faxnumber` `fax` TEXT CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL
1767 # alter table `borrowers` change `faxnumber` `fax` type text null after phone
1769 "alter table `$table` change `$row->{from}` `$row->{to}` $types{$row->{from}}->{type} ".
1770 ($types{$row->{from}}->{null} eq 'YES'?" NULL":" NOT NULL").
1771 ($types{$row->{from}}->{default}?" default ".$types{$row->{from}}->{default}:"").
1772 "$types{$row->{from}}->{extra} after $row->{after} ";
1779 # Enter here the field you want to delete from DB.
1780 # FIXME :: there is a %uselessfield before which seems doing the same things.
1781 my %fieldtodelete = (
1782 # tablename => [fieldname1,fieldname2,...]
1786 print "removing some unused fields...\n";
1787 foreach my $table ( keys %fieldtodelete ) {
1788 foreach my $field ( @{$fieldtodelete{$table}} ){
1789 print "removing ".$field." from ".$table;
1790 my $sth = $dbh->prepare("ALTER TABLE $table DROP $field");
1793 print "Error : $sth->errstr \n";
1798 # Enter here the line you want to remove from DB.
1799 my %linetodelete = (
1800 # table name => where clause.
1801 userflags => "bit = 8", # delete the 'reserveforself' flags
1805 #-------------------
1810 # Get version of MySQL database engine.
1811 my $mysqlversion = `mysqld --version`;
1812 $mysqlversion =~ /Ver (\S*) /;
1814 if ( $mysqlversion ge '3.23' ) {
1815 print "Could convert to MyISAM database tables...\n" unless $silent;
1818 #---------------------------------
1821 # Collect all tables into a list
1822 $sth = $dbh->prepare("show tables");
1824 while ( my ($table) = $sth->fetchrow ) {
1825 $existingtables{$table} = 1;
1829 # Now add any missing tables
1830 foreach $table ( keys %requiretables ) {
1831 unless ( $existingtables{$table} ) {
1832 print "Adding $table table...\n" unless $silent;
1833 my $sth = $dbh->prepare("create table $table $requiretables{$table}");
1836 print "Error : $sth->errstr \n";
1842 #---------------------------------
1845 foreach $table ( keys %requirefields ) {
1846 print "Check table $table\n" if $debug and not $silent;
1847 $sth = $dbh->prepare("show columns from $table");
1850 while ( ( $column, $type, $null, $key, $default, $extra ) = $sth->fetchrow )
1852 $types{$column} = $type;
1854 foreach $column ( keys %{ $requirefields{$table} } ) {
1855 print " Check column $column [$types{$column}]\n" if $debug and not $silent;
1856 if ( !$types{$column} ) {
1858 # column doesn't exist
1859 print "Adding $column field to $table table...\n" unless $silent;
1860 $query = "alter table $table
1861 add column $column " . $requirefields{$table}->{$column};
1862 print "Execute: $query\n" if $debug;
1863 my $sti = $dbh->prepare($query);
1866 print "**Error : $sti->errstr \n";
1873 foreach $table ( keys %fielddefinitions ) {
1874 print "Check table $table\n" if $debug;
1875 $sth = $dbh->prepare("show columns from $table");
1878 while ( ( $column, $type, $null, $key, $default, $extra ) = $sth->fetchrow )
1880 $definitions->{$column}->{type} = $type;
1881 $definitions->{$column}->{null} = $null;
1882 $definitions->{$column}->{null} = 'NULL' if $null eq 'YES';
1883 $definitions->{$column}->{key} = $key;
1884 $definitions->{$column}->{default} = $default;
1885 $definitions->{$column}->{extra} = $extra;
1887 my $fieldrow = $fielddefinitions{$table};
1888 foreach my $row (@$fieldrow) {
1889 my $field = $row->{field};
1890 my $type = $row->{type};
1891 my $null = $row->{null};
1892 # $null = 'YES' if $row->{null} eq 'NULL';
1893 my $key = $row->{key};
1894 my $default = $row->{default};
1895 # $default="''" unless $default;
1896 my $extra = $row->{extra};
1897 my $def = $definitions->{$field};
1898 my $after = ($row->{after}?" after ".$row->{after}:"");
1900 unless ( $type eq $def->{type}
1901 && $null eq $def->{null}
1902 && $key eq $def->{key}
1903 && $extra eq $def->{extra} )
1905 if ( $null eq '' ) {
1908 if ( $key eq 'PRI' ) {
1909 $key = 'PRIMARY KEY';
1911 unless ( $extra eq 'auto_increment' ) {
1915 # if it's a new column use "add", if it's an old one, use "change".
1917 if ($definitions->{$field}->{type}) {
1918 $action="change $field"
1922 # if it's a primary key, drop the previous pk, before altering the table
1923 print " alter or create $field in $table\n" unless $silent;
1925 if ($key ne 'PRIMARY KEY') {
1926 # warn "alter table $table $action $field $type $null $key $extra default $default $after";
1927 $query = "alter table $table $action $field $type $null $key $extra ".($default?"default ".$dbh->quote($default):"")." $after";
1929 # warn "alter table $table drop primary key, $action $field $type $null $key $extra default $default $after";
1930 # something strange : for indexes UNIQUE, they are reported as primary key here.
1931 # but if you try to run with drop primary key, it fails.
1932 # thus, we run the query twice, one will fail, one will succeed.
1934 $query="alter table $table drop primary key, $action $field $type $null $key $extra ".($default?"default ".$dbh->quote($default):"")." $after";
1935 $query="alter table $table $action $field $type $null $key $extra ".($default?"default ".$dbh->quote($default):"")." $after";
1942 print "removing some unused data...\n";
1943 foreach my $table ( keys %linetodelete ) {
1944 foreach my $where ( @{linetodelete{$table}} ){
1945 print "DELETE FROM ".$table." where ".$where;
1947 my $sth = $dbh->prepare("DELETE FROM $table where $where");
1950 print "Error : $sth->errstr \n";
1955 # Populate tables with required data
1957 # synch table and deletedtable.
1958 foreach my $table (('borrowers','items','biblio','biblioitems')) {
1959 my %deletedborrowers;
1960 print "synch'ing $table and deleted$table\n";
1961 $sth = $dbh->prepare("show columns from deleted$table");
1963 while ( my ( $column, $type, $null, $key, $default, $extra ) = $sth->fetchrow ) {
1964 $deletedborrowers{$column}=1;
1966 $sth = $dbh->prepare("show columns from $table");
1969 while ( my ( $column, $type, $null, $key, $default, $extra ) = $sth->fetchrow ) {
1970 unless ($deletedborrowers{$column}) {
1971 my $newcol="alter table deleted$table add $column $type";
1972 if ($null eq 'YES') {
1973 $newcol .= " NULL ";
1975 $newcol .= " NOT NULL ";
1977 $newcol .= "default ".$dbh->quote($default) if $default;
1978 $newcol .= " after $previous" if $previous;
1980 print "creating column $column\n";
1986 # update publisheddate
1988 $sth = $dbh->prepare("select count(*) from serial where publisheddate is NULL");
1990 my ($emptypublished) = $sth->fetchrow;
1991 if ($emptypublished) {
1992 print "Updating publisheddate\n";
1993 $dbh->do("update serial set publisheddate=planneddate where publisheddate is NULL");
1995 foreach my $table ( keys %tabledata ) {
1996 print "Checking for data required in table $table...\n" unless $silent;
1997 my $tablerows = $tabledata{$table};
1998 foreach my $row (@$tablerows) {
1999 my $uniquefieldrequired = $row->{uniquefieldrequired};
2000 my $uniquevalue = $row->{$uniquefieldrequired};
2001 my $forceupdate = $row->{forceupdate};
2004 "select $uniquefieldrequired from $table where $uniquefieldrequired=?"
2006 $sth->execute($uniquevalue);
2008 foreach my $field (keys %$forceupdate) {
2009 if ($forceupdate->{$field}) {
2010 my $sth=$dbh->prepare("update systempreferences set $field=? where $uniquefieldrequired=?");
2011 $sth->execute($row->{$field}, $uniquevalue);
2015 print "Adding row to $table: " unless $silent;
2019 foreach my $field ( keys %$row ) {
2020 next if $field eq 'uniquefieldrequired';
2021 next if $field eq 'forceupdate';
2022 my $value = $row->{$field};
2023 push @values, $value;
2024 print " $field => $value" unless $silent;
2025 $fieldlist .= "$field,";
2026 $placeholders .= "?,";
2028 print "\n" unless $silent;
2029 $fieldlist =~ s/,$//;
2030 $placeholders =~ s/,$//;
2031 print "insert into $table ($fieldlist) values ($placeholders)";
2034 "insert into $table ($fieldlist) values ($placeholders)");
2035 $sth->execute(@values);
2041 # check indexes and create them when needed
2043 print "Checking for index required...\n" unless $silent;
2044 foreach my $table ( keys %indexes ) {
2046 # read all indexes from $table
2048 $sth = $dbh->prepare("show index from $table");
2050 my %existingindexes;
2051 while ( my ( $table, $non_unique, $key_name, $Seq_in_index, $Column_name, $Collation, $cardinality, $sub_part, $Packed, $comment ) = $sth->fetchrow ) {
2052 $existingindexes{$key_name} = 1;
2054 # read indexes to check
2055 my $tablerows = $indexes{$table};
2056 foreach my $row (@$tablerows) {
2057 my $key_name=$row->{indexname};
2058 if ($existingindexes{$key_name} eq 1) {
2059 # print "$key_name existing";
2061 print "\tCreating index $key_name in $table\n";
2063 if ($row->{indexname} eq 'PRIMARY') {
2064 $sql = "alter table $table ADD PRIMARY KEY ($row->{content})";
2066 $sql = "alter table $table ADD INDEX $key_name ($row->{content}) $row->{type}";
2069 print "Error $sql : $dbh->err \n" if $dbh->err;
2075 # check foreign keys and create them when needed
2077 print "Checking for foreign keys required...\n" unless $silent;
2078 foreach my $table ( keys %foreign_keys ) {
2080 # read all indexes from $table
2082 $sth = $dbh->prepare("show table status like '$table'");
2084 my $stat = $sth->fetchrow_hashref;
2085 # read indexes to check
2086 my $tablerows = $foreign_keys{$table};
2087 foreach my $row (@$tablerows) {
2088 my $foreign_table=$row->{foreigntable};
2089 if ($stat->{'Comment'} =~/$foreign_table/) {
2090 # print "$foreign_table existing\n";
2092 print "\tCreating foreign key $foreign_table in $table\n";
2093 # first, drop any orphan value in child table
2094 if ($row->{onDelete} ne "RESTRICT") {
2095 my $sql = "delete from $table where $row->{key} not in (select $row->{foreignkey} from $row->{foreigntable})";
2097 print "SQL ERROR: $sql : $dbh->err \n" if $dbh->err;
2099 my $sql="alter table $table ADD FOREIGN KEY $row->{key} ($row->{key}) REFERENCES $row->{foreigntable} ($row->{foreignkey})";
2100 $sql .= " on update ".$row->{onUpdate} if $row->{onUpdate};
2101 $sql .= " on delete ".$row->{onDelete} if $row->{onDelete};
2104 print "====================
2105 An error occured during :
2107 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).
2108 You can find those values with select
2109 \t$table.* from $table where $row->{key} not in (select $row->{foreignkey} from $row->{foreigntable})
2110 ====================\n
2116 # now drop useless tables
2117 foreach $table ( @TableToDelete ) {
2118 if ( $existingtables{$table} ) {
2119 print "Dropping unused table $table\n" if $debug and not $silent;
2120 $dbh->do("drop table $table");
2122 print "Error : $dbh->errstr \n";
2131 # create frameworkcode row in biblio table & fill it with marc_biblio.frameworkcode.
2134 # 1st, get how many biblio we will have to do...
2135 $sth = $dbh->prepare('select count(*) from marc_biblio');
2137 my ($totaltodo) = $sth->fetchrow;
2139 $sth = $dbh->prepare("show columns from biblio");
2142 my $bibliofwexist=0;
2143 while ( ( $column, $type, $null, $key, $default, $extra ) = $sth->fetchrow ){
2144 $bibliofwexist=1 if $column eq 'frameworkcode';
2146 unless ($bibliofwexist) {
2147 print "moving biblioframework to biblio table\n";
2148 $dbh->do('ALTER TABLE `biblio` ADD `frameworkcode` VARCHAR( 4 ) NOT NULL AFTER `biblionumber`');
2149 $sth = $dbh->prepare('select biblionumber,frameworkcode from marc_biblio');
2151 my $sth_update = $dbh->prepare('update biblio set frameworkcode=? where biblionumber=?');
2153 while (my ($biblionumber,$frameworkcode) = $sth->fetchrow) {
2154 $sth_update->execute($frameworkcode,$biblionumber);
2156 print "\r$totaldone / $totaltodo" unless ($totaldone % 100);
2161 # at last, remove useless fields
2162 foreach $table ( keys %uselessfields ) {
2163 my @fields = split /,/,$uselessfields{$table};
2166 foreach my $fieldtodrop (@fields) {
2167 $fieldtodrop =~ s/\t//g;
2168 $fieldtodrop =~ s/\n//g;
2170 $sth = $dbh->prepare("show columns from $table");
2172 while ( my ( $column, $type, $null, $key, $default, $extra ) = $sth->fetchrow )
2174 $exists =1 if ($column eq $fieldtodrop);
2177 print "deleting $fieldtodrop field in $table...\n" unless $silent;
2178 my $sth = $dbh->prepare("alter table $table drop $fieldtodrop");
2185 # Changing aqbookfund's primary key
2187 $sth=$dbh->prepare("ALTER TABLE `aqbookfund` DROP PRIMARY KEY , ADD PRIMARY KEY ( `bookfundid` , `branchcode` ) ;");
2191 print "upgrade to Koha 3.0 done\n";
2192 SetVersion ($DBversion);
2195 =item TransformToNum
2197 Transform the Koha version from a 4 parts string
2198 to a number, with just 1 .
2202 sub TransformToNum {
2203 my $version = shift;
2204 # remove the 3 last . to have a Perl number
2205 $version =~ s/(.*\..*)\.(.*)\.(.*)/$1$2$3/;
2210 set the DBversion in the systempreferences
2214 my $kohaversion = TransformToNum(shift);
2215 if (C4::Context->preference('Version')) {
2216 my $finish=$dbh->prepare("UPDATE systempreferences SET value=? WHERE variable='Version'");
2217 $finish->execute($kohaversion);
2219 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')");
2220 $finish->execute($kohaversion);
2226 # Revision 1.172 2007/07/19 10:21:22 hdl