From 0b72851520a1cbcf7294aec83b662ad51f476a1b Mon Sep 17 00:00:00 2001 From: Kyle M Hall Date: Thu, 6 Jun 2019 08:00:47 -0400 Subject: [PATCH] Bug 23066: Add foreign key for issues tables to branches table for branchcodes - Update DB We should really have foreign key constrains between the branches table and the issues and old_issues tables. --- .../data/mysql/atomicupdate/bug_23066.perl | 17 +++++++++++++++++ installer/data/mysql/kohastructure.sql | 10 +++++----- 2 files changed, 22 insertions(+), 5 deletions(-) create mode 100644 installer/data/mysql/atomicupdate/bug_23066.perl diff --git a/installer/data/mysql/atomicupdate/bug_23066.perl b/installer/data/mysql/atomicupdate/bug_23066.perl new file mode 100644 index 0000000000..a63ff75e00 --- /dev/null +++ b/installer/data/mysql/atomicupdate/bug_23066.perl @@ -0,0 +1,17 @@ +$DBversion = 'XXX'; # will be replaced by the RM +if( CheckVersion( $DBversion ) ) { + + # Add constraint for branchcode + unless ( foreign_key_exists( 'issues', 'issues_ibfk_3' ) ) { + $dbh->do(q{UPDATE issues i LEFT JOIN branches b ON (i.branchcode = b.branchcode) SET i.branchcode = NULL WHERE b.branchcode IS NULL}); + $dbh->do(q{ALTER TABLE issues ADD CONSTRAINT `issues_ibfk_3` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`) ON DELETE RESTRICT ON UPDATE CASCADE}); + } + unless ( foreign_key_exists( 'old_issues', 'old_issues_ibfk_3' ) ) { + $dbh->do(q{UPDATE old_issues i LEFT JOIN branches b ON (i.branchcode = b.branchcode) SET i.branchcode = NULL WHERE b.branchcode IS NULL}); + $dbh->do(q{ALTER TABLE old_issues ADD CONSTRAINT `old_issues_ibfk_3` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`) ON DELETE RESTRICT ON UPDATE CASCADE}); + } + + # Always end with this (adjust the bug info) + SetVersion( $DBversion ); + print "Upgrade to $DBversion done (Bug 23066 - Add foreign key for issues tables to branches table for branchcodes)\n"; +} diff --git a/installer/data/mysql/kohastructure.sql b/installer/data/mysql/kohastructure.sql index da3ff83f37..46060e8513 100644 --- a/installer/data/mysql/kohastructure.sql +++ b/installer/data/mysql/kohastructure.sql @@ -1746,7 +1746,8 @@ CREATE TABLE `issues` ( -- information related to check outs or issues KEY `branchcode_idx` (`branchcode`), KEY `bordate` (`borrowernumber`,`timestamp`), CONSTRAINT `issues_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE RESTRICT ON UPDATE CASCADE, - CONSTRAINT `issues_ibfk_2` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE RESTRICT ON UPDATE CASCADE + CONSTRAINT `issues_ibfk_2` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE RESTRICT ON UPDATE CASCADE, + CONSTRAINT `issues_ibfk_3` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`) ON DELETE RESTRICT ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- @@ -1776,10 +1777,9 @@ CREATE TABLE `old_issues` ( -- lists items that were checked out and have been r KEY `old_issuesitemidx` (`itemnumber`), KEY `branchcode_idx` (`branchcode`), KEY `old_bordate` (`borrowernumber`,`timestamp`), - CONSTRAINT `old_issues_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) - ON DELETE SET NULL ON UPDATE SET NULL, - CONSTRAINT `old_issues_ibfk_2` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) - ON DELETE SET NULL ON UPDATE SET NULL + CONSTRAINT `old_issues_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE SET NULL ON UPDATE SET NULL, + CONSTRAINT `old_issues_ibfk_2` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE SET NULL ON UPDATE SET NULL, + CONSTRAINT `old_issues_ibfk_3` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`) ON DELETE RESTRICT ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- -- 2.20.1 (Apple Git-117)