Attachment 153799 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.48 KB, created by Katrin Fischer on 2023-07-21 15:11:31 UTC

(hide)

Description:

Filename:

MIME Type:

Creator: Katrin Fischer

Created: 2023-07-21 15:11:31 UTC

Size: 3.48 KB

patch

obsolete

>From cba10492559a12928bbf755203a02abc7947cb00 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
>---
> ...1_do_not_delete_orders_with_subscription.pl | 18 ++++++++++++++++++
> installer/data/mysql/kohastructure.sql         |  2 +-
> 2 files changed, 19 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 0000000000..dccda200ad
>--- /dev/null
>+++ b/installer/data/mysql/atomicupdate/bug_30451_do_not_delete_orders_with_subscription.pl
>@@ -0,0 +1,18 @@
>+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 270cca9f50..aca4a2d227 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.30.2

Actions: View | Diff | Splinter Review

Attachments on bug 30451: 153799 | 153818 | 153966