Bugzilla – Attachment 189565 Details for
Bug 41246
Deleting patron categories or libraries can leave orphaned rules in the overduerules table
Home
|
New
|
Browse
|
Search
|
[?]
|
Reports
|
Help
|
New Account
|
Log In
[x]
|
Forgot Password
Login:
[x]
[patch]
Bug 41246: Add foreign key constraints to overduerules table and cascade on delete
Bug-41246-Add-foreign-key-constraints-to-overdueru.patch (text/plain), 5.16 KB, created by
Lucas Gass (lukeg)
on 2025-11-13 15:05:55 UTC
(
hide
)
Description:
Bug 41246: Add foreign key constraints to overduerules table and cascade on delete
Filename:
MIME Type:
Creator:
Lucas Gass (lukeg)
Created:
2025-11-13 15:05:55 UTC
Size:
5.16 KB
patch
obsolete
>From 9ba4ac90fca842b4e18e2e9f8838018dbe7267ad Mon Sep 17 00:00:00 2001 >From: Lucas Gass <lucas@bywatersolutions.com> >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
You cannot view the attachment while viewing its details because your browser does not support IFRAMEs.
View the attachment on a separate page
.
View Attachment As Diff
View Attachment As Raw
Actions:
View
|
Diff
|
Splinter Review
Attachments on
bug 41246
:
189564
| 189565 |
189566