From 9ddb78f0d36910139a6dea4b0375a760f77d65c4 Mon Sep 17 00:00:00 2001
From: Fridolin Somers <fridolin.somers@biblibre.com>
Date: Fri, 28 Jul 2017 14:05:03 +0200
Subject: [PATCH] Bug 19008: More database cleanups - deleted catalog

misc/cronjobs/cleanup_database.pl provides some database cleanup.
Yet some tables that could need cleanup are not in this script.

This patch adds cleanup for deleted catalog tables.
Note that deletedbiblio_metadata is managed by foreign key on biblionumber.

Test plan :
- Count :
select count(*),year(timestamp) from deleteditems group by year(timestamp);
select count(*),year(timestamp) from deletedbiblio group by year(timestamp);
select count(*),year(timestamp) from deletedbiblioitems group by year(timestamp);
- Run cleanup : misc/cronjobs/cleanup_database.pl -v --deleted-catalog 30
- Recount

Signed-off-by: Mark Tompsett <mtompset@hotmail.com>
Signed-off-by: Kyle M Hall <kyle@bywatersolutions.com>
Signed-off-by: Jonathan Druart <jonathan.druart@bugs.koha-community.org>
Signed-off-by: Martin Renvoize <martin.renvoize@ptfs-europe.com>
---
 misc/cronjobs/cleanup_database.pl | 34 ++++++++++++++++++++++++++++++-
 1 file changed, 33 insertions(+), 1 deletion(-)

diff --git a/misc/cronjobs/cleanup_database.pl b/misc/cronjobs/cleanup_database.pl
index 875a53a986..32a4cdddc1 100755
--- a/misc/cronjobs/cleanup_database.pl
+++ b/misc/cronjobs/cleanup_database.pl
@@ -45,7 +45,7 @@ use Koha::UploadedFiles;
 
 sub usage {
     print STDERR <<USAGE;
-Usage: $0 [-h|--help] [--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]
+Usage: $0 [-h|--help] [--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]
 
    -h --help          prints this help message, and exits, ignoring all
                       other options
@@ -84,6 +84,8 @@ Usage: $0 [-h|--help] [--sessions] [--sessdays DAYS] [-v|--verbose] [--zebraqueu
    --uploads-missing FLAG Delete upload records for missing files when FLAG is true, count them otherwise
    --oauth-tokens     Delete expired OAuth2 tokens
    --statistics DAYS       Purge entries from statistics older than DAYS days.
+   --deleted-catalog  DAYS Purge deleted catalog older than DAYS
+                           in tables deleteditems, deletedbiblioitems, deletedbiblio_metadata and deletedbiblio
 USAGE
     exit $_[0];
 }
@@ -111,6 +113,7 @@ my $temp_uploads_days;
 my $uploads_missing;
 my $oauth_tokens;
 my $pStatistics;
+my $pDeletedCatalog;
 
 GetOptions(
     'h|help'            => \$help,
@@ -136,6 +139,7 @@ GetOptions(
     'uploads-missing:i' => \$uploads_missing,
     'oauth-tokens'      => \$oauth_tokens,
     'statistics:i'      => \$pStatistics,
+    'deleted-catalog:i' => \$pDeletedCatalog,
 ) || usage(1);
 
 # Use default values
@@ -171,6 +175,7 @@ unless ( $sessions
     || defined $uploads_missing
     || $oauth_tokens
     || $pStatistics
+    || $pDeletedCatalog
 ) {
     print "You did not specify any cleanup work for the script to do.\n\n";
     usage(1);
@@ -369,6 +374,33 @@ if ($pStatistics) {
     print "Done with purging statistics.\n" if $verbose;
 }
 
+if ($pDeletedCatalog) {
+    print "Purging deleted catalog older than $pDeletedCatalog days.\n" if $verbose;
+    my $sth1 = $dbh->prepare(
+        q{
+            DELETE FROM deleteditems
+            WHERE timestamp < DATE_SUB(CURDATE(), INTERVAL ? DAY)
+        }
+    );
+    my $sth2 = $dbh->prepare(
+        q{
+            DELETE FROM deletedbiblioitems
+            WHERE timestamp < DATE_SUB(CURDATE(), INTERVAL ? DAY)
+        }
+    );
+    my $sth3 = $dbh->prepare(
+        q{
+            DELETE FROM deletedbiblio
+            WHERE timestamp < DATE_SUB(CURDATE(), INTERVAL ? DAY)
+        }
+    );
+    # deletedbiblio_metadata is managed by FK with deletedbiblio
+    $sth1->execute($pDeletedCatalog);
+    $sth2->execute($pDeletedCatalog);
+    $sth3->execute($pDeletedCatalog);
+    print "Done with purging deleted catalog.\n" if $verbose;
+}
+
 exit(0);
 
 sub RemoveOldSessions {
-- 
2.39.5