[Svn] r5190 - in trunk/roundcubemail: . SQL program/include program/steps/mail

trac at roundcube.net trac at roundcube.net
Wed Sep 7 13:07:03 CEST 2011


Author: alec
Date: 2011-09-07 06:07:03 -0500 (Wed, 07 Sep 2011)
New Revision: 5190

Added:
   trunk/roundcubemail/program/include/rcube_imap_cache.php
Modified:
   trunk/roundcubemail/
   trunk/roundcubemail/CHANGELOG
   trunk/roundcubemail/SQL/mssql.initial.sql
   trunk/roundcubemail/SQL/mssql.upgrade.sql
   trunk/roundcubemail/SQL/mysql.initial.sql
   trunk/roundcubemail/SQL/mysql.update.sql
   trunk/roundcubemail/SQL/postgres.initial.sql
   trunk/roundcubemail/SQL/postgres.update.sql
   trunk/roundcubemail/SQL/sqlite.initial.sql
   trunk/roundcubemail/SQL/sqlite.update.sql
   trunk/roundcubemail/program/include/main.inc
   trunk/roundcubemail/program/include/rcube_imap.php
   trunk/roundcubemail/program/include/rcube_imap_generic.php
   trunk/roundcubemail/program/include/rcube_message.php
   trunk/roundcubemail/program/include/rcube_mime_struct.php
   trunk/roundcubemail/program/steps/mail/func.inc
   trunk/roundcubemail/program/steps/mail/show.inc
Log:
- 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



Property changes on: trunk/roundcubemail
___________________________________________________________________
Modified: svn:mergeinfo
   - /branches/devel-addressbook:4193-4422
/branches/devel-saved-search/roundcubemail:5070-5181
/branches/devel-spellcheck/roundcubemail:5133-5180
/branches/devel-threads:3067-3364
   + /branches/devel-addressbook:4193-4422
/branches/devel-mcache/roundcubemail:5017-5189
/branches/devel-saved-search/roundcubemail:5070-5181
/branches/devel-spellcheck/roundcubemail:5133-5180
/branches/devel-threads:3067-3364

Modified: trunk/roundcubemail/CHANGELOG
===================================================================
--- trunk/roundcubemail/CHANGELOG	2011-09-07 09:29:31 UTC (rev 5189)
+++ trunk/roundcubemail/CHANGELOG	2011-09-07 11:07:03 UTC (rev 5190)
@@ -1,6 +1,13 @@
 CHANGELOG Roundcube Webmail
 ===========================
 
