Return to cleanup_database.pl CVS log

Up to [LON-CAPA] / loncom / metadata_database

- older DBI versions don't support the arrarref usage

    1: #!/usr/bin/perl
    2: # The LearningOnline Network
    3: # cleanup_database.pl Remove stale temporary search results.
    4: #
    5: # $Id: cleanup_database.pl,v 1.7 2006/11/20 17:07:57 albertel Exp $
    6: #
    7: # Copyright Michigan State University Board of Trustees
    8: #
    9: # This file is part of the LearningOnline Network with CAPA (LON-CAPA).
   10: #
   11: # LON-CAPA is free software; you can redistribute it and/or modify
   12: # it under the terms of the GNU General Public License as published by
   13: # the Free Software Foundation; either version 2 of the License, or
   14: # (at your option) any later version.
   15: #
   16: # LON-CAPA is distributed in the hope that it will be useful,
   17: # but WITHOUT ANY WARRANTY; without even the implied warranty of
   18: # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
   19: # GNU General Public License for more details.
   20: #
   21: # You should have received a copy of the GNU General Public License
   22: # along with LON-CAPA; if not, write to the Free Software
   23: # Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307  USA
   24: #
   25: # /home/httpd/html/adm/gpl.txt
   26: #
   27: # http://www.lon-capa.org/
   28: #
   29: #################################################
   30: 
   31: =pod
   32: 
   33: =head1 NAME
   34: 
   35: cleanup_database.pl - Remove temporary tables from the LON-CAPA MySQL database.
   36: 
   37: =head1 SYNOPSIS
   38: 
   39: cleanup_database.pl drops tables from the LON-CAPA MySQL database if their 
   40: comment is 'temporary' and they have not been modified in a given number 
   41: of seconds.
   42: 
   43: =head1 DESCRIPTION
   44: 
   45: There are two command line arguements possible.  
   46: 
   47: =over 4
   48: 
   49: =item help 
   50: 
   51: Display a brief help message.
   52: 
   53: =item killtime <time>
   54: 
   55: The time in seconds that must have passed since the last update of a table
   56: before it will be dropped.
   57: 
   58: =back
   59: 
   60: The following invocation is equivalent to the default:
   61: 
   62:  cleanup_database.pl --killtime 86400
   63: 
   64: If you desire the immediate cleanup of temporary tables, use the following:
   65: 
   66:  cleanup_database.pl --killtime 0
   67: 
   68: Depending on permissions, you may have to run this script as root.
   69: 
   70: =cut
   71: 
   72: #################################################
   73: 
   74: use strict;
   75: use lib '/home/httpd/lib/perl/';
   76: use LONCAPA::Configuration;
   77: use Getopt::Long;
   78: use Time::Local;
   79: use DBI;
   80: 
   81: my $help = 0;
   82: my $killtime = 86400*2;
   83: GetOptions( "killtime=s" => \$killtime, 
   84:             "help"       => \$help );
   85: if ($help) {
   86:     print <<ENDHELP;
   87: cleanup_database.pl     Cleans up the LON-CAPA MySQL database by removing 
   88:                         temporary tables.
   89: Command line arguements
   90:    --killtime  <number>     The number of seconds a temporary table is allowed
   91:                             to live.  Defaults to 86400 (1 day)
   92:    --help                   Print out this help message.
   93: 
   94: Examples:
   95: 
   96: cleanup_database.pl --killtime 0
   97: cleanup_database.pl --killtime 86400
   98: 
   99: Note:  You will probably need to execute this script as root.
  100: 
  101: ENDHELP
  102:     exit;
  103: }
  104: 
  105: # ---------------  Read loncapa_apache.conf and loncapa.conf and get variables
  106: my %perlvar = %{&LONCAPA::Configuration::read_conf('loncapa.conf')};
  107: delete $perlvar{'lonReceipt'}; # remove since sensitive and not needed
  108: 
  109: my $dbh;
  110: # ------------------------------------- Make sure that database can be accessed
  111: unless ($dbh = DBI->connect("DBI:mysql:loncapa","www",
  112:                             $perlvar{'lonSqlAccess'},
  113:                             {RaiseError=>0,PrintError=>0}
  114:                             )
  115:         ) {
  116:     print "Cannot connect to database!\n";
  117:     exit;
  118: }
  119: 
  120: my $sth = $dbh->prepare("SHOW TABLE STATUS");
  121: $sth->execute();
  122: my $results = $sth->fetchall_hashref('Name');
  123: 
  124: foreach my $name (keys(%$results)) {
  125:     next if ($results->{$name}{Comment} ne 'temporary'); 
  126:     my $tabletime = $results->{$name}{Update_time};
  127:     # Times are like: 2002-07-25 10:17:08
  128:     my ($year,$month,$day,$hour,$min,$sec)= 
  129:         ($tabletime =~ /(\d+)-(\d+)-(\d+) (\d+):(\d+):(\d+)/);
  130:     my $epoch_seconds = timelocal($sec,$min,$hour,$day,$month-1,$year-1900);
  131:     if ((time - $epoch_seconds) > $killtime) {
  132:         $dbh->do('DROP TABLE '.$name);
  133:     }
  134: }
  135: $sth->finish();
  136: 
  137: # --------------------------------------------------- Close database connection
  138: $dbh->disconnect;
  139: