[exim] A couple autoreply+database questions

Top Page
Delete this message
Reply to this message
Author: Casey Allen Shobe
Date:  
To: exim-users
Subject: [exim] A couple autoreply+database questions
I have the following in the exim.conf on a primary mx:

As a router:

uservacation:
        driver                  = accept
        domains                 = +local_domains
        condition               = ${if or { {match {$h_precedence:} {(?i)junk|
bulk|list}} {eq {$sender_address} {}} } {no} {yes}}       : \
                                  ${lookup pgsql{select "subject" 
from "vacations" where "email_address" = '${quote_pgsql:
$local_part}@${quote_pgsql:$domain}' and now () between "start_time" 
and "end_time"} {yes}{no}}
        senders                 = ! ^.*-request@.*      : \
                                  ! ^bounce-.*@.*       : \
                                  ! ^.*-bounce@.*       : \
                                  ! ^owner-.*@.*        : \
                                  ! ^postmaster@.*      : \
                                  ! ^webmaster@.*       : \
                                  ! ^listmaster@.*      : \
                                  ! ^mailer-daemon@.*   : \
                                  ! ^root@.*
        no_expn
        transport               = vacation_autoresponse
        unseen
        no_verify



As a transport:

vacation_autoresponse:
        driver                  = autoreply
        once                    
= "/var/spool/mail/${domain}/${local_part}/vacation.db"
        once_repeat             = 2d
        from                    = ${lookup 
pgsql{select '"' || "real_name" || '" <${quote_pgsql:
$local_part}@${quote_pgsql:$domain}>' from "email_accounts" 
where "email_address" = '${quote_pgsql:$local_part}@${quote_pgsql:$domain}'}}
        to                      = $sender_address
        subject                 = ${lookup pgsql{select replace 
("subject", '%S', '${quote_pgsql:$h_subject:}') from "vacations" 
where "email_address" = '${quote_pgsql:$local_part}@${quote_pgsql:$domain}' 
and now () between "start_time" and "end_time"}}
        text                    = ${lookup pgsql{select "message" 
from "vacations" where "email_address" = '${quote_pgsql:
$local_part}@${quote_pgsql:$domain}' and now () between "start_time" 
and "end_time"}}



This whole ordeal ends up as 4 SQL queries hitting the PostgreSQL server. I'd
rather do one query in the router and pass the necessary values into the
transport somehow, or if that's not possible, then consolidate the queries in
the transport at least. I'm not sure how to go about doing that.

I'd also like to move the data stored in the berkeley db file for the "once"
data into a PostgreSQL table, but I don't think that's possible in current
versions at least.

Lastly, are there any other checks I should add to the router to prevent
sending replies to places that they shouldn't be sent to? I hate all the
vacation spam but users demand this functionality.

Cheers,
--
Casey Allen Shobe | cshobe@??? | 206-381-2800
SeattleServer.com, Inc. | http://www.seattleserver.com