Merge branch 'schema_upgrades' into release-0.9

author: Aleksander Machniak <alec@alec.pl> 2013-01-17 08:39:39 +0100
committer: Aleksander Machniak <alec@alec.pl> 2013-01-17 08:39:39 +0100
commit: f5c7df4c324d3157ab47469f492ecdb2a4b82e93 (patch)
tree: 2137040407617eb0891aba2ac0908a2bb9e9e5fb /SQL
parent: a03c28a22f194c3cbaee46abd8def0d7b8fa7e60 (diff)
parent: c101871d463963fe5426a2db3ba39cb524a96dbd (diff)
57 files changed, 1119 insertions, 1085 deletions
diff --git a/SQL/mssql.initial.sql b/SQL/mssql.initial.sql
index 85b8e4ef6..e312cfbcd 100644
--- a/SQL/mssql.initial.sql
+++ b/SQL/mssql.initial.sql
@@ -115,6 +115,12 @@ CREATE TABLE [dbo].[searches] (
 ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
 GO
 
+CREATE TABLE [dbo].[system] (
+	[name] [varchar] (64) COLLATE Latin1_General_CI_AI NOT NULL ,
+	[value] [text] COLLATE Latin1_General_CI_AI NOT NULL 
+) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
+GO
+
 ALTER TABLE [dbo].[cache_index] WITH NOCHECK ADD 
 	 PRIMARY KEY CLUSTERED 
 	(
@@ -185,6 +191,13 @@ ALTER TABLE [dbo].[searches] WITH NOCHECK ADD
 	) ON [PRIMARY] 
 GO
 
+ALTER TABLE [dbo].[system] WITH NOCHECK ADD 
+	CONSTRAINT [PK_system_name] PRIMARY KEY CLUSTERED 
+	(
+		[name]
+	) 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],
@@ -358,3 +371,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', '2013011000.sql')
+GO
+
+\ No newline at end of file
diff --git a/SQL/mssql.upgrade.sql b/SQL/mssql.upgrade.sql
deleted file mode 100644
index 26001e713..000000000
--- a/SQL/mssql.upgrade.sql
+++ /dev/null
@@ -1,273 +0,0 @@
--- Roundcube Webmail update script for MSSQL databases
-
--- Updates from version 0.3.1
-
-ALTER TABLE [dbo].[messages] ADD CONSTRAINT [FK_messages_user_id]
-    FOREIGN KEY ([user_id]) REFERENCES [dbo].[users] ([user_id])
-    ON DELETE CASCADE ON UPDATE CASCADE
-GO
-
-ALTER TABLE [dbo].[cache] ADD CONSTRAINT [FK_cache_user_id]
-    FOREIGN KEY ([user_id]) REFERENCES [dbo].[users] ([user_id])
-    ON DELETE CASCADE ON UPDATE CASCADE
-GO
-
-ALTER TABLE [dbo].[contacts] ADD CONSTRAINT [FK_contacts_user_id]
-    FOREIGN KEY ([user_id]) REFERENCES [dbo].[users] ([user_id])
-    ON DELETE CASCADE ON UPDATE CASCADE
-GO
-
-ALTER TABLE [dbo].[identities] ADD CONSTRAINT [FK_identities_user_id] 
-    FOREIGN KEY ([user_id]) REFERENCES [dbo].[users] ([user_id])
-    ON DELETE CASCADE ON UPDATE CASCADE
-GO
-
-ALTER TABLE [dbo].[identities] ADD [changed] [datetime] NULL 
-GO
-
-CREATE TABLE [dbo].[contactgroups] (
-	[contactgroup_id] [int] IDENTITY (1, 1) NOT NULL ,
-	[user_id] [int] NOT NULL ,
-	[changed] [datetime] NOT NULL ,
-	[del] [char] (1) COLLATE Latin1_General_CI_AI NOT NULL ,
-	[name] [varchar] (128) COLLATE Latin1_General_CI_AI NOT NULL
-) ON [PRIMARY] 
-GO
-
-CREATE TABLE [dbo].[contactgroupmembers] (
-	[contactgroup_id] [int] NOT NULL ,
-	[contact_id] [int] NOT NULL ,
-	[created] [datetime] NOT NULL
-) ON [PRIMARY] 
-GO
-
-ALTER TABLE [dbo].[contactgroups] WITH NOCHECK ADD 
-	CONSTRAINT [PK_contactgroups_contactgroup_id] PRIMARY KEY CLUSTERED 
-	(
-		[contactgroup_id]
-	)  ON [PRIMARY] 
-GO
-
-ALTER TABLE [dbo].[contactgroupmembers] WITH NOCHECK ADD 
-	CONSTRAINT [PK_contactgroupmembers_id] PRIMARY KEY CLUSTERED 
-	(
-		[contactgroup_id], [contact_id]
-	)  ON [PRIMARY] 
-GO
-
-ALTER TABLE [dbo].[contactgroups] ADD 
-	CONSTRAINT [DF_contactgroups_user_id] DEFAULT (0) FOR [user_id],
-	CONSTRAINT [DF_contactgroups_changed] DEFAULT (getdate()) FOR [changed],
-	CONSTRAINT [DF_contactgroups_del] DEFAULT ('0') FOR [del],
-	CONSTRAINT [DF_contactgroups_name] DEFAULT ('') FOR [name],
-	CONSTRAINT [CK_contactgroups_del] CHECK ([del] = '1' or [del] = '0')
-GO
-
-CREATE  INDEX [IX_contactgroups_user_id] ON [dbo].[contacts]([user_id]) ON [PRIMARY]
-GO
-
-ALTER TABLE [dbo].[contactgroupmembers] ADD 
-	CONSTRAINT [DF_contactgroupmembers_contactgroup_id] DEFAULT (0) FOR [contactgroup_id],
-	CONSTRAINT [DF_contactgroupmembers_contact_id] DEFAULT (0) FOR [contact_id],
-	CONSTRAINT [DF_contactgroupmembers_created] DEFAULT (getdate()) FOR [created]
-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
-GO
-
-CREATE TRIGGER [contact_delete_member] ON [dbo].[contacts]
-    AFTER DELETE AS
-    DELETE FROM [dbo].[contactgroupmembers]
-    WHERE [contact_id] IN (SELECT [contact_id] FROM deleted)
-GO
-
-ALTER TABLE [dbo].[contactgroups] ADD CONSTRAINT [FK_contactgroups_user_id]
-    FOREIGN KEY ([user_id]) REFERENCES [dbo].[users] ([user_id])
-    ON DELETE CASCADE ON UPDATE CASCADE
-GO
-
--- Updates from version 0.4.2
-
-DROP INDEX [IX_users_username]
-GO
-CREATE UNIQUE INDEX [IX_users_username] ON [dbo].[users]([username],[mail_host]) ON [PRIMARY]
-GO
-ALTER TABLE [dbo].[contacts] ALTER COLUMN [email] [varchar] (255) COLLATE Latin1_General_CI_AI NOT NULL
-GO
-
--- Updates from version 0.5.1
--- Updates from version 0.5.2
--- Updates from version 0.5.3
--- Updates from version 0.5.4
-
-ALTER TABLE [dbo].[contacts] ADD [words] [text] COLLATE Latin1_General_CI_AI NULL 
-GO
-CREATE INDEX [IX_contactgroupmembers_contact_id] ON [dbo].[contactgroupmembers]([contact_id]) ON [PRIMARY]
-GO
-DELETE FROM [dbo].[messages]
-GO
-DELETE FROM [dbo].[cache]
-GO
-
--- Updates from version 0.6
-
-CREATE TABLE [dbo].[dictionary] (
-    [user_id] [int] ,
-    [language] [varchar] (5) COLLATE Latin1_General_CI_AI NOT NULL ,
-    [data] [text] COLLATE Latin1_General_CI_AI NOT NULL 
-) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
-GO
-CREATE  UNIQUE INDEX [IX_dictionary_user_language] ON [dbo].[dictionary]([user_id],[language]) ON [PRIMARY]
-GO
-
-CREATE TABLE [dbo].[searches] (
-	[search_id] [int] IDENTITY (1, 1) NOT NULL ,
-	[user_id] [int] NOT NULL ,
-	[type] [tinyint] NOT NULL ,
-	[name] [varchar] (128) COLLATE Latin1_General_CI_AI NOT NULL ,
-	[data] [text] COLLATE Latin1_General_CI_AI NOT NULL 
-) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
-GO
-
-ALTER TABLE [dbo].[searches] WITH NOCHECK ADD 
-	CONSTRAINT [PK_searches_search_id] PRIMARY KEY CLUSTERED 
-	(
-		[search_id]
-	) ON [PRIMARY] 
-GO
-
-ALTER TABLE [dbo].[searches] ADD 
-	CONSTRAINT [DF_searches_user] DEFAULT (0) FOR [user_id],
-	CONSTRAINT [DF_searches_type] DEFAULT (0) FOR [type],
-GO
-
-CREATE UNIQUE INDEX [IX_searches_user_type_name] ON [dbo].[searches]([user_id],[type],[name]) ON [PRIMARY]
-GO
-
-ALTER TABLE [dbo].[searches] ADD CONSTRAINT [FK_searches_user_id]
-    FOREIGN KEY ([user_id]) REFERENCES [dbo].[users] ([user_id])
-    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 ,
-	[valid] [char] (1) COLLATE Latin1_General_CI_AI 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 ,
-	[flags] [int] 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],
-	CONSTRAINT [DF_cache_index_valid] DEFAULT ('0') FOR [valid]
-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_flags] DEFAULT (0) FOR [flags]
-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
-
--- Updates from version 0.7-beta
-
-ALTER TABLE [dbo].[session] ALTER COLUMN [sess_id] [varchar] (128) COLLATE Latin1_General_CI_AI NOT NULL
-GO
-
--- Updates from version 0.7
-
-ALTER TABLE [dbo].[contacts] DROP CONSTRAINT [DF_contacts_email]
-GO
-ALTER TABLE [dbo].[contacts] ALTER COLUMN [email] [text] COLLATE Latin1_General_CI_AI NOT NULL
-GO
-ALTER TABLE [dbo].[contacts] ADD CONSTRAINT [DF_contacts_email] DEFAULT ('') FOR [email]
-GO
-
--- Updates from version 0.8-rc
-
-ALTER TABLE [dbo].[contacts] DROP CONSTRAINT [DF_contacts_email]
-GO
-ALTER TABLE [dbo].[contacts] ALTER COLUMN [email] [varchar] (8000) COLLATE Latin1_General_CI_AI NOT NULL
-GO
-ALTER TABLE [dbo].[contacts] ADD CONSTRAINT [DF_contacts_email] DEFAULT ('') FOR [email]
-GO
-
--- Updates from version 0.8
-
-ALTER TABLE [dbo].[cache] DROP COLUMN [cache_id]
-GO
-ALTER TABLE [dbo].[users] DROP COLUMN [alias]
-GO
-CREATE INDEX [IX_identities_email] ON [dbo].[identities]([email],[del]) ON [PRIMARY]
-GO
-
-\ No newline at end of file
diff --git a/SQL/mssql/2009103100.sql b/SQL/mssql/2009103100.sql
new file mode 100644
index 000000000..646fe38af
--- /dev/null
+++ b/SQL/mssql/2009103100.sql
@@ -0,0 +1,87 @@
+-- Updates from version 0.3.1
+
+ALTER TABLE [dbo].[messages] ADD CONSTRAINT [FK_messages_user_id]
+    FOREIGN KEY ([user_id]) REFERENCES [dbo].[users] ([user_id])
+    ON DELETE CASCADE ON UPDATE CASCADE
+GO
+
+ALTER TABLE [dbo].[cache] ADD CONSTRAINT [FK_cache_user_id]
+    FOREIGN KEY ([user_id]) REFERENCES [dbo].[users] ([user_id])
+    ON DELETE CASCADE ON UPDATE CASCADE
+GO
+
+ALTER TABLE [dbo].[contacts] ADD CONSTRAINT [FK_contacts_user_id]
+    FOREIGN KEY ([user_id]) REFERENCES [dbo].[users] ([user_id])
+    ON DELETE CASCADE ON UPDATE CASCADE
+GO
+
+ALTER TABLE [dbo].[identities] ADD CONSTRAINT [FK_identities_user_id] 
+    FOREIGN KEY ([user_id]) REFERENCES [dbo].[users] ([user_id])
+    ON DELETE CASCADE ON UPDATE CASCADE
+GO
+
+ALTER TABLE [dbo].[identities] ADD [changed] [datetime] NULL 
+GO
+
+CREATE TABLE [dbo].[contactgroups] (
+	[contactgroup_id] [int] IDENTITY (1, 1) NOT NULL ,
+	[user_id] [int] NOT NULL ,
+	[changed] [datetime] NOT NULL ,
+	[del] [char] (1) COLLATE Latin1_General_CI_AI NOT NULL ,
+	[name] [varchar] (128) COLLATE Latin1_General_CI_AI NOT NULL
+) ON [PRIMARY] 
+GO
+
+CREATE TABLE [dbo].[contactgroupmembers] (
+	[contactgroup_id] [int] NOT NULL ,
+	[contact_id] [int] NOT NULL ,
+	[created] [datetime] NOT NULL
+) ON [PRIMARY] 
+GO
+
+ALTER TABLE [dbo].[contactgroups] WITH NOCHECK ADD 
+	CONSTRAINT [PK_contactgroups_contactgroup_id] PRIMARY KEY CLUSTERED 
+	(
+		[contactgroup_id]
+	)  ON [PRIMARY] 
+GO
+
+ALTER TABLE [dbo].[contactgroupmembers] WITH NOCHECK ADD 
+	CONSTRAINT [PK_contactgroupmembers_id] PRIMARY KEY CLUSTERED 
+	(
+		[contactgroup_id], [contact_id]
+	)  ON [PRIMARY] 
+GO
+
+ALTER TABLE [dbo].[contactgroups] ADD 
+	CONSTRAINT [DF_contactgroups_user_id] DEFAULT (0) FOR [user_id],
+	CONSTRAINT [DF_contactgroups_changed] DEFAULT (getdate()) FOR [changed],
+	CONSTRAINT [DF_contactgroups_del] DEFAULT ('0') FOR [del],
+	CONSTRAINT [DF_contactgroups_name] DEFAULT ('') FOR [name],
+	CONSTRAINT [CK_contactgroups_del] CHECK ([del] = '1' or [del] = '0')
+GO
+
+CREATE  INDEX [IX_contactgroups_user_id] ON [dbo].[contacts]([user_id]) ON [PRIMARY]
+GO
+
+ALTER TABLE [dbo].[contactgroupmembers] ADD 
+	CONSTRAINT [DF_contactgroupmembers_contactgroup_id] DEFAULT (0) FOR [contactgroup_id],
+	CONSTRAINT [DF_contactgroupmembers_contact_id] DEFAULT (0) FOR [contact_id],
+	CONSTRAINT [DF_contactgroupmembers_created] DEFAULT (getdate()) FOR [created]
+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
+GO
+
+CREATE TRIGGER [contact_delete_member] ON [dbo].[contacts]
+    AFTER DELETE AS
+    DELETE FROM [dbo].[contactgroupmembers]
+    WHERE [contact_id] IN (SELECT [contact_id] FROM deleted)
+GO
+
+ALTER TABLE [dbo].[contactgroups] ADD CONSTRAINT [FK_contactgroups_user_id]
+    FOREIGN KEY ([user_id]) REFERENCES [dbo].[users] ([user_id])
+    ON DELETE CASCADE ON UPDATE CASCADE
+GO
diff --git a/SQL/mssql/2010100600.sql b/SQL/mssql/2010100600.sql
new file mode 100644
index 000000000..fb045e303
--- /dev/null
+++ b/SQL/mssql/2010100600.sql
@@ -0,0 +1,9 @@
+-- Updates from version 0.4.2
+
+DROP INDEX [IX_users_username]
+GO
+CREATE UNIQUE INDEX [IX_users_username] ON [dbo].[users]([username],[mail_host]) ON [PRIMARY]
+GO
+ALTER TABLE [dbo].[contacts] ALTER COLUMN [email] [varchar] (255) COLLATE Latin1_General_CI_AI NOT NULL
+GO
+
+\ No newline at end of file
diff --git a/SQL/mssql/2011011200.sql b/SQL/mssql/2011011200.sql
new file mode 100644
index 000000000..8715455df
--- /dev/null
+++ b/SQL/mssql/2011011200.sql
@@ -0,0 +1,10 @@
+-- Updates from version 0.5.x
+
+ALTER TABLE [dbo].[contacts] ADD [words] [text] COLLATE Latin1_General_CI_AI NULL 
+GO
+CREATE INDEX [IX_contactgroupmembers_contact_id] ON [dbo].[contactgroupmembers]([contact_id]) ON [PRIMARY]
+GO
+DELETE FROM [dbo].[messages]
+GO
+DELETE FROM [dbo].[cache]
+GO
diff --git a/SQL/mssql/2011092800.sql b/SQL/mssql/2011092800.sql
new file mode 100644
index 000000000..00f79bbc0
--- /dev/null
+++ b/SQL/mssql/2011092800.sql
@@ -0,0 +1,127 @@
+-- Updates from version 0.6
+
+CREATE TABLE [dbo].[dictionary] (
+    [user_id] [int] ,
+    [language] [varchar] (5) COLLATE Latin1_General_CI_AI NOT NULL ,
+    [data] [text] COLLATE Latin1_General_CI_AI NOT NULL 
+) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
+GO
+CREATE  UNIQUE INDEX [IX_dictionary_user_language] ON [dbo].[dictionary]([user_id],[language]) ON [PRIMARY]
+GO
+
+CREATE TABLE [dbo].[searches] (
+	[search_id] [int] IDENTITY (1, 1) NOT NULL ,
+	[user_id] [int] NOT NULL ,
+	[type] [tinyint] NOT NULL ,
+	[name] [varchar] (128) COLLATE Latin1_General_CI_AI NOT NULL ,
+	[data] [text] COLLATE Latin1_General_CI_AI NOT NULL 
+) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
+GO
+
+ALTER TABLE [dbo].[searches] WITH NOCHECK ADD 
+	CONSTRAINT [PK_searches_search_id] PRIMARY KEY CLUSTERED 
+	(
+		[search_id]
+	) ON [PRIMARY] 
+GO
+
+ALTER TABLE [dbo].[searches] ADD 
+	CONSTRAINT [DF_searches_user] DEFAULT (0) FOR [user_id],
+	CONSTRAINT [DF_searches_type] DEFAULT (0) FOR [type],
+GO
+
+CREATE UNIQUE INDEX [IX_searches_user_type_name] ON [dbo].[searches]([user_id],[type],[name]) ON [PRIMARY]
+GO
+
+ALTER TABLE [dbo].[searches] ADD CONSTRAINT [FK_searches_user_id]
+    FOREIGN KEY ([user_id]) REFERENCES [dbo].[users] ([user_id])
+    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 ,
+	[valid] [char] (1) COLLATE Latin1_General_CI_AI 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 ,
+	[flags] [int] 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],
+	CONSTRAINT [DF_cache_index_valid] DEFAULT ('0') FOR [valid]
+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_flags] DEFAULT (0) FOR [flags]
+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
diff --git a/SQL/mssql/2011111600.sql b/SQL/mssql/2011111600.sql
new file mode 100644
index 000000000..387877604
--- /dev/null
+++ b/SQL/mssql/2011111600.sql
@@ -0,0 +1,4 @@
+-- Updates from version 0.7-beta
+
+ALTER TABLE [dbo].[session] ALTER COLUMN [sess_id] [varchar] (128) COLLATE Latin1_General_CI_AI NOT NULL
+GO
diff --git a/SQL/mssql/2011121400.sql b/SQL/mssql/2011121400.sql
new file mode 100644
index 000000000..fde63ea79
--- /dev/null
+++ b/SQL/mssql/2011121400.sql
@@ -0,0 +1,9 @@
+-- Updates from version 0.7
+
+ALTER TABLE [dbo].[contacts] DROP CONSTRAINT [DF_contacts_email]
+GO
+ALTER TABLE [dbo].[contacts] ALTER COLUMN [email] [text] COLLATE Latin1_General_CI_AI NOT NULL
+GO
+ALTER TABLE [dbo].[contacts] ADD CONSTRAINT [DF_contacts_email] DEFAULT ('') FOR [email]
+GO
+
+\ No newline at end of file
diff --git a/SQL/mssql/2012051800.sql b/SQL/mssql/2012051800.sql
new file mode 100644
index 000000000..8dcf7bfc0
--- /dev/null
+++ b/SQL/mssql/2012051800.sql
@@ -0,0 +1,18 @@
+-- Updates from version 0.8-rc
+
+ALTER TABLE [dbo].[contacts] DROP CONSTRAINT [DF_contacts_email]
+GO
+ALTER TABLE [dbo].[contacts] ALTER COLUMN [email] [varchar] (8000) COLLATE Latin1_General_CI_AI NOT NULL
+GO
+ALTER TABLE [dbo].[contacts] ADD CONSTRAINT [DF_contacts_email] DEFAULT ('') FOR [email]
+GO
+
+-- Updates from version 0.8
+
+ALTER TABLE [dbo].[cache] DROP COLUMN [cache_id]
+GO
+ALTER TABLE [dbo].[users] DROP COLUMN [alias]
+GO
+CREATE INDEX [IX_identities_email] ON [dbo].[identities]([email],[del]) ON [PRIMARY]
+GO
+
+\ No newline at end of file
diff --git a/SQL/mssql/2012080700.sql b/SQL/mssql/2012080700.sql
new file mode 100644
index 000000000..33e6133b3
--- /dev/null
+++ b/SQL/mssql/2012080700.sql
@@ -0,0 +1,8 @@
+-- Updates from version 0.8
+
+ALTER TABLE [dbo].[cache] DROP COLUMN [cache_id]
+GO
+ALTER TABLE [dbo].[users] DROP COLUMN [alias]
+GO
+CREATE INDEX [IX_identities_email] ON [dbo].[identities]([email],[del]) ON [PRIMARY]
+GO
diff --git a/SQL/mssql/2013011000.sql b/SQL/mssql/2013011000.sql
new file mode 100644
index 000000000..2eb51e6b5
--- /dev/null
+++ b/SQL/mssql/2013011000.sql
@@ -0,0 +1,14 @@
+-- Upgrades from 0.9-beta
+
+CREATE TABLE [dbo].[system] (
+    [name] [varchar] (64) COLLATE Latin1_General_CI_AI NOT NULL ,
+    [value] [text] COLLATE Latin1_General_CI_AI 
+) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
+GO
+
+ALTER TABLE [dbo].[system] WITH NOCHECK ADD
+    CONSTRAINT [PK_system_name] PRIMARY KEY CLUSTERED
+    (
+        [name]
+    ) ON [PRIMARY]
+GO
diff --git a/SQL/mysql.initial.sql b/SQL/mysql.initial.sql
index 47d9db4a2..bb1856cf7 100644
--- a/SQL/mysql.initial.sql
+++ b/SQL/mysql.initial.sql
@@ -186,4 +186,14 @@ CREATE TABLE `searches` (
 ) /*!40000 ENGINE=INNODB */ /*!40101 CHARACTER SET utf8 COLLATE utf8_general_ci */;
 
 
