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
53 Deal with virtualshelves
57 my $DBversion = "3.00.00.001";
58 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
59 # update virtualshelves table to
61 $dbh->do("ALTER TABLE `bookshelf` RENAME `virtualshelves`");
62 $dbh->do("ALTER TABLE `shelfcontents` RENAME `virtualshelfcontents`");
63 $dbh->do("ALTER TABLE `virtualshelfcontents` ADD `biblionumber` INT( 11 ) NOT NULL");
64 $dbh->do("UPDATE `virtualshelfcontents` SET biblionumber=(SELECT biblionumber FROM items WHERE items.itemnumber=virtualshelfcontents.itemnumber)");
65 # drop all foreign keys : otherwise, we can't drop itemnumber field.
66 DropAllForeignKeys('virtualshelfcontents');
67 # create the new foreign keys (on biblionumber)
68 $dbh->do("ALTER TABLE `virtualshelfcontents` ADD FOREIGN KEY biblionumber_fk (biblionumber) REFERENCES biblio (biblionumber) ON UPDATE CASCADE ON DELETE CASCADE");
69 # re-create the foreign key on virtualshelf
70 $dbh->do("ALTER TABLE `virtualshelfcontents` ADD FOREIGN KEY shelfnumber_fk (shelfnumber) REFERENCES virtualshelves (shelfnumber) ON UPDATE CASCADE ON DELETE CASCADE");
71 # now we can drop the itemnumber column
72 $dbh->do("ALTER TABLE `virtualshelfcontents` DROP `itemnumber`");
73 print "Upgrade to $DBversion done (virtualshelves)\n";
74 SetVersion ($DBversion);
78 $DBversion = "3.00.00.002";
79 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
80 $dbh->do("DROP TABLE sessions");
81 $dbh->do("CREATE TABLE `sessions` (
82 `id` char(32) NOT NULL,
83 `a_session` text NOT NULL,
84 UNIQUE KEY `id` (`id`)
85 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;");
86 print "Upgrade to $DBversion done (sessions uses CGI::session, new table structure for sessions)\n";
87 SetVersion ($DBversion);
91 $DBversion = "3.00.00.003";
92 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
93 if (C4::Context->preference("opaclanguages") eq "fr") {
94 $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')");
96 $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')");
98 print "Upgrade to $DBversion done (adding ReservesNeedReturns systempref, in circulation)\n";
99 SetVersion ($DBversion);
103 $DBversion = "3.00.00.004";
104 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
105 $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')");
106 print "Upgrade to $DBversion done (adding DebugLevel systempref, in 'Admin' tab)\n";
107 SetVersion ($DBversion);
110 $DBversion = "3.00.00.005";
111 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
112 $dbh->do("CREATE TABLE `tags` (
113 `entry` varchar(255) NOT NULL default '',
114 `weight` bigint(20) NOT NULL default 0,
115 PRIMARY KEY (`entry`)
116 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
118 $dbh->do("CREATE TABLE `nozebra` (
119 `server` varchar(20) NOT NULL,
120 `indexname` varchar(40) NOT NULL,
121 `value` varchar(250) NOT NULL,
122 `biblionumbers` longtext NOT NULL,
123 KEY `indexname` (`server`,`indexname`),
124 KEY `value` (`server`,`value`))
125 ENGINE=InnoDB DEFAULT CHARSET=utf8;
127 print "Upgrade to $DBversion done (adding tags and nozebra tables )\n";
128 SetVersion ($DBversion);
131 $DBversion = "3.00.00.006";
132 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
133 $dbh->do("UPDATE issues SET issuedate=timestamp WHERE issuedate='0000-00-00'");
134 print "Upgrade to $DBversion done (filled issues.issuedate with timestamp)\n";
135 SetVersion ($DBversion);
138 $DBversion = "3.00.00.007";
139 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
140 $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')");
141 print "Upgrade to $DBversion done (set SessionStorage variable)\n";
142 SetVersion ($DBversion);
145 $DBversion = "3.00.00.008";
146 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
147 $dbh->do("ALTER TABLE `biblio` ADD `datecreated` DATE NOT NULL AFTER `timestamp` ;");
148 $dbh->do("UPDATE biblio SET datecreated=timestamp");
149 print "Upgrade to $DBversion done (biblio creation date)\n";
150 SetVersion ($DBversion);
153 $DBversion = "3.00.00.009";
154 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
156 # Create backups of call number columns
157 # in case default migration needs to be customized
159 # UPGRADE NOTE: temp_upg_biblioitems_call_num should be dropped
160 # after call numbers have been transformed to the new structure
162 # Not bothering to do the same with deletedbiblioitems -- assume
163 # default is good enough.
164 $dbh->do("CREATE TABLE `temp_upg_biblioitems_call_num` AS
165 SELECT `biblioitemnumber`, `biblionumber`,
166 `classification`, `dewey`, `subclass`,
168 FROM `biblioitems`");
170 # biblioitems changes
171 $dbh->do("ALTER TABLE `biblioitems` CHANGE COLUMN `volumeddesc` `volumedesc` TEXT,
172 ADD `cn_source` VARCHAR(10) DEFAULT NULL AFTER `ccode`,
173 ADD `cn_class` VARCHAR(30) DEFAULT NULL AFTER `cn_source`,
174 ADD `cn_item` VARCHAR(10) DEFAULT NULL AFTER `cn_class`,
175 ADD `cn_suffix` VARCHAR(10) DEFAULT NULL AFTER `cn_item`,
176 ADD `cn_sort` VARCHAR(30) DEFAULT NULL AFTER `cn_suffix`,
177 ADD `totalissues` INT(10) AFTER `cn_sort`");
179 # default mapping of call number columns:
180 # cn_class = concatentation of classification + dewey,
181 # trimmed to fit -- assumes that most users do not
182 # populate both classification and dewey in a single record
184 # cn_source = left null
187 # After upgrade, cn_sort will have to be set based on whatever
188 # default call number scheme user sets as a preference. Misc
189 # script will be added at some point to do that.
191 $dbh->do("UPDATE `biblioitems`
192 SET cn_class = SUBSTR(TRIM(CONCAT_WS(' ', `classification`, `dewey`)), 1, 30),
197 # Now drop the old call number columns
198 $dbh->do("ALTER TABLE `biblioitems` DROP COLUMN `classification`,
200 DROP COLUMN `subclass`,
201 DROP COLUMN `lcsort`,
202 DROP COLUMN `ccode`");
204 # deletedbiblio changes
205 $dbh->do("ALTER TABLE `deletedbiblio` ALTER COLUMN `frameworkcode` SET DEFAULT '',
207 ADD `datecreated` DATE NOT NULL AFTER `timestamp`");
208 $dbh->do("UPDATE deletedbiblio SET datecreated = timestamp");
210 # deletedbiblioitems changes
211 $dbh->do("ALTER TABLE `deletedbiblioitems`
212 MODIFY `publicationyear` TEXT,
213 CHANGE `volumeddesc` `volumedesc` TEXT,
214 MODIFY `collectiontitle` MEDIUMTEXT DEFAULT NULL AFTER `volumedesc`,
215 MODIFY `collectionissn` TEXT DEFAULT NULL AFTER `collectiontitle`,
216 MODIFY `collectionvolume` MEDIUMTEXT DEFAULT NULL AFTER `collectionissn`,
217 MODIFY `editionstatement` TEXT DEFAULT NULL AFTER `collectionvolume`,
218 MODIFY `editionresponsibility` TEXT DEFAULT NULL AFTER `editionstatement`,
219 MODIFY `place` VARCHAR(255) DEFAULT NULL AFTER `size`,
221 ADD `cn_source` VARCHAR(10) DEFAULT NULL AFTER `url`,
222 ADD `cn_class` VARCHAR(30) DEFAULT NULL AFTER `cn_source`,
223 ADD `cn_item` VARCHAR(10) DEFAULT NULL AFTER `cn_class`,
224 ADD `cn_suffix` VARCHAR(10) DEFAULT NULL AFTER `cn_item`,
225 ADD `cn_sort` VARCHAR(30) DEFAULT NULL AFTER `cn_suffix`,
226 ADD `totalissues` INT(10) AFTER `cn_sort`,
227 ADD KEY `isbn` (`isbn`),
228 ADD KEY `publishercode` (`publishercode`)
231 $dbh->do("UPDATE `deletedbiblioitems`
232 SET `cn_class` = SUBSTR(TRIM(CONCAT_WS(' ', `classification`, `dewey`)), 1, 30),
233 `cn_item` = `subclass`,
236 $dbh->do("ALTER TABLE `deletedbiblioitems`
237 DROP COLUMN `classification`,
239 DROP COLUMN `subclass`,
240 DROP COLUMN `lcsort`,
244 # deleteditems changes
245 $dbh->do("ALTER TABLE `deleteditems`
246 MODIFY `barcode` VARCHAR(20) DEFAULT NULL,
247 MODIFY `price` DECIMAL(8,2) DEFAULT NULL,
248 MODIFY `replacementprice` DECIMAL(8,2) DEFAULT NULL,
250 MODIFY `itemcallnumber` VARCHAR(30) DEFAULT NULL AFTER `wthdrawn`,
251 MODIFY `holdingbranch` VARCHAR(10) DEFAULT NULL,
253 MODIFY `timestamp` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP AFTER `paidfor`,
255 ADD `cn_source` VARCHAR(10) DEFAULT NULL AFTER `onloan`,
256 ADD `cn_sort` VARCHAR(30) DEFAULT NULL AFTER `cn_source`,
257 ADD `ccode` VARCHAR(10) DEFAULT NULL AFTER `cn_sort`,
258 ADD `materials` VARCHAR(10) DEFAULT NULL AFTER `ccode`,
259 ADD `uri` VARCHAR(255) DEFAULT NULL AFTER `materials`,
260 MODIFY `marc` LONGBLOB AFTER `uri`,
262 DROP KEY `itembarcodeidx`,
263 DROP KEY `itembinoidx`,
264 DROP KEY `itembibnoidx`,
265 ADD UNIQUE KEY `delitembarcodeidx` (`barcode`),
266 ADD KEY `delitembinoidx` (`biblioitemnumber`),
267 ADD KEY `delitembibnoidx` (`biblionumber`),
268 ADD KEY `delhomebranch` (`homebranch`),
269 ADD KEY `delholdingbranch` (`holdingbranch`)");
270 $dbh->do("UPDATE deleteditems SET `ccode` = `itype`");
271 $dbh->do("ALTER TABLE deleteditems DROP `itype`");
272 $dbh->do("UPDATE `deleteditems` SET `cn_sort` = `itemcallnumber`");
275 $dbh->do("ALTER TABLE `items` ADD `cn_source` VARCHAR(10) DEFAULT NULL AFTER `onloan`,
276 ADD `cn_sort` VARCHAR(30) DEFAULT NULL AFTER `cn_source`,
277 ADD `ccode` VARCHAR(10) DEFAULT NULL AFTER `cn_sort`,
278 ADD `materials` VARCHAR(10) DEFAULT NULL AFTER `ccode`,
279 ADD `uri` VARCHAR(255) DEFAULT NULL AFTER `materials`
281 $dbh->do("ALTER TABLE `items`
282 DROP KEY `itembarcodeidx`,
283 ADD UNIQUE KEY `itembarcodeidx` (`barcode`)");
285 # map items.itype to items.ccode and
286 # set cn_sort to itemcallnumber -- as with biblioitems.cn_sort,
287 # will have to be subsequently updated per user's default
288 # classification scheme
289 $dbh->do("UPDATE `items` SET `cn_sort` = `itemcallnumber`,
292 $dbh->do("ALTER TABLE `items` DROP `cutterextra`,
295 print "Upgrade to $DBversion done (major changes to biblio, biblioitems, items, and deleted* versions of same\n";
296 SetVersion ($DBversion);
299 $DBversion = "3.00.00.010";
300 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
301 $dbh->do("CREATE INDEX `userid` ON borrowers (`userid`) ");
302 print "Upgrade to $DBversion done (userid index added)\n";
303 SetVersion ($DBversion);
306 $DBversion = "3.00.00.011";
307 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
308 $dbh->do("ALTER TABLE `branchcategories` CHANGE `categorycode` `categorycode` char(10) ");
309 $dbh->do("ALTER TABLE `branchcategories` CHANGE `categoryname` `categoryname` varchar(32) ");
310 $dbh->do("ALTER TABLE `branchcategories` ADD COLUMN `categorytype` varchar(16) ");
311 $dbh->do("UPDATE `branchcategories` SET `categorytype` = 'properties'");
312 $dbh->do("ALTER TABLE `branchrelations` CHANGE `categorycode` `categorycode` char(10) ");
313 print "Upgrade to $DBversion done (added branchcategory type)\n";
314 SetVersion ($DBversion);
317 $DBversion = "3.00.00.012";
318 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
319 $dbh->do("CREATE TABLE `class_sort_rules` (
320 `class_sort_rule` varchar(10) NOT NULL default '',
321 `description` mediumtext,
322 `sort_routine` varchar(30) NOT NULL default '',
323 PRIMARY KEY (`class_sort_rule`),
324 UNIQUE KEY `class_sort_rule_idx` (`class_sort_rule`)
325 ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
326 $dbh->do("CREATE TABLE `class_sources` (
327 `cn_source` varchar(10) NOT NULL default '',
328 `description` mediumtext,
329 `used` tinyint(4) NOT NULL default 0,
330 `class_sort_rule` varchar(10) NOT NULL default '',
331 PRIMARY KEY (`cn_source`),
332 UNIQUE KEY `cn_source_idx` (`cn_source`),
333 KEY `used_idx` (`used`),
334 CONSTRAINT `class_source_ibfk_1` FOREIGN KEY (`class_sort_rule`)
335 REFERENCES `class_sort_rules` (`class_sort_rule`)
336 ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
337 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type)
338 VALUES('DefaultClassificationSource','ddc',
339 'Default classification scheme used by the collection. E.g., Dewey, LCC, etc.', NULL,'free')");
340 $dbh->do("INSERT INTO `class_sort_rules` (`class_sort_rule`, `description`, `sort_routine`) VALUES
341 ('dewey', 'Default filing rules for DDC', 'Dewey'),
342 ('lcc', 'Default filing rules for LCC', 'LCC'),
343 ('generic', 'Generic call number filing rules', 'Generic')");
344 $dbh->do("INSERT INTO `class_sources` (`cn_source`, `description`, `used`, `class_sort_rule`) VALUES
345 ('ddc', 'Dewey Decimal Classification', 1, 'dewey'),
346 ('lcc', 'Library of Congress Classification', 1, 'lcc'),
347 ('udc', 'Universal Decimal Classification', 0, 'generic'),
348 ('sudocs', 'SuDoc Classification (U.S. GPO)', 0, 'generic'),
349 ('z', 'Other/Generic Classification Scheme', 0, 'generic')");
350 print "Upgrade to $DBversion done (classification sources added)\n";
351 SetVersion ($DBversion);
354 $DBversion = "3.00.00.013";
355 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
356 $dbh->do("CREATE TABLE `import_batches` (
357 `import_batch_id` int(11) NOT NULL auto_increment,
358 `template_id` int(11) default NULL,
359 `branchcode` varchar(10) default NULL,
360 `num_biblios` int(11) NOT NULL default 0,
361 `num_items` int(11) NOT NULL default 0,
362 `upload_timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP,
363 `overlay_action` enum('replace', 'create_new', 'use_template') NOT NULL default 'create_new',
364 `import_status` enum('staging', 'staged', 'importing', 'imported', 'reverting', 'reverted', 'cleaned') NOT NULL default 'staging',
365 `batch_type` enum('batch', 'z3950') NOT NULL default 'batch',
366 `file_name` varchar(100),
367 `comments` mediumtext,
368 PRIMARY KEY (`import_batch_id`),
369 KEY `branchcode` (`branchcode`)
370 ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
371 $dbh->do("CREATE TABLE `import_records` (
372 `import_record_id` int(11) NOT NULL auto_increment,
373 `import_batch_id` int(11) NOT NULL,
374 `branchcode` varchar(10) default NULL,
375 `record_sequence` int(11) NOT NULL default 0,
376 `upload_timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP,
377 `import_date` DATE default NULL,
378 `marc` longblob NOT NULL,
379 `marcxml` longtext NOT NULL,
380 `marcxml_old` longtext NOT NULL,
381 `record_type` enum('biblio', 'auth', 'holdings') NOT NULL default 'biblio',
382 `overlay_status` enum('no_match', 'auto_match', 'manual_match', 'match_applied') NOT NULL default 'no_match',
383 `status` enum('error', 'staged', 'imported', 'reverted', 'items_reverted') NOT NULL default 'staged',
384 `import_error` mediumtext,
385 `encoding` varchar(40) NOT NULL default '',
386 `z3950random` varchar(40) default NULL,
387 PRIMARY KEY (`import_record_id`),
388 CONSTRAINT `import_records_ifbk_1` FOREIGN KEY (`import_batch_id`)
389 REFERENCES `import_batches` (`import_batch_id`) ON DELETE CASCADE ON UPDATE CASCADE,
390 KEY `branchcode` (`branchcode`),
391 KEY `batch_sequence` (`import_batch_id`, `record_sequence`)
392 ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
393 $dbh->do("CREATE TABLE `import_record_matches` (
394 `import_record_id` int(11) NOT NULL,
395 `candidate_match_id` int(11) NOT NULL,
396 `score` int(11) NOT NULL default 0,
397 CONSTRAINT `import_record_matches_ibfk_1` FOREIGN KEY (`import_record_id`)
398 REFERENCES `import_records` (`import_record_id`) ON DELETE CASCADE ON UPDATE CASCADE,
399 KEY `record_score` (`import_record_id`, `score`)
400 ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
401 $dbh->do("CREATE TABLE `import_biblios` (
402 `import_record_id` int(11) NOT NULL,
403 `matched_biblionumber` int(11) default NULL,
404 `control_number` varchar(25) default NULL,
405 `original_source` varchar(25) default NULL,
406 `title` varchar(128) default NULL,
407 `author` varchar(80) default NULL,
408 `isbn` varchar(14) default NULL,
409 `issn` varchar(9) default NULL,
410 `has_items` tinyint(1) NOT NULL default 0,
411 CONSTRAINT `import_biblios_ibfk_1` FOREIGN KEY (`import_record_id`)
412 REFERENCES `import_records` (`import_record_id`) ON DELETE CASCADE ON UPDATE CASCADE,
413 KEY `matched_biblionumber` (`matched_biblionumber`),
414 KEY `title` (`title`),
416 ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
417 $dbh->do("CREATE TABLE `import_items` (
418 `import_items_id` int(11) NOT NULL auto_increment,
419 `import_record_id` int(11) NOT NULL,
420 `itemnumber` int(11) default NULL,
421 `branchcode` varchar(10) default NULL,
422 `status` enum('error', 'staged', 'imported', 'reverted') NOT NULL default 'staged',
423 `marcxml` longtext NOT NULL,
424 `import_error` mediumtext,
425 PRIMARY KEY (`import_items_id`),
426 CONSTRAINT `import_items_ibfk_1` FOREIGN KEY (`import_record_id`)
427 REFERENCES `import_records` (`import_record_id`) ON DELETE CASCADE ON UPDATE CASCADE,
428 KEY `itemnumber` (`itemnumber`),
429 KEY `branchcode` (`branchcode`)
430 ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
432 $dbh->do("INSERT INTO `import_batches`
433 (`overlay_action`, `import_status`, `batch_type`, `file_name`)
434 SELECT distinct 'create_new', 'staged', 'z3950', `file`
435 FROM `marc_breeding`");
437 $dbh->do("INSERT INTO `import_records`
438 (`import_batch_id`, `import_record_id`, `record_sequence`, `marc`, `record_type`, `status`,
439 `encoding`, `z3950random`, `marcxml`, `marcxml_old`)
440 SELECT `import_batch_id`, `id`, 1, `marc`, 'biblio', 'staged', `encoding`, `z3950random`, '', ''
442 JOIN `import_batches` ON (`file_name` = `file`)");
444 $dbh->do("INSERT INTO `import_biblios`
445 (`import_record_id`, `title`, `author`, `isbn`)
446 SELECT `import_record_id`, `title`, `author`, `isbn`
448 JOIN `import_records` ON (`import_record_id` = `id`)");
450 $dbh->do("UPDATE `import_batches`
451 SET `num_biblios` = (
453 FROM `import_records`
454 WHERE `import_batch_id` = `import_batches`.`import_batch_id`
457 $dbh->do("DROP TABLE `marc_breeding`");
459 print "Upgrade to $DBversion done (import_batches et al. added)\n";
460 SetVersion ($DBversion);
463 $DBversion = "3.00.00.014";
464 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
465 $dbh->do("ALTER TABLE subscription ADD lastbranch VARCHAR(4)");
466 print "Upgrade to $DBversion done (userid index added)\n";
467 SetVersion ($DBversion);
470 $DBversion = "3.00.00.015";
471 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
472 $dbh->do("CREATE TABLE `saved_sql` (
473 `id` int(11) NOT NULL auto_increment,
474 `borrowernumber` int(11) default NULL,
475 `date_created` datetime default NULL,
476 `last_modified` datetime default NULL,
478 `last_run` datetime default NULL,
479 `report_name` varchar(255) default NULL,
480 `type` varchar(255) default NULL,
483 KEY boridx (`borrowernumber`)
484 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;");
485 $dbh->do("CREATE TABLE `saved_reports` (
486 `id` int(11) NOT NULL auto_increment,
487 `report_id` int(11) default NULL,
489 `date_run` datetime default NULL,
491 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;");
492 print "Upgrade to $DBversion done (saved_sql and saved_reports added)\n";
493 SetVersion ($DBversion);
496 $DBversion = "3.00.00.016";
497 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
498 $dbh->do(" CREATE TABLE reports_dictionary (
499 id int(11) NOT NULL auto_increment,
500 name varchar(255) default NULL,
502 date_created datetime default NULL,
503 date_modified datetime default NULL,
505 area int(11) default NULL,
507 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ");
508 print "Upgrade to $DBversion done (reports_dictionary) added)\n";
509 SetVersion ($DBversion);
512 $DBversion = "3.00.00.017";
513 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
514 $dbh->do("ALTER TABLE action_logs DROP PRIMARY KEY");
515 $dbh->do("ALTER TABLE action_logs ADD KEY timestamp (timestamp,user)");
516 $dbh->do("ALTER TABLE action_logs ADD action_id INT(11) NOT NULL FIRST");
517 $dbh->do("UPDATE action_logs SET action_id = if (\@a, \@a:=\@a+1, \@a:=1)");
518 $dbh->do("ALTER TABLE action_logs MODIFY action_id INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY");
519 print "Upgrade to $DBversion done (added column to action_logs)\n";
520 SetVersion ($DBversion);
523 $DBversion = "3.00.00.018";
524 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
525 $dbh->do("ALTER TABLE `zebraqueue`
526 ADD `done` INT NOT NULL DEFAULT '0',
527 ADD `time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ;
529 print "Upgrade to $DBversion done (adding timestamp and done columns to zebraque table to improve problem tracking) added)\n";
530 SetVersion ($DBversion);
533 $DBversion = "3.00.00.019";
534 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
535 $dbh->do("ALTER TABLE biblio MODIFY biblionumber INT(11) NOT NULL AUTO_INCREMENT");
536 $dbh->do("ALTER TABLE biblioitems MODIFY biblioitemnumber INT(11) NOT NULL AUTO_INCREMENT");
537 $dbh->do("ALTER TABLE items MODIFY itemnumber INT(11) NOT NULL AUTO_INCREMENT");
538 print "Upgrade to $DBversion done (made bib/item PKs auto_increment)\n";
539 SetVersion ($DBversion);
542 $DBversion = "3.00.00.020";
543 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
544 $dbh->do("ALTER TABLE deleteditems
545 DROP KEY `delitembarcodeidx`,
546 ADD KEY `delitembarcodeidx` (`barcode`)");
547 print "Upgrade to $DBversion done (dropped uniqueness of key on deleteditems.barcode)\n";
548 SetVersion ($DBversion);
551 $DBversion = "3.00.00.021";
552 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
553 $dbh->do("ALTER TABLE items CHANGE homebranch homebranch VARCHAR(10)");
554 $dbh->do("ALTER TABLE deleteditems CHANGE homebranch homebranch VARCHAR(10)");
555 $dbh->do("ALTER TABLE statistics CHANGE branch branch VARCHAR(10)");
556 $dbh->do("ALTER TABLE subscription CHANGE lastbranch lastbranch VARCHAR(10)");
557 print "Upgrade to $DBversion done (extended missed branchcode columns to 10 chars)\n";
558 SetVersion ($DBversion);
561 $DBversion = "3.00.00.022";
562 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
563 $dbh->do("ALTER TABLE items
564 ADD `damaged` tinyint(1) default NULL");
565 print "Upgrade to $DBversion done (adding damaged column to items table)\n";
566 SetVersion ($DBversion);
569 $DBversion = "3.00.00.023";
570 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
571 $dbh->do("INSERT INTO `systempreferences` (variable,value,options,explanation,type)
572 VALUES ('yuipath','http://yui.yahooapis.com/2.3.1/build','Insert the path to YUI libraries','','free')");
573 print "Upgrade to $DBversion done (adding new system preference for controlling YUI path)\n";
574 SetVersion ($DBversion);
577 $DBversion = "3.00.00.024";
578 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
579 $dbh->do("ALTER TABLE biblioitems CHANGE itemtype itemtype VARCHAR(10)");
580 print "Upgrade to $DBversion done (changing itemtype to (10))\n";
581 SetVersion ($DBversion);
584 $DBversion = "3.00.00.025";
585 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
586 $dbh->do("ALTER TABLE items ADD COLUMN itype VARCHAR(10)");
587 if(C4::Context->preference('item-level_itypes')){
588 $dbh->do('update items,biblioitems set items.itype=biblioitems.itemtype where items.biblionumber=biblioitems.biblionumber and itype is null');
590 print "Upgrade to $DBversion done (reintroduce items.itype - fill from itemtype)\n ";
591 SetVersion ($DBversion);
594 $DBversion = "3.00.00.026";
595 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
596 $dbh->do("INSERT INTO `systempreferences` (variable,value,options,explanation,type)
597 VALUES ('HomeOrHoldingBranch','homebranch','homebranch|holdingbranch','With independent branches turned on this decides whether to check the items holdingbranch or homebranch at circulatilon','choice')");
598 print "Upgrade to $DBversion done (adding new system preference for choosing whether homebranch or holdingbranch is checked in circulation)\n";
599 SetVersion ($DBversion);
602 $DBversion = "3.00.00.027";
603 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
604 $dbh->do("CREATE TABLE `marc_matchers` (
605 `matcher_id` int(11) NOT NULL auto_increment,
606 `code` varchar(10) NOT NULL default '',
607 `description` varchar(255) NOT NULL default '',
608 `record_type` varchar(10) NOT NULL default 'biblio',
609 `threshold` int(11) NOT NULL default 0,
610 PRIMARY KEY (`matcher_id`),
612 KEY `record_type` (`record_type`)
613 ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
614 $dbh->do("CREATE TABLE `matchpoints` (
615 `matcher_id` int(11) NOT NULL,
616 `matchpoint_id` int(11) NOT NULL auto_increment,
617 `search_index` varchar(30) NOT NULL default '',
618 `score` int(11) NOT NULL default 0,
619 PRIMARY KEY (`matchpoint_id`),
620 CONSTRAINT `matchpoints_ifbk_1` FOREIGN KEY (`matcher_id`)
621 REFERENCES `marc_matchers` (`matcher_id`) ON DELETE CASCADE ON UPDATE CASCADE
622 ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
623 $dbh->do("CREATE TABLE `matchpoint_components` (
624 `matchpoint_id` int(11) NOT NULL,
625 `matchpoint_component_id` int(11) NOT NULL auto_increment,
626 sequence int(11) NOT NULL default 0,
627 tag varchar(3) NOT NULL default '',
628 subfields varchar(40) NOT NULL default '',
629 offset int(4) NOT NULL default 0,
630 length int(4) NOT NULL default 0,
631 PRIMARY KEY (`matchpoint_component_id`),
632 KEY `by_sequence` (`matchpoint_id`, `sequence`),
633 CONSTRAINT `matchpoint_components_ifbk_1` FOREIGN KEY (`matchpoint_id`)
634 REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE
635 ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
636 $dbh->do("CREATE TABLE `matchpoint_component_norms` (
637 `matchpoint_component_id` int(11) NOT NULL,
638 `sequence` int(11) NOT NULL default 0,
639 `norm_routine` varchar(50) NOT NULL default '',
640 KEY `matchpoint_component_norms` (`matchpoint_component_id`, `sequence`),
641 CONSTRAINT `matchpoint_component_norms_ifbk_1` FOREIGN KEY (`matchpoint_component_id`)
642 REFERENCES `matchpoint_components` (`matchpoint_component_id`) ON DELETE CASCADE ON UPDATE CASCADE
643 ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
644 $dbh->do("CREATE TABLE `matcher_matchpoints` (
645 `matcher_id` int(11) NOT NULL,
646 `matchpoint_id` int(11) NOT NULL,
647 CONSTRAINT `matcher_matchpoints_ifbk_1` FOREIGN KEY (`matcher_id`)
648 REFERENCES `marc_matchers` (`matcher_id`) ON DELETE CASCADE ON UPDATE CASCADE,
649 CONSTRAINT `matcher_matchpoints_ifbk_2` FOREIGN KEY (`matchpoint_id`)
650 REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE
651 ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
652 $dbh->do("CREATE TABLE `matchchecks` (
653 `matcher_id` int(11) NOT NULL,
654 `matchcheck_id` int(11) NOT NULL auto_increment,
655 `source_matchpoint_id` int(11) NOT NULL,
656 `target_matchpoint_id` int(11) NOT NULL,
657 PRIMARY KEY (`matchcheck_id`),
658 CONSTRAINT `matcher_matchchecks_ifbk_1` FOREIGN KEY (`matcher_id`)
659 REFERENCES `marc_matchers` (`matcher_id`) ON DELETE CASCADE ON UPDATE CASCADE,
660 CONSTRAINT `matcher_matchchecks_ifbk_2` FOREIGN KEY (`source_matchpoint_id`)
661 REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE,
662 CONSTRAINT `matcher_matchchecks_ifbk_3` FOREIGN KEY (`target_matchpoint_id`)
663 REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE
664 ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
665 print "Upgrade to $DBversion done (added C4::Matcher serialization tables)\n ";
666 SetVersion ($DBversion);
669 $DBversion = "3.00.00.028";
670 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
671 $dbh->do("INSERT INTO `systempreferences` (variable,value,options,explanation,type)
672 VALUES ('canreservefromotherbranches','1','','With Independent branches on, can a user from one library reserve an item from another library','YesNo')");
673 print "Upgrade to $DBversion done (adding new system preference for changing reserve/holds behaviour with independent branches)\n";
674 SetVersion ($DBversion);
678 $DBversion = "3.00.00.029";
679 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
680 $dbh->do("ALTER TABLE `import_batches` ADD `matcher_id` int(11) NULL AFTER `import_batch_id`");
681 print "Upgrade to $DBversion done (adding matcher_id to import_batches)\n";
682 SetVersion ($DBversion);
685 $DBversion = "3.00.00.030";
686 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
688 CREATE TABLE services_throttle (
689 service_type varchar(10) NOT NULL default '',
690 service_count varchar(45) default NULL,
691 PRIMARY KEY (service_type)
692 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
694 $dbh->do("INSERT INTO `systempreferences` (variable,value,options,explanation,type)
695 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')");
696 $dbh->do("INSERT INTO `systempreferences` (variable,value,options,explanation,type)
697 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')");
698 $dbh->do("INSERT INTO `systempreferences` (variable,value,options,explanation,type)
699 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')");
700 $dbh->do("INSERT INTO `systempreferences` (variable,value,options,explanation,type)
701 VALUES ('XISBNDailyLimit',499,'','The xISBN Web service is free for non-commercial use when usage does not exceed 500 requests per day','free')");
702 $dbh->do("INSERT INTO `systempreferences` (variable,value,options,explanation,type)
703 VALUES ('PINESISBN',0,'','Use with FRBRizeEditions. If ON, Koha will use PINES OISBN web service in the Editions tab on the detail pages.','YesNo')");
704 $dbh->do("INSERT INTO `systempreferences` (variable,value,options,explanation,type)
705 VALUES ('ThingISBN',0,'','Use with FRBRizeEditions. If ON, Koha will use the ThingISBN web service in the Editions tab on the detail pages.','YesNo')");
706 print "Upgrade to $DBversion done (adding services throttle table and sysprefs for xISBN)\n";
707 SetVersion ($DBversion);
710 $DBversion = "3.00.00.031";
711 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
713 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('QueryStemming',1,'If ON, enables query stemming',NULL,'YesNo')");
714 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('QueryFuzzy',1,'If ON, enables fuzzy option for searches',NULL,'YesNo')");
715 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('QueryWeightFields',1,'If ON, enables field weighting',NULL,'YesNo')");
716 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('WebBasedSelfCheck',0,'If ON, enables the web-based self-check system',NULL,'YesNo')");
717 $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')");
718 $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')");
719 $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')");
720 $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')");
721 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('defaultSortOrder',NULL,'Specify the default sort order','asc|dsc|az|za','Choice')");
722 $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')");
723 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('OPACdefaultSortOrder',NULL,'Specify the default sort order','asc|dsc|za|az','Choice')");
724 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('staffClientBaseURL','','Specify the base URL of the staff client',NULL,'free')");
725 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('minPasswordLength',3,'Specify the minimum length of a patron/staff password',NULL,'free')");
726 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('noItemTypeImages',0,'If ON, disables item-type images',NULL,'YesNo')");
727 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('noOPACHolds',0,'If ON, disables holds globally',NULL,'YesNo')");
728 $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')");
729 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('holdCancelLength','','Specify how many days before a hold is canceled',NULL,'free')");
730 $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')");
731 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('finesMode','test','Choose the fines mode, test or production','test|production','Choice')");
732 $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')");
733 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('itemBarcodeInputFilter','','If set, allows specification of a item barcode input filter','cuecat','Choice')");
734 $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')");
735 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('URLLinkText','','Text to display as the link anchor in the OPAC',NULL,'free')");
736 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('noOPACUserLogin',0,'If ON, disables the OPAC User Login',NULL,'YesNo')");
737 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('OPACSubscriptionDisplay','economical','Specify how to display subscription information in the OPAC','economical|off|full','Choice')");
738 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('OPACDisplayExtendedSubInfo',1,'If ON, extended subscription information is displayed in the OPAC',NULL,'YesNo')");
739 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('OPACViewOthersSuggestions',0,'If ON, allows all suggestions to be displayed in the OPAC',NULL,'YesNo')");
740 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('OPACURLOpenInNewWindow',0,'If ON, URLs in the OPAC open in a new window',NULL,'YesNo')");
741 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('OPACUserCSS',0,'Add CSS to be included in the OPAC',NULL,'free')");
742 $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')");
744 print "Upgrade to $DBversion done (adding additional system preference)\n";
745 SetVersion ($DBversion);
748 $DBversion = "3.00.00.032";
749 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
750 $dbh->do("UPDATE `marc_subfield_structure` SET `kohafield` = 'items.wthdrawn' WHERE `kohafield` = 'items.withdrawn'");
751 print "Upgrade to $DBversion done (fixed MARC framework references to items.withdrawn)\n";
752 SetVersion ($DBversion);
755 $DBversion = "3.00.00.033";
756 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
757 $dbh->do("INSERT INTO `userflags` VALUES(17,'staffaccess','Modify login / permissions for staff users',0)");
758 print "Upgrade to $DBversion done (Adding permissions flag for staff member access modification. )\n";
759 SetVersion ($DBversion);
762 $DBversion = "3.00.00.034";
763 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
764 $dbh->do("ALTER TABLE `virtualshelves` ADD COLUMN `sortfield` VARCHAR(16) ");
765 print "Upgrade to $DBversion done (Adding sortfield for Virtual Shelves. )\n";
766 SetVersion ($DBversion);
769 $DBversion = "3.00.00.035";
770 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
771 $dbh->do("UPDATE marc_subfield_structure
772 SET authorised_value = 'cn_source'
773 WHERE kohafield IN ('items.cn_source', 'biblioitems.cn_source')
774 AND (authorised_value is NULL OR authorised_value = '')");
775 print "Upgrade to $DBversion done (MARC frameworks: make classification source a drop-down)\n";
776 SetVersion ($DBversion);
779 $DBversion = "3.00.00.036";
780 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
781 $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');");
782 print "Upgrade to $DBversion done (OPACItemsResultsDisplay systempreference added)\n";
783 SetVersion ($DBversion);
786 $DBversion = "3.00.00.037";
787 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
788 $dbh->do("ALTER TABLE `borrowers` ADD COLUMN `altcontactfirstname` varchar(255)");
789 $dbh->do("ALTER TABLE `borrowers` ADD COLUMN `altcontactsurname` varchar(255)");
790 $dbh->do("ALTER TABLE `borrowers` ADD COLUMN `altcontactaddress1` varchar(255)");
791 $dbh->do("ALTER TABLE `borrowers` ADD COLUMN `altcontactaddress2` varchar(255)");
792 $dbh->do("ALTER TABLE `borrowers` ADD COLUMN `altcontactaddress3` varchar(255)");
793 $dbh->do("ALTER TABLE `borrowers` ADD COLUMN `altcontactzipcode` varchar(50)");
794 $dbh->do("ALTER TABLE `borrowers` ADD COLUMN `altcontactphone` varchar(50)");
795 print "Upgrade to $DBversion done (Adding Alternative Contact Person information to borrowers table)\n";
796 SetVersion ($DBversion);
799 $DBversion = "3.00.00.038";
800 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
801 $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'");
802 $dbh->do("DELETE FROM `systempreferences` WHERE variable='hideBiblioNumber'");
803 print "Upgrade to $DBversion done ('alter finesMode systempreference, remove superfluous syspref.')\n";
804 SetVersion ($DBversion);
807 $DBversion = "3.00.00.039";
808 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
809 $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')");
810 $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')");
811 $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')");
812 $dbh->do("DELETE FROM `systempreferences` WHERE variable='HomeOrHoldingBranch'");
813 print "Upgrade to $DBversion done ('add circ sysprefs CircControl, finesCalendar, and uppercasesurnames, and delete HomeOrHoldingBranch.')\n";
814 SetVersion ($DBversion);
817 $DBversion = "3.00.00.040";
818 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
819 $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')");
820 $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')");
821 print "Upgrade to $DBversion done ('add circ sysprefs todaysIssuesDefaultSortOrder and previousIssuesDefaultSortOrder.')\n";
822 SetVersion ($DBversion);
826 $DBversion = "3.00.00.041";
827 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
828 # Strictly speaking it is not necessary to explicitly change
829 # NULL values to 0, because the ALTER TABLE statement will do that.
830 # However, setting them first avoids a warning.
831 $dbh->do("UPDATE items SET notforloan = 0 WHERE notforloan IS NULL");
832 $dbh->do("UPDATE items SET damaged = 0 WHERE damaged IS NULL");
833 $dbh->do("UPDATE items SET itemlost = 0 WHERE itemlost IS NULL");
834 $dbh->do("UPDATE items SET wthdrawn = 0 WHERE wthdrawn IS NULL");
835 $dbh->do("ALTER TABLE items
836 MODIFY notforloan tinyint(1) NOT NULL default 0,
837 MODIFY damaged tinyint(1) NOT NULL default 0,
838 MODIFY itemlost tinyint(1) NOT NULL default 0,
839 MODIFY wthdrawn tinyint(1) NOT NULL default 0");
840 print "Upgrade to $DBversion done (disallow NULL in several item status columns)\n";
841 SetVersion ($DBversion);
844 $DBversion = "3.00.00.042";
845 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
846 $dbh->do("ALTER TABLE aqbooksellers CHANGE name name mediumtext NOT NULL");
847 print "Upgrade to $DBversion done (disallow NULL in aqbooksellers.name; part of fix for bug 1251)\n";
848 SetVersion ($DBversion);
851 $DBversion = "3.00.00.043";
852 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
853 $dbh->do("ALTER TABLE `currency` ADD `symbol` varchar(5) default NULL, ADD `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP");
854 print "Upgrade to $DBversion done (currency table: add symbol and timestamp columns)\n";
855 SetVersion ($DBversion);
858 $DBversion = "3.00.00.044";
859 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
860 $dbh->do("ALTER TABLE deletedborrowers
861 ADD `altcontactfirstname` varchar(255) default NULL,
862 ADD `altcontactsurname` varchar(255) default NULL,
863 ADD `altcontactaddress1` varchar(255) default NULL,
864 ADD `altcontactaddress2` varchar(255) default NULL,
865 ADD `altcontactaddress3` varchar(255) default NULL,
866 ADD `altcontactzipcode` varchar(50) default NULL,
867 ADD `altcontactphone` varchar(50) default NULL
869 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES
870 ('OPACBaseURL',NULL,'Specify the Base URL of the OPAC, e.g., opac.mylibrary.com, the http:// will be added automatically by Koha.',NULL,'Free'),
871 ('language','en','Set the default language in the staff client.',NULL,'Languages'),
872 ('QueryAutoTruncate',1,'If ON, query truncation is enabled by default',NULL,'YesNo'),
873 ('QueryRemoveStopwords',0,'If ON, stopwords listed in the Administration area will be removed from queries',NULL,'YesNo')
875 print "Upgrade to $DBversion done (syncing deletedborrowers table with borrowers table)\n";
876 SetVersion ($DBversion);
879 #-- http://www.w3.org/International/articles/language-tags/
882 $DBversion = "3.00.00.045";
883 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
885 CREATE TABLE language_subtag_registry (
887 type varchar(25), -- language-script-region-variant-extension-privateuse
888 description varchar(25), -- only one of the possible descriptions for ease of reference, see language_descriptions for the complete list
890 KEY `subtag` (`subtag`)
891 ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
893 #-- TODO: add suppress_scripts
894 #-- this maps three letter codes defined in iso639.2 back to their
895 #-- two letter equivilents in rfc4646 (LOC maintains iso639+)
896 $dbh->do("CREATE TABLE language_rfc4646_to_iso639 (
897 rfc4646_subtag varchar(25),
898 iso639_2_code varchar(25),
899 KEY `rfc4646_subtag` (`rfc4646_subtag`)
900 ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
902 $dbh->do("CREATE TABLE language_descriptions (
906 description varchar(255),
908 ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
910 #-- bi-directional support, keyed by script subcode
911 $dbh->do("CREATE TABLE language_script_bidi (
912 rfc4646_subtag varchar(25), -- script subtag, Arab, Hebr, etc.
913 bidi varchar(3), -- rtl ltr
914 KEY `rfc4646_subtag` (`rfc4646_subtag`)
915 ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
917 #-- BIDI Stuff, Arabic and Hebrew
918 $dbh->do("INSERT INTO language_script_bidi(rfc4646_subtag,bidi)
919 VALUES( 'Arab', 'rtl')");
920 $dbh->do("INSERT INTO language_script_bidi(rfc4646_subtag,bidi)
921 VALUES( 'Hebr', 'rtl')");
923 #-- TODO: need to map language subtags to script subtags for detection
924 #-- of bidi when script is not specified (like ar, he)
925 $dbh->do("CREATE TABLE language_script_mapping (
926 language_subtag varchar(25),
927 script_subtag varchar(25),
928 KEY `language_subtag` (`language_subtag`)
929 ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
931 #-- Default mappings between script and language subcodes
932 $dbh->do("INSERT INTO language_script_mapping(language_subtag,script_subtag)
933 VALUES( 'ar', 'Arab')");
934 $dbh->do("INSERT INTO language_script_mapping(language_subtag,script_subtag)
935 VALUES( 'he', 'Hebr')");
937 print "Upgrade to $DBversion done (adding language subtag registry and basic BiDi support NOTE: You should import the subtag registry SQL)\n";
938 SetVersion ($DBversion);
941 $DBversion = "3.00.00.046";
942 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
943 $dbh->do("ALTER TABLE `subscription` CHANGE `numberlength` `numberlength` int(11) default NULL ,
944 CHANGE `weeklength` `weeklength` int(11) default NULL");
945 $dbh->do("CREATE TABLE `serialitems` (`serialid` int(11) NOT NULL, `itemnumber` int(11) NOT NULL, UNIQUE KEY (`serialid`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
946 $dbh->do("INSERT INTO `serialitems` SELECT `serialid`,`itemnumber` from serial where NOT ISNULL(itemnumber) && itemnumber <> '' && itemnumber NOT LIKE '%,%'");
947 print "Upgrade to $DBversion done (Add serialitems table to link serial issues to items. )\n";
948 SetVersion ($DBversion);
951 $DBversion = "3.00.00.047";
952 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
953 $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');");
954 print "Upgrade to $DBversion done ( Added OpacRenewalAllowed syspref )\n";
955 SetVersion ($DBversion);
958 $DBversion = "3.00.00.048";
959 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
960 $dbh->do("ALTER TABLE `items` ADD `more_subfields_xml` longtext default NULL");
961 print "Upgrade to $DBversion done (added items.more_subfields_xml)\n";
962 SetVersion ($DBversion);
965 $DBversion = "3.00.00.049";
966 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
967 $dbh->do("ALTER TABLE `z3950servers` ADD `encoding` text default NULL ");
968 print "Upgrade to $DBversion done ( Added encoding field to z3950servers table )\n";
969 SetVersion ($DBversion);
972 $DBversion = "3.00.00.050";
973 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
974 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('OpacHighlightedWords','0','If Set, query matched terms are highlighted in OPAC',NULL,'YesNo');");
975 print "Upgrade to $DBversion done ( Added OpacHighlightedWords syspref )\n";
976 SetVersion ($DBversion);
979 $DBversion = "3.00.00.051";
980 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
981 $dbh->do("UPDATE systempreferences SET explanation = 'Define the current theme for the OPAC interface.' WHERE variable = 'opacthemes';");
982 print "Upgrade to $DBversion done ( Corrected opacthemes explanation. )\n";
983 SetVersion ($DBversion);
986 $DBversion = "3.00.00.052";
987 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
988 $dbh->do("ALTER TABLE `deleteditems` ADD `more_subfields_xml` LONGTEXT DEFAULT NULL;");
989 print "Upgrade to $DBversion done ( Adding missing column to deleteditems table. )\n";
990 SetVersion ($DBversion);
993 $DBversion = "3.00.00.053";
994 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
995 $dbh->do("CREATE TABLE `printers_profile` (
996 `prof_id` int(4) NOT NULL auto_increment,
997 `printername` varchar(40) NOT NULL,
998 `tmpl_id` int(4) NOT NULL,
999 `paper_bin` varchar(20) NOT NULL,
1000 `offset_horz` float default NULL,
1001 `offset_vert` float default NULL,
1002 `creep_horz` float default NULL,
1003 `creep_vert` float default NULL,
1004 `unit` char(20) NOT NULL default 'POINT',
1005 PRIMARY KEY (`prof_id`),
1006 UNIQUE KEY `printername` (`printername`,`tmpl_id`,`paper_bin`),
1007 CONSTRAINT `printers_profile_pnfk_1` FOREIGN KEY (`tmpl_id`) REFERENCES `labels_templates` (`tmpl_id`) ON DELETE CASCADE ON UPDATE CASCADE
1008 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ");
1009 $dbh->do("CREATE TABLE `labels_profile` (
1010 `tmpl_id` int(4) NOT NULL,
1011 `prof_id` int(4) NOT NULL,
1012 UNIQUE KEY `tmpl_id` (`tmpl_id`),
1013 UNIQUE KEY `prof_id` (`prof_id`)
1014 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ");
1015 print "Upgrade to $DBversion done ( Printer Profile tables added )\n";
1016 SetVersion ($DBversion);
1019 $DBversion = "3.00.00.054";
1020 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1021 $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';");
1022 print "Upgrade to $DBversion done ( Added another barcode autogeneration sequence to barcode.pl. )\n";
1023 SetVersion ($DBversion);
1026 $DBversion = "3.00.00.055";
1027 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1028 $dbh->do("ALTER TABLE `zebraqueue` ADD KEY `zebraqueue_lookup` (`server`, `biblio_auth_number`, `operation`, `done`)");
1029 print "Upgrade to $DBversion done ( Added index on zebraqueue. )\n";
1030 SetVersion ($DBversion);
1032 $DBversion = "3.00.00.056";
1033 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1034 if (C4::Context->preference("marcflavour") eq 'UNIMARC') {
1035 $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) ");
1037 $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) ");
1039 $dbh->do("ALTER TABLE `items` ADD `enumchron` VARCHAR(80) DEFAULT NULL;");
1040 print "Upgrade to $DBversion done ( Added item.enumchron column, and framework map to 952h )\n";
1041 SetVersion ($DBversion);
1044 $DBversion = "3.00.00.057";
1045 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1046 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('OAI-PMH','0','if ON, OAI-PMH server is enabled',NULL,'YesNo');");
1047 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('OAI-PMH:archiveID','KOHA-OAI-TEST','OAI-PMH archive identification',NULL,'Free');");
1048 $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');");
1049 $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');");
1050 $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');");
1051 SetVersion ($DBversion);
1054 $DBversion = "3.00.00.058";
1055 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1056 $dbh->do("ALTER TABLE `opac_news`
1057 CHANGE `lang` `lang` VARCHAR( 25 )
1059 COLLATE utf8_general_ci
1061 print "Upgrade to $DBversion done ( lang field in opac_news made longer )\n";
1062 SetVersion ($DBversion);
1065 $DBversion = "3.00.00.059";
1066 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1068 $dbh->do("CREATE TABLE IF NOT EXISTS `labels_templates` (
1069 `tmpl_id` int(4) NOT NULL auto_increment,
1070 `tmpl_code` char(100) default '',
1071 `tmpl_desc` char(100) default '',
1072 `page_width` float default '0',
1073 `page_height` float default '0',
1074 `label_width` float default '0',
1075 `label_height` float default '0',
1076 `topmargin` float default '0',
1077 `leftmargin` float default '0',
1078 `cols` int(2) default '0',
1079 `rows` int(2) default '0',
1080 `colgap` float default '0',
1081 `rowgap` float default '0',
1082 `active` int(1) default NULL,
1083 `units` char(20) default 'PX',
1084 `fontsize` int(4) NOT NULL default '3',
1085 PRIMARY KEY (`tmpl_id`)
1086 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;");
1087 $dbh->do("CREATE TABLE IF NOT EXISTS `printers_profile` (
1088 `prof_id` int(4) NOT NULL auto_increment,
1089 `printername` varchar(40) NOT NULL,
1090 `tmpl_id` int(4) NOT NULL,
1091 `paper_bin` varchar(20) NOT NULL,
1092 `offset_horz` float default NULL,
1093 `offset_vert` float default NULL,
1094 `creep_horz` float default NULL,
1095 `creep_vert` float default NULL,
1096 `unit` char(20) NOT NULL default 'POINT',
1097 PRIMARY KEY (`prof_id`),
1098 UNIQUE KEY `printername` (`printername`,`tmpl_id`,`paper_bin`),
1099 CONSTRAINT `printers_profile_pnfk_1` FOREIGN KEY (`tmpl_id`) REFERENCES `labels_templates` (`tmpl_id`) ON DELETE CASCADE ON UPDATE CASCADE
1100 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ");
1101 print "Upgrade to $DBversion done ( Added labels_templates table if it did not exist. )\n";
1102 SetVersion ($DBversion);
1105 $DBversion = "3.00.00.060";
1106 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1107 $dbh->do("CREATE TABLE IF NOT EXISTS `patronimage` (
1108 `cardnumber` varchar(16) NOT NULL,
1109 `mimetype` varchar(15) NOT NULL,
1110 `imagefile` mediumblob NOT NULL,
1111 PRIMARY KEY (`cardnumber`),
1112 CONSTRAINT `patronimage_fk1` FOREIGN KEY (`cardnumber`) REFERENCES `borrowers` (`cardnumber`) ON DELETE CASCADE ON UPDATE CASCADE
1113 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;");
1114 print "Upgrade to $DBversion done ( Added patronimage table. )\n";
1115 SetVersion ($DBversion);
1118 $DBversion = "3.00.00.061";
1119 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1120 $dbh->do("ALTER TABLE labels_templates ADD COLUMN font char(10) NOT NULL DEFAULT 'TR';");
1121 print "Upgrade to $DBversion done ( Added font column to labels_templates )\n";
1122 SetVersion ($DBversion);
1125 $DBversion = "3.00.00.062";
1126 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1127 $dbh->do("CREATE TABLE `old_issues` (
1128 `borrowernumber` int(11) default NULL,
1129 `itemnumber` int(11) default NULL,
1130 `date_due` date default NULL,
1131 `branchcode` varchar(10) default NULL,
1132 `issuingbranch` varchar(18) default NULL,
1133 `returndate` date default NULL,
1134 `lastreneweddate` date default NULL,
1135 `return` varchar(4) default NULL,
1136 `renewals` tinyint(4) default NULL,
1137 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1138 `issuedate` date default NULL,
1139 KEY `old_issuesborridx` (`borrowernumber`),
1140 KEY `old_issuesitemidx` (`itemnumber`),
1141 KEY `old_bordate` (`borrowernumber`,`timestamp`),
1142 CONSTRAINT `old_issues_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`)
1143 ON DELETE SET NULL ON UPDATE SET NULL,
1144 CONSTRAINT `old_issues_ibfk_2` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`)
1145 ON DELETE SET NULL ON UPDATE SET NULL
1146 ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
1147 $dbh->do("CREATE TABLE `old_reserves` (
1148 `borrowernumber` int(11) default NULL,
1149 `reservedate` date default NULL,
1150 `biblionumber` int(11) default NULL,
1151 `constrainttype` varchar(1) default NULL,
1152 `branchcode` varchar(10) default NULL,
1153 `notificationdate` date default NULL,
1154 `reminderdate` date default NULL,
1155 `cancellationdate` date default NULL,
1156 `reservenotes` mediumtext,
1157 `priority` smallint(6) default NULL,
1158 `found` varchar(1) default NULL,
1159 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1160 `itemnumber` int(11) default NULL,
1161 `waitingdate` date default NULL,
1162 KEY `old_reserves_borrowernumber` (`borrowernumber`),
1163 KEY `old_reserves_biblionumber` (`biblionumber`),
1164 KEY `old_reserves_itemnumber` (`itemnumber`),
1165 KEY `old_reserves_branchcode` (`branchcode`),
1166 CONSTRAINT `old_reserves_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`)
1167 ON DELETE SET NULL ON UPDATE SET NULL,
1168 CONSTRAINT `old_reserves_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`)
1169 ON DELETE SET NULL ON UPDATE SET NULL,
1170 CONSTRAINT `old_reserves_ibfk_3` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`)
1171 ON DELETE SET NULL ON UPDATE SET NULL
1172 ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
1174 # move closed transactions to old_* tables
1175 $dbh->do("INSERT INTO old_issues SELECT * FROM issues WHERE returndate IS NOT NULL");
1176 $dbh->do("DELETE FROM issues WHERE returndate IS NOT NULL");
1177 $dbh->do("INSERT INTO old_reserves SELECT * FROM reserves WHERE cancellationdate IS NOT NULL OR found = 'F'");
1178 $dbh->do("DELETE FROM reserves WHERE cancellationdate IS NOT NULL OR found = 'F'");
1180 print "Upgrade to $DBversion done ( Added old_issues and old_reserves tables )\n";
1181 SetVersion ($DBversion);
1184 $DBversion = "3.00.00.063";
1185 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1186 $dbh->do("ALTER TABLE deleteditems
1187 CHANGE COLUMN booksellerid booksellerid MEDIUMTEXT DEFAULT NULL,
1188 ADD COLUMN enumchron VARCHAR(80) DEFAULT NULL AFTER more_subfields_xml,
1189 ADD COLUMN copynumber SMALLINT(6) DEFAULT NULL AFTER enumchron;");
1190 $dbh->do("ALTER TABLE items
1191 CHANGE COLUMN booksellerid booksellerid MEDIUMTEXT,
1192 ADD COLUMN copynumber SMALLINT(6) DEFAULT NULL AFTER enumchron;");
1193 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";
1194 SetVersion ($DBversion);
1197 $DBversion = "3.00.00.064";
1198 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1199 $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');");
1200 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('AWSAccessKeyID','','See: http://aws.amazon.com','','free');");
1201 $dbh->do("DELETE FROM `systempreferences` WHERE variable='AmazonDevKey';");
1202 $dbh->do("DELETE FROM `systempreferences` WHERE variable='XISBNAmazonSimilarItems';");
1203 $dbh->do("DELETE FROM `systempreferences` WHERE variable='OPACXISBNAmazonSimilarItems';");
1204 print "Upgrade to $DBversion done (IMPORTANT: Upgrading to Amazon.com Associates Web Service 4.0 ) \n";
1205 SetVersion ($DBversion);
1208 $DBversion = "3.00.00.065";
1209 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1210 $dbh->do("CREATE TABLE `patroncards` (
1211 `cardid` int(11) NOT NULL auto_increment,
1212 `batch_id` varchar(10) NOT NULL default '1',
1213 `borrowernumber` int(11) NOT NULL,
1214 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1215 PRIMARY KEY (`cardid`),
1216 KEY `patroncards_ibfk_1` (`borrowernumber`),
1217 CONSTRAINT `patroncards_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE
1218 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;");
1219 print "Upgrade to $DBversion done (Adding patroncards table for patroncards generation feature. ) \n";
1220 SetVersion ($DBversion);
1223 $DBversion = "3.00.00.066";
1224 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1225 $dbh->do("ALTER TABLE `virtualshelfcontents` MODIFY `dateadded` timestamp NOT NULL
1226 DEFAULT CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP;
1228 print "Upgrade to $DBversion done (fix for bug 1873: virtualshelfcontents dateadded column empty. ) \n";
1229 SetVersion ($DBversion);
1232 $DBversion = "3.00.00.067";
1233 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1234 $dbh->do("UPDATE systempreferences SET explanation = 'Enable patron images for the Staff Client', type = 'YesNo' WHERE variable = 'patronimages'");
1235 print "Upgrade to $DBversion done (Updating patronimages syspref to reflect current kohastructure.sql. ) \n";
1236 SetVersion ($DBversion);
1239 $DBversion = "3.00.00.068";
1240 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1241 $dbh->do("CREATE TABLE `permissions` (
1242 `module_bit` int(11) NOT NULL DEFAULT 0,
1243 `code` varchar(30) DEFAULT NULL,
1244 `description` varchar(255) DEFAULT NULL,
1245 PRIMARY KEY (`module_bit`, `code`),
1246 CONSTRAINT `permissions_ibfk_1` FOREIGN KEY (`module_bit`) REFERENCES `userflags` (`bit`)
1247 ON DELETE CASCADE ON UPDATE CASCADE
1248 ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
1249 $dbh->do("CREATE TABLE `user_permissions` (
1250 `borrowernumber` int(11) NOT NULL DEFAULT 0,
1251 `module_bit` int(11) NOT NULL DEFAULT 0,
1252 `code` varchar(30) DEFAULT NULL,
1253 CONSTRAINT `user_permissions_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`)
1254 ON DELETE CASCADE ON UPDATE CASCADE,
1255 CONSTRAINT `user_permissions_ibfk_2` FOREIGN KEY (`module_bit`, `code`)
1256 REFERENCES `permissions` (`module_bit`, `code`)
1257 ON DELETE CASCADE ON UPDATE CASCADE
1258 ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
1260 $dbh->do("INSERT INTO permissions (module_bit, code, description) VALUES
1261 (13, 'edit_news', 'Write news for the OPAC and staff interfaces'),
1262 (13, 'label_creator', 'Create printable labels and barcodes from catalog and patron data'),
1263 (13, 'edit_calendar', 'Define days when the library is closed'),
1264 (13, 'moderate_comments', 'Moderate patron comments'),
1265 (13, 'edit_notices', 'Define notices'),
1266 (13, 'edit_notice_status_triggers', 'Set notice/status triggers for overdue items'),
1267 (13, 'view_system_logs', 'Browse the system logs'),
1268 (13, 'inventory', 'Perform inventory (stocktaking) of your catalogue'),
1269 (13, 'stage_marc_import', 'Stage MARC records into the reservoir'),
1270 (13, 'manage_staged_marc', 'Managed staged MARC records, including completing and reversing imports'),
1271 (13, 'export_catalog', 'Export bibliographic and holdings data'),
1272 (13, 'import_patrons', 'Import patron data'),
1273 (13, 'delete_anonymize_patrons', 'Delete old borrowers and anonymize circulation history (deletes borrower reading history)'),
1274 (13, 'batch_upload_patron_images', 'Upload patron images in batch or one at a time'),
1275 (13, 'schedule_tasks', 'Schedule tasks to run')");
1277 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('GranularPermissions','0','Use detailed staff user permissions',NULL,'YesNo')");
1279 print "Upgrade to $DBversion done (adding permissions and user_permissions tables and GranularPermissions syspref) \n";
1280 SetVersion ($DBversion);
1283 $DBversion = "3.00.00.069";
1284 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1285 $dbh->do("ALTER TABLE labels_conf CHANGE COLUMN class classification int(1) DEFAULT NULL;");
1286 print "Upgrade to $DBversion done ( Correcting columname in labels_conf )\n";
1287 SetVersion ($DBversion);
1290 $DBversion = "3.00.00.070";
1291 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1292 $sth = $dbh->prepare("SELECT value FROM systempreferences WHERE variable='yuipath'");
1294 my ($value) = $sth->fetchrow;
1295 $value =~ s/2.3.1/2.5.1/;
1296 $dbh->do("UPDATE systempreferences SET value='$value' WHERE variable='yuipath';");
1297 print "Update yuipath syspref to 2.5.1 if necessary\n";
1298 SetVersion ($DBversion);
1301 $DBversion = "3.00.00.071";
1302 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1303 $dbh->do(" ALTER TABLE `subscription` ADD `serialsadditems` TINYINT( 1 ) NOT NULL DEFAULT '0';");
1304 # fill the new field with the previous systempreference value, then drop the syspref
1305 my $sth = $dbh->prepare("SELECT value FROM systempreferences WHERE variable='serialsadditems'");
1307 my ($serialsadditems) = $sth->fetchrow();
1308 $dbh->do("UPDATE subscription SET serialsadditems=$serialsadditems");
1309 $dbh->do("DELETE FROM systempreferences WHERE variable='serialsadditems'");
1310 print "Upgrade to $DBversion done ( moving serialsadditems from syspref to subscription )\n";
1311 SetVersion ($DBversion);
1314 $DBversion = "3.00.00.072";
1315 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1316 $dbh->do("ALTER TABLE labels_conf ADD COLUMN formatstring VARCHAR(64) DEFAULT NULL;");
1317 print "Upgrade to $DBversion done ( Adding format string to labels generator. )\n";
1318 SetVersion ($DBversion);
1321 $DBversion = "3.00.00.073";
1322 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1323 $dbh->do("DROP TABLE IF EXISTS `tags_all`;");
1325 CREATE TABLE `tags_all` (
1326 `tag_id` int(11) NOT NULL auto_increment,
1327 `borrowernumber` int(11) NOT NULL,
1328 `biblionumber` int(11) NOT NULL,
1329 `term` varchar(255) NOT NULL,
1330 `language` int(4) default NULL,
1331 `date_created` datetime NOT NULL,
1332 PRIMARY KEY (`tag_id`),
1333 KEY `tags_borrowers_fk_1` (`borrowernumber`),
1334 KEY `tags_biblionumber_fk_1` (`biblionumber`),
1335 CONSTRAINT `tags_borrowers_fk_1` FOREIGN KEY (`borrowernumber`)
1336 REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1337 CONSTRAINT `tags_biblionumber_fk_1` FOREIGN KEY (`biblionumber`)
1338 REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE
1339 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1341 $dbh->do("DROP TABLE IF EXISTS `tags_approval`;");
1343 CREATE TABLE `tags_approval` (
1344 `term` varchar(255) NOT NULL,
1345 `approved` int(1) NOT NULL default '0',
1346 `date_approved` datetime default NULL,
1347 `approved_by` int(11) default NULL,
1348 `weight_total` int(9) NOT NULL default '1',
1349 PRIMARY KEY (`term`),
1350 KEY `tags_approval_borrowers_fk_1` (`approved_by`),
1351 CONSTRAINT `tags_approval_borrowers_fk_1` FOREIGN KEY (`approved_by`)
1352 REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE
1353 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1355 $dbh->do("DROP TABLE IF EXISTS `tags_index`;");
1357 CREATE TABLE `tags_index` (
1358 `term` varchar(255) NOT NULL,
1359 `biblionumber` int(11) NOT NULL,
1360 `weight` int(9) NOT NULL default '1',
1361 PRIMARY KEY (`term`,`biblionumber`),
1362 KEY `tags_index_biblionumber_fk_1` (`biblionumber`),
1363 CONSTRAINT `tags_index_term_fk_1` FOREIGN KEY (`term`)
1364 REFERENCES `tags_approval` (`term`) ON DELETE CASCADE ON UPDATE CASCADE,
1365 CONSTRAINT `tags_index_biblionumber_fk_1` FOREIGN KEY (`biblionumber`)
1366 REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE
1367 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1370 INSERT INTO `systempreferences` VALUES
1371 ('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=',''),
1372 ('BakerTaylorEnabled','0','','Enable or disable all Baker & Taylor features.','YesNo'),
1373 ('BakerTaylorPassword','','','Baker & Taylor Password for Content Cafe (external content)','Textarea'),
1374 ('BakerTaylorUsername','','','Baker & Taylor Username for Content Cafe (external content)','Textarea'),
1375 ('TagsEnabled','1','','Enables or disables all tagging features. This is the main switch for tags.','YesNo'),
1376 ('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.',''),
1377 ('TagsInputOnDetail','1','','Allow users to input tags from the detail page.', 'YesNo'),
1378 ('TagsInputOnList', '0','','Allow users to input tags from the search results list.', 'YesNo'),
1379 ('TagsModeration', NULL,'','(unimplemented) Requires tags from patrons to be approved before becoming visible.','YesNo'),
1380 ('TagsShowOnDetail','10','','Number of tags to display on detail page. 0 is off.', 'Integer'),
1381 ('TagsShowOnList', '6','','Number of tags to display on search results list. 0 is off.','Integer')
1383 print "Upgrade to $DBversion done (Baker/Taylor,Tags: sysprefs and tables (tags_all, tags_index, tags_approval)) \n";
1384 SetVersion ($DBversion);
1387 $DBversion = "3.00.00.074";
1388 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1389 $dbh->do( q(update itemtypes set imageurl = concat( 'npl/', imageurl )
1390 where imageurl not like 'http%'
1391 and imageurl is not NULL
1392 and imageurl != '') );
1393 print "Upgrade to $DBversion done (updating imagetype.imageurls to reflect new icon locations.)\n";
1394 SetVersion ($DBversion);
1397 $DBversion = "3.00.00.075";
1398 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1399 $dbh->do( q(alter table authorised_values add imageurl varchar(200) default NULL) );
1400 print "Upgrade to $DBversion done (adding imageurl field to authorised_values table)\n";
1401 SetVersion ($DBversion);
1404 $DBversion = "3.00.00.076";
1405 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1406 $dbh->do("ALTER TABLE import_batches
1407 ADD COLUMN nomatch_action enum('create_new', 'ignore') NOT NULL default 'create_new' AFTER overlay_action");
1408 $dbh->do("ALTER TABLE import_batches
1409 ADD COLUMN item_action enum('always_add', 'add_only_for_matches', 'add_only_for_new', 'ignore')
1410 NOT NULL default 'always_add' AFTER nomatch_action");
1411 $dbh->do("ALTER TABLE import_batches
1412 MODIFY overlay_action enum('replace', 'create_new', 'use_template', 'ignore')
1413 NOT NULL default 'create_new'");
1414 $dbh->do("ALTER TABLE import_records
1415 MODIFY status enum('error', 'staged', 'imported', 'reverted', 'items_reverted',
1416 'ignored') NOT NULL default 'staged'");
1417 $dbh->do("ALTER TABLE import_items
1418 MODIFY status enum('error', 'staged', 'imported', 'reverted', 'ignored') NOT NULL default 'staged'");
1420 print "Upgrade to $DBversion done (changes to import_batches and import_records)\n";
1421 SetVersion ($DBversion);
1424 $DBversion = "3.00.00.077";
1425 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1426 # drop these tables only if they exist and none of them are empty
1427 # these tables are not defined in the packaged 2.2.9, but since it is believed
1428 # that at least one library may be using them in a post-2.2.9 but pre-3.0 Koha,
1429 # some care is taken.
1430 my ($print_error) = $dbh->{PrintError};
1431 $dbh->{PrintError} = 0;
1432 my ($raise_error) = $dbh->{RaiseError};
1433 $dbh->{RaiseError} = 1;
1437 eval { $count = $dbh->do("SELECT 1 FROM categorytable"); };
1441 eval { $count = $dbh->do("SELECT 1 FROM mediatypetable"); };
1445 eval { $count = $dbh->do("SELECT 1 FROM subcategorytable"); };
1451 $dbh->do("DROP TABLE IF EXISTS `categorytable`");
1452 $dbh->do("DROP TABLE IF EXISTS `mediatypetable`");
1453 $dbh->do("DROP TABLE IF EXISTS `subcategorytable`");
1456 $dbh->{PrintError} = $print_error;
1457 $dbh->{RaiseError} = $raise_error;
1458 print "Upgrade to $DBversion done (drop categorytable, subcategorytable, and mediatypetable)\n";
1459 SetVersion ($DBversion);
1462 $DBversion = "3.00.00.078";
1463 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1464 my ($print_error) = $dbh->{PrintError};
1465 $dbh->{PrintError} = 0;
1467 unless ($dbh->do("SELECT 1 FROM browser")) {
1468 $dbh->{PrintError} = $print_error;
1469 $dbh->do("CREATE TABLE `browser` (
1470 `level` int(11) NOT NULL,
1471 `classification` varchar(20) NOT NULL,
1472 `description` varchar(255) NOT NULL,
1473 `number` bigint(20) NOT NULL,
1474 `endnode` tinyint(4) NOT NULL
1475 ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
1477 $dbh->{PrintError} = $print_error;
1478 print "Upgrade to $DBversion done (add browser table if not already present)\n";
1479 SetVersion ($DBversion);
1482 =item DropAllForeignKeys($table)
1484 Drop all foreign keys of the table $table
1488 sub DropAllForeignKeys {
1490 # get the table description
1491 my $sth = $dbh->prepare("SHOW CREATE TABLE $table");
1493 my $vsc_structure = $sth->fetchrow;
1494 # split on CONSTRAINT keyword
1495 my @fks = split /CONSTRAINT /,$vsc_structure;
1498 # isolate what is before FOREIGN KEY, if there is something, it's a foreign key to drop
1499 $_ = /(.*) FOREIGN KEY.*/;
1502 # we have found 1 foreign, drop it
1503 $dbh->do("ALTER TABLE $table DROP FOREIGN KEY $id");
1510 =item TransformToNum
1512 Transform the Koha version from a 4 parts string
1513 to a number, with just 1 .
1517 sub TransformToNum {
1518 my $version = shift;
1519 # remove the 3 last . to have a Perl number
1520 $version =~ s/(.*\..*)\.(.*)\.(.*)/$1$2$3/;
1526 set the DBversion in the systempreferences
1531 my $kohaversion = TransformToNum(shift);
1532 if (C4::Context->preference('Version')) {
1533 my $finish=$dbh->prepare("UPDATE systempreferences SET value=? WHERE variable='Version'");
1534 $finish->execute($kohaversion);
1536 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')");
1537 $finish->execute($kohaversion);