This is an old revision of the document!
Referential integrity constraints
Before 0.9.10, there were no referential integrity constraints in the schema. The reason was mainly that most RI constraints refer to a message and messages were split between the mail
and trashed_mail
tables (as opposed to one table only), thus making simple RI constraints impractical. In 0.9.10, those tables are unified so RI constraints are enabled.
For the 0.9.10 upgrade script to succeed on an existing Manitou-Mail installation, these constraints must be satisfied beforehand. In principle that should be the case, but improper manual deletes or malfunctions may have introduced dangling references. The SQL fragments below can be used to check and delete the rows that refer to non-existing messages, adresses, or attachments. Check and delete are separated so that the rows to delete may be examined and moved elsewhere before delete when there is some value in keeping them.
addresses
Check entries in mail_addresses that refer to deleted messages
SELECT ma.* FROM mail_addresses ma LEFT JOIN mail m ON ma.mail_id=m.mail_id WHERE m.mail_id IS NULL;
Delete entries in mail_addresses that refer to deleted messages
DELETE FROM mail_addresses WHERE mail_id IN (SELECT ma.mail_id FROM mail_addresses ma LEFT JOIN mail m ON ma.mail_id=m.mail_id WHERE m.mail_id IS NULL);
Check entries in mail_addresses
that refer to deleted addresses
SELECT ma.* FROM mail_addresses ma LEFT JOIN addresses a ON ma.addr_id=a.addr_id WHERE a.addr_id IS NULL;
Delete entries from mail_addresses
that refer to deleted addresses
DELETE FROM mail_addresses WHERE addr_id IN (SELECT ma.addr_id FROM mail_addresses ma LEFT JOIN addresses a ON ma.addr_id=a.addr_id WHERE a.addr_id IS NULL);
body
Check entries in body
that refer to deleted messages
SELECT b.mail_id FROM body b LEFT JOIN mail m ON b.mail_id=m.mail_id WHERE m.mail_id IS NULL;
Delete entries from body
that refer to deleted messages
DELETE FROM body WHERE mail_id IN (SELECT b.mail_id FROM body b LEFT JOIN mail m ON b.mail_id=m.mail_id WHERE m.mail_id IS NULL);
header
Check entries in header
that refer to deleted messages
SELECT h.mail_id FROM header h LEFT JOIN mail m ON h.mail_id=m.mail_id WHERE m.mail_id IS NULL;
Delete entries from header
that refer to deleted messages
DELETE FROM header WHERE mail_id IN (SELECT h.mail_id FROM header h LEFT JOIN mail m ON h.mail_id=m.mail_id WHERE m.mail_id IS NULL);
notes
Check entries in notes
that refer to deleted messages
SELECT h.mail_id FROM notes h LEFT JOIN mail m ON h.mail_id=m.mail_id WHERE m.mail_id IS NULL;
Delete entries from notes
that refer to deleted messages
DELETE FROM notes WHERE mail_id IN (SELECT h.mail_id FROM notes h LEFT JOIN mail m ON h.mail_id=m.mail_id WHERE m.mail_id IS NULL);
raw_mail
Check entries in raw_mail
that refer to deleted messages
SELECT h.mail_id FROM raw_mail h LEFT JOIN mail m ON h.mail_id=m.mail_id WHERE m.mail_id IS NULL;
Delete entries from raw_mail
that refer to deleted messages
DELETE FROM raw_mail WHERE mail_id IN (SELECT h.mail_id FROM notes h LEFT JOIN mail m ON h.mail_id=m.mail_id WHERE m.mail_id IS NULL);
Note that raw_mail
contain references to large objects, which are not automatically purged on delete. vacuumlo can be used to purge large objects that are no longer referenced.
mail_tags
Check entries in mail_tags
that refer to deleted messages
SELECT h.mail_id FROM mail_tags h LEFT JOIN mail m ON h.mail_id=m.mail_id WHERE m.mail_id IS NULL;
Delete entries from mail_tags
that refer to deleted messages
DELETE FROM mail_tags WHERE mail_id IN (SELECT h.mail_id FROM notes h LEFT JOIN mail m ON h.mail_id=m.mail_id WHERE m.mail_id IS NULL);
Check entries in mail_tags
that refer to deleted tags
SELECT h.mail_id FROM mail_tags h LEFT JOIN tags t ON h.tag=t.tag_id WHERE t.tag_id IS NULL;
Delete entries from mail_tags
that refer to deleted tags
DELETE FROM mail_tags WHERE mail_id IN (SELECT h.mail_id FROM mail_tags h LEFT JOIN tags t ON h.tag=t.tag_id WHERE t.tag_id IS NULL);
Remove references to non-existing mailboxes (does not delete messages)
UPDATE MAIL SET mbox_id=NULL WHERE mail_id IN (SELECT mail_id FROM mail m LEFT JOIN mailboxes mx ON m.mbox_id=mx.mbox_id WHERE mx.mbox_is IS NULL);
tags_words
Delete entries from tags_words
that refer to deleted tags
DELETE FROM tags_words WHERE tag_id IN (SELECT h.tag_id FROM tags_words h LEFT JOIN tags t ON h.tag_id=t.tag_id WHERE t.tag_id IS NULL);
Delete entries from tags_words
that refer to deleted words
DELETE FROM tags_words WHERE word_id IN (SELECT h.word_id FROM tags_words h LEFT JOIN words w ON h.word_id=w.word_id WHERE w.word_id IS NULL);
inverted_word_index
Delete entries from inverted_word_index
that refer to deleted words
DELETE FROM inverted_word_index WHERE word_id IN (SELECT h.word_id FROM inverted_word_index h LEFT JOIN words w ON h.word_id=w.word_id WHERE w.word_id IS NULL);