Attachment 90372 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), 3.95 KB, created by Kyle M Hall (khall) on 2019-06-06 13:42:28 UTC

(hide)

Description:

Filename:

MIME Type:

Creator: Kyle M Hall (khall)

Created: 2019-06-06 13:42:28 UTC

Size: 3.95 KB

patch

obsolete

>From fcaa93a73279a8e1e6fff0a3b12b1bc4014a787e 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.
>---
> .../data/mysql/atomicupdate/bug_15985.perl      | 17 +++++++++++++++++
> installer/data/mysql/kohastructure.sql          | 10 +++++-----
> 2 files changed, 22 insertions(+), 5 deletions(-)
> create mode 100644 installer/data/mysql/atomicupdate/bug_15985.perl
>
>diff --git a/installer/data/mysql/atomicupdate/bug_15985.perl b/installer/data/mysql/atomicupdate/bug_15985.perl
>new file mode 100644
>index 0000000000..6ea4160dce
>--- /dev/null
>+++ b/installer/data/mysql/atomicupdate/bug_15985.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 15985 - Include transacting library in fines)\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)

Actions: View | Diff | Splinter Review

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