schema_0_9_10_upgrade_notes
Differences
This shows you the differences between two versions of the page.
Next revision | Previous revisionNext revisionBoth sides next revision | ||
schema_0_9_10_upgrade_notes [2008/11/02 23:46] – created daniel | schema_0_9_10_upgrade_notes [2008/11/03 22:56] – daniel | ||
---|---|---|---|
Line 4: | Line 4: | ||
For the 0.9.10 upgrade script to succeed on an existing Manitou-Mail installation, | For the 0.9.10 upgrade script to succeed on an existing Manitou-Mail installation, | ||
+ | |||
+ | Generally, mail IDs can refer to entries that live either in '' | ||
+ | <code sql> | ||
+ | CREATE VIEW all_mail AS SELECT * FROM mail UNION SELECT * FROM trashed_mail; | ||
+ | </ | ||
=== addresses === | === addresses === | ||
Check entries in mail_addresses that refer to deleted messages | Check entries in mail_addresses that refer to deleted messages | ||
<code sql> | <code sql> | ||
- | SELECT ma.* FROM mail_addresses ma LEFT JOIN mail m ON ma.mail_id=m.mail_id WHERE m.mail_id IS NULL; | + | SELECT ma.* FROM mail_addresses ma LEFT JOIN all_mail |
</ | </ | ||
Line 14: | Line 19: | ||
<code sql> | <code sql> | ||
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 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 |
</ | </ | ||
Line 31: | Line 36: | ||
Check entries in '' | Check entries in '' | ||
<code sql> | <code sql> | ||
- | SELECT b.mail_id FROM body b LEFT JOIN mail m ON b.mail_id=m.mail_id WHERE m.mail_id IS NULL; | + | SELECT b.mail_id FROM body b LEFT JOIN all_mail |
</ | </ | ||
Line 37: | Line 42: | ||
<code sql> | <code sql> | ||
DELETE FROM body WHERE mail_id IN | DELETE FROM body WHERE mail_id IN | ||
- | | + | |
</ | </ | ||
Line 43: | Line 48: | ||
Check entries in '' | Check entries in '' | ||
<code sql> | <code sql> | ||
- | SELECT h.mail_id FROM header h LEFT JOIN mail m ON h.mail_id=m.mail_id WHERE m.mail_id IS NULL; | + | SELECT h.mail_id FROM header h LEFT JOIN all_mail |
</ | </ | ||
Line 49: | Line 54: | ||
<code sql> | <code sql> | ||
DELETE FROM header WHERE mail_id IN | DELETE FROM header WHERE mail_id IN | ||
- | | + | |
</ | </ | ||
Line 55: | Line 60: | ||
Check entries in '' | Check entries in '' | ||
<code sql> | <code sql> | ||
- | SELECT h.mail_id FROM notes h LEFT JOIN mail m ON h.mail_id=m.mail_id WHERE m.mail_id IS NULL; | + | SELECT h.mail_id FROM notes h LEFT JOIN all_mail |
</ | </ | ||
Line 61: | Line 66: | ||
<code sql> | <code sql> | ||
DELETE FROM notes WHERE mail_id IN | DELETE FROM notes WHERE mail_id IN | ||
- | | + | |
</ | </ | ||
Line 67: | Line 72: | ||
Check entries in '' | Check entries in '' | ||
<code sql> | <code sql> | ||
- | 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; | + | SELECT h.mail_id FROM raw_mail h LEFT JOIN all_mail |
</ | </ | ||
Line 73: | Line 78: | ||
<code sql> | <code sql> | ||
DELETE FROM raw_mail WHERE mail_id IN | DELETE FROM raw_mail WHERE mail_id IN | ||
- | | + | |
</ | </ | ||
Note that '' | Note that '' | ||
- | + | ||
+ | === mail_tags === | ||
+ | Check entries in '' | ||
+ | <code sql> | ||
+ | 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 '' | ||
+ | <code sql> | ||
+ | DELETE FROM mail_tags WHERE mail_id IN | ||
+ | | ||
+ | </ | ||
+ | |||
+ | Check entries in '' | ||
+ | <code sql> | ||
+ | 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 '' | ||
+ | <code sql> | ||
+ | DELETE FROM mail_tags WHERE mail_id IN | ||
+ | | ||
+ | </ | ||
+ | |||
+ | === mail === | ||
+ | Remove references to non-existing mailboxes (does not delete any message) | ||
+ | <code sql> | ||
+ | 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 '' | ||
+ | <code sql> | ||
+ | DELETE FROM tags_words WHERE tag_id IN | ||
+ | | ||
+ | </ | ||
+ | |||
+ | Delete entries from '' | ||
+ | <code sql> | ||
+ | DELETE FROM tags_words WHERE word_id IN | ||
+ | | ||
+ | </ | ||
+ | |||
+ | === inverted_word_index === | ||
+ | Delete entries from '' | ||
+ | <code sql> | ||
+ | DELETE FROM inverted_word_index WHERE word_id IN | ||
+ | | ||
+ | </ | ||
+ | |||
+ | |||
+ | === All deletes and updates combined === | ||
+ | <code sql> | ||
+ | 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 | ||
+ | | ||
+ | |||
+ | DELETE FROM header WHERE mail_id IN | ||
+ | | ||
+ | |||
+ | DELETE FROM notes WHERE mail_id IN | ||
+ | | ||
+ | |||
+ | DELETE FROM raw_mail WHERE mail_id IN | ||
+ | | ||
+ | |||
+ | DELETE FROM mail_tags WHERE mail_id IN | ||
+ | | ||
+ | |||
+ | DELETE FROM mail_tags WHERE mail_id IN | ||
+ | | ||
+ | |||
+ | 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 | ||
+ | | ||
+ | |||
+ | DELETE FROM tags_words WHERE word_id IN | ||
+ | | ||
+ | |||
+ | DELETE FROM inverted_word_index WHERE word_id IN | ||
+ | | ||
+ | </ |
schema_0_9_10_upgrade_notes.txt · Last modified: 2008/11/04 12:27 by daniel