+-- Table structure for table `system`
+
+CREATE TABLE `system` (
+ `name` varchar(64) NOT NULL,
+ `value` mediumtext,
+ PRIMARY KEY(`name`)
+) /*!40000 ENGINE=INNODB */ /*!40101 CHARACTER SET utf8 COLLATE utf8_general_ci */;
+
 /*!40014 SET FOREIGN_KEY_CHECKS=1 */;
+
+INSERT INTO system (name, value) VALUES ('roundcube-version', '2013011000.sql');
diff --git a/SQL/mysql.update.sql b/SQL/mysql.update.sql
deleted file mode 100644
index 237aa3e38..000000000
--- a/SQL/mysql.update.sql
+++ /dev/null
@@ -1,247 +0,0 @@
--- Roundcube Webmail update script for MySQL databases
-
--- Updates from version 0.1-stable
-
-TRUNCATE TABLE `messages`;
-
-ALTER TABLE `messages`
-  DROP INDEX `idx`,
-  DROP INDEX `uid`;
-
-ALTER TABLE `cache`
-  DROP INDEX `cache_key`,
-  DROP INDEX `session_id`,
-  ADD INDEX `user_cache_index` (`user_id`,`cache_key`);
-
-ALTER TABLE `users`
-    ADD INDEX `username_index` (`username`),
-    ADD INDEX `alias_index` (`alias`);
-
--- Updates from version 0.1.1
-
-ALTER TABLE `identities`
-    MODIFY `signature` text, 
-    MODIFY `bcc` varchar(128) NOT NULL DEFAULT '', 
-    MODIFY `reply-to` varchar(128) NOT NULL DEFAULT '', 
-    MODIFY `organization` varchar(128) NOT NULL DEFAULT '',
-    MODIFY `name` varchar(128) NOT NULL, 
-    MODIFY `email` varchar(128) NOT NULL; 
-
--- Updates from version 0.2-alpha
-
-ALTER TABLE `messages`
-    ADD INDEX `created_index` (`created`);
-
--- Updates from version 0.2-beta (InnoDB required)
-
-ALTER TABLE `cache`
-    DROP `session_id`;
-
-ALTER TABLE `session`
-    ADD INDEX `changed_index` (`changed`);
-
-ALTER TABLE `cache`
-    ADD INDEX `created_index` (`created`);
-
-ALTER TABLE `users`
-    CHANGE `language` `language` varchar(5);
-
-ALTER TABLE `cache` ENGINE=InnoDB;
-ALTER TABLE `session` ENGINE=InnoDB;
-ALTER TABLE `messages` ENGINE=InnoDB;
-ALTER TABLE `users` ENGINE=InnoDB;
-ALTER TABLE `contacts` ENGINE=InnoDB;
-ALTER TABLE `identities` ENGINE=InnoDB;
-
--- Updates from version 0.3-stable
-
-TRUNCATE `messages`;
-
-ALTER TABLE `messages`
-    ADD INDEX `index_index` (`user_id`, `cache_key`, `idx`);
-
-ALTER TABLE `session` 
-    CHANGE `vars` `vars` MEDIUMTEXT NOT NULL;
-
-ALTER TABLE `contacts`
-    ADD INDEX `user_contacts_index` (`user_id`,`email`);
-
--- Updates from version 0.3.1
--- WARNING: Make sure that all tables are using InnoDB engine!!!
---          If not, use: ALTER TABLE xxx ENGINE=InnoDB;
-
-/* MySQL bug workaround: http://bugs.mysql.com/bug.php?id=46293 */
-/*!40014 SET FOREIGN_KEY_CHECKS=0 */;
-
-ALTER TABLE `messages` DROP FOREIGN KEY `user_id_fk_messages`;
-ALTER TABLE `cache` DROP FOREIGN KEY `user_id_fk_cache`;
-ALTER TABLE `contacts` DROP FOREIGN KEY `user_id_fk_contacts`;
-ALTER TABLE `identities` DROP FOREIGN KEY `user_id_fk_identities`;
-
-ALTER TABLE `messages` ADD CONSTRAINT `user_id_fk_messages` FOREIGN KEY (`user_id`)
- REFERENCES `users`(`user_id`) ON DELETE CASCADE ON UPDATE CASCADE;
-ALTER TABLE `cache` ADD CONSTRAINT `user_id_fk_cache` FOREIGN KEY (`user_id`)
- REFERENCES `users`(`user_id`) ON DELETE CASCADE ON UPDATE CASCADE;
-ALTER TABLE `contacts` ADD CONSTRAINT `user_id_fk_contacts` FOREIGN KEY (`user_id`)
- REFERENCES `users`(`user_id`) ON DELETE CASCADE ON UPDATE CASCADE;
-ALTER TABLE `identities` ADD CONSTRAINT `user_id_fk_identities` FOREIGN KEY (`user_id`)
- REFERENCES `users`(`user_id`) ON DELETE CASCADE ON UPDATE CASCADE;
-
-ALTER TABLE `contacts` ALTER `name` SET DEFAULT '';
-ALTER TABLE `contacts` ALTER `firstname` SET DEFAULT '';
-ALTER TABLE `contacts` ALTER `surname` SET DEFAULT '';
-
-ALTER TABLE `identities` ADD INDEX `user_identities_index` (`user_id`, `del`);
-ALTER TABLE `identities` ADD `changed` datetime NOT NULL DEFAULT '1000-01-01 00:00:00' AFTER `user_id`;
-
-CREATE TABLE `contactgroups` (
-  `contactgroup_id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
-  `user_id` int(10) UNSIGNED NOT NULL DEFAULT '0',
-  `changed` datetime NOT NULL DEFAULT '1000-01-01 00:00:00',
-  `del` tinyint(1) NOT NULL DEFAULT '0',
-  `name` varchar(128) NOT NULL DEFAULT '',
-  PRIMARY KEY(`contactgroup_id`),
-  CONSTRAINT `user_id_fk_contactgroups` FOREIGN KEY (`user_id`)
-    REFERENCES `users`(`user_id`) ON DELETE CASCADE ON UPDATE CASCADE,
-  INDEX `contactgroups_user_index` (`user_id`,`del`)
-) /*!40000 ENGINE=INNODB */ /*!40101 CHARACTER SET utf8 COLLATE utf8_general_ci */;
-
-CREATE TABLE `contactgroupmembers` (
-  `contactgroup_id` int(10) UNSIGNED NOT NULL,
-  `contact_id` int(10) UNSIGNED NOT NULL DEFAULT '0',
-  `created` datetime NOT NULL DEFAULT '1000-01-01 00:00:00',
-  PRIMARY KEY (`contactgroup_id`, `contact_id`),
-  CONSTRAINT `contactgroup_id_fk_contactgroups` FOREIGN KEY (`contactgroup_id`)
-    REFERENCES `contactgroups`(`contactgroup_id`) ON DELETE CASCADE ON UPDATE CASCADE,
-  CONSTRAINT `contact_id_fk_contacts` FOREIGN KEY (`contact_id`)
-    REFERENCES `contacts`(`contact_id`) ON DELETE CASCADE ON UPDATE CASCADE
-) /*!40000 ENGINE=INNODB */;
-
-/*!40014 SET FOREIGN_KEY_CHECKS=1 */;
-
--- Updates from version 0.4-beta
-
-ALTER TABLE `users` CHANGE `last_login` `last_login` datetime DEFAULT NULL;
-UPDATE `users` SET `last_login` = NULL WHERE `last_login` = '1000-01-01 00:00:00';
-
--- Updates from version 0.4.2
-
-ALTER TABLE `users` DROP INDEX `username_index`;
-ALTER TABLE `users` ADD UNIQUE `username` (`username`, `mail_host`);
-
-ALTER TABLE `contacts` MODIFY `email` varchar(255) NOT NULL;
-
-TRUNCATE TABLE `messages`;
-
--- Updates from version 0.5.1
--- Updates from version 0.5.2
--- Updates from version 0.5.3
--- Updates from version 0.5.4
-
-ALTER TABLE `contacts` ADD `words` TEXT NULL AFTER `vcard`;
-ALTER TABLE `contacts` CHANGE `vcard` `vcard` LONGTEXT /*!40101 CHARACTER SET utf8 */ NULL DEFAULT NULL;
-ALTER TABLE `contactgroupmembers` ADD INDEX `contactgroupmembers_contact_index` (`contact_id`);
-
-TRUNCATE TABLE `messages`;
-TRUNCATE TABLE `cache`;
-
--- Updates from version 0.6
-
-/*!40014 SET FOREIGN_KEY_CHECKS=0 */;
-
-ALTER TABLE `users` CHANGE `alias` `alias` varchar(128) BINARY NOT NULL;
-ALTER TABLE `users` CHANGE `username` `username` varchar(128) BINARY NOT NULL;
-
-CREATE TABLE `dictionary` (
-  `user_id` int(10) UNSIGNED DEFAULT NULL,
-  `language` varchar(5) NOT NULL,
-  `data` longtext NOT NULL,
-  CONSTRAINT `user_id_fk_dictionary` FOREIGN KEY (`user_id`)
-    REFERENCES `users`(`user_id`) ON DELETE CASCADE ON UPDATE CASCADE,
-  UNIQUE `uniqueness` (`user_id`, `language`)
-) /*!40000 ENGINE=INNODB */ /*!40101 CHARACTER SET utf8 COLLATE utf8_general_ci */;
-
-CREATE TABLE `searches` (
-  `search_id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
-  `user_id` int(10) UNSIGNED NOT NULL DEFAULT '0',
-  `type` int(3) NOT NULL DEFAULT '0',
-  `name` varchar(128) NOT NULL,
-  `data` text,
-  PRIMARY KEY(`search_id`),
-  CONSTRAINT `user_id_fk_searches` FOREIGN KEY (`user_id`)
-    REFERENCES `users`(`user_id`) ON DELETE CASCADE ON UPDATE CASCADE,
-  UNIQUE `uniqueness` (`user_id`, `type`, `name`)
-) /*!40000 ENGINE=INNODB */ /*!40101 CHARACTER SET utf8 COLLATE utf8_general_ci */;
-
-DROP TABLE `messages`;
-
-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',
- `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`),
- PRIMARY KEY (`user_id`, `mailbox`)
-) /*!40000 ENGINE=INNODB */ /*!40101 CHARACTER SET utf8 COLLATE utf8_general_ci */;
-
-CREATE TABLE `cache_thread` (
- `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_thread` FOREIGN KEY (`user_id`)
-   REFERENCES `users`(`user_id`) ON DELETE CASCADE ON UPDATE CASCADE,
- INDEX `changed_index` (`changed`),
- PRIMARY KEY (`user_id`, `mailbox`)
-) /*!40000 ENGINE=INNODB */ /*!40101 CHARACTER SET utf8 COLLATE utf8_general_ci */;
-
-CREATE TABLE `cache_messages` (
- `user_id` int(10) UNSIGNED NOT NULL DEFAULT '0',
- `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',
- `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`),
- PRIMARY KEY (`user_id`, `mailbox`, `uid`)
-) /*!40000 ENGINE=INNODB */ /*!40101 CHARACTER SET utf8 COLLATE utf8_general_ci */;
-
-/*!40014 SET FOREIGN_KEY_CHECKS=1 */;
-
--- Updates from version 0.7-beta
-
-ALTER TABLE `session` CHANGE `sess_id` `sess_id` varchar(128) NOT NULL;
-
--- Updates from version 0.7
-
-/*!40014 SET FOREIGN_KEY_CHECKS=0 */;
-
-ALTER TABLE `contacts` DROP FOREIGN KEY `user_id_fk_contacts`;
-ALTER TABLE `contacts` DROP INDEX `user_contacts_index`;
-ALTER TABLE `contacts` MODIFY `email` text NOT NULL;
-ALTER TABLE `contacts` ADD INDEX `user_contacts_index` (`user_id`,`del`);
-ALTER TABLE `contacts` ADD CONSTRAINT `user_id_fk_contacts` FOREIGN KEY (`user_id`)
-   REFERENCES `users`(`user_id`) ON DELETE CASCADE ON UPDATE CASCADE;
-
-ALTER TABLE `cache` ALTER `user_id` DROP DEFAULT;
-ALTER TABLE `cache_index` ALTER `user_id` DROP DEFAULT;
-ALTER TABLE `cache_thread` ALTER `user_id` DROP DEFAULT;
-ALTER TABLE `cache_messages` ALTER `user_id` DROP DEFAULT;
-ALTER TABLE `contacts` ALTER `user_id` DROP DEFAULT;
-ALTER TABLE `contactgroups` ALTER `user_id` DROP DEFAULT;
-ALTER TABLE `contactgroupmembers` ALTER `contact_id` DROP DEFAULT;
-ALTER TABLE `identities` ALTER `user_id` DROP DEFAULT;
-ALTER TABLE `searches` ALTER `user_id` DROP DEFAULT;
-
-/*!40014 SET FOREIGN_KEY_CHECKS=1 */;
-
--- Updates from version 0.8
-
-ALTER TABLE `cache` DROP COLUMN `cache_id`;
-ALTER TABLE `users` DROP COLUMN `alias`;
-ALTER TABLE `identities` ADD INDEX `email_identities_index` (`email`, `del`);
diff --git a/SQL/mysql/2008030300.sql b/SQL/mysql/2008030300.sql
new file mode 100644
index 000000000..9a3d04847
--- /dev/null
+++ b/SQL/mysql/2008030300.sql
@@ -0,0 +1,16 @@
+-- Updates from version 0.1-stable
+
+TRUNCATE TABLE `messages`;
+
+ALTER TABLE `messages`
+  DROP INDEX `idx`,
+  DROP INDEX `uid`;
+
+ALTER TABLE `cache`
+  DROP INDEX `cache_key`,
+  DROP INDEX `session_id`,
+  ADD INDEX `user_cache_index` (`user_id`,`cache_key`);
+
+ALTER TABLE `users`
+    ADD INDEX `username_index` (`username`),
+    ADD INDEX `alias_index` (`alias`);
diff --git a/SQL/mysql/2008040500.sql b/SQL/mysql/2008040500.sql
new file mode 100644
index 000000000..f538f6378
--- /dev/null
+++ b/SQL/mysql/2008040500.sql
@@ -0,0 +1,9 @@
+-- Updates from version 0.1.1
+
+ALTER TABLE `identities`
+    MODIFY `signature` text, 
+    MODIFY `bcc` varchar(128) NOT NULL DEFAULT '', 
+    MODIFY `reply-to` varchar(128) NOT NULL DEFAULT '', 
+    MODIFY `organization` varchar(128) NOT NULL DEFAULT '',
+    MODIFY `name` varchar(128) NOT NULL, 
+    MODIFY `email` varchar(128) NOT NULL; 
diff --git a/SQL/mysql/2008060900.sql b/SQL/mysql/2008060900.sql
new file mode 100644
index 000000000..9f8de0a54
--- /dev/null
+++ b/SQL/mysql/2008060900.sql
@@ -0,0 +1,4 @@
+-- Updates from version 0.2-alpha
+
+ALTER TABLE `messages`
+    ADD INDEX `created_index` (`created`);
diff --git a/SQL/mysql/2008092100.sql b/SQL/mysql/2008092100.sql
new file mode 100644
index 000000000..3989c75f2
--- /dev/null
+++ b/SQL/mysql/2008092100.sql
@@ -0,0 +1,20 @@
+-- Updates from version 0.2-beta (InnoDB required)
+
+ALTER TABLE `cache`
+    DROP `session_id`;
+
+ALTER TABLE `session`
+    ADD INDEX `changed_index` (`changed`);
+
+ALTER TABLE `cache`
+    ADD INDEX `created_index` (`created`);
+
+ALTER TABLE `users`
+    CHANGE `language` `language` varchar(5);
+
+ALTER TABLE `cache` ENGINE=InnoDB;
+ALTER TABLE `session` ENGINE=InnoDB;
+ALTER TABLE `messages` ENGINE=InnoDB;
+ALTER TABLE `users` ENGINE=InnoDB;
+ALTER TABLE `contacts` ENGINE=InnoDB;
+ALTER TABLE `identities` ENGINE=InnoDB;
diff --git a/SQL/mysql/2009090400.sql b/SQL/mysql/2009090400.sql
new file mode 100644
index 000000000..fd31bed5b
--- /dev/null
+++ b/SQL/mysql/2009090400.sql
@@ -0,0 +1,12 @@
+-- Updates from version 0.3-stable
+
+TRUNCATE `messages`;
+
+ALTER TABLE `messages`
+    ADD INDEX `index_index` (`user_id`, `cache_key`, `idx`);
+
+ALTER TABLE `session` 
+    CHANGE `vars` `vars` MEDIUMTEXT NOT NULL;
+
+ALTER TABLE `contacts`
+    ADD INDEX `user_contacts_index` (`user_id`,`email`);
diff --git a/SQL/mysql/2009103100.sql b/SQL/mysql/2009103100.sql
new file mode 100644
index 000000000..aafbe238f
--- /dev/null
+++ b/SQL/mysql/2009103100.sql
@@ -0,0 +1,52 @@
+-- Updates from version 0.3.1
+-- WARNING: Make sure that all tables are using InnoDB engine!!!
+--          If not, use: ALTER TABLE xxx ENGINE=InnoDB;
+
+/* MySQL bug workaround: http://bugs.mysql.com/bug.php?id=46293 */
+/*!40014 SET FOREIGN_KEY_CHECKS=0 */;
+
+ALTER TABLE `messages` DROP FOREIGN KEY `user_id_fk_messages`;
+ALTER TABLE `cache` DROP FOREIGN KEY `user_id_fk_cache`;
+ALTER TABLE `contacts` DROP FOREIGN KEY `user_id_fk_contacts`;
+ALTER TABLE `identities` DROP FOREIGN KEY `user_id_fk_identities`;
+
+ALTER TABLE `messages` ADD CONSTRAINT `user_id_fk_messages` FOREIGN KEY (`user_id`)
+ REFERENCES `users`(`user_id`) ON DELETE CASCADE ON UPDATE CASCADE;
+ALTER TABLE `cache` ADD CONSTRAINT `user_id_fk_cache` FOREIGN KEY (`user_id`)
+ REFERENCES `users`(`user_id`) ON DELETE CASCADE ON UPDATE CASCADE;
+ALTER TABLE `contacts` ADD CONSTRAINT `user_id_fk_contacts` FOREIGN KEY (`user_id`)
+ REFERENCES `users`(`user_id`) ON DELETE CASCADE ON UPDATE CASCADE;
+ALTER TABLE `identities` ADD CONSTRAINT `user_id_fk_identities` FOREIGN KEY (`user_id`)
+ REFERENCES `users`(`user_id`) ON DELETE CASCADE ON UPDATE CASCADE;
+
+ALTER TABLE `contacts` ALTER `name` SET DEFAULT '';
+ALTER TABLE `contacts` ALTER `firstname` SET DEFAULT '';
+ALTER TABLE `contacts` ALTER `surname` SET DEFAULT '';
+
+ALTER TABLE `identities` ADD INDEX `user_identities_index` (`user_id`, `del`);
+ALTER TABLE `identities` ADD `changed` datetime NOT NULL DEFAULT '1000-01-01 00:00:00' AFTER `user_id`;
+
+CREATE TABLE `contactgroups` (
+  `contactgroup_id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
+  `user_id` int(10) UNSIGNED NOT NULL DEFAULT '0',
+  `changed` datetime NOT NULL DEFAULT '1000-01-01 00:00:00',
+  `del` tinyint(1) NOT NULL DEFAULT '0',
+  `name` varchar(128) NOT NULL DEFAULT '',
+  PRIMARY KEY(`contactgroup_id`),
+  CONSTRAINT `user_id_fk_contactgroups` FOREIGN KEY (`user_id`)
+    REFERENCES `users`(`user_id`) ON DELETE CASCADE ON UPDATE CASCADE,
+  INDEX `contactgroups_user_index` (`user_id`,`del`)
+) /*!40000 ENGINE=INNODB */ /*!40101 CHARACTER SET utf8 COLLATE utf8_general_ci */;
+
+CREATE TABLE `contactgroupmembers` (
+  `contactgroup_id` int(10) UNSIGNED NOT NULL,
+  `contact_id` int(10) UNSIGNED NOT NULL DEFAULT '0',
+  `created` datetime NOT NULL DEFAULT '1000-01-01 00:00:00',
+  PRIMARY KEY (`contactgroup_id`, `contact_id`),
+  CONSTRAINT `contactgroup_id_fk_contactgroups` FOREIGN KEY (`contactgroup_id`)
+    REFERENCES `contactgroups`(`contactgroup_id`) ON DELETE CASCADE ON UPDATE CASCADE,
+  CONSTRAINT `contact_id_fk_contacts` FOREIGN KEY (`contact_id`)
+    REFERENCES `contacts`(`contact_id`) ON DELETE CASCADE ON UPDATE CASCADE
+) /*!40000 ENGINE=INNODB */;
+
+/*!40014 SET FOREIGN_KEY_CHECKS=1 */;
diff --git a/SQL/mysql/2010042300.sql b/SQL/mysql/2010042300.sql
new file mode 100644
index 000000000..8b90af45e
--- /dev/null
+++ b/SQL/mysql/2010042300.sql
@@ -0,0 +1,4 @@
+-- Updates from version 0.4-beta
+
+ALTER TABLE `users` CHANGE `last_login` `last_login` datetime DEFAULT NULL;
+UPDATE `users` SET `last_login` = NULL WHERE `last_login` = '1000-01-01 00:00:00';
diff --git a/SQL/mysql/2010100600.sql b/SQL/mysql/2010100600.sql
new file mode 100644
index 000000000..ca0fec858
--- /dev/null
+++ b/SQL/mysql/2010100600.sql
@@ -0,0 +1,8 @@
+-- Updates from version 0.4.2
+
+ALTER TABLE `users` DROP INDEX `username_index`;
+ALTER TABLE `users` ADD UNIQUE `username` (`username`, `mail_host`);
+
+ALTER TABLE `contacts` MODIFY `email` varchar(255) NOT NULL;
+
+TRUNCATE TABLE `messages`;
diff --git a/SQL/mysql/2011011200.sql b/SQL/mysql/2011011200.sql
new file mode 100644
index 000000000..6597034c2
--- /dev/null
+++ b/SQL/mysql/2011011200.sql
@@ -0,0 +1,8 @@
+-- Updates from version 0.5.x
+
+ALTER TABLE `contacts` ADD `words` TEXT NULL AFTER `vcard`;
+ALTER TABLE `contacts` CHANGE `vcard` `vcard` LONGTEXT /*!40101 CHARACTER SET utf8 */ NULL DEFAULT NULL;
+ALTER TABLE `contactgroupmembers` ADD INDEX `contactgroupmembers_contact_index` (`contact_id`);
+
+TRUNCATE TABLE `messages`;
+TRUNCATE TABLE `cache`;
diff --git a/SQL/mysql/2011092800.sql b/SQL/mysql/2011092800.sql
new file mode 100644
index 000000000..6b7cbe1f3
--- /dev/null
+++ b/SQL/mysql/2011092800.sql
@@ -0,0 +1,67 @@
+-- Updates from version 0.6
+
+/*!40014 SET FOREIGN_KEY_CHECKS=0 */;
+
+ALTER TABLE `users` CHANGE `alias` `alias` varchar(128) BINARY NOT NULL;
+ALTER TABLE `users` CHANGE `username` `username` varchar(128) BINARY NOT NULL;
+
+CREATE TABLE `dictionary` (
+  `user_id` int(10) UNSIGNED DEFAULT NULL,
+  `language` varchar(5) NOT NULL,
+  `data` longtext NOT NULL,
+  CONSTRAINT `user_id_fk_dictionary` FOREIGN KEY (`user_id`)
+    REFERENCES `users`(`user_id`) ON DELETE CASCADE ON UPDATE CASCADE,
+  UNIQUE `uniqueness` (`user_id`, `language`)
+) /*!40000 ENGINE=INNODB */ /*!40101 CHARACTER SET utf8 COLLATE utf8_general_ci */;
+
+CREATE TABLE `searches` (
+  `search_id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
+  `user_id` int(10) UNSIGNED NOT NULL DEFAULT '0',
+  `type` int(3) NOT NULL DEFAULT '0',
+  `name` varchar(128) NOT NULL,
+  `data` text,
+  PRIMARY KEY(`search_id`),
+  CONSTRAINT `user_id_fk_searches` FOREIGN KEY (`user_id`)
+    REFERENCES `users`(`user_id`) ON DELETE CASCADE ON UPDATE CASCADE,
+  UNIQUE `uniqueness` (`user_id`, `type`, `name`)
+) /*!40000 ENGINE=INNODB */ /*!40101 CHARACTER SET utf8 COLLATE utf8_general_ci */;
+
+DROP TABLE `messages`;
+
+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',
+ `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`),
+ PRIMARY KEY (`user_id`, `mailbox`)
+) /*!40000 ENGINE=INNODB */ /*!40101 CHARACTER SET utf8 COLLATE utf8_general_ci */;
+
+CREATE TABLE `cache_thread` (
+ `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_thread` FOREIGN KEY (`user_id`)
+   REFERENCES `users`(`user_id`) ON DELETE CASCADE ON UPDATE CASCADE,
+ INDEX `changed_index` (`changed`),
+ PRIMARY KEY (`user_id`, `mailbox`)
+) /*!40000 ENGINE=INNODB */ /*!40101 CHARACTER SET utf8 COLLATE utf8_general_ci */;
+
+CREATE TABLE `cache_messages` (
+ `user_id` int(10) UNSIGNED NOT NULL DEFAULT '0',
+ `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',
+ `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`),
+ PRIMARY KEY (`user_id`, `mailbox`, `uid`)
+) /*!40000 ENGINE=INNODB */ /*!40101 CHARACTER SET utf8 COLLATE utf8_general_ci */;
+
+/*!40014 SET FOREIGN_KEY_CHECKS=1 */;
diff --git a/SQL/mysql/2011111600.sql b/SQL/mysql/2011111600.sql
new file mode 100644
index 000000000..6f53daa9c
--- /dev/null
+++ b/SQL/mysql/2011111600.sql
@@ -0,0 +1,3 @@
+-- Updates from version 0.7-beta
+
+ALTER TABLE `session` CHANGE `sess_id` `sess_id` varchar(128) NOT NULL;
diff --git a/SQL/mysql/2011121400.sql b/SQL/mysql/2011121400.sql
new file mode 100644
index 000000000..5aee80601
--- /dev/null
+++ b/SQL/mysql/2011121400.sql
@@ -0,0 +1,22 @@
+-- Updates from version 0.7
+
+/*!40014 SET FOREIGN_KEY_CHECKS=0 */;
+
+ALTER TABLE `contacts` DROP FOREIGN KEY `user_id_fk_contacts`;
+ALTER TABLE `contacts` DROP INDEX `user_contacts_index`;
+ALTER TABLE `contacts` MODIFY `email` text NOT NULL;
+ALTER TABLE `contacts` ADD INDEX `user_contacts_index` (`user_id`,`del`);
+ALTER TABLE `contacts` ADD CONSTRAINT `user_id_fk_contacts` FOREIGN KEY (`user_id`)
+   REFERENCES `users`(`user_id`) ON DELETE CASCADE ON UPDATE CASCADE;
+
+ALTER TABLE `cache` ALTER `user_id` DROP DEFAULT;
+ALTER TABLE `cache_index` ALTER `user_id` DROP DEFAULT;
+ALTER TABLE `cache_thread` ALTER `user_id` DROP DEFAULT;
+ALTER TABLE `cache_messages` ALTER `user_id` DROP DEFAULT;
+ALTER TABLE `contacts` ALTER `user_id` DROP DEFAULT;
+ALTER TABLE `contactgroups` ALTER `user_id` DROP DEFAULT;
+ALTER TABLE `contactgroupmembers` ALTER `contact_id` DROP DEFAULT;
+ALTER TABLE `identities` ALTER `user_id` DROP DEFAULT;
+ALTER TABLE `searches` ALTER `user_id` DROP DEFAULT;
+
+/*!40014 SET FOREIGN_KEY_CHECKS=1 */;
diff --git a/SQL/mysql/2012080700.sql b/SQL/mysql/2012080700.sql
new file mode 100644
index 000000000..789b7122c
--- /dev/null
+++ b/SQL/mysql/2012080700.sql
@@ -0,0 +1,5 @@
+-- Updates from version 0.8
+
+ALTER TABLE `cache` DROP COLUMN `cache_id`;
+ALTER TABLE `users` DROP COLUMN `alias`;
+ALTER TABLE `identities` ADD INDEX `email_identities_index` (`email`, `del`);
diff --git a/SQL/mysql/2013011000.sql b/SQL/mysql/2013011000.sql
new file mode 100644
index 000000000..f0b5e6a03
--- /dev/null
+++ b/SQL/mysql/2013011000.sql
@@ -0,0 +1,7 @@
+-- Upgrade from 0.9-beta
+
+CREATE TABLE `system` (
+ `name` varchar(64) NOT NULL,
+ `value` mediumtext,
+ PRIMARY KEY(`name`)
+) /*!40000 ENGINE=INNODB */ /*!40101 CHARACTER SET utf8 COLLATE utf8_general_ci */;
diff --git a/SQL/postgres.initial.sql b/SQL/postgres.initial.sql
index f7b2d96d9..d7dc08f90 100644
--- a/SQL/postgres.initial.sql
+++ b/SQL/postgres.initial.sql
@@ -262,3 +262,16 @@ CREATE TABLE searches (
     data text NOT NULL,
     CONSTRAINT searches_user_id_key UNIQUE (user_id, "type", name)
 );
