Jez Hancock <> wrote:
> Hi Rossz,
>
> On Sat, Feb 07, 2004 at 06:52:27PM -0800, Rossz Vamos-Wentworth wrote:
>> When getting a sql generated list of items that are normally
>> seperated by colons, do I need to concat them with colons or can I
>> use the list raw. For example, would this work?:
>>
>> domainlist local_domains = localhost : \
>> ${lookup mysql{select unique domain from domains}}
Lookups that yeild more than one row are concatenated together with a
newline at the end of each one
(
http://www.exim.org/exim-html-4.30/doc/html/spec_toc.html#TOC99). So if
you return more than one row in this particular case, yes you have to modify
the output to replace the \n with a : (or in the case of my example, a
comma).
This is what I have (not the same context, but the idea is the use of sg):
data = ${lookup mysql{SELECT ... } {${sg{$value}{\\n}{, }}}}
>
> You can just do:
>
> domainlist local_domains = localhost : \
> mysql;select unique domain from domains
>
> which I believe is syntactically equivalent to what you have in your
> example anyway.
No it's not actually. The "mysql;select ..." syntax does not return any
data. It only returns a true/false condition as you can see in your tests
even. This is explained in chapter 10 of the Exim docs
(
http://www.exim.org/exim-html-4.30/doc/html/spec_10.html) - just scroll
down a bit until you see a part that shows a "mysql; ..." example and read
that.
> MYSQL_DOMAIN_LOCALS = SELECT domain FROM domains WHERE
> domain='${quote_mysql:${domain}}'
This works because chances are that you have the domain field as unique in
MySQL. This means that when you query with the domain name you're expecting
to find, you only ever get one row back - even then though it's not even
used - just the true/false value is computed to see if Exim continues or
not.
> To be honest I don't know how efficient this kludge is but it's been
> working fine for several months now.
I didn't think of doing something like this at first either. I was doing it
the way Rossz thought of which is to query for every single domain I have,
and just distinct the results to remove dups. I then thought what would
happen when I host like 10000 domains - a pretty long list of domains is
what happens :)
However, since we can use the $domain variable in most cases where we need
to check the receiving domain, we don't need to return *every* domain, we
just need to see if the domain the email is coming in for is local or not.
I have this query to determine local domains:
domainlist domain_virtual = ${lookup mysql{SELECT host FROM domain WHERE
host = '${quote_mysql:$domain}' LIMIT 1}}
You can call it a bit of paranoia I guess, since my host field is marked as
unique in MySQL, but I still have the LIMIT 1 clause there just in case
(plus I think it might speed up the return of data in MySQL since it won't
continue to search for more possible matches - or is it smart enough to know
a unique field should act like this? :P).
>>>> somelocaldomain.com in "@ : /etc/mail/local-host-names :
>>>> mysql;SELECT domain FROM domains WHERE
>>>> domain='somelocaldomain.com'"? yes (matched "mysql;SELECT domain
>>>> FROM domains WHERE domain='somelocaldomain.com'")
As you can see here, it's just returning "yes" based on your mysql; type
query; it's not returning the actual domain name, but if it works, it works
:)
Eli.
