From 393618dacb75c55fbf482e9ee5e368b852bfc6e7 Mon Sep 17 00:00:00 2001
From: Aleksander Machniak <alec@alec.pl>
Date: Thu, 17 Jan 2013 02:35:35 -0500
Subject: [PATCH] Merge branch 'schema_upgrades'
---
bin/update.sh | 36
SQL/postgres/2008092100.sql | 14
SQL/sqlite/2009103100.sql | 61 ++
SQL/mysql/2008060900.sql | 4
SQL/mysql/2011011200.sql | 8
SQL/mysql/2008040500.sql | 9
SQL/sqlite/2008060900.sql | 3
installer/rcube_install.php | 43 -
SQL/mssql/2013011000.sql | 14
SQL/postgres/2009090400.sql | 6
SQL/mysql/2013011000.sql | 7
SQL/postgres/2009103100.sql | 32 +
SQL/mssql/2011011200.sql | 10
SQL/postgres/2012080700.sql | 7
SQL/postgres/2008060900.sql | 3
SQL/mysql/2008030300.sql | 16
SQL/mysql/2010100600.sql | 8
SQL/sqlite/2012080700.sql | 44 +
SQL/sqlite/2011121400.sql | 38 +
SQL/sqlite/2013011000.sql | 6
SQL/sqlite/2011111600.sql | 11
SQL/mysql/2011092800.sql | 67 ++
SQL/sqlite/2011011200.sql | 41 +
SQL/postgres.initial.sql | 13
SQL/mssql/2011092800.sql | 127 ++++
SQL/mysql.initial.sql | 10
SQL/mssql/2010100600.sql | 9
SQL/sqlite/2010042300.sql | 35 +
SQL/mysql/2009090400.sql | 12
SQL/mysql/2012080700.sql | 5
SQL/postgres/2010100600.sql | 7
config/db.inc.php.dist | 1
SQL/mysql/2011121400.sql | 22
SQL/sqlite/2010100600.sql | 40 +
SQL/mssql/2011121400.sql | 9
SQL/mysql/2008092100.sql | 20
installer/test.php | 13
SQL/sqlite/2008030300.sql | 25
SQL/mssql.initial.sql | 16
SQL/postgres/2010042300.sql | 4
SQL/mysql/2009103100.sql | 52 +
SQL/sqlite/2009090400.sql | 8
SQL/postgres/2011121400.sql | 5
SQL/postgres/2013011000.sql | 4
SQL/postgres/2011111600.sql | 3
SQL/postgres/2011092800.sql | 64 ++
SQL/mssql/2012051800.sql | 18
SQL/mssql/2011111600.sql | 4
SQL/sqlite.initial.sql | 13
SQL/postgres/2011011200.sql | 7
SQL/sqlite/2011092800.sql | 54 +
/dev/null | 380 ------------
SQL/mysql/2011111600.sql | 3
SQL/sqlite/2008092100.sql | 4
SQL/postgres/2008030300.sql | 18
SQL/mssql/2012080700.sql | 8
SQL/mssql/2009103100.sql | 87 ++
bin/updatedb.sh | 184 ++++++
SQL/mysql/2010042300.sql | 4
59 files changed, 1,324 insertions(+), 452 deletions(-)
diff --git a/SQL/mssql.initial.sql b/SQL/mssql.initial.sql
index 85b8e4e..e312cfb 100644
--- a/SQL/mssql.initial.sql
+++ b/SQL/mssql.initial.sql
@@ -115,6 +115,12 @@
) 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
(
@@ -182,6 +188,13 @@
CONSTRAINT [PK_searches_search_id] PRIMARY KEY CLUSTERED
(
[search_id]
+ ) ON [PRIMARY]
+GO
+
+ALTER TABLE [dbo].[system] WITH NOCHECK ADD
+ CONSTRAINT [PK_system_name] PRIMARY KEY CLUSTERED
+ (
+ [name]
) ON [PRIMARY]
GO
@@ -358,3 +371,6 @@
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 26001e7..0000000
--- 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 0000000..646fe38
--- /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 0000000..fb045e3
--- /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 0000000..8715455
--- /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 0000000..00f79bb
--- /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 0000000..3878776
--- /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 0000000..fde63ea
--- /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 0000000..8dcf7bf
--- /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 0000000..33e6133
--- /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 0000000..2eb51e6
--- /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 47d9db4..bb1856c 100644
--- a/SQL/mysql.initial.sql
+++ b/SQL/mysql.initial.sql
@@ -186,4 +186,14 @@
) /*!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 237aa3e..0000000
--- 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 0000000..9a3d048
--- /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 0000000..f538f63
--- /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 0000000..9f8de0a
--- /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 0000000..3989c75
--- /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 0000000..fd31bed
--- /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 0000000..aafbe23
--- /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 0000000..8b90af4
--- /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 0000000..ca0fec8
--- /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 0000000..6597034
--- /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 0000000..6b7cbe1
--- /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 0000000..6f53daa
--- /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 0000000..5aee806
--- /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 0000000..789b712
--- /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 0000000..f0b5e6a
--- /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 f7b2d96..d7dc08f 100644
--- a/SQL/postgres.initial.sql
+++ b/SQL/postgres.initial.sql
@@ -262,3 +262,16 @@
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 11ab93b..0000000
--- 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 0000000..67a8f15
--- /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 0000000..7c60a44
--- /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 0000000..6cd1929
--- /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 0000000..8eb4949
--- /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 0000000..2350f79
--- /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 0000000..bc9bd82
--- /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 0000000..8dfcc12
--- /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 0000000..db468d8
--- /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 0000000..fac3cd3
--- /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 0000000..a382ba0
--- /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 0000000..81a0cb8
--- /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 0000000..41f6016
--- /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 0000000..a8cf917
--- /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 f5b5615..76913e8 100644
--- a/SQL/sqlite.initial.sql
+++ b/SQL/sqlite.initial.sql
@@ -197,3 +197,16 @@
);
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 72a29e9..0000000
--- 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 0000000..b5b4890
--- /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 0000000..157029c
--- /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 0000000..8bc081c
--- /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 0000000..c8a53ed
--- /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 0000000..bd7b174
--- /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 0000000..1a9023c
--- /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 0000000..5c7259f
--- /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 0000000..4cca740
--- /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 0000000..27dbffc
--- /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 0000000..cea0d12
--- /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 0000000..9d92276
--- /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 0000000..c6ede89
--- /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 0000000..2c66ef9
--- /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
+);
diff --git a/bin/update.sh b/bin/update.sh
index 2015aa9..536bad9 100755
--- a/bin/update.sh
+++ b/bin/update.sh
@@ -124,7 +124,7 @@
}
}
else {
- echo "Please update your config files manually according to the above messages.\n\n";
+ echo "Please update your config files manually according to the above messages.\n";
}
}
@@ -143,36 +143,18 @@
// check database schema
if ($RCI->config['db_dsnw']) {
- $DB = rcube_db::factory($RCI->config['db_dsnw'], '', false);
- $DB->db_connect('w');
- if ($db_error_msg = $DB->is_error()) {
- echo "Error connecting to database: $db_error_msg\n";
- $success = false;
- }
- else if ($err = $RCI->db_schema_check($DB, false)) {
- $updatefile = INSTALL_PATH . 'SQL/' . (isset($RCI->db_map[$DB->db_provider]) ? $RCI->db_map[$DB->db_provider] : $DB->db_provider) . '.update.sql';
- echo "WARNING: Database schema needs to be updated!\n";
- echo join("\n", $err) . "\n\n";
- $success = false;
-
- if ($opts['version']) {
- echo "Do you want to run the update queries to get the schmea fixed? (y/N)\n";
- $input = trim(fgets(STDIN));
- if (strtolower($input) == 'y') {
- $success = $RCI->update_db($DB, $opts['version']);
- }
- }
-
- if (!$success)
- echo "Open $updatefile and execute all queries below the comment with the currently installed version number.\n";
- }
+ echo "Executing database schema update.\n";
+ system(INSTALL_PATH . "bin/updatedb.sh --package=roundcube --version=" . $ops['version']
+ . " --dir=" . INSTALL_PATH . DIRECTORY_SEPARATOR . "SQL", $res);
+
+ $success = !$res;
}
-
+
// index contacts for fulltext searching
if (version_compare(version_parse($opts['version']), '0.6.0', '<')) {
system(INSTALL_PATH . 'bin/indexcontacts.sh');
}
-
+
if ($success) {
echo "This instance of Roundcube is up-to-date.\n";
echo "Have fun!\n";
@@ -182,7 +164,5 @@
echo "This instance of Roundcube is not yet configured!\n";
echo "Open http://url-to-roundcube/installer/ in your browser and follow the instuctions.\n";
}
-
-echo "\n";
?>
diff --git a/bin/updatedb.sh b/bin/updatedb.sh
new file mode 100755
index 0000000..4b922ea
--- /dev/null
+++ b/bin/updatedb.sh
@@ -0,0 +1,184 @@
+#!/usr/bin/env php
+<?php
+/*
+ +-----------------------------------------------------------------------+
+ | bin/updatedb.sh |
+ | |
+ | This file is part of the Roundcube Webmail client |
+ | Copyright (C) 2010-2012, The Roundcube Dev Team |
+ | Copyright (C) 2010-2012, Kolab Systems AG |
+ | |
+ | Licensed under the GNU General Public License version 3 or |
+ | any later version with exceptions for skins & plugins. |
+ | See the README file for a full license statement. |
+ | |
+ | PURPOSE: |
+ | Update database schema |
+ +-----------------------------------------------------------------------+
+ | Author: Aleksander Machniak <alec@alec.pl> |
+ +-----------------------------------------------------------------------+
+*/
+
+define('INSTALL_PATH', realpath(dirname(__FILE__) . '/..') . '/' );
+
+require_once INSTALL_PATH . 'program/include/clisetup.php';
+
+// get arguments
+$opts = rcube_utils::get_opt(array(
+ 'v' => 'version',
+ 'd' => 'dir',
+ 'p' => 'package',
+));
+
+if (empty($opts['dir'])) {
+ echo "ERROR: Database schema directory not specified (--dir).\n";
+ exit(1);
+}
+if (empty($opts['package'])) {
+ echo "ERROR: Database schema package name not specified (--package).\n";
+ exit(1);
+}
+
+// Check if directory exists
+if (!file_exists($opts['dir'])) {
+ echo "ERROR: Specified database schema directory doesn't exist.\n";
+ exit(1);
+}
+
+$RC = rcube::get_instance();
+$DB = rcube_db::factory($RC->config->get('db_dsnw'));
+
+// Connect to database
+$DB->db_connect('w');
+if (!$DB->is_connected()) {
+ echo "Error connecting to database: " . $DB->is_error() . ".\n";
+ exit(1);
+}
+
+// Read DB schema version from database (if 'system' table exists)
+if (in_array('system', (array)$DB->list_tables())) {
+ $DB->query("SELECT " . $DB->quote_identifier('value')
+ ." FROM " . $DB->quote_identifier($DB->table_name('system'))
+ ." WHERE " . $DB->quote_identifier('name') ." = ?",
+ $opts['package'] . '-version');
+
+ $row = $DB->fetch_array();
+ $version = $row[0];
+}
+
+// DB version not found, but release version is specified
+if (!$version && $opts['version']) {
+ // Map old release version string to DB schema version
+ // Note: This is for backward compat. only, do not need to be updated
+ $map = array(
+ '0.1-stable' => 1,
+ '0.1.1' => 2008030300,
+ '0.2-alpha' => 2008040500,
+ '0.2-beta' => 2008060900,
+ '0.2-stable' => 2008092100,
+ '0.3-stable' => 2008092100,
+ '0.3.1' => 2009090400,
+ '0.4-beta' => 2009103100,
+ '0.4.2' => 2010042300,
+ '0.5-beta' => 2010100600,
+ '0.5' => 2010100600,
+ '0.5.1' => 2010100600,
+ '0.6-beta' => 2011011200,
+ '0.6' => 2011011200,
+ '0.7-beta' => 2011092800,
+ '0.7' => 2011111600,
+ '0.7.1' => 2011111600,
+ '0.7.2' => 2011111600,
+ '0.7.3' => 2011111600,
+ '0.8-beta' => 2011121400,
+ '0.8-rc' => 2011121400,
+ '0.8.0' => 2011121400,
+ '0.8.1' => 2011121400,
+ '0.8.2' => 2011121400,
+ '0.8.3' => 2011121400,
+ '0.8.4' => 2011121400,
+ '0.9-beta' => 2012080700,
+ );
+
+ $version = $map[$opts['version']];
+}
+
+// Assume last version before the 'system' table was added
+if (empty($version)) {
+ $version = 2012080700;
+}
+
+$dir = $opts['dir'] . DIRECTORY_SEPARATOR . $DB->db_provider;
+if (!file_exists($dir)) {
+ echo "DDL Upgrade files for " . $DB->db_provider . " driver not found.\n";
+ exit(1);
+}
+
+$dh = opendir($dir);
+$result = array();
+
+while ($file = readdir($dh)) {
+ if (preg_match('/^([0-9]+)\.sql$/', $file, $m) && $m[1] > $version) {
+ $result[] = $m[1];
+ }
+}
+sort($result, SORT_NUMERIC);
+
+foreach ($result as $v) {
+ echo "Updating database schema ($v)... ";
+ $error = update_db_schema($opts['package'], $v, $dir . DIRECTORY_SEPARATOR . "$v.sql");
+
+ if ($error) {
+ echo "\nError in DDL upgrade $v: $error\n";
+ exit(1);
+ }
+ echo "[OK]\n";
+}
+
+exit(0);
+
+function update_db_schema($package, $version, $file)
+{
+ global $DB;
+
+ // read DDL file
+ if ($lines = file($file)) {
+ $sql = '';
+ foreach ($lines as $line) {
+ if (preg_match('/^--/', $line) || trim($line) == '')
+ continue;
+
+ $sql .= $line . "\n";
+ if (preg_match('/(;|^GO)$/', trim($line))) {
+ @$DB->query($sql);
+ $sql = '';
+ if ($error = $DB->is_error()) {
+ return $error;
+ }
+ }
+ }
+ }
+
+ // escape if 'system' table does not exist
+ if ($version < 2013011000) {
+ return;
+ }
+
+ $system_table = $DB->quote_identifier($DB->table_name('system'));
+
+ $DB->query("UPDATE " . $system_table
+ ." SET " . $DB->quote_identifier('value') . " = ?"
+ ." WHERE " . $DB->quote_identifier('name') . " = ?",
+ $version, $package . '-version');
+
+ if (!$DB->is_error() && !$DB->affected_rows()) {
+ $DB->query("INSERT INTO " . $system_table
+ ." (" . $DB->quote_identifier('name') . ", " . $DB->quote_identifier('value') . ")"
+ ." VALUES (?, ?)",
+ $package . '-version', $version);
+ }
+
+ return $DB->is_error();
+}
+
+?>
diff --git a/config/db.inc.php.dist b/config/db.inc.php.dist
index a596d64..9bbf7d7 100644
--- a/config/db.inc.php.dist
+++ b/config/db.inc.php.dist
@@ -49,6 +49,7 @@
$rcmail_config['db_table_cache_messages'] = 'cache_messages';
$rcmail_config['db_table_dictionary'] = 'dictionary';
$rcmail_config['db_table_searches'] = 'searches';
+$rcmail_config['db_table_system'] = 'system';
// you can define specific sequence names used in PostgreSQL
$rcmail_config['db_sequence_users'] = 'user_ids';
diff --git a/installer/rcube_install.php b/installer/rcube_install.php
index e6fbc62..33fd4c6 100644
--- a/installer/rcube_install.php
+++ b/installer/rcube_install.php
@@ -29,7 +29,6 @@
var $config = array();
var $configured = false;
var $last_error = null;
- var $db_map = array('pgsql' => 'postgres', 'mysqli' => 'mysql', 'sqlsrv' => 'mssql');
var $email_pattern = '([a-z0-9][a-z0-9\-\.\+\_]*@[a-z0-9]([a-z0-9\-][.]?)*[a-z0-9])';
var $bool_config_props = array();
@@ -609,7 +608,7 @@
*/
function init_db($DB)
{
- $engine = isset($this->db_map[$DB->db_provider]) ? $this->db_map[$DB->db_provider] : $DB->db_provider;
+ $engine = $DB->db_provider;
// read schema file from /SQL/*
$fname = INSTALL_PATH . "SQL/$engine.initial.sql";
@@ -631,46 +630,18 @@
/**
- * Update database with SQL statements from SQL/*.update.sql
+ * Update database schema
*
- * @param object rcube_db Database connection
* @param string Version to update from
+ *
* @return boolen True on success, False on error
*/
- function update_db($DB, $version)
+ function update_db($version)
{
- $version = version_parse(strtolower($version));
- $engine = isset($this->db_map[$DB->db_provider]) ? $this->db_map[$DB->db_provider] : $DB->db_provider;
+ system(INSTALL_PATH . "bin/updatedb.sh --package=roundcube --version=" . $version
+ . " --dir=" . INSTALL_PATH . "SQL", $result);
- // read schema file from /SQL/*
- $fname = INSTALL_PATH . "SQL/$engine.update.sql";
- if ($lines = @file($fname, FILE_SKIP_EMPTY_LINES)) {
- $from = false; $sql = '';
- foreach ($lines as $line) {
- $is_comment = preg_match('/^--/', $line);
- if (!$from && $is_comment && preg_match('/from version\s([0-9.]+[a-z-]*)/', $line, $m)) {
- $v = version_parse(strtolower($m[1]));
- if ($v == $version || version_compare($version, $v, '<='))
- $from = true;
- }
- if ($from && !$is_comment)
- $sql .= $line. "\n";
- }
-
- if ($sql)
- $this->exec_sql($sql, $DB);
- }
- else {
- $this->fail('DB Schema', "Cannot read the update file: $fname");
- return false;
- }
-
- if ($err = $this->get_error()) {
- $this->fail('DB Schema', "Error updating database: $err");
- return false;
- }
-
- return true;
+ return !$result;
}
diff --git a/installer/test.php b/installer/test.php
index b118924..bd49ac5 100644
--- a/installer/test.php
+++ b/installer/test.php
@@ -164,11 +164,9 @@
}
else if ($db_working && $_POST['updatedb']) {
- if (!($success = $RCI->update_db($DB, $_POST['version']))) {
- $updatefile = INSTALL_PATH . 'SQL/' . (isset($RCI->db_map[$DB->db_provider]) ? $RCI->db_map[$DB->db_provider] : $DB->db_provider) . '.update.sql';
- echo '<p class="warning">Please manually execute the SQL statements from '.$updatefile.' on your database.<br/>';
- echo 'See comments in the file and execute queries below the comment with the currently installed version number.</p>';
- }
+ if (!($success = $RCI->update_db($_POST['version']))) {
+ echo '<p class="warning">Database schema update failed.</p>';
+ }
}
// test database
@@ -183,9 +181,8 @@
$RCI->fail('DB Schema', "Database schema differs");
echo '<ul style="margin:0"><li>' . join("</li>\n<li>", $err) . "</li></ul>";
$select = $RCI->versions_select(array('name' => 'version'));
- echo '<p class="suggestion">You should run the update queries to get the schmea fixed.<br/><br/>Version to update from: ' . $select->show() . ' <input type="submit" name="updatedb" value="Update" /></p>';
-// echo '<p class="warning">Please manually execute the SQL statements from '.$updatefile.' on your database.<br/>';
-// echo 'See comments in the file and execute queries that are superscribed with the currently installed version number.</p>';
+ $select->add('0.9 or newer', '');
+ echo '<p class="suggestion">You should run the update queries to get the schema fixed.<br/><br/>Version to update from: ' . $select->show() . ' <input type="submit" name="updatedb" value="Update" /></p>';
$db_working = false;
}
else {
--
Gitblit v1.9.1