From 80152b333ca5d856dcf09f5ca10a9ffd80ba117f Mon Sep 17 00:00:00 2001
From: alecpl <alec@alec.pl>
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

