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 ('' | ||
+ | |||
+ | This script as well as all the SQL files are available in [[http:// | ||
+ | |||
+ | <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 ( | ||
+ | | ||
+ | name text | ||
+ | ); | ||
+ | CREATE UNIQUE INDEX mailboxes_mbox_idx ON mailboxes(mbox_id); | ||
+ | CREATE UNIQUE INDEX mailboxes_mbox_name ON mailboxes(name); | ||
+ | |||
+ | CREATE TABLE mail ( | ||
+ | | ||
+ | | ||
+ | | ||
+ | | ||
+ | | ||
+ | | ||
+ | | ||
+ | | ||
+ | | ||
+ | | ||
+ | | ||
+ | | ||
+ | | ||
+ | | ||
+ | | ||
+ | | ||
+ | | ||
+ | | ||
+ | | ||
+ | | ||
+ | | ||
+ | | ||
+ | ); | ||
+ | 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 ( | ||
+ | | ||
+ | | ||
+ | | ||
+ | ); | ||
+ | create unique index pk_header_idx on header(mail_id); | ||
+ | |||
+ | CREATE TABLE body ( | ||
+ | | ||
+ | | ||
+ | | ||
+ | ); | ||
+ | create unique index pk_body_idx on body(mail_id); | ||
+ | |||
+ | CREATE TABLE attachments ( | ||
+ | | ||
+ | | ||
+ | | ||
+ | | ||
+ | | ||
+ | | ||
+ | | ||
+ | ); | ||
+ | CREATE INDEX idx_attachments_mail_id ON Attachments(mail_id); | ||
+ | |||
+ | CREATE TABLE attachment_contents ( | ||
+ | | ||
+ | | ||
+ | | ||
+ | ); | ||
+ | CREATE UNIQUE INDEX attch_ct_idx on attachment_contents(attachment_id); | ||
+ | CREATE INDEX attach_ct_fp_idx ON attachment_contents(fingerprint); | ||
+ | |||
+ | CREATE TABLE users ( | ||
+ | | ||
+ | | ||
+ | | ||
+ | ); | ||
+ | CREATE UNIQUE INDEX users_login_idx ON Users(Login); | ||
+ | |||
+ | CREATE TABLE tags ( | ||
+ | | ||
+ | | ||
+ | | ||
+ | ); | ||
+ | CREATE UNIQUE INDEX tag_id_pk ON tags(tag_id); | ||
+ | ALTER TABLE tags ADD CONSTRAINT parent_tag_fk | ||
+ | | ||
+ | |||
+ | CREATE TABLE mail_tags ( | ||
+ | | ||
+ | tag INT REFERENCES tags(tag_id), | ||
+ | agent INT, | ||
+ | | ||
+ | ); | ||
+ | CREATE UNIQUE INDEX mail_tags_idx ON mail_tags(mail_id, | ||
+ | |||
+ | CREATE TABLE config ( | ||
+ | | ||
+ | | ||
+ | | ||
+ | | ||
+ | ); | ||
+ | |||
+ | CREATE TABLE files ( | ||
+ | | ||
+ | | ||
+ | ); | ||
+ | |||
+ | CREATE TABLE addresses ( | ||
+ | | ||
+ | | ||
+ | | ||
+ | | ||
+ | | ||
+ | | ||
+ | | ||
+ | notes text, | ||
+ | | ||
+ | | ||
+ | | ||
+ | | ||
+ | | ||
+ | ); | ||
+ | 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 ( | ||
+ | | ||
+ | | ||
+ | | ||
+ | | ||
+ | ); | ||
+ | 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 ( | ||
+ | | ||
+ | | ||
+ | | ||
+ | ); | ||
+ | |||
+ | CREATE TABLE mime_types ( | ||
+ | | ||
+ | | ||
+ | ); | ||
+ | |||
+ | 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 ( | ||
+ | | ||
+ | | ||
+ | ); | ||
+ | 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 ' | ||
+ | ); | ||
+ | CREATE UNIQUE INDEX expr_idx ON filter_expr(name); | ||
+ | |||
+ | CREATE TABLE filter_action ( | ||
+ | | ||
+ | | ||
+ | | ||
+ | | ||
+ | ); | ||
+ | CREATE UNIQUE INDEX filter_action_idx ON filter_action(expr_id, | ||
+ | |||
+ | CREATE TABLE filter_log ( | ||
+ | | ||
+ | | ||
+ | | ||
+ | ); | ||
+ | |||
+ | CREATE TABLE user_queries ( | ||
+ | title varchar(100) NOT NULL, | ||
+ | | ||
+ | ); | ||
+ | CREATE UNIQUE INDEX user_queries_idx ON user_queries(title); | ||
+ | |||
+ | INSERT INTO mime_types VALUES(' | ||
+ | INSERT INTO mime_types VALUES(' | ||
+ | INSERT INTO mime_types VALUES(' | ||
+ | INSERT INTO mime_types VALUES(' | ||
+ | INSERT INTO mime_types VALUES(' | ||
+ | INSERT INTO mime_types VALUES(' | ||
+ | INSERT INTO mime_types VALUES(' | ||
+ | INSERT INTO mime_types VALUES(' | ||
+ | INSERT INTO mime_types VALUES(' | ||
+ | INSERT INTO mime_types VALUES(' | ||
+ | INSERT INTO mime_types VALUES(' | ||
+ | INSERT INTO mime_types VALUES(' | ||
+ | INSERT INTO mime_types VALUES(' | ||
+ | INSERT INTO mime_types VALUES(' | ||
+ | |||
+ | 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 ( | ||
+ | | ||
+ | | ||
+ | ); | ||
+ | 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, | ||
+ | |||
+ | CREATE TABLE jobs_queue ( | ||
+ | | ||
+ | | ||
+ | | ||
+ | | ||
+ | ); | ||
+ | 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, | ||
+ | |||
+ | END; | ||
+ | </ | ||
schema_0_9_11.txt · Last modified: 2009/06/13 20:45 by daniel