Sorry, I didn't remember, that I did create a foreign key
constraint on a new column in dbmail_aliases connecting it
back to user_idnr.
By adding that column, I was able to retain complete functionality.
Now I admit that my internal requirement is that you can't have
an dbmail_alias entry if you don't have a dbmail_users entry.
I made a placeholder user and tie all my standalone aliases to
it. I suppose I could have used user_idnr #1 (the internal user),
but that doesn't seem like a good idea.
As for the messages, I think that is also removed (this is v2.0.10).
CREATE TABLE dbmail_messages (
...
mailbox_idnr INT8 REFERENCES dbmail_mailboxes(mailbox_idnr)
ON DELETE CASCADE,
physmessage_id INT8 REFERENCES dbmail_physmessage(id)
ON DELETE CASCADE,
...
Since the dbmail_messages keys to the dbmail_mailboxes, and
to dbmail_physmessages (same with dbmail_messageblks), I
believe that it will cascade all the way down.
When I delete a user, I never see any orphaned blocks when I
run dbmail-util.
--
David A. Niblett | email: [EMAIL PROTECTED]
Network Administrator | Phone: (352) 334-3400
Gainesville Regional Utilities | Web: http://www.gru.net/
-----Original Message-----
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On
Behalf Of Paul J Stevens
Sent: Monday, July 10, 2006 2:48 PM
To: DBMail mailinglist
Subject: Re: [Dbmail] Delete accounts
Niblett, David A wrote:
> I just did a quick search and it would appear that MySQL supports "ON
> DELETE CASACDE", so this should apply to both MySQL and PostgreSQL, if
> my google source is correct.
>
> If you look in the create_tables.postgresql script in the dbmail tree,
> you will notice that all the user_idnr foreign key constraints have
> "ON DELETE CASCADE". I noticed that the corresponding MySQL script
> does not have this, but the MySQL InnoDB does have this feature. So
> maybe it's only available in InnoDB.
>
> What it means is if the foreign key reference is removed,
> the delete process will cascade through the database. (at least
> that's my understanding.)
>
> So because I have this on all the user_idnr's and the message_idnr's
> when I do a "delete from dbmail_users where user_idnr = 592" the
> database takes care of deleting the aliases, mailboxes, messages,
> physmessages and finally the actual user.
Aliases can not be maintained through cascades. There's no restraint on
them. And I don't think dbmail-util cleans them up either. As there are
more corner cases that are /not/ covered by dbmail-util at present.
Also, since physmessages can belong to more than one message, there can
be no cascades to physmessages by deleting messages either. But *that*
one *is* covered by dbmail-util.
So when you delete a user from dbmail_users you delete all mailboxes and
associated messages. Physmessages and messageblks that become orphans
are removable using dbmail-util. It's just the aliases you have to
cleanout yourself.
You /can/ of course declare a belongs-to relation between an alias and a
user if you want to, but then you loose the ability to use forwards and
commands in the aliases table.
--
________________________________________________________________
Paul Stevens paul at nfg.nl
NET FACILITIES GROUP GPG/PGP: 1024D/11F8CD31
The Netherlands________________________________http://www.nfg.nl
_______________________________________________
Dbmail mailing list
[email protected] https://mailman.fastxs.nl/mailman/listinfo/dbmail
