From 57046b92a0ce9fa14320ca830f23e78ba7f10bc1 Mon Sep 17 00:00:00 2001
From: Tomas Cohen Arazi <tomascohen@theke.io>
Date: Mon, 29 Jul 2024 08:12:56 -0300
Subject: [PATCH] Bug 37419: Update FK constraint on record sources to avoid
 data loss

Without this patch, deleting a record source will delete the associated
biblio_metadata rows, which is a severe data loss.

This patch makes the constraint restrict this action.

To test:
1. Add a record source
2. Set the record source to some records
   $ koha-mysql kohadev
   > UPDATE biblio_metadata SET record_source_id='your source id' WHERE
biblionumber=1;
3. Delete the record source
=> FAIL: Record metadata deleted
4. Apply this patch
5, Run:
   $ ktd --shell
  k$ updatedatabase
=> SUCCESS: DB update goes well
6. Repeat 1~3 with another record
=> SUCCESS: Source cannot be deleted if there are linked records

Signed-off-by: Tomas Cohen Arazi <tomascohen@theke.io>
Signed-off-by: Janusz Kaczmarek <januszop@gmail.com>
Signed-off-by: Martin Renvoize <martin.renvoize@ptfs-europe.com>
Signed-off-by: Katrin Fischer <katrin.fischer@bsz-bw.de>
(cherry picked from commit 8754458775da37acfc2513cc0c1b2523740fe79b)
Signed-off-by: Lucas Gass <lucas@bywatersolutions.com>
---
 .../data/mysql/atomicupdate/bug_37419.pl      | 29 +++++++++++++++++++
 installer/data/mysql/kohastructure.sql        |  2 +-
 2 files changed, 30 insertions(+), 1 deletion(-)
 create mode 100755 installer/data/mysql/atomicupdate/bug_37419.pl

diff --git a/installer/data/mysql/atomicupdate/bug_37419.pl b/installer/data/mysql/atomicupdate/bug_37419.pl
new file mode 100755
index 0000000000..b39b42a10b
--- /dev/null
+++ b/installer/data/mysql/atomicupdate/bug_37419.pl
@@ -0,0 +1,29 @@
+use Modern::Perl;
+use Koha::Installer::Output qw(say_warning say_failure say_success say_info);
+
+return {
+    bug_number  => "37419",
+    description => "Replace FK constraint to avoid data loss",
+    up          => sub {
+        my ($args) = @_;
+        my ( $dbh, $out ) = @$args{qw(dbh out)};
+
+        # Drop table constraint
+        if ( foreign_key_exists( 'biblio_metadata', 'record_metadata_fk_2' ) ) {
+            $dbh->do(
+                q{
+            ALTER TABLE biblio_metadata DROP FOREIGN KEY record_metadata_fk_2
+            }
+            );
+        }
+
+        $dbh->do(
+            q{
+            ALTER TABLE biblio_metadata
+            ADD CONSTRAINT `record_metadata_fk_2` FOREIGN KEY (`record_source_id`) REFERENCES `record_sources` (`record_source_id`) ON DELETE RESTRICT ON UPDATE CASCADE
+        }
+        );
+
+        say_success( $out, "Updated foreign key 'biblio_metadata.record_metadata_fk_2'" );
+    },
+};
diff --git a/installer/data/mysql/kohastructure.sql b/installer/data/mysql/kohastructure.sql
index 91c1bec42f..4d1f1c10ef 100644
--- a/installer/data/mysql/kohastructure.sql
+++ b/installer/data/mysql/kohastructure.sql
@@ -1144,7 +1144,7 @@ CREATE TABLE `biblio_metadata` (
   KEY `timestamp` (`timestamp`),
   KEY `record_metadata_fk_2` (`record_source_id`),
   CONSTRAINT `record_metadata_fk_1` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE,
-  CONSTRAINT `record_metadata_fk_2` FOREIGN KEY (`record_source_id`) REFERENCES `record_sources` (`record_source_id`) ON DELETE CASCADE ON UPDATE CASCADE
+  CONSTRAINT `record_metadata_fk_2` FOREIGN KEY (`record_source_id`) REFERENCES `record_sources` (`record_source_id`) ON DELETE RESTRICT ON UPDATE CASCADE
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
 /*!40101 SET character_set_client = @saved_cs_client */;
 
-- 
2.39.5