Fixed redundancy in sql caching system and compatibility with Galera Cluster (#5439)

- Removed redundant 'created' column from cache and cache_shared tables
- Removed use of redundant data records
- Added missing primary keys (dictionary, cache, cache_shared tables)

11 files changed, 162 insertions, 43 deletions

diff --git a/SQL/mssql.initial.sql b/SQL/mssql.initial.sql
index e0f876348..219aaa775 100644
--- a/SQL/mssql.initial.sql
+++ b/SQL/mssql.initial.sql
@@ -8,7 +8,6 @@ GO
 

 CREATE TABLE [dbo].[cache_shared] (

 	[cache_key] [varchar] (255) COLLATE Latin1_General_CI_AI NOT NULL ,

-	[created] [datetime] NOT NULL ,

 	[expires] [datetime] NULL ,

 	[data] [text] COLLATE Latin1_General_CI_AI NOT NULL 

 ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

@@ -131,6 +130,20 @@ CREATE TABLE [dbo].[system] (
 ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

 GO

 

+ALTER TABLE [dbo].[cache] WITH NOCHECK ADD 

+	 PRIMARY KEY CLUSTERED 

+	(

+		[user_id],[cache_key]

+	) ON [PRIMARY] 

+GO

+

+ALTER TABLE [dbo].[cache_shared] WITH NOCHECK ADD 

+	 PRIMARY KEY CLUSTERED 

+	(

+		[cache_key]

+	) ON [PRIMARY] 

+GO

+

 ALTER TABLE [dbo].[cache_index] WITH NOCHECK ADD 

 	 PRIMARY KEY CLUSTERED 

 	(

@@ -211,11 +224,6 @@ GO
 ALTER TABLE [dbo].[cache] ADD 

 	CONSTRAINT [DF_cache_user_id] DEFAULT ('0') FOR [user_id],

 	CONSTRAINT [DF_cache_cache_key] DEFAULT ('') FOR [cache_key],

-	CONSTRAINT [DF_cache_created] DEFAULT (getdate()) FOR [created]

-GO

-

-ALTER TABLE [dbo].[cache_shared] ADD 

-	CONSTRAINT [DF_cache_shared_created] DEFAULT (getdate()) FOR [created]

 GO

 

 ALTER TABLE [dbo].[cache_index] ADD 

@@ -226,12 +234,6 @@ ALTER TABLE [dbo].[cache_messages] ADD
 	CONSTRAINT [DF_cache_messages_flags] DEFAULT (0) FOR [flags]

 GO

 

-CREATE INDEX [IX_cache_user_id] ON [dbo].[cache]([user_id]) ON [PRIMARY]

-GO

-

-CREATE INDEX [IX_cache_cache_key] ON [dbo].[cache]([cache_key]) ON [PRIMARY]

-GO

-

 CREATE INDEX [IX_cache_shared_cache_key] ON [dbo].[cache_shared]([cache_key]) ON [PRIMARY]

 GO

 

@@ -393,6 +395,6 @@ CREATE TRIGGER [contact_delete_member] ON [dbo].[contacts]
     WHERE [contact_id] IN (SELECT [contact_id] FROM deleted)

 GO

 

-INSERT INTO [dbo].[system] ([name], [value]) VALUES ('roundcube-version', '2016100900')

+INSERT INTO [dbo].[system] ([name], [value]) VALUES ('roundcube-version', '2016112200')

 GO

 
\ No newline at end of file
diff --git a/SQL/mssql/2016112200.sql b/SQL/mssql/2016112200.sql
new file mode 100644
index 000000000..14d29b448
--- /dev/null
+++ b/SQL/mssql/2016112200.sql
@@ -0,0 +1,36 @@
+DROP TABLE [dbo].[cache]
+GO
+DROP TABLE [dbo].[cache_shared]
+GO
+
+CREATE TABLE [dbo].[cache] (
+  [user_id] [int] NOT NULL ,
+  [cache_key] [varchar] (128) COLLATE Latin1_General_CI_AI NOT NULL ,
+  [expires] [datetime] NULL ,
+  [data] [text] COLLATE Latin1_General_CI_AI NOT NULL 
+) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
+GO
+CREATE TABLE [dbo].[cache_shared] (
+  [cache_key] [varchar] (255) COLLATE Latin1_General_CI_AI NOT NULL ,
+  [expires] [datetime] NULL ,
+  [data] [text] COLLATE Latin1_General_CI_AI NOT NULL
+) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
+GO
+ALTER TABLE [dbo].[cache] ADD
+  CONSTRAINT [DF_cache_user_id] DEFAULT ('0') FOR [user_id],
+  CONSTRAINT [DF_cache_cache_key] DEFAULT ('') FOR [cache_key],
+GO
+CREATE INDEX [IX_cache_expires] ON [dbo].[cache]([expires]) ON [PRIMARY]
+GO
+CREATE INDEX [IX_cache_shared_expires] ON [dbo].[cache_shared]([expires]) ON [PRIMARY]
+GO
+ALTER TABLE [dbo].[cache] WITH NOCHECK ADD
+  PRIMARY KEY CLUSTERED (
+    [user_id],[cache_key]
+  ) ON [PRIMARY]
+GO
+ALTER TABLE [dbo].[cache_shared] WITH NOCHECK ADD
+  PRIMARY KEY CLUSTERED (
+    [cache_key]
+  ) ON [PRIMARY]
+GO
diff --git a/SQL/mysql.initial.sql b/SQL/mysql.initial.sql
index cb2e75f26..c3ce70746 100644
--- a/SQL/mysql.initial.sql
+++ b/SQL/mysql.initial.sql
@@ -37,13 +37,12 @@ CREATE TABLE `users` (
 CREATE TABLE `cache` (
  `user_id` int(10) UNSIGNED NOT NULL,
  `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',
  `expires` datetime DEFAULT NULL,
  `data` longtext NOT NULL,
+ PRIMARY KEY (`user_id`, `cache_key`),
  CONSTRAINT `user_id_fk_cache` FOREIGN KEY (`user_id`)
    REFERENCES `users`(`user_id`) ON DELETE CASCADE ON UPDATE CASCADE,
- INDEX `expires_index` (`expires`),
- INDEX `user_cache_index` (`user_id`,`cache_key`)
+ INDEX `expires_index` (`expires`)
 ) /*!40000 ENGINE=INNODB */ /*!40101 CHARACTER SET utf8 COLLATE utf8_general_ci */;
 
 
@@ -51,11 +50,10 @@ CREATE TABLE `cache` (
 
 CREATE TABLE `cache_shared` (
  `cache_key` varchar(255) /*!40101 CHARACTER SET ascii COLLATE ascii_general_ci */ NOT NULL,
- `created` datetime NOT NULL DEFAULT '1000-01-01 00:00:00',
  `expires` datetime DEFAULT NULL,
  `data` longtext NOT NULL,
- INDEX `expires_index` (`expires`),
- INDEX `cache_key_index` (`cache_key`)
+ PRIMARY KEY (`cache_key`),
+ INDEX `expires_index` (`expires`)
 ) /*!40000 ENGINE=INNODB */ /*!40101 CHARACTER SET utf8 COLLATE utf8_general_ci */;
 
 
@@ -176,7 +174,8 @@ CREATE TABLE `identities` (
 -- Table structure for table `dictionary`
 
 CREATE TABLE `dictionary` (
-  `user_id` int(10) UNSIGNED DEFAULT NULL,
+  `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, -- redundant, for compat. with Galera Cluster
+  `user_id` int(10) UNSIGNED DEFAULT NULL, -- NULL here is for "shared dictionaries"
   `language` varchar(5) NOT NULL,
   `data` longtext NOT NULL,
   CONSTRAINT `user_id_fk_dictionary` FOREIGN KEY (`user_id`)
@@ -210,4 +209,4 @@ CREATE TABLE `system` (
 
 /*!40014 SET FOREIGN_KEY_CHECKS=1 */;
 
-INSERT INTO system (name, value) VALUES ('roundcube-version', '2016100900');
+INSERT INTO system (name, value) VALUES ('roundcube-version', '2016112200');
diff --git a/SQL/mysql/2016112200.sql b/SQL/mysql/2016112200.sql
new file mode 100644
index 000000000..62c974946
--- /dev/null
+++ b/SQL/mysql/2016112200.sql
@@ -0,0 +1,24 @@
+ALTER TABLE `dictionary` ADD COLUMN `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST; -- redundant, for compat. with Galera Cluster
+
+DROP TABLE `cache`;
+DROP TABLE `cache_shared`;
+
+CREATE TABLE `cache` (
+ `user_id` int(10) UNSIGNED NOT NULL,
+ `cache_key` varchar(128) /*!40101 CHARACTER SET ascii COLLATE ascii_general_ci */ NOT NULL,
+ `expires` datetime DEFAULT NULL,
+ `data` longtext NOT NULL,
+ PRIMARY KEY (`user_id`, `cache_key`),
+ CONSTRAINT `user_id_fk_cache` FOREIGN KEY (`user_id`)
+   REFERENCES `users`(`user_id`) ON DELETE CASCADE ON UPDATE CASCADE,
+ INDEX `expires_index` (`expires`)
+) /*!40000 ENGINE=INNODB */ /*!40101 CHARACTER SET utf8 COLLATE utf8_general_ci */;
+
+
+CREATE TABLE `cache_shared` (
+ `cache_key` varchar(255) /*!40101 CHARACTER SET ascii COLLATE ascii_general_ci */ NOT NULL,
+ `expires` datetime DEFAULT NULL,
+ `data` longtext NOT NULL,
+ PRIMARY KEY (`cache_key`),
+ INDEX `expires_index` (`expires`)
+) /*!40000 ENGINE=INNODB */ /*!40101 CHARACTER SET utf8 COLLATE utf8_general_ci */;
diff --git a/SQL/oracle.initial.sql b/SQL/oracle.initial.sql
index 668b71333..164ae7131 100644
--- a/SQL/oracle.initial.sql
+++ b/SQL/oracle.initial.sql
@@ -126,23 +126,21 @@ CREATE TABLE "cache" (
     "user_id" integer NOT NULL
         REFERENCES "users" ("user_id") ON DELETE CASCADE,
     "cache_key" varchar(128) NOT NULL,
-    "created" timestamp with time zone DEFAULT current_timestamp NOT NULL,
     "expires" timestamp with time zone DEFAULT NULL,
-    "data" long NOT NULL
+    "data" long NOT NULL,
+    PRIMARY KEY ("user_id", "cache_key")
 );
 
-CREATE INDEX "cache_user_id_idx" ON "cache" ("user_id", "cache_key");
 CREATE INDEX "cache_expires_idx" ON "cache" ("expires");
 
 
 CREATE TABLE "cache_shared" (
     "cache_key" varchar(255) NOT NULL,
-    "created" timestamp with time zone DEFAULT current_timestamp NOT NULL,
     "expires" timestamp with time zone DEFAULT NULL,
-    "data" long NOT NULL
+    "data" long NOT NULL,
+    PRIMARY KEY ("cache_key")
 );
 
-CREATE INDEX "cache_shared_cache_key_idx" ON "cache_shared" ("cache_key");
 CREATE INDEX "cache_shared_expires_idx" ON "cache_shared" ("expires");
 
 
@@ -219,4 +217,4 @@ CREATE TABLE "system" (
     "value" long
 );
 
-INSERT INTO "system" ("name", "value") VALUES ('roundcube-version', '2016100900');
+INSERT INTO "system" ("name", "value") VALUES ('roundcube-version', '2016112200');
diff --git a/SQL/oracle/2016100900.sql b/SQL/oracle/2016100900.sql
index e92e7eb91..030f169e3 100644
--- a/SQL/oracle/2016100900.sql
+++ b/SQL/oracle/2016100900.sql
@@ -1 +1 @@
-ALTER TABLE session MODIFY ip varchar(41) NOT NULL;
+ALTER TABLE "session" MODIFY "ip" varchar(41) NOT NULL;
diff --git a/SQL/oracle/2016112200.sql b/SQL/oracle/2016112200.sql
new file mode 100644
index 000000000..3aab26510
--- /dev/null
+++ b/SQL/oracle/2016112200.sql
@@ -0,0 +1,23 @@
+DROP TABLE "cache";
+DROP TABLE "cache_shared";
+
+CREATE TABLE "cache" (
+    "user_id" integer NOT NULL
+        REFERENCES "users" ("user_id") ON DELETE CASCADE,
+    "cache_key" varchar(128) NOT NULL,
+    "expires" timestamp with time zone DEFAULT NULL,
+    "data" long NOT NULL,
+    PRIMARY KEY ("user_id", "cache_key")
+);
+
+CREATE INDEX "cache_expires_idx" ON "cache" ("expires");
+
+
+CREATE TABLE "cache_shared" (
+    "cache_key" varchar(255) NOT NULL,
+    "expires" timestamp with time zone DEFAULT NULL,
+    "data" long NOT NULL,
+    PRIMARY KEY ("cache_key")
+);
+
+CREATE INDEX "cache_shared_expires_idx" ON "cache_shared" ("expires");
diff --git a/SQL/postgres.initial.sql b/SQL/postgres.initial.sql
index 965bc54bd..135499698 100644
--- a/SQL/postgres.initial.sql
+++ b/SQL/postgres.initial.sql
@@ -167,12 +167,11 @@ CREATE TABLE "cache" (
     user_id integer NOT NULL
         REFERENCES users (user_id) ON DELETE CASCADE ON UPDATE CASCADE,
     cache_key varchar(128) DEFAULT '' NOT NULL,
-    created timestamp with time zone DEFAULT now() NOT NULL,
     expires timestamp with time zone DEFAULT NULL,
-    data text NOT NULL
+    data text NOT NULL,
+    PRIMARY KEY (user_id, cache_key)
 );
 
-CREATE INDEX cache_user_id_idx ON "cache" (user_id, cache_key);
 CREATE INDEX cache_expires_idx ON "cache" (expires);
 
 --
@@ -181,13 +180,11 @@ CREATE INDEX cache_expires_idx ON "cache" (expires);
 --
 
 CREATE TABLE "cache_shared" (
-    cache_key varchar(255) NOT NULL,
-    created timestamp with time zone DEFAULT now() NOT NULL,
+    cache_key varchar(255) NOT NULL PRIMARY KEY,
     expires timestamp with time zone DEFAULT NULL,
     data text NOT NULL
 );
 
-CREATE INDEX cache_shared_cache_key_idx ON "cache_shared" (cache_key);
 CREATE INDEX cache_shared_expires_idx ON "cache_shared" (expires);
 
 --
@@ -291,4 +288,4 @@ CREATE TABLE "system" (
     value text
 );
 
-INSERT INTO system (name, value) VALUES ('roundcube-version', '2016100900');
+INSERT INTO system (name, value) VALUES ('roundcube-version', '2016112200');
diff --git a/SQL/postgres/2016112200.sql b/SQL/postgres/2016112200.sql
new file mode 100644
index 000000000..34af9712c
--- /dev/null
+++ b/SQL/postgres/2016112200.sql
@@ -0,0 +1,21 @@
+DROP TABLE "cache";
+DROP TABLE "cache_shared";
+
+CREATE TABLE "cache" (
+    user_id integer NOT NULL
+        REFERENCES users (user_id) ON DELETE CASCADE ON UPDATE CASCADE,
+    cache_key varchar(128) DEFAULT '' NOT NULL,
+    expires timestamp with time zone DEFAULT NULL,
+    data text NOT NULL,
+    PRIMARY KEY (user_id, cache_key)
+);
+
+CREATE INDEX cache_expires_idx ON "cache" (expires);
+
+CREATE TABLE "cache_shared" (
+    cache_key varchar(255) NOT NULL PRIMARY KEY,
+    expires timestamp with time zone DEFAULT NULL,
+    data text NOT NULL
+);
+
+CREATE INDEX cache_shared_expires_idx ON "cache_shared" (expires);
diff --git a/SQL/sqlite.initial.sql b/SQL/sqlite.initial.sql
index 83d688133..03dd432e2 100644
--- a/SQL/sqlite.initial.sql
+++ b/SQL/sqlite.initial.sql
@@ -126,12 +126,11 @@ CREATE UNIQUE INDEX ix_searches_user_type_name ON searches (user_id, type, name)
 CREATE TABLE cache (
   user_id integer NOT NULL default 0,
   cache_key varchar(128) NOT NULL default '',
-  created datetime NOT NULL default '0000-00-00 00:00:00',
   expires datetime DEFAULT NULL,
-  data text NOT NULL
+  data text NOT NULL,
+  PRIMARY KEY (user_id, cache_key)
 );
 
-CREATE INDEX ix_cache_user_cache_key ON cache(user_id, cache_key);
 CREATE INDEX ix_cache_expires ON cache(expires);
 
 -- 
@@ -140,12 +139,11 @@ CREATE INDEX ix_cache_expires ON cache(expires);
 
 CREATE TABLE cache_shared (
   cache_key varchar(255) NOT NULL,
-  created datetime NOT NULL default '0000-00-00 00:00:00',
   expires datetime DEFAULT NULL,
-  data text NOT NULL
+  data text NOT NULL,
+  PRIMARY KEY (cache_key)
 );
 
-CREATE INDEX ix_cache_shared_cache_key ON cache_shared(cache_key);
 CREATE INDEX ix_cache_shared_expires ON cache_shared(expires);
 
 --
@@ -202,4 +200,4 @@ CREATE TABLE system (
   value text NOT NULL
 );
 
-INSERT INTO system (name, value) VALUES ('roundcube-version', '2016100900');
+INSERT INTO system (name, value) VALUES ('roundcube-version', '2016112200');
diff --git a/SQL/sqlite/2016112200.sql b/SQL/sqlite/2016112200.sql
new file mode 100644
index 000000000..c185493ad
--- /dev/null
+++ b/SQL/sqlite/2016112200.sql
@@ -0,0 +1,21 @@
+DROP TABLE cache;
+DROP TABLE cache_shared;
+
+CREATE TABLE cache (
+  user_id integer NOT NULL default 0,
+  cache_key varchar(128) NOT NULL default '',
+  expires datetime DEFAULT NULL,
+  data text NOT NULL,
+  PRIMARY KEY (user_id, cache_key)
+);
+
+CREATE INDEX ix_cache_expires ON cache(expires);
+
+CREATE TABLE cache_shared (
+  cache_key varchar(255) NOT NULL,
+  expires datetime DEFAULT NULL,
+  data text NOT NULL,
+  PRIMARY KEY (cache_key)
+);
+
+CREATE INDEX ix_cache_shared_expires ON cache_shared(expires);