Some months ago Paul Kelly sent me some patches for Exim in support of
MYSQL lookups. They have now managed to get my attention. The basic
concept is another lookup type called "mysql", and I've been checking
out the code against MYSQL release 3.22.25. However, there are a number
of questions about the packaging to be decided, so I thought I'd see
what you all thought. Please react to the following:
1. Query Syntax
The supplied code uses queries that are rather like NIS+ queries in
their syntax, for example
[id='ph10'],some_table:some_field
to extract the contents of some_field from a record in some_table that
is identified by id='ph10'. (This query maps into "select some_field
from some_table where id='ph10'", for example.)
It occurs to me that maybe an alternative formulation would be in the
form of a URL, similar to the one that is used for LDAP. Does anyone
know if anyone else has defined such a URL format for MYSQL? Is this a
useful way to proceed? The "obvious" analog of the LDAP URLs for the
above query would be something like
mysql:///id='ph10'?some_field?some_table?
However, in the LDAP case the URL-parsing code is provided as part of
the LDAP library. It would have to be written for MYSQL.
2. Specification of host, database, user, and password
The supplied code just had these hardcoded, which of course must be
changed. As well as host, a port number should be settable.
For the host, a global list of servers to try, like the newly
implemented (but not yet released) ldap_default_servers seems like the
right way to go.
The database, I feel, really ought to be included as part of the query,
since you might want to query different ones, though maybe setting a
default one globally could be useful in keeping the actual queries
shorter and less repetitive. Perhaps syntax like
database_name/table_name
instead of just table_name could be used?
User and password are most likely fixed for the whole session, and so
some global configuration options (only) seem reasonsble.
3. Caching
The current code does no caching of connections. It opens a connection
to the SQL server each time. I feel it probably ought to cache
connections in the same way as the LDAP lookup does.
--
Philip Hazel University of Cambridge Computing Service,
ph10@??? Cambridge, England. Phone: +44 1223 334714.