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 C4/Context.pm up-to-date!
26 use MARC::File::XML ( BinaryEncoding => 'utf8' );
28 # FIXME - The user might be installing a new database, so can't rely
29 # on /etc/koha.conf anyway.
36 %existingtables, # tables already in database
40 $type, $null, $key, $default, $extra,
41 $prefitem, # preference item in systempreferences table
48 my $dbh = C4::Context->dbh;
49 $|=1; # flushes output
52 Deal with virtualshelves
55 my $DBversion = "3.00.00.001";
56 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
57 # update virtualshelves table to
59 $dbh->do("ALTER TABLE `bookshelf` RENAME `virtualshelves`");
60 $dbh->do("ALTER TABLE `shelfcontents` RENAME `virtualshelfcontents`");
61 $dbh->do("ALTER TABLE `virtualshelfcontents` ADD `biblionumber` INT( 11 ) NOT NULL");
62 $dbh->do("UPDATE `virtualshelfcontents` SET biblionumber=(SELECT biblionumber FROM items WHERE items.itemnumber=virtualshelfcontents.itemnumber)");
63 # drop all foreign keys : otherwise, we can't drop itemnumber field.
64 DropAllForeignKeys('virtualshelfcontents');
65 # create the new foreign keys (on biblionumber)
66 $dbh->do("ALTER TABLE `virtualshelfcontents` ADD FOREIGN KEY biblionumber_fk (biblionumber) REFERENCES biblio (biblionumber) ON UPDATE CASCADE ON DELETE CASCADE");
67 # re-create the foreign key on virtualshelf
68 $dbh->do("ALTER TABLE `virtualshelfcontents` ADD FOREIGN KEY shelfnumber_fk (shelfnumber) REFERENCES virtualshelves (shelfnumber) ON UPDATE CASCADE ON DELETE CASCADE");
69 # now we can drop the itemnumber column
70 $dbh->do("ALTER TABLE `virtualshelfcontents` DROP `itemnumber`");
71 print "Upgrade to $DBversion done (virtualshelves)\n";
72 SetVersion ($DBversion);
76 $DBversion = "3.00.00.002";
77 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
78 $dbh->do("DROP TABLE sessions");
79 $dbh->do("CREATE TABLE `sessions` (
80 `id` char(32) NOT NULL,
81 `a_session` text NOT NULL,
82 UNIQUE KEY `id` (`id`)
83 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;");
84 print "Upgrade to $DBversion done (sessions uses CGI::session, new table structure for sessions)\n";
85 SetVersion ($DBversion);
89 $DBversion = "3.00.00.003";
90 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
91 if (C4::Context->preference("opaclanguages") eq "fr") {
92 $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')");
94 $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')");
96 print "Upgrade to $DBversion done (adding ReservesNeedReturns systempref, in circulation)\n";
97 SetVersion ($DBversion);
101 $DBversion = "3.00.00.004";
102 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
103 $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')");
104 print "Upgrade to $DBversion done (adding DebugLevel systempref, in 'Admin' tab)\n";
105 SetVersion ($DBversion);
108 $DBversion = "3.00.00.005";
109 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
110 $dbh->do("CREATE TABLE `tags` (
111 `entry` varchar(255) NOT NULL default '',
112 `weight` bigint(20) NOT NULL default 0,
113 PRIMARY KEY (`entry`)
114 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
116 $dbh->do("CREATE TABLE `nozebra` (
117 `server` varchar(20) NOT NULL,
118 `indexname` varchar(40) NOT NULL,
119 `value` varchar(250) NOT NULL,
120 `biblionumbers` longtext NOT NULL,
121 KEY `indexname` (`server`,`indexname`),
122 KEY `value` (`server`,`value`))
123 ENGINE=InnoDB DEFAULT CHARSET=utf8;
125 print "Upgrade to $DBversion done (adding tags and nozebra tables )\n";
126 SetVersion ($DBversion);
129 $DBversion = "3.00.00.006";
130 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
131 $dbh->do("UPDATE issues SET issuedate=timestamp WHERE issuedate='0000-00-00'");
132 print "Upgrade to $DBversion done (filled issues.issuedate with timestamp)\n";
133 SetVersion ($DBversion);
136 $DBversion = "3.00.00.007";
137 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
138 $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')");
139 print "Upgrade to $DBversion done (set SessionStorage variable)\n";
140 SetVersion ($DBversion);
143 $DBversion = "3.00.00.008";
144 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
145 $dbh->do("ALTER TABLE `biblio` ADD `datecreated` DATE NOT NULL AFTER `timestamp` ;");
146 $dbh->do("UPDATE biblio SET datecreated=timestamp");
147 print "Upgrade to $DBversion done (biblio creation date)\n";
148 SetVersion ($DBversion);
151 $DBversion = "3.00.00.009";
152 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
154 # Create backups of call number columns
155 # in case default migration needs to be customized
157 # UPGRADE NOTE: temp_upg_biblioitems_call_num should be dropped
158 # after call numbers have been transformed to the new structure
160 # Not bothering to do the same with deletedbiblioitems -- assume
161 # default is good enough.
162 $dbh->do("CREATE TABLE `temp_upg_biblioitems_call_num` AS
163 SELECT `biblioitemnumber`, `biblionumber`,
164 `classification`, `dewey`, `subclass`,
166 FROM `biblioitems`");
168 # biblioitems changes
169 $dbh->do("ALTER TABLE `biblioitems` CHANGE COLUMN `volumeddesc` `volumedesc` TEXT,
170 ADD `cn_source` VARCHAR(10) DEFAULT NULL AFTER `ccode`,
171 ADD `cn_class` VARCHAR(30) DEFAULT NULL AFTER `cn_source`,
172 ADD `cn_item` VARCHAR(10) DEFAULT NULL AFTER `cn_class`,
173 ADD `cn_suffix` VARCHAR(10) DEFAULT NULL AFTER `cn_item`,
174 ADD `cn_sort` VARCHAR(30) DEFAULT NULL AFTER `cn_suffix`,
175 ADD `totalissues` INT(10) AFTER `cn_sort`");
177 # default mapping of call number columns:
178 # cn_class = concatentation of classification + dewey,
179 # trimmed to fit -- assumes that most users do not
180 # populate both classification and dewey in a single record
182 # cn_source = left null
185 # After upgrade, cn_sort will have to be set based on whatever
186 # default call number scheme user sets as a preference. Misc
187 # script will be added at some point to do that.
189 $dbh->do("UPDATE `biblioitems`
190 SET cn_class = SUBSTR(TRIM(CONCAT_WS(' ', `classification`, `dewey`)), 1, 30),
195 # Now drop the old call number columns
196 $dbh->do("ALTER TABLE `biblioitems` DROP COLUMN `classification`,
198 DROP COLUMN `subclass`,
199 DROP COLUMN `lcsort`,
200 DROP COLUMN `ccode`");
202 # deletedbiblio changes
203 $dbh->do("ALTER TABLE `deletedbiblio` ALTER COLUMN `frameworkcode` SET DEFAULT '',
205 ADD `datecreated` DATE NOT NULL AFTER `timestamp`");
206 $dbh->do("UPDATE deletedbiblio SET datecreated = timestamp");
208 # deletedbiblioitems changes
209 $dbh->do("ALTER TABLE `deletedbiblioitems`
210 MODIFY `publicationyear` TEXT,
211 CHANGE `volumeddesc` `volumedesc` TEXT,
212 MODIFY `collectiontitle` MEDIUMTEXT DEFAULT NULL AFTER `volumedesc`,
213 MODIFY `collectionissn` TEXT DEFAULT NULL AFTER `collectiontitle`,
214 MODIFY `collectionvolume` MEDIUMTEXT DEFAULT NULL AFTER `collectionissn`,
215 MODIFY `editionstatement` TEXT DEFAULT NULL AFTER `collectionvolume`,
216 MODIFY `editionresponsibility` TEXT DEFAULT NULL AFTER `editionstatement`,
217 MODIFY `place` VARCHAR(255) DEFAULT NULL AFTER `size`,
219 ADD `cn_source` VARCHAR(10) DEFAULT NULL AFTER `url`,
220 ADD `cn_class` VARCHAR(30) DEFAULT NULL AFTER `cn_source`,
221 ADD `cn_item` VARCHAR(10) DEFAULT NULL AFTER `cn_class`,
222 ADD `cn_suffix` VARCHAR(10) DEFAULT NULL AFTER `cn_item`,
223 ADD `cn_sort` VARCHAR(30) DEFAULT NULL AFTER `cn_suffix`,
224 ADD `totalissues` INT(10) AFTER `cn_sort`,
225 ADD KEY `isbn` (`isbn`),
226 ADD KEY `publishercode` (`publishercode`)
229 $dbh->do("UPDATE `deletedbiblioitems`
230 SET `cn_class` = SUBSTR(TRIM(CONCAT_WS(' ', `classification`, `dewey`)), 1, 30),
231 `cn_item` = `subclass`,
234 $dbh->do("ALTER TABLE `deletedbiblioitems`
235 DROP COLUMN `classification`,
237 DROP COLUMN `subclass`,
238 DROP COLUMN `lcsort`,
242 # deleteditems changes
243 $dbh->do("ALTER TABLE `deleteditems`
244 MODIFY `barcode` VARCHAR(20) DEFAULT NULL,
245 MODIFY `price` DECIMAL(8,2) DEFAULT NULL,
246 MODIFY `replacementprice` DECIMAL(8,2) DEFAULT NULL,
248 MODIFY `itemcallnumber` VARCHAR(30) DEFAULT NULL AFTER `wthdrawn`,
249 MODIFY `holdingbranch` VARCHAR(10) DEFAULT NULL,
251 MODIFY `timestamp` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP AFTER `paidfor`,
253 ADD `cn_source` VARCHAR(10) DEFAULT NULL AFTER `onloan`,
254 ADD `cn_sort` VARCHAR(30) DEFAULT NULL AFTER `cn_source`,
255 ADD `ccode` VARCHAR(10) DEFAULT NULL AFTER `cn_sort`,
256 ADD `materials` VARCHAR(10) DEFAULT NULL AFTER `ccode`,
257 ADD `uri` VARCHAR(255) DEFAULT NULL AFTER `materials`,
258 MODIFY `marc` LONGBLOB AFTER `uri`,
260 DROP KEY `itembarcodeidx`,
261 DROP KEY `itembinoidx`,
262 DROP KEY `itembibnoidx`,
263 ADD UNIQUE KEY `delitembarcodeidx` (`barcode`),
264 ADD KEY `delitembinoidx` (`biblioitemnumber`),
265 ADD KEY `delitembibnoidx` (`biblionumber`),
266 ADD KEY `delhomebranch` (`homebranch`),
267 ADD KEY `delholdingbranch` (`holdingbranch`)");
268 $dbh->do("UPDATE deleteditems SET `ccode` = `itype`");
269 $dbh->do("ALTER TABLE deleteditems DROP `itype`");
270 $dbh->do("UPDATE `deleteditems` SET `cn_sort` = `itemcallnumber`");
273 $dbh->do("ALTER TABLE `items` ADD `cn_source` VARCHAR(10) DEFAULT NULL AFTER `onloan`,
274 ADD `cn_sort` VARCHAR(30) DEFAULT NULL AFTER `cn_source`,
275 ADD `ccode` VARCHAR(10) DEFAULT NULL AFTER `cn_sort`,
276 ADD `materials` VARCHAR(10) DEFAULT NULL AFTER `ccode`,
277 ADD `uri` VARCHAR(255) DEFAULT NULL AFTER `materials`
279 $dbh->do("ALTER TABLE `items`
280 DROP KEY `itembarcodeidx`,
281 ADD UNIQUE KEY `itembarcodeidx` (`barcode`)");
283 # map items.itype to items.ccode and
284 # set cn_sort to itemcallnumber -- as with biblioitems.cn_sort,
285 # will have to be subsequently updated per user's default
286 # classification scheme
287 $dbh->do("UPDATE `items` SET `cn_sort` = `itemcallnumber`,
290 $dbh->do("ALTER TABLE `items` DROP `cutterextra`,
293 print "Upgrade to $DBversion done (major changes to biblio, biblioitems, items, and deleted* versions of same\n";
294 SetVersion ($DBversion);
297 $DBversion = "3.00.00.010";
298 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
299 $dbh->do("CREATE INDEX `userid` ON borrowers (`userid`) ");
300 print "Upgrade to $DBversion done (userid index added)\n";
301 SetVersion ($DBversion);
304 $DBversion = "3.00.00.011";
305 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
306 $dbh->do("ALTER TABLE `branchcategories` CHANGE `categorycode` `categorycode` char(10) ");
307 $dbh->do("ALTER TABLE `branchcategories` CHANGE `categoryname` `categoryname` varchar(32) ");
308 $dbh->do("ALTER TABLE `branchcategories` ADD COLUMN `categorytype` varchar(16) ");
309 $dbh->do("UPDATE `branchcategories` SET `categorytype` = 'properties'");
310 $dbh->do("ALTER TABLE `branchrelations` CHANGE `categorycode` `categorycode` char(10) ");
311 print "Upgrade to $DBversion done (added branchcategory type)\n";
312 SetVersion ($DBversion);
315 $DBversion = "3.00.00.012";
316 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
317 $dbh->do("CREATE TABLE `class_sort_rules` (
318 `class_sort_rule` varchar(10) NOT NULL default '',
319 `description` mediumtext,
320 `sort_routine` varchar(30) NOT NULL default '',
321 PRIMARY KEY (`class_sort_rule`),
322 UNIQUE KEY `class_sort_rule_idx` (`class_sort_rule`)
323 ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
324 $dbh->do("CREATE TABLE `class_sources` (
325 `cn_source` varchar(10) NOT NULL default '',
326 `description` mediumtext,
327 `used` tinyint(4) NOT NULL default 0,
328 `class_sort_rule` varchar(10) NOT NULL default '',
329 PRIMARY KEY (`cn_source`),
330 UNIQUE KEY `cn_source_idx` (`cn_source`),
331 KEY `used_idx` (`used`),
332 CONSTRAINT `class_source_ibfk_1` FOREIGN KEY (`class_sort_rule`)
333 REFERENCES `class_sort_rules` (`class_sort_rule`)
334 ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
335 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type)
336 VALUES('DefaultClassificationSource','ddc',
337 'Default classification scheme used by the collection. E.g., Dewey, LCC, etc.', NULL,'free')");
338 $dbh->do("INSERT INTO `class_sort_rules` (`class_sort_rule`, `description`, `sort_routine`) VALUES
339 ('dewey', 'Default filing rules for DDC', 'Dewey'),
340 ('lcc', 'Default filing rules for LCC', 'LCC'),
341 ('generic', 'Generic call number filing rules', 'Generic')");
342 $dbh->do("INSERT INTO `class_sources` (`cn_source`, `description`, `used`, `class_sort_rule`) VALUES
343 ('ddc', 'Dewey Decimal Classification', 1, 'dewey'),
344 ('lcc', 'Library of Congress Classification', 1, 'lcc'),
345 ('udc', 'Universal Decimal Classification', 0, 'generic'),
346 ('sudocs', 'SuDoc Classification (U.S. GPO)', 0, 'generic'),
347 ('z', 'Other/Generic Classification Scheme', 0, 'generic')");
348 print "Upgrade to $DBversion done (classification sources added)\n";
349 SetVersion ($DBversion);
352 $DBversion = "3.00.00.013";
353 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
354 $dbh->do("CREATE TABLE `import_batches` (
355 `import_batch_id` int(11) NOT NULL auto_increment,
356 `template_id` int(11) default NULL,
357 `branchcode` varchar(10) default NULL,
358 `num_biblios` int(11) NOT NULL default 0,
359 `num_items` int(11) NOT NULL default 0,
360 `upload_timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP,
361 `overlay_action` enum('replace', 'create_new', 'use_template') NOT NULL default 'create_new',
362 `import_status` enum('staging', 'staged', 'importing', 'imported', 'reverting', 'reverted', 'cleaned') NOT NULL default 'staging',
363 `batch_type` enum('batch', 'z3950') NOT NULL default 'batch',
364 `file_name` varchar(100),
365 `comments` mediumtext,
366 PRIMARY KEY (`import_batch_id`),
367 KEY `branchcode` (`branchcode`)
368 ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
369 $dbh->do("CREATE TABLE `import_records` (
370 `import_record_id` int(11) NOT NULL auto_increment,
371 `import_batch_id` int(11) NOT NULL,
372 `branchcode` varchar(10) default NULL,
373 `record_sequence` int(11) NOT NULL default 0,
374 `upload_timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP,
375 `import_date` DATE default NULL,
376 `marc` longblob NOT NULL,
377 `marcxml` longtext NOT NULL,
378 `marcxml_old` longtext NOT NULL,
379 `record_type` enum('biblio', 'auth', 'holdings') NOT NULL default 'biblio',
380 `overlay_status` enum('no_match', 'auto_match', 'manual_match', 'match_applied') NOT NULL default 'no_match',
381 `status` enum('error', 'staged', 'imported', 'reverted', 'items_reverted') NOT NULL default 'staged',
382 `import_error` mediumtext,
383 `encoding` varchar(40) NOT NULL default '',
384 `z3950random` varchar(40) default NULL,
385 PRIMARY KEY (`import_record_id`),
386 CONSTRAINT `import_records_ifbk_1` FOREIGN KEY (`import_batch_id`)
387 REFERENCES `import_batches` (`import_batch_id`) ON DELETE CASCADE ON UPDATE CASCADE,
388 KEY `branchcode` (`branchcode`),
389 KEY `batch_sequence` (`import_batch_id`, `record_sequence`)
390 ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
391 $dbh->do("CREATE TABLE `import_record_matches` (
392 `import_record_id` int(11) NOT NULL,
393 `candidate_match_id` int(11) NOT NULL,
394 `score` int(11) NOT NULL default 0,
395 CONSTRAINT `import_record_matches_ibfk_1` FOREIGN KEY (`import_record_id`)
396 REFERENCES `import_records` (`import_record_id`) ON DELETE CASCADE ON UPDATE CASCADE,
397 KEY `record_score` (`import_record_id`, `score`)
398 ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
399 $dbh->do("CREATE TABLE `import_biblios` (
400 `import_record_id` int(11) NOT NULL,
401 `matched_biblionumber` int(11) default NULL,
402 `control_number` varchar(25) default NULL,
403 `original_source` varchar(25) default NULL,
404 `title` varchar(128) default NULL,
405 `author` varchar(80) default NULL,
406 `isbn` varchar(14) default NULL,
407 `issn` varchar(9) default NULL,
408 `has_items` tinyint(1) NOT NULL default 0,
409 CONSTRAINT `import_biblios_ibfk_1` FOREIGN KEY (`import_record_id`)
410 REFERENCES `import_records` (`import_record_id`) ON DELETE CASCADE ON UPDATE CASCADE,
411 KEY `matched_biblionumber` (`matched_biblionumber`),
412 KEY `title` (`title`),
414 ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
415 $dbh->do("CREATE TABLE `import_items` (
416 `import_items_id` int(11) NOT NULL auto_increment,
417 `import_record_id` int(11) NOT NULL,
418 `itemnumber` int(11) default NULL,
419 `branchcode` varchar(10) default NULL,
420 `status` enum('error', 'staged', 'imported', 'reverted') NOT NULL default 'staged',
421 `marcxml` longtext NOT NULL,
422 `import_error` mediumtext,
423 PRIMARY KEY (`import_items_id`),
424 CONSTRAINT `import_items_ibfk_1` FOREIGN KEY (`import_record_id`)
425 REFERENCES `import_records` (`import_record_id`) ON DELETE CASCADE ON UPDATE CASCADE,
426 KEY `itemnumber` (`itemnumber`),
427 KEY `branchcode` (`branchcode`)
428 ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
430 $dbh->do("INSERT INTO `import_batches`
431 (`overlay_action`, `import_status`, `batch_type`, `file_name`)
432 SELECT distinct 'create_new', 'staged', 'z3950', `file`
433 FROM `marc_breeding`");
435 $dbh->do("INSERT INTO `import_records`
436 (`import_batch_id`, `import_record_id`, `record_sequence`, `marc`, `record_type`, `status`,
437 `encoding`, `z3950random`, `marcxml`, `marcxml_old`)
438 SELECT `import_batch_id`, `id`, 1, `marc`, 'biblio', 'staged', `encoding`, `z3950random`, '', ''
440 JOIN `import_batches` ON (`file_name` = `file`)");
442 $dbh->do("INSERT INTO `import_biblios`
443 (`import_record_id`, `title`, `author`, `isbn`)
444 SELECT `import_record_id`, `title`, `author`, `isbn`
446 JOIN `import_records` ON (`import_record_id` = `id`)");
448 $dbh->do("UPDATE `import_batches`
449 SET `num_biblios` = (
451 FROM `import_records`
452 WHERE `import_batch_id` = `import_batches`.`import_batch_id`
455 $dbh->do("DROP TABLE `marc_breeding`");
457 print "Upgrade to $DBversion done (import_batches et al. added)\n";
458 SetVersion ($DBversion);
461 $DBversion = "3.00.00.014";
462 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
463 $dbh->do("ALTER TABLE subscription ADD lastbranch VARCHAR(4)");
464 print "Upgrade to $DBversion done (userid index added)\n";
465 SetVersion ($DBversion);
468 $DBversion = "3.00.00.015";
469 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
470 $dbh->do("CREATE TABLE `saved_sql` (
471 `id` int(11) NOT NULL auto_increment,
472 `borrowernumber` int(11) default NULL,
473 `date_created` datetime default NULL,
474 `last_modified` datetime default NULL,
476 `last_run` datetime default NULL,
477 `report_name` varchar(255) default NULL,
478 `type` varchar(255) default NULL,
481 KEY boridx (`borrowernumber`)
482 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;");
483 $dbh->do("CREATE TABLE `saved_reports` (
484 `id` int(11) NOT NULL auto_increment,
485 `report_id` int(11) default NULL,
487 `date_run` datetime default NULL,
489 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;");
490 print "Upgrade to $DBversion done (saved_sql and saved_reports added)\n";
491 SetVersion ($DBversion);
494 $DBversion = "3.00.00.016";
495 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
496 $dbh->do(" CREATE TABLE reports_dictionary (
497 id int(11) NOT NULL auto_increment,
498 name varchar(255) default NULL,
500 date_created datetime default NULL,
501 date_modified datetime default NULL,
503 area int(11) default NULL,
505 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ");
506 print "Upgrade to $DBversion done (reports_dictionary) added)\n";
507 SetVersion ($DBversion);
510 $DBversion = "3.00.00.017";
511 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
512 $dbh->do("ALTER TABLE action_logs DROP PRIMARY KEY");
513 $dbh->do("ALTER TABLE action_logs ADD KEY timestamp (timestamp,user)");
514 $dbh->do("ALTER TABLE action_logs ADD action_id INT(11) NOT NULL FIRST");
515 $dbh->do("UPDATE action_logs SET action_id = if (\@a, \@a:=\@a+1, \@a:=1)");
516 $dbh->do("ALTER TABLE action_logs MODIFY action_id INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY");
517 print "Upgrade to $DBversion done (added column to action_logs)\n";
518 SetVersion ($DBversion);
521 $DBversion = "3.00.00.018";
522 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
523 $dbh->do("ALTER TABLE `zebraqueue`
524 ADD `done` INT NOT NULL DEFAULT '0',
525 ADD `time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ;
527 print "Upgrade to $DBversion done (adding timestamp and done columns to zebraque table to improve problem tracking) added)\n";
528 SetVersion ($DBversion);
531 $DBversion = "3.00.00.019";
532 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
533 $dbh->do("ALTER TABLE biblio MODIFY biblionumber INT(11) NOT NULL AUTO_INCREMENT");
534 $dbh->do("ALTER TABLE biblioitems MODIFY biblioitemnumber INT(11) NOT NULL AUTO_INCREMENT");
535 $dbh->do("ALTER TABLE items MODIFY itemnumber INT(11) NOT NULL AUTO_INCREMENT");
536 print "Upgrade to $DBversion done (made bib/item PKs auto_increment)\n";
537 SetVersion ($DBversion);
540 $DBversion = "3.00.00.020";
541 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
542 $dbh->do("ALTER TABLE deleteditems
543 DROP KEY `delitembarcodeidx`,
544 ADD KEY `delitembarcodeidx` (`barcode`)");
545 print "Upgrade to $DBversion done (dropped uniqueness of key on deleteditems.barcode)\n";
546 SetVersion ($DBversion);
549 $DBversion = "3.00.00.021";
550 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
551 $dbh->do("ALTER TABLE items CHANGE homebranch homebranch VARCHAR(10)");
552 $dbh->do("ALTER TABLE deleteditems CHANGE homebranch homebranch VARCHAR(10)");
553 $dbh->do("ALTER TABLE statistics CHANGE branch branch VARCHAR(10)");
554 $dbh->do("ALTER TABLE subscription CHANGE lastbranch lastbranch VARCHAR(10)");
555 print "Upgrade to $DBversion done (extended missed branchcode columns to 10 chars)\n";
556 SetVersion ($DBversion);
559 $DBversion = "3.00.00.022";
560 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
561 $dbh->do("ALTER TABLE items
562 ADD `damaged` tinyint(1) default NULL");
563 print "Upgrade to $DBversion done (adding damaged column to items table)\n";
564 SetVersion ($DBversion);
567 $DBversion = "3.00.00.023";
568 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
569 $dbh->do("INSERT INTO `systempreferences` (variable,value,options,explanation,type)
570 VALUES ('yuipath','http://yui.yahooapis.com/2.3.1/build','Insert the path to YUI libraries','','free')");
571 print "Upgrade to $DBversion done (adding new system preference for controlling YUI path)\n";
572 SetVersion ($DBversion);
575 $DBversion = "3.00.00.024";
576 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
577 $dbh->do("ALTER TABLE biblioitems CHANGE itemtype itemtype VARCHAR(10)");
578 print "Upgrade to $DBversion done (changing itemtype to (10))\n";
579 SetVersion ($DBversion);
582 $DBversion = "3.00.00.025";
583 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
584 $dbh->do("ALTER TABLE items ADD COLUMN itype VARCHAR(10)");
585 if(C4::Context->preference('item-level_itypes')){
586 $dbh->do('update items,biblioitems set items.itype=biblioitems.itemtype where items.biblionumber=biblioitems.biblionumber and itype is null');
588 print "Upgrade to $DBversion done (reintroduce items.itype - fill from itemtype)\n ";
589 SetVersion ($DBversion);
592 $DBversion = "3.00.00.026";
593 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
594 $dbh->do("INSERT INTO `systempreferences` (variable,value,options,explanation,type)
595 VALUES ('HomeOrHoldingBranch','homebranch','homebranch|holdingbranch','With independent branches turned on this decides whether to check the items holdingbranch or homebranch at circulatilon','choice')");
596 print "Upgrade to $DBversion done (adding new system preference for choosing whether homebranch or holdingbranch is checked in circulation)\n";
597 SetVersion ($DBversion);
600 $DBversion = "3.00.00.027";
601 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
602 $dbh->do("CREATE TABLE `marc_matchers` (
603 `matcher_id` int(11) NOT NULL auto_increment,
604 `code` varchar(10) NOT NULL default '',
605 `description` varchar(255) NOT NULL default '',
606 `record_type` varchar(10) NOT NULL default 'biblio',
607 `threshold` int(11) NOT NULL default 0,
608 PRIMARY KEY (`matcher_id`),
610 KEY `record_type` (`record_type`)
611 ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
612 $dbh->do("CREATE TABLE `matchpoints` (
613 `matcher_id` int(11) NOT NULL,
614 `matchpoint_id` int(11) NOT NULL auto_increment,
615 `search_index` varchar(30) NOT NULL default '',
616 `score` int(11) NOT NULL default 0,
617 PRIMARY KEY (`matchpoint_id`),
618 CONSTRAINT `matchpoints_ifbk_1` FOREIGN KEY (`matcher_id`)
619 REFERENCES `marc_matchers` (`matcher_id`) ON DELETE CASCADE ON UPDATE CASCADE
620 ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
621 $dbh->do("CREATE TABLE `matchpoint_components` (
622 `matchpoint_id` int(11) NOT NULL,
623 `matchpoint_component_id` int(11) NOT NULL auto_increment,
624 sequence int(11) NOT NULL default 0,
625 tag varchar(3) NOT NULL default '',
626 subfields varchar(40) NOT NULL default '',
627 offset int(4) NOT NULL default 0,
628 length int(4) NOT NULL default 0,
629 PRIMARY KEY (`matchpoint_component_id`),
630 KEY `by_sequence` (`matchpoint_id`, `sequence`),
631 CONSTRAINT `matchpoint_components_ifbk_1` FOREIGN KEY (`matchpoint_id`)
632 REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE
633 ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
634 $dbh->do("CREATE TABLE `matchpoint_component_norms` (
635 `matchpoint_component_id` int(11) NOT NULL,
636 `sequence` int(11) NOT NULL default 0,
637 `norm_routine` varchar(50) NOT NULL default '',
638 KEY `matchpoint_component_norms` (`matchpoint_component_id`, `sequence`),
639 CONSTRAINT `matchpoint_component_norms_ifbk_1` FOREIGN KEY (`matchpoint_component_id`)
640 REFERENCES `matchpoint_components` (`matchpoint_component_id`) ON DELETE CASCADE ON UPDATE CASCADE
641 ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
642 $dbh->do("CREATE TABLE `matcher_matchpoints` (
643 `matcher_id` int(11) NOT NULL,
644 `matchpoint_id` int(11) NOT NULL,
645 CONSTRAINT `matcher_matchpoints_ifbk_1` FOREIGN KEY (`matcher_id`)
646 REFERENCES `marc_matchers` (`matcher_id`) ON DELETE CASCADE ON UPDATE CASCADE,
647 CONSTRAINT `matcher_matchpoints_ifbk_2` FOREIGN KEY (`matchpoint_id`)
648 REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE
649 ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
650 $dbh->do("CREATE TABLE `matchchecks` (
651 `matcher_id` int(11) NOT NULL,
652 `matchcheck_id` int(11) NOT NULL auto_increment,
653 `source_matchpoint_id` int(11) NOT NULL,
654 `target_matchpoint_id` int(11) NOT NULL,
655 PRIMARY KEY (`matchcheck_id`),
656 CONSTRAINT `matcher_matchchecks_ifbk_1` FOREIGN KEY (`matcher_id`)
657 REFERENCES `marc_matchers` (`matcher_id`) ON DELETE CASCADE ON UPDATE CASCADE,
658 CONSTRAINT `matcher_matchchecks_ifbk_2` FOREIGN KEY (`source_matchpoint_id`)
659 REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE,
660 CONSTRAINT `matcher_matchchecks_ifbk_3` FOREIGN KEY (`target_matchpoint_id`)
661 REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE
662 ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
663 print "Upgrade to $DBversion done (added C4::Matcher serialization tables)\n ";
664 SetVersion ($DBversion);
667 $DBversion = "3.00.00.028";
668 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
669 $dbh->do("INSERT INTO `systempreferences` (variable,value,options,explanation,type)
670 VALUES ('canreservefromotherbranches','1','','With Independent branches on, can a user from one library reserve an item from another library','YesNo')");
671 print "Upgrade to $DBversion done (adding new system preference for changing reserve/holds behaviour with independent branches)\n";
672 SetVersion ($DBversion);
676 $DBversion = "3.00.00.029";
677 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
678 $dbh->do("ALTER TABLE `import_batches` ADD `matcher_id` int(11) NULL AFTER `import_batch_id`");
679 print "Upgrade to $DBversion done (adding matcher_id to import_batches)\n";
680 SetVersion ($DBversion);
683 $DBversion = "3.00.00.030";
684 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
686 CREATE TABLE services_throttle (
687 service_type varchar(10) NOT NULL default '',
688 service_count varchar(45) default NULL,
689 PRIMARY KEY (service_type)
690 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
692 $dbh->do("INSERT INTO `systempreferences` (variable,value,options,explanation,type)
693 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')");
694 $dbh->do("INSERT INTO `systempreferences` (variable,value,options,explanation,type)
695 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')");
696 $dbh->do("INSERT INTO `systempreferences` (variable,value,options,explanation,type)
697 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')");
698 $dbh->do("INSERT INTO `systempreferences` (variable,value,options,explanation,type)
699 VALUES ('XISBNDailyLimit',499,'','The xISBN Web service is free for non-commercial use when usage does not exceed 500 requests per day','free')");
700 $dbh->do("INSERT INTO `systempreferences` (variable,value,options,explanation,type)
701 VALUES ('PINESISBN',0,'','Use with FRBRizeEditions. If ON, Koha will use PINES OISBN web service in the Editions tab on the detail pages.','YesNo')");
702 $dbh->do("INSERT INTO `systempreferences` (variable,value,options,explanation,type)
703 VALUES ('ThingISBN',0,'','Use with FRBRizeEditions. If ON, Koha will use the ThingISBN web service in the Editions tab on the detail pages.','YesNo')");
704 print "Upgrade to $DBversion done (adding services throttle table and sysprefs for xISBN)\n";
705 SetVersion ($DBversion);
708 $DBversion = "3.00.00.031";
709 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
711 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('QueryStemming',1,'If ON, enables query stemming',NULL,'YesNo')");
712 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('QueryFuzzy',1,'If ON, enables fuzzy option for searches',NULL,'YesNo')");
713 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('QueryWeightFields',1,'If ON, enables field weighting',NULL,'YesNo')");
714 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('WebBasedSelfCheck',0,'If ON, enables the web-based self-check system',NULL,'YesNo')");
715 $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')");
716 $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')");
717 $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')");
718 $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')");
719 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('defaultSortOrder',NULL,'Specify the default sort order','asc|dsc|az|za','Choice')");
720 $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')");
721 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('OPACdefaultSortOrder',NULL,'Specify the default sort order','asc|dsc|za|az','Choice')");
722 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('staffClientBaseURL','','Specify the base URL of the staff client',NULL,'free')");
723 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('minPasswordLength',3,'Specify the minimum length of a patron/staff password',NULL,'free')");
724 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('noItemTypeImages',0,'If ON, disables item-type images',NULL,'YesNo')");
725 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('noOPACHolds',0,'If ON, disables holds globally',NULL,'YesNo')");
726 $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')");
727 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('holdCancelLength','','Specify how many days before a hold is canceled',NULL,'free')");
728 $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')");
729 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('finesMode','test','Choose the fines mode, test or production','test|production','Choice')");
730 $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')");
731 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('itemBarcodeInputFilter','','If set, allows specification of a item barcode input filter','cuecat','Choice')");
732 $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')");
733 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('URLLinkText','','Text to display as the link anchor in the OPAC',NULL,'free')");
734 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('noOPACUserLogin',0,'If ON, disables the OPAC User Login',NULL,'YesNo')");
735 $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')");
736 $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')");
737 $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')");
738 $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')");
739 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('OPACUserCSS',0,'Add CSS to be included in the OPAC',NULL,'free')");
740 $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')");
742 print "Upgrade to $DBversion done (adding additional system preference)\n";
743 SetVersion ($DBversion);
746 $DBversion = "3.00.00.032";
747 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
748 $dbh->do("UPDATE `marc_subfield_structure` SET `kohafield` = 'items.wthdrawn' WHERE `kohafield` = 'items.withdrawn'");
749 print "Upgrade to $DBversion done (fixed MARC framework references to items.withdrawn)\n";
750 SetVersion ($DBversion);
753 $DBversion = "3.00.00.033";
754 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
755 $dbh->do("INSERT INTO `userflags` VALUES(17,'staffaccess','Modify login / permissions for staff users',0)");
756 print "Upgrade to $DBversion done (Adding permissions flag for staff member access modification. )\n";
757 SetVersion ($DBversion);
760 $DBversion = "3.00.00.034";
761 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
762 $dbh->do("ALTER TABLE `virtualshelves` ADD COLUMN `sortfield` VARCHAR(16) ");
763 print "Upgrade to $DBversion done (Adding sortfield for Virtual Shelves. )\n";
764 SetVersion ($DBversion);
767 $DBversion = "3.00.00.035";
768 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
769 $dbh->do("UPDATE marc_subfield_structure
770 SET authorised_value = 'cn_source'
771 WHERE kohafield IN ('items.cn_source', 'biblioitems.cn_source')
772 AND (authorised_value is NULL OR authorised_value = '')");
773 print "Upgrade to $DBversion done (MARC frameworks: make classification source a drop-down)\n";
774 SetVersion ($DBversion);
777 $DBversion = "3.00.00.036";
778 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
779 $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');");
780 print "Upgrade to $DBversion done (OPACItemsResultsDisplay systempreference added)\n";
781 SetVersion ($DBversion);
784 $DBversion = "3.00.00.037";
785 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
786 $dbh->do("ALTER TABLE `borrowers` ADD COLUMN `altcontactfirstname` varchar(255)");
787 $dbh->do("ALTER TABLE `borrowers` ADD COLUMN `altcontactsurname` varchar(255)");
788 $dbh->do("ALTER TABLE `borrowers` ADD COLUMN `altcontactaddress1` varchar(255)");
789 $dbh->do("ALTER TABLE `borrowers` ADD COLUMN `altcontactaddress2` varchar(255)");
790 $dbh->do("ALTER TABLE `borrowers` ADD COLUMN `altcontactaddress3` varchar(255)");
791 $dbh->do("ALTER TABLE `borrowers` ADD COLUMN `altcontactzipcode` varchar(50)");
792 $dbh->do("ALTER TABLE `borrowers` ADD COLUMN `altcontactphone` varchar(50)");
793 print "Upgrade to $DBversion done (Adding Alternative Contact Person information to borrowers table)\n";
794 SetVersion ($DBversion);
797 $DBversion = "3.00.00.038";
798 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
799 $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'");
800 $dbh->do("DELETE FROM `systempreferences` WHERE variable='hideBiblioNumber'");
801 print "Upgrade to $DBversion done ('alter finesMode systempreference, remove superfluous syspref.')\n";
802 SetVersion ($DBversion);
805 $DBversion = "3.00.00.039";
806 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
807 $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')");
808 $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')");
809 $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')");
810 $dbh->do("DELETE FROM `systempreferences` WHERE variable='HomeOrHoldingBranch'");
811 print "Upgrade to $DBversion done ('add circ sysprefs CircControl, finesCalendar, and uppercasesurnames, and delete HomeOrHoldingBranch.')\n";
812 SetVersion ($DBversion);
815 $DBversion = "3.00.00.040";
816 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
817 $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')");
818 $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')");
819 print "Upgrade to $DBversion done ('add circ sysprefs todaysIssuesDefaultSortOrder and previousIssuesDefaultSortOrder.')\n";
820 SetVersion ($DBversion);
824 $DBversion = "3.00.00.041";
825 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
826 # Strictly speaking it is not necessary to explicitly change
827 # NULL values to 0, because the ALTER TABLE statement will do that.
828 # However, setting them first avoids a warning.
829 $dbh->do("UPDATE items SET notforloan = 0 WHERE notforloan IS NULL");
830 $dbh->do("UPDATE items SET damaged = 0 WHERE damaged IS NULL");
831 $dbh->do("UPDATE items SET itemlost = 0 WHERE itemlost IS NULL");
832 $dbh->do("UPDATE items SET wthdrawn = 0 WHERE wthdrawn IS NULL");
833 $dbh->do("ALTER TABLE items
834 MODIFY notforloan tinyint(1) NOT NULL default 0,
835 MODIFY damaged tinyint(1) NOT NULL default 0,
836 MODIFY itemlost tinyint(1) NOT NULL default 0,
837 MODIFY wthdrawn tinyint(1) NOT NULL default 0");
838 print "Upgrade to $DBversion done (disallow NULL in several item status columns)\n";
839 SetVersion ($DBversion);
842 $DBversion = "3.00.00.042";
843 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
844 $dbh->do("ALTER TABLE aqbooksellers CHANGE name name mediumtext NOT NULL");
845 print "Upgrade to $DBversion done (disallow NULL in aqbooksellers.name; part of fix for bug 1251)\n";
846 SetVersion ($DBversion);
849 $DBversion = "3.00.00.043";
850 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
851 $dbh->do("ALTER TABLE `currency` ADD `symbol` varchar(5) default NULL, ADD `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP");
852 print "Upgrade to $DBversion done (currency table: add symbol and timestamp columns)\n";
853 SetVersion ($DBversion);
856 $DBversion = "3.00.00.044";
857 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
858 $dbh->do("ALTER TABLE deletedborrowers
859 ADD `altcontactfirstname` varchar(255) default NULL,
860 ADD `altcontactsurname` varchar(255) default NULL,
861 ADD `altcontactaddress1` varchar(255) default NULL,
862 ADD `altcontactaddress2` varchar(255) default NULL,
863 ADD `altcontactaddress3` varchar(255) default NULL,
864 ADD `altcontactzipcode` varchar(50) default NULL,
865 ADD `altcontactphone` varchar(50) default NULL
867 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES
868 ('OPACBaseURL',NULL,'Specify the Base URL of the OPAC, e.g., opac.mylibrary.com, the http:// will be added automatically by Koha.',NULL,'Free'),
869 ('language','en','Set the default language in the staff client.',NULL,'Languages'),
870 ('QueryAutoTruncate',1,'If ON, query truncation is enabled by default',NULL,'YesNo'),
871 ('QueryRemoveStopwords',0,'If ON, stopwords listed in the Administration area will be removed from queries',NULL,'YesNo')
873 print "Upgrade to $DBversion done (syncing deletedborrowers table with borrowers table)\n";
874 SetVersion ($DBversion);
877 #-- http://www.w3.org/International/articles/language-tags/
880 $DBversion = "3.00.00.045";
881 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
883 CREATE TABLE language_subtag_registry (
885 type varchar(25), -- language-script-region-variant-extension-privateuse
886 description varchar(25), -- only one of the possible descriptions for ease of reference, see language_descriptions for the complete list
888 KEY `subtag` (`subtag`)
889 ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
891 #-- TODO: add suppress_scripts
892 #-- this maps three letter codes defined in iso639.2 back to their
893 #-- two letter equivilents in rfc4646 (LOC maintains iso639+)
894 $dbh->do("CREATE TABLE language_rfc4646_to_iso639 (
895 rfc4646_subtag varchar(25),
896 iso639_2_code varchar(25),
897 KEY `rfc4646_subtag` (`rfc4646_subtag`)
898 ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
900 $dbh->do("CREATE TABLE language_descriptions (
904 description varchar(255),
906 ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
908 #-- bi-directional support, keyed by script subcode
909 $dbh->do("CREATE TABLE language_script_bidi (
910 rfc4646_subtag varchar(25), -- script subtag, Arab, Hebr, etc.
911 bidi varchar(3), -- rtl ltr
912 KEY `rfc4646_subtag` (`rfc4646_subtag`)
913 ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
915 #-- BIDI Stuff, Arabic and Hebrew
916 $dbh->do("INSERT INTO language_script_bidi(rfc4646_subtag,bidi)
917 VALUES( 'Arab', 'rtl')");
918 $dbh->do("INSERT INTO language_script_bidi(rfc4646_subtag,bidi)
919 VALUES( 'Hebr', 'rtl')");
921 #-- TODO: need to map language subtags to script subtags for detection
922 #-- of bidi when script is not specified (like ar, he)
923 $dbh->do("CREATE TABLE language_script_mapping (
924 language_subtag varchar(25),
925 script_subtag varchar(25),
926 KEY `language_subtag` (`language_subtag`)
927 ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
929 #-- Default mappings between script and language subcodes
930 $dbh->do("INSERT INTO language_script_mapping(language_subtag,script_subtag)
931 VALUES( 'ar', 'Arab')");
932 $dbh->do("INSERT INTO language_script_mapping(language_subtag,script_subtag)
933 VALUES( 'he', 'Hebr')");
935 print "Upgrade to $DBversion done (adding language subtag registry and basic BiDi support NOTE: You should import the subtag registry SQL)\n";
936 SetVersion ($DBversion);
939 $DBversion = "3.00.00.046";
940 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
941 $dbh->do("ALTER TABLE `subscription` CHANGE `numberlength` `numberlength` int(11) default NULL ,
942 CHANGE `weeklength` `weeklength` int(11) default NULL");
943 $dbh->do("CREATE TABLE `serialitems` (`serialid` int(11) NOT NULL, `itemnumber` int(11) NOT NULL, UNIQUE KEY (`serialid`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
944 $dbh->do("INSERT INTO `serialitems` SELECT `serialid`,`itemnumber` from serial where NOT ISNULL(itemnumber) && itemnumber <> '' && itemnumber NOT LIKE '%,%'");
945 print "Upgrade to $DBversion done (Add serialitems table to link serial issues to items. )\n";
946 SetVersion ($DBversion);
949 $DBversion = "3.00.00.047";
950 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
951 $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');");
952 print "Upgrade to $DBversion done ( Added OpacRenewalAllowed syspref )\n";
953 SetVersion ($DBversion);
956 $DBversion = "3.00.00.048";
957 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
958 $dbh->do("ALTER TABLE `items` ADD `more_subfields_xml` longtext default NULL");
959 print "Upgrade to $DBversion done (added items.more_subfields_xml)\n";
960 SetVersion ($DBversion);
963 $DBversion = "3.00.00.049";
964 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
965 $dbh->do("ALTER TABLE `z3950servers` ADD `encoding` text default NULL ");
966 print "Upgrade to $DBversion done ( Added encoding field to z3950servers table )\n";
967 SetVersion ($DBversion);
970 $DBversion = "3.00.00.050";
971 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
972 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('OpacHighlightedWords','0','If Set, query matched terms are highlighted in OPAC',NULL,'YesNo');");
973 print "Upgrade to $DBversion done ( Added OpacHighlightedWords syspref )\n";
974 SetVersion ($DBversion);
977 $DBversion = "3.00.00.051";
978 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
979 $dbh->do("UPDATE systempreferences SET explanation = 'Define the current theme for the OPAC interface.' WHERE variable = 'opacthemes';");
980 print "Upgrade to $DBversion done ( Corrected opacthemes explanation. )\n";
981 SetVersion ($DBversion);
984 $DBversion = "3.00.00.052";
985 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
986 $dbh->do("ALTER TABLE `deleteditems` ADD `more_subfields_xml` LONGTEXT DEFAULT NULL;");
987 print "Upgrade to $DBversion done ( Adding missing column to deleteditems table. )\n";
988 SetVersion ($DBversion);
991 $DBversion = "3.00.00.053";
992 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
993 $dbh->do("CREATE TABLE `printers_profile` (
994 `prof_id` int(4) NOT NULL auto_increment,
995 `printername` varchar(40) NOT NULL,
996 `tmpl_id` int(4) NOT NULL,
997 `paper_bin` varchar(20) NOT NULL,
998 `offset_horz` float default NULL,
999 `offset_vert` float default NULL,
1000 `creep_horz` float default NULL,
1001 `creep_vert` float default NULL,
1002 `unit` char(20) NOT NULL default 'POINT',
1003 PRIMARY KEY (`prof_id`),
1004 UNIQUE KEY `printername` (`printername`,`tmpl_id`,`paper_bin`),
1005 CONSTRAINT `printers_profile_pnfk_1` FOREIGN KEY (`tmpl_id`) REFERENCES `labels_templates` (`tmpl_id`) ON DELETE CASCADE ON UPDATE CASCADE
1006 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ");
1007 $dbh->do("CREATE TABLE `labels_profile` (
1008 `tmpl_id` int(4) NOT NULL,
1009 `prof_id` int(4) NOT NULL,
1010 UNIQUE KEY `tmpl_id` (`tmpl_id`),
1011 UNIQUE KEY `prof_id` (`prof_id`)
1012 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ");
1013 print "Upgrade to $DBversion done ( Printer Profile tables added )\n";
1014 SetVersion ($DBversion);
1017 $DBversion = "3.00.00.054";
1018 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1019 $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';");
1020 print "Upgrade to $DBversion done ( Added another barcode autogeneration sequence to barcode.pl. )\n";
1021 SetVersion ($DBversion);
1024 $DBversion = "3.00.00.055";
1025 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1026 $dbh->do("ALTER TABLE `zebraqueue` ADD KEY `zebraqueue_lookup` (`server`, `biblio_auth_number`, `operation`, `done`)");
1027 print "Upgrade to $DBversion done ( Added index on zebraqueue. )\n";
1028 SetVersion ($DBversion);
1030 $DBversion = "3.00.00.056";
1031 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1032 $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) ");
1033 $dbh->do("ALTER TABLE `items` ADD `enumchron` VARCHAR(80) DEFAULT NULL;");
1034 print "Upgrade to $DBversion done ( Added item.enumchron column, and framework map to 952h )\n";
1035 SetVersion ($DBversion);
1038 $DBversion = "3.00.00.057";
1039 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1040 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('OAI-PMH','0','if ON, OAI-PMH server is enabled',NULL,'YesNo');");
1041 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('OAI-PMH:archiveID','KOHA-OAI-TEST','OAI-PMH archive identification',NULL,'Free');");
1042 $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');");
1043 $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');");
1044 $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');");
1045 SetVersion ($DBversion);
1048 $DBversion = "3.00.00.058";
1049 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1050 $dbh->do("ALTER TABLE `opac_news`
1051 CHANGE `lang` `lang` VARCHAR( 25 )
1053 COLLATE utf8_general_ci
1055 print "Upgrade to $DBversion done ( lang field in opac_news made longer )\n";
1056 SetVersion ($DBversion);
1059 $DBversion = "3.00.00.059";
1060 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1062 $dbh->do("CREATE TABLE IF NOT EXISTS `labels_templates` (
1063 `tmpl_id` int(4) NOT NULL auto_increment,
1064 `tmpl_code` char(100) default '',
1065 `tmpl_desc` char(100) default '',
1066 `page_width` float default '0',
1067 `page_height` float default '0',
1068 `label_width` float default '0',
1069 `label_height` float default '0',
1070 `topmargin` float default '0',
1071 `leftmargin` float default '0',
1072 `cols` int(2) default '0',
1073 `rows` int(2) default '0',
1074 `colgap` float default '0',
1075 `rowgap` float default '0',
1076 `active` int(1) default NULL,
1077 `units` char(20) default 'PX',
1078 `fontsize` int(4) NOT NULL default '3',
1079 PRIMARY KEY (`tmpl_id`)
1080 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;");
1081 $dbh->do("CREATE TABLE IF NOT EXISTS `printers_profile` (
1082 `prof_id` int(4) NOT NULL auto_increment,
1083 `printername` varchar(40) NOT NULL,
1084 `tmpl_id` int(4) NOT NULL,
1085 `paper_bin` varchar(20) NOT NULL,
1086 `offset_horz` float default NULL,
1087 `offset_vert` float default NULL,
1088 `creep_horz` float default NULL,
1089 `creep_vert` float default NULL,
1090 `unit` char(20) NOT NULL default 'POINT',
1091 PRIMARY KEY (`prof_id`),
1092 UNIQUE KEY `printername` (`printername`,`tmpl_id`,`paper_bin`),
1093 CONSTRAINT `printers_profile_pnfk_1` FOREIGN KEY (`tmpl_id`) REFERENCES `labels_templates` (`tmpl_id`) ON DELETE CASCADE ON UPDATE CASCADE
1094 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ");
1095 print "Upgrade to $DBversion done ( Added labels_templates table if it did not exist. )\n";
1096 SetVersion ($DBversion);
1099 $DBversion = "3.00.00.060";
1100 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1101 $dbh->do("CREATE TABLE IF NOT EXISTS `patronimage` (
1102 `cardnumber` varchar(16) NOT NULL,
1103 `mimetype` varchar(15) NOT NULL,
1104 `imagefile` mediumblob NOT NULL,
1105 PRIMARY KEY (`cardnumber`),
1106 CONSTRAINT `patronimage_fk1` FOREIGN KEY (`cardnumber`) REFERENCES `borrowers` (`cardnumber`) ON DELETE CASCADE ON UPDATE CASCADE
1107 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;");
1108 print "Upgrade to $DBversion done ( Added patronimage table. )\n";
1109 SetVersion ($DBversion);
1112 $DBversion = "3.00.00.061";
1113 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1114 $dbh->do("ALTER TABLE labels_templates ADD COLUMN font char(10) NOT NULL DEFAULT 'TR';");
1115 print "Upgrade to $DBversion done ( Added font column to labels_templates )\n";
1116 SetVersion ($DBversion);
1119 $DBversion = "3.00.00.062";
1120 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1121 $dbh->do("CREATE TABLE `old_issues` (
1122 `borrowernumber` int(11) default NULL,
1123 `itemnumber` int(11) default NULL,
1124 `date_due` date default NULL,
1125 `branchcode` varchar(10) default NULL,
1126 `issuingbranch` varchar(18) default NULL,
1127 `returndate` date default NULL,
1128 `lastreneweddate` date default NULL,
1129 `return` varchar(4) default NULL,
1130 `renewals` tinyint(4) default NULL,
1131 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1132 `issuedate` date default NULL,
1133 KEY `old_issuesborridx` (`borrowernumber`),
1134 KEY `old_issuesitemidx` (`itemnumber`),
1135 KEY `old_bordate` (`borrowernumber`,`timestamp`),
1136 CONSTRAINT `old_issues_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`)
1137 ON DELETE SET NULL ON UPDATE SET NULL,
1138 CONSTRAINT `old_issues_ibfk_2` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`)
1139 ON DELETE SET NULL ON UPDATE SET NULL
1140 ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
1141 $dbh->do("CREATE TABLE `old_reserves` (
1142 `borrowernumber` int(11) default NULL,
1143 `reservedate` date default NULL,
1144 `biblionumber` int(11) default NULL,
1145 `constrainttype` varchar(1) default NULL,
1146 `branchcode` varchar(10) default NULL,
1147 `notificationdate` date default NULL,
1148 `reminderdate` date default NULL,
1149 `cancellationdate` date default NULL,
1150 `reservenotes` mediumtext,
1151 `priority` smallint(6) default NULL,
1152 `found` varchar(1) default NULL,
1153 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1154 `itemnumber` int(11) default NULL,
1155 `waitingdate` date default NULL,
1156 KEY `old_reserves_borrowernumber` (`borrowernumber`),
1157 KEY `old_reserves_biblionumber` (`biblionumber`),
1158 KEY `old_reserves_itemnumber` (`itemnumber`),
1159 KEY `old_reserves_branchcode` (`branchcode`),
1160 CONSTRAINT `old_reserves_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`)
1161 ON DELETE SET NULL ON UPDATE SET NULL,
1162 CONSTRAINT `old_reserves_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`)
1163 ON DELETE SET NULL ON UPDATE SET NULL,
1164 CONSTRAINT `old_reserves_ibfk_3` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`)
1165 ON DELETE SET NULL ON UPDATE SET NULL
1166 ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
1168 # move closed transactions to old_* tables
1169 $dbh->do("INSERT INTO old_issues SELECT * FROM issues WHERE returndate IS NOT NULL");
1170 $dbh->do("DELETE FROM issues WHERE returndate IS NOT NULL");
1171 $dbh->do("INSERT INTO old_reserves SELECT * FROM reserves WHERE cancellationdate IS NOT NULL OR found = 'F'");
1172 $dbh->do("DELETE FROM reserves WHERE cancellationdate IS NOT NULL OR found = 'F'");
1174 print "Upgrade to $DBversion done ( Added old_issues and old_reserves tables )\n";
1175 SetVersion ($DBversion);
1178 $DBversion = "3.00.00.063";
1179 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1180 $dbh->do("ALTER TABLE deleteditems
1181 CHANGE COLUMN booksellerid booksellerid MEDIUMTEXT DEFAULT NULL,
1182 ADD COLUMN enumchron VARCHAR(80) DEFAULT NULL AFTER more_subfields_xml,
1183 ADD COLUMN copynumber SMALLINT(6) DEFAULT NULL AFTER enumchron;");
1184 $dbh->do("ALTER TABLE items
1185 CHANGE COLUMN booksellerid booksellerid MEDIUMTEXT,
1186 ADD COLUMN copynumber SMALLINT(6) DEFAULT NULL AFTER enumchron;");
1187 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";
1188 SetVersion ($DBversion);
1191 $DBversion = "3.00.00.064";
1192 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1193 $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');");
1194 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('AWSAccessKeyID','','See: http://aws.amazon.com','','free');");
1195 $dbh->do("DELETE FROM `systempreferences` WHERE variable='AmazonDevKey';");
1196 $dbh->do("DELETE FROM `systempreferences` WHERE variable='XISBNAmazonSimilarItems';");
1197 $dbh->do("DELETE FROM `systempreferences` WHERE variable='OPACXISBNAmazonSimilarItems';");
1198 print "Upgrade to $DBversion done (IMPORTANT: Upgrading to Amazon.com Associates Web Service 4.0 ) ";
1199 SetVersion ($DBversion);
1202 $DBversion = "3.00.00.065";
1203 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1204 $dbh->do("CREATE TABLE `patroncards` (
1205 `cardid` int(11) NOT NULL auto_increment,
1206 `batch_id` varchar(10) NOT NULL default '1',
1207 `borrowernumber` int(11) NOT NULL,
1208 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1209 PRIMARY KEY (`cardid`),
1210 KEY `patroncards_ibfk_1` (`borrowernumber`),
1211 CONSTRAINT `patroncards_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE
1212 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;");
1213 print "Upgrade to $DBversion done (Adding patroncards table for patroncards generation feature. ) ";
1214 SetVersion ($DBversion);
1217 $DBversion = "3.00.00.066";
1218 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1219 $dbh->do("ALTER TABLE `virtualshelfcontents` MODIFY `dateadded` timestamp NOT NULL
1220 DEFAULT CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP;
1222 print "Upgrade to $DBversion done (fix for bug 1873: virtualshelfcontents dateadded column empty. ) ";
1223 SetVersion ($DBversion);
1226 $DBversion = "3.00.00.067";
1227 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1228 $dbh->do("UPDATE systempreferences SET explanation = 'Enable patron images for the Staff Client', type = 'YesNo' WHERE variable = 'patronimages'");
1229 print "Upgrade to $DBversion done (Updating patronimages syspref to reflect current kohastructure.sql. ) ";
1230 SetVersion ($DBversion);
1233 $DBversion = "3.00.00.068";
1234 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1235 $dbh->do("CREATE TABLE `permissions` (
1236 `module_bit` int(11) NOT NULL DEFAULT 0,
1237 `code` varchar(30) DEFAULT NULL,
1238 `description` varchar(255) DEFAULT NULL,
1239 PRIMARY KEY (`module_bit`, `code`),
1240 CONSTRAINT `permissions_ibfk_1` FOREIGN KEY (`module_bit`) REFERENCES `userflags` (`bit`)
1241 ON DELETE CASCADE ON UPDATE CASCADE
1242 ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
1243 $dbh->do("CREATE TABLE `user_permissions` (
1244 `borrowernumber` int(11) NOT NULL DEFAULT 0,
1245 `module_bit` int(11) NOT NULL DEFAULT 0,
1246 `code` varchar(30) DEFAULT NULL,
1247 CONSTRAINT `user_permissions_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`)
1248 ON DELETE CASCADE ON UPDATE CASCADE,
1249 CONSTRAINT `user_permissions_ibfk_2` FOREIGN KEY (`module_bit`, `code`)
1250 REFERENCES `permissions` (`module_bit`, `code`)
1251 ON DELETE CASCADE ON UPDATE CASCADE
1252 ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
1254 $dbh->do("INSERT INTO permissions (module_bit, code, description) VALUES
1255 (13, 'edit_news', 'Write news for the OPAC and staff interfaces'),
1256 (13, 'label_creator', 'Create printable labels and barcodes from catalog and patron data'),
1257 (13, 'edit_calendar', 'Define days when the library is closed'),
1258 (13, 'moderate_comments', 'Moderate patron comments'),
1259 (13, 'edit_notices', 'Define notices'),
1260 (13, 'edit_notice_status_triggers', 'Set notice/status triggers for overdue items'),
1261 (13, 'view_system_logs', 'Browse the system logs'),
1262 (13, 'inventory', 'Perform inventory (stocktaking) of your catalogue'),
1263 (13, 'stage_marc_import', 'Stage MARC records into the reservoir'),
1264 (13, 'manage_staged_marc', 'Managed staged MARC records, including completing and reversing imports'),
1265 (13, 'export_catalog', 'Export bibliographic and holdings data'),
1266 (13, 'import_patrons', 'Import patron data'),
1267 (13, 'delete_anonymize_patrons', 'Delete old borrowers and anonymize circulation history (deletes borrower reading history)'),
1268 (13, 'batch_upload_patron_images', 'Upload patron images in batch or one at a time'),
1269 (13, 'schedule_tasks', 'Schedule tasks to run')");
1271 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('GranularPermissions','0','Use detailed staff user permissions',NULL,'YesNo')");
1273 print "Upgrade to $DBversion done (adding permissions and user_permissions tables and GranularPermissions syspref) ";
1274 SetVersion ($DBversion);
1277 $DBversion = "3.00.00.069";
1278 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1279 $dbh->do("ALTER TABLE labels_conf CHANGE COLUMN class classification int(1) DEFAULT NULL;");
1280 print "Upgrade to $DBversion done ( Correcting columname in labels_conf )\n";
1281 SetVersion ($DBversion);
1284 $DBversion = "3.00.00.070";
1285 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1286 $sth = $dbh->prepare("SELECT value FROM systempreferences WHERE variable='yuipath'");
1288 my ($value) = $sth->fetchrow;
1289 $value =~ s/2.3.1/2.5.1/;
1290 $dbh->do("UPDATE systempreferences SET value='$value' WHERE variable='yuipath';");
1291 print "Update yuipath syspref to 2.5.1 if necessary\n";
1292 SetVersion ($DBversion);
1295 $DBversion = "3.00.00.071";
1296 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1297 $dbh->do(" ALTER TABLE `subscription` ADD `serialsadditems` TINYINT( 1 ) NOT NULL DEFAULT '0';");
1298 # fill the new field with the previous systempreference value, then drop the syspref
1299 my $sth = $dbh->prepare("SELECT value FROM systempreferences WHERE variable='serialsadditems'");
1301 my ($serialsadditems) = $sth->fetchrow();
1302 $dbh->do("UPDATE subscription SET serialsadditems=$serialsadditems");
1303 $dbh->do("DELETE FROM systempreferences WHERE variable='serialsadditems'");
1304 print "Upgrade to $DBversion done ( moving serialsadditems from syspref to subscription )\n";
1305 SetVersion ($DBversion);
1308 $DBversion = "3.00.00.072";
1309 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1310 $dbh->do("ALTER TABLE labels_conf ADD COLUMN formatstring VARCHAR(64) DEFAULT NULL;");
1311 print "Upgrade to $DBversion done ( Adding format string to labels generator. )\n";
1312 SetVersion ($DBversion);
1315 $DBversion = "3.00.00.073";
1316 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1317 $dbh->do("DROP TABLE IF EXISTS `tags_all`;");
1319 CREATE TABLE `tags_all` (
1320 `tag_id` int(11) NOT NULL auto_increment,
1321 `borrowernumber` int(11) NOT NULL,
1322 `biblionumber` int(11) NOT NULL,
1323 `term` varchar(255) NOT NULL,
1324 `language` int(4) default NULL,
1325 `date_created` datetime NOT NULL,
1326 PRIMARY KEY (`tag_id`),
1327 KEY `tags_borrowers_fk_1` (`borrowernumber`),
1328 KEY `tags_biblionumber_fk_1` (`biblionumber`),
1329 CONSTRAINT `tags_borrowers_fk_1` FOREIGN KEY (`borrowernumber`)
1330 REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1331 CONSTRAINT `tags_biblionumber_fk_1` FOREIGN KEY (`biblionumber`)
1332 REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE
1333 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1335 $dbh->do("DROP TABLE IF EXISTS `tags_approval`;");
1337 CREATE TABLE `tags_approval` (
1338 `term` varchar(255) NOT NULL,
1339 `approved` int(1) NOT NULL default '0',
1340 `date_approved` datetime default NULL,
1341 `approved_by` int(11) default NULL,
1342 `weight_total` int(9) NOT NULL default '1',
1343 PRIMARY KEY (`term`),
1344 KEY `tags_approval_borrowers_fk_1` (`approved_by`),
1345 CONSTRAINT `tags_approval_borrowers_fk_1` FOREIGN KEY (`approved_by`)
1346 REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE
1347 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1349 $dbh->do("DROP TABLE IF EXISTS `tags_index`;");
1351 CREATE TABLE `tags_index` (
1352 `term` varchar(255) NOT NULL,
1353 `biblionumber` int(11) NOT NULL,
1354 `weight` int(9) NOT NULL default '1',
1355 PRIMARY KEY (`term`,`biblionumber`),
1356 KEY `tags_index_biblionumber_fk_1` (`biblionumber`),
1357 CONSTRAINT `tags_index_term_fk_1` FOREIGN KEY (`term`)
1358 REFERENCES `tags_approval` (`term`) ON DELETE CASCADE ON UPDATE CASCADE,
1359 CONSTRAINT `tags_index_biblionumber_fk_1` FOREIGN KEY (`biblionumber`)
1360 REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE
1361 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1364 INSERT INTO `systempreferences` VALUES
1365 ('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=',''),
1366 ('BakerTaylorEnabled','0','','Enable or disable all Baker & Taylor features.','YesNo'),
1367 ('BakerTaylorPassword','','','Baker & Taylor Password for Content Cafe (external content)','Textarea'),
1368 ('BakerTaylorUsername','','','Baker & Taylor Username for Content Cafe (external content)','Textarea'),
1369 ('TagsEnabled','1','','Enables or disables all tagging features. This is the main switch for tags.','YesNo'),
1370 ('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.',''),
1371 ('TagsInputOnDetail','1','','Allow users to input tags from the detail page.', 'YesNo'),
1372 ('TagsInputOnList', '0','','Allow users to input tags from the search results list.', 'YesNo'),
1373 ('TagsModeration', NULL,'','(unimplemented) Requires tags from patrons to be approved before becoming visible.','YesNo'),
1374 ('TagsShowOnDetail','10','','Number of tags to display on detail page. 0 is off.', 'Integer'),
1375 ('TagsShowOnList', '6','','Number of tags to display on search results list. 0 is off.','Integer')
1377 print "Upgrade to $DBversion done (Baker/Taylor,Tags: sysprefs and tables (tags_all, tags_index, tags_approval)) ";
1378 SetVersion ($DBversion);
1381 =item DropAllForeignKeys($table)
1383 Drop all foreign keys of the table $table
1387 sub DropAllForeignKeys {
1389 # get the table description
1390 my $sth = $dbh->prepare("SHOW CREATE TABLE $table");
1392 my $vsc_structure = $sth->fetchrow;
1393 # split on CONSTRAINT keyword
1394 my @fks = split /CONSTRAINT /,$vsc_structure;
1397 # isolate what is before FOREIGN KEY, if there is something, it's a foreign key to drop
1398 $_ = /(.*) FOREIGN KEY.*/;
1401 # we have found 1 foreign, drop it
1402 $dbh->do("ALTER TABLE $table DROP FOREIGN KEY $id");
1409 =item TransformToNum
1411 Transform the Koha version from a 4 parts string
1412 to a number, with just 1 .
1416 sub TransformToNum {
1417 my $version = shift;
1418 # remove the 3 last . to have a Perl number
1419 $version =~ s/(.*\..*)\.(.*)\.(.*)/$1$2$3/;
1424 set the DBversion in the systempreferences
1428 my $kohaversion = TransformToNum(shift);
1429 if (C4::Context->preference('Version')) {
1430 my $finish=$dbh->prepare("UPDATE systempreferences SET value=? WHERE variable='Version'");
1431 $finish->execute($kohaversion);
1433 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')");
1434 $finish->execute($kohaversion);