Log summary to DB

[Elliot Foster]

I wrote a plugin last weekend that logs mail summary to a DB (sqlite at the 
moment, I plan on making it more flexible.)  If anyone is interested, let me 
know how I could modify it to make it more useful to you (logging message-id, 
etc.)  Suggestions/questions are welcome.

I plan on writing a web UI so that users can authenticate and see their mail 
history and eventually whitelist ip/sender/whatever, but haven't done so yet.

Elliot
#!/usr/bin/perl -Tw

use DBI;
use Data::Dumper;

my $dbtable = 'smtplog';

sub register {
        my ($self, $qp, %args) = @_;

        $self->log(LOGINFO,'Initializing logging::logdb plugin');

        # get DB config
        #$self->qp->config('logdb');
        #dbname
        #dbtable
        #dbuser
        #dbpass

        # check health of DB with test connection
        &schema_check($self);
}

#sub hook_connect {
#       # connect to DB here?
#}

sub schema_check {
        my ($self) = @_;
    my $dbh = &connect_to_db($self);

    eval {
        $dbh->do(qq{
            CREATE TABLE smtplog (
                id     INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
                result INTEGER,
                type   INTEGER,
                plugin VARCHAR(128),
                user   VARCHAR(16),
                ip     VARCHAR(16),
                sender VARCHAR(128),
                recip  VARCHAR(128),
                subj   VARCHAR(128),
                size   INTEGER,
                date   INTEGER
            )});
    };

    if ($@ && $@ !~ /table \w+ already exists/) {
        print "SQL Error: [EMAIL PROTECTED]";
        die "exiting.\n";
    }

    $dbh->commit;
    $dbh->disconnect;
}

sub connect_to_db {
        my ($self) = @_;

        my $dbname = '/var/tmp/qpsmtpd/dbfile';

        # FIXME: pull DB information from config
        my $dbh = DBI->connect("dbi:SQLite:dbname=$dbname", "", "",
                { RaiseError => 1, PrintError => 0, AutoCommit => 0 });

        return $dbh;
}

sub populate_rows {
        my ($result, $self, $transaction, $prev_hook, @args) = @_;
        warn "triggered for $result\n";

        my $rows = $self->qp->transaction->notes('logdb');

        my ($rpath, $from, $to, $subj, $ip); # mesg id, sender, recipient 
user(s), recipient domain(s)
        $rpath = $transaction->sender;
        $rpath ||= '';
        if ($rpath =~ m/^<?(.*?)>?$/) {
                $rpath = $1;
        }

        # if we have mail headers, get information out of them
        if (defined($transaction->header)) {
                $from = $transaction->header->get('From') || '';
                $from =~ s/.*(<[EMAIL PROTECTED]>)/$1/;
                chomp($from);

                $to = $transaction->header->get('To') || '';
                $to =~ s/.*(<[EMAIL PROTECTED]>)/$1/;
                chomp($to);

                $subj = $transaction->header->get('Subject') || '';
                $subj = substr $subj, 0, 127;
                chomp($subj);
        }

        $from ||= '';
        $to ||= '';
        $subj ||= '';
        $prev_hook ||= '';

        $ip = $self->qp->connection->remote_ip;

        # add a row for every recipient
        my @recipients = $transaction->recipients;
        push @recipients, ''
                if ($result eq 'DENY');

        foreach my $recip (@recipients) {
                if ($recip =~ m/^<?(.*?)>?$/) {
                        $recip = $1;
                }
                warn $result, "\t", $recip, "\n";
                $rows->{$recip} = [
                        $result,
                        $prev_hook,
                        $rpath,
                        $ip,
                        $recip,
                        $subj
                ];
        }

        $self->qp->transaction->notes('logdb', $rows);

        return DECLINED;
}

# populate failure
sub hook_deny {
        my ($self) = @_;
        $self->log(LOGINFO,'hook_deny 
firing--------------------------------------------------------');
        return &populate_rows("DENY", @_);
}

# populate successfully commited emails
sub hook_queue {
        my ($self) = @_;
        $self->log(LOGINFO,'Queue hook 
firing--------------------------------------------------------');
        return &populate_rows("ACCEPT", @_);
}

# commit items to DB
sub hook_reset_transaction {
        my ($self) = @_;
        return DECLINED unless $self->qp->transaction->notes('logdb');

        $self->log(LOGINFO,'Reset 
transaction--------------------------------------------------------');

        my $rows = $self->qp->transaction->notes('logdb');
        warn Dumper($rows);

        my $dbh = &connect_to_db($self);

        my $sth = $dbh->prepare(
                "INSERT INTO $dbtable(result, plugin, sender, ip, recip, subj, 
date)
                VALUES (?, ?, ?, ?, ?, ?, ?)"
        );

        my $now = time();

        # FIXME: loop through actual users rather than the recipient addresses
        foreach my $recip (keys %$rows) {
                my @data = @{ $rows->{$recip} };
                $sth->execute(
                        qq{$data[0]},
                        qq{$data[1]},
                        qq{$data[2]},
                        qq{$data[3]},
                        qq{$data[4]},
                        qq{$data[5]},
                        $now,
                );
        }

        $dbh->commit;
        undef($sth);
        $dbh->disconnect;
        $self->qp->connection->notes('logdb', '');

        return DECLINED;
}