From ffe81694fd12ff44dd24aa13bdd88509ee8de08e Mon Sep 17 00:00:00 2001 From: David Cook Date: Tue, 15 Jul 2025 01:46:59 +0000 Subject: [PATCH] Bug 15516: (follow-up) explicitly define indexes for foreign keys This patch explicitly defines indexes for foreign keys. This isn't really needed for the atomic update, but we've included it for consistency. It is needed in kohastructure.sql, because MySQL/MariaDB silently/implicitly adds these indexes for foreign keys, which means that the database audit tool will show a discrepancy between kohastructure.sql and the installed DB schema. Also fix a discrepancy between atomic update and kohastructure.sql Test plan: 0. Apply the patch 1. Use both methods to update the DB schema 2. Go to http://localhost:8081/cgi-bin/koha/about.pl?tab=database 3. Note that about.pl isn't telling you to drop the indexes for reserves_ibfk_hg and old_reserves_ibfk_hg now Signed-off-by: Andrew Fuerste Henry --- .../data/mysql/atomicupdate/bug_15516_-_hold_group_table.pl | 4 +++- installer/data/mysql/kohastructure.sql | 2 ++ 2 files changed, 5 insertions(+), 1 deletion(-) diff --git a/installer/data/mysql/atomicupdate/bug_15516_-_hold_group_table.pl b/installer/data/mysql/atomicupdate/bug_15516_-_hold_group_table.pl index 7640282c8c5..a928ce1b2bd 100755 --- a/installer/data/mysql/atomicupdate/bug_15516_-_hold_group_table.pl +++ b/installer/data/mysql/atomicupdate/bug_15516_-_hold_group_table.pl @@ -19,6 +19,7 @@ return { 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 KEY reserves_ibfk_hg (hold_group_id)}); $dbh->do( q{ALTER TABLE reserves ADD CONSTRAINT reserves_ibfk_hg FOREIGN KEY (hold_group_id) REFERENCES hold_groups (hold_group_id) ON DELETE SET NULL ON UPDATE CASCADE} ); @@ -27,8 +28,9 @@ return { 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 KEY old_reserves_ibfk_hg (hold_group_id)}); $dbh->do( - q{ALTER TABLE old_reserves ADD CONSTRAINT old_reserves_ibfk_hg FOREIGN KEY (hold_group_id) REFERENCES hold_groups (hold_group_id) ON DELETE SET NULL ON UPDATE CASCADE} + q{ALTER TABLE old_reserves ADD CONSTRAINT old_reserves_ibfk_hg FOREIGN KEY (hold_group_id) REFERENCES hold_groups (hold_group_id) ON DELETE SET NULL ON UPDATE SET NULL} ); } }, diff --git a/installer/data/mysql/kohastructure.sql b/installer/data/mysql/kohastructure.sql index d852609f5d6..838249c2d66 100644 --- a/installer/data/mysql/kohastructure.sql +++ b/installer/data/mysql/kohastructure.sql @@ -5113,6 +5113,7 @@ CREATE TABLE `old_reserves` ( KEY `old_reserves_branchcode` (`branchcode`), KEY `old_reserves_itemtype` (`itemtype`), KEY `old_reserves_ibfk_ig` (`item_group_id`), + KEY `old_reserves_ibfk_hg` (`hold_group_id`), CONSTRAINT `old_reserves_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE SET NULL ON UPDATE SET NULL, CONSTRAINT `old_reserves_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE SET NULL ON UPDATE SET NULL, CONSTRAINT `old_reserves_ibfk_3` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE SET NULL ON UPDATE SET NULL, @@ -5686,6 +5687,7 @@ CREATE TABLE `reserves` ( KEY `desk_id` (`desk_id`), KEY `itemtype` (`itemtype`), KEY `reserves_ibfk_ig` (`item_group_id`), + KEY `reserves_ibfk_hg` (`hold_group_id`), CONSTRAINT `reserves_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `reserves_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `reserves_ibfk_3` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE CASCADE ON UPDATE CASCADE, -- 2.39.5