5 # This script checks for required updates to the database.
7 # Parts copyright Catalyst IT 2011
9 # Part of the Koha Library Software www.koha-community.org
10 # Koha is free software; you can redistribute it and/or modify it under the
11 # terms of the GNU General Public License as published by the Free Software
12 # Foundation; either version 2 of the License, or (at your option) any later
15 # Koha is distributed in the hope that it will be useful, but WITHOUT ANY
16 # WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR
17 # A PARTICULAR PURPOSE. See the GNU General Public License for more details.
19 # You should have received a copy of the GNU General Public License along with
20 # Koha; if not, write to the Free Software Foundation, Inc., 59 Temple Place,
21 # Suite 330, Boston, MA 02111-1307 USA
25 # - Would also be a good idea to offer to do a backup at this time...
27 # NOTE: If you do something more than once in here, make it table driven.
29 # NOTE: Please keep the version in kohaversion.pl up-to-date!
43 use MARC::File::XML ( BinaryEncoding => 'utf8' );
45 # FIXME - The user might be installing a new database, so can't rely
46 # on /etc/koha.conf anyway.
53 %existingtables, # tables already in database
57 $type, $null, $key, $default, $extra,
58 $prefitem, # preference item in systempreferences table
65 my $dbh = C4::Context->dbh;
66 $|=1; # flushes output
69 # Deal with virtualshelves
71 my $DBversion = "3.00.00.001";
72 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
73 # update virtualshelves table to
75 $dbh->do("ALTER TABLE `bookshelf` RENAME `virtualshelves`");
76 $dbh->do("ALTER TABLE `shelfcontents` RENAME `virtualshelfcontents`");
77 $dbh->do("ALTER TABLE `virtualshelfcontents` ADD `biblionumber` INT( 11 ) NOT NULL default '0' AFTER shelfnumber");
78 $dbh->do("UPDATE `virtualshelfcontents` SET biblionumber=(SELECT biblionumber FROM items WHERE items.itemnumber=virtualshelfcontents.itemnumber)");
79 # drop all foreign keys : otherwise, we can't drop itemnumber field.
80 DropAllForeignKeys('virtualshelfcontents');
81 $dbh->do("ALTER TABLE `virtualshelfcontents` ADD KEY biblionumber (biblionumber)");
82 # create the new foreign keys (on biblionumber)
83 $dbh->do("ALTER TABLE `virtualshelfcontents` ADD CONSTRAINT `virtualshelfcontents_ibfk_1` FOREIGN KEY (`shelfnumber`) REFERENCES `virtualshelves` (`shelfnumber`) ON DELETE CASCADE ON UPDATE CASCADE");
84 # re-create the foreign key on virtualshelf
85 $dbh->do("ALTER TABLE `virtualshelfcontents` ADD CONSTRAINT `shelfcontents_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE");
86 $dbh->do("ALTER TABLE `virtualshelfcontents` DROP `itemnumber`");
87 print "Upgrade to $DBversion done (virtualshelves)\n";
88 SetVersion ($DBversion);
92 $DBversion = "3.00.00.002";
93 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
94 $dbh->do("DROP TABLE sessions");
95 $dbh->do("CREATE TABLE `sessions` (
96 `id` varchar(32) NOT NULL,
97 `a_session` text NOT NULL,
98 UNIQUE KEY `id` (`id`)
99 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;");
100 print "Upgrade to $DBversion done (sessions uses CGI::session, new table structure for sessions)\n";
101 SetVersion ($DBversion);
105 $DBversion = "3.00.00.003";
106 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
107 if (C4::Context->preference("opaclanguages") eq "fr") {
108 $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')");
110 $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')");
112 print "Upgrade to $DBversion done (adding ReservesNeedReturns systempref, in circulation)\n";
113 SetVersion ($DBversion);
117 $DBversion = "3.00.00.004";
118 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
119 $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')");
120 print "Upgrade to $DBversion done (adding DebugLevel systempref, in 'Admin' tab)\n";
121 SetVersion ($DBversion);
124 $DBversion = "3.00.00.005";
125 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
126 $dbh->do("CREATE TABLE `tags` (
127 `entry` varchar(255) NOT NULL default '',
128 `weight` bigint(20) NOT NULL default 0,
129 PRIMARY KEY (`entry`)
130 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
132 $dbh->do("CREATE TABLE `nozebra` (
133 `server` varchar(20) NOT NULL,
134 `indexname` varchar(40) NOT NULL,
135 `value` varchar(250) NOT NULL,
136 `biblionumbers` longtext NOT NULL,
137 KEY `indexname` (`server`,`indexname`),
138 KEY `value` (`server`,`value`))
139 ENGINE=InnoDB DEFAULT CHARSET=utf8;
141 print "Upgrade to $DBversion done (adding tags and nozebra tables )\n";
142 SetVersion ($DBversion);
145 $DBversion = "3.00.00.006";
146 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
147 $dbh->do("UPDATE issues SET issuedate=timestamp WHERE issuedate='0000-00-00'");
148 print "Upgrade to $DBversion done (filled issues.issuedate with timestamp)\n";
149 SetVersion ($DBversion);
152 $DBversion = "3.00.00.007";
153 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
154 $dbh->do("INSERT INTO systempreferences (variable,value,explanation,options,type) VALUES ('SessionStorage','mysql','Use mysql or a temporary file for storing session data','mysql|tmp','Choice')");
155 print "Upgrade to $DBversion done (set SessionStorage variable)\n";
156 SetVersion ($DBversion);
159 $DBversion = "3.00.00.008";
160 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
161 $dbh->do("ALTER TABLE `biblio` ADD `datecreated` DATE NOT NULL AFTER `timestamp` ;");
162 $dbh->do("UPDATE biblio SET datecreated=timestamp");
163 print "Upgrade to $DBversion done (biblio creation date)\n";
164 SetVersion ($DBversion);
167 $DBversion = "3.00.00.009";
168 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
170 # Create backups of call number columns
171 # in case default migration needs to be customized
173 # UPGRADE NOTE: temp_upg_biblioitems_call_num should be dropped
174 # after call numbers have been transformed to the new structure
176 # Not bothering to do the same with deletedbiblioitems -- assume
177 # default is good enough.
178 $dbh->do("CREATE TABLE `temp_upg_biblioitems_call_num` AS
179 SELECT `biblioitemnumber`, `biblionumber`,
180 `classification`, `dewey`, `subclass`,
182 FROM `biblioitems`");
184 # biblioitems changes
185 $dbh->do("ALTER TABLE `biblioitems` CHANGE COLUMN `volumeddesc` `volumedesc` TEXT,
186 ADD `cn_source` VARCHAR(10) DEFAULT NULL AFTER `ccode`,
187 ADD `cn_class` VARCHAR(30) DEFAULT NULL AFTER `cn_source`,
188 ADD `cn_item` VARCHAR(10) DEFAULT NULL AFTER `cn_class`,
189 ADD `cn_suffix` VARCHAR(10) DEFAULT NULL AFTER `cn_item`,
190 ADD `cn_sort` VARCHAR(30) DEFAULT NULL AFTER `cn_suffix`,
191 ADD `totalissues` INT(10) AFTER `cn_sort`");
193 # default mapping of call number columns:
194 # cn_class = concatentation of classification + dewey,
195 # trimmed to fit -- assumes that most users do not
196 # populate both classification and dewey in a single record
198 # cn_source = left null
201 # After upgrade, cn_sort will have to be set based on whatever
202 # default call number scheme user sets as a preference. Misc
203 # script will be added at some point to do that.
205 $dbh->do("UPDATE `biblioitems`
206 SET cn_class = SUBSTR(TRIM(CONCAT_WS(' ', `classification`, `dewey`)), 1, 30),
211 # Now drop the old call number columns
212 $dbh->do("ALTER TABLE `biblioitems` DROP COLUMN `classification`,
214 DROP COLUMN `subclass`,
215 DROP COLUMN `lcsort`,
216 DROP COLUMN `ccode`");
218 # deletedbiblio changes
219 $dbh->do("ALTER TABLE `deletedbiblio` ALTER COLUMN `frameworkcode` SET DEFAULT '',
221 ADD `datecreated` DATE NOT NULL AFTER `timestamp`");
222 $dbh->do("UPDATE deletedbiblio SET datecreated = timestamp");
224 # deletedbiblioitems changes
225 $dbh->do("ALTER TABLE `deletedbiblioitems`
226 MODIFY `publicationyear` TEXT,
227 CHANGE `volumeddesc` `volumedesc` TEXT,
228 MODIFY `collectiontitle` MEDIUMTEXT DEFAULT NULL AFTER `volumedesc`,
229 MODIFY `collectionissn` TEXT DEFAULT NULL AFTER `collectiontitle`,
230 MODIFY `collectionvolume` MEDIUMTEXT DEFAULT NULL AFTER `collectionissn`,
231 MODIFY `editionstatement` TEXT DEFAULT NULL AFTER `collectionvolume`,
232 MODIFY `editionresponsibility` TEXT DEFAULT NULL AFTER `editionstatement`,
233 MODIFY `place` VARCHAR(255) DEFAULT NULL AFTER `size`,
234 MODIFY `marc` LONGBLOB,
235 ADD `cn_source` VARCHAR(10) DEFAULT NULL AFTER `url`,
236 ADD `cn_class` VARCHAR(30) DEFAULT NULL AFTER `cn_source`,
237 ADD `cn_item` VARCHAR(10) DEFAULT NULL AFTER `cn_class`,
238 ADD `cn_suffix` VARCHAR(10) DEFAULT NULL AFTER `cn_item`,
239 ADD `cn_sort` VARCHAR(30) DEFAULT NULL AFTER `cn_suffix`,
240 ADD `totalissues` INT(10) AFTER `cn_sort`,
241 ADD `marcxml` LONGTEXT NOT NULL AFTER `totalissues`,
242 ADD KEY `isbn` (`isbn`),
243 ADD KEY `publishercode` (`publishercode`)
246 $dbh->do("UPDATE `deletedbiblioitems`
247 SET `cn_class` = SUBSTR(TRIM(CONCAT_WS(' ', `classification`, `dewey`)), 1, 30),
248 `cn_item` = `subclass`,
251 $dbh->do("ALTER TABLE `deletedbiblioitems`
252 DROP COLUMN `classification`,
254 DROP COLUMN `subclass`,
255 DROP COLUMN `lcsort`,
259 # deleteditems changes
260 $dbh->do("ALTER TABLE `deleteditems`
261 MODIFY `barcode` VARCHAR(20) DEFAULT NULL,
262 MODIFY `price` DECIMAL(8,2) DEFAULT NULL,
263 MODIFY `replacementprice` DECIMAL(8,2) DEFAULT NULL,
265 MODIFY `itemcallnumber` VARCHAR(30) DEFAULT NULL AFTER `wthdrawn`,
266 MODIFY `holdingbranch` VARCHAR(10) DEFAULT NULL,
268 MODIFY `timestamp` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP AFTER `paidfor`,
270 ADD `cn_source` VARCHAR(10) DEFAULT NULL AFTER `onloan`,
271 ADD `cn_sort` VARCHAR(30) DEFAULT NULL AFTER `cn_source`,
272 ADD `ccode` VARCHAR(10) DEFAULT NULL AFTER `cn_sort`,
273 ADD `materials` VARCHAR(10) DEFAULT NULL AFTER `ccode`,
274 ADD `uri` VARCHAR(255) DEFAULT NULL AFTER `materials`,
275 MODIFY `marc` LONGBLOB AFTER `uri`,
277 DROP KEY `itembarcodeidx`,
278 DROP KEY `itembinoidx`,
279 DROP KEY `itembibnoidx`,
280 ADD UNIQUE KEY `delitembarcodeidx` (`barcode`),
281 ADD KEY `delitembinoidx` (`biblioitemnumber`),
282 ADD KEY `delitembibnoidx` (`biblionumber`),
283 ADD KEY `delhomebranch` (`homebranch`),
284 ADD KEY `delholdingbranch` (`holdingbranch`)");
285 $dbh->do("UPDATE deleteditems SET `ccode` = `itype`");
286 $dbh->do("ALTER TABLE deleteditems DROP `itype`");
287 $dbh->do("UPDATE `deleteditems` SET `cn_sort` = `itemcallnumber`");
290 $dbh->do("ALTER TABLE `items` ADD `cn_source` VARCHAR(10) DEFAULT NULL AFTER `onloan`,
291 ADD `cn_sort` VARCHAR(30) DEFAULT NULL AFTER `cn_source`,
292 ADD `ccode` VARCHAR(10) DEFAULT NULL AFTER `cn_sort`,
293 ADD `materials` VARCHAR(10) DEFAULT NULL AFTER `ccode`,
294 ADD `uri` VARCHAR(255) DEFAULT NULL AFTER `materials`
296 $dbh->do("ALTER TABLE `items`
297 DROP KEY `itembarcodeidx`,
298 ADD UNIQUE KEY `itembarcodeidx` (`barcode`)");
300 # map items.itype to items.ccode and
301 # set cn_sort to itemcallnumber -- as with biblioitems.cn_sort,
302 # will have to be subsequently updated per user's default
303 # classification scheme
304 $dbh->do("UPDATE `items` SET `cn_sort` = `itemcallnumber`,
307 $dbh->do("ALTER TABLE `items` DROP `cutterextra`,
310 print "Upgrade to $DBversion done (major changes to biblio, biblioitems, items, and deleted* versions of same\n";
311 SetVersion ($DBversion);
314 $DBversion = "3.00.00.010";
315 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
316 $dbh->do("CREATE INDEX `userid` ON borrowers (`userid`) ");
317 print "Upgrade to $DBversion done (userid index added)\n";
318 SetVersion ($DBversion);
321 $DBversion = "3.00.00.011";
322 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
323 $dbh->do("ALTER TABLE `branchcategories` CHANGE `categorycode` `categorycode` varchar(10) ");
324 $dbh->do("ALTER TABLE `branchcategories` CHANGE `categoryname` `categoryname` varchar(32) ");
325 $dbh->do("ALTER TABLE `branchcategories` ADD COLUMN `categorytype` varchar(16) ");
326 $dbh->do("UPDATE `branchcategories` SET `categorytype` = 'properties'");
327 $dbh->do("ALTER TABLE `branchrelations` CHANGE `categorycode` `categorycode` varchar(10) ");
328 print "Upgrade to $DBversion done (added branchcategory type)\n";
329 SetVersion ($DBversion);
332 $DBversion = "3.00.00.012";
333 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
334 $dbh->do("CREATE TABLE `class_sort_rules` (
335 `class_sort_rule` varchar(10) NOT NULL default '',
336 `description` mediumtext,
337 `sort_routine` varchar(30) NOT NULL default '',
338 PRIMARY KEY (`class_sort_rule`),
339 UNIQUE KEY `class_sort_rule_idx` (`class_sort_rule`)
340 ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
341 $dbh->do("CREATE TABLE `class_sources` (
342 `cn_source` varchar(10) NOT NULL default '',
343 `description` mediumtext,
344 `used` tinyint(4) NOT NULL default 0,
345 `class_sort_rule` varchar(10) NOT NULL default '',
346 PRIMARY KEY (`cn_source`),
347 UNIQUE KEY `cn_source_idx` (`cn_source`),
348 KEY `used_idx` (`used`),
349 CONSTRAINT `class_source_ibfk_1` FOREIGN KEY (`class_sort_rule`)
350 REFERENCES `class_sort_rules` (`class_sort_rule`)
351 ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
352 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type)
353 VALUES('DefaultClassificationSource','ddc',
354 'Default classification scheme used by the collection. E.g., Dewey, LCC, etc.', NULL,'free')");
355 $dbh->do("INSERT INTO `class_sort_rules` (`class_sort_rule`, `description`, `sort_routine`) VALUES
356 ('dewey', 'Default filing rules for DDC', 'Dewey'),
357 ('lcc', 'Default filing rules for LCC', 'LCC'),
358 ('generic', 'Generic call number filing rules', 'Generic')");
359 $dbh->do("INSERT INTO `class_sources` (`cn_source`, `description`, `used`, `class_sort_rule`) VALUES
360 ('ddc', 'Dewey Decimal Classification', 1, 'dewey'),
361 ('lcc', 'Library of Congress Classification', 1, 'lcc'),
362 ('udc', 'Universal Decimal Classification', 0, 'generic'),
363 ('sudocs', 'SuDoc Classification (U.S. GPO)', 0, 'generic'),
364 ('z', 'Other/Generic Classification Scheme', 0, 'generic')");
365 print "Upgrade to $DBversion done (classification sources added)\n";
366 SetVersion ($DBversion);
369 $DBversion = "3.00.00.013";
370 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
371 $dbh->do("CREATE TABLE `import_batches` (
372 `import_batch_id` int(11) NOT NULL auto_increment,
373 `template_id` int(11) default NULL,
374 `branchcode` varchar(10) default NULL,
375 `num_biblios` int(11) NOT NULL default 0,
376 `num_items` int(11) NOT NULL default 0,
377 `upload_timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP,
378 `overlay_action` enum('replace', 'create_new', 'use_template') NOT NULL default 'create_new',
379 `import_status` enum('staging', 'staged', 'importing', 'imported', 'reverting', 'reverted', 'cleaned') NOT NULL default 'staging',
380 `batch_type` enum('batch', 'z3950') NOT NULL default 'batch',
381 `file_name` varchar(100),
382 `comments` mediumtext,
383 PRIMARY KEY (`import_batch_id`),
384 KEY `branchcode` (`branchcode`)
385 ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
386 $dbh->do("CREATE TABLE `import_records` (
387 `import_record_id` int(11) NOT NULL auto_increment,
388 `import_batch_id` int(11) NOT NULL,
389 `branchcode` varchar(10) default NULL,
390 `record_sequence` int(11) NOT NULL default 0,
391 `upload_timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP,
392 `import_date` DATE default NULL,
393 `marc` longblob NOT NULL,
394 `marcxml` longtext NOT NULL,
395 `marcxml_old` longtext NOT NULL,
396 `record_type` enum('biblio', 'auth', 'holdings') NOT NULL default 'biblio',
397 `overlay_status` enum('no_match', 'auto_match', 'manual_match', 'match_applied') NOT NULL default 'no_match',
398 `status` enum('error', 'staged', 'imported', 'reverted', 'items_reverted') NOT NULL default 'staged',
399 `import_error` mediumtext,
400 `encoding` varchar(40) NOT NULL default '',
401 `z3950random` varchar(40) default NULL,
402 PRIMARY KEY (`import_record_id`),
403 CONSTRAINT `import_records_ifbk_1` FOREIGN KEY (`import_batch_id`)
404 REFERENCES `import_batches` (`import_batch_id`) ON DELETE CASCADE ON UPDATE CASCADE,
405 KEY `branchcode` (`branchcode`),
406 KEY `batch_sequence` (`import_batch_id`, `record_sequence`)
407 ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
408 $dbh->do("CREATE TABLE `import_record_matches` (
409 `import_record_id` int(11) NOT NULL,
410 `candidate_match_id` int(11) NOT NULL,
411 `score` int(11) NOT NULL default 0,
412 CONSTRAINT `import_record_matches_ibfk_1` FOREIGN KEY (`import_record_id`)
413 REFERENCES `import_records` (`import_record_id`) ON DELETE CASCADE ON UPDATE CASCADE,
414 KEY `record_score` (`import_record_id`, `score`)
415 ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
416 $dbh->do("CREATE TABLE `import_biblios` (
417 `import_record_id` int(11) NOT NULL,
418 `matched_biblionumber` int(11) default NULL,
419 `control_number` varchar(25) default NULL,
420 `original_source` varchar(25) default NULL,
421 `title` varchar(128) default NULL,
422 `author` varchar(80) default NULL,
423 `isbn` varchar(14) default NULL,
424 `issn` varchar(9) default NULL,
425 `has_items` tinyint(1) NOT NULL default 0,
426 CONSTRAINT `import_biblios_ibfk_1` FOREIGN KEY (`import_record_id`)
427 REFERENCES `import_records` (`import_record_id`) ON DELETE CASCADE ON UPDATE CASCADE,
428 KEY `matched_biblionumber` (`matched_biblionumber`),
429 KEY `title` (`title`),
431 ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
432 $dbh->do("CREATE TABLE `import_items` (
433 `import_items_id` int(11) NOT NULL auto_increment,
434 `import_record_id` int(11) NOT NULL,
435 `itemnumber` int(11) default NULL,
436 `branchcode` varchar(10) default NULL,
437 `status` enum('error', 'staged', 'imported', 'reverted') NOT NULL default 'staged',
438 `marcxml` longtext NOT NULL,
439 `import_error` mediumtext,
440 PRIMARY KEY (`import_items_id`),
441 CONSTRAINT `import_items_ibfk_1` FOREIGN KEY (`import_record_id`)
442 REFERENCES `import_records` (`import_record_id`) ON DELETE CASCADE ON UPDATE CASCADE,
443 KEY `itemnumber` (`itemnumber`),
444 KEY `branchcode` (`branchcode`)
445 ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
447 $dbh->do("INSERT INTO `import_batches`
448 (`overlay_action`, `import_status`, `batch_type`, `file_name`)
449 SELECT distinct 'create_new', 'staged', 'z3950', `file`
450 FROM `marc_breeding`");
452 $dbh->do("INSERT INTO `import_records`
453 (`import_batch_id`, `import_record_id`, `record_sequence`, `marc`, `record_type`, `status`,
454 `encoding`, `z3950random`, `marcxml`, `marcxml_old`)
455 SELECT `import_batch_id`, `id`, 1, `marc`, 'biblio', 'staged', `encoding`, `z3950random`, '', ''
457 JOIN `import_batches` ON (`file_name` = `file`)");
459 $dbh->do("INSERT INTO `import_biblios`
460 (`import_record_id`, `title`, `author`, `isbn`)
461 SELECT `import_record_id`, `title`, `author`, `isbn`
463 JOIN `import_records` ON (`import_record_id` = `id`)");
465 $dbh->do("UPDATE `import_batches`
466 SET `num_biblios` = (
468 FROM `import_records`
469 WHERE `import_batch_id` = `import_batches`.`import_batch_id`
472 $dbh->do("DROP TABLE `marc_breeding`");
474 print "Upgrade to $DBversion done (import_batches et al. added)\n";
475 SetVersion ($DBversion);
478 $DBversion = "3.00.00.014";
479 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
480 $dbh->do("ALTER TABLE subscription ADD lastbranch VARCHAR(4)");
481 print "Upgrade to $DBversion done (userid index added)\n";
482 SetVersion ($DBversion);
485 $DBversion = "3.00.00.015";
486 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
487 $dbh->do("CREATE TABLE `saved_sql` (
488 `id` int(11) NOT NULL auto_increment,
489 `borrowernumber` int(11) default NULL,
490 `date_created` datetime default NULL,
491 `last_modified` datetime default NULL,
493 `last_run` datetime default NULL,
494 `report_name` varchar(255) default NULL,
495 `type` varchar(255) default NULL,
498 KEY boridx (`borrowernumber`)
499 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;");
500 $dbh->do("CREATE TABLE `saved_reports` (
501 `id` int(11) NOT NULL auto_increment,
502 `report_id` int(11) default NULL,
504 `date_run` datetime default NULL,
506 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;");
507 print "Upgrade to $DBversion done (saved_sql and saved_reports added)\n";
508 SetVersion ($DBversion);
511 $DBversion = "3.00.00.016";
512 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
513 $dbh->do(" CREATE TABLE reports_dictionary (
514 id int(11) NOT NULL auto_increment,
515 name varchar(255) default NULL,
517 date_created datetime default NULL,
518 date_modified datetime default NULL,
520 area int(11) default NULL,
522 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ");
523 print "Upgrade to $DBversion done (reports_dictionary) added)\n";
524 SetVersion ($DBversion);
527 $DBversion = "3.00.00.017";
528 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
529 $dbh->do("ALTER TABLE action_logs DROP PRIMARY KEY");
530 $dbh->do("ALTER TABLE action_logs ADD KEY timestamp (timestamp,user)");
531 $dbh->do("ALTER TABLE action_logs ADD action_id INT(11) NOT NULL FIRST");
532 $dbh->do("UPDATE action_logs SET action_id = if (\@a, \@a:=\@a+1, \@a:=1)");
533 $dbh->do("ALTER TABLE action_logs MODIFY action_id INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY");
534 print "Upgrade to $DBversion done (added column to action_logs)\n";
535 SetVersion ($DBversion);
538 $DBversion = "3.00.00.018";
539 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
540 $dbh->do("ALTER TABLE `zebraqueue`
541 ADD `done` INT NOT NULL DEFAULT '0',
542 ADD `time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ;
544 print "Upgrade to $DBversion done (adding timestamp and done columns to zebraque table to improve problem tracking) added)\n";
545 SetVersion ($DBversion);
548 $DBversion = "3.00.00.019";
549 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
550 $dbh->do("ALTER TABLE biblio MODIFY biblionumber INT(11) NOT NULL AUTO_INCREMENT");
551 $dbh->do("ALTER TABLE biblioitems MODIFY biblioitemnumber INT(11) NOT NULL AUTO_INCREMENT");
552 $dbh->do("ALTER TABLE items MODIFY itemnumber INT(11) NOT NULL AUTO_INCREMENT");
553 print "Upgrade to $DBversion done (made bib/item PKs auto_increment)\n";
554 SetVersion ($DBversion);
557 $DBversion = "3.00.00.020";
558 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
559 $dbh->do("ALTER TABLE deleteditems
560 DROP KEY `delitembarcodeidx`,
561 ADD KEY `delitembarcodeidx` (`barcode`)");
562 print "Upgrade to $DBversion done (dropped uniqueness of key on deleteditems.barcode)\n";
563 SetVersion ($DBversion);
566 $DBversion = "3.00.00.021";
567 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
568 $dbh->do("ALTER TABLE items CHANGE homebranch homebranch VARCHAR(10)");
569 $dbh->do("ALTER TABLE deleteditems CHANGE homebranch homebranch VARCHAR(10)");
570 $dbh->do("ALTER TABLE statistics CHANGE branch branch VARCHAR(10)");
571 $dbh->do("ALTER TABLE subscription CHANGE lastbranch lastbranch VARCHAR(10)");
572 print "Upgrade to $DBversion done (extended missed branchcode columns to 10 chars)\n";
573 SetVersion ($DBversion);
576 $DBversion = "3.00.00.022";
577 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
578 $dbh->do("ALTER TABLE items
579 ADD `damaged` tinyint(1) default NULL AFTER notforloan");
580 $dbh->do("ALTER TABLE deleteditems
581 ADD `damaged` tinyint(1) default NULL AFTER notforloan");
582 print "Upgrade to $DBversion done (adding damaged column to items table)\n";
583 SetVersion ($DBversion);
586 $DBversion = "3.00.00.023";
587 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
588 $dbh->do("INSERT INTO `systempreferences` (variable,value,options,explanation,type)
589 VALUES ('yuipath','http://yui.yahooapis.com/2.3.1/build','Insert the path to YUI libraries','','free')");
590 print "Upgrade to $DBversion done (adding new system preference for controlling YUI path)\n";
591 SetVersion ($DBversion);
593 $DBversion = "3.00.00.024";
594 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
595 $dbh->do("ALTER TABLE biblioitems CHANGE itemtype itemtype VARCHAR(10)");
596 print "Upgrade to $DBversion done (changing itemtype to (10))\n";
597 SetVersion ($DBversion);
600 $DBversion = "3.00.00.025";
601 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
602 $dbh->do("ALTER TABLE items ADD COLUMN itype VARCHAR(10)");
603 $dbh->do("ALTER TABLE deleteditems ADD COLUMN itype VARCHAR(10) AFTER uri");
604 if(C4::Context->preference('item-level_itypes')){
605 $dbh->do('update items,biblioitems set items.itype=biblioitems.itemtype where items.biblionumber=biblioitems.biblionumber and itype is null');
607 print "Upgrade to $DBversion done (reintroduce items.itype - fill from itemtype)\n ";
608 SetVersion ($DBversion);
611 $DBversion = "3.00.00.026";
612 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
613 $dbh->do("INSERT INTO `systempreferences` (variable,value,options,explanation,type)
614 VALUES ('HomeOrHoldingBranch','homebranch','homebranch|holdingbranch','With independent branches turned on this decides whether to check the items holdingbranch or homebranch at circulatilon','choice')");
615 print "Upgrade to $DBversion done (adding new system preference for choosing whether homebranch or holdingbranch is checked in circulation)\n";
616 SetVersion ($DBversion);
619 $DBversion = "3.00.00.027";
620 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
621 $dbh->do("CREATE TABLE `marc_matchers` (
622 `matcher_id` int(11) NOT NULL auto_increment,
623 `code` varchar(10) NOT NULL default '',
624 `description` varchar(255) NOT NULL default '',
625 `record_type` varchar(10) NOT NULL default 'biblio',
626 `threshold` int(11) NOT NULL default 0,
627 PRIMARY KEY (`matcher_id`),
629 KEY `record_type` (`record_type`)
630 ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
631 $dbh->do("CREATE TABLE `matchpoints` (
632 `matcher_id` int(11) NOT NULL,
633 `matchpoint_id` int(11) NOT NULL auto_increment,
634 `search_index` varchar(30) NOT NULL default '',
635 `score` int(11) NOT NULL default 0,
636 PRIMARY KEY (`matchpoint_id`),
637 CONSTRAINT `matchpoints_ifbk_1` FOREIGN KEY (`matcher_id`)
638 REFERENCES `marc_matchers` (`matcher_id`) ON DELETE CASCADE ON UPDATE CASCADE
639 ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
640 $dbh->do("CREATE TABLE `matchpoint_components` (
641 `matchpoint_id` int(11) NOT NULL,
642 `matchpoint_component_id` int(11) NOT NULL auto_increment,
643 sequence int(11) NOT NULL default 0,
644 tag varchar(3) NOT NULL default '',
645 subfields varchar(40) NOT NULL default '',
646 offset int(4) NOT NULL default 0,
647 length int(4) NOT NULL default 0,
648 PRIMARY KEY (`matchpoint_component_id`),
649 KEY `by_sequence` (`matchpoint_id`, `sequence`),
650 CONSTRAINT `matchpoint_components_ifbk_1` FOREIGN KEY (`matchpoint_id`)
651 REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE
652 ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
653 $dbh->do("CREATE TABLE `matchpoint_component_norms` (
654 `matchpoint_component_id` int(11) NOT NULL,
655 `sequence` int(11) NOT NULL default 0,
656 `norm_routine` varchar(50) NOT NULL default '',
657 KEY `matchpoint_component_norms` (`matchpoint_component_id`, `sequence`),
658 CONSTRAINT `matchpoint_component_norms_ifbk_1` FOREIGN KEY (`matchpoint_component_id`)
659 REFERENCES `matchpoint_components` (`matchpoint_component_id`) ON DELETE CASCADE ON UPDATE CASCADE
660 ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
661 $dbh->do("CREATE TABLE `matcher_matchpoints` (
662 `matcher_id` int(11) NOT NULL,
663 `matchpoint_id` int(11) NOT NULL,
664 CONSTRAINT `matcher_matchpoints_ifbk_1` FOREIGN KEY (`matcher_id`)
665 REFERENCES `marc_matchers` (`matcher_id`) ON DELETE CASCADE ON UPDATE CASCADE,
666 CONSTRAINT `matcher_matchpoints_ifbk_2` FOREIGN KEY (`matchpoint_id`)
667 REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE
668 ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
669 $dbh->do("CREATE TABLE `matchchecks` (
670 `matcher_id` int(11) NOT NULL,
671 `matchcheck_id` int(11) NOT NULL auto_increment,
672 `source_matchpoint_id` int(11) NOT NULL,
673 `target_matchpoint_id` int(11) NOT NULL,
674 PRIMARY KEY (`matchcheck_id`),
675 CONSTRAINT `matcher_matchchecks_ifbk_1` FOREIGN KEY (`matcher_id`)
676 REFERENCES `marc_matchers` (`matcher_id`) ON DELETE CASCADE ON UPDATE CASCADE,
677 CONSTRAINT `matcher_matchchecks_ifbk_2` FOREIGN KEY (`source_matchpoint_id`)
678 REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE,
679 CONSTRAINT `matcher_matchchecks_ifbk_3` FOREIGN KEY (`target_matchpoint_id`)
680 REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE
681 ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
682 print "Upgrade to $DBversion done (added C4::Matcher serialization tables)\n ";
683 SetVersion ($DBversion);
686 $DBversion = "3.00.00.028";
687 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
688 $dbh->do("INSERT INTO `systempreferences` (variable,value,options,explanation,type)
689 VALUES ('canreservefromotherbranches','1','','With Independent branches on, can a user from one library reserve an item from another library','YesNo')");
690 print "Upgrade to $DBversion done (adding new system preference for changing reserve/holds behaviour with independent branches)\n";
691 SetVersion ($DBversion);
695 $DBversion = "3.00.00.029";
696 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
697 $dbh->do("ALTER TABLE `import_batches` ADD `matcher_id` int(11) NULL AFTER `import_batch_id`");
698 print "Upgrade to $DBversion done (adding matcher_id to import_batches)\n";
699 SetVersion ($DBversion);
702 $DBversion = "3.00.00.030";
703 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
705 CREATE TABLE services_throttle (
706 service_type varchar(10) NOT NULL default '',
707 service_count varchar(45) default NULL,
708 PRIMARY KEY (service_type)
709 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
711 $dbh->do("INSERT INTO `systempreferences` (variable,value,options,explanation,type)
712 VALUES ('FRBRizeEditions',0,'','If ON, Koha will query one or more ISBN web services for associated ISBNs and display an Editions tab on the details pages','YesNo')");
713 $dbh->do("INSERT INTO `systempreferences` (variable,value,options,explanation,type)
714 VALUES ('XISBN',0,'','Use with FRBRizeEditions. If ON, Koha will use the OCLC xISBN web service in the Editions tab on the detail pages. See: http://www.worldcat.org/affiliate/webservices/xisbn/app.jsp','YesNo')");
715 $dbh->do("INSERT INTO `systempreferences` (variable,value,options,explanation,type)
716 VALUES ('OCLCAffiliateID','','','Use with FRBRizeEditions and XISBN. You can sign up for an AffiliateID here: http://www.worldcat.org/wcpa/do/AffiliateUserServices?method=initSelfRegister','free')");
717 $dbh->do("INSERT INTO `systempreferences` (variable,value,options,explanation,type)
718 VALUES ('XISBNDailyLimit',499,'','The xISBN Web service is free for non-commercial use when usage does not exceed 500 requests per day','free')");
719 $dbh->do("INSERT INTO `systempreferences` (variable,value,options,explanation,type)
720 VALUES ('PINESISBN',0,'','Use with FRBRizeEditions. If ON, Koha will use PINES OISBN web service in the Editions tab on the detail pages.','YesNo')");
721 $dbh->do("INSERT INTO `systempreferences` (variable,value,options,explanation,type)
722 VALUES ('ThingISBN',0,'','Use with FRBRizeEditions. If ON, Koha will use the ThingISBN web service in the Editions tab on the detail pages.','YesNo')");
723 print "Upgrade to $DBversion done (adding services throttle table and sysprefs for xISBN)\n";
724 SetVersion ($DBversion);
727 $DBversion = "3.00.00.031";
728 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
730 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('QueryStemming',1,'If ON, enables query stemming',NULL,'YesNo')");
731 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('QueryFuzzy',1,'If ON, enables fuzzy option for searches',NULL,'YesNo')");
732 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('QueryWeightFields',1,'If ON, enables field weighting',NULL,'YesNo')");
733 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('WebBasedSelfCheck',0,'If ON, enables the web-based self-check system',NULL,'YesNo')");
734 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('numSearchResults',20,'Specify the maximum number of results to display on a page of results',NULL,'free')");
735 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('OPACnumSearchResults',20,'Specify the maximum number of results to display on a page of results',NULL,'free')");
736 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('maxItemsInSearchResults',20,'Specify the maximum number of items to display for each result on a page of results',NULL,'free')");
737 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('defaultSortField',NULL,'Specify the default field used for sorting','relevance|popularity|call_number|pubdate|acqdate|title|author','Choice')");
738 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('defaultSortOrder',NULL,'Specify the default sort order','asc|dsc|az|za','Choice')");
739 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('OPACdefaultSortField',NULL,'Specify the default field used for sorting','relevance|popularity|call_number|pubdate|acqdate|title|author','Choice')");
740 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('OPACdefaultSortOrder',NULL,'Specify the default sort order','asc|dsc|za|az','Choice')");
741 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('staffClientBaseURL','','Specify the base URL of the staff client',NULL,'free')");
742 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('minPasswordLength',3,'Specify the minimum length of a patron/staff password',NULL,'free')");
743 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('noItemTypeImages',0,'If ON, disables item-type images',NULL,'YesNo')");
744 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('emailLibrarianWhenHoldIsPlaced',0,'If ON, emails the librarian whenever a hold is placed',NULL,'YesNo')");
745 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('holdCancelLength','','Specify how many days before a hold is canceled',NULL,'free')");
746 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('libraryAddress','','The address to use for printing receipts, overdues, etc. if different than physical address',NULL,'free')");
747 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('finesMode','test','Choose the fines mode, test or production','test|production','Choice')");
748 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('globalDueDate','','If set, allows a global static due date for all checkouts',NULL,'free')");
749 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('itemBarcodeInputFilter','','If set, allows specification of a item barcode input filter','cuecat','Choice')");
750 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('singleBranchMode',0,'Operate in Single-branch mode, hide branch selection in the OPAC',NULL,'YesNo')");
751 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('URLLinkText','','Text to display as the link anchor in the OPAC',NULL,'free')");
752 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('OPACSubscriptionDisplay','economical','Specify how to display subscription information in the OPAC','economical|off|full','Choice')");
753 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('OPACDisplayExtendedSubInfo',1,'If ON, extended subscription information is displayed in the OPAC',NULL,'YesNo')");
754 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('OPACViewOthersSuggestions',0,'If ON, allows all suggestions to be displayed in the OPAC',NULL,'YesNo')");
755 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('OPACURLOpenInNewWindow',0,'If ON, URLs in the OPAC open in a new window',NULL,'YesNo')");
756 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('OPACUserCSS',0,'Add CSS to be included in the OPAC',NULL,'free')");
758 print "Upgrade to $DBversion done (adding additional system preference)\n";
759 SetVersion ($DBversion);
762 $DBversion = "3.00.00.032";
763 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
764 $dbh->do("UPDATE `marc_subfield_structure` SET `kohafield` = 'items.wthdrawn' WHERE `kohafield` = 'items.withdrawn'");
765 print "Upgrade to $DBversion done (fixed MARC framework references to items.withdrawn)\n";
766 SetVersion ($DBversion);
769 $DBversion = "3.00.00.033";
770 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
771 $dbh->do("INSERT INTO `userflags` VALUES(17,'staffaccess','Modify login / permissions for staff users',0)");
772 print "Upgrade to $DBversion done (Adding permissions flag for staff member access modification. )\n";
773 SetVersion ($DBversion);
776 $DBversion = "3.00.00.034";
777 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
778 $dbh->do("ALTER TABLE `virtualshelves` ADD COLUMN `sortfield` VARCHAR(16) ");
779 print "Upgrade to $DBversion done (Adding sortfield for Virtual Shelves. )\n";
780 SetVersion ($DBversion);
783 $DBversion = "3.00.00.035";
784 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
785 $dbh->do("UPDATE marc_subfield_structure
786 SET authorised_value = 'cn_source'
787 WHERE kohafield IN ('items.cn_source', 'biblioitems.cn_source')
788 AND (authorised_value is NULL OR authorised_value = '')");
789 print "Upgrade to $DBversion done (MARC frameworks: make classification source a drop-down)\n";
790 SetVersion ($DBversion);
793 $DBversion = "3.00.00.036";
794 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
795 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('OPACItemsResultsDisplay','statuses','statuses : show only the status of items in result list. itemdisplay : show full location of items (branch+location+callnumber) as in staff interface','statuses|itemdetails','Choice');");
796 print "Upgrade to $DBversion done (OPACItemsResultsDisplay systempreference added)\n";
797 SetVersion ($DBversion);
800 $DBversion = "3.00.00.037";
801 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
802 $dbh->do("ALTER TABLE `borrowers` ADD COLUMN `altcontactfirstname` varchar(255)");
803 $dbh->do("ALTER TABLE `borrowers` ADD COLUMN `altcontactsurname` varchar(255)");
804 $dbh->do("ALTER TABLE `borrowers` ADD COLUMN `altcontactaddress1` varchar(255)");
805 $dbh->do("ALTER TABLE `borrowers` ADD COLUMN `altcontactaddress2` varchar(255)");
806 $dbh->do("ALTER TABLE `borrowers` ADD COLUMN `altcontactaddress3` varchar(255)");
807 $dbh->do("ALTER TABLE `borrowers` ADD COLUMN `altcontactzipcode` varchar(50)");
808 $dbh->do("ALTER TABLE `borrowers` ADD COLUMN `altcontactphone` varchar(50)");
809 print "Upgrade to $DBversion done (Adding Alternative Contact Person information to borrowers table)\n";
810 SetVersion ($DBversion);
813 $DBversion = "3.00.00.038";
814 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
815 $dbh->do("UPDATE `systempreferences` set explanation='Choose the fines mode, off, test (emails admin report) or production (accrue overdue fines). Requires fines cron script' , options='off|test|production' where variable='finesMode'");
816 $dbh->do("DELETE FROM `systempreferences` WHERE variable='hideBiblioNumber'");
817 print "Upgrade to $DBversion done ('alter finesMode systempreference, remove superfluous syspref.')\n";
818 SetVersion ($DBversion);
821 $DBversion = "3.00.00.039";
822 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
823 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('uppercasesurnames',0,'If ON, surnames are converted to upper case in patron entry form',NULL,'YesNo')");
824 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('CircControl','ItemHomeLibrary','Specify the agency that controls the circulation and fines policy','PickupLibrary|PatronLibrary|ItemHomeLibrary','Choice')");
825 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('finesCalendar','noFinesWhenClosed','Specify whether to use the Calendar in calculating duedates and fines','ignoreCalendar|noFinesWhenClosed','Choice')");
826 # $dbh->do("DELETE FROM `systempreferences` WHERE variable='HomeOrHoldingBranch'"); # Bug #2752
827 print "Upgrade to $DBversion done ('add circ sysprefs CircControl, finesCalendar, and uppercasesurnames, and delete HomeOrHoldingBranch.')\n";
828 SetVersion ($DBversion);
831 $DBversion = "3.00.00.040";
832 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
833 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('previousIssuesDefaultSortOrder','asc','Specify the sort order of Previous Issues on the circulation page','asc|desc','Choice')");
834 $dbh->do("INSERT INTO systempreferences (variable,value,explanation,options,type) VALUES('todaysIssuesDefaultSortOrder','desc','Specify the sort order of Todays Issues on the circulation page','asc|desc','Choice')");
835 print "Upgrade to $DBversion done ('add circ sysprefs todaysIssuesDefaultSortOrder and previousIssuesDefaultSortOrder.')\n";
836 SetVersion ($DBversion);
840 $DBversion = "3.00.00.041";
841 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
842 # Strictly speaking it is not necessary to explicitly change
843 # NULL values to 0, because the ALTER TABLE statement will do that.
844 # However, setting them first avoids a warning.
845 $dbh->do("UPDATE items SET notforloan = 0 WHERE notforloan IS NULL");
846 $dbh->do("UPDATE items SET damaged = 0 WHERE damaged IS NULL");
847 $dbh->do("UPDATE items SET itemlost = 0 WHERE itemlost IS NULL");
848 $dbh->do("UPDATE items SET wthdrawn = 0 WHERE wthdrawn IS NULL");
849 $dbh->do("ALTER TABLE items
850 MODIFY notforloan tinyint(1) NOT NULL default 0,
851 MODIFY damaged tinyint(1) NOT NULL default 0,
852 MODIFY itemlost tinyint(1) NOT NULL default 0,
853 MODIFY wthdrawn tinyint(1) NOT NULL default 0");
854 $dbh->do("UPDATE deleteditems SET notforloan = 0 WHERE notforloan IS NULL");
855 $dbh->do("UPDATE deleteditems SET damaged = 0 WHERE damaged IS NULL");
856 $dbh->do("UPDATE deleteditems SET itemlost = 0 WHERE itemlost IS NULL");
857 $dbh->do("UPDATE deleteditems SET wthdrawn = 0 WHERE wthdrawn IS NULL");
858 $dbh->do("ALTER TABLE deleteditems
859 MODIFY notforloan tinyint(1) NOT NULL default 0,
860 MODIFY damaged tinyint(1) NOT NULL default 0,
861 MODIFY itemlost tinyint(1) NOT NULL default 0,
862 MODIFY wthdrawn tinyint(1) NOT NULL default 0");
863 print "Upgrade to $DBversion done (disallow NULL in several item status columns)\n";
864 SetVersion ($DBversion);
867 $DBversion = "3.00.00.042";
868 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
869 $dbh->do("ALTER TABLE aqbooksellers CHANGE name name mediumtext NOT NULL");
870 print "Upgrade to $DBversion done (disallow NULL in aqbooksellers.name; part of fix for bug 1251)\n";
871 SetVersion ($DBversion);
874 $DBversion = "3.00.00.043";
875 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
876 $dbh->do("ALTER TABLE `currency` ADD `symbol` varchar(5) default NULL AFTER currency, ADD `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP AFTER symbol");
877 print "Upgrade to $DBversion done (currency table: add symbol and timestamp columns)\n";
878 SetVersion ($DBversion);
881 $DBversion = "3.00.00.044";
882 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
883 $dbh->do("ALTER TABLE deletedborrowers
884 ADD `altcontactfirstname` varchar(255) default NULL,
885 ADD `altcontactsurname` varchar(255) default NULL,
886 ADD `altcontactaddress1` varchar(255) default NULL,
887 ADD `altcontactaddress2` varchar(255) default NULL,
888 ADD `altcontactaddress3` varchar(255) default NULL,
889 ADD `altcontactzipcode` varchar(50) default NULL,
890 ADD `altcontactphone` varchar(50) default NULL
892 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES
893 ('OPACBaseURL',NULL,'Specify the Base URL of the OPAC, e.g., opac.mylibrary.com, the http:// will be added automatically by Koha.',NULL,'Free'),
894 ('language','en','Set the default language in the staff client.',NULL,'Languages'),
895 ('QueryAutoTruncate',1,'If ON, query truncation is enabled by default',NULL,'YesNo'),
896 ('QueryRemoveStopwords',0,'If ON, stopwords listed in the Administration area will be removed from queries',NULL,'YesNo')
898 print "Upgrade to $DBversion done (syncing deletedborrowers table with borrowers table)\n";
899 SetVersion ($DBversion);
902 #-- http://www.w3.org/International/articles/language-tags/
905 $DBversion = "3.00.00.045";
906 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
908 CREATE TABLE language_subtag_registry (
910 type varchar(25), -- language-script-region-variant-extension-privateuse
911 description varchar(25), -- only one of the possible descriptions for ease of reference, see language_descriptions for the complete list
913 KEY `subtag` (`subtag`)
914 ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
916 #-- TODO: add suppress_scripts
917 #-- this maps three letter codes defined in iso639.2 back to their
918 #-- two letter equivilents in rfc4646 (LOC maintains iso639+)
919 $dbh->do("CREATE TABLE language_rfc4646_to_iso639 (
920 rfc4646_subtag varchar(25),
921 iso639_2_code varchar(25),
922 KEY `rfc4646_subtag` (`rfc4646_subtag`)
923 ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
925 $dbh->do("CREATE TABLE language_descriptions (
929 description varchar(255),
931 ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
933 #-- bi-directional support, keyed by script subcode
934 $dbh->do("CREATE TABLE language_script_bidi (
935 rfc4646_subtag varchar(25), -- script subtag, Arab, Hebr, etc.
936 bidi varchar(3), -- rtl ltr
937 KEY `rfc4646_subtag` (`rfc4646_subtag`)
938 ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
940 #-- BIDI Stuff, Arabic and Hebrew
941 $dbh->do("INSERT INTO language_script_bidi(rfc4646_subtag,bidi)
942 VALUES( 'Arab', 'rtl')");
943 $dbh->do("INSERT INTO language_script_bidi(rfc4646_subtag,bidi)
944 VALUES( 'Hebr', 'rtl')");
946 #-- TODO: need to map language subtags to script subtags for detection
947 #-- of bidi when script is not specified (like ar, he)
948 $dbh->do("CREATE TABLE language_script_mapping (
949 language_subtag varchar(25),
950 script_subtag varchar(25),
951 KEY `language_subtag` (`language_subtag`)
952 ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
954 #-- Default mappings between script and language subcodes
955 $dbh->do("INSERT INTO language_script_mapping(language_subtag,script_subtag)
956 VALUES( 'ar', 'Arab')");
957 $dbh->do("INSERT INTO language_script_mapping(language_subtag,script_subtag)
958 VALUES( 'he', 'Hebr')");
960 print "Upgrade to $DBversion done (adding language subtag registry and basic BiDi support NOTE: You should import the subtag registry SQL)\n";
961 SetVersion ($DBversion);
964 $DBversion = "3.00.00.046";
965 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
966 $dbh->do("ALTER TABLE `subscription` CHANGE `numberlength` `numberlength` int(11) default '0' ,
967 CHANGE `weeklength` `weeklength` int(11) default '0'");
968 $dbh->do("CREATE TABLE `serialitems` (`serialid` int(11) NOT NULL, `itemnumber` int(11) NOT NULL, UNIQUE KEY `serialididx` (`serialid`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
969 $dbh->do("INSERT INTO `serialitems` SELECT `serialid`,`itemnumber` from serial where NOT ISNULL(itemnumber) && itemnumber <> '' && itemnumber NOT LIKE '%,%'");
970 print "Upgrade to $DBversion done (Add serialitems table to link serial issues to items. )\n";
971 SetVersion ($DBversion);
974 $DBversion = "3.00.00.047";
975 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
976 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('OpacRenewalAllowed',0,'If ON, users can renew their issues directly from their OPAC account',NULL,'YesNo');");
977 print "Upgrade to $DBversion done ( Added OpacRenewalAllowed syspref )\n";
978 SetVersion ($DBversion);
981 $DBversion = "3.00.00.048";
982 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
983 $dbh->do("ALTER TABLE `items` ADD `more_subfields_xml` longtext default NULL AFTER `itype`");
984 print "Upgrade to $DBversion done (added items.more_subfields_xml)\n";
985 SetVersion ($DBversion);
988 $DBversion = "3.00.00.049";
989 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
990 $dbh->do("ALTER TABLE `z3950servers` ADD `encoding` text default NULL AFTER type ");
991 print "Upgrade to $DBversion done ( Added encoding field to z3950servers table )\n";
992 SetVersion ($DBversion);
995 $DBversion = "3.00.00.050";
996 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
997 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('OpacHighlightedWords','0','If Set, query matched terms are highlighted in OPAC',NULL,'YesNo');");
998 print "Upgrade to $DBversion done ( Added OpacHighlightedWords syspref )\n";
999 SetVersion ($DBversion);
1002 $DBversion = "3.00.00.051";
1003 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1004 $dbh->do("UPDATE systempreferences SET explanation = 'Define the current theme for the OPAC interface.' WHERE variable = 'opacthemes';");
1005 print "Upgrade to $DBversion done ( Corrected opacthemes explanation. )\n";
1006 SetVersion ($DBversion);
1009 $DBversion = "3.00.00.052";
1010 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1011 $dbh->do("ALTER TABLE `deleteditems` ADD `more_subfields_xml` LONGTEXT DEFAULT NULL AFTER `itype`");
1012 print "Upgrade to $DBversion done ( Adding missing column to deleteditems table. )\n";
1013 SetVersion ($DBversion);
1016 $DBversion = "3.00.00.053";
1017 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1018 $dbh->do("CREATE TABLE `printers_profile` (
1019 `prof_id` int(4) NOT NULL auto_increment,
1020 `printername` varchar(40) NOT NULL,
1021 `tmpl_id` int(4) NOT NULL,
1022 `paper_bin` varchar(20) NOT NULL,
1023 `offset_horz` float default NULL,
1024 `offset_vert` float default NULL,
1025 `creep_horz` float default NULL,
1026 `creep_vert` float default NULL,
1027 `unit` char(20) NOT NULL default 'POINT',
1028 PRIMARY KEY (`prof_id`),
1029 UNIQUE KEY `printername` (`printername`,`tmpl_id`,`paper_bin`),
1030 CONSTRAINT `printers_profile_pnfk_1` FOREIGN KEY (`tmpl_id`) REFERENCES `labels_templates` (`tmpl_id`) ON DELETE CASCADE ON UPDATE CASCADE
1031 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ");
1032 $dbh->do("CREATE TABLE `labels_profile` (
1033 `tmpl_id` int(4) NOT NULL,
1034 `prof_id` int(4) NOT NULL,
1035 UNIQUE KEY `tmpl_id` (`tmpl_id`),
1036 UNIQUE KEY `prof_id` (`prof_id`)
1037 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ");
1038 print "Upgrade to $DBversion done ( Printer Profile tables added )\n";
1039 SetVersion ($DBversion);
1042 $DBversion = "3.00.00.054";
1043 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1044 $dbh->do("UPDATE systempreferences SET options = 'incremental|annual|hbyymmincr|OFF', explanation = 'Used to autogenerate a barcode: incremental will be of the form 1, 2, 3; annual of the form 2007-0001, 2007-0002; hbyymmincr of the form HB08010001 where HB = Home Branch' WHERE variable = 'autoBarcode';");
1045 print "Upgrade to $DBversion done ( Added another barcode autogeneration sequence to barcode.pl. )\n";
1046 SetVersion ($DBversion);
1049 $DBversion = "3.00.00.055";
1050 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1051 $dbh->do("ALTER TABLE `zebraqueue` ADD KEY `zebraqueue_lookup` (`server`, `biblio_auth_number`, `operation`, `done`)");
1052 print "Upgrade to $DBversion done ( Added index on zebraqueue. )\n";
1053 SetVersion ($DBversion);
1055 $DBversion = "3.00.00.056";
1056 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1057 if (C4::Context->preference("marcflavour") eq 'UNIMARC') {
1058 $dbh->do("INSERT INTO `marc_subfield_structure` (`tagfield`, `tagsubfield`, `liblibrarian`, `libopac`, `repeatable`, `mandatory`, `kohafield`, `tab`, `authorised_value` , `authtypecode`, `value_builder`, `isurl`, `hidden`, `frameworkcode`, `seealso`, `link`, `defaultvalue`) VALUES ('995', 'v', 'Note sur le N° de périodique','Note sur le N° de périodique', 0, 0, 'items.enumchron', 10, '', '', '', 0, 0, '', '', '', NULL) ");
1060 $dbh->do("INSERT INTO `marc_subfield_structure` (`tagfield`, `tagsubfield`, `liblibrarian`, `libopac`, `repeatable`, `mandatory`, `kohafield`, `tab`, `authorised_value` , `authtypecode`, `value_builder`, `isurl`, `hidden`, `frameworkcode`, `seealso`, `link`, `defaultvalue`) VALUES ('952', 'h', 'Serial Enumeration / chronology','Serial Enumeration / chronology', 0, 0, 'items.enumchron', 10, '', '', '', 0, 0, '', '', '', NULL) ");
1062 $dbh->do("ALTER TABLE `items` ADD `enumchron` VARCHAR(80) DEFAULT NULL;");
1063 print "Upgrade to $DBversion done ( Added item.enumchron column, and framework map to 952h )\n";
1064 SetVersion ($DBversion);
1067 $DBversion = "3.00.00.057";
1068 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1069 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('OAI-PMH','0','if ON, OAI-PMH server is enabled',NULL,'YesNo');");
1070 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('OAI-PMH:archiveID','KOHA-OAI-TEST','OAI-PMH archive identification',NULL,'Free');");
1071 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('OAI-PMH:MaxCount','50','OAI-PMH maximum number of records by answer to ListRecords and ListIdentifiers queries',NULL,'Integer');");
1072 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('OAI-PMH:Set','SET,Experimental set\r\nSET:SUBSET,Experimental subset','OAI-PMH exported set, the set name is followed by a comma and a short description, one set by line',NULL,'Free');");
1073 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('OAI-PMH:Subset',\"itemtype='BOOK'\",'Restrict answer to matching raws of the biblioitems table (experimental)',NULL,'Free');");
1074 SetVersion ($DBversion);
1077 $DBversion = "3.00.00.058";
1078 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1079 $dbh->do("ALTER TABLE `opac_news`
1080 CHANGE `lang` `lang` VARCHAR( 25 )
1082 COLLATE utf8_general_ci
1083 NOT NULL default ''");
1084 print "Upgrade to $DBversion done ( lang field in opac_news made longer )\n";
1085 SetVersion ($DBversion);
1088 $DBversion = "3.00.00.059";
1089 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1091 $dbh->do("CREATE TABLE IF NOT EXISTS `labels_templates` (
1092 `tmpl_id` int(4) NOT NULL auto_increment,
1093 `tmpl_code` char(100) default '',
1094 `tmpl_desc` char(100) default '',
1095 `page_width` float default '0',
1096 `page_height` float default '0',
1097 `label_width` float default '0',
1098 `label_height` float default '0',
1099 `topmargin` float default '0',
1100 `leftmargin` float default '0',
1101 `cols` int(2) default '0',
1102 `rows` int(2) default '0',
1103 `colgap` float default '0',
1104 `rowgap` float default '0',
1105 `active` int(1) default NULL,
1106 `units` char(20) default 'PX',
1107 `fontsize` int(4) NOT NULL default '3',
1108 PRIMARY KEY (`tmpl_id`)
1109 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;");
1110 $dbh->do("CREATE TABLE IF NOT EXISTS `printers_profile` (
1111 `prof_id` int(4) NOT NULL auto_increment,
1112 `printername` varchar(40) NOT NULL,
1113 `tmpl_id` int(4) NOT NULL,
1114 `paper_bin` varchar(20) NOT NULL,
1115 `offset_horz` float default NULL,
1116 `offset_vert` float default NULL,
1117 `creep_horz` float default NULL,
1118 `creep_vert` float default NULL,
1119 `unit` char(20) NOT NULL default 'POINT',
1120 PRIMARY KEY (`prof_id`),
1121 UNIQUE KEY `printername` (`printername`,`tmpl_id`,`paper_bin`),
1122 CONSTRAINT `printers_profile_pnfk_1` FOREIGN KEY (`tmpl_id`) REFERENCES `labels_templates` (`tmpl_id`) ON DELETE CASCADE ON UPDATE CASCADE
1123 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ");
1124 print "Upgrade to $DBversion done ( Added labels_templates table if it did not exist. )\n";
1125 SetVersion ($DBversion);
1128 $DBversion = "3.00.00.060";
1129 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1130 $dbh->do("CREATE TABLE IF NOT EXISTS `patronimage` (
1131 `cardnumber` varchar(16) NOT NULL,
1132 `mimetype` varchar(15) NOT NULL,
1133 `imagefile` mediumblob NOT NULL,
1134 PRIMARY KEY (`cardnumber`),
1135 CONSTRAINT `patronimage_fk1` FOREIGN KEY (`cardnumber`) REFERENCES `borrowers` (`cardnumber`) ON DELETE CASCADE ON UPDATE CASCADE
1136 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;");
1137 print "Upgrade to $DBversion done ( Added patronimage table. )\n";
1138 SetVersion ($DBversion);
1141 $DBversion = "3.00.00.061";
1142 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1143 $dbh->do("ALTER TABLE labels_templates ADD COLUMN font char(10) NOT NULL DEFAULT 'TR';");
1144 print "Upgrade to $DBversion done ( Added font column to labels_templates )\n";
1145 SetVersion ($DBversion);
1148 $DBversion = "3.00.00.062";
1149 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1150 $dbh->do("CREATE TABLE `old_issues` (
1151 `borrowernumber` int(11) default NULL,
1152 `itemnumber` int(11) default NULL,
1153 `date_due` date default NULL,
1154 `branchcode` varchar(10) default NULL,
1155 `issuingbranch` varchar(18) default NULL,
1156 `returndate` date default NULL,
1157 `lastreneweddate` date default NULL,
1158 `return` varchar(4) default NULL,
1159 `renewals` tinyint(4) default NULL,
1160 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1161 `issuedate` date default NULL,
1162 KEY `old_issuesborridx` (`borrowernumber`),
1163 KEY `old_issuesitemidx` (`itemnumber`),
1164 KEY `old_bordate` (`borrowernumber`,`timestamp`),
1165 CONSTRAINT `old_issues_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`)
1166 ON DELETE SET NULL ON UPDATE SET NULL,
1167 CONSTRAINT `old_issues_ibfk_2` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`)
1168 ON DELETE SET NULL ON UPDATE SET NULL
1169 ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
1170 $dbh->do("CREATE TABLE `old_reserves` (
1171 `borrowernumber` int(11) default NULL,
1172 `reservedate` date default NULL,
1173 `biblionumber` int(11) default NULL,
1174 `constrainttype` varchar(1) default NULL,
1175 `branchcode` varchar(10) default NULL,
1176 `notificationdate` date default NULL,
1177 `reminderdate` date default NULL,
1178 `cancellationdate` date default NULL,
1179 `reservenotes` mediumtext,
1180 `priority` smallint(6) default NULL,
1181 `found` varchar(1) default NULL,
1182 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1183 `itemnumber` int(11) default NULL,
1184 `waitingdate` date default NULL,
1185 KEY `old_reserves_borrowernumber` (`borrowernumber`),
1186 KEY `old_reserves_biblionumber` (`biblionumber`),
1187 KEY `old_reserves_itemnumber` (`itemnumber`),
1188 KEY `old_reserves_branchcode` (`branchcode`),
1189 CONSTRAINT `old_reserves_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`)
1190 ON DELETE SET NULL ON UPDATE SET NULL,
1191 CONSTRAINT `old_reserves_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`)
1192 ON DELETE SET NULL ON UPDATE SET NULL,
1193 CONSTRAINT `old_reserves_ibfk_3` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`)
1194 ON DELETE SET NULL ON UPDATE SET NULL
1195 ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
1197 # move closed transactions to old_* tables
1198 $dbh->do("INSERT INTO old_issues SELECT * FROM issues WHERE returndate IS NOT NULL");
1199 $dbh->do("DELETE FROM issues WHERE returndate IS NOT NULL");
1200 $dbh->do("INSERT INTO old_reserves SELECT * FROM reserves WHERE cancellationdate IS NOT NULL OR found = 'F'");
1201 $dbh->do("DELETE FROM reserves WHERE cancellationdate IS NOT NULL OR found = 'F'");
1203 print "Upgrade to $DBversion done ( Added old_issues and old_reserves tables )\n";
1204 SetVersion ($DBversion);
1207 $DBversion = "3.00.00.063";
1208 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1209 $dbh->do("ALTER TABLE deleteditems
1210 CHANGE COLUMN booksellerid booksellerid MEDIUMTEXT DEFAULT NULL,
1211 ADD COLUMN enumchron VARCHAR(80) DEFAULT NULL AFTER more_subfields_xml,
1212 ADD COLUMN copynumber SMALLINT(6) DEFAULT NULL AFTER enumchron;");
1213 $dbh->do("ALTER TABLE items
1214 CHANGE COLUMN booksellerid booksellerid MEDIUMTEXT,
1215 ADD COLUMN copynumber SMALLINT(6) DEFAULT NULL AFTER enumchron;");
1216 print "Upgrade to $DBversion done ( Changed items.booksellerid and deleteditems.booksellerid to MEDIUMTEXT and added missing items.copynumber and deleteditems.copynumber to fix Bug 1927)\n";
1217 SetVersion ($DBversion);
1220 $DBversion = "3.00.00.064";
1221 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1222 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('AmazonLocale','US','Use to set the Locale of your Amazon.com Web Services','US|CA|DE|FR|JP|UK','Choice');");
1223 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('AWSAccessKeyID','','See: http://aws.amazon.com','','free');");
1224 $dbh->do("DELETE FROM `systempreferences` WHERE variable='AmazonDevKey';");
1225 $dbh->do("DELETE FROM `systempreferences` WHERE variable='XISBNAmazonSimilarItems';");
1226 $dbh->do("DELETE FROM `systempreferences` WHERE variable='OPACXISBNAmazonSimilarItems';");
1227 print "Upgrade to $DBversion done (IMPORTANT: Upgrading to Amazon.com Associates Web Service 4.0 ) \n";
1228 SetVersion ($DBversion);
1231 $DBversion = "3.00.00.065";
1232 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1233 $dbh->do("CREATE TABLE `patroncards` (
1234 `cardid` int(11) NOT NULL auto_increment,
1235 `batch_id` varchar(10) NOT NULL default '1',
1236 `borrowernumber` int(11) NOT NULL,
1237 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1238 PRIMARY KEY (`cardid`),
1239 KEY `patroncards_ibfk_1` (`borrowernumber`),
1240 CONSTRAINT `patroncards_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE
1241 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;");
1242 print "Upgrade to $DBversion done (Adding patroncards table for patroncards generation feature. ) \n";
1243 SetVersion ($DBversion);
1246 $DBversion = "3.00.00.066";
1247 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1248 $dbh->do("ALTER TABLE `virtualshelfcontents` MODIFY `dateadded` timestamp NOT NULL
1249 DEFAULT CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP;
1251 print "Upgrade to $DBversion done (fix for bug 1873: virtualshelfcontents dateadded column empty. ) \n";
1252 SetVersion ($DBversion);
1255 $DBversion = "3.00.00.067";
1256 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1257 $dbh->do("UPDATE systempreferences SET explanation = 'Enable patron images for the Staff Client', type = 'YesNo' WHERE variable = 'patronimages'");
1258 print "Upgrade to $DBversion done (Updating patronimages syspref to reflect current kohastructure.sql. ) \n";
1259 SetVersion ($DBversion);
1262 $DBversion = "3.00.00.068";
1263 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1264 $dbh->do("CREATE TABLE `permissions` (
1265 `module_bit` int(11) NOT NULL DEFAULT 0,
1266 `code` varchar(30) DEFAULT NULL,
1267 `description` varchar(255) DEFAULT NULL,
1268 PRIMARY KEY (`module_bit`, `code`),
1269 CONSTRAINT `permissions_ibfk_1` FOREIGN KEY (`module_bit`) REFERENCES `userflags` (`bit`)
1270 ON DELETE CASCADE ON UPDATE CASCADE
1271 ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
1272 $dbh->do("CREATE TABLE `user_permissions` (
1273 `borrowernumber` int(11) NOT NULL DEFAULT 0,
1274 `module_bit` int(11) NOT NULL DEFAULT 0,
1275 `code` varchar(30) DEFAULT NULL,
1276 CONSTRAINT `user_permissions_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`)
1277 ON DELETE CASCADE ON UPDATE CASCADE,
1278 CONSTRAINT `user_permissions_ibfk_2` FOREIGN KEY (`module_bit`, `code`)
1279 REFERENCES `permissions` (`module_bit`, `code`)
1280 ON DELETE CASCADE ON UPDATE CASCADE
1281 ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
1283 $dbh->do("INSERT INTO permissions (module_bit, code, description) VALUES
1284 (13, 'edit_news', 'Write news for the OPAC and staff interfaces'),
1285 (13, 'label_creator', 'Create printable labels and barcodes from catalog and patron data'),
1286 (13, 'edit_calendar', 'Define days when the library is closed'),
1287 (13, 'moderate_comments', 'Moderate patron comments'),
1288 (13, 'edit_notices', 'Define notices'),
1289 (13, 'edit_notice_status_triggers', 'Set notice/status triggers for overdue items'),
1290 (13, 'view_system_logs', 'Browse the system logs'),
1291 (13, 'inventory', 'Perform inventory (stocktaking) of your catalogue'),
1292 (13, 'stage_marc_import', 'Stage MARC records into the reservoir'),
1293 (13, 'manage_staged_marc', 'Managed staged MARC records, including completing and reversing imports'),
1294 (13, 'export_catalog', 'Export bibliographic and holdings data'),
1295 (13, 'import_patrons', 'Import patron data'),
1296 (13, 'delete_anonymize_patrons', 'Delete old borrowers and anonymize circulation history (deletes borrower reading history)'),
1297 (13, 'batch_upload_patron_images', 'Upload patron images in batch or one at a time'),
1298 (13, 'schedule_tasks', 'Schedule tasks to run')");
1300 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('GranularPermissions','0','Use detailed staff user permissions',NULL,'YesNo')");
1302 print "Upgrade to $DBversion done (adding permissions and user_permissions tables and GranularPermissions syspref) \n";
1303 SetVersion ($DBversion);
1305 $DBversion = "3.00.00.069";
1306 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1307 $dbh->do("ALTER TABLE labels_conf CHANGE COLUMN class classification int(1) DEFAULT NULL;");
1308 print "Upgrade to $DBversion done ( Correcting columname in labels_conf )\n";
1309 SetVersion ($DBversion);
1312 $DBversion = "3.00.00.070";
1313 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1314 $sth = $dbh->prepare("SELECT value FROM systempreferences WHERE variable='yuipath'");
1316 my ($value) = $sth->fetchrow;
1317 $value =~ s/2.3.1/2.5.1/;
1318 $dbh->do("UPDATE systempreferences SET value='$value' WHERE variable='yuipath';");
1319 print "Update yuipath syspref to 2.5.1 if necessary\n";
1320 SetVersion ($DBversion);
1323 $DBversion = "3.00.00.071";
1324 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1325 $dbh->do(" ALTER TABLE `subscription` ADD `serialsadditems` TINYINT( 1 ) NOT NULL DEFAULT '0';");
1326 # fill the new field with the previous systempreference value, then drop the syspref
1327 my $sth = $dbh->prepare("SELECT value FROM systempreferences WHERE variable='serialsadditems'");
1329 my ($serialsadditems) = $sth->fetchrow();
1330 $dbh->do("UPDATE subscription SET serialsadditems=$serialsadditems");
1331 $dbh->do("DELETE FROM systempreferences WHERE variable='serialsadditems'");
1332 print "Upgrade to $DBversion done ( moving serialsadditems from syspref to subscription )\n";
1333 SetVersion ($DBversion);
1336 $DBversion = "3.00.00.072";
1337 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1338 $dbh->do("ALTER TABLE labels_conf ADD COLUMN formatstring mediumtext DEFAULT NULL AFTER printingtype");
1339 print "Upgrade to $DBversion done ( Adding format string to labels generator. )\n";
1340 SetVersion ($DBversion);
1343 $DBversion = "3.00.00.073";
1344 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1345 $dbh->do("DROP TABLE IF EXISTS `tags_all`;");
1347 CREATE TABLE `tags_all` (
1348 `tag_id` int(11) NOT NULL auto_increment,
1349 `borrowernumber` int(11) NOT NULL,
1350 `biblionumber` int(11) NOT NULL,
1351 `term` varchar(255) NOT NULL,
1352 `language` int(4) default NULL,
1353 `date_created` datetime NOT NULL,
1354 PRIMARY KEY (`tag_id`),
1355 KEY `tags_borrowers_fk_1` (`borrowernumber`),
1356 KEY `tags_biblionumber_fk_1` (`biblionumber`),
1357 CONSTRAINT `tags_borrowers_fk_1` FOREIGN KEY (`borrowernumber`)
1358 REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1359 CONSTRAINT `tags_biblionumber_fk_1` FOREIGN KEY (`biblionumber`)
1360 REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE
1361 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1363 $dbh->do("DROP TABLE IF EXISTS `tags_approval`;");
1365 CREATE TABLE `tags_approval` (
1366 `term` varchar(255) NOT NULL,
1367 `approved` int(1) NOT NULL default '0',
1368 `date_approved` datetime default NULL,
1369 `approved_by` int(11) default NULL,
1370 `weight_total` int(9) NOT NULL default '1',
1371 PRIMARY KEY (`term`),
1372 KEY `tags_approval_borrowers_fk_1` (`approved_by`),
1373 CONSTRAINT `tags_approval_borrowers_fk_1` FOREIGN KEY (`approved_by`)
1374 REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE
1375 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1377 $dbh->do("DROP TABLE IF EXISTS `tags_index`;");
1379 CREATE TABLE `tags_index` (
1380 `term` varchar(255) NOT NULL,
1381 `biblionumber` int(11) NOT NULL,
1382 `weight` int(9) NOT NULL default '1',
1383 PRIMARY KEY (`term`,`biblionumber`),
1384 KEY `tags_index_biblionumber_fk_1` (`biblionumber`),
1385 CONSTRAINT `tags_index_term_fk_1` FOREIGN KEY (`term`)
1386 REFERENCES `tags_approval` (`term`) ON DELETE CASCADE ON UPDATE CASCADE,
1387 CONSTRAINT `tags_index_biblionumber_fk_1` FOREIGN KEY (`biblionumber`)
1388 REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE
1389 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1392 INSERT INTO `systempreferences` VALUES
1393 ('BakerTaylorBookstoreURL','','','URL template for \"My Libary Bookstore\" links, to which the \"key\" value is appended, and \"https://\" is prepended. It should include your hostname and \"Parent Number\". Make this variable empty to turn MLB links off. Example: ocls.mylibrarybookstore.com/MLB/actions/searchHandler.do?nextPage=bookDetails&parentNum=10923&key=',''),
1394 ('BakerTaylorEnabled','0','','Enable or disable all Baker & Taylor features.','YesNo'),
1395 ('BakerTaylorPassword','','','Baker & Taylor Password for Content Cafe (external content)','Textarea'),
1396 ('BakerTaylorUsername','','','Baker & Taylor Username for Content Cafe (external content)','Textarea'),
1397 ('TagsEnabled','1','','Enables or disables all tagging features. This is the main switch for tags.','YesNo'),
1398 ('TagsExternalDictionary',NULL,'','Path on server to local ispell executable, used to set $Lingua::Ispell::path This dictionary is used as a \"whitelist\" of pre-allowed tags.',''),
1399 ('TagsInputOnDetail','1','','Allow users to input tags from the detail page.', 'YesNo'),
1400 ('TagsInputOnList', '0','','Allow users to input tags from the search results list.', 'YesNo'),
1401 ('TagsModeration', NULL,'','Require tags from patrons to be approved before becoming visible.','YesNo'),
1402 ('TagsShowOnDetail','10','','Number of tags to display on detail page. 0 is off.', 'Integer'),
1403 ('TagsShowOnList', '6','','Number of tags to display on search results list. 0 is off.','Integer')
1405 print "Upgrade to $DBversion done (Baker/Taylor,Tags: sysprefs and tables (tags_all, tags_index, tags_approval)) \n";
1406 SetVersion ($DBversion);
1409 $DBversion = "3.00.00.074";
1410 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1411 $dbh->do( q(update itemtypes set imageurl = concat( 'npl/', imageurl )
1412 where imageurl not like 'http%'
1413 and imageurl is not NULL
1414 and imageurl != '') );
1415 print "Upgrade to $DBversion done (updating imagetype.imageurls to reflect new icon locations.)\n";
1416 SetVersion ($DBversion);
1419 $DBversion = "3.00.00.075";
1420 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1421 $dbh->do( q(alter table authorised_values add imageurl varchar(200) default NULL) );
1422 print "Upgrade to $DBversion done (adding imageurl field to authorised_values table)\n";
1423 SetVersion ($DBversion);
1426 $DBversion = "3.00.00.076";
1427 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1428 $dbh->do("ALTER TABLE import_batches
1429 ADD COLUMN nomatch_action enum('create_new', 'ignore') NOT NULL default 'create_new' AFTER overlay_action");
1430 $dbh->do("ALTER TABLE import_batches
1431 ADD COLUMN item_action enum('always_add', 'add_only_for_matches', 'add_only_for_new', 'ignore')
1432 NOT NULL default 'always_add' AFTER nomatch_action");
1433 $dbh->do("ALTER TABLE import_batches
1434 MODIFY overlay_action enum('replace', 'create_new', 'use_template', 'ignore')
1435 NOT NULL default 'create_new'");
1436 $dbh->do("ALTER TABLE import_records
1437 MODIFY status enum('error', 'staged', 'imported', 'reverted', 'items_reverted',
1438 'ignored') NOT NULL default 'staged'");
1439 $dbh->do("ALTER TABLE import_items
1440 MODIFY status enum('error', 'staged', 'imported', 'reverted', 'ignored') NOT NULL default 'staged'");
1442 print "Upgrade to $DBversion done (changes to import_batches and import_records)\n";
1443 SetVersion ($DBversion);
1446 $DBversion = "3.00.00.077";
1447 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1448 # drop these tables only if they exist and none of them are empty
1449 # these tables are not defined in the packaged 2.2.9, but since it is believed
1450 # that at least one library may be using them in a post-2.2.9 but pre-3.0 Koha,
1451 # some care is taken.
1452 my ($print_error) = $dbh->{PrintError};
1453 $dbh->{PrintError} = 0;
1454 my ($raise_error) = $dbh->{RaiseError};
1455 $dbh->{RaiseError} = 1;
1459 eval { $count = $dbh->do("SELECT 1 FROM categorytable"); };
1463 eval { $count = $dbh->do("SELECT 1 FROM mediatypetable"); };
1467 eval { $count = $dbh->do("SELECT 1 FROM subcategorytable"); };
1473 $dbh->do("DROP TABLE IF EXISTS `categorytable`");
1474 $dbh->do("DROP TABLE IF EXISTS `mediatypetable`");
1475 $dbh->do("DROP TABLE IF EXISTS `subcategorytable`");
1478 $dbh->{PrintError} = $print_error;
1479 $dbh->{RaiseError} = $raise_error;
1480 print "Upgrade to $DBversion done (drop categorytable, subcategorytable, and mediatypetable)\n";
1481 SetVersion ($DBversion);
1484 $DBversion = "3.00.00.078";
1485 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1486 my ($print_error) = $dbh->{PrintError};
1487 $dbh->{PrintError} = 0;
1489 unless ($dbh->do("SELECT 1 FROM browser")) {
1490 $dbh->{PrintError} = $print_error;
1491 $dbh->do("CREATE TABLE `browser` (
1492 `level` int(11) NOT NULL,
1493 `classification` varchar(20) NOT NULL,
1494 `description` varchar(255) NOT NULL,
1495 `number` bigint(20) NOT NULL,
1496 `endnode` tinyint(4) NOT NULL
1497 ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
1499 $dbh->{PrintError} = $print_error;
1500 print "Upgrade to $DBversion done (add browser table if not already present)\n";
1501 SetVersion ($DBversion);
1504 $DBversion = "3.00.00.079";
1505 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1506 my ($print_error) = $dbh->{PrintError};
1507 $dbh->{PrintError} = 0;
1509 $dbh->do("INSERT INTO `systempreferences` (variable, value,options,type, explanation)VALUES
1510 ('AddPatronLists','categorycode','categorycode|category_type','Choice','Allow user to choose what list to pick up from when adding patrons')");
1511 print "Upgrade to $DBversion done (add browser table if not already present)\n";
1512 SetVersion ($DBversion);
1515 $DBversion = "3.00.00.080";
1516 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1517 $dbh->do("ALTER TABLE subscription CHANGE monthlength monthlength int(11) default '0'");
1518 $dbh->do("ALTER TABLE deleteditems MODIFY marc LONGBLOB AFTER copynumber");
1519 $dbh->do("ALTER TABLE aqbooksellers CHANGE name name mediumtext NOT NULL");
1520 print "Upgrade to $DBversion done (catch up on DB schema changes since alpha and beta)\n";
1521 SetVersion ($DBversion);
1524 $DBversion = "3.00.00.081";
1525 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1526 $dbh->do("CREATE TABLE `borrower_attribute_types` (
1527 `code` varchar(10) NOT NULL,
1528 `description` varchar(255) NOT NULL,
1529 `repeatable` tinyint(1) NOT NULL default 0,
1530 `unique_id` tinyint(1) NOT NULL default 0,
1531 `opac_display` tinyint(1) NOT NULL default 0,
1532 `password_allowed` tinyint(1) NOT NULL default 0,
1533 `staff_searchable` tinyint(1) NOT NULL default 0,
1534 `authorised_value_category` varchar(10) default NULL,
1535 PRIMARY KEY (`code`)
1536 ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
1537 $dbh->do("CREATE TABLE `borrower_attributes` (
1538 `borrowernumber` int(11) NOT NULL,
1539 `code` varchar(10) NOT NULL,
1540 `attribute` varchar(30) default NULL,
1541 `password` varchar(30) default NULL,
1542 KEY `borrowernumber` (`borrowernumber`),
1543 KEY `code_attribute` (`code`, `attribute`),
1544 CONSTRAINT `borrower_attributes_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`)
1545 ON DELETE CASCADE ON UPDATE CASCADE,
1546 CONSTRAINT `borrower_attributes_ibfk_2` FOREIGN KEY (`code`) REFERENCES `borrower_attribute_types` (`code`)
1547 ON DELETE CASCADE ON UPDATE CASCADE
1548 ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
1549 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('ExtendedPatronAttributes','0','Use extended patron IDs and attributes',NULL,'YesNo')");
1550 print "Upgrade to $DBversion done (added borrower_attributes and borrower_attribute_types)\n";
1551 SetVersion ($DBversion);
1554 $DBversion = "3.00.00.082";
1555 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1556 $dbh->do( q(alter table accountlines add column lastincrement decimal(28,6) default NULL) );
1557 print "Upgrade to $DBversion done (adding lastincrement column to accountlines table)\n";
1558 SetVersion ($DBversion);
1561 $DBversion = "3.00.00.083";
1562 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1563 $dbh->do( qq(UPDATE systempreferences SET value='local' where variable='yuipath' and value like "%/intranet-tmpl/prog/%"));
1564 print "Upgrade to $DBversion done (Changing yuipath behaviour in managing a local value)\n";
1565 SetVersion ($DBversion);
1567 $DBversion = "3.00.00.084";
1568 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1569 $dbh->do("INSERT INTO systempreferences (variable,value,explanation,options,type) VALUES('RenewSerialAddsSuggestion','0','if ON, adds a new suggestion at serial subscription renewal',NULL,'YesNo')");
1570 $dbh->do("INSERT INTO systempreferences (variable,value,explanation,options,type) VALUES('GoogleJackets','0','if ON, displays jacket covers from Google Books API',NULL,'YesNo')");
1571 print "Upgrade to $DBversion done (add new sysprefs)\n";
1572 SetVersion ($DBversion);
1575 $DBversion = "3.00.00.085";
1576 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1577 if (C4::Context->preference("marcflavour") eq 'MARC21') {
1578 $dbh->do("UPDATE marc_subfield_structure SET tab = 0 WHERE tab = 9 AND tagfield = '037'");
1579 $dbh->do("UPDATE marc_subfield_structure SET tab = 1 WHERE tab = 6 AND tagfield in ('100', '110', '111', '130')");
1580 $dbh->do("UPDATE marc_subfield_structure SET tab = 2 WHERE tab = 6 AND tagfield in ('240', '243')");
1581 $dbh->do("UPDATE marc_subfield_structure SET tab = 4 WHERE tab = 6 AND tagfield in ('400', '410', '411', '440')");
1582 $dbh->do("UPDATE marc_subfield_structure SET tab = 5 WHERE tab = 9 AND tagfield = '584'");
1583 $dbh->do("UPDATE marc_subfield_structure SET tab = 7 WHERE tab = -6 AND tagfield = '760'");
1585 print "Upgrade to $DBversion done (move editing tab of various MARC21 subfields)\n";
1586 SetVersion ($DBversion);
1589 $DBversion = "3.00.00.086";
1590 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1592 "CREATE TABLE `tmp_holdsqueue` (
1593 `biblionumber` int(11) default NULL,
1594 `itemnumber` int(11) default NULL,
1595 `barcode` varchar(20) default NULL,
1596 `surname` mediumtext NOT NULL,
1599 `borrowernumber` int(11) NOT NULL,
1600 `cardnumber` varchar(16) default NULL,
1601 `reservedate` date default NULL,
1603 `itemcallnumber` varchar(30) default NULL,
1604 `holdingbranch` varchar(10) default NULL,
1605 `pickbranch` varchar(10) default NULL,
1607 ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
1609 $dbh->do("INSERT INTO systempreferences (variable,value,explanation,options,type) VALUES('RandomizeHoldsQueueWeight','0','if ON, the holds queue in circulation will be randomized, either based on all location codes, or by the location codes specified in StaticHoldsQueueWeight',NULL,'YesNo')");
1610 $dbh->do("INSERT INTO systempreferences (variable,value,explanation,options,type) VALUES('StaticHoldsQueueWeight','0','Specify a list of library location codes separated by commas -- the list of codes will be traversed and weighted with first values given higher weight for holds fulfillment -- alternatively, if RandomizeHoldsQueueWeight is set, the list will be randomly selective',NULL,'TextArea')");
1612 print "Upgrade to $DBversion done (Table structure for table `tmp_holdsqueue`)\n";
1613 SetVersion ($DBversion);
1616 $DBversion = "3.00.00.087";
1617 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1618 $dbh->do("INSERT INTO `systempreferences` VALUES ('AutoEmailOpacUser','0','','Sends notification emails containing new account details to patrons - when account is created.','YesNo')" );
1619 $dbh->do("INSERT INTO `systempreferences` VALUES ('AutoEmailPrimaryAddress','OFF','email|emailpro|B_email|cardnumber|OFF','Defines the default email address where Account Details emails are sent.','Choice')");
1620 print "Upgrade to $DBversion done (added 2 new 'AutoEmailOpacUser' sysprefs)\n";
1621 SetVersion ($DBversion);
1624 $DBversion = "3.00.00.088";
1625 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1626 $dbh->do("INSERT INTO `systempreferences` (variable,value,options,explanation,type) VALUES ('OPACShelfBrowser','1','','Enable/disable Shelf Browser on item details page','YesNo')");
1627 $dbh->do("INSERT INTO `systempreferences` (variable,value,options,explanation,type) VALUES ('OPACItemHolds','1','Allow OPAC users to place hold on specific items. If OFF, users can only request next available copy.','','YesNo')");
1628 $dbh->do("INSERT INTO `systempreferences` (variable,value,options,explanation,type) VALUES ('XSLTDetailsDisplay','0','','Enable XSL stylesheet control over details page display on OPAC WARNING: MARC21 Only','YesNo')");
1629 $dbh->do("INSERT INTO `systempreferences` (variable,value,options,explanation,type) VALUES ('XSLTResultsDisplay','0','','Enable XSL stylesheet control over results page display on OPAC WARNING: MARC21 Only','YesNo')");
1630 print "Upgrade to $DBversion done (added 2 new 'AutoEmailOpacUser' sysprefs)\n";
1631 SetVersion ($DBversion);
1634 $DBversion = "3.00.00.089";
1635 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1636 $dbh->do("INSERT INTO `systempreferences` (variable,value,options,explanation,type) VALUES('AdvancedSearchTypes','itemtypes','itemtypes|ccode','Select which set of fields comprise the Type limit in the advanced search','Choice')");
1637 print "Upgrade to $DBversion done (added new AdvancedSearchTypes syspref)\n";
1638 SetVersion ($DBversion);
1641 $DBversion = "3.00.00.090";
1642 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1644 CREATE TABLE `branch_borrower_circ_rules` (
1645 `branchcode` VARCHAR(10) NOT NULL,
1646 `categorycode` VARCHAR(10) NOT NULL,
1647 `maxissueqty` int(4) default NULL,
1648 PRIMARY KEY (`categorycode`, `branchcode`),
1649 CONSTRAINT `branch_borrower_circ_rules_ibfk_1` FOREIGN KEY (`categorycode`) REFERENCES `categories` (`categorycode`)
1650 ON DELETE CASCADE ON UPDATE CASCADE,
1651 CONSTRAINT `branch_borrower_circ_rules_ibfk_2` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`)
1652 ON DELETE CASCADE ON UPDATE CASCADE
1653 ) ENGINE=InnoDB DEFAULT CHARSET=utf8
1656 CREATE TABLE `default_borrower_circ_rules` (
1657 `categorycode` VARCHAR(10) NOT NULL,
1658 `maxissueqty` int(4) default NULL,
1659 PRIMARY KEY (`categorycode`),
1660 CONSTRAINT `borrower_borrower_circ_rules_ibfk_1` FOREIGN KEY (`categorycode`) REFERENCES `categories` (`categorycode`)
1661 ON DELETE CASCADE ON UPDATE CASCADE
1662 ) ENGINE=InnoDB DEFAULT CHARSET=utf8
1665 CREATE TABLE `default_branch_circ_rules` (
1666 `branchcode` VARCHAR(10) NOT NULL,
1667 `maxissueqty` int(4) default NULL,
1668 PRIMARY KEY (`branchcode`),
1669 CONSTRAINT `default_branch_circ_rules_ibfk_1` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`)
1670 ON DELETE CASCADE ON UPDATE CASCADE
1671 ) ENGINE=InnoDB DEFAULT CHARSET=utf8
1674 CREATE TABLE `default_circ_rules` (
1675 `singleton` enum('singleton') NOT NULL default 'singleton',
1676 `maxissueqty` int(4) default NULL,
1677 PRIMARY KEY (`singleton`)
1678 ) ENGINE=InnoDB DEFAULT CHARSET=utf8
1680 print "Upgrade to $DBversion done (added several circ rules tables)\n";
1681 SetVersion ($DBversion);
1685 $DBversion = "3.00.00.091";
1686 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1687 $dbh->do(<<'END_SQL');
1688 ALTER TABLE borrowers
1689 ADD `smsalertnumber` varchar(50) default NULL
1692 $dbh->do(<<'END_SQL');
1693 CREATE TABLE `message_attributes` (
1694 `message_attribute_id` int(11) NOT NULL auto_increment,
1695 `message_name` varchar(20) NOT NULL default '',
1696 `takes_days` tinyint(1) NOT NULL default '0',
1697 PRIMARY KEY (`message_attribute_id`),
1698 UNIQUE KEY `message_name` (`message_name`)
1699 ) ENGINE=InnoDB DEFAULT CHARSET=utf8
1702 $dbh->do(<<'END_SQL');
1703 CREATE TABLE `message_transport_types` (
1704 `message_transport_type` varchar(20) NOT NULL,
1705 PRIMARY KEY (`message_transport_type`)
1706 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1709 $dbh->do(<<'END_SQL');
1710 CREATE TABLE `message_transports` (
1711 `message_attribute_id` int(11) NOT NULL,
1712 `message_transport_type` varchar(20) NOT NULL,
1713 `is_digest` tinyint(1) NOT NULL default '0',
1714 `letter_module` varchar(20) NOT NULL default '',
1715 `letter_code` varchar(20) NOT NULL default '',
1716 PRIMARY KEY (`message_attribute_id`,`message_transport_type`,`is_digest`),
1717 KEY `message_transport_type` (`message_transport_type`),
1718 KEY `letter_module` (`letter_module`,`letter_code`),
1719 CONSTRAINT `message_transports_ibfk_1` FOREIGN KEY (`message_attribute_id`) REFERENCES `message_attributes` (`message_attribute_id`) ON DELETE CASCADE ON UPDATE CASCADE,
1720 CONSTRAINT `message_transports_ibfk_2` FOREIGN KEY (`message_transport_type`) REFERENCES `message_transport_types` (`message_transport_type`) ON DELETE CASCADE ON UPDATE CASCADE,
1721 CONSTRAINT `message_transports_ibfk_3` FOREIGN KEY (`letter_module`, `letter_code`) REFERENCES `letter` (`module`, `code`) ON DELETE CASCADE ON UPDATE CASCADE
1722 ) ENGINE=InnoDB DEFAULT CHARSET=utf8
1725 $dbh->do(<<'END_SQL');
1726 CREATE TABLE `borrower_message_preferences` (
1727 `borrower_message_preference_id` int(11) NOT NULL auto_increment,
1728 `borrowernumber` int(11) NOT NULL default '0',
1729 `message_attribute_id` int(11) default '0',
1730 `days_in_advance` int(11) default '0',
1731 `wants_digets` tinyint(1) NOT NULL default '0',
1732 PRIMARY KEY (`borrower_message_preference_id`),
1733 KEY `borrowernumber` (`borrowernumber`),
1734 KEY `message_attribute_id` (`message_attribute_id`),
1735 CONSTRAINT `borrower_message_preferences_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1736 CONSTRAINT `borrower_message_preferences_ibfk_2` FOREIGN KEY (`message_attribute_id`) REFERENCES `message_attributes` (`message_attribute_id`) ON DELETE CASCADE ON UPDATE CASCADE
1737 ) ENGINE=InnoDB DEFAULT CHARSET=utf8
1740 $dbh->do(<<'END_SQL');
1741 CREATE TABLE `borrower_message_transport_preferences` (
1742 `borrower_message_preference_id` int(11) NOT NULL default '0',
1743 `message_transport_type` varchar(20) NOT NULL default '0',
1744 PRIMARY KEY (`borrower_message_preference_id`,`message_transport_type`),
1745 KEY `message_transport_type` (`message_transport_type`),
1746 CONSTRAINT `borrower_message_transport_preferences_ibfk_1` FOREIGN KEY (`borrower_message_preference_id`) REFERENCES `borrower_message_preferences` (`borrower_message_preference_id`) ON DELETE CASCADE ON UPDATE CASCADE,
1747 CONSTRAINT `borrower_message_transport_preferences_ibfk_2` FOREIGN KEY (`message_transport_type`) REFERENCES `message_transport_types` (`message_transport_type`) ON DELETE CASCADE ON UPDATE CASCADE
1748 ) ENGINE=InnoDB DEFAULT CHARSET=utf8
1751 $dbh->do(<<'END_SQL');
1752 CREATE TABLE `message_queue` (
1753 `message_id` int(11) NOT NULL auto_increment,
1754 `borrowernumber` int(11) NOT NULL,
1757 `message_transport_type` varchar(20) NOT NULL,
1758 `status` enum('sent','pending','failed','deleted') NOT NULL default 'pending',
1759 `time_queued` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1760 KEY `message_id` (`message_id`),
1761 KEY `borrowernumber` (`borrowernumber`),
1762 KEY `message_transport_type` (`message_transport_type`),
1763 CONSTRAINT `messageq_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1764 CONSTRAINT `messageq_ibfk_2` FOREIGN KEY (`message_transport_type`) REFERENCES `message_transport_types` (`message_transport_type`) ON DELETE RESTRICT ON UPDATE CASCADE
1765 ) ENGINE=InnoDB DEFAULT CHARSET=utf8
1768 $dbh->do(<<'END_SQL');
1769 INSERT INTO `systempreferences`
1770 (variable,value,explanation,options,type)
1772 ('EnhancedMessagingPreferences',0,'If ON, allows patrons to select to receive additional messages about items due or nearly due.','','YesNo')
1775 $dbh->do( <<'END_SQL');
1776 INSERT INTO `letter`
1777 (module, code, name, title, content)
1779 ('circulation','DUE','Item Due Reminder','Item Due Reminder','Dear <<borrowers.firstname>> <<borrowers.surname>>,\r\n\r\nThe following item is now due:\r\n\r\n<<biblio.title>> by <<biblio.author>>'),
1780 ('circulation','DUEDGST','Item Due Reminder (Digest)','Item Due Reminder','You have <<count>> items due'),
1781 ('circulation','PREDUE','Advance Notice of Item Due','Advance Notice of Item Due','Dear <<borrowers.firstname>> <<borrowers.surname>>,\r\n\r\nThe following item will be due soon:\r\n\r\n<<biblio.title>> by <<biblio.author>>'),
1782 ('circulation','PREDUEDGST','Advance Notice of Item Due (Digest)','Advance Notice of Item Due','You have <<count>> items due soon'),
1783 ('circulation','EVENT','Upcoming Library Event','Upcoming Library Event','Dear <<borrowers.firstname>> <<borrowers.surname>>,\r\n\r\nThis is a reminder of an upcoming library event in which you have expressed interest.');
1787 'installer/data/mysql/en/mandatory/message_transport_types.sql',
1788 'installer/data/mysql/en/optional/sample_notices_message_attributes.sql',
1789 'installer/data/mysql/en/optional/sample_notices_message_transports.sql',
1792 my $installer = C4::Installer->new();
1793 foreach my $script ( @sql_scripts ) {
1794 my $full_path = $installer->get_file_path_from_name($script);
1795 my $error = $installer->load_sql($full_path);
1796 warn $error if $error;
1799 print "Upgrade to $DBversion done (Table structure for table `message_queue`, `message_transport_types`, `message_attributes`, `message_transports`, `borrower_message_preferences`, and `borrower_message_transport_preferences`. Alter `borrowers` table,\n";
1800 SetVersion ($DBversion);
1803 $DBversion = "3.00.00.092";
1804 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1805 $dbh->do("INSERT INTO `systempreferences` (variable,value,options,explanation,type) VALUES('AllowOnShelfHolds', '0', '', 'Allow hold requests to be placed on items that are not on loan', 'YesNo')");
1806 $dbh->do("INSERT INTO `systempreferences` (variable,value,options,explanation,type) VALUES('AllowHoldsOnDamagedItems', '1', '', 'Allow hold requests to be placed on damaged items', 'YesNo')");
1807 print "Upgrade to $DBversion done (added new AllowOnShelfHolds syspref)\n";
1808 SetVersion ($DBversion);
1811 $DBversion = "3.00.00.093";
1812 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1813 $dbh->do("ALTER TABLE `items` MODIFY COLUMN `copynumber` VARCHAR(32) DEFAULT NULL");
1814 $dbh->do("ALTER TABLE `deleteditems` MODIFY COLUMN `copynumber` VARCHAR(32) DEFAULT NULL");
1815 print "Upgrade to $DBversion done (Change data type of items.copynumber to allow free text)\n";
1816 SetVersion ($DBversion);
1819 $DBversion = "3.00.00.094";
1820 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1821 $dbh->do("ALTER TABLE `marc_subfield_structure` MODIFY `tagsubfield` VARCHAR(1) NOT NULL DEFAULT '' COLLATE utf8_bin");
1822 print "Upgrade to $DBversion done (Change Collation of marc_subfield_structure to allow mixed case in subfield labels.)\n";
1823 SetVersion ($DBversion);
1826 $DBversion = "3.00.00.095";
1827 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1828 if (C4::Context->preference("marcflavour") eq 'MARC21') {
1829 $dbh->do("UPDATE marc_subfield_structure SET authtypecode = 'MEETI_NAME' WHERE authtypecode = 'Meeting Name'");
1830 $dbh->do("UPDATE marc_subfield_structure SET authtypecode = 'CORPO_NAME' WHERE authtypecode = 'CORP0_NAME'");
1832 print "Upgrade to $DBversion done (fix invalid authority types in MARC21 frameworks [bug 2254])\n";
1833 SetVersion ($DBversion);
1836 $DBversion = "3.00.00.096";
1837 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1838 $sth = $dbh->prepare("SHOW COLUMNS FROM borrower_message_preferences LIKE 'wants_digets'");
1840 if (my $row = $sth->fetchrow_hashref) {
1841 $dbh->do("ALTER TABLE borrower_message_preferences CHANGE wants_digets wants_digest tinyint(1) NOT NULL default 0");
1843 print "Upgrade to $DBversion done (fix name borrower_message_preferences.wants_digest)\n";
1844 SetVersion ($DBversion);
1847 $DBversion = '3.00.00.097';
1848 if ( C4::Context->preference('Version') < TransformToNum($DBversion) ) {
1850 $dbh->do('ALTER TABLE message_queue ADD to_address mediumtext default NULL');
1851 $dbh->do('ALTER TABLE message_queue ADD from_address mediumtext default NULL');
1852 $dbh->do('ALTER TABLE message_queue ADD content_type text');
1853 $dbh->do('ALTER TABLE message_queue CHANGE borrowernumber borrowernumber int(11) default NULL');
1855 print "Upgrade to $DBversion done (updating 4 fields in message_queue table)\n";
1856 SetVersion($DBversion);
1859 $DBversion = '3.00.00.098';
1860 if ( C4::Context->preference('Version') < TransformToNum($DBversion) ) {
1862 $dbh->do(q(DELETE FROM message_transport_types WHERE message_transport_type = 'rss'));
1863 $dbh->do(q(DELETE FROM message_transports WHERE message_transport_type = 'rss'));
1865 print "Upgrade to $DBversion done (removing unused RSS message_transport_type)\n";
1866 SetVersion($DBversion);
1869 $DBversion = '3.00.00.099';
1870 if ( C4::Context->preference('Version') < TransformToNum($DBversion) ) {
1871 $dbh->do("INSERT INTO systempreferences (variable,value,options,explanation,type) VALUES('OpacSuppression', '0', '', 'Turn ON the OPAC Suppression feature, requires further setup, ask your system administrator for details', 'YesNo')");
1872 print "Upgrade to $DBversion done (Adding OpacSuppression syspref)\n";
1873 SetVersion($DBversion);
1876 $DBversion = '3.00.00.100';
1877 if ( C4::Context->preference('Version') < TransformToNum($DBversion) ) {
1878 $dbh->do('ALTER TABLE virtualshelves ADD COLUMN lastmodified timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP');
1879 print "Upgrade to $DBversion done (Adding lastmodified column to virtualshelves)\n";
1880 SetVersion($DBversion);
1883 $DBversion = '3.00.00.101';
1884 if ( C4::Context->preference('Version') < TransformToNum($DBversion) ) {
1885 $dbh->do('ALTER TABLE `overduerules` CHANGE `categorycode` `categorycode` VARCHAR(10) NOT NULL');
1886 $dbh->do('ALTER TABLE `deletedborrowers` CHANGE `categorycode` `categorycode` VARCHAR(10) NOT NULL');
1887 print "Upgrade to $DBversion done (Updating columnd definitions for patron category codes in notice/statsu triggers and deletedborrowers tables.)\n";
1888 SetVersion($DBversion);
1891 $DBversion = '3.00.00.102';
1892 if ( C4::Context->preference('Version') < TransformToNum($DBversion) ) {
1893 $dbh->do('ALTER TABLE serialitems MODIFY `serialid` int(11) NOT NULL AFTER itemnumber' );
1894 $dbh->do('ALTER TABLE serialitems DROP KEY serialididx' );
1895 $dbh->do('ALTER TABLE serialitems ADD CONSTRAINT UNIQUE KEY serialitemsidx (itemnumber)' );
1896 # before setting constraint, delete any unvalid data
1897 $dbh->do('DELETE from serialitems WHERE serialid not in (SELECT serial.serialid FROM serial)');
1898 $dbh->do('ALTER TABLE serialitems ADD CONSTRAINT serialitems_sfk_1 FOREIGN KEY (serialid) REFERENCES serial (serialid) ON DELETE CASCADE ON UPDATE CASCADE' );
1899 print "Upgrade to $DBversion done (Updating serialitems table to allow for multiple items per serial fixing kohabug 2380)\n";
1900 SetVersion($DBversion);
1903 $DBversion = "3.00.00.103";
1904 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1905 $dbh->do("DELETE FROM systempreferences WHERE variable='serialsadditems'");
1906 print "Upgrade to $DBversion done ( Verifying the removal of serialsadditems from syspref fixing kohabug 2219)\n";
1907 SetVersion ($DBversion);
1910 $DBversion = "3.00.00.104";
1911 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1912 $dbh->do("DELETE FROM systempreferences WHERE variable='noOPACHolds'");
1913 print "Upgrade to $DBversion done (remove superseded 'noOPACHolds' system preference per bug 2413)\n";
1914 SetVersion ($DBversion);
1917 $DBversion = '3.00.00.105';
1918 if ( C4::Context->preference("Version") < TransformToNum($DBversion) ) {
1920 # it is possible that this syspref is already defined since the feature was added some time ago.
1921 unless ( $dbh->do(q(SELECT variable FROM systempreferences WHERE variable = 'SMSSendDriver')) ) {
1922 $dbh->do(<<'END_SQL');
1923 INSERT INTO `systempreferences`
1924 (variable,value,explanation,options,type)
1926 ('SMSSendDriver','','Sets which SMS::Send driver is used to send SMS messages.','','free')
1929 print "Upgrade to $DBversion done (added SMSSendDriver system preference)\n";
1930 SetVersion($DBversion);
1933 $DBversion = "3.00.00.106";
1934 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1935 $dbh->do("DELETE FROM systempreferences WHERE variable='noOPACHolds'");
1937 # db revision 105 didn't apply correctly, so we're rolling this into 106
1938 $dbh->do("INSERT INTO `systempreferences`
1939 (variable,value,explanation,options,type)
1941 ('SMSSendDriver','','Sets which SMS::Send driver is used to send SMS messages.','','free')");
1943 print "Upgrade to $DBversion done (remove default '0000-00-00' in subscriptionhistory.enddate field)\n";
1944 $dbh->do("ALTER TABLE `subscriptionhistory` CHANGE `enddate` `enddate` DATE NULL DEFAULT NULL ");
1945 $dbh->do("UPDATE subscriptionhistory SET enddate=NULL WHERE enddate='0000-00-00'");
1946 SetVersion ($DBversion);
1949 $DBversion = '3.00.00.107';
1950 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1951 $dbh->do(<<'END_SQL');
1952 UPDATE systempreferences
1953 SET explanation = CONCAT( explanation, '. WARNING: this feature is very resource consuming on collections with large numbers of items.' )
1954 WHERE variable = 'OPACShelfBrowser'
1955 AND explanation NOT LIKE '%WARNING%'
1957 $dbh->do(<<'END_SQL');
1958 UPDATE systempreferences
1959 SET explanation = CONCAT( explanation, '. WARNING: this feature is very resource consuming.' )
1960 WHERE variable = 'CataloguingLog'
1961 AND explanation NOT LIKE '%WARNING%'
1963 $dbh->do(<<'END_SQL');
1964 UPDATE systempreferences
1965 SET explanation = CONCAT( explanation, '. WARNING: using NoZebra on even modest sized collections is very slow.' )
1966 WHERE variable = 'NoZebra'
1967 AND explanation NOT LIKE '%WARNING%'
1969 print "Upgrade to $DBversion done (warning added to OPACShelfBrowser system preference)\n";
1970 SetVersion ($DBversion);
1973 $DBversion = '3.01.00.000';
1974 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1975 print "Upgrade to $DBversion done (start of 3.1)\n";
1976 SetVersion ($DBversion);
1979 $DBversion = '3.01.00.001';
1980 if ( C4::Context->preference('Version') < TransformToNum($DBversion) ) {
1982 CREATE TABLE hold_fill_targets (
1983 `borrowernumber` int(11) NOT NULL,
1984 `biblionumber` int(11) NOT NULL,
1985 `itemnumber` int(11) NOT NULL,
1986 `source_branchcode` varchar(10) default NULL,
1987 `item_level_request` tinyint(4) NOT NULL default 0,
1988 PRIMARY KEY `itemnumber` (`itemnumber`),
1989 KEY `bib_branch` (`biblionumber`, `source_branchcode`),
1990 CONSTRAINT `hold_fill_targets_ibfk_1` FOREIGN KEY (`borrowernumber`)
1991 REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1992 CONSTRAINT `hold_fill_targets_ibfk_2` FOREIGN KEY (`biblionumber`)
1993 REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1994 CONSTRAINT `hold_fill_targets_ibfk_3` FOREIGN KEY (`itemnumber`)
1995 REFERENCES `items` (`itemnumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1996 CONSTRAINT `hold_fill_targets_ibfk_4` FOREIGN KEY (`source_branchcode`)
1997 REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE
1998 ) ENGINE=InnoDB DEFAULT CHARSET=utf8
2001 ALTER TABLE tmp_holdsqueue
2002 ADD item_level_request tinyint(4) NOT NULL default 0
2005 print "Upgrade to $DBversion done (add hold_fill_targets table and a column to tmp_holdsqueue)\n";
2006 SetVersion($DBversion);
2009 $DBversion = '3.01.00.002';
2010 if ( C4::Context->preference('Version') < TransformToNum($DBversion) ) {
2011 # use statistics where available
2013 ALTER TABLE statistics ADD KEY tmp_stats (type, itemnumber, borrowernumber)
2018 SELECT max(datetime)
2020 WHERE type = 'issue'
2021 AND itemnumber = iss.itemnumber
2022 AND borrowernumber = iss.borrowernumber
2024 WHERE issuedate IS NULL;
2026 $dbh->do("ALTER TABLE statistics DROP KEY tmp_stats");
2028 # default to last renewal date
2031 SET issuedate = lastreneweddate
2032 WHERE issuedate IS NULL
2033 and lastreneweddate IS NOT NULL
2036 my $num_bad_issuedates = $dbh->selectrow_array("SELECT COUNT(*) FROM issues WHERE issuedate IS NULL");
2037 if ($num_bad_issuedates > 0) {
2038 print STDERR "After the upgrade to $DBversion, there are still $num_bad_issuedates loan(s) with a NULL (blank) loan date. ",
2039 "Please check the issues table in your database.";
2041 print "Upgrade to $DBversion done (bug 2582: set null issues.issuedate to lastreneweddate)\n";
2042 SetVersion($DBversion);
2045 $DBversion = "3.01.00.003";
2046 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2047 $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')");
2048 print "Upgrade to $DBversion done (add new syspref)\n";
2049 SetVersion ($DBversion);
2052 $DBversion = '3.01.00.004';
2053 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2054 $dbh->do("INSERT INTO systempreferences (variable,value,explanation,options,type) VALUES ('OPACDisplayRequestPriority','0','Show patrons the priority level on holds in the OPAC','','YesNo')");
2055 print "Upgrade to $DBversion done (added OPACDisplayRequestPriority system preference)\n";
2056 SetVersion ($DBversion);
2059 $DBversion = '3.01.00.005';
2060 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2062 INSERT INTO `letter` (module, code, name, title, content)
2063 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>>')
2065 $dbh->do("INSERT INTO `message_attributes` (message_attribute_id, message_name, takes_days) values(4, 'Hold Filled', 0)");
2066 $dbh->do("INSERT INTO `message_transports` (message_attribute_id, message_transport_type, is_digest, letter_module, letter_code) values(4, 'sms', 0, 'reserves', 'HOLD')");
2067 $dbh->do("INSERT INTO `message_transports` (message_attribute_id, message_transport_type, is_digest, letter_module, letter_code) values(4, 'email', 0, 'reserves', 'HOLD')");
2068 print "Upgrade to $DBversion done (Add letter for holds notifications)\n";
2069 SetVersion ($DBversion);
2072 $DBversion = '3.01.00.006';
2073 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2074 $dbh->do("ALTER TABLE `biblioitems` ADD KEY issn (issn)");
2075 print "Upgrade to $DBversion done (add index on biblioitems.issn)\n";
2076 SetVersion ($DBversion);
2079 $DBversion = "3.01.00.007";
2080 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2081 $dbh->do("UPDATE `systempreferences` SET options='70|10' WHERE variable='intranetmainUserblock'");
2082 $dbh->do("UPDATE `systempreferences` SET options='70|10' WHERE variable='intranetuserjs'");
2083 $dbh->do("UPDATE `systempreferences` SET options='70|10' WHERE variable='opacheader'");
2084 $dbh->do("UPDATE `systempreferences` SET options='70|10' WHERE variable='OpacMainUserBlock'");
2085 $dbh->do("UPDATE `systempreferences` SET options='70|10' WHERE variable='OpacNav'");
2086 $dbh->do("UPDATE `systempreferences` SET options='70|10' WHERE variable='opacuserjs'");
2087 $dbh->do("UPDATE `systempreferences` SET options='30|10', type='Textarea' WHERE variable='OAI-PMH:Set'");
2088 $dbh->do("UPDATE `systempreferences` SET options='50' WHERE variable='intranetstylesheet'");
2089 $dbh->do("UPDATE `systempreferences` SET options='50' WHERE variable='intranetcolorstylesheet'");
2090 $dbh->do("UPDATE `systempreferences` SET options='10' WHERE variable='globalDueDate'");
2091 $dbh->do("UPDATE `systempreferences` SET type='Integer' WHERE variable='numSearchResults'");
2092 $dbh->do("UPDATE `systempreferences` SET type='Integer' WHERE variable='OPACnumSearchResults'");
2093 $dbh->do("UPDATE `systempreferences` SET type='Integer' WHERE variable='ReservesMaxPickupDelay'");
2094 $dbh->do("UPDATE `systempreferences` SET type='Integer' WHERE variable='TransfersMaxDaysWarning'");
2095 $dbh->do("UPDATE `systempreferences` SET type='Integer' WHERE variable='StaticHoldsQueueWeight'");
2096 $dbh->do("UPDATE `systempreferences` SET type='Integer' WHERE variable='holdCancelLength'");
2097 $dbh->do("UPDATE `systempreferences` SET type='Integer' WHERE variable='XISBNDailyLimit'");
2098 $dbh->do("UPDATE `systempreferences` SET type='Float' WHERE variable='gist'");
2099 $dbh->do("UPDATE `systempreferences` SET type='Free' WHERE variable='BakerTaylorUsername'");
2100 $dbh->do("UPDATE `systempreferences` SET type='Free' WHERE variable='BakerTaylorPassword'");
2101 $dbh->do("UPDATE `systempreferences` SET type='Textarea', options='70|10' WHERE variable='ISBD'");
2102 $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'");
2103 print "Upgrade to $DBversion done (fix display of many sysprefs)\n";
2104 SetVersion ($DBversion);
2107 $DBversion = '3.01.00.008';
2108 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2110 $dbh->do("CREATE TABLE branch_transfer_limits (
2111 limitId int(8) NOT NULL auto_increment,
2112 toBranch varchar(4) NOT NULL,
2113 fromBranch varchar(4) NOT NULL,
2114 itemtype varchar(4) NOT NULL,
2115 PRIMARY KEY (limitId)
2116 ) ENGINE=InnoDB DEFAULT CHARSET=utf8"
2119 $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')");
2121 print "Upgrade to $DBversion done (added branch_transfer_limits table and UseBranchTransferLimits system preference)\n";
2122 SetVersion ($DBversion);
2125 $DBversion = "3.01.00.009";
2126 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2127 $dbh->do("ALTER TABLE permissions MODIFY `code` varchar(64) DEFAULT NULL");
2128 $dbh->do("ALTER TABLE user_permissions MODIFY `code` varchar(64) DEFAULT NULL");
2129 $dbh->do("INSERT INTO permissions (module_bit, code, description) VALUES ( 1, 'circulate_remaining_permissions', 'Remaining circulation permissions')");
2130 $dbh->do("INSERT INTO permissions (module_bit, code, description) VALUES ( 1, 'override_renewals', 'Override blocked renewals')");
2131 print "Upgrade to $DBversion done (added subpermissions for circulate permission)\n";
2134 $DBversion = '3.01.00.010';
2135 if ( C4::Context->preference('Version') < TransformToNum($DBversion) ) {
2136 $dbh->do("ALTER TABLE `borrower_attributes` MODIFY COLUMN `attribute` VARCHAR(64) DEFAULT NULL");
2137 $dbh->do("ALTER TABLE `borrower_attributes` MODIFY COLUMN `password` VARCHAR(64) DEFAULT NULL");
2138 print "Upgrade to $DBversion done (bug 2687: increase length of borrower attribute fields)\n";
2139 SetVersion ($DBversion);
2142 $DBversion = '3.01.00.011';
2143 if ( C4::Context->preference('Version') < TransformToNum($DBversion) ) {
2145 # Yes, the old value was ^M terminated.
2146 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);";
2148 my $intranetuserjs = C4::Context->preference('intranetuserjs');
2149 if ($intranetuserjs and $intranetuserjs eq $bad_value) {
2150 my $sql = <<'END_SQL';
2151 UPDATE systempreferences
2153 WHERE variable = 'intranetuserjs'
2157 print "Upgrade to $DBversion done (removed bogus intranetuserjs syspref)\n";
2158 SetVersion($DBversion);
2161 $DBversion = "3.01.00.012";
2162 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2163 $dbh->do("INSERT INTO systempreferences (variable,value,explanation,options,type) VALUES('AllowHoldPolicyOverride', '0', 'Allow staff to override hold policies when placing holds',NULL,'YesNo')");
2165 CREATE TABLE `branch_item_rules` (
2166 `branchcode` varchar(10) NOT NULL,
2167 `itemtype` varchar(10) NOT NULL,
2168 `holdallowed` tinyint(1) default NULL,
2169 PRIMARY KEY (`itemtype`,`branchcode`),
2170 KEY `branch_item_rules_ibfk_2` (`branchcode`),
2171 CONSTRAINT `branch_item_rules_ibfk_1` FOREIGN KEY (`itemtype`) REFERENCES `itemtypes` (`itemtype`) ON DELETE CASCADE ON UPDATE CASCADE,
2172 CONSTRAINT `branch_item_rules_ibfk_2` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE
2173 ) ENGINE=InnoDB DEFAULT CHARSET=utf8
2176 CREATE TABLE `default_branch_item_rules` (
2177 `itemtype` varchar(10) NOT NULL,
2178 `holdallowed` tinyint(1) default NULL,
2179 PRIMARY KEY (`itemtype`),
2180 CONSTRAINT `default_branch_item_rules_ibfk_1` FOREIGN KEY (`itemtype`) REFERENCES `itemtypes` (`itemtype`) ON DELETE CASCADE ON UPDATE CASCADE
2181 ) ENGINE=InnoDB DEFAULT CHARSET=utf8
2184 ALTER TABLE default_branch_circ_rules
2185 ADD COLUMN holdallowed tinyint(1) NULL
2188 ALTER TABLE default_circ_rules
2189 ADD COLUMN holdallowed tinyint(1) NULL
2191 print "Upgrade to $DBversion done (Add tables and system preferences for holds policies)\n";
2192 SetVersion ($DBversion);
2195 $DBversion = '3.01.00.013';
2196 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2198 CREATE TABLE item_circulation_alert_preferences (
2199 id int(11) AUTO_INCREMENT,
2200 branchcode varchar(10) NOT NULL,
2201 categorycode varchar(10) NOT NULL,
2202 item_type varchar(10) NOT NULL,
2203 notification varchar(16) NOT NULL,
2205 KEY (branchcode, categorycode, item_type, notification)
2206 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2209 $dbh->do(q{ ALTER TABLE `message_queue` ADD metadata text DEFAULT NULL AFTER content; });
2210 $dbh->do(q{ ALTER TABLE `message_queue` ADD letter_code varchar(64) DEFAULT NULL AFTER metadata; });
2213 INSERT INTO `letter` (`module`, `code`, `name`, `title`, `content`) VALUES
2214 ('circulation','CHECKIN','Item Check-in','Check-ins','The following items have been checked in:\r\n----\r\n<<biblio.title>>\r\n----\r\nThank you.');
2217 INSERT INTO `letter` (`module`, `code`, `name`, `title`, `content`) VALUES
2218 ('circulation','CHECKOUT','Item Checkout','Checkouts','The following items have been checked out:\r\n----\r\n<<biblio.title>>\r\n----\r\nThank you for visiting <<branches.branchname>>.');
2221 $dbh->do(q{INSERT INTO message_attributes (message_attribute_id, message_name, takes_days) VALUES (5, 'Item Check-in', 0);});
2222 $dbh->do(q{INSERT INTO message_attributes (message_attribute_id, message_name, takes_days) VALUES (6, 'Item Checkout', 0);});
2224 $dbh->do(q{INSERT INTO message_transports (message_attribute_id, message_transport_type, is_digest, letter_module, letter_code) VALUES (5, 'email', 0, 'circulation', 'CHECKIN');});
2225 $dbh->do(q{INSERT INTO message_transports (message_attribute_id, message_transport_type, is_digest, letter_module, letter_code) VALUES (5, 'sms', 0, 'circulation', 'CHECKIN');});
2226 $dbh->do(q{INSERT INTO message_transports (message_attribute_id, message_transport_type, is_digest, letter_module, letter_code) VALUES (6, 'email', 0, 'circulation', 'CHECKOUT');});
2227 $dbh->do(q{INSERT INTO message_transports (message_attribute_id, message_transport_type, is_digest, letter_module, letter_code) VALUES (6, 'sms', 0, 'circulation', 'CHECKOUT');});
2229 print "Upgrade to $DBversion done (data for Email Checkout Slips project)\n";
2230 SetVersion ($DBversion);
2233 $DBversion = "3.01.00.014";
2234 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2235 $dbh->do("ALTER TABLE `branch_transfer_limits` CHANGE `itemtype` `itemtype` VARCHAR( 4 ) CHARACTER SET utf8 COLLATE utf8_general_ci NULL");
2236 $dbh->do("ALTER TABLE `branch_transfer_limits` ADD `ccode` VARCHAR( 10 ) NULL ;");
2237 $dbh->do("INSERT INTO `systempreferences` ( `variable` , `value` , `options` , `explanation` , `type` )
2239 'BranchTransferLimitsType', 'ccode', 'itemtype|ccode', 'When using branch transfer limits, choose whether to limit by itemtype or collection code.', 'Choice'
2242 print "Upgrade to $DBversion done ( Updated table for Branch Transfer Limits)\n";
2243 SetVersion ($DBversion);
2246 $DBversion = '3.01.00.015';
2247 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2248 $dbh->do("INSERT INTO systempreferences (variable,value,explanation,options,type) VALUES ('SyndeticsClientCode', '0', 'Client Code for using Syndetics Solutions content','','free')");
2250 $dbh->do("INSERT INTO systempreferences (variable,value,explanation,options,type) VALUES ('SyndeticsEnabled', '0', 'Turn on Syndetics Enhanced Content','','YesNo')");
2252 $dbh->do("INSERT INTO systempreferences (variable,value,explanation,options,type) VALUES ('SyndeticsCoverImages', '0', 'Display Cover Images from Syndetics','','YesNo')");
2254 $dbh->do("INSERT INTO systempreferences (variable,value,explanation,options,type) VALUES ('SyndeticsTOC', '0', 'Display Table of Content information from Syndetics','','YesNo')");
2256 $dbh->do("INSERT INTO systempreferences (variable,value,explanation,options,type) VALUES ('SyndeticsSummary', '0', 'Display Summary Information from Syndetics','','YesNo')");
2258 $dbh->do("INSERT INTO systempreferences (variable,value,explanation,options,type) VALUES ('SyndeticsEditions', '0', 'Display Editions from Syndetics','','YesNo')");
2260 $dbh->do("INSERT INTO systempreferences (variable,value,explanation,options,type) VALUES ('SyndeticsExcerpt', '0', 'Display Excerpts and first chapters on OPAC from Syndetics','','YesNo')");
2262 $dbh->do("INSERT INTO systempreferences (variable,value,explanation,options,type) VALUES ('SyndeticsReviews', '0', 'Display Reviews on OPAC from Syndetics','','YesNo')");
2264 $dbh->do("INSERT INTO systempreferences (variable,value,explanation,options,type) VALUES ('SyndeticsAuthorNotes', '0', 'Display Notes about the Author on OPAC from Syndetics','','YesNo')");
2266 $dbh->do("INSERT INTO systempreferences (variable,value,explanation,options,type) VALUES ('SyndeticsAwards', '0', 'Display Awards on OPAC from Syndetics','','YesNo')");
2268 $dbh->do("INSERT INTO systempreferences (variable,value,explanation,options,type) VALUES ('SyndeticsSeries', '0', 'Display Series information on OPAC from Syndetics','','YesNo')");
2270 $dbh->do("INSERT INTO systempreferences (variable,value,explanation,options,type) VALUES ('SyndeticsCoverImageSize', 'MC', 'Choose the size of the Syndetics Cover Image to display on the OPAC detail page, MC is Medium, LC is Large','MC|LC','Choice')");
2272 $dbh->do("INSERT INTO systempreferences (variable,value,explanation,options,type) VALUES ('OPACAmazonCoverImages', '0', 'Display cover images on OPAC from Amazon Web Services','','YesNo')");
2274 $dbh->do("INSERT INTO systempreferences (variable,value,explanation,options,type) VALUES ('AmazonCoverImages', '0', 'Display Cover Images in Staff Client from Amazon Web Services','','YesNo')");
2276 $dbh->do("UPDATE systempreferences SET variable='AmazonEnabled' WHERE variable = 'AmazonContent'");
2278 $dbh->do("UPDATE systempreferences SET variable='OPACAmazonEnabled' WHERE variable = 'OPACAmazonContent'");
2280 print "Upgrade to $DBversion done (added Syndetics Enhanced Content system preferences)\n";
2281 SetVersion ($DBversion);
2284 $DBversion = "3.01.00.016";
2285 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2286 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('Babeltheque',0,'Turn ON Babeltheque content - See babeltheque.com to subscribe to this service','','YesNo')");
2287 print "Upgrade to $DBversion done (Added Babeltheque syspref)\n";
2288 SetVersion ($DBversion);
2291 $DBversion = "3.01.00.017";
2292 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2293 $dbh->do("ALTER TABLE `subscription` ADD `staffdisplaycount` VARCHAR(10) NULL;");
2294 $dbh->do("ALTER TABLE `subscription` ADD `opacdisplaycount` VARCHAR(10) NULL;");
2295 $dbh->do("INSERT INTO `systempreferences` ( `variable` , `value` , `options` , `explanation` , `type` )
2297 'StaffSerialIssueDisplayCount', '3', '', 'Number of serial issues to display per subscription in the Staff client', 'Integer'
2299 $dbh->do("INSERT INTO `systempreferences` ( `variable` , `value` , `options` , `explanation` , `type` )
2301 'OPACSerialIssueDisplayCount', '3', '', 'Number of serial issues to display per subscription in the OPAC', 'Integer'
2304 print "Upgrade to $DBversion done ( Updated table for Serials Display)\n";
2305 SetVersion ($DBversion);
2308 $DBversion = "3.01.00.018";
2309 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2310 $dbh->do("ALTER TABLE deletedborrowers ADD `smsalertnumber` varchar(50) default NULL");
2311 print "Upgrade to $DBversion done (added deletedborrowers.smsalertnumber, missed in 3.00.00.091)\n";
2312 SetVersion ($DBversion);
2315 $DBversion = "3.01.00.019";
2316 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2317 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('OPACShowCheckoutName','0','Displays in the OPAC the name of patron who has checked out the material. WARNING: Most sites should leave this off. It is intended for corporate or special sites which need to track who has the item.','','YesNo')");
2318 print "Upgrade to $DBversion done (adding OPACShowCheckoutName systempref)\n";
2319 SetVersion ($DBversion);
2322 $DBversion = "3.01.00.020";
2323 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2324 $dbh->do("INSERT INTO systempreferences (variable,value,explanation,options,type)VALUES('LibraryThingForLibrariesID','','See:http://librarything.com/forlibraries/','','free')");
2325 $dbh->do("INSERT INTO systempreferences (variable,value,explanation,options,type)VALUES('LibraryThingForLibrariesEnabled','0','Enable or Disable Library Thing for Libraries Features','','YesNo')");
2326 $dbh->do("INSERT INTO systempreferences (variable,value,explanation,options,type)VALUES('LibraryThingForLibrariesTabbedView','0','Put LibraryThingForLibraries Content in Tabs.','','YesNo')");
2327 print "Upgrade to $DBversion done (adding LibraryThing for Libraries sysprefs)\n";
2328 SetVersion ($DBversion);
2331 $DBversion = "3.01.00.021";
2332 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2333 my $enable_reviews = C4::Context->preference('OPACAmazonEnabled') ? '1' : '0';
2334 $dbh->do("INSERT INTO systempreferences (variable,value,explanation,options,type) VALUES ('OPACAmazonReviews', '$enable_reviews', 'Display Amazon readers reviews on OPAC','','YesNo')");
2335 print "Upgrade to $DBversion done (adding OPACAmazonReviews syspref)\n";
2336 SetVersion ($DBversion);
2339 $DBversion = '3.01.00.022';
2340 if ( C4::Context->preference('Version') < TransformToNum($DBversion) ) {
2341 $dbh->do("ALTER TABLE `labels_conf` MODIFY COLUMN `formatstring` mediumtext DEFAULT NULL");
2342 print "Upgrade to $DBversion done (bug 2945: increase size of labels_conf.formatstring)\n";
2343 SetVersion ($DBversion);
2346 $DBversion = '3.01.00.023';
2347 if ( C4::Context->preference('Version') < TransformToNum($DBversion) ) {
2348 $dbh->do("ALTER TABLE biblioitems MODIFY COLUMN isbn VARCHAR(30) DEFAULT NULL");
2349 $dbh->do("ALTER TABLE deletedbiblioitems MODIFY COLUMN isbn VARCHAR(30) DEFAULT NULL");
2350 $dbh->do("ALTER TABLE import_biblios MODIFY COLUMN isbn VARCHAR(30) DEFAULT NULL");
2351 $dbh->do("ALTER TABLE suggestions MODIFY COLUMN isbn VARCHAR(30) DEFAULT NULL");
2352 print "Upgrade to $DBversion done (bug 2765: increase width of isbn column in several tables)\n";
2353 SetVersion ($DBversion);
2356 $DBversion = "3.01.00.024";
2357 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2358 $dbh->do("ALTER TABLE labels MODIFY COLUMN batch_id int(10) NOT NULL default 1;");
2359 print "Upgrade to $DBversion done (change labels.batch_id from varchar to int)\n";
2360 SetVersion ($DBversion);
2363 $DBversion = '3.01.00.025';
2364 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2365 $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')");
2367 print "Upgrade to $DBversion done (added ceilingDueDate system preference)\n";
2368 SetVersion ($DBversion);
2371 $DBversion = '3.01.00.026';
2372 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2373 $dbh->do("INSERT INTO `systempreferences` ( `variable` , `value` , `options` , `explanation` , `type` ) VALUES ( 'numReturnedItemsToShow', '20', '', 'Number of returned items to show on the check-in page', 'Integer')");
2375 print "Upgrade to $DBversion done (added numReturnedItemsToShow system preference)\n";
2376 SetVersion ($DBversion);
2379 $DBversion = '3.01.00.027';
2380 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2381 $dbh->do("ALTER TABLE zebraqueue CHANGE `biblio_auth_number` `biblio_auth_number` bigint(20) unsigned NOT NULL default 0");
2382 print "Upgrade to $DBversion done (Increased size of zebraqueue biblio_auth_number to address bug 3148.)\n";
2383 SetVersion ($DBversion);
2386 $DBversion = '3.01.00.028';
2387 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2388 my $enable_reviews = C4::Context->preference('AmazonEnabled') ? '1' : '0';
2389 $dbh->do("INSERT INTO systempreferences (variable,value,explanation,options,type) VALUES ('AmazonReviews', '$enable_reviews', 'Display Amazon reviews on staff interface','','YesNo')");
2390 print "Upgrade to $DBversion done (added AmazonReviews)\n";
2391 SetVersion ($DBversion);
2394 $DBversion = '3.01.00.029';
2395 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2396 $dbh->do(q( UPDATE language_rfc4646_to_iso639
2397 SET iso639_2_code = 'spa'
2398 WHERE rfc4646_subtag = 'es'
2399 AND iso639_2_code = 'rus' )
2401 print "Upgrade to $DBversion done (fixed bug 2599: using Spanish search limit retrieves Russian results)\n";
2402 SetVersion ($DBversion);
2405 $DBversion = "3.01.00.030";
2406 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2407 $dbh->do("INSERT INTO `systempreferences` ( `variable` , `value` , `options` , `explanation` , `type` ) VALUES ( 'AllowNotForLoanOverride', '0', '', 'If ON, Koha will allow the librarian to loan a not for loan item.', 'YesNo')");
2408 print "Upgrade to $DBversion done (added AllowNotForLoanOverride system preference)\n";
2409 SetVersion ($DBversion);
2412 $DBversion = "3.01.00.031";
2413 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2414 $dbh->do("ALTER TABLE branch_transfer_limits
2415 MODIFY toBranch varchar(10) NOT NULL,
2416 MODIFY fromBranch varchar(10) NOT NULL,
2417 MODIFY itemtype varchar(10) NULL");
2418 print "Upgrade to $DBversion done (fix column widths in branch_transfer_limits)\n";
2419 SetVersion ($DBversion);
2422 $DBversion = "3.01.00.032";
2423 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2424 $dbh->do(<<ENDOFRENEWAL);
2425 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');
2427 print "Upgrade to $DBversion done (Change the field)\n";
2428 SetVersion ($DBversion);
2431 $DBversion = "3.01.00.033";
2432 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2434 ALTER TABLE borrower_message_preferences
2435 MODIFY borrowernumber int(11) default NULL,
2436 ADD categorycode varchar(10) default NULL AFTER borrowernumber,
2437 ADD KEY `categorycode` (`categorycode`),
2438 ADD CONSTRAINT `borrower_message_preferences_ibfk_3`
2439 FOREIGN KEY (`categorycode`) REFERENCES `categories` (`categorycode`)
2440 ON DELETE CASCADE ON UPDATE CASCADE
2442 print "Upgrade to $DBversion done (DB changes to allow patron category defaults for messaging preferences)\n";
2443 SetVersion ($DBversion);
2446 $DBversion = "3.01.00.034";
2447 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2448 $dbh->do("ALTER TABLE `subscription` ADD COLUMN `graceperiod` INT(11) NOT NULL default '0';");
2449 print "Upgrade to $DBversion done (Adding graceperiod column to subscription table)\n";
2450 SetVersion ($DBversion);
2453 $DBversion = '3.01.00.035';
2454 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2455 $dbh->do(q{ ALTER TABLE `subscription` ADD location varchar(80) NULL DEFAULT '' AFTER callnumber; });
2456 print "Upgrade to $DBversion done (Adding location to subscription table)\n";
2457 SetVersion ($DBversion);
2460 $DBversion = '3.01.00.036';
2461 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2462 $dbh->do("UPDATE systempreferences SET explanation = 'Choose the default detail view in the staff interface; choose between normal, labeled_marc, marc or isbd'
2463 WHERE variable = 'IntranetBiblioDefaultView'
2464 AND explanation = 'IntranetBiblioDefaultView'");
2465 $dbh->do("UPDATE systempreferences SET type = 'Choice', options = 'normal|marc|isbd|labeled_marc'
2466 WHERE variable = 'IntranetBiblioDefaultView'");
2467 $dbh->do("INSERT INTO systempreferences (variable,value,explanation,options,type)VALUES('viewISBD','1','Allow display of ISBD view of bibiographic records','','YesNo')");
2468 $dbh->do("INSERT INTO systempreferences (variable,value,explanation,options,type)VALUES('viewLabeledMARC','0','Allow display of labeled MARC view of bibiographic records','','YesNo')");
2469 $dbh->do("INSERT INTO systempreferences (variable,value,explanation,options,type)VALUES('viewMARC','1','Allow display of MARC view of bibiographic records','','YesNo')");
2470 print "Upgrade to $DBversion done (new viewISBD, viewLabeledMARC, viewMARC sysprefs and tweak IntranetBiblioDefaultView)\n";
2471 SetVersion ($DBversion);
2474 $DBversion = '3.01.00.037';
2475 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2476 $dbh->do('ALTER TABLE authorised_values ADD KEY `lib` (`lib`)');
2477 $dbh->do("INSERT INTO systempreferences (variable,value,explanation,options,type)VALUES('FilterBeforeOverdueReport','0','Do not run overdue report until filter selected','','YesNo')");
2478 SetVersion ($DBversion);
2479 print "Upgrade to $DBversion done (added FilterBeforeOverdueReport syspref and new index on authorised_values)\n";
2482 $DBversion = "3.01.00.038";
2483 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2484 # update branches table
2486 $dbh->do("ALTER TABLE branches ADD `branchzip` varchar(25) default NULL AFTER `branchaddress3`");
2487 $dbh->do("ALTER TABLE branches ADD `branchcity` mediumtext AFTER `branchzip`");
2488 $dbh->do("ALTER TABLE branches ADD `branchcountry` text AFTER `branchcity`");
2489 $dbh->do("ALTER TABLE branches ADD `branchurl` mediumtext AFTER `branchemail`");
2490 $dbh->do("ALTER TABLE branches ADD `branchnotes` mediumtext AFTER `branchprinter`");
2491 print "Upgrade to $DBversion done (add ZIP, city, country, URL, and notes column to branches)\n";
2492 SetVersion ($DBversion);
2495 $DBversion = '3.01.00.039';
2496 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2497 $dbh->do("INSERT INTO systempreferences (variable,value,options,explanation,type)VALUES('SpineLabelFormat', '<itemcallnumber><copynumber>', '30|10', 'This preference defines the format for the quick spine label printer. Just list the fields you would like to see in the order you would like to see them, surrounded by <>, for example <itemcallnumber>.', 'Textarea')");
2498 $dbh->do("INSERT INTO systempreferences (variable,value,options,explanation,type)VALUES('SpineLabelAutoPrint', '0', '', 'If this setting is turned on, a print dialog will automatically pop up for the quick spine label printer.', 'YesNo')");
2499 SetVersion ($DBversion);
2500 print "Upgrade to $DBversion done (added SpineLabelFormat and SpineLabelAutoPrint sysprefs)\n";
2503 $DBversion = '3.01.00.040';
2504 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2505 $dbh->do("INSERT INTO systempreferences (variable,value,explanation,options,type)VALUES('AllowHoldDateInFuture','0','If set a date field is displayed on the Hold screen of the Staff Interface, allowing the hold date to be set in the future.','','YesNo')");
2506 $dbh->do("INSERT INTO systempreferences (variable,value,explanation,options,type)VALUES('OPACAllowHoldDateInFuture','0','If set, along with the AllowHoldDateInFuture system preference, OPAC users can set the date of a hold to be in the future.','','YesNo')");
2507 SetVersion ($DBversion);
2508 print "Upgrade to $DBversion done (AllowHoldDateInFuture and OPACAllowHoldDateInFuture sysprefs)\n";
2511 $DBversion = '3.01.00.041';
2512 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2513 $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')");
2514 SetVersion ($DBversion);
2515 print "Upgrade to $DBversion done (added AWSPrivateKey syspref - note that if you use enhanced content from Amazon, this should be set right away.)\n";
2518 $DBversion = '3.01.00.042';
2519 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2520 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('OPACFineNoRenewals','99999','Fine Limit above which user canmot renew books via OPAC','','Integer')");
2521 SetVersion ($DBversion);
2522 print "Upgrade to $DBversion done (added OPACFineNoRenewals syspref)\n";
2525 $DBversion = '3.01.00.043';
2526 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2527 $dbh->do('ALTER TABLE items ADD COLUMN permanent_location VARCHAR(80) DEFAULT NULL AFTER location');
2528 $dbh->do('UPDATE items SET permanent_location = location');
2529 $dbh->do("INSERT INTO `systempreferences` ( `variable` , `value` , `options` , `explanation` , `type` ) VALUES ( 'NewItemsDefaultLocation', '', '', 'If set, all new items will have a location of the given Location Code ( Authorized Value type LOC )', '')");
2530 $dbh->do("INSERT INTO `systempreferences` ( `variable` , `value` , `options` , `explanation` , `type` ) VALUES ( 'InProcessingToShelvingCart', '0', '', 'If set, when any item with a location code of PROC is ''checked in'', it''s location code will be changed to CART.', 'YesNo')");
2531 $dbh->do("INSERT INTO `systempreferences` ( `variable` , `value` , `options` , `explanation` , `type` ) VALUES ( 'ReturnToShelvingCart', '0', '', 'If set, when any item is ''checked in'', it''s location code will be changed to CART.', 'YesNo')");
2532 SetVersion ($DBversion);
2533 print "Upgrade to $DBversion done (amended Item added NewItemsDefaultLocation, InProcessingToShelvingCart, ReturnToShelvingCart sysprefs)\n";
2536 $DBversion = '3.01.00.044';
2537 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2538 $dbh->do("INSERT INTO systempreferences (variable,value,explanation,options,type)VALUES( 'DisplayClearScreenButton', '0', 'If set to yes, a clear screen button will appear on the circulation page.', 'If set to yes, a clear screen button will appear on the circulation page.', 'YesNo')");
2539 SetVersion ($DBversion);
2540 print "Upgrade to $DBversion done (added DisplayClearScreenButton system preference)\n";
2543 $DBversion = '3.01.00.045';
2544 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2545 $dbh->do("INSERT INTO systempreferences (variable,value,options,explanation,type)VALUES('HidePatronName', '0', '', 'If this is switched on, patron''s cardnumber will be shown instead of their name on the holds and catalog screens', 'YesNo')");
2546 SetVersion ($DBversion);
2547 print "Upgrade to $DBversion done (added a preference to hide the patrons name in the staff catalog)";
2550 $DBversion = "3.01.00.046";
2551 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2552 # update borrowers table
2554 $dbh->do("ALTER TABLE borrowers ADD `country` text AFTER zipcode");
2555 $dbh->do("ALTER TABLE borrowers ADD `B_country` text AFTER B_zipcode");
2556 $dbh->do("ALTER TABLE deletedborrowers ADD `country` text AFTER zipcode");
2557 $dbh->do("ALTER TABLE deletedborrowers ADD `B_country` text AFTER B_zipcode");
2558 print "Upgrade to $DBversion done (add country and B_country to borrowers)\n";
2559 SetVersion ($DBversion);
2562 $DBversion = '3.01.00.047';
2563 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2564 $dbh->do("ALTER TABLE items MODIFY itemcallnumber varchar(255);");
2565 $dbh->do("ALTER TABLE deleteditems MODIFY itemcallnumber varchar(255);");
2566 $dbh->do("ALTER TABLE tmp_holdsqueue MODIFY itemcallnumber varchar(255);");
2567 SetVersion ($DBversion);
2568 print " Upgrade to $DBversion done (bug 2761: change max length of itemcallnumber to 255 from 30)\n";
2571 $DBversion = '3.01.00.048';
2572 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2573 $dbh->do("UPDATE userflags SET flagdesc='View Catalog (Librarian Interface)' WHERE bit=2;");
2574 $dbh->do("UPDATE userflags SET flagdesc='Edit Catalog (Modify bibliographic/holdings data)' WHERE bit=9;");
2575 $dbh->do("UPDATE userflags SET flagdesc='Allow to edit authorities' WHERE bit=14;");
2576 $dbh->do("UPDATE userflags SET flagdesc='Allow to access to the reports module' WHERE bit=16;");
2577 $dbh->do("UPDATE userflags SET flagdesc='Allow to manage serials subscriptions' WHERE bit=15;");
2578 SetVersion ($DBversion);
2579 print " Upgrade to $DBversion done (bug 2611: fix spelling/capitalization in permission flag descriptions)\n";
2582 $DBversion = '3.01.00.049';
2583 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2584 $dbh->do("UPDATE permissions SET description = 'Perform inventory (stocktaking) of your catalog' WHERE code = 'inventory';");
2585 SetVersion ($DBversion);
2586 print "Upgrade to $DBversion done (bug 2611: changed catalogue to catalog per the standard)\n";
2589 $DBversion = '3.01.00.050';
2590 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2591 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES ('OPACSearchForTitleIn','<li class=\"yuimenuitem\">\n<a target=\"_blank\" class=\"yuimenuitemlabel\" href=\"http://worldcat.org/search?q=TITLE\">Other Libraries (WorldCat)</a></li>\n<li class=\"yuimenuitem\">\n<a class=\"yuimenuitemlabel\" href=\"http://www.scholar.google.com/scholar?q=TITLE\" target=\"_blank\">Other Databases (Google Scholar)</a></li>\n<li class=\"yuimenuitem\">\n<a class=\"yuimenuitemlabel\" href=\"http://www.bookfinder.com/search/?author=AUTHOR&title=TITLE&st=xl&ac=qr\" target=\"_blank\">Online Stores (Bookfinder.com)</a></li>','Enter the HTML that will appear in the ''Search for this title in'' box on the detail page in the OPAC. Enter TITLE, AUTHOR, or ISBN in place of their respective variables in the URL. Leave blank to disable ''More Searches'' menu.','70|10','Textarea');");
2592 SetVersion ($DBversion);
2593 print "Upgrade to $DBversion done (bug 1934: Add OPACSearchForTitleIn syspref)\n";
2596 $DBversion = '3.01.00.051';
2597 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2598 $dbh->do("UPDATE systempreferences SET explanation='Fine limit above which user cannot renew books via OPAC' WHERE variable='OPACFineNoRenewals';");
2599 $dbh->do("UPDATE systempreferences SET explanation='If set to ON, a clear screen button will appear on the circulation page.' WHERE variable='DisplayClearScreenButton';");
2600 SetVersion ($DBversion);
2601 print "Upgrade to $DBversion done (fixed typos in new sysprefs)\n";
2604 $DBversion = '3.01.00.052';
2605 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2606 $dbh->do('ALTER TABLE deleteditems ADD COLUMN permanent_location VARCHAR(80) DEFAULT NULL AFTER location');
2607 SetVersion ($DBversion);
2608 print "Upgrade to $DBversion done (bug 3481: add permanent_location column to deleteditems)\n";
2611 $DBversion = '3.01.00.053';
2612 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2613 my $upgrade_script = C4::Context->config("intranetdir") . "/installer/data/mysql/labels_upgrade.pl";
2614 system("perl $upgrade_script");
2615 print "Upgrade to $DBversion done (Migrated labels tables and data to new schema.) NOTE: All existing label batches have been assigned to the first branch in the list of branches. This is ONLY true of migrated label batches.\n";
2616 SetVersion ($DBversion);
2619 $DBversion = '3.01.00.054';
2620 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2621 $dbh->do("ALTER TABLE borrowers ADD `B_address2` text AFTER B_address");
2622 $dbh->do("ALTER TABLE borrowers ADD `altcontactcountry` text AFTER altcontactzipcode");
2623 $dbh->do("ALTER TABLE deletedborrowers ADD `B_address2` text AFTER B_address");
2624 $dbh->do("ALTER TABLE deletedborrowers ADD `altcontactcountry` text AFTER altcontactzipcode");
2625 SetVersion ($DBversion);
2626 print "Upgrade to $DBversion done (bug 1600, bug 3454: add altcontactcountry and B_address2 to borrowers and deletedborrowers)\n";
2629 $DBversion = '3.01.00.055';
2630 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2631 $dbh->do(qq|UPDATE systempreferences set explanation='Enter the HTML that will appear in the ''Search for this title in'' box on the detail page in the OPAC. Enter {TITLE}, {AUTHOR}, or {ISBN} in place of their respective variables in the URL. Leave blank to disable ''More Searches'' menu.', value='<li><a href="http://worldcat.org/search?q={TITLE}" target="_blank">Other Libraries (WorldCat)</a></li>\n<li><a href="http://www.scholar.google.com/scholar?q={TITLE}" target="_blank">Other Databases (Google Scholar)</a></li>\n<li><a href="http://www.bookfinder.com/search/?author={AUTHOR}&title={TITLE}&st=xl&ac=qr" target="_blank">Online Stores (Bookfinder.com)</a></li>' WHERE variable='OPACSearchForTitleIn'|);
2632 SetVersion ($DBversion);
2633 print "Upgrade to $DBversion done (changed OPACSearchForTitleIn per requests in bug 1934)\n";
2636 $DBversion = '3.01.00.056';
2637 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2638 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES ('OPACPatronDetails','1','If OFF the patron details tab in the OPAC is disabled.','','YesNo');");
2639 SetVersion ($DBversion);
2640 print "Upgrade to $DBversion done (Bug 1172 : Add OPACPatronDetails syspref)\n";
2643 $DBversion = '3.01.00.057';
2644 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2645 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES ('OPACFinesTab','1','If OFF the patron fines tab in the OPAC is disabled.','','YesNo');");
2646 SetVersion ($DBversion);
2647 print "Upgrade to $DBversion done (Bug 2576 : Add OPACFinesTab syspref)\n";
2650 $DBversion = '3.01.00.058';
2651 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2652 $dbh->do("ALTER TABLE `language_subtag_registry` ADD `id` INT( 11 ) NOT NULL AUTO_INCREMENT PRIMARY KEY;");
2653 $dbh->do("ALTER TABLE `language_rfc4646_to_iso639` ADD `id` INT( 11 ) NOT NULL AUTO_INCREMENT PRIMARY KEY;");
2654 $dbh->do("ALTER TABLE `language_descriptions` ADD `id` INT( 11 ) NOT NULL AUTO_INCREMENT PRIMARY KEY;");
2655 SetVersion ($DBversion);
2656 print "Upgrade to $DBversion done (Added primary keys to language tables)\n";
2659 $DBversion = '3.01.00.059';
2660 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2661 $dbh->do("INSERT INTO systempreferences (variable,value,options,explanation,type)VALUES('DisplayOPACiconsXSLT', '1', '', 'If ON, displays the format, audience, type icons in XSLT MARC21 results and display pages.', 'YesNo')");
2662 SetVersion ($DBversion);
2663 print "Upgrade to $DBversion done (added DisplayOPACiconsXSLT sysprefs)\n";
2666 $DBversion = '3.01.00.060';
2667 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2668 $dbh->do("INSERT INTO systempreferences (variable,value,explanation,options,type) VALUES('AllowAllMessageDeletion','0','Allow any Library to delete any message','','YesNo');");
2669 $dbh->do('DROP TABLE IF EXISTS messages');
2670 $dbh->do("CREATE TABLE messages ( `message_id` int(11) NOT NULL auto_increment,
2671 `borrowernumber` int(11) NOT NULL,
2672 `branchcode` varchar(4) default NULL,
2673 `message_type` varchar(1) NOT NULL,
2674 `message` text NOT NULL,
2675 `message_date` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
2676 PRIMARY KEY (`message_id`)
2677 ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
2679 print "Upgrade to $DBversion done ( Added AllowAllMessageDeletion syspref and messages table )\n";
2680 SetVersion ($DBversion);
2683 $DBversion = '3.01.00.061';
2684 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2685 $dbh->do("INSERT INTO systempreferences (variable,value,explanation,options,type)VALUES('ShowPatronImageInWebBasedSelfCheck', '0', 'If ON, displays patron image when a patron uses web-based self-checkout', '', 'YesNo')");
2686 print "Upgrade to $DBversion done ( Added ShowPatronImageInWebBasedSelfCheck system preference )\n";
2687 SetVersion ($DBversion);
2690 $DBversion = "3.01.00.062";
2691 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2692 $dbh->do("INSERT INTO permissions (module_bit, code, description) VALUES ( 13, 'manage_csv_profiles', 'Manage CSV export profiles')");
2694 CREATE TABLE `export_format` (
2695 `export_format_id` int(11) NOT NULL auto_increment,
2696 `profile` varchar(255) NOT NULL,
2697 `description` mediumtext NOT NULL,
2698 `marcfields` mediumtext NOT NULL,
2699 PRIMARY KEY (`export_format_id`)
2700 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Used for CSV export';
2702 print "Upgrade to $DBversion done (added csv export profiles)\n";
2705 $DBversion = "3.01.00.063";
2706 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2708 CREATE TABLE `fieldmapping` (
2709 `id` int(11) NOT NULL auto_increment,
2710 `field` varchar(255) NOT NULL,
2711 `frameworkcode` char(4) NOT NULL default '',
2712 `fieldcode` char(3) NOT NULL,
2713 `subfieldcode` char(1) NOT NULL,
2715 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2717 SetVersion ($DBversion);
2720 $DBversion = '3.01.00.065';
2721 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2722 $dbh->do('ALTER TABLE issuingrules ADD COLUMN `renewalsallowed` smallint(6) NOT NULL default "0" AFTER `issuelength`;');
2723 $sth = $dbh->prepare("SELECT itemtype, renewalsallowed FROM itemtypes");
2726 my $sthupd = $dbh->prepare("UPDATE issuingrules SET renewalsallowed = ? WHERE itemtype = ?");
2728 while(my $row = $sth->fetchrow_hashref){
2729 $sthupd->execute($row->{renewalsallowed}, $row->{itemtype});
2732 $dbh->do('ALTER TABLE itemtypes DROP COLUMN `renewalsallowed`;');
2734 SetVersion ($DBversion);
2735 print "Upgrade to $DBversion done (Moving allowed renewals from itemtypes to issuingrule)\n";
2738 $DBversion = '3.01.00.066';
2739 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2740 $dbh->do('ALTER TABLE issuingrules ADD COLUMN `reservesallowed` smallint(6) NOT NULL default "0" AFTER `renewalsallowed`;');
2742 my $maxreserves = C4::Context->preference('maxreserves');
2743 $sth = $dbh->prepare('UPDATE issuingrules SET reservesallowed = ?;');
2744 $sth->execute($maxreserves);
2746 $dbh->do('DELETE FROM systempreferences WHERE variable = "maxreserves";');
2748 $dbh->do("INSERT INTO systempreferences (variable,value, options, explanation, type) VALUES('ReservesControlBranch','PatronLibrary','ItemHomeLibrary|PatronLibrary','Branch checked for members reservations rights','Choice')");
2750 SetVersion ($DBversion);
2751 print "Upgrade to $DBversion done (Moving max allowed reserves from system preference to issuingrule)\n";
2754 $DBversion = "3.01.00.067";
2755 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2756 $dbh->do("INSERT INTO permissions (module_bit, code, description) VALUES ( 13, 'batchmod', 'Perform batch modification of items')");
2757 $dbh->do("INSERT INTO permissions (module_bit, code, description) VALUES ( 13, 'batchdel', 'Perform batch deletion of items')");
2758 print "Upgrade to $DBversion done (added permissions for batch modification and deletion)\n";
2759 SetVersion ($DBversion);
2762 $DBversion = "3.01.00.068";
2763 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2764 $dbh->do("ALTER TABLE issuingrules ADD COLUMN `finedays` int(11) default NULL AFTER `fine` ");
2765 print "Upgrade done (Adding finedays in issuingrules table)\n";
2766 SetVersion ($DBversion);
2770 $DBversion = "3.01.00.069";
2771 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2772 $dbh->do("INSERT INTO `systempreferences` (`variable`, `value`, `options`, `explanation`, `type`) VALUES ('EnableOpacSearchHistory', '1', '', 'Enable or disable opac search history', 'YesNo')");
2774 my $create = <<SEARCHHIST;
2775 CREATE TABLE IF NOT EXISTS `search_history` (
2776 `userid` int(11) NOT NULL,
2777 `sessionid` varchar(32) NOT NULL,
2778 `query_desc` varchar(255) NOT NULL,
2779 `query_cgi` varchar(255) NOT NULL,
2780 `total` int(11) NOT NULL,
2781 `time` timestamp NOT NULL default CURRENT_TIMESTAMP,
2782 KEY `userid` (`userid`),
2783 KEY `sessionid` (`sessionid`)
2784 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Opac search history results';
2788 print "Upgrade done (added OPAC search history preference and table)\n";
2791 $DBversion = "3.01.00.070";
2792 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2793 $dbh->do("ALTER TABLE authorised_values ADD COLUMN `lib_opac` VARCHAR(80) default NULL AFTER `lib`");
2794 print "Upgrade done (Added a lib_opac field in authorised_values table)\n";
2797 $DBversion = "3.01.00.071";
2798 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2799 $dbh->do("ALTER TABLE `subscription` ADD `enddate` date default NULL");
2800 $dbh->do("ALTER TABLE subscriptionhistory CHANGE enddate histenddate DATE default NULL");
2801 print "Upgrade to $DBversion done ( Adding enddate to subscription)\n";
2804 # Acquisitions update
2806 $DBversion = "3.01.00.072";
2807 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2808 $dbh->do("INSERT INTO systempreferences (variable,value,explanation,options,type) VALUES('OpacPrivacy', '0', 'if ON, allows patrons to define their privacy rules (reading history)',NULL,'YesNo')");
2809 # create a new syspref for the 'Mr anonymous' patron
2810 $dbh->do("INSERT INTO systempreferences (variable,value,explanation,options,type) VALUES('AnonymousPatron', '0', \"Set the identifier (borrowernumber) of the 'Mister anonymous' patron. Used for Suggestion and reading history privacy\",NULL,'')");
2811 # fill AnonymousPatron with AnonymousSuggestion value (copy)
2812 my $sth=$dbh->prepare("SELECT value FROM systempreferences WHERE variable='AnonSuggestions'");
2814 my ($value) = $sth->fetchrow() || 0;
2815 $dbh->do("UPDATE systempreferences SET value='$value' WHERE variable='AnonymousPatron'");
2816 # set AnonymousSuggestion do YesNo
2817 # 1st, set the value (1/True if it had a borrowernumber)
2818 $dbh->do("UPDATE systempreferences SET value=1 WHERE variable='AnonSuggestions' AND value>0");
2819 # 2nd, change the type to Choice
2820 $dbh->do("UPDATE systempreferences SET type='YesNo' WHERE variable='AnonSuggestions'");
2821 # borrower reading record privacy : 0 : forever, 1 : laws, 2 : don't keep at all
2822 $dbh->do("ALTER TABLE `borrowers` ADD `privacy` INTEGER NOT NULL DEFAULT 1;");
2823 print "Upgrade to $DBversion done (add new syspref and column in borrowers)\n";
2824 SetVersion ($DBversion);
2827 $DBversion = '3.01.00.073';
2828 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2829 $dbh->do('SET FOREIGN_KEY_CHECKS=0 ');
2830 $dbh->do(<<'END_SQL');
2831 CREATE TABLE IF NOT EXISTS `aqcontract` (
2832 `contractnumber` int(11) NOT NULL auto_increment,
2833 `contractstartdate` date default NULL,
2834 `contractenddate` date default NULL,
2835 `contractname` varchar(50) default NULL,
2836 `contractdescription` mediumtext,
2837 `booksellerid` int(11) not NULL,
2838 PRIMARY KEY (`contractnumber`),
2839 CONSTRAINT `booksellerid_fk1` FOREIGN KEY (`booksellerid`)
2840 REFERENCES `aqbooksellers` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
2841 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
2843 $dbh->do('SET FOREIGN_KEY_CHECKS=1 ');
2844 print "Upgrade to $DBversion done (adding aqcontract table)\n";
2845 SetVersion ($DBversion);
2848 $DBversion = '3.01.00.074';
2849 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2850 $dbh->do("ALTER TABLE `aqbasket` ADD COLUMN `basketname` varchar(50) default NULL AFTER `basketno`");
2851 $dbh->do("ALTER TABLE `aqbasket` ADD COLUMN `note` mediumtext AFTER `basketname`");
2852 $dbh->do("ALTER TABLE `aqbasket` ADD COLUMN `booksellernote` mediumtext AFTER `note`");
2853 $dbh->do("ALTER TABLE `aqbasket` ADD COLUMN `contractnumber` int(11) AFTER `booksellernote`");
2854 $dbh->do("ALTER TABLE `aqbasket` ADD FOREIGN KEY (`contractnumber`) REFERENCES `aqcontract` (`contractnumber`)");
2855 print "Upgrade to $DBversion done (edit aqbasket table done)\n";
2856 SetVersion ($DBversion);
2859 $DBversion = '3.01.00.075';
2860 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2861 $dbh->do("ALTER TABLE `aqorders` ADD COLUMN `uncertainprice` tinyint(1)");
2863 print "Upgrade to $DBversion done (adding uncertainprices)\n";
2864 SetVersion ($DBversion);
2867 $DBversion = '3.01.00.076';
2868 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2869 $dbh->do('SET FOREIGN_KEY_CHECKS=0 ');
2870 $dbh->do("CREATE TABLE IF NOT EXISTS `aqbasketgroups` (
2871 `id` int(11) NOT NULL auto_increment,
2872 `name` varchar(50) default NULL,
2873 `closed` tinyint(1) default NULL,
2874 `booksellerid` int(11) NOT NULL,
2876 KEY `booksellerid` (`booksellerid`),
2877 CONSTRAINT `aqbasketgroups_ibfk_1` FOREIGN KEY (`booksellerid`) REFERENCES `aqbooksellers` (`id`) ON UPDATE CASCADE ON DELETE CASCADE
2878 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;");
2879 $dbh->do("ALTER TABLE aqbasket ADD COLUMN `basketgroupid` int(11)");
2880 $dbh->do("ALTER TABLE aqbasket ADD FOREIGN KEY (`basketgroupid`) REFERENCES `aqbasketgroups` (`id`) ON UPDATE CASCADE ON DELETE SET NULL");
2881 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES ('pdfformat','pdfformat::layout2pages','Controls what script is used for printing (basketgroups)','','free')");
2882 $dbh->do('SET FOREIGN_KEY_CHECKS=1 ');
2883 print "Upgrade to $DBversion done (adding basketgroups)\n";
2884 SetVersion ($DBversion);
2886 $DBversion = '3.01.00.077';
2887 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2889 $dbh->do("SET FOREIGN_KEY_CHECKS=0 ");
2890 # create a mapping table holding the info we need to match orders to budgets
2891 $dbh->do('DROP TABLE IF EXISTS fundmapping');
2893 q|CREATE TABLE fundmapping AS
2894 SELECT aqorderbreakdown.ordernumber, branchcode, bookfundid, budgetdate, entrydate
2895 FROM aqorderbreakdown JOIN aqorders ON aqorderbreakdown.ordernumber = aqorders.ordernumber|);
2896 # match the new type of the corresponding field
2897 $dbh->do('ALTER TABLE fundmapping modify column bookfundid varchar(30)');
2898 # System did not ensure budgetdate was valid historically
2899 $dbh->do(q|UPDATE fundmapping SET budgetdate = entrydate WHERE budgetdate = '0000-00-00' OR budgetdate IS NULL|);
2900 # We save the map in fundmapping in case you need later processing
2901 $dbh->do(q|ALTER TABLE fundmapping add column aqbudgetid integer|);
2902 # these can speed processing up
2903 $dbh->do(q|CREATE INDEX fundmaporder ON fundmapping (ordernumber)|);
2904 $dbh->do(q|CREATE INDEX fundmapid ON fundmapping (bookfundid)|);
2906 $dbh->do("DROP TABLE IF EXISTS `aqbudgetperiods` ");
2909 CREATE TABLE `aqbudgetperiods` (
2910 `budget_period_id` int(11) NOT NULL auto_increment,
2911 `budget_period_startdate` date NOT NULL,
2912 `budget_period_enddate` date NOT NULL,
2913 `budget_period_active` tinyint(1) default '0',
2914 `budget_period_description` mediumtext,
2915 `budget_period_locked` tinyint(1) default NULL,
2916 `sort1_authcat` varchar(10) default NULL,
2917 `sort2_authcat` varchar(10) default NULL,
2918 PRIMARY KEY (`budget_period_id`)
2919 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 |);
2921 $dbh->do(<<ADDPERIODS);
2922 INSERT INTO aqbudgetperiods (budget_period_startdate,budget_period_enddate,budget_period_active,budget_period_description,budget_period_locked)
2923 SELECT DISTINCT startdate, enddate, NOW() BETWEEN startdate and enddate, concat(startdate," ",enddate),NOT NOW() BETWEEN startdate AND enddate from aqbudget
2925 # SORRY , NO AQBUDGET/AQBOOKFUND -> AQBUDGETS IMPORT JUST YET,
2926 # BUT A NEW CLEAN AQBUDGETS TABLE CREATE FOR NOW..
2927 # DROP TABLE IF EXISTS `aqbudget`;
2928 #CREATE TABLE `aqbudget` (
2929 # `bookfundid` varchar(10) NOT NULL default ',
2930 # `startdate` date NOT NULL default 0,
2931 # `enddate` date default NULL,
2932 # `budgetamount` decimal(13,2) default NULL,
2933 # `aqbudgetid` tinyint(4) NOT NULL auto_increment,
2934 # `branchcode` varchar(10) default NULL,
2935 DropAllForeignKeys('aqbudget');
2936 #$dbh->do("drop table aqbudget;");
2939 my $maxbudgetid = $dbh->selectcol_arrayref(<<IDsBUDGET);
2940 SELECT MAX(aqbudgetid) from aqbudget
2943 $$maxbudgetid[0] = 0 if !$$maxbudgetid[0];
2945 $dbh->do(<<BUDGETAUTOINCREMENT);
2946 ALTER TABLE aqbudget AUTO_INCREMENT=$$maxbudgetid[0]
2949 $dbh->do(<<BUDGETNAME);
2950 ALTER TABLE aqbudget RENAME `aqbudgets`
2953 $dbh->do(<<BUDGETS);
2954 ALTER TABLE `aqbudgets`
2955 CHANGE COLUMN aqbudgetid `budget_id` int(11) NOT NULL AUTO_INCREMENT,
2956 CHANGE COLUMN branchcode `budget_branchcode` varchar(10) default NULL,
2957 CHANGE COLUMN budgetamount `budget_amount` decimal(28,6) NOT NULL default '0.00',
2958 CHANGE COLUMN bookfundid `budget_code` varchar(30) default NULL,
2959 ADD COLUMN `budget_parent_id` int(11) default NULL,
2960 ADD COLUMN `budget_name` varchar(80) default NULL,
2961 ADD COLUMN `budget_encumb` decimal(28,6) default '0.00',
2962 ADD COLUMN `budget_expend` decimal(28,6) default '0.00',
2963 ADD COLUMN `budget_notes` mediumtext,
2964 ADD COLUMN `budget_description` mediumtext,
2965 ADD COLUMN `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
2966 ADD COLUMN `budget_amount_sublevel` decimal(28,6) AFTER `budget_amount`,
2967 ADD COLUMN `budget_period_id` int(11) default NULL,
2968 ADD COLUMN `sort1_authcat` varchar(80) default NULL,
2969 ADD COLUMN `sort2_authcat` varchar(80) default NULL,
2970 ADD COLUMN `budget_owner_id` int(11) default NULL,
2971 ADD COLUMN `budget_permission` int(1) default '0';
2974 $dbh->do(<<BUDGETCONSTRAINTS);
2975 ALTER TABLE `aqbudgets`
2976 ADD CONSTRAINT `aqbudgets_ifbk_1` FOREIGN KEY (`budget_period_id`) REFERENCES `aqbudgetperiods` (`budget_period_id`) ON DELETE CASCADE ON UPDATE CASCADE
2978 # $dbh->do(<<BUDGETPKDROP);
2979 #ALTER TABLE `aqbudgets`
2982 # $dbh->do(<<BUDGETPKADD);
2983 #ALTER TABLE `aqbudgets`
2984 # ADD PRIMARY KEY budget_id
2988 my $query_period= $dbh->prepare(qq|SELECT budget_period_id from aqbudgetperiods where budget_period_startdate=? and budget_period_enddate=?|);
2989 my $query_bookfund= $dbh->prepare(qq|SELECT * from aqbookfund where bookfundid=?|);
2990 my $selectbudgets=$dbh->prepare(qq|SELECT * from aqbudgets|);
2991 my $updatebudgets=$dbh->prepare(qq|UPDATE aqbudgets SET budget_period_id= ? , budget_name=?, budget_branchcode=? where budget_id=?|);
2992 $selectbudgets->execute;
2993 while (my $databudget=$selectbudgets->fetchrow_hashref){
2994 $query_period->execute ($$databudget{startdate},$$databudget{enddate});
2995 my ($budgetperiodid)=$query_period->fetchrow;
2996 $query_bookfund->execute ($$databudget{budget_code});
2997 my $databf=$query_bookfund->fetchrow_hashref;
2998 my $branchcode=$$databudget{budget_branchcode}||$$databf{branchcode};
2999 $updatebudgets->execute($budgetperiodid,$$databf{bookfundname},$branchcode,$$databudget{budget_id});
3001 $dbh->do(<<BUDGETDROPDATES);
3002 ALTER TABLE `aqbudgets`
3008 $dbh->do("DROP TABLE IF EXISTS `aqbudgets_planning` ");
3009 $dbh->do("CREATE TABLE `aqbudgets_planning` (
3010 `plan_id` int(11) NOT NULL auto_increment,
3011 `budget_id` int(11) NOT NULL,
3012 `budget_period_id` int(11) NOT NULL,
3013 `estimated_amount` decimal(28,6) default NULL,
3014 `authcat` varchar(30) NOT NULL,
3015 `authvalue` varchar(30) NOT NULL,
3016 `display` tinyint(1) DEFAULT 1,
3017 PRIMARY KEY (`plan_id`),
3018 CONSTRAINT `aqbudgets_planning_ifbk_1` FOREIGN KEY (`budget_id`) REFERENCES `aqbudgets` (`budget_id`) ON DELETE CASCADE ON UPDATE CASCADE
3019 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;");
3021 $dbh->do("ALTER TABLE `aqorders`
3022 ADD COLUMN `budget_id` tinyint(4) NOT NULL,
3023 ADD COLUMN `budgetgroup_id` int(11) NOT NULL,
3024 ADD COLUMN `sort1_authcat` varchar(10) default NULL,
3025 ADD COLUMN `sort2_authcat` varchar(10) default NULL" );
3026 # We need to map the orders to the budgets
3027 # For Historic reasons this is more complex than it should be on occasions
3028 my $budg_arr = $dbh->selectall_arrayref(
3029 q|SELECT aqbudgets.budget_id, aqbudgets.budget_code, aqbudgetperiods.budget_period_startdate,
3030 aqbudgetperiods.budget_period_enddate
3031 FROM aqbudgets JOIN aqbudgetperiods ON aqbudgets.budget_period_id = aqbudgetperiods.budget_period_id
3032 ORDER BY budget_code, budget_period_startdate|, { Slice => {} });
3033 # We arbitarily order on start date, this means if you have overlapping periods the order will be
3034 # linked to the latest matching budget YMMV
3035 my $b_sth = $dbh->prepare(
3036 'UPDATE fundmapping set aqbudgetid = ? where bookfundid =? AND budgetdate >= ? AND budgetdate <= ?');
3037 for my $b ( @{$budg_arr}) {
3038 $b_sth->execute($b->{budget_id}, $b->{budget_code}, $b->{budget_period_startdate}, $b->{budget_period_enddate});
3040 # move the budgetids to aqorders
3041 $dbh->do(q|UPDATE aqorders, fundmapping SET aqorders.budget_id = fundmapping.aqbudgetid
3042 WHERE aqorders.ordernumber = fundmapping.ordernumber AND fundmapping.aqbudgetid IS NOT NULL|);
3043 # NB fundmapping is left as an accontants trail also if you have budgetids that werent set
3044 # you can decide what to do with them
3047 q|UPDATE aqorders, aqbudgets SET aqorders.budgetgroup_id = aqbudgets.budget_period_id
3048 WHERE aqorders.budget_id = aqbudgets.budget_id|);
3049 # cannot do until aqorderbreakdown removed
3050 # $dbh->do("DROP TABLE aqbookfund ");
3051 # $dbh->do("ALTER TABLE aqorders ADD FOREIGN KEY (`budget_id`) REFERENCES `aqbudgets` (`budget_id`) ON UPDATE CASCADE " ); ????
3052 $dbh->do("SET FOREIGN_KEY_CHECKS=1 ");
3054 print "Upgrade to $DBversion done (Adding new aqbudgetperiods, aqbudgets and aqbudget_planning tables )\n";
3055 SetVersion ($DBversion);
3060 $DBversion = '3.01.00.078';
3061 if ( C4::Context->preference("Version") < TransformToNum($DBversion) ) {
3062 $dbh->do("ALTER TABLE aqbudgetperiods ADD COLUMN budget_period_total decimal(28,6)");
3063 print "Upgrade to $DBversion done (adds 'budget_period_total' column to aqbudgetperiods table)\n";
3064 SetVersion($DBversion);
3068 $DBversion = '3.01.00.079';
3069 if ( C4::Context->preference("Version") < TransformToNum($DBversion) ) {
3070 $dbh->do("ALTER TABLE currency ADD COLUMN active tinyint(1)");
3072 print "Upgrade to $DBversion done (adds 'active' column to currencies table)\n";
3073 SetVersion($DBversion);
3076 $DBversion = '3.01.00.080';
3077 if ( C4::Context->preference("Version") < TransformToNum($DBversion) ) {
3078 $dbh->do(<<BUDG_PERM );
3079 INSERT INTO permissions (module_bit, code, description) VALUES
3080 (11, 'vendors_manage', 'Manage vendors'),
3081 (11, 'contracts_manage', 'Manage contracts'),
3082 (11, 'period_manage', 'Manage periods'),
3083 (11, 'budget_manage', 'Manage budgets'),
3084 (11, 'budget_modify', "Modify budget (can't create lines but can modify existing ones)"),
3085 (11, 'planning_manage', 'Manage budget plannings'),
3086 (11, 'order_manage', 'Manage orders & basket'),
3087 (11, 'group_manage', 'Manage orders & basketgroups'),
3088 (11, 'order_receive', 'Manage orders & basket'),
3089 (11, 'budget_add_del', "Add and delete budgets (but can't modify budgets)");
3092 print "Upgrade to $DBversion done (adds permissions for the acquisitions module)\n";
3093 SetVersion($DBversion);
3097 $DBversion = '3.01.00.081';
3098 if ( C4::Context->preference("Version") < TransformToNum($DBversion) ) {
3099 $dbh->do("ALTER TABLE aqbooksellers ADD COLUMN `gstrate` decimal(6,4) default NULL");
3100 if (my $gist=C4::Context->preference("gist")){
3101 my $sql=$dbh->prepare("UPDATE aqbooksellers set `gstrate`=? ");
3102 $sql->execute($gist) ;
3104 print "Upgrade to $DBversion done (added per-supplier gstrate setting)\n";
3105 SetVersion($DBversion);
3108 $DBversion = "3.01.00.082";
3109 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
3110 if (C4::Context->preference("opaclanguages") eq "fr") {
3111 $dbh->do(qq#INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('AcqCreateItem','ordering',"Définit quand l'exemplaire est créé : à la commande, à la livraison, au catalogage",'ordering|receiving|cataloguing','Choice')#);
3113 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('AcqCreateItem','ordering','Define when the item is created : when ordering, when receiving, or in cataloguing module','ordering|receiving|cataloguing','Choice')");
3115 print "Upgrade to $DBversion done (adding ReservesNeedReturns systempref, in circulation)\n";
3116 SetVersion ($DBversion);
3119 $DBversion = "3.01.00.083";
3120 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
3122 CREATE TABLE `aqorders_items` (
3123 `ordernumber` int(11) NOT NULL,
3124 `itemnumber` int(11) NOT NULL,
3125 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
3126 PRIMARY KEY (`itemnumber`),
3127 KEY `ordernumber` (`ordernumber`)
3128 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
3131 $dbh->do(qq| DROP TABLE aqorderbreakdown |);
3132 $dbh->do('DROP TABLE aqbookfund');
3133 print "Upgrade to $DBversion done (New aqorders_items table for acqui)\n";
3134 SetVersion ($DBversion);
3137 $DBversion = "3.01.00.084";
3138 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
3139 $dbh->do( qq# INSERT INTO `systempreferences` VALUES ('CurrencyFormat','US','US|FR','Determines the display format of currencies. eg: ''36000'' is displayed as ''360 000,00'' in ''FR'' or 360,000.00'' in ''US''.','Choice') #);
3141 print "Upgrade to $DBversion done (CurrencyFormat syspref added)\n";
3142 SetVersion ($DBversion);
3145 $DBversion = "3.01.00.085";
3146 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
3147 $dbh->do("ALTER table aqorders drop column title");
3148 $dbh->do("ALTER TABLE `aqorders` CHANGE `budget_id` `budget_id` INT( 11 ) NOT NULL");
3149 print "Upgrade to $DBversion done update budget_id size that should not be a tinyint\n";
3150 SetVersion ($DBversion);
3153 $DBversion = "3.01.00.086";
3154 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
3155 $dbh->do(<<SUGGESTIONS);
3156 ALTER table suggestions
3157 ADD budgetid INT(11),
3158 ADD branchcode VARCHAR(10) default NULL,
3159 ADD acceptedby INT(11) default NULL,
3160 ADD accepteddate date default NULL,
3161 ADD suggesteddate date default NULL,
3162 ADD manageddate date default NULL,
3163 ADD rejectedby INT(11) default NULL,
3164 ADD rejecteddate date default NULL,
3165 ADD collectiontitle text default NULL,
3166 ADD itemtype VARCHAR(30) default NULL
3169 print "Upgrade to $DBversion done Suggestions";
3170 SetVersion ($DBversion);
3173 $DBversion = "3.01.00.087";
3174 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
3175 $dbh->do("ALTER table aqbudgets drop column budget_amount_sublevel;");
3176 print "Upgrade to $DBversion done drop column budget_amount_sublevel from aqbudgets\n";
3177 SetVersion ($DBversion);
3180 $DBversion = "3.01.00.088";
3181 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
3182 $dbh->do( qq# INSERT INTO `systempreferences` VALUES ('intranetbookbag','1','','If ON, enables display of Cart feature in the intranet','YesNo') #);
3184 print "Upgrade to $DBversion done (intranetbookbag syspref added)\n";
3185 SetVersion ($DBversion);
3188 $DBversion = "3.01.00.090";
3189 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
3191 INSERT INTO `permissions` (`module_bit`, `code`, `description`) VALUES
3192 (16, 'execute_reports', 'Execute SQL reports'),
3193 (16, 'create_reports', 'Create SQL Reports')
3196 print "Upgrade to $DBversion done (granular permissions for guided reports added)\n";
3197 SetVersion ($DBversion);
3200 $DBversion = "3.01.00.091";
3201 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
3203 UPDATE `systempreferences` SET `options` = 'holdings|serialcollection|subscriptions'
3204 WHERE `systempreferences`.`variable` = 'opacSerialDefaultTab' LIMIT 1
3207 print "Upgrade to $DBversion done (opac-detail default tag updated)\n";
3208 SetVersion ($DBversion);
3211 $DBversion = "3.01.00.092";
3212 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
3213 if (C4::Context->preference("opaclanguages") =~ /fr/) {
3215 INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES ('RoutingListAddReserves','1','Si activé, des reservations sont automatiquement créées pour chaque lecteur de la liste de circulation d''un numéro de périodique','','YesNo');
3219 INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES ('RoutingListAddReserves','1','If ON the patrons on routing lists are automatically added to holds on the issue.','','YesNo');
3222 print "Upgrade to $DBversion done (Added RoutingListAddReserves syspref)\n";
3223 SetVersion ($DBversion);
3226 $DBversion = "3.01.00.093";
3227 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
3229 ALTER TABLE biblioitems ADD INDEX issn_idx (issn);
3231 print "Upgrade to $DBversion done (added index to ISSN)\n";
3232 SetVersion ($DBversion);
3235 $DBversion = "3.01.00.094";
3236 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
3238 ALTER TABLE aqbasketgroups ADD deliveryplace VARCHAR(10) default NULL, ADD deliverycomment VARCHAR(255) default NULL;
3241 print "Upgrade to $DBversion done (adding deliveryplace deliverycomment to basketgroups)\n";
3242 SetVersion ($DBversion);
3245 $DBversion = "3.01.00.095";
3246 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
3248 ALTER TABLE items ADD stocknumber VARCHAR(32) DEFAULT NULL COMMENT "stores the inventory number";
3251 ALTER TABLE items ADD UNIQUE INDEX itemsstocknumberidx (stocknumber);
3254 ALTER TABLE deleteditems ADD stocknumber VARCHAR(32) DEFAULT NULL COMMENT "stores the inventory number of deleted items";
3257 ALTER TABLE deleteditems ADD UNIQUE INDEX deleteditemsstocknumberidx (stocknumber);
3259 if (C4::Context->preference('marcflavour') eq 'UNIMARC'){
3261 INSERT IGNORE INTO marc_subfield_structure (frameworkcode,tagfield, tagsubfield, tab, repeatable, mandatory,kohafield)
3262 SELECT DISTINCT (frameworkcode),995,"j",10,0,0,"items.stocknumber" from biblio_framework ;
3264 #Previously, copynumber was used as stocknumber
3266 UPDATE items set stocknumber=copynumber;
3269 UPDATE items set copynumber=NULL;
3272 print "Upgrade to $DBversion done (stocknumber field added)\n";
3273 SetVersion ($DBversion);
3276 $DBversion = "3.01.00.096";
3277 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
3278 $dbh->do("INSERT INTO systempreferences (variable,value,explanation,options,type) VALUES ('OrderPdfTemplate','','Uploads a PDF template to use for printing baskets','NULL','Upload')");
3279 $dbh->do("UPDATE systempreferences SET variable='OrderPdfFormat' WHERE variable='pdfformat'");
3280 print "Upgrade to $DBversion done (PDF orders system preferences added and updated)\n";
3281 SetVersion ($DBversion);
3284 $DBversion = "3.01.00.097";
3285 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
3287 ALTER TABLE aqbasketgroups ADD billingplace VARCHAR(10) NOT NULL AFTER deliverycomment;
3290 print "Upgrade to $DBversion done (Adding billingplace to aqbasketgroups)\n";
3291 SetVersion ($DBversion);
3294 $DBversion = "3.01.00.098";
3295 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
3297 ALTER TABLE auth_subfield_structure MODIFY frameworkcode VARCHAR(10) NULL;
3300 print "Upgrade to $DBversion done (changing frameworkcode length in auth_subfield_structure)\n";
3301 SetVersion ($DBversion);
3304 $DBversion = "3.01.00.099";
3305 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
3307 INSERT INTO `permissions` (`module_bit`, `code`, `description`) VALUES
3308 (9, 'edit_catalogue', 'Edit catalogue'),
3309 (9, 'fast_cataloging', 'Fast cataloging')
3312 print "Upgrade to $DBversion done (granular permissions for cataloging added)\n";
3313 SetVersion ($DBversion);
3316 $DBversion = "3.01.00.100";
3317 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
3318 $dbh->do("INSERT INTO `systempreferences` (`variable`, `value`, `options`, `explanation`, `type`) VALUES ('casAuthentication', '0', '', 'Enable or disable CAS authentication', 'YesNo'), ('casLogout', '1', '', 'Does a logout from Koha should also log out of CAS ?', 'YesNo'), ('casServerUrl', 'https://localhost:8443/cas', '', 'URL of the cas server', 'Free')");
3319 print "Upgrade done (added CAS authentication system preferences)\n";
3320 SetVersion ($DBversion);
3323 $DBversion = "3.01.00.101";
3324 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
3326 "INSERT INTO systempreferences
3327 (variable, value, options, explanation, type)
3329 'OverdueNoticeBcc', '', '',
3330 'Email address to Bcc outgoing notices sent by email',
3333 print "Upgrade to $DBversion done (added OverdueNoticeBcc system preferences)\n";
3334 SetVersion ($DBversion);
3336 $DBversion = "3.01.00.102";
3337 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
3339 "UPDATE permissions set description = 'Edit catalog (Modify bibliographic/holdings data)' where module_bit = 9 and code = 'edit_catalogue'"
3341 print "Upgrade done (fixed spelling error in edit_catalogue permission)\n";
3342 SetVersion ($DBversion);
3345 $DBversion = "3.01.00.103";
3346 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
3347 $dbh->do("INSERT INTO permissions (module_bit, code, description) VALUES (13, 'moderate_tags', 'Moderate patron tags')");
3348 print "Upgrade done (adding patron permissions for tags tool)\n";
3349 SetVersion ($DBversion);
3352 $DBversion = "3.01.00.104";
3353 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
3355 my ($maninv_count, $borrnotes_count);
3356 eval { $maninv_count = $dbh->do("SELECT 1 FROM authorised_values WHERE category='MANUAL_INV'"); };
3357 if ($maninv_count == 0) {
3358 $dbh->do("INSERT INTO authorised_values (category,authorised_value,lib) VALUES ('MANUAL_INV','Copier Fees','.25')");
3360 eval { $borrnotes_count = $dbh->do("SELECT 1 FROM authorised_values WHERE category='BOR_NOTES'"); };
3361 if ($borrnotes_count == 0) {
3362 $dbh->do("INSERT INTO authorised_values (category,authorised_value,lib) VALUES ('BOR_NOTES','ADDR','Address Notes')");
3365 $dbh->do("INSERT INTO authorised_values (category,authorised_value,lib) VALUES ('LOC','CART','Book Cart')");
3366 $dbh->do("INSERT INTO authorised_values (category,authorised_value,lib) VALUES ('LOC','PROC','Processing Center')");
3368 print "Upgrade to $DBversion done ( add defaults to authorized values for MANUAL_INV and BOR_NOTES and add new default LOC authorized values for shelf to cart processing )\n";
3369 SetVersion ($DBversion);
3373 $DBversion = "3.01.00.105";
3374 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
3376 CREATE TABLE `collections` (
3377 `colId` int(11) NOT NULL auto_increment,
3378 `colTitle` varchar(100) NOT NULL default '',
3379 `colDesc` text NOT NULL,
3380 `colBranchcode` varchar(4) default NULL COMMENT 'branchcode for branch where item should be held.',
3381 PRIMARY KEY (`colId`)
3382 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
3386 CREATE TABLE `collections_tracking` (
3387 `ctId` int(11) NOT NULL auto_increment,
3388 `colId` int(11) NOT NULL default '0' COMMENT 'collections.colId',
3389 `itemnumber` int(11) NOT NULL default '0' COMMENT 'items.itemnumber',
3390 PRIMARY KEY (`ctId`)
3391 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
3394 INSERT INTO permissions (module_bit, code, description)
3395 VALUES ( 13, 'rotating_collections', 'Manage Rotating collections')" );
3396 print "Upgrade to $DBversion done (added collection and collection_tracking tables for rotating collections functionality)\n";
3397 SetVersion ($DBversion);
3399 $DBversion = "3.01.00.106";
3400 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
3401 $dbh->do("INSERT INTO `systempreferences` (variable,value,options,explanation,type) VALUES ( 'OpacAddMastheadLibraryPulldown', '0', '', 'Adds a pulldown menu to select the library to search on the opac masthead.', 'YesNo' )");
3402 print "Upgrade done (added OpacAddMastheadLibraryPulldown system preferences)\n";
3403 SetVersion ($DBversion);
3406 $DBversion = '3.01.00.107';
3407 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
3408 my $upgrade_script = C4::Context->config("intranetdir") . "/installer/data/mysql/patroncards_upgrade.pl";
3409 system("perl $upgrade_script");
3410 print "Upgrade to $DBversion done (Migrated labels and patroncards tables and data to new schema.)\n";
3411 SetVersion ($DBversion);
3414 $DBversion = '3.01.00.108';
3415 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
3417 ALTER TABLE `export_format` ADD `csv_separator` VARCHAR( 2 ) NOT NULL AFTER `marcfields` ,
3418 ADD `field_separator` VARCHAR( 2 ) NOT NULL AFTER `csv_separator` ,
3419 ADD `subfield_separator` VARCHAR( 2 ) NOT NULL AFTER `field_separator`
3421 print "Upgrade done (added separators for csv export)\n";
3422 SetVersion ($DBversion);
3425 $DBversion = "3.01.00.109";
3426 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
3428 ALTER TABLE `export_format` ADD `encoding` VARCHAR(255) NOT NULL AFTER `subfield_separator`
3430 print "Upgrade done (added encoding for csv export)\n";
3431 SetVersion ($DBversion);
3434 $DBversion = '3.01.00.110';
3435 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
3436 $dbh->do('ALTER TABLE `categories` ADD COLUMN `enrolmentperioddate` DATE NULL DEFAULT NULL AFTER `enrolmentperiod`');
3437 print "Upgrade done (Add enrolment period date support)\n";
3438 SetVersion ($DBversion);
3441 $DBversion = '3.01.00.111';
3442 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
3443 print "Upgrade done (mark DBrev for 3.2-alpha release)\n";
3444 SetVersion ($DBversion);
3447 $DBversion = '3.01.00.112';
3448 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
3449 $dbh->do("INSERT INTO `systempreferences` (variable,value,options,explanation,type) VALUES ('SpineLabelShowPrintOnBibDetails', '0', '', 'If turned on, a \"Print Label\" link will appear for each item on the bib details page in the staff interface.', 'YesNo');");
3450 print "Upgrade done ( added Show Spine Label Printer on Bib Items Details preferences )\n";
3451 SetVersion ($DBversion);
3454 $DBversion = '3.01.00.113';
3455 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
3456 my $value = C4::Context->preference("XSLTResultsDisplay");
3458 "INSERT INTO systempreferences (variable,value,type)
3459 VALUES('OPACXSLTResultsDisplay',?,'YesNo')", {}, $value ? 1 : 0);
3460 $value = C4::Context->preference("XSLTDetailsDisplay");
3462 "INSERT INTO systempreferences (variable,value,type)
3463 VALUES('OPACXSLTDetailsDisplay',?,'YesNo')", {}, $value ? 1 : 0);
3464 print "Upgrade done (added two new syspref: OPACXSLTResultsDisplay and OPACXSLTDetailDisplay). You may have to go in Admin > System preference to tweak XSLT related syspref both in OPAC and Search tabs.\n ";
3465 SetVersion ($DBversion);
3468 $DBversion = '3.01.00.114';
3469 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
3470 $dbh->do("INSERT INTO systempreferences (variable,value,explanation,options,type)VALUES('AutoSelfCheckAllowed', '0', 'For corporate and special libraries which want web-based self-check available from any PC without the need for a manual staff login. Most libraries will want to leave this turned off. If on, requires self-check ID and password to be entered in AutoSelfCheckID and AutoSelfCheckPass sysprefs.', '', 'YesNo')");
3471 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('AutoSelfCheckID','','Staff ID with circulation rights to be used for automatic web-based self-check. Only applies if AutoSelfCheckAllowed syspref is turned on.','','free')");
3472 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('AutoSelfCheckPass','','Password to be used for automatic web-based self-check. Only applies if AutoSelfCheckAllowed syspref is turned on.','','free')");
3473 print "Upgrade to $DBversion done ( Added AutoSelfCheckAllowed, AutoSelfCheckID, and AutoShelfCheckPass system preference )\n";
3474 SetVersion ($DBversion);
3477 $DBversion = '3.01.00.115';
3478 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
3479 $dbh->do('UPDATE aqorders SET quantityreceived = 0 WHERE quantityreceived IS NULL');
3480 $dbh->do('ALTER TABLE aqorders MODIFY COLUMN quantityreceived smallint(6) NOT NULL DEFAULT 0');
3481 print "Upgrade to $DBversion done ( Default aqorders.quantityreceived to 0 )\n";
3482 SetVersion ($DBversion);
3485 $DBversion = '3.01.00.116';
3486 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
3487 if (C4::Context->preference('OrderPdfFormat') eq 'pdfformat::example'){
3488 $dbh->do("UPDATE `systempreferences` set value='pdfformat::layout2pages' WHERE variable='OrderPdfFormat'");
3490 print "Upgrade done ( corrected default OrderPdfFormat value if still set wrong )\n";
3491 SetVersion ($DBversion);
3494 $DBversion = '3.01.00.117';
3495 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
3496 $dbh->do("UPDATE language_rfc4646_to_iso639 SET iso639_2_code = 'por' WHERE rfc4646_subtag='pt' ");
3497 print "Upgrade to $DBversion done (corrected ISO 639-2 language code for Portuguese)\n";
3498 SetVersion ($DBversion);
3501 $DBversion = '3.01.00.118';
3502 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
3503 my ($count) = $dbh->selectrow_array("SELECT count(*) FROM information_schema.columns
3504 WHERE table_name = 'aqbudgets_planning'
3505 AND column_name = 'display'");
3507 $dbh->do("ALTER TABLE aqbudgets_planning ADD COLUMN display tinyint(1) DEFAULT 1");
3509 print "Upgrade to $DBversion done (bug 4203: add display column to aqbudgets_planning if missing)\n";
3510 SetVersion ($DBversion);
3513 $DBversion = '3.01.00.119';
3514 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
3515 eval{require Locale::Currency::Format};
3517 print "Upgrade to $DBversion done (Locale::Currency::Format installed.)\n";
3518 SetVersion ($DBversion);
3521 print "Upgrade to $DBversion done.\n";
3522 print "NOTICE: The Locale::Currency::Format package is not installed on your system or not found in \@INC.\nThis dependency is required in order to include fine information in overdue notices.\nPlease ask your system administrator to install this package.\n";
3523 SetVersion ($DBversion);
3527 $DBversion = '3.01.00.120';
3528 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
3530 INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('soundon','0','Enable circulation sounds during checkin and checkout in the staff interface. Not supported by all web browsers yet.','','YesNo');
3532 print "Upgrade to $DBversion done (bug 1080: add soundon system preference for circulation sounds)\n";
3533 SetVersion ($DBversion);
3536 $DBversion = '3.01.00.121';
3537 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
3538 $dbh->do("ALTER TABLE `reserves` ADD `expirationdate` DATE DEFAULT NULL");
3539 $dbh->do("ALTER TABLE `reserves` ADD `lowestPriority` tinyint(1) NOT NULL");
3540 $dbh->do("ALTER TABLE `old_reserves` ADD `expirationdate` DATE DEFAULT NULL");
3541 $dbh->do("ALTER TABLE `old_reserves` ADD `lowestPriority` tinyint(1) NOT NULL");
3542 print "Upgrade to $DBversion done ( Added Additional Fields to Reserves tables )\n";
3543 SetVersion ($DBversion);
3546 $DBversion = '3.01.00.122';
3547 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
3549 INSERT INTO systempreferences (variable,value,explanation,options,type)
3550 VALUES ('OAI-PMH:ConfFile', '', 'If empty, Koha OAI Server operates in normal mode, otherwise it operates in extended mode.','','File');
3552 print "Upgrade to $DBversion done. — Add a new system preference OAI-PMF:ConfFile\n";
3553 SetVersion ($DBversion);
3556 $DBversion = "3.01.00.123";
3557 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
3558 $dbh->do("INSERT INTO `permissions` (`module_bit`, `code`, `description`) VALUES
3559 (6, 'place_holds', 'Place holds for patrons')");
3560 $dbh->do("INSERT INTO `permissions` (`module_bit`, `code`, `description`) VALUES
3561 (6, 'modify_holds_priority', 'Modify holds priority')");
3562 $dbh->do("UPDATE `userflags` SET `flagdesc` = 'Place and modify holds for patrons' WHERE `flag` = 'reserveforothers'");
3563 print "Upgrade to $DBversion done (Add granular permission for holds modification and update description of reserveforothers permission)\n";
3564 SetVersion ($DBversion);
3567 $DBversion = '3.01.00.124';
3568 if (C4::Context->preference('Version') < TransformToNum($DBversion)){
3570 INSERT INTO `letter` (module, code, name, title, content) VALUES('reserves', 'HOLDPLACED', 'Hold Placed on Item', 'Hold Placed on Item','A hold has been placed on the following item : <<title>> (<<biblionumber>>) by the user <<firstname>> <<surname>> (<<cardnumber>>).');
3572 print "Upgrade to $DBversion done (bug 3242: add HOLDPLACED letter template, which is used when emailLibrarianWhenHoldIsPlaced is enabled)\n";
3573 SetVersion ($DBversion);
3576 $DBversion = '3.01.00.125';
3577 if (C4::Context->preference('Version') < TransformToNum($DBversion)){
3579 INSERT INTO `systempreferences` ( `variable` , `value` , `options` , `explanation` , `type` ) VALUES ( 'PrintNoticesMaxLines', '0', '', 'If greater than 0, sets the maximum number of lines an overdue notice will print. If the number of items is greater than this number, the notice will end with a warning asking the borrower to check their online account for a full list of overdue items.', 'Integer' );
3582 INSERT INTO message_transport_types (message_transport_type) values ('print');
3584 print "Upgrade to $DBversion done (bug 3482: Printable hold and overdue notices)\n";
3585 SetVersion ($DBversion);
3588 $DBversion = "3.01.00.126";
3589 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
3590 $dbh->do("INSERT INTO systempreferences (variable,value,explanation,options,type) VALUES ('ILS-DI','0','Enable ILS-DI services. See http://your.opac.name/cgi-bin/koha/ilsdi.pl for online documentation.','','YesNo')");
3591 $dbh->do("INSERT INTO systempreferences (variable,value,explanation,options,type) VALUES ('ILS-DI:AuthorizedIPs','127.0.0.1','A comma separated list of IP addresses authorized to access the web services.','','free')");
3593 print "Upgrade to $DBversion done (Adding ILS-DI updates and ILS-DI:AuthorizedIPs)\n";
3594 SetVersion ($DBversion);
3597 $DBversion = '3.01.00.127';
3598 if (C4::Context->preference('Version') < TransformToNum($DBversion)){
3599 $dbh->do("ALTER TABLE messages CHANGE branchcode branchcode varchar(10);");
3600 print "Upgrade to $DBversion done (bug 4190: messages in patron account did not work with branchcodes > 4)\n";
3601 SetVersion ($DBversion);
3604 $DBversion = '3.01.00.128';
3605 if (C4::Context->preference('Version') < TransformToNum($DBversion)){
3606 $dbh->do('CREATE INDEX budget_id ON aqorders (budget_id );');
3607 print "Upgrade to $DBversion done (bug 4331: index orders by budget_id)\n";
3608 SetVersion ($DBversion);
3611 $DBversion = "3.01.00.129";
3612 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
3613 $dbh->do("UPDATE `permissions` SET `code` = 'items_batchdel' WHERE `permissions`.`module_bit` =13 AND `permissions`.`code` = 'batchdel' LIMIT 1 ;");
3614 $dbh->do("UPDATE `permissions` SET `code` = 'items_batchmod' WHERE `permissions`.`module_bit` =13 AND `permissions`.`code` = 'batchmod' LIMIT 1 ;");
3615 print "Upgrade done (Change permissions names for item batch modification / deletion)\n";
3617 SetVersion ($DBversion);
3620 $DBversion = "3.01.00.130";
3621 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
3622 $dbh->do("UPDATE reserves SET expirationdate = NULL WHERE expirationdate = '0000-00-00'");
3623 print "Upgrade done (change reserves.expirationdate values of 0000-00-00 to NULL (bug 1532)";
3624 SetVersion ($DBversion);
3627 $DBversion = "3.01.00.131";
3628 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
3630 INSERT IGNORE INTO message_transport_types (message_transport_type) VALUES ('print'),('feed');
3632 print "Upgrade to $DBversion done (adding print and feed message transport types)\n";
3633 SetVersion ($DBversion);
3636 $DBversion = "3.01.00.132";
3637 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
3639 ALTER TABLE language_descriptions ADD INDEX subtag_type_lang (subtag, type, lang);
3641 print "Upgrade to $DBversion done (Adding index to language_descriptions table)\n";
3642 SetVersion ($DBversion);
3645 $DBversion = '3.01.00.133';
3646 if (C4::Context->preference('Version') < TransformToNum($DBversion)){
3647 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES ('OverduesBlockCirc','noblock','When checking out an item should overdues block checkout, generate a confirmation dialogue, or allow checkout','noblock|confirmation|block','Choice')");
3648 print "Upgrade to $DBversion done (bug 4405: added OverduesBlockCirc syspref to control whether circulation is blocked if a borrower has overdues)\n";
3649 SetVersion ($DBversion);
3652 $DBversion = '3.01.00.134';
3653 if (C4::Context->preference('Version') < TransformToNum($DBversion)){
3654 $dbh->do("INSERT INTO systempreferences (variable,value,explanation,options,type) VALUES ('DisplayMultiPlaceHold','1','Display the ability to place multiple holds or not','','YesNo')");
3655 print "Upgrade to $DBversion done adding syspref DisplayMultiPlaceHold to control whether multiple holds can be placed from the search results page";
3656 SetVersion ($DBversion);
3659 $DBversion = '3.01.00.135';
3660 if (C4::Context->preference('Version') < TransformToNum($DBversion)){
3662 INSERT INTO `letter` (module, code, name, title, content) VALUES
3663 ('reserves', 'HOLD_PRINT', 'Hold Available for Pickup (print notice)', 'Hold Available for Pickup (print notice)', '<<branches.branchname>>\r\n<<branches.branchaddress1>>\r\n<<branches.branchaddress2>>\r\n\r\n\r\nChange Service Requested\r\n\r\n\r\n\r\n\r\n\r\n\r\n\r\n<<borrowers.firstname>> <<borrowers.surname>>\r\n<<borrowers.address>>\r\n<<borrowers.city>> <<borrowers.zipcode>>\r\n\r\n\r\n\r\n\r\n\r\n\r\n\r\n\r\n\r\n\r\n<<borrowers.firstname>> <<borrowers.surname>> <<borrowers.cardnumber>>\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\n')
3665 print "Upgrade to $DBversion done (bug 4377: added HOLD_PRINT message template)";
3666 SetVersion ($DBversion);
3669 $DBversion = '3.01.00.136';
3670 if (C4::Context->preference('Version') < TransformToNum($DBversion)){
3672 INSERT INTO permissions (module_bit, code, description) VALUES
3673 ( 9, 'edit_items', 'Edit Items');});
3674 print "Upgrade to $DBversion done Adding a new permission to edit items";
3675 SetVersion ($DBversion);
3678 $DBversion = "3.01.00.137";
3679 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
3681 INSERT INTO permissions (module_bit, code, description) VALUES
3682 (15, 'check_expiration', 'Check the expiration of a serial'),
3683 (15, 'claim_serials', 'Claim missing serials'),
3684 (15, 'create_subscription', 'Create a new subscription'),
3685 (15, 'delete_subscription', 'Delete an existing subscription'),
3686 (15, 'edit_subscription', 'Edit an existing subscription'),
3687 (15, 'receive_serials', 'Serials receiving'),
3688 (15, 'renew_subscription', 'Renew a subscription'),
3689 (15, 'routing', 'Routing');
3691 print "Upgrade to $DBversion done (adding granular permissions for serials)";
3692 SetVersion ($DBversion);
3695 $DBversion = "3.01.00.138";
3696 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
3697 $dbh->do("DELETE FROM systempreferences WHERE variable = 'GranularPermissions'");
3698 print "Upgrade to $DBversion done (bug 4896: removing GranularPermissions syspref; use of granular permissions is now the default)";
3699 SetVersion ($DBversion);
3702 $DBversion = '3.01.00.139';
3703 if (C4::Context->preference('Version') < TransformToNum($DBversion)){
3704 $dbh->do("ALTER TABLE message_attributes CHANGE message_name message_name varchar(40);");
3705 print "Upgrade to $DBversion done (bug 3682: change message_name from varchar(20) to varchar(40))\n";
3706 SetVersion ($DBversion);
3709 $DBversion = '3.01.00.140';
3710 if (C4::Context->preference('Version') < TransformToNum($DBversion)){
3711 $dbh->do("UPDATE systempreferences SET value = '0' WHERE variable = 'TagsModeration' AND value is NULL");
3712 print "Upgrade to $DBversion done (bug 4312 TagsModeration changed from NULL to 0)\n";
3713 SetVersion ($DBversion);
3716 $DBversion = '3.01.00.141';
3717 if (C4::Context->preference('Version') < TransformToNum($DBversion)){
3718 $dbh->do(qq{DELETE FROM message_attributes WHERE message_attribute_id=3;});
3719 $dbh->do(qq{DELETE FROM letter WHERE code='EVENT' AND title='Upcoming Library Event';});
3720 print "Upgrade to $DBversion done Remove upcoming events messaging option (bug 2434)";
3721 SetVersion ($DBversion);
3724 $DBversion = '3.01.00.142';
3725 if (C4::Context->preference('Version') < TransformToNum($DBversion)){
3726 $dbh->do(qq{DELETE FROM message_transports WHERE message_attribute_id=3;});
3727 print "Upgrade to $DBversion done Remove upcoming events messaging option part 2 (bug 2434)";
3728 SetVersion ($DBversion);
3731 $DBversion = '3.01.00.143';
3732 if (C4::Context->preference('Version') < TransformToNum($DBversion)){
3733 $dbh->do(qq{CREATE INDEX auth_value_idx ON authorised_values (authorised_value)});
3734 $dbh->do(qq{CREATE INDEX auth_val_cat_idx ON borrower_attribute_types (authorised_value_category)});
3735 print "Create index on authorised_values and borrower_attribute_types (bug 4139)";
3736 SetVersion ($DBversion);
3739 $DBversion = '3.01.00.144';
3740 if (C4::Context->preference('Version') < TransformToNum($DBversion)){
3741 $dbh->do(qq{UPDATE systempreferences SET value='normal' where value='default' and variable='IntranetBiblioDefaultView'});
3742 print "Update the 'default' to 'normal' for the IntranetBiblioDefaultView syspref (bug 5007)";
3743 SetVersion ($DBversion);
3746 $DBversion = "3.01.00.145";
3747 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
3748 $dbh->do("ALTER TABLE borrowers ADD KEY `guarantorid` (guarantorid);");
3749 print "Upgrade to $DBversion done (Add index on guarantorid)\n";
3750 SetVersion ($DBversion);
3753 $DBversion = '3.01.00.999';
3754 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
3755 print "Upgrade to $DBversion done (3.2.0 release candidate)\n";
3756 SetVersion ($DBversion);
3759 $DBversion = "3.02.00.000";
3760 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
3761 my $value = $dbh->selectrow_array("SELECT value FROM systempreferences WHERE variable = 'HomeOrHoldingBranch'");
3762 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('HomeOrHoldingBranchReturn','$value','Used by Circulation to determine which branch of an item to check checking-in items','holdingbranch|homebranch','Choice');");
3763 print "Upgrade to $DBversion done (Add HomeOrHoldingBranchReturn system preference)\n";
3764 SetVersion ($DBversion);
3767 $DBversion = "3.02.00.001";
3768 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
3769 $dbh->do(q{DELETE FROM systempreferences WHERE variable IN (
3774 'OPACSubscriptionDisplay',
3775 'OPACDisplayExtendedSubInfo',
3786 print "Upgrade to $DBversion done (bug 3756: remove disused system preferences)\n";
3787 SetVersion ($DBversion);
3790 $DBversion = "3.02.00.002";
3791 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
3792 $dbh->do(q{DELETE FROM systempreferences WHERE variable = 'OpacPrivacy'});
3793 print "Upgrade to $DBversion done (bug 3881: remove unused OpacPrivacy system preference)\n";
3794 SetVersion ($DBversion);
3797 $DBversion = "3.02.00.003";
3798 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
3799 $dbh->do(q{UPDATE systempreferences SET variable = 'ILS-DI:AuthorizedIPs' WHERE variable = 'ILS-DI:Authorized_IPs'});
3800 print "Upgrade to $DBversion done (correct ILS-DI:AuthorizedIPs)\n";
3801 SetVersion ($DBversion);
3804 $DBversion = "3.02.00.004";
3805 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
3806 print "Upgrade to $DBversion done (3.2.0 general release)\n";
3807 SetVersion ($DBversion);
3810 $DBversion = "3.03.00.001";
3811 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
3812 $dbh->do("DELETE FROM subscriptionroutinglist WHERE borrowernumber IS NULL;");
3813 $dbh->do("ALTER TABLE subscriptionroutinglist MODIFY COLUMN `borrowernumber` int(11) NOT NULL;");
3814 $dbh->do("DELETE FROM subscriptionroutinglist WHERE subscriptionid IS NULL;");
3815 $dbh->do("ALTER TABLE subscriptionroutinglist MODIFY COLUMN `subscriptionid` int(11) NOT NULL;");
3816 $dbh->do("CREATE TEMPORARY TABLE del_subscriptionroutinglist
3817 SELECT s1.routingid FROM subscriptionroutinglist s1
3818 WHERE EXISTS (SELECT * FROM subscriptionroutinglist s2
3819 WHERE s2.borrowernumber = s1.borrowernumber
3820 AND s2.subscriptionid = s1.subscriptionid
3821 AND s2.routingid < s1.routingid);");
3822 $dbh->do("DELETE FROM subscriptionroutinglist
3823 WHERE routingid IN (SELECT routingid FROM del_subscriptionroutinglist);");
3824 $dbh->do("ALTER TABLE subscriptionroutinglist ADD UNIQUE (subscriptionid, borrowernumber);");
3825 $dbh->do("ALTER TABLE subscriptionroutinglist
3826 ADD CONSTRAINT `subscriptionroutinglist_ibfk_1` FOREIGN KEY (`borrowernumber`)
3827 REFERENCES `borrowers` (`borrowernumber`)
3828 ON DELETE CASCADE ON UPDATE CASCADE");
3829 $dbh->do("ALTER TABLE subscriptionroutinglist
3830 ADD CONSTRAINT `subscriptionroutinglist_ibfk_2` FOREIGN KEY (`subscriptionid`)
3831 REFERENCES `subscription` (`subscriptionid`)
3832 ON DELETE CASCADE ON UPDATE CASCADE");
3833 print "Upgrade to $DBversion done (Make subscriptionroutinglist more strict)\n";
3834 SetVersion ($DBversion);
3837 $DBversion = '3.03.00.002';
3838 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
3839 $dbh->do("UPDATE language_rfc4646_to_iso639 SET iso639_2_code='arm' WHERE rfc4646_subtag='hy';");
3840 $dbh->do("UPDATE language_rfc4646_to_iso639 SET iso639_2_code='eng' WHERE rfc4646_subtag='en';");
3841 $dbh->do("INSERT INTO language_rfc4646_to_iso639(rfc4646_subtag,iso639_2_code) VALUES( 'fi','fin');");
3842 $dbh->do("UPDATE language_rfc4646_to_iso639 SET iso639_2_code='fre' WHERE rfc4646_subtag='fr';");
3843 $dbh->do("INSERT INTO language_rfc4646_to_iso639(rfc4646_subtag,iso639_2_code) VALUES( 'lo','lao');");
3844 $dbh->do("UPDATE language_rfc4646_to_iso639 SET iso639_2_code='ita' WHERE rfc4646_subtag='it';");
3845 $dbh->do("INSERT INTO language_rfc4646_to_iso639(rfc4646_subtag,iso639_2_code) VALUES( 'sr','srp');");
3846 $dbh->do("INSERT INTO language_rfc4646_to_iso639(rfc4646_subtag,iso639_2_code) VALUES( 'tet','tet');");
3847 $dbh->do("INSERT INTO language_rfc4646_to_iso639(rfc4646_subtag,iso639_2_code) VALUES( 'ur','urd');");
3849 print "Upgrade to $DBversion done (Correct language mappings)\n";
3850 SetVersion ($DBversion);
3853 $DBversion = '3.03.00.003';
3854 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
3855 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('UseTablesortForCirc','0','If on, use the JQuery tablesort function on the list of current borrower checkouts on the circulation page. Note that the use of this function may slow down circ for patrons with may checkouts.','','YesNo');");
3856 print "Upgrade to $DBversion done (Add UseTablesortForCirc syspref)\n";
3857 SetVersion ($DBversion);
3860 $DBversion = '3.03.00.004';
3861 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
3862 my $count = $dbh->selectrow_array('SELECT COUNT(*) FROM letter WHERE module = ? AND code = ?', {}, 'suggestions', 'ACCEPTED');
3864 INSERT INTO `letter`
3865 (module, code, name, title, content)
3867 ('suggestions','ACCEPTED','Suggestion accepted', 'Purchase suggestion accepted','Dear <<borrowers.firstname>> <<borrowers.surname>>,\n\nYou have suggested that the library acquire <<suggestions.title>> by <<suggestions.author>>.\n\nThe library has reviewed your suggestion today. The item will be ordered as soon as possible. You will be notified by mail when the order is completed, and again when the item arrives at the library.\n\nIf you have any questions, please email us at <<branches.branchemail>>.\n\nThank you,\n\n<<branches.branchname>>')
3868 /) unless $count > 0;
3869 $count = $dbh->selectrow_array('SELECT COUNT(*) FROM letter WHERE module = ? AND code = ?', {}, 'suggestions', 'AVAILABLE');
3871 INSERT INTO `letter`
3872 (module, code, name, title, content)
3874 ('suggestions','AVAILABLE','Suggestion available', 'Suggested purchase available','Dear <<borrowers.firstname>> <<borrowers.surname>>,\n\nYou have suggested that the library acquire <<suggestions.title>> by <<suggestions.author>>.\n\nWe are pleased to inform you that the item you requested is now part of the collection.\n\nIf you have any questions, please email us at <<branches.branchemail>>.\n\nThank you,\n\n<<branches.branchname>>')
3875 /) unless $count > 0;
3876 $count = $dbh->selectrow_array('SELECT COUNT(*) FROM letter WHERE module = ? AND code = ?', {}, 'suggestions', 'ORDERED');
3878 INSERT INTO `letter`
3879 (module, code, name, title, content)
3881 ('suggestions','ORDERED','Suggestion ordered', 'Suggested item ordered','Dear <<borrowers.firstname>> <<borrowers.surname>>,\n\nYou have suggested that the library acquire <<suggestions.title>> by <<suggestions.author>>.\n\nWe are pleased to inform you that the item you requested has now been ordered. It should arrive soon, at which time it will be processed for addition into the collection.\n\nYou will be notified again when the book is available.\n\nIf you have any questions, please email us at <<branches.branchemail>>\n\nThank you,\n\n<<branches.branchname>>')
3882 /) unless $count > 0;
3883 $count = $dbh->selectrow_array('SELECT COUNT(*) FROM letter WHERE module = ? AND code = ?', {}, 'suggestions', 'REJECTED');
3885 INSERT INTO `letter`
3886 (module, code, name, title, content)
3888 ('suggestions','REJECTED','Suggestion rejected', 'Purchase suggestion declined','Dear <<borrowers.firstname>> <<borrowers.surname>>,\n\nYou have suggested that the library acquire <<suggestions.title>> by <<suggestions.author>>.\n\nThe library has reviewed your request today, and has decided not to accept the suggestion at this time.\n\nThe reason given is: <<suggestions.reason>>\n\nIf you have any questions, please email us at <<branches.branchemail>>.\n\nThank you,\n\n<<branches.branchname>>')
3889 /) unless $count > 0;
3890 print "Upgrade to $DBversion done (bug 5127: add default templates for suggestion status change notifications)\n";
3891 SetVersion ($DBversion);
3894 $DBversion = '3.03.00.005';
3895 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
3896 $dbh->do("update `systempreferences` set options='whitespace|T-prefix|cuecat|libsuite8' where variable='itemBarcodeInputFilter'");
3897 print "Upgrade to $DBversion done (Add itemBarcodeInputFilter choice libsuite8)\n";
3900 $DBversion = '3.03.00.006';
3901 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
3902 $dbh->do("ALTER TABLE deletedborrowers ADD `privacy` int(11) AFTER smsalertnumber;");
3903 $dbh->do("ALTER TABLE deletedborrowers CHANGE `cardnumber` `cardnumber` varchar(16);");
3904 print "Upgrade to $DBversion done (Fix differences between borrowers and deletedborrowers)\n";
3905 SetVersion ($DBversion);
3908 $DBversion = '3.03.00.007';
3909 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
3910 $dbh->do("ALTER table suggestions ADD quantity SMALLINT(6) default NULL,
3911 ADD currency VARCHAR(3) default NULL,
3912 ADD price DECIMAL(28,6) default NULL,
3913 ADD total DECIMAL(28,6) default NULL;
3915 print "Upgrade to $DBversion done (Added acq related columns to suggestions)\n";
3916 SetVersion ($DBversion);
3919 $DBversion = '3.03.00.008';
3920 if (C4::Context->preference('Version') < TransformToNum($DBversion)){
3921 $dbh->do("INSERT INTO systempreferences (variable,value,explanation,options,type) VALUES ('OPACNoResultsFound','','Display this HTML when no results are found for a search in the OPAC','70|10','Textarea')");
3922 print "Upgrade to $DBversion done adding syspref OPACNoResultsFound to control what displays when no results are found for a search in the OPAC.";
3923 SetVersion ($DBversion);
3926 $DBversion = '3.03.00.009';
3927 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
3928 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('IntranetUserCSS','','Add CSS to be included in the Intranet',NULL,'free')");
3929 print "Upgrade to $DBversion done (Add IntranetUserCSS syspref)\n";
3930 SetVersion ($DBversion);
3933 $DBversion = "3.03.00.010";
3934 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
3935 $dbh->do("UPDATE `marc_subfield_structure` SET liblibrarian = 'Distance from earth' WHERE liblibrarian = 'Distrance from earth' AND tagfield = '034' AND tagsubfield = 'r';");
3936 $dbh->do("UPDATE `marc_subfield_structure` SET libopac = 'Distance from earth' WHERE libopac = 'Distrance from earth' AND tagfield = '034' AND tagsubfield = 'r';");
3937 print "Upgrade to $DBversion done (Fix misspelled 034r subfield in MARC21 Frameworks)\n";
3938 SetVersion ($DBversion);
3941 $DBversion = "3.03.00.011";
3942 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
3943 $dbh->do("UPDATE aqbooksellers SET gstrate=NULL WHERE gstrate=0.0");
3944 print "Upgrade to $DBversion done (Bug 5186: allow GST rate to be set to 0)\n";
3945 SetVersion ($DBversion);
3948 $DBversion = "3.03.00.012";
3949 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
3950 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('maxItemsInSearchResults',20,'Specify the maximum number of items to display for each result on a page of results',NULL,'free')");
3951 print "Upgrade to $DBversion done (Bug 2142: maxItemsInSearchResults syspref resurrected)\n";
3952 SetVersion ($DBversion);
3955 $DBversion = "3.03.00.013";
3956 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
3957 $dbh->do("INSERT INTO systempreferences (variable,value,explanation,options,type) VALUES ('OpacPublic','1','If set to OFF and user is not logged in, all OPAC pages require authentication, and OPAC searchbar is removed)','','YesNo')");
3958 print "Upgrade to $DBversion done (added 'OpacPublic' syspref)\n";
3959 SetVersion ($DBversion);
3962 $DBversion = "3.03.00.014";
3963 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
3964 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES ('ShelfBrowserUsesLocation','1','Use the item location when finding items for the shelf browser.','1','YesNo')");
3965 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES ('ShelfBrowserUsesHomeBranch','1','Use the item home branch when finding items for the shelf browser.','1','YesNo')");
3966 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES ('ShelfBrowserUsesCcode','0','Use the item collection code when finding items for the shelf browser.','1','YesNo')");
3967 print "Upgrade to $DBversion done (Add flexible shelf browser constraints)\n";
3968 SetVersion ($DBversion);
3971 $DBversion = "3.03.00.015";
3972 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
3973 my $sth = $dbh->prepare("INSERT INTO `marc_subfield_structure` (`tagfield`, `tagsubfield`, `liblibrarian`, `libopac`, `repeatable`, `mandatory`, `kohafield`,
3974 `tab`, `authorised_value`, `authtypecode`, `value_builder`, `isurl`, `hidden`, `frameworkcode`, `seealso`, `link`, `defaultvalue`)
3975 VALUES ( ?, '9', '9 (RLIN)', '9 (RLIN)', 0, 0, '', 6, '', '', '', 0, -5, '', '', '', NULL)");
3976 $sth->execute('648');
3977 $sth->execute('654');
3978 $sth->execute('655');
3979 $sth->execute('656');
3980 $sth->execute('657');
3981 $sth->execute('658');
3982 $sth->execute('662');
3984 print "Upgrade to $DBversion done (Bug 5619: Add subfield 9 to marc21 648,654,655,656,657,658,662)\n";
3985 SetVersion ($DBversion);
3988 $DBversion = '3.03.00.016';
3989 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
3990 # reimplement OpacPrivacy system preference
3991 $dbh->do("INSERT INTO systempreferences (variable,value,explanation,options,type) VALUES('OpacPrivacy', '0', 'if ON, allows patrons to define their privacy rules (reading history)',NULL,'YesNo')");
3992 $dbh->do("ALTER TABLE `borrowers` ADD `privacy` INTEGER NOT NULL DEFAULT 1;");
3993 $dbh->do("ALTER TABLE `deletedborrowers` ADD `privacy` INTEGER NOT NULL DEFAULT 1;");
3994 print "Upgrade to $DBversion done (OpacPrivacy reimplementation)\n";
3995 SetVersion($DBversion);
3998 $DBversion = '3.03.00.017';
3999 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
4000 $dbh->do("ALTER TABLE `currency` CHANGE `rate` `rate` FLOAT( 15, 5 ) NULL DEFAULT NULL;");
4001 print "Upgrade to $DBversion done (Enable currency rates >= 100)\n";
4002 SetVersion ($DBversion);
4005 $DBversion = '3.03.00.018';
4006 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
4007 $dbh->do( q|update language_descriptions set description = 'Nederlands' where lang = 'nl' and subtag = 'nl'|);
4008 $dbh->do( q|update language_descriptions set description = 'Dansk' where lang = 'da' and subtag = 'da'|);
4009 print "Upgrade to $DBversion done (Correct language descriptions)\n";
4010 SetVersion ($DBversion);
4013 $DBversion = '3.03.00.019';
4014 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
4016 $dbh->do("UPDATE language_subtag_registry SET description = 'Norwegian bokmål' WHERE subtag = 'nb';");
4017 $dbh->do("INSERT INTO language_rfc4646_to_iso639(rfc4646_subtag,iso639_2_code) VALUES( 'nb','nob');");
4018 $dbh->do("UPDATE language_descriptions SET description = 'Norsk bokmål' WHERE subtag = 'nb' AND lang = 'nb';");
4019 $dbh->do("UPDATE language_descriptions SET description = 'Norwegian bokmål' WHERE subtag = 'nb' AND lang = 'en';");
4020 $dbh->do("UPDATE language_descriptions SET description = 'Norvégien bokmål' WHERE subtag = 'nb' AND lang = 'fr';");
4022 $dbh->do("INSERT INTO language_subtag_registry( subtag, type, description, added) VALUES ( 'nn', 'language', 'Norwegian nynorsk','2011-02-14' )");
4023 $dbh->do("INSERT INTO language_rfc4646_to_iso639(rfc4646_subtag,iso639_2_code) VALUES( 'nn','nno')");
4024 $dbh->do("INSERT INTO language_descriptions(subtag, type, lang, description) VALUES( 'nn', 'language', 'nb', 'Norsk nynorsk')");
4025 $dbh->do("INSERT INTO language_descriptions(subtag, type, lang, description) VALUES( 'nn', 'language', 'nn', 'Norsk nynorsk')");
4026 $dbh->do("INSERT INTO language_descriptions(subtag, type, lang, description) VALUES( 'nn', 'language', 'en', 'Norwegian nynorsk')");
4027 $dbh->do("INSERT INTO language_descriptions(subtag, type, lang, description) VALUES( 'nn', 'language', 'fr', 'Norvégien nynorsk')");
4028 print "Upgrade to $DBversion done (Correct language descriptions for Norwegian)\n";
4029 SetVersion ($DBversion);
4032 $DBversion = '3.03.00.020';
4033 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
4034 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES ('AllowFineOverride','0','If on, staff will be able to issue books to patrons with fines greater than noissuescharge.','0','YesNo')");
4035 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES ('AllFinesNeedOverride','1','If on, staff will be asked to override every fine, even if it is below noissuescharge.','0','YesNo')");
4036 print "Upgrade to $DBversion done (Bug 5811: Add sysprefs controlling overriding fines)\n";
4037 SetVersion($DBversion);
4040 $DBversion = '3.03.00.021';
4041 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
4042 $dbh->do("ALTER TABLE items MODIFY enumchron TEXT");
4043 $dbh->do("ALTER TABLE deleteditems MODIFY enumchron TEXT");
4044 print "Upgrade to $DBversion done (bug 5642: longer serial enumeration)\n";
4045 SetVersion ($DBversion);
4048 $DBversion = '3.03.00.022';
4049 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
4050 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES ('AuthoritiesLog','0','If ON, log edit/create/delete actions on authorities.','','YesNo');");
4051 print "Upgrade to $DBversion done (Add AuthoritiesLog syspref)\n";
4052 SetVersion ($DBversion);
4055 # due to a mismatch in kohastructure.sql some koha will have missing columns in aqbasketgroup
4056 # this attempts to fix that
4057 $DBversion = '3.03.00.023';
4058 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
4059 my $sth = $dbh->prepare("SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'aqbasketgroups' AND COLUMN_NAME = 'billingplace'");
4061 $dbh->do("ALTER TABLE aqbasketgroups ADD billingplace VARCHAR(10)") if ! $sth->fetchrow_hashref;
4062 $sth = $dbh->prepare("SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'aqbasketgroups' AND COLUMN_NAME = 'deliveryplace'");
4064 $dbh->do("ALTER TABLE aqbasketgroups ADD deliveryplace VARCHAR(10)") if ! $sth->fetchrow_hashref;
4065 $sth = $dbh->prepare("SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'aqbasketgroups' AND COLUMN_NAME = 'deliverycomment'");
4067 $dbh->do("ALTER TABLE aqbasketgroups ADD deliverycomment VARCHAR(255)") if ! $sth->fetchrow_hashref;
4068 print "Upgrade to $DBversion done (Reconcile aqbasketgroups)\n";
4069 SetVersion ($DBversion);
4072 $DBversion = '3.03.00.024';
4073 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
4074 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES ('TraceCompleteSubfields','0','Force subject tracings to only match complete subfields.','0','YesNo')");
4075 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES ('UseAuthoritiesForTracings','1','Use authority record numbers for subject tracings instead of heading strings.','0','YesNo')");
4076 print "Upgrade to $DBversion done (Add syspref to force whole-subfield matching on subject tracings)\n";
4077 SetVersion($DBversion);
4080 $DBversion = "3.03.00.025";
4081 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
4082 $dbh->do("INSERT INTO systempreferences (variable,value,explanation,options,type) VALUES ('OPACAllowUserToChooseBranch', 1, 'Allow the user to choose the branch they want to pickup their hold from','1','YesNo')");
4083 print "Upgrade to $DBversion done (Add syspref to control if user can choose pickup branch for holds)\n";
4084 SetVersion ($DBversion);
4087 $DBversion = '3.03.00.026';
4088 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
4089 $dbh->do("UPDATE `message_attributes` SET message_name='Item Due' WHERE message_attribute_id=1 AND message_name LIKE 'Item DUE'");
4090 print "Upgrade to $DBversion done ( fix capitalization in message type )\n";
4091 SetVersion ($DBversion);
4094 $DBversion = '3.03.00.027';
4095 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
4096 $dbh->do("INSERT INTO systempreferences (variable,value,explanation,options,type) VALUES('displayFacetCount', '0', NULL, NULL, 'YesNo')");
4097 $dbh->do("INSERT INTO systempreferences (variable,value,explanation,options,type) VALUES('maxRecordsForFacets', '20', NULL, NULL, 'Integer')");
4098 print "Upgrade to $DBversion done (Preferences for facet count)\n";
4099 SetVersion ($DBversion);
4102 $DBversion = "3.03.00.028";
4103 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
4104 $dbh->do("INSERT INTO systempreferences (variable,value,explanation,options,type) VALUES ('FacetLabelTruncationLength', 20, 'Truncate facets length to','','free')");
4105 print "Upgrade to $DBversion done (Add FacetLabelTruncationLength syspref to control facets displayed length)\n";
4106 SetVersion ($DBversion);
4109 $DBversion = "3.03.00.029";
4110 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
4111 $dbh->do("INSERT INTO systempreferences (variable,value,explanation,options,type) VALUES ('AllowPurchaseSuggestionBranchChoice', 0, 'Allow user to choose branch when making a purchase suggestion','1','YesNo')");
4112 print "Upgrade to $DBversion done (Add syspref to control if user can choose branch when making purchase suggestion)\n";
4113 SetVersion ($DBversion);
4116 $DBversion = "3.03.00.030";
4117 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
4118 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('OpacFavicon','','Enter a complete URL to an image to replace the default Koha favicon on the OPAC','','free')");
4119 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('IntranetFavicon','','Enter a complete URL to an image to replace the default Koha favicon on the Staff client','','free')");
4120 print "Upgrade to $DBversion done (Add sysprefs to control custom favicons)\n";
4121 SetVersion ($DBversion);
4124 $DBversion = "3.03.00.031";
4125 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
4126 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('FineNotifyAtCheckin',0,'If ON notify librarians of overdue fines on the items they are checking in.',NULL,'YesNo');");
4127 print "Upgrade to $DBversion done (Add syspref FineNotifyAtCheckin)\n";
4128 SetVersion ($DBversion);
4131 $DBversion = '3.03.00.032';
4132 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
4133 $dbh->do("INSERT INTO systempreferences (variable,value,explanation,options,type) VALUES ('TraceSubjectSubdivisions', 1, 'Create searches on all subdivisions for subject tracings.','1','YesNo')");
4134 print "Upgrade to $DBversion done ( include subdivisions when generating subject tracing searches )\n";
4135 SetVersion ($DBversion);
4138 $DBversion = '3.03.00.033';
4139 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
4140 $dbh->do("INSERT INTO systempreferences (variable,value,explanation,options,type) VALUES('StaffAuthorisedValueImages', '1', '', NULL, 'YesNo')");
4141 print "Upgrade to $DBversion done (System pref StaffAuthorisedValueImages)\n";
4142 SetVersion ($DBversion);
4145 $DBversion = '3.03.00.034';
4146 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
4147 $dbh->do("ALTER TABLE `categories` ADD `hidelostitems` tinyint(1) NOT NULL default '0' AFTER `reservefee`");
4148 print "Upgrade to $DBversion done (Add hidelostitems preference to borrower categories)\n";
4149 SetVersion ($DBversion);
4152 $DBversion = '3.03.00.035';
4153 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
4154 $dbh->do("ALTER TABLE `issuingrules` ADD hardduedate date default NULL AFTER issuelength");
4155 $dbh->do("ALTER TABLE `issuingrules` ADD hardduedatecompare tinyint NOT NULL default 0 AFTER hardduedate");
4157 if (C4::Context->preference("globalDueDate")) {
4158 $duedate = C4::Dates::format_date_in_iso(C4::Context->preference("globalDueDate"));
4159 $dbh->do("UPDATE `issuingrules` SET hardduedate = '$duedate', hardduedatecompare = 0");
4160 } elsif (C4::Context->preference("ceilingDueDate")) {
4161 $duedate = C4::Dates::format_date_in_iso(C4::Context->preference("ceilingDueDate"));
4162 $dbh->do("UPDATE `issuingrules` SET hardduedate = '$duedate', hardduedatecompare = -1");
4164 $dbh->do("DELETE FROM `systempreferences` WHERE variable = 'globalDueDate' OR variable = 'ceilingDueDate'");
4165 print "Upgrade to $DBversion done (Move global and ceiling due dates to Circ Rules level)\n";
4166 SetVersion ($DBversion);
4169 $DBversion = '3.03.00.036';
4170 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
4171 $dbh->do("INSERT INTO systempreferences (variable,value,explanation,options,type) VALUES ('COinSinOPACResults', 1, 'If ON, use COinS in OPAC search results page. NOTE: this can slow down search response time significantly','','YesNo')");
4172 print "Upgrade to $DBversion done ( Make COinS optional in OPAC search results )\n";
4173 SetVersion ($DBversion);
4176 $DBversion = '3.03.00.037';
4177 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
4178 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('OPACDisplay856uAsImage','OFF','Display the URI in the 856u field as an image, the corresponding OPACXSLT option must be on','OFF|Details|Results|Both','Choice')");
4179 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('Display856uAsImage','OFF','Display the URI in the 856u field as an image, the corresponding Staff Client XSLT option must be on','OFF|Details|Results|Both','Choice')");
4180 print "Upgrade to $DBversion done (Add 'Display856uAsImage' and 'OPACDisplay856uAsImage' syspref)\n";
4181 SetVersion ($DBversion);
4184 $DBversion = '3.03.00.038';
4185 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
4186 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES ('SelfCheckTimeout',120,'Define the number of seconds before the Web-based Self Checkout times out a patron','','Integer')");
4187 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES ('AllowSelfCheckReturns',0,'If enabled, patrons may return items through the Web-based Self Checkout','','YesNo')");
4188 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES ('SelfCheckHelpMessage','','Enter HTML to include under the basic Web-based Self Checkout instructions on the Help page','70|10','Textarea')");
4189 print "Upgrade to $DBversion done ( Add Self-checkout by Login system preferences )\n";
4192 $DBversion = "3.03.00.039";
4193 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
4194 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('ShowReviewer',1,'If ON, name of reviewer will be shown above comments in OPAC',NULL,'YesNo');");
4195 print "Upgrade to $DBversion done (Add syspref ShowReviewer)\n";
4198 $DBversion = "3.03.00.040";
4199 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
4200 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('UseControlNumber',0,'If ON, record control number (w subfields) and control number (001) are used for linking of bibliographic records.','','YesNo');");
4201 print "Upgrade to $DBversion done (Add syspref UseControlNumber)\n";
4204 $DBversion = "3.03.00.041";
4205 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
4206 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('AlternateHoldingsField','','The MARC field/subfield that contains alternate holdings information for bibs taht do not have items attached (e.g. 852abchi for libraries converting from MARC Magician).',NULL,'free')");
4207 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('AlternateHoldingsSeparator','','The string to use to separate subfields in alternate holdings displays.',NULL,'free')");
4208 print "Upgrade to $DBversion done (Add sysprefs to control alternate holdings information display)\n";
4209 SetVersion ($DBversion);
4212 $DBversion = '3.03.00.042';
4213 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
4214 $dbh->do("ALTER TABLE `items` DROP INDEX `itemsstocknumberidx`;");
4215 $dbh->do("ALTER TABLE items ADD INDEX itemstocknumberidx (stocknumber);");
4216 print "Upgrade to $DBversion done (Change items.stocknumber to be not unique)\n";
4217 SetVersion ($DBversion);
4220 $DBversion = "3.03.00.043";
4221 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
4223 $dbh->do("INSERT INTO authorised_values (category,authorised_value,lib,lib_opac) VALUES ('YES_NO','0','No','No')");
4224 $dbh->do("INSERT INTO authorised_values (category,authorised_value,lib,lib_opac) VALUES ('YES_NO','1','Yes','Yes')");
4226 print "Upgrade to $DBversion done ( add generic boolean YES_NO authorised_values pair )\n";
4227 SetVersion ($DBversion);
4232 =head2 DropAllForeignKeys($table)
4234 Drop all foreign keys of the table $table
4239 sub DropAllForeignKeys {
4241 # get the table description
4242 my $sth = $dbh->prepare("SHOW CREATE TABLE $table");
4244 my $vsc_structure = $sth->fetchrow;
4245 # split on CONSTRAINT keyword
4246 my @fks = split /CONSTRAINT /,$vsc_structure;
4249 # isolate what is before FOREIGN KEY, if there is something, it's a foreign key to drop
4250 $_ = /(.*) FOREIGN KEY.*/;
4253 # we have found 1 foreign, drop it
4254 $dbh->do("ALTER TABLE $table DROP FOREIGN KEY $id");
4261 =head2 TransformToNum
4263 Transform the Koha version from a 4 parts string
4264 to a number, with just 1 .
4268 sub TransformToNum {
4269 my $version = shift;
4270 # remove the 3 last . to have a Perl number
4271 $version =~ s/(.*\..*)\.(.*)\.(.*)/$1$2$3/;
4277 set the DBversion in the systempreferences
4282 my $kohaversion = TransformToNum(shift);
4283 if (C4::Context->preference('Version')) {
4284 my $finish=$dbh->prepare("UPDATE systempreferences SET value=? WHERE variable='Version'");
4285 $finish->execute($kohaversion);
4287 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')");
4288 $finish->execute($kohaversion);