Attachment 86163 Details for Bug 22008 – Bug 22008: Add missing constraints

[patch] Bug 22008: Add missing constraints

Bug-22008-Add-missing-constraints.patch (text/plain), 3.71 KB, created by Kyle M Hall (khall) on 2019-03-06 13:09:05 UTC

(hide)

Description:

Filename:

MIME Type:

Creator: Kyle M Hall (khall)

Created: 2019-03-06 13:09:05 UTC

Size: 3.71 KB

patch

obsolete

>From 10d7b033c832871c2a6deadf86ffdde691efff2b Mon Sep 17 00:00:00 2001
>From: Martin Renvoize <martin.renvoize@ptfs-europe.com>
>Date: Wed, 27 Feb 2019 12:04:46 +0000
>Subject: [PATCH] Bug 22008: Add missing constraints
>
>Signed-off-by: Kyle M Hall <kyle@bywatersolutions.com>
>---
> .../data/mysql/atomicupdate/bug_22008.perl    | 19 +++++++++++++++++++
> installer/data/mysql/kohastructure.sql        |  6 ++++--
> 2 files changed, 23 insertions(+), 2 deletions(-)
> create mode 100644 installer/data/mysql/atomicupdate/bug_22008.perl
>
>diff --git a/installer/data/mysql/atomicupdate/bug_22008.perl b/installer/data/mysql/atomicupdate/bug_22008.perl
>new file mode 100644
>index 0000000000..7f6098e65e
>--- /dev/null
>+++ b/installer/data/mysql/atomicupdate/bug_22008.perl
>@@ -0,0 +1,19 @@
>+$DBversion = 'XXX';  # will be replaced by the RM
>+if( CheckVersion( $DBversion ) ) {
>+
>+    # Add constraint for manager_id
>+    unless( foreign_key_exists( 'accountlines', 'accountlines_ibfk_borrowers_2' ) ) {
>+        $dbh->do("ALTER TABLE accountlines CHANGE COLUMN manager_id manager_id INT(11) NULL DEFAULT NULL;");
>+        $dbh->do("UPDATE accountlines a LEFT JOIN borrowers b ON ( a.manager_id = b.borrowernumber) SET a.manager_id = NULL WHERE b.borrowernumber IS NULL;
>+        $dbh->do("ALTER TABLE accountlines ADD CONSTRAINT `accountlines_ibfk_borrowers_2` FOREIGN KEY (`manager_id`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE SET NULL ON UPDATE CASCADE");
>+    }
>+
>+    # Rename accountlines_ibfk_2 to accountlines_ibfk_items
>+    if ( foreign_key_exists( 'accountlines', 'accountlines_ibfk_2' ) && !foreign_key_exists( 'accountlines', 'accountlines_ibfk_items' ) ) {
>+        $dbh->do("ALTER TABLE accountlines DROP FOREIGN KEY accountlines_ibfk_2");
>+        $dbh->do("ALTER TABLE accountlines ADD CONSTRAINT `accountlines_ibfk_items` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE SET NULL ON UPDATE CASCADE");
>+    }
>+
>+    SetVersion( $DBversion );
>+    print "Upgrade to $DBversion done (Bug 22008 - Add missing constraints for accountlines.manager_id)\n";
>+}
>diff --git a/installer/data/mysql/kohastructure.sql b/installer/data/mysql/kohastructure.sql
>index 2ef6860178..691d1961e4 100644
>--- a/installer/data/mysql/kohastructure.sql
>+++ b/installer/data/mysql/kohastructure.sql
>@@ -2682,15 +2682,17 @@ CREATE TABLE `accountlines` (
>   `lastincrement` decimal(28,6) default NULL,
>   `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
>   `note` MEDIUMTEXT NULL default NULL,
>-  `manager_id` int(11) NULL,
>+  `manager_id` int(11) NULL DEFAULT NULL,
>   `branchcode` VARCHAR( 10 ) NULL DEFAULT NULL, -- the branchcode of the library where a payment was made, a manual invoice created, etc.
>   PRIMARY KEY (`accountlines_id`),
>   KEY `acctsborridx` (`borrowernumber`),
>   KEY `timeidx` (`timestamp`),
>   KEY `itemnumber` (`itemnumber`),
>   KEY `branchcode` (`branchcode`),
>+  KEY `manager_id` (`manager_id`),
>   CONSTRAINT `accountlines_ibfk_borrowers` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE SET NULL ON UPDATE CASCADE,
>-  CONSTRAINT `accountlines_ibfk_2` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE SET NULL ON UPDATE SET NULL,
>+  CONSTRAINT `accountlines_ibfk_items` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE SET NULL ON UPDATE CASCADE,
>+  CONSTRAINT `accountlines_ibfk_borrowers_2` FOREIGN KEY (`manager_id`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE SET NULL ON UPDATE CASCADE,
>   CONSTRAINT `accountlines_ibfk_branches` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`) ON DELETE SET NULL ON UPDATE CASCADE
> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
> 
>-- 
>2.17.2 (Apple Git-113)

Actions: View | Diff | Splinter Review

Attachments on bug 22008: 83278 | 83280 | 83460 | 83461 | 83462 | 85267 | 85268 | 85613 | 85614 | 85767 | 85768 | 86162 | 86163 | 86164 | 86659 | 86660 | 86661 | 86994 | 86995 | 87066 | 87072