On Sun, 2007-06-10 at 23:37 +0200, Michael Monnerie wrote:
> On Freitag, 1. Juni 2007 Blake Mitchell wrote:
> > It always seemed to me that the dbmail_aliases table should have a
> > user_idnr column along with the deliver_to column. This way if
> > deliver_to was null, go to the user's inbox. This would allow
> > constraints to prevent dangling aliases on user delete, and aliases
> > to user_idnrs that don't exist.
> >
> > It would require a user for every alias, but there's no harm in
> > creating administrative users that don't actually receive mail.
>
> Why would it require a user? You could simply define that either the
> deliver_to or the user_idnr must be filled, but not both. But I'm not
I'd have some dummy user who owns all of the external forwards.
> sure about the performance of such a solution, it could well be that
> this is slower, as it could require separate lookups at some points.
You'd just always select both columns and then see which one has a
value.
> But it sure would make the DB model more stable, and I'd prefer that.
We're going to change the schema between major stable releases, and
sometimes we're going to make subtle semantic changes to the meaning of
certain values in certain columns, either in response to bugs or to add
new features within the existing schema.
> I love to have the DB rock solid, after all, it's a bit important. ;-)
Huh? Is this all stemming from people being unhappy about dangling
entries in the aliases table? Just because deliver_to is a varchar
doesn't mean you can't do anything with it. For example, this works
fine:
select userid, alias from dbmail_aliases, dbmail_users
where deliver_to = user_idnr;
Likewise this works, too:
select userid from dbmail_users
where user_idnr not in (select deliver_to from dbmail_aliases);
As does this for finding unattached aliases:
select alias, deliver_to from dbmail_aliases
where deliver_to not in (select user_idnr from dbmail_users)
and convert(deliver_to, unsigned) > 0;
It's not elegant, but it basically works (except when deliver_to begins
with a number but is actually an email address, e.g. [EMAIL PROTECTED] if
someone has an idea for how to hack around this, I'd love to hear it!)
> And is we're developing a complete web interface for it, having
> dependencies guaranteed by the DB is much better than logic in external
> programs. Developers make errors, after all.
No argument there :-P
Aaron
_______________________________________________
DBmail mailing list
[email protected]
https://mailman.fastxs.nl/mailman/listinfo/dbmail
