Re: [exim] log to a database?

Martin A. Brooks wrote:
> Can exim be persuaded to log directly to a SQL database?


I eventually solved this by doing the following:

1) I created a table with the schema I needed and wrote a postgres
perlpl stored function to insert data into the table.

CREATE FUNCTION log_from_exim(character varying, character varying, 
character varying, integer) RETURNS integer
    AS $_$
        my $eximid=shift;
        my $mailfrom=shift;
        my $mailto=shift;
        my $size=shift;
        my $query="";

        $mailfrom=~s/\'/\'\'/g;
        $mailto=~s/\'/\'\'/g;

        my @recipients=split/,/,$mailto;
        my ($froma,$fromd)=split/\@/,$mailfrom;

        foreach my $recip (@recipients){
                $recip=~s/\s//g;
                my ($radd,$rdom)=split/\@/,$recip;
                $query="INSERT INTO eximlog 
(eximid,from_domain,to_domain,mail_from,mail_to,size) values ";

$query.="('$eximid','$fromd','$rdom','$mailfrom','$recip',$size)";
                my $rv = spi_exec_query($query);
        }
        return 0;
$_$
    LANGUAGE plperl;

2) I added a macro with the correct SQL syntax to call the stored function:

SELECT log_from_exim
('$message_exim_id',lower('${quote_pgsql:$sender_address}'),lower('${quote_pgsql:$recipients}'),$message_size)


3) I added simple ACL to call the macro after a message has been accepted

set acl_m1 = ${lookup pgsql{ABCONFIG_LOGMAIL}}



I know a couple of scenarios under which this will break, but it does so
silently and without affecting the message in question. Hope someone
else finds this useful.

Regards



--

 Martin A. Brooks |  http://www.antibodymx.net/ | Anti-spam & anti-virus
    Consultant    |  martin@???      | filtering. Inoculate
  antibodymx.net  |  m: +447896578023           | your mail system.