From 9ad9e65d3fdb6b906fc984da1adce844ae941b20 Mon Sep 17 00:00:00 2001
From: Marcel de Rooy <M.de.Rooy@rijksmuseum.nl>
Date: Thu, 29 Jul 2010 12:46:02 +0000
Subject: [PATCH] Enhancement 5074 (Adding possibility to cleanup_database.pl
 to purge only older sessions) [for 3.0.x and master]

Currently, the misc/cronjobs script cleanup_database truncates the session table (deleting all records, including active sessions).
With an additional parameter sessdays, this behavior could be changed or (perhaps better) extended. If the parameter sessdays is passed along with a number of days, the script only deletes older session records. This is accomplished by examining the values of lasttime, atime or ctime in the record.
So, calling the script like:
  ./cleanup_database.pl -v -sessions -sessdays 7
will only delete sessions records older than 7 days. The "old style" call
  ./cleanup_database.pl -v -sessions
still works too and truncates the table as before.
Patch can be applied to 3.0.x and master.
---
 misc/cronjobs/cleanup_database.pl | 46 +++++++++++++++++++++++++++++--
 1 file changed, 43 insertions(+), 3 deletions(-)

diff --git a/misc/cronjobs/cleanup_database.pl b/misc/cronjobs/cleanup_database.pl
index 47b9c26b34..f68e3eb993 100755
--- a/misc/cronjobs/cleanup_database.pl
+++ b/misc/cronjobs/cleanup_database.pl
@@ -37,21 +37,23 @@ use Getopt::Long;
 
 sub usage {
     print STDERR <<USAGE;
-Usage: $0 [-h|--help] [--sessions] [-v|--verbose] [--zebraqueue DAYS]
+Usage: $0 [-h|--help] [--sessions] [--sessdays DAYS] [-v|--verbose] [--zebraqueue DAYS]
    -h --help         prints this help message, and exits, ignoring all other options
    --sessions        purge the sessions table.  If you use this while users are logged
                      into Koha, they will have to reconnect.
+   --sessdays DAYS   purge only sessions older than DAYS days (use together with sessions parameter).
    -v --verbose      will cause the script to give you a bit more information about the run.
    --zebraqueue DAYS purge completed entries from the zebraqueue from more than DAYS days ago.
 USAGE
     exit $_[0];
 }
 
-my ($help, $sessions, $verbose, $zebraqueue_days);
+my ($help, $sessions, $sess_days, $verbose, $zebraqueue_days);
 
 GetOptions(
     'h|help' => \$help,
     'sessions' => \$sessions,
+    'sessdays:i' => \$sess_days,
     'v|verbose' => \$verbose,
     'zebraqueue:i' => \$zebraqueue_days,
 ) || usage(1);
@@ -71,7 +73,7 @@ my $sth;
 my $sth2;
 my $count;
 
-if ($sessions) {
+if ($sessions && !$sess_days) { #old behavior
     if ($verbose){
         print "Session purge triggered.\n";
         $sth = $dbh->prepare("SELECT COUNT(*) FROM sessions");
@@ -85,6 +87,15 @@ if ($sessions) {
         print "Done with session purge.\n";
     }
 }
+elsif($sessions && $sess_days>0) { #new behavior with number of days old
+    if ($verbose){
+        print "Session purge triggered with days>$sess_days.\n";
+    }
+    RemoveOldSessions();
+    if ($verbose){
+        print "Done with session purge with days>$sess_days.\n";
+    }
+}
 
 if ($zebraqueue_days){
     $count = 0;
@@ -104,3 +115,32 @@ if ($zebraqueue_days){
     }
 }
 exit(0);
+
+sub RemoveOldSessions {
+  my ($id, $a_session, $limit, $lasttime);
+  $limit= time() - 24*3600*$sess_days;
+
+  $sth= $dbh->prepare("SELECT id, a_session FROM sessions");
+  $sth->execute or die $dbh->errstr;
+  $sth->bind_columns(\$id, \$a_session);
+  $sth2 = $dbh->prepare("DELETE FROM sessions WHERE id=?");
+  $count=0;
+
+  while ($sth->fetch) {
+    $lasttime=0;
+    if($a_session =~ /lasttime:\s+(\d+)/) {
+	$lasttime= $1;
+    }
+    elsif($a_session =~ /(ATIME|CTIME):\s+(\d+)/ ) {
+	$lasttime= $2;
+    }
+    if($lasttime && $lasttime < $limit) {
+	$sth2->execute($id) or die $dbh->errstr;
+	$count++;
+    }
+  }
+  if ($verbose){
+      print "$count sessions were deleted.\n";
+  }
+}
+
-- 
2.39.5