Attachment 141001 Details for Bug 31626 – Bug 31626: Add letter id to the message queue table

[patch] Bug 31626: Add letter id to the message queue table

Bug-31626-Add-letter-id-to-the-message-queue-table.patch (text/plain), 5.01 KB, created by Kyle M Hall (khall) on 2022-09-26 16:33:24 UTC

(hide)

Description:

Filename:

MIME Type:

Creator: Kyle M Hall (khall)

Created: 2022-09-26 16:33:24 UTC

Size: 5.01 KB

patch

obsolete

>From 1f7b67b24c4970f056addae01aad7bb5c34c4002 Mon Sep 17 00:00:00 2001
>From: Kyle Hall <kyle@bywatersolutions.com>
>Date: Mon, 26 Sep 2022 12:05:10 -0400
>Subject: [PATCH] Bug 31626: Add letter id to the message queue table
>
>It can be useful to know exactly what template was used to generate a notice. To this end, it would be useful to store the letter id as a foreign key in the message queue table.
>
>Test Plan:
>1) Apply this patch
>2) Run updatedatabase.pl
>3) Restart all the things!
>4) Run an action that will send a notice to a patron
>5) Note the letter id is now in the message_queue table for that notice!
>---
> C4/Letters.pm                                  |  6 +++---
> installer/data/mysql/atomicupdate/bug_31626.pl | 15 +++++++++++++++
> installer/data/mysql/kohastructure.sql         |  2 ++
> t/db_dependent/Letters.t                       |  3 ++-
> 4 files changed, 22 insertions(+), 4 deletions(-)
> create mode 100755 installer/data/mysql/atomicupdate/bug_31626.pl
>
>diff --git a/C4/Letters.pm b/C4/Letters.pm
>index bde0d2b0dc..42edb9c1e0 100644
>--- a/C4/Letters.pm
>+++ b/C4/Letters.pm
>@@ -921,13 +921,13 @@ sub EnqueueLetter {
>     my $dbh       = C4::Context->dbh();
>     my $statement = << 'ENDSQL';
> INSERT INTO message_queue
>-( borrowernumber, subject, content, metadata, letter_code, message_transport_type, status, time_queued, to_address, from_address, reply_address, content_type, failure_code )
>-VALUES
>-( ?,              ?,       ?,       ?,        ?,           ?,                      ?,      CAST(NOW() AS DATETIME),       ?,          ?,            ?,           ?,              ? )
>+( letter_id, borrowernumber, subject, content, metadata, letter_code, message_transport_type, status, time_queued, to_address, from_address, reply_address, content_type, failure_code )
>+VALUES ( ?, ?, ?, ?, ?, ?, ?, ?, CAST(NOW() AS DATETIME), ?, ?, ?, ?, ? )
> ENDSQL
> 
>     my $sth    = $dbh->prepare($statement);
>     my $result = $sth->execute(
>+        $params->{letter}->{id} || undef,         # letter.id
>         $params->{'borrowernumber'},              # borrowernumber
>         $params->{'letter'}->{'title'},           # subject
>         $params->{'letter'}->{'content'},         # content
>diff --git a/installer/data/mysql/atomicupdate/bug_31626.pl b/installer/data/mysql/atomicupdate/bug_31626.pl
>new file mode 100755
>index 0000000000..d02b3a88b1
>--- /dev/null
>+++ b/installer/data/mysql/atomicupdate/bug_31626.pl
>@@ -0,0 +1,15 @@
>+use Modern::Perl;
>+
>+return {
>+    bug_number => "31626",
>+    description => "Add letter id to the message queue table",
>+    up => sub {
>+        my ($args) = @_;
>+        my ($dbh, $out) = @$args{qw(dbh out)};
>+        $dbh->do(q{
>+            ALTER TABLE message_queue
>+            ADD COLUMN letter_id INT(11) NULL DEFAULT NULL AFTER message_id,
>+            ADD CONSTRAINT letter_fk FOREIGN KEY (letter_id) REFERENCES letter(id) ON DELETE SET NULL ON UPDATE CASCADE
>+        });
>+    },
>+};
>diff --git a/installer/data/mysql/kohastructure.sql b/installer/data/mysql/kohastructure.sql
>index ce0c4eacc5..f32b668c48 100644
>--- a/installer/data/mysql/kohastructure.sql
>+++ b/installer/data/mysql/kohastructure.sql
>@@ -3843,6 +3843,7 @@ DROP TABLE IF EXISTS `message_queue`;
> /*!40101 SET character_set_client = utf8 */;
> CREATE TABLE `message_queue` (
>   `message_id` int(11) NOT NULL AUTO_INCREMENT,
>+  `letter_id` int(11) DEFAULT NULL,
>   `borrowernumber` int(11) DEFAULT NULL,
>   `subject` mediumtext COLLATE utf8mb4_unicode_ci DEFAULT NULL,
>   `content` mediumtext COLLATE utf8mb4_unicode_ci DEFAULT NULL,
>@@ -3860,6 +3861,7 @@ CREATE TABLE `message_queue` (
>   PRIMARY KEY (`message_id`),
>   KEY `borrowernumber` (`borrowernumber`),
>   KEY `message_transport_type` (`message_transport_type`),
>+  CONSTRAINT `letter_fk` FOREIGN KEY (`letter_id`) REFERENCES `letter` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
>   CONSTRAINT `messageq_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
>   CONSTRAINT `messageq_ibfk_2` FOREIGN KEY (`message_transport_type`) REFERENCES `message_transport_types` (`message_transport_type`) ON UPDATE CASCADE
> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
>diff --git a/t/db_dependent/Letters.t b/t/db_dependent/Letters.t
>index 426df4aea2..8c5ccd424f 100755
>--- a/t/db_dependent/Letters.t
>+++ b/t/db_dependent/Letters.t
>@@ -781,7 +781,7 @@ subtest 'TranslateNotices' => sub {
> 
> subtest 'Test SMS handling in SendQueuedMessages' => sub {
> 
>-    plan tests => 13;
>+    plan tests => 14;
> 
>     t::lib::Mocks::mock_preference( 'SMSSendDriver', 'Email' );
>     t::lib::Mocks::mock_preference('EmailSMSSendDriverFromAddress', '');
>@@ -808,6 +808,7 @@ subtest 'Test SMS handling in SendQueuedMessages' => sub {
>         status => 'sent'
>     })->next();
> 
>+    is( $message->letter_id, $messages->[0]->{id}, "Message letter_id is set correctly" );
>     is( $message->to_address(), '5555555555@kidclamp.rocks', 'SendQueuedMessages populates the to address correctly for SMS by email when to_address not set' );
>     is(
>         $message->from_address(),
>-- 
>2.30.2

Actions: View | Diff | Splinter Review

Attachments on bug 31626: 140996 | 140999 | 141001 | 141005 | 141006 | 141942 | 141943 | 142054