Attachment 83237 Details for Bug 21065 – Bug 21065: Set ON DELETE SET NULL on accountlines.borrowernumber

[patch] Bug 21065: Set ON DELETE SET NULL on accountlines.borrowernumber

Bug-21065-Set-ON-DELETE-SET-NULL-on-accountlinesbo.patch (text/plain), 2.77 KB, created by Martin Renvoize (ashimema) on 2018-12-14 17:10:56 UTC

(hide)

Description:

Filename:

MIME Type:

Creator: Martin Renvoize (ashimema)

Created: 2018-12-14 17:10:56 UTC

Size: 2.77 KB

patch

obsolete

>From 1dfebe5484018a0bb896dae45c8f0262d0f13ef8 Mon Sep 17 00:00:00 2001
>From: Jonathan Druart <jonathan.druart@bugs.koha-community.org>
>Date: Fri, 14 Dec 2018 12:49:44 -0300
>Subject: [PATCH] Bug 21065: Set ON DELETE SET NULL on
> accountlines.borrowernumber
>
>Note: Why do we have ON UPDATE SET NULL on accountlines.itemnumber?
>Signed-off-by: Martin Renvoize <martin.renvoize@ptfs-europe.com>
>---
> installer/data/mysql/atomicupdate/bug_21065.perl | 16 ++++++++++++++++
> installer/data/mysql/kohastructure.sql           |  4 ++--
> 2 files changed, 18 insertions(+), 2 deletions(-)
> create mode 100644 installer/data/mysql/atomicupdate/bug_21065.perl
>
>diff --git a/installer/data/mysql/atomicupdate/bug_21065.perl b/installer/data/mysql/atomicupdate/bug_21065.perl
>new file mode 100644
>index 0000000000..8c732da964
>--- /dev/null
>+++ b/installer/data/mysql/atomicupdate/bug_21065.perl
>@@ -0,0 +1,16 @@
>+$DBversion = 'XXX';  # will be replaced by the RM
>+if( CheckVersion( $DBversion ) ) {
>+
>+    $dbh->do(q|
>+        ALTER TABLE accountlines DROP FOREIGN KEY accountlines_ibfk_1;
>+    |);
>+    $dbh->do(q|
>+        ALTER TABLE accountlines CHANGE COLUMN borrowernumber borrowernumber INT(11) DEFAULT NULL;
>+    |);
>+    $dbh->do(q|
>+        ALTER TABLE accountlines ADD CONSTRAINT accountlines_ibfk_1 FOREIGN KEY (borrowernumber) REFERENCES borrowers (borrowernumber) ON DELETE SET NULL ON UPDATE CASCADE;
>+    |);
>+
>+    SetVersion( $DBversion );
>+    print "Upgrade to $DBversion done (Bug 21065 - Set ON DELETE SET NULL on accountlines.borrowernumber)\n";
>+}
>diff --git a/installer/data/mysql/kohastructure.sql b/installer/data/mysql/kohastructure.sql
>index c3c9570a29..9b45f15d15 100644
>--- a/installer/data/mysql/kohastructure.sql
>+++ b/installer/data/mysql/kohastructure.sql
>@@ -2705,7 +2705,7 @@ DROP TABLE IF EXISTS `accountlines`;
> CREATE TABLE `accountlines` (
>   `accountlines_id` int(11) NOT NULL AUTO_INCREMENT,
>   `issue_id` int(11) NULL DEFAULT NULL,
>-  `borrowernumber` int(11) NOT NULL default 0,
>+  `borrowernumber` int(11) DEFAULT NULL,
>   `accountno` smallint(6) NOT NULL default 0,
>   `itemnumber` int(11) default NULL,
>   `date` date default NULL,
>@@ -2722,7 +2722,7 @@ CREATE TABLE `accountlines` (
>   KEY `acctsborridx` (`borrowernumber`),
>   KEY `timeidx` (`timestamp`),
>   KEY `itemnumber` (`itemnumber`),
>-  CONSTRAINT `accountlines_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
>+  CONSTRAINT `accountlines_ibfk_1` 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
> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
> 
>-- 
>2.19.2

Actions: View | Diff | Splinter Review

Attachments on bug 21065: 83220 | 83221 | 83222 | 83223 | 83224 | 83235 | 83236 | 83237 | 83238 | 83366