Annotation of loncom/metadata_database/cleanup_database.pl, revision 1.8
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.8 ! raeburn 5: # $Id: cleanup_database.pl,v 1.7 2006/11/20 17:07:57 albertel 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:
1.8 ! raeburn 60: The following invocation will drop all tables without updates in the past
! 61: two days (the default).
1.1 matthew 62:
1.8 ! raeburn 63: cleanup_database.pl --killtime 172800
1.1 matthew 64:
65: If you desire the immediate cleanup of temporary tables, use the following:
66:
67: cleanup_database.pl --killtime 0
68:
69: Depending on permissions, you may have to run this script as root.
70:
71: =cut
72:
73: #################################################
74:
75: use strict;
76: use lib '/home/httpd/lib/perl/';
77: use LONCAPA::Configuration;
1.8 ! raeburn 78: use Apache::lonnet;
1.1 matthew 79: use Getopt::Long;
80: use Time::Local;
81: use DBI;
1.8 ! raeburn 82: use Digest::MD5();
1.1 matthew 83:
84: my $help = 0;
1.8 ! raeburn 85: my $killtime;
1.1 matthew 86: GetOptions( "killtime=s" => \$killtime,
87: "help" => \$help );
88: if ($help) {
89: print <<ENDHELP;
90: cleanup_database.pl Cleans up the LON-CAPA MySQL database by removing
91: temporary tables.
92: Command line arguements
93: --killtime <number> The number of seconds a temporary table is allowed
1.8 ! raeburn 94: to live. On a library server, specifying this argument
! 95: also overrides any course-specific or domain-specific
! 96: lifetimes which apply to the various md5_hash_* tables
! 97: which contain student data for a course, for which the
! 98: current server is the homeserver.
1.1 matthew 99: --help Print out this help message.
100:
101: Examples:
102:
103: cleanup_database.pl --killtime 0
104: cleanup_database.pl --killtime 86400
105:
106: Note: You will probably need to execute this script as root.
107:
108: ENDHELP
109: exit;
110: }
111:
112: # --------------- Read loncapa_apache.conf and loncapa.conf and get variables
1.2 harris41 113: my %perlvar = %{&LONCAPA::Configuration::read_conf('loncapa.conf')};
1.1 matthew 114: delete $perlvar{'lonReceipt'}; # remove since sensitive and not needed
115:
116: my $dbh;
117: # ------------------------------------- Make sure that database can be accessed
118: unless ($dbh = DBI->connect("DBI:mysql:loncapa","www",
119: $perlvar{'lonSqlAccess'},
120: {RaiseError=>0,PrintError=>0}
121: )
122: ) {
123: print "Cannot connect to database!\n";
124: exit;
125: }
126:
127: my $sth = $dbh->prepare("SHOW TABLE STATUS");
128: $sth->execute();
1.7 albertel 129: my $results = $sth->fetchall_hashref('Name');
1.1 matthew 130:
1.8 ! raeburn 131: my ($nokilltime,$gotconf,%coursetypes,%md5hashes,%domcrsdefs,%gotcourseenv,%crssetting);
! 132: if ($killtime eq '') {
! 133: $killtime = 86400*2;
! 134: if ($perlvar{'lonRole'} eq 'library') {
! 135: $nokilltime = 1;
! 136: }
! 137: }
! 138:
1.5 albertel 139: foreach my $name (keys(%$results)) {
1.8 ! raeburn 140: next if ($results->{$name}{Comment} ne 'temporary');
1.5 albertel 141: my $tabletime = $results->{$name}{Update_time};
1.1 matthew 142: # Times are like: 2002-07-25 10:17:08
143: my ($year,$month,$day,$hour,$min,$sec)=
144: ($tabletime =~ /(\d+)-(\d+)-(\d+) (\d+):(\d+):(\d+)/);
145: my $epoch_seconds = timelocal($sec,$min,$hour,$day,$month-1,$year-1900);
1.8 ! raeburn 146: my $currkilltime = $killtime;
! 147: if ($nokilltime) {
! 148: if ($name =~ /^md5_(\w+)_(?:\w+)$/) {
! 149: my $hashid = $1;
! 150: unless ($gotconf) {
! 151: &get_config(\%coursetypes,\%md5hashes,\%domcrsdefs);
! 152: $gotconf = 1;
! 153: }
! 154: if (exists($md5hashes{$hashid})) {
! 155: my ($cdom,$cnum) = split(/_/,$md5hashes{$hashid});
! 156: unless ($gotcourseenv{$md5hashes{$hashid}}) {
! 157: my %envhash = &Apache::lonnet::dump('environment',$cdom,$cnum);
! 158: $gotcourseenv{$md5hashes{$hashid}} = 1;
! 159: if ($coursetypes{$md5hashes{$hashid}} eq 'unofficial') {
! 160: if ($envhash{'internal.textbook'}) {
! 161: $coursetypes{$md5hashes{$hashid}} = 'textbook';
! 162: }
! 163: }
! 164: $crssetting{$md5hashes{$hashid}} = $envhash{'internal.mysqltables'};
! 165: }
! 166: if (($crssetting{$md5hashes{$hashid}} ne '') && ($crssetting{$md5hashes{$hashid}} !~ /^\D/)) {
! 167: $currkilltime = $crssetting{$md5hashes{$hashid}};
! 168: } elsif (ref($domcrsdefs{$cdom}) eq 'HASH') {
! 169: if (($domcrsdefs{$cdom}{$coursetypes{$md5hashes{$hashid}}} ne '') &&
! 170: ($domcrsdefs{$cdom}{$coursetypes{$md5hashes{$hashid}}} !~ /^\D/)) {
! 171: $currkilltime = $domcrsdefs{$cdom}{$coursetypes{$md5hashes{$hashid}}};
! 172: }
! 173: }
! 174: }
! 175: }
! 176: }
! 177: if ((time - $epoch_seconds) > $currkilltime) {
1.1 matthew 178: $dbh->do('DROP TABLE '.$name);
179: }
180: }
1.6 albertel 181: $sth->finish();
1.1 matthew 182:
183: # --------------------------------------------------- Close database connection
184: $dbh->disconnect;
185:
1.8 ! raeburn 186: sub get_config {
! 187: my ($coursetypes,$md5hashref,$domsettings) = @_;
! 188: my @domains = sort(&Apache::lonnet::current_machine_domains());
! 189: my @ids=&Apache::lonnet::current_machine_ids();
! 190: foreach my $dom (@domains) {
! 191: my %domconfig = &Apache::lonnet::get_dom('configuration',['coursedefaults'],$dom);
! 192: if (ref($domconfig{'coursedefaults'}) eq 'HASH') {
! 193: if (ref($domconfig{'coursedefaults'}{'mysqltables'}) eq 'HASH') {
! 194: $domsettings->{$dom} = $domconfig{'coursedefaults'}{'mysqltables'};
! 195: }
! 196: }
! 197: my %currhash = &Apache::lonnet::courseiddump($dom,'.',1,'.','.','.',1,\@ids,'.');
! 198: if (keys(%currhash)) {
! 199: foreach my $key (keys(%currhash)) {
! 200: if ($key ne '') {
! 201: if (ref($currhash{$key}) eq 'HASH') {
! 202: my $digest = &Digest::MD5::md5_hex($key);
! 203: $md5hashref->{$digest} = $key;
! 204: my $crstype = $currhash{$key}{'type'};
! 205: my $longcrstype = 'unofficial';
! 206: if ($crstype eq 'Community') {
! 207: $longcrstype = 'community';
! 208: } elsif ($crstype eq 'Placement') {
! 209: $longcrstype = 'placement';
! 210: } elsif ($currhash{$key}{'inst_code'}) {
! 211: $longcrstype = 'official';
! 212: }
! 213: $coursetypes->{$key} = $longcrstype;
! 214: }
! 215: }
! 216: }
! 217: }
! 218: }
! 219: return;
! 220: }
! 221:
FreeBSD-CVSweb <freebsd-cvsweb@FreeBSD.org>
![[BACK]](/icons/back.gif){kind=icon}
Return to cleanup_database.pl CVS log ![[TXT]](/icons/text.gif){kind=icon}
![[DIR]](/icons/dir.gif){kind=icon}
Up to [LON-CAPA] / loncom / metadata_database