From 9ba4ac90fca842b4e18e2e9f8838018dbe7267ad Mon Sep 17 00:00:00 2001 From: Lucas Gass Date: Thu, 13 Nov 2025 14:55:40 +0000 Subject: [PATCH] Bug 41246: Add foreign key constraints to overduerules table and cascade on delete To test: 0. APPLY PATCH, updatedatabase, restart_all 1. Add or have a patron category. 2. Go to Tools > Overdue notice/status triggers 3. Set some rules for that category. 4. Delete the patron category 5. Now do "select distinct categorycode from overduerules;" 6. All rules should be gone. --- .../data/mysql/atomicupdate/bug_41246.pl | 87 +++++++++++++++++++ installer/data/mysql/kohastructure.sql | 4 +- 2 files changed, 90 insertions(+), 1 deletion(-) create mode 100755 installer/data/mysql/atomicupdate/bug_41246.pl diff --git a/installer/data/mysql/atomicupdate/bug_41246.pl b/installer/data/mysql/atomicupdate/bug_41246.pl new file mode 100755 index 00000000000..e581ca6c3f2 --- /dev/null +++ b/installer/data/mysql/atomicupdate/bug_41246.pl @@ -0,0 +1,87 @@ +use Modern::Perl; +use Koha::Installer::Output qw(say_warning say_success say_info); + +return { + bug_number => "41246", + description => "Add foreign key constraints to overduerules table and cascade on delete", + up => sub { + my ($args) = @_; + my ( $dbh, $out ) = @{$args}{qw(dbh out)}; + + # Check for and report orphaned branchcode records + my $orphaned_branch = $dbh->selectrow_array( + q{ + SELECT COUNT(*) FROM overduerules + WHERE branchcode != '' + AND branchcode NOT IN (SELECT branchcode FROM branches) + } + ); + + if ($orphaned_branch) { + say_warning( + $out, + "Found $orphaned_branch orphaned overduerules records with invalid branchcode - removing" + ); + $dbh->do( + q{ + DELETE FROM overduerules + WHERE branchcode != '' + AND branchcode NOT IN (SELECT branchcode FROM branches) + } + ); + } + + # Check for and report orphaned categorycode records + my $orphaned_category = $dbh->selectrow_array( + q{ + SELECT COUNT(*) FROM overduerules + WHERE categorycode != '' + AND categorycode NOT IN (SELECT categorycode FROM categories) + } + ); + + if ($orphaned_category) { + say_warning( + $out, + "Found $orphaned_category orphaned overduerules records with invalid categorycode - removing" + ); + $dbh->do( + q{ + DELETE FROM overduerules + WHERE categorycode != '' + AND categorycode NOT IN (SELECT categorycode FROM categories) + } + ); + } + + # Add foreign key for branchcode + unless ( foreign_key_exists( 'overduerules', 'overduerules_ibfk_branchcode' ) ) { + $dbh->do( + q{ + ALTER TABLE overduerules + ADD CONSTRAINT overduerules_ibfk_branchcode + FOREIGN KEY (branchcode) + REFERENCES branches(branchcode) + ON DELETE CASCADE + ON UPDATE CASCADE + } + ); + say_success( $out, "Added foreign key constraint overduerules.branchcode -> branches.branchcode" ); + } + + # Add foreign key for categorycode + unless ( foreign_key_exists( 'overduerules', 'overduerules_ibfk_categorycode' ) ) { + $dbh->do( + q{ + ALTER TABLE overduerules + ADD CONSTRAINT overduerules_ibfk_categorycode + FOREIGN KEY (categorycode) + REFERENCES categories(categorycode) + ON DELETE CASCADE + ON UPDATE CASCADE + } + ); + say_success( $out, "Added foreign key constraint overduerules.categorycode -> categories.categorycode" ); + } + }, +}; diff --git a/installer/data/mysql/kohastructure.sql b/installer/data/mysql/kohastructure.sql index e5bb429ecec..9777e706c0f 100644 --- a/installer/data/mysql/kohastructure.sql +++ b/installer/data/mysql/kohastructure.sql @@ -5206,7 +5206,9 @@ CREATE TABLE `overduerules` ( `letter3` varchar(20) DEFAULT NULL COMMENT 'foreign key from the letter table to define which notice should be sent as the third notice', `debarred3` tinyint(1) DEFAULT 0 COMMENT 'is the patron restricted when the third notice is sent (1 for yes, 0 for no)', PRIMARY KEY (`overduerules_id`), - UNIQUE KEY `overduerules_branch_cat` (`branchcode`,`categorycode`) + UNIQUE KEY `overduerules_branch_cat` (`branchcode`,`categorycode`), + CONSTRAINT `overduerules_ibfk_branchcode` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE, + CONSTRAINT `overduerules_ibfk_categorycode` FOREIGN KEY (`categorycode`) REFERENCES `categories` (`categorycode`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- 2.39.5