1 - $dbh->do("ALTER TABLE issuingrules ADD COLUMN `finedays` int(11) default NULL AFTER `fine` ");
2 - print "Upgrade done (Adding finedays in issuingrules table)\n";
7 # This script checks for required updates to the database.
9 # Part of the Koha Library Software www.koha.org
10 # Licensed under the GPL.
13 # - Would also be a good idea to offer to do a backup at this time...
15 # NOTE: If you do something more than once in here, make it table driven.
17 # NOTE: Please keep the version in kohaversion.pl up-to-date!
30 use MARC::File::XML ( BinaryEncoding => 'utf8' );
32 # FIXME - The user might be installing a new database, so can't rely
33 # on /etc/koha.conf anyway.
40 %existingtables, # tables already in database
44 $type, $null, $key, $default, $extra,
45 $prefitem, # preference item in systempreferences table
52 my $dbh = C4::Context->dbh;
53 $|=1; # flushes output
57 Deal with virtualshelves
61 my $DBversion = "3.00.00.001";
62 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
63 # update virtualshelves table to
65 $dbh->do("ALTER TABLE `bookshelf` RENAME `virtualshelves`");
66 $dbh->do("ALTER TABLE `shelfcontents` RENAME `virtualshelfcontents`");
67 $dbh->do("ALTER TABLE `virtualshelfcontents` ADD `biblionumber` INT( 11 ) NOT NULL default '0' AFTER shelfnumber");
68 $dbh->do("UPDATE `virtualshelfcontents` SET biblionumber=(SELECT biblionumber FROM items WHERE items.itemnumber=virtualshelfcontents.itemnumber)");
69 # drop all foreign keys : otherwise, we can't drop itemnumber field.
70 DropAllForeignKeys('virtualshelfcontents');
71 $dbh->do("ALTER TABLE `virtualshelfcontents` ADD KEY biblionumber (biblionumber)");
72 # create the new foreign keys (on biblionumber)
73 $dbh->do("ALTER TABLE `virtualshelfcontents` ADD CONSTRAINT `virtualshelfcontents_ibfk_1` FOREIGN KEY (`shelfnumber`) REFERENCES `virtualshelves` (`shelfnumber`) ON DELETE CASCADE ON UPDATE CASCADE");
74 # re-create the foreign key on virtualshelf
75 $dbh->do("ALTER TABLE `virtualshelfcontents` ADD CONSTRAINT `shelfcontents_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE");
76 $dbh->do("ALTER TABLE `virtualshelfcontents` DROP `itemnumber`");
77 print "Upgrade to $DBversion done (virtualshelves)\n";
78 SetVersion ($DBversion);
82 $DBversion = "3.00.00.002";
83 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
84 $dbh->do("DROP TABLE sessions");
85 $dbh->do("CREATE TABLE `sessions` (
86 `id` varchar(32) NOT NULL,
87 `a_session` text NOT NULL,
88 UNIQUE KEY `id` (`id`)
89 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;");
90 print "Upgrade to $DBversion done (sessions uses CGI::session, new table structure for sessions)\n";
91 SetVersion ($DBversion);
95 $DBversion = "3.00.00.003";
96 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
97 if (C4::Context->preference("opaclanguages") eq "fr") {
98 $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')");
100 $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')");
102 print "Upgrade to $DBversion done (adding ReservesNeedReturns systempref, in circulation)\n";
103 SetVersion ($DBversion);
107 $DBversion = "3.00.00.004";
108 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
109 $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')");
110 print "Upgrade to $DBversion done (adding DebugLevel systempref, in 'Admin' tab)\n";
111 SetVersion ($DBversion);
114 $DBversion = "3.00.00.005";
115 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
116 $dbh->do("CREATE TABLE `tags` (
117 `entry` varchar(255) NOT NULL default '',
118 `weight` bigint(20) NOT NULL default 0,
119 PRIMARY KEY (`entry`)
120 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
122 $dbh->do("CREATE TABLE `nozebra` (
123 `server` varchar(20) NOT NULL,
124 `indexname` varchar(40) NOT NULL,
125 `value` varchar(250) NOT NULL,
126 `biblionumbers` longtext NOT NULL,
127 KEY `indexname` (`server`,`indexname`),
128 KEY `value` (`server`,`value`))
129 ENGINE=InnoDB DEFAULT CHARSET=utf8;
131 print "Upgrade to $DBversion done (adding tags and nozebra tables )\n";
132 SetVersion ($DBversion);
135 $DBversion = "3.00.00.006";
136 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
137 $dbh->do("UPDATE issues SET issuedate=timestamp WHERE issuedate='0000-00-00'");
138 print "Upgrade to $DBversion done (filled issues.issuedate with timestamp)\n";
139 SetVersion ($DBversion);
142 $DBversion = "3.00.00.007";
143 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
144 $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')");
145 print "Upgrade to $DBversion done (set SessionStorage variable)\n";
146 SetVersion ($DBversion);
149 $DBversion = "3.00.00.008";
150 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
151 $dbh->do("ALTER TABLE `biblio` ADD `datecreated` DATE NOT NULL AFTER `timestamp` ;");
152 $dbh->do("UPDATE biblio SET datecreated=timestamp");
153 print "Upgrade to $DBversion done (biblio creation date)\n";
154 SetVersion ($DBversion);
157 $DBversion = "3.00.00.009";
158 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
160 # Create backups of call number columns
161 # in case default migration needs to be customized
163 # UPGRADE NOTE: temp_upg_biblioitems_call_num should be dropped
164 # after call numbers have been transformed to the new structure
166 # Not bothering to do the same with deletedbiblioitems -- assume
167 # default is good enough.
168 $dbh->do("CREATE TABLE `temp_upg_biblioitems_call_num` AS
169 SELECT `biblioitemnumber`, `biblionumber`,
170 `classification`, `dewey`, `subclass`,
172 FROM `biblioitems`");
174 # biblioitems changes
175 $dbh->do("ALTER TABLE `biblioitems` CHANGE COLUMN `volumeddesc` `volumedesc` TEXT,
176 ADD `cn_source` VARCHAR(10) DEFAULT NULL AFTER `ccode`,
177 ADD `cn_class` VARCHAR(30) DEFAULT NULL AFTER `cn_source`,
178 ADD `cn_item` VARCHAR(10) DEFAULT NULL AFTER `cn_class`,
179 ADD `cn_suffix` VARCHAR(10) DEFAULT NULL AFTER `cn_item`,
180 ADD `cn_sort` VARCHAR(30) DEFAULT NULL AFTER `cn_suffix`,
181 ADD `totalissues` INT(10) AFTER `cn_sort`");
183 # default mapping of call number columns:
184 # cn_class = concatentation of classification + dewey,
185 # trimmed to fit -- assumes that most users do not
186 # populate both classification and dewey in a single record
188 # cn_source = left null
191 # After upgrade, cn_sort will have to be set based on whatever
192 # default call number scheme user sets as a preference. Misc
193 # script will be added at some point to do that.
195 $dbh->do("UPDATE `biblioitems`
196 SET cn_class = SUBSTR(TRIM(CONCAT_WS(' ', `classification`, `dewey`)), 1, 30),
201 # Now drop the old call number columns
202 $dbh->do("ALTER TABLE `biblioitems` DROP COLUMN `classification`,
204 DROP COLUMN `subclass`,
205 DROP COLUMN `lcsort`,
206 DROP COLUMN `ccode`");
208 # deletedbiblio changes
209 $dbh->do("ALTER TABLE `deletedbiblio` ALTER COLUMN `frameworkcode` SET DEFAULT '',
211 ADD `datecreated` DATE NOT NULL AFTER `timestamp`");
212 $dbh->do("UPDATE deletedbiblio SET datecreated = timestamp");
214 # deletedbiblioitems changes
215 $dbh->do("ALTER TABLE `deletedbiblioitems`
216 MODIFY `publicationyear` TEXT,
217 CHANGE `volumeddesc` `volumedesc` TEXT,
218 MODIFY `collectiontitle` MEDIUMTEXT DEFAULT NULL AFTER `volumedesc`,
219 MODIFY `collectionissn` TEXT DEFAULT NULL AFTER `collectiontitle`,
220 MODIFY `collectionvolume` MEDIUMTEXT DEFAULT NULL AFTER `collectionissn`,
221 MODIFY `editionstatement` TEXT DEFAULT NULL AFTER `collectionvolume`,
222 MODIFY `editionresponsibility` TEXT DEFAULT NULL AFTER `editionstatement`,
223 MODIFY `place` VARCHAR(255) DEFAULT NULL AFTER `size`,
224 MODIFY `marc` LONGBLOB,
225 ADD `cn_source` VARCHAR(10) DEFAULT NULL AFTER `url`,
226 ADD `cn_class` VARCHAR(30) DEFAULT NULL AFTER `cn_source`,
227 ADD `cn_item` VARCHAR(10) DEFAULT NULL AFTER `cn_class`,
228 ADD `cn_suffix` VARCHAR(10) DEFAULT NULL AFTER `cn_item`,
229 ADD `cn_sort` VARCHAR(30) DEFAULT NULL AFTER `cn_suffix`,
230 ADD `totalissues` INT(10) AFTER `cn_sort`,
231 ADD `marcxml` LONGTEXT NOT NULL AFTER `totalissues`,
232 ADD KEY `isbn` (`isbn`),
233 ADD KEY `publishercode` (`publishercode`)
236 $dbh->do("UPDATE `deletedbiblioitems`
237 SET `cn_class` = SUBSTR(TRIM(CONCAT_WS(' ', `classification`, `dewey`)), 1, 30),
238 `cn_item` = `subclass`,
241 $dbh->do("ALTER TABLE `deletedbiblioitems`
242 DROP COLUMN `classification`,
244 DROP COLUMN `subclass`,
245 DROP COLUMN `lcsort`,
249 # deleteditems changes
250 $dbh->do("ALTER TABLE `deleteditems`
251 MODIFY `barcode` VARCHAR(20) DEFAULT NULL,
252 MODIFY `price` DECIMAL(8,2) DEFAULT NULL,
253 MODIFY `replacementprice` DECIMAL(8,2) DEFAULT NULL,
255 MODIFY `itemcallnumber` VARCHAR(30) DEFAULT NULL AFTER `wthdrawn`,
256 MODIFY `holdingbranch` VARCHAR(10) DEFAULT NULL,
258 MODIFY `timestamp` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP AFTER `paidfor`,
260 ADD `cn_source` VARCHAR(10) DEFAULT NULL AFTER `onloan`,
261 ADD `cn_sort` VARCHAR(30) DEFAULT NULL AFTER `cn_source`,
262 ADD `ccode` VARCHAR(10) DEFAULT NULL AFTER `cn_sort`,
263 ADD `materials` VARCHAR(10) DEFAULT NULL AFTER `ccode`,
264 ADD `uri` VARCHAR(255) DEFAULT NULL AFTER `materials`,
265 MODIFY `marc` LONGBLOB AFTER `uri`,
267 DROP KEY `itembarcodeidx`,
268 DROP KEY `itembinoidx`,
269 DROP KEY `itembibnoidx`,
270 ADD UNIQUE KEY `delitembarcodeidx` (`barcode`),
271 ADD KEY `delitembinoidx` (`biblioitemnumber`),
272 ADD KEY `delitembibnoidx` (`biblionumber`),
273 ADD KEY `delhomebranch` (`homebranch`),
274 ADD KEY `delholdingbranch` (`holdingbranch`)");
275 $dbh->do("UPDATE deleteditems SET `ccode` = `itype`");
276 $dbh->do("ALTER TABLE deleteditems DROP `itype`");
277 $dbh->do("UPDATE `deleteditems` SET `cn_sort` = `itemcallnumber`");
280 $dbh->do("ALTER TABLE `items` ADD `cn_source` VARCHAR(10) DEFAULT NULL AFTER `onloan`,
281 ADD `cn_sort` VARCHAR(30) DEFAULT NULL AFTER `cn_source`,
282 ADD `ccode` VARCHAR(10) DEFAULT NULL AFTER `cn_sort`,
283 ADD `materials` VARCHAR(10) DEFAULT NULL AFTER `ccode`,
284 ADD `uri` VARCHAR(255) DEFAULT NULL AFTER `materials`
286 $dbh->do("ALTER TABLE `items`
287 DROP KEY `itembarcodeidx`,
288 ADD UNIQUE KEY `itembarcodeidx` (`barcode`)");
290 # map items.itype to items.ccode and
291 # set cn_sort to itemcallnumber -- as with biblioitems.cn_sort,
292 # will have to be subsequently updated per user's default
293 # classification scheme
294 $dbh->do("UPDATE `items` SET `cn_sort` = `itemcallnumber`,
297 $dbh->do("ALTER TABLE `items` DROP `cutterextra`,
300 print "Upgrade to $DBversion done (major changes to biblio, biblioitems, items, and deleted* versions of same\n";
301 SetVersion ($DBversion);
304 $DBversion = "3.00.00.010";
305 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
306 $dbh->do("CREATE INDEX `userid` ON borrowers (`userid`) ");
307 print "Upgrade to $DBversion done (userid index added)\n";
308 SetVersion ($DBversion);
311 $DBversion = "3.00.00.011";
312 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
313 $dbh->do("ALTER TABLE `branchcategories` CHANGE `categorycode` `categorycode` varchar(10) ");
314 $dbh->do("ALTER TABLE `branchcategories` CHANGE `categoryname` `categoryname` varchar(32) ");
315 $dbh->do("ALTER TABLE `branchcategories` ADD COLUMN `categorytype` varchar(16) ");
316 $dbh->do("UPDATE `branchcategories` SET `categorytype` = 'properties'");
317 $dbh->do("ALTER TABLE `branchrelations` CHANGE `categorycode` `categorycode` varchar(10) ");
318 print "Upgrade to $DBversion done (added branchcategory type)\n";
319 SetVersion ($DBversion);
322 $DBversion = "3.00.00.012";
323 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
324 $dbh->do("CREATE TABLE `class_sort_rules` (
325 `class_sort_rule` varchar(10) NOT NULL default '',
326 `description` mediumtext,
327 `sort_routine` varchar(30) NOT NULL default '',
328 PRIMARY KEY (`class_sort_rule`),
329 UNIQUE KEY `class_sort_rule_idx` (`class_sort_rule`)
330 ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
331 $dbh->do("CREATE TABLE `class_sources` (
332 `cn_source` varchar(10) NOT NULL default '',
333 `description` mediumtext,
334 `used` tinyint(4) NOT NULL default 0,
335 `class_sort_rule` varchar(10) NOT NULL default '',
336 PRIMARY KEY (`cn_source`),
337 UNIQUE KEY `cn_source_idx` (`cn_source`),
338 KEY `used_idx` (`used`),
339 CONSTRAINT `class_source_ibfk_1` FOREIGN KEY (`class_sort_rule`)
340 REFERENCES `class_sort_rules` (`class_sort_rule`)
341 ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
342 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type)
343 VALUES('DefaultClassificationSource','ddc',
344 'Default classification scheme used by the collection. E.g., Dewey, LCC, etc.', NULL,'free')");
345 $dbh->do("INSERT INTO `class_sort_rules` (`class_sort_rule`, `description`, `sort_routine`) VALUES
346 ('dewey', 'Default filing rules for DDC', 'Dewey'),
347 ('lcc', 'Default filing rules for LCC', 'LCC'),
348 ('generic', 'Generic call number filing rules', 'Generic')");
349 $dbh->do("INSERT INTO `class_sources` (`cn_source`, `description`, `used`, `class_sort_rule`) VALUES
350 ('ddc', 'Dewey Decimal Classification', 1, 'dewey'),
351 ('lcc', 'Library of Congress Classification', 1, 'lcc'),
352 ('udc', 'Universal Decimal Classification', 0, 'generic'),
353 ('sudocs', 'SuDoc Classification (U.S. GPO)', 0, 'generic'),
354 ('z', 'Other/Generic Classification Scheme', 0, 'generic')");
355 print "Upgrade to $DBversion done (classification sources added)\n";
356 SetVersion ($DBversion);
359 $DBversion = "3.00.00.013";
360 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
361 $dbh->do("CREATE TABLE `import_batches` (
362 `import_batch_id` int(11) NOT NULL auto_increment,
363 `template_id` int(11) default NULL,
364 `branchcode` varchar(10) default NULL,
365 `num_biblios` int(11) NOT NULL default 0,
366 `num_items` int(11) NOT NULL default 0,
367 `upload_timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP,
368 `overlay_action` enum('replace', 'create_new', 'use_template') NOT NULL default 'create_new',
369 `import_status` enum('staging', 'staged', 'importing', 'imported', 'reverting', 'reverted', 'cleaned') NOT NULL default 'staging',
370 `batch_type` enum('batch', 'z3950') NOT NULL default 'batch',
371 `file_name` varchar(100),
372 `comments` mediumtext,
373 PRIMARY KEY (`import_batch_id`),
374 KEY `branchcode` (`branchcode`)
375 ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
376 $dbh->do("CREATE TABLE `import_records` (
377 `import_record_id` int(11) NOT NULL auto_increment,
378 `import_batch_id` int(11) NOT NULL,
379 `branchcode` varchar(10) default NULL,
380 `record_sequence` int(11) NOT NULL default 0,
381 `upload_timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP,
382 `import_date` DATE default NULL,
383 `marc` longblob NOT NULL,
384 `marcxml` longtext NOT NULL,
385 `marcxml_old` longtext NOT NULL,
386 `record_type` enum('biblio', 'auth', 'holdings') NOT NULL default 'biblio',
387 `overlay_status` enum('no_match', 'auto_match', 'manual_match', 'match_applied') NOT NULL default 'no_match',
388 `status` enum('error', 'staged', 'imported', 'reverted', 'items_reverted') NOT NULL default 'staged',
389 `import_error` mediumtext,
390 `encoding` varchar(40) NOT NULL default '',
391 `z3950random` varchar(40) default NULL,
392 PRIMARY KEY (`import_record_id`),
393 CONSTRAINT `import_records_ifbk_1` FOREIGN KEY (`import_batch_id`)
394 REFERENCES `import_batches` (`import_batch_id`) ON DELETE CASCADE ON UPDATE CASCADE,
395 KEY `branchcode` (`branchcode`),
396 KEY `batch_sequence` (`import_batch_id`, `record_sequence`)
397 ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
398 $dbh->do("CREATE TABLE `import_record_matches` (
399 `import_record_id` int(11) NOT NULL,
400 `candidate_match_id` int(11) NOT NULL,
401 `score` int(11) NOT NULL default 0,
402 CONSTRAINT `import_record_matches_ibfk_1` FOREIGN KEY (`import_record_id`)
403 REFERENCES `import_records` (`import_record_id`) ON DELETE CASCADE ON UPDATE CASCADE,
404 KEY `record_score` (`import_record_id`, `score`)
405 ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
406 $dbh->do("CREATE TABLE `import_biblios` (
407 `import_record_id` int(11) NOT NULL,
408 `matched_biblionumber` int(11) default NULL,
409 `control_number` varchar(25) default NULL,
410 `original_source` varchar(25) default NULL,
411 `title` varchar(128) default NULL,
412 `author` varchar(80) default NULL,
413 `isbn` varchar(14) default NULL,
414 `issn` varchar(9) default NULL,
415 `has_items` tinyint(1) NOT NULL default 0,
416 CONSTRAINT `import_biblios_ibfk_1` FOREIGN KEY (`import_record_id`)
417 REFERENCES `import_records` (`import_record_id`) ON DELETE CASCADE ON UPDATE CASCADE,
418 KEY `matched_biblionumber` (`matched_biblionumber`),
419 KEY `title` (`title`),
421 ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
422 $dbh->do("CREATE TABLE `import_items` (
423 `import_items_id` int(11) NOT NULL auto_increment,
424 `import_record_id` int(11) NOT NULL,
425 `itemnumber` int(11) default NULL,
426 `branchcode` varchar(10) default NULL,
427 `status` enum('error', 'staged', 'imported', 'reverted') NOT NULL default 'staged',
428 `marcxml` longtext NOT NULL,
429 `import_error` mediumtext,
430 PRIMARY KEY (`import_items_id`),
431 CONSTRAINT `import_items_ibfk_1` FOREIGN KEY (`import_record_id`)
432 REFERENCES `import_records` (`import_record_id`) ON DELETE CASCADE ON UPDATE CASCADE,
433 KEY `itemnumber` (`itemnumber`),
434 KEY `branchcode` (`branchcode`)
435 ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
437 $dbh->do("INSERT INTO `import_batches`
438 (`overlay_action`, `import_status`, `batch_type`, `file_name`)
439 SELECT distinct 'create_new', 'staged', 'z3950', `file`
440 FROM `marc_breeding`");
442 $dbh->do("INSERT INTO `import_records`
443 (`import_batch_id`, `import_record_id`, `record_sequence`, `marc`, `record_type`, `status`,
444 `encoding`, `z3950random`, `marcxml`, `marcxml_old`)
445 SELECT `import_batch_id`, `id`, 1, `marc`, 'biblio', 'staged', `encoding`, `z3950random`, '', ''
447 JOIN `import_batches` ON (`file_name` = `file`)");
449 $dbh->do("INSERT INTO `import_biblios`
450 (`import_record_id`, `title`, `author`, `isbn`)
451 SELECT `import_record_id`, `title`, `author`, `isbn`
453 JOIN `import_records` ON (`import_record_id` = `id`)");
455 $dbh->do("UPDATE `import_batches`
456 SET `num_biblios` = (
458 FROM `import_records`
459 WHERE `import_batch_id` = `import_batches`.`import_batch_id`
462 $dbh->do("DROP TABLE `marc_breeding`");
464 print "Upgrade to $DBversion done (import_batches et al. added)\n";
465 SetVersion ($DBversion);
468 $DBversion = "3.00.00.014";
469 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
470 $dbh->do("ALTER TABLE subscription ADD lastbranch VARCHAR(4)");
471 print "Upgrade to $DBversion done (userid index added)\n";
472 SetVersion ($DBversion);
475 $DBversion = "3.00.00.015";
476 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
477 $dbh->do("CREATE TABLE `saved_sql` (
478 `id` int(11) NOT NULL auto_increment,
479 `borrowernumber` int(11) default NULL,
480 `date_created` datetime default NULL,
481 `last_modified` datetime default NULL,
483 `last_run` datetime default NULL,
484 `report_name` varchar(255) default NULL,
485 `type` varchar(255) default NULL,
488 KEY boridx (`borrowernumber`)
489 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;");
490 $dbh->do("CREATE TABLE `saved_reports` (
491 `id` int(11) NOT NULL auto_increment,
492 `report_id` int(11) default NULL,
494 `date_run` datetime default NULL,
496 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;");
497 print "Upgrade to $DBversion done (saved_sql and saved_reports added)\n";
498 SetVersion ($DBversion);
501 $DBversion = "3.00.00.016";
502 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
503 $dbh->do(" CREATE TABLE reports_dictionary (
504 id int(11) NOT NULL auto_increment,
505 name varchar(255) default NULL,
507 date_created datetime default NULL,
508 date_modified datetime default NULL,
510 area int(11) default NULL,
512 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ");
513 print "Upgrade to $DBversion done (reports_dictionary) added)\n";
514 SetVersion ($DBversion);
517 $DBversion = "3.00.00.017";
518 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
519 $dbh->do("ALTER TABLE action_logs DROP PRIMARY KEY");
520 $dbh->do("ALTER TABLE action_logs ADD KEY timestamp (timestamp,user)");
521 $dbh->do("ALTER TABLE action_logs ADD action_id INT(11) NOT NULL FIRST");
522 $dbh->do("UPDATE action_logs SET action_id = if (\@a, \@a:=\@a+1, \@a:=1)");
523 $dbh->do("ALTER TABLE action_logs MODIFY action_id INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY");
524 print "Upgrade to $DBversion done (added column to action_logs)\n";
525 SetVersion ($DBversion);
528 $DBversion = "3.00.00.018";
529 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
530 $dbh->do("ALTER TABLE `zebraqueue`
531 ADD `done` INT NOT NULL DEFAULT '0',
532 ADD `time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ;
534 print "Upgrade to $DBversion done (adding timestamp and done columns to zebraque table to improve problem tracking) added)\n";
535 SetVersion ($DBversion);
538 $DBversion = "3.00.00.019";
539 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
540 $dbh->do("ALTER TABLE biblio MODIFY biblionumber INT(11) NOT NULL AUTO_INCREMENT");
541 $dbh->do("ALTER TABLE biblioitems MODIFY biblioitemnumber INT(11) NOT NULL AUTO_INCREMENT");
542 $dbh->do("ALTER TABLE items MODIFY itemnumber INT(11) NOT NULL AUTO_INCREMENT");
543 print "Upgrade to $DBversion done (made bib/item PKs auto_increment)\n";
544 SetVersion ($DBversion);
547 $DBversion = "3.00.00.020";
548 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
549 $dbh->do("ALTER TABLE deleteditems
550 DROP KEY `delitembarcodeidx`,
551 ADD KEY `delitembarcodeidx` (`barcode`)");
552 print "Upgrade to $DBversion done (dropped uniqueness of key on deleteditems.barcode)\n";
553 SetVersion ($DBversion);
556 $DBversion = "3.00.00.021";
557 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
558 $dbh->do("ALTER TABLE items CHANGE homebranch homebranch VARCHAR(10)");
559 $dbh->do("ALTER TABLE deleteditems CHANGE homebranch homebranch VARCHAR(10)");
560 $dbh->do("ALTER TABLE statistics CHANGE branch branch VARCHAR(10)");
561 $dbh->do("ALTER TABLE subscription CHANGE lastbranch lastbranch VARCHAR(10)");
562 print "Upgrade to $DBversion done (extended missed branchcode columns to 10 chars)\n";
563 SetVersion ($DBversion);
566 $DBversion = "3.00.00.022";
567 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
568 $dbh->do("ALTER TABLE items
569 ADD `damaged` tinyint(1) default NULL AFTER notforloan");
570 $dbh->do("ALTER TABLE deleteditems
571 ADD `damaged` tinyint(1) default NULL AFTER notforloan");
572 print "Upgrade to $DBversion done (adding damaged column to items table)\n";
573 SetVersion ($DBversion);
576 $DBversion = "3.00.00.023";
577 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
578 $dbh->do("INSERT INTO `systempreferences` (variable,value,options,explanation,type)
579 VALUES ('yuipath','http://yui.yahooapis.com/2.3.1/build','Insert the path to YUI libraries','','free')");
580 print "Upgrade to $DBversion done (adding new system preference for controlling YUI path)\n";
581 SetVersion ($DBversion);
583 $DBversion = "3.00.00.024";
584 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
585 $dbh->do("ALTER TABLE biblioitems CHANGE itemtype itemtype VARCHAR(10)");
586 print "Upgrade to $DBversion done (changing itemtype to (10))\n";
587 SetVersion ($DBversion);
590 $DBversion = "3.00.00.025";
591 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
592 $dbh->do("ALTER TABLE items ADD COLUMN itype VARCHAR(10)");
593 $dbh->do("ALTER TABLE deleteditems ADD COLUMN itype VARCHAR(10) AFTER uri");
594 if(C4::Context->preference('item-level_itypes')){
595 $dbh->do('update items,biblioitems set items.itype=biblioitems.itemtype where items.biblionumber=biblioitems.biblionumber and itype is null');
597 print "Upgrade to $DBversion done (reintroduce items.itype - fill from itemtype)\n ";
598 SetVersion ($DBversion);
601 $DBversion = "3.00.00.026";
602 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
603 $dbh->do("INSERT INTO `systempreferences` (variable,value,options,explanation,type)
604 VALUES ('HomeOrHoldingBranch','homebranch','homebranch|holdingbranch','With independent branches turned on this decides whether to check the items holdingbranch or homebranch at circulatilon','choice')");
605 print "Upgrade to $DBversion done (adding new system preference for choosing whether homebranch or holdingbranch is checked in circulation)\n";
606 SetVersion ($DBversion);
609 $DBversion = "3.00.00.027";
610 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
611 $dbh->do("CREATE TABLE `marc_matchers` (
612 `matcher_id` int(11) NOT NULL auto_increment,
613 `code` varchar(10) NOT NULL default '',
614 `description` varchar(255) NOT NULL default '',
615 `record_type` varchar(10) NOT NULL default 'biblio',
616 `threshold` int(11) NOT NULL default 0,
617 PRIMARY KEY (`matcher_id`),
619 KEY `record_type` (`record_type`)
620 ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
621 $dbh->do("CREATE TABLE `matchpoints` (
622 `matcher_id` int(11) NOT NULL,
623 `matchpoint_id` int(11) NOT NULL auto_increment,
624 `search_index` varchar(30) NOT NULL default '',
625 `score` int(11) NOT NULL default 0,
626 PRIMARY KEY (`matchpoint_id`),
627 CONSTRAINT `matchpoints_ifbk_1` FOREIGN KEY (`matcher_id`)
628 REFERENCES `marc_matchers` (`matcher_id`) ON DELETE CASCADE ON UPDATE CASCADE
629 ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
630 $dbh->do("CREATE TABLE `matchpoint_components` (
631 `matchpoint_id` int(11) NOT NULL,
632 `matchpoint_component_id` int(11) NOT NULL auto_increment,
633 sequence int(11) NOT NULL default 0,
634 tag varchar(3) NOT NULL default '',
635 subfields varchar(40) NOT NULL default '',
636 offset int(4) NOT NULL default 0,
637 length int(4) NOT NULL default 0,
638 PRIMARY KEY (`matchpoint_component_id`),
639 KEY `by_sequence` (`matchpoint_id`, `sequence`),
640 CONSTRAINT `matchpoint_components_ifbk_1` FOREIGN KEY (`matchpoint_id`)
641 REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE
642 ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
643 $dbh->do("CREATE TABLE `matchpoint_component_norms` (
644 `matchpoint_component_id` int(11) NOT NULL,
645 `sequence` int(11) NOT NULL default 0,
646 `norm_routine` varchar(50) NOT NULL default '',
647 KEY `matchpoint_component_norms` (`matchpoint_component_id`, `sequence`),
648 CONSTRAINT `matchpoint_component_norms_ifbk_1` FOREIGN KEY (`matchpoint_component_id`)
649 REFERENCES `matchpoint_components` (`matchpoint_component_id`) ON DELETE CASCADE ON UPDATE CASCADE
650 ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
651 $dbh->do("CREATE TABLE `matcher_matchpoints` (
652 `matcher_id` int(11) NOT NULL,
653 `matchpoint_id` int(11) NOT NULL,
654 CONSTRAINT `matcher_matchpoints_ifbk_1` FOREIGN KEY (`matcher_id`)
655 REFERENCES `marc_matchers` (`matcher_id`) ON DELETE CASCADE ON UPDATE CASCADE,
656 CONSTRAINT `matcher_matchpoints_ifbk_2` FOREIGN KEY (`matchpoint_id`)
657 REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE
658 ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
659 $dbh->do("CREATE TABLE `matchchecks` (
660 `matcher_id` int(11) NOT NULL,
661 `matchcheck_id` int(11) NOT NULL auto_increment,
662 `source_matchpoint_id` int(11) NOT NULL,
663 `target_matchpoint_id` int(11) NOT NULL,
664 PRIMARY KEY (`matchcheck_id`),
665 CONSTRAINT `matcher_matchchecks_ifbk_1` FOREIGN KEY (`matcher_id`)
666 REFERENCES `marc_matchers` (`matcher_id`) ON DELETE CASCADE ON UPDATE CASCADE,
667 CONSTRAINT `matcher_matchchecks_ifbk_2` FOREIGN KEY (`source_matchpoint_id`)
668 REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE,
669 CONSTRAINT `matcher_matchchecks_ifbk_3` FOREIGN KEY (`target_matchpoint_id`)
670 REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE
671 ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
672 print "Upgrade to $DBversion done (added C4::Matcher serialization tables)\n ";
673 SetVersion ($DBversion);
676 $DBversion = "3.00.00.028";
677 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
678 $dbh->do("INSERT INTO `systempreferences` (variable,value,options,explanation,type)
679 VALUES ('canreservefromotherbranches','1','','With Independent branches on, can a user from one library reserve an item from another library','YesNo')");
680 print "Upgrade to $DBversion done (adding new system preference for changing reserve/holds behaviour with independent branches)\n";
681 SetVersion ($DBversion);
685 $DBversion = "3.00.00.029";
686 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
687 $dbh->do("ALTER TABLE `import_batches` ADD `matcher_id` int(11) NULL AFTER `import_batch_id`");
688 print "Upgrade to $DBversion done (adding matcher_id to import_batches)\n";
689 SetVersion ($DBversion);
692 $DBversion = "3.00.00.030";
693 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
695 CREATE TABLE services_throttle (
696 service_type varchar(10) NOT NULL default '',
697 service_count varchar(45) default NULL,
698 PRIMARY KEY (service_type)
699 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
701 $dbh->do("INSERT INTO `systempreferences` (variable,value,options,explanation,type)
702 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')");
703 $dbh->do("INSERT INTO `systempreferences` (variable,value,options,explanation,type)
704 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')");
705 $dbh->do("INSERT INTO `systempreferences` (variable,value,options,explanation,type)
706 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')");
707 $dbh->do("INSERT INTO `systempreferences` (variable,value,options,explanation,type)
708 VALUES ('XISBNDailyLimit',499,'','The xISBN Web service is free for non-commercial use when usage does not exceed 500 requests per day','free')");
709 $dbh->do("INSERT INTO `systempreferences` (variable,value,options,explanation,type)
710 VALUES ('PINESISBN',0,'','Use with FRBRizeEditions. If ON, Koha will use PINES OISBN web service in the Editions tab on the detail pages.','YesNo')");
711 $dbh->do("INSERT INTO `systempreferences` (variable,value,options,explanation,type)
712 VALUES ('ThingISBN',0,'','Use with FRBRizeEditions. If ON, Koha will use the ThingISBN web service in the Editions tab on the detail pages.','YesNo')");
713 print "Upgrade to $DBversion done (adding services throttle table and sysprefs for xISBN)\n";
714 SetVersion ($DBversion);
717 $DBversion = "3.00.00.031";
718 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
720 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('QueryStemming',1,'If ON, enables query stemming',NULL,'YesNo')");
721 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('QueryFuzzy',1,'If ON, enables fuzzy option for searches',NULL,'YesNo')");
722 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('QueryWeightFields',1,'If ON, enables field weighting',NULL,'YesNo')");
723 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('WebBasedSelfCheck',0,'If ON, enables the web-based self-check system',NULL,'YesNo')");
724 $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')");
725 $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')");
726 $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')");
727 $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')");
728 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('defaultSortOrder',NULL,'Specify the default sort order','asc|dsc|az|za','Choice')");
729 $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')");
730 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('OPACdefaultSortOrder',NULL,'Specify the default sort order','asc|dsc|za|az','Choice')");
731 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('staffClientBaseURL','','Specify the base URL of the staff client',NULL,'free')");
732 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('minPasswordLength',3,'Specify the minimum length of a patron/staff password',NULL,'free')");
733 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('noItemTypeImages',0,'If ON, disables item-type images',NULL,'YesNo')");
734 $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')");
735 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('holdCancelLength','','Specify how many days before a hold is canceled',NULL,'free')");
736 $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')");
737 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('finesMode','test','Choose the fines mode, test or production','test|production','Choice')");
738 $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')");
739 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('itemBarcodeInputFilter','','If set, allows specification of a item barcode input filter','cuecat','Choice')");
740 $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')");
741 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('URLLinkText','','Text to display as the link anchor in the OPAC',NULL,'free')");
742 $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')");
743 $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')");
744 $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')");
745 $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')");
746 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('OPACUserCSS',0,'Add CSS to be included in the OPAC',NULL,'free')");
748 print "Upgrade to $DBversion done (adding additional system preference)\n";
749 SetVersion ($DBversion);
752 $DBversion = "3.00.00.032";
753 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
754 $dbh->do("UPDATE `marc_subfield_structure` SET `kohafield` = 'items.wthdrawn' WHERE `kohafield` = 'items.withdrawn'");
755 print "Upgrade to $DBversion done (fixed MARC framework references to items.withdrawn)\n";
756 SetVersion ($DBversion);
759 $DBversion = "3.00.00.033";
760 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
761 $dbh->do("INSERT INTO `userflags` VALUES(17,'staffaccess','Modify login / permissions for staff users',0)");
762 print "Upgrade to $DBversion done (Adding permissions flag for staff member access modification. )\n";
763 SetVersion ($DBversion);
766 $DBversion = "3.00.00.034";
767 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
768 $dbh->do("ALTER TABLE `virtualshelves` ADD COLUMN `sortfield` VARCHAR(16) ");
769 print "Upgrade to $DBversion done (Adding sortfield for Virtual Shelves. )\n";
770 SetVersion ($DBversion);
773 $DBversion = "3.00.00.035";
774 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
775 $dbh->do("UPDATE marc_subfield_structure
776 SET authorised_value = 'cn_source'
777 WHERE kohafield IN ('items.cn_source', 'biblioitems.cn_source')
778 AND (authorised_value is NULL OR authorised_value = '')");
779 print "Upgrade to $DBversion done (MARC frameworks: make classification source a drop-down)\n";
780 SetVersion ($DBversion);
783 $DBversion = "3.00.00.036";
784 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
785 $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');");
786 print "Upgrade to $DBversion done (OPACItemsResultsDisplay systempreference added)\n";
787 SetVersion ($DBversion);
790 $DBversion = "3.00.00.037";
791 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
792 $dbh->do("ALTER TABLE `borrowers` ADD COLUMN `altcontactfirstname` varchar(255)");
793 $dbh->do("ALTER TABLE `borrowers` ADD COLUMN `altcontactsurname` varchar(255)");
794 $dbh->do("ALTER TABLE `borrowers` ADD COLUMN `altcontactaddress1` varchar(255)");
795 $dbh->do("ALTER TABLE `borrowers` ADD COLUMN `altcontactaddress2` varchar(255)");
796 $dbh->do("ALTER TABLE `borrowers` ADD COLUMN `altcontactaddress3` varchar(255)");
797 $dbh->do("ALTER TABLE `borrowers` ADD COLUMN `altcontactzipcode` varchar(50)");
798 $dbh->do("ALTER TABLE `borrowers` ADD COLUMN `altcontactphone` varchar(50)");
799 print "Upgrade to $DBversion done (Adding Alternative Contact Person information to borrowers table)\n";
800 SetVersion ($DBversion);
803 $DBversion = "3.00.00.038";
804 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
805 $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'");
806 $dbh->do("DELETE FROM `systempreferences` WHERE variable='hideBiblioNumber'");
807 print "Upgrade to $DBversion done ('alter finesMode systempreference, remove superfluous syspref.')\n";
808 SetVersion ($DBversion);
811 $DBversion = "3.00.00.039";
812 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
813 $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')");
814 $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')");
815 $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')");
816 # $dbh->do("DELETE FROM `systempreferences` WHERE variable='HomeOrHoldingBranch'"); # Bug #2752
817 print "Upgrade to $DBversion done ('add circ sysprefs CircControl, finesCalendar, and uppercasesurnames, and delete HomeOrHoldingBranch.')\n";
818 SetVersion ($DBversion);
821 $DBversion = "3.00.00.040";
822 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
823 $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')");
824 $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')");
825 print "Upgrade to $DBversion done ('add circ sysprefs todaysIssuesDefaultSortOrder and previousIssuesDefaultSortOrder.')\n";
826 SetVersion ($DBversion);
830 $DBversion = "3.00.00.041";
831 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
832 # Strictly speaking it is not necessary to explicitly change
833 # NULL values to 0, because the ALTER TABLE statement will do that.
834 # However, setting them first avoids a warning.
835 $dbh->do("UPDATE items SET notforloan = 0 WHERE notforloan IS NULL");
836 $dbh->do("UPDATE items SET damaged = 0 WHERE damaged IS NULL");
837 $dbh->do("UPDATE items SET itemlost = 0 WHERE itemlost IS NULL");
838 $dbh->do("UPDATE items SET wthdrawn = 0 WHERE wthdrawn IS NULL");
839 $dbh->do("ALTER TABLE items
840 MODIFY notforloan tinyint(1) NOT NULL default 0,
841 MODIFY damaged tinyint(1) NOT NULL default 0,
842 MODIFY itemlost tinyint(1) NOT NULL default 0,
843 MODIFY wthdrawn tinyint(1) NOT NULL default 0");
844 $dbh->do("UPDATE deleteditems SET notforloan = 0 WHERE notforloan IS NULL");
845 $dbh->do("UPDATE deleteditems SET damaged = 0 WHERE damaged IS NULL");
846 $dbh->do("UPDATE deleteditems SET itemlost = 0 WHERE itemlost IS NULL");
847 $dbh->do("UPDATE deleteditems SET wthdrawn = 0 WHERE wthdrawn IS NULL");
848 $dbh->do("ALTER TABLE deleteditems
849 MODIFY notforloan tinyint(1) NOT NULL default 0,
850 MODIFY damaged tinyint(1) NOT NULL default 0,
851 MODIFY itemlost tinyint(1) NOT NULL default 0,
852 MODIFY wthdrawn tinyint(1) NOT NULL default 0");
853 print "Upgrade to $DBversion done (disallow NULL in several item status columns)\n";
854 SetVersion ($DBversion);
857 $DBversion = "3.00.00.042";
858 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
859 $dbh->do("ALTER TABLE aqbooksellers CHANGE name name mediumtext NOT NULL");
860 print "Upgrade to $DBversion done (disallow NULL in aqbooksellers.name; part of fix for bug 1251)\n";
861 SetVersion ($DBversion);
864 $DBversion = "3.00.00.043";
865 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
866 $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");
867 print "Upgrade to $DBversion done (currency table: add symbol and timestamp columns)\n";
868 SetVersion ($DBversion);
871 $DBversion = "3.00.00.044";
872 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
873 $dbh->do("ALTER TABLE deletedborrowers
874 ADD `altcontactfirstname` varchar(255) default NULL,
875 ADD `altcontactsurname` varchar(255) default NULL,
876 ADD `altcontactaddress1` varchar(255) default NULL,
877 ADD `altcontactaddress2` varchar(255) default NULL,
878 ADD `altcontactaddress3` varchar(255) default NULL,
879 ADD `altcontactzipcode` varchar(50) default NULL,
880 ADD `altcontactphone` varchar(50) default NULL
882 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES
883 ('OPACBaseURL',NULL,'Specify the Base URL of the OPAC, e.g., opac.mylibrary.com, the http:// will be added automatically by Koha.',NULL,'Free'),
884 ('language','en','Set the default language in the staff client.',NULL,'Languages'),
885 ('QueryAutoTruncate',1,'If ON, query truncation is enabled by default',NULL,'YesNo'),
886 ('QueryRemoveStopwords',0,'If ON, stopwords listed in the Administration area will be removed from queries',NULL,'YesNo')
888 print "Upgrade to $DBversion done (syncing deletedborrowers table with borrowers table)\n";
889 SetVersion ($DBversion);
892 #-- http://www.w3.org/International/articles/language-tags/
895 $DBversion = "3.00.00.045";
896 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
898 CREATE TABLE language_subtag_registry (
900 type varchar(25), -- language-script-region-variant-extension-privateuse
901 description varchar(25), -- only one of the possible descriptions for ease of reference, see language_descriptions for the complete list
903 KEY `subtag` (`subtag`)
904 ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
906 #-- TODO: add suppress_scripts
907 #-- this maps three letter codes defined in iso639.2 back to their
908 #-- two letter equivilents in rfc4646 (LOC maintains iso639+)
909 $dbh->do("CREATE TABLE language_rfc4646_to_iso639 (
910 rfc4646_subtag varchar(25),
911 iso639_2_code varchar(25),
912 KEY `rfc4646_subtag` (`rfc4646_subtag`)
913 ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
915 $dbh->do("CREATE TABLE language_descriptions (
919 description varchar(255),
921 ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
923 #-- bi-directional support, keyed by script subcode
924 $dbh->do("CREATE TABLE language_script_bidi (
925 rfc4646_subtag varchar(25), -- script subtag, Arab, Hebr, etc.
926 bidi varchar(3), -- rtl ltr
927 KEY `rfc4646_subtag` (`rfc4646_subtag`)
928 ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
930 #-- BIDI Stuff, Arabic and Hebrew
931 $dbh->do("INSERT INTO language_script_bidi(rfc4646_subtag,bidi)
932 VALUES( 'Arab', 'rtl')");
933 $dbh->do("INSERT INTO language_script_bidi(rfc4646_subtag,bidi)
934 VALUES( 'Hebr', 'rtl')");
936 #-- TODO: need to map language subtags to script subtags for detection
937 #-- of bidi when script is not specified (like ar, he)
938 $dbh->do("CREATE TABLE language_script_mapping (
939 language_subtag varchar(25),
940 script_subtag varchar(25),
941 KEY `language_subtag` (`language_subtag`)
942 ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
944 #-- Default mappings between script and language subcodes
945 $dbh->do("INSERT INTO language_script_mapping(language_subtag,script_subtag)
946 VALUES( 'ar', 'Arab')");
947 $dbh->do("INSERT INTO language_script_mapping(language_subtag,script_subtag)
948 VALUES( 'he', 'Hebr')");
950 print "Upgrade to $DBversion done (adding language subtag registry and basic BiDi support NOTE: You should import the subtag registry SQL)\n";
951 SetVersion ($DBversion);
954 $DBversion = "3.00.00.046";
955 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
956 $dbh->do("ALTER TABLE `subscription` CHANGE `numberlength` `numberlength` int(11) default '0' ,
957 CHANGE `weeklength` `weeklength` int(11) default '0'");
958 $dbh->do("CREATE TABLE `serialitems` (`serialid` int(11) NOT NULL, `itemnumber` int(11) NOT NULL, UNIQUE KEY `serialididx` (`serialid`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
959 $dbh->do("INSERT INTO `serialitems` SELECT `serialid`,`itemnumber` from serial where NOT ISNULL(itemnumber) && itemnumber <> '' && itemnumber NOT LIKE '%,%'");
960 print "Upgrade to $DBversion done (Add serialitems table to link serial issues to items. )\n";
961 SetVersion ($DBversion);
964 $DBversion = "3.00.00.047";
965 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
966 $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');");
967 print "Upgrade to $DBversion done ( Added OpacRenewalAllowed syspref )\n";
968 SetVersion ($DBversion);
971 $DBversion = "3.00.00.048";
972 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
973 $dbh->do("ALTER TABLE `items` ADD `more_subfields_xml` longtext default NULL AFTER `itype`");
974 print "Upgrade to $DBversion done (added items.more_subfields_xml)\n";
975 SetVersion ($DBversion);
978 $DBversion = "3.00.00.049";
979 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
980 $dbh->do("ALTER TABLE `z3950servers` ADD `encoding` text default NULL AFTER type ");
981 print "Upgrade to $DBversion done ( Added encoding field to z3950servers table )\n";
982 SetVersion ($DBversion);
985 $DBversion = "3.00.00.050";
986 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
987 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('OpacHighlightedWords','0','If Set, query matched terms are highlighted in OPAC',NULL,'YesNo');");
988 print "Upgrade to $DBversion done ( Added OpacHighlightedWords syspref )\n";
989 SetVersion ($DBversion);
992 $DBversion = "3.00.00.051";
993 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
994 $dbh->do("UPDATE systempreferences SET explanation = 'Define the current theme for the OPAC interface.' WHERE variable = 'opacthemes';");
995 print "Upgrade to $DBversion done ( Corrected opacthemes explanation. )\n";
996 SetVersion ($DBversion);
999 $DBversion = "3.00.00.052";
1000 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1001 $dbh->do("ALTER TABLE `deleteditems` ADD `more_subfields_xml` LONGTEXT DEFAULT NULL AFTER `itype`");
1002 print "Upgrade to $DBversion done ( Adding missing column to deleteditems table. )\n";
1003 SetVersion ($DBversion);
1006 $DBversion = "3.00.00.053";
1007 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1008 $dbh->do("CREATE TABLE `printers_profile` (
1009 `prof_id` int(4) NOT NULL auto_increment,
1010 `printername` varchar(40) NOT NULL,
1011 `tmpl_id` int(4) NOT NULL,
1012 `paper_bin` varchar(20) NOT NULL,
1013 `offset_horz` float default NULL,
1014 `offset_vert` float default NULL,
1015 `creep_horz` float default NULL,
1016 `creep_vert` float default NULL,
1017 `unit` char(20) NOT NULL default 'POINT',
1018 PRIMARY KEY (`prof_id`),
1019 UNIQUE KEY `printername` (`printername`,`tmpl_id`,`paper_bin`),
1020 CONSTRAINT `printers_profile_pnfk_1` FOREIGN KEY (`tmpl_id`) REFERENCES `labels_templates` (`tmpl_id`) ON DELETE CASCADE ON UPDATE CASCADE
1021 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ");
1022 $dbh->do("CREATE TABLE `labels_profile` (
1023 `tmpl_id` int(4) NOT NULL,
1024 `prof_id` int(4) NOT NULL,
1025 UNIQUE KEY `tmpl_id` (`tmpl_id`),
1026 UNIQUE KEY `prof_id` (`prof_id`)
1027 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ");
1028 print "Upgrade to $DBversion done ( Printer Profile tables added )\n";
1029 SetVersion ($DBversion);
1032 $DBversion = "3.00.00.054";
1033 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1034 $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';");
1035 print "Upgrade to $DBversion done ( Added another barcode autogeneration sequence to barcode.pl. )\n";
1036 SetVersion ($DBversion);
1039 $DBversion = "3.00.00.055";
1040 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1041 $dbh->do("ALTER TABLE `zebraqueue` ADD KEY `zebraqueue_lookup` (`server`, `biblio_auth_number`, `operation`, `done`)");
1042 print "Upgrade to $DBversion done ( Added index on zebraqueue. )\n";
1043 SetVersion ($DBversion);
1045 $DBversion = "3.00.00.056";
1046 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1047 if (C4::Context->preference("marcflavour") eq 'UNIMARC') {
1048 $dbh->do("INSERT INTO `marc_subfield_structure` (`tagfield`, `tagsubfield`, `liblibrarian`, `libopac`, `repeatable`, `mandatory`, `kohafield`, `tab`, `authorised_value` , `authtypecode`, `value_builder`, `isurl`, `hidden`, `frameworkcode`, `seealso`, `link`, `defaultvalue`) VALUES ('995', 'v', 'Note sur le N° de périodique','Note sur le N° de périodique', 0, 0, 'items.enumchron', 10, '', '', '', 0, 0, '', '', '', NULL) ");
1050 $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) ");
1052 $dbh->do("ALTER TABLE `items` ADD `enumchron` VARCHAR(80) DEFAULT NULL;");
1053 print "Upgrade to $DBversion done ( Added item.enumchron column, and framework map to 952h )\n";
1054 SetVersion ($DBversion);
1057 $DBversion = "3.00.00.057";
1058 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1059 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('OAI-PMH','0','if ON, OAI-PMH server is enabled',NULL,'YesNo');");
1060 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('OAI-PMH:archiveID','KOHA-OAI-TEST','OAI-PMH archive identification',NULL,'Free');");
1061 $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');");
1062 $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');");
1063 $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');");
1064 SetVersion ($DBversion);
1067 $DBversion = "3.00.00.058";
1068 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1069 $dbh->do("ALTER TABLE `opac_news`
1070 CHANGE `lang` `lang` VARCHAR( 25 )
1072 COLLATE utf8_general_ci
1073 NOT NULL default ''");
1074 print "Upgrade to $DBversion done ( lang field in opac_news made longer )\n";
1075 SetVersion ($DBversion);
1078 $DBversion = "3.00.00.059";
1079 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1081 $dbh->do("CREATE TABLE IF NOT EXISTS `labels_templates` (
1082 `tmpl_id` int(4) NOT NULL auto_increment,
1083 `tmpl_code` char(100) default '',
1084 `tmpl_desc` char(100) default '',
1085 `page_width` float default '0',
1086 `page_height` float default '0',
1087 `label_width` float default '0',
1088 `label_height` float default '0',
1089 `topmargin` float default '0',
1090 `leftmargin` float default '0',
1091 `cols` int(2) default '0',
1092 `rows` int(2) default '0',
1093 `colgap` float default '0',
1094 `rowgap` float default '0',
1095 `active` int(1) default NULL,
1096 `units` char(20) default 'PX',
1097 `fontsize` int(4) NOT NULL default '3',
1098 PRIMARY KEY (`tmpl_id`)
1099 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;");
1100 $dbh->do("CREATE TABLE IF NOT EXISTS `printers_profile` (
1101 `prof_id` int(4) NOT NULL auto_increment,
1102 `printername` varchar(40) NOT NULL,
1103 `tmpl_id` int(4) NOT NULL,
1104 `paper_bin` varchar(20) NOT NULL,
1105 `offset_horz` float default NULL,
1106 `offset_vert` float default NULL,
1107 `creep_horz` float default NULL,
1108 `creep_vert` float default NULL,
1109 `unit` char(20) NOT NULL default 'POINT',
1110 PRIMARY KEY (`prof_id`),
1111 UNIQUE KEY `printername` (`printername`,`tmpl_id`,`paper_bin`),
1112 CONSTRAINT `printers_profile_pnfk_1` FOREIGN KEY (`tmpl_id`) REFERENCES `labels_templates` (`tmpl_id`) ON DELETE CASCADE ON UPDATE CASCADE
1113 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ");
1114 print "Upgrade to $DBversion done ( Added labels_templates table if it did not exist. )\n";
1115 SetVersion ($DBversion);
1118 $DBversion = "3.00.00.060";
1119 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1120 $dbh->do("CREATE TABLE IF NOT EXISTS `patronimage` (
1121 `cardnumber` varchar(16) NOT NULL,
1122 `mimetype` varchar(15) NOT NULL,
1123 `imagefile` mediumblob NOT NULL,
1124 PRIMARY KEY (`cardnumber`),
1125 CONSTRAINT `patronimage_fk1` FOREIGN KEY (`cardnumber`) REFERENCES `borrowers` (`cardnumber`) ON DELETE CASCADE ON UPDATE CASCADE
1126 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;");
1127 print "Upgrade to $DBversion done ( Added patronimage table. )\n";
1128 SetVersion ($DBversion);
1131 $DBversion = "3.00.00.061";
1132 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1133 $dbh->do("ALTER TABLE labels_templates ADD COLUMN font char(10) NOT NULL DEFAULT 'TR';");
1134 print "Upgrade to $DBversion done ( Added font column to labels_templates )\n";
1135 SetVersion ($DBversion);
1138 $DBversion = "3.00.00.062";
1139 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1140 $dbh->do("CREATE TABLE `old_issues` (
1141 `borrowernumber` int(11) default NULL,
1142 `itemnumber` int(11) default NULL,
1143 `date_due` date default NULL,
1144 `branchcode` varchar(10) default NULL,
1145 `issuingbranch` varchar(18) default NULL,
1146 `returndate` date default NULL,
1147 `lastreneweddate` date default NULL,
1148 `return` varchar(4) default NULL,
1149 `renewals` tinyint(4) default NULL,
1150 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1151 `issuedate` date default NULL,
1152 KEY `old_issuesborridx` (`borrowernumber`),
1153 KEY `old_issuesitemidx` (`itemnumber`),
1154 KEY `old_bordate` (`borrowernumber`,`timestamp`),
1155 CONSTRAINT `old_issues_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`)
1156 ON DELETE SET NULL ON UPDATE SET NULL,
1157 CONSTRAINT `old_issues_ibfk_2` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`)
1158 ON DELETE SET NULL ON UPDATE SET NULL
1159 ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
1160 $dbh->do("CREATE TABLE `old_reserves` (
1161 `borrowernumber` int(11) default NULL,
1162 `reservedate` date default NULL,
1163 `biblionumber` int(11) default NULL,
1164 `constrainttype` varchar(1) default NULL,
1165 `branchcode` varchar(10) default NULL,
1166 `notificationdate` date default NULL,
1167 `reminderdate` date default NULL,
1168 `cancellationdate` date default NULL,
1169 `reservenotes` mediumtext,
1170 `priority` smallint(6) default NULL,
1171 `found` varchar(1) default NULL,
1172 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1173 `itemnumber` int(11) default NULL,
1174 `waitingdate` date default NULL,
1175 KEY `old_reserves_borrowernumber` (`borrowernumber`),
1176 KEY `old_reserves_biblionumber` (`biblionumber`),
1177 KEY `old_reserves_itemnumber` (`itemnumber`),
1178 KEY `old_reserves_branchcode` (`branchcode`),
1179 CONSTRAINT `old_reserves_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`)
1180 ON DELETE SET NULL ON UPDATE SET NULL,
1181 CONSTRAINT `old_reserves_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`)
1182 ON DELETE SET NULL ON UPDATE SET NULL,
1183 CONSTRAINT `old_reserves_ibfk_3` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`)
1184 ON DELETE SET NULL ON UPDATE SET NULL
1185 ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
1187 # move closed transactions to old_* tables
1188 $dbh->do("INSERT INTO old_issues SELECT * FROM issues WHERE returndate IS NOT NULL");
1189 $dbh->do("DELETE FROM issues WHERE returndate IS NOT NULL");
1190 $dbh->do("INSERT INTO old_reserves SELECT * FROM reserves WHERE cancellationdate IS NOT NULL OR found = 'F'");
1191 $dbh->do("DELETE FROM reserves WHERE cancellationdate IS NOT NULL OR found = 'F'");
1193 print "Upgrade to $DBversion done ( Added old_issues and old_reserves tables )\n";
1194 SetVersion ($DBversion);
1197 $DBversion = "3.00.00.063";
1198 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1199 $dbh->do("ALTER TABLE deleteditems
1200 CHANGE COLUMN booksellerid booksellerid MEDIUMTEXT DEFAULT NULL,
1201 ADD COLUMN enumchron VARCHAR(80) DEFAULT NULL AFTER more_subfields_xml,
1202 ADD COLUMN copynumber SMALLINT(6) DEFAULT NULL AFTER enumchron;");
1203 $dbh->do("ALTER TABLE items
1204 CHANGE COLUMN booksellerid booksellerid MEDIUMTEXT,
1205 ADD COLUMN copynumber SMALLINT(6) DEFAULT NULL AFTER enumchron;");
1206 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";
1207 SetVersion ($DBversion);
1210 $DBversion = "3.00.00.064";
1211 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1212 $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');");
1213 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('AWSAccessKeyID','','See: http://aws.amazon.com','','free');");
1214 $dbh->do("DELETE FROM `systempreferences` WHERE variable='AmazonDevKey';");
1215 $dbh->do("DELETE FROM `systempreferences` WHERE variable='XISBNAmazonSimilarItems';");
1216 $dbh->do("DELETE FROM `systempreferences` WHERE variable='OPACXISBNAmazonSimilarItems';");
1217 print "Upgrade to $DBversion done (IMPORTANT: Upgrading to Amazon.com Associates Web Service 4.0 ) \n";
1218 SetVersion ($DBversion);
1221 $DBversion = "3.00.00.065";
1222 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1223 $dbh->do("CREATE TABLE `patroncards` (
1224 `cardid` int(11) NOT NULL auto_increment,
1225 `batch_id` varchar(10) NOT NULL default '1',
1226 `borrowernumber` int(11) NOT NULL,
1227 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1228 PRIMARY KEY (`cardid`),
1229 KEY `patroncards_ibfk_1` (`borrowernumber`),
1230 CONSTRAINT `patroncards_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE
1231 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;");
1232 print "Upgrade to $DBversion done (Adding patroncards table for patroncards generation feature. ) \n";
1233 SetVersion ($DBversion);
1236 $DBversion = "3.00.00.066";
1237 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1238 $dbh->do("ALTER TABLE `virtualshelfcontents` MODIFY `dateadded` timestamp NOT NULL
1239 DEFAULT CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP;
1241 print "Upgrade to $DBversion done (fix for bug 1873: virtualshelfcontents dateadded column empty. ) \n";
1242 SetVersion ($DBversion);
1245 $DBversion = "3.00.00.067";
1246 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1247 $dbh->do("UPDATE systempreferences SET explanation = 'Enable patron images for the Staff Client', type = 'YesNo' WHERE variable = 'patronimages'");
1248 print "Upgrade to $DBversion done (Updating patronimages syspref to reflect current kohastructure.sql. ) \n";
1249 SetVersion ($DBversion);
1252 $DBversion = "3.00.00.068";
1253 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1254 $dbh->do("CREATE TABLE `permissions` (
1255 `module_bit` int(11) NOT NULL DEFAULT 0,
1256 `code` varchar(30) DEFAULT NULL,
1257 `description` varchar(255) DEFAULT NULL,
1258 PRIMARY KEY (`module_bit`, `code`),
1259 CONSTRAINT `permissions_ibfk_1` FOREIGN KEY (`module_bit`) REFERENCES `userflags` (`bit`)
1260 ON DELETE CASCADE ON UPDATE CASCADE
1261 ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
1262 $dbh->do("CREATE TABLE `user_permissions` (
1263 `borrowernumber` int(11) NOT NULL DEFAULT 0,
1264 `module_bit` int(11) NOT NULL DEFAULT 0,
1265 `code` varchar(30) DEFAULT NULL,
1266 CONSTRAINT `user_permissions_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`)
1267 ON DELETE CASCADE ON UPDATE CASCADE,
1268 CONSTRAINT `user_permissions_ibfk_2` FOREIGN KEY (`module_bit`, `code`)
1269 REFERENCES `permissions` (`module_bit`, `code`)
1270 ON DELETE CASCADE ON UPDATE CASCADE
1271 ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
1273 $dbh->do("INSERT INTO permissions (module_bit, code, description) VALUES
1274 (13, 'edit_news', 'Write news for the OPAC and staff interfaces'),
1275 (13, 'label_creator', 'Create printable labels and barcodes from catalog and patron data'),
1276 (13, 'edit_calendar', 'Define days when the library is closed'),
1277 (13, 'moderate_comments', 'Moderate patron comments'),
1278 (13, 'edit_notices', 'Define notices'),
1279 (13, 'edit_notice_status_triggers', 'Set notice/status triggers for overdue items'),
1280 (13, 'view_system_logs', 'Browse the system logs'),
1281 (13, 'inventory', 'Perform inventory (stocktaking) of your catalogue'),
1282 (13, 'stage_marc_import', 'Stage MARC records into the reservoir'),
1283 (13, 'manage_staged_marc', 'Managed staged MARC records, including completing and reversing imports'),
1284 (13, 'export_catalog', 'Export bibliographic and holdings data'),
1285 (13, 'import_patrons', 'Import patron data'),
1286 (13, 'delete_anonymize_patrons', 'Delete old borrowers and anonymize circulation history (deletes borrower reading history)'),
1287 (13, 'batch_upload_patron_images', 'Upload patron images in batch or one at a time'),
1288 (13, 'schedule_tasks', 'Schedule tasks to run')");
1290 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('GranularPermissions','0','Use detailed staff user permissions',NULL,'YesNo')");
1292 print "Upgrade to $DBversion done (adding permissions and user_permissions tables and GranularPermissions syspref) \n";
1293 SetVersion ($DBversion);
1295 $DBversion = "3.00.00.069";
1296 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1297 $dbh->do("ALTER TABLE labels_conf CHANGE COLUMN class classification int(1) DEFAULT NULL;");
1298 print "Upgrade to $DBversion done ( Correcting columname in labels_conf )\n";
1299 SetVersion ($DBversion);
1302 $DBversion = "3.00.00.070";
1303 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1304 $sth = $dbh->prepare("SELECT value FROM systempreferences WHERE variable='yuipath'");
1306 my ($value) = $sth->fetchrow;
1307 $value =~ s/2.3.1/2.5.1/;
1308 $dbh->do("UPDATE systempreferences SET value='$value' WHERE variable='yuipath';");
1309 print "Update yuipath syspref to 2.5.1 if necessary\n";
1310 SetVersion ($DBversion);
1313 $DBversion = "3.00.00.071";
1314 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1315 $dbh->do(" ALTER TABLE `subscription` ADD `serialsadditems` TINYINT( 1 ) NOT NULL DEFAULT '0';");
1316 # fill the new field with the previous systempreference value, then drop the syspref
1317 my $sth = $dbh->prepare("SELECT value FROM systempreferences WHERE variable='serialsadditems'");
1319 my ($serialsadditems) = $sth->fetchrow();
1320 $dbh->do("UPDATE subscription SET serialsadditems=$serialsadditems");
1321 $dbh->do("DELETE FROM systempreferences WHERE variable='serialsadditems'");
1322 print "Upgrade to $DBversion done ( moving serialsadditems from syspref to subscription )\n";
1323 SetVersion ($DBversion);
1326 $DBversion = "3.00.00.072";
1327 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1328 $dbh->do("ALTER TABLE labels_conf ADD COLUMN formatstring mediumtext DEFAULT NULL AFTER printingtype");
1329 print "Upgrade to $DBversion done ( Adding format string to labels generator. )\n";
1330 SetVersion ($DBversion);
1333 $DBversion = "3.00.00.073";
1334 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1335 $dbh->do("DROP TABLE IF EXISTS `tags_all`;");
1337 CREATE TABLE `tags_all` (
1338 `tag_id` int(11) NOT NULL auto_increment,
1339 `borrowernumber` int(11) NOT NULL,
1340 `biblionumber` int(11) NOT NULL,
1341 `term` varchar(255) NOT NULL,
1342 `language` int(4) default NULL,
1343 `date_created` datetime NOT NULL,
1344 PRIMARY KEY (`tag_id`),
1345 KEY `tags_borrowers_fk_1` (`borrowernumber`),
1346 KEY `tags_biblionumber_fk_1` (`biblionumber`),
1347 CONSTRAINT `tags_borrowers_fk_1` FOREIGN KEY (`borrowernumber`)
1348 REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1349 CONSTRAINT `tags_biblionumber_fk_1` FOREIGN KEY (`biblionumber`)
1350 REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE
1351 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1353 $dbh->do("DROP TABLE IF EXISTS `tags_approval`;");
1355 CREATE TABLE `tags_approval` (
1356 `term` varchar(255) NOT NULL,
1357 `approved` int(1) NOT NULL default '0',
1358 `date_approved` datetime default NULL,
1359 `approved_by` int(11) default NULL,
1360 `weight_total` int(9) NOT NULL default '1',
1361 PRIMARY KEY (`term`),
1362 KEY `tags_approval_borrowers_fk_1` (`approved_by`),
1363 CONSTRAINT `tags_approval_borrowers_fk_1` FOREIGN KEY (`approved_by`)
1364 REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE
1365 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1367 $dbh->do("DROP TABLE IF EXISTS `tags_index`;");
1369 CREATE TABLE `tags_index` (
1370 `term` varchar(255) NOT NULL,
1371 `biblionumber` int(11) NOT NULL,
1372 `weight` int(9) NOT NULL default '1',
1373 PRIMARY KEY (`term`,`biblionumber`),
1374 KEY `tags_index_biblionumber_fk_1` (`biblionumber`),
1375 CONSTRAINT `tags_index_term_fk_1` FOREIGN KEY (`term`)
1376 REFERENCES `tags_approval` (`term`) ON DELETE CASCADE ON UPDATE CASCADE,
1377 CONSTRAINT `tags_index_biblionumber_fk_1` FOREIGN KEY (`biblionumber`)
1378 REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE
1379 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1382 INSERT INTO `systempreferences` VALUES
1383 ('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=',''),
1384 ('BakerTaylorEnabled','0','','Enable or disable all Baker & Taylor features.','YesNo'),
1385 ('BakerTaylorPassword','','','Baker & Taylor Password for Content Cafe (external content)','Textarea'),
1386 ('BakerTaylorUsername','','','Baker & Taylor Username for Content Cafe (external content)','Textarea'),
1387 ('TagsEnabled','1','','Enables or disables all tagging features. This is the main switch for tags.','YesNo'),
1388 ('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.',''),
1389 ('TagsInputOnDetail','1','','Allow users to input tags from the detail page.', 'YesNo'),
1390 ('TagsInputOnList', '0','','Allow users to input tags from the search results list.', 'YesNo'),
1391 ('TagsModeration', NULL,'','Require tags from patrons to be approved before becoming visible.','YesNo'),
1392 ('TagsShowOnDetail','10','','Number of tags to display on detail page. 0 is off.', 'Integer'),
1393 ('TagsShowOnList', '6','','Number of tags to display on search results list. 0 is off.','Integer')
1395 print "Upgrade to $DBversion done (Baker/Taylor,Tags: sysprefs and tables (tags_all, tags_index, tags_approval)) \n";
1396 SetVersion ($DBversion);
1399 $DBversion = "3.00.00.074";
1400 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1401 $dbh->do( q(update itemtypes set imageurl = concat( 'npl/', imageurl )
1402 where imageurl not like 'http%'
1403 and imageurl is not NULL
1404 and imageurl != '') );
1405 print "Upgrade to $DBversion done (updating imagetype.imageurls to reflect new icon locations.)\n";
1406 SetVersion ($DBversion);
1409 $DBversion = "3.00.00.075";
1410 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1411 $dbh->do( q(alter table authorised_values add imageurl varchar(200) default NULL) );
1412 print "Upgrade to $DBversion done (adding imageurl field to authorised_values table)\n";
1413 SetVersion ($DBversion);
1416 $DBversion = "3.00.00.076";
1417 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1418 $dbh->do("ALTER TABLE import_batches
1419 ADD COLUMN nomatch_action enum('create_new', 'ignore') NOT NULL default 'create_new' AFTER overlay_action");
1420 $dbh->do("ALTER TABLE import_batches
1421 ADD COLUMN item_action enum('always_add', 'add_only_for_matches', 'add_only_for_new', 'ignore')
1422 NOT NULL default 'always_add' AFTER nomatch_action");
1423 $dbh->do("ALTER TABLE import_batches
1424 MODIFY overlay_action enum('replace', 'create_new', 'use_template', 'ignore')
1425 NOT NULL default 'create_new'");
1426 $dbh->do("ALTER TABLE import_records
1427 MODIFY status enum('error', 'staged', 'imported', 'reverted', 'items_reverted',
1428 'ignored') NOT NULL default 'staged'");
1429 $dbh->do("ALTER TABLE import_items
1430 MODIFY status enum('error', 'staged', 'imported', 'reverted', 'ignored') NOT NULL default 'staged'");
1432 print "Upgrade to $DBversion done (changes to import_batches and import_records)\n";
1433 SetVersion ($DBversion);
1436 $DBversion = "3.00.00.077";
1437 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1438 # drop these tables only if they exist and none of them are empty
1439 # these tables are not defined in the packaged 2.2.9, but since it is believed
1440 # that at least one library may be using them in a post-2.2.9 but pre-3.0 Koha,
1441 # some care is taken.
1442 my ($print_error) = $dbh->{PrintError};
1443 $dbh->{PrintError} = 0;
1444 my ($raise_error) = $dbh->{RaiseError};
1445 $dbh->{RaiseError} = 1;
1449 eval { $count = $dbh->do("SELECT 1 FROM categorytable"); };
1453 eval { $count = $dbh->do("SELECT 1 FROM mediatypetable"); };
1457 eval { $count = $dbh->do("SELECT 1 FROM subcategorytable"); };
1463 $dbh->do("DROP TABLE IF EXISTS `categorytable`");
1464 $dbh->do("DROP TABLE IF EXISTS `mediatypetable`");
1465 $dbh->do("DROP TABLE IF EXISTS `subcategorytable`");
1468 $dbh->{PrintError} = $print_error;
1469 $dbh->{RaiseError} = $raise_error;
1470 print "Upgrade to $DBversion done (drop categorytable, subcategorytable, and mediatypetable)\n";
1471 SetVersion ($DBversion);
1474 $DBversion = "3.00.00.078";
1475 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1476 my ($print_error) = $dbh->{PrintError};
1477 $dbh->{PrintError} = 0;
1479 unless ($dbh->do("SELECT 1 FROM browser")) {
1480 $dbh->{PrintError} = $print_error;
1481 $dbh->do("CREATE TABLE `browser` (
1482 `level` int(11) NOT NULL,
1483 `classification` varchar(20) NOT NULL,
1484 `description` varchar(255) NOT NULL,
1485 `number` bigint(20) NOT NULL,
1486 `endnode` tinyint(4) NOT NULL
1487 ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
1489 $dbh->{PrintError} = $print_error;
1490 print "Upgrade to $DBversion done (add browser table if not already present)\n";
1491 SetVersion ($DBversion);
1494 $DBversion = "3.00.00.079";
1495 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1496 my ($print_error) = $dbh->{PrintError};
1497 $dbh->{PrintError} = 0;
1499 $dbh->do("INSERT INTO `systempreferences` (variable, value,options,type, explanation)VALUES
1500 ('AddPatronLists','categorycode','categorycode|category_type','Choice','Allow user to choose what list to pick up from when adding patrons')");
1501 print "Upgrade to $DBversion done (add browser table if not already present)\n";
1502 SetVersion ($DBversion);
1505 $DBversion = "3.00.00.080";
1506 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1507 $dbh->do("ALTER TABLE subscription CHANGE monthlength monthlength int(11) default '0'");
1508 $dbh->do("ALTER TABLE deleteditems MODIFY marc LONGBLOB AFTER copynumber");
1509 $dbh->do("ALTER TABLE aqbooksellers CHANGE name name mediumtext NOT NULL");
1510 print "Upgrade to $DBversion done (catch up on DB schema changes since alpha and beta)\n";
1511 SetVersion ($DBversion);
1514 $DBversion = "3.00.00.081";
1515 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1516 $dbh->do("CREATE TABLE `borrower_attribute_types` (
1517 `code` varchar(10) NOT NULL,
1518 `description` varchar(255) NOT NULL,
1519 `repeatable` tinyint(1) NOT NULL default 0,
1520 `unique_id` tinyint(1) NOT NULL default 0,
1521 `opac_display` tinyint(1) NOT NULL default 0,
1522 `password_allowed` tinyint(1) NOT NULL default 0,
1523 `staff_searchable` tinyint(1) NOT NULL default 0,
1524 `authorised_value_category` varchar(10) default NULL,
1525 PRIMARY KEY (`code`)
1526 ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
1527 $dbh->do("CREATE TABLE `borrower_attributes` (
1528 `borrowernumber` int(11) NOT NULL,
1529 `code` varchar(10) NOT NULL,
1530 `attribute` varchar(30) default NULL,
1531 `password` varchar(30) default NULL,
1532 KEY `borrowernumber` (`borrowernumber`),
1533 KEY `code_attribute` (`code`, `attribute`),
1534 CONSTRAINT `borrower_attributes_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`)
1535 ON DELETE CASCADE ON UPDATE CASCADE,
1536 CONSTRAINT `borrower_attributes_ibfk_2` FOREIGN KEY (`code`) REFERENCES `borrower_attribute_types` (`code`)
1537 ON DELETE CASCADE ON UPDATE CASCADE
1538 ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
1539 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('ExtendedPatronAttributes','0','Use extended patron IDs and attributes',NULL,'YesNo')");
1540 print "Upgrade to $DBversion done (added borrower_attributes and borrower_attribute_types)\n";
1541 SetVersion ($DBversion);
1544 $DBversion = "3.00.00.082";
1545 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1546 $dbh->do( q(alter table accountlines add column lastincrement decimal(28,6) default NULL) );
1547 print "Upgrade to $DBversion done (adding lastincrement column to accountlines table)\n";
1548 SetVersion ($DBversion);
1551 $DBversion = "3.00.00.083";
1552 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1553 $dbh->do( qq(UPDATE systempreferences SET value='local' where variable='yuipath' and value like "%/intranet-tmpl/prog/%"));
1554 print "Upgrade to $DBversion done (Changing yuipath behaviour in managing a local value)\n";
1555 SetVersion ($DBversion);
1557 $DBversion = "3.00.00.084";
1558 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1559 $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')");
1560 $dbh->do("INSERT INTO systempreferences (variable,value,explanation,options,type) VALUES('GoogleJackets','0','if ON, displays jacket covers from Google Books API',NULL,'YesNo')");
1561 print "Upgrade to $DBversion done (add new sysprefs)\n";
1562 SetVersion ($DBversion);
1565 $DBversion = "3.00.00.085";
1566 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1567 if (C4::Context->preference("marcflavour") eq 'MARC21') {
1568 $dbh->do("UPDATE marc_subfield_structure SET tab = 0 WHERE tab = 9 AND tagfield = '037'");
1569 $dbh->do("UPDATE marc_subfield_structure SET tab = 1 WHERE tab = 6 AND tagfield in ('100', '110', '111', '130')");
1570 $dbh->do("UPDATE marc_subfield_structure SET tab = 2 WHERE tab = 6 AND tagfield in ('240', '243')");
1571 $dbh->do("UPDATE marc_subfield_structure SET tab = 4 WHERE tab = 6 AND tagfield in ('400', '410', '411', '440')");
1572 $dbh->do("UPDATE marc_subfield_structure SET tab = 5 WHERE tab = 9 AND tagfield = '584'");
1573 $dbh->do("UPDATE marc_subfield_structure SET tab = 7 WHERE tab = -6 AND tagfield = '760'");
1575 print "Upgrade to $DBversion done (move editing tab of various MARC21 subfields)\n";
1576 SetVersion ($DBversion);
1579 $DBversion = "3.00.00.086";
1580 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1582 "CREATE TABLE `tmp_holdsqueue` (
1583 `biblionumber` int(11) default NULL,
1584 `itemnumber` int(11) default NULL,
1585 `barcode` varchar(20) default NULL,
1586 `surname` mediumtext NOT NULL,
1589 `borrowernumber` int(11) NOT NULL,
1590 `cardnumber` varchar(16) default NULL,
1591 `reservedate` date default NULL,
1593 `itemcallnumber` varchar(30) default NULL,
1594 `holdingbranch` varchar(10) default NULL,
1595 `pickbranch` varchar(10) default NULL,
1597 ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
1599 $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')");
1600 $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')");
1602 print "Upgrade to $DBversion done (Table structure for table `tmp_holdsqueue`)\n";
1603 SetVersion ($DBversion);
1606 $DBversion = "3.00.00.087";
1607 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1608 $dbh->do("INSERT INTO `systempreferences` VALUES ('AutoEmailOpacUser','0','','Sends notification emails containing new account details to patrons - when account is created.','YesNo')" );
1609 $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')");
1610 print "Upgrade to $DBversion done (added 2 new 'AutoEmailOpacUser' sysprefs)\n";
1611 SetVersion ($DBversion);
1614 $DBversion = "3.00.00.088";
1615 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1616 $dbh->do("INSERT INTO `systempreferences` (variable,value,options,explanation,type) VALUES ('OPACShelfBrowser','1','','Enable/disable Shelf Browser on item details page','YesNo')");
1617 $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')");
1618 $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')");
1619 $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')");
1620 print "Upgrade to $DBversion done (added 2 new 'AutoEmailOpacUser' sysprefs)\n";
1621 SetVersion ($DBversion);
1624 $DBversion = "3.00.00.089";
1625 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1626 $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')");
1627 print "Upgrade to $DBversion done (added new AdvancedSearchTypes syspref)\n";
1628 SetVersion ($DBversion);
1631 $DBversion = "3.00.00.090";
1632 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1634 CREATE TABLE `branch_borrower_circ_rules` (
1635 `branchcode` VARCHAR(10) NOT NULL,
1636 `categorycode` VARCHAR(10) NOT NULL,
1637 `maxissueqty` int(4) default NULL,
1638 PRIMARY KEY (`categorycode`, `branchcode`),
1639 CONSTRAINT `branch_borrower_circ_rules_ibfk_1` FOREIGN KEY (`categorycode`) REFERENCES `categories` (`categorycode`)
1640 ON DELETE CASCADE ON UPDATE CASCADE,
1641 CONSTRAINT `branch_borrower_circ_rules_ibfk_2` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`)
1642 ON DELETE CASCADE ON UPDATE CASCADE
1643 ) ENGINE=InnoDB DEFAULT CHARSET=utf8
1646 CREATE TABLE `default_borrower_circ_rules` (
1647 `categorycode` VARCHAR(10) NOT NULL,
1648 `maxissueqty` int(4) default NULL,
1649 PRIMARY KEY (`categorycode`),
1650 CONSTRAINT `borrower_borrower_circ_rules_ibfk_1` FOREIGN KEY (`categorycode`) REFERENCES `categories` (`categorycode`)
1651 ON DELETE CASCADE ON UPDATE CASCADE
1652 ) ENGINE=InnoDB DEFAULT CHARSET=utf8
1655 CREATE TABLE `default_branch_circ_rules` (
1656 `branchcode` VARCHAR(10) NOT NULL,
1657 `maxissueqty` int(4) default NULL,
1658 PRIMARY KEY (`branchcode`),
1659 CONSTRAINT `default_branch_circ_rules_ibfk_1` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`)
1660 ON DELETE CASCADE ON UPDATE CASCADE
1661 ) ENGINE=InnoDB DEFAULT CHARSET=utf8
1664 CREATE TABLE `default_circ_rules` (
1665 `singleton` enum('singleton') NOT NULL default 'singleton',
1666 `maxissueqty` int(4) default NULL,
1667 PRIMARY KEY (`singleton`)
1668 ) ENGINE=InnoDB DEFAULT CHARSET=utf8
1670 print "Upgrade to $DBversion done (added several circ rules tables)\n";
1671 SetVersion ($DBversion);
1675 $DBversion = "3.00.00.091";
1676 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1677 $dbh->do(<<'END_SQL');
1678 ALTER TABLE borrowers
1679 ADD `smsalertnumber` varchar(50) default NULL
1682 $dbh->do(<<'END_SQL');
1683 CREATE TABLE `message_attributes` (
1684 `message_attribute_id` int(11) NOT NULL auto_increment,
1685 `message_name` varchar(20) NOT NULL default '',
1686 `takes_days` tinyint(1) NOT NULL default '0',
1687 PRIMARY KEY (`message_attribute_id`),
1688 UNIQUE KEY `message_name` (`message_name`)
1689 ) ENGINE=InnoDB DEFAULT CHARSET=utf8
1692 $dbh->do(<<'END_SQL');
1693 CREATE TABLE `message_transport_types` (
1694 `message_transport_type` varchar(20) NOT NULL,
1695 PRIMARY KEY (`message_transport_type`)
1696 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1699 $dbh->do(<<'END_SQL');
1700 CREATE TABLE `message_transports` (
1701 `message_attribute_id` int(11) NOT NULL,
1702 `message_transport_type` varchar(20) NOT NULL,
1703 `is_digest` tinyint(1) NOT NULL default '0',
1704 `letter_module` varchar(20) NOT NULL default '',
1705 `letter_code` varchar(20) NOT NULL default '',
1706 PRIMARY KEY (`message_attribute_id`,`message_transport_type`,`is_digest`),
1707 KEY `message_transport_type` (`message_transport_type`),
1708 KEY `letter_module` (`letter_module`,`letter_code`),
1709 CONSTRAINT `message_transports_ibfk_1` FOREIGN KEY (`message_attribute_id`) REFERENCES `message_attributes` (`message_attribute_id`) ON DELETE CASCADE ON UPDATE CASCADE,
1710 CONSTRAINT `message_transports_ibfk_2` FOREIGN KEY (`message_transport_type`) REFERENCES `message_transport_types` (`message_transport_type`) ON DELETE CASCADE ON UPDATE CASCADE,
1711 CONSTRAINT `message_transports_ibfk_3` FOREIGN KEY (`letter_module`, `letter_code`) REFERENCES `letter` (`module`, `code`) ON DELETE CASCADE ON UPDATE CASCADE
1712 ) ENGINE=InnoDB DEFAULT CHARSET=utf8
1715 $dbh->do(<<'END_SQL');
1716 CREATE TABLE `borrower_message_preferences` (
1717 `borrower_message_preference_id` int(11) NOT NULL auto_increment,
1718 `borrowernumber` int(11) NOT NULL default '0',
1719 `message_attribute_id` int(11) default '0',
1720 `days_in_advance` int(11) default '0',
1721 `wants_digets` tinyint(1) NOT NULL default '0',
1722 PRIMARY KEY (`borrower_message_preference_id`),
1723 KEY `borrowernumber` (`borrowernumber`),
1724 KEY `message_attribute_id` (`message_attribute_id`),
1725 CONSTRAINT `borrower_message_preferences_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1726 CONSTRAINT `borrower_message_preferences_ibfk_2` FOREIGN KEY (`message_attribute_id`) REFERENCES `message_attributes` (`message_attribute_id`) ON DELETE CASCADE ON UPDATE CASCADE
1727 ) ENGINE=InnoDB DEFAULT CHARSET=utf8
1730 $dbh->do(<<'END_SQL');
1731 CREATE TABLE `borrower_message_transport_preferences` (
1732 `borrower_message_preference_id` int(11) NOT NULL default '0',
1733 `message_transport_type` varchar(20) NOT NULL default '0',
1734 PRIMARY KEY (`borrower_message_preference_id`,`message_transport_type`),
1735 KEY `message_transport_type` (`message_transport_type`),
1736 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,
1737 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
1738 ) ENGINE=InnoDB DEFAULT CHARSET=utf8
1741 $dbh->do(<<'END_SQL');
1742 CREATE TABLE `message_queue` (
1743 `message_id` int(11) NOT NULL auto_increment,
1744 `borrowernumber` int(11) NOT NULL,
1747 `message_transport_type` varchar(20) NOT NULL,
1748 `status` enum('sent','pending','failed','deleted') NOT NULL default 'pending',
1749 `time_queued` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1750 KEY `message_id` (`message_id`),
1751 KEY `borrowernumber` (`borrowernumber`),
1752 KEY `message_transport_type` (`message_transport_type`),
1753 CONSTRAINT `messageq_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1754 CONSTRAINT `messageq_ibfk_2` FOREIGN KEY (`message_transport_type`) REFERENCES `message_transport_types` (`message_transport_type`) ON DELETE RESTRICT ON UPDATE CASCADE
1755 ) ENGINE=InnoDB DEFAULT CHARSET=utf8
1758 $dbh->do(<<'END_SQL');
1759 INSERT INTO `systempreferences`
1760 (variable,value,explanation,options,type)
1762 ('EnhancedMessagingPreferences',0,'If ON, allows patrons to select to receive additional messages about items due or nearly due.','','YesNo')
1765 $dbh->do( <<'END_SQL');
1766 INSERT INTO `letter`
1767 (module, code, name, title, content)
1769 ('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>>'),
1770 ('circulation','DUEDGST','Item Due Reminder (Digest)','Item Due Reminder','You have <<count>> items due'),
1771 ('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>>'),
1772 ('circulation','PREDUEDGST','Advance Notice of Item Due (Digest)','Advance Notice of Item Due','You have <<count>> items due soon'),
1773 ('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.');
1777 'installer/data/mysql/en/mandatory/message_transport_types.sql',
1778 'installer/data/mysql/en/optional/sample_notices_message_attributes.sql',
1779 'installer/data/mysql/en/optional/sample_notices_message_transports.sql',
1782 my $installer = C4::Installer->new();
1783 foreach my $script ( @sql_scripts ) {
1784 my $full_path = $installer->get_file_path_from_name($script);
1785 my $error = $installer->load_sql($full_path);
1786 warn $error if $error;
1789 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";
1790 SetVersion ($DBversion);
1793 $DBversion = "3.00.00.092";
1794 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1795 $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')");
1796 $dbh->do("INSERT INTO `systempreferences` (variable,value,options,explanation,type) VALUES('AllowHoldsOnDamagedItems', '1', '', 'Allow hold requests to be placed on damaged items', 'YesNo')");
1797 print "Upgrade to $DBversion done (added new AllowOnShelfHolds syspref)\n";
1798 SetVersion ($DBversion);
1801 $DBversion = "3.00.00.093";
1802 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1803 $dbh->do("ALTER TABLE `items` MODIFY COLUMN `copynumber` VARCHAR(32) DEFAULT NULL");
1804 $dbh->do("ALTER TABLE `deleteditems` MODIFY COLUMN `copynumber` VARCHAR(32) DEFAULT NULL");
1805 print "Upgrade to $DBversion done (Change data type of items.copynumber to allow free text)\n";
1806 SetVersion ($DBversion);
1809 $DBversion = "3.00.00.094";
1810 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1811 $dbh->do("ALTER TABLE `marc_subfield_structure` MODIFY `tagsubfield` VARCHAR(1) NOT NULL DEFAULT '' COLLATE utf8_bin");
1812 print "Upgrade to $DBversion done (Change Collation of marc_subfield_structure to allow mixed case in subfield labels.)\n";
1813 SetVersion ($DBversion);
1816 $DBversion = "3.00.00.095";
1817 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1818 if (C4::Context->preference("marcflavour") eq 'MARC21') {
1819 $dbh->do("UPDATE marc_subfield_structure SET authtypecode = 'MEETI_NAME' WHERE authtypecode = 'Meeting Name'");
1820 $dbh->do("UPDATE marc_subfield_structure SET authtypecode = 'CORPO_NAME' WHERE authtypecode = 'CORP0_NAME'");
1822 print "Upgrade to $DBversion done (fix invalid authority types in MARC21 frameworks [bug 2254])\n";
1823 SetVersion ($DBversion);
1826 $DBversion = "3.00.00.096";
1827 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1828 $sth = $dbh->prepare("SHOW COLUMNS FROM borrower_message_preferences LIKE 'wants_digets'");
1830 if (my $row = $sth->fetchrow_hashref) {
1831 $dbh->do("ALTER TABLE borrower_message_preferences CHANGE wants_digets wants_digest tinyint(1) NOT NULL default 0");
1833 print "Upgrade to $DBversion done (fix name borrower_message_preferences.wants_digest)\n";
1834 SetVersion ($DBversion);
1837 $DBversion = '3.00.00.097';
1838 if ( C4::Context->preference('Version') < TransformToNum($DBversion) ) {
1840 $dbh->do('ALTER TABLE message_queue ADD to_address mediumtext default NULL');
1841 $dbh->do('ALTER TABLE message_queue ADD from_address mediumtext default NULL');
1842 $dbh->do('ALTER TABLE message_queue ADD content_type text');
1843 $dbh->do('ALTER TABLE message_queue CHANGE borrowernumber borrowernumber int(11) default NULL');
1845 print "Upgrade to $DBversion done (updating 4 fields in message_queue table)\n";
1846 SetVersion($DBversion);
1849 $DBversion = '3.00.00.098';
1850 if ( C4::Context->preference('Version') < TransformToNum($DBversion) ) {
1852 $dbh->do(q(DELETE FROM message_transport_types WHERE message_transport_type = 'rss'));
1853 $dbh->do(q(DELETE FROM message_transports WHERE message_transport_type = 'rss'));
1855 print "Upgrade to $DBversion done (removing unused RSS message_transport_type)\n";
1856 SetVersion($DBversion);
1859 $DBversion = '3.00.00.099';
1860 if ( C4::Context->preference('Version') < TransformToNum($DBversion) ) {
1861 $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')");
1862 print "Upgrade to $DBversion done (Adding OpacSuppression syspref)\n";
1863 SetVersion($DBversion);
1866 $DBversion = '3.00.00.100';
1867 if ( C4::Context->preference('Version') < TransformToNum($DBversion) ) {
1868 $dbh->do('ALTER TABLE virtualshelves ADD COLUMN lastmodified timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP');
1869 print "Upgrade to $DBversion done (Adding lastmodified column to virtualshelves)\n";
1870 SetVersion($DBversion);
1873 $DBversion = '3.00.00.101';
1874 if ( C4::Context->preference('Version') < TransformToNum($DBversion) ) {
1875 $dbh->do('ALTER TABLE `overduerules` CHANGE `categorycode` `categorycode` VARCHAR(10) NOT NULL');
1876 $dbh->do('ALTER TABLE `deletedborrowers` CHANGE `categorycode` `categorycode` VARCHAR(10) NOT NULL');
1877 print "Upgrade to $DBversion done (Updating columnd definitions for patron category codes in notice/statsu triggers and deletedborrowers tables.)\n";
1878 SetVersion($DBversion);
1881 $DBversion = '3.00.00.102';
1882 if ( C4::Context->preference('Version') < TransformToNum($DBversion) ) {
1883 $dbh->do('ALTER TABLE serialitems MODIFY `serialid` int(11) NOT NULL AFTER itemnumber' );
1884 $dbh->do('ALTER TABLE serialitems DROP KEY serialididx' );
1885 $dbh->do('ALTER TABLE serialitems ADD CONSTRAINT UNIQUE KEY serialitemsidx (itemnumber)' );
1886 # before setting constraint, delete any unvalid data
1887 $dbh->do('DELETE from serialitems WHERE serialid not in (SELECT serial.serialid FROM serial)');
1888 $dbh->do('ALTER TABLE serialitems ADD CONSTRAINT serialitems_sfk_1 FOREIGN KEY (serialid) REFERENCES serial (serialid) ON DELETE CASCADE ON UPDATE CASCADE' );
1889 print "Upgrade to $DBversion done (Updating serialitems table to allow for multiple items per serial fixing kohabug 2380)\n";
1890 SetVersion($DBversion);
1893 $DBversion = "3.00.00.103";
1894 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1895 $dbh->do("DELETE FROM systempreferences WHERE variable='serialsadditems'");
1896 print "Upgrade to $DBversion done ( Verifying the removal of serialsadditems from syspref fixing kohabug 2219)\n";
1897 SetVersion ($DBversion);
1900 $DBversion = "3.00.00.104";
1901 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1902 $dbh->do("DELETE FROM systempreferences WHERE variable='noOPACHolds'");
1903 print "Upgrade to $DBversion done (remove superseded 'noOPACHolds' system preference per bug 2413)\n";
1904 SetVersion ($DBversion);
1907 $DBversion = '3.00.00.105';
1908 if ( C4::Context->preference("Version") < TransformToNum($DBversion) ) {
1910 # it is possible that this syspref is already defined since the feature was added some time ago.
1911 unless ( $dbh->do(q(SELECT variable FROM systempreferences WHERE variable = 'SMSSendDriver')) ) {
1912 $dbh->do(<<'END_SQL');
1913 INSERT INTO `systempreferences`
1914 (variable,value,explanation,options,type)
1916 ('SMSSendDriver','','Sets which SMS::Send driver is used to send SMS messages.','','free')
1919 print "Upgrade to $DBversion done (added SMSSendDriver system preference)\n";
1920 SetVersion($DBversion);
1923 $DBversion = "3.00.00.106";
1924 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1925 $dbh->do("DELETE FROM systempreferences WHERE variable='noOPACHolds'");
1927 # db revision 105 didn't apply correctly, so we're rolling this into 106
1928 $dbh->do("INSERT INTO `systempreferences`
1929 (variable,value,explanation,options,type)
1931 ('SMSSendDriver','','Sets which SMS::Send driver is used to send SMS messages.','','free')");
1933 print "Upgrade to $DBversion done (remove default '0000-00-00' in subscriptionhistory.enddate field)\n";
1934 $dbh->do("ALTER TABLE `subscriptionhistory` CHANGE `enddate` `enddate` DATE NULL DEFAULT NULL ");
1935 $dbh->do("UPDATE subscriptionhistory SET enddate=NULL WHERE enddate='0000-00-00'");
1936 SetVersion ($DBversion);
1939 $DBversion = '3.00.00.107';
1940 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1941 $dbh->do(<<'END_SQL');
1942 UPDATE systempreferences
1943 SET explanation = CONCAT( explanation, '. WARNING: this feature is very resource consuming on collections with large numbers of items.' )
1944 WHERE variable = 'OPACShelfBrowser'
1945 AND explanation NOT LIKE '%WARNING%'
1947 $dbh->do(<<'END_SQL');
1948 UPDATE systempreferences
1949 SET explanation = CONCAT( explanation, '. WARNING: this feature is very resource consuming.' )
1950 WHERE variable = 'CataloguingLog'
1951 AND explanation NOT LIKE '%WARNING%'
1953 $dbh->do(<<'END_SQL');
1954 UPDATE systempreferences
1955 SET explanation = CONCAT( explanation, '. WARNING: using NoZebra on even modest sized collections is very slow.' )
1956 WHERE variable = 'NoZebra'
1957 AND explanation NOT LIKE '%WARNING%'
1959 print "Upgrade to $DBversion done (warning added to OPACShelfBrowser system preference)\n";
1960 SetVersion ($DBversion);
1963 $DBversion = '3.01.00.000';
1964 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1965 print "Upgrade to $DBversion done (start of 3.1)\n";
1966 SetVersion ($DBversion);
1969 $DBversion = '3.01.00.001';
1970 if ( C4::Context->preference('Version') < TransformToNum($DBversion) ) {
1972 CREATE TABLE hold_fill_targets (
1973 `borrowernumber` int(11) NOT NULL,
1974 `biblionumber` int(11) NOT NULL,
1975 `itemnumber` int(11) NOT NULL,
1976 `source_branchcode` varchar(10) default NULL,
1977 `item_level_request` tinyint(4) NOT NULL default 0,
1978 PRIMARY KEY `itemnumber` (`itemnumber`),
1979 KEY `bib_branch` (`biblionumber`, `source_branchcode`),
1980 CONSTRAINT `hold_fill_targets_ibfk_1` FOREIGN KEY (`borrowernumber`)
1981 REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1982 CONSTRAINT `hold_fill_targets_ibfk_2` FOREIGN KEY (`biblionumber`)
1983 REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1984 CONSTRAINT `hold_fill_targets_ibfk_3` FOREIGN KEY (`itemnumber`)
1985 REFERENCES `items` (`itemnumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1986 CONSTRAINT `hold_fill_targets_ibfk_4` FOREIGN KEY (`source_branchcode`)
1987 REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE
1988 ) ENGINE=InnoDB DEFAULT CHARSET=utf8
1991 ALTER TABLE tmp_holdsqueue
1992 ADD item_level_request tinyint(4) NOT NULL default 0
1995 print "Upgrade to $DBversion done (add hold_fill_targets table and a column to tmp_holdsqueue)\n";
1996 SetVersion($DBversion);
1999 $DBversion = '3.01.00.002';
2000 if ( C4::Context->preference('Version') < TransformToNum($DBversion) ) {
2001 # use statistics where available
2003 ALTER TABLE statistics ADD KEY tmp_stats (type, itemnumber, borrowernumber)
2008 SELECT max(datetime)
2010 WHERE type = 'issue'
2011 AND itemnumber = iss.itemnumber
2012 AND borrowernumber = iss.borrowernumber
2014 WHERE issuedate IS NULL;
2016 $dbh->do("ALTER TABLE statistics DROP KEY tmp_stats");
2018 # default to last renewal date
2021 SET issuedate = lastreneweddate
2022 WHERE issuedate IS NULL
2023 and lastreneweddate IS NOT NULL
2026 my $num_bad_issuedates = $dbh->selectrow_array("SELECT COUNT(*) FROM issues WHERE issuedate IS NULL");
2027 if ($num_bad_issuedates > 0) {
2028 print STDERR "After the upgrade to $DBversion, there are still $num_bad_issuedates loan(s) with a NULL (blank) loan date. ",
2029 "Please check the issues table in your database.";
2031 print "Upgrade to $DBversion done (bug 2582: set null issues.issuedate to lastreneweddate)\n";
2032 SetVersion($DBversion);
2035 $DBversion = "3.01.00.003";
2036 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2037 $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')");
2038 print "Upgrade to $DBversion done (add new syspref)\n";
2039 SetVersion ($DBversion);
2042 $DBversion = '3.01.00.004';
2043 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2044 $dbh->do("INSERT INTO systempreferences (variable,value,explanation,options,type) VALUES ('OPACDisplayRequestPriority','0','Show patrons the priority level on holds in the OPAC','','YesNo')");
2045 print "Upgrade to $DBversion done (added OPACDisplayRequestPriority system preference)\n";
2046 SetVersion ($DBversion);
2049 $DBversion = '3.01.00.005';
2050 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2052 INSERT INTO `letter` (module, code, name, title, content)
2053 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>>')
2055 $dbh->do("INSERT INTO `message_attributes` (message_attribute_id, message_name, takes_days) values(4, 'Hold Filled', 0)");
2056 $dbh->do("INSERT INTO `message_transports` (message_attribute_id, message_transport_type, is_digest, letter_module, letter_code) values(4, 'sms', 0, 'reserves', 'HOLD')");
2057 $dbh->do("INSERT INTO `message_transports` (message_attribute_id, message_transport_type, is_digest, letter_module, letter_code) values(4, 'email', 0, 'reserves', 'HOLD')");
2058 print "Upgrade to $DBversion done (Add letter for holds notifications)\n";
2059 SetVersion ($DBversion);
2062 $DBversion = '3.01.00.006';
2063 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2064 $dbh->do("ALTER TABLE `biblioitems` ADD KEY issn (issn)");
2065 print "Upgrade to $DBversion done (add index on biblioitems.issn)\n";
2066 SetVersion ($DBversion);
2069 $DBversion = "3.01.00.007";
2070 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2071 $dbh->do("UPDATE `systempreferences` SET options='70|10' WHERE variable='intranetmainUserblock'");
2072 $dbh->do("UPDATE `systempreferences` SET options='70|10' WHERE variable='intranetuserjs'");
2073 $dbh->do("UPDATE `systempreferences` SET options='70|10' WHERE variable='opacheader'");
2074 $dbh->do("UPDATE `systempreferences` SET options='70|10' WHERE variable='OpacMainUserBlock'");
2075 $dbh->do("UPDATE `systempreferences` SET options='70|10' WHERE variable='OpacNav'");
2076 $dbh->do("UPDATE `systempreferences` SET options='70|10' WHERE variable='opacuserjs'");
2077 $dbh->do("UPDATE `systempreferences` SET options='30|10', type='Textarea' WHERE variable='OAI-PMH:Set'");
2078 $dbh->do("UPDATE `systempreferences` SET options='50' WHERE variable='intranetstylesheet'");
2079 $dbh->do("UPDATE `systempreferences` SET options='50' WHERE variable='intranetcolorstylesheet'");
2080 $dbh->do("UPDATE `systempreferences` SET options='10' WHERE variable='globalDueDate'");
2081 $dbh->do("UPDATE `systempreferences` SET type='Integer' WHERE variable='numSearchResults'");
2082 $dbh->do("UPDATE `systempreferences` SET type='Integer' WHERE variable='OPACnumSearchResults'");
2083 $dbh->do("UPDATE `systempreferences` SET type='Integer' WHERE variable='ReservesMaxPickupDelay'");
2084 $dbh->do("UPDATE `systempreferences` SET type='Integer' WHERE variable='TransfersMaxDaysWarning'");
2085 $dbh->do("UPDATE `systempreferences` SET type='Integer' WHERE variable='StaticHoldsQueueWeight'");
2086 $dbh->do("UPDATE `systempreferences` SET type='Integer' WHERE variable='holdCancelLength'");
2087 $dbh->do("UPDATE `systempreferences` SET type='Integer' WHERE variable='XISBNDailyLimit'");
2088 $dbh->do("UPDATE `systempreferences` SET type='Float' WHERE variable='gist'");
2089 $dbh->do("UPDATE `systempreferences` SET type='Free' WHERE variable='BakerTaylorUsername'");
2090 $dbh->do("UPDATE `systempreferences` SET type='Free' WHERE variable='BakerTaylorPassword'");
2091 $dbh->do("UPDATE `systempreferences` SET type='Textarea', options='70|10' WHERE variable='ISBD'");
2092 $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'");
2093 print "Upgrade to $DBversion done (fix display of many sysprefs)\n";
2094 SetVersion ($DBversion);
2097 $DBversion = '3.01.00.008';
2098 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2100 $dbh->do("CREATE TABLE branch_transfer_limits (
2101 limitId int(8) NOT NULL auto_increment,
2102 toBranch varchar(4) NOT NULL,
2103 fromBranch varchar(4) NOT NULL,
2104 itemtype varchar(4) NOT NULL,
2105 PRIMARY KEY (limitId)
2106 ) ENGINE=InnoDB DEFAULT CHARSET=utf8"
2109 $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')");
2111 print "Upgrade to $DBversion done (added branch_transfer_limits table and UseBranchTransferLimits system preference)\n";
2112 SetVersion ($DBversion);
2115 $DBversion = "3.01.00.009";
2116 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2117 $dbh->do("ALTER TABLE permissions MODIFY `code` varchar(64) DEFAULT NULL");
2118 $dbh->do("ALTER TABLE user_permissions MODIFY `code` varchar(64) DEFAULT NULL");
2119 $dbh->do("INSERT INTO permissions (module_bit, code, description) VALUES ( 1, 'circulate_remaining_permissions', 'Remaining circulation permissions')");
2120 $dbh->do("INSERT INTO permissions (module_bit, code, description) VALUES ( 1, 'override_renewals', 'Override blocked renewals')");
2121 print "Upgrade to $DBversion done (added subpermissions for circulate permission)\n";
2124 $DBversion = '3.01.00.010';
2125 if ( C4::Context->preference('Version') < TransformToNum($DBversion) ) {
2126 $dbh->do("ALTER TABLE `borrower_attributes` MODIFY COLUMN `attribute` VARCHAR(64) DEFAULT NULL");
2127 $dbh->do("ALTER TABLE `borrower_attributes` MODIFY COLUMN `password` VARCHAR(64) DEFAULT NULL");
2128 print "Upgrade to $DBversion done (bug 2687: increase length of borrower attribute fields)\n";
2129 SetVersion ($DBversion);
2132 $DBversion = '3.01.00.011';
2133 if ( C4::Context->preference('Version') < TransformToNum($DBversion) ) {
2135 # Yes, the old value was ^M terminated.
2136 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);";
2138 my $intranetuserjs = C4::Context->preference('intranetuserjs');
2139 if ($intranetuserjs and $intranetuserjs eq $bad_value) {
2140 my $sql = <<'END_SQL';
2141 UPDATE systempreferences
2143 WHERE variable = 'intranetuserjs'
2147 print "Upgrade to $DBversion done (removed bogus intranetuserjs syspref)\n";
2148 SetVersion($DBversion);
2151 $DBversion = "3.01.00.012";
2152 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2153 $dbh->do("INSERT INTO systempreferences (variable,value,explanation,options,type) VALUES('AllowHoldPolicyOverride', '0', 'Allow staff to override hold policies when placing holds',NULL,'YesNo')");
2155 CREATE TABLE `branch_item_rules` (
2156 `branchcode` varchar(10) NOT NULL,
2157 `itemtype` varchar(10) NOT NULL,
2158 `holdallowed` tinyint(1) default NULL,
2159 PRIMARY KEY (`itemtype`,`branchcode`),
2160 KEY `branch_item_rules_ibfk_2` (`branchcode`),
2161 CONSTRAINT `branch_item_rules_ibfk_1` FOREIGN KEY (`itemtype`) REFERENCES `itemtypes` (`itemtype`) ON DELETE CASCADE ON UPDATE CASCADE,
2162 CONSTRAINT `branch_item_rules_ibfk_2` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE
2163 ) ENGINE=InnoDB DEFAULT CHARSET=utf8
2166 CREATE TABLE `default_branch_item_rules` (
2167 `itemtype` varchar(10) NOT NULL,
2168 `holdallowed` tinyint(1) default NULL,
2169 PRIMARY KEY (`itemtype`),
2170 CONSTRAINT `default_branch_item_rules_ibfk_1` FOREIGN KEY (`itemtype`) REFERENCES `itemtypes` (`itemtype`) ON DELETE CASCADE ON UPDATE CASCADE
2171 ) ENGINE=InnoDB DEFAULT CHARSET=utf8
2174 ALTER TABLE default_branch_circ_rules
2175 ADD COLUMN holdallowed tinyint(1) NULL
2178 ALTER TABLE default_circ_rules
2179 ADD COLUMN holdallowed tinyint(1) NULL
2181 print "Upgrade to $DBversion done (Add tables and system preferences for holds policies)\n";
2182 SetVersion ($DBversion);
2185 $DBversion = '3.01.00.013';
2186 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2188 CREATE TABLE item_circulation_alert_preferences (
2189 id int(11) AUTO_INCREMENT,
2190 branchcode varchar(10) NOT NULL,
2191 categorycode varchar(10) NOT NULL,
2192 item_type varchar(10) NOT NULL,
2193 notification varchar(16) NOT NULL,
2195 KEY (branchcode, categorycode, item_type, notification)
2196 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2199 $dbh->do(q{ ALTER TABLE `message_queue` ADD metadata text DEFAULT NULL AFTER content; });
2200 $dbh->do(q{ ALTER TABLE `message_queue` ADD letter_code varchar(64) DEFAULT NULL AFTER metadata; });
2203 INSERT INTO `letter` (`module`, `code`, `name`, `title`, `content`) VALUES
2204 ('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.');
2207 INSERT INTO `letter` (`module`, `code`, `name`, `title`, `content`) VALUES
2208 ('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>>.');
2211 $dbh->do(q{INSERT INTO message_attributes (message_attribute_id, message_name, takes_days) VALUES (5, 'Item Check-in', 0);});
2212 $dbh->do(q{INSERT INTO message_attributes (message_attribute_id, message_name, takes_days) VALUES (6, 'Item Checkout', 0);});
2214 $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');});
2215 $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');});
2216 $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');});
2217 $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');});
2219 print "Upgrade to $DBversion done (data for Email Checkout Slips project)\n";
2220 SetVersion ($DBversion);
2223 $DBversion = "3.01.00.014";
2224 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2225 $dbh->do("ALTER TABLE `branch_transfer_limits` CHANGE `itemtype` `itemtype` VARCHAR( 4 ) CHARACTER SET utf8 COLLATE utf8_general_ci NULL");
2226 $dbh->do("ALTER TABLE `branch_transfer_limits` ADD `ccode` VARCHAR( 10 ) NULL ;");
2227 $dbh->do("INSERT INTO `systempreferences` ( `variable` , `value` , `options` , `explanation` , `type` )
2229 'BranchTransferLimitsType', 'ccode', 'itemtype|ccode', 'When using branch transfer limits, choose whether to limit by itemtype or collection code.', 'Choice'
2232 print "Upgrade to $DBversion done ( Updated table for Branch Transfer Limits)\n";
2233 SetVersion ($DBversion);
2236 $DBversion = '3.01.00.015';
2237 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2238 $dbh->do("INSERT INTO systempreferences (variable,value,explanation,options,type) VALUES ('SyndeticsClientCode', '0', 'Client Code for using Syndetics Solutions content','','free')");
2240 $dbh->do("INSERT INTO systempreferences (variable,value,explanation,options,type) VALUES ('SyndeticsEnabled', '0', 'Turn on Syndetics Enhanced Content','','YesNo')");
2242 $dbh->do("INSERT INTO systempreferences (variable,value,explanation,options,type) VALUES ('SyndeticsCoverImages', '0', 'Display Cover Images from Syndetics','','YesNo')");
2244 $dbh->do("INSERT INTO systempreferences (variable,value,explanation,options,type) VALUES ('SyndeticsTOC', '0', 'Display Table of Content information from Syndetics','','YesNo')");
2246 $dbh->do("INSERT INTO systempreferences (variable,value,explanation,options,type) VALUES ('SyndeticsSummary', '0', 'Display Summary Information from Syndetics','','YesNo')");
2248 $dbh->do("INSERT INTO systempreferences (variable,value,explanation,options,type) VALUES ('SyndeticsEditions', '0', 'Display Editions from Syndetics','','YesNo')");
2250 $dbh->do("INSERT INTO systempreferences (variable,value,explanation,options,type) VALUES ('SyndeticsExcerpt', '0', 'Display Excerpts and first chapters on OPAC from Syndetics','','YesNo')");
2252 $dbh->do("INSERT INTO systempreferences (variable,value,explanation,options,type) VALUES ('SyndeticsReviews', '0', 'Display Reviews on OPAC from Syndetics','','YesNo')");
2254 $dbh->do("INSERT INTO systempreferences (variable,value,explanation,options,type) VALUES ('SyndeticsAuthorNotes', '0', 'Display Notes about the Author on OPAC from Syndetics','','YesNo')");
2256 $dbh->do("INSERT INTO systempreferences (variable,value,explanation,options,type) VALUES ('SyndeticsAwards', '0', 'Display Awards on OPAC from Syndetics','','YesNo')");
2258 $dbh->do("INSERT INTO systempreferences (variable,value,explanation,options,type) VALUES ('SyndeticsSeries', '0', 'Display Series information on OPAC from Syndetics','','YesNo')");
2260 $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')");
2262 $dbh->do("INSERT INTO systempreferences (variable,value,explanation,options,type) VALUES ('OPACAmazonCoverImages', '0', 'Display cover images on OPAC from Amazon Web Services','','YesNo')");
2264 $dbh->do("INSERT INTO systempreferences (variable,value,explanation,options,type) VALUES ('AmazonCoverImages', '0', 'Display Cover Images in Staff Client from Amazon Web Services','','YesNo')");
2266 $dbh->do("UPDATE systempreferences SET variable='AmazonEnabled' WHERE variable = 'AmazonContent'");
2268 $dbh->do("UPDATE systempreferences SET variable='OPACAmazonEnabled' WHERE variable = 'OPACAmazonContent'");
2270 print "Upgrade to $DBversion done (added Syndetics Enhanced Content system preferences)\n";
2271 SetVersion ($DBversion);
2274 $DBversion = "3.01.00.016";
2275 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2276 $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')");
2277 print "Upgrade to $DBversion done (Added Babeltheque syspref)\n";
2278 SetVersion ($DBversion);
2281 $DBversion = "3.01.00.017";
2282 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2283 $dbh->do("ALTER TABLE `subscription` ADD `staffdisplaycount` VARCHAR(10) NULL;");
2284 $dbh->do("ALTER TABLE `subscription` ADD `opacdisplaycount` VARCHAR(10) NULL;");
2285 $dbh->do("INSERT INTO `systempreferences` ( `variable` , `value` , `options` , `explanation` , `type` )
2287 'StaffSerialIssueDisplayCount', '3', '', 'Number of serial issues to display per subscription in the Staff client', 'Integer'
2289 $dbh->do("INSERT INTO `systempreferences` ( `variable` , `value` , `options` , `explanation` , `type` )
2291 'OPACSerialIssueDisplayCount', '3', '', 'Number of serial issues to display per subscription in the OPAC', 'Integer'
2294 print "Upgrade to $DBversion done ( Updated table for Serials Display)\n";
2295 SetVersion ($DBversion);
2298 $DBversion = "3.01.00.018";
2299 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2300 $dbh->do("ALTER TABLE deletedborrowers ADD `smsalertnumber` varchar(50) default NULL");
2301 print "Upgrade to $DBversion done (added deletedborrowers.smsalertnumber, missed in 3.00.00.091)\n";
2302 SetVersion ($DBversion);
2305 $DBversion = "3.01.00.019";
2306 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2307 $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')");
2308 print "Upgrade to $DBversion done (adding OPACShowCheckoutName systempref)\n";
2309 SetVersion ($DBversion);
2312 $DBversion = "3.01.00.020";
2313 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2314 $dbh->do("INSERT INTO systempreferences (variable,value,explanation,options,type)VALUES('LibraryThingForLibrariesID','','See:http://librarything.com/forlibraries/','','free')");
2315 $dbh->do("INSERT INTO systempreferences (variable,value,explanation,options,type)VALUES('LibraryThingForLibrariesEnabled','0','Enable or Disable Library Thing for Libraries Features','','YesNo')");
2316 $dbh->do("INSERT INTO systempreferences (variable,value,explanation,options,type)VALUES('LibraryThingForLibrariesTabbedView','0','Put LibraryThingForLibraries Content in Tabs.','','YesNo')");
2317 print "Upgrade to $DBversion done (adding LibraryThing for Libraries sysprefs)\n";
2318 SetVersion ($DBversion);
2321 $DBversion = "3.01.00.021";
2322 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2323 my $enable_reviews = C4::Context->preference('OPACAmazonEnabled') ? '1' : '0';
2324 $dbh->do("INSERT INTO systempreferences (variable,value,explanation,options,type) VALUES ('OPACAmazonReviews', '$enable_reviews', 'Display Amazon readers reviews on OPAC','','YesNo')");
2325 print "Upgrade to $DBversion done (adding OPACAmazonReviews syspref)\n";
2326 SetVersion ($DBversion);
2329 $DBversion = '3.01.00.022';
2330 if ( C4::Context->preference('Version') < TransformToNum($DBversion) ) {
2331 $dbh->do("ALTER TABLE `labels_conf` MODIFY COLUMN `formatstring` mediumtext DEFAULT NULL");
2332 print "Upgrade to $DBversion done (bug 2945: increase size of labels_conf.formatstring)\n";
2333 SetVersion ($DBversion);
2336 $DBversion = '3.01.00.023';
2337 if ( C4::Context->preference('Version') < TransformToNum($DBversion) ) {
2338 $dbh->do("ALTER TABLE biblioitems MODIFY COLUMN isbn VARCHAR(30) DEFAULT NULL");
2339 $dbh->do("ALTER TABLE deletedbiblioitems MODIFY COLUMN isbn VARCHAR(30) DEFAULT NULL");
2340 $dbh->do("ALTER TABLE import_biblios MODIFY COLUMN isbn VARCHAR(30) DEFAULT NULL");
2341 $dbh->do("ALTER TABLE suggestions MODIFY COLUMN isbn VARCHAR(30) DEFAULT NULL");
2342 print "Upgrade to $DBversion done (bug 2765: increase width of isbn column in several tables)\n";
2343 SetVersion ($DBversion);
2346 $DBversion = "3.01.00.024";
2347 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2348 $dbh->do("ALTER TABLE labels MODIFY COLUMN batch_id int(10) NOT NULL default 1;");
2349 print "Upgrade to $DBversion done (change labels.batch_id from varchar to int)\n";
2350 SetVersion ($DBversion);
2353 $DBversion = '3.01.00.025';
2354 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2355 $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')");
2357 print "Upgrade to $DBversion done (added ceilingDueDate system preference)\n";
2358 SetVersion ($DBversion);
2361 $DBversion = '3.01.00.026';
2362 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2363 $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')");
2365 print "Upgrade to $DBversion done (added numReturnedItemsToShow system preference)\n";
2366 SetVersion ($DBversion);
2369 $DBversion = '3.01.00.027';
2370 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2371 $dbh->do("ALTER TABLE zebraqueue CHANGE `biblio_auth_number` `biblio_auth_number` bigint(20) unsigned NOT NULL default 0");
2372 print "Upgrade to $DBversion done (Increased size of zebraqueue biblio_auth_number to address bug 3148.)\n";
2373 SetVersion ($DBversion);
2376 $DBversion = '3.01.00.028';
2377 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2378 my $enable_reviews = C4::Context->preference('AmazonEnabled') ? '1' : '0';
2379 $dbh->do("INSERT INTO systempreferences (variable,value,explanation,options,type) VALUES ('AmazonReviews', '$enable_reviews', 'Display Amazon reviews on staff interface','','YesNo')");
2380 print "Upgrade to $DBversion done (added AmazonReviews)\n";
2381 SetVersion ($DBversion);
2384 $DBversion = '3.01.00.029';
2385 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2386 $dbh->do(q( UPDATE language_rfc4646_to_iso639
2387 SET iso639_2_code = 'spa'
2388 WHERE rfc4646_subtag = 'es'
2389 AND iso639_2_code = 'rus' )
2391 print "Upgrade to $DBversion done (fixed bug 2599: using Spanish search limit retrieves Russian results)\n";
2392 SetVersion ($DBversion);
2395 $DBversion = "3.01.00.030";
2396 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2397 $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')");
2398 print "Upgrade to $DBversion done (added AllowNotForLoanOverride system preference)\n";
2399 SetVersion ($DBversion);
2402 $DBversion = "3.01.00.031";
2403 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2404 $dbh->do("ALTER TABLE branch_transfer_limits
2405 MODIFY toBranch varchar(10) NOT NULL,
2406 MODIFY fromBranch varchar(10) NOT NULL,
2407 MODIFY itemtype varchar(10) NULL");
2408 print "Upgrade to $DBversion done (fix column widths in branch_transfer_limits)\n";
2409 SetVersion ($DBversion);
2412 $DBversion = "3.01.00.032";
2413 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2414 $dbh->do(<<ENDOFRENEWAL);
2415 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');
2417 print "Upgrade to $DBversion done (Change the field)\n";
2418 SetVersion ($DBversion);
2421 $DBversion = "3.01.00.033";
2422 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2424 ALTER TABLE borrower_message_preferences
2425 MODIFY borrowernumber int(11) default NULL,
2426 ADD categorycode varchar(10) default NULL AFTER borrowernumber,
2427 ADD KEY `categorycode` (`categorycode`),
2428 ADD CONSTRAINT `borrower_message_preferences_ibfk_3`
2429 FOREIGN KEY (`categorycode`) REFERENCES `categories` (`categorycode`)
2430 ON DELETE CASCADE ON UPDATE CASCADE
2432 print "Upgrade to $DBversion done (DB changes to allow patron category defaults for messaging preferences)\n";
2433 SetVersion ($DBversion);
2436 $DBversion = "3.01.00.034";
2437 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2438 $dbh->do("ALTER TABLE `subscription` ADD COLUMN `graceperiod` INT(11) NOT NULL default '0';");
2439 print "Upgrade to $DBversion done (Adding graceperiod column to subscription table)\n";
2440 SetVersion ($DBversion);
2443 $DBversion = '3.01.00.035';
2444 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2445 $dbh->do(q{ ALTER TABLE `subscription` ADD location varchar(80) NULL DEFAULT '' AFTER callnumber; });
2446 print "Upgrade to $DBversion done (Adding location to subscription table)\n";
2447 SetVersion ($DBversion);
2450 $DBversion = '3.01.00.036';
2451 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2452 $dbh->do("UPDATE systempreferences SET explanation = 'Choose the default detail view in the staff interface; choose between normal, labeled_marc, marc or isbd'
2453 WHERE variable = 'IntranetBiblioDefaultView'
2454 AND explanation = 'IntranetBiblioDefaultView'");
2455 $dbh->do("UPDATE systempreferences SET type = 'Choice', options = 'normal|marc|isbd|labeled_marc'
2456 WHERE variable = 'IntranetBiblioDefaultView'");
2457 $dbh->do("INSERT INTO systempreferences (variable,value,explanation,options,type)VALUES('viewISBD','1','Allow display of ISBD view of bibiographic records','','YesNo')");
2458 $dbh->do("INSERT INTO systempreferences (variable,value,explanation,options,type)VALUES('viewLabeledMARC','0','Allow display of labeled MARC view of bibiographic records','','YesNo')");
2459 $dbh->do("INSERT INTO systempreferences (variable,value,explanation,options,type)VALUES('viewMARC','1','Allow display of MARC view of bibiographic records','','YesNo')");
2460 print "Upgrade to $DBversion done (new viewISBD, viewLabeledMARC, viewMARC sysprefs and tweak IntranetBiblioDefaultView)\n";
2461 SetVersion ($DBversion);
2464 $DBversion = '3.01.00.037';
2465 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2466 $dbh->do('ALTER TABLE authorised_values ADD KEY `lib` (`lib`)');
2467 $dbh->do("INSERT INTO systempreferences (variable,value,explanation,options,type)VALUES('FilterBeforeOverdueReport','0','Do not run overdue report until filter selected','','YesNo')");
2468 SetVersion ($DBversion);
2469 print "Upgrade to $DBversion done (added FilterBeforeOverdueReport syspref and new index on authorised_values)\n";
2472 $DBversion = "3.01.00.038";
2473 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2474 # update branches table
2476 $dbh->do("ALTER TABLE branches ADD `branchzip` varchar(25) default NULL AFTER `branchaddress3`");
2477 $dbh->do("ALTER TABLE branches ADD `branchcity` mediumtext AFTER `branchzip`");
2478 $dbh->do("ALTER TABLE branches ADD `branchcountry` text AFTER `branchcity`");
2479 $dbh->do("ALTER TABLE branches ADD `branchurl` mediumtext AFTER `branchemail`");
2480 $dbh->do("ALTER TABLE branches ADD `branchnotes` mediumtext AFTER `branchprinter`");
2481 print "Upgrade to $DBversion done (add ZIP, city, country, URL, and notes column to branches)\n";
2482 SetVersion ($DBversion);
2485 $DBversion = '3.01.00.039';
2486 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2487 $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')");
2488 $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')");
2489 SetVersion ($DBversion);
2490 print "Upgrade to $DBversion done (added SpineLabelFormat and SpineLabelAutoPrint sysprefs)\n";
2493 $DBversion = '3.01.00.040';
2494 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2495 $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')");
2496 $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')");
2497 SetVersion ($DBversion);
2498 print "Upgrade to $DBversion done (AllowHoldDateInFuture and OPACAllowHoldDateInFuture sysprefs)\n";
2501 $DBversion = '3.01.00.041';
2502 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2503 $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')");
2504 SetVersion ($DBversion);
2505 print "Upgrade to $DBversion done (added AWSPrivateKey syspref - note that if you use enhanced content from Amazon, this should be set right away.)\n";
2508 $DBversion = '3.01.00.042';
2509 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2510 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('OPACFineNoRenewals','99999','Fine Limit above which user canmot renew books via OPAC','','Integer')");
2511 SetVersion ($DBversion);
2512 print "Upgrade to $DBversion done (added OPACFineNoRenewals syspref)\n";
2515 $DBversion = '3.01.00.043';
2516 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2517 $dbh->do('ALTER TABLE items ADD COLUMN permanent_location VARCHAR(80) DEFAULT NULL AFTER location');
2518 $dbh->do('UPDATE items SET permanent_location = location');
2519 $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 )', '')");
2520 $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')");
2521 $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')");
2522 SetVersion ($DBversion);
2523 print "Upgrade to $DBversion done (amended Item added NewItemsDefaultLocation, InProcessingToShelvingCart, ReturnToShelvingCart sysprefs)\n";
2526 $DBversion = '3.01.00.044';
2527 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2528 $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')");
2529 SetVersion ($DBversion);
2530 print "Upgrade to $DBversion done (added DisplayClearScreenButton system preference)\n";
2533 $DBversion = '3.01.00.045';
2534 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2535 $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')");
2536 SetVersion ($DBversion);
2537 print "Upgrade to $DBversion done (added a preference to hide the patrons name in the staff catalog)";
2540 $DBversion = "3.01.00.046";
2541 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2542 # update borrowers table
2544 $dbh->do("ALTER TABLE borrowers ADD `country` text AFTER zipcode");
2545 $dbh->do("ALTER TABLE borrowers ADD `B_country` text AFTER B_zipcode");
2546 $dbh->do("ALTER TABLE deletedborrowers ADD `country` text AFTER zipcode");
2547 $dbh->do("ALTER TABLE deletedborrowers ADD `B_country` text AFTER B_zipcode");
2548 print "Upgrade to $DBversion done (add country and B_country to borrowers)\n";
2549 SetVersion ($DBversion);
2552 $DBversion = '3.01.00.047';
2553 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2554 $dbh->do("ALTER TABLE items MODIFY itemcallnumber varchar(255);");
2555 $dbh->do("ALTER TABLE deleteditems MODIFY itemcallnumber varchar(255);");
2556 $dbh->do("ALTER TABLE tmp_holdsqueue MODIFY itemcallnumber varchar(255);");
2557 SetVersion ($DBversion);
2558 print " Upgrade to $DBversion done (bug 2761: change max length of itemcallnumber to 255 from 30)\n";
2561 $DBversion = '3.01.00.048';
2562 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2563 $dbh->do("UPDATE userflags SET flagdesc='View Catalog (Librarian Interface)' WHERE bit=2;");
2564 $dbh->do("UPDATE userflags SET flagdesc='Edit Catalog (Modify bibliographic/holdings data)' WHERE bit=9;");
2565 $dbh->do("UPDATE userflags SET flagdesc='Allow to edit authorities' WHERE bit=14;");
2566 $dbh->do("UPDATE userflags SET flagdesc='Allow to access to the reports module' WHERE bit=16;");
2567 $dbh->do("UPDATE userflags SET flagdesc='Allow to manage serials subscriptions' WHERE bit=15;");
2568 SetVersion ($DBversion);
2569 print " Upgrade to $DBversion done (bug 2611: fix spelling/capitalization in permission flag descriptions)\n";
2572 $DBversion = '3.01.00.049';
2573 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2574 $dbh->do("UPDATE permissions SET description = 'Perform inventory (stocktaking) of your catalog' WHERE code = 'inventory';");
2575 SetVersion ($DBversion);
2576 print "Upgrade to $DBversion done (bug 2611: changed catalogue to catalog per the standard)\n";
2579 $DBversion = '3.01.00.050';
2580 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2581 $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');");
2582 SetVersion ($DBversion);
2583 print "Upgrade to $DBversion done (bug 1934: Add OPACSearchForTitleIn syspref)\n";
2586 $DBversion = '3.01.00.051';
2587 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2588 $dbh->do("UPDATE systempreferences SET explanation='Fine limit above which user cannot renew books via OPAC' WHERE variable='OPACFineNoRenewals';");
2589 $dbh->do("UPDATE systempreferences SET explanation='If set to ON, a clear screen button will appear on the circulation page.' WHERE variable='DisplayClearScreenButton';");
2590 SetVersion ($DBversion);
2591 print "Upgrade to $DBversion done (fixed typos in new sysprefs)\n";
2594 $DBversion = '3.01.00.052';
2595 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2596 $dbh->do('ALTER TABLE deleteditems ADD COLUMN permanent_location VARCHAR(80) DEFAULT NULL AFTER location');
2597 SetVersion ($DBversion);
2598 print "Upgrade to $DBversion done (bug 3481: add permanent_location column to deleteditems)\n";
2601 $DBversion = '3.01.00.053';
2602 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2603 my $upgrade_script = C4::Context->config("intranetdir") . "/installer/data/mysql/labels_upgrade.pl";
2604 system("perl $upgrade_script");
2605 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";
2606 SetVersion ($DBversion);
2609 $DBversion = '3.01.00.054';
2610 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2611 $dbh->do("ALTER TABLE borrowers ADD `B_address2` text AFTER B_address");
2612 $dbh->do("ALTER TABLE borrowers ADD `altcontactcountry` text AFTER altcontactzipcode");
2613 $dbh->do("ALTER TABLE deletedborrowers ADD `B_address2` text AFTER B_address");
2614 $dbh->do("ALTER TABLE deletedborrowers ADD `altcontactcountry` text AFTER altcontactzipcode");
2615 SetVersion ($DBversion);
2616 print "Upgrade to $DBversion done (bug 1600, bug 3454: add altcontactcountry and B_address2 to borrowers and deletedborrowers)\n";
2619 $DBversion = '3.01.00.055';
2620 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2621 $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'|);
2622 SetVersion ($DBversion);
2623 print "Upgrade to $DBversion done (changed OPACSearchForTitleIn per requests in bug 1934)\n";
2626 $DBversion = '3.01.00.056';
2627 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2628 $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');");
2629 SetVersion ($DBversion);
2630 print "Upgrade to $DBversion done (Bug 1172 : Add OPACPatronDetails syspref)\n";
2633 $DBversion = '3.01.00.057';
2634 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2635 $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');");
2636 SetVersion ($DBversion);
2637 print "Upgrade to $DBversion done (Bug 2576 : Add OPACFinesTab syspref)\n";
2640 $DBversion = '3.01.00.058';
2641 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2642 $dbh->do("ALTER TABLE `language_subtag_registry` ADD `id` INT( 11 ) NOT NULL AUTO_INCREMENT PRIMARY KEY;");
2643 $dbh->do("ALTER TABLE `language_rfc4646_to_iso639` ADD `id` INT( 11 ) NOT NULL AUTO_INCREMENT PRIMARY KEY;");
2644 $dbh->do("ALTER TABLE `language_descriptions` ADD `id` INT( 11 ) NOT NULL AUTO_INCREMENT PRIMARY KEY;");
2645 SetVersion ($DBversion);
2646 print "Upgrade to $DBversion done (Added primary keys to language tables)\n";
2649 $DBversion = '3.01.00.059';
2650 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2651 $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')");
2652 SetVersion ($DBversion);
2653 print "Upgrade to $DBversion done (added DisplayOPACiconsXSLT sysprefs)\n";
2656 $DBversion = '3.01.00.060';
2657 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2658 $dbh->do("INSERT INTO systempreferences (variable,value,explanation,options,type) VALUES('AllowAllMessageDeletion','0','Allow any Library to delete any message','','YesNo');");
2659 $dbh->do('DROP TABLE IF EXISTS messages');
2660 $dbh->do("CREATE TABLE messages ( `message_id` int(11) NOT NULL auto_increment,
2661 `borrowernumber` int(11) NOT NULL,
2662 `branchcode` varchar(4) default NULL,
2663 `message_type` varchar(1) NOT NULL,
2664 `message` text NOT NULL,
2665 `message_date` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
2666 PRIMARY KEY (`message_id`)
2667 ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
2669 print "Upgrade to $DBversion done ( Added AllowAllMessageDeletion syspref and messages table )\n";
2670 SetVersion ($DBversion);
2673 $DBversion = '3.01.00.061';
2674 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2675 $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')");
2676 print "Upgrade to $DBversion done ( Added ShowPatronImageInWebBasedSelfCheck system preference )\n";
2677 SetVersion ($DBversion);
2680 $DBversion = "3.01.00.062";
2681 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2682 $dbh->do("INSERT INTO permissions (module_bit, code, description) VALUES ( 13, 'manage_csv_profiles', 'Manage CSV export profiles')");
2684 CREATE TABLE `export_format` (
2685 `export_format_id` int(11) NOT NULL auto_increment,
2686 `profile` varchar(255) NOT NULL,
2687 `description` mediumtext NOT NULL,
2688 `marcfields` mediumtext NOT NULL,
2689 PRIMARY KEY (`export_format_id`)
2690 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Used for CSV export';
2692 print "Upgrade to $DBversion done (added csv export profiles)\n";
2695 $DBversion = "3.01.00.063";
2696 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2698 CREATE TABLE `fieldmapping` (
2699 `id` int(11) NOT NULL auto_increment,
2700 `field` varchar(255) NOT NULL,
2701 `frameworkcode` char(4) NOT NULL default '',
2702 `fieldcode` char(3) NOT NULL,
2703 `subfieldcode` char(1) NOT NULL,
2705 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2707 SetVersion ($DBversion);
2710 $DBversion = '3.01.00.065';
2711 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2712 $dbh->do('ALTER TABLE issuingrules ADD COLUMN `renewalsallowed` smallint(6) NOT NULL default "0" AFTER `issuelength`;');
2713 $sth = $dbh->prepare("SELECT itemtype, renewalsallowed FROM itemtypes");
2716 my $sthupd = $dbh->prepare("UPDATE issuingrules SET renewalsallowed = ? WHERE itemtype = ?");
2718 while(my $row = $sth->fetchrow_hashref){
2719 $sthupd->execute($row->{renewalsallowed}, $row->{itemtype});
2722 $dbh->do('ALTER TABLE itemtypes DROP COLUMN `renewalsallowed`;');
2724 SetVersion ($DBversion);
2725 print "Upgrade to $DBversion done (Moving allowed renewals from itemtypes to issuingrule)\n";
2728 $DBversion = '3.01.00.066';
2729 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2730 $dbh->do('ALTER TABLE issuingrules ADD COLUMN `reservesallowed` smallint(6) NOT NULL default "0" AFTER `renewalsallowed`;');
2732 my $maxreserves = C4::Context->preference('maxreserves');
2733 $sth = $dbh->prepare('UPDATE issuingrules SET reservesallowed = ?;');
2734 $sth->execute($maxreserves);
2736 $dbh->do('DELETE FROM systempreferences WHERE variable = "maxreserves";');
2738 $dbh->do("INSERT INTO systempreferences (variable,value, options, explanation, type) VALUES('ReservesControlBranch','PatronLibrary','ItemHomeLibrary|PatronLibrary','Branch checked for members reservations rights','Choice')");
2740 SetVersion ($DBversion);
2741 print "Upgrade to $DBversion done (Moving max allowed reserves from system preference to issuingrule)\n";
2744 $DBversion = "3.01.00.067";
2745 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2746 $dbh->do("INSERT INTO permissions (module_bit, code, description) VALUES ( 13, 'batchmod', 'Perform batch modification of items')");
2747 $dbh->do("INSERT INTO permissions (module_bit, code, description) VALUES ( 13, 'batchdel', 'Perform batch deletion of items')");
2748 print "Upgrade to $DBversion done (added permissions for batch modification and deletion)\n";
2749 SetVersion ($DBversion);
2752 $DBversion = "3.01.00.068";
2753 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2754 $dbh->do("ALTER TABLE issuingrules ADD COLUMN `finedays` int(11) default NULL AFTER `fine` ");
2755 print "Upgrade done (Adding finedays in issuingrules table)\n";
2756 SetVersion ($DBversion);
2760 $DBversion = "3.01.00.069";
2761 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2762 $dbh->do("INSERT INTO `systempreferences` (`variable`, `value`, `options`, `explanation`, `type`) VALUES ('EnableOpacSearchHistory', '1', '', 'Enable or disable opac search history', 'YesNo')");
2764 my $create = <<SEARCHHIST;
2765 CREATE TABLE IF NOT EXISTS `search_history` (
2766 `userid` int(11) NOT NULL,
2767 `sessionid` varchar(32) NOT NULL,
2768 `query_desc` varchar(255) NOT NULL,
2769 `query_cgi` varchar(255) NOT NULL,
2770 `total` int(11) NOT NULL,
2771 `time` timestamp NOT NULL default CURRENT_TIMESTAMP,
2772 KEY `userid` (`userid`),
2773 KEY `sessionid` (`sessionid`)
2774 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Opac search history results';
2778 print "Upgrade done (added OPAC search history preference and table)\n";
2781 $DBversion = "3.01.00.070";
2782 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2783 $dbh->do("ALTER TABLE authorised_values ADD COLUMN `lib_opac` VARCHAR(80) default NULL AFTER `lib`");
2784 print "Upgrade done (Added a lib_opac field in authorised_values table)\n";
2787 $DBversion = "3.01.00.071";
2788 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2789 $dbh->do("ALTER TABLE `subscription` ADD `enddate` date default NULL");
2790 $dbh->do("ALTER TABLE subscriptionhistory CHANGE enddate histenddate DATE default NULL");
2791 print "Upgrade to $DBversion done ( Adding enddate to subscription)\n";
2800 $DBversion = "3.01.00.072";
2801 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2802 $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')");
2803 # create a new syspref for the 'Mr anonymous' patron
2804 $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,'')");
2805 # fill AnonymousPatron with AnonymousSuggestion value (copy)
2806 my $sth=$dbh->prepare("SELECT value FROM systempreferences WHERE variable='AnonSuggestions'");
2808 my ($value) = $sth->fetchrow() || 0;
2809 $dbh->do("UPDATE systempreferences SET value='$value' WHERE variable='AnonymousPatron'");
2810 # set AnonymousSuggestion do YesNo
2811 # 1st, set the value (1/True if it had a borrowernumber)
2812 $dbh->do("UPDATE systempreferences SET value=1 WHERE variable='AnonSuggestions' AND value>0");
2813 # 2nd, change the type to Choice
2814 $dbh->do("UPDATE systempreferences SET type='YesNo' WHERE variable='AnonSuggestions'");
2815 # borrower reading record privacy : 0 : forever, 1 : laws, 2 : don't keep at all
2816 $dbh->do("ALTER TABLE `borrowers` ADD `privacy` INTEGER NOT NULL DEFAULT 1;");
2817 print "Upgrade to $DBversion done (add new syspref and column in borrowers)\n";
2818 SetVersion ($DBversion);
2821 $DBversion = '3.01.00.073';
2822 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2823 $dbh->do(<<'END_SQL');
2824 CREATE TABLE IF NOT EXISTS `aqcontract` (
2825 `contractnumber` int(11) NOT NULL auto_increment,
2826 `contractstartdate` date default NULL,
2827 `contractenddate` date default NULL,
2828 `contractname` varchar(50) default NULL,
2829 `contractdescription` mediumtext,
2830 `booksellerid` int(11) not NULL,
2831 PRIMARY KEY (`contractnumber`),
2832 CONSTRAINT `booksellerid_fk1` FOREIGN KEY (`booksellerid`)
2833 REFERENCES `aqbooksellers` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
2834 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
2836 print "Upgrade to $DBversion done (adding aqcontract table)\n";
2837 SetVersion ($DBversion);
2840 $DBversion = '3.01.00.074';
2841 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2842 $dbh->do("ALTER TABLE `aqbasket` ADD COLUMN `basketname` varchar(50) default NULL AFTER `basketno`");
2843 $dbh->do("ALTER TABLE `aqbasket` ADD COLUMN `note` mediumtext AFTER `basketname`");
2844 $dbh->do("ALTER TABLE `aqbasket` ADD COLUMN `booksellernote` mediumtext AFTER `note`");
2845 $dbh->do("ALTER TABLE `aqbasket` ADD COLUMN `contractnumber` int(11) AFTER `booksellernote`");
2846 $dbh->do("ALTER TABLE `aqbasket` ADD FOREIGN KEY (`contractnumber`) REFERENCES `aqcontract` (`contractnumber`)");
2847 print "Upgrade to $DBversion done (edit aqbasket table done)\n";
2848 SetVersion ($DBversion);
2851 $DBversion = '3.01.00.075';
2852 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2853 $dbh->do("ALTER TABLE `aqorders` ADD COLUMN `uncertainprice` tinyint(1)");
2855 print "Upgrade to $DBversion done (adding uncertainprices)\n";
2856 SetVersion ($DBversion);
2859 $DBversion = '3.01.00.076';
2860 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2861 $dbh->do("CREATE TABLE IF NOT EXISTS `aqbasketgroups` (
2862 `id` int(11) NOT NULL auto_increment,
2863 `name` varchar(50) default NULL,
2864 `closed` tinyint(1) default NULL,
2865 `booksellerid` int(11) NOT NULL,
2867 KEY `booksellerid` (`booksellerid`),
2868 CONSTRAINT `aqbasketgroups_ibfk_1` FOREIGN KEY (`booksellerid`) REFERENCES `aqbooksellers` (`id`) ON UPDATE CASCADE ON DELETE CASCADE
2869 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;");
2870 $dbh->do("ALTER TABLE aqbasket ADD COLUMN `basketgroupid` int(11)");
2871 $dbh->do("ALTER TABLE aqbasket ADD FOREIGN KEY (`basketgroupid`) REFERENCES `aqbasketgroups` (`id`) ON UPDATE CASCADE ON DELETE SET NULL");
2872 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES ('pdfformat','pdfformat::example','Controls what script is used for printing (basketgroups)','','free')");
2873 print "Upgrade to $DBversion done (adding basketgroups)\n";
2874 SetVersion ($DBversion);
2877 $DBversion = '3.01.00.077';
2878 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2879 $dbh->do("DROP TABLE IF EXISTS `aqbudgetperiods` ");
2881 CREATE TABLE `aqbudgetperiods` (
2882 `budget_period_id` int(11) NOT NULL auto_increment,
2883 `budget_period_startdate` date NOT NULL,
2884 `budget_period_enddate` date NOT NULL,
2885 `budget_period_active` tinyint(1) default '0',
2886 `budget_period_description` mediumtext,
2887 `budget_period_locked` tinyint(1) default NULL,
2888 `sort1_authcat` varchar(10) default NULL,
2889 `sort2_authcat` varchar(10) default NULL,
2890 PRIMARY KEY (`budget_period_id`)
2891 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 |);
2893 $dbh->do(<<ADDPERIODS);
2894 INSERT INTO aqbudgetperiods (budget_period_startdate,budget_period_enddate,budget_period_active,budget_period_description,budget_period_locked)
2895 SELECT DISTINCT startdate, enddate, 1, concat(startdate," ",enddate),1 from aqbudget
2897 # SORRY , NO AQBUDGET/AQBOOKFUND -> AQBUDGETS IMPORT JUST YET,
2898 # BUT A NEW CLEAN AQBUDGETS TABLE CREATE FOR NOW..
2899 # DROP TABLE IF EXISTS `aqbudget`;
2900 #CREATE TABLE `aqbudget` (
2901 # `bookfundid` varchar(10) NOT NULL default ',
2902 # `startdate` date NOT NULL default 0,
2903 # `enddate` date default NULL,
2904 # `budgetamount` decimal(13,2) default NULL,
2905 # `aqbudgetid` tinyint(4) NOT NULL auto_increment,
2906 # `branchcode` varchar(10) default NULL,
2907 DropAllForeignKeys('aqbudget');
2908 #$dbh->do("drop table aqbudget;");
2911 $dbh->do(<<BUDGETNAME);
2912 ALTER TABLE aqbudget RENAME `aqbudgets`
2914 my $maxbudgetid=$dbh->selectcol_arrayref(<<IDsBUDGET);
2915 SELECT MAX(aqbudgetid) from aqbudgets
2918 $dbh->do(<<BUDGETAUTOINCREMENT);
2919 ALTER TABLE `aqbudgets` AUTO_INCREMENT=$$maxbudgetid[0]
2922 $dbh->do(<<BUDGETS);
2923 ALTER TABLE `aqbudgets`
2924 CHANGE COLUMN aqbudgetid `budget_id` int(11) NOT NULL AUTO_INCREMENT,
2925 CHANGE COLUMN branchcode `budget_branchcode` varchar(10) default NULL,
2926 CHANGE COLUMN budgetamount `budget_amount` decimal(28,6) NOT NULL default '0.00',
2927 CHANGE COLUMN bookfundid `budget_code` varchar(30) default NULL,
2928 ADD COLUMN `budget_parent_id` int(11) default NULL,
2929 ADD COLUMN `budget_name` varchar(80) default NULL,
2930 ADD COLUMN `budget_encumb` decimal(28,6) default '0.00',
2931 ADD COLUMN `budget_expend` decimal(28,6) default '0.00',
2932 ADD COLUMN `budget_notes` mediumtext,
2933 ADD COLUMN `budget_description` mediumtext,
2934 ADD COLUMN `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
2935 ADD COLUMN `budget_amount_sublevel` decimal(28,6) AFTER `budget_amount`,
2936 ADD COLUMN `budget_period_id` int(11) default NULL,
2937 ADD COLUMN `sort1_authcat` varchar(80) default NULL,
2938 ADD COLUMN `sort2_authcat` varchar(80) default NULL,
2939 ADD COLUMN `budget_owner_id` int(11) default NULL,
2940 ADD COLUMN `budget_permission` int(1) default '0';
2943 $dbh->do(<<BUDGETCONSTRAINTS);
2944 ALTER TABLE `aqbudgets`
2945 ADD CONSTRAINT `aqbudgets_ifbk_1` FOREIGN KEY (`budget_period_id`) REFERENCES `aqbudgetperiods` (`budget_period_id`) ON DELETE CASCADE ON UPDATE CASCADE
2947 # $dbh->do(<<BUDGETPKDROP);
2948 #ALTER TABLE `aqbudgets`
2951 # $dbh->do(<<BUDGETPKADD);
2952 #ALTER TABLE `aqbudgets`
2953 # ADD PRIMARY KEY budget_id
2957 my $query_period= $dbh->prepare(qq|SELECT budget_period_id from aqbudgetperiods where budget_period_startdate=? and budget_period_enddate=?|);
2958 my $query_bookfund= $dbh->prepare(qq|SELECT * from aqbookfund where bookfundid=?|);
2959 my $selectbudgets=$dbh->prepare(qq|SELECT * from aqbudgets|);
2960 my $updatebudgets=$dbh->prepare(qq|UPDATE aqbudgets SET budget_period_id= ? , budget_name=?, budget_branchcode=? where budget_id=?|);
2961 $selectbudgets->execute;
2962 while (my $databudget=$selectbudgets->fetchrow_hashref){
2963 $query_period->execute ($$databudget{startdate},$$databudget{enddate});
2964 my ($budgetperiodid)=$query_period->fetchrow;
2965 $query_bookfund->execute ($$databudget{budget_code});
2966 my $databf=$query_bookfund->fetchrow_hashref;
2967 my $branchcode=$$databudget{budget_branchcode}||$$databf{branchcode};
2968 $updatebudgets->execute($budgetperiodid,$$databf{bookfundname},$branchcode,$$databudget{budget_id});
2970 $dbh->do(<<BUDGETDROPDATES);
2971 ALTER TABLE `aqbudgets`
2977 $dbh->do("DROP TABLE IF EXISTS `aqbudgets_planning` ");
2978 $dbh->do("CREATE TABLE `aqbudgets_planning` (
2979 `plan_id` int(11) NOT NULL auto_increment,
2980 `budget_id` int(11) NOT NULL,
2981 `budget_period_id` int(11) NOT NULL,
2982 `estimated_amount` decimal(28,6) default NULL,
2983 `authcat` varchar(30) NOT NULL,
2984 `authvalue` varchar(30) NOT NULL,
2985 `display` tinyint(1) DEFAULT 1,
2986 PRIMARY KEY (`plan_id`),
2987 CONSTRAINT `aqbudgets_planning_ifbk_1` FOREIGN KEY (`budget_id`) REFERENCES `aqbudgets` (`budget_id`) ON DELETE CASCADE ON UPDATE CASCADE
2988 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;");
2990 $dbh->do("ALTER TABLE `aqorders`
2991 ADD COLUMN `budget_id` tinyint(4) NOT NULL,
2992 ADD COLUMN `budgetgroup_id` int(11) NOT NULL,
2993 ADD COLUMN `sort1_authcat` varchar(10) default NULL,
2994 ADD COLUMN `sort2_authcat` varchar(10) default NULL" );
2996 # cannot do until aqorderbreakdown removed
2997 # $dbh->do("DROP TABLE aqbookfund ");
3001 # $dbh->do("ALTER TABLE aqorders ADD FOREIGN KEY (`budget_id`) REFERENCES `aqbudgets` (`budget_id`) ON UPDATE CASCADE " ); ????
3003 print "Upgrade to $DBversion done (Adding new aqbudgetperiods, aqbudgets and aqbudget_planning tables )\n";
3004 SetVersion ($DBversion);
3009 $DBversion = '3.01.00.078';
3010 if ( C4::Context->preference("Version") < TransformToNum($DBversion) ) {
3011 $dbh->do("ALTER TABLE aqbudgetperiods ADD COLUMN budget_period_total decimal(28,6)");
3012 print "Upgrade to $DBversion done (adds 'budget_period_total' column to aqbudgetperiods table)\n";
3013 SetVersion($DBversion);
3017 $DBversion = '3.01.00.079';
3018 if ( C4::Context->preference("Version") < TransformToNum($DBversion) ) {
3019 $dbh->do("ALTER TABLE currency ADD COLUMN active tinyint(1)");
3021 print "Upgrade to $DBversion done (adds 'active' column to currencies table)\n";
3022 SetVersion($DBversion);
3025 $DBversion = '3.01.00.080';
3026 if ( C4::Context->preference("Version") < TransformToNum($DBversion) ) {
3027 $dbh->do(<<BUDG_PERM );
3028 INSERT INTO permissions (module_bit, code, description) VALUES
3029 (11, 'vendors_manage', 'Manage vendors'),
3030 (11, 'contracts_manage', 'Manage contracts'),
3031 (11, 'period_manage', 'Manage periods'),
3032 (11, 'budget_manage', 'Manage budgets'),
3033 (11, 'budget_modify', "Modify budget (can't create lines but can modify existing ones)"),
3034 (11, 'planning_manage', 'Manage budget plannings'),
3035 (11, 'order_manage', 'Manage orders & basket'),
3036 (11, 'group_manage', 'Manage orders & basketgroups'),
3037 (11, 'order_receive', 'Manage orders & basket'),
3038 (11, 'budget_add_del', "Add and delete budgets (but can't modify budgets)");
3041 print "Upgrade to $DBversion done (adds permissions for the acquisitions module)\n";
3042 SetVersion($DBversion);
3046 $DBversion = '3.01.00.081';
3047 if ( C4::Context->preference("Version") < TransformToNum($DBversion) ) {
3048 $dbh->do("ALTER TABLE aqbooksellers ADD COLUMN `gstrate` decimal(6,4) default NULL");
3049 if (my $gist=C4::Context->preference("gist")){
3050 my $sql=$dbh->prepare("UPDATE aqbooksellers set `gstrate`=? ");
3051 $sql->execute($gist) ;
3053 print "Upgrade to $DBversion done (added per-supplier gstrate setting)\n";
3054 SetVersion($DBversion);
3057 $DBversion = "3.01.00.082";
3058 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
3059 if (C4::Context->preference("opaclanguages") eq "fr") {
3060 $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')#);
3062 $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')");
3064 print "Upgrade to $DBversion done (adding ReservesNeedReturns systempref, in circulation)\n";
3065 SetVersion ($DBversion);
3068 $DBversion = "3.01.00.083";
3069 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
3071 CREATE TABLE `aqorders_items` (
3072 `ordernumber` int(11) NOT NULL,
3073 `itemnumber` int(11) NOT NULL,
3074 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
3075 PRIMARY KEY (`itemnumber`),
3076 KEY `ordernumber` (`ordernumber`)
3077 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
3080 $dbh->do(qq| DROP TABLE aqorderbreakdown |);
3081 $dbh->do('DROP TABLE aqbookfund');
3082 print "Upgrade to $DBversion done (New aqorders_items table for acqui)\n";
3083 SetVersion ($DBversion);
3086 $DBversion = "3.01.00.084";
3087 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
3088 $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') #);
3090 print "Upgrade to $DBversion done (CurrencyFormat syspref added)\n";
3091 SetVersion ($DBversion);
3094 $DBversion = "3.01.00.085";
3095 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
3096 $dbh->do("ALTER table aqorders drop column title");
3097 $dbh->do("ALTER TABLE `aqorders` CHANGE `budget_id` `budget_id` INT( 11 ) NOT NULL");
3098 print "Upgrade to $DBversion done update budget_id size that should not be a tinyint\n";
3099 SetVersion ($DBversion);
3102 $DBversion = "3.01.00.086";
3103 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
3104 $dbh->do(<<SUGGESTIONS);
3105 ALTER table suggestions
3106 ADD budgetid INT(11),
3107 ADD branchcode VARCHAR(10) default NULL,
3108 ADD acceptedby INT(11) default NULL,
3109 ADD accepteddate date default NULL,
3110 ADD suggesteddate date default NULL,
3111 ADD manageddate date default NULL,
3112 ADD rejectedby INT(11) default NULL,
3113 ADD rejecteddate date default NULL,
3114 ADD collectiontitle text default NULL,
3115 ADD itemtype VARCHAR(30) default NULL
3118 print "Upgrade to $DBversion done Suggestions";
3119 SetVersion ($DBversion);
3122 $DBversion = "3.01.00.087";
3123 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
3124 $dbh->do("ALTER table aqbudgets drop column budget_amount_sublevel;");
3125 print "Upgrade to $DBversion done drop column budget_amount_sublevel from aqbudgets\n";
3126 SetVersion ($DBversion);
3129 $DBversion = "3.01.00.088";
3130 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
3131 $dbh->do( qq# INSERT INTO `systempreferences` VALUES ('intranetbookbag','1','','If ON, enables display of Cart feature in the intranet','YesNo') #);
3133 print "Upgrade to $DBversion done (intranetbookbag syspref added)\n";
3134 SetVersion ($DBversion);
3137 $DBversion = "3.01.00.090";
3138 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
3140 INSERT INTO `permissions` (`module_bit`, `code`, `description`) VALUES
3141 (16, 'execute_reports', 'Execute SQL reports'),
3142 (16, 'create_reports', 'Create SQL Reports')
3145 print "Upgrade to $DBversion done (granular permissions for guided reports added)\n";
3146 SetVersion ($DBversion);
3149 $DBversion = "3.01.00.091";
3150 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
3152 UPDATE `systempreferences` SET `options` = 'holdings|serialcollection|subscriptions'
3153 WHERE `systempreferences`.`variable` = 'opacSerialDefaultTab' LIMIT 1
3156 print "Upgrade to $DBversion done (opac-detail default tag updated)\n";
3157 SetVersion ($DBversion);
3160 $DBversion = "3.01.00.092";
3161 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
3162 if (C4::Context->preference("opaclanguages") =~ /fr/) {
3164 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');
3168 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');
3171 print "Upgrade to $DBversion done (Added RoutingListAddReserves syspref)\n";
3172 SetVersion ($DBversion);
3175 $DBversion = "3.01.00.093";
3176 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
3178 ALTER TABLE biblioitems ADD INDEX issn_idx (issn);
3180 print "Upgrade to $DBversion done (added index to ISSN)\n";
3181 SetVersion ($DBversion);
3184 $DBversion = "3.01.00.094";
3185 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
3187 ALTER TABLE aqbasketgroups ADD deliveryplace VARCHAR(10) default NULL, ADD deliverycomment VARCHAR(255) default NULL;
3190 print "Upgrade to $DBversion done (adding deliveryplace deliverycomment to basketgroups)\n";
3191 SetVersion ($DBversion);
3194 $DBversion = "3.01.00.095";
3195 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
3197 ALTER TABLE items ADD stocknumber VARCHAR(32) DEFAULT NULL COMMENT "stores the inventory number";
3200 ALTER TABLE items ADD UNIQUE INDEX itemsstocknumberidx (stocknumber);
3203 ALTER TABLE deleteditems ADD stocknumber VARCHAR(32) DEFAULT NULL COMMENT "stores the inventory number of deleted items";
3206 ALTER TABLE deleteditems ADD UNIQUE INDEX deleteditemsstocknumberidx (stocknumber);
3208 if (C4::Context->preference('marcflavour') eq 'UNIMARC'){
3210 INSERT IGNORE INTO marc_subfield_structure (frameworkcode,tagfield, tagsubfield, tab, repeatable, mandatory,kohafield)
3211 SELECT DISTINCT (frameworkcode),995,"j",10,0,0,"items.stocknumber" from biblio_framework ;
3213 #Previously, copynumber was used as stocknumber
3215 UPDATE items set stocknumber=copynumber;
3218 UPDATE items set copynumber=NULL;
3221 print "Upgrade to $DBversion done (stocknumber field added)\n";
3222 SetVersion ($DBversion);
3225 $DBversion = "3.01.00.096";
3226 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
3227 $dbh->do("INSERT INTO systempreferences (variable,value,explanation,options,type) VALUES ('OrderPdfTemplate','','Uploads a PDF template to use for printing baskets','NULL','Upload')");
3228 $dbh->do("UPDATE systempreferences SET variable='OrderPdfFormat' WHERE variable='pdfformat'");
3229 print "Upgrade to $DBversion done (PDF orders system preferences added and updated)\n";
3230 SetVersion ($DBversion);
3233 $DBversion = "3.01.00.097";
3234 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
3236 ALTER TABLE aqbasketgroups ADD billingplace VARCHAR(10) NOT NULL AFTER deliverycomment;
3239 print "Upgrade to $DBversion done (Adding billingplace to aqbasketgroups)\n";
3240 SetVersion ($DBversion);
3243 $DBversion = "3.01.00.098";
3244 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
3246 ALTER TABLE auth_subfield_structure MODIFY frameworkcode VARCHAR(10) NULL;
3249 print "Upgrade to $DBversion done (changing frameworkcode length in auth_subfield_structure)\n";
3250 SetVersion ($DBversion);
3253 $DBversion = "3.01.00.099";
3254 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
3256 INSERT INTO `permissions` (`module_bit`, `code`, `description`) VALUES
3257 (9, 'edit_catalogue', 'Edit catalogue'),
3258 (9, 'fast_cataloging', 'Fast cataloging')
3261 print "Upgrade to $DBversion done (granular permissions for cataloging added)\n";
3262 SetVersion ($DBversion);
3265 $DBversion = "3.01.00.100";
3266 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
3267 $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')");
3268 print "Upgrade done (added CAS authentication system preferences)\n";
3269 SetVersion ($DBversion);
3272 $DBversion = "3.01.00.101";
3273 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
3275 "INSERT INTO systempreferences
3276 (variable, value, options, explanation, type)
3278 'OverdueNoticeBcc', '', '',
3279 'Email address to Bcc outgoing notices sent by email',
3282 print "Upgrade to $DBversion done (added OverdueNoticeBcc system preferences)\n";
3283 SetVersion ($DBversion);
3285 $DBversion = "3.01.00.102";
3286 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
3288 "UPDATE permissions set description = 'Edit catalog (Modify bibliographic/holdings data)' where module_bit = 9 and code = 'edit_catalogue'"
3290 print "Upgrade done (fixed spelling error in edit_catalogue permission)\n";
3291 SetVersion ($DBversion);
3294 $DBversion = "3.01.00.103";
3295 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
3296 $dbh->do("INSERT INTO permissions (module_bit, code, description) VALUES (13, 'moderate_tags', 'Moderate patron tags')");
3297 print "Upgrade done (adding patron permissions for tags tool)\n";
3298 SetVersion ($DBversion);
3301 $DBversion = "3.01.00.104";
3302 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
3304 my ($maninv_count, $borrnotes_count);
3305 eval { $maninv_count = $dbh->do("SELECT 1 FROM authorised_values WHERE category='MANUAL_INV'"); };
3306 if ($maninv_count == 0) {
3307 $dbh->do("INSERT INTO authorised_values (category,authorised_value,lib) VALUES ('MANUAL_INV','Copier Fees','.25')");
3309 eval { $borrnotes_count = $dbh->do("SELECT 1 FROM authorised_values WHERE category='BOR_NOTES'"); };
3310 if ($borrnotes_count == 0) {
3311 $dbh->do("INSERT INTO authorised_values (category,authorised_value,lib) VALUES ('BOR_NOTES','ADDR','Address Notes')");
3314 $dbh->do("INSERT INTO authorised_values (category,authorised_value,lib) VALUES ('LOC','CART','Book Cart')");
3315 $dbh->do("INSERT INTO authorised_values (category,authorised_value,lib) VALUES ('LOC','PROC','Processing Center')");
3317 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";
3318 SetVersion ($DBversion);
3322 $DBversion = "3.01.00.105";
3323 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
3325 CREATE TABLE `collections` (
3326 `colId` int(11) NOT NULL auto_increment,
3327 `colTitle` varchar(100) NOT NULL default '',
3328 `colDesc` text NOT NULL,
3329 `colBranchcode` varchar(4) default NULL COMMENT 'branchcode for branch where item should be held.',
3330 PRIMARY KEY (`colId`)
3331 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
3335 CREATE TABLE `collections_tracking` (
3336 `ctId` int(11) NOT NULL auto_increment,
3337 `colId` int(11) NOT NULL default '0' COMMENT 'collections.colId',
3338 `itemnumber` int(11) NOT NULL default '0' COMMENT 'items.itemnumber',
3339 PRIMARY KEY (`ctId`)
3340 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
3343 INSERT INTO permissions (module_bit, code, description)
3344 VALUES ( 13, 'rotating_collections', 'Manage Rotating collections')" );
3345 print "Upgrade to $DBversion done (added collection and collection_tracking tables for rotataing collection functionnality)\n";
3346 SetVersion ($DBversion);
3348 $DBversion = "3.01.00.106";
3349 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
3350 $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' )");
3351 print "Upgrade done (added OpacAddMastheadLibraryPulldown system preferences)\n";
3352 SetVersion ($DBversion);
3355 $DBversion = '3.01.00.107';
3356 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
3357 my $upgrade_script = C4::Context->config("intranetdir") . "/installer/data/mysql/patroncards_upgrade.pl";
3358 system("perl $upgrade_script");
3359 print "Upgrade to $DBversion done (Migrated labels and patroncards tables and data to new schema.)\n";
3360 SetVersion ($DBversion);
3363 $DBversion = '3.01.00.108';
3364 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
3366 ALTER TABLE `export_format` ADD `csv_separator` VARCHAR( 2 ) NOT NULL AFTER `marcfields` ,
3367 ADD `field_separator` VARCHAR( 2 ) NOT NULL AFTER `csv_separator` ,
3368 ADD `subfield_separator` VARCHAR( 2 ) NOT NULL AFTER `field_separator`
3370 print "Upgrade done (added separators for csv export)\n";
3371 SetVersion ($DBversion);
3374 $DBversion = "3.01.00.109";
3375 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
3377 ALTER TABLE `export_format` ADD `encoding` VARCHAR(255) NOT NULL AFTER `subfield_separator`
3379 print "Upgrade done (added encoding for csv export)\n";
3380 SetVersion ($DBversion);
3383 $DBversion = '3.01.00.110';
3384 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
3385 $dbh->do('ALTER TABLE `categories` ADD COLUMN `enrolmentperioddate` DATE NULL DEFAULT NULL AFTER `enrolmentperiod`');
3386 print "Upgrade done (Add enrolment period date support)\n";
3387 SetVersion ($DBversion);
3390 =item DropAllForeignKeys($table)
3392 Drop all foreign keys of the table $table
3397 sub DropAllForeignKeys {
3399 # get the table description
3400 my $sth = $dbh->prepare("SHOW CREATE TABLE $table");
3402 my $vsc_structure = $sth->fetchrow;
3403 # split on CONSTRAINT keyword
3404 my @fks = split /CONSTRAINT /,$vsc_structure;
3407 # isolate what is before FOREIGN KEY, if there is something, it's a foreign key to drop
3408 $_ = /(.*) FOREIGN KEY.*/;
3411 # we have found 1 foreign, drop it
3412 $dbh->do("ALTER TABLE $table DROP FOREIGN KEY $id");
3419 =item TransformToNum
3421 Transform the Koha version from a 4 parts string
3422 to a number, with just 1 .
3426 sub TransformToNum {
3427 my $version = shift;
3428 # remove the 3 last . to have a Perl number
3429 $version =~ s/(.*\..*)\.(.*)\.(.*)/$1$2$3/;
3435 set the DBversion in the systempreferences
3440 my $kohaversion = TransformToNum(shift);
3441 if (C4::Context->preference('Version')) {
3442 my $finish=$dbh->prepare("UPDATE systempreferences SET value=? WHERE variable='Version'");
3443 $finish->execute($kohaversion);
3445 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')");
3446 $finish->execute($kohaversion);
projects / koha.git / blob