From 67a6f56e39f57bddd2fc7aff6d81e9878e5cf8e8 Mon Sep 17 00:00:00 2001 From: Jonathan Druart 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 --- 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 00000000000..231e1ab88bc --- /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 da0d7403463..6a7dde138f6 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