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 if (C4::Context->preference("Version") < TransformToNum($DBversion) ) {
52 # Tables to add if they don't exist
55 `timestamp` TIMESTAMP NOT NULL ,
56 `user` INT( 11 ) NOT NULL default '0' ,
57 `module` TEXT default '',
58 `action` TEXT default '' ,
59 `object` INT(11) NULL ,
60 `info` TEXT default '' ,
61 PRIMARY KEY ( `timestamp` , `user` )
64 module varchar(20) NOT NULL default '',
65 code varchar(20) NOT NULL default '',
66 name varchar(100) NOT NULL default '',
67 title varchar(200) NOT NULL default '',
69 PRIMARY KEY (module,code)
72 alertid int(11) NOT NULL auto_increment,
73 borrowernumber int(11) NOT NULL default '0',
74 type varchar(10) NOT NULL default '',
75 externalid varchar(20) NOT NULL default '',
76 PRIMARY KEY (alertid),
77 KEY borrowernumber (borrowernumber),
78 KEY type (type,externalid)
81 `idnew` int(10) unsigned NOT NULL auto_increment,
82 `title` varchar(250) NOT NULL default '',
84 `lang` varchar(4) NOT NULL default '',
85 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP,
88 repeatable_holidays => "(
89 `id` int(11) NOT NULL auto_increment,
90 `branchcode` varchar(4) NOT NULL default '',
91 `weekday` smallint(6) default NULL,
92 `day` smallint(6) default NULL,
93 `month` smallint(6) default NULL,
94 `title` varchar(50) NOT NULL default '',
95 `description` text NOT NULL,
98 special_holidays => "(
99 `id` int(11) NOT NULL auto_increment,
100 `branchcode` varchar(4) NOT NULL default '',
101 `day` smallint(6) NOT NULL default '0',
102 `month` smallint(6) NOT NULL default '0',
103 `year` smallint(6) NOT NULL default '0',
104 `isexception` smallint(1) NOT NULL default '1',
105 `title` varchar(50) NOT NULL default '',
106 `description` text NOT NULL,
109 overduerules =>"(`branchcode` varchar(255) NOT NULL default '',
110 `categorycode` char(2) NOT NULL default '',
111 `delay1` int(4) default '0',
112 `letter1` varchar(20) default NULL,
113 `debarred1` char(1) default '0',
114 `delay2` int(4) default '0',
115 `debarred2` char(1) default '0',
116 `letter2` varchar(20) default NULL,
117 `delay3` int(4) default '0',
118 `letter3` varchar(20) default NULL,
119 `debarred3` int(1) default '0',
120 PRIMARY KEY (`branchcode`,`categorycode`)
122 cities => "(`cityid` int auto_increment,
123 `city_name` char(100) NOT NULL,
124 `city_zipcode` char(20),
125 PRIMARY KEY (`cityid`)
127 roadtype => "(`roadtypeid` int auto_increment,
128 `road_type` char(100) NOT NULL,
129 PRIMARY KEY (`roadtypeid`)
133 labelid int(11) NOT NULL auto_increment,
134 itemnumber varchar(100) NOT NULL default '',
135 timestamp timestamp(14) NOT NULL,
136 PRIMARY KEY (labelid)
140 id int(4) NOT NULL auto_increment,
141 barcodetype char(100) default '',
142 title tinyint(1) default '0',
143 isbn tinyint(1) default '0',
144 itemtype tinyint(1) default '0',
145 barcode tinyint(1) default '0',
146 dewey tinyint(1) default '0',
147 class tinyint(1) default '0',
148 author tinyint(1) default '0',
149 papertype char(100) default '',
150 startrow int(2) default NULL,
154 reviewid integer NOT NULL auto_increment,
155 borrowernumber integer,
156 biblionumber integer,
159 datereviewed datetime,
160 PRIMARY KEY (reviewid)
162 subscriptionroutinglist=>"(
163 routingid integer NOT NULL auto_increment,
164 borrowernumber integer,
166 subscriptionid integer,
167 PRIMARY KEY (routingid)
171 notify_id int(11) NOT NULL default '0',
172 `borrowernumber` int(11) NOT NULL default '0',
173 `itemnumber` int(11) NOT NULL default '0',
174 `notify_date` date NOT NULL default '0000-00-00',
175 `notify_send_date` date default NULL,
176 `notify_level` int(1) NOT NULL default '0',
177 `method` varchar(20) NOT NULL default ''
181 `charge_id` varchar(5) NOT NULL default '',
182 `description` text NOT NULL,
183 `amount` decimal(28,6) NOT NULL default '0.000000',
184 `min` int(4) NOT NULL default '0',
185 `max` int(4) NOT NULL default '0',
186 `level` int(1) NOT NULL default '0',
187 PRIMARY KEY (`charge_id`)
190 `entry` varchar(255) NOT NULL default '',
191 `weight` bigint(20) NOT NULL default '0',
192 PRIMARY KEY (`entry`)
196 `id` int NOT NULL auto_increment,
197 `biblio_auth_number` int NOT NULL,
198 `operation` char(20) NOT NULL,
199 `server` char(20) NOT NULL ,
201 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci AUTO_INCREMENT=1",
205 my %requirefields = (
206 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 \'\''},
207 itemtypes => { 'imageurl' => 'char(200) NULL'},
208 aqbookfund => { 'branchcode' => 'varchar(4) NULL'},
209 aqbudget => { 'branchcode' => 'varchar(4) NULL'},
210 auth_header => { 'marc' => 'BLOB NOT NULL', 'linkid' => 'BIGINT(20) NULL'},
211 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'},
212 marc_breeding => { 'isbn' => 'varchar(13) NOT NULL'},
213 serial =>{ 'publisheddate' => 'date', 'claimdate' => 'date', 'itemnumber'=>'text NULL','routingnotes'=>'text NULL',},
214 statistics => { 'associatedborrower' => 'integer'},
215 z3950servers =>{ "name" =>"text", "description" => "text NOT NULL",
216 "position" =>"enum('primary','secondary','') NOT NULL default 'primary'", "icon" =>"text",
217 "type" =>"enum('zed','opensearch') NOT NULL default 'zed'",
219 issues =>{ 'issuedate'=>"date NOT NULL default '0000-00-00'", },
221 # tablename => { 'field' => 'fieldtype' },
224 # Enter here the table to delete.
225 my @TableToDelete = qw(
232 my %uselessfields = (
233 # tablename => "field1,field2",
234 borrowers => "suburb,altstreetaddress,altsuburb,altcity,studentnumber,school,area,preferredcont,altcp",
235 deletedborrowers=> "suburb,altstreetaddress,altsuburb,altcity,studentnumber,school,area,preferredcont,altcp",
237 # the other hash contains other actions that can't be done elsewhere. they are done
238 # either BEFORE of AFTER everything else, depending on "when" entry (default => AFTER)
240 # The tabledata hash contains data that should be in the tables.
241 # The uniquefieldrequired hash entry is used to determine which (if any) fields
242 # must not exist in the table for this row to be inserted. If the
243 # uniquefieldrequired entry is already in the table, the existing data is not
244 # modified, unless the forceupdate hash entry is also set. Fields in the
245 # anonymous "forceupdate" hash will be forced to be updated to the default
246 # values given in the %tabledata hash.
250 # { uniquefielrequired => 'fieldname', # the primary key in the table
251 # fieldname => fieldvalue,
252 # fieldname2 => fieldvalue2,
255 systempreferences => [
257 uniquefieldrequired => 'variable',
258 variable => 'useDaysMode',
260 forceupdate => { 'explanation' => 1,
262 explanation => 'How to calculate return dates : Calendar means holidays will be controled, Days means the return date don\'t depend on holidays',
264 options => 'Calendar|Days'
267 uniquefieldrequired => 'variable',
268 variable => 'DebugLevel',
270 forceupdate => { 'explanation' => 1,
272 explanation => 'Set the level of error info sent to the browser. 0=none, 1=some, 2=most',
277 uniquefieldrequired => 'variable',
278 variable => 'BorrowersTitles',
279 value => 'Mr|Mrs|Miss|Ms',
280 forceupdate => { 'explanation' => 1,
282 explanation => 'List all Titles for borrowers',
286 uniquefieldrequired => 'variable',
287 variable => 'BorrowerMandatoryField',
288 value => 'cardnumber|surname|address',
289 forceupdate => { 'explanation' => 1,
291 explanation => 'List all mandatory fields for borrowers',
295 uniquefieldrequired => 'variable',
296 variable => 'borrowerRelationship',
297 value => 'father|mother,grand-mother',
298 forceupdate => { 'explanation' => 1,
300 explanation => 'The relationships between a guarantor & a guarantee (separated by | or ,)',
304 uniquefieldrequired => 'variable',
305 variable => 'ReservesMaxPickUpDelay',
307 forceupdate => { 'explanation' => 1,
309 explanation => 'Maximum delay to pick up a reserved document',
313 uniquefieldrequired => 'variable',
314 variable => 'TransfersMaxDaysWarning',
316 forceupdate => { 'explanation' => 1,
318 explanation => 'Max delay before considering the transfer has potentialy a problem',
322 uniquefieldrequired => 'variable',
323 variable => 'memberofinstitution',
325 forceupdate => { 'explanation' => 1,
327 explanation => 'Are your patrons members of institutions',
331 uniquefieldrequired => 'variable',
332 variable => 'ReadingHistory',
334 forceupdate => { 'explanation' => 1,
336 explanation => 'Allow reading record info retrievable from issues and oldissues tables',
340 uniquefieldrequired => 'variable',
341 variable => 'IssuingInProcess',
343 forceupdate => { 'explanation' => 1,
345 explanation => 'Allow no debt alert if the patron is issuing item that accumulate debt',
349 uniquefieldrequired => 'variable',
350 variable => 'AutomaticItemReturn',
352 forceupdate => { 'explanation' => 1,
354 explanation => 'This Variable allow or not to return automaticly to his homebranch',
358 uniquefieldrequired => 'variable',
359 variable => 'reviewson',
361 forceupdate => { 'explanation' => 1,
363 explanation => 'Allows patrons to submit reviews from the opac',
367 uniquefieldrequired => 'variable',
368 variable => 'intranet_includes',
370 forceupdate => { 'explanation' => 1,
372 explanation => 'The includes directory you want for specific look of Koha (includes or includes_npl for example)',
376 uniquefieldrequired => 'variable',
377 variable => 'AutoLocation',
379 forceupdate => { 'explanation' => 1,
381 explanation => 'switch to activate or not Autolocation, if Yes, the Librarian can\'t change his location, it\'s defined by branchip',
385 uniquefieldrequired => 'variable',
386 variable => 'serialsadditems',
392 explanation => 'If set, a new item will be automatically added when receiving an issue',
396 uniquefieldrequired => 'variable',
397 variable => 'expandedSearchOption',
403 explanation => 'search among marc field',
407 uniquefieldrequired => 'variable',
408 variable => 'RequestOnOpac',
410 forceupdate => { 'explanation' => 1,
412 explanation => 'option to allow reserves on opac',
416 uniquefieldrequired => 'variable',
417 variable => 'OpacCloud',
419 forceupdate => { 'explanation' => 1,
421 explanation => 'Enable / Disable cloud link on OPAC (Require to run misc/cronjobs/build_browser_and_cloud.pl on the server)',
425 uniquefieldrequired => 'variable',
426 variable => 'OpacBrowser',
428 forceupdate => { 'explanation' => 1,
430 explanation => 'Enable/Disable browser link on OPAC (Require to run misc/cronjobs/build_browser_and_cloud.pl on the server)',
434 uniquefieldrequired => 'variable',
435 variable => 'OpacTopissue',
437 forceupdate => { 'explanation' => 1,
439 explanation => 'Enable / Disable the top issue link on OPAC',
443 uniquefieldrequired => 'variable',
444 variable => 'OpacAuthorities',
446 forceupdate => { 'explanation' => 1,
448 explanation => 'Enable / Disable the search authority link on OPAC',
452 uniquefieldrequired => 'variable',
453 variable => 'CataloguingLog',
455 forceupdate => {'explanation' => 1, 'type' => 1},
456 explanation => 'Active this if you want to log cataloguing action.',
460 uniquefieldrequired => 'variable',
461 variable => 'BorrowersLog',
463 forceupdate => {'explanation' => 1, 'type' => 1},
464 explanation => 'Active this if you want to log borrowers edition/creation/deletion...',
468 uniquefieldrequired => 'variable',
469 variable => 'SubscriptionLog',
471 forceupdate => {'explanation' => 1, 'type' => 1},
472 explanation => 'Active this if you want to log Subscription action',
476 uniquefieldrequired => 'variable',
477 variable => 'IssueLog',
479 forceupdate => {'explanation' => 1, 'type' => 1},
480 explanation => 'Active this if you want to log issue.',
484 uniquefieldrequired => 'variable',
485 variable => 'ReturnLog',
487 forceupdate => {'explanation' => 1, 'type' => 1},
488 explanation => 'Active this if you want to log the circulation return',
492 uniquefieldrequired => 'variable',
493 variable => 'Version',
495 forceupdate => {'explanation' => 1, 'type' => 1},
496 explanation => 'Koha Version',
500 uniquefieldrequired => 'variable',
501 variable => 'LetterLog',
503 forceupdate => {'explanation' => 1, 'type' => 1},
504 explanation => 'Active this if you want to log all the letter sent',
508 uniquefieldrequired => 'variable',
509 variable => 'FinesLog',
511 forceupdate => {'explanation' => 1, 'type' => 1},
512 explanation => 'Active this if you want to log fines',
516 uniquefieldrequired => 'variable',
517 variable => 'NoZebra',
519 forceupdate => {'explanation' => 1, 'type' => 1},
520 explanation => 'Active this if you want NOT to use zebra (large libraries should avoid this parameters)',
524 uniquefieldrequired => 'variable',
525 variable => 'NoZebraIndexes',
527 forceupdate => {'explanation' => 1, 'type' => 1},
528 explanation => "Enter a specific hash for NoZebra indexes. Enter : 'indexname' => '100a,245a,500*','index2' => '...'",
532 uniquefieldrequired => 'variable',
533 variable => 'uppercasesurnames',
535 forceupdate => {'explanation' => 1, 'type' => 1},
536 explanation => "Force Surnames to be uppercase",
542 uniquefieldrequired => 'bit',
544 flag => 'editauthorities',
545 flagdesc => 'allow to edit authorities',
549 uniquefieldrequired => 'bit',
552 flagdesc => 'allow to manage serials subscriptions',
556 uniquefieldrequired => 'bit',
559 flagdesc => 'allow to access to the reports module',
563 authorised_values => [
565 uniquefieldrequired => 'id',
566 category => 'SUGGEST',
567 authorised_value => 'Not enough budget',
568 lib => 'This book it too much expensive',
573 my %fielddefinitions = (
575 # { field => 'fieldname',
576 # type => 'fieldtype',
584 field => 'booksellerid',
594 field => 'bookfundid',
595 type => 'varchar(5)',
610 extra => 'auto_increment',
613 field => 'listprice',
614 type => 'varchar(10)',
621 field => 'invoiceprice',
622 type => 'varchar(10)',
629 field => 'invoicedisc',
630 type => 'float(6,4)',
641 field => 'bookfundid',
642 type => 'varchar(5)',
652 field => 'notify_id',
660 field => 'notify_level',
668 field => 'accountno',
669 type => 'smallint(6)',
676 field => 'description',
677 type => 'mediumtext',
682 type => 'mediumtext',
689 { field => 'firstname',
693 { field => 'initials',
697 { field => 'B_email',
700 after => 'B_zipcode',
703 field => 'streetnumber', # street number (hidden if streettable table is empty)
709 field => 'streettype', # street table, list builded from a system table
712 after => 'streetnumber',
719 field => 'B_streetnumber', # street number (hidden if streettable table is empty)
725 field => 'B_streettype', # street table, list builded from a system table
728 after => 'B_streetnumber',
737 field => 'address2', # complement address
749 field => 'contactfirstname', # contact's firstname
752 after => 'contactname',
755 field => 'contacttitle', # contact's title
758 after => 'contactfirstname',
761 field => 'branchcode',
762 type => 'varchar(10)',
768 field => 'categorycode',
769 type => 'varchar(10)',
779 type => 'varchar(25)',
787 type => 'varchar(4)',
795 type => 'varchar(30)',
801 field => 'publicationyear',
808 field => 'collectiontitle',
809 type => 'mediumtext',
815 field => 'collectionissn',
816 type => 'mediumtext',
822 field => 'collectionvolume',
823 type => 'mediumtext',
829 field => 'editionstatement',
836 field => 'editionresponsability',
844 deletedbiblioitems => [
847 type => 'varchar(30)',
856 type => 'varchar(15)',
863 field => 'branchprinter',
864 type => 'varchar(100)',
871 field => 'branchcode',
872 type => 'varchar(10)',
880 field => 'frombranch',
881 type => 'VARCHAR(10)',
889 type => 'VARCHAR(10)',
898 field => 'category_type',
906 field => 'categorycode',
907 type => 'varchar(10)',
915 deletedborrowers => [
916 { field => 'firstname',
920 { field => 'initials',
924 { field => 'B_email',
927 after => 'B_zipcode',
930 field => 'streetnumber', # street number (hidden if streettable table is empty)
936 field => 'streettype', # street table, list builded from a system table
939 after => 'streetnumber',
946 field => 'B_streetnumber', # street number (hidden if streettable table is empty)
952 field => 'B_streettype', # street table, list builded from a system table
955 after => 'B_streetnumber',
964 field => 'address2', # complement address
976 field => 'contactfirstname', # contact's firstname
979 after => 'contactname',
982 field => 'contacttitle', # contact's title
985 after => 'contactfirstname',
991 field => 'borrowernumber',
993 null => 'NULL', # can be null when a borrower is deleted and the foreign key rule executed
999 field => 'itemnumber',
1001 null => 'NULL', # can be null when a borrower is deleted and the foreign key rule executed
1007 field => 'branchcode',
1008 type => 'varchar(10)',
1015 field => 'issuedate',
1019 default => '0000-00-00',
1030 default => '0000-00-00',
1034 field => 'cutterextra',
1035 type => 'varchar(45)',
1042 field => 'homebranch',
1043 type => 'varchar(10)',
1050 field => 'holdingbranch',
1051 type => 'varchar(10)',
1059 type => 'varchar(10)',
1068 field => 'itemtype',
1069 type => 'varchar(10)',
1092 marc_subfield_structure => [
1094 field => 'defaultvalue',
1103 field => 'expirationdate',
1120 field => 'waitingdate',
1140 field => 'dateadded',
1141 type => 'timestamp',
1145 systempreferences => [
1155 field => 'explanation',
1177 # { indexname => 'index detail'
1181 { indexname => 'PRIMARY',
1187 { indexname => 'booksellerid',
1188 content => 'booksellerid',
1192 { indexname => 'basketno',
1193 content => 'basketno',
1196 aqorderbreakdown => [
1197 { indexname => 'ordernumber',
1198 content => 'ordernumber',
1200 { indexname => 'bookfundid',
1201 content => 'bookfundid',
1205 { indexname => 'isbn',
1208 { indexname => 'publishercode',
1209 content => 'publishercode',
1214 indexname => 'branchcode',
1215 content => 'branchcode',
1219 branchrelations => [
1221 indexname => 'PRIMARY',
1222 content => 'categorycode',
1226 branchrelations => [
1227 { indexname => 'PRIMARY',
1228 content => 'branchcode,categorycode',
1231 { indexname => 'branchcode',
1232 content => 'branchcode',
1234 { indexname => 'categorycode',
1235 content => 'categorycode',
1239 { indexname => 'PRIMARY',
1240 content => 'currency',
1246 indexname => 'categorycode',
1247 content => 'categorycode',
1251 { indexname => 'homebranch',
1252 content => 'homebranch',
1254 { indexname => 'holdingbranch',
1255 content => 'holdingbranch',
1260 indexname => 'itemtype',
1261 content => 'itemtype',
1265 { indexname => 'shelfnumber',
1266 content => 'shelfnumber',
1268 { indexname => 'itemnumber',
1269 content => 'itemnumber',
1273 { indexname => 'PRIMARY',
1280 my %foreign_keys = (
1282 # { key => 'the key in table' (must be indexed)
1283 # foreigntable => 'the foreigntable name', # (the parent)
1284 # foreignkey => 'the foreign key column(s)' # (in the parent)
1285 # onUpdate => 'CASCADE|SET NULL|NO ACTION| RESTRICT',
1286 # onDelete => 'CASCADE|SET NULL|NO ACTION| RESTRICT',
1289 branchrelations => [
1290 { key => 'branchcode',
1291 foreigntable => 'branches',
1292 foreignkey => 'branchcode',
1293 onUpdate => 'CASCADE',
1294 onDelete => 'CASCADE',
1296 { key => 'categorycode',
1297 foreigntable => 'branchcategories',
1298 foreignkey => 'categorycode',
1299 onUpdate => 'CASCADE',
1300 onDelete => 'CASCADE',
1304 { key => 'shelfnumber',
1305 foreigntable => 'virtualshelf',
1306 foreignkey => 'shelfnumber',
1307 onUpdate => 'CASCADE',
1308 onDelete => 'CASCADE',
1310 { key => 'itemnumber',
1311 foreigntable => 'items',
1312 foreignkey => 'itemnumber',
1313 onUpdate => 'CASCADE',
1314 onDelete => 'CASCADE',
1317 # onDelete is RESTRICT on reference tables (branches, itemtype) as we don't want items to be
1318 # easily deleted, but branches/itemtype not too easy to empty...
1320 { key => 'biblionumber',
1321 foreigntable => 'biblio',
1322 foreignkey => 'biblionumber',
1323 onUpdate => 'CASCADE',
1324 onDelete => 'CASCADE',
1326 { key => 'itemtype',
1327 foreigntable => 'itemtypes',
1328 foreignkey => 'itemtype',
1329 onUpdate => 'CASCADE',
1330 onDelete => 'RESTRICT',
1334 { key => 'biblioitemnumber',
1335 foreigntable => 'biblioitems',
1336 foreignkey => 'biblioitemnumber',
1337 onUpdate => 'CASCADE',
1338 onDelete => 'CASCADE',
1340 { key => 'homebranch',
1341 foreigntable => 'branches',
1342 foreignkey => 'branchcode',
1343 onUpdate => 'CASCADE',
1344 onDelete => 'RESTRICT',
1346 { key => 'holdingbranch',
1347 foreigntable => 'branches',
1348 foreignkey => 'branchcode',
1349 onUpdate => 'CASCADE',
1350 onDelete => 'RESTRICT',
1354 { key => 'booksellerid',
1355 foreigntable => 'aqbooksellers',
1357 onUpdate => 'CASCADE',
1358 onDelete => 'RESTRICT',
1362 { key => 'basketno',
1363 foreigntable => 'aqbasket',
1364 foreignkey => 'basketno',
1365 onUpdate => 'CASCADE',
1366 onDelete => 'CASCADE',
1368 { key => 'biblionumber',
1369 foreigntable => 'biblio',
1370 foreignkey => 'biblionumber',
1371 onUpdate => 'SET NULL',
1372 onDelete => 'SET NULL',
1376 { key => 'listprice',
1377 foreigntable => 'currency',
1378 foreignkey => 'currency',
1379 onUpdate => 'CASCADE',
1380 onDelete => 'CASCADE',
1382 { key => 'invoiceprice',
1383 foreigntable => 'currency',
1384 foreignkey => 'currency',
1385 onUpdate => 'CASCADE',
1386 onDelete => 'CASCADE',
1389 aqorderbreakdown => [
1390 { key => 'ordernumber',
1391 foreigntable => 'aqorders',
1392 foreignkey => 'ordernumber',
1393 onUpdate => 'CASCADE',
1394 onDelete => 'CASCADE',
1396 { key => 'bookfundid',
1397 foreigntable => 'aqbookfund',
1398 foreignkey => 'bookfundid',
1399 onUpdate => 'CASCADE',
1400 onDelete => 'CASCADE',
1403 branchtransfers => [
1404 { key => 'frombranch',
1405 foreigntable => 'branches',
1406 foreignkey => 'branchcode',
1407 onUpdate => 'CASCADE',
1408 onDelete => 'CASCADE',
1410 { key => 'tobranch',
1411 foreigntable => 'branches',
1412 foreignkey => 'branchcode',
1413 onUpdate => 'CASCADE',
1414 onDelete => 'CASCADE',
1416 { key => 'itemnumber',
1417 foreigntable => 'items',
1418 foreignkey => 'itemnumber',
1419 onUpdate => 'CASCADE',
1420 onDelete => 'CASCADE',
1424 { key => 'categorycode',
1425 foreigntable => 'categories',
1426 foreignkey => 'categorycode',
1427 onUpdate => 'CASCADE',
1428 onDelete => 'CASCADE',
1430 { key => 'itemtype',
1431 foreigntable => 'itemtypes',
1432 foreignkey => 'itemtype',
1433 onUpdate => 'CASCADE',
1434 onDelete => 'CASCADE',
1437 issues => [ # constraint is SET NULL : when a borrower or an item is deleted, we keep the issuing record
1439 { key => 'borrowernumber',
1440 foreigntable => 'borrowers',
1441 foreignkey => 'borrowernumber',
1442 onUpdate => 'SET NULL',
1443 onDelete => 'SET NULL',
1445 { key => 'itemnumber',
1446 foreigntable => 'items',
1447 foreignkey => 'itemnumber',
1448 onUpdate => 'SET NULL',
1449 onDelete => 'SET NULL',
1453 { key => 'borrowernumber',
1454 foreigntable => 'borrowers',
1455 foreignkey => 'borrowernumber',
1456 onUpdate => 'CASCADE',
1457 onDelete => 'CASCADE',
1459 { key => 'biblionumber',
1460 foreigntable => 'biblio',
1461 foreignkey => 'biblionumber',
1462 onUpdate => 'CASCADE',
1463 onDelete => 'CASCADE',
1465 { key => 'itemnumber',
1466 foreigntable => 'items',
1467 foreignkey => 'itemnumber',
1468 onUpdate => 'CASCADE',
1469 onDelete => 'CASCADE',
1471 { key => 'branchcode',
1472 foreigntable => 'branches',
1473 foreignkey => 'branchcode',
1474 onUpdate => 'CASCADE',
1475 onDelete => 'CASCADE',
1478 borrowers => [ # foreign keys are RESTRICT as we don't want to delete borrowers when a branch is deleted
1479 # but prevent deleting a branch as soon as it has 1 borrower !
1480 { key => 'categorycode',
1481 foreigntable => 'categories',
1482 foreignkey => 'categorycode',
1483 onUpdate => 'RESTRICT',
1484 onDelete => 'RESTRICT',
1486 { key => 'branchcode',
1487 foreigntable => 'branches',
1488 foreignkey => 'branchcode',
1489 onUpdate => 'RESTRICT',
1490 onDelete => 'RESTRICT',
1493 deletedborrowers => [ # foreign keys are RESTRICT as we don't want to delete borrowers when a branch is deleted
1494 # but prevent deleting a branch as soon as it has 1 borrower !
1495 { key => 'categorycode',
1496 foreigntable => 'categories',
1497 foreignkey => 'categorycode',
1498 onUpdate => 'RESTRICT',
1499 onDelete => 'RESTRICT',
1501 { key => 'branchcode',
1502 foreigntable => 'branches',
1503 foreignkey => 'branchcode',
1504 onUpdate => 'RESTRICT',
1505 onDelete => 'RESTRICT',
1509 { key => 'borrowernumber',
1510 foreigntable => 'borrowers',
1511 foreignkey => 'borrowernumber',
1512 onUpdate => 'CASCADE',
1513 onDelete => 'CASCADE',
1515 { key => 'itemnumber',
1516 foreigntable => 'items',
1517 foreignkey => 'itemnumber',
1518 onUpdate => 'SET NULL',
1519 onDelete => 'SET NULL',
1522 auth_tag_structure => [
1523 { key => 'authtypecode',
1524 foreigntable => 'auth_types',
1525 foreignkey => 'authtypecode',
1526 onUpdate => 'CASCADE',
1527 onDelete => 'CASCADE',
1530 # FIXME : don't constraint auth_*_table and auth_word, as they may be replaced by zebra
1535 my %column_change = (
1539 from => 'emailaddress',
1544 from => 'streetaddress',
1546 after => 'initials',
1549 from => 'faxnumber',
1554 from => 'textmessaging',
1560 to => 'contactnote',
1561 after => 'opacnote',
1564 from => 'physstreet',
1569 from => 'streetcity',
1571 after => 'B_address',
1584 from => 'homezipcode',
1591 after => 'B_zipcode',
1596 after => 'dateenrolled',
1599 from => 'guarantor',
1600 to => 'guarantorid',
1601 after => 'contactname',
1604 from => 'altrelationship',
1605 to => 'relationship',
1606 after => 'borrowernotes',
1610 deletedborrowers => [
1612 from => 'emailaddress',
1617 from => 'streetaddress',
1619 after => 'initials',
1622 from => 'faxnumber',
1627 from => 'textmessaging',
1633 to => 'contactnote',
1634 after => 'opacnote',
1637 from => 'physstreet',
1642 from => 'streetcity',
1644 after => 'B_address',
1657 from => 'homezipcode',
1664 after => 'B_zipcode',
1669 after => 'dateenrolled',
1672 from => 'guarantor',
1673 to => 'guarantorid',
1674 after => 'contactname',
1677 from => 'altrelationship',
1678 to => 'relationship',
1679 after => 'borrowernotes',
1685 # MOVE all tables TO UTF-8 and innoDB
1686 $sth = $dbh->prepare("show table status");
1688 while ( my $table = $sth->fetchrow_hashref ) {
1689 next if $table->{Name} eq 'marc_word';
1690 next if $table->{Name} eq 'marc_subfield_table';
1691 next if $table->{Name} eq 'auth_word';
1692 next if $table->{Name} eq 'auth_subfield_table';
1693 if ($table->{Engine} ne 'InnoDB') {
1694 print "moving $table->{Name} to InnoDB\n";
1695 $dbh->do("ALTER TABLE $table->{Name} TYPE = innodb");
1697 unless ($table->{Collation} =~ /^utf8/) {
1698 print "moving $table->{Name} to utf8\n";
1699 $dbh->do("ALTER TABLE $table->{Name} CONVERT TO CHARACTER SET utf8");
1700 $dbh->do("ALTER TABLE $table->{Name} DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci");
1701 # 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 !
1707 foreach my $table (keys %column_change) {
1708 $sth = $dbh->prepare("show columns from $table");
1711 while ( ( $column, $type, $null, $key, $default, $extra ) = $sth->fetchrow )
1713 $types{$column}->{type} ="$type";
1714 $types{$column}->{null} = "$null";
1715 $types{$column}->{key} = "$key";
1716 $types{$column}->{default} = "$default";
1717 $types{$column}->{extra} = "$extra";
1719 my $tablerows = $column_change{$table};
1720 foreach my $row ( @$tablerows ) {
1721 if ($types{$row->{from}}->{type}) {
1722 print "altering $table $row->{from} to $row->{to}\n";
1723 # ALTER TABLE `borrowers` CHANGE `faxnumber` `fax` TEXT CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL
1724 # alter table `borrowers` change `faxnumber` `fax` type text null after phone
1726 "alter table `$table` change `$row->{from}` `$row->{to}` $types{$row->{from}}->{type} ".
1727 ($types{$row->{from}}->{null} eq 'YES'?" NULL":" NOT NULL").
1728 ($types{$row->{from}}->{default}?" default ".$types{$row->{from}}->{default}:"").
1729 "$types{$row->{from}}->{extra} after $row->{after} ";
1736 # Enter here the field you want to delete from DB.
1737 # FIXME :: there is a %uselessfield before which seems doing the same things.
1738 my %fieldtodelete = (
1739 # tablename => [fieldname1,fieldname2,...]
1743 print "removing some unused fields...\n";
1744 foreach my $table ( keys %fieldtodelete ) {
1745 foreach my $field ( @{$fieldtodelete{$table}} ){
1746 print "removing ".$field." from ".$table;
1747 my $sth = $dbh->prepare("ALTER TABLE $table DROP $field");
1750 print "Error : $sth->errstr \n";
1755 # Enter here the line you want to remove from DB.
1756 my %linetodelete = (
1757 # table name => where clause.
1758 userflags => "bit = 8", # delete the 'reserveforself' flags
1762 #-------------------
1767 # Get version of MySQL database engine.
1768 my $mysqlversion = `mysqld --version`;
1769 $mysqlversion =~ /Ver (\S*) /;
1771 if ( $mysqlversion ge '3.23' ) {
1772 print "Could convert to MyISAM database tables...\n" unless $silent;
1775 #---------------------------------
1778 # Collect all tables into a list
1779 $sth = $dbh->prepare("show tables");
1781 while ( my ($table) = $sth->fetchrow ) {
1782 $existingtables{$table} = 1;
1786 # Now add any missing tables
1787 foreach $table ( keys %requiretables ) {
1788 unless ( $existingtables{$table} ) {
1789 print "Adding $table table...\n" unless $silent;
1790 my $sth = $dbh->prepare("create table $table $requiretables{$table}");
1793 print "Error : $sth->errstr \n";
1799 #---------------------------------
1802 foreach $table ( keys %requirefields ) {
1803 print "Check table $table\n" if $debug and not $silent;
1804 $sth = $dbh->prepare("show columns from $table");
1807 while ( ( $column, $type, $null, $key, $default, $extra ) = $sth->fetchrow )
1809 $types{$column} = $type;
1811 foreach $column ( keys %{ $requirefields{$table} } ) {
1812 print " Check column $column [$types{$column}]\n" if $debug and not $silent;
1813 if ( !$types{$column} ) {
1815 # column doesn't exist
1816 print "Adding $column field to $table table...\n" unless $silent;
1817 $query = "alter table $table
1818 add column $column " . $requirefields{$table}->{$column};
1819 print "Execute: $query\n" if $debug;
1820 my $sti = $dbh->prepare($query);
1823 print "**Error : $sti->errstr \n";
1830 foreach $table ( keys %fielddefinitions ) {
1831 print "Check table $table\n" if $debug;
1832 $sth = $dbh->prepare("show columns from $table");
1835 while ( ( $column, $type, $null, $key, $default, $extra ) = $sth->fetchrow )
1837 $definitions->{$column}->{type} = $type;
1838 $definitions->{$column}->{null} = $null;
1839 $definitions->{$column}->{null} = 'NULL' if $null eq 'YES';
1840 $definitions->{$column}->{key} = $key;
1841 $definitions->{$column}->{default} = $default;
1842 $definitions->{$column}->{extra} = $extra;
1844 my $fieldrow = $fielddefinitions{$table};
1845 foreach my $row (@$fieldrow) {
1846 my $field = $row->{field};
1847 my $type = $row->{type};
1848 my $null = $row->{null};
1849 # $null = 'YES' if $row->{null} eq 'NULL';
1850 my $key = $row->{key};
1851 my $default = $row->{default};
1852 # $default="''" unless $default;
1853 my $extra = $row->{extra};
1854 my $def = $definitions->{$field};
1855 my $after = ($row->{after}?" after ".$row->{after}:"");
1857 unless ( $type eq $def->{type}
1858 && $null eq $def->{null}
1859 && $key eq $def->{key}
1860 && $extra eq $def->{extra} )
1862 if ( $null eq '' ) {
1865 if ( $key eq 'PRI' ) {
1866 $key = 'PRIMARY KEY';
1868 unless ( $extra eq 'auto_increment' ) {
1872 # if it's a new column use "add", if it's an old one, use "change".
1874 if ($definitions->{$field}->{type}) {
1875 $action="change $field"
1879 # if it's a primary key, drop the previous pk, before altering the table
1880 print " alter or create $field in $table\n" unless $silent;
1882 if ($key ne 'PRIMARY KEY') {
1883 # warn "alter table $table $action $field $type $null $key $extra default $default $after";
1884 $query = "alter table $table $action $field $type $null $key $extra ".($default?"default ".$dbh->quote($default):"")." $after";
1886 # warn "alter table $table drop primary key, $action $field $type $null $key $extra default $default $after";
1887 # something strange : for indexes UNIQUE, they are reported as primary key here.
1888 # but if you try to run with drop primary key, it fails.
1889 # thus, we run the query twice, one will fail, one will succeed.
1891 $query="alter table $table drop primary key, $action $field $type $null $key $extra ".($default?"default ".$dbh->quote($default):"")." $after";
1892 $query="alter table $table $action $field $type $null $key $extra ".($default?"default ".$dbh->quote($default):"")." $after";
1899 print "removing some unused data...\n";
1900 foreach my $table ( keys %linetodelete ) {
1901 foreach my $where ( @{linetodelete{$table}} ){
1902 print "DELETE FROM ".$table." where ".$where;
1904 my $sth = $dbh->prepare("DELETE FROM $table where $where");
1907 print "Error : $sth->errstr \n";
1912 # Populate tables with required data
1914 # synch table and deletedtable.
1915 foreach my $table (('borrowers','items','biblio','biblioitems')) {
1916 my %deletedborrowers;
1917 print "synch'ing $table and deleted$table\n";
1918 $sth = $dbh->prepare("show columns from deleted$table");
1920 while ( my ( $column, $type, $null, $key, $default, $extra ) = $sth->fetchrow ) {
1921 $deletedborrowers{$column}=1;
1923 $sth = $dbh->prepare("show columns from $table");
1926 while ( my ( $column, $type, $null, $key, $default, $extra ) = $sth->fetchrow ) {
1927 unless ($deletedborrowers{$column}) {
1928 my $newcol="alter table deleted$table add $column $type";
1929 if ($null eq 'YES') {
1930 $newcol .= " NULL ";
1932 $newcol .= " NOT NULL ";
1934 $newcol .= "default ".$dbh->quote($default) if $default;
1935 $newcol .= " after $previous" if $previous;
1937 print "creating column $column\n";
1943 # update publisheddate
1945 $sth = $dbh->prepare("select count(*) from serial where publisheddate is NULL");
1947 my ($emptypublished) = $sth->fetchrow;
1948 if ($emptypublished) {
1949 print "Updating publisheddate\n";
1950 $dbh->do("update serial set publisheddate=planneddate where publisheddate is NULL");
1952 foreach my $table ( keys %tabledata ) {
1953 print "Checking for data required in table $table...\n" unless $silent;
1954 my $tablerows = $tabledata{$table};
1955 foreach my $row (@$tablerows) {
1956 my $uniquefieldrequired = $row->{uniquefieldrequired};
1957 my $uniquevalue = $row->{$uniquefieldrequired};
1958 my $forceupdate = $row->{forceupdate};
1961 "select $uniquefieldrequired from $table where $uniquefieldrequired=?"
1963 $sth->execute($uniquevalue);
1965 foreach my $field (keys %$forceupdate) {
1966 if ($forceupdate->{$field}) {
1967 my $sth=$dbh->prepare("update systempreferences set $field=? where $uniquefieldrequired=?");
1968 $sth->execute($row->{$field}, $uniquevalue);
1972 print "Adding row to $table: " unless $silent;
1976 foreach my $field ( keys %$row ) {
1977 next if $field eq 'uniquefieldrequired';
1978 next if $field eq 'forceupdate';
1979 my $value = $row->{$field};
1980 push @values, $value;
1981 print " $field => $value" unless $silent;
1982 $fieldlist .= "$field,";
1983 $placeholders .= "?,";
1985 print "\n" unless $silent;
1986 $fieldlist =~ s/,$//;
1987 $placeholders =~ s/,$//;
1988 print "insert into $table ($fieldlist) values ($placeholders)";
1991 "insert into $table ($fieldlist) values ($placeholders)");
1992 $sth->execute(@values);
1998 # check indexes and create them when needed
2000 print "Checking for index required...\n" unless $silent;
2001 foreach my $table ( keys %indexes ) {
2003 # read all indexes from $table
2005 $sth = $dbh->prepare("show index from $table");
2007 my %existingindexes;
2008 while ( my ( $table, $non_unique, $key_name, $Seq_in_index, $Column_name, $Collation, $cardinality, $sub_part, $Packed, $comment ) = $sth->fetchrow ) {
2009 $existingindexes{$key_name} = 1;
2011 # read indexes to check
2012 my $tablerows = $indexes{$table};
2013 foreach my $row (@$tablerows) {
2014 my $key_name=$row->{indexname};
2015 if ($existingindexes{$key_name} eq 1) {
2016 # print "$key_name existing";
2018 print "\tCreating index $key_name in $table\n";
2020 if ($row->{indexname} eq 'PRIMARY') {
2021 $sql = "alter table $table ADD PRIMARY KEY ($row->{content})";
2023 $sql = "alter table $table ADD INDEX $key_name ($row->{content}) $row->{type}";
2026 print "Error $sql : $dbh->err \n" if $dbh->err;
2032 # check foreign keys and create them when needed
2034 print "Checking for foreign keys required...\n" unless $silent;
2035 foreach my $table ( keys %foreign_keys ) {
2037 # read all indexes from $table
2039 $sth = $dbh->prepare("show table status like '$table'");
2041 my $stat = $sth->fetchrow_hashref;
2042 # read indexes to check
2043 my $tablerows = $foreign_keys{$table};
2044 foreach my $row (@$tablerows) {
2045 my $foreign_table=$row->{foreigntable};
2046 if ($stat->{'Comment'} =~/$foreign_table/) {
2047 # print "$foreign_table existing\n";
2049 print "\tCreating foreign key $foreign_table in $table\n";
2050 # first, drop any orphan value in child table
2051 if ($row->{onDelete} ne "RESTRICT") {
2052 my $sql = "delete from $table where $row->{key} not in (select $row->{foreignkey} from $row->{foreigntable})";
2054 print "SQL ERROR: $sql : $dbh->err \n" if $dbh->err;
2056 my $sql="alter table $table ADD FOREIGN KEY $row->{key} ($row->{key}) REFERENCES $row->{foreigntable} ($row->{foreignkey})";
2057 $sql .= " on update ".$row->{onUpdate} if $row->{onUpdate};
2058 $sql .= " on delete ".$row->{onDelete} if $row->{onDelete};
2061 print "====================
2062 An error occured during :
2064 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).
2065 You can find those values with select
2066 \t$table.* from $table where $row->{key} not in (select $row->{foreignkey} from $row->{foreigntable})
2067 ====================\n
2073 # now drop useless tables
2074 foreach $table ( @TableToDelete ) {
2075 if ( $existingtables{$table} ) {
2076 print "Dropping unused table $table\n" if $debug and not $silent;
2077 $dbh->do("drop table $table");
2079 print "Error : $dbh->errstr \n";
2088 # create frameworkcode row in biblio table & fill it with marc_biblio.frameworkcode.
2091 # 1st, get how many biblio we will have to do...
2092 $sth = $dbh->prepare('select count(*) from marc_biblio');
2094 my ($totaltodo) = $sth->fetchrow;
2096 $sth = $dbh->prepare("show columns from biblio");
2099 my $bibliofwexist=0;
2100 while ( ( $column, $type, $null, $key, $default, $extra ) = $sth->fetchrow ){
2101 $bibliofwexist=1 if $column eq 'frameworkcode';
2103 unless ($bibliofwexist) {
2104 print "moving biblioframework to biblio table\n";
2105 $dbh->do('ALTER TABLE `biblio` ADD `frameworkcode` VARCHAR( 4 ) NOT NULL AFTER `biblionumber`');
2106 $sth = $dbh->prepare('select biblionumber,frameworkcode from marc_biblio');
2108 my $sth_update = $dbh->prepare('update biblio set frameworkcode=? where biblionumber=?');
2110 while (my ($biblionumber,$frameworkcode) = $sth->fetchrow) {
2111 $sth_update->execute($frameworkcode,$biblionumber);
2113 print "\r$totaldone / $totaltodo" unless ($totaldone % 100);
2118 # at last, remove useless fields
2119 foreach $table ( keys %uselessfields ) {
2120 my @fields = split /,/,$uselessfields{$table};
2123 foreach my $fieldtodrop (@fields) {
2124 $fieldtodrop =~ s/\t//g;
2125 $fieldtodrop =~ s/\n//g;
2127 $sth = $dbh->prepare("show columns from $table");
2129 while ( my ( $column, $type, $null, $key, $default, $extra ) = $sth->fetchrow )
2131 $exists =1 if ($column eq $fieldtodrop);
2134 print "deleting $fieldtodrop field in $table...\n" unless $silent;
2135 my $sth = $dbh->prepare("alter table $table drop $fieldtodrop");
2142 # Changing aqbookfund's primary key
2144 $sth=$dbh->prepare("ALTER TABLE `aqbookfund` DROP PRIMARY KEY , ADD PRIMARY KEY ( `bookfundid` , `branchcode` ) ;");
2148 print "upgrade to Koha 3.0 done\n";
2149 SetVersion ($DBversion);
2155 Deal with virtualshelves
2158 $DBversion = "3.00.00.001";
2159 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2160 # update virtualshelves table to
2162 $dbh->do("ALTER TABLE `bookshelf` RENAME `virtualshelves`");
2163 $dbh->do("ALTER TABLE `shelfcontents` RENAME `virtualshelfcontents`");
2164 $dbh->do("ALTER TABLE `virtualshelfcontents` ADD `biblionumber` INT( 11 ) NOT NULL");
2165 $dbh->do("UPDATE `virtualshelfcontents` SET biblionumber=(SELECT biblionumber FROM items WHERE items.itemnumber=virtualshelfcontents.itemnumber)");
2166 # drop all foreign keys : otherwise, we can't drop itemnumber field.
2167 DropAllForeignKeys('virtualshelfcontents');
2168 # create the new foreign keys (on biblionumber)
2169 $dbh->do("ALTER TABLE `virtualshelfcontents` ADD FOREIGN KEY biblionumber_fk (biblionumber) REFERENCES biblio (biblionumber) ON UPDATE CASCADE ON DELETE CASCADE");
2170 # re-create the foreign key on virtualshelf
2171 $dbh->do("ALTER TABLE `virtualshelfcontents` ADD FOREIGN KEY shelfnumber_fk (shelfnumber) REFERENCES virtualshelves (shelfnumber) ON UPDATE CASCADE ON DELETE CASCADE");
2172 # now we can drop the itemnumber column
2173 $dbh->do("ALTER TABLE `virtualshelfcontents` DROP `itemnumber`");
2174 print "Upgrade to $DBversion done (virtualshelves)\n";
2175 SetVersion ($DBversion);
2179 $DBversion = "3.00.00.002";
2180 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2181 $dbh->do("DROP TABLE sessions");
2182 $dbh->do("CREATE TABLE `sessions` (
2183 `id` char(32) NOT NULL,
2184 `a_session` text NOT NULL,
2185 UNIQUE KEY `id` (`id`)
2186 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;");
2187 print "Upgrade to $DBversion done (sessions uses CGI::session, new table structure for sessions)\n";
2188 SetVersion ($DBversion);
2192 $DBversion = "3.00.00.003";
2193 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2194 if (C4::Context->preference("opaclanguage") eq "fr") {
2195 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('ReservesNeedReturns','0','Si ce paramètre est mis à 1, une réservation posée sur un exemplaire présent sur le site devra être passée en retour pour être disponible. Sinon, elle sera automatiquement disponible, Koha considère que le bibliothécaire place la réservation en ayant le document en mains','','YesNo')");
2197 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('ReservesNeedReturns','0','If set, a reserve done on an item available in this branch need a check-in, otherwise, a reserve on a specific item, that is on the branch & available is considered as available','','YesNo')");
2199 print "Upgrade to $DBversion done (adding ReservesNeedReturns systempref, in circulation)\n";
2200 SetVersion ($DBversion);
2204 $DBversion = "3.00.00.004";
2205 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2206 $dbh->do("INSERT INTO `systempreferences` VALUES ('DebugLevel','2','set the level of error info sent to the browser. 0=none, 1=some, 2=most','0|1|2','Choice')");
2207 print "Upgrade to $DBversion done (adding DebugLevel systempref, in 'Admin' tab)\n";
2208 SetVersion ($DBversion);
2211 $DBversion = "3.00.00.005";
2212 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2213 $dbh->do("CREATE TABLE `tags` (
2214 `entry` varchar(255) NOT NULL default '',
2215 `weight` bigint(20) NOT NULL default 0,
2216 PRIMARY KEY (`entry`)
2217 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2219 $dbh->do("CREATE TABLE `nozebra` (
2220 `server` varchar(20) NOT NULL,
2221 `indexname` varchar(40) NOT NULL,
2222 `value` varchar(250) NOT NULL,
2223 `biblionumbers` longtext NOT NULL,
2224 KEY `indexname` (`server`,`indexname`),
2225 KEY `value` (`server`,`value`))
2226 ENGINE=InnoDB DEFAULT CHARSET=utf8;
2228 print "Upgrade to $DBversion done (adding tags and nozebra tables )\n";
2229 SetVersion ($DBversion);
2232 $DBversion = "3.00.00.006";
2233 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2234 $dbh->do("UPDATE issues SET issuedate=timestamp WHERE issuedate='0000-00-00'");
2235 print "Upgrade to $DBversion done (filled issues.issuedate with timestamp)\n";
2236 SetVersion ($DBversion);
2239 $DBversion = "3.00.00.007";
2240 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2241 $dbh->do("INSERT INTO systempreferences (variable,value,explanation,options,type) VALUES ('SessionStorage','mysql','Use mysql or a temporary file for storing session data','mysql|tmp','Choice')");
2242 print "Upgrade to $DBversion done (set SessionStorage variable)\n";
2243 SetVersion ($DBversion);
2246 =item DropAllForeignKeys($table)
2248 Drop all foreign keys of the table $table
2252 sub DropAllForeignKeys {
2254 # get the table description
2255 my $sth = $dbh->prepare("SHOW CREATE TABLE $table");
2257 my $vsc_structure = $sth->fetchrow;
2258 # split on CONSTRAINT keyword
2259 my @fks = split /CONSTRAINT /,$vsc_structure;
2262 # isolate what is before FOREIGN KEY, if there is something, it's a foreign key to drop
2263 $_ = /(.*) FOREIGN KEY.*/;
2266 # we have found 1 foreign, drop it
2267 $dbh->do("ALTER TABLE $table DROP FOREIGN KEY $id");
2279 =item TransformToNum
2281 Transform the Koha version from a 4 parts string
2282 to a number, with just 1 .
2286 sub TransformToNum {
2287 my $version = shift;
2288 # remove the 3 last . to have a Perl number
2289 $version =~ s/(.*\..*)\.(.*)\.(.*)/$1$2$3/;
2294 set the DBversion in the systempreferences
2298 my $kohaversion = TransformToNum(shift);
2299 if (C4::Context->preference('Version')) {
2300 my $finish=$dbh->prepare("UPDATE systempreferences SET value=? WHERE variable='Version'");
2301 $finish->execute($kohaversion);
2303 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')");
2304 $finish->execute($kohaversion);
2310 # Revision 1.172 2007/07/19 10:21:22 hdl