From 5ffed95821b1acf80a6f437552f7503e919f38d2 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 f35fadecf1b..a1a4afe12af 100644 --- a/installer/data/mysql/kohastructure.sql +++ b/installer/data/mysql/kohastructure.sql @@ -5112,6 +5112,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