Manitou-Mail Wiki

Below is the SQL script for PostgreSQL that upgrades the schema from 0.9.9 to 0.9.10. The changes fall mostly in two categories:

• removing the trashed_mail table, the trashcan being merged into the mail table
• setting referential integrity constraints

This script as well as all the SQL files are available in http://www.manitou-mail.org/source/manitou-sql-0.9.10.tar.gz

-- This SQL script upgrades the database schema for manitou-mail
-- from 0.9.9 to 0.9.10 version
 
\SET ON_ERROR_STOP 1
 
BEGIN;
 
INSERT INTO mail SELECT * FROM trashed_mail;
ALTER TABLE trashed_mail RENAME TO old_trashed_mail;
CREATE VIEW trashed_mail AS SELECT * FROM mail WHERE status&16=16;
 
ALTER TABLE mail_addresses ADD CONSTRAINT addresses_mail_fk
 FOREIGN KEY (mail_id) REFERENCES mail(mail_id);
 
ALTER TABLE mail_addresses ADD CONSTRAINT ma_addr_fk
 FOREIGN KEY (addr_id) REFERENCES addresses(addr_id);
 
ALTER TABLE attachments ADD CONSTRAINT attachments_mail_fk
 FOREIGN KEY (mail_id) REFERENCES mail(mail_id);
 
ALTER TABLE attachments ADD PRIMARY KEY (attachment_id);
 
ALTER TABLE attachment_contents ADD CONSTRAINT attachments_attch_fk
 FOREIGN KEY (attachment_id) REFERENCES attachments(attachment_id);
 
ALTER TABLE notes ADD CONSTRAINT notes_mail_fk
 FOREIGN KEY (mail_id) REFERENCES mail(mail_id);
 
ALTER TABLE header ADD CONSTRAINT header_mail_fk
 FOREIGN KEY (mail_id) REFERENCES mail(mail_id);
 
ALTER TABLE body ADD CONSTRAINT body_mail_fk
 FOREIGN KEY (mail_id) REFERENCES mail(mail_id);
 
ALTER TABLE tags ADD CONSTRAINT parent_tag_fk
 FOREIGN KEY (parent_id) REFERENCES tags(tag_id);
 
ALTER TABLE mail_tags ADD CONSTRAINT mt_tag_fk
 FOREIGN KEY (tag) REFERENCES tags(tag_id);
 
ALTER TABLE mail_tags ADD CONSTRAINT mt_mail_fk
 FOREIGN KEY (mail_id) REFERENCES mail(mail_id);
 
ALTER TABLE mail ADD CONSTRAINT mbox_mail_fk
 FOREIGN KEY (mbox_id) REFERENCES mailboxes(mbox_id);
 
DROP TABLE word_indexed_mail;
 
ALTER TABLE non_indexable_words ALTER COLUMN wordtext TYPE VARCHAR(50);
 
ALTER TABLE filter_action ADD CONSTRAINT filter_expr_fk
 FOREIGN KEY (expr_id) REFERENCES filter_expr(expr_id);
 
ALTER TABLE words ADD PRIMARY KEY (word_id);
 
ALTER TABLE tags_words ADD CONSTRAINT tags_words_tag_id_fk
 FOREIGN KEY (tag_id) REFERENCES tags(tag_id);
 
ALTER TABLE tags_words ADD CONSTRAINT tags_words_word_id_fk
 FOREIGN KEY (word_id) REFERENCES words(word_id);
 
ALTER TABLE raw_mail ADD CONSTRAINT raw_mail_fk
 FOREIGN KEY (mail_id) REFERENCES mail(mail_id);
 
ALTER TABLE inverted_word_index ADD CONSTRAINT iwi_word_id_fk
 FOREIGN KEY (word_id) REFERENCES words(word_id);
 
\i trash_msg.sql
\i trash_msg_set.sql
\i delete_msg.sql
\i delete_msg_set.sql
\i untrash_msg.sql
 
UPDATE runtime_info SET rt_value='0.9.10' WHERE rt_key='schema_version';
 
COMMIT;