[Koha-patches] [PATCH] Created database-tidying cron script

[Galen Charlton]

Pushed with thanks --gmc

On Sat, Aug 1, 2009 at 7:38 PM, J. David Bavousett<dbavousett at ptfs.com> wrote:
> This little script establishes a framework for database cleanup on some regular
> schedule.  Initial implementation provides for brute truncation of the sessions
> table, and selective-by-age cleanup of the zebraqueue.
> ---
>  misc/cronjobs/cleanup_database.pl |  106 +++++++++++++++++++++++++++++++++++++
>  misc/cronjobs/crontab.example     |    2 +
>  2 files changed, 108 insertions(+), 0 deletions(-)
>  create mode 100755 misc/cronjobs/cleanup_database.pl
>
> diff --git a/misc/cronjobs/cleanup_database.pl b/misc/cronjobs/cleanup_database.pl
> new file mode 100755
> index 0000000..830eac3
> --- /dev/null
> +++ b/misc/cronjobs/cleanup_database.pl
> @@ -0,0 +1,106 @@
> +#!/usr/bin/perl -w
> +
> +# Copyright 2009 PTFS, Inc.
> +#
> +# This file is part of Koha.
> +#
> +# Koha is free software; you can redistribute it and/or modify it under the
> +# terms of the GNU General Public License as published by the Free Software
> +# Foundation; either version 2 of the License, or (at your option) any later
> +# version.
> +#
> +# Koha is distributed in the hope that it will be useful, but WITHOUT ANY
> +# 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
> +
> +use strict;
> +use warnings;
> +
> +BEGIN {
> +
> +    # find Koha's Perl modules
> +    # test carefully before changing this
> +    use FindBin;
> +    eval { require "$FindBin::Bin/../kohalib.pl" };
> +}
> +
> +use C4::Context;
> +use C4::Dates;
> +#use C4::Debug;
> +#use C4::Letters;
> +#use File::Spec;
> +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
> +    exit $_[0];
> +}
> +
> +my ($help, $sessions, $verbose, $zebraqueue_days);
> +
> +GetOptions(
> +    'h|help' => \$help,
> +    'sessions' => \$sessions,
> +    'v|verbose' => \$verbose,
> +    'zebraqueue:i' => \$zebraqueue_days,
> +) || usage(1);
> +
> +if ($help) {
> +    usage(0);
> +}
> +
> +if (!($sessions || $zebraqueue_days)){
> +    print "You did not specify any cleanup work for the script to do.\n\n";
> +    usage(1);
> +}
> +
> +my $dbh = C4::Context->dbh();
> +my $query;
> +my $sth;
> +my $sth2;
> +my $count;
> +
> +if ($sessions) {
> +    if ($verbose){
> +        print "Session purge triggered.\n";
> +        $sth = $dbh->prepare("SELECT COUNT(*) FROM sessions");
> +        $sth->execute() or die $dbh->errstr;
> +        my @count_arr = $sth->fetchrow_array;
> +        print "$count_arr[0] entries will be deleted.\n";
> +    }
> +    $sth = $dbh->prepare("TRUNCATE sessions");
> +    $sth->execute() or die $dbh->errstr;;
> +    if ($verbose){
> +        print "Done with session purge.\n";
> +    }
> +}
> +
> +if ($zebraqueue_days){
> +    $count = 0;
> +    if ($verbose){
> +        print "Zebraqueue purge triggered for $zebraqueue_days days.\n";
> +    }
> +    $sth = $dbh->prepare("SELECT id,biblio_auth_number,server,time FROM zebraqueue
> +                          WHERE done=1 and time < date_sub(curdate(), interval ? day)");
> +    $sth->execute($zebraqueue_days) or die $dbh->errstr;
> +    $sth2 = $dbh->prepare("DELETE FROM zebraqueue WHERE id=?");
> +    while (my $record = $sth->fetchrow_hashref){
> +        $sth2->execute($record->{id}) or die $dbh->errstr;
> +        $count++;
> +    }
> +    if ($verbose){
> +        print "$count records were deleted.\nDone with zebraqueue purge.\n";
> +    }
> +}
> +exit(0);
> diff --git a/misc/cronjobs/crontab.example b/misc/cronjobs/crontab.example
> index 9cd7ca3..bdb58f7 100644
> --- a/misc/cronjobs/crontab.example
> +++ b/misc/cronjobs/crontab.example
> @@ -75,3 +75,5 @@ KOHA_CRON_PATH = /home/liblime/kohaclone/misc/cronjobs
>  # Obviously, one needs logrotate(8) to do this
>  # 20 1 * * *  /usr/sbin/logrotate -s /home/liblime/kohaclone/var/run/zebradb/logstatus /home/liblime/kohaclone/etc/logrotate.conf
>
> +# clean up databases nightly.  Be sure not to run this with --sessions during a time when the system is in use!
> +16 1 * * * $KOHA_CRON_PATH/cleanup_database.pl --sessions --zebraqueue 10
> --
> 1.5.6.5
>
> _______________________________________________
> Koha-patches mailing list
> Koha-patches at lists.koha.org
> http://lists.koha.org/mailman/listinfo/koha-patches
>



-- 
Galen Charlton
gmcharlt at gmail.com