5 # This script checks for required updates to the database.
7 # Part of the Koha Library Software www.koha.org
8 # Licensed under the GPL.
11 # - Would also be a good idea to offer to do a backup at this time...
13 # NOTE: If you do something more than once in here, make it table driven.
23 use MARC::File::XML ( BinaryEncoding => 'utf8' );
25 # FIXME - The user might be installing a new database, so can't rely
26 # on /etc/koha.conf anyway.
33 %existingtables, # tables already in database
37 $type, $null, $key, $default, $extra,
38 $prefitem, # preference item in systempreferences table
45 my $dbh = C4::Context->dbh;
46 $|=1; # flushes output
49 Deal with virtualshelves
52 my $DBversion = "3.00.00.001";
53 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
54 # update virtualshelves table to
56 $dbh->do("ALTER TABLE `bookshelf` RENAME `virtualshelves`");
57 $dbh->do("ALTER TABLE `shelfcontents` RENAME `virtualshelfcontents`");
58 $dbh->do("ALTER TABLE `virtualshelfcontents` ADD `biblionumber` INT( 11 ) NOT NULL");
59 $dbh->do("UPDATE `virtualshelfcontents` SET biblionumber=(SELECT biblionumber FROM items WHERE items.itemnumber=virtualshelfcontents.itemnumber)");
60 # drop all foreign keys : otherwise, we can't drop itemnumber field.
61 DropAllForeignKeys('virtualshelfcontents');
62 # create the new foreign keys (on biblionumber)
63 $dbh->do("ALTER TABLE `virtualshelfcontents` ADD FOREIGN KEY biblionumber_fk (biblionumber) REFERENCES biblio (biblionumber) ON UPDATE CASCADE ON DELETE CASCADE");
64 # re-create the foreign key on virtualshelf
65 $dbh->do("ALTER TABLE `virtualshelfcontents` ADD FOREIGN KEY shelfnumber_fk (shelfnumber) REFERENCES virtualshelves (shelfnumber) ON UPDATE CASCADE ON DELETE CASCADE");
66 # now we can drop the itemnumber column
67 $dbh->do("ALTER TABLE `virtualshelfcontents` DROP `itemnumber`");
68 print "Upgrade to $DBversion done (virtualshelves)\n";
69 SetVersion ($DBversion);
73 $DBversion = "3.00.00.002";
74 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
75 $dbh->do("DROP TABLE sessions");
76 $dbh->do("CREATE TABLE `sessions` (
77 `id` char(32) NOT NULL,
78 `a_session` text NOT NULL,
79 UNIQUE KEY `id` (`id`)
80 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;");
81 print "Upgrade to $DBversion done (sessions uses CGI::session, new table structure for sessions)\n";
82 SetVersion ($DBversion);
86 $DBversion = "3.00.00.003";
87 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
88 if (C4::Context->preference("opaclanguage") eq "fr") {
89 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('ReservesNeedReturns','0','Si ce paramètre est mis à 1, une réservation posée sur un exemplaire présent sur le site devra être passée en retour pour être disponible. Sinon, elle sera automatiquement disponible, Koha considère que le bibliothécaire place la réservation en ayant le document en mains','','YesNo')");
91 $dbh->do("INSERT INTO `systempreferences` (variable,value,explanation,options,type) VALUES('ReservesNeedReturns','0','If set, a reserve done on an item available in this branch need a check-in, otherwise, a reserve on a specific item, that is on the branch & available is considered as available','','YesNo')");
93 print "Upgrade to $DBversion done (adding ReservesNeedReturns systempref, in circulation)\n";
94 SetVersion ($DBversion);
98 $DBversion = "3.00.00.004";
99 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
100 $dbh->do("INSERT INTO `systempreferences` VALUES ('DebugLevel','2','set the level of error info sent to the browser. 0=none, 1=some, 2=most','0|1|2','Choice')");
101 print "Upgrade to $DBversion done (adding DebugLevel systempref, in 'Admin' tab)\n";
102 SetVersion ($DBversion);
105 $DBversion = "3.00.00.005";
106 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
107 $dbh->do("CREATE TABLE `tags` (
108 `entry` varchar(255) NOT NULL default '',
109 `weight` bigint(20) NOT NULL default 0,
110 PRIMARY KEY (`entry`)
111 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
113 $dbh->do("CREATE TABLE `nozebra` (
114 `server` varchar(20) NOT NULL,
115 `indexname` varchar(40) NOT NULL,
116 `value` varchar(250) NOT NULL,
117 `biblionumbers` longtext NOT NULL,
118 KEY `indexname` (`server`,`indexname`),
119 KEY `value` (`server`,`value`))
120 ENGINE=InnoDB DEFAULT CHARSET=utf8;
122 print "Upgrade to $DBversion done (adding tags and nozebra tables )\n";
123 SetVersion ($DBversion);
126 $DBversion = "3.00.00.006";
127 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
128 $dbh->do("UPDATE issues SET issuedate=timestamp WHERE issuedate='0000-00-00'");
129 print "Upgrade to $DBversion done (filled issues.issuedate with timestamp)\n";
130 SetVersion ($DBversion);
133 $DBversion = "3.00.00.007";
134 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
135 $dbh->do("INSERT INTO systempreferences (variable,value,explanation,options,type) VALUES ('SessionStorage','mysql','Use mysql or a temporary file for storing session data','mysql|tmp','Choice')");
136 print "Upgrade to $DBversion done (set SessionStorage variable)\n";
137 SetVersion ($DBversion);
140 $DBversion = "3.00.00.008";
141 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
142 $dbh->do("ALTER TABLE `biblio` ADD `datecreated` DATE NOT NULL AFTER `timestamp` ;");
143 $dbh->do("UPDATE biblio SET datecreated=timestamp");
144 print "Upgrade to $DBversion done (biblio creation date)\n";
145 SetVersion ($DBversion);
148 $DBversion = "3.00.00.009";
149 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
151 # Create backups of call number columns
152 # in case default migration needs to be customized
154 # UPGRADE NOTE: temp_upg_biblioitems_call_num should be dropped
155 # after call numbers have been transformed to the new structure
157 # Not bothering to do the same with deletedbiblioitems -- assume
158 # default is good enough.
159 $dbh->do("CREATE TABLE `temp_upg_biblioitems_call_num` AS
160 SELECT `biblioitemnumber`, `biblionumber`,
161 `classification`, `dewey`, `subclass`,
163 FROM `biblioitems`");
165 # biblioitems changes
166 $dbh->do("ALTER TABLE `biblioitems` CHANGE COLUMN `volumeddesc` `volumedesc` TEXT,
167 ADD `cn_source` VARCHAR(10) DEFAULT NULL AFTER `ccode`,
168 ADD `cn_class` VARCHAR(30) DEFAULT NULL AFTER `cn_source`,
169 ADD `cn_item` VARCHAR(10) DEFAULT NULL AFTER `cn_class`,
170 ADD `cn_suffix` VARCHAR(10) DEFAULT NULL AFTER `cn_item`,
171 ADD `cn_sort` VARCHAR(30) DEFAULT NULL AFTER `cn_suffix`,
172 ADD `totalissues` INT(10) AFTER `cn_sort`");
174 # default mapping of call number columns:
175 # cn_class = concatentation of classification + dewey,
176 # trimmed to fit -- assumes that most users do not
177 # populate both classification and dewey in a single record
179 # cn_source = left null
182 # After upgrade, cn_sort will have to be set based on whatever
183 # default call number scheme user sets as a preference. Misc
184 # script will be added at some point to do that.
186 $dbh->do("UPDATE `biblioitems`
187 SET cn_class = SUBSTR(TRIM(CONCAT_WS(' ', `classification`, `dewey`)), 1, 30),
192 # Now drop the old call number columns
193 $dbh->do("ALTER TABLE `biblioitems` DROP COLUMN `classification`,
195 DROP COLUMN `subclass`,
196 DROP COLUMN `lcsort`,
197 DROP COLUMN `ccode`");
199 # deletedbiblio changes
200 $dbh->do("ALTER TABLE `deletedbiblio` ALTER COLUMN `frameworkcode` SET DEFAULT '',
202 ADD `datecreated` DATE NOT NULL AFTER `timestamp`");
203 $dbh->do("UPDATE deletedbiblio SET datecreated = timestamp");
205 # deletedbiblioitems changes
206 $dbh->do("ALTER TABLE `deletedbiblioitems`
207 MODIFY `publicationyear` TEXT,
208 CHANGE `volumeddesc` `volumedesc` TEXT,
209 MODIFY `collectiontitle` MEDIUMTEXT DEFAULT NULL AFTER `volumedesc`,
210 MODIFY `collectionissn` TEXT DEFAULT NULL AFTER `collectiontitle`,
211 MODIFY `collectionvolume` MEDIUMTEXT DEFAULT NULL AFTER `collectionissn`,
212 MODIFY `editionstatement` TEXT DEFAULT NULL AFTER `collectionvolume`,
213 MODIFY `editionresponsibility` TEXT DEFAULT NULL AFTER `editionstatement`,
214 MODIFY `place` VARCHAR(255) DEFAULT NULL AFTER `size`,
216 ADD `cn_source` VARCHAR(10) DEFAULT NULL AFTER `url`,
217 ADD `cn_class` VARCHAR(30) DEFAULT NULL AFTER `cn_source`,
218 ADD `cn_item` VARCHAR(10) DEFAULT NULL AFTER `cn_class`,
219 ADD `cn_suffix` VARCHAR(10) DEFAULT NULL AFTER `cn_item`,
220 ADD `cn_sort` VARCHAR(30) DEFAULT NULL AFTER `cn_suffix`,
221 ADD `totalissues` INT(10) AFTER `cn_sort`,
222 ADD KEY `isbn` (`isbn`),
223 ADD KEY `publishercode` (`publishercode`)
226 $dbh->do("UPDATE `deletedbiblioitems`
227 SET `cn_class` = SUBSTR(TRIM(CONCAT_WS(' ', `classification`, `dewey`)), 1, 30),
228 `cn_item` = `subclass`,
231 $dbh->do("ALTER TABLE `deletedbiblioitems`
232 DROP COLUMN `classification`,
234 DROP COLUMN `subclass`,
235 DROP COLUMN `lcsort`,
239 # deleteditems changes
240 $dbh->do("ALTER TABLE `deleteditems`
241 MODIFY `barcode` VARCHAR(20) DEFAULT NULL,
242 MODIFY `price` DECIMAL(8,2) DEFAULT NULL,
243 MODIFY `replacementprice` DECIMAL(8,2) DEFAULT NULL,
245 MODIFY `itemcallnumber` VARCHAR(30) DEFAULT NULL AFTER `wthdrawn`,
246 MODIFY `holdingbranch` VARCHAR(10) DEFAULT NULL,
248 MODIFY `timestamp` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP AFTER `paidfor`,
250 ADD `cn_source` VARCHAR(10) DEFAULT NULL AFTER `onloan`,
251 ADD `cn_sort` VARCHAR(30) DEFAULT NULL AFTER `cn_source`,
252 ADD `ccode` VARCHAR(10) DEFAULT NULL AFTER `cn_sort`,
253 ADD `materials` VARCHAR(10) DEFAULT NULL AFTER `ccode`,
254 ADD `uri` VARCHAR(255) DEFAULT NULL AFTER `materials`,
255 MODIFY `marc` LONGBLOB AFTER `uri`,
257 DROP KEY `itembarcodeidx`,
258 DROP KEY `itembinoidx`,
259 DROP KEY `itembibnoidx`,
260 ADD UNIQUE KEY `delitembarcodeidx` (`barcode`),
261 ADD KEY `delitembinoidx` (`biblioitemnumber`),
262 ADD KEY `delitembibnoidx` (`biblionumber`),
263 ADD KEY `delhomebranch` (`homebranch`),
264 ADD KEY `delholdingbranch` (`holdingbranch`)");
265 $dbh->do("UPDATE deleteditems SET `ccode` = `itype`");
266 $dbh->do("ALTER TABLE deleteditems DROP `itype`");
267 $dbh->do("UPDATE `deleteditems` SET `cn_sort` = `itemcallnumber`");
270 $dbh->do("ALTER TABLE `items` ADD `cn_source` VARCHAR(10) DEFAULT NULL AFTER `onloan`,
271 ADD `cn_sort` VARCHAR(30) DEFAULT NULL AFTER `cn_source`,
272 ADD `ccode` VARCHAR(10) DEFAULT NULL AFTER `cn_sort`,
273 ADD `materials` VARCHAR(10) DEFAULT NULL AFTER `ccode`,
274 ADD `uri` VARCHAR(255) DEFAULT NULL AFTER `materials`
276 $dbh->do("ALTER TABLE `items`
277 DROP KEY `itembarcodeidx`,
278 ADD UNIQUE KEY `itembarcodeidx` (`barcode`)");
280 # map items.itype to items.ccode and
281 # set cn_sort to itemcallnumber -- as with biblioitems.cn_sort,
282 # will have to be subsequently updated per user's default
283 # classification scheme
284 $dbh->do("UPDATE `items` SET `cn_sort` = `itemcallnumber`,
287 $dbh->do("ALTER TABLE `items` DROP `cutterextra`,
290 print "Upgrade to $DBversion done (major changes to biblio, biblioitems, items, and deleted* versions of same\n";
291 SetVersion ($DBversion);
294 $DBversion = "3.00.00.010";
295 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
296 $dbh->do("CREATE INDEX `userid` ON borrowers (`userid`) ");
297 print "Upgrade to $DBversion done (userid index added)\n";
298 SetVersion ($DBversion);
301 $DBversion = "3.00.00.011";
302 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
303 $dbh->do("ALTER TABLE `branchcategories` CHANGE `categorycode` `categorycode` char(10) ");
304 $dbh->do("ALTER TABLE `branchcategories` CHANGE `categoryname` `categoryname` varchar(32) ");
305 $dbh->do("ALTER TABLE `branchcategories` ADD COLUMN `categorytype` varchar(16) ");
306 $dbh->do("UPDATE `branchcategories` SET `categorytype` = 'properties'");
307 $dbh->do("ALTER TABLE `branchrelations` CHANGE `cateorycode` `cateorycode` char(10) ");
308 print "Upgrade to $DBversion done (added branchcategory type)\n";
309 SetVersion ($DBversion);
314 =item DropAllForeignKeys($table)
316 Drop all foreign keys of the table $table
320 sub DropAllForeignKeys {
322 # get the table description
323 my $sth = $dbh->prepare("SHOW CREATE TABLE $table");
325 my $vsc_structure = $sth->fetchrow;
326 # split on CONSTRAINT keyword
327 my @fks = split /CONSTRAINT /,$vsc_structure;
330 # isolate what is before FOREIGN KEY, if there is something, it's a foreign key to drop
331 $_ = /(.*) FOREIGN KEY.*/;
334 # we have found 1 foreign, drop it
335 $dbh->do("ALTER TABLE $table DROP FOREIGN KEY $id");
349 Transform the Koha version from a 4 parts string
350 to a number, with just 1 .
356 # remove the 3 last . to have a Perl number
357 $version =~ s/(.*\..*)\.(.*)\.(.*)/$1$2$3/;
362 set the DBversion in the systempreferences
366 my $kohaversion = TransformToNum(shift);
367 if (C4::Context->preference('Version')) {
368 my $finish=$dbh->prepare("UPDATE systempreferences SET value=? WHERE variable='Version'");
369 $finish->execute($kohaversion);
371 my $finish=$dbh->prepare("INSERT into systempreferences (variable,value,explanation) values ('Version',?,'The Koha database version. Don t change this value manually, it s holded by the webinstaller')");
372 $finish->execute($kohaversion);
377 # Revision 1.172 2007/07/19 10:21:22 hdl