Attachment 83509 Details for Bug 13515 – Bug 13515: Add a foreign key constraint to messages.borrowernumber

[patch] Bug 13515: Add a foreign key constraint to messages.borrowernumber

Bug-13515-Add-a-foreign-key-constraint-to-messages.patch (text/plain), 3.32 KB, created by Alex Arnaud on 2018-12-26 16:46:33 UTC

(hide)

Description:

Filename:

MIME Type:

Creator: Alex Arnaud

Created: 2018-12-26 16:46:33 UTC

Size: 3.32 KB

patch

obsolete

>From a818bb89dd3b4f7437b961ec4049e7649548c848 Mon Sep 17 00:00:00 2001
>From: Jonathan Druart <jonathan.druart@bugs.koha-community.org>
>Date: Tue, 18 Dec 2018 12:43:00 -0300
>Subject: [PATCH] Bug 13515: Add a foreign key constraint to
> messages.borrowernumber
>MIME-Version: 1.0
>Content-Type: text/plain; charset=UTF-8
>Content-Transfer-Encoding: 8bit
>
>This patch adds a missing foreign key constraint to
>mesages.borrowernumber.
>To create it sucessfully the entries from the messages table that are
>not linked with an existing patron will be removed.
>
>Test plan:
>0/ Do not apply the patch
>1/ Add messages to different patrons
>2/ Delete one of the patron's record
>3/ Have a look at the messages table and notice that the messages for
>the deleted patron's record still appear
>4/ Apply the patch and execute the update DB entry
>5/ Have a look again at the messages table and notice that the messages
>for the deleted patron's record have been removed
>6/ Delete a patron that have messages
>7/ Notice that now the messages are deleted when the patron's record is
>deleted
>
>Signed-off-by: NazlÄ± Ãetin <nazli@devinim.com.tr>
>Signed-off-by: Alex Arnaud <alex.arnaud@biblibre.com>
>---
> installer/data/mysql/atomicupdate/bug_13515.perl | 20 ++++++++++++++++++++
> installer/data/mysql/kohastructure.sql           |  3 ++-
> 2 files changed, 22 insertions(+), 1 deletion(-)
> create mode 100644 installer/data/mysql/atomicupdate/bug_13515.perl
>
>diff --git a/installer/data/mysql/atomicupdate/bug_13515.perl b/installer/data/mysql/atomicupdate/bug_13515.perl
>new file mode 100644
>index 0000000..837ae45
>--- /dev/null
>+++ b/installer/data/mysql/atomicupdate/bug_13515.perl
>@@ -0,0 +1,20 @@
>+$DBversion = 'XXX';  # will be replaced by the RM
>+if( CheckVersion( $DBversion ) ) {
>+
>+    unless( foreign_key_exists( 'messages', 'borrowernumber' ) ) {
>+        $dbh->do(q|
>+            DELETE m FROM messages m
>+            LEFT JOIN borrowers b ON m.borrowernumber=b.borrowernumber
>+            WHERE b.borrowernumber IS NULL
>+        |);
>+
>+        $dbh->do(q|
>+            ALTER TABLE messages
>+            ADD CONSTRAINT messages_borrowernumber
>+            FOREIGN KEY (borrowernumber) REFERENCES borrowers (borrowernumber) ON DELETE CASCADE ON UPDATE CASCADE
>+        |);
>+    }
>+
>+    SetVersion( $DBversion );
>+    print "Upgrade to $DBversion done (Bug 13515 - Add a FOREIGN KEY constaint on messages.borrowernumber)\n";
>+}
>diff --git a/installer/data/mysql/kohastructure.sql b/installer/data/mysql/kohastructure.sql
>index 8a8ca49..0a212a2 100644
>--- a/installer/data/mysql/kohastructure.sql
>+++ b/installer/data/mysql/kohastructure.sql
>@@ -2694,7 +2694,8 @@ CREATE TABLE `messages` ( -- circulation messages left via the patron's check ou
>   `message_date` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, -- the date and time the message was written
>   `manager_id` int(11) default NULL, -- creator of message
>   PRIMARY KEY (`message_id`),
>-  CONSTRAINT `messages_ibfk_1` FOREIGN KEY (`manager_id`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE SET NULL
>+  CONSTRAINT `messages_ibfk_1` FOREIGN KEY (`manager_id`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE SET NULL,
>+  CONSTRAINT `messages_borrowernumber` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE
> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
> 
> --
>-- 
>2.7.4

Actions: View | Diff | Splinter Review

Attachments on bug 13515: 83367 | 83463 | 83508 | 83509 | 83510 | 85138 | 85155 | 85156 | 85157