On Sat, Jan 31, 2004 at 09:32:29PM -0000, Aaron Stone wrote:
> The new delivery chain requires a temporary account with the fixed user id
> number 0. The next release candidate should probably include an appropriate
> insert/update to add this user (note that it needs to be an insert and then an
> update, because the insert alone with '0' triggers the auto_increment.
>
> INSERT INTO users (user_idnr, userid, passwd) VALUES (0, '*', '*');
> UPDATE userid SET user_idnr = 0 WHERE username = '*';
We should consider using something else besides user_idnr = 0, for this
special account. Perhaps, username = 'delivery' (or whatever) and
client_id = 0, or even a special boolean column just for this user.
Using user_idnr=0 has some unfortanate side-effects with mysql. If
you're joining between aliases and users on deliver_to = user_indr,
mysql considers 0 equal to any string, e.g.
SELECT
IF (u.user_idnr IS NULL, a.deliver_to, u.userid) AS recipient
FROM
aliases a
LEFT JOIN
users u
ON
u.user_idnr = a.deliver_to
WHERE
a.alias = "xn@postica.com"
Also, when altering the table, you can run into key violations as mysql
helpfully tries to replace the 0.
xn
> The new delivery chain requires a temporary account with the fixed user id
> number 0. The next release candidate should probably include an appropriate
> insert/update to add this user (note that it needs to be an insert and then an
> update, because the insert alone with '0' triggers the auto_increment.
>
> INSERT INTO users (user_idnr, userid, passwd) VALUES (0, '*', '*');
> UPDATE userid SET user_idnr = 0 WHERE username = '*';
We should consider using something else besides user_idnr = 0, for this
special account. Perhaps, username = 'delivery' (or whatever) and
client_id = 0, or even a special boolean column just for this user.
Using user_idnr=0 has some unfortanate side-effects with mysql. If
you're joining between aliases and users on deliver_to = user_indr,
mysql considers 0 equal to any string, e.g.
SELECT
IF (u.user_idnr IS NULL, a.deliver_to, u.userid) AS recipient
FROM
aliases a
LEFT JOIN
users u
ON
u.user_idnr = a.deliver_to
WHERE
a.alias = "xn@postica.com"
Also, when altering the table, you can run into key violations as mysql
helpfully tries to replace the 0.
xn