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
54 # Deal with virtualshelves
56 my $DBversion = "3.00.00.001";
57 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
58 # update virtualshelves table to
60 $dbh->do("ALTER TABLE `bookshelf` RENAME `virtualshelves`");
61 $dbh->do("ALTER TABLE `shelfcontents` RENAME `virtualshelfcontents`");
62 $dbh->do("ALTER TABLE `virtualshelfcontents` ADD `biblionumber` INT( 11 ) NOT NULL default '0' AFTER shelfnumber");
63 $dbh->do("UPDATE `virtualshelfcontents` SET biblionumber=(SELECT biblionumber FROM items WHERE items.itemnumber=virtualshelfcontents.itemnumber)");
64 # drop all foreign keys : otherwise, we can't drop itemnumber field.
65 DropAllForeignKeys('virtualshelfcontents');
66 $dbh->do("ALTER TABLE `virtualshelfcontents` ADD KEY biblionumber (biblionumber)");
67 # create the new foreign keys (on biblionumber)
68 $dbh->do("ALTER TABLE `virtualshelfcontents` ADD CONSTRAINT `virtualshelfcontents_ibfk_1` FOREIGN KEY (`shelfnumber`) REFERENCES `virtualshelves` (`shelfnumber`) ON DELETE CASCADE ON UPDATE CASCADE");
69 # re-create the foreign key on virtualshelf
70 $dbh->do("ALTER TABLE `virtualshelfcontents` ADD CONSTRAINT `shelfcontents_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE");
71 $dbh->do("ALTER TABLE `virtualshelfcontents` DROP `itemnumber`");
72 print "Upgrade to $DBversion done (virtualshelves)\n";
73 SetVersion ($DBversion);
77 $DBversion = "3.00.00.002";
78 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
79 $dbh->do("DROP TABLE sessions");
80 $dbh->do("CREATE TABLE `sessions` (
81 `id` varchar(32) NOT NULL,
82 `a_session` text NOT NULL,
83 UNIQUE KEY `id` (`id`)
84 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;");
85 print "Upgrade to $DBversion done (sessions uses CGI::session, new table structure for sessions)\n";
86 SetVersion ($DBversion);
90 $DBversion = "3.00.00.003";
91 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
92 if (C4::Context->preference("opaclanguages") eq "fr") {
93 $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')");
95 $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')");
97 print "Upgrade to $DBversion done (adding ReservesNeedReturns systempref, in circulation)\n";
98 SetVersion ($DBversion);
102 $DBversion = "3.00.00.004";
103 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
104 $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')");
105 print "Upgrade to $DBversion done (adding DebugLevel systempref, in 'Admin' tab)\n";
106 SetVersion ($DBversion);
109 $DBversion = "3.00.00.005";
110 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
111 $dbh->do("CREATE TABLE `tags` (
112 `entry` varchar(255) NOT NULL default '',
113 `weight` bigint(20) NOT NULL default 0,
114 PRIMARY KEY (`entry`)
115 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
117 $dbh->do("CREATE TABLE `nozebra` (
118 `server` varchar(20) NOT NULL,
119 `indexname` varchar(40) NOT NULL,
120 `value` varchar(250) NOT NULL,
121 `biblionumbers` longtext NOT NULL,
122 KEY `indexname` (`server`,`indexname`),
123 KEY `value` (`server`,`value`))
124 ENGINE=InnoDB DEFAULT CHARSET=utf8;
126 print "Upgrade to $DBversion done (adding tags and nozebra tables )\n";
127 SetVersion ($DBversion);
130 $DBversion = "3.00.00.006";
131 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
132 $dbh->do("UPDATE issues SET issuedate=timestamp WHERE issuedate='0000-00-00'");
133 print "Upgrade to $DBversion done (filled issues.issuedate with timestamp)\n";
134 SetVersion ($DBversion);
137 $DBversion = "3.00.00.007";
138 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
139 $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')");
140 print "Upgrade to $DBversion done (set SessionStorage variable)\n";
141 SetVersion ($DBversion);
144 $DBversion = "3.00.00.008";
145 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
146 $dbh->do("ALTER TABLE `biblio` ADD `datecreated` DATE NOT NULL AFTER `timestamp` ;");
147 $dbh->do("UPDATE biblio SET datecreated=timestamp");
148 print "Upgrade to $DBversion done (biblio creation date)\n";
149 SetVersion ($DBversion);
152 $DBversion = "3.00.00.009";
153 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
155 # Create backups of call number columns
156 # in case default migration needs to be customized
158 # UPGRADE NOTE: temp_upg_biblioitems_call_num should be dropped
159 # after call numbers have been transformed to the new structure
161 # Not bothering to do the same with deletedbiblioitems -- assume
162 # default is good enough.
163 $dbh->do("CREATE TABLE `temp_upg_biblioitems_call_num` AS
164 SELECT `biblioitemnumber`, `biblionumber`,
165 `classification`, `dewey`, `subclass`,
167 FROM `biblioitems`");
169 # biblioitems changes
170 $dbh->do("ALTER TABLE `biblioitems` CHANGE COLUMN `volumeddesc` `volumedesc` TEXT,
171 ADD `cn_source` VARCHAR(10) DEFAULT NULL AFTER `ccode`,
172 ADD `cn_class` VARCHAR(30) DEFAULT NULL AFTER `cn_source`,
173 ADD `cn_item` VARCHAR(10) DEFAULT NULL AFTER `cn_class`,
174 ADD `cn_suffix` VARCHAR(10) DEFAULT NULL AFTER `cn_item`,
175 ADD `cn_sort` VARCHAR(30) DEFAULT NULL AFTER `cn_suffix`,
176 ADD `totalissues` INT(10) AFTER `cn_sort`");
178 # default mapping of call number columns:
179 # cn_class = concatentation of classification + dewey,
180 # trimmed to fit -- assumes that most users do not
181 # populate both classification and dewey in a single record
183 # cn_source = left null
186 # After upgrade, cn_sort will have to be set based on whatever
187 # default call number scheme user sets as a preference. Misc
188 # script will be added at some point to do that.
190 $dbh->do("UPDATE `biblioitems`
191 SET cn_class = SUBSTR(TRIM(CONCAT_WS(' ', `classification`, `dewey`)), 1, 30),
196 # Now drop the old call number columns
197 $dbh->do("ALTER TABLE `biblioitems` DROP COLUMN `classification`,
199 DROP COLUMN `subclass`,
200 DROP COLUMN `lcsort`,
201 DROP COLUMN `ccode`");
203 # deletedbiblio changes
204 $dbh->do("ALTER TABLE `deletedbiblio` ALTER COLUMN `frameworkcode` SET DEFAULT '',
206 ADD `datecreated` DATE NOT NULL AFTER `timestamp`");
207 $dbh->do("UPDATE deletedbiblio SET datecreated = timestamp");
209 # deletedbiblioitems changes
210 $dbh->do("ALTER TABLE `deletedbiblioitems`
211 MODIFY `publicationyear` TEXT,
212 CHANGE `volumeddesc` `volumedesc` TEXT,
213 MODIFY `collectiontitle` MEDIUMTEXT DEFAULT NULL AFTER `volumedesc`,
214 MODIFY `collectionissn` TEXT DEFAULT NULL AFTER `collectiontitle`,
215 MODIFY `collectionvolume` MEDIUMTEXT DEFAULT NULL AFTER `collectionissn`,
216 MODIFY `editionstatement` TEXT DEFAULT NULL AFTER `collectionvolume`,
217 MODIFY `editionresponsibility` TEXT DEFAULT NULL AFTER `editionstatement`,
218 MODIFY `place` VARCHAR(255) DEFAULT NULL AFTER `size`,
219 MODIFY `marc` LONGBLOB,
220 ADD `cn_source` VARCHAR(10) DEFAULT NULL AFTER `url`,
221 ADD `cn_class` VARCHAR(30) DEFAULT NULL AFTER `cn_source`,
222 ADD `cn_item` VARCHAR(10) DEFAULT NULL AFTER `cn_class`,
223 ADD `cn_suffix` VARCHAR(10) DEFAULT NULL AFTER `cn_item`,
224 ADD `cn_sort` VARCHAR(30) DEFAULT NULL AFTER `cn_suffix`,
225 ADD `totalissues` INT(10) AFTER `cn_sort`,
226 ADD `marcxml` LONGTEXT NOT NULL AFTER `totalissues`,
227 ADD KEY `isbn` (`isbn`),
228 ADD KEY `publishercode` (`publishercode`)
231 $dbh->do("UPDATE `deletedbiblioitems`
232 SET `cn_class` = SUBSTR(TRIM(CONCAT_WS(' ', `classification`, `dewey`)), 1, 30),
233 `cn_item` = `subclass`,
236 $dbh->do("ALTER TABLE `deletedbiblioitems`
237 DROP COLUMN `classification`,
239 DROP COLUMN `subclass`,
240 DROP COLUMN `lcsort`,
244 # deleteditems changes
245 $dbh->do("ALTER TABLE `deleteditems`
246 MODIFY `barcode` VARCHAR(20) DEFAULT NULL,
247 MODIFY `price` DECIMAL(8,2) DEFAULT NULL,
248 MODIFY `replacementprice` DECIMAL(8,2) DEFAULT NULL,
250 MODIFY `itemcallnumber` VARCHAR(30) DEFAULT NULL AFTER `wthdrawn`,
251 MODIFY `holdingbranch` VARCHAR(10) DEFAULT NULL,
253 MODIFY `timestamp` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP AFTER `paidfor`,
255 ADD `cn_source` VARCHAR(10) DEFAULT NULL AFTER `onloan`,
256 ADD `cn_sort` VARCHAR(30) DEFAULT NULL AFTER `cn_source`,
257 ADD `ccode` VARCHAR(10) DEFAULT NULL AFTER `cn_sort`,
258 ADD `materials` VARCHAR(10) DEFAULT NULL AFTER `ccode`,
259 ADD `uri` VARCHAR(255) DEFAULT NULL AFTER `materials`,
260 MODIFY `marc` LONGBLOB AFTER `uri`,
262 DROP KEY `itembarcodeidx`,
263 DROP KEY `itembinoidx`,
264 DROP KEY `itembibnoidx`,
265 ADD UNIQUE KEY `delitembarcodeidx` (`barcode`),
266 ADD KEY `delitembinoidx` (`biblioitemnumber`),
267 ADD KEY `delitembibnoidx` (`biblionumber`),
268 ADD KEY `delhomebranch` (`homebranch`),
269 ADD KEY `delholdingbranch` (`holdingbranch`)");
270 $dbh->do("UPDATE deleteditems SET `ccode` = `itype`");
271 $dbh->do("ALTER TABLE deleteditems DROP `itype`");
272 $dbh->do("UPDATE `deleteditems` SET `cn_sort` = `itemcallnumber`");
275 $dbh->do("ALTER TABLE `items` ADD `cn_source` VARCHAR(10) DEFAULT NULL AFTER `onloan`,
276 ADD `cn_sort` VARCHAR(30) DEFAULT NULL AFTER `cn_source`,
277 ADD `ccode` VARCHAR(10) DEFAULT NULL AFTER `cn_sort`,
278 ADD `materials` VARCHAR(10) DEFAULT NULL AFTER `ccode`,
279 ADD `uri` VARCHAR(255) DEFAULT NULL AFTER `materials`
281 $dbh->do("ALTER TABLE `items`
282 DROP KEY `itembarcodeidx`,
283 ADD UNIQUE KEY `itembarcodeidx` (`barcode`)");
285 # map items.itype to items.ccode and
286 # set cn_sort to itemcallnumber -- as with biblioitems.cn_sort,
287 # will have to be subsequently updated per user's default
288 # classification scheme
289 $dbh->do("UPDATE `items` SET `cn_sort` = `itemcallnumber`,
292 $dbh->do("ALTER TABLE `items` DROP `cutterextra`,
295 print "Upgrade to $DBversion done (major changes to biblio, biblioitems, items, and deleted* versions of same\n";
296 SetVersion ($DBversion);
299 $DBversion = "3.00.00.010";
300 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
301 $dbh->do("CREATE INDEX `userid` ON borrowers (`userid`) ");
302 print "Upgrade to $DBversion done (userid index added)\n";
303 SetVersion ($DBversion);
306 $DBversion = "3.00.00.011";
307 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
308 $dbh->do("ALTER TABLE `branchcategories` CHANGE `categorycode` `categorycode` varchar(10) ");
309 $dbh->do("ALTER TABLE `branchcategories` CHANGE `categoryname` `categoryname` varchar(32) ");
310 $dbh->do("ALTER TABLE `branchcategories` ADD COLUMN `categorytype` varchar(16) ");
311 $dbh->do("UPDATE `branchcategories` SET `categorytype` = 'properties'");
312 $dbh->do("ALTER TABLE `branchrelations` CHANGE `categorycode` `categorycode` varchar(10) ");
313 print "Upgrade to $DBversion done (added branchcategory type)\n";
314 SetVersion ($DBversion);
317 $DBversion = "3.00.00.012";
318 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
319 $dbh->do("CREATE TABLE `class_sort_rules` (
320 `class_sort_rule` varchar(10) NOT NULL default '',
321 `description` mediumtext,
322 `sort_routine` varchar(30) NOT NULL default '',
323 PRIMARY KEY (`class_sort_rule`),
324 UNIQUE KEY `class_sort_rule_idx` (`class_sort_rule`)
325 ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
326 $dbh->do("CREATE TABLE `class_sources` (
327 `cn_source` varchar(10) NOT NULL default '',
328 `description` mediumtext,
329 `used` tinyint(4) NOT NULL default 0,
330 `class_sort_rule` varchar(10) NOT NULL default '',
331 PRIMARY KEY (`cn_source`),
332 UNIQUE KEY `cn_source_idx` (`cn_source`),
333 KEY `used_idx` (`used`),
334 CONSTRAINT `class_source_ibfk_1` FOREIGN KEY (`class_sort_rule`)
335 REFERENCES `class_sort_rules` (`class_sort_rule`)
336 ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
337 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type)
338 VALUES('DefaultClassificationSource','ddc',
339 'Default classification scheme used by the collection. E.g., Dewey, LCC, etc.', NULL,'free')");
340 $dbh->do("INSERT INTO `class_sort_rules` (`class_sort_rule`, `description`, `sort_routine`) VALUES
341 ('dewey', 'Default filing rules for DDC', 'Dewey'),
342 ('lcc', 'Default filing rules for LCC', 'LCC'),
343 ('generic', 'Generic call number filing rules', 'Generic')");
344 $dbh->do("INSERT INTO `class_sources` (`cn_source`, `description`, `used`, `class_sort_rule`) VALUES
345 ('ddc', 'Dewey Decimal Classification', 1, 'dewey'),
346 ('lcc', 'Library of Congress Classification', 1, 'lcc'),
347 ('udc', 'Universal Decimal Classification', 0, 'generic'),
348 ('sudocs', 'SuDoc Classification (U.S. GPO)', 0, 'generic'),
349 ('z', 'Other/Generic Classification Scheme', 0, 'generic')");
350 print "Upgrade to $DBversion done (classification sources added)\n";
351 SetVersion ($DBversion);
354 $DBversion = "3.00.00.013";
355 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
356 $dbh->do("CREATE TABLE `import_batches` (
357 `import_batch_id` int(11) NOT NULL auto_increment,
358 `template_id` int(11) default NULL,
359 `branchcode` varchar(10) default NULL,
360 `num_biblios` int(11) NOT NULL default 0,
361 `num_items` int(11) NOT NULL default 0,
362 `upload_timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP,
363 `overlay_action` enum('replace', 'create_new', 'use_template') NOT NULL default 'create_new',
364 `import_status` enum('staging', 'staged', 'importing', 'imported', 'reverting', 'reverted', 'cleaned') NOT NULL default 'staging',
365 `batch_type` enum('batch', 'z3950') NOT NULL default 'batch',
366 `file_name` varchar(100),
367 `comments` mediumtext,
368 PRIMARY KEY (`import_batch_id`),
369 KEY `branchcode` (`branchcode`)
370 ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
371 $dbh->do("CREATE TABLE `import_records` (
372 `import_record_id` int(11) NOT NULL auto_increment,
373 `import_batch_id` int(11) NOT NULL,
374 `branchcode` varchar(10) default NULL,
375 `record_sequence` int(11) NOT NULL default 0,
376 `upload_timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP,
377 `import_date` DATE default NULL,
378 `marc` longblob NOT NULL,
379 `marcxml` longtext NOT NULL,
380 `marcxml_old` longtext NOT NULL,
381 `record_type` enum('biblio', 'auth', 'holdings') NOT NULL default 'biblio',
382 `overlay_status` enum('no_match', 'auto_match', 'manual_match', 'match_applied') NOT NULL default 'no_match',
383 `status` enum('error', 'staged', 'imported', 'reverted', 'items_reverted') NOT NULL default 'staged',
384 `import_error` mediumtext,
385 `encoding` varchar(40) NOT NULL default '',
386 `z3950random` varchar(40) default NULL,
387 PRIMARY KEY (`import_record_id`),
388 CONSTRAINT `import_records_ifbk_1` FOREIGN KEY (`import_batch_id`)
389 REFERENCES `import_batches` (`import_batch_id`) ON DELETE CASCADE ON UPDATE CASCADE,
390 KEY `branchcode` (`branchcode`),
391 KEY `batch_sequence` (`import_batch_id`, `record_sequence`)
392 ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
393 $dbh->do("CREATE TABLE `import_record_matches` (
394 `import_record_id` int(11) NOT NULL,
395 `candidate_match_id` int(11) NOT NULL,
396 `score` int(11) NOT NULL default 0,
397 CONSTRAINT `import_record_matches_ibfk_1` FOREIGN KEY (`import_record_id`)
398 REFERENCES `import_records` (`import_record_id`) ON DELETE CASCADE ON UPDATE CASCADE,
399 KEY `record_score` (`import_record_id`, `score`)
400 ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
401 $dbh->do("CREATE TABLE `import_biblios` (
402 `import_record_id` int(11) NOT NULL,
403 `matched_biblionumber` int(11) default NULL,
404 `control_number` varchar(25) default NULL,
405 `original_source` varchar(25) default NULL,
406 `title` varchar(128) default NULL,
407 `author` varchar(80) default NULL,
408 `isbn` varchar(14) default NULL,
409 `issn` varchar(9) default NULL,
410 `has_items` tinyint(1) NOT NULL default 0,
411 CONSTRAINT `import_biblios_ibfk_1` FOREIGN KEY (`import_record_id`)
412 REFERENCES `import_records` (`import_record_id`) ON DELETE CASCADE ON UPDATE CASCADE,
413 KEY `matched_biblionumber` (`matched_biblionumber`),
414 KEY `title` (`title`),
416 ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
417 $dbh->do("CREATE TABLE `import_items` (
418 `import_items_id` int(11) NOT NULL auto_increment,
419 `import_record_id` int(11) NOT NULL,
420 `itemnumber` int(11) default NULL,
421 `branchcode` varchar(10) default NULL,
422 `status` enum('error', 'staged', 'imported', 'reverted') NOT NULL default 'staged',
423 `marcxml` longtext NOT NULL,
424 `import_error` mediumtext,
425 PRIMARY KEY (`import_items_id`),
426 CONSTRAINT `import_items_ibfk_1` FOREIGN KEY (`import_record_id`)
427 REFERENCES `import_records` (`import_record_id`) ON DELETE CASCADE ON UPDATE CASCADE,
428 KEY `itemnumber` (`itemnumber`),
429 KEY `branchcode` (`branchcode`)
430 ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
432 $dbh->do("INSERT INTO `import_batches`
433 (`overlay_action`, `import_status`, `batch_type`, `file_name`)
434 SELECT distinct 'create_new', 'staged', 'z3950', `file`
435 FROM `marc_breeding`");
437 $dbh->do("INSERT INTO `import_records`
438 (`import_batch_id`, `import_record_id`, `record_sequence`, `marc`, `record_type`, `status`,
439 `encoding`, `z3950random`, `marcxml`, `marcxml_old`)
440 SELECT `import_batch_id`, `id`, 1, `marc`, 'biblio', 'staged', `encoding`, `z3950random`, '', ''
442 JOIN `import_batches` ON (`file_name` = `file`)");
444 $dbh->do("INSERT INTO `import_biblios`
445 (`import_record_id`, `title`, `author`, `isbn`)
446 SELECT `import_record_id`, `title`, `author`, `isbn`
448 JOIN `import_records` ON (`import_record_id` = `id`)");
450 $dbh->do("UPDATE `import_batches`
451 SET `num_biblios` = (
453 FROM `import_records`
454 WHERE `import_batch_id` = `import_batches`.`import_batch_id`
457 $dbh->do("DROP TABLE `marc_breeding`");
459 print "Upgrade to $DBversion done (import_batches et al. added)\n";
460 SetVersion ($DBversion);
463 $DBversion = "3.00.00.014";
464 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
465 $dbh->do("ALTER TABLE subscription ADD lastbranch VARCHAR(4)");
466 print "Upgrade to $DBversion done (userid index added)\n";
467 SetVersion ($DBversion);
470 $DBversion = "3.00.00.015";
471 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
472 $dbh->do("CREATE TABLE `saved_sql` (
473 `id` int(11) NOT NULL auto_increment,
474 `borrowernumber` int(11) default NULL,
475 `date_created` datetime default NULL,
476 `last_modified` datetime default NULL,
478 `last_run` datetime default NULL,
479 `report_name` varchar(255) default NULL,
480 `type` varchar(255) default NULL,
483 KEY boridx (`borrowernumber`)
484 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;");
485 $dbh->do("CREATE TABLE `saved_reports` (
486 `id` int(11) NOT NULL auto_increment,
487 `report_id` int(11) default NULL,
489 `date_run` datetime default NULL,
491 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;");
492 print "Upgrade to $DBversion done (saved_sql and saved_reports added)\n";
493 SetVersion ($DBversion);
496 $DBversion = "3.00.00.016";
497 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
498 $dbh->do(" CREATE TABLE reports_dictionary (
499 id int(11) NOT NULL auto_increment,
500 name varchar(255) default NULL,
502 date_created datetime default NULL,
503 date_modified datetime default NULL,
505 area int(11) default NULL,
507 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ");
508 print "Upgrade to $DBversion done (reports_dictionary) added)\n";
509 SetVersion ($DBversion);
512 $DBversion = "3.00.00.017";
513 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
514 $dbh->do("ALTER TABLE action_logs DROP PRIMARY KEY");
515 $dbh->do("ALTER TABLE action_logs ADD KEY timestamp (timestamp,user)");
516 $dbh->do("ALTER TABLE action_logs ADD action_id INT(11) NOT NULL FIRST");
517 $dbh->do("UPDATE action_logs SET action_id = if (\@a, \@a:=\@a+1, \@a:=1)");
518 $dbh->do("ALTER TABLE action_logs MODIFY action_id INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY");
519 print "Upgrade to $DBversion done (added column to action_logs)\n";
520 SetVersion ($DBversion);
523 $DBversion = "3.00.00.018";
524 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
525 $dbh->do("ALTER TABLE `zebraqueue`
526 ADD `done` INT NOT NULL DEFAULT '0',
527 ADD `time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ;
529 print "Upgrade to $DBversion done (adding timestamp and done columns to zebraque table to improve problem tracking) added)\n";
530 SetVersion ($DBversion);
533 $DBversion = "3.00.00.019";
534 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
535 $dbh->do("ALTER TABLE biblio MODIFY biblionumber INT(11) NOT NULL AUTO_INCREMENT");
536 $dbh->do("ALTER TABLE biblioitems MODIFY biblioitemnumber INT(11) NOT NULL AUTO_INCREMENT");
537 $dbh->do("ALTER TABLE items MODIFY itemnumber INT(11) NOT NULL AUTO_INCREMENT");
538 print "Upgrade to $DBversion done (made bib/item PKs auto_increment)\n";
539 SetVersion ($DBversion);
542 $DBversion = "3.00.00.020";
543 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
544 $dbh->do("ALTER TABLE deleteditems
545 DROP KEY `delitembarcodeidx`,
546 ADD KEY `delitembarcodeidx` (`barcode`)");
547 print "Upgrade to $DBversion done (dropped uniqueness of key on deleteditems.barcode)\n";
548 SetVersion ($DBversion);
551 $DBversion = "3.00.00.021";
552 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
553 $dbh->do("ALTER TABLE items CHANGE homebranch homebranch VARCHAR(10)");
554 $dbh->do("ALTER TABLE deleteditems CHANGE homebranch homebranch VARCHAR(10)");
555 $dbh->do("ALTER TABLE statistics CHANGE branch branch VARCHAR(10)");
556 $dbh->do("ALTER TABLE subscription CHANGE lastbranch lastbranch VARCHAR(10)");
557 print "Upgrade to $DBversion done (extended missed branchcode columns to 10 chars)\n";
558 SetVersion ($DBversion);
561 $DBversion = "3.00.00.022";
562 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
563 $dbh->do("ALTER TABLE items
564 ADD `damaged` tinyint(1) default NULL AFTER notforloan");
565 $dbh->do("ALTER TABLE deleteditems
566 ADD `damaged` tinyint(1) default NULL AFTER notforloan");
567 print "Upgrade to $DBversion done (adding damaged column to items table)\n";
568 SetVersion ($DBversion);
571 $DBversion = "3.00.00.023";
572 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
573 $dbh->do("INSERT INTO `systempreferences` (variable,value,options,explanation,type)
574 VALUES ('yuipath','http://yui.yahooapis.com/2.3.1/build','Insert the path to YUI libraries','','free')");
575 print "Upgrade to $DBversion done (adding new system preference for controlling YUI path)\n";
576 SetVersion ($DBversion);
578 $DBversion = "3.00.00.024";
579 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
580 $dbh->do("ALTER TABLE biblioitems CHANGE itemtype itemtype VARCHAR(10)");
581 print "Upgrade to $DBversion done (changing itemtype to (10))\n";
582 SetVersion ($DBversion);
585 $DBversion = "3.00.00.025";
586 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
587 $dbh->do("ALTER TABLE items ADD COLUMN itype VARCHAR(10)");
588 $dbh->do("ALTER TABLE deleteditems ADD COLUMN itype VARCHAR(10) AFTER uri");
589 if(C4::Context->preference('item-level_itypes')){
590 $dbh->do('update items,biblioitems set items.itype=biblioitems.itemtype where items.biblionumber=biblioitems.biblionumber and itype is null');
592 print "Upgrade to $DBversion done (reintroduce items.itype - fill from itemtype)\n ";
593 SetVersion ($DBversion);
596 $DBversion = "3.00.00.026";
597 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
598 $dbh->do("INSERT INTO `systempreferences` (variable,value,options,explanation,type)
599 VALUES ('HomeOrHoldingBranch','homebranch','homebranch|holdingbranch','With independent branches turned on this decides whether to check the items holdingbranch or homebranch at circulatilon','choice')");
600 print "Upgrade to $DBversion done (adding new system preference for choosing whether homebranch or holdingbranch is checked in circulation)\n";
601 SetVersion ($DBversion);
604 $DBversion = "3.00.00.027";
605 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
606 $dbh->do("CREATE TABLE `marc_matchers` (
607 `matcher_id` int(11) NOT NULL auto_increment,
608 `code` varchar(10) NOT NULL default '',
609 `description` varchar(255) NOT NULL default '',
610 `record_type` varchar(10) NOT NULL default 'biblio',
611 `threshold` int(11) NOT NULL default 0,
612 PRIMARY KEY (`matcher_id`),
614 KEY `record_type` (`record_type`)
615 ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
616 $dbh->do("CREATE TABLE `matchpoints` (
617 `matcher_id` int(11) NOT NULL,
618 `matchpoint_id` int(11) NOT NULL auto_increment,
619 `search_index` varchar(30) NOT NULL default '',
620 `score` int(11) NOT NULL default 0,
621 PRIMARY KEY (`matchpoint_id`),
622 CONSTRAINT `matchpoints_ifbk_1` FOREIGN KEY (`matcher_id`)
623 REFERENCES `marc_matchers` (`matcher_id`) ON DELETE CASCADE ON UPDATE CASCADE
624 ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
625 $dbh->do("CREATE TABLE `matchpoint_components` (
626 `matchpoint_id` int(11) NOT NULL,
627 `matchpoint_component_id` int(11) NOT NULL auto_increment,
628 sequence int(11) NOT NULL default 0,
629 tag varchar(3) NOT NULL default '',
630 subfields varchar(40) NOT NULL default '',
631 offset int(4) NOT NULL default 0,
632 length int(4) NOT NULL default 0,
633 PRIMARY KEY (`matchpoint_component_id`),
634 KEY `by_sequence` (`matchpoint_id`, `sequence`),
635 CONSTRAINT `matchpoint_components_ifbk_1` FOREIGN KEY (`matchpoint_id`)
636 REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE
637 ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
638 $dbh->do("CREATE TABLE `matchpoint_component_norms` (
639 `matchpoint_component_id` int(11) NOT NULL,
640 `sequence` int(11) NOT NULL default 0,
641 `norm_routine` varchar(50) NOT NULL default '',
642 KEY `matchpoint_component_norms` (`matchpoint_component_id`, `sequence`),
643 CONSTRAINT `matchpoint_component_norms_ifbk_1` FOREIGN KEY (`matchpoint_component_id`)
644 REFERENCES `matchpoint_components` (`matchpoint_component_id`) ON DELETE CASCADE ON UPDATE CASCADE
645 ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
646 $dbh->do("CREATE TABLE `matcher_matchpoints` (
647 `matcher_id` int(11) NOT NULL,
648 `matchpoint_id` int(11) NOT NULL,
649 CONSTRAINT `matcher_matchpoints_ifbk_1` FOREIGN KEY (`matcher_id`)
650 REFERENCES `marc_matchers` (`matcher_id`) ON DELETE CASCADE ON UPDATE CASCADE,
651 CONSTRAINT `matcher_matchpoints_ifbk_2` FOREIGN KEY (`matchpoint_id`)
652 REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE
653 ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
654 $dbh->do("CREATE TABLE `matchchecks` (
655 `matcher_id` int(11) NOT NULL,
656 `matchcheck_id` int(11) NOT NULL auto_increment,
657 `source_matchpoint_id` int(11) NOT NULL,
658 `target_matchpoint_id` int(11) NOT NULL,
659 PRIMARY KEY (`matchcheck_id`),
660 CONSTRAINT `matcher_matchchecks_ifbk_1` FOREIGN KEY (`matcher_id`)
661 REFERENCES `marc_matchers` (`matcher_id`) ON DELETE CASCADE ON UPDATE CASCADE,
662 CONSTRAINT `matcher_matchchecks_ifbk_2` FOREIGN KEY (`source_matchpoint_id`)
663 REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE,
664 CONSTRAINT `matcher_matchchecks_ifbk_3` FOREIGN KEY (`target_matchpoint_id`)
665 REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE
666 ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
667 print "Upgrade to $DBversion done (added C4::Matcher serialization tables)\n ";
668 SetVersion ($DBversion);
671 $DBversion = "3.00.00.028";
672 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
673 $dbh->do("INSERT INTO `systempreferences` (variable,value,options,explanation,type)
674 VALUES ('canreservefromotherbranches','1','','With Independent branches on, can a user from one library reserve an item from another library','YesNo')");
675 print "Upgrade to $DBversion done (adding new system preference for changing reserve/holds behaviour with independent branches)\n";
676 SetVersion ($DBversion);
680 $DBversion = "3.00.00.029";
681 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
682 $dbh->do("ALTER TABLE `import_batches` ADD `matcher_id` int(11) NULL AFTER `import_batch_id`");
683 print "Upgrade to $DBversion done (adding matcher_id to import_batches)\n";
684 SetVersion ($DBversion);
687 $DBversion = "3.00.00.030";
688 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
690 CREATE TABLE services_throttle (
691 service_type varchar(10) NOT NULL default '',
692 service_count varchar(45) default NULL,
693 PRIMARY KEY (service_type)
694 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
696 $dbh->do("INSERT INTO `systempreferences` (variable,value,options,explanation,type)
697 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')");
698 $dbh->do("INSERT INTO `systempreferences` (variable,value,options,explanation,type)
699 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')");
700 $dbh->do("INSERT INTO `systempreferences` (variable,value,options,explanation,type)
701 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')");
702 $dbh->do("INSERT INTO `systempreferences` (variable,value,options,explanation,type)
703 VALUES ('XISBNDailyLimit',499,'','The xISBN Web service is free for non-commercial use when usage does not exceed 500 requests per day','free')");
704 $dbh->do("INSERT INTO `systempreferences` (variable,value,options,explanation,type)
705 VALUES ('PINESISBN',0,'','Use with FRBRizeEditions. If ON, Koha will use PINES OISBN web service in the Editions tab on the detail pages.','YesNo')");
706 $dbh->do("INSERT INTO `systempreferences` (variable,value,options,explanation,type)
707 VALUES ('ThingISBN',0,'','Use with FRBRizeEditions. If ON, Koha will use the ThingISBN web service in the Editions tab on the detail pages.','YesNo')");
708 print "Upgrade to $DBversion done (adding services throttle table and sysprefs for xISBN)\n";
709 SetVersion ($DBversion);
712 $DBversion = "3.00.00.031";
713 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
715 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('QueryStemming',1,'If ON, enables query stemming',NULL,'YesNo')");
716 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('QueryFuzzy',1,'If ON, enables fuzzy option for searches',NULL,'YesNo')");
717 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('QueryWeightFields',1,'If ON, enables field weighting',NULL,'YesNo')");
718 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('WebBasedSelfCheck',0,'If ON, enables the web-based self-check system',NULL,'YesNo')");
719 $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')");
720 $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')");
721 $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')");
722 $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')");
723 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('defaultSortOrder',NULL,'Specify the default sort order','asc|dsc|az|za','Choice')");
724 $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')");
725 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('OPACdefaultSortOrder',NULL,'Specify the default sort order','asc|dsc|za|az','Choice')");
726 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('staffClientBaseURL','','Specify the base URL of the staff client',NULL,'free')");
727 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('minPasswordLength',3,'Specify the minimum length of a patron/staff password',NULL,'free')");
728 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('noItemTypeImages',0,'If ON, disables item-type images',NULL,'YesNo')");
729 $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')");
730 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('holdCancelLength','','Specify how many days before a hold is canceled',NULL,'free')");
731 $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')");
732 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('finesMode','test','Choose the fines mode, test or production','test|production','Choice')");
733 $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')");
734 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('itemBarcodeInputFilter','','If set, allows specification of a item barcode input filter','cuecat','Choice')");
735 $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')");
736 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('URLLinkText','','Text to display as the link anchor in the OPAC',NULL,'free')");
737 $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')");
738 $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')");
739 $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')");
740 $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')");
741 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('OPACUserCSS',0,'Add CSS to be included in the OPAC',NULL,'free')");
743 print "Upgrade to $DBversion done (adding additional system preference)\n";
744 SetVersion ($DBversion);
747 $DBversion = "3.00.00.032";
748 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
749 $dbh->do("UPDATE `marc_subfield_structure` SET `kohafield` = 'items.wthdrawn' WHERE `kohafield` = 'items.withdrawn'");
750 print "Upgrade to $DBversion done (fixed MARC framework references to items.withdrawn)\n";
751 SetVersion ($DBversion);
754 $DBversion = "3.00.00.033";
755 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
756 $dbh->do("INSERT INTO `userflags` VALUES(17,'staffaccess','Modify login / permissions for staff users',0)");
757 print "Upgrade to $DBversion done (Adding permissions flag for staff member access modification. )\n";
758 SetVersion ($DBversion);
761 $DBversion = "3.00.00.034";
762 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
763 $dbh->do("ALTER TABLE `virtualshelves` ADD COLUMN `sortfield` VARCHAR(16) ");
764 print "Upgrade to $DBversion done (Adding sortfield for Virtual Shelves. )\n";
765 SetVersion ($DBversion);
768 $DBversion = "3.00.00.035";
769 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
770 $dbh->do("UPDATE marc_subfield_structure
771 SET authorised_value = 'cn_source'
772 WHERE kohafield IN ('items.cn_source', 'biblioitems.cn_source')
773 AND (authorised_value is NULL OR authorised_value = '')");
774 print "Upgrade to $DBversion done (MARC frameworks: make classification source a drop-down)\n";
775 SetVersion ($DBversion);
778 $DBversion = "3.00.00.036";
779 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
780 $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');");
781 print "Upgrade to $DBversion done (OPACItemsResultsDisplay systempreference added)\n";
782 SetVersion ($DBversion);
785 $DBversion = "3.00.00.037";
786 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
787 $dbh->do("ALTER TABLE `borrowers` ADD COLUMN `altcontactfirstname` varchar(255)");
788 $dbh->do("ALTER TABLE `borrowers` ADD COLUMN `altcontactsurname` varchar(255)");
789 $dbh->do("ALTER TABLE `borrowers` ADD COLUMN `altcontactaddress1` varchar(255)");
790 $dbh->do("ALTER TABLE `borrowers` ADD COLUMN `altcontactaddress2` varchar(255)");
791 $dbh->do("ALTER TABLE `borrowers` ADD COLUMN `altcontactaddress3` varchar(255)");
792 $dbh->do("ALTER TABLE `borrowers` ADD COLUMN `altcontactzipcode` varchar(50)");
793 $dbh->do("ALTER TABLE `borrowers` ADD COLUMN `altcontactphone` varchar(50)");
794 print "Upgrade to $DBversion done (Adding Alternative Contact Person information to borrowers table)\n";
795 SetVersion ($DBversion);
798 $DBversion = "3.00.00.038";
799 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
800 $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'");
801 $dbh->do("DELETE FROM `systempreferences` WHERE variable='hideBiblioNumber'");
802 print "Upgrade to $DBversion done ('alter finesMode systempreference, remove superfluous syspref.')\n";
803 SetVersion ($DBversion);
806 $DBversion = "3.00.00.039";
807 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
808 $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')");
809 $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')");
810 $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')");
811 # $dbh->do("DELETE FROM `systempreferences` WHERE variable='HomeOrHoldingBranch'"); # Bug #2752
812 print "Upgrade to $DBversion done ('add circ sysprefs CircControl, finesCalendar, and uppercasesurnames, and delete HomeOrHoldingBranch.')\n";
813 SetVersion ($DBversion);
816 $DBversion = "3.00.00.040";
817 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
818 $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')");
819 $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')");
820 print "Upgrade to $DBversion done ('add circ sysprefs todaysIssuesDefaultSortOrder and previousIssuesDefaultSortOrder.')\n";
821 SetVersion ($DBversion);
825 $DBversion = "3.00.00.041";
826 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
827 # Strictly speaking it is not necessary to explicitly change
828 # NULL values to 0, because the ALTER TABLE statement will do that.
829 # However, setting them first avoids a warning.
830 $dbh->do("UPDATE items SET notforloan = 0 WHERE notforloan IS NULL");
831 $dbh->do("UPDATE items SET damaged = 0 WHERE damaged IS NULL");
832 $dbh->do("UPDATE items SET itemlost = 0 WHERE itemlost IS NULL");
833 $dbh->do("UPDATE items SET wthdrawn = 0 WHERE wthdrawn IS NULL");
834 $dbh->do("ALTER TABLE items
835 MODIFY notforloan tinyint(1) NOT NULL default 0,
836 MODIFY damaged tinyint(1) NOT NULL default 0,
837 MODIFY itemlost tinyint(1) NOT NULL default 0,
838 MODIFY wthdrawn tinyint(1) NOT NULL default 0");
839 $dbh->do("UPDATE deleteditems SET notforloan = 0 WHERE notforloan IS NULL");
840 $dbh->do("UPDATE deleteditems SET damaged = 0 WHERE damaged IS NULL");
841 $dbh->do("UPDATE deleteditems SET itemlost = 0 WHERE itemlost IS NULL");
842 $dbh->do("UPDATE deleteditems SET wthdrawn = 0 WHERE wthdrawn IS NULL");
843 $dbh->do("ALTER TABLE deleteditems
844 MODIFY notforloan tinyint(1) NOT NULL default 0,
845 MODIFY damaged tinyint(1) NOT NULL default 0,
846 MODIFY itemlost tinyint(1) NOT NULL default 0,
847 MODIFY wthdrawn tinyint(1) NOT NULL default 0");
848 print "Upgrade to $DBversion done (disallow NULL in several item status columns)\n";
849 SetVersion ($DBversion);
852 $DBversion = "3.00.00.042";
853 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
854 $dbh->do("ALTER TABLE aqbooksellers CHANGE name name mediumtext NOT NULL");
855 print "Upgrade to $DBversion done (disallow NULL in aqbooksellers.name; part of fix for bug 1251)\n";
856 SetVersion ($DBversion);
859 $DBversion = "3.00.00.043";
860 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
861 $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");
862 print "Upgrade to $DBversion done (currency table: add symbol and timestamp columns)\n";
863 SetVersion ($DBversion);
866 $DBversion = "3.00.00.044";
867 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
868 $dbh->do("ALTER TABLE deletedborrowers
869 ADD `altcontactfirstname` varchar(255) default NULL,
870 ADD `altcontactsurname` varchar(255) default NULL,
871 ADD `altcontactaddress1` varchar(255) default NULL,
872 ADD `altcontactaddress2` varchar(255) default NULL,
873 ADD `altcontactaddress3` varchar(255) default NULL,
874 ADD `altcontactzipcode` varchar(50) default NULL,
875 ADD `altcontactphone` varchar(50) default NULL
877 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES
878 ('OPACBaseURL',NULL,'Specify the Base URL of the OPAC, e.g., opac.mylibrary.com, the http:// will be added automatically by Koha.',NULL,'Free'),
879 ('language','en','Set the default language in the staff client.',NULL,'Languages'),
880 ('QueryAutoTruncate',1,'If ON, query truncation is enabled by default',NULL,'YesNo'),
881 ('QueryRemoveStopwords',0,'If ON, stopwords listed in the Administration area will be removed from queries',NULL,'YesNo')
883 print "Upgrade to $DBversion done (syncing deletedborrowers table with borrowers table)\n";
884 SetVersion ($DBversion);
887 #-- http://www.w3.org/International/articles/language-tags/
890 $DBversion = "3.00.00.045";
891 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
893 CREATE TABLE language_subtag_registry (
895 type varchar(25), -- language-script-region-variant-extension-privateuse
896 description varchar(25), -- only one of the possible descriptions for ease of reference, see language_descriptions for the complete list
898 KEY `subtag` (`subtag`)
899 ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
901 #-- TODO: add suppress_scripts
902 #-- this maps three letter codes defined in iso639.2 back to their
903 #-- two letter equivilents in rfc4646 (LOC maintains iso639+)
904 $dbh->do("CREATE TABLE language_rfc4646_to_iso639 (
905 rfc4646_subtag varchar(25),
906 iso639_2_code varchar(25),
907 KEY `rfc4646_subtag` (`rfc4646_subtag`)
908 ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
910 $dbh->do("CREATE TABLE language_descriptions (
914 description varchar(255),
916 ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
918 #-- bi-directional support, keyed by script subcode
919 $dbh->do("CREATE TABLE language_script_bidi (
920 rfc4646_subtag varchar(25), -- script subtag, Arab, Hebr, etc.
921 bidi varchar(3), -- rtl ltr
922 KEY `rfc4646_subtag` (`rfc4646_subtag`)
923 ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
925 #-- BIDI Stuff, Arabic and Hebrew
926 $dbh->do("INSERT INTO language_script_bidi(rfc4646_subtag,bidi)
927 VALUES( 'Arab', 'rtl')");
928 $dbh->do("INSERT INTO language_script_bidi(rfc4646_subtag,bidi)
929 VALUES( 'Hebr', 'rtl')");
931 #-- TODO: need to map language subtags to script subtags for detection
932 #-- of bidi when script is not specified (like ar, he)
933 $dbh->do("CREATE TABLE language_script_mapping (
934 language_subtag varchar(25),
935 script_subtag varchar(25),
936 KEY `language_subtag` (`language_subtag`)
937 ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
939 #-- Default mappings between script and language subcodes
940 $dbh->do("INSERT INTO language_script_mapping(language_subtag,script_subtag)
941 VALUES( 'ar', 'Arab')");
942 $dbh->do("INSERT INTO language_script_mapping(language_subtag,script_subtag)
943 VALUES( 'he', 'Hebr')");
945 print "Upgrade to $DBversion done (adding language subtag registry and basic BiDi support NOTE: You should import the subtag registry SQL)\n";
946 SetVersion ($DBversion);
949 $DBversion = "3.00.00.046";
950 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
951 $dbh->do("ALTER TABLE `subscription` CHANGE `numberlength` `numberlength` int(11) default '0' ,
952 CHANGE `weeklength` `weeklength` int(11) default '0'");
953 $dbh->do("CREATE TABLE `serialitems` (`serialid` int(11) NOT NULL, `itemnumber` int(11) NOT NULL, UNIQUE KEY `serialididx` (`serialid`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
954 $dbh->do("INSERT INTO `serialitems` SELECT `serialid`,`itemnumber` from serial where NOT ISNULL(itemnumber) && itemnumber <> '' && itemnumber NOT LIKE '%,%'");
955 print "Upgrade to $DBversion done (Add serialitems table to link serial issues to items. )\n";
956 SetVersion ($DBversion);
959 $DBversion = "3.00.00.047";
960 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
961 $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');");
962 print "Upgrade to $DBversion done ( Added OpacRenewalAllowed syspref )\n";
963 SetVersion ($DBversion);
966 $DBversion = "3.00.00.048";
967 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
968 $dbh->do("ALTER TABLE `items` ADD `more_subfields_xml` longtext default NULL AFTER `itype`");
969 print "Upgrade to $DBversion done (added items.more_subfields_xml)\n";
970 SetVersion ($DBversion);
973 $DBversion = "3.00.00.049";
974 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
975 $dbh->do("ALTER TABLE `z3950servers` ADD `encoding` text default NULL AFTER type ");
976 print "Upgrade to $DBversion done ( Added encoding field to z3950servers table )\n";
977 SetVersion ($DBversion);
980 $DBversion = "3.00.00.050";
981 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
982 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('OpacHighlightedWords','0','If Set, query matched terms are highlighted in OPAC',NULL,'YesNo');");
983 print "Upgrade to $DBversion done ( Added OpacHighlightedWords syspref )\n";
984 SetVersion ($DBversion);
987 $DBversion = "3.00.00.051";
988 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
989 $dbh->do("UPDATE systempreferences SET explanation = 'Define the current theme for the OPAC interface.' WHERE variable = 'opacthemes';");
990 print "Upgrade to $DBversion done ( Corrected opacthemes explanation. )\n";
991 SetVersion ($DBversion);
994 $DBversion = "3.00.00.052";
995 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
996 $dbh->do("ALTER TABLE `deleteditems` ADD `more_subfields_xml` LONGTEXT DEFAULT NULL AFTER `itype`");
997 print "Upgrade to $DBversion done ( Adding missing column to deleteditems table. )\n";
998 SetVersion ($DBversion);
1001 $DBversion = "3.00.00.053";
1002 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1003 $dbh->do("CREATE TABLE `printers_profile` (
1004 `prof_id` int(4) NOT NULL auto_increment,
1005 `printername` varchar(40) NOT NULL,
1006 `tmpl_id` int(4) NOT NULL,
1007 `paper_bin` varchar(20) NOT NULL,
1008 `offset_horz` float default NULL,
1009 `offset_vert` float default NULL,
1010 `creep_horz` float default NULL,
1011 `creep_vert` float default NULL,
1012 `unit` char(20) NOT NULL default 'POINT',
1013 PRIMARY KEY (`prof_id`),
1014 UNIQUE KEY `printername` (`printername`,`tmpl_id`,`paper_bin`),
1015 CONSTRAINT `printers_profile_pnfk_1` FOREIGN KEY (`tmpl_id`) REFERENCES `labels_templates` (`tmpl_id`) ON DELETE CASCADE ON UPDATE CASCADE
1016 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ");
1017 $dbh->do("CREATE TABLE `labels_profile` (
1018 `tmpl_id` int(4) NOT NULL,
1019 `prof_id` int(4) NOT NULL,
1020 UNIQUE KEY `tmpl_id` (`tmpl_id`),
1021 UNIQUE KEY `prof_id` (`prof_id`)
1022 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ");
1023 print "Upgrade to $DBversion done ( Printer Profile tables added )\n";
1024 SetVersion ($DBversion);
1027 $DBversion = "3.00.00.054";
1028 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1029 $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';");
1030 print "Upgrade to $DBversion done ( Added another barcode autogeneration sequence to barcode.pl. )\n";
1031 SetVersion ($DBversion);
1034 $DBversion = "3.00.00.055";
1035 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1036 $dbh->do("ALTER TABLE `zebraqueue` ADD KEY `zebraqueue_lookup` (`server`, `biblio_auth_number`, `operation`, `done`)");
1037 print "Upgrade to $DBversion done ( Added index on zebraqueue. )\n";
1038 SetVersion ($DBversion);
1040 $DBversion = "3.00.00.056";
1041 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1042 if (C4::Context->preference("marcflavour") eq 'UNIMARC') {
1043 $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) ");
1045 $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) ");
1047 $dbh->do("ALTER TABLE `items` ADD `enumchron` VARCHAR(80) DEFAULT NULL;");
1048 print "Upgrade to $DBversion done ( Added item.enumchron column, and framework map to 952h )\n";
1049 SetVersion ($DBversion);
1052 $DBversion = "3.00.00.057";
1053 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1054 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('OAI-PMH','0','if ON, OAI-PMH server is enabled',NULL,'YesNo');");
1055 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('OAI-PMH:archiveID','KOHA-OAI-TEST','OAI-PMH archive identification',NULL,'Free');");
1056 $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');");
1057 $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');");
1058 $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');");
1059 SetVersion ($DBversion);
1062 $DBversion = "3.00.00.058";
1063 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1064 $dbh->do("ALTER TABLE `opac_news`
1065 CHANGE `lang` `lang` VARCHAR( 25 )
1067 COLLATE utf8_general_ci
1068 NOT NULL default ''");
1069 print "Upgrade to $DBversion done ( lang field in opac_news made longer )\n";
1070 SetVersion ($DBversion);
1073 $DBversion = "3.00.00.059";
1074 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1076 $dbh->do("CREATE TABLE IF NOT EXISTS `labels_templates` (
1077 `tmpl_id` int(4) NOT NULL auto_increment,
1078 `tmpl_code` char(100) default '',
1079 `tmpl_desc` char(100) default '',
1080 `page_width` float default '0',
1081 `page_height` float default '0',
1082 `label_width` float default '0',
1083 `label_height` float default '0',
1084 `topmargin` float default '0',
1085 `leftmargin` float default '0',
1086 `cols` int(2) default '0',
1087 `rows` int(2) default '0',
1088 `colgap` float default '0',
1089 `rowgap` float default '0',
1090 `active` int(1) default NULL,
1091 `units` char(20) default 'PX',
1092 `fontsize` int(4) NOT NULL default '3',
1093 PRIMARY KEY (`tmpl_id`)
1094 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;");
1095 $dbh->do("CREATE TABLE IF NOT EXISTS `printers_profile` (
1096 `prof_id` int(4) NOT NULL auto_increment,
1097 `printername` varchar(40) NOT NULL,
1098 `tmpl_id` int(4) NOT NULL,
1099 `paper_bin` varchar(20) NOT NULL,
1100 `offset_horz` float default NULL,
1101 `offset_vert` float default NULL,
1102 `creep_horz` float default NULL,
1103 `creep_vert` float default NULL,
1104 `unit` char(20) NOT NULL default 'POINT',
1105 PRIMARY KEY (`prof_id`),
1106 UNIQUE KEY `printername` (`printername`,`tmpl_id`,`paper_bin`),
1107 CONSTRAINT `printers_profile_pnfk_1` FOREIGN KEY (`tmpl_id`) REFERENCES `labels_templates` (`tmpl_id`) ON DELETE CASCADE ON UPDATE CASCADE
1108 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ");
1109 print "Upgrade to $DBversion done ( Added labels_templates table if it did not exist. )\n";
1110 SetVersion ($DBversion);
1113 $DBversion = "3.00.00.060";
1114 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1115 $dbh->do("CREATE TABLE IF NOT EXISTS `patronimage` (
1116 `cardnumber` varchar(16) NOT NULL,
1117 `mimetype` varchar(15) NOT NULL,
1118 `imagefile` mediumblob NOT NULL,
1119 PRIMARY KEY (`cardnumber`),
1120 CONSTRAINT `patronimage_fk1` FOREIGN KEY (`cardnumber`) REFERENCES `borrowers` (`cardnumber`) ON DELETE CASCADE ON UPDATE CASCADE
1121 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;");
1122 print "Upgrade to $DBversion done ( Added patronimage table. )\n";
1123 SetVersion ($DBversion);
1126 $DBversion = "3.00.00.061";
1127 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1128 $dbh->do("ALTER TABLE labels_templates ADD COLUMN font char(10) NOT NULL DEFAULT 'TR';");
1129 print "Upgrade to $DBversion done ( Added font column to labels_templates )\n";
1130 SetVersion ($DBversion);
1133 $DBversion = "3.00.00.062";
1134 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1135 $dbh->do("CREATE TABLE `old_issues` (
1136 `borrowernumber` int(11) default NULL,
1137 `itemnumber` int(11) default NULL,
1138 `date_due` date default NULL,
1139 `branchcode` varchar(10) default NULL,
1140 `issuingbranch` varchar(18) default NULL,
1141 `returndate` date default NULL,
1142 `lastreneweddate` date default NULL,
1143 `return` varchar(4) default NULL,
1144 `renewals` tinyint(4) default NULL,
1145 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1146 `issuedate` date default NULL,
1147 KEY `old_issuesborridx` (`borrowernumber`),
1148 KEY `old_issuesitemidx` (`itemnumber`),
1149 KEY `old_bordate` (`borrowernumber`,`timestamp`),
1150 CONSTRAINT `old_issues_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`)
1151 ON DELETE SET NULL ON UPDATE SET NULL,
1152 CONSTRAINT `old_issues_ibfk_2` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`)
1153 ON DELETE SET NULL ON UPDATE SET NULL
1154 ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
1155 $dbh->do("CREATE TABLE `old_reserves` (
1156 `borrowernumber` int(11) default NULL,
1157 `reservedate` date default NULL,
1158 `biblionumber` int(11) default NULL,
1159 `constrainttype` varchar(1) default NULL,
1160 `branchcode` varchar(10) default NULL,
1161 `notificationdate` date default NULL,
1162 `reminderdate` date default NULL,
1163 `cancellationdate` date default NULL,
1164 `reservenotes` mediumtext,
1165 `priority` smallint(6) default NULL,
1166 `found` varchar(1) default NULL,
1167 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1168 `itemnumber` int(11) default NULL,
1169 `waitingdate` date default NULL,
1170 KEY `old_reserves_borrowernumber` (`borrowernumber`),
1171 KEY `old_reserves_biblionumber` (`biblionumber`),
1172 KEY `old_reserves_itemnumber` (`itemnumber`),
1173 KEY `old_reserves_branchcode` (`branchcode`),
1174 CONSTRAINT `old_reserves_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`)
1175 ON DELETE SET NULL ON UPDATE SET NULL,
1176 CONSTRAINT `old_reserves_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`)
1177 ON DELETE SET NULL ON UPDATE SET NULL,
1178 CONSTRAINT `old_reserves_ibfk_3` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`)
1179 ON DELETE SET NULL ON UPDATE SET NULL
1180 ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
1182 # move closed transactions to old_* tables
1183 $dbh->do("INSERT INTO old_issues SELECT * FROM issues WHERE returndate IS NOT NULL");
1184 $dbh->do("DELETE FROM issues WHERE returndate IS NOT NULL");
1185 $dbh->do("INSERT INTO old_reserves SELECT * FROM reserves WHERE cancellationdate IS NOT NULL OR found = 'F'");
1186 $dbh->do("DELETE FROM reserves WHERE cancellationdate IS NOT NULL OR found = 'F'");
1188 print "Upgrade to $DBversion done ( Added old_issues and old_reserves tables )\n";
1189 SetVersion ($DBversion);
1192 $DBversion = "3.00.00.063";
1193 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1194 $dbh->do("ALTER TABLE deleteditems
1195 CHANGE COLUMN booksellerid booksellerid MEDIUMTEXT DEFAULT NULL,
1196 ADD COLUMN enumchron VARCHAR(80) DEFAULT NULL AFTER more_subfields_xml,
1197 ADD COLUMN copynumber SMALLINT(6) DEFAULT NULL AFTER enumchron;");
1198 $dbh->do("ALTER TABLE items
1199 CHANGE COLUMN booksellerid booksellerid MEDIUMTEXT,
1200 ADD COLUMN copynumber SMALLINT(6) DEFAULT NULL AFTER enumchron;");
1201 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";
1202 SetVersion ($DBversion);
1205 $DBversion = "3.00.00.064";
1206 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1207 $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');");
1208 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('AWSAccessKeyID','','See: http://aws.amazon.com','','free');");
1209 $dbh->do("DELETE FROM `systempreferences` WHERE variable='AmazonDevKey';");
1210 $dbh->do("DELETE FROM `systempreferences` WHERE variable='XISBNAmazonSimilarItems';");
1211 $dbh->do("DELETE FROM `systempreferences` WHERE variable='OPACXISBNAmazonSimilarItems';");
1212 print "Upgrade to $DBversion done (IMPORTANT: Upgrading to Amazon.com Associates Web Service 4.0 ) \n";
1213 SetVersion ($DBversion);
1216 $DBversion = "3.00.00.065";
1217 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1218 $dbh->do("CREATE TABLE `patroncards` (
1219 `cardid` int(11) NOT NULL auto_increment,
1220 `batch_id` varchar(10) NOT NULL default '1',
1221 `borrowernumber` int(11) NOT NULL,
1222 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1223 PRIMARY KEY (`cardid`),
1224 KEY `patroncards_ibfk_1` (`borrowernumber`),
1225 CONSTRAINT `patroncards_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE
1226 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;");
1227 print "Upgrade to $DBversion done (Adding patroncards table for patroncards generation feature. ) \n";
1228 SetVersion ($DBversion);
1231 $DBversion = "3.00.00.066";
1232 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1233 $dbh->do("ALTER TABLE `virtualshelfcontents` MODIFY `dateadded` timestamp NOT NULL
1234 DEFAULT CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP;
1236 print "Upgrade to $DBversion done (fix for bug 1873: virtualshelfcontents dateadded column empty. ) \n";
1237 SetVersion ($DBversion);
1240 $DBversion = "3.00.00.067";
1241 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1242 $dbh->do("UPDATE systempreferences SET explanation = 'Enable patron images for the Staff Client', type = 'YesNo' WHERE variable = 'patronimages'");
1243 print "Upgrade to $DBversion done (Updating patronimages syspref to reflect current kohastructure.sql. ) \n";
1244 SetVersion ($DBversion);
1247 $DBversion = "3.00.00.068";
1248 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1249 $dbh->do("CREATE TABLE `permissions` (
1250 `module_bit` int(11) NOT NULL DEFAULT 0,
1251 `code` varchar(30) DEFAULT NULL,
1252 `description` varchar(255) DEFAULT NULL,
1253 PRIMARY KEY (`module_bit`, `code`),
1254 CONSTRAINT `permissions_ibfk_1` FOREIGN KEY (`module_bit`) REFERENCES `userflags` (`bit`)
1255 ON DELETE CASCADE ON UPDATE CASCADE
1256 ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
1257 $dbh->do("CREATE TABLE `user_permissions` (
1258 `borrowernumber` int(11) NOT NULL DEFAULT 0,
1259 `module_bit` int(11) NOT NULL DEFAULT 0,
1260 `code` varchar(30) DEFAULT NULL,
1261 CONSTRAINT `user_permissions_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`)
1262 ON DELETE CASCADE ON UPDATE CASCADE,
1263 CONSTRAINT `user_permissions_ibfk_2` FOREIGN KEY (`module_bit`, `code`)
1264 REFERENCES `permissions` (`module_bit`, `code`)
1265 ON DELETE CASCADE ON UPDATE CASCADE
1266 ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
1268 $dbh->do("INSERT INTO permissions (module_bit, code, description) VALUES
1269 (13, 'edit_news', 'Write news for the OPAC and staff interfaces'),
1270 (13, 'label_creator', 'Create printable labels and barcodes from catalog and patron data'),
1271 (13, 'edit_calendar', 'Define days when the library is closed'),
1272 (13, 'moderate_comments', 'Moderate patron comments'),
1273 (13, 'edit_notices', 'Define notices'),
1274 (13, 'edit_notice_status_triggers', 'Set notice/status triggers for overdue items'),
1275 (13, 'view_system_logs', 'Browse the system logs'),
1276 (13, 'inventory', 'Perform inventory (stocktaking) of your catalogue'),
1277 (13, 'stage_marc_import', 'Stage MARC records into the reservoir'),
1278 (13, 'manage_staged_marc', 'Managed staged MARC records, including completing and reversing imports'),
1279 (13, 'export_catalog', 'Export bibliographic and holdings data'),
1280 (13, 'import_patrons', 'Import patron data'),
1281 (13, 'delete_anonymize_patrons', 'Delete old borrowers and anonymize circulation history (deletes borrower reading history)'),
1282 (13, 'batch_upload_patron_images', 'Upload patron images in batch or one at a time'),
1283 (13, 'schedule_tasks', 'Schedule tasks to run')");
1285 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('GranularPermissions','0','Use detailed staff user permissions',NULL,'YesNo')");
1287 print "Upgrade to $DBversion done (adding permissions and user_permissions tables and GranularPermissions syspref) \n";
1288 SetVersion ($DBversion);
1290 $DBversion = "3.00.00.069";
1291 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1292 $dbh->do("ALTER TABLE labels_conf CHANGE COLUMN class classification int(1) DEFAULT NULL;");
1293 print "Upgrade to $DBversion done ( Correcting columname in labels_conf )\n";
1294 SetVersion ($DBversion);
1297 $DBversion = "3.00.00.070";
1298 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1299 $sth = $dbh->prepare("SELECT value FROM systempreferences WHERE variable='yuipath'");
1301 my ($value) = $sth->fetchrow;
1302 $value =~ s/2.3.1/2.5.1/;
1303 $dbh->do("UPDATE systempreferences SET value='$value' WHERE variable='yuipath';");
1304 print "Update yuipath syspref to 2.5.1 if necessary\n";
1305 SetVersion ($DBversion);
1308 $DBversion = "3.00.00.071";
1309 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1310 $dbh->do(" ALTER TABLE `subscription` ADD `serialsadditems` TINYINT( 1 ) NOT NULL DEFAULT '0';");
1311 # fill the new field with the previous systempreference value, then drop the syspref
1312 my $sth = $dbh->prepare("SELECT value FROM systempreferences WHERE variable='serialsadditems'");
1314 my ($serialsadditems) = $sth->fetchrow();
1315 $dbh->do("UPDATE subscription SET serialsadditems=$serialsadditems");
1316 $dbh->do("DELETE FROM systempreferences WHERE variable='serialsadditems'");
1317 print "Upgrade to $DBversion done ( moving serialsadditems from syspref to subscription )\n";
1318 SetVersion ($DBversion);
1321 $DBversion = "3.00.00.072";
1322 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1323 $dbh->do("ALTER TABLE labels_conf ADD COLUMN formatstring mediumtext DEFAULT NULL AFTER printingtype");
1324 print "Upgrade to $DBversion done ( Adding format string to labels generator. )\n";
1325 SetVersion ($DBversion);
1328 $DBversion = "3.00.00.073";
1329 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1330 $dbh->do("DROP TABLE IF EXISTS `tags_all`;");
1332 CREATE TABLE `tags_all` (
1333 `tag_id` int(11) NOT NULL auto_increment,
1334 `borrowernumber` int(11) NOT NULL,
1335 `biblionumber` int(11) NOT NULL,
1336 `term` varchar(255) NOT NULL,
1337 `language` int(4) default NULL,
1338 `date_created` datetime NOT NULL,
1339 PRIMARY KEY (`tag_id`),
1340 KEY `tags_borrowers_fk_1` (`borrowernumber`),
1341 KEY `tags_biblionumber_fk_1` (`biblionumber`),
1342 CONSTRAINT `tags_borrowers_fk_1` FOREIGN KEY (`borrowernumber`)
1343 REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1344 CONSTRAINT `tags_biblionumber_fk_1` FOREIGN KEY (`biblionumber`)
1345 REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE
1346 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1348 $dbh->do("DROP TABLE IF EXISTS `tags_approval`;");
1350 CREATE TABLE `tags_approval` (
1351 `term` varchar(255) NOT NULL,
1352 `approved` int(1) NOT NULL default '0',
1353 `date_approved` datetime default NULL,
1354 `approved_by` int(11) default NULL,
1355 `weight_total` int(9) NOT NULL default '1',
1356 PRIMARY KEY (`term`),
1357 KEY `tags_approval_borrowers_fk_1` (`approved_by`),
1358 CONSTRAINT `tags_approval_borrowers_fk_1` FOREIGN KEY (`approved_by`)
1359 REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE
1360 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1362 $dbh->do("DROP TABLE IF EXISTS `tags_index`;");
1364 CREATE TABLE `tags_index` (
1365 `term` varchar(255) NOT NULL,
1366 `biblionumber` int(11) NOT NULL,
1367 `weight` int(9) NOT NULL default '1',
1368 PRIMARY KEY (`term`,`biblionumber`),
1369 KEY `tags_index_biblionumber_fk_1` (`biblionumber`),
1370 CONSTRAINT `tags_index_term_fk_1` FOREIGN KEY (`term`)
1371 REFERENCES `tags_approval` (`term`) ON DELETE CASCADE ON UPDATE CASCADE,
1372 CONSTRAINT `tags_index_biblionumber_fk_1` FOREIGN KEY (`biblionumber`)
1373 REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE
1374 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1377 INSERT INTO `systempreferences` VALUES
1378 ('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=',''),
1379 ('BakerTaylorEnabled','0','','Enable or disable all Baker & Taylor features.','YesNo'),
1380 ('BakerTaylorPassword','','','Baker & Taylor Password for Content Cafe (external content)','Textarea'),
1381 ('BakerTaylorUsername','','','Baker & Taylor Username for Content Cafe (external content)','Textarea'),
1382 ('TagsEnabled','1','','Enables or disables all tagging features. This is the main switch for tags.','YesNo'),
1383 ('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.',''),
1384 ('TagsInputOnDetail','1','','Allow users to input tags from the detail page.', 'YesNo'),
1385 ('TagsInputOnList', '0','','Allow users to input tags from the search results list.', 'YesNo'),
1386 ('TagsModeration', NULL,'','Require tags from patrons to be approved before becoming visible.','YesNo'),
1387 ('TagsShowOnDetail','10','','Number of tags to display on detail page. 0 is off.', 'Integer'),
1388 ('TagsShowOnList', '6','','Number of tags to display on search results list. 0 is off.','Integer')
1390 print "Upgrade to $DBversion done (Baker/Taylor,Tags: sysprefs and tables (tags_all, tags_index, tags_approval)) \n";
1391 SetVersion ($DBversion);
1394 $DBversion = "3.00.00.074";
1395 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1396 $dbh->do( q(update itemtypes set imageurl = concat( 'npl/', imageurl )
1397 where imageurl not like 'http%'
1398 and imageurl is not NULL
1399 and imageurl != '') );
1400 print "Upgrade to $DBversion done (updating imagetype.imageurls to reflect new icon locations.)\n";
1401 SetVersion ($DBversion);
1404 $DBversion = "3.00.00.075";
1405 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1406 $dbh->do( q(alter table authorised_values add imageurl varchar(200) default NULL) );
1407 print "Upgrade to $DBversion done (adding imageurl field to authorised_values table)\n";
1408 SetVersion ($DBversion);
1411 $DBversion = "3.00.00.076";
1412 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1413 $dbh->do("ALTER TABLE import_batches
1414 ADD COLUMN nomatch_action enum('create_new', 'ignore') NOT NULL default 'create_new' AFTER overlay_action");
1415 $dbh->do("ALTER TABLE import_batches
1416 ADD COLUMN item_action enum('always_add', 'add_only_for_matches', 'add_only_for_new', 'ignore')
1417 NOT NULL default 'always_add' AFTER nomatch_action");
1418 $dbh->do("ALTER TABLE import_batches
1419 MODIFY overlay_action enum('replace', 'create_new', 'use_template', 'ignore')
1420 NOT NULL default 'create_new'");
1421 $dbh->do("ALTER TABLE import_records
1422 MODIFY status enum('error', 'staged', 'imported', 'reverted', 'items_reverted',
1423 'ignored') NOT NULL default 'staged'");
1424 $dbh->do("ALTER TABLE import_items
1425 MODIFY status enum('error', 'staged', 'imported', 'reverted', 'ignored') NOT NULL default 'staged'");
1427 print "Upgrade to $DBversion done (changes to import_batches and import_records)\n";
1428 SetVersion ($DBversion);
1431 $DBversion = "3.00.00.077";
1432 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1433 # drop these tables only if they exist and none of them are empty
1434 # these tables are not defined in the packaged 2.2.9, but since it is believed
1435 # that at least one library may be using them in a post-2.2.9 but pre-3.0 Koha,
1436 # some care is taken.
1437 my ($print_error) = $dbh->{PrintError};
1438 $dbh->{PrintError} = 0;
1439 my ($raise_error) = $dbh->{RaiseError};
1440 $dbh->{RaiseError} = 1;
1444 eval { $count = $dbh->do("SELECT 1 FROM categorytable"); };
1448 eval { $count = $dbh->do("SELECT 1 FROM mediatypetable"); };
1452 eval { $count = $dbh->do("SELECT 1 FROM subcategorytable"); };
1458 $dbh->do("DROP TABLE IF EXISTS `categorytable`");
1459 $dbh->do("DROP TABLE IF EXISTS `mediatypetable`");
1460 $dbh->do("DROP TABLE IF EXISTS `subcategorytable`");
1463 $dbh->{PrintError} = $print_error;
1464 $dbh->{RaiseError} = $raise_error;
1465 print "Upgrade to $DBversion done (drop categorytable, subcategorytable, and mediatypetable)\n";
1466 SetVersion ($DBversion);
1469 $DBversion = "3.00.00.078";
1470 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1471 my ($print_error) = $dbh->{PrintError};
1472 $dbh->{PrintError} = 0;
1474 unless ($dbh->do("SELECT 1 FROM browser")) {
1475 $dbh->{PrintError} = $print_error;
1476 $dbh->do("CREATE TABLE `browser` (
1477 `level` int(11) NOT NULL,
1478 `classification` varchar(20) NOT NULL,
1479 `description` varchar(255) NOT NULL,
1480 `number` bigint(20) NOT NULL,
1481 `endnode` tinyint(4) NOT NULL
1482 ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
1484 $dbh->{PrintError} = $print_error;
1485 print "Upgrade to $DBversion done (add browser table if not already present)\n";
1486 SetVersion ($DBversion);
1489 $DBversion = "3.00.00.079";
1490 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1491 my ($print_error) = $dbh->{PrintError};
1492 $dbh->{PrintError} = 0;
1494 $dbh->do("INSERT INTO `systempreferences` (variable, value,options,type, explanation)VALUES
1495 ('AddPatronLists','categorycode','categorycode|category_type','Choice','Allow user to choose what list to pick up from when adding patrons')");
1496 print "Upgrade to $DBversion done (add browser table if not already present)\n";
1497 SetVersion ($DBversion);
1500 $DBversion = "3.00.00.080";
1501 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1502 $dbh->do("ALTER TABLE subscription CHANGE monthlength monthlength int(11) default '0'");
1503 $dbh->do("ALTER TABLE deleteditems MODIFY marc LONGBLOB AFTER copynumber");
1504 $dbh->do("ALTER TABLE aqbooksellers CHANGE name name mediumtext NOT NULL");
1505 print "Upgrade to $DBversion done (catch up on DB schema changes since alpha and beta)\n";
1506 SetVersion ($DBversion);
1509 $DBversion = "3.00.00.081";
1510 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1511 $dbh->do("CREATE TABLE `borrower_attribute_types` (
1512 `code` varchar(10) NOT NULL,
1513 `description` varchar(255) NOT NULL,
1514 `repeatable` tinyint(1) NOT NULL default 0,
1515 `unique_id` tinyint(1) NOT NULL default 0,
1516 `opac_display` tinyint(1) NOT NULL default 0,
1517 `password_allowed` tinyint(1) NOT NULL default 0,
1518 `staff_searchable` tinyint(1) NOT NULL default 0,
1519 `authorised_value_category` varchar(10) default NULL,
1520 PRIMARY KEY (`code`)
1521 ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
1522 $dbh->do("CREATE TABLE `borrower_attributes` (
1523 `borrowernumber` int(11) NOT NULL,
1524 `code` varchar(10) NOT NULL,
1525 `attribute` varchar(30) default NULL,
1526 `password` varchar(30) default NULL,
1527 KEY `borrowernumber` (`borrowernumber`),
1528 KEY `code_attribute` (`code`, `attribute`),
1529 CONSTRAINT `borrower_attributes_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`)
1530 ON DELETE CASCADE ON UPDATE CASCADE,
1531 CONSTRAINT `borrower_attributes_ibfk_2` FOREIGN KEY (`code`) REFERENCES `borrower_attribute_types` (`code`)
1532 ON DELETE CASCADE ON UPDATE CASCADE
1533 ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
1534 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('ExtendedPatronAttributes','0','Use extended patron IDs and attributes',NULL,'YesNo')");
1535 print "Upgrade to $DBversion done (added borrower_attributes and borrower_attribute_types)\n";
1536 SetVersion ($DBversion);
1539 $DBversion = "3.00.00.082";
1540 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1541 $dbh->do( q(alter table accountlines add column lastincrement decimal(28,6) default NULL) );
1542 print "Upgrade to $DBversion done (adding lastincrement column to accountlines table)\n";
1543 SetVersion ($DBversion);
1546 $DBversion = "3.00.00.083";
1547 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1548 $dbh->do( qq(UPDATE systempreferences SET value='local' where variable='yuipath' and value like "%/intranet-tmpl/prog/%"));
1549 print "Upgrade to $DBversion done (Changing yuipath behaviour in managing a local value)\n";
1550 SetVersion ($DBversion);
1552 $DBversion = "3.00.00.084";
1553 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1554 $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')");
1555 $dbh->do("INSERT INTO systempreferences (variable,value,explanation,options,type) VALUES('GoogleJackets','0','if ON, displays jacket covers from Google Books API',NULL,'YesNo')");
1556 print "Upgrade to $DBversion done (add new sysprefs)\n";
1557 SetVersion ($DBversion);
1560 $DBversion = "3.00.00.085";
1561 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1562 if (C4::Context->preference("marcflavour") eq 'MARC21') {
1563 $dbh->do("UPDATE marc_subfield_structure SET tab = 0 WHERE tab = 9 AND tagfield = '037'");
1564 $dbh->do("UPDATE marc_subfield_structure SET tab = 1 WHERE tab = 6 AND tagfield in ('100', '110', '111', '130')");
1565 $dbh->do("UPDATE marc_subfield_structure SET tab = 2 WHERE tab = 6 AND tagfield in ('240', '243')");
1566 $dbh->do("UPDATE marc_subfield_structure SET tab = 4 WHERE tab = 6 AND tagfield in ('400', '410', '411', '440')");
1567 $dbh->do("UPDATE marc_subfield_structure SET tab = 5 WHERE tab = 9 AND tagfield = '584'");
1568 $dbh->do("UPDATE marc_subfield_structure SET tab = 7 WHERE tab = -6 AND tagfield = '760'");
1570 print "Upgrade to $DBversion done (move editing tab of various MARC21 subfields)\n";
1571 SetVersion ($DBversion);
1574 $DBversion = "3.00.00.086";
1575 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1577 "CREATE TABLE `tmp_holdsqueue` (
1578 `biblionumber` int(11) default NULL,
1579 `itemnumber` int(11) default NULL,
1580 `barcode` varchar(20) default NULL,
1581 `surname` mediumtext NOT NULL,
1584 `borrowernumber` int(11) NOT NULL,
1585 `cardnumber` varchar(16) default NULL,
1586 `reservedate` date default NULL,
1588 `itemcallnumber` varchar(30) default NULL,
1589 `holdingbranch` varchar(10) default NULL,
1590 `pickbranch` varchar(10) default NULL,
1592 ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
1594 $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')");
1595 $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')");
1597 print "Upgrade to $DBversion done (Table structure for table `tmp_holdsqueue`)\n";
1598 SetVersion ($DBversion);
1601 $DBversion = "3.00.00.087";
1602 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1603 $dbh->do("INSERT INTO `systempreferences` VALUES ('AutoEmailOpacUser','0','','Sends notification emails containing new account details to patrons - when account is created.','YesNo')" );
1604 $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')");
1605 print "Upgrade to $DBversion done (added 2 new 'AutoEmailOpacUser' sysprefs)\n";
1606 SetVersion ($DBversion);
1609 $DBversion = "3.00.00.088";
1610 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1611 $dbh->do("INSERT INTO `systempreferences` (variable,value,options,explanation,type) VALUES ('OPACShelfBrowser','1','','Enable/disable Shelf Browser on item details page','YesNo')");
1612 $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')");
1613 $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')");
1614 $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')");
1615 print "Upgrade to $DBversion done (added 2 new 'AutoEmailOpacUser' sysprefs)\n";
1616 SetVersion ($DBversion);
1619 $DBversion = "3.00.00.089";
1620 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1621 $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')");
1622 print "Upgrade to $DBversion done (added new AdvancedSearchTypes syspref)\n";
1623 SetVersion ($DBversion);
1626 $DBversion = "3.00.00.090";
1627 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1629 CREATE TABLE `branch_borrower_circ_rules` (
1630 `branchcode` VARCHAR(10) NOT NULL,
1631 `categorycode` VARCHAR(10) NOT NULL,
1632 `maxissueqty` int(4) default NULL,
1633 PRIMARY KEY (`categorycode`, `branchcode`),
1634 CONSTRAINT `branch_borrower_circ_rules_ibfk_1` FOREIGN KEY (`categorycode`) REFERENCES `categories` (`categorycode`)
1635 ON DELETE CASCADE ON UPDATE CASCADE,
1636 CONSTRAINT `branch_borrower_circ_rules_ibfk_2` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`)
1637 ON DELETE CASCADE ON UPDATE CASCADE
1638 ) ENGINE=InnoDB DEFAULT CHARSET=utf8
1641 CREATE TABLE `default_borrower_circ_rules` (
1642 `categorycode` VARCHAR(10) NOT NULL,
1643 `maxissueqty` int(4) default NULL,
1644 PRIMARY KEY (`categorycode`),
1645 CONSTRAINT `borrower_borrower_circ_rules_ibfk_1` FOREIGN KEY (`categorycode`) REFERENCES `categories` (`categorycode`)
1646 ON DELETE CASCADE ON UPDATE CASCADE
1647 ) ENGINE=InnoDB DEFAULT CHARSET=utf8
1650 CREATE TABLE `default_branch_circ_rules` (
1651 `branchcode` VARCHAR(10) NOT NULL,
1652 `maxissueqty` int(4) default NULL,
1653 PRIMARY KEY (`branchcode`),
1654 CONSTRAINT `default_branch_circ_rules_ibfk_1` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`)
1655 ON DELETE CASCADE ON UPDATE CASCADE
1656 ) ENGINE=InnoDB DEFAULT CHARSET=utf8
1659 CREATE TABLE `default_circ_rules` (
1660 `singleton` enum('singleton') NOT NULL default 'singleton',
1661 `maxissueqty` int(4) default NULL,
1662 PRIMARY KEY (`singleton`)
1663 ) ENGINE=InnoDB DEFAULT CHARSET=utf8
1665 print "Upgrade to $DBversion done (added several circ rules tables)\n";
1666 SetVersion ($DBversion);
1670 $DBversion = "3.00.00.091";
1671 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1672 $dbh->do(<<'END_SQL');
1673 ALTER TABLE borrowers
1674 ADD `smsalertnumber` varchar(50) default NULL
1677 $dbh->do(<<'END_SQL');
1678 CREATE TABLE `message_attributes` (
1679 `message_attribute_id` int(11) NOT NULL auto_increment,
1680 `message_name` varchar(20) NOT NULL default '',
1681 `takes_days` tinyint(1) NOT NULL default '0',
1682 PRIMARY KEY (`message_attribute_id`),
1683 UNIQUE KEY `message_name` (`message_name`)
1684 ) ENGINE=InnoDB DEFAULT CHARSET=utf8
1687 $dbh->do(<<'END_SQL');
1688 CREATE TABLE `message_transport_types` (
1689 `message_transport_type` varchar(20) NOT NULL,
1690 PRIMARY KEY (`message_transport_type`)
1691 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1694 $dbh->do(<<'END_SQL');
1695 CREATE TABLE `message_transports` (
1696 `message_attribute_id` int(11) NOT NULL,
1697 `message_transport_type` varchar(20) NOT NULL,
1698 `is_digest` tinyint(1) NOT NULL default '0',
1699 `letter_module` varchar(20) NOT NULL default '',
1700 `letter_code` varchar(20) NOT NULL default '',
1701 PRIMARY KEY (`message_attribute_id`,`message_transport_type`,`is_digest`),
1702 KEY `message_transport_type` (`message_transport_type`),
1703 KEY `letter_module` (`letter_module`,`letter_code`),
1704 CONSTRAINT `message_transports_ibfk_1` FOREIGN KEY (`message_attribute_id`) REFERENCES `message_attributes` (`message_attribute_id`) ON DELETE CASCADE ON UPDATE CASCADE,
1705 CONSTRAINT `message_transports_ibfk_2` FOREIGN KEY (`message_transport_type`) REFERENCES `message_transport_types` (`message_transport_type`) ON DELETE CASCADE ON UPDATE CASCADE,
1706 CONSTRAINT `message_transports_ibfk_3` FOREIGN KEY (`letter_module`, `letter_code`) REFERENCES `letter` (`module`, `code`) ON DELETE CASCADE ON UPDATE CASCADE
1707 ) ENGINE=InnoDB DEFAULT CHARSET=utf8
1710 $dbh->do(<<'END_SQL');
1711 CREATE TABLE `borrower_message_preferences` (
1712 `borrower_message_preference_id` int(11) NOT NULL auto_increment,
1713 `borrowernumber` int(11) NOT NULL default '0',
1714 `message_attribute_id` int(11) default '0',
1715 `days_in_advance` int(11) default '0',
1716 `wants_digets` tinyint(1) NOT NULL default '0',
1717 PRIMARY KEY (`borrower_message_preference_id`),
1718 KEY `borrowernumber` (`borrowernumber`),
1719 KEY `message_attribute_id` (`message_attribute_id`),
1720 CONSTRAINT `borrower_message_preferences_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1721 CONSTRAINT `borrower_message_preferences_ibfk_2` FOREIGN KEY (`message_attribute_id`) REFERENCES `message_attributes` (`message_attribute_id`) ON DELETE CASCADE ON UPDATE CASCADE
1722 ) ENGINE=InnoDB DEFAULT CHARSET=utf8
1725 $dbh->do(<<'END_SQL');
1726 CREATE TABLE `borrower_message_transport_preferences` (
1727 `borrower_message_preference_id` int(11) NOT NULL default '0',
1728 `message_transport_type` varchar(20) NOT NULL default '0',
1729 PRIMARY KEY (`borrower_message_preference_id`,`message_transport_type`),
1730 KEY `message_transport_type` (`message_transport_type`),
1731 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,
1732 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
1733 ) ENGINE=InnoDB DEFAULT CHARSET=utf8
1736 $dbh->do(<<'END_SQL');
1737 CREATE TABLE `message_queue` (
1738 `message_id` int(11) NOT NULL auto_increment,
1739 `borrowernumber` int(11) NOT NULL,
1742 `message_transport_type` varchar(20) NOT NULL,
1743 `status` enum('sent','pending','failed','deleted') NOT NULL default 'pending',
1744 `time_queued` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1745 KEY `message_id` (`message_id`),
1746 KEY `borrowernumber` (`borrowernumber`),
1747 KEY `message_transport_type` (`message_transport_type`),
1748 CONSTRAINT `messageq_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1749 CONSTRAINT `messageq_ibfk_2` FOREIGN KEY (`message_transport_type`) REFERENCES `message_transport_types` (`message_transport_type`) ON DELETE RESTRICT ON UPDATE CASCADE
1750 ) ENGINE=InnoDB DEFAULT CHARSET=utf8
1753 $dbh->do(<<'END_SQL');
1754 INSERT INTO `systempreferences`
1755 (variable,value,explanation,options,type)
1757 ('EnhancedMessagingPreferences',0,'If ON, allows patrons to select to receive additional messages about items due or nearly due.','','YesNo')
1760 $dbh->do( <<'END_SQL');
1761 INSERT INTO `letter`
1762 (module, code, name, title, content)
1764 ('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>>'),
1765 ('circulation','DUEDGST','Item Due Reminder (Digest)','Item Due Reminder','You have <<count>> items due'),
1766 ('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>>'),
1767 ('circulation','PREDUEDGST','Advance Notice of Item Due (Digest)','Advance Notice of Item Due','You have <<count>> items due soon'),
1768 ('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.');
1772 'installer/data/mysql/en/mandatory/message_transport_types.sql',
1773 'installer/data/mysql/en/optional/sample_notices_message_attributes.sql',
1774 'installer/data/mysql/en/optional/sample_notices_message_transports.sql',
1777 my $installer = C4::Installer->new();
1778 foreach my $script ( @sql_scripts ) {
1779 my $full_path = $installer->get_file_path_from_name($script);
1780 my $error = $installer->load_sql($full_path);
1781 warn $error if $error;
1784 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";
1785 SetVersion ($DBversion);
1788 $DBversion = "3.00.00.092";
1789 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1790 $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')");
1791 $dbh->do("INSERT INTO `systempreferences` (variable,value,options,explanation,type) VALUES('AllowHoldsOnDamagedItems', '1', '', 'Allow hold requests to be placed on damaged items', 'YesNo')");
1792 print "Upgrade to $DBversion done (added new AllowOnShelfHolds syspref)\n";
1793 SetVersion ($DBversion);
1796 $DBversion = "3.00.00.093";
1797 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1798 $dbh->do("ALTER TABLE `items` MODIFY COLUMN `copynumber` VARCHAR(32) DEFAULT NULL");
1799 $dbh->do("ALTER TABLE `deleteditems` MODIFY COLUMN `copynumber` VARCHAR(32) DEFAULT NULL");
1800 print "Upgrade to $DBversion done (Change data type of items.copynumber to allow free text)\n";
1801 SetVersion ($DBversion);
1804 $DBversion = "3.00.00.094";
1805 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1806 $dbh->do("ALTER TABLE `marc_subfield_structure` MODIFY `tagsubfield` VARCHAR(1) NOT NULL DEFAULT '' COLLATE utf8_bin");
1807 print "Upgrade to $DBversion done (Change Collation of marc_subfield_structure to allow mixed case in subfield labels.)\n";
1808 SetVersion ($DBversion);
1811 $DBversion = "3.00.00.095";
1812 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1813 if (C4::Context->preference("marcflavour") eq 'MARC21') {
1814 $dbh->do("UPDATE marc_subfield_structure SET authtypecode = 'MEETI_NAME' WHERE authtypecode = 'Meeting Name'");
1815 $dbh->do("UPDATE marc_subfield_structure SET authtypecode = 'CORPO_NAME' WHERE authtypecode = 'CORP0_NAME'");
1817 print "Upgrade to $DBversion done (fix invalid authority types in MARC21 frameworks [bug 2254])\n";
1818 SetVersion ($DBversion);
1821 $DBversion = "3.00.00.096";
1822 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1823 $sth = $dbh->prepare("SHOW COLUMNS FROM borrower_message_preferences LIKE 'wants_digets'");
1825 if (my $row = $sth->fetchrow_hashref) {
1826 $dbh->do("ALTER TABLE borrower_message_preferences CHANGE wants_digets wants_digest tinyint(1) NOT NULL default 0");
1828 print "Upgrade to $DBversion done (fix name borrower_message_preferences.wants_digest)\n";
1829 SetVersion ($DBversion);
1832 $DBversion = '3.00.00.097';
1833 if ( C4::Context->preference('Version') < TransformToNum($DBversion) ) {
1835 $dbh->do('ALTER TABLE message_queue ADD to_address mediumtext default NULL');
1836 $dbh->do('ALTER TABLE message_queue ADD from_address mediumtext default NULL');
1837 $dbh->do('ALTER TABLE message_queue ADD content_type text');
1838 $dbh->do('ALTER TABLE message_queue CHANGE borrowernumber borrowernumber int(11) default NULL');
1840 print "Upgrade to $DBversion done (updating 4 fields in message_queue table)\n";
1841 SetVersion($DBversion);
1844 $DBversion = '3.00.00.098';
1845 if ( C4::Context->preference('Version') < TransformToNum($DBversion) ) {
1847 $dbh->do(q(DELETE FROM message_transport_types WHERE message_transport_type = 'rss'));
1848 $dbh->do(q(DELETE FROM message_transports WHERE message_transport_type = 'rss'));
1850 print "Upgrade to $DBversion done (removing unused RSS message_transport_type)\n";
1851 SetVersion($DBversion);
1854 $DBversion = '3.00.00.099';
1855 if ( C4::Context->preference('Version') < TransformToNum($DBversion) ) {
1856 $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')");
1857 print "Upgrade to $DBversion done (Adding OpacSuppression syspref)\n";
1858 SetVersion($DBversion);
1861 $DBversion = '3.00.00.100';
1862 if ( C4::Context->preference('Version') < TransformToNum($DBversion) ) {
1863 $dbh->do('ALTER TABLE virtualshelves ADD COLUMN lastmodified timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP');
1864 print "Upgrade to $DBversion done (Adding lastmodified column to virtualshelves)\n";
1865 SetVersion($DBversion);
1868 $DBversion = '3.00.00.101';
1869 if ( C4::Context->preference('Version') < TransformToNum($DBversion) ) {
1870 $dbh->do('ALTER TABLE `overduerules` CHANGE `categorycode` `categorycode` VARCHAR(10) NOT NULL');
1871 $dbh->do('ALTER TABLE `deletedborrowers` CHANGE `categorycode` `categorycode` VARCHAR(10) NOT NULL');
1872 print "Upgrade to $DBversion done (Updating columnd definitions for patron category codes in notice/statsu triggers and deletedborrowers tables.)\n";
1873 SetVersion($DBversion);
1876 $DBversion = '3.00.00.102';
1877 if ( C4::Context->preference('Version') < TransformToNum($DBversion) ) {
1878 $dbh->do('ALTER TABLE serialitems MODIFY `serialid` int(11) NOT NULL AFTER itemnumber' );
1879 $dbh->do('ALTER TABLE serialitems DROP KEY serialididx' );
1880 $dbh->do('ALTER TABLE serialitems ADD CONSTRAINT UNIQUE KEY serialitemsidx (itemnumber)' );
1881 # before setting constraint, delete any unvalid data
1882 $dbh->do('DELETE from serialitems WHERE serialid not in (SELECT serial.serialid FROM serial)');
1883 $dbh->do('ALTER TABLE serialitems ADD CONSTRAINT serialitems_sfk_1 FOREIGN KEY (serialid) REFERENCES serial (serialid) ON DELETE CASCADE ON UPDATE CASCADE' );
1884 print "Upgrade to $DBversion done (Updating serialitems table to allow for multiple items per serial fixing kohabug 2380)\n";
1885 SetVersion($DBversion);
1888 $DBversion = "3.00.00.103";
1889 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1890 $dbh->do("DELETE FROM systempreferences WHERE variable='serialsadditems'");
1891 print "Upgrade to $DBversion done ( Verifying the removal of serialsadditems from syspref fixing kohabug 2219)\n";
1892 SetVersion ($DBversion);
1895 $DBversion = "3.00.00.104";
1896 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1897 $dbh->do("DELETE FROM systempreferences WHERE variable='noOPACHolds'");
1898 print "Upgrade to $DBversion done (remove superseded 'noOPACHolds' system preference per bug 2413)\n";
1899 SetVersion ($DBversion);
1902 $DBversion = '3.00.00.105';
1903 if ( C4::Context->preference("Version") < TransformToNum($DBversion) ) {
1905 # it is possible that this syspref is already defined since the feature was added some time ago.
1906 unless ( $dbh->do(q(SELECT variable FROM systempreferences WHERE variable = 'SMSSendDriver')) ) {
1907 $dbh->do(<<'END_SQL');
1908 INSERT INTO `systempreferences`
1909 (variable,value,explanation,options,type)
1911 ('SMSSendDriver','','Sets which SMS::Send driver is used to send SMS messages.','','free')
1914 print "Upgrade to $DBversion done (added SMSSendDriver system preference)\n";
1915 SetVersion($DBversion);
1918 $DBversion = "3.00.00.106";
1919 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1920 $dbh->do("DELETE FROM systempreferences WHERE variable='noOPACHolds'");
1922 # db revision 105 didn't apply correctly, so we're rolling this into 106
1923 $dbh->do("INSERT INTO `systempreferences`
1924 (variable,value,explanation,options,type)
1926 ('SMSSendDriver','','Sets which SMS::Send driver is used to send SMS messages.','','free')");
1928 print "Upgrade to $DBversion done (remove default '0000-00-00' in subscriptionhistory.enddate field)\n";
1929 $dbh->do("ALTER TABLE `subscriptionhistory` CHANGE `enddate` `enddate` DATE NULL DEFAULT NULL ");
1930 $dbh->do("UPDATE subscriptionhistory SET enddate=NULL WHERE enddate='0000-00-00'");
1931 SetVersion ($DBversion);
1934 $DBversion = '3.00.00.107';
1935 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1936 $dbh->do(<<'END_SQL');
1937 UPDATE systempreferences
1938 SET explanation = CONCAT( explanation, '. WARNING: this feature is very resource consuming on collections with large numbers of items.' )
1939 WHERE variable = 'OPACShelfBrowser'
1940 AND explanation NOT LIKE '%WARNING%'
1942 $dbh->do(<<'END_SQL');
1943 UPDATE systempreferences
1944 SET explanation = CONCAT( explanation, '. WARNING: this feature is very resource consuming.' )
1945 WHERE variable = 'CataloguingLog'
1946 AND explanation NOT LIKE '%WARNING%'
1948 $dbh->do(<<'END_SQL');
1949 UPDATE systempreferences
1950 SET explanation = CONCAT( explanation, '. WARNING: using NoZebra on even modest sized collections is very slow.' )
1951 WHERE variable = 'NoZebra'
1952 AND explanation NOT LIKE '%WARNING%'
1954 print "Upgrade to $DBversion done (warning added to OPACShelfBrowser system preference)\n";
1955 SetVersion ($DBversion);
1958 $DBversion = '3.01.00.000';
1959 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1960 print "Upgrade to $DBversion done (start of 3.1)\n";
1961 SetVersion ($DBversion);
1964 $DBversion = '3.01.00.001';
1965 if ( C4::Context->preference('Version') < TransformToNum($DBversion) ) {
1967 CREATE TABLE hold_fill_targets (
1968 `borrowernumber` int(11) NOT NULL,
1969 `biblionumber` int(11) NOT NULL,
1970 `itemnumber` int(11) NOT NULL,
1971 `source_branchcode` varchar(10) default NULL,
1972 `item_level_request` tinyint(4) NOT NULL default 0,
1973 PRIMARY KEY `itemnumber` (`itemnumber`),
1974 KEY `bib_branch` (`biblionumber`, `source_branchcode`),
1975 CONSTRAINT `hold_fill_targets_ibfk_1` FOREIGN KEY (`borrowernumber`)
1976 REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1977 CONSTRAINT `hold_fill_targets_ibfk_2` FOREIGN KEY (`biblionumber`)
1978 REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1979 CONSTRAINT `hold_fill_targets_ibfk_3` FOREIGN KEY (`itemnumber`)
1980 REFERENCES `items` (`itemnumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1981 CONSTRAINT `hold_fill_targets_ibfk_4` FOREIGN KEY (`source_branchcode`)
1982 REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE
1983 ) ENGINE=InnoDB DEFAULT CHARSET=utf8
1986 ALTER TABLE tmp_holdsqueue
1987 ADD item_level_request tinyint(4) NOT NULL default 0
1990 print "Upgrade to $DBversion done (add hold_fill_targets table and a column to tmp_holdsqueue)\n";
1991 SetVersion($DBversion);
1994 $DBversion = '3.01.00.002';
1995 if ( C4::Context->preference('Version') < TransformToNum($DBversion) ) {
1996 # use statistics where available
1998 ALTER TABLE statistics ADD KEY tmp_stats (type, itemnumber, borrowernumber)
2003 SELECT max(datetime)
2005 WHERE type = 'issue'
2006 AND itemnumber = iss.itemnumber
2007 AND borrowernumber = iss.borrowernumber
2009 WHERE issuedate IS NULL;
2011 $dbh->do("ALTER TABLE statistics DROP KEY tmp_stats");
2013 # default to last renewal date
2016 SET issuedate = lastreneweddate
2017 WHERE issuedate IS NULL
2018 and lastreneweddate IS NOT NULL
2021 my $num_bad_issuedates = $dbh->selectrow_array("SELECT COUNT(*) FROM issues WHERE issuedate IS NULL");
2022 if ($num_bad_issuedates > 0) {
2023 print STDERR "After the upgrade to $DBversion, there are still $num_bad_issuedates loan(s) with a NULL (blank) loan date. ",
2024 "Please check the issues table in your database.";
2026 print "Upgrade to $DBversion done (bug 2582: set null issues.issuedate to lastreneweddate)\n";
2027 SetVersion($DBversion);
2030 $DBversion = "3.01.00.003";
2031 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2032 $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')");
2033 print "Upgrade to $DBversion done (add new syspref)\n";
2034 SetVersion ($DBversion);
2037 $DBversion = '3.01.00.004';
2038 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2039 $dbh->do("INSERT INTO systempreferences (variable,value,explanation,options,type) VALUES ('OPACDisplayRequestPriority','0','Show patrons the priority level on holds in the OPAC','','YesNo')");
2040 print "Upgrade to $DBversion done (added OPACDisplayRequestPriority system preference)\n";
2041 SetVersion ($DBversion);
2044 $DBversion = '3.01.00.005';
2045 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2047 INSERT INTO `letter` (module, code, name, title, content)
2048 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>>')
2050 $dbh->do("INSERT INTO `message_attributes` (message_attribute_id, message_name, takes_days) values(4, 'Hold Filled', 0)");
2051 $dbh->do("INSERT INTO `message_transports` (message_attribute_id, message_transport_type, is_digest, letter_module, letter_code) values(4, 'sms', 0, 'reserves', 'HOLD')");
2052 $dbh->do("INSERT INTO `message_transports` (message_attribute_id, message_transport_type, is_digest, letter_module, letter_code) values(4, 'email', 0, 'reserves', 'HOLD')");
2053 print "Upgrade to $DBversion done (Add letter for holds notifications)\n";
2054 SetVersion ($DBversion);
2057 $DBversion = '3.01.00.006';
2058 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2059 $dbh->do("ALTER TABLE `biblioitems` ADD KEY issn (issn)");
2060 print "Upgrade to $DBversion done (add index on biblioitems.issn)\n";
2061 SetVersion ($DBversion);
2064 $DBversion = "3.01.00.007";
2065 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2066 $dbh->do("UPDATE `systempreferences` SET options='70|10' WHERE variable='intranetmainUserblock'");
2067 $dbh->do("UPDATE `systempreferences` SET options='70|10' WHERE variable='intranetuserjs'");
2068 $dbh->do("UPDATE `systempreferences` SET options='70|10' WHERE variable='opacheader'");
2069 $dbh->do("UPDATE `systempreferences` SET options='70|10' WHERE variable='OpacMainUserBlock'");
2070 $dbh->do("UPDATE `systempreferences` SET options='70|10' WHERE variable='OpacNav'");
2071 $dbh->do("UPDATE `systempreferences` SET options='70|10' WHERE variable='opacuserjs'");
2072 $dbh->do("UPDATE `systempreferences` SET options='30|10', type='Textarea' WHERE variable='OAI-PMH:Set'");
2073 $dbh->do("UPDATE `systempreferences` SET options='50' WHERE variable='intranetstylesheet'");
2074 $dbh->do("UPDATE `systempreferences` SET options='50' WHERE variable='intranetcolorstylesheet'");
2075 $dbh->do("UPDATE `systempreferences` SET options='10' WHERE variable='globalDueDate'");
2076 $dbh->do("UPDATE `systempreferences` SET type='Integer' WHERE variable='numSearchResults'");
2077 $dbh->do("UPDATE `systempreferences` SET type='Integer' WHERE variable='OPACnumSearchResults'");
2078 $dbh->do("UPDATE `systempreferences` SET type='Integer' WHERE variable='ReservesMaxPickupDelay'");
2079 $dbh->do("UPDATE `systempreferences` SET type='Integer' WHERE variable='TransfersMaxDaysWarning'");
2080 $dbh->do("UPDATE `systempreferences` SET type='Integer' WHERE variable='StaticHoldsQueueWeight'");
2081 $dbh->do("UPDATE `systempreferences` SET type='Integer' WHERE variable='holdCancelLength'");
2082 $dbh->do("UPDATE `systempreferences` SET type='Integer' WHERE variable='XISBNDailyLimit'");
2083 $dbh->do("UPDATE `systempreferences` SET type='Float' WHERE variable='gist'");
2084 $dbh->do("UPDATE `systempreferences` SET type='Free' WHERE variable='BakerTaylorUsername'");
2085 $dbh->do("UPDATE `systempreferences` SET type='Free' WHERE variable='BakerTaylorPassword'");
2086 $dbh->do("UPDATE `systempreferences` SET type='Textarea', options='70|10' WHERE variable='ISBD'");
2087 $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'");
2088 print "Upgrade to $DBversion done (fix display of many sysprefs)\n";
2089 SetVersion ($DBversion);
2092 $DBversion = '3.01.00.008';
2093 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2095 $dbh->do("CREATE TABLE branch_transfer_limits (
2096 limitId int(8) NOT NULL auto_increment,
2097 toBranch varchar(4) NOT NULL,
2098 fromBranch varchar(4) NOT NULL,
2099 itemtype varchar(4) NOT NULL,
2100 PRIMARY KEY (limitId)
2101 ) ENGINE=InnoDB DEFAULT CHARSET=utf8"
2104 $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')");
2106 print "Upgrade to $DBversion done (added branch_transfer_limits table and UseBranchTransferLimits system preference)\n";
2107 SetVersion ($DBversion);
2110 $DBversion = "3.01.00.009";
2111 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2112 $dbh->do("ALTER TABLE permissions MODIFY `code` varchar(64) DEFAULT NULL");
2113 $dbh->do("ALTER TABLE user_permissions MODIFY `code` varchar(64) DEFAULT NULL");
2114 $dbh->do("INSERT INTO permissions (module_bit, code, description) VALUES ( 1, 'circulate_remaining_permissions', 'Remaining circulation permissions')");
2115 $dbh->do("INSERT INTO permissions (module_bit, code, description) VALUES ( 1, 'override_renewals', 'Override blocked renewals')");
2116 print "Upgrade to $DBversion done (added subpermissions for circulate permission)\n";
2119 $DBversion = '3.01.00.010';
2120 if ( C4::Context->preference('Version') < TransformToNum($DBversion) ) {
2121 $dbh->do("ALTER TABLE `borrower_attributes` MODIFY COLUMN `attribute` VARCHAR(64) DEFAULT NULL");
2122 $dbh->do("ALTER TABLE `borrower_attributes` MODIFY COLUMN `password` VARCHAR(64) DEFAULT NULL");
2123 print "Upgrade to $DBversion done (bug 2687: increase length of borrower attribute fields)\n";
2124 SetVersion ($DBversion);
2127 $DBversion = '3.01.00.011';
2128 if ( C4::Context->preference('Version') < TransformToNum($DBversion) ) {
2130 # Yes, the old value was ^M terminated.
2131 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);";
2133 my $intranetuserjs = C4::Context->preference('intranetuserjs');
2134 if ($intranetuserjs and $intranetuserjs eq $bad_value) {
2135 my $sql = <<'END_SQL';
2136 UPDATE systempreferences
2138 WHERE variable = 'intranetuserjs'
2142 print "Upgrade to $DBversion done (removed bogus intranetuserjs syspref)\n";
2143 SetVersion($DBversion);
2146 $DBversion = "3.01.00.012";
2147 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2148 $dbh->do("INSERT INTO systempreferences (variable,value,explanation,options,type) VALUES('AllowHoldPolicyOverride', '0', 'Allow staff to override hold policies when placing holds',NULL,'YesNo')");
2150 CREATE TABLE `branch_item_rules` (
2151 `branchcode` varchar(10) NOT NULL,
2152 `itemtype` varchar(10) NOT NULL,
2153 `holdallowed` tinyint(1) default NULL,
2154 PRIMARY KEY (`itemtype`,`branchcode`),
2155 KEY `branch_item_rules_ibfk_2` (`branchcode`),
2156 CONSTRAINT `branch_item_rules_ibfk_1` FOREIGN KEY (`itemtype`) REFERENCES `itemtypes` (`itemtype`) ON DELETE CASCADE ON UPDATE CASCADE,
2157 CONSTRAINT `branch_item_rules_ibfk_2` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE
2158 ) ENGINE=InnoDB DEFAULT CHARSET=utf8
2161 CREATE TABLE `default_branch_item_rules` (
2162 `itemtype` varchar(10) NOT NULL,
2163 `holdallowed` tinyint(1) default NULL,
2164 PRIMARY KEY (`itemtype`),
2165 CONSTRAINT `default_branch_item_rules_ibfk_1` FOREIGN KEY (`itemtype`) REFERENCES `itemtypes` (`itemtype`) ON DELETE CASCADE ON UPDATE CASCADE
2166 ) ENGINE=InnoDB DEFAULT CHARSET=utf8
2169 ALTER TABLE default_branch_circ_rules
2170 ADD COLUMN holdallowed tinyint(1) NULL
2173 ALTER TABLE default_circ_rules
2174 ADD COLUMN holdallowed tinyint(1) NULL
2176 print "Upgrade to $DBversion done (Add tables and system preferences for holds policies)\n";
2177 SetVersion ($DBversion);
2180 $DBversion = '3.01.00.013';
2181 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2183 CREATE TABLE item_circulation_alert_preferences (
2184 id int(11) AUTO_INCREMENT,
2185 branchcode varchar(10) NOT NULL,
2186 categorycode varchar(10) NOT NULL,
2187 item_type varchar(10) NOT NULL,
2188 notification varchar(16) NOT NULL,
2190 KEY (branchcode, categorycode, item_type, notification)
2191 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2194 $dbh->do(q{ ALTER TABLE `message_queue` ADD metadata text DEFAULT NULL AFTER content; });
2195 $dbh->do(q{ ALTER TABLE `message_queue` ADD letter_code varchar(64) DEFAULT NULL AFTER metadata; });
2198 INSERT INTO `letter` (`module`, `code`, `name`, `title`, `content`) VALUES
2199 ('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.');
2202 INSERT INTO `letter` (`module`, `code`, `name`, `title`, `content`) VALUES
2203 ('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>>.');
2206 $dbh->do(q{INSERT INTO message_attributes (message_attribute_id, message_name, takes_days) VALUES (5, 'Item Check-in', 0);});
2207 $dbh->do(q{INSERT INTO message_attributes (message_attribute_id, message_name, takes_days) VALUES (6, 'Item Checkout', 0);});
2209 $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');});
2210 $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');});
2211 $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');});
2212 $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');});
2214 print "Upgrade to $DBversion done (data for Email Checkout Slips project)\n";
2215 SetVersion ($DBversion);
2218 $DBversion = "3.01.00.014";
2219 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2220 $dbh->do("ALTER TABLE `branch_transfer_limits` CHANGE `itemtype` `itemtype` VARCHAR( 4 ) CHARACTER SET utf8 COLLATE utf8_general_ci NULL");
2221 $dbh->do("ALTER TABLE `branch_transfer_limits` ADD `ccode` VARCHAR( 10 ) NULL ;");
2222 $dbh->do("INSERT INTO `systempreferences` ( `variable` , `value` , `options` , `explanation` , `type` )
2224 'BranchTransferLimitsType', 'ccode', 'itemtype|ccode', 'When using branch transfer limits, choose whether to limit by itemtype or collection code.', 'Choice'
2227 print "Upgrade to $DBversion done ( Updated table for Branch Transfer Limits)\n";
2228 SetVersion ($DBversion);
2231 $DBversion = '3.01.00.015';
2232 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2233 $dbh->do("INSERT INTO systempreferences (variable,value,explanation,options,type) VALUES ('SyndeticsClientCode', '0', 'Client Code for using Syndetics Solutions content','','free')");
2235 $dbh->do("INSERT INTO systempreferences (variable,value,explanation,options,type) VALUES ('SyndeticsEnabled', '0', 'Turn on Syndetics Enhanced Content','','YesNo')");
2237 $dbh->do("INSERT INTO systempreferences (variable,value,explanation,options,type) VALUES ('SyndeticsCoverImages', '0', 'Display Cover Images from Syndetics','','YesNo')");
2239 $dbh->do("INSERT INTO systempreferences (variable,value,explanation,options,type) VALUES ('SyndeticsTOC', '0', 'Display Table of Content information from Syndetics','','YesNo')");
2241 $dbh->do("INSERT INTO systempreferences (variable,value,explanation,options,type) VALUES ('SyndeticsSummary', '0', 'Display Summary Information from Syndetics','','YesNo')");
2243 $dbh->do("INSERT INTO systempreferences (variable,value,explanation,options,type) VALUES ('SyndeticsEditions', '0', 'Display Editions from Syndetics','','YesNo')");
2245 $dbh->do("INSERT INTO systempreferences (variable,value,explanation,options,type) VALUES ('SyndeticsExcerpt', '0', 'Display Excerpts and first chapters on OPAC from Syndetics','','YesNo')");
2247 $dbh->do("INSERT INTO systempreferences (variable,value,explanation,options,type) VALUES ('SyndeticsReviews', '0', 'Display Reviews on OPAC from Syndetics','','YesNo')");
2249 $dbh->do("INSERT INTO systempreferences (variable,value,explanation,options,type) VALUES ('SyndeticsAuthorNotes', '0', 'Display Notes about the Author on OPAC from Syndetics','','YesNo')");
2251 $dbh->do("INSERT INTO systempreferences (variable,value,explanation,options,type) VALUES ('SyndeticsAwards', '0', 'Display Awards on OPAC from Syndetics','','YesNo')");
2253 $dbh->do("INSERT INTO systempreferences (variable,value,explanation,options,type) VALUES ('SyndeticsSeries', '0', 'Display Series information on OPAC from Syndetics','','YesNo')");
2255 $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')");
2257 $dbh->do("INSERT INTO systempreferences (variable,value,explanation,options,type) VALUES ('OPACAmazonCoverImages', '0', 'Display cover images on OPAC from Amazon Web Services','','YesNo')");
2259 $dbh->do("INSERT INTO systempreferences (variable,value,explanation,options,type) VALUES ('AmazonCoverImages', '0', 'Display Cover Images in Staff Client from Amazon Web Services','','YesNo')");
2261 $dbh->do("UPDATE systempreferences SET variable='AmazonEnabled' WHERE variable = 'AmazonContent'");
2263 $dbh->do("UPDATE systempreferences SET variable='OPACAmazonEnabled' WHERE variable = 'OPACAmazonContent'");
2265 print "Upgrade to $DBversion done (added Syndetics Enhanced Content system preferences)\n";
2266 SetVersion ($DBversion);
2269 $DBversion = "3.01.00.016";
2270 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2271 $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')");
2272 print "Upgrade to $DBversion done (Added Babeltheque syspref)\n";
2273 SetVersion ($DBversion);
2276 $DBversion = "3.01.00.017";
2277 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2278 $dbh->do("ALTER TABLE `subscription` ADD `staffdisplaycount` VARCHAR(10) NULL;");
2279 $dbh->do("ALTER TABLE `subscription` ADD `opacdisplaycount` VARCHAR(10) NULL;");
2280 $dbh->do("INSERT INTO `systempreferences` ( `variable` , `value` , `options` , `explanation` , `type` )
2282 'StaffSerialIssueDisplayCount', '3', '', 'Number of serial issues to display per subscription in the Staff client', 'Integer'
2284 $dbh->do("INSERT INTO `systempreferences` ( `variable` , `value` , `options` , `explanation` , `type` )
2286 'OPACSerialIssueDisplayCount', '3', '', 'Number of serial issues to display per subscription in the OPAC', 'Integer'
2289 print "Upgrade to $DBversion done ( Updated table for Serials Display)\n";
2290 SetVersion ($DBversion);
2293 $DBversion = "3.01.00.018";
2294 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2295 $dbh->do("ALTER TABLE deletedborrowers ADD `smsalertnumber` varchar(50) default NULL");
2296 print "Upgrade to $DBversion done (added deletedborrowers.smsalertnumber, missed in 3.00.00.091)\n";
2297 SetVersion ($DBversion);
2300 $DBversion = "3.01.00.019";
2301 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2302 $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')");
2303 print "Upgrade to $DBversion done (adding OPACShowCheckoutName systempref)\n";
2304 SetVersion ($DBversion);
2307 $DBversion = "3.01.00.020";
2308 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2309 $dbh->do("INSERT INTO systempreferences (variable,value,explanation,options,type)VALUES('LibraryThingForLibrariesID','','See:http://librarything.com/forlibraries/','','free')");
2310 $dbh->do("INSERT INTO systempreferences (variable,value,explanation,options,type)VALUES('LibraryThingForLibrariesEnabled','0','Enable or Disable Library Thing for Libraries Features','','YesNo')");
2311 $dbh->do("INSERT INTO systempreferences (variable,value,explanation,options,type)VALUES('LibraryThingForLibrariesTabbedView','0','Put LibraryThingForLibraries Content in Tabs.','','YesNo')");
2312 print "Upgrade to $DBversion done (adding LibraryThing for Libraries sysprefs)\n";
2313 SetVersion ($DBversion);
2316 $DBversion = "3.01.00.021";
2317 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2318 my $enable_reviews = C4::Context->preference('OPACAmazonEnabled') ? '1' : '0';
2319 $dbh->do("INSERT INTO systempreferences (variable,value,explanation,options,type) VALUES ('OPACAmazonReviews', '$enable_reviews', 'Display Amazon readers reviews on OPAC','','YesNo')");
2320 print "Upgrade to $DBversion done (adding OPACAmazonReviews syspref)\n";
2321 SetVersion ($DBversion);
2324 $DBversion = '3.01.00.022';
2325 if ( C4::Context->preference('Version') < TransformToNum($DBversion) ) {
2326 $dbh->do("ALTER TABLE `labels_conf` MODIFY COLUMN `formatstring` mediumtext DEFAULT NULL");
2327 print "Upgrade to $DBversion done (bug 2945: increase size of labels_conf.formatstring)\n";
2328 SetVersion ($DBversion);
2331 $DBversion = '3.01.00.023';
2332 if ( C4::Context->preference('Version') < TransformToNum($DBversion) ) {
2333 $dbh->do("ALTER TABLE biblioitems MODIFY COLUMN isbn VARCHAR(30) DEFAULT NULL");
2334 $dbh->do("ALTER TABLE deletedbiblioitems MODIFY COLUMN isbn VARCHAR(30) DEFAULT NULL");
2335 $dbh->do("ALTER TABLE import_biblios MODIFY COLUMN isbn VARCHAR(30) DEFAULT NULL");
2336 $dbh->do("ALTER TABLE suggestions MODIFY COLUMN isbn VARCHAR(30) DEFAULT NULL");
2337 print "Upgrade to $DBversion done (bug 2765: increase width of isbn column in several tables)\n";
2338 SetVersion ($DBversion);
2341 $DBversion = "3.01.00.024";
2342 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2343 $dbh->do("ALTER TABLE labels MODIFY COLUMN batch_id int(10) NOT NULL default 1;");
2344 print "Upgrade to $DBversion done (change labels.batch_id from varchar to int)\n";
2345 SetVersion ($DBversion);
2348 $DBversion = '3.01.00.025';
2349 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2350 $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')");
2352 print "Upgrade to $DBversion done (added ceilingDueDate system preference)\n";
2353 SetVersion ($DBversion);
2356 $DBversion = '3.01.00.026';
2357 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2358 $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')");
2360 print "Upgrade to $DBversion done (added numReturnedItemsToShow system preference)\n";
2361 SetVersion ($DBversion);
2364 $DBversion = '3.01.00.027';
2365 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2366 $dbh->do("ALTER TABLE zebraqueue CHANGE `biblio_auth_number` `biblio_auth_number` bigint(20) unsigned NOT NULL default 0");
2367 print "Upgrade to $DBversion done (Increased size of zebraqueue biblio_auth_number to address bug 3148.)\n";
2368 SetVersion ($DBversion);
2371 $DBversion = '3.01.00.028';
2372 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2373 my $enable_reviews = C4::Context->preference('AmazonEnabled') ? '1' : '0';
2374 $dbh->do("INSERT INTO systempreferences (variable,value,explanation,options,type) VALUES ('AmazonReviews', '$enable_reviews', 'Display Amazon reviews on staff interface','','YesNo')");
2375 print "Upgrade to $DBversion done (added AmazonReviews)\n";
2376 SetVersion ($DBversion);
2379 $DBversion = '3.01.00.029';
2380 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2381 $dbh->do(q( UPDATE language_rfc4646_to_iso639
2382 SET iso639_2_code = 'spa'
2383 WHERE rfc4646_subtag = 'es'
2384 AND iso639_2_code = 'rus' )
2386 print "Upgrade to $DBversion done (fixed bug 2599: using Spanish search limit retrieves Russian results)\n";
2387 SetVersion ($DBversion);
2390 $DBversion = "3.01.00.030";
2391 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2392 $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')");
2393 print "Upgrade to $DBversion done (added AllowNotForLoanOverride system preference)\n";
2394 SetVersion ($DBversion);
2397 $DBversion = "3.01.00.031";
2398 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2399 $dbh->do("ALTER TABLE branch_transfer_limits
2400 MODIFY toBranch varchar(10) NOT NULL,
2401 MODIFY fromBranch varchar(10) NOT NULL,
2402 MODIFY itemtype varchar(10) NULL");
2403 print "Upgrade to $DBversion done (fix column widths in branch_transfer_limits)\n";
2404 SetVersion ($DBversion);
2407 $DBversion = "3.01.00.032";
2408 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2409 $dbh->do(<<ENDOFRENEWAL);
2410 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');
2412 print "Upgrade to $DBversion done (Change the field)\n";
2413 SetVersion ($DBversion);
2416 $DBversion = "3.01.00.033";
2417 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2419 ALTER TABLE borrower_message_preferences
2420 MODIFY borrowernumber int(11) default NULL,
2421 ADD categorycode varchar(10) default NULL AFTER borrowernumber,
2422 ADD KEY `categorycode` (`categorycode`),
2423 ADD CONSTRAINT `borrower_message_preferences_ibfk_3`
2424 FOREIGN KEY (`categorycode`) REFERENCES `categories` (`categorycode`)
2425 ON DELETE CASCADE ON UPDATE CASCADE
2427 print "Upgrade to $DBversion done (DB changes to allow patron category defaults for messaging preferences)\n";
2428 SetVersion ($DBversion);
2431 $DBversion = "3.01.00.034";
2432 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2433 $dbh->do("ALTER TABLE `subscription` ADD COLUMN `graceperiod` INT(11) NOT NULL default '0';");
2434 print "Upgrade to $DBversion done (Adding graceperiod column to subscription table)\n";
2435 SetVersion ($DBversion);
2438 $DBversion = '3.01.00.035';
2439 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2440 $dbh->do(q{ ALTER TABLE `subscription` ADD location varchar(80) NULL DEFAULT '' AFTER callnumber; });
2441 print "Upgrade to $DBversion done (Adding location to subscription table)\n";
2442 SetVersion ($DBversion);
2445 $DBversion = '3.01.00.036';
2446 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2447 $dbh->do("UPDATE systempreferences SET explanation = 'Choose the default detail view in the staff interface; choose between normal, labeled_marc, marc or isbd'
2448 WHERE variable = 'IntranetBiblioDefaultView'
2449 AND explanation = 'IntranetBiblioDefaultView'");
2450 $dbh->do("UPDATE systempreferences SET type = 'Choice', options = 'normal|marc|isbd|labeled_marc'
2451 WHERE variable = 'IntranetBiblioDefaultView'");
2452 $dbh->do("INSERT INTO systempreferences (variable,value,explanation,options,type)VALUES('viewISBD','1','Allow display of ISBD view of bibiographic records','','YesNo')");
2453 $dbh->do("INSERT INTO systempreferences (variable,value,explanation,options,type)VALUES('viewLabeledMARC','0','Allow display of labeled MARC view of bibiographic records','','YesNo')");
2454 $dbh->do("INSERT INTO systempreferences (variable,value,explanation,options,type)VALUES('viewMARC','1','Allow display of MARC view of bibiographic records','','YesNo')");
2455 print "Upgrade to $DBversion done (new viewISBD, viewLabeledMARC, viewMARC sysprefs and tweak IntranetBiblioDefaultView)\n";
2456 SetVersion ($DBversion);
2459 $DBversion = '3.01.00.037';
2460 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2461 $dbh->do('ALTER TABLE authorised_values ADD KEY `lib` (`lib`)');
2462 $dbh->do("INSERT INTO systempreferences (variable,value,explanation,options,type)VALUES('FilterBeforeOverdueReport','0','Do not run overdue report until filter selected','','YesNo')");
2463 SetVersion ($DBversion);
2464 print "Upgrade to $DBversion done (added FilterBeforeOverdueReport syspref and new index on authorised_values)\n";
2467 $DBversion = "3.01.00.038";
2468 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2469 # update branches table
2471 $dbh->do("ALTER TABLE branches ADD `branchzip` varchar(25) default NULL AFTER `branchaddress3`");
2472 $dbh->do("ALTER TABLE branches ADD `branchcity` mediumtext AFTER `branchzip`");
2473 $dbh->do("ALTER TABLE branches ADD `branchcountry` text AFTER `branchcity`");
2474 $dbh->do("ALTER TABLE branches ADD `branchurl` mediumtext AFTER `branchemail`");
2475 $dbh->do("ALTER TABLE branches ADD `branchnotes` mediumtext AFTER `branchprinter`");
2476 print "Upgrade to $DBversion done (add ZIP, city, country, URL, and notes column to branches)\n";
2477 SetVersion ($DBversion);
2480 $DBversion = '3.01.00.039';
2481 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2482 $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')");
2483 $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')");
2484 SetVersion ($DBversion);
2485 print "Upgrade to $DBversion done (added SpineLabelFormat and SpineLabelAutoPrint sysprefs)\n";
2488 $DBversion = '3.01.00.040';
2489 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2490 $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')");
2491 $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')");
2492 SetVersion ($DBversion);
2493 print "Upgrade to $DBversion done (AllowHoldDateInFuture and OPACAllowHoldDateInFuture sysprefs)\n";
2496 $DBversion = '3.01.00.041';
2497 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2498 $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')");
2499 SetVersion ($DBversion);
2500 print "Upgrade to $DBversion done (added AWSPrivateKey syspref - note that if you use enhanced content from Amazon, this should be set right away.)\n";
2503 $DBversion = '3.01.00.042';
2504 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2505 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('OPACFineNoRenewals','99999','Fine Limit above which user canmot renew books via OPAC','','Integer')");
2506 SetVersion ($DBversion);
2507 print "Upgrade to $DBversion done (added OPACFineNoRenewals syspref)\n";
2510 $DBversion = '3.01.00.043';
2511 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2512 $dbh->do('ALTER TABLE items ADD COLUMN permanent_location VARCHAR(80) DEFAULT NULL AFTER location');
2513 $dbh->do('UPDATE items SET permanent_location = location');
2514 $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 )', '')");
2515 $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')");
2516 $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')");
2517 SetVersion ($DBversion);
2518 print "Upgrade to $DBversion done (amended Item added NewItemsDefaultLocation, InProcessingToShelvingCart, ReturnToShelvingCart sysprefs)\n";
2521 $DBversion = '3.01.00.044';
2522 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2523 $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')");
2524 SetVersion ($DBversion);
2525 print "Upgrade to $DBversion done (added DisplayClearScreenButton system preference)\n";
2528 $DBversion = '3.01.00.045';
2529 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2530 $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')");
2531 SetVersion ($DBversion);
2532 print "Upgrade to $DBversion done (added a preference to hide the patrons name in the staff catalog)";
2535 $DBversion = "3.01.00.046";
2536 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2537 # update borrowers table
2539 $dbh->do("ALTER TABLE borrowers ADD `country` text AFTER zipcode");
2540 $dbh->do("ALTER TABLE borrowers ADD `B_country` text AFTER B_zipcode");
2541 $dbh->do("ALTER TABLE deletedborrowers ADD `country` text AFTER zipcode");
2542 $dbh->do("ALTER TABLE deletedborrowers ADD `B_country` text AFTER B_zipcode");
2543 print "Upgrade to $DBversion done (add country and B_country to borrowers)\n";
2544 SetVersion ($DBversion);
2547 $DBversion = '3.01.00.047';
2548 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2549 $dbh->do("ALTER TABLE items MODIFY itemcallnumber varchar(255);");
2550 $dbh->do("ALTER TABLE deleteditems MODIFY itemcallnumber varchar(255);");
2551 $dbh->do("ALTER TABLE tmp_holdsqueue MODIFY itemcallnumber varchar(255);");
2552 SetVersion ($DBversion);
2553 print " Upgrade to $DBversion done (bug 2761: change max length of itemcallnumber to 255 from 30)\n";
2556 $DBversion = '3.01.00.048';
2557 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2558 $dbh->do("UPDATE userflags SET flagdesc='View Catalog (Librarian Interface)' WHERE bit=2;");
2559 $dbh->do("UPDATE userflags SET flagdesc='Edit Catalog (Modify bibliographic/holdings data)' WHERE bit=9;");
2560 $dbh->do("UPDATE userflags SET flagdesc='Allow to edit authorities' WHERE bit=14;");
2561 $dbh->do("UPDATE userflags SET flagdesc='Allow to access to the reports module' WHERE bit=16;");
2562 $dbh->do("UPDATE userflags SET flagdesc='Allow to manage serials subscriptions' WHERE bit=15;");
2563 SetVersion ($DBversion);
2564 print " Upgrade to $DBversion done (bug 2611: fix spelling/capitalization in permission flag descriptions)\n";
2567 $DBversion = '3.01.00.049';
2568 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2569 $dbh->do("UPDATE permissions SET description = 'Perform inventory (stocktaking) of your catalog' WHERE code = 'inventory';");
2570 SetVersion ($DBversion);
2571 print "Upgrade to $DBversion done (bug 2611: changed catalogue to catalog per the standard)\n";
2574 $DBversion = '3.01.00.050';
2575 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2576 $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');");
2577 SetVersion ($DBversion);
2578 print "Upgrade to $DBversion done (bug 1934: Add OPACSearchForTitleIn syspref)\n";
2581 $DBversion = '3.01.00.051';
2582 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2583 $dbh->do("UPDATE systempreferences SET explanation='Fine limit above which user cannot renew books via OPAC' WHERE variable='OPACFineNoRenewals';");
2584 $dbh->do("UPDATE systempreferences SET explanation='If set to ON, a clear screen button will appear on the circulation page.' WHERE variable='DisplayClearScreenButton';");
2585 SetVersion ($DBversion);
2586 print "Upgrade to $DBversion done (fixed typos in new sysprefs)\n";
2589 $DBversion = '3.01.00.052';
2590 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2591 $dbh->do('ALTER TABLE deleteditems ADD COLUMN permanent_location VARCHAR(80) DEFAULT NULL AFTER location');
2592 SetVersion ($DBversion);
2593 print "Upgrade to $DBversion done (bug 3481: add permanent_location column to deleteditems)\n";
2596 $DBversion = '3.01.00.053';
2597 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2598 my $upgrade_script = C4::Context->config("intranetdir") . "/installer/data/mysql/labels_upgrade.pl";
2599 system("perl $upgrade_script");
2600 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";
2601 SetVersion ($DBversion);
2604 $DBversion = '3.01.00.054';
2605 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2606 $dbh->do("ALTER TABLE borrowers ADD `B_address2` text AFTER B_address");
2607 $dbh->do("ALTER TABLE borrowers ADD `altcontactcountry` text AFTER altcontactzipcode");
2608 $dbh->do("ALTER TABLE deletedborrowers ADD `B_address2` text AFTER B_address");
2609 $dbh->do("ALTER TABLE deletedborrowers ADD `altcontactcountry` text AFTER altcontactzipcode");
2610 SetVersion ($DBversion);
2611 print "Upgrade to $DBversion done (bug 1600, bug 3454: add altcontactcountry and B_address2 to borrowers and deletedborrowers)\n";
2614 $DBversion = '3.01.00.055';
2615 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2616 $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'|);
2617 SetVersion ($DBversion);
2618 print "Upgrade to $DBversion done (changed OPACSearchForTitleIn per requests in bug 1934)\n";
2621 $DBversion = '3.01.00.056';
2622 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2623 $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');");
2624 SetVersion ($DBversion);
2625 print "Upgrade to $DBversion done (Bug 1172 : Add OPACPatronDetails syspref)\n";
2628 $DBversion = '3.01.00.057';
2629 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2630 $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');");
2631 SetVersion ($DBversion);
2632 print "Upgrade to $DBversion done (Bug 2576 : Add OPACFinesTab syspref)\n";
2635 $DBversion = '3.01.00.058';
2636 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2637 $dbh->do("ALTER TABLE `language_subtag_registry` ADD `id` INT( 11 ) NOT NULL AUTO_INCREMENT PRIMARY KEY;");
2638 $dbh->do("ALTER TABLE `language_rfc4646_to_iso639` ADD `id` INT( 11 ) NOT NULL AUTO_INCREMENT PRIMARY KEY;");
2639 $dbh->do("ALTER TABLE `language_descriptions` ADD `id` INT( 11 ) NOT NULL AUTO_INCREMENT PRIMARY KEY;");
2640 SetVersion ($DBversion);
2641 print "Upgrade to $DBversion done (Added primary keys to language tables)\n";
2644 $DBversion = '3.01.00.059';
2645 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2646 $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')");
2647 SetVersion ($DBversion);
2648 print "Upgrade to $DBversion done (added DisplayOPACiconsXSLT sysprefs)\n";
2651 $DBversion = '3.01.00.060';
2652 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2653 $dbh->do("INSERT INTO systempreferences (variable,value,explanation,options,type) VALUES('AllowAllMessageDeletion','0','Allow any Library to delete any message','','YesNo');");
2654 $dbh->do('DROP TABLE IF EXISTS messages');
2655 $dbh->do("CREATE TABLE messages ( `message_id` int(11) NOT NULL auto_increment,
2656 `borrowernumber` int(11) NOT NULL,
2657 `branchcode` varchar(4) default NULL,
2658 `message_type` varchar(1) NOT NULL,
2659 `message` text NOT NULL,
2660 `message_date` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
2661 PRIMARY KEY (`message_id`)
2662 ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
2664 print "Upgrade to $DBversion done ( Added AllowAllMessageDeletion syspref and messages table )\n";
2665 SetVersion ($DBversion);
2668 $DBversion = '3.01.00.061';
2669 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2670 $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')");
2671 print "Upgrade to $DBversion done ( Added ShowPatronImageInWebBasedSelfCheck system preference )\n";
2672 SetVersion ($DBversion);
2675 $DBversion = "3.01.00.062";
2676 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2677 $dbh->do("INSERT INTO permissions (module_bit, code, description) VALUES ( 13, 'manage_csv_profiles', 'Manage CSV export profiles')");
2679 CREATE TABLE `export_format` (
2680 `export_format_id` int(11) NOT NULL auto_increment,
2681 `profile` varchar(255) NOT NULL,
2682 `description` mediumtext NOT NULL,
2683 `marcfields` mediumtext NOT NULL,
2684 PRIMARY KEY (`export_format_id`)
2685 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Used for CSV export';
2687 print "Upgrade to $DBversion done (added csv export profiles)\n";
2690 $DBversion = "3.01.00.063";
2691 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2693 CREATE TABLE `fieldmapping` (
2694 `id` int(11) NOT NULL auto_increment,
2695 `field` varchar(255) NOT NULL,
2696 `frameworkcode` char(4) NOT NULL default '',
2697 `fieldcode` char(3) NOT NULL,
2698 `subfieldcode` char(1) NOT NULL,
2700 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2702 SetVersion ($DBversion);
2705 $DBversion = '3.01.00.065';
2706 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2707 $dbh->do('ALTER TABLE issuingrules ADD COLUMN `renewalsallowed` smallint(6) NOT NULL default "0" AFTER `issuelength`;');
2708 $sth = $dbh->prepare("SELECT itemtype, renewalsallowed FROM itemtypes");
2711 my $sthupd = $dbh->prepare("UPDATE issuingrules SET renewalsallowed = ? WHERE itemtype = ?");
2713 while(my $row = $sth->fetchrow_hashref){
2714 $sthupd->execute($row->{renewalsallowed}, $row->{itemtype});
2717 $dbh->do('ALTER TABLE itemtypes DROP COLUMN `renewalsallowed`;');
2719 SetVersion ($DBversion);
2720 print "Upgrade to $DBversion done (Moving allowed renewals from itemtypes to issuingrule)\n";
2723 $DBversion = '3.01.00.066';
2724 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2725 $dbh->do('ALTER TABLE issuingrules ADD COLUMN `reservesallowed` smallint(6) NOT NULL default "0" AFTER `renewalsallowed`;');
2727 my $maxreserves = C4::Context->preference('maxreserves');
2728 $sth = $dbh->prepare('UPDATE issuingrules SET reservesallowed = ?;');
2729 $sth->execute($maxreserves);
2731 $dbh->do('DELETE FROM systempreferences WHERE variable = "maxreserves";');
2733 $dbh->do("INSERT INTO systempreferences (variable,value, options, explanation, type) VALUES('ReservesControlBranch','PatronLibrary','ItemHomeLibrary|PatronLibrary','Branch checked for members reservations rights','Choice')");
2735 SetVersion ($DBversion);
2736 print "Upgrade to $DBversion done (Moving max allowed reserves from system preference to issuingrule)\n";
2739 $DBversion = "3.01.00.067";
2740 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2741 $dbh->do("INSERT INTO permissions (module_bit, code, description) VALUES ( 13, 'batchmod', 'Perform batch modification of items')");
2742 $dbh->do("INSERT INTO permissions (module_bit, code, description) VALUES ( 13, 'batchdel', 'Perform batch deletion of items')");
2743 print "Upgrade to $DBversion done (added permissions for batch modification and deletion)\n";
2744 SetVersion ($DBversion);
2747 $DBversion = "3.01.00.068";
2748 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2749 $dbh->do("ALTER TABLE issuingrules ADD COLUMN `finedays` int(11) default NULL AFTER `fine` ");
2750 print "Upgrade done (Adding finedays in issuingrules table)\n";
2751 SetVersion ($DBversion);
2755 $DBversion = "3.01.00.069";
2756 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2757 $dbh->do("INSERT INTO `systempreferences` (`variable`, `value`, `options`, `explanation`, `type`) VALUES ('EnableOpacSearchHistory', '1', '', 'Enable or disable opac search history', 'YesNo')");
2759 my $create = <<SEARCHHIST;
2760 CREATE TABLE IF NOT EXISTS `search_history` (
2761 `userid` int(11) NOT NULL,
2762 `sessionid` varchar(32) NOT NULL,
2763 `query_desc` varchar(255) NOT NULL,
2764 `query_cgi` varchar(255) NOT NULL,
2765 `total` int(11) NOT NULL,
2766 `time` timestamp NOT NULL default CURRENT_TIMESTAMP,
2767 KEY `userid` (`userid`),
2768 KEY `sessionid` (`sessionid`)
2769 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Opac search history results';
2773 print "Upgrade done (added OPAC search history preference and table)\n";
2776 $DBversion = "3.01.00.070";
2777 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2778 $dbh->do("ALTER TABLE authorised_values ADD COLUMN `lib_opac` VARCHAR(80) default NULL AFTER `lib`");
2779 print "Upgrade done (Added a lib_opac field in authorised_values table)\n";
2782 $DBversion = "3.01.00.071";
2783 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2784 $dbh->do("ALTER TABLE `subscription` ADD `enddate` date default NULL");
2785 $dbh->do("ALTER TABLE subscriptionhistory CHANGE enddate histenddate DATE default NULL");
2786 print "Upgrade to $DBversion done ( Adding enddate to subscription)\n";
2789 # Acquisitions update
2791 $DBversion = "3.01.00.072";
2792 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2793 $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')");
2794 # create a new syspref for the 'Mr anonymous' patron
2795 $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,'')");
2796 # fill AnonymousPatron with AnonymousSuggestion value (copy)
2797 my $sth=$dbh->prepare("SELECT value FROM systempreferences WHERE variable='AnonSuggestions'");
2799 my ($value) = $sth->fetchrow() || 0;
2800 $dbh->do("UPDATE systempreferences SET value='$value' WHERE variable='AnonymousPatron'");
2801 # set AnonymousSuggestion do YesNo
2802 # 1st, set the value (1/True if it had a borrowernumber)
2803 $dbh->do("UPDATE systempreferences SET value=1 WHERE variable='AnonSuggestions' AND value>0");
2804 # 2nd, change the type to Choice
2805 $dbh->do("UPDATE systempreferences SET type='YesNo' WHERE variable='AnonSuggestions'");
2806 # borrower reading record privacy : 0 : forever, 1 : laws, 2 : don't keep at all
2807 $dbh->do("ALTER TABLE `borrowers` ADD `privacy` INTEGER NOT NULL DEFAULT 1;");
2808 print "Upgrade to $DBversion done (add new syspref and column in borrowers)\n";
2809 SetVersion ($DBversion);
2812 $DBversion = '3.01.00.073';
2813 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2814 $dbh->do('SET FOREIGN_KEY_CHECKS=0 ');
2815 $dbh->do(<<'END_SQL');
2816 CREATE TABLE IF NOT EXISTS `aqcontract` (
2817 `contractnumber` int(11) NOT NULL auto_increment,
2818 `contractstartdate` date default NULL,
2819 `contractenddate` date default NULL,
2820 `contractname` varchar(50) default NULL,
2821 `contractdescription` mediumtext,
2822 `booksellerid` int(11) not NULL,
2823 PRIMARY KEY (`contractnumber`),
2824 CONSTRAINT `booksellerid_fk1` FOREIGN KEY (`booksellerid`)
2825 REFERENCES `aqbooksellers` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
2826 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
2828 $dbh->do('SET FOREIGN_KEY_CHECKS=1 ');
2829 print "Upgrade to $DBversion done (adding aqcontract table)\n";
2830 SetVersion ($DBversion);
2833 $DBversion = '3.01.00.074';
2834 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2835 $dbh->do("ALTER TABLE `aqbasket` ADD COLUMN `basketname` varchar(50) default NULL AFTER `basketno`");
2836 $dbh->do("ALTER TABLE `aqbasket` ADD COLUMN `note` mediumtext AFTER `basketname`");
2837 $dbh->do("ALTER TABLE `aqbasket` ADD COLUMN `booksellernote` mediumtext AFTER `note`");
2838 $dbh->do("ALTER TABLE `aqbasket` ADD COLUMN `contractnumber` int(11) AFTER `booksellernote`");
2839 $dbh->do("ALTER TABLE `aqbasket` ADD FOREIGN KEY (`contractnumber`) REFERENCES `aqcontract` (`contractnumber`)");
2840 print "Upgrade to $DBversion done (edit aqbasket table done)\n";
2841 SetVersion ($DBversion);
2844 $DBversion = '3.01.00.075';
2845 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2846 $dbh->do("ALTER TABLE `aqorders` ADD COLUMN `uncertainprice` tinyint(1)");
2848 print "Upgrade to $DBversion done (adding uncertainprices)\n";
2849 SetVersion ($DBversion);
2852 $DBversion = '3.01.00.076';
2853 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2854 $dbh->do('SET FOREIGN_KEY_CHECKS=0 ');
2855 $dbh->do("CREATE TABLE IF NOT EXISTS `aqbasketgroups` (
2856 `id` int(11) NOT NULL auto_increment,
2857 `name` varchar(50) default NULL,
2858 `closed` tinyint(1) default NULL,
2859 `booksellerid` int(11) NOT NULL,
2861 KEY `booksellerid` (`booksellerid`),
2862 CONSTRAINT `aqbasketgroups_ibfk_1` FOREIGN KEY (`booksellerid`) REFERENCES `aqbooksellers` (`id`) ON UPDATE CASCADE ON DELETE CASCADE
2863 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;");
2864 $dbh->do("ALTER TABLE aqbasket ADD COLUMN `basketgroupid` int(11)");
2865 $dbh->do("ALTER TABLE aqbasket ADD FOREIGN KEY (`basketgroupid`) REFERENCES `aqbasketgroups` (`id`) ON UPDATE CASCADE ON DELETE SET NULL");
2866 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES ('pdfformat','pdfformat::layout2pages','Controls what script is used for printing (basketgroups)','','free')");
2867 $dbh->do('SET FOREIGN_KEY_CHECKS=1 ');
2868 print "Upgrade to $DBversion done (adding basketgroups)\n";
2869 SetVersion ($DBversion);
2871 $DBversion = '3.01.00.077';
2872 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2874 $dbh->do("SET FOREIGN_KEY_CHECKS=0 ");
2875 # create a mapping table holding the info we need to match orders to budgets
2876 $dbh->do('DROP TABLE IF EXISTS fundmapping');
2878 q|CREATE TABLE fundmapping AS
2879 SELECT aqorderbreakdown.ordernumber, branchcode, bookfundid, budgetdate, entrydate
2880 FROM aqorderbreakdown JOIN aqorders ON aqorderbreakdown.ordernumber = aqorders.ordernumber|);
2881 # match the new type of the corresponding field
2882 $dbh->do('ALTER TABLE fundmapping modify column bookfundid varchar(30)');
2883 # System did not ensure budgetdate was valid historically
2884 $dbh->do(q|UPDATE fundmapping SET budgetdate = entrydate WHERE budgetdate = '0000-00-00' OR budgetdate IS NULL|);
2885 # We save the map in fundmapping in case you need later processing
2886 $dbh->do(q|ALTER TABLE fundmapping add column aqbudgetid integer|);
2887 # these can speed processing up
2888 $dbh->do(q|CREATE INDEX fundmaporder ON fundmapping (ordernumber)|);
2889 $dbh->do(q|CREATE INDEX fundmapid ON fundmapping (bookfundid)|);
2891 $dbh->do("DROP TABLE IF EXISTS `aqbudgetperiods` ");
2894 CREATE TABLE `aqbudgetperiods` (
2895 `budget_period_id` int(11) NOT NULL auto_increment,
2896 `budget_period_startdate` date NOT NULL,
2897 `budget_period_enddate` date NOT NULL,
2898 `budget_period_active` tinyint(1) default '0',
2899 `budget_period_description` mediumtext,
2900 `budget_period_locked` tinyint(1) default NULL,
2901 `sort1_authcat` varchar(10) default NULL,
2902 `sort2_authcat` varchar(10) default NULL,
2903 PRIMARY KEY (`budget_period_id`)
2904 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 |);
2906 $dbh->do(<<ADDPERIODS);
2907 INSERT INTO aqbudgetperiods (budget_period_startdate,budget_period_enddate,budget_period_active,budget_period_description,budget_period_locked)
2908 SELECT DISTINCT startdate, enddate, NOW() BETWEEN startdate and enddate, concat(startdate," ",enddate),NOT NOW() BETWEEN startdate AND enddate from aqbudget
2910 # SORRY , NO AQBUDGET/AQBOOKFUND -> AQBUDGETS IMPORT JUST YET,
2911 # BUT A NEW CLEAN AQBUDGETS TABLE CREATE FOR NOW..
2912 # DROP TABLE IF EXISTS `aqbudget`;
2913 #CREATE TABLE `aqbudget` (
2914 # `bookfundid` varchar(10) NOT NULL default ',
2915 # `startdate` date NOT NULL default 0,
2916 # `enddate` date default NULL,
2917 # `budgetamount` decimal(13,2) default NULL,
2918 # `aqbudgetid` tinyint(4) NOT NULL auto_increment,
2919 # `branchcode` varchar(10) default NULL,
2920 DropAllForeignKeys('aqbudget');
2921 #$dbh->do("drop table aqbudget;");
2924 my $maxbudgetid = $dbh->selectcol_arrayref(<<IDsBUDGET);
2925 SELECT MAX(aqbudgetid) from aqbudget
2928 $$maxbudgetid[0] = 0 if !$$maxbudgetid[0];
2930 $dbh->do(<<BUDGETAUTOINCREMENT);
2931 ALTER TABLE aqbudget AUTO_INCREMENT=$$maxbudgetid[0]
2934 $dbh->do(<<BUDGETNAME);
2935 ALTER TABLE aqbudget RENAME `aqbudgets`
2938 $dbh->do(<<BUDGETS);
2939 ALTER TABLE `aqbudgets`
2940 CHANGE COLUMN aqbudgetid `budget_id` int(11) NOT NULL AUTO_INCREMENT,
2941 CHANGE COLUMN branchcode `budget_branchcode` varchar(10) default NULL,
2942 CHANGE COLUMN budgetamount `budget_amount` decimal(28,6) NOT NULL default '0.00',
2943 CHANGE COLUMN bookfundid `budget_code` varchar(30) default NULL,
2944 ADD COLUMN `budget_parent_id` int(11) default NULL,
2945 ADD COLUMN `budget_name` varchar(80) default NULL,
2946 ADD COLUMN `budget_encumb` decimal(28,6) default '0.00',
2947 ADD COLUMN `budget_expend` decimal(28,6) default '0.00',
2948 ADD COLUMN `budget_notes` mediumtext,
2949 ADD COLUMN `budget_description` mediumtext,
2950 ADD COLUMN `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
2951 ADD COLUMN `budget_amount_sublevel` decimal(28,6) AFTER `budget_amount`,
2952 ADD COLUMN `budget_period_id` int(11) default NULL,
2953 ADD COLUMN `sort1_authcat` varchar(80) default NULL,
2954 ADD COLUMN `sort2_authcat` varchar(80) default NULL,
2955 ADD COLUMN `budget_owner_id` int(11) default NULL,
2956 ADD COLUMN `budget_permission` int(1) default '0';
2959 $dbh->do(<<BUDGETCONSTRAINTS);
2960 ALTER TABLE `aqbudgets`
2961 ADD CONSTRAINT `aqbudgets_ifbk_1` FOREIGN KEY (`budget_period_id`) REFERENCES `aqbudgetperiods` (`budget_period_id`) ON DELETE CASCADE ON UPDATE CASCADE
2963 # $dbh->do(<<BUDGETPKDROP);
2964 #ALTER TABLE `aqbudgets`
2967 # $dbh->do(<<BUDGETPKADD);
2968 #ALTER TABLE `aqbudgets`
2969 # ADD PRIMARY KEY budget_id
2973 my $query_period= $dbh->prepare(qq|SELECT budget_period_id from aqbudgetperiods where budget_period_startdate=? and budget_period_enddate=?|);
2974 my $query_bookfund= $dbh->prepare(qq|SELECT * from aqbookfund where bookfundid=?|);
2975 my $selectbudgets=$dbh->prepare(qq|SELECT * from aqbudgets|);
2976 my $updatebudgets=$dbh->prepare(qq|UPDATE aqbudgets SET budget_period_id= ? , budget_name=?, budget_branchcode=? where budget_id=?|);
2977 $selectbudgets->execute;
2978 while (my $databudget=$selectbudgets->fetchrow_hashref){
2979 $query_period->execute ($$databudget{startdate},$$databudget{enddate});
2980 my ($budgetperiodid)=$query_period->fetchrow;
2981 $query_bookfund->execute ($$databudget{budget_code});
2982 my $databf=$query_bookfund->fetchrow_hashref;
2983 my $branchcode=$$databudget{budget_branchcode}||$$databf{branchcode};
2984 $updatebudgets->execute($budgetperiodid,$$databf{bookfundname},$branchcode,$$databudget{budget_id});
2986 $dbh->do(<<BUDGETDROPDATES);
2987 ALTER TABLE `aqbudgets`
2993 $dbh->do("DROP TABLE IF EXISTS `aqbudgets_planning` ");
2994 $dbh->do("CREATE TABLE `aqbudgets_planning` (
2995 `plan_id` int(11) NOT NULL auto_increment,
2996 `budget_id` int(11) NOT NULL,
2997 `budget_period_id` int(11) NOT NULL,
2998 `estimated_amount` decimal(28,6) default NULL,
2999 `authcat` varchar(30) NOT NULL,
3000 `authvalue` varchar(30) NOT NULL,
3001 `display` tinyint(1) DEFAULT 1,
3002 PRIMARY KEY (`plan_id`),
3003 CONSTRAINT `aqbudgets_planning_ifbk_1` FOREIGN KEY (`budget_id`) REFERENCES `aqbudgets` (`budget_id`) ON DELETE CASCADE ON UPDATE CASCADE
3004 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;");
3006 $dbh->do("ALTER TABLE `aqorders`
3007 ADD COLUMN `budget_id` tinyint(4) NOT NULL,
3008 ADD COLUMN `budgetgroup_id` int(11) NOT NULL,
3009 ADD COLUMN `sort1_authcat` varchar(10) default NULL,
3010 ADD COLUMN `sort2_authcat` varchar(10) default NULL" );
3011 # We need to map the orders to the budgets
3012 # For Historic reasons this is more complex than it should be on occasions
3013 my $budg_arr = $dbh->selectall_arrayref(
3014 q|SELECT aqbudgets.budget_id, aqbudgets.budget_code, aqbudgetperiods.budget_period_startdate,
3015 aqbudgetperiods.budget_period_enddate
3016 FROM aqbudgets JOIN aqbudgetperiods ON aqbudgets.budget_period_id = aqbudgetperiods.budget_period_id
3017 ORDER BY budget_code, budget_period_startdate|, { Slice => {} });
3018 # We arbitarily order on start date, this means if you have overlapping periods the order will be
3019 # linked to the latest matching budget YMMV
3020 my $b_sth = $dbh->prepare(
3021 'UPDATE fundmapping set aqbudgetid = ? where bookfundid =? AND budgetdate >= ? AND budgetdate <= ?');
3022 for my $b ( @{$budg_arr}) {
3023 $b_sth->execute($b->{budget_id}, $b->{budget_code}, $b->{budget_period_startdate}, $b->{budget_period_enddate});
3025 # move the budgetids to aqorders
3026 $dbh->do(q|UPDATE aqorders, fundmapping SET aqorders.budget_id = fundmapping.aqbudgetid
3027 WHERE aqorders.ordernumber = fundmapping.ordernumber AND fundmapping.aqbudgetid IS NOT NULL|);
3028 # NB fundmapping is left as an accontants trail also if you have budgetids that werent set
3029 # you can decide what to do with them
3032 q|UPDATE aqorders, aqbudgets SET aqorders.budgetgroup_id = aqbudgets.budget_period_id
3033 WHERE aqorders.budget_id = aqbudgets.budget_id|);
3034 # cannot do until aqorderbreakdown removed
3035 # $dbh->do("DROP TABLE aqbookfund ");
3036 # $dbh->do("ALTER TABLE aqorders ADD FOREIGN KEY (`budget_id`) REFERENCES `aqbudgets` (`budget_id`) ON UPDATE CASCADE " ); ????
3037 $dbh->do("SET FOREIGN_KEY_CHECKS=1 ");
3039 print "Upgrade to $DBversion done (Adding new aqbudgetperiods, aqbudgets and aqbudget_planning tables )\n";
3040 SetVersion ($DBversion);
3045 $DBversion = '3.01.00.078';
3046 if ( C4::Context->preference("Version") < TransformToNum($DBversion) ) {
3047 $dbh->do("ALTER TABLE aqbudgetperiods ADD COLUMN budget_period_total decimal(28,6)");
3048 print "Upgrade to $DBversion done (adds 'budget_period_total' column to aqbudgetperiods table)\n";
3049 SetVersion($DBversion);
3053 $DBversion = '3.01.00.079';
3054 if ( C4::Context->preference("Version") < TransformToNum($DBversion) ) {
3055 $dbh->do("ALTER TABLE currency ADD COLUMN active tinyint(1)");
3057 print "Upgrade to $DBversion done (adds 'active' column to currencies table)\n";
3058 SetVersion($DBversion);
3061 $DBversion = '3.01.00.080';
3062 if ( C4::Context->preference("Version") < TransformToNum($DBversion) ) {
3063 $dbh->do(<<BUDG_PERM );
3064 INSERT INTO permissions (module_bit, code, description) VALUES
3065 (11, 'vendors_manage', 'Manage vendors'),
3066 (11, 'contracts_manage', 'Manage contracts'),
3067 (11, 'period_manage', 'Manage periods'),
3068 (11, 'budget_manage', 'Manage budgets'),
3069 (11, 'budget_modify', "Modify budget (can't create lines but can modify existing ones)"),
3070 (11, 'planning_manage', 'Manage budget plannings'),
3071 (11, 'order_manage', 'Manage orders & basket'),
3072 (11, 'group_manage', 'Manage orders & basketgroups'),
3073 (11, 'order_receive', 'Manage orders & basket'),
3074 (11, 'budget_add_del', "Add and delete budgets (but can't modify budgets)");
3077 print "Upgrade to $DBversion done (adds permissions for the acquisitions module)\n";
3078 SetVersion($DBversion);
3082 $DBversion = '3.01.00.081';
3083 if ( C4::Context->preference("Version") < TransformToNum($DBversion) ) {
3084 $dbh->do("ALTER TABLE aqbooksellers ADD COLUMN `gstrate` decimal(6,4) default NULL");
3085 if (my $gist=C4::Context->preference("gist")){
3086 my $sql=$dbh->prepare("UPDATE aqbooksellers set `gstrate`=? ");
3087 $sql->execute($gist) ;
3089 print "Upgrade to $DBversion done (added per-supplier gstrate setting)\n";
3090 SetVersion($DBversion);
3093 $DBversion = "3.01.00.082";
3094 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
3095 if (C4::Context->preference("opaclanguages") eq "fr") {
3096 $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')#);
3098 $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')");
3100 print "Upgrade to $DBversion done (adding ReservesNeedReturns systempref, in circulation)\n";
3101 SetVersion ($DBversion);
3104 $DBversion = "3.01.00.083";
3105 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
3107 CREATE TABLE `aqorders_items` (
3108 `ordernumber` int(11) NOT NULL,
3109 `itemnumber` int(11) NOT NULL,
3110 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
3111 PRIMARY KEY (`itemnumber`),
3112 KEY `ordernumber` (`ordernumber`)
3113 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
3116 $dbh->do(qq| DROP TABLE aqorderbreakdown |);
3117 $dbh->do('DROP TABLE aqbookfund');
3118 print "Upgrade to $DBversion done (New aqorders_items table for acqui)\n";
3119 SetVersion ($DBversion);
3122 $DBversion = "3.01.00.084";
3123 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
3124 $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') #);
3126 print "Upgrade to $DBversion done (CurrencyFormat syspref added)\n";
3127 SetVersion ($DBversion);
3130 $DBversion = "3.01.00.085";
3131 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
3132 $dbh->do("ALTER table aqorders drop column title");
3133 $dbh->do("ALTER TABLE `aqorders` CHANGE `budget_id` `budget_id` INT( 11 ) NOT NULL");
3134 print "Upgrade to $DBversion done update budget_id size that should not be a tinyint\n";
3135 SetVersion ($DBversion);
3138 $DBversion = "3.01.00.086";
3139 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
3140 $dbh->do(<<SUGGESTIONS);
3141 ALTER table suggestions
3142 ADD budgetid INT(11),
3143 ADD branchcode VARCHAR(10) default NULL,
3144 ADD acceptedby INT(11) default NULL,
3145 ADD accepteddate date default NULL,
3146 ADD suggesteddate date default NULL,
3147 ADD manageddate date default NULL,
3148 ADD rejectedby INT(11) default NULL,
3149 ADD rejecteddate date default NULL,
3150 ADD collectiontitle text default NULL,
3151 ADD itemtype VARCHAR(30) default NULL
3154 print "Upgrade to $DBversion done Suggestions";
3155 SetVersion ($DBversion);
3158 $DBversion = "3.01.00.087";
3159 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
3160 $dbh->do("ALTER table aqbudgets drop column budget_amount_sublevel;");
3161 print "Upgrade to $DBversion done drop column budget_amount_sublevel from aqbudgets\n";
3162 SetVersion ($DBversion);
3165 $DBversion = "3.01.00.088";
3166 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
3167 $dbh->do( qq# INSERT INTO `systempreferences` VALUES ('intranetbookbag','1','','If ON, enables display of Cart feature in the intranet','YesNo') #);
3169 print "Upgrade to $DBversion done (intranetbookbag syspref added)\n";
3170 SetVersion ($DBversion);
3173 $DBversion = "3.01.00.090";
3174 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
3176 INSERT INTO `permissions` (`module_bit`, `code`, `description`) VALUES
3177 (16, 'execute_reports', 'Execute SQL reports'),
3178 (16, 'create_reports', 'Create SQL Reports')
3181 print "Upgrade to $DBversion done (granular permissions for guided reports added)\n";
3182 SetVersion ($DBversion);
3185 $DBversion = "3.01.00.091";
3186 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
3188 UPDATE `systempreferences` SET `options` = 'holdings|serialcollection|subscriptions'
3189 WHERE `systempreferences`.`variable` = 'opacSerialDefaultTab' LIMIT 1
3192 print "Upgrade to $DBversion done (opac-detail default tag updated)\n";
3193 SetVersion ($DBversion);
3196 $DBversion = "3.01.00.092";
3197 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
3198 if (C4::Context->preference("opaclanguages") =~ /fr/) {
3200 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');
3204 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');
3207 print "Upgrade to $DBversion done (Added RoutingListAddReserves syspref)\n";
3208 SetVersion ($DBversion);
3211 $DBversion = "3.01.00.093";
3212 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
3214 ALTER TABLE biblioitems ADD INDEX issn_idx (issn);
3216 print "Upgrade to $DBversion done (added index to ISSN)\n";
3217 SetVersion ($DBversion);
3220 $DBversion = "3.01.00.094";
3221 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
3223 ALTER TABLE aqbasketgroups ADD deliveryplace VARCHAR(10) default NULL, ADD deliverycomment VARCHAR(255) default NULL;
3226 print "Upgrade to $DBversion done (adding deliveryplace deliverycomment to basketgroups)\n";
3227 SetVersion ($DBversion);
3230 $DBversion = "3.01.00.095";
3231 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
3233 ALTER TABLE items ADD stocknumber VARCHAR(32) DEFAULT NULL COMMENT "stores the inventory number";
3236 ALTER TABLE items ADD UNIQUE INDEX itemsstocknumberidx (stocknumber);
3239 ALTER TABLE deleteditems ADD stocknumber VARCHAR(32) DEFAULT NULL COMMENT "stores the inventory number of deleted items";
3242 ALTER TABLE deleteditems ADD UNIQUE INDEX deleteditemsstocknumberidx (stocknumber);
3244 if (C4::Context->preference('marcflavour') eq 'UNIMARC'){
3246 INSERT IGNORE INTO marc_subfield_structure (frameworkcode,tagfield, tagsubfield, tab, repeatable, mandatory,kohafield)
3247 SELECT DISTINCT (frameworkcode),995,"j",10,0,0,"items.stocknumber" from biblio_framework ;
3249 #Previously, copynumber was used as stocknumber
3251 UPDATE items set stocknumber=copynumber;
3254 UPDATE items set copynumber=NULL;
3257 print "Upgrade to $DBversion done (stocknumber field added)\n";
3258 SetVersion ($DBversion);
3261 $DBversion = "3.01.00.096";
3262 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
3263 $dbh->do("INSERT INTO systempreferences (variable,value,explanation,options,type) VALUES ('OrderPdfTemplate','','Uploads a PDF template to use for printing baskets','NULL','Upload')");
3264 $dbh->do("UPDATE systempreferences SET variable='OrderPdfFormat' WHERE variable='pdfformat'");
3265 print "Upgrade to $DBversion done (PDF orders system preferences added and updated)\n";
3266 SetVersion ($DBversion);
3269 $DBversion = "3.01.00.097";
3270 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
3272 ALTER TABLE aqbasketgroups ADD billingplace VARCHAR(10) NOT NULL AFTER deliverycomment;
3275 print "Upgrade to $DBversion done (Adding billingplace to aqbasketgroups)\n";
3276 SetVersion ($DBversion);
3279 $DBversion = "3.01.00.098";
3280 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
3282 ALTER TABLE auth_subfield_structure MODIFY frameworkcode VARCHAR(10) NULL;
3285 print "Upgrade to $DBversion done (changing frameworkcode length in auth_subfield_structure)\n";
3286 SetVersion ($DBversion);
3289 $DBversion = "3.01.00.099";
3290 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
3292 INSERT INTO `permissions` (`module_bit`, `code`, `description`) VALUES
3293 (9, 'edit_catalogue', 'Edit catalogue'),
3294 (9, 'fast_cataloging', 'Fast cataloging')
3297 print "Upgrade to $DBversion done (granular permissions for cataloging added)\n";
3298 SetVersion ($DBversion);
3301 $DBversion = "3.01.00.100";
3302 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
3303 $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')");
3304 print "Upgrade done (added CAS authentication system preferences)\n";
3305 SetVersion ($DBversion);
3308 $DBversion = "3.01.00.101";
3309 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
3311 "INSERT INTO systempreferences
3312 (variable, value, options, explanation, type)
3314 'OverdueNoticeBcc', '', '',
3315 'Email address to Bcc outgoing notices sent by email',
3318 print "Upgrade to $DBversion done (added OverdueNoticeBcc system preferences)\n";
3319 SetVersion ($DBversion);
3321 $DBversion = "3.01.00.102";
3322 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
3324 "UPDATE permissions set description = 'Edit catalog (Modify bibliographic/holdings data)' where module_bit = 9 and code = 'edit_catalogue'"
3326 print "Upgrade done (fixed spelling error in edit_catalogue permission)\n";
3327 SetVersion ($DBversion);
3330 $DBversion = "3.01.00.103";
3331 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
3332 $dbh->do("INSERT INTO permissions (module_bit, code, description) VALUES (13, 'moderate_tags', 'Moderate patron tags')");
3333 print "Upgrade done (adding patron permissions for tags tool)\n";
3334 SetVersion ($DBversion);
3337 $DBversion = "3.01.00.104";
3338 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
3340 my ($maninv_count, $borrnotes_count);
3341 eval { $maninv_count = $dbh->do("SELECT 1 FROM authorised_values WHERE category='MANUAL_INV'"); };
3342 if ($maninv_count == 0) {
3343 $dbh->do("INSERT INTO authorised_values (category,authorised_value,lib) VALUES ('MANUAL_INV','Copier Fees','.25')");
3345 eval { $borrnotes_count = $dbh->do("SELECT 1 FROM authorised_values WHERE category='BOR_NOTES'"); };
3346 if ($borrnotes_count == 0) {
3347 $dbh->do("INSERT INTO authorised_values (category,authorised_value,lib) VALUES ('BOR_NOTES','ADDR','Address Notes')");
3350 $dbh->do("INSERT INTO authorised_values (category,authorised_value,lib) VALUES ('LOC','CART','Book Cart')");
3351 $dbh->do("INSERT INTO authorised_values (category,authorised_value,lib) VALUES ('LOC','PROC','Processing Center')");
3353 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";
3354 SetVersion ($DBversion);
3358 $DBversion = "3.01.00.105";
3359 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
3361 CREATE TABLE `collections` (
3362 `colId` int(11) NOT NULL auto_increment,
3363 `colTitle` varchar(100) NOT NULL default '',
3364 `colDesc` text NOT NULL,
3365 `colBranchcode` varchar(4) default NULL COMMENT 'branchcode for branch where item should be held.',
3366 PRIMARY KEY (`colId`)
3367 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
3371 CREATE TABLE `collections_tracking` (
3372 `ctId` int(11) NOT NULL auto_increment,
3373 `colId` int(11) NOT NULL default '0' COMMENT 'collections.colId',
3374 `itemnumber` int(11) NOT NULL default '0' COMMENT 'items.itemnumber',
3375 PRIMARY KEY (`ctId`)
3376 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
3379 INSERT INTO permissions (module_bit, code, description)
3380 VALUES ( 13, 'rotating_collections', 'Manage Rotating collections')" );
3381 print "Upgrade to $DBversion done (added collection and collection_tracking tables for rotating collections functionality)\n";
3382 SetVersion ($DBversion);
3384 $DBversion = "3.01.00.106";
3385 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
3386 $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' )");
3387 print "Upgrade done (added OpacAddMastheadLibraryPulldown system preferences)\n";
3388 SetVersion ($DBversion);
3391 $DBversion = '3.01.00.107';
3392 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
3393 my $upgrade_script = C4::Context->config("intranetdir") . "/installer/data/mysql/patroncards_upgrade.pl";
3394 system("perl $upgrade_script");
3395 print "Upgrade to $DBversion done (Migrated labels and patroncards tables and data to new schema.)\n";
3396 SetVersion ($DBversion);
3399 $DBversion = '3.01.00.108';
3400 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
3402 ALTER TABLE `export_format` ADD `csv_separator` VARCHAR( 2 ) NOT NULL AFTER `marcfields` ,
3403 ADD `field_separator` VARCHAR( 2 ) NOT NULL AFTER `csv_separator` ,
3404 ADD `subfield_separator` VARCHAR( 2 ) NOT NULL AFTER `field_separator`
3406 print "Upgrade done (added separators for csv export)\n";
3407 SetVersion ($DBversion);
3410 $DBversion = "3.01.00.109";
3411 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
3413 ALTER TABLE `export_format` ADD `encoding` VARCHAR(255) NOT NULL AFTER `subfield_separator`
3415 print "Upgrade done (added encoding for csv export)\n";
3416 SetVersion ($DBversion);
3419 $DBversion = '3.01.00.110';
3420 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
3421 $dbh->do('ALTER TABLE `categories` ADD COLUMN `enrolmentperioddate` DATE NULL DEFAULT NULL AFTER `enrolmentperiod`');
3422 print "Upgrade done (Add enrolment period date support)\n";
3423 SetVersion ($DBversion);
3426 $DBversion = '3.01.00.111';
3427 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
3428 print "Upgrade done (mark DBrev for 3.2-alpha release)\n";
3429 SetVersion ($DBversion);
3432 $DBversion = '3.01.00.112';
3433 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
3434 $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');");
3435 print "Upgrade done ( added Show Spine Label Printer on Bib Items Details preferences )\n";
3436 SetVersion ($DBversion);
3439 $DBversion = '3.01.00.113';
3440 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
3441 my $value = C4::Context->preference("XSLTResultsDisplay");
3443 "INSERT INTO systempreferences (variable,value,type)
3444 VALUES('OPACXSLTResultsDisplay',?,'YesNo')", {}, $value ? 1 : 0);
3445 $value = C4::Context->preference("XSLTDetailsDisplay");
3447 "INSERT INTO systempreferences (variable,value,type)
3448 VALUES('OPACXSLTDetailsDisplay',?,'YesNo')", {}, $value ? 1 : 0);
3449 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 ";
3450 SetVersion ($DBversion);
3453 $DBversion = '3.01.00.114';
3454 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
3455 $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')");
3456 $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')");
3457 $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')");
3458 print "Upgrade to $DBversion done ( Added AutoSelfCheckAllowed, AutoSelfCheckID, and AutoShelfCheckPass system preference )\n";
3459 SetVersion ($DBversion);
3462 $DBversion = '3.01.00.115';
3463 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
3464 $dbh->do('UPDATE aqorders SET quantityreceived = 0 WHERE quantityreceived IS NULL');
3465 $dbh->do('ALTER TABLE aqorders MODIFY COLUMN quantityreceived smallint(6) NOT NULL DEFAULT 0');
3466 print "Upgrade to $DBversion done ( Default aqorders.quantityreceived to 0 )\n";
3467 SetVersion ($DBversion);
3470 $DBversion = '3.01.00.116';
3471 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
3472 if (C4::Context->preference('OrderPdfFormat') eq 'pdfformat::example'){
3473 $dbh->do("UPDATE `systempreferences` set value='pdfformat::layout2pages' WHERE variable='OrderPdfFormat'");
3475 print "Upgrade done ( corrected default OrderPdfFormat value if still set wrong )\n";
3476 SetVersion ($DBversion);
3479 $DBversion = '3.01.00.117';
3480 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
3481 $dbh->do("UPDATE language_rfc4646_to_iso639 SET iso639_2_code = 'por' WHERE rfc4646_subtag='pt' ");
3482 print "Upgrade to $DBversion done (corrected ISO 639-2 language code for Portuguese)\n";
3483 SetVersion ($DBversion);
3486 $DBversion = '3.01.00.118';
3487 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
3488 my ($count) = $dbh->selectrow_array("SELECT count(*) FROM information_schema.columns
3489 WHERE table_name = 'aqbudgets_planning'
3490 AND column_name = 'display'");
3492 $dbh->do("ALTER TABLE aqbudgets_planning ADD COLUMN display tinyint(1) DEFAULT 1");
3494 print "Upgrade to $DBversion done (bug 4203: add display column to aqbudgets_planning if missing)\n";
3495 SetVersion ($DBversion);
3498 $DBversion = '3.01.00.119';
3499 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
3500 eval{require Locale::Currency::Format};
3502 print "Upgrade to $DBversion done (Locale::Currency::Format installed.)\n";
3503 SetVersion ($DBversion);
3506 print "Upgrade to $DBversion done.\n";
3507 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";
3508 SetVersion ($DBversion);
3512 $DBversion = '3.01.00.120';
3513 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
3515 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');
3517 print "Upgrade to $DBversion done (bug 1080: add soundon system preference for circulation sounds)\n";
3518 SetVersion ($DBversion);
3521 $DBversion = '3.01.00.121';
3522 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
3523 $dbh->do("ALTER TABLE `reserves` ADD `expirationdate` DATE DEFAULT NULL");
3524 $dbh->do("ALTER TABLE `reserves` ADD `lowestPriority` tinyint(1) NOT NULL");
3525 $dbh->do("ALTER TABLE `old_reserves` ADD `expirationdate` DATE DEFAULT NULL");
3526 $dbh->do("ALTER TABLE `old_reserves` ADD `lowestPriority` tinyint(1) NOT NULL");
3527 print "Upgrade to $DBversion done ( Added Additional Fields to Reserves tables )\n";
3528 SetVersion ($DBversion);
3531 $DBversion = '3.01.00.122';
3532 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
3534 INSERT INTO systempreferences (variable,value,explanation,options,type)
3535 VALUES ('OAI-PMH:ConfFile', '', 'If empty, Koha OAI Server operates in normal mode, otherwise it operates in extended mode.','','File');
3537 print "Upgrade to $DBversion done. — Add a new system preference OAI-PMF:ConfFile\n";
3538 SetVersion ($DBversion);
3541 $DBversion = "3.01.00.123";
3542 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
3543 $dbh->do("INSERT INTO `permissions` (`module_bit`, `code`, `description`) VALUES
3544 (6, 'place_holds', 'Place holds for patrons')");
3545 $dbh->do("INSERT INTO `permissions` (`module_bit`, `code`, `description`) VALUES
3546 (6, 'modify_holds_priority', 'Modify holds priority')");
3547 $dbh->do("UPDATE `userflags` SET `flagdesc` = 'Place and modify holds for patrons' WHERE `flag` = 'reserveforothers'");
3548 print "Upgrade to $DBversion done (Add granular permission for holds modification and update description of reserveforothers permission)\n";
3549 SetVersion ($DBversion);
3552 $DBversion = '3.01.00.124';
3553 if (C4::Context->preference('Version') < TransformToNum($DBversion)){
3555 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>>).');
3557 print "Upgrade to $DBversion done (bug 3242: add HOLDPLACED letter template, which is used when emailLibrarianWhenHoldIsPlaced is enabled)\n";
3558 SetVersion ($DBversion);
3561 $DBversion = '3.01.00.125';
3562 if (C4::Context->preference('Version') < TransformToNum($DBversion)){
3564 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' );
3567 INSERT INTO message_transport_types (message_transport_type) values ('print');
3569 print "Upgrade to $DBversion done (bug 3482: Printable hold and overdue notices)\n";
3570 SetVersion ($DBversion);
3573 $DBversion = "3.01.00.126";
3574 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
3575 $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')");
3576 $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')");
3578 print "Upgrade to $DBversion done (Adding ILS-DI updates and ILS-DI:AuthorizedIPs)\n";
3579 SetVersion ($DBversion);
3582 $DBversion = '3.01.00.127';
3583 if (C4::Context->preference('Version') < TransformToNum($DBversion)){
3584 $dbh->do("ALTER TABLE messages CHANGE branchcode branchcode varchar(10);");
3585 print "Upgrade to $DBversion done (bug 4190: messages in patron account did not work with branchcodes > 4)\n";
3586 SetVersion ($DBversion);
3589 $DBversion = '3.01.00.128';
3590 if (C4::Context->preference('Version') < TransformToNum($DBversion)){
3591 $dbh->do('CREATE INDEX budget_id ON aqorders (budget_id );');
3592 print "Upgrade to $DBversion done (bug 4331: index orders by budget_id)\n";
3593 SetVersion ($DBversion);
3596 $DBversion = "3.01.00.129";
3597 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
3598 $dbh->do("UPDATE `permissions` SET `code` = 'items_batchdel' WHERE `permissions`.`module_bit` =13 AND `permissions`.`code` = 'batchdel' LIMIT 1 ;");
3599 $dbh->do("UPDATE `permissions` SET `code` = 'items_batchmod' WHERE `permissions`.`module_bit` =13 AND `permissions`.`code` = 'batchmod' LIMIT 1 ;");
3600 print "Upgrade done (Change permissions names for item batch modification / deletion)\n";
3602 SetVersion ($DBversion);
3605 $DBversion = "3.01.00.130";
3606 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
3607 $dbh->do("UPDATE reserves SET expirationdate = NULL WHERE expirationdate = '0000-00-00'");
3608 print "Upgrade done (change reserves.expirationdate values of 0000-00-00 to NULL (bug 1532)";
3609 SetVersion ($DBversion);
3612 $DBversion = "3.01.00.131";
3613 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
3615 INSERT IGNORE INTO message_transport_types (message_transport_type) VALUES ('print'),('feed');
3617 print "Upgrade to $DBversion done (adding print and feed message transport types)\n";
3618 SetVersion ($DBversion);
3621 $DBversion = "3.01.00.132";
3622 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
3624 ALTER TABLE language_descriptions ADD INDEX subtag_type_lang (subtag, type, lang);
3626 print "Upgrade to $DBversion done (Adding index to language_descriptions table)\n";
3627 SetVersion ($DBversion);
3630 $DBversion = '3.01.00.133';
3631 if (C4::Context->preference('Version') < TransformToNum($DBversion)){
3632 $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')");
3633 print "Upgrade to $DBversion done (bug 4405: added OverduesBlockCirc syspref to control whether circulation is blocked if a borrower has overdues)\n";
3634 SetVersion ($DBversion);
3637 $DBversion = '3.01.00.134';
3638 if (C4::Context->preference('Version') < TransformToNum($DBversion)){
3639 $dbh->do("INSERT INTO systempreferences (variable,value,explanation,options,type) VALUES ('DisplayMultiPlaceHold','1','Display the ability to place multiple holds or not','','YesNo')");
3640 print "Upgrade to $DBversion done adding syspref DisplayMultiPlaceHold to control whether multiple holds can be placed from the search results page";
3641 SetVersion ($DBversion);
3644 $DBversion = '3.01.00.135';
3645 if (C4::Context->preference('Version') < TransformToNum($DBversion)){
3647 INSERT INTO `letter` (module, code, name, title, content) VALUES
3648 ('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')
3650 print "Upgrade to $DBversion done (bug 4377: added HOLD_PRINT message template)";
3651 SetVersion ($DBversion);
3654 $DBversion = '3.01.00.136';
3655 if (C4::Context->preference('Version') < TransformToNum($DBversion)){
3657 INSERT INTO permissions (module_bit, code, description) VALUES
3658 ( 9, 'edit_items', 'Edit Items');});
3659 print "Upgrade to $DBversion done Adding a new permission to edit items";
3660 SetVersion ($DBversion);
3663 $DBversion = "3.01.00.137";
3664 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
3666 INSERT INTO permissions (module_bit, code, description) VALUES
3667 (15, 'check_expiration', 'Check the expiration of a serial'),
3668 (15, 'claim_serials', 'Claim missing serials'),
3669 (15, 'create_subscription', 'Create a new subscription'),
3670 (15, 'delete_subscription', 'Delete an existing subscription'),
3671 (15, 'edit_subscription', 'Edit an existing subscription'),
3672 (15, 'receive_serials', 'Serials receiving'),
3673 (15, 'renew_subscription', 'Renew a subscription'),
3674 (15, 'routing', 'Routing');
3676 print "Upgrade to $DBversion done (adding granular permissions for serials)";
3677 SetVersion ($DBversion);
3680 $DBversion = "3.01.00.138";
3681 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
3682 $dbh->do("DELETE FROM systempreferences WHERE variable = 'GranularPermissions'");
3683 print "Upgrade to $DBversion done (bug 4896: removing GranularPermissions syspref; use of granular permissions is now the default)";
3684 SetVersion ($DBversion);
3687 $DBversion = '3.01.00.139';
3688 if (C4::Context->preference('Version') < TransformToNum($DBversion)){
3689 $dbh->do("ALTER TABLE message_attributes CHANGE message_name message_name varchar(40);");
3690 print "Upgrade to $DBversion done (bug 3682: change message_name from varchar(20) to varchar(40))\n";
3691 SetVersion ($DBversion);
3694 $DBversion = '3.01.00.140';
3695 if (C4::Context->preference('Version') < TransformToNum($DBversion)){
3696 $dbh->do("UPDATE systempreferences SET value = '0' WHERE variable = 'TagsModeration' AND value is NULL");
3697 print "Upgrade to $DBversion done (bug 4312 TagsModeration changed from NULL to 0)\n";
3698 SetVersion ($DBversion);
3701 $DBversion = '3.01.00.141';
3702 if (C4::Context->preference('Version') < TransformToNum($DBversion)){
3703 $dbh->do(qq{DELETE FROM message_attributes WHERE message_attribute_id=3;});
3704 $dbh->do(qq{DELETE FROM letter WHERE code='EVENT' AND title='Upcoming Library Event';});
3705 print "Upgrade to $DBversion done Remove upcoming events messaging option (bug 2434)";
3706 SetVersion ($DBversion);
3709 $DBversion = '3.01.00.142';
3710 if (C4::Context->preference('Version') < TransformToNum($DBversion)){
3711 $dbh->do(qq{DELETE FROM message_transports WHERE message_attribute_id=3;});
3712 print "Upgrade to $DBversion done Remove upcoming events messaging option part 2 (bug 2434)";
3713 SetVersion ($DBversion);
3716 $DBversion = '3.01.00.143';
3717 if (C4::Context->preference('Version') < TransformToNum($DBversion)){
3718 $dbh->do(qq{CREATE INDEX auth_value_idx ON authorised_values (authorised_value)});
3719 $dbh->do(qq{CREATE INDEX auth_val_cat_idx ON borrower_attribute_types (authorised_value_category)});
3720 print "Create index on authorised_values and borrower_attribute_types (bug 4139)";
3721 SetVersion ($DBversion);
3724 $DBversion = '3.01.00.144';
3725 if (C4::Context->preference('Version') < TransformToNum($DBversion)){
3726 $dbh->do(qq{UPDATE systempreferences SET value='normal' where value='default' and variable='IntranetBiblioDefaultView'});
3727 print "Update the 'default' to 'normal' for the IntranetBiblioDefaultView syspref (bug 5007)";
3728 SetVersion ($DBversion);
3731 $DBversion = "3.01.00.145";
3732 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
3733 $dbh->do("ALTER TABLE borrowers ADD KEY `guarantorid` (guarantorid);");
3734 print "Upgrade to $DBversion done (Add index on guarantorid)\n";
3735 SetVersion ($DBversion);
3738 $DBversion = '3.01.00.999';
3739 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
3740 print "Upgrade to $DBversion done (3.2.0 release candidate)\n";
3741 SetVersion ($DBversion);
3744 $DBversion = "3.02.00.000";
3745 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
3746 my $value = $dbh->selectrow_array("SELECT value FROM systempreferences WHERE variable = 'HomeOrHoldingBranch'");
3747 $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');");
3748 print "Upgrade to $DBversion done (Add HomeOrHoldingBranchReturn system preference)\n";
3749 SetVersion ($DBversion);
3752 $DBversion = "3.02.00.001";
3753 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
3754 $dbh->do(q{DELETE FROM systempreferences WHERE variable IN (
3759 'OPACSubscriptionDisplay',
3760 'OPACDisplayExtendedSubInfo',
3771 print "Upgrade to $DBversion done (bug 3756: remove disused system preferences)\n";
3772 SetVersion ($DBversion);
3775 $DBversion = "3.02.00.002";
3776 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
3777 $dbh->do(q{DELETE FROM systempreferences WHERE variable = 'OpacPrivacy'});
3778 print "Upgrade to $DBversion done (bug 3881: remove unused OpacPrivacy system preference)\n";
3779 SetVersion ($DBversion);
3782 $DBversion = "3.02.00.003";
3783 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
3784 $dbh->do(q{UPDATE systempreferences SET variable = 'ILS-DI:AuthorizedIPs' WHERE variable = 'ILS-DI:Authorized_IPs'});
3785 print "Upgrade to $DBversion done (correct ILS-DI:AuthorizedIPs)\n";
3786 SetVersion ($DBversion);
3789 $DBversion = "3.02.00.004";
3790 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
3791 print "Upgrade to $DBversion done (3.2.0 general release)\n";
3792 SetVersion ($DBversion);
3795 $DBversion = "3.03.00.001";
3796 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
3797 $dbh->do("DELETE FROM subscriptionroutinglist WHERE borrowernumber IS NULL;");
3798 $dbh->do("ALTER TABLE subscriptionroutinglist MODIFY COLUMN `borrowernumber` int(11) NOT NULL;");
3799 $dbh->do("DELETE FROM subscriptionroutinglist WHERE subscriptionid IS NULL;");
3800 $dbh->do("ALTER TABLE subscriptionroutinglist MODIFY COLUMN `subscriptionid` int(11) NOT NULL;");
3801 $dbh->do("CREATE TEMPORARY TABLE del_subscriptionroutinglist
3802 SELECT s1.routingid FROM subscriptionroutinglist s1
3803 WHERE EXISTS (SELECT * FROM subscriptionroutinglist s2
3804 WHERE s2.borrowernumber = s1.borrowernumber
3805 AND s2.subscriptionid = s1.subscriptionid
3806 AND s2.routingid < s1.routingid);");
3807 $dbh->do("DELETE FROM subscriptionroutinglist
3808 WHERE routingid IN (SELECT routingid FROM del_subscriptionroutinglist);");
3809 $dbh->do("ALTER TABLE subscriptionroutinglist ADD UNIQUE (subscriptionid, borrowernumber);");
3810 $dbh->do("ALTER TABLE subscriptionroutinglist
3811 ADD CONSTRAINT `subscriptionroutinglist_ibfk_1` FOREIGN KEY (`borrowernumber`)
3812 REFERENCES `borrowers` (`borrowernumber`)
3813 ON DELETE CASCADE ON UPDATE CASCADE");
3814 $dbh->do("ALTER TABLE subscriptionroutinglist
3815 ADD CONSTRAINT `subscriptionroutinglist_ibfk_2` FOREIGN KEY (`subscriptionid`)
3816 REFERENCES `subscription` (`subscriptionid`)
3817 ON DELETE CASCADE ON UPDATE CASCADE");
3818 print "Upgrade to $DBversion done (Make subscriptionroutinglist more strict)\n";
3819 SetVersion ($DBversion);
3822 $DBversion = '3.03.00.002';
3823 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
3824 $dbh->do("UPDATE language_rfc4646_to_iso639 SET iso639_2_code='arm' WHERE rfc4646_subtag='hy';");
3825 $dbh->do("UPDATE language_rfc4646_to_iso639 SET iso639_2_code='eng' WHERE rfc4646_subtag='en';");
3826 $dbh->do("INSERT INTO language_rfc4646_to_iso639(rfc4646_subtag,iso639_2_code) VALUES( 'fi','fin');");
3827 $dbh->do("UPDATE language_rfc4646_to_iso639 SET iso639_2_code='fre' WHERE rfc4646_subtag='fr';");
3828 $dbh->do("INSERT INTO language_rfc4646_to_iso639(rfc4646_subtag,iso639_2_code) VALUES( 'lo','lao');");
3829 $dbh->do("UPDATE language_rfc4646_to_iso639 SET iso639_2_code='ita' WHERE rfc4646_subtag='it';");
3830 $dbh->do("INSERT INTO language_rfc4646_to_iso639(rfc4646_subtag,iso639_2_code) VALUES( 'sr','srp');");
3831 $dbh->do("INSERT INTO language_rfc4646_to_iso639(rfc4646_subtag,iso639_2_code) VALUES( 'tet','tet');");
3832 $dbh->do("INSERT INTO language_rfc4646_to_iso639(rfc4646_subtag,iso639_2_code) VALUES( 'ur','urd');");
3834 print "Upgrade to $DBversion done (Correct language mappings)\n";
3835 SetVersion ($DBversion);
3838 $DBversion = '3.03.00.003';
3839 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
3840 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('UseTablesortForCirc','0','If on, use the JQuery tablesort function on the list of current borrower checkouts on the circulation page. Note that the use of this function may slow down circ for patrons with may checkouts.','','YesNo');");
3841 print "Upgrade to $DBversion done (Add UseTablesortForCirc syspref)\n";
3842 SetVersion ($DBversion);
3845 $DBversion = '3.03.00.004';
3846 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
3847 my $count = $dbh->selectrow_array('SELECT COUNT(*) FROM letter WHERE module = ? AND code = ?', {}, 'suggestions', 'ACCEPTED');
3849 INSERT INTO `letter`
3850 (module, code, name, title, content)
3852 ('suggestions','ACCEPTED','Suggestion accepted', 'Purchase suggestion accepted','Dear <<borrowers.firstname>> <<borrowers.surname>>,\n\nYou have suggested that the library acquire <<suggestions.title>> by <<suggestions.author>>.\n\nThe library has reviewed your suggestion today. The item will be ordered as soon as possible. You will be notified by mail when the order is completed, and again when the item arrives at the library.\n\nIf you have any questions, please email us at <<branches.branchemail>>.\n\nThank you,\n\n<<branches.branchname>>')
3853 /) unless $count > 0;
3854 $count = $dbh->selectrow_array('SELECT COUNT(*) FROM letter WHERE module = ? AND code = ?', {}, 'suggestions', 'AVAILABLE');
3856 INSERT INTO `letter`
3857 (module, code, name, title, content)
3859 ('suggestions','AVAILABLE','Suggestion available', 'Suggested purchase available','Dear <<borrowers.firstname>> <<borrowers.surname>>,\n\nYou have suggested that the library acquire <<suggestions.title>> by <<suggestions.author>>.\n\nWe are pleased to inform you that the item you requested is now part of the collection.\n\nIf you have any questions, please email us at <<branches.branchemail>>.\n\nThank you,\n\n<<branches.branchname>>')
3860 /) unless $count > 0;
3861 $count = $dbh->selectrow_array('SELECT COUNT(*) FROM letter WHERE module = ? AND code = ?', {}, 'suggestions', 'ORDERED');
3863 INSERT INTO `letter`
3864 (module, code, name, title, content)
3866 ('suggestions','ORDERED','Suggestion ordered', 'Suggested item ordered','Dear <<borrowers.firstname>> <<borrowers.surname>>,\n\nYou have suggested that the library acquire <<suggestions.title>> by <<suggestions.author>>.\n\nWe are pleased to inform you that the item you requested has now been ordered. It should arrive soon, at which time it will be processed for addition into the collection.\n\nYou will be notified again when the book is available.\n\nIf you have any questions, please email us at <<branches.branchemail>>\n\nThank you,\n\n<<branches.branchname>>')
3867 /) unless $count > 0;
3868 $count = $dbh->selectrow_array('SELECT COUNT(*) FROM letter WHERE module = ? AND code = ?', {}, 'suggestions', 'REJECTED');
3870 INSERT INTO `letter`
3871 (module, code, name, title, content)
3873 ('suggestions','REJECTED','Suggestion rejected', 'Purchase suggestion declined','Dear <<borrowers.firstname>> <<borrowers.surname>>,\n\nYou have suggested that the library acquire <<suggestions.title>> by <<suggestions.author>>.\n\nThe library has reviewed your request today, and has decided not to accept the suggestion at this time.\n\nThe reason given is: <<suggestions.reason>>\n\nIf you have any questions, please email us at <<branches.branchemail>>.\n\nThank you,\n\n<<branches.branchname>>')
3874 /) unless $count > 0;
3875 print "Upgrade to $DBversion done (bug 5127: add default templates for suggestion status change notifications)\n";
3876 SetVersion ($DBversion);
3879 $DBversion = '3.03.00.005';
3880 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
3881 $dbh->do("update `systempreferences` set options='whitespace|T-prefix|cuecat|libsuite8' where variable='itemBarcodeInputFilter'");
3882 print "Upgrade to $DBversion done (Add itemBarcodeInputFilter choice libsuite8)\n";
3883 SetVersion ($DBversion);
3886 $DBversion = '3.03.00.006';
3887 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
3888 $dbh->do("ALTER TABLE deletedborrowers ADD `privacy` int(11) AFTER smsalertnumber;");
3889 $dbh->do("ALTER TABLE deletedborrowers CHANGE `cardnumber` `cardnumber` varchar(16);");
3890 print "Upgrade to $DBversion done (Fix differences between borrowers and deletedborrowers)\n";
3891 SetVersion ($DBversion);
3894 $DBversion = '3.03.00.007';
3895 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
3896 $dbh->do("ALTER table suggestions ADD quantity SMALLINT(6) default NULL,
3897 ADD currency VARCHAR(3) default NULL,
3898 ADD price DECIMAL(28,6) default NULL,
3899 ADD total DECIMAL(28,6) default NULL;
3901 print "Upgrade to $DBversion done (Added acq related columns to suggestions)\n";
3902 SetVersion ($DBversion);
3905 $DBversion = '3.03.00.008';
3906 if (C4::Context->preference('Version') < TransformToNum($DBversion)){
3907 $dbh->do("INSERT INTO systempreferences (variable,value,explanation,options,type) VALUES ('OPACNoResultsFound','','Display this HTML when no results are found for a search in the OPAC','70|10','Textarea')");
3908 print "Upgrade to $DBversion done adding syspref OPACNoResultsFound to control what displays when no results are found for a search in the OPAC.";
3909 SetVersion ($DBversion);
3914 =head2 DropAllForeignKeys($table)
3916 Drop all foreign keys of the table $table
3921 sub DropAllForeignKeys {
3923 # get the table description
3924 my $sth = $dbh->prepare("SHOW CREATE TABLE $table");
3926 my $vsc_structure = $sth->fetchrow;
3927 # split on CONSTRAINT keyword
3928 my @fks = split /CONSTRAINT /,$vsc_structure;
3931 # isolate what is before FOREIGN KEY, if there is something, it's a foreign key to drop
3932 $_ = /(.*) FOREIGN KEY.*/;
3935 # we have found 1 foreign, drop it
3936 $dbh->do("ALTER TABLE $table DROP FOREIGN KEY $id");
3943 =head2 TransformToNum
3945 Transform the Koha version from a 4 parts string
3946 to a number, with just 1 .
3950 sub TransformToNum {
3951 my $version = shift;
3952 # remove the 3 last . to have a Perl number
3953 $version =~ s/(.*\..*)\.(.*)\.(.*)/$1$2$3/;
3959 set the DBversion in the systempreferences
3964 my $kohaversion = TransformToNum(shift);
3965 if (C4::Context->preference('Version')) {
3966 my $finish=$dbh->prepare("UPDATE systempreferences SET value=? WHERE variable='Version'");
3967 $finish->execute($kohaversion);
3969 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')");
3970 $finish->execute($kohaversion);
projects / koha.git / blob