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 => 'BorrowersTitles',
269 value => 'Mr|Mrs|Miss|Ms',
270 forceupdate => { 'explanation' => 1,
272 explanation => 'List all Titles for borrowers',
276 uniquefieldrequired => 'variable',
277 variable => 'BorrowerMandatoryField',
278 value => 'cardnumber|surname|address',
279 forceupdate => { 'explanation' => 1,
281 explanation => 'List all mandatory fields for borrowers',
285 uniquefieldrequired => 'variable',
286 variable => 'borrowerRelationship',
287 value => 'father|mother,grand-mother',
288 forceupdate => { 'explanation' => 1,
290 explanation => 'The relationships between a guarantor & a guarantee (separated by | or ,)',
294 uniquefieldrequired => 'variable',
295 variable => 'ReservesMaxPickUpDelay',
297 forceupdate => { 'explanation' => 1,
299 explanation => 'Maximum delay to pick up a reserved document',
303 uniquefieldrequired => 'variable',
304 variable => 'TransfersMaxDaysWarning',
306 forceupdate => { 'explanation' => 1,
308 explanation => 'Max delay before considering the transfer has potentialy a problem',
312 uniquefieldrequired => 'variable',
313 variable => 'memberofinstitution',
315 forceupdate => { 'explanation' => 1,
317 explanation => 'Are your patrons members of institutions',
321 uniquefieldrequired => 'variable',
322 variable => 'ReadingHistory',
324 forceupdate => { 'explanation' => 1,
326 explanation => 'Allow reading record info retrievable from issues and oldissues tables',
330 uniquefieldrequired => 'variable',
331 variable => 'IssuingInProcess',
333 forceupdate => { 'explanation' => 1,
335 explanation => 'Allow no debt alert if the patron is issuing item that accumulate debt',
339 uniquefieldrequired => 'variable',
340 variable => 'AutomaticItemReturn',
342 forceupdate => { 'explanation' => 1,
344 explanation => 'This Variable allow or not to return automaticly to his homebranch',
348 uniquefieldrequired => 'variable',
349 variable => 'reviewson',
351 forceupdate => { 'explanation' => 1,
353 explanation => 'Allows patrons to submit reviews from the opac',
357 uniquefieldrequired => 'variable',
358 variable => 'intranet_includes',
360 forceupdate => { 'explanation' => 1,
362 explanation => 'The includes directory you want for specific look of Koha (includes or includes_npl for example)',
366 uniquefieldrequired => 'variable',
367 variable => 'AutoLocation',
369 forceupdate => { 'explanation' => 1,
371 explanation => 'switch to activate or not Autolocation, if Yes, the Librarian can\'t change his location, it\'s defined by branchip',
375 uniquefieldrequired => 'variable',
376 variable => 'serialsadditems',
382 explanation => 'If set, a new item will be automatically added when receiving an issue',
386 uniquefieldrequired => 'variable',
387 variable => 'expandedSearchOption',
393 explanation => 'search among marc field',
397 uniquefieldrequired => 'variable',
398 variable => 'RequestOnOpac',
400 forceupdate => { 'explanation' => 1,
402 explanation => 'option to allow reserves on opac',
406 uniquefieldrequired => 'variable',
407 variable => 'OpacCloud',
409 forceupdate => { 'explanation' => 1,
411 explanation => 'Enable / Disable cloud link on OPAC (Require to run misc/cronjobs/build_browser_and_cloud.pl on the server)',
415 uniquefieldrequired => 'variable',
416 variable => 'OpacBrowser',
418 forceupdate => { 'explanation' => 1,
420 explanation => 'Enable/Disable browser link on OPAC (Require to run misc/cronjobs/build_browser_and_cloud.pl on the server)',
424 uniquefieldrequired => 'variable',
425 variable => 'OpacTopissue',
427 forceupdate => { 'explanation' => 1,
429 explanation => 'Enable / Disable the top issue link on OPAC',
433 uniquefieldrequired => 'variable',
434 variable => 'OpacAuthorities',
436 forceupdate => { 'explanation' => 1,
438 explanation => 'Enable / Disable the search authority link on OPAC',
442 uniquefieldrequired => 'variable',
443 variable => 'CataloguingLog',
445 forceupdate => {'explanation' => 1, 'type' => 1},
446 explanation => 'Active this if you want to log cataloguing action.',
450 uniquefieldrequired => 'variable',
451 variable => 'BorrowersLog',
453 forceupdate => {'explanation' => 1, 'type' => 1},
454 explanation => 'Active this if you want to log borrowers edition/creation/deletion...',
458 uniquefieldrequired => 'variable',
459 variable => 'SubscriptionLog',
461 forceupdate => {'explanation' => 1, 'type' => 1},
462 explanation => 'Active this if you want to log Subscription action',
466 uniquefieldrequired => 'variable',
467 variable => 'IssueLog',
469 forceupdate => {'explanation' => 1, 'type' => 1},
470 explanation => 'Active this if you want to log issue.',
474 uniquefieldrequired => 'variable',
475 variable => 'ReturnLog',
477 forceupdate => {'explanation' => 1, 'type' => 1},
478 explanation => 'Active this if you want to log the circulation return',
482 uniquefieldrequired => 'variable',
483 variable => 'Version',
485 forceupdate => {'explanation' => 1, 'type' => 1},
486 explanation => 'Koha Version',
490 uniquefieldrequired => 'variable',
491 variable => 'LetterLog',
493 forceupdate => {'explanation' => 1, 'type' => 1},
494 explanation => 'Active this if you want to log all the letter sent',
498 uniquefieldrequired => 'variable',
499 variable => 'FinesLog',
501 forceupdate => {'explanation' => 1, 'type' => 1},
502 explanation => 'Active this if you want to log fines',
506 uniquefieldrequired => 'variable',
507 variable => 'NoZebra',
509 forceupdate => {'explanation' => 1, 'type' => 1},
510 explanation => 'Active this if you want NOT to use zebra (large libraries should avoid this parameters)',
514 uniquefieldrequired => 'variable',
515 variable => 'NoZebraIndexes',
517 forceupdate => {'explanation' => 1, 'type' => 1},
518 explanation => "Enter a specific hash for NoZebra indexes. Enter : 'indexname' => '100a,245a,500*','index2' => '...'",
522 uniquefieldrequired => 'variable',
523 variable => 'uppercasesurnames',
525 forceupdate => {'explanation' => 1, 'type' => 1},
526 explanation => "Force Surnames to be uppercase",
532 uniquefieldrequired => 'bit',
534 flag => 'editauthorities',
535 flagdesc => 'allow to edit authorities',
539 uniquefieldrequired => 'bit',
542 flagdesc => 'allow to manage serials subscriptions',
546 uniquefieldrequired => 'bit',
549 flagdesc => 'allow to access to the reports module',
553 authorised_values => [
555 uniquefieldrequired => 'id',
556 category => 'SUGGEST',
557 authorised_value => 'Not enough budget',
558 lib => 'This book it too much expensive',
563 my %fielddefinitions = (
565 # { field => 'fieldname',
566 # type => 'fieldtype',
574 field => 'booksellerid',
584 field => 'bookfundid',
585 type => 'varchar(5)',
600 extra => 'auto_increment',
603 field => 'listprice',
604 type => 'varchar(10)',
611 field => 'invoiceprice',
612 type => 'varchar(10)',
619 field => 'invoicedisc',
620 type => 'float(6,4)',
631 field => 'bookfundid',
632 type => 'varchar(5)',
642 field => 'notify_id',
650 field => 'notify_level',
658 field => 'accountno',
659 type => 'smallint(6)',
666 field => 'description',
667 type => 'mediumtext',
672 type => 'mediumtext',
679 { field => 'firstname',
683 { field => 'initials',
687 { field => 'B_email',
690 after => 'B_zipcode',
693 field => 'streetnumber', # street number (hidden if streettable table is empty)
699 field => 'streettype', # street table, list builded from a system table
702 after => 'streetnumber',
709 field => 'B_streetnumber', # street number (hidden if streettable table is empty)
715 field => 'B_streettype', # street table, list builded from a system table
718 after => 'B_streetnumber',
727 field => 'address2', # complement address
739 field => 'contactfirstname', # contact's firstname
742 after => 'contactname',
745 field => 'contacttitle', # contact's title
748 after => 'contactfirstname',
751 field => 'branchcode',
752 type => 'varchar(10)',
758 field => 'categorycode',
759 type => 'varchar(10)',
769 type => 'varchar(25)',
777 type => 'varchar(4)',
785 type => 'varchar(30)',
791 field => 'publicationyear',
798 field => 'collectiontitle',
799 type => 'mediumtext',
805 field => 'collectionissn',
806 type => 'mediumtext',
812 field => 'collectionvolume',
813 type => 'mediumtext',
819 field => 'editionstatement',
826 field => 'editionresponsability',
834 deletedbiblioitems => [
837 type => 'varchar(30)',
846 type => 'varchar(15)',
853 field => 'branchprinter',
854 type => 'varchar(100)',
861 field => 'branchcode',
862 type => 'varchar(10)',
870 field => 'frombranch',
871 type => 'VARCHAR(10)',
879 type => 'VARCHAR(10)',
888 field => 'category_type',
896 field => 'categorycode',
897 type => 'varchar(10)',
905 deletedborrowers => [
906 { field => 'firstname',
910 { field => 'initials',
914 { field => 'B_email',
917 after => 'B_zipcode',
920 field => 'streetnumber', # street number (hidden if streettable table is empty)
926 field => 'streettype', # street table, list builded from a system table
929 after => 'streetnumber',
936 field => 'B_streetnumber', # street number (hidden if streettable table is empty)
942 field => 'B_streettype', # street table, list builded from a system table
945 after => 'B_streetnumber',
954 field => 'address2', # complement address
966 field => 'contactfirstname', # contact's firstname
969 after => 'contactname',
972 field => 'contacttitle', # contact's title
975 after => 'contactfirstname',
981 field => 'borrowernumber',
983 null => 'NULL', # can be null when a borrower is deleted and the foreign key rule executed
989 field => 'itemnumber',
991 null => 'NULL', # can be null when a borrower is deleted and the foreign key rule executed
997 field => 'branchcode',
998 type => 'varchar(10)',
1005 field => 'issuedate',
1009 default => '0000-00-00',
1020 default => '0000-00-00',
1024 field => 'cutterextra',
1025 type => 'varchar(45)',
1032 field => 'issue_date',
1040 field => 'homebranch',
1041 type => 'varchar(10)',
1048 field => 'holdingbranch',
1049 type => 'varchar(10)',
1057 type => 'varchar(10)',
1066 field => 'itemtype',
1067 type => 'varchar(10)',
1090 marc_subfield_structure => [
1092 field => 'defaultvalue',
1101 field => 'expirationdate',
1118 field => 'waitingdate',
1138 field => 'dateadded',
1139 type => 'timestamp',
1143 systempreferences => [
1153 field => 'explanation',
1175 # { indexname => 'index detail'
1179 { indexname => 'PRIMARY',
1185 { indexname => 'booksellerid',
1186 content => 'booksellerid',
1190 { indexname => 'basketno',
1191 content => 'basketno',
1194 aqorderbreakdown => [
1195 { indexname => 'ordernumber',
1196 content => 'ordernumber',
1198 { indexname => 'bookfundid',
1199 content => 'bookfundid',
1203 { indexname => 'isbn',
1206 { indexname => 'publishercode',
1207 content => 'publishercode',
1212 indexname => 'branchcode',
1213 content => 'branchcode',
1217 branchrelations => [
1219 indexname => 'PRIMARY',
1220 content => 'categorycode',
1224 branchrelations => [
1225 { indexname => 'PRIMARY',
1226 content => 'branchcode,categorycode',
1229 { indexname => 'branchcode',
1230 content => 'branchcode',
1232 { indexname => 'categorycode',
1233 content => 'categorycode',
1237 { indexname => 'PRIMARY',
1238 content => 'currency',
1244 indexname => 'categorycode',
1245 content => 'categorycode',
1249 { indexname => 'homebranch',
1250 content => 'homebranch',
1252 { indexname => 'holdingbranch',
1253 content => 'holdingbranch',
1258 indexname => 'itemtype',
1259 content => 'itemtype',
1263 { indexname => 'shelfnumber',
1264 content => 'shelfnumber',
1266 { indexname => 'itemnumber',
1267 content => 'itemnumber',
1271 { indexname => 'PRIMARY',
1278 my %foreign_keys = (
1280 # { key => 'the key in table' (must be indexed)
1281 # foreigntable => 'the foreigntable name', # (the parent)
1282 # foreignkey => 'the foreign key column(s)' # (in the parent)
1283 # onUpdate => 'CASCADE|SET NULL|NO ACTION| RESTRICT',
1284 # onDelete => 'CASCADE|SET NULL|NO ACTION| RESTRICT',
1287 branchrelations => [
1288 { key => 'branchcode',
1289 foreigntable => 'branches',
1290 foreignkey => 'branchcode',
1291 onUpdate => 'CASCADE',
1292 onDelete => 'CASCADE',
1294 { key => 'categorycode',
1295 foreigntable => 'branchcategories',
1296 foreignkey => 'categorycode',
1297 onUpdate => 'CASCADE',
1298 onDelete => 'CASCADE',
1302 { key => 'shelfnumber',
1303 foreigntable => 'virtualshelf',
1304 foreignkey => 'shelfnumber',
1305 onUpdate => 'CASCADE',
1306 onDelete => 'CASCADE',
1308 { key => 'itemnumber',
1309 foreigntable => 'items',
1310 foreignkey => 'itemnumber',
1311 onUpdate => 'CASCADE',
1312 onDelete => 'CASCADE',
1315 # onDelete is RESTRICT on reference tables (branches, itemtype) as we don't want items to be
1316 # easily deleted, but branches/itemtype not too easy to empty...
1318 { key => 'biblionumber',
1319 foreigntable => 'biblio',
1320 foreignkey => 'biblionumber',
1321 onUpdate => 'CASCADE',
1322 onDelete => 'CASCADE',
1324 { key => 'itemtype',
1325 foreigntable => 'itemtypes',
1326 foreignkey => 'itemtype',
1327 onUpdate => 'CASCADE',
1328 onDelete => 'RESTRICT',
1332 { key => 'biblioitemnumber',
1333 foreigntable => 'biblioitems',
1334 foreignkey => 'biblioitemnumber',
1335 onUpdate => 'CASCADE',
1336 onDelete => 'CASCADE',
1338 { key => 'homebranch',
1339 foreigntable => 'branches',
1340 foreignkey => 'branchcode',
1341 onUpdate => 'CASCADE',
1342 onDelete => 'RESTRICT',
1344 { key => 'holdingbranch',
1345 foreigntable => 'branches',
1346 foreignkey => 'branchcode',
1347 onUpdate => 'CASCADE',
1348 onDelete => 'RESTRICT',
1352 { key => 'booksellerid',
1353 foreigntable => 'aqbooksellers',
1355 onUpdate => 'CASCADE',
1356 onDelete => 'RESTRICT',
1360 { key => 'basketno',
1361 foreigntable => 'aqbasket',
1362 foreignkey => 'basketno',
1363 onUpdate => 'CASCADE',
1364 onDelete => 'CASCADE',
1366 { key => 'biblionumber',
1367 foreigntable => 'biblio',
1368 foreignkey => 'biblionumber',
1369 onUpdate => 'SET NULL',
1370 onDelete => 'SET NULL',
1374 { key => 'listprice',
1375 foreigntable => 'currency',
1376 foreignkey => 'currency',
1377 onUpdate => 'CASCADE',
1378 onDelete => 'CASCADE',
1380 { key => 'invoiceprice',
1381 foreigntable => 'currency',
1382 foreignkey => 'currency',
1383 onUpdate => 'CASCADE',
1384 onDelete => 'CASCADE',
1387 aqorderbreakdown => [
1388 { key => 'ordernumber',
1389 foreigntable => 'aqorders',
1390 foreignkey => 'ordernumber',
1391 onUpdate => 'CASCADE',
1392 onDelete => 'CASCADE',
1394 { key => 'bookfundid',
1395 foreigntable => 'aqbookfund',
1396 foreignkey => 'bookfundid',
1397 onUpdate => 'CASCADE',
1398 onDelete => 'CASCADE',
1401 branchtransfers => [
1402 { key => 'frombranch',
1403 foreigntable => 'branches',
1404 foreignkey => 'branchcode',
1405 onUpdate => 'CASCADE',
1406 onDelete => 'CASCADE',
1408 { key => 'tobranch',
1409 foreigntable => 'branches',
1410 foreignkey => 'branchcode',
1411 onUpdate => 'CASCADE',
1412 onDelete => 'CASCADE',
1414 { key => 'itemnumber',
1415 foreigntable => 'items',
1416 foreignkey => 'itemnumber',
1417 onUpdate => 'CASCADE',
1418 onDelete => 'CASCADE',
1422 { key => 'categorycode',
1423 foreigntable => 'categories',
1424 foreignkey => 'categorycode',
1425 onUpdate => 'CASCADE',
1426 onDelete => 'CASCADE',
1428 { key => 'itemtype',
1429 foreigntable => 'itemtypes',
1430 foreignkey => 'itemtype',
1431 onUpdate => 'CASCADE',
1432 onDelete => 'CASCADE',
1435 issues => [ # constraint is SET NULL : when a borrower or an item is deleted, we keep the issuing record
1437 { key => 'borrowernumber',
1438 foreigntable => 'borrowers',
1439 foreignkey => 'borrowernumber',
1440 onUpdate => 'SET NULL',
1441 onDelete => 'SET NULL',
1443 { key => 'itemnumber',
1444 foreigntable => 'items',
1445 foreignkey => 'itemnumber',
1446 onUpdate => 'SET NULL',
1447 onDelete => 'SET NULL',
1451 { key => 'borrowernumber',
1452 foreigntable => 'borrowers',
1453 foreignkey => 'borrowernumber',
1454 onUpdate => 'CASCADE',
1455 onDelete => 'CASCADE',
1457 { key => 'biblionumber',
1458 foreigntable => 'biblio',
1459 foreignkey => 'biblionumber',
1460 onUpdate => 'CASCADE',
1461 onDelete => 'CASCADE',
1463 { key => 'itemnumber',
1464 foreigntable => 'items',
1465 foreignkey => 'itemnumber',
1466 onUpdate => 'CASCADE',
1467 onDelete => 'CASCADE',
1469 { key => 'branchcode',
1470 foreigntable => 'branches',
1471 foreignkey => 'branchcode',
1472 onUpdate => 'CASCADE',
1473 onDelete => 'CASCADE',
1476 borrowers => [ # foreign keys are RESTRICT as we don't want to delete borrowers when a branch is deleted
1477 # but prevent deleting a branch as soon as it has 1 borrower !
1478 { key => 'categorycode',
1479 foreigntable => 'categories',
1480 foreignkey => 'categorycode',
1481 onUpdate => 'RESTRICT',
1482 onDelete => 'RESTRICT',
1484 { key => 'branchcode',
1485 foreigntable => 'branches',
1486 foreignkey => 'branchcode',
1487 onUpdate => 'RESTRICT',
1488 onDelete => 'RESTRICT',
1491 deletedborrowers => [ # foreign keys are RESTRICT as we don't want to delete borrowers when a branch is deleted
1492 # but prevent deleting a branch as soon as it has 1 borrower !
1493 { key => 'categorycode',
1494 foreigntable => 'categories',
1495 foreignkey => 'categorycode',
1496 onUpdate => 'RESTRICT',
1497 onDelete => 'RESTRICT',
1499 { key => 'branchcode',
1500 foreigntable => 'branches',
1501 foreignkey => 'branchcode',
1502 onUpdate => 'RESTRICT',
1503 onDelete => 'RESTRICT',
1507 { key => 'borrowernumber',
1508 foreigntable => 'borrowers',
1509 foreignkey => 'borrowernumber',
1510 onUpdate => 'CASCADE',
1511 onDelete => 'CASCADE',
1513 { key => 'itemnumber',
1514 foreigntable => 'items',
1515 foreignkey => 'itemnumber',
1516 onUpdate => 'SET NULL',
1517 onDelete => 'SET NULL',
1520 auth_tag_structure => [
1521 { key => 'authtypecode',
1522 foreigntable => 'auth_types',
1523 foreignkey => 'authtypecode',
1524 onUpdate => 'CASCADE',
1525 onDelete => 'CASCADE',
1528 # FIXME : don't constraint auth_*_table and auth_word, as they may be replaced by zebra
1533 my %column_change = (
1537 from => 'emailaddress',
1542 from => 'streetaddress',
1544 after => 'initials',
1547 from => 'faxnumber',
1552 from => 'textmessaging',
1558 to => 'contactnote',
1559 after => 'opacnote',
1562 from => 'physstreet',
1567 from => 'streetcity',
1569 after => 'B_address',
1582 from => 'homezipcode',
1589 after => 'B_zipcode',
1594 after => 'dateenrolled',
1597 from => 'guarantor',
1598 to => 'guarantorid',
1599 after => 'contactname',
1602 from => 'altrelationship',
1603 to => 'relationship',
1604 after => 'borrowernotes',
1608 deletedborrowers => [
1610 from => 'emailaddress',
1615 from => 'streetaddress',
1617 after => 'initials',
1620 from => 'faxnumber',
1625 from => 'textmessaging',
1631 to => 'contactnote',
1632 after => 'opacnote',
1635 from => 'physstreet',
1640 from => 'streetcity',
1642 after => 'B_address',
1655 from => 'homezipcode',
1662 after => 'B_zipcode',
1667 after => 'dateenrolled',
1670 from => 'guarantor',
1671 to => 'guarantorid',
1672 after => 'contactname',
1675 from => 'altrelationship',
1676 to => 'relationship',
1677 after => 'borrowernotes',
1683 # MOVE all tables TO UTF-8 and innoDB
1684 $sth = $dbh->prepare("show table status");
1686 while ( my $table = $sth->fetchrow_hashref ) {
1687 next if $table->{Name} eq 'marc_word';
1688 next if $table->{Name} eq 'marc_subfield_table';
1689 next if $table->{Name} eq 'auth_word';
1690 next if $table->{Name} eq 'auth_subfield_table';
1691 if ($table->{Engine} ne 'InnoDB') {
1692 print "moving $table->{Name} to InnoDB\n";
1693 $dbh->do("ALTER TABLE $table->{Name} TYPE = innodb");
1695 unless ($table->{Collation} =~ /^utf8/) {
1696 print "moving $table->{Name} to utf8\n";
1697 $dbh->do("ALTER TABLE $table->{Name} CONVERT TO CHARACTER SET utf8");
1698 $dbh->do("ALTER TABLE $table->{Name} DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci");
1699 # 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 !
1705 foreach my $table (keys %column_change) {
1706 $sth = $dbh->prepare("show columns from $table");
1709 while ( ( $column, $type, $null, $key, $default, $extra ) = $sth->fetchrow )
1711 $types{$column}->{type} ="$type";
1712 $types{$column}->{null} = "$null";
1713 $types{$column}->{key} = "$key";
1714 $types{$column}->{default} = "$default";
1715 $types{$column}->{extra} = "$extra";
1717 my $tablerows = $column_change{$table};
1718 foreach my $row ( @$tablerows ) {
1719 if ($types{$row->{from}}->{type}) {
1720 print "altering $table $row->{from} to $row->{to}\n";
1721 # ALTER TABLE `borrowers` CHANGE `faxnumber` `fax` TEXT CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL
1722 # alter table `borrowers` change `faxnumber` `fax` type text null after phone
1724 "alter table `$table` change `$row->{from}` `$row->{to}` $types{$row->{from}}->{type} ".
1725 ($types{$row->{from}}->{null} eq 'YES'?" NULL":" NOT NULL").
1726 ($types{$row->{from}}->{default}?" default ".$types{$row->{from}}->{default}:"").
1727 "$types{$row->{from}}->{extra} after $row->{after} ";
1734 # Enter here the field you want to delete from DB.
1735 # FIXME :: there is a %uselessfield before which seems doing the same things.
1736 my %fieldtodelete = (
1737 # tablename => [fieldname1,fieldname2,...]
1741 print "removing some unused fields...\n";
1742 foreach my $table ( keys %fieldtodelete ) {
1743 foreach my $field ( @{$fieldtodelete{$table}} ){
1744 print "removing ".$field." from ".$table;
1745 my $sth = $dbh->prepare("ALTER TABLE $table DROP $field");
1748 print "Error : $sth->errstr \n";
1753 # Enter here the line you want to remove from DB.
1754 my %linetodelete = (
1755 # table name => where clause.
1756 userflags => "bit = 8", # delete the 'reserveforself' flags
1760 #-------------------
1765 # Get version of MySQL database engine.
1766 my $mysqlversion = `mysqld --version`;
1767 $mysqlversion =~ /Ver (\S*) /;
1769 if ( $mysqlversion ge '3.23' ) {
1770 print "Could convert to MyISAM database tables...\n" unless $silent;
1773 #---------------------------------
1776 # Collect all tables into a list
1777 $sth = $dbh->prepare("show tables");
1779 while ( my ($table) = $sth->fetchrow ) {
1780 $existingtables{$table} = 1;
1784 # Now add any missing tables
1785 foreach $table ( keys %requiretables ) {
1786 unless ( $existingtables{$table} ) {
1787 print "Adding $table table...\n" unless $silent;
1788 my $sth = $dbh->prepare("create table $table $requiretables{$table}");
1791 print "Error : $sth->errstr \n";
1797 #---------------------------------
1800 foreach $table ( keys %requirefields ) {
1801 print "Check table $table\n" if $debug and not $silent;
1802 $sth = $dbh->prepare("show columns from $table");
1805 while ( ( $column, $type, $null, $key, $default, $extra ) = $sth->fetchrow )
1807 $types{$column} = $type;
1809 foreach $column ( keys %{ $requirefields{$table} } ) {
1810 print " Check column $column [$types{$column}]\n" if $debug and not $silent;
1811 if ( !$types{$column} ) {
1813 # column doesn't exist
1814 print "Adding $column field to $table table...\n" unless $silent;
1815 $query = "alter table $table
1816 add column $column " . $requirefields{$table}->{$column};
1817 print "Execute: $query\n" if $debug;
1818 my $sti = $dbh->prepare($query);
1821 print "**Error : $sti->errstr \n";
1828 foreach $table ( keys %fielddefinitions ) {
1829 print "Check table $table\n" if $debug;
1830 $sth = $dbh->prepare("show columns from $table");
1833 while ( ( $column, $type, $null, $key, $default, $extra ) = $sth->fetchrow )
1835 $definitions->{$column}->{type} = $type;
1836 $definitions->{$column}->{null} = $null;
1837 $definitions->{$column}->{null} = 'NULL' if $null eq 'YES';
1838 $definitions->{$column}->{key} = $key;
1839 $definitions->{$column}->{default} = $default;
1840 $definitions->{$column}->{extra} = $extra;
1842 my $fieldrow = $fielddefinitions{$table};
1843 foreach my $row (@$fieldrow) {
1844 my $field = $row->{field};
1845 my $type = $row->{type};
1846 my $null = $row->{null};
1847 # $null = 'YES' if $row->{null} eq 'NULL';
1848 my $key = $row->{key};
1849 my $default = $row->{default};
1850 # $default="''" unless $default;
1851 my $extra = $row->{extra};
1852 my $def = $definitions->{$field};
1853 my $after = ($row->{after}?" after ".$row->{after}:"");
1855 unless ( $type eq $def->{type}
1856 && $null eq $def->{null}
1857 && $key eq $def->{key}
1858 && $extra eq $def->{extra} )
1860 if ( $null eq '' ) {
1863 if ( $key eq 'PRI' ) {
1864 $key = 'PRIMARY KEY';
1866 unless ( $extra eq 'auto_increment' ) {
1870 # if it's a new column use "add", if it's an old one, use "change".
1872 if ($definitions->{$field}->{type}) {
1873 $action="change $field"
1877 # if it's a primary key, drop the previous pk, before altering the table
1878 print " alter or create $field in $table\n" unless $silent;
1880 if ($key ne 'PRIMARY KEY') {
1881 # warn "alter table $table $action $field $type $null $key $extra default $default $after";
1882 $query = "alter table $table $action $field $type $null $key $extra ".($default?"default ".$dbh->quote($default):"")." $after";
1884 # warn "alter table $table drop primary key, $action $field $type $null $key $extra default $default $after";
1885 # something strange : for indexes UNIQUE, they are reported as primary key here.
1886 # but if you try to run with drop primary key, it fails.
1887 # thus, we run the query twice, one will fail, one will succeed.
1889 $query="alter table $table drop primary key, $action $field $type $null $key $extra ".($default?"default ".$dbh->quote($default):"")." $after";
1890 $query="alter table $table $action $field $type $null $key $extra ".($default?"default ".$dbh->quote($default):"")." $after";
1897 print "removing some unused data...\n";
1898 foreach my $table ( keys %linetodelete ) {
1899 foreach my $where ( @{linetodelete{$table}} ){
1900 print "DELETE FROM ".$table." where ".$where;
1902 my $sth = $dbh->prepare("DELETE FROM $table where $where");
1905 print "Error : $sth->errstr \n";
1910 # Populate tables with required data
1912 # synch table and deletedtable.
1913 foreach my $table (('borrowers','items','biblio','biblioitems')) {
1914 my %deletedborrowers;
1915 print "synch'ing $table and deleted$table\n";
1916 $sth = $dbh->prepare("show columns from deleted$table");
1918 while ( my ( $column, $type, $null, $key, $default, $extra ) = $sth->fetchrow ) {
1919 $deletedborrowers{$column}=1;
1921 $sth = $dbh->prepare("show columns from $table");
1924 while ( my ( $column, $type, $null, $key, $default, $extra ) = $sth->fetchrow ) {
1925 unless ($deletedborrowers{$column}) {
1926 my $newcol="alter table deleted$table add $column $type";
1927 if ($null eq 'YES') {
1928 $newcol .= " NULL ";
1930 $newcol .= " NOT NULL ";
1932 $newcol .= "default ".$dbh->quote($default) if $default;
1933 $newcol .= " after $previous" if $previous;
1935 print "creating column $column\n";
1941 # update publisheddate
1943 $sth = $dbh->prepare("select count(*) from serial where publisheddate is NULL");
1945 my ($emptypublished) = $sth->fetchrow;
1946 if ($emptypublished) {
1947 print "Updating publisheddate\n";
1948 $dbh->do("update serial set publisheddate=planneddate where publisheddate is NULL");
1950 foreach my $table ( keys %tabledata ) {
1951 print "Checking for data required in table $table...\n" unless $silent;
1952 my $tablerows = $tabledata{$table};
1953 foreach my $row (@$tablerows) {
1954 my $uniquefieldrequired = $row->{uniquefieldrequired};
1955 my $uniquevalue = $row->{$uniquefieldrequired};
1956 my $forceupdate = $row->{forceupdate};
1959 "select $uniquefieldrequired from $table where $uniquefieldrequired=?"
1961 $sth->execute($uniquevalue);
1963 foreach my $field (keys %$forceupdate) {
1964 if ($forceupdate->{$field}) {
1965 my $sth=$dbh->prepare("update systempreferences set $field=? where $uniquefieldrequired=?");
1966 $sth->execute($row->{$field}, $uniquevalue);
1970 print "Adding row to $table: " unless $silent;
1974 foreach my $field ( keys %$row ) {
1975 next if $field eq 'uniquefieldrequired';
1976 next if $field eq 'forceupdate';
1977 my $value = $row->{$field};
1978 push @values, $value;
1979 print " $field => $value" unless $silent;
1980 $fieldlist .= "$field,";
1981 $placeholders .= "?,";
1983 print "\n" unless $silent;
1984 $fieldlist =~ s/,$//;
1985 $placeholders =~ s/,$//;
1986 print "insert into $table ($fieldlist) values ($placeholders)";
1989 "insert into $table ($fieldlist) values ($placeholders)");
1990 $sth->execute(@values);
1996 # check indexes and create them when needed
1998 print "Checking for index required...\n" unless $silent;
1999 foreach my $table ( keys %indexes ) {
2001 # read all indexes from $table
2003 $sth = $dbh->prepare("show index from $table");
2005 my %existingindexes;
2006 while ( my ( $table, $non_unique, $key_name, $Seq_in_index, $Column_name, $Collation, $cardinality, $sub_part, $Packed, $comment ) = $sth->fetchrow ) {
2007 $existingindexes{$key_name} = 1;
2009 # read indexes to check
2010 my $tablerows = $indexes{$table};
2011 foreach my $row (@$tablerows) {
2012 my $key_name=$row->{indexname};
2013 if ($existingindexes{$key_name} eq 1) {
2014 # print "$key_name existing";
2016 print "\tCreating index $key_name in $table\n";
2018 if ($row->{indexname} eq 'PRIMARY') {
2019 $sql = "alter table $table ADD PRIMARY KEY ($row->{content})";
2021 $sql = "alter table $table ADD INDEX $key_name ($row->{content}) $row->{type}";
2024 print "Error $sql : $dbh->err \n" if $dbh->err;
2030 # check foreign keys and create them when needed
2032 print "Checking for foreign keys required...\n" unless $silent;
2033 foreach my $table ( keys %foreign_keys ) {
2035 # read all indexes from $table
2037 $sth = $dbh->prepare("show table status like '$table'");
2039 my $stat = $sth->fetchrow_hashref;
2040 # read indexes to check
2041 my $tablerows = $foreign_keys{$table};
2042 foreach my $row (@$tablerows) {
2043 my $foreign_table=$row->{foreigntable};
2044 if ($stat->{'Comment'} =~/$foreign_table/) {
2045 # print "$foreign_table existing\n";
2047 print "\tCreating foreign key $foreign_table in $table\n";
2048 # first, drop any orphan value in child table
2049 if ($row->{onDelete} ne "RESTRICT") {
2050 my $sql = "delete from $table where $row->{key} not in (select $row->{foreignkey} from $row->{foreigntable})";
2052 print "SQL ERROR: $sql : $dbh->err \n" if $dbh->err;
2054 my $sql="alter table $table ADD FOREIGN KEY $row->{key} ($row->{key}) REFERENCES $row->{foreigntable} ($row->{foreignkey})";
2055 $sql .= " on update ".$row->{onUpdate} if $row->{onUpdate};
2056 $sql .= " on delete ".$row->{onDelete} if $row->{onDelete};
2059 print "====================
2060 An error occured during :
2062 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).
2063 You can find those values with select
2064 \t$table.* from $table where $row->{key} not in (select $row->{foreignkey} from $row->{foreigntable})
2065 ====================\n
2071 # now drop useless tables
2072 foreach $table ( @TableToDelete ) {
2073 if ( $existingtables{$table} ) {
2074 print "Dropping unused table $table\n" if $debug and not $silent;
2075 $dbh->do("drop table $table");
2077 print "Error : $dbh->errstr \n";
2086 # create frameworkcode row in biblio table & fill it with marc_biblio.frameworkcode.
2089 # 1st, get how many biblio we will have to do...
2090 $sth = $dbh->prepare('select count(*) from marc_biblio');
2092 my ($totaltodo) = $sth->fetchrow;
2094 $sth = $dbh->prepare("show columns from biblio");
2097 my $bibliofwexist=0;
2098 while ( ( $column, $type, $null, $key, $default, $extra ) = $sth->fetchrow ){
2099 $bibliofwexist=1 if $column eq 'frameworkcode';
2101 unless ($bibliofwexist) {
2102 print "moving biblioframework to biblio table\n";
2103 $dbh->do('ALTER TABLE `biblio` ADD `frameworkcode` VARCHAR( 4 ) NOT NULL AFTER `biblionumber`');
2104 $sth = $dbh->prepare('select biblionumber,frameworkcode from marc_biblio');
2106 my $sth_update = $dbh->prepare('update biblio set frameworkcode=? where biblionumber=?');
2108 while (my ($biblionumber,$frameworkcode) = $sth->fetchrow) {
2109 $sth_update->execute($frameworkcode,$biblionumber);
2111 print "\r$totaldone / $totaltodo" unless ($totaldone % 100);
2116 # at last, remove useless fields
2117 foreach $table ( keys %uselessfields ) {
2118 my @fields = split /,/,$uselessfields{$table};
2121 foreach my $fieldtodrop (@fields) {
2122 $fieldtodrop =~ s/\t//g;
2123 $fieldtodrop =~ s/\n//g;
2125 $sth = $dbh->prepare("show columns from $table");
2127 while ( my ( $column, $type, $null, $key, $default, $extra ) = $sth->fetchrow )
2129 $exists =1 if ($column eq $fieldtodrop);
2132 print "deleting $fieldtodrop field in $table...\n" unless $silent;
2133 my $sth = $dbh->prepare("alter table $table drop $fieldtodrop");
2140 # Changing aqbookfund's primary key
2142 $sth=$dbh->prepare("ALTER TABLE `aqbookfund` DROP PRIMARY KEY , ADD PRIMARY KEY ( `bookfundid` , `branchcode` ) ;");
2146 print "upgrade to Koha 3.0 done\n";
2147 SetVersion ($DBversion);
2153 Deal with virtualshelves
2156 $DBversion = "3.00.00.001";
2157 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2158 # update virtualshelves table to
2160 $dbh->do("ALTER TABLE `bookshelf` RENAME `virtualshelves`");
2161 $dbh->do("ALTER TABLE `shelfcontents` RENAME `virtualshelfcontents`");
2162 $dbh->do("ALTER TABLE `virtualshelfcontents` ADD `biblionumber` INT( 11 ) NOT NULL");
2163 $dbh->do("UPDATE `virtualshelfcontents` SET biblionumber=(SELECT biblionumber FROM items WHERE items.itemnumber=virtualshelfcontents.itemnumber)");
2164 # drop all foreign keys : otherwise, we can't drop itemnumber field.
2165 DropAllForeignKeys('virtualshelfcontents');
2166 # create the new foreign keys (on biblionumber)
2167 $dbh->do("ALTER TABLE `virtualshelfcontents` ADD FOREIGN KEY biblionumber_fk (biblionumber) REFERENCES biblio (biblionumber) ON UPDATE CASCADE ON DELETE CASCADE");
2168 # re-create the foreign key on virtualshelf
2169 $dbh->do("ALTER TABLE `virtualshelfcontents` ADD FOREIGN KEY shelfnumber_fk (shelfnumber) REFERENCES virtualshelves (shelfnumber) ON UPDATE CASCADE ON DELETE CASCADE");
2170 # now we can drop the itemnumber column
2171 $dbh->do("ALTER TABLE `virtualshelfcontents` DROP `itemnumber`");
2172 print "Upgrade to $DBversion done (virtualshelves)\n";
2173 SetVersion ($DBversion);
2177 Deal with the sessions table that uses CGI::Session
2180 $DBversion = "3.00.00.002";
2181 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2182 $dbh->do("DROP TABLE sessions");
2183 $dbh->do("CREATE TABLE `sessions` (
2184 `id` char(32) NOT NULL,
2185 `a_session` text NOT NULL,
2186 UNIQUE KEY `id` (`id`)
2187 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;");
2188 print "Upgrade to $DBversion done (sessions uses CGI::session, new table structure for sessions)\n";
2189 SetVersion ($DBversion);
2193 Deal with the sessions table that uses CGI::Session
2196 $DBversion = "3.00.00.003";
2197 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2198 if (C4::Context->preference("opaclanguage") eq "fr") {
2199 $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')");
2201 $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')");
2203 print "Upgrade to $DBversion done (adding ReservesNeedReturns systempref, in circulation)\n";
2204 SetVersion ($DBversion);
2207 =item DropAllForeignKeys($table)
2209 Drop all foreign keys of the table $table
2213 sub DropAllForeignKeys {
2215 # get the table description
2216 my $sth = $dbh->prepare("SHOW CREATE TABLE $table");
2218 my $vsc_structure = $sth->fetchrow;
2219 # split on CONSTRAINT keyword
2220 my @fks = split /CONSTRAINT /,$vsc_structure;
2223 # isolate what is before FOREIGN KEY, if there is something, it's a foreign key to drop
2224 $_ = /(.*) FOREIGN KEY.*/;
2227 # we have found 1 foreign, drop it
2228 $dbh->do("ALTER TABLE $table DROP FOREIGN KEY $id");
2240 =item TransformToNum
2242 Transform the Koha version from a 4 parts string
2243 to a number, with just 1 .
2247 sub TransformToNum {
2248 my $version = shift;
2249 # remove the 3 last . to have a Perl number
2250 $version =~ s/(.*\..*)\.(.*)\.(.*)/$1$2$3/;
2255 set the DBversion in the systempreferences
2259 my $kohaversion = TransformToNum(shift);
2260 if (C4::Context->preference('Version')) {
2261 my $finish=$dbh->prepare("UPDATE systempreferences SET value=? WHERE variable='Version'");
2262 $finish->execute($kohaversion);
2264 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')");
2265 $finish->execute($kohaversion);
2271 # Revision 1.172 2007/07/19 10:21:22 hdl