Attachment 92167 Details for Bug 23066 – Bug 23066: Add foreign key for issues tables to branches table for branchcodes - Update DB

[patch] Bug 23066: Add foreign key for issues tables to branches table for branchcodes - Update DB

Bug-23066-Add-foreign-key-for-issues-tables-to-bra.patch (text/plain), 4.02 KB, created by Katrin Fischer on 2019-08-13 05:43:43 UTC

(hide)

Description:

Filename:

MIME Type:

Creator: Katrin Fischer

Created: 2019-08-13 05:43:43 UTC

Size: 4.02 KB

patch

obsolete

>From b4dfb9cec1652dda8d75913d1dc59d039122fe90 Mon Sep 17 00:00:00 2001
>From: Kyle M Hall <kyle@bywatersolutions.com>
>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.
>
>Signed-off-by: Katrin Fischer <katrin.fischer.83@web.de>
>---
> installer/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 5016b68c51..0a55ca963d 100644
>--- a/installer/data/mysql/kohastructure.sql
>+++ b/installer/data/mysql/kohastructure.sql
>@@ -1681,7 +1681,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;
> 
> --
>@@ -1711,10 +1712,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.11.0

Actions: View | Diff | Splinter Review

Attachments on bug 23066: 90372 | 90373 | 90374 | 92167