From b53e441b521983b290e2ae5e5b7f50c682651adf Mon Sep 17 00:00:00 2001
From: Nick Clemens <nick@bywatersolutions.com>
Date: Thu, 13 Oct 2022 18:37:07 +0000
Subject: [PATCH] Bug 31629: Add elasticqueue options to cleanup_database
MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 8bit

This patch adds an elasticqueue option to cleanup_database.pl to allow
for purging completed reindexing jobs

To test:
 1 - Enable elastic search in Koha
 2 - perl misc/maintenance/touch_all_items.pl
 3 - Check db and note there are a bunch of elastic reindex jobs
 4 - Update to make them old
     UPDATE background_jobs SET ended_on = '2022-10-01 00:00:00' WHERE type = 'update_elastic_index'
 5 - perl misc/cronjobs/cleanup_database.pl
 6 - Note elasticqueue entry shows in help
 7 - perl misc/cronjobs/cleanup_database.pl --elasticqueue 1 -v
 8 - Note that elasticqueue would have been cleared
 9 - perl misc/cronjobs/cleanup_database.pl --elasticqueue 1 -v --confirm
10 - Note that number of entries deleted is reported
11 - Confirm in staff interface that jobs are gone:
     http://localhost:8081/cgi-bin/koha/admin/background_jobs.pl
     (Uncheck 'Current jobs only')

Signed-off-by: David Nind <david@davidnind.com>
Signed-off-by: Frédéric Demians <f.demians@tamil.fr>
---
 misc/cronjobs/cleanup_database.pl | 28 +++++++++++++++++++++++++++-
 1 file changed, 27 insertions(+), 1 deletion(-)

diff --git a/misc/cronjobs/cleanup_database.pl b/misc/cronjobs/cleanup_database.pl
index dc62ef8aea..b0990f156a 100755
--- a/misc/cronjobs/cleanup_database.pl
+++ b/misc/cronjobs/cleanup_database.pl
@@ -20,6 +20,7 @@
 use Modern::Perl;
 
 use constant DEFAULT_ZEBRAQ_PURGEDAYS             => 30;
+use constant DEFAULT_ELASTICQ_PURGEDAYS           => 30;
 use constant DEFAULT_MAIL_PURGEDAYS               => 30;
 use constant DEFAULT_IMPORT_PURGEDAYS             => 60;
 use constant DEFAULT_LOGS_PURGEDAYS               => 180;
@@ -49,7 +50,7 @@ use Koha::Patron::Debarments qw( DelDebarment );
 
 sub usage {
     print STDERR <<USAGE;
-Usage: $0 [-h|--help] [--confirm] [--sessions] [--sessdays DAYS] [-v|--verbose] [--zebraqueue DAYS] [-m|--mail] [--merged] [--import DAYS] [--logs DAYS] [--searchhistory DAYS] [--restrictions DAYS] [--all-restrictions] [--fees DAYS] [--temp-uploads] [--temp-uploads-days DAYS] [--uploads-missing 0|1 ] [--statistics DAYS] [--deleted-catalog DAYS] [--deleted-patrons DAYS] [--old-issues DAYS] [--old-reserves DAYS] [--transfers DAYS] [--labels DAYS] [--cards DAYS]
+Usage: $0 [-h|--help] [--confirm] [--sessions] [--sessdays DAYS] [-v|--verbose] [--zebraqueue DAYS] [--elasticqueue DAYS] [-m|--mail] [--merged] [--import DAYS] [--logs DAYS] [--searchhistory DAYS] [--restrictions DAYS] [--all-restrictions] [--fees DAYS] [--temp-uploads] [--temp-uploads-days DAYS] [--uploads-missing 0|1 ] [--statistics DAYS] [--deleted-catalog DAYS] [--deleted-patrons DAYS] [--old-issues DAYS] [--old-reserves DAYS] [--transfers DAYS] [--labels DAYS] [--cards DAYS]
 
    -h --help          prints this help message, and exits, ignoring all
                       other options
@@ -61,6 +62,8 @@ Usage: $0 [-h|--help] [--confirm] [--sessions] [--sessdays DAYS] [-v|--verbose]
                       about the run.
    --zebraqueue DAYS  purge completed zebraqueue entries older than DAYS days.
                       Defaults to 30 days if no days specified.
+   --elasticqueue DAYS  purge completed elastic reindex background job queue entries older than DAYS days.
+                      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.
    --merged           purged completed entries from need_merge_authorities.
@@ -117,6 +120,7 @@ my $sessions;
 my $sess_days;
 my $verbose;
 my $zebraqueue_days;
+my $elasticqueue_days;
 my $mail;
 my $purge_merged;
 my $pImport;
@@ -159,6 +163,7 @@ GetOptions(
     'v|verbose'         => \$verbose,
     'm|mail:i'          => \$mail,
     'zebraqueue:i'      => \$zebraqueue_days,
+    'elasticqueue:i'    => \$elasticqueue_days,
     'merged'            => \$purge_merged,
     'import:i'          => \$pImport,
     'z3950'             => \$pZ3950,
@@ -209,6 +214,7 @@ if ($help) {
 
 unless ( $sessions
     || $zebraqueue_days
+    || $elasticqueue_days
     || $mail
     || $purge_merged
     || $pImport
@@ -303,6 +309,26 @@ if ($zebraqueue_days) {
     }
 }
 
+if ($elasticqueue_days) {
+    my $count = 0;
+    print "Elastic queue (background jobs) purge triggered for $elasticqueue_days days.\n" if $verbose;
+    $sth = $dbh->prepare(
+        q{
+            DELETE FROM background_jobs
+            WHERE status='finished' AND ended_on < date_sub(curdate(), INTERVAL ? DAY) AND
+            type = "update_elastic_index"
+        }
+    );
+    if ( $confirm ) {
+        $sth->execute($elasticqueue_days) or die $dbh->errstr;
+        $count = $sth->rows;
+    }
+    if ( $verbose ) {
+        say $confirm ? "$count elastic index jobs were deleted from the background jobs queue." : "Elastic reindex jobs from backgorund jobs queue would have been deleted";
+        say "Done with elasticqueue purge.";
+    }
+}
+
 if ($mail) {
     my $count = 0;
     print "Mail queue purge triggered for $mail days.\n" if $verbose;
-- 
2.34.1