5 # This script checks for required updates to the database.
7 # Part of the Koha Library Software www.koha.org
8 # Licensed under the GPL.
11 # - Would also be a good idea to offer to do a backup at this time...
13 # NOTE: If you do something more than once in here, make it table driven.
15 # NOTE: Please keep the version in kohaversion.pl up-to-date!
28 use MARC::File::XML ( BinaryEncoding => 'utf8' );
30 # FIXME - The user might be installing a new database, so can't rely
31 # on /etc/koha.conf anyway.
38 %existingtables, # tables already in database
42 $type, $null, $key, $default, $extra,
43 $prefitem, # preference item in systempreferences table
50 my $dbh = C4::Context->dbh;
51 $|=1; # flushes output
55 Deal with virtualshelves
59 my $DBversion = '3.00.01.000';
60 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
61 print "Upgrade to $DBversion done (start of 3.0.1)\n";
62 SetVersion ($DBversion);
65 $DBversion = '3.00.01.001';
66 if ( C4::Context->preference('Version') < TransformToNum($DBversion) ) {
67 # use statistics where available
69 ALTER TABLE statistics ADD KEY tmp_stats (type, itemnumber, borrowernumber)
77 AND itemnumber = iss.itemnumber
78 AND borrowernumber = iss.borrowernumber
80 WHERE issuedate IS NULL;
82 $dbh->do("ALTER TABLE statistics DROP KEY tmp_stats");
84 # default to last renewal date
87 SET issuedate = lastreneweddate
88 WHERE issuedate IS NULL
89 and lastreneweddate IS NOT NULL
92 my $num_bad_issuedates = $dbh->selectrow_array("SELECT COUNT(*) FROM issues WHERE issuedate IS NULL");
93 if ($num_bad_issuedates > 0) {
94 print STDERR "After the upgrade to $DBversion, there are still $num_bad_issuedates loan(s) with a NULL (blank) loan date. ",
95 "Please check the issues table in your database.";
97 print "Upgrade to $DBversion done (bug 2582: set null issues.issuedate to lastreneweddate)";
98 SetVersion($DBversion);
101 $DBversion = "3.00.01.002";
102 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
103 $dbh->do("INSERT INTO systempreferences (variable,value,explanation,options,type) VALUES('AllowRenewalLimitOverride', '0', 'if ON, allows renewal limits to be overridden on the circulation screen',NULL,'YesNo')");
104 print "Upgrade to $DBversion done (add new syspref)\n";
105 SetVersion ($DBversion);
108 $DBversion = "3.00.01.003";
109 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
110 my $search=$dbh->selectall_arrayref("select * from systempreferences where variable='dontmerge'");
112 my $search=$dbh->selectall_arrayref("select * from systempreferences where variable='MergeAuthoritiesOnUpdate'");
114 $dbh->do("DELETE FROM systempreferences set variable='dontmerge'");
117 $dbh->do("UPDATE systempreferences set variable='MergeAuthoritiesOnUpdate' ,value=1-value*1 WHERE variable='dontmerge'");
121 $dbh->do("INSERT INTO systempreferences (variable,value,explanation,options,type) VALUES('MergeAuthoritiesOnUpdate', '1', 'if ON, Updating authorities will automatically updates biblios',NULL,'YesNo')");
123 print "Upgrade to $DBversion done (add new syspref MergeAuthoritiesOnUpdate)\n";
124 SetVersion ($DBversion);
127 $DBversion = "3.00.01.004";
128 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
129 if (lc(C4::Context->preference('marcflavour')) eq "unimarc"){
130 $dbh->do("INSERT IGNORE INTO `marc_tag_structure` (`tagfield`, `liblibrarian`, `libopac`, `repeatable`, `mandatory`, `authorised_value`, `frameworkcode`) VALUES ('099', 'Informations locales', '', 0, 0, '', '');");
131 $dbh->do("INSERT IGNORE INTO `marc_tag_structure` (`frameworkcode`,`tagfield`, `liblibrarian`, `libopac`, `repeatable`, `mandatory`, `authorised_value`) SELECT DISTINCT(frameworkcode),'099', 'Informations locales', '', 0, 0, '' from biblio_framework");
132 $dbh->do(<<ENDOFSQL);
133 INSERT IGNORE INTO marc_subfield_structure (`tagfield`, `tagsubfield`, `liblibrarian`, `libopac`, `repeatable`, `mandatory`, `kohafield`, `tab`, `authorised_value`, `authtypecode`, `value_builder`, `isurl`, `hidden`, `seealso`, `link`, `defaultvalue`,frameworkcode )
134 VALUES ('099', 'c', 'date creation notice (koha)', '', 0, 0, 'biblio.datecreated', -1, '', '', '', NULL, 0, '', '', NULL, ''),
135 ('099', 'd', 'date modification notice (koha)', '', 0, 0, 'biblio.timestamp', -1, '', '', '', NULL, 0, '', '', NULL, ''),
136 ('995', '2', 'Perdu', '', 0, 0, 'items.itemlost', 10, '', '', '', NULL, 1, '', NULL, NULL, '');
138 $dbh->do(<<ENDOFSQL1);
139 INSERT IGNORE INTO marc_subfield_structure (`frameworkcode`,`tagfield`, `tagsubfield`, `liblibrarian`, `libopac`, `repeatable`, `mandatory`, `kohafield`, `tab`, `authorised_value`, `authtypecode`, `value_builder`, `isurl`, `hidden`, seealso, link, defaultvalue )
140 SELECT DISTINCT(frameworkcode), '099', 'c', 'date creation notice (koha)', '', 0, 0, 'biblio.datecreated', -1, '', '', '', NULL, 0, '', '', NULL from biblio_framework;
142 $dbh->do(<<ENDOFSQL2);
143 INSERT IGNORE INTO marc_subfield_structure (`frameworkcode`,`tagfield`, `tagsubfield`, `liblibrarian`, `libopac`, `repeatable`, `mandatory`, `kohafield`, `tab`, `authorised_value`, `authtypecode`, `value_builder`, `isurl`, `hidden`, seealso, link, defaultvalue )
144 SELECT DISTINCT(frameworkcode), '099', 'd', 'date modification notice (koha)', '', 0, 0, 'biblio.timestamp', -1, '', '', '', NULL, 0, '', '', NULL from biblio_framework;
146 $dbh->do(<<ENDOFSQL3);
147 INSERT IGNORE INTO marc_subfield_structure (`frameworkcode`,`tagfield`, `tagsubfield`, `liblibrarian`, `libopac`, `repeatable`, `mandatory`, `kohafield`, `tab`, `authorised_value`, `authtypecode`, `value_builder`, `isurl`, `hidden`, seealso, link, defaultvalue )
148 SELECT DISTINCT(frameworkcode), '995', '2', 'Perdu', '', 0, 0, 'items.itemlost', 10, '', '', '', NULL, 1, '', NULL, NULL from biblio_framework;
150 print "Upgrade to $DBversion done (updates MARC framework structure)\n";
152 SetVersion ($DBversion);
155 $DBversion = "3.00.01.005";
156 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
157 $dbh->do(<<ENDOFNOTFORLOANOVERRIDE);
158 INSERT INTO systempreferences (variable,value,explanation,options,type) VALUES('AllowNotForLoanOverride', '0', 'if ON, enables the librarian to choose when they want to check out a notForLoan regular item',NULL,'YesNo')
159 ENDOFNOTFORLOANOVERRIDE
160 print "Upgrade to $DBversion done (Adding AllowNotForLoanOverride System preference)\n";
163 $DBversion = "3.00.01.005";
164 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
165 $dbh->do("ALTER TABLE issuingrules ADD COLUMN `finedays` int(11) default NULL AFTER `fine`");
166 print "Upgrade to $DBversion done (Adding a field in issuingrules table)\n";
167 SetVersion ($DBversion);
170 $DBversion = "3.00.01.006";
171 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
172 $dbh->do("ALTER TABLE `aqbudget` CHANGE `aqbudgetid` `aqbudgetid` INT( 11 ) NOT NULL AUTO_INCREMENT");
173 print "Upgrade to $DBversion done (Change the field)\n";
174 SetVersion ($DBversion);
177 $DBversion = "3.00.01.007";
178 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
179 $dbh->do(<<ENDOFRENEWAL);
180 INSERT INTO systempreferences (variable,value,explanation,options,type) VALUES('RenewalPeriodBase', 'date_due', 'Set whether the renewal date should be counted from the date_due or from the moment the Patron asks for renewal ','date_due|now','Choice');
182 print "Upgrade to $DBversion done (Change the field)\n";
183 SetVersion ($DBversion);
185 $DBversion = "3.00.02.001";
187 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
189 INSERT INTO `letter` (module, code, name, title, content)
190 VALUES('reserves', 'HOLD', 'Hold Available for Pickup', 'Hold Available for Pickup at <<branches.branchname>>', 'Dear <<borrowers.firstname>> <<borrowers.surname>>,\r\n\r\nYou have a hold available for pickup as of <<reserves.waitingdate>>:\r\n\r\nTitle: <<biblio.title>>\r\nAuthor: <<biblio.author>>\r\nCopy: <<items.copynumber>>\r\nLocation: <<branches.branchname>>\r\n<<branches.branchaddress1>>\r\n<<branches.branchaddress2>>\r\n<<branches.branchaddress3>>')
192 $dbh->do("INSERT INTO `message_attributes` (message_attribute_id, message_name, takes_days) values(4, 'Hold Filled', 0)");
193 $dbh->do("INSERT INTO `message_transports` (message_attribute_id, message_transport_type, is_digest, letter_module, letter_code) values(4, 'sms', 0, 'reserves', 'HOLD')");
194 $dbh->do("INSERT INTO `message_transports` (message_attribute_id, message_transport_type, is_digest, letter_module, letter_code) values(4, 'email', 0, 'reserves', 'HOLD')");
195 print "Upgrade to $DBversion done (Add letter for holds notifications)\n";
196 SetVersion ($DBversion);
199 $DBversion = "3.00.02.002";
200 #$DBversion = '3.01.00.006';
201 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
202 $dbh->do("ALTER TABLE `biblioitems` ADD KEY issn (issn)");
203 print "Upgrade to $DBversion done (add index on biblioitems.issn)\n";
204 SetVersion ($DBversion);
207 $DBversion = "3.00.02.003";
208 #$DBversion = "3.01.00.007";
209 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
210 $dbh->do("UPDATE `systempreferences` SET options='70|10' WHERE variable='intranetmainUserblock'");
211 $dbh->do("UPDATE `systempreferences` SET options='70|10' WHERE variable='intranetuserjs'");
212 $dbh->do("UPDATE `systempreferences` SET options='70|10' WHERE variable='opacheader'");
213 $dbh->do("UPDATE `systempreferences` SET options='70|10' WHERE variable='OpacMainUserBlock'");
214 $dbh->do("UPDATE `systempreferences` SET options='70|10' WHERE variable='OpacNav'");
215 $dbh->do("UPDATE `systempreferences` SET options='70|10' WHERE variable='opacuserjs'");
216 $dbh->do("UPDATE `systempreferences` SET options='30|10', type='Textarea' WHERE variable='OAI-PMH:Set'");
217 $dbh->do("UPDATE `systempreferences` SET options='50' WHERE variable='intranetstylesheet'");
218 $dbh->do("UPDATE `systempreferences` SET options='50' WHERE variable='intranetcolorstylesheet'");
219 $dbh->do("UPDATE `systempreferences` SET options='10' WHERE variable='globalDueDate'");
220 $dbh->do("UPDATE `systempreferences` SET type='Integer' WHERE variable='numSearchResults'");
221 $dbh->do("UPDATE `systempreferences` SET type='Integer' WHERE variable='OPACnumSearchResults'");
222 $dbh->do("UPDATE `systempreferences` SET type='Integer' WHERE variable='ReservesMaxPickupDelay'");
223 $dbh->do("UPDATE `systempreferences` SET type='Integer' WHERE variable='TransfersMaxDaysWarning'");
224 $dbh->do("UPDATE `systempreferences` SET type='Integer' WHERE variable='StaticHoldsQueueWeight'");
225 $dbh->do("UPDATE `systempreferences` SET type='Integer' WHERE variable='holdCancelLength'");
226 $dbh->do("UPDATE `systempreferences` SET type='Integer' WHERE variable='XISBNDailyLimit'");
227 $dbh->do("UPDATE `systempreferences` SET type='Float' WHERE variable='gist'");
228 $dbh->do("UPDATE `systempreferences` SET type='Free' WHERE variable='BakerTaylorUsername'");
229 $dbh->do("UPDATE `systempreferences` SET type='Free' WHERE variable='BakerTaylorPassword'");
230 $dbh->do("UPDATE `systempreferences` SET type='Textarea', options='70|10' WHERE variable='ISBD'");
231 $dbh->do("UPDATE `systempreferences` SET type='Textarea', options='70|10', explanation='Enter a specific hash for NoZebra indexes. Enter : \\\'indexname\\\' => \\\'100a,245a,500*\\\',\\\'index2\\\' => \\\'...\\\'' WHERE variable='NoZebraIndexes'");
232 print "Upgrade to $DBversion done (fix display of many sysprefs)\n";
233 SetVersion ($DBversion);
237 $DBversion = "3.00.02.004";
238 #$DBversion = "3.01.00.009";
239 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
240 $dbh->do("INSERT INTO permissions (module_bit, code, description) VALUES ( 1, 'circulate_remaining_permissions', 'Remaining circulation permissions')");
241 $dbh->do("INSERT INTO permissions (module_bit, code, description) VALUES ( 1, 'override_renewals', 'Override blocked renewals')");
242 print "Upgrade to $DBversion done (added subpermissions for circulate permission)\n";
245 $DBversion = "3.00.02.005";
246 #$DBversion = '3.01.00.010';
247 if ( C4::Context->preference('Version') < TransformToNum($DBversion) ) {
248 $dbh->do("ALTER TABLE `borrower_attributes` MODIFY COLUMN `attribute` VARCHAR(64) DEFAULT NULL");
249 $dbh->do("ALTER TABLE `borrower_attributes` MODIFY COLUMN `password` VARCHAR(64) DEFAULT NULL");
250 print "Upgrade to $DBversion done (bug 2687: increase length of borrower attribute fields)\n";
251 SetVersion ($DBversion);
254 $DBversion = "3.00.02.006";
255 #$DBversion = '3.01.00.011';
256 if ( C4::Context->preference('Version') < TransformToNum($DBversion) ) {
258 # Yes, the old value was ^M terminated.
259 my $bad_value = "function prepareEmailPopup(){\r\n if (!document.getElementById) return false;\r\n if (!document.getElementById('reserveemail')) return false;\r\n rsvlink = document.getElementById('reserveemail');\r\n rsvlink.onclick = function() {\r\n doReservePopup();\r\n return false;\r\n }\r\n}\r\n\r\nfunction doReservePopup(){\r\n}\r\n\r\nfunction prepareReserveList(){\r\n}\r\n\r\naddLoadEvent(prepareEmailPopup);\r\naddLoadEvent(prepareReserveList);";
261 my $intranetuserjs = C4::Context->preference('intranetuserjs');
262 if ($intranetuserjs and $intranetuserjs eq $bad_value) {
263 my $sql = <<'END_SQL';
264 UPDATE systempreferences
266 WHERE variable = 'intranetuserjs'
270 print "Upgrade to $DBversion done (removed bogus intranetuserjs syspref)\n";
271 SetVersion($DBversion);
275 $DBversion = "3.00.02.007";
276 #$DBversion = '3.01.00.015';
277 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
278 $dbh->do("INSERT INTO systempreferences (variable,value,explanation,options,type) VALUES ('OPACAmazonCoverImages', '0', 'Display cover images on OPAC from Amazon Web Services','','YesNo')");
280 $dbh->do("INSERT INTO systempreferences (variable,value,explanation,options,type) VALUES ('AmazonCoverImages', '0', 'Display Cover Images in Staff Client from Amazon Web Services','','YesNo')");
282 $dbh->do("UPDATE systempreferences SET variable='AmazonEnabled' WHERE variable = 'AmazonContent'");
284 $dbh->do("UPDATE systempreferences SET variable='OPACAmazonEnabled' WHERE variable = 'OPACAmazonContent'");
286 print "Upgrade to $DBversion done (added Syndetics Enhanced Content system preferences)\n";
287 SetVersion ($DBversion);
292 $DBversion = "3.00.02.008";
293 #$DBversion = "3.01.00.018";
294 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
295 $dbh->do("ALTER TABLE deletedborrowers ADD `smsalertnumber` varchar(50) default NULL");
296 print "Upgrade to $DBversion done (added deletedborrowers.smsalertnumber, missed in 3.00.00.091)\n";
297 SetVersion ($DBversion);
301 $DBversion = "3.00.02.009";
302 #$DBversion = '3.01.00.023';
303 if ( C4::Context->preference('Version') < TransformToNum($DBversion) ) {
304 $dbh->do("ALTER TABLE biblioitems MODIFY COLUMN isbn VARCHAR(30) DEFAULT NULL");
305 $dbh->do("ALTER TABLE deletedbiblioitems MODIFY COLUMN isbn VARCHAR(30) DEFAULT NULL");
306 $dbh->do("ALTER TABLE import_biblios MODIFY COLUMN isbn VARCHAR(30) DEFAULT NULL");
307 $dbh->do("ALTER TABLE suggestions MODIFY COLUMN isbn VARCHAR(30) DEFAULT NULL");
308 print "Upgrade to $DBversion done (bug 2765: increase width of isbn column in several tables)\n";
309 SetVersion ($DBversion);
312 $DBversion = '3.00.02.010';
313 #$DBversion = '3.01.00.027';
314 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
315 $dbh->do("ALTER TABLE zebraqueue CHANGE `biblio_auth_number` `biblio_auth_number` bigint(20) unsigned NOT NULL default 0");
316 print "Upgrade to $DBversion done (Increased size of zebraqueue biblio_auth_number to address bug 3148.)\n";
317 SetVersion ($DBversion);
320 #$DBversion = '3.01.00.028';
321 $DBversion = '3.00.02.011';
322 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
323 my $enable_reviews = C4::Context->preference('AmazonEnabled') ? '1' : '0';
324 $dbh->do("INSERT INTO systempreferences (variable,value,explanation,options,type) VALUES ('AmazonReviews', '$enable_reviews', 'Display Amazon reviews on staff interface','','YesNo')");
325 print "Upgrade to $DBversion done (added AmazonReviews)\n";
326 SetVersion ($DBversion);
329 #$DBversion = '3.01.00.029';
330 $DBversion = '3.00.02.012';
331 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
332 $dbh->do(q( UPDATE language_rfc4646_to_iso639
333 SET iso639_2_code = 'spa'
334 WHERE rfc4646_subtag = 'es'
335 AND iso639_2_code = 'rus' )
337 print "Upgrade to $DBversion done (fixed bug 2599: using Spanish search limit retrieves Russian results)\n";
338 SetVersion ($DBversion);
341 $DBversion = '3.00.03.001';
342 if (C4::Context->preference('Version') < TransformToNum($DBversion)){
344 INSERT INTO `letter` (module, code, name, title, content) VALUES('reserves', 'HOLDPLACED', 'Hold Placed on Item', 'Hold Placed on Item','An hold has been placed on the following item : <<title>> (<<biblionumber>>) by the user <<firstname>> <<surname>> (<<cardnumber>>).');
348 $DBversion = '3.00.04.001';
349 if ( C4::Context->preference('Version') < TransformToNum($DBversion) ) {
351 CREATE TABLE hold_fill_targets (
352 `borrowernumber` int(11) NOT NULL,
353 `biblionumber` int(11) NOT NULL,
354 `itemnumber` int(11) NOT NULL,
355 `source_branchcode` varchar(10) default NULL,
356 `item_level_request` tinyint(4) NOT NULL default 0,
357 PRIMARY KEY `itemnumber` (`itemnumber`),
358 KEY `bib_branch` (`biblionumber`, `source_branchcode`),
359 CONSTRAINT `hold_fill_targets_ibfk_1` FOREIGN KEY (`borrowernumber`)
360 REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
361 CONSTRAINT `hold_fill_targets_ibfk_2` FOREIGN KEY (`biblionumber`)
362 REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE,
363 CONSTRAINT `hold_fill_targets_ibfk_3` FOREIGN KEY (`itemnumber`)
364 REFERENCES `items` (`itemnumber`) ON DELETE CASCADE ON UPDATE CASCADE,
365 CONSTRAINT `hold_fill_targets_ibfk_4` FOREIGN KEY (`source_branchcode`)
366 REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE
367 ) ENGINE=InnoDB DEFAULT CHARSET=utf8
370 ALTER TABLE tmp_holdsqueue
371 ADD item_level_request tinyint(4) NOT NULL default 0
374 print "Upgrade to $DBversion done (add hold_fill_targets table and a column to tmp_holdsqueue)\n";
375 SetVersion($DBversion);
378 $DBversion = '3.00.04.002';
379 if ( C4::Context->preference('Version') < TransformToNum($DBversion) ) {
380 # use statistics where available
382 ALTER TABLE statistics ADD KEY tmp_stats (type, itemnumber, borrowernumber)
390 AND itemnumber = iss.itemnumber
391 AND borrowernumber = iss.borrowernumber
393 WHERE issuedate IS NULL;
395 $dbh->do("ALTER TABLE statistics DROP KEY tmp_stats");
397 # default to last renewal date
400 SET issuedate = lastreneweddate
401 WHERE issuedate IS NULL
402 and lastreneweddate IS NOT NULL
405 my $num_bad_issuedates = $dbh->selectrow_array("SELECT COUNT(*) FROM issues WHERE issuedate IS NULL");
406 if ($num_bad_issuedates > 0) {
407 print STDERR "After the upgrade to $DBversion, there are still $num_bad_issuedates loan(s) with a NULL (blank) loan date. ",
408 "Please check the issues table in your database.";
410 print "Upgrade to $DBversion done (bug 2582: set null issues.issuedate to lastreneweddate)\n";
411 SetVersion($DBversion);
414 $DBversion = "3.00.04.003";
415 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
416 $dbh->do("INSERT INTO systempreferences (variable,value,explanation,options,type) VALUES('AllowRenewalLimitOverride', '0', 'if ON, allows renewal limits to be overridden on the circulation screen',NULL,'YesNo')");
417 print "Upgrade to $DBversion done (add new syspref)\n";
418 SetVersion ($DBversion);
421 $DBversion = '3.00.04.004';
422 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
423 $dbh->do("INSERT INTO systempreferences (variable,value,explanation,options,type) VALUES ('OPACDisplayRequestPriority','0','Show patrons the priority level on holds in the OPAC','','YesNo')");
424 print "Upgrade to $DBversion done (added OPACDisplayRequestPriority system preference)\n";
425 SetVersion ($DBversion);
428 $DBversion = '3.00.04.005';
429 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
431 INSERT INTO `letter` (module, code, name, title, content)
432 VALUES('reserves', 'HOLD', 'Hold Available for Pickup', 'Hold Available for Pickup at <<branches.branchname>>', 'Dear <<borrowers.firstname>> <<borrowers.surname>>,\r\n\r\nYou have a hold available for pickup as of <<reserves.waitingdate>>:\r\n\r\nTitle: <<biblio.title>>\r\nAuthor: <<biblio.author>>\r\nCopy: <<items.copynumber>>\r\nLocation: <<branches.branchname>>\r\n<<branches.branchaddress1>>\r\n<<branches.branchaddress2>>\r\n<<branches.branchaddress3>>')
434 $dbh->do("INSERT INTO `message_attributes` (message_attribute_id, message_name, takes_days) values(4, 'Hold Filled', 0)");
435 $dbh->do("INSERT INTO `message_transports` (message_attribute_id, message_transport_type, is_digest, letter_module, letter_code) values(4, 'sms', 0, 'reserves', 'HOLD')");
436 $dbh->do("INSERT INTO `message_transports` (message_attribute_id, message_transport_type, is_digest, letter_module, letter_code) values(4, 'email', 0, 'reserves', 'HOLD')");
437 print "Upgrade to $DBversion done (Add letter for holds notifications)\n";
438 SetVersion ($DBversion);
441 $DBversion = '3.00.04.006';
442 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
443 $dbh->do("ALTER TABLE `biblioitems` ADD KEY issn (issn)");
444 print "Upgrade to $DBversion done (add index on biblioitems.issn)\n";
445 SetVersion ($DBversion);
448 $DBversion = "3.00.04.007";
449 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
450 $dbh->do("UPDATE `systempreferences` SET options='70|10' WHERE variable='intranetmainUserblock'");
451 $dbh->do("UPDATE `systempreferences` SET options='70|10' WHERE variable='intranetuserjs'");
452 $dbh->do("UPDATE `systempreferences` SET options='70|10' WHERE variable='opacheader'");
453 $dbh->do("UPDATE `systempreferences` SET options='70|10' WHERE variable='OpacMainUserBlock'");
454 $dbh->do("UPDATE `systempreferences` SET options='70|10' WHERE variable='OpacNav'");
455 $dbh->do("UPDATE `systempreferences` SET options='70|10' WHERE variable='opacuserjs'");
456 $dbh->do("UPDATE `systempreferences` SET options='30|10', type='Textarea' WHERE variable='OAI-PMH:Set'");
457 $dbh->do("UPDATE `systempreferences` SET options='50' WHERE variable='intranetstylesheet'");
458 $dbh->do("UPDATE `systempreferences` SET options='50' WHERE variable='intranetcolorstylesheet'");
459 $dbh->do("UPDATE `systempreferences` SET options='10' WHERE variable='globalDueDate'");
460 $dbh->do("UPDATE `systempreferences` SET type='Integer' WHERE variable='numSearchResults'");
461 $dbh->do("UPDATE `systempreferences` SET type='Integer' WHERE variable='OPACnumSearchResults'");
462 $dbh->do("UPDATE `systempreferences` SET type='Integer' WHERE variable='ReservesMaxPickupDelay'");
463 $dbh->do("UPDATE `systempreferences` SET type='Integer' WHERE variable='TransfersMaxDaysWarning'");
464 $dbh->do("UPDATE `systempreferences` SET type='Integer' WHERE variable='StaticHoldsQueueWeight'");
465 $dbh->do("UPDATE `systempreferences` SET type='Integer' WHERE variable='holdCancelLength'");
466 $dbh->do("UPDATE `systempreferences` SET type='Integer' WHERE variable='XISBNDailyLimit'");
467 $dbh->do("UPDATE `systempreferences` SET type='Float' WHERE variable='gist'");
468 $dbh->do("UPDATE `systempreferences` SET type='Free' WHERE variable='BakerTaylorUsername'");
469 $dbh->do("UPDATE `systempreferences` SET type='Free' WHERE variable='BakerTaylorPassword'");
470 $dbh->do("UPDATE `systempreferences` SET type='Textarea', options='70|10' WHERE variable='ISBD'");
471 $dbh->do("UPDATE `systempreferences` SET type='Textarea', options='70|10', explanation='Enter a specific hash for NoZebra indexes. Enter : \\\'indexname\\\' => \\\'100a,245a,500*\\\',\\\'index2\\\' => \\\'...\\\'' WHERE variable='NoZebraIndexes'");
472 print "Upgrade to $DBversion done (fix display of many sysprefs)\n";
473 SetVersion ($DBversion);
477 $DBversion = '3.00.04.008';
478 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
480 $dbh->do("CREATE TABLE branch_transfer_limits (
481 limitId int(8) NOT NULL auto_increment,
482 toBranch varchar(4) NOT NULL,
483 fromBranch varchar(4) NOT NULL,
484 itemtype varchar(4) NOT NULL,
485 PRIMARY KEY (limitId)
486 ) ENGINE=InnoDB DEFAULT CHARSET=utf8"
489 $dbh->do("INSERT INTO `systempreferences` ( `variable` , `value` , `options` , `explanation` , `type` ) VALUES ( 'UseBranchTransferLimits', '0', '', 'If ON, Koha will will use the rules defined in branch_transfer_limits to decide if an item transfer should be allowed.', 'YesNo')");
491 print "Upgrade to $DBversion done (added branch_transfer_limits table and UseBranchTransferLimits system preference)\n";
492 SetVersion ($DBversion);
495 $DBversion = "3.00.04.009";
496 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
497 $dbh->do("ALTER TABLE permissions MODIFY `code` varchar(64) DEFAULT NULL");
498 $dbh->do("ALTER TABLE user_permissions MODIFY `code` varchar(64) DEFAULT NULL");
499 $dbh->do("INSERT INTO permissions (module_bit, code, description) VALUES ( 1, 'circulate_remaining_permissions', 'Remaining circulation permissions')");
500 $dbh->do("INSERT INTO permissions (module_bit, code, description) VALUES ( 1, 'override_renewals', 'Override blocked renewals')");
501 print "Upgrade to $DBversion done (added subpermissions for circulate permission)\n";
502 SetVersion ($DBversion);
505 $DBversion = '3.00.04.010';
506 if ( C4::Context->preference('Version') < TransformToNum($DBversion) ) {
507 $dbh->do("ALTER TABLE `borrower_attributes` MODIFY COLUMN `attribute` VARCHAR(64) DEFAULT NULL");
508 $dbh->do("ALTER TABLE `borrower_attributes` MODIFY COLUMN `password` VARCHAR(64) DEFAULT NULL");
509 print "Upgrade to $DBversion done (bug 2687: increase length of borrower attribute fields)\n";
510 SetVersion($DBversion);
513 $DBversion = '3.00.04.011';
514 if ( C4::Context->preference('Version') < TransformToNum($DBversion) ) {
515 $dbh->do("ALTER TABLE biblioitems MODIFY COLUMN isbn VARCHAR(30) DEFAULT NULL");
516 $dbh->do("ALTER TABLE deletedbiblioitems MODIFY COLUMN isbn VARCHAR(30) DEFAULT NULL");
517 $dbh->do("ALTER TABLE import_biblios MODIFY COLUMN isbn VARCHAR(30) DEFAULT NULL");
518 $dbh->do("ALTER TABLE suggestions MODIFY COLUMN isbn VARCHAR(30) DEFAULT NULL");
519 print "Upgrade to $DBversion done (bug 2765: increase width of isbn column in several tables)\n";
520 SetVersion ($DBversion);
523 $DBversion = '3.00.04.012';
524 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
525 $dbh->do("INSERT INTO `systempreferences` ( `variable` , `value` , `options` , `explanation` , `type` ) VALUES ( 'ceilingDueDate', '', '', 'If set, date due will not be past this date. Enter date according to the dateformat System Preference', 'free')");
527 print "Upgrade to $DBversion done (added ceilingDueDate system preference)\n";
528 SetVersion ($DBversion);
531 $DBversion = '3.00.04.013';
532 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
533 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('AWSPrivateKey','','See: http://aws.amazon.com. Note that this is required after 2009/08/15 in order to retrieve any enhanced content other than book covers from Amazon.','','free')");
534 SetVersion ($DBversion);
535 print "Upgrade to $DBversion done (added AWSPrivateKey syspref - note that if you use enhanced content from Amazon, this should be set right away.)";
538 $DBversion = '3.00.04.014';
539 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
540 $dbh->do("ALTER TABLE zebraqueue CHANGE `biblio_auth_number` `biblio_auth_number` bigint(20) unsigned NOT NULL default 0");
541 print "Upgrade to $DBversion done (Increased size of zebraqueue biblio_auth_number to address bug 3148.)\n";
542 SetVersion ($DBversion);
546 $DBversion = "3.00.04.015";
547 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
548 $dbh->do(<<ENDOFRENEWAL);
549 INSERT INTO systempreferences (variable,value,explanation,options,type) VALUES('RenewalPeriodBase', 'now', 'Set whether the renewal date should be counted from the date_due or from the moment the Patron asks for renewal ','date_due|now','Choice');
551 print "Upgrade to $DBversion done (Change the field)\n";
552 SetVersion ($DBversion);
555 #$DBversion = "3.01.00.040";
556 $DBversion = "3.00.04.016";
557 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
559 CREATE TABLE `fieldmapping` (
560 `id` int(11) NOT NULL auto_increment,
561 `field` varchar(255) NOT NULL,
562 `frameworkcode` char(4) NOT NULL default '',
563 `fieldcode` char(3) NOT NULL,
564 `subfieldcode` char(1) NOT NULL,
566 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
568 SetVersion ($DBversion);
570 $DBversion = "3.00.04.017";
571 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
573 INSERT INTO `permissions` (`module_bit`, `code`, `description`) VALUES
574 (15, 'check_expiration', 'Check the expiration of a serial'),
575 (15, 'claim_serials', 'Claim missing serials'),
576 (15, 'create_subscription', 'Create a new subscription'),
577 (15, 'delete_subscription', 'Delete an existing subscription'),
578 (15, 'edit_subscription', 'Edit an existing subscription'),
579 (15, 'receive_serials', 'Serials receiving'),
580 (15, 'renew_subscription', 'Renew a subscription'),
581 (15, 'routing', 'Routing');
583 SetVersion ($DBversion);
586 #$DBversion = '3.01.00.036';
587 $DBversion = "3.00.04.018";
588 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
589 $dbh->do("UPDATE systempreferences SET explanation = 'Choose the default detail view in the staff interface; choose between normal, labeled_marc, marc or isbd'
590 WHERE variable = 'IntranetBiblioDefaultView'
591 AND explanation = 'IntranetBiblioDefaultView'");
592 $dbh->do("UPDATE systempreferences SET type = 'Choice', options = 'normal|marc|isbd|labeled_marc'
593 WHERE variable = 'IntranetBiblioDefaultView'");
594 $dbh->do("INSERT INTO systempreferences (variable,value,explanation,options,type)VALUES('viewISBD','1','Allow display of ISBD view of bibiographic records','','YesNo')");
595 $dbh->do("INSERT INTO systempreferences (variable,value,explanation,options,type)VALUES('viewLabeledMARC','0','Allow display of labeled MARC view of bibiographic records','','YesNo')");
596 $dbh->do("INSERT INTO systempreferences (variable,value,explanation,options,type)VALUES('viewMARC','1','Allow display of MARC view of bibiographic records','','YesNo')");
597 print "Upgrade to $DBversion done (new viewISBD, viewLabeledMARC, viewMARC sysprefs and tweak IntranetBiblioDefaultView)\n";
598 SetVersion ($DBversion);
601 $DBversion = "3.00.04.019";
602 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
603 my $authdisplayhierarchy = C4::Context->preference('AuthDisplayHierarchy');
604 if ($authdisplayhierarchy < 1){
605 $dbh->do("INSERT IGNORE INTO systempreferences (variable,value,explanation,options,type)VALUES('AuthDisplayHierarchy','0','To display authorities in a hierarchy way. Put ON only if you have a thesaurus. Default is OFF','','YesNo')");
608 my $opacamazon = C4::Context->preference('OPACAmazonCoverImages');
609 if ($opacamazon < 1){
610 $dbh->do("INSERT IGNORE INTO systempreferences (variable,value,explanation,options,type)VALUES('OPACAmazonCoverImages','0','Display cover images on OPAC from Amazon Web Services. Default is OFF','','YesNo')");
613 print "Upgrade to $DBversion done (new AuthDisplayHierarchy, )\n";
614 SetVersion ($DBversion);
618 =item DropAllForeignKeys($table)
620 Drop all foreign keys of the table $table
624 sub DropAllForeignKeys {
626 # get the table description
627 my $sth = $dbh->prepare("SHOW CREATE TABLE $table");
629 my $vsc_structure = $sth->fetchrow;
630 # split on CONSTRAINT keyword
631 my @fks = split /CONSTRAINT /,$vsc_structure;
634 # isolate what is before FOREIGN KEY, if there is something, it's a foreign key to drop
635 $_ = /(.*) FOREIGN KEY.*/;
638 # we have found 1 foreign, drop it
639 $dbh->do("ALTER TABLE $table DROP FOREIGN KEY $id");
648 Transform the Koha version from a 4 parts string
649 to a number, with just 1 .
655 # remove the 3 last . to have a Perl number
656 $version =~ s/(.*\..*)\.(.*)\.(.*)/$1$2$3/;
662 set the DBversion in the systempreferences
667 my $kohaversion = TransformToNum(shift);
668 if (C4::Context->preference('Version')) {
669 my $finish=$dbh->prepare("UPDATE systempreferences SET value=? WHERE variable='Version'");
670 $finish->execute($kohaversion);
672 my $finish=$dbh->prepare("INSERT into systempreferences (variable,value,explanation) values ('Version',?,'The Koha database version. WARNING: Do not change this value manually, it is maintained by the webinstaller')");
673 $finish->execute($kohaversion);