5 # This script checks for required updates to the database.
7 # Part of the Koha Library Software www.koha-community.org
8 # Licensed under the GPL.
11 # - Would also be a good idea to offer to do a backup at this time...
13 # NOTE: If you do something more than once in here, make it table driven.
15 # NOTE: Please keep the version in kohaversion.pl up-to-date!
28 use MARC::File::XML ( BinaryEncoding => 'utf8' );
30 # FIXME - The user might be installing a new database, so can't rely
31 # on /etc/koha.conf anyway.
38 %existingtables, # tables already in database
42 $type, $null, $key, $default, $extra,
43 $prefitem, # preference item in systempreferences table
50 my $dbh = C4::Context->dbh;
51 $|=1; # flushes output
55 Deal with virtualshelves
59 my $DBversion = "3.00.00.001";
60 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
61 # update virtualshelves table to
63 $dbh->do("ALTER TABLE `bookshelf` RENAME `virtualshelves`");
64 $dbh->do("ALTER TABLE `shelfcontents` RENAME `virtualshelfcontents`");
65 $dbh->do("ALTER TABLE `virtualshelfcontents` ADD `biblionumber` INT( 11 ) NOT NULL default '0' AFTER shelfnumber");
66 $dbh->do("UPDATE `virtualshelfcontents` SET biblionumber=(SELECT biblionumber FROM items WHERE items.itemnumber=virtualshelfcontents.itemnumber)");
67 # drop all foreign keys : otherwise, we can't drop itemnumber field.
68 DropAllForeignKeys('virtualshelfcontents');
69 $dbh->do("ALTER TABLE `virtualshelfcontents` ADD KEY biblionumber (biblionumber)");
70 # create the new foreign keys (on biblionumber)
71 $dbh->do("ALTER TABLE `virtualshelfcontents` ADD CONSTRAINT `virtualshelfcontents_ibfk_1` FOREIGN KEY (`shelfnumber`) REFERENCES `virtualshelves` (`shelfnumber`) ON DELETE CASCADE ON UPDATE CASCADE");
72 # re-create the foreign key on virtualshelf
73 $dbh->do("ALTER TABLE `virtualshelfcontents` ADD CONSTRAINT `shelfcontents_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE");
74 $dbh->do("ALTER TABLE `virtualshelfcontents` DROP `itemnumber`");
75 print "Upgrade to $DBversion done (virtualshelves)\n";
76 SetVersion ($DBversion);
80 $DBversion = "3.00.00.002";
81 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
82 $dbh->do("DROP TABLE sessions");
83 $dbh->do("CREATE TABLE `sessions` (
84 `id` varchar(32) NOT NULL,
85 `a_session` text NOT NULL,
86 UNIQUE KEY `id` (`id`)
87 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;");
88 print "Upgrade to $DBversion done (sessions uses CGI::session, new table structure for sessions)\n";
89 SetVersion ($DBversion);
93 $DBversion = "3.00.00.003";
94 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
95 if (C4::Context->preference("opaclanguages") eq "fr") {
96 $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')");
98 $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')");
100 print "Upgrade to $DBversion done (adding ReservesNeedReturns systempref, in circulation)\n";
101 SetVersion ($DBversion);
105 $DBversion = "3.00.00.004";
106 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
107 $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')");
108 print "Upgrade to $DBversion done (adding DebugLevel systempref, in 'Admin' tab)\n";
109 SetVersion ($DBversion);
112 $DBversion = "3.00.00.005";
113 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
114 $dbh->do("CREATE TABLE `tags` (
115 `entry` varchar(255) NOT NULL default '',
116 `weight` bigint(20) NOT NULL default 0,
117 PRIMARY KEY (`entry`)
118 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
120 $dbh->do("CREATE TABLE `nozebra` (
121 `server` varchar(20) NOT NULL,
122 `indexname` varchar(40) NOT NULL,
123 `value` varchar(250) NOT NULL,
124 `biblionumbers` longtext NOT NULL,
125 KEY `indexname` (`server`,`indexname`),
126 KEY `value` (`server`,`value`))
127 ENGINE=InnoDB DEFAULT CHARSET=utf8;
129 print "Upgrade to $DBversion done (adding tags and nozebra tables )\n";
130 SetVersion ($DBversion);
133 $DBversion = "3.00.00.006";
134 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
135 $dbh->do("UPDATE issues SET issuedate=timestamp WHERE issuedate='0000-00-00'");
136 print "Upgrade to $DBversion done (filled issues.issuedate with timestamp)\n";
137 SetVersion ($DBversion);
140 $DBversion = "3.00.00.007";
141 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
142 $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')");
143 print "Upgrade to $DBversion done (set SessionStorage variable)\n";
144 SetVersion ($DBversion);
147 $DBversion = "3.00.00.008";
148 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
149 $dbh->do("ALTER TABLE `biblio` ADD `datecreated` DATE NOT NULL AFTER `timestamp` ;");
150 $dbh->do("UPDATE biblio SET datecreated=timestamp");
151 print "Upgrade to $DBversion done (biblio creation date)\n";
152 SetVersion ($DBversion);
155 $DBversion = "3.00.00.009";
156 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
158 # Create backups of call number columns
159 # in case default migration needs to be customized
161 # UPGRADE NOTE: temp_upg_biblioitems_call_num should be dropped
162 # after call numbers have been transformed to the new structure
164 # Not bothering to do the same with deletedbiblioitems -- assume
165 # default is good enough.
166 $dbh->do("CREATE TABLE `temp_upg_biblioitems_call_num` AS
167 SELECT `biblioitemnumber`, `biblionumber`,
168 `classification`, `dewey`, `subclass`,
170 FROM `biblioitems`");
172 # biblioitems changes
173 $dbh->do("ALTER TABLE `biblioitems` CHANGE COLUMN `volumeddesc` `volumedesc` TEXT,
174 ADD `cn_source` VARCHAR(10) DEFAULT NULL AFTER `ccode`,
175 ADD `cn_class` VARCHAR(30) DEFAULT NULL AFTER `cn_source`,
176 ADD `cn_item` VARCHAR(10) DEFAULT NULL AFTER `cn_class`,
177 ADD `cn_suffix` VARCHAR(10) DEFAULT NULL AFTER `cn_item`,
178 ADD `cn_sort` VARCHAR(30) DEFAULT NULL AFTER `cn_suffix`,
179 ADD `totalissues` INT(10) AFTER `cn_sort`");
181 # default mapping of call number columns:
182 # cn_class = concatentation of classification + dewey,
183 # trimmed to fit -- assumes that most users do not
184 # populate both classification and dewey in a single record
186 # cn_source = left null
189 # After upgrade, cn_sort will have to be set based on whatever
190 # default call number scheme user sets as a preference. Misc
191 # script will be added at some point to do that.
193 $dbh->do("UPDATE `biblioitems`
194 SET cn_class = SUBSTR(TRIM(CONCAT_WS(' ', `classification`, `dewey`)), 1, 30),
199 # Now drop the old call number columns
200 $dbh->do("ALTER TABLE `biblioitems` DROP COLUMN `classification`,
202 DROP COLUMN `subclass`,
203 DROP COLUMN `lcsort`,
204 DROP COLUMN `ccode`");
206 # deletedbiblio changes
207 $dbh->do("ALTER TABLE `deletedbiblio` ALTER COLUMN `frameworkcode` SET DEFAULT '',
209 ADD `datecreated` DATE NOT NULL AFTER `timestamp`");
210 $dbh->do("UPDATE deletedbiblio SET datecreated = timestamp");
212 # deletedbiblioitems changes
213 $dbh->do("ALTER TABLE `deletedbiblioitems`
214 MODIFY `publicationyear` TEXT,
215 CHANGE `volumeddesc` `volumedesc` TEXT,
216 MODIFY `collectiontitle` MEDIUMTEXT DEFAULT NULL AFTER `volumedesc`,
217 MODIFY `collectionissn` TEXT DEFAULT NULL AFTER `collectiontitle`,
218 MODIFY `collectionvolume` MEDIUMTEXT DEFAULT NULL AFTER `collectionissn`,
219 MODIFY `editionstatement` TEXT DEFAULT NULL AFTER `collectionvolume`,
220 MODIFY `editionresponsibility` TEXT DEFAULT NULL AFTER `editionstatement`,
221 MODIFY `place` VARCHAR(255) DEFAULT NULL AFTER `size`,
222 MODIFY `marc` LONGBLOB,
223 ADD `cn_source` VARCHAR(10) DEFAULT NULL AFTER `url`,
224 ADD `cn_class` VARCHAR(30) DEFAULT NULL AFTER `cn_source`,
225 ADD `cn_item` VARCHAR(10) DEFAULT NULL AFTER `cn_class`,
226 ADD `cn_suffix` VARCHAR(10) DEFAULT NULL AFTER `cn_item`,
227 ADD `cn_sort` VARCHAR(30) DEFAULT NULL AFTER `cn_suffix`,
228 ADD `totalissues` INT(10) AFTER `cn_sort`,
229 ADD `marcxml` LONGTEXT NOT NULL AFTER `totalissues`,
230 ADD KEY `isbn` (`isbn`),
231 ADD KEY `publishercode` (`publishercode`)
234 $dbh->do("UPDATE `deletedbiblioitems`
235 SET `cn_class` = SUBSTR(TRIM(CONCAT_WS(' ', `classification`, `dewey`)), 1, 30),
236 `cn_item` = `subclass`,
239 $dbh->do("ALTER TABLE `deletedbiblioitems`
240 DROP COLUMN `classification`,
242 DROP COLUMN `subclass`,
243 DROP COLUMN `lcsort`,
247 # deleteditems changes
248 $dbh->do("ALTER TABLE `deleteditems`
249 MODIFY `barcode` VARCHAR(20) DEFAULT NULL,
250 MODIFY `price` DECIMAL(8,2) DEFAULT NULL,
251 MODIFY `replacementprice` DECIMAL(8,2) DEFAULT NULL,
253 MODIFY `itemcallnumber` VARCHAR(30) DEFAULT NULL AFTER `wthdrawn`,
254 MODIFY `holdingbranch` VARCHAR(10) DEFAULT NULL,
256 MODIFY `timestamp` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP AFTER `paidfor`,
258 ADD `cn_source` VARCHAR(10) DEFAULT NULL AFTER `onloan`,
259 ADD `cn_sort` VARCHAR(30) DEFAULT NULL AFTER `cn_source`,
260 ADD `ccode` VARCHAR(10) DEFAULT NULL AFTER `cn_sort`,
261 ADD `materials` VARCHAR(10) DEFAULT NULL AFTER `ccode`,
262 ADD `uri` VARCHAR(255) DEFAULT NULL AFTER `materials`,
263 MODIFY `marc` LONGBLOB AFTER `uri`,
265 DROP KEY `itembarcodeidx`,
266 DROP KEY `itembinoidx`,
267 DROP KEY `itembibnoidx`,
268 ADD UNIQUE KEY `delitembarcodeidx` (`barcode`),
269 ADD KEY `delitembinoidx` (`biblioitemnumber`),
270 ADD KEY `delitembibnoidx` (`biblionumber`),
271 ADD KEY `delhomebranch` (`homebranch`),
272 ADD KEY `delholdingbranch` (`holdingbranch`)");
273 $dbh->do("UPDATE deleteditems SET `ccode` = `itype`");
274 $dbh->do("ALTER TABLE deleteditems DROP `itype`");
275 $dbh->do("UPDATE `deleteditems` SET `cn_sort` = `itemcallnumber`");
278 $dbh->do("ALTER TABLE `items` ADD `cn_source` VARCHAR(10) DEFAULT NULL AFTER `onloan`,
279 ADD `cn_sort` VARCHAR(30) DEFAULT NULL AFTER `cn_source`,
280 ADD `ccode` VARCHAR(10) DEFAULT NULL AFTER `cn_sort`,
281 ADD `materials` VARCHAR(10) DEFAULT NULL AFTER `ccode`,
282 ADD `uri` VARCHAR(255) DEFAULT NULL AFTER `materials`
284 $dbh->do("ALTER TABLE `items`
285 DROP KEY `itembarcodeidx`,
286 ADD UNIQUE KEY `itembarcodeidx` (`barcode`)");
288 # map items.itype to items.ccode and
289 # set cn_sort to itemcallnumber -- as with biblioitems.cn_sort,
290 # will have to be subsequently updated per user's default
291 # classification scheme
292 $dbh->do("UPDATE `items` SET `cn_sort` = `itemcallnumber`,
295 $dbh->do("ALTER TABLE `items` DROP `cutterextra`,
298 print "Upgrade to $DBversion done (major changes to biblio, biblioitems, items, and deleted* versions of same\n";
299 SetVersion ($DBversion);
302 $DBversion = "3.00.00.010";
303 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
304 $dbh->do("CREATE INDEX `userid` ON borrowers (`userid`) ");
305 print "Upgrade to $DBversion done (userid index added)\n";
306 SetVersion ($DBversion);
309 $DBversion = "3.00.00.011";
310 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
311 $dbh->do("ALTER TABLE `branchcategories` CHANGE `categorycode` `categorycode` varchar(10) ");
312 $dbh->do("ALTER TABLE `branchcategories` CHANGE `categoryname` `categoryname` varchar(32) ");
313 $dbh->do("ALTER TABLE `branchcategories` ADD COLUMN `categorytype` varchar(16) ");
314 $dbh->do("UPDATE `branchcategories` SET `categorytype` = 'properties'");
315 $dbh->do("ALTER TABLE `branchrelations` CHANGE `categorycode` `categorycode` varchar(10) ");
316 print "Upgrade to $DBversion done (added branchcategory type)\n";
317 SetVersion ($DBversion);
320 $DBversion = "3.00.00.012";
321 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
322 $dbh->do("CREATE TABLE `class_sort_rules` (
323 `class_sort_rule` varchar(10) NOT NULL default '',
324 `description` mediumtext,
325 `sort_routine` varchar(30) NOT NULL default '',
326 PRIMARY KEY (`class_sort_rule`),
327 UNIQUE KEY `class_sort_rule_idx` (`class_sort_rule`)
328 ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
329 $dbh->do("CREATE TABLE `class_sources` (
330 `cn_source` varchar(10) NOT NULL default '',
331 `description` mediumtext,
332 `used` tinyint(4) NOT NULL default 0,
333 `class_sort_rule` varchar(10) NOT NULL default '',
334 PRIMARY KEY (`cn_source`),
335 UNIQUE KEY `cn_source_idx` (`cn_source`),
336 KEY `used_idx` (`used`),
337 CONSTRAINT `class_source_ibfk_1` FOREIGN KEY (`class_sort_rule`)
338 REFERENCES `class_sort_rules` (`class_sort_rule`)
339 ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
340 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type)
341 VALUES('DefaultClassificationSource','ddc',
342 'Default classification scheme used by the collection. E.g., Dewey, LCC, etc.', NULL,'free')");
343 $dbh->do("INSERT INTO `class_sort_rules` (`class_sort_rule`, `description`, `sort_routine`) VALUES
344 ('dewey', 'Default filing rules for DDC', 'Dewey'),
345 ('lcc', 'Default filing rules for LCC', 'LCC'),
346 ('generic', 'Generic call number filing rules', 'Generic')");
347 $dbh->do("INSERT INTO `class_sources` (`cn_source`, `description`, `used`, `class_sort_rule`) VALUES
348 ('ddc', 'Dewey Decimal Classification', 1, 'dewey'),
349 ('lcc', 'Library of Congress Classification', 1, 'lcc'),
350 ('udc', 'Universal Decimal Classification', 0, 'generic'),
351 ('sudocs', 'SuDoc Classification (U.S. GPO)', 0, 'generic'),
352 ('z', 'Other/Generic Classification Scheme', 0, 'generic')");
353 print "Upgrade to $DBversion done (classification sources added)\n";
354 SetVersion ($DBversion);
357 $DBversion = "3.00.00.013";
358 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
359 $dbh->do("CREATE TABLE `import_batches` (
360 `import_batch_id` int(11) NOT NULL auto_increment,
361 `template_id` int(11) default NULL,
362 `branchcode` varchar(10) default NULL,
363 `num_biblios` int(11) NOT NULL default 0,
364 `num_items` int(11) NOT NULL default 0,
365 `upload_timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP,
366 `overlay_action` enum('replace', 'create_new', 'use_template') NOT NULL default 'create_new',
367 `import_status` enum('staging', 'staged', 'importing', 'imported', 'reverting', 'reverted', 'cleaned') NOT NULL default 'staging',
368 `batch_type` enum('batch', 'z3950') NOT NULL default 'batch',
369 `file_name` varchar(100),
370 `comments` mediumtext,
371 PRIMARY KEY (`import_batch_id`),
372 KEY `branchcode` (`branchcode`)
373 ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
374 $dbh->do("CREATE TABLE `import_records` (
375 `import_record_id` int(11) NOT NULL auto_increment,
376 `import_batch_id` int(11) NOT NULL,
377 `branchcode` varchar(10) default NULL,
378 `record_sequence` int(11) NOT NULL default 0,
379 `upload_timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP,
380 `import_date` DATE default NULL,
381 `marc` longblob NOT NULL,
382 `marcxml` longtext NOT NULL,
383 `marcxml_old` longtext NOT NULL,
384 `record_type` enum('biblio', 'auth', 'holdings') NOT NULL default 'biblio',
385 `overlay_status` enum('no_match', 'auto_match', 'manual_match', 'match_applied') NOT NULL default 'no_match',
386 `status` enum('error', 'staged', 'imported', 'reverted', 'items_reverted') NOT NULL default 'staged',
387 `import_error` mediumtext,
388 `encoding` varchar(40) NOT NULL default '',
389 `z3950random` varchar(40) default NULL,
390 PRIMARY KEY (`import_record_id`),
391 CONSTRAINT `import_records_ifbk_1` FOREIGN KEY (`import_batch_id`)
392 REFERENCES `import_batches` (`import_batch_id`) ON DELETE CASCADE ON UPDATE CASCADE,
393 KEY `branchcode` (`branchcode`),
394 KEY `batch_sequence` (`import_batch_id`, `record_sequence`)
395 ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
396 $dbh->do("CREATE TABLE `import_record_matches` (
397 `import_record_id` int(11) NOT NULL,
398 `candidate_match_id` int(11) NOT NULL,
399 `score` int(11) NOT NULL default 0,
400 CONSTRAINT `import_record_matches_ibfk_1` FOREIGN KEY (`import_record_id`)
401 REFERENCES `import_records` (`import_record_id`) ON DELETE CASCADE ON UPDATE CASCADE,
402 KEY `record_score` (`import_record_id`, `score`)
403 ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
404 $dbh->do("CREATE TABLE `import_biblios` (
405 `import_record_id` int(11) NOT NULL,
406 `matched_biblionumber` int(11) default NULL,
407 `control_number` varchar(25) default NULL,
408 `original_source` varchar(25) default NULL,
409 `title` varchar(128) default NULL,
410 `author` varchar(80) default NULL,
411 `isbn` varchar(14) default NULL,
412 `issn` varchar(9) default NULL,
413 `has_items` tinyint(1) NOT NULL default 0,
414 CONSTRAINT `import_biblios_ibfk_1` FOREIGN KEY (`import_record_id`)
415 REFERENCES `import_records` (`import_record_id`) ON DELETE CASCADE ON UPDATE CASCADE,
416 KEY `matched_biblionumber` (`matched_biblionumber`),
417 KEY `title` (`title`),
419 ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
420 $dbh->do("CREATE TABLE `import_items` (
421 `import_items_id` int(11) NOT NULL auto_increment,
422 `import_record_id` int(11) NOT NULL,
423 `itemnumber` int(11) default NULL,
424 `branchcode` varchar(10) default NULL,
425 `status` enum('error', 'staged', 'imported', 'reverted') NOT NULL default 'staged',
426 `marcxml` longtext NOT NULL,
427 `import_error` mediumtext,
428 PRIMARY KEY (`import_items_id`),
429 CONSTRAINT `import_items_ibfk_1` FOREIGN KEY (`import_record_id`)
430 REFERENCES `import_records` (`import_record_id`) ON DELETE CASCADE ON UPDATE CASCADE,
431 KEY `itemnumber` (`itemnumber`),
432 KEY `branchcode` (`branchcode`)
433 ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
435 $dbh->do("INSERT INTO `import_batches`
436 (`overlay_action`, `import_status`, `batch_type`, `file_name`)
437 SELECT distinct 'create_new', 'staged', 'z3950', `file`
438 FROM `marc_breeding`");
440 $dbh->do("INSERT INTO `import_records`
441 (`import_batch_id`, `import_record_id`, `record_sequence`, `marc`, `record_type`, `status`,
442 `encoding`, `z3950random`, `marcxml`, `marcxml_old`)
443 SELECT `import_batch_id`, `id`, 1, `marc`, 'biblio', 'staged', `encoding`, `z3950random`, '', ''
445 JOIN `import_batches` ON (`file_name` = `file`)");
447 $dbh->do("INSERT INTO `import_biblios`
448 (`import_record_id`, `title`, `author`, `isbn`)
449 SELECT `import_record_id`, `title`, `author`, `isbn`
451 JOIN `import_records` ON (`import_record_id` = `id`)");
453 $dbh->do("UPDATE `import_batches`
454 SET `num_biblios` = (
456 FROM `import_records`
457 WHERE `import_batch_id` = `import_batches`.`import_batch_id`
460 $dbh->do("DROP TABLE `marc_breeding`");
462 print "Upgrade to $DBversion done (import_batches et al. added)\n";
463 SetVersion ($DBversion);
466 $DBversion = "3.00.00.014";
467 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
468 $dbh->do("ALTER TABLE subscription ADD lastbranch VARCHAR(4)");
469 print "Upgrade to $DBversion done (userid index added)\n";
470 SetVersion ($DBversion);
473 $DBversion = "3.00.00.015";
474 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
475 $dbh->do("CREATE TABLE `saved_sql` (
476 `id` int(11) NOT NULL auto_increment,
477 `borrowernumber` int(11) default NULL,
478 `date_created` datetime default NULL,
479 `last_modified` datetime default NULL,
481 `last_run` datetime default NULL,
482 `report_name` varchar(255) default NULL,
483 `type` varchar(255) default NULL,
486 KEY boridx (`borrowernumber`)
487 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;");
488 $dbh->do("CREATE TABLE `saved_reports` (
489 `id` int(11) NOT NULL auto_increment,
490 `report_id` int(11) default NULL,
492 `date_run` datetime default NULL,
494 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;");
495 print "Upgrade to $DBversion done (saved_sql and saved_reports added)\n";
496 SetVersion ($DBversion);
499 $DBversion = "3.00.00.016";
500 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
501 $dbh->do(" CREATE TABLE reports_dictionary (
502 id int(11) NOT NULL auto_increment,
503 name varchar(255) default NULL,
505 date_created datetime default NULL,
506 date_modified datetime default NULL,
508 area int(11) default NULL,
510 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ");
511 print "Upgrade to $DBversion done (reports_dictionary) added)\n";
512 SetVersion ($DBversion);
515 $DBversion = "3.00.00.017";
516 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
517 $dbh->do("ALTER TABLE action_logs DROP PRIMARY KEY");
518 $dbh->do("ALTER TABLE action_logs ADD KEY timestamp (timestamp,user)");
519 $dbh->do("ALTER TABLE action_logs ADD action_id INT(11) NOT NULL FIRST");
520 $dbh->do("UPDATE action_logs SET action_id = if (\@a, \@a:=\@a+1, \@a:=1)");
521 $dbh->do("ALTER TABLE action_logs MODIFY action_id INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY");
522 print "Upgrade to $DBversion done (added column to action_logs)\n";
523 SetVersion ($DBversion);
526 $DBversion = "3.00.00.018";
527 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
528 $dbh->do("ALTER TABLE `zebraqueue`
529 ADD `done` INT NOT NULL DEFAULT '0',
530 ADD `time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ;
532 print "Upgrade to $DBversion done (adding timestamp and done columns to zebraque table to improve problem tracking) added)\n";
533 SetVersion ($DBversion);
536 $DBversion = "3.00.00.019";
537 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
538 $dbh->do("ALTER TABLE biblio MODIFY biblionumber INT(11) NOT NULL AUTO_INCREMENT");
539 $dbh->do("ALTER TABLE biblioitems MODIFY biblioitemnumber INT(11) NOT NULL AUTO_INCREMENT");
540 $dbh->do("ALTER TABLE items MODIFY itemnumber INT(11) NOT NULL AUTO_INCREMENT");
541 print "Upgrade to $DBversion done (made bib/item PKs auto_increment)\n";
542 SetVersion ($DBversion);
545 $DBversion = "3.00.00.020";
546 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
547 $dbh->do("ALTER TABLE deleteditems
548 DROP KEY `delitembarcodeidx`,
549 ADD KEY `delitembarcodeidx` (`barcode`)");
550 print "Upgrade to $DBversion done (dropped uniqueness of key on deleteditems.barcode)\n";
551 SetVersion ($DBversion);
554 $DBversion = "3.00.00.021";
555 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
556 $dbh->do("ALTER TABLE items CHANGE homebranch homebranch VARCHAR(10)");
557 $dbh->do("ALTER TABLE deleteditems CHANGE homebranch homebranch VARCHAR(10)");
558 $dbh->do("ALTER TABLE statistics CHANGE branch branch VARCHAR(10)");
559 $dbh->do("ALTER TABLE subscription CHANGE lastbranch lastbranch VARCHAR(10)");
560 print "Upgrade to $DBversion done (extended missed branchcode columns to 10 chars)\n";
561 SetVersion ($DBversion);
564 $DBversion = "3.00.00.022";
565 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
566 $dbh->do("ALTER TABLE items
567 ADD `damaged` tinyint(1) default NULL AFTER notforloan");
568 $dbh->do("ALTER TABLE deleteditems
569 ADD `damaged` tinyint(1) default NULL AFTER notforloan");
570 print "Upgrade to $DBversion done (adding damaged column to items table)\n";
571 SetVersion ($DBversion);
574 $DBversion = "3.00.00.023";
575 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
576 $dbh->do("INSERT INTO `systempreferences` (variable,value,options,explanation,type)
577 VALUES ('yuipath','http://yui.yahooapis.com/2.3.1/build','Insert the path to YUI libraries','','free')");
578 print "Upgrade to $DBversion done (adding new system preference for controlling YUI path)\n";
579 SetVersion ($DBversion);
581 $DBversion = "3.00.00.024";
582 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
583 $dbh->do("ALTER TABLE biblioitems CHANGE itemtype itemtype VARCHAR(10)");
584 print "Upgrade to $DBversion done (changing itemtype to (10))\n";
585 SetVersion ($DBversion);
588 $DBversion = "3.00.00.025";
589 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
590 $dbh->do("ALTER TABLE items ADD COLUMN itype VARCHAR(10)");
591 $dbh->do("ALTER TABLE deleteditems ADD COLUMN itype VARCHAR(10) AFTER uri");
592 if(C4::Context->preference('item-level_itypes')){
593 $dbh->do('update items,biblioitems set items.itype=biblioitems.itemtype where items.biblionumber=biblioitems.biblionumber and itype is null');
595 print "Upgrade to $DBversion done (reintroduce items.itype - fill from itemtype)\n ";
596 SetVersion ($DBversion);
599 $DBversion = "3.00.00.026";
600 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
601 $dbh->do("INSERT INTO `systempreferences` (variable,value,options,explanation,type)
602 VALUES ('HomeOrHoldingBranch','homebranch','homebranch|holdingbranch','With independent branches turned on this decides whether to check the items holdingbranch or homebranch at circulatilon','choice')");
603 print "Upgrade to $DBversion done (adding new system preference for choosing whether homebranch or holdingbranch is checked in circulation)\n";
604 SetVersion ($DBversion);
607 $DBversion = "3.00.00.027";
608 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
609 $dbh->do("CREATE TABLE `marc_matchers` (
610 `matcher_id` int(11) NOT NULL auto_increment,
611 `code` varchar(10) NOT NULL default '',
612 `description` varchar(255) NOT NULL default '',
613 `record_type` varchar(10) NOT NULL default 'biblio',
614 `threshold` int(11) NOT NULL default 0,
615 PRIMARY KEY (`matcher_id`),
617 KEY `record_type` (`record_type`)
618 ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
619 $dbh->do("CREATE TABLE `matchpoints` (
620 `matcher_id` int(11) NOT NULL,
621 `matchpoint_id` int(11) NOT NULL auto_increment,
622 `search_index` varchar(30) NOT NULL default '',
623 `score` int(11) NOT NULL default 0,
624 PRIMARY KEY (`matchpoint_id`),
625 CONSTRAINT `matchpoints_ifbk_1` FOREIGN KEY (`matcher_id`)
626 REFERENCES `marc_matchers` (`matcher_id`) ON DELETE CASCADE ON UPDATE CASCADE
627 ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
628 $dbh->do("CREATE TABLE `matchpoint_components` (
629 `matchpoint_id` int(11) NOT NULL,
630 `matchpoint_component_id` int(11) NOT NULL auto_increment,
631 sequence int(11) NOT NULL default 0,
632 tag varchar(3) NOT NULL default '',
633 subfields varchar(40) NOT NULL default '',
634 offset int(4) NOT NULL default 0,
635 length int(4) NOT NULL default 0,
636 PRIMARY KEY (`matchpoint_component_id`),
637 KEY `by_sequence` (`matchpoint_id`, `sequence`),
638 CONSTRAINT `matchpoint_components_ifbk_1` FOREIGN KEY (`matchpoint_id`)
639 REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE
640 ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
641 $dbh->do("CREATE TABLE `matchpoint_component_norms` (
642 `matchpoint_component_id` int(11) NOT NULL,
643 `sequence` int(11) NOT NULL default 0,
644 `norm_routine` varchar(50) NOT NULL default '',
645 KEY `matchpoint_component_norms` (`matchpoint_component_id`, `sequence`),
646 CONSTRAINT `matchpoint_component_norms_ifbk_1` FOREIGN KEY (`matchpoint_component_id`)
647 REFERENCES `matchpoint_components` (`matchpoint_component_id`) ON DELETE CASCADE ON UPDATE CASCADE
648 ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
649 $dbh->do("CREATE TABLE `matcher_matchpoints` (
650 `matcher_id` int(11) NOT NULL,
651 `matchpoint_id` int(11) NOT NULL,
652 CONSTRAINT `matcher_matchpoints_ifbk_1` FOREIGN KEY (`matcher_id`)
653 REFERENCES `marc_matchers` (`matcher_id`) ON DELETE CASCADE ON UPDATE CASCADE,
654 CONSTRAINT `matcher_matchpoints_ifbk_2` FOREIGN KEY (`matchpoint_id`)
655 REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE
656 ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
657 $dbh->do("CREATE TABLE `matchchecks` (
658 `matcher_id` int(11) NOT NULL,
659 `matchcheck_id` int(11) NOT NULL auto_increment,
660 `source_matchpoint_id` int(11) NOT NULL,
661 `target_matchpoint_id` int(11) NOT NULL,
662 PRIMARY KEY (`matchcheck_id`),
663 CONSTRAINT `matcher_matchchecks_ifbk_1` FOREIGN KEY (`matcher_id`)
664 REFERENCES `marc_matchers` (`matcher_id`) ON DELETE CASCADE ON UPDATE CASCADE,
665 CONSTRAINT `matcher_matchchecks_ifbk_2` FOREIGN KEY (`source_matchpoint_id`)
666 REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE,
667 CONSTRAINT `matcher_matchchecks_ifbk_3` FOREIGN KEY (`target_matchpoint_id`)
668 REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE
669 ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
670 print "Upgrade to $DBversion done (added C4::Matcher serialization tables)\n ";
671 SetVersion ($DBversion);
674 $DBversion = "3.00.00.028";
675 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
676 $dbh->do("INSERT INTO `systempreferences` (variable,value,options,explanation,type)
677 VALUES ('canreservefromotherbranches','1','','With Independent branches on, can a user from one library reserve an item from another library','YesNo')");
678 print "Upgrade to $DBversion done (adding new system preference for changing reserve/holds behaviour with independent branches)\n";
679 SetVersion ($DBversion);
683 $DBversion = "3.00.00.029";
684 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
685 $dbh->do("ALTER TABLE `import_batches` ADD `matcher_id` int(11) NULL AFTER `import_batch_id`");
686 print "Upgrade to $DBversion done (adding matcher_id to import_batches)\n";
687 SetVersion ($DBversion);
690 $DBversion = "3.00.00.030";
691 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
693 CREATE TABLE services_throttle (
694 service_type varchar(10) NOT NULL default '',
695 service_count varchar(45) default NULL,
696 PRIMARY KEY (service_type)
697 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
699 $dbh->do("INSERT INTO `systempreferences` (variable,value,options,explanation,type)
700 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')");
701 $dbh->do("INSERT INTO `systempreferences` (variable,value,options,explanation,type)
702 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')");
703 $dbh->do("INSERT INTO `systempreferences` (variable,value,options,explanation,type)
704 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')");
705 $dbh->do("INSERT INTO `systempreferences` (variable,value,options,explanation,type)
706 VALUES ('XISBNDailyLimit',499,'','The xISBN Web service is free for non-commercial use when usage does not exceed 500 requests per day','free')");
707 $dbh->do("INSERT INTO `systempreferences` (variable,value,options,explanation,type)
708 VALUES ('PINESISBN',0,'','Use with FRBRizeEditions. If ON, Koha will use PINES OISBN web service in the Editions tab on the detail pages.','YesNo')");
709 $dbh->do("INSERT INTO `systempreferences` (variable,value,options,explanation,type)
710 VALUES ('ThingISBN',0,'','Use with FRBRizeEditions. If ON, Koha will use the ThingISBN web service in the Editions tab on the detail pages.','YesNo')");
711 print "Upgrade to $DBversion done (adding services throttle table and sysprefs for xISBN)\n";
712 SetVersion ($DBversion);
715 $DBversion = "3.00.00.031";
716 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
718 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('QueryStemming',1,'If ON, enables query stemming',NULL,'YesNo')");
719 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('QueryFuzzy',1,'If ON, enables fuzzy option for searches',NULL,'YesNo')");
720 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('QueryWeightFields',1,'If ON, enables field weighting',NULL,'YesNo')");
721 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('WebBasedSelfCheck',0,'If ON, enables the web-based self-check system',NULL,'YesNo')");
722 $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')");
723 $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')");
724 $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')");
725 $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')");
726 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('defaultSortOrder',NULL,'Specify the default sort order','asc|dsc|az|za','Choice')");
727 $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')");
728 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('OPACdefaultSortOrder',NULL,'Specify the default sort order','asc|dsc|za|az','Choice')");
729 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('staffClientBaseURL','','Specify the base URL of the staff client',NULL,'free')");
730 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('minPasswordLength',3,'Specify the minimum length of a patron/staff password',NULL,'free')");
731 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('noItemTypeImages',0,'If ON, disables item-type images',NULL,'YesNo')");
732 $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')");
733 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('holdCancelLength','','Specify how many days before a hold is canceled',NULL,'free')");
734 $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')");
735 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('finesMode','test','Choose the fines mode, test or production','test|production','Choice')");
736 $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')");
737 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('itemBarcodeInputFilter','','If set, allows specification of a item barcode input filter','cuecat','Choice')");
738 $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')");
739 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('URLLinkText','','Text to display as the link anchor in the OPAC',NULL,'free')");
740 $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')");
741 $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')");
742 $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')");
743 $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')");
744 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('OPACUserCSS',0,'Add CSS to be included in the OPAC',NULL,'free')");
746 print "Upgrade to $DBversion done (adding additional system preference)\n";
747 SetVersion ($DBversion);
750 $DBversion = "3.00.00.032";
751 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
752 $dbh->do("UPDATE `marc_subfield_structure` SET `kohafield` = 'items.wthdrawn' WHERE `kohafield` = 'items.withdrawn'");
753 print "Upgrade to $DBversion done (fixed MARC framework references to items.withdrawn)\n";
754 SetVersion ($DBversion);
757 $DBversion = "3.00.00.033";
758 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
759 $dbh->do("INSERT INTO `userflags` VALUES(17,'staffaccess','Modify login / permissions for staff users',0)");
760 print "Upgrade to $DBversion done (Adding permissions flag for staff member access modification. )\n";
761 SetVersion ($DBversion);
764 $DBversion = "3.00.00.034";
765 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
766 $dbh->do("ALTER TABLE `virtualshelves` ADD COLUMN `sortfield` VARCHAR(16) ");
767 print "Upgrade to $DBversion done (Adding sortfield for Virtual Shelves. )\n";
768 SetVersion ($DBversion);
771 $DBversion = "3.00.00.035";
772 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
773 $dbh->do("UPDATE marc_subfield_structure
774 SET authorised_value = 'cn_source'
775 WHERE kohafield IN ('items.cn_source', 'biblioitems.cn_source')
776 AND (authorised_value is NULL OR authorised_value = '')");
777 print "Upgrade to $DBversion done (MARC frameworks: make classification source a drop-down)\n";
778 SetVersion ($DBversion);
781 $DBversion = "3.00.00.036";
782 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
783 $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');");
784 print "Upgrade to $DBversion done (OPACItemsResultsDisplay systempreference added)\n";
785 SetVersion ($DBversion);
788 $DBversion = "3.00.00.037";
789 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
790 $dbh->do("ALTER TABLE `borrowers` ADD COLUMN `altcontactfirstname` varchar(255)");
791 $dbh->do("ALTER TABLE `borrowers` ADD COLUMN `altcontactsurname` varchar(255)");
792 $dbh->do("ALTER TABLE `borrowers` ADD COLUMN `altcontactaddress1` varchar(255)");
793 $dbh->do("ALTER TABLE `borrowers` ADD COLUMN `altcontactaddress2` varchar(255)");
794 $dbh->do("ALTER TABLE `borrowers` ADD COLUMN `altcontactaddress3` varchar(255)");
795 $dbh->do("ALTER TABLE `borrowers` ADD COLUMN `altcontactzipcode` varchar(50)");
796 $dbh->do("ALTER TABLE `borrowers` ADD COLUMN `altcontactphone` varchar(50)");
797 print "Upgrade to $DBversion done (Adding Alternative Contact Person information to borrowers table)\n";
798 SetVersion ($DBversion);
801 $DBversion = "3.00.00.038";
802 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
803 $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'");
804 $dbh->do("DELETE FROM `systempreferences` WHERE variable='hideBiblioNumber'");
805 print "Upgrade to $DBversion done ('alter finesMode systempreference, remove superfluous syspref.')\n";
806 SetVersion ($DBversion);
809 $DBversion = "3.00.00.039";
810 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
811 $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')");
812 $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')");
813 $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')");
814 # $dbh->do("DELETE FROM `systempreferences` WHERE variable='HomeOrHoldingBranch'"); # Bug #2752
815 print "Upgrade to $DBversion done ('add circ sysprefs CircControl, finesCalendar, and uppercasesurnames, and delete HomeOrHoldingBranch.')\n";
816 SetVersion ($DBversion);
819 $DBversion = "3.00.00.040";
820 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
821 $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')");
822 $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')");
823 print "Upgrade to $DBversion done ('add circ sysprefs todaysIssuesDefaultSortOrder and previousIssuesDefaultSortOrder.')\n";
824 SetVersion ($DBversion);
828 $DBversion = "3.00.00.041";
829 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
830 # Strictly speaking it is not necessary to explicitly change
831 # NULL values to 0, because the ALTER TABLE statement will do that.
832 # However, setting them first avoids a warning.
833 $dbh->do("UPDATE items SET notforloan = 0 WHERE notforloan IS NULL");
834 $dbh->do("UPDATE items SET damaged = 0 WHERE damaged IS NULL");
835 $dbh->do("UPDATE items SET itemlost = 0 WHERE itemlost IS NULL");
836 $dbh->do("UPDATE items SET wthdrawn = 0 WHERE wthdrawn IS NULL");
837 $dbh->do("ALTER TABLE items
838 MODIFY notforloan tinyint(1) NOT NULL default 0,
839 MODIFY damaged tinyint(1) NOT NULL default 0,
840 MODIFY itemlost tinyint(1) NOT NULL default 0,
841 MODIFY wthdrawn tinyint(1) NOT NULL default 0");
842 $dbh->do("UPDATE deleteditems SET notforloan = 0 WHERE notforloan IS NULL");
843 $dbh->do("UPDATE deleteditems SET damaged = 0 WHERE damaged IS NULL");
844 $dbh->do("UPDATE deleteditems SET itemlost = 0 WHERE itemlost IS NULL");
845 $dbh->do("UPDATE deleteditems SET wthdrawn = 0 WHERE wthdrawn IS NULL");
846 $dbh->do("ALTER TABLE deleteditems
847 MODIFY notforloan tinyint(1) NOT NULL default 0,
848 MODIFY damaged tinyint(1) NOT NULL default 0,
849 MODIFY itemlost tinyint(1) NOT NULL default 0,
850 MODIFY wthdrawn tinyint(1) NOT NULL default 0");
851 print "Upgrade to $DBversion done (disallow NULL in several item status columns)\n";
852 SetVersion ($DBversion);
855 $DBversion = "3.00.00.042";
856 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
857 $dbh->do("ALTER TABLE aqbooksellers CHANGE name name mediumtext NOT NULL");
858 print "Upgrade to $DBversion done (disallow NULL in aqbooksellers.name; part of fix for bug 1251)\n";
859 SetVersion ($DBversion);
862 $DBversion = "3.00.00.043";
863 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
864 $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");
865 print "Upgrade to $DBversion done (currency table: add symbol and timestamp columns)\n";
866 SetVersion ($DBversion);
869 $DBversion = "3.00.00.044";
870 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
871 $dbh->do("ALTER TABLE deletedborrowers
872 ADD `altcontactfirstname` varchar(255) default NULL,
873 ADD `altcontactsurname` varchar(255) default NULL,
874 ADD `altcontactaddress1` varchar(255) default NULL,
875 ADD `altcontactaddress2` varchar(255) default NULL,
876 ADD `altcontactaddress3` varchar(255) default NULL,
877 ADD `altcontactzipcode` varchar(50) default NULL,
878 ADD `altcontactphone` varchar(50) default NULL
880 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES
881 ('OPACBaseURL',NULL,'Specify the Base URL of the OPAC, e.g., opac.mylibrary.com, the http:// will be added automatically by Koha.',NULL,'Free'),
882 ('language','en','Set the default language in the staff client.',NULL,'Languages'),
883 ('QueryAutoTruncate',1,'If ON, query truncation is enabled by default',NULL,'YesNo'),
884 ('QueryRemoveStopwords',0,'If ON, stopwords listed in the Administration area will be removed from queries',NULL,'YesNo')
886 print "Upgrade to $DBversion done (syncing deletedborrowers table with borrowers table)\n";
887 SetVersion ($DBversion);
890 #-- http://www.w3.org/International/articles/language-tags/
893 $DBversion = "3.00.00.045";
894 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
896 CREATE TABLE language_subtag_registry (
898 type varchar(25), -- language-script-region-variant-extension-privateuse
899 description varchar(25), -- only one of the possible descriptions for ease of reference, see language_descriptions for the complete list
901 KEY `subtag` (`subtag`)
902 ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
904 #-- TODO: add suppress_scripts
905 #-- this maps three letter codes defined in iso639.2 back to their
906 #-- two letter equivilents in rfc4646 (LOC maintains iso639+)
907 $dbh->do("CREATE TABLE language_rfc4646_to_iso639 (
908 rfc4646_subtag varchar(25),
909 iso639_2_code varchar(25),
910 KEY `rfc4646_subtag` (`rfc4646_subtag`)
911 ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
913 $dbh->do("CREATE TABLE language_descriptions (
917 description varchar(255),
919 ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
921 #-- bi-directional support, keyed by script subcode
922 $dbh->do("CREATE TABLE language_script_bidi (
923 rfc4646_subtag varchar(25), -- script subtag, Arab, Hebr, etc.
924 bidi varchar(3), -- rtl ltr
925 KEY `rfc4646_subtag` (`rfc4646_subtag`)
926 ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
928 #-- BIDI Stuff, Arabic and Hebrew
929 $dbh->do("INSERT INTO language_script_bidi(rfc4646_subtag,bidi)
930 VALUES( 'Arab', 'rtl')");
931 $dbh->do("INSERT INTO language_script_bidi(rfc4646_subtag,bidi)
932 VALUES( 'Hebr', 'rtl')");
934 #-- TODO: need to map language subtags to script subtags for detection
935 #-- of bidi when script is not specified (like ar, he)
936 $dbh->do("CREATE TABLE language_script_mapping (
937 language_subtag varchar(25),
938 script_subtag varchar(25),
939 KEY `language_subtag` (`language_subtag`)
940 ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
942 #-- Default mappings between script and language subcodes
943 $dbh->do("INSERT INTO language_script_mapping(language_subtag,script_subtag)
944 VALUES( 'ar', 'Arab')");
945 $dbh->do("INSERT INTO language_script_mapping(language_subtag,script_subtag)
946 VALUES( 'he', 'Hebr')");
948 print "Upgrade to $DBversion done (adding language subtag registry and basic BiDi support NOTE: You should import the subtag registry SQL)\n";
949 SetVersion ($DBversion);
952 $DBversion = "3.00.00.046";
953 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
954 $dbh->do("ALTER TABLE `subscription` CHANGE `numberlength` `numberlength` int(11) default '0' ,
955 CHANGE `weeklength` `weeklength` int(11) default '0'");
956 $dbh->do("CREATE TABLE `serialitems` (`serialid` int(11) NOT NULL, `itemnumber` int(11) NOT NULL, UNIQUE KEY `serialididx` (`serialid`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
957 $dbh->do("INSERT INTO `serialitems` SELECT `serialid`,`itemnumber` from serial where NOT ISNULL(itemnumber) && itemnumber <> '' && itemnumber NOT LIKE '%,%'");
958 print "Upgrade to $DBversion done (Add serialitems table to link serial issues to items. )\n";
959 SetVersion ($DBversion);
962 $DBversion = "3.00.00.047";
963 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
964 $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');");
965 print "Upgrade to $DBversion done ( Added OpacRenewalAllowed syspref )\n";
966 SetVersion ($DBversion);
969 $DBversion = "3.00.00.048";
970 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
971 $dbh->do("ALTER TABLE `items` ADD `more_subfields_xml` longtext default NULL AFTER `itype`");
972 print "Upgrade to $DBversion done (added items.more_subfields_xml)\n";
973 SetVersion ($DBversion);
976 $DBversion = "3.00.00.049";
977 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
978 $dbh->do("ALTER TABLE `z3950servers` ADD `encoding` text default NULL AFTER type ");
979 print "Upgrade to $DBversion done ( Added encoding field to z3950servers table )\n";
980 SetVersion ($DBversion);
983 $DBversion = "3.00.00.050";
984 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
985 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('OpacHighlightedWords','0','If Set, query matched terms are highlighted in OPAC',NULL,'YesNo');");
986 print "Upgrade to $DBversion done ( Added OpacHighlightedWords syspref )\n";
987 SetVersion ($DBversion);
990 $DBversion = "3.00.00.051";
991 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
992 $dbh->do("UPDATE systempreferences SET explanation = 'Define the current theme for the OPAC interface.' WHERE variable = 'opacthemes';");
993 print "Upgrade to $DBversion done ( Corrected opacthemes explanation. )\n";
994 SetVersion ($DBversion);
997 $DBversion = "3.00.00.052";
998 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
999 $dbh->do("ALTER TABLE `deleteditems` ADD `more_subfields_xml` LONGTEXT DEFAULT NULL AFTER `itype`");
1000 print "Upgrade to $DBversion done ( Adding missing column to deleteditems table. )\n";
1001 SetVersion ($DBversion);
1004 $DBversion = "3.00.00.053";
1005 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1006 $dbh->do("CREATE TABLE `printers_profile` (
1007 `prof_id` int(4) NOT NULL auto_increment,
1008 `printername` varchar(40) NOT NULL,
1009 `tmpl_id` int(4) NOT NULL,
1010 `paper_bin` varchar(20) NOT NULL,
1011 `offset_horz` float default NULL,
1012 `offset_vert` float default NULL,
1013 `creep_horz` float default NULL,
1014 `creep_vert` float default NULL,
1015 `unit` char(20) NOT NULL default 'POINT',
1016 PRIMARY KEY (`prof_id`),
1017 UNIQUE KEY `printername` (`printername`,`tmpl_id`,`paper_bin`),
1018 CONSTRAINT `printers_profile_pnfk_1` FOREIGN KEY (`tmpl_id`) REFERENCES `labels_templates` (`tmpl_id`) ON DELETE CASCADE ON UPDATE CASCADE
1019 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ");
1020 $dbh->do("CREATE TABLE `labels_profile` (
1021 `tmpl_id` int(4) NOT NULL,
1022 `prof_id` int(4) NOT NULL,
1023 UNIQUE KEY `tmpl_id` (`tmpl_id`),
1024 UNIQUE KEY `prof_id` (`prof_id`)
1025 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ");
1026 print "Upgrade to $DBversion done ( Printer Profile tables added )\n";
1027 SetVersion ($DBversion);
1030 $DBversion = "3.00.00.054";
1031 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1032 $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';");
1033 print "Upgrade to $DBversion done ( Added another barcode autogeneration sequence to barcode.pl. )\n";
1034 SetVersion ($DBversion);
1037 $DBversion = "3.00.00.055";
1038 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1039 $dbh->do("ALTER TABLE `zebraqueue` ADD KEY `zebraqueue_lookup` (`server`, `biblio_auth_number`, `operation`, `done`)");
1040 print "Upgrade to $DBversion done ( Added index on zebraqueue. )\n";
1041 SetVersion ($DBversion);
1043 $DBversion = "3.00.00.056";
1044 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1045 if (C4::Context->preference("marcflavour") eq 'UNIMARC') {
1046 $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) ");
1048 $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) ");
1050 $dbh->do("ALTER TABLE `items` ADD `enumchron` VARCHAR(80) DEFAULT NULL;");
1051 print "Upgrade to $DBversion done ( Added item.enumchron column, and framework map to 952h )\n";
1052 SetVersion ($DBversion);
1055 $DBversion = "3.00.00.057";
1056 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1057 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('OAI-PMH','0','if ON, OAI-PMH server is enabled',NULL,'YesNo');");
1058 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('OAI-PMH:archiveID','KOHA-OAI-TEST','OAI-PMH archive identification',NULL,'Free');");
1059 $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');");
1060 $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');");
1061 $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');");
1062 SetVersion ($DBversion);
1065 $DBversion = "3.00.00.058";
1066 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1067 $dbh->do("ALTER TABLE `opac_news`
1068 CHANGE `lang` `lang` VARCHAR( 25 )
1070 COLLATE utf8_general_ci
1071 NOT NULL default ''");
1072 print "Upgrade to $DBversion done ( lang field in opac_news made longer )\n";
1073 SetVersion ($DBversion);
1076 $DBversion = "3.00.00.059";
1077 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1079 $dbh->do("CREATE TABLE IF NOT EXISTS `labels_templates` (
1080 `tmpl_id` int(4) NOT NULL auto_increment,
1081 `tmpl_code` char(100) default '',
1082 `tmpl_desc` char(100) default '',
1083 `page_width` float default '0',
1084 `page_height` float default '0',
1085 `label_width` float default '0',
1086 `label_height` float default '0',
1087 `topmargin` float default '0',
1088 `leftmargin` float default '0',
1089 `cols` int(2) default '0',
1090 `rows` int(2) default '0',
1091 `colgap` float default '0',
1092 `rowgap` float default '0',
1093 `active` int(1) default NULL,
1094 `units` char(20) default 'PX',
1095 `fontsize` int(4) NOT NULL default '3',
1096 PRIMARY KEY (`tmpl_id`)
1097 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;");
1098 $dbh->do("CREATE TABLE IF NOT EXISTS `printers_profile` (
1099 `prof_id` int(4) NOT NULL auto_increment,
1100 `printername` varchar(40) NOT NULL,
1101 `tmpl_id` int(4) NOT NULL,
1102 `paper_bin` varchar(20) NOT NULL,
1103 `offset_horz` float default NULL,
1104 `offset_vert` float default NULL,
1105 `creep_horz` float default NULL,
1106 `creep_vert` float default NULL,
1107 `unit` char(20) NOT NULL default 'POINT',
1108 PRIMARY KEY (`prof_id`),
1109 UNIQUE KEY `printername` (`printername`,`tmpl_id`,`paper_bin`),
1110 CONSTRAINT `printers_profile_pnfk_1` FOREIGN KEY (`tmpl_id`) REFERENCES `labels_templates` (`tmpl_id`) ON DELETE CASCADE ON UPDATE CASCADE
1111 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ");
1112 print "Upgrade to $DBversion done ( Added labels_templates table if it did not exist. )\n";
1113 SetVersion ($DBversion);
1116 $DBversion = "3.00.00.060";
1117 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1118 $dbh->do("CREATE TABLE IF NOT EXISTS `patronimage` (
1119 `cardnumber` varchar(16) NOT NULL,
1120 `mimetype` varchar(15) NOT NULL,
1121 `imagefile` mediumblob NOT NULL,
1122 PRIMARY KEY (`cardnumber`),
1123 CONSTRAINT `patronimage_fk1` FOREIGN KEY (`cardnumber`) REFERENCES `borrowers` (`cardnumber`) ON DELETE CASCADE ON UPDATE CASCADE
1124 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;");
1125 print "Upgrade to $DBversion done ( Added patronimage table. )\n";
1126 SetVersion ($DBversion);
1129 $DBversion = "3.00.00.061";
1130 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1131 $dbh->do("ALTER TABLE labels_templates ADD COLUMN font char(10) NOT NULL DEFAULT 'TR';");
1132 print "Upgrade to $DBversion done ( Added font column to labels_templates )\n";
1133 SetVersion ($DBversion);
1136 $DBversion = "3.00.00.062";
1137 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1138 $dbh->do("CREATE TABLE `old_issues` (
1139 `borrowernumber` int(11) default NULL,
1140 `itemnumber` int(11) default NULL,
1141 `date_due` date default NULL,
1142 `branchcode` varchar(10) default NULL,
1143 `issuingbranch` varchar(18) default NULL,
1144 `returndate` date default NULL,
1145 `lastreneweddate` date default NULL,
1146 `return` varchar(4) default NULL,
1147 `renewals` tinyint(4) default NULL,
1148 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1149 `issuedate` date default NULL,
1150 KEY `old_issuesborridx` (`borrowernumber`),
1151 KEY `old_issuesitemidx` (`itemnumber`),
1152 KEY `old_bordate` (`borrowernumber`,`timestamp`),
1153 CONSTRAINT `old_issues_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`)
1154 ON DELETE SET NULL ON UPDATE SET NULL,
1155 CONSTRAINT `old_issues_ibfk_2` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`)
1156 ON DELETE SET NULL ON UPDATE SET NULL
1157 ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
1158 $dbh->do("CREATE TABLE `old_reserves` (
1159 `borrowernumber` int(11) default NULL,
1160 `reservedate` date default NULL,
1161 `biblionumber` int(11) default NULL,
1162 `constrainttype` varchar(1) default NULL,
1163 `branchcode` varchar(10) default NULL,
1164 `notificationdate` date default NULL,
1165 `reminderdate` date default NULL,
1166 `cancellationdate` date default NULL,
1167 `reservenotes` mediumtext,
1168 `priority` smallint(6) default NULL,
1169 `found` varchar(1) default NULL,
1170 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1171 `itemnumber` int(11) default NULL,
1172 `waitingdate` date default NULL,
1173 KEY `old_reserves_borrowernumber` (`borrowernumber`),
1174 KEY `old_reserves_biblionumber` (`biblionumber`),
1175 KEY `old_reserves_itemnumber` (`itemnumber`),
1176 KEY `old_reserves_branchcode` (`branchcode`),
1177 CONSTRAINT `old_reserves_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`)
1178 ON DELETE SET NULL ON UPDATE SET NULL,
1179 CONSTRAINT `old_reserves_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`)
1180 ON DELETE SET NULL ON UPDATE SET NULL,
1181 CONSTRAINT `old_reserves_ibfk_3` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`)
1182 ON DELETE SET NULL ON UPDATE SET NULL
1183 ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
1185 # move closed transactions to old_* tables
1186 $dbh->do("INSERT INTO old_issues SELECT * FROM issues WHERE returndate IS NOT NULL");
1187 $dbh->do("DELETE FROM issues WHERE returndate IS NOT NULL");
1188 $dbh->do("INSERT INTO old_reserves SELECT * FROM reserves WHERE cancellationdate IS NOT NULL OR found = 'F'");
1189 $dbh->do("DELETE FROM reserves WHERE cancellationdate IS NOT NULL OR found = 'F'");
1191 print "Upgrade to $DBversion done ( Added old_issues and old_reserves tables )\n";
1192 SetVersion ($DBversion);
1195 $DBversion = "3.00.00.063";
1196 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1197 $dbh->do("ALTER TABLE deleteditems
1198 CHANGE COLUMN booksellerid booksellerid MEDIUMTEXT DEFAULT NULL,
1199 ADD COLUMN enumchron VARCHAR(80) DEFAULT NULL AFTER more_subfields_xml,
1200 ADD COLUMN copynumber SMALLINT(6) DEFAULT NULL AFTER enumchron;");
1201 $dbh->do("ALTER TABLE items
1202 CHANGE COLUMN booksellerid booksellerid MEDIUMTEXT,
1203 ADD COLUMN copynumber SMALLINT(6) DEFAULT NULL AFTER enumchron;");
1204 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";
1205 SetVersion ($DBversion);
1208 $DBversion = "3.00.00.064";
1209 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1210 $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');");
1211 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('AWSAccessKeyID','','See: http://aws.amazon.com','','free');");
1212 $dbh->do("DELETE FROM `systempreferences` WHERE variable='AmazonDevKey';");
1213 $dbh->do("DELETE FROM `systempreferences` WHERE variable='XISBNAmazonSimilarItems';");
1214 $dbh->do("DELETE FROM `systempreferences` WHERE variable='OPACXISBNAmazonSimilarItems';");
1215 print "Upgrade to $DBversion done (IMPORTANT: Upgrading to Amazon.com Associates Web Service 4.0 ) \n";
1216 SetVersion ($DBversion);
1219 $DBversion = "3.00.00.065";
1220 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1221 $dbh->do("CREATE TABLE `patroncards` (
1222 `cardid` int(11) NOT NULL auto_increment,
1223 `batch_id` varchar(10) NOT NULL default '1',
1224 `borrowernumber` int(11) NOT NULL,
1225 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1226 PRIMARY KEY (`cardid`),
1227 KEY `patroncards_ibfk_1` (`borrowernumber`),
1228 CONSTRAINT `patroncards_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE
1229 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;");
1230 print "Upgrade to $DBversion done (Adding patroncards table for patroncards generation feature. ) \n";
1231 SetVersion ($DBversion);
1234 $DBversion = "3.00.00.066";
1235 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1236 $dbh->do("ALTER TABLE `virtualshelfcontents` MODIFY `dateadded` timestamp NOT NULL
1237 DEFAULT CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP;
1239 print "Upgrade to $DBversion done (fix for bug 1873: virtualshelfcontents dateadded column empty. ) \n";
1240 SetVersion ($DBversion);
1243 $DBversion = "3.00.00.067";
1244 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1245 $dbh->do("UPDATE systempreferences SET explanation = 'Enable patron images for the Staff Client', type = 'YesNo' WHERE variable = 'patronimages'");
1246 print "Upgrade to $DBversion done (Updating patronimages syspref to reflect current kohastructure.sql. ) \n";
1247 SetVersion ($DBversion);
1250 $DBversion = "3.00.00.068";
1251 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1252 $dbh->do("CREATE TABLE `permissions` (
1253 `module_bit` int(11) NOT NULL DEFAULT 0,
1254 `code` varchar(30) DEFAULT NULL,
1255 `description` varchar(255) DEFAULT NULL,
1256 PRIMARY KEY (`module_bit`, `code`),
1257 CONSTRAINT `permissions_ibfk_1` FOREIGN KEY (`module_bit`) REFERENCES `userflags` (`bit`)
1258 ON DELETE CASCADE ON UPDATE CASCADE
1259 ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
1260 $dbh->do("CREATE TABLE `user_permissions` (
1261 `borrowernumber` int(11) NOT NULL DEFAULT 0,
1262 `module_bit` int(11) NOT NULL DEFAULT 0,
1263 `code` varchar(30) DEFAULT NULL,
1264 CONSTRAINT `user_permissions_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`)
1265 ON DELETE CASCADE ON UPDATE CASCADE,
1266 CONSTRAINT `user_permissions_ibfk_2` FOREIGN KEY (`module_bit`, `code`)
1267 REFERENCES `permissions` (`module_bit`, `code`)
1268 ON DELETE CASCADE ON UPDATE CASCADE
1269 ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
1271 $dbh->do("INSERT INTO permissions (module_bit, code, description) VALUES
1272 (13, 'edit_news', 'Write news for the OPAC and staff interfaces'),
1273 (13, 'label_creator', 'Create printable labels and barcodes from catalog and patron data'),
1274 (13, 'edit_calendar', 'Define days when the library is closed'),
1275 (13, 'moderate_comments', 'Moderate patron comments'),
1276 (13, 'edit_notices', 'Define notices'),
1277 (13, 'edit_notice_status_triggers', 'Set notice/status triggers for overdue items'),
1278 (13, 'view_system_logs', 'Browse the system logs'),
1279 (13, 'inventory', 'Perform inventory (stocktaking) of your catalogue'),
1280 (13, 'stage_marc_import', 'Stage MARC records into the reservoir'),
1281 (13, 'manage_staged_marc', 'Managed staged MARC records, including completing and reversing imports'),
1282 (13, 'export_catalog', 'Export bibliographic and holdings data'),
1283 (13, 'import_patrons', 'Import patron data'),
1284 (13, 'delete_anonymize_patrons', 'Delete old borrowers and anonymize circulation history (deletes borrower reading history)'),
1285 (13, 'batch_upload_patron_images', 'Upload patron images in batch or one at a time'),
1286 (13, 'schedule_tasks', 'Schedule tasks to run')");
1288 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('GranularPermissions','0','Use detailed staff user permissions',NULL,'YesNo')");
1290 print "Upgrade to $DBversion done (adding permissions and user_permissions tables and GranularPermissions syspref) \n";
1291 SetVersion ($DBversion);
1293 $DBversion = "3.00.00.069";
1294 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1295 $dbh->do("ALTER TABLE labels_conf CHANGE COLUMN class classification int(1) DEFAULT NULL;");
1296 print "Upgrade to $DBversion done ( Correcting columname in labels_conf )\n";
1297 SetVersion ($DBversion);
1300 $DBversion = "3.00.00.070";
1301 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1302 $sth = $dbh->prepare("SELECT value FROM systempreferences WHERE variable='yuipath'");
1304 my ($value) = $sth->fetchrow;
1305 $value =~ s/2.3.1/2.5.1/;
1306 $dbh->do("UPDATE systempreferences SET value='$value' WHERE variable='yuipath';");
1307 print "Update yuipath syspref to 2.5.1 if necessary\n";
1308 SetVersion ($DBversion);
1311 $DBversion = "3.00.00.071";
1312 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1313 $dbh->do(" ALTER TABLE `subscription` ADD `serialsadditems` TINYINT( 1 ) NOT NULL DEFAULT '0';");
1314 # fill the new field with the previous systempreference value, then drop the syspref
1315 my $sth = $dbh->prepare("SELECT value FROM systempreferences WHERE variable='serialsadditems'");
1317 my ($serialsadditems) = $sth->fetchrow();
1318 $dbh->do("UPDATE subscription SET serialsadditems=$serialsadditems");
1319 $dbh->do("DELETE FROM systempreferences WHERE variable='serialsadditems'");
1320 print "Upgrade to $DBversion done ( moving serialsadditems from syspref to subscription )\n";
1321 SetVersion ($DBversion);
1324 $DBversion = "3.00.00.072";
1325 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1326 $dbh->do("ALTER TABLE labels_conf ADD COLUMN formatstring mediumtext DEFAULT NULL AFTER printingtype");
1327 print "Upgrade to $DBversion done ( Adding format string to labels generator. )\n";
1328 SetVersion ($DBversion);
1331 $DBversion = "3.00.00.073";
1332 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1333 $dbh->do("DROP TABLE IF EXISTS `tags_all`;");
1335 CREATE TABLE `tags_all` (
1336 `tag_id` int(11) NOT NULL auto_increment,
1337 `borrowernumber` int(11) NOT NULL,
1338 `biblionumber` int(11) NOT NULL,
1339 `term` varchar(255) NOT NULL,
1340 `language` int(4) default NULL,
1341 `date_created` datetime NOT NULL,
1342 PRIMARY KEY (`tag_id`),
1343 KEY `tags_borrowers_fk_1` (`borrowernumber`),
1344 KEY `tags_biblionumber_fk_1` (`biblionumber`),
1345 CONSTRAINT `tags_borrowers_fk_1` FOREIGN KEY (`borrowernumber`)
1346 REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1347 CONSTRAINT `tags_biblionumber_fk_1` FOREIGN KEY (`biblionumber`)
1348 REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE
1349 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1351 $dbh->do("DROP TABLE IF EXISTS `tags_approval`;");
1353 CREATE TABLE `tags_approval` (
1354 `term` varchar(255) NOT NULL,
1355 `approved` int(1) NOT NULL default '0',
1356 `date_approved` datetime default NULL,
1357 `approved_by` int(11) default NULL,
1358 `weight_total` int(9) NOT NULL default '1',
1359 PRIMARY KEY (`term`),
1360 KEY `tags_approval_borrowers_fk_1` (`approved_by`),
1361 CONSTRAINT `tags_approval_borrowers_fk_1` FOREIGN KEY (`approved_by`)
1362 REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE
1363 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1365 $dbh->do("DROP TABLE IF EXISTS `tags_index`;");
1367 CREATE TABLE `tags_index` (
1368 `term` varchar(255) NOT NULL,
1369 `biblionumber` int(11) NOT NULL,
1370 `weight` int(9) NOT NULL default '1',
1371 PRIMARY KEY (`term`,`biblionumber`),
1372 KEY `tags_index_biblionumber_fk_1` (`biblionumber`),
1373 CONSTRAINT `tags_index_term_fk_1` FOREIGN KEY (`term`)
1374 REFERENCES `tags_approval` (`term`) ON DELETE CASCADE ON UPDATE CASCADE,
1375 CONSTRAINT `tags_index_biblionumber_fk_1` FOREIGN KEY (`biblionumber`)
1376 REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE
1377 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1380 INSERT INTO `systempreferences` VALUES
1381 ('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=',''),
1382 ('BakerTaylorEnabled','0','','Enable or disable all Baker & Taylor features.','YesNo'),
1383 ('BakerTaylorPassword','','','Baker & Taylor Password for Content Cafe (external content)','Textarea'),
1384 ('BakerTaylorUsername','','','Baker & Taylor Username for Content Cafe (external content)','Textarea'),
1385 ('TagsEnabled','1','','Enables or disables all tagging features. This is the main switch for tags.','YesNo'),
1386 ('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.',''),
1387 ('TagsInputOnDetail','1','','Allow users to input tags from the detail page.', 'YesNo'),
1388 ('TagsInputOnList', '0','','Allow users to input tags from the search results list.', 'YesNo'),
1389 ('TagsModeration', NULL,'','Require tags from patrons to be approved before becoming visible.','YesNo'),
1390 ('TagsShowOnDetail','10','','Number of tags to display on detail page. 0 is off.', 'Integer'),
1391 ('TagsShowOnList', '6','','Number of tags to display on search results list. 0 is off.','Integer')
1393 print "Upgrade to $DBversion done (Baker/Taylor,Tags: sysprefs and tables (tags_all, tags_index, tags_approval)) \n";
1394 SetVersion ($DBversion);
1397 $DBversion = "3.00.00.074";
1398 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1399 $dbh->do( q(update itemtypes set imageurl = concat( 'npl/', imageurl )
1400 where imageurl not like 'http%'
1401 and imageurl is not NULL
1402 and imageurl != '') );
1403 print "Upgrade to $DBversion done (updating imagetype.imageurls to reflect new icon locations.)\n";
1404 SetVersion ($DBversion);
1407 $DBversion = "3.00.00.075";
1408 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1409 $dbh->do( q(alter table authorised_values add imageurl varchar(200) default NULL) );
1410 print "Upgrade to $DBversion done (adding imageurl field to authorised_values table)\n";
1411 SetVersion ($DBversion);
1414 $DBversion = "3.00.00.076";
1415 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1416 $dbh->do("ALTER TABLE import_batches
1417 ADD COLUMN nomatch_action enum('create_new', 'ignore') NOT NULL default 'create_new' AFTER overlay_action");
1418 $dbh->do("ALTER TABLE import_batches
1419 ADD COLUMN item_action enum('always_add', 'add_only_for_matches', 'add_only_for_new', 'ignore')
1420 NOT NULL default 'always_add' AFTER nomatch_action");
1421 $dbh->do("ALTER TABLE import_batches
1422 MODIFY overlay_action enum('replace', 'create_new', 'use_template', 'ignore')
1423 NOT NULL default 'create_new'");
1424 $dbh->do("ALTER TABLE import_records
1425 MODIFY status enum('error', 'staged', 'imported', 'reverted', 'items_reverted',
1426 'ignored') NOT NULL default 'staged'");
1427 $dbh->do("ALTER TABLE import_items
1428 MODIFY status enum('error', 'staged', 'imported', 'reverted', 'ignored') NOT NULL default 'staged'");
1430 print "Upgrade to $DBversion done (changes to import_batches and import_records)\n";
1431 SetVersion ($DBversion);
1434 $DBversion = "3.00.00.077";
1435 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1436 # drop these tables only if they exist and none of them are empty
1437 # these tables are not defined in the packaged 2.2.9, but since it is believed
1438 # that at least one library may be using them in a post-2.2.9 but pre-3.0 Koha,
1439 # some care is taken.
1440 my ($print_error) = $dbh->{PrintError};
1441 $dbh->{PrintError} = 0;
1442 my ($raise_error) = $dbh->{RaiseError};
1443 $dbh->{RaiseError} = 1;
1447 eval { $count = $dbh->do("SELECT 1 FROM categorytable"); };
1451 eval { $count = $dbh->do("SELECT 1 FROM mediatypetable"); };
1455 eval { $count = $dbh->do("SELECT 1 FROM subcategorytable"); };
1461 $dbh->do("DROP TABLE IF EXISTS `categorytable`");
1462 $dbh->do("DROP TABLE IF EXISTS `mediatypetable`");
1463 $dbh->do("DROP TABLE IF EXISTS `subcategorytable`");
1466 $dbh->{PrintError} = $print_error;
1467 $dbh->{RaiseError} = $raise_error;
1468 print "Upgrade to $DBversion done (drop categorytable, subcategorytable, and mediatypetable)\n";
1469 SetVersion ($DBversion);
1472 $DBversion = "3.00.00.078";
1473 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1474 my ($print_error) = $dbh->{PrintError};
1475 $dbh->{PrintError} = 0;
1477 unless ($dbh->do("SELECT 1 FROM browser")) {
1478 $dbh->{PrintError} = $print_error;
1479 $dbh->do("CREATE TABLE `browser` (
1480 `level` int(11) NOT NULL,
1481 `classification` varchar(20) NOT NULL,
1482 `description` varchar(255) NOT NULL,
1483 `number` bigint(20) NOT NULL,
1484 `endnode` tinyint(4) NOT NULL
1485 ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
1487 $dbh->{PrintError} = $print_error;
1488 print "Upgrade to $DBversion done (add browser table if not already present)\n";
1489 SetVersion ($DBversion);
1492 $DBversion = "3.00.00.079";
1493 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1494 my ($print_error) = $dbh->{PrintError};
1495 $dbh->{PrintError} = 0;
1497 $dbh->do("INSERT INTO `systempreferences` (variable, value,options,type, explanation)VALUES
1498 ('AddPatronLists','categorycode','categorycode|category_type','Choice','Allow user to choose what list to pick up from when adding patrons')");
1499 print "Upgrade to $DBversion done (add browser table if not already present)\n";
1500 SetVersion ($DBversion);
1503 $DBversion = "3.00.00.080";
1504 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1505 $dbh->do("ALTER TABLE subscription CHANGE monthlength monthlength int(11) default '0'");
1506 $dbh->do("ALTER TABLE deleteditems MODIFY marc LONGBLOB AFTER copynumber");
1507 $dbh->do("ALTER TABLE aqbooksellers CHANGE name name mediumtext NOT NULL");
1508 print "Upgrade to $DBversion done (catch up on DB schema changes since alpha and beta)\n";
1509 SetVersion ($DBversion);
1512 $DBversion = "3.00.00.081";
1513 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1514 $dbh->do("CREATE TABLE `borrower_attribute_types` (
1515 `code` varchar(10) NOT NULL,
1516 `description` varchar(255) NOT NULL,
1517 `repeatable` tinyint(1) NOT NULL default 0,
1518 `unique_id` tinyint(1) NOT NULL default 0,
1519 `opac_display` tinyint(1) NOT NULL default 0,
1520 `password_allowed` tinyint(1) NOT NULL default 0,
1521 `staff_searchable` tinyint(1) NOT NULL default 0,
1522 `authorised_value_category` varchar(10) default NULL,
1523 PRIMARY KEY (`code`)
1524 ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
1525 $dbh->do("CREATE TABLE `borrower_attributes` (
1526 `borrowernumber` int(11) NOT NULL,
1527 `code` varchar(10) NOT NULL,
1528 `attribute` varchar(30) default NULL,
1529 `password` varchar(30) default NULL,
1530 KEY `borrowernumber` (`borrowernumber`),
1531 KEY `code_attribute` (`code`, `attribute`),
1532 CONSTRAINT `borrower_attributes_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`)
1533 ON DELETE CASCADE ON UPDATE CASCADE,
1534 CONSTRAINT `borrower_attributes_ibfk_2` FOREIGN KEY (`code`) REFERENCES `borrower_attribute_types` (`code`)
1535 ON DELETE CASCADE ON UPDATE CASCADE
1536 ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
1537 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('ExtendedPatronAttributes','0','Use extended patron IDs and attributes',NULL,'YesNo')");
1538 print "Upgrade to $DBversion done (added borrower_attributes and borrower_attribute_types)\n";
1539 SetVersion ($DBversion);
1542 $DBversion = "3.00.00.082";
1543 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1544 $dbh->do( q(alter table accountlines add column lastincrement decimal(28,6) default NULL) );
1545 print "Upgrade to $DBversion done (adding lastincrement column to accountlines table)\n";
1546 SetVersion ($DBversion);
1549 $DBversion = "3.00.00.083";
1550 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1551 $dbh->do( qq(UPDATE systempreferences SET value='local' where variable='yuipath' and value like "%/intranet-tmpl/prog/%"));
1552 print "Upgrade to $DBversion done (Changing yuipath behaviour in managing a local value)\n";
1553 SetVersion ($DBversion);
1555 $DBversion = "3.00.00.084";
1556 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1557 $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')");
1558 $dbh->do("INSERT INTO systempreferences (variable,value,explanation,options,type) VALUES('GoogleJackets','0','if ON, displays jacket covers from Google Books API',NULL,'YesNo')");
1559 print "Upgrade to $DBversion done (add new sysprefs)\n";
1560 SetVersion ($DBversion);
1563 $DBversion = "3.00.00.085";
1564 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1565 if (C4::Context->preference("marcflavour") eq 'MARC21') {
1566 $dbh->do("UPDATE marc_subfield_structure SET tab = 0 WHERE tab = 9 AND tagfield = '037'");
1567 $dbh->do("UPDATE marc_subfield_structure SET tab = 1 WHERE tab = 6 AND tagfield in ('100', '110', '111', '130')");
1568 $dbh->do("UPDATE marc_subfield_structure SET tab = 2 WHERE tab = 6 AND tagfield in ('240', '243')");
1569 $dbh->do("UPDATE marc_subfield_structure SET tab = 4 WHERE tab = 6 AND tagfield in ('400', '410', '411', '440')");
1570 $dbh->do("UPDATE marc_subfield_structure SET tab = 5 WHERE tab = 9 AND tagfield = '584'");
1571 $dbh->do("UPDATE marc_subfield_structure SET tab = 7 WHERE tab = -6 AND tagfield = '760'");
1573 print "Upgrade to $DBversion done (move editing tab of various MARC21 subfields)\n";
1574 SetVersion ($DBversion);
1577 $DBversion = "3.00.00.086";
1578 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1580 "CREATE TABLE `tmp_holdsqueue` (
1581 `biblionumber` int(11) default NULL,
1582 `itemnumber` int(11) default NULL,
1583 `barcode` varchar(20) default NULL,
1584 `surname` mediumtext NOT NULL,
1587 `borrowernumber` int(11) NOT NULL,
1588 `cardnumber` varchar(16) default NULL,
1589 `reservedate` date default NULL,
1591 `itemcallnumber` varchar(30) default NULL,
1592 `holdingbranch` varchar(10) default NULL,
1593 `pickbranch` varchar(10) default NULL,
1595 ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
1597 $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')");
1598 $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')");
1600 print "Upgrade to $DBversion done (Table structure for table `tmp_holdsqueue`)\n";
1601 SetVersion ($DBversion);
1604 $DBversion = "3.00.00.087";
1605 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1606 $dbh->do("INSERT INTO `systempreferences` VALUES ('AutoEmailOpacUser','0','','Sends notification emails containing new account details to patrons - when account is created.','YesNo')" );
1607 $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')");
1608 print "Upgrade to $DBversion done (added 2 new 'AutoEmailOpacUser' sysprefs)\n";
1609 SetVersion ($DBversion);
1612 $DBversion = "3.00.00.088";
1613 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1614 $dbh->do("INSERT INTO `systempreferences` (variable,value,options,explanation,type) VALUES ('OPACShelfBrowser','1','','Enable/disable Shelf Browser on item details page','YesNo')");
1615 $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')");
1616 $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')");
1617 $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')");
1618 print "Upgrade to $DBversion done (added 2 new 'AutoEmailOpacUser' sysprefs)\n";
1619 SetVersion ($DBversion);
1622 $DBversion = "3.00.00.089";
1623 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1624 $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')");
1625 print "Upgrade to $DBversion done (added new AdvancedSearchTypes syspref)\n";
1626 SetVersion ($DBversion);
1629 $DBversion = "3.00.00.090";
1630 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1632 CREATE TABLE `branch_borrower_circ_rules` (
1633 `branchcode` VARCHAR(10) NOT NULL,
1634 `categorycode` VARCHAR(10) NOT NULL,
1635 `maxissueqty` int(4) default NULL,
1636 PRIMARY KEY (`categorycode`, `branchcode`),
1637 CONSTRAINT `branch_borrower_circ_rules_ibfk_1` FOREIGN KEY (`categorycode`) REFERENCES `categories` (`categorycode`)
1638 ON DELETE CASCADE ON UPDATE CASCADE,
1639 CONSTRAINT `branch_borrower_circ_rules_ibfk_2` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`)
1640 ON DELETE CASCADE ON UPDATE CASCADE
1641 ) ENGINE=InnoDB DEFAULT CHARSET=utf8
1644 CREATE TABLE `default_borrower_circ_rules` (
1645 `categorycode` VARCHAR(10) NOT NULL,
1646 `maxissueqty` int(4) default NULL,
1647 PRIMARY KEY (`categorycode`),
1648 CONSTRAINT `borrower_borrower_circ_rules_ibfk_1` FOREIGN KEY (`categorycode`) REFERENCES `categories` (`categorycode`)
1649 ON DELETE CASCADE ON UPDATE CASCADE
1650 ) ENGINE=InnoDB DEFAULT CHARSET=utf8
1653 CREATE TABLE `default_branch_circ_rules` (
1654 `branchcode` VARCHAR(10) NOT NULL,
1655 `maxissueqty` int(4) default NULL,
1656 PRIMARY KEY (`branchcode`),
1657 CONSTRAINT `default_branch_circ_rules_ibfk_1` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`)
1658 ON DELETE CASCADE ON UPDATE CASCADE
1659 ) ENGINE=InnoDB DEFAULT CHARSET=utf8
1662 CREATE TABLE `default_circ_rules` (
1663 `singleton` enum('singleton') NOT NULL default 'singleton',
1664 `maxissueqty` int(4) default NULL,
1665 PRIMARY KEY (`singleton`)
1666 ) ENGINE=InnoDB DEFAULT CHARSET=utf8
1668 print "Upgrade to $DBversion done (added several circ rules tables)\n";
1669 SetVersion ($DBversion);
1673 $DBversion = "3.00.00.091";
1674 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1675 $dbh->do(<<'END_SQL');
1676 ALTER TABLE borrowers
1677 ADD `smsalertnumber` varchar(50) default NULL
1680 $dbh->do(<<'END_SQL');
1681 CREATE TABLE `message_attributes` (
1682 `message_attribute_id` int(11) NOT NULL auto_increment,
1683 `message_name` varchar(20) NOT NULL default '',
1684 `takes_days` tinyint(1) NOT NULL default '0',
1685 PRIMARY KEY (`message_attribute_id`),
1686 UNIQUE KEY `message_name` (`message_name`)
1687 ) ENGINE=InnoDB DEFAULT CHARSET=utf8
1690 $dbh->do(<<'END_SQL');
1691 CREATE TABLE `message_transport_types` (
1692 `message_transport_type` varchar(20) NOT NULL,
1693 PRIMARY KEY (`message_transport_type`)
1694 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1697 $dbh->do(<<'END_SQL');
1698 CREATE TABLE `message_transports` (
1699 `message_attribute_id` int(11) NOT NULL,
1700 `message_transport_type` varchar(20) NOT NULL,
1701 `is_digest` tinyint(1) NOT NULL default '0',
1702 `letter_module` varchar(20) NOT NULL default '',
1703 `letter_code` varchar(20) NOT NULL default '',
1704 PRIMARY KEY (`message_attribute_id`,`message_transport_type`,`is_digest`),
1705 KEY `message_transport_type` (`message_transport_type`),
1706 KEY `letter_module` (`letter_module`,`letter_code`),
1707 CONSTRAINT `message_transports_ibfk_1` FOREIGN KEY (`message_attribute_id`) REFERENCES `message_attributes` (`message_attribute_id`) ON DELETE CASCADE ON UPDATE CASCADE,
1708 CONSTRAINT `message_transports_ibfk_2` FOREIGN KEY (`message_transport_type`) REFERENCES `message_transport_types` (`message_transport_type`) ON DELETE CASCADE ON UPDATE CASCADE,
1709 CONSTRAINT `message_transports_ibfk_3` FOREIGN KEY (`letter_module`, `letter_code`) REFERENCES `letter` (`module`, `code`) ON DELETE CASCADE ON UPDATE CASCADE
1710 ) ENGINE=InnoDB DEFAULT CHARSET=utf8
1713 $dbh->do(<<'END_SQL');
1714 CREATE TABLE `borrower_message_preferences` (
1715 `borrower_message_preference_id` int(11) NOT NULL auto_increment,
1716 `borrowernumber` int(11) NOT NULL default '0',
1717 `message_attribute_id` int(11) default '0',
1718 `days_in_advance` int(11) default '0',
1719 `wants_digets` tinyint(1) NOT NULL default '0',
1720 PRIMARY KEY (`borrower_message_preference_id`),
1721 KEY `borrowernumber` (`borrowernumber`),
1722 KEY `message_attribute_id` (`message_attribute_id`),
1723 CONSTRAINT `borrower_message_preferences_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1724 CONSTRAINT `borrower_message_preferences_ibfk_2` FOREIGN KEY (`message_attribute_id`) REFERENCES `message_attributes` (`message_attribute_id`) ON DELETE CASCADE ON UPDATE CASCADE
1725 ) ENGINE=InnoDB DEFAULT CHARSET=utf8
1728 $dbh->do(<<'END_SQL');
1729 CREATE TABLE `borrower_message_transport_preferences` (
1730 `borrower_message_preference_id` int(11) NOT NULL default '0',
1731 `message_transport_type` varchar(20) NOT NULL default '0',
1732 PRIMARY KEY (`borrower_message_preference_id`,`message_transport_type`),
1733 KEY `message_transport_type` (`message_transport_type`),
1734 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,
1735 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
1736 ) ENGINE=InnoDB DEFAULT CHARSET=utf8
1739 $dbh->do(<<'END_SQL');
1740 CREATE TABLE `message_queue` (
1741 `message_id` int(11) NOT NULL auto_increment,
1742 `borrowernumber` int(11) NOT NULL,
1745 `message_transport_type` varchar(20) NOT NULL,
1746 `status` enum('sent','pending','failed','deleted') NOT NULL default 'pending',
1747 `time_queued` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1748 KEY `message_id` (`message_id`),
1749 KEY `borrowernumber` (`borrowernumber`),
1750 KEY `message_transport_type` (`message_transport_type`),
1751 CONSTRAINT `messageq_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1752 CONSTRAINT `messageq_ibfk_2` FOREIGN KEY (`message_transport_type`) REFERENCES `message_transport_types` (`message_transport_type`) ON DELETE RESTRICT ON UPDATE CASCADE
1753 ) ENGINE=InnoDB DEFAULT CHARSET=utf8
1756 $dbh->do(<<'END_SQL');
1757 INSERT INTO `systempreferences`
1758 (variable,value,explanation,options,type)
1760 ('EnhancedMessagingPreferences',0,'If ON, allows patrons to select to receive additional messages about items due or nearly due.','','YesNo')
1763 $dbh->do( <<'END_SQL');
1764 INSERT INTO `letter`
1765 (module, code, name, title, content)
1767 ('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>>'),
1768 ('circulation','DUEDGST','Item Due Reminder (Digest)','Item Due Reminder','You have <<count>> items due'),
1769 ('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>>'),
1770 ('circulation','PREDUEDGST','Advance Notice of Item Due (Digest)','Advance Notice of Item Due','You have <<count>> items due soon'),
1771 ('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.');
1775 'installer/data/mysql/en/mandatory/message_transport_types.sql',
1776 'installer/data/mysql/en/optional/sample_notices_message_attributes.sql',
1777 'installer/data/mysql/en/optional/sample_notices_message_transports.sql',
1780 my $installer = C4::Installer->new();
1781 foreach my $script ( @sql_scripts ) {
1782 my $full_path = $installer->get_file_path_from_name($script);
1783 my $error = $installer->load_sql($full_path);
1784 warn $error if $error;
1787 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";
1788 SetVersion ($DBversion);
1791 $DBversion = "3.00.00.092";
1792 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1793 $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')");
1794 $dbh->do("INSERT INTO `systempreferences` (variable,value,options,explanation,type) VALUES('AllowHoldsOnDamagedItems', '1', '', 'Allow hold requests to be placed on damaged items', 'YesNo')");
1795 print "Upgrade to $DBversion done (added new AllowOnShelfHolds syspref)\n";
1796 SetVersion ($DBversion);
1799 $DBversion = "3.00.00.093";
1800 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1801 $dbh->do("ALTER TABLE `items` MODIFY COLUMN `copynumber` VARCHAR(32) DEFAULT NULL");
1802 $dbh->do("ALTER TABLE `deleteditems` MODIFY COLUMN `copynumber` VARCHAR(32) DEFAULT NULL");
1803 print "Upgrade to $DBversion done (Change data type of items.copynumber to allow free text)\n";
1804 SetVersion ($DBversion);
1807 $DBversion = "3.00.00.094";
1808 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1809 $dbh->do("ALTER TABLE `marc_subfield_structure` MODIFY `tagsubfield` VARCHAR(1) NOT NULL DEFAULT '' COLLATE utf8_bin");
1810 print "Upgrade to $DBversion done (Change Collation of marc_subfield_structure to allow mixed case in subfield labels.)\n";
1811 SetVersion ($DBversion);
1814 $DBversion = "3.00.00.095";
1815 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1816 if (C4::Context->preference("marcflavour") eq 'MARC21') {
1817 $dbh->do("UPDATE marc_subfield_structure SET authtypecode = 'MEETI_NAME' WHERE authtypecode = 'Meeting Name'");
1818 $dbh->do("UPDATE marc_subfield_structure SET authtypecode = 'CORPO_NAME' WHERE authtypecode = 'CORP0_NAME'");
1820 print "Upgrade to $DBversion done (fix invalid authority types in MARC21 frameworks [bug 2254])\n";
1821 SetVersion ($DBversion);
1824 $DBversion = "3.00.00.096";
1825 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1826 $sth = $dbh->prepare("SHOW COLUMNS FROM borrower_message_preferences LIKE 'wants_digets'");
1828 if (my $row = $sth->fetchrow_hashref) {
1829 $dbh->do("ALTER TABLE borrower_message_preferences CHANGE wants_digets wants_digest tinyint(1) NOT NULL default 0");
1831 print "Upgrade to $DBversion done (fix name borrower_message_preferences.wants_digest)\n";
1832 SetVersion ($DBversion);
1835 $DBversion = '3.00.00.097';
1836 if ( C4::Context->preference('Version') < TransformToNum($DBversion) ) {
1838 $dbh->do('ALTER TABLE message_queue ADD to_address mediumtext default NULL');
1839 $dbh->do('ALTER TABLE message_queue ADD from_address mediumtext default NULL');
1840 $dbh->do('ALTER TABLE message_queue ADD content_type text');
1841 $dbh->do('ALTER TABLE message_queue CHANGE borrowernumber borrowernumber int(11) default NULL');
1843 print "Upgrade to $DBversion done (updating 4 fields in message_queue table)\n";
1844 SetVersion($DBversion);
1847 $DBversion = '3.00.00.098';
1848 if ( C4::Context->preference('Version') < TransformToNum($DBversion) ) {
1850 $dbh->do(q(DELETE FROM message_transport_types WHERE message_transport_type = 'rss'));
1851 $dbh->do(q(DELETE FROM message_transports WHERE message_transport_type = 'rss'));
1853 print "Upgrade to $DBversion done (removing unused RSS message_transport_type)\n";
1854 SetVersion($DBversion);
1857 $DBversion = '3.00.00.099';
1858 if ( C4::Context->preference('Version') < TransformToNum($DBversion) ) {
1859 $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')");
1860 print "Upgrade to $DBversion done (Adding OpacSuppression syspref)\n";
1861 SetVersion($DBversion);
1864 $DBversion = '3.00.00.100';
1865 if ( C4::Context->preference('Version') < TransformToNum($DBversion) ) {
1866 $dbh->do('ALTER TABLE virtualshelves ADD COLUMN lastmodified timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP');
1867 print "Upgrade to $DBversion done (Adding lastmodified column to virtualshelves)\n";
1868 SetVersion($DBversion);
1871 $DBversion = '3.00.00.101';
1872 if ( C4::Context->preference('Version') < TransformToNum($DBversion) ) {
1873 $dbh->do('ALTER TABLE `overduerules` CHANGE `categorycode` `categorycode` VARCHAR(10) NOT NULL');
1874 $dbh->do('ALTER TABLE `deletedborrowers` CHANGE `categorycode` `categorycode` VARCHAR(10) NOT NULL');
1875 print "Upgrade to $DBversion done (Updating columnd definitions for patron category codes in notice/statsu triggers and deletedborrowers tables.)\n";
1876 SetVersion($DBversion);
1879 $DBversion = '3.00.00.102';
1880 if ( C4::Context->preference('Version') < TransformToNum($DBversion) ) {
1881 $dbh->do('ALTER TABLE serialitems MODIFY `serialid` int(11) NOT NULL AFTER itemnumber' );
1882 $dbh->do('ALTER TABLE serialitems DROP KEY serialididx' );
1883 $dbh->do('ALTER TABLE serialitems ADD CONSTRAINT UNIQUE KEY serialitemsidx (itemnumber)' );
1884 # before setting constraint, delete any unvalid data
1885 $dbh->do('DELETE from serialitems WHERE serialid not in (SELECT serial.serialid FROM serial)');
1886 $dbh->do('ALTER TABLE serialitems ADD CONSTRAINT serialitems_sfk_1 FOREIGN KEY (serialid) REFERENCES serial (serialid) ON DELETE CASCADE ON UPDATE CASCADE' );
1887 print "Upgrade to $DBversion done (Updating serialitems table to allow for multiple items per serial fixing kohabug 2380)\n";
1888 SetVersion($DBversion);
1891 $DBversion = "3.00.00.103";
1892 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1893 $dbh->do("DELETE FROM systempreferences WHERE variable='serialsadditems'");
1894 print "Upgrade to $DBversion done ( Verifying the removal of serialsadditems from syspref fixing kohabug 2219)\n";
1895 SetVersion ($DBversion);
1898 $DBversion = "3.00.00.104";
1899 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1900 $dbh->do("DELETE FROM systempreferences WHERE variable='noOPACHolds'");
1901 print "Upgrade to $DBversion done (remove superseded 'noOPACHolds' system preference per bug 2413)\n";
1902 SetVersion ($DBversion);
1905 $DBversion = '3.00.00.105';
1906 if ( C4::Context->preference("Version") < TransformToNum($DBversion) ) {
1908 # it is possible that this syspref is already defined since the feature was added some time ago.
1909 unless ( $dbh->do(q(SELECT variable FROM systempreferences WHERE variable = 'SMSSendDriver')) ) {
1910 $dbh->do(<<'END_SQL');
1911 INSERT INTO `systempreferences`
1912 (variable,value,explanation,options,type)
1914 ('SMSSendDriver','','Sets which SMS::Send driver is used to send SMS messages.','','free')
1917 print "Upgrade to $DBversion done (added SMSSendDriver system preference)\n";
1918 SetVersion($DBversion);
1921 $DBversion = "3.00.00.106";
1922 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1923 $dbh->do("DELETE FROM systempreferences WHERE variable='noOPACHolds'");
1925 # db revision 105 didn't apply correctly, so we're rolling this into 106
1926 $dbh->do("INSERT INTO `systempreferences`
1927 (variable,value,explanation,options,type)
1929 ('SMSSendDriver','','Sets which SMS::Send driver is used to send SMS messages.','','free')");
1931 print "Upgrade to $DBversion done (remove default '0000-00-00' in subscriptionhistory.enddate field)\n";
1932 $dbh->do("ALTER TABLE `subscriptionhistory` CHANGE `enddate` `enddate` DATE NULL DEFAULT NULL ");
1933 $dbh->do("UPDATE subscriptionhistory SET enddate=NULL WHERE enddate='0000-00-00'");
1934 SetVersion ($DBversion);
1937 $DBversion = '3.00.00.107';
1938 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1939 $dbh->do(<<'END_SQL');
1940 UPDATE systempreferences
1941 SET explanation = CONCAT( explanation, '. WARNING: this feature is very resource consuming on collections with large numbers of items.' )
1942 WHERE variable = 'OPACShelfBrowser'
1943 AND explanation NOT LIKE '%WARNING%'
1945 $dbh->do(<<'END_SQL');
1946 UPDATE systempreferences
1947 SET explanation = CONCAT( explanation, '. WARNING: this feature is very resource consuming.' )
1948 WHERE variable = 'CataloguingLog'
1949 AND explanation NOT LIKE '%WARNING%'
1951 $dbh->do(<<'END_SQL');
1952 UPDATE systempreferences
1953 SET explanation = CONCAT( explanation, '. WARNING: using NoZebra on even modest sized collections is very slow.' )
1954 WHERE variable = 'NoZebra'
1955 AND explanation NOT LIKE '%WARNING%'
1957 print "Upgrade to $DBversion done (warning added to OPACShelfBrowser system preference)\n";
1958 SetVersion ($DBversion);
1961 $DBversion = '3.01.00.000';
1962 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1963 print "Upgrade to $DBversion done (start of 3.1)\n";
1964 SetVersion ($DBversion);
1967 $DBversion = '3.01.00.001';
1968 if ( C4::Context->preference('Version') < TransformToNum($DBversion) ) {
1970 CREATE TABLE hold_fill_targets (
1971 `borrowernumber` int(11) NOT NULL,
1972 `biblionumber` int(11) NOT NULL,
1973 `itemnumber` int(11) NOT NULL,
1974 `source_branchcode` varchar(10) default NULL,
1975 `item_level_request` tinyint(4) NOT NULL default 0,
1976 PRIMARY KEY `itemnumber` (`itemnumber`),
1977 KEY `bib_branch` (`biblionumber`, `source_branchcode`),
1978 CONSTRAINT `hold_fill_targets_ibfk_1` FOREIGN KEY (`borrowernumber`)
1979 REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1980 CONSTRAINT `hold_fill_targets_ibfk_2` FOREIGN KEY (`biblionumber`)
1981 REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1982 CONSTRAINT `hold_fill_targets_ibfk_3` FOREIGN KEY (`itemnumber`)
1983 REFERENCES `items` (`itemnumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1984 CONSTRAINT `hold_fill_targets_ibfk_4` FOREIGN KEY (`source_branchcode`)
1985 REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE
1986 ) ENGINE=InnoDB DEFAULT CHARSET=utf8
1989 ALTER TABLE tmp_holdsqueue
1990 ADD item_level_request tinyint(4) NOT NULL default 0
1993 print "Upgrade to $DBversion done (add hold_fill_targets table and a column to tmp_holdsqueue)\n";
1994 SetVersion($DBversion);
1997 $DBversion = '3.01.00.002';
1998 if ( C4::Context->preference('Version') < TransformToNum($DBversion) ) {
1999 # use statistics where available
2001 ALTER TABLE statistics ADD KEY tmp_stats (type, itemnumber, borrowernumber)
2006 SELECT max(datetime)
2008 WHERE type = 'issue'
2009 AND itemnumber = iss.itemnumber
2010 AND borrowernumber = iss.borrowernumber
2012 WHERE issuedate IS NULL;
2014 $dbh->do("ALTER TABLE statistics DROP KEY tmp_stats");
2016 # default to last renewal date
2019 SET issuedate = lastreneweddate
2020 WHERE issuedate IS NULL
2021 and lastreneweddate IS NOT NULL
2024 my $num_bad_issuedates = $dbh->selectrow_array("SELECT COUNT(*) FROM issues WHERE issuedate IS NULL");
2025 if ($num_bad_issuedates > 0) {
2026 print STDERR "After the upgrade to $DBversion, there are still $num_bad_issuedates loan(s) with a NULL (blank) loan date. ",
2027 "Please check the issues table in your database.";
2029 print "Upgrade to $DBversion done (bug 2582: set null issues.issuedate to lastreneweddate)\n";
2030 SetVersion($DBversion);
2033 $DBversion = "3.01.00.003";
2034 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2035 $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')");
2036 print "Upgrade to $DBversion done (add new syspref)\n";
2037 SetVersion ($DBversion);
2040 $DBversion = '3.01.00.004';
2041 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2042 $dbh->do("INSERT INTO systempreferences (variable,value,explanation,options,type) VALUES ('OPACDisplayRequestPriority','0','Show patrons the priority level on holds in the OPAC','','YesNo')");
2043 print "Upgrade to $DBversion done (added OPACDisplayRequestPriority system preference)\n";
2044 SetVersion ($DBversion);
2047 $DBversion = '3.01.00.005';
2048 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2050 INSERT INTO `letter` (module, code, name, title, content)
2051 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>>')
2053 $dbh->do("INSERT INTO `message_attributes` (message_attribute_id, message_name, takes_days) values(4, 'Hold Filled', 0)");
2054 $dbh->do("INSERT INTO `message_transports` (message_attribute_id, message_transport_type, is_digest, letter_module, letter_code) values(4, 'sms', 0, 'reserves', 'HOLD')");
2055 $dbh->do("INSERT INTO `message_transports` (message_attribute_id, message_transport_type, is_digest, letter_module, letter_code) values(4, 'email', 0, 'reserves', 'HOLD')");
2056 print "Upgrade to $DBversion done (Add letter for holds notifications)\n";
2057 SetVersion ($DBversion);
2060 $DBversion = '3.01.00.006';
2061 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2062 $dbh->do("ALTER TABLE `biblioitems` ADD KEY issn (issn)");
2063 print "Upgrade to $DBversion done (add index on biblioitems.issn)\n";
2064 SetVersion ($DBversion);
2067 $DBversion = "3.01.00.007";
2068 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2069 $dbh->do("UPDATE `systempreferences` SET options='70|10' WHERE variable='intranetmainUserblock'");
2070 $dbh->do("UPDATE `systempreferences` SET options='70|10' WHERE variable='intranetuserjs'");
2071 $dbh->do("UPDATE `systempreferences` SET options='70|10' WHERE variable='opacheader'");
2072 $dbh->do("UPDATE `systempreferences` SET options='70|10' WHERE variable='OpacMainUserBlock'");
2073 $dbh->do("UPDATE `systempreferences` SET options='70|10' WHERE variable='OpacNav'");
2074 $dbh->do("UPDATE `systempreferences` SET options='70|10' WHERE variable='opacuserjs'");
2075 $dbh->do("UPDATE `systempreferences` SET options='30|10', type='Textarea' WHERE variable='OAI-PMH:Set'");
2076 $dbh->do("UPDATE `systempreferences` SET options='50' WHERE variable='intranetstylesheet'");
2077 $dbh->do("UPDATE `systempreferences` SET options='50' WHERE variable='intranetcolorstylesheet'");
2078 $dbh->do("UPDATE `systempreferences` SET options='10' WHERE variable='globalDueDate'");
2079 $dbh->do("UPDATE `systempreferences` SET type='Integer' WHERE variable='numSearchResults'");
2080 $dbh->do("UPDATE `systempreferences` SET type='Integer' WHERE variable='OPACnumSearchResults'");
2081 $dbh->do("UPDATE `systempreferences` SET type='Integer' WHERE variable='ReservesMaxPickupDelay'");
2082 $dbh->do("UPDATE `systempreferences` SET type='Integer' WHERE variable='TransfersMaxDaysWarning'");
2083 $dbh->do("UPDATE `systempreferences` SET type='Integer' WHERE variable='StaticHoldsQueueWeight'");
2084 $dbh->do("UPDATE `systempreferences` SET type='Integer' WHERE variable='holdCancelLength'");
2085 $dbh->do("UPDATE `systempreferences` SET type='Integer' WHERE variable='XISBNDailyLimit'");
2086 $dbh->do("UPDATE `systempreferences` SET type='Float' WHERE variable='gist'");
2087 $dbh->do("UPDATE `systempreferences` SET type='Free' WHERE variable='BakerTaylorUsername'");
2088 $dbh->do("UPDATE `systempreferences` SET type='Free' WHERE variable='BakerTaylorPassword'");
2089 $dbh->do("UPDATE `systempreferences` SET type='Textarea', options='70|10' WHERE variable='ISBD'");
2090 $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'");
2091 print "Upgrade to $DBversion done (fix display of many sysprefs)\n";
2092 SetVersion ($DBversion);
2095 $DBversion = '3.01.00.008';
2096 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2098 $dbh->do("CREATE TABLE branch_transfer_limits (
2099 limitId int(8) NOT NULL auto_increment,
2100 toBranch varchar(4) NOT NULL,
2101 fromBranch varchar(4) NOT NULL,
2102 itemtype varchar(4) NOT NULL,
2103 PRIMARY KEY (limitId)
2104 ) ENGINE=InnoDB DEFAULT CHARSET=utf8"
2107 $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')");
2109 print "Upgrade to $DBversion done (added branch_transfer_limits table and UseBranchTransferLimits system preference)\n";
2110 SetVersion ($DBversion);
2113 $DBversion = "3.01.00.009";
2114 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2115 $dbh->do("ALTER TABLE permissions MODIFY `code` varchar(64) DEFAULT NULL");
2116 $dbh->do("ALTER TABLE user_permissions MODIFY `code` varchar(64) DEFAULT NULL");
2117 $dbh->do("INSERT INTO permissions (module_bit, code, description) VALUES ( 1, 'circulate_remaining_permissions', 'Remaining circulation permissions')");
2118 $dbh->do("INSERT INTO permissions (module_bit, code, description) VALUES ( 1, 'override_renewals', 'Override blocked renewals')");
2119 print "Upgrade to $DBversion done (added subpermissions for circulate permission)\n";
2122 $DBversion = '3.01.00.010';
2123 if ( C4::Context->preference('Version') < TransformToNum($DBversion) ) {
2124 $dbh->do("ALTER TABLE `borrower_attributes` MODIFY COLUMN `attribute` VARCHAR(64) DEFAULT NULL");
2125 $dbh->do("ALTER TABLE `borrower_attributes` MODIFY COLUMN `password` VARCHAR(64) DEFAULT NULL");
2126 print "Upgrade to $DBversion done (bug 2687: increase length of borrower attribute fields)\n";
2127 SetVersion ($DBversion);
2130 $DBversion = '3.01.00.011';
2131 if ( C4::Context->preference('Version') < TransformToNum($DBversion) ) {
2133 # Yes, the old value was ^M terminated.
2134 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);";
2136 my $intranetuserjs = C4::Context->preference('intranetuserjs');
2137 if ($intranetuserjs and $intranetuserjs eq $bad_value) {
2138 my $sql = <<'END_SQL';
2139 UPDATE systempreferences
2141 WHERE variable = 'intranetuserjs'
2145 print "Upgrade to $DBversion done (removed bogus intranetuserjs syspref)\n";
2146 SetVersion($DBversion);
2149 $DBversion = "3.01.00.012";
2150 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2151 $dbh->do("INSERT INTO systempreferences (variable,value,explanation,options,type) VALUES('AllowHoldPolicyOverride', '0', 'Allow staff to override hold policies when placing holds',NULL,'YesNo')");
2153 CREATE TABLE `branch_item_rules` (
2154 `branchcode` varchar(10) NOT NULL,
2155 `itemtype` varchar(10) NOT NULL,
2156 `holdallowed` tinyint(1) default NULL,
2157 PRIMARY KEY (`itemtype`,`branchcode`),
2158 KEY `branch_item_rules_ibfk_2` (`branchcode`),
2159 CONSTRAINT `branch_item_rules_ibfk_1` FOREIGN KEY (`itemtype`) REFERENCES `itemtypes` (`itemtype`) ON DELETE CASCADE ON UPDATE CASCADE,
2160 CONSTRAINT `branch_item_rules_ibfk_2` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE
2161 ) ENGINE=InnoDB DEFAULT CHARSET=utf8
2164 CREATE TABLE `default_branch_item_rules` (
2165 `itemtype` varchar(10) NOT NULL,
2166 `holdallowed` tinyint(1) default NULL,
2167 PRIMARY KEY (`itemtype`),
2168 CONSTRAINT `default_branch_item_rules_ibfk_1` FOREIGN KEY (`itemtype`) REFERENCES `itemtypes` (`itemtype`) ON DELETE CASCADE ON UPDATE CASCADE
2169 ) ENGINE=InnoDB DEFAULT CHARSET=utf8
2172 ALTER TABLE default_branch_circ_rules
2173 ADD COLUMN holdallowed tinyint(1) NULL
2176 ALTER TABLE default_circ_rules
2177 ADD COLUMN holdallowed tinyint(1) NULL
2179 print "Upgrade to $DBversion done (Add tables and system preferences for holds policies)\n";
2180 SetVersion ($DBversion);
2183 $DBversion = '3.01.00.013';
2184 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2186 CREATE TABLE item_circulation_alert_preferences (
2187 id int(11) AUTO_INCREMENT,
2188 branchcode varchar(10) NOT NULL,
2189 categorycode varchar(10) NOT NULL,
2190 item_type varchar(10) NOT NULL,
2191 notification varchar(16) NOT NULL,
2193 KEY (branchcode, categorycode, item_type, notification)
2194 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2197 $dbh->do(q{ ALTER TABLE `message_queue` ADD metadata text DEFAULT NULL AFTER content; });
2198 $dbh->do(q{ ALTER TABLE `message_queue` ADD letter_code varchar(64) DEFAULT NULL AFTER metadata; });
2201 INSERT INTO `letter` (`module`, `code`, `name`, `title`, `content`) VALUES
2202 ('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.');
2205 INSERT INTO `letter` (`module`, `code`, `name`, `title`, `content`) VALUES
2206 ('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>>.');
2209 $dbh->do(q{INSERT INTO message_attributes (message_attribute_id, message_name, takes_days) VALUES (5, 'Item Check-in', 0);});
2210 $dbh->do(q{INSERT INTO message_attributes (message_attribute_id, message_name, takes_days) VALUES (6, 'Item Checkout', 0);});
2212 $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');});
2213 $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');});
2214 $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');});
2215 $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');});
2217 print "Upgrade to $DBversion done (data for Email Checkout Slips project)\n";
2218 SetVersion ($DBversion);
2221 $DBversion = "3.01.00.014";
2222 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2223 $dbh->do("ALTER TABLE `branch_transfer_limits` CHANGE `itemtype` `itemtype` VARCHAR( 4 ) CHARACTER SET utf8 COLLATE utf8_general_ci NULL");
2224 $dbh->do("ALTER TABLE `branch_transfer_limits` ADD `ccode` VARCHAR( 10 ) NULL ;");
2225 $dbh->do("INSERT INTO `systempreferences` ( `variable` , `value` , `options` , `explanation` , `type` )
2227 'BranchTransferLimitsType', 'ccode', 'itemtype|ccode', 'When using branch transfer limits, choose whether to limit by itemtype or collection code.', 'Choice'
2230 print "Upgrade to $DBversion done ( Updated table for Branch Transfer Limits)\n";
2231 SetVersion ($DBversion);
2234 $DBversion = '3.01.00.015';
2235 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2236 $dbh->do("INSERT INTO systempreferences (variable,value,explanation,options,type) VALUES ('SyndeticsClientCode', '0', 'Client Code for using Syndetics Solutions content','','free')");
2238 $dbh->do("INSERT INTO systempreferences (variable,value,explanation,options,type) VALUES ('SyndeticsEnabled', '0', 'Turn on Syndetics Enhanced Content','','YesNo')");
2240 $dbh->do("INSERT INTO systempreferences (variable,value,explanation,options,type) VALUES ('SyndeticsCoverImages', '0', 'Display Cover Images from Syndetics','','YesNo')");
2242 $dbh->do("INSERT INTO systempreferences (variable,value,explanation,options,type) VALUES ('SyndeticsTOC', '0', 'Display Table of Content information from Syndetics','','YesNo')");
2244 $dbh->do("INSERT INTO systempreferences (variable,value,explanation,options,type) VALUES ('SyndeticsSummary', '0', 'Display Summary Information from Syndetics','','YesNo')");
2246 $dbh->do("INSERT INTO systempreferences (variable,value,explanation,options,type) VALUES ('SyndeticsEditions', '0', 'Display Editions from Syndetics','','YesNo')");
2248 $dbh->do("INSERT INTO systempreferences (variable,value,explanation,options,type) VALUES ('SyndeticsExcerpt', '0', 'Display Excerpts and first chapters on OPAC from Syndetics','','YesNo')");
2250 $dbh->do("INSERT INTO systempreferences (variable,value,explanation,options,type) VALUES ('SyndeticsReviews', '0', 'Display Reviews on OPAC from Syndetics','','YesNo')");
2252 $dbh->do("INSERT INTO systempreferences (variable,value,explanation,options,type) VALUES ('SyndeticsAuthorNotes', '0', 'Display Notes about the Author on OPAC from Syndetics','','YesNo')");
2254 $dbh->do("INSERT INTO systempreferences (variable,value,explanation,options,type) VALUES ('SyndeticsAwards', '0', 'Display Awards on OPAC from Syndetics','','YesNo')");
2256 $dbh->do("INSERT INTO systempreferences (variable,value,explanation,options,type) VALUES ('SyndeticsSeries', '0', 'Display Series information on OPAC from Syndetics','','YesNo')");
2258 $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')");
2260 $dbh->do("INSERT INTO systempreferences (variable,value,explanation,options,type) VALUES ('OPACAmazonCoverImages', '0', 'Display cover images on OPAC from Amazon Web Services','','YesNo')");
2262 $dbh->do("INSERT INTO systempreferences (variable,value,explanation,options,type) VALUES ('AmazonCoverImages', '0', 'Display Cover Images in Staff Client from Amazon Web Services','','YesNo')");
2264 $dbh->do("UPDATE systempreferences SET variable='AmazonEnabled' WHERE variable = 'AmazonContent'");
2266 $dbh->do("UPDATE systempreferences SET variable='OPACAmazonEnabled' WHERE variable = 'OPACAmazonContent'");
2268 print "Upgrade to $DBversion done (added Syndetics Enhanced Content system preferences)\n";
2269 SetVersion ($DBversion);
2272 $DBversion = "3.01.00.016";
2273 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2274 $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')");
2275 print "Upgrade to $DBversion done (Added Babeltheque syspref)\n";
2276 SetVersion ($DBversion);
2279 $DBversion = "3.01.00.017";
2280 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2281 $dbh->do("ALTER TABLE `subscription` ADD `staffdisplaycount` VARCHAR(10) NULL;");
2282 $dbh->do("ALTER TABLE `subscription` ADD `opacdisplaycount` VARCHAR(10) NULL;");
2283 $dbh->do("INSERT INTO `systempreferences` ( `variable` , `value` , `options` , `explanation` , `type` )
2285 'StaffSerialIssueDisplayCount', '3', '', 'Number of serial issues to display per subscription in the Staff client', 'Integer'
2287 $dbh->do("INSERT INTO `systempreferences` ( `variable` , `value` , `options` , `explanation` , `type` )
2289 'OPACSerialIssueDisplayCount', '3', '', 'Number of serial issues to display per subscription in the OPAC', 'Integer'
2292 print "Upgrade to $DBversion done ( Updated table for Serials Display)\n";
2293 SetVersion ($DBversion);
2296 $DBversion = "3.01.00.018";
2297 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2298 $dbh->do("ALTER TABLE deletedborrowers ADD `smsalertnumber` varchar(50) default NULL");
2299 print "Upgrade to $DBversion done (added deletedborrowers.smsalertnumber, missed in 3.00.00.091)\n";
2300 SetVersion ($DBversion);
2303 $DBversion = "3.01.00.019";
2304 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2305 $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')");
2306 print "Upgrade to $DBversion done (adding OPACShowCheckoutName systempref)\n";
2307 SetVersion ($DBversion);
2310 $DBversion = "3.01.00.020";
2311 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2312 $dbh->do("INSERT INTO systempreferences (variable,value,explanation,options,type)VALUES('LibraryThingForLibrariesID','','See:http://librarything.com/forlibraries/','','free')");
2313 $dbh->do("INSERT INTO systempreferences (variable,value,explanation,options,type)VALUES('LibraryThingForLibrariesEnabled','0','Enable or Disable Library Thing for Libraries Features','','YesNo')");
2314 $dbh->do("INSERT INTO systempreferences (variable,value,explanation,options,type)VALUES('LibraryThingForLibrariesTabbedView','0','Put LibraryThingForLibraries Content in Tabs.','','YesNo')");
2315 print "Upgrade to $DBversion done (adding LibraryThing for Libraries sysprefs)\n";
2316 SetVersion ($DBversion);
2319 $DBversion = "3.01.00.021";
2320 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2321 my $enable_reviews = C4::Context->preference('OPACAmazonEnabled') ? '1' : '0';
2322 $dbh->do("INSERT INTO systempreferences (variable,value,explanation,options,type) VALUES ('OPACAmazonReviews', '$enable_reviews', 'Display Amazon readers reviews on OPAC','','YesNo')");
2323 print "Upgrade to $DBversion done (adding OPACAmazonReviews syspref)\n";
2324 SetVersion ($DBversion);
2327 $DBversion = '3.01.00.022';
2328 if ( C4::Context->preference('Version') < TransformToNum($DBversion) ) {
2329 $dbh->do("ALTER TABLE `labels_conf` MODIFY COLUMN `formatstring` mediumtext DEFAULT NULL");
2330 print "Upgrade to $DBversion done (bug 2945: increase size of labels_conf.formatstring)\n";
2331 SetVersion ($DBversion);
2334 $DBversion = '3.01.00.023';
2335 if ( C4::Context->preference('Version') < TransformToNum($DBversion) ) {
2336 $dbh->do("ALTER TABLE biblioitems MODIFY COLUMN isbn VARCHAR(30) DEFAULT NULL");
2337 $dbh->do("ALTER TABLE deletedbiblioitems MODIFY COLUMN isbn VARCHAR(30) DEFAULT NULL");
2338 $dbh->do("ALTER TABLE import_biblios MODIFY COLUMN isbn VARCHAR(30) DEFAULT NULL");
2339 $dbh->do("ALTER TABLE suggestions MODIFY COLUMN isbn VARCHAR(30) DEFAULT NULL");
2340 print "Upgrade to $DBversion done (bug 2765: increase width of isbn column in several tables)\n";
2341 SetVersion ($DBversion);
2344 $DBversion = "3.01.00.024";
2345 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2346 $dbh->do("ALTER TABLE labels MODIFY COLUMN batch_id int(10) NOT NULL default 1;");
2347 print "Upgrade to $DBversion done (change labels.batch_id from varchar to int)\n";
2348 SetVersion ($DBversion);
2351 $DBversion = '3.01.00.025';
2352 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2353 $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')");
2355 print "Upgrade to $DBversion done (added ceilingDueDate system preference)\n";
2356 SetVersion ($DBversion);
2359 $DBversion = '3.01.00.026';
2360 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2361 $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')");
2363 print "Upgrade to $DBversion done (added numReturnedItemsToShow system preference)\n";
2364 SetVersion ($DBversion);
2367 $DBversion = '3.01.00.027';
2368 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2369 $dbh->do("ALTER TABLE zebraqueue CHANGE `biblio_auth_number` `biblio_auth_number` bigint(20) unsigned NOT NULL default 0");
2370 print "Upgrade to $DBversion done (Increased size of zebraqueue biblio_auth_number to address bug 3148.)\n";
2371 SetVersion ($DBversion);
2374 $DBversion = '3.01.00.028';
2375 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2376 my $enable_reviews = C4::Context->preference('AmazonEnabled') ? '1' : '0';
2377 $dbh->do("INSERT INTO systempreferences (variable,value,explanation,options,type) VALUES ('AmazonReviews', '$enable_reviews', 'Display Amazon reviews on staff interface','','YesNo')");
2378 print "Upgrade to $DBversion done (added AmazonReviews)\n";
2379 SetVersion ($DBversion);
2382 $DBversion = '3.01.00.029';
2383 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2384 $dbh->do(q( UPDATE language_rfc4646_to_iso639
2385 SET iso639_2_code = 'spa'
2386 WHERE rfc4646_subtag = 'es'
2387 AND iso639_2_code = 'rus' )
2389 print "Upgrade to $DBversion done (fixed bug 2599: using Spanish search limit retrieves Russian results)\n";
2390 SetVersion ($DBversion);
2393 $DBversion = "3.01.00.030";
2394 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2395 $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')");
2396 print "Upgrade to $DBversion done (added AllowNotForLoanOverride system preference)\n";
2397 SetVersion ($DBversion);
2400 $DBversion = "3.01.00.031";
2401 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2402 $dbh->do("ALTER TABLE branch_transfer_limits
2403 MODIFY toBranch varchar(10) NOT NULL,
2404 MODIFY fromBranch varchar(10) NOT NULL,
2405 MODIFY itemtype varchar(10) NULL");
2406 print "Upgrade to $DBversion done (fix column widths in branch_transfer_limits)\n";
2407 SetVersion ($DBversion);
2410 $DBversion = "3.01.00.032";
2411 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2412 $dbh->do(<<ENDOFRENEWAL);
2413 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');
2415 print "Upgrade to $DBversion done (Change the field)\n";
2416 SetVersion ($DBversion);
2419 $DBversion = "3.01.00.033";
2420 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2422 ALTER TABLE borrower_message_preferences
2423 MODIFY borrowernumber int(11) default NULL,
2424 ADD categorycode varchar(10) default NULL AFTER borrowernumber,
2425 ADD KEY `categorycode` (`categorycode`),
2426 ADD CONSTRAINT `borrower_message_preferences_ibfk_3`
2427 FOREIGN KEY (`categorycode`) REFERENCES `categories` (`categorycode`)
2428 ON DELETE CASCADE ON UPDATE CASCADE
2430 print "Upgrade to $DBversion done (DB changes to allow patron category defaults for messaging preferences)\n";
2431 SetVersion ($DBversion);
2434 $DBversion = "3.01.00.034";
2435 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2436 $dbh->do("ALTER TABLE `subscription` ADD COLUMN `graceperiod` INT(11) NOT NULL default '0';");
2437 print "Upgrade to $DBversion done (Adding graceperiod column to subscription table)\n";
2438 SetVersion ($DBversion);
2441 $DBversion = '3.01.00.035';
2442 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2443 $dbh->do(q{ ALTER TABLE `subscription` ADD location varchar(80) NULL DEFAULT '' AFTER callnumber; });
2444 print "Upgrade to $DBversion done (Adding location to subscription table)\n";
2445 SetVersion ($DBversion);
2448 $DBversion = '3.01.00.036';
2449 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2450 $dbh->do("UPDATE systempreferences SET explanation = 'Choose the default detail view in the staff interface; choose between normal, labeled_marc, marc or isbd'
2451 WHERE variable = 'IntranetBiblioDefaultView'
2452 AND explanation = 'IntranetBiblioDefaultView'");
2453 $dbh->do("UPDATE systempreferences SET type = 'Choice', options = 'normal|marc|isbd|labeled_marc'
2454 WHERE variable = 'IntranetBiblioDefaultView'");
2455 $dbh->do("INSERT INTO systempreferences (variable,value,explanation,options,type)VALUES('viewISBD','1','Allow display of ISBD view of bibiographic records','','YesNo')");
2456 $dbh->do("INSERT INTO systempreferences (variable,value,explanation,options,type)VALUES('viewLabeledMARC','0','Allow display of labeled MARC view of bibiographic records','','YesNo')");
2457 $dbh->do("INSERT INTO systempreferences (variable,value,explanation,options,type)VALUES('viewMARC','1','Allow display of MARC view of bibiographic records','','YesNo')");
2458 print "Upgrade to $DBversion done (new viewISBD, viewLabeledMARC, viewMARC sysprefs and tweak IntranetBiblioDefaultView)\n";
2459 SetVersion ($DBversion);
2462 $DBversion = '3.01.00.037';
2463 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2464 $dbh->do('ALTER TABLE authorised_values ADD KEY `lib` (`lib`)');
2465 $dbh->do("INSERT INTO systempreferences (variable,value,explanation,options,type)VALUES('FilterBeforeOverdueReport','0','Do not run overdue report until filter selected','','YesNo')");
2466 SetVersion ($DBversion);
2467 print "Upgrade to $DBversion done (added FilterBeforeOverdueReport syspref and new index on authorised_values)\n";
2470 $DBversion = "3.01.00.038";
2471 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2472 # update branches table
2474 $dbh->do("ALTER TABLE branches ADD `branchzip` varchar(25) default NULL AFTER `branchaddress3`");
2475 $dbh->do("ALTER TABLE branches ADD `branchcity` mediumtext AFTER `branchzip`");
2476 $dbh->do("ALTER TABLE branches ADD `branchcountry` text AFTER `branchcity`");
2477 $dbh->do("ALTER TABLE branches ADD `branchurl` mediumtext AFTER `branchemail`");
2478 $dbh->do("ALTER TABLE branches ADD `branchnotes` mediumtext AFTER `branchprinter`");
2479 print "Upgrade to $DBversion done (add ZIP, city, country, URL, and notes column to branches)\n";
2480 SetVersion ($DBversion);
2483 $DBversion = '3.01.00.039';
2484 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2485 $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')");
2486 $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')");
2487 SetVersion ($DBversion);
2488 print "Upgrade to $DBversion done (added SpineLabelFormat and SpineLabelAutoPrint sysprefs)\n";
2491 $DBversion = '3.01.00.040';
2492 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2493 $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')");
2494 $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')");
2495 SetVersion ($DBversion);
2496 print "Upgrade to $DBversion done (AllowHoldDateInFuture and OPACAllowHoldDateInFuture sysprefs)\n";
2499 $DBversion = '3.01.00.041';
2500 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2501 $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')");
2502 SetVersion ($DBversion);
2503 print "Upgrade to $DBversion done (added AWSPrivateKey syspref - note that if you use enhanced content from Amazon, this should be set right away.)\n";
2506 $DBversion = '3.01.00.042';
2507 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2508 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('OPACFineNoRenewals','99999','Fine Limit above which user canmot renew books via OPAC','','Integer')");
2509 SetVersion ($DBversion);
2510 print "Upgrade to $DBversion done (added OPACFineNoRenewals syspref)\n";
2513 $DBversion = '3.01.00.043';
2514 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2515 $dbh->do('ALTER TABLE items ADD COLUMN permanent_location VARCHAR(80) DEFAULT NULL AFTER location');
2516 $dbh->do('UPDATE items SET permanent_location = location');
2517 $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 )', '')");
2518 $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')");
2519 $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')");
2520 SetVersion ($DBversion);
2521 print "Upgrade to $DBversion done (amended Item added NewItemsDefaultLocation, InProcessingToShelvingCart, ReturnToShelvingCart sysprefs)\n";
2524 $DBversion = '3.01.00.044';
2525 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2526 $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')");
2527 SetVersion ($DBversion);
2528 print "Upgrade to $DBversion done (added DisplayClearScreenButton system preference)\n";
2531 $DBversion = '3.01.00.045';
2532 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2533 $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')");
2534 SetVersion ($DBversion);
2535 print "Upgrade to $DBversion done (added a preference to hide the patrons name in the staff catalog)";
2538 $DBversion = "3.01.00.046";
2539 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2540 # update borrowers table
2542 $dbh->do("ALTER TABLE borrowers ADD `country` text AFTER zipcode");
2543 $dbh->do("ALTER TABLE borrowers ADD `B_country` text AFTER B_zipcode");
2544 $dbh->do("ALTER TABLE deletedborrowers ADD `country` text AFTER zipcode");
2545 $dbh->do("ALTER TABLE deletedborrowers ADD `B_country` text AFTER B_zipcode");
2546 print "Upgrade to $DBversion done (add country and B_country to borrowers)\n";
2547 SetVersion ($DBversion);
2550 $DBversion = '3.01.00.047';
2551 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2552 $dbh->do("ALTER TABLE items MODIFY itemcallnumber varchar(255);");
2553 $dbh->do("ALTER TABLE deleteditems MODIFY itemcallnumber varchar(255);");
2554 $dbh->do("ALTER TABLE tmp_holdsqueue MODIFY itemcallnumber varchar(255);");
2555 SetVersion ($DBversion);
2556 print " Upgrade to $DBversion done (bug 2761: change max length of itemcallnumber to 255 from 30)\n";
2559 $DBversion = '3.01.00.048';
2560 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2561 $dbh->do("UPDATE userflags SET flagdesc='View Catalog (Librarian Interface)' WHERE bit=2;");
2562 $dbh->do("UPDATE userflags SET flagdesc='Edit Catalog (Modify bibliographic/holdings data)' WHERE bit=9;");
2563 $dbh->do("UPDATE userflags SET flagdesc='Allow to edit authorities' WHERE bit=14;");
2564 $dbh->do("UPDATE userflags SET flagdesc='Allow to access to the reports module' WHERE bit=16;");
2565 $dbh->do("UPDATE userflags SET flagdesc='Allow to manage serials subscriptions' WHERE bit=15;");
2566 SetVersion ($DBversion);
2567 print " Upgrade to $DBversion done (bug 2611: fix spelling/capitalization in permission flag descriptions)\n";
2570 $DBversion = '3.01.00.049';
2571 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2572 $dbh->do("UPDATE permissions SET description = 'Perform inventory (stocktaking) of your catalog' WHERE code = 'inventory';");
2573 SetVersion ($DBversion);
2574 print "Upgrade to $DBversion done (bug 2611: changed catalogue to catalog per the standard)\n";
2577 $DBversion = '3.01.00.050';
2578 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2579 $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');");
2580 SetVersion ($DBversion);
2581 print "Upgrade to $DBversion done (bug 1934: Add OPACSearchForTitleIn syspref)\n";
2584 $DBversion = '3.01.00.051';
2585 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2586 $dbh->do("UPDATE systempreferences SET explanation='Fine limit above which user cannot renew books via OPAC' WHERE variable='OPACFineNoRenewals';");
2587 $dbh->do("UPDATE systempreferences SET explanation='If set to ON, a clear screen button will appear on the circulation page.' WHERE variable='DisplayClearScreenButton';");
2588 SetVersion ($DBversion);
2589 print "Upgrade to $DBversion done (fixed typos in new sysprefs)\n";
2592 $DBversion = '3.01.00.052';
2593 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2594 $dbh->do('ALTER TABLE deleteditems ADD COLUMN permanent_location VARCHAR(80) DEFAULT NULL AFTER location');
2595 SetVersion ($DBversion);
2596 print "Upgrade to $DBversion done (bug 3481: add permanent_location column to deleteditems)\n";
2599 $DBversion = '3.01.00.053';
2600 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2601 my $upgrade_script = C4::Context->config("intranetdir") . "/installer/data/mysql/labels_upgrade.pl";
2602 system("perl $upgrade_script");
2603 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";
2604 SetVersion ($DBversion);
2607 $DBversion = '3.01.00.054';
2608 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2609 $dbh->do("ALTER TABLE borrowers ADD `B_address2` text AFTER B_address");
2610 $dbh->do("ALTER TABLE borrowers ADD `altcontactcountry` text AFTER altcontactzipcode");
2611 $dbh->do("ALTER TABLE deletedborrowers ADD `B_address2` text AFTER B_address");
2612 $dbh->do("ALTER TABLE deletedborrowers ADD `altcontactcountry` text AFTER altcontactzipcode");
2613 SetVersion ($DBversion);
2614 print "Upgrade to $DBversion done (bug 1600, bug 3454: add altcontactcountry and B_address2 to borrowers and deletedborrowers)\n";
2617 $DBversion = '3.01.00.055';
2618 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2619 $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'|);
2620 SetVersion ($DBversion);
2621 print "Upgrade to $DBversion done (changed OPACSearchForTitleIn per requests in bug 1934)\n";
2624 $DBversion = '3.01.00.056';
2625 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2626 $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');");
2627 SetVersion ($DBversion);
2628 print "Upgrade to $DBversion done (Bug 1172 : Add OPACPatronDetails syspref)\n";
2631 $DBversion = '3.01.00.057';
2632 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2633 $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');");
2634 SetVersion ($DBversion);
2635 print "Upgrade to $DBversion done (Bug 2576 : Add OPACFinesTab syspref)\n";
2638 $DBversion = '3.01.00.058';
2639 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2640 $dbh->do("ALTER TABLE `language_subtag_registry` ADD `id` INT( 11 ) NOT NULL AUTO_INCREMENT PRIMARY KEY;");
2641 $dbh->do("ALTER TABLE `language_rfc4646_to_iso639` ADD `id` INT( 11 ) NOT NULL AUTO_INCREMENT PRIMARY KEY;");
2642 $dbh->do("ALTER TABLE `language_descriptions` ADD `id` INT( 11 ) NOT NULL AUTO_INCREMENT PRIMARY KEY;");
2643 SetVersion ($DBversion);
2644 print "Upgrade to $DBversion done (Added primary keys to language tables)\n";
2647 $DBversion = '3.01.00.059';
2648 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2649 $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')");
2650 SetVersion ($DBversion);
2651 print "Upgrade to $DBversion done (added DisplayOPACiconsXSLT sysprefs)\n";
2654 $DBversion = '3.01.00.060';
2655 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2656 $dbh->do("INSERT INTO systempreferences (variable,value,explanation,options,type) VALUES('AllowAllMessageDeletion','0','Allow any Library to delete any message','','YesNo');");
2657 $dbh->do('DROP TABLE IF EXISTS messages');
2658 $dbh->do("CREATE TABLE messages ( `message_id` int(11) NOT NULL auto_increment,
2659 `borrowernumber` int(11) NOT NULL,
2660 `branchcode` varchar(4) default NULL,
2661 `message_type` varchar(1) NOT NULL,
2662 `message` text NOT NULL,
2663 `message_date` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
2664 PRIMARY KEY (`message_id`)
2665 ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
2667 print "Upgrade to $DBversion done ( Added AllowAllMessageDeletion syspref and messages table )\n";
2668 SetVersion ($DBversion);
2671 $DBversion = '3.01.00.061';
2672 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2673 $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')");
2674 print "Upgrade to $DBversion done ( Added ShowPatronImageInWebBasedSelfCheck system preference )\n";
2675 SetVersion ($DBversion);
2678 $DBversion = "3.01.00.062";
2679 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2680 $dbh->do("INSERT INTO permissions (module_bit, code, description) VALUES ( 13, 'manage_csv_profiles', 'Manage CSV export profiles')");
2682 CREATE TABLE `export_format` (
2683 `export_format_id` int(11) NOT NULL auto_increment,
2684 `profile` varchar(255) NOT NULL,
2685 `description` mediumtext NOT NULL,
2686 `marcfields` mediumtext NOT NULL,
2687 PRIMARY KEY (`export_format_id`)
2688 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Used for CSV export';
2690 print "Upgrade to $DBversion done (added csv export profiles)\n";
2693 $DBversion = "3.01.00.063";
2694 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2696 CREATE TABLE `fieldmapping` (
2697 `id` int(11) NOT NULL auto_increment,
2698 `field` varchar(255) NOT NULL,
2699 `frameworkcode` char(4) NOT NULL default '',
2700 `fieldcode` char(3) NOT NULL,
2701 `subfieldcode` char(1) NOT NULL,
2703 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2705 SetVersion ($DBversion);
2708 $DBversion = '3.01.00.065';
2709 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2710 $dbh->do('ALTER TABLE issuingrules ADD COLUMN `renewalsallowed` smallint(6) NOT NULL default "0" AFTER `issuelength`;');
2711 $sth = $dbh->prepare("SELECT itemtype, renewalsallowed FROM itemtypes");
2714 my $sthupd = $dbh->prepare("UPDATE issuingrules SET renewalsallowed = ? WHERE itemtype = ?");
2716 while(my $row = $sth->fetchrow_hashref){
2717 $sthupd->execute($row->{renewalsallowed}, $row->{itemtype});
2720 $dbh->do('ALTER TABLE itemtypes DROP COLUMN `renewalsallowed`;');
2722 SetVersion ($DBversion);
2723 print "Upgrade to $DBversion done (Moving allowed renewals from itemtypes to issuingrule)\n";
2726 $DBversion = '3.01.00.066';
2727 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2728 $dbh->do('ALTER TABLE issuingrules ADD COLUMN `reservesallowed` smallint(6) NOT NULL default "0" AFTER `renewalsallowed`;');
2730 my $maxreserves = C4::Context->preference('maxreserves');
2731 $sth = $dbh->prepare('UPDATE issuingrules SET reservesallowed = ?;');
2732 $sth->execute($maxreserves);
2734 $dbh->do('DELETE FROM systempreferences WHERE variable = "maxreserves";');
2736 $dbh->do("INSERT INTO systempreferences (variable,value, options, explanation, type) VALUES('ReservesControlBranch','PatronLibrary','ItemHomeLibrary|PatronLibrary','Branch checked for members reservations rights','Choice')");
2738 SetVersion ($DBversion);
2739 print "Upgrade to $DBversion done (Moving max allowed reserves from system preference to issuingrule)\n";
2742 $DBversion = "3.01.00.067";
2743 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2744 $dbh->do("INSERT INTO permissions (module_bit, code, description) VALUES ( 13, 'batchmod', 'Perform batch modification of items')");
2745 $dbh->do("INSERT INTO permissions (module_bit, code, description) VALUES ( 13, 'batchdel', 'Perform batch deletion of items')");
2746 print "Upgrade to $DBversion done (added permissions for batch modification and deletion)\n";
2747 SetVersion ($DBversion);
2750 $DBversion = "3.01.00.068";
2751 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2752 $dbh->do("ALTER TABLE issuingrules ADD COLUMN `finedays` int(11) default NULL AFTER `fine` ");
2753 print "Upgrade done (Adding finedays in issuingrules table)\n";
2754 SetVersion ($DBversion);
2758 $DBversion = "3.01.00.069";
2759 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2760 $dbh->do("INSERT INTO `systempreferences` (`variable`, `value`, `options`, `explanation`, `type`) VALUES ('EnableOpacSearchHistory', '1', '', 'Enable or disable opac search history', 'YesNo')");
2762 my $create = <<SEARCHHIST;
2763 CREATE TABLE IF NOT EXISTS `search_history` (
2764 `userid` int(11) NOT NULL,
2765 `sessionid` varchar(32) NOT NULL,
2766 `query_desc` varchar(255) NOT NULL,
2767 `query_cgi` varchar(255) NOT NULL,
2768 `total` int(11) NOT NULL,
2769 `time` timestamp NOT NULL default CURRENT_TIMESTAMP,
2770 KEY `userid` (`userid`),
2771 KEY `sessionid` (`sessionid`)
2772 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Opac search history results';
2776 print "Upgrade done (added OPAC search history preference and table)\n";
2779 $DBversion = "3.01.00.070";
2780 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2781 $dbh->do("ALTER TABLE authorised_values ADD COLUMN `lib_opac` VARCHAR(80) default NULL AFTER `lib`");
2782 print "Upgrade done (Added a lib_opac field in authorised_values table)\n";
2785 $DBversion = "3.01.00.071";
2786 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2787 $dbh->do("ALTER TABLE `subscription` ADD `enddate` date default NULL");
2788 $dbh->do("ALTER TABLE subscriptionhistory CHANGE enddate histenddate DATE default NULL");
2789 print "Upgrade to $DBversion done ( Adding enddate to subscription)\n";
2798 $DBversion = "3.01.00.072";
2799 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2800 $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')");
2801 # create a new syspref for the 'Mr anonymous' patron
2802 $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,'')");
2803 # fill AnonymousPatron with AnonymousSuggestion value (copy)
2804 my $sth=$dbh->prepare("SELECT value FROM systempreferences WHERE variable='AnonSuggestions'");
2806 my ($value) = $sth->fetchrow() || 0;
2807 $dbh->do("UPDATE systempreferences SET value='$value' WHERE variable='AnonymousPatron'");
2808 # set AnonymousSuggestion do YesNo
2809 # 1st, set the value (1/True if it had a borrowernumber)
2810 $dbh->do("UPDATE systempreferences SET value=1 WHERE variable='AnonSuggestions' AND value>0");
2811 # 2nd, change the type to Choice
2812 $dbh->do("UPDATE systempreferences SET type='YesNo' WHERE variable='AnonSuggestions'");
2813 # borrower reading record privacy : 0 : forever, 1 : laws, 2 : don't keep at all
2814 $dbh->do("ALTER TABLE `borrowers` ADD `privacy` INTEGER NOT NULL DEFAULT 1;");
2815 print "Upgrade to $DBversion done (add new syspref and column in borrowers)\n";
2816 SetVersion ($DBversion);
2819 $DBversion = '3.01.00.073';
2820 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2821 $dbh->do('SET FOREIGN_KEY_CHECKS=0 ');
2822 $dbh->do(<<'END_SQL');
2823 CREATE TABLE IF NOT EXISTS `aqcontract` (
2824 `contractnumber` int(11) NOT NULL auto_increment,
2825 `contractstartdate` date default NULL,
2826 `contractenddate` date default NULL,
2827 `contractname` varchar(50) default NULL,
2828 `contractdescription` mediumtext,
2829 `booksellerid` int(11) not NULL,
2830 PRIMARY KEY (`contractnumber`),
2831 CONSTRAINT `booksellerid_fk1` FOREIGN KEY (`booksellerid`)
2832 REFERENCES `aqbooksellers` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
2833 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
2835 $dbh->do('SET FOREIGN_KEY_CHECKS=1 ');
2836 print "Upgrade to $DBversion done (adding aqcontract table)\n";
2837 SetVersion ($DBversion);
2840 $DBversion = '3.01.00.074';
2841 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2842 $dbh->do("ALTER TABLE `aqbasket` ADD COLUMN `basketname` varchar(50) default NULL AFTER `basketno`");
2843 $dbh->do("ALTER TABLE `aqbasket` ADD COLUMN `note` mediumtext AFTER `basketname`");
2844 $dbh->do("ALTER TABLE `aqbasket` ADD COLUMN `booksellernote` mediumtext AFTER `note`");
2845 $dbh->do("ALTER TABLE `aqbasket` ADD COLUMN `contractnumber` int(11) AFTER `booksellernote`");
2846 $dbh->do("ALTER TABLE `aqbasket` ADD FOREIGN KEY (`contractnumber`) REFERENCES `aqcontract` (`contractnumber`)");
2847 print "Upgrade to $DBversion done (edit aqbasket table done)\n";
2848 SetVersion ($DBversion);
2851 $DBversion = '3.01.00.075';
2852 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2853 $dbh->do("ALTER TABLE `aqorders` ADD COLUMN `uncertainprice` tinyint(1)");
2855 print "Upgrade to $DBversion done (adding uncertainprices)\n";
2856 SetVersion ($DBversion);
2859 $DBversion = '3.01.00.076';
2860 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2861 $dbh->do('SET FOREIGN_KEY_CHECKS=0 ');
2862 $dbh->do("CREATE TABLE IF NOT EXISTS `aqbasketgroups` (
2863 `id` int(11) NOT NULL auto_increment,
2864 `name` varchar(50) default NULL,
2865 `closed` tinyint(1) default NULL,
2866 `booksellerid` int(11) NOT NULL,
2868 KEY `booksellerid` (`booksellerid`),
2869 CONSTRAINT `aqbasketgroups_ibfk_1` FOREIGN KEY (`booksellerid`) REFERENCES `aqbooksellers` (`id`) ON UPDATE CASCADE ON DELETE CASCADE
2870 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;");
2871 $dbh->do("ALTER TABLE aqbasket ADD COLUMN `basketgroupid` int(11)");
2872 $dbh->do("ALTER TABLE aqbasket ADD FOREIGN KEY (`basketgroupid`) REFERENCES `aqbasketgroups` (`id`) ON UPDATE CASCADE ON DELETE SET NULL");
2873 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES ('pdfformat','pdfformat::layout2pages','Controls what script is used for printing (basketgroups)','','free')");
2874 $dbh->do('SET FOREIGN_KEY_CHECKS=1 ');
2875 print "Upgrade to $DBversion done (adding basketgroups)\n";
2876 SetVersion ($DBversion);
2878 $DBversion = '3.01.00.077';
2879 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2881 $dbh->do("SET FOREIGN_KEY_CHECKS=0 ");
2882 # create a mapping table holding the info we need to match orders to budgets
2883 $dbh->do('DROP TABLE IF EXISTS fundmapping');
2885 q|CREATE TABLE fundmapping AS
2886 SELECT aqorderbreakdown.ordernumber, branchcode, bookfundid, budgetdate, entrydate
2887 FROM aqorderbreakdown JOIN aqorders ON aqorderbreakdown.ordernumber = aqorders.ordernumber|);
2888 # match the new type of the corresponding field
2889 $dbh->do('ALTER TABLE fundmapping modify column bookfundid varchar(30)');
2890 # System did not ensure budgetdate was valid historically
2891 $dbh->do(q|UPDATE fundmapping SET budgetdate = entrydate WHERE budgetdate = '0000-00-00' OR budgetdate IS NULL|);
2892 # We save the map in fundmapping in case you need later processing
2893 $dbh->do(q|ALTER TABLE fundmapping add column aqbudgetid integer|);
2894 # these can speed processing up
2895 $dbh->do(q|CREATE INDEX fundmaporder ON fundmapping (ordernumber)|);
2896 $dbh->do(q|CREATE INDEX fundmapid ON fundmapping (bookfundid)|);
2898 $dbh->do("DROP TABLE IF EXISTS `aqbudgetperiods` ");
2901 CREATE TABLE `aqbudgetperiods` (
2902 `budget_period_id` int(11) NOT NULL auto_increment,
2903 `budget_period_startdate` date NOT NULL,
2904 `budget_period_enddate` date NOT NULL,
2905 `budget_period_active` tinyint(1) default '0',
2906 `budget_period_description` mediumtext,
2907 `budget_period_locked` tinyint(1) default NULL,
2908 `sort1_authcat` varchar(10) default NULL,
2909 `sort2_authcat` varchar(10) default NULL,
2910 PRIMARY KEY (`budget_period_id`)
2911 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 |);
2913 $dbh->do(<<ADDPERIODS);
2914 INSERT INTO aqbudgetperiods (budget_period_startdate,budget_period_enddate,budget_period_active,budget_period_description,budget_period_locked)
2915 SELECT DISTINCT startdate, enddate, NOW() BETWEEN startdate and enddate, concat(startdate," ",enddate),NOT NOW() BETWEEN startdate AND enddate from aqbudget
2917 # SORRY , NO AQBUDGET/AQBOOKFUND -> AQBUDGETS IMPORT JUST YET,
2918 # BUT A NEW CLEAN AQBUDGETS TABLE CREATE FOR NOW..
2919 # DROP TABLE IF EXISTS `aqbudget`;
2920 #CREATE TABLE `aqbudget` (
2921 # `bookfundid` varchar(10) NOT NULL default ',
2922 # `startdate` date NOT NULL default 0,
2923 # `enddate` date default NULL,
2924 # `budgetamount` decimal(13,2) default NULL,
2925 # `aqbudgetid` tinyint(4) NOT NULL auto_increment,
2926 # `branchcode` varchar(10) default NULL,
2927 DropAllForeignKeys('aqbudget');
2928 #$dbh->do("drop table aqbudget;");
2931 my $maxbudgetid = $dbh->selectcol_arrayref(<<IDsBUDGET);
2932 SELECT MAX(aqbudgetid) from aqbudget
2935 $$maxbudgetid[0] = 0 if !$$maxbudgetid[0];
2937 $dbh->do(<<BUDGETAUTOINCREMENT);
2938 ALTER TABLE aqbudget AUTO_INCREMENT=$$maxbudgetid[0]
2941 $dbh->do(<<BUDGETNAME);
2942 ALTER TABLE aqbudget RENAME `aqbudgets`
2945 $dbh->do(<<BUDGETS);
2946 ALTER TABLE `aqbudgets`
2947 CHANGE COLUMN aqbudgetid `budget_id` int(11) NOT NULL AUTO_INCREMENT,
2948 CHANGE COLUMN branchcode `budget_branchcode` varchar(10) default NULL,
2949 CHANGE COLUMN budgetamount `budget_amount` decimal(28,6) NOT NULL default '0.00',
2950 CHANGE COLUMN bookfundid `budget_code` varchar(30) default NULL,
2951 ADD COLUMN `budget_parent_id` int(11) default NULL,
2952 ADD COLUMN `budget_name` varchar(80) default NULL,
2953 ADD COLUMN `budget_encumb` decimal(28,6) default '0.00',
2954 ADD COLUMN `budget_expend` decimal(28,6) default '0.00',
2955 ADD COLUMN `budget_notes` mediumtext,
2956 ADD COLUMN `budget_description` mediumtext,
2957 ADD COLUMN `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
2958 ADD COLUMN `budget_amount_sublevel` decimal(28,6) AFTER `budget_amount`,
2959 ADD COLUMN `budget_period_id` int(11) default NULL,
2960 ADD COLUMN `sort1_authcat` varchar(80) default NULL,
2961 ADD COLUMN `sort2_authcat` varchar(80) default NULL,
2962 ADD COLUMN `budget_owner_id` int(11) default NULL,
2963 ADD COLUMN `budget_permission` int(1) default '0';
2966 $dbh->do(<<BUDGETCONSTRAINTS);
2967 ALTER TABLE `aqbudgets`
2968 ADD CONSTRAINT `aqbudgets_ifbk_1` FOREIGN KEY (`budget_period_id`) REFERENCES `aqbudgetperiods` (`budget_period_id`) ON DELETE CASCADE ON UPDATE CASCADE
2970 # $dbh->do(<<BUDGETPKDROP);
2971 #ALTER TABLE `aqbudgets`
2974 # $dbh->do(<<BUDGETPKADD);
2975 #ALTER TABLE `aqbudgets`
2976 # ADD PRIMARY KEY budget_id
2980 my $query_period= $dbh->prepare(qq|SELECT budget_period_id from aqbudgetperiods where budget_period_startdate=? and budget_period_enddate=?|);
2981 my $query_bookfund= $dbh->prepare(qq|SELECT * from aqbookfund where bookfundid=?|);
2982 my $selectbudgets=$dbh->prepare(qq|SELECT * from aqbudgets|);
2983 my $updatebudgets=$dbh->prepare(qq|UPDATE aqbudgets SET budget_period_id= ? , budget_name=?, budget_branchcode=? where budget_id=?|);
2984 $selectbudgets->execute;
2985 while (my $databudget=$selectbudgets->fetchrow_hashref){
2986 $query_period->execute ($$databudget{startdate},$$databudget{enddate});
2987 my ($budgetperiodid)=$query_period->fetchrow;
2988 $query_bookfund->execute ($$databudget{budget_code});
2989 my $databf=$query_bookfund->fetchrow_hashref;
2990 my $branchcode=$$databudget{budget_branchcode}||$$databf{branchcode};
2991 $updatebudgets->execute($budgetperiodid,$$databf{bookfundname},$branchcode,$$databudget{budget_id});
2993 $dbh->do(<<BUDGETDROPDATES);
2994 ALTER TABLE `aqbudgets`
3000 $dbh->do("DROP TABLE IF EXISTS `aqbudgets_planning` ");
3001 $dbh->do("CREATE TABLE `aqbudgets_planning` (
3002 `plan_id` int(11) NOT NULL auto_increment,
3003 `budget_id` int(11) NOT NULL,
3004 `budget_period_id` int(11) NOT NULL,
3005 `estimated_amount` decimal(28,6) default NULL,
3006 `authcat` varchar(30) NOT NULL,
3007 `authvalue` varchar(30) NOT NULL,
3008 `display` tinyint(1) DEFAULT 1,
3009 PRIMARY KEY (`plan_id`),
3010 CONSTRAINT `aqbudgets_planning_ifbk_1` FOREIGN KEY (`budget_id`) REFERENCES `aqbudgets` (`budget_id`) ON DELETE CASCADE ON UPDATE CASCADE
3011 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;");
3013 $dbh->do("ALTER TABLE `aqorders`
3014 ADD COLUMN `budget_id` tinyint(4) NOT NULL,
3015 ADD COLUMN `budgetgroup_id` int(11) NOT NULL,
3016 ADD COLUMN `sort1_authcat` varchar(10) default NULL,
3017 ADD COLUMN `sort2_authcat` varchar(10) default NULL" );
3018 # We need to map the orders to the budgets
3019 # For Historic reasons this is more complex than it should be on occasions
3020 my $budg_arr = $dbh->selectall_arrayref(
3021 q|SELECT aqbudgets.budget_id, aqbudgets.budget_code, aqbudgetperiods.budget_period_startdate,
3022 aqbudgetperiods.budget_period_enddate
3023 FROM aqbudgets JOIN aqbudgetperiods ON aqbudgets.budget_period_id = aqbudgetperiods.budget_period_id
3024 ORDER BY budget_code, budget_period_startdate|, { Slice => {} });
3025 # We arbitarily order on start date, this means if you have overlapping periods the order will be
3026 # linked to the latest matching budget YMMV
3027 my $b_sth = $dbh->prepare(
3028 'UPDATE fundmapping set aqbudgetid = ? where bookfundid =? AND budgetdate >= ? AND budgetdate <= ?');
3029 for my $b ( @{$budg_arr}) {
3030 $b_sth->execute($b->{budget_id}, $b->{budget_code}, $b->{budget_period_startdate}, $b->{budget_period_enddate});
3032 # move the budgetids to aqorders
3033 $dbh->do(q|UPDATE aqorders, fundmapping SET aqorders.budget_id = fundmapping.aqbudgetid
3034 WHERE aqorders.ordernumber = fundmapping.ordernumber AND fundmapping.aqbudgetid IS NOT NULL|);
3035 # NB fundmapping is left as an accontants trail also if you have budgetids that werent set
3036 # you can decide what to do with them
3039 q|UPDATE aqorders, aqbudgets SET aqorders.budgetgroup_id = aqbudgets.budget_period_id
3040 WHERE aqorders.budget_id = aqbudgets.budget_id|);
3041 # cannot do until aqorderbreakdown removed
3042 # $dbh->do("DROP TABLE aqbookfund ");
3043 # $dbh->do("ALTER TABLE aqorders ADD FOREIGN KEY (`budget_id`) REFERENCES `aqbudgets` (`budget_id`) ON UPDATE CASCADE " ); ????
3044 $dbh->do("SET FOREIGN_KEY_CHECKS=1 ");
3046 print "Upgrade to $DBversion done (Adding new aqbudgetperiods, aqbudgets and aqbudget_planning tables )\n";
3047 SetVersion ($DBversion);
3052 $DBversion = '3.01.00.078';
3053 if ( C4::Context->preference("Version") < TransformToNum($DBversion) ) {
3054 $dbh->do("ALTER TABLE aqbudgetperiods ADD COLUMN budget_period_total decimal(28,6)");
3055 print "Upgrade to $DBversion done (adds 'budget_period_total' column to aqbudgetperiods table)\n";
3056 SetVersion($DBversion);
3060 $DBversion = '3.01.00.079';
3061 if ( C4::Context->preference("Version") < TransformToNum($DBversion) ) {
3062 $dbh->do("ALTER TABLE currency ADD COLUMN active tinyint(1)");
3064 print "Upgrade to $DBversion done (adds 'active' column to currencies table)\n";
3065 SetVersion($DBversion);
3068 $DBversion = '3.01.00.080';
3069 if ( C4::Context->preference("Version") < TransformToNum($DBversion) ) {
3070 $dbh->do(<<BUDG_PERM );
3071 INSERT INTO permissions (module_bit, code, description) VALUES
3072 (11, 'vendors_manage', 'Manage vendors'),
3073 (11, 'contracts_manage', 'Manage contracts'),
3074 (11, 'period_manage', 'Manage periods'),
3075 (11, 'budget_manage', 'Manage budgets'),
3076 (11, 'budget_modify', "Modify budget (can't create lines but can modify existing ones)"),
3077 (11, 'planning_manage', 'Manage budget plannings'),
3078 (11, 'order_manage', 'Manage orders & basket'),
3079 (11, 'group_manage', 'Manage orders & basketgroups'),
3080 (11, 'order_receive', 'Manage orders & basket'),
3081 (11, 'budget_add_del', "Add and delete budgets (but can't modify budgets)");
3084 print "Upgrade to $DBversion done (adds permissions for the acquisitions module)\n";
3085 SetVersion($DBversion);
3089 $DBversion = '3.01.00.081';
3090 if ( C4::Context->preference("Version") < TransformToNum($DBversion) ) {
3091 $dbh->do("ALTER TABLE aqbooksellers ADD COLUMN `gstrate` decimal(6,4) default NULL");
3092 if (my $gist=C4::Context->preference("gist")){
3093 my $sql=$dbh->prepare("UPDATE aqbooksellers set `gstrate`=? ");
3094 $sql->execute($gist) ;
3096 print "Upgrade to $DBversion done (added per-supplier gstrate setting)\n";
3097 SetVersion($DBversion);
3100 $DBversion = "3.01.00.082";
3101 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
3102 if (C4::Context->preference("opaclanguages") eq "fr") {
3103 $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')#);
3105 $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')");
3107 print "Upgrade to $DBversion done (adding ReservesNeedReturns systempref, in circulation)\n";
3108 SetVersion ($DBversion);
3111 $DBversion = "3.01.00.083";
3112 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
3114 CREATE TABLE `aqorders_items` (
3115 `ordernumber` int(11) NOT NULL,
3116 `itemnumber` int(11) NOT NULL,
3117 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
3118 PRIMARY KEY (`itemnumber`),
3119 KEY `ordernumber` (`ordernumber`)
3120 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
3123 $dbh->do(qq| DROP TABLE aqorderbreakdown |);
3124 $dbh->do('DROP TABLE aqbookfund');
3125 print "Upgrade to $DBversion done (New aqorders_items table for acqui)\n";
3126 SetVersion ($DBversion);
3129 $DBversion = "3.01.00.084";
3130 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
3131 $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') #);
3133 print "Upgrade to $DBversion done (CurrencyFormat syspref added)\n";
3134 SetVersion ($DBversion);
3137 $DBversion = "3.01.00.085";
3138 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
3139 $dbh->do("ALTER table aqorders drop column title");
3140 $dbh->do("ALTER TABLE `aqorders` CHANGE `budget_id` `budget_id` INT( 11 ) NOT NULL");
3141 print "Upgrade to $DBversion done update budget_id size that should not be a tinyint\n";
3142 SetVersion ($DBversion);
3145 $DBversion = "3.01.00.086";
3146 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
3147 $dbh->do(<<SUGGESTIONS);
3148 ALTER table suggestions
3149 ADD budgetid INT(11),
3150 ADD branchcode VARCHAR(10) default NULL,
3151 ADD acceptedby INT(11) default NULL,
3152 ADD accepteddate date default NULL,
3153 ADD suggesteddate date default NULL,
3154 ADD manageddate date default NULL,
3155 ADD rejectedby INT(11) default NULL,
3156 ADD rejecteddate date default NULL,
3157 ADD collectiontitle text default NULL,
3158 ADD itemtype VARCHAR(30) default NULL
3161 print "Upgrade to $DBversion done Suggestions";
3162 SetVersion ($DBversion);
3165 $DBversion = "3.01.00.087";
3166 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
3167 $dbh->do("ALTER table aqbudgets drop column budget_amount_sublevel;");
3168 print "Upgrade to $DBversion done drop column budget_amount_sublevel from aqbudgets\n";
3169 SetVersion ($DBversion);
3172 $DBversion = "3.01.00.088";
3173 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
3174 $dbh->do( qq# INSERT INTO `systempreferences` VALUES ('intranetbookbag','1','','If ON, enables display of Cart feature in the intranet','YesNo') #);
3176 print "Upgrade to $DBversion done (intranetbookbag syspref added)\n";
3177 SetVersion ($DBversion);
3180 $DBversion = "3.01.00.090";
3181 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
3183 INSERT INTO `permissions` (`module_bit`, `code`, `description`) VALUES
3184 (16, 'execute_reports', 'Execute SQL reports'),
3185 (16, 'create_reports', 'Create SQL Reports')
3188 print "Upgrade to $DBversion done (granular permissions for guided reports added)\n";
3189 SetVersion ($DBversion);
3192 $DBversion = "3.01.00.091";
3193 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
3195 UPDATE `systempreferences` SET `options` = 'holdings|serialcollection|subscriptions'
3196 WHERE `systempreferences`.`variable` = 'opacSerialDefaultTab' LIMIT 1
3199 print "Upgrade to $DBversion done (opac-detail default tag updated)\n";
3200 SetVersion ($DBversion);
3203 $DBversion = "3.01.00.092";
3204 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
3205 if (C4::Context->preference("opaclanguages") =~ /fr/) {
3207 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');
3211 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');
3214 print "Upgrade to $DBversion done (Added RoutingListAddReserves syspref)\n";
3215 SetVersion ($DBversion);
3218 $DBversion = "3.01.00.093";
3219 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
3221 ALTER TABLE biblioitems ADD INDEX issn_idx (issn);
3223 print "Upgrade to $DBversion done (added index to ISSN)\n";
3224 SetVersion ($DBversion);
3227 $DBversion = "3.01.00.094";
3228 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
3230 ALTER TABLE aqbasketgroups ADD deliveryplace VARCHAR(10) default NULL, ADD deliverycomment VARCHAR(255) default NULL;
3233 print "Upgrade to $DBversion done (adding deliveryplace deliverycomment to basketgroups)\n";
3234 SetVersion ($DBversion);
3237 $DBversion = "3.01.00.095";
3238 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
3240 ALTER TABLE items ADD stocknumber VARCHAR(32) DEFAULT NULL COMMENT "stores the inventory number";
3243 ALTER TABLE items ADD UNIQUE INDEX itemsstocknumberidx (stocknumber);
3246 ALTER TABLE deleteditems ADD stocknumber VARCHAR(32) DEFAULT NULL COMMENT "stores the inventory number of deleted items";
3249 ALTER TABLE deleteditems ADD UNIQUE INDEX deleteditemsstocknumberidx (stocknumber);
3251 if (C4::Context->preference('marcflavour') eq 'UNIMARC'){
3253 INSERT IGNORE INTO marc_subfield_structure (frameworkcode,tagfield, tagsubfield, tab, repeatable, mandatory,kohafield)
3254 SELECT DISTINCT (frameworkcode),995,"j",10,0,0,"items.stocknumber" from biblio_framework ;
3256 #Previously, copynumber was used as stocknumber
3258 UPDATE items set stocknumber=copynumber;
3261 UPDATE items set copynumber=NULL;
3264 print "Upgrade to $DBversion done (stocknumber field added)\n";
3265 SetVersion ($DBversion);
3268 $DBversion = "3.01.00.096";
3269 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
3270 $dbh->do("INSERT INTO systempreferences (variable,value,explanation,options,type) VALUES ('OrderPdfTemplate','','Uploads a PDF template to use for printing baskets','NULL','Upload')");
3271 $dbh->do("UPDATE systempreferences SET variable='OrderPdfFormat' WHERE variable='pdfformat'");
3272 print "Upgrade to $DBversion done (PDF orders system preferences added and updated)\n";
3273 SetVersion ($DBversion);
3276 $DBversion = "3.01.00.097";
3277 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
3279 ALTER TABLE aqbasketgroups ADD billingplace VARCHAR(10) NOT NULL AFTER deliverycomment;
3282 print "Upgrade to $DBversion done (Adding billingplace to aqbasketgroups)\n";
3283 SetVersion ($DBversion);
3286 $DBversion = "3.01.00.098";
3287 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
3289 ALTER TABLE auth_subfield_structure MODIFY frameworkcode VARCHAR(10) NULL;
3292 print "Upgrade to $DBversion done (changing frameworkcode length in auth_subfield_structure)\n";
3293 SetVersion ($DBversion);
3296 $DBversion = "3.01.00.099";
3297 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
3299 INSERT INTO `permissions` (`module_bit`, `code`, `description`) VALUES
3300 (9, 'edit_catalogue', 'Edit catalogue'),
3301 (9, 'fast_cataloging', 'Fast cataloging')
3304 print "Upgrade to $DBversion done (granular permissions for cataloging added)\n";
3305 SetVersion ($DBversion);
3308 $DBversion = "3.01.00.100";
3309 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
3310 $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')");
3311 print "Upgrade done (added CAS authentication system preferences)\n";
3312 SetVersion ($DBversion);
3315 $DBversion = "3.01.00.101";
3316 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
3318 "INSERT INTO systempreferences
3319 (variable, value, options, explanation, type)
3321 'OverdueNoticeBcc', '', '',
3322 'Email address to Bcc outgoing notices sent by email',
3325 print "Upgrade to $DBversion done (added OverdueNoticeBcc system preferences)\n";
3326 SetVersion ($DBversion);
3328 $DBversion = "3.01.00.102";
3329 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
3331 "UPDATE permissions set description = 'Edit catalog (Modify bibliographic/holdings data)' where module_bit = 9 and code = 'edit_catalogue'"
3333 print "Upgrade done (fixed spelling error in edit_catalogue permission)\n";
3334 SetVersion ($DBversion);
3337 $DBversion = "3.01.00.103";
3338 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
3339 $dbh->do("INSERT INTO permissions (module_bit, code, description) VALUES (13, 'moderate_tags', 'Moderate patron tags')");
3340 print "Upgrade done (adding patron permissions for tags tool)\n";
3341 SetVersion ($DBversion);
3344 $DBversion = "3.01.00.104";
3345 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
3347 my ($maninv_count, $borrnotes_count);
3348 eval { $maninv_count = $dbh->do("SELECT 1 FROM authorised_values WHERE category='MANUAL_INV'"); };
3349 if ($maninv_count == 0) {
3350 $dbh->do("INSERT INTO authorised_values (category,authorised_value,lib) VALUES ('MANUAL_INV','Copier Fees','.25')");
3352 eval { $borrnotes_count = $dbh->do("SELECT 1 FROM authorised_values WHERE category='BOR_NOTES'"); };
3353 if ($borrnotes_count == 0) {
3354 $dbh->do("INSERT INTO authorised_values (category,authorised_value,lib) VALUES ('BOR_NOTES','ADDR','Address Notes')");
3357 $dbh->do("INSERT INTO authorised_values (category,authorised_value,lib) VALUES ('LOC','CART','Book Cart')");
3358 $dbh->do("INSERT INTO authorised_values (category,authorised_value,lib) VALUES ('LOC','PROC','Processing Center')");
3360 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";
3361 SetVersion ($DBversion);
3365 $DBversion = "3.01.00.105";
3366 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
3368 CREATE TABLE `collections` (
3369 `colId` int(11) NOT NULL auto_increment,
3370 `colTitle` varchar(100) NOT NULL default '',
3371 `colDesc` text NOT NULL,
3372 `colBranchcode` varchar(4) default NULL COMMENT 'branchcode for branch where item should be held.',
3373 PRIMARY KEY (`colId`)
3374 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
3378 CREATE TABLE `collections_tracking` (
3379 `ctId` int(11) NOT NULL auto_increment,
3380 `colId` int(11) NOT NULL default '0' COMMENT 'collections.colId',
3381 `itemnumber` int(11) NOT NULL default '0' COMMENT 'items.itemnumber',
3382 PRIMARY KEY (`ctId`)
3383 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
3386 INSERT INTO permissions (module_bit, code, description)
3387 VALUES ( 13, 'rotating_collections', 'Manage Rotating collections')" );
3388 print "Upgrade to $DBversion done (added collection and collection_tracking tables for rotating collections functionality)\n";
3389 SetVersion ($DBversion);
3391 $DBversion = "3.01.00.106";
3392 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
3393 $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' )");
3394 print "Upgrade done (added OpacAddMastheadLibraryPulldown system preferences)\n";
3395 SetVersion ($DBversion);
3398 $DBversion = '3.01.00.107';
3399 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
3400 my $upgrade_script = C4::Context->config("intranetdir") . "/installer/data/mysql/patroncards_upgrade.pl";
3401 system("perl $upgrade_script");
3402 print "Upgrade to $DBversion done (Migrated labels and patroncards tables and data to new schema.)\n";
3403 SetVersion ($DBversion);
3406 $DBversion = '3.01.00.108';
3407 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
3409 ALTER TABLE `export_format` ADD `csv_separator` VARCHAR( 2 ) NOT NULL AFTER `marcfields` ,
3410 ADD `field_separator` VARCHAR( 2 ) NOT NULL AFTER `csv_separator` ,
3411 ADD `subfield_separator` VARCHAR( 2 ) NOT NULL AFTER `field_separator`
3413 print "Upgrade done (added separators for csv export)\n";
3414 SetVersion ($DBversion);
3417 $DBversion = "3.01.00.109";
3418 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
3420 ALTER TABLE `export_format` ADD `encoding` VARCHAR(255) NOT NULL AFTER `subfield_separator`
3422 print "Upgrade done (added encoding for csv export)\n";
3423 SetVersion ($DBversion);
3426 $DBversion = '3.01.00.110';
3427 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
3428 $dbh->do('ALTER TABLE `categories` ADD COLUMN `enrolmentperioddate` DATE NULL DEFAULT NULL AFTER `enrolmentperiod`');
3429 print "Upgrade done (Add enrolment period date support)\n";
3430 SetVersion ($DBversion);
3433 $DBversion = '3.01.00.111';
3434 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
3435 print "Upgrade done (mark DBrev for 3.2-alpha release)\n";
3436 SetVersion ($DBversion);
3439 $DBversion = '3.01.00.112';
3440 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
3441 $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');");
3442 print "Upgrade done ( added Show Spine Label Printer on Bib Items Details preferences )\n";
3443 SetVersion ($DBversion);
3446 $DBversion = '3.01.00.113';
3447 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
3448 my $value = C4::Context->preference("XSLTResultsDisplay");
3450 "INSERT INTO systempreferences (variable,value,type)
3451 VALUES('OPACXSLTResultsDisplay',$value,'YesNo')");
3452 $value = C4::Context->preference("XSLTDetailsDisplay");
3454 "INSERT INTO systempreferences (variable,value,type)
3455 VALUES('OPACXSLTDetailsDisplay',$value,'YesNo')");
3456 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 ";
3457 SetVersion ($DBversion);
3460 $DBversion = '3.01.00.114';
3461 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
3462 $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')");
3463 $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')");
3464 $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')");
3465 print "Upgrade to $DBversion done ( Added AutoSelfCheckAllowed, AutoSelfCheckID, and AutoShelfCheckPass system preference )\n";
3466 SetVersion ($DBversion);
3469 $DBversion = '3.01.00.115';
3470 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
3471 $dbh->do('UPDATE aqorders SET quantityreceived = 0 WHERE quantityreceived IS NULL');
3472 $dbh->do('ALTER TABLE aqorders MODIFY COLUMN quantityreceived smallint(6) NOT NULL DEFAULT 0');
3473 print "Upgrade to $DBversion done ( Default aqorders.quantityreceived to 0 )\n";
3474 SetVersion ($DBversion);
3477 $DBversion = '3.01.00.116';
3478 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
3479 if (C4::Context->preference('OrderPdfFormat') eq 'pdfformat::example'){
3480 $dbh->do("UPDATE `systempreferences` set value='pdfformat::layout2pages' WHERE variable='OrderPdfFormat'");
3482 print "Upgrade done ( corrected default OrderPdfFormat value if still set wrong )\n";
3483 SetVersion ($DBversion);
3486 $DBversion = '3.01.00.117';
3487 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
3488 $dbh->do("UPDATE language_rfc4646_to_iso639 SET iso639_2_code = 'por' WHERE rfc4646_subtag='pt' ");
3489 print "Upgrade to $DBversion done (corrected ISO 639-2 language code for Portuguese)\n";
3490 SetVersion ($DBversion);
3493 $DBversion = '3.01.00.118';
3494 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
3495 my ($count) = $dbh->selectrow_array("SELECT count(*) FROM information_schema.columns
3496 WHERE table_name = 'aqbudgets_planning'
3497 AND column_name = 'display'");
3499 $dbh->do("ALTER TABLE aqbudgets_planning ADD COLUMN display tinyint(1) DEFAULT 1");
3501 print "Upgrade to $DBversion done (bug 4203: add display column to aqbudgets_planning if missing)\n";
3502 SetVersion ($DBversion);
3505 $DBversion = '3.01.00.119';
3506 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
3507 eval{require Locale::Currency::Format};
3509 print "Upgrade to $DBversion done (Locale::Currency::Format installed.)\n";
3510 SetVersion ($DBversion);
3513 print "Upgrade to $DBversion done.\n";
3514 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";
3515 SetVersion ($DBversion);
3519 $DBversion = '3.01.00.120';
3520 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
3522 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');
3524 print "Upgrade to $DBversion done (bug 1080: add soundon system preference for circulation sounds)\n";
3525 SetVersion ($DBversion);
3528 $DBversion = '3.01.00.121';
3529 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
3530 $dbh->do("ALTER TABLE `reserves` ADD `expirationdate` DATE DEFAULT NULL");
3531 $dbh->do("ALTER TABLE `reserves` ADD `lowestPriority` tinyint(1) NOT NULL");
3532 $dbh->do("ALTER TABLE `old_reserves` ADD `expirationdate` DATE DEFAULT NULL");
3533 $dbh->do("ALTER TABLE `old_reserves` ADD `lowestPriority` tinyint(1) NOT NULL");
3534 print "Upgrade to $DBversion done ( Added Additional Fields to Reserves tables )\n";
3535 SetVersion ($DBversion);
3538 $DBversion = '3.01.00.122';
3539 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
3541 INSERT INTO systempreferences (variable,value,explanation,options,type)
3542 VALUES ('OAI-PMH:ConfFile', '', 'If empty, Koha OAI Server operates in normal mode, otherwise it operates in extended mode.','','File');
3544 print "Upgrade to $DBversion done. — Add a new system preference OAI-PMF:ConfFile\n";
3545 SetVersion ($DBversion);
3548 $DBversion = "3.01.00.123";
3549 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
3550 $dbh->do("INSERT INTO `permissions` (`module_bit`, `code`, `description`) VALUES
3551 (6, 'place_holds', 'Place holds for patrons')");
3552 $dbh->do("INSERT INTO `permissions` (`module_bit`, `code`, `description`) VALUES
3553 (6, 'modify_holds_priority', 'Modify holds priority')");
3554 $dbh->do("UPDATE `userflags` SET `flagdesc` = 'Place and modify holds for patrons' WHERE `flag` = 'reserveforothers'");
3555 print "Upgrade to $DBversion done (Add granular permission for holds modification and update description of reserveforothers permission)\n";
3556 SetVersion ($DBversion);
3559 $DBversion = '3.01.00.124';
3560 if (C4::Context->preference('Version') < TransformToNum($DBversion)){
3562 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>>).');
3564 print "Upgrade to $DBversion done (bug 3242: add HOLDPLACED letter template, which is used when emailLibrarianWhenHoldIsPlaced is enabled)\n";
3565 SetVersion ($DBversion);
3568 $DBversion = '3.01.00.125';
3569 if (C4::Context->preference('Version') < TransformToNum($DBversion)){
3571 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' );
3574 INSERT INTO message_transport_types (message_transport_type) values ('print');
3576 print "Upgrade to $DBversion done (bug 3482: Printable hold and overdue notices)\n";
3577 SetVersion ($DBversion);
3580 $DBversion = "3.01.00.126";
3581 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
3582 $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')");
3583 $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')");
3585 print "Upgrade to $DBversion done (Adding ILS-DI updates and ILS-DI:AuthorizedIPs)\n";
3586 SetVersion ($DBversion);
3589 $DBversion = '3.01.00.127';
3590 if (C4::Context->preference('Version') < TransformToNum($DBversion)){
3591 $dbh->do("ALTER TABLE messages CHANGE branchcode branchcode varchar(10);");
3592 print "Upgrade to $DBversion done (bug 4190: messages in patron account did not work with branchcodes > 4)\n";
3593 SetVersion ($DBversion);
3596 $DBversion = '3.01.00.128';
3597 if (C4::Context->preference('Version') < TransformToNum($DBversion)){
3598 $dbh->do('CREATE INDEX budget_id ON aqorders (budget_id );');
3599 print "Upgrade to $DBversion done (bug 4331: index orders by budget_id)\n";
3600 SetVersion ($DBversion);
3603 $DBversion = "3.01.00.129";
3604 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
3605 $dbh->do("UPDATE `permissions` SET `code` = 'items_batchdel' WHERE `permissions`.`module_bit` =13 AND `permissions`.`code` = 'batchdel' LIMIT 1 ;");
3606 $dbh->do("UPDATE `permissions` SET `code` = 'items_batchmod' WHERE `permissions`.`module_bit` =13 AND `permissions`.`code` = 'batchmod' LIMIT 1 ;");
3607 print "Upgrade done (Change permissions names for item batch modification / deletion)\n";
3609 SetVersion ($DBversion);
3612 $DBversion = "3.01.00.130";
3613 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
3614 $dbh->do("UPDATE reserves SET expirationdate = NULL WHERE expirationdate = '0000-00-00'");
3615 print "Upgrade done (change reserves.expirationdate values of 0000-00-00 to NULL (bug 1532)";
3616 SetVersion ($DBversion);
3619 $DBversion = "3.01.00.131";
3620 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
3622 INSERT IGNORE INTO message_transport_types (message_transport_type) VALUES ('print'),('feed');
3624 print "Upgrade to $DBversion done (adding print and feed message transport types)\n";
3625 SetVersion ($DBversion);
3628 $DBversion = "3.01.00.132";
3629 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
3631 ALTER TABLE language_descriptions ADD INDEX subtag_type_lang (subtag, type, lang);
3633 print "Upgrade to $DBversion done (Adding index to language_descriptions table)\n";
3634 SetVersion ($DBversion);
3637 $DBversion = '3.01.00.133';
3638 if (C4::Context->preference('Version') < TransformToNum($DBversion)){
3639 $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')");
3640 print "Upgrade to $DBversion done (bug 4405: added OverduesBlockCirc syspref to control whether circulation is blocked if a borrower has overdues)\n";
3641 SetVersion ($DBversion);
3644 $DBversion = '3.01.00.134';
3645 if (C4::Context->preference('Version') < TransformToNum($DBversion)){
3646 $dbh->do("INSERT INTO systempreferences (variable,value,explanation,options,type) VALUES ('DisplayMultiPlaceHold','1','Display the ability to place multiple holds or not','','YesNo')");
3647 print "Upgrade to $DBversion done adding syspref DisplayMultiPlaceHold to control whether multiple holds can be placed from the search results page";
3648 SetVersion ($DBversion);
3651 $DBversion = '3.01.00.135';
3652 if (C4::Context->preference('Version') < TransformToNum($DBversion)){
3654 INSERT INTO `letter` (module, code, name, title, content) VALUES
3655 ('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')
3657 print "Upgrade to $DBversion done (bug 4377: added HOLD_PRINT message template)";
3658 SetVersion ($DBversion);
3661 $DBversion = '3.01.00.136';
3662 if (C4::Context->preference('Version') < TransformToNum($DBversion)){
3664 INSERT INTO permissions (module_bit, code, description) VALUES
3665 ( 9, 'edit_items', 'Edit Items');});
3666 print "Upgrade to $DBversion done Adding a new permission to edit items";
3667 SetVersion ($DBversion);
3670 $DBversion = "3.01.00.137";
3671 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
3673 INSERT INTO permissions (module_bit, code, description) VALUES
3674 (15, 'check_expiration', 'Check the expiration of a serial'),
3675 (15, 'claim_serials', 'Claim missing serials'),
3676 (15, 'create_subscription', 'Create a new subscription'),
3677 (15, 'delete_subscription', 'Delete an existing subscription'),
3678 (15, 'edit_subscription', 'Edit an existing subscription'),
3679 (15, 'receive_serials', 'Serials receiving'),
3680 (15, 'renew_subscription', 'Renew a subscription'),
3681 (15, 'routing', 'Routing');
3683 print "Upgrade to $DBversion done (adding granular permissions for serials)";
3684 SetVersion ($DBversion);
3687 $DBversion = "3.01.00.138";
3688 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
3689 $dbh->do("DELETE FROM systempreferences WHERE variable = 'GranularPermissions'");
3690 print "Upgrade to $DBversion done (bug 4896: removing GranularPermissions syspref; use of granular permissions is now the default)";
3691 SetVersion ($DBversion);
3694 $DBversion = '3.01.00.139';
3695 if (C4::Context->preference('Version') < TransformToNum($DBversion)){
3696 $dbh->do("ALTER TABLE message_attributes CHANGE message_name message_name varchar(40);");
3697 print "Upgrade to $DBversion done (bug 3682: change message_name from varchar(20) to varchar(40))\n";
3698 SetVersion ($DBversion);
3701 $DBversion = '3.01.00.140';
3702 if (C4::Context->preference('Version') < TransformToNum($DBversion)){
3703 $dbh->do("UPDATE systempreferences SET value = '0' WHERE variable = 'TagsModeration' AND value is NULL");
3704 print "Upgrade to $DBversion done (bug 4312 TagsModeration changed from NULL to 0)\n";
3705 SetVersion ($DBversion);
3708 $DBversion = '3.01.00.141';
3709 if (C4::Context->preference('Version') < TransformToNum($DBversion)){
3710 $dbh->do(qq{DELETE FROM message_attributes WHERE message_attribute_id=3;});
3711 $dbh->do(qq{DELETE FROM letter WHERE code='EVENT' AND title='Upcoming Library Event';});
3712 print "Upgrade to $DBversion done Remove upcoming events messaging option (bug 2434)";
3713 SetVersion ($DBversion);
3716 $DBversion = '3.01.00.142';
3717 if (C4::Context->preference('Version') < TransformToNum($DBversion)){
3718 $dbh->do(qq{DELETE FROM message_transports WHERE message_attribute_id=3;});
3719 print "Upgrade to $DBversion done Remove upcoming events messaging option part 2 (bug 2434)";
3720 SetVersion ($DBversion);
3723 $DBversion = '3.01.00.143';
3724 if (C4::Context->preference('Version') < TransformToNum($DBversion)){
3725 $dbh->do(qq{CREATE INDEX auth_value_idx ON authorised_values (authorised_value)});
3726 $dbh->do(qq{CREATE INDEX auth_val_cat_idx ON borrower_attribute_types (authorised_value_category)});
3727 print "Create index on authorised_values and borrower_attribute_types (bug 4139)";
3728 SetVersion ($DBversion);
3731 $DBversion = '3.01.00.144';
3732 if (C4::Context->preference('Version') < TransformToNum($DBversion)){
3733 $dbh->do(qq{UPDATE systempreferences SET value='normal' where value='default' and variable='IntranetBiblioDefaultView'});
3734 print "Update the 'default' to 'normal' for the IntranetBiblioDefaultView syspref (bug 5007)";
3735 SetVersion ($DBversion);
3738 $DBversion = "3.01.00.145";
3739 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
3740 $dbh->do("ALTER TABLE borrowers ADD KEY `guarantorid` (guarantorid);");
3741 print "Upgrade to $DBversion done (Add index on guarantorid)\n";
3742 SetVersion ($DBversion);
3745 $DBversion = '3.01.00.999';
3746 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
3747 print "Upgrade to $DBversion done (3.2.0 release candidate)\n";
3748 SetVersion ($DBversion);
3751 $DBversion = "3.02.00.000";
3752 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
3753 my $value = $dbh->selectrow_array("SELECT value FROM systempreferences WHERE variable = 'HomeOrHoldingBranch'");
3754 $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');");
3755 print "Upgrade to $DBversion done (Add HomeOrHoldingBranchReturn system preference)\n";
3756 SetVersion ($DBversion);
3759 $DBversion = "3.02.00.001";
3760 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
3761 $dbh->do(q{DELETE FROM systempreferences WHERE variable IN (
3766 'OPACSubscriptionDisplay',
3767 'OPACDisplayExtendedSubInfo',
3778 print "Upgrade to $DBversion done (bug 3756: remove disused system preferences)\n";
3779 SetVersion ($DBversion);
3782 $DBversion = "3.02.00.002";
3783 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
3784 $dbh->do(q{DELETE FROM systempreferences WHERE variable = 'OpacPrivacy'});
3785 print "Upgrade to $DBversion done (bug 3881: remove unused OpacPrivacy system preference)\n";
3786 SetVersion ($DBversion);
3789 $DBversion = "3.02.00.003";
3790 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
3791 $dbh->do(q{UPDATE systempreferences SET variable = 'ILS-DI:AuthorizedIPs' WHERE variable = 'ILS-DI:Authorized_IPs'});
3792 print "Upgrade to $DBversion done (correct ILS-DI:AuthorizedIPs)\n";
3793 SetVersion ($DBversion);
3796 $DBversion = "3.02.00.004";
3797 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
3798 print "Upgrade to $DBversion done (3.2.0 general release)\n";
3799 SetVersion ($DBversion);
3802 $DBversion = "3.01.00.XXX";
3803 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
3804 $dbh->do("DELETE FROM subscriptionroutinglist WHERE borrowernumber IS NULL;");
3805 $dbh->do("ALTER TABLE subscriptionroutinglist MODIFY COLUMN `borrowernumber` int(11) NOT NULL;");
3806 $dbh->do("DELETE FROM subscriptionroutinglist WHERE subscriptionid IS NULL;");
3807 $dbh->do("ALTER TABLE subscriptionroutinglist MODIFY COLUMN `subscriptionid` int(11) NOT NULL;");
3808 $dbh->do("CREATE TEMPORARY TABLE del_subscriptionroutinglist
3809 SELECT s1.routingid FROM subscriptionroutinglist s1
3810 WHERE EXISTS (SELECT * FROM subscriptionroutinglist s2
3811 WHERE s2.borrowernumber = s1.borrowernumber
3812 AND s2.subscriptionid = s1.subscriptionid
3813 AND s2.routingid < s1.routingid);");
3814 $dbh->do("DELETE FROM subscriptionroutinglist
3815 WHERE routingid IN (SELECT routingid FROM del_subscriptionroutinglist);");
3816 $dbh->do("ALTER TABLE subscriptionroutinglist ADD UNIQUE (subscriptionid, borrowernumber);");
3817 print "Upgrade to $DBversion done (Make subscriptionroutinglist more strict)\n";
3818 SetVersion ($DBversion);
3821 =item DropAllForeignKeys($table)
3823 Drop all foreign keys of the table $table
3828 sub DropAllForeignKeys {
3830 # get the table description
3831 my $sth = $dbh->prepare("SHOW CREATE TABLE $table");
3833 my $vsc_structure = $sth->fetchrow;
3834 # split on CONSTRAINT keyword
3835 my @fks = split /CONSTRAINT /,$vsc_structure;
3838 # isolate what is before FOREIGN KEY, if there is something, it's a foreign key to drop
3839 $_ = /(.*) FOREIGN KEY.*/;
3842 # we have found 1 foreign, drop it
3843 $dbh->do("ALTER TABLE $table DROP FOREIGN KEY $id");
3850 =item TransformToNum
3852 Transform the Koha version from a 4 parts string
3853 to a number, with just 1 .
3857 sub TransformToNum {
3858 my $version = shift;
3859 # remove the 3 last . to have a Perl number
3860 $version =~ s/(.*\..*)\.(.*)\.(.*)/$1$2$3/;
3866 set the DBversion in the systempreferences
3871 my $kohaversion = TransformToNum(shift);
3872 if (C4::Context->preference('Version')) {
3873 my $finish=$dbh->prepare("UPDATE systempreferences SET value=? WHERE variable='Version'");
3874 $finish->execute($kohaversion);
3876 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')");
3877 $finish->execute($kohaversion);