On Wed, Aug 20, 2014 at 02:16:05PM +0200, Martijn van Duren wrote:
>
> > query_alias select COALESCE(destination, REPLACE(maddr, '@',
> > '_')), ? as maddr from mail_forwarding where source=maddr;
>
> And of course I was a bit too fast with this one (column aliases can't
> be used in other columns), so if something should work it would be:
>
> query_alias SET @maddr = ?; SELECT COALESCE(destination,
> REPLACE(@maddr, '@', '_')) from mail_forwarding where source=@maddr;
>
> I've tested this one as a proof of concept, yet not in an
> table_mysql.conf setup.
>
> I am curious if this works, so keep me posted. :)
>
This one seems to work, I created a view to get rid of the SET instructions
that does not seem
to work.
I will do more tests soon.
Thanks & Cheers
Giovanni
#
# Sample PostgreSQL configuration file
#
# This is an example configuration file for using OpenSMTPD with the PostgreSQL
# backend for lookups. OpenSMTPD does not have hardcoded tables and you may
#
#
host localhost
username user
password pass
database db
# Alias lookup query
#
# rows >= 0
# fields == 1 (user varchar)
#
query_alias select destination from mail_valias where source=?
# Domain lookup query
#
# rows == 1
# fields == 1 (domain varchar)
#
query_domain select domain from mail_domain where domain=?;
# User lookup query
#
# rows == 1
# fields == 3 (uid int, gid int, directory varchar)
#
query_userinfo select uid,gid,maildir from mail_user where
REPLACE(login, '@', '_')=? and server_id=6;
# Credentials lookup query
#
# rows == 1
# fields == 2 (username varchar, password varchar)
#
query_credentials select login, password from mail_user where login=?;
# Netaddr lookup query
#
# rows == 1
# fields == 1 (netaddr varchar)
#
#query_netaddr select value from netaddr where value=$1;
MariaDB [dbispconfig]> show create view mail_valias\G
*************************** 1. row ***************************
View: mail_valias
Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL
SECURITY DEFINER VIEW `mail_valias` AS select `mail_forwarding`.`source` AS
`source`,replace(replace(`mail_forwarding`.`destination`,'@','_'),'\r\n',', ')
AS `destination` from `mail_forwarding` union select `mail_user`.`email` AS
`email`,replace(`mail_user`.`email`,'@','_') AS `replace(email, '@', '_')` from
`mail_user`
character_set_client: utf8
collation_connection: utf8_general_ci
1 row in set (0.00 sec)
MariaDB [dbispconfig]> \q
