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