From 1cb57396410161f866d1d8822c82bcae68a0ec23 Mon Sep 17 00:00:00 2001
From: Aleisha Amohia <aleishaamohia@hotmail.com>
Date: Fri, 16 Jun 2023 14:06:21 +1200
Subject: [PATCH] Bug 34027: Add functionality to cleanup_database.pl to purge
 action logs in batches

This enhancement gives libraries the option to purge action logs 'slowly', in batches. This is useful for when there are many, many rows to purge (i.e. millions).

To test, set up some sample logs (you'll need to change the timestamp in the database so they are picked up by the cronjob), run the cleanup_database.pl script with the --logs parameter and confirm it behaves as expected.

For example:

sudo koha-foreach --chdir --enabled /kohadevbox/koha/misc/cronjobs/cleanup_database.pl --confirm --logs 1 --logs-batch 500 --verbose

Test cases:

1. logs argument, no logs-batch argument. Cleanup should run as normal, in one query.

sudo koha-foreach --chdir --enabled /kohadevbox/koha/misc/cronjobs/cleanup_database.pl --confirm --logs 1 --verbose

2. logs argument, logs-batch argument with no ROWS specified. logs-batch should default to 1000, deletions done in batches of 1000 rows at a time.

sudo koha-foreach --chdir --enabled /kohadevbox/koha/misc/cronjobs/cleanup_database.pl --confirm --logs 1 --logs-batch --verbose

3. logs argument, logs-batch argument with ROWS specified. Deletions should be done in batches of the specified ROWS

sudo koha-foreach --chdir --enabled /kohadevbox/koha/misc/cronjobs/cleanup_database.pl --confirm --logs 1 --logs-batch 500 --verbose

Sponsored-by: Auckland University of Technology
---
 misc/cronjobs/cleanup_database.pl | 23 ++++++++++++++++++++++-
 1 file changed, 22 insertions(+), 1 deletion(-)

diff --git a/misc/cronjobs/cleanup_database.pl b/misc/cronjobs/cleanup_database.pl
index 60b36520121..42d38636965 100755
--- a/misc/cronjobs/cleanup_database.pl
+++ b/misc/cronjobs/cleanup_database.pl
@@ -30,6 +30,7 @@ use constant DEFAULT_DEBARMENTS_PURGEDAYS         => 30;
 use constant DEFAULT_JOBS_PURGEDAYS               => 1;
 use constant DEFAULT_JOBS_PURGETYPES              => qw{ update_elastic_index };
 use constant DEFAULT_EDIFACT_MSG_PURGEDAYS        => 365;
+use constant DEFAULT_LOGS_BATCHSIZE               => 1000;
 
 use Getopt::Long qw( GetOptions );
 
@@ -87,6 +88,8 @@ Usage: $0 [-h|--help] [--confirm] [--sessions] [--sessdays DAYS] [-v|--verbose]
    --log-action       Specify which action log action entries to trim. Repeatable.
    --logs DAYS        purge entries from action_logs older than DAYS days.
                       Defaults to 180 days if no days specified.
+   --logs-batch ROWS  Delete action_logs rows in batches of ROWS. Ignored if --logs is not set.
+                      Defaults to 1000.
    --searchhistory DAYS  purge entries from search_history older than DAYS days.
                          Defaults to 30 days if no days specified
    --list-invites  DAYS  purge (unaccepted) list share invites older than DAYS
@@ -168,6 +171,7 @@ my $jobs_days;
 my @jobs_types;
 my $reports;
 my $edifact_msg_days;
+my $logs_batch;
 
 my $command_line_options = join(" ",@ARGV);
 
@@ -215,6 +219,7 @@ GetOptions(
     'jobs-days:i'                => \$jobs_days,
     'reports:i'                  => \$reports,
     'edifact-messages:i'         => \$edifact_msg_days,
+    'logs-batch:i'               => \$logs_batch,
 ) || usage(1);
 
 # Use default values
@@ -230,6 +235,7 @@ $pMessages         = DEFAULT_MESSAGES_PURGEDAYS           if defined($pMessages)
 $jobs_days         = DEFAULT_JOBS_PURGEDAYS               if defined($jobs_days)         && $jobs_days == 0;
 @jobs_types        = (DEFAULT_JOBS_PURGETYPES)            if $jobs_days                  && @jobs_types == 0;
 $edifact_msg_days  = DEFAULT_EDIFACT_MSG_PURGEDAYS        if defined($edifact_msg_days)  && $edifact_msg_days == 0;
+$logs_batch        = DEFAULT_LOGS_BATCHSIZE               if defined($logs_batch)        && $logs_batch == 0;
 
 if ($help) {
     usage(0);
@@ -393,9 +399,24 @@ if ($pLogs) {
         $log_query .= " AND action IN (" . join(',',('?') x @log_actions ) . ")";
         push @query_params, @log_actions;
     }
+    if( $logs_batch ){
+        $log_query .= " LIMIT ?";
+        push @query_params, $logs_batch;
+    }
     $sth = $dbh->prepare( $log_query );
     if ( $confirm ) {
-        $sth->execute($pLogs, @query_params) or die $dbh->errstr;
+        if ( $logs_batch ) {
+            my $counter = 0;
+            my $rows = 0;
+            while ( $counter <= $rows ) {
+                $sth->execute($pLogs, @query_params) or die $dbh->errstr;
+                sleep 3;
+                $rows += $sth->rows;
+                $counter += $logs_batch;
+            }
+        } else {
+            $sth->execute($pLogs, @query_params) or die $dbh->errstr;
+        }
     }
     print "Done with purging action_logs.\n" if $verbose;
 }
-- 
2.30.2