From 5bb9f54fab8006360850619fbf74cc06eede8e55 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 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 Signed-off-by: Marcel de Rooy --- .../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