5 # This script checks for required updates to the database.
7 # Part of the Koha Library Software www.koha.org
8 # Licensed under the GPL.
11 # - Would also be a good idea to offer to do a backup at this time...
13 # NOTE: If you do something more than once in here, make it table driven.
23 use MARC::File::XML ( BinaryEncoding => 'utf8' );
25 # FIXME - The user might be installing a new database, so can't rely
26 # on /etc/koha.conf anyway.
33 %existingtables, # tables already in database
37 $type, $null, $key, $default, $extra,
38 $prefitem, # preference item in systempreferences table
45 my $dbh = C4::Context->dbh;
46 $|=1; # flushes output
48 my $DBversion = "3.00.00.000";
49 # if we are upgrading from Koha 2.2, then we need to run the complete & long updatedatabase
50 # Tables to add if they don't exist
53 `timestamp` TIMESTAMP NOT NULL ,
54 `user` INT( 11 ) NOT NULL default '0' ,
55 `module` TEXT default '',
56 `action` TEXT default '' ,
57 `object` INT(11) NULL ,
58 `info` TEXT default '' ,
59 PRIMARY KEY ( `timestamp` , `user` )
62 module varchar(20) NOT NULL default '',
63 code varchar(20) NOT NULL default '',
64 name varchar(100) NOT NULL default '',
65 title varchar(200) NOT NULL default '',
67 PRIMARY KEY (module,code)
70 alertid int(11) NOT NULL auto_increment,
71 borrowernumber int(11) NOT NULL default '0',
72 type varchar(10) NOT NULL default '',
73 externalid varchar(20) NOT NULL default '',
74 PRIMARY KEY (alertid),
75 KEY borrowernumber (borrowernumber),
76 KEY type (type,externalid)
79 `idnew` int(10) unsigned NOT NULL auto_increment,
80 `title` varchar(250) NOT NULL default '',
82 `lang` varchar(4) NOT NULL default '',
83 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP,
86 repeatable_holidays => "(
87 `id` int(11) NOT NULL auto_increment,
88 `branchcode` varchar(4) NOT NULL default '',
89 `weekday` smallint(6) default NULL,
90 `day` smallint(6) default NULL,
91 `month` smallint(6) default NULL,
92 `title` varchar(50) NOT NULL default '',
93 `description` text NOT NULL,
96 special_holidays => "(
97 `id` int(11) NOT NULL auto_increment,
98 `branchcode` varchar(4) NOT NULL default '',
99 `day` smallint(6) NOT NULL default '0',
100 `month` smallint(6) NOT NULL default '0',
101 `year` smallint(6) NOT NULL default '0',
102 `isexception` smallint(1) NOT NULL default '1',
103 `title` varchar(50) NOT NULL default '',
104 `description` text NOT NULL,
107 overduerules =>"(`branchcode` varchar(255) NOT NULL default '',
108 `categorycode` char(2) NOT NULL default '',
109 `delay1` int(4) default '0',
110 `letter1` varchar(20) default NULL,
111 `debarred1` char(1) default '0',
112 `delay2` int(4) default '0',
113 `debarred2` char(1) default '0',
114 `letter2` varchar(20) default NULL,
115 `delay3` int(4) default '0',
116 `letter3` varchar(20) default NULL,
117 `debarred3` int(1) default '0',
118 PRIMARY KEY (`branchcode`,`categorycode`)
120 cities => "(`cityid` int auto_increment,
121 `city_name` char(100) NOT NULL,
122 `city_zipcode` char(20),
123 PRIMARY KEY (`cityid`)
125 roadtype => "(`roadtypeid` int auto_increment,
126 `road_type` char(100) NOT NULL,
127 PRIMARY KEY (`roadtypeid`)
131 labelid int(11) NOT NULL auto_increment,
132 itemnumber varchar(100) NOT NULL default '',
133 timestamp timestamp(14) NOT NULL,
134 PRIMARY KEY (labelid)
138 id int(4) NOT NULL auto_increment,
139 barcodetype char(100) default '',
140 title tinyint(1) default '0',
141 isbn tinyint(1) default '0',
142 itemtype tinyint(1) default '0',
143 barcode tinyint(1) default '0',
144 dewey tinyint(1) default '0',
145 class tinyint(1) default '0',
146 author tinyint(1) default '0',
147 papertype char(100) default '',
148 startrow int(2) default NULL,
152 reviewid integer NOT NULL auto_increment,
153 borrowernumber integer,
154 biblionumber integer,
157 datereviewed datetime,
158 PRIMARY KEY (reviewid)
160 subscriptionroutinglist=>"(
161 routingid integer NOT NULL auto_increment,
162 borrowernumber integer,
164 subscriptionid integer,
165 PRIMARY KEY (routingid)
169 notify_id int(11) NOT NULL default '0',
170 `borrowernumber` int(11) NOT NULL default '0',
171 `itemnumber` int(11) NOT NULL default '0',
172 `notify_date` date NOT NULL default '0000-00-00',
173 `notify_send_date` date default NULL,
174 `notify_level` int(1) NOT NULL default '0',
175 `method` varchar(20) NOT NULL default ''
179 `charge_id` varchar(5) NOT NULL default '',
180 `description` text NOT NULL,
181 `amount` decimal(28,6) NOT NULL default '0.000000',
182 `min` int(4) NOT NULL default '0',
183 `max` int(4) NOT NULL default '0',
184 `level` int(1) NOT NULL default '0',
185 PRIMARY KEY (`charge_id`)
188 `entry` varchar(255) NOT NULL default '',
189 `weight` bigint(20) NOT NULL default '0',
190 PRIMARY KEY (`entry`)
194 `id` int NOT NULL auto_increment,
195 `biblio_auth_number` int NOT NULL,
196 `operation` char(20) NOT NULL,
197 `server` char(20) NOT NULL ,
199 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci AUTO_INCREMENT=1",
203 my %requirefields = (
204 subscription => { 'letter' => 'char(20) NULL', 'distributedto' => 'text NULL', 'firstacquidate'=>'date NOT NULL','irregularity'=>'TEXT NULL default \'\'','numberpattern'=>'TINYINT(3) NULL default 0', 'callnumber'=>'text NULL', 'hemisphere' =>'TINYINT(3) NULL default 0', 'issuesatonce'=>'TINYINT(3) NOT NULL default 1', 'branchcode' =>'varchar(12) NOT NULL default \'\'', 'manualhistory'=>'TINYINT(1) NOT NULL default 0','internalnotes'=>'LONGTEXT NULL default \'\''},
205 itemtypes => { 'imageurl' => 'char(200) NULL'},
206 aqbookfund => { 'branchcode' => 'varchar(4) NULL'},
207 aqbudget => { 'branchcode' => 'varchar(4) NULL'},
208 auth_header => { 'marc' => 'BLOB NOT NULL', 'linkid' => 'BIGINT(20) NULL'},
209 auth_subfield_structure =>{ 'hidden' => 'TINYINT(3) NOT NULL default 0', 'kohafield' => 'VARCHAR(45) NULL', 'linkid' => 'TINYINT(1) NOT NULL default 0', 'isurl' => 'TINYINT(1)', 'frameworkcode'=>'VARCHAR(8) NOT NULL'},
210 marc_breeding => { 'isbn' => 'varchar(13) NOT NULL'},
211 serial =>{ 'publisheddate' => 'date', 'claimdate' => 'date', 'itemnumber'=>'text NULL','routingnotes'=>'text NULL',},
212 statistics => { 'associatedborrower' => 'integer'},
213 z3950servers =>{ "name" =>"text", "description" => "text NOT NULL",
214 "position" =>"enum('primary','secondary','') NOT NULL default 'primary'", "icon" =>"text",
215 "type" =>"enum('zed','opensearch') NOT NULL default 'zed'",
217 issues =>{ 'issuedate'=>"date NOT NULL default '0000-00-00'", },
219 # tablename => { 'field' => 'fieldtype' },
222 # Enter here the table to delete.
223 my @TableToDelete = qw(
230 my %uselessfields = (
231 # tablename => "field1,field2",
232 borrowers => "suburb,altstreetaddress,altsuburb,altcity,studentnumber,school,area,preferredcont,altcp",
233 deletedborrowers=> "suburb,altstreetaddress,altsuburb,altcity,studentnumber,school,area,preferredcont,altcp",
235 # the other hash contains other actions that can't be done elsewhere. they are done
236 # either BEFORE of AFTER everything else, depending on "when" entry (default => AFTER)
238 # The tabledata hash contains data that should be in the tables.
239 # The uniquefieldrequired hash entry is used to determine which (if any) fields
240 # must not exist in the table for this row to be inserted. If the
241 # uniquefieldrequired entry is already in the table, the existing data is not
242 # modified, unless the forceupdate hash entry is also set. Fields in the
243 # anonymous "forceupdate" hash will be forced to be updated to the default
244 # values given in the %tabledata hash.
248 # { uniquefielrequired => 'fieldname', # the primary key in the table
249 # fieldname => fieldvalue,
250 # fieldname2 => fieldvalue2,
253 systempreferences => [
255 uniquefieldrequired => 'variable',
256 variable => 'useDaysMode',
258 forceupdate => { 'explanation' => 1,
260 explanation => 'Choose the method for calculating due date: select Calendar to use the holidays module, and Days to ignore the holidays module',
262 options => 'Calendar|Days|Datedue'
265 uniquefieldrequired => 'variable',
266 variable => 'DebugLevel',
268 forceupdate => { 'explanation' => 1,
270 explanation => 'Set the level of error info sent to the browser. 0=none, 1=some, 2=most',
275 uniquefieldrequired => 'variable',
276 variable => 'BorrowersTitles',
277 value => 'Mr|Mrs|Miss|Ms',
278 forceupdate => { 'explanation' => 1,
280 explanation => 'List all Titles for borrowers',
284 uniquefieldrequired => 'variable',
285 variable => 'BorrowerMandatoryField',
286 value => 'cardnumber|surname|address',
287 forceupdate => { 'explanation' => 1,
289 explanation => 'List all mandatory fields for borrowers',
293 uniquefieldrequired => 'variable',
294 variable => 'borrowerRelationship',
295 value => 'father|mother,grand-mother',
296 forceupdate => { 'explanation' => 1,
298 explanation => 'The relationships between a guarantor & a guarantee (separated by | or ,)',
302 uniquefieldrequired => 'variable',
303 variable => 'ReservesMaxPickUpDelay',
305 forceupdate => { 'explanation' => 1,
307 explanation => 'Maximum delay to pick up a reserved document',
311 uniquefieldrequired => 'variable',
312 variable => 'TransfersMaxDaysWarning',
314 forceupdate => { 'explanation' => 1,
316 explanation => 'Max delay before considering the transfer has potentialy a problem',
320 uniquefieldrequired => 'variable',
321 variable => 'memberofinstitution',
323 forceupdate => { 'explanation' => 1,
325 explanation => 'Are your patrons members of institutions',
329 uniquefieldrequired => 'variable',
330 variable => 'ReadingHistory',
332 forceupdate => { 'explanation' => 1,
334 explanation => 'Allow reading record info retrievable from issues and oldissues tables',
338 uniquefieldrequired => 'variable',
339 variable => 'IssuingInProcess',
341 forceupdate => { 'explanation' => 1,
343 explanation => 'Allow no debt alert if the patron is issuing item that accumulate debt',
347 uniquefieldrequired => 'variable',
348 variable => 'AutomaticItemReturn',
350 forceupdate => { 'explanation' => 1,
352 explanation => 'This Variable allow or not to return automaticly to his homebranch',
356 uniquefieldrequired => 'variable',
357 variable => 'reviewson',
359 forceupdate => { 'explanation' => 1,
361 explanation => 'Allows patrons to submit reviews from the opac',
365 uniquefieldrequired => 'variable',
366 variable => 'intranet_includes',
368 forceupdate => { 'explanation' => 1,
370 explanation => 'The includes directory you want for specific look of Koha (includes or includes_npl for example)',
374 uniquefieldrequired => 'variable',
375 variable => 'AutoLocation',
377 forceupdate => { 'explanation' => 1,
379 explanation => 'switch to activate or not Autolocation, if Yes, the Librarian can\'t change his location, it\'s defined by branchip',
383 uniquefieldrequired => 'variable',
384 variable => 'serialsadditems',
390 explanation => 'If set, a new item will be automatically added when receiving an issue',
394 uniquefieldrequired => 'variable',
395 variable => 'expandedSearchOption',
401 explanation => 'search among marc field',
405 uniquefieldrequired => 'variable',
406 variable => 'RequestOnOpac',
408 forceupdate => { 'explanation' => 1,
410 explanation => 'option to allow reserves on opac',
414 uniquefieldrequired => 'variable',
415 variable => 'OpacCloud',
417 forceupdate => { 'explanation' => 1,
419 explanation => 'Enable / Disable cloud link on OPAC (Require to run misc/cronjobs/build_browser_and_cloud.pl on the server)',
423 uniquefieldrequired => 'variable',
424 variable => 'OpacBrowser',
426 forceupdate => { 'explanation' => 1,
428 explanation => 'Enable/Disable browser link on OPAC (Require to run misc/cronjobs/build_browser_and_cloud.pl on the server)',
432 uniquefieldrequired => 'variable',
433 variable => 'OpacTopissue',
435 forceupdate => { 'explanation' => 1,
437 explanation => 'Enable / Disable the top issue link on OPAC',
441 uniquefieldrequired => 'variable',
442 variable => 'OpacAuthorities',
444 forceupdate => { 'explanation' => 1,
446 explanation => 'Enable / Disable the search authority link on OPAC',
450 uniquefieldrequired => 'variable',
451 variable => 'CataloguingLog',
453 forceupdate => {'explanation' => 1, 'type' => 1},
454 explanation => 'Active this if you want to log cataloguing action.',
458 uniquefieldrequired => 'variable',
459 variable => 'BorrowersLog',
461 forceupdate => {'explanation' => 1, 'type' => 1},
462 explanation => 'Active this if you want to log borrowers edition/creation/deletion...',
466 uniquefieldrequired => 'variable',
467 variable => 'SubscriptionLog',
469 forceupdate => {'explanation' => 1, 'type' => 1},
470 explanation => 'Active this if you want to log Subscription action',
474 uniquefieldrequired => 'variable',
475 variable => 'IssueLog',
477 forceupdate => {'explanation' => 1, 'type' => 1},
478 explanation => 'Active this if you want to log issue.',
482 uniquefieldrequired => 'variable',
483 variable => 'ReturnLog',
485 forceupdate => {'explanation' => 1, 'type' => 1},
486 explanation => 'Active this if you want to log the circulation return',
490 uniquefieldrequired => 'variable',
491 variable => 'Version',
493 forceupdate => {'explanation' => 1, 'type' => 1},
494 explanation => 'Koha Version',
498 uniquefieldrequired => 'variable',
499 variable => 'LetterLog',
501 forceupdate => {'explanation' => 1, 'type' => 1},
502 explanation => 'Active this if you want to log all the letter sent',
506 uniquefieldrequired => 'variable',
507 variable => 'FinesLog',
509 forceupdate => {'explanation' => 1, 'type' => 1},
510 explanation => 'Active this if you want to log fines',
514 uniquefieldrequired => 'variable',
515 variable => 'NoZebra',
517 forceupdate => {'explanation' => 1, 'type' => 1},
518 explanation => 'Active this if you want NOT to use zebra (large libraries should avoid this parameters)',
522 uniquefieldrequired => 'variable',
523 variable => 'NoZebraIndexes',
525 forceupdate => {'explanation' => 1, 'type' => 1},
526 explanation => "Enter a specific hash for NoZebra indexes. Enter : 'indexname' => '100a,245a,500*','index2' => '...'",
530 uniquefieldrequired => 'variable',
531 variable => 'uppercasesurnames',
533 forceupdate => {'explanation' => 1, 'type' => 1},
534 explanation => "Force Surnames to be uppercase",
540 uniquefieldrequired => 'bit',
542 flag => 'editauthorities',
543 flagdesc => 'allow to edit authorities',
547 uniquefieldrequired => 'bit',
550 flagdesc => 'allow to manage serials subscriptions',
554 uniquefieldrequired => 'bit',
557 flagdesc => 'allow to access to the reports module',
561 authorised_values => [
563 uniquefieldrequired => 'id',
564 category => 'SUGGEST',
565 authorised_value => 'Not enough budget',
566 lib => 'This book it too much expensive',
571 my %fielddefinitions = (
573 # { field => 'fieldname',
574 # type => 'fieldtype',
582 field => 'booksellerid',
592 field => 'bookfundid',
593 type => 'varchar(5)',
608 extra => 'auto_increment',
611 field => 'listprice',
612 type => 'varchar(10)',
619 field => 'invoiceprice',
620 type => 'varchar(10)',
627 field => 'invoicedisc',
628 type => 'float(6,4)',
639 field => 'bookfundid',
640 type => 'varchar(5)',
650 field => 'notify_id',
658 field => 'notify_level',
666 field => 'accountno',
667 type => 'smallint(6)',
674 field => 'description',
675 type => 'mediumtext',
680 type => 'mediumtext',
687 { field => 'firstname',
691 { field => 'initials',
695 { field => 'B_email',
698 after => 'B_zipcode',
701 field => 'streetnumber', # street number (hidden if streettable table is empty)
707 field => 'streettype', # street table, list builded from a system table
710 after => 'streetnumber',
717 field => 'B_streetnumber', # street number (hidden if streettable table is empty)
723 field => 'B_streettype', # street table, list builded from a system table
726 after => 'B_streetnumber',
735 field => 'address2', # complement address
747 field => 'contactfirstname', # contact's firstname
750 after => 'contactname',
753 field => 'contacttitle', # contact's title
756 after => 'contactfirstname',
759 field => 'branchcode',
760 type => 'varchar(10)',
766 field => 'categorycode',
767 type => 'varchar(10)',
777 type => 'varchar(10)',
785 type => 'varchar(25)',
793 type => 'varchar(4)',
801 type => 'varchar(30)',
807 field => 'publicationyear',
814 field => 'collectiontitle',
815 type => 'mediumtext',
821 field => 'collectionissn',
822 type => 'mediumtext',
828 field => 'collectionvolume',
829 type => 'mediumtext',
835 field => 'editionstatement',
842 field => 'editionresponsibility',
852 field => 'datecreated',
859 field => 'frameworkcode',
860 type => 'varchar(4)',
868 field => 'datecreated',
875 field => 'frameworkcode',
876 type => 'varchar(4)',
882 deletedbiblioitems => [
885 type => 'varchar(10)',
892 type => 'varchar(30)',
901 type => 'varchar(15)',
908 field => 'branchprinter',
909 type => 'varchar(100)',
916 field => 'branchcode',
917 type => 'varchar(10)',
925 field => 'frombranch',
926 type => 'VARCHAR(10)',
934 type => 'VARCHAR(10)',
943 field => 'category_type',
951 field => 'categorycode',
952 type => 'varchar(10)',
960 deletedborrowers => [
961 { field => 'firstname',
965 { field => 'initials',
969 { field => 'B_email',
972 after => 'B_zipcode',
975 field => 'streetnumber', # street number (hidden if streettable table is empty)
981 field => 'streettype', # street table, list builded from a system table
984 after => 'streetnumber',
991 field => 'B_streetnumber', # street number (hidden if streettable table is empty)
997 field => 'B_streettype', # street table, list builded from a system table
1000 after => 'B_streetnumber',
1003 field => 'phonepro',
1009 field => 'address2', # complement address
1015 field => 'emailpro',
1021 field => 'contactfirstname', # contact's firstname
1024 after => 'contactname',
1027 field => 'contacttitle', # contact's title
1030 after => 'contactfirstname',
1036 field => 'borrowernumber',
1038 null => 'NULL', # can be null when a borrower is deleted and the foreign key rule executed
1044 field => 'itemnumber',
1046 null => 'NULL', # can be null when a borrower is deleted and the foreign key rule executed
1052 field => 'branchcode',
1053 type => 'varchar(10)',
1060 field => 'issuedate',
1064 default => '0000-00-00',
1070 field => 'categorycode',
1071 type => 'varchar(10)',
1077 field => 'branchcode',
1078 type => 'varchar(10)',
1084 field => 'itemtype',
1085 type => 'varchar(10)',
1098 default => '0000-00-00',
1102 field => 'cutterextra',
1103 type => 'varchar(45)',
1110 field => 'homebranch',
1111 type => 'varchar(10)',
1118 field => 'holdingbranch',
1119 type => 'varchar(10)',
1127 type => 'varchar(10)',
1136 field => 'itemtype',
1137 type => 'varchar(10)',
1160 marc_subfield_structure => [
1162 field => 'defaultvalue',
1171 field => 'expirationdate',
1188 field => 'waitingdate',
1208 field => 'dateadded',
1209 type => 'timestamp',
1216 type => 'varchar(10)',
1220 field => 'itemtype',
1221 type => 'varchar(10)',
1225 systempreferences => [
1235 field => 'explanation',
1257 # { indexname => 'index detail'
1261 { indexname => 'PRIMARY',
1267 { indexname => 'booksellerid',
1268 content => 'booksellerid',
1272 { indexname => 'basketno',
1273 content => 'basketno',
1276 aqorderbreakdown => [
1277 { indexname => 'ordernumber',
1278 content => 'ordernumber',
1280 { indexname => 'bookfundid',
1281 content => 'bookfundid',
1285 { indexname => 'isbn',
1288 { indexname => 'publishercode',
1289 content => 'publishercode',
1294 indexname => 'branchcode',
1295 content => 'branchcode',
1299 branchrelations => [
1301 indexname => 'PRIMARY',
1302 content => 'categorycode',
1306 branchrelations => [
1307 { indexname => 'PRIMARY',
1308 content => 'branchcode,categorycode',
1311 { indexname => 'branchcode',
1312 content => 'branchcode',
1314 { indexname => 'categorycode',
1315 content => 'categorycode',
1319 { indexname => 'PRIMARY',
1320 content => 'currency',
1326 indexname => 'categorycode',
1327 content => 'categorycode',
1331 { indexname => 'homebranch',
1332 content => 'homebranch',
1334 { indexname => 'holdingbranch',
1335 content => 'holdingbranch',
1340 indexname => 'itemtype',
1341 content => 'itemtype',
1345 { indexname => 'shelfnumber',
1346 content => 'shelfnumber',
1348 { indexname => 'itemnumber',
1349 content => 'itemnumber',
1353 { indexname => 'PRIMARY',
1360 my %foreign_keys = (
1362 # { key => 'the key in table' (must be indexed)
1363 # foreigntable => 'the foreigntable name', # (the parent)
1364 # foreignkey => 'the foreign key column(s)' # (in the parent)
1365 # onUpdate => 'CASCADE|SET NULL|NO ACTION| RESTRICT',
1366 # onDelete => 'CASCADE|SET NULL|NO ACTION| RESTRICT',
1369 branchrelations => [
1370 { key => 'branchcode',
1371 foreigntable => 'branches',
1372 foreignkey => 'branchcode',
1373 onUpdate => 'CASCADE',
1374 onDelete => 'CASCADE',
1376 { key => 'categorycode',
1377 foreigntable => 'branchcategories',
1378 foreignkey => 'categorycode',
1379 onUpdate => 'CASCADE',
1380 onDelete => 'CASCADE',
1384 { key => 'shelfnumber',
1385 foreigntable => 'virtualshelf',
1386 foreignkey => 'shelfnumber',
1387 onUpdate => 'CASCADE',
1388 onDelete => 'CASCADE',
1390 { key => 'itemnumber',
1391 foreigntable => 'items',
1392 foreignkey => 'itemnumber',
1393 onUpdate => 'CASCADE',
1394 onDelete => 'CASCADE',
1397 # onDelete is RESTRICT on reference tables (branches, itemtype) as we don't want items to be
1398 # easily deleted, but branches/itemtype not too easy to empty...
1400 { key => 'biblionumber',
1401 foreigntable => 'biblio',
1402 foreignkey => 'biblionumber',
1403 onUpdate => 'CASCADE',
1404 onDelete => 'CASCADE',
1406 { key => 'itemtype',
1407 foreigntable => 'itemtypes',
1408 foreignkey => 'itemtype',
1409 onUpdate => 'CASCADE',
1410 onDelete => 'RESTRICT',
1414 { key => 'biblioitemnumber',
1415 foreigntable => 'biblioitems',
1416 foreignkey => 'biblioitemnumber',
1417 onUpdate => 'CASCADE',
1418 onDelete => 'CASCADE',
1420 { key => 'homebranch',
1421 foreigntable => 'branches',
1422 foreignkey => 'branchcode',
1423 onUpdate => 'CASCADE',
1424 onDelete => 'RESTRICT',
1426 { key => 'holdingbranch',
1427 foreigntable => 'branches',
1428 foreignkey => 'branchcode',
1429 onUpdate => 'CASCADE',
1430 onDelete => 'RESTRICT',
1434 { key => 'booksellerid',
1435 foreigntable => 'aqbooksellers',
1437 onUpdate => 'CASCADE',
1438 onDelete => 'RESTRICT',
1442 { key => 'basketno',
1443 foreigntable => 'aqbasket',
1444 foreignkey => 'basketno',
1445 onUpdate => 'CASCADE',
1446 onDelete => 'CASCADE',
1448 { key => 'biblionumber',
1449 foreigntable => 'biblio',
1450 foreignkey => 'biblionumber',
1451 onUpdate => 'SET NULL',
1452 onDelete => 'SET NULL',
1456 { key => 'listprice',
1457 foreigntable => 'currency',
1458 foreignkey => 'currency',
1459 onUpdate => 'CASCADE',
1460 onDelete => 'CASCADE',
1462 { key => 'invoiceprice',
1463 foreigntable => 'currency',
1464 foreignkey => 'currency',
1465 onUpdate => 'CASCADE',
1466 onDelete => 'CASCADE',
1469 aqorderbreakdown => [
1470 { key => 'ordernumber',
1471 foreigntable => 'aqorders',
1472 foreignkey => 'ordernumber',
1473 onUpdate => 'CASCADE',
1474 onDelete => 'CASCADE',
1476 { key => 'bookfundid',
1477 foreigntable => 'aqbookfund',
1478 foreignkey => 'bookfundid',
1479 onUpdate => 'CASCADE',
1480 onDelete => 'CASCADE',
1483 branchtransfers => [
1484 { key => 'frombranch',
1485 foreigntable => 'branches',
1486 foreignkey => 'branchcode',
1487 onUpdate => 'CASCADE',
1488 onDelete => 'CASCADE',
1490 { key => 'tobranch',
1491 foreigntable => 'branches',
1492 foreignkey => 'branchcode',
1493 onUpdate => 'CASCADE',
1494 onDelete => 'CASCADE',
1496 { key => 'itemnumber',
1497 foreigntable => 'items',
1498 foreignkey => 'itemnumber',
1499 onUpdate => 'CASCADE',
1500 onDelete => 'CASCADE',
1503 issues => [ # constraint is SET NULL : when a borrower or an item is deleted, we keep the issuing record
1505 { key => 'borrowernumber',
1506 foreigntable => 'borrowers',
1507 foreignkey => 'borrowernumber',
1508 onUpdate => 'SET NULL',
1509 onDelete => 'SET NULL',
1511 { key => 'itemnumber',
1512 foreigntable => 'items',
1513 foreignkey => 'itemnumber',
1514 onUpdate => 'SET NULL',
1515 onDelete => 'SET NULL',
1519 { key => 'borrowernumber',
1520 foreigntable => 'borrowers',
1521 foreignkey => 'borrowernumber',
1522 onUpdate => 'CASCADE',
1523 onDelete => 'CASCADE',
1525 { key => 'biblionumber',
1526 foreigntable => 'biblio',
1527 foreignkey => 'biblionumber',
1528 onUpdate => 'CASCADE',
1529 onDelete => 'CASCADE',
1531 { key => 'itemnumber',
1532 foreigntable => 'items',
1533 foreignkey => 'itemnumber',
1534 onUpdate => 'CASCADE',
1535 onDelete => 'CASCADE',
1537 { key => 'branchcode',
1538 foreigntable => 'branches',
1539 foreignkey => 'branchcode',
1540 onUpdate => 'CASCADE',
1541 onDelete => 'CASCADE',
1544 borrowers => [ # foreign keys are RESTRICT as we don't want to delete borrowers when a branch is deleted
1545 # but prevent deleting a branch as soon as it has 1 borrower !
1546 { key => 'categorycode',
1547 foreigntable => 'categories',
1548 foreignkey => 'categorycode',
1549 onUpdate => 'RESTRICT',
1550 onDelete => 'RESTRICT',
1552 { key => 'branchcode',
1553 foreigntable => 'branches',
1554 foreignkey => 'branchcode',
1555 onUpdate => 'RESTRICT',
1556 onDelete => 'RESTRICT',
1559 deletedborrowers => [ # foreign keys are RESTRICT as we don't want to delete borrowers when a branch is deleted
1560 # but prevent deleting a branch as soon as it has 1 borrower !
1561 { key => 'categorycode',
1562 foreigntable => 'categories',
1563 foreignkey => 'categorycode',
1564 onUpdate => 'RESTRICT',
1565 onDelete => 'RESTRICT',
1567 { key => 'branchcode',
1568 foreigntable => 'branches',
1569 foreignkey => 'branchcode',
1570 onUpdate => 'RESTRICT',
1571 onDelete => 'RESTRICT',
1575 { key => 'borrowernumber',
1576 foreigntable => 'borrowers',
1577 foreignkey => 'borrowernumber',
1578 onUpdate => 'CASCADE',
1579 onDelete => 'CASCADE',
1581 { key => 'itemnumber',
1582 foreigntable => 'items',
1583 foreignkey => 'itemnumber',
1584 onUpdate => 'SET NULL',
1585 onDelete => 'SET NULL',
1588 auth_tag_structure => [
1589 { key => 'authtypecode',
1590 foreigntable => 'auth_types',
1591 foreignkey => 'authtypecode',
1592 onUpdate => 'CASCADE',
1593 onDelete => 'CASCADE',
1596 # FIXME : don't constraint auth_*_table and auth_word, as they may be replaced by zebra
1601 my %column_change = (
1605 from => 'emailaddress',
1610 from => 'streetaddress',
1612 after => 'initials',
1615 from => 'faxnumber',
1620 from => 'textmessaging',
1626 to => 'contactnote',
1627 after => 'opacnote',
1630 from => 'physstreet',
1635 from => 'streetcity',
1637 after => 'B_address',
1650 from => 'homezipcode',
1657 after => 'B_zipcode',
1662 after => 'dateenrolled',
1665 from => 'guarantor',
1666 to => 'guarantorid',
1667 after => 'contactname',
1670 from => 'altrelationship',
1671 to => 'relationship',
1672 after => 'borrowernotes',
1676 deletedborrowers => [
1678 from => 'emailaddress',
1683 from => 'streetaddress',
1685 after => 'initials',
1688 from => 'faxnumber',
1693 from => 'textmessaging',
1699 to => 'contactnote',
1700 after => 'opacnote',
1703 from => 'physstreet',
1708 from => 'streetcity',
1710 after => 'B_address',
1723 from => 'homezipcode',
1730 after => 'B_zipcode',
1735 after => 'dateenrolled',
1738 from => 'guarantor',
1739 to => 'guarantorid',
1740 after => 'contactname',
1743 from => 'altrelationship',
1744 to => 'relationship',
1745 after => 'borrowernotes',
1751 # MOVE all tables TO UTF-8 and innoDB
1752 $sth = $dbh->prepare("show table status");
1754 while ( my $table = $sth->fetchrow_hashref ) {
1755 next if $table->{Name} eq 'marc_word';
1756 next if $table->{Name} eq 'marc_subfield_table';
1757 next if $table->{Name} eq 'auth_word';
1758 next if $table->{Name} eq 'auth_subfield_table';
1759 if ($table->{Engine} ne 'InnoDB') {
1760 print "moving $table->{Name} to InnoDB\n";
1761 $dbh->do("ALTER TABLE $table->{Name} TYPE = innodb");
1763 unless ($table->{Collation} =~ /^utf8/) {
1764 print "moving $table->{Name} to utf8\n";
1765 $dbh->do("ALTER TABLE $table->{Name} CONVERT TO CHARACTER SET utf8");
1766 $dbh->do("ALTER TABLE $table->{Name} DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci");
1767 # 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 !
1773 foreach my $table (keys %column_change) {
1774 $sth = $dbh->prepare("show columns from $table");
1777 while ( ( $column, $type, $null, $key, $default, $extra ) = $sth->fetchrow )
1779 $types{$column}->{type} ="$type";
1780 $types{$column}->{null} = "$null";
1781 $types{$column}->{key} = "$key";
1782 $types{$column}->{default} = "$default";
1783 $types{$column}->{extra} = "$extra";
1785 my $tablerows = $column_change{$table};
1786 foreach my $row ( @$tablerows ) {
1787 if ($types{$row->{from}}->{type}) {
1788 print "altering $table $row->{from} to $row->{to}\n";
1789 # ALTER TABLE `borrowers` CHANGE `faxnumber` `fax` TEXT CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL
1790 # alter table `borrowers` change `faxnumber` `fax` type text null after phone
1792 "alter table `$table` change `$row->{from}` `$row->{to}` $types{$row->{from}}->{type} ".
1793 ($types{$row->{from}}->{null} eq 'YES'?" NULL":" NOT NULL").
1794 ($types{$row->{from}}->{default}?" default ".$types{$row->{from}}->{default}:"").
1795 "$types{$row->{from}}->{extra} after $row->{after} ";
1802 # Enter here the field you want to delete from DB.
1803 # FIXME :: there is a %uselessfield before which seems doing the same things.
1804 my %fieldtodelete = (
1805 # tablename => [fieldname1,fieldname2,...]
1809 print "removing some unused fields...\n";
1810 foreach my $table ( keys %fieldtodelete ) {
1811 foreach my $field ( @{$fieldtodelete{$table}} ){
1812 print "removing ".$field." from ".$table;
1813 my $sth = $dbh->prepare("ALTER TABLE $table DROP $field");
1816 print "Error : $sth->errstr \n";
1821 # Enter here the line you want to remove from DB.
1822 my %linetodelete = (
1823 # table name => where clause.
1824 userflags => "bit = 8", # delete the 'reserveforself' flags
1828 #-------------------
1833 # Get version of MySQL database engine.
1834 my $mysqlversion = `mysqld --version`;
1835 $mysqlversion =~ /Ver (\S*) /;
1837 if ( $mysqlversion ge '3.23' ) {
1838 print "Could convert to MyISAM database tables...\n" unless $silent;
1841 #---------------------------------
1844 # Collect all tables into a list
1845 $sth = $dbh->prepare("show tables");
1847 while ( my ($table) = $sth->fetchrow ) {
1848 $existingtables{$table} = 1;
1852 # Now add any missing tables
1853 foreach $table ( keys %requiretables ) {
1854 unless ( $existingtables{$table} ) {
1855 print "Adding $table table...\n" unless $silent;
1856 my $sth = $dbh->prepare("create table $table $requiretables{$table} ENGINE=InnoDB DEFAULT CHARSET=utf8");
1859 print "Error : $sth->errstr \n";
1865 #---------------------------------
1868 foreach $table ( keys %requirefields ) {
1869 print "Check table $table\n" if $debug and not $silent;
1870 $sth = $dbh->prepare("show columns from $table");
1873 while ( ( $column, $type, $null, $key, $default, $extra ) = $sth->fetchrow )
1875 $types{$column} = $type;
1877 foreach $column ( keys %{ $requirefields{$table} } ) {
1878 print " Check column $column [$types{$column}]\n" if $debug and not $silent;
1879 if ( !$types{$column} ) {
1881 # column doesn't exist
1882 print "Adding $column field to $table table...\n" unless $silent;
1883 $query = "alter table $table
1884 add column $column " . $requirefields{$table}->{$column};
1885 print "Execute: $query\n" if $debug;
1886 my $sti = $dbh->prepare($query);
1889 print "**Error : $sti->errstr \n";
1896 foreach $table ( keys %fielddefinitions ) {
1897 print "Check table $table\n" if $debug;
1898 $sth = $dbh->prepare("show columns from $table");
1901 while ( ( $column, $type, $null, $key, $default, $extra ) = $sth->fetchrow )
1903 $definitions->{$column}->{type} = $type;
1904 $definitions->{$column}->{null} = $null;
1905 $definitions->{$column}->{null} = 'NULL' if $null eq 'YES';
1906 $definitions->{$column}->{key} = $key;
1907 $definitions->{$column}->{default} = $default;
1908 $definitions->{$column}->{extra} = $extra;
1910 my $fieldrow = $fielddefinitions{$table};
1911 foreach my $row (@$fieldrow) {
1912 my $field = $row->{field};
1913 my $type = $row->{type};
1914 my $null = $row->{null};
1915 # $null = 'YES' if $row->{null} eq 'NULL';
1916 my $key = $row->{key};
1917 my $default = $row->{default};
1918 # $default="''" unless $default;
1919 my $extra = $row->{extra};
1920 my $def = $definitions->{$field};
1921 my $after = ($row->{after}?" after ".$row->{after}:"");
1923 unless ( $type eq $def->{type}
1924 && $null eq $def->{null}
1925 && $key eq $def->{key}
1926 && $extra eq $def->{extra} )
1928 if ( $null eq '' ) {
1931 if ( $key eq 'PRI' ) {
1932 $key = 'PRIMARY KEY';
1934 unless ( $extra eq 'auto_increment' ) {
1938 # if it's a new column use "add", if it's an old one, use "change".
1940 if ($definitions->{$field}->{type}) {
1941 $action="change $field"
1945 # if it's a primary key, drop the previous pk, before altering the table
1946 print " alter or create $field in $table\n" unless $silent;
1948 if ($key ne 'PRIMARY KEY') {
1949 # warn "alter table $table $action $field $type $null $key $extra default $default $after";
1950 $query = "alter table $table $action $field $type $null $key $extra ".($default?"default ".$dbh->quote($default):"")." $after";
1952 # warn "alter table $table drop primary key, $action $field $type $null $key $extra default $default $after";
1953 # something strange : for indexes UNIQUE, they are reported as primary key here.
1954 # but if you try to run with drop primary key, it fails.
1955 # thus, we run the query twice, one will fail, one will succeed.
1957 $query="alter table $table drop primary key, $action $field $type $null $key $extra ".($default?"default ".$dbh->quote($default):"")." $after";
1958 $query="alter table $table $action $field $type $null $key $extra ".($default?"default ".$dbh->quote($default):"")." $after";
1965 print "removing some unused data...\n";
1966 foreach my $table ( keys %linetodelete ) {
1967 foreach my $where ( @{linetodelete{$table}} ){
1968 print "DELETE FROM ".$table." where ".$where;
1970 my $sth = $dbh->prepare("DELETE FROM $table where $where");
1973 print "Error : $sth->errstr \n";
1978 # Populate tables with required data
1980 # synch table and deletedtable.
1981 foreach my $table (('borrowers','items','biblio','biblioitems')) {
1982 my %deletedborrowers;
1983 print "synch'ing $table and deleted$table\n";
1984 $sth = $dbh->prepare("show columns from deleted$table");
1986 while ( my ( $column, $type, $null, $key, $default, $extra ) = $sth->fetchrow ) {
1987 $deletedborrowers{$column}=1;
1989 $sth = $dbh->prepare("show columns from $table");
1992 while ( my ( $column, $type, $null, $key, $default, $extra ) = $sth->fetchrow ) {
1993 unless ($deletedborrowers{$column}) {
1994 my $newcol="alter table deleted$table add $column $type";
1995 if ($null eq 'YES') {
1996 $newcol .= " NULL ";
1998 $newcol .= " NOT NULL ";
2000 $newcol .= "default ".$dbh->quote($default) if $default;
2001 $newcol .= " after $previous" if $previous;
2003 print "creating column $column\n";
2009 # update publisheddate
2011 $sth = $dbh->prepare("select count(*) from serial where publisheddate is NULL");
2013 my ($emptypublished) = $sth->fetchrow;
2014 if ($emptypublished) {
2015 print "Updating publisheddate\n";
2016 $dbh->do("update serial set publisheddate=planneddate where publisheddate is NULL");
2018 # Why are we setting publisheddate = planneddate ?? if we don't have the data, we don't know it.
2019 # now, let's get rid of 000-00-00's.
2021 $dbh->do("update serial set publisheddate=NULL where publisheddate = 0");
2022 $dbh->do("update subscription set firstacquidate=startdate where firstacquidate = 0");
2024 foreach my $table ( keys %tabledata ) {
2025 print "Checking for data required in table $table...\n" unless $silent;
2026 my $tablerows = $tabledata{$table};
2027 foreach my $row (@$tablerows) {
2028 my $uniquefieldrequired = $row->{uniquefieldrequired};
2029 my $uniquevalue = $row->{$uniquefieldrequired};
2030 my $forceupdate = $row->{forceupdate};
2033 "select $uniquefieldrequired from $table where $uniquefieldrequired=?"
2035 $sth->execute($uniquevalue);
2037 foreach my $field (keys %$forceupdate) {
2038 if ($forceupdate->{$field}) {
2039 my $sth=$dbh->prepare("update systempreferences set $field=? where $uniquefieldrequired=?");
2040 $sth->execute($row->{$field}, $uniquevalue);
2044 print "Adding row to $table: " unless $silent;
2048 foreach my $field ( keys %$row ) {
2049 next if $field eq 'uniquefieldrequired';
2050 next if $field eq 'forceupdate';
2051 my $value = $row->{$field};
2052 push @values, $value;
2053 print " $field => $value" unless $silent;
2054 $fieldlist .= "$field,";
2055 $placeholders .= "?,";
2057 print "\n" unless $silent;
2058 $fieldlist =~ s/,$//;
2059 $placeholders =~ s/,$//;
2060 print "insert into $table ($fieldlist) values ($placeholders)";
2063 "insert into $table ($fieldlist) values ($placeholders)");
2064 $sth->execute(@values);
2070 # check indexes and create them when needed
2072 print "Checking for index required...\n" unless $silent;
2073 foreach my $table ( keys %indexes ) {
2075 # read all indexes from $table
2077 $sth = $dbh->prepare("show index from $table");
2079 my %existingindexes;
2080 while ( my ( $table, $non_unique, $key_name, $Seq_in_index, $Column_name, $Collation, $cardinality, $sub_part, $Packed, $comment ) = $sth->fetchrow ) {
2081 $existingindexes{$key_name} = 1;
2083 # read indexes to check
2084 my $tablerows = $indexes{$table};
2085 foreach my $row (@$tablerows) {
2086 my $key_name=$row->{indexname};
2087 if ($existingindexes{$key_name} eq 1) {
2088 # print "$key_name existing";
2090 print "\tCreating index $key_name in $table\n";
2092 if ($row->{indexname} eq 'PRIMARY') {
2093 $sql = "alter table $table ADD PRIMARY KEY ($row->{content})";
2095 $sql = "alter table $table ADD INDEX $key_name ($row->{content}) $row->{type}";
2098 print "Error $sql : $dbh->err \n" if $dbh->err;
2104 # check foreign keys and create them when needed
2106 print "Checking for foreign keys required...\n" unless $silent;
2107 foreach my $table ( keys %foreign_keys ) {
2109 # read all indexes from $table
2111 $sth = $dbh->prepare("show table status like '$table'");
2113 my $stat = $sth->fetchrow_hashref;
2114 # read indexes to check
2115 my $tablerows = $foreign_keys{$table};
2116 foreach my $row (@$tablerows) {
2117 my $foreign_table=$row->{foreigntable};
2118 if ($stat->{'Comment'} =~/$foreign_table/) {
2119 # print "$foreign_table existing\n";
2121 print "\tCreating foreign key $foreign_table in $table\n";
2122 # first, drop any orphan value in child table
2123 if ($row->{onDelete} ne "RESTRICT") {
2124 my $sql = "delete from $table where $row->{key} not in (select $row->{foreignkey} from $row->{foreigntable})";
2126 print "SQL ERROR: $sql : $dbh->err \n" if $dbh->err;
2128 my $sql="alter table $table ADD FOREIGN KEY $row->{key} ($row->{key}) REFERENCES $row->{foreigntable} ($row->{foreignkey})";
2129 $sql .= " on update ".$row->{onUpdate} if $row->{onUpdate};
2130 $sql .= " on delete ".$row->{onDelete} if $row->{onDelete};
2133 print "====================
2134 An error occured during :
2136 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).
2137 You can find those values with select
2138 \t$table.* from $table where $row->{key} not in (select $row->{foreignkey} from $row->{foreigntable})
2139 ====================\n
2145 # now drop useless tables
2146 foreach $table ( @TableToDelete ) {
2147 if ( $existingtables{$table} ) {
2148 print "Dropping unused table $table\n" if $debug and not $silent;
2149 $dbh->do("drop table $table");
2151 print "Error : $dbh->errstr \n";
2160 # create frameworkcode row in biblio table & fill it with marc_biblio.frameworkcode.
2163 # 1st, get how many biblio we will have to do...
2164 $sth = $dbh->prepare('select count(*) from marc_biblio');
2166 my ($totaltodo) = $sth->fetchrow;
2168 $sth = $dbh->prepare("show columns from biblio");
2171 my $bibliofwexist=0;
2172 while ( ( $column, $type, $null, $key, $default, $extra ) = $sth->fetchrow ){
2173 $bibliofwexist=1 if $column eq 'frameworkcode';
2175 unless ($bibliofwexist) {
2176 print "moving biblioframework to biblio table\n";
2177 $dbh->do('ALTER TABLE `biblio` ADD `frameworkcode` VARCHAR( 4 ) NOT NULL AFTER `biblionumber`');
2178 $sth = $dbh->prepare('select biblionumber,frameworkcode from marc_biblio');
2180 my $sth_update = $dbh->prepare('update biblio set frameworkcode=? where biblionumber=?');
2182 while (my ($biblionumber,$frameworkcode) = $sth->fetchrow) {
2183 $sth_update->execute($frameworkcode,$biblionumber);
2185 print "\r$totaldone / $totaltodo" unless ($totaldone % 100);
2190 # at last, remove useless fields
2191 foreach $table ( keys %uselessfields ) {
2192 my @fields = split /,/,$uselessfields{$table};
2195 foreach my $fieldtodrop (@fields) {
2196 $fieldtodrop =~ s/\t//g;
2197 $fieldtodrop =~ s/\n//g;
2199 $sth = $dbh->prepare("show columns from $table");
2201 while ( my ( $column, $type, $null, $key, $default, $extra ) = $sth->fetchrow )
2203 $exists =1 if ($column eq $fieldtodrop);
2206 print "deleting $fieldtodrop field in $table...\n" unless $silent;
2207 my $sth = $dbh->prepare("alter table $table drop $fieldtodrop");
2214 # Changing aqbookfund's primary key
2216 $sth=$dbh->prepare("ALTER TABLE `aqbookfund` DROP PRIMARY KEY , ADD PRIMARY KEY ( `bookfundid` , `branchcode` ) ;");
2220 print "upgrade to Koha 3.0 done\n";
2221 SetVersion ($DBversion);
2224 =item TransformToNum
2226 Transform the Koha version from a 4 parts string
2227 to a number, with just 1 .
2231 sub TransformToNum {
2232 my $version = shift;
2233 # remove the 3 last . to have a Perl number
2234 $version =~ s/(.*\..*)\.(.*)\.(.*)/$1$2$3/;
2239 set the DBversion in the systempreferences
2243 my $kohaversion = TransformToNum(shift);
2244 if (C4::Context->preference('Version')) {
2245 my $finish=$dbh->prepare("UPDATE systempreferences SET value=? WHERE variable='Version'");
2246 $finish->execute($kohaversion);
2248 my $finish=$dbh->prepare("INSERT into systempreferences (variable,value,explanation) values ('Version',?,'The Koha database version. WARNING: Do not change this value manually, it is maintained by the webinstaller')");
2249 $finish->execute($kohaversion);
2254 # Revision 1.172 2007/07/19 10:21:22 hdl