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 $dbh->do("ALTER TABLE `biblioitems` ADD `cn_source` VARCHAR(10) DEFAULT NULL AFTER `ccode`");
168 $dbh->do("ALTER TABLE `biblioitems` ADD `cn_class` VARCHAR(30) DEFAULT NULL AFTER `cn_source`");
169 $dbh->do("ALTER TABLE `biblioitems` ADD `cn_item` VARCHAR(10) DEFAULT NULL AFTER `cn_class`");
170 $dbh->do("ALTER TABLE `biblioitems` ADD `cn_suffix` VARCHAR(10) DEFAULT NULL AFTER `cn_item`");
171 $dbh->do("ALTER TABLE `biblioitems` ADD `cn_sort` VARCHAR(30) DEFAULT NULL AFTER `cn_suffix`");
172 $dbh->do("ALTER TABLE `biblioitems` 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)");
188 $dbh->do("UPDATE `biblioitems` SET cn_item = subclass");
189 $dbh->do("UPDATE `biblioitems` SET `cn_sort` = `lcsort`");
191 # Now drop the old call number columns
192 $dbh->do("ALTER TABLE `biblioitems` DROP COLUMN `classification`");
193 $dbh->do("ALTER TABLE `biblioitems` DROP COLUMN `dewey`");
194 $dbh->do("ALTER TABLE `biblioitems` DROP COLUMN `subclass`");
195 $dbh->do("ALTER TABLE `biblioitems` DROP COLUMN `lcsort`");
196 $dbh->do("ALTER TABLE `biblioitems` DROP COLUMN `ccode`");
198 # deletedbiblio changes
199 $dbh->do("ALTER TABLE `deletedbiblio` ALTER COLUMN `frameworkcode` SET DEFAULT ''");
200 $dbh->do("ALTER TABLE `deletedbiblio` DROP COLUMN `marc`");
201 $dbh->do("ALTER TABLE `deletedbiblio` ADD `datecreated` DATE NOT NULL AFTER `timestamp`");
202 $dbh->do("UPDATE deletedbiblio SET datecreated = timestamp");
204 # deletedbiblioitems changes
205 $dbh->do("ALTER TABLE `deletedbiblioitems` MODIFY `publicationyear` TEXT");
206 $dbh->do("ALTER TABLE `deletedbiblioitems` CHANGE `volumeddesc` `volumedesc` TEXT");
207 $dbh->do("ALTER TABLE `deletedbiblioitems` MODIFY `collectiontitle` MEDIUMTEXT DEFAULT NULL AFTER `volumedesc`");
208 $dbh->do("ALTER TABLE `deletedbiblioitems` MODIFY `collectionissn` TEXT DEFAULT NULL AFTER `collectiontitle`");
209 $dbh->do("ALTER TABLE `deletedbiblioitems` MODIFY `collectionvolume` MEDIUMTEXT DEFAULT NULL AFTER `collectionissn`");
210 $dbh->do("ALTER TABLE `deletedbiblioitems` MODIFY `editionstatement` TEXT DEFAULT NULL AFTER `collectionvolume`");
211 $dbh->do("ALTER TABLE `deletedbiblioitems` MODIFY `editionresponsibility` TEXT DEFAULT NULL AFTER `editionstatement`");
212 $dbh->do("ALTER TABLE `deletedbiblioitems` MODIFY `place` VARCHAR(255) DEFAULT NULL AFTER `size`");
213 $dbh->do("ALTER TABLE `deletedbiblioitems` MODIFY `marc` BLOB");
214 $dbh->do("ALTER TABLE `deletedbiblioitems` ADD `cn_source` VARCHAR(10) DEFAULT NULL AFTER `url`");
215 $dbh->do("ALTER TABLE `deletedbiblioitems` ADD `cn_class` VARCHAR(30) DEFAULT NULL AFTER `cn_source`");
216 $dbh->do("ALTER TABLE `deletedbiblioitems` ADD `cn_item` VARCHAR(10) DEFAULT NULL AFTER `cn_class`");
217 $dbh->do("ALTER TABLE `deletedbiblioitems` ADD `cn_suffix` VARCHAR(10) DEFAULT NULL AFTER `cn_item`");
218 $dbh->do("ALTER TABLE `deletedbiblioitems` ADD `cn_sort` VARCHAR(30) DEFAULT NULL AFTER `cn_suffix`");
219 $dbh->do("ALTER TABLE `deletedbiblioitems` ADD `totalissues` INT(10) AFTER `cn_sort`");
220 $dbh->do("ALTER TABLE `deletedbiblioitems` ADD KEY `isbn` (`isbn`)");
221 $dbh->do("ALTER TABLE `deletedbiblioitems` ADD KEY `publishercode` (`publishercode`)");
223 $dbh->do("UPDATE `deletedbiblioitems`
224 SET cn_class = SUBSTR(TRIM(CONCAT_WS(' ', `classification`, `dewey`)), 1, 30)");
225 $dbh->do("UPDATE `deletedbiblioitems` SET cn_item = subclass");
226 $dbh->do("UPDATE `deletedbiblioitems` SET `cn_sort` = `lcsort`");
227 $dbh->do("ALTER TABLE `deletedbiblioitems` DROP COLUMN `classification`");
228 $dbh->do("ALTER TABLE `deletedbiblioitems` DROP COLUMN `dewey`");
229 $dbh->do("ALTER TABLE `deletedbiblioitems` DROP COLUMN `subclass`");
230 $dbh->do("ALTER TABLE `deletedbiblioitems` DROP COLUMN `lcsort`");
231 $dbh->do("ALTER TABLE `deletedbiblioitems` DROP COLUMN `ccode`");
233 # deleteditems changes
234 $dbh->do("ALTER TABLE `deleteditems` MODIFY `barcode` VARCHAR(20) DEFAULT NULL");
235 $dbh->do("ALTER TABLE `deleteditems` MODIFY `price` DECIMAL(8,2) DEFAULT NULL");
236 $dbh->do("ALTER TABLE `deleteditems` MODIFY `replacementprice` DECIMAL(8,2) DEFAULT NULL");
237 $dbh->do("ALTER TABLE `deleteditems` DROP `bulk`");
238 $dbh->do("ALTER TABLE `deleteditems` MODIFY `itemcallnumber` VARCHAR(30) DEFAULT NULL AFTER `wthdrawn`");
239 $dbh->do("ALTER TABLE `deleteditems` MODIFY `holdingbranch` VARCHAR(10) DEFAULT NULL");
240 $dbh->do("ALTER TABLE `deleteditems` DROP `interim`");
241 $dbh->do("ALTER TABLE `deleteditems` MODIFY `timestamp` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP AFTER `paidfor`");
242 $dbh->do("ALTER TABLE `deleteditems` DROP `cutterextra`");
243 $dbh->do("ALTER TABLE `deleteditems` ADD `cn_source` VARCHAR(10) DEFAULT NULL AFTER `onloan`");
244 $dbh->do("ALTER TABLE `deleteditems` ADD `cn_sort` VARCHAR(30) DEFAULT NULL AFTER `cn_source");
245 $dbh->do("ALTER TABLE `deleteditems` ADD `ccode` VARCHAR(10) DEFAULT NULL AFTER `cn_sort`");
246 $dbh->do("ALTER TABLE `deleteditems` ADD `materials` VARCHAR(10) DEFAULT NULL AFTER `ccode`");
247 $dbh->do("ALTER TABLE `deleteditems` ADD `uri` VARCHAR(255) DEFAULT NULL AFTER `materials`");
248 $dbh->do("ALTER TABLE `deleteditems` MODIFY `marc` LONGBLOB AFTER `uri`");
249 $dbh->do("ALTER TABLE `deleteditems` DROP KEY `barcode`");
250 $dbh->do("ALTER TABLE `deleteditems` DROP KEY `itembarcodeidx`");
251 $dbh->do("ALTER TABLE `deleteditems` DROP KEY `itembinoidx`");
252 $dbh->do("ALTER TABLE `deleteditems` DROP KEY `itembibnoidx`");
253 $dbh->do("ALTER TABLE `deleteditems` ADD UNIQUE KEY `delitembarcodeidx` (`barcode`)");
254 $dbh->do("ALTER TABLE `deleteditems` ADD KEY `delitembinoidx` (`biblioitemnumber`)");
255 $dbh->do("ALTER TABLE `deleteditems` ADD KEY `delitembibnoidx` (`biblionumber`)");
256 $dbh->do("ALTER TABLE `deleteditems` ADD KEY `delhomebranch` (`homebranch`)");
257 $dbh->do("ALTER TABLE `deleteditems` ADD KEY `delholdingbranch` (`holdingbranch`)");
258 $dbh->do("UPDATE `deleteditems` SET `ccode` = `itype`");
259 $dbh->do("ALTER TABLE `deleteditems` DROP `itype`");
260 $dbh->do("UPDATE `deleteditems` SET `cn_sort` = `itemcallnumber`");
263 $dbh->do("ALTER TABLE `items` ADD `cn_source` VARCHAR(10) DEFAULT NULL AFTER `onloan`");
264 $dbh->do("ALTER TABLE `items` ADD `cn_sort` VARCHAR(30) DEFAULT NULL AFTER `cn_source");
265 $dbh->do("ALTER TABLE `items` ADD `ccode` VARCHAR(10) DEFAULT NULL AFTER `cn_sort`");
266 $dbh->do("ALTER TABLE `items` ADD `materials` VARCHAR(10) DEFAULT NULL AFTER `ccode`");
267 $dbh->do("ALTER TABLE `items` ADD `uri` VARCHAR(255) DEFAULT NULL AFTER `materials`");
268 $dbh->do("ALTER TABLE `items` DROP KEY `itembarcodeidx`");
269 $dbh->do("ALTER TABLE `items` ADD UNIQUE KEY `itembarcodeidx` (`barcode`)");
271 # map items.itype to items.ccode
272 $dbh->do("UPDATE `items` SET `ccode` = `itype`");
274 # set cn_sort to itemcallnumber -- as with biblioitems.cn_sort,
275 # will have to be subsequently updated per user's default
276 # classification scheme
277 $dbh->do("UPDATE `items` SET `cn_sort` = `itemcallnumber`");
279 $dbh->do("ALTER TABLE `items` DROP `cutterextra`");
280 $dbh->do("ALTER TABLE `items` DROP `itype`");
282 print "Upgrade to $DBversion done (major changes to biblio, biblioitems, items, and deleted* versions of same\n";
283 SetVersion ($DBversion);
286 $DBversion = "3.00.00.010";
287 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
288 $dbh->do("CREATE INDEX `userid` ON borrowers (`userid`) ");
289 print "Upgrade to $DBversion done (userid index added)\n";
290 SetVersion ($DBversion);
295 =item DropAllForeignKeys($table)
297 Drop all foreign keys of the table $table
301 sub DropAllForeignKeys {
303 # get the table description
304 my $sth = $dbh->prepare("SHOW CREATE TABLE $table");
306 my $vsc_structure = $sth->fetchrow;
307 # split on CONSTRAINT keyword
308 my @fks = split /CONSTRAINT /,$vsc_structure;
311 # isolate what is before FOREIGN KEY, if there is something, it's a foreign key to drop
312 $_ = /(.*) FOREIGN KEY.*/;
315 # we have found 1 foreign, drop it
316 $dbh->do("ALTER TABLE $table DROP FOREIGN KEY $id");
330 Transform the Koha version from a 4 parts string
331 to a number, with just 1 .
337 # remove the 3 last . to have a Perl number
338 $version =~ s/(.*\..*)\.(.*)\.(.*)/$1$2$3/;
343 set the DBversion in the systempreferences
347 my $kohaversion = TransformToNum(shift);
348 if (C4::Context->preference('Version')) {
349 my $finish=$dbh->prepare("UPDATE systempreferences SET value=? WHERE variable='Version'");
350 $finish->execute($kohaversion);
352 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')");
353 $finish->execute($kohaversion);
358 # Revision 1.172 2007/07/19 10:21:22 hdl