From a41268b29405d856860694db62efa1d53dc94dfe Mon Sep 17 00:00:00 2001
From: Baptiste Wojtkowski <bski@laposte.net>
Date: Mon, 22 Jul 2024 15:51:55 +0200
Subject: [PATCH] Bug 18086: (follow-up) Update atomic update

---
 .../data/mysql/atomicupdate/bug_18086.perl    | 38 ------------
 .../data/mysql/atomicupdate/bug_18086.pl      | 62 +++++++++++++++++++
 installer/data/mysql/kohastructure.sql        |  4 +-
 3 files changed, 64 insertions(+), 40 deletions(-)
 delete mode 100644 installer/data/mysql/atomicupdate/bug_18086.perl
 create mode 100755 installer/data/mysql/atomicupdate/bug_18086.pl

diff --git a/installer/data/mysql/atomicupdate/bug_18086.perl b/installer/data/mysql/atomicupdate/bug_18086.perl
deleted file mode 100644
index 123dbd0..0000000
--- a/installer/data/mysql/atomicupdate/bug_18086.perl
+++ /dev/null
@@ -1,38 +0,0 @@
-$DBversion = 'XXX'; # will be replaced by the RM
-if( CheckVersion( $DBversion ) ) {
-    $dbh->do(q{
-        UPDATE overduerules SET categorycode = NULL WHERE categorycode = "";
-    });
-
-    $dbh->do(q{
-        DELETE overduerules FROM overduerules
-        LEFT JOIN categories USING ( categorycode )
-        WHERE
-            overduerules.categorycode IS NOT NULL
-          AND
-            categories.categorycode IS NULL;
-    });
-
-    $dbh->do(q{
-        UPDATE overduerules SET branchcode = NULL WHERE branchcode = "";
-    });
-
-    $dbh->do(q{
-        DELETE overduerules FROM overduerules
-        LEFT JOIN branches USING ( branchcode )
-        WHERE
-            branches.branchcode IS NULL
-          AND
-            overduerules.branchcode IS NOT NULL
-    });
-
-    $dbh->do(q{
-        ALTER TABLE overduerules
-        MODIFY `branchcode` varchar(10) NULL default NULL,
-        MODIFY `categorycode` varchar(10) NOT NULL,
-        ADD CONSTRAINT `fk_branchcode` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE,
-        ADD CONSTRAINT `fk_categorycode` FOREIGN KEY (`categorycode`) REFERENCES `categories` (`categorycode`) ON DELETE CASCADE ON UPDATE CASCADE;
-    });
-
-    NewVersion( $DBversion, 18086, "Add FK constraints for branchcode and categorycode");
-}
diff --git a/installer/data/mysql/atomicupdate/bug_18086.pl b/installer/data/mysql/atomicupdate/bug_18086.pl
new file mode 100755
index 0000000..1a2fbac
--- /dev/null
+++ b/installer/data/mysql/atomicupdate/bug_18086.pl
@@ -0,0 +1,62 @@
+use Modern::Perl;
+use Koha::Installer::Output qw(say_warning say_failure say_success say_info);
+
+return {
+    bug_number  => "18086",
+    description => "Add foreign key constraints to table overduerule",
+    up          => sub {
+        my ($args) = @_;
+        my ( $dbh, $out ) = @$args{qw(dbh out)};
+
+        unless ( foreign_key_exists( 'overduerules', 'branchcode_ibfk' ) ) {
+            $dbh->do(q{
+                ALTER TABLE overduerules
+                MODIFY `branchcode` varchar(10) NULL default NULL;
+            });
+
+            $dbh->do(q{
+                UPDATE overduerules SET branchcode = NULL WHERE branchcode = "";
+            });
+
+            $dbh->do(q{
+                DELETE overduerules FROM overduerules
+                WHERE
+                    branchcode NOT IN (SELECT branchcode FROM branches);
+            });
+
+            $dbh->do(q{
+                ALTER TABLE overduerules
+                ADD CONSTRAINT `branchcode_ibfk` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE;
+            });
+            say $out "Added constraint to column of table 'overduerules.branchcode'";
+        }
+
+
+        unless ( foreign_key_exists( 'overduerules', 'categorycode_ibfk' ) ) {
+            $dbh->do(q{
+                ALTER TABLE overduerules
+                MODIFY `categorycode` varchar(10) NULL default NULL;
+            });
+
+            $dbh->do(q{
+                ALTER TABLE overduerules
+                ADD CONSTRAINT `categorycode_ibfk` FOREIGN KEY (`categorycode`) REFERENCES `categories` (`categorycode`) ON DELETE CASCADE ON UPDATE CASCADE;
+            });
+
+            $dbh->do(q{
+                UPDATE overduerules SET categorycode = NULL WHERE categorycode = "";
+            });
+
+            $dbh->do(q{
+                DELETE overduerules FROM overduerules
+                WHERE
+                    categorycode NOT IN (SELECT categorycode FROM categories);
+            });
+
+            say $out "Added constraint to column of table 'overduerules.categorycode'";
+
+
+        }
+
+    },
+};
diff --git a/installer/data/mysql/kohastructure.sql b/installer/data/mysql/kohastructure.sql
index 29554a3..8e7baf8 100644
--- a/installer/data/mysql/kohastructure.sql
+++ b/installer/data/mysql/kohastructure.sql
@@ -4975,8 +4975,8 @@ CREATE TABLE `overduerules` (
   `debarred3` int(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`),
-  CONSTRAINT `fk_branchcode` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE,
-  CONSTRAINT `fk_categorycode` FOREIGN KEY (`categorycode`) REFERENCES `categories` (`categorycode`) ON DELETE CASCADE ON UPDATE CASCADE
+  CONSTRAINT `branchcode_ibfk` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE,
+  CONSTRAINT `categorycode_ibfk` 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.43.0