Attachment #33643 for bug #13007

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

Lines 2212-2218 CREATE TABLE `suggestions` ( -- purchase suggestions Link Here

(-)a/installer/data/mysql/kohastructure.sql (-1 / +2 lines)
2212	total DECIMAL(28,6) default NULL, -- suggested total cost (price*quantity updated for currency)	2212	total DECIMAL(28,6) default NULL, -- suggested total cost (price*quantity updated for currency)
2213	PRIMARY KEY (`suggestionid`),	2213	PRIMARY KEY (`suggestionid`),
2214	KEY `suggestedby` (`suggestedby`),	2214	KEY `suggestedby` (`suggestedby`),
2215	KEY `managedby` (`managedby`)	2215	KEY `managedby` (`managedby`),
		2216	CONSTRAINT `suggestions_budget_id_fk` FOREIGN KEY (`budgetid`) REFERENCES `aqbudgets` (`budget_id`) ON DELETE SET NULL ON UPDATE CASCADE
2216	) ENGINE=InnoDB DEFAULT CHARSET=utf8;	2217	) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2217		2218
2218	--	2219	--




    SetVersion($DBversion);
}

$DBversion = "3.17.00.XXX";
if ( CheckVersion($DBversion) ) {
    $dbh->do(q|
        UPDATE suggestions s SET s.budgetid = NULL
        WHERE NOT EXISTS (
            SELECT NULL
            FROM aqbudgets b
            WHERE b.budget_id = s.budgetid
        );
    |);

    $dbh->do(q|
        ALTER TABLE suggestions
        ADD CONSTRAINT suggestions_budget_id_fk FOREIGN KEY (budgetid) REFERENCES aqbudgets(budget_id) ON DELETE SET NULL ON UPDATE CASCADE
    |);

    print "Upgrade to $DBversion done (Bug 13007 - Add new foreign key suggestions.budgetid)\n";
    SetVersion($DBversion);
}

=head1 FUNCTIONS

=head2 TableExists($table)

Lines 23-29 use C4::Letters; Link Here

(-)a/t/db_dependent/Suggestions.t (-2 / +3 lines)
23		23
24	use Koha::DateUtils qw( dt_from_string );	24	use Koha::DateUtils qw( dt_from_string );
25		25
26	use Test::More tests => 97;	26	use Test::More tests => 98;
27	use Test::Warn;	27	use Test::Warn;
28		28
29	BEGIN {	29	BEGIN {
Lines 85-90 is( $suggestion->{biblionumber}, $my_suggestion->{biblionumber}, 'NewSuggestion Link Here
85	is( $suggestion->{STATUS}, 'ASKED', 'NewSuggestion stores a suggestion with the status ASKED by default' );	85	is( $suggestion->{STATUS}, 'ASKED', 'NewSuggestion stores a suggestion with the status ASKED by default' );
86	is( $suggestion->{managedby}, undef, 'NewSuggestion stores empty string as undef for non existent foreign key (integer)' );	86	is( $suggestion->{managedby}, undef, 'NewSuggestion stores empty string as undef for non existent foreign key (integer)' );
87	is( $suggestion->{manageddate}, undef, 'NewSuggestion stores empty string as undef for date' );	87	is( $suggestion->{manageddate}, undef, 'NewSuggestion stores empty string as undef for date' );
		88	is( $suggestion->{budgetid}, undef, 'NewSuggestion should set budgetid to NULL if not given' );
		89
88	is( CountSuggestion('ASKED'), 1, 'CountSuggestion returns the correct number of suggestions' );	90	is( CountSuggestion('ASKED'), 1, 'CountSuggestion returns the correct number of suggestions' );
89		91
90		92
91	-

Return to bug 13007

Lines 9525-9530 if ( CheckVersion($DBversion) ) { Link Here

(-)a/installer/data/mysql/updatedatabase.pl (+20 lines)
9525	SetVersion($DBversion);	9525	SetVersion($DBversion);
9526	}	9526	}
9527		9527
		9528	$DBversion = "3.17.00.XXX";
		9529	if ( CheckVersion($DBversion) ) {
		9530	$dbh->do(q\|
		9531	UPDATE suggestions s SET s.budgetid = NULL
		9532	WHERE NOT EXISTS (
		9533	SELECT NULL
		9534	FROM aqbudgets b
		9535	WHERE b.budget_id = s.budgetid
		9536	);
		9537	\|);
		9538
		9539	$dbh->do(q\|
		9540	ALTER TABLE suggestions
		9541	ADD CONSTRAINT suggestions_budget_id_fk FOREIGN KEY (budgetid) REFERENCES aqbudgets(budget_id) ON DELETE SET NULL ON UPDATE CASCADE
		9542	\|);
		9543
		9544	print "Upgrade to $DBversion done (Bug 13007 - Add new foreign key suggestions.budgetid)\n";
		9545	SetVersion($DBversion);
		9546	}
		9547
9528	=head1 FUNCTIONS	9548	=head1 FUNCTIONS
9529		9549
9530	=head2 TableExists($table)	9550	=head2 TableExists($table)