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. Otherwise a script that just fixes all tables in one go is available at the end of the page.

Generally, mail IDs can refer to entries that live either in mail or in trashed_mail. To simplify the queries, we defined and use a view that combines both tables: 

CREATE VIEW all_mail AS SELECT * FROM mail UNION SELECT * FROM trashed_mail;

addresses

Check entries in mail_addresses that refer to deleted messages 

SELECT ma.* FROM mail_addresses ma LEFT JOIN all_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 all_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 all_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 all_mail m ON b.mail_id=m.mail_id WHERE m.mail_id IS NULL);

Check entries in header that refer to deleted messages 

SELECT h.mail_id FROM header h LEFT JOIN all_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 all_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 all_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 all_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 all_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 all_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 all_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 mail_tags h LEFT JOIN all_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);

mail

Remove references to non-existing mailboxes (does not delete any message) 

UPDATE mail SET mbox_id=NULL WHERE mbox_id IS NOT NULL AND mail_id IN
  (SELECT mail_id FROM mail m LEFT JOIN mailboxes mx ON m.mbox_id=mx.mbox_id WHERE mx.mbox_id IS NULL);
 
UPDATE trashed_mail SET mbox_id=NULL WHERE mbox_id IS NOT NULL AND mail_id IN
  (SELECT mail_id FROM trashed_mail m LEFT JOIN mailboxes mx ON m.mbox_id=mx.mbox_id WHERE mx.mbox_id 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);

attachments

Check entries in attachment_contents that refer to deleted attachments 

SELECT ac.attachment_id FROM attachment_contents ac LEFT JOIN attachments a ON ac.attachment_id=a.attachment_id
   WHERE a.attachment_id IS NULL;

Delete entries from attachment_contents that refer to deleted attachments 

DELETE FROM attachment_contents WHERE attachment_id IN
 (SELECT ac.attachment_id FROM attachment_contents ac LEFT JOIN attachments a ON ac.attachment_id=a.attachment_id
   WHERE a.attachment_id IS NULL);

Check entries in attachments that refer to deleted mail 

SELECT a.attachment_id FROM attachments a LEFT JOIN all_mail m ON a.mail_id=m.mail_id WHERE m.mail_id IS NULL;

Delete entries from attachments and attachment_contents that refer to deleted mail. 

DELETE FROM attachment_contents WHERE attachment_id IN
 (SELECT a.attachment_id FROM attachments a LEFT JOIN all_mail m ON a.mail_id=m.mail_id WHERE m.mail_id IS NULL);
 
DELETE FROM attachments WHERE attachment_id IN
 (SELECT a.attachment_id FROM attachments a LEFT JOIN all_mail m ON a.mail_id=m.mail_id WHERE m.mail_id IS NULL);

The actual contents in large objects don't get removed by a simple delete, see the comment above on raw_mail to purge the unused large objects.

All deletes and updates combined

CREATE OR REPLACE VIEW all_mail AS SELECT * FROM mail UNION SELECT * FROM trashed_mail;
 
DELETE FROM mail_addresses WHERE mail_id IN
(SELECT ma.mail_id FROM mail_addresses ma LEFT JOIN all_mail m ON ma.mail_id=m.mail_id WHERE m.mail_id IS NULL);
 
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);
 
DELETE FROM body WHERE mail_id IN 
 (SELECT b.mail_id FROM body b LEFT JOIN all_mail m ON b.mail_id=m.mail_id WHERE m.mail_id IS NULL);
 
DELETE FROM header WHERE mail_id IN 
 (SELECT h.mail_id FROM header h LEFT JOIN all_mail m ON h.mail_id=m.mail_id WHERE m.mail_id IS NULL);
 
DELETE FROM notes WHERE mail_id IN 
 (SELECT h.mail_id FROM notes h LEFT JOIN all_mail m ON h.mail_id=m.mail_id WHERE m.mail_id IS NULL);
 
DELETE FROM raw_mail WHERE mail_id IN 
 (SELECT h.mail_id FROM notes h LEFT JOIN all_mail m ON h.mail_id=m.mail_id WHERE m.mail_id IS NULL);
 
DELETE FROM mail_tags WHERE mail_id IN 
 (SELECT h.mail_id FROM mail_tags h LEFT JOIN all_mail m ON h.mail_id=m.mail_id WHERE m.mail_id IS NULL);
 
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);
 
UPDATE mail SET mbox_id=NULL WHERE mbox_id IS NOT NULL AND mail_id IN
  (SELECT mail_id FROM mail m LEFT JOIN mailboxes mx ON m.mbox_id=mx.mbox_id WHERE mx.mbox_id IS NULL);
 
UPDATE trashed_mail SET mbox_id=NULL WHERE mbox_id IS NOT NULL AND mail_id IN
  (SELECT mail_id FROM trashed_mail m LEFT JOIN mailboxes mx ON m.mbox_id=mx.mbox_id WHERE mx.mbox_id IS NULL);
 
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 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);
 
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);
 
DELETE FROM attachment_contents WHERE attachment_id IN
 (SELECT ac.attachment_id FROM attachment_contents ac LEFT JOIN attachments a ON ac.attachment_id=a.attachment_id
   WHERE a.attachment_id IS NULL);
 
DELETE FROM attachment_contents WHERE attachment_id IN
 (SELECT a.attachment_id FROM attachments a LEFT JOIN all_mail m ON a.mail_id=m.mail_id WHERE m.mail_id IS NULL);
 
DELETE FROM attachments WHERE attachment_id IN
 (SELECT a.attachment_id FROM attachments a LEFT JOIN all_mail m ON a.mail_id=m.mail_id WHERE m.mail_id IS NULL);