Schema creation script

Below is the SQL script for PostgreSQL that creates from scratch all the database objects. Before running it through psql, the database should exist and have UTF8 encoding (createdb -E UTF8 name_of_database in shell or CREATE DATABASE name_of_database ENCODING 'UTF8' under psql). Also, the current directory should contain the SQL files that are referenced at the end of the script; they contain the definitions of plpgsql functions.

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

-- Manitou-mail objects, postgresql script
-- Schema version: 0.9.11
 
-- create language plpgsql and ignore if error
\SET ON_ERROR_STOP off
BEGIN;
CREATE LANGUAGE plpgsql;
END;
 
-- suppress NOTICEs
SET client_min_messages TO error;
\SET ON_ERROR_STOP ON
 
BEGIN;
 
CREATE TABLE mailboxes (
 mbox_id INT,
 name text
);
CREATE UNIQUE INDEX mailboxes_mbox_idx ON mailboxes(mbox_id);
CREATE UNIQUE INDEX mailboxes_mbox_name ON mailboxes(name);
 
CREATE TABLE mail (
 mail_id  INT,
 sender  VARCHAR(200),
 toname  VARCHAR(200),
 replyto  VARCHAR(200),
 cc  VARCHAR(200),
 sender_fullname VARCHAR(200),
 subject  VARCHAR(1000),
 msg_date timestamptz DEFAULT now(),
 sender_date timestamptz,
 mbox_id INT REFERENCES mailboxes(mbox_id),
 user_lock INT,
 time_lock timestamptz,
 STATUS  INT,
 mod_userid INT,
 thread_id INT,
 message_id VARCHAR(100),
 in_reply_to INT,
 msg_day INT,
 date_processed timestamptz,
 operator INT,
 priority INT DEFAULT 0,
 attachments INT DEFAULT 0
);
CREATE UNIQUE INDEX pk_mail_idx ON mail(mail_id);
CREATE INDEX mail_in_replyto_idx ON mail(in_reply_to);
CREATE INDEX mail_message_id_idx ON mail(message_id);
CREATE INDEX mail_date_idx ON mail(msg_date);
CREATE INDEX mail_thread_idx ON mail(thread_id);
 
CREATE TABLE notes (
  mail_id INT REFERENCES mail(mail_id),
  note text,
  last_changed timestamptz DEFAULT now()
);
CREATE UNIQUE INDEX notes_idx ON notes(mail_id);
 
CREATE TABLE mail_status (
  mail_id INT,
  STATUS INT
);
CREATE UNIQUE INDEX pk_mail_status_idx ON mail_status(mail_id);
 
CREATE TABLE header (
 mail_id  INT REFERENCES mail(mail_id),
 LINES  TEXT,
 header_size INT
);
CREATE UNIQUE INDEX pk_header_idx ON header(mail_id);
 
CREATE TABLE body (
 mail_id  INT REFERENCES mail(mail_id),
 bodytext TEXT,
 textsize INT
);
CREATE UNIQUE INDEX pk_body_idx ON body(mail_id);
 
CREATE TABLE attachments (
 attachment_id INT PRIMARY KEY,
 mail_id  INT REFERENCES mail(mail_id),
 content_type VARCHAR(300),
 content_size INT,
 filename VARCHAR(300),
 charset VARCHAR(30),
 mime_content_id text
);
CREATE INDEX idx_attachments_mail_id ON Attachments(mail_id);
 
CREATE TABLE attachment_contents (
 attachment_id INT REFERENCES attachments(attachment_id),
 content  oid,
 fingerprint TEXT
);
CREATE UNIQUE INDEX attch_ct_idx ON attachment_contents(attachment_id);
CREATE INDEX attach_ct_fp_idx ON attachment_contents(fingerprint);
 
CREATE TABLE users (
 user_id  INT PRIMARY KEY,
 fullname VARCHAR(300),
 login  VARCHAR(80)
);
CREATE UNIQUE INDEX users_login_idx ON Users(Login);
 
CREATE TABLE tags (
 tag_id INT,
 name  VARCHAR(300),
 parent_id INT
);
CREATE UNIQUE INDEX tag_id_pk ON tags(tag_id);
ALTER TABLE tags ADD CONSTRAINT parent_tag_fk
 FOREIGN KEY (parent_id) REFERENCES tags(tag_id);
 
CREATE TABLE mail_tags (
 mail_id INT REFERENCES mail(mail_id),
 tag INT REFERENCES tags(tag_id),
 agent INT,
 date_insert timestamptz DEFAULT now()
);
CREATE UNIQUE INDEX mail_tags_idx ON mail_tags(mail_id,tag);
 
CREATE TABLE config (
 conf_key VARCHAR(100) NOT NULL,
 VALUE  VARCHAR(2000),
 conf_name VARCHAR(100),
 date_update timestamptz
);
 
CREATE TABLE files (
 mail_id  INT,
 filename VARCHAR(300)
);
 
