Attachment 83280 Details for Bug 22008 – Bug 22008: Add missing constraints to accountlines

[patch] Bug 22008: Add missing constraints to accountlines

Bug-22008-Add-missing-constraints-to-accountlines.patch (text/plain), 3.62 KB, created by Martin Renvoize (ashimema) on 2018-12-17 13:41:18 UTC

(hide)

Description:

Filename:

MIME Type:

Creator: Martin Renvoize (ashimema)

Created: 2018-12-17 13:41:18 UTC

Size: 3.62 KB

patch

obsolete

>From 81e1c7c365e6d3d7c6b6b84064ec72dd4615fc2e Mon Sep 17 00:00:00 2001
>From: Martin Renvoize <martin.renvoize@ptfs-europe.com>
>Date: Mon, 17 Dec 2018 13:29:08 +0000
>Subject: [PATCH] Bug 22008: Add missing constraints to accountlines
>
>Add constraints for manager_id referencing the borrowers table and issue_id
>referencing the issues table and update the itemnumber constraint from
>`ON UPDATE SET NULL` to `ON UPDATE CASCADE`
>---
> .../data/mysql/atomicupdate/bug_22008.perl      | 17 +++++++++++++++++
> installer/data/mysql/kohastructure.sql          |  8 ++++++--
> 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..e5fd7e9aa4
>--- /dev/null
>+++ b/installer/data/mysql/atomicupdate/bug_22008.perl
>@@ -0,0 +1,17 @@
>+$DBversion = 'XXX';  # will be replaced by the RM
>+if( CheckVersion( $DBversion ) ) {
>+
>+    # Clean up
>+    $dbh->do("ALTER TABLE accountlines CHANGE COLUMN manager_id manager_id INT(11) NULL DEFAULT NULL;");
>+    $dbh->do("UPDATE accountlines SET issue_id = NULL where issue_id NOT IN (SELECT issue_id FROM issues)");
>+    $dbh->do("UPDATE accountlines SET manager_id = NULL where manager_id NOT IN (SELECT borrowernumber FROM borrowers)");
>+    $dbh->do("ALTER TABLE accountlines DROP FOREIGN KEY accountlines_ibfk_2");
>+    
>+    # Add Constraints
>+    $dbh->do("ALTER TABLE accountlines ADD CONSTRAINT `accountlines_ibfk_items` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE SET NULL ON UPDATE CASCADE");
>+    $dbh->do("ALTER TABLE accountlines ADD CONSTRAINT `accountlines_ibfk_issues` FOREIGN KEY (`issue_id`) REFERENCES `issues` (`issue_id`) ON DELETE SET NULL ON UPDATE CASCADE");
>+    $dbh->do("ALTER TABLE accountlines ADD CONSTRAINT `accountlines_ibfk_borrowers_m` FOREIGN KEY (`manager_id`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE SET NULL ON UPDATE CASCADE");
>+
>+    SetVersion( $DBversion );
>+    print "Upgrade to $DBversion done (Bug 22008 - Add missing constraints to accountlines)\n";
>+}
>diff --git a/installer/data/mysql/kohastructure.sql b/installer/data/mysql/kohastructure.sql
>index c3c9570a29..e475d17bc7 100644
>--- a/installer/data/mysql/kohastructure.sql
>+++ b/installer/data/mysql/kohastructure.sql
>@@ -2717,13 +2717,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,
>   PRIMARY KEY (`accountlines_id`),
>   KEY `acctsborridx` (`borrowernumber`),
>   KEY `timeidx` (`timestamp`),
>   KEY `itemnumber` (`itemnumber`),
>+  KEY `issue_id` (`issue_id`),
>+  KEY `manager_id` (`manager_id`),
>   CONSTRAINT `accountlines_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE 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_issues` FOREIGN KEY (`issue_id`) REFERENCES `issues` (`issue_id`) ON DELETE SET NULL ON UPDATE CASCADE,
>+  CONSTRAINT `accountlines_ibfk_borrowers_m` FOREIGN KEY (`manager_id`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE SET NULL ON UPDATE CASCADE
> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
> 
> --
>-- 
>2.19.2

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