[Vmail-discuss] Separate domain_alias table?

[Chris Lightfoot]

On Thu, Jul 10, 2003 at 08:44:39PM +0100, Paul Makepeace wrote:
> On Thu, Jul 10, 2003 at 08:31:31PM +0100, Chris Lightfoot wrote:

> >     insert into domain (id, name) values (1, 'example.com');
> >     insert into domain (id, name) values (1, 'example.org');
> >     insert into domain (id, name) values (1, 'example.net');
> 
> (That's three entries with the same primary key!)

Gah. I really wasn't awake when I wrote that....

>      insert into domain (id, name) values (1, 'example.com');
>      insert into domain (id, name) values (1, 'example.org');
>      insert into domain (id, name) values (2, 'example.net');
>      insert into destination (domainid, localpart, type, destination)
>          values (2, 'fred', forwarder, 'fred@somewhere.else');
>      insert into destination (domainid, localpart, type, destination)
>          values (2, 'jane', forwarder, 'jane@somewhere.else');
>      
> So fred@example.net -> fred@somewhere.else
>    jane@example.net -> jane@somewhere.else
> 
> Now, if I make example.net aliased to example.{com,org}, say with
>      update domain set domainid=1 where name='example.net';
> 
> ..it'll end up with a load of dangling destination references for the
> old domainid=2 (e.g. jane). Not a problem with a ANSI SQL RDBMS but a
> with MySQL which doesn't have ON UPDATE CASCADE you'd have to
> additionally fix every table that REFERENCES domain(id).
> 
> (Unless I've missed something neat instead.)

No, I don't think you have. MySQL doesn't even have
transactions, so you can't really safely do both
operations, either. But in practice this would be OK (and
would work fine in PostgreSQL or whatever). (Actually, you
can use lock tables to avoid the race condition, but it
doesn't help with the other problems.)


BTW, have you seen SQLite-- a very neat bit of code.

> I'm leaning ever toward an alias field in the domain table...

I.e., if it's non-NULL it means that the named domain is
an alias for the alias domain? Doesn't that make the
queries a bit painful?

-- 
Blow your mind. Smoke gunpowder.