File: /volume1/@appstore/MailClient/etc/sql/mail_client_1.sql
CREATE TABLE attachment_temp(
id integer NOT NULL,
is_inline boolean NOT NULL,
content_id varchar(64) NOT NULL,
name varchar(256) NOT NULL,
path text NOT NULL,
CONSTRAINT attachment_temp_id_pk PRIMARY KEY (id),
CONSTRAINT attachment_temp_content_id_uk UNIQUE (content_id)
);
CREATE TABLE reference(
id integer NOT NULL,
msg_id varchar(1024) NOT NULL,
CONSTRAINT reference_id_pk PRIMARY KEY (id),
CONSTRAINT reference_msg_id_uk UNIQUE (msg_id)
);
CREATE TABLE thread(
id integer NOT NULL,
last_modified bigint NOT NULL,
CONSTRAINT thread_id_pk PRIMARY KEY (id)
);
CREATE TABLE sticker(
id integer NOT NULL,
category integer NOT NULL DEFAULT 0,
name varchar(256) NOT NULL,
path text NOT NULL,
last_modified bigint NOT NULL DEFAULT (strftime('%s', 'now')),
CONSTRAINT sticker_id_pk PRIMARY KEY (id)
);
CREATE TABLE mailbox(
id integer NOT NULL,
is_subscribed boolean NOT NULL,
uid_validity bigint NOT NULL,
highest_mod_seq varchar(32) NOT NULL,
path varchar(256) NOT NULL,
CONSTRAINT mailbox_id_pk PRIMARY KEY (id),
CONSTRAINT mailbox_uid_validity_uk UNIQUE (uid_validity),
CONSTRAINT mailbox_path_uk UNIQUE (path)
);
CREATE TABLE filter(
id integer NOT NULL,
sequence integer NOT NULL DEFAULT 0,
condition text NOT NULL,
action text NOT NULL,
updated integer NOT NULL DEFAULT 0,
CONSTRAINT filter_id_pk PRIMARY KEY (id)
);
CREATE TABLE many_message_has_many_reference(
is_message_id boolean NOT NULL DEFAULT 0,
id_message integer,
id_reference integer,
CONSTRAINT many_message_has_many_reference_pk PRIMARY KEY (id_message,id_reference),
CONSTRAINT message_fk FOREIGN KEY (id_message) REFERENCES message (id) MATCH FULL
ON DELETE CASCADE ON UPDATE CASCADE NOT DEFERRABLE,
CONSTRAINT reference_fk FOREIGN KEY (id_reference) REFERENCES reference (id) MATCH FULL
ON DELETE CASCADE ON UPDATE CASCADE NOT DEFERRABLE
);
CREATE TABLE many_thread_has_many_label(
id_thread integer,
id_label integer,
CONSTRAINT many_thread_has_many_label_pk PRIMARY KEY (id_thread,id_label),
CONSTRAINT thread_fk FOREIGN KEY (id_thread) REFERENCES thread (id) MATCH FULL
ON DELETE CASCADE ON UPDATE CASCADE NOT DEFERRABLE,
CONSTRAINT label_fk FOREIGN KEY (id_label) REFERENCES label (id) MATCH FULL
ON DELETE CASCADE ON UPDATE CASCADE NOT DEFERRABLE
);
CREATE TABLE attachment(
id integer NOT NULL,
is_inline boolean NOT NULL,
size integer NOT NULL,
part_id varchar(16) NOT NULL,
encoding varchar(32) NOT NULL,
content_id varchar(64) NOT NULL,
mimetype varchar(128) NOT NULL,
name varchar(256) NOT NULL,
preview_path text NOT NULL DEFAULT '',
id_message integer NOT NULL,
CONSTRAINT attachment_id_pk PRIMARY KEY (id),
CONSTRAINT message_fk FOREIGN KEY (id_message) REFERENCES message (id) MATCH FULL
ON DELETE RESTRICT ON UPDATE CASCADE NOT DEFERRABLE
);
CREATE TABLE email(
id integer NOT NULL,
display_addr varchar(1024) NOT NULL,
CONSTRAINT email_id_pk PRIMARY KEY (id),
CONSTRAINT email_display_addr_uk UNIQUE (display_addr)
);
CREATE TABLE many_message_has_many_recipient(
id_message integer,
id_email integer,
CONSTRAINT many_message_has_many_recipient_pk PRIMARY KEY (id_message,id_email),
CONSTRAINT message_fk FOREIGN KEY (id_message) REFERENCES message (id) MATCH FULL
ON DELETE CASCADE ON UPDATE CASCADE NOT DEFERRABLE,
CONSTRAINT email_fk FOREIGN KEY (id_email) REFERENCES email (id) MATCH FULL
ON DELETE CASCADE ON UPDATE CASCADE NOT DEFERRABLE
);
CREATE TABLE message(
id integer NOT NULL,
uid bigint NOT NULL,
type integer NOT NULL DEFAULT 0,
is_read boolean NOT NULL,
star integer NOT NULL DEFAULT 0,
body_preview varchar(512) NOT NULL,
from_addr varchar(1024) NOT NULL,
subject varchar(1024) NOT NULL,
arrival_time bigint NOT NULL,
last_modified bigint NOT NULL,
reply_to integer,
id_thread integer NOT NULL,
id_mailbox integer NOT NULL,
CONSTRAINT message_id_pk PRIMARY KEY (id),
CONSTRAINT reply_to_fk FOREIGN KEY (reply_to)
REFERENCES message (id) MATCH FULL
ON DELETE SET NULL ON UPDATE CASCADE NOT DEFERRABLE,
CONSTRAINT thread_fk FOREIGN KEY (id_thread) REFERENCES thread (id) MATCH FULL
ON DELETE RESTRICT ON UPDATE CASCADE NOT DEFERRABLE,
CONSTRAINT mailbox_fk FOREIGN KEY (id_mailbox) REFERENCES mailbox (id) MATCH FULL
ON DELETE RESTRICT ON UPDATE CASCADE NOT DEFERRABLE,
CONSTRAINT message_uid_mailbox_uk UNIQUE (uid, id_mailbox)
);
CREATE TABLE label(
id integer NOT NULL,
flag_name varchar(64) NOT NULL,
name varchar(128) NOT NULL,
config text NOT NULL,
updated integer NOT NULL DEFAULT 0,
CONSTRAINT label_id_pk PRIMARY KEY (id),
CONSTRAINT label_flag_name_uk UNIQUE (flag_name),
CONSTRAINT label_name_uk UNIQUE (name)
);
CREATE TABLE config(
key varchar(256) NOT NULL,
value text NOT NULL,
updated integer NOT NULL DEFAULT 0,
CONSTRAINT config_key_pk PRIMARY KEY (key)
);
CREATE VIEW reference_with_message_id
AS SELECT * FROM reference INNER JOIN many_message_has_many_reference ON reference.id = many_message_has_many_reference.id_reference;
CREATE VIEW reference_with_thread_id
AS SELECT * FROM reference_with_message_id INNER JOIN (SELECT id, id_thread FROM message) AS t1 ON t1.id = reference_with_message_id.id_message;
CREATE VIEW thread_max_arrival
AS SELECT * FROM thread INNER JOIN (SELECT id_thread, MAX(arrival_time) AS max_arrival_time FROM message WHERE id_mailbox NOT IN (-3, -5, -6) GROUP BY id_thread) AS t1 on thread.id = t1.id_thread;
CREATE VIEW thread_max_arrival_draft
AS SELECT * FROM thread INNER JOIN (SELECT id_thread, MAX(arrival_time) AS max_arrival_time FROM message WHERE id_mailbox = -3 GROUP BY id_thread) AS t1 on thread.id = t1.id_thread;
CREATE VIEW thread_max_arrival_junk
AS SELECT * FROM thread INNER JOIN (SELECT id_thread, MAX(arrival_time) AS max_arrival_time FROM message WHERE id_mailbox = -5 GROUP BY id_thread) AS t1 on thread.id = t1.id_thread;
CREATE VIEW thread_max_arrival_sent
AS SELECT * FROM thread INNER JOIN (SELECT id_thread, MAX(arrival_time) AS max_arrival_time FROM message WHERE id_mailbox = -4 GROUP BY id_thread) AS t1 on thread.id = t1.id_thread;
CREATE VIEW thread_max_arrival_trash
AS SELECT * FROM thread INNER JOIN (SELECT id_thread, MAX(arrival_time) AS max_arrival_time FROM message WHERE id_mailbox = -6 GROUP BY id_thread) AS t1 on thread.id = t1.id_thread;
CREATE VIEW thread_max_arrival_single_draft
AS SELECT * FROM thread INNER JOIN ( SELECT id_thread, MAX(arrival_time) AS max_arrival_time FROM message WHERE id_mailbox NOT IN (-3, -5, -6) GROUP BY id_thread UNION ALL select id_thread, arrival_time as max_arrival_time from message group by id_thread having count(id_thread) = 1 AND id_mailbox = -3 ) AS t1 on thread.id = t1.id_thread;
CREATE VIEW message_with_recipient
AS SELECT id_message, group_concat(display_addr) AS recipient FROM (SELECT * FROM many_message_has_many_recipient LEFT JOIN email ON id_email = email.id) GROUP BY id_message;
CREATE INDEX message_uid_idx ON message (uid);
CREATE INDEX message_arrival_time_idx ON message (arrival_time);
CREATE INDEX sticker_category_idx ON sticker (category);
CREATE INDEX message_thread_id_idx ON message (id_thread);
CREATE INDEX reference_msg_id_idx ON reference (msg_id);
CREATE INDEX message_mailbox_id_idx ON message (id_mailbox);
INSERT INTO sticker VALUES(1,1,'face_01.png','/var/packages/MailClient/target/ui/images/emoticon/2x/face_01.png', 0);
INSERT INTO sticker VALUES(2,1,'face_02.png','/var/packages/MailClient/target/ui/images/emoticon/2x/face_02.png', 0);
INSERT INTO sticker VALUES(3,1,'face_03.png','/var/packages/MailClient/target/ui/images/emoticon/2x/face_03.png', 0);
INSERT INTO sticker VALUES(4,1,'face_04.png','/var/packages/MailClient/target/ui/images/emoticon/2x/face_04.png', 0);
INSERT INTO sticker VALUES(5,1,'face_05.png','/var/packages/MailClient/target/ui/images/emoticon/2x/face_05.png', 0);
INSERT INTO sticker VALUES(6,1,'face_06.png','/var/packages/MailClient/target/ui/images/emoticon/2x/face_06.png', 0);
INSERT INTO sticker VALUES(7,1,'face_07.png','/var/packages/MailClient/target/ui/images/emoticon/2x/face_07.png', 0);
INSERT INTO sticker VALUES(8,1,'face_08.png','/var/packages/MailClient/target/ui/images/emoticon/2x/face_08.png', 0);
INSERT INTO sticker VALUES(9,1,'face_09.png','/var/packages/MailClient/target/ui/images/emoticon/2x/face_09.png', 0);
INSERT INTO sticker VALUES(10,1,'face_10.png','/var/packages/MailClient/target/ui/images/emoticon/2x/face_10.png', 0);
INSERT INTO sticker VALUES(11,1,'face_11.png','/var/packages/MailClient/target/ui/images/emoticon/2x/face_11.png', 0);
INSERT INTO sticker VALUES(12,1,'face_12.png','/var/packages/MailClient/target/ui/images/emoticon/2x/face_12.png', 0);
INSERT INTO sticker VALUES(13,1,'face_13.png','/var/packages/MailClient/target/ui/images/emoticon/2x/face_13.png', 0);
INSERT INTO sticker VALUES(14,1,'face_14.png','/var/packages/MailClient/target/ui/images/emoticon/2x/face_14.png', 0);
INSERT INTO sticker VALUES(15,1,'face_15.png','/var/packages/MailClient/target/ui/images/emoticon/2x/face_15.png', 0);
INSERT INTO sticker VALUES(16,1,'face_16.png','/var/packages/MailClient/target/ui/images/emoticon/2x/face_16.png', 0);
INSERT INTO sticker VALUES(17,1,'face_17.png','/var/packages/MailClient/target/ui/images/emoticon/2x/face_17.png', 0);
INSERT INTO sticker VALUES(18,1,'face_18.png','/var/packages/MailClient/target/ui/images/emoticon/2x/face_18.png', 0);
INSERT INTO sticker VALUES(19,1,'face_19.png','/var/packages/MailClient/target/ui/images/emoticon/2x/face_19.png', 0);
INSERT INTO sticker VALUES(20,1,'face_20.png','/var/packages/MailClient/target/ui/images/emoticon/2x/face_20.png', 0);
INSERT INTO sticker VALUES(21,1,'face_21.png','/var/packages/MailClient/target/ui/images/emoticon/2x/face_21.png', 0);
INSERT INTO sticker VALUES(22,1,'face_22.png','/var/packages/MailClient/target/ui/images/emoticon/2x/face_22.png', 0);
INSERT INTO sticker VALUES(23,1,'face_23.png','/var/packages/MailClient/target/ui/images/emoticon/2x/face_23.png', 0);
INSERT INTO sticker VALUES(24,1,'face_24.png','/var/packages/MailClient/target/ui/images/emoticon/2x/face_24.png', 0);
INSERT INTO sticker VALUES(25,1,'face_25.png','/var/packages/MailClient/target/ui/images/emoticon/2x/face_25.png', 0);
INSERT INTO sticker VALUES(26,1,'face_26.png','/var/packages/MailClient/target/ui/images/emoticon/2x/face_26.png', 0);
INSERT INTO sticker VALUES(27,1,'face_27.png','/var/packages/MailClient/target/ui/images/emoticon/2x/face_27.png', 0);
INSERT INTO sticker VALUES(28,1,'face_28.png','/var/packages/MailClient/target/ui/images/emoticon/2x/face_28.png', 0);
INSERT INTO sticker VALUES(29,1,'face_29.png','/var/packages/MailClient/target/ui/images/emoticon/2x/face_29.png', 0);
INSERT INTO sticker VALUES(30,1,'face_30.png','/var/packages/MailClient/target/ui/images/emoticon/2x/face_30.png', 0);
INSERT INTO sticker VALUES(31,1,'item_01.png','/var/packages/MailClient/target/ui/images/emoticon/2x/item_01.png', 0);
INSERT INTO sticker VALUES(32,1,'item_02.png','/var/packages/MailClient/target/ui/images/emoticon/2x/item_02.png', 0);
INSERT INTO sticker VALUES(33,1,'item_03.png','/var/packages/MailClient/target/ui/images/emoticon/2x/item_03.png', 0);
INSERT INTO sticker VALUES(34,1,'item_04.png','/var/packages/MailClient/target/ui/images/emoticon/2x/item_04.png', 0);
INSERT INTO sticker VALUES(35,1,'item_05.png','/var/packages/MailClient/target/ui/images/emoticon/2x/item_05.png', 0);
INSERT INTO sticker VALUES(36,1,'item_06.png','/var/packages/MailClient/target/ui/images/emoticon/2x/item_06.png', 0);
INSERT INTO sticker VALUES(37,1,'item_07.png','/var/packages/MailClient/target/ui/images/emoticon/2x/item_07.png', 0);
INSERT INTO sticker VALUES(38,1,'item_08.png','/var/packages/MailClient/target/ui/images/emoticon/2x/item_08.png', 0);
INSERT INTO sticker VALUES(39,1,'item_09.png','/var/packages/MailClient/target/ui/images/emoticon/2x/item_09.png', 0);
INSERT INTO sticker VALUES(40,1,'item_10.png','/var/packages/MailClient/target/ui/images/emoticon/2x/item_10.png', 0);
INSERT INTO sticker VALUES(41,1,'text_01.png','/var/packages/MailClient/target/ui/images/emoticon/2x/text_01.png', 0);
INSERT INTO sticker VALUES(42,1,'text_02.png','/var/packages/MailClient/target/ui/images/emoticon/2x/text_02.png', 0);
INSERT INTO sticker VALUES(43,1,'text_03.png','/var/packages/MailClient/target/ui/images/emoticon/2x/text_03.png', 0);
INSERT INTO sticker VALUES(44,2,'01.png','/var/packages/MailClient/target/ui/images/sticker/business_man/01.png', 0);
INSERT INTO sticker VALUES(45,2,'02.png','/var/packages/MailClient/target/ui/images/sticker/business_man/02.png', 0);
INSERT INTO sticker VALUES(46,2,'03.png','/var/packages/MailClient/target/ui/images/sticker/business_man/03.png', 0);
INSERT INTO sticker VALUES(47,2,'04.png','/var/packages/MailClient/target/ui/images/sticker/business_man/04.png', 0);
INSERT INTO sticker VALUES(48,2,'05.png','/var/packages/MailClient/target/ui/images/sticker/business_man/05.png', 0);
INSERT INTO sticker VALUES(49,2,'06.png','/var/packages/MailClient/target/ui/images/sticker/business_man/06.png', 0);
INSERT INTO sticker VALUES(50,2,'07.png','/var/packages/MailClient/target/ui/images/sticker/business_man/07.png', 0);
INSERT INTO sticker VALUES(51,2,'08.png','/var/packages/MailClient/target/ui/images/sticker/business_man/08.png', 0);
INSERT INTO sticker VALUES(52,2,'09.png','/var/packages/MailClient/target/ui/images/sticker/business_man/09.png', 0);
INSERT INTO sticker VALUES(53,2,'10.png','/var/packages/MailClient/target/ui/images/sticker/business_man/10.png', 0);
INSERT INTO sticker VALUES(54,2,'11.png','/var/packages/MailClient/target/ui/images/sticker/business_man/11.png', 0);
INSERT INTO sticker VALUES(55,2,'12.png','/var/packages/MailClient/target/ui/images/sticker/business_man/12.png', 0);
INSERT INTO sticker VALUES(56,2,'13.png','/var/packages/MailClient/target/ui/images/sticker/business_man/13.png', 0);
INSERT INTO sticker VALUES(57,2,'14.png','/var/packages/MailClient/target/ui/images/sticker/business_man/14.png', 0);
INSERT INTO sticker VALUES(58,2,'15.png','/var/packages/MailClient/target/ui/images/sticker/business_man/15.png', 0);
INSERT INTO sticker VALUES(59,2,'16.png','/var/packages/MailClient/target/ui/images/sticker/business_man/16.png', 0);
INSERT INTO sticker VALUES(60,2,'17.png','/var/packages/MailClient/target/ui/images/sticker/business_man/17.png', 0);
INSERT INTO sticker VALUES(61,2,'18.png','/var/packages/MailClient/target/ui/images/sticker/business_man/18.png', 0);
INSERT INTO sticker VALUES(62,2,'19.png','/var/packages/MailClient/target/ui/images/sticker/business_man/19.png', 0);
INSERT INTO sticker VALUES(63,2,'20.png','/var/packages/MailClient/target/ui/images/sticker/business_man/20.png', 0);
INSERT INTO sticker VALUES(64,2,'21.png','/var/packages/MailClient/target/ui/images/sticker/business_man/21.png', 0);
INSERT INTO sticker VALUES(65,3,'01.png','/var/packages/MailClient/target/ui/images/sticker/business_woman/01.png', 0);
INSERT INTO sticker VALUES(66,3,'02.png','/var/packages/MailClient/target/ui/images/sticker/business_woman/02.png', 0);
INSERT INTO sticker VALUES(67,3,'03.png','/var/packages/MailClient/target/ui/images/sticker/business_woman/03.png', 0);
INSERT INTO sticker VALUES(68,3,'04.png','/var/packages/MailClient/target/ui/images/sticker/business_woman/04.png', 0);
INSERT INTO sticker VALUES(69,3,'05.png','/var/packages/MailClient/target/ui/images/sticker/business_woman/05.png', 0);
INSERT INTO sticker VALUES(70,3,'06.png','/var/packages/MailClient/target/ui/images/sticker/business_woman/06.png', 0);
INSERT INTO sticker VALUES(71,3,'07.png','/var/packages/MailClient/target/ui/images/sticker/business_woman/07.png', 0);
INSERT INTO sticker VALUES(72,3,'08.png','/var/packages/MailClient/target/ui/images/sticker/business_woman/08.png', 0);
INSERT INTO sticker VALUES(73,3,'09.png','/var/packages/MailClient/target/ui/images/sticker/business_woman/09.png', 0);
INSERT INTO sticker VALUES(74,3,'10.png','/var/packages/MailClient/target/ui/images/sticker/business_woman/10.png', 0);
INSERT INTO sticker VALUES(75,3,'11.png','/var/packages/MailClient/target/ui/images/sticker/business_woman/11.png', 0);
INSERT INTO sticker VALUES(76,3,'12.png','/var/packages/MailClient/target/ui/images/sticker/business_woman/12.png', 0);
INSERT INTO sticker VALUES(77,3,'13.png','/var/packages/MailClient/target/ui/images/sticker/business_woman/13.png', 0);
INSERT INTO sticker VALUES(78,3,'14.png','/var/packages/MailClient/target/ui/images/sticker/business_woman/14.png', 0);
INSERT INTO sticker VALUES(79,3,'15.png','/var/packages/MailClient/target/ui/images/sticker/business_woman/15.png', 0);
INSERT INTO sticker VALUES(80,3,'16.png','/var/packages/MailClient/target/ui/images/sticker/business_woman/16.png', 0);
INSERT INTO sticker VALUES(81,3,'17.png','/var/packages/MailClient/target/ui/images/sticker/business_woman/17.png', 0);
INSERT INTO sticker VALUES(82,3,'18.png','/var/packages/MailClient/target/ui/images/sticker/business_woman/18.png', 0);
INSERT INTO sticker VALUES(83,3,'19.png','/var/packages/MailClient/target/ui/images/sticker/business_woman/19.png', 0);
INSERT INTO sticker VALUES(84,3,'20.png','/var/packages/MailClient/target/ui/images/sticker/business_woman/20.png', 0);
INSERT INTO sticker VALUES(85,3,'21.png','/var/packages/MailClient/target/ui/images/sticker/business_woman/21.png', 0);
UPDATE sticker SET last_modified = strftime('%s','now');
INSERT INTO mailbox VALUES(0, 0, 0, 0, '');
INSERT INTO config (key, value) VALUES('version', '1');