+
+
+--
+-- Table "system"
+-- Name: system; Type: TABLE; Schema: public; Owner: postgres
+--
+
+CREATE TABLE "system" (
+    name varchar(64) NOT NULL PRIMARY KEY,
+    value text
+);
+
+INSERT INTO system (name, value) VALUES ('roundcube-version', '2013011000.sql');
diff --git a/SQL/postgres.update.sql b/SQL/postgres.update.sql
deleted file mode 100644
index 11ab93bfc..000000000
--- a/SQL/postgres.update.sql
+++ /dev/null
@@ -1,185 +0,0 @@
--- Roundcube Webmail update script for Postgres databases
--- Updates from version 0.1-stable to 0.1.1
-
-CREATE INDEX cache_user_id_idx ON cache (user_id, cache_key);
-CREATE INDEX contacts_user_id_idx ON contacts (user_id);
-CREATE INDEX identities_user_id_idx ON identities (user_id);
-
-CREATE INDEX users_username_id_idx ON users (username);
-CREATE INDEX users_alias_id_idx ON users (alias);
-
--- added ON DELETE/UPDATE actions
-ALTER TABLE messages DROP CONSTRAINT messages_user_id_fkey;
-ALTER TABLE messages ADD FOREIGN KEY (user_id) REFERENCES users(user_id) ON DELETE CASCADE ON UPDATE CASCADE;
-ALTER TABLE identities DROP CONSTRAINT identities_user_id_fkey;
-ALTER TABLE identities ADD FOREIGN KEY (user_id) REFERENCES users(user_id) ON DELETE CASCADE ON UPDATE CASCADE;
-ALTER TABLE contacts DROP CONSTRAINT contacts_user_id_fkey;
-ALTER TABLE contacts ADD FOREIGN KEY (user_id) REFERENCES users(user_id) ON DELETE CASCADE ON UPDATE CASCADE;
-ALTER TABLE cache DROP CONSTRAINT cache_user_id_fkey;
-ALTER TABLE cache ADD FOREIGN KEY (user_id) REFERENCES users(user_id) ON DELETE CASCADE ON UPDATE CASCADE;
-
--- Updates from version 0.2-alpha
-
-CREATE INDEX messages_created_idx ON messages (created);
-
--- Updates from version 0.2-beta
-
-ALTER TABLE cache DROP session_id;
-
-CREATE INDEX session_changed_idx ON session (changed);
-CREATE INDEX cache_created_idx ON "cache" (created);
-
-ALTER TABLE users ALTER "language" DROP NOT NULL;
-ALTER TABLE users ALTER "language" DROP DEFAULT;
-
-ALTER TABLE identities ALTER del TYPE smallint;
-ALTER TABLE identities ALTER standard TYPE smallint;
-ALTER TABLE contacts ALTER del TYPE smallint;
-ALTER TABLE messages ALTER del TYPE smallint;
-
--- Updates from version 0.3-stable
-
-TRUNCATE messages;
-CREATE INDEX messages_index_idx ON messages (user_id, cache_key, idx);
-DROP INDEX contacts_user_id_idx;
-CREATE INDEX contacts_user_id_idx ON contacts (user_id, email);
-
--- Updates from version 0.3.1
-
-DROP INDEX identities_user_id_idx;
-CREATE INDEX identities_user_id_idx ON identities (user_id, del);
-
-ALTER TABLE identities ADD changed timestamp with time zone DEFAULT now() NOT NULL;
-
-CREATE SEQUENCE contactgroups_ids
-    INCREMENT BY 1
-    NO MAXVALUE
-    NO MINVALUE
-    CACHE 1;
-
-CREATE TABLE contactgroups (
-    contactgroup_id integer DEFAULT nextval('contactgroups_ids'::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,
-    del 	smallint 	NOT NULL DEFAULT 0,
-    name 	varchar(128) 	NOT NULL DEFAULT ''
-);
-
-CREATE INDEX contactgroups_user_id_idx ON contactgroups (user_id, del);
-
-CREATE TABLE contactgroupmembers (
-    contactgroup_id 	integer NOT NULL
-	REFERENCES contactgroups(contactgroup_id) ON DELETE CASCADE ON UPDATE CASCADE,
-    contact_id 		integer NOT NULL
-	REFERENCES contacts(contact_id) ON DELETE CASCADE ON UPDATE CASCADE,
-    created timestamp with time zone DEFAULT now() NOT NULL,
-    PRIMARY KEY (contactgroup_id, contact_id)
-);
-
--- Updates from version 0.4-beta
-
-ALTER TABLE users ALTER last_login DROP NOT NULL;
-ALTER TABLE users ALTER last_login SET DEFAULT NULL;
-
--- Updates from version 0.4.2
-
-DROP INDEX users_username_id_idx;
-ALTER TABLE users ADD CONSTRAINT users_username_key UNIQUE (username, mail_host);
-ALTER TABLE contacts ALTER email TYPE varchar(255);
-
-TRUNCATE messages;
-
--- Updates from version 0.5.1
--- Updates from version 0.5.2
--- Updates from version 0.5.3
--- Updates from version 0.5.4
-
-ALTER TABLE contacts ADD words TEXT NULL;
-CREATE INDEX contactgroupmembers_contact_id_idx ON contactgroupmembers (contact_id);
-
-TRUNCATE messages;
-TRUNCATE cache;
-
--- Updates from version 0.6
-
-CREATE TABLE dictionary (
-    user_id integer DEFAULT NULL
-        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")
-);
-
-CREATE SEQUENCE search_ids
-    INCREMENT BY 1
-    NO MAXVALUE
-    NO MINVALUE
-    CACHE 1;
-
-CREATE TABLE searches (
-    search_id integer DEFAULT nextval('search_ids'::text) PRIMARY KEY,
-    user_id integer NOT NULL
-        REFERENCES users (user_id) ON DELETE CASCADE ON UPDATE CASCADE,
-    "type" smallint DEFAULT 0 NOT NULL,
-    name varchar(128) NOT NULL,
-    data text NOT NULL,
-    CONSTRAINT searches_user_id_key UNIQUE (user_id, "type", name)
-);
-
-DROP SEQUENCE message_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,
-    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 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,
-    flags integer NOT NULL DEFAULT 0,
-    PRIMARY KEY (user_id, mailbox, uid)
-);
-
-CREATE INDEX cache_messages_changed_idx ON cache_messages (changed);
-
--- Updates from version 0.7-beta
-
-ALTER TABLE "session" ALTER sess_id TYPE varchar(128);
-
--- Updates from version 0.7
-
-DROP INDEX contacts_user_id_idx;
-CREATE INDEX contacts_user_id_idx ON contacts USING btree (user_id, del);
-ALTER TABLE contacts ALTER email TYPE text;
-
--- Updates from version 0.8
-
-ALTER TABLE cache DROP COLUMN cache_id;
-DROP SEQUENCE cache_ids;
-
-ALTER TABLE users DROP COLUMN alias;
-CREATE INDEX identities_email_idx ON identities (email, del);
diff --git a/SQL/postgres/2008030300.sql b/SQL/postgres/2008030300.sql
new file mode 100644
index 000000000..67a8f158f
--- /dev/null
+++ b/SQL/postgres/2008030300.sql
@@ -0,0 +1,18 @@
+-- Updates from version 0.1-stable to 0.1.1
+
+CREATE INDEX cache_user_id_idx ON cache (user_id, cache_key);
+CREATE INDEX contacts_user_id_idx ON contacts (user_id);
+CREATE INDEX identities_user_id_idx ON identities (user_id);
+
+CREATE INDEX users_username_id_idx ON users (username);
+CREATE INDEX users_alias_id_idx ON users (alias);
+
+-- added ON DELETE/UPDATE actions
+ALTER TABLE messages DROP CONSTRAINT messages_user_id_fkey;
+ALTER TABLE messages ADD FOREIGN KEY (user_id) REFERENCES users(user_id) ON DELETE CASCADE ON UPDATE CASCADE;
+ALTER TABLE identities DROP CONSTRAINT identities_user_id_fkey;
+ALTER TABLE identities ADD FOREIGN KEY (user_id) REFERENCES users(user_id) ON DELETE CASCADE ON UPDATE CASCADE;
+ALTER TABLE contacts DROP CONSTRAINT contacts_user_id_fkey;
+ALTER TABLE contacts ADD FOREIGN KEY (user_id) REFERENCES users(user_id) ON DELETE CASCADE ON UPDATE CASCADE;
+ALTER TABLE cache DROP CONSTRAINT cache_user_id_fkey;
+ALTER TABLE cache ADD FOREIGN KEY (user_id) REFERENCES users(user_id) ON DELETE CASCADE ON UPDATE CASCADE;
diff --git a/SQL/postgres/2008060900.sql b/SQL/postgres/2008060900.sql
new file mode 100644
index 000000000..7c60a4422
--- /dev/null
+++ b/SQL/postgres/2008060900.sql
@@ -0,0 +1,3 @@
+-- Updates from version 0.2-alpha
+
+CREATE INDEX messages_created_idx ON messages (created);
diff --git a/SQL/postgres/2008092100.sql b/SQL/postgres/2008092100.sql
new file mode 100644
index 000000000..6cd19290c
--- /dev/null
+++ b/SQL/postgres/2008092100.sql
@@ -0,0 +1,14 @@
+-- Updates from version 0.2-beta
+
+ALTER TABLE cache DROP session_id;
+
+CREATE INDEX session_changed_idx ON session (changed);
+CREATE INDEX cache_created_idx ON "cache" (created);
+
+ALTER TABLE users ALTER "language" DROP NOT NULL;
+ALTER TABLE users ALTER "language" DROP DEFAULT;
+
+ALTER TABLE identities ALTER del TYPE smallint;
+ALTER TABLE identities ALTER standard TYPE smallint;
+ALTER TABLE contacts ALTER del TYPE smallint;
+ALTER TABLE messages ALTER del TYPE smallint;
diff --git a/SQL/postgres/2009090400.sql b/SQL/postgres/2009090400.sql
new file mode 100644
index 000000000..8eb4949e8
--- /dev/null
+++ b/SQL/postgres/2009090400.sql
@@ -0,0 +1,6 @@
+-- Updates from version 0.3-stable
+
+TRUNCATE messages;
+CREATE INDEX messages_index_idx ON messages (user_id, cache_key, idx);
+DROP INDEX contacts_user_id_idx;
+CREATE INDEX contacts_user_id_idx ON contacts (user_id, email);
diff --git a/SQL/postgres/2009103100.sql b/SQL/postgres/2009103100.sql
new file mode 100644
index 000000000..2350f79f7
--- /dev/null
+++ b/SQL/postgres/2009103100.sql
@@ -0,0 +1,32 @@
+-- Updates from version 0.3.1
+
+DROP INDEX identities_user_id_idx;
+CREATE INDEX identities_user_id_idx ON identities (user_id, del);
+
+ALTER TABLE identities ADD changed timestamp with time zone DEFAULT now() NOT NULL;
+
+CREATE SEQUENCE contactgroups_ids
+    INCREMENT BY 1
+    NO MAXVALUE
+    NO MINVALUE
+    CACHE 1;
+
+CREATE TABLE contactgroups (
+    contactgroup_id integer DEFAULT nextval('contactgroups_ids'::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,
+    del smallint NOT NULL DEFAULT 0,
+    name varchar(128) NOT NULL DEFAULT ''
+);
+
+CREATE INDEX contactgroups_user_id_idx ON contactgroups (user_id, del);
+
+CREATE TABLE contactgroupmembers (
+    contactgroup_id integer NOT NULL
+        REFERENCES contactgroups(contactgroup_id) ON DELETE CASCADE ON UPDATE CASCADE,
+    contact_id integer NOT NULL
+        REFERENCES contacts(contact_id) ON DELETE CASCADE ON UPDATE CASCADE,
+    created timestamp with time zone DEFAULT now() NOT NULL,
+    PRIMARY KEY (contactgroup_id, contact_id)
+);
diff --git a/SQL/postgres/2010042300.sql b/SQL/postgres/2010042300.sql
new file mode 100644
index 000000000..bc9bd826a
--- /dev/null
+++ b/SQL/postgres/2010042300.sql
@@ -0,0 +1,4 @@
+-- Updates from version 0.4-beta
+
+ALTER TABLE users ALTER last_login DROP NOT NULL;
+ALTER TABLE users ALTER last_login SET DEFAULT NULL;
diff --git a/SQL/postgres/2010100600.sql b/SQL/postgres/2010100600.sql
new file mode 100644
index 000000000..8dfcc12df
--- /dev/null
+++ b/SQL/postgres/2010100600.sql
@@ -0,0 +1,7 @@
+-- Updates from version 0.4.2
+
+DROP INDEX users_username_id_idx;
+ALTER TABLE users ADD CONSTRAINT users_username_key UNIQUE (username, mail_host);
+ALTER TABLE contacts ALTER email TYPE varchar(255);
+
+TRUNCATE messages;
diff --git a/SQL/postgres/2011011200.sql b/SQL/postgres/2011011200.sql
new file mode 100644
index 000000000..db468d8e8
--- /dev/null
+++ b/SQL/postgres/2011011200.sql
@@ -0,0 +1,7 @@
+-- Updates from version 0.5.x
+
+ALTER TABLE contacts ADD words TEXT NULL;
+CREATE INDEX contactgroupmembers_contact_id_idx ON contactgroupmembers (contact_id);
+
+TRUNCATE messages;
+TRUNCATE cache;
diff --git a/SQL/postgres/2011092800.sql b/SQL/postgres/2011092800.sql
new file mode 100644
index 000000000..fac3cd371
--- /dev/null
+++ b/SQL/postgres/2011092800.sql
@@ -0,0 +1,64 @@
+-- Updates from version 0.6
+
+CREATE TABLE dictionary (
+    user_id integer DEFAULT NULL
+        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")
+);
+
+CREATE SEQUENCE search_ids
+    INCREMENT BY 1
+    NO MAXVALUE
+    NO MINVALUE
+    CACHE 1;
+
+CREATE TABLE searches (
+    search_id integer DEFAULT nextval('search_ids'::text) PRIMARY KEY,
+    user_id integer NOT NULL
+        REFERENCES users (user_id) ON DELETE CASCADE ON UPDATE CASCADE,
+    "type" smallint DEFAULT 0 NOT NULL,
+    name varchar(128) NOT NULL,
+    data text NOT NULL,
+    CONSTRAINT searches_user_id_key UNIQUE (user_id, "type", name)
+);
+
+DROP SEQUENCE message_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,
+    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 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,
+    flags integer NOT NULL DEFAULT 0,
+    PRIMARY KEY (user_id, mailbox, uid)
+);
+
+CREATE INDEX cache_messages_changed_idx ON cache_messages (changed);
diff --git a/SQL/postgres/2011111600.sql b/SQL/postgres/2011111600.sql
new file mode 100644
index 000000000..a382ba0ba
--- /dev/null
+++ b/SQL/postgres/2011111600.sql
@@ -0,0 +1,3 @@
+-- Updates from version 0.7-beta
+
+ALTER TABLE "session" ALTER sess_id TYPE varchar(128);
diff --git a/SQL/postgres/2011121400.sql b/SQL/postgres/2011121400.sql
new file mode 100644
index 000000000..81a0cb880
--- /dev/null
+++ b/SQL/postgres/2011121400.sql
@@ -0,0 +1,5 @@
+-- Updates from version 0.7
+
+DROP INDEX contacts_user_id_idx;
+CREATE INDEX contacts_user_id_idx ON contacts USING btree (user_id, del);
+ALTER TABLE contacts ALTER email TYPE text;
diff --git a/SQL/postgres/2012080700.sql b/SQL/postgres/2012080700.sql
new file mode 100644
index 000000000..41f60168e
--- /dev/null
+++ b/SQL/postgres/2012080700.sql
@@ -0,0 +1,7 @@
+-- Updates from version 0.8
+
+ALTER TABLE cache DROP COLUMN cache_id;
+DROP SEQUENCE cache_ids;
+
+ALTER TABLE users DROP COLUMN alias;
+CREATE INDEX identities_email_idx ON identities (email, del);
diff --git a/SQL/postgres/2013011000.sql b/SQL/postgres/2013011000.sql
new file mode 100644
index 000000000..a8cf917d0
--- /dev/null
+++ b/SQL/postgres/2013011000.sql
@@ -0,0 +1,4 @@
+CREATE TABLE "system" (
+    name varchar(64) NOT NULL PRIMARY KEY,
+    value text
+);
diff --git a/SQL/sqlite.initial.sql b/SQL/sqlite.initial.sql
index f5b5615d8..76913e89c 100644
--- a/SQL/sqlite.initial.sql
+++ b/SQL/sqlite.initial.sql
@@ -197,3 +197,16 @@ CREATE TABLE cache_messages (
 );
 
 CREATE INDEX ix_cache_messages_changed ON cache_messages (changed);
