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 => 'issue_date',
1050 field => 'homebranch',
1051 type => 'varchar(10)',
1058 field => 'holdingbranch',
1059 type => 'varchar(10)',
1067 type => 'varchar(10)',
1076 field => 'itemtype',
1077 type => 'varchar(10)',
1100 marc_subfield_structure => [
1102 field => 'defaultvalue',
1111 field => 'expirationdate',
1128 field => 'waitingdate',
1148 field => 'dateadded',
1149 type => 'timestamp',
1153 systempreferences => [
1163 field => 'explanation',
1185 # { indexname => 'index detail'
1189 { indexname => 'PRIMARY',
1195 { indexname => 'booksellerid',
1196 content => 'booksellerid',
1200 { indexname => 'basketno',
1201 content => 'basketno',
1204 aqorderbreakdown => [
1205 { indexname => 'ordernumber',
1206 content => 'ordernumber',
1208 { indexname => 'bookfundid',
1209 content => 'bookfundid',
1213 { indexname => 'isbn',
1216 { indexname => 'publishercode',
1217 content => 'publishercode',
1222 indexname => 'branchcode',
1223 content => 'branchcode',
1227 branchrelations => [
1229 indexname => 'PRIMARY',
1230 content => 'categorycode',
1234 branchrelations => [
1235 { indexname => 'PRIMARY',
1236 content => 'branchcode,categorycode',
1239 { indexname => 'branchcode',
1240 content => 'branchcode',
1242 { indexname => 'categorycode',
1243 content => 'categorycode',
1247 { indexname => 'PRIMARY',
1248 content => 'currency',
1254 indexname => 'categorycode',
1255 content => 'categorycode',
1259 { indexname => 'homebranch',
1260 content => 'homebranch',
1262 { indexname => 'holdingbranch',
1263 content => 'holdingbranch',
1268 indexname => 'itemtype',
1269 content => 'itemtype',
1273 { indexname => 'shelfnumber',
1274 content => 'shelfnumber',
1276 { indexname => 'itemnumber',
1277 content => 'itemnumber',
1281 { indexname => 'PRIMARY',
1288 my %foreign_keys = (
1290 # { key => 'the key in table' (must be indexed)
1291 # foreigntable => 'the foreigntable name', # (the parent)
1292 # foreignkey => 'the foreign key column(s)' # (in the parent)
1293 # onUpdate => 'CASCADE|SET NULL|NO ACTION| RESTRICT',
1294 # onDelete => 'CASCADE|SET NULL|NO ACTION| RESTRICT',
1297 branchrelations => [
1298 { key => 'branchcode',
1299 foreigntable => 'branches',
1300 foreignkey => 'branchcode',
1301 onUpdate => 'CASCADE',
1302 onDelete => 'CASCADE',
1304 { key => 'categorycode',
1305 foreigntable => 'branchcategories',
1306 foreignkey => 'categorycode',
1307 onUpdate => 'CASCADE',
1308 onDelete => 'CASCADE',
1312 { key => 'shelfnumber',
1313 foreigntable => 'virtualshelf',
1314 foreignkey => 'shelfnumber',
1315 onUpdate => 'CASCADE',
1316 onDelete => 'CASCADE',
1318 { key => 'itemnumber',
1319 foreigntable => 'items',
1320 foreignkey => 'itemnumber',
1321 onUpdate => 'CASCADE',
1322 onDelete => 'CASCADE',
1325 # onDelete is RESTRICT on reference tables (branches, itemtype) as we don't want items to be
1326 # easily deleted, but branches/itemtype not too easy to empty...
1328 { key => 'biblionumber',
1329 foreigntable => 'biblio',
1330 foreignkey => 'biblionumber',
1331 onUpdate => 'CASCADE',
1332 onDelete => 'CASCADE',
1334 { key => 'itemtype',
1335 foreigntable => 'itemtypes',
1336 foreignkey => 'itemtype',
1337 onUpdate => 'CASCADE',
1338 onDelete => 'RESTRICT',
1342 { key => 'biblioitemnumber',
1343 foreigntable => 'biblioitems',
1344 foreignkey => 'biblioitemnumber',
1345 onUpdate => 'CASCADE',
1346 onDelete => 'CASCADE',
1348 { key => 'homebranch',
1349 foreigntable => 'branches',
1350 foreignkey => 'branchcode',
1351 onUpdate => 'CASCADE',
1352 onDelete => 'RESTRICT',
1354 { key => 'holdingbranch',
1355 foreigntable => 'branches',
1356 foreignkey => 'branchcode',
1357 onUpdate => 'CASCADE',
1358 onDelete => 'RESTRICT',
1362 { key => 'booksellerid',
1363 foreigntable => 'aqbooksellers',
1365 onUpdate => 'CASCADE',
1366 onDelete => 'RESTRICT',
1370 { key => 'basketno',
1371 foreigntable => 'aqbasket',
1372 foreignkey => 'basketno',
1373 onUpdate => 'CASCADE',
1374 onDelete => 'CASCADE',
1376 { key => 'biblionumber',
1377 foreigntable => 'biblio',
1378 foreignkey => 'biblionumber',
1379 onUpdate => 'SET NULL',
1380 onDelete => 'SET NULL',
1384 { key => 'listprice',
1385 foreigntable => 'currency',
1386 foreignkey => 'currency',
1387 onUpdate => 'CASCADE',
1388 onDelete => 'CASCADE',
1390 { key => 'invoiceprice',
1391 foreigntable => 'currency',
1392 foreignkey => 'currency',
1393 onUpdate => 'CASCADE',
1394 onDelete => 'CASCADE',
1397 aqorderbreakdown => [
1398 { key => 'ordernumber',
1399 foreigntable => 'aqorders',
1400 foreignkey => 'ordernumber',
1401 onUpdate => 'CASCADE',
1402 onDelete => 'CASCADE',
1404 { key => 'bookfundid',
1405 foreigntable => 'aqbookfund',
1406 foreignkey => 'bookfundid',
1407 onUpdate => 'CASCADE',
1408 onDelete => 'CASCADE',
1411 branchtransfers => [
1412 { key => 'frombranch',
1413 foreigntable => 'branches',
1414 foreignkey => 'branchcode',
1415 onUpdate => 'CASCADE',
1416 onDelete => 'CASCADE',
1418 { key => 'tobranch',
1419 foreigntable => 'branches',
1420 foreignkey => 'branchcode',
1421 onUpdate => 'CASCADE',
1422 onDelete => 'CASCADE',
1424 { key => 'itemnumber',
1425 foreigntable => 'items',
1426 foreignkey => 'itemnumber',
1427 onUpdate => 'CASCADE',
1428 onDelete => 'CASCADE',
1432 { key => 'categorycode',
1433 foreigntable => 'categories',
1434 foreignkey => 'categorycode',
1435 onUpdate => 'CASCADE',
1436 onDelete => 'CASCADE',
1438 { key => 'itemtype',
1439 foreigntable => 'itemtypes',
1440 foreignkey => 'itemtype',
1441 onUpdate => 'CASCADE',
1442 onDelete => 'CASCADE',
1445 issues => [ # constraint is SET NULL : when a borrower or an item is deleted, we keep the issuing record
1447 { key => 'borrowernumber',
1448 foreigntable => 'borrowers',
1449 foreignkey => 'borrowernumber',
1450 onUpdate => 'SET NULL',
1451 onDelete => 'SET NULL',
1453 { key => 'itemnumber',
1454 foreigntable => 'items',
1455 foreignkey => 'itemnumber',
1456 onUpdate => 'SET NULL',
1457 onDelete => 'SET NULL',
1461 { key => 'borrowernumber',
1462 foreigntable => 'borrowers',
1463 foreignkey => 'borrowernumber',
1464 onUpdate => 'CASCADE',
1465 onDelete => 'CASCADE',
1467 { key => 'biblionumber',
1468 foreigntable => 'biblio',
1469 foreignkey => 'biblionumber',
1470 onUpdate => 'CASCADE',
1471 onDelete => 'CASCADE',
1473 { key => 'itemnumber',
1474 foreigntable => 'items',
1475 foreignkey => 'itemnumber',
1476 onUpdate => 'CASCADE',
1477 onDelete => 'CASCADE',
1479 { key => 'branchcode',
1480 foreigntable => 'branches',
1481 foreignkey => 'branchcode',
1482 onUpdate => 'CASCADE',
1483 onDelete => 'CASCADE',
1486 borrowers => [ # foreign keys are RESTRICT as we don't want to delete borrowers when a branch is deleted
1487 # but prevent deleting a branch as soon as it has 1 borrower !
1488 { key => 'categorycode',
1489 foreigntable => 'categories',
1490 foreignkey => 'categorycode',
1491 onUpdate => 'RESTRICT',
1492 onDelete => 'RESTRICT',
1494 { key => 'branchcode',
1495 foreigntable => 'branches',
1496 foreignkey => 'branchcode',
1497 onUpdate => 'RESTRICT',
1498 onDelete => 'RESTRICT',
1501 deletedborrowers => [ # foreign keys are RESTRICT as we don't want to delete borrowers when a branch is deleted
1502 # but prevent deleting a branch as soon as it has 1 borrower !
1503 { key => 'categorycode',
1504 foreigntable => 'categories',
1505 foreignkey => 'categorycode',
1506 onUpdate => 'RESTRICT',
1507 onDelete => 'RESTRICT',
1509 { key => 'branchcode',
1510 foreigntable => 'branches',
1511 foreignkey => 'branchcode',
1512 onUpdate => 'RESTRICT',
1513 onDelete => 'RESTRICT',
1517 { key => 'borrowernumber',
1518 foreigntable => 'borrowers',
1519 foreignkey => 'borrowernumber',
1520 onUpdate => 'CASCADE',
1521 onDelete => 'CASCADE',
1523 { key => 'itemnumber',
1524 foreigntable => 'items',
1525 foreignkey => 'itemnumber',
1526 onUpdate => 'SET NULL',
1527 onDelete => 'SET NULL',
1530 auth_tag_structure => [
1531 { key => 'authtypecode',
1532 foreigntable => 'auth_types',
1533 foreignkey => 'authtypecode',
1534 onUpdate => 'CASCADE',
1535 onDelete => 'CASCADE',
1538 # FIXME : don't constraint auth_*_table and auth_word, as they may be replaced by zebra
1543 my %column_change = (
1547 from => 'emailaddress',
1552 from => 'streetaddress',
1554 after => 'initials',
1557 from => 'faxnumber',
1562 from => 'textmessaging',
1568 to => 'contactnote',
1569 after => 'opacnote',
1572 from => 'physstreet',
1577 from => 'streetcity',
1579 after => 'B_address',
1592 from => 'homezipcode',
1599 after => 'B_zipcode',
1604 after => 'dateenrolled',
1607 from => 'guarantor',
1608 to => 'guarantorid',
1609 after => 'contactname',
1612 from => 'altrelationship',
1613 to => 'relationship',
1614 after => 'borrowernotes',
1618 deletedborrowers => [
1620 from => 'emailaddress',
1625 from => 'streetaddress',
1627 after => 'initials',
1630 from => 'faxnumber',
1635 from => 'textmessaging',
1641 to => 'contactnote',
1642 after => 'opacnote',
1645 from => 'physstreet',
1650 from => 'streetcity',
1652 after => 'B_address',
1665 from => 'homezipcode',
1672 after => 'B_zipcode',
1677 after => 'dateenrolled',
1680 from => 'guarantor',
1681 to => 'guarantorid',
1682 after => 'contactname',
1685 from => 'altrelationship',
1686 to => 'relationship',
1687 after => 'borrowernotes',
1693 # MOVE all tables TO UTF-8 and innoDB
1694 $sth = $dbh->prepare("show table status");
1696 while ( my $table = $sth->fetchrow_hashref ) {
1697 next if $table->{Name} eq 'marc_word';
1698 next if $table->{Name} eq 'marc_subfield_table';
1699 next if $table->{Name} eq 'auth_word';
1700 next if $table->{Name} eq 'auth_subfield_table';
1701 if ($table->{Engine} ne 'InnoDB') {
1702 print "moving $table->{Name} to InnoDB\n";
1703 $dbh->do("ALTER TABLE $table->{Name} TYPE = innodb");
1705 unless ($table->{Collation} =~ /^utf8/) {
1706 print "moving $table->{Name} to utf8\n";
1707 $dbh->do("ALTER TABLE $table->{Name} CONVERT TO CHARACTER SET utf8");
1708 $dbh->do("ALTER TABLE $table->{Name} DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci");
1709 # 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 !
1715 foreach my $table (keys %column_change) {
1716 $sth = $dbh->prepare("show columns from $table");
1719 while ( ( $column, $type, $null, $key, $default, $extra ) = $sth->fetchrow )
1721 $types{$column}->{type} ="$type";
1722 $types{$column}->{null} = "$null";
1723 $types{$column}->{key} = "$key";
1724 $types{$column}->{default} = "$default";
1725 $types{$column}->{extra} = "$extra";
1727 my $tablerows = $column_change{$table};
1728 foreach my $row ( @$tablerows ) {
1729 if ($types{$row->{from}}->{type}) {
1730 print "altering $table $row->{from} to $row->{to}\n";
1731 # ALTER TABLE `borrowers` CHANGE `faxnumber` `fax` TEXT CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL
1732 # alter table `borrowers` change `faxnumber` `fax` type text null after phone
1734 "alter table `$table` change `$row->{from}` `$row->{to}` $types{$row->{from}}->{type} ".
1735 ($types{$row->{from}}->{null} eq 'YES'?" NULL":" NOT NULL").
1736 ($types{$row->{from}}->{default}?" default ".$types{$row->{from}}->{default}:"").
1737 "$types{$row->{from}}->{extra} after $row->{after} ";
1744 # Enter here the field you want to delete from DB.
1745 # FIXME :: there is a %uselessfield before which seems doing the same things.
1746 my %fieldtodelete = (
1747 # tablename => [fieldname1,fieldname2,...]
1751 print "removing some unused fields...\n";
1752 foreach my $table ( keys %fieldtodelete ) {
1753 foreach my $field ( @{$fieldtodelete{$table}} ){
1754 print "removing ".$field." from ".$table;
1755 my $sth = $dbh->prepare("ALTER TABLE $table DROP $field");
1758 print "Error : $sth->errstr \n";
1763 # Enter here the line you want to remove from DB.
1764 my %linetodelete = (
1765 # table name => where clause.
1766 userflags => "bit = 8", # delete the 'reserveforself' flags
1770 #-------------------
1775 # Get version of MySQL database engine.
1776 my $mysqlversion = `mysqld --version`;
1777 $mysqlversion =~ /Ver (\S*) /;
1779 if ( $mysqlversion ge '3.23' ) {
1780 print "Could convert to MyISAM database tables...\n" unless $silent;
1783 #---------------------------------
1786 # Collect all tables into a list
1787 $sth = $dbh->prepare("show tables");
1789 while ( my ($table) = $sth->fetchrow ) {
1790 $existingtables{$table} = 1;
1794 # Now add any missing tables
1795 foreach $table ( keys %requiretables ) {
1796 unless ( $existingtables{$table} ) {
1797 print "Adding $table table...\n" unless $silent;
1798 my $sth = $dbh->prepare("create table $table $requiretables{$table}");
1801 print "Error : $sth->errstr \n";
1807 #---------------------------------
1810 foreach $table ( keys %requirefields ) {
1811 print "Check table $table\n" if $debug and not $silent;
1812 $sth = $dbh->prepare("show columns from $table");
1815 while ( ( $column, $type, $null, $key, $default, $extra ) = $sth->fetchrow )
1817 $types{$column} = $type;
1819 foreach $column ( keys %{ $requirefields{$table} } ) {
1820 print " Check column $column [$types{$column}]\n" if $debug and not $silent;
1821 if ( !$types{$column} ) {
1823 # column doesn't exist
1824 print "Adding $column field to $table table...\n" unless $silent;
1825 $query = "alter table $table
1826 add column $column " . $requirefields{$table}->{$column};
1827 print "Execute: $query\n" if $debug;
1828 my $sti = $dbh->prepare($query);
1831 print "**Error : $sti->errstr \n";
1838 foreach $table ( keys %fielddefinitions ) {
1839 print "Check table $table\n" if $debug;
1840 $sth = $dbh->prepare("show columns from $table");
1843 while ( ( $column, $type, $null, $key, $default, $extra ) = $sth->fetchrow )
1845 $definitions->{$column}->{type} = $type;
1846 $definitions->{$column}->{null} = $null;
1847 $definitions->{$column}->{null} = 'NULL' if $null eq 'YES';
1848 $definitions->{$column}->{key} = $key;
1849 $definitions->{$column}->{default} = $default;
1850 $definitions->{$column}->{extra} = $extra;
1852 my $fieldrow = $fielddefinitions{$table};
1853 foreach my $row (@$fieldrow) {
1854 my $field = $row->{field};
1855 my $type = $row->{type};
1856 my $null = $row->{null};
1857 # $null = 'YES' if $row->{null} eq 'NULL';
1858 my $key = $row->{key};
1859 my $default = $row->{default};
1860 # $default="''" unless $default;
1861 my $extra = $row->{extra};
1862 my $def = $definitions->{$field};
1863 my $after = ($row->{after}?" after ".$row->{after}:"");
1865 unless ( $type eq $def->{type}
1866 && $null eq $def->{null}
1867 && $key eq $def->{key}
1868 && $extra eq $def->{extra} )
1870 if ( $null eq '' ) {
1873 if ( $key eq 'PRI' ) {
1874 $key = 'PRIMARY KEY';
1876 unless ( $extra eq 'auto_increment' ) {
1880 # if it's a new column use "add", if it's an old one, use "change".
1882 if ($definitions->{$field}->{type}) {
1883 $action="change $field"
1887 # if it's a primary key, drop the previous pk, before altering the table
1888 print " alter or create $field in $table\n" unless $silent;
1890 if ($key ne 'PRIMARY KEY') {
1891 # warn "alter table $table $action $field $type $null $key $extra default $default $after";
1892 $query = "alter table $table $action $field $type $null $key $extra ".($default?"default ".$dbh->quote($default):"")." $after";
1894 # warn "alter table $table drop primary key, $action $field $type $null $key $extra default $default $after";
1895 # something strange : for indexes UNIQUE, they are reported as primary key here.
1896 # but if you try to run with drop primary key, it fails.
1897 # thus, we run the query twice, one will fail, one will succeed.
1899 $query="alter table $table drop primary key, $action $field $type $null $key $extra ".($default?"default ".$dbh->quote($default):"")." $after";
1900 $query="alter table $table $action $field $type $null $key $extra ".($default?"default ".$dbh->quote($default):"")." $after";
1907 print "removing some unused data...\n";
1908 foreach my $table ( keys %linetodelete ) {
1909 foreach my $where ( @{linetodelete{$table}} ){
1910 print "DELETE FROM ".$table." where ".$where;
1912 my $sth = $dbh->prepare("DELETE FROM $table where $where");
1915 print "Error : $sth->errstr \n";
1920 # Populate tables with required data
1922 # synch table and deletedtable.
1923 foreach my $table (('borrowers','items','biblio','biblioitems')) {
1924 my %deletedborrowers;
1925 print "synch'ing $table and deleted$table\n";
1926 $sth = $dbh->prepare("show columns from deleted$table");
1928 while ( my ( $column, $type, $null, $key, $default, $extra ) = $sth->fetchrow ) {
1929 $deletedborrowers{$column}=1;
1931 $sth = $dbh->prepare("show columns from $table");
1934 while ( my ( $column, $type, $null, $key, $default, $extra ) = $sth->fetchrow ) {
1935 unless ($deletedborrowers{$column}) {
1936 my $newcol="alter table deleted$table add $column $type";
1937 if ($null eq 'YES') {
1938 $newcol .= " NULL ";
1940 $newcol .= " NOT NULL ";
1942 $newcol .= "default ".$dbh->quote($default) if $default;
1943 $newcol .= " after $previous" if $previous;
1945 print "creating column $column\n";
1951 # update publisheddate
1953 $sth = $dbh->prepare("select count(*) from serial where publisheddate is NULL");
1955 my ($emptypublished) = $sth->fetchrow;
1956 if ($emptypublished) {
1957 print "Updating publisheddate\n";
1958 $dbh->do("update serial set publisheddate=planneddate where publisheddate is NULL");
1960 foreach my $table ( keys %tabledata ) {
1961 print "Checking for data required in table $table...\n" unless $silent;
1962 my $tablerows = $tabledata{$table};
1963 foreach my $row (@$tablerows) {
1964 my $uniquefieldrequired = $row->{uniquefieldrequired};
1965 my $uniquevalue = $row->{$uniquefieldrequired};
1966 my $forceupdate = $row->{forceupdate};
1969 "select $uniquefieldrequired from $table where $uniquefieldrequired=?"
1971 $sth->execute($uniquevalue);
1973 foreach my $field (keys %$forceupdate) {
1974 if ($forceupdate->{$field}) {
1975 my $sth=$dbh->prepare("update systempreferences set $field=? where $uniquefieldrequired=?");
1976 $sth->execute($row->{$field}, $uniquevalue);
1980 print "Adding row to $table: " unless $silent;
1984 foreach my $field ( keys %$row ) {
1985 next if $field eq 'uniquefieldrequired';
1986 next if $field eq 'forceupdate';
1987 my $value = $row->{$field};
1988 push @values, $value;
1989 print " $field => $value" unless $silent;
1990 $fieldlist .= "$field,";
1991 $placeholders .= "?,";
1993 print "\n" unless $silent;
1994 $fieldlist =~ s/,$//;
1995 $placeholders =~ s/,$//;
1996 print "insert into $table ($fieldlist) values ($placeholders)";
1999 "insert into $table ($fieldlist) values ($placeholders)");
2000 $sth->execute(@values);
2006 # check indexes and create them when needed
2008 print "Checking for index required...\n" unless $silent;
2009 foreach my $table ( keys %indexes ) {
2011 # read all indexes from $table
2013 $sth = $dbh->prepare("show index from $table");
2015 my %existingindexes;
2016 while ( my ( $table, $non_unique, $key_name, $Seq_in_index, $Column_name, $Collation, $cardinality, $sub_part, $Packed, $comment ) = $sth->fetchrow ) {
2017 $existingindexes{$key_name} = 1;
2019 # read indexes to check
2020 my $tablerows = $indexes{$table};
2021 foreach my $row (@$tablerows) {
2022 my $key_name=$row->{indexname};
2023 if ($existingindexes{$key_name} eq 1) {
2024 # print "$key_name existing";
2026 print "\tCreating index $key_name in $table\n";
2028 if ($row->{indexname} eq 'PRIMARY') {
2029 $sql = "alter table $table ADD PRIMARY KEY ($row->{content})";
2031 $sql = "alter table $table ADD INDEX $key_name ($row->{content}) $row->{type}";
2034 print "Error $sql : $dbh->err \n" if $dbh->err;
2040 # check foreign keys and create them when needed
2042 print "Checking for foreign keys required...\n" unless $silent;
2043 foreach my $table ( keys %foreign_keys ) {
2045 # read all indexes from $table
2047 $sth = $dbh->prepare("show table status like '$table'");
2049 my $stat = $sth->fetchrow_hashref;
2050 # read indexes to check
2051 my $tablerows = $foreign_keys{$table};
2052 foreach my $row (@$tablerows) {
2053 my $foreign_table=$row->{foreigntable};
2054 if ($stat->{'Comment'} =~/$foreign_table/) {
2055 # print "$foreign_table existing\n";
2057 print "\tCreating foreign key $foreign_table in $table\n";
2058 # first, drop any orphan value in child table
2059 if ($row->{onDelete} ne "RESTRICT") {
2060 my $sql = "delete from $table where $row->{key} not in (select $row->{foreignkey} from $row->{foreigntable})";
2062 print "SQL ERROR: $sql : $dbh->err \n" if $dbh->err;
2064 my $sql="alter table $table ADD FOREIGN KEY $row->{key} ($row->{key}) REFERENCES $row->{foreigntable} ($row->{foreignkey})";
2065 $sql .= " on update ".$row->{onUpdate} if $row->{onUpdate};
2066 $sql .= " on delete ".$row->{onDelete} if $row->{onDelete};
2069 print "====================
2070 An error occured during :
2072 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).
2073 You can find those values with select
2074 \t$table.* from $table where $row->{key} not in (select $row->{foreignkey} from $row->{foreigntable})
2075 ====================\n
2081 # now drop useless tables
2082 foreach $table ( @TableToDelete ) {
2083 if ( $existingtables{$table} ) {
2084 print "Dropping unused table $table\n" if $debug and not $silent;
2085 $dbh->do("drop table $table");
2087 print "Error : $dbh->errstr \n";
2096 # create frameworkcode row in biblio table & fill it with marc_biblio.frameworkcode.
2099 # 1st, get how many biblio we will have to do...
2100 $sth = $dbh->prepare('select count(*) from marc_biblio');
2102 my ($totaltodo) = $sth->fetchrow;
2104 $sth = $dbh->prepare("show columns from biblio");
2107 my $bibliofwexist=0;
2108 while ( ( $column, $type, $null, $key, $default, $extra ) = $sth->fetchrow ){
2109 $bibliofwexist=1 if $column eq 'frameworkcode';
2111 unless ($bibliofwexist) {
2112 print "moving biblioframework to biblio table\n";
2113 $dbh->do('ALTER TABLE `biblio` ADD `frameworkcode` VARCHAR( 4 ) NOT NULL AFTER `biblionumber`');
2114 $sth = $dbh->prepare('select biblionumber,frameworkcode from marc_biblio');
2116 my $sth_update = $dbh->prepare('update biblio set frameworkcode=? where biblionumber=?');
2118 while (my ($biblionumber,$frameworkcode) = $sth->fetchrow) {
2119 $sth_update->execute($frameworkcode,$biblionumber);
2121 print "\r$totaldone / $totaltodo" unless ($totaldone % 100);
2126 # at last, remove useless fields
2127 foreach $table ( keys %uselessfields ) {
2128 my @fields = split /,/,$uselessfields{$table};
2131 foreach my $fieldtodrop (@fields) {
2132 $fieldtodrop =~ s/\t//g;
2133 $fieldtodrop =~ s/\n//g;
2135 $sth = $dbh->prepare("show columns from $table");
2137 while ( my ( $column, $type, $null, $key, $default, $extra ) = $sth->fetchrow )
2139 $exists =1 if ($column eq $fieldtodrop);
2142 print "deleting $fieldtodrop field in $table...\n" unless $silent;
2143 my $sth = $dbh->prepare("alter table $table drop $fieldtodrop");
2150 # Changing aqbookfund's primary key
2152 $sth=$dbh->prepare("ALTER TABLE `aqbookfund` DROP PRIMARY KEY , ADD PRIMARY KEY ( `bookfundid` , `branchcode` ) ;");
2156 print "upgrade to Koha 3.0 done\n";
2157 SetVersion ($DBversion);
2163 Deal with virtualshelves
2166 $DBversion = "3.00.00.001";
2167 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2168 # update virtualshelves table to
2170 $dbh->do("ALTER TABLE `bookshelf` RENAME `virtualshelves`");
2171 $dbh->do("ALTER TABLE `shelfcontents` RENAME `virtualshelfcontents`");
2172 $dbh->do("ALTER TABLE `virtualshelfcontents` ADD `biblionumber` INT( 11 ) NOT NULL");
2173 $dbh->do("UPDATE `virtualshelfcontents` SET biblionumber=(SELECT biblionumber FROM items WHERE items.itemnumber=virtualshelfcontents.itemnumber)");
2174 # drop all foreign keys : otherwise, we can't drop itemnumber field.
2175 DropAllForeignKeys('virtualshelfcontents');
2176 # create the new foreign keys (on biblionumber)
2177 $dbh->do("ALTER TABLE `virtualshelfcontents` ADD FOREIGN KEY biblionumber_fk (biblionumber) REFERENCES biblio (biblionumber) ON UPDATE CASCADE ON DELETE CASCADE");
2178 # re-create the foreign key on virtualshelf
2179 $dbh->do("ALTER TABLE `virtualshelfcontents` ADD FOREIGN KEY shelfnumber_fk (shelfnumber) REFERENCES virtualshelves (shelfnumber) ON UPDATE CASCADE ON DELETE CASCADE");
2180 # now we can drop the itemnumber column
2181 $dbh->do("ALTER TABLE `virtualshelfcontents` DROP `itemnumber`");
2182 print "Upgrade to $DBversion done (virtualshelves)\n";
2183 SetVersion ($DBversion);
2187 $DBversion = "3.00.00.002";
2188 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2189 $dbh->do("DROP TABLE sessions");
2190 $dbh->do("CREATE TABLE `sessions` (
2191 `id` char(32) NOT NULL,
2192 `a_session` text NOT NULL,
2193 UNIQUE KEY `id` (`id`)
2194 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;");
2195 print "Upgrade to $DBversion done (sessions uses CGI::session, new table structure for sessions)\n";
2196 SetVersion ($DBversion);
2200 $DBversion = "3.00.00.003";
2201 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2202 if (C4::Context->preference("opaclanguage") eq "fr") {
2203 $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')");
2205 $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')");
2207 print "Upgrade to $DBversion done (adding ReservesNeedReturns systempref, in circulation)\n";
2208 SetVersion ($DBversion);
2212 $DBversion = "3.00.00.004";
2213 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2214 $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')");
2215 print "Upgrade to $DBversion done (adding DebugLevel systempref, in 'Admin' tab)\n";
2216 SetVersion ($DBversion);
2219 $DBversion = "3.00.00.005";
2220 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2221 $dbh->do("CREATE TABLE `tags` (
2222 `entry` varchar(255) NOT NULL default '',
2223 `weight` bigint(20) NOT NULL default 0,
2224 PRIMARY KEY (`entry`)
2225 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2227 $dbh->do("CREATE TABLE `nozebra` (
2228 `server` varchar(20) NOT NULL,
2229 `indexname` varchar(40) NOT NULL,
2230 `value` varchar(250) NOT NULL,
2231 `biblionumbers` longtext NOT NULL,
2232 KEY `indexname` (`server`,`indexname`),
2233 KEY `value` (`server`,`value`))
2234 ENGINE=InnoDB DEFAULT CHARSET=utf8;
2236 print "Upgrade to $DBversion done (adding tags and nozebra tables )\n";
2237 SetVersion ($DBversion);
2240 $DBversion = "3.00.00.006";
2241 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2242 $dbh->do("UPDATE issues SET issuedate=timestamp WHERE issuedate='0000-00-00'");
2243 print "Upgrade to $DBversion done (filled issues.issuedate with timestamp)\n";
2244 SetVersion ($DBversion);
2248 =item DropAllForeignKeys($table)
2250 Drop all foreign keys of the table $table
2254 sub DropAllForeignKeys {
2256 # get the table description
2257 my $sth = $dbh->prepare("SHOW CREATE TABLE $table");
2259 my $vsc_structure = $sth->fetchrow;
2260 # split on CONSTRAINT keyword
2261 my @fks = split /CONSTRAINT /,$vsc_structure;
2264 # isolate what is before FOREIGN KEY, if there is something, it's a foreign key to drop
2265 $_ = /(.*) FOREIGN KEY.*/;
2268 # we have found 1 foreign, drop it
2269 $dbh->do("ALTER TABLE $table DROP FOREIGN KEY $id");
2281 =item TransformToNum
2283 Transform the Koha version from a 4 parts string
2284 to a number, with just 1 .
2288 sub TransformToNum {
2289 my $version = shift;
2290 # remove the 3 last . to have a Perl number
2291 $version =~ s/(.*\..*)\.(.*)\.(.*)/$1$2$3/;
2296 set the DBversion in the systempreferences
2300 my $kohaversion = TransformToNum(shift);
2301 if (C4::Context->preference('Version')) {
2302 my $finish=$dbh->prepare("UPDATE systempreferences SET value=? WHERE variable='Version'");
2303 $finish->execute($kohaversion);
2305 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')");
2306 $finish->execute($kohaversion);
2312 # Revision 1.172 2007/07/19 10:21:22 hdl