schema_0_9_10_upgrade_notes
Differences
This shows you the differences between two versions of the page.
Both sides previous revisionPrevious revisionNext revision | Previous revision | ||
schema_0_9_10_upgrade_notes [2008/11/03 22:56] – daniel | schema_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 '' | 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 '' | ||
- | 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 '' | Generally, mail IDs can refer to entries that live either in '' | ||
Line 136: | Line 136: | ||
</ | </ | ||
+ | === attachments === | ||
+ | Check entries in '' | ||
+ | <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; | ||
+ | </ | ||
+ | |||
+ | Delete entries from '' | ||
+ | <code sql> | ||
+ | DELETE FROM attachment_contents WHERE attachment_id IN | ||
+ | | ||
+ | WHERE a.attachment_id IS NULL); | ||
+ | </ | ||
+ | |||
+ | Check entries in '' | ||
+ | <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; | ||
+ | </ | ||
+ | |||
+ | Delete entries from '' | ||
+ | <code sql> | ||
+ | DELETE FROM attachment_contents WHERE attachment_id IN | ||
+ | | ||
+ | |||
+ | DELETE FROM attachments WHERE attachment_id IN | ||
+ | | ||
+ | </ | ||
+ | The actual contents in large objects don't get removed by a simple delete, see the comment above on '' | ||
=== 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 | ||
| | ||
+ | |||
+ | DELETE FROM attachment_contents WHERE attachment_id IN | ||
+ | | ||
+ | WHERE a.attachment_id IS NULL); | ||
+ | |||
+ | DELETE FROM attachment_contents WHERE attachment_id IN | ||
+ | | ||
+ | |||
+ | DELETE FROM attachments WHERE attachment_id IN | ||
+ | | ||
+ | |||
</ | </ | ||
schema_0_9_10_upgrade_notes.1225752978.txt.gz · Last modified: 2008/11/03 22:56 by daniel