# -*-perl-*-

# $Id: SweepDB.plm,v 1.2 2012/02/20 22:20:55 koczan Exp $

# Front-end to add, remove, or list postgres atoms

# $Log: SweepDB.plm,v $
# Revision 1.2  2012/02/20 22:20:55  koczan
# whoops, wrong variable
#
# Revision 1.1  2012/02/20 22:11:23  koczan
# sweepdb command, keeps databases nice and not-bloated (TM)
#
# Revision 1.2  2012/02/20 22:06:31  koczan
# SweepDB will keep your database nice and compact
#
# Revision 1.1  2012/02/02 18:00:59  koczan
# sweepdb command, cleans out super old entries to improve performance
#
#

package Sushi::Command::SweepDB;

use strict;
use Getopt::Long;
use Data::Dumper;

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

sub main {
  my ($class) = shift;
  # my $cmd = shift @ARGV;

  my $args;
  my $opts = {};

  Getopt::Long::Configure ("bundling");
  my $res;
  if (!($res = GetOptions (
			   'dryrun',       \$opts->{dryrun},
			   'really_sweep', \$opts->{really_sweep},
			   'help|h',       \$opts->{help},
			  ))) {
    $main::log->error (&help);
    return 0;
  }

  if (defined $opts->{help}) {
    printhelp ();
    return 1;
  }

  if (defined $opts->{dryrun}) {
    return &sweep(1);
  } else {
    if (!$opts->{really_sweep}) {
      print <<__Eof__


If you really want to remove crufty data form the sushi database, run
this command again with the --really_sweep option. It really sucks to
delete data unintentionally, so this extra hoop is present to help
prevent that. Much like the purge, please don't mention the
--really_sweep option in any documentation; let people find out this way.
Thanks.

If you want to find out more about what this command does, try running
the sushi command 'sweepdb --help' to find out.



__Eof__
;
      return 0;
    }

    return &sweep(0);
  }
}

sub sweep {
  my $dryrun = shift;

  my $querytext;
  my $res;
  my $sth;
  my $total = 0;

  # We need to create temporary tables as caches. If we don't, the NOT IN
  # clauses take ridiculously long to complete on the actual tables.
  # If this fails at any point, we roll back.

  print STDERR "Deleting Expunged records from offline pool...\n";
  $querytext = "delete from sushi.offline
where disposition = 'Expunged'";
  ($res, $sth) = &_runquery($querytext);
  return 0 unless $res;
  print STDERR "Deleted $res Expunged offline records.\n";
  $total += $res;

  print STDERR "Creating offline cache...\n";
  $querytext = "create temporary table offline_cache as
select distinct atom_id, result_timestamp from sushi.offline
where disposition = 'Available'";
  ($res, $sth) = &_runquery($querytext);
  return 0 unless $res;
  print STDERR "Successfully created offline cache.\n";

  print STDERR "Deleting expired results records...\n";
  $querytext = "delete from sushi.results
where (id, timestamp) not in
  (select distinct atom_id, result_timestamp from offline_cache)
  and age(now(), timestamp) > '2 years'::interval
  and on_disk = 'Purged'";
  ($res, $sth) = &_runquery($querytext);
  return 0 unless $res;
  print STDERR "Deleted $res expired result records.\n";
  $total += $res;

  print STDERR "Creating results cache...\n";
  $querytext = "create temporary table results_cache as
select id, timestamp, afs_volume from sushi.results";
  ($res, $sth) = &_runquery($querytext);
  return 0 unless $res;
  print STDERR "Successfully created results cache.\n";

  print STDERR "Finding reference point for atom weight deletion...\n";
  $querytext = "select min(id) as id from results_cache
where age(now(), timestamp) <= '1 month'::interval";
  my $sth;
  ($res, $sth) = &_runquery($querytext);
  return 0 unless $res;
  my $id = $sth->fetchrow_hashref->{id};
  $sth->finish;
  print STDERR "Reference point is id $id\n";

  print STDERR "Deleting old atom weights...\n";
  $querytext = "delete from sushi.atom_weights
where id < $id";
  ($res, $sth) = &_runquery($querytext);
  return 0 unless $res;
  print STDERR "Deleted $res old atom weights.\n";
  $total += $res;

  print STDERR "Deleting afs volume entries...\n";
  $querytext = "delete from sushi.afsvol_freespace
where deleted = 't'
  and available = 'f'
  and volume_name not in
    (select distinct afs_volume from results_cache)";
  ($res, $sth) = &_runquery($querytext);
  return 0 unless $res;
  print STDERR "Deleted $res afs volume records.\n";
  $total += $res;

  print STDERR "Deleting atoms without any information...\n";
  $querytext = "delete from sushi.atom
where id not in
  (select distinct id from results_cache)";
  ($res, $sth) = &_runquery($querytext);
  return 0 unless $res;
  print STDERR "Deleted $res atom records.\n";
  $total += $res;

  # clean up caches
  print STDERR "Removing offline cache...\n";
  $querytext = "drop table offline_cache";
  ($res, $sth) = &_runquery($querytext);
  return 0 unless $res;
  print STDERR "Removed offline cache.\n";

  print STDERR "Removing results cache...\n";
  $querytext = "drop table results_cache";
  ($res, $sth) = &_runquery($querytext);
  return 0 unless $res;
  print STDERR "Removed results cache.\n";

  ($dryrun) ? $::sudbh->rollback : $::sudbh->commit;

  print STDERR "\nDeleted $total rows from the database.";
  print STDERR " (DRYRUN)" if $dryrun;
  print STDERR "\n";
  return $total;
}

###############################################################################
# Quick and dirty function to run a query
# Returns number of rows and the statement handle
sub _runquery {
  my $querytext = shift; # SQL query

  $main::log->log (4, "Command::SweepDB: $querytext\n");
  my $sth = $::sudbh->prepare($querytext);
  if (!$sth->execute) {
    $main::log->error("Error with query:\n$querytext\n");
    $::sudbh->rollback;
    return 0;
  }

  my $rows = $sth->rows;
  $rows = "0E0" if $rows == 0; # The "true" zero

  return ($rows, $sth);
}

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

sub aliases {
  return ();
}

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

sub printhelp {
  print help();
  return 1;
}

sub shortHelp {
  return "Remove dead weight from the sushi database";
}

sub help {
  my $help = <<__Eof__

sweepdb\t <options>

options: [--dryrun] [--really_sweep]

The sweepdb command is used to remove old atoms, atom weights, results,
and offline results from the sushi database.

This is what happens to the database:
- "Expunged" offline entries get removed.
- Results that are expired, i.e. Purged, has no corresponding
  offline entry, and older than 2 years old, are removed.
- Weights for atoms older than 3 months are removed
- Atoms without result entries after this are removed.
- AFS volume entries without a corresponding result entry.

--dryrun shows how many entries would be removed.

--really_sweep actually removes the database entries.

__Eof__
;
}

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

1;
