====== 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]]

<code sql>
-- 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;
</code>