From f794b2b3141059f4c661bc2c95eef6075355b4da Mon Sep 17 00:00:00 2001 From: Kyle Hall 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! Signed-off-by: Andrew Fuerste-Henry Signed-off-by: Katrin Fischer --- 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 95dfdd0093..2c59de7fa9 100644 --- a/C4/Letters.pm +++ b/C4/Letters.pm @@ -922,13 +922,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 7025f9bbf9..bf1302f437 100644 --- a/installer/data/mysql/kohastructure.sql +++ b/installer/data/mysql/kohastructure.sql @@ -3846,6 +3846,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, @@ -3863,6 +3864,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 7afcc60814..814d5e60e9 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