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) NOT 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(25)',
786 type => 'varchar(4)',
794 type => 'varchar(30)',
800 field => 'publicationyear',
807 field => 'collectiontitle',
808 type => 'mediumtext',
814 field => 'collectionissn',
815 type => 'mediumtext',
821 field => 'collectionvolume',
822 type => 'mediumtext',
828 field => 'editionstatement',
835 field => 'editionresponsability',
843 deletedbiblioitems => [
846 type => 'varchar(30)',
855 type => 'varchar(15)',
862 field => 'branchprinter',
863 type => 'varchar(100)',
870 field => 'branchcode',
871 type => 'varchar(10)',
879 field => 'frombranch',
880 type => 'VARCHAR(10)',
888 type => 'VARCHAR(10)',
897 field => 'category_type',
905 field => 'categorycode',
906 type => 'varchar(10)',
914 deletedborrowers => [
915 { field => 'firstname',
919 { field => 'initials',
923 { field => 'B_email',
926 after => 'B_zipcode',
929 field => 'streetnumber', # street number (hidden if streettable table is empty)
935 field => 'streettype', # street table, list builded from a system table
938 after => 'streetnumber',
945 field => 'B_streetnumber', # street number (hidden if streettable table is empty)
951 field => 'B_streettype', # street table, list builded from a system table
954 after => 'B_streetnumber',
963 field => 'address2', # complement address
975 field => 'contactfirstname', # contact's firstname
978 after => 'contactname',
981 field => 'contacttitle', # contact's title
984 after => 'contactfirstname',
990 field => 'borrowernumber',
992 null => 'NULL', # can be null when a borrower is deleted and the foreign key rule executed
998 field => 'itemnumber',
1000 null => 'NULL', # can be null when a borrower is deleted and the foreign key rule executed
1006 field => 'branchcode',
1007 type => 'varchar(10)',
1014 field => 'issuedate',
1018 default => '0000-00-00',
1024 field => 'categorycode',
1025 type => 'varchar(10)',
1031 field => 'branchcode',
1032 type => 'varchar(10)',
1038 field => 'itemtype',
1039 type => 'varchar(10)',
1052 default => '0000-00-00',
1056 field => 'cutterextra',
1057 type => 'varchar(45)',
1064 field => 'homebranch',
1065 type => 'varchar(10)',
1072 field => 'holdingbranch',
1073 type => 'varchar(10)',
1081 type => 'varchar(10)',
1090 field => 'itemtype',
1091 type => 'varchar(10)',
1114 marc_subfield_structure => [
1116 field => 'defaultvalue',
1125 field => 'expirationdate',
1142 field => 'waitingdate',
1162 field => 'dateadded',
1163 type => 'timestamp',
1167 systempreferences => [
1177 field => 'explanation',
1199 # { indexname => 'index detail'
1203 { indexname => 'PRIMARY',
1209 { indexname => 'booksellerid',
1210 content => 'booksellerid',
1214 { indexname => 'basketno',
1215 content => 'basketno',
1218 aqorderbreakdown => [
1219 { indexname => 'ordernumber',
1220 content => 'ordernumber',
1222 { indexname => 'bookfundid',
1223 content => 'bookfundid',
1227 { indexname => 'isbn',
1230 { indexname => 'publishercode',
1231 content => 'publishercode',
1236 indexname => 'branchcode',
1237 content => 'branchcode',
1241 branchrelations => [
1243 indexname => 'PRIMARY',
1244 content => 'categorycode',
1248 branchrelations => [
1249 { indexname => 'PRIMARY',
1250 content => 'branchcode,categorycode',
1253 { indexname => 'branchcode',
1254 content => 'branchcode',
1256 { indexname => 'categorycode',
1257 content => 'categorycode',
1261 { indexname => 'PRIMARY',
1262 content => 'currency',
1268 indexname => 'categorycode',
1269 content => 'categorycode',
1273 { indexname => 'homebranch',
1274 content => 'homebranch',
1276 { indexname => 'holdingbranch',
1277 content => 'holdingbranch',
1282 indexname => 'itemtype',
1283 content => 'itemtype',
1287 { indexname => 'shelfnumber',
1288 content => 'shelfnumber',
1290 { indexname => 'itemnumber',
1291 content => 'itemnumber',
1295 { indexname => 'PRIMARY',
1302 my %foreign_keys = (
1304 # { key => 'the key in table' (must be indexed)
1305 # foreigntable => 'the foreigntable name', # (the parent)
1306 # foreignkey => 'the foreign key column(s)' # (in the parent)
1307 # onUpdate => 'CASCADE|SET NULL|NO ACTION| RESTRICT',
1308 # onDelete => 'CASCADE|SET NULL|NO ACTION| RESTRICT',
1311 branchrelations => [
1312 { key => 'branchcode',
1313 foreigntable => 'branches',
1314 foreignkey => 'branchcode',
1315 onUpdate => 'CASCADE',
1316 onDelete => 'CASCADE',
1318 { key => 'categorycode',
1319 foreigntable => 'branchcategories',
1320 foreignkey => 'categorycode',
1321 onUpdate => 'CASCADE',
1322 onDelete => 'CASCADE',
1326 { key => 'shelfnumber',
1327 foreigntable => 'virtualshelf',
1328 foreignkey => 'shelfnumber',
1329 onUpdate => 'CASCADE',
1330 onDelete => 'CASCADE',
1332 { key => 'itemnumber',
1333 foreigntable => 'items',
1334 foreignkey => 'itemnumber',
1335 onUpdate => 'CASCADE',
1336 onDelete => 'CASCADE',
1339 # onDelete is RESTRICT on reference tables (branches, itemtype) as we don't want items to be
1340 # easily deleted, but branches/itemtype not too easy to empty...
1342 { key => 'biblionumber',
1343 foreigntable => 'biblio',
1344 foreignkey => 'biblionumber',
1345 onUpdate => 'CASCADE',
1346 onDelete => 'CASCADE',
1348 { key => 'itemtype',
1349 foreigntable => 'itemtypes',
1350 foreignkey => 'itemtype',
1351 onUpdate => 'CASCADE',
1352 onDelete => 'RESTRICT',
1356 { key => 'biblioitemnumber',
1357 foreigntable => 'biblioitems',
1358 foreignkey => 'biblioitemnumber',
1359 onUpdate => 'CASCADE',
1360 onDelete => 'CASCADE',
1362 { key => 'homebranch',
1363 foreigntable => 'branches',
1364 foreignkey => 'branchcode',
1365 onUpdate => 'CASCADE',
1366 onDelete => 'RESTRICT',
1368 { key => 'holdingbranch',
1369 foreigntable => 'branches',
1370 foreignkey => 'branchcode',
1371 onUpdate => 'CASCADE',
1372 onDelete => 'RESTRICT',
1376 { key => 'booksellerid',
1377 foreigntable => 'aqbooksellers',
1379 onUpdate => 'CASCADE',
1380 onDelete => 'RESTRICT',
1384 { key => 'basketno',
1385 foreigntable => 'aqbasket',
1386 foreignkey => 'basketno',
1387 onUpdate => 'CASCADE',
1388 onDelete => 'CASCADE',
1390 { key => 'biblionumber',
1391 foreigntable => 'biblio',
1392 foreignkey => 'biblionumber',
1393 onUpdate => 'SET NULL',
1394 onDelete => 'SET NULL',
1398 { key => 'listprice',
1399 foreigntable => 'currency',
1400 foreignkey => 'currency',
1401 onUpdate => 'CASCADE',
1402 onDelete => 'CASCADE',
1404 { key => 'invoiceprice',
1405 foreigntable => 'currency',
1406 foreignkey => 'currency',
1407 onUpdate => 'CASCADE',
1408 onDelete => 'CASCADE',
1411 aqorderbreakdown => [
1412 { key => 'ordernumber',
1413 foreigntable => 'aqorders',
1414 foreignkey => 'ordernumber',
1415 onUpdate => 'CASCADE',
1416 onDelete => 'CASCADE',
1418 { key => 'bookfundid',
1419 foreigntable => 'aqbookfund',
1420 foreignkey => 'bookfundid',
1421 onUpdate => 'CASCADE',
1422 onDelete => 'CASCADE',
1425 branchtransfers => [
1426 { key => 'frombranch',
1427 foreigntable => 'branches',
1428 foreignkey => 'branchcode',
1429 onUpdate => 'CASCADE',
1430 onDelete => 'CASCADE',
1432 { key => 'tobranch',
1433 foreigntable => 'branches',
1434 foreignkey => 'branchcode',
1435 onUpdate => 'CASCADE',
1436 onDelete => 'CASCADE',
1438 { key => 'itemnumber',
1439 foreigntable => 'items',
1440 foreignkey => 'itemnumber',
1441 onUpdate => 'CASCADE',
1442 onDelete => 'CASCADE',
1446 { key => 'categorycode',
1447 foreigntable => 'categories',
1448 foreignkey => 'categorycode',
1449 onUpdate => 'CASCADE',
1450 onDelete => 'CASCADE',
1453 issues => [ # constraint is SET NULL : when a borrower or an item is deleted, we keep the issuing record
1455 { key => 'borrowernumber',
1456 foreigntable => 'borrowers',
1457 foreignkey => 'borrowernumber',
1458 onUpdate => 'SET NULL',
1459 onDelete => 'SET NULL',
1461 { key => 'itemnumber',
1462 foreigntable => 'items',
1463 foreignkey => 'itemnumber',
1464 onUpdate => 'SET NULL',
1465 onDelete => 'SET NULL',
1469 { key => 'borrowernumber',
1470 foreigntable => 'borrowers',
1471 foreignkey => 'borrowernumber',
1472 onUpdate => 'CASCADE',
1473 onDelete => 'CASCADE',
1475 { key => 'biblionumber',
1476 foreigntable => 'biblio',
1477 foreignkey => 'biblionumber',
1478 onUpdate => 'CASCADE',
1479 onDelete => 'CASCADE',
1481 { key => 'itemnumber',
1482 foreigntable => 'items',
1483 foreignkey => 'itemnumber',
1484 onUpdate => 'CASCADE',
1485 onDelete => 'CASCADE',
1487 { key => 'branchcode',
1488 foreigntable => 'branches',
1489 foreignkey => 'branchcode',
1490 onUpdate => 'CASCADE',
1491 onDelete => 'CASCADE',
1494 borrowers => [ # foreign keys are RESTRICT as we don't want to delete borrowers when a branch is deleted
1495 # but prevent deleting a branch as soon as it has 1 borrower !
1496 { key => 'categorycode',
1497 foreigntable => 'categories',
1498 foreignkey => 'categorycode',
1499 onUpdate => 'RESTRICT',
1500 onDelete => 'RESTRICT',
1502 { key => 'branchcode',
1503 foreigntable => 'branches',
1504 foreignkey => 'branchcode',
1505 onUpdate => 'RESTRICT',
1506 onDelete => 'RESTRICT',
1509 deletedborrowers => [ # foreign keys are RESTRICT as we don't want to delete borrowers when a branch is deleted
1510 # but prevent deleting a branch as soon as it has 1 borrower !
1511 { key => 'categorycode',
1512 foreigntable => 'categories',
1513 foreignkey => 'categorycode',
1514 onUpdate => 'RESTRICT',
1515 onDelete => 'RESTRICT',
1517 { key => 'branchcode',
1518 foreigntable => 'branches',
1519 foreignkey => 'branchcode',
1520 onUpdate => 'RESTRICT',
1521 onDelete => 'RESTRICT',
1525 { key => 'borrowernumber',
1526 foreigntable => 'borrowers',
1527 foreignkey => 'borrowernumber',
1528 onUpdate => 'CASCADE',
1529 onDelete => 'CASCADE',
1531 { key => 'itemnumber',
1532 foreigntable => 'items',
1533 foreignkey => 'itemnumber',
1534 onUpdate => 'SET NULL',
1535 onDelete => 'SET NULL',
1538 auth_tag_structure => [
1539 { key => 'authtypecode',
1540 foreigntable => 'auth_types',
1541 foreignkey => 'authtypecode',
1542 onUpdate => 'CASCADE',
1543 onDelete => 'CASCADE',
1546 # FIXME : don't constraint auth_*_table and auth_word, as they may be replaced by zebra
1551 my %column_change = (
1555 from => 'emailaddress',
1560 from => 'streetaddress',
1562 after => 'initials',
1565 from => 'faxnumber',
1570 from => 'textmessaging',
1576 to => 'contactnote',
1577 after => 'opacnote',
1580 from => 'physstreet',
1585 from => 'streetcity',
1587 after => 'B_address',
1600 from => 'homezipcode',
1607 after => 'B_zipcode',
1612 after => 'dateenrolled',
1615 from => 'guarantor',
1616 to => 'guarantorid',
1617 after => 'contactname',
1620 from => 'altrelationship',
1621 to => 'relationship',
1622 after => 'borrowernotes',
1626 deletedborrowers => [
1628 from => 'emailaddress',
1633 from => 'streetaddress',
1635 after => 'initials',
1638 from => 'faxnumber',
1643 from => 'textmessaging',
1649 to => 'contactnote',
1650 after => 'opacnote',
1653 from => 'physstreet',
1658 from => 'streetcity',
1660 after => 'B_address',
1673 from => 'homezipcode',
1680 after => 'B_zipcode',
1685 after => 'dateenrolled',
1688 from => 'guarantor',
1689 to => 'guarantorid',
1690 after => 'contactname',
1693 from => 'altrelationship',
1694 to => 'relationship',
1695 after => 'borrowernotes',
1701 # MOVE all tables TO UTF-8 and innoDB
1702 $sth = $dbh->prepare("show table status");
1704 while ( my $table = $sth->fetchrow_hashref ) {
1705 next if $table->{Name} eq 'marc_word';
1706 next if $table->{Name} eq 'marc_subfield_table';
1707 next if $table->{Name} eq 'auth_word';
1708 next if $table->{Name} eq 'auth_subfield_table';
1709 if ($table->{Engine} ne 'InnoDB') {
1710 print "moving $table->{Name} to InnoDB\n";
1711 $dbh->do("ALTER TABLE $table->{Name} TYPE = innodb");
1713 unless ($table->{Collation} =~ /^utf8/) {
1714 print "moving $table->{Name} to utf8\n";
1715 $dbh->do("ALTER TABLE $table->{Name} CONVERT TO CHARACTER SET utf8");
1716 $dbh->do("ALTER TABLE $table->{Name} DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci");
1717 # 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 !
1723 foreach my $table (keys %column_change) {
1724 $sth = $dbh->prepare("show columns from $table");
1727 while ( ( $column, $type, $null, $key, $default, $extra ) = $sth->fetchrow )
1729 $types{$column}->{type} ="$type";
1730 $types{$column}->{null} = "$null";
1731 $types{$column}->{key} = "$key";
1732 $types{$column}->{default} = "$default";
1733 $types{$column}->{extra} = "$extra";
1735 my $tablerows = $column_change{$table};
1736 foreach my $row ( @$tablerows ) {
1737 if ($types{$row->{from}}->{type}) {
1738 print "altering $table $row->{from} to $row->{to}\n";
1739 # ALTER TABLE `borrowers` CHANGE `faxnumber` `fax` TEXT CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL
1740 # alter table `borrowers` change `faxnumber` `fax` type text null after phone
1742 "alter table `$table` change `$row->{from}` `$row->{to}` $types{$row->{from}}->{type} ".
1743 ($types{$row->{from}}->{null} eq 'YES'?" NULL":" NOT NULL").
1744 ($types{$row->{from}}->{default}?" default ".$types{$row->{from}}->{default}:"").
1745 "$types{$row->{from}}->{extra} after $row->{after} ";
1752 # Enter here the field you want to delete from DB.
1753 # FIXME :: there is a %uselessfield before which seems doing the same things.
1754 my %fieldtodelete = (
1755 # tablename => [fieldname1,fieldname2,...]
1759 print "removing some unused fields...\n";
1760 foreach my $table ( keys %fieldtodelete ) {
1761 foreach my $field ( @{$fieldtodelete{$table}} ){
1762 print "removing ".$field." from ".$table;
1763 my $sth = $dbh->prepare("ALTER TABLE $table DROP $field");
1766 print "Error : $sth->errstr \n";
1771 # Enter here the line you want to remove from DB.
1772 my %linetodelete = (
1773 # table name => where clause.
1774 userflags => "bit = 8", # delete the 'reserveforself' flags
1778 #-------------------
1783 # Get version of MySQL database engine.
1784 my $mysqlversion = `mysqld --version`;
1785 $mysqlversion =~ /Ver (\S*) /;
1787 if ( $mysqlversion ge '3.23' ) {
1788 print "Could convert to MyISAM database tables...\n" unless $silent;
1791 #---------------------------------
1794 # Collect all tables into a list
1795 $sth = $dbh->prepare("show tables");
1797 while ( my ($table) = $sth->fetchrow ) {
1798 $existingtables{$table} = 1;
1802 # Now add any missing tables
1803 foreach $table ( keys %requiretables ) {
1804 unless ( $existingtables{$table} ) {
1805 print "Adding $table table...\n" unless $silent;
1806 my $sth = $dbh->prepare("create table $table $requiretables{$table}");
1809 print "Error : $sth->errstr \n";
1815 #---------------------------------
1818 foreach $table ( keys %requirefields ) {
1819 print "Check table $table\n" if $debug and not $silent;
1820 $sth = $dbh->prepare("show columns from $table");
1823 while ( ( $column, $type, $null, $key, $default, $extra ) = $sth->fetchrow )
1825 $types{$column} = $type;
1827 foreach $column ( keys %{ $requirefields{$table} } ) {
1828 print " Check column $column [$types{$column}]\n" if $debug and not $silent;
1829 if ( !$types{$column} ) {
1831 # column doesn't exist
1832 print "Adding $column field to $table table...\n" unless $silent;
1833 $query = "alter table $table
1834 add column $column " . $requirefields{$table}->{$column};
1835 print "Execute: $query\n" if $debug;
1836 my $sti = $dbh->prepare($query);
1839 print "**Error : $sti->errstr \n";
1846 foreach $table ( keys %fielddefinitions ) {
1847 print "Check table $table\n" if $debug;
1848 $sth = $dbh->prepare("show columns from $table");
1851 while ( ( $column, $type, $null, $key, $default, $extra ) = $sth->fetchrow )
1853 $definitions->{$column}->{type} = $type;
1854 $definitions->{$column}->{null} = $null;
1855 $definitions->{$column}->{null} = 'NULL' if $null eq 'YES';
1856 $definitions->{$column}->{key} = $key;
1857 $definitions->{$column}->{default} = $default;
1858 $definitions->{$column}->{extra} = $extra;
1860 my $fieldrow = $fielddefinitions{$table};
1861 foreach my $row (@$fieldrow) {
1862 my $field = $row->{field};
1863 my $type = $row->{type};
1864 my $null = $row->{null};
1865 # $null = 'YES' if $row->{null} eq 'NULL';
1866 my $key = $row->{key};
1867 my $default = $row->{default};
1868 # $default="''" unless $default;
1869 my $extra = $row->{extra};
1870 my $def = $definitions->{$field};
1871 my $after = ($row->{after}?" after ".$row->{after}:"");
1873 unless ( $type eq $def->{type}
1874 && $null eq $def->{null}
1875 && $key eq $def->{key}
1876 && $extra eq $def->{extra} )
1878 if ( $null eq '' ) {
1881 if ( $key eq 'PRI' ) {
1882 $key = 'PRIMARY KEY';
1884 unless ( $extra eq 'auto_increment' ) {
1888 # if it's a new column use "add", if it's an old one, use "change".
1890 if ($definitions->{$field}->{type}) {
1891 $action="change $field"
1895 # if it's a primary key, drop the previous pk, before altering the table
1896 print " alter or create $field in $table\n" unless $silent;
1898 if ($key ne 'PRIMARY KEY') {
1899 # warn "alter table $table $action $field $type $null $key $extra default $default $after";
1900 $query = "alter table $table $action $field $type $null $key $extra ".($default?"default ".$dbh->quote($default):"")." $after";
1902 # warn "alter table $table drop primary key, $action $field $type $null $key $extra default $default $after";
1903 # something strange : for indexes UNIQUE, they are reported as primary key here.
1904 # but if you try to run with drop primary key, it fails.
1905 # thus, we run the query twice, one will fail, one will succeed.
1907 $query="alter table $table drop primary key, $action $field $type $null $key $extra ".($default?"default ".$dbh->quote($default):"")." $after";
1908 $query="alter table $table $action $field $type $null $key $extra ".($default?"default ".$dbh->quote($default):"")." $after";
1915 print "removing some unused data...\n";
1916 foreach my $table ( keys %linetodelete ) {
1917 foreach my $where ( @{linetodelete{$table}} ){
1918 print "DELETE FROM ".$table." where ".$where;
1920 my $sth = $dbh->prepare("DELETE FROM $table where $where");
1923 print "Error : $sth->errstr \n";
1928 # Populate tables with required data
1930 # synch table and deletedtable.
1931 foreach my $table (('borrowers','items','biblio','biblioitems')) {
1932 my %deletedborrowers;
1933 print "synch'ing $table and deleted$table\n";
1934 $sth = $dbh->prepare("show columns from deleted$table");
1936 while ( my ( $column, $type, $null, $key, $default, $extra ) = $sth->fetchrow ) {
1937 $deletedborrowers{$column}=1;
1939 $sth = $dbh->prepare("show columns from $table");
1942 while ( my ( $column, $type, $null, $key, $default, $extra ) = $sth->fetchrow ) {
1943 unless ($deletedborrowers{$column}) {
1944 my $newcol="alter table deleted$table add $column $type";
1945 if ($null eq 'YES') {
1946 $newcol .= " NULL ";
1948 $newcol .= " NOT NULL ";
1950 $newcol .= "default ".$dbh->quote($default) if $default;
1951 $newcol .= " after $previous" if $previous;
1953 print "creating column $column\n";
1959 # update publisheddate
1961 $sth = $dbh->prepare("select count(*) from serial where publisheddate is NULL");
1963 my ($emptypublished) = $sth->fetchrow;
1964 if ($emptypublished) {
1965 print "Updating publisheddate\n";
1966 $dbh->do("update serial set publisheddate=planneddate where publisheddate is NULL");
1968 foreach my $table ( keys %tabledata ) {
1969 print "Checking for data required in table $table...\n" unless $silent;
1970 my $tablerows = $tabledata{$table};
1971 foreach my $row (@$tablerows) {
1972 my $uniquefieldrequired = $row->{uniquefieldrequired};
1973 my $uniquevalue = $row->{$uniquefieldrequired};
1974 my $forceupdate = $row->{forceupdate};
1977 "select $uniquefieldrequired from $table where $uniquefieldrequired=?"
1979 $sth->execute($uniquevalue);
1981 foreach my $field (keys %$forceupdate) {
1982 if ($forceupdate->{$field}) {
1983 my $sth=$dbh->prepare("update systempreferences set $field=? where $uniquefieldrequired=?");
1984 $sth->execute($row->{$field}, $uniquevalue);
1988 print "Adding row to $table: " unless $silent;
1992 foreach my $field ( keys %$row ) {
1993 next if $field eq 'uniquefieldrequired';
1994 next if $field eq 'forceupdate';
1995 my $value = $row->{$field};
1996 push @values, $value;
1997 print " $field => $value" unless $silent;
1998 $fieldlist .= "$field,";
1999 $placeholders .= "?,";
2001 print "\n" unless $silent;
2002 $fieldlist =~ s/,$//;
2003 $placeholders =~ s/,$//;
2004 print "insert into $table ($fieldlist) values ($placeholders)";
2007 "insert into $table ($fieldlist) values ($placeholders)");
2008 $sth->execute(@values);
2014 # check indexes and create them when needed
2016 print "Checking for index required...\n" unless $silent;
2017 foreach my $table ( keys %indexes ) {
2019 # read all indexes from $table
2021 $sth = $dbh->prepare("show index from $table");
2023 my %existingindexes;
2024 while ( my ( $table, $non_unique, $key_name, $Seq_in_index, $Column_name, $Collation, $cardinality, $sub_part, $Packed, $comment ) = $sth->fetchrow ) {
2025 $existingindexes{$key_name} = 1;
2027 # read indexes to check
2028 my $tablerows = $indexes{$table};
2029 foreach my $row (@$tablerows) {
2030 my $key_name=$row->{indexname};
2031 if ($existingindexes{$key_name} eq 1) {
2032 # print "$key_name existing";
2034 print "\tCreating index $key_name in $table\n";
2036 if ($row->{indexname} eq 'PRIMARY') {
2037 $sql = "alter table $table ADD PRIMARY KEY ($row->{content})";
2039 $sql = "alter table $table ADD INDEX $key_name ($row->{content}) $row->{type}";
2042 print "Error $sql : $dbh->err \n" if $dbh->err;
2048 # check foreign keys and create them when needed
2050 print "Checking for foreign keys required...\n" unless $silent;
2051 foreach my $table ( keys %foreign_keys ) {
2053 # read all indexes from $table
2055 $sth = $dbh->prepare("show table status like '$table'");
2057 my $stat = $sth->fetchrow_hashref;
2058 # read indexes to check
2059 my $tablerows = $foreign_keys{$table};
2060 foreach my $row (@$tablerows) {
2061 my $foreign_table=$row->{foreigntable};
2062 if ($stat->{'Comment'} =~/$foreign_table/) {
2063 # print "$foreign_table existing\n";
2065 print "\tCreating foreign key $foreign_table in $table\n";
2066 # first, drop any orphan value in child table
2067 if ($row->{onDelete} ne "RESTRICT") {
2068 my $sql = "delete from $table where $row->{key} not in (select $row->{foreignkey} from $row->{foreigntable})";
2070 print "SQL ERROR: $sql : $dbh->err \n" if $dbh->err;
2072 my $sql="alter table $table ADD FOREIGN KEY $row->{key} ($row->{key}) REFERENCES $row->{foreigntable} ($row->{foreignkey})";
2073 $sql .= " on update ".$row->{onUpdate} if $row->{onUpdate};
2074 $sql .= " on delete ".$row->{onDelete} if $row->{onDelete};
2077 print "====================
2078 An error occured during :
2080 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).
2081 You can find those values with select
2082 \t$table.* from $table where $row->{key} not in (select $row->{foreignkey} from $row->{foreigntable})
2083 ====================\n
2089 # now drop useless tables
2090 foreach $table ( @TableToDelete ) {
2091 if ( $existingtables{$table} ) {
2092 print "Dropping unused table $table\n" if $debug and not $silent;
2093 $dbh->do("drop table $table");
2095 print "Error : $dbh->errstr \n";
2104 # create frameworkcode row in biblio table & fill it with marc_biblio.frameworkcode.
2107 # 1st, get how many biblio we will have to do...
2108 $sth = $dbh->prepare('select count(*) from marc_biblio');
2110 my ($totaltodo) = $sth->fetchrow;
2112 $sth = $dbh->prepare("show columns from biblio");
2115 my $bibliofwexist=0;
2116 while ( ( $column, $type, $null, $key, $default, $extra ) = $sth->fetchrow ){
2117 $bibliofwexist=1 if $column eq 'frameworkcode';
2119 unless ($bibliofwexist) {
2120 print "moving biblioframework to biblio table\n";
2121 $dbh->do('ALTER TABLE `biblio` ADD `frameworkcode` VARCHAR( 4 ) NOT NULL AFTER `biblionumber`');
2122 $sth = $dbh->prepare('select biblionumber,frameworkcode from marc_biblio');
2124 my $sth_update = $dbh->prepare('update biblio set frameworkcode=? where biblionumber=?');
2126 while (my ($biblionumber,$frameworkcode) = $sth->fetchrow) {
2127 $sth_update->execute($frameworkcode,$biblionumber);
2129 print "\r$totaldone / $totaltodo" unless ($totaldone % 100);
2134 # at last, remove useless fields
2135 foreach $table ( keys %uselessfields ) {
2136 my @fields = split /,/,$uselessfields{$table};
2139 foreach my $fieldtodrop (@fields) {
2140 $fieldtodrop =~ s/\t//g;
2141 $fieldtodrop =~ s/\n//g;
2143 $sth = $dbh->prepare("show columns from $table");
2145 while ( my ( $column, $type, $null, $key, $default, $extra ) = $sth->fetchrow )
2147 $exists =1 if ($column eq $fieldtodrop);
2150 print "deleting $fieldtodrop field in $table...\n" unless $silent;
2151 my $sth = $dbh->prepare("alter table $table drop $fieldtodrop");
2158 # Changing aqbookfund's primary key
2160 $sth=$dbh->prepare("ALTER TABLE `aqbookfund` DROP PRIMARY KEY , ADD PRIMARY KEY ( `bookfundid` , `branchcode` ) ;");
2164 print "upgrade to Koha 3.0 done\n";
2165 SetVersion ($DBversion);
2168 =item TransformToNum
2170 Transform the Koha version from a 4 parts string
2171 to a number, with just 1 .
2175 sub TransformToNum {
2176 my $version = shift;
2177 # remove the 3 last . to have a Perl number
2178 $version =~ s/(.*\..*)\.(.*)\.(.*)/$1$2$3/;
2183 set the DBversion in the systempreferences
2187 my $kohaversion = TransformToNum(shift);
2188 if (C4::Context->preference('Version')) {
2189 my $finish=$dbh->prepare("UPDATE systempreferences SET value=? WHERE variable='Version'");
2190 $finish->execute($kohaversion);
2192 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')");
2193 $finish->execute($kohaversion);
2199 # Revision 1.172 2007/07/19 10:21:22 hdl