Attachment 158041 Details for Bug 34520 – Bug 34520: Fix FK for item_groups in reserves for new installations

[patch] Bug 34520: Fix FK for item_groups in reserves for new installations

Bug-34520-Fix-FK-for-itemgroups-in-reserves-for-ne.patch (text/plain), 3.47 KB, created by Katrin Fischer on 2023-10-29 14:19:36 UTC

(hide)

Description:

Filename:

MIME Type:

Creator: Katrin Fischer

Created: 2023-10-29 14:19:36 UTC

Size: 3.47 KB

patch

obsolete

>From 94bfd154fabfbc15909c1c020fed257f208c56dd Mon Sep 17 00:00:00 2001
>From: Katrin Fischer <katrin.fischer.83@web.de>
>Date: Sun, 29 Oct 2023 14:14:37 +0000
>Subject: [PATCH] Bug 34520: Fix FK for item_groups in reserves for new
> installations
>
>There was a discrepancy between the database update for reserves
>and the kohastructure.sql definition. This makes sure that the
>FK is always "ON DELETE SET NULL".
>
>To test:
>* Before applying this path
>* sudo koha-mysql kohadev
>* show create table reserves;
>
>CONSTRAINT `reserves_ibfk_ig` FOREIGN KEY (`item_group_id`) REFERENCES `item_groups` (`item_group_id`) ON DELETE CASCADE ON UPDATE CASCADE
>
>* Apply patch
>* Run database update
>* Check database again:
>
>CONSTRAINT `reserves_ibfk_ig` FOREIGN KEY (`item_group_id`) REFERENCES `item_groups` (`item_group_id`) ON DELETE SET NULL ON UPDATE CASCADE
>
>* reset_all (create a new database)
>* Check database again:
>
>CONSTRAINT `reserves_ibfk_ig` FOREIGN KEY (`item_group_id`) REFERENCES `item_groups` (`item_group_id`) ON DELETE SET NULL ON UPDATE CASCADE
>---
> .../atomicupdate/bug_34520_item_groups_fk.pl  | 30 +++++++++++++++++++
> installer/data/mysql/kohastructure.sql        |  2 +-
> 2 files changed, 31 insertions(+), 1 deletion(-)
> create mode 100755 installer/data/mysql/atomicupdate/bug_34520_item_groups_fk.pl
>
>diff --git a/installer/data/mysql/atomicupdate/bug_34520_item_groups_fk.pl b/installer/data/mysql/atomicupdate/bug_34520_item_groups_fk.pl
>new file mode 100755
>index 0000000000..215dfdb84d
>--- /dev/null
>+++ b/installer/data/mysql/atomicupdate/bug_34520_item_groups_fk.pl
>@@ -0,0 +1,30 @@
>+use Modern::Perl;
>+
>+return {
>+    bug_number  => "34520",
>+    description => "Correct item_groups FK in reserves table",
>+    up          => sub {
>+        my ($args) = @_;
>+        my ( $dbh, $out ) = @$args{qw(dbh out)};
>+
>+        if ( foreign_key_exists( 'reserves', 'reserves_ibfk_ig' ) ) {
>+            $dbh->do(
>+                q|
>+                ALTER TABLE reserves
>+                DROP FOREIGN KEY reserves_ibfk_ig
>+            |
>+            );
>+
>+            $dbh->do(
>+                q|
>+                ALTER TABLE reserves
>+                ADD CONSTRAINT reserves_ibfk_ig
>+                    FOREIGN KEY (item_group_id)
>+                    REFERENCES item_groups (item_group_id) ON DELETE SET NULL ON UPDATE CASCADE
>+            |
>+            );
>+        }
>+
>+        say $out "FK 'reserves_ibfk_ig' on reserves updated to ON DELETE SET NULL";
>+    },
>+};
>diff --git a/installer/data/mysql/kohastructure.sql b/installer/data/mysql/kohastructure.sql
>index 479c3c55dd..3eaeb629e6 100644
>--- a/installer/data/mysql/kohastructure.sql
>+++ b/installer/data/mysql/kohastructure.sql
>@@ -5182,7 +5182,7 @@ CREATE TABLE `reserves` (
>   CONSTRAINT `reserves_ibfk_4` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE,
>   CONSTRAINT `reserves_ibfk_5` FOREIGN KEY (`itemtype`) REFERENCES `itemtypes` (`itemtype`) ON DELETE CASCADE ON UPDATE CASCADE,
>   CONSTRAINT `reserves_ibfk_6` FOREIGN KEY (`desk_id`) REFERENCES `desks` (`desk_id`) ON DELETE SET NULL ON UPDATE CASCADE,
>-  CONSTRAINT `reserves_ibfk_ig` FOREIGN KEY (`item_group_id`) REFERENCES `item_groups` (`item_group_id`) ON DELETE CASCADE ON UPDATE CASCADE
>+  CONSTRAINT `reserves_ibfk_ig` FOREIGN KEY (`item_group_id`) REFERENCES `item_groups` (`item_group_id`) ON DELETE SET NULL ON UPDATE CASCADE
> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
> /*!40101 SET character_set_client = @saved_cs_client */;
> 
>-- 
>2.30.2

Actions: View | Diff | Splinter Review

Attachments on bug 34520: 158041 | 158052 | 158080