[sork] Setting up Vacation with custom SQL driver

[Jan Schneider]

Zitat von Sebastian Feltel <feltel at rahn-schulen.de>:

> Hello,
> I´ve installed Vacation on top of our Horde webmail system (latest
> available versions on an Debian Lenny server). The mail users are
> virtual ones with an MySQL backend. I´ve chosen the custom SQL driver
> with the following settings:
>
>
> /* CONFIG START. DO NOT CHANGE ANYTHING IN OR AFTER THIS LINE. */
> // $Horde: vacation/config/conf.xml,v 1.15.2.13 2009/08/18 12:54:49 jan
> Exp $
> $conf['vacation']['path'] = '/usr/bin/vacation';
> $conf['vacation']['default_subject'] = _("On vacation message");
> $conf['vacation']['default_message'] = _("I'm on vacation and will not
> be reading my mail for a while.\nYour mail will be dealt with when I
> return.");
> $conf['vacation']['subject'] = true;
> $conf['vacation']['from'] = false;
> $conf['server']['params']['default']['query_set'] = 'INSERT INTO
> system.vacation (email, subject, body, active) VALUES (\'\U, \S, \M,
> \'1\');';
> $conf['server']['params']['default']['query_unset'] = 'DELETE FROM
> system.vacation WHERE vacation.email = \U AND vacation.active = \'1\';';
> $conf['server']['params']['default']['query_get'] = 'SELECT CASE active
> when \'1\' then \'Y\' when \'2\' then \'N\' END AS vacation FROM
> system.vacation WHERE email LIKE \U;';
> $conf['server']['params']['default']['query_password'] = 'SELECT clear
> FROM system.postfix_users WHERE username LIKE \u; ';
> $conf['server']['params']['default']['driverconfig'] = 'horde';
> $conf['server']['params']['default']['hordeauth'] = 'full';
> $conf['server']['params']['encryption'] = 'plain';
> $conf['server']['params']['show_encryption'] = false;
> $conf['server']['driver'] = 'customsql';
> $conf['aliases']['show'] = false;
> $conf['aliases']['driver'] = 'none';
> $conf['user']['refused'] = array('root', 'bin', 'daemon', 'adm', 'lp',
> 'shutdown', 'halt', 'uucp', 'ftp', 'anonymous', 'nobody', 'httpd',
> 'operator', 'guest', 'diginext', 'bind', 'cyrus', 'courier', 'games',
> 'kmem', 'mailnull', 'man', 'mysql', 'news', 'postfix', 'sshd', 'tty',
> 'www');
> $conf['menu']['apps'] = array('horde');
> /* CONFIG END. DO NOT CHANGE ANYTHING IN OR BEFORE THIS LINE. */
>
>
> I can enable/disable the vacation msg (the SQL queries were executed)
> but I´m not able to use the \F, \D and other variables as they are
> empty. \u translates to the username but \U which should be the email
> address is also only my username. Another problem is that I cannot
> construct other variables in the SQL queries as the variables are
> expanded like this:

\U is *not* the email address, it's the full user name. If your user  
names don't contain a domain part, \u and \U are the same, and \D is  
empty.

> \U expands to 'MyUsername' (with apostrophes)
>
> so i cannot use
>
> INSERT into TABLE (username, email, foo, bar) VALUES (\U, '\U at domain',
> fooval, barvar);
>
> as this translates to an false SQL statement
>
> INSERT into TABLE (username, email, foo, bar) VALUES ('MyUsername',
> ''MyUsername'@domain', 'fooval', 'barval');
>
> Any help or hint is highly appreciated. :-)

Use CONCAT():
INSERT into TABLE (username, email, foo, bar) VALUES (\U, CONCAT(\U,  
'@domain'), fooval, barvar);

Jan.

-- 
Do you need professional PHP or Horde consulting?
http://horde.org/consulting/