On Wed, 2010-04-28 at 13:44 -0400, W B Hacker wrote:
> 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.
> >
> >
>
> Almost forget...
>
Thanks Bill.
> If using that 'drop' you illustrated above, either insure that 'postmaster@',
> abuse@', et al are IN THE DB as well as on the system..
Once I iron out the wrinkles I'm most likely going to 'hard' code that
in the runtime config. It's not in production yet :-)
I'm just not clear on how (or where) the returned fields can be
recovered from when I run a query that returns multiple fields. I'm sure
I read something on it, but I can't find it again.
Anyway, I'll attack it again tomorrow and carry out some experiments
(CUE EVIL LAUGH.....)
