?  search:
summary | log | graphiclog1 | graphiclog2 | commit | commitdiff | tree | refs | edit | fork
blame | history | raw | HEAD
bug 1710 and 1739: two new sysprefs (DB rev 092)
[koha.git] / installer / data / mysql / updatedatabase.pl
blob1e75e9a2f225224d4fc4af32a0488e089e2b3a1b
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 kohaversion.pl up-to-date!
16
17 use strict;
18 # use warnings;
19
20 # CPAN modules
21 use DBI;
22 use Getopt::Long;
23 # Koha modules
24 use C4::Context;
25
26 use MARC::Record;
27 use MARC::File::XML ( BinaryEncoding => 'utf8' );
28
29 # FIXME - The user might be installing a new database, so can't rely
30 # on /etc/koha.conf anyway.
31
32 my $debug = 0;
33
34 my (
35 $sth, $sti,
36 $query,
37 %existingtables, # tables already in database
38 %types,
39 $table,
40 $column,
41 $type, $null, $key, $default, $extra,
42 $prefitem, # preference item in systempreferences table
43 );
44
45 my $silent;
46 GetOptions(
47 's' =>\$silent
48 );
49 my $dbh = C4::Context->dbh;
50 $|=1; # flushes output
51
52 =item
53
54 Deal with virtualshelves
55
56 =cut
57
58 my $DBversion = "3.00.00.001";
59 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
60 # update virtualshelves table to
61 #
62 $dbh->do("ALTER TABLE `bookshelf` RENAME `virtualshelves`");
63 $dbh->do("ALTER TABLE `shelfcontents` RENAME `virtualshelfcontents`");
64 $dbh->do("ALTER TABLE `virtualshelfcontents` ADD `biblionumber` INT( 11 ) NOT NULL default '0' AFTER shelfnumber");
65 $dbh->do("UPDATE `virtualshelfcontents` SET biblionumber=(SELECT biblionumber FROM items WHERE items.itemnumber=virtualshelfcontents.itemnumber)");
66 # drop all foreign keys : otherwise, we can't drop itemnumber field.
67 DropAllForeignKeys('virtualshelfcontents');
68 $dbh->do("ALTER TABLE `virtualshelfcontents` ADD KEY biblionumber (biblionumber)");
69 # create the new foreign keys (on biblionumber)
70 $dbh->do("ALTER TABLE `virtualshelfcontents` ADD CONSTRAINT `virtualshelfcontents_ibfk_1` FOREIGN KEY (`shelfnumber`) REFERENCES `virtualshelves` (`shelfnumber`) ON DELETE CASCADE ON UPDATE CASCADE");
71 # re-create the foreign key on virtualshelf
72 $dbh->do("ALTER TABLE `virtualshelfcontents` ADD CONSTRAINT `shelfcontents_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE");
73 $dbh->do("ALTER TABLE `virtualshelfcontents` DROP `itemnumber`");
74 print "Upgrade to $DBversion done (virtualshelves)\n";
75 SetVersion ($DBversion);
76 }
77
78
79 $DBversion = "3.00.00.002";
80 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
81 $dbh->do("DROP TABLE sessions");
82 $dbh->do("CREATE TABLE `sessions` (
83 `id` varchar(32) NOT NULL,
84 `a_session` text NOT NULL,
85 UNIQUE KEY `id` (`id`)
86 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;");
87 print "Upgrade to $DBversion done (sessions uses CGI::session, new table structure for sessions)\n";
88 SetVersion ($DBversion);
89 }
90
91
92 $DBversion = "3.00.00.003";
93 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
94 if (C4::Context->preference("opaclanguages") eq "fr") {
95 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('ReservesNeedReturns','0','Si ce paramètre est mis à 1, une réservation posée sur un exemplaire présent sur le site devra être passée en retour pour être disponible. Sinon, elle sera automatiquement disponible, Koha considère que le bibliothécaire place la réservation en ayant le document en mains','','YesNo')");
96 } else {
97 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('ReservesNeedReturns','0','If set, a reserve done on an item available in this branch need a check-in, otherwise, a reserve on a specific item, that is on the branch & available is considered as available','','YesNo')");
98 }
99 print "Upgrade to $DBversion done (adding ReservesNeedReturns systempref, in circulation)\n";
100 SetVersion ($DBversion);
101 }
102
103
104 $DBversion = "3.00.00.004";
105 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
106 $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')");
107 print "Upgrade to $DBversion done (adding DebugLevel systempref, in 'Admin' tab)\n";
108 SetVersion ($DBversion);
109 }
110
111 $DBversion = "3.00.00.005";
112 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
113 $dbh->do("CREATE TABLE `tags` (
114 `entry` varchar(255) NOT NULL default '',
115 `weight` bigint(20) NOT NULL default 0,
116 PRIMARY KEY (`entry`)
117 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
118 ");
119 $dbh->do("CREATE TABLE `nozebra` (
120 `server` varchar(20) NOT NULL,
121 `indexname` varchar(40) NOT NULL,
122 `value` varchar(250) NOT NULL,
123 `biblionumbers` longtext NOT NULL,
124 KEY `indexname` (`server`,`indexname`),
125 KEY `value` (`server`,`value`))
126 ENGINE=InnoDB DEFAULT CHARSET=utf8;
127 ");
128 print "Upgrade to $DBversion done (adding tags and nozebra tables )\n";
129 SetVersion ($DBversion);
130 }
131
132 $DBversion = "3.00.00.006";
133 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
134 $dbh->do("UPDATE issues SET issuedate=timestamp WHERE issuedate='0000-00-00'");
135 print "Upgrade to $DBversion done (filled issues.issuedate with timestamp)\n";
136 SetVersion ($DBversion);
137 }
138
139 $DBversion = "3.00.00.007";
140 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
141 $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')");
142 print "Upgrade to $DBversion done (set SessionStorage variable)\n";
143 SetVersion ($DBversion);
144 }
145
146 $DBversion = "3.00.00.008";
147 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
148 $dbh->do("ALTER TABLE `biblio` ADD `datecreated` DATE NOT NULL AFTER `timestamp` ;");
149 $dbh->do("UPDATE biblio SET datecreated=timestamp");
150 print "Upgrade to $DBversion done (biblio creation date)\n";
151 SetVersion ($DBversion);
152 }
153
154 $DBversion = "3.00.00.009";
155 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
156
157 # Create backups of call number columns
158 # in case default migration needs to be customized
159 #
160 # UPGRADE NOTE: temp_upg_biblioitems_call_num should be dropped
161 # after call numbers have been transformed to the new structure
162 #
163 # Not bothering to do the same with deletedbiblioitems -- assume
164 # default is good enough.
165 $dbh->do("CREATE TABLE `temp_upg_biblioitems_call_num` AS
166 SELECT `biblioitemnumber`, `biblionumber`,
167 `classification`, `dewey`, `subclass`,
168 `lcsort`, `ccode`
169 FROM `biblioitems`");
170
171 # biblioitems changes
172 $dbh->do("ALTER TABLE `biblioitems` CHANGE COLUMN `volumeddesc` `volumedesc` TEXT,
173 ADD `cn_source` VARCHAR(10) DEFAULT NULL AFTER `ccode`,
174 ADD `cn_class` VARCHAR(30) DEFAULT NULL AFTER `cn_source`,
175 ADD `cn_item` VARCHAR(10) DEFAULT NULL AFTER `cn_class`,
176 ADD `cn_suffix` VARCHAR(10) DEFAULT NULL AFTER `cn_item`,
177 ADD `cn_sort` VARCHAR(30) DEFAULT NULL AFTER `cn_suffix`,
178 ADD `totalissues` INT(10) AFTER `cn_sort`");
179
180 # default mapping of call number columns:
181 # cn_class = concatentation of classification + dewey,
182 # trimmed to fit -- assumes that most users do not
183 # populate both classification and dewey in a single record
184 # cn_item = subclass
185 # cn_source = left null
186 # cn_sort = lcsort
187 #
188 # After upgrade, cn_sort will have to be set based on whatever
189 # default call number scheme user sets as a preference. Misc
190 # script will be added at some point to do that.
191 #
192 $dbh->do("UPDATE `biblioitems`
193 SET cn_class = SUBSTR(TRIM(CONCAT_WS(' ', `classification`, `dewey`)), 1, 30),
194 cn_item = subclass,
195 `cn_sort` = `lcsort`
196 ");
197
198 # Now drop the old call number columns
199 $dbh->do("ALTER TABLE `biblioitems` DROP COLUMN `classification`,
200 DROP COLUMN `dewey`,
201 DROP COLUMN `subclass`,
202 DROP COLUMN `lcsort`,
203 DROP COLUMN `ccode`");
204
205 # deletedbiblio changes
206 $dbh->do("ALTER TABLE `deletedbiblio` ALTER COLUMN `frameworkcode` SET DEFAULT '',
207 DROP COLUMN `marc`,
208 ADD `datecreated` DATE NOT NULL AFTER `timestamp`");
209 $dbh->do("UPDATE deletedbiblio SET datecreated = timestamp");
210
211 # deletedbiblioitems changes
212 $dbh->do("ALTER TABLE `deletedbiblioitems`
213 MODIFY `publicationyear` TEXT,
214 CHANGE `volumeddesc` `volumedesc` TEXT,
215 MODIFY `collectiontitle` MEDIUMTEXT DEFAULT NULL AFTER `volumedesc`,
216 MODIFY `collectionissn` TEXT DEFAULT NULL AFTER `collectiontitle`,
217 MODIFY `collectionvolume` MEDIUMTEXT DEFAULT NULL AFTER `collectionissn`,
218 MODIFY `editionstatement` TEXT DEFAULT NULL AFTER `collectionvolume`,
219 MODIFY `editionresponsibility` TEXT DEFAULT NULL AFTER `editionstatement`,
220 MODIFY `place` VARCHAR(255) DEFAULT NULL AFTER `size`,
221 MODIFY `marc` LONGBLOB,
222 ADD `cn_source` VARCHAR(10) DEFAULT NULL AFTER `url`,
223 ADD `cn_class` VARCHAR(30) DEFAULT NULL AFTER `cn_source`,
224 ADD `cn_item` VARCHAR(10) DEFAULT NULL AFTER `cn_class`,
225 ADD `cn_suffix` VARCHAR(10) DEFAULT NULL AFTER `cn_item`,
226 ADD `cn_sort` VARCHAR(30) DEFAULT NULL AFTER `cn_suffix`,
227 ADD `totalissues` INT(10) AFTER `cn_sort`,
228 ADD `marcxml` LONGTEXT NOT NULL AFTER `totalissues`,
229 ADD KEY `isbn` (`isbn`),
230 ADD KEY `publishercode` (`publishercode`)
231 ");
232
233 $dbh->do("UPDATE `deletedbiblioitems`
234 SET `cn_class` = SUBSTR(TRIM(CONCAT_WS(' ', `classification`, `dewey`)), 1, 30),
235 `cn_item` = `subclass`,
236 `cn_sort` = `lcsort`
237 ");
238 $dbh->do("ALTER TABLE `deletedbiblioitems`
239 DROP COLUMN `classification`,
240 DROP COLUMN `dewey`,
241 DROP COLUMN `subclass`,
242 DROP COLUMN `lcsort`,
243 DROP COLUMN `ccode`
244 ");
245
246 # deleteditems changes
247 $dbh->do("ALTER TABLE `deleteditems`
248 MODIFY `barcode` VARCHAR(20) DEFAULT NULL,
249 MODIFY `price` DECIMAL(8,2) DEFAULT NULL,
250 MODIFY `replacementprice` DECIMAL(8,2) DEFAULT NULL,
251 DROP `bulk`,
252 MODIFY `itemcallnumber` VARCHAR(30) DEFAULT NULL AFTER `wthdrawn`,
253 MODIFY `holdingbranch` VARCHAR(10) DEFAULT NULL,
254 DROP `interim`,
255 MODIFY `timestamp` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP AFTER `paidfor`,
256 DROP `cutterextra`,
257 ADD `cn_source` VARCHAR(10) DEFAULT NULL AFTER `onloan`,
258 ADD `cn_sort` VARCHAR(30) DEFAULT NULL AFTER `cn_source`,
259 ADD `ccode` VARCHAR(10) DEFAULT NULL AFTER `cn_sort`,
260 ADD `materials` VARCHAR(10) DEFAULT NULL AFTER `ccode`,
261 ADD `uri` VARCHAR(255) DEFAULT NULL AFTER `materials`,
262 MODIFY `marc` LONGBLOB AFTER `uri`,
263 DROP KEY `barcode`,
264 DROP KEY `itembarcodeidx`,
265 DROP KEY `itembinoidx`,
266 DROP KEY `itembibnoidx`,
267 ADD UNIQUE KEY `delitembarcodeidx` (`barcode`),
268 ADD KEY `delitembinoidx` (`biblioitemnumber`),
269 ADD KEY `delitembibnoidx` (`biblionumber`),
270 ADD KEY `delhomebranch` (`homebranch`),
271 ADD KEY `delholdingbranch` (`holdingbranch`)");
272 $dbh->do("UPDATE deleteditems SET `ccode` = `itype`");
273 $dbh->do("ALTER TABLE deleteditems DROP `itype`");
274 $dbh->do("UPDATE `deleteditems` SET `cn_sort` = `itemcallnumber`");
275
276 # items changes
277 $dbh->do("ALTER TABLE `items` ADD `cn_source` VARCHAR(10) DEFAULT NULL AFTER `onloan`,
278 ADD `cn_sort` VARCHAR(30) DEFAULT NULL AFTER `cn_source`,
279 ADD `ccode` VARCHAR(10) DEFAULT NULL AFTER `cn_sort`,
280 ADD `materials` VARCHAR(10) DEFAULT NULL AFTER `ccode`,
281 ADD `uri` VARCHAR(255) DEFAULT NULL AFTER `materials`
282 ");
283 $dbh->do("ALTER TABLE `items`
284 DROP KEY `itembarcodeidx`,
285 ADD UNIQUE KEY `itembarcodeidx` (`barcode`)");
286
287 # map items.itype to items.ccode and
288 # set cn_sort to itemcallnumber -- as with biblioitems.cn_sort,
289 # will have to be subsequently updated per user's default
290 # classification scheme
291 $dbh->do("UPDATE `items` SET `cn_sort` = `itemcallnumber`,
292 `ccode` = `itype`");
293
294 $dbh->do("ALTER TABLE `items` DROP `cutterextra`,
295 DROP `itype`");
296
297 print "Upgrade to $DBversion done (major changes to biblio, biblioitems, items, and deleted* versions of same\n";
298 SetVersion ($DBversion);
299 }
300
301 $DBversion = "3.00.00.010";
302 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
303 $dbh->do("CREATE INDEX `userid` ON borrowers (`userid`) ");
304 print "Upgrade to $DBversion done (userid index added)\n";
305 SetVersion ($DBversion);
306 }
307
308 $DBversion = "3.00.00.011";
309 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
310 $dbh->do("ALTER TABLE `branchcategories` CHANGE `categorycode` `categorycode` varchar(10) ");
311 $dbh->do("ALTER TABLE `branchcategories` CHANGE `categoryname` `categoryname` varchar(32) ");
312 $dbh->do("ALTER TABLE `branchcategories` ADD COLUMN `categorytype` varchar(16) ");
313 $dbh->do("UPDATE `branchcategories` SET `categorytype` = 'properties'");
314 $dbh->do("ALTER TABLE `branchrelations` CHANGE `categorycode` `categorycode` varchar(10) ");
315 print "Upgrade to $DBversion done (added branchcategory type)\n";
316 SetVersion ($DBversion);
317 }
318
319 $DBversion = "3.00.00.012";
320 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
321 $dbh->do("CREATE TABLE `class_sort_rules` (
322 `class_sort_rule` varchar(10) NOT NULL default '',
323 `description` mediumtext,
324 `sort_routine` varchar(30) NOT NULL default '',
325 PRIMARY KEY (`class_sort_rule`),
326 UNIQUE KEY `class_sort_rule_idx` (`class_sort_rule`)
327 ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
328 $dbh->do("CREATE TABLE `class_sources` (
329 `cn_source` varchar(10) NOT NULL default '',
330 `description` mediumtext,
331 `used` tinyint(4) NOT NULL default 0,
332 `class_sort_rule` varchar(10) NOT NULL default '',
333 PRIMARY KEY (`cn_source`),
334 UNIQUE KEY `cn_source_idx` (`cn_source`),
335 KEY `used_idx` (`used`),
336 CONSTRAINT `class_source_ibfk_1` FOREIGN KEY (`class_sort_rule`)
337 REFERENCES `class_sort_rules` (`class_sort_rule`)
338 ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
339 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type)
340 VALUES('DefaultClassificationSource','ddc',
341 'Default classification scheme used by the collection. E.g., Dewey, LCC, etc.', NULL,'free')");
342 $dbh->do("INSERT INTO `class_sort_rules` (`class_sort_rule`, `description`, `sort_routine`) VALUES
343 ('dewey', 'Default filing rules for DDC', 'Dewey'),
344 ('lcc', 'Default filing rules for LCC', 'LCC'),
345 ('generic', 'Generic call number filing rules', 'Generic')");
346 $dbh->do("INSERT INTO `class_sources` (`cn_source`, `description`, `used`, `class_sort_rule`) VALUES
347 ('ddc', 'Dewey Decimal Classification', 1, 'dewey'),
348 ('lcc', 'Library of Congress Classification', 1, 'lcc'),
349 ('udc', 'Universal Decimal Classification', 0, 'generic'),
350 ('sudocs', 'SuDoc Classification (U.S. GPO)', 0, 'generic'),
351 ('z', 'Other/Generic Classification Scheme', 0, 'generic')");
352 print "Upgrade to $DBversion done (classification sources added)\n";
353 SetVersion ($DBversion);
354 }
355
356 $DBversion = "3.00.00.013";
357 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
358 $dbh->do("CREATE TABLE `import_batches` (
359 `import_batch_id` int(11) NOT NULL auto_increment,
360 `template_id` int(11) default NULL,
361 `branchcode` varchar(10) default NULL,
362 `num_biblios` int(11) NOT NULL default 0,
363 `num_items` int(11) NOT NULL default 0,
364 `upload_timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP,
365 `overlay_action` enum('replace', 'create_new', 'use_template') NOT NULL default 'create_new',
366 `import_status` enum('staging', 'staged', 'importing', 'imported', 'reverting', 'reverted', 'cleaned') NOT NULL default 'staging',
367 `batch_type` enum('batch', 'z3950') NOT NULL default 'batch',
368 `file_name` varchar(100),
369 `comments` mediumtext,
370 PRIMARY KEY (`import_batch_id`),
371 KEY `branchcode` (`branchcode`)
372 ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
373 $dbh->do("CREATE TABLE `import_records` (
374 `import_record_id` int(11) NOT NULL auto_increment,
375 `import_batch_id` int(11) NOT NULL,
376 `branchcode` varchar(10) default NULL,
377 `record_sequence` int(11) NOT NULL default 0,
378 `upload_timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP,
379 `import_date` DATE default NULL,
380 `marc` longblob NOT NULL,
381 `marcxml` longtext NOT NULL,
382 `marcxml_old` longtext NOT NULL,
383 `record_type` enum('biblio', 'auth', 'holdings') NOT NULL default 'biblio',
384 `overlay_status` enum('no_match', 'auto_match', 'manual_match', 'match_applied') NOT NULL default 'no_match',
385 `status` enum('error', 'staged', 'imported', 'reverted', 'items_reverted') NOT NULL default 'staged',
386 `import_error` mediumtext,
387 `encoding` varchar(40) NOT NULL default '',
388 `z3950random` varchar(40) default NULL,
389 PRIMARY KEY (`import_record_id`),
390 CONSTRAINT `import_records_ifbk_1` FOREIGN KEY (`import_batch_id`)
391 REFERENCES `import_batches` (`import_batch_id`) ON DELETE CASCADE ON UPDATE CASCADE,
392 KEY `branchcode` (`branchcode`),
393 KEY `batch_sequence` (`import_batch_id`, `record_sequence`)
394 ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
395 $dbh->do("CREATE TABLE `import_record_matches` (
396 `import_record_id` int(11) NOT NULL,
397 `candidate_match_id` int(11) NOT NULL,
398 `score` int(11) NOT NULL default 0,
399 CONSTRAINT `import_record_matches_ibfk_1` FOREIGN KEY (`import_record_id`)
400 REFERENCES `import_records` (`import_record_id`) ON DELETE CASCADE ON UPDATE CASCADE,
401 KEY `record_score` (`import_record_id`, `score`)
402 ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
403 $dbh->do("CREATE TABLE `import_biblios` (
404 `import_record_id` int(11) NOT NULL,
405 `matched_biblionumber` int(11) default NULL,
406 `control_number` varchar(25) default NULL,
407 `original_source` varchar(25) default NULL,
408 `title` varchar(128) default NULL,
409 `author` varchar(80) default NULL,
410 `isbn` varchar(14) default NULL,
411 `issn` varchar(9) default NULL,
412 `has_items` tinyint(1) NOT NULL default 0,
413 CONSTRAINT `import_biblios_ibfk_1` FOREIGN KEY (`import_record_id`)
414 REFERENCES `import_records` (`import_record_id`) ON DELETE CASCADE ON UPDATE CASCADE,
415 KEY `matched_biblionumber` (`matched_biblionumber`),
416 KEY `title` (`title`),
417 KEY `isbn` (`isbn`)
418 ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
419 $dbh->do("CREATE TABLE `import_items` (
420 `import_items_id` int(11) NOT NULL auto_increment,
421 `import_record_id` int(11) NOT NULL,
422 `itemnumber` int(11) default NULL,
423 `branchcode` varchar(10) default NULL,
424 `status` enum('error', 'staged', 'imported', 'reverted') NOT NULL default 'staged',
425 `marcxml` longtext NOT NULL,
426 `import_error` mediumtext,
427 PRIMARY KEY (`import_items_id`),
428 CONSTRAINT `import_items_ibfk_1` FOREIGN KEY (`import_record_id`)
429 REFERENCES `import_records` (`import_record_id`) ON DELETE CASCADE ON UPDATE CASCADE,
430 KEY `itemnumber` (`itemnumber`),
431 KEY `branchcode` (`branchcode`)
432 ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
433
434 $dbh->do("INSERT INTO `import_batches`
435 (`overlay_action`, `import_status`, `batch_type`, `file_name`)
436 SELECT distinct 'create_new', 'staged', 'z3950', `file`
437 FROM `marc_breeding`");
438
439 $dbh->do("INSERT INTO `import_records`
440 (`import_batch_id`, `import_record_id`, `record_sequence`, `marc`, `record_type`, `status`,
441 `encoding`, `z3950random`, `marcxml`, `marcxml_old`)
442 SELECT `import_batch_id`, `id`, 1, `marc`, 'biblio', 'staged', `encoding`, `z3950random`, '', ''
443 FROM `marc_breeding`
444 JOIN `import_batches` ON (`file_name` = `file`)");
445
446 $dbh->do("INSERT INTO `import_biblios`
447 (`import_record_id`, `title`, `author`, `isbn`)
448 SELECT `import_record_id`, `title`, `author`, `isbn`
449 FROM `marc_breeding`
450 JOIN `import_records` ON (`import_record_id` = `id`)");
451
452 $dbh->do("UPDATE `import_batches`
453 SET `num_biblios` = (
454 SELECT COUNT(*)
455 FROM `import_records`
456 WHERE `import_batch_id` = `import_batches`.`import_batch_id`
457 )");
458
459 $dbh->do("DROP TABLE `marc_breeding`");
460
461 print "Upgrade to $DBversion done (import_batches et al. added)\n";
462 SetVersion ($DBversion);
463 }
464
465 $DBversion = "3.00.00.014";
466 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
467 $dbh->do("ALTER TABLE subscription ADD lastbranch VARCHAR(4)");
468 print "Upgrade to $DBversion done (userid index added)\n";
469 SetVersion ($DBversion);
470 }
471
472 $DBversion = "3.00.00.015";
473 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
474 $dbh->do("CREATE TABLE `saved_sql` (
475 `id` int(11) NOT NULL auto_increment,
476 `borrowernumber` int(11) default NULL,
477 `date_created` datetime default NULL,
478 `last_modified` datetime default NULL,
479 `savedsql` text,
480 `last_run` datetime default NULL,
481 `report_name` varchar(255) default NULL,
482 `type` varchar(255) default NULL,
483 `notes` text,
484 PRIMARY KEY (`id`),
485 KEY boridx (`borrowernumber`)
486 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;");
487 $dbh->do("CREATE TABLE `saved_reports` (
488 `id` int(11) NOT NULL auto_increment,
489 `report_id` int(11) default NULL,
490 `report` longtext,
491 `date_run` datetime default NULL,
492 PRIMARY KEY (`id`)
493 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;");
494 print "Upgrade to $DBversion done (saved_sql and saved_reports added)\n";
495 SetVersion ($DBversion);
496 }
497
498 $DBversion = "3.00.00.016";
499 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
500 $dbh->do(" CREATE TABLE reports_dictionary (
501 id int(11) NOT NULL auto_increment,
502 name varchar(255) default NULL,
503 description text,
504 date_created datetime default NULL,
505 date_modified datetime default NULL,
506 saved_sql text,
507 area int(11) default NULL,
508 PRIMARY KEY (id)
509 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ");
510 print "Upgrade to $DBversion done (reports_dictionary) added)\n";
511 SetVersion ($DBversion);
512 }
513
514 $DBversion = "3.00.00.017";
515 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
516 $dbh->do("ALTER TABLE action_logs DROP PRIMARY KEY");
517 $dbh->do("ALTER TABLE action_logs ADD KEY timestamp (timestamp,user)");
518 $dbh->do("ALTER TABLE action_logs ADD action_id INT(11) NOT NULL FIRST");
519 $dbh->do("UPDATE action_logs SET action_id = if (\@a, \@a:=\@a+1, \@a:=1)");
520 $dbh->do("ALTER TABLE action_logs MODIFY action_id INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY");
521 print "Upgrade to $DBversion done (added column to action_logs)\n";
522 SetVersion ($DBversion);
523 }
524
525 $DBversion = "3.00.00.018";
526 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
527 $dbh->do("ALTER TABLE `zebraqueue`
528 ADD `done` INT NOT NULL DEFAULT '0',
529 ADD `time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ;
530 ");
531 print "Upgrade to $DBversion done (adding timestamp and done columns to zebraque table to improve problem tracking) added)\n";
532 SetVersion ($DBversion);
533 }
534
535 $DBversion = "3.00.00.019";
536 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
537 $dbh->do("ALTER TABLE biblio MODIFY biblionumber INT(11) NOT NULL AUTO_INCREMENT");
538 $dbh->do("ALTER TABLE biblioitems MODIFY biblioitemnumber INT(11) NOT NULL AUTO_INCREMENT");
539 $dbh->do("ALTER TABLE items MODIFY itemnumber INT(11) NOT NULL AUTO_INCREMENT");
540 print "Upgrade to $DBversion done (made bib/item PKs auto_increment)\n";
541 SetVersion ($DBversion);
542 }
543
544 $DBversion = "3.00.00.020";
545 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
546 $dbh->do("ALTER TABLE deleteditems
547 DROP KEY `delitembarcodeidx`,
548 ADD KEY `delitembarcodeidx` (`barcode`)");
549 print "Upgrade to $DBversion done (dropped uniqueness of key on deleteditems.barcode)\n";
550 SetVersion ($DBversion);
551 }
552
553 $DBversion = "3.00.00.021";
554 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
555 $dbh->do("ALTER TABLE items CHANGE homebranch homebranch VARCHAR(10)");
556 $dbh->do("ALTER TABLE deleteditems CHANGE homebranch homebranch VARCHAR(10)");
557 $dbh->do("ALTER TABLE statistics CHANGE branch branch VARCHAR(10)");
558 $dbh->do("ALTER TABLE subscription CHANGE lastbranch lastbranch VARCHAR(10)");
559 print "Upgrade to $DBversion done (extended missed branchcode columns to 10 chars)\n";
560 SetVersion ($DBversion);
561 }
562
563 $DBversion = "3.00.00.022";
564 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
565 $dbh->do("ALTER TABLE items
566 ADD `damaged` tinyint(1) default NULL AFTER notforloan");
567 $dbh->do("ALTER TABLE deleteditems
568 ADD `damaged` tinyint(1) default NULL AFTER notforloan");
569 print "Upgrade to $DBversion done (adding damaged column to items table)\n";
570 SetVersion ($DBversion);
571 }
572
573 $DBversion = "3.00.00.023";
574 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
575 $dbh->do("INSERT INTO `systempreferences` (variable,value,options,explanation,type)
576 VALUES ('yuipath','http://yui.yahooapis.com/2.3.1/build','Insert the path to YUI libraries','','free')");
577 print "Upgrade to $DBversion done (adding new system preference for controlling YUI path)\n";
578 SetVersion ($DBversion);
579 }
580 $DBversion = "3.00.00.024";
581 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
582 $dbh->do("ALTER TABLE biblioitems CHANGE itemtype itemtype VARCHAR(10)");
583 print "Upgrade to $DBversion done (changing itemtype to (10))\n";
584 SetVersion ($DBversion);
585 }
586
587 $DBversion = "3.00.00.025";
588 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
589 $dbh->do("ALTER TABLE items ADD COLUMN itype VARCHAR(10)");
590 $dbh->do("ALTER TABLE deleteditems ADD COLUMN itype VARCHAR(10) AFTER uri");
591 if(C4::Context->preference('item-level_itypes')){
592 $dbh->do('update items,biblioitems set items.itype=biblioitems.itemtype where items.biblionumber=biblioitems.biblionumber and itype is null');
593 }
594 print "Upgrade to $DBversion done (reintroduce items.itype - fill from itemtype)\n ";
595 SetVersion ($DBversion);
596 }
597
598 $DBversion = "3.00.00.026";
599 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
600 $dbh->do("INSERT INTO `systempreferences` (variable,value,options,explanation,type)
601 VALUES ('HomeOrHoldingBranch','homebranch','homebranch|holdingbranch','With independent branches turned on this decides whether to check the items holdingbranch or homebranch at circulatilon','choice')");
602 print "Upgrade to $DBversion done (adding new system preference for choosing whether homebranch or holdingbranch is checked in circulation)\n";
603 SetVersion ($DBversion);
604 }
605
606 $DBversion = "3.00.00.027";
607 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
608 $dbh->do("CREATE TABLE `marc_matchers` (
609 `matcher_id` int(11) NOT NULL auto_increment,
610 `code` varchar(10) NOT NULL default '',
611 `description` varchar(255) NOT NULL default '',
612 `record_type` varchar(10) NOT NULL default 'biblio',
613 `threshold` int(11) NOT NULL default 0,
614 PRIMARY KEY (`matcher_id`),
615 KEY `code` (`code`),
616 KEY `record_type` (`record_type`)
617 ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
618 $dbh->do("CREATE TABLE `matchpoints` (
619 `matcher_id` int(11) NOT NULL,
620 `matchpoint_id` int(11) NOT NULL auto_increment,
621 `search_index` varchar(30) NOT NULL default '',
622 `score` int(11) NOT NULL default 0,
623 PRIMARY KEY (`matchpoint_id`),
624 CONSTRAINT `matchpoints_ifbk_1` FOREIGN KEY (`matcher_id`)
625 REFERENCES `marc_matchers` (`matcher_id`) ON DELETE CASCADE ON UPDATE CASCADE
626 ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
627 $dbh->do("CREATE TABLE `matchpoint_components` (
628 `matchpoint_id` int(11) NOT NULL,
629 `matchpoint_component_id` int(11) NOT NULL auto_increment,
630 sequence int(11) NOT NULL default 0,
631 tag varchar(3) NOT NULL default '',
632 subfields varchar(40) NOT NULL default '',
633 offset int(4) NOT NULL default 0,
634 length int(4) NOT NULL default 0,
635 PRIMARY KEY (`matchpoint_component_id`),
636 KEY `by_sequence` (`matchpoint_id`, `sequence`),
637 CONSTRAINT `matchpoint_components_ifbk_1` FOREIGN KEY (`matchpoint_id`)
638 REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE
639 ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
640 $dbh->do("CREATE TABLE `matchpoint_component_norms` (
641 `matchpoint_component_id` int(11) NOT NULL,
642 `sequence` int(11) NOT NULL default 0,
643 `norm_routine` varchar(50) NOT NULL default '',
644 KEY `matchpoint_component_norms` (`matchpoint_component_id`, `sequence`),
645 CONSTRAINT `matchpoint_component_norms_ifbk_1` FOREIGN KEY (`matchpoint_component_id`)
646 REFERENCES `matchpoint_components` (`matchpoint_component_id`) ON DELETE CASCADE ON UPDATE CASCADE
647 ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
648 $dbh->do("CREATE TABLE `matcher_matchpoints` (
649 `matcher_id` int(11) NOT NULL,
650 `matchpoint_id` int(11) NOT NULL,
651 CONSTRAINT `matcher_matchpoints_ifbk_1` FOREIGN KEY (`matcher_id`)
652 REFERENCES `marc_matchers` (`matcher_id`) ON DELETE CASCADE ON UPDATE CASCADE,
653 CONSTRAINT `matcher_matchpoints_ifbk_2` FOREIGN KEY (`matchpoint_id`)
654 REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE
655 ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
656 $dbh->do("CREATE TABLE `matchchecks` (
657 `matcher_id` int(11) NOT NULL,
658 `matchcheck_id` int(11) NOT NULL auto_increment,
659 `source_matchpoint_id` int(11) NOT NULL,
660 `target_matchpoint_id` int(11) NOT NULL,
661 PRIMARY KEY (`matchcheck_id`),
662 CONSTRAINT `matcher_matchchecks_ifbk_1` FOREIGN KEY (`matcher_id`)
663 REFERENCES `marc_matchers` (`matcher_id`) ON DELETE CASCADE ON UPDATE CASCADE,
664 CONSTRAINT `matcher_matchchecks_ifbk_2` FOREIGN KEY (`source_matchpoint_id`)
665 REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE,
666 CONSTRAINT `matcher_matchchecks_ifbk_3` FOREIGN KEY (`target_matchpoint_id`)
667 REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE
668 ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
669 print "Upgrade to $DBversion done (added C4::Matcher serialization tables)\n ";
670 SetVersion ($DBversion);
671 }
672
673 $DBversion = "3.00.00.028";
674 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
675 $dbh->do("INSERT INTO `systempreferences` (variable,value,options,explanation,type)
676 VALUES ('canreservefromotherbranches','1','','With Independent branches on, can a user from one library reserve an item from another library','YesNo')");
677 print "Upgrade to $DBversion done (adding new system preference for changing reserve/holds behaviour with independent branches)\n";
678 SetVersion ($DBversion);
679 }
680
681
682 $DBversion = "3.00.00.029";
683 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
684 $dbh->do("ALTER TABLE `import_batches` ADD `matcher_id` int(11) NULL AFTER `import_batch_id`");
685 print "Upgrade to $DBversion done (adding matcher_id to import_batches)\n";
686 SetVersion ($DBversion);
687 }
688
689 $DBversion = "3.00.00.030";
690 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
691 $dbh->do("
692 CREATE TABLE services_throttle (
693 service_type varchar(10) NOT NULL default '',
694 service_count varchar(45) default NULL,
695 PRIMARY KEY (service_type)
696 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
697 ");
698 $dbh->do("INSERT INTO `systempreferences` (variable,value,options,explanation,type)
699 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')");
700 $dbh->do("INSERT INTO `systempreferences` (variable,value,options,explanation,type)
701 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')");
702 $dbh->do("INSERT INTO `systempreferences` (variable,value,options,explanation,type)
703 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')");
704 $dbh->do("INSERT INTO `systempreferences` (variable,value,options,explanation,type)
705 VALUES ('XISBNDailyLimit',499,'','The xISBN Web service is free for non-commercial use when usage does not exceed 500 requests per day','free')");
706 $dbh->do("INSERT INTO `systempreferences` (variable,value,options,explanation,type)
707 VALUES ('PINESISBN',0,'','Use with FRBRizeEditions. If ON, Koha will use PINES OISBN web service in the Editions tab on the detail pages.','YesNo')");
708 $dbh->do("INSERT INTO `systempreferences` (variable,value,options,explanation,type)
709 VALUES ('ThingISBN',0,'','Use with FRBRizeEditions. If ON, Koha will use the ThingISBN web service in the Editions tab on the detail pages.','YesNo')");
710 print "Upgrade to $DBversion done (adding services throttle table and sysprefs for xISBN)\n";
711 SetVersion ($DBversion);
712 }
713
714 $DBversion = "3.00.00.031";
715 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
716
717 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('QueryStemming',1,'If ON, enables query stemming',NULL,'YesNo')");
718 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('QueryFuzzy',1,'If ON, enables fuzzy option for searches',NULL,'YesNo')");
719 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('QueryWeightFields',1,'If ON, enables field weighting',NULL,'YesNo')");
720 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('WebBasedSelfCheck',0,'If ON, enables the web-based self-check system',NULL,'YesNo')");
721 $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')");
722 $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')");
723 $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')");
724 $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')");
725 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('defaultSortOrder',NULL,'Specify the default sort order','asc|dsc|az|za','Choice')");
726 $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')");
727 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('OPACdefaultSortOrder',NULL,'Specify the default sort order','asc|dsc|za|az','Choice')");
728 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('staffClientBaseURL','','Specify the base URL of the staff client',NULL,'free')");
729 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('minPasswordLength',3,'Specify the minimum length of a patron/staff password',NULL,'free')");
730 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('noItemTypeImages',0,'If ON, disables item-type images',NULL,'YesNo')");
731 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('noOPACHolds',0,'If ON, disables holds globally',NULL,'YesNo')");
732 $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')");
733 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('holdCancelLength','','Specify how many days before a hold is canceled',NULL,'free')");
734 $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')");
735 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('finesMode','test','Choose the fines mode, test or production','test|production','Choice')");
736 $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')");
737 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('itemBarcodeInputFilter','','If set, allows specification of a item barcode input filter','cuecat','Choice')");
738 $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')");
739 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('URLLinkText','','Text to display as the link anchor in the OPAC',NULL,'free')");
740 $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')");
741 $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')");
742 $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')");
743 $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')");
744 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('OPACUserCSS',0,'Add CSS to be included in the OPAC',NULL,'free')");
745 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('emailPurchaseSuggestions',0,'If ON, patron suggestions are emailed rather than managed in Acquisitions',NULL,'YesNo')");
746
747 print "Upgrade to $DBversion done (adding additional system preference)\n";
748 SetVersion ($DBversion);
749 }
750
751 $DBversion = "3.00.00.032";
752 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
753 $dbh->do("UPDATE `marc_subfield_structure` SET `kohafield` = 'items.wthdrawn' WHERE `kohafield` = 'items.withdrawn'");
754 print "Upgrade to $DBversion done (fixed MARC framework references to items.withdrawn)\n";
755 SetVersion ($DBversion);
756 }
757
758 $DBversion = "3.00.00.033";
759 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
760 $dbh->do("INSERT INTO `userflags` VALUES(17,'staffaccess','Modify login / permissions for staff users',0)");
761 print "Upgrade to $DBversion done (Adding permissions flag for staff member access modification. )\n";
762 SetVersion ($DBversion);
763 }
764
765 $DBversion = "3.00.00.034";
766 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
767 $dbh->do("ALTER TABLE `virtualshelves` ADD COLUMN `sortfield` VARCHAR(16) ");
768 print "Upgrade to $DBversion done (Adding sortfield for Virtual Shelves. )\n";
769 SetVersion ($DBversion);
770 }
771
772 $DBversion = "3.00.00.035";
773 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
774 $dbh->do("UPDATE marc_subfield_structure
775 SET authorised_value = 'cn_source'
776 WHERE kohafield IN ('items.cn_source', 'biblioitems.cn_source')
777 AND (authorised_value is NULL OR authorised_value = '')");
778 print "Upgrade to $DBversion done (MARC frameworks: make classification source a drop-down)\n";
779 SetVersion ($DBversion);
780 }
781
782 $DBversion = "3.00.00.036";
783 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
784 $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');");
785 print "Upgrade to $DBversion done (OPACItemsResultsDisplay systempreference added)\n";
786 SetVersion ($DBversion);
787 }
788
789 $DBversion = "3.00.00.037";
790 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
791 $dbh->do("ALTER TABLE `borrowers` ADD COLUMN `altcontactfirstname` varchar(255)");
792 $dbh->do("ALTER TABLE `borrowers` ADD COLUMN `altcontactsurname` varchar(255)");
793 $dbh->do("ALTER TABLE `borrowers` ADD COLUMN `altcontactaddress1` varchar(255)");
794 $dbh->do("ALTER TABLE `borrowers` ADD COLUMN `altcontactaddress2` varchar(255)");
795 $dbh->do("ALTER TABLE `borrowers` ADD COLUMN `altcontactaddress3` varchar(255)");
796 $dbh->do("ALTER TABLE `borrowers` ADD COLUMN `altcontactzipcode` varchar(50)");
797 $dbh->do("ALTER TABLE `borrowers` ADD COLUMN `altcontactphone` varchar(50)");
798 print "Upgrade to $DBversion done (Adding Alternative Contact Person information to borrowers table)\n";
799 SetVersion ($DBversion);
800 }
801
802 $DBversion = "3.00.00.038";
803 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
804 $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'");
805 $dbh->do("DELETE FROM `systempreferences` WHERE variable='hideBiblioNumber'");
806 print "Upgrade to $DBversion done ('alter finesMode systempreference, remove superfluous syspref.')\n";
807 SetVersion ($DBversion);
808 }
809
810 $DBversion = "3.00.00.039";
811 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
812 $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')");
813 $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')");
814 $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')");
815 $dbh->do("DELETE FROM `systempreferences` WHERE variable='HomeOrHoldingBranch'");
816 print "Upgrade to $DBversion done ('add circ sysprefs CircControl, finesCalendar, and uppercasesurnames, and delete HomeOrHoldingBranch.')\n";
817 SetVersion ($DBversion);
818 }
819
820 $DBversion = "3.00.00.040";
821 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
822 $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')");
823 $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')");
824 print "Upgrade to $DBversion done ('add circ sysprefs todaysIssuesDefaultSortOrder and previousIssuesDefaultSortOrder.')\n";
825 SetVersion ($DBversion);
826 }
827
828
829 $DBversion = "3.00.00.041";
830 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
831 # Strictly speaking it is not necessary to explicitly change
832 # NULL values to 0, because the ALTER TABLE statement will do that.
833 # However, setting them first avoids a warning.
834 $dbh->do("UPDATE items SET notforloan = 0 WHERE notforloan IS NULL");
835 $dbh->do("UPDATE items SET damaged = 0 WHERE damaged IS NULL");
836 $dbh->do("UPDATE items SET itemlost = 0 WHERE itemlost IS NULL");
837 $dbh->do("UPDATE items SET wthdrawn = 0 WHERE wthdrawn IS NULL");
838 $dbh->do("ALTER TABLE items
839 MODIFY notforloan tinyint(1) NOT NULL default 0,
840 MODIFY damaged tinyint(1) NOT NULL default 0,
841 MODIFY itemlost tinyint(1) NOT NULL default 0,
842 MODIFY wthdrawn tinyint(1) NOT NULL default 0");
843 $dbh->do("UPDATE deleteditems SET notforloan = 0 WHERE notforloan IS NULL");
844 $dbh->do("UPDATE deleteditems SET damaged = 0 WHERE damaged IS NULL");
845 $dbh->do("UPDATE deleteditems SET itemlost = 0 WHERE itemlost IS NULL");
846 $dbh->do("UPDATE deleteditems SET wthdrawn = 0 WHERE wthdrawn IS NULL");
847 $dbh->do("ALTER TABLE deleteditems
848 MODIFY notforloan tinyint(1) NOT NULL default 0,
849 MODIFY damaged tinyint(1) NOT NULL default 0,
850 MODIFY itemlost tinyint(1) NOT NULL default 0,
851 MODIFY wthdrawn tinyint(1) NOT NULL default 0");
852 print "Upgrade to $DBversion done (disallow NULL in several item status columns)\n";
853 SetVersion ($DBversion);
854 }
855
856 $DBversion = "3.00.00.042";
857 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
858 $dbh->do("ALTER TABLE aqbooksellers CHANGE name name mediumtext NOT NULL");
859 print "Upgrade to $DBversion done (disallow NULL in aqbooksellers.name; part of fix for bug 1251)\n";
860 SetVersion ($DBversion);
861 }
862
863 $DBversion = "3.00.00.043";
864 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
865 $dbh->do("ALTER TABLE `currency` ADD `symbol` varchar(5) default NULL AFTER currency, ADD `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP AFTER symbol");
866 print "Upgrade to $DBversion done (currency table: add symbol and timestamp columns)\n";
867 SetVersion ($DBversion);
868 }
869
870 $DBversion = "3.00.00.044";
871 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
872 $dbh->do("ALTER TABLE deletedborrowers
873 ADD `altcontactfirstname` varchar(255) default NULL,
874 ADD `altcontactsurname` varchar(255) default NULL,
875 ADD `altcontactaddress1` varchar(255) default NULL,
876 ADD `altcontactaddress2` varchar(255) default NULL,
877 ADD `altcontactaddress3` varchar(255) default NULL,
878 ADD `altcontactzipcode` varchar(50) default NULL,
879 ADD `altcontactphone` varchar(50) default NULL
880 ");
881 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES
882 ('OPACBaseURL',NULL,'Specify the Base URL of the OPAC, e.g., opac.mylibrary.com, the http:// will be added automatically by Koha.',NULL,'Free'),
883 ('language','en','Set the default language in the staff client.',NULL,'Languages'),
884 ('QueryAutoTruncate',1,'If ON, query truncation is enabled by default',NULL,'YesNo'),
885 ('QueryRemoveStopwords',0,'If ON, stopwords listed in the Administration area will be removed from queries',NULL,'YesNo')
886 ");
887 print "Upgrade to $DBversion done (syncing deletedborrowers table with borrowers table)\n";
888 SetVersion ($DBversion);
889 }
890
891 #-- http://www.w3.org/International/articles/language-tags/
892
893 #-- RFC4646
894 $DBversion = "3.00.00.045";
895 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
896 $dbh->do("
897 CREATE TABLE language_subtag_registry (
898 subtag varchar(25),
899 type varchar(25), -- language-script-region-variant-extension-privateuse
900 description varchar(25), -- only one of the possible descriptions for ease of reference, see language_descriptions for the complete list
901 added date,
902 KEY `subtag` (`subtag`)
903 ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
904
905 #-- TODO: add suppress_scripts
906 #-- this maps three letter codes defined in iso639.2 back to their
907 #-- two letter equivilents in rfc4646 (LOC maintains iso639+)
908 $dbh->do("CREATE TABLE language_rfc4646_to_iso639 (
909 rfc4646_subtag varchar(25),
910 iso639_2_code varchar(25),
911 KEY `rfc4646_subtag` (`rfc4646_subtag`)
912 ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
913
914 $dbh->do("CREATE TABLE language_descriptions (
915 subtag varchar(25),
916 type varchar(25),
917 lang varchar(25),
918 description varchar(255),
919 KEY `lang` (`lang`)
920 ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
921
922 #-- bi-directional support, keyed by script subcode
923 $dbh->do("CREATE TABLE language_script_bidi (
924 rfc4646_subtag varchar(25), -- script subtag, Arab, Hebr, etc.
925 bidi varchar(3), -- rtl ltr
926 KEY `rfc4646_subtag` (`rfc4646_subtag`)
927 ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
928
929 #-- BIDI Stuff, Arabic and Hebrew
930 $dbh->do("INSERT INTO language_script_bidi(rfc4646_subtag,bidi)
931 VALUES( 'Arab', 'rtl')");
932 $dbh->do("INSERT INTO language_script_bidi(rfc4646_subtag,bidi)
933 VALUES( 'Hebr', 'rtl')");
934
935 #-- TODO: need to map language subtags to script subtags for detection
936 #-- of bidi when script is not specified (like ar, he)
937 $dbh->do("CREATE TABLE language_script_mapping (
938 language_subtag varchar(25),
939 script_subtag varchar(25),
940 KEY `language_subtag` (`language_subtag`)
941 ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
942
943 #-- Default mappings between script and language subcodes
944 $dbh->do("INSERT INTO language_script_mapping(language_subtag,script_subtag)
945 VALUES( 'ar', 'Arab')");
946 $dbh->do("INSERT INTO language_script_mapping(language_subtag,script_subtag)
947 VALUES( 'he', 'Hebr')");
948
949 print "Upgrade to $DBversion done (adding language subtag registry and basic BiDi support NOTE: You should import the subtag registry SQL)\n";
950 SetVersion ($DBversion);
951 }
952
953 $DBversion = "3.00.00.046";
954 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
955 $dbh->do("ALTER TABLE `subscription` CHANGE `numberlength` `numberlength` int(11) default '0' ,
956 CHANGE `weeklength` `weeklength` int(11) default '0'");
957 $dbh->do("CREATE TABLE `serialitems` (`serialid` int(11) NOT NULL, `itemnumber` int(11) NOT NULL, UNIQUE KEY `serialididx` (`serialid`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
958 $dbh->do("INSERT INTO `serialitems` SELECT `serialid`,`itemnumber` from serial where NOT ISNULL(itemnumber) && itemnumber <> '' && itemnumber NOT LIKE '%,%'");
959 print "Upgrade to $DBversion done (Add serialitems table to link serial issues to items. )\n";
960 SetVersion ($DBversion);
961 }
962
963 $DBversion = "3.00.00.047";
964 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
965 $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');");
966 print "Upgrade to $DBversion done ( Added OpacRenewalAllowed syspref )\n";
967 SetVersion ($DBversion);
968 }
969
970 $DBversion = "3.00.00.048";
971 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
972 $dbh->do("ALTER TABLE `items` ADD `more_subfields_xml` longtext default NULL AFTER `itype`");
973 print "Upgrade to $DBversion done (added items.more_subfields_xml)\n";
974 SetVersion ($DBversion);
975 }
976
977 $DBversion = "3.00.00.049";
978 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
979 $dbh->do("ALTER TABLE `z3950servers` ADD `encoding` text default NULL AFTER type ");
980 print "Upgrade to $DBversion done ( Added encoding field to z3950servers table )\n";
981 SetVersion ($DBversion);
982 }
983
984 $DBversion = "3.00.00.050";
985 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
986 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('OpacHighlightedWords','0','If Set, query matched terms are highlighted in OPAC',NULL,'YesNo');");
987 print "Upgrade to $DBversion done ( Added OpacHighlightedWords syspref )\n";
988 SetVersion ($DBversion);
989 }
990
991 $DBversion = "3.00.00.051";
992 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
993 $dbh->do("UPDATE systempreferences SET explanation = 'Define the current theme for the OPAC interface.' WHERE variable = 'opacthemes';");
994 print "Upgrade to $DBversion done ( Corrected opacthemes explanation. )\n";
995 SetVersion ($DBversion);
996 }
997
998 $DBversion = "3.00.00.052";
999 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1000 $dbh->do("ALTER TABLE `deleteditems` ADD `more_subfields_xml` LONGTEXT DEFAULT NULL AFTER `itype`");
1001 print "Upgrade to $DBversion done ( Adding missing column to deleteditems table. )\n";
1002 SetVersion ($DBversion);
1003 }
1004
1005 $DBversion = "3.00.00.053";
1006 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1007 $dbh->do("CREATE TABLE `printers_profile` (
1008 `prof_id` int(4) NOT NULL auto_increment,
1009 `printername` varchar(40) NOT NULL,
1010 `tmpl_id` int(4) NOT NULL,
1011 `paper_bin` varchar(20) NOT NULL,
1012 `offset_horz` float default NULL,
1013 `offset_vert` float default NULL,
1014 `creep_horz` float default NULL,
1015 `creep_vert` float default NULL,
1016 `unit` char(20) NOT NULL default 'POINT',
1017 PRIMARY KEY (`prof_id`),
1018 UNIQUE KEY `printername` (`printername`,`tmpl_id`,`paper_bin`),
1019 CONSTRAINT `printers_profile_pnfk_1` FOREIGN KEY (`tmpl_id`) REFERENCES `labels_templates` (`tmpl_id`) ON DELETE CASCADE ON UPDATE CASCADE
1020 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ");
1021 $dbh->do("CREATE TABLE `labels_profile` (
1022 `tmpl_id` int(4) NOT NULL,
1023 `prof_id` int(4) NOT NULL,
1024 UNIQUE KEY `tmpl_id` (`tmpl_id`),
1025 UNIQUE KEY `prof_id` (`prof_id`)
1026 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ");
1027 print "Upgrade to $DBversion done ( Printer Profile tables added )\n";
1028 SetVersion ($DBversion);
1029 }
1030
1031 $DBversion = "3.00.00.054";
1032 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1033 $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';");
1034 print "Upgrade to $DBversion done ( Added another barcode autogeneration sequence to barcode.pl. )\n";
1035 SetVersion ($DBversion);
1036 }
1037
1038 $DBversion = "3.00.00.055";
1039 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1040 $dbh->do("ALTER TABLE `zebraqueue` ADD KEY `zebraqueue_lookup` (`server`, `biblio_auth_number`, `operation`, `done`)");
1041 print "Upgrade to $DBversion done ( Added index on zebraqueue. )\n";
1042 SetVersion ($DBversion);
1043 }
1044 $DBversion = "3.00.00.056";
1045 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1046 if (C4::Context->preference("marcflavour") eq 'UNIMARC') {
1047 $dbh->do("INSERT INTO `marc_subfield_structure` (`tagfield`, `tagsubfield`, `liblibrarian`, `libopac`, `repeatable`, `mandatory`, `kohafield`, `tab`, `authorised_value` , `authtypecode`, `value_builder`, `isurl`, `hidden`, `frameworkcode`, `seealso`, `link`, `defaultvalue`) VALUES ('995', 'v', 'Note sur le N° de périodique','Note sur le N° de périodique', 0, 0, 'items.enumchron', 10, '', '', '', 0, 0, '', '', '', NULL) ");
1048 } else {
1049 $dbh->do("INSERT INTO `marc_subfield_structure` (`tagfield`, `tagsubfield`, `liblibrarian`, `libopac`, `repeatable`, `mandatory`, `kohafield`, `tab`, `authorised_value` , `authtypecode`, `value_builder`, `isurl`, `hidden`, `frameworkcode`, `seealso`, `link`, `defaultvalue`) VALUES ('952', 'h', 'Serial Enumeration / chronology','Serial Enumeration / chronology', 0, 0, 'items.enumchron', 10, '', '', '', 0, 0, '', '', '', NULL) ");
1050 }
1051 $dbh->do("ALTER TABLE `items` ADD `enumchron` VARCHAR(80) DEFAULT NULL;");
1052 print "Upgrade to $DBversion done ( Added item.enumchron column, and framework map to 952h )\n";
1053 SetVersion ($DBversion);
1054 }
1055
1056 $DBversion = "3.00.00.057";
1057 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1058 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('OAI-PMH','0','if ON, OAI-PMH server is enabled',NULL,'YesNo');");
1059 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('OAI-PMH:archiveID','KOHA-OAI-TEST','OAI-PMH archive identification',NULL,'Free');");
1060 $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');");
1061 $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');");
1062 $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');");
1063 SetVersion ($DBversion);
1064 }
1065
1066 $DBversion = "3.00.00.058";
1067 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1068 $dbh->do("ALTER TABLE `opac_news`
1069 CHANGE `lang` `lang` VARCHAR( 25 )
1070 CHARACTER SET utf8
1071 COLLATE utf8_general_ci
1072 NOT NULL default ''");
1073 print "Upgrade to $DBversion done ( lang field in opac_news made longer )\n";
1074 SetVersion ($DBversion);
1075 }
1076
1077 $DBversion = "3.00.00.059";
1078 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1079
1080 $dbh->do("CREATE TABLE IF NOT EXISTS `labels_templates` (
1081 `tmpl_id` int(4) NOT NULL auto_increment,
1082 `tmpl_code` char(100) default '',
1083 `tmpl_desc` char(100) default '',
1084 `page_width` float default '0',
1085 `page_height` float default '0',
1086 `label_width` float default '0',
1087 `label_height` float default '0',
1088 `topmargin` float default '0',
1089 `leftmargin` float default '0',
1090 `cols` int(2) default '0',
1091 `rows` int(2) default '0',
1092 `colgap` float default '0',
1093 `rowgap` float default '0',
1094 `active` int(1) default NULL,
1095 `units` char(20) default 'PX',
1096 `fontsize` int(4) NOT NULL default '3',
1097 PRIMARY KEY (`tmpl_id`)
1098 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;");
1099 $dbh->do("CREATE TABLE IF NOT EXISTS `printers_profile` (
1100 `prof_id` int(4) NOT NULL auto_increment,
1101 `printername` varchar(40) NOT NULL,
1102 `tmpl_id` int(4) NOT NULL,
1103 `paper_bin` varchar(20) NOT NULL,
1104 `offset_horz` float default NULL,
1105 `offset_vert` float default NULL,
1106 `creep_horz` float default NULL,
1107 `creep_vert` float default NULL,
1108 `unit` char(20) NOT NULL default 'POINT',
1109 PRIMARY KEY (`prof_id`),
1110 UNIQUE KEY `printername` (`printername`,`tmpl_id`,`paper_bin`),
1111 CONSTRAINT `printers_profile_pnfk_1` FOREIGN KEY (`tmpl_id`) REFERENCES `labels_templates` (`tmpl_id`) ON DELETE CASCADE ON UPDATE CASCADE
1112 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ");
1113 print "Upgrade to $DBversion done ( Added labels_templates table if it did not exist. )\n";
1114 SetVersion ($DBversion);
1115 }
1116
1117 $DBversion = "3.00.00.060";
1118 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1119 $dbh->do("CREATE TABLE IF NOT EXISTS `patronimage` (
1120 `cardnumber` varchar(16) NOT NULL,
1121 `mimetype` varchar(15) NOT NULL,
1122 `imagefile` mediumblob NOT NULL,
1123 PRIMARY KEY (`cardnumber`),
1124 CONSTRAINT `patronimage_fk1` FOREIGN KEY (`cardnumber`) REFERENCES `borrowers` (`cardnumber`) ON DELETE CASCADE ON UPDATE CASCADE
1125 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;");
1126 print "Upgrade to $DBversion done ( Added patronimage table. )\n";
1127 SetVersion ($DBversion);
1128 }
1129
1130 $DBversion = "3.00.00.061";
1131 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1132 $dbh->do("ALTER TABLE labels_templates ADD COLUMN font char(10) NOT NULL DEFAULT 'TR';");
1133 print "Upgrade to $DBversion done ( Added font column to labels_templates )\n";
1134 SetVersion ($DBversion);
1135 }
1136
1137 $DBversion = "3.00.00.062";
1138 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1139 $dbh->do("CREATE TABLE `old_issues` (
1140 `borrowernumber` int(11) default NULL,
1141 `itemnumber` int(11) default NULL,
1142 `date_due` date default NULL,
1143 `branchcode` varchar(10) default NULL,
1144 `issuingbranch` varchar(18) default NULL,
1145 `returndate` date default NULL,
1146 `lastreneweddate` date default NULL,
1147 `return` varchar(4) default NULL,
1148 `renewals` tinyint(4) default NULL,
1149 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1150 `issuedate` date default NULL,
1151 KEY `old_issuesborridx` (`borrowernumber`),
1152 KEY `old_issuesitemidx` (`itemnumber`),
1153 KEY `old_bordate` (`borrowernumber`,`timestamp`),
1154 CONSTRAINT `old_issues_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`)
1155 ON DELETE SET NULL ON UPDATE SET NULL,
1156 CONSTRAINT `old_issues_ibfk_2` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`)
1157 ON DELETE SET NULL ON UPDATE SET NULL
1158 ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
1159 $dbh->do("CREATE TABLE `old_reserves` (
1160 `borrowernumber` int(11) default NULL,
1161 `reservedate` date default NULL,
1162 `biblionumber` int(11) default NULL,
1163 `constrainttype` varchar(1) default NULL,
1164 `branchcode` varchar(10) default NULL,
1165 `notificationdate` date default NULL,
1166 `reminderdate` date default NULL,
1167 `cancellationdate` date default NULL,
1168 `reservenotes` mediumtext,
1169 `priority` smallint(6) default NULL,
1170 `found` varchar(1) default NULL,
1171 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1172 `itemnumber` int(11) default NULL,
1173 `waitingdate` date default NULL,
1174 KEY `old_reserves_borrowernumber` (`borrowernumber`),
1175 KEY `old_reserves_biblionumber` (`biblionumber`),
1176 KEY `old_reserves_itemnumber` (`itemnumber`),
1177 KEY `old_reserves_branchcode` (`branchcode`),
1178 CONSTRAINT `old_reserves_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`)
1179 ON DELETE SET NULL ON UPDATE SET NULL,
1180 CONSTRAINT `old_reserves_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`)
1181 ON DELETE SET NULL ON UPDATE SET NULL,
1182 CONSTRAINT `old_reserves_ibfk_3` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`)
1183 ON DELETE SET NULL ON UPDATE SET NULL
1184 ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
1185
1186 # move closed transactions to old_* tables
1187 $dbh->do("INSERT INTO old_issues SELECT * FROM issues WHERE returndate IS NOT NULL");
1188 $dbh->do("DELETE FROM issues WHERE returndate IS NOT NULL");
1189 $dbh->do("INSERT INTO old_reserves SELECT * FROM reserves WHERE cancellationdate IS NOT NULL OR found = 'F'");
1190 $dbh->do("DELETE FROM reserves WHERE cancellationdate IS NOT NULL OR found = 'F'");
1191
1192 print "Upgrade to $DBversion done ( Added old_issues and old_reserves tables )\n";
1193 SetVersion ($DBversion);
1194 }
1195
1196 $DBversion = "3.00.00.063";
1197 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1198 $dbh->do("ALTER TABLE deleteditems
1199 CHANGE COLUMN booksellerid booksellerid MEDIUMTEXT DEFAULT NULL,
1200 ADD COLUMN enumchron VARCHAR(80) DEFAULT NULL AFTER more_subfields_xml,
1201 ADD COLUMN copynumber SMALLINT(6) DEFAULT NULL AFTER enumchron;");
1202 $dbh->do("ALTER TABLE items
1203 CHANGE COLUMN booksellerid booksellerid MEDIUMTEXT,
1204 ADD COLUMN copynumber SMALLINT(6) DEFAULT NULL AFTER enumchron;");
1205 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";
1206 SetVersion ($DBversion);
1207 }
1208
1209 $DBversion = "3.00.00.064";
1210 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1211 $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');");
1212 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('AWSAccessKeyID','','See: http://aws.amazon.com','','free');");
1213 $dbh->do("DELETE FROM `systempreferences` WHERE variable='AmazonDevKey';");
1214 $dbh->do("DELETE FROM `systempreferences` WHERE variable='XISBNAmazonSimilarItems';");
1215 $dbh->do("DELETE FROM `systempreferences` WHERE variable='OPACXISBNAmazonSimilarItems';");
1216 print "Upgrade to $DBversion done (IMPORTANT: Upgrading to Amazon.com Associates Web Service 4.0 ) \n";
1217 SetVersion ($DBversion);
1218 }
1219
1220 $DBversion = "3.00.00.065";
1221 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1222 $dbh->do("CREATE TABLE `patroncards` (
1223 `cardid` int(11) NOT NULL auto_increment,
1224 `batch_id` varchar(10) NOT NULL default '1',
1225 `borrowernumber` int(11) NOT NULL,
1226 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1227 PRIMARY KEY (`cardid`),
1228 KEY `patroncards_ibfk_1` (`borrowernumber`),
1229 CONSTRAINT `patroncards_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE
1230 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;");
1231 print "Upgrade to $DBversion done (Adding patroncards table for patroncards generation feature. ) \n";
1232 SetVersion ($DBversion);
1233 }
1234
1235 $DBversion = "3.00.00.066";
1236 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1237 $dbh->do("ALTER TABLE `virtualshelfcontents` MODIFY `dateadded` timestamp NOT NULL
1238 DEFAULT CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP;
1239 ");
1240 print "Upgrade to $DBversion done (fix for bug 1873: virtualshelfcontents dateadded column empty. ) \n";
1241 SetVersion ($DBversion);
1242 }
1243
1244 $DBversion = "3.00.00.067";
1245 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1246 $dbh->do("UPDATE systempreferences SET explanation = 'Enable patron images for the Staff Client', type = 'YesNo' WHERE variable = 'patronimages'");
1247 print "Upgrade to $DBversion done (Updating patronimages syspref to reflect current kohastructure.sql. ) \n";
1248 SetVersion ($DBversion);
1249 }
1250
1251 $DBversion = "3.00.00.068";
1252 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1253 $dbh->do("CREATE TABLE `permissions` (
1254 `module_bit` int(11) NOT NULL DEFAULT 0,
1255 `code` varchar(30) DEFAULT NULL,
1256 `description` varchar(255) DEFAULT NULL,
1257 PRIMARY KEY (`module_bit`, `code`),
1258 CONSTRAINT `permissions_ibfk_1` FOREIGN KEY (`module_bit`) REFERENCES `userflags` (`bit`)
1259 ON DELETE CASCADE ON UPDATE CASCADE
1260 ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
1261 $dbh->do("CREATE TABLE `user_permissions` (
1262 `borrowernumber` int(11) NOT NULL DEFAULT 0,
1263 `module_bit` int(11) NOT NULL DEFAULT 0,
1264 `code` varchar(30) DEFAULT NULL,
1265 CONSTRAINT `user_permissions_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`)
1266 ON DELETE CASCADE ON UPDATE CASCADE,
1267 CONSTRAINT `user_permissions_ibfk_2` FOREIGN KEY (`module_bit`, `code`)
1268 REFERENCES `permissions` (`module_bit`, `code`)
1269 ON DELETE CASCADE ON UPDATE CASCADE
1270 ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
1271
1272 $dbh->do("INSERT INTO permissions (module_bit, code, description) VALUES
1273 (13, 'edit_news', 'Write news for the OPAC and staff interfaces'),
1274 (13, 'label_creator', 'Create printable labels and barcodes from catalog and patron data'),
1275 (13, 'edit_calendar', 'Define days when the library is closed'),
1276 (13, 'moderate_comments', 'Moderate patron comments'),
1277 (13, 'edit_notices', 'Define notices'),
1278 (13, 'edit_notice_status_triggers', 'Set notice/status triggers for overdue items'),
1279 (13, 'view_system_logs', 'Browse the system logs'),
1280 (13, 'inventory', 'Perform inventory (stocktaking) of your catalogue'),
1281 (13, 'stage_marc_import', 'Stage MARC records into the reservoir'),
1282 (13, 'manage_staged_marc', 'Managed staged MARC records, including completing and reversing imports'),
1283 (13, 'export_catalog', 'Export bibliographic and holdings data'),
1284 (13, 'import_patrons', 'Import patron data'),
1285 (13, 'delete_anonymize_patrons', 'Delete old borrowers and anonymize circulation history (deletes borrower reading history)'),
1286 (13, 'batch_upload_patron_images', 'Upload patron images in batch or one at a time'),
1287 (13, 'schedule_tasks', 'Schedule tasks to run')");
1288
1289 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('GranularPermissions','0','Use detailed staff user permissions',NULL,'YesNo')");
1290
1291 print "Upgrade to $DBversion done (adding permissions and user_permissions tables and GranularPermissions syspref) \n";
1292 SetVersion ($DBversion);
1293 }
1294 $DBversion = "3.00.00.069";
1295 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1296 $dbh->do("ALTER TABLE labels_conf CHANGE COLUMN class classification int(1) DEFAULT NULL;");
1297 print "Upgrade to $DBversion done ( Correcting columname in labels_conf )\n";
1298 SetVersion ($DBversion);
1299 }
1300
1301 $DBversion = "3.00.00.070";
1302 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1303 $sth = $dbh->prepare("SELECT value FROM systempreferences WHERE variable='yuipath'");
1304 $sth->execute;
1305 my ($value) = $sth->fetchrow;
1306 $value =~ s/2.3.1/2.5.1/;
1307 $dbh->do("UPDATE systempreferences SET value='$value' WHERE variable='yuipath';");
1308 print "Update yuipath syspref to 2.5.1 if necessary\n";
1309 SetVersion ($DBversion);
1310 }
1311
1312 $DBversion = "3.00.00.071";
1313 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1314 $dbh->do(" ALTER TABLE `subscription` ADD `serialsadditems` TINYINT( 1 ) NOT NULL DEFAULT '0';");
1315 # fill the new field with the previous systempreference value, then drop the syspref
1316 my $sth = $dbh->prepare("SELECT value FROM systempreferences WHERE variable='serialsadditems'");
1317 $sth->execute;
1318 my ($serialsadditems) = $sth->fetchrow();
1319 $dbh->do("UPDATE subscription SET serialsadditems=$serialsadditems");
1320 $dbh->do("DELETE FROM systempreferences WHERE variable='serialsadditems'");
1321 print "Upgrade to $DBversion done ( moving serialsadditems from syspref to subscription )\n";
1322 SetVersion ($DBversion);
1323 }
1324
1325 $DBversion = "3.00.00.072";
1326 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1327 $dbh->do("ALTER TABLE labels_conf ADD COLUMN formatstring VARCHAR(64) DEFAULT NULL AFTER printingtype");
1328 print "Upgrade to $DBversion done ( Adding format string to labels generator. )\n";
1329 SetVersion ($DBversion);
1330 }
1331
1332 $DBversion = "3.00.00.073";
1333 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1334 $dbh->do("DROP TABLE IF EXISTS `tags_all`;");
1335 $dbh->do(q#
1336 CREATE TABLE `tags_all` (
1337 `tag_id` int(11) NOT NULL auto_increment,
1338 `borrowernumber` int(11) NOT NULL,
1339 `biblionumber` int(11) NOT NULL,
1340 `term` varchar(255) NOT NULL,
1341 `language` int(4) default NULL,
1342 `date_created` datetime NOT NULL,
1343 PRIMARY KEY (`tag_id`),
1344 KEY `tags_borrowers_fk_1` (`borrowernumber`),
1345 KEY `tags_biblionumber_fk_1` (`biblionumber`),
1346 CONSTRAINT `tags_borrowers_fk_1` FOREIGN KEY (`borrowernumber`)
1347 REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1348 CONSTRAINT `tags_biblionumber_fk_1` FOREIGN KEY (`biblionumber`)
1349 REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE
1350 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1351 #);
1352 $dbh->do("DROP TABLE IF EXISTS `tags_approval`;");
1353 $dbh->do(q#
1354 CREATE TABLE `tags_approval` (
1355 `term` varchar(255) NOT NULL,
1356 `approved` int(1) NOT NULL default '0',
1357 `date_approved` datetime default NULL,
1358 `approved_by` int(11) default NULL,
1359 `weight_total` int(9) NOT NULL default '1',
1360 PRIMARY KEY (`term`),
1361 KEY `tags_approval_borrowers_fk_1` (`approved_by`),
1362 CONSTRAINT `tags_approval_borrowers_fk_1` FOREIGN KEY (`approved_by`)
1363 REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE
1364 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1365 #);
1366 $dbh->do("DROP TABLE IF EXISTS `tags_index`;");
1367 $dbh->do(q#
1368 CREATE TABLE `tags_index` (
1369 `term` varchar(255) NOT NULL,
1370 `biblionumber` int(11) NOT NULL,
1371 `weight` int(9) NOT NULL default '1',
1372 PRIMARY KEY (`term`,`biblionumber`),
1373 KEY `tags_index_biblionumber_fk_1` (`biblionumber`),
1374 CONSTRAINT `tags_index_term_fk_1` FOREIGN KEY (`term`)
1375 REFERENCES `tags_approval` (`term`) ON DELETE CASCADE ON UPDATE CASCADE,
1376 CONSTRAINT `tags_index_biblionumber_fk_1` FOREIGN KEY (`biblionumber`)
1377 REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE
1378 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1379 #);
1380 $dbh->do(q#
1381 INSERT INTO `systempreferences` VALUES
1382 ('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=',''),
1383 ('BakerTaylorEnabled','0','','Enable or disable all Baker & Taylor features.','YesNo'),
1384 ('BakerTaylorPassword','','','Baker & Taylor Password for Content Cafe (external content)','Textarea'),
1385 ('BakerTaylorUsername','','','Baker & Taylor Username for Content Cafe (external content)','Textarea'),
1386 ('TagsEnabled','1','','Enables or disables all tagging features. This is the main switch for tags.','YesNo'),
1387 ('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.',''),
1388 ('TagsInputOnDetail','1','','Allow users to input tags from the detail page.', 'YesNo'),
1389 ('TagsInputOnList', '0','','Allow users to input tags from the search results list.', 'YesNo'),
1390 ('TagsModeration', NULL,'','Require tags from patrons to be approved before becoming visible.','YesNo'),
1391 ('TagsShowOnDetail','10','','Number of tags to display on detail page. 0 is off.', 'Integer'),
1392 ('TagsShowOnList', '6','','Number of tags to display on search results list. 0 is off.','Integer')
1393 #);
1394 print "Upgrade to $DBversion done (Baker/Taylor,Tags: sysprefs and tables (tags_all, tags_index, tags_approval)) \n";
1395 SetVersion ($DBversion);
1396 }
1397
1398 $DBversion = "3.00.00.074";
1399 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1400 $dbh->do( q(update itemtypes set imageurl = concat( 'npl/', imageurl )
1401 where imageurl not like 'http%'
1402 and imageurl is not NULL
1403 and imageurl != '') );
1404 print "Upgrade to $DBversion done (updating imagetype.imageurls to reflect new icon locations.)\n";
1405 SetVersion ($DBversion);
1406 }
1407
1408 $DBversion = "3.00.00.075";
1409 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1410 $dbh->do( q(alter table authorised_values add imageurl varchar(200) default NULL) );
1411 print "Upgrade to $DBversion done (adding imageurl field to authorised_values table)\n";
1412 SetVersion ($DBversion);
1413 }
1414
1415 $DBversion = "3.00.00.076";
1416 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1417 $dbh->do("ALTER TABLE import_batches
1418 ADD COLUMN nomatch_action enum('create_new', 'ignore') NOT NULL default 'create_new' AFTER overlay_action");
1419 $dbh->do("ALTER TABLE import_batches
1420 ADD COLUMN item_action enum('always_add', 'add_only_for_matches', 'add_only_for_new', 'ignore')
1421 NOT NULL default 'always_add' AFTER nomatch_action");
1422 $dbh->do("ALTER TABLE import_batches
1423 MODIFY overlay_action enum('replace', 'create_new', 'use_template', 'ignore')
1424 NOT NULL default 'create_new'");
1425 $dbh->do("ALTER TABLE import_records
1426 MODIFY status enum('error', 'staged', 'imported', 'reverted', 'items_reverted',
1427 'ignored') NOT NULL default 'staged'");
1428 $dbh->do("ALTER TABLE import_items
1429 MODIFY status enum('error', 'staged', 'imported', 'reverted', 'ignored') NOT NULL default 'staged'");
1430
1431 print "Upgrade to $DBversion done (changes to import_batches and import_records)\n";
1432 SetVersion ($DBversion);
1433 }
1434
1435 $DBversion = "3.00.00.077";
1436 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1437 # drop these tables only if they exist and none of them are empty
1438 # these tables are not defined in the packaged 2.2.9, but since it is believed
1439 # that at least one library may be using them in a post-2.2.9 but pre-3.0 Koha,
1440 # some care is taken.
1441 my ($print_error) = $dbh->{PrintError};
1442 $dbh->{PrintError} = 0;
1443 my ($raise_error) = $dbh->{RaiseError};
1444 $dbh->{RaiseError} = 1;
1445
1446 my $count = 0;
1447 my $do_drop = 1;
1448 eval { $count = $dbh->do("SELECT 1 FROM categorytable"); };
1449 if ($count > 0) {
1450 $do_drop = 0;
1451 }
1452 eval { $count = $dbh->do("SELECT 1 FROM mediatypetable"); };
1453 if ($count > 0) {
1454 $do_drop = 0;
1455 }
1456 eval { $count = $dbh->do("SELECT 1 FROM subcategorytable"); };
1457 if ($count > 0) {
1458 $do_drop = 0;
1459 }
1460
1461 if ($do_drop) {
1462 $dbh->do("DROP TABLE IF EXISTS `categorytable`");
1463 $dbh->do("DROP TABLE IF EXISTS `mediatypetable`");
1464 $dbh->do("DROP TABLE IF EXISTS `subcategorytable`");
1465 }
1466
1467 $dbh->{PrintError} = $print_error;
1468 $dbh->{RaiseError} = $raise_error;
1469 print "Upgrade to $DBversion done (drop categorytable, subcategorytable, and mediatypetable)\n";
1470 SetVersion ($DBversion);
1471 }
1472
1473 $DBversion = "3.00.00.078";
1474 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1475 my ($print_error) = $dbh->{PrintError};
1476 $dbh->{PrintError} = 0;
1477
1478 unless ($dbh->do("SELECT 1 FROM browser")) {
1479 $dbh->{PrintError} = $print_error;
1480 $dbh->do("CREATE TABLE `browser` (
1481 `level` int(11) NOT NULL,
1482 `classification` varchar(20) NOT NULL,
1483 `description` varchar(255) NOT NULL,
1484 `number` bigint(20) NOT NULL,
1485 `endnode` tinyint(4) NOT NULL
1486 ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
1487 }
1488 $dbh->{PrintError} = $print_error;
1489 print "Upgrade to $DBversion done (add browser table if not already present)\n";
1490 SetVersion ($DBversion);
1491 }
1492
1493 $DBversion = "3.00.00.079";
1494 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1495 my ($print_error) = $dbh->{PrintError};
1496 $dbh->{PrintError} = 0;
1497
1498 $dbh->do("INSERT INTO `systempreferences` (variable, value,options,type, explanation)VALUES
1499 ('AddPatronLists','categorycode','categorycode|category_type','Choice','Allow user to choose what list to pick up from when adding patrons')");
1500 print "Upgrade to $DBversion done (add browser table if not already present)\n";
1501 SetVersion ($DBversion);
1502 }
1503
1504
1505
1506 $DBversion = "3.00.00.080";
1507 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1508 $dbh->do("ALTER TABLE subscription CHANGE monthlength monthlength int(11) default '0'");
1509 $dbh->do("ALTER TABLE deleteditems MODIFY marc LONGBLOB AFTER copynumber");
1510 $dbh->do("ALTER TABLE aqbooksellers CHANGE name name mediumtext NOT NULL");
1511 print "Upgrade to $DBversion done (catch up on DB schema changes since alpha and beta)\n";
1512 SetVersion ($DBversion);
1513 }
1514
1515 $DBversion = "3.00.00.081";
1516 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1517 $dbh->do("CREATE TABLE `borrower_attribute_types` (
1518 `code` varchar(10) NOT NULL,
1519 `description` varchar(255) NOT NULL,
1520 `repeatable` tinyint(1) NOT NULL default 0,
1521 `unique_id` tinyint(1) NOT NULL default 0,
1522 `opac_display` tinyint(1) NOT NULL default 0,
1523 `password_allowed` tinyint(1) NOT NULL default 0,
1524 `staff_searchable` tinyint(1) NOT NULL default 0,
1525 `authorised_value_category` varchar(10) default NULL,
1526 PRIMARY KEY (`code`)
1527 ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
1528 $dbh->do("CREATE TABLE `borrower_attributes` (
1529 `borrowernumber` int(11) NOT NULL,
1530 `code` varchar(10) NOT NULL,
1531 `attribute` varchar(30) default NULL,
1532 `password` varchar(30) default NULL,
1533 KEY `borrowernumber` (`borrowernumber`),
1534 KEY `code_attribute` (`code`, `attribute`),
1535 CONSTRAINT `borrower_attributes_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`)
1536 ON DELETE CASCADE ON UPDATE CASCADE,
1537 CONSTRAINT `borrower_attributes_ibfk_2` FOREIGN KEY (`code`) REFERENCES `borrower_attribute_types` (`code`)
1538 ON DELETE CASCADE ON UPDATE CASCADE
1539 ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
1540 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('ExtendedPatronAttributes','0','Use extended patron IDs and attributes',NULL,'YesNo')");
1541 print "Upgrade to $DBversion done (added borrower_attributes and borrower_attribute_types)\n";
1542 SetVersion ($DBversion);
1543 }
1544
1545 $DBversion = "3.00.00.082";
1546 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1547 $dbh->do( q(alter table accountlines add column lastincrement decimal(28,6) default NULL) );
1548 print "Upgrade to $DBversion done (adding lastincrement column to accountlines table)\n";
1549 SetVersion ($DBversion);
1550 }
1551
1552 $DBversion = "3.00.00.083";
1553 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1554 $dbh->do( qq(UPDATE systempreferences SET value='local' where variable='yuipath' and value like "%/intranet-tmpl/prog/%"));
1555 print "Upgrade to $DBversion done (Changing yuipath behaviour in managing a local value)\n";
1556 SetVersion ($DBversion);
1557 }
1558 $DBversion = "3.00.00.084";
1559 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1560 $dbh->do("INSERT INTO systempreferences (variable,value,explanation,options,type) VALUES('RenewSerialAddsSuggestion','0','if ON, adds a new suggestion at serial subscription renewal',NULL,'YesNo')");
1561 $dbh->do("INSERT INTO systempreferences (variable,value,explanation,options,type) VALUES('GoogleJackets','0','if ON, displays jacket covers from Google Books API',NULL,'YesNo')");
1562 print "Upgrade to $DBversion done (add new sysprefs)\n";
1563 SetVersion ($DBversion);
1564 }
1565
1566 $DBversion = "3.00.00.085";
1567 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1568 if (C4::Context->preference("marcflavour") eq 'MARC21') {
1569 $dbh->do("UPDATE marc_subfield_structure SET tab = 0 WHERE tab = 9 AND tagfield = '037'");
1570 $dbh->do("UPDATE marc_subfield_structure SET tab = 1 WHERE tab = 6 AND tagfield in ('100', '110', '111', '130')");
1571 $dbh->do("UPDATE marc_subfield_structure SET tab = 2 WHERE tab = 6 AND tagfield in ('240', '243')");
1572 $dbh->do("UPDATE marc_subfield_structure SET tab = 4 WHERE tab = 6 AND tagfield in ('400', '410', '411', '440')");
1573 $dbh->do("UPDATE marc_subfield_structure SET tab = 5 WHERE tab = 9 AND tagfield = '584'");
1574 $dbh->do("UPDATE marc_subfield_structure SET tab = 7 WHERE tab = -6 AND tagfield = '760'");
1575 }
1576 print "Upgrade to $DBversion done (move editing tab of various MARC21 subfields)\n";
1577 SetVersion ($DBversion);
1578 }
1579
1580 $DBversion = "3.00.00.086";
1581 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1582 $dbh->do(
1583 "CREATE TABLE `tmp_holdsqueue` (
1584 `biblionumber` int(11) default NULL,
1585 `itemnumber` int(11) default NULL,
1586 `barcode` varchar(20) default NULL,
1587 `surname` mediumtext NOT NULL,
1588 `firstname` text,
1589 `phone` text,
1590 `borrowernumber` int(11) NOT NULL,
1591 `cardnumber` varchar(16) default NULL,
1592 `reservedate` date default NULL,
1593 `title` mediumtext,
1594 `itemcallnumber` varchar(30) default NULL,
1595 `holdingbranch` varchar(10) default NULL,
1596 `pickbranch` varchar(10) default NULL,
1597 `notes` text
1598 ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
1599
1600 $dbh->do("INSERT INTO systempreferences (variable,value,explanation,options,type) VALUES('RandomizeHoldsQueueWeight','0','if ON, the holds queue in circulation will be randomized, either based on all location codes, or by the location codes specified in StaticHoldsQueueWeight',NULL,'YesNo')");
1601 $dbh->do("INSERT INTO systempreferences (variable,value,explanation,options,type) VALUES('StaticHoldsQueueWeight','0','Specify a list of library location codes separated by commas -- the list of codes will be traversed and weighted with first values given higher weight for holds fulfillment -- alternatively, if RandomizeHoldsQueueWeight is set, the list will be randomly selective',NULL,'TextArea')");
1602
1603 print "Upgrade to $DBversion done (Table structure for table `tmp_holdsqueue`)\n";
1604 SetVersion ($DBversion);
1605 }
1606
1607 $DBversion = "3.00.00.087";
1608 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1609 $dbh->do("INSERT INTO `systempreferences` VALUES ('AutoEmailOpacUser','0','','Sends notification emails containing new account details to patrons - when account is created.','YesNo')" );
1610 $dbh->do("INSERT INTO `systempreferences` VALUES ('AutoEmailPrimaryAddress','OFF','email|emailpro|B_email|cardnumber|OFF','Defines the default email address where Account Details emails are sent.','Choice')");
1611 print "Upgrade to $DBversion done (added 2 new 'AutoEmailOpacUser' sysprefs)\n";
1612 SetVersion ($DBversion);
1613 }
1614
1615 $DBversion = "3.00.00.088";
1616 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1617 $dbh->do("INSERT INTO `systempreferences` (variable,value,options,explanation,type) VALUES ('OPACShelfBrowser','1','','Enable/disable Shelf Browser on item details page','YesNo')");
1618 $dbh->do("INSERT INTO `systempreferences` (variable,value,options,explanation,type) VALUES ('OPACItemHolds','1','Allow OPAC users to place hold on specific items. If OFF, users can only request next available copy.','','YesNo')");
1619 $dbh->do("INSERT INTO `systempreferences` (variable,value,options,explanation,type) VALUES ('XSLTDetailsDisplay','0','','Enable XSL stylesheet control over details page display on OPAC WARNING: MARC21 Only','YesNo')");
1620 $dbh->do("INSERT INTO `systempreferences` (variable,value,options,explanation,type) VALUES ('XSLTResultsDisplay','0','','Enable XSL stylesheet control over results page display on OPAC WARNING: MARC21 Only','YesNo')");
1621 print "Upgrade to $DBversion done (added 2 new 'AutoEmailOpacUser' sysprefs)\n";
1622 SetVersion ($DBversion);
1623 }
1624
1625 $DBversion = "3.00.00.089";
1626 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1627 $dbh->do("INSERT INTO `systempreferences` (variable,value,options,explanation,type) VALUES('AdvancedSearchTypes','itemtypes','itemtypes|ccode','Select which set of fields comprise the Type limit in the advanced search','Choice')");
1628 print "Upgrade to $DBversion done (added new AdvancedSearchTypes syspref)\n";
1629 SetVersion ($DBversion);
1630 }
1631
1632 $DBversion = "3.00.00.090";
1633 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1634 $dbh->do("
1635 CREATE TABLE `branch_borrower_circ_rules` (
1636 `branchcode` VARCHAR(10) NOT NULL,
1637 `categorycode` VARCHAR(10) NOT NULL,
1638 `maxissueqty` int(4) default NULL,
1639 PRIMARY KEY (`categorycode`, `branchcode`),
1640 CONSTRAINT `branch_borrower_circ_rules_ibfk_1` FOREIGN KEY (`categorycode`) REFERENCES `categories` (`categorycode`)
1641 ON DELETE CASCADE ON UPDATE CASCADE,
1642 CONSTRAINT `branch_borrower_circ_rules_ibfk_2` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`)
1643 ON DELETE CASCADE ON UPDATE CASCADE
1644 ) ENGINE=InnoDB DEFAULT CHARSET=utf8
1645 ");
1646 $dbh->do("
1647 CREATE TABLE `default_borrower_circ_rules` (
1648 `categorycode` VARCHAR(10) NOT NULL,
1649 `maxissueqty` int(4) default NULL,
1650 PRIMARY KEY (`categorycode`),
1651 CONSTRAINT `borrower_borrower_circ_rules_ibfk_1` FOREIGN KEY (`categorycode`) REFERENCES `categories` (`categorycode`)
1652 ON DELETE CASCADE ON UPDATE CASCADE
1653 ) ENGINE=InnoDB DEFAULT CHARSET=utf8
1654 ");
1655 $dbh->do("
1656 CREATE TABLE `default_branch_circ_rules` (
1657 `branchcode` VARCHAR(10) NOT NULL,
1658 `maxissueqty` int(4) default NULL,
1659 PRIMARY KEY (`branchcode`),
1660 CONSTRAINT `default_branch_circ_rules_ibfk_1` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`)
1661 ON DELETE CASCADE ON UPDATE CASCADE
1662 ) ENGINE=InnoDB DEFAULT CHARSET=utf8
1663 ");
1664 $dbh->do("
1665 CREATE TABLE `default_circ_rules` (
1666 `singleton` enum('singleton') NOT NULL default 'singleton',
1667 `maxissueqty` int(4) default NULL,
1668 PRIMARY KEY (`singleton`)
1669 ) ENGINE=InnoDB DEFAULT CHARSET=utf8
1670 ");
1671 print "Upgrade to $DBversion done (added several circ rules tables)\n";
1672 SetVersion ($DBversion);
1673 }
1674
1675
1676 $DBversion = "3.00.00.091";
1677 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1678 $dbh->do(<<'END_SQL');
1679 CREATE TABLE `message_queue` (
1680 `message_id` int(11) NOT NULL auto_increment,
1681 `borrowernumber` int(11) NOT NULL,
1682 `subject` text,
1683 `content` text,
1684 `message_transport_type` varchar(20) NOT NULL,
1685 `status` enum('sent','pending','failed','deleted') NOT NULL default 'pending',
1686 `time_queued` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1687 KEY `message_id` (`message_id`),
1688 KEY `borrowernumber` (`borrowernumber`),
1689 KEY `message_transport_type` (`message_transport_type`),
1690 CONSTRAINT `messageq_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1691 CONSTRAINT `messageq_ibfk_2` FOREIGN KEY (`message_transport_type`) REFERENCES `message_transport_types` (`message_transport_type`) ON DELETE RESTRICT ON UPDATE CASCADE
1692 ) ENGINE=InnoDB DEFAULT CHARSET=utf8
1693 END_SQL
1694
1695 $dbh->do(<<'END_SQL');
1696 ALTER TABLE borrowers
1697 ADD `smsalertnumber` varchar(50) default NULL
1698 END_SQL
1699
1700 $dbh->do(<<'END_SQL');
1701 CREATE TABLE `message_transport_types` (
1702 `message_transport_type` varchar(20) NOT NULL,
1703 PRIMARY KEY (`message_transport_type`)
1704 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1705 END_SQL
1706
1707 $dbh->do(<<'END_SQL');
1708 CREATE TABLE `message_attributes` (
1709 `message_attribute_id` int(11) NOT NULL auto_increment,
1710 `message_name` varchar(20) NOT NULL default '',
1711 `takes_days` tinyint(1) NOT NULL default '0',
1712 PRIMARY KEY (`message_attribute_id`),
1713 UNIQUE KEY `message_name` (`message_name`)
1714 ) ENGINE=InnoDB DEFAULT CHARSET=utf8
1715 END_SQL
1716
1717 $dbh->do(<<'END_SQL');
1718 CREATE TABLE `message_transports` (
1719 `message_attribute_id` int(11) NOT NULL,
1720 `message_transport_type` varchar(20) NOT NULL,
1721 `is_digest` tinyint(1) NOT NULL default '0',
1722 `letter_module` varchar(20) NOT NULL default '',
1723 `letter_code` varchar(20) NOT NULL default '',
1724 PRIMARY KEY (`message_attribute_id`,`message_transport_type`,`is_digest`),
1725 KEY `message_transport_type` (`message_transport_type`),
1726 KEY `letter_module` (`letter_module`,`letter_code`),
1727 CONSTRAINT `message_transports_ibfk_1` FOREIGN KEY (`message_attribute_id`) REFERENCES `message_attributes` (`message_attribute_id`) ON DELETE CASCADE ON UPDATE CASCADE,
1728 CONSTRAINT `message_transports_ibfk_2` FOREIGN KEY (`message_transport_type`) REFERENCES `message_transport_types` (`message_transport_type`) ON DELETE CASCADE ON UPDATE CASCADE,
1729 CONSTRAINT `message_transports_ibfk_3` FOREIGN KEY (`letter_module`, `letter_code`) REFERENCES `letter` (`module`, `code`) ON DELETE CASCADE ON UPDATE CASCADE
1730 ) ENGINE=InnoDB DEFAULT CHARSET=utf8
1731 END_SQL
1732
1733 $dbh->do(<<'END_SQL');
1734 CREATE TABLE `borrower_message_preferences` (
1735 `borrower_message_preference_id` int(11) NOT NULL auto_increment,
1736 `borrowernumber` int(11) NOT NULL default '0',
1737 `message_attribute_id` int(11) default '0',
1738 `days_in_advance` int(11) default '0',
1739 `wants_digets` tinyint(1) NOT NULL default '0',
1740 PRIMARY KEY (`borrower_message_preference_id`),
1741 KEY `borrowernumber` (`borrowernumber`),
1742 KEY `message_attribute_id` (`message_attribute_id`),
1743 CONSTRAINT `borrower_message_preferences_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1744 CONSTRAINT `borrower_message_preferences_ibfk_2` FOREIGN KEY (`message_attribute_id`) REFERENCES `message_attributes` (`message_attribute_id`) ON DELETE CASCADE ON UPDATE CASCADE
1745 ) ENGINE=InnoDB DEFAULT CHARSET=utf8
1746 END_SQL
1747
1748 $dbh->do(<<'END_SQL');
1749 CREATE TABLE `borrower_message_transport_preferences` (
1750 `borrower_message_preference_id` int(11) NOT NULL default '0',
1751 `message_transport_type` varchar(20) NOT NULL default '0',
1752 PRIMARY KEY (`borrower_message_preference_id`,`message_transport_type`),
1753 KEY `message_transport_type` (`message_transport_type`),
1754 CONSTRAINT `borrower_message_transport_preferences_ibfk_1` FOREIGN KEY (`borrower_message_preference_id`) REFERENCES `borrower_message_preferences` (`borrower_message_preference_id`) ON DELETE CASCADE ON UPDATE CASCADE,
1755 CONSTRAINT `borrower_message_transport_preferences_ibfk_2` FOREIGN KEY (`message_transport_type`) REFERENCES `message_transport_types` (`message_transport_type`) ON DELETE CASCADE ON UPDATE CASCADE
1756 ) ENGINE=InnoDB DEFAULT CHARSET=utf8
1757 END_SQL
1758
1759 $dbh->do(<<'END_SQL');
1760 INSERT INTO `systempreferences`
1761 (variable,value,explanation,options,type)
1762 VALUES
1763 ('EnhancedMessagingPreferences',0,'If ON, allows patrons to select to receive additional messages about items due or nearly due.','','YesNo')
1764 END_SQL
1765
1766 print "Upgrade to $DBversion done (Table structure for table `message_queue`, `message_transport_types`, `message_attributes`, `message_transports`, `borrower_message_preferences`, and `borrower_message_transport_preferences`. Alter `borrowers` table,\n";
1767 SetVersion ($DBversion);
1768 }
1769
1770 $DBversion = "3.00.00.092";
1771 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1772 $dbh->do("INSERT INTO `systempreferences` (variable,value,options,explanation,type) VALUES('AllowOnShelfHolds', '0', '', 'Allow hold requests to be placed on items that are not on loan', 'YesNo')");
1773 $dbh->do("INSERT INTO `systempreferences` (variable,value,options,explanation,type) VALUES('AllowHoldsOnDamagedItems', '1', '', 'Allow hold requests to be placed on damaged items', 'YesNo')");
1774 print "Upgrade to $DBversion done (added new AllowOnShelfHolds syspref)\n";
1775 SetVersion ($DBversion);
1776 }
1777
1778 =item DropAllForeignKeys($table)
1779
1780 Drop all foreign keys of the table $table
1781
1782 =cut
1783
1784 sub DropAllForeignKeys {
1785 my ($table) = @_;
1786 # get the table description
1787 my $sth = $dbh->prepare("SHOW CREATE TABLE $table");
1788 $sth->execute;
1789 my $vsc_structure = $sth->fetchrow;
1790 # split on CONSTRAINT keyword
1791 my @fks = split /CONSTRAINT /,$vsc_structure;
1792 # parse each entry
1793 foreach (@fks) {
1794 # isolate what is before FOREIGN KEY, if there is something, it's a foreign key to drop
1795 $_ = /(.*) FOREIGN KEY.*/;
1796 my $id = $1;
1797 if ($id) {
1798 # we have found 1 foreign, drop it
1799 $dbh->do("ALTER TABLE $table DROP FOREIGN KEY $id");
1800 $id="";
1801 }
1802 }
1803 }
1804
1805
1806 =item TransformToNum
1807
1808 Transform the Koha version from a 4 parts string
1809 to a number, with just 1 .
1810
1811 =cut
1812
1813 sub TransformToNum {
1814 my $version = shift;
1815 # remove the 3 last . to have a Perl number
1816 $version =~ s/(.*\..*)\.(.*)\.(.*)/$1$2$3/;
1817 return $version;
1818 }
1819
1820 =item SetVersion
1821
1822 set the DBversion in the systempreferences
1823
1824 =cut
1825
1826 sub SetVersion {
1827 my $kohaversion = TransformToNum(shift);
1828 if (C4::Context->preference('Version')) {
1829 my $finish=$dbh->prepare("UPDATE systempreferences SET value=? WHERE variable='Version'");
1830 $finish->execute($kohaversion);
1831 } else {
1832 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')");
1833 $finish->execute($kohaversion);
1834 }
1835 }
1836 exit;
1837
Koha ILS