From 475f785a6cd2f7b59155e6d8fa1af71eafcbc553 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:
0/ Verify it is not possible to create an suggestion not linked to a fund via
the Koha interface.
1/ Using your SQL CLI (or equivalent), create or update 1+ suggestions and
set
"0" in the budgetid field.
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).
---
 installer/data/mysql/kohastructure.sql |  3 ++-
 installer/data/mysql/updatedatabase.pl | 20 ++++++++++++++++++++
 2 files changed, 22 insertions(+), 1 deletion(-)

diff --git a/installer/data/mysql/kohastructure.sql b/installer/data/mysql/kohastructure.sql
index aeeba17..baaeb15 100644
--- a/installer/data/mysql/kohastructure.sql
+++ b/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;
 
 --
diff --git a/installer/data/mysql/updatedatabase.pl b/installer/data/mysql/updatedatabase.pl
index 5d2c50e..b139425 100755
--- a/installer/data/mysql/updatedatabase.pl
+++ b/installer/data/mysql/updatedatabase.pl
@@ -8790,6 +8790,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 XXXXX - Add new foreign key suggestions.budgetid\n");
+    SetVersion($DBversion);
+}
+
 =head1 FUNCTIONS
 
 =head2 TableExists($table)
-- 
2.1.0