@@ -, +, @@ --- installer/data/mysql/kohastructure.sql | 3 ++- installer/data/mysql/updatedatabase.pl | 20 ++++++++++++++++++++ t/db_dependent/Suggestions.t | 1 + 3 files changed, 23 insertions(+), 1 deletion(-) --- a/installer/data/mysql/kohastructure.sql +++ a/installer/data/mysql/kohastructure.sql @@ -2183,7 +2183,8 @@ CREATE TABLE `suggestions` ( -- purchase suggestions total DECIMAL(28,6) default NULL, -- suggested total cost (price*quantity updated for currency) PRIMARY KEY (`suggestionid`), KEY `suggestedby` (`suggestedby`), - KEY `managedby` (`managedby`) + KEY `managedby` (`managedby`), + CONSTRAINT `suggestions_budget_id_fk` FOREIGN KEY (`budgetid`) REFERENCES `aqbudgets` (`budget_id`) ON DELETE SET NULL ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8; -- --- a/installer/data/mysql/updatedatabase.pl +++ a/installer/data/mysql/updatedatabase.pl @@ -8781,6 +8781,26 @@ if ( CheckVersion($DBversion) ) { 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) --- a/t/db_dependent/Suggestions.t +++ a/t/db_dependent/Suggestions.t @@ -78,6 +78,7 @@ is( $suggestion->{publishercode}, $my_suggestion->{publishercode}, 'NewSuggestio is( $suggestion->{suggestedby}, $my_suggestion->{suggestedby}, 'NewSuggestion stores the borrower number correctly' ); is( $suggestion->{biblionumber}, $my_suggestion->{biblionumber}, 'NewSuggestion stores the biblio number correctly' ); is( $suggestion->{STATUS}, 'ASKED', 'NewSuggestion stores a suggestion with the status ASKED by default' ); +is( $suggestion->{budgetid}, undef, 'NewSuggestion should set budgetid to NULL if not given' ); is( CountSuggestion('ASKED'), 1, 'CountSuggestion returns the correct number of suggestions' ); --