Attachment 111076 Details for Bug 13535 – Bug 13535: Add FK constraint on borrowernumber to alert table

[patch] Bug 13535: Add FK constraint on borrowernumber to alert table

Bug-13535-Add-FK-constraint-on-borrowernumber-to-a.patch (text/plain), 3.30 KB, created by Marcel de Rooy on 2020-10-02 09:33:00 UTC

(hide)

Description:

Filename:

MIME Type:

Creator: Marcel de Rooy

Created: 2020-10-02 09:33:00 UTC

Size: 3.30 KB

patch

obsolete

>From 5bb9f54fab8006360850619fbf74cc06eede8e55 Mon Sep 17 00:00:00 2001
>From: Katrin Fischer <katrin.fischer.83@web.de>
>Date: Sun, 12 Apr 2020 20:52:11 +0000
>Subject: [PATCH] Bug 13535: Add FK constraint on borrowernumber to alert table
>Content-Type: text/plain; charset=utf-8
>
>When a patron is deleted, we should clean up the data connected
>to the patron. This includes subscriptions to 'new issue arrived'
>arrive messages from the serials module.
>
>To test:
>
>Part1:
>- Before applying the patch
>- Add a subscription and select a notice template for patron notification
>- Go to the OPAC and subscribe to the email alert
>- Log in as any other patron (not yourself) and subscribe with that patron too
>- Verfiy on the subscription detail page, that the patrons are subscribed
>- Note the borrowernumber of your patrons
>- Delete the second patron
>- Note borrowernumber and delete patron
>- Run: SELECT * from alert; in a report
>- Despite the patron being deleted, you will see entries for both
>- Apply patch
>- Run updatedatabase - verify no error message
>- Run updatedatabase again - verify still no error message
>- There should no longer be an entry with the deleted borrowernumber,
>  but only the entry for your own patron account
>Part 2:
>- Start fresh with the web installer, verify there are no errors on
>  creating the database tables
>
>Signed-off-by: Martin Renvoize <martin.renvoize@ptfs-europe.com>
>
>Signed-off-by: Marcel de Rooy <m.de.rooy@rijksmuseum.nl>
>---
> .../mysql/atomicupdate/bug_13535_alert_constraint.perl   | 16 ++++++++++++++++
> installer/data/mysql/kohastructure.sql                   |  3 ++-
> 2 files changed, 18 insertions(+), 1 deletion(-)
> create mode 100644 installer/data/mysql/atomicupdate/bug_13535_alert_constraint.perl
>
>diff --git a/installer/data/mysql/atomicupdate/bug_13535_alert_constraint.perl b/installer/data/mysql/atomicupdate/bug_13535_alert_constraint.perl
>new file mode 100644
>index 0000000000..2f837e2594
>--- /dev/null
>+++ b/installer/data/mysql/atomicupdate/bug_13535_alert_constraint.perl
>@@ -0,0 +1,16 @@
>+$DBversion = 'XXX'; # will be replaced by the RM
>+if( CheckVersion( $DBversion ) ) {
>+    unless ( foreign_key_exists( 'alert', 'alert_ibfk_1' ) ) {
>+        $dbh->do(q|
>+            DELETE a FROM alert a
>+            LEFT JOIN borrowers b ON a.borrowernumber=b.borrowernumber
>+            WHERE b.borrowernumber IS NULL
>+        |);
>+        $dbh->do(
>+            qq{
>+            ALTER TABLE alert ADD CONSTRAINT alert_ibfk_1 FOREIGN KEY (borrowernumber) REFERENCES borrowers (borrowernumber) ON UPDATE CASCADE ON DELETE CASCADE
>+              }
>+        );
>+    }
>+    NewVersion( $DBversion, 13535, "Add FK constraint on borrowernumber to alert table");
>+}
>diff --git a/installer/data/mysql/kohastructure.sql b/installer/data/mysql/kohastructure.sql
>index 7bc8a4dd96..0fb85937da 100644
>--- a/installer/data/mysql/kohastructure.sql
>+++ b/installer/data/mysql/kohastructure.sql
>@@ -2841,7 +2841,8 @@ CREATE TABLE `alert` (
>   `externalid` varchar(20) NOT NULL default '',
>   PRIMARY KEY  (`alertid`),
>   KEY `borrowernumber` (`borrowernumber`),
>-  KEY `type` (`type`,`externalid`)
>+  KEY `type` (`type`,`externalid`),
>+  CONSTRAINT alert_ibfk_1 FOREIGN KEY (borrowernumber) REFERENCES borrowers (borrowernumber) ON UPDATE CASCADE ON DELETE CASCADE
> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
> 
> --
>-- 
>2.11.0

Actions: View | Diff | Splinter Review

Attachments on bug 13535: 103039 | 108272 | 108275 | 110194 | 111076