From eb849c690aae34074afa15726c00af0532084618 Mon Sep 17 00:00:00 2001 From: "J. David Bavousett" Date: Sat, 1 Aug 2009 19:38:44 -0400 Subject: [PATCH] Created database-tidying cron script 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. Signed-off-by: Galen Charlton --- misc/cronjobs/cleanup_database.pl | 106 ++++++++++++++++++++++++++++++ misc/cronjobs/crontab.example | 2 + 2 files changed, 108 insertions(+) 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 0000000000..47b9c26b34 --- /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 < \$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 9cd7ca328e..bdb58f70a1 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 -- 2.39.5