From 311afaaf91bb55443a76729c64833cd8343f5692 Mon Sep 17 00:00:00 2001 From: Jonathan Druart Date: Mon, 29 Sep 2014 16:16:23 +0200 Subject: [PATCH] Bug 13007: Add a foreign key for suggestions.budgetid The DB field suggestions.budgetid should be linked to aqbudgets.budget_id. If the fund is removed, this column should be set to NULL. Test plan: 1/ Using your SQL CLI (or equivalent), create or update 1+ suggestions and set "0" in the budgetid field (or a nonexistent budget id). 2/ Execute the updatedabase script. 3/ Verify that your suggestion is unlinked to the nonexistent fund. 4/ Verify the constraint has correctly been added (show create table suggestions). 5/ Check that this change does not affect the behavior on adding a suggestion (linked to a fund or not). Signed-off-by: Paola Rossi Signed-off-by: Katrin Fischer Signed-off-by: Tomas Cohen Arazi --- installer/data/mysql/kohastructure.sql | 3 ++- installer/data/mysql/updatedatabase.pl | 20 ++++++++++++++++++++ t/db_dependent/Suggestions.t | 4 +++- 3 files changed, 25 insertions(+), 2 deletions(-) diff --git a/installer/data/mysql/kohastructure.sql b/installer/data/mysql/kohastructure.sql index 0d2700be6d..cd83f99c03 100644 --- a/installer/data/mysql/kohastructure.sql +++ b/installer/data/mysql/kohastructure.sql @@ -2212,7 +2212,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 COLLATE=utf8_unicode_ci; -- diff --git a/installer/data/mysql/updatedatabase.pl b/installer/data/mysql/updatedatabase.pl index 7e00df3573..c6ddc230f2 100755 --- a/installer/data/mysql/updatedatabase.pl +++ b/installer/data/mysql/updatedatabase.pl @@ -9732,6 +9732,26 @@ if ( CheckVersion($DBversion) ) { SetVersion($DBversion); } +$DBversion = "3.19.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) diff --git a/t/db_dependent/Suggestions.t b/t/db_dependent/Suggestions.t index a2377f9f94..73f10e6f9d 100644 --- a/t/db_dependent/Suggestions.t +++ b/t/db_dependent/Suggestions.t @@ -25,7 +25,7 @@ use C4::Budgets; use Koha::DateUtils qw( dt_from_string ); -use Test::More tests => 101; +use Test::More tests => 102; use Test::Warn; BEGIN { @@ -148,6 +148,8 @@ is( $suggestion->{biblionumber}, $my_suggestion->{biblionumber}, 'NewSuggestion is( $suggestion->{STATUS}, 'ASKED', 'NewSuggestion stores a suggestion with the status ASKED by default' ); is( $suggestion->{managedby}, undef, 'NewSuggestion stores empty string as undef for non existent foreign key (integer)' ); is( $suggestion->{manageddate}, undef, 'NewSuggestion stores empty string as undef for date' ); +is( $suggestion->{budgetid}, undef, 'NewSuggestion should set budgetid to NULL if not given' ); + is( CountSuggestion('ASKED'), 2, 'CountSuggestion returns the correct number of suggestions' ); -- 2.11.4.GIT