Merge remote branch 'kc/new/bug_5308' into kcmaster

[koha_gimpoz] / misc / cronjobs / cleanup_database.pl

diff --git a/misc/cronjobs/cleanup_database.pl b/misc/cronjobs/cleanup_database.pl
index 47b9c26..7d5bcd1 100755 (executable)
--- a/misc/cronjobs/cleanup_database.pl
+++ b/misc/cronjobs/cleanup_database.pl
@@ -1,4 +1,4 @@
-#!/usr/bin/perl -w
+#!/usr/bin/perl
 
 # Copyright 2009 PTFS, Inc.
 #
@@ -13,9 +13,9 @@
 # WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR
 # A PARTICULAR PURPOSE.  See the GNU General Public License for more details.
 #
-# You should have received a copy of the GNU General Public License along with
-# Koha; if not, write to the Free Software Foundation, Inc., 59 Temple Place,
-# Suite 330, Boston, MA  02111-1307 USA
+# You should have received a copy of the GNU General Public License along
+# with Koha; if not, write to the Free Software Foundation, Inc.,
+# 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA.
 
 use strict;
 use warnings;
@@ -37,22 +37,30 @@ use Getopt::Long;
 
 sub usage {
     print STDERR <<USAGE;
-Usage: $0 [-h|--help] [--sessions] [-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.
-   -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: $0 [-h|--help] [--sessions] [--sessdays DAYS] [-v|--verbose] [--zebraqueue DAYS]
+        [-m|--mail]
+   -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.
+   -m --mail          purge the mail queue. 
 USAGE
     exit $_[0];
 }
 
-my ($help, $sessions, $verbose, $zebraqueue_days);
+my ($help, $sessions, $sess_days, $verbose, $zebraqueue_days, $mail);
 
 GetOptions(
-    'h|help' => \$help,
-    'sessions' => \$sessions,
+    'h|help'    => \$help,
+    'sessions'  => \$sessions,
+    'sessdays:i' => \$sess_days,
     'v|verbose' => \$verbose,
+    'm|mail'    => \$mail,
     'zebraqueue:i' => \$zebraqueue_days,
 ) || usage(1);
 
@@ -60,7 +68,7 @@ if ($help) {
     usage(0);
 }
 
-if (!($sessions || $zebraqueue_days)){
+if (!($sessions || $zebraqueue_days || $mail)){
     print "You did not specify any cleanup work for the script to do.\n\n";
     usage(1);
 }
@@ -71,7 +79,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 +93,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;
@@ -103,4 +120,44 @@ if ($zebraqueue_days){
         print "$count records were deleted.\nDone with zebraqueue purge.\n";
     }
 }
+
+if ($mail) {
+    if ($verbose) {
+        $sth = $dbh->prepare("SELECT COUNT(*) FROM message_queue");
+        $sth->execute() or die $dbh->errstr;
+        my @count_arr = $sth->fetchrow_array;
+        print "Deleting $count_arr[0] entries from the mail queue.\n";
+    }
+    $sth = $dbh->prepare("TRUNCATE message_queue");
+    $sth->execute() or $dbh->errstr;
+    print "Done with purging the mail queue.\n" if ($verbose);
+}
 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";
+  }
+}