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>