From 942d9fcfb81d21381fc892bdb7b1ed25f29e7699 Mon Sep 17 00:00:00 2001 From: Charles Farmer Date: Mon, 13 Aug 2018 10:54:09 -0400 Subject: [PATCH] Bug 21214: cleanup_database.pl --keep-lc to dodge deletion in message_queue based on letter_code Librarians sometime worry if a certain email was sent or not to a patron (PREDUES, ODUES, etc.), and iirc, there are no way to have that information once the cleanup_database.pl has run, unless we dig back an archive of their database. This patch would let an admin choose which letters to keep in the message_queue, based on its letter_code, by passing a comma-separated list to the script. TEST PLAN: 1) Either you have already have data, or you'll want to add some new test lines in your message_queue table . INSERT INTO message_queue(borrowernumber,letter_code,message_transport_type,status,time_queued) VALUES(1,,'email','sent','2018-01-01 00:00:42'); 2) Make a backup of this table 3) Run the script patchless with the --mail 1 option 3.1) Messages older than 1 day should have been deleted 4) Put the data back in 5) Apply patch 6) Choose a letter_code you'd like to keep, and run the script with --mail 1 --keep-lc [,] 7) Messages older than 1 day that don't have the code [or ] should've been deleted --- misc/cronjobs/cleanup_database.pl | 34 ++++++++++++++++++++----------- 1 file changed, 22 insertions(+), 12 deletions(-) diff --git a/misc/cronjobs/cleanup_database.pl b/misc/cronjobs/cleanup_database.pl index 04cc934b9c..680737809f 100755 --- a/misc/cronjobs/cleanup_database.pl +++ b/misc/cronjobs/cleanup_database.pl @@ -71,6 +71,9 @@ Usage: $0 [-h|--help] [--confirm] [--sessions] [--sessdays DAYS] [-v|--verbose] Defaults to 30 days if no days specified. -m --mail DAYS purge items from the mail queue that are older than DAYS days. Defaults to 30 days if no days specified. + --keep-lc LC[,LC]* only used in conjunction with -m/--mail. + Keeps certain emails in the message_queue based on a + comma-separated list of LETTER_CODES --merged purged completed entries from need_merge_authorities. --messages DAYS purge entries from messages table older than DAYS days. Defaults to 365 days if no days specified. @@ -136,6 +139,7 @@ my $sess_days; my $verbose; my $zebraqueue_days; my $mail; +my $letter_codes; my $purge_merged; my $pImport; my $pLogs; @@ -181,6 +185,7 @@ GetOptions( 'sessdays:i' => \$sess_days, 'v|verbose' => \$verbose, 'm|mail:i' => \$mail, + 'keep-lc:s' => \$letter_codes, 'zebraqueue:i' => \$zebraqueue_days, 'merged' => \$purge_merged, 'import:i' => \$pImport, @@ -350,20 +355,25 @@ if ($zebraqueue_days) { if ($mail) { my $count = 0; print "Mail queue purge triggered for $mail days.\n" if $verbose; - $sth = $dbh->prepare( - q{ - DELETE FROM message_queue - WHERE time_queued < date_sub(curdate(), INTERVAL ? DAY) - } - ); - if ($confirm) { - $sth->execute($mail) or die $dbh->errstr; + print "Not deleting emails of type $letter_codes.\n" if ($verbose && $letter_codes); + my $query = q{ + DELETE FROM message_queue + WHERE time_queued < date_sub(curdate(), INTERVAL ? DAY) + }; + my @bind_params = ($mail); + if ($letter_codes) { + $letter_codes =~ s/ //g; + my @codes = map { "$_" } split(',', $letter_codes); + $query .= " AND letter_code NOT IN ( ".(join ",", ("?") x @codes)." )"; + push @bind_params, @codes; + } + $sth = $dbh->prepare($query); + if ( $confirm ) { + $sth->execute(@bind_params) or die $dbh->errstr; $count = $sth->rows; } - if ($verbose) { - say $confirm - ? "$count messages were deleted from the mail queue." - : "Message from message_queue would have been deleted"; + if ( $verbose ) { + say $confirm ? "$count messages were deleted from the mail queue." : "Message from message_queue would have been deleted"; say "Done with message_queue purge."; } } -- 2.34.1