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.
23 use MARC::File::XML ( BinaryEncoding => 'utf8' );
25 # FIXME - The user might be installing a new database, so can't rely
26 # on /etc/koha.conf anyway.
33 %existingtables, # tables already in database
37 $type, $null, $key, $default, $extra,
38 $prefitem, # preference item in systempreferences table
45 my $dbh = C4::Context->dbh;
46 $|=1; # flushes output
49 Deal with virtualshelves
52 my $DBversion = "3.00.00.001";
53 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
54 # update virtualshelves table to
56 $dbh->do("ALTER TABLE `bookshelf` RENAME `virtualshelves`");
57 $dbh->do("ALTER TABLE `shelfcontents` RENAME `virtualshelfcontents`");
58 $dbh->do("ALTER TABLE `virtualshelfcontents` ADD `biblionumber` INT( 11 ) NOT NULL");
59 $dbh->do("UPDATE `virtualshelfcontents` SET biblionumber=(SELECT biblionumber FROM items WHERE items.itemnumber=virtualshelfcontents.itemnumber)");
60 # drop all foreign keys : otherwise, we can't drop itemnumber field.
61 DropAllForeignKeys('virtualshelfcontents');
62 # create the new foreign keys (on biblionumber)
63 $dbh->do("ALTER TABLE `virtualshelfcontents` ADD FOREIGN KEY biblionumber_fk (biblionumber) REFERENCES biblio (biblionumber) ON UPDATE CASCADE ON DELETE CASCADE");
64 # re-create the foreign key on virtualshelf
65 $dbh->do("ALTER TABLE `virtualshelfcontents` ADD FOREIGN KEY shelfnumber_fk (shelfnumber) REFERENCES virtualshelves (shelfnumber) ON UPDATE CASCADE ON DELETE CASCADE");
66 # now we can drop the itemnumber column
67 $dbh->do("ALTER TABLE `virtualshelfcontents` DROP `itemnumber`");
68 print "Upgrade to $DBversion done (virtualshelves)\n";
69 SetVersion ($DBversion);
73 $DBversion = "3.00.00.002";
74 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
75 $dbh->do("DROP TABLE sessions");
76 $dbh->do("CREATE TABLE `sessions` (
77 `id` char(32) NOT NULL,
78 `a_session` text NOT NULL,
79 UNIQUE KEY `id` (`id`)
80 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;");
81 print "Upgrade to $DBversion done (sessions uses CGI::session, new table structure for sessions)\n";
82 SetVersion ($DBversion);
86 $DBversion = "3.00.00.003";
87 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
88 if (C4::Context->preference("opaclanguage") eq "fr") {
89 $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')");
91 $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')");
93 print "Upgrade to $DBversion done (adding ReservesNeedReturns systempref, in circulation)\n";
94 SetVersion ($DBversion);
98 $DBversion = "3.00.00.004";
99 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
100 $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')");
101 print "Upgrade to $DBversion done (adding DebugLevel systempref, in 'Admin' tab)\n";
102 SetVersion ($DBversion);
105 $DBversion = "3.00.00.005";
106 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
107 $dbh->do("CREATE TABLE `tags` (
108 `entry` varchar(255) NOT NULL default '',
109 `weight` bigint(20) NOT NULL default 0,
110 PRIMARY KEY (`entry`)
111 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
113 $dbh->do("CREATE TABLE `nozebra` (
114 `server` varchar(20) NOT NULL,
115 `indexname` varchar(40) NOT NULL,
116 `value` varchar(250) NOT NULL,
117 `biblionumbers` longtext NOT NULL,
118 KEY `indexname` (`server`,`indexname`),
119 KEY `value` (`server`,`value`))
120 ENGINE=InnoDB DEFAULT CHARSET=utf8;
122 print "Upgrade to $DBversion done (adding tags and nozebra tables )\n";
123 SetVersion ($DBversion);
126 $DBversion = "3.00.00.006";
127 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
128 $dbh->do("UPDATE issues SET issuedate=timestamp WHERE issuedate='0000-00-00'");
129 print "Upgrade to $DBversion done (filled issues.issuedate with timestamp)\n";
130 SetVersion ($DBversion);
133 $DBversion = "3.00.00.007";
134 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
135 $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')");
136 print "Upgrade to $DBversion done (set SessionStorage variable)\n";
137 SetVersion ($DBversion);
140 $DBversion = "3.00.00.008";
141 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
142 $dbh->do("ALTER TABLE `biblio` ADD `datecreated` DATE NOT NULL AFTER `timestamp` ;");
143 $dbh->do("UPDATE biblio SET datecreated=timestamp");
144 print "Upgrade to $DBversion done (biblio creation date)\n";
145 SetVersion ($DBversion);
148 $DBversion = "3.00.00.009";
149 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
151 # Create backups of call number columns
152 # in case default migration needs to be customized
154 # UPGRADE NOTE: temp_upg_biblioitems_call_num should be dropped
155 # after call numbers have been transformed to the new structure
157 # Not bothering to do the same with deletedbiblioitems -- assume
158 # default is good enough.
159 $dbh->do("CREATE TABLE `temp_upg_biblioitems_call_num` AS
160 SELECT `biblioitemnumber`, `biblionumber`,
161 `classification`, `dewey`, `subclass`,
163 FROM `biblioitems`");
165 # biblioitems changes
166 $dbh->do("ALTER TABLE `biblioitems` CHANGE COLUMN `volumeddesc` `volumedesc` TEXT,
167 ADD `cn_source` VARCHAR(10) DEFAULT NULL AFTER `ccode`,
168 ADD `cn_class` VARCHAR(30) DEFAULT NULL AFTER `cn_source`,
169 ADD `cn_item` VARCHAR(10) DEFAULT NULL AFTER `cn_class`,
170 ADD `cn_suffix` VARCHAR(10) DEFAULT NULL AFTER `cn_item`,
171 ADD `cn_sort` VARCHAR(30) DEFAULT NULL AFTER `cn_suffix`,
172 ADD `totalissues` INT(10) AFTER `cn_sort`");
174 # default mapping of call number columns:
175 # cn_class = concatentation of classification + dewey,
176 # trimmed to fit -- assumes that most users do not
177 # populate both classification and dewey in a single record
179 # cn_source = left null
182 # After upgrade, cn_sort will have to be set based on whatever
183 # default call number scheme user sets as a preference. Misc
184 # script will be added at some point to do that.
186 $dbh->do("UPDATE `biblioitems`
187 SET cn_class = SUBSTR(TRIM(CONCAT_WS(' ', `classification`, `dewey`)), 1, 30),
192 # Now drop the old call number columns
193 $dbh->do("ALTER TABLE `biblioitems` DROP COLUMN `classification`,
195 DROP COLUMN `subclass`,
196 DROP COLUMN `lcsort`,
197 DROP COLUMN `ccode`");
199 # deletedbiblio changes
200 $dbh->do("ALTER TABLE `deletedbiblio` ALTER COLUMN `frameworkcode` SET DEFAULT '',
202 ADD `datecreated` DATE NOT NULL AFTER `timestamp`");
203 $dbh->do("UPDATE deletedbiblio SET datecreated = timestamp");
205 # deletedbiblioitems changes
206 $dbh->do("ALTER TABLE `deletedbiblioitems`
207 MODIFY `publicationyear` TEXT,
208 CHANGE `volumeddesc` `volumedesc` TEXT,
209 MODIFY `collectiontitle` MEDIUMTEXT DEFAULT NULL AFTER `volumedesc`,
210 MODIFY `collectionissn` TEXT DEFAULT NULL AFTER `collectiontitle`,
211 MODIFY `collectionvolume` MEDIUMTEXT DEFAULT NULL AFTER `collectionissn`,
212 MODIFY `editionstatement` TEXT DEFAULT NULL AFTER `collectionvolume`,
213 MODIFY `editionresponsibility` TEXT DEFAULT NULL AFTER `editionstatement`,
214 MODIFY `place` VARCHAR(255) DEFAULT NULL AFTER `size`,
216 ADD `cn_source` VARCHAR(10) DEFAULT NULL AFTER `url`,
217 ADD `cn_class` VARCHAR(30) DEFAULT NULL AFTER `cn_source`,
218 ADD `cn_item` VARCHAR(10) DEFAULT NULL AFTER `cn_class`,
219 ADD `cn_suffix` VARCHAR(10) DEFAULT NULL AFTER `cn_item`,
220 ADD `cn_sort` VARCHAR(30) DEFAULT NULL AFTER `cn_suffix`,
221 ADD `totalissues` INT(10) AFTER `cn_sort`,
222 ADD KEY `isbn` (`isbn`),
223 ADD KEY `publishercode` (`publishercode`)
226 $dbh->do("UPDATE `deletedbiblioitems`
227 SET `cn_class` = SUBSTR(TRIM(CONCAT_WS(' ', `classification`, `dewey`)), 1, 30),
228 `cn_item` = `subclass`,
231 $dbh->do("ALTER TABLE `deletedbiblioitems`
232 DROP COLUMN `classification`,
234 DROP COLUMN `subclass`,
235 DROP COLUMN `lcsort`,
239 # deleteditems changes
240 $dbh->do("ALTER TABLE `deleteditems`
241 MODIFY `barcode` VARCHAR(20) DEFAULT NULL,
242 MODIFY `price` DECIMAL(8,2) DEFAULT NULL,
243 MODIFY `replacementprice` DECIMAL(8,2) DEFAULT NULL,
245 MODIFY `itemcallnumber` VARCHAR(30) DEFAULT NULL AFTER `wthdrawn`,
246 MODIFY `holdingbranch` VARCHAR(10) DEFAULT NULL,
248 MODIFY `timestamp` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP AFTER `paidfor`,
250 ADD `cn_source` VARCHAR(10) DEFAULT NULL AFTER `onloan`,
251 ADD `cn_sort` VARCHAR(30) DEFAULT NULL AFTER `cn_source`,
252 ADD `ccode` VARCHAR(10) DEFAULT NULL AFTER `cn_sort`,
253 ADD `materials` VARCHAR(10) DEFAULT NULL AFTER `ccode`,
254 ADD `uri` VARCHAR(255) DEFAULT NULL AFTER `materials`,
255 MODIFY `marc` LONGBLOB AFTER `uri`,
257 DROP KEY `itembarcodeidx`,
258 DROP KEY `itembinoidx`,
259 DROP KEY `itembibnoidx`,
260 ADD UNIQUE KEY `delitembarcodeidx` (`barcode`),
261 ADD KEY `delitembinoidx` (`biblioitemnumber`),
262 ADD KEY `delitembibnoidx` (`biblionumber`),
263 ADD KEY `delhomebranch` (`homebranch`),
264 ADD KEY `delholdingbranch` (`holdingbranch`)");
265 $dbh->do("UPDATE deleteditems SET `ccode` = `itype`");
266 $dbh->do("ALTER TABLE deleteditems DROP `itype`");
267 $dbh->do("UPDATE `deleteditems` SET `cn_sort` = `itemcallnumber`");
270 $dbh->do("ALTER TABLE `items` ADD `cn_source` VARCHAR(10) DEFAULT NULL AFTER `onloan`,
271 ADD `cn_sort` VARCHAR(30) DEFAULT NULL AFTER `cn_source`,
272 ADD `ccode` VARCHAR(10) DEFAULT NULL AFTER `cn_sort`,
273 ADD `materials` VARCHAR(10) DEFAULT NULL AFTER `ccode`,
274 ADD `uri` VARCHAR(255) DEFAULT NULL AFTER `materials`
276 $dbh->do("ALTER TABLE `items`
277 DROP KEY `itembarcodeidx`,
278 ADD UNIQUE KEY `itembarcodeidx` (`barcode`)");
280 # map items.itype to items.ccode and
281 # set cn_sort to itemcallnumber -- as with biblioitems.cn_sort,
282 # will have to be subsequently updated per user's default
283 # classification scheme
284 $dbh->do("UPDATE `items` SET `cn_sort` = `itemcallnumber`,
287 $dbh->do("ALTER TABLE `items` DROP `cutterextra`,
290 print "Upgrade to $DBversion done (major changes to biblio, biblioitems, items, and deleted* versions of same\n";
291 SetVersion ($DBversion);
294 $DBversion = "3.00.00.010";
295 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
296 $dbh->do("CREATE INDEX `userid` ON borrowers (`userid`) ");
297 print "Upgrade to $DBversion done (userid index added)\n";
298 SetVersion ($DBversion);
301 $DBversion = "3.00.00.011";
302 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
303 $dbh->do("ALTER TABLE `branchcategories` CHANGE `categorycode` `categorycode` char(10) ");
304 $dbh->do("ALTER TABLE `branchcategories` CHANGE `categoryname` `categoryname` varchar(32) ");
305 $dbh->do("ALTER TABLE `branchcategories` ADD COLUMN `categorytype` varchar(16) ");
306 $dbh->do("UPDATE `branchcategories` SET `categorytype` = 'properties'");
307 $dbh->do("ALTER TABLE `branchrelations` CHANGE `categorycode` `categorycode` char(10) ");
308 print "Upgrade to $DBversion done (added branchcategory type)\n";
309 SetVersion ($DBversion);
312 $DBversion = "3.00.00.012";
313 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
314 $dbh->do("CREATE TABLE `class_sort_rules` (
315 `class_sort_rule` varchar(10) NOT NULL default '',
316 `description` mediumtext,
317 `sort_routine` varchar(30) NOT NULL default '',
318 PRIMARY KEY (`class_sort_rule`),
319 UNIQUE KEY `class_sort_rule_idx` (`class_sort_rule`)
320 ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
321 $dbh->do("CREATE TABLE `class_sources` (
322 `cn_source` varchar(10) NOT NULL default '',
323 `description` mediumtext,
324 `used` tinyint(4) NOT NULL default 0,
325 `class_sort_rule` varchar(10) NOT NULL default '',
326 PRIMARY KEY (`cn_source`),
327 UNIQUE KEY `cn_source_idx` (`cn_source`),
328 KEY `used_idx` (`used`),
329 CONSTRAINT `class_source_ibfk_1` FOREIGN KEY (`class_sort_rule`)
330 REFERENCES `class_sort_rules` (`class_sort_rule`)
331 ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
332 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type)
333 VALUES('DefaultClassificationSource','ddc',
334 'Default classification scheme used by the collection. E.g., Dewey, LCC, etc.', NULL,'free')");
335 $dbh->do("INSERT INTO `class_sort_rules` (`class_sort_rule`, `description`, `sort_routine`) VALUES
336 ('dewey', 'Default filing rules for DDC', 'Dewey'),
337 ('lcc', 'Default filing rules for LCC', 'LCC'),
338 ('generic', 'Generic call number filing rules', 'Generic')");
339 $dbh->do("INSERT INTO `class_sources` (`cn_source`, `description`, `used`, `class_sort_rule`) VALUES
340 ('ddc', 'Dewey Decimal Classification', 1, 'dewey'),
341 ('lcc', 'Library of Congress Classification', 1, 'lcc'),
342 ('udc', 'Universal Decimal Classification', 0, 'generic'),
343 ('sudocs', 'SuDoc Classification (U.S. GPO)', 0, 'generic'),
344 ('z', 'Other/Generic Classification Scheme', 0, 'generic')");
345 print "Upgrade to $DBversion done (classification sources added)\n";
346 SetVersion ($DBversion);
349 $DBversion = "3.00.00.013";
350 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
351 $dbh->do("CREATE TABLE `import_batches` (
352 `import_batch_id` int(11) NOT NULL auto_increment,
353 `template_id` int(11) default NULL,
354 `branchcode` varchar(10) default NULL,
355 `num_biblios` int(11) NOT NULL default 0,
356 `num_items` int(11) NOT NULL default 0,
357 `upload_timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP,
358 `overlay_action` enum('replace', 'create_new', 'use_template') NOT NULL default 'create_new',
359 `import_status` enum('staging', 'staged', 'importing', 'imported', 'reverting', 'reverted', 'cleaned') NOT NULL default 'staging',
360 `batch_type` enum('batch', 'z3950') NOT NULL default 'batch',
361 `file_name` varchar(100),
362 `comments` mediumtext,
363 PRIMARY KEY (`import_batch_id`),
364 KEY `branchcode` (`branchcode`)
365 ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
366 $dbh->do("CREATE TABLE `import_records` (
367 `import_record_id` int(11) NOT NULL auto_increment,
368 `import_batch_id` int(11) NOT NULL,
369 `branchcode` varchar(10) default NULL,
370 `record_sequence` int(11) NOT NULL default 0,
371 `upload_timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP,
372 `import_date` DATE default NULL,
373 `marc` longblob NOT NULL,
374 `marcxml` longtext NOT NULL,
375 `marcxml_old` longtext NOT NULL,
376 `record_type` enum('biblio', 'auth', 'holdings') NOT NULL default 'biblio',
377 `overlay_status` enum('no_match', 'auto_match', 'manual_match', 'match_applied') NOT NULL default 'no_match',
378 `status` enum('error', 'staged', 'imported', 'reverted', 'items_reverted') NOT NULL default 'staged',
379 `import_error` mediumtext,
380 `encoding` varchar(40) NOT NULL default '',
381 `z3950random` varchar(40) default NULL,
382 PRIMARY KEY (`import_record_id`),
383 CONSTRAINT `import_records_ifbk_1` FOREIGN KEY (`import_batch_id`)
384 REFERENCES `import_batches` (`import_batch_id`) ON DELETE CASCADE ON UPDATE CASCADE,
385 KEY `branchcode` (`branchcode`),
386 KEY `batch_sequence` (`import_batch_id`, `record_sequence`)
387 ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
388 $dbh->do("CREATE TABLE `import_record_matches` (
389 `import_record_id` int(11) NOT NULL,
390 `candidate_match_id` int(11) NOT NULL,
391 `score` int(11) NOT NULL default 0,
392 CONSTRAINT `import_record_matches_ibfk_1` FOREIGN KEY (`import_record_id`)
393 REFERENCES `import_records` (`import_record_id`) ON DELETE CASCADE ON UPDATE CASCADE,
394 KEY `record_score` (`import_record_id`, `score`)
395 ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
396 $dbh->do("CREATE TABLE `import_biblios` (
397 `import_record_id` int(11) NOT NULL,
398 `matched_biblionumber` int(11) default NULL,
399 `control_number` varchar(25) default NULL,
400 `original_source` varchar(25) default NULL,
401 `title` varchar(128) default NULL,
402 `author` varchar(80) default NULL,
403 `isbn` varchar(14) default NULL,
404 `issn` varchar(9) default NULL,
405 `has_items` tinyint(1) NOT NULL default 0,
406 CONSTRAINT `import_biblios_ibfk_1` FOREIGN KEY (`import_record_id`)
407 REFERENCES `import_records` (`import_record_id`) ON DELETE CASCADE ON UPDATE CASCADE,
408 KEY `matched_biblionumber` (`matched_biblionumber`),
409 KEY `title` (`title`),
411 ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
412 $dbh->do("CREATE TABLE `import_items` (
413 `import_items_id` int(11) NOT NULL auto_increment,
414 `import_record_id` int(11) NOT NULL,
415 `itemnumber` int(11) default NULL,
416 `branchcode` varchar(10) default NULL,
417 `status` enum('error', 'staged', 'imported', 'reverted') NOT NULL default 'staged',
418 `marcxml` longtext NOT NULL,
419 `import_error` mediumtext,
420 PRIMARY KEY (`import_items_id`),
421 CONSTRAINT `import_items_ibfk_1` FOREIGN KEY (`import_record_id`)
422 REFERENCES `import_records` (`import_record_id`) ON DELETE CASCADE ON UPDATE CASCADE,
423 KEY `itemnumber` (`itemnumber`),
424 KEY `branchcode` (`branchcode`)
425 ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
427 $dbh->do("INSERT INTO `import_batches`
428 (`overlay_action`, `import_status`, `batch_type`, `file_name`)
429 SELECT distinct 'create_new', 'staged', 'z3950', `file`
430 FROM `marc_breeding`");
432 $dbh->do("INSERT INTO `import_records`
433 (`import_batch_id`, `import_record_id`, `record_sequence`, `marc`, `record_type`, `status`,
434 `encoding`, `z3950random`, `marcxml`, `marcxml_old`)
435 SELECT `import_batch_id`, `id`, 1, `marc`, 'biblio', 'staged', `encoding`, `z3950random`, '', ''
437 JOIN `import_batches` ON (`file_name` = `file`)");
439 $dbh->do("INSERT INTO `import_biblios`
440 (`import_record_id`, `title`, `author`, `isbn`)
441 SELECT `import_record_id`, `title`, `author`, `isbn`
443 JOIN `import_records` ON (`import_record_id` = `id`)");
445 $dbh->do("UPDATE `import_batches`
446 SET `num_biblios` = (
448 FROM `import_records`
449 WHERE `import_batch_id` = `import_batches`.`import_batch_id`
452 $dbh->do("DROP TABLE `marc_breeding`");
454 print "Upgrade to $DBversion done (import_batches et al. added)\n";
455 SetVersion ($DBversion);
458 $DBversion = "3.00.00.014";
459 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
460 $dbh->do("ALTER TABLE subscription ADD lastbranch VARCHAR(4)");
461 print "Upgrade to $DBversion done (userid index added)\n";
462 SetVersion ($DBversion);
465 $DBversion = "3.00.00.015";
466 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
467 $dbh->do("CREATE TABLE `saved_sql` (
468 `id` int(11) NOT NULL auto_increment,
469 `borrowernumber` int(11) default NULL,
470 `date_created` datetime default NULL,
471 `last_modified` datetime default NULL,
473 `last_run` datetime default NULL,
474 `report_name` varchar(255) default NULL,
475 `type` varchar(255) default NULL,
478 KEY boridx (`borrowernumber`)
479 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;");
480 $dbh->do("CREATE TABLE `saved_reports` (
481 `id` int(11) NOT NULL auto_increment,
482 `report_id` int(11) default NULL,
484 `date_run` datetime default NULL,
486 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;");
487 print "Upgrade to $DBversion done (saved_sql and saved_reports added)\n";
488 SetVersion ($DBversion);
491 $DBversion = "3.00.00.016";
492 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
493 $dbh->do(" CREATE TABLE reports_dictionary (
494 id int(11) NOT NULL auto_increment,
495 name varchar(255) default NULL,
497 date_created datetime default NULL,
498 date_modified datetime default NULL,
500 area int(11) default NULL,
502 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ");
503 print "Upgrade to $DBversion done (reports_dictionary) added)\n";
504 SetVersion ($DBversion);
507 $DBversion = "3.00.00.017";
508 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
509 $dbh->do("ALTER TABLE action_logs DROP PRIMARY KEY");
510 $dbh->do("ALTER TABLE action_logs ADD KEY timestamp (timestamp,user)");
511 $dbh->do("ALTER TABLE action_logs ADD action_id INT(11) NOT NULL FIRST");
512 $dbh->do("UPDATE action_logs SET action_id = if (\@a, \@a:=\@a+1, \@a:=1)");
513 $dbh->do("ALTER TABLE action_logs MODIFY action_id INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY");
514 print "Upgrade to $DBversion done (added column to action_logs)\n";
515 SetVersion ($DBversion);
518 $DBversion = "3.00.00.018";
519 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
520 $dbh->do("ALTER TABLE `zebraqueue`
521 ADD `done` INT NOT NULL DEFAULT '0',
522 ADD `time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ;
524 print "Upgrade to $DBversion done (adding timestamp and done columns to zebraque table to improve problem tracking) added)\n";
525 SetVersion ($DBversion);
528 $DBversion = "3.00.00.019";
529 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
530 $dbh->do("ALTER TABLE biblio MODIFY biblionumber INT(11) NOT NULL AUTO_INCREMENT");
531 $dbh->do("ALTER TABLE biblioitems MODIFY biblioitemnumber INT(11) NOT NULL AUTO_INCREMENT");
532 $dbh->do("ALTER TABLE items MODIFY itemnumber INT(11) NOT NULL AUTO_INCREMENT");
533 print "Upgrade to $DBversion done (made bib/item PKs auto_increment)\n";
534 SetVersion ($DBversion);
537 $DBversion = "3.00.00.020";
538 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
539 $dbh->do("ALTER TABLE deleteditems
540 DROP KEY `delitembarcodeidx`,
541 ADD KEY `delitembarcodeidx` (`barcode`)");
542 print "Upgrade to $DBversion done (dropped uniqueness of key on deleteditems.barcode)\n";
543 SetVersion ($DBversion);
546 $DBversion = "3.00.00.021";
547 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
548 $dbh->do("ALTER TABLE items CHANGE homebranch homebranch VARCHAR(10)");
549 $dbh->do("ALTER TABLE deleteditems CHANGE homebranch homebranch VARCHAR(10)");
550 $dbh->do("ALTER TABLE statistics CHANGE branch branch VARCHAR(10)");
551 $dbh->do("ALTER TABLE subscription CHANGE lastbranch lastbranch VARCHAR(10)");
552 print "Upgrade to $DBversion done (extended missed branchcode columns to 10 chars)\n";
553 SetVersion ($DBversion);
556 $DBversion = "3.00.00.022";
557 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
558 $dbh->do("ALTER TABLE items
559 ADD `damaged` tinyint(1) default NULL");
560 print "Upgrade to $DBversion done (adding damaged column to items table)\n";
561 SetVersion ($DBversion);
564 $DBversion = "3.00.00.023";
565 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
566 $dbh->do("INSERT INTO `systempreferences` (variable,value,options,explanation,type)
567 VALUES ('yuipath','http://yui.yahooapis.com/2.3.1/build','Insert the path to YUI libraries','','free')");
568 print "Upgrade to $DBversion done (adding new system preference for controlling YUI path)\n";
569 SetVersion ($DBversion);
572 $DBversion = "3.00.00.024";
573 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
574 $dbh->do("ALTER TABLE biblioitems CHANGE itemtype itemtype VARCHAR(10)");
575 print "Upgrade to $DBversion done (changing itemtype to (10))\n";
576 SetVersion ($DBversion);
579 $DBversion = "3.00.00.025";
580 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
581 $dbh->do("ALTER TABLE items ADD COLUMN itype VARCHAR(10)");
582 if(C4::Context->preference('item-level_itypes')){
583 $dbh->do('update items,biblioitems set items.itype=biblioitems.itemtype where items.biblionumber=biblioitems.biblionumber and itype is null');
585 print "Upgrade to $DBversion done (reintroduce items.itype - fill from itemtype)\n ";
586 SetVersion ($DBversion);
589 $DBversion = "3.00.00.026";
590 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
591 $dbh->do("INSERT INTO `systempreferences` (variable,value,options,explanation,type)
592 VALUES ('HomeOrHoldingBranch','homebranch','homebranch|holdingbranch','With independent branches turned on this decides whether to check the items holdingbranch or homebranch at circulatilon','choice')");
593 print "Upgrade to $DBversion done (adding new system preference for choosing whether homebranch or holdingbranch is checked in circulation)\n";
594 SetVersion ($DBversion);
597 $DBversion = "3.00.00.027";
598 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
599 $dbh->do("CREATE TABLE `marc_matchers` (
600 `matcher_id` int(11) NOT NULL auto_increment,
601 `code` varchar(10) NOT NULL default '',
602 `description` varchar(255) NOT NULL default '',
603 `record_type` varchar(10) NOT NULL default 'biblio',
604 `threshold` int(11) NOT NULL default 0,
605 PRIMARY KEY (`matcher_id`),
607 KEY `record_type` (`record_type`)
608 ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
609 $dbh->do("CREATE TABLE `matchpoints` (
610 `matcher_id` int(11) NOT NULL,
611 `matchpoint_id` int(11) NOT NULL auto_increment,
612 `search_index` varchar(30) NOT NULL default '',
613 `score` int(11) NOT NULL default 0,
614 PRIMARY KEY (`matchpoint_id`),
615 CONSTRAINT `matchpoints_ifbk_1` FOREIGN KEY (`matcher_id`)
616 REFERENCES `marc_matchers` (`matcher_id`) ON DELETE CASCADE ON UPDATE CASCADE
617 ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
618 $dbh->do("CREATE TABLE `matchpoint_components` (
619 `matchpoint_id` int(11) NOT NULL,
620 `matchpoint_component_id` int(11) NOT NULL auto_increment,
621 sequence int(11) NOT NULL default 0,
622 tag varchar(3) NOT NULL default '',
623 subfields varchar(40) NOT NULL default '',
624 offset int(4) NOT NULL default 0,
625 length int(4) NOT NULL default 0,
626 PRIMARY KEY (`matchpoint_component_id`),
627 KEY `by_sequence` (`matchpoint_id`, `sequence`),
628 CONSTRAINT `matchpoint_components_ifbk_1` FOREIGN KEY (`matchpoint_id`)
629 REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE
630 ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
631 $dbh->do("CREATE TABLE `matchpoint_component_norms` (
632 `matchpoint_component_id` int(11) NOT NULL,
633 `sequence` int(11) NOT NULL default 0,
634 `norm_routine` varchar(50) NOT NULL default '',
635 KEY `matchpoint_component_norms` (`matchpoint_component_id`, `sequence`),
636 CONSTRAINT `matchpoint_component_norms_ifbk_1` FOREIGN KEY (`matchpoint_component_id`)
637 REFERENCES `matchpoint_components` (`matchpoint_component_id`) ON DELETE CASCADE ON UPDATE CASCADE
638 ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
639 $dbh->do("CREATE TABLE `matcher_matchpoints` (
640 `matcher_id` int(11) NOT NULL,
641 `matchpoint_id` int(11) NOT NULL,
642 CONSTRAINT `matcher_matchpoints_ifbk_1` FOREIGN KEY (`matcher_id`)
643 REFERENCES `marc_matchers` (`matcher_id`) ON DELETE CASCADE ON UPDATE CASCADE,
644 CONSTRAINT `matcher_matchpoints_ifbk_2` FOREIGN KEY (`matchpoint_id`)
645 REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE
646 ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
647 $dbh->do("CREATE TABLE `matchchecks` (
648 `matcher_id` int(11) NOT NULL,
649 `matchcheck_id` int(11) NOT NULL auto_increment,
650 `source_matchpoint_id` int(11) NOT NULL,
651 `target_matchpoint_id` int(11) NOT NULL,
652 PRIMARY KEY (`matchcheck_id`),
653 CONSTRAINT `matcher_matchchecks_ifbk_1` FOREIGN KEY (`matcher_id`)
654 REFERENCES `marc_matchers` (`matcher_id`) ON DELETE CASCADE ON UPDATE CASCADE,
655 CONSTRAINT `matcher_matchchecks_ifbk_2` FOREIGN KEY (`source_matchpoint_id`)
656 REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE,
657 CONSTRAINT `matcher_matchchecks_ifbk_3` FOREIGN KEY (`target_matchpoint_id`)
658 REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE
659 ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
660 print "Upgrade to $DBversion done (added C4::Matcher serialization tables)\n ";
661 SetVersion ($DBversion);
664 $DBversion = "3.00.00.028";
665 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
666 $dbh->do("INSERT INTO `systempreferences` (variable,value,options,explanation,type)
667 VALUES ('canreservefromotherbranches','1','','With Independent branches on, can a user from one library reserve an item from another library','YesNo')");
668 print "Upgrade to $DBversion done (adding new system preference for changing reserve/holds behaviour with independent branches)\n";
669 SetVersion ($DBversion);
673 =item DropAllForeignKeys($table)
675 Drop all foreign keys of the table $table
679 sub DropAllForeignKeys {
681 # get the table description
682 my $sth = $dbh->prepare("SHOW CREATE TABLE $table");
684 my $vsc_structure = $sth->fetchrow;
685 # split on CONSTRAINT keyword
686 my @fks = split /CONSTRAINT /,$vsc_structure;
689 # isolate what is before FOREIGN KEY, if there is something, it's a foreign key to drop
690 $_ = /(.*) FOREIGN KEY.*/;
693 # we have found 1 foreign, drop it
694 $dbh->do("ALTER TABLE $table DROP FOREIGN KEY $id");
708 Transform the Koha version from a 4 parts string
709 to a number, with just 1 .
715 # remove the 3 last . to have a Perl number
716 $version =~ s/(.*\..*)\.(.*)\.(.*)/$1$2$3/;
721 set the DBversion in the systempreferences
725 my $kohaversion = TransformToNum(shift);
726 if (C4::Context->preference('Version')) {
727 my $finish=$dbh->prepare("UPDATE systempreferences SET value=? WHERE variable='Version'");
728 $finish->execute($kohaversion);
730 my $finish=$dbh->prepare("INSERT into systempreferences (variable,value,explanation) values ('Version',?,'The Koha database version. Don t change this value manually, it s holded by the webinstaller')");
731 $finish->execute($kohaversion);
736 # Revision 1.172 2007/07/19 10:21:22 hdl