#!/usr/bin/perl -w

use strict;
use DBI;

my $dbname = 'exim';
my $dbhost = 'localhost';
my $dbuser = 'eximadmin';
my $dbpass = 'some password';

my $logger = '/usr/bin/logger -p mail.info -t greylist';

my $dbh = DBI->connect(
  "dbi:Pg:dbname=$dbname;host=$dbhost", $dbuser, $dbpass
);
if (!$dbh) {
  system("$logger 'Error on initial connect to database: $DBI::errstr'");
}

my $sth = $dbh->prepare("
  SELECT * FROM relaytofrom WHERE now() > record_expires
");
my $numrows = $sth->execute();
my $archived = 0;

if ($numrows == 0) {
  system("$logger 'no records to archive'");
} else {
  # grab all column names (except for the 'id' column)
  my %row;
  $sth->bind_columns( \( @row{ @{$sth->{NAME_lc} } } ));
  my @cols = sort grep {$_ ne 'id'} keys %row;

  $dbh->{AutoCommit} = 0;
  $dbh->{RaiseError} = 1;
  while($sth->fetch) {
    eval {
      my @values;
      foreach my $col (@cols) {
        if (defined $row{$col}) {
	  push @values, $dbh->quote($row{$col});
	} else {
	  push @values, 'NULL';
	}
      }
      my $sth = $dbh->prepare('INSERT INTO relaytofrom_archive (' 
        . join(',', @cols) . ') VALUES (' . join(',', @values) . ')');
      $sth->execute;
      $sth = $dbh->prepare("DELETE FROM relaytofrom WHERE id = $row{id}");
      $sth->execute;
      $dbh->commit;
    };
    if ($@) {
      eval { $dbh->rollback };
      system("$logger 'archive transaction aborted because $@'");
    } else {
      $archived++;
    }
  }
  system("$logger '$archived records archived'");
}
$sth->finish;
$dbh->disconnect;