Manitou-Mail Wiki

User Tools

Site Tools

Trace:
schema_0_9_10_upgrade_notes

Differences

This shows you the differences between two versions of the page.

Link to this comparison view

Both sides previous revisionPrevious revision
Next revision		Previous revision
schema_0_9_10_upgrade_notes [2008/11/03 22:56] – danielschema_0_9_10_upgrade_notes [2008/11/04 12:27] (current) – daniel
Line 3: Line 3:
 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. 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.+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: 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:
Line 136: Line 136:
 </code> </code>
  
 +=== attachments ===
 +Check entries in ''attachment_contents'' that refer to deleted attachments
 +<code sql>
 +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;
 +</code>
 +
 +Delete entries from ''attachment_contents'' that refer to deleted attachments
 +<code sql>
 +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);
 +</code>
 +
 +Check entries in ''attachments'' that refer to deleted mail
 +<code sql>
 +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;
 +</code>
 +
 +Delete entries from ''attachments'' and ''attachment_contents'' that refer to deleted mail.
 +<code sql>
 +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);
 +</code>
 +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 === === All deletes and updates combined ===
 <code sql> <code sql>
 +CREATE OR REPLACE VIEW all_mail AS SELECT * FROM mail UNION SELECT * FROM trashed_mail;
 +
 DELETE FROM mail_addresses WHERE mail_id IN 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); (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);
Line 177: Line 207:
 DELETE FROM inverted_word_index WHERE word_id IN  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);  (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);
 +
 </code> </code>
  
schema_0_9_10_upgrade_notes.1225752978.txt.gz · Last modified: 2008/11/03 22:56 by daniel