From: David Anderson (david.andersoncalixo.net)
Date: Mon May 03 2004 - 18:18:08 CDT

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]

Hi,

I have recently installed postfix as MTA and MDA for some virtual
domains. I wanted the configuration stored in a MySQL database. Simple
enough, dict_mysql is there for just that purpose. Except that I wanted
to store account names (local part of the email address) and domain
names in separate tables, and link from one to the other.

This sort of setup has a problem: using the configuration options for
dict_mysql, the queries that one can construct are fairly limited. For
$virtual_mailbox_maps, I managed to get to something like this:

select_field=CONCAT(domains.name,'/',accounts.login,'/')
table=accounts INNER JOIN domains ON domains.id=accounts.domain_id
where_field=CONCAT(accounts.login,'',domains.name)
additionnal_conditions=accounts.active=1 AND domains.active=1

Which produces the following query:

SELECT CONCAT(domains.name,'/',accounts.login,'/')
  FROM accounts
  INNER JOIN domains ON domains.id=accounts.domain_id
  WHERE CONCAT(accounts.login,'',domains.name) = 'logindomain.net'
   AND accounts.active=1
   AND domains.active=1

Only this query is extremely inefficient due to the CONCAT in the WHERE
field. FYI it takes about 0.02s to execute via phpmyadmin with 2 domains
and 2 email accounts (fairly long on my SQL server). I suspect this time
would increase exponentially with more accounts and domains.

This patch adds a configuration directive to dict_mysql, $custom_query.
If you supply this configuration option, all of $select_field,
$where_field, $table and $additionnal_conditions are ignored.

$custom_query must contain a full SQL query. Use the token $(lookup)
where you want to insert the search key in the request.

The database structure I described above can then be accessed by the
following configuration:

custom_query = SELECT CONCAT(domains.name,'/',accounts.login,'/') \
  FROM accounts \
  INNER JOIN domains ON domains.id=accounts.domain_id \
  WHERE accounts.login=SUBSTRING_INDEX('$(lookup)', '', 1) \
  AND domains.name=SUBSTRING_INDEX('$(lookup)', '', -1) \
  AND accounts.active=1 \
  AND domains.active=1

This query produces the exact same results as the previous one, but
executes in an average 0.003s, almost 7 times faster. Also the query
time will not increase as drastically with more accounts and domains (or
so I suspect - I have yet to test scaleability).
This is why I feel this feature is justified: drastic speed increases
derived from being able to write complex queries instead of hacking
together inefficient ones.

Patch against version:
2.0.19:
http://natulte.net/pub/postfix-patch/mysql_custom_query.2.0.19.diff.gz
2.0.20:
http://natulte.net/pub/postfix-patch/mysql_custom_query.2.0.20.diff.gz
2.1.0 :
http://natulte.net/pub/postfix-patch/mysql_custom_query.2.1.0.diff.gz

Apply with:

cd postfix-version;
gunzip -c mysql_custom_query.version.diff.gz | patch -p1

The patch affects dict_mysql.c, vstring.h and vstring.c .
vstring_replace implements a simple "s/$(lookup)/.../g" replacement
engine, used by mysql_lookup in dict_mysql.c . I didn't write it using
all the vstring utility functions; instead, I export the original
string, do replacement using traditionnal string functions, then
reimport it into a vstring.
I tried doing it via vstring functions, but I couldn't find my way
around the various "dangerous" macros, so I preferred using things I
more or less know how to manipulate.

Please let me know what you think of this feature. Is it worth my while
implementing a similar patch for the other SQL database engines? Would
anyone here find this "custom" query mode useful for their setup?

Thanks in advance for your comments and suggestions on this patch.
Regards,
David Anderson

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]