[Koha-patches] [PATCH] kohabug 2076: more reconciliation of DB schema (DB rev 080)
Galen Charlton
galen.charlton at liblime.com
Mon May 5 17:04:26 CEST 2008
Adjustments to updatedatabase.pl to help ensure that a
DB upgraded from 2.2.9, 3.0-alpha, or 3.0-beta has a schema
identical to a fresh installation.
The changes to the following columns and indexes are to
default values, field widths, position relative to other
columns or index names.
virtualshelfcontents.biblionumber
virtualshelfcontents_ibfk_1 (virtualshelfcontents)
shelfcontents_ibfk_2 (virtualshelfcontents)
sessions.id
deletedbiblioitems.marc
branchcategories.categorycode
branchrelations.categorycode
items.damaged
deleteditems.notforloan
deleteditems.damaged
deleteditems.itemlost
deleteditems.wthdrawn
currency.symbol
subscription.numberlength
subscription.weeklength
serialidx (serialitems)
items.more_subfields_xml
z3950servers.type
deleteditems.more_subfields_xml
opac_news.lang
labels_conf.formatstring
The following missing columns were added:
deletedbiblioitems.marcxml
deleteditems.itype
The 080 DB rev is specifically for syncing the schema
for users of 3.0-alpha, 3.0-beta, and 3.0-beta2, and should
be a no-op for anybody who has followed git HEAD for
the past few months:
subscription.monthlength
deleteditems.marc
aqbooksellers.name
NOTE: this patch does not handle the case of syncing
the DB of a developer or user who has been following
git HEAD since before 3.0-alpha.
---
installer/data/mysql/updatedatabase.pl | 63 ++++++++++++++++++++++----------
kohaversion.pl | 2 +-
2 files changed, 44 insertions(+), 21 deletions(-)
diff --git a/installer/data/mysql/updatedatabase.pl b/installer/data/mysql/updatedatabase.pl
index 33bfa8d..98294bd 100755
--- a/installer/data/mysql/updatedatabase.pl
+++ b/installer/data/mysql/updatedatabase.pl
@@ -60,15 +60,15 @@ if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
#
$dbh->do("ALTER TABLE `bookshelf` RENAME `virtualshelves`");
$dbh->do("ALTER TABLE `shelfcontents` RENAME `virtualshelfcontents`");
- $dbh->do("ALTER TABLE `virtualshelfcontents` ADD `biblionumber` INT( 11 ) NOT NULL");
+ $dbh->do("ALTER TABLE `virtualshelfcontents` ADD `biblionumber` INT( 11 ) NOT NULL default '0' AFTER shelfnumber");
$dbh->do("UPDATE `virtualshelfcontents` SET biblionumber=(SELECT biblionumber FROM items WHERE items.itemnumber=virtualshelfcontents.itemnumber)");
# drop all foreign keys : otherwise, we can't drop itemnumber field.
DropAllForeignKeys('virtualshelfcontents');
+ $dbh->do("ALTER TABLE `virtualshelfcontents` ADD KEY biblionumber (biblionumber)");
# create the new foreign keys (on biblionumber)
- $dbh->do("ALTER TABLE `virtualshelfcontents` ADD FOREIGN KEY biblionumber_fk (biblionumber) REFERENCES biblio (biblionumber) ON UPDATE CASCADE ON DELETE CASCADE");
+ $dbh->do("ALTER TABLE `virtualshelfcontents` ADD CONSTRAINT `virtualshelfcontents_ibfk_1` FOREIGN KEY (`shelfnumber`) REFERENCES `virtualshelves` (`shelfnumber`) ON DELETE CASCADE ON UPDATE CASCADE");
# re-create the foreign key on virtualshelf
- $dbh->do("ALTER TABLE `virtualshelfcontents` ADD FOREIGN KEY shelfnumber_fk (shelfnumber) REFERENCES virtualshelves (shelfnumber) ON UPDATE CASCADE ON DELETE CASCADE");
- # now we can drop the itemnumber column
+ $dbh->do("ALTER TABLE `virtualshelfcontents` ADD CONSTRAINT `shelfcontents_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE");
$dbh->do("ALTER TABLE `virtualshelfcontents` DROP `itemnumber`");
print "Upgrade to $DBversion done (virtualshelves)\n";
SetVersion ($DBversion);
@@ -79,7 +79,7 @@ $DBversion = "3.00.00.002";
if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
$dbh->do("DROP TABLE sessions");
$dbh->do("CREATE TABLE `sessions` (
- `id` char(32) NOT NULL,
+ `id` varchar(32) NOT NULL,
`a_session` text NOT NULL,
UNIQUE KEY `id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;");
@@ -217,13 +217,14 @@ if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
MODIFY `editionstatement` TEXT DEFAULT NULL AFTER `collectionvolume`,
MODIFY `editionresponsibility` TEXT DEFAULT NULL AFTER `editionstatement`,
MODIFY `place` VARCHAR(255) DEFAULT NULL AFTER `size`,
- MODIFY `marc` BLOB,
+ MODIFY `marc` LONGBLOB,
ADD `cn_source` VARCHAR(10) DEFAULT NULL AFTER `url`,
ADD `cn_class` VARCHAR(30) DEFAULT NULL AFTER `cn_source`,
ADD `cn_item` VARCHAR(10) DEFAULT NULL AFTER `cn_class`,
ADD `cn_suffix` VARCHAR(10) DEFAULT NULL AFTER `cn_item`,
ADD `cn_sort` VARCHAR(30) DEFAULT NULL AFTER `cn_suffix`,
ADD `totalissues` INT(10) AFTER `cn_sort`,
+ ADD `marcxml` LONGTEXT NOT NULL AFTER `totalissues`,
ADD KEY `isbn` (`isbn`),
ADD KEY `publishercode` (`publishercode`)
");
@@ -305,11 +306,11 @@ if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
$DBversion = "3.00.00.011";
if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
- $dbh->do("ALTER TABLE `branchcategories` CHANGE `categorycode` `categorycode` char(10) ");
+ $dbh->do("ALTER TABLE `branchcategories` CHANGE `categorycode` `categorycode` varchar(10) ");
$dbh->do("ALTER TABLE `branchcategories` CHANGE `categoryname` `categoryname` varchar(32) ");
$dbh->do("ALTER TABLE `branchcategories` ADD COLUMN `categorytype` varchar(16) ");
$dbh->do("UPDATE `branchcategories` SET `categorytype` = 'properties'");
- $dbh->do("ALTER TABLE `branchrelations` CHANGE `categorycode` `categorycode` char(10) ");
+ $dbh->do("ALTER TABLE `branchrelations` CHANGE `categorycode` `categorycode` varchar(10) ");
print "Upgrade to $DBversion done (added branchcategory type)\n";
SetVersion ($DBversion);
}
@@ -561,7 +562,9 @@ if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
$DBversion = "3.00.00.022";
if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
$dbh->do("ALTER TABLE items
- ADD `damaged` tinyint(1) default NULL");
+ ADD `damaged` tinyint(1) default NULL AFTER notforloan");
+ $dbh->do("ALTER TABLE deleteditems
+ ADD `damaged` tinyint(1) default NULL AFTER notforloan");
print "Upgrade to $DBversion done (adding damaged column to items table)\n";
SetVersion ($DBversion);
}
@@ -572,8 +575,7 @@ if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
VALUES ('yuipath','http://yui.yahooapis.com/2.3.1/build','Insert the path to YUI libraries','','free')");
print "Upgrade to $DBversion done (adding new system preference for controlling YUI path)\n";
SetVersion ($DBversion);
-}
-
+}
$DBversion = "3.00.00.024";
if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
$dbh->do("ALTER TABLE biblioitems CHANGE itemtype itemtype VARCHAR(10)");
@@ -584,6 +586,7 @@ if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
$DBversion = "3.00.00.025";
if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
$dbh->do("ALTER TABLE items ADD COLUMN itype VARCHAR(10)");
+ $dbh->do("ALTER TABLE deleteditems ADD COLUMN itype VARCHAR(10) AFTER uri");
if(C4::Context->preference('item-level_itypes')){
$dbh->do('update items,biblioitems set items.itype=biblioitems.itemtype where items.biblionumber=biblioitems.biblionumber and itype is null');
}
@@ -837,6 +840,15 @@ if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
MODIFY damaged tinyint(1) NOT NULL default 0,
MODIFY itemlost tinyint(1) NOT NULL default 0,
MODIFY wthdrawn tinyint(1) NOT NULL default 0");
+ $dbh->do("UPDATE deleteditems SET notforloan = 0 WHERE notforloan IS NULL");
+ $dbh->do("UPDATE deleteditems SET damaged = 0 WHERE damaged IS NULL");
+ $dbh->do("UPDATE deleteditems SET itemlost = 0 WHERE itemlost IS NULL");
+ $dbh->do("UPDATE deleteditems SET wthdrawn = 0 WHERE wthdrawn IS NULL");
+ $dbh->do("ALTER TABLE deleteditems
+ MODIFY notforloan tinyint(1) NOT NULL default 0,
+ MODIFY damaged tinyint(1) NOT NULL default 0,
+ MODIFY itemlost tinyint(1) NOT NULL default 0,
+ MODIFY wthdrawn tinyint(1) NOT NULL default 0");
print "Upgrade to $DBversion done (disallow NULL in several item status columns)\n";
SetVersion ($DBversion);
}
@@ -850,7 +862,7 @@ if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
$DBversion = "3.00.00.043";
if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
- $dbh->do("ALTER TABLE `currency` ADD `symbol` varchar(5) default NULL, ADD `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP");
+ $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");
print "Upgrade to $DBversion done (currency table: add symbol and timestamp columns)\n";
SetVersion ($DBversion);
}
@@ -940,9 +952,9 @@ VALUES( 'he', 'Hebr')");
$DBversion = "3.00.00.046";
if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
- $dbh->do("ALTER TABLE `subscription` CHANGE `numberlength` `numberlength` int(11) default NULL ,
- CHANGE `weeklength` `weeklength` int(11) default NULL");
- $dbh->do("CREATE TABLE `serialitems` (`serialid` int(11) NOT NULL, `itemnumber` int(11) NOT NULL, UNIQUE KEY (`serialid`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
+ $dbh->do("ALTER TABLE `subscription` CHANGE `numberlength` `numberlength` int(11) default '0' ,
+ CHANGE `weeklength` `weeklength` int(11) default '0'");
+ $dbh->do("CREATE TABLE `serialitems` (`serialid` int(11) NOT NULL, `itemnumber` int(11) NOT NULL, UNIQUE KEY `serialididx` (`serialid`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
$dbh->do("INSERT INTO `serialitems` SELECT `serialid`,`itemnumber` from serial where NOT ISNULL(itemnumber) && itemnumber <> '' && itemnumber NOT LIKE '%,%'");
print "Upgrade to $DBversion done (Add serialitems table to link serial issues to items. )\n";
SetVersion ($DBversion);
@@ -957,14 +969,14 @@ if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
$DBversion = "3.00.00.048";
if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
- $dbh->do("ALTER TABLE `items` ADD `more_subfields_xml` longtext default NULL");
+ $dbh->do("ALTER TABLE `items` ADD `more_subfields_xml` longtext default NULL AFTER `itype`");
print "Upgrade to $DBversion done (added items.more_subfields_xml)\n";
SetVersion ($DBversion);
}
$DBversion = "3.00.00.049";
if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
- $dbh->do("ALTER TABLE `z3950servers` ADD `encoding` text default NULL ");
+ $dbh->do("ALTER TABLE `z3950servers` ADD `encoding` text default NULL AFTER type ");
print "Upgrade to $DBversion done ( Added encoding field to z3950servers table )\n";
SetVersion ($DBversion);
}
@@ -985,7 +997,7 @@ if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
$DBversion = "3.00.00.052";
if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
- $dbh->do("ALTER TABLE `deleteditems` ADD `more_subfields_xml` LONGTEXT DEFAULT NULL;");
+ $dbh->do("ALTER TABLE `deleteditems` ADD `more_subfields_xml` LONGTEXT DEFAULT NULL AFTER `itype`");
print "Upgrade to $DBversion done ( Adding missing column to deleteditems table. )\n";
SetVersion ($DBversion);
}
@@ -1053,7 +1065,7 @@ if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
CHANGE `lang` `lang` VARCHAR( 25 )
CHARACTER SET utf8
COLLATE utf8_general_ci
- NOT NULL ");
+ NOT NULL default ''");
print "Upgrade to $DBversion done ( lang field in opac_news made longer )\n";
SetVersion ($DBversion);
}
@@ -1309,7 +1321,7 @@ if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
$DBversion = "3.00.00.072";
if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
- $dbh->do("ALTER TABLE labels_conf ADD COLUMN formatstring VARCHAR(64) DEFAULT NULL;");
+ $dbh->do("ALTER TABLE labels_conf ADD COLUMN formatstring VARCHAR(64) DEFAULT NULL AFTER printingtype");
print "Upgrade to $DBversion done ( Adding format string to labels generator. )\n";
SetVersion ($DBversion);
}
@@ -1475,6 +1487,17 @@ if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
SetVersion ($DBversion);
}
+
+
+$DBversion = "3.00.00.080";
+if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
+ $dbh->do("ALTER TABLE subscription CHANGE monthlength monthlength int(11) default '0'");
+ $dbh->do("ALTER TABLE deleteditems MODIFY marc LONGBLOB AFTER copynumber");
+ $dbh->do("ALTER TABLE aqbooksellers CHANGE name name mediumtext NOT NULL");
+ print "Upgrade to $DBversion done (catch up on DB schema changes since alpha and beta)\n";
+ SetVersion ($DBversion);
+}
+
=item DropAllForeignKeys($table)
Drop all foreign keys of the table $table
diff --git a/kohaversion.pl b/kohaversion.pl
index ccff567..2d83d26 100644
--- a/kohaversion.pl
+++ b/kohaversion.pl
@@ -10,7 +10,7 @@
use strict;
sub kohaversion {
- our $VERSION = "3.00.00.079";
+ our $VERSION = "3.00.00.080";
# version needs to be set this way
# so that it can be picked up by Makefile.PL
# during install
--
1.5.5.rc0.16.g02b00
More information about the Koha-patches
mailing list