Bugzilla – Attachment 97410 Details for
Bug 24289
Deleting branch will not delete entry in special or repeatable holidays
Home
|
New
|
Browse
|
Search
|
[?]
|
Reports
|
Help
|
New Account
|
Log In
[x]
|
Forgot Password
Login:
[x]
[patch]
Bug 24289: Add foreign keys on *_holidays.branchcode tables
Bug-24289-Add-foreign-keys-on-holidaysbranchcode-t.patch (text/plain), 5.10 KB, created by
Owen Leonard
on 2020-01-15 19:21:51 UTC
(
hide
)
Description:
Bug 24289: Add foreign keys on *_holidays.branchcode tables
Filename:
MIME Type:
Creator:
Owen Leonard
Created:
2020-01-15 19:21:51 UTC
Size:
5.10 KB
patch
obsolete
>From 67a6f56e39f57bddd2fc7aff6d81e9878e5cf8e8 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 >--- > 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
You cannot view the attachment while viewing its details because your browser does not support IFRAMEs.
View the attachment on a separate page
.
View Attachment As Diff
View Attachment As Raw
Actions:
View
|
Diff
|
Splinter Review
Attachments on
bug 24289
:
96630
|
96631
|
96632
|
97410
|
97411
|
97412
|
97600
|
97601
|
97602