summaryrefslogtreecommitdiff
path: root/SQL
diff options
context:
space:
mode:
Diffstat (limited to 'SQL')
-rw-r--r--SQL/mssql.initial.sql54
-rw-r--r--SQL/mssql/2013042700.sql1
-rw-r--r--SQL/mssql/2013052500.sql17
-rw-r--r--SQL/mssql/2013061000.sql44
-rw-r--r--SQL/mysql.initial.sql31
-rw-r--r--SQL/mysql/2013011000.sql2
-rw-r--r--SQL/mysql/2013042700.sql1
-rw-r--r--SQL/mysql/2013052500.sql7
-rw-r--r--SQL/mysql/2013061000.sql24
-rw-r--r--SQL/postgres.initial.sql82
-rw-r--r--SQL/postgres/2013042700.sql14
-rw-r--r--SQL/postgres/2013052500.sql8
-rw-r--r--SQL/postgres/2013061000.sql24
-rw-r--r--SQL/sqlite.initial.sql78
-rw-r--r--SQL/sqlite/2013011000.sql2
-rw-r--r--SQL/sqlite/2013042700.sql1
-rw-r--r--SQL/sqlite/2013052500.sql8
-rw-r--r--SQL/sqlite/2013061000.sql48
18 files changed, 343 insertions, 103 deletions
diff --git a/SQL/mssql.initial.sql b/SQL/mssql.initial.sql
index 694fe7efb..1027867e3 100644
--- a/SQL/mssql.initial.sql
+++ b/SQL/mssql.initial.sql
@@ -2,6 +2,15 @@ CREATE TABLE [dbo].[cache] (
[user_id] [int] NOT NULL ,
[cache_key] [varchar] (128) 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]
+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]
GO
@@ -9,7 +18,7 @@ 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 ,
+ [expires] [datetime] NULL ,
[valid] [char] (1) COLLATE Latin1_General_CI_AI NOT NULL ,
[data] [text] COLLATE Latin1_General_CI_AI NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
@@ -18,7 +27,7 @@ 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 ,
+ [expires] [datetime] NULL ,
[data] [text] COLLATE Latin1_General_CI_AI NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
@@ -27,7 +36,7 @@ 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 ,
+ [expires] [datetime] NULL ,
[data] [text] COLLATE Latin1_General_CI_AI NOT NULL ,
[flags] [int] NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
@@ -204,36 +213,49 @@ ALTER TABLE [dbo].[cache] ADD
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
+ CONSTRAINT [DF_cache_index_valid] DEFAULT ('0') FOR [valid]
+GO
+
+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_created] ON [dbo].[cache]([created]) ON [PRIMARY]
+CREATE INDEX [IX_cache_shared_cache_key] ON [dbo].[cache_shared]([cache_key]) ON [PRIMARY]
GO
-ALTER TABLE [dbo].[cache_index] ADD
- CONSTRAINT [DF_cache_index_changed] DEFAULT (getdate()) FOR [changed],
- CONSTRAINT [DF_cache_index_valid] DEFAULT ('0') FOR [valid]
+CREATE INDEX [IX_cache_index_user_id] ON [dbo].[cache_index]([user_id]) ON [PRIMARY]
GO
-CREATE INDEX [IX_cache_index_user_id] ON [dbo].[cache_index]([user_id]) ON [PRIMARY]
+CREATE INDEX [IX_cache_thread_user_id] ON [dbo].[cache_thread]([user_id]) ON [PRIMARY]
+GO
+
+CREATE INDEX [IX_cache_messages_user_id] ON [dbo].[cache_messages]([user_id]) ON [PRIMARY]
GO
-ALTER TABLE [dbo].[cache_thread] ADD
- CONSTRAINT [DF_cache_thread_changed] DEFAULT (getdate()) FOR [changed]
+CREATE INDEX [IX_cache_expires] ON [dbo].[cache]([expires]) ON [PRIMARY]
GO
-CREATE INDEX [IX_cache_thread_user_id] ON [dbo].[cache_thread]([user_id]) ON [PRIMARY]
+CREATE INDEX [IX_cache_shared_expires] ON [dbo].[cache_shared]([expires]) ON [PRIMARY]
GO
-ALTER TABLE [dbo].[cache_messages] ADD
- CONSTRAINT [DF_cache_messages_changed] DEFAULT (getdate()) FOR [changed],
- CONSTRAINT [DF_cache_messages_flags] DEFAULT (0) FOR [flags]
+CREATE INDEX [IX_cache_index_expires] ON [dbo].[cache_index]([expires]) ON [PRIMARY]
GO
-CREATE INDEX [IX_cache_messages_user_id] ON [dbo].[cache_messages]([user_id]) ON [PRIMARY]
+CREATE INDEX [IX_cache_thread_expires] ON [dbo].[cache_thread]([expires]) ON [PRIMARY]
+GO
+
+CREATE INDEX [IX_cache_messages_expires] ON [dbo].[cache_messages]([expires]) ON [PRIMARY]
GO
ALTER TABLE [dbo].[contacts] ADD
@@ -371,6 +393,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', '2013011700.sql')
+INSERT INTO [dbo].[system] ([name], [value]) VALUES ('roundcube-version', '2013061000')
GO
\ No newline at end of file
diff --git a/SQL/mssql/2013042700.sql b/SQL/mssql/2013042700.sql
new file mode 100644
index 000000000..fe6741a02
--- /dev/null
+++ b/SQL/mssql/2013042700.sql
@@ -0,0 +1 @@
+-- empty \ No newline at end of file
diff --git a/SQL/mssql/2013052500.sql b/SQL/mssql/2013052500.sql
new file mode 100644
index 000000000..6a7e31d46
--- /dev/null
+++ b/SQL/mssql/2013052500.sql
@@ -0,0 +1,17 @@
+CREATE TABLE [dbo].[cache_shared] (
+ [cache_key] [varchar] (255) COLLATE Latin1_General_CI_AI NOT NULL ,
+ [created] [datetime] NOT NULL ,
+ [data] [text] COLLATE Latin1_General_CI_AI NOT NULL
+) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
+GO
+
+ALTER TABLE [dbo].[cache_shared] ADD
+ CONSTRAINT [DF_cache_shared_created] DEFAULT (getdate()) FOR [created]
+GO
+
+CREATE INDEX [IX_cache_shared_cache_key] ON [dbo].[cache_shared]([cache_key]) ON [PRIMARY]
+GO
+
+CREATE INDEX [IX_cache_shared_created] ON [dbo].[cache_shared]([created]) ON [PRIMARY]
+GO
+
diff --git a/SQL/mssql/2013061000.sql b/SQL/mssql/2013061000.sql
new file mode 100644
index 000000000..55b5ec3a5
--- /dev/null
+++ b/SQL/mssql/2013061000.sql
@@ -0,0 +1,44 @@
+ALTER TABLE [dbo].[cache] ADD COLUMN [expires] [datetime] NULL
+GO
+ALTER TABLE [dbo].[cache_shared] ADD COLUMN [expires] [datetime] NULL
+GO
+ALTER TABLE [dbo].[cache_index] ADD COLUMN [expires] [datetime] NULL
+GO
+ALTER TABLE [dbo].[cache_thread] ADD COLUMN [expires] [datetime] NULL
+GO
+ALTER TABLE [dbo].[cache_messages] ADD COLUMN [expires] [datetime] NULL
+GO
+
+UPDATE [dbo].[cache] SET [expires] = DATEADD(second, 604800, [created])
+GO
+UPDATE [dbo].[cache_shared] SET [expires] = DATEADD(second, 604800, [created])
+GO
+UPDATE [dbo].[cache_index] SET [expires] = DATEADD(second, 604800, [changed])
+GO
+UPDATE [dbo].[cache_thread] SET [expires] = DATEADD(second, 604800, [changed])
+GO
+UPDATE [dbo].[cache_messages] SET [expires] = DATEADD(second, 604800, [changed])
+GO
+
+DROP INDEX [IX_cache_created]
+GO
+DROP INDEX [IX_cache_shared_created]
+GO
+ALTER TABLE [dbo].[cache_index] DROP COLUMN [changed]
+GO
+ALTER TABLE [dbo].[cache_thread] DROP COLUMN [changed]
+GO
+ALTER TABLE [dbo].[cache_messages] DROP COLUMN [changed]
+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
+CREATE INDEX [IX_cache_index_expires] ON [dbo].[cache_index]([expires]) ON [PRIMARY]
+GO
+CREATE INDEX [IX_cache_thread_expires] ON [dbo].[cache_thread]([expires]) ON [PRIMARY]
+GO
+CREATE INDEX [IX_cache_messages_expires] ON [dbo].[cache_messages]([expires]) ON [PRIMARY]
+GO
+ \ No newline at end of file
diff --git a/SQL/mysql.initial.sql b/SQL/mysql.initial.sql
index 3d6bae48a..4e4833a62 100644
--- a/SQL/mysql.initial.sql
+++ b/SQL/mysql.initial.sql
@@ -35,27 +35,40 @@ 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 ,
+ `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,
CONSTRAINT `user_id_fk_cache` FOREIGN KEY (`user_id`)
REFERENCES `users`(`user_id`) ON DELETE CASCADE ON UPDATE CASCADE,
- INDEX `created_index` (`created`),
+ INDEX `expires_index` (`expires`),
INDEX `user_cache_index` (`user_id`,`cache_key`)
) /*!40000 ENGINE=INNODB */ /*!40101 CHARACTER SET utf8 COLLATE utf8_general_ci */;
+-- Table structure for table `cache_shared`
+
+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`)
+) /*!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,
`mailbox` varchar(255) BINARY NOT NULL,
- `changed` datetime NOT NULL DEFAULT '1000-01-01 00:00:00',
+ `expires` datetime DEFAULT NULL,
`valid` tinyint(1) NOT NULL DEFAULT '0',
`data` longtext NOT NULL,
CONSTRAINT `user_id_fk_cache_index` FOREIGN KEY (`user_id`)
REFERENCES `users`(`user_id`) ON DELETE CASCADE ON UPDATE CASCADE,
- INDEX `changed_index` (`changed`),
+ INDEX `expires_index` (`expires`),
PRIMARY KEY (`user_id`, `mailbox`)
) /*!40000 ENGINE=INNODB */ /*!40101 CHARACTER SET utf8 COLLATE utf8_general_ci */;
@@ -65,11 +78,11 @@ CREATE TABLE `cache_index` (
CREATE TABLE `cache_thread` (
`user_id` int(10) UNSIGNED NOT NULL,
`mailbox` varchar(255) BINARY NOT NULL,
- `changed` datetime NOT NULL DEFAULT '1000-01-01 00:00:00',
+ `expires` datetime DEFAULT NULL,
`data` longtext NOT NULL,
CONSTRAINT `user_id_fk_cache_thread` FOREIGN KEY (`user_id`)
REFERENCES `users`(`user_id`) ON DELETE CASCADE ON UPDATE CASCADE,
- INDEX `changed_index` (`changed`),
+ INDEX `expires_index` (`expires`),
PRIMARY KEY (`user_id`, `mailbox`)
) /*!40000 ENGINE=INNODB */ /*!40101 CHARACTER SET utf8 COLLATE utf8_general_ci */;
@@ -80,12 +93,12 @@ CREATE TABLE `cache_messages` (
`user_id` int(10) UNSIGNED NOT NULL,
`mailbox` varchar(255) BINARY NOT NULL,
`uid` int(11) UNSIGNED NOT NULL DEFAULT '0',
- `changed` datetime NOT NULL DEFAULT '1000-01-01 00:00:00',
+ `expires` datetime DEFAULT NULL,
`data` longtext NOT NULL,
`flags` int(11) NOT NULL DEFAULT '0',
CONSTRAINT `user_id_fk_cache_messages` FOREIGN KEY (`user_id`)
REFERENCES `users`(`user_id`) ON DELETE CASCADE ON UPDATE CASCADE,
- INDEX `changed_index` (`changed`),
+ INDEX `expires_index` (`expires`),
PRIMARY KEY (`user_id`, `mailbox`, `uid`)
) /*!40000 ENGINE=INNODB */ /*!40101 CHARACTER SET utf8 COLLATE utf8_general_ci */;
@@ -196,4 +209,4 @@ CREATE TABLE `system` (
/*!40014 SET FOREIGN_KEY_CHECKS=1 */;
-INSERT INTO system (name, value) VALUES ('roundcube-version', '2013011700.sql');
+INSERT INTO system (name, value) VALUES ('roundcube-version', '2013061000');
diff --git a/SQL/mysql/2013011000.sql b/SQL/mysql/2013011000.sql
index f0b5e6a03..d1ea001db 100644
--- a/SQL/mysql/2013011000.sql
+++ b/SQL/mysql/2013011000.sql
@@ -1,6 +1,6 @@
-- Upgrade from 0.9-beta
-CREATE TABLE `system` (
+CREATE TABLE IF NOT EXISTS `system` (
`name` varchar(64) NOT NULL,
`value` mediumtext,
PRIMARY KEY(`name`)
diff --git a/SQL/mysql/2013042700.sql b/SQL/mysql/2013042700.sql
new file mode 100644
index 000000000..fe6741a02
--- /dev/null
+++ b/SQL/mysql/2013042700.sql
@@ -0,0 +1 @@
+-- empty \ No newline at end of file
diff --git a/SQL/mysql/2013052500.sql b/SQL/mysql/2013052500.sql
new file mode 100644
index 000000000..b5f72b8ab
--- /dev/null
+++ b/SQL/mysql/2013052500.sql
@@ -0,0 +1,7 @@
+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',
+ `data` longtext NOT NULL,
+ INDEX `created_index` (`created`),
+ INDEX `cache_key_index` (`cache_key`)
+) /*!40000 ENGINE=INNODB */ /*!40101 CHARACTER SET utf8 COLLATE utf8_general_ci */;
diff --git a/SQL/mysql/2013061000.sql b/SQL/mysql/2013061000.sql
new file mode 100644
index 000000000..54041b340
--- /dev/null
+++ b/SQL/mysql/2013061000.sql
@@ -0,0 +1,24 @@
+ALTER TABLE `cache` ADD `expires` datetime DEFAULT NULL;
+ALTER TABLE `cache_shared` ADD `expires` datetime DEFAULT NULL;
+ALTER TABLE `cache_index` ADD `expires` datetime DEFAULT NULL;
+ALTER TABLE `cache_thread` ADD `expires` datetime DEFAULT NULL;
+ALTER TABLE `cache_messages` ADD `expires` datetime DEFAULT NULL;
+
+-- initialize expires column with created/changed date + 7days
+UPDATE `cache` SET `expires` = `created` + interval 604800 second;
+UPDATE `cache_shared` SET `expires` = `created` + interval 604800 second;
+UPDATE `cache_index` SET `expires` = `changed` + interval 604800 second;
+UPDATE `cache_thread` SET `expires` = `changed` + interval 604800 second;
+UPDATE `cache_messages` SET `expires` = `changed` + interval 604800 second;
+
+ALTER TABLE `cache` DROP INDEX `created_index`;
+ALTER TABLE `cache_shared` DROP INDEX `created_index`;
+ALTER TABLE `cache_index` DROP `changed`;
+ALTER TABLE `cache_thread` DROP `changed`;
+ALTER TABLE `cache_messages` DROP `changed`;
+
+ALTER TABLE `cache` ADD INDEX `expires_index` (`expires`);
+ALTER TABLE `cache_shared` ADD INDEX `expires_index` (`expires`);
+ALTER TABLE `cache_index` ADD INDEX `expires_index` (`expires`);
+ALTER TABLE `cache_thread` ADD INDEX `expires_index` (`expires`);
+ALTER TABLE `cache_messages` ADD INDEX `expires_index` (`expires`);
diff --git a/SQL/postgres.initial.sql b/SQL/postgres.initial.sql
index d6acb5d69..f18cb6ad0 100644
--- a/SQL/postgres.initial.sql
+++ b/SQL/postgres.initial.sql
@@ -1,11 +1,11 @@
-- Roundcube Webmail initial database structure
--
--- Sequence "user_ids"
--- Name: user_ids; Type: SEQUENCE; Schema: public; Owner: postgres
+-- Sequence "users_seq"
+-- Name: users_seq; Type: SEQUENCE; Schema: public; Owner: postgres
--
-CREATE SEQUENCE user_ids
+CREATE SEQUENCE users_seq
INCREMENT BY 1
NO MAXVALUE
NO MINVALUE
@@ -17,7 +17,7 @@ CREATE SEQUENCE user_ids
--
CREATE TABLE users (
- user_id integer DEFAULT nextval('user_ids'::text) PRIMARY KEY,
+ user_id integer DEFAULT nextval('users_seq'::text) PRIMARY KEY,
username varchar(128) DEFAULT '' NOT NULL,
mail_host varchar(128) DEFAULT '' NOT NULL,
created timestamp with time zone DEFAULT now() NOT NULL,
@@ -45,11 +45,11 @@ CREATE INDEX session_changed_idx ON session (changed);
--
--- Sequence "identity_ids"
--- Name: identity_ids; Type: SEQUENCE; Schema: public; Owner: postgres
+-- Sequence "identities_seq"
+-- Name: identities_seq; Type: SEQUENCE; Schema: public; Owner: postgres
--
-CREATE SEQUENCE identity_ids
+CREATE SEQUENCE identities_seq
START WITH 1
INCREMENT BY 1
NO MAXVALUE
@@ -62,7 +62,7 @@ CREATE SEQUENCE identity_ids
--
CREATE TABLE identities (
- identity_id integer DEFAULT nextval('identity_ids'::text) PRIMARY KEY,
+ identity_id integer DEFAULT nextval('identities_seq'::text) PRIMARY KEY,
user_id integer NOT NULL
REFERENCES users (user_id) ON DELETE CASCADE ON UPDATE CASCADE,
changed timestamp with time zone DEFAULT now() NOT NULL,
@@ -82,11 +82,11 @@ CREATE INDEX identities_email_idx ON identities (email, del);
--
--- Sequence "contact_ids"
--- Name: contact_ids; Type: SEQUENCE; Schema: public; Owner: postgres
+-- Sequence "contacts_seq"
+-- Name: contacts_seq; Type: SEQUENCE; Schema: public; Owner: postgres
--
-CREATE SEQUENCE contact_ids
+CREATE SEQUENCE contacts_seq
START WITH 1
INCREMENT BY 1
NO MAXVALUE
@@ -99,7 +99,7 @@ CREATE SEQUENCE contact_ids
--
CREATE TABLE contacts (
- contact_id integer DEFAULT nextval('contact_ids'::text) PRIMARY KEY,
+ contact_id integer DEFAULT nextval('contacts_seq'::text) PRIMARY KEY,
user_id integer NOT NULL
REFERENCES users (user_id) ON DELETE CASCADE ON UPDATE CASCADE,
changed timestamp with time zone DEFAULT now() NOT NULL,
@@ -115,11 +115,11 @@ CREATE TABLE contacts (
CREATE INDEX contacts_user_id_idx ON contacts (user_id, del);
--
--- Sequence "contactgroups_ids"
--- Name: contactgroups_ids; Type: SEQUENCE; Schema: public; Owner: postgres
+-- Sequence "contactgroups_seq"
+-- Name: contactgroups_seq; Type: SEQUENCE; Schema: public; Owner: postgres
--
-CREATE SEQUENCE contactgroups_ids
+CREATE SEQUENCE contactgroups_seq
INCREMENT BY 1
NO MAXVALUE
NO MINVALUE
@@ -131,7 +131,7 @@ CREATE SEQUENCE contactgroups_ids
--
CREATE TABLE contactgroups (
- contactgroup_id integer DEFAULT nextval('contactgroups_ids'::text) PRIMARY KEY,
+ contactgroup_id integer DEFAULT nextval('contactgroups_seq'::text) PRIMARY KEY,
user_id integer NOT NULL
REFERENCES users(user_id) ON DELETE CASCADE ON UPDATE CASCADE,
changed timestamp with time zone DEFAULT now() NOT NULL,
@@ -164,14 +164,30 @@ CREATE INDEX contactgroupmembers_contact_id_idx ON contactgroupmembers (contact_
CREATE TABLE "cache" (
user_id integer NOT NULL
- REFERENCES users (user_id) ON DELETE CASCADE ON UPDATE CASCADE,
+ 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
);
CREATE INDEX cache_user_id_idx ON "cache" (user_id, cache_key);
-CREATE INDEX cache_created_idx ON "cache" (created);
+CREATE INDEX cache_expires_idx ON "cache" (expires);
+
+--
+-- Table "cache_shared"
+-- Name: cache_shared; Type: TABLE; Schema: public; Owner: postgres
+--
+
+CREATE TABLE "cache_shared" (
+ cache_key varchar(255) NOT NULL,
+ created timestamp with time zone DEFAULT now() NOT NULL,
+ 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);
--
-- Table "cache_index"
@@ -180,15 +196,15 @@ CREATE INDEX cache_created_idx ON "cache" (created);
CREATE TABLE cache_index (
user_id integer NOT NULL
- REFERENCES users (user_id) ON DELETE CASCADE ON UPDATE CASCADE,
+ REFERENCES users (user_id) ON DELETE CASCADE ON UPDATE CASCADE,
mailbox varchar(255) NOT NULL,
- changed timestamp with time zone DEFAULT now() NOT NULL,
+ expires timestamp with time zone DEFAULT NULL,
valid smallint NOT NULL DEFAULT 0,
data text NOT NULL,
PRIMARY KEY (user_id, mailbox)
);
-CREATE INDEX cache_index_changed_idx ON cache_index (changed);
+CREATE INDEX cache_index_expires_idx ON cache_index (expires);
--
-- Table "cache_thread"
@@ -197,14 +213,14 @@ 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,
+ REFERENCES users (user_id) ON DELETE CASCADE ON UPDATE CASCADE,
mailbox varchar(255) NOT NULL,
- changed timestamp with time zone DEFAULT now() NOT NULL,
+ expires timestamp with time zone DEFAULT NULL,
data text NOT NULL,
PRIMARY KEY (user_id, mailbox)
);
-CREATE INDEX cache_thread_changed_idx ON cache_thread (changed);
+CREATE INDEX cache_thread_expires_idx ON cache_thread (expires);
--
-- Table "cache_messages"
@@ -213,16 +229,16 @@ 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,
+ 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,
+ expires timestamp with time zone DEFAULT NULL,
data text NOT NULL,
flags integer NOT NULL DEFAULT 0,
PRIMARY KEY (user_id, mailbox, uid)
);
-CREATE INDEX cache_messages_changed_idx ON cache_messages (changed);
+CREATE INDEX cache_messages_expires_idx ON cache_messages (expires);
--
-- Table "dictionary"
@@ -231,18 +247,18 @@ CREATE INDEX cache_messages_changed_idx ON cache_messages (changed);
CREATE TABLE dictionary (
user_id integer DEFAULT NULL
- REFERENCES users (user_id) ON DELETE CASCADE ON UPDATE CASCADE,
+ REFERENCES users (user_id) ON DELETE CASCADE ON UPDATE CASCADE,
"language" varchar(5) NOT NULL,
data text NOT NULL,
CONSTRAINT dictionary_user_id_language_key UNIQUE (user_id, "language")
);
--
--- Sequence "searches_ids"
--- Name: searches_ids; Type: SEQUENCE; Schema: public; Owner: postgres
+-- Sequence "searches_seq"
+-- Name: searches_seq; Type: SEQUENCE; Schema: public; Owner: postgres
--
-CREATE SEQUENCE search_ids
+CREATE SEQUENCE searches_seq
INCREMENT BY 1
NO MAXVALUE
NO MINVALUE
@@ -254,7 +270,7 @@ CREATE SEQUENCE search_ids
--
CREATE TABLE searches (
- search_id integer DEFAULT nextval('search_ids'::text) PRIMARY KEY,
+ search_id integer DEFAULT nextval('searches_seq'::text) PRIMARY KEY,
user_id integer NOT NULL
REFERENCES users (user_id) ON DELETE CASCADE ON UPDATE CASCADE,
"type" smallint DEFAULT 0 NOT NULL,
@@ -274,4 +290,4 @@ CREATE TABLE "system" (
value text
);
-INSERT INTO system (name, value) VALUES ('roundcube-version', '2013011700.sql');
+INSERT INTO system (name, value) VALUES ('roundcube-version', '2013061000');
diff --git a/SQL/postgres/2013042700.sql b/SQL/postgres/2013042700.sql
new file mode 100644
index 000000000..bbd567515
--- /dev/null
+++ b/SQL/postgres/2013042700.sql
@@ -0,0 +1,14 @@
+ALTER SEQUENCE user_ids RENAME TO users_seq;
+ALTER TABLE users ALTER COLUMN user_id SET DEFAULT nextval('users_seq'::text);
+
+ALTER SEQUENCE identity_ids RENAME TO identities_seq;
+ALTER TABLE identities ALTER COLUMN identity_id SET DEFAULT nextval('identities_seq'::text);
+
+ALTER SEQUENCE contact_ids RENAME TO contacts_seq;
+ALTER TABLE contacts ALTER COLUMN contact_id SET DEFAULT nextval('contacts_seq'::text);
+
+ALTER SEQUENCE contactgroups_ids RENAME TO contactgroups_seq;
+ALTER TABLE contactgroups ALTER COLUMN contactgroup_id SET DEFAULT nextval('contactgroups_seq'::text);
+
+ALTER SEQUENCE search_ids RENAME TO searches_seq;
+ALTER TABLE searches ALTER COLUMN search_id SET DEFAULT nextval('searches_seq'::text);
diff --git a/SQL/postgres/2013052500.sql b/SQL/postgres/2013052500.sql
new file mode 100644
index 000000000..471e57176
--- /dev/null
+++ b/SQL/postgres/2013052500.sql
@@ -0,0 +1,8 @@
+CREATE TABLE "cache_shared" (
+ cache_key varchar(255) NOT NULL,
+ created timestamp with time zone DEFAULT now() NOT NULL,
+ data text NOT NULL
+);
+
+CREATE INDEX cache_shared_cache_key_idx ON "cache_shared" (cache_key);
+CREATE INDEX cache_shared_created_idx ON "cache_shared" (created);
diff --git a/SQL/postgres/2013061000.sql b/SQL/postgres/2013061000.sql
new file mode 100644
index 000000000..9db0ebcd7
--- /dev/null
+++ b/SQL/postgres/2013061000.sql
@@ -0,0 +1,24 @@
+ALTER TABLE "cache" ADD expires timestamp with time zone DEFAULT NULL;
+ALTER TABLE "cache_shared" ADD expires timestamp with time zone DEFAULT NULL;
+ALTER TABLE "cache_index" ADD expires timestamp with time zone DEFAULT NULL;
+ALTER TABLE "cache_thread" ADD expires timestamp with time zone DEFAULT NULL;
+ALTER TABLE "cache_messages" ADD expires timestamp with time zone DEFAULT NULL;
+
+-- initialize expires column with created/changed date + 7days
+UPDATE "cache" SET expires = created + interval '604800 seconds';
+UPDATE "cache_shared" SET expires = created + interval '604800 seconds';
+UPDATE "cache_index" SET expires = changed + interval '604800 seconds';
+UPDATE "cache_thread" SET expires = changed + interval '604800 seconds';
+UPDATE "cache_messages" SET expires = changed + interval '604800 seconds';
+
+DROP INDEX cache_created_idx;
+DROP INDEX cache_shared_created_idx;
+ALTER TABLE "cache_index" DROP "changed";
+ALTER TABLE "cache_thread" DROP "changed";
+ALTER TABLE "cache_messages" DROP "changed";
+
+CREATE INDEX cache_expires_idx ON "cache" (expires);
+CREATE INDEX cache_shared_expires_idx ON "cache_shared" (expires);
+CREATE INDEX cache_index_expires_idx ON "cache_index" (expires);
+CREATE INDEX cache_thread_expires_idx ON "cache_thread" (expires);
+CREATE INDEX cache_messages_expires_idx ON "cache_messages" (expires);
diff --git a/SQL/sqlite.initial.sql b/SQL/sqlite.initial.sql
index a9c7f5047..28a43680d 100644
--- a/SQL/sqlite.initial.sql
+++ b/SQL/sqlite.initial.sql
@@ -1,23 +1,6 @@
-- Roundcube Webmail initial database structure
--
--- Table structure for table cache
---
-
-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',
- data text NOT NULL
-);
-
-CREATE INDEX ix_cache_user_cache_key ON cache(user_id, cache_key);
-CREATE INDEX ix_cache_created ON cache(created);
-
-
--- --------------------------------------------------------
-
---
-- Table structure for table contacts and related
--
@@ -57,9 +40,6 @@ CREATE TABLE contactgroupmembers (
CREATE INDEX ix_contactgroupmembers_contact_id ON contactgroupmembers (contact_id);
-
--- --------------------------------------------------------
-
--
-- Table structure for table identities
--
@@ -82,9 +62,6 @@ CREATE TABLE identities (
CREATE INDEX ix_identities_user_id ON identities(user_id, del);
CREATE INDEX ix_identities_email ON identities(email, del);
-
--- --------------------------------------------------------
-
--
-- Table structure for table users
--
@@ -101,8 +78,6 @@ CREATE TABLE users (
CREATE UNIQUE INDEX ix_users_username ON users(username, mail_host);
--- --------------------------------------------------------
-
--
-- Table structure for table session
--
@@ -117,8 +92,6 @@ CREATE TABLE session (
CREATE INDEX ix_session_changed ON session (changed);
--- --------------------------------------------------------
-
--
-- Table structure for table dictionary
--
@@ -131,8 +104,6 @@ CREATE TABLE dictionary (
CREATE UNIQUE INDEX ix_dictionary_user_language ON dictionary (user_id, "language");
--- --------------------------------------------------------
-
--
-- Table structure for table searches
--
@@ -147,7 +118,34 @@ CREATE TABLE searches (
CREATE UNIQUE INDEX ix_searches_user_type_name ON searches (user_id, type, name);
--- --------------------------------------------------------
+--
+-- Table structure for table cache
+--
+
+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 NOT,
+ data text NOT NULL
+);
+
+CREATE INDEX ix_cache_user_cache_key ON cache(user_id, cache_key);
+CREATE INDEX ix_cache_expires ON cache(expires);
+
+--
+-- Table structure for table cache_shared
+--
+
+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
+);
+
+CREATE INDEX ix_cache_shared_cache_key ON cache_shared(cache_key);
+CREATE INDEX ix_cache_shared_expires ON cache_shared(expires);
--
-- Table structure for table cache_index
@@ -156,15 +154,13 @@ CREATE UNIQUE INDEX ix_searches_user_type_name ON searches (user_id, type, name)
CREATE TABLE cache_index (
user_id integer NOT NULL,
mailbox varchar(255) NOT NULL,
- changed datetime NOT NULL default '0000-00-00 00:00:00',
+ expires datetime DEFAULT NULL,
valid smallint NOT NULL DEFAULT '0',
data text NOT NULL,
PRIMARY KEY (user_id, mailbox)
);
-CREATE INDEX ix_cache_index_changed ON cache_index (changed);
-
--- --------------------------------------------------------
+CREATE INDEX ix_cache_index_expires ON cache_index (expires);
--
-- Table structure for table cache_thread
@@ -173,14 +169,12 @@ CREATE INDEX ix_cache_index_changed ON cache_index (changed);
CREATE TABLE cache_thread (
user_id integer NOT NULL,
mailbox varchar(255) NOT NULL,
- changed datetime NOT NULL default '0000-00-00 00:00:00',
+ expires datetime DEFAULT NULL,
data text NOT NULL,
PRIMARY KEY (user_id, mailbox)
);
-CREATE INDEX ix_cache_thread_changed ON cache_thread (changed);
-
--- --------------------------------------------------------
+CREATE INDEX ix_cache_thread_expires ON cache_thread (expires);
--
-- Table structure for table cache_messages
@@ -190,15 +184,13 @@ CREATE TABLE cache_messages (
user_id integer NOT NULL,
mailbox varchar(255) NOT NULL,
uid integer NOT NULL,
- changed datetime NOT NULL default '0000-00-00 00:00:00',
+ expires datetime DEFAULT NULL,
data text NOT NULL,
flags integer NOT NULL DEFAULT '0',
PRIMARY KEY (user_id, mailbox, uid)
);
-CREATE INDEX ix_cache_messages_changed ON cache_messages (changed);
-
--- --------------------------------------------------------
+CREATE INDEX ix_cache_messages_expires ON cache_messages (expires);
--
-- Table structure for table system
@@ -209,4 +201,4 @@ CREATE TABLE system (
value text NOT NULL
);
-INSERT INTO system (name, value) VALUES ('roundcube-version', '2013011700.sql');
+INSERT INTO system (name, value) VALUES ('roundcube-version', '2013061000');
diff --git a/SQL/sqlite/2013011000.sql b/SQL/sqlite/2013011000.sql
index 2c66ef95e..ec261a635 100644
--- a/SQL/sqlite/2013011000.sql
+++ b/SQL/sqlite/2013011000.sql
@@ -1,6 +1,6 @@
-- Updates from version 0.9-beta
-CREATE TABLE system (
+CREATE TABLE IF NOT EXISTS system (
name varchar(64) NOT NULL PRIMARY KEY,
value text NOT NULL
);
diff --git a/SQL/sqlite/2013042700.sql b/SQL/sqlite/2013042700.sql
new file mode 100644
index 000000000..fe6741a02
--- /dev/null
+++ b/SQL/sqlite/2013042700.sql
@@ -0,0 +1 @@
+-- empty \ No newline at end of file
diff --git a/SQL/sqlite/2013052500.sql b/SQL/sqlite/2013052500.sql
new file mode 100644
index 000000000..19ae1b110
--- /dev/null
+++ b/SQL/sqlite/2013052500.sql
@@ -0,0 +1,8 @@
+CREATE TABLE cache_shared (
+ cache_key varchar(255) NOT NULL,
+ created datetime NOT NULL default '0000-00-00 00:00:00',
+ data text NOT NULL
+);
+
+CREATE INDEX ix_cache_shared_cache_key ON cache_shared(cache_key);
+CREATE INDEX ix_cache_shared_created ON cache_shared(created);
diff --git a/SQL/sqlite/2013061000.sql b/SQL/sqlite/2013061000.sql
new file mode 100644
index 000000000..3c6b43eac
--- /dev/null
+++ b/SQL/sqlite/2013061000.sql
@@ -0,0 +1,48 @@
+DROP TABLE cache_index;
+DROP TABLE cache_thread;
+DROP TABLE cache_messages;
+
+ALTER TABLE cache ADD expires datetime DEFAULT NULL;
+DROP INDEX ix_cache_created;
+
+ALTER TABLE cache_shared ADD expires datetime DEFAULT NULL;
+DROP INDEX ix_cache_shared_created;
+
+UPDATE cache SET expires = datetime(created, '+604800 seconds');
+UPDATE cache_shared SET expires = datetime(created, '+604800 seconds');
+
+CREATE INDEX ix_cache_expires ON cache(expires);
+CREATE INDEX ix_cache_shared_expires ON cache_shared(expires);
+
+CREATE TABLE cache_index (
+ user_id integer NOT NULL,
+ mailbox varchar(255) NOT NULL,
+ expires datetime DEFAULT NULL,
+ valid smallint NOT NULL DEFAULT '0',
+ data text NOT NULL,
+ PRIMARY KEY (user_id, mailbox)
+);
+
+CREATE INDEX ix_cache_index_expires ON cache_index (expires);
+
+CREATE TABLE cache_thread (
+ user_id integer NOT NULL,
+ mailbox varchar(255) NOT NULL,
+ expires datetime DEFAULT NULL,
+ data text NOT NULL,
+ PRIMARY KEY (user_id, mailbox)
+);
+
+CREATE INDEX ix_cache_thread_expires ON cache_thread (expires);
+
+CREATE TABLE cache_messages (
+ user_id integer NOT NULL,
+ mailbox varchar(255) NOT NULL,
+ uid integer NOT NULL,
+ expires datetime DEFAULT NULL,
+ data text NOT NULL,
+ flags integer NOT NULL DEFAULT '0',
+ PRIMARY KEY (user_id, mailbox, uid)
+);
+
+CREATE INDEX ix_cache_messages_expires ON cache_messages (expires);
generated by cgit v1.2.3 (git 2.39.1) at 2024-07-11 20:56:03 +0000