Attachment 174180 Details for Bug 38011 – Bug 38011: Add a foreign key between vendors and subscriptions

[patch] Bug 38011: Add a foreign key between vendors and subscriptions

Bug-38011-Add-a-foreign-key-between-vendors-and-su.patch (text/plain), 4.75 KB, created by Martin Renvoize (ashimema) on 2024-11-07 12:10:19 UTC

(hide)

Description:

Filename:

MIME Type:

Creator: Martin Renvoize (ashimema)

Created: 2024-11-07 12:10:19 UTC

Size: 4.75 KB

patch

obsolete

>From 59b09ffbb9423e0789d246234bb05ee7005ff352 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>
>---
> 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..098a0be19bd 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 $booksellers_rs = $self->_result->subscriptions;
>+    return Koha::Subscriptions->_new_from_dbic($booksellers_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.47.0

Actions: View | Diff | Splinter Review

Attachments on bug 38011: 171971 | 171972 | 173859 | 173860 | 174180 | 174181 | 174182 | 174183 | 174185 | 174186 | 174187 | 174188 | 174397 | 174492