From b5365977d88506ba74e2b9202615fd172af465ec Mon Sep 17 00:00:00 2001 From: Pedro Amorim Date: Wed, 29 Oct 2025 17:56:49 +0000 Subject: [PATCH] Bug 40529: (QA follow-up): Fix DB changes Add UNIQUE KEY entries to table definition The UNIQUE KEY are required as a hold group 'might have' a target hold, not 'has many' target holds. This update and the other things this patch updates in atomicupdate + kohastructure should fix the error thrown by command: perl /kohadevbox/misc4dev/run_tests.pl --instance=kohadev --db-password=password --run-db-compare-only --compare-with=HEAD This command no longer produces any errors. Also in this patch: Renamed table from 'hold_group_target_holds' to 'hold_groups_target_holds' and move it alphabetically to be after 'hold_groups' in kohastructure.sql This is cleaner, as it matches the 'hold_groups' related table, and allows for alphabetical sorting on kohastructure.sql that makes sense i.e. hold_groups is created first, then hold_group_target_holds is created second with an FK to the former. All of this passes: prove ./t/db_dependent/Koha/Hold.t prove ./t/db_dependent/Reserves/HoldGroup.t prove ./t/db_dependent/Circulation* prove ./t/db_dependent/Hold* prove ./t/db_dependent/Reserves* --- Koha/Hold.pm | 4 +-- Koha/HoldGroup.pm | 8 ++--- .../bug_40529_-_hold_group_table_target.pl | 14 ++++---- installer/data/mysql/kohastructure.sql | 33 ++++++++++--------- 4 files changed, 32 insertions(+), 27 deletions(-) diff --git a/Koha/Hold.pm b/Koha/Hold.pm index f88b7621180..66a236a853c 100644 --- a/Koha/Hold.pm +++ b/Koha/Hold.pm @@ -284,7 +284,7 @@ sub set_as_hold_group_target { if ( $self->hold_group && !$self->hold_group->target_hold_id && C4::Context->preference("DisplayAddHoldGroups") ) { $self->_result->create_related( - 'hold_group_target_hold', + 'hold_groups_target_hold', { hold_group_id => $self->hold_group->hold_group_id } ); } @@ -308,7 +308,7 @@ sub remove_as_hold_group_target { && C4::Context->preference("DisplayAddHoldGroups") ) { $self->_result->find_related( - 'hold_group_target_hold', + 'hold_groups_target_hold', { hold_group_id => $self->hold_group->hold_group_id, reserve_id => $self->reserve_id } )->delete(); } diff --git a/Koha/HoldGroup.pm b/Koha/HoldGroup.pm index ff7ad9df5b1..d1c0afc5fad 100644 --- a/Koha/HoldGroup.pm +++ b/Koha/HoldGroup.pm @@ -89,9 +89,9 @@ Return the target_hold_id sub target_hold_id { my ($self) = @_; - return unless $self->_result->hold_group_target_hold; + return unless $self->_result->hold_groups_target_hold; - return $self->_result->hold_group_target_hold->reserve_id; + return $self->_result->hold_groups_target_hold->reserve_id; } =head3 skip_non_target_holds_query @@ -116,12 +116,12 @@ sub skip_non_target_holds_query { -and => { '!=' => undef }, { -not_in => \ - 'SELECT hold_group_id FROM hold_group_target_holds WHERE reserve_id IS NOT NULL AND reserve_id != me.reserve_id' + 'SELECT hold_group_id FROM hold_groups_target_holds WHERE reserve_id IS NOT NULL AND reserve_id != me.reserve_id' } ] ], 'sql' => - 'AND (hold_group_id IS NULL OR reserves.hold_group_id NOT IN (SELECT hold_group_id FROM hold_group_target_holds WHERE reserve_id IS NOT NULL AND reserve_id != reserves.reserve_id))' + 'AND (hold_group_id IS NULL OR reserves.hold_group_id NOT IN (SELECT hold_group_id FROM hold_groups_target_holds WHERE reserve_id IS NOT NULL AND reserve_id != reserves.reserve_id))' }; return unless $query_type && exists $query_types->{$query_type}; diff --git a/installer/data/mysql/atomicupdate/bug_40529_-_hold_group_table_target.pl b/installer/data/mysql/atomicupdate/bug_40529_-_hold_group_table_target.pl index bff6bd7504b..3f677c0d7ca 100755 --- a/installer/data/mysql/atomicupdate/bug_40529_-_hold_group_table_target.pl +++ b/installer/data/mysql/atomicupdate/bug_40529_-_hold_group_table_target.pl @@ -8,19 +8,21 @@ return { my ($args) = @_; my ( $dbh, $out ) = @$args{qw(dbh out)}; - unless ( TableExists('hold_group_target_holds') ) { + unless ( TableExists('hold_groups_target_holds') ) { $dbh->do( q{ - CREATE TABLE hold_group_target_holds ( - `hold_group_id` int unsigned NOT NULL UNIQUE COMMENT 'foreign key, linking this to the hold_groups table', - `reserve_id` int(11) DEFAULT NULL UNIQUE COMMENT 'foreign key, linking this to the reserves table', - PRIMARY KEY (`hold_group_id`, `reserve_id`), + CREATE TABLE `hold_groups_target_holds` ( + `hold_group_id` int(10) unsigned NOT NULL COMMENT 'foreign key, linking this to the hold_groups table', + `reserve_id` int(11) NOT NULL COMMENT 'foreign key, linking this to the reserves table', + PRIMARY KEY (`hold_group_id`,`reserve_id`), + UNIQUE KEY `uq_hold_group_target_holds_hold_group_id` (`hold_group_id`), + UNIQUE KEY `uq_hold_group_target_holds_reserve_id` (`reserve_id`), CONSTRAINT `hold_group_target_holds_ibfk_1` FOREIGN KEY (`hold_group_id`) REFERENCES `hold_groups` (`hold_group_id`) ON DELETE CASCADE, CONSTRAINT `hold_group_target_holds_ibfk_2` FOREIGN KEY (`reserve_id`) REFERENCES `reserves` (`reserve_id`) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; } ); - say_success( $out, "Added table 'hold_group_target_holds'" ); + say_success( $out, "Added table 'hold_groups_target_holds'" ); } }, }; diff --git a/installer/data/mysql/kohastructure.sql b/installer/data/mysql/kohastructure.sql index 56def5468e2..c3a2f73480d 100644 --- a/installer/data/mysql/kohastructure.sql +++ b/installer/data/mysql/kohastructure.sql @@ -3560,6 +3560,24 @@ CREATE TABLE `hold_groups` ( ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; +-- +-- Table structure for table `hold_groups_target_holds` +-- + +DROP TABLE IF EXISTS `hold_groups_target_holds`; +/*!40101 SET @saved_cs_client = @@character_set_client */; +/*!40101 SET character_set_client = utf8mb4 */; +CREATE TABLE `hold_groups_target_holds` ( + `hold_group_id` int(10) unsigned NOT NULL COMMENT 'foreign key, linking this to the hold_groups table', + `reserve_id` int(11) NOT NULL COMMENT 'foreign key, linking this to the reserves table', + PRIMARY KEY (`hold_group_id`,`reserve_id`), + UNIQUE KEY `uq_hold_group_target_holds_hold_group_id` (`hold_group_id`), + UNIQUE KEY `uq_hold_group_target_holds_reserve_id` (`reserve_id`), + CONSTRAINT `hold_group_target_holds_ibfk_1` FOREIGN KEY (`hold_group_id`) REFERENCES `hold_groups` (`hold_group_id`) ON DELETE CASCADE, + CONSTRAINT `hold_group_target_holds_ibfk_2` FOREIGN KEY (`reserve_id`) REFERENCES `reserves` (`reserve_id`) ON DELETE CASCADE +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; +/*!40101 SET character_set_client = @saved_cs_client */; + -- -- Table structure for table `housebound_profile` -- @@ -5113,21 +5131,6 @@ CREATE TABLE `old_issues` ( ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; --- --- Table structure for table `hold_group_target_holds` --- -DROP TABLE IF EXISTS `hold_group_target_holds`; -/*!40101 SET @saved_cs_client = @@character_set_client */; -/*!40101 SET character_set_client = utf8 */; -CREATE TABLE hold_group_target_holds ( - `hold_group_id` int unsigned NOT NULL UNIQUE COMMENT 'foreign key, linking this to the hold_groups table', - `reserve_id` int(11) DEFAULT NULL UNIQUE COMMENT 'foreign key, linking this to the reserves table', - PRIMARY KEY (`hold_group_id`, `reserve_id`), - CONSTRAINT `hold_group_target_holds_ibfk_1` FOREIGN KEY (`hold_group_id`) REFERENCES `hold_groups` (`hold_group_id`) ON DELETE CASCADE, - CONSTRAINT `hold_group_target_holds_ibfk_2` FOREIGN KEY (`reserve_id`) REFERENCES `reserves` (`reserve_id`) ON DELETE CASCADE -) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -/*!40101 SET character_set_client = @saved_cs_client */; - -- -- Table structure for table `old_reserves` -- -- 2.39.5