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    |  [EMAIL PROTECTED]      | filtering. Inoculate
  antibodymx.net  |  m: +447896578023           | your mail system.



-- 
## List details at http://lists.exim.org/mailman/listinfo/exim-users 
## Exim details at http://www.exim.org/
## Please use the Wiki with this list - http://wiki.exim.org/

Reply via email to