File:
[LON-CAPA] /
loncom /
metadata_database /
cleanup_database.pl
Revision
1.4:
download - view:
text,
annotated -
select for diffs
Wed Jun 9 12:39:39 2004 UTC (20 years, 3 months ago) by
matthew
Branches:
MAIN
CVS tags:
version_2_1_X,
version_2_1_99_2,
version_2_1_99_1,
version_2_1_99_0,
version_2_1_3,
version_2_1_2,
version_2_1_1,
version_2_1_0,
version_2_0_X,
version_2_0_99_1,
version_2_0_2,
version_2_0_1,
version_2_0_0,
version_1_99_3,
version_1_99_2,
version_1_99_1_tmcc,
version_1_99_1,
version_1_99_0_tmcc,
version_1_99_0,
version_1_3_X,
version_1_3_3,
version_1_3_2,
version_1_3_1,
version_1_3_0,
version_1_2_X,
version_1_2_99_1,
version_1_2_99_0,
version_1_2_1,
version_1_2_0,
version_1_1_99_5,
version_1_1_99_4,
version_1_1_99_3,
version_1_1_99_2,
version_1_1_99_1,
HEAD
Lengthen cache time - this might make life a little easier for instructors
doing statistics.
#!/usr/bin/perl
# The LearningOnline Network
# cleanup_database.pl Remove stale temporary search results.
#
# $Id: cleanup_database.pl,v 1.4 2004/06/09 12:39:39 matthew Exp $
#
# Copyright Michigan State University Board of Trustees
#
# This file is part of the LearningOnline Network with CAPA (LON-CAPA).
#
# LON-CAPA 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.
#
# LON-CAPA 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 LON-CAPA; if not, write to the Free Software
# Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
#
# /home/httpd/html/adm/gpl.txt
#
# http://www.lon-capa.org/
#
#################################################
=pod
=head1 NAME
cleanup_database.pl - Remove temporary tables from the LON-CAPA MySQL database.
=head1 SYNOPSIS
cleanup_database.pl drops tables from the LON-CAPA MySQL database if their
comment is 'temporary' and they have not been modified in a given number
of seconds.
=head1 DESCRIPTION
There are two command line arguements possible.
=over 4
=item help
Display a brief help message.
=item killtime <time>
The time in seconds that must have passed since the last update of a table
before it will be dropped.
=back
The following invocation is equivalent to the default:
cleanup_database.pl --killtime 86400
If you desire the immediate cleanup of temporary tables, use the following:
cleanup_database.pl --killtime 0
Depending on permissions, you may have to run this script as root.
=cut
#################################################
use strict;
use lib '/home/httpd/lib/perl/';
use LONCAPA::Configuration;
use Getopt::Long;
use Time::Local;
use DBI;
my $help = 0;
my $killtime = 86400*2;
GetOptions( "killtime=s" => \$killtime,
"help" => \$help );
if ($help) {
print <<ENDHELP;
cleanup_database.pl Cleans up the LON-CAPA MySQL database by removing
temporary tables.
Command line arguements
--killtime <number> The number of seconds a temporary table is allowed
to live. Defaults to 86400 (1 day)
--help Print out this help message.
Examples:
cleanup_database.pl --killtime 0
cleanup_database.pl --killtime 86400
Note: You will probably need to execute this script as root.
ENDHELP
exit;
}
# --------------- Read loncapa_apache.conf and loncapa.conf and get variables
my %perlvar = %{&LONCAPA::Configuration::read_conf('loncapa.conf')};
delete $perlvar{'lonReceipt'}; # remove since sensitive and not needed
my $dbh;
# ------------------------------------- Make sure that database can be accessed
unless ($dbh = DBI->connect("DBI:mysql:loncapa","www",
$perlvar{'lonSqlAccess'},
{RaiseError=>0,PrintError=>0}
)
) {
print "Cannot connect to database!\n";
exit;
}
my $sth = $dbh->prepare("SHOW TABLE STATUS");
$sth->execute();
my $results = $sth->fetchall_arrayref;
foreach my $array (@$results) {
next if ($array->[14] ne 'temporary'); # [14] is the comment
my $name = $array->[0];
# [10] in status report is creation time.
# [11] in status report is update (last modification?) time.
my $tabletime = $array->[11];
# Times are like: 2002-07-25 10:17:08
my ($year,$month,$day,$hour,$min,$sec)=
($tabletime =~ /(\d+)-(\d+)-(\d+) (\d+):(\d+):(\d+)/);
my $epoch_seconds = timelocal($sec,$min,$hour,$day,$month-1,$year-1900);
if ((time - $epoch_seconds) > $killtime) {
$dbh->do('DROP TABLE '.$name);
}
}
# --------------------------------------------------- Close database connection
$dbh->disconnect;
FreeBSD-CVSweb <freebsd-cvsweb@FreeBSD.org>