From 22e23753880752dfafe28fbcd045017434897ba7 Mon Sep 17 00:00:00 2001 From: Marcel de Rooy Date: Mon, 16 May 2011 11:34:25 +0200 Subject: [PATCH] [REVISED] [3.2.X] 5860 Fixing stocknumber index Content-Type: text/plain; charset="utf-8" Fixing code for 3.2.6.1/3.2.7.1 update. Adding code at end of update script if install past that point already. --- installer/data/mysql/updatedatabase.pl | 34 ++++++++++++++++++++++++++----- 1 files changed, 28 insertions(+), 6 deletions(-) diff --git a/installer/data/mysql/updatedatabase.pl b/installer/data/mysql/updatedatabase.pl index 6888b72..061ebd6 100755 --- a/installer/data/mysql/updatedatabase.pl +++ b/installer/data/mysql/updatedatabase.pl @@ -4017,12 +4017,26 @@ if (C4::Context->preference("Version") < TransformToNum($DBversion)) { $DBversion = '3.02.06.001'; if (C4::Context->preference("Version") < TransformToNum($DBversion)) { - $dbh->do("ALTER TABLE `items` DROP INDEX `itemsstocknumberidx`;"); - $dbh->do("ALTER TABLE items ADD INDEX itemstocknumberidx (stocknumber);"); - print "Upgrade to $DBversion done (Change items.stocknumber to be not unique)\n"; + stocknumber_checker(); + print "Upgrade to $DBversion done (5860 Index itemstocknumber)\n"; SetVersion ($DBversion); } +sub stocknumber_checker { #code reused later on + my @row; + #drop the obsolete itemSStocknumber idx if it exists + @row = $dbh->selectrow_array("SHOW INDEXES FROM items WHERE key_name='itemsstocknumberidx'"); + $dbh->do("ALTER TABLE `items` DROP INDEX `itemsstocknumberidx`;") if @row; + + #check itemstocknumber idx; remove it if it is unique + @row = $dbh->selectrow_array("SHOW INDEXES FROM items WHERE key_name='itemstocknumberidx' AND non_unique=0"); + $dbh->do("ALTER TABLE `items` DROP INDEX `itemstocknumberidx`;") if @row; + + #add itemstocknumber index non-unique IF it still not exists + @row = $dbh->selectrow_array("SHOW INDEXES FROM items WHERE key_name='itemstocknumberidx'"); + $dbh->do("ALTER TABLE items ADD INDEX itemstocknumberidx (stocknumber);") unless @row; +} + $DBversion = '3.02.07.000'; if (C4::Context->preference("Version") < TransformToNum($DBversion)) { print "Upgrade to $DBversion done (Incrementing version for 3.2.7 release. See release notes for details.)\n"; @@ -4031,9 +4045,9 @@ if (C4::Context->preference("Version") < TransformToNum($DBversion)) { $DBversion = '3.02.07.001'; if (C4::Context->preference("Version") < TransformToNum($DBversion)) { - $dbh->do("ALTER TABLE `items` DROP INDEX `itemsstocknumberidx`;"); - $dbh->do("ALTER TABLE items ADD INDEX itemstocknumberidx (stocknumber);"); - print "Upgrade to $DBversion done (Change items.stocknumber to be not unique)\n"; + #follow up fix 5860: some installs already past 3.2.6.1 + stocknumber_checker(); + print "Upgrade to $DBversion done (Fix for stocknumber index)\n"; SetVersion ($DBversion); } @@ -4074,6 +4088,14 @@ if (C4::Context->preference("Version") < TransformToNum($DBversion)) { SetVersion ($DBversion); } +$DBversion = '3.02.09.XXX'; +if (C4::Context->preference("Version") < TransformToNum($DBversion)) { + #follow up fix 5860: some installs already past 3.2.6.1/3.2.7.1 + stocknumber_checker(); + print "Upgrade to $DBversion done (Fix for stocknumber index)\n"; + SetVersion ($DBversion); +} + =head1 FUNCTIONS =head2 DropAllForeignKeys($table) -- 1.6.0.6