From 331f188a4e1e6752b0f1802ad0b8a120df2dc2c9 Mon Sep 17 00:00:00 2001
From: Jonathan Druart <jonathan.druart@bugs.koha-community.org>
Date: Wed, 30 Aug 2023 16:21:37 +0200
Subject: [PATCH] Bug 34276: (bug 21983 follow-up) Fix db rev 23.06.00.002

Signed-off-by: Pedro Amorim <pedro.amorim@ptfs-europe.com>

Signed-off-by: Marcel de Rooy <m.de.rooy@rijksmuseum.nl>
Signed-off-by: Tomas Cohen Arazi <tomascohen@theke.io>
(cherry picked from commit 352a2afd0bf1b67c4c579d299c088604d131ae1a)
Signed-off-by: Fridolin Somers <fridolin.somers@biblibre.com>
(cherry picked from commit 10324fd58718e48b8caffbd73573842affdc4ad8)
Signed-off-by: Matt Blenkinsop <matt.blenkinsop@ptfs-europe.com>
---
 installer/data/mysql/db_revs/221105004.pl |  3 ++-
 installer/data/mysql/db_revs/221106005.pl | 26 +++++++++++++++++++++--
 2 files changed, 26 insertions(+), 3 deletions(-)

diff --git a/installer/data/mysql/db_revs/221105004.pl b/installer/data/mysql/db_revs/221105004.pl
index 7b840e51da..5726f50970 100755
--- a/installer/data/mysql/db_revs/221105004.pl
+++ b/installer/data/mysql/db_revs/221105004.pl
@@ -30,7 +30,8 @@ return {
         unless ( foreign_key_exists( 'illrequests', 'illrequests_bibfk' ) ) {
             $dbh->do(q{
                 ALTER TABLE illrequests
-                    ADD FOREIGN KEY illrequests_bibfk (`biblio_id`) REFERENCES `biblio` (`biblionumber`) ON DELETE SET NULL ON UPDATE CASCADE;
+                    ADD KEY `illrequests_bibfk` (`biblio_id`),
+                    ADD CONSTRAINT illrequests_bibfk FOREIGN KEY (`biblio_id`) REFERENCES `biblio` (`biblionumber`) ON DELETE SET NULL ON UPDATE CASCADE;
             });
 
             say $out "Added foreign key constraint 'illrequests.illrequests_bibfk'";
diff --git a/installer/data/mysql/db_revs/221106005.pl b/installer/data/mysql/db_revs/221106005.pl
index 2453185c68..21804ee49d 100755
--- a/installer/data/mysql/db_revs/221106005.pl
+++ b/installer/data/mysql/db_revs/221106005.pl
@@ -27,11 +27,33 @@ return {
                   AND illrequests.biblio_id IS NOT NULL
         });
 
-        unless ( foreign_key_exists( 'illrequests', 'illrequests_bibfk' ) ) {
+        if ( foreign_key_exists( 'illrequests', 'illrequests_ibfk_1' ) ) {
+            $dbh->do(q{
+                ALTER TABLE illrequests
+                DROP FOREIGN KEY illrequests_ibfk_1
+            });
+        }
+
+        if (   !foreign_key_exists( 'illrequests', 'illrequests_bibfk' )
+            || !index_exists('illrequests', 'illrequests_bibfk') ) {
+
+            if ( foreign_key_exists( 'illrequests', 'illrequests_bibfk' ) ) {
+                $dbh->do(q{
+                    ALTER TABLE illrequests
+                    DROP FOREIGN KEY illrequests_bibfk
+                });
+            }
+            if (index_exists('illrequests', 'illrequests_bibfk')) {
+                $dbh->do(q{
+                    ALTER TABLE illrequests
+                    DROP INDEX illrequests_bibfk
+                });
+            }
+
             $dbh->do(q{
                 ALTER TABLE illrequests
                     ADD KEY `illrequests_bibfk` (`biblio_id`),
-                    ADD FOREIGN KEY illrequests_bibfk (`biblio_id`) REFERENCES `biblio` (`biblionumber`) ON DELETE SET NULL ON UPDATE CASCADE;
+                    ADD CONSTRAINT illrequests_bibfk FOREIGN KEY (`biblio_id`) REFERENCES `biblio` (`biblionumber`) ON DELETE SET NULL ON UPDATE CASCADE;
             });
 
             say $out "Added foreign key constraint 'illrequests.illrequests_bibfk'";
-- 
2.39.2