From 0fc577b75e6b68ed2385b019846dc6610ca2dda9 Mon Sep 17 00:00:00 2001 From: Katrin Fischer Date: Sun, 12 Apr 2020 20:52:11 +0000 Subject: [PATCH] Bug 13535: Add FK constraint on borrowrnumber to alert table 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 --- .../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 35c643fa38..482b014ad1 100644 --- a/installer/data/mysql/kohastructure.sql +++ b/installer/data/mysql/kohastructure.sql @@ -2830,7 +2830,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