From 80152b333ca5d856dcf09f5ca10a9ffd80ba117f Mon Sep 17 00:00:00 2001 From: alecpl Date: Wed, 7 Sep 2011 11:07:03 +0000 Subject: - Rewritten messages caching (merged devel-mcache branch): Indexes are stored in a separate table, so there's no need to store all messages in a folder Added threads data caching Flags are stored separately, so flag change doesn't cause DELETE+INSERT, just UPDATE - Partial QRESYNC support - Improved FETCH response handling - Improvements in response tokenization method --- SQL/postgres.update.sql | 43 +++++++++++++++++++++++++++++++++++++++++++ 1 file changed, 43 insertions(+) (limited to 'SQL/postgres.update.sql') diff --git a/SQL/postgres.update.sql b/SQL/postgres.update.sql index e316ff540..4bd0400c9 100644 --- a/SQL/postgres.update.sql +++ b/SQL/postgres.update.sql @@ -126,3 +126,46 @@ CREATE TABLE searches ( data text NOT NULL, CONSTRAINT searches_user_id_key UNIQUE (user_id, "type", name) ); + +DROP SEQUENCE messages_ids; +DROP TABLE messages; + +CREATE TABLE cache_index ( + user_id integer NOT NULL + REFERENCES users (user_id) ON DELETE CASCADE ON UPDATE CASCADE, + mailbox varchar(255) NOT NULL, + changed timestamp with time zone DEFAULT now() NOT NULL, + data text NOT NULL, + PRIMARY KEY (user_id, mailbox) +); + +CREATE INDEX cache_index_changed_idx ON cache_index (changed); + +CREATE TABLE cache_thread ( + user_id integer NOT NULL + REFERENCES users (user_id) ON DELETE CASCADE ON UPDATE CASCADE, + mailbox varchar(255) NOT NULL, + changed timestamp with time zone DEFAULT now() NOT NULL, + data text NOT NULL, + PRIMARY KEY (user_id, mailbox) +); + +CREATE INDEX cache_thread_changed_idx ON cache_thread (changed); + +CREATE TABLE cache_messages ( + user_id integer NOT NULL + REFERENCES users (user_id) ON DELETE CASCADE ON UPDATE CASCADE, + mailbox varchar(255) NOT NULL, + uid integer NOT NULL, + changed timestamp with time zone DEFAULT now() NOT NULL, + data text NOT NULL, + seen smallint NOT NULL DEFAULT 0, + deleted smallint NOT NULL DEFAULT 0, + answered smallint NOT NULL DEFAULT 0, + forwarded smallint NOT NULL DEFAULT 0, + flagged smallint NOT NULL DEFAULT 0, + mdnsent smallint NOT NULL DEFAULT 0, + PRIMARY KEY (user_id, mailbox, uid) +); + +CREATE INDEX cache_messages_changed_idx ON cache_messages (changed); -- cgit v1.2.3