User Tools

Site Tools


schema_0_9_10_upgrade

Differences

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

Link to this comparison view

schema_0_9_10_upgrade [2008/11/02 23:12] (current)
daniel created
Line 1: Line 1:
 +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]]
 +
 +<code sql>
 +-- 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;
 +
 +</​code>​
 +
  
schema_0_9_10_upgrade.txt ยท Last modified: 2008/11/02 23:12 by daniel