From 6b15dbed2a063a0148def62a8f56868e739e22fd Mon Sep 17 00:00:00 2001 From: Josef Moravec Date: Fri, 18 Dec 2020 10:58:44 +0000 Subject: [PATCH] Bug 15516: (follow-up) Fix db structure and atomic update --- .../atomicupdate/bug_15516_-_hold_group_table.perl | 28 +++++++--------------- installer/data/mysql/kohastructure.sql | 6 ++--- 2 files changed, 11 insertions(+), 23 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 a968ea601b..2cd1bf75ee 100644 --- a/installer/data/mysql/atomicupdate/bug_15516_-_hold_group_table.perl +++ b/installer/data/mysql/atomicupdate/bug_15516_-_hold_group_table.perl @@ -1,34 +1,22 @@ $DBversion = 'XXX'; if( CheckVersion( $DBversion ) ) { - 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}); + unless( TableExists( '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}); } - 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( column_exists( 'reserves', 'hold_group_id' ) ) { + $dbh->do(q{ALTER TABLE reserves ADD COLUMN hold_group_id int unsigned NULL DEFAULT NULL AFTER non_priority}); + $dbh->do(q{ALTER TABLE reserves ADD CONSTRAINT reserves_ibfk_hold_group FOREIGN KEY (hold_group_id) REFERENCES hold_group (hold_group_id) ON DELETE SET NULL ON UPDATE CASCADE}); } - 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}); + 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 AFTER non_priority}); + $dbh->do(q{ALTER TABLE old_reserves ADD CONSTRAINT old_reserves_ibfk_hold_group 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"; + NewVersion( $DBversion, 15516, "Adding hold group table"); } diff --git a/installer/data/mysql/kohastructure.sql b/installer/data/mysql/kohastructure.sql index c21b0d19e7..db6c3b1334 100644 --- a/installer/data/mysql/kohastructure.sql +++ b/installer/data/mysql/kohastructure.sql @@ -1704,7 +1704,7 @@ CREATE TABLE `old_issues` ( -- lists items that were checked out and have been r ON DELETE SET NULL ON UPDATE SET NULL, CONSTRAINT `old_issues_ibfk_2` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE SET NULL ON UPDATE SET NULL, - CONSTRAINT `old_issues_ibfk_borrowers_borrowernumber` FOREIGN KEY (`issuer_id`) REFERENCES `borrowers` (`borrowernumber`), + CONSTRAINT `old_issues_ibfk_borrowers_borrowernumber` FOREIGN KEY (`issuer_id`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE SET NULL ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; @@ -1838,7 +1838,7 @@ CREATE TABLE `reserves` ( -- information related to holds/reserves in Koha CONSTRAINT `reserves_ibfk_4` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `reserves_ibfk_5` FOREIGN KEY (`itemtype`) REFERENCES `itemtypes` (`itemtype`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `reserves_ibfk_6` FOREIGN KEY (`desk_id`) REFERENCES `desks` (`desk_id`) ON DELETE SET NULL ON UPDATE CASCADE, - CONSTRAINT `reserves_ibfk_7` FOREIGN KEY (`hold_group_id`) REFERENCES `hold_group` (`hold_group_id`) ON DELETE SET NULL ON UPDATE CASCADE + CONSTRAINT `reserves_ibfk_hold_group` 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; -- @@ -1885,7 +1885,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_hold_group` 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.11.0