On Thu, Oct 16, 2003 at 01:06:59PM -0400, Matthew T. O'Connor wrote:
> 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.
Even without triggers, much of the logic could be moved to the database.
Currently, I believe it does something like (mixing sql and application
pseudocode):
SELECT message_idnr, physmessage_id WHERE status = 3;
foreach message_idnr {
DELETE FROM
messages
WHERE
message_idnr = $message_idnr;
SELECT
message_idnr
FROM
messages
WHERE
physmessage_id = $physmessage_id;
if (no results) {
DELETE FROM
physmessages
WHERE
id = $physmessage_id;
DELETE FROM
messageblks
WHERE
physmessage_id = $physmessage_id;
}
}
This could be simpilified (in MySQL syntax; don't know similar
PostgreSQL (or SQL92?) syntax):
DELETE FROM
messages
WHERE
status = 3;
DELETE FROM
physmessage
USING
physmessage p
LEFT JOIN
messages m ON m.physmessage_id = p.id
WHERE
m.message_idnr IS NULL;
With a foreign key constraint using ON DELETE CASCADE on messageblks,
that's it.
> 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.
Right. If the purging happens within an isolated transaction, it'll
always be consistent. I'm looking forward to transactions in 2.0.
xn
