User Tools

Site Tools


schema_0_9_11
no way to compare when less than two revisions

Differences

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


schema_0_9_11 [2009/06/13 20:45] (current) – created daniel
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 20:45 by daniel