Return to cleanup_database.pl CVS log

Up to [LON-CAPA] / loncom / metadata_database

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

1.1     ! matthew     1: #!/usr/bin/perl
        !             2: # The LearningOnline Network
        !             3: # searchcat.pl "Search Catalog" batch script
        !             4: #
        !             5: # $Id: searchcat.pl,v 1.19 2002/07/01 18:23:00 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: