#!/usr/bin/perl -w ; # this is -*- Perl -*-

# Wed May 31 01:24:32 CDT 2000
# dbCheck

# the idea is to go through all the tables, read lock them,
# and perform isamchk on it.
# prior to isamchk, we do a backup of each table, putting
# it into a tar file.

# Fri Jul  7 22:25:12 CDT 2000
# Working well. Now we need to delete older backups (> 1 month)

$BACKUP_DIR = "/usr/local/dbbackup";
$DB_ROOT = "/usr/local/var";

use DBI;
use Time::Local; # for timelocal function

my $dbh = DBI->connect("DBI:mysql:database=", "root", "qzev3112")
  or die("Can't connect to DB!");

# make the tar filename to put the tables into
# begin with the date

my $now = time();

$tarFile = MakeTarFileName($now);

my $sth = $dbh->prepare("SHOW DATABASES");
$sth->execute()  || die("Bad query");

my $dbName;

while(($dbName) = $sth->fetchrow_array()) {
  next if($dbName =~ /^lost\+found$/); # not a database
  &DoDatabase($dbName);
}

$dbh->disconnect();

# compress the tarFile
system ("gzip", "-v9", $tarFile);

# delete old files in the directory
# one month in seconds = 30 (days) * 24 (hours) * 60 (minutes) * 60 (seconds)
CleanUpDirectory(($now - (30 * 24 * 60 * 60)));

exit 0;

###########################################################################

sub DoDatabase {
  # goes through a database, calling DoTable for each Table

  my $dbName = shift;
  my $sth;
  my $tableName;
  
  $dbh->do("USE $dbName") || die("Can't USE $dbName");
  $sth = $dbh->prepare("SHOW TABLES");
  $sth->execute();
  
  while(($tableName) = $sth->fetchrow_array()) {
    DoTable($dbName, $tableName);
  }
}

###########################################################################

sub DoTable {
  my $dbName = shift;
  my $tableName = shift;
  
  #lock it
  $dbh->do("LOCK TABLES $tableName READ") || die("Can't lock $tableName!");
  $dbh->do("FLUSH TABLES") || die("Can't flush tables!");
  
  my $tarCommand;
  
  if( -e $tarFile) {
    $tarCommand = "rf";
  } else {
    $tarCommand = "cf";
  }
  
  # each table in the file system has three files associtaed with it,
  # all in the database directory.
  #  tablename.ISD
  #  tablename.ISM
  #  tablename.frm
  
  print "** $dbName.$tableName **\n";

  my $fileName = $DB_ROOT."/".$dbName."/".$tableName;
  my @fileNames = ("$fileName.ISD", "$fileName.ISM", "$fileName.frm");
  
  system ("/bin/tar", $tarCommand, $tarFile, @fileNames);
  
  # now check the table for errors
  
  system ("/usr/local/bin/isamchk", "$fileName.ISM");
  
  $dbh->do("FLUSH TABLES") || die ("Can't flush tables!");
  $dbh->do("UNLOCK TABLES") || die("Can't unlock table $tableName!");
}

#####################################################################

sub MakeTarFileName {
  my $time = shift;

  my(undef, undef, undef, $mday, $mon, $year, undef, undef, undef)
    = localtime($time);

  $mon += 1; # put it into 1-12 range
  $year += 1900; #normal years

  # now we have the date which we want to use in the name of the tar file
  # so we make the tar file in the $BACKUP_DIR directory

  return(sprintf("$BACKUP_DIR/bu%04d%02d%02d.tar", $year, $mon, $mday));
}

########################################################################

sub CleanUpDirectory {
  my $before = shift;
  
  # $before is a time variable (seconds), of which we remove
  # any files which are made before it.
  
  my $aFile;
  
  opendir BUDIR, $BACKUP_DIR;
  while($aFile = readdir BUDIR) {
    if(FileNameToSeconds($aFile) < $before) {
      if(unlink "$BACKUP_DIR/$aFile") {
        printf("Deleted old backupfile $aFile\n");
      } else {
        printf("Error deleting old backupfile $aFile\n");
      }
    } else {
#      printf("won't delete: $aFile ($tmp)\n");
    }
  }
  closedir BUDIR;
}

#######################################################################

sub FileNameToSeconds {
  my $fileName = shift;
  
  # filenames begin with "bu"
  if($fileName =~ /^bu[0-9]{4}[0-9]{2}[0-9]{2}/) {
    my($year, $month, $day) = ($fileName =~ /bu([0-9]{4})([0-9]{2})([0-9]{2})/);
    return(timelocal(0, 0, 0, $day, ($month-1), $year));
  } else {
    return (timelocal(59, 59, 23, 31, 11, 2037)); # ie, don't delete it.
  }
}

#########################################################################