On Thu, 2003-10-16 at 12:50, Christian G. Warden wrote: > On Thu, Oct 16, 2003 at 10:15:20AM -0400, Matthew T. O'Connor wrote: > > Foreign keys are a good example, when we delete the last entry in the > > message table, the database should automatically delete all entries in > > the phs_message table and in the msg_blocks table for us. > > I think this would require triggers, which MySQL doesn't support, > because there can't be a one-to-many relationship between a foreign key > and field it references. Messages reference physmessages, not the > other way around.
true... so without triggers, we may have to do some manual work to make sure we are the only message left, if so then delete phsmessage, which will automatically delete the msg_blocks entries. BTW, this could probably be abstracted away with the different db drivers, the pg driver would assume that this is being taken care of by a trigger, and the mysql driver would have to do the work. Another important reason to have the database maintain consistency itself as much as possible is that you can't that dbmail is the only client app that touches the database. I know there is at least one webmail app that bypasses IMAP / POP3 and goes directly to the database for performance reasons, now if that client doesn't correctly do all the same things that dbmail does the database may become inconsistent.
