5 # This script checks for required updates to the database.
7 # Part of the Koha Library Software www.koha.org
8 # Licensed under the GPL.
11 # - Would also be a good idea to offer to do a backup at this time...
13 # NOTE: If you do something more than once in here, make it table driven.
15 # NOTE: Please keep the version in C4/Context.pm up-to-date!
26 use MARC::File::XML ( BinaryEncoding => 'utf8' );
28 # FIXME - The user might be installing a new database, so can't rely
29 # on /etc/koha.conf anyway.
36 %existingtables, # tables already in database
40 $type, $null, $key, $default, $extra,
41 $prefitem, # preference item in systempreferences table
48 my $dbh = C4::Context->dbh;
49 $|=1; # flushes output
52 Deal with virtualshelves
55 my $DBversion = "3.00.00.001";
56 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
57 # update virtualshelves table to
59 $dbh->do("ALTER TABLE `bookshelf` RENAME `virtualshelves`");
60 $dbh->do("ALTER TABLE `shelfcontents` RENAME `virtualshelfcontents`");
61 $dbh->do("ALTER TABLE `virtualshelfcontents` ADD `biblionumber` INT( 11 ) NOT NULL");
62 $dbh->do("UPDATE `virtualshelfcontents` SET biblionumber=(SELECT biblionumber FROM items WHERE items.itemnumber=virtualshelfcontents.itemnumber)");
63 # drop all foreign keys : otherwise, we can't drop itemnumber field.
64 DropAllForeignKeys('virtualshelfcontents');
65 # create the new foreign keys (on biblionumber)
66 $dbh->do("ALTER TABLE `virtualshelfcontents` ADD FOREIGN KEY biblionumber_fk (biblionumber) REFERENCES biblio (biblionumber) ON UPDATE CASCADE ON DELETE CASCADE");
67 # re-create the foreign key on virtualshelf
68 $dbh->do("ALTER TABLE `virtualshelfcontents` ADD FOREIGN KEY shelfnumber_fk (shelfnumber) REFERENCES virtualshelves (shelfnumber) ON UPDATE CASCADE ON DELETE CASCADE");
69 # now we can drop the itemnumber column
70 $dbh->do("ALTER TABLE `virtualshelfcontents` DROP `itemnumber`");
71 print "Upgrade to $DBversion done (virtualshelves)\n";
72 SetVersion ($DBversion);
76 $DBversion = "3.00.00.002";
77 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
78 $dbh->do("DROP TABLE sessions");
79 $dbh->do("CREATE TABLE `sessions` (
80 `id` char(32) NOT NULL,
81 `a_session` text NOT NULL,
82 UNIQUE KEY `id` (`id`)
83 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;");
84 print "Upgrade to $DBversion done (sessions uses CGI::session, new table structure for sessions)\n";
85 SetVersion ($DBversion);
89 $DBversion = "3.00.00.003";
90 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
91 if (C4::Context->preference("opaclanguage") eq "fr") {
92 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('ReservesNeedReturns','0','Si ce paramètre est mis à 1, une réservation posée sur un exemplaire présent sur le site devra être passée en retour pour être disponible. Sinon, elle sera automatiquement disponible, Koha considère que le bibliothécaire place la réservation en ayant le document en mains','','YesNo')");
94 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('ReservesNeedReturns','0','If set, a reserve done on an item available in this branch need a check-in, otherwise, a reserve on a specific item, that is on the branch & available is considered as available','','YesNo')");
96 print "Upgrade to $DBversion done (adding ReservesNeedReturns systempref, in circulation)\n";
97 SetVersion ($DBversion);
101 $DBversion = "3.00.00.004";
102 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
103 $dbh->do("INSERT INTO `systempreferences` VALUES ('DebugLevel','2','set the level of error info sent to the browser. 0=none, 1=some, 2=most','0|1|2','Choice')");
104 print "Upgrade to $DBversion done (adding DebugLevel systempref, in 'Admin' tab)\n";
105 SetVersion ($DBversion);
108 $DBversion = "3.00.00.005";
109 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
110 $dbh->do("CREATE TABLE `tags` (
111 `entry` varchar(255) NOT NULL default '',
112 `weight` bigint(20) NOT NULL default 0,
113 PRIMARY KEY (`entry`)
114 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
116 $dbh->do("CREATE TABLE `nozebra` (
117 `server` varchar(20) NOT NULL,
118 `indexname` varchar(40) NOT NULL,
119 `value` varchar(250) NOT NULL,
120 `biblionumbers` longtext NOT NULL,
121 KEY `indexname` (`server`,`indexname`),
122 KEY `value` (`server`,`value`))
123 ENGINE=InnoDB DEFAULT CHARSET=utf8;
125 print "Upgrade to $DBversion done (adding tags and nozebra tables )\n";
126 SetVersion ($DBversion);
129 $DBversion = "3.00.00.006";
130 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
131 $dbh->do("UPDATE issues SET issuedate=timestamp WHERE issuedate='0000-00-00'");
132 print "Upgrade to $DBversion done (filled issues.issuedate with timestamp)\n";
133 SetVersion ($DBversion);
136 $DBversion = "3.00.00.007";
137 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
138 $dbh->do("INSERT INTO systempreferences (variable,value,explanation,options,type) VALUES ('SessionStorage','mysql','Use mysql or a temporary file for storing session data','mysql|tmp','Choice')");
139 print "Upgrade to $DBversion done (set SessionStorage variable)\n";
140 SetVersion ($DBversion);
143 $DBversion = "3.00.00.008";
144 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
145 $dbh->do("ALTER TABLE `biblio` ADD `datecreated` DATE NOT NULL AFTER `timestamp` ;");
146 $dbh->do("UPDATE biblio SET datecreated=timestamp");
147 print "Upgrade to $DBversion done (biblio creation date)\n";
148 SetVersion ($DBversion);
151 $DBversion = "3.00.00.009";
152 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
154 # Create backups of call number columns
155 # in case default migration needs to be customized
157 # UPGRADE NOTE: temp_upg_biblioitems_call_num should be dropped
158 # after call numbers have been transformed to the new structure
160 # Not bothering to do the same with deletedbiblioitems -- assume
161 # default is good enough.
162 $dbh->do("CREATE TABLE `temp_upg_biblioitems_call_num` AS
163 SELECT `biblioitemnumber`, `biblionumber`,
164 `classification`, `dewey`, `subclass`,
166 FROM `biblioitems`");
168 # biblioitems changes
169 $dbh->do("ALTER TABLE `biblioitems` CHANGE COLUMN `volumeddesc` `volumedesc` TEXT,
170 ADD `cn_source` VARCHAR(10) DEFAULT NULL AFTER `ccode`,
171 ADD `cn_class` VARCHAR(30) DEFAULT NULL AFTER `cn_source`,
172 ADD `cn_item` VARCHAR(10) DEFAULT NULL AFTER `cn_class`,
173 ADD `cn_suffix` VARCHAR(10) DEFAULT NULL AFTER `cn_item`,
174 ADD `cn_sort` VARCHAR(30) DEFAULT NULL AFTER `cn_suffix`,
175 ADD `totalissues` INT(10) AFTER `cn_sort`");
177 # default mapping of call number columns:
178 # cn_class = concatentation of classification + dewey,
179 # trimmed to fit -- assumes that most users do not
180 # populate both classification and dewey in a single record
182 # cn_source = left null
185 # After upgrade, cn_sort will have to be set based on whatever
186 # default call number scheme user sets as a preference. Misc
187 # script will be added at some point to do that.
189 $dbh->do("UPDATE `biblioitems`
190 SET cn_class = SUBSTR(TRIM(CONCAT_WS(' ', `classification`, `dewey`)), 1, 30),
195 # Now drop the old call number columns
196 $dbh->do("ALTER TABLE `biblioitems` DROP COLUMN `classification`,
198 DROP COLUMN `subclass`,
199 DROP COLUMN `lcsort`,
200 DROP COLUMN `ccode`");
202 # deletedbiblio changes
203 $dbh->do("ALTER TABLE `deletedbiblio` ALTER COLUMN `frameworkcode` SET DEFAULT '',
205 ADD `datecreated` DATE NOT NULL AFTER `timestamp`");
206 $dbh->do("UPDATE deletedbiblio SET datecreated = timestamp");
208 # deletedbiblioitems changes
209 $dbh->do("ALTER TABLE `deletedbiblioitems`
210 MODIFY `publicationyear` TEXT,
211 CHANGE `volumeddesc` `volumedesc` TEXT,
212 MODIFY `collectiontitle` MEDIUMTEXT DEFAULT NULL AFTER `volumedesc`,
213 MODIFY `collectionissn` TEXT DEFAULT NULL AFTER `collectiontitle`,
214 MODIFY `collectionvolume` MEDIUMTEXT DEFAULT NULL AFTER `collectionissn`,
215 MODIFY `editionstatement` TEXT DEFAULT NULL AFTER `collectionvolume`,
216 MODIFY `editionresponsibility` TEXT DEFAULT NULL AFTER `editionstatement`,
217 MODIFY `place` VARCHAR(255) DEFAULT NULL AFTER `size`,
219 ADD `cn_source` VARCHAR(10) DEFAULT NULL AFTER `url`,
220 ADD `cn_class` VARCHAR(30) DEFAULT NULL AFTER `cn_source`,
221 ADD `cn_item` VARCHAR(10) DEFAULT NULL AFTER `cn_class`,
222 ADD `cn_suffix` VARCHAR(10) DEFAULT NULL AFTER `cn_item`,
223 ADD `cn_sort` VARCHAR(30) DEFAULT NULL AFTER `cn_suffix`,
224 ADD `totalissues` INT(10) AFTER `cn_sort`,
225 ADD KEY `isbn` (`isbn`),
226 ADD KEY `publishercode` (`publishercode`)
229 $dbh->do("UPDATE `deletedbiblioitems`
230 SET `cn_class` = SUBSTR(TRIM(CONCAT_WS(' ', `classification`, `dewey`)), 1, 30),
231 `cn_item` = `subclass`,
234 $dbh->do("ALTER TABLE `deletedbiblioitems`
235 DROP COLUMN `classification`,
237 DROP COLUMN `subclass`,
238 DROP COLUMN `lcsort`,
242 # deleteditems changes
243 $dbh->do("ALTER TABLE `deleteditems`
244 MODIFY `barcode` VARCHAR(20) DEFAULT NULL,
245 MODIFY `price` DECIMAL(8,2) DEFAULT NULL,
246 MODIFY `replacementprice` DECIMAL(8,2) DEFAULT NULL,
248 MODIFY `itemcallnumber` VARCHAR(30) DEFAULT NULL AFTER `wthdrawn`,
249 MODIFY `holdingbranch` VARCHAR(10) DEFAULT NULL,
251 MODIFY `timestamp` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP AFTER `paidfor`,
253 ADD `cn_source` VARCHAR(10) DEFAULT NULL AFTER `onloan`,
254 ADD `cn_sort` VARCHAR(30) DEFAULT NULL AFTER `cn_source`,
255 ADD `ccode` VARCHAR(10) DEFAULT NULL AFTER `cn_sort`,
256 ADD `materials` VARCHAR(10) DEFAULT NULL AFTER `ccode`,
257 ADD `uri` VARCHAR(255) DEFAULT NULL AFTER `materials`,
258 MODIFY `marc` LONGBLOB AFTER `uri`,
260 DROP KEY `itembarcodeidx`,
261 DROP KEY `itembinoidx`,
262 DROP KEY `itembibnoidx`,
263 ADD UNIQUE KEY `delitembarcodeidx` (`barcode`),
264 ADD KEY `delitembinoidx` (`biblioitemnumber`),
265 ADD KEY `delitembibnoidx` (`biblionumber`),
266 ADD KEY `delhomebranch` (`homebranch`),
267 ADD KEY `delholdingbranch` (`holdingbranch`)");
268 $dbh->do("UPDATE deleteditems SET `ccode` = `itype`");
269 $dbh->do("ALTER TABLE deleteditems DROP `itype`");
270 $dbh->do("UPDATE `deleteditems` SET `cn_sort` = `itemcallnumber`");
273 $dbh->do("ALTER TABLE `items` ADD `cn_source` VARCHAR(10) DEFAULT NULL AFTER `onloan`,
274 ADD `cn_sort` VARCHAR(30) DEFAULT NULL AFTER `cn_source`,
275 ADD `ccode` VARCHAR(10) DEFAULT NULL AFTER `cn_sort`,
276 ADD `materials` VARCHAR(10) DEFAULT NULL AFTER `ccode`,
277 ADD `uri` VARCHAR(255) DEFAULT NULL AFTER `materials`
279 $dbh->do("ALTER TABLE `items`
280 DROP KEY `itembarcodeidx`,
281 ADD UNIQUE KEY `itembarcodeidx` (`barcode`)");
283 # map items.itype to items.ccode and
284 # set cn_sort to itemcallnumber -- as with biblioitems.cn_sort,
285 # will have to be subsequently updated per user's default
286 # classification scheme
287 $dbh->do("UPDATE `items` SET `cn_sort` = `itemcallnumber`,
290 $dbh->do("ALTER TABLE `items` DROP `cutterextra`,
293 print "Upgrade to $DBversion done (major changes to biblio, biblioitems, items, and deleted* versions of same\n";
294 SetVersion ($DBversion);
297 $DBversion = "3.00.00.010";
298 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
299 $dbh->do("CREATE INDEX `userid` ON borrowers (`userid`) ");
300 print "Upgrade to $DBversion done (userid index added)\n";
301 SetVersion ($DBversion);
304 $DBversion = "3.00.00.011";
305 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
306 $dbh->do("ALTER TABLE `branchcategories` CHANGE `categorycode` `categorycode` char(10) ");
307 $dbh->do("ALTER TABLE `branchcategories` CHANGE `categoryname` `categoryname` varchar(32) ");
308 $dbh->do("ALTER TABLE `branchcategories` ADD COLUMN `categorytype` varchar(16) ");
309 $dbh->do("UPDATE `branchcategories` SET `categorytype` = 'properties'");
310 $dbh->do("ALTER TABLE `branchrelations` CHANGE `categorycode` `categorycode` char(10) ");
311 print "Upgrade to $DBversion done (added branchcategory type)\n";
312 SetVersion ($DBversion);
315 $DBversion = "3.00.00.012";
316 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
317 $dbh->do("CREATE TABLE `class_sort_rules` (
318 `class_sort_rule` varchar(10) NOT NULL default '',
319 `description` mediumtext,
320 `sort_routine` varchar(30) NOT NULL default '',
321 PRIMARY KEY (`class_sort_rule`),
322 UNIQUE KEY `class_sort_rule_idx` (`class_sort_rule`)
323 ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
324 $dbh->do("CREATE TABLE `class_sources` (
325 `cn_source` varchar(10) NOT NULL default '',
326 `description` mediumtext,
327 `used` tinyint(4) NOT NULL default 0,
328 `class_sort_rule` varchar(10) NOT NULL default '',
329 PRIMARY KEY (`cn_source`),
330 UNIQUE KEY `cn_source_idx` (`cn_source`),
331 KEY `used_idx` (`used`),
332 CONSTRAINT `class_source_ibfk_1` FOREIGN KEY (`class_sort_rule`)
333 REFERENCES `class_sort_rules` (`class_sort_rule`)
334 ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
335 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type)
336 VALUES('DefaultClassificationSource','ddc',
337 'Default classification scheme used by the collection. E.g., Dewey, LCC, etc.', NULL,'free')");
338 $dbh->do("INSERT INTO `class_sort_rules` (`class_sort_rule`, `description`, `sort_routine`) VALUES
339 ('dewey', 'Default filing rules for DDC', 'Dewey'),
340 ('lcc', 'Default filing rules for LCC', 'LCC'),
341 ('generic', 'Generic call number filing rules', 'Generic')");
342 $dbh->do("INSERT INTO `class_sources` (`cn_source`, `description`, `used`, `class_sort_rule`) VALUES
343 ('ddc', 'Dewey Decimal Classification', 1, 'dewey'),
344 ('lcc', 'Library of Congress Classification', 1, 'lcc'),
345 ('udc', 'Universal Decimal Classification', 0, 'generic'),
346 ('sudocs', 'SuDoc Classification (U.S. GPO)', 0, 'generic'),
347 ('z', 'Other/Generic Classification Scheme', 0, 'generic')");
348 print "Upgrade to $DBversion done (classification sources added)\n";
349 SetVersion ($DBversion);
352 $DBversion = "3.00.00.013";
353 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
354 $dbh->do("CREATE TABLE `import_batches` (
355 `import_batch_id` int(11) NOT NULL auto_increment,
356 `template_id` int(11) default NULL,
357 `branchcode` varchar(10) default NULL,
358 `num_biblios` int(11) NOT NULL default 0,
359 `num_items` int(11) NOT NULL default 0,
360 `upload_timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP,
361 `overlay_action` enum('replace', 'create_new', 'use_template') NOT NULL default 'create_new',
362 `import_status` enum('staging', 'staged', 'importing', 'imported', 'reverting', 'reverted', 'cleaned') NOT NULL default 'staging',
363 `batch_type` enum('batch', 'z3950') NOT NULL default 'batch',
364 `file_name` varchar(100),
365 `comments` mediumtext,
366 PRIMARY KEY (`import_batch_id`),
367 KEY `branchcode` (`branchcode`)
368 ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
369 $dbh->do("CREATE TABLE `import_records` (
370 `import_record_id` int(11) NOT NULL auto_increment,
371 `import_batch_id` int(11) NOT NULL,
372 `branchcode` varchar(10) default NULL,
373 `record_sequence` int(11) NOT NULL default 0,
374 `upload_timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP,
375 `import_date` DATE default NULL,
376 `marc` longblob NOT NULL,
377 `marcxml` longtext NOT NULL,
378 `marcxml_old` longtext NOT NULL,
379 `record_type` enum('biblio', 'auth', 'holdings') NOT NULL default 'biblio',
380 `overlay_status` enum('no_match', 'auto_match', 'manual_match', 'match_applied') NOT NULL default 'no_match',
381 `status` enum('error', 'staged', 'imported', 'reverted', 'items_reverted') NOT NULL default 'staged',
382 `import_error` mediumtext,
383 `encoding` varchar(40) NOT NULL default '',
384 `z3950random` varchar(40) default NULL,
385 PRIMARY KEY (`import_record_id`),
386 CONSTRAINT `import_records_ifbk_1` FOREIGN KEY (`import_batch_id`)
387 REFERENCES `import_batches` (`import_batch_id`) ON DELETE CASCADE ON UPDATE CASCADE,
388 KEY `branchcode` (`branchcode`),
389 KEY `batch_sequence` (`import_batch_id`, `record_sequence`)
390 ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
391 $dbh->do("CREATE TABLE `import_record_matches` (
392 `import_record_id` int(11) NOT NULL,
393 `candidate_match_id` int(11) NOT NULL,
394 `score` int(11) NOT NULL default 0,
395 CONSTRAINT `import_record_matches_ibfk_1` FOREIGN KEY (`import_record_id`)
396 REFERENCES `import_records` (`import_record_id`) ON DELETE CASCADE ON UPDATE CASCADE,
397 KEY `record_score` (`import_record_id`, `score`)
398 ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
399 $dbh->do("CREATE TABLE `import_biblios` (
400 `import_record_id` int(11) NOT NULL,
401 `matched_biblionumber` int(11) default NULL,
402 `control_number` varchar(25) default NULL,
403 `original_source` varchar(25) default NULL,
404 `title` varchar(128) default NULL,
405 `author` varchar(80) default NULL,
406 `isbn` varchar(14) default NULL,
407 `issn` varchar(9) default NULL,
408 `has_items` tinyint(1) NOT NULL default 0,
409 CONSTRAINT `import_biblios_ibfk_1` FOREIGN KEY (`import_record_id`)
410 REFERENCES `import_records` (`import_record_id`) ON DELETE CASCADE ON UPDATE CASCADE,
411 KEY `matched_biblionumber` (`matched_biblionumber`),
412 KEY `title` (`title`),
414 ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
415 $dbh->do("CREATE TABLE `import_items` (
416 `import_items_id` int(11) NOT NULL auto_increment,
417 `import_record_id` int(11) NOT NULL,
418 `itemnumber` int(11) default NULL,
419 `branchcode` varchar(10) default NULL,
420 `status` enum('error', 'staged', 'imported', 'reverted') NOT NULL default 'staged',
421 `marcxml` longtext NOT NULL,
422 `import_error` mediumtext,
423 PRIMARY KEY (`import_items_id`),
424 CONSTRAINT `import_items_ibfk_1` FOREIGN KEY (`import_record_id`)
425 REFERENCES `import_records` (`import_record_id`) ON DELETE CASCADE ON UPDATE CASCADE,
426 KEY `itemnumber` (`itemnumber`),
427 KEY `branchcode` (`branchcode`)
428 ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
430 $dbh->do("INSERT INTO `import_batches`
431 (`overlay_action`, `import_status`, `batch_type`, `file_name`)
432 SELECT distinct 'create_new', 'staged', 'z3950', `file`
433 FROM `marc_breeding`");
435 $dbh->do("INSERT INTO `import_records`
436 (`import_batch_id`, `import_record_id`, `record_sequence`, `marc`, `record_type`, `status`,
437 `encoding`, `z3950random`, `marcxml`, `marcxml_old`)
438 SELECT `import_batch_id`, `id`, 1, `marc`, 'biblio', 'staged', `encoding`, `z3950random`, '', ''
440 JOIN `import_batches` ON (`file_name` = `file`)");
442 $dbh->do("INSERT INTO `import_biblios`
443 (`import_record_id`, `title`, `author`, `isbn`)
444 SELECT `import_record_id`, `title`, `author`, `isbn`
446 JOIN `import_records` ON (`import_record_id` = `id`)");
448 $dbh->do("UPDATE `import_batches`
449 SET `num_biblios` = (
451 FROM `import_records`
452 WHERE `import_batch_id` = `import_batches`.`import_batch_id`
455 $dbh->do("DROP TABLE `marc_breeding`");
457 print "Upgrade to $DBversion done (import_batches et al. added)\n";
458 SetVersion ($DBversion);
461 $DBversion = "3.00.00.014";
462 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
463 $dbh->do("ALTER TABLE subscription ADD lastbranch VARCHAR(4)");
464 print "Upgrade to $DBversion done (userid index added)\n";
465 SetVersion ($DBversion);
468 $DBversion = "3.00.00.015";
469 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
470 $dbh->do("CREATE TABLE `saved_sql` (
471 `id` int(11) NOT NULL auto_increment,
472 `borrowernumber` int(11) default NULL,
473 `date_created` datetime default NULL,
474 `last_modified` datetime default NULL,
476 `last_run` datetime default NULL,
477 `report_name` varchar(255) default NULL,
478 `type` varchar(255) default NULL,
481 KEY boridx (`borrowernumber`)
482 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;");
483 $dbh->do("CREATE TABLE `saved_reports` (
484 `id` int(11) NOT NULL auto_increment,
485 `report_id` int(11) default NULL,
487 `date_run` datetime default NULL,
489 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;");
490 print "Upgrade to $DBversion done (saved_sql and saved_reports added)\n";
491 SetVersion ($DBversion);
494 $DBversion = "3.00.00.016";
495 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
496 $dbh->do(" CREATE TABLE reports_dictionary (
497 id int(11) NOT NULL auto_increment,
498 name varchar(255) default NULL,
500 date_created datetime default NULL,
501 date_modified datetime default NULL,
503 area int(11) default NULL,
505 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ");
506 print "Upgrade to $DBversion done (reports_dictionary) added)\n";
507 SetVersion ($DBversion);
510 $DBversion = "3.00.00.017";
511 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
512 $dbh->do("ALTER TABLE action_logs DROP PRIMARY KEY");
513 $dbh->do("ALTER TABLE action_logs ADD KEY timestamp (timestamp,user)");
514 $dbh->do("ALTER TABLE action_logs ADD action_id INT(11) NOT NULL FIRST");
515 $dbh->do("UPDATE action_logs SET action_id = if (\@a, \@a:=\@a+1, \@a:=1)");
516 $dbh->do("ALTER TABLE action_logs MODIFY action_id INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY");
517 print "Upgrade to $DBversion done (added column to action_logs)\n";
518 SetVersion ($DBversion);
521 $DBversion = "3.00.00.018";
522 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
523 $dbh->do("ALTER TABLE `zebraqueue`
524 ADD `done` INT NOT NULL DEFAULT '0',
525 ADD `time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ;
527 print "Upgrade to $DBversion done (adding timestamp and done columns to zebraque table to improve problem tracking) added)\n";
528 SetVersion ($DBversion);
531 $DBversion = "3.00.00.019";
532 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
533 $dbh->do("ALTER TABLE biblio MODIFY biblionumber INT(11) NOT NULL AUTO_INCREMENT");
534 $dbh->do("ALTER TABLE biblioitems MODIFY biblioitemnumber INT(11) NOT NULL AUTO_INCREMENT");
535 $dbh->do("ALTER TABLE items MODIFY itemnumber INT(11) NOT NULL AUTO_INCREMENT");
536 print "Upgrade to $DBversion done (made bib/item PKs auto_increment)\n";
537 SetVersion ($DBversion);
540 $DBversion = "3.00.00.020";
541 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
542 $dbh->do("ALTER TABLE deleteditems
543 DROP KEY `delitembarcodeidx`,
544 ADD KEY `delitembarcodeidx` (`barcode`)");
545 print "Upgrade to $DBversion done (dropped uniqueness of key on deleteditems.barcode)\n";
546 SetVersion ($DBversion);
549 $DBversion = "3.00.00.021";
550 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
551 $dbh->do("ALTER TABLE items CHANGE homebranch homebranch VARCHAR(10)");
552 $dbh->do("ALTER TABLE deleteditems CHANGE homebranch homebranch VARCHAR(10)");
553 $dbh->do("ALTER TABLE statistics CHANGE branch branch VARCHAR(10)");
554 $dbh->do("ALTER TABLE subscription CHANGE lastbranch lastbranch VARCHAR(10)");
555 print "Upgrade to $DBversion done (extended missed branchcode columns to 10 chars)\n";
556 SetVersion ($DBversion);
559 $DBversion = "3.00.00.022";
560 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
561 $dbh->do("ALTER TABLE items
562 ADD `damaged` tinyint(1) default NULL");
563 print "Upgrade to $DBversion done (adding damaged column to items table)\n";
564 SetVersion ($DBversion);
567 $DBversion = "3.00.00.023";
568 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
569 $dbh->do("INSERT INTO `systempreferences` (variable,value,options,explanation,type)
570 VALUES ('yuipath','http://yui.yahooapis.com/2.3.1/build','Insert the path to YUI libraries','','free')");
571 print "Upgrade to $DBversion done (adding new system preference for controlling YUI path)\n";
572 SetVersion ($DBversion);
575 $DBversion = "3.00.00.024";
576 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
577 $dbh->do("ALTER TABLE biblioitems CHANGE itemtype itemtype VARCHAR(10)");
578 print "Upgrade to $DBversion done (changing itemtype to (10))\n";
579 SetVersion ($DBversion);
582 $DBversion = "3.00.00.025";
583 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
584 $dbh->do("ALTER TABLE items ADD COLUMN itype VARCHAR(10)");
585 if(C4::Context->preference('item-level_itypes')){
586 $dbh->do('update items,biblioitems set items.itype=biblioitems.itemtype where items.biblionumber=biblioitems.biblionumber and itype is null');
588 print "Upgrade to $DBversion done (reintroduce items.itype - fill from itemtype)\n ";
589 SetVersion ($DBversion);
592 $DBversion = "3.00.00.026";
593 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
594 $dbh->do("INSERT INTO `systempreferences` (variable,value,options,explanation,type)
595 VALUES ('HomeOrHoldingBranch','homebranch','homebranch|holdingbranch','With independent branches turned on this decides whether to check the items holdingbranch or homebranch at circulatilon','choice')");
596 print "Upgrade to $DBversion done (adding new system preference for choosing whether homebranch or holdingbranch is checked in circulation)\n";
597 SetVersion ($DBversion);
600 $DBversion = "3.00.00.027";
601 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
602 $dbh->do("CREATE TABLE `marc_matchers` (
603 `matcher_id` int(11) NOT NULL auto_increment,
604 `code` varchar(10) NOT NULL default '',
605 `description` varchar(255) NOT NULL default '',
606 `record_type` varchar(10) NOT NULL default 'biblio',
607 `threshold` int(11) NOT NULL default 0,
608 PRIMARY KEY (`matcher_id`),
610 KEY `record_type` (`record_type`)
611 ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
612 $dbh->do("CREATE TABLE `matchpoints` (
613 `matcher_id` int(11) NOT NULL,
614 `matchpoint_id` int(11) NOT NULL auto_increment,
615 `search_index` varchar(30) NOT NULL default '',
616 `score` int(11) NOT NULL default 0,
617 PRIMARY KEY (`matchpoint_id`),
618 CONSTRAINT `matchpoints_ifbk_1` FOREIGN KEY (`matcher_id`)
619 REFERENCES `marc_matchers` (`matcher_id`) ON DELETE CASCADE ON UPDATE CASCADE
620 ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
621 $dbh->do("CREATE TABLE `matchpoint_components` (
622 `matchpoint_id` int(11) NOT NULL,
623 `matchpoint_component_id` int(11) NOT NULL auto_increment,
624 sequence int(11) NOT NULL default 0,
625 tag varchar(3) NOT NULL default '',
626 subfields varchar(40) NOT NULL default '',
627 offset int(4) NOT NULL default 0,
628 length int(4) NOT NULL default 0,
629 PRIMARY KEY (`matchpoint_component_id`),
630 KEY `by_sequence` (`matchpoint_id`, `sequence`),
631 CONSTRAINT `matchpoint_components_ifbk_1` FOREIGN KEY (`matchpoint_id`)
632 REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE
633 ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
634 $dbh->do("CREATE TABLE `matchpoint_component_norms` (
635 `matchpoint_component_id` int(11) NOT NULL,
636 `sequence` int(11) NOT NULL default 0,
637 `norm_routine` varchar(50) NOT NULL default '',
638 KEY `matchpoint_component_norms` (`matchpoint_component_id`, `sequence`),
639 CONSTRAINT `matchpoint_component_norms_ifbk_1` FOREIGN KEY (`matchpoint_component_id`)
640 REFERENCES `matchpoint_components` (`matchpoint_component_id`) ON DELETE CASCADE ON UPDATE CASCADE
641 ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
642 $dbh->do("CREATE TABLE `matcher_matchpoints` (
643 `matcher_id` int(11) NOT NULL,
644 `matchpoint_id` int(11) NOT NULL,
645 CONSTRAINT `matcher_matchpoints_ifbk_1` FOREIGN KEY (`matcher_id`)
646 REFERENCES `marc_matchers` (`matcher_id`) ON DELETE CASCADE ON UPDATE CASCADE,
647 CONSTRAINT `matcher_matchpoints_ifbk_2` FOREIGN KEY (`matchpoint_id`)
648 REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE
649 ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
650 $dbh->do("CREATE TABLE `matchchecks` (
651 `matcher_id` int(11) NOT NULL,
652 `matchcheck_id` int(11) NOT NULL auto_increment,
653 `source_matchpoint_id` int(11) NOT NULL,
654 `target_matchpoint_id` int(11) NOT NULL,
655 PRIMARY KEY (`matchcheck_id`),
656 CONSTRAINT `matcher_matchchecks_ifbk_1` FOREIGN KEY (`matcher_id`)
657 REFERENCES `marc_matchers` (`matcher_id`) ON DELETE CASCADE ON UPDATE CASCADE,
658 CONSTRAINT `matcher_matchchecks_ifbk_2` FOREIGN KEY (`source_matchpoint_id`)
659 REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE,
660 CONSTRAINT `matcher_matchchecks_ifbk_3` FOREIGN KEY (`target_matchpoint_id`)
661 REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE
662 ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
663 print "Upgrade to $DBversion done (added C4::Matcher serialization tables)\n ";
664 SetVersion ($DBversion);
667 $DBversion = "3.00.00.028";
668 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
669 $dbh->do("INSERT INTO `systempreferences` (variable,value,options,explanation,type)
670 VALUES ('canreservefromotherbranches','1','','With Independent branches on, can a user from one library reserve an item from another library','YesNo')");
671 print "Upgrade to $DBversion done (adding new system preference for changing reserve/holds behaviour with independent branches)\n";
672 SetVersion ($DBversion);
676 =item DropAllForeignKeys($table)
678 Drop all foreign keys of the table $table
682 sub DropAllForeignKeys {
684 # get the table description
685 my $sth = $dbh->prepare("SHOW CREATE TABLE $table");
687 my $vsc_structure = $sth->fetchrow;
688 # split on CONSTRAINT keyword
689 my @fks = split /CONSTRAINT /,$vsc_structure;
692 # isolate what is before FOREIGN KEY, if there is something, it's a foreign key to drop
693 $_ = /(.*) FOREIGN KEY.*/;
696 # we have found 1 foreign, drop it
697 $dbh->do("ALTER TABLE $table DROP FOREIGN KEY $id");
711 Transform the Koha version from a 4 parts string
712 to a number, with just 1 .
718 # remove the 3 last . to have a Perl number
719 $version =~ s/(.*\..*)\.(.*)\.(.*)/$1$2$3/;
724 set the DBversion in the systempreferences
728 my $kohaversion = TransformToNum(shift);
729 if (C4::Context->preference('Version')) {
730 my $finish=$dbh->prepare("UPDATE systempreferences SET value=? WHERE variable='Version'");
731 $finish->execute($kohaversion);
733 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')");
734 $finish->execute($kohaversion);
739 # Revision 1.172 2007/07/19 10:21:22 hdl