?  search:
summary | log | graphiclog1 | graphiclog2 | commit | commitdiff | tree | refs | edit | fork
blame | history | raw | HEAD
Bumpding database version
[koha.git] / installer / data / mysql / updatedatabase.pl
blob6b88c296290c4aa38e8672e2bbc816d2f2d697ad
1 #!/usr/bin/perl
2
3
4 # Database Updater
5 # This script checks for required updates to the database.
6
7 # Parts copyright Catalyst IT 2011
8
9 # Part of the Koha Library Software www.koha-community.org
10 # Koha is free software; you can redistribute it and/or modify it under the
11 # terms of the GNU General Public License as published by the Free Software
12 # Foundation; either version 2 of the License, or (at your option) any later
13 # version.
14 #
15 # Koha is distributed in the hope that it will be useful, but WITHOUT ANY
16 # WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR
17 # A PARTICULAR PURPOSE. See the GNU General Public License for more details.
18 #
19 # You should have received a copy of the GNU General Public License along with
20 # Koha; if not, write to the Free Software Foundation, Inc., 59 Temple Place,
21 # Suite 330, Boston, MA 02111-1307 USA
22 #
23
24 # Bugs/ToDo:
25 # - Would also be a good idea to offer to do a backup at this time...
26
27 # NOTE: If you do something more than once in here, make it table driven.
28
29 # NOTE: Please keep the version in kohaversion.pl up-to-date!
30
31 use strict;
32 use warnings;
33
34 # CPAN modules
35 use DBI;
36 use Getopt::Long;
37 # Koha modules
38 use C4::Context;
39 use C4::Installer;
40 use C4::Dates;
41
42 use MARC::Record;
43 use MARC::File::XML ( BinaryEncoding => 'utf8' );
44
45 # FIXME - The user might be installing a new database, so can't rely
46 # on /etc/koha.conf anyway.
47
48 my $debug = 0;
49
50 my (
51 $sth, $sti,
52 $query,
53 %existingtables, # tables already in database
54 %types,
55 $table,
56 $column,
57 $type, $null, $key, $default, $extra,
58 $prefitem, # preference item in systempreferences table
59 );
60
61 my $silent;
62 GetOptions(
63 's' =>\$silent
64 );
65 my $dbh = C4::Context->dbh;
66 $|=1; # flushes output
67
68
69 # Record the version we are coming from
70
71 my $original_version = C4::Context->preference("Version");
72
73 # Deal with virtualshelves
74 my $DBversion = "3.00.00.001";
75 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
76 # update virtualshelves table to
77 #
78 $dbh->do("ALTER TABLE `bookshelf` RENAME `virtualshelves`");
79 $dbh->do("ALTER TABLE `shelfcontents` RENAME `virtualshelfcontents`");
80 $dbh->do("ALTER TABLE `virtualshelfcontents` ADD `biblionumber` INT( 11 ) NOT NULL default '0' AFTER shelfnumber");
81 $dbh->do("UPDATE `virtualshelfcontents` SET biblionumber=(SELECT biblionumber FROM items WHERE items.itemnumber=virtualshelfcontents.itemnumber)");
82 # drop all foreign keys : otherwise, we can't drop itemnumber field.
83 DropAllForeignKeys('virtualshelfcontents');
84 $dbh->do("ALTER TABLE `virtualshelfcontents` ADD KEY biblionumber (biblionumber)");
85 # create the new foreign keys (on biblionumber)
86 $dbh->do("ALTER TABLE `virtualshelfcontents` ADD CONSTRAINT `virtualshelfcontents_ibfk_1` FOREIGN KEY (`shelfnumber`) REFERENCES `virtualshelves` (`shelfnumber`) ON DELETE CASCADE ON UPDATE CASCADE");
87 # re-create the foreign key on virtualshelf
88 $dbh->do("ALTER TABLE `virtualshelfcontents` ADD CONSTRAINT `shelfcontents_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE");
89 $dbh->do("ALTER TABLE `virtualshelfcontents` DROP `itemnumber`");
90 print "Upgrade to $DBversion done (virtualshelves)\n";
91 SetVersion ($DBversion);
92 }
93
94
95 $DBversion = "3.00.00.002";
96 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
97 $dbh->do("DROP TABLE sessions");
98 $dbh->do("CREATE TABLE `sessions` (
99 `id` varchar(32) NOT NULL,
100 `a_session` text NOT NULL,
101 UNIQUE KEY `id` (`id`)
102 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;");
103 print "Upgrade to $DBversion done (sessions uses CGI::session, new table structure for sessions)\n";
104 SetVersion ($DBversion);
105 }
106
107
108 $DBversion = "3.00.00.003";
109 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
110 if (C4::Context->preference("opaclanguages") eq "fr") {
111 $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')");
112 } else {
113 $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')");
114 }
115 print "Upgrade to $DBversion done (adding ReservesNeedReturns systempref, in circulation)\n";
116 SetVersion ($DBversion);
117 }
118
119
120 $DBversion = "3.00.00.004";
121 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
122 $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')");
123 print "Upgrade to $DBversion done (adding DebugLevel systempref, in 'Admin' tab)\n";
124 SetVersion ($DBversion);
125 }
126
127 $DBversion = "3.00.00.005";
128 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
129 $dbh->do("CREATE TABLE `tags` (
130 `entry` varchar(255) NOT NULL default '',
131 `weight` bigint(20) NOT NULL default 0,
132 PRIMARY KEY (`entry`)
133 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
134 ");
135 $dbh->do("CREATE TABLE `nozebra` (
136 `server` varchar(20) NOT NULL,
137 `indexname` varchar(40) NOT NULL,
138 `value` varchar(250) NOT NULL,
139 `biblionumbers` longtext NOT NULL,
140 KEY `indexname` (`server`,`indexname`),
141 KEY `value` (`server`,`value`))
142 ENGINE=InnoDB DEFAULT CHARSET=utf8;
143 ");
144 print "Upgrade to $DBversion done (adding tags and nozebra tables )\n";
145 SetVersion ($DBversion);
146 }
147
148 $DBversion = "3.00.00.006";
149 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
150 $dbh->do("UPDATE issues SET issuedate=timestamp WHERE issuedate='0000-00-00'");
151 print "Upgrade to $DBversion done (filled issues.issuedate with timestamp)\n";
152 SetVersion ($DBversion);
153 }
154
155 $DBversion = "3.00.00.007";
156 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
157 $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')");
158 print "Upgrade to $DBversion done (set SessionStorage variable)\n";
159 SetVersion ($DBversion);
160 }
161
162 $DBversion = "3.00.00.008";
163 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
164 $dbh->do("ALTER TABLE `biblio` ADD `datecreated` DATE NOT NULL AFTER `timestamp` ;");
165 $dbh->do("UPDATE biblio SET datecreated=timestamp");
166 print "Upgrade to $DBversion done (biblio creation date)\n";
167 SetVersion ($DBversion);
168 }
169
170 $DBversion = "3.00.00.009";
171 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
172
173 # Create backups of call number columns
174 # in case default migration needs to be customized
175 #
176 # UPGRADE NOTE: temp_upg_biblioitems_call_num should be dropped
177 # after call numbers have been transformed to the new structure
178 #
179 # Not bothering to do the same with deletedbiblioitems -- assume
180 # default is good enough.
181 $dbh->do("CREATE TABLE `temp_upg_biblioitems_call_num` AS
182 SELECT `biblioitemnumber`, `biblionumber`,
183 `classification`, `dewey`, `subclass`,
184 `lcsort`, `ccode`
185 FROM `biblioitems`");
186
187 # biblioitems changes
188 $dbh->do("ALTER TABLE `biblioitems` CHANGE COLUMN `volumeddesc` `volumedesc` TEXT,
189 ADD `cn_source` VARCHAR(10) DEFAULT NULL AFTER `ccode`,
190 ADD `cn_class` VARCHAR(30) DEFAULT NULL AFTER `cn_source`,
191 ADD `cn_item` VARCHAR(10) DEFAULT NULL AFTER `cn_class`,
192 ADD `cn_suffix` VARCHAR(10) DEFAULT NULL AFTER `cn_item`,
193 ADD `cn_sort` VARCHAR(30) DEFAULT NULL AFTER `cn_suffix`,
194 ADD `totalissues` INT(10) AFTER `cn_sort`");
195
196 # default mapping of call number columns:
197 # cn_class = concatentation of classification + dewey,
198 # trimmed to fit -- assumes that most users do not
199 # populate both classification and dewey in a single record
200 # cn_item = subclass
201 # cn_source = left null
202 # cn_sort = lcsort
203 #
204 # After upgrade, cn_sort will have to be set based on whatever
205 # default call number scheme user sets as a preference. Misc
206 # script will be added at some point to do that.
207 #
208 $dbh->do("UPDATE `biblioitems`
209 SET cn_class = SUBSTR(TRIM(CONCAT_WS(' ', `classification`, `dewey`)), 1, 30),
210 cn_item = subclass,
211 `cn_sort` = `lcsort`
212 ");
213
214 # Now drop the old call number columns
215 $dbh->do("ALTER TABLE `biblioitems` DROP COLUMN `classification`,
216 DROP COLUMN `dewey`,
217 DROP COLUMN `subclass`,
218 DROP COLUMN `lcsort`,
219 DROP COLUMN `ccode`");
220
221 # deletedbiblio changes
222 $dbh->do("ALTER TABLE `deletedbiblio` ALTER COLUMN `frameworkcode` SET DEFAULT '',
223 DROP COLUMN `marc`,
224 ADD `datecreated` DATE NOT NULL AFTER `timestamp`");
225 $dbh->do("UPDATE deletedbiblio SET datecreated = timestamp");
226
227 # deletedbiblioitems changes
228 $dbh->do("ALTER TABLE `deletedbiblioitems`
229 MODIFY `publicationyear` TEXT,
230 CHANGE `volumeddesc` `volumedesc` TEXT,
231 MODIFY `collectiontitle` MEDIUMTEXT DEFAULT NULL AFTER `volumedesc`,
232 MODIFY `collectionissn` TEXT DEFAULT NULL AFTER `collectiontitle`,
233 MODIFY `collectionvolume` MEDIUMTEXT DEFAULT NULL AFTER `collectionissn`,
234 MODIFY `editionstatement` TEXT DEFAULT NULL AFTER `collectionvolume`,
235 MODIFY `editionresponsibility` TEXT DEFAULT NULL AFTER `editionstatement`,
236 MODIFY `place` VARCHAR(255) DEFAULT NULL AFTER `size`,
237 MODIFY `marc` LONGBLOB,
238 ADD `cn_source` VARCHAR(10) DEFAULT NULL AFTER `url`,
239 ADD `cn_class` VARCHAR(30) DEFAULT NULL AFTER `cn_source`,
240 ADD `cn_item` VARCHAR(10) DEFAULT NULL AFTER `cn_class`,
241 ADD `cn_suffix` VARCHAR(10) DEFAULT NULL AFTER `cn_item`,
242 ADD `cn_sort` VARCHAR(30) DEFAULT NULL AFTER `cn_suffix`,
243 ADD `totalissues` INT(10) AFTER `cn_sort`,
244 ADD `marcxml` LONGTEXT NOT NULL AFTER `totalissues`,
245 ADD KEY `isbn` (`isbn`),
246 ADD KEY `publishercode` (`publishercode`)
247 ");
248
249 $dbh->do("UPDATE `deletedbiblioitems`
250 SET `cn_class` = SUBSTR(TRIM(CONCAT_WS(' ', `classification`, `dewey`)), 1, 30),
251 `cn_item` = `subclass`,
252 `cn_sort` = `lcsort`
253 ");
254 $dbh->do("ALTER TABLE `deletedbiblioitems`
255 DROP COLUMN `classification`,
256 DROP COLUMN `dewey`,
257 DROP COLUMN `subclass`,
258 DROP COLUMN `lcsort`,
259 DROP COLUMN `ccode`
260 ");
261
262 # deleteditems changes
263 $dbh->do("ALTER TABLE `deleteditems`
264 MODIFY `barcode` VARCHAR(20) DEFAULT NULL,
265 MODIFY `price` DECIMAL(8,2) DEFAULT NULL,
266 MODIFY `replacementprice` DECIMAL(8,2) DEFAULT NULL,
267 DROP `bulk`,
268 MODIFY `itemcallnumber` VARCHAR(30) DEFAULT NULL AFTER `wthdrawn`,
269 MODIFY `holdingbranch` VARCHAR(10) DEFAULT NULL,
270 DROP `interim`,
271 MODIFY `timestamp` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP AFTER `paidfor`,
272 DROP `cutterextra`,
273 ADD `cn_source` VARCHAR(10) DEFAULT NULL AFTER `onloan`,
274 ADD `cn_sort` VARCHAR(30) DEFAULT NULL AFTER `cn_source`,
275 ADD `ccode` VARCHAR(10) DEFAULT NULL AFTER `cn_sort`,
276 ADD `materials` VARCHAR(10) DEFAULT NULL AFTER `ccode`,
277 ADD `uri` VARCHAR(255) DEFAULT NULL AFTER `materials`,
278 MODIFY `marc` LONGBLOB AFTER `uri`,
279 DROP KEY `barcode`,
280 DROP KEY `itembarcodeidx`,
281 DROP KEY `itembinoidx`,
282 DROP KEY `itembibnoidx`,
283 ADD UNIQUE KEY `delitembarcodeidx` (`barcode`),
284 ADD KEY `delitembinoidx` (`biblioitemnumber`),
285 ADD KEY `delitembibnoidx` (`biblionumber`),
286 ADD KEY `delhomebranch` (`homebranch`),
287 ADD KEY `delholdingbranch` (`holdingbranch`)");
288 $dbh->do("UPDATE deleteditems SET `ccode` = `itype`");
289 $dbh->do("ALTER TABLE deleteditems DROP `itype`");
290 $dbh->do("UPDATE `deleteditems` SET `cn_sort` = `itemcallnumber`");
291
292 # items changes
293 $dbh->do("ALTER TABLE `items` ADD `cn_source` VARCHAR(10) DEFAULT NULL AFTER `onloan`,
294 ADD `cn_sort` VARCHAR(30) DEFAULT NULL AFTER `cn_source`,
295 ADD `ccode` VARCHAR(10) DEFAULT NULL AFTER `cn_sort`,
296 ADD `materials` VARCHAR(10) DEFAULT NULL AFTER `ccode`,
297 ADD `uri` VARCHAR(255) DEFAULT NULL AFTER `materials`
298 ");
299 $dbh->do("ALTER TABLE `items`
300 DROP KEY `itembarcodeidx`,
301 ADD UNIQUE KEY `itembarcodeidx` (`barcode`)");
302
303 # map items.itype to items.ccode and
304 # set cn_sort to itemcallnumber -- as with biblioitems.cn_sort,
305 # will have to be subsequently updated per user's default
306 # classification scheme
307 $dbh->do("UPDATE `items` SET `cn_sort` = `itemcallnumber`,
308 `ccode` = `itype`");
309
310 $dbh->do("ALTER TABLE `items` DROP `cutterextra`,
311 DROP `itype`");
312
313 print "Upgrade to $DBversion done (major changes to biblio, biblioitems, items, and deleted* versions of same\n";
314 SetVersion ($DBversion);
315 }
316
317 $DBversion = "3.00.00.010";
318 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
319 $dbh->do("CREATE INDEX `userid` ON borrowers (`userid`) ");
320 print "Upgrade to $DBversion done (userid index added)\n";
321 SetVersion ($DBversion);
322 }
323
324 $DBversion = "3.00.00.011";
325 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
326 $dbh->do("ALTER TABLE `branchcategories` CHANGE `categorycode` `categorycode` varchar(10) ");
327 $dbh->do("ALTER TABLE `branchcategories` CHANGE `categoryname` `categoryname` varchar(32) ");
328 $dbh->do("ALTER TABLE `branchcategories` ADD COLUMN `categorytype` varchar(16) ");
329 $dbh->do("UPDATE `branchcategories` SET `categorytype` = 'properties'");
330 $dbh->do("ALTER TABLE `branchrelations` CHANGE `categorycode` `categorycode` varchar(10) ");
331 print "Upgrade to $DBversion done (added branchcategory type)\n";
332 SetVersion ($DBversion);
333 }
334
335 $DBversion = "3.00.00.012";
336 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
337 $dbh->do("CREATE TABLE `class_sort_rules` (
338 `class_sort_rule` varchar(10) NOT NULL default '',
339 `description` mediumtext,
340 `sort_routine` varchar(30) NOT NULL default '',
341 PRIMARY KEY (`class_sort_rule`),
342 UNIQUE KEY `class_sort_rule_idx` (`class_sort_rule`)
343 ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
344 $dbh->do("CREATE TABLE `class_sources` (
345 `cn_source` varchar(10) NOT NULL default '',
346 `description` mediumtext,
347 `used` tinyint(4) NOT NULL default 0,
348 `class_sort_rule` varchar(10) NOT NULL default '',
349 PRIMARY KEY (`cn_source`),
350 UNIQUE KEY `cn_source_idx` (`cn_source`),
351 KEY `used_idx` (`used`),
352 CONSTRAINT `class_source_ibfk_1` FOREIGN KEY (`class_sort_rule`)
353 REFERENCES `class_sort_rules` (`class_sort_rule`)
354 ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
355 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type)
356 VALUES('DefaultClassificationSource','ddc',
357 'Default classification scheme used by the collection. E.g., Dewey, LCC, etc.', NULL,'free')");
358 $dbh->do("INSERT INTO `class_sort_rules` (`class_sort_rule`, `description`, `sort_routine`) VALUES
359 ('dewey', 'Default filing rules for DDC', 'Dewey'),
360 ('lcc', 'Default filing rules for LCC', 'LCC'),
361 ('generic', 'Generic call number filing rules', 'Generic')");
362 $dbh->do("INSERT INTO `class_sources` (`cn_source`, `description`, `used`, `class_sort_rule`) VALUES
363 ('ddc', 'Dewey Decimal Classification', 1, 'dewey'),
364 ('lcc', 'Library of Congress Classification', 1, 'lcc'),
365 ('udc', 'Universal Decimal Classification', 0, 'generic'),
366 ('sudocs', 'SuDoc Classification (U.S. GPO)', 0, 'generic'),
367 ('z', 'Other/Generic Classification Scheme', 0, 'generic')");
368 print "Upgrade to $DBversion done (classification sources added)\n";
369 SetVersion ($DBversion);
370 }
371
372 $DBversion = "3.00.00.013";
373 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
374 $dbh->do("CREATE TABLE `import_batches` (
375 `import_batch_id` int(11) NOT NULL auto_increment,
376 `template_id` int(11) default NULL,
377 `branchcode` varchar(10) default NULL,
378 `num_biblios` int(11) NOT NULL default 0,
379 `num_items` int(11) NOT NULL default 0,
380 `upload_timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP,
381 `overlay_action` enum('replace', 'create_new', 'use_template') NOT NULL default 'create_new',
382 `import_status` enum('staging', 'staged', 'importing', 'imported', 'reverting', 'reverted', 'cleaned') NOT NULL default 'staging',
383 `batch_type` enum('batch', 'z3950') NOT NULL default 'batch',
384 `file_name` varchar(100),
385 `comments` mediumtext,
386 PRIMARY KEY (`import_batch_id`),
387 KEY `branchcode` (`branchcode`)
388 ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
389 $dbh->do("CREATE TABLE `import_records` (
390 `import_record_id` int(11) NOT NULL auto_increment,
391 `import_batch_id` int(11) NOT NULL,
392 `branchcode` varchar(10) default NULL,
393 `record_sequence` int(11) NOT NULL default 0,
394 `upload_timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP,
395 `import_date` DATE default NULL,
396 `marc` longblob NOT NULL,
397 `marcxml` longtext NOT NULL,
398 `marcxml_old` longtext NOT NULL,
399 `record_type` enum('biblio', 'auth', 'holdings') NOT NULL default 'biblio',
400 `overlay_status` enum('no_match', 'auto_match', 'manual_match', 'match_applied') NOT NULL default 'no_match',
401 `status` enum('error', 'staged', 'imported', 'reverted', 'items_reverted') NOT NULL default 'staged',
402 `import_error` mediumtext,
403 `encoding` varchar(40) NOT NULL default '',
404 `z3950random` varchar(40) default NULL,
405 PRIMARY KEY (`import_record_id`),
406 CONSTRAINT `import_records_ifbk_1` FOREIGN KEY (`import_batch_id`)
407 REFERENCES `import_batches` (`import_batch_id`) ON DELETE CASCADE ON UPDATE CASCADE,
408 KEY `branchcode` (`branchcode`),
409 KEY `batch_sequence` (`import_batch_id`, `record_sequence`)
410 ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
411 $dbh->do("CREATE TABLE `import_record_matches` (
412 `import_record_id` int(11) NOT NULL,
413 `candidate_match_id` int(11) NOT NULL,
414 `score` int(11) NOT NULL default 0,
415 CONSTRAINT `import_record_matches_ibfk_1` FOREIGN KEY (`import_record_id`)
416 REFERENCES `import_records` (`import_record_id`) ON DELETE CASCADE ON UPDATE CASCADE,
417 KEY `record_score` (`import_record_id`, `score`)
418 ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
419 $dbh->do("CREATE TABLE `import_biblios` (
420 `import_record_id` int(11) NOT NULL,
421 `matched_biblionumber` int(11) default NULL,
422 `control_number` varchar(25) default NULL,
423 `original_source` varchar(25) default NULL,
424 `title` varchar(128) default NULL,
425 `author` varchar(80) default NULL,
426 `isbn` varchar(14) default NULL,
427 `issn` varchar(9) default NULL,
428 `has_items` tinyint(1) NOT NULL default 0,
429 CONSTRAINT `import_biblios_ibfk_1` FOREIGN KEY (`import_record_id`)
430 REFERENCES `import_records` (`import_record_id`) ON DELETE CASCADE ON UPDATE CASCADE,
431 KEY `matched_biblionumber` (`matched_biblionumber`),
432 KEY `title` (`title`),
433 KEY `isbn` (`isbn`)
434 ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
435 $dbh->do("CREATE TABLE `import_items` (
436 `import_items_id` int(11) NOT NULL auto_increment,
437 `import_record_id` int(11) NOT NULL,
438 `itemnumber` int(11) default NULL,
439 `branchcode` varchar(10) default NULL,
440 `status` enum('error', 'staged', 'imported', 'reverted') NOT NULL default 'staged',
441 `marcxml` longtext NOT NULL,
442 `import_error` mediumtext,
443 PRIMARY KEY (`import_items_id`),
444 CONSTRAINT `import_items_ibfk_1` FOREIGN KEY (`import_record_id`)
445 REFERENCES `import_records` (`import_record_id`) ON DELETE CASCADE ON UPDATE CASCADE,
446 KEY `itemnumber` (`itemnumber`),
447 KEY `branchcode` (`branchcode`)
448 ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
449
450 $dbh->do("INSERT INTO `import_batches`
451 (`overlay_action`, `import_status`, `batch_type`, `file_name`)
452 SELECT distinct 'create_new', 'staged', 'z3950', `file`
453 FROM `marc_breeding`");
454
455 $dbh->do("INSERT INTO `import_records`
456 (`import_batch_id`, `import_record_id`, `record_sequence`, `marc`, `record_type`, `status`,
457 `encoding`, `z3950random`, `marcxml`, `marcxml_old`)
458 SELECT `import_batch_id`, `id`, 1, `marc`, 'biblio', 'staged', `encoding`, `z3950random`, '', ''
459 FROM `marc_breeding`
460 JOIN `import_batches` ON (`file_name` = `file`)");
461
462 $dbh->do("INSERT INTO `import_biblios`
463 (`import_record_id`, `title`, `author`, `isbn`)
464 SELECT `import_record_id`, `title`, `author`, `isbn`
465 FROM `marc_breeding`
466 JOIN `import_records` ON (`import_record_id` = `id`)");
467
468 $dbh->do("UPDATE `import_batches`
469 SET `num_biblios` = (
470 SELECT COUNT(*)
471 FROM `import_records`
472 WHERE `import_batch_id` = `import_batches`.`import_batch_id`
473 )");
474
475 $dbh->do("DROP TABLE `marc_breeding`");
476
477 print "Upgrade to $DBversion done (import_batches et al. added)\n";
478 SetVersion ($DBversion);
479 }
480
481 $DBversion = "3.00.00.014";
482 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
483 $dbh->do("ALTER TABLE subscription ADD lastbranch VARCHAR(4)");
484 print "Upgrade to $DBversion done (userid index added)\n";
485 SetVersion ($DBversion);
486 }
487
488 $DBversion = "3.00.00.015";
489 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
490 $dbh->do("CREATE TABLE `saved_sql` (
491 `id` int(11) NOT NULL auto_increment,
492 `borrowernumber` int(11) default NULL,
493 `date_created` datetime default NULL,
494 `last_modified` datetime default NULL,
495 `savedsql` text,
496 `last_run` datetime default NULL,
497 `report_name` varchar(255) default NULL,
498 `type` varchar(255) default NULL,
499 `notes` text,
500 PRIMARY KEY (`id`),
501 KEY boridx (`borrowernumber`)
502 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;");
503 $dbh->do("CREATE TABLE `saved_reports` (
504 `id` int(11) NOT NULL auto_increment,
505 `report_id` int(11) default NULL,
506 `report` longtext,
507 `date_run` datetime default NULL,
508 PRIMARY KEY (`id`)
509 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;");
510 print "Upgrade to $DBversion done (saved_sql and saved_reports added)\n";
511 SetVersion ($DBversion);
512 }
513
514 $DBversion = "3.00.00.016";
515 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
516 $dbh->do(" CREATE TABLE reports_dictionary (
517 id int(11) NOT NULL auto_increment,
518 name varchar(255) default NULL,
519 description text,
520 date_created datetime default NULL,
521 date_modified datetime default NULL,
522 saved_sql text,
523 area int(11) default NULL,
524 PRIMARY KEY (id)
525 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ");
526 print "Upgrade to $DBversion done (reports_dictionary) added)\n";
527 SetVersion ($DBversion);
528 }
529
530 $DBversion = "3.00.00.017";
531 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
532 $dbh->do("ALTER TABLE action_logs DROP PRIMARY KEY");
533 $dbh->do("ALTER TABLE action_logs ADD KEY timestamp (timestamp,user)");
534 $dbh->do("ALTER TABLE action_logs ADD action_id INT(11) NOT NULL FIRST");
535 $dbh->do("UPDATE action_logs SET action_id = if (\@a, \@a:=\@a+1, \@a:=1)");
536 $dbh->do("ALTER TABLE action_logs MODIFY action_id INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY");
537 print "Upgrade to $DBversion done (added column to action_logs)\n";
538 SetVersion ($DBversion);
539 }
540
541 $DBversion = "3.00.00.018";
542 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
543 $dbh->do("ALTER TABLE `zebraqueue`
544 ADD `done` INT NOT NULL DEFAULT '0',
545 ADD `time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ;
546 ");
547 print "Upgrade to $DBversion done (adding timestamp and done columns to zebraque table to improve problem tracking) added)\n";
548 SetVersion ($DBversion);
549 }
550
551 $DBversion = "3.00.00.019";
552 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
553 $dbh->do("ALTER TABLE biblio MODIFY biblionumber INT(11) NOT NULL AUTO_INCREMENT");
554 $dbh->do("ALTER TABLE biblioitems MODIFY biblioitemnumber INT(11) NOT NULL AUTO_INCREMENT");
555 $dbh->do("ALTER TABLE items MODIFY itemnumber INT(11) NOT NULL AUTO_INCREMENT");
556 print "Upgrade to $DBversion done (made bib/item PKs auto_increment)\n";
557 SetVersion ($DBversion);
558 }
559
560 $DBversion = "3.00.00.020";
561 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
562 $dbh->do("ALTER TABLE deleteditems
563 DROP KEY `delitembarcodeidx`,
564 ADD KEY `delitembarcodeidx` (`barcode`)");
565 print "Upgrade to $DBversion done (dropped uniqueness of key on deleteditems.barcode)\n";
566 SetVersion ($DBversion);
567 }
568
569 $DBversion = "3.00.00.021";
570 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
571 $dbh->do("ALTER TABLE items CHANGE homebranch homebranch VARCHAR(10)");
572 $dbh->do("ALTER TABLE deleteditems CHANGE homebranch homebranch VARCHAR(10)");
573 $dbh->do("ALTER TABLE statistics CHANGE branch branch VARCHAR(10)");
574 $dbh->do("ALTER TABLE subscription CHANGE lastbranch lastbranch VARCHAR(10)");
575 print "Upgrade to $DBversion done (extended missed branchcode columns to 10 chars)\n";
576 SetVersion ($DBversion);
577 }
578
579 $DBversion = "3.00.00.022";
580 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
581 $dbh->do("ALTER TABLE items
582 ADD `damaged` tinyint(1) default NULL AFTER notforloan");
583 $dbh->do("ALTER TABLE deleteditems
584 ADD `damaged` tinyint(1) default NULL AFTER notforloan");
585 print "Upgrade to $DBversion done (adding damaged column to items table)\n";
586 SetVersion ($DBversion);
587 }
588
589 $DBversion = "3.00.00.023";
590 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
591 $dbh->do("INSERT INTO `systempreferences` (variable,value,options,explanation,type)
592 VALUES ('yuipath','http://yui.yahooapis.com/2.3.1/build','Insert the path to YUI libraries','','free')");
593 print "Upgrade to $DBversion done (adding new system preference for controlling YUI path)\n";
594 SetVersion ($DBversion);
595 }
596 $DBversion = "3.00.00.024";
597 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
598 $dbh->do("ALTER TABLE biblioitems CHANGE itemtype itemtype VARCHAR(10)");
599 print "Upgrade to $DBversion done (changing itemtype to (10))\n";
600 SetVersion ($DBversion);
601 }
602
603 $DBversion = "3.00.00.025";
604 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
605 $dbh->do("ALTER TABLE items ADD COLUMN itype VARCHAR(10)");
606 $dbh->do("ALTER TABLE deleteditems ADD COLUMN itype VARCHAR(10) AFTER uri");
607 if(C4::Context->preference('item-level_itypes')){
608 $dbh->do('update items,biblioitems set items.itype=biblioitems.itemtype where items.biblionumber=biblioitems.biblionumber and itype is null');
609 }
610 print "Upgrade to $DBversion done (reintroduce items.itype - fill from itemtype)\n ";
611 SetVersion ($DBversion);
612 }
613
614 $DBversion = "3.00.00.026";
615 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
616 $dbh->do("INSERT INTO `systempreferences` (variable,value,options,explanation,type)
617 VALUES ('HomeOrHoldingBranch','homebranch','homebranch|holdingbranch','With independent branches turned on this decides whether to check the items holdingbranch or homebranch at circulatilon','choice')");
618 print "Upgrade to $DBversion done (adding new system preference for choosing whether homebranch or holdingbranch is checked in circulation)\n";
619 SetVersion ($DBversion);
620 }
621
622 $DBversion = "3.00.00.027";
623 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
624 $dbh->do("CREATE TABLE `marc_matchers` (
625 `matcher_id` int(11) NOT NULL auto_increment,
626 `code` varchar(10) NOT NULL default '',
627 `description` varchar(255) NOT NULL default '',
628 `record_type` varchar(10) NOT NULL default 'biblio',
629 `threshold` int(11) NOT NULL default 0,
630 PRIMARY KEY (`matcher_id`),
631 KEY `code` (`code`),
632 KEY `record_type` (`record_type`)
633 ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
634 $dbh->do("CREATE TABLE `matchpoints` (
635 `matcher_id` int(11) NOT NULL,
636 `matchpoint_id` int(11) NOT NULL auto_increment,
637 `search_index` varchar(30) NOT NULL default '',
638 `score` int(11) NOT NULL default 0,
639 PRIMARY KEY (`matchpoint_id`),
640 CONSTRAINT `matchpoints_ifbk_1` FOREIGN KEY (`matcher_id`)
641 REFERENCES `marc_matchers` (`matcher_id`) ON DELETE CASCADE ON UPDATE CASCADE
642 ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
643 $dbh->do("CREATE TABLE `matchpoint_components` (
644 `matchpoint_id` int(11) NOT NULL,
645 `matchpoint_component_id` int(11) NOT NULL auto_increment,
646 sequence int(11) NOT NULL default 0,
647 tag varchar(3) NOT NULL default '',
648 subfields varchar(40) NOT NULL default '',
649 offset int(4) NOT NULL default 0,
650 length int(4) NOT NULL default 0,
651 PRIMARY KEY (`matchpoint_component_id`),
652 KEY `by_sequence` (`matchpoint_id`, `sequence`),
653 CONSTRAINT `matchpoint_components_ifbk_1` 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 `matchpoint_component_norms` (
657 `matchpoint_component_id` int(11) NOT NULL,
658 `sequence` int(11) NOT NULL default 0,
659 `norm_routine` varchar(50) NOT NULL default '',
660 KEY `matchpoint_component_norms` (`matchpoint_component_id`, `sequence`),
661 CONSTRAINT `matchpoint_component_norms_ifbk_1` FOREIGN KEY (`matchpoint_component_id`)
662 REFERENCES `matchpoint_components` (`matchpoint_component_id`) ON DELETE CASCADE ON UPDATE CASCADE
663 ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
664 $dbh->do("CREATE TABLE `matcher_matchpoints` (
665 `matcher_id` int(11) NOT NULL,
666 `matchpoint_id` int(11) NOT NULL,
667 CONSTRAINT `matcher_matchpoints_ifbk_1` FOREIGN KEY (`matcher_id`)
668 REFERENCES `marc_matchers` (`matcher_id`) ON DELETE CASCADE ON UPDATE CASCADE,
669 CONSTRAINT `matcher_matchpoints_ifbk_2` FOREIGN KEY (`matchpoint_id`)
670 REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE
671 ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
672 $dbh->do("CREATE TABLE `matchchecks` (
673 `matcher_id` int(11) NOT NULL,
674 `matchcheck_id` int(11) NOT NULL auto_increment,
675 `source_matchpoint_id` int(11) NOT NULL,
676 `target_matchpoint_id` int(11) NOT NULL,
677 PRIMARY KEY (`matchcheck_id`),
678 CONSTRAINT `matcher_matchchecks_ifbk_1` FOREIGN KEY (`matcher_id`)
679 REFERENCES `marc_matchers` (`matcher_id`) ON DELETE CASCADE ON UPDATE CASCADE,
680 CONSTRAINT `matcher_matchchecks_ifbk_2` FOREIGN KEY (`source_matchpoint_id`)
681 REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE,
682 CONSTRAINT `matcher_matchchecks_ifbk_3` FOREIGN KEY (`target_matchpoint_id`)
683 REFERENCES `matchpoints` (`matchpoint_id`) ON DELETE CASCADE ON UPDATE CASCADE
684 ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
685 print "Upgrade to $DBversion done (added C4::Matcher serialization tables)\n ";
686 SetVersion ($DBversion);
687 }
688
689 $DBversion = "3.00.00.028";
690 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
691 $dbh->do("INSERT INTO `systempreferences` (variable,value,options,explanation,type)
692 VALUES ('canreservefromotherbranches','1','','With Independent branches on, can a user from one library reserve an item from another library','YesNo')");
693 print "Upgrade to $DBversion done (adding new system preference for changing reserve/holds behaviour with independent branches)\n";
694 SetVersion ($DBversion);
695 }
696
697
698 $DBversion = "3.00.00.029";
699 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
700 $dbh->do("ALTER TABLE `import_batches` ADD `matcher_id` int(11) NULL AFTER `import_batch_id`");
701 print "Upgrade to $DBversion done (adding matcher_id to import_batches)\n";
702 SetVersion ($DBversion);
703 }
704
705 $DBversion = "3.00.00.030";
706 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
707 $dbh->do("
708 CREATE TABLE services_throttle (
709 service_type varchar(10) NOT NULL default '',
710 service_count varchar(45) default NULL,
711 PRIMARY KEY (service_type)
712 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
713 ");
714 $dbh->do("INSERT INTO `systempreferences` (variable,value,options,explanation,type)
715 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')");
716 $dbh->do("INSERT INTO `systempreferences` (variable,value,options,explanation,type)
717 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')");
718 $dbh->do("INSERT INTO `systempreferences` (variable,value,options,explanation,type)
719 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')");
720 $dbh->do("INSERT INTO `systempreferences` (variable,value,options,explanation,type)
721 VALUES ('XISBNDailyLimit',499,'','The xISBN Web service is free for non-commercial use when usage does not exceed 500 requests per day','free')");
722 $dbh->do("INSERT INTO `systempreferences` (variable,value,options,explanation,type)
723 VALUES ('PINESISBN',0,'','Use with FRBRizeEditions. If ON, Koha will use PINES OISBN web service in the Editions tab on the detail pages.','YesNo')");
724 $dbh->do("INSERT INTO `systempreferences` (variable,value,options,explanation,type)
725 VALUES ('ThingISBN',0,'','Use with FRBRizeEditions. If ON, Koha will use the ThingISBN web service in the Editions tab on the detail pages.','YesNo')");
726 print "Upgrade to $DBversion done (adding services throttle table and sysprefs for xISBN)\n";
727 SetVersion ($DBversion);
728 }
729
730 $DBversion = "3.00.00.031";
731 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
732
733 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('QueryStemming',1,'If ON, enables query stemming',NULL,'YesNo')");
734 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('QueryFuzzy',1,'If ON, enables fuzzy option for searches',NULL,'YesNo')");
735 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('QueryWeightFields',1,'If ON, enables field weighting',NULL,'YesNo')");
736 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('WebBasedSelfCheck',0,'If ON, enables the web-based self-check system',NULL,'YesNo')");
737 $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')");
738 $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')");
739 $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')");
740 $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')");
741 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('defaultSortOrder',NULL,'Specify the default sort order','asc|dsc|az|za','Choice')");
742 $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')");
743 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('OPACdefaultSortOrder',NULL,'Specify the default sort order','asc|dsc|za|az','Choice')");
744 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('staffClientBaseURL','','Specify the base URL of the staff client',NULL,'free')");
745 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('minPasswordLength',3,'Specify the minimum length of a patron/staff password',NULL,'free')");
746 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('noItemTypeImages',0,'If ON, disables item-type images',NULL,'YesNo')");
747 $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')");
748 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('holdCancelLength','','Specify how many days before a hold is canceled',NULL,'free')");
749 $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')");
750 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('finesMode','test','Choose the fines mode, test or production','test|production','Choice')");
751 $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')");
752 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('itemBarcodeInputFilter','','If set, allows specification of a item barcode input filter','cuecat','Choice')");
753 $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')");
754 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('URLLinkText','','Text to display as the link anchor in the OPAC',NULL,'free')");
755 $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')");
756 $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')");
757 $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')");
758 $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')");
759 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('OPACUserCSS',0,'Add CSS to be included in the OPAC',NULL,'free')");
760
761 print "Upgrade to $DBversion done (adding additional system preference)\n";
762 SetVersion ($DBversion);
763 }
764
765 $DBversion = "3.00.00.032";
766 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
767 $dbh->do("UPDATE `marc_subfield_structure` SET `kohafield` = 'items.wthdrawn' WHERE `kohafield` = 'items.withdrawn'");
768 print "Upgrade to $DBversion done (fixed MARC framework references to items.withdrawn)\n";
769 SetVersion ($DBversion);
770 }
771
772 $DBversion = "3.00.00.033";
773 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
774 $dbh->do("INSERT INTO `userflags` VALUES(17,'staffaccess','Modify login / permissions for staff users',0)");
775 print "Upgrade to $DBversion done (Adding permissions flag for staff member access modification. )\n";
776 SetVersion ($DBversion);
777 }
778
779 $DBversion = "3.00.00.034";
780 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
781 $dbh->do("ALTER TABLE `virtualshelves` ADD COLUMN `sortfield` VARCHAR(16) ");
782 print "Upgrade to $DBversion done (Adding sortfield for Virtual Shelves. )\n";
783 SetVersion ($DBversion);
784 }
785
786 $DBversion = "3.00.00.035";
787 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
788 $dbh->do("UPDATE marc_subfield_structure
789 SET authorised_value = 'cn_source'
790 WHERE kohafield IN ('items.cn_source', 'biblioitems.cn_source')
791 AND (authorised_value is NULL OR authorised_value = '')");
792 print "Upgrade to $DBversion done (MARC frameworks: make classification source a drop-down)\n";
793 SetVersion ($DBversion);
794 }
795
796 $DBversion = "3.00.00.036";
797 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
798 $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');");
799 print "Upgrade to $DBversion done (OPACItemsResultsDisplay systempreference added)\n";
800 SetVersion ($DBversion);
801 }
802
803 $DBversion = "3.00.00.037";
804 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
805 $dbh->do("ALTER TABLE `borrowers` ADD COLUMN `altcontactfirstname` varchar(255)");
806 $dbh->do("ALTER TABLE `borrowers` ADD COLUMN `altcontactsurname` varchar(255)");
807 $dbh->do("ALTER TABLE `borrowers` ADD COLUMN `altcontactaddress1` varchar(255)");
808 $dbh->do("ALTER TABLE `borrowers` ADD COLUMN `altcontactaddress2` varchar(255)");
809 $dbh->do("ALTER TABLE `borrowers` ADD COLUMN `altcontactaddress3` varchar(255)");
810 $dbh->do("ALTER TABLE `borrowers` ADD COLUMN `altcontactzipcode` varchar(50)");
811 $dbh->do("ALTER TABLE `borrowers` ADD COLUMN `altcontactphone` varchar(50)");
812 print "Upgrade to $DBversion done (Adding Alternative Contact Person information to borrowers table)\n";
813 SetVersion ($DBversion);
814 }
815
816 $DBversion = "3.00.00.038";
817 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
818 $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'");
819 $dbh->do("DELETE FROM `systempreferences` WHERE variable='hideBiblioNumber'");
820 print "Upgrade to $DBversion done ('alter finesMode systempreference, remove superfluous syspref.')\n";
821 SetVersion ($DBversion);
822 }
823
824 $DBversion = "3.00.00.039";
825 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
826 $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')");
827 $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')");
828 $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')");
829 # $dbh->do("DELETE FROM `systempreferences` WHERE variable='HomeOrHoldingBranch'"); # Bug #2752
830 print "Upgrade to $DBversion done ('add circ sysprefs CircControl, finesCalendar, and uppercasesurnames, and delete HomeOrHoldingBranch.')\n";
831 SetVersion ($DBversion);
832 }
833
834 $DBversion = "3.00.00.040";
835 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
836 $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')");
837 $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')");
838 print "Upgrade to $DBversion done ('add circ sysprefs todaysIssuesDefaultSortOrder and previousIssuesDefaultSortOrder.')\n";
839 SetVersion ($DBversion);
840 }
841
842
843 $DBversion = "3.00.00.041";
844 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
845 # Strictly speaking it is not necessary to explicitly change
846 # NULL values to 0, because the ALTER TABLE statement will do that.
847 # However, setting them first avoids a warning.
848 $dbh->do("UPDATE items SET notforloan = 0 WHERE notforloan IS NULL");
849 $dbh->do("UPDATE items SET damaged = 0 WHERE damaged IS NULL");
850 $dbh->do("UPDATE items SET itemlost = 0 WHERE itemlost IS NULL");
851 $dbh->do("UPDATE items SET wthdrawn = 0 WHERE wthdrawn IS NULL");
852 $dbh->do("ALTER TABLE items
853 MODIFY notforloan tinyint(1) NOT NULL default 0,
854 MODIFY damaged tinyint(1) NOT NULL default 0,
855 MODIFY itemlost tinyint(1) NOT NULL default 0,
856 MODIFY wthdrawn tinyint(1) NOT NULL default 0");
857 $dbh->do("UPDATE deleteditems SET notforloan = 0 WHERE notforloan IS NULL");
858 $dbh->do("UPDATE deleteditems SET damaged = 0 WHERE damaged IS NULL");
859 $dbh->do("UPDATE deleteditems SET itemlost = 0 WHERE itemlost IS NULL");
860 $dbh->do("UPDATE deleteditems SET wthdrawn = 0 WHERE wthdrawn IS NULL");
861 $dbh->do("ALTER TABLE deleteditems
862 MODIFY notforloan tinyint(1) NOT NULL default 0,
863 MODIFY damaged tinyint(1) NOT NULL default 0,
864 MODIFY itemlost tinyint(1) NOT NULL default 0,
865 MODIFY wthdrawn tinyint(1) NOT NULL default 0");
866 print "Upgrade to $DBversion done (disallow NULL in several item status columns)\n";
867 SetVersion ($DBversion);
868 }
869
870 $DBversion = "3.00.00.04";
871 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
872 $dbh->do("ALTER TABLE aqbooksellers CHANGE name name mediumtext NOT NULL");
873 print "Upgrade to $DBversion done (disallow NULL in aqbooksellers.name; part of fix for bug 1251)\n";
874 SetVersion ($DBversion);
875 }
876
877 $DBversion = "3.00.00.043";
878 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
879 $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");
880 print "Upgrade to $DBversion done (currency table: add symbol and timestamp columns)\n";
881 SetVersion ($DBversion);
882 }
883
884 $DBversion = "3.00.00.044";
885 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
886 $dbh->do("ALTER TABLE deletedborrowers
887 ADD `altcontactfirstname` varchar(255) default NULL,
888 ADD `altcontactsurname` varchar(255) default NULL,
889 ADD `altcontactaddress1` varchar(255) default NULL,
890 ADD `altcontactaddress2` varchar(255) default NULL,
891 ADD `altcontactaddress3` varchar(255) default NULL,
892 ADD `altcontactzipcode` varchar(50) default NULL,
893 ADD `altcontactphone` varchar(50) default NULL
894 ");
895 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES
896 ('OPACBaseURL',NULL,'Specify the Base URL of the OPAC, e.g., opac.mylibrary.com, the http:// will be added automatically by Koha.',NULL,'Free'),
897 ('language','en','Set the default language in the staff client.',NULL,'Languages'),
898 ('QueryAutoTruncate',1,'If ON, query truncation is enabled by default',NULL,'YesNo'),
899 ('QueryRemoveStopwords',0,'If ON, stopwords listed in the Administration area will be removed from queries',NULL,'YesNo')
900 ");
901 print "Upgrade to $DBversion done (syncing deletedborrowers table with borrowers table)\n";
902 SetVersion ($DBversion);
903 }
904
905 #-- http://www.w3.org/International/articles/language-tags/
906
907 #-- RFC4646
908 $DBversion = "3.00.00.045";
909 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
910 $dbh->do("
911 CREATE TABLE language_subtag_registry (
912 subtag varchar(25),
913 type varchar(25), -- language-script-region-variant-extension-privateuse
914 description varchar(25), -- only one of the possible descriptions for ease of reference, see language_descriptions for the complete list
915 added date,
916 KEY `subtag` (`subtag`)
917 ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
918
919 #-- TODO: add suppress_scripts
920 #-- this maps three letter codes defined in iso639.2 back to their
921 #-- two letter equivilents in rfc4646 (LOC maintains iso639+)
922 $dbh->do("CREATE TABLE language_rfc4646_to_iso639 (
923 rfc4646_subtag varchar(25),
924 iso639_2_code varchar(25),
925 KEY `rfc4646_subtag` (`rfc4646_subtag`)
926 ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
927
928 $dbh->do("CREATE TABLE language_descriptions (
929 subtag varchar(25),
930 type varchar(25),
931 lang varchar(25),
932 description varchar(255),
933 KEY `lang` (`lang`)
934 ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
935
936 #-- bi-directional support, keyed by script subcode
937 $dbh->do("CREATE TABLE language_script_bidi (
938 rfc4646_subtag varchar(25), -- script subtag, Arab, Hebr, etc.
939 bidi varchar(3), -- rtl ltr
940 KEY `rfc4646_subtag` (`rfc4646_subtag`)
941 ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
942
943 #-- BIDI Stuff, Arabic and Hebrew
944 $dbh->do("INSERT INTO language_script_bidi(rfc4646_subtag,bidi)
945 VALUES( 'Arab', 'rtl')");
946 $dbh->do("INSERT INTO language_script_bidi(rfc4646_subtag,bidi)
947 VALUES( 'Hebr', 'rtl')");
948
949 #-- TODO: need to map language subtags to script subtags for detection
950 #-- of bidi when script is not specified (like ar, he)
951 $dbh->do("CREATE TABLE language_script_mapping (
952 language_subtag varchar(25),
953 script_subtag varchar(25),
954 KEY `language_subtag` (`language_subtag`)
955 ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
956
957 #-- Default mappings between script and language subcodes
958 $dbh->do("INSERT INTO language_script_mapping(language_subtag,script_subtag)
959 VALUES( 'ar', 'Arab')");
960 $dbh->do("INSERT INTO language_script_mapping(language_subtag,script_subtag)
961 VALUES( 'he', 'Hebr')");
962
963 print "Upgrade to $DBversion done (adding language subtag registry and basic BiDi support NOTE: You should import the subtag registry SQL)\n";
964 SetVersion ($DBversion);
965 }
966
967 $DBversion = "3.00.00.046";
968 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
969 $dbh->do("ALTER TABLE `subscription` CHANGE `numberlength` `numberlength` int(11) default '0' ,
970 CHANGE `weeklength` `weeklength` int(11) default '0'");
971 $dbh->do("CREATE TABLE `serialitems` (`serialid` int(11) NOT NULL, `itemnumber` int(11) NOT NULL, UNIQUE KEY `serialididx` (`serialid`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
972 $dbh->do("INSERT INTO `serialitems` SELECT `serialid`,`itemnumber` from serial where NOT ISNULL(itemnumber) && itemnumber <> '' && itemnumber NOT LIKE '%,%'");
973 print "Upgrade to $DBversion done (Add serialitems table to link serial issues to items. )\n";
974 SetVersion ($DBversion);
975 }
976
977 $DBversion = "3.00.00.047";
978 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
979 $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');");
980 print "Upgrade to $DBversion done ( Added OpacRenewalAllowed syspref )\n";
981 SetVersion ($DBversion);
982 }
983
984 $DBversion = "3.00.00.048";
985 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
986 $dbh->do("ALTER TABLE `items` ADD `more_subfields_xml` longtext default NULL AFTER `itype`");
987 print "Upgrade to $DBversion done (added items.more_subfields_xml)\n";
988 SetVersion ($DBversion);
989 }
990
991 $DBversion = "3.00.00.049";
992 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
993 $dbh->do("ALTER TABLE `z3950servers` ADD `encoding` text default NULL AFTER type ");
994 print "Upgrade to $DBversion done ( Added encoding field to z3950servers table )\n";
995 SetVersion ($DBversion);
996 }
997
998 $DBversion = "3.00.00.050";
999 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1000 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('OpacHighlightedWords','0','If Set, query matched terms are highlighted in OPAC',NULL,'YesNo');");
1001 print "Upgrade to $DBversion done ( Added OpacHighlightedWords syspref )\n";
1002 SetVersion ($DBversion);
1003 }
1004
1005 $DBversion = "3.00.00.051";
1006 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1007 $dbh->do("UPDATE systempreferences SET explanation = 'Define the current theme for the OPAC interface.' WHERE variable = 'opacthemes';");
1008 print "Upgrade to $DBversion done ( Corrected opacthemes explanation. )\n";
1009 SetVersion ($DBversion);
1010 }
1011
1012 $DBversion = "3.00.00.052";
1013 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1014 $dbh->do("ALTER TABLE `deleteditems` ADD `more_subfields_xml` LONGTEXT DEFAULT NULL AFTER `itype`");
1015 print "Upgrade to $DBversion done ( Adding missing column to deleteditems table. )\n";
1016 SetVersion ($DBversion);
1017 }
1018
1019 $DBversion = "3.00.00.053";
1020 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1021 $dbh->do("CREATE TABLE `printers_profile` (
1022 `prof_id` int(4) NOT NULL auto_increment,
1023 `printername` varchar(40) NOT NULL,
1024 `tmpl_id` int(4) NOT NULL,
1025 `paper_bin` varchar(20) NOT NULL,
1026 `offset_horz` float default NULL,
1027 `offset_vert` float default NULL,
1028 `creep_horz` float default NULL,
1029 `creep_vert` float default NULL,
1030 `unit` char(20) NOT NULL default 'POINT',
1031 PRIMARY KEY (`prof_id`),
1032 UNIQUE KEY `printername` (`printername`,`tmpl_id`,`paper_bin`),
1033 CONSTRAINT `printers_profile_pnfk_1` FOREIGN KEY (`tmpl_id`) REFERENCES `labels_templates` (`tmpl_id`) ON DELETE CASCADE ON UPDATE CASCADE
1034 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ");
1035 $dbh->do("CREATE TABLE `labels_profile` (
1036 `tmpl_id` int(4) NOT NULL,
1037 `prof_id` int(4) NOT NULL,
1038 UNIQUE KEY `tmpl_id` (`tmpl_id`),
1039 UNIQUE KEY `prof_id` (`prof_id`)
1040 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ");
1041 print "Upgrade to $DBversion done ( Printer Profile tables added )\n";
1042 SetVersion ($DBversion);
1043 }
1044
1045 $DBversion = "3.00.00.054";
1046 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1047 $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';");
1048 print "Upgrade to $DBversion done ( Added another barcode autogeneration sequence to barcode.pl. )\n";
1049 SetVersion ($DBversion);
1050 }
1051
1052 $DBversion = "3.00.00.055";
1053 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1054 $dbh->do("ALTER TABLE `zebraqueue` ADD KEY `zebraqueue_lookup` (`server`, `biblio_auth_number`, `operation`, `done`)");
1055 print "Upgrade to $DBversion done ( Added index on zebraqueue. )\n";
1056 SetVersion ($DBversion);
1057 }
1058 $DBversion = "3.00.00.056";
1059 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1060 if (C4::Context->preference("marcflavour") eq 'UNIMARC') {
1061 $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) ");
1062 } else {
1063 $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) ");
1064 }
1065 $dbh->do("ALTER TABLE `items` ADD `enumchron` VARCHAR(80) DEFAULT NULL;");
1066 print "Upgrade to $DBversion done ( Added item.enumchron column, and framework map to 952h )\n";
1067 SetVersion ($DBversion);
1068 }
1069
1070 $DBversion = "3.00.00.057";
1071 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1072 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('OAI-PMH','0','if ON, OAI-PMH server is enabled',NULL,'YesNo');");
1073 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('OAI-PMH:archiveID','KOHA-OAI-TEST','OAI-PMH archive identification',NULL,'Free');");
1074 $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');");
1075 $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');");
1076 $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');");
1077 SetVersion ($DBversion);
1078 }
1079
1080 $DBversion = "3.00.00.058";
1081 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1082 $dbh->do("ALTER TABLE `opac_news`
1083 CHANGE `lang` `lang` VARCHAR( 25 )
1084 CHARACTER SET utf8
1085 COLLATE utf8_general_ci
1086 NOT NULL default ''");
1087 print "Upgrade to $DBversion done ( lang field in opac_news made longer )\n";
1088 SetVersion ($DBversion);
1089 }
1090
1091 $DBversion = "3.00.00.059";
1092 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1093
1094 $dbh->do("CREATE TABLE IF NOT EXISTS `labels_templates` (
1095 `tmpl_id` int(4) NOT NULL auto_increment,
1096 `tmpl_code` char(100) default '',
1097 `tmpl_desc` char(100) default '',
1098 `page_width` float default '0',
1099 `page_height` float default '0',
1100 `label_width` float default '0',
1101 `label_height` float default '0',
1102 `topmargin` float default '0',
1103 `leftmargin` float default '0',
1104 `cols` int(2) default '0',
1105 `rows` int(2) default '0',
1106 `colgap` float default '0',
1107 `rowgap` float default '0',
1108 `active` int(1) default NULL,
1109 `units` char(20) default 'PX',
1110 `fontsize` int(4) NOT NULL default '3',
1111 PRIMARY KEY (`tmpl_id`)
1112 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;");
1113 $dbh->do("CREATE TABLE IF NOT EXISTS `printers_profile` (
1114 `prof_id` int(4) NOT NULL auto_increment,
1115 `printername` varchar(40) NOT NULL,
1116 `tmpl_id` int(4) NOT NULL,
1117 `paper_bin` varchar(20) NOT NULL,
1118 `offset_horz` float default NULL,
1119 `offset_vert` float default NULL,
1120 `creep_horz` float default NULL,
1121 `creep_vert` float default NULL,
1122 `unit` char(20) NOT NULL default 'POINT',
1123 PRIMARY KEY (`prof_id`),
1124 UNIQUE KEY `printername` (`printername`,`tmpl_id`,`paper_bin`),
1125 CONSTRAINT `printers_profile_pnfk_1` FOREIGN KEY (`tmpl_id`) REFERENCES `labels_templates` (`tmpl_id`) ON DELETE CASCADE ON UPDATE CASCADE
1126 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ");
1127 print "Upgrade to $DBversion done ( Added labels_templates table if it did not exist. )\n";
1128 SetVersion ($DBversion);
1129 }
1130
1131 $DBversion = "3.00.00.060";
1132 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1133 $dbh->do("CREATE TABLE IF NOT EXISTS `patronimage` (
1134 `cardnumber` varchar(16) NOT NULL,
1135 `mimetype` varchar(15) NOT NULL,
1136 `imagefile` mediumblob NOT NULL,
1137 PRIMARY KEY (`cardnumber`),
1138 CONSTRAINT `patronimage_fk1` FOREIGN KEY (`cardnumber`) REFERENCES `borrowers` (`cardnumber`) ON DELETE CASCADE ON UPDATE CASCADE
1139 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;");
1140 print "Upgrade to $DBversion done ( Added patronimage table. )\n";
1141 SetVersion ($DBversion);
1142 }
1143
1144 $DBversion = "3.00.00.061";
1145 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1146 $dbh->do("ALTER TABLE labels_templates ADD COLUMN font char(10) NOT NULL DEFAULT 'TR';");
1147 print "Upgrade to $DBversion done ( Added font column to labels_templates )\n";
1148 SetVersion ($DBversion);
1149 }
1150
1151 $DBversion = "3.00.00.062";
1152 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1153 $dbh->do("CREATE TABLE `old_issues` (
1154 `borrowernumber` int(11) default NULL,
1155 `itemnumber` int(11) default NULL,
1156 `date_due` date default NULL,
1157 `branchcode` varchar(10) default NULL,
1158 `issuingbranch` varchar(18) default NULL,
1159 `returndate` date default NULL,
1160 `lastreneweddate` date default NULL,
1161 `return` varchar(4) default NULL,
1162 `renewals` tinyint(4) default NULL,
1163 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1164 `issuedate` date default NULL,
1165 KEY `old_issuesborridx` (`borrowernumber`),
1166 KEY `old_issuesitemidx` (`itemnumber`),
1167 KEY `old_bordate` (`borrowernumber`,`timestamp`),
1168 CONSTRAINT `old_issues_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`)
1169 ON DELETE SET NULL ON UPDATE SET NULL,
1170 CONSTRAINT `old_issues_ibfk_2` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`)
1171 ON DELETE SET NULL ON UPDATE SET NULL
1172 ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
1173 $dbh->do("CREATE TABLE `old_reserves` (
1174 `borrowernumber` int(11) default NULL,
1175 `reservedate` date default NULL,
1176 `biblionumber` int(11) default NULL,
1177 `constrainttype` varchar(1) default NULL,
1178 `branchcode` varchar(10) default NULL,
1179 `notificationdate` date default NULL,
1180 `reminderdate` date default NULL,
1181 `cancellationdate` date default NULL,
1182 `reservenotes` mediumtext,
1183 `priority` smallint(6) default NULL,
1184 `found` varchar(1) default NULL,
1185 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1186 `itemnumber` int(11) default NULL,
1187 `waitingdate` date default NULL,
1188 KEY `old_reserves_borrowernumber` (`borrowernumber`),
1189 KEY `old_reserves_biblionumber` (`biblionumber`),
1190 KEY `old_reserves_itemnumber` (`itemnumber`),
1191 KEY `old_reserves_branchcode` (`branchcode`),
1192 CONSTRAINT `old_reserves_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`)
1193 ON DELETE SET NULL ON UPDATE SET NULL,
1194 CONSTRAINT `old_reserves_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`)
1195 ON DELETE SET NULL ON UPDATE SET NULL,
1196 CONSTRAINT `old_reserves_ibfk_3` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`)
1197 ON DELETE SET NULL ON UPDATE SET NULL
1198 ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
1199
1200 # move closed transactions to old_* tables
1201 $dbh->do("INSERT INTO old_issues SELECT * FROM issues WHERE returndate IS NOT NULL");
1202 $dbh->do("DELETE FROM issues WHERE returndate IS NOT NULL");
1203 $dbh->do("INSERT INTO old_reserves SELECT * FROM reserves WHERE cancellationdate IS NOT NULL OR found = 'F'");
1204 $dbh->do("DELETE FROM reserves WHERE cancellationdate IS NOT NULL OR found = 'F'");
1205
1206 print "Upgrade to $DBversion done ( Added old_issues and old_reserves tables )\n";
1207 SetVersion ($DBversion);
1208 }
1209
1210 $DBversion = "3.00.00.063";
1211 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1212 $dbh->do("ALTER TABLE deleteditems
1213 CHANGE COLUMN booksellerid booksellerid MEDIUMTEXT DEFAULT NULL,
1214 ADD COLUMN enumchron VARCHAR(80) DEFAULT NULL AFTER more_subfields_xml,
1215 ADD COLUMN copynumber SMALLINT(6) DEFAULT NULL AFTER enumchron;");
1216 $dbh->do("ALTER TABLE items
1217 CHANGE COLUMN booksellerid booksellerid MEDIUMTEXT,
1218 ADD COLUMN copynumber SMALLINT(6) DEFAULT NULL AFTER enumchron;");
1219 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";
1220 SetVersion ($DBversion);
1221 }
1222
1223 $DBversion = "3.00.00.064";
1224 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1225 $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');");
1226 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('AWSAccessKeyID','','See: http://aws.amazon.com','','free');");
1227 $dbh->do("DELETE FROM `systempreferences` WHERE variable='AmazonDevKey';");
1228 $dbh->do("DELETE FROM `systempreferences` WHERE variable='XISBNAmazonSimilarItems';");
1229 $dbh->do("DELETE FROM `systempreferences` WHERE variable='OPACXISBNAmazonSimilarItems';");
1230 print "Upgrade to $DBversion done (IMPORTANT: Upgrading to Amazon.com Associates Web Service 4.0 ) \n";
1231 SetVersion ($DBversion);
1232 }
1233
1234 $DBversion = "3.00.00.065";
1235 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1236 $dbh->do("CREATE TABLE `patroncards` (
1237 `cardid` int(11) NOT NULL auto_increment,
1238 `batch_id` varchar(10) NOT NULL default '1',
1239 `borrowernumber` int(11) NOT NULL,
1240 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1241 PRIMARY KEY (`cardid`),
1242 KEY `patroncards_ibfk_1` (`borrowernumber`),
1243 CONSTRAINT `patroncards_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE
1244 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;");
1245 print "Upgrade to $DBversion done (Adding patroncards table for patroncards generation feature. ) \n";
1246 SetVersion ($DBversion);
1247 }
1248
1249 $DBversion = "3.00.00.066";
1250 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1251 $dbh->do("ALTER TABLE `virtualshelfcontents` MODIFY `dateadded` timestamp NOT NULL
1252 DEFAULT CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP;
1253 ");
1254 print "Upgrade to $DBversion done (fix for bug 1873: virtualshelfcontents dateadded column empty. ) \n";
1255 SetVersion ($DBversion);
1256 }
1257
1258 $DBversion = "3.00.00.067";
1259 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1260 $dbh->do("UPDATE systempreferences SET explanation = 'Enable patron images for the Staff Client', type = 'YesNo' WHERE variable = 'patronimages'");
1261 print "Upgrade to $DBversion done (Updating patronimages syspref to reflect current kohastructure.sql. ) \n";
1262 SetVersion ($DBversion);
1263 }
1264
1265 $DBversion = "3.00.00.068";
1266 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1267 $dbh->do("CREATE TABLE `permissions` (
1268 `module_bit` int(11) NOT NULL DEFAULT 0,
1269 `code` varchar(30) DEFAULT NULL,
1270 `description` varchar(255) DEFAULT NULL,
1271 PRIMARY KEY (`module_bit`, `code`),
1272 CONSTRAINT `permissions_ibfk_1` FOREIGN KEY (`module_bit`) REFERENCES `userflags` (`bit`)
1273 ON DELETE CASCADE ON UPDATE CASCADE
1274 ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
1275 $dbh->do("CREATE TABLE `user_permissions` (
1276 `borrowernumber` int(11) NOT NULL DEFAULT 0,
1277 `module_bit` int(11) NOT NULL DEFAULT 0,
1278 `code` varchar(30) DEFAULT NULL,
1279 CONSTRAINT `user_permissions_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`)
1280 ON DELETE CASCADE ON UPDATE CASCADE,
1281 CONSTRAINT `user_permissions_ibfk_2` FOREIGN KEY (`module_bit`, `code`)
1282 REFERENCES `permissions` (`module_bit`, `code`)
1283 ON DELETE CASCADE ON UPDATE CASCADE
1284 ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
1285
1286 $dbh->do("INSERT INTO permissions (module_bit, code, description) VALUES
1287 (13, 'edit_news', 'Write news for the OPAC and staff interfaces'),
1288 (13, 'label_creator', 'Create printable labels and barcodes from catalog and patron data'),
1289 (13, 'edit_calendar', 'Define days when the library is closed'),
1290 (13, 'moderate_comments', 'Moderate patron comments'),
1291 (13, 'edit_notices', 'Define notices'),
1292 (13, 'edit_notice_status_triggers', 'Set notice/status triggers for overdue items'),
1293 (13, 'view_system_logs', 'Browse the system logs'),
1294 (13, 'inventory', 'Perform inventory (stocktaking) of your catalogue'),
1295 (13, 'stage_marc_import', 'Stage MARC records into the reservoir'),
1296 (13, 'manage_staged_marc', 'Managed staged MARC records, including completing and reversing imports'),
1297 (13, 'export_catalog', 'Export bibliographic and holdings data'),
1298 (13, 'import_patrons', 'Import patron data'),
1299 (13, 'delete_anonymize_patrons', 'Delete old borrowers and anonymize circulation history (deletes borrower reading history)'),
1300 (13, 'batch_upload_patron_images', 'Upload patron images in batch or one at a time'),
1301 (13, 'schedule_tasks', 'Schedule tasks to run')");
1302
1303 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('GranularPermissions','0','Use detailed staff user permissions',NULL,'YesNo')");
1304
1305 print "Upgrade to $DBversion done (adding permissions and user_permissions tables and GranularPermissions syspref) \n";
1306 SetVersion ($DBversion);
1307 }
1308 $DBversion = "3.00.00.069";
1309 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1310 $dbh->do("ALTER TABLE labels_conf CHANGE COLUMN class classification int(1) DEFAULT NULL;");
1311 print "Upgrade to $DBversion done ( Correcting columname in labels_conf )\n";
1312 SetVersion ($DBversion);
1313 }
1314
1315 $DBversion = "3.00.00.070";
1316 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1317 $sth = $dbh->prepare("SELECT value FROM systempreferences WHERE variable='yuipath'");
1318 $sth->execute;
1319 my ($value) = $sth->fetchrow;
1320 $value =~ s/2.3.1/2.5.1/;
1321 $dbh->do("UPDATE systempreferences SET value='$value' WHERE variable='yuipath';");
1322 print "Update yuipath syspref to 2.5.1 if necessary\n";
1323 SetVersion ($DBversion);
1324 }
1325
1326 $DBversion = "3.00.00.071";
1327 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1328 $dbh->do(" ALTER TABLE `subscription` ADD `serialsadditems` TINYINT( 1 ) NOT NULL DEFAULT '0';");
1329 # fill the new field with the previous systempreference value, then drop the syspref
1330 my $sth = $dbh->prepare("SELECT value FROM systempreferences WHERE variable='serialsadditems'");
1331 $sth->execute;
1332 my ($serialsadditems) = $sth->fetchrow();
1333 $dbh->do("UPDATE subscription SET serialsadditems=$serialsadditems");
1334 $dbh->do("DELETE FROM systempreferences WHERE variable='serialsadditems'");
1335 print "Upgrade to $DBversion done ( moving serialsadditems from syspref to subscription )\n";
1336 SetVersion ($DBversion);
1337 }
1338
1339 $DBversion = "3.00.00.072";
1340 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1341 $dbh->do("ALTER TABLE labels_conf ADD COLUMN formatstring mediumtext DEFAULT NULL AFTER printingtype");
1342 print "Upgrade to $DBversion done ( Adding format string to labels generator. )\n";
1343 SetVersion ($DBversion);
1344 }
1345
1346 $DBversion = "3.00.00.073";
1347 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1348 $dbh->do("DROP TABLE IF EXISTS `tags_all`;");
1349 $dbh->do(q#
1350 CREATE TABLE `tags_all` (
1351 `tag_id` int(11) NOT NULL auto_increment,
1352 `borrowernumber` int(11) NOT NULL,
1353 `biblionumber` int(11) NOT NULL,
1354 `term` varchar(255) NOT NULL,
1355 `language` int(4) default NULL,
1356 `date_created` datetime NOT NULL,
1357 PRIMARY KEY (`tag_id`),
1358 KEY `tags_borrowers_fk_1` (`borrowernumber`),
1359 KEY `tags_biblionumber_fk_1` (`biblionumber`),
1360 CONSTRAINT `tags_borrowers_fk_1` FOREIGN KEY (`borrowernumber`)
1361 REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1362 CONSTRAINT `tags_biblionumber_fk_1` FOREIGN KEY (`biblionumber`)
1363 REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE
1364 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1365 #);
1366 $dbh->do("DROP TABLE IF EXISTS `tags_approval`;");
1367 $dbh->do(q#
1368 CREATE TABLE `tags_approval` (
1369 `term` varchar(255) NOT NULL,
1370 `approved` int(1) NOT NULL default '0',
1371 `date_approved` datetime default NULL,
1372 `approved_by` int(11) default NULL,
1373 `weight_total` int(9) NOT NULL default '1',
1374 PRIMARY KEY (`term`),
1375 KEY `tags_approval_borrowers_fk_1` (`approved_by`),
1376 CONSTRAINT `tags_approval_borrowers_fk_1` FOREIGN KEY (`approved_by`)
1377 REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE
1378 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1379 #);
1380 $dbh->do("DROP TABLE IF EXISTS `tags_index`;");
1381 $dbh->do(q#
1382 CREATE TABLE `tags_index` (
1383 `term` varchar(255) NOT NULL,
1384 `biblionumber` int(11) NOT NULL,
1385 `weight` int(9) NOT NULL default '1',
1386 PRIMARY KEY (`term`,`biblionumber`),
1387 KEY `tags_index_biblionumber_fk_1` (`biblionumber`),
1388 CONSTRAINT `tags_index_term_fk_1` FOREIGN KEY (`term`)
1389 REFERENCES `tags_approval` (`term`) ON DELETE CASCADE ON UPDATE CASCADE,
1390 CONSTRAINT `tags_index_biblionumber_fk_1` FOREIGN KEY (`biblionumber`)
1391 REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE
1392 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1393 #);
1394 $dbh->do(q#
1395 INSERT INTO `systempreferences` VALUES
1396 ('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. Example: ocls.mylibrarybookstore.com/MLB/actions/searchHandler.do?nextPage=bookDetails&parentNum=10923&key=',''),
1397 ('BakerTaylorEnabled','0','','Enable or disable all Baker & Taylor features.','YesNo'),
1398 ('BakerTaylorPassword','','','Baker & Taylor Password for Content Cafe (external content)','Textarea'),
1399 ('BakerTaylorUsername','','','Baker & Taylor Username for Content Cafe (external content)','Textarea'),
1400 ('TagsEnabled','1','','Enables or disables all tagging features. This is the main switch for tags.','YesNo'),
1401 ('TagsExternalDictionary',NULL,'','Path on server to local ispell executable, used to set $Lingua::Ispell::path This dictionary is used as a \"whitelist\" of pre-allowed tags.',''),
1402 ('TagsInputOnDetail','1','','Allow users to input tags from the detail page.', 'YesNo'),
1403 ('TagsInputOnList', '0','','Allow users to input tags from the search results list.', 'YesNo'),
1404 ('TagsModeration', NULL,'','Require tags from patrons to be approved before becoming visible.','YesNo'),
1405 ('TagsShowOnDetail','10','','Number of tags to display on detail page. 0 is off.', 'Integer'),
1406 ('TagsShowOnList', '6','','Number of tags to display on search results list. 0 is off.','Integer')
1407 #);
1408 print "Upgrade to $DBversion done (Baker/Taylor,Tags: sysprefs and tables (tags_all, tags_index, tags_approval)) \n";
1409 SetVersion ($DBversion);
1410 }
1411
1412 $DBversion = "3.00.00.074";
1413 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1414 $dbh->do( q(update itemtypes set imageurl = concat( 'npl/', imageurl )
1415 where imageurl not like 'http%'
1416 and imageurl is not NULL
1417 and imageurl != '') );
1418 print "Upgrade to $DBversion done (updating imagetype.imageurls to reflect new icon locations.)\n";
1419 SetVersion ($DBversion);
1420 }
1421
1422 $DBversion = "3.00.00.075";
1423 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1424 $dbh->do( q(alter table authorised_values add imageurl varchar(200) default NULL) );
1425 print "Upgrade to $DBversion done (adding imageurl field to authorised_values table)\n";
1426 SetVersion ($DBversion);
1427 }
1428
1429 $DBversion = "3.00.00.076";
1430 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1431 $dbh->do("ALTER TABLE import_batches
1432 ADD COLUMN nomatch_action enum('create_new', 'ignore') NOT NULL default 'create_new' AFTER overlay_action");
1433 $dbh->do("ALTER TABLE import_batches
1434 ADD COLUMN item_action enum('always_add', 'add_only_for_matches', 'add_only_for_new', 'ignore')
1435 NOT NULL default 'always_add' AFTER nomatch_action");
1436 $dbh->do("ALTER TABLE import_batches
1437 MODIFY overlay_action enum('replace', 'create_new', 'use_template', 'ignore')
1438 NOT NULL default 'create_new'");
1439 $dbh->do("ALTER TABLE import_records
1440 MODIFY status enum('error', 'staged', 'imported', 'reverted', 'items_reverted',
1441 'ignored') NOT NULL default 'staged'");
1442 $dbh->do("ALTER TABLE import_items
1443 MODIFY status enum('error', 'staged', 'imported', 'reverted', 'ignored') NOT NULL default 'staged'");
1444
1445 print "Upgrade to $DBversion done (changes to import_batches and import_records)\n";
1446 SetVersion ($DBversion);
1447 }
1448
1449 $DBversion = "3.00.00.077";
1450 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1451 # drop these tables only if they exist and none of them are empty
1452 # these tables are not defined in the packaged 2.2.9, but since it is believed
1453 # that at least one library may be using them in a post-2.2.9 but pre-3.0 Koha,
1454 # some care is taken.
1455 my ($print_error) = $dbh->{PrintError};
1456 $dbh->{PrintError} = 0;
1457 my ($raise_error) = $dbh->{RaiseError};
1458 $dbh->{RaiseError} = 1;
1459
1460 my $count = 0;
1461 my $do_drop = 1;
1462 eval { $count = $dbh->do("SELECT 1 FROM categorytable"); };
1463 if ($count > 0) {
1464 $do_drop = 0;
1465 }
1466 eval { $count = $dbh->do("SELECT 1 FROM mediatypetable"); };
1467 if ($count > 0) {
1468 $do_drop = 0;
1469 }
1470 eval { $count = $dbh->do("SELECT 1 FROM subcategorytable"); };
1471 if ($count > 0) {
1472 $do_drop = 0;
1473 }
1474
1475 if ($do_drop) {
1476 $dbh->do("DROP TABLE IF EXISTS `categorytable`");
1477 $dbh->do("DROP TABLE IF EXISTS `mediatypetable`");
1478 $dbh->do("DROP TABLE IF EXISTS `subcategorytable`");
1479 }
1480
1481 $dbh->{PrintError} = $print_error;
1482 $dbh->{RaiseError} = $raise_error;
1483 print "Upgrade to $DBversion done (drop categorytable, subcategorytable, and mediatypetable)\n";
1484 SetVersion ($DBversion);
1485 }
1486
1487 $DBversion = "3.00.00.078";
1488 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1489 my ($print_error) = $dbh->{PrintError};
1490 $dbh->{PrintError} = 0;
1491
1492 unless ($dbh->do("SELECT 1 FROM browser")) {
1493 $dbh->{PrintError} = $print_error;
1494 $dbh->do("CREATE TABLE `browser` (
1495 `level` int(11) NOT NULL,
1496 `classification` varchar(20) NOT NULL,
1497 `description` varchar(255) NOT NULL,
1498 `number` bigint(20) NOT NULL,
1499 `endnode` tinyint(4) NOT NULL
1500 ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
1501 }
1502 $dbh->{PrintError} = $print_error;
1503 print "Upgrade to $DBversion done (add browser table if not already present)\n";
1504 SetVersion ($DBversion);
1505 }
1506
1507 $DBversion = "3.00.00.079";
1508 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1509 my ($print_error) = $dbh->{PrintError};
1510 $dbh->{PrintError} = 0;
1511
1512 $dbh->do("INSERT INTO `systempreferences` (variable, value,options,type, explanation)VALUES
1513 ('AddPatronLists','categorycode','categorycode|category_type','Choice','Allow user to choose what list to pick up from when adding patrons')");
1514 print "Upgrade to $DBversion done (add browser table if not already present)\n";
1515 SetVersion ($DBversion);
1516 }
1517
1518 $DBversion = "3.00.00.080";
1519 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1520 $dbh->do("ALTER TABLE subscription CHANGE monthlength monthlength int(11) default '0'");
1521 $dbh->do("ALTER TABLE deleteditems MODIFY marc LONGBLOB AFTER copynumber");
1522 $dbh->do("ALTER TABLE aqbooksellers CHANGE name name mediumtext NOT NULL");
1523 print "Upgrade to $DBversion done (catch up on DB schema changes since alpha and beta)\n";
1524 SetVersion ($DBversion);
1525 }
1526
1527 $DBversion = "3.00.00.081";
1528 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1529 $dbh->do("CREATE TABLE `borrower_attribute_types` (
1530 `code` varchar(10) NOT NULL,
1531 `description` varchar(255) NOT NULL,
1532 `repeatable` tinyint(1) NOT NULL default 0,
1533 `unique_id` tinyint(1) NOT NULL default 0,
1534 `opac_display` tinyint(1) NOT NULL default 0,
1535 `password_allowed` tinyint(1) NOT NULL default 0,
1536 `staff_searchable` tinyint(1) NOT NULL default 0,
1537 `authorised_value_category` varchar(10) default NULL,
1538 PRIMARY KEY (`code`)
1539 ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
1540 $dbh->do("CREATE TABLE `borrower_attributes` (
1541 `borrowernumber` int(11) NOT NULL,
1542 `code` varchar(10) NOT NULL,
1543 `attribute` varchar(30) default NULL,
1544 `password` varchar(30) default NULL,
1545 KEY `borrowernumber` (`borrowernumber`),
1546 KEY `code_attribute` (`code`, `attribute`),
1547 CONSTRAINT `borrower_attributes_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`)
1548 ON DELETE CASCADE ON UPDATE CASCADE,
1549 CONSTRAINT `borrower_attributes_ibfk_2` FOREIGN KEY (`code`) REFERENCES `borrower_attribute_types` (`code`)
1550 ON DELETE CASCADE ON UPDATE CASCADE
1551 ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
1552 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('ExtendedPatronAttributes','0','Use extended patron IDs and attributes',NULL,'YesNo')");
1553 print "Upgrade to $DBversion done (added borrower_attributes and borrower_attribute_types)\n";
1554 SetVersion ($DBversion);
1555 }
1556
1557 $DBversion = "3.00.00.082";
1558 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1559 $dbh->do( q(alter table accountlines add column lastincrement decimal(28,6) default NULL) );
1560 print "Upgrade to $DBversion done (adding lastincrement column to accountlines table)\n";
1561 SetVersion ($DBversion);
1562 }
1563
1564 $DBversion = "3.00.00.083";
1565 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1566 $dbh->do( qq(UPDATE systempreferences SET value='local' where variable='yuipath' and value like "%/intranet-tmpl/prog/%"));
1567 print "Upgrade to $DBversion done (Changing yuipath behaviour in managing a local value)\n";
1568 SetVersion ($DBversion);
1569 }
1570 $DBversion = "3.00.00.084";
1571 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1572 $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')");
1573 $dbh->do("INSERT INTO systempreferences (variable,value,explanation,options,type) VALUES('GoogleJackets','0','if ON, displays jacket covers from Google Books API',NULL,'YesNo')");
1574 print "Upgrade to $DBversion done (add new sysprefs)\n";
1575 SetVersion ($DBversion);
1576 }
1577
1578 $DBversion = "3.00.00.085";
1579 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1580 if (C4::Context->preference("marcflavour") eq 'MARC21') {
1581 $dbh->do("UPDATE marc_subfield_structure SET tab = 0 WHERE tab = 9 AND tagfield = '037'");
1582 $dbh->do("UPDATE marc_subfield_structure SET tab = 1 WHERE tab = 6 AND tagfield in ('100', '110', '111', '130')");
1583 $dbh->do("UPDATE marc_subfield_structure SET tab = 2 WHERE tab = 6 AND tagfield in ('240', '243')");
1584 $dbh->do("UPDATE marc_subfield_structure SET tab = 4 WHERE tab = 6 AND tagfield in ('400', '410', '411', '440')");
1585 $dbh->do("UPDATE marc_subfield_structure SET tab = 5 WHERE tab = 9 AND tagfield = '584'");
1586 $dbh->do("UPDATE marc_subfield_structure SET tab = 7 WHERE tab = -6 AND tagfield = '760'");
1587 }
1588 print "Upgrade to $DBversion done (move editing tab of various MARC21 subfields)\n";
1589 SetVersion ($DBversion);
1590 }
1591
1592 $DBversion = "3.00.00.086";
1593 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1594 $dbh->do(
1595 "CREATE TABLE `tmp_holdsqueue` (
1596 `biblionumber` int(11) default NULL,
1597 `itemnumber` int(11) default NULL,
1598 `barcode` varchar(20) default NULL,
1599 `surname` mediumtext NOT NULL,
1600 `firstname` text,
1601 `phone` text,
1602 `borrowernumber` int(11) NOT NULL,
1603 `cardnumber` varchar(16) default NULL,
1604 `reservedate` date default NULL,
1605 `title` mediumtext,
1606 `itemcallnumber` varchar(30) default NULL,
1607 `holdingbranch` varchar(10) default NULL,
1608 `pickbranch` varchar(10) default NULL,
1609 `notes` text
1610 ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
1611
1612 $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')");
1613 $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')");
1614
1615 print "Upgrade to $DBversion done (Table structure for table `tmp_holdsqueue`)\n";
1616 SetVersion ($DBversion);
1617 }
1618
1619 $DBversion = "3.00.00.087";
1620 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1621 $dbh->do("INSERT INTO `systempreferences` VALUES ('AutoEmailOpacUser','0','','Sends notification emails containing new account details to patrons - when account is created.','YesNo')" );
1622 $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')");
1623 print "Upgrade to $DBversion done (added 2 new 'AutoEmailOpacUser' sysprefs)\n";
1624 SetVersion ($DBversion);
1625 }
1626
1627 $DBversion = "3.00.00.088";
1628 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1629 $dbh->do("INSERT INTO `systempreferences` (variable,value,options,explanation,type) VALUES ('OPACShelfBrowser','1','','Enable/disable Shelf Browser on item details page','YesNo')");
1630 $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')");
1631 $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')");
1632 $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')");
1633 print "Upgrade to $DBversion done (added 2 new 'AutoEmailOpacUser' sysprefs)\n";
1634 SetVersion ($DBversion);
1635 }
1636
1637 $DBversion = "3.00.00.089";
1638 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1639 $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')");
1640 print "Upgrade to $DBversion done (added new AdvancedSearchTypes syspref)\n";
1641 SetVersion ($DBversion);
1642 }
1643
1644 $DBversion = "3.00.00.090";
1645 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1646 $dbh->do("
1647 CREATE TABLE `branch_borrower_circ_rules` (
1648 `branchcode` VARCHAR(10) NOT NULL,
1649 `categorycode` VARCHAR(10) NOT NULL,
1650 `maxissueqty` int(4) default NULL,
1651 PRIMARY KEY (`categorycode`, `branchcode`),
1652 CONSTRAINT `branch_borrower_circ_rules_ibfk_1` FOREIGN KEY (`categorycode`) REFERENCES `categories` (`categorycode`)
1653 ON DELETE CASCADE ON UPDATE CASCADE,
1654 CONSTRAINT `branch_borrower_circ_rules_ibfk_2` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`)
1655 ON DELETE CASCADE ON UPDATE CASCADE
1656 ) ENGINE=InnoDB DEFAULT CHARSET=utf8
1657 ");
1658 $dbh->do("
1659 CREATE TABLE `default_borrower_circ_rules` (
1660 `categorycode` VARCHAR(10) NOT NULL,
1661 `maxissueqty` int(4) default NULL,
1662 PRIMARY KEY (`categorycode`),
1663 CONSTRAINT `borrower_borrower_circ_rules_ibfk_1` FOREIGN KEY (`categorycode`) REFERENCES `categories` (`categorycode`)
1664 ON DELETE CASCADE ON UPDATE CASCADE
1665 ) ENGINE=InnoDB DEFAULT CHARSET=utf8
1666 ");
1667 $dbh->do("
1668 CREATE TABLE `default_branch_circ_rules` (
1669 `branchcode` VARCHAR(10) NOT NULL,
1670 `maxissueqty` int(4) default NULL,
1671 PRIMARY KEY (`branchcode`),
1672 CONSTRAINT `default_branch_circ_rules_ibfk_1` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`)
1673 ON DELETE CASCADE ON UPDATE CASCADE
1674 ) ENGINE=InnoDB DEFAULT CHARSET=utf8
1675 ");
1676 $dbh->do("
1677 CREATE TABLE `default_circ_rules` (
1678 `singleton` enum('singleton') NOT NULL default 'singleton',
1679 `maxissueqty` int(4) default NULL,
1680 PRIMARY KEY (`singleton`)
1681 ) ENGINE=InnoDB DEFAULT CHARSET=utf8
1682 ");
1683 print "Upgrade to $DBversion done (added several circ rules tables)\n";
1684 SetVersion ($DBversion);
1685 }
1686
1687
1688 $DBversion = "3.00.00.091";
1689 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1690 $dbh->do(<<'END_SQL');
1691 ALTER TABLE borrowers
1692 ADD `smsalertnumber` varchar(50) default NULL
1693 END_SQL
1694
1695 $dbh->do(<<'END_SQL');
1696 CREATE TABLE `message_attributes` (
1697 `message_attribute_id` int(11) NOT NULL auto_increment,
1698 `message_name` varchar(20) NOT NULL default '',
1699 `takes_days` tinyint(1) NOT NULL default '0',
1700 PRIMARY KEY (`message_attribute_id`),
1701 UNIQUE KEY `message_name` (`message_name`)
1702 ) ENGINE=InnoDB DEFAULT CHARSET=utf8
1703 END_SQL
1704
1705 $dbh->do(<<'END_SQL');
1706 CREATE TABLE `message_transport_types` (
1707 `message_transport_type` varchar(20) NOT NULL,
1708 PRIMARY KEY (`message_transport_type`)
1709 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1710 END_SQL
1711
1712 $dbh->do(<<'END_SQL');
1713 CREATE TABLE `message_transports` (
1714 `message_attribute_id` int(11) NOT NULL,
1715 `message_transport_type` varchar(20) NOT NULL,
1716 `is_digest` tinyint(1) NOT NULL default '0',
1717 `letter_module` varchar(20) NOT NULL default '',
1718 `letter_code` varchar(20) NOT NULL default '',
1719 PRIMARY KEY (`message_attribute_id`,`message_transport_type`,`is_digest`),
1720 KEY `message_transport_type` (`message_transport_type`),
1721 KEY `letter_module` (`letter_module`,`letter_code`),
1722 CONSTRAINT `message_transports_ibfk_1` FOREIGN KEY (`message_attribute_id`) REFERENCES `message_attributes` (`message_attribute_id`) ON DELETE CASCADE ON UPDATE CASCADE,
1723 CONSTRAINT `message_transports_ibfk_2` FOREIGN KEY (`message_transport_type`) REFERENCES `message_transport_types` (`message_transport_type`) ON DELETE CASCADE ON UPDATE CASCADE,
1724 CONSTRAINT `message_transports_ibfk_3` FOREIGN KEY (`letter_module`, `letter_code`) REFERENCES `letter` (`module`, `code`) ON DELETE CASCADE ON UPDATE CASCADE
1725 ) ENGINE=InnoDB DEFAULT CHARSET=utf8
1726 END_SQL
1727
1728 $dbh->do(<<'END_SQL');
1729 CREATE TABLE `borrower_message_preferences` (
1730 `borrower_message_preference_id` int(11) NOT NULL auto_increment,
1731 `borrowernumber` int(11) NOT NULL default '0',
1732 `message_attribute_id` int(11) default '0',
1733 `days_in_advance` int(11) default '0',
1734 `wants_digets` tinyint(1) NOT NULL default '0',
1735 PRIMARY KEY (`borrower_message_preference_id`),
1736 KEY `borrowernumber` (`borrowernumber`),
1737 KEY `message_attribute_id` (`message_attribute_id`),
1738 CONSTRAINT `borrower_message_preferences_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1739 CONSTRAINT `borrower_message_preferences_ibfk_2` FOREIGN KEY (`message_attribute_id`) REFERENCES `message_attributes` (`message_attribute_id`) ON DELETE CASCADE ON UPDATE CASCADE
1740 ) ENGINE=InnoDB DEFAULT CHARSET=utf8
1741 END_SQL
1742
1743 $dbh->do(<<'END_SQL');
1744 CREATE TABLE `borrower_message_transport_preferences` (
1745 `borrower_message_preference_id` int(11) NOT NULL default '0',
1746 `message_transport_type` varchar(20) NOT NULL default '0',
1747 PRIMARY KEY (`borrower_message_preference_id`,`message_transport_type`),
1748 KEY `message_transport_type` (`message_transport_type`),
1749 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,
1750 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
1751 ) ENGINE=InnoDB DEFAULT CHARSET=utf8
1752 END_SQL
1753
1754 $dbh->do(<<'END_SQL');
1755 CREATE TABLE `message_queue` (
1756 `message_id` int(11) NOT NULL auto_increment,
1757 `borrowernumber` int(11) NOT NULL,
1758 `subject` text,
1759 `content` text,
1760 `message_transport_type` varchar(20) NOT NULL,
1761 `status` enum('sent','pending','failed','deleted') NOT NULL default 'pending',
1762 `time_queued` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
1763 KEY `message_id` (`message_id`),
1764 KEY `borrowernumber` (`borrowernumber`),
1765 KEY `message_transport_type` (`message_transport_type`),
1766 CONSTRAINT `messageq_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1767 CONSTRAINT `messageq_ibfk_2` FOREIGN KEY (`message_transport_type`) REFERENCES `message_transport_types` (`message_transport_type`) ON DELETE RESTRICT ON UPDATE CASCADE
1768 ) ENGINE=InnoDB DEFAULT CHARSET=utf8
1769 END_SQL
1770
1771 $dbh->do(<<'END_SQL');
1772 INSERT INTO `systempreferences`
1773 (variable,value,explanation,options,type)
1774 VALUES
1775 ('EnhancedMessagingPreferences',0,'If ON, allows patrons to select to receive additional messages about items due or nearly due.','','YesNo')
1776 END_SQL
1777
1778 $dbh->do( <<'END_SQL');
1779 INSERT INTO `letter`
1780 (module, code, name, title, content)
1781 VALUES
1782 ('circulation','DUE','Item Due Reminder','Item Due Reminder','Dear <<borrowers.firstname>> <<borrowers.surname>>,\r\n\r\nThe following item is now due:\r\n\r\n<<biblio.title>> by <<biblio.author>>'),
1783 ('circulation','DUEDGST','Item Due Reminder (Digest)','Item Due Reminder','You have <<count>> items due'),
1784 ('circulation','PREDUE','Advance Notice of Item Due','Advance Notice of Item Due','Dear <<borrowers.firstname>> <<borrowers.surname>>,\r\n\r\nThe following item will be due soon:\r\n\r\n<<biblio.title>> by <<biblio.author>>'),
1785 ('circulation','PREDUEDGST','Advance Notice of Item Due (Digest)','Advance Notice of Item Due','You have <<count>> items due soon'),
1786 ('circulation','EVENT','Upcoming Library Event','Upcoming Library Event','Dear <<borrowers.firstname>> <<borrowers.surname>>,\r\n\r\nThis is a reminder of an upcoming library event in which you have expressed interest.');
1787 END_SQL
1788
1789 my @sql_scripts = (
1790 'installer/data/mysql/en/mandatory/message_transport_types.sql',
1791 'installer/data/mysql/en/optional/sample_notices_message_attributes.sql',
1792 'installer/data/mysql/en/optional/sample_notices_message_transports.sql',
1793 );
1794
1795 my $installer = C4::Installer->new();
1796 foreach my $script ( @sql_scripts ) {
1797 my $full_path = $installer->get_file_path_from_name($script);
1798 my $error = $installer->load_sql($full_path);
1799 warn $error if $error;
1800 }
1801
1802 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";
1803 SetVersion ($DBversion);
1804 }
1805
1806 $DBversion = "3.00.00.092";
1807 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1808 $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')");
1809 $dbh->do("INSERT INTO `systempreferences` (variable,value,options,explanation,type) VALUES('AllowHoldsOnDamagedItems', '1', '', 'Allow hold requests to be placed on damaged items', 'YesNo')");
1810 print "Upgrade to $DBversion done (added new AllowOnShelfHolds syspref)\n";
1811 SetVersion ($DBversion);
1812 }
1813
1814 $DBversion = "3.00.00.093";
1815 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1816 $dbh->do("ALTER TABLE `items` MODIFY COLUMN `copynumber` VARCHAR(32) DEFAULT NULL");
1817 $dbh->do("ALTER TABLE `deleteditems` MODIFY COLUMN `copynumber` VARCHAR(32) DEFAULT NULL");
1818 print "Upgrade to $DBversion done (Change data type of items.copynumber to allow free text)\n";
1819 SetVersion ($DBversion);
1820 }
1821
1822 $DBversion = "3.00.00.094";
1823 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1824 $dbh->do("ALTER TABLE `marc_subfield_structure` MODIFY `tagsubfield` VARCHAR(1) NOT NULL DEFAULT '' COLLATE utf8_bin");
1825 print "Upgrade to $DBversion done (Change Collation of marc_subfield_structure to allow mixed case in subfield labels.)\n";
1826 SetVersion ($DBversion);
1827 }
1828
1829 $DBversion = "3.00.00.095";
1830 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1831 if (C4::Context->preference("marcflavour") eq 'MARC21') {
1832 $dbh->do("UPDATE marc_subfield_structure SET authtypecode = 'MEETI_NAME' WHERE authtypecode = 'Meeting Name'");
1833 $dbh->do("UPDATE marc_subfield_structure SET authtypecode = 'CORPO_NAME' WHERE authtypecode = 'CORP0_NAME'");
1834 }
1835 print "Upgrade to $DBversion done (fix invalid authority types in MARC21 frameworks [bug 2254])\n";
1836 SetVersion ($DBversion);
1837 }
1838
1839 $DBversion = "3.00.00.096";
1840 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1841 $sth = $dbh->prepare("SHOW COLUMNS FROM borrower_message_preferences LIKE 'wants_digets'");
1842 $sth->execute();
1843 if (my $row = $sth->fetchrow_hashref) {
1844 $dbh->do("ALTER TABLE borrower_message_preferences CHANGE wants_digets wants_digest tinyint(1) NOT NULL default 0");
1845 }
1846 print "Upgrade to $DBversion done (fix name borrower_message_preferences.wants_digest)\n";
1847 SetVersion ($DBversion);
1848 }
1849
1850 $DBversion = '3.00.00.097';
1851 if ( C4::Context->preference('Version') < TransformToNum($DBversion) ) {
1852
1853 $dbh->do('ALTER TABLE message_queue ADD to_address mediumtext default NULL');
1854 $dbh->do('ALTER TABLE message_queue ADD from_address mediumtext default NULL');
1855 $dbh->do('ALTER TABLE message_queue ADD content_type text');
1856 $dbh->do('ALTER TABLE message_queue CHANGE borrowernumber borrowernumber int(11) default NULL');
1857
1858 print "Upgrade to $DBversion done (updating 4 fields in message_queue table)\n";
1859 SetVersion($DBversion);
1860 }
1861
1862 $DBversion = '3.00.00.098';
1863 if ( C4::Context->preference('Version') < TransformToNum($DBversion) ) {
1864
1865 $dbh->do(q(DELETE FROM message_transport_types WHERE message_transport_type = 'rss'));
1866 $dbh->do(q(DELETE FROM message_transports WHERE message_transport_type = 'rss'));
1867
1868 print "Upgrade to $DBversion done (removing unused RSS message_transport_type)\n";
1869 SetVersion($DBversion);
1870 }
1871
1872 $DBversion = '3.00.00.099';
1873 if ( C4::Context->preference('Version') < TransformToNum($DBversion) ) {
1874 $dbh->do("INSERT INTO systempreferences (variable,value,options,explanation,type) VALUES('OpacSuppression', '0', '', 'Turn ON the OPAC Suppression feature, requires further setup, ask your system administrator for details', 'YesNo')");
1875 print "Upgrade to $DBversion done (Adding OpacSuppression syspref)\n";
1876 SetVersion($DBversion);
1877 }
1878
1879 $DBversion = '3.00.00.100';
1880 if ( C4::Context->preference('Version') < TransformToNum($DBversion) ) {
1881 $dbh->do('ALTER TABLE virtualshelves ADD COLUMN lastmodified timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP');
1882 print "Upgrade to $DBversion done (Adding lastmodified column to virtualshelves)\n";
1883 SetVersion($DBversion);
1884 }
1885
1886 $DBversion = '3.00.00.101';
1887 if ( C4::Context->preference('Version') < TransformToNum($DBversion) ) {
1888 $dbh->do('ALTER TABLE `overduerules` CHANGE `categorycode` `categorycode` VARCHAR(10) NOT NULL');
1889 $dbh->do('ALTER TABLE `deletedborrowers` CHANGE `categorycode` `categorycode` VARCHAR(10) NOT NULL');
1890 print "Upgrade to $DBversion done (Updating columnd definitions for patron category codes in notice/statsu triggers and deletedborrowers tables.)\n";
1891 SetVersion($DBversion);
1892 }
1893
1894 $DBversion = '3.00.00.102';
1895 if ( C4::Context->preference('Version') < TransformToNum($DBversion) ) {
1896 $dbh->do('ALTER TABLE serialitems MODIFY `serialid` int(11) NOT NULL AFTER itemnumber' );
1897 $dbh->do('ALTER TABLE serialitems DROP KEY serialididx' );
1898 $dbh->do('ALTER TABLE serialitems ADD CONSTRAINT UNIQUE KEY serialitemsidx (itemnumber)' );
1899 # before setting constraint, delete any unvalid data
1900 $dbh->do('DELETE from serialitems WHERE serialid not in (SELECT serial.serialid FROM serial)');
1901 $dbh->do('ALTER TABLE serialitems ADD CONSTRAINT serialitems_sfk_1 FOREIGN KEY (serialid) REFERENCES serial (serialid) ON DELETE CASCADE ON UPDATE CASCADE' );
1902 print "Upgrade to $DBversion done (Updating serialitems table to allow for multiple items per serial fixing kohabug 2380)\n";
1903 SetVersion($DBversion);
1904 }
1905
1906 $DBversion = "3.00.00.103";
1907 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1908 $dbh->do("DELETE FROM systempreferences WHERE variable='serialsadditems'");
1909 print "Upgrade to $DBversion done ( Verifying the removal of serialsadditems from syspref fixing kohabug 2219)\n";
1910 SetVersion ($DBversion);
1911 }
1912
1913 $DBversion = "3.00.00.104";
1914 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1915 $dbh->do("DELETE FROM systempreferences WHERE variable='noOPACHolds'");
1916 print "Upgrade to $DBversion done (remove superseded 'noOPACHolds' system preference per bug 2413)\n";
1917 SetVersion ($DBversion);
1918 }
1919
1920 $DBversion = '3.00.00.105';
1921 if ( C4::Context->preference("Version") < TransformToNum($DBversion) ) {
1922
1923 # it is possible that this syspref is already defined since the feature was added some time ago.
1924 unless ( $dbh->do(q(SELECT variable FROM systempreferences WHERE variable = 'SMSSendDriver')) ) {
1925 $dbh->do(<<'END_SQL');
1926 INSERT INTO `systempreferences`
1927 (variable,value,explanation,options,type)
1928 VALUES
1929 ('SMSSendDriver','','Sets which SMS::Send driver is used to send SMS messages.','','free')
1930 END_SQL
1931 }
1932 print "Upgrade to $DBversion done (added SMSSendDriver system preference)\n";
1933 SetVersion($DBversion);
1934 }
1935
1936 $DBversion = "3.00.00.106";
1937 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1938 $dbh->do("DELETE FROM systempreferences WHERE variable='noOPACHolds'");
1939
1940 # db revision 105 didn't apply correctly, so we're rolling this into 106
1941 $dbh->do("INSERT INTO `systempreferences`
1942 (variable,value,explanation,options,type)
1943 VALUES
1944 ('SMSSendDriver','','Sets which SMS::Send driver is used to send SMS messages.','','free')");
1945
1946 print "Upgrade to $DBversion done (remove default '0000-00-00' in subscriptionhistory.enddate field)\n";
1947 $dbh->do("ALTER TABLE `subscriptionhistory` CHANGE `enddate` `enddate` DATE NULL DEFAULT NULL ");
1948 $dbh->do("UPDATE subscriptionhistory SET enddate=NULL WHERE enddate='0000-00-00'");
1949 SetVersion ($DBversion);
1950 }
1951
1952 $DBversion = '3.00.00.107';
1953 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1954 $dbh->do(<<'END_SQL');
1955 UPDATE systempreferences
1956 SET explanation = CONCAT( explanation, '. WARNING: this feature is very resource consuming on collections with large numbers of items.' )
1957 WHERE variable = 'OPACShelfBrowser'
1958 AND explanation NOT LIKE '%WARNING%'
1959 END_SQL
1960 $dbh->do(<<'END_SQL');
1961 UPDATE systempreferences
1962 SET explanation = CONCAT( explanation, '. WARNING: this feature is very resource consuming.' )
1963 WHERE variable = 'CataloguingLog'
1964 AND explanation NOT LIKE '%WARNING%'
1965 END_SQL
1966 $dbh->do(<<'END_SQL');
1967 UPDATE systempreferences
1968 SET explanation = CONCAT( explanation, '. WARNING: using NoZebra on even modest sized collections is very slow.' )
1969 WHERE variable = 'NoZebra'
1970 AND explanation NOT LIKE '%WARNING%'
1971 END_SQL
1972 print "Upgrade to $DBversion done (warning added to OPACShelfBrowser system preference)\n";
1973 SetVersion ($DBversion);
1974 }
1975
1976 $DBversion = '3.01.00.000';
1977 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
1978 print "Upgrade to $DBversion done (start of 3.1)\n";
1979 SetVersion ($DBversion);
1980 }
1981
1982 $DBversion = '3.01.00.001';
1983 if ( C4::Context->preference('Version') < TransformToNum($DBversion) ) {
1984 $dbh->do("
1985 CREATE TABLE hold_fill_targets (
1986 `borrowernumber` int(11) NOT NULL,
1987 `biblionumber` int(11) NOT NULL,
1988 `itemnumber` int(11) NOT NULL,
1989 `source_branchcode` varchar(10) default NULL,
1990 `item_level_request` tinyint(4) NOT NULL default 0,
1991 PRIMARY KEY `itemnumber` (`itemnumber`),
1992 KEY `bib_branch` (`biblionumber`, `source_branchcode`),
1993 CONSTRAINT `hold_fill_targets_ibfk_1` FOREIGN KEY (`borrowernumber`)
1994 REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1995 CONSTRAINT `hold_fill_targets_ibfk_2` FOREIGN KEY (`biblionumber`)
1996 REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1997 CONSTRAINT `hold_fill_targets_ibfk_3` FOREIGN KEY (`itemnumber`)
1998 REFERENCES `items` (`itemnumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1999 CONSTRAINT `hold_fill_targets_ibfk_4` FOREIGN KEY (`source_branchcode`)
2000 REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE
2001 ) ENGINE=InnoDB DEFAULT CHARSET=utf8
2002 ");
2003 $dbh->do("
2004 ALTER TABLE tmp_holdsqueue
2005 ADD item_level_request tinyint(4) NOT NULL default 0
2006 ");
2007
2008 print "Upgrade to $DBversion done (add hold_fill_targets table and a column to tmp_holdsqueue)\n";
2009 SetVersion($DBversion);
2010 }
2011
2012 $DBversion = '3.01.00.002';
2013 if ( C4::Context->preference('Version') < TransformToNum($DBversion) ) {
2014 # use statistics where available
2015 $dbh->do("
2016 ALTER TABLE statistics ADD KEY tmp_stats (type, itemnumber, borrowernumber)
2017 ");
2018 $dbh->do("
2019 UPDATE issues iss
2020 SET issuedate = (
2021 SELECT max(datetime)
2022 FROM statistics
2023 WHERE type = 'issue'
2024 AND itemnumber = iss.itemnumber
2025 AND borrowernumber = iss.borrowernumber
2026 )
2027 WHERE issuedate IS NULL;
2028 ");
2029 $dbh->do("ALTER TABLE statistics DROP KEY tmp_stats");
2030
2031 # default to last renewal date
2032 $dbh->do("
2033 UPDATE issues
2034 SET issuedate = lastreneweddate
2035 WHERE issuedate IS NULL
2036 and lastreneweddate IS NOT NULL
2037 ");
2038
2039 my $num_bad_issuedates = $dbh->selectrow_array("SELECT COUNT(*) FROM issues WHERE issuedate IS NULL");
2040 if ($num_bad_issuedates > 0) {
2041 print STDERR "After the upgrade to $DBversion, there are still $num_bad_issuedates loan(s) with a NULL (blank) loan date. ",
2042 "Please check the issues table in your database.";
2043 }
2044 print "Upgrade to $DBversion done (bug 2582: set null issues.issuedate to lastreneweddate)\n";
2045 SetVersion($DBversion);
2046 }
2047
2048 $DBversion = "3.01.00.003";
2049 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2050 $dbh->do("INSERT INTO systempreferences (variable,value,explanation,options,type) VALUES('AllowRenewalLimitOverride', '0', 'if ON, allows renewal limits to be overridden on the circulation screen',NULL,'YesNo')");
2051 print "Upgrade to $DBversion done (add new syspref)\n";
2052 SetVersion ($DBversion);
2053 }
2054
2055 $DBversion = '3.01.00.004';
2056 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2057 $dbh->do("INSERT INTO systempreferences (variable,value,explanation,options,type) VALUES ('OPACDisplayRequestPriority','0','Show patrons the priority level on holds in the OPAC','','YesNo')");
2058 print "Upgrade to $DBversion done (added OPACDisplayRequestPriority system preference)\n";
2059 SetVersion ($DBversion);
2060 }
2061
2062 $DBversion = '3.01.00.005';
2063 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2064 $dbh->do("
2065 INSERT INTO `letter` (module, code, name, title, content)
2066 VALUES('reserves', 'HOLD', 'Hold Available for Pickup', 'Hold Available for Pickup at <<branches.branchname>>', 'Dear <<borrowers.firstname>> <<borrowers.surname>>,\r\n\r\nYou have a hold available for pickup as of <<reserves.waitingdate>>:\r\n\r\nTitle: <<biblio.title>>\r\nAuthor: <<biblio.author>>\r\nCopy: <<items.copynumber>>\r\nLocation: <<branches.branchname>>\r\n<<branches.branchaddress1>>\r\n<<branches.branchaddress2>>\r\n<<branches.branchaddress3>>')
2067 ");
2068 $dbh->do("INSERT INTO `message_attributes` (message_attribute_id, message_name, takes_days) values(4, 'Hold Filled', 0)");
2069 $dbh->do("INSERT INTO `message_transports` (message_attribute_id, message_transport_type, is_digest, letter_module, letter_code) values(4, 'sms', 0, 'reserves', 'HOLD')");
2070 $dbh->do("INSERT INTO `message_transports` (message_attribute_id, message_transport_type, is_digest, letter_module, letter_code) values(4, 'email', 0, 'reserves', 'HOLD')");
2071 print "Upgrade to $DBversion done (Add letter for holds notifications)\n";
2072 SetVersion ($DBversion);
2073 }
2074
2075 $DBversion = '3.01.00.006';
2076 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2077 $dbh->do("ALTER TABLE `biblioitems` ADD KEY issn (issn)");
2078 print "Upgrade to $DBversion done (add index on biblioitems.issn)\n";
2079 SetVersion ($DBversion);
2080 }
2081
2082 $DBversion = "3.01.00.007";
2083 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2084 $dbh->do("UPDATE `systempreferences` SET options='70|10' WHERE variable='intranetmainUserblock'");
2085 $dbh->do("UPDATE `systempreferences` SET options='70|10' WHERE variable='intranetuserjs'");
2086 $dbh->do("UPDATE `systempreferences` SET options='70|10' WHERE variable='opacheader'");
2087 $dbh->do("UPDATE `systempreferences` SET options='70|10' WHERE variable='OpacMainUserBlock'");
2088 $dbh->do("UPDATE `systempreferences` SET options='70|10' WHERE variable='OpacNav'");
2089 $dbh->do("UPDATE `systempreferences` SET options='70|10' WHERE variable='opacuserjs'");
2090 $dbh->do("UPDATE `systempreferences` SET options='30|10', type='Textarea' WHERE variable='OAI-PMH:Set'");
2091 $dbh->do("UPDATE `systempreferences` SET options='50' WHERE variable='intranetstylesheet'");
2092 $dbh->do("UPDATE `systempreferences` SET options='50' WHERE variable='intranetcolorstylesheet'");
2093 $dbh->do("UPDATE `systempreferences` SET options='10' WHERE variable='globalDueDate'");
2094 $dbh->do("UPDATE `systempreferences` SET type='Integer' WHERE variable='numSearchResults'");
2095 $dbh->do("UPDATE `systempreferences` SET type='Integer' WHERE variable='OPACnumSearchResults'");
2096 $dbh->do("UPDATE `systempreferences` SET type='Integer' WHERE variable='ReservesMaxPickupDelay'");
2097 $dbh->do("UPDATE `systempreferences` SET type='Integer' WHERE variable='TransfersMaxDaysWarning'");
2098 $dbh->do("UPDATE `systempreferences` SET type='Integer' WHERE variable='StaticHoldsQueueWeight'");
2099 $dbh->do("UPDATE `systempreferences` SET type='Integer' WHERE variable='holdCancelLength'");
2100 $dbh->do("UPDATE `systempreferences` SET type='Integer' WHERE variable='XISBNDailyLimit'");
2101 $dbh->do("UPDATE `systempreferences` SET type='Float' WHERE variable='gist'");
2102 $dbh->do("UPDATE `systempreferences` SET type='Free' WHERE variable='BakerTaylorUsername'");
2103 $dbh->do("UPDATE `systempreferences` SET type='Free' WHERE variable='BakerTaylorPassword'");
2104 $dbh->do("UPDATE `systempreferences` SET type='Textarea', options='70|10' WHERE variable='ISBD'");
2105 $dbh->do("UPDATE `systempreferences` SET type='Textarea', options='70|10', explanation='Enter a specific hash for NoZebra indexes. Enter : \\\'indexname\\\' => \\\'100a,245a,500*\\\',\\\'index2\\\' => \\\'...\\\'' WHERE variable='NoZebraIndexes'");
2106 print "Upgrade to $DBversion done (fix display of many sysprefs)\n";
2107 SetVersion ($DBversion);
2108 }
2109
2110 $DBversion = '3.01.00.008';
2111 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2112
2113 $dbh->do("CREATE TABLE branch_transfer_limits (
2114 limitId int(8) NOT NULL auto_increment,
2115 toBranch varchar(4) NOT NULL,
2116 fromBranch varchar(4) NOT NULL,
2117 itemtype varchar(4) NOT NULL,
2118 PRIMARY KEY (limitId)
2119 ) ENGINE=InnoDB DEFAULT CHARSET=utf8"
2120 );
2121
2122 $dbh->do("INSERT INTO `systempreferences` ( `variable` , `value` , `options` , `explanation` , `type` ) VALUES ( 'UseBranchTransferLimits', '0', '', 'If ON, Koha will will use the rules defined in branch_transfer_limits to decide if an item transfer should be allowed.', 'YesNo')");
2123
2124 print "Upgrade to $DBversion done (added branch_transfer_limits table and UseBranchTransferLimits system preference)\n";
2125 SetVersion ($DBversion);
2126 }
2127
2128 $DBversion = "3.01.00.009";
2129 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2130 $dbh->do("ALTER TABLE permissions MODIFY `code` varchar(64) DEFAULT NULL");
2131 $dbh->do("ALTER TABLE user_permissions MODIFY `code` varchar(64) DEFAULT NULL");
2132 $dbh->do("INSERT INTO permissions (module_bit, code, description) VALUES ( 1, 'circulate_remaining_permissions', 'Remaining circulation permissions')");
2133 $dbh->do("INSERT INTO permissions (module_bit, code, description) VALUES ( 1, 'override_renewals', 'Override blocked renewals')");
2134 print "Upgrade to $DBversion done (added subpermissions for circulate permission)\n";
2135 }
2136
2137 $DBversion = '3.01.00.010';
2138 if ( C4::Context->preference('Version') < TransformToNum($DBversion) ) {
2139 $dbh->do("ALTER TABLE `borrower_attributes` MODIFY COLUMN `attribute` VARCHAR(64) DEFAULT NULL");
2140 $dbh->do("ALTER TABLE `borrower_attributes` MODIFY COLUMN `password` VARCHAR(64) DEFAULT NULL");
2141 print "Upgrade to $DBversion done (bug 2687: increase length of borrower attribute fields)\n";
2142 SetVersion ($DBversion);
2143 }
2144
2145 $DBversion = '3.01.00.011';
2146 if ( C4::Context->preference('Version') < TransformToNum($DBversion) ) {
2147
2148 # Yes, the old value was ^M terminated.
2149 my $bad_value = "function prepareEmailPopup(){\r\n if (!document.getElementById) return false;\r\n if (!document.getElementById('reserveemail')) return false;\r\n rsvlink = document.getElementById('reserveemail');\r\n rsvlink.onclick = function() {\r\n doReservePopup();\r\n return false;\r\n }\r\n}\r\n\r\nfunction doReservePopup(){\r\n}\r\n\r\nfunction prepareReserveList(){\r\n}\r\n\r\naddLoadEvent(prepareEmailPopup);\r\naddLoadEvent(prepareReserveList);";
2150
2151 my $intranetuserjs = C4::Context->preference('intranetuserjs');
2152 if ($intranetuserjs and $intranetuserjs eq $bad_value) {
2153 my $sql = <<'END_SQL';
2154 UPDATE systempreferences
2155 SET value = ''
2156 WHERE variable = 'intranetuserjs'
2157 END_SQL
2158 $dbh->do($sql);
2159 }
2160 print "Upgrade to $DBversion done (removed bogus intranetuserjs syspref)\n";
2161 SetVersion($DBversion);
2162 }
2163
2164 $DBversion = "3.01.00.012";
2165 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2166 $dbh->do("INSERT INTO systempreferences (variable,value,explanation,options,type) VALUES('AllowHoldPolicyOverride', '0', 'Allow staff to override hold policies when placing holds',NULL,'YesNo')");
2167 $dbh->do("
2168 CREATE TABLE `branch_item_rules` (
2169 `branchcode` varchar(10) NOT NULL,
2170 `itemtype` varchar(10) NOT NULL,
2171 `holdallowed` tinyint(1) default NULL,
2172 PRIMARY KEY (`itemtype`,`branchcode`),
2173 KEY `branch_item_rules_ibfk_2` (`branchcode`),
2174 CONSTRAINT `branch_item_rules_ibfk_1` FOREIGN KEY (`itemtype`) REFERENCES `itemtypes` (`itemtype`) ON DELETE CASCADE ON UPDATE CASCADE,
2175 CONSTRAINT `branch_item_rules_ibfk_2` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE
2176 ) ENGINE=InnoDB DEFAULT CHARSET=utf8
2177 ");
2178 $dbh->do("
2179 CREATE TABLE `default_branch_item_rules` (
2180 `itemtype` varchar(10) NOT NULL,
2181 `holdallowed` tinyint(1) default NULL,
2182 PRIMARY KEY (`itemtype`),
2183 CONSTRAINT `default_branch_item_rules_ibfk_1` FOREIGN KEY (`itemtype`) REFERENCES `itemtypes` (`itemtype`) ON DELETE CASCADE ON UPDATE CASCADE
2184 ) ENGINE=InnoDB DEFAULT CHARSET=utf8
2185 ");
2186 $dbh->do("
2187 ALTER TABLE default_branch_circ_rules
2188 ADD COLUMN holdallowed tinyint(1) NULL
2189 ");
2190 $dbh->do("
2191 ALTER TABLE default_circ_rules
2192 ADD COLUMN holdallowed tinyint(1) NULL
2193 ");
2194 print "Upgrade to $DBversion done (Add tables and system preferences for holds policies)\n";
2195 SetVersion ($DBversion);
2196 }
2197
2198 $DBversion = '3.01.00.013';
2199 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2200 $dbh->do("
2201 CREATE TABLE item_circulation_alert_preferences (
2202 id int(11) AUTO_INCREMENT,
2203 branchcode varchar(10) NOT NULL,
2204 categorycode varchar(10) NOT NULL,
2205 item_type varchar(10) NOT NULL,
2206 notification varchar(16) NOT NULL,
2207 PRIMARY KEY (id),
2208 KEY (branchcode, categorycode, item_type, notification)
2209 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2210 ");
2211
2212 $dbh->do(q{ ALTER TABLE `message_queue` ADD metadata text DEFAULT NULL AFTER content; });
2213 $dbh->do(q{ ALTER TABLE `message_queue` ADD letter_code varchar(64) DEFAULT NULL AFTER metadata; });
2214
2215 $dbh->do(q{
2216 INSERT INTO `letter` (`module`, `code`, `name`, `title`, `content`) VALUES
2217 ('circulation','CHECKIN','Item Check-in','Check-ins','The following items have been checked in:\r\n----\r\n<<biblio.title>>\r\n----\r\nThank you.');
2218 });
2219 $dbh->do(q{
2220 INSERT INTO `letter` (`module`, `code`, `name`, `title`, `content`) VALUES
2221 ('circulation','CHECKOUT','Item Checkout','Checkouts','The following items have been checked out:\r\n----\r\n<<biblio.title>>\r\n----\r\nThank you for visiting <<branches.branchname>>.');
2222 });
2223
2224 $dbh->do(q{INSERT INTO message_attributes (message_attribute_id, message_name, takes_days) VALUES (5, 'Item Check-in', 0);});
2225 $dbh->do(q{INSERT INTO message_attributes (message_attribute_id, message_name, takes_days) VALUES (6, 'Item Checkout', 0);});
2226
2227 $dbh->do(q{INSERT INTO message_transports (message_attribute_id, message_transport_type, is_digest, letter_module, letter_code) VALUES (5, 'email', 0, 'circulation', 'CHECKIN');});
2228 $dbh->do(q{INSERT INTO message_transports (message_attribute_id, message_transport_type, is_digest, letter_module, letter_code) VALUES (5, 'sms', 0, 'circulation', 'CHECKIN');});
2229 $dbh->do(q{INSERT INTO message_transports (message_attribute_id, message_transport_type, is_digest, letter_module, letter_code) VALUES (6, 'email', 0, 'circulation', 'CHECKOUT');});
2230 $dbh->do(q{INSERT INTO message_transports (message_attribute_id, message_transport_type, is_digest, letter_module, letter_code) VALUES (6, 'sms', 0, 'circulation', 'CHECKOUT');});
2231
2232 print "Upgrade to $DBversion done (data for Email Checkout Slips project)\n";
2233 SetVersion ($DBversion);
2234 }
2235
2236 $DBversion = "3.01.00.014";
2237 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2238 $dbh->do("ALTER TABLE `branch_transfer_limits` CHANGE `itemtype` `itemtype` VARCHAR( 4 ) CHARACTER SET utf8 COLLATE utf8_general_ci NULL");
2239 $dbh->do("ALTER TABLE `branch_transfer_limits` ADD `ccode` VARCHAR( 10 ) NULL ;");
2240 $dbh->do("INSERT INTO `systempreferences` ( `variable` , `value` , `options` , `explanation` , `type` )
2241 VALUES (
2242 'BranchTransferLimitsType', 'ccode', 'itemtype|ccode', 'When using branch transfer limits, choose whether to limit by itemtype or collection code.', 'Choice'
2243 );");
2244
2245 print "Upgrade to $DBversion done ( Updated table for Branch Transfer Limits)\n";
2246 SetVersion ($DBversion);
2247 }
2248
2249 $DBversion = '3.01.00.015';
2250 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2251 $dbh->do("INSERT INTO systempreferences (variable,value,explanation,options,type) VALUES ('SyndeticsClientCode', '0', 'Client Code for using Syndetics Solutions content','','free')");
2252
2253 $dbh->do("INSERT INTO systempreferences (variable,value,explanation,options,type) VALUES ('SyndeticsEnabled', '0', 'Turn on Syndetics Enhanced Content','','YesNo')");
2254
2255 $dbh->do("INSERT INTO systempreferences (variable,value,explanation,options,type) VALUES ('SyndeticsCoverImages', '0', 'Display Cover Images from Syndetics','','YesNo')");
2256
2257 $dbh->do("INSERT INTO systempreferences (variable,value,explanation,options,type) VALUES ('SyndeticsTOC', '0', 'Display Table of Content information from Syndetics','','YesNo')");
2258
2259 $dbh->do("INSERT INTO systempreferences (variable,value,explanation,options,type) VALUES ('SyndeticsSummary', '0', 'Display Summary Information from Syndetics','','YesNo')");
2260
2261 $dbh->do("INSERT INTO systempreferences (variable,value,explanation,options,type) VALUES ('SyndeticsEditions', '0', 'Display Editions from Syndetics','','YesNo')");
2262
2263 $dbh->do("INSERT INTO systempreferences (variable,value,explanation,options,type) VALUES ('SyndeticsExcerpt', '0', 'Display Excerpts and first chapters on OPAC from Syndetics','','YesNo')");
2264
2265 $dbh->do("INSERT INTO systempreferences (variable,value,explanation,options,type) VALUES ('SyndeticsReviews', '0', 'Display Reviews on OPAC from Syndetics','','YesNo')");
2266
2267 $dbh->do("INSERT INTO systempreferences (variable,value,explanation,options,type) VALUES ('SyndeticsAuthorNotes', '0', 'Display Notes about the Author on OPAC from Syndetics','','YesNo')");
2268
2269 $dbh->do("INSERT INTO systempreferences (variable,value,explanation,options,type) VALUES ('SyndeticsAwards', '0', 'Display Awards on OPAC from Syndetics','','YesNo')");
2270
2271 $dbh->do("INSERT INTO systempreferences (variable,value,explanation,options,type) VALUES ('SyndeticsSeries', '0', 'Display Series information on OPAC from Syndetics','','YesNo')");
2272
2273 $dbh->do("INSERT INTO systempreferences (variable,value,explanation,options,type) VALUES ('SyndeticsCoverImageSize', 'MC', 'Choose the size of the Syndetics Cover Image to display on the OPAC detail page, MC is Medium, LC is Large','MC|LC','Choice')");
2274
2275 $dbh->do("INSERT INTO systempreferences (variable,value,explanation,options,type) VALUES ('OPACAmazonCoverImages', '0', 'Display cover images on OPAC from Amazon Web Services','','YesNo')");
2276
2277 $dbh->do("INSERT INTO systempreferences (variable,value,explanation,options,type) VALUES ('AmazonCoverImages', '0', 'Display Cover Images in Staff Client from Amazon Web Services','','YesNo')");
2278
2279 $dbh->do("UPDATE systempreferences SET variable='AmazonEnabled' WHERE variable = 'AmazonContent'");
2280
2281 $dbh->do("UPDATE systempreferences SET variable='OPACAmazonEnabled' WHERE variable = 'OPACAmazonContent'");
2282
2283 print "Upgrade to $DBversion done (added Syndetics Enhanced Content system preferences)\n";
2284 SetVersion ($DBversion);
2285 }
2286
2287 $DBversion = "3.01.00.016";
2288 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2289 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('Babeltheque',0,'Turn ON Babeltheque content - See babeltheque.com to subscribe to this service','','YesNo')");
2290 print "Upgrade to $DBversion done (Added Babeltheque syspref)\n";
2291 SetVersion ($DBversion);
2292 }
2293
2294 $DBversion = "3.01.00.017";
2295 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2296 $dbh->do("ALTER TABLE `subscription` ADD `staffdisplaycount` VARCHAR(10) NULL;");
2297 $dbh->do("ALTER TABLE `subscription` ADD `opacdisplaycount` VARCHAR(10) NULL;");
2298 $dbh->do("INSERT INTO `systempreferences` ( `variable` , `value` , `options` , `explanation` , `type` )
2299 VALUES (
2300 'StaffSerialIssueDisplayCount', '3', '', 'Number of serial issues to display per subscription in the Staff client', 'Integer'
2301 );");
2302 $dbh->do("INSERT INTO `systempreferences` ( `variable` , `value` , `options` , `explanation` , `type` )
2303 VALUES (
2304 'OPACSerialIssueDisplayCount', '3', '', 'Number of serial issues to display per subscription in the OPAC', 'Integer'
2305 );");
2306
2307 print "Upgrade to $DBversion done ( Updated table for Serials Display)\n";
2308 SetVersion ($DBversion);
2309 }
2310
2311 $DBversion = "3.01.00.018";
2312 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2313 $dbh->do("ALTER TABLE deletedborrowers ADD `smsalertnumber` varchar(50) default NULL");
2314 print "Upgrade to $DBversion done (added deletedborrowers.smsalertnumber, missed in 3.00.00.091)\n";
2315 SetVersion ($DBversion);
2316 }
2317
2318 $DBversion = "3.01.00.019";
2319 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2320 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('OPACShowCheckoutName','0','Displays in the OPAC the name of patron who has checked out the material. WARNING: Most sites should leave this off. It is intended for corporate or special sites which need to track who has the item.','','YesNo')");
2321 print "Upgrade to $DBversion done (adding OPACShowCheckoutName systempref)\n";
2322 SetVersion ($DBversion);
2323 }
2324
2325 $DBversion = "3.01.00.020";
2326 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2327 $dbh->do("INSERT INTO systempreferences (variable,value,explanation,options,type)VALUES('LibraryThingForLibrariesID','','See:http://librarything.com/forlibraries/','','free')");
2328 $dbh->do("INSERT INTO systempreferences (variable,value,explanation,options,type)VALUES('LibraryThingForLibrariesEnabled','0','Enable or Disable Library Thing for Libraries Features','','YesNo')");
2329 $dbh->do("INSERT INTO systempreferences (variable,value,explanation,options,type)VALUES('LibraryThingForLibrariesTabbedView','0','Put LibraryThingForLibraries Content in Tabs.','','YesNo')");
2330 print "Upgrade to $DBversion done (adding LibraryThing for Libraries sysprefs)\n";
2331 SetVersion ($DBversion);
2332 }
2333
2334 $DBversion = "3.01.00.021";
2335 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2336 my $enable_reviews = C4::Context->preference('OPACAmazonEnabled') ? '1' : '0';
2337 $dbh->do("INSERT INTO systempreferences (variable,value,explanation,options,type) VALUES ('OPACAmazonReviews', '$enable_reviews', 'Display Amazon readers reviews on OPAC','','YesNo')");
2338 print "Upgrade to $DBversion done (adding OPACAmazonReviews syspref)\n";
2339 SetVersion ($DBversion);
2340 }
2341
2342 $DBversion = '3.01.00.022';
2343 if ( C4::Context->preference('Version') < TransformToNum($DBversion) ) {
2344 $dbh->do("ALTER TABLE `labels_conf` MODIFY COLUMN `formatstring` mediumtext DEFAULT NULL");
2345 print "Upgrade to $DBversion done (bug 2945: increase size of labels_conf.formatstring)\n";
2346 SetVersion ($DBversion);
2347 }
2348
2349 $DBversion = '3.01.00.023';
2350 if ( C4::Context->preference('Version') < TransformToNum($DBversion) ) {
2351 $dbh->do("ALTER TABLE biblioitems MODIFY COLUMN isbn VARCHAR(30) DEFAULT NULL");
2352 $dbh->do("ALTER TABLE deletedbiblioitems MODIFY COLUMN isbn VARCHAR(30) DEFAULT NULL");
2353 $dbh->do("ALTER TABLE import_biblios MODIFY COLUMN isbn VARCHAR(30) DEFAULT NULL");
2354 $dbh->do("ALTER TABLE suggestions MODIFY COLUMN isbn VARCHAR(30) DEFAULT NULL");
2355 print "Upgrade to $DBversion done (bug 2765: increase width of isbn column in several tables)\n";
2356 SetVersion ($DBversion);
2357 }
2358
2359 $DBversion = "3.01.00.024";
2360 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2361 $dbh->do("ALTER TABLE labels MODIFY COLUMN batch_id int(10) NOT NULL default 1;");
2362 print "Upgrade to $DBversion done (change labels.batch_id from varchar to int)\n";
2363 SetVersion ($DBversion);
2364 }
2365
2366 $DBversion = '3.01.00.025';
2367 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2368 $dbh->do("INSERT INTO `systempreferences` ( `variable` , `value` , `options` , `explanation` , `type` ) VALUES ( 'ceilingDueDate', '', '', 'If set, date due will not be past this date. Enter date according to the dateformat System Preference', 'free')");
2369
2370 print "Upgrade to $DBversion done (added ceilingDueDate system preference)\n";
2371 SetVersion ($DBversion);
2372 }
2373
2374 $DBversion = '3.01.00.026';
2375 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2376 $dbh->do("INSERT INTO `systempreferences` ( `variable` , `value` , `options` , `explanation` , `type` ) VALUES ( 'numReturnedItemsToShow', '20', '', 'Number of returned items to show on the check-in page', 'Integer')");
2377
2378 print "Upgrade to $DBversion done (added numReturnedItemsToShow system preference)\n";
2379 SetVersion ($DBversion);
2380 }
2381
2382 $DBversion = '3.01.00.027';
2383 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2384 $dbh->do("ALTER TABLE zebraqueue CHANGE `biblio_auth_number` `biblio_auth_number` bigint(20) unsigned NOT NULL default 0");
2385 print "Upgrade to $DBversion done (Increased size of zebraqueue biblio_auth_number to address bug 3148.)\n";
2386 SetVersion ($DBversion);
2387 }
2388
2389 $DBversion = '3.01.00.028';
2390 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2391 my $enable_reviews = C4::Context->preference('AmazonEnabled') ? '1' : '0';
2392 $dbh->do("INSERT INTO systempreferences (variable,value,explanation,options,type) VALUES ('AmazonReviews', '$enable_reviews', 'Display Amazon reviews on staff interface','','YesNo')");
2393 print "Upgrade to $DBversion done (added AmazonReviews)\n";
2394 SetVersion ($DBversion);
2395 }
2396
2397 $DBversion = '3.01.00.029';
2398 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2399 $dbh->do(q( UPDATE language_rfc4646_to_iso639
2400 SET iso639_2_code = 'spa'
2401 WHERE rfc4646_subtag = 'es'
2402 AND iso639_2_code = 'rus' )
2403 );
2404 print "Upgrade to $DBversion done (fixed bug 2599: using Spanish search limit retrieves Russian results)\n";
2405 SetVersion ($DBversion);
2406 }
2407
2408 $DBversion = "3.01.00.030";
2409 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2410 $dbh->do("INSERT INTO `systempreferences` ( `variable` , `value` , `options` , `explanation` , `type` ) VALUES ( 'AllowNotForLoanOverride', '0', '', 'If ON, Koha will allow the librarian to loan a not for loan item.', 'YesNo')");
2411 print "Upgrade to $DBversion done (added AllowNotForLoanOverride system preference)\n";
2412 SetVersion ($DBversion);
2413 }
2414
2415 $DBversion = "3.01.00.031";
2416 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2417 $dbh->do("ALTER TABLE branch_transfer_limits
2418 MODIFY toBranch varchar(10) NOT NULL,
2419 MODIFY fromBranch varchar(10) NOT NULL,
2420 MODIFY itemtype varchar(10) NULL");
2421 print "Upgrade to $DBversion done (fix column widths in branch_transfer_limits)\n";
2422 SetVersion ($DBversion);
2423 }
2424
2425 $DBversion = "3.01.00.032";
2426 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2427 $dbh->do(<<ENDOFRENEWAL);
2428 INSERT INTO systempreferences (variable,value,explanation,options,type) VALUES('RenewalPeriodBase', 'now', 'Set whether the renewal date should be counted from the date_due or from the moment the Patron asks for renewal ','date_due|now','Choice');
2429 ENDOFRENEWAL
2430 print "Upgrade to $DBversion done (Change the field)\n";
2431 SetVersion ($DBversion);
2432 }
2433
2434 $DBversion = "3.01.00.033";
2435 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2436 $dbh->do(q/
2437 ALTER TABLE borrower_message_preferences
2438 MODIFY borrowernumber int(11) default NULL,
2439 ADD categorycode varchar(10) default NULL AFTER borrowernumber,
2440 ADD KEY `categorycode` (`categorycode`),
2441 ADD CONSTRAINT `borrower_message_preferences_ibfk_3`
2442 FOREIGN KEY (`categorycode`) REFERENCES `categories` (`categorycode`)
2443 ON DELETE CASCADE ON UPDATE CASCADE
2444 /);
2445 print "Upgrade to $DBversion done (DB changes to allow patron category defaults for messaging preferences)\n";
2446 SetVersion ($DBversion);
2447 }
2448
2449 $DBversion = "3.01.00.034";
2450 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2451 $dbh->do("ALTER TABLE `subscription` ADD COLUMN `graceperiod` INT(11) NOT NULL default '0';");
2452 print "Upgrade to $DBversion done (Adding graceperiod column to subscription table)\n";
2453 SetVersion ($DBversion);
2454 }
2455
2456 $DBversion = '3.01.00.035';
2457 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2458 $dbh->do(q{ ALTER TABLE `subscription` ADD location varchar(80) NULL DEFAULT '' AFTER callnumber; });
2459 print "Upgrade to $DBversion done (Adding location to subscription table)\n";
2460 SetVersion ($DBversion);
2461 }
2462
2463 $DBversion = '3.01.00.036';
2464 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2465 $dbh->do("UPDATE systempreferences SET explanation = 'Choose the default detail view in the staff interface; choose between normal, labeled_marc, marc or isbd'
2466 WHERE variable = 'IntranetBiblioDefaultView'
2467 AND explanation = 'IntranetBiblioDefaultView'");
2468 $dbh->do("UPDATE systempreferences SET type = 'Choice', options = 'normal|marc|isbd|labeled_marc'
2469 WHERE variable = 'IntranetBiblioDefaultView'");
2470 $dbh->do("INSERT INTO systempreferences (variable,value,explanation,options,type)VALUES('viewISBD','1','Allow display of ISBD view of bibiographic records','','YesNo')");
2471 $dbh->do("INSERT INTO systempreferences (variable,value,explanation,options,type)VALUES('viewLabeledMARC','0','Allow display of labeled MARC view of bibiographic records','','YesNo')");
2472 $dbh->do("INSERT INTO systempreferences (variable,value,explanation,options,type)VALUES('viewMARC','1','Allow display of MARC view of bibiographic records','','YesNo')");
2473 print "Upgrade to $DBversion done (new viewISBD, viewLabeledMARC, viewMARC sysprefs and tweak IntranetBiblioDefaultView)\n";
2474 SetVersion ($DBversion);
2475 }
2476
2477 $DBversion = '3.01.00.037';
2478 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2479 $dbh->do('ALTER TABLE authorised_values ADD KEY `lib` (`lib`)');
2480 $dbh->do("INSERT INTO systempreferences (variable,value,explanation,options,type)VALUES('FilterBeforeOverdueReport','0','Do not run overdue report until filter selected','','YesNo')");
2481 SetVersion ($DBversion);
2482 print "Upgrade to $DBversion done (added FilterBeforeOverdueReport syspref and new index on authorised_values)\n";
2483 }
2484
2485 $DBversion = "3.01.00.038";
2486 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2487 # update branches table
2488 #
2489 $dbh->do("ALTER TABLE branches ADD `branchzip` varchar(25) default NULL AFTER `branchaddress3`");
2490 $dbh->do("ALTER TABLE branches ADD `branchcity` mediumtext AFTER `branchzip`");
2491 $dbh->do("ALTER TABLE branches ADD `branchcountry` text AFTER `branchcity`");
2492 $dbh->do("ALTER TABLE branches ADD `branchurl` mediumtext AFTER `branchemail`");
2493 $dbh->do("ALTER TABLE branches ADD `branchnotes` mediumtext AFTER `branchprinter`");
2494 print "Upgrade to $DBversion done (add ZIP, city, country, URL, and notes column to branches)\n";
2495 SetVersion ($DBversion);
2496 }
2497
2498 $DBversion = '3.01.00.039';
2499 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2500 $dbh->do("INSERT INTO systempreferences (variable,value,options,explanation,type)VALUES('SpineLabelFormat', '<itemcallnumber><copynumber>', '30|10', 'This preference defines the format for the quick spine label printer. Just list the fields you would like to see in the order you would like to see them, surrounded by <>, for example <itemcallnumber>.', 'Textarea')");
2501 $dbh->do("INSERT INTO systempreferences (variable,value,options,explanation,type)VALUES('SpineLabelAutoPrint', '0', '', 'If this setting is turned on, a print dialog will automatically pop up for the quick spine label printer.', 'YesNo')");
2502 SetVersion ($DBversion);
2503 print "Upgrade to $DBversion done (added SpineLabelFormat and SpineLabelAutoPrint sysprefs)\n";
2504 }
2505
2506 $DBversion = '3.01.00.040';
2507 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2508 $dbh->do("INSERT INTO systempreferences (variable,value,explanation,options,type)VALUES('AllowHoldDateInFuture','0','If set a date field is displayed on the Hold screen of the Staff Interface, allowing the hold date to be set in the future.','','YesNo')");
2509 $dbh->do("INSERT INTO systempreferences (variable,value,explanation,options,type)VALUES('OPACAllowHoldDateInFuture','0','If set, along with the AllowHoldDateInFuture system preference, OPAC users can set the date of a hold to be in the future.','','YesNo')");
2510 SetVersion ($DBversion);
2511 print "Upgrade to $DBversion done (AllowHoldDateInFuture and OPACAllowHoldDateInFuture sysprefs)\n";
2512 }
2513
2514 $DBversion = '3.01.00.041';
2515 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2516 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('AWSPrivateKey','','See: http://aws.amazon.com. Note that this is required after 2009/08/15 in order to retrieve any enhanced content other than book covers from Amazon.','','free')");
2517 SetVersion ($DBversion);
2518 print "Upgrade to $DBversion done (added AWSPrivateKey syspref - note that if you use enhanced content from Amazon, this should be set right away.)\n";
2519 }
2520
2521 $DBversion = '3.01.00.042';
2522 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2523 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('OPACFineNoRenewals','99999','Fine Limit above which user canmot renew books via OPAC','','Integer')");
2524 SetVersion ($DBversion);
2525 print "Upgrade to $DBversion done (added OPACFineNoRenewals syspref)\n";
2526 }
2527
2528 $DBversion = '3.01.00.043';
2529 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2530 $dbh->do('ALTER TABLE items ADD COLUMN permanent_location VARCHAR(80) DEFAULT NULL AFTER location');
2531 $dbh->do('UPDATE items SET permanent_location = location');
2532 $dbh->do("INSERT INTO `systempreferences` ( `variable` , `value` , `options` , `explanation` , `type` ) VALUES ( 'NewItemsDefaultLocation', '', '', 'If set, all new items will have a location of the given Location Code ( Authorized Value type LOC )', '')");
2533 $dbh->do("INSERT INTO `systempreferences` ( `variable` , `value` , `options` , `explanation` , `type` ) VALUES ( 'InProcessingToShelvingCart', '0', '', 'If set, when any item with a location code of PROC is ''checked in'', it''s location code will be changed to CART.', 'YesNo')");
2534 $dbh->do("INSERT INTO `systempreferences` ( `variable` , `value` , `options` , `explanation` , `type` ) VALUES ( 'ReturnToShelvingCart', '0', '', 'If set, when any item is ''checked in'', it''s location code will be changed to CART.', 'YesNo')");
2535 SetVersion ($DBversion);
2536 print "Upgrade to $DBversion done (amended Item added NewItemsDefaultLocation, InProcessingToShelvingCart, ReturnToShelvingCart sysprefs)\n";
2537 }
2538
2539 $DBversion = '3.01.00.044';
2540 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2541 $dbh->do("INSERT INTO systempreferences (variable,value,explanation,options,type)VALUES( 'DisplayClearScreenButton', '0', 'If set to yes, a clear screen button will appear on the circulation page.', 'If set to yes, a clear screen button will appear on the circulation page.', 'YesNo')");
2542 SetVersion ($DBversion);
2543 print "Upgrade to $DBversion done (added DisplayClearScreenButton system preference)\n";
2544 }
2545
2546 $DBversion = '3.01.00.045';
2547 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2548 $dbh->do("INSERT INTO systempreferences (variable,value,options,explanation,type)VALUES('HidePatronName', '0', '', 'If this is switched on, patron''s cardnumber will be shown instead of their name on the holds and catalog screens', 'YesNo')");
2549 SetVersion ($DBversion);
2550 print "Upgrade to $DBversion done (added a preference to hide the patrons name in the staff catalog)\n";
2551 }
2552
2553 $DBversion = "3.01.00.046";
2554 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2555 # update borrowers table
2556 #
2557 $dbh->do("ALTER TABLE borrowers ADD `country` text AFTER zipcode");
2558 $dbh->do("ALTER TABLE borrowers ADD `B_country` text AFTER B_zipcode");
2559 $dbh->do("ALTER TABLE deletedborrowers ADD `country` text AFTER zipcode");
2560 $dbh->do("ALTER TABLE deletedborrowers ADD `B_country` text AFTER B_zipcode");
2561 print "Upgrade to $DBversion done (add country and B_country to borrowers)\n";
2562 SetVersion ($DBversion);
2563 }
2564
2565 $DBversion = '3.01.00.047';
2566 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2567 $dbh->do("ALTER TABLE items MODIFY itemcallnumber varchar(255);");
2568 $dbh->do("ALTER TABLE deleteditems MODIFY itemcallnumber varchar(255);");
2569 $dbh->do("ALTER TABLE tmp_holdsqueue MODIFY itemcallnumber varchar(255);");
2570 SetVersion ($DBversion);
2571 print " Upgrade to $DBversion done (bug 2761: change max length of itemcallnumber to 255 from 30)\n";
2572 }
2573
2574 $DBversion = '3.01.00.048';
2575 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2576 $dbh->do("UPDATE userflags SET flagdesc='View Catalog (Librarian Interface)' WHERE bit=2;");
2577 $dbh->do("UPDATE userflags SET flagdesc='Edit Catalog (Modify bibliographic/holdings data)' WHERE bit=9;");
2578 $dbh->do("UPDATE userflags SET flagdesc='Allow to edit authorities' WHERE bit=14;");
2579 $dbh->do("UPDATE userflags SET flagdesc='Allow to access to the reports module' WHERE bit=16;");
2580 $dbh->do("UPDATE userflags SET flagdesc='Allow to manage serials subscriptions' WHERE bit=15;");
2581 SetVersion ($DBversion);
2582 print " Upgrade to $DBversion done (bug 2611: fix spelling/capitalization in permission flag descriptions)\n";
2583 }
2584
2585 $DBversion = '3.01.00.049';
2586 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2587 $dbh->do("UPDATE permissions SET description = 'Perform inventory (stocktaking) of your catalog' WHERE code = 'inventory';");
2588 SetVersion ($DBversion);
2589 print "Upgrade to $DBversion done (bug 2611: changed catalogue to catalog per the standard)\n";
2590 }
2591
2592 $DBversion = '3.01.00.050';
2593 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2594 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES ('OPACSearchForTitleIn','<li class=\"yuimenuitem\">\n<a target=\"_blank\" class=\"yuimenuitemlabel\" href=\"http://worldcat.org/search?q=TITLE\">Other Libraries (WorldCat)</a></li>\n<li class=\"yuimenuitem\">\n<a class=\"yuimenuitemlabel\" href=\"http://www.scholar.google.com/scholar?q=TITLE\" target=\"_blank\">Other Databases (Google Scholar)</a></li>\n<li class=\"yuimenuitem\">\n<a class=\"yuimenuitemlabel\" href=\"http://www.bookfinder.com/search/?author=AUTHOR&amp;title=TITLE&amp;st=xl&amp;ac=qr\" target=\"_blank\">Online Stores (Bookfinder.com)</a></li>','Enter the HTML that will appear in the ''Search for this title in'' box on the detail page in the OPAC. Enter TITLE, AUTHOR, or ISBN in place of their respective variables in the URL. Leave blank to disable ''More Searches'' menu.','70|10','Textarea');");
2595 SetVersion ($DBversion);
2596 print "Upgrade to $DBversion done (bug 1934: Add OPACSearchForTitleIn syspref)\n";
2597 }
2598
2599 $DBversion = '3.01.00.051';
2600 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2601 $dbh->do("UPDATE systempreferences SET explanation='Fine limit above which user cannot renew books via OPAC' WHERE variable='OPACFineNoRenewals';");
2602 $dbh->do("UPDATE systempreferences SET explanation='If set to ON, a clear screen button will appear on the circulation page.' WHERE variable='DisplayClearScreenButton';");
2603 SetVersion ($DBversion);
2604 print "Upgrade to $DBversion done (fixed typos in new sysprefs)\n";
2605 }
2606
2607 $DBversion = '3.01.00.052';
2608 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2609 $dbh->do('ALTER TABLE deleteditems ADD COLUMN permanent_location VARCHAR(80) DEFAULT NULL AFTER location');
2610 SetVersion ($DBversion);
2611 print "Upgrade to $DBversion done (bug 3481: add permanent_location column to deleteditems)\n";
2612 }
2613
2614 $DBversion = '3.01.00.053';
2615 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2616 my $upgrade_script = C4::Context->config("intranetdir") . "/installer/data/mysql/labels_upgrade.pl";
2617 system("perl $upgrade_script");
2618 print "Upgrade to $DBversion done (Migrated labels tables and data to new schema.) NOTE: All existing label batches have been assigned to the first branch in the list of branches. This is ONLY true of migrated label batches.\n";
2619 SetVersion ($DBversion);
2620 }
2621
2622 $DBversion = '3.01.00.054';
2623 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2624 $dbh->do("ALTER TABLE borrowers ADD `B_address2` text AFTER B_address");
2625 $dbh->do("ALTER TABLE borrowers ADD `altcontactcountry` text AFTER altcontactzipcode");
2626 $dbh->do("ALTER TABLE deletedborrowers ADD `B_address2` text AFTER B_address");
2627 $dbh->do("ALTER TABLE deletedborrowers ADD `altcontactcountry` text AFTER altcontactzipcode");
2628 SetVersion ($DBversion);
2629 print "Upgrade to $DBversion done (bug 1600, bug 3454: add altcontactcountry and B_address2 to borrowers and deletedborrowers)\n";
2630 }
2631
2632 $DBversion = '3.01.00.055';
2633 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2634 $dbh->do(qq|UPDATE systempreferences set explanation='Enter the HTML that will appear in the ''Search for this title in'' box on the detail page in the OPAC. Enter {TITLE}, {AUTHOR}, or {ISBN} in place of their respective variables in the URL. Leave blank to disable ''More Searches'' menu.', value='<li><a href="http://worldcat.org/search?q={TITLE}" target="_blank">Other Libraries (WorldCat)</a></li>\n<li><a href="http://www.scholar.google.com/scholar?q={TITLE}" target="_blank">Other Databases (Google Scholar)</a></li>\n<li><a href="http://www.bookfinder.com/search/?author={AUTHOR}&amp;title={TITLE}&amp;st=xl&amp;ac=qr" target="_blank">Online Stores (Bookfinder.com)</a></li>' WHERE variable='OPACSearchForTitleIn'|);
2635 SetVersion ($DBversion);
2636 print "Upgrade to $DBversion done (changed OPACSearchForTitleIn per requests in bug 1934)\n";
2637 }
2638
2639 $DBversion = '3.01.00.056';
2640 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2641 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES ('OPACPatronDetails','1','If OFF the patron details tab in the OPAC is disabled.','','YesNo');");
2642 SetVersion ($DBversion);
2643 print "Upgrade to $DBversion done (Bug 1172 : Add OPACPatronDetails syspref)\n";
2644 }
2645
2646 $DBversion = '3.01.00.057';
2647 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2648 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES ('OPACFinesTab','1','If OFF the patron fines tab in the OPAC is disabled.','','YesNo');");
2649 SetVersion ($DBversion);
2650 print "Upgrade to $DBversion done (Bug 2576 : Add OPACFinesTab syspref)\n";
2651 }
2652
2653 $DBversion = '3.01.00.058';
2654 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2655 $dbh->do("ALTER TABLE `language_subtag_registry` ADD `id` INT( 11 ) NOT NULL AUTO_INCREMENT PRIMARY KEY;");
2656 $dbh->do("ALTER TABLE `language_rfc4646_to_iso639` ADD `id` INT( 11 ) NOT NULL AUTO_INCREMENT PRIMARY KEY;");
2657 $dbh->do("ALTER TABLE `language_descriptions` ADD `id` INT( 11 ) NOT NULL AUTO_INCREMENT PRIMARY KEY;");
2658 SetVersion ($DBversion);
2659 print "Upgrade to $DBversion done (Added primary keys to language tables)\n";
2660 }
2661
2662 $DBversion = '3.01.00.059';
2663 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2664 $dbh->do("INSERT INTO systempreferences (variable,value,options,explanation,type)VALUES('DisplayOPACiconsXSLT', '1', '', 'If ON, displays the format, audience, type icons in XSLT MARC21 results and display pages.', 'YesNo')");
2665 SetVersion ($DBversion);
2666 print "Upgrade to $DBversion done (added DisplayOPACiconsXSLT sysprefs)\n";
2667 }
2668
2669 $DBversion = '3.01.00.060';
2670 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2671 $dbh->do("INSERT INTO systempreferences (variable,value,explanation,options,type) VALUES('AllowAllMessageDeletion','0','Allow any Library to delete any message','','YesNo');");
2672 $dbh->do('DROP TABLE IF EXISTS messages');
2673 $dbh->do("CREATE TABLE messages ( `message_id` int(11) NOT NULL auto_increment,
2674 `borrowernumber` int(11) NOT NULL,
2675 `branchcode` varchar(4) default NULL,
2676 `message_type` varchar(1) NOT NULL,
2677 `message` text NOT NULL,
2678 `message_date` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
2679 PRIMARY KEY (`message_id`)
2680 ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
2681
2682 print "Upgrade to $DBversion done ( Added AllowAllMessageDeletion syspref and messages table )\n";
2683 SetVersion ($DBversion);
2684 }
2685
2686 $DBversion = '3.01.00.061';
2687 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2688 $dbh->do("INSERT INTO systempreferences (variable,value,explanation,options,type)VALUES('ShowPatronImageInWebBasedSelfCheck', '0', 'If ON, displays patron image when a patron uses web-based self-checkout', '', 'YesNo')");
2689 print "Upgrade to $DBversion done ( Added ShowPatronImageInWebBasedSelfCheck system preference )\n";
2690 SetVersion ($DBversion);
2691 }
2692
2693 $DBversion = "3.01.00.062";
2694 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2695 $dbh->do("INSERT INTO permissions (module_bit, code, description) VALUES ( 13, 'manage_csv_profiles', 'Manage CSV export profiles')");
2696 $dbh->do(q/
2697 CREATE TABLE `export_format` (
2698 `export_format_id` int(11) NOT NULL auto_increment,
2699 `profile` varchar(255) NOT NULL,
2700 `description` mediumtext NOT NULL,
2701 `marcfields` mediumtext NOT NULL,
2702 PRIMARY KEY (`export_format_id`)
2703 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Used for CSV export';
2704 /);
2705 print "Upgrade to $DBversion done (added csv export profiles)\n";
2706 }
2707
2708 $DBversion = "3.01.00.063";
2709 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2710 $dbh->do("
2711 CREATE TABLE `fieldmapping` (
2712 `id` int(11) NOT NULL auto_increment,
2713 `field` varchar(255) NOT NULL,
2714 `frameworkcode` char(4) NOT NULL default '',
2715 `fieldcode` char(3) NOT NULL,
2716 `subfieldcode` char(1) NOT NULL,
2717 PRIMARY KEY (`id`)
2718 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2719 ");
2720 SetVersion ($DBversion);print "Upgrade to $DBversion done (Created table fieldmapping)\n";print "Upgrade to 3.01.00.064 done (Version number skipped: nothing done)\n";
2721 }
2722
2723 $DBversion = '3.01.00.065';
2724 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2725 $dbh->do('ALTER TABLE issuingrules ADD COLUMN `renewalsallowed` smallint(6) NOT NULL default "0" AFTER `issuelength`;');
2726 $sth = $dbh->prepare("SELECT itemtype, renewalsallowed FROM itemtypes");
2727 $sth->execute();
2728
2729 my $sthupd = $dbh->prepare("UPDATE issuingrules SET renewalsallowed = ? WHERE itemtype = ?");
2730
2731 while(my $row = $sth->fetchrow_hashref){
2732 $sthupd->execute($row->{renewalsallowed}, $row->{itemtype});
2733 }
2734
2735 $dbh->do('ALTER TABLE itemtypes DROP COLUMN `renewalsallowed`;');
2736
2737 SetVersion ($DBversion);
2738 print "Upgrade to $DBversion done (Moving allowed renewals from itemtypes to issuingrule)\n";
2739 }
2740
2741 $DBversion = '3.01.00.066';
2742 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2743 $dbh->do('ALTER TABLE issuingrules ADD COLUMN `reservesallowed` smallint(6) NOT NULL default "0" AFTER `renewalsallowed`;');
2744
2745 my $maxreserves = C4::Context->preference('maxreserves');
2746 $sth = $dbh->prepare('UPDATE issuingrules SET reservesallowed = ?;');
2747 $sth->execute($maxreserves);
2748
2749 $dbh->do('DELETE FROM systempreferences WHERE variable = "maxreserves";');
2750
2751 $dbh->do("INSERT INTO systempreferences (variable,value, options, explanation, type) VALUES('ReservesControlBranch','PatronLibrary','ItemHomeLibrary|PatronLibrary','Branch checked for members reservations rights','Choice')");
2752
2753 SetVersion ($DBversion);
2754 print "Upgrade to $DBversion done (Moving max allowed reserves from system preference to issuingrule)\n";
2755 }
2756
2757 $DBversion = "3.01.00.067";
2758 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2759 $dbh->do("INSERT INTO permissions (module_bit, code, description) VALUES ( 13, 'batchmod', 'Perform batch modification of items')");
2760 $dbh->do("INSERT INTO permissions (module_bit, code, description) VALUES ( 13, 'batchdel', 'Perform batch deletion of items')");
2761 print "Upgrade to $DBversion done (added permissions for batch modification and deletion)\n";
2762 SetVersion ($DBversion);
2763 }
2764
2765 $DBversion = "3.01.00.068";
2766 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2767 $dbh->do("ALTER TABLE issuingrules ADD COLUMN `finedays` int(11) default NULL AFTER `fine` ");
2768 print "Upgrade to $DBversion done (Adding finedays in issuingrules table)\n";
2769 SetVersion ($DBversion);
2770 }
2771
2772
2773 $DBversion = "3.01.00.069";
2774 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2775 $dbh->do("INSERT INTO `systempreferences` (`variable`, `value`, `options`, `explanation`, `type`) VALUES ('EnableOpacSearchHistory', '1', '', 'Enable or disable opac search history', 'YesNo')");
2776
2777 my $create = <<SEARCHHIST;
2778 CREATE TABLE IF NOT EXISTS `search_history` (
2779 `userid` int(11) NOT NULL,
2780 `sessionid` varchar(32) NOT NULL,
2781 `query_desc` varchar(255) NOT NULL,
2782 `query_cgi` varchar(255) NOT NULL,
2783 `total` int(11) NOT NULL,
2784 `time` timestamp NOT NULL default CURRENT_TIMESTAMP,
2785 KEY `userid` (`userid`),
2786 KEY `sessionid` (`sessionid`)
2787 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Opac search history results';
2788 SEARCHHIST
2789 $dbh->do($create);
2790
2791 print "Upgrade to $DBversion done (added OPAC search history preference and table)\n";
2792 }
2793
2794 $DBversion = "3.01.00.070";
2795 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2796 $dbh->do("ALTER TABLE authorised_values ADD COLUMN `lib_opac` VARCHAR(80) default NULL AFTER `lib`");
2797 print "Upgrade to $DBversion done (Added a lib_opac field in authorised_values table)\n";
2798 }
2799
2800 $DBversion = "3.01.00.071";
2801 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2802 $dbh->do("ALTER TABLE `subscription` ADD `enddate` date default NULL");
2803 $dbh->do("ALTER TABLE subscriptionhistory CHANGE enddate histenddate DATE default NULL");
2804 print "Upgrade to $DBversion done ( Adding enddate to subscription)\n";
2805 }
2806
2807 # Acquisitions update
2808
2809 $DBversion = "3.01.00.072";
2810 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2811 $dbh->do("INSERT INTO systempreferences (variable,value,explanation,options,type) VALUES('OpacPrivacy', '0', 'if ON, allows patrons to define their privacy rules (reading history)',NULL,'YesNo')");
2812 # create a new syspref for the 'Mr anonymous' patron
2813 $dbh->do("INSERT INTO systempreferences (variable,value,explanation,options,type) VALUES('AnonymousPatron', '0', \"Set the identifier (borrowernumber) of the 'Mister anonymous' patron. Used for Suggestion and reading history privacy\",NULL,'')");
2814 # fill AnonymousPatron with AnonymousSuggestion value (copy)
2815 my $sth=$dbh->prepare("SELECT value FROM systempreferences WHERE variable='AnonSuggestions'");
2816 $sth->execute;
2817 my ($value) = $sth->fetchrow() || 0;
2818 $dbh->do("UPDATE systempreferences SET value='$value' WHERE variable='AnonymousPatron'");
2819 # set AnonymousSuggestion do YesNo
2820 # 1st, set the value (1/True if it had a borrowernumber)
2821 $dbh->do("UPDATE systempreferences SET value=1 WHERE variable='AnonSuggestions' AND value>0");
2822 # 2nd, change the type to Choice
2823 $dbh->do("UPDATE systempreferences SET type='YesNo' WHERE variable='AnonSuggestions'");
2824 # borrower reading record privacy : 0 : forever, 1 : laws, 2 : don't keep at all
2825 $dbh->do("ALTER TABLE `borrowers` ADD `privacy` INTEGER NOT NULL DEFAULT 1;");
2826 print "Upgrade to $DBversion done (add new syspref and column in borrowers)\n";
2827 SetVersion ($DBversion);
2828 }
2829
2830 $DBversion = '3.01.00.073';
2831 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2832 $dbh->do('SET FOREIGN_KEY_CHECKS=0 ');
2833 $dbh->do(<<'END_SQL');
2834 CREATE TABLE IF NOT EXISTS `aqcontract` (
2835 `contractnumber` int(11) NOT NULL auto_increment,
2836 `contractstartdate` date default NULL,
2837 `contractenddate` date default NULL,
2838 `contractname` varchar(50) default NULL,
2839 `contractdescription` mediumtext,
2840 `booksellerid` int(11) not NULL,
2841 PRIMARY KEY (`contractnumber`),
2842 CONSTRAINT `booksellerid_fk1` FOREIGN KEY (`booksellerid`)
2843 REFERENCES `aqbooksellers` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
2844 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
2845 END_SQL
2846 $dbh->do('SET FOREIGN_KEY_CHECKS=1 ');
2847 print "Upgrade to $DBversion done (adding aqcontract table)\n";
2848 SetVersion ($DBversion);
2849 }
2850
2851 $DBversion = '3.01.00.074';
2852 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2853 $dbh->do("ALTER TABLE `aqbasket` ADD COLUMN `basketname` varchar(50) default NULL AFTER `basketno`");
2854 $dbh->do("ALTER TABLE `aqbasket` ADD COLUMN `note` mediumtext AFTER `basketname`");
2855 $dbh->do("ALTER TABLE `aqbasket` ADD COLUMN `booksellernote` mediumtext AFTER `note`");
2856 $dbh->do("ALTER TABLE `aqbasket` ADD COLUMN `contractnumber` int(11) AFTER `booksellernote`");
2857 $dbh->do("ALTER TABLE `aqbasket` ADD FOREIGN KEY (`contractnumber`) REFERENCES `aqcontract` (`contractnumber`)");
2858 print "Upgrade to $DBversion done (edit aqbasket table done)\n";
2859 SetVersion ($DBversion);
2860 }
2861
2862 $DBversion = '3.01.00.075';
2863 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2864 $dbh->do("ALTER TABLE `aqorders` ADD COLUMN `uncertainprice` tinyint(1)");
2865
2866 print "Upgrade to $DBversion done (adding uncertainprices)\n";
2867 SetVersion ($DBversion);
2868 }
2869
2870 $DBversion = '3.01.00.076';
2871 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2872 $dbh->do('SET FOREIGN_KEY_CHECKS=0 ');
2873 $dbh->do("CREATE TABLE IF NOT EXISTS `aqbasketgroups` (
2874 `id` int(11) NOT NULL auto_increment,
2875 `name` varchar(50) default NULL,
2876 `closed` tinyint(1) default NULL,
2877 `booksellerid` int(11) NOT NULL,
2878 PRIMARY KEY (`id`),
2879 KEY `booksellerid` (`booksellerid`),
2880 CONSTRAINT `aqbasketgroups_ibfk_1` FOREIGN KEY (`booksellerid`) REFERENCES `aqbooksellers` (`id`) ON UPDATE CASCADE ON DELETE CASCADE
2881 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;");
2882 $dbh->do("ALTER TABLE aqbasket ADD COLUMN `basketgroupid` int(11)");
2883 $dbh->do("ALTER TABLE aqbasket ADD FOREIGN KEY (`basketgroupid`) REFERENCES `aqbasketgroups` (`id`) ON UPDATE CASCADE ON DELETE SET NULL");
2884 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES ('pdfformat','pdfformat::layout2pages','Controls what script is used for printing (basketgroups)','','free')");
2885 $dbh->do('SET FOREIGN_KEY_CHECKS=1 ');
2886 print "Upgrade to $DBversion done (adding basketgroups)\n";
2887 SetVersion ($DBversion);
2888 }
2889 $DBversion = '3.01.00.077';
2890 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
2891
2892 $dbh->do("SET FOREIGN_KEY_CHECKS=0 ");
2893 # create a mapping table holding the info we need to match orders to budgets
2894 $dbh->do('DROP TABLE IF EXISTS fundmapping');
2895 $dbh->do(
2896 q|CREATE TABLE fundmapping AS
2897 SELECT aqorderbreakdown.ordernumber, branchcode, bookfundid, budgetdate, entrydate
2898 FROM aqorderbreakdown JOIN aqorders ON aqorderbreakdown.ordernumber = aqorders.ordernumber|);
2899 # match the new type of the corresponding field
2900 $dbh->do('ALTER TABLE fundmapping modify column bookfundid varchar(30)');
2901 # System did not ensure budgetdate was valid historically
2902 $dbh->do(q|UPDATE fundmapping SET budgetdate = entrydate WHERE budgetdate = '0000-00-00' OR budgetdate IS NULL|);
2903 # We save the map in fundmapping in case you need later processing
2904 $dbh->do(q|ALTER TABLE fundmapping add column aqbudgetid integer|);
2905 # these can speed processing up
2906 $dbh->do(q|CREATE INDEX fundmaporder ON fundmapping (ordernumber)|);
2907 $dbh->do(q|CREATE INDEX fundmapid ON fundmapping (bookfundid)|);
2908
2909 $dbh->do("DROP TABLE IF EXISTS `aqbudgetperiods` ");
2910
2911 $dbh->do(qq|
2912 CREATE TABLE `aqbudgetperiods` (
2913 `budget_period_id` int(11) NOT NULL auto_increment,
2914 `budget_period_startdate` date NOT NULL,
2915 `budget_period_enddate` date NOT NULL,
2916 `budget_period_active` tinyint(1) default '0',
2917 `budget_period_description` mediumtext,
2918 `budget_period_locked` tinyint(1) default NULL,
2919 `sort1_authcat` varchar(10) default NULL,
2920 `sort2_authcat` varchar(10) default NULL,
2921 PRIMARY KEY (`budget_period_id`)
2922 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 |);
2923
2924 $dbh->do(<<ADDPERIODS);
2925 INSERT INTO aqbudgetperiods (budget_period_startdate,budget_period_enddate,budget_period_active,budget_period_description,budget_period_locked)
2926 SELECT DISTINCT startdate, enddate, NOW() BETWEEN startdate and enddate, concat(startdate," ",enddate),NOT NOW() BETWEEN startdate AND enddate from aqbudget
2927 ADDPERIODS
2928 # SORRY , NO AQBUDGET/AQBOOKFUND -> AQBUDGETS IMPORT JUST YET,
2929 # BUT A NEW CLEAN AQBUDGETS TABLE CREATE FOR NOW..
2930 # DROP TABLE IF EXISTS `aqbudget`;
2931 #CREATE TABLE `aqbudget` (
2932 # `bookfundid` varchar(10) NOT NULL default ',
2933 # `startdate` date NOT NULL default 0,
2934 # `enddate` date default NULL,
2935 # `budgetamount` decimal(13,2) default NULL,
2936 # `aqbudgetid` tinyint(4) NOT NULL auto_increment,
2937 # `branchcode` varchar(10) default NULL,
2938 DropAllForeignKeys('aqbudget');
2939 #$dbh->do("drop table aqbudget;");
2940
2941
2942 my $maxbudgetid = $dbh->selectcol_arrayref(<<IDsBUDGET);
2943 SELECT MAX(aqbudgetid) from aqbudget
2944 IDsBUDGET
2945
2946 $$maxbudgetid[0] = 0 if !$$maxbudgetid[0];
2947
2948 $dbh->do(<<BUDGETAUTOINCREMENT);
2949 ALTER TABLE aqbudget AUTO_INCREMENT=$$maxbudgetid[0]
2950 BUDGETAUTOINCREMENT
2951
2952 $dbh->do(<<BUDGETNAME);
2953 ALTER TABLE aqbudget RENAME `aqbudgets`
2954 BUDGETNAME
2955
2956 $dbh->do(<<BUDGETS);
2957 ALTER TABLE `aqbudgets`
2958 CHANGE COLUMN aqbudgetid `budget_id` int(11) NOT NULL AUTO_INCREMENT,
2959 CHANGE COLUMN branchcode `budget_branchcode` varchar(10) default NULL,
2960 CHANGE COLUMN budgetamount `budget_amount` decimal(28,6) NOT NULL default '0.00',
2961 CHANGE COLUMN bookfundid `budget_code` varchar(30) default NULL,
2962 ADD COLUMN `budget_parent_id` int(11) default NULL,
2963 ADD COLUMN `budget_name` varchar(80) default NULL,
2964 ADD COLUMN `budget_encumb` decimal(28,6) default '0.00',
2965 ADD COLUMN `budget_expend` decimal(28,6) default '0.00',
2966 ADD COLUMN `budget_notes` mediumtext,
2967 ADD COLUMN `budget_description` mediumtext,
2968 ADD COLUMN `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
2969 ADD COLUMN `budget_amount_sublevel` decimal(28,6) AFTER `budget_amount`,
2970 ADD COLUMN `budget_period_id` int(11) default NULL,
2971 ADD COLUMN `sort1_authcat` varchar(80) default NULL,
2972 ADD COLUMN `sort2_authcat` varchar(80) default NULL,
2973 ADD COLUMN `budget_owner_id` int(11) default NULL,
2974 ADD COLUMN `budget_permission` int(1) default '0';
2975 BUDGETS
2976
2977 $dbh->do(<<BUDGETCONSTRAINTS);
2978 ALTER TABLE `aqbudgets`
2979 ADD CONSTRAINT `aqbudgets_ifbk_1` FOREIGN KEY (`budget_period_id`) REFERENCES `aqbudgetperiods` (`budget_period_id`) ON DELETE CASCADE ON UPDATE CASCADE
2980 BUDGETCONSTRAINTS
2981 # $dbh->do(<<BUDGETPKDROP);
2982 #ALTER TABLE `aqbudgets`
2983 # DROP PRIMARY KEY
2984 #BUDGETPKDROP
2985 # $dbh->do(<<BUDGETPKADD);
2986 #ALTER TABLE `aqbudgets`
2987 # ADD PRIMARY KEY budget_id
2988 #BUDGETPKADD
2989
2990
2991 my $query_period= $dbh->prepare(qq|SELECT budget_period_id from aqbudgetperiods where budget_period_startdate=? and budget_period_enddate=?|);
2992 my $query_bookfund= $dbh->prepare(qq|SELECT * from aqbookfund where bookfundid=?|);
2993 my $selectbudgets=$dbh->prepare(qq|SELECT * from aqbudgets|);
2994 my $updatebudgets=$dbh->prepare(qq|UPDATE aqbudgets SET budget_period_id= ? , budget_name=?, budget_branchcode=? where budget_id=?|);
2995 $selectbudgets->execute;
2996 while (my $databudget=$selectbudgets->fetchrow_hashref){
2997 $query_period->execute ($$databudget{startdate},$$databudget{enddate});
2998 my ($budgetperiodid)=$query_period->fetchrow;
2999 $query_bookfund->execute ($$databudget{budget_code});
3000 my $databf=$query_bookfund->fetchrow_hashref;
3001 my $branchcode=$$databudget{budget_branchcode}||$$databf{branchcode};
3002 $updatebudgets->execute($budgetperiodid,$$databf{bookfundname},$branchcode,$$databudget{budget_id});
3003 }
3004 $dbh->do(<<BUDGETDROPDATES);
3005 ALTER TABLE `aqbudgets`
3006 DROP startdate,
3007 DROP enddate
3008 BUDGETDROPDATES
3009
3010
3011 $dbh->do("DROP TABLE IF EXISTS `aqbudgets_planning` ");
3012 $dbh->do("CREATE TABLE `aqbudgets_planning` (
3013 `plan_id` int(11) NOT NULL auto_increment,
3014 `budget_id` int(11) NOT NULL,
3015 `budget_period_id` int(11) NOT NULL,
3016 `estimated_amount` decimal(28,6) default NULL,
3017 `authcat` varchar(30) NOT NULL,
3018 `authvalue` varchar(30) NOT NULL,
3019 `display` tinyint(1) DEFAULT 1,
3020 PRIMARY KEY (`plan_id`),
3021 CONSTRAINT `aqbudgets_planning_ifbk_1` FOREIGN KEY (`budget_id`) REFERENCES `aqbudgets` (`budget_id`) ON DELETE CASCADE ON UPDATE CASCADE
3022 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;");
3023
3024 $dbh->do("ALTER TABLE `aqorders`
3025 ADD COLUMN `budget_id` tinyint(4) NOT NULL,
3026 ADD COLUMN `budgetgroup_id` int(11) NOT NULL,
3027 ADD COLUMN `sort1_authcat` varchar(10) default NULL,
3028 ADD COLUMN `sort2_authcat` varchar(10) default NULL" );
3029 # We need to map the orders to the budgets
3030 # For Historic reasons this is more complex than it should be on occasions
3031 my $budg_arr = $dbh->selectall_arrayref(
3032 q|SELECT aqbudgets.budget_id, aqbudgets.budget_code, aqbudgetperiods.budget_period_startdate,
3033 aqbudgetperiods.budget_period_enddate
3034 FROM aqbudgets JOIN aqbudgetperiods ON aqbudgets.budget_period_id = aqbudgetperiods.budget_period_id
3035 ORDER BY budget_code, budget_period_startdate|, { Slice => {} });
3036 # We arbitarily order on start date, this means if you have overlapping periods the order will be
3037 # linked to the latest matching budget YMMV
3038 my $b_sth = $dbh->prepare(
3039 'UPDATE fundmapping set aqbudgetid = ? where bookfundid =? AND budgetdate >= ? AND budgetdate <= ?');
3040 for my $b ( @{$budg_arr}) {
3041 $b_sth->execute($b->{budget_id}, $b->{budget_code}, $b->{budget_period_startdate}, $b->{budget_period_enddate});
3042 }
3043 # move the budgetids to aqorders
3044 $dbh->do(q|UPDATE aqorders, fundmapping SET aqorders.budget_id = fundmapping.aqbudgetid
3045 WHERE aqorders.ordernumber = fundmapping.ordernumber AND fundmapping.aqbudgetid IS NOT NULL|);
3046 # NB fundmapping is left as an accontants trail also if you have budgetids that werent set
3047 # you can decide what to do with them
3048
3049 $dbh->do(
3050 q|UPDATE aqorders, aqbudgets SET aqorders.budgetgroup_id = aqbudgets.budget_period_id
3051 WHERE aqorders.budget_id = aqbudgets.budget_id|);
3052 # cannot do until aqorderbreakdown removed
3053 # $dbh->do("DROP TABLE aqbookfund ");
3054 # $dbh->do("ALTER TABLE aqorders ADD FOREIGN KEY (`budget_id`) REFERENCES `aqbudgets` (`budget_id`) ON UPDATE CASCADE " ); ????
3055 $dbh->do("SET FOREIGN_KEY_CHECKS=1 ");
3056
3057 print "Upgrade to $DBversion done (Adding new aqbudgetperiods, aqbudgets and aqbudget_planning tables )\n";
3058 SetVersion ($DBversion);
3059 }
3060
3061
3062
3063 $DBversion = '3.01.00.078';
3064 if ( C4::Context->preference("Version") < TransformToNum($DBversion) ) {
3065 $dbh->do("ALTER TABLE aqbudgetperiods ADD COLUMN budget_period_total decimal(28,6)");
3066 print "Upgrade to $DBversion done (adds 'budget_period_total' column to aqbudgetperiods table)\n";
3067 SetVersion($DBversion);
3068 }
3069
3070
3071 $DBversion = '3.01.00.079';
3072 if ( C4::Context->preference("Version") < TransformToNum($DBversion) ) {
3073 $dbh->do("ALTER TABLE currency ADD COLUMN active tinyint(1)");
3074
3075 print "Upgrade to $DBversion done (adds 'active' column to currencies table)\n";
3076 SetVersion($DBversion);
3077 }
3078
3079 $DBversion = '3.01.00.080';
3080 if ( C4::Context->preference("Version") < TransformToNum($DBversion) ) {
3081 $dbh->do(<<BUDG_PERM );
3082 INSERT INTO permissions (module_bit, code, description) VALUES
3083 (11, 'vendors_manage', 'Manage vendors'),
3084 (11, 'contracts_manage', 'Manage contracts'),
3085 (11, 'period_manage', 'Manage periods'),
3086 (11, 'budget_manage', 'Manage budgets'),
3087 (11, 'budget_modify', "Modify budget (can't create lines but can modify existing ones)"),
3088 (11, 'planning_manage', 'Manage budget plannings'),
3089 (11, 'order_manage', 'Manage orders & basket'),
3090 (11, 'group_manage', 'Manage orders & basketgroups'),
3091 (11, 'order_receive', 'Manage orders & basket'),
3092 (11, 'budget_add_del', "Add and delete budgets (but can't modify budgets)");
3093 BUDG_PERM
3094
3095 print "Upgrade to $DBversion done (adds permissions for the acquisitions module)\n";
3096 SetVersion($DBversion);
3097 }
3098
3099
3100 $DBversion = '3.01.00.081';
3101 if ( C4::Context->preference("Version") < TransformToNum($DBversion) ) {
3102 $dbh->do("ALTER TABLE aqbooksellers ADD COLUMN `gstrate` decimal(6,4) default NULL");
3103 if (my $gist=C4::Context->preference("gist")){
3104 my $sql=$dbh->prepare("UPDATE aqbooksellers set `gstrate`=? ");
3105 $sql->execute($gist) ;
3106 }
3107 print "Upgrade to $DBversion done (added per-supplier gstrate setting)\n";
3108 SetVersion($DBversion);
3109 }
3110
3111 $DBversion = "3.01.00.082";
3112 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
3113 if (C4::Context->preference("opaclanguages") eq "fr") {
3114 $dbh->do(qq#INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('AcqCreateItem','ordering',"Définit quand l'exemplaire est créé : à la commande, à la livraison, au catalogage",'ordering|receiving|cataloguing','Choice')#);
3115 } else {
3116 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('AcqCreateItem','ordering','Define when the item is created : when ordering, when receiving, or in cataloguing module','ordering|receiving|cataloguing','Choice')");
3117 }
3118 print "Upgrade to $DBversion done (adding ReservesNeedReturns systempref, in circulation)\n";
3119 SetVersion ($DBversion);
3120 }
3121
3122 $DBversion = "3.01.00.083";
3123 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
3124 $dbh->do(qq|
3125 CREATE TABLE `aqorders_items` (
3126 `ordernumber` int(11) NOT NULL,
3127 `itemnumber` int(11) NOT NULL,
3128 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
3129 PRIMARY KEY (`itemnumber`),
3130 KEY `ordernumber` (`ordernumber`)
3131 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
3132 );
3133
3134 $dbh->do(qq| DROP TABLE aqorderbreakdown |);
3135 $dbh->do('DROP TABLE aqbookfund');
3136 print "Upgrade to $DBversion done (New aqorders_items table for acqui)\n";
3137 SetVersion ($DBversion);
3138 }
3139
3140 $DBversion = "3.01.00.084";
3141 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
3142 $dbh->do( qq# INSERT INTO `systempreferences` VALUES ('CurrencyFormat','US','US|FR','Determines the display format of currencies. eg: ''36000'' is displayed as ''360 000,00'' in ''FR'' or 360,000.00'' in ''US''.','Choice') #);
3143
3144 print "Upgrade to $DBversion done (CurrencyFormat syspref added)\n";
3145 SetVersion ($DBversion);
3146 }
3147
3148 $DBversion = "3.01.00.085";
3149 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
3150 $dbh->do("ALTER table aqorders drop column title");
3151 $dbh->do("ALTER TABLE `aqorders` CHANGE `budget_id` `budget_id` INT( 11 ) NOT NULL");
3152 print "Upgrade to $DBversion done update budget_id size that should not be a tinyint\n";
3153 SetVersion ($DBversion);
3154 }
3155
3156 $DBversion = "3.01.00.086";
3157 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
3158 $dbh->do(<<SUGGESTIONS);
3159 ALTER table suggestions
3160 ADD budgetid INT(11),
3161 ADD branchcode VARCHAR(10) default NULL,
3162 ADD acceptedby INT(11) default NULL,
3163 ADD accepteddate date default NULL,
3164 ADD suggesteddate date default NULL,
3165 ADD manageddate date default NULL,
3166 ADD rejectedby INT(11) default NULL,
3167 ADD rejecteddate date default NULL,
3168 ADD collectiontitle text default NULL,
3169 ADD itemtype VARCHAR(30) default NULL
3170 ;
3171 SUGGESTIONS
3172 print "Upgrade to $DBversion done (Suggestions)\n";
3173 SetVersion ($DBversion);
3174 }
3175
3176 $DBversion = "3.01.00.087";
3177 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
3178 $dbh->do("ALTER table aqbudgets drop column budget_amount_sublevel;");
3179 print "Upgrade to $DBversion done (Drop column budget_amount_sublevel from aqbudgets)\n";
3180 SetVersion ($DBversion);
3181 }
3182
3183 $DBversion = "3.01.00.088";
3184 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
3185 $dbh->do( qq# INSERT INTO `systempreferences` VALUES ('intranetbookbag','1','','If ON, enables display of Cart feature in the intranet','YesNo') #);
3186
3187 print "Upgrade to $DBversion done (intranetbookbag syspref added)\n";
3188 SetVersion ($DBversion);
3189 }
3190
3191 $DBversion = "3.01.00.090";
3192 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
3193 $dbh->do("
3194 INSERT INTO `permissions` (`module_bit`, `code`, `description`) VALUES
3195 (16, 'execute_reports', 'Execute SQL reports'),
3196 (16, 'create_reports', 'Create SQL Reports')
3197 ");
3198
3199 print "Upgrade to $DBversion done (granular permissions for guided reports added)\n";
3200 SetVersion ($DBversion);
3201 }
3202
3203 $DBversion = "3.01.00.091";
3204 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
3205 $dbh->do("
3206 UPDATE `systempreferences` SET `options` = 'holdings|serialcollection|subscriptions'
3207 WHERE `systempreferences`.`variable` = 'opacSerialDefaultTab' LIMIT 1
3208 ");
3209
3210 print "Upgrade to $DBversion done (opac-detail default tag updated)\n";
3211 SetVersion ($DBversion);
3212 }
3213
3214 $DBversion = "3.01.00.092";
3215 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
3216 if (C4::Context->preference("opaclanguages") =~ /fr/) {
3217 $dbh->do(qq{
3218 INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES ('RoutingListAddReserves','1','Si activé, des reservations sont automatiquement créées pour chaque lecteur de la liste de circulation d''un numéro de périodique','','YesNo');
3219 });
3220 }else{
3221 $dbh->do(qq{
3222 INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES ('RoutingListAddReserves','1','If ON the patrons on routing lists are automatically added to holds on the issue.','','YesNo');
3223 });
3224 }
3225 print "Upgrade to $DBversion done (Added RoutingListAddReserves syspref)\n";
3226 SetVersion ($DBversion);
3227 }
3228
3229 $DBversion = "3.01.00.093";
3230 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
3231 $dbh->do(qq{
3232 ALTER TABLE biblioitems ADD INDEX issn_idx (issn);
3233 });
3234 print "Upgrade to $DBversion done (added index to ISSN)\n";
3235 SetVersion ($DBversion);
3236 }
3237
3238 $DBversion = "3.01.00.094";
3239 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
3240 $dbh->do(qq{
3241 ALTER TABLE aqbasketgroups ADD deliveryplace VARCHAR(10) default NULL, ADD deliverycomment VARCHAR(255) default NULL;
3242 });
3243
3244 print "Upgrade to $DBversion done (adding deliveryplace deliverycomment to basketgroups)\n";
3245 SetVersion ($DBversion);
3246 }
3247
3248 $DBversion = "3.01.00.095";
3249 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
3250 $dbh->do(qq{
3251 ALTER TABLE items ADD stocknumber VARCHAR(32) DEFAULT NULL COMMENT "stores the inventory number";
3252 });
3253 $dbh->do(qq{
3254 ALTER TABLE items ADD UNIQUE INDEX itemsstocknumberidx (stocknumber);
3255 });
3256 $dbh->do(qq{
3257 ALTER TABLE deleteditems ADD stocknumber VARCHAR(32) DEFAULT NULL COMMENT "stores the inventory number of deleted items";
3258 });
3259 $dbh->do(qq{
3260 ALTER TABLE deleteditems ADD UNIQUE INDEX deleteditemsstocknumberidx (stocknumber);
3261 });
3262 if (C4::Context->preference('marcflavour') eq 'UNIMARC'){
3263 $dbh->do(qq{
3264 INSERT IGNORE INTO marc_subfield_structure (frameworkcode,tagfield, tagsubfield, tab, repeatable, mandatory,kohafield)
3265 SELECT DISTINCT (frameworkcode),995,"j",10,0,0,"items.stocknumber" from biblio_framework ;
3266 });
3267 #Previously, copynumber was used as stocknumber
3268 $dbh->do(qq{
3269 UPDATE items set stocknumber=copynumber;
3270 });
3271 $dbh->do(qq{
3272 UPDATE items set copynumber=NULL;
3273 });
3274 }
3275 print "Upgrade to $DBversion done (stocknumber field added)\n";
3276 SetVersion ($DBversion);
3277 }
3278
3279 $DBversion = "3.01.00.096";
3280 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
3281 $dbh->do("INSERT INTO systempreferences (variable,value,explanation,options,type) VALUES ('OrderPdfTemplate','','Uploads a PDF template to use for printing baskets','NULL','Upload')");
3282 $dbh->do("UPDATE systempreferences SET variable='OrderPdfFormat' WHERE variable='pdfformat'");
3283 print "Upgrade to $DBversion done (PDF orders system preferences added and updated)\n";
3284 SetVersion ($DBversion);
3285 }
3286
3287 $DBversion = "3.01.00.097";
3288 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
3289 $dbh->do(qq{
3290 ALTER TABLE aqbasketgroups ADD billingplace VARCHAR(10) NOT NULL AFTER deliverycomment;
3291 });
3292
3293 print "Upgrade to $DBversion done (Adding billingplace to aqbasketgroups)\n";
3294 SetVersion ($DBversion);
3295 }
3296
3297 $DBversion = "3.01.00.098";
3298 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
3299 $dbh->do(qq{
3300 ALTER TABLE auth_subfield_structure MODIFY frameworkcode VARCHAR(10) NULL;
3301 });
3302
3303 print "Upgrade to $DBversion done (changing frameworkcode length in auth_subfield_structure)\n";
3304 SetVersion ($DBversion);
3305 }
3306
3307 $DBversion = "3.01.00.099";
3308 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
3309 $dbh->do(qq{
3310 INSERT INTO `permissions` (`module_bit`, `code`, `description`) VALUES
3311 (9, 'edit_catalogue', 'Edit catalogue'),
3312 (9, 'fast_cataloging', 'Fast cataloging')
3313 });
3314
3315 print "Upgrade to $DBversion done (granular permissions for cataloging added)\n";
3316 SetVersion ($DBversion);
3317 }
3318
3319 $DBversion = "3.01.00.100";
3320 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
3321 $dbh->do("INSERT INTO `systempreferences` (`variable`, `value`, `options`, `explanation`, `type`) VALUES ('casAuthentication', '0', '', 'Enable or disable CAS authentication', 'YesNo'), ('casLogout', '1', '', 'Does a logout from Koha should also log out of CAS ?', 'YesNo'), ('casServerUrl', 'https://localhost:8443/cas', '', 'URL of the cas server', 'Free')");
3322 print "Upgrade to $DBversion done (added CAS authentication system preferences)\n";
3323 SetVersion ($DBversion);
3324 }
3325
3326 $DBversion = "3.01.00.101";
3327 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
3328 $dbh->do(
3329 "INSERT INTO systempreferences
3330 (variable, value, options, explanation, type)
3331 VALUES (
3332 'OverdueNoticeBcc', '', '',
3333 'Email address to Bcc outgoing notices sent by email',
3334 'free')
3335 ");
3336 print "Upgrade to $DBversion done (added OverdueNoticeBcc system preferences)\n";
3337 SetVersion ($DBversion);
3338 }
3339 $DBversion = "3.01.00.102";
3340 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
3341 $dbh->do(
3342 "UPDATE permissions set description = 'Edit catalog (Modify bibliographic/holdings data)' where module_bit = 9 and code = 'edit_catalogue'"
3343 );
3344 print "Upgrade to $DBversion done (fixed spelling error in edit_catalogue permission)\n";
3345 SetVersion ($DBversion);
3346 }
3347
3348 $DBversion = "3.01.00.103";
3349 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
3350 $dbh->do("INSERT INTO permissions (module_bit, code, description) VALUES (13, 'moderate_tags', 'Moderate patron tags')");
3351 print "Upgrade to $DBversion done (adding patron permissions for tags tool)\n";
3352 SetVersion ($DBversion);
3353 }
3354
3355 $DBversion = "3.01.00.104";
3356 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
3357
3358 my ($maninv_count, $borrnotes_count);
3359 eval { $maninv_count = $dbh->do("SELECT 1 FROM authorised_values WHERE category='MANUAL_INV'"); };
3360 if ($maninv_count == 0) {
3361 $dbh->do("INSERT INTO authorised_values (category,authorised_value,lib) VALUES ('MANUAL_INV','Copier Fees','.25')");
3362 }
3363 eval { $borrnotes_count = $dbh->do("SELECT 1 FROM authorised_values WHERE category='BOR_NOTES'"); };
3364 if ($borrnotes_count == 0) {
3365 $dbh->do("INSERT INTO authorised_values (category,authorised_value,lib) VALUES ('BOR_NOTES','ADDR','Address Notes')");
3366 }
3367
3368 $dbh->do("INSERT INTO authorised_values (category,authorised_value,lib) VALUES ('LOC','CART','Book Cart')");
3369 $dbh->do("INSERT INTO authorised_values (category,authorised_value,lib) VALUES ('LOC','PROC','Processing Center')");
3370
3371 print "Upgrade to $DBversion done ( add defaults to authorized values for MANUAL_INV and BOR_NOTES and add new default LOC authorized values for shelf to cart processing )\n";
3372 SetVersion ($DBversion);
3373 }
3374
3375
3376 $DBversion = "3.01.00.105";
3377 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
3378 $dbh->do("
3379 CREATE TABLE `collections` (
3380 `colId` int(11) NOT NULL auto_increment,
3381 `colTitle` varchar(100) NOT NULL default '',
3382 `colDesc` text NOT NULL,
3383 `colBranchcode` varchar(4) default NULL COMMENT 'branchcode for branch where item should be held.',
3384 PRIMARY KEY (`colId`)
3385 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
3386 ");
3387
3388 $dbh->do("
3389 CREATE TABLE `collections_tracking` (
3390 `ctId` int(11) NOT NULL auto_increment,
3391 `colId` int(11) NOT NULL default '0' COMMENT 'collections.colId',
3392 `itemnumber` int(11) NOT NULL default '0' COMMENT 'items.itemnumber',
3393 PRIMARY KEY (`ctId`)
3394 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
3395 ");
3396 $dbh->do("
3397 INSERT INTO permissions (module_bit, code, description)
3398 VALUES ( 13, 'rotating_collections', 'Manage Rotating collections')" );
3399 print "Upgrade to $DBversion done (added collection and collection_tracking tables for rotating collections functionality)\n";
3400 SetVersion ($DBversion);
3401 }
3402 $DBversion = "3.01.00.106";
3403 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
3404 $dbh->do("INSERT INTO `systempreferences` (variable,value,options,explanation,type) VALUES ( 'OpacAddMastheadLibraryPulldown', '0', '', 'Adds a pulldown menu to select the library to search on the opac masthead.', 'YesNo' )");
3405 print "Upgrade to $DBversion done (added OpacAddMastheadLibraryPulldown system preferences)\n";
3406 SetVersion ($DBversion);
3407 }
3408
3409 $DBversion = '3.01.00.107';
3410 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
3411 my $upgrade_script = C4::Context->config("intranetdir") . "/installer/data/mysql/patroncards_upgrade.pl";
3412 system("perl $upgrade_script");
3413 print "Upgrade to $DBversion done (Migrated labels and patroncards tables and data to new schema.)\n";
3414 SetVersion ($DBversion);
3415 }
3416
3417 $DBversion = '3.01.00.108';
3418 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
3419 $dbh->do(qq{
3420 ALTER TABLE `export_format` ADD `csv_separator` VARCHAR( 2 ) NOT NULL AFTER `marcfields` ,
3421 ADD `field_separator` VARCHAR( 2 ) NOT NULL AFTER `csv_separator` ,
3422 ADD `subfield_separator` VARCHAR( 2 ) NOT NULL AFTER `field_separator`
3423 });
3424 print "Upgrade to $DBversion done (added separators for csv export)\n";
3425 SetVersion ($DBversion);
3426 }
3427
3428 $DBversion = "3.01.00.109";
3429 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
3430 $dbh->do(qq{
3431 ALTER TABLE `export_format` ADD `encoding` VARCHAR(255) NOT NULL AFTER `subfield_separator`
3432 });
3433 print "Upgrade to $DBversion done (added encoding for csv export)\n";
3434 SetVersion ($DBversion);
3435 }
3436
3437 $DBversion = '3.01.00.110';
3438 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
3439 $dbh->do('ALTER TABLE `categories` ADD COLUMN `enrolmentperioddate` DATE NULL DEFAULT NULL AFTER `enrolmentperiod`');
3440 print "Upgrade to $DBversion done (Add enrolment period date support)\n";
3441 SetVersion ($DBversion);
3442 }
3443
3444 $DBversion = '3.01.00.111';
3445 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
3446 print "Upgrade to $DBversion done (mark DBrev for 3.2-alpha release)\n";
3447 SetVersion ($DBversion);
3448 }
3449
3450 $DBversion = '3.01.00.112';
3451 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
3452 $dbh->do("INSERT INTO `systempreferences` (variable,value,options,explanation,type) VALUES ('SpineLabelShowPrintOnBibDetails', '0', '', 'If turned on, a \"Print Label\" link will appear for each item on the bib details page in the staff interface.', 'YesNo');");
3453 print "Upgrade to $DBversion done ( added Show Spine Label Printer on Bib Items Details preferences )\n";
3454 SetVersion ($DBversion);
3455 }
3456
3457 $DBversion = '3.01.00.113';
3458 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
3459 my $value = C4::Context->preference("XSLTResultsDisplay");
3460 $dbh->do(
3461 "INSERT INTO systempreferences (variable,value,type)
3462 VALUES('OPACXSLTResultsDisplay',?,'YesNo')", {}, $value ? 1 : 0);
3463 $value = C4::Context->preference("XSLTDetailsDisplay");
3464 $dbh->do(
3465 "INSERT INTO systempreferences (variable,value,type)
3466 VALUES('OPACXSLTDetailsDisplay',?,'YesNo')", {}, $value ? 1 : 0);
3467 print "Upgrade to $DBversion done (added two new syspref: OPACXSLTResultsDisplay and OPACXSLTDetailDisplay). You may have to go in Admin > System preference to tweak XSLT related syspref both in OPAC and Search tabs.\n";
3468 SetVersion ($DBversion);
3469 }
3470
3471 $DBversion = '3.01.00.114';
3472 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
3473 $dbh->do("INSERT INTO systempreferences (variable,value,explanation,options,type)VALUES('AutoSelfCheckAllowed', '0', 'For corporate and special libraries which want web-based self-check available from any PC without the need for a manual staff login. Most libraries will want to leave this turned off. If on, requires self-check ID and password to be entered in AutoSelfCheckID and AutoSelfCheckPass sysprefs.', '', 'YesNo')");
3474 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('AutoSelfCheckID','','Staff ID with circulation rights to be used for automatic web-based self-check. Only applies if AutoSelfCheckAllowed syspref is turned on.','','free')");
3475 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('AutoSelfCheckPass','','Password to be used for automatic web-based self-check. Only applies if AutoSelfCheckAllowed syspref is turned on.','','free')");
3476 print "Upgrade to $DBversion done ( Added AutoSelfCheckAllowed, AutoSelfCheckID, and AutoShelfCheckPass system preference )\n";
3477 SetVersion ($DBversion);
3478 }
3479
3480 $DBversion = '3.01.00.115';
3481 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
3482 $dbh->do('UPDATE aqorders SET quantityreceived = 0 WHERE quantityreceived IS NULL');
3483 $dbh->do('ALTER TABLE aqorders MODIFY COLUMN quantityreceived smallint(6) NOT NULL DEFAULT 0');
3484 print "Upgrade to $DBversion done ( Default aqorders.quantityreceived to 0 )\n";
3485 SetVersion ($DBversion);
3486 }
3487
3488 $DBversion = '3.01.00.116';
3489 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
3490 if (C4::Context->preference('OrderPdfFormat') eq 'pdfformat::example'){
3491 $dbh->do("UPDATE `systempreferences` set value='pdfformat::layout2pages' WHERE variable='OrderPdfFormat'");
3492 }
3493 print "Upgrade to $DBversion done (corrected default OrderPdfFormat value if still set wrong )\n";
3494 SetVersion ($DBversion);
3495 }
3496
3497 $DBversion = '3.01.00.117';
3498 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
3499 $dbh->do("UPDATE language_rfc4646_to_iso639 SET iso639_2_code = 'por' WHERE rfc4646_subtag='pt' ");
3500 print "Upgrade to $DBversion done (corrected ISO 639-2 language code for Portuguese)\n";
3501 SetVersion ($DBversion);
3502 }
3503
3504 $DBversion = '3.01.00.118';
3505 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
3506 my ($count) = $dbh->selectrow_array("SELECT count(*) FROM information_schema.columns
3507 WHERE table_name = 'aqbudgets_planning'
3508 AND column_name = 'display'");
3509 if ($count < 1) {
3510 $dbh->do("ALTER TABLE aqbudgets_planning ADD COLUMN display tinyint(1) DEFAULT 1");
3511 }
3512 print "Upgrade to $DBversion done (bug 4203: add display column to aqbudgets_planning if missing)\n";
3513 SetVersion ($DBversion);
3514 }
3515
3516 $DBversion = '3.01.00.119';
3517 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
3518 eval{require Locale::Currency::Format};
3519 if (!$@) {
3520 print "Upgrade to $DBversion done (Locale::Currency::Format installed.)\n";
3521 SetVersion ($DBversion);
3522 }
3523 else {
3524 print "Upgrade to $DBversion done.\n";
3525 print "NOTICE: The Locale::Currency::Format package is not installed on your system or not found in \@INC.\nThis dependency is required in order to include fine information in overdue notices.\nPlease ask your system administrator to install this package.\n";
3526 SetVersion ($DBversion);
3527 }
3528 }
3529
3530 $DBversion = '3.01.00.120';
3531 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
3532 $dbh->do(q{
3533 INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('soundon','0','Enable circulation sounds during checkin and checkout in the staff interface. Not supported by all web browsers yet.','','YesNo');
3534 });
3535 print "Upgrade to $DBversion done (bug 1080: add soundon system preference for circulation sounds)\n";
3536 SetVersion ($DBversion);
3537 }
3538
3539 $DBversion = '3.01.00.121';
3540 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
3541 $dbh->do("ALTER TABLE `reserves` ADD `expirationdate` DATE DEFAULT NULL");
3542 $dbh->do("ALTER TABLE `reserves` ADD `lowestPriority` tinyint(1) NOT NULL");
3543 $dbh->do("ALTER TABLE `old_reserves` ADD `expirationdate` DATE DEFAULT NULL");
3544 $dbh->do("ALTER TABLE `old_reserves` ADD `lowestPriority` tinyint(1) NOT NULL");
3545 print "Upgrade to $DBversion done ( Added Additional Fields to Reserves tables )\n";
3546 SetVersion ($DBversion);
3547 }
3548
3549 $DBversion = '3.01.00.122';
3550 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
3551 $dbh->do(q{
3552 INSERT INTO systempreferences (variable,value,explanation,options,type)
3553 VALUES ('OAI-PMH:ConfFile', '', 'If empty, Koha OAI Server operates in normal mode, otherwise it operates in extended mode.','','File');
3554 });
3555 print "Upgrade to $DBversion done. — Add a new system preference OAI-PMF:ConfFile\n";
3556 SetVersion ($DBversion);
3557 }
3558
3559 $DBversion = "3.01.00.123";
3560 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
3561 $dbh->do("INSERT INTO `permissions` (`module_bit`, `code`, `description`) VALUES
3562 (6, 'place_holds', 'Place holds for patrons')");
3563 $dbh->do("INSERT INTO `permissions` (`module_bit`, `code`, `description`) VALUES
3564 (6, 'modify_holds_priority', 'Modify holds priority')");
3565 $dbh->do("UPDATE `userflags` SET `flagdesc` = 'Place and modify holds for patrons' WHERE `flag` = 'reserveforothers'");
3566 print "Upgrade to $DBversion done (Add granular permission for holds modification and update description of reserveforothers permission)\n";
3567 SetVersion ($DBversion);
3568 }
3569
3570 $DBversion = '3.01.00.124';
3571 if (C4::Context->preference('Version') < TransformToNum($DBversion)){
3572 $dbh->do("
3573 INSERT INTO `letter` (module, code, name, title, content) VALUES('reserves', 'HOLDPLACED', 'Hold Placed on Item', 'Hold Placed on Item','A hold has been placed on the following item : <<title>> (<<biblionumber>>) by the user <<firstname>> <<surname>> (<<cardnumber>>).');
3574 ");
3575 print "Upgrade to $DBversion done (bug 3242: add HOLDPLACED letter template, which is used when emailLibrarianWhenHoldIsPlaced is enabled)\n";
3576 SetVersion ($DBversion);
3577 }
3578
3579 $DBversion = '3.01.00.125';
3580 if (C4::Context->preference('Version') < TransformToNum($DBversion)){
3581 $dbh->do("
3582 INSERT INTO `systempreferences` ( `variable` , `value` , `options` , `explanation` , `type` ) VALUES ( 'PrintNoticesMaxLines', '0', '', 'If greater than 0, sets the maximum number of lines an overdue notice will print. If the number of items is greater than this number, the notice will end with a warning asking the borrower to check their online account for a full list of overdue items.', 'Integer' );
3583 ");
3584 $dbh->do("
3585 INSERT INTO message_transport_types (message_transport_type) values ('print');
3586 ");
3587 print "Upgrade to $DBversion done (bug 3482: Printable hold and overdue notices)\n";
3588 SetVersion ($DBversion);
3589 }
3590
3591 $DBversion = "3.01.00.126";
3592 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
3593 $dbh->do("INSERT INTO systempreferences (variable,value,explanation,options,type) VALUES ('ILS-DI','0','Enable ILS-DI services. See http://your.opac.name/cgi-bin/koha/ilsdi.pl for online documentation.','','YesNo')");
3594 $dbh->do("INSERT INTO systempreferences (variable,value,explanation,options,type) VALUES ('ILS-DI:AuthorizedIPs','127.0.0.1','A comma separated list of IP addresses authorized to access the web services.','','free')");
3595
3596 print "Upgrade to $DBversion done (Adding ILS-DI updates and ILS-DI:AuthorizedIPs)\n";
3597 SetVersion ($DBversion);
3598 }
3599
3600 $DBversion = '3.01.00.127';
3601 if (C4::Context->preference('Version') < TransformToNum($DBversion)){
3602 $dbh->do("ALTER TABLE messages CHANGE branchcode branchcode varchar(10);");
3603 print "Upgrade to $DBversion done (bug 4190: messages in patron account did not work with branchcodes > 4)\n";
3604 SetVersion ($DBversion);
3605 }
3606
3607 $DBversion = '3.01.00.128';
3608 if (C4::Context->preference('Version') < TransformToNum($DBversion)){
3609 $dbh->do('CREATE INDEX budget_id ON aqorders (budget_id );');
3610 print "Upgrade to $DBversion done (bug 4331: index orders by budget_id)\n";
3611 SetVersion ($DBversion);
3612 }
3613
3614 $DBversion = "3.01.00.129";
3615 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
3616 $dbh->do("UPDATE `permissions` SET `code` = 'items_batchdel' WHERE `permissions`.`module_bit` =13 AND `permissions`.`code` = 'batchdel' LIMIT 1 ;");
3617 $dbh->do("UPDATE `permissions` SET `code` = 'items_batchmod' WHERE `permissions`.`module_bit` =13 AND `permissions`.`code` = 'batchmod' LIMIT 1 ;");
3618 print "Upgrade to $DBversion done (Change permissions names for item batch modification / deletion)\n";
3619
3620 SetVersion ($DBversion);
3621 }
3622
3623 $DBversion = "3.01.00.130";
3624 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
3625 $dbh->do("UPDATE reserves SET expirationdate = NULL WHERE expirationdate = '0000-00-00'");
3626 print "Upgrade to $DBversion done (change reserves.expirationdate values of 0000-00-00 to NULL (bug 1532)\n";
3627 SetVersion ($DBversion);
3628 }
3629
3630 $DBversion = "3.01.00.131";
3631 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
3632 $dbh->do(q{
3633 INSERT IGNORE INTO message_transport_types (message_transport_type) VALUES ('print'),('feed');
3634 });
3635 print "Upgrade to $DBversion done (adding print and feed message transport types)\n";
3636 SetVersion ($DBversion);
3637 }
3638
3639 $DBversion = "3.01.00.132";
3640 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
3641 $dbh->do(q{
3642 ALTER TABLE language_descriptions ADD INDEX subtag_type_lang (subtag, type, lang);
3643 });
3644 print "Upgrade to $DBversion done (Adding index to language_descriptions table)\n";
3645 SetVersion ($DBversion);
3646 }
3647
3648 $DBversion = '3.01.00.133';
3649 if (C4::Context->preference('Version') < TransformToNum($DBversion)){
3650 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES ('OverduesBlockCirc','noblock','When checking out an item should overdues block checkout, generate a confirmation dialogue, or allow checkout','noblock|confirmation|block','Choice')");
3651 print "Upgrade to $DBversion done (bug 4405: added OverduesBlockCirc syspref to control whether circulation is blocked if a borrower has overdues)\n";
3652 SetVersion ($DBversion);
3653 }
3654
3655 $DBversion = '3.01.00.134';
3656 if (C4::Context->preference('Version') < TransformToNum($DBversion)){
3657 $dbh->do("INSERT INTO systempreferences (variable,value,explanation,options,type) VALUES ('DisplayMultiPlaceHold','1','Display the ability to place multiple holds or not','','YesNo')");
3658 print "Upgrade to $DBversion done (adding syspref DisplayMultiPlaceHold to control whether multiple holds can be placed from the search results page)\n";
3659 SetVersion ($DBversion);
3660 }
3661
3662 $DBversion = '3.01.00.135';
3663 if (C4::Context->preference('Version') < TransformToNum($DBversion)){
3664 $dbh->do("
3665 INSERT INTO `letter` (module, code, name, title, content) VALUES
3666 ('reserves', 'HOLD_PRINT', 'Hold Available for Pickup (print notice)', 'Hold Available for Pickup (print notice)', '<<branches.branchname>>\r\n<<branches.branchaddress1>>\r\n<<branches.branchaddress2>>\r\n\r\n\r\nChange Service Requested\r\n\r\n\r\n\r\n\r\n\r\n\r\n\r\n<<borrowers.firstname>> <<borrowers.surname>>\r\n<<borrowers.address>>\r\n<<borrowers.city>> <<borrowers.zipcode>>\r\n\r\n\r\n\r\n\r\n\r\n\r\n\r\n\r\n\r\n\r\n<<borrowers.firstname>> <<borrowers.surname>> <<borrowers.cardnumber>>\r\n\r\nYou have a hold available for pickup as of <<reserves.waitingdate>>:\r\n\r\nTitle: <<biblio.title>>\r\nAuthor: <<biblio.author>>\r\nCopy: <<items.copynumber>>\r\n')
3667 ");
3668 print "Upgrade to $DBversion done (bug 4377: added HOLD_PRINT message template)\n";
3669 SetVersion ($DBversion);
3670 }
3671
3672 $DBversion = '3.01.00.136';
3673 if (C4::Context->preference('Version') < TransformToNum($DBversion)){
3674 $dbh->do(qq{
3675 INSERT INTO permissions (module_bit, code, description) VALUES
3676 ( 9, 'edit_items', 'Edit Items');});
3677 print "Upgrade to $DBversion done (Adding a new permission to edit items)\n";
3678 SetVersion ($DBversion);
3679 }
3680
3681 $DBversion = "3.01.00.137";
3682 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
3683 $dbh->do("
3684 INSERT INTO permissions (module_bit, code, description) VALUES
3685 (15, 'check_expiration', 'Check the expiration of a serial'),
3686 (15, 'claim_serials', 'Claim missing serials'),
3687 (15, 'create_subscription', 'Create a new subscription'),
3688 (15, 'delete_subscription', 'Delete an existing subscription'),
3689 (15, 'edit_subscription', 'Edit an existing subscription'),
3690 (15, 'receive_serials', 'Serials receiving'),
3691 (15, 'renew_subscription', 'Renew a subscription'),
3692 (15, 'routing', 'Routing');
3693 ");
3694 print "Upgrade to $DBversion done (adding granular permissions for serials)\n";
3695 SetVersion ($DBversion);
3696 }
3697
3698 $DBversion = "3.01.00.138";
3699 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
3700 $dbh->do("DELETE FROM systempreferences WHERE variable = 'GranularPermissions'");
3701 print "Upgrade to $DBversion done (bug 4896: removing GranularPermissions syspref; use of granular permissions is now the default)\n";
3702 SetVersion ($DBversion);
3703 }
3704
3705 $DBversion = '3.01.00.139';
3706 if (C4::Context->preference('Version') < TransformToNum($DBversion)){
3707 $dbh->do("ALTER TABLE message_attributes CHANGE message_name message_name varchar(40);");
3708 print "Upgrade to $DBversion done (bug 3682: change message_name from varchar(20) to varchar(40))\n";
3709 SetVersion ($DBversion);
3710 }
3711
3712 $DBversion = '3.01.00.140';
3713 if (C4::Context->preference('Version') < TransformToNum($DBversion)){
3714 $dbh->do("UPDATE systempreferences SET value = '0' WHERE variable = 'TagsModeration' AND value is NULL");
3715 print "Upgrade to $DBversion done (bug 4312 TagsModeration changed from NULL to 0)\n";
3716 SetVersion ($DBversion);
3717 }
3718
3719 $DBversion = '3.01.00.141';
3720 if (C4::Context->preference('Version') < TransformToNum($DBversion)){
3721 $dbh->do(qq{DELETE FROM message_attributes WHERE message_attribute_id=3;});
3722 $dbh->do(qq{DELETE FROM letter WHERE code='EVENT' AND title='Upcoming Library Event';});
3723 print "Upgrade to $DBversion done Remove upcoming events messaging option (bug 2434)\n";
3724 SetVersion ($DBversion);
3725 }
3726
3727 $DBversion = '3.01.00.142';
3728 if (C4::Context->preference('Version') < TransformToNum($DBversion)){
3729 $dbh->do(qq{DELETE FROM message_transports WHERE message_attribute_id=3;});
3730 print "Upgrade to $DBversion done (Remove upcoming events messaging option part 2 (bug 2434))\n";
3731 SetVersion ($DBversion);
3732 }
3733
3734 $DBversion = '3.01.00.143';
3735 if (C4::Context->preference('Version') < TransformToNum($DBversion)){
3736 $dbh->do(qq{CREATE INDEX auth_value_idx ON authorised_values (authorised_value)});
3737 $dbh->do(qq{CREATE INDEX auth_val_cat_idx ON borrower_attribute_types (authorised_value_category)});
3738 print "Upgrade to $DBversion done (Create index on authorised_values and borrower_attribute_types (bug 4139))\n";
3739 SetVersion ($DBversion);
3740 }
3741
3742 $DBversion = '3.01.00.144';
3743 if (C4::Context->preference('Version') < TransformToNum($DBversion)){
3744 $dbh->do(qq{UPDATE systempreferences SET value='normal' where value='default' and variable='IntranetBiblioDefaultView'});
3745 print "Upgrade to $DBversion done (Update the 'default' to 'normal' for the IntranetBiblioDefaultView syspref (bug 5007))\n";
3746 SetVersion ($DBversion);
3747 }
3748
3749 $DBversion = "3.01.00.145";
3750 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
3751 $dbh->do("ALTER TABLE borrowers ADD KEY `guarantorid` (guarantorid);");
3752 print "Upgrade to $DBversion done (Add index on guarantorid)\n";
3753 SetVersion ($DBversion);
3754 }
3755
3756 $DBversion = '3.01.00.999';
3757 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
3758 print "Upgrade to $DBversion done (3.2.0 release candidate)\n";
3759 SetVersion ($DBversion);
3760 }
3761
3762 $DBversion = "3.02.00.000";
3763 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
3764 my $value = $dbh->selectrow_array("SELECT value FROM systempreferences WHERE variable = 'HomeOrHoldingBranch'");
3765 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('HomeOrHoldingBranchReturn','$value','Used by Circulation to determine which branch of an item to check checking-in items','holdingbranch|homebranch','Choice');");
3766 print "Upgrade to $DBversion done (Add HomeOrHoldingBranchReturn system preference)\n";
3767 SetVersion ($DBversion);
3768 }
3769
3770 $DBversion = "3.02.00.001";
3771 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
3772 $dbh->do(q{DELETE FROM systempreferences WHERE variable IN (
3773 'holdCancelLength',
3774 'PINESISBN',
3775 'sortbynonfiling',
3776 'TemplateEncoding',
3777 'OPACSubscriptionDisplay',
3778 'OPACDisplayExtendedSubInfo',
3779 'OAI-PMH:Set',
3780 'OAI-PMH:Subset',
3781 'libraryAddress',
3782 'kohaspsuggest',
3783 'OrderPdfTemplate',
3784 'marc',
3785 'acquisitions',
3786 'MIME')
3787 }
3788 );
3789 print "Upgrade to $DBversion done (bug 3756: remove disused system preferences)\n";
3790 SetVersion ($DBversion);
3791 }
3792
3793 $DBversion = "3.02.00.002";
3794 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
3795 $dbh->do(q{DELETE FROM systempreferences WHERE variable = 'OpacPrivacy'});
3796 print "Upgrade to $DBversion done (bug 3881: remove unused OpacPrivacy system preference)\n";
3797 SetVersion ($DBversion);
3798 }
3799
3800 $DBversion = "3.02.00.003";
3801 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
3802 $dbh->do(q{UPDATE systempreferences SET variable = 'ILS-DI:AuthorizedIPs' WHERE variable = 'ILS-DI:Authorized_IPs'});
3803 print "Upgrade to $DBversion done (correct ILS-DI:AuthorizedIPs)\n";
3804 SetVersion ($DBversion);
3805 }
3806
3807 $DBversion = "3.02.00.004";
3808 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
3809 print "Upgrade to $DBversion done (3.2.0 general release)\n";
3810 SetVersion ($DBversion);
3811 }
3812
3813 # This is the point where 3.2.x and master diverged, we can use $original_version to make sure we don't
3814 # apply updates that have already been done
3815
3816 $DBversion = "3.03.00.001";
3817 if (C4::Context->preference("Version") < TransformToNum($DBversion) && $original_version < TransformToNum("3.02.00.005")) {
3818 $dbh->do("DELETE FROM subscriptionroutinglist WHERE borrowernumber IS NULL;");
3819 $dbh->do("ALTER TABLE subscriptionroutinglist MODIFY COLUMN `borrowernumber` int(11) NOT NULL;");
3820 $dbh->do("DELETE FROM subscriptionroutinglist WHERE subscriptionid IS NULL;");
3821 $dbh->do("ALTER TABLE subscriptionroutinglist MODIFY COLUMN `subscriptionid` int(11) NOT NULL;");
3822 $dbh->do("CREATE TEMPORARY TABLE del_subscriptionroutinglist
3823 SELECT s1.routingid FROM subscriptionroutinglist s1
3824 WHERE EXISTS (SELECT * FROM subscriptionroutinglist s2
3825 WHERE s2.borrowernumber = s1.borrowernumber
3826 AND s2.subscriptionid = s1.subscriptionid
3827 AND s2.routingid < s1.routingid);");
3828 $dbh->do("DELETE FROM subscriptionroutinglist
3829 WHERE routingid IN (SELECT routingid FROM del_subscriptionroutinglist);");
3830 $dbh->do("ALTER TABLE subscriptionroutinglist ADD UNIQUE (subscriptionid, borrowernumber);");
3831 $dbh->do("ALTER TABLE subscriptionroutinglist
3832 ADD CONSTRAINT `subscriptionroutinglist_ibfk_1` FOREIGN KEY (`borrowernumber`)
3833 REFERENCES `borrowers` (`borrowernumber`)
3834 ON DELETE CASCADE ON UPDATE CASCADE");
3835 $dbh->do("ALTER TABLE subscriptionroutinglist
3836 ADD CONSTRAINT `subscriptionroutinglist_ibfk_2` FOREIGN KEY (`subscriptionid`)
3837 REFERENCES `subscription` (`subscriptionid`)
3838 ON DELETE CASCADE ON UPDATE CASCADE");
3839 print "Upgrade to $DBversion done (Make subscriptionroutinglist more strict)\n";
3840 SetVersion ($DBversion);
3841 }
3842
3843 $DBversion = '3.03.00.002';
3844 if (C4::Context->preference("Version") < TransformToNum($DBversion) && $original_version < TransformToNum("3.02.00.006")) {
3845 $dbh->do("UPDATE language_rfc4646_to_iso639 SET iso639_2_code='arm' WHERE rfc4646_subtag='hy';");
3846 $dbh->do("UPDATE language_rfc4646_to_iso639 SET iso639_2_code='eng' WHERE rfc4646_subtag='en';");
3847 $dbh->do("INSERT INTO language_rfc4646_to_iso639(rfc4646_subtag,iso639_2_code) VALUES( 'fi','fin');");
3848 $dbh->do("UPDATE language_rfc4646_to_iso639 SET iso639_2_code='fre' WHERE rfc4646_subtag='fr';");
3849 $dbh->do("INSERT INTO language_rfc4646_to_iso639(rfc4646_subtag,iso639_2_code) VALUES( 'lo','lao');");
3850 $dbh->do("UPDATE language_rfc4646_to_iso639 SET iso639_2_code='ita' WHERE rfc4646_subtag='it';");
3851 $dbh->do("INSERT INTO language_rfc4646_to_iso639(rfc4646_subtag,iso639_2_code) VALUES( 'sr','srp');");
3852 $dbh->do("INSERT INTO language_rfc4646_to_iso639(rfc4646_subtag,iso639_2_code) VALUES( 'tet','tet');");
3853 $dbh->do("INSERT INTO language_rfc4646_to_iso639(rfc4646_subtag,iso639_2_code) VALUES( 'ur','urd');");
3854
3855 print "Upgrade to $DBversion done (Correct language mappings)\n";
3856 SetVersion ($DBversion);
3857 }
3858
3859 $DBversion = '3.03.00.003';
3860 if (C4::Context->preference("Version") < TransformToNum($DBversion) && $original_version < TransformToNum("3.02.00.007")) {
3861 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('UseTablesortForCirc','0','If on, use the JQuery tablesort function on the list of current borrower checkouts on the circulation page. Note that the use of this function may slow down circ for patrons with may checkouts.','','YesNo');");
3862 print "Upgrade to $DBversion done (Add UseTablesortForCirc syspref)\n";
3863 SetVersion ($DBversion);
3864 }
3865
3866 $DBversion = '3.03.00.004';
3867 if (C4::Context->preference("Version") < TransformToNum($DBversion) && $original_version < TransformToNum("3.02.01.001")) {
3868 my $count = $dbh->selectrow_array('SELECT COUNT(*) FROM letter WHERE module = ? AND code = ?', {}, 'suggestions', 'ACCEPTED');
3869 $dbh->do(q/
3870 INSERT INTO `letter`
3871 (module, code, name, title, content)
3872 VALUES
3873 ('suggestions','ACCEPTED','Suggestion accepted', 'Purchase suggestion accepted','Dear <<borrowers.firstname>> <<borrowers.surname>>,\n\nYou have suggested that the library acquire <<suggestions.title>> by <<suggestions.author>>.\n\nThe library has reviewed your suggestion today. The item will be ordered as soon as possible. You will be notified by mail when the order is completed, and again when the item arrives at the library.\n\nIf you have any questions, please email us at <<branches.branchemail>>.\n\nThank you,\n\n<<branches.branchname>>')
3874 /) unless $count > 0;
3875 $count = $dbh->selectrow_array('SELECT COUNT(*) FROM letter WHERE module = ? AND code = ?', {}, 'suggestions', 'AVAILABLE');
3876 $dbh->do(q/
3877 INSERT INTO `letter`
3878 (module, code, name, title, content)
3879 VALUES
3880 ('suggestions','AVAILABLE','Suggestion available', 'Suggested purchase available','Dear <<borrowers.firstname>> <<borrowers.surname>>,\n\nYou have suggested that the library acquire <<suggestions.title>> by <<suggestions.author>>.\n\nWe are pleased to inform you that the item you requested is now part of the collection.\n\nIf you have any questions, please email us at <<branches.branchemail>>.\n\nThank you,\n\n<<branches.branchname>>')
3881 /) unless $count > 0;
3882 $count = $dbh->selectrow_array('SELECT COUNT(*) FROM letter WHERE module = ? AND code = ?', {}, 'suggestions', 'ORDERED');
3883 $dbh->do(q/
3884 INSERT INTO `letter`
3885 (module, code, name, title, content)
3886 VALUES
3887 ('suggestions','ORDERED','Suggestion ordered', 'Suggested item ordered','Dear <<borrowers.firstname>> <<borrowers.surname>>,\n\nYou have suggested that the library acquire <<suggestions.title>> by <<suggestions.author>>.\n\nWe are pleased to inform you that the item you requested has now been ordered. It should arrive soon, at which time it will be processed for addition into the collection.\n\nYou will be notified again when the book is available.\n\nIf you have any questions, please email us at <<branches.branchemail>>\n\nThank you,\n\n<<branches.branchname>>')
3888 /) unless $count > 0;
3889 $count = $dbh->selectrow_array('SELECT COUNT(*) FROM letter WHERE module = ? AND code = ?', {}, 'suggestions', 'REJECTED');
3890 $dbh->do(q/
3891 INSERT INTO `letter`
3892 (module, code, name, title, content)
3893 VALUES
3894 ('suggestions','REJECTED','Suggestion rejected', 'Purchase suggestion declined','Dear <<borrowers.firstname>> <<borrowers.surname>>,\n\nYou have suggested that the library acquire <<suggestions.title>> by <<suggestions.author>>.\n\nThe library has reviewed your request today, and has decided not to accept the suggestion at this time.\n\nThe reason given is: <<suggestions.reason>>\n\nIf you have any questions, please email us at <<branches.branchemail>>.\n\nThank you,\n\n<<branches.branchname>>')
3895 /) unless $count > 0;
3896 print "Upgrade to $DBversion done (bug 5127: add default templates for suggestion status change notifications)\n";
3897 SetVersion ($DBversion);
3898 };
3899
3900 $DBversion = '3.03.00.005';
3901 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
3902 $dbh->do("update `systempreferences` set options='whitespace|T-prefix|cuecat|libsuite8' where variable='itemBarcodeInputFilter'");
3903 print "Upgrade to $DBversion done (Add itemBarcodeInputFilter choice libsuite8)\n";
3904 }
3905
3906 $DBversion = '3.03.00.006';
3907 if (C4::Context->preference("Version") < TransformToNum($DBversion) && $original_version < TransformToNum("3.02.01.002")) {
3908 $dbh->do("ALTER TABLE deletedborrowers ADD `privacy` int(11) AFTER smsalertnumber;");
3909 $dbh->do("ALTER TABLE deletedborrowers CHANGE `cardnumber` `cardnumber` varchar(16);");
3910 print "Upgrade to $DBversion done (Fix differences between borrowers and deletedborrowers)\n";
3911 SetVersion ($DBversion);
3912 }
3913
3914 $DBversion = '3.03.00.007';
3915 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
3916 $dbh->do("ALTER table suggestions ADD quantity SMALLINT(6) default NULL,
3917 ADD currency VARCHAR(3) default NULL,
3918 ADD price DECIMAL(28,6) default NULL,
3919 ADD total DECIMAL(28,6) default NULL;
3920 ");
3921 print "Upgrade to $DBversion done (Added acq related columns to suggestions)\n";
3922 SetVersion ($DBversion);
3923 }
3924
3925 $DBversion = '3.03.00.008';
3926 if (C4::Context->preference('Version') < TransformToNum($DBversion)){
3927 $dbh->do("INSERT INTO systempreferences (variable,value,explanation,options,type) VALUES ('OPACNoResultsFound','','Display this HTML when no results are found for a search in the OPAC','70|10','Textarea')");
3928 print "Upgrade to $DBversion done (adding syspref OPACNoResultsFound to control what displays when no results are found for a search in the OPAC.)\n";
3929 SetVersion ($DBversion);
3930 }
3931
3932 $DBversion = '3.03.00.009';
3933 if (C4::Context->preference("Version") < TransformToNum($DBversion) && $original_version < TransformToNum("3.02.01.003")) {
3934 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('IntranetUserCSS','','Add CSS to be included in the Intranet',NULL,'free')");
3935 print "Upgrade to $DBversion done (Add IntranetUserCSS syspref)\n";
3936 SetVersion ($DBversion);
3937 }
3938
3939 $DBversion = "3.03.00.010";
3940 if (C4::Context->preference("Version") < TransformToNum($DBversion) && $original_version < TransformToNum("3.02.02.001")) {
3941 $dbh->do("UPDATE `marc_subfield_structure` SET liblibrarian = 'Distance from earth' WHERE liblibrarian = 'Distrance from earth' AND tagfield = '034' AND tagsubfield = 'r';");
3942 $dbh->do("UPDATE `marc_subfield_structure` SET libopac = 'Distance from earth' WHERE libopac = 'Distrance from earth' AND tagfield = '034' AND tagsubfield = 'r';");
3943 print "Upgrade to $DBversion done (Fix misspelled 034r subfield in MARC21 Frameworks)\n";
3944 SetVersion ($DBversion);
3945 }
3946
3947 $DBversion = "3.03.00.011";
3948 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
3949 $dbh->do("UPDATE aqbooksellers SET gstrate=NULL WHERE gstrate=0.0");
3950 print "Upgrade to $DBversion done (Bug 5186: allow GST rate to be set to 0)\n";
3951 SetVersion ($DBversion);
3952 }
3953
3954 $DBversion = "3.03.00.012";
3955 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
3956 $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')");
3957 print "Upgrade to $DBversion done (Bug 2142: maxItemsInSearchResults syspref resurrected)\n";
3958 SetVersion ($DBversion);
3959 }
3960
3961 $DBversion = "3.03.00.013";
3962 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
3963 $dbh->do("INSERT INTO systempreferences (variable,value,explanation,options,type) VALUES ('OpacPublic','1','If set to OFF and user is not logged in, all OPAC pages require authentication, and OPAC searchbar is removed)','','YesNo')");
3964 print "Upgrade to $DBversion done (added 'OpacPublic' syspref)\n";
3965 SetVersion ($DBversion);
3966 }
3967
3968 $DBversion = "3.03.00.014";
3969 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
3970 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES ('ShelfBrowserUsesLocation','1','Use the item location when finding items for the shelf browser.','1','YesNo')");
3971 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES ('ShelfBrowserUsesHomeBranch','1','Use the item home branch when finding items for the shelf browser.','1','YesNo')");
3972 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES ('ShelfBrowserUsesCcode','0','Use the item collection code when finding items for the shelf browser.','1','YesNo')");
3973 print "Upgrade to $DBversion done (Add flexible shelf browser constraints)\n";
3974 SetVersion ($DBversion);
3975 }
3976
3977 $DBversion = "3.03.00.015";
3978 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
3979 my $sth = $dbh->prepare("INSERT INTO `marc_subfield_structure` (`tagfield`, `tagsubfield`, `liblibrarian`, `libopac`, `repeatable`, `mandatory`, `kohafield`,
3980 `tab`, `authorised_value`, `authtypecode`, `value_builder`, `isurl`, `hidden`, `frameworkcode`, `seealso`, `link`, `defaultvalue`)
3981 VALUES ( ?, '9', '9 (RLIN)', '9 (RLIN)', 0, 0, '', 6, '', '', '', 0, -5, '', '', '', NULL)");
3982 $sth->execute('648');
3983 $sth->execute('654');
3984 $sth->execute('655');
3985 $sth->execute('656');
3986 $sth->execute('657');
3987 $sth->execute('658');
3988 $sth->execute('662');
3989 $sth->finish;
3990 print "Upgrade to $DBversion done (Bug 5619: Add subfield 9 to marc21 648,654,655,656,657,658,662)\n";
3991 SetVersion ($DBversion);
3992 }
3993
3994 $DBversion = '3.03.00.016';
3995 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
3996 # reimplement OpacPrivacy system preference
3997 $dbh->do("INSERT INTO systempreferences (variable,value,explanation,options,type) VALUES('OpacPrivacy', '0', 'if ON, allows patrons to define their privacy rules (reading history)',NULL,'YesNo')");
3998 $dbh->do("ALTER TABLE `borrowers` ADD `privacy` INTEGER NOT NULL DEFAULT 1;");
3999 $dbh->do("ALTER TABLE `deletedborrowers` ADD `privacy` INTEGER NOT NULL DEFAULT 1;");
4000 print "Upgrade to $DBversion done (OpacPrivacy reimplementation)\n";
4001 SetVersion($DBversion);
4002 };
4003
4004 $DBversion = '3.03.00.017';
4005 if (C4::Context->preference("Version") < TransformToNum($DBversion) && $original_version < TransformToNum("3.02.03.001")) {
4006 $dbh->do("ALTER TABLE `currency` CHANGE `rate` `rate` FLOAT( 15, 5 ) NULL DEFAULT NULL;");
4007 print "Upgrade to $DBversion done (Enable currency rates >= 100)\n";
4008 SetVersion ($DBversion);
4009 }
4010
4011 $DBversion = '3.03.00.018';
4012 if (C4::Context->preference("Version") < TransformToNum($DBversion) && $original_version < TransformToNum("3.02.03.002")) {
4013 $dbh->do( q|update language_descriptions set description = 'Nederlands' where lang = 'nl' and subtag = 'nl'|);
4014 $dbh->do( q|update language_descriptions set description = 'Dansk' where lang = 'da' and subtag = 'da'|);
4015 print "Upgrade to $DBversion done (Correct language descriptions)\n";
4016 SetVersion ($DBversion);
4017 }
4018
4019 $DBversion = '3.03.00.019';
4020 if (C4::Context->preference("Version") < TransformToNum($DBversion) && $original_version < TransformToNum("3.02.03.003")) {
4021 # Fix bokmål
4022 $dbh->do("UPDATE language_subtag_registry SET description = 'Norwegian bokm&#229;l' WHERE subtag = 'nb';");
4023 $dbh->do("INSERT INTO language_rfc4646_to_iso639(rfc4646_subtag,iso639_2_code) VALUES( 'nb','nob');");
4024 $dbh->do("UPDATE language_descriptions SET description = 'Norsk bokm&#229;l' WHERE subtag = 'nb' AND lang = 'nb';");
4025 $dbh->do("UPDATE language_descriptions SET description = 'Norwegian bokm&#229;l' WHERE subtag = 'nb' AND lang = 'en';");
4026 $dbh->do("UPDATE language_descriptions SET description = 'Norvégien bokm&#229;l' WHERE subtag = 'nb' AND lang = 'fr';");
4027 # Add nynorsk
4028 $dbh->do("INSERT INTO language_subtag_registry( subtag, type, description, added) VALUES ( 'nn', 'language', 'Norwegian nynorsk','2011-02-14' )");
4029 $dbh->do("INSERT INTO language_rfc4646_to_iso639(rfc4646_subtag,iso639_2_code) VALUES( 'nn','nno')");
4030 $dbh->do("INSERT INTO language_descriptions(subtag, type, lang, description) VALUES( 'nn', 'language', 'nb', 'Norsk nynorsk')");
4031 $dbh->do("INSERT INTO language_descriptions(subtag, type, lang, description) VALUES( 'nn', 'language', 'nn', 'Norsk nynorsk')");
4032 $dbh->do("INSERT INTO language_descriptions(subtag, type, lang, description) VALUES( 'nn', 'language', 'en', 'Norwegian nynorsk')");
4033 $dbh->do("INSERT INTO language_descriptions(subtag, type, lang, description) VALUES( 'nn', 'language', 'fr', 'Norvégien nynorsk')");
4034 print "Upgrade to $DBversion done (Correct language descriptions for Norwegian)\n";
4035 SetVersion ($DBversion);
4036 }
4037
4038 $DBversion = '3.03.00.020';
4039 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
4040 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES ('AllowFineOverride','0','If on, staff will be able to issue books to patrons with fines greater than noissuescharge.','0','YesNo')");
4041 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES ('AllFinesNeedOverride','1','If on, staff will be asked to override every fine, even if it is below noissuescharge.','0','YesNo')");
4042 print "Upgrade to $DBversion done (Bug 5811: Add sysprefs controlling overriding fines)\n";
4043 SetVersion($DBversion);
4044 };
4045
4046 $DBversion = '3.03.00.021';
4047 if (C4::Context->preference("Version") < TransformToNum($DBversion) && $original_version < TransformToNum("3.02.05.001")) {
4048 $dbh->do("ALTER TABLE items MODIFY enumchron TEXT");
4049 $dbh->do("ALTER TABLE deleteditems MODIFY enumchron TEXT");
4050 print "Upgrade to $DBversion done (bug 5642: longer serial enumeration)\n";
4051 SetVersion ($DBversion);
4052 }
4053
4054 $DBversion = '3.03.00.022';
4055 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
4056 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES ('AuthoritiesLog','0','If ON, log edit/create/delete actions on authorities.','','YesNo');");
4057 print "Upgrade to $DBversion done (Add AuthoritiesLog syspref)\n";
4058 SetVersion ($DBversion);
4059 }
4060
4061 # due to a mismatch in kohastructure.sql some koha will have missing columns in aqbasketgroup
4062 # this attempts to fix that
4063 $DBversion = '3.03.00.023';
4064 if (C4::Context->preference("Version") < TransformToNum($DBversion) && $original_version < TransformToNum("3.02.05.002")) {
4065 my $sth = $dbh->prepare("SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'aqbasketgroups' AND COLUMN_NAME = 'billingplace'");
4066 $sth->execute;
4067 $dbh->do("ALTER TABLE aqbasketgroups ADD billingplace VARCHAR(10)") if ! $sth->fetchrow_hashref;
4068 $sth = $dbh->prepare("SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'aqbasketgroups' AND COLUMN_NAME = 'deliveryplace'");
4069 $sth->execute;
4070 $dbh->do("ALTER TABLE aqbasketgroups ADD deliveryplace VARCHAR(10)") if ! $sth->fetchrow_hashref;
4071 $sth = $dbh->prepare("SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'aqbasketgroups' AND COLUMN_NAME = 'deliverycomment'");
4072 $sth->execute;
4073 $dbh->do("ALTER TABLE aqbasketgroups ADD deliverycomment VARCHAR(255)") if ! $sth->fetchrow_hashref;
4074 print "Upgrade to $DBversion done (Reconcile aqbasketgroups)\n";
4075 SetVersion ($DBversion);
4076 }
4077
4078 $DBversion = '3.03.00.024';
4079 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
4080 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES ('TraceCompleteSubfields','0','Force subject tracings to only match complete subfields.','0','YesNo')");
4081 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES ('UseAuthoritiesForTracings','1','Use authority record numbers for subject tracings instead of heading strings.','0','YesNo')");
4082 print "Upgrade to $DBversion done (Add syspref to force whole-subfield matching on subject tracings)\n";
4083 SetVersion($DBversion);
4084 };
4085
4086 $DBversion = "3.03.00.025";
4087 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
4088 $dbh->do("INSERT INTO systempreferences (variable,value,explanation,options,type) VALUES ('OPACAllowUserToChooseBranch', 1, 'Allow the user to choose the branch they want to pickup their hold from','1','YesNo')");
4089 print "Upgrade to $DBversion done (Add syspref to control if user can choose pickup branch for holds)\n";
4090 SetVersion ($DBversion);
4091 }
4092
4093 $DBversion = '3.03.00.026';
4094 if (C4::Context->preference("Version") < TransformToNum($DBversion) && $original_version < TransformToNum("3.02.05.003")) {
4095 $dbh->do("UPDATE `message_attributes` SET message_name='Item Due' WHERE message_attribute_id=1 AND message_name LIKE 'Item DUE'");
4096 print "Upgrade to $DBversion done ( fix capitalization in message type )\n";
4097 SetVersion ($DBversion);
4098 }
4099
4100 $DBversion = '3.03.00.027';
4101 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
4102 $dbh->do("INSERT INTO systempreferences (variable,value,explanation,options,type) VALUES('displayFacetCount', '0', NULL, NULL, 'YesNo')");
4103 $dbh->do("INSERT INTO systempreferences (variable,value,explanation,options,type) VALUES('maxRecordsForFacets', '20', NULL, NULL, 'Integer')");
4104 print "Upgrade to $DBversion done (Preferences for facet count)\n";
4105 SetVersion ($DBversion);
4106 }
4107
4108 $DBversion = "3.03.00.028";
4109 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
4110 $dbh->do("INSERT INTO systempreferences (variable,value,explanation,options,type) VALUES ('FacetLabelTruncationLength', 20, 'Truncate facets length to','','free')");
4111 print "Upgrade to $DBversion done (Add FacetLabelTruncationLength syspref to control facets displayed length)\n";
4112 SetVersion ($DBversion);
4113 }
4114
4115 $DBversion = "3.03.00.029";
4116 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
4117 $dbh->do("INSERT INTO systempreferences (variable,value,explanation,options,type) VALUES ('AllowPurchaseSuggestionBranchChoice', 0, 'Allow user to choose branch when making a purchase suggestion','1','YesNo')");
4118 print "Upgrade to $DBversion done (Add syspref to control if user can choose branch when making purchase suggestion)\n";
4119 SetVersion ($DBversion);
4120 }
4121
4122 $DBversion = "3.03.00.030";
4123 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
4124 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('OpacFavicon','','Enter a complete URL to an image to replace the default Koha favicon on the OPAC','','free')");
4125 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('IntranetFavicon','','Enter a complete URL to an image to replace the default Koha favicon on the Staff client','','free')");
4126 print "Upgrade to $DBversion done (Add sysprefs to control custom favicons)\n";
4127 SetVersion ($DBversion);
4128 }
4129
4130 $DBversion = "3.03.00.031";
4131 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
4132 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('FineNotifyAtCheckin',0,'If ON notify librarians of overdue fines on the items they are checking in.',NULL,'YesNo');");
4133 print "Upgrade to $DBversion done (Add syspref FineNotifyAtCheckin)\n";
4134 SetVersion ($DBversion);
4135 }
4136
4137 $DBversion = '3.03.00.032';
4138 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
4139 $dbh->do("INSERT INTO systempreferences (variable,value,explanation,options,type) VALUES ('TraceSubjectSubdivisions', 1, 'Create searches on all subdivisions for subject tracings.','1','YesNo')");
4140 print "Upgrade to $DBversion done ( include subdivisions when generating subject tracing searches )\n";
4141 }
4142
4143
4144 $DBversion = '3.03.00.033';
4145 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
4146 $dbh->do("INSERT INTO systempreferences (variable,value,explanation,options,type) VALUES('StaffAuthorisedValueImages', '1', '', NULL, 'YesNo')");
4147 print "Upgrade to $DBversion done (System pref StaffAuthorisedValueImages)\n";
4148 SetVersion ($DBversion);
4149 }
4150
4151 $DBversion = '3.03.00.034';
4152 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
4153 $dbh->do("ALTER TABLE `categories` ADD `hidelostitems` tinyint(1) NOT NULL default '0' AFTER `reservefee`");
4154 print "Upgrade to $DBversion done (Add hidelostitems preference to borrower categories)\n";
4155 SetVersion ($DBversion);
4156 }
4157
4158 $DBversion = '3.03.00.035';
4159 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
4160 $dbh->do("ALTER TABLE `issuingrules` ADD hardduedate date default NULL AFTER issuelength");
4161 $dbh->do("ALTER TABLE `issuingrules` ADD hardduedatecompare tinyint NOT NULL default 0 AFTER hardduedate");
4162 my $duedate;
4163 if (C4::Context->preference("globalDueDate")) {
4164 $duedate = C4::Dates::format_date_in_iso(C4::Context->preference("globalDueDate"));
4165 $dbh->do("UPDATE `issuingrules` SET hardduedate = '$duedate', hardduedatecompare = 0");
4166 } elsif (C4::Context->preference("ceilingDueDate")) {
4167 $duedate = C4::Dates::format_date_in_iso(C4::Context->preference("ceilingDueDate"));
4168 $dbh->do("UPDATE `issuingrules` SET hardduedate = '$duedate', hardduedatecompare = -1");
4169 }
4170 $dbh->do("DELETE FROM `systempreferences` WHERE variable = 'globalDueDate' OR variable = 'ceilingDueDate'");
4171 print "Upgrade to $DBversion done (Move global and ceiling due dates to Circ Rules level)\n";
4172 SetVersion ($DBversion);
4173 }
4174
4175 $DBversion = '3.03.00.036';
4176 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
4177 $dbh->do("INSERT INTO systempreferences (variable,value,explanation,options,type) VALUES ('COinSinOPACResults', 1, 'If ON, use COinS in OPAC search results page. NOTE: this can slow down search response time significantly','','YesNo')");
4178 print "Upgrade to $DBversion done ( Make COinS optional in OPAC search results )\n";
4179 SetVersion ($DBversion);
4180 }
4181
4182 $DBversion = '3.03.00.037';
4183 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
4184 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('OPACDisplay856uAsImage','OFF','Display the URI in the 856u field as an image, the corresponding OPACXSLT option must be on','OFF|Details|Results|Both','Choice')");
4185 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('Display856uAsImage','OFF','Display the URI in the 856u field as an image, the corresponding Staff Client XSLT option must be on','OFF|Details|Results|Both','Choice')");
4186 print "Upgrade to $DBversion done (Add 'Display856uAsImage' and 'OPACDisplay856uAsImage' syspref)\n";
4187 SetVersion ($DBversion);
4188 }
4189
4190 $DBversion = '3.03.00.038';
4191 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
4192 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES ('SelfCheckTimeout',120,'Define the number of seconds before the Web-based Self Checkout times out a patron','','Integer')");
4193 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES ('AllowSelfCheckReturns',0,'If enabled, patrons may return items through the Web-based Self Checkout','','YesNo')");
4194 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES ('SelfCheckHelpMessage','','Enter HTML to include under the basic Web-based Self Checkout instructions on the Help page','70|10','Textarea')");
4195 print "Upgrade to $DBversion done ( Add Self-checkout by Login system preferences )\n";
4196 }
4197
4198 $DBversion = "3.03.00.039";
4199 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
4200 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('ShowReviewer',1,'If ON, name of reviewer will be shown above comments in OPAC',NULL,'YesNo');");
4201 print "Upgrade to $DBversion done (Add syspref ShowReviewer)\n";
4202 }
4203
4204 $DBversion = "3.03.00.040";
4205 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
4206 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('UseControlNumber',0,'If ON, record control number (w subfields) and control number (001) are used for linking of bibliographic records.','','YesNo');");
4207 print "Upgrade to $DBversion done (Add syspref UseControlNumber)\n";
4208 }
4209
4210 $DBversion = "3.03.00.041";
4211 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
4212 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('AlternateHoldingsField','','The MARC field/subfield that contains alternate holdings information for bibs taht do not have items attached (e.g. 852abchi for libraries converting from MARC Magician).',NULL,'free')");
4213 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('AlternateHoldingsSeparator','','The string to use to separate subfields in alternate holdings displays.',NULL,'free')");
4214 print "Upgrade to $DBversion done (Add sysprefs to control alternate holdings information display)\n";
4215 SetVersion ($DBversion);
4216 }
4217
4218 $DBversion = '3.03.00.042';
4219 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
4220 stocknumber_checker();
4221 print "Upgrade to $DBversion done (5860 Index itemstocknumber)\n";
4222 SetVersion ($DBversion);
4223 }
4224
4225 sub stocknumber_checker { #code reused later on
4226 my @row;
4227 #drop the obsolete itemSStocknumber idx if it exists
4228 @row = $dbh->selectrow_array("SHOW INDEXES FROM items WHERE key_name='itemsstocknumberidx'");
4229 $dbh->do("ALTER TABLE `items` DROP INDEX `itemsstocknumberidx`;") if @row;
4230
4231 #check itemstocknumber idx; remove it if it is unique
4232 @row = $dbh->selectrow_array("SHOW INDEXES FROM items WHERE key_name='itemstocknumberidx' AND non_unique=0");
4233 $dbh->do("ALTER TABLE `items` DROP INDEX `itemstocknumberidx`;") if @row;
4234
4235 #add itemstocknumber index non-unique IF it still not exists
4236 @row = $dbh->selectrow_array("SHOW INDEXES FROM items WHERE key_name='itemstocknumberidx'");
4237 $dbh->do("ALTER TABLE items ADD INDEX itemstocknumberidx (stocknumber);") unless @row;
4238 }
4239
4240 $DBversion = "3.03.00.043";
4241 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
4242
4243 $dbh->do("INSERT INTO authorised_values (category,authorised_value,lib,lib_opac) VALUES ('YES_NO','0','No','No')");
4244 $dbh->do("INSERT INTO authorised_values (category,authorised_value,lib,lib_opac) VALUES ('YES_NO','1','Yes','Yes')");
4245
4246 print "Upgrade to $DBversion done ( add generic boolean YES_NO authorised_values pair )\n";
4247 SetVersion ($DBversion);
4248 }
4249
4250 $DBversion = '3.03.00.044';
4251 if ( C4::Context->preference("Version") < TransformToNum($DBversion) ) {
4252 $dbh->do("ALTER TABLE `aqbasketgroups` ADD `freedeliveryplace` TEXT NULL AFTER `deliveryplace`;");
4253 print "Upgrade to $DBversion done (adding freedeliveryplace to basketgroups)\n";
4254 }
4255
4256 $DBversion = '3.03.00.045';
4257 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
4258 #Remove obsolete columns from aqbooksellers if needed
4259 my $a = $dbh->selectall_hashref('SHOW columns from aqbooksellers','Field');
4260 my $sqldrop="ALTER TABLE aqbooksellers DROP COLUMN ";
4261 foreach(qw/deliverydays followupdays followupscancel invoicedisc nocalc specialty/) {
4262 $dbh->do($sqldrop.$_) if exists $a->{$_};
4263 }
4264 #Remove obsolete column from aqbudgets if needed
4265 #The correct column is budget_notes
4266 $a = $dbh->selectall_hashref('SHOW columns from aqbudgets','Field');
4267 if(exists $a->{budget_description}) {
4268 $dbh->do("ALTER TABLE aqbudgets DROP COLUMN budget_description");
4269 }
4270 print "Upgrade to $DBversion done (Remove obsolete columns from aqbooksellers and aqbudgets if needed)\n";
4271 SetVersion ($DBversion);
4272 }
4273
4274 $DBversion = "3.03.00.046";
4275 if ( C4::Context->preference("Version") < TransformToNum($DBversion) ) {
4276 $dbh->do("ALTER TABLE overduerules ALTER delay1 SET DEFAULT NULL, ALTER delay2 SET DEFAULT NULL, ALTER delay3 SET DEFAULT NULL");
4277 print "Upgrade to $DBversion done (Setting NULL default value for delayn columns in table overduerules)\n";
4278 SetVersion($DBversion);
4279 }
4280
4281 $DBversion = '3.03.00.047';
4282 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
4283 $dbh->do("ALTER TABLE borrowers ADD `state` mediumtext AFTER city;");
4284 $dbh->do("ALTER TABLE borrowers ADD `B_state` mediumtext AFTER B_city;");
4285 $dbh->do("ALTER TABLE borrowers ADD `altcontactstate` mediumtext AFTER altcontactaddress3;");
4286 $dbh->do("ALTER TABLE deletedborrowers ADD `state` mediumtext AFTER city;");
4287 $dbh->do("ALTER TABLE deletedborrowers ADD `B_state` mediumtext AFTER B_city;");
4288 $dbh->do("ALTER TABLE deletedborrowers ADD `altcontactstate` mediumtext AFTER altcontactaddress3;");
4289 print "Upgrade to $DBversion done (Add state field to patron's addresses)\n";
4290 }
4291
4292 $DBversion = '3.03.00.048';
4293 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
4294 $dbh->do("ALTER TABLE branches ADD `branchstate` mediumtext AFTER `branchcity`;");
4295 print "Upgrade to $DBversion done (Add state to branch address)\n";
4296 SetVersion ($DBversion);
4297 }
4298
4299 $DBversion = '3.03.00.049';
4300 if ( C4::Context->preference("Version") < TransformToNum($DBversion) ) {
4301 $dbh->do("ALTER TABLE `accountlines` ADD `note` text NULL default NULL");
4302 $dbh->do("ALTER TABLE `accountlines` ADD `manager_id` int( 11 ) NULL ");
4303 print "Upgrade to $DBversion done (adding note and manager_id fields in accountlines table)\n";
4304 SetVersion($DBversion);
4305 }
4306
4307 $DBversion = "3.03.00.050";
4308 if ( C4::Context->preference("Version") < TransformToNum($DBversion) ) {
4309 $dbh->do("
4310 INSERT IGNORE INTO `systempreferences` (variable,value,explanation,options,type) VALUES('OpacHiddenItems','','This syspref allows to define custom rules for hiding specific items at opac. See docs/opac/OpacHiddenItems.txt for more informations.','','Textarea');
4311 ");
4312 print "Upgrade to $DBversion done (Adding OpacHiddenItems syspref)\n";
4313 SetVersion($DBversion);
4314 }
4315
4316 $DBversion = "3.03.00.051";
4317 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
4318 print "Upgrade to $DBversion done (Remove spaces and dashes from message_attribute names)\n";
4319 $dbh->do("UPDATE message_attributes SET message_name = 'Item_Due' WHERE message_name='Item Due'");
4320 $dbh->do("UPDATE message_attributes SET message_name = 'Advance_Notice' WHERE message_name='Advance Notice'");
4321 $dbh->do("UPDATE message_attributes SET message_name = 'Hold_Filled' WHERE message_name='Hold Filled'");
4322 $dbh->do("UPDATE message_attributes SET message_name = 'Item_Check_in' WHERE message_name='Item Check-in'");
4323 $dbh->do("UPDATE message_attributes SET message_name = 'Item_Checkout' WHERE message_name='Item Checkout'");
4324 SetVersion ($DBversion);
4325 }
4326
4327 $DBversion = "3.03.00.052";
4328 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
4329 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('WaitingNotifyAtCheckin',0,'If ON, notify librarians of waiting holds for the patron whose items they are checking in.',NULL,'YesNo');");
4330 print "Upgrade to $DBversion done (Add syspref WaitingNotifyAtCheckin)\n";
4331 SetVersion ($DBversion);
4332 }
4333
4334 $DBversion = "3.04.00.000";
4335 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
4336 print "Upgrade to $DBversion done Koha 3.4.0 release \n";
4337 SetVersion ($DBversion);
4338 }
4339
4340 $DBversion = "3.05.00.001";
4341 if ( C4::Context->preference("Version") < TransformToNum($DBversion) ) {
4342 $dbh->do(qq{
4343 INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('numSearchRSSResults',50,'Specify the maximum number of results to display on a RSS page of results',NULL,'Integer');
4344 });
4345 print "Upgrade to $DBversion done (Adds New System preference numSearchRSSResults)\n";
4346 SetVersion($DBversion);
4347 }
4348
4349 $DBversion = '3.05.00.002';
4350 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
4351 #follow up fix 5860: some installs already past 3.3.0.42
4352 stocknumber_checker();
4353 print "Upgrade to $DBversion done (Fix for stocknumber index)\n";
4354 SetVersion ($DBversion);
4355 }
4356
4357 $DBversion = "3.05.00.003";
4358 if ( C4::Context->preference("Version") < TransformToNum($DBversion) ) {
4359 $dbh->do(qq{
4360 INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('OpacRenewalBranch','checkoutbranch','Choose how the branch for an OPAC renewal is recorded in statistics','itemhomebranch|patronhomebranch|checkoutbranch|null','Choice');
4361 });
4362 print "Upgrade to $DBversion done (Adds New System preference OpacRenewalBranch)\n";
4363 SetVersion($DBversion);
4364 }
4365
4366 $DBversion = "3.05.00.004";
4367 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
4368 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('ShowReviewerPhoto',1,'If ON, photo of reviewer will be shown beside comments in OPAC',NULL,'YesNo');");
4369 print "Upgrade to $DBversion done (Add syspref ShowReviewerPhoto)\n";
4370 SetVersion($DBversion);
4371 }
4372
4373 $DBversion = "3.05.00.005";
4374 if ( C4::Context->preference("Version") < TransformToNum($DBversion) ) {
4375 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES ('BasketConfirmations', '1', 'When closing or reopening a basket,', 'always ask for confirmation.|do not ask for confirmation.', 'Choice');");
4376 print "Upgrade to $DBversion done (Adds pref BasketConfirmations)\n";
4377 SetVersion($DBversion);
4378 }
4379
4380 $DBversion = "3.05.00.006";
4381 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
4382 $dbh->do("INSERT INTO systempreferences (variable,value,explanation,options,type) VALUES ('MARCAuthorityControlField008', '|| aca||aabn | a|a d', NULL, NULL, 'Textarea')");
4383 print "Upgrade to $DBversion done (Add syspref MARCAuthorityControlField008)\n";
4384 SetVersion ($DBversion);
4385 }
4386
4387 $DBversion = "3.05.00.007";
4388 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
4389 $dbh->do("INSERT INTO systempreferences (variable,value,explanation,options,type) VALUES('OpenLibraryCovers',0,'If ON Openlibrary book covers will be show',NULL,'YesNo');");
4390 print "Upgrade to $DBversion done (Add syspref OpenLibraryCovers)\n";
4391 SetVersion($DBversion);
4392 }
4393
4394 $DBversion = "3.05.00.008";
4395 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
4396 $dbh->do("ALTER TABLE `cities` ADD `city_state` VARCHAR( 100 ) NULL DEFAULT NULL AFTER `city_name`;");
4397 $dbh->do("ALTER TABLE `cities` ADD `city_country` VARCHAR( 100 ) NULL DEFAULT NULL AFTER `city_zipcode`;");
4398 print "Add state and country to cities table corresponding to new columns in borrowers\n";
4399 SetVersion($DBversion);
4400 }
4401
4402 $DBversion = "3.05.00.009";
4403 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
4404 $dbh->do("INSERT INTO old_issues (borrowernumber, itemnumber, date_due, branchcode, issuingbranch, returndate, lastreneweddate, `return`, renewals, timestamp, issuedate)
4405 SELECT borrowernumber, itemnumber, date_due, branchcode, issuingbranch, returndate, lastreneweddate, `return`, renewals, timestamp, issuedate FROM issues WHERE borrowernumber IS NULL");
4406 $dbh->do("DELETE FROM issues WHERE borrowernumber IS NULL");
4407
4408 $dbh->do("INSERT INTO old_issues (borrowernumber, itemnumber, date_due, branchcode, issuingbranch, returndate, lastreneweddate, `return`, renewals, timestamp, issuedate)
4409 SELECT borrowernumber, itemnumber, date_due, branchcode, issuingbranch, returndate, lastreneweddate, `return`, renewals, timestamp, issuedate FROM issues WHERE itemnumber IS NULL");
4410 $dbh->do("DELETE FROM issues WHERE itemnumber IS NULL");
4411
4412 $dbh->do("INSERT INTO old_issues (borrowernumber, itemnumber, date_due, branchcode, issuingbranch, returndate, lastreneweddate, `return`, renewals, timestamp, issuedate)
4413 SELECT borrowernumber, itemnumber, date_due, branchcode, issuingbranch, returndate, lastreneweddate, `return`, renewals, timestamp, issuedate FROM issues WHERE NOT EXISTS (SELECT * FROM borrowers WHERE borrowernumber = issues.borrowernumber)");
4414 $dbh->do("DELETE FROM issues WHERE NOT EXISTS (SELECT * FROM borrowers WHERE borrowernumber = issues.borrowernumber)");
4415
4416 $dbh->do("INSERT INTO old_issues (borrowernumber, itemnumber, date_due, branchcode, issuingbranch, returndate, lastreneweddate, `return`, renewals, timestamp, issuedate)
4417 SELECT borrowernumber, itemnumber, date_due, branchcode, issuingbranch, returndate, lastreneweddate, `return`, renewals, timestamp, issuedate FROM issues WHERE NOT EXISTS (SELECT * FROM items WHERE itemnumber = issues.itemnumber)");
4418 $dbh->do("DELETE FROM issues WHERE NOT EXISTS (SELECT * FROM items WHERE itemnumber = issues.itemnumber)");
4419
4420 $dbh->do("ALTER TABLE issues DROP FOREIGN KEY `issues_ibfk_1`");
4421 $dbh->do("ALTER TABLE issues DROP FOREIGN KEY `issues_ibfk_2`");
4422 $dbh->do("ALTER TABLE issues ALTER COLUMN borrowernumber DROP DEFAULT");
4423 $dbh->do("ALTER TABLE issues ALTER COLUMN itemnumber DROP DEFAULT");
4424 $dbh->do("ALTER TABLE issues MODIFY COLUMN borrowernumber int(11) NOT NULL");
4425 $dbh->do("ALTER TABLE issues MODIFY COLUMN itemnumber int(11) NOT NULL");
4426 $dbh->do("ALTER TABLE issues DROP KEY `issuesitemidx`");
4427 $dbh->do("ALTER TABLE issues ADD PRIMARY KEY (`itemnumber`)");
4428 $dbh->do("ALTER TABLE issues ADD CONSTRAINT `issues_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE RESTRICT ON UPDATE CASCADE");
4429 $dbh->do("ALTER TABLE issues ADD CONSTRAINT `issues_ibfk_2` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE RESTRICT ON UPDATE CASCADE");
4430
4431 print "Upgrade to $DBversion done (issues referential integrity)\n";
4432 SetVersion ($DBversion);
4433 }
4434
4435 $DBversion = "3.05.00.010";
4436 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
4437 $dbh->do("CREATE INDEX priorityfoundidx ON reserves (priority,found)");
4438 print "Create an index on reserves to speed up holds awaiting pickup report bug 5866\n";
4439 SetVersion($DBversion);
4440 }
4441
4442 $DBversion = "3.05.00.011";
4443 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
4444 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('OPACResultsSidebar','','Define HTML to be included on the search results page, underneath the facets sidebar','70|10','Textarea')");
4445 print "Upgrade to $DBversion done (add OPACResultsSidebar syspref (enh 6165))\n";
4446 SetVersion($DBversion);
4447 }
4448
4449
4450 =head1 FUNCTIONS
4451
4452 =head2 DropAllForeignKeys($table)
4453
4454 Drop all foreign keys of the table $table
4455
4456 =cut
4457
4458
4459 sub DropAllForeignKeys {
4460 my ($table) = @_;
4461 # get the table description
4462 my $sth = $dbh->prepare("SHOW CREATE TABLE $table");
4463 $sth->execute;
4464 my $vsc_structure = $sth->fetchrow;
4465 # split on CONSTRAINT keyword
4466 my @fks = split /CONSTRAINT /,$vsc_structure;
4467 # parse each entry
4468 foreach (@fks) {
4469 # isolate what is before FOREIGN KEY, if there is something, it's a foreign key to drop
4470 $_ = /(.*) FOREIGN KEY.*/;
4471 my $id = $1;
4472 if ($id) {
4473 # we have found 1 foreign, drop it
4474 $dbh->do("ALTER TABLE $table DROP FOREIGN KEY $id");
4475 $id="";
4476 }
4477 }
4478 }
4479
4480
4481 =head2 TransformToNum
4482
4483 Transform the Koha version from a 4 parts string
4484 to a number, with just 1 .
4485
4486 =cut
4487
4488 sub TransformToNum {
4489 my $version = shift;
4490 # remove the 3 last . to have a Perl number
4491 $version =~ s/(.*\..*)\.(.*)\.(.*)/$1$2$3/;
4492 return $version;
4493 }
4494
4495 =head2 SetVersion
4496
4497 set the DBversion in the systempreferences
4498
4499 =cut
4500
4501 sub SetVersion {
4502 my $kohaversion = TransformToNum(shift);
4503 if (C4::Context->preference('Version')) {
4504 my $finish=$dbh->prepare("UPDATE systempreferences SET value=? WHERE variable='Version'");
4505 $finish->execute($kohaversion);
4506 } else {
4507 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')");
4508 $finish->execute($kohaversion);
4509 }
4510 }
4511 exit;
4512
Koha ILS