Attachment 35601 Details for Bug 13007 – Bug 13007: Add a foreign key for suggestions.budgetid

[patch] Bug 13007: Add a foreign key for suggestions.budgetid

Bug-13007-Add-a-foreign-key-for-suggestionsbudgeti.patch (text/plain), 3.74 KB, created by Jonathan Druart on 2015-01-29 09:23:15 UTC

(hide)

Description:

Filename:

MIME Type:

Creator: Jonathan Druart

Created: 2015-01-29 09:23:15 UTC

Size: 3.74 KB

patch

obsolete

>From 6249666b2c2116239bea9a59b05b380015c47ba3 Mon Sep 17 00:00:00 2001
>From: Jonathan Druart <jonathan.druart@biblibre.com>
>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 <paola.rossi@cineca.it>
>
>Signed-off-by: Katrin Fischer <katrin.fischer.83@web.de>
>---
> 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 82868e7..3a9b3ed 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 4653c02..7b92bdd 100755
>--- a/installer/data/mysql/updatedatabase.pl
>+++ b/installer/data/mysql/updatedatabase.pl
>@@ -9669,6 +9669,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 927b6df..49c6c5c 100644
>--- a/t/db_dependent/Suggestions.t
>+++ b/t/db_dependent/Suggestions.t
>@@ -24,7 +24,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 {
>@@ -114,6 +114,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.1.0

Actions: View | Diff | Splinter Review

Attachments on bug 13007: 31924 | 31945 | 33241 | 33242 | 33643 | 33644 | 33651 | 33653 | 33807 | 33808 | 35601 | 35602