?  search:
summary | log | graphiclog1 | graphiclog2 | commit | commitdiff | tree | refs | edit | fork
blame | history | raw | HEAD
kohabug 2076 - add browser if necessary (DB rev 078)
[koha.git] / installer / data / mysql / updatedatabase.pl
blob33bfa8d976a713c836b61468e734a3ba93d5e847
1 #!/usr/bin/perl
2
3
4 # Database Updater
5 # This script checks for required updates to the database.
6
7 # Part of the Koha Library Software www.koha.org
8 # Licensed under the GPL.
9
10 # Bugs/ToDo:
11 # - Would also be a good idea to offer to do a backup at this time...
12
13 # NOTE: If you do something more than once in here, make it table driven.
14
15 # NOTE: Please keep the version in C4/Context.pm up-to-date!
16
17 use strict;
18
19 # CPAN modules
20 use DBI;
21 use Getopt::Long;
22 # Koha modules
23 use C4::Context;
24
25 use MARC::Record;
26 use MARC::File::XML ( BinaryEncoding => 'utf8' );
27
28 # FIXME - The user might be installing a new database, so can't rely
29 # on /etc/koha.conf anyway.
30
31 my $debug = 0;
32
33 my (
34 $sth, $sti,
35 $query,
36 %existingtables, # tables already in database
37 %types,
38 $table,
39 $column,
40 $type, $null, $key, $default, $extra,
41 $prefitem, # preference item in systempreferences table
42 );
43
44 my $silent;
45 GetOptions(
46 's' =>\$silent
47 );
48 my $dbh = C4::Context->dbh;
49 $|=1; # flushes output
50
51 =item
52
53 Deal with virtualshelves
54
55 =cut
56
57 my $DBversion = "3.00.00.001";
58 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
59 # update virtualshelves table to
60 #
61 $dbh->do("ALTER TABLE `bookshelf` RENAME `virtualshelves`");
62 $dbh->do("ALTER TABLE `shelfcontents` RENAME `virtualshelfcontents`");
63 $dbh->do("ALTER TABLE `virtualshelfcontents` ADD `biblionumber` INT( 11 ) NOT NULL");
64 $dbh->do("UPDATE `virtualshelfcontents` SET biblionumber=(SELECT biblionumber FROM items WHERE items.itemnumber=virtualshelfcontents.itemnumber)");
65 # drop all foreign keys : otherwise, we can't drop itemnumber field.
66 DropAllForeignKeys('virtualshelfcontents');
67 # create the new foreign keys (on biblionumber)
68 $dbh->do("ALTER TABLE `virtualshelfcontents` ADD FOREIGN KEY biblionumber_fk (biblionumber) REFERENCES biblio (biblionumber) ON UPDATE CASCADE ON DELETE CASCADE");
69 # re-create the foreign key on virtualshelf
70 $dbh->do("ALTER TABLE `virtualshelfcontents` ADD FOREIGN KEY shelfnumber_fk (shelfnumber) REFERENCES virtualshelves (shelfnumber) ON UPDATE CASCADE ON DELETE CASCADE");
71 # now we can drop the itemnumber column
72 $dbh->do("ALTER TABLE `virtualshelfcontents` DROP `itemnumber`");
73 print "Upgrade to $DBversion done (virtualshelves)\n";
74 SetVersion ($DBversion);
75 }
76
77
78 $DBversion = "3.00.00.002";
79 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
80 $dbh->do("DROP TABLE sessions");
81 $dbh->do("CREATE TABLE `sessions` (
82 `id` char(32) NOT NULL,
83 `a_session` text NOT NULL,
84 UNIQUE KEY `id` (`id`)
85 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;");
86 print "Upgrade to $DBversion done (sessions uses CGI::session, new table structure for sessions)\n";
87 SetVersion ($DBversion);
88 }
89
90
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')");
95 } else {
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')");
97 }
98 print "Upgrade to $DBversion done (adding ReservesNeedReturns systempref, in circulation)\n";
99 SetVersion ($DBversion);
100 }
101
102
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);
108 }
109
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;
117 ");
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;
126 ");
127 print "Upgrade to $DBversion done (adding tags and nozebra tables )\n";
128 SetVersion ($DBversion);
129 }
130
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);
136 }
137
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);
143 }
144
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);
151 }
152
153 $DBversion = "3.00.00.009";
154 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
155
156 # Create backups of call number columns
157 # in case default migration needs to be customized
158 #
159 # UPGRADE NOTE: temp_upg_biblioitems_call_num should be dropped
160 # after call numbers have been transformed to the new structure
161 #
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`,
167 `lcsort`, `ccode`
168 FROM `biblioitems`");
169
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`");
178
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
183 # cn_item = subclass
184 # cn_source = left null
185 # cn_sort = lcsort
186 #
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.
190 #
191 $dbh->do("UPDATE `biblioitems`
192 SET cn_class = SUBSTR(TRIM(CONCAT_WS(' ', `classification`, `dewey`)), 1, 30),
193 cn_item = subclass,
194 `cn_sort` = `lcsort`
195 ");
196
197 # Now drop the old call number columns
198 $dbh->do("ALTER TABLE `biblioitems` DROP COLUMN `classification`,
199 DROP COLUMN `dewey`,
200 DROP COLUMN `subclass`,
201 DROP COLUMN `lcsort`,
202 DROP COLUMN `ccode`");
203
204 # deletedbiblio changes
205 $dbh->do("ALTER TABLE `deletedbiblio` ALTER COLUMN `frameworkcode` SET DEFAULT '',
206 DROP COLUMN `marc`,
207 ADD `datecreated` DATE NOT NULL AFTER `timestamp`");
208 $dbh->do("UPDATE deletedbiblio SET datecreated = timestamp");
209
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` BLOB,
221 ADD `cn_source` VARCHAR(10) DEFAULT NULL AFTER `url`,
222 ADD `cn_class` VARCHAR(30) DEFAULT NULL AFTER `cn_source`,
223 ADD `cn_item` VARCHAR(10) DEFAULT NULL AFTER `cn_class`,
224 ADD `cn_suffix` VARCHAR(10) DEFAULT NULL AFTER `cn_item`,
225 ADD `cn_sort` VARCHAR(30) DEFAULT NULL AFTER `cn_suffix`,
226 ADD `totalissues` INT(10) AFTER `cn_sort`,
227 ADD KEY `isbn` (`isbn`),
228 ADD KEY `publishercode` (`publishercode`)
229 ");
230
231 $dbh->do("UPDATE `deletedbiblioitems`
232 SET `cn_class` = SUBSTR(TRIM(CONCAT_WS(' ', `classification`, `dewey`)), 1, 30),
233 `cn_item` = `subclass`,
234 `cn_sort` = `lcsort`
235 ");
236 $dbh->do("ALTER TABLE `deletedbiblioitems`
237 DROP COLUMN `classification`,
238 DROP COLUMN `dewey`,
239 DROP COLUMN `subclass`,
240 DROP COLUMN `lcsort`,
241 DROP COLUMN `ccode`
242 ");
243
244 # deleteditems changes
245 $dbh->do("ALTER TABLE `deleteditems`
246 MODIFY `barcode` VARCHAR(20) DEFAULT NULL,
247 MODIFY `price` DECIMAL(8,2) DEFAULT NULL,
248 MODIFY `replacementprice` DECIMAL(8,2) DEFAULT NULL,
249 DROP `bulk`,
250 MODIFY `itemcallnumber` VARCHAR(30) DEFAULT NULL AFTER `wthdrawn`,
251 MODIFY `holdingbranch` VARCHAR(10) DEFAULT NULL,
252 DROP `interim`,
253 MODIFY `timestamp` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP AFTER `paidfor`,
254 DROP `cutterextra`,
255 ADD `cn_source` VARCHAR(10) DEFAULT NULL AFTER `onloan`,
256 ADD `cn_sort` VARCHAR(30) DEFAULT NULL AFTER `cn_source`,
257 ADD `ccode` VARCHAR(10) DEFAULT NULL AFTER `cn_sort`,
258 ADD `materials` VARCHAR(10) DEFAULT NULL AFTER `ccode`,
259 ADD `uri` VARCHAR(255) DEFAULT NULL AFTER `materials`,
260 MODIFY `marc` LONGBLOB AFTER `uri`,
261 DROP KEY `barcode`,
262 DROP KEY `itembarcodeidx`,
263 DROP KEY `itembinoidx`,
264 DROP KEY `itembibnoidx`,
265 ADD UNIQUE KEY `delitembarcodeidx` (`barcode`),
266 ADD KEY `delitembinoidx` (`biblioitemnumber`),
267 ADD KEY `delitembibnoidx` (`biblionumber`),
268 ADD KEY `delhomebranch` (`homebranch`),
269 ADD KEY `delholdingbranch` (`holdingbranch`)");
270 $dbh->do("UPDATE deleteditems SET `ccode` = `itype`");
271 $dbh->do("ALTER TABLE deleteditems DROP `itype`");
272 $dbh->do("UPDATE `deleteditems` SET `cn_sort` = `itemcallnumber`");
273
274 # items changes
275 $dbh->do("ALTER TABLE `items` ADD `cn_source` VARCHAR(10) DEFAULT NULL AFTER `onloan`,
276 ADD `cn_sort` VARCHAR(30) DEFAULT NULL AFTER `cn_source`,
277 ADD `ccode` VARCHAR(10) DEFAULT NULL AFTER `cn_sort`,
278 ADD `materials` VARCHAR(10) DEFAULT NULL AFTER `ccode`,
279 ADD `uri` VARCHAR(255) DEFAULT NULL AFTER `materials`
280 ");
281 $dbh->do("ALTER TABLE `items`
282 DROP KEY `itembarcodeidx`,
283 ADD UNIQUE KEY `itembarcodeidx` (`barcode`)");
284
285 # map items.itype to items.ccode and
286 # set cn_sort to itemcallnumber -- as with biblioitems.cn_sort,
287 # will have to be subsequently updated per user's default
288 # classification scheme
289 $dbh->do("UPDATE `items` SET `cn_sort` = `itemcallnumber`,
290 `ccode` = `itype`");
291
292 $dbh->do("ALTER TABLE `items` DROP `cutterextra`,
293 DROP `itype`");
294
295 print "Upgrade to $DBversion done (major changes to biblio, biblioitems, items, and deleted* versions of same\n";
296 SetVersion ($DBversion);
297 }
298
299 $DBversion = "3.00.00.010";
300 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
301 $dbh->do("CREATE INDEX `userid` ON borrowers (`userid`) ");
302 print "Upgrade to $DBversion done (userid index added)\n";
303 SetVersion ($DBversion);
304 }
305
306 $DBversion = "3.00.00.011";
307 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
308 $dbh->do("ALTER TABLE `branchcategories` CHANGE `categorycode` `categorycode` char(10) ");
309 $dbh->do("ALTER TABLE `branchcategories` CHANGE `categoryname` `categoryname` varchar(32) ");
310 $dbh->do("ALTER TABLE `branchcategories` ADD COLUMN `categorytype` varchar(16) ");
311 $dbh->do("UPDATE `branchcategories` SET `categorytype` = 'properties'");
312 $dbh->do("ALTER TABLE `branchrelations` CHANGE `categorycode` `categorycode` char(10) ");
313 print "Upgrade to $DBversion done (added branchcategory type)\n";
314 SetVersion ($DBversion);
315 }
316
317 $DBversion = "3.00.00.012";
318 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
319 $dbh->do("CREATE TABLE `class_sort_rules` (
320 `class_sort_rule` varchar(10) NOT NULL default '',
321 `description` mediumtext,
322 `sort_routine` varchar(30) NOT NULL default '',
323 PRIMARY KEY (`class_sort_rule`),
324 UNIQUE KEY `class_sort_rule_idx` (`class_sort_rule`)
325 ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
326 $dbh->do("CREATE TABLE `class_sources` (
327 `cn_source` varchar(10) NOT NULL default '',
328 `description` mediumtext,
329 `used` tinyint(4) NOT NULL default 0,
330 `class_sort_rule` varchar(10) NOT NULL default '',
331 PRIMARY KEY (`cn_source`),
332 UNIQUE KEY `cn_source_idx` (`cn_source`),
333 KEY `used_idx` (`used`),
334 CONSTRAINT `class_source_ibfk_1` FOREIGN KEY (`class_sort_rule`)
335 REFERENCES `class_sort_rules` (`class_sort_rule`)
336 ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
337 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type)
338 VALUES('DefaultClassificationSource','ddc',
339 'Default classification scheme used by the collection. E.g., Dewey, LCC, etc.', NULL,'free')");
340 $dbh->do("INSERT INTO `class_sort_rules` (`class_sort_rule`, `description`, `sort_routine`) VALUES
341 ('dewey', 'Default filing rules for DDC', 'Dewey'),
342 ('lcc', 'Default filing rules for LCC', 'LCC'),
343 ('generic', 'Generic call number filing rules', 'Generic')");
344 $dbh->do("INSERT INTO `class_sources` (`cn_source`, `description`, `used`, `class_sort_rule`) VALUES
345 ('ddc', 'Dewey Decimal Classification', 1, 'dewey'),
346 ('lcc', 'Library of Congress Classification', 1, 'lcc'),
347 ('udc', 'Universal Decimal Classification', 0, 'generic'),
348 ('sudocs', 'SuDoc Classification (U.S. GPO)', 0, 'generic'),
349 ('z', 'Other/Generic Classification Scheme', 0, 'generic')");
350 print "Upgrade to $DBversion done (classification sources added)\n";
351 SetVersion ($DBversion);
352 }
353
354 $DBversion = "3.00.00.013";
355 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
356 $dbh->do("CREATE TABLE `import_batches` (
357 `import_batch_id` int(11) NOT NULL auto_increment,
358 `template_id` int(11) default NULL,
359 `branchcode` varchar(10) default NULL,
360 `num_biblios` int(11) NOT NULL default 0,
361 `num_items` int(11) NOT NULL default 0,
362 `upload_timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP,
363 `overlay_action` enum('replace', 'create_new', 'use_template') NOT NULL default 'create_new',
364 `import_status` enum('staging', 'staged', 'importing', 'imported', 'reverting', 'reverted', 'cleaned') NOT NULL default 'staging',
365 `batch_type` enum('batch', 'z3950') NOT NULL default 'batch',
366 `file_name` varchar(100),
367 `comments` mediumtext,
368 PRIMARY KEY (`import_batch_id`),
369 KEY `branchcode` (`branchcode`)
370 ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
371 $dbh->do("CREATE TABLE `import_records` (
372 `import_record_id` int(11) NOT NULL auto_increment,
373 `import_batch_id` int(11) NOT NULL,
374 `branchcode` varchar(10) default NULL,
375 `record_sequence` int(11) NOT NULL default 0,
376 `upload_timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP,
377 `import_date` DATE default NULL,
378 `marc` longblob NOT NULL,
379 `marcxml` longtext NOT NULL,
380 `marcxml_old` longtext NOT NULL,
381 `record_type` enum('biblio', 'auth', 'holdings') NOT NULL default 'biblio',
382 `overlay_status` enum('no_match', 'auto_match', 'manual_match', 'match_applied') NOT NULL default 'no_match',
383 `status` enum('error', 'staged', 'imported', 'reverted', 'items_reverted') NOT NULL default 'staged',
384 `import_error` mediumtext,
385 `encoding` varchar(40) NOT NULL default '',
386 `z3950random` varchar(40) default NULL,
387 PRIMARY KEY (`import_record_id`),
388 CONSTRAINT `import_records_ifbk_1` FOREIGN KEY (`import_batch_id`)
389 REFERENCES `import_batches` (`import_batch_id`) ON DELETE CASCADE ON UPDATE CASCADE,
390 KEY `branchcode` (`branchcode`),
391 KEY `batch_sequence` (`import_batch_id`, `record_sequence`)
392 ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
393 $dbh->do("CREATE TABLE `import_record_matches` (
394 `import_record_id` int(11) NOT NULL,
395 `candidate_match_id` int(11) NOT NULL,
396 `score` int(11) NOT NULL default 0,
397 CONSTRAINT `import_record_matches_ibfk_1` FOREIGN KEY (`import_record_id`)
398 REFERENCES `import_records` (`import_record_id`) ON DELETE CASCADE ON UPDATE CASCADE,
399 KEY `record_score` (`import_record_id`, `score`)
400 ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
401 $dbh->do("CREATE TABLE `import_biblios` (
402 `import_record_id` int(11) NOT NULL,
403 `matched_biblionumber` int(11) default NULL,
404 `control_number` varchar(25) default NULL,
405 `original_source` varchar(25) default NULL,
406 `title` varchar(128) default NULL,
407 `author` varchar(80) default NULL,
408 `isbn` varchar(14) default NULL,
409 `issn` varchar(9) default NULL,
410 `has_items` tinyint(1) NOT NULL default 0,
411 CONSTRAINT `import_biblios_ibfk_1` FOREIGN KEY (`import_record_id`)
412 REFERENCES `import_records` (`import_record_id`) ON DELETE CASCADE ON UPDATE CASCADE,
413 KEY `matched_biblionumber` (`matched_biblionumber`),
414 KEY `title` (`title`),
415 KEY `isbn` (`isbn`)
416 ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
417 $dbh->do("CREATE TABLE `import_items` (
418 `import_items_id` int(11) NOT NULL auto_increment,
419 `import_record_id` int(11) NOT NULL,
420 `itemnumber` int(11) default NULL,
421 `branchcode` varchar(10) default NULL,
422 `status` enum('error', 'staged', 'imported', 'reverted') NOT NULL default 'staged',
423 `marcxml` longtext NOT NULL,
424 `import_error` mediumtext,
425 PRIMARY KEY (`import_items_id`),
426 CONSTRAINT `import_items_ibfk_1` FOREIGN KEY (`import_record_id`)
427 REFERENCES `import_records` (`import_record_id`) ON DELETE CASCADE ON UPDATE CASCADE,
428 KEY `itemnumber` (`itemnumber`),
429 KEY `branchcode` (`branchcode`)
430 ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
431
432 $dbh->do("INSERT INTO `import_batches`
433 (`overlay_action`, `import_status`, `batch_type`, `file_name`)
434 SELECT distinct 'create_new', 'staged', 'z3950', `file`
435 FROM `marc_breeding`");
436
437 $dbh->do("INSERT INTO `import_records`
438 (`import_batch_id`, `import_record_id`, `record_sequence`, `marc`, `record_type`, `status`,
439 `encoding`, `z3950random`, `marcxml`, `marcxml_old`)
440 SELECT `import_batch_id`, `id`, 1, `marc`, 'biblio', 'staged', `encoding`, `z3950random`, '', ''
441 FROM `marc_breeding`
442 JOIN `import_batches` ON (`file_name` = `file`)");
443
444 $dbh->do("INSERT INTO `import_biblios`
445 (`import_record_id`, `title`, `author`, `isbn`)
446 SELECT `import_record_id`, `title`, `author`, `isbn`
447 FROM `marc_breeding`
448 JOIN `import_records` ON (`import_record_id` = `id`)");
449
450 $dbh->do("UPDATE `import_batches`
451 SET `num_biblios` = (
452 SELECT COUNT(*)
453 FROM `import_records`
454 WHERE `import_batch_id` = `import_batches`.`import_batch_id`
455 )");
456
457 $dbh->do("DROP TABLE `marc_breeding`");
458
459 print "Upgrade to $DBversion done (import_batches et al. added)\n";
460 SetVersion ($DBversion);
461 }
462
463 $DBversion = "3.00.00.014";
464 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
465 $dbh->do("ALTER TABLE subscription ADD lastbranch VARCHAR(4)");
466 print "Upgrade to $DBversion done (userid index added)\n";
467 SetVersion ($DBversion);
468 }
469
470 $DBversion = "3.00.00.015";
471 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
472 $dbh->do("CREATE TABLE `saved_sql` (
473 `id` int(11) NOT NULL auto_increment,
474 `borrowernumber` int(11) default NULL,
475 `date_created` datetime default NULL,
476 `last_modified` datetime default NULL,
477 `savedsql` text,
478 `last_run` datetime default NULL,
479 `report_name` varchar(255) default NULL,
480 `type` varchar(255) default NULL,
481 `notes` text,
482 PRIMARY KEY (`id`),
483 KEY boridx (`borrowernumber`)
484 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;");
485 $dbh->do("CREATE TABLE `saved_reports` (
486 `id` int(11) NOT NULL auto_increment,
487 `report_id` int(11) default NULL,
488 `report` longtext,
489 `date_run` datetime default NULL,
490 PRIMARY KEY (`id`)
491 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;");
492 print "Upgrade to $DBversion done (saved_sql and saved_reports added)\n";
493 SetVersion ($DBversion);
494 }
495
496 $DBversion = "3.00.00.016";
497 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
498 $dbh->do(" CREATE TABLE reports_dictionary (
499 id int(11) NOT NULL auto_increment,
500 name varchar(255) default NULL,
501 description text,
502 date_created datetime default NULL,
503 date_modified datetime default NULL,
504 saved_sql text,
505 area int(11) default NULL,
506 PRIMARY KEY (id)
507 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ");
508 print "Upgrade to $DBversion done (reports_dictionary) added)\n";
509 SetVersion ($DBversion);
510 }
511
512 $DBversion = "3.00.00.017";
513 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
514 $dbh->do("ALTER TABLE action_logs DROP PRIMARY KEY");
515 $dbh->do("ALTER TABLE action_logs ADD KEY timestamp (timestamp,user)");
516 $dbh->do("ALTER TABLE action_logs ADD action_id INT(11) NOT NULL FIRST");
517 $dbh->do("UPDATE action_logs SET action_id = if (\@a, \@a:=\@a+1, \@a:=1)");
518 $dbh->do("ALTER TABLE action_logs MODIFY action_id INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY");
519 print "Upgrade to $DBversion done (added column to action_logs)\n";
520 SetVersion ($DBversion);
521 }
522
523 $DBversion = "3.00.00.018";
524 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
525 $dbh->do("ALTER TABLE `zebraqueue`
526 ADD `done` INT NOT NULL DEFAULT '0',
527 ADD `time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ;
528 ");
529 print "Upgrade to $DBversion done (adding timestamp and done columns to zebraque table to improve problem tracking) added)\n";
530 SetVersion ($DBversion);
531 }
532
533 $DBversion = "3.00.00.019";
534 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
535 $dbh->do("ALTER TABLE biblio MODIFY biblionumber INT(11) NOT NULL AUTO_INCREMENT");
536 $dbh->do("ALTER TABLE biblioitems MODIFY biblioitemnumber INT(11) NOT NULL AUTO_INCREMENT");
537 $dbh->do("ALTER TABLE items MODIFY itemnumber INT(11) NOT NULL AUTO_INCREMENT");
538 print "Upgrade to $DBversion done (made bib/item PKs auto_increment)\n";
539 SetVersion ($DBversion);
540 }
541
542 $DBversion = "3.00.00.020";
543 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
544 $dbh->do("ALTER TABLE deleteditems
545 DROP KEY `delitembarcodeidx`,
546 ADD KEY `delitembarcodeidx` (`barcode`)");
547 print "Upgrade to $DBversion done (dropped uniqueness of key on deleteditems.barcode)\n";
548 SetVersion ($DBversion);
549 }
550
551 $DBversion = "3.00.00.021";
552 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
553 $dbh->do("ALTER TABLE items CHANGE homebranch homebranch VARCHAR(10)");
554 $dbh->do("ALTER TABLE deleteditems CHANGE homebranch homebranch VARCHAR(10)");
555 $dbh->do("ALTER TABLE statistics CHANGE branch branch VARCHAR(10)");
556 $dbh->do("ALTER TABLE subscription CHANGE lastbranch lastbranch VARCHAR(10)");
557 print "Upgrade to $DBversion done (extended missed branchcode columns to 10 chars)\n";
558 SetVersion ($DBversion);
559 }
560
561 $DBversion = "3.00.00.022";
562 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
563 $dbh->do("ALTER TABLE items
564 ADD `damaged` tinyint(1) default NULL");
565 print "Upgrade to $DBversion done (adding damaged column to items table)\n";
566 SetVersion ($DBversion);
567 }
568
569 $DBversion = "3.00.00.023";
570 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
571 $dbh->do("INSERT INTO `systempreferences` (variable,value,options,explanation,type)
572 VALUES ('yuipath','http://yui.yahooapis.com/2.3.1/build','Insert the path to YUI libraries','','free')");
573 print "Upgrade to $DBversion done (adding new system preference for controlling YUI path)\n";
574 SetVersion ($DBversion);
575 }
576
577 $DBversion = "3.00.00.024";
578 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
579 $dbh->do("ALTER TABLE biblioitems CHANGE itemtype itemtype VARCHAR(10)");
580 print "Upgrade to $DBversion done (changing itemtype to (10))\n";
581 SetVersion ($DBversion);
582 }
583
584 $DBversion = "3.00.00.025";
585 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
586 $dbh->do("ALTER TABLE items ADD COLUMN itype VARCHAR(10)");
587 if(C4::Context->preference('item-level_itypes')){
588 $dbh->do('update items,biblioitems set items.itype=biblioitems.itemtype where items.biblionumber=biblioitems.biblionumber and itype is null');
589 }
590 print "Upgrade to $DBversion done (reintroduce items.itype - fill from itemtype)\n ";
591 SetVersion ($DBversion);
592 }
593
594 $DBversion = "3.00.00.026";
595 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
596 $dbh->do("INSERT INTO `systempreferences` (variable,value,options,explanation,type)
597 VALUES ('HomeOrHoldingBranch','homebranch','homebranch|holdingbranch','With independent branches turned on this decides whether to check the items holdingbranch or homebranch at circulatilon','choice')");
598 print "Upgrade to $DBversion done (adding new system preference for choosing whether homebranch or holdingbranch is checked in circulation)\n";
599 SetVersion ($DBversion);
600 }
601
602 $DBversion = "3.00.00.027";
603 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
604 $dbh->do("CREATE TABLE `marc_matchers` (
605 `matcher_id` int(11) NOT NULL auto_increment,
606 `code` varchar(10) NOT NULL default '',
607 `description` varchar(255) NOT NULL default '',
608 `record_type` varchar(10) NOT NULL default 'biblio',
609 `threshold` int(11) NOT NULL default 0,
610 PRIMARY KEY (`matcher_id`),
611 KEY `code` (`code`),
612 KEY `record_type` (`record_type`)
613 ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
614 $dbh->do("CREATE TABLE `matchpoints` (
615 `matcher_id` int(11) NOT NULL,
616 `matchpoint_id` int(11) NOT NULL auto_increment,
617 `search_index` varchar(30) NOT NULL default '',
618 `score` int(11) NOT NULL default 0,
619 PRIMARY KEY (`matchpoint_id`),
620 CONSTRAINT `matchpoints_ifbk_1` FOREIGN KEY (`matcher_id`)
621 REFERENCES `marc_matchers` (`matcher_id`) ON DELETE CASCADE ON UPDATE CASCADE
622 ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
623 $dbh->do("CREATE TABLE `matchpoint_components` (
624 `matchpoint_id` int(11) NOT NULL,
625 `matchpoint_component_id` int(11) NOT NULL auto_increment,
626 sequence int(11) NOT NULL default 0,
627 tag varchar(3) NOT NULL default '',
628 subfields varchar(40) NOT NULL default '',
629 offset int(4) NOT NULL default 0,
630 length int(4) NOT NULL default 0,
631 PRIMARY KEY (`matchpoint_component_id`),
632 KEY `by_sequence` (`matchpoint_id`, `sequence`),
633 CONSTRAINT `matchpoint_components_ifbk_1` FOREIGN KEY (`matchpoint_id`)
634 REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE
635 ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
636 $dbh->do("CREATE TABLE `matchpoint_component_norms` (
637 `matchpoint_component_id` int(11) NOT NULL,
638 `sequence` int(11) NOT NULL default 0,
639 `norm_routine` varchar(50) NOT NULL default '',
640 KEY `matchpoint_component_norms` (`matchpoint_component_id`, `sequence`),
641 CONSTRAINT `matchpoint_component_norms_ifbk_1` FOREIGN KEY (`matchpoint_component_id`)
642 REFERENCES `matchpoint_components` (`matchpoint_component_id`) ON DELETE CASCADE ON UPDATE CASCADE
643 ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
644 $dbh->do("CREATE TABLE `matcher_matchpoints` (
645 `matcher_id` int(11) NOT NULL,
646 `matchpoint_id` int(11) NOT NULL,
647 CONSTRAINT `matcher_matchpoints_ifbk_1` FOREIGN KEY (`matcher_id`)
648 REFERENCES `marc_matchers` (`matcher_id`) ON DELETE CASCADE ON UPDATE CASCADE,
649 CONSTRAINT `matcher_matchpoints_ifbk_2` FOREIGN KEY (`matchpoint_id`)
650 REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE
651 ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
652 $dbh->do("CREATE TABLE `matchchecks` (
653 `matcher_id` int(11) NOT NULL,
654 `matchcheck_id` int(11) NOT NULL auto_increment,
655 `source_matchpoint_id` int(11) NOT NULL,
656 `target_matchpoint_id` int(11) NOT NULL,
657 PRIMARY KEY (`matchcheck_id`),
658 CONSTRAINT `matcher_matchchecks_ifbk_1` FOREIGN KEY (`matcher_id`)
659 REFERENCES `marc_matchers` (`matcher_id`) ON DELETE CASCADE ON UPDATE CASCADE,
660 CONSTRAINT `matcher_matchchecks_ifbk_2` FOREIGN KEY (`source_matchpoint_id`)
661 REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE,
662 CONSTRAINT `matcher_matchchecks_ifbk_3` FOREIGN KEY (`target_matchpoint_id`)
663 REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE
664 ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
665 print "Upgrade to $DBversion done (added C4::Matcher serialization tables)\n ";
666 SetVersion ($DBversion);
667 }
668
669 $DBversion = "3.00.00.028";
670 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
671 $dbh->do("INSERT INTO `systempreferences` (variable,value,options,explanation,type)
672 VALUES ('canreservefromotherbranches','1','','With Independent branches on, can a user from one library reserve an item from another library','YesNo')");
673 print "Upgrade to $DBversion done (adding new system preference for changing reserve/holds behaviour with independent branches)\n";
674 SetVersion ($DBversion);
675 }
676
677
678 $DBversion = "3.00.00.029";
679 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
680 $dbh->do("ALTER TABLE `import_batches` ADD `matcher_id` int(11) NULL AFTER `import_batch_id`");
681 print "Upgrade to $DBversion done (adding matcher_id to import_batches)\n";
682 SetVersion ($DBversion);
683 }
684
685 $DBversion = "3.00.00.030";
686 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
687 $dbh->do("
688 CREATE TABLE services_throttle (
689 service_type varchar(10) NOT NULL default '',
690 service_count varchar(45) default NULL,
691 PRIMARY KEY (service_type)
692 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
693 ");
694 $dbh->do("INSERT INTO `systempreferences` (variable,value,options,explanation,type)
695 VALUES ('FRBRizeEditions',0,'','If ON, Koha will query one or more ISBN web services for associated ISBNs and display an Editions tab on the details pages','YesNo')");
696 $dbh->do("INSERT INTO `systempreferences` (variable,value,options,explanation,type)
697 VALUES ('XISBN',0,'','Use with FRBRizeEditions. If ON, Koha will use the OCLC xISBN web service in the Editions tab on the detail pages. See: http://www.worldcat.org/affiliate/webservices/xisbn/app.jsp','YesNo')");
698 $dbh->do("INSERT INTO `systempreferences` (variable,value,options,explanation,type)
699 VALUES ('OCLCAffiliateID','','','Use with FRBRizeEditions and XISBN. You can sign up for an AffiliateID here: http://www.worldcat.org/wcpa/do/AffiliateUserServices?method=initSelfRegister','free')");
700 $dbh->do("INSERT INTO `systempreferences` (variable,value,options,explanation,type)
701 VALUES ('XISBNDailyLimit',499,'','The xISBN Web service is free for non-commercial use when usage does not exceed 500 requests per day','free')");
702 $dbh->do("INSERT INTO `systempreferences` (variable,value,options,explanation,type)
703 VALUES ('PINESISBN',0,'','Use with FRBRizeEditions. If ON, Koha will use PINES OISBN web service in the Editions tab on the detail pages.','YesNo')");
704 $dbh->do("INSERT INTO `systempreferences` (variable,value,options,explanation,type)
705 VALUES ('ThingISBN',0,'','Use with FRBRizeEditions. If ON, Koha will use the ThingISBN web service in the Editions tab on the detail pages.','YesNo')");
706 print "Upgrade to $DBversion done (adding services throttle table and sysprefs for xISBN)\n";
707 SetVersion ($DBversion);
708 }
709
710 $DBversion = "3.00.00.031";
711 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
712
713 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('QueryStemming',1,'If ON, enables query stemming',NULL,'YesNo')");
714 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('QueryFuzzy',1,'If ON, enables fuzzy option for searches',NULL,'YesNo')");
715 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('QueryWeightFields',1,'If ON, enables field weighting',NULL,'YesNo')");
716 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('WebBasedSelfCheck',0,'If ON, enables the web-based self-check system',NULL,'YesNo')");
717 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('numSearchResults',20,'Specify the maximum number of results to display on a page of results',NULL,'free')");
718 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('OPACnumSearchResults',20,'Specify the maximum number of results to display on a page of results',NULL,'free')");
719 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('maxItemsInSearchResults',20,'Specify the maximum number of items to display for each result on a page of results',NULL,'free')");
720 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('defaultSortField',NULL,'Specify the default field used for sorting','relevance|popularity|call_number|pubdate|acqdate|title|author','Choice')");
721 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('defaultSortOrder',NULL,'Specify the default sort order','asc|dsc|az|za','Choice')");
722 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('OPACdefaultSortField',NULL,'Specify the default field used for sorting','relevance|popularity|call_number|pubdate|acqdate|title|author','Choice')");
723 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('OPACdefaultSortOrder',NULL,'Specify the default sort order','asc|dsc|za|az','Choice')");
724 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('staffClientBaseURL','','Specify the base URL of the staff client',NULL,'free')");
725 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('minPasswordLength',3,'Specify the minimum length of a patron/staff password',NULL,'free')");
726 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('noItemTypeImages',0,'If ON, disables item-type images',NULL,'YesNo')");
727 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('noOPACHolds',0,'If ON, disables holds globally',NULL,'YesNo')");
728 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('emailLibrarianWhenHoldIsPlaced',0,'If ON, emails the librarian whenever a hold is placed',NULL,'YesNo')");
729 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('holdCancelLength','','Specify how many days before a hold is canceled',NULL,'free')");
730 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('libraryAddress','','The address to use for printing receipts, overdues, etc. if different than physical address',NULL,'free')");
731 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('finesMode','test','Choose the fines mode, test or production','test|production','Choice')");
732 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('globalDueDate','','If set, allows a global static due date for all checkouts',NULL,'free')");
733 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('itemBarcodeInputFilter','','If set, allows specification of a item barcode input filter','cuecat','Choice')");
734 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('singleBranchMode',0,'Operate in Single-branch mode, hide branch selection in the OPAC',NULL,'YesNo')");
735 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('URLLinkText','','Text to display as the link anchor in the OPAC',NULL,'free')");
736 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('noOPACUserLogin',0,'If ON, disables the OPAC User Login',NULL,'YesNo')");
737 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('OPACSubscriptionDisplay','economical','Specify how to display subscription information in the OPAC','economical|off|full','Choice')");
738 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('OPACDisplayExtendedSubInfo',1,'If ON, extended subscription information is displayed in the OPAC',NULL,'YesNo')");
739 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('OPACViewOthersSuggestions',0,'If ON, allows all suggestions to be displayed in the OPAC',NULL,'YesNo')");
740 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('OPACURLOpenInNewWindow',0,'If ON, URLs in the OPAC open in a new window',NULL,'YesNo')");
741 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('OPACUserCSS',0,'Add CSS to be included in the OPAC',NULL,'free')");
742 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('emailPurchaseSuggestions',0,'If ON, patron suggestions are emailed rather than managed in Acquisitions',NULL,'YesNo')");
743
744 print "Upgrade to $DBversion done (adding additional system preference)\n";
745 SetVersion ($DBversion);
746 }
747
748 $DBversion = "3.00.00.032";
749 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
750 $dbh->do("UPDATE `marc_subfield_structure` SET `kohafield` = 'items.wthdrawn' WHERE `kohafield` = 'items.withdrawn'");
751 print "Upgrade to $DBversion done (fixed MARC framework references to items.withdrawn)\n";
752 SetVersion ($DBversion);
753 }
754
755 $DBversion = "3.00.00.033";
756 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
757 $dbh->do("INSERT INTO `userflags` VALUES(17,'staffaccess','Modify login / permissions for staff users',0)");
758 print "Upgrade to $DBversion done (Adding permissions flag for staff member access modification. )\n";
759 SetVersion ($DBversion);
760 }
761
762 $DBversion = "3.00.00.034";
763 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
764 $dbh->do("ALTER TABLE `virtualshelves` ADD COLUMN `sortfield` VARCHAR(16) ");
765 print "Upgrade to $DBversion done (Adding sortfield for Virtual Shelves. )\n";
766 SetVersion ($DBversion);
767 }
768
769 $DBversion = "3.00.00.035";
770 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
771 $dbh->do("UPDATE marc_subfield_structure
772 SET authorised_value = 'cn_source'
773 WHERE kohafield IN ('items.cn_source', 'biblioitems.cn_source')
774 AND (authorised_value is NULL OR authorised_value = '')");
775 print "Upgrade to $DBversion done (MARC frameworks: make classification source a drop-down)\n";
776 SetVersion ($DBversion);
777 }
778
779 $DBversion = "3.00.00.036";
780 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
781 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('OPACItemsResultsDisplay','statuses','statuses : show only the status of items in result list. itemdisplay : show full location of items (branch+location+callnumber) as in staff interface','statuses|itemdetails','Choice');");
782 print "Upgrade to $DBversion done (OPACItemsResultsDisplay systempreference added)\n";
783 SetVersion ($DBversion);
784 }
785
786 $DBversion = "3.00.00.037";
787 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
788 $dbh->do("ALTER TABLE `borrowers` ADD COLUMN `altcontactfirstname` varchar(255)");
789 $dbh->do("ALTER TABLE `borrowers` ADD COLUMN `altcontactsurname` varchar(255)");
790 $dbh->do("ALTER TABLE `borrowers` ADD COLUMN `altcontactaddress1` varchar(255)");
791 $dbh->do("ALTER TABLE `borrowers` ADD COLUMN `altcontactaddress2` varchar(255)");
792 $dbh->do("ALTER TABLE `borrowers` ADD COLUMN `altcontactaddress3` varchar(255)");
793 $dbh->do("ALTER TABLE `borrowers` ADD COLUMN `altcontactzipcode` varchar(50)");
794 $dbh->do("ALTER TABLE `borrowers` ADD COLUMN `altcontactphone` varchar(50)");
795 print "Upgrade to $DBversion done (Adding Alternative Contact Person information to borrowers table)\n";
796 SetVersion ($DBversion);
797 }
798
799 $DBversion = "3.00.00.038";
800 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
801 $dbh->do("UPDATE `systempreferences` set explanation='Choose the fines mode, off, test (emails admin report) or production (accrue overdue fines). Requires fines cron script' , options='off|test|production' where variable='finesMode'");
802 $dbh->do("DELETE FROM `systempreferences` WHERE variable='hideBiblioNumber'");
803 print "Upgrade to $DBversion done ('alter finesMode systempreference, remove superfluous syspref.')\n";
804 SetVersion ($DBversion);
805 }
806
807 $DBversion = "3.00.00.039";
808 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
809 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('uppercasesurnames',0,'If ON, surnames are converted to upper case in patron entry form',NULL,'YesNo')");
810 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('CircControl','ItemHomeLibrary','Specify the agency that controls the circulation and fines policy','PickupLibrary|PatronLibrary|ItemHomeLibrary','Choice')");
811 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('finesCalendar','noFinesWhenClosed','Specify whether to use the Calendar in calculating duedates and fines','ignoreCalendar|noFinesWhenClosed','Choice')");
812 $dbh->do("DELETE FROM `systempreferences` WHERE variable='HomeOrHoldingBranch'");
813 print "Upgrade to $DBversion done ('add circ sysprefs CircControl, finesCalendar, and uppercasesurnames, and delete HomeOrHoldingBranch.')\n";
814 SetVersion ($DBversion);
815 }
816
817 $DBversion = "3.00.00.040";
818 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
819 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('previousIssuesDefaultSortOrder','asc','Specify the sort order of Previous Issues on the circulation page','asc|desc','Choice')");
820 $dbh->do("INSERT INTO systempreferences (variable,value,explanation,options,type) VALUES('todaysIssuesDefaultSortOrder','desc','Specify the sort order of Todays Issues on the circulation page','asc|desc','Choice')");
821 print "Upgrade to $DBversion done ('add circ sysprefs todaysIssuesDefaultSortOrder and previousIssuesDefaultSortOrder.')\n";
822 SetVersion ($DBversion);
823 }
824
825
826 $DBversion = "3.00.00.041";
827 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
828 # Strictly speaking it is not necessary to explicitly change
829 # NULL values to 0, because the ALTER TABLE statement will do that.
830 # However, setting them first avoids a warning.
831 $dbh->do("UPDATE items SET notforloan = 0 WHERE notforloan IS NULL");
832 $dbh->do("UPDATE items SET damaged = 0 WHERE damaged IS NULL");
833 $dbh->do("UPDATE items SET itemlost = 0 WHERE itemlost IS NULL");
834 $dbh->do("UPDATE items SET wthdrawn = 0 WHERE wthdrawn IS NULL");
835 $dbh->do("ALTER TABLE items
836 MODIFY notforloan tinyint(1) NOT NULL default 0,
837 MODIFY damaged tinyint(1) NOT NULL default 0,
838 MODIFY itemlost tinyint(1) NOT NULL default 0,
839 MODIFY wthdrawn tinyint(1) NOT NULL default 0");
840 print "Upgrade to $DBversion done (disallow NULL in several item status columns)\n";
841 SetVersion ($DBversion);
842 }
843
844 $DBversion = "3.00.00.042";
845 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
846 $dbh->do("ALTER TABLE aqbooksellers CHANGE name name mediumtext NOT NULL");
847 print "Upgrade to $DBversion done (disallow NULL in aqbooksellers.name; part of fix for bug 1251)\n";
848 SetVersion ($DBversion);
849 }
850
851 $DBversion = "3.00.00.043";
852 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
853 $dbh->do("ALTER TABLE `currency` ADD `symbol` varchar(5) default NULL, ADD `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP");
854 print "Upgrade to $DBversion done (currency table: add symbol and timestamp columns)\n";
855 SetVersion ($DBversion);
856 }
857
858 $DBversion = "3.00.00.044";
859 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
860 $dbh->do("ALTER TABLE deletedborrowers
861 ADD `altcontactfirstname` varchar(255) default NULL,
862 ADD `altcontactsurname` varchar(255) default NULL,
863 ADD `altcontactaddress1` varchar(255) default NULL,
864 ADD `altcontactaddress2` varchar(255) default NULL,
865 ADD `altcontactaddress3` varchar(255) default NULL,
866 ADD `altcontactzipcode` varchar(50) default NULL,
867 ADD `altcontactphone` varchar(50) default NULL
868 ");
869 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES
870 ('OPACBaseURL',NULL,'Specify the Base URL of the OPAC, e.g., opac.mylibrary.com, the http:// will be added automatically by Koha.',NULL,'Free'),
871 ('language','en','Set the default language in the staff client.',NULL,'Languages'),
872 ('QueryAutoTruncate',1,'If ON, query truncation is enabled by default',NULL,'YesNo'),
873 ('QueryRemoveStopwords',0,'If ON, stopwords listed in the Administration area will be removed from queries',NULL,'YesNo')
874 ");
875 print "Upgrade to $DBversion done (syncing deletedborrowers table with borrowers table)\n";
876 SetVersion ($DBversion);
877 }
878
879 #-- http://www.w3.org/International/articles/language-tags/
880
881 #-- RFC4646
882 $DBversion = "3.00.00.045";
883 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
884 $dbh->do("
885 CREATE TABLE language_subtag_registry (
886 subtag varchar(25),
887 type varchar(25), -- language-script-region-variant-extension-privateuse
888 description varchar(25), -- only one of the possible descriptions for ease of reference, see language_descriptions for the complete list
889 added date,
890 KEY `subtag` (`subtag`)
891 ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
892
893 #-- TODO: add suppress_scripts
894 #-- this maps three letter codes defined in iso639.2 back to their
895 #-- two letter equivilents in rfc4646 (LOC maintains iso639+)
896 $dbh->do("CREATE TABLE language_rfc4646_to_iso639 (
897 rfc4646_subtag varchar(25),
898 iso639_2_code varchar(25),
899 KEY `rfc4646_subtag` (`rfc4646_subtag`)
900 ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
901
902 $dbh->do("CREATE TABLE language_descriptions (
903 subtag varchar(25),
904 type varchar(25),
905 lang varchar(25),
906 description varchar(255),
907 KEY `lang` (`lang`)
908 ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
909
910 #-- bi-directional support, keyed by script subcode
911 $dbh->do("CREATE TABLE language_script_bidi (
912 rfc4646_subtag varchar(25), -- script subtag, Arab, Hebr, etc.
913 bidi varchar(3), -- rtl ltr
914 KEY `rfc4646_subtag` (`rfc4646_subtag`)
915 ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
916
917 #-- BIDI Stuff, Arabic and Hebrew
918 $dbh->do("INSERT INTO language_script_bidi(rfc4646_subtag,bidi)
919 VALUES( 'Arab', 'rtl')");
920 $dbh->do("INSERT INTO language_script_bidi(rfc4646_subtag,bidi)
921 VALUES( 'Hebr', 'rtl')");
922
923 #-- TODO: need to map language subtags to script subtags for detection
924 #-- of bidi when script is not specified (like ar, he)
925 $dbh->do("CREATE TABLE language_script_mapping (
926 language_subtag varchar(25),
927 script_subtag varchar(25),
928 KEY `language_subtag` (`language_subtag`)
929 ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
930
931 #-- Default mappings between script and language subcodes
932 $dbh->do("INSERT INTO language_script_mapping(language_subtag,script_subtag)
933 VALUES( 'ar', 'Arab')");
934 $dbh->do("INSERT INTO language_script_mapping(language_subtag,script_subtag)
935 VALUES( 'he', 'Hebr')");
936
937 print "Upgrade to $DBversion done (adding language subtag registry and basic BiDi support NOTE: You should import the subtag registry SQL)\n";
938 SetVersion ($DBversion);
939 }
940
941 $DBversion = "3.00.00.046";
942 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
943 $dbh->do("ALTER TABLE `subscription` CHANGE `numberlength` `numberlength` int(11) default NULL ,
944 CHANGE `weeklength` `weeklength` int(11) default NULL");
945 $dbh->do("CREATE TABLE `serialitems` (`serialid` int(11) NOT NULL, `itemnumber` int(11) NOT NULL, UNIQUE KEY (`serialid`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
946 $dbh->do("INSERT INTO `serialitems` SELECT `serialid`,`itemnumber` from serial where NOT ISNULL(itemnumber) && itemnumber <> '' && itemnumber NOT LIKE '%,%'");
947 print "Upgrade to $DBversion done (Add serialitems table to link serial issues to items. )\n";
948 SetVersion ($DBversion);
949 }
950
951 $DBversion = "3.00.00.047";
952 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
953 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('OpacRenewalAllowed',0,'If ON, users can renew their issues directly from their OPAC account',NULL,'YesNo');");
954 print "Upgrade to $DBversion done ( Added OpacRenewalAllowed syspref )\n";
955 SetVersion ($DBversion);
956 }
957
958 $DBversion = "3.00.00.048";
959 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
960 $dbh->do("ALTER TABLE `items` ADD `more_subfields_xml` longtext default NULL");
961 print "Upgrade to $DBversion done (added items.more_subfields_xml)\n";
962 SetVersion ($DBversion);
963 }
964
965 $DBversion = "3.00.00.049";
966 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
967 $dbh->do("ALTER TABLE `z3950servers` ADD `encoding` text default NULL ");
968 print "Upgrade to $DBversion done ( Added encoding field to z3950servers table )\n";
969 SetVersion ($DBversion);
970 }
971
972 $DBversion = "3.00.00.050";
973 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
974 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('OpacHighlightedWords','0','If Set, query matched terms are highlighted in OPAC',NULL,'YesNo');");
975 print "Upgrade to $DBversion done ( Added OpacHighlightedWords syspref )\n";
976 SetVersion ($DBversion);
977 }
978
979 $DBversion = "3.00.00.051";
980 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
981 $dbh->do("UPDATE systempreferences SET explanation = 'Define the current theme for the OPAC interface.' WHERE variable = 'opacthemes';");
982 print "Upgrade to $DBversion done ( Corrected opacthemes explanation. )\n";
983 SetVersion ($DBversion);
984 }
985
986 $DBversion = "3.00.00.052";
987 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
988 $dbh->do("ALTER TABLE `deleteditems` ADD `more_subfields_xml` LONGTEXT DEFAULT NULL;");
989 print "Upgrade to $DBversion done ( Adding missing column to deleteditems table. )\n";
990 SetVersion ($DBversion);
991 }
992
993 $DBversion = "3.00.00.053";
994 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
995 $dbh->do("CREATE TABLE `printers_profile` (
996 `prof_id` int(4) NOT NULL auto_increment,
997 `printername` varchar(40) NOT NULL,
998 `tmpl_id` int(4) NOT NULL,
999 `paper_bin` varchar(20) NOT NULL,
1000 `offset_horz` float default NULL,
1001 `offset_vert` float default NULL,
1002 `creep_horz` float default NULL,
1003 `creep_vert` float default NULL,
1004 `unit` char(20) NOT NULL default 'POINT',
1005 PRIMARY KEY (`prof_id`),
1006 UNIQUE KEY `printername` (`printername`,`tmpl_id`,`paper_bin`),
1007 CONSTRAINT `printers_profile_pnfk_1` FOREIGN KEY (`tmpl_id`) REFERENCES `labels_templates` (`tmpl_id`) ON DELETE CASCADE ON UPDATE CASCADE
1008 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ");
1009 $dbh->do("CREATE TABLE `labels_profile` (
1010 `tmpl_id` int(4) NOT NULL,
1011 `prof_id` int(4) NOT NULL,
1012 UNIQUE KEY `tmpl_id` (`tmpl_id`),
1013 UNIQUE KEY `prof_id` (`prof_id`)
1014 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ");
1015 print "Upgrade to $DBversion done ( Printer Profile tables added )\n";
1016 SetVersion ($DBversion);
1017 }
1018
1019 $DBversion = "3.00.00.054";
1020 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1021 $dbh->do("UPDATE systempreferences SET options = 'incremental|annual|hbyymmincr|OFF', explanation = 'Used to autogenerate a barcode: incremental will be of the form 1, 2, 3; annual of the form 2007-0001, 2007-0002; hbyymmincr of the form HB08010001 where HB = Home Branch' WHERE variable = 'autoBarcode';");
1022 print "Upgrade to $DBversion done ( Added another barcode autogeneration sequence to barcode.pl. )\n";
1023 SetVersion ($DBversion);
1024 }
1025
1026 $DBversion = "3.00.00.055";
1027 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1028 $dbh->do("ALTER TABLE `zebraqueue` ADD KEY `zebraqueue_lookup` (`server`, `biblio_auth_number`, `operation`, `done`)");
1029 print "Upgrade to $DBversion done ( Added index on zebraqueue. )\n";
1030 SetVersion ($DBversion);
1031 }
1032 $DBversion = "3.00.00.056";
1033 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1034 $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) ");
1035 $dbh->do("ALTER TABLE `items` ADD `enumchron` VARCHAR(80) DEFAULT NULL;");
1036 print "Upgrade to $DBversion done ( Added item.enumchron column, and framework map to 952h )\n";
1037 SetVersion ($DBversion);
1038 }
1039
1040 $DBversion = "3.00.00.057";
1041 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1042 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('OAI-PMH','0','if ON, OAI-PMH server is enabled',NULL,'YesNo');");
1043 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('OAI-PMH:archiveID','KOHA-OAI-TEST','OAI-PMH archive identification',NULL,'Free');");
1044 $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');");
1045 $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');");
1046 $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');");
1047 SetVersion ($DBversion);
1048 }
1049
1050 $DBversion = "3.00.00.058";
1051 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1052 $dbh->do("ALTER TABLE `opac_news`
1053 CHANGE `lang` `lang` VARCHAR( 25 )
1054 CHARACTER SET utf8
1055 COLLATE utf8_general_ci
1056 NOT NULL ");
1057 print "Upgrade to $DBversion done ( lang field in opac_news made longer )\n";
1058 SetVersion ($DBversion);
1059 }
1060
1061 $DBversion = "3.00.00.059";
1062 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1063
1064 $dbh->do("CREATE TABLE IF NOT EXISTS `labels_templates` (
1065 `tmpl_id` int(4) NOT NULL auto_increment,
1066 `tmpl_code` char(100) default '',
1067 `tmpl_desc` char(100) default '',
1068 `page_width` float default '0',
1069 `page_height` float default '0',
1070 `label_width` float default '0',
1071 `label_height` float default '0',
1072 `topmargin` float default '0',
1073 `leftmargin` float default '0',
1074 `cols` int(2) default '0',
1075 `rows` int(2) default '0',
1076 `colgap` float default '0',
1077 `rowgap` float default '0',
1078 `active` int(1) default NULL,
1079 `units` char(20) default 'PX',
1080 `fontsize` int(4) NOT NULL default '3',
1081 PRIMARY KEY (`tmpl_id`)
1082 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;");
1083 $dbh->do("CREATE TABLE IF NOT EXISTS `printers_profile` (
1084 `prof_id` int(4) NOT NULL auto_increment,
1085 `printername` varchar(40) NOT NULL,
1086 `tmpl_id` int(4) NOT NULL,
1087 `paper_bin` varchar(20) NOT NULL,
1088 `offset_horz` float default NULL,
1089 `offset_vert` float default NULL,
1090 `creep_horz` float default NULL,
1091 `creep_vert` float default NULL,
1092 `unit` char(20) NOT NULL default 'POINT',
1093 PRIMARY KEY (`prof_id`),
1094 UNIQUE KEY `printername` (`printername`,`tmpl_id`,`paper_bin`),
1095 CONSTRAINT `printers_profile_pnfk_1` FOREIGN KEY (`tmpl_id`) REFERENCES `labels_templates` (`tmpl_id`) ON DELETE CASCADE ON UPDATE CASCADE
1096 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ");
1097 print "Upgrade to $DBversion done ( Added labels_templates table if it did not exist. )\n";
1098 SetVersion ($DBversion);
1099 }
1100
1101 $DBversion = "3.00.00.060";
1102 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1103 $dbh->do("CREATE TABLE IF NOT EXISTS `patronimage` (
1104 `cardnumber` varchar(16) NOT NULL,
1105 `mimetype` varchar(15) NOT NULL,
1106 `imagefile` mediumblob NOT NULL,
1107 PRIMARY KEY (`cardnumber`),
1108 CONSTRAINT `patronimage_fk1` FOREIGN KEY (`cardnumber`) REFERENCES `borrowers` (`cardnumber`) ON DELETE CASCADE ON UPDATE CASCADE
1109 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;");
1110 print "Upgrade to $DBversion done ( Added patronimage table. )\n";
1111 SetVersion ($DBversion);
1112 }
1113
1114 $DBversion = "3.00.00.061";
1115 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1116 $dbh->do("ALTER TABLE labels_templates ADD COLUMN font char(10) NOT NULL DEFAULT 'TR';");
1117 print "Upgrade to $DBversion done ( Added font column to labels_templates )\n";
1118 SetVersion ($DBversion);
1119 }
1120
1121 $DBversion = "3.00.00.062";
1122 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1123 $dbh->do("CREATE TABLE `old_issues` (
1124 `borrowernumber` int(11) default NULL,
1125 `itemnumber` int(11) default NULL,
1126 `date_due` date default NULL,
1127 `branchcode` varchar(10) default NULL,
1128 `issuingbranch` varchar(18) default NULL,
1129 `returndate` date default NULL,
1130 `lastreneweddate` date default NULL,
1131 `return` varchar(4) default NULL,
1132 `renewals` tinyint(4) default NULL,
1133 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1134 `issuedate` date default NULL,
1135 KEY `old_issuesborridx` (`borrowernumber`),
1136 KEY `old_issuesitemidx` (`itemnumber`),
1137 KEY `old_bordate` (`borrowernumber`,`timestamp`),
1138 CONSTRAINT `old_issues_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`)
1139 ON DELETE SET NULL ON UPDATE SET NULL,
1140 CONSTRAINT `old_issues_ibfk_2` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`)
1141 ON DELETE SET NULL ON UPDATE SET NULL
1142 ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
1143 $dbh->do("CREATE TABLE `old_reserves` (
1144 `borrowernumber` int(11) default NULL,
1145 `reservedate` date default NULL,
1146 `biblionumber` int(11) default NULL,
1147 `constrainttype` varchar(1) default NULL,
1148 `branchcode` varchar(10) default NULL,
1149 `notificationdate` date default NULL,
1150 `reminderdate` date default NULL,
1151 `cancellationdate` date default NULL,
1152 `reservenotes` mediumtext,
1153 `priority` smallint(6) default NULL,
1154 `found` varchar(1) default NULL,
1155 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1156 `itemnumber` int(11) default NULL,
1157 `waitingdate` date default NULL,
1158 KEY `old_reserves_borrowernumber` (`borrowernumber`),
1159 KEY `old_reserves_biblionumber` (`biblionumber`),
1160 KEY `old_reserves_itemnumber` (`itemnumber`),
1161 KEY `old_reserves_branchcode` (`branchcode`),
1162 CONSTRAINT `old_reserves_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`)
1163 ON DELETE SET NULL ON UPDATE SET NULL,
1164 CONSTRAINT `old_reserves_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`)
1165 ON DELETE SET NULL ON UPDATE SET NULL,
1166 CONSTRAINT `old_reserves_ibfk_3` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`)
1167 ON DELETE SET NULL ON UPDATE SET NULL
1168 ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
1169
1170 # move closed transactions to old_* tables
1171 $dbh->do("INSERT INTO old_issues SELECT * FROM issues WHERE returndate IS NOT NULL");
1172 $dbh->do("DELETE FROM issues WHERE returndate IS NOT NULL");
1173 $dbh->do("INSERT INTO old_reserves SELECT * FROM reserves WHERE cancellationdate IS NOT NULL OR found = 'F'");
1174 $dbh->do("DELETE FROM reserves WHERE cancellationdate IS NOT NULL OR found = 'F'");
1175
1176 print "Upgrade to $DBversion done ( Added old_issues and old_reserves tables )\n";
1177 SetVersion ($DBversion);
1178 }
1179
1180 $DBversion = "3.00.00.063";
1181 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1182 $dbh->do("ALTER TABLE deleteditems
1183 CHANGE COLUMN booksellerid booksellerid MEDIUMTEXT DEFAULT NULL,
1184 ADD COLUMN enumchron VARCHAR(80) DEFAULT NULL AFTER more_subfields_xml,
1185 ADD COLUMN copynumber SMALLINT(6) DEFAULT NULL AFTER enumchron;");
1186 $dbh->do("ALTER TABLE items
1187 CHANGE COLUMN booksellerid booksellerid MEDIUMTEXT,
1188 ADD COLUMN copynumber SMALLINT(6) DEFAULT NULL AFTER enumchron;");
1189 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";
1190 SetVersion ($DBversion);
1191 }
1192
1193 $DBversion = "3.00.00.064";
1194 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1195 $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');");
1196 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('AWSAccessKeyID','','See: http://aws.amazon.com','','free');");
1197 $dbh->do("DELETE FROM `systempreferences` WHERE variable='AmazonDevKey';");
1198 $dbh->do("DELETE FROM `systempreferences` WHERE variable='XISBNAmazonSimilarItems';");
1199 $dbh->do("DELETE FROM `systempreferences` WHERE variable='OPACXISBNAmazonSimilarItems';");
1200 print "Upgrade to $DBversion done (IMPORTANT: Upgrading to Amazon.com Associates Web Service 4.0 ) \n";
1201 SetVersion ($DBversion);
1202 }
1203
1204 $DBversion = "3.00.00.065";
1205 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1206 $dbh->do("CREATE TABLE `patroncards` (
1207 `cardid` int(11) NOT NULL auto_increment,
1208 `batch_id` varchar(10) NOT NULL default '1',
1209 `borrowernumber` int(11) NOT NULL,
1210 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1211 PRIMARY KEY (`cardid`),
1212 KEY `patroncards_ibfk_1` (`borrowernumber`),
1213 CONSTRAINT `patroncards_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE
1214 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;");
1215 print "Upgrade to $DBversion done (Adding patroncards table for patroncards generation feature. ) \n";
1216 SetVersion ($DBversion);
1217 }
1218
1219 $DBversion = "3.00.00.066";
1220 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1221 $dbh->do("ALTER TABLE `virtualshelfcontents` MODIFY `dateadded` timestamp NOT NULL
1222 DEFAULT CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP;
1223 ");
1224 print "Upgrade to $DBversion done (fix for bug 1873: virtualshelfcontents dateadded column empty. ) \n";
1225 SetVersion ($DBversion);
1226 }
1227
1228 $DBversion = "3.00.00.067";
1229 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1230 $dbh->do("UPDATE systempreferences SET explanation = 'Enable patron images for the Staff Client', type = 'YesNo' WHERE variable = 'patronimages'");
1231 print "Upgrade to $DBversion done (Updating patronimages syspref to reflect current kohastructure.sql. ) \n";
1232 SetVersion ($DBversion);
1233 }
1234
1235 $DBversion = "3.00.00.068";
1236 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1237 $dbh->do("CREATE TABLE `permissions` (
1238 `module_bit` int(11) NOT NULL DEFAULT 0,
1239 `code` varchar(30) DEFAULT NULL,
1240 `description` varchar(255) DEFAULT NULL,
1241 PRIMARY KEY (`module_bit`, `code`),
1242 CONSTRAINT `permissions_ibfk_1` FOREIGN KEY (`module_bit`) REFERENCES `userflags` (`bit`)
1243 ON DELETE CASCADE ON UPDATE CASCADE
1244 ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
1245 $dbh->do("CREATE TABLE `user_permissions` (
1246 `borrowernumber` int(11) NOT NULL DEFAULT 0,
1247 `module_bit` int(11) NOT NULL DEFAULT 0,
1248 `code` varchar(30) DEFAULT NULL,
1249 CONSTRAINT `user_permissions_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`)
1250 ON DELETE CASCADE ON UPDATE CASCADE,
1251 CONSTRAINT `user_permissions_ibfk_2` FOREIGN KEY (`module_bit`, `code`)
1252 REFERENCES `permissions` (`module_bit`, `code`)
1253 ON DELETE CASCADE ON UPDATE CASCADE
1254 ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
1255
1256 $dbh->do("INSERT INTO permissions (module_bit, code, description) VALUES
1257 (13, 'edit_news', 'Write news for the OPAC and staff interfaces'),
1258 (13, 'label_creator', 'Create printable labels and barcodes from catalog and patron data'),
1259 (13, 'edit_calendar', 'Define days when the library is closed'),
1260 (13, 'moderate_comments', 'Moderate patron comments'),
1261 (13, 'edit_notices', 'Define notices'),
1262 (13, 'edit_notice_status_triggers', 'Set notice/status triggers for overdue items'),
1263 (13, 'view_system_logs', 'Browse the system logs'),
1264 (13, 'inventory', 'Perform inventory (stocktaking) of your catalogue'),
1265 (13, 'stage_marc_import', 'Stage MARC records into the reservoir'),
1266 (13, 'manage_staged_marc', 'Managed staged MARC records, including completing and reversing imports'),
1267 (13, 'export_catalog', 'Export bibliographic and holdings data'),
1268 (13, 'import_patrons', 'Import patron data'),
1269 (13, 'delete_anonymize_patrons', 'Delete old borrowers and anonymize circulation history (deletes borrower reading history)'),
1270 (13, 'batch_upload_patron_images', 'Upload patron images in batch or one at a time'),
1271 (13, 'schedule_tasks', 'Schedule tasks to run')");
1272
1273 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('GranularPermissions','0','Use detailed staff user permissions',NULL,'YesNo')");
1274
1275 print "Upgrade to $DBversion done (adding permissions and user_permissions tables and GranularPermissions syspref) \n";
1276 SetVersion ($DBversion);
1277 }
1278
1279 $DBversion = "3.00.00.069";
1280 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1281 $dbh->do("ALTER TABLE labels_conf CHANGE COLUMN class classification int(1) DEFAULT NULL;");
1282 print "Upgrade to $DBversion done ( Correcting columname in labels_conf )\n";
1283 SetVersion ($DBversion);
1284 }
1285
1286 $DBversion = "3.00.00.070";
1287 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1288 $sth = $dbh->prepare("SELECT value FROM systempreferences WHERE variable='yuipath'");
1289 $sth->execute;
1290 my ($value) = $sth->fetchrow;
1291 $value =~ s/2.3.1/2.5.1/;
1292 $dbh->do("UPDATE systempreferences SET value='$value' WHERE variable='yuipath';");
1293 print "Update yuipath syspref to 2.5.1 if necessary\n";
1294 SetVersion ($DBversion);
1295 }
1296
1297 $DBversion = "3.00.00.071";
1298 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1299 $dbh->do(" ALTER TABLE `subscription` ADD `serialsadditems` TINYINT( 1 ) NOT NULL DEFAULT '0';");
1300 # fill the new field with the previous systempreference value, then drop the syspref
1301 my $sth = $dbh->prepare("SELECT value FROM systempreferences WHERE variable='serialsadditems'");
1302 $sth->execute;
1303 my ($serialsadditems) = $sth->fetchrow();
1304 $dbh->do("UPDATE subscription SET serialsadditems=$serialsadditems");
1305 $dbh->do("DELETE FROM systempreferences WHERE variable='serialsadditems'");
1306 print "Upgrade to $DBversion done ( moving serialsadditems from syspref to subscription )\n";
1307 SetVersion ($DBversion);
1308 }
1309
1310 $DBversion = "3.00.00.072";
1311 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1312 $dbh->do("ALTER TABLE labels_conf ADD COLUMN formatstring VARCHAR(64) DEFAULT NULL;");
1313 print "Upgrade to $DBversion done ( Adding format string to labels generator. )\n";
1314 SetVersion ($DBversion);
1315 }
1316
1317 $DBversion = "3.00.00.073";
1318 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1319 $dbh->do("DROP TABLE IF EXISTS `tags_all`;");
1320 $dbh->do(q#
1321 CREATE TABLE `tags_all` (
1322 `tag_id` int(11) NOT NULL auto_increment,
1323 `borrowernumber` int(11) NOT NULL,
1324 `biblionumber` int(11) NOT NULL,
1325 `term` varchar(255) NOT NULL,
1326 `language` int(4) default NULL,
1327 `date_created` datetime NOT NULL,
1328 PRIMARY KEY (`tag_id`),
1329 KEY `tags_borrowers_fk_1` (`borrowernumber`),
1330 KEY `tags_biblionumber_fk_1` (`biblionumber`),
1331 CONSTRAINT `tags_borrowers_fk_1` FOREIGN KEY (`borrowernumber`)
1332 REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1333 CONSTRAINT `tags_biblionumber_fk_1` FOREIGN KEY (`biblionumber`)
1334 REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE
1335 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1336 #);
1337 $dbh->do("DROP TABLE IF EXISTS `tags_approval`;");
1338 $dbh->do(q#
1339 CREATE TABLE `tags_approval` (
1340 `term` varchar(255) NOT NULL,
1341 `approved` int(1) NOT NULL default '0',
1342 `date_approved` datetime default NULL,
1343 `approved_by` int(11) default NULL,
1344 `weight_total` int(9) NOT NULL default '1',
1345 PRIMARY KEY (`term`),
1346 KEY `tags_approval_borrowers_fk_1` (`approved_by`),
1347 CONSTRAINT `tags_approval_borrowers_fk_1` FOREIGN KEY (`approved_by`)
1348 REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE
1349 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1350 #);
1351 $dbh->do("DROP TABLE IF EXISTS `tags_index`;");
1352 $dbh->do(q#
1353 CREATE TABLE `tags_index` (
1354 `term` varchar(255) NOT NULL,
1355 `biblionumber` int(11) NOT NULL,
1356 `weight` int(9) NOT NULL default '1',
1357 PRIMARY KEY (`term`,`biblionumber`),
1358 KEY `tags_index_biblionumber_fk_1` (`biblionumber`),
1359 CONSTRAINT `tags_index_term_fk_1` FOREIGN KEY (`term`)
1360 REFERENCES `tags_approval` (`term`) ON DELETE CASCADE ON UPDATE CASCADE,
1361 CONSTRAINT `tags_index_biblionumber_fk_1` FOREIGN KEY (`biblionumber`)
1362 REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE
1363 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1364 #);
1365 $dbh->do(q#
1366 INSERT INTO `systempreferences` VALUES
1367 ('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=',''),
1368 ('BakerTaylorEnabled','0','','Enable or disable all Baker & Taylor features.','YesNo'),
1369 ('BakerTaylorPassword','','','Baker & Taylor Password for Content Cafe (external content)','Textarea'),
1370 ('BakerTaylorUsername','','','Baker & Taylor Username for Content Cafe (external content)','Textarea'),
1371 ('TagsEnabled','1','','Enables or disables all tagging features. This is the main switch for tags.','YesNo'),
1372 ('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.',''),
1373 ('TagsInputOnDetail','1','','Allow users to input tags from the detail page.', 'YesNo'),
1374 ('TagsInputOnList', '0','','Allow users to input tags from the search results list.', 'YesNo'),
1375 ('TagsModeration', NULL,'','(unimplemented) Requires tags from patrons to be approved before becoming visible.','YesNo'),
1376 ('TagsShowOnDetail','10','','Number of tags to display on detail page. 0 is off.', 'Integer'),
1377 ('TagsShowOnList', '6','','Number of tags to display on search results list. 0 is off.','Integer')
1378 #);
1379 print "Upgrade to $DBversion done (Baker/Taylor,Tags: sysprefs and tables (tags_all, tags_index, tags_approval)) \n";
1380 SetVersion ($DBversion);
1381 }
1382
1383 $DBversion = "3.00.00.074";
1384 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1385 $dbh->do( q(update itemtypes set imageurl = concat( 'npl/', imageurl )
1386 where imageurl not like 'http%'
1387 and imageurl is not NULL
1388 and imageurl != '') );
1389 print "Upgrade to $DBversion done (updating imagetype.imageurls to reflect new icon locations.)\n";
1390 SetVersion ($DBversion);
1391 }
1392
1393 $DBversion = "3.00.00.075";
1394 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1395 $dbh->do( q(alter table authorised_values add imageurl varchar(200) default NULL) );
1396 print "Upgrade to $DBversion done (adding imageurl field to authorised_values table)\n";
1397 SetVersion ($DBversion);
1398 }
1399
1400 $DBversion = "3.00.00.076";
1401 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1402 $dbh->do("ALTER TABLE import_batches
1403 ADD COLUMN nomatch_action enum('create_new', 'ignore') NOT NULL default 'create_new' AFTER overlay_action");
1404 $dbh->do("ALTER TABLE import_batches
1405 ADD COLUMN item_action enum('always_add', 'add_only_for_matches', 'add_only_for_new', 'ignore')
1406 NOT NULL default 'always_add' AFTER nomatch_action");
1407 $dbh->do("ALTER TABLE import_batches
1408 MODIFY overlay_action enum('replace', 'create_new', 'use_template', 'ignore')
1409 NOT NULL default 'create_new'");
1410 $dbh->do("ALTER TABLE import_records
1411 MODIFY status enum('error', 'staged', 'imported', 'reverted', 'items_reverted',
1412 'ignored') NOT NULL default 'staged'");
1413 $dbh->do("ALTER TABLE import_items
1414 MODIFY status enum('error', 'staged', 'imported', 'reverted', 'ignored') NOT NULL default 'staged'");
1415
1416 print "Upgrade to $DBversion done (changes to import_batches and import_records)\n";
1417 SetVersion ($DBversion);
1418 }
1419
1420 $DBversion = "3.00.00.077";
1421 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1422 # drop these tables only if they exist and none of them are empty
1423 # these tables are not defined in the packaged 2.2.9, but since it is believed
1424 # that at least one library may be using them in a post-2.2.9 but pre-3.0 Koha,
1425 # some care is taken.
1426 my ($print_error) = $dbh->{PrintError};
1427 $dbh->{PrintError} = 0;
1428 my ($raise_error) = $dbh->{RaiseError};
1429 $dbh->{RaiseError} = 1;
1430
1431 my $count = 0;
1432 my $do_drop = 1;
1433 eval { $count = $dbh->do("SELECT 1 FROM categorytable"); };
1434 if ($count > 0) {
1435 $do_drop = 0;
1436 }
1437 eval { $count = $dbh->do("SELECT 1 FROM mediatypetable"); };
1438 if ($count > 0) {
1439 $do_drop = 0;
1440 }
1441 eval { $count = $dbh->do("SELECT 1 FROM subcategorytable"); };
1442 if ($count > 0) {
1443 $do_drop = 0;
1444 }
1445
1446 if ($do_drop) {
1447 $dbh->do("DROP TABLE IF EXISTS `categorytable`");
1448 $dbh->do("DROP TABLE IF EXISTS `mediatypetable`");
1449 $dbh->do("DROP TABLE IF EXISTS `subcategorytable`");
1450 }
1451
1452 $dbh->{PrintError} = $print_error;
1453 $dbh->{RaiseError} = $raise_error;
1454 print "Upgrade to $DBversion done (drop categorytable, subcategorytable, and mediatypetable)\n";
1455 SetVersion ($DBversion);
1456 }
1457
1458 $DBversion = "3.00.00.078";
1459 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1460 my ($print_error) = $dbh->{PrintError};
1461 $dbh->{PrintError} = 0;
1462
1463 unless ($dbh->do("SELECT 1 FROM browser")) {
1464 $dbh->{PrintError} = $print_error;
1465 $dbh->do("CREATE TABLE `browser` (
1466 `level` int(11) NOT NULL,
1467 `classification` varchar(20) NOT NULL,
1468 `description` varchar(255) NOT NULL,
1469 `number` bigint(20) NOT NULL,
1470 `endnode` tinyint(4) NOT NULL
1471 ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
1472 }
1473 $dbh->{PrintError} = $print_error;
1474 print "Upgrade to $DBversion done (add browser table if not already present)\n";
1475 SetVersion ($DBversion);
1476 }
1477
1478 =item DropAllForeignKeys($table)
1479
1480 Drop all foreign keys of the table $table
1481
1482 =cut
1483
1484 sub DropAllForeignKeys {
1485 my ($table) = @_;
1486 # get the table description
1487 my $sth = $dbh->prepare("SHOW CREATE TABLE $table");
1488 $sth->execute;
1489 my $vsc_structure = $sth->fetchrow;
1490 # split on CONSTRAINT keyword
1491 my @fks = split /CONSTRAINT /,$vsc_structure;
1492 # parse each entry
1493 foreach (@fks) {
1494 # isolate what is before FOREIGN KEY, if there is something, it's a foreign key to drop
1495 $_ = /(.*) FOREIGN KEY.*/;
1496 my $id = $1;
1497 if ($id) {
1498 # we have found 1 foreign, drop it
1499 $dbh->do("ALTER TABLE $table DROP FOREIGN KEY $id");
1500 $id="";
1501 }
1502 }
1503 }
1504
1505
1506 =item TransformToNum
1507
1508 Transform the Koha version from a 4 parts string
1509 to a number, with just 1 .
1510
1511 =cut
1512
1513 sub TransformToNum {
1514 my $version = shift;
1515 # remove the 3 last . to have a Perl number
1516 $version =~ s/(.*\..*)\.(.*)\.(.*)/$1$2$3/;
1517 return $version;
1518 }
1519
1520 =item SetVersion
1521
1522 set the DBversion in the systempreferences
1523
1524 =cut
1525
1526 sub SetVersion {
1527 my $kohaversion = TransformToNum(shift);
1528 if (C4::Context->preference('Version')) {
1529 my $finish=$dbh->prepare("UPDATE systempreferences SET value=? WHERE variable='Version'");
1530 $finish->execute($kohaversion);
1531 } else {
1532 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')");
1533 $finish->execute($kohaversion);
1534 }
1535 }
1536 exit;
1537
Koha ILS