From acc550ec1a8f1f420fdc5ed55db2ce28e37264d1 Mon Sep 17 00:00:00 2001 From: Jonathan Druart 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 Content-Type: text/plain; charset=utf-8 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 Signed-off-by: Alex Arnaud Signed-off-by: Marcel de Rooy --- 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 0000000000..837ae458df --- /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 89e71c94a5..aa2372c0f2 100644 --- a/installer/data/mysql/kohastructure.sql +++ b/installer/data/mysql/kohastructure.sql @@ -2695,7 +2695,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.11.0