Return to cleanup_database.pl CVS log

Up to [LON-CAPA] / loncom / metadata_database

- Lifetime of temporary md5_* tables (used to speed up access to student
  performance data, e.g., for Chart or Statistics), can be set as domain
  default (based on course type), and be overridden by a DC in a course.

#!/usr/bin/perl
# The LearningOnline Network
# cleanup_database.pl Remove stale temporary search results.
#
# $Id: cleanup_database.pl,v 1.8 2016/08/15 18:01:14 raeburn 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 will drop all tables without updates in the past
two days (the default).

 cleanup_database.pl --killtime 172800

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 Apache::lonnet;
use Getopt::Long;
use Time::Local;
use DBI;
use Digest::MD5();

my $help = 0;
my $killtime;
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. On a library server, specifying this argument
                            also overrides any course-specific or domain-specific
                            lifetimes which apply to the various md5_hash_* tables
                            which contain student data for a course, for which the
                            current server is the homeserver.
   --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_hashref('Name');

my ($nokilltime,$gotconf,%coursetypes,%md5hashes,%domcrsdefs,%gotcourseenv,%crssetting);
if ($killtime eq '') {
    $killtime = 86400*2;
    if ($perlvar{'lonRole'} eq 'library') {
        $nokilltime = 1; 
    }
}

foreach my $name (keys(%$results)) {
    next if ($results->{$name}{Comment} ne 'temporary');
    my $tabletime = $results->{$name}{Update_time};
    # 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);
    my $currkilltime = $killtime;
    if ($nokilltime) {
        if ($name =~ /^md5_(\w+)_(?:\w+)$/) {
            my $hashid = $1;
            unless ($gotconf) {
                &get_config(\%coursetypes,\%md5hashes,\%domcrsdefs);
                $gotconf = 1;
            }
            if (exists($md5hashes{$hashid})) {
                my ($cdom,$cnum) = split(/_/,$md5hashes{$hashid});  
                unless ($gotcourseenv{$md5hashes{$hashid}}) {
                    my %envhash = &Apache::lonnet::dump('environment',$cdom,$cnum);
                    $gotcourseenv{$md5hashes{$hashid}} = 1;
                    if ($coursetypes{$md5hashes{$hashid}} eq 'unofficial') {
                         if ($envhash{'internal.textbook'}) {
                             $coursetypes{$md5hashes{$hashid}} = 'textbook';
                         }
                    }
                    $crssetting{$md5hashes{$hashid}} = $envhash{'internal.mysqltables'};
                }
                if (($crssetting{$md5hashes{$hashid}} ne '') && ($crssetting{$md5hashes{$hashid}} !~ /^\D/)) {
                    $currkilltime = $crssetting{$md5hashes{$hashid}};
                } elsif (ref($domcrsdefs{$cdom}) eq 'HASH') {
                    if (($domcrsdefs{$cdom}{$coursetypes{$md5hashes{$hashid}}} ne '') &&
                        ($domcrsdefs{$cdom}{$coursetypes{$md5hashes{$hashid}}} !~ /^\D/)) {
                        $currkilltime = $domcrsdefs{$cdom}{$coursetypes{$md5hashes{$hashid}}};
                    }
                }
            }
        }
    }
    if ((time - $epoch_seconds) > $currkilltime) {
        $dbh->do('DROP TABLE '.$name);
    }
}
$sth->finish();

# --------------------------------------------------- Close database connection
$dbh->disconnect;

sub get_config {
    my ($coursetypes,$md5hashref,$domsettings) = @_;
    my @domains = sort(&Apache::lonnet::current_machine_domains());
    my @ids=&Apache::lonnet::current_machine_ids();
    foreach my $dom (@domains) {
        my %domconfig = &Apache::lonnet::get_dom('configuration',['coursedefaults'],$dom);
        if (ref($domconfig{'coursedefaults'}) eq 'HASH') {
            if (ref($domconfig{'coursedefaults'}{'mysqltables'}) eq 'HASH') {
                $domsettings->{$dom} = $domconfig{'coursedefaults'}{'mysqltables'};
            }
        }
        my %currhash = &Apache::lonnet::courseiddump($dom,'.',1,'.','.','.',1,\@ids,'.');
        if (keys(%currhash)) {
            foreach my $key (keys(%currhash)) {
                if ($key ne '') {
                    if (ref($currhash{$key}) eq 'HASH') {
                        my $digest = &Digest::MD5::md5_hex($key);
                        $md5hashref->{$digest} = $key;
                        my $crstype = $currhash{$key}{'type'}; 
                        my $longcrstype = 'unofficial';
                        if ($crstype eq 'Community') {
                            $longcrstype = 'community';
                        } elsif ($crstype eq 'Placement') {
                            $longcrstype = 'placement';
                        } elsif ($currhash{$key}{'inst_code'}) {
                            $longcrstype = 'official';
                        }
                        $coursetypes->{$key} = $longcrstype;
                    }
                }
            }
        }
    }
    return;
}