Return to cleanup_database.pl CVS log

Up to [LON-CAPA] / loncom / metadata_database

Annotation of loncom/metadata_database/cleanup_database.pl, revision 1.3

1.1       matthew     1: #!/usr/bin/perl
                      2: # The LearningOnline Network
1.3     ! harris41    3: # cleanup_database.pl Remove stale temporary search results.
1.1       matthew     4: #
1.3     ! harris41    5: # $Id: cleanup_database.pl,v 1.2 2002/09/09 14:01:04 harris41 Exp $
1.1       matthew     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
1.2       harris41  106: my %perlvar = %{&LONCAPA::Configuration::read_conf('loncapa.conf')};
1.1       matthew   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_arrayref;
                    123: 
                    124: foreach my $array (@$results) {
                    125:     next if ($array->[14] ne 'temporary');  # [14] is the comment
                    126:     my $name = $array->[0];
                    127:     # [10] in status report is creation time.
                    128:     # [11] in status report is update (last modification?) time.
                    129:     my $tabletime = $array->[11];
                    130:     # Times are like: 2002-07-25 10:17:08
                    131:     my ($year,$month,$day,$hour,$min,$sec)= 
                    132:         ($tabletime =~ /(\d+)-(\d+)-(\d+) (\d+):(\d+):(\d+)/);
                    133:     my $epoch_seconds = timelocal($sec,$min,$hour,$day,$month-1,$year-1900);
                    134:     if ((time - $epoch_seconds) > $killtime) {
                    135:         $dbh->do('DROP TABLE '.$name);
                    136:     }
                    137: }
                    138: 
                    139: # --------------------------------------------------- Close database connection
                    140: $dbh->disconnect;
                    141: