From ca835d4f611e1881bd182f9f2d9a9e44508b863a Mon Sep 17 00:00:00 2001 From: Matt Blenkinsop 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 --- 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 + +=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 + +=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