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`, `record_sequence`, `marc`, `record_type`, `status`,
434 `encoding`, `z3950random`, `marcxml`, `marcxml_old`)
435 SELECT `import_batch_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` USING (`z3950random`)");
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);
508 =item DropAllForeignKeys($table)
510 Drop all foreign keys of the table $table
514 sub DropAllForeignKeys {
516 # get the table description
517 my $sth = $dbh->prepare("SHOW CREATE TABLE $table");
519 my $vsc_structure = $sth->fetchrow;
520 # split on CONSTRAINT keyword
521 my @fks = split /CONSTRAINT /,$vsc_structure;
524 # isolate what is before FOREIGN KEY, if there is something, it's a foreign key to drop
525 $_ = /(.*) FOREIGN KEY.*/;
528 # we have found 1 foreign, drop it
529 $dbh->do("ALTER TABLE $table DROP FOREIGN KEY $id");
543 Transform the Koha version from a 4 parts string
544 to a number, with just 1 .
550 # remove the 3 last . to have a Perl number
551 $version =~ s/(.*\..*)\.(.*)\.(.*)/$1$2$3/;
556 set the DBversion in the systempreferences
560 my $kohaversion = TransformToNum(shift);
561 if (C4::Context->preference('Version')) {
562 my $finish=$dbh->prepare("UPDATE systempreferences SET value=? WHERE variable='Version'");
563 $finish->execute($kohaversion);
565 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')");
566 $finish->execute($kohaversion);
571 # Revision 1.172 2007/07/19 10:21:22 hdl