#!/usr/bin/perl -w
###########################################################################
#
#      clear_greylist.pl
#
# Copyright (c) 2003 Roman Festchook <roma@polesye.net>
# http://noc.polesye.net/greylist/
#
# quick and dirty perl script to clear expired records from Greylist database
#
# This program is free software; you can redistribute it and/or modify
# it under the terms of the GNU General Public License Version 2 from
# June 1991 as published by the Free Software Foundation.
#
# This program is distributed in the hope that it will be useful, but
# WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
# GNU General Public License for more details.
#
# You should have received a copy of the GNU General Public License along
# with this program; if not, write to the Free Software Foundation, Inc.,
# 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
#
###########################################################################
# ChangeLog:
#
# 26/06/03 first release
#
###########################################################################


use strict;
use DBI;

my $date = localtime;
my $LOGFILE = "/var/log/isp";
my $i = 0;
my $host = "";
my $mail_from = "";
my $rcpt_to = "";
my $block = "";
my $pass = "";

# put your mysql connect options here
my $mydb = "";
my $myhost = "";
my $myuser = "";
my $mypasswd = "";


open(FILE, ">>$LOGFILE");

my $isp= DBI->connect("DBI:mysql:$mydb:$myhost", "$myuser", "$mypasswd") || print FILE "$date Can't connect\n";

my $sth = $isp->prepare("select relay_ip, mail_from, rcpt_to, blocked_count, passed_count from greylist.relaytofrom where (passed_count=0 and (UNIX_TIMESTAMP() - UNIX_TIMESTAMP(block_expires)) > 28850) or (UNIX_TIMESTAMP() - UNIX_TIMESTAMP(record_expires) > 0) ")
  || print FILE "Can't prepare retrieving dates";
$sth->execute || print FILE "Can't execute retrieving dates";

my $lines = $sth->fetchall_arrayref || &error("Can't fetch all period data");
$sth->finish;


foreach $i (0 .. $#{$lines}) {
$host = $lines->[$i][0];
$mail_from = $lines->[$i][1];
$rcpt_to = $lines->[$i][2];
$block = $lines->[$i][3];
$pass = $lines->[$i][4];


$sth = $isp->prepare("insert into greylist.spam_records values ('$host', '$mail_from', '$rcpt_to', $block, $pass, NULL)")
  || print FILE "Can't prepare inserting triplet\n";
$sth->execute || print FILE "Can't execute inserting triplet [".$sth->errstr()."]\n";
$sth = $isp->prepare("delete from greylist.relaytofrom where rcpt_to='$rcpt_to' and mail_from='$mail_from' and relay_ip='$host'")
  || print FILE "Can't prepare retrieving dates";
$sth->execute || print FILE "Can't execute retrieving dates";
}

$isp->disconnect;
close(FILE);
