From 2e620d05401931a6416125a64efb60b25a002636 Mon Sep 17 00:00:00 2001 From: Marcel de Rooy Date: Mon, 21 Nov 2011 12:21:11 +0100 Subject: [PATCH] Bug 7240: Cleaning up import tables and action_logs Content-Type: text/plain; charset="utf-8" This patch lets cleanup_database also purge older records from the (five) import tables and the action_logs table. Two new command line parameters are introduced: --import and --logs. If no number of days is specified for --zebraqueue, --import or --logs, it defaults to 30 days, 60 days resp. 180 days. I did not add a default for --sessdays, because this parameter cannot be seen separately from parameter --sessions. --- misc/cronjobs/cleanup_database.pl | 62 +++++++++++++++++++++++++++++++----- 1 files changed, 53 insertions(+), 9 deletions(-) diff --git a/misc/cronjobs/cleanup_database.pl b/misc/cronjobs/cleanup_database.pl index 12aa12b..99310df 100755 --- a/misc/cronjobs/cleanup_database.pl +++ b/misc/cronjobs/cleanup_database.pl @@ -20,8 +20,11 @@ use strict; use warnings; -BEGIN { +use constant DEFAULT_ZEBRAQ_PURGEDAYS => 30; +use constant DEFAULT_IMPORT_PURGEDAYS => 60; +use constant DEFAULT_LOGS_PURGEDAYS => 180; +BEGIN { # find Koha's Perl modules # test carefully before changing this use FindBin; @@ -31,14 +34,11 @@ BEGIN { use C4::Context; use C4::Dates; -#use C4::Debug; -#use C4::Letters; -#use File::Spec; use Getopt::Long; sub usage { print STDERR < \$help, @@ -65,14 +69,22 @@ GetOptions( 'm|mail' => \$mail, 'zebraqueue:i' => \$zebraqueue_days, 'merged' => \$purge_merged, + 'import:i' => \$pImport, + 'logs:i' => \$pLogs, ) || usage(1); + $sessions=1 if $sess_days && $sess_days>0; +# if --import, --logs or --zebraqueue were passed without number of days, +# use defaults +$pImport= DEFAULT_IMPORT_PURGEDAYS if defined($pImport) && $pImport==0; +$pLogs= DEFAULT_LOGS_PURGEDAYS if defined($pLogs) && $pLogs==0; +$zebraqueue_days= DEFAULT_ZEBRAQ_PURGEDAYS if defined($zebraqueue_days) && $zebraqueue_days==0; if ($help) { usage(0); } -if ( !( $sessions || $zebraqueue_days || $mail || $purge_merged) ) { +if ( !( $sessions || $zebraqueue_days || $mail || $purge_merged || $pImport || $pLogs) ) { print "You did not specify any cleanup work for the script to do.\n\n"; usage(1); } @@ -145,6 +157,19 @@ if($purge_merged) { print "Done with purging need_merge_authorities.\n" if $verbose; } +if($pImport) { + print "Purging records from import tables.\n" if $verbose; + PurgeImportTables(); + print "Done with purging import tables.\n" if $verbose; +} + +if($pLogs) { + print "Purging records from action_logs.\n" if $verbose; + $sth = $dbh->prepare("DELETE FROM action_logs WHERE timestamp < date_sub(curdate(), interval ? DAY)"); + $sth->execute($pLogs) or die $dbh->errstr; + print "Done with purging action_logs.\n" if $verbose; +} + exit(0); sub RemoveOldSessions { @@ -173,3 +198,22 @@ sub RemoveOldSessions { print "$count sessions were deleted.\n"; } } + +sub PurgeImportTables { + #First purge import_records + #Delete cascades to import_biblios, import_items and import_record_matches + $sth = $dbh->prepare("DELETE FROM import_records WHERE upload_timestamp < date_sub(curdate(), interval ? DAY)"); + $sth->execute($pImport) or die $dbh->errstr; + + # Now purge import_batches + # Timestamp cannot be used here without care, because records are added + # continuously to batches without updating timestamp (z3950 search). + # So we only delete older empty batches. + # This delete will therefore not have a cascading effect. + $sth = $dbh->prepare("DELETE ba + FROM import_batches ba + LEFT JOIN import_records re ON re.import_batch_id=ba.import_batch_id + WHERE re.import_record_id IS NULL AND + ba.upload_timestamp < date_sub(curdate(), interval ? DAY)"); + $sth->execute($pImport) or die $dbh->errstr; +} -- 1.6.0.6