Attachment 153966 Details for Bug 30451 – Bug 30451: Update FK constraint on aqorders.subscriptionid

[patch] Bug 30451: Update FK constraint on aqorders.subscriptionid

Bug-30451-Update-FK-constraint-on-aqorderssubscrip.patch (text/plain), 3.70 KB, created by Jonathan Druart on 2023-07-27 12:17:32 UTC

(hide)

Description:

Filename:

MIME Type:

Creator: Jonathan Druart

Created: 2023-07-27 12:17:32 UTC

Size: 3.70 KB

patch

obsolete

>From 4fdbda8b5d0b10e6bf804d883e2c731e5b409008 Mon Sep 17 00:00:00 2001
>From: Katrin Fischer <katrin.fischer@bsz-bw.de>
>Date: Fri, 21 Jul 2023 14:39:07 +0000
>Subject: [PATCH] Bug 30451: Update FK constraint on aqorders.subscriptionid
>
>This updates the FK constrant from ON DELETE CASCADE to ON DELETE
>SET NULL. This means that if a subscription linked to an order is
>deleted, we no longer will also delete the order, but we will just
>set subscrptinid in the order to NULL. This will avoid data loss
>that can cause the budgets/funds not to add up anymore with the
>real espenses of the library.
>
>To test:
>
>Preparation:
>* Create 2 subscriptions on different records
>* Create a new basket
>* Use the "order from subscription" functionality to create order
>  lines for both of your subscriptions
>* Close basket
>
>Without patch:
>* Delete the first subscription
>* Verify the order line for this subscription is gone from your basket
>
>Apply patch:
>* Run database update and restart_all
>* Delete the second subscription
>* Verify the order line now remained in the basket
>
>Signed-off-by: Michaela Sieber <michaela.sieber@kit.edu>
>
>JD amended patch: perl tidy
>
>Signed-off-by: Jonathan Druart <jonathan.druart@bugs.koha-community.org>
>---
> ..._do_not_delete_orders_with_subscription.pl | 22 +++++++++++++++++++
> installer/data/mysql/kohastructure.sql        |  2 +-
> 2 files changed, 23 insertions(+), 1 deletion(-)
> create mode 100755 installer/data/mysql/atomicupdate/bug_30451_do_not_delete_orders_with_subscription.pl
>
>diff --git a/installer/data/mysql/atomicupdate/bug_30451_do_not_delete_orders_with_subscription.pl b/installer/data/mysql/atomicupdate/bug_30451_do_not_delete_orders_with_subscription.pl
>new file mode 100755
>index 00000000000..bb3b7747fcc
>--- /dev/null
>+++ b/installer/data/mysql/atomicupdate/bug_30451_do_not_delete_orders_with_subscription.pl
>@@ -0,0 +1,22 @@
>+use Modern::Perl;
>+
>+return {
>+    bug_number  => "30451",
>+    description => "Update FK constraint on aqorders.subscriptionid to ON DELETE SET NULL",
>+    up          => sub {
>+        my ($args) = @_;
>+        my ( $dbh, $out ) = @$args{qw(dbh out)};
>+
>+        if ( foreign_key_exists( 'aqorders', 'aqorders_subscriptionid' ) ) {
>+            $dbh->do(q{
>+                ALTER TABLE aqorders
>+                DROP FOREIGN KEY aqorders_subscriptionid
>+            });
>+        }
>+        $dbh->do(q{
>+            ALTER TABLE aqorders
>+            ADD CONSTRAINT `aqorders_subscriptionid` FOREIGN KEY (`subscriptionid`) REFERENCES subscription(`subscriptionid`) ON DELETE SET NULL ON UPDATE CASCADE
>+        });
>+        say $out "Update FK constraint on subscriptionid";
>+    },
>+};
>diff --git a/installer/data/mysql/kohastructure.sql b/installer/data/mysql/kohastructure.sql
>index c1f800fe7f6..6f4fa288294 100644
>--- a/installer/data/mysql/kohastructure.sql
>+++ b/installer/data/mysql/kohastructure.sql
>@@ -773,7 +773,7 @@ CREATE TABLE `aqorders` (
>   CONSTRAINT `aqorders_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE SET NULL ON UPDATE CASCADE,
>   CONSTRAINT `aqorders_ibfk_3` FOREIGN KEY (`invoiceid`) REFERENCES `aqinvoices` (`invoiceid`) ON DELETE SET NULL ON UPDATE CASCADE,
>   CONSTRAINT `aqorders_invoice_currency` FOREIGN KEY (`invoice_currency`) REFERENCES `currency` (`currency`) ON DELETE SET NULL ON UPDATE SET NULL,
>-  CONSTRAINT `aqorders_subscriptionid` FOREIGN KEY (`subscriptionid`) REFERENCES `subscription` (`subscriptionid`) ON DELETE CASCADE ON UPDATE CASCADE
>+  CONSTRAINT `aqorders_subscriptionid` FOREIGN KEY (`subscriptionid`) REFERENCES `subscription` (`subscriptionid`) 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.25.1

Actions: View | Diff | Splinter Review

Attachments on bug 30451: 153799 | 153818 | 153966