+
+-- --------------------------------------------------------
+
+--
+-- Table structure for table system
+--
+
+CREATE TABLE system (
+  name varchar(64) NOT NULL PRIMARY KEY,
+  value text NOT NULL
+);
+
+INSERT INTO system (name, value) VALUES ('roundcube-version', '2013011000.sql');
diff --git a/SQL/sqlite.update.sql b/SQL/sqlite.update.sql
deleted file mode 100644
index 72a29e9ae..000000000
--- a/SQL/sqlite.update.sql
+++ /dev/null
@@ -1,380 +0,0 @@
--- Roundcube Webmail update script for SQLite databases
--- Updates from version 0.1-stable to 0.1.1
-
-DROP TABLE messages;
-
-CREATE TABLE messages (
-  message_id integer NOT NULL PRIMARY KEY,
-  user_id integer NOT NULL default '0',
-  del tinyint NOT NULL default '0',
-  cache_key varchar(128) NOT NULL default '',
-  created datetime NOT NULL default '0000-00-00 00:00:00',
-  idx integer NOT NULL default '0',
-  uid integer NOT NULL default '0',
-  subject varchar(255) NOT NULL default '',
-  "from" varchar(255) NOT NULL default '',
-  "to" varchar(255) NOT NULL default '',
-  "cc" varchar(255) NOT NULL default '',
-  "date" datetime NOT NULL default '0000-00-00 00:00:00',
-  size integer NOT NULL default '0',
-  headers text NOT NULL,
-  structure text
-);
-
-CREATE INDEX ix_messages_user_cache_uid ON messages(user_id,cache_key,uid);
-CREATE INDEX ix_users_username ON users(username);
-CREATE INDEX ix_users_alias ON users(alias);
-
--- Updates from version 0.2-alpha
-
-CREATE INDEX ix_messages_created ON messages (created);
-
--- Updates from version 0.2-beta
-
-CREATE INDEX ix_session_changed ON session (changed);
-CREATE INDEX ix_cache_created ON cache (created);
-
--- Updates from version 0.3-stable
-
-DELETE FROM messages;
-DROP INDEX ix_messages_user_cache_uid;
-CREATE UNIQUE INDEX ix_messages_user_cache_uid ON messages (user_id,cache_key,uid);
-CREATE INDEX ix_messages_index ON messages (user_id,cache_key,idx);
-DROP INDEX ix_contacts_user_id;
-CREATE INDEX ix_contacts_user_id ON contacts(user_id, email);
-
--- Updates from version 0.3.1
-
--- ALTER TABLE identities ADD COLUMN changed datetime NOT NULL default '0000-00-00 00:00:00'; --
-
-CREATE TABLE temp_identities (
-  identity_id integer NOT NULL PRIMARY KEY,
-  user_id integer NOT NULL default '0',
-  standard tinyint NOT NULL default '0',
-  name varchar(128) NOT NULL default '',
-  organization varchar(128) default '',
-  email varchar(128) NOT NULL default '',
-  "reply-to" varchar(128) NOT NULL default '',
-  bcc varchar(128) NOT NULL default '',
-  signature text NOT NULL default '',
-  html_signature tinyint NOT NULL default '0'
-);
-INSERT INTO temp_identities (identity_id, user_id, standard, name, organization, email, "reply-to", bcc, signature, html_signature)
-  SELECT identity_id, user_id, standard, name, organization, email, "reply-to", bcc, signature, html_signature
-  FROM identities WHERE del=0;
-
-DROP INDEX ix_identities_user_id;
-DROP TABLE identities;
-
-CREATE TABLE identities (
-  identity_id integer NOT NULL PRIMARY KEY,
-  user_id integer NOT NULL default '0',
-  changed datetime NOT NULL default '0000-00-00 00:00:00',
-  del tinyint NOT NULL default '0',
-  standard tinyint NOT NULL default '0',
-  name varchar(128) NOT NULL default '',
-  organization varchar(128) default '',
-  email varchar(128) NOT NULL default '',
-  "reply-to" varchar(128) NOT NULL default '',
-  bcc varchar(128) NOT NULL default '',
-  signature text NOT NULL default '',
-  html_signature tinyint NOT NULL default '0'
-);
-CREATE INDEX ix_identities_user_id ON identities(user_id, del);
-
-INSERT INTO identities (identity_id, user_id, standard, name, organization, email, "reply-to", bcc, signature, html_signature)
-  SELECT identity_id, user_id, standard, name, organization, email, "reply-to", bcc, signature, html_signature
-  FROM temp_identities;
-
-DROP TABLE temp_identities;
-
-CREATE TABLE contactgroups (
-  contactgroup_id integer NOT NULL PRIMARY KEY,
-  user_id integer NOT NULL default '0',
-  changed datetime NOT NULL default '0000-00-00 00:00:00',
-  del tinyint NOT NULL default '0',
-  name varchar(128) NOT NULL default ''
-);
-
-CREATE INDEX ix_contactgroups_user_id ON contactgroups(user_id, del);
-
-CREATE TABLE contactgroupmembers (
-  contactgroup_id integer NOT NULL,
-  contact_id integer NOT NULL default '0',
-  created datetime NOT NULL default '0000-00-00 00:00:00',
-  PRIMARY KEY (contactgroup_id, contact_id)
-);
-
--- Updates from version 0.3.1
-
-CREATE TABLE tmp_users (
-  user_id integer NOT NULL PRIMARY KEY,
-  username varchar(128) NOT NULL default '',
-  mail_host varchar(128) NOT NULL default '',
-  alias varchar(128) NOT NULL default '',
-  created datetime NOT NULL default '0000-00-00 00:00:00',
-  last_login datetime NOT NULL default '0000-00-00 00:00:00',
-  language varchar(5),
-  preferences text NOT NULL default ''
-);
-
-INSERT INTO tmp_users (user_id, username, mail_host, alias, created, last_login, language, preferences)
-    SELECT user_id, username, mail_host, alias, created, last_login, language, preferences FROM users;
-
-DROP TABLE users;
-
-CREATE TABLE users (
-  user_id integer NOT NULL PRIMARY KEY,
-  username varchar(128) NOT NULL default '',
-  mail_host varchar(128) NOT NULL default '',
-  alias varchar(128) NOT NULL default '',
-  created datetime NOT NULL default '0000-00-00 00:00:00',
-  last_login datetime DEFAULT NULL,
-  language varchar(5),
-  preferences text NOT NULL default ''
-);
-
-INSERT INTO users (user_id, username, mail_host, alias, created, last_login, language, preferences)
-    SELECT user_id, username, mail_host, alias, created, last_login, language, preferences FROM tmp_users;
-
-CREATE INDEX ix_users_username ON users(username);
-CREATE INDEX ix_users_alias ON users(alias);
-DROP TABLE tmp_users;
-
--- Updates from version 0.4.2
-
-DROP INDEX ix_users_username;
-CREATE UNIQUE INDEX ix_users_username ON users(username, mail_host);
-
-CREATE TABLE contacts_tmp (
-    contact_id integer NOT NULL PRIMARY KEY,
-    user_id integer NOT NULL default '0',
-    changed datetime NOT NULL default '0000-00-00 00:00:00',
-    del tinyint NOT NULL default '0',
-    name varchar(128) NOT NULL default '',
-    email varchar(255) NOT NULL default '',
-    firstname varchar(128) NOT NULL default '',
-    surname varchar(128) NOT NULL default '',
-    vcard text NOT NULL default ''
-);
-
-INSERT INTO contacts_tmp (contact_id, user_id, changed, del, name, email, firstname, surname, vcard)
-    SELECT contact_id, user_id, changed, del, name, email, firstname, surname, vcard FROM contacts;
-
-DROP TABLE contacts;
-CREATE TABLE contacts (
-    contact_id integer NOT NULL PRIMARY KEY,
-    user_id integer NOT NULL default '0',
-    changed datetime NOT NULL default '0000-00-00 00:00:00',
-    del tinyint NOT NULL default '0',
-    name varchar(128) NOT NULL default '',
-    email varchar(255) NOT NULL default '',
-    firstname varchar(128) NOT NULL default '',
-    surname varchar(128) NOT NULL default '',
-    vcard text NOT NULL default ''
-);
-
-INSERT INTO contacts (contact_id, user_id, changed, del, name, email, firstname, surname, vcard)
-    SELECT contact_id, user_id, changed, del, name, email, firstname, surname, vcard FROM contacts_tmp;
-
-CREATE INDEX ix_contacts_user_id ON contacts(user_id, email);
-DROP TABLE contacts_tmp;
-
-DELETE FROM messages;
-
-
--- Updates from version 0.5.1
--- Updates from version 0.5.2
--- Updates from version 0.5.3
--- Updates from version 0.5.4
-
-CREATE TABLE contacts_tmp (
-    contact_id integer NOT NULL PRIMARY KEY,
-    user_id integer NOT NULL default '0',
-    changed datetime NOT NULL default '0000-00-00 00:00:00',
-    del tinyint NOT NULL default '0',
-    name varchar(128) NOT NULL default '',
-    email varchar(255) NOT NULL default '',
-    firstname varchar(128) NOT NULL default '',
-    surname varchar(128) NOT NULL default '',
-    vcard text NOT NULL default ''
-);
-
-INSERT INTO contacts_tmp (contact_id, user_id, changed, del, name, email, firstname, surname, vcard)
-    SELECT contact_id, user_id, changed, del, name, email, firstname, surname, vcard FROM contacts;
-
-DROP TABLE contacts;
-CREATE TABLE contacts (
-    contact_id integer NOT NULL PRIMARY KEY,
-    user_id integer NOT NULL default '0',
-    changed datetime NOT NULL default '0000-00-00 00:00:00',
-    del tinyint NOT NULL default '0',
-    name varchar(128) NOT NULL default '',
-    email varchar(255) NOT NULL default '',
-    firstname varchar(128) NOT NULL default '',
-    surname varchar(128) NOT NULL default '',
-    vcard text NOT NULL default '',
-    words text NOT NULL default ''
-);
-
-INSERT INTO contacts (contact_id, user_id, changed, del, name, email, firstname, surname, vcard)
-    SELECT contact_id, user_id, changed, del, name, email, firstname, surname, vcard FROM contacts_tmp;
-
-CREATE INDEX ix_contacts_user_id ON contacts(user_id, email);
-DROP TABLE contacts_tmp;
-
-
-DELETE FROM messages;
-DELETE FROM cache;
-CREATE INDEX ix_contactgroupmembers_contact_id ON contactgroupmembers (contact_id);
-
--- Updates from version 0.6
-
-CREATE TABLE dictionary (
-    user_id integer DEFAULT NULL,
-   "language" varchar(5) NOT NULL,
-    data text NOT NULL
-);
-
-CREATE UNIQUE INDEX ix_dictionary_user_language ON dictionary (user_id, "language");
-
-CREATE TABLE searches (
-  search_id integer NOT NULL PRIMARY KEY,
-  user_id integer NOT NULL DEFAULT '0',
-  "type" smallint NOT NULL DEFAULT '0',
-  name varchar(128) NOT NULL,
-  data text NOT NULL
-);
-
-CREATE UNIQUE INDEX ix_searches_user_type_name ON searches (user_id, type, name);
-
-DROP TABLE messages;
-
-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',
-    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 TABLE cache_thread (
-    user_id integer NOT NULL,
-    mailbox varchar(255) NOT NULL,
-    changed datetime NOT NULL default '0000-00-00 00:00:00',
-    data text NOT NULL,
-    PRIMARY KEY (user_id, mailbox)
-);
-
-CREATE INDEX ix_cache_thread_changed ON cache_thread (changed);
-
-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',
-    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);
-
--- Updates from version 0.7-beta
-
-DROP TABLE session;
-CREATE TABLE session (
-  sess_id varchar(128) NOT NULL PRIMARY KEY,
-  created datetime NOT NULL default '0000-00-00 00:00:00',
-  changed datetime NOT NULL default '0000-00-00 00:00:00',
-  ip varchar(40) NOT NULL default '',
-  vars text NOT NULL
-);
-CREATE INDEX ix_session_changed ON session (changed);
-
--- Updates from version 0.7
-
-CREATE TABLE contacts_tmp (
-  contact_id integer NOT NULL PRIMARY KEY,
-  user_id integer NOT NULL,
-  changed datetime NOT NULL default '0000-00-00 00:00:00',
-  del tinyint NOT NULL default '0',
-  name varchar(128) NOT NULL default '',
-  email text NOT NULL default '',
-  firstname varchar(128) NOT NULL default '',
-  surname varchar(128) NOT NULL default '',
-  vcard text NOT NULL default '',
-  words text NOT NULL default ''
-);
-
-INSERT INTO contacts_tmp (contact_id, user_id, changed, del, name, email, firstname, surname, vcard, words)
-    SELECT contact_id, user_id, changed, del, name, email, firstname, surname, vcard, words FROM contacts;
-
-DROP TABLE contacts;
-
-CREATE TABLE contacts (
-  contact_id integer NOT NULL PRIMARY KEY,
-  user_id integer NOT NULL,
-  changed datetime NOT NULL default '0000-00-00 00:00:00',
-  del tinyint NOT NULL default '0',
-  name varchar(128) NOT NULL default '',
-  email text NOT NULL default '',
-  firstname varchar(128) NOT NULL default '',
-  surname varchar(128) NOT NULL default '',
-  vcard text NOT NULL default '',
-  words text NOT NULL default ''
-);
-
-INSERT INTO contacts (contact_id, user_id, changed, del, name, email, firstname, surname, vcard, words)
-    SELECT contact_id, user_id, changed, del, name, email, firstname, surname, vcard, words FROM contacts_tmp;
-
-CREATE INDEX ix_contacts_user_id ON contacts(user_id, del);
-DROP TABLE contacts_tmp;
-
--- Updates from version 0.8
-
-DROP 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);
-
-CREATE TABLE tmp_users (
-  user_id integer NOT NULL PRIMARY KEY,
-  username varchar(128) NOT NULL default '',
-  mail_host varchar(128) NOT NULL default '',
-  created datetime NOT NULL default '0000-00-00 00:00:00',
-  last_login datetime DEFAULT NULL,
-  language varchar(5),
-  preferences text NOT NULL default ''
-);
-
-INSERT INTO tmp_users (user_id, username, mail_host, created, last_login, language, preferences)
-    SELECT user_id, username, mail_host, created, last_login, language, preferences FROM users;
-
-DROP TABLE users;
-
-CREATE TABLE users (
-  user_id integer NOT NULL PRIMARY KEY,
-  username varchar(128) NOT NULL default '',
-  mail_host varchar(128) NOT NULL default '',
-  created datetime NOT NULL default '0000-00-00 00:00:00',
-  last_login datetime DEFAULT NULL,
-  language varchar(5),
-  preferences text NOT NULL default ''
-);
-
-INSERT INTO users (user_id, username, mail_host, created, last_login, language, preferences)
-    SELECT user_id, username, mail_host, created, last_login, language, preferences FROM tmp_users;
-
-CREATE UNIQUE INDEX ix_users_username ON users(username, mail_host);
-
-CREATE INDEX ix_identities_email ON identities(email, del);
diff --git a/SQL/sqlite/2008030300.sql b/SQL/sqlite/2008030300.sql
new file mode 100644
index 000000000..b5b4890bf
--- /dev/null
+++ b/SQL/sqlite/2008030300.sql
@@ -0,0 +1,25 @@
+-- Updates from version 0.1-stable to 0.1.1
+
+DROP TABLE messages;
+
+CREATE TABLE messages (
+  message_id integer NOT NULL PRIMARY KEY,
+  user_id integer NOT NULL default '0',
+  del tinyint NOT NULL default '0',
+  cache_key varchar(128) NOT NULL default '',
+  created datetime NOT NULL default '0000-00-00 00:00:00',
+  idx integer NOT NULL default '0',
+  uid integer NOT NULL default '0',
+  subject varchar(255) NOT NULL default '',
+  "from" varchar(255) NOT NULL default '',
+  "to" varchar(255) NOT NULL default '',
+  "cc" varchar(255) NOT NULL default '',
+  "date" datetime NOT NULL default '0000-00-00 00:00:00',
+  size integer NOT NULL default '0',
+  headers text NOT NULL,
+  structure text
+);
+
+CREATE INDEX ix_messages_user_cache_uid ON messages(user_id,cache_key,uid);
+CREATE INDEX ix_users_username ON users(username);
+CREATE INDEX ix_users_alias ON users(alias);
diff --git a/SQL/sqlite/2008060900.sql b/SQL/sqlite/2008060900.sql
new file mode 100644
index 000000000..157029c28
--- /dev/null
+++ b/SQL/sqlite/2008060900.sql
@@ -0,0 +1,3 @@
+-- Updates from version 0.2-alpha
+
+CREATE INDEX ix_messages_created ON messages (created);
diff --git a/SQL/sqlite/2008092100.sql b/SQL/sqlite/2008092100.sql
new file mode 100644
index 000000000..8bc081c79
--- /dev/null
+++ b/SQL/sqlite/2008092100.sql
@@ -0,0 +1,4 @@
+-- Updates from version 0.2-beta
+
+CREATE INDEX ix_session_changed ON session (changed);
+CREATE INDEX ix_cache_created ON cache (created);
diff --git a/SQL/sqlite/2009090400.sql b/SQL/sqlite/2009090400.sql
new file mode 100644
index 000000000..c8a53edf1
--- /dev/null
+++ b/SQL/sqlite/2009090400.sql
@@ -0,0 +1,8 @@
+-- Updates from version 0.3-stable
+
+DELETE FROM messages;
+DROP INDEX ix_messages_user_cache_uid;
+CREATE UNIQUE INDEX ix_messages_user_cache_uid ON messages (user_id,cache_key,uid);
+CREATE INDEX ix_messages_index ON messages (user_id,cache_key,idx);
+DROP INDEX ix_contacts_user_id;
+CREATE INDEX ix_contacts_user_id ON contacts(user_id, email);
diff --git a/SQL/sqlite/2009103100.sql b/SQL/sqlite/2009103100.sql
new file mode 100644
index 000000000..bd7b1741c
--- /dev/null
+++ b/SQL/sqlite/2009103100.sql
@@ -0,0 +1,61 @@
+-- Updates from version 0.3.1
+
+-- ALTER TABLE identities ADD COLUMN changed datetime NOT NULL default '0000-00-00 00:00:00'; --
+
+CREATE TABLE temp_identities (
+  identity_id integer NOT NULL PRIMARY KEY,
+  user_id integer NOT NULL default '0',
+  standard tinyint NOT NULL default '0',
+  name varchar(128) NOT NULL default '',
+  organization varchar(128) default '',
+  email varchar(128) NOT NULL default '',
+  "reply-to" varchar(128) NOT NULL default '',
+  bcc varchar(128) NOT NULL default '',
+  signature text NOT NULL default '',
+  html_signature tinyint NOT NULL default '0'
+);
+INSERT INTO temp_identities (identity_id, user_id, standard, name, organization, email, "reply-to", bcc, signature, html_signature)
+  SELECT identity_id, user_id, standard, name, organization, email, "reply-to", bcc, signature, html_signature
+  FROM identities WHERE del=0;
+
+DROP INDEX ix_identities_user_id;
+DROP TABLE identities;
+
+CREATE TABLE identities (
+  identity_id integer NOT NULL PRIMARY KEY,
+  user_id integer NOT NULL default '0',
+  changed datetime NOT NULL default '0000-00-00 00:00:00',
+  del tinyint NOT NULL default '0',
+  standard tinyint NOT NULL default '0',
+  name varchar(128) NOT NULL default '',
+  organization varchar(128) default '',
+  email varchar(128) NOT NULL default '',
+  "reply-to" varchar(128) NOT NULL default '',
+  bcc varchar(128) NOT NULL default '',
+  signature text NOT NULL default '',
+  html_signature tinyint NOT NULL default '0'
+);
+CREATE INDEX ix_identities_user_id ON identities(user_id, del);
+
+INSERT INTO identities (identity_id, user_id, standard, name, organization, email, "reply-to", bcc, signature, html_signature)
+  SELECT identity_id, user_id, standard, name, organization, email, "reply-to", bcc, signature, html_signature
+  FROM temp_identities;
+
+DROP TABLE temp_identities;
+
+CREATE TABLE contactgroups (
+  contactgroup_id integer NOT NULL PRIMARY KEY,
+  user_id integer NOT NULL default '0',
+  changed datetime NOT NULL default '0000-00-00 00:00:00',
+  del tinyint NOT NULL default '0',
+  name varchar(128) NOT NULL default ''
+);
+
+CREATE INDEX ix_contactgroups_user_id ON contactgroups(user_id, del);
+
+CREATE TABLE contactgroupmembers (
+  contactgroup_id integer NOT NULL,
+  contact_id integer NOT NULL default '0',
+  created datetime NOT NULL default '0000-00-00 00:00:00',
+  PRIMARY KEY (contactgroup_id, contact_id)
+);
diff --git a/SQL/sqlite/2010042300.sql b/SQL/sqlite/2010042300.sql
new file mode 100644
index 000000000..1a9023cfc
--- /dev/null
+++ b/SQL/sqlite/2010042300.sql
@@ -0,0 +1,35 @@
+-- Updates from version 0.4-beta
+
+CREATE TABLE tmp_users (
+  user_id integer NOT NULL PRIMARY KEY,
+  username varchar(128) NOT NULL default '',
+  mail_host varchar(128) NOT NULL default '',
+  alias varchar(128) NOT NULL default '',
+  created datetime NOT NULL default '0000-00-00 00:00:00',
+  last_login datetime NOT NULL default '0000-00-00 00:00:00',
+  language varchar(5),
+  preferences text NOT NULL default ''
+);
+
+INSERT INTO tmp_users (user_id, username, mail_host, alias, created, last_login, language, preferences)
+    SELECT user_id, username, mail_host, alias, created, last_login, language, preferences FROM users;
+
+DROP TABLE users;
+
+CREATE TABLE users (
+  user_id integer NOT NULL PRIMARY KEY,
+  username varchar(128) NOT NULL default '',
+  mail_host varchar(128) NOT NULL default '',
+  alias varchar(128) NOT NULL default '',
+  created datetime NOT NULL default '0000-00-00 00:00:00',
+  last_login datetime DEFAULT NULL,
+  language varchar(5),
+  preferences text NOT NULL default ''
+);
+
+INSERT INTO users (user_id, username, mail_host, alias, created, last_login, language, preferences)
+    SELECT user_id, username, mail_host, alias, created, last_login, language, preferences FROM tmp_users;
+
+CREATE INDEX ix_users_username ON users(username);
+CREATE INDEX ix_users_alias ON users(alias);
+DROP TABLE tmp_users;
diff --git a/SQL/sqlite/2010100600.sql b/SQL/sqlite/2010100600.sql
new file mode 100644
index 000000000..5c7259f37
--- /dev/null
+++ b/SQL/sqlite/2010100600.sql
@@ -0,0 +1,40 @@
+-- Updates from version 0.4.2
+
+DROP INDEX ix_users_username;
+CREATE UNIQUE INDEX ix_users_username ON users(username, mail_host);
+
+CREATE TABLE contacts_tmp (
+    contact_id integer NOT NULL PRIMARY KEY,
+    user_id integer NOT NULL default '0',
+    changed datetime NOT NULL default '0000-00-00 00:00:00',
+    del tinyint NOT NULL default '0',
+    name varchar(128) NOT NULL default '',
+    email varchar(255) NOT NULL default '',
+    firstname varchar(128) NOT NULL default '',
+    surname varchar(128) NOT NULL default '',
+    vcard text NOT NULL default ''
+);
+
+INSERT INTO contacts_tmp (contact_id, user_id, changed, del, name, email, firstname, surname, vcard)
+    SELECT contact_id, user_id, changed, del, name, email, firstname, surname, vcard FROM contacts;
+
+DROP TABLE contacts;
+CREATE TABLE contacts (
+    contact_id integer NOT NULL PRIMARY KEY,
+    user_id integer NOT NULL default '0',
+    changed datetime NOT NULL default '0000-00-00 00:00:00',
+    del tinyint NOT NULL default '0',
+    name varchar(128) NOT NULL default '',
+    email varchar(255) NOT NULL default '',
+    firstname varchar(128) NOT NULL default '',
+    surname varchar(128) NOT NULL default '',
+    vcard text NOT NULL default ''
+);
+
+INSERT INTO contacts (contact_id, user_id, changed, del, name, email, firstname, surname, vcard)
+    SELECT contact_id, user_id, changed, del, name, email, firstname, surname, vcard FROM contacts_tmp;
+
+CREATE INDEX ix_contacts_user_id ON contacts(user_id, email);
+DROP TABLE contacts_tmp;
+
+DELETE FROM messages;
diff --git a/SQL/sqlite/2011011200.sql b/SQL/sqlite/2011011200.sql
new file mode 100644
index 000000000..4cca74076
--- /dev/null
+++ b/SQL/sqlite/2011011200.sql
@@ -0,0 +1,41 @@
+-- Updates from version 0.5.x
+
+CREATE TABLE contacts_tmp (
+    contact_id integer NOT NULL PRIMARY KEY,
+    user_id integer NOT NULL default '0',
+    changed datetime NOT NULL default '0000-00-00 00:00:00',
+    del tinyint NOT NULL default '0',
+    name varchar(128) NOT NULL default '',
+    email varchar(255) NOT NULL default '',
+    firstname varchar(128) NOT NULL default '',
+    surname varchar(128) NOT NULL default '',
+    vcard text NOT NULL default ''
+);
+
+INSERT INTO contacts_tmp (contact_id, user_id, changed, del, name, email, firstname, surname, vcard)
+    SELECT contact_id, user_id, changed, del, name, email, firstname, surname, vcard FROM contacts;
+
+DROP TABLE contacts;
+CREATE TABLE contacts (
+    contact_id integer NOT NULL PRIMARY KEY,
+    user_id integer NOT NULL default '0',
+    changed datetime NOT NULL default '0000-00-00 00:00:00',
+    del tinyint NOT NULL default '0',
+    name varchar(128) NOT NULL default '',
+    email varchar(255) NOT NULL default '',
+    firstname varchar(128) NOT NULL default '',
+    surname varchar(128) NOT NULL default '',
+    vcard text NOT NULL default '',
+    words text NOT NULL default ''
+);
+
+INSERT INTO contacts (contact_id, user_id, changed, del, name, email, firstname, surname, vcard)
+    SELECT contact_id, user_id, changed, del, name, email, firstname, surname, vcard FROM contacts_tmp;
+
+CREATE INDEX ix_contacts_user_id ON contacts(user_id, email);
+DROP TABLE contacts_tmp;
+
+
+DELETE FROM messages;
+DELETE FROM cache;
+CREATE INDEX ix_contactgroupmembers_contact_id ON contactgroupmembers (contact_id);
diff --git a/SQL/sqlite/2011092800.sql b/SQL/sqlite/2011092800.sql
new file mode 100644
index 000000000..27dbffc1d
--- /dev/null
+++ b/SQL/sqlite/2011092800.sql
@@ -0,0 +1,54 @@
+-- Updates from version 0.6
+
+CREATE TABLE dictionary (
+    user_id integer DEFAULT NULL,
+   "language" varchar(5) NOT NULL,
+    data text NOT NULL
+);
+
+CREATE UNIQUE INDEX ix_dictionary_user_language ON dictionary (user_id, "language");
+
+CREATE TABLE searches (
+  search_id integer NOT NULL PRIMARY KEY,
+  user_id integer NOT NULL DEFAULT '0',
+  "type" smallint NOT NULL DEFAULT '0',
+  name varchar(128) NOT NULL,
+  data text NOT NULL
+);
+
+CREATE UNIQUE INDEX ix_searches_user_type_name ON searches (user_id, type, name);
+
+DROP TABLE messages;
+
+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',
+    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 TABLE cache_thread (
+    user_id integer NOT NULL,
+    mailbox varchar(255) NOT NULL,
+    changed datetime NOT NULL default '0000-00-00 00:00:00',
+    data text NOT NULL,
+    PRIMARY KEY (user_id, mailbox)
+);
+
+CREATE INDEX ix_cache_thread_changed ON cache_thread (changed);
+
+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',
+    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);
diff --git a/SQL/sqlite/2011111600.sql b/SQL/sqlite/2011111600.sql
new file mode 100644
index 000000000..cea0d129e
--- /dev/null
+++ b/SQL/sqlite/2011111600.sql
@@ -0,0 +1,11 @@
+-- Updates from version 0.7-beta
+
+DROP TABLE session;
+CREATE TABLE session (
+  sess_id varchar(128) NOT NULL PRIMARY KEY,
+  created datetime NOT NULL default '0000-00-00 00:00:00',
+  changed datetime NOT NULL default '0000-00-00 00:00:00',
+  ip varchar(40) NOT NULL default '',
+  vars text NOT NULL
+);
+CREATE INDEX ix_session_changed ON session (changed);
diff --git a/SQL/sqlite/2011121400.sql b/SQL/sqlite/2011121400.sql
new file mode 100644
index 000000000..9d9227673
--- /dev/null
+++ b/SQL/sqlite/2011121400.sql
@@ -0,0 +1,38 @@
+-- Updates from version 0.7
+
+CREATE TABLE contacts_tmp (
+  contact_id integer NOT NULL PRIMARY KEY,
+  user_id integer NOT NULL,
+  changed datetime NOT NULL default '0000-00-00 00:00:00',
+  del tinyint NOT NULL default '0',
+  name varchar(128) NOT NULL default '',
+  email text NOT NULL default '',
+  firstname varchar(128) NOT NULL default '',
+  surname varchar(128) NOT NULL default '',
+  vcard text NOT NULL default '',
+  words text NOT NULL default ''
+);
+
+INSERT INTO contacts_tmp (contact_id, user_id, changed, del, name, email, firstname, surname, vcard, words)
+    SELECT contact_id, user_id, changed, del, name, email, firstname, surname, vcard, words FROM contacts;
+
+DROP TABLE contacts;
+
+CREATE TABLE contacts (
+  contact_id integer NOT NULL PRIMARY KEY,
+  user_id integer NOT NULL,
+  changed datetime NOT NULL default '0000-00-00 00:00:00',
+  del tinyint NOT NULL default '0',
+  name varchar(128) NOT NULL default '',
+  email text NOT NULL default '',
+  firstname varchar(128) NOT NULL default '',
+  surname varchar(128) NOT NULL default '',
+  vcard text NOT NULL default '',
+  words text NOT NULL default ''
+);
+
+INSERT INTO contacts (contact_id, user_id, changed, del, name, email, firstname, surname, vcard, words)
+    SELECT contact_id, user_id, changed, del, name, email, firstname, surname, vcard, words FROM contacts_tmp;
+
+CREATE INDEX ix_contacts_user_id ON contacts(user_id, del);
+DROP TABLE contacts_tmp;
diff --git a/SQL/sqlite/2012080700.sql b/SQL/sqlite/2012080700.sql
new file mode 100644
index 000000000..c6ede89e8
--- /dev/null
+++ b/SQL/sqlite/2012080700.sql
@@ -0,0 +1,44 @@
+-- Updates from version 0.8
+
+DROP 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);
+
+CREATE TABLE tmp_users (
+  user_id integer NOT NULL PRIMARY KEY,
+  username varchar(128) NOT NULL default '',
+  mail_host varchar(128) NOT NULL default '',
+  created datetime NOT NULL default '0000-00-00 00:00:00',
+  last_login datetime DEFAULT NULL,
+  language varchar(5),
+  preferences text NOT NULL default ''
+);
+
+INSERT INTO tmp_users (user_id, username, mail_host, created, last_login, language, preferences)
+    SELECT user_id, username, mail_host, created, last_login, language, preferences FROM users;
+
+DROP TABLE users;
+
+CREATE TABLE users (
+  user_id integer NOT NULL PRIMARY KEY,
+  username varchar(128) NOT NULL default '',
+  mail_host varchar(128) NOT NULL default '',
+  created datetime NOT NULL default '0000-00-00 00:00:00',
+  last_login datetime DEFAULT NULL,
+  language varchar(5),
+  preferences text NOT NULL default ''
+);
+
+INSERT INTO users (user_id, username, mail_host, created, last_login, language, preferences)
+    SELECT user_id, username, mail_host, created, last_login, language, preferences FROM tmp_users;
+
+CREATE UNIQUE INDEX ix_users_username ON users(username, mail_host);
+
+CREATE INDEX ix_identities_email ON identities(email, del);
diff --git a/SQL/sqlite/2013011000.sql b/SQL/sqlite/2013011000.sql
new file mode 100644
index 000000000..2c66ef95e
--- /dev/null
+++ b/SQL/sqlite/2013011000.sql
@@ -0,0 +1,6 @@
+-- Updates from version 0.9-beta
+
+CREATE TABLE system (
+  name varchar(64) NOT NULL PRIMARY KEY,
+  value text NOT NULL
+);
author	Aleksander Machniak <alec@alec.pl>	2013-01-17 08:39:39 +0100
committer	Aleksander Machniak <alec@alec.pl>	2013-01-17 08:39:39 +0100
commit	f5c7df4c324d3157ab47469f492ecdb2a4b82e93 (patch)
tree	2137040407617eb0891aba2ac0908a2bb9e9e5fb /SQL
parent	a03c28a22f194c3cbaee46abd8def0d7b8fa7e60 (diff)
parent	c101871d463963fe5426a2db3ba39cb524a96dbd (diff)