CREATE TABLE addresses (
 addr_id INT,
 email_addr  VARCHAR(300),
 name  VARCHAR(300),
 nickname VARCHAR(300),
 last_sent_to timestamptz,
 last_recv_from timestamptz,
 nb_messages INT,
 notes text,
 owner_id INT,
 invalid INT DEFAULT 0,
 recv_pri INT DEFAULT 0,
 nb_sent_to INT,
 nb_recv_from INT
);
CREATE UNIQUE INDEX pk_address_idx ON addresses(addr_id);
CREATE INDEX addresses_email_idx ON addresses(email_addr);
CREATE INDEX addresses_nickname_idx ON addresses(nickname);
 
 
CREATE TABLE mail_addresses (
 mail_id INT REFERENCES mail(mail_id),
 addr_id INT REFERENCES addresses(addr_id),
 addr_type SMALLINT,
 addr_pos SMALLINT
);
CREATE INDEX mail_addresses_addrid_idx ON mail_addresses(addr_id);
CREATE INDEX mail_addresses_mailid_idx ON mail_addresses(mail_id);
 
CREATE TABLE programs (
 program_name VARCHAR(256),
 content_type VARCHAR(256),
 conf_name VARCHAR(100)
);
 
CREATE TABLE mime_types (
 suffix VARCHAR(20) NOT NULL,
 mime_type VARCHAR(100) NOT NULL
);
 
CREATE TABLE runtime_info (
  rt_key VARCHAR(100) NOT NULL,
  rt_value VARCHAR(1000)
);
CREATE UNIQUE INDEX runtime_info_pk ON runtime_info(rt_key);
 
CREATE TABLE identities (
  email_addr VARCHAR(200) NOT NULL,
  username VARCHAR(200),
  xface VARCHAR(2000),
  signature text
);
 
CREATE TABLE words (
 word_id INT PRIMARY KEY,
 wordtext VARCHAR(50)
);
CREATE UNIQUE INDEX wordtext_idx ON words(wordtext);
 
CREATE SEQUENCE seq_word_id;
 
CREATE TABLE non_indexable_words (
  wordtext VARCHAR(50)
);
 
CREATE TABLE filter_expr (
  expr_id INT PRIMARY KEY,
  name VARCHAR(100),
  user_lastmod INT,
  last_update timestamptz DEFAULT now(),
  expression text,
  direction CHAR(1) DEFAULT 'I'
);
CREATE UNIQUE INDEX expr_idx ON filter_expr(name);
 
CREATE TABLE filter_action (
 expr_id INT REFERENCES filter_expr(expr_id),
 action_order SMALLINT,
 action_arg text,
 action_type VARCHAR(100)
);
CREATE UNIQUE INDEX filter_action_idx ON filter_action(expr_id,action_order);
 
CREATE TABLE filter_log (
 expr_id INT,   -- No reference to filter_expr, we don't want any constraint here
 mail_id INT,   -- No reference to mail to be able to delete mail without touching this table
 hit_date timestamptz DEFAULT now()
);
 
CREATE TABLE user_queries (
 title VARCHAR(100) NOT NULL,
 sql_stmt text
);
CREATE UNIQUE INDEX user_queries_idx ON user_queries(title);
 
INSERT INTO mime_types VALUES('txt', 'text/plain');
INSERT INTO mime_types VALUES('htm', 'text/html');
INSERT INTO mime_types VALUES('html', 'text/html');
INSERT INTO mime_types VALUES('xml', 'text/xml');
INSERT INTO mime_types VALUES('rtf', 'application/rtf');
INSERT INTO mime_types VALUES('zip', 'application/zip');
INSERT INTO mime_types VALUES('doc', 'application/msword');
INSERT INTO mime_types VALUES('xls', 'application/vnd.ms-excel');
INSERT INTO mime_types VALUES('pdf', 'application/pdf');
INSERT INTO mime_types VALUES('tar', 'application/x-tar');
INSERT INTO mime_types VALUES('jpg', 'image/jpeg');
INSERT INTO mime_types VALUES('gif', 'image/gif');
INSERT INTO mime_types VALUES('png', 'image/png');
INSERT INTO mime_types VALUES('bmp', 'image/bmp');
 
CREATE TABLE tags_words (
  tag_id INT REFERENCES tags(tag_id),
  word_id INT REFERENCES words(word_id),
  counter INT
) WITHOUT OIDS;
CREATE INDEX tags_words_idx ON tags_words(word_id);
 
CREATE TABLE forward_addresses (
 to_email_addr  VARCHAR(300),
 forward_to  text
);
CREATE UNIQUE INDEX fwa_idx ON forward_addresses(to_email_addr);
 
CREATE TABLE raw_mail (
  mail_id INT REFERENCES mail(mail_id),
  mail_text oid
);
CREATE UNIQUE INDEX idx_raw_mail ON raw_mail(mail_id);
 
CREATE TABLE inverted_word_index (
  word_id INT REFERENCES words(word_id),
  part_no INT,
  mailvec bytea,
  nz_offset INT DEFAULT 0
);
CREATE UNIQUE INDEX iwi_idx ON inverted_word_index(word_id,part_no);
 
CREATE TABLE jobs_queue (
 job_id serial,
 mail_id INT,
 job_type VARCHAR(4),
 job_args text
);
CREATE UNIQUE INDEX jobs_pk_idx ON jobs_queue(job_id);
 
 
CREATE SEQUENCE seq_tag_id;
CREATE SEQUENCE seq_mail_id;
CREATE SEQUENCE seq_thread_id;
CREATE SEQUENCE seq_addr_id;
CREATE SEQUENCE seq_attachment_id;
 
\i triggers_on_mail.sql
\i delete_msg.sql
\i delete_msg_set.sql
\i trash_msg.sql
\i trash_msg_set.sql
\i untrash_msg.sql
 
INSERT INTO runtime_info(rt_key,rt_value) VALUES ('schema_version','0.9.11');
 
END;