+- Rewritten messages caching:
+  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
 - Use 'From' and 'To' labels instead of 'Sender' and 'Recipient'
 - Fix username case-insensitivity issue in MySQL (#1488021)
 - Addressbook Saved Searches

Modified: trunk/roundcubemail/SQL/mssql.initial.sql
===================================================================
--- trunk/roundcubemail/SQL/mssql.initial.sql	2011-09-07 09:29:31 UTC (rev 5189)
+++ trunk/roundcubemail/SQL/mssql.initial.sql	2011-09-07 11:07:03 UTC (rev 5190)
@@ -7,6 +7,37 @@
 ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
 GO
 
+CREATE TABLE [dbo].[cache_index] (
+	[user_id] [int] NOT NULL ,
+	[mailbox] [varchar] (128) COLLATE Latin1_General_CI_AI NOT NULL ,
+	[changed] [datetime] NOT NULL ,
+	[data] [text] COLLATE Latin1_General_CI_AI NOT NULL 
+) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
+GO
+
+CREATE TABLE [dbo].[cache_thread] (
+	[user_id] [int] NOT NULL ,
+	[mailbox] [varchar] (128) COLLATE Latin1_General_CI_AI NOT NULL ,
+	[changed] [datetime] NOT NULL ,
+	[data] [text] COLLATE Latin1_General_CI_AI NOT NULL 
+) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
+GO
+
+CREATE TABLE [dbo].[cache_messages] (
+	[user_id] [int] NOT NULL ,
+	[mailbox] [varchar] (128) COLLATE Latin1_General_CI_AI NOT NULL ,
+	[uid] [int] NOT NULL ,
+	[changed] [datetime] NOT NULL ,
+	[data] [text] COLLATE Latin1_General_CI_AI NOT NULL 
+	[seen] [char](1) NOT NULL ,
+	[deleted] [char](1) NOT NULL ,
+	[answered] [char](1) NOT NULL ,
+	[forwarded] [char](1) NOT NULL ,
+	[flagged] [char](1) NOT NULL ,
+	[mdnsent] [char](1) NOT NULL ,
+) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
+GO
+
 CREATE TABLE [dbo].[contacts] (
 	[contact_id] [int] IDENTITY (1, 1) NOT NULL ,
 	[user_id] [int] NOT NULL ,
@@ -53,25 +84,6 @@
 ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
 GO
 
-CREATE TABLE [dbo].[messages] (
-	[message_id] [int] IDENTITY (1, 1) NOT NULL ,
-	[user_id] [int] NOT NULL ,
-	[del] [tinyint] NOT NULL ,
-	[cache_key] [varchar] (128) COLLATE Latin1_General_CI_AI NOT NULL ,
-	[created] [datetime] NOT NULL ,
-	[idx] [int] NOT NULL ,
-	[uid] [int] NOT NULL ,
-	[subject] [varchar] (255) COLLATE Latin1_General_CI_AI NOT NULL ,
-	[from] [varchar] (255) COLLATE Latin1_General_CI_AI NOT NULL ,
-	[to] [varchar] (255) COLLATE Latin1_General_CI_AI NOT NULL ,
-	[cc] [varchar] (255) COLLATE Latin1_General_CI_AI NOT NULL ,
-	[date] [datetime] NOT NULL ,
-	[size] [int] NOT NULL ,
-	[headers] [text] COLLATE Latin1_General_CI_AI NOT NULL ,
-	[structure] [text] COLLATE Latin1_General_CI_AI NULL 
-) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
-GO
-
 CREATE TABLE [dbo].[session] (
 	[sess_id] [varchar] (32) COLLATE Latin1_General_CI_AI NOT NULL ,
 	[created] [datetime] NOT NULL ,
@@ -116,6 +128,27 @@
 	)  ON [PRIMARY] 
 GO
 
+ALTER TABLE [dbo].[cache_index] WITH NOCHECK ADD 
+	 PRIMARY KEY CLUSTERED 
+	(
+		[user_id],[mailbox]
+	) ON [PRIMARY] 
+GO
+
+ALTER TABLE [dbo].[cache_thread] WITH NOCHECK ADD 
+	 PRIMARY KEY CLUSTERED 
+	(
+		[user_id],[mailbox]
+	) ON [PRIMARY] 
+GO
+
+ALTER TABLE [dbo].[cache_messages] WITH NOCHECK ADD 
+	 PRIMARY KEY CLUSTERED 
+	(
+		[user_id],[mailbox],[uid]
+	) ON [PRIMARY] 
+GO
+
 ALTER TABLE [dbo].[contacts] WITH NOCHECK ADD 
 	CONSTRAINT [PK_contacts_contact_id] PRIMARY KEY  CLUSTERED 
 	(
@@ -144,13 +177,6 @@
 	)  ON [PRIMARY] 
 GO
 
-ALTER TABLE [dbo].[messages] WITH NOCHECK ADD 
-	 PRIMARY KEY  CLUSTERED 
-	(
-		[message_id]
-	)  ON [PRIMARY] 
-GO
-
 ALTER TABLE [dbo].[session] WITH NOCHECK ADD 
 	CONSTRAINT [PK_session_sess_id] PRIMARY KEY  CLUSTERED 
 	(
@@ -187,6 +213,33 @@
 CREATE  INDEX [IX_cache_created] ON [dbo].[cache]([created]) ON [PRIMARY]
 GO
 
+ALTER TABLE [dbo].[cache_index] ADD 
+	CONSTRAINT [DF_cache_index_changed] DEFAULT (getdate()) FOR [changed]
+GO
+
+CREATE  INDEX [IX_cache_index_user_id] ON [dbo].[cache_index]([user_id]) ON [PRIMARY]
+GO
+
+ALTER TABLE [dbo].[cache_thread] ADD 
+	CONSTRAINT [DF_cache_thread_changed] DEFAULT (getdate()) FOR [changed]
+GO
+
+CREATE  INDEX [IX_cache_thread_user_id] ON [dbo].[cache_thread]([user_id]) ON [PRIMARY]
+GO
+
+ALTER TABLE [dbo].[cache_messages] ADD 
+	CONSTRAINT [DF_cache_messages_changed] DEFAULT (getdate()) FOR [changed]
+	CONSTRAINT [DF_cache_messages_seen] DEFAULT (0) FOR [seen],
+	CONSTRAINT [DF_cache_messages_deleted] DEFAULT (0) FOR [deleted],
+	CONSTRAINT [DF_cache_messages_answered] DEFAULT (0) FOR [answered],
+	CONSTRAINT [DF_cache_messages_forwarded] DEFAULT (0) FOR [forwarded],
+	CONSTRAINT [DF_cache_messages_flagged] DEFAULT (0) FOR [flagged],
+	CONSTRAINT [DF_cache_messages_mdnsent] DEFAULT (0) FOR [mdnsent],
+GO
+
+CREATE  INDEX [IX_cache_messages_user_id] ON [dbo].[cache_messages]([user_id]) ON [PRIMARY]
+GO
+
 ALTER TABLE [dbo].[contacts] ADD 
 	CONSTRAINT [DF_contacts_user_id] DEFAULT (0) FOR [user_id],
 	CONSTRAINT [DF_contacts_changed] DEFAULT (getdate()) FOR [changed],
@@ -238,33 +291,6 @@
 CREATE  INDEX [IX_identities_user_id] ON [dbo].[identities]([user_id]) ON [PRIMARY]
 GO
 
-ALTER TABLE [dbo].[messages] ADD 
-	CONSTRAINT [DF_messages_user_id] DEFAULT (0) FOR [user_id],
-	CONSTRAINT [DF_messages_del] DEFAULT (0) FOR [del],
-	CONSTRAINT [DF_messages_cache_key] DEFAULT ('') FOR [cache_key],
-	CONSTRAINT [DF_messages_created] DEFAULT (getdate()) FOR [created],
-	CONSTRAINT [DF_messages_idx] DEFAULT (0) FOR [idx],
-	CONSTRAINT [DF_messages_uid] DEFAULT (0) FOR [uid],
-	CONSTRAINT [DF_messages_subject] DEFAULT ('') FOR [subject],
-	CONSTRAINT [DF_messages_from] DEFAULT ('') FOR [from],
-	CONSTRAINT [DF_messages_to] DEFAULT ('') FOR [to],
-	CONSTRAINT [DF_messages_cc] DEFAULT ('') FOR [cc],
-	CONSTRAINT [DF_messages_date] DEFAULT (getdate()) FOR [date],
-	CONSTRAINT [DF_messages_size] DEFAULT (0) FOR [size]
-GO
-
-CREATE  INDEX [IX_messages_user_id] ON [dbo].[messages]([user_id]) ON [PRIMARY]
-GO
-
-CREATE  INDEX [IX_messages_cache_key] ON [dbo].[messages]([cache_key]) ON [PRIMARY]
-GO
-
-CREATE  INDEX [IX_messages_uid] ON [dbo].[messages]([uid]) ON [PRIMARY]
-GO
-
-CREATE  INDEX [IX_messages_created] ON [dbo].[messages]([created]) ON [PRIMARY]
-GO
-
 ALTER TABLE [dbo].[session] ADD 
 	CONSTRAINT [DF_session_sess_id] DEFAULT ('') FOR [sess_id],
 	CONSTRAINT [DF_session_created] DEFAULT (getdate()) FOR [created],
@@ -318,11 +344,21 @@
     ON DELETE CASCADE ON UPDATE CASCADE
 GO
 
-ALTER TABLE [dbo].[messages] ADD CONSTRAINT [FK_messages_user_id]
+ALTER TABLE [dbo].[cache_index] ADD CONSTRAINT [FK_cache_index_user_id]
     FOREIGN KEY ([user_id]) REFERENCES [dbo].[users] ([user_id])
     ON DELETE CASCADE ON UPDATE CASCADE
 GO
 
+ALTER TABLE [dbo].[cache_thread] ADD CONSTRAINT [FK_cache_thread_user_id]
+    FOREIGN KEY ([user_id]) REFERENCES [dbo].[users] ([user_id])
+    ON DELETE CASCADE ON UPDATE CASCADE
+GO
+
+ALTER TABLE [dbo].[cache_messages] ADD CONSTRAINT [FK_cache_messages_user_id]
+    FOREIGN KEY ([user_id]) REFERENCES [dbo].[users] ([user_id])
+    ON DELETE CASCADE ON UPDATE CASCADE
+GO
+
 ALTER TABLE [dbo].[contactgroupmembers] ADD CONSTRAINT [FK_contactgroupmembers_contactgroup_id]
     FOREIGN KEY ([contactgroup_id]) REFERENCES [dbo].[contactgroups] ([contactgroup_id])
     ON DELETE CASCADE ON UPDATE CASCADE

Modified: trunk/roundcubemail/SQL/mssql.upgrade.sql
===================================================================
--- trunk/roundcubemail/SQL/mssql.upgrade.sql	2011-09-07 09:29:31 UTC (rev 5189)
+++ trunk/roundcubemail/SQL/mssql.upgrade.sql	2011-09-07 11:07:03 UTC (rev 5190)
@@ -151,3 +151,99 @@
     ON DELETE CASCADE ON UPDATE CASCADE
 GO
 
+DROP TABLE [dbo].[messages]
+GO
+CREATE TABLE [dbo].[cache_index] (
+	[user_id] [int] NOT NULL ,
+	[mailbox] [varchar] (128) COLLATE Latin1_General_CI_AI NOT NULL ,
+	[changed] [datetime] NOT NULL ,
+	[data] [text] COLLATE Latin1_General_CI_AI NOT NULL 
+) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
+GO
+
+CREATE TABLE [dbo].[cache_thread] (
+	[user_id] [int] NOT NULL ,
+	[mailbox] [varchar] (128) COLLATE Latin1_General_CI_AI NOT NULL ,
+	[changed] [datetime] NOT NULL ,
+	[data] [text] COLLATE Latin1_General_CI_AI NOT NULL 
+) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
+GO
+
+CREATE TABLE [dbo].[cache_messages] (
+	[user_id] [int] NOT NULL ,
+	[mailbox] [varchar] (128) COLLATE Latin1_General_CI_AI NOT NULL ,
+	[uid] [int] NOT NULL ,
+	[changed] [datetime] NOT NULL ,
+	[data] [text] COLLATE Latin1_General_CI_AI NOT NULL 
+	[seen] [char](1) NOT NULL ,
+	[deleted] [char](1) NOT NULL ,
+	[answered] [char](1) NOT NULL ,
+	[forwarded] [char](1) NOT NULL ,
+	[flagged] [char](1) NOT NULL ,
+	[mdnsent] [char](1) NOT NULL ,
+) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
+GO
+
+ALTER TABLE [dbo].[cache_index] WITH NOCHECK ADD 
+	 PRIMARY KEY CLUSTERED 
+	(
+		[user_id],[mailbox]
+	) ON [PRIMARY] 
+GO
+
+ALTER TABLE [dbo].[cache_thread] WITH NOCHECK ADD 
+	 PRIMARY KEY CLUSTERED 
+	(
+		[user_id],[mailbox]
+	) ON [PRIMARY] 
+GO
+
+ALTER TABLE [dbo].[cache_messages] WITH NOCHECK ADD 
+	 PRIMARY KEY CLUSTERED 
+	(
+		[user_id],[mailbox],[uid]
+	) ON [PRIMARY] 
+GO
+
+ALTER TABLE [dbo].[cache_index] ADD 
+	CONSTRAINT [DF_cache_index_changed] DEFAULT (getdate()) FOR [changed]
+GO
+
+CREATE  INDEX [IX_cache_index_user_id] ON [dbo].[cache_index]([user_id]) ON [PRIMARY]
+GO
+
+ALTER TABLE [dbo].[cache_thread] ADD 
+	CONSTRAINT [DF_cache_thread_changed] DEFAULT (getdate()) FOR [changed]
+GO
+
+CREATE  INDEX [IX_cache_thread_user_id] ON [dbo].[cache_thread]([user_id]) ON [PRIMARY]
+GO
+
+ALTER TABLE [dbo].[cache_messages] ADD 
+	CONSTRAINT [DF_cache_messages_changed] DEFAULT (getdate()) FOR [changed]
+	CONSTRAINT [DF_cache_messages_seen] DEFAULT (0) FOR [seen],
+	CONSTRAINT [DF_cache_messages_deleted] DEFAULT (0) FOR [deleted],
+	CONSTRAINT [DF_cache_messages_answered] DEFAULT (0) FOR [answered],
+	CONSTRAINT [DF_cache_messages_forwarded] DEFAULT (0) FOR [forwarded],
+	CONSTRAINT [DF_cache_messages_flagged] DEFAULT (0) FOR [flagged],
+	CONSTRAINT [DF_cache_messages_mdnsent] DEFAULT (0) FOR [mdnsent],
+GO
+
+CREATE  INDEX [IX_cache_messages_user_id] ON [dbo].[cache_messages]([user_id]) ON [PRIMARY]
+GO
+
+ALTER TABLE [dbo].[cache_index] ADD CONSTRAINT [FK_cache_index_user_id]
+    FOREIGN KEY ([user_id]) REFERENCES [dbo].[users] ([user_id])
+    ON DELETE CASCADE ON UPDATE CASCADE
+GO
+
+ALTER TABLE [dbo].[cache_thread] ADD CONSTRAINT [FK_cache_thread_user_id]
+    FOREIGN KEY ([user_id]) REFERENCES [dbo].[users] ([user_id])
+    ON DELETE CASCADE ON UPDATE CASCADE
+GO
+
+ALTER TABLE [dbo].[cache_messages] ADD CONSTRAINT [FK_cache_messages_user_id]
+    FOREIGN KEY ([user_id]) REFERENCES [dbo].[users] ([user_id])
+    ON DELETE CASCADE ON UPDATE CASCADE
+GO
+
Modified: trunk/roundcubemail/SQL/mysql.initial.sql
===================================================================
--- trunk/roundcubemail/SQL/mysql.initial.sql	2011-09-07 09:29:31 UTC (rev 5189)
+++ trunk/roundcubemail/SQL/mysql.initial.sql	2011-09-07 11:07:03 UTC (rev 5190)
@@ -33,33 +33,6 @@
 ) /*!40000 ENGINE=INNODB */ /*!40101 CHARACTER SET utf8 COLLATE utf8_general_ci */;
 
 
--- Table structure for table `messages`
-
-CREATE TABLE `messages` (
- `message_id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
- `user_id` int(10) UNSIGNED NOT NULL DEFAULT '0',
- `del` tinyint(1) NOT NULL DEFAULT '0',
- `cache_key` varchar(128) /*!40101 CHARACTER SET ascii COLLATE ascii_general_ci */ NOT NULL,
- `created` datetime NOT NULL DEFAULT '1000-01-01 00:00:00',
- `idx` int(11) UNSIGNED NOT NULL DEFAULT '0',
- `uid` int(11) UNSIGNED NOT NULL DEFAULT '0',
- `subject` varchar(255) NOT NULL,
- `from` varchar(255) NOT NULL,
- `to` varchar(255) NOT NULL,
- `cc` varchar(255) NOT NULL,
- `date` datetime NOT NULL DEFAULT '1000-01-01 00:00:00',
- `size` int(11) UNSIGNED NOT NULL DEFAULT '0',
- `headers` text NOT NULL,
- `structure` text,
- PRIMARY KEY(`message_id`),
- CONSTRAINT `user_id_fk_messages` FOREIGN KEY (`user_id`)
-   REFERENCES `users`(`user_id`) ON DELETE CASCADE ON UPDATE CASCADE,
- INDEX `created_index` (`created`),
- INDEX `index_index` (`user_id`, `cache_key`, `idx`),
- UNIQUE `uniqueness` (`user_id`, `cache_key`, `uid`)
-) /*!40000 ENGINE=INNODB */ /*!40101 CHARACTER SET utf8 COLLATE utf8_general_ci */;
-
-
 -- Table structure for table `cache`
 
 CREATE TABLE `cache` (
@@ -76,6 +49,55 @@
 ) /*!40000 ENGINE=INNODB */ /*!40101 CHARACTER SET utf8 COLLATE utf8_general_ci */;
 
 
+-- Table structure for table `cache_index`
+
+CREATE TABLE `cache_index` (
+ `user_id` int(10) UNSIGNED NOT NULL DEFAULT '0',
+ `mailbox` varchar(255) BINARY NOT NULL,
+ `changed` datetime NOT NULL DEFAULT '1000-01-01 00:00:00',
+ `data` longtext NOT NULL,
+ CONSTRAINT `user_id_fk_cache_index` FOREIGN KEY (`user_id`)
+   REFERE