5 # This script checks for required updates to the database.
7 # Part of the Koha Library Software www.koha.org
8 # Licensed under the GPL.
11 # - Would also be a good idea to offer to do a backup at this time...
13 # NOTE: If you do something more than once in here, make it table driven.
15 # NOTE: Please keep the version in kohaversion.pl up-to-date!
28 use MARC::File::XML ( BinaryEncoding => 'utf8' );
30 # FIXME - The user might be installing a new database, so can't rely
31 # on /etc/koha.conf anyway.
38 %existingtables, # tables already in database
42 $type, $null, $key, $default, $extra,
43 $prefitem, # preference item in systempreferences table
50 my $dbh = C4::Context->dbh;
51 $|=1; # flushes output
55 Deal with virtualshelves
59 my $DBversion = "3.00.00.001";
60 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
61 # update virtualshelves table to
63 $dbh->do("ALTER TABLE `bookshelf` RENAME `virtualshelves`");
64 $dbh->do("ALTER TABLE `shelfcontents` RENAME `virtualshelfcontents`");
65 $dbh->do("ALTER TABLE `virtualshelfcontents` ADD `biblionumber` INT( 11 ) NOT NULL default '0' AFTER shelfnumber");
66 $dbh->do("UPDATE `virtualshelfcontents` SET biblionumber=(SELECT biblionumber FROM items WHERE items.itemnumber=virtualshelfcontents.itemnumber)");
67 # drop all foreign keys : otherwise, we can't drop itemnumber field.
68 DropAllForeignKeys('virtualshelfcontents');
69 $dbh->do("ALTER TABLE `virtualshelfcontents` ADD KEY biblionumber (biblionumber)");
70 # create the new foreign keys (on biblionumber)
71 $dbh->do("ALTER TABLE `virtualshelfcontents` ADD CONSTRAINT `virtualshelfcontents_ibfk_1` FOREIGN KEY (`shelfnumber`) REFERENCES `virtualshelves` (`shelfnumber`) ON DELETE CASCADE ON UPDATE CASCADE");
72 # re-create the foreign key on virtualshelf
73 $dbh->do("ALTER TABLE `virtualshelfcontents` ADD CONSTRAINT `shelfcontents_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE");
74 $dbh->do("ALTER TABLE `virtualshelfcontents` DROP `itemnumber`");
75 print "Upgrade to $DBversion done (virtualshelves)\n";
76 SetVersion ($DBversion);
80 $DBversion = "3.00.00.002";
81 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
82 $dbh->do("DROP TABLE sessions");
83 $dbh->do("CREATE TABLE `sessions` (
84 `id` varchar(32) NOT NULL,
85 `a_session` text NOT NULL,
86 UNIQUE KEY `id` (`id`)
87 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;");
88 print "Upgrade to $DBversion done (sessions uses CGI::session, new table structure for sessions)\n";
89 SetVersion ($DBversion);
93 $DBversion = "3.00.00.003";
94 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
95 if (C4::Context->preference("opaclanguages") eq "fr") {
96 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('ReservesNeedReturns','0','Si ce paramètre est mis à 1, une réservation posée sur un exemplaire présent sur le site devra être passée en retour pour être disponible. Sinon, elle sera automatiquement disponible, Koha considère que le bibliothécaire place la réservation en ayant le document en mains','','YesNo')");
98 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('ReservesNeedReturns','0','If set, a reserve done on an item available in this branch need a check-in, otherwise, a reserve on a specific item, that is on the branch & available is considered as available','','YesNo')");
100 print "Upgrade to $DBversion done (adding ReservesNeedReturns systempref, in circulation)\n";
101 SetVersion ($DBversion);
105 $DBversion = "3.00.00.004";
106 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
107 $dbh->do("INSERT INTO `systempreferences` VALUES ('DebugLevel','2','set the level of error info sent to the browser. 0=none, 1=some, 2=most','0|1|2','Choice')");
108 print "Upgrade to $DBversion done (adding DebugLevel systempref, in 'Admin' tab)\n";
109 SetVersion ($DBversion);
112 $DBversion = "3.00.00.005";
113 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
114 $dbh->do("CREATE TABLE `tags` (
115 `entry` varchar(255) NOT NULL default '',
116 `weight` bigint(20) NOT NULL default 0,
117 PRIMARY KEY (`entry`)
118 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
120 $dbh->do("CREATE TABLE `nozebra` (
121 `server` varchar(20) NOT NULL,
122 `indexname` varchar(40) NOT NULL,
123 `value` varchar(250) NOT NULL,
124 `biblionumbers` longtext NOT NULL,
125 KEY `indexname` (`server`,`indexname`),
126 KEY `value` (`server`,`value`))
127 ENGINE=InnoDB DEFAULT CHARSET=utf8;
129 print "Upgrade to $DBversion done (adding tags and nozebra tables )\n";
130 SetVersion ($DBversion);
133 $DBversion = "3.00.00.006";
134 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
135 $dbh->do("UPDATE issues SET issuedate=timestamp WHERE issuedate='0000-00-00'");
136 print "Upgrade to $DBversion done (filled issues.issuedate with timestamp)\n";
137 SetVersion ($DBversion);
140 $DBversion = "3.00.00.007";
141 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
142 $dbh->do("INSERT INTO systempreferences (variable,value,explanation,options,type) VALUES ('SessionStorage','mysql','Use mysql or a temporary file for storing session data','mysql|tmp','Choice')");
143 print "Upgrade to $DBversion done (set SessionStorage variable)\n";
144 SetVersion ($DBversion);
147 $DBversion = "3.00.00.008";
148 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
149 $dbh->do("ALTER TABLE `biblio` ADD `datecreated` DATE NOT NULL AFTER `timestamp` ;");
150 $dbh->do("UPDATE biblio SET datecreated=timestamp");
151 print "Upgrade to $DBversion done (biblio creation date)\n";
152 SetVersion ($DBversion);
155 $DBversion = "3.00.00.009";
156 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
158 # Create backups of call number columns
159 # in case default migration needs to be customized
161 # UPGRADE NOTE: temp_upg_biblioitems_call_num should be dropped
162 # after call numbers have been transformed to the new structure
164 # Not bothering to do the same with deletedbiblioitems -- assume
165 # default is good enough.
166 $dbh->do("CREATE TABLE `temp_upg_biblioitems_call_num` AS
167 SELECT `biblioitemnumber`, `biblionumber`,
168 `classification`, `dewey`, `subclass`,
170 FROM `biblioitems`");
172 # biblioitems changes
173 $dbh->do("ALTER TABLE `biblioitems` CHANGE COLUMN `volumeddesc` `volumedesc` TEXT,
174 ADD `cn_source` VARCHAR(10) DEFAULT NULL AFTER `ccode`,
175 ADD `cn_class` VARCHAR(30) DEFAULT NULL AFTER `cn_source`,
176 ADD `cn_item` VARCHAR(10) DEFAULT NULL AFTER `cn_class`,
177 ADD `cn_suffix` VARCHAR(10) DEFAULT NULL AFTER `cn_item`,
178 ADD `cn_sort` VARCHAR(30) DEFAULT NULL AFTER `cn_suffix`,
179 ADD `totalissues` INT(10) AFTER `cn_sort`");
181 # default mapping of call number columns:
182 # cn_class = concatentation of classification + dewey,
183 # trimmed to fit -- assumes that most users do not
184 # populate both classification and dewey in a single record
186 # cn_source = left null
189 # After upgrade, cn_sort will have to be set based on whatever
190 # default call number scheme user sets as a preference. Misc
191 # script will be added at some point to do that.
193 $dbh->do("UPDATE `biblioitems`
194 SET cn_class = SUBSTR(TRIM(CONCAT_WS(' ', `classification`, `dewey`)), 1, 30),
199 # Now drop the old call number columns
200 $dbh->do("ALTER TABLE `biblioitems` DROP COLUMN `classification`,
202 DROP COLUMN `subclass`,
203 DROP COLUMN `lcsort`,
204 DROP COLUMN `ccode`");
206 # deletedbiblio changes
207 $dbh->do("ALTER TABLE `deletedbiblio` ALTER COLUMN `frameworkcode` SET DEFAULT '',
209 ADD `datecreated` DATE NOT NULL AFTER `timestamp`");
210 $dbh->do("UPDATE deletedbiblio SET datecreated = timestamp");
212 # deletedbiblioitems changes
213 $dbh->do("ALTER TABLE `deletedbiblioitems`
214 MODIFY `publicationyear` TEXT,
215 CHANGE `volumeddesc` `volumedesc` TEXT,
216 MODIFY `collectiontitle` MEDIUMTEXT DEFAULT NULL AFTER `volumedesc`,
217 MODIFY `collectionissn` TEXT DEFAULT NULL AFTER `collectiontitle`,
218 MODIFY `collectionvolume` MEDIUMTEXT DEFAULT NULL AFTER `collectionissn`,
219 MODIFY `editionstatement` TEXT DEFAULT NULL AFTER `collectionvolume`,
220 MODIFY `editionresponsibility` TEXT DEFAULT NULL AFTER `editionstatement`,
221 MODIFY `place` VARCHAR(255) DEFAULT NULL AFTER `size`,
222 MODIFY `marc` LONGBLOB,
223 ADD `cn_source` VARCHAR(10) DEFAULT NULL AFTER `url`,
224 ADD `cn_class` VARCHAR(30) DEFAULT NULL AFTER `cn_source`,
225 ADD `cn_item` VARCHAR(10) DEFAULT NULL AFTER `cn_class`,
226 ADD `cn_suffix` VARCHAR(10) DEFAULT NULL AFTER `cn_item`,
227 ADD `cn_sort` VARCHAR(30) DEFAULT NULL AFTER `cn_suffix`,
228 ADD `totalissues` INT(10) AFTER `cn_sort`,
229 ADD `marcxml` LONGTEXT NOT NULL AFTER `totalissues`,
230 ADD KEY `isbn` (`isbn`),
231 ADD KEY `publishercode` (`publishercode`)
234 $dbh->do("UPDATE `deletedbiblioitems`
235 SET `cn_class` = SUBSTR(TRIM(CONCAT_WS(' ', `classification`, `dewey`)), 1, 30),
236 `cn_item` = `subclass`,
239 $dbh->do("ALTER TABLE `deletedbiblioitems`
240 DROP COLUMN `classification`,
242 DROP COLUMN `subclass`,
243 DROP COLUMN `lcsort`,
247 # deleteditems changes
248 $dbh->do("ALTER TABLE `deleteditems`
249 MODIFY `barcode` VARCHAR(20) DEFAULT NULL,
250 MODIFY `price` DECIMAL(8,2) DEFAULT NULL,
251 MODIFY `replacementprice` DECIMAL(8,2) DEFAULT NULL,
253 MODIFY `itemcallnumber` VARCHAR(30) DEFAULT NULL AFTER `wthdrawn`,
254 MODIFY `holdingbranch` VARCHAR(10) DEFAULT NULL,
256 MODIFY `timestamp` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP AFTER `paidfor`,
258 ADD `cn_source` VARCHAR(10) DEFAULT NULL AFTER `onloan`,
259 ADD `cn_sort` VARCHAR(30) DEFAULT NULL AFTER `cn_source`,
260 ADD `ccode` VARCHAR(10) DEFAULT NULL AFTER `cn_sort`,
261 ADD `materials` VARCHAR(10) DEFAULT NULL AFTER `ccode`,
262 ADD `uri` VARCHAR(255) DEFAULT NULL AFTER `materials`,
263 MODIFY `marc` LONGBLOB AFTER `uri`,
265 DROP KEY `itembarcodeidx`,
266 DROP KEY `itembinoidx`,
267 DROP KEY `itembibnoidx`,
268 ADD UNIQUE KEY `delitembarcodeidx` (`barcode`),
269 ADD KEY `delitembinoidx` (`biblioitemnumber`),
270 ADD KEY `delitembibnoidx` (`biblionumber`),
271 ADD KEY `delhomebranch` (`homebranch`),
272 ADD KEY `delholdingbranch` (`holdingbranch`)");
273 $dbh->do("UPDATE deleteditems SET `ccode` = `itype`");
274 $dbh->do("ALTER TABLE deleteditems DROP `itype`");
275 $dbh->do("UPDATE `deleteditems` SET `cn_sort` = `itemcallnumber`");
278 $dbh->do("ALTER TABLE `items` ADD `cn_source` VARCHAR(10) DEFAULT NULL AFTER `onloan`,
279 ADD `cn_sort` VARCHAR(30) DEFAULT NULL AFTER `cn_source`,
280 ADD `ccode` VARCHAR(10) DEFAULT NULL AFTER `cn_sort`,
281 ADD `materials` VARCHAR(10) DEFAULT NULL AFTER `ccode`,
282 ADD `uri` VARCHAR(255) DEFAULT NULL AFTER `materials`
284 $dbh->do("ALTER TABLE `items`
285 DROP KEY `itembarcodeidx`,
286 ADD UNIQUE KEY `itembarcodeidx` (`barcode`)");
288 # map items.itype to items.ccode and
289 # set cn_sort to itemcallnumber -- as with biblioitems.cn_sort,
290 # will have to be subsequently updated per user's default
291 # classification scheme
292 $dbh->do("UPDATE `items` SET `cn_sort` = `itemcallnumber`,
295 $dbh->do("ALTER TABLE `items` DROP `cutterextra`,
298 print "Upgrade to $DBversion done (major changes to biblio, biblioitems, items, and deleted* versions of same\n";
299 SetVersion ($DBversion);
302 $DBversion = "3.00.00.010";
303 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
304 $dbh->do("CREATE INDEX `userid` ON borrowers (`userid`) ");
305 print "Upgrade to $DBversion done (userid index added)\n";
306 SetVersion ($DBversion);
309 $DBversion = "3.00.00.011";
310 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
311 $dbh->do("ALTER TABLE `branchcategories` CHANGE `categorycode` `categorycode` varchar(10) ");
312 $dbh->do("ALTER TABLE `branchcategories` CHANGE `categoryname` `categoryname` varchar(32) ");
313 $dbh->do("ALTER TABLE `branchcategories` ADD COLUMN `categorytype` varchar(16) ");
314 $dbh->do("UPDATE `branchcategories` SET `categorytype` = 'properties'");
315 $dbh->do("ALTER TABLE `branchrelations` CHANGE `categorycode` `categorycode` varchar(10) ");
316 print "Upgrade to $DBversion done (added branchcategory type)\n";
317 SetVersion ($DBversion);
320 $DBversion = "3.00.00.012";
321 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
322 $dbh->do("CREATE TABLE `class_sort_rules` (
323 `class_sort_rule` varchar(10) NOT NULL default '',
324 `description` mediumtext,
325 `sort_routine` varchar(30) NOT NULL default '',
326 PRIMARY KEY (`class_sort_rule`),
327 UNIQUE KEY `class_sort_rule_idx` (`class_sort_rule`)
328 ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
329 $dbh->do("CREATE TABLE `class_sources` (
330 `cn_source` varchar(10) NOT NULL default '',
331 `description` mediumtext,
332 `used` tinyint(4) NOT NULL default 0,
333 `class_sort_rule` varchar(10) NOT NULL default '',
334 PRIMARY KEY (`cn_source`),
335 UNIQUE KEY `cn_source_idx` (`cn_source`),
336 KEY `used_idx` (`used`),
337 CONSTRAINT `class_source_ibfk_1` FOREIGN KEY (`class_sort_rule`)
338 REFERENCES `class_sort_rules` (`class_sort_rule`)
339 ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
340 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type)
341 VALUES('DefaultClassificationSource','ddc',
342 'Default classification scheme used by the collection. E.g., Dewey, LCC, etc.', NULL,'free')");
343 $dbh->do("INSERT INTO `class_sort_rules` (`class_sort_rule`, `description`, `sort_routine`) VALUES
344 ('dewey', 'Default filing rules for DDC', 'Dewey'),
345 ('lcc', 'Default filing rules for LCC', 'LCC'),
346 ('generic', 'Generic call number filing rules', 'Generic')");
347 $dbh->do("INSERT INTO `class_sources` (`cn_source`, `description`, `used`, `class_sort_rule`) VALUES
348 ('ddc', 'Dewey Decimal Classification', 1, 'dewey'),
349 ('lcc', 'Library of Congress Classification', 1, 'lcc'),
350 ('udc', 'Universal Decimal Classification', 0, 'generic'),
351 ('sudocs', 'SuDoc Classification (U.S. GPO)', 0, 'generic'),
352 ('z', 'Other/Generic Classification Scheme', 0, 'generic')");
353 print "Upgrade to $DBversion done (classification sources added)\n";
354 SetVersion ($DBversion);
357 $DBversion = "3.00.00.013";
358 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
359 $dbh->do("CREATE TABLE `import_batches` (
360 `import_batch_id` int(11) NOT NULL auto_increment,
361 `template_id` int(11) default NULL,
362 `branchcode` varchar(10) default NULL,
363 `num_biblios` int(11) NOT NULL default 0,
364 `num_items` int(11) NOT NULL default 0,
365 `upload_timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP,
366 `overlay_action` enum('replace', 'create_new', 'use_template') NOT NULL default 'create_new',
367 `import_status` enum('staging', 'staged', 'importing', 'imported', 'reverting', 'reverted', 'cleaned') NOT NULL default 'staging',
368 `batch_type` enum('batch', 'z3950') NOT NULL default 'batch',
369 `file_name` varchar(100),
370 `comments` mediumtext,
371 PRIMARY KEY (`import_batch_id`),
372 KEY `branchcode` (`branchcode`)
373 ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
374 $dbh->do("CREATE TABLE `import_records` (
375 `import_record_id` int(11) NOT NULL auto_increment,
376 `import_batch_id` int(11) NOT NULL,
377 `branchcode` varchar(10) default NULL,
378 `record_sequence` int(11) NOT NULL default 0,
379 `upload_timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP,
380 `import_date` DATE default NULL,
381 `marc` longblob NOT NULL,
382 `marcxml` longtext NOT NULL,
383 `marcxml_old` longtext NOT NULL,
384 `record_type` enum('biblio', 'auth', 'holdings') NOT NULL default 'biblio',
385 `overlay_status` enum('no_match', 'auto_match', 'manual_match', 'match_applied') NOT NULL default 'no_match',
386 `status` enum('error', 'staged', 'imported', 'reverted', 'items_reverted') NOT NULL default 'staged',
387 `import_error` mediumtext,
388 `encoding` varchar(40) NOT NULL default '',
389 `z3950random` varchar(40) default NULL,
390 PRIMARY KEY (`import_record_id`),
391 CONSTRAINT `import_records_ifbk_1` FOREIGN KEY (`import_batch_id`)
392 REFERENCES `import_batches` (`import_batch_id`) ON DELETE CASCADE ON UPDATE CASCADE,
393 KEY `branchcode` (`branchcode`),
394 KEY `batch_sequence` (`import_batch_id`, `record_sequence`)
395 ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
396 $dbh->do("CREATE TABLE `import_record_matches` (
397 `import_record_id` int(11) NOT NULL,
398 `candidate_match_id` int(11) NOT NULL,
399 `score` int(11) NOT NULL default 0,
400 CONSTRAINT `import_record_matches_ibfk_1` FOREIGN KEY (`import_record_id`)
401 REFERENCES `import_records` (`import_record_id`) ON DELETE CASCADE ON UPDATE CASCADE,
402 KEY `record_score` (`import_record_id`, `score`)
403 ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
404 $dbh->do("CREATE TABLE `import_biblios` (
405 `import_record_id` int(11) NOT NULL,
406 `matched_biblionumber` int(11) default NULL,
407 `control_number` varchar(25) default NULL,
408 `original_source` varchar(25) default NULL,
409 `title` varchar(128) default NULL,
410 `author` varchar(80) default NULL,
411 `isbn` varchar(14) default NULL,
412 `issn` varchar(9) default NULL,
413 `has_items` tinyint(1) NOT NULL default 0,
414 CONSTRAINT `import_biblios_ibfk_1` FOREIGN KEY (`import_record_id`)
415 REFERENCES `import_records` (`import_record_id`) ON DELETE CASCADE ON UPDATE CASCADE,
416 KEY `matched_biblionumber` (`matched_biblionumber`),
417 KEY `title` (`title`),
419 ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
420 $dbh->do("CREATE TABLE `import_items` (
421 `import_items_id` int(11) NOT NULL auto_increment,
422 `import_record_id` int(11) NOT NULL,
423 `itemnumber` int(11) default NULL,
424 `branchcode` varchar(10) default NULL,
425 `status` enum('error', 'staged', 'imported', 'reverted') NOT NULL default 'staged',
426 `marcxml` longtext NOT NULL,
427 `import_error` mediumtext,
428 PRIMARY KEY (`import_items_id`),
429 CONSTRAINT `import_items_ibfk_1` FOREIGN KEY (`import_record_id`)
430 REFERENCES `import_records` (`import_record_id`) ON DELETE CASCADE ON UPDATE CASCADE,
431 KEY `itemnumber` (`itemnumber`),
432 KEY `branchcode` (`branchcode`)
433 ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
435 $dbh->do("INSERT INTO `import_batches`
436 (`overlay_action`, `import_status`, `batch_type`, `file_name`)
437 SELECT distinct 'create_new', 'staged', 'z3950', `file`
438 FROM `marc_breeding`");
440 $dbh->do("INSERT INTO `import_records`
441 (`import_batch_id`, `import_record_id`, `record_sequence`, `marc`, `record_type`, `status`,
442 `encoding`, `z3950random`, `marcxml`, `marcxml_old`)
443 SELECT `import_batch_id`, `id`, 1, `marc`, 'biblio', 'staged', `encoding`, `z3950random`, '', ''
445 JOIN `import_batches` ON (`file_name` = `file`)");
447 $dbh->do("INSERT INTO `import_biblios`
448 (`import_record_id`, `title`, `author`, `isbn`)
449 SELECT `import_record_id`, `title`, `author`, `isbn`
451 JOIN `import_records` ON (`import_record_id` = `id`)");
453 $dbh->do("UPDATE `import_batches`
454 SET `num_biblios` = (
456 FROM `import_records`
457 WHERE `import_batch_id` = `import_batches`.`import_batch_id`
460 $dbh->do("DROP TABLE `marc_breeding`");
462 print "Upgrade to $DBversion done (import_batches et al. added)\n";
463 SetVersion ($DBversion);
466 $DBversion = "3.00.00.014";
467 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
468 $dbh->do("ALTER TABLE subscription ADD lastbranch VARCHAR(4)");
469 print "Upgrade to $DBversion done (userid index added)\n";
470 SetVersion ($DBversion);
473 $DBversion = "3.00.00.015";
474 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
475 $dbh->do("CREATE TABLE `saved_sql` (
476 `id` int(11) NOT NULL auto_increment,
477 `borrowernumber` int(11) default NULL,
478 `date_created` datetime default NULL,
479 `last_modified` datetime default NULL,
481 `last_run` datetime default NULL,
482 `report_name` varchar(255) default NULL,
483 `type` varchar(255) default NULL,
486 KEY boridx (`borrowernumber`)
487 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;");
488 $dbh->do("CREATE TABLE `saved_reports` (
489 `id` int(11) NOT NULL auto_increment,
490 `report_id` int(11) default NULL,
492 `date_run` datetime default NULL,
494 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;");
495 print "Upgrade to $DBversion done (saved_sql and saved_reports added)\n";
496 SetVersion ($DBversion);
499 $DBversion = "3.00.00.016";
500 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
501 $dbh->do(" CREATE TABLE reports_dictionary (
502 id int(11) NOT NULL auto_increment,
503 name varchar(255) default NULL,
505 date_created datetime default NULL,
506 date_modified datetime default NULL,
508 area int(11) default NULL,
510 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ");
511 print "Upgrade to $DBversion done (reports_dictionary) added)\n";
512 SetVersion ($DBversion);
515 $DBversion = "3.00.00.017";
516 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
517 $dbh->do("ALTER TABLE action_logs DROP PRIMARY KEY");
518 $dbh->do("ALTER TABLE action_logs ADD KEY timestamp (timestamp,user)");
519 $dbh->do("ALTER TABLE action_logs ADD action_id INT(11) NOT NULL FIRST");
520 $dbh->do("UPDATE action_logs SET action_id = if (\@a, \@a:=\@a+1, \@a:=1)");
521 $dbh->do("ALTER TABLE action_logs MODIFY action_id INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY");
522 print "Upgrade to $DBversion done (added column to action_logs)\n";
523 SetVersion ($DBversion);
526 $DBversion = "3.00.00.018";
527 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
528 $dbh->do("ALTER TABLE `zebraqueue`
529 ADD `done` INT NOT NULL DEFAULT '0',
530 ADD `time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ;
532 print "Upgrade to $DBversion done (adding timestamp and done columns to zebraque table to improve problem tracking) added)\n";
533 SetVersion ($DBversion);
536 $DBversion = "3.00.00.019";
537 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
538 $dbh->do("ALTER TABLE biblio MODIFY biblionumber INT(11) NOT NULL AUTO_INCREMENT");
539 $dbh->do("ALTER TABLE biblioitems MODIFY biblioitemnumber INT(11) NOT NULL AUTO_INCREMENT");
540 $dbh->do("ALTER TABLE items MODIFY itemnumber INT(11) NOT NULL AUTO_INCREMENT");
541 print "Upgrade to $DBversion done (made bib/item PKs auto_increment)\n";
542 SetVersion ($DBversion);
545 $DBversion = "3.00.00.020";
546 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
547 $dbh->do("ALTER TABLE deleteditems
548 DROP KEY `delitembarcodeidx`,
549 ADD KEY `delitembarcodeidx` (`barcode`)");
550 print "Upgrade to $DBversion done (dropped uniqueness of key on deleteditems.barcode)\n";
551 SetVersion ($DBversion);
554 $DBversion = "3.00.00.021";
555 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
556 $dbh->do("ALTER TABLE items CHANGE homebranch homebranch VARCHAR(10)");
557 $dbh->do("ALTER TABLE deleteditems CHANGE homebranch homebranch VARCHAR(10)");
558 $dbh->do("ALTER TABLE statistics CHANGE branch branch VARCHAR(10)");
559 $dbh->do("ALTER TABLE subscription CHANGE lastbranch lastbranch VARCHAR(10)");
560 print "Upgrade to $DBversion done (extended missed branchcode columns to 10 chars)\n";
561 SetVersion ($DBversion);
564 $DBversion = "3.00.00.022";
565 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
566 $dbh->do("ALTER TABLE items
567 ADD `damaged` tinyint(1) default NULL AFTER notforloan");
568 $dbh->do("ALTER TABLE deleteditems
569 ADD `damaged` tinyint(1) default NULL AFTER notforloan");
570 print "Upgrade to $DBversion done (adding damaged column to items table)\n";
571 SetVersion ($DBversion);
574 $DBversion = "3.00.00.023";
575 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
576 $dbh->do("INSERT INTO `systempreferences` (variable,value,options,explanation,type)
577 VALUES ('yuipath','http://yui.yahooapis.com/2.3.1/build','Insert the path to YUI libraries','','free')");
578 print "Upgrade to $DBversion done (adding new system preference for controlling YUI path)\n";
579 SetVersion ($DBversion);
581 $DBversion = "3.00.00.024";
582 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
583 $dbh->do("ALTER TABLE biblioitems CHANGE itemtype itemtype VARCHAR(10)");
584 print "Upgrade to $DBversion done (changing itemtype to (10))\n";
585 SetVersion ($DBversion);
588 $DBversion = "3.00.00.025";
589 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
590 $dbh->do("ALTER TABLE items ADD COLUMN itype VARCHAR(10)");
591 $dbh->do("ALTER TABLE deleteditems ADD COLUMN itype VARCHAR(10) AFTER uri");
592 if(C4::Context->preference('item-level_itypes')){
593 $dbh->do('update items,biblioitems set items.itype=biblioitems.itemtype where items.biblionumber=biblioitems.biblionumber and itype is null');
595 print "Upgrade to $DBversion done (reintroduce items.itype - fill from itemtype)\n ";
596 SetVersion ($DBversion);
599 $DBversion = "3.00.00.026";
600 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
601 $dbh->do("INSERT INTO `systempreferences` (variable,value,options,explanation,type)
602 VALUES ('HomeOrHoldingBranch','homebranch','homebranch|holdingbranch','With independent branches turned on this decides whether to check the items holdingbranch or homebranch at circulatilon','choice')");
603 print "Upgrade to $DBversion done (adding new system preference for choosing whether homebranch or holdingbranch is checked in circulation)\n";
604 SetVersion ($DBversion);
607 $DBversion = "3.00.00.027";
608 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
609 $dbh->do("CREATE TABLE `marc_matchers` (
610 `matcher_id` int(11) NOT NULL auto_increment,
611 `code` varchar(10) NOT NULL default '',
612 `description` varchar(255) NOT NULL default '',
613 `record_type` varchar(10) NOT NULL default 'biblio',
614 `threshold` int(11) NOT NULL default 0,
615 PRIMARY KEY (`matcher_id`),
617 KEY `record_type` (`record_type`)
618 ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
619 $dbh->do("CREATE TABLE `matchpoints` (
620 `matcher_id` int(11) NOT NULL,
621 `matchpoint_id` int(11) NOT NULL auto_increment,
622 `search_index` varchar(30) NOT NULL default '',
623 `score` int(11) NOT NULL default 0,
624 PRIMARY KEY (`matchpoint_id`),
625 CONSTRAINT `matchpoints_ifbk_1` FOREIGN KEY (`matcher_id`)
626 REFERENCES `marc_matchers` (`matcher_id`) ON DELETE CASCADE ON UPDATE CASCADE
627 ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
628 $dbh->do("CREATE TABLE `matchpoint_components` (
629 `matchpoint_id` int(11) NOT NULL,
630 `matchpoint_component_id` int(11) NOT NULL auto_increment,
631 sequence int(11) NOT NULL default 0,
632 tag varchar(3) NOT NULL default '',
633 subfields varchar(40) NOT NULL default '',
634 offset int(4) NOT NULL default 0,
635 length int(4) NOT NULL default 0,
636 PRIMARY KEY (`matchpoint_component_id`),
637 KEY `by_sequence` (`matchpoint_id`, `sequence`),
638 CONSTRAINT `matchpoint_components_ifbk_1` FOREIGN KEY (`matchpoint_id`)
639 REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE
640 ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
641 $dbh->do("CREATE TABLE `matchpoint_component_norms` (
642 `matchpoint_component_id` int(11) NOT NULL,
643 `sequence` int(11) NOT NULL default 0,
644 `norm_routine` varchar(50) NOT NULL default '',
645 KEY `matchpoint_component_norms` (`matchpoint_component_id`, `sequence`),
646 CONSTRAINT `matchpoint_component_norms_ifbk_1` FOREIGN KEY (`matchpoint_component_id`)
647 REFERENCES `matchpoint_components` (`matchpoint_component_id`) ON DELETE CASCADE ON UPDATE CASCADE
648 ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
649 $dbh->do("CREATE TABLE `matcher_matchpoints` (
650 `matcher_id` int(11) NOT NULL,
651 `matchpoint_id` int(11) NOT NULL,
652 CONSTRAINT `matcher_matchpoints_ifbk_1` FOREIGN KEY (`matcher_id`)
653 REFERENCES `marc_matchers` (`matcher_id`) ON DELETE CASCADE ON UPDATE CASCADE,
654 CONSTRAINT `matcher_matchpoints_ifbk_2` FOREIGN KEY (`matchpoint_id`)
655 REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE
656 ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
657 $dbh->do("CREATE TABLE `matchchecks` (
658 `matcher_id` int(11) NOT NULL,
659 `matchcheck_id` int(11) NOT NULL auto_increment,
660 `source_matchpoint_id` int(11) NOT NULL,
661 `target_matchpoint_id` int(11) NOT NULL,
662 PRIMARY KEY (`matchcheck_id`),
663 CONSTRAINT `matcher_matchchecks_ifbk_1` FOREIGN KEY (`matcher_id`)
664 REFERENCES `marc_matchers` (`matcher_id`) ON DELETE CASCADE ON UPDATE CASCADE,
665 CONSTRAINT `matcher_matchchecks_ifbk_2` FOREIGN KEY (`source_matchpoint_id`)
666 REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE,
667 CONSTRAINT `matcher_matchchecks_ifbk_3` FOREIGN KEY (`target_matchpoint_id`)
668 REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE
669 ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
670 print "Upgrade to $DBversion done (added C4::Matcher serialization tables)\n ";
671 SetVersion ($DBversion);
674 $DBversion = "3.00.00.028";
675 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
676 $dbh->do("INSERT INTO `systempreferences` (variable,value,options,explanation,type)
677 VALUES ('canreservefromotherbranches','1','','With Independent branches on, can a user from one library reserve an item from another library','YesNo')");
678 print "Upgrade to $DBversion done (adding new system preference for changing reserve/holds behaviour with independent branches)\n";
679 SetVersion ($DBversion);
683 $DBversion = "3.00.00.029";
684 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
685 $dbh->do("ALTER TABLE `import_batches` ADD `matcher_id` int(11) NULL AFTER `import_batch_id`");
686 print "Upgrade to $DBversion done (adding matcher_id to import_batches)\n";
687 SetVersion ($DBversion);
690 $DBversion = "3.00.00.030";
691 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
693 CREATE TABLE services_throttle (
694 service_type varchar(10) NOT NULL default '',
695 service_count varchar(45) default NULL,
696 PRIMARY KEY (service_type)
697 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
699 $dbh->do("INSERT INTO `systempreferences` (variable,value,options,explanation,type)
700 VALUES ('FRBRizeEditions',0,'','If ON, Koha will query one or more ISBN web services for associated ISBNs and display an Editions tab on the details pages','YesNo')");
701 $dbh->do("INSERT INTO `systempreferences` (variable,value,options,explanation,type)
702 VALUES ('XISBN',0,'','Use with FRBRizeEditions. If ON, Koha will use the OCLC xISBN web service in the Editions tab on the detail pages. See: http://www.worldcat.org/affiliate/webservices/xisbn/app.jsp','YesNo')");
703 $dbh->do("INSERT INTO `systempreferences` (variable,value,options,explanation,type)
704 VALUES ('OCLCAffiliateID','','','Use with FRBRizeEditions and XISBN. You can sign up for an AffiliateID here: http://www.worldcat.org/wcpa/do/AffiliateUserServices?method=initSelfRegister','free')");
705 $dbh->do("INSERT INTO `systempreferences` (variable,value,options,explanation,type)
706 VALUES ('XISBNDailyLimit',499,'','The xISBN Web service is free for non-commercial use when usage does not exceed 500 requests per day','free')");
707 $dbh->do("INSERT INTO `systempreferences` (variable,value,options,explanation,type)
708 VALUES ('PINESISBN',0,'','Use with FRBRizeEditions. If ON, Koha will use PINES OISBN web service in the Editions tab on the detail pages.','YesNo')");
709 $dbh->do("INSERT INTO `systempreferences` (variable,value,options,explanation,type)
710 VALUES ('ThingISBN',0,'','Use with FRBRizeEditions. If ON, Koha will use the ThingISBN web service in the Editions tab on the detail pages.','YesNo')");
711 print "Upgrade to $DBversion done (adding services throttle table and sysprefs for xISBN)\n";
712 SetVersion ($DBversion);
715 $DBversion = "3.00.00.031";
716 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
718 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('QueryStemming',1,'If ON, enables query stemming',NULL,'YesNo')");
719 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('QueryFuzzy',1,'If ON, enables fuzzy option for searches',NULL,'YesNo')");
720 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('QueryWeightFields',1,'If ON, enables field weighting',NULL,'YesNo')");
721 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('WebBasedSelfCheck',0,'If ON, enables the web-based self-check system',NULL,'YesNo')");
722 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('numSearchResults',20,'Specify the maximum number of results to display on a page of results',NULL,'free')");
723 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('OPACnumSearchResults',20,'Specify the maximum number of results to display on a page of results',NULL,'free')");
724 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('maxItemsInSearchResults',20,'Specify the maximum number of items to display for each result on a page of results',NULL,'free')");
725 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('defaultSortField',NULL,'Specify the default field used for sorting','relevance|popularity|call_number|pubdate|acqdate|title|author','Choice')");
726 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('defaultSortOrder',NULL,'Specify the default sort order','asc|dsc|az|za','Choice')");
727 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('OPACdefaultSortField',NULL,'Specify the default field used for sorting','relevance|popularity|call_number|pubdate|acqdate|title|author','Choice')");
728 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('OPACdefaultSortOrder',NULL,'Specify the default sort order','asc|dsc|za|az','Choice')");
729 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('staffClientBaseURL','','Specify the base URL of the staff client',NULL,'free')");
730 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('minPasswordLength',3,'Specify the minimum length of a patron/staff password',NULL,'free')");
731 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('noItemTypeImages',0,'If ON, disables item-type images',NULL,'YesNo')");
732 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('noOPACHolds',0,'If ON, disables holds globally',NULL,'YesNo')");
733 $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')");
734 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('holdCancelLength','','Specify how many days before a hold is canceled',NULL,'free')");
735 $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')");
736 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('finesMode','test','Choose the fines mode, test or production','test|production','Choice')");
737 $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')");
738 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('itemBarcodeInputFilter','','If set, allows specification of a item barcode input filter','cuecat','Choice')");
739 $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')");
740 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('URLLinkText','','Text to display as the link anchor in the OPAC',NULL,'free')");
741 $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')");
742 $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')");
743 $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')");
744 $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')");
745 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('OPACUserCSS',0,'Add CSS to be included in the OPAC',NULL,'free')");
746 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('emailPurchaseSuggestions',0,'If ON, patron suggestions are emailed rather than managed in Acquisitions',NULL,'YesNo')");
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'");
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 VARCHAR(64) 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.<br /> 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 <br />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);
1507 $DBversion = "3.00.00.080";
1508 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1509 $dbh->do("ALTER TABLE subscription CHANGE monthlength monthlength int(11) default '0'");
1510 $dbh->do("ALTER TABLE deleteditems MODIFY marc LONGBLOB AFTER copynumber");
1511 $dbh->do("ALTER TABLE aqbooksellers CHANGE name name mediumtext NOT NULL");
1512 print "Upgrade to $DBversion done (catch up on DB schema changes since alpha and beta)\n";
1513 SetVersion ($DBversion);
1516 $DBversion = "3.00.00.081";
1517 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1518 $dbh->do("CREATE TABLE `borrower_attribute_types` (
1519 `code` varchar(10) NOT NULL,
1520 `description` varchar(255) NOT NULL,
1521 `repeatable` tinyint(1) NOT NULL default 0,
1522 `unique_id` tinyint(1) NOT NULL default 0,
1523 `opac_display` tinyint(1) NOT NULL default 0,
1524 `password_allowed` tinyint(1) NOT NULL default 0,
1525 `staff_searchable` tinyint(1) NOT NULL default 0,
1526 `authorised_value_category` varchar(10) default NULL,
1527 PRIMARY KEY (`code`)
1528 ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
1529 $dbh->do("CREATE TABLE `borrower_attributes` (
1530 `borrowernumber` int(11) NOT NULL,
1531 `code` varchar(10) NOT NULL,
1532 `attribute` varchar(30) default NULL,
1533 `password` varchar(30) default NULL,
1534 KEY `borrowernumber` (`borrowernumber`),
1535 KEY `code_attribute` (`code`, `attribute`),
1536 CONSTRAINT `borrower_attributes_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`)
1537 ON DELETE CASCADE ON UPDATE CASCADE,
1538 CONSTRAINT `borrower_attributes_ibfk_2` FOREIGN KEY (`code`) REFERENCES `borrower_attribute_types` (`code`)
1539 ON DELETE CASCADE ON UPDATE CASCADE
1540 ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
1541 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('ExtendedPatronAttributes','0','Use extended patron IDs and attributes',NULL,'YesNo')");
1542 print "Upgrade to $DBversion done (added borrower_attributes and borrower_attribute_types)\n";
1543 SetVersion ($DBversion);
1546 $DBversion = "3.00.00.082";
1547 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1548 $dbh->do( q(alter table accountlines add column lastincrement decimal(28,6) default NULL) );
1549 print "Upgrade to $DBversion done (adding lastincrement column to accountlines table)\n";
1550 SetVersion ($DBversion);
1553 $DBversion = "3.00.00.083";
1554 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1555 $dbh->do( qq(UPDATE systempreferences SET value='local' where variable='yuipath' and value like "%/intranet-tmpl/prog/%"));
1556 print "Upgrade to $DBversion done (Changing yuipath behaviour in managing a local value)\n";
1557 SetVersion ($DBversion);
1559 $DBversion = "3.00.00.084";
1560 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1561 $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')");
1562 $dbh->do("INSERT INTO systempreferences (variable,value,explanation,options,type) VALUES('GoogleJackets','0','if ON, displays jacket covers from Google Books API',NULL,'YesNo')");
1563 print "Upgrade to $DBversion done (add new sysprefs)\n";
1564 SetVersion ($DBversion);
1567 $DBversion = "3.00.00.085";
1568 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1569 if (C4::Context->preference("marcflavour") eq 'MARC21') {
1570 $dbh->do("UPDATE marc_subfield_structure SET tab = 0 WHERE tab = 9 AND tagfield = '037'");
1571 $dbh->do("UPDATE marc_subfield_structure SET tab = 1 WHERE tab = 6 AND tagfield in ('100', '110', '111', '130')");
1572 $dbh->do("UPDATE marc_subfield_structure SET tab = 2 WHERE tab = 6 AND tagfield in ('240', '243')");
1573 $dbh->do("UPDATE marc_subfield_structure SET tab = 4 WHERE tab = 6 AND tagfield in ('400', '410', '411', '440')");
1574 $dbh->do("UPDATE marc_subfield_structure SET tab = 5 WHERE tab = 9 AND tagfield = '584'");
1575 $dbh->do("UPDATE marc_subfield_structure SET tab = 7 WHERE tab = -6 AND tagfield = '760'");
1577 print "Upgrade to $DBversion done (move editing tab of various MARC21 subfields)\n";
1578 SetVersion ($DBversion);
1581 $DBversion = "3.00.00.086";
1582 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1584 "CREATE TABLE `tmp_holdsqueue` (
1585 `biblionumber` int(11) default NULL,
1586 `itemnumber` int(11) default NULL,
1587 `barcode` varchar(20) default NULL,
1588 `surname` mediumtext NOT NULL,
1591 `borrowernumber` int(11) NOT NULL,
1592 `cardnumber` varchar(16) default NULL,
1593 `reservedate` date default NULL,
1595 `itemcallnumber` varchar(30) default NULL,
1596 `holdingbranch` varchar(10) default NULL,
1597 `pickbranch` varchar(10) default NULL,
1599 ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
1601 $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')");
1602 $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')");
1604 print "Upgrade to $DBversion done (Table structure for table `tmp_holdsqueue`)\n";
1605 SetVersion ($DBversion);
1608 $DBversion = "3.00.00.087";
1609 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1610 $dbh->do("INSERT INTO `systempreferences` VALUES ('AutoEmailOpacUser','0','','Sends notification emails containing new account details to patrons - when account is created.','YesNo')" );
1611 $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')");
1612 print "Upgrade to $DBversion done (added 2 new 'AutoEmailOpacUser' sysprefs)\n";
1613 SetVersion ($DBversion);
1616 $DBversion = "3.00.00.088";
1617 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1618 $dbh->do("INSERT INTO `systempreferences` (variable,value,options,explanation,type) VALUES ('OPACShelfBrowser','1','','Enable/disable Shelf Browser on item details page','YesNo')");
1619 $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')");
1620 $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')");
1621 $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')");
1622 print "Upgrade to $DBversion done (added 2 new 'AutoEmailOpacUser' sysprefs)\n";
1623 SetVersion ($DBversion);
1626 $DBversion = "3.00.00.089";
1627 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1628 $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')");
1629 print "Upgrade to $DBversion done (added new AdvancedSearchTypes syspref)\n";
1630 SetVersion ($DBversion);
1633 $DBversion = "3.00.00.090";
1634 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1636 CREATE TABLE `branch_borrower_circ_rules` (
1637 `branchcode` VARCHAR(10) NOT NULL,
1638 `categorycode` VARCHAR(10) NOT NULL,
1639 `maxissueqty` int(4) default NULL,
1640 PRIMARY KEY (`categorycode`, `branchcode`),
1641 CONSTRAINT `branch_borrower_circ_rules_ibfk_1` FOREIGN KEY (`categorycode`) REFERENCES `categories` (`categorycode`)
1642 ON DELETE CASCADE ON UPDATE CASCADE,
1643 CONSTRAINT `branch_borrower_circ_rules_ibfk_2` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`)
1644 ON DELETE CASCADE ON UPDATE CASCADE
1645 ) ENGINE=InnoDB DEFAULT CHARSET=utf8
1648 CREATE TABLE `default_borrower_circ_rules` (
1649 `categorycode` VARCHAR(10) NOT NULL,
1650 `maxissueqty` int(4) default NULL,
1651 PRIMARY KEY (`categorycode`),
1652 CONSTRAINT `borrower_borrower_circ_rules_ibfk_1` FOREIGN KEY (`categorycode`) REFERENCES `categories` (`categorycode`)
1653 ON DELETE CASCADE ON UPDATE CASCADE
1654 ) ENGINE=InnoDB DEFAULT CHARSET=utf8
1657 CREATE TABLE `default_branch_circ_rules` (
1658 `branchcode` VARCHAR(10) NOT NULL,
1659 `maxissueqty` int(4) default NULL,
1660 PRIMARY KEY (`branchcode`),
1661 CONSTRAINT `default_branch_circ_rules_ibfk_1` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`)
1662 ON DELETE CASCADE ON UPDATE CASCADE
1663 ) ENGINE=InnoDB DEFAULT CHARSET=utf8
1666 CREATE TABLE `default_circ_rules` (
1667 `singleton` enum('singleton') NOT NULL default 'singleton',
1668 `maxissueqty` int(4) default NULL,
1669 PRIMARY KEY (`singleton`)
1670 ) ENGINE=InnoDB DEFAULT CHARSET=utf8
1672 print "Upgrade to $DBversion done (added several circ rules tables)\n";
1673 SetVersion ($DBversion);
1677 $DBversion = "3.00.00.091";
1678 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1679 $dbh->do(<<'END_SQL');
1680 ALTER TABLE borrowers
1681 ADD `smsalertnumber` varchar(50) default NULL
1684 $dbh->do(<<'END_SQL');
1685 CREATE TABLE `message_attributes` (
1686 `message_attribute_id` int(11) NOT NULL auto_increment,
1687 `message_name` varchar(20) NOT NULL default '',
1688 `takes_days` tinyint(1) NOT NULL default '0',
1689 PRIMARY KEY (`message_attribute_id`),
1690 UNIQUE KEY `message_name` (`message_name`)
1691 ) ENGINE=InnoDB DEFAULT CHARSET=utf8
1694 $dbh->do(<<'END_SQL');
1695 CREATE TABLE `message_transport_types` (
1696 `message_transport_type` varchar(20) NOT NULL,
1697 PRIMARY KEY (`message_transport_type`)
1698 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1701 $dbh->do(<<'END_SQL');
1702 CREATE TABLE `message_transports` (
1703 `message_attribute_id` int(11) NOT NULL,
1704 `message_transport_type` varchar(20) NOT NULL,
1705 `is_digest` tinyint(1) NOT NULL default '0',
1706 `letter_module` varchar(20) NOT NULL default '',
1707 `letter_code` varchar(20) NOT NULL default '',
1708 PRIMARY KEY (`message_attribute_id`,`message_transport_type`,`is_digest`),
1709 KEY `message_transport_type` (`message_transport_type`),
1710 KEY `letter_module` (`letter_module`,`letter_code`),
1711 CONSTRAINT `message_transports_ibfk_1` FOREIGN KEY (`message_attribute_id`) REFERENCES `message_attributes` (`message_attribute_id`) ON DELETE CASCADE ON UPDATE CASCADE,
1712 CONSTRAINT `message_transports_ibfk_2` FOREIGN KEY (`message_transport_type`) REFERENCES `message_transport_types` (`message_transport_type`) ON DELETE CASCADE ON UPDATE CASCADE,
1713 CONSTRAINT `message_transports_ibfk_3` FOREIGN KEY (`letter_module`, `letter_code`) REFERENCES `letter` (`module`, `code`) ON DELETE CASCADE ON UPDATE CASCADE
1714 ) ENGINE=InnoDB DEFAULT CHARSET=utf8
1717 $dbh->do(<<'END_SQL');
1718 CREATE TABLE `borrower_message_preferences` (
1719 `borrower_message_preference_id` int(11) NOT NULL auto_increment,
1720 `borrowernumber` int(11) NOT NULL default '0',
1721 `message_attribute_id` int(11) default '0',
1722 `days_in_advance` int(11) default '0',
1723 `wants_digets` tinyint(1) NOT NULL default '0',
1724 PRIMARY KEY (`borrower_message_preference_id`),
1725 KEY `borrowernumber` (`borrowernumber`),
1726 KEY `message_attribute_id` (`message_attribute_id`),
1727 CONSTRAINT `borrower_message_preferences_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1728 CONSTRAINT `borrower_message_preferences_ibfk_2` FOREIGN KEY (`message_attribute_id`) REFERENCES `message_attributes` (`message_attribute_id`) ON DELETE CASCADE ON UPDATE CASCADE
1729 ) ENGINE=InnoDB DEFAULT CHARSET=utf8
1732 $dbh->do(<<'END_SQL');
1733 CREATE TABLE `borrower_message_transport_preferences` (
1734 `borrower_message_preference_id` int(11) NOT NULL default '0',
1735 `message_transport_type` varchar(20) NOT NULL default '0',
1736 PRIMARY KEY (`borrower_message_preference_id`,`message_transport_type`),
1737 KEY `message_transport_type` (`message_transport_type`),
1738 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,
1739 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
1740 ) ENGINE=InnoDB DEFAULT CHARSET=utf8
1743 $dbh->do(<<'END_SQL');
1744 CREATE TABLE `message_queue` (
1745 `message_id` int(11) NOT NULL auto_increment,
1746 `borrowernumber` int(11) NOT NULL,
1749 `message_transport_type` varchar(20) NOT NULL,
1750 `status` enum('sent','pending','failed','deleted') NOT NULL default 'pending',
1751 `time_queued` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1752 KEY `message_id` (`message_id`),
1753 KEY `borrowernumber` (`borrowernumber`),
1754 KEY `message_transport_type` (`message_transport_type`),
1755 CONSTRAINT `messageq_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1756 CONSTRAINT `messageq_ibfk_2` FOREIGN KEY (`message_transport_type`) REFERENCES `message_transport_types` (`message_transport_type`) ON DELETE RESTRICT ON UPDATE CASCADE
1757 ) ENGINE=InnoDB DEFAULT CHARSET=utf8
1760 $dbh->do(<<'END_SQL');
1761 INSERT INTO `systempreferences`
1762 (variable,value,explanation,options,type)
1764 ('EnhancedMessagingPreferences',0,'If ON, allows patrons to select to receive additional messages about items due or nearly due.','','YesNo')
1767 $dbh->do( <<'END_SQL');
1768 INSERT INTO `letter`
1769 (module, code, name, title, content)
1771 ('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>>'),
1772 ('circulation','DUEDGST','Item Due Reminder (Digest)','Item Due Reminder','You have <<count>> items due'),
1773 ('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>>'),
1774 ('circulation','PREDUEDGST','Advance Notice of Item Due (Digest)','Advance Notice of Item Due','You have <<count>> items due soon'),
1775 ('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.');
1779 'installer/data/mysql/en/mandatory/message_transport_types.sql',
1780 'installer/data/mysql/en/optional/sample_notices_message_attributes.sql',
1781 'installer/data/mysql/en/optional/sample_notices_message_transports.sql',
1784 my $installer = C4::Installer->new();
1785 foreach my $script ( @sql_scripts ) {
1786 my $full_path = $installer->get_file_path_from_name($script);
1787 my $error = $installer->load_sql($full_path);
1788 warn $error if $error;
1791 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";
1792 SetVersion ($DBversion);
1795 $DBversion = "3.00.00.092";
1796 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1797 $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')");
1798 $dbh->do("INSERT INTO `systempreferences` (variable,value,options,explanation,type) VALUES('AllowHoldsOnDamagedItems', '1', '', 'Allow hold requests to be placed on damaged items', 'YesNo')");
1799 print "Upgrade to $DBversion done (added new AllowOnShelfHolds syspref)\n";
1800 SetVersion ($DBversion);
1803 $DBversion = "3.00.00.093";
1804 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1805 $dbh->do("ALTER TABLE `items` MODIFY COLUMN `copynumber` VARCHAR(32) DEFAULT NULL");
1806 $dbh->do("ALTER TABLE `deleteditems` MODIFY COLUMN `copynumber` VARCHAR(32) DEFAULT NULL");
1807 print "Upgrade to $DBversion done (Change data type of items.copynumber to allow free text)\n";
1808 SetVersion ($DBversion);
1811 $DBversion = "3.00.00.094";
1812 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1813 $dbh->do("ALTER TABLE `marc_subfield_structure` MODIFY `tagsubfield` VARCHAR(1) NOT NULL DEFAULT '' COLLATE utf8_bin");
1814 print "Upgrade to $DBversion done (Change Collation of marc_subfield_structure to allow mixed case in subfield labels.)\n";
1815 SetVersion ($DBversion);
1818 $DBversion = "3.00.00.095";
1819 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1820 if (C4::Context->preference("marcflavour") eq 'MARC21') {
1821 $dbh->do("UPDATE marc_subfield_structure SET authtypecode = 'MEETI_NAME' WHERE authtypecode = 'Meeting Name'");
1822 $dbh->do("UPDATE marc_subfield_structure SET authtypecode = 'CORPO_NAME' WHERE authtypecode = 'CORP0_NAME'");
1824 print "Upgrade to $DBversion done (fix invalid authority types in MARC21 frameworks [bug 2254])\n";
1825 SetVersion ($DBversion);
1828 $DBversion = "3.00.00.096";
1829 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1830 $sth = $dbh->prepare("SHOW COLUMNS FROM borrower_message_preferences LIKE 'wants_digets'");
1832 if (my $row = $sth->fetchrow_hashref) {
1833 $dbh->do("ALTER TABLE borrower_message_preferences CHANGE wants_digets wants_digest tinyint(1) NOT NULL default 0");
1835 print "Upgrade to $DBversion done (fix name borrower_message_preferences.wants_digest)\n";
1836 SetVersion ($DBversion);
1838 =item DropAllForeignKeys($table)
1840 Drop all foreign keys of the table $table
1844 sub DropAllForeignKeys {
1846 # get the table description
1847 my $sth = $dbh->prepare("SHOW CREATE TABLE $table");
1849 my $vsc_structure = $sth->fetchrow;
1850 # split on CONSTRAINT keyword
1851 my @fks = split /CONSTRAINT /,$vsc_structure;
1854 # isolate what is before FOREIGN KEY, if there is something, it's a foreign key to drop
1855 $_ = /(.*) FOREIGN KEY.*/;
1858 # we have found 1 foreign, drop it
1859 $dbh->do("ALTER TABLE $table DROP FOREIGN KEY $id");
1866 =item TransformToNum
1868 Transform the Koha version from a 4 parts string
1869 to a number, with just 1 .
1873 sub TransformToNum {
1874 my $version = shift;
1875 # remove the 3 last . to have a Perl number
1876 $version =~ s/(.*\..*)\.(.*)\.(.*)/$1$2$3/;
1882 set the DBversion in the systempreferences
1887 my $kohaversion = TransformToNum(shift);
1888 if (C4::Context->preference('Version')) {
1889 my $finish=$dbh->prepare("UPDATE systempreferences SET value=? WHERE variable='Version'");
1890 $finish->execute($kohaversion);
1892 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')");
1893 $finish->execute($kohaversion);