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 default '0' AFTER shelfnumber");
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 $dbh->do("ALTER TABLE `virtualshelfcontents` ADD KEY biblionumber (biblionumber)");
68 # create the new foreign keys (on biblionumber)
69 $dbh->do("ALTER TABLE `virtualshelfcontents` ADD CONSTRAINT `virtualshelfcontents_ibfk_1` FOREIGN KEY (`shelfnumber`) REFERENCES `virtualshelves` (`shelfnumber`) ON DELETE CASCADE ON UPDATE CASCADE");
70 # re-create the foreign key on virtualshelf
71 $dbh->do("ALTER TABLE `virtualshelfcontents` ADD CONSTRAINT `shelfcontents_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE");
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` varchar(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`,
220 MODIFY `marc` LONGBLOB,
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 `marcxml` LONGTEXT NOT NULL AFTER `totalissues`,
228 ADD KEY `isbn` (`isbn`),
229 ADD KEY `publishercode` (`publishercode`)
232 $dbh->do("UPDATE `deletedbiblioitems`
233 SET `cn_class` = SUBSTR(TRIM(CONCAT_WS(' ', `classification`, `dewey`)), 1, 30),
234 `cn_item` = `subclass`,
237 $dbh->do("ALTER TABLE `deletedbiblioitems`
238 DROP COLUMN `classification`,
240 DROP COLUMN `subclass`,
241 DROP COLUMN `lcsort`,
245 # deleteditems changes
246 $dbh->do("ALTER TABLE `deleteditems`
247 MODIFY `barcode` VARCHAR(20) DEFAULT NULL,
248 MODIFY `price` DECIMAL(8,2) DEFAULT NULL,
249 MODIFY `replacementprice` DECIMAL(8,2) DEFAULT NULL,
251 MODIFY `itemcallnumber` VARCHAR(30) DEFAULT NULL AFTER `wthdrawn`,
252 MODIFY `holdingbranch` VARCHAR(10) DEFAULT NULL,
254 MODIFY `timestamp` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP AFTER `paidfor`,
256 ADD `cn_source` VARCHAR(10) DEFAULT NULL AFTER `onloan`,
257 ADD `cn_sort` VARCHAR(30) DEFAULT NULL AFTER `cn_source`,
258 ADD `ccode` VARCHAR(10) DEFAULT NULL AFTER `cn_sort`,
259 ADD `materials` VARCHAR(10) DEFAULT NULL AFTER `ccode`,
260 ADD `uri` VARCHAR(255) DEFAULT NULL AFTER `materials`,
261 MODIFY `marc` LONGBLOB AFTER `uri`,
263 DROP KEY `itembarcodeidx`,
264 DROP KEY `itembinoidx`,
265 DROP KEY `itembibnoidx`,
266 ADD UNIQUE KEY `delitembarcodeidx` (`barcode`),
267 ADD KEY `delitembinoidx` (`biblioitemnumber`),
268 ADD KEY `delitembibnoidx` (`biblionumber`),
269 ADD KEY `delhomebranch` (`homebranch`),
270 ADD KEY `delholdingbranch` (`holdingbranch`)");
271 $dbh->do("UPDATE deleteditems SET `ccode` = `itype`");
272 $dbh->do("ALTER TABLE deleteditems DROP `itype`");
273 $dbh->do("UPDATE `deleteditems` SET `cn_sort` = `itemcallnumber`");
276 $dbh->do("ALTER TABLE `items` ADD `cn_source` VARCHAR(10) DEFAULT NULL AFTER `onloan`,
277 ADD `cn_sort` VARCHAR(30) DEFAULT NULL AFTER `cn_source`,
278 ADD `ccode` VARCHAR(10) DEFAULT NULL AFTER `cn_sort`,
279 ADD `materials` VARCHAR(10) DEFAULT NULL AFTER `ccode`,
280 ADD `uri` VARCHAR(255) DEFAULT NULL AFTER `materials`
282 $dbh->do("ALTER TABLE `items`
283 DROP KEY `itembarcodeidx`,
284 ADD UNIQUE KEY `itembarcodeidx` (`barcode`)");
286 # map items.itype to items.ccode and
287 # set cn_sort to itemcallnumber -- as with biblioitems.cn_sort,
288 # will have to be subsequently updated per user's default
289 # classification scheme
290 $dbh->do("UPDATE `items` SET `cn_sort` = `itemcallnumber`,
293 $dbh->do("ALTER TABLE `items` DROP `cutterextra`,
296 print "Upgrade to $DBversion done (major changes to biblio, biblioitems, items, and deleted* versions of same\n";
297 SetVersion ($DBversion);
300 $DBversion = "3.00.00.010";
301 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
302 $dbh->do("CREATE INDEX `userid` ON borrowers (`userid`) ");
303 print "Upgrade to $DBversion done (userid index added)\n";
304 SetVersion ($DBversion);
307 $DBversion = "3.00.00.011";
308 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
309 $dbh->do("ALTER TABLE `branchcategories` CHANGE `categorycode` `categorycode` varchar(10) ");
310 $dbh->do("ALTER TABLE `branchcategories` CHANGE `categoryname` `categoryname` varchar(32) ");
311 $dbh->do("ALTER TABLE `branchcategories` ADD COLUMN `categorytype` varchar(16) ");
312 $dbh->do("UPDATE `branchcategories` SET `categorytype` = 'properties'");
313 $dbh->do("ALTER TABLE `branchrelations` CHANGE `categorycode` `categorycode` varchar(10) ");
314 print "Upgrade to $DBversion done (added branchcategory type)\n";
315 SetVersion ($DBversion);
318 $DBversion = "3.00.00.012";
319 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
320 $dbh->do("CREATE TABLE `class_sort_rules` (
321 `class_sort_rule` varchar(10) NOT NULL default '',
322 `description` mediumtext,
323 `sort_routine` varchar(30) NOT NULL default '',
324 PRIMARY KEY (`class_sort_rule`),
325 UNIQUE KEY `class_sort_rule_idx` (`class_sort_rule`)
326 ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
327 $dbh->do("CREATE TABLE `class_sources` (
328 `cn_source` varchar(10) NOT NULL default '',
329 `description` mediumtext,
330 `used` tinyint(4) NOT NULL default 0,
331 `class_sort_rule` varchar(10) NOT NULL default '',
332 PRIMARY KEY (`cn_source`),
333 UNIQUE KEY `cn_source_idx` (`cn_source`),
334 KEY `used_idx` (`used`),
335 CONSTRAINT `class_source_ibfk_1` FOREIGN KEY (`class_sort_rule`)
336 REFERENCES `class_sort_rules` (`class_sort_rule`)
337 ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
338 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type)
339 VALUES('DefaultClassificationSource','ddc',
340 'Default classification scheme used by the collection. E.g., Dewey, LCC, etc.', NULL,'free')");
341 $dbh->do("INSERT INTO `class_sort_rules` (`class_sort_rule`, `description`, `sort_routine`) VALUES
342 ('dewey', 'Default filing rules for DDC', 'Dewey'),
343 ('lcc', 'Default filing rules for LCC', 'LCC'),
344 ('generic', 'Generic call number filing rules', 'Generic')");
345 $dbh->do("INSERT INTO `class_sources` (`cn_source`, `description`, `used`, `class_sort_rule`) VALUES
346 ('ddc', 'Dewey Decimal Classification', 1, 'dewey'),
347 ('lcc', 'Library of Congress Classification', 1, 'lcc'),
348 ('udc', 'Universal Decimal Classification', 0, 'generic'),
349 ('sudocs', 'SuDoc Classification (U.S. GPO)', 0, 'generic'),
350 ('z', 'Other/Generic Classification Scheme', 0, 'generic')");
351 print "Upgrade to $DBversion done (classification sources added)\n";
352 SetVersion ($DBversion);
355 $DBversion = "3.00.00.013";
356 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
357 $dbh->do("CREATE TABLE `import_batches` (
358 `import_batch_id` int(11) NOT NULL auto_increment,
359 `template_id` int(11) default NULL,
360 `branchcode` varchar(10) default NULL,
361 `num_biblios` int(11) NOT NULL default 0,
362 `num_items` int(11) NOT NULL default 0,
363 `upload_timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP,
364 `overlay_action` enum('replace', 'create_new', 'use_template') NOT NULL default 'create_new',
365 `import_status` enum('staging', 'staged', 'importing', 'imported', 'reverting', 'reverted', 'cleaned') NOT NULL default 'staging',
366 `batch_type` enum('batch', 'z3950') NOT NULL default 'batch',
367 `file_name` varchar(100),
368 `comments` mediumtext,
369 PRIMARY KEY (`import_batch_id`),
370 KEY `branchcode` (`branchcode`)
371 ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
372 $dbh->do("CREATE TABLE `import_records` (
373 `import_record_id` int(11) NOT NULL auto_increment,
374 `import_batch_id` int(11) NOT NULL,
375 `branchcode` varchar(10) default NULL,
376 `record_sequence` int(11) NOT NULL default 0,
377 `upload_timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP,
378 `import_date` DATE default NULL,
379 `marc` longblob NOT NULL,
380 `marcxml` longtext NOT NULL,
381 `marcxml_old` longtext NOT NULL,
382 `record_type` enum('biblio', 'auth', 'holdings') NOT NULL default 'biblio',
383 `overlay_status` enum('no_match', 'auto_match', 'manual_match', 'match_applied') NOT NULL default 'no_match',
384 `status` enum('error', 'staged', 'imported', 'reverted', 'items_reverted') NOT NULL default 'staged',
385 `import_error` mediumtext,
386 `encoding` varchar(40) NOT NULL default '',
387 `z3950random` varchar(40) default NULL,
388 PRIMARY KEY (`import_record_id`),
389 CONSTRAINT `import_records_ifbk_1` FOREIGN KEY (`import_batch_id`)
390 REFERENCES `import_batches` (`import_batch_id`) ON DELETE CASCADE ON UPDATE CASCADE,
391 KEY `branchcode` (`branchcode`),
392 KEY `batch_sequence` (`import_batch_id`, `record_sequence`)
393 ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
394 $dbh->do("CREATE TABLE `import_record_matches` (
395 `import_record_id` int(11) NOT NULL,
396 `candidate_match_id` int(11) NOT NULL,
397 `score` int(11) NOT NULL default 0,
398 CONSTRAINT `import_record_matches_ibfk_1` FOREIGN KEY (`import_record_id`)
399 REFERENCES `import_records` (`import_record_id`) ON DELETE CASCADE ON UPDATE CASCADE,
400 KEY `record_score` (`import_record_id`, `score`)
401 ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
402 $dbh->do("CREATE TABLE `import_biblios` (
403 `import_record_id` int(11) NOT NULL,
404 `matched_biblionumber` int(11) default NULL,
405 `control_number` varchar(25) default NULL,
406 `original_source` varchar(25) default NULL,
407 `title` varchar(128) default NULL,
408 `author` varchar(80) default NULL,
409 `isbn` varchar(14) default NULL,
410 `issn` varchar(9) default NULL,
411 `has_items` tinyint(1) NOT NULL default 0,
412 CONSTRAINT `import_biblios_ibfk_1` FOREIGN KEY (`import_record_id`)
413 REFERENCES `import_records` (`import_record_id`) ON DELETE CASCADE ON UPDATE CASCADE,
414 KEY `matched_biblionumber` (`matched_biblionumber`),
415 KEY `title` (`title`),
417 ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
418 $dbh->do("CREATE TABLE `import_items` (
419 `import_items_id` int(11) NOT NULL auto_increment,
420 `import_record_id` int(11) NOT NULL,
421 `itemnumber` int(11) default NULL,
422 `branchcode` varchar(10) default NULL,
423 `status` enum('error', 'staged', 'imported', 'reverted') NOT NULL default 'staged',
424 `marcxml` longtext NOT NULL,
425 `import_error` mediumtext,
426 PRIMARY KEY (`import_items_id`),
427 CONSTRAINT `import_items_ibfk_1` FOREIGN KEY (`import_record_id`)
428 REFERENCES `import_records` (`import_record_id`) ON DELETE CASCADE ON UPDATE CASCADE,
429 KEY `itemnumber` (`itemnumber`),
430 KEY `branchcode` (`branchcode`)
431 ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
433 $dbh->do("INSERT INTO `import_batches`
434 (`overlay_action`, `import_status`, `batch_type`, `file_name`)
435 SELECT distinct 'create_new', 'staged', 'z3950', `file`
436 FROM `marc_breeding`");
438 $dbh->do("INSERT INTO `import_records`
439 (`import_batch_id`, `import_record_id`, `record_sequence`, `marc`, `record_type`, `status`,
440 `encoding`, `z3950random`, `marcxml`, `marcxml_old`)
441 SELECT `import_batch_id`, `id`, 1, `marc`, 'biblio', 'staged', `encoding`, `z3950random`, '', ''
443 JOIN `import_batches` ON (`file_name` = `file`)");
445 $dbh->do("INSERT INTO `import_biblios`
446 (`import_record_id`, `title`, `author`, `isbn`)
447 SELECT `import_record_id`, `title`, `author`, `isbn`
449 JOIN `import_records` ON (`import_record_id` = `id`)");
451 $dbh->do("UPDATE `import_batches`
452 SET `num_biblios` = (
454 FROM `import_records`
455 WHERE `import_batch_id` = `import_batches`.`import_batch_id`
458 $dbh->do("DROP TABLE `marc_breeding`");
460 print "Upgrade to $DBversion done (import_batches et al. added)\n";
461 SetVersion ($DBversion);
464 $DBversion = "3.00.00.014";
465 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
466 $dbh->do("ALTER TABLE subscription ADD lastbranch VARCHAR(4)");
467 print "Upgrade to $DBversion done (userid index added)\n";
468 SetVersion ($DBversion);
471 $DBversion = "3.00.00.015";
472 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
473 $dbh->do("CREATE TABLE `saved_sql` (
474 `id` int(11) NOT NULL auto_increment,
475 `borrowernumber` int(11) default NULL,
476 `date_created` datetime default NULL,
477 `last_modified` datetime default NULL,
479 `last_run` datetime default NULL,
480 `report_name` varchar(255) default NULL,
481 `type` varchar(255) default NULL,
484 KEY boridx (`borrowernumber`)
485 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;");
486 $dbh->do("CREATE TABLE `saved_reports` (
487 `id` int(11) NOT NULL auto_increment,
488 `report_id` int(11) default NULL,
490 `date_run` datetime default NULL,
492 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;");
493 print "Upgrade to $DBversion done (saved_sql and saved_reports added)\n";
494 SetVersion ($DBversion);
497 $DBversion = "3.00.00.016";
498 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
499 $dbh->do(" CREATE TABLE reports_dictionary (
500 id int(11) NOT NULL auto_increment,
501 name varchar(255) default NULL,
503 date_created datetime default NULL,
504 date_modified datetime default NULL,
506 area int(11) default NULL,
508 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ");
509 print "Upgrade to $DBversion done (reports_dictionary) added)\n";
510 SetVersion ($DBversion);
513 $DBversion = "3.00.00.017";
514 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
515 $dbh->do("ALTER TABLE action_logs DROP PRIMARY KEY");
516 $dbh->do("ALTER TABLE action_logs ADD KEY timestamp (timestamp,user)");
517 $dbh->do("ALTER TABLE action_logs ADD action_id INT(11) NOT NULL FIRST");
518 $dbh->do("UPDATE action_logs SET action_id = if (\@a, \@a:=\@a+1, \@a:=1)");
519 $dbh->do("ALTER TABLE action_logs MODIFY action_id INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY");
520 print "Upgrade to $DBversion done (added column to action_logs)\n";
521 SetVersion ($DBversion);
524 $DBversion = "3.00.00.018";
525 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
526 $dbh->do("ALTER TABLE `zebraqueue`
527 ADD `done` INT NOT NULL DEFAULT '0',
528 ADD `time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ;
530 print "Upgrade to $DBversion done (adding timestamp and done columns to zebraque table to improve problem tracking) added)\n";
531 SetVersion ($DBversion);
534 $DBversion = "3.00.00.019";
535 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
536 $dbh->do("ALTER TABLE biblio MODIFY biblionumber INT(11) NOT NULL AUTO_INCREMENT");
537 $dbh->do("ALTER TABLE biblioitems MODIFY biblioitemnumber INT(11) NOT NULL AUTO_INCREMENT");
538 $dbh->do("ALTER TABLE items MODIFY itemnumber INT(11) NOT NULL AUTO_INCREMENT");
539 print "Upgrade to $DBversion done (made bib/item PKs auto_increment)\n";
540 SetVersion ($DBversion);
543 $DBversion = "3.00.00.020";
544 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
545 $dbh->do("ALTER TABLE deleteditems
546 DROP KEY `delitembarcodeidx`,
547 ADD KEY `delitembarcodeidx` (`barcode`)");
548 print "Upgrade to $DBversion done (dropped uniqueness of key on deleteditems.barcode)\n";
549 SetVersion ($DBversion);
552 $DBversion = "3.00.00.021";
553 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
554 $dbh->do("ALTER TABLE items CHANGE homebranch homebranch VARCHAR(10)");
555 $dbh->do("ALTER TABLE deleteditems CHANGE homebranch homebranch VARCHAR(10)");
556 $dbh->do("ALTER TABLE statistics CHANGE branch branch VARCHAR(10)");
557 $dbh->do("ALTER TABLE subscription CHANGE lastbranch lastbranch VARCHAR(10)");
558 print "Upgrade to $DBversion done (extended missed branchcode columns to 10 chars)\n";
559 SetVersion ($DBversion);
562 $DBversion = "3.00.00.022";
563 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
564 $dbh->do("ALTER TABLE items
565 ADD `damaged` tinyint(1) default NULL AFTER notforloan");
566 $dbh->do("ALTER TABLE deleteditems
567 ADD `damaged` tinyint(1) default NULL AFTER notforloan");
568 print "Upgrade to $DBversion done (adding damaged column to items table)\n";
569 SetVersion ($DBversion);
572 $DBversion = "3.00.00.023";
573 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
574 $dbh->do("INSERT INTO `systempreferences` (variable,value,options,explanation,type)
575 VALUES ('yuipath','http://yui.yahooapis.com/2.3.1/build','Insert the path to YUI libraries','','free')");
576 print "Upgrade to $DBversion done (adding new system preference for controlling YUI path)\n";
577 SetVersion ($DBversion);
579 $DBversion = "3.00.00.024";
580 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
581 $dbh->do("ALTER TABLE biblioitems CHANGE itemtype itemtype VARCHAR(10)");
582 print "Upgrade to $DBversion done (changing itemtype to (10))\n";
583 SetVersion ($DBversion);
586 $DBversion = "3.00.00.025";
587 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
588 $dbh->do("ALTER TABLE items ADD COLUMN itype VARCHAR(10)");
589 $dbh->do("ALTER TABLE deleteditems ADD COLUMN itype VARCHAR(10) AFTER uri");
590 if(C4::Context->preference('item-level_itypes')){
591 $dbh->do('update items,biblioitems set items.itype=biblioitems.itemtype where items.biblionumber=biblioitems.biblionumber and itype is null');
593 print "Upgrade to $DBversion done (reintroduce items.itype - fill from itemtype)\n ";
594 SetVersion ($DBversion);
597 $DBversion = "3.00.00.026";
598 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
599 $dbh->do("INSERT INTO `systempreferences` (variable,value,options,explanation,type)
600 VALUES ('HomeOrHoldingBranch','homebranch','homebranch|holdingbranch','With independent branches turned on this decides whether to check the items holdingbranch or homebranch at circulatilon','choice')");
601 print "Upgrade to $DBversion done (adding new system preference for choosing whether homebranch or holdingbranch is checked in circulation)\n";
602 SetVersion ($DBversion);
605 $DBversion = "3.00.00.027";
606 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
607 $dbh->do("CREATE TABLE `marc_matchers` (
608 `matcher_id` int(11) NOT NULL auto_increment,
609 `code` varchar(10) NOT NULL default '',
610 `description` varchar(255) NOT NULL default '',
611 `record_type` varchar(10) NOT NULL default 'biblio',
612 `threshold` int(11) NOT NULL default 0,
613 PRIMARY KEY (`matcher_id`),
615 KEY `record_type` (`record_type`)
616 ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
617 $dbh->do("CREATE TABLE `matchpoints` (
618 `matcher_id` int(11) NOT NULL,
619 `matchpoint_id` int(11) NOT NULL auto_increment,
620 `search_index` varchar(30) NOT NULL default '',
621 `score` int(11) NOT NULL default 0,
622 PRIMARY KEY (`matchpoint_id`),
623 CONSTRAINT `matchpoints_ifbk_1` FOREIGN KEY (`matcher_id`)
624 REFERENCES `marc_matchers` (`matcher_id`) ON DELETE CASCADE ON UPDATE CASCADE
625 ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
626 $dbh->do("CREATE TABLE `matchpoint_components` (
627 `matchpoint_id` int(11) NOT NULL,
628 `matchpoint_component_id` int(11) NOT NULL auto_increment,
629 sequence int(11) NOT NULL default 0,
630 tag varchar(3) NOT NULL default '',
631 subfields varchar(40) NOT NULL default '',
632 offset int(4) NOT NULL default 0,
633 length int(4) NOT NULL default 0,
634 PRIMARY KEY (`matchpoint_component_id`),
635 KEY `by_sequence` (`matchpoint_id`, `sequence`),
636 CONSTRAINT `matchpoint_components_ifbk_1` FOREIGN KEY (`matchpoint_id`)
637 REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE
638 ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
639 $dbh->do("CREATE TABLE `matchpoint_component_norms` (
640 `matchpoint_component_id` int(11) NOT NULL,
641 `sequence` int(11) NOT NULL default 0,
642 `norm_routine` varchar(50) NOT NULL default '',
643 KEY `matchpoint_component_norms` (`matchpoint_component_id`, `sequence`),
644 CONSTRAINT `matchpoint_component_norms_ifbk_1` FOREIGN KEY (`matchpoint_component_id`)
645 REFERENCES `matchpoint_components` (`matchpoint_component_id`) ON DELETE CASCADE ON UPDATE CASCADE
646 ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
647 $dbh->do("CREATE TABLE `matcher_matchpoints` (
648 `matcher_id` int(11) NOT NULL,
649 `matchpoint_id` int(11) NOT NULL,
650 CONSTRAINT `matcher_matchpoints_ifbk_1` FOREIGN KEY (`matcher_id`)
651 REFERENCES `marc_matchers` (`matcher_id`) ON DELETE CASCADE ON UPDATE CASCADE,
652 CONSTRAINT `matcher_matchpoints_ifbk_2` FOREIGN KEY (`matchpoint_id`)
653 REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE
654 ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
655 $dbh->do("CREATE TABLE `matchchecks` (
656 `matcher_id` int(11) NOT NULL,
657 `matchcheck_id` int(11) NOT NULL auto_increment,
658 `source_matchpoint_id` int(11) NOT NULL,
659 `target_matchpoint_id` int(11) NOT NULL,
660 PRIMARY KEY (`matchcheck_id`),
661 CONSTRAINT `matcher_matchchecks_ifbk_1` FOREIGN KEY (`matcher_id`)
662 REFERENCES `marc_matchers` (`matcher_id`) ON DELETE CASCADE ON UPDATE CASCADE,
663 CONSTRAINT `matcher_matchchecks_ifbk_2` FOREIGN KEY (`source_matchpoint_id`)
664 REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE,
665 CONSTRAINT `matcher_matchchecks_ifbk_3` FOREIGN KEY (`target_matchpoint_id`)
666 REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE
667 ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
668 print "Upgrade to $DBversion done (added C4::Matcher serialization tables)\n ";
669 SetVersion ($DBversion);
672 $DBversion = "3.00.00.028";
673 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
674 $dbh->do("INSERT INTO `systempreferences` (variable,value,options,explanation,type)
675 VALUES ('canreservefromotherbranches','1','','With Independent branches on, can a user from one library reserve an item from another library','YesNo')");
676 print "Upgrade to $DBversion done (adding new system preference for changing reserve/holds behaviour with independent branches)\n";
677 SetVersion ($DBversion);
681 $DBversion = "3.00.00.029";
682 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
683 $dbh->do("ALTER TABLE `import_batches` ADD `matcher_id` int(11) NULL AFTER `import_batch_id`");
684 print "Upgrade to $DBversion done (adding matcher_id to import_batches)\n";
685 SetVersion ($DBversion);
688 $DBversion = "3.00.00.030";
689 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
691 CREATE TABLE services_throttle (
692 service_type varchar(10) NOT NULL default '',
693 service_count varchar(45) default NULL,
694 PRIMARY KEY (service_type)
695 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
697 $dbh->do("INSERT INTO `systempreferences` (variable,value,options,explanation,type)
698 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')");
699 $dbh->do("INSERT INTO `systempreferences` (variable,value,options,explanation,type)
700 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')");
701 $dbh->do("INSERT INTO `systempreferences` (variable,value,options,explanation,type)
702 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')");
703 $dbh->do("INSERT INTO `systempreferences` (variable,value,options,explanation,type)
704 VALUES ('XISBNDailyLimit',499,'','The xISBN Web service is free for non-commercial use when usage does not exceed 500 requests per day','free')");
705 $dbh->do("INSERT INTO `systempreferences` (variable,value,options,explanation,type)
706 VALUES ('PINESISBN',0,'','Use with FRBRizeEditions. If ON, Koha will use PINES OISBN web service in the Editions tab on the detail pages.','YesNo')");
707 $dbh->do("INSERT INTO `systempreferences` (variable,value,options,explanation,type)
708 VALUES ('ThingISBN',0,'','Use with FRBRizeEditions. If ON, Koha will use the ThingISBN web service in the Editions tab on the detail pages.','YesNo')");
709 print "Upgrade to $DBversion done (adding services throttle table and sysprefs for xISBN)\n";
710 SetVersion ($DBversion);
713 $DBversion = "3.00.00.031";
714 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
716 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('QueryStemming',1,'If ON, enables query stemming',NULL,'YesNo')");
717 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('QueryFuzzy',1,'If ON, enables fuzzy option for searches',NULL,'YesNo')");
718 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('QueryWeightFields',1,'If ON, enables field weighting',NULL,'YesNo')");
719 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('WebBasedSelfCheck',0,'If ON, enables the web-based self-check system',NULL,'YesNo')");
720 $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')");
721 $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')");
722 $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')");
723 $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')");
724 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('defaultSortOrder',NULL,'Specify the default sort order','asc|dsc|az|za','Choice')");
725 $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')");
726 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('OPACdefaultSortOrder',NULL,'Specify the default sort order','asc|dsc|za|az','Choice')");
727 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('staffClientBaseURL','','Specify the base URL of the staff client',NULL,'free')");
728 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('minPasswordLength',3,'Specify the minimum length of a patron/staff password',NULL,'free')");
729 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('noItemTypeImages',0,'If ON, disables item-type images',NULL,'YesNo')");
730 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('noOPACHolds',0,'If ON, disables holds globally',NULL,'YesNo')");
731 $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')");
732 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('holdCancelLength','','Specify how many days before a hold is canceled',NULL,'free')");
733 $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')");
734 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('finesMode','test','Choose the fines mode, test or production','test|production','Choice')");
735 $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')");
736 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('itemBarcodeInputFilter','','If set, allows specification of a item barcode input filter','cuecat','Choice')");
737 $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')");
738 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('URLLinkText','','Text to display as the link anchor in the OPAC',NULL,'free')");
739 $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')");
740 $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')");
741 $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')");
742 $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')");
743 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('OPACUserCSS',0,'Add CSS to be included in the OPAC',NULL,'free')");
744 $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')");
746 print "Upgrade to $DBversion done (adding additional system preference)\n";
747 SetVersion ($DBversion);
750 $DBversion = "3.00.00.032";
751 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
752 $dbh->do("UPDATE `marc_subfield_structure` SET `kohafield` = 'items.wthdrawn' WHERE `kohafield` = 'items.withdrawn'");
753 print "Upgrade to $DBversion done (fixed MARC framework references to items.withdrawn)\n";
754 SetVersion ($DBversion);
757 $DBversion = "3.00.00.033";
758 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
759 $dbh->do("INSERT INTO `userflags` VALUES(17,'staffaccess','Modify login / permissions for staff users',0)");
760 print "Upgrade to $DBversion done (Adding permissions flag for staff member access modification. )\n";
761 SetVersion ($DBversion);
764 $DBversion = "3.00.00.034";
765 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
766 $dbh->do("ALTER TABLE `virtualshelves` ADD COLUMN `sortfield` VARCHAR(16) ");
767 print "Upgrade to $DBversion done (Adding sortfield for Virtual Shelves. )\n";
768 SetVersion ($DBversion);
771 $DBversion = "3.00.00.035";
772 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
773 $dbh->do("UPDATE marc_subfield_structure
774 SET authorised_value = 'cn_source'
775 WHERE kohafield IN ('items.cn_source', 'biblioitems.cn_source')
776 AND (authorised_value is NULL OR authorised_value = '')");
777 print "Upgrade to $DBversion done (MARC frameworks: make classification source a drop-down)\n";
778 SetVersion ($DBversion);
781 $DBversion = "3.00.00.036";
782 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
783 $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');");
784 print "Upgrade to $DBversion done (OPACItemsResultsDisplay systempreference added)\n";
785 SetVersion ($DBversion);
788 $DBversion = "3.00.00.037";
789 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
790 $dbh->do("ALTER TABLE `borrowers` ADD COLUMN `altcontactfirstname` varchar(255)");
791 $dbh->do("ALTER TABLE `borrowers` ADD COLUMN `altcontactsurname` varchar(255)");
792 $dbh->do("ALTER TABLE `borrowers` ADD COLUMN `altcontactaddress1` varchar(255)");
793 $dbh->do("ALTER TABLE `borrowers` ADD COLUMN `altcontactaddress2` varchar(255)");
794 $dbh->do("ALTER TABLE `borrowers` ADD COLUMN `altcontactaddress3` varchar(255)");
795 $dbh->do("ALTER TABLE `borrowers` ADD COLUMN `altcontactzipcode` varchar(50)");
796 $dbh->do("ALTER TABLE `borrowers` ADD COLUMN `altcontactphone` varchar(50)");
797 print "Upgrade to $DBversion done (Adding Alternative Contact Person information to borrowers table)\n";
798 SetVersion ($DBversion);
801 $DBversion = "3.00.00.038";
802 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
803 $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'");
804 $dbh->do("DELETE FROM `systempreferences` WHERE variable='hideBiblioNumber'");
805 print "Upgrade to $DBversion done ('alter finesMode systempreference, remove superfluous syspref.')\n";
806 SetVersion ($DBversion);
809 $DBversion = "3.00.00.039";
810 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
811 $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')");
812 $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')");
813 $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')");
814 $dbh->do("DELETE FROM `systempreferences` WHERE variable='HomeOrHoldingBranch'");
815 print "Upgrade to $DBversion done ('add circ sysprefs CircControl, finesCalendar, and uppercasesurnames, and delete HomeOrHoldingBranch.')\n";
816 SetVersion ($DBversion);
819 $DBversion = "3.00.00.040";
820 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
821 $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')");
822 $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')");
823 print "Upgrade to $DBversion done ('add circ sysprefs todaysIssuesDefaultSortOrder and previousIssuesDefaultSortOrder.')\n";
824 SetVersion ($DBversion);
828 $DBversion = "3.00.00.041";
829 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
830 # Strictly speaking it is not necessary to explicitly change
831 # NULL values to 0, because the ALTER TABLE statement will do that.
832 # However, setting them first avoids a warning.
833 $dbh->do("UPDATE items SET notforloan = 0 WHERE notforloan IS NULL");
834 $dbh->do("UPDATE items SET damaged = 0 WHERE damaged IS NULL");
835 $dbh->do("UPDATE items SET itemlost = 0 WHERE itemlost IS NULL");
836 $dbh->do("UPDATE items SET wthdrawn = 0 WHERE wthdrawn IS NULL");
837 $dbh->do("ALTER TABLE items
838 MODIFY notforloan tinyint(1) NOT NULL default 0,
839 MODIFY damaged tinyint(1) NOT NULL default 0,
840 MODIFY itemlost tinyint(1) NOT NULL default 0,
841 MODIFY wthdrawn tinyint(1) NOT NULL default 0");
842 $dbh->do("UPDATE deleteditems SET notforloan = 0 WHERE notforloan IS NULL");
843 $dbh->do("UPDATE deleteditems SET damaged = 0 WHERE damaged IS NULL");
844 $dbh->do("UPDATE deleteditems SET itemlost = 0 WHERE itemlost IS NULL");
845 $dbh->do("UPDATE deleteditems SET wthdrawn = 0 WHERE wthdrawn IS NULL");
846 $dbh->do("ALTER TABLE deleteditems
847 MODIFY notforloan tinyint(1) NOT NULL default 0,
848 MODIFY damaged tinyint(1) NOT NULL default 0,
849 MODIFY itemlost tinyint(1) NOT NULL default 0,
850 MODIFY wthdrawn tinyint(1) NOT NULL default 0");
851 print "Upgrade to $DBversion done (disallow NULL in several item status columns)\n";
852 SetVersion ($DBversion);
855 $DBversion = "3.00.00.042";
856 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
857 $dbh->do("ALTER TABLE aqbooksellers CHANGE name name mediumtext NOT NULL");
858 print "Upgrade to $DBversion done (disallow NULL in aqbooksellers.name; part of fix for bug 1251)\n";
859 SetVersion ($DBversion);
862 $DBversion = "3.00.00.043";
863 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
864 $dbh->do("ALTER TABLE `currency` ADD `symbol` varchar(5) default NULL AFTER currency, ADD `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP AFTER symbol");
865 print "Upgrade to $DBversion done (currency table: add symbol and timestamp columns)\n";
866 SetVersion ($DBversion);
869 $DBversion = "3.00.00.044";
870 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
871 $dbh->do("ALTER TABLE deletedborrowers
872 ADD `altcontactfirstname` varchar(255) default NULL,
873 ADD `altcontactsurname` varchar(255) default NULL,
874 ADD `altcontactaddress1` varchar(255) default NULL,
875 ADD `altcontactaddress2` varchar(255) default NULL,
876 ADD `altcontactaddress3` varchar(255) default NULL,
877 ADD `altcontactzipcode` varchar(50) default NULL,
878 ADD `altcontactphone` varchar(50) default NULL
880 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES
881 ('OPACBaseURL',NULL,'Specify the Base URL of the OPAC, e.g., opac.mylibrary.com, the http:// will be added automatically by Koha.',NULL,'Free'),
882 ('language','en','Set the default language in the staff client.',NULL,'Languages'),
883 ('QueryAutoTruncate',1,'If ON, query truncation is enabled by default',NULL,'YesNo'),
884 ('QueryRemoveStopwords',0,'If ON, stopwords listed in the Administration area will be removed from queries',NULL,'YesNo')
886 print "Upgrade to $DBversion done (syncing deletedborrowers table with borrowers table)\n";
887 SetVersion ($DBversion);
890 #-- http://www.w3.org/International/articles/language-tags/
893 $DBversion = "3.00.00.045";
894 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
896 CREATE TABLE language_subtag_registry (
898 type varchar(25), -- language-script-region-variant-extension-privateuse
899 description varchar(25), -- only one of the possible descriptions for ease of reference, see language_descriptions for the complete list
901 KEY `subtag` (`subtag`)
902 ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
904 #-- TODO: add suppress_scripts
905 #-- this maps three letter codes defined in iso639.2 back to their
906 #-- two letter equivilents in rfc4646 (LOC maintains iso639+)
907 $dbh->do("CREATE TABLE language_rfc4646_to_iso639 (
908 rfc4646_subtag varchar(25),
909 iso639_2_code varchar(25),
910 KEY `rfc4646_subtag` (`rfc4646_subtag`)
911 ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
913 $dbh->do("CREATE TABLE language_descriptions (
917 description varchar(255),
919 ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
921 #-- bi-directional support, keyed by script subcode
922 $dbh->do("CREATE TABLE language_script_bidi (
923 rfc4646_subtag varchar(25), -- script subtag, Arab, Hebr, etc.
924 bidi varchar(3), -- rtl ltr
925 KEY `rfc4646_subtag` (`rfc4646_subtag`)
926 ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
928 #-- BIDI Stuff, Arabic and Hebrew
929 $dbh->do("INSERT INTO language_script_bidi(rfc4646_subtag,bidi)
930 VALUES( 'Arab', 'rtl')");
931 $dbh->do("INSERT INTO language_script_bidi(rfc4646_subtag,bidi)
932 VALUES( 'Hebr', 'rtl')");
934 #-- TODO: need to map language subtags to script subtags for detection
935 #-- of bidi when script is not specified (like ar, he)
936 $dbh->do("CREATE TABLE language_script_mapping (
937 language_subtag varchar(25),
938 script_subtag varchar(25),
939 KEY `language_subtag` (`language_subtag`)
940 ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
942 #-- Default mappings between script and language subcodes
943 $dbh->do("INSERT INTO language_script_mapping(language_subtag,script_subtag)
944 VALUES( 'ar', 'Arab')");
945 $dbh->do("INSERT INTO language_script_mapping(language_subtag,script_subtag)
946 VALUES( 'he', 'Hebr')");
948 print "Upgrade to $DBversion done (adding language subtag registry and basic BiDi support NOTE: You should import the subtag registry SQL)\n";
949 SetVersion ($DBversion);
952 $DBversion = "3.00.00.046";
953 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
954 $dbh->do("ALTER TABLE `subscription` CHANGE `numberlength` `numberlength` int(11) default '0' ,
955 CHANGE `weeklength` `weeklength` int(11) default '0'");
956 $dbh->do("CREATE TABLE `serialitems` (`serialid` int(11) NOT NULL, `itemnumber` int(11) NOT NULL, UNIQUE KEY `serialididx` (`serialid`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
957 $dbh->do("INSERT INTO `serialitems` SELECT `serialid`,`itemnumber` from serial where NOT ISNULL(itemnumber) && itemnumber <> '' && itemnumber NOT LIKE '%,%'");
958 print "Upgrade to $DBversion done (Add serialitems table to link serial issues to items. )\n";
959 SetVersion ($DBversion);
962 $DBversion = "3.00.00.047";
963 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
964 $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');");
965 print "Upgrade to $DBversion done ( Added OpacRenewalAllowed syspref )\n";
966 SetVersion ($DBversion);
969 $DBversion = "3.00.00.048";
970 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
971 $dbh->do("ALTER TABLE `items` ADD `more_subfields_xml` longtext default NULL AFTER `itype`");
972 print "Upgrade to $DBversion done (added items.more_subfields_xml)\n";
973 SetVersion ($DBversion);
976 $DBversion = "3.00.00.049";
977 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
978 $dbh->do("ALTER TABLE `z3950servers` ADD `encoding` text default NULL AFTER type ");
979 print "Upgrade to $DBversion done ( Added encoding field to z3950servers table )\n";
980 SetVersion ($DBversion);
983 $DBversion = "3.00.00.050";
984 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
985 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('OpacHighlightedWords','0','If Set, query matched terms are highlighted in OPAC',NULL,'YesNo');");
986 print "Upgrade to $DBversion done ( Added OpacHighlightedWords syspref )\n";
987 SetVersion ($DBversion);
990 $DBversion = "3.00.00.051";
991 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
992 $dbh->do("UPDATE systempreferences SET explanation = 'Define the current theme for the OPAC interface.' WHERE variable = 'opacthemes';");
993 print "Upgrade to $DBversion done ( Corrected opacthemes explanation. )\n";
994 SetVersion ($DBversion);
997 $DBversion = "3.00.00.052";
998 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
999 $dbh->do("ALTER TABLE `deleteditems` ADD `more_subfields_xml` LONGTEXT DEFAULT NULL AFTER `itype`");
1000 print "Upgrade to $DBversion done ( Adding missing column to deleteditems table. )\n";
1001 SetVersion ($DBversion);
1004 $DBversion = "3.00.00.053";
1005 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1006 $dbh->do("CREATE TABLE `printers_profile` (
1007 `prof_id` int(4) NOT NULL auto_increment,
1008 `printername` varchar(40) NOT NULL,
1009 `tmpl_id` int(4) NOT NULL,
1010 `paper_bin` varchar(20) NOT NULL,
1011 `offset_horz` float default NULL,
1012 `offset_vert` float default NULL,
1013 `creep_horz` float default NULL,
1014 `creep_vert` float default NULL,
1015 `unit` char(20) NOT NULL default 'POINT',
1016 PRIMARY KEY (`prof_id`),
1017 UNIQUE KEY `printername` (`printername`,`tmpl_id`,`paper_bin`),
1018 CONSTRAINT `printers_profile_pnfk_1` FOREIGN KEY (`tmpl_id`) REFERENCES `labels_templates` (`tmpl_id`) ON DELETE CASCADE ON UPDATE CASCADE
1019 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ");
1020 $dbh->do("CREATE TABLE `labels_profile` (
1021 `tmpl_id` int(4) NOT NULL,
1022 `prof_id` int(4) NOT NULL,
1023 UNIQUE KEY `tmpl_id` (`tmpl_id`),
1024 UNIQUE KEY `prof_id` (`prof_id`)
1025 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ");
1026 print "Upgrade to $DBversion done ( Printer Profile tables added )\n";
1027 SetVersion ($DBversion);
1030 $DBversion = "3.00.00.054";
1031 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1032 $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';");
1033 print "Upgrade to $DBversion done ( Added another barcode autogeneration sequence to barcode.pl. )\n";
1034 SetVersion ($DBversion);
1037 $DBversion = "3.00.00.055";
1038 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1039 $dbh->do("ALTER TABLE `zebraqueue` ADD KEY `zebraqueue_lookup` (`server`, `biblio_auth_number`, `operation`, `done`)");
1040 print "Upgrade to $DBversion done ( Added index on zebraqueue. )\n";
1041 SetVersion ($DBversion);
1043 $DBversion = "3.00.00.056";
1044 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1045 if (C4::Context->preference("marcflavour") eq 'UNIMARC') {
1046 $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) ");
1048 $dbh->do("INSERT INTO `marc_subfield_structure` (`tagfield`, `tagsubfield`, `liblibrarian`, `libopac`, `repeatable`, `mandatory`, `kohafield`, `tab`, `authorised_value` , `authtypecode`, `value_builder`, `isurl`, `hidden`, `frameworkcode`, `seealso`, `link`, `defaultvalue`) VALUES ('952', 'h', 'Serial Enumeration / chronology','Serial Enumeration / chronology', 0, 0, 'items.enumchron', 10, '', '', '', 0, 0, '', '', '', NULL) ");
1050 $dbh->do("ALTER TABLE `items` ADD `enumchron` VARCHAR(80) DEFAULT NULL;");
1051 print "Upgrade to $DBversion done ( Added item.enumchron column, and framework map to 952h )\n";
1052 SetVersion ($DBversion);
1055 $DBversion = "3.00.00.057";
1056 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1057 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('OAI-PMH','0','if ON, OAI-PMH server is enabled',NULL,'YesNo');");
1058 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('OAI-PMH:archiveID','KOHA-OAI-TEST','OAI-PMH archive identification',NULL,'Free');");
1059 $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');");
1060 $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');");
1061 $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');");
1062 SetVersion ($DBversion);
1065 $DBversion = "3.00.00.058";
1066 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1067 $dbh->do("ALTER TABLE `opac_news`
1068 CHANGE `lang` `lang` VARCHAR( 25 )
1070 COLLATE utf8_general_ci
1071 NOT NULL default ''");
1072 print "Upgrade to $DBversion done ( lang field in opac_news made longer )\n";
1073 SetVersion ($DBversion);
1076 $DBversion = "3.00.00.059";
1077 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1079 $dbh->do("CREATE TABLE IF NOT EXISTS `labels_templates` (
1080 `tmpl_id` int(4) NOT NULL auto_increment,
1081 `tmpl_code` char(100) default '',
1082 `tmpl_desc` char(100) default '',
1083 `page_width` float default '0',
1084 `page_height` float default '0',
1085 `label_width` float default '0',
1086 `label_height` float default '0',
1087 `topmargin` float default '0',
1088 `leftmargin` float default '0',
1089 `cols` int(2) default '0',
1090 `rows` int(2) default '0',
1091 `colgap` float default '0',
1092 `rowgap` float default '0',
1093 `active` int(1) default NULL,
1094 `units` char(20) default 'PX',
1095 `fontsize` int(4) NOT NULL default '3',
1096 PRIMARY KEY (`tmpl_id`)
1097 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;");
1098 $dbh->do("CREATE TABLE IF NOT EXISTS `printers_profile` (
1099 `prof_id` int(4) NOT NULL auto_increment,
1100 `printername` varchar(40) NOT NULL,
1101 `tmpl_id` int(4) NOT NULL,
1102 `paper_bin` varchar(20) NOT NULL,
1103 `offset_horz` float default NULL,
1104 `offset_vert` float default NULL,
1105 `creep_horz` float default NULL,
1106 `creep_vert` float default NULL,
1107 `unit` char(20) NOT NULL default 'POINT',
1108 PRIMARY KEY (`prof_id`),
1109 UNIQUE KEY `printername` (`printername`,`tmpl_id`,`paper_bin`),
1110 CONSTRAINT `printers_profile_pnfk_1` FOREIGN KEY (`tmpl_id`) REFERENCES `labels_templates` (`tmpl_id`) ON DELETE CASCADE ON UPDATE CASCADE
1111 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ");
1112 print "Upgrade to $DBversion done ( Added labels_templates table if it did not exist. )\n";
1113 SetVersion ($DBversion);
1116 $DBversion = "3.00.00.060";
1117 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1118 $dbh->do("CREATE TABLE IF NOT EXISTS `patronimage` (
1119 `cardnumber` varchar(16) NOT NULL,
1120 `mimetype` varchar(15) NOT NULL,
1121 `imagefile` mediumblob NOT NULL,
1122 PRIMARY KEY (`cardnumber`),
1123 CONSTRAINT `patronimage_fk1` FOREIGN KEY (`cardnumber`) REFERENCES `borrowers` (`cardnumber`) ON DELETE CASCADE ON UPDATE CASCADE
1124 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;");
1125 print "Upgrade to $DBversion done ( Added patronimage table. )\n";
1126 SetVersion ($DBversion);
1129 $DBversion = "3.00.00.061";
1130 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1131 $dbh->do("ALTER TABLE labels_templates ADD COLUMN font char(10) NOT NULL DEFAULT 'TR';");
1132 print "Upgrade to $DBversion done ( Added font column to labels_templates )\n";
1133 SetVersion ($DBversion);
1136 $DBversion = "3.00.00.062";
1137 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1138 $dbh->do("CREATE TABLE `old_issues` (
1139 `borrowernumber` int(11) default NULL,
1140 `itemnumber` int(11) default NULL,
1141 `date_due` date default NULL,
1142 `branchcode` varchar(10) default NULL,
1143 `issuingbranch` varchar(18) default NULL,
1144 `returndate` date default NULL,
1145 `lastreneweddate` date default NULL,
1146 `return` varchar(4) default NULL,
1147 `renewals` tinyint(4) default NULL,
1148 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1149 `issuedate` date default NULL,
1150 KEY `old_issuesborridx` (`borrowernumber`),
1151 KEY `old_issuesitemidx` (`itemnumber`),
1152 KEY `old_bordate` (`borrowernumber`,`timestamp`),
1153 CONSTRAINT `old_issues_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`)
1154 ON DELETE SET NULL ON UPDATE SET NULL,
1155 CONSTRAINT `old_issues_ibfk_2` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`)
1156 ON DELETE SET NULL ON UPDATE SET NULL
1157 ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
1158 $dbh->do("CREATE TABLE `old_reserves` (
1159 `borrowernumber` int(11) default NULL,
1160 `reservedate` date default NULL,
1161 `biblionumber` int(11) default NULL,
1162 `constrainttype` varchar(1) default NULL,
1163 `branchcode` varchar(10) default NULL,
1164 `notificationdate` date default NULL,
1165 `reminderdate` date default NULL,
1166 `cancellationdate` date default NULL,
1167 `reservenotes` mediumtext,
1168 `priority` smallint(6) default NULL,
1169 `found` varchar(1) default NULL,
1170 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1171 `itemnumber` int(11) default NULL,
1172 `waitingdate` date default NULL,
1173 KEY `old_reserves_borrowernumber` (`borrowernumber`),
1174 KEY `old_reserves_biblionumber` (`biblionumber`),
1175 KEY `old_reserves_itemnumber` (`itemnumber`),
1176 KEY `old_reserves_branchcode` (`branchcode`),
1177 CONSTRAINT `old_reserves_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`)
1178 ON DELETE SET NULL ON UPDATE SET NULL,
1179 CONSTRAINT `old_reserves_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`)
1180 ON DELETE SET NULL ON UPDATE SET NULL,
1181 CONSTRAINT `old_reserves_ibfk_3` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`)
1182 ON DELETE SET NULL ON UPDATE SET NULL
1183 ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
1185 # move closed transactions to old_* tables
1186 $dbh->do("INSERT INTO old_issues SELECT * FROM issues WHERE returndate IS NOT NULL");
1187 $dbh->do("DELETE FROM issues WHERE returndate IS NOT NULL");
1188 $dbh->do("INSERT INTO old_reserves SELECT * FROM reserves WHERE cancellationdate IS NOT NULL OR found = 'F'");
1189 $dbh->do("DELETE FROM reserves WHERE cancellationdate IS NOT NULL OR found = 'F'");
1191 print "Upgrade to $DBversion done ( Added old_issues and old_reserves tables )\n";
1192 SetVersion ($DBversion);
1195 $DBversion = "3.00.00.063";
1196 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1197 $dbh->do("ALTER TABLE deleteditems
1198 CHANGE COLUMN booksellerid booksellerid MEDIUMTEXT DEFAULT NULL,
1199 ADD COLUMN enumchron VARCHAR(80) DEFAULT NULL AFTER more_subfields_xml,
1200 ADD COLUMN copynumber SMALLINT(6) DEFAULT NULL AFTER enumchron;");
1201 $dbh->do("ALTER TABLE items
1202 CHANGE COLUMN booksellerid booksellerid MEDIUMTEXT,
1203 ADD COLUMN copynumber SMALLINT(6) DEFAULT NULL AFTER enumchron;");
1204 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";
1205 SetVersion ($DBversion);
1208 $DBversion = "3.00.00.064";
1209 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1210 $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');");
1211 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('AWSAccessKeyID','','See: http://aws.amazon.com','','free');");
1212 $dbh->do("DELETE FROM `systempreferences` WHERE variable='AmazonDevKey';");
1213 $dbh->do("DELETE FROM `systempreferences` WHERE variable='XISBNAmazonSimilarItems';");
1214 $dbh->do("DELETE FROM `systempreferences` WHERE variable='OPACXISBNAmazonSimilarItems';");
1215 print "Upgrade to $DBversion done (IMPORTANT: Upgrading to Amazon.com Associates Web Service 4.0 ) \n";
1216 SetVersion ($DBversion);
1219 $DBversion = "3.00.00.065";
1220 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1221 $dbh->do("CREATE TABLE `patroncards` (
1222 `cardid` int(11) NOT NULL auto_increment,
1223 `batch_id` varchar(10) NOT NULL default '1',
1224 `borrowernumber` int(11) NOT NULL,
1225 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1226 PRIMARY KEY (`cardid`),
1227 KEY `patroncards_ibfk_1` (`borrowernumber`),
1228 CONSTRAINT `patroncards_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE
1229 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;");
1230 print "Upgrade to $DBversion done (Adding patroncards table for patroncards generation feature. ) \n";
1231 SetVersion ($DBversion);
1234 $DBversion = "3.00.00.066";
1235 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1236 $dbh->do("ALTER TABLE `virtualshelfcontents` MODIFY `dateadded` timestamp NOT NULL
1237 DEFAULT CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP;
1239 print "Upgrade to $DBversion done (fix for bug 1873: virtualshelfcontents dateadded column empty. ) \n";
1240 SetVersion ($DBversion);
1243 $DBversion = "3.00.00.067";
1244 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1245 $dbh->do("UPDATE systempreferences SET explanation = 'Enable patron images for the Staff Client', type = 'YesNo' WHERE variable = 'patronimages'");
1246 print "Upgrade to $DBversion done (Updating patronimages syspref to reflect current kohastructure.sql. ) \n";
1247 SetVersion ($DBversion);
1250 $DBversion = "3.00.00.068";
1251 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1252 $dbh->do("CREATE TABLE `permissions` (
1253 `module_bit` int(11) NOT NULL DEFAULT 0,
1254 `code` varchar(30) DEFAULT NULL,
1255 `description` varchar(255) DEFAULT NULL,
1256 PRIMARY KEY (`module_bit`, `code`),
1257 CONSTRAINT `permissions_ibfk_1` FOREIGN KEY (`module_bit`) REFERENCES `userflags` (`bit`)
1258 ON DELETE CASCADE ON UPDATE CASCADE
1259 ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
1260 $dbh->do("CREATE TABLE `user_permissions` (
1261 `borrowernumber` int(11) NOT NULL DEFAULT 0,
1262 `module_bit` int(11) NOT NULL DEFAULT 0,
1263 `code` varchar(30) DEFAULT NULL,
1264 CONSTRAINT `user_permissions_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`)
1265 ON DELETE CASCADE ON UPDATE CASCADE,
1266 CONSTRAINT `user_permissions_ibfk_2` FOREIGN KEY (`module_bit`, `code`)
1267 REFERENCES `permissions` (`module_bit`, `code`)
1268 ON DELETE CASCADE ON UPDATE CASCADE
1269 ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
1271 $dbh->do("INSERT INTO permissions (module_bit, code, description) VALUES
1272 (13, 'edit_news', 'Write news for the OPAC and staff interfaces'),
1273 (13, 'label_creator', 'Create printable labels and barcodes from catalog and patron data'),
1274 (13, 'edit_calendar', 'Define days when the library is closed'),
1275 (13, 'moderate_comments', 'Moderate patron comments'),
1276 (13, 'edit_notices', 'Define notices'),
1277 (13, 'edit_notice_status_triggers', 'Set notice/status triggers for overdue items'),
1278 (13, 'view_system_logs', 'Browse the system logs'),
1279 (13, 'inventory', 'Perform inventory (stocktaking) of your catalogue'),
1280 (13, 'stage_marc_import', 'Stage MARC records into the reservoir'),
1281 (13, 'manage_staged_marc', 'Managed staged MARC records, including completing and reversing imports'),
1282 (13, 'export_catalog', 'Export bibliographic and holdings data'),
1283 (13, 'import_patrons', 'Import patron data'),
1284 (13, 'delete_anonymize_patrons', 'Delete old borrowers and anonymize circulation history (deletes borrower reading history)'),
1285 (13, 'batch_upload_patron_images', 'Upload patron images in batch or one at a time'),
1286 (13, 'schedule_tasks', 'Schedule tasks to run')");
1288 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('GranularPermissions','0','Use detailed staff user permissions',NULL,'YesNo')");
1290 print "Upgrade to $DBversion done (adding permissions and user_permissions tables and GranularPermissions syspref) \n";
1291 SetVersion ($DBversion);
1293 $DBversion = "3.00.00.069";
1294 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1295 $dbh->do("ALTER TABLE labels_conf CHANGE COLUMN class classification int(1) DEFAULT NULL;");
1296 print "Upgrade to $DBversion done ( Correcting columname in labels_conf )\n";
1297 SetVersion ($DBversion);
1300 $DBversion = "3.00.00.070";
1301 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1302 $sth = $dbh->prepare("SELECT value FROM systempreferences WHERE variable='yuipath'");
1304 my ($value) = $sth->fetchrow;
1305 $value =~ s/2.3.1/2.5.1/;
1306 $dbh->do("UPDATE systempreferences SET value='$value' WHERE variable='yuipath';");
1307 print "Update yuipath syspref to 2.5.1 if necessary\n";
1308 SetVersion ($DBversion);
1311 $DBversion = "3.00.00.071";
1312 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1313 $dbh->do(" ALTER TABLE `subscription` ADD `serialsadditems` TINYINT( 1 ) NOT NULL DEFAULT '0';");
1314 # fill the new field with the previous systempreference value, then drop the syspref
1315 my $sth = $dbh->prepare("SELECT value FROM systempreferences WHERE variable='serialsadditems'");
1317 my ($serialsadditems) = $sth->fetchrow();
1318 $dbh->do("UPDATE subscription SET serialsadditems=$serialsadditems");
1319 $dbh->do("DELETE FROM systempreferences WHERE variable='serialsadditems'");
1320 print "Upgrade to $DBversion done ( moving serialsadditems from syspref to subscription )\n";
1321 SetVersion ($DBversion);
1324 $DBversion = "3.00.00.072";
1325 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1326 $dbh->do("ALTER TABLE labels_conf ADD COLUMN formatstring VARCHAR(64) DEFAULT NULL AFTER printingtype");
1327 print "Upgrade to $DBversion done ( Adding format string to labels generator. )\n";
1328 SetVersion ($DBversion);
1331 $DBversion = "3.00.00.073";
1332 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1333 $dbh->do("DROP TABLE IF EXISTS `tags_all`;");
1335 CREATE TABLE `tags_all` (
1336 `tag_id` int(11) NOT NULL auto_increment,
1337 `borrowernumber` int(11) NOT NULL,
1338 `biblionumber` int(11) NOT NULL,
1339 `term` varchar(255) NOT NULL,
1340 `language` int(4) default NULL,
1341 `date_created` datetime NOT NULL,
1342 PRIMARY KEY (`tag_id`),
1343 KEY `tags_borrowers_fk_1` (`borrowernumber`),
1344 KEY `tags_biblionumber_fk_1` (`biblionumber`),
1345 CONSTRAINT `tags_borrowers_fk_1` FOREIGN KEY (`borrowernumber`)
1346 REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1347 CONSTRAINT `tags_biblionumber_fk_1` FOREIGN KEY (`biblionumber`)
1348 REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE
1349 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1351 $dbh->do("DROP TABLE IF EXISTS `tags_approval`;");
1353 CREATE TABLE `tags_approval` (
1354 `term` varchar(255) NOT NULL,
1355 `approved` int(1) NOT NULL default '0',
1356 `date_approved` datetime default NULL,
1357 `approved_by` int(11) default NULL,
1358 `weight_total` int(9) NOT NULL default '1',
1359 PRIMARY KEY (`term`),
1360 KEY `tags_approval_borrowers_fk_1` (`approved_by`),
1361 CONSTRAINT `tags_approval_borrowers_fk_1` FOREIGN KEY (`approved_by`)
1362 REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE
1363 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1365 $dbh->do("DROP TABLE IF EXISTS `tags_index`;");
1367 CREATE TABLE `tags_index` (
1368 `term` varchar(255) NOT NULL,
1369 `biblionumber` int(11) NOT NULL,
1370 `weight` int(9) NOT NULL default '1',
1371 PRIMARY KEY (`term`,`biblionumber`),
1372 KEY `tags_index_biblionumber_fk_1` (`biblionumber`),
1373 CONSTRAINT `tags_index_term_fk_1` FOREIGN KEY (`term`)
1374 REFERENCES `tags_approval` (`term`) ON DELETE CASCADE ON UPDATE CASCADE,
1375 CONSTRAINT `tags_index_biblionumber_fk_1` FOREIGN KEY (`biblionumber`)
1376 REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE
1377 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1380 INSERT INTO `systempreferences` VALUES
1381 ('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=',''),
1382 ('BakerTaylorEnabled','0','','Enable or disable all Baker & Taylor features.','YesNo'),
1383 ('BakerTaylorPassword','','','Baker & Taylor Password for Content Cafe (external content)','Textarea'),
1384 ('BakerTaylorUsername','','','Baker & Taylor Username for Content Cafe (external content)','Textarea'),
1385 ('TagsEnabled','1','','Enables or disables all tagging features. This is the main switch for tags.','YesNo'),
1386 ('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.',''),
1387 ('TagsInputOnDetail','1','','Allow users to input tags from the detail page.', 'YesNo'),
1388 ('TagsInputOnList', '0','','Allow users to input tags from the search results list.', 'YesNo'),
1389 ('TagsModeration', NULL,'','Require tags from patrons to be approved before becoming visible.','YesNo'),
1390 ('TagsShowOnDetail','10','','Number of tags to display on detail page. 0 is off.', 'Integer'),
1391 ('TagsShowOnList', '6','','Number of tags to display on search results list. 0 is off.','Integer')
1393 print "Upgrade to $DBversion done (Baker/Taylor,Tags: sysprefs and tables (tags_all, tags_index, tags_approval)) \n";
1394 SetVersion ($DBversion);
1397 $DBversion = "3.00.00.074";
1398 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1399 $dbh->do( q(update itemtypes set imageurl = concat( 'npl/', imageurl )
1400 where imageurl not like 'http%'
1401 and imageurl is not NULL
1402 and imageurl != '') );
1403 print "Upgrade to $DBversion done (updating imagetype.imageurls to reflect new icon locations.)\n";
1404 SetVersion ($DBversion);
1407 $DBversion = "3.00.00.075";
1408 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1409 $dbh->do( q(alter table authorised_values add imageurl varchar(200) default NULL) );
1410 print "Upgrade to $DBversion done (adding imageurl field to authorised_values table)\n";
1411 SetVersion ($DBversion);
1414 $DBversion = "3.00.00.076";
1415 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1416 $dbh->do("ALTER TABLE import_batches
1417 ADD COLUMN nomatch_action enum('create_new', 'ignore') NOT NULL default 'create_new' AFTER overlay_action");
1418 $dbh->do("ALTER TABLE import_batches
1419 ADD COLUMN item_action enum('always_add', 'add_only_for_matches', 'add_only_for_new', 'ignore')
1420 NOT NULL default 'always_add' AFTER nomatch_action");
1421 $dbh->do("ALTER TABLE import_batches
1422 MODIFY overlay_action enum('replace', 'create_new', 'use_template', 'ignore')
1423 NOT NULL default 'create_new'");
1424 $dbh->do("ALTER TABLE import_records
1425 MODIFY status enum('error', 'staged', 'imported', 'reverted', 'items_reverted',
1426 'ignored') NOT NULL default 'staged'");
1427 $dbh->do("ALTER TABLE import_items
1428 MODIFY status enum('error', 'staged', 'imported', 'reverted', 'ignored') NOT NULL default 'staged'");
1430 print "Upgrade to $DBversion done (changes to import_batches and import_records)\n";
1431 SetVersion ($DBversion);
1434 $DBversion = "3.00.00.077";
1435 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1436 # drop these tables only if they exist and none of them are empty
1437 # these tables are not defined in the packaged 2.2.9, but since it is believed
1438 # that at least one library may be using them in a post-2.2.9 but pre-3.0 Koha,
1439 # some care is taken.
1440 my ($print_error) = $dbh->{PrintError};
1441 $dbh->{PrintError} = 0;
1442 my ($raise_error) = $dbh->{RaiseError};
1443 $dbh->{RaiseError} = 1;
1447 eval { $count = $dbh->do("SELECT 1 FROM categorytable"); };
1451 eval { $count = $dbh->do("SELECT 1 FROM mediatypetable"); };
1455 eval { $count = $dbh->do("SELECT 1 FROM subcategorytable"); };
1461 $dbh->do("DROP TABLE IF EXISTS `categorytable`");
1462 $dbh->do("DROP TABLE IF EXISTS `mediatypetable`");
1463 $dbh->do("DROP TABLE IF EXISTS `subcategorytable`");
1466 $dbh->{PrintError} = $print_error;
1467 $dbh->{RaiseError} = $raise_error;
1468 print "Upgrade to $DBversion done (drop categorytable, subcategorytable, and mediatypetable)\n";
1469 SetVersion ($DBversion);
1472 $DBversion = "3.00.00.078";
1473 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1474 my ($print_error) = $dbh->{PrintError};
1475 $dbh->{PrintError} = 0;
1477 unless ($dbh->do("SELECT 1 FROM browser")) {
1478 $dbh->{PrintError} = $print_error;
1479 $dbh->do("CREATE TABLE `browser` (
1480 `level` int(11) NOT NULL,
1481 `classification` varchar(20) NOT NULL,
1482 `description` varchar(255) NOT NULL,
1483 `number` bigint(20) NOT NULL,
1484 `endnode` tinyint(4) NOT NULL
1485 ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
1487 $dbh->{PrintError} = $print_error;
1488 print "Upgrade to $DBversion done (add browser table if not already present)\n";
1489 SetVersion ($DBversion);
1492 $DBversion = "3.00.00.079";
1493 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1494 my ($print_error) = $dbh->{PrintError};
1495 $dbh->{PrintError} = 0;
1497 $dbh->do("INSERT INTO `systempreferences` (variable, value,options,type, explanation)VALUES
1498 ('AddPatronLists','categorycode','categorycode|category_type','Choice','Allow user to choose what list to pick up from when adding patrons')");
1499 print "Upgrade to $DBversion done (add browser table if not already present)\n";
1500 SetVersion ($DBversion);
1505 $DBversion = "3.00.00.080";
1506 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1507 $dbh->do("ALTER TABLE subscription CHANGE monthlength monthlength int(11) default '0'");
1508 $dbh->do("ALTER TABLE deleteditems MODIFY marc LONGBLOB AFTER copynumber");
1509 $dbh->do("ALTER TABLE aqbooksellers CHANGE name name mediumtext NOT NULL");
1510 print "Upgrade to $DBversion done (catch up on DB schema changes since alpha and beta)\n";
1511 SetVersion ($DBversion);
1514 $DBversion = "3.00.00.081";
1515 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1516 $dbh->do("CREATE TABLE `borrower_attribute_types` (
1517 `code` varchar(10) NOT NULL,
1518 `description` varchar(255) NOT NULL,
1519 `repeatable` tinyint(1) NOT NULL default 0,
1520 `unique_id` tinyint(1) NOT NULL default 0,
1521 `opac_display` tinyint(1) NOT NULL default 0,
1522 `password_allowed` tinyint(1) NOT NULL default 0,
1523 `staff_searchable` tinyint(1) NOT NULL default 0,
1524 `authorised_value_category` varchar(10) default NULL,
1525 PRIMARY KEY (`code`)
1526 ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
1527 $dbh->do("CREATE TABLE `borrower_attributes` (
1528 `borrowernumber` int(11) NOT NULL,
1529 `code` varchar(10) NOT NULL,
1530 `attribute` varchar(30) default NULL,
1531 `password` varchar(30) default NULL,
1532 KEY `borrowernumber` (`borrowernumber`),
1533 KEY `code_attribute` (`code`, `attribute`),
1534 CONSTRAINT `borrower_attributes_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`)
1535 ON DELETE CASCADE ON UPDATE CASCADE,
1536 CONSTRAINT `borrower_attributes_ibfk_2` FOREIGN KEY (`code`) REFERENCES `borrower_attribute_types` (`code`)
1537 ON DELETE CASCADE ON UPDATE CASCADE
1538 ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
1539 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('ExtendedPatronAttributes','0','Use extended patron IDs and attributes',NULL,'YesNo')");
1540 print "Upgrade to $DBversion done (added borrower_attributes and borrower_attribute_types)\n";
1541 SetVersion ($DBversion);
1544 $DBversion = "3.00.00.082";
1545 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1546 $dbh->do( q(alter table accountlines add column lastincrement decimal(28,6) default NULL) );
1547 print "Upgrade to $DBversion done (adding lastincrement column to accountlines table)\n";
1548 SetVersion ($DBversion);
1551 $DBversion = "3.00.00.083";
1552 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1553 $dbh->do( qq(UPDATE systempreferences SET value='local' where variable='yuipath' and value like "%/intranet-tmpl/prog/%"));
1554 print "Upgrade to $DBversion done (Changing yuipath behaviour in managing a local value)\n";
1555 SetVersion ($DBversion);
1557 $DBversion = "3.00.00.084";
1558 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1559 $dbh->do("INSERT INTO systempreferences (variable,value,explanation,options,type) VALUES('RenewSerialAddsSuggestion','0','if ON, adds a new suggestion at serial subscription renewal',NULL,'YesNo')");
1560 $dbh->do("INSERT INTO systempreferences (variable,value,explanation,options,type) VALUES('GoogleJackets','0','if ON, displays jacket covers from Google Books API',NULL,'YesNo')");
1561 print "Upgrade to $DBversion done (add new sysprefs)\n";
1562 SetVersion ($DBversion);
1565 $DBversion = "3.00.00.085";
1566 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1567 if (C4::Context->preference("marcflavour") eq 'MARC21') {
1568 $dbh->do("UPDATE marc_subfield_structure SET tab = 0 WHERE tab = 9 AND tagfield = '037'");
1569 $dbh->do("UPDATE marc_subfield_structure SET tab = 1 WHERE tab = 6 AND tagfield in ('100', '110', '111', '130')");
1570 $dbh->do("UPDATE marc_subfield_structure SET tab = 2 WHERE tab = 6 AND tagfield in ('240', '243')");
1571 $dbh->do("UPDATE marc_subfield_structure SET tab = 4 WHERE tab = 6 AND tagfield in ('400', '410', '411', '440')");
1572 $dbh->do("UPDATE marc_subfield_structure SET tab = 5 WHERE tab = 9 AND tagfield = '584'");
1573 $dbh->do("UPDATE marc_subfield_structure SET tab = 7 WHERE tab = -6 AND tagfield = '760'");
1575 print "Upgrade to $DBversion done (move editing tab of various MARC21 subfields)\n";
1576 SetVersion ($DBversion);
1579 $DBversion = "3.00.00.086";
1580 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1582 "CREATE TABLE `tmp_holdsqueue` (
1583 `biblionumber` int(11) default NULL,
1584 `itemnumber` int(11) default NULL,
1585 `barcode` varchar(20) default NULL,
1586 `surname` mediumtext NOT NULL,
1589 `borrowernumber` int(11) NOT NULL,
1590 `cardnumber` varchar(16) default NULL,
1591 `reservedate` date default NULL,
1593 `itemcallnumber` varchar(30) default NULL,
1594 `holdingbranch` varchar(10) default NULL,
1595 `pickbranch` varchar(10) default NULL,
1597 ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
1599 $dbh->do("INSERT INTO systempreferences (variable,value,explanation,options,type) VALUES('RandomizeHoldsQueueWeight','0','if ON, the holds queue in circulation will be randomized, either based on all location codes, or by the location codes specified in StaticHoldsQueueWeight',NULL,'YesNo')");
1600 $dbh->do("INSERT INTO systempreferences (variable,value,explanation,options,type) VALUES('StaticHoldsQueueWeight','0','Specify a list of library location codes separated by commas -- the list of codes will be traversed and weighted with first values given higher weight for holds fulfillment -- alternatively, if RandomizeHoldsQueueWeight is set, the list will be randomly selective',NULL,'TextArea')");
1602 print "Upgrade to $DBversion done (Table structure for table `tmp_holdsqueue`)\n";
1603 SetVersion ($DBversion);
1606 $DBversion = "3.00.00.087";
1607 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1608 $dbh->do("INSERT INTO `systempreferences` VALUES ('AutoEmailOpacUser','0','','Sends notification emails containing new account details to patrons - when account is created.','YesNo')" );
1609 $dbh->do("INSERT INTO `systempreferences` VALUES ('AutoEmailPrimaryAddress','OFF','email|emailpro|B_email|cardnumber|OFF','Defines the default email address where Account Details emails are sent.','Choice')");
1610 print "Upgrade to $DBversion done (added 2 new 'AutoEmailOpacUser' sysprefs)\n";
1611 SetVersion ($DBversion);
1615 $DBversion = "3.00.00.088";
1616 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1617 $dbh->do("INSERT INTO `systempreferences` (variable,value,options,explanation,type) VALUES ('OPACShelfBrowser','1','','Enable/disable Shelf Browser on item details page','YesNo')");
1618 $dbh->do("INSERT INTO `systempreferences` (variable,value,options,explanation,type) VALUES ('OPACItemHolds','1','Allow OPAC users to place hold on specific items. If OFF, users can only request next available copy.','','YesNo')");
1619 $dbh->do("INSERT INTO `systempreferences` (variable,value,options,explanation,type) VALUES ('XSLTDetailsDisplay','0','','Enable XSL stylesheet control over details page display on OPAC WARNING: MARC21 Only','YesNo')");
1620 $dbh->do("INSERT INTO `systempreferences` (variable,value,options,explanation,type) VALUES ('XSLTResultsDisplay','0','','Enable XSL stylesheet control over results page display on OPAC WARNING: MARC21 Only','YesNo')");
1621 print "Upgrade to $DBversion done (added 2 new 'AutoEmailOpacUser' sysprefs)\n";
1622 SetVersion ($DBversion);
1626 =item DropAllForeignKeys($table)
1628 Drop all foreign keys of the table $table
1632 sub DropAllForeignKeys {
1634 # get the table description
1635 my $sth = $dbh->prepare("SHOW CREATE TABLE $table");
1637 my $vsc_structure = $sth->fetchrow;
1638 # split on CONSTRAINT keyword
1639 my @fks = split /CONSTRAINT /,$vsc_structure;
1642 # isolate what is before FOREIGN KEY, if there is something, it's a foreign key to drop
1643 $_ = /(.*) FOREIGN KEY.*/;
1646 # we have found 1 foreign, drop it
1647 $dbh->do("ALTER TABLE $table DROP FOREIGN KEY $id");
1654 =item TransformToNum
1656 Transform the Koha version from a 4 parts string
1657 to a number, with just 1 .
1661 sub TransformToNum {
1662 my $version = shift;
1663 # remove the 3 last . to have a Perl number
1664 $version =~ s/(.*\..*)\.(.*)\.(.*)/$1$2$3/;
1670 set the DBversion in the systempreferences
1675 my $kohaversion = TransformToNum(shift);
1676 if (C4::Context->preference('Version')) {
1677 my $finish=$dbh->prepare("UPDATE systempreferences SET value=? WHERE variable='Version'");
1678 $finish->execute($kohaversion);
1680 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')");
1681 $finish->execute($kohaversion);
projects / koha.git / blob