From bf5c5773aabfac41e9fa69cc28e3c6664e0be2f2 Mon Sep 17 00:00:00 2001 From: Katrin Fischer Date: Sun, 29 Oct 2023 14:14:37 +0000 Subject: [PATCH] Bug 34520: Fix FK for item_groups in reserves for new installations There was a discrepancy between the database update for reserves and the kohastructure.sql definition. This makes sure that the FK is always "ON DELETE SET NULL". To test: * Before applying this path * sudo koha-mysql kohadev * show create table reserves; CONSTRAINT `reserves_ibfk_ig` FOREIGN KEY (`item_group_id`) REFERENCES `item_groups` (`item_group_id`) ON DELETE CASCADE ON UPDATE CASCADE * Apply patch * Run database update * Check database again: CONSTRAINT `reserves_ibfk_ig` FOREIGN KEY (`item_group_id`) REFERENCES `item_groups` (`item_group_id`) ON DELETE SET NULL ON UPDATE CASCADE * reset_all (create a new database) * Check database again: CONSTRAINT `reserves_ibfk_ig` FOREIGN KEY (`item_group_id`) REFERENCES `item_groups` (`item_group_id`) ON DELETE SET NULL ON UPDATE CASCADE Signed-off-by: David Nind Signed-off-by: Nick Clemens --- .../atomicupdate/bug_34520_item_groups_fk.pl | 30 +++++++++++++++++++ installer/data/mysql/kohastructure.sql | 2 +- 2 files changed, 31 insertions(+), 1 deletion(-) create mode 100755 installer/data/mysql/atomicupdate/bug_34520_item_groups_fk.pl diff --git a/installer/data/mysql/atomicupdate/bug_34520_item_groups_fk.pl b/installer/data/mysql/atomicupdate/bug_34520_item_groups_fk.pl new file mode 100755 index 00000000000..215dfdb84d6 --- /dev/null +++ b/installer/data/mysql/atomicupdate/bug_34520_item_groups_fk.pl @@ -0,0 +1,30 @@ +use Modern::Perl; + +return { + bug_number => "34520", + description => "Correct item_groups FK in reserves table", + up => sub { + my ($args) = @_; + my ( $dbh, $out ) = @$args{qw(dbh out)}; + + if ( foreign_key_exists( 'reserves', 'reserves_ibfk_ig' ) ) { + $dbh->do( + q| + ALTER TABLE reserves + DROP FOREIGN KEY reserves_ibfk_ig + | + ); + + $dbh->do( + q| + ALTER TABLE reserves + ADD CONSTRAINT reserves_ibfk_ig + FOREIGN KEY (item_group_id) + REFERENCES item_groups (item_group_id) ON DELETE SET NULL ON UPDATE CASCADE + | + ); + } + + say $out "FK 'reserves_ibfk_ig' on reserves updated to ON DELETE SET NULL"; + }, +}; diff --git a/installer/data/mysql/kohastructure.sql b/installer/data/mysql/kohastructure.sql index 479c3c55dd4..3eaeb629e61 100644 --- a/installer/data/mysql/kohastructure.sql +++ b/installer/data/mysql/kohastructure.sql @@ -5182,7 +5182,7 @@ CREATE TABLE `reserves` ( 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_ig` FOREIGN KEY (`item_group_id`) REFERENCES `item_groups` (`item_group_id`) ON DELETE CASCADE ON UPDATE CASCADE + CONSTRAINT `reserves_ibfk_ig` FOREIGN KEY (`item_group_id`) REFERENCES `item_groups` (`item_group_id`) ON DELETE SET NULL ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- 2.30.2