From dd31ff7dfaf388a79922137ff45980a62b6d0862 Mon Sep 17 00:00:00 2001 From: Aleisha Amohia Date: Tue, 4 Jun 2019 02:55:55 +0000 Subject: [PATCH] Bug 15516: (follow-up) Fixing atomic update to prevent errors This patch prevents errors when applying atomic update, particularly for those that have already tested this feature before. --- .../bug_15516_-_hold_group_table.perl | 35 ++++++++++++++----- installer/data/mysql/kohastructure.sql | 2 +- 2 files changed, 28 insertions(+), 9 deletions(-) diff --git a/installer/data/mysql/atomicupdate/bug_15516_-_hold_group_table.perl b/installer/data/mysql/atomicupdate/bug_15516_-_hold_group_table.perl index c9128ca008..a968ea601b 100644 --- a/installer/data/mysql/atomicupdate/bug_15516_-_hold_group_table.perl +++ b/installer/data/mysql/atomicupdate/bug_15516_-_hold_group_table.perl @@ -1,14 +1,33 @@ $DBversion = 'XXX'; if( CheckVersion( $DBversion ) ) { - $dbh->do(q{DROP TABLE IF EXISTS hold_group}); - $dbh->do(q{CREATE TABLE hold_group ( - hold_group_id int unsigned NOT NULL AUTO_INCREMENT, - PRIMARY KEY (hold_group_id) + + if( TableExists( 'hold_group' ) ){ + $dbh->do(q{UPDATE reserves SET hold_group_id = NULL}); + $dbh->do(q{UPDATE old_reserves SET hold_group_id = NULL}); + + $dbh->do(q{ALTER TABLE reserves DROP FOREIGN KEY reserves_ibfk_6}); + $dbh->do(q{ALTER TABLE old_reserves DROP FOREIGN KEY old_reserves_ibfk_5}); + + $dbh->do(q{DROP TABLE hold_group}); + $dbh->do(q{CREATE TABLE hold_group ( + hold_group_id int unsigned NOT NULL AUTO_INCREMENT, + PRIMARY KEY (hold_group_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci}); - $dbh->do(q{ALTER TABLE reserves ADD COLUMN hold_group_id int unsigned NULL DEFAULT NULL}); - $dbh->do(q{ALTER TABLE reserves ADD CONSTRAINT reserves_ibfk_6 FOREIGN KEY (hold_group_id) REFERENCES hold_group (hold_group_id) ON DELETE SET NULL ON UPDATE CASCADE}); - $dbh->do(q{ALTER TABLE old_reserves ADD COLUMN hold_group_id int unsigned NULL DEFAULT NULL}); - $dbh->do(q{ALTER TABLE old_reserves ADD CONSTRAINT old_reserves_ibfk_5 FOREIGN KEY (hold_group_id) REFERENCES hold_group (hold_group_id) ON DELETE SET NULL ON UPDATE CASCADE}); + } + + unless( column_exists( 'reserves', 'hold_group_id' ) ){ + $dbh->do(q{ALTER TABLE reserves ADD COLUMN hold_group_id int unsigned NULL DEFAULT NULL}); + } + unless( column_exists( 'old_reserves', 'hold_group_id') ){ + $dbh->do(q{ALTER TABLE old_reserves ADD COLUMN hold_group_id int unsigned NULL DEFAULT NULL}); + } + + unless( foreign_key_exists( 'reserves', 'reserves_ibfk_6' ) ){ + $dbh->do(q{ALTER TABLE reserves ADD CONSTRAINT reserves_ibfk_6 FOREIGN KEY (hold_group_id) REFERENCES hold_group (hold_group_id) ON DELETE SET NULL ON UPDATE CASCADE}); + } + unless( foreign_key_exists( 'old_reserves', 'old_reserves_ibfk_5' ) ){ + $dbh->do(q{ALTER TABLE old_reserves ADD CONSTRAINT old_reserves_ibfk_5 FOREIGN KEY (hold_group_id) REFERENCES hold_group (hold_group_id) ON DELETE SET NULL ON UPDATE CASCADE}); + } SetVersion( $DBversion ); print "Upgrade to $DBversion done (Bug 15516 - Adding hold group table)\n"; diff --git a/installer/data/mysql/kohastructure.sql b/installer/data/mysql/kohastructure.sql index b41034bc0a..3d1cfc1cb4 100644 --- a/installer/data/mysql/kohastructure.sql +++ b/installer/data/mysql/kohastructure.sql @@ -1884,7 +1884,7 @@ CREATE TABLE `old_reserves` ( -- this table holds all holds/reserves that have b ON DELETE SET NULL ON UPDATE SET NULL, CONSTRAINT `old_reserves_ibfk_4` FOREIGN KEY (`itemtype`) REFERENCES `itemtypes` (`itemtype`) ON DELETE SET NULL ON UPDATE SET NULL, - CONSTRAINT `old_reserves_ibfk_5` FOREIGN KEY (hold_group_id) REFERENCES hold_group (hold_group_id) + CONSTRAINT `old_reserves_ibfk_5` FOREIGN KEY (`hold_group_id`) REFERENCES `hold_group` (`hold_group_id`) ON DELETE SET NULL ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- 2.20.1