Ron White wrote:
> As my ACL grows It has crossed my mind that for every acl stanza making
> use of a MySQL that fires off, there is the roundtrip cost of the query.
> Whilst some of the lookups can be optimised by ACL layout, I'm not going
> to be able to avoid some similar very similar queries when I check my
> database for certain flags being set on a per user basis.
>
> I spotted that there were a couple of sets of variables available to use
> in ACL's. The per session '$acl_c0-9' and (near enough 'per rcpt')
> $acl_m0-9
>
> Probably a simple question, but I can't find a relate-able example in
> the 'query-style lookups' chunk of my book. Would it be possible for me
> to have a single query that returned multiple results and assign each
> result to $acl_m0-> so I can look at them elsewhere in the ACL without
> the need to make a costly similar query?
>
> For example, I understand how this checks for a recipient:
> #************************************************
> #CHECK USER IS IN OUR LIST OF VALID RECIPIENTS
> #************************************************
> drop !recipients = ${lookup mysql{MYSQL_RCPT}}
> domains = +hosted_domains
> message = No such user
> log_message = user $local_part@$domain not defined in database
>
> .. but could I change the query:
> MYSQL_RCPT
> SELECT email FROM mailusers WHERE email='${quote_mysql:$local_part}@
> ${quote_mysql:$domain}'
>
> To something like this:
>
> SELECT email,usertype,inbound,outbound FROM mailusers WHERE
> email='${quote_mysql:$local_part}@${quote_mysql:$domain}';
>
> and actually access the other items? ($0-> ???). Would this break
> anything ? any pitfalls ? even possible ?
>
> Again, my thanks to anyone who has time to cast an eye over it.
>
>
Eminently possible, and it can pay back handsomely.
To plan for it, you might wish to 'under-normalize' your DB w/r record
structure, so that one SELECT clause can pull one record or row that has all the
info you need for the whole session AND post-session router/transport sets as
you transit the acl_smtp_rcpt phase.
CAVEATS:
Keeping in mind that Exim can have 'very many' child processes in the air at
once, each independently making SQL calls:
- MANY other approaches are both more efficient (CDB) and/or more robust (CDB,
hostlist, flat file) than using an(y) SQL-capable RDBMS engine AT ALL.
OTOH - SQL's flexibility is very hard work to match, AND if you already have one
in-place for OTHER work, the 'cost' is reduced and admin effort shared out.
YMMV,
Bill
