Manitou-Mail Wiki

This is an old revision of the document!

---

SQL script that creates the database:

The plpgsql language should exist (CREATE LANGUAGE plpgsql)

-- Manitou-mail objects, postgresql script
-- Schema version: 0.9.10
 
-- suppress NOTICEs
SET client_min_messages TO error;
\SET ON_ERROR_STOP 1
 
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)
);
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);
 
INSERT INTO runtime_info(rt_key,rt_value) VALUES ('schema_version','0.9.9');
 
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
 
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
);
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 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);
 
\i delete_msg.sql
\i delete_msg_set.sql
\i trash_msg.sql
\i trash_msg_set.sql
\i untrash_msg.sql
 
COMMIT;