#!/usr/bin/perl
#
# Copyright (c) 2010-2012, The Trusted Domain Project.  All rights reserved.
#
#
# Script to age out OpenDKIM statistics data

###
### Setup
###

use strict;
use warnings;

use DBI;
use File::Basename;
use Getopt::Long;
use IO::Handle;
use POSIX;

require DBD::mysql;

# general
my $progname      = basename($0);
my $version       = "2.11.0";
my $verbose       = 0;
my $helponly      = 0;
my $showversion   = 0;

my $minmsg;

my $dbi_s;
my $dbi_h;
my $dbi_a;

# DB parameters
my $def_dbhost    = "localhost";
my $def_dbname    = "opendkim";
my $def_dbuser    = "opendkim";
my $def_dbpasswd  = "opendkim";
my $def_dbport    = "3306";
my $dbhost;
my $dbname;
my $dbuser;
my $dbpasswd;
my $dbport;

my $dbscheme      = "mysql";

my $def_maxage    = 180;

my $rows;
my $maxage;

###
### NO user-serviceable parts beyond this point
###

sub usage
{
	print STDERR "$progname: usage: $progname [options]\n";
	print STDERR "\t--dbhost=host      database host [$def_dbhost]\n";
	print STDERR "\t--dbname=name      database name [$def_dbname]\n";
	print STDERR "\t--dbpasswd=passwd  database password [$def_dbpasswd]\n";
	print STDERR "\t--dbport=port      database port [$def_dbport]\n";
	print STDERR "\t--dbuser=user      database user [$def_dbuser]\n";
	print STDERR "\t--expire=days      expiration time, in days [$def_maxage]\n";
	print STDERR "\t--help             print help and exit\n";
	print STDERR "\t--verbose          verbose output\n";
	print STDERR "\t--version          print version and exit\n";
}

# parse command line arguments
my $opt_retval = &Getopt::Long::GetOptions ('dbhost=s' => \$dbhost,
                                            'dbname=s' => \$dbname,
                                            'dbpasswd=s' => \$dbpasswd,
                                            'dbport=s' => \$dbport,
                                            'dbuser=s' => \$dbuser,
                                            'expire=i' => \$maxage,
                                            'help!' => \$helponly,
                                            'verbose!' => \$verbose,
                                            'version!' => \$showversion,
                                           );

if (!$opt_retval || $helponly)
{
	usage();

	if ($helponly)
	{
		exit(0);
	}
	else
	{
		exit(1);
	}
}

if ($showversion)
{
	print STDOUT "$progname v$version\n";
	exit(0);
}

# apply defaults
if (!defined($dbhost))
{
	if (defined($ENV{'OPENDKIM_DBHOST'}))
	{
		$dbhost = $ENV{'OPENDKIM_DBHOST'};
	}
	else
	{
		$dbhost = $def_dbhost;
	}
}

if (!defined($dbname))
{
	if (defined($ENV{'OPENDKIM_DB'}))
	{
		$dbname = $ENV{'OPENDKIM_DB'};
	}
	else
	{
		$dbname = $def_dbname;
	}
}

if (!defined($dbpasswd))
{
	if (defined($ENV{'OPENDKIM_PASSWORD'}))
	{
		$dbpasswd = $ENV{'OPENDKIM_PASSWORD'};
	}
	else
	{
		$dbpasswd = $def_dbpasswd;
	}
}

if (!defined($dbport))
{
	if (defined($ENV{'OPENDKIM_PORT'}))
	{
		$dbport = $ENV{'OPENDKIM_PORT'};
	}
	else
	{
		$dbport = $def_dbport;
	}
}

if (!defined($dbuser))
{
	if (defined($ENV{'OPENDKIM_USER'}))
	{
		$dbuser = $ENV{'OPENDKIM_USER'};
	}
	else
	{
		$dbuser = $def_dbuser;
	}
}

