User Tools

Site Tools


schema_0_9_11

Differences

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

Link to this comparison view

schema_0_9_11 [2009/06/13 22:45] (current)
daniel created
Line 1: Line 1:
 +====== 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>​
  
schema_0_9_11.txt ยท Last modified: 2009/06/13 22:45 by daniel