From 668311e7fcaf412c1c503e006c6be65005fe2ce1 Mon Sep 17 00:00:00 2001
From: Pedro Amorim <pedro.amorim@openfifth.co.uk>
Date: Tue, 3 Jun 2025 11:17:15 +0000
Subject: [PATCH] Bug 40057: Fix db revision

Test plan (ktd):
1) reset_all
2) Run first part of the 241200017.pl atomicupdate manually
	koha-mysql kohadev
	ALTER TABLE illrequests DROP FOREIGN KEY illrequests_bnfk;
3) Create an ILL request linked to a borrower that doesn't exist (simulate old data):
	INSERT INTO illrequests (`backend`, `borrowernumber`,`branchcode` ) VALUES ('Standard', '99999', 'CPL');
4) Run the 2nd part of the atomicupdate manually:
	ALTER TABLE illrequests ADD CONSTRAINT illrequests_bnfk
	FOREIGN KEY()
	REFERENCES  ()
	ON DELETE SET NULL ON UPDATE CASCADE;
5) Notice you get the error:
	ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (., CONSTRAINT  FOREIGN KEY () REFERENCES  () ON DELETE SET NULL ON UPDATE CASCADE)
6) Run the added query in the patch:
	UPDATE illrequests SET borrowernumber = NULL WHERE borrowernumber NOT IN (SELECT borrowernumber FROM borrowers);
7) Repeat step 4)
8) Notice there's no errors
---
 installer/data/mysql/db_revs/241200017.pl | 16 ++++++++++++++++
 1 file changed, 16 insertions(+)

diff --git a/installer/data/mysql/db_revs/241200017.pl b/installer/data/mysql/db_revs/241200017.pl
index 13f4bf332fc..058ef04dd90 100755
--- a/installer/data/mysql/db_revs/241200017.pl
+++ b/installer/data/mysql/db_revs/241200017.pl
@@ -8,6 +8,22 @@ return {
         my ($args) = @_;
         my ( $dbh, $out ) = @$args{qw(dbh out)};
 
+        my $sth = $dbh->prepare(
+            q{
+            SELECT COUNT(*) FROM illrequests WHERE borrowernumber NOT IN (SELECT borrowernumber FROM borrowers)
+        }
+        );
+        $sth->execute;
+        my $count = $sth->fetchrow_array;
+
+        if ($count) {
+            say_warning( $out, "Found $count illrequests with a borrowernumber that doesn't exist in borrowers" );
+            $dbh->do(
+                q{ UPDATE illrequests SET borrowernumber = NULL WHERE borrowernumber NOT IN (SELECT borrowernumber FROM borrowers) }
+            );
+            say_info( $out, "Set those borrowernumbers to NULL" );
+        }
+
         $dbh->do(q{ ALTER TABLE illrequests DROP FOREIGN KEY illrequests_bnfk });
         $dbh->do(
             q{
-- 
2.39.5