if (!defined($maxage))
{
	if (defined($ENV{'OPENDKIM_MAXAGE'}))
	{
		$maxage = $ENV{'OPENDKIM_MAXAGE'};
	}
	else
	{
		$maxage = $def_maxage;
	}
}

# sanity check
if ($maxage <= 0)
{
	print STDERR "$progname: invalid expiration time\n";
	exit(1);
}

#
# Let's go!
#

if ($verbose)
{
	print STDERR "$progname: started at " . localtime() . "\n";
}

my $dbi_dsn = "DBI:" . $dbscheme . ":database=" . $dbname .
              ";host=" . $dbhost . ";port=" . $dbport;

$dbi_h = DBI->connect($dbi_dsn, $dbuser, $dbpasswd, { PrintError => 0 });
if (!defined($dbi_h))
{
	print STDERR "$progname: unable to connect to database: $DBI::errstr\n";
	exit(1);
}

if ($verbose)
{
	print STDERR "$progname: connected to database\n";
}

#
# Expire messages
#

if ($verbose)
{
	print STDERR "$progname: expiring messages older than $maxage day(s)\n";
}

$dbi_s = $dbi_h->prepare("DELETE FROM messages WHERE msgtime <= DATE_SUB(CURRENT_TIMESTAMP(), INTERVAL ? DAY)");
$rows = $dbi_s->execute($maxage);
if (!$rows)
{
	print STDERR "$progname: DELETE failed: " . $dbi_h->errstr;
	$dbi_s->finish;
	$dbi_h->disconnect;
	exit(1);
}
elsif ($verbose)
{
	if ($rows eq "0E0")
	{
		print STDOUT "$progname: no rows deleted\n";
	}
	else
	{
		print STDOUT "$progname: $rows row(s) deleted\n";
	}
}

$dbi_s->finish;

#
# Expire signatures
#

$dbi_s = $dbi_h->prepare("SELECT MIN(id) FROM messages");
if (!$dbi_s->execute)
{
	print STDERR "$progname: SELECT failed: " . $dbi_h->errstr;
	$dbi_s->finish;
	$dbi_h->disconnect;
	exit(1);
}

while ($dbi_a = $dbi_s->fetchrow_arrayref())
{
	$minmsg = $dbi_a->[0];
}

$dbi_s->finish;

if ($verbose)
{
	print STDERR "$progname: expiring signatures on expired messages (id < $minmsg)\n";
}

$dbi_s = $dbi_h->prepare("DELETE FROM signatures WHERE message < ?");
$rows = $dbi_s->execute($minmsg);
if (!$rows)
{
	print STDERR "$progname: DELETE failed: " . $dbi_h->errstr;
	$dbi_s->finish;
	$dbi_h->disconnect;
	exit(1);
}
elsif ($verbose)
{
	if ($rows eq "0E0")
	{
		print STDOUT "$progname: no rows deleted\n";
	}
	else
	{
		print STDOUT "$progname: $rows row(s) deleted\n";
	}
}

$dbi_s->finish;

#
# Expire aggregate data
#

if ($verbose)
{
	print STDERR "$progname: expiring aggregate data older than $maxage days\n";
}

$dbi_s = $dbi_h->prepare("DELETE FROM aggregate WHERE date <= DATE_SUB(CURRENT_TIMESTAMP(), INTERVAL ? DAY)");
$rows = $dbi_s->execute($maxage);
if (!$rows)
{
	print STDERR "$progname: DELETE failed: " . $dbi_h->errstr;
	$dbi_s->finish;
	$dbi_h->disconnect;
	exit(1);
}
elsif ($verbose)
{
	if ($rows eq "0E0")
	{
		print STDOUT "$progname: no rows deleted\n";
	}
	else
	{
		print STDOUT "$progname: $rows row(s) deleted\n";
	}
}

$dbi_s->finish;

#
# All done!
#

if ($verbose)
{
	print STDERR "$progname: terminating at " . localtime() . "\n";
}

$dbi_h->disconnect;

exit(0);