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

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>

Signed-off-by: Marcel de Rooy <m.de.rooy@rijksmuseum.nl>

Signed-off-by: Nick Clemens <nick@bywatersolutions.com>

diff --git a/installer/data/mysql/atomicupdate/bug_13515.perl b/installer/data/mysql/atomicupdate/bug_13515.perl
new file mode 100644 (file)
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 89e71c9..aa2372c 100644 (file)
--- 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;
 
 --