From 673b192d01fa1af56bc3c7a140684c573ebb4fd0 Mon Sep 17 00:00:00 2001
From: Jonathan Druart <jonathan.druart@bugs.koha-community.org>
Date: Thu, 26 Dec 2019 11:42:43 +0100
Subject: [PATCH] Bug 24289: Add foreign keys on *_holidays.branchcode tables

Those are missing foreign keys, it will also clean the holidays tables
and automatically remove the holidays when a library is removed.

Test plan:
0. Do not apply this patch
1. Create a new library
2. Create special and repeatable holiday for this library
3. Remove the library
=> Notice that the holidays still exist in DB
4. Apply this patch
5. Execute the update database entry
=> Notice that the holidays for this library have been removed from the DB
6. Repeat 1-3
=> Notice that the holidays have been removed along with the library

Signed-off-by: Katrin Fischer <katrin.fischer.83@web.de>
---
 installer/data/mysql/atomicupdate/bug_24289.perl | 31 ++++++++++++++++++++++++
 installer/data/mysql/kohastructure.sql           | 10 +++++---
 2 files changed, 37 insertions(+), 4 deletions(-)
 create mode 100644 installer/data/mysql/atomicupdate/bug_24289.perl

diff --git a/installer/data/mysql/atomicupdate/bug_24289.perl b/installer/data/mysql/atomicupdate/bug_24289.perl
new file mode 100644
index 0000000000..231e1ab88b
--- /dev/null
+++ b/installer/data/mysql/atomicupdate/bug_24289.perl
@@ -0,0 +1,31 @@
+$DBversion = 'XXX'; # will be replaced by the RM
+if( CheckVersion( $DBversion ) ) {
+    if( !foreign_key_exists( 'repeatable_holidays', 'repeatable_holidays_ibfk_1' ) ) {
+        $dbh->do(q|
+            DELETE h
+            FROM repeatable_holidays h
+            LEFT JOIN branches b ON h.branchcode=b.branchcode
+            WHERE b.branchcode IS NULL;
+        |);
+        $dbh->do(q|
+            ALTER TABLE repeatable_holidays
+            ADD FOREIGN KEY repeatable_holidays_ibfk_1 (branchcode) REFERENCES branches (branchcode) ON DELETE CASCADE ON UPDATE CASCADE
+        |);
+    }
+
+    if( !foreign_key_exists( 'special_holidays', 'special_holidays_ibfk_1' ) ) {
+        $dbh->do(q|
+            DELETE h
+            FROM special_holidays h
+            LEFT JOIN branches b ON h.branchcode=b.branchcode
+            WHERE b.branchcode IS NULL;
+        |);
+        $dbh->do(q|
+            ALTER TABLE special_holidays
+            ADD FOREIGN KEY special_holidays_ibfk_1 (branchcode) REFERENCES branches (branchcode) ON DELETE CASCADE ON UPDATE CASCADE
+        |);
+    }
+
+    SetVersion( $DBversion );
+    print "Upgrade to $DBversion done (Bug 24289 - Adding foreign keys on *_holidays.branchcode tables)\n";
+}
diff --git a/installer/data/mysql/kohastructure.sql b/installer/data/mysql/kohastructure.sql
index 328b1acf96..3f28188c02 100644
--- a/installer/data/mysql/kohastructure.sql
+++ b/installer/data/mysql/kohastructure.sql
@@ -1320,13 +1320,14 @@ CREATE TABLE `printers_profile` (
 DROP TABLE IF EXISTS `repeatable_holidays`;
 CREATE TABLE `repeatable_holidays` ( -- information for the days the library is closed
   `id` int(11) NOT NULL auto_increment, -- unique identifier assigned by Koha
-  `branchcode` varchar(10) NOT NULL default '', -- foreign key from the branches table, defines which branch this closing is for
+  `branchcode` varchar(10) NOT NULL, -- foreign key from the branches table, defines which branch this closing is for
   `weekday` smallint(6) default NULL, -- day of the week (0=Sunday, 1=Monday, etc) this closing is repeated on
   `day` smallint(6) default NULL, -- day of the month this closing is on
   `month` smallint(6) default NULL, -- month this closing is in
   `title` varchar(50) NOT NULL default '', -- title of this closing
   `description` MEDIUMTEXT NOT NULL, -- description for this closing
-  PRIMARY KEY  (`id`)
+  PRIMARY KEY  (`id`),
+  CONSTRAINT `repeatable_holidays_ibfk_1` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
 
 --
@@ -1902,14 +1903,15 @@ CREATE TABLE `reviews` ( -- patron opac comments
 DROP TABLE IF EXISTS `special_holidays`;
 CREATE TABLE `special_holidays` ( -- non repeatable holidays/library closings
   `id` int(11) NOT NULL auto_increment, -- unique identifier assigned by Koha
-  `branchcode` varchar(10) NOT NULL default '', -- foreign key from the branches table, defines which branch this closing is for
+  `branchcode` varchar(10) NOT NULL, -- foreign key from the branches table, defines which branch this closing is for
   `day` smallint(6) NOT NULL default 0, -- day of the month this closing is on
   `month` smallint(6) NOT NULL default 0, -- month this closing is in
   `year` smallint(6) NOT NULL default 0, -- year this closing is in
   `isexception` smallint(1) NOT NULL default 1, -- is this a holiday exception to a repeatable holiday (1 for yes, 0 for no)
   `title` varchar(50) NOT NULL default '', -- title for this closing
   `description` MEDIUMTEXT NOT NULL, -- description of this closing
-  PRIMARY KEY  (`id`)
+  PRIMARY KEY  (`id`),
+  CONSTRAINT `special_holidays_ibfk_1` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
 
 --
-- 
2.11.0