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