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',
1472 issues => [ # constraint is SET NULL : when a borrower or an item is deleted, we keep the issuing record
1474 { key => 'borrowernumber',
1475 foreigntable => 'borrowers',
1476 foreignkey => 'borrowernumber',
1477 onUpdate => 'SET NULL',
1478 onDelete => 'SET NULL',
1480 { key => 'itemnumber',
1481 foreigntable => 'items',
1482 foreignkey => 'itemnumber',
1483 onUpdate => 'SET NULL',
1484 onDelete => 'SET NULL',
1488 { key => 'borrowernumber',
1489 foreigntable => 'borrowers',
1490 foreignkey => 'borrowernumber',
1491 onUpdate => 'CASCADE',
1492 onDelete => 'CASCADE',
1494 { key => 'biblionumber',
1495 foreigntable => 'biblio',
1496 foreignkey => 'biblionumber',
1497 onUpdate => 'CASCADE',
1498 onDelete => 'CASCADE',
1500 { key => 'itemnumber',
1501 foreigntable => 'items',
1502 foreignkey => 'itemnumber',
1503 onUpdate => 'CASCADE',
1504 onDelete => 'CASCADE',
1506 { key => 'branchcode',
1507 foreigntable => 'branches',
1508 foreignkey => 'branchcode',
1509 onUpdate => 'CASCADE',
1510 onDelete => 'CASCADE',
1513 borrowers => [ # foreign keys are RESTRICT as we don't want to delete borrowers when a branch is deleted
1514 # but prevent deleting a branch as soon as it has 1 borrower !
1515 { key => 'categorycode',
1516 foreigntable => 'categories',
1517 foreignkey => 'categorycode',
1518 onUpdate => 'RESTRICT',
1519 onDelete => 'RESTRICT',
1521 { key => 'branchcode',
1522 foreigntable => 'branches',
1523 foreignkey => 'branchcode',
1524 onUpdate => 'RESTRICT',
1525 onDelete => 'RESTRICT',
1528 deletedborrowers => [ # foreign keys are RESTRICT as we don't want to delete borrowers when a branch is deleted
1529 # but prevent deleting a branch as soon as it has 1 borrower !
1530 { key => 'categorycode',
1531 foreigntable => 'categories',
1532 foreignkey => 'categorycode',
1533 onUpdate => 'RESTRICT',
1534 onDelete => 'RESTRICT',
1536 { key => 'branchcode',
1537 foreigntable => 'branches',
1538 foreignkey => 'branchcode',
1539 onUpdate => 'RESTRICT',
1540 onDelete => 'RESTRICT',
1544 { key => 'borrowernumber',
1545 foreigntable => 'borrowers',
1546 foreignkey => 'borrowernumber',
1547 onUpdate => 'CASCADE',
1548 onDelete => 'CASCADE',
1550 { key => 'itemnumber',
1551 foreigntable => 'items',
1552 foreignkey => 'itemnumber',
1553 onUpdate => 'SET NULL',
1554 onDelete => 'SET NULL',
1557 auth_tag_structure => [
1558 { key => 'authtypecode',
1559 foreigntable => 'auth_types',
1560 foreignkey => 'authtypecode',
1561 onUpdate => 'CASCADE',
1562 onDelete => 'CASCADE',
1565 # FIXME : don't constraint auth_*_table and auth_word, as they may be replaced by zebra
1570 my %column_change = (
1574 from => 'emailaddress',
1579 from => 'streetaddress',
1581 after => 'initials',
1584 from => 'faxnumber',
1589 from => 'textmessaging',
1595 to => 'contactnote',
1596 after => 'opacnote',
1599 from => 'physstreet',
1604 from => 'streetcity',
1606 after => 'B_address',
1619 from => 'homezipcode',
1626 after => 'B_zipcode',
1631 after => 'dateenrolled',
1634 from => 'guarantor',
1635 to => 'guarantorid',
1636 after => 'contactname',
1639 from => 'altrelationship',
1640 to => 'relationship',
1641 after => 'borrowernotes',
1645 deletedborrowers => [
1647 from => 'emailaddress',
1652 from => 'streetaddress',
1654 after => 'initials',
1657 from => 'faxnumber',
1662 from => 'textmessaging',
1668 to => 'contactnote',
1669 after => 'opacnote',
1672 from => 'physstreet',
1677 from => 'streetcity',
1679 after => 'B_address',
1692 from => 'homezipcode',
1699 after => 'B_zipcode',
1704 after => 'dateenrolled',
1707 from => 'guarantor',
1708 to => 'guarantorid',
1709 after => 'contactname',
1712 from => 'altrelationship',
1713 to => 'relationship',
1714 after => 'borrowernotes',
1720 # MOVE all tables TO UTF-8 and innoDB
1721 $sth = $dbh->prepare("show table status");
1723 while ( my $table = $sth->fetchrow_hashref ) {
1724 next if $table->{Name} eq 'marc_word';
1725 next if $table->{Name} eq 'marc_subfield_table';
1726 next if $table->{Name} eq 'auth_word';
1727 next if $table->{Name} eq 'auth_subfield_table';
1728 if ($table->{Engine} ne 'InnoDB') {
1729 print "moving $table->{Name} to InnoDB\n";
1730 $dbh->do("ALTER TABLE $table->{Name} TYPE = innodb");
1732 unless ($table->{Collation} =~ /^utf8/) {
1733 print "moving $table->{Name} to utf8\n";
1734 $dbh->do("ALTER TABLE $table->{Name} CONVERT TO CHARACTER SET utf8");
1735 $dbh->do("ALTER TABLE $table->{Name} DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci");
1736 # 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 !
1742 foreach my $table (keys %column_change) {
1743 $sth = $dbh->prepare("show columns from $table");
1746 while ( ( $column, $type, $null, $key, $default, $extra ) = $sth->fetchrow )
1748 $types{$column}->{type} ="$type";
1749 $types{$column}->{null} = "$null";
1750 $types{$column}->{key} = "$key";
1751 $types{$column}->{default} = "$default";
1752 $types{$column}->{extra} = "$extra";
1754 my $tablerows = $column_change{$table};
1755 foreach my $row ( @$tablerows ) {
1756 if ($types{$row->{from}}->{type}) {
1757 print "altering $table $row->{from} to $row->{to}\n";
1758 # ALTER TABLE `borrowers` CHANGE `faxnumber` `fax` TEXT CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL
1759 # alter table `borrowers` change `faxnumber` `fax` type text null after phone
1761 "alter table `$table` change `$row->{from}` `$row->{to}` $types{$row->{from}}->{type} ".
1762 ($types{$row->{from}}->{null} eq 'YES'?" NULL":" NOT NULL").
1763 ($types{$row->{from}}->{default}?" default ".$types{$row->{from}}->{default}:"").
1764 "$types{$row->{from}}->{extra} after $row->{after} ";
1771 # Enter here the field you want to delete from DB.
1772 # FIXME :: there is a %uselessfield before which seems doing the same things.
1773 my %fieldtodelete = (
1774 # tablename => [fieldname1,fieldname2,...]
1778 print "removing some unused fields...\n";
1779 foreach my $table ( keys %fieldtodelete ) {
1780 foreach my $field ( @{$fieldtodelete{$table}} ){
1781 print "removing ".$field." from ".$table;
1782 my $sth = $dbh->prepare("ALTER TABLE $table DROP $field");
1785 print "Error : $sth->errstr \n";
1790 # Enter here the line you want to remove from DB.
1791 my %linetodelete = (
1792 # table name => where clause.
1793 userflags => "bit = 8", # delete the 'reserveforself' flags
1797 #-------------------
1802 # Get version of MySQL database engine.
1803 my $mysqlversion = `mysqld --version`;
1804 $mysqlversion =~ /Ver (\S*) /;
1806 if ( $mysqlversion ge '3.23' ) {
1807 print "Could convert to MyISAM database tables...\n" unless $silent;
1810 #---------------------------------
1813 # Collect all tables into a list
1814 $sth = $dbh->prepare("show tables");
1816 while ( my ($table) = $sth->fetchrow ) {
1817 $existingtables{$table} = 1;
1821 # Now add any missing tables
1822 foreach $table ( keys %requiretables ) {
1823 unless ( $existingtables{$table} ) {
1824 print "Adding $table table...\n" unless $silent;
1825 my $sth = $dbh->prepare("create table $table $requiretables{$table}");
1828 print "Error : $sth->errstr \n";
1834 #---------------------------------
1837 foreach $table ( keys %requirefields ) {
1838 print "Check table $table\n" if $debug and not $silent;
1839 $sth = $dbh->prepare("show columns from $table");
1842 while ( ( $column, $type, $null, $key, $default, $extra ) = $sth->fetchrow )
1844 $types{$column} = $type;
1846 foreach $column ( keys %{ $requirefields{$table} } ) {
1847 print " Check column $column [$types{$column}]\n" if $debug and not $silent;
1848 if ( !$types{$column} ) {
1850 # column doesn't exist
1851 print "Adding $column field to $table table...\n" unless $silent;
1852 $query = "alter table $table
1853 add column $column " . $requirefields{$table}->{$column};
1854 print "Execute: $query\n" if $debug;
1855 my $sti = $dbh->prepare($query);
1858 print "**Error : $sti->errstr \n";
1865 foreach $table ( keys %fielddefinitions ) {
1866 print "Check table $table\n" if $debug;
1867 $sth = $dbh->prepare("show columns from $table");
1870 while ( ( $column, $type, $null, $key, $default, $extra ) = $sth->fetchrow )
1872 $definitions->{$column}->{type} = $type;
1873 $definitions->{$column}->{null} = $null;
1874 $definitions->{$column}->{null} = 'NULL' if $null eq 'YES';
1875 $definitions->{$column}->{key} = $key;
1876 $definitions->{$column}->{default} = $default;
1877 $definitions->{$column}->{extra} = $extra;
1879 my $fieldrow = $fielddefinitions{$table};
1880 foreach my $row (@$fieldrow) {
1881 my $field = $row->{field};
1882 my $type = $row->{type};
1883 my $null = $row->{null};
1884 # $null = 'YES' if $row->{null} eq 'NULL';
1885 my $key = $row->{key};
1886 my $default = $row->{default};
1887 # $default="''" unless $default;
1888 my $extra = $row->{extra};
1889 my $def = $definitions->{$field};
1890 my $after = ($row->{after}?" after ".$row->{after}:"");
1892 unless ( $type eq $def->{type}
1893 && $null eq $def->{null}
1894 && $key eq $def->{key}
1895 && $extra eq $def->{extra} )
1897 if ( $null eq '' ) {
1900 if ( $key eq 'PRI' ) {
1901 $key = 'PRIMARY KEY';
1903 unless ( $extra eq 'auto_increment' ) {
1907 # if it's a new column use "add", if it's an old one, use "change".
1909 if ($definitions->{$field}->{type}) {
1910 $action="change $field"
1914 # if it's a primary key, drop the previous pk, before altering the table
1915 print " alter or create $field in $table\n" unless $silent;
1917 if ($key ne 'PRIMARY KEY') {
1918 # warn "alter table $table $action $field $type $null $key $extra default $default $after";
1919 $query = "alter table $table $action $field $type $null $key $extra ".($default?"default ".$dbh->quote($default):"")." $after";
1921 # warn "alter table $table drop primary key, $action $field $type $null $key $extra default $default $after";
1922 # something strange : for indexes UNIQUE, they are reported as primary key here.
1923 # but if you try to run with drop primary key, it fails.
1924 # thus, we run the query twice, one will fail, one will succeed.
1926 $query="alter table $table drop primary key, $action $field $type $null $key $extra ".($default?"default ".$dbh->quote($default):"")." $after";
1927 $query="alter table $table $action $field $type $null $key $extra ".($default?"default ".$dbh->quote($default):"")." $after";
1934 print "removing some unused data...\n";
1935 foreach my $table ( keys %linetodelete ) {
1936 foreach my $where ( @{linetodelete{$table}} ){
1937 print "DELETE FROM ".$table." where ".$where;
1939 my $sth = $dbh->prepare("DELETE FROM $table where $where");
1942 print "Error : $sth->errstr \n";
1947 # Populate tables with required data
1949 # synch table and deletedtable.
1950 foreach my $table (('borrowers','items','biblio','biblioitems')) {
1951 my %deletedborrowers;
1952 print "synch'ing $table and deleted$table\n";
1953 $sth = $dbh->prepare("show columns from deleted$table");
1955 while ( my ( $column, $type, $null, $key, $default, $extra ) = $sth->fetchrow ) {
1956 $deletedborrowers{$column}=1;
1958 $sth = $dbh->prepare("show columns from $table");
1961 while ( my ( $column, $type, $null, $key, $default, $extra ) = $sth->fetchrow ) {
1962 unless ($deletedborrowers{$column}) {
1963 my $newcol="alter table deleted$table add $column $type";
1964 if ($null eq 'YES') {
1965 $newcol .= " NULL ";
1967 $newcol .= " NOT NULL ";
1969 $newcol .= "default ".$dbh->quote($default) if $default;
1970 $newcol .= " after $previous" if $previous;
1972 print "creating column $column\n";
1978 # update publisheddate
1980 $sth = $dbh->prepare("select count(*) from serial where publisheddate is NULL");
1982 my ($emptypublished) = $sth->fetchrow;
1983 if ($emptypublished) {
1984 print "Updating publisheddate\n";
1985 $dbh->do("update serial set publisheddate=planneddate where publisheddate is NULL");
1987 foreach my $table ( keys %tabledata ) {
1988 print "Checking for data required in table $table...\n" unless $silent;
1989 my $tablerows = $tabledata{$table};
1990 foreach my $row (@$tablerows) {
1991 my $uniquefieldrequired = $row->{uniquefieldrequired};
1992 my $uniquevalue = $row->{$uniquefieldrequired};
1993 my $forceupdate = $row->{forceupdate};
1996 "select $uniquefieldrequired from $table where $uniquefieldrequired=?"
1998 $sth->execute($uniquevalue);
2000 foreach my $field (keys %$forceupdate) {
2001 if ($forceupdate->{$field}) {
2002 my $sth=$dbh->prepare("update systempreferences set $field=? where $uniquefieldrequired=?");
2003 $sth->execute($row->{$field}, $uniquevalue);
2007 print "Adding row to $table: " unless $silent;
2011 foreach my $field ( keys %$row ) {
2012 next if $field eq 'uniquefieldrequired';
2013 next if $field eq 'forceupdate';
2014 my $value = $row->{$field};
2015 push @values, $value;
2016 print " $field => $value" unless $silent;
2017 $fieldlist .= "$field,";
2018 $placeholders .= "?,";
2020 print "\n" unless $silent;
2021 $fieldlist =~ s/,$//;
2022 $placeholders =~ s/,$//;
2023 print "insert into $table ($fieldlist) values ($placeholders)";
2026 "insert into $table ($fieldlist) values ($placeholders)");
2027 $sth->execute(@values);
2033 # check indexes and create them when needed
2035 print "Checking for index required...\n" unless $silent;
2036 foreach my $table ( keys %indexes ) {
2038 # read all indexes from $table
2040 $sth = $dbh->prepare("show index from $table");
2042 my %existingindexes;
2043 while ( my ( $table, $non_unique, $key_name, $Seq_in_index, $Column_name, $Collation, $cardinality, $sub_part, $Packed, $comment ) = $sth->fetchrow ) {
2044 $existingindexes{$key_name} = 1;
2046 # read indexes to check
2047 my $tablerows = $indexes{$table};
2048 foreach my $row (@$tablerows) {
2049 my $key_name=$row->{indexname};
2050 if ($existingindexes{$key_name} eq 1) {
2051 # print "$key_name existing";
2053 print "\tCreating index $key_name in $table\n";
2055 if ($row->{indexname} eq 'PRIMARY') {
2056 $sql = "alter table $table ADD PRIMARY KEY ($row->{content})";
2058 $sql = "alter table $table ADD INDEX $key_name ($row->{content}) $row->{type}";
2061 print "Error $sql : $dbh->err \n" if $dbh->err;
2067 # check foreign keys and create them when needed
2069 print "Checking for foreign keys required...\n" unless $silent;
2070 foreach my $table ( keys %foreign_keys ) {
2072 # read all indexes from $table
2074 $sth = $dbh->prepare("show table status like '$table'");
2076 my $stat = $sth->fetchrow_hashref;
2077 # read indexes to check
2078 my $tablerows = $foreign_keys{$table};
2079 foreach my $row (@$tablerows) {
2080 my $foreign_table=$row->{foreigntable};
2081 if ($stat->{'Comment'} =~/$foreign_table/) {
2082 # print "$foreign_table existing\n";
2084 print "\tCreating foreign key $foreign_table in $table\n";
2085 # first, drop any orphan value in child table
2086 if ($row->{onDelete} ne "RESTRICT") {
2087 my $sql = "delete from $table where $row->{key} not in (select $row->{foreignkey} from $row->{foreigntable})";
2089 print "SQL ERROR: $sql : $dbh->err \n" if $dbh->err;
2091 my $sql="alter table $table ADD FOREIGN KEY $row->{key} ($row->{key}) REFERENCES $row->{foreigntable} ($row->{foreignkey})";
2092 $sql .= " on update ".$row->{onUpdate} if $row->{onUpdate};
2093 $sql .= " on delete ".$row->{onDelete} if $row->{onDelete};
2096 print "====================
2097 An error occured during :
2099 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).
2100 You can find those values with select
2101 \t$table.* from $table where $row->{key} not in (select $row->{foreignkey} from $row->{foreigntable})
2102 ====================\n
2108 # now drop useless tables
2109 foreach $table ( @TableToDelete ) {
2110 if ( $existingtables{$table} ) {
2111 print "Dropping unused table $table\n" if $debug and not $silent;
2112 $dbh->do("drop table $table");
2114 print "Error : $dbh->errstr \n";
2123 # create frameworkcode row in biblio table & fill it with marc_biblio.frameworkcode.
2126 # 1st, get how many biblio we will have to do...
2127 $sth = $dbh->prepare('select count(*) from marc_biblio');
2129 my ($totaltodo) = $sth->fetchrow;
2131 $sth = $dbh->prepare("show columns from biblio");
2134 my $bibliofwexist=0;
2135 while ( ( $column, $type, $null, $key, $default, $extra ) = $sth->fetchrow ){
2136 $bibliofwexist=1 if $column eq 'frameworkcode';
2138 unless ($bibliofwexist) {
2139 print "moving biblioframework to biblio table\n";
2140 $dbh->do('ALTER TABLE `biblio` ADD `frameworkcode` VARCHAR( 4 ) NOT NULL AFTER `biblionumber`');
2141 $sth = $dbh->prepare('select biblionumber,frameworkcode from marc_biblio');
2143 my $sth_update = $dbh->prepare('update biblio set frameworkcode=? where biblionumber=?');
2145 while (my ($biblionumber,$frameworkcode) = $sth->fetchrow) {
2146 $sth_update->execute($frameworkcode,$biblionumber);
2148 print "\r$totaldone / $totaltodo" unless ($totaldone % 100);
2153 # at last, remove useless fields
2154 foreach $table ( keys %uselessfields ) {
2155 my @fields = split /,/,$uselessfields{$table};
2158 foreach my $fieldtodrop (@fields) {
2159 $fieldtodrop =~ s/\t//g;
2160 $fieldtodrop =~ s/\n//g;
2162 $sth = $dbh->prepare("show columns from $table");
2164 while ( my ( $column, $type, $null, $key, $default, $extra ) = $sth->fetchrow )
2166 $exists =1 if ($column eq $fieldtodrop);
2169 print "deleting $fieldtodrop field in $table...\n" unless $silent;
2170 my $sth = $dbh->prepare("alter table $table drop $fieldtodrop");
2177 # Changing aqbookfund's primary key
2179 $sth=$dbh->prepare("ALTER TABLE `aqbookfund` DROP PRIMARY KEY , ADD PRIMARY KEY ( `bookfundid` , `branchcode` ) ;");
2183 print "upgrade to Koha 3.0 done\n";
2184 SetVersion ($DBversion);
2187 =item TransformToNum
2189 Transform the Koha version from a 4 parts string
2190 to a number, with just 1 .
2194 sub TransformToNum {
2195 my $version = shift;
2196 # remove the 3 last . to have a Perl number
2197 $version =~ s/(.*\..*)\.(.*)\.(.*)/$1$2$3/;
2202 set the DBversion in the systempreferences
2206 my $kohaversion = TransformToNum(shift);
2207 if (C4::Context->preference('Version')) {
2208 my $finish=$dbh->prepare("UPDATE systempreferences SET value=? WHERE variable='Version'");
2209 $finish->execute($kohaversion);
2211 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')");
2212 $finish->execute($kohaversion);
2218 # Revision 1.172 2007/07/19 10:21:22 hdl