File:
[LON-CAPA] /
loncom /
metadata_database /
cleanup_database.pl
Revision
1.7:
download - view:
text,
annotated -
select for diffs
Mon Nov 20 17:07:57 2006 UTC (17 years, 10 months ago) by
albertel
Branches:
MAIN
CVS tags:
version_2_9_X,
version_2_9_99_0,
version_2_9_1,
version_2_9_0,
version_2_8_X,
version_2_8_99_1,
version_2_8_99_0,
version_2_8_2,
version_2_8_1,
version_2_8_0,
version_2_7_X,
version_2_7_99_1,
version_2_7_99_0,
version_2_7_1,
version_2_7_0,
version_2_6_X,
version_2_6_99_1,
version_2_6_99_0,
version_2_6_3,
version_2_6_2,
version_2_6_1,
version_2_6_0,
version_2_5_X,
version_2_5_99_1,
version_2_5_99_0,
version_2_5_2,
version_2_5_1,
version_2_5_0,
version_2_4_X,
version_2_4_99_0,
version_2_4_2,
version_2_4_1,
version_2_4_0,
version_2_3_X,
version_2_3_99_0,
version_2_3_2,
version_2_3_1,
version_2_3_0,
version_2_2_99_1,
version_2_2_99_0,
version_2_11_1,
version_2_11_0_RC3,
version_2_11_0_RC2,
version_2_11_0_RC1,
version_2_11_0,
version_2_10_X,
version_2_10_1,
version_2_10_0_RC2,
version_2_10_0_RC1,
version_2_10_0,
loncapaMITrelate_1,
language_hyphenation_merge,
language_hyphenation,
bz6209-base,
bz6209,
bz5969,
bz2851,
PRINT_INCOMPLETE_base,
PRINT_INCOMPLETE,
HEAD,
GCI_3,
GCI_2,
GCI_1,
BZ5971-printing-apage,
BZ5434-fox,
BZ4492-merge,
BZ4492-feature_horizontal_radioresponse
- 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:
FreeBSD-CVSweb <freebsd-cvsweb@FreeBSD.org>