#!/usr/bin/env perl

## This file last updated: $Date: 2007/06/11 16:59:33 $
##  Run periodically to clean userDb and sessionDb tables in hgcentral

use warnings;
use strict;

## Configure the server, database, mysql_user, password and NOTIFY address

use DBI;
$db_server = 'localhost';
$db_database = 'hgcentral';
$db_mysql_user = 'readwrite';
$db_password = 'readwrite-users-password';

##  An email address to use when hgcentral tables are detected to be
##	too large
$NOTIFY='youremail@bigU.edu';

$DB_TYPE="mysql";

printf("START hgcentdb optimize clean start %s",`date`);



$dbh = DBI->connect("DBI:$DB_TYPE:dbname=$db_database",$db_mysql_user,$db_password) || die "\nCannot access DB server!\n"; 

#
# Delete low usage 
#

$rc=$dbh->do("delete from  userDb where to_days(firstUse) < to_days(now()) - 7  and useCount=1;");
sleep 90;
$rc=$dbh->do("delete from  userDb where to_days(firstUse) < to_days(now()) - 7  and useCount=2;");
sleep 90;
$rc=$dbh->do("delete from  userDb where to_days(firstUse) < to_days(now()) - 7  and useCount=3;");
sleep 90;
$rc=$dbh->do("delete from  userDb where to_days(firstUse) < to_days(now()) - 7  and useCount=4;");
sleep 90;
$rc=$dbh->do("delete from  userDb where to_days(firstUse) < to_days(now()) - 7  and useCount=5;");
sleep 90;
$rc=$dbh->do("delete from  userDb where to_days(firstUse) < to_days(now()) - 7  and useCount=6;");
sleep 90;

#
# Delete the zero time use rows
# which are often bot related
#

$rc=$dbh->do("delete from  userDb where to_days(firstUse) < to_days(now()) - 2  and useCount=0;");

$rc=$dbh->do("delete  from sessionDb where to_days(lastUse) < to_days(now()) - 14 ;");

$sth = $dbh->prepare(q{show table status;});
$rc = $sth->execute() || die("DB Error\n");

printf("RUNNING hgcentdb delete is done %s",`date`);

while( $R = $sth->fetchrow_hashref('NAME_lc') ) {
    if (( $R->{data_free} > 0 ) && ($R->{type} eq "MyISAM" )) {
        #print "$R->{name} $R->{data_free}\n";
#
# Go into fail mode if tables are huge
#
        if ($R->{data_length} > 3000000000) {
	   printf("BIG HGCENTRAL TABLE $R->{name} $R->{data_free} $R->{data_length}\n");
	   $foo=printf("BIG HGCENTRAL TABLE $R->{name} $R->{data_free} $R->{data_length}\n");
	   system("echo $foo|mail -s 'WARNING hgcentral optimize halted due to size' $NOTIFY");
           exit 1;
        }
    }
}