Attachment 173860 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), 8.37 KB, created by David Nind on 2024-11-01 09:59:54 UTC

(hide)

Description:

Filename:

MIME Type:

Creator: David Nind

Created: 2024-11-01 09:59:54 UTC

Size: 8.37 KB

patch

obsolete

>From ca835d4f611e1881bd182f9f2d9a9e44508b863a 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
>
>Signed-off-by: David Nind <david@davidnind.com>
>---
> Koha/Acquisition/Bookseller.pm                |  5 ++-
> Koha/Schema/Result/Aqbookseller.pm            | 19 +++++++++--
> Koha/Schema/Result/Subscription.pm            | 28 +++++++++++++---
> api/v1/swagger/definitions/vendor.yaml        |  3 ++
> .../swagger/paths/acquisitions_vendors.yaml   |  1 +
> .../bug_38011-add_fk_to_subscriptions.pl      | 32 +++++++++++++++++++
> installer/data/mysql/kohastructure.sql        |  6 ++--
> 7 files changed, 83 insertions(+), 11 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 50daff7fa2..098a0be19b 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/Koha/Schema/Result/Aqbookseller.pm b/Koha/Schema/Result/Aqbookseller.pm
>index f9ce48cae4..a4c1370b57 100644
>--- a/Koha/Schema/Result/Aqbookseller.pm
>+++ b/Koha/Schema/Result/Aqbookseller.pm
>@@ -494,6 +494,21 @@ __PACKAGE__->belongs_to(
>   },
> );
> 
>+=head2 subscriptions
>+
>+Type: has_many
>+
>+Related object: L<Koha::Schema::Result::Subscription>
>+
>+=cut
>+
>+__PACKAGE__->has_many(
>+  "subscriptions",
>+  "Koha::Schema::Result::Subscription",
>+  { "foreign.aqbooksellerid" => "self.id" },
>+  { cascade_copy => 0, cascade_delete => 0 },
>+);
>+
> =head2 vendor_edi_accounts
> 
> Type: has_many
>@@ -510,8 +525,8 @@ __PACKAGE__->has_many(
> );
> 
> 
>-# Created by DBIx::Class::Schema::Loader v0.07049 @ 2023-06-30 09:54:35
>-# DO NOT MODIFY THIS OR ANYTHING ABOVE! md5sum:xjeOqpcdN3Kb1wmLGDjzLg
>+# Created by DBIx::Class::Schema::Loader v0.07051 @ 2024-09-11 09:24:20
>+# DO NOT MODIFY THIS OR ANYTHING ABOVE! md5sum:BAp8LQbC16KTLoV2ZDtRsQ
> 
> __PACKAGE__->add_columns(
>     '+active' => { is_boolean => 1 },
>diff --git a/Koha/Schema/Result/Subscription.pm b/Koha/Schema/Result/Subscription.pm
>index cb5d1533f4..b148beaaed 100644
>--- a/Koha/Schema/Result/Subscription.pm
>+++ b/Koha/Schema/Result/Subscription.pm
>@@ -59,7 +59,7 @@ start date for this subscription
> =head2 aqbooksellerid
> 
>   data_type: 'integer'
>-  default_value: 0
>+  is_foreign_key: 1
>   is_nullable: 1
> 
> foreign key for aqbooksellers.id to link to the vendor
>@@ -353,7 +353,7 @@ __PACKAGE__->add_columns(
>   "startdate",
>   { data_type => "date", datetime_undef_if_invalid => 1, is_nullable => 1 },
>   "aqbooksellerid",
>-  { data_type => "integer", default_value => 0, is_nullable => 1 },
>+  { data_type => "integer", is_foreign_key => 1, is_nullable => 1 },
>   "cost",
>   { data_type => "integer", default_value => 0, is_nullable => 1 },
>   "aqbudgetid",
>@@ -450,6 +450,26 @@ __PACKAGE__->set_primary_key("subscriptionid");
> 
> =head1 RELATIONS
> 
>+=head2 aqbooksellerid
>+
>+Type: belongs_to
>+
>+Related object: L<Koha::Schema::Result::Aqbookseller>
>+
>+=cut
>+
>+__PACKAGE__->belongs_to(
>+  "aqbooksellerid",
>+  "Koha::Schema::Result::Aqbookseller",
>+  { id => "aqbooksellerid" },
>+  {
>+    is_deferrable => 1,
>+    join_type     => "LEFT",
>+    on_delete     => "SET NULL",
>+    on_update     => "CASCADE",
>+  },
>+);
>+
> =head2 aqorders
> 
> Type: has_many
>@@ -566,8 +586,8 @@ __PACKAGE__->has_many(
> );
> 
> 
>-# Created by DBIx::Class::Schema::Loader v0.07049 @ 2023-07-14 11:46:15
>-# DO NOT MODIFY THIS OR ANYTHING ABOVE! md5sum:LICIiWzag365cUAq5OYD2A
>+# Created by DBIx::Class::Schema::Loader v0.07051 @ 2024-09-11 09:24:20
>+# DO NOT MODIFY THIS OR ANYTHING ABOVE! md5sum:mi3Uxt+shIeD869VpcRV4A
> 
> __PACKAGE__->has_many(
>   "additional_field_values",
>diff --git a/api/v1/swagger/definitions/vendor.yaml b/api/v1/swagger/definitions/vendor.yaml
>index 3bf8a17977..6918d00469 100644
>--- a/api/v1/swagger/definitions/vendor.yaml
>+++ b/api/v1/swagger/definitions/vendor.yaml
>@@ -119,6 +119,9 @@ properties:
>     description: List of aliases
>     items:
>       $ref: "vendor_alias.yaml"
>+  subscriptions:
>+    type: array
>+    description: List of subscriptions
> additionalProperties: false
> required:
>   - name
>diff --git a/api/v1/swagger/paths/acquisitions_vendors.yaml b/api/v1/swagger/paths/acquisitions_vendors.yaml
>index ed817cc2a1..aaaed8c534 100644
>--- a/api/v1/swagger/paths/acquisitions_vendors.yaml
>+++ b/api/v1/swagger/paths/acquisitions_vendors.yaml
>@@ -35,6 +35,7 @@
>           type: string
>           enum:
>             - aliases
>+            - subscriptions
>         collectionFormat: csv
>     responses:
>       "200":
>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 0000000000..3e23724b92
>--- /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 442f683e02..04fd1ba536 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.39.5

Actions: View | Diff | Splinter Review

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