view release on metacpan or  search on metacpan

lib/Mail/MtPolicyd/Plugin/Greylist/AWL/Sql.pm  view on Meta::CPAN

sub get {
	my ( $self, $sender_domain, $client_ip ) = @_;
	my $sql = sprintf("SELECT * FROM %s WHERE sender_domain=? AND client_ip=?",
       		$self->autowl_table );
	my $row = $self->execute_sql($sql, $sender_domain, $client_ip)->fetchrow_hashref;
  return unless defined $row;

	my $last_seen = $row->{'last_seen'};
	my $expires = $last_seen + ( ONE_DAY * $self->autowl_expire_days );
  my $now = Time::Piece->new->epoch;
	if( $now > $expires ) {
		return;
	}

  return $row->{'count'};
}

sub create {
	my ( $self, $sender_domain, $client_ip ) = @_;
    my $timestamp = 
	my $sql = sprintf("INSERT INTO %s VALUES(NULL, ?, ?, 1, %d)",
       		$self->autowl_table, Time::Piece->new->epoch );
	$self->execute_sql($sql, $sender_domain, $client_ip);
	return;
}

sub incr {
	my ( $self, $sender_domain, $client_ip ) = @_;
	my $sql = sprintf(
        "UPDATE %s SET count=count+1, last_seen=%d WHERE sender_domain=? AND client_ip=?",
        $self->autowl_table,
        Time::Piece->new->epoch );
	$self->execute_sql($sql, $sender_domain, $client_ip);
	return;
}

sub remove {
	my ( $self, $sender_domain, $client_ip ) = @_;
	my $sql = sprintf("DELETE FROM %s WHERE sender_domain=? AND client_ip=?",
       		$self->autowl_table );
	$self->execute_sql($sql, $sender_domain, $client_ip);
	return;
}

sub expire {
	my ( $self ) = @_;
	my $timeout = ONE_DAY * $self->autowl_expire_days;
  my $now = Time::Piece->new->epoch;
	my $sql = sprintf("DELETE FROM %s WHERE ? > last_seen + ?",
    $self->autowl_table );
	$self->execute_sql($sql, $now, $timeout);
	return;
}

sub init {
  my $self = shift;
  $self->check_sql_tables( %{$self->_table_definitions} );
}

has '_table_definitions' => ( is => 'ro', isa => 'HashRef', lazy => 1,
    default => sub { {
        'autowl' => {
            'mysql' => 'CREATE TABLE %TABLE_NAME% (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `sender_domain` VARCHAR(255) NOT NULL,
    `client_ip` VARCHAR(39) NOT NULL,
    `count` INT UNSIGNED NOT NULL,
    `last_seen` INT UNSIGNED NOT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `domain_ip` (`client_ip`, `sender_domain`),
    KEY(`client_ip`),
    KEY(`sender_domain`)
  ) ENGINE=%MYSQL_ENGINE%  DEFAULT CHARSET=latin1',
            'SQLite' => 'CREATE TABLE %TABLE_NAME% (
    `id` INTEGER PRIMARY KEY AUTOINCREMENT,
    `sender_domain` VARCHAR(255) NOT NULL,
    `client_ip` VARCHAR(39) NOT NULL,
    `count` INT UNSIGNED NOT NULL,
    `last_seen` INTEGER NOT NULL
)',
        },
    } },
);

1;

__END__

=pod

=encoding UTF-8

=head1 NAME

Mail::MtPolicyd::Plugin::Greylist::AWL::Sql - backend for SQL greylisting awl storage

=head1 VERSION

version 2.05

=head1 AUTHOR

Markus Benning <ich@markusbenning.de>

=head1 COPYRIGHT AND LICENSE

This software is Copyright (c) 2014 by Markus Benning <ich@markusbenning.de>.

This is free software, licensed under:

  The GNU General Public License, Version 2, June 1991

=cut