Return to cleanup_database.pl CVS log

Up to [LON-CAPA] / loncom / metadata_database

This script will drop tables from the LON-CAPA MySQL database which are
flagged as 'temporary' in their comment field.

    1: #!/usr/bin/perl
    2: # The LearningOnline Network
    3: # searchcat.pl "Search Catalog" batch script
    4: #
    5: # $Id: cleanup_database.pl,v 1.1 2002/07/25 15:31:10 matthew 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;
   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_apache.conf',
  107:                                                    'loncapa.conf')};
  108: delete $perlvar{'lonReceipt'}; # remove since sensitive and not needed
  109: 
  110: my $dbh;
  111: # ------------------------------------- Make sure that database can be accessed
  112: unless ($dbh = DBI->connect("DBI:mysql:loncapa","www",
  113:                             $perlvar{'lonSqlAccess'},
  114:                             {RaiseError=>0,PrintError=>0}
  115:                             )
  116:         ) {
  117:     print "Cannot connect to database!\n";
  118:     exit;
  119: }
  120: 
  121: my $sth = $dbh->prepare("SHOW TABLE STATUS");
  122: $sth->execute();
  123: my $results = $sth->fetchall_arrayref;
  124: 
  125: foreach my $array (@$results) {
  126:     next if ($array->[14] ne 'temporary');  # [14] is the comment
  127:     my $name = $array->[0];
  128:     # [10] in status report is creation time.
  129:     # [11] in status report is update (last modification?) time.
  130:     my $tabletime = $array->[11];
  131:     # Times are like: 2002-07-25 10:17:08
  132:     my ($year,$month,$day,$hour,$min,$sec)= 
  133:         ($tabletime =~ /(\d+)-(\d+)-(\d+) (\d+):(\d+):(\d+)/);
  134:     my $epoch_seconds = timelocal($sec,$min,$hour,$day,$month-1,$year-1900);
  135:     if ((time - $epoch_seconds) > $killtime) {
  136:         $dbh->do('DROP TABLE '.$name);
  137:     }
  138: }
  139: 
  140: # --------------------------------------------------- Close database connection
  141: $dbh->disconnect;
  142: