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

Next revision		Previous revision
schema_0_9_10_upgrade_notes [2008/11/02 23:46] – created 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: 
 +<code sql> 
 +CREATE VIEW all_mail AS SELECT * FROM mail UNION SELECT * FROM trashed_mail; 
 +</code>
  
 === 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 m ON ma.mail_id=m.mail_id WHERE m.mail_id IS NULL;
 </code> </code>
  
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 m ON ma.mail_id=m.mail_id WHERE m.mail_id IS NULL);
 </code> </code>
  
Line 31: Line 36:
 Check entries in ''body'' that refer to deleted messages Check entries in ''body'' that refer to deleted messages
 <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 m ON b.mail_id=m.mail_id WHERE m.mail_id IS NULL;
 </code> </code>
  
Line 37: Line 42:
 <code sql> <code sql>
 DELETE FROM body WHERE mail_id IN  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);+ (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);
 </code> </code>
  
Line 43: Line 48:
 Check entries in ''header'' that refer to deleted messages Check entries in ''header'' that refer to deleted messages
 <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 m ON h.mail_id=m.mail_id WHERE m.mail_id IS NULL;
 </code> </code>
  
Line 49: Line 54:
 <code sql> <code sql>
 DELETE FROM header WHERE mail_id IN  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);+ (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);
 </code> </code>
  
Line 55: Line 60:
 Check entries in ''notes'' that refer to deleted messages Check entries in ''notes'' that refer to deleted messages
 <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 m ON h.mail_id=m.mail_id WHERE m.mail_id IS NULL;
 </code> </code>
  
Line 61: Line 66:
 <code sql> <code sql>
 DELETE FROM notes WHERE mail_id IN  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);+ (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);
 </code> </code>
  
Line 67: Line 72:
 Check entries in ''raw_mail'' that refer to deleted messages Check entries in ''raw_mail'' that refer to deleted messages
 <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 m ON h.mail_id=m.mail_id WHERE m.mail_id IS NULL;
 </code> </code>
  
Line 73: Line 78:
 <code sql> <code sql>
 DELETE FROM raw_mail WHERE mail_id IN  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);+ (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);
 </code> </code>
  
 Note that ''raw_mail'' contain references to large objects, which are not automatically purged on delete. [[http://www.postgresql.org/docs/8.3/static/vacuumlo.html|vacuumlo]] can be used to purge large objects that are no longer referenced. Note that ''raw_mail'' contain references to large objects, which are not automatically purged on delete. [[http://www.postgresql.org/docs/8.3/static/vacuumlo.html|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 
 +<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; 
 +</code> 
 + 
 +Delete entries from ''mail_tags'' that refer to deleted messages 
 +<code sql> 
 +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); 
 +</code> 
 + 
 +Check entries in ''mail_tags'' that refer to deleted tags 
 +<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; 
 +</code> 
 + 
 +Delete entries from ''mail_tags'' that refer to deleted tags 
 +<code sql> 
 +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); 
 +</code> 
 + 
 +=== 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); 
 +</code> 
 + 
 +=== tags_words === 
 +Delete entries from ''tags_words'' that refer to deleted tags 
 +<code sql> 
 +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); 
 +</code> 
 + 
 +Delete entries from ''tags_words'' that refer to deleted words 
 +<code sql> 
 +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); 
 +</code> 
 + 
 +=== inverted_word_index === 
 +Delete entries from ''inverted_word_index'' that refer to deleted words 
 +<code sql> 
 +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); 
 +</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 === 
 +<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 
 +(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); 
 + 
 +</code> 
schema_0_9_10_upgrade_notes.1225669583.txt.gz · Last modified: 2008/11/02 23:46 by daniel