Attachment #174185 for bug #38011

View | Details | Raw Unified | Return to bug 38011
Collapse All | Expand All

Lines 74-82 Returns the list of subscriptions for the vendor Link Here

(-)a/Koha/Acquisition/Bookseller.pm (-3 / +2 lines)
74		74
75	sub subscriptions {	75	sub subscriptions {
76	my ($self) = @_;	76	my ($self) = @_;
77		77	my $rs = $self->_result->subscriptions;
78	# FIXME FK missing at DB level	78	return Koha::Subscriptions->_new_from_dbic($rs);
79	return Koha::Subscriptions->search( { aqbooksellerid => $self->id } );
80	}	79	}
81		80
82	=head3 aliases	81	=head3 aliases




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" );
        }
    },
};




  `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 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)',

  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_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 */;

- 

Return to bug 38011

Line 0 Link Here

(-)a/installer/data/mysql/atomicupdate/bug_38011-add_fk_to_subscriptions.pl (+32 lines)
	1	use Modern::Perl;
	2	use Koha::Installer::Output qw(say_warning say_failure say_success say_info);
	3
	4	return {
	5	bug_number => "38011",
	6	description => "Add missing foreign key to subscription table",
	7	up => sub {
	8	my ($args) = @_;
	9	my ( $dbh, $out ) = @$args{qw(dbh out)};
	10
	11	unless ( foreign_key_exists( 'subscription', 'subscription_ibfk_4' ) ) {
	12
	13	$dbh->do(
	14	q{
	15	ALTER TABLE subscription MODIFY COLUMN aqbooksellerid int(11) DEFAULT NULL COMMENT 'foreign key for aqbooksellers.id to link to the vendor'
	16	}
	17	);
	18
	19	$dbh->do(
	20	q\|
	21	ALTER TABLE subscription
	22	ADD CONSTRAINT subscription_ibfk_4
	23	FOREIGN KEY (aqbooksellerid)
	24	REFERENCES aqbooksellers (id) ON DELETE SET NULL ON UPDATE CASCADE
	25	\|
	26	);
	27	say_success( $out, "Updated foreign key 'subscription_ibfk_4'" );
	28	} else {
	29	say_warning( $out, "Foreign key 'subscription_ibfk_4' already exists" );
	30	}
	31	},
	32	};

Lines 6059-6065 CREATE TABLE `subscription` ( Link Here

(-)a/installer/data/mysql/kohastructure.sql (-3 / +4 lines)
6059	`subscriptionid` int(11) NOT NULL AUTO_INCREMENT COMMENT 'unique key for this subscription',	6059	`subscriptionid` int(11) NOT NULL AUTO_INCREMENT COMMENT 'unique key for this subscription',
6060	`librarian` varchar(100) DEFAULT '' COMMENT 'the librarian''s username from borrowers.userid',	6060	`librarian` varchar(100) DEFAULT '' COMMENT 'the librarian''s username from borrowers.userid',
6061	`startdate` date DEFAULT NULL COMMENT 'start date for this subscription',	6061	`startdate` date DEFAULT NULL COMMENT 'start date for this subscription',
6062	`aqbooksellerid` int(11) DEFAULT 0 COMMENT 'foreign key for aqbooksellers.id to link to the vendor',	6062	`aqbooksellerid` int(11) DEFAULT NULL COMMENT 'foreign key for aqbooksellers.id to link to the vendor',
6063	`cost` int(11) DEFAULT 0,	6063	`cost` int(11) DEFAULT 0,
6064	`aqbudgetid` int(11) DEFAULT 0,	6064	`aqbudgetid` int(11) DEFAULT 0,
6065	`weeklength` int(11) DEFAULT 0 COMMENT 'subscription length in weeks (will not be filled in if monthlength or numberlength is set)',	6065	`weeklength` int(11) DEFAULT 0 COMMENT 'subscription length in weeks (will not be filled in if monthlength or numberlength is set)',
Lines 6104-6112 CREATE TABLE `subscription` ( Link Here
6104	KEY `subscription_ibfk_1` (`periodicity`),	6104	KEY `subscription_ibfk_1` (`periodicity`),
6105	KEY `subscription_ibfk_2` (`numberpattern`),	6105	KEY `subscription_ibfk_2` (`numberpattern`),
6106	KEY `subscription_ibfk_3` (`biblionumber`),	6106	KEY `subscription_ibfk_3` (`biblionumber`),
		6107	KEY `subscription_ibfk_4` (`aqbooksellerid`),
6107	CONSTRAINT `subscription_ibfk_1` FOREIGN KEY (`periodicity`) REFERENCES `subscription_frequencies` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,	6108	CONSTRAINT `subscription_ibfk_1` FOREIGN KEY (`periodicity`) REFERENCES `subscription_frequencies` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
6108	CONSTRAINT `subscription_ibfk_2` FOREIGN KEY (`numberpattern`) REFERENCES `subscription_numberpatterns` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,	6109	CONSTRAINT `subscription_ibfk_2` FOREIGN KEY (`numberpattern`) REFERENCES `subscription_numberpatterns` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
6109	CONSTRAINT `subscription_ibfk_3` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE	6110	CONSTRAINT `subscription_ibfk_3` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE,
		6111	CONSTRAINT `subscription_ibfk_4` FOREIGN KEY (`aqbooksellerid`) REFERENCES `aqbooksellers` (`id`) ON DELETE SET NULL ON UPDATE CASCADE
6110	) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;	6112	) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
6111	/!40101 SET character_set_client = @saved_cs_client /;	6113	/!40101 SET character_set_client = @saved_cs_client /;
6112		6114
6113	-