From 26725539fd988385fa88031cb384a0212e5c868a Mon Sep 17 00:00:00 2001
From: Matt Blenkinsop <matt.blenkinsop@ptfs-europe.com>
Date: Thu, 26 Sep 2024 13:30:02 +0000
Subject: [PATCH] Bug 38011: Add a foreign key between vendors and
 subscriptions

Test plan:
prove t/db_dependent/Koha/Subscription.t
prove t/db_dependent/Koha/Acquisition/Booksellers.t

Signed-off-by: David Nind <david@davidnind.com>
Signed-off-by: Martin Renvoize <martin.renvoize@ptfs-europe.com>

Amended-by: Jonathan Druart
Rename $rs

Signed-off-by: Jonathan Druart <jonathan.druart@bugs.koha-community.org>
---
 Koha/Acquisition/Bookseller.pm                |  5 ++-
 .../bug_38011-add_fk_to_subscriptions.pl      | 32 +++++++++++++++++++
 installer/data/mysql/kohastructure.sql        |  6 ++--
 3 files changed, 38 insertions(+), 5 deletions(-)
 create mode 100755 installer/data/mysql/atomicupdate/bug_38011-add_fk_to_subscriptions.pl

diff --git a/Koha/Acquisition/Bookseller.pm b/Koha/Acquisition/Bookseller.pm
index 50daff7fa25..dd101e713e1 100644
--- a/Koha/Acquisition/Bookseller.pm
+++ b/Koha/Acquisition/Bookseller.pm
@@ -74,9 +74,8 @@ Returns the list of subscriptions for the vendor
 
 sub subscriptions {
     my ($self) = @_;
-
-    # FIXME FK missing at DB level
-    return Koha::Subscriptions->search( { aqbooksellerid => $self->id } );
+    my $rs = $self->_result->subscriptions;
+    return Koha::Subscriptions->_new_from_dbic($rs);
 }
 
 =head3 aliases
diff --git a/installer/data/mysql/atomicupdate/bug_38011-add_fk_to_subscriptions.pl b/installer/data/mysql/atomicupdate/bug_38011-add_fk_to_subscriptions.pl
new file mode 100755
index 00000000000..3e23724b921
--- /dev/null
+++ b/installer/data/mysql/atomicupdate/bug_38011-add_fk_to_subscriptions.pl
@@ -0,0 +1,32 @@
+use Modern::Perl;
+use Koha::Installer::Output qw(say_warning say_failure say_success say_info);
+
+return {
+    bug_number  => "38011",
+    description => "Add missing foreign key to subscription table",
+    up          => sub {
+        my ($args) = @_;
+        my ( $dbh, $out ) = @$args{qw(dbh out)};
+
+        unless ( foreign_key_exists( 'subscription', 'subscription_ibfk_4' ) ) {
+
+            $dbh->do(
+                q{
+                ALTER TABLE subscription MODIFY COLUMN aqbooksellerid int(11) DEFAULT NULL COMMENT 'foreign key for aqbooksellers.id to link to the vendor'
+            }
+            );
+
+            $dbh->do(
+                q|
+                ALTER TABLE subscription
+                ADD CONSTRAINT  subscription_ibfk_4
+                    FOREIGN KEY (aqbooksellerid)
+                    REFERENCES aqbooksellers (id) ON DELETE SET NULL ON UPDATE CASCADE
+            |
+            );
+            say_success( $out, "Updated foreign key 'subscription_ibfk_4'" );
+        } else {
+            say_warning( $out, "Foreign key 'subscription_ibfk_4' already exists" );
+        }
+    },
+};
diff --git a/installer/data/mysql/kohastructure.sql b/installer/data/mysql/kohastructure.sql
index 442f683e02f..04fd1ba5368 100644
--- a/installer/data/mysql/kohastructure.sql
+++ b/installer/data/mysql/kohastructure.sql
@@ -6059,7 +6059,7 @@ CREATE TABLE `subscription` (
   `subscriptionid` int(11) NOT NULL AUTO_INCREMENT COMMENT 'unique key for this subscription',
   `librarian` varchar(100) DEFAULT '' COMMENT 'the librarian''s username from borrowers.userid',
   `startdate` date DEFAULT NULL COMMENT 'start date for this subscription',
-  `aqbooksellerid` int(11) DEFAULT 0 COMMENT 'foreign key for aqbooksellers.id to link to the vendor',
+  `aqbooksellerid` int(11) DEFAULT NULL COMMENT 'foreign key for aqbooksellers.id to link to the vendor',
   `cost` int(11) DEFAULT 0,
   `aqbudgetid` int(11) DEFAULT 0,
   `weeklength` int(11) DEFAULT 0 COMMENT 'subscription length in weeks (will not be filled in if monthlength or numberlength is set)',
@@ -6104,9 +6104,11 @@ CREATE TABLE `subscription` (
   KEY `subscription_ibfk_1` (`periodicity`),
   KEY `subscription_ibfk_2` (`numberpattern`),
   KEY `subscription_ibfk_3` (`biblionumber`),
+  KEY `subscription_ibfk_4` (`aqbooksellerid`),
   CONSTRAINT `subscription_ibfk_1` FOREIGN KEY (`periodicity`) REFERENCES `subscription_frequencies` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
   CONSTRAINT `subscription_ibfk_2` FOREIGN KEY (`numberpattern`) REFERENCES `subscription_numberpatterns` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
-  CONSTRAINT `subscription_ibfk_3` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE
+  CONSTRAINT `subscription_ibfk_3` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE,
+  CONSTRAINT `subscription_ibfk_4` FOREIGN KEY (`aqbooksellerid`) REFERENCES `aqbooksellers` (`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.34.1