Attachment 169294 Details for Bug 18086 – Bug 18086: (follow-up) Update atomic update

[patch] Bug 18086: (follow-up) Update atomic update

Bug-18086-follow-up-Update-atomic-update.patch (text/plain), 5.61 KB, created by Baptiste Wojtkowski (bwoj) on 2024-07-22 14:04:10 UTC

(hide)

Description:

Filename:

MIME Type:

Creator: Baptiste Wojtkowski (bwoj)

Created: 2024-07-22 14:04:10 UTC

Size: 5.61 KB

patch

obsolete

>From 35338a354084c33921f608ff2dd8afd4fb9f8363 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

Actions: View | Diff | Splinter Review

Attachments on bug 18086: 105368 | 105369 | 105380 | 105382 | 105387 | 105388 | 105424 | 105551 | 106201 | 134730 | 134731 | 168810 | 168811 | 168812 | 168813 | 168814 | 168815 | 169287 | 169288 | 169289 | 169290 | 169291 | 169292 | 169293 | 169294 | 169295 | 169296 | 169297 | 169298 | 169299 | 169300 | 169301 | 169302 | 169396 | 169397 | 169398 | 169399 | 169400 | 169401 | 169402 | 169403 | 169404 | 169455 | 169456 | 169457 | 169458 | 169459 | 169460 | 169461 | 169462 | 169463 | 169543 | 169544