Page MenuHomePhorge
Files F223986

No OneTemporary
Actions

View Options

diff --git a/SQL/mssql.initial.sql b/SQL/mssql.initial.sql
index 85b8e4ef6..e312cfbcd 100644
--- a/SQL/mssql.initial.sql
+++ b/SQL/mssql.initial.sql
@@ -1,360 +1,376 @@
CREATE TABLE [dbo].[cache] (
[user_id] [int] NOT NULL ,
[cache_key] [varchar] (128) COLLATE Latin1_General_CI_AI NOT NULL ,
[created] [datetime] NOT NULL ,
[data] [text] COLLATE Latin1_General_CI_AI NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
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
CREATE TABLE [dbo].[contacts] (
[contact_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 ,
[email] [varchar] (8000) COLLATE Latin1_General_CI_AI NOT NULL ,
[firstname] [varchar] (128) COLLATE Latin1_General_CI_AI NOT NULL ,
[surname] [varchar] (128) COLLATE Latin1_General_CI_AI NOT NULL ,
[vcard] [text] COLLATE Latin1_General_CI_AI NULL ,
[words] [text] COLLATE Latin1_General_CI_AI NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
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
CREATE TABLE [dbo].[identities] (
[identity_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 ,
[standard] [char] (1) COLLATE Latin1_General_CI_AI NOT NULL ,
[name] [varchar] (128) COLLATE Latin1_General_CI_AI NOT NULL ,
[organization] [varchar] (128) COLLATE Latin1_General_CI_AI NOT NULL ,
[email] [varchar] (128) COLLATE Latin1_General_CI_AI NOT NULL ,
[reply-to] [varchar] (128) COLLATE Latin1_General_CI_AI NOT NULL ,
[bcc] [varchar] (128) COLLATE Latin1_General_CI_AI NOT NULL ,
[signature] [text] COLLATE Latin1_General_CI_AI NULL,
[html_signature] [char] (1) COLLATE Latin1_General_CI_AI NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
CREATE TABLE [dbo].[session] (
[sess_id] [varchar] (128) COLLATE Latin1_General_CI_AI NOT NULL ,
[created] [datetime] NOT NULL ,
[changed] [datetime] NULL ,
[ip] [varchar] (40) COLLATE Latin1_General_CI_AI NOT NULL ,
[vars] [text] COLLATE Latin1_General_CI_AI NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
CREATE TABLE [dbo].[users] (
[user_id] [int] IDENTITY (1, 1) NOT NULL ,
[username] [varchar] (128) COLLATE Latin1_General_CI_AI NOT NULL ,
[mail_host] [varchar] (128) COLLATE Latin1_General_CI_AI NOT NULL ,
[created] [datetime] NOT NULL ,
[last_login] [datetime] NULL ,
[language] [varchar] (5) COLLATE Latin1_General_CI_AI NULL ,
[preferences] [text] COLLATE Latin1_General_CI_AI NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
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 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
+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
(
[user_id],[mailbox]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[cache_thread] WITH NOCHECK ADD
PRIMARY KEY CLUSTERED
(
[user_id],[mailbox]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[cache_messages] WITH NOCHECK ADD
PRIMARY KEY CLUSTERED
(
[user_id],[mailbox],[uid]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[contacts] WITH NOCHECK ADD
CONSTRAINT [PK_contacts_contact_id] PRIMARY KEY CLUSTERED
(
[contact_id]
) 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].[identities] WITH NOCHECK ADD
PRIMARY KEY CLUSTERED
(
[identity_id]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[session] WITH NOCHECK ADD
CONSTRAINT [PK_session_sess_id] PRIMARY KEY CLUSTERED
(
[sess_id]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[users] WITH NOCHECK ADD
CONSTRAINT [PK_users_user_id] PRIMARY KEY CLUSTERED
(
[user_id]
) 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].[system] WITH NOCHECK ADD
+ CONSTRAINT [PK_system_name] PRIMARY KEY CLUSTERED
+ (
+ [name]
+ ) ON [PRIMARY]
+GO
+
ALTER TABLE [dbo].[cache] ADD
CONSTRAINT [DF_cache_user_id] DEFAULT ('0') FOR [user_id],
CONSTRAINT [DF_cache_cache_key] DEFAULT ('') FOR [cache_key],
CONSTRAINT [DF_cache_created] DEFAULT (getdate()) FOR [created]
GO
CREATE INDEX [IX_cache_user_id] ON [dbo].[cache]([user_id]) ON [PRIMARY]
GO
CREATE INDEX [IX_cache_cache_key] ON [dbo].[cache]([cache_key]) ON [PRIMARY]
GO
CREATE INDEX [IX_cache_created] ON [dbo].[cache]([created]) ON [PRIMARY]
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].[contacts] ADD
CONSTRAINT [DF_contacts_user_id] DEFAULT (0) FOR [user_id],
CONSTRAINT [DF_contacts_changed] DEFAULT (getdate()) FOR [changed],
CONSTRAINT [DF_contacts_del] DEFAULT ('0') FOR [del],
CONSTRAINT [DF_contacts_name] DEFAULT ('') FOR [name],
CONSTRAINT [DF_contacts_email] DEFAULT ('') FOR [email],
CONSTRAINT [DF_contacts_firstname] DEFAULT ('') FOR [firstname],
CONSTRAINT [DF_contacts_surname] DEFAULT ('') FOR [surname],
CONSTRAINT [CK_contacts_del] CHECK ([del] = '1' or [del] = '0')
GO
CREATE INDEX [IX_contacts_user_id] ON [dbo].[contacts]([user_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
CREATE INDEX [IX_contactgroupmembers_contact_id] ON [dbo].[contactgroupmembers]([contact_id]) ON [PRIMARY]
GO
ALTER TABLE [dbo].[identities] ADD
CONSTRAINT [DF_identities_user] DEFAULT ('0') FOR [user_id],
CONSTRAINT [DF_identities_del] DEFAULT ('0') FOR [del],
CONSTRAINT [DF_identities_standard] DEFAULT ('0') FOR [standard],
CONSTRAINT [DF_identities_name] DEFAULT ('') FOR [name],
CONSTRAINT [DF_identities_organization] DEFAULT ('') FOR [organization],
CONSTRAINT [DF_identities_email] DEFAULT ('') FOR [email],
CONSTRAINT [DF_identities_reply] DEFAULT ('') FOR [reply-to],
CONSTRAINT [DF_identities_bcc] DEFAULT ('') FOR [bcc],
CONSTRAINT [DF_identities_html_signature] DEFAULT ('0') FOR [html_signature],
CHECK ([standard] = '1' or [standard] = '0'),
CHECK ([del] = '1' or [del] = '0')
GO
CREATE INDEX [IX_identities_user_id] ON [dbo].[identities]([user_id]) ON [PRIMARY]
GO
CREATE INDEX [IX_identities_email] ON [dbo].[identities]([email],[del]) ON [PRIMARY]
GO
ALTER TABLE [dbo].[session] ADD
CONSTRAINT [DF_session_sess_id] DEFAULT ('') FOR [sess_id],
CONSTRAINT [DF_session_created] DEFAULT (getdate()) FOR [created],
CONSTRAINT [DF_session_ip] DEFAULT ('') FOR [ip]
GO
CREATE INDEX [IX_session_changed] ON [dbo].[session]([changed]) ON [PRIMARY]
GO
ALTER TABLE [dbo].[users] ADD
CONSTRAINT [DF_users_username] DEFAULT ('') FOR [username],
CONSTRAINT [DF_users_mail_host] DEFAULT ('') FOR [mail_host],
CONSTRAINT [DF_users_created] DEFAULT (getdate()) FOR [created]
GO
CREATE UNIQUE INDEX [IX_users_username] ON [dbo].[users]([username],[mail_host]) ON [PRIMARY]
GO
CREATE UNIQUE INDEX [IX_dictionary_user_language] ON [dbo].[dictionary]([user_id],[language]) 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].[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].[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].[contactgroups] ADD CONSTRAINT [FK_contactgroups_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].[cache_index] ADD CONSTRAINT [FK_cache_index_user_id]
FOREIGN KEY ([user_id]) REFERENCES [dbo].[users] ([user_id])
ON DELETE CASCADE ON UPDATE CASCADE
GO
ALTER TABLE [dbo].[cache_thread] ADD CONSTRAINT [FK_cache_thread_user_id]
FOREIGN KEY ([user_id]) REFERENCES [dbo].[users] ([user_id])
ON DELETE CASCADE ON UPDATE CASCADE
GO
ALTER TABLE [dbo].[cache_messages] ADD CONSTRAINT [FK_cache_messages_user_id]
FOREIGN KEY ([user_id]) REFERENCES [dbo].[users] ([user_id])
ON DELETE CASCADE ON UPDATE CASCADE
GO
ALTER TABLE [dbo].[contactgroupmembers] ADD CONSTRAINT [FK_contactgroupmembers_contactgroup_id]
FOREIGN KEY ([contactgroup_id]) REFERENCES [dbo].[contactgroups] ([contactgroup_id])
ON DELETE CASCADE ON UPDATE CASCADE
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
-- Use trigger instead of foreign key (#1487112)
-- "Introducing FOREIGN KEY constraint ... may cause cycles or multiple cascade paths."
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
+INSERT INTO [dbo].[system] ([name], [value]) VALUES ('roundcube-version', '2013011000.sql')
+GO
+
\ No newline at end of file
diff --git a/SQL/mssql.upgrade.sql b/SQL/mssql.upgrade.sql
deleted file mode 100644
index 26001e713..000000000
--- a/SQL/mssql.upgrade.sql
+++ /dev/null
@@ -1,273 +0,0 @@
--- Roundcube Webmail update script for MSSQL databases
-
--- Updates from version 0.3.1
-
-ALTER TABLE [dbo].[messages] ADD CONSTRAINT [FK_messages_user_id]
- FOREIGN KEY ([user_id]) REFERENCES [dbo].[users] ([user_id])
- ON DELETE CASCADE ON UPDATE CASCADE
-GO
-
-ALTER TABLE [dbo].[cache] ADD CONSTRAINT [FK_cache_user_id]
- FOREIGN KEY ([user_id]) REFERENCES [dbo].[users] ([user_id])
- ON DELETE CASCADE ON UPDATE CASCADE
-GO
-
-ALTER TABLE [dbo].[contacts] ADD CONSTRAINT [FK_contacts_user_id]
- FOREIGN KEY ([user_id]) REFERENCES [dbo].[users] ([user_id])
- ON DELETE CASCADE ON UPDATE CASCADE
-GO
-
-ALTER TABLE [dbo].[identities] ADD CONSTRAINT [FK_identities_user_id]
- FOREIGN KEY ([user_id]) REFERENCES [dbo].[users] ([user_id])
- ON DELETE CASCADE ON UPDATE CASCADE
-GO
-
-ALTER TABLE [dbo].[identities] ADD [changed] [datetime] NULL
-GO
-
-CREATE TABLE [dbo].[contactgroups] (
- [contactgroup_id] [int] IDENTITY (1, 1) NOT NULL ,
- [user_id] [int] NOT NULL ,
- [changed] [datetime] NOT NULL ,
- [del] [char] (1) COLLATE Latin1_General_CI_AI NOT NULL ,
- [name] [varchar] (128) COLLATE Latin1_General_CI_AI NOT NULL
-) ON [PRIMARY]
-GO
-
-CREATE TABLE [dbo].[contactgroupmembers] (
- [contactgroup_id] [int] NOT NULL ,
- [contact_id] [int] NOT NULL ,
- [created] [datetime] NOT NULL
-) ON [PRIMARY]
-GO
-
-ALTER TABLE [dbo].[contactgroups] WITH NOCHECK ADD
- CONSTRAINT [PK_contactgroups_contactgroup_id] PRIMARY KEY CLUSTERED
- (
- [contactgroup_id]
- ) ON [PRIMARY]
-GO
-
-ALTER TABLE [dbo].[contactgroupmembers] WITH NOCHECK ADD
- CONSTRAINT [PK_contactgroupmembers_id] PRIMARY KEY CLUSTERED
- (
- [contactgroup_id], [contact_id]
- ) ON [PRIMARY]
-GO
-
-ALTER TABLE [dbo].[contactgroups] ADD
- CONSTRAINT [DF_contactgroups_user_id] DEFAULT (0) FOR [user_id],
- CONSTRAINT [DF_contactgroups_changed] DEFAULT (getdate()) FOR [changed],
- CONSTRAINT [DF_contactgroups_del] DEFAULT ('0') FOR [del],
- CONSTRAINT [DF_contactgroups_name] DEFAULT ('') FOR [name],
- CONSTRAINT [CK_contactgroups_del] CHECK ([del] = '1' or [del] = '0')
-GO
-
-CREATE INDEX [IX_contactgroups_user_id] ON [dbo].[contacts]([user_id]) ON [PRIMARY]
-GO
-
-ALTER TABLE [dbo].[contactgroupmembers] ADD
- CONSTRAINT [DF_contactgroupmembers_contactgroup_id] DEFAULT (0) FOR [contactgroup_id],
- CONSTRAINT [DF_contactgroupmembers_contact_id] DEFAULT (0) FOR [contact_id],
- CONSTRAINT [DF_contactgroupmembers_created] DEFAULT (getdate()) FOR [created]
-GO
-
-ALTER TABLE [dbo].[contactgroupmembers] ADD CONSTRAINT [FK_contactgroupmembers_contactgroup_id]
- FOREIGN KEY ([contactgroup_id]) REFERENCES [dbo].[contactgroups] ([contactgroup_id])
- ON DELETE CASCADE ON UPDATE CASCADE
-GO
-
-CREATE TRIGGER [contact_delete_member] ON [dbo].[contacts]
- AFTER DELETE AS
- DELETE FROM [dbo].[contactgroupmembers]
- WHERE [contact_id] IN (SELECT [contact_id] FROM deleted)
-GO
-
-ALTER TABLE [dbo].[contactgroups] ADD CONSTRAINT [FK_contactgroups_user_id]
- FOREIGN KEY ([user_id]) REFERENCES [dbo].[users] ([user_id])
- ON DELETE CASCADE ON UPDATE CASCADE
-GO
-
--- Updates from version 0.4.2
-
-DROP INDEX [IX_users_username]
-GO
-CREATE UNIQUE INDEX [IX_users_username] ON [dbo].[users]([username],[mail_host]) ON [PRIMARY]
-GO
-ALTER TABLE [dbo].[contacts] ALTER COLUMN [email] [varchar] (255) COLLATE Latin1_General_CI_AI NOT NULL
-GO
-
--- Updates from version 0.5.1
--- Updates from version 0.5.2
--- Updates from version 0.5.3
--- Updates from version 0.5.4
-
-ALTER TABLE [dbo].[contacts] ADD [words] [text] COLLATE Latin1_General_CI_AI NULL
-GO
-CREATE INDEX [IX_contactgroupmembers_contact_id] ON [dbo].[contactgroupmembers]([contact_id]) ON [PRIMARY]
-GO
-DELETE FROM [dbo].[messages]
-GO
-DELETE FROM [dbo].[cache]
-GO
-
--- Updates from version 0.6
-
-CREATE TABLE [dbo].[dictionary] (
- [user_id] [int] ,
- [language] [varchar] (5) COLLATE Latin1_General_CI_AI NOT NULL ,
- [data] [text] COLLATE Latin1_General_CI_AI NOT NULL
-) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
-GO
-CREATE UNIQUE INDEX [IX_dictionary_user_language] ON [dbo].[dictionary]([user_id],[language]) ON [PRIMARY]
-GO
-
-CREATE TABLE [dbo].[searches] (
- [search_id] [int] IDENTITY (1, 1) NOT NULL ,
- [user_id] [int] NOT NULL ,
- [type] [tinyint] NOT NULL ,
- [name] [varchar] (128) COLLATE Latin1_General_CI_AI NOT NULL ,
- [data] [text] COLLATE Latin1_General_CI_AI NOT NULL
-) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
-GO
-
-ALTER TABLE [dbo].[searches] WITH NOCHECK ADD
- CONSTRAINT [PK_searches_search_id] PRIMARY KEY CLUSTERED
- (
- [search_id]
- ) ON [PRIMARY]
-GO
-
-ALTER TABLE [dbo].[searches] ADD
- CONSTRAINT [DF_searches_user] DEFAULT (0) FOR [user_id],
- CONSTRAINT [DF_searches_type] DEFAULT (0) FOR [type],
-GO
-
-CREATE UNIQUE INDEX [IX_searches_user_type_name] ON [dbo].[searches]([user_id],[type],[name]) ON [PRIMARY]
-GO
-
-ALTER TABLE [dbo].[searches] ADD CONSTRAINT [FK_searches_user_id]
- FOREIGN KEY ([user_id]) REFERENCES [dbo].[users] ([user_id])
- ON DELETE CASCADE ON UPDATE CASCADE
-GO
-
-DROP TABLE [dbo].[messages]
-GO
-CREATE TABLE [dbo].[cache_index] (
- [user_id] [int] NOT NULL ,
- [mailbox] [varchar] (128) COLLATE Latin1_General_CI_AI NOT NULL ,
- [changed] [datetime] NOT NULL ,
- [valid] [char] (1) COLLATE Latin1_General_CI_AI NOT NULL ,
- [data] [text] COLLATE Latin1_General_CI_AI NOT NULL
-) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
-GO
-
-CREATE TABLE [dbo].[cache_thread] (
- [user_id] [int] NOT NULL ,
- [mailbox] [varchar] (128) COLLATE Latin1_General_CI_AI NOT NULL ,
- [changed] [datetime] NOT NULL ,
- [data] [text] COLLATE Latin1_General_CI_AI NOT NULL
-) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
-GO
-
-CREATE TABLE [dbo].[cache_messages] (
- [user_id] [int] NOT NULL ,
- [mailbox] [varchar] (128) COLLATE Latin1_General_CI_AI NOT NULL ,
- [uid] [int] NOT NULL ,
- [changed] [datetime] NOT NULL ,
- [data] [text] COLLATE Latin1_General_CI_AI NOT NULL ,
- [flags] [int] NOT NULL
-) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
-GO
-
-ALTER TABLE [dbo].[cache_index] WITH NOCHECK ADD
- PRIMARY KEY CLUSTERED
- (
- [user_id],[mailbox]
- ) ON [PRIMARY]
-GO
-
-ALTER TABLE [dbo].[cache_thread] WITH NOCHECK ADD
- PRIMARY KEY CLUSTERED
- (
- [user_id],[mailbox]
- ) ON [PRIMARY]
-GO
-
-ALTER TABLE [dbo].[cache_messages] WITH NOCHECK ADD
- PRIMARY KEY CLUSTERED
- (
- [user_id],[mailbox],[uid]
- ) ON [PRIMARY]
-GO
-
-ALTER TABLE [dbo].[cache_index] ADD
- CONSTRAINT [DF_cache_index_changed] DEFAULT (getdate()) FOR [changed],
- CONSTRAINT [DF_cache_index_valid] DEFAULT ('0') FOR [valid]
-GO
-
-CREATE INDEX [IX_cache_index_user_id] ON [dbo].[cache_index]([user_id]) ON [PRIMARY]
-GO
-
-ALTER TABLE [dbo].[cache_thread] ADD
- CONSTRAINT [DF_cache_thread_changed] DEFAULT (getdate()) FOR [changed]
-GO
-
-CREATE INDEX [IX_cache_thread_user_id] ON [dbo].[cache_thread]([user_id]) ON [PRIMARY]
-GO
-
-ALTER TABLE [dbo].[cache_messages] ADD
- CONSTRAINT [DF_cache_messages_changed] DEFAULT (getdate()) FOR [changed],
- CONSTRAINT [DF_cache_messages_flags] DEFAULT (0) FOR [flags]
-GO
-
-CREATE INDEX [IX_cache_messages_user_id] ON [dbo].[cache_messages]([user_id]) ON [PRIMARY]
-GO
-
-ALTER TABLE [dbo].[cache_index] ADD CONSTRAINT [FK_cache_index_user_id]
- FOREIGN KEY ([user_id]) REFERENCES [dbo].[users] ([user_id])
- ON DELETE CASCADE ON UPDATE CASCADE
-GO
-
-ALTER TABLE [dbo].[cache_thread] ADD CONSTRAINT [FK_cache_thread_user_id]
- FOREIGN KEY ([user_id]) REFERENCES [dbo].[users] ([user_id])
- ON DELETE CASCADE ON UPDATE CASCADE
-GO
-
-ALTER TABLE [dbo].[cache_messages] ADD CONSTRAINT [FK_cache_messages_user_id]
- FOREIGN KEY ([user_id]) REFERENCES [dbo].[users] ([user_id])
- ON DELETE CASCADE ON UPDATE CASCADE
-GO
-
--- Updates from version 0.7-beta
-
-ALTER TABLE [dbo].[session] ALTER COLUMN [sess_id] [varchar] (128) COLLATE Latin1_General_CI_AI NOT NULL
-GO
-
--- Updates from version 0.7
-
-ALTER TABLE [dbo].[contacts] DROP CONSTRAINT [DF_contacts_email]
-GO
-ALTER TABLE [dbo].[contacts] ALTER COLUMN [email] [text] COLLATE Latin1_General_CI_AI NOT NULL
-GO
-ALTER TABLE [dbo].[contacts] ADD CONSTRAINT [DF_contacts_email] DEFAULT ('') FOR [email]
-GO
-
--- Updates from version 0.8-rc
-
-ALTER TABLE [dbo].[contacts] DROP CONSTRAINT [DF_contacts_email]
-GO
-ALTER TABLE [dbo].[contacts] ALTER COLUMN [email] [varchar] (8000) COLLATE Latin1_General_CI_AI NOT NULL
-GO
-ALTER TABLE [dbo].[contacts] ADD CONSTRAINT [DF_contacts_email] DEFAULT ('') FOR [email]
-GO
-
--- Updates from version 0.8
-
-ALTER TABLE [dbo].[cache] DROP COLUMN [cache_id]
-GO
-ALTER TABLE [dbo].[users] DROP COLUMN [alias]
-GO
-CREATE INDEX [IX_identities_email] ON [dbo].[identities]([email],[del]) ON [PRIMARY]
-GO
-
\ No newline at end of file
diff --git a/SQL/mssql/2009103100.sql b/SQL/mssql/2009103100.sql
new file mode 100644
index 000000000..646fe38af
--- /dev/null
+++ b/SQL/mssql/2009103100.sql
@@ -0,0 +1,87 @@
+-- Updates from version 0.3.1
+
+ALTER TABLE [dbo].[messages] ADD CONSTRAINT [FK_messages_user_id]
+ FOREIGN KEY ([user_id]) REFERENCES [dbo].[users] ([user_id])
+ ON DELETE CASCADE ON UPDATE CASCADE
+GO
+
+ALTER TABLE [dbo].[cache] ADD CONSTRAINT [FK_cache_user_id]
+ FOREIGN KEY ([user_id]) REFERENCES [dbo].[users] ([user_id])
+ ON DELETE CASCADE ON UPDATE CASCADE
+GO
+
+ALTER TABLE [dbo].[contacts] ADD CONSTRAINT [FK_contacts_user_id]
+ FOREIGN KEY ([user_id]) REFERENCES [dbo].[users] ([user_id])
+ ON DELETE CASCADE ON UPDATE CASCADE
+GO
+
+ALTER TABLE [dbo].[identities] ADD CONSTRAINT [FK_identities_user_id]
+ FOREIGN KEY ([user_id]) REFERENCES [dbo].[users] ([user_id])
+ ON DELETE CASCADE ON UPDATE CASCADE
+GO
+
+ALTER TABLE [dbo].[identities] ADD [changed] [datetime] NULL
+GO
+
+CREATE TABLE [dbo].[contactgroups] (
+ [contactgroup_id] [int] IDENTITY (1, 1) NOT NULL ,
+ [user_id] [int] NOT NULL ,
+ [changed] [datetime] NOT NULL ,
+ [del] [char] (1) COLLATE Latin1_General_CI_AI NOT NULL ,
+ [name] [varchar] (128) COLLATE Latin1_General_CI_AI NOT NULL
+) ON [PRIMARY]
+GO
+
+CREATE TABLE [dbo].[contactgroupmembers] (
+ [contactgroup_id] [int] NOT NULL ,
+ [contact_id] [int] NOT NULL ,
+ [created] [datetime] NOT NULL
+) ON [PRIMARY]
+GO
+
+ALTER TABLE [dbo].[contactgroups] WITH NOCHECK ADD
+ CONSTRAINT [PK_contactgroups_contactgroup_id] PRIMARY KEY CLUSTERED
+ (
+ [contactgroup_id]
+ ) ON [PRIMARY]
+GO
+
+ALTER TABLE [dbo].[contactgroupmembers] WITH NOCHECK ADD
+ CONSTRAINT [PK_contactgroupmembers_id] PRIMARY KEY CLUSTERED
+ (
+ [contactgroup_id], [contact_id]
+ ) ON [PRIMARY]
+GO
+
+ALTER TABLE [dbo].[contactgroups] ADD
+ CONSTRAINT [DF_contactgroups_user_id] DEFAULT (0) FOR [user_id],
+ CONSTRAINT [DF_contactgroups_changed] DEFAULT (getdate()) FOR [changed],
+ CONSTRAINT [DF_contactgroups_del] DEFAULT ('0') FOR [del],
+ CONSTRAINT [DF_contactgroups_name] DEFAULT ('') FOR [name],
+ CONSTRAINT [CK_contactgroups_del] CHECK ([del] = '1' or [del] = '0')
+GO
+
+CREATE INDEX [IX_contactgroups_user_id] ON [dbo].[contacts]([user_id]) ON [PRIMARY]
+GO
+
+ALTER TABLE [dbo].[contactgroupmembers] ADD
+ CONSTRAINT [DF_contactgroupmembers_contactgroup_id] DEFAULT (0) FOR [contactgroup_id],
+ CONSTRAINT [DF_contactgroupmembers_contact_id] DEFAULT (0) FOR [contact_id],
+ CONSTRAINT [DF_contactgroupmembers_created] DEFAULT (getdate()) FOR [created]
+GO
+
+ALTER TABLE [dbo].[contactgroupmembers] ADD CONSTRAINT [FK_contactgroupmembers_contactgroup_id]
+ FOREIGN KEY ([contactgroup_id]) REFERENCES [dbo].[contactgroups] ([contactgroup_id])
+ ON DELETE CASCADE ON UPDATE CASCADE
+GO
+
+CREATE TRIGGER [contact_delete_member] ON [dbo].[contacts]
+ AFTER DELETE AS
+ DELETE FROM [dbo].[contactgroupmembers]
+ WHERE [contact_id] IN (SELECT [contact_id] FROM deleted)
+GO
+
+ALTER TABLE [dbo].[contactgroups] ADD CONSTRAINT [FK_contactgroups_user_id]
+ FOREIGN KEY ([user_id]) REFERENCES [dbo].[users] ([user_id])
+ ON DELETE CASCADE ON UPDATE CASCADE
+GO
diff --git a/SQL/mssql/2010100600.sql b/SQL/mssql/2010100600.sql
new file mode 100644
index 000000000..fb045e303
--- /dev/null
+++ b/SQL/mssql/2010100600.sql
@@ -0,0 +1,9 @@
+-- Updates from version 0.4.2
+
+DROP INDEX [IX_users_username]
+GO
+CREATE UNIQUE INDEX [IX_users_username] ON [dbo].[users]([username],[mail_host]) ON [PRIMARY]
+GO
+ALTER TABLE [dbo].[contacts] ALTER COLUMN [email] [varchar] (255) COLLATE Latin1_General_CI_AI NOT NULL
+GO
+
\ No newline at end of file
diff --git a/SQL/mssql/2011011200.sql b/SQL/mssql/2011011200.sql
new file mode 100644
index 000000000..8715455df
--- /dev/null
+++ b/SQL/mssql/2011011200.sql
@@ -0,0 +1,10 @@
+-- Updates from version 0.5.x
+
+ALTER TABLE [dbo].[contacts] ADD [words] [text] COLLATE Latin1_General_CI_AI NULL
+GO
+CREATE INDEX [IX_contactgroupmembers_contact_id] ON [dbo].[contactgroupmembers]([contact_id]) ON [PRIMARY]
+GO
+DELETE FROM [dbo].[messages]
+GO
+DELETE FROM [dbo].[cache]
+GO
diff --git a/SQL/mssql/2011092800.sql b/SQL/mssql/2011092800.sql
new file mode 100644
index 000000000..00f79bbc0
--- /dev/null
+++ b/SQL/mssql/2011092800.sql
@@ -0,0 +1,127 @@
+-- Updates from version 0.6
+
+CREATE TABLE [dbo].[dictionary] (
+ [user_id] [int] ,
+ [language] [varchar] (5) COLLATE Latin1_General_CI_AI NOT NULL ,
+ [data] [text] COLLATE Latin1_General_CI_AI NOT NULL
+) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
+GO
+CREATE UNIQUE INDEX [IX_dictionary_user_language] ON [dbo].[dictionary]([user_id],[language]) ON [PRIMARY]
+GO
+
+CREATE TABLE [dbo].[searches] (
+ [search_id] [int] IDENTITY (1, 1) NOT NULL ,
+ [user_id] [int] NOT NULL ,
+ [type] [tinyint] NOT NULL ,
+ [name] [varchar] (128) COLLATE Latin1_General_CI_AI NOT NULL ,
+ [data] [text] COLLATE Latin1_General_CI_AI NOT NULL
+) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
+GO
+
+ALTER TABLE [dbo].[searches] WITH NOCHECK ADD
+ CONSTRAINT [PK_searches_search_id] PRIMARY KEY CLUSTERED
+ (
+ [search_id]
+ ) ON [PRIMARY]
+GO
+
+ALTER TABLE [dbo].[searches] ADD
+ CONSTRAINT [DF_searches_user] DEFAULT (0) FOR [user_id],
+ CONSTRAINT [DF_searches_type] DEFAULT (0) FOR [type],
+GO
+
+CREATE UNIQUE INDEX [IX_searches_user_type_name] ON [dbo].[searches]([user_id],[type],[name]) ON [PRIMARY]
+GO
+
+ALTER TABLE [dbo].[searches] ADD CONSTRAINT [FK_searches_user_id]
+ FOREIGN KEY ([user_id]) REFERENCES [dbo].[users] ([user_id])
+ ON DELETE CASCADE ON UPDATE CASCADE
+GO
+
+DROP TABLE [dbo].[messages]
+GO
+CREATE TABLE [dbo].[cache_index] (
+ [user_id] [int] NOT NULL ,
+ [mailbox] [varchar] (128) COLLATE Latin1_General_CI_AI NOT NULL ,
+ [changed] [datetime] NOT NULL ,
+ [valid] [char] (1) COLLATE Latin1_General_CI_AI NOT NULL ,
+ [data] [text] COLLATE Latin1_General_CI_AI NOT NULL
+) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
+GO
+
+CREATE TABLE [dbo].[cache_thread] (
+ [user_id] [int] NOT NULL ,
+ [mailbox] [varchar] (128) COLLATE Latin1_General_CI_AI NOT NULL ,
+ [changed] [datetime] NOT NULL ,
+ [data] [text] COLLATE Latin1_General_CI_AI NOT NULL
+) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
+GO
+
+CREATE TABLE [dbo].[cache_messages] (
+ [user_id] [int] NOT NULL ,
+ [mailbox] [varchar] (128) COLLATE Latin1_General_CI_AI NOT NULL ,
+ [uid] [int] NOT NULL ,
+ [changed] [datetime] NOT NULL ,
+ [data] [text] COLLATE Latin1_General_CI_AI NOT NULL ,
+ [flags] [int] NOT NULL
+) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
+GO
+
+ALTER TABLE [dbo].[cache_index] WITH NOCHECK ADD
+ PRIMARY KEY CLUSTERED
+ (
+ [user_id],[mailbox]
+ ) ON [PRIMARY]
+GO
+
+ALTER TABLE [dbo].[cache_thread] WITH NOCHECK ADD
+ PRIMARY KEY CLUSTERED
+ (
+ [user_id],[mailbox]
+ ) ON [PRIMARY]
+GO
+
+ALTER TABLE [dbo].[cache_messages] WITH NOCHECK ADD
+ PRIMARY KEY CLUSTERED
+ (
+ [user_id],[mailbox],[uid]
+ ) ON [PRIMARY]
+GO
+
+ALTER TABLE [dbo].[cache_index] ADD
+ CONSTRAINT [DF_cache_index_changed] DEFAULT (getdate()) FOR [changed],
+ CONSTRAINT [DF_cache_index_valid] DEFAULT ('0') FOR [valid]
+GO
+
+CREATE INDEX [IX_cache_index_user_id] ON [dbo].[cache_index]([user_id]) ON [PRIMARY]
+GO
+
+ALTER TABLE [dbo].[cache_thread] ADD
+ CONSTRAINT [DF_cache_thread_changed] DEFAULT (getdate()) FOR [changed]
+GO
+
+CREATE INDEX [IX_cache_thread_user_id] ON [dbo].[cache_thread]([user_id]) ON [PRIMARY]
+GO
+
+ALTER TABLE [dbo].[cache_messages] ADD
+ CONSTRAINT [DF_cache_messages_changed] DEFAULT (getdate()) FOR [changed],
+ CONSTRAINT [DF_cache_messages_flags] DEFAULT (0) FOR [flags]
+GO
+
+CREATE INDEX [IX_cache_messages_user_id] ON [dbo].[cache_messages]([user_id]) ON [PRIMARY]
+GO
+
+ALTER TABLE [dbo].[cache_index] ADD CONSTRAINT [FK_cache_index_user_id]
+ FOREIGN KEY ([user_id]) REFERENCES [dbo].[users] ([user_id])
+ ON DELETE CASCADE ON UPDATE CASCADE
+GO
+
+ALTER TABLE [dbo].[cache_thread] ADD CONSTRAINT [FK_cache_thread_user_id]
+ FOREIGN KEY ([user_id]) REFERENCES [dbo].[users] ([user_id])
+ ON DELETE CASCADE ON UPDATE CASCADE
+GO
+
+ALTER TABLE [dbo].[cache_messages] ADD CONSTRAINT [FK_cache_messages_user_id]
+ FOREIGN KEY ([user_id]) REFERENCES [dbo].[users] ([user_id])
+ ON DELETE CASCADE ON UPDATE CASCADE
+GO
diff --git a/SQL/mssql/2011111600.sql b/SQL/mssql/2011111600.sql
new file mode 100644
index 000000000..387877604
--- /dev/null
+++ b/SQL/mssql/2011111600.sql
@@ -0,0 +1,4 @@
+-- Updates from version 0.7-beta
+
+ALTER TABLE [dbo].[session] ALTER COLUMN [sess_id] [varchar] (128) COLLATE Latin1_General_CI_AI NOT NULL
+GO
diff --git a/SQL/mssql/2011121400.sql b/SQL/mssql/2011121400.sql
new file mode 100644
index 000000000..fde63ea79
--- /dev/null
+++ b/SQL/mssql/2011121400.sql
@@ -0,0 +1,9 @@
+-- Updates from version 0.7
+
+ALTER TABLE [dbo].[contacts] DROP CONSTRAINT [DF_contacts_email]
+GO
+ALTER TABLE [dbo].[contacts] ALTER COLUMN [email] [text] COLLATE Latin1_General_CI_AI NOT NULL
+GO
+ALTER TABLE [dbo].[contacts] ADD CONSTRAINT [DF_contacts_email] DEFAULT ('') FOR [email]
+GO
+
\ No newline at end of file
diff --git a/SQL/mssql/2012051800.sql b/SQL/mssql/2012051800.sql
new file mode 100644
index 000000000..8dcf7bfc0
--- /dev/null
+++ b/SQL/mssql/2012051800.sql
@@ -0,0 +1,18 @@
+-- Updates from version 0.8-rc
+
+ALTER TABLE [dbo].[contacts] DROP CONSTRAINT [DF_contacts_email]
+GO
+ALTER TABLE [dbo].[contacts] ALTER COLUMN [email] [varchar] (8000) COLLATE Latin1_General_CI_AI NOT NULL
+GO
+ALTER TABLE [dbo].[contacts] ADD CONSTRAINT [DF_contacts_email] DEFAULT ('') FOR [email]
+GO
+
+-- Updates from version 0.8
+
+ALTER TABLE [dbo].[cache] DROP COLUMN [cache_id]
+GO
+ALTER TABLE [dbo].[users] DROP COLUMN [alias]
+GO
+CREATE INDEX [IX_identities_email] ON [dbo].[identities]([email],[del]) ON [PRIMARY]
+GO
+
\ No newline at end of file
diff --git a/SQL/mssql/2012080700.sql b/SQL/mssql/2012080700.sql
new file mode 100644
index 000000000..33e6133b3
--- /dev/null
+++ b/SQL/mssql/2012080700.sql
@@ -0,0 +1,8 @@
+-- Updates from version 0.8
+
+ALTER TABLE [dbo].[cache] DROP COLUMN [cache_id]
+GO
+ALTER TABLE [dbo].[users] DROP COLUMN [alias]
+GO
+CREATE INDEX [IX_identities_email] ON [dbo].[identities]([email],[del]) ON [PRIMARY]
+GO
diff --git a/SQL/mssql/2013011000.sql b/SQL/mssql/2013011000.sql
new file mode 100644
index 000000000..2eb51e6b5
--- /dev/null
+++ b/SQL/mssql/2013011000.sql
@@ -0,0 +1,14 @@
+-- Upgrades from 0.9-beta
+
+CREATE TABLE [dbo].[system] (
+ [name] [varchar] (64) COLLATE Latin1_General_CI_AI NOT NULL ,
+ [value] [text] COLLATE Latin1_General_CI_AI
+) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
+GO
+
+ALTER TABLE [dbo].[system] WITH NOCHECK ADD
+ CONSTRAINT [PK_system_name] PRIMARY KEY CLUSTERED
+ (
+ [name]
+ ) ON [PRIMARY]
+GO
diff --git a/SQL/mysql.initial.sql b/SQL/mysql.initial.sql
index 47d9db4a2..bb1856cf7 100644
--- a/SQL/mysql.initial.sql
+++ b/SQL/mysql.initial.sql
@@ -1,189 +1,199 @@
-- Roundcube Webmail initial database structure
/*!40014 SET FOREIGN_KEY_CHECKS=0 */;
-- Table structure for table `session`
CREATE TABLE `session` (
`sess_id` varchar(128) NOT NULL,
`created` datetime NOT NULL DEFAULT '1000-01-01 00:00:00',
`changed` datetime NOT NULL DEFAULT '1000-01-01 00:00:00',
`ip` varchar(40) NOT NULL,
`vars` mediumtext NOT NULL,
PRIMARY KEY(`sess_id`),
INDEX `changed_index` (`changed`)
) /*!40000 ENGINE=INNODB */ /*!40101 CHARACTER SET utf8 COLLATE utf8_general_ci */;
-- Table structure for table `users`
CREATE TABLE `users` (
`user_id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`username` varchar(128) BINARY NOT NULL,
`mail_host` varchar(128) NOT NULL,
`created` datetime NOT NULL DEFAULT '1000-01-01 00:00:00',
`last_login` datetime DEFAULT NULL,
`language` varchar(5),
`preferences` text,
PRIMARY KEY(`user_id`),
UNIQUE `username` (`username`, `mail_host`)
) /*!40000 ENGINE=INNODB */ /*!40101 CHARACTER SET utf8 COLLATE utf8_general_ci */;
-- Table structure for table `cache`
CREATE TABLE `cache` (
`user_id` int(10) UNSIGNED NOT NULL,
`cache_key` varchar(128) /*!40101 CHARACTER SET ascii COLLATE ascii_general_ci */ NOT NULL ,
`created` datetime NOT NULL DEFAULT '1000-01-01 00:00:00',
`data` longtext NOT NULL,
CONSTRAINT `user_id_fk_cache` FOREIGN KEY (`user_id`)
REFERENCES `users`(`user_id`) ON DELETE CASCADE ON UPDATE CASCADE,
INDEX `created_index` (`created`),
INDEX `user_cache_index` (`user_id`,`cache_key`)
) /*!40000 ENGINE=INNODB */ /*!40101 CHARACTER SET utf8 COLLATE utf8_general_ci */;
-- Table structure for table `cache_index`
CREATE TABLE `cache_index` (
`user_id` int(10) UNSIGNED NOT NULL,
`mailbox` varchar(255) BINARY NOT NULL,
`changed` datetime NOT NULL DEFAULT '1000-01-01 00:00:00',
`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 */;
-- Table structure for table `cache_thread`
CREATE TABLE `cache_thread` (
`user_id` int(10) UNSIGNED NOT NULL,
`mailbox` varchar(255) BINARY NOT NULL,
`changed` datetime NOT NULL DEFAULT '1000-01-01 00:00:00',
`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 */;
-- Table structure for table `cache_messages`
CREATE TABLE `cache_messages` (
`user_id` int(10) UNSIGNED NOT NULL,
`mailbox` varchar(255) BINARY NOT NULL,
`uid` int(11) UNSIGNED NOT NULL DEFAULT '0',
`changed` datetime NOT NULL DEFAULT '1000-01-01 00:00:00',
`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 */;
-- Table structure for table `contacts`
CREATE TABLE `contacts` (
`contact_id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`changed` datetime NOT NULL DEFAULT '1000-01-01 00:00:00',
`del` tinyint(1) NOT NULL DEFAULT '0',
`name` varchar(128) NOT NULL DEFAULT '',
`email` text NOT NULL,
`firstname` varchar(128) NOT NULL DEFAULT '',
`surname` varchar(128) NOT NULL DEFAULT '',
`vcard` longtext NULL,
`words` text NULL,
`user_id` int(10) UNSIGNED NOT NULL,
PRIMARY KEY(`contact_id`),
CONSTRAINT `user_id_fk_contacts` FOREIGN KEY (`user_id`)
REFERENCES `users`(`user_id`) ON DELETE CASCADE ON UPDATE CASCADE,
INDEX `user_contacts_index` (`user_id`,`del`)
) /*!40000 ENGINE=INNODB */ /*!40101 CHARACTER SET utf8 COLLATE utf8_general_ci */;
-- Table structure for table `contactgroups`
CREATE TABLE `contactgroups` (
`contactgroup_id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`user_id` int(10) UNSIGNED NOT NULL,
`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,
`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,
INDEX `contactgroupmembers_contact_index` (`contact_id`)
) /*!40000 ENGINE=INNODB */;
-- Table structure for table `identities`
CREATE TABLE `identities` (
`identity_id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`user_id` int(10) UNSIGNED NOT NULL,
`changed` datetime NOT NULL DEFAULT '1000-01-01 00:00:00',
`del` tinyint(1) NOT NULL DEFAULT '0',
`standard` tinyint(1) NOT NULL DEFAULT '0',
`name` varchar(128) NOT NULL,
`organization` varchar(128) NOT NULL DEFAULT '',
`email` varchar(128) NOT NULL,
`reply-to` varchar(128) NOT NULL DEFAULT '',
`bcc` varchar(128) NOT NULL DEFAULT '',
`signature` text,
`html_signature` tinyint(1) NOT NULL DEFAULT '0',
PRIMARY KEY(`identity_id`),
CONSTRAINT `user_id_fk_identities` FOREIGN KEY (`user_id`)
REFERENCES `users`(`user_id`) ON DELETE CASCADE ON UPDATE CASCADE,
INDEX `user_identities_index` (`user_id`, `del`),
INDEX `email_identities_index` (`email`, `del`)
) /*!40000 ENGINE=INNODB */ /*!40101 CHARACTER SET utf8 COLLATE utf8_general_ci */;
-- Table structure for table `dictionary`
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 */;
-- Table structure for table `searches`
CREATE TABLE `searches` (
`search_id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`user_id` int(10) UNSIGNED NOT NULL,
`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 */;
+-- Table structure for table `system`
+
+CREATE TABLE `system` (
+ `name` varchar(64) NOT NULL,
+ `value` mediumtext,
+ PRIMARY KEY(`name`)
+) /*!40000 ENGINE=INNODB */ /*!40101 CHARACTER SET utf8 COLLATE utf8_general_ci */;
+
/*!40014 SET FOREIGN_KEY_CHECKS=1 */;
+
+INSERT INTO system (name, value) VALUES ('roundcube-version', '2013011000.sql');
diff --git a/SQL/mysql.update.sql b/SQL/mysql.update.sql
deleted file mode 100644
index 237aa3e38..000000000
--- a/SQL/mysql.update.sql
+++ /dev/null
@@ -1,247 +0,0 @@
--- Roundcube Webmail update script for MySQL databases
-
--- Updates from version 0.1-stable
-
-TRUNCATE TABLE `messages`;
-
-ALTER TABLE `messages`
- DROP INDEX `idx`,
- DROP INDEX `uid`;
-
-ALTER TABLE `cache`
- DROP INDEX `cache_key`,
- DROP INDEX `session_id`,
- ADD INDEX `user_cache_index` (`user_id`,`cache_key`);
-
-ALTER TABLE `users`
- ADD INDEX `username_index` (`username`),
- ADD INDEX `alias_index` (`alias`);
-
--- Updates from version 0.1.1
-
-ALTER TABLE `identities`
- MODIFY `signature` text,
- MODIFY `bcc` varchar(128) NOT NULL DEFAULT '',
- MODIFY `reply-to` varchar(128) NOT NULL DEFAULT '',
- MODIFY `organization` varchar(128) NOT NULL DEFAULT '',
- MODIFY `name` varchar(128) NOT NULL,
- MODIFY `email` varchar(128) NOT NULL;
-
--- Updates from version 0.2-alpha
-
-ALTER TABLE `messages`
- ADD INDEX `created_index` (`created`);
-
--- Updates from version 0.2-beta (InnoDB required)
-
-ALTER TABLE `cache`
- DROP `session_id`;
-
-ALTER TABLE `session`
- ADD INDEX `changed_index` (`changed`);
-
-ALTER TABLE `cache`
- ADD INDEX `created_index` (`created`);
-
-ALTER TABLE `users`
- CHANGE `language` `language` varchar(5);
-
-ALTER TABLE `cache` ENGINE=InnoDB;
-ALTER TABLE `session` ENGINE=InnoDB;
-ALTER TABLE `messages` ENGINE=InnoDB;
-ALTER TABLE `users` ENGINE=InnoDB;
-ALTER TABLE `contacts` ENGINE=InnoDB;
-ALTER TABLE `identities` ENGINE=InnoDB;
-
--- Updates from version 0.3-stable
-
-TRUNCATE `messages`;
-
-ALTER TABLE `messages`
- ADD INDEX `index_index` (`user_id`, `cache_key`, `idx`);
-
-ALTER TABLE `session`
- CHANGE `vars` `vars` MEDIUMTEXT NOT NULL;
-
-ALTER TABLE `contacts`
- ADD INDEX `user_contacts_index` (`user_id`,`email`);
-
--- Updates from version 0.3.1
--- WARNING: Make sure that all tables are using InnoDB engine!!!
--- If not, use: ALTER TABLE xxx ENGINE=InnoDB;
-
-/* MySQL bug workaround: http://bugs.mysql.com/bug.php?id=46293 */
-/*!40014 SET FOREIGN_KEY_CHECKS=0 */;
-
-ALTER TABLE `messages` DROP FOREIGN KEY `user_id_fk_messages`;
-ALTER TABLE `cache` DROP FOREIGN KEY `user_id_fk_cache`;
-ALTER TABLE `contacts` DROP FOREIGN KEY `user_id_fk_contacts`;
-ALTER TABLE `identities` DROP FOREIGN KEY `user_id_fk_identities`;
-
-ALTER TABLE `messages` ADD CONSTRAINT `user_id_fk_messages` FOREIGN KEY (`user_id`)
- REFERENCES `users`(`user_id`) ON DELETE CASCADE ON UPDATE CASCADE;
-ALTER TABLE `cache` ADD CONSTRAINT `user_id_fk_cache` FOREIGN KEY (`user_id`)
- REFERENCES `users`(`user_id`) ON DELETE CASCADE ON UPDATE CASCADE;
-ALTER TABLE `contacts` ADD CONSTRAINT `user_id_fk_contacts` FOREIGN KEY (`user_id`)
- REFERENCES `users`(`user_id`) ON DELETE CASCADE ON UPDATE CASCADE;
-ALTER TABLE `identities` ADD CONSTRAINT `user_id_fk_identities` FOREIGN KEY (`user_id`)
- REFERENCES `users`(`user_id`) ON DELETE CASCADE ON UPDATE CASCADE;
-
-ALTER TABLE `contacts` ALTER `name` SET DEFAULT '';
-ALTER TABLE `contacts` ALTER `firstname` SET DEFAULT '';
-ALTER TABLE `contacts` ALTER `surname` SET DEFAULT '';
-
-ALTER TABLE `identities` ADD INDEX `user_identities_index` (`user_id`, `del`);
-ALTER TABLE `identities` ADD `changed` datetime NOT NULL DEFAULT '1000-01-01 00:00:00' AFTER `user_id`;
-
-CREATE TABLE `contactgroups` (
- `contactgroup_id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
- `user_id` int(10) UNSIGNED NOT NULL DEFAULT '0',
- `changed` datetime NOT NULL DEFAULT '1000-01-01 00:00:00',
- `del` tinyint(1) NOT NULL DEFAULT '0',
- `name` varchar(128) NOT NULL DEFAULT '',
- PRIMARY KEY(`contactgroup_id`),
- CONSTRAINT `user_id_fk_contactgroups` FOREIGN KEY (`user_id`)
- REFERENCES `users`(`user_id`) ON DELETE CASCADE ON UPDATE CASCADE,
- INDEX `contactgroups_user_index` (`user_id`,`del`)
-) /*!40000 ENGINE=INNODB */ /*!40101 CHARACTER SET utf8 COLLATE utf8_general_ci */;
-
-CREATE TABLE `contactgroupmembers` (
- `contactgroup_id` int(10) UNSIGNED NOT NULL,
- `contact_id` int(10) UNSIGNED NOT NULL DEFAULT '0',
- `created` datetime NOT NULL DEFAULT '1000-01-01 00:00:00',
- PRIMARY KEY (`contactgroup_id`, `contact_id`),
- CONSTRAINT `contactgroup_id_fk_contactgroups` FOREIGN KEY (`contactgroup_id`)
- REFERENCES `contactgroups`(`contactgroup_id`) ON DELETE CASCADE ON UPDATE CASCADE,
- CONSTRAINT `contact_id_fk_contacts` FOREIGN KEY (`contact_id`)
- REFERENCES `contacts`(`contact_id`) ON DELETE CASCADE ON UPDATE CASCADE
-) /*!40000 ENGINE=INNODB */;
-
-/*!40014 SET FOREIGN_KEY_CHECKS=1 */;
-
--- Updates from version 0.4-beta
-
-ALTER TABLE `users` CHANGE `last_login` `last_login` datetime DEFAULT NULL;
-UPDATE `users` SET `last_login` = NULL WHERE `last_login` = '1000-01-01 00:00:00';
-
--- Updates from version 0.4.2
-
-ALTER TABLE `users` DROP INDEX `username_index`;
-ALTER TABLE `users` ADD UNIQUE `username` (`username`, `mail_host`);
-
-ALTER TABLE `contacts` MODIFY `email` varchar(255) NOT NULL;
-
-TRUNCATE TABLE `messages`;
-
--- Updates from version 0.5.1
--- Updates from version 0.5.2
--- Updates from version 0.5.3
--- Updates from version 0.5.4
-
-ALTER TABLE `contacts` ADD `words` TEXT NULL AFTER `vcard`;
-ALTER TABLE `contacts` CHANGE `vcard` `vcard` LONGTEXT /*!40101 CHARACTER SET utf8 */ NULL DEFAULT NULL;
-ALTER TABLE `contactgroupmembers` ADD INDEX `contactgroupmembers_contact_index` (`contact_id`);
-
-TRUNCATE TABLE `messages`;
-TRUNCATE TABLE `cache`;
-
--- Updates from version 0.6
-
-/*!40014 SET FOREIGN_KEY_CHECKS=0 */;
-
-ALTER TABLE `users` CHANGE `alias` `alias` varchar(128) BINARY NOT NULL;
-ALTER TABLE `users` CHANGE `username` `username` varchar(128) BINARY NOT NULL;
-
-CREATE TABLE `dictionary` (
- `user_id` int(10) UNSIGNED DEFAULT NULL,
- `language` varchar(5) NOT NULL,
- `data` longtext NOT NULL,
- CONSTRAINT `user_id_fk_dictionary` FOREIGN KEY (`user_id`)
- REFERENCES `users`(`user_id`) ON DELETE CASCADE ON UPDATE CASCADE,
- UNIQUE `uniqueness` (`user_id`, `language`)
-) /*!40000 ENGINE=INNODB */ /*!40101 CHARACTER SET utf8 COLLATE utf8_general_ci */;
-
-CREATE TABLE `searches` (
- `search_id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
- `user_id` int(10) UNSIGNED NOT NULL DEFAULT '0',
- `type` int(3) NOT NULL DEFAULT '0',
- `name` varchar(128) NOT NULL,
- `data` text,
- PRIMARY KEY(`search_id`),
- CONSTRAINT `user_id_fk_searches` FOREIGN KEY (`user_id`)
- REFERENCES `users`(`user_id`) ON DELETE CASCADE ON UPDATE CASCADE,
- UNIQUE `uniqueness` (`user_id`, `type`, `name`)
-) /*!40000 ENGINE=INNODB */ /*!40101 CHARACTER SET utf8 COLLATE utf8_general_ci */;
-
-DROP TABLE `messages`;
-
-CREATE TABLE `cache_index` (
- `user_id` int(10) UNSIGNED NOT NULL DEFAULT '0',
- `mailbox` varchar(255) BINARY NOT NULL,
- `changed` datetime NOT NULL DEFAULT '1000-01-01 00:00:00',
- `valid` tinyint(1) NOT NULL DEFAULT '0',
- `data` longtext NOT NULL,
- CONSTRAINT `user_id_fk_cache_index` FOREIGN KEY (`user_id`)
- REFERENCES `users`(`user_id`) ON DELETE CASCADE ON UPDATE CASCADE,
- INDEX `changed_index` (`changed`),
- PRIMARY KEY (`user_id`, `mailbox`)
-) /*!40000 ENGINE=INNODB */ /*!40101 CHARACTER SET utf8 COLLATE utf8_general_ci */;
-
-CREATE TABLE `cache_thread` (
- `user_id` int(10) UNSIGNED NOT NULL DEFAULT '0',
- `mailbox` varchar(255) BINARY NOT NULL,
- `changed` datetime NOT NULL DEFAULT '1000-01-01 00:00:00',
- `data` longtext NOT NULL,
- CONSTRAINT `user_id_fk_cache_thread` FOREIGN KEY (`user_id`)
- REFERENCES `users`(`user_id`) ON DELETE CASCADE ON UPDATE CASCADE,
- INDEX `changed_index` (`changed`),
- PRIMARY KEY (`user_id`, `mailbox`)
-) /*!40000 ENGINE=INNODB */ /*!40101 CHARACTER SET utf8 COLLATE utf8_general_ci */;
-
-CREATE TABLE `cache_messages` (
- `user_id` int(10) UNSIGNED NOT NULL DEFAULT '0',
- `mailbox` varchar(255) BINARY NOT NULL,
- `uid` int(11) UNSIGNED NOT NULL DEFAULT '0',
- `changed` datetime NOT NULL DEFAULT '1000-01-01 00:00:00',
- `data` longtext NOT NULL,
- `flags` int(11) NOT NULL DEFAULT '0',
- CONSTRAINT `user_id_fk_cache_messages` FOREIGN KEY (`user_id`)
- REFERENCES `users`(`user_id`) ON DELETE CASCADE ON UPDATE CASCADE,
- INDEX `changed_index` (`changed`),
- PRIMARY KEY (`user_id`, `mailbox`, `uid`)
-) /*!40000 ENGINE=INNODB */ /*!40101 CHARACTER SET utf8 COLLATE utf8_general_ci */;
-
-/*!40014 SET FOREIGN_KEY_CHECKS=1 */;
-
--- Updates from version 0.7-beta
-
-ALTER TABLE `session` CHANGE `sess_id` `sess_id` varchar(128) NOT NULL;
-
--- Updates from version 0.7
-
-/*!40014 SET FOREIGN_KEY_CHECKS=0 */;
-
-ALTER TABLE `contacts` DROP FOREIGN KEY `user_id_fk_contacts`;
-ALTER TABLE `contacts` DROP INDEX `user_contacts_index`;
-ALTER TABLE `contacts` MODIFY `email` text NOT NULL;
-ALTER TABLE `contacts` ADD INDEX `user_contacts_index` (`user_id`,`del`);
-ALTER TABLE `contacts` ADD CONSTRAINT `user_id_fk_contacts` FOREIGN KEY (`user_id`)
- REFERENCES `users`(`user_id`) ON DELETE CASCADE ON UPDATE CASCADE;
-
-ALTER TABLE `cache` ALTER `user_id` DROP DEFAULT;
-ALTER TABLE `cache_index` ALTER `user_id` DROP DEFAULT;
-ALTER TABLE `cache_thread` ALTER `user_id` DROP DEFAULT;
-ALTER TABLE `cache_messages` ALTER `user_id` DROP DEFAULT;
-ALTER TABLE `contacts` ALTER `user_id` DROP DEFAULT;
-ALTER TABLE `contactgroups` ALTER `user_id` DROP DEFAULT;
-ALTER TABLE `contactgroupmembers` ALTER `contact_id` DROP DEFAULT;
-ALTER TABLE `identities` ALTER `user_id` DROP DEFAULT;
-ALTER TABLE `searches` ALTER `user_id` DROP DEFAULT;
-
-/*!40014 SET FOREIGN_KEY_CHECKS=1 */;
-
--- Updates from version 0.8
-
-ALTER TABLE `cache` DROP COLUMN `cache_id`;
-ALTER TABLE `users` DROP COLUMN `alias`;
-ALTER TABLE `identities` ADD INDEX `email_identities_index` (`email`, `del`);
diff --git a/SQL/mysql/2008030300.sql b/SQL/mysql/2008030300.sql
new file mode 100644
index 000000000..9a3d04847
--- /dev/null
+++ b/SQL/mysql/2008030300.sql
@@ -0,0 +1,16 @@
+-- Updates from version 0.1-stable
+
+TRUNCATE TABLE `messages`;
+
+ALTER TABLE `messages`
+ DROP INDEX `idx`,
+ DROP INDEX `uid`;
+
+ALTER TABLE `cache`
+ DROP INDEX `cache_key`,
+ DROP INDEX `session_id`,
+ ADD INDEX `user_cache_index` (`user_id`,`cache_key`);
+
+ALTER TABLE `users`
+ ADD INDEX `username_index` (`username`),
+ ADD INDEX `alias_index` (`alias`);
diff --git a/SQL/mysql/2008040500.sql b/SQL/mysql/2008040500.sql
new file mode 100644
index 000000000..f538f6378
--- /dev/null
+++ b/SQL/mysql/2008040500.sql
@@ -0,0 +1,9 @@
+-- Updates from version 0.1.1
+
+ALTER TABLE `identities`
+ MODIFY `signature` text,
+ MODIFY `bcc` varchar(128) NOT NULL DEFAULT '',
+ MODIFY `reply-to` varchar(128) NOT NULL DEFAULT '',
+ MODIFY `organization` varchar(128) NOT NULL DEFAULT '',
+ MODIFY `name` varchar(128) NOT NULL,
+ MODIFY `email` varchar(128) NOT NULL;
diff --git a/SQL/mysql/2008060900.sql b/SQL/mysql/2008060900.sql
new file mode 100644
index 000000000..9f8de0a54
--- /dev/null
+++ b/SQL/mysql/2008060900.sql
@@ -0,0 +1,4 @@
+-- Updates from version 0.2-alpha
+
+ALTER TABLE `messages`
+ ADD INDEX `created_index` (`created`);
diff --git a/SQL/mysql/2008092100.sql b/SQL/mysql/2008092100.sql
new file mode 100644
index 000000000..3989c75f2
--- /dev/null
+++ b/SQL/mysql/2008092100.sql
@@ -0,0 +1,20 @@
+-- Updates from version 0.2-beta (InnoDB required)
+
+ALTER TABLE `cache`
+ DROP `session_id`;
+
+ALTER TABLE `session`
+ ADD INDEX `changed_index` (`changed`);
+
+ALTER TABLE `cache`
+ ADD INDEX `created_index` (`created`);
+
+ALTER TABLE `users`
+ CHANGE `language` `language` varchar(5);
+
+ALTER TABLE `cache` ENGINE=InnoDB;
+ALTER TABLE `session` ENGINE=InnoDB;
+ALTER TABLE `messages` ENGINE=InnoDB;
+ALTER TABLE `users` ENGINE=InnoDB;
+ALTER TABLE `contacts` ENGINE=InnoDB;
+ALTER TABLE `identities` ENGINE=InnoDB;
diff --git a/SQL/mysql/2009090400.sql b/SQL/mysql/2009090400.sql
new file mode 100644
index 000000000..fd31bed5b
--- /dev/null
+++ b/SQL/mysql/2009090400.sql
@@ -0,0 +1,12 @@
+-- Updates from version 0.3-stable
+
+TRUNCATE `messages`;
+
+ALTER TABLE `messages`
+ ADD INDEX `index_index` (`user_id`, `cache_key`, `idx`);
+
+ALTER TABLE `session`
+ CHANGE `vars` `vars` MEDIUMTEXT NOT NULL;
+
+ALTER TABLE `contacts`
+ ADD INDEX `user_contacts_index` (`user_id`,`email`);
diff --git a/SQL/mysql/2009103100.sql b/SQL/mysql/2009103100.sql
new file mode 100644
index 000000000..aafbe238f
--- /dev/null
+++ b/SQL/mysql/2009103100.sql
@@ -0,0 +1,52 @@
+-- Updates from version 0.3.1
+-- WARNING: Make sure that all tables are using InnoDB engine!!!
+-- If not, use: ALTER TABLE xxx ENGINE=InnoDB;
+
+/* MySQL bug workaround: http://bugs.mysql.com/bug.php?id=46293 */
+/*!40014 SET FOREIGN_KEY_CHECKS=0 */;
+
+ALTER TABLE `messages` DROP FOREIGN KEY `user_id_fk_messages`;
+ALTER TABLE `cache` DROP FOREIGN KEY `user_id_fk_cache`;
+ALTER TABLE `contacts` DROP FOREIGN KEY `user_id_fk_contacts`;
+ALTER TABLE `identities` DROP FOREIGN KEY `user_id_fk_identities`;
+
+ALTER TABLE `messages` ADD CONSTRAINT `user_id_fk_messages` FOREIGN KEY (`user_id`)
+ REFERENCES `users`(`user_id`) ON DELETE CASCADE ON UPDATE CASCADE;
+ALTER TABLE `cache` ADD CONSTRAINT `user_id_fk_cache` FOREIGN KEY (`user_id`)
+ REFERENCES `users`(`user_id`) ON DELETE CASCADE ON UPDATE CASCADE;
+ALTER TABLE `contacts` ADD CONSTRAINT `user_id_fk_contacts` FOREIGN KEY (`user_id`)
+ REFERENCES `users`(`user_id`) ON DELETE CASCADE ON UPDATE CASCADE;
+ALTER TABLE `identities` ADD CONSTRAINT `user_id_fk_identities` FOREIGN KEY (`user_id`)
+ REFERENCES `users`(`user_id`) ON DELETE CASCADE ON UPDATE CASCADE;
+
+ALTER TABLE `contacts` ALTER `name` SET DEFAULT '';
+ALTER TABLE `contacts` ALTER `firstname` SET DEFAULT '';
+ALTER TABLE `contacts` ALTER `surname` SET DEFAULT '';
+
+ALTER TABLE `identities` ADD INDEX `user_identities_index` (`user_id`, `del`);
+ALTER TABLE `identities` ADD `changed` datetime NOT NULL DEFAULT '1000-01-01 00:00:00' AFTER `user_id`;
+
+CREATE TABLE `contactgroups` (
+ `contactgroup_id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
+ `user_id` int(10) UNSIGNED NOT NULL DEFAULT '0',
+ `changed` datetime NOT NULL DEFAULT '1000-01-01 00:00:00',
+ `del` tinyint(1) NOT NULL DEFAULT '0',
+ `name` varchar(128) NOT NULL DEFAULT '',
+ PRIMARY KEY(`contactgroup_id`),
+ CONSTRAINT `user_id_fk_contactgroups` FOREIGN KEY (`user_id`)
+ REFERENCES `users`(`user_id`) ON DELETE CASCADE ON UPDATE CASCADE,
+ INDEX `contactgroups_user_index` (`user_id`,`del`)
+) /*!40000 ENGINE=INNODB */ /*!40101 CHARACTER SET utf8 COLLATE utf8_general_ci */;
+
+CREATE TABLE `contactgroupmembers` (
+ `contactgroup_id` int(10) UNSIGNED NOT NULL,
+ `contact_id` int(10) UNSIGNED NOT NULL DEFAULT '0',
+ `created` datetime NOT NULL DEFAULT '1000-01-01 00:00:00',
+ PRIMARY KEY (`contactgroup_id`, `contact_id`),
+ CONSTRAINT `contactgroup_id_fk_contactgroups` FOREIGN KEY (`contactgroup_id`)
+ REFERENCES `contactgroups`(`contactgroup_id`) ON DELETE CASCADE ON UPDATE CASCADE,
+ CONSTRAINT `contact_id_fk_contacts` FOREIGN KEY (`contact_id`)
+ REFERENCES `contacts`(`contact_id`) ON DELETE CASCADE ON UPDATE CASCADE
+) /*!40000 ENGINE=INNODB */;
+
+/*!40014 SET FOREIGN_KEY_CHECKS=1 */;
diff --git a/SQL/mysql/2010042300.sql b/SQL/mysql/2010042300.sql
new file mode 100644
index 000000000..8b90af45e
--- /dev/null
+++ b/SQL/mysql/2010042300.sql
@@ -0,0 +1,4 @@
+-- Updates from version 0.4-beta
+
+ALTER TABLE `users` CHANGE `last_login` `last_login` datetime DEFAULT NULL;
+UPDATE `users` SET `last_login` = NULL WHERE `last_login` = '1000-01-01 00:00:00';
diff --git a/SQL/mysql/2010100600.sql b/SQL/mysql/2010100600.sql
new file mode 100644
index 000000000..ca0fec858
--- /dev/null
+++ b/SQL/mysql/2010100600.sql
@@ -0,0 +1,8 @@
+-- Updates from version 0.4.2
+
+ALTER TABLE `users` DROP INDEX `username_index`;
+ALTER TABLE `users` ADD UNIQUE `username` (`username`, `mail_host`);
+
+ALTER TABLE `contacts` MODIFY `email` varchar(255) NOT NULL;
+
+TRUNCATE TABLE `messages`;
diff --git a/SQL/mysql/2011011200.sql b/SQL/mysql/2011011200.sql
new file mode 100644
index 000000000..6597034c2
--- /dev/null
+++ b/SQL/mysql/2011011200.sql
@@ -0,0 +1,8 @@
+-- Updates from version 0.5.x
+
+ALTER TABLE `contacts` ADD `words` TEXT NULL AFTER `vcard`;
+ALTER TABLE `contacts` CHANGE `vcard` `vcard` LONGTEXT /*!40101 CHARACTER SET utf8 */ NULL DEFAULT NULL;
+ALTER TABLE `contactgroupmembers` ADD INDEX `contactgroupmembers_contact_index` (`contact_id`);
+
+TRUNCATE TABLE `messages`;
+TRUNCATE TABLE `cache`;
diff --git a/SQL/mysql/2011092800.sql b/SQL/mysql/2011092800.sql
new file mode 100644
index 000000000..6b7cbe1f3
--- /dev/null
+++ b/SQL/mysql/2011092800.sql
@@ -0,0 +1,67 @@
+-- Updates from version 0.6
+
+/*!40014 SET FOREIGN_KEY_CHECKS=0 */;
+
+ALTER TABLE `users` CHANGE `alias` `alias` varchar(128) BINARY NOT NULL;
+ALTER TABLE `users` CHANGE `username` `username` varchar(128) BINARY NOT NULL;
+
+CREATE TABLE `dictionary` (
+ `user_id` int(10) UNSIGNED DEFAULT NULL,
+ `language` varchar(5) NOT NULL,
+ `data` longtext NOT NULL,
+ CONSTRAINT `user_id_fk_dictionary` FOREIGN KEY (`user_id`)
+ REFERENCES `users`(`user_id`) ON DELETE CASCADE ON UPDATE CASCADE,
+ UNIQUE `uniqueness` (`user_id`, `language`)
+) /*!40000 ENGINE=INNODB */ /*!40101 CHARACTER SET utf8 COLLATE utf8_general_ci */;
+
+CREATE TABLE `searches` (
+ `search_id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
+ `user_id` int(10) UNSIGNED NOT NULL DEFAULT '0',
+ `type` int(3) NOT NULL DEFAULT '0',
+ `name` varchar(128) NOT NULL,
+ `data` text,
+ PRIMARY KEY(`search_id`),
+ CONSTRAINT `user_id_fk_searches` FOREIGN KEY (`user_id`)
+ REFERENCES `users`(`user_id`) ON DELETE CASCADE ON UPDATE CASCADE,
+ UNIQUE `uniqueness` (`user_id`, `type`, `name`)
+) /*!40000 ENGINE=INNODB */ /*!40101 CHARACTER SET utf8 COLLATE utf8_general_ci */;
+
+DROP TABLE `messages`;
+
+CREATE TABLE `cache_index` (
+ `user_id` int(10) UNSIGNED NOT NULL DEFAULT '0',
+ `mailbox` varchar(255) BINARY NOT NULL,
+ `changed` datetime NOT NULL DEFAULT '1000-01-01 00:00:00',
+ `valid` tinyint(1) NOT NULL DEFAULT '0',
+ `data` longtext NOT NULL,
+ CONSTRAINT `user_id_fk_cache_index` FOREIGN KEY (`user_id`)
+ REFERENCES `users`(`user_id`) ON DELETE CASCADE ON UPDATE CASCADE,
+ INDEX `changed_index` (`changed`),
+ PRIMARY KEY (`user_id`, `mailbox`)
+) /*!40000 ENGINE=INNODB */ /*!40101 CHARACTER SET utf8 COLLATE utf8_general_ci */;
+
+CREATE TABLE `cache_thread` (
+ `user_id` int(10) UNSIGNED NOT NULL DEFAULT '0',
+ `mailbox` varchar(255) BINARY NOT NULL,
+ `changed` datetime NOT NULL DEFAULT '1000-01-01 00:00:00',
+ `data` longtext NOT NULL,
+ CONSTRAINT `user_id_fk_cache_thread` FOREIGN KEY (`user_id`)
+ REFERENCES `users`(`user_id`) ON DELETE CASCADE ON UPDATE CASCADE,
+ INDEX `changed_index` (`changed`),
+ PRIMARY KEY (`user_id`, `mailbox`)
+) /*!40000 ENGINE=INNODB */ /*!40101 CHARACTER SET utf8 COLLATE utf8_general_ci */;
+
+CREATE TABLE `cache_messages` (
+ `user_id` int(10) UNSIGNED NOT NULL DEFAULT '0',
+ `mailbox` varchar(255) BINARY NOT NULL,
+ `uid` int(11) UNSIGNED NOT NULL DEFAULT '0',
+ `changed` datetime NOT NULL DEFAULT '1000-01-01 00:00:00',
+ `data` longtext NOT NULL,
+ `flags` int(11) NOT NULL DEFAULT '0',
+ CONSTRAINT `user_id_fk_cache_messages` FOREIGN KEY (`user_id`)
+ REFERENCES `users`(`user_id`) ON DELETE CASCADE ON UPDATE CASCADE,
+ INDEX `changed_index` (`changed`),
+ PRIMARY KEY (`user_id`, `mailbox`, `uid`)
+) /*!40000 ENGINE=INNODB */ /*!40101 CHARACTER SET utf8 COLLATE utf8_general_ci */;
+
+/*!40014 SET FOREIGN_KEY_CHECKS=1 */;
diff --git a/SQL/mysql/2011111600.sql b/SQL/mysql/2011111600.sql
new file mode 100644
index 000000000..6f53daa9c
--- /dev/null
+++ b/SQL/mysql/2011111600.sql
@@ -0,0 +1,3 @@
+-- Updates from version 0.7-beta
+
+ALTER TABLE `session` CHANGE `sess_id` `sess_id` varchar(128) NOT NULL;
diff --git a/SQL/mysql/2011121400.sql b/SQL/mysql/2011121400.sql
new file mode 100644
index 000000000..5aee80601
--- /dev/null
+++ b/SQL/mysql/2011121400.sql
@@ -0,0 +1,22 @@
+-- Updates from version 0.7
+
+/*!40014 SET FOREIGN_KEY_CHECKS=0 */;
+
+ALTER TABLE `contacts` DROP FOREIGN KEY `user_id_fk_contacts`;
+ALTER TABLE `contacts` DROP INDEX `user_contacts_index`;
+ALTER TABLE `contacts` MODIFY `email` text NOT NULL;
+ALTER TABLE `contacts` ADD INDEX `user_contacts_index` (`user_id`,`del`);
+ALTER TABLE `contacts` ADD CONSTRAINT `user_id_fk_contacts` FOREIGN KEY (`user_id`)
+ REFERENCES `users`(`user_id`) ON DELETE CASCADE ON UPDATE CASCADE;
+
+ALTER TABLE `cache` ALTER `user_id` DROP DEFAULT;
+ALTER TABLE `cache_index` ALTER `user_id` DROP DEFAULT;
+ALTER TABLE `cache_thread` ALTER `user_id` DROP DEFAULT;
+ALTER TABLE `cache_messages` ALTER `user_id` DROP DEFAULT;
+ALTER TABLE `contacts` ALTER `user_id` DROP DEFAULT;
+ALTER TABLE `contactgroups` ALTER `user_id` DROP DEFAULT;
+ALTER TABLE `contactgroupmembers` ALTER `contact_id` DROP DEFAULT;
+ALTER TABLE `identities` ALTER `user_id` DROP DEFAULT;
+ALTER TABLE `searches` ALTER `user_id` DROP DEFAULT;
+
+/*!40014 SET FOREIGN_KEY_CHECKS=1 */;
diff --git a/SQL/mysql/2012080700.sql b/SQL/mysql/2012080700.sql
new file mode 100644
index 000000000..789b7122c
--- /dev/null
+++ b/SQL/mysql/2012080700.sql
@@ -0,0 +1,5 @@
+-- Updates from version 0.8
+
+ALTER TABLE `cache` DROP COLUMN `cache_id`;
+ALTER TABLE `users` DROP COLUMN `alias`;
+ALTER TABLE `identities` ADD INDEX `email_identities_index` (`email`, `del`);
diff --git a/SQL/mysql/2013011000.sql b/SQL/mysql/2013011000.sql
new file mode 100644
index 000000000..f0b5e6a03
--- /dev/null
+++ b/SQL/mysql/2013011000.sql
@@ -0,0 +1,7 @@
+-- Upgrade from 0.9-beta
+
+CREATE TABLE `system` (
+ `name` varchar(64) NOT NULL,
+ `value` mediumtext,
+ PRIMARY KEY(`name`)
+) /*!40000 ENGINE=INNODB */ /*!40101 CHARACTER SET utf8 COLLATE utf8_general_ci */;
diff --git a/SQL/postgres.initial.sql b/SQL/postgres.initial.sql
index f7b2d96d9..d7dc08f90 100644
--- a/SQL/postgres.initial.sql
+++ b/SQL/postgres.initial.sql
@@ -1,264 +1,277 @@
-- Roundcube Webmail initial database structure
--
-- Sequence "user_ids"
-- Name: user_ids; Type: SEQUENCE; Schema: public; Owner: postgres
--
CREATE SEQUENCE user_ids
INCREMENT BY 1
NO MAXVALUE
NO MINVALUE
CACHE 1;
--
-- Table "users"
-- Name: users; Type: TABLE; Schema: public; Owner: postgres
--
CREATE TABLE users (
user_id integer DEFAULT nextval('user_ids'::text) PRIMARY KEY,
username varchar(128) DEFAULT '' NOT NULL,
mail_host varchar(128) DEFAULT '' NOT NULL,
created timestamp with time zone DEFAULT now() NOT NULL,
last_login timestamp with time zone DEFAULT NULL,
"language" varchar(5),
preferences text DEFAULT ''::text NOT NULL,
CONSTRAINT users_username_key UNIQUE (username, mail_host)
);
--
-- Table "session"
-- Name: session; Type: TABLE; Schema: public; Owner: postgres
--
CREATE TABLE "session" (
sess_id varchar(128) DEFAULT '' PRIMARY KEY,
created timestamp with time zone DEFAULT now() NOT NULL,
changed timestamp with time zone DEFAULT now() NOT NULL,
ip varchar(41) NOT NULL,
vars text NOT NULL
);
CREATE INDEX session_changed_idx ON session (changed);
--
-- Sequence "identity_ids"
-- Name: identity_ids; Type: SEQUENCE; Schema: public; Owner: postgres
--
CREATE SEQUENCE identity_ids
START WITH 1
INCREMENT BY 1
NO MAXVALUE
NO MINVALUE
CACHE 1;
--
-- Table "identities"
-- Name: identities; Type: TABLE; Schema: public; Owner: postgres
--
CREATE TABLE identities (
identity_id integer DEFAULT nextval('identity_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 DEFAULT 0 NOT NULL,
standard smallint DEFAULT 0 NOT NULL,
name varchar(128) NOT NULL,
organization varchar(128),
email varchar(128) NOT NULL,
"reply-to" varchar(128),
bcc varchar(128),
signature text,
html_signature integer DEFAULT 0 NOT NULL
);
CREATE INDEX identities_user_id_idx ON identities (user_id, del);
CREATE INDEX identities_email_idx ON identities (email, del);
--
-- Sequence "contact_ids"
-- Name: contact_ids; Type: SEQUENCE; Schema: public; Owner: postgres
--
CREATE SEQUENCE contact_ids
START WITH 1
INCREMENT BY 1
NO MAXVALUE
NO MINVALUE
CACHE 1;
--
-- Table "contacts"
-- Name: contacts; Type: TABLE; Schema: public; Owner: postgres
--
CREATE TABLE contacts (
contact_id integer DEFAULT nextval('contact_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 DEFAULT 0 NOT NULL,
name varchar(128) DEFAULT '' NOT NULL,
email text DEFAULT '' NOT NULL,
firstname varchar(128) DEFAULT '' NOT NULL,
surname varchar(128) DEFAULT '' NOT NULL,
vcard text,
words text
);
CREATE INDEX contacts_user_id_idx ON contacts (user_id, del);
--
-- Sequence "contactgroups_ids"
-- Name: contactgroups_ids; Type: SEQUENCE; Schema: public; Owner: postgres
--
CREATE SEQUENCE contactgroups_ids
INCREMENT BY 1
NO MAXVALUE
NO MINVALUE
CACHE 1;
--
-- Table "contactgroups"
-- Name: contactgroups; Type: TABLE; Schema: public; Owner: postgres
--
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);
--
-- Table "contactgroupmembers"
-- Name: contactgroupmembers; Type: TABLE; Schema: public; Owner: postgres
--
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)
);
CREATE INDEX contactgroupmembers_contact_id_idx ON contactgroupmembers (contact_id);
--
-- Table "cache"
-- Name: cache; Type: TABLE; Schema: public; Owner: postgres
--
CREATE TABLE "cache" (
user_id integer NOT NULL
REFERENCES users (user_id) ON DELETE CASCADE ON UPDATE CASCADE,
cache_key varchar(128) DEFAULT '' NOT NULL,
created timestamp with time zone DEFAULT now() NOT NULL,
data text NOT NULL
);
CREATE INDEX cache_user_id_idx ON "cache" (user_id, cache_key);
CREATE INDEX cache_created_idx ON "cache" (created);
--
-- Table "cache_index"
-- Name: cache_index; Type: TABLE; Schema: public; Owner: postgres
--
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);
--
-- Table "cache_thread"
-- Name: cache_thread; Type: TABLE; Schema: public; Owner: postgres
--
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);
--
-- Table "cache_messages"
-- Name: cache_messages; Type: TABLE; Schema: public; Owner: postgres
--
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);
--
-- Table "dictionary"
-- Name: dictionary; Type: TABLE; Schema: public; Owner: postgres
--
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")
);
--
-- Sequence "searches_ids"
-- Name: searches_ids; Type: SEQUENCE; Schema: public; Owner: postgres
--
CREATE SEQUENCE search_ids
INCREMENT BY 1
NO MAXVALUE
NO MINVALUE
CACHE 1;
--
-- Table "searches"
-- Name: searches; Type: TABLE; Schema: public; Owner: postgres
--
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)
);
+
+
+--
+-- Table "system"
+-- Name: system; Type: TABLE; Schema: public; Owner: postgres
+--
+
+CREATE TABLE "system" (
+ name varchar(64) NOT NULL PRIMARY KEY,
+ value text
+);
+
+INSERT INTO system (name, value) VALUES ('roundcube-version', '2013011000.sql');
diff --git a/SQL/postgres.update.sql b/SQL/postgres.update.sql
deleted file mode 100644
index 11ab93bfc..000000000
--- a/SQL/postgres.update.sql
+++ /dev/null
@@ -1,185 +0,0 @@
--- Roundcube Webmail update script for Postgres databases
--- Updates from version 0.1-stable to 0.1.1
-
-CREATE INDEX cache_user_id_idx ON cache (user_id, cache_key);
-CREATE INDEX contacts_user_id_idx ON contacts (user_id);
-CREATE INDEX identities_user_id_idx ON identities (user_id);
-
-CREATE INDEX users_username_id_idx ON users (username);
-CREATE INDEX users_alias_id_idx ON users (alias);
-
--- added ON DELETE/UPDATE actions
-ALTER TABLE messages DROP CONSTRAINT messages_user_id_fkey;
-ALTER TABLE messages ADD FOREIGN KEY (user_id) REFERENCES users(user_id) ON DELETE CASCADE ON UPDATE CASCADE;
-ALTER TABLE identities DROP CONSTRAINT identities_user_id_fkey;
-ALTER TABLE identities ADD FOREIGN KEY (user_id) REFERENCES users(user_id) ON DELETE CASCADE ON UPDATE CASCADE;
-ALTER TABLE contacts DROP CONSTRAINT contacts_user_id_fkey;
-ALTER TABLE contacts ADD FOREIGN KEY (user_id) REFERENCES users(user_id) ON DELETE CASCADE ON UPDATE CASCADE;
-ALTER TABLE cache DROP CONSTRAINT cache_user_id_fkey;
-ALTER TABLE cache ADD FOREIGN KEY (user_id) REFERENCES users(user_id) ON DELETE CASCADE ON UPDATE CASCADE;
-
--- Updates from version 0.2-alpha
-
-CREATE INDEX messages_created_idx ON messages (created);
-
--- Updates from version 0.2-beta
-
-ALTER TABLE cache DROP session_id;
-
-CREATE INDEX session_changed_idx ON session (changed);
-CREATE INDEX cache_created_idx ON "cache" (created);
-
-ALTER TABLE users ALTER "language" DROP NOT NULL;
-ALTER TABLE users ALTER "language" DROP DEFAULT;
-
-ALTER TABLE identities ALTER del TYPE smallint;
-ALTER TABLE identities ALTER standard TYPE smallint;
-ALTER TABLE contacts ALTER del TYPE smallint;
-ALTER TABLE messages ALTER del TYPE smallint;
-
--- Updates from version 0.3-stable
-
-TRUNCATE messages;
-CREATE INDEX messages_index_idx ON messages (user_id, cache_key, idx);
-DROP INDEX contacts_user_id_idx;
-CREATE INDEX contacts_user_id_idx ON contacts (user_id, email);
-
--- Updates from version 0.3.1
-
-DROP INDEX identities_user_id_idx;
-CREATE INDEX identities_user_id_idx ON identities (user_id, del);
-
-ALTER TABLE identities ADD changed timestamp with time zone DEFAULT now() NOT NULL;
-
-CREATE SEQUENCE contactgroups_ids
- INCREMENT BY 1
- NO MAXVALUE
- NO MINVALUE
- CACHE 1;
-
-CREATE TABLE contactgroups (
- contactgroup_id integer DEFAULT nextval('contactgroups_ids'::text) PRIMARY KEY,
- user_id integer NOT NULL
- REFERENCES users(user_id) ON DELETE CASCADE ON UPDATE CASCADE,
- changed timestamp with time zone DEFAULT now() NOT NULL,
- del smallint NOT NULL DEFAULT 0,
- name varchar(128) NOT NULL DEFAULT ''
-);
-
-CREATE INDEX contactgroups_user_id_idx ON contactgroups (user_id, del);
-
-CREATE TABLE contactgroupmembers (
- contactgroup_id integer NOT NULL
- REFERENCES contactgroups(contactgroup_id) ON DELETE CASCADE ON UPDATE CASCADE,
- contact_id integer NOT NULL
- REFERENCES contacts(contact_id) ON DELETE CASCADE ON UPDATE CASCADE,
- created timestamp with time zone DEFAULT now() NOT NULL,
- PRIMARY KEY (contactgroup_id, contact_id)
-);
-
--- Updates from version 0.4-beta
-
-ALTER TABLE users ALTER last_login DROP NOT NULL;
-ALTER TABLE users ALTER last_login SET DEFAULT NULL;
-
--- Updates from version 0.4.2
-
-DROP INDEX users_username_id_idx;
-ALTER TABLE users ADD CONSTRAINT users_username_key UNIQUE (username, mail_host);
-ALTER TABLE contacts ALTER email TYPE varchar(255);
-
-TRUNCATE messages;
-
--- Updates from version 0.5.1
--- Updates from version 0.5.2
--- Updates from version 0.5.3
--- Updates from version 0.5.4
-
-ALTER TABLE contacts ADD words TEXT NULL;
-CREATE INDEX contactgroupmembers_contact_id_idx ON contactgroupmembers (contact_id);
-
-TRUNCATE messages;
-TRUNCATE cache;
-
--- Updates from version 0.6
-
-CREATE TABLE dictionary (
- user_id integer DEFAULT NULL
- REFERENCES users (user_id) ON DELETE CASCADE ON UPDATE CASCADE,
- "language" varchar(5) NOT NULL,
- data text NOT NULL,
- CONSTRAINT dictionary_user_id_language_key UNIQUE (user_id, "language")
-);
-
-CREATE SEQUENCE search_ids
- INCREMENT BY 1
- NO MAXVALUE
- NO MINVALUE
- CACHE 1;
-
-CREATE TABLE searches (
- search_id integer DEFAULT nextval('search_ids'::text) PRIMARY KEY,
- user_id integer NOT NULL
- REFERENCES users (user_id) ON DELETE CASCADE ON UPDATE CASCADE,
- "type" smallint DEFAULT 0 NOT NULL,
- name varchar(128) NOT NULL,
- data text NOT NULL,
- CONSTRAINT searches_user_id_key UNIQUE (user_id, "type", name)
-);
-
-DROP SEQUENCE message_ids;
-DROP TABLE messages;
-
-CREATE TABLE cache_index (
- user_id integer NOT NULL
- REFERENCES users (user_id) ON DELETE CASCADE ON UPDATE CASCADE,
- mailbox varchar(255) NOT NULL,
- changed timestamp with time zone DEFAULT now() NOT NULL,
- valid smallint NOT NULL DEFAULT 0,
- data text NOT NULL,
- PRIMARY KEY (user_id, mailbox)
-);
-
-CREATE INDEX cache_index_changed_idx ON cache_index (changed);
-
-CREATE TABLE cache_thread (
- user_id integer NOT NULL
- REFERENCES users (user_id) ON DELETE CASCADE ON UPDATE CASCADE,
- mailbox varchar(255) NOT NULL,
- changed timestamp with time zone DEFAULT now() NOT NULL,
- data text NOT NULL,
- PRIMARY KEY (user_id, mailbox)
-);
-
-CREATE INDEX cache_thread_changed_idx ON cache_thread (changed);
-
-CREATE TABLE cache_messages (
- user_id integer NOT NULL
- REFERENCES users (user_id) ON DELETE CASCADE ON UPDATE CASCADE,
- mailbox varchar(255) NOT NULL,
- uid integer NOT NULL,
- changed timestamp with time zone DEFAULT now() NOT NULL,
- data text NOT NULL,
- flags integer NOT NULL DEFAULT 0,
- PRIMARY KEY (user_id, mailbox, uid)
-);
-
-CREATE INDEX cache_messages_changed_idx ON cache_messages (changed);
-
--- Updates from version 0.7-beta
-
-ALTER TABLE "session" ALTER sess_id TYPE varchar(128);
-
--- Updates from version 0.7
-
-DROP INDEX contacts_user_id_idx;
-CREATE INDEX contacts_user_id_idx ON contacts USING btree (user_id, del);
-ALTER TABLE contacts ALTER email TYPE text;
-
--- Updates from version 0.8
-
-ALTER TABLE cache DROP COLUMN cache_id;
-DROP SEQUENCE cache_ids;
-
-ALTER TABLE users DROP COLUMN alias;
-CREATE INDEX identities_email_idx ON identities (email, del);
diff --git a/SQL/postgres/2008030300.sql b/SQL/postgres/2008030300.sql
new file mode 100644
index 000000000..67a8f158f
--- /dev/null
+++ b/SQL/postgres/2008030300.sql
@@ -0,0 +1,18 @@
+-- Updates from version 0.1-stable to 0.1.1
+
+CREATE INDEX cache_user_id_idx ON cache (user_id, cache_key);
+CREATE INDEX contacts_user_id_idx ON contacts (user_id);
+CREATE INDEX identities_user_id_idx ON identities (user_id);
+
+CREATE INDEX users_username_id_idx ON users (username);
+CREATE INDEX users_alias_id_idx ON users (alias);
+
+-- added ON DELETE/UPDATE actions
+ALTER TABLE messages DROP CONSTRAINT messages_user_id_fkey;
+ALTER TABLE messages ADD FOREIGN KEY (user_id) REFERENCES users(user_id) ON DELETE CASCADE ON UPDATE CASCADE;
+ALTER TABLE identities DROP CONSTRAINT identities_user_id_fkey;
+ALTER TABLE identities ADD FOREIGN KEY (user_id) REFERENCES users(user_id) ON DELETE CASCADE ON UPDATE CASCADE;
+ALTER TABLE contacts DROP CONSTRAINT contacts_user_id_fkey;
+ALTER TABLE contacts ADD FOREIGN KEY (user_id) REFERENCES users(user_id) ON DELETE CASCADE ON UPDATE CASCADE;
+ALTER TABLE cache DROP CONSTRAINT cache_user_id_fkey;
+ALTER TABLE cache ADD FOREIGN KEY (user_id) REFERENCES users(user_id) ON DELETE CASCADE ON UPDATE CASCADE;
diff --git a/SQL/postgres/2008060900.sql b/SQL/postgres/2008060900.sql
new file mode 100644
index 000000000..7c60a4422
--- /dev/null
+++ b/SQL/postgres/2008060900.sql
@@ -0,0 +1,3 @@
+-- Updates from version 0.2-alpha
+
+CREATE INDEX messages_created_idx ON messages (created);
diff --git a/SQL/postgres/2008092100.sql b/SQL/postgres/2008092100.sql
new file mode 100644
index 000000000..6cd19290c
--- /dev/null
+++ b/SQL/postgres/2008092100.sql
@@ -0,0 +1,14 @@
+-- Updates from version 0.2-beta
+
+ALTER TABLE cache DROP session_id;
+
+CREATE INDEX session_changed_idx ON session (changed);
+CREATE INDEX cache_created_idx ON "cache" (created);
+
+ALTER TABLE users ALTER "language" DROP NOT NULL;
+ALTER TABLE users ALTER "language" DROP DEFAULT;
+
+ALTER TABLE identities ALTER del TYPE smallint;
+ALTER TABLE identities ALTER standard TYPE smallint;
+ALTER TABLE contacts ALTER del TYPE smallint;
+ALTER TABLE messages ALTER del TYPE smallint;
diff --git a/SQL/postgres/2009090400.sql b/SQL/postgres/2009090400.sql
new file mode 100644
index 000000000..8eb4949e8
--- /dev/null
+++ b/SQL/postgres/2009090400.sql
@@ -0,0 +1,6 @@
+-- Updates from version 0.3-stable
+
+TRUNCATE messages;
+CREATE INDEX messages_index_idx ON messages (user_id, cache_key, idx);
+DROP INDEX contacts_user_id_idx;
+CREATE INDEX contacts_user_id_idx ON contacts (user_id, email);
diff --git a/SQL/postgres/2009103100.sql b/SQL/postgres/2009103100.sql
new file mode 100644
index 000000000..2350f79f7
--- /dev/null
+++ b/SQL/postgres/2009103100.sql
@@ -0,0 +1,32 @@
+-- Updates from version 0.3.1
+
+DROP INDEX identities_user_id_idx;
+CREATE INDEX identities_user_id_idx ON identities (user_id, del);
+
+ALTER TABLE identities ADD changed timestamp with time zone DEFAULT now() NOT NULL;
+
+CREATE SEQUENCE contactgroups_ids
+ INCREMENT BY 1
+ NO MAXVALUE
+ NO MINVALUE
+ CACHE 1;
+
+CREATE TABLE contactgroups (
+ contactgroup_id integer DEFAULT nextval('contactgroups_ids'::text) PRIMARY KEY,
+ user_id integer NOT NULL
+ REFERENCES users(user_id) ON DELETE CASCADE ON UPDATE CASCADE,
+ changed timestamp with time zone DEFAULT now() NOT NULL,
+ del smallint NOT NULL DEFAULT 0,
+ name varchar(128) NOT NULL DEFAULT ''
+);
+
+CREATE INDEX contactgroups_user_id_idx ON contactgroups (user_id, del);
+
+CREATE TABLE contactgroupmembers (
+ contactgroup_id integer NOT NULL
+ REFERENCES contactgroups(contactgroup_id) ON DELETE CASCADE ON UPDATE CASCADE,
+ contact_id integer NOT NULL
+ REFERENCES contacts(contact_id) ON DELETE CASCADE ON UPDATE CASCADE,
+ created timestamp with time zone DEFAULT now() NOT NULL,
+ PRIMARY KEY (contactgroup_id, contact_id)
+);
diff --git a/SQL/postgres/2010042300.sql b/SQL/postgres/2010042300.sql
new file mode 100644
index 000000000..bc9bd826a
--- /dev/null
+++ b/SQL/postgres/2010042300.sql
@@ -0,0 +1,4 @@
+-- Updates from version 0.4-beta
+
+ALTER TABLE users ALTER last_login DROP NOT NULL;
+ALTER TABLE users ALTER last_login SET DEFAULT NULL;
diff --git a/SQL/postgres/2010100600.sql b/SQL/postgres/2010100600.sql
new file mode 100644
index 000000000..8dfcc12df
--- /dev/null
+++ b/SQL/postgres/2010100600.sql
@@ -0,0 +1,7 @@
+-- Updates from version 0.4.2
+
+DROP INDEX users_username_id_idx;
+ALTER TABLE users ADD CONSTRAINT users_username_key UNIQUE (username, mail_host);
+ALTER TABLE contacts ALTER email TYPE varchar(255);
+
+TRUNCATE messages;
diff --git a/SQL/postgres/2011011200.sql b/SQL/postgres/2011011200.sql
new file mode 100644
index 000000000..db468d8e8
--- /dev/null
+++ b/SQL/postgres/2011011200.sql
@@ -0,0 +1,7 @@
+-- Updates from version 0.5.x
+
+ALTER TABLE contacts ADD words TEXT NULL;
+CREATE INDEX contactgroupmembers_contact_id_idx ON contactgroupmembers (contact_id);
+
+TRUNCATE messages;
+TRUNCATE cache;
diff --git a/SQL/postgres/2011092800.sql b/SQL/postgres/2011092800.sql
new file mode 100644
index 000000000..fac3cd371
--- /dev/null
+++ b/SQL/postgres/2011092800.sql
@@ -0,0 +1,64 @@
+-- Updates from version 0.6
+
+CREATE TABLE dictionary (
+ user_id integer DEFAULT NULL
+ REFERENCES users (user_id) ON DELETE CASCADE ON UPDATE CASCADE,
+ "language" varchar(5) NOT NULL,
+ data text NOT NULL,
+ CONSTRAINT dictionary_user_id_language_key UNIQUE (user_id, "language")
+);
+
+CREATE SEQUENCE search_ids
+ INCREMENT BY 1
+ NO MAXVALUE
+ NO MINVALUE
+ CACHE 1;
+
+CREATE TABLE searches (
+ search_id integer DEFAULT nextval('search_ids'::text) PRIMARY KEY,
+ user_id integer NOT NULL
+ REFERENCES users (user_id) ON DELETE CASCADE ON UPDATE CASCADE,
+ "type" smallint DEFAULT 0 NOT NULL,
+ name varchar(128) NOT NULL,
+ data text NOT NULL,
+ CONSTRAINT searches_user_id_key UNIQUE (user_id, "type", name)
+);
+
+DROP SEQUENCE message_ids;
+DROP TABLE messages;
+
+CREATE TABLE cache_index (
+ user_id integer NOT NULL
+ REFERENCES users (user_id) ON DELETE CASCADE ON UPDATE CASCADE,
+ mailbox varchar(255) NOT NULL,
+ changed timestamp with time zone DEFAULT now() NOT NULL,
+ valid smallint NOT NULL DEFAULT 0,
+ data text NOT NULL,
+ PRIMARY KEY (user_id, mailbox)
+);
+
+CREATE INDEX cache_index_changed_idx ON cache_index (changed);
+
+CREATE TABLE cache_thread (
+ user_id integer NOT NULL
+ REFERENCES users (user_id) ON DELETE CASCADE ON UPDATE CASCADE,
+ mailbox varchar(255) NOT NULL,
+ changed timestamp with time zone DEFAULT now() NOT NULL,
+ data text NOT NULL,
+ PRIMARY KEY (user_id, mailbox)
+);
+
+CREATE INDEX cache_thread_changed_idx ON cache_thread (changed);
+
+CREATE TABLE cache_messages (
+ user_id integer NOT NULL
+ REFERENCES users (user_id) ON DELETE CASCADE ON UPDATE CASCADE,
+ mailbox varchar(255) NOT NULL,
+ uid integer NOT NULL,
+ changed timestamp with time zone DEFAULT now() NOT NULL,
+ data text NOT NULL,
+ flags integer NOT NULL DEFAULT 0,
+ PRIMARY KEY (user_id, mailbox, uid)
+);
+
+CREATE INDEX cache_messages_changed_idx ON cache_messages (changed);
diff --git a/SQL/postgres/2011111600.sql b/SQL/postgres/2011111600.sql
new file mode 100644
index 000000000..a382ba0ba
--- /dev/null
+++ b/SQL/postgres/2011111600.sql
@@ -0,0 +1,3 @@
+-- Updates from version 0.7-beta
+
+ALTER TABLE "session" ALTER sess_id TYPE varchar(128);
diff --git a/SQL/postgres/2011121400.sql b/SQL/postgres/2011121400.sql
new file mode 100644
index 000000000..81a0cb880
--- /dev/null
+++ b/SQL/postgres/2011121400.sql
@@ -0,0 +1,5 @@
+-- Updates from version 0.7
+
+DROP INDEX contacts_user_id_idx;
+CREATE INDEX contacts_user_id_idx ON contacts USING btree (user_id, del);
+ALTER TABLE contacts ALTER email TYPE text;
diff --git a/SQL/postgres/2012080700.sql b/SQL/postgres/2012080700.sql
new file mode 100644
index 000000000..41f60168e
--- /dev/null
+++ b/SQL/postgres/2012080700.sql
@@ -0,0 +1,7 @@
+-- Updates from version 0.8
+
+ALTER TABLE cache DROP COLUMN cache_id;
+DROP SEQUENCE cache_ids;
+
+ALTER TABLE users DROP COLUMN alias;
+CREATE INDEX identities_email_idx ON identities (email, del);
diff --git a/SQL/postgres/2013011000.sql b/SQL/postgres/2013011000.sql
new file mode 100644
index 000000000..a8cf917d0
--- /dev/null
+++ b/SQL/postgres/2013011000.sql
@@ -0,0 +1,4 @@
+CREATE TABLE "system" (
+ name varchar(64) NOT NULL PRIMARY KEY,
+ value text
+);
diff --git a/SQL/sqlite.initial.sql b/SQL/sqlite.initial.sql
index f5b5615d8..76913e89c 100644
--- a/SQL/sqlite.initial.sql
+++ b/SQL/sqlite.initial.sql
@@ -1,199 +1,212 @@
-- Roundcube Webmail initial database structure
--
-- Table structure for table cache
--
CREATE TABLE cache (
user_id integer NOT NULL default 0,
cache_key varchar(128) NOT NULL default '',
created datetime NOT NULL default '0000-00-00 00:00:00',
data text NOT NULL
);
CREATE INDEX ix_cache_user_cache_key ON cache(user_id, cache_key);
CREATE INDEX ix_cache_created ON cache(created);
-- --------------------------------------------------------
--
-- Table structure for table contacts and related
--
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 ''
);
CREATE INDEX ix_contacts_user_id ON contacts(user_id, del);
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)
);
CREATE INDEX ix_contactgroupmembers_contact_id ON contactgroupmembers (contact_id);
-- --------------------------------------------------------
--
-- Table structure for 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);
CREATE INDEX ix_identities_email ON identities(email, del);
-- --------------------------------------------------------
--
-- Table structure for 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 ''
);
CREATE UNIQUE INDEX ix_users_username ON users(username, mail_host);
-- --------------------------------------------------------
--
-- Table structure for 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);
-- --------------------------------------------------------
--
-- Table structure for table dictionary
--
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");
-- --------------------------------------------------------
--
-- Table structure for table searches
--
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);
-- --------------------------------------------------------
--
-- Table structure for table cache_index
--
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);
-- --------------------------------------------------------
--
-- Table structure for table cache_thread
--
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);
-- --------------------------------------------------------
--
-- Table structure for table cache_messages
--
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);
+
+-- --------------------------------------------------------
+
+--
+-- Table structure for table system
+--
+
+CREATE TABLE system (
+ name varchar(64) NOT NULL PRIMARY KEY,
+ value text NOT NULL
+);
+
+INSERT INTO system (name, value) VALUES ('roundcube-version', '2013011000.sql');
diff --git a/SQL/sqlite.update.sql b/SQL/sqlite.update.sql
deleted file mode 100644
index 72a29e9ae..000000000
--- a/SQL/sqlite.update.sql
+++ /dev/null
@@ -1,380 +0,0 @@
--- Roundcube Webmail update script for SQLite databases
--- Updates from version 0.1-stable to 0.1.1
-
-DROP TABLE messages;
-
-CREATE TABLE messages (
- message_id integer NOT NULL PRIMARY KEY,
- user_id integer NOT NULL default '0',
- del tinyint NOT NULL default '0',
- cache_key varchar(128) NOT NULL default '',
- created datetime NOT NULL default '0000-00-00 00:00:00',
- idx integer NOT NULL default '0',
- uid integer NOT NULL default '0',
- subject varchar(255) NOT NULL default '',
- "from" varchar(255) NOT NULL default '',
- "to" varchar(255) NOT NULL default '',
- "cc" varchar(255) NOT NULL default '',
- "date" datetime NOT NULL default '0000-00-00 00:00:00',
- size integer NOT NULL default '0',
- headers text NOT NULL,
- structure text
-);
-
-CREATE INDEX ix_messages_user_cache_uid ON messages(user_id,cache_key,uid);
-CREATE INDEX ix_users_username ON users(username);
-CREATE INDEX ix_users_alias ON users(alias);
-
--- Updates from version 0.2-alpha
-
-CREATE INDEX ix_messages_created ON messages (created);
-
--- Updates from version 0.2-beta
-
-CREATE INDEX ix_session_changed ON session (changed);
-CREATE INDEX ix_cache_created ON cache (created);
-
--- Updates from version 0.3-stable
-
-DELETE FROM messages;
-DROP INDEX ix_messages_user_cache_uid;
-CREATE UNIQUE INDEX ix_messages_user_cache_uid ON messages (user_id,cache_key,uid);
-CREATE INDEX ix_messages_index ON messages (user_id,cache_key,idx);
-DROP INDEX ix_contacts_user_id;
-CREATE INDEX ix_contacts_user_id ON contacts(user_id, email);
-
--- Updates from version 0.3.1
-
--- ALTER TABLE identities ADD COLUMN changed datetime NOT NULL default '0000-00-00 00:00:00'; --
-
-CREATE TABLE temp_identities (
- identity_id integer NOT NULL PRIMARY KEY,
- user_id integer NOT NULL default '0',
- standard tinyint NOT NULL default '0',
- name varchar(128) NOT NULL default '',
- organization varchar(128) default '',
- email varchar(128) NOT NULL default '',
- "reply-to" varchar(128) NOT NULL default '',
- bcc varchar(128) NOT NULL default '',
- signature text NOT NULL default '',
- html_signature tinyint NOT NULL default '0'
-);
-INSERT INTO temp_identities (identity_id, user_id, standard, name, organization, email, "reply-to", bcc, signature, html_signature)
- SELECT identity_id, user_id, standard, name, organization, email, "reply-to", bcc, signature, html_signature
- FROM identities WHERE del=0;
-
-DROP INDEX ix_identities_user_id;
-DROP TABLE identities;
-
-CREATE TABLE identities (
- identity_id integer NOT NULL PRIMARY KEY,
- user_id integer NOT NULL default '0',
- changed datetime NOT NULL default '0000-00-00 00:00:00',
- del tinyint NOT NULL default '0',
- standard tinyint NOT NULL default '0',
- name varchar(128) NOT NULL default '',
- organization varchar(128) default '',
- email varchar(128) NOT NULL default '',
- "reply-to" varchar(128) NOT NULL default '',
- bcc varchar(128) NOT NULL default '',
- signature text NOT NULL default '',
- html_signature tinyint NOT NULL default '0'
-);
-CREATE INDEX ix_identities_user_id ON identities(user_id, del);
-
-INSERT INTO identities (identity_id, user_id, standard, name, organization, email, "reply-to", bcc, signature, html_signature)
- SELECT identity_id, user_id, standard, name, organization, email, "reply-to", bcc, signature, html_signature
- FROM temp_identities;
-
-DROP TABLE temp_identities;
-
-CREATE TABLE contactgroups (
- contactgroup_id integer NOT NULL PRIMARY KEY,
- user_id integer NOT NULL default '0',
- changed datetime NOT NULL default '0000-00-00 00:00:00',
- del tinyint NOT NULL default '0',
- name varchar(128) NOT NULL default ''
-);
-
-CREATE INDEX ix_contactgroups_user_id ON contactgroups(user_id, del);
-
-CREATE TABLE contactgroupmembers (
- contactgroup_id integer NOT NULL,
- contact_id integer NOT NULL default '0',
- created datetime NOT NULL default '0000-00-00 00:00:00',
- PRIMARY KEY (contactgroup_id, contact_id)
-);
-
--- Updates from version 0.3.1
-
-CREATE TABLE tmp_users (
- user_id integer NOT NULL PRIMARY KEY,
- username varchar(128) NOT NULL default '',
- mail_host varchar(128) NOT NULL default '',
- alias varchar(128) NOT NULL default '',
- created datetime NOT NULL default '0000-00-00 00:00:00',
- last_login datetime NOT NULL default '0000-00-00 00:00:00',
- language varchar(5),
- preferences text NOT NULL default ''
-);
-
-INSERT INTO tmp_users (user_id, username, mail_host, alias, created, last_login, language, preferences)
- SELECT user_id, username, mail_host, alias, created, last_login, language, preferences FROM users;
-
-DROP TABLE users;
-
-CREATE TABLE users (
- user_id integer NOT NULL PRIMARY KEY,
- username varchar(128) NOT NULL default '',
- mail_host varchar(128) NOT NULL default '',
- alias varchar(128) NOT NULL default '',
- created datetime NOT NULL default '0000-00-00 00:00:00',
- last_login datetime DEFAULT NULL,
- language varchar(5),
- preferences text NOT NULL default ''
-);
-
-INSERT INTO users (user_id, username, mail_host, alias, created, last_login, language, preferences)
- SELECT user_id, username, mail_host, alias, created, last_login, language, preferences FROM tmp_users;
-
-CREATE INDEX ix_users_username ON users(username);
-CREATE INDEX ix_users_alias ON users(alias);
-DROP TABLE tmp_users;
-
--- Updates from version 0.4.2
-
-DROP INDEX ix_users_username;
-CREATE UNIQUE INDEX ix_users_username ON users(username, mail_host);
-
-CREATE TABLE contacts_tmp (
- contact_id integer NOT NULL PRIMARY KEY,
- user_id integer NOT NULL default '0',
- changed datetime NOT NULL default '0000-00-00 00:00:00',
- del tinyint NOT NULL default '0',
- name varchar(128) NOT NULL default '',
- email varchar(255) NOT NULL default '',
- firstname varchar(128) NOT NULL default '',
- surname varchar(128) NOT NULL default '',
- vcard text NOT NULL default ''
-);
-
-INSERT INTO contacts_tmp (contact_id, user_id, changed, del, name, email, firstname, surname, vcard)
- SELECT contact_id, user_id, changed, del, name, email, firstname, surname, vcard FROM contacts;
-
-DROP TABLE contacts;
-CREATE TABLE contacts (
- contact_id integer NOT NULL PRIMARY KEY,
- user_id integer NOT NULL default '0',
- changed datetime NOT NULL default '0000-00-00 00:00:00',
- del tinyint NOT NULL default '0',
- name varchar(128) NOT NULL default '',
- email varchar(255) NOT NULL default '',
- firstname varchar(128) NOT NULL default '',
- surname varchar(128) NOT NULL default '',
- vcard text NOT NULL default ''
-);
-
-INSERT INTO contacts (contact_id, user_id, changed, del, name, email, firstname, surname, vcard)
- SELECT contact_id, user_id, changed, del, name, email, firstname, surname, vcard FROM contacts_tmp;
-
-CREATE INDEX ix_contacts_user_id ON contacts(user_id, email);
-DROP TABLE contacts_tmp;
-
-DELETE FROM messages;
-
-
--- Updates from version 0.5.1
--- Updates from version 0.5.2
--- Updates from version 0.5.3
--- Updates from version 0.5.4
-
-CREATE TABLE contacts_tmp (
- contact_id integer NOT NULL PRIMARY KEY,
- user_id integer NOT NULL default '0',
- changed datetime NOT NULL default '0000-00-00 00:00:00',
- del tinyint NOT NULL default '0',
- name varchar(128) NOT NULL default '',
- email varchar(255) NOT NULL default '',
- firstname varchar(128) NOT NULL default '',
- surname varchar(128) NOT NULL default '',
- vcard text NOT NULL default ''
-);
-
-INSERT INTO contacts_tmp (contact_id, user_id, changed, del, name, email, firstname, surname, vcard)
- SELECT contact_id, user_id, changed, del, name, email, firstname, surname, vcard FROM contacts;
-
-DROP TABLE contacts;
-CREATE TABLE contacts (
- contact_id integer NOT NULL PRIMARY KEY,
- user_id integer NOT NULL default '0',
- changed datetime NOT NULL default '0000-00-00 00:00:00',
- del tinyint NOT NULL default '0',
- name varchar(128) NOT NULL default '',
- email varchar(255) NOT NULL default '',
- firstname varchar(128) NOT NULL default '',
- surname varchar(128) NOT NULL default '',
- vcard text NOT NULL default '',
- words text NOT NULL default ''
-);
-
-INSERT INTO contacts (contact_id, user_id, changed, del, name, email, firstname, surname, vcard)
- SELECT contact_id, user_id, changed, del, name, email, firstname, surname, vcard FROM contacts_tmp;
-
-CREATE INDEX ix_contacts_user_id ON contacts(user_id, email);
-DROP TABLE contacts_tmp;
-
-
-DELETE FROM messages;
-DELETE FROM cache;
-CREATE INDEX ix_contactgroupmembers_contact_id ON contactgroupmembers (contact_id);
-
--- Updates from version 0.6
-
-CREATE TABLE dictionary (
- user_id integer DEFAULT NULL,
- "language" varchar(5) NOT NULL,
- data text NOT NULL
-);
-
-CREATE UNIQUE INDEX ix_dictionary_user_language ON dictionary (user_id, "language");
-
-CREATE TABLE searches (
- search_id integer NOT NULL PRIMARY KEY,
- user_id integer NOT NULL DEFAULT '0',
- "type" smallint NOT NULL DEFAULT '0',
- name varchar(128) NOT NULL,
- data text NOT NULL
-);
-
-CREATE UNIQUE INDEX ix_searches_user_type_name ON searches (user_id, type, name);
-
-DROP TABLE messages;
-
-CREATE TABLE cache_index (
- user_id integer NOT NULL,
- mailbox varchar(255) NOT NULL,
- changed datetime NOT NULL default '0000-00-00 00:00:00',
- valid smallint NOT NULL DEFAULT '0',
- data text NOT NULL,
- PRIMARY KEY (user_id, mailbox)
-);
-
-CREATE INDEX ix_cache_index_changed ON cache_index (changed);
-
-CREATE TABLE cache_thread (
- user_id integer NOT NULL,
- mailbox varchar(255) NOT NULL,
- changed datetime NOT NULL default '0000-00-00 00:00:00',
- data text NOT NULL,
- PRIMARY KEY (user_id, mailbox)
-);
-
-CREATE INDEX ix_cache_thread_changed ON cache_thread (changed);
-
-CREATE TABLE cache_messages (
- user_id integer NOT NULL,
- mailbox varchar(255) NOT NULL,
- uid integer NOT NULL,
- changed datetime NOT NULL default '0000-00-00 00:00:00',
- data text NOT NULL,
- flags integer NOT NULL DEFAULT '0',
- PRIMARY KEY (user_id, mailbox, uid)
-);
-
-CREATE INDEX ix_cache_messages_changed ON cache_messages (changed);
-
--- Updates from version 0.7-beta
-
-DROP TABLE session;
-CREATE TABLE session (
- sess_id varchar(128) NOT NULL PRIMARY KEY,
- created datetime NOT NULL default '0000-00-00 00:00:00',
- changed datetime NOT NULL default '0000-00-00 00:00:00',
- ip varchar(40) NOT NULL default '',
- vars text NOT NULL
-);
-CREATE INDEX ix_session_changed ON session (changed);
-
--- Updates from version 0.7
-
-CREATE TABLE contacts_tmp (
- contact_id integer NOT NULL PRIMARY KEY,
- user_id integer NOT NULL,
- changed datetime NOT NULL default '0000-00-00 00:00:00',
- del tinyint NOT NULL default '0',
- name varchar(128) NOT NULL default '',
- email text NOT NULL default '',
- firstname varchar(128) NOT NULL default '',
- surname varchar(128) NOT NULL default '',
- vcard text NOT NULL default '',
- words text NOT NULL default ''
-);
-
-INSERT INTO contacts_tmp (contact_id, user_id, changed, del, name, email, firstname, surname, vcard, words)
- SELECT contact_id, user_id, changed, del, name, email, firstname, surname, vcard, words FROM contacts;
-
-DROP TABLE contacts;
-
-CREATE TABLE contacts (
- contact_id integer NOT NULL PRIMARY KEY,
- user_id integer NOT NULL,
- changed datetime NOT NULL default '0000-00-00 00:00:00',
- del tinyint NOT NULL default '0',
- name varchar(128) NOT NULL default '',
- email text NOT NULL default '',
- firstname varchar(128) NOT NULL default '',
- surname varchar(128) NOT NULL default '',
- vcard text NOT NULL default '',
- words text NOT NULL default ''
-);
-
-INSERT INTO contacts (contact_id, user_id, changed, del, name, email, firstname, surname, vcard, words)
- SELECT contact_id, user_id, changed, del, name, email, firstname, surname, vcard, words FROM contacts_tmp;
-
-CREATE INDEX ix_contacts_user_id ON contacts(user_id, del);
-DROP TABLE contacts_tmp;
-
--- Updates from version 0.8
-
-DROP TABLE cache;
-CREATE TABLE cache (
- user_id integer NOT NULL default 0,
- cache_key varchar(128) NOT NULL default '',
- created datetime NOT NULL default '0000-00-00 00:00:00',
- data text NOT NULL
-);
-
-CREATE INDEX ix_cache_user_cache_key ON cache(user_id, cache_key);
-CREATE INDEX ix_cache_created ON cache(created);
-
-CREATE TABLE tmp_users (
- user_id integer NOT NULL PRIMARY KEY,
- username varchar(128) NOT NULL default '',
- mail_host varchar(128) NOT NULL default '',
- created datetime NOT NULL default '0000-00-00 00:00:00',
- last_login datetime DEFAULT NULL,
- language varchar(5),
- preferences text NOT NULL default ''
-);
-
-INSERT INTO tmp_users (user_id, username, mail_host, created, last_login, language, preferences)
- SELECT user_id, username, mail_host, created, last_login, language, preferences FROM users;
-
-DROP TABLE users;
-
-CREATE TABLE users (
- user_id integer NOT NULL PRIMARY KEY,
- username varchar(128) NOT NULL default '',
- mail_host varchar(128) NOT NULL default '',
- created datetime NOT NULL default '0000-00-00 00:00:00',
- last_login datetime DEFAULT NULL,
- language varchar(5),
- preferences text NOT NULL default ''
-);
-
-INSERT INTO users (user_id, username, mail_host, created, last_login, language, preferences)
- SELECT user_id, username, mail_host, created, last_login, language, preferences FROM tmp_users;
-
-CREATE UNIQUE INDEX ix_users_username ON users(username, mail_host);
-
-CREATE INDEX ix_identities_email ON identities(email, del);
diff --git a/SQL/sqlite/2008030300.sql b/SQL/sqlite/2008030300.sql
new file mode 100644
index 000000000..b5b4890bf
--- /dev/null
+++ b/SQL/sqlite/2008030300.sql
@@ -0,0 +1,25 @@
+-- Updates from version 0.1-stable to 0.1.1
+
+DROP TABLE messages;
+
+CREATE TABLE messages (
+ message_id integer NOT NULL PRIMARY KEY,
+ user_id integer NOT NULL default '0',
+ del tinyint NOT NULL default '0',
+ cache_key varchar(128) NOT NULL default '',
+ created datetime NOT NULL default '0000-00-00 00:00:00',
+ idx integer NOT NULL default '0',
+ uid integer NOT NULL default '0',
+ subject varchar(255) NOT NULL default '',
+ "from" varchar(255) NOT NULL default '',
+ "to" varchar(255) NOT NULL default '',
+ "cc" varchar(255) NOT NULL default '',
+ "date" datetime NOT NULL default '0000-00-00 00:00:00',
+ size integer NOT NULL default '0',
+ headers text NOT NULL,
+ structure text
+);
+
+CREATE INDEX ix_messages_user_cache_uid ON messages(user_id,cache_key,uid);
+CREATE INDEX ix_users_username ON users(username);
+CREATE INDEX ix_users_alias ON users(alias);
diff --git a/SQL/sqlite/2008060900.sql b/SQL/sqlite/2008060900.sql
new file mode 100644
index 000000000..157029c28
--- /dev/null
+++ b/SQL/sqlite/2008060900.sql
@@ -0,0 +1,3 @@
+-- Updates from version 0.2-alpha
+
+CREATE INDEX ix_messages_created ON messages (created);
diff --git a/SQL/sqlite/2008092100.sql b/SQL/sqlite/2008092100.sql
new file mode 100644
index 000000000..8bc081c79
--- /dev/null
+++ b/SQL/sqlite/2008092100.sql
@@ -0,0 +1,4 @@
+-- Updates from version 0.2-beta
+
+CREATE INDEX ix_session_changed ON session (changed);
+CREATE INDEX ix_cache_created ON cache (created);
diff --git a/SQL/sqlite/2009090400.sql b/SQL/sqlite/2009090400.sql
new file mode 100644
index 000000000..c8a53edf1
--- /dev/null
+++ b/SQL/sqlite/2009090400.sql
@@ -0,0 +1,8 @@
+-- Updates from version 0.3-stable
+
+DELETE FROM messages;
+DROP INDEX ix_messages_user_cache_uid;
+CREATE UNIQUE INDEX ix_messages_user_cache_uid ON messages (user_id,cache_key,uid);
+CREATE INDEX ix_messages_index ON messages (user_id,cache_key,idx);
+DROP INDEX ix_contacts_user_id;
+CREATE INDEX ix_contacts_user_id ON contacts(user_id, email);
diff --git a/SQL/sqlite/2009103100.sql b/SQL/sqlite/2009103100.sql
new file mode 100644
index 000000000..bd7b1741c
--- /dev/null
+++ b/SQL/sqlite/2009103100.sql
@@ -0,0 +1,61 @@
+-- Updates from version 0.3.1
+
+-- ALTER TABLE identities ADD COLUMN changed datetime NOT NULL default '0000-00-00 00:00:00'; --
+
+CREATE TABLE temp_identities (
+ identity_id integer NOT NULL PRIMARY KEY,
+ user_id integer NOT NULL default '0',
+ standard tinyint NOT NULL default '0',
+ name varchar(128) NOT NULL default '',
+ organization varchar(128) default '',
+ email varchar(128) NOT NULL default '',
+ "reply-to" varchar(128) NOT NULL default '',
+ bcc varchar(128) NOT NULL default '',
+ signature text NOT NULL default '',
+ html_signature tinyint NOT NULL default '0'
+);
+INSERT INTO temp_identities (identity_id, user_id, standard, name, organization, email, "reply-to", bcc, signature, html_signature)
+ SELECT identity_id, user_id, standard, name, organization, email, "reply-to", bcc, signature, html_signature
+ FROM identities WHERE del=0;
+
+DROP INDEX ix_identities_user_id;
+DROP TABLE identities;
+
+CREATE TABLE identities (
+ identity_id integer NOT NULL PRIMARY KEY,
+ user_id integer NOT NULL default '0',
+ changed datetime NOT NULL default '0000-00-00 00:00:00',
+ del tinyint NOT NULL default '0',
+ standard tinyint NOT NULL default '0',
+ name varchar(128) NOT NULL default '',
+ organization varchar(128) default '',
+ email varchar(128) NOT NULL default '',
+ "reply-to" varchar(128) NOT NULL default '',
+ bcc varchar(128) NOT NULL default '',
+ signature text NOT NULL default '',
+ html_signature tinyint NOT NULL default '0'
+);
+CREATE INDEX ix_identities_user_id ON identities(user_id, del);
+
+INSERT INTO identities (identity_id, user_id, standard, name, organization, email, "reply-to", bcc, signature, html_signature)
+ SELECT identity_id, user_id, standard, name, organization, email, "reply-to", bcc, signature, html_signature
+ FROM temp_identities;
+
+DROP TABLE temp_identities;
+
+CREATE TABLE contactgroups (
+ contactgroup_id integer NOT NULL PRIMARY KEY,
+ user_id integer NOT NULL default '0',
+ changed datetime NOT NULL default '0000-00-00 00:00:00',
+ del tinyint NOT NULL default '0',
+ name varchar(128) NOT NULL default ''
+);
+
+CREATE INDEX ix_contactgroups_user_id ON contactgroups(user_id, del);
+
+CREATE TABLE contactgroupmembers (
+ contactgroup_id integer NOT NULL,
+ contact_id integer NOT NULL default '0',
+ created datetime NOT NULL default '0000-00-00 00:00:00',
+ PRIMARY KEY (contactgroup_id, contact_id)
+);
diff --git a/SQL/sqlite/2010042300.sql b/SQL/sqlite/2010042300.sql
new file mode 100644
index 000000000..1a9023cfc
--- /dev/null
+++ b/SQL/sqlite/2010042300.sql
@@ -0,0 +1,35 @@
+-- Updates from version 0.4-beta
+
+CREATE TABLE tmp_users (
+ user_id integer NOT NULL PRIMARY KEY,
+ username varchar(128) NOT NULL default '',
+ mail_host varchar(128) NOT NULL default '',
+ alias varchar(128) NOT NULL default '',
+ created datetime NOT NULL default '0000-00-00 00:00:00',
+ last_login datetime NOT NULL default '0000-00-00 00:00:00',
+ language varchar(5),
+ preferences text NOT NULL default ''
+);
+
+INSERT INTO tmp_users (user_id, username, mail_host, alias, created, last_login, language, preferences)
+ SELECT user_id, username, mail_host, alias, created, last_login, language, preferences FROM users;
+
+DROP TABLE users;
+
+CREATE TABLE users (
+ user_id integer NOT NULL PRIMARY KEY,
+ username varchar(128) NOT NULL default '',
+ mail_host varchar(128) NOT NULL default '',
+ alias varchar(128) NOT NULL default '',
+ created datetime NOT NULL default '0000-00-00 00:00:00',
+ last_login datetime DEFAULT NULL,
+ language varchar(5),
+ preferences text NOT NULL default ''
+);
+
+INSERT INTO users (user_id, username, mail_host, alias, created, last_login, language, preferences)
+ SELECT user_id, username, mail_host, alias, created, last_login, language, preferences FROM tmp_users;
+
+CREATE INDEX ix_users_username ON users(username);
+CREATE INDEX ix_users_alias ON users(alias);
+DROP TABLE tmp_users;
diff --git a/SQL/sqlite/2010100600.sql b/SQL/sqlite/2010100600.sql
new file mode 100644
index 000000000..5c7259f37
--- /dev/null
+++ b/SQL/sqlite/2010100600.sql
@@ -0,0 +1,40 @@
+-- Updates from version 0.4.2
+
+DROP INDEX ix_users_username;
+CREATE UNIQUE INDEX ix_users_username ON users(username, mail_host);
+
+CREATE TABLE contacts_tmp (
+ contact_id integer NOT NULL PRIMARY KEY,
+ user_id integer NOT NULL default '0',
+ changed datetime NOT NULL default '0000-00-00 00:00:00',
+ del tinyint NOT NULL default '0',
+ name varchar(128) NOT NULL default '',
+ email varchar(255) NOT NULL default '',
+ firstname varchar(128) NOT NULL default '',
+ surname varchar(128) NOT NULL default '',
+ vcard text NOT NULL default ''
+);
+
+INSERT INTO contacts_tmp (contact_id, user_id, changed, del, name, email, firstname, surname, vcard)
+ SELECT contact_id, user_id, changed, del, name, email, firstname, surname, vcard FROM contacts;
+
+DROP TABLE contacts;
+CREATE TABLE contacts (
+ contact_id integer NOT NULL PRIMARY KEY,
+ user_id integer NOT NULL default '0',
+ changed datetime NOT NULL default '0000-00-00 00:00:00',
+ del tinyint NOT NULL default '0',
+ name varchar(128) NOT NULL default '',
+ email varchar(255) NOT NULL default '',
+ firstname varchar(128) NOT NULL default '',
+ surname varchar(128) NOT NULL default '',
+ vcard text NOT NULL default ''
+);
+
+INSERT INTO contacts (contact_id, user_id, changed, del, name, email, firstname, surname, vcard)
+ SELECT contact_id, user_id, changed, del, name, email, firstname, surname, vcard FROM contacts_tmp;
+
+CREATE INDEX ix_contacts_user_id ON contacts(user_id, email);
+DROP TABLE contacts_tmp;
+
+DELETE FROM messages;
diff --git a/SQL/sqlite/2011011200.sql b/SQL/sqlite/2011011200.sql
new file mode 100644
index 000000000..4cca74076
--- /dev/null
+++ b/SQL/sqlite/2011011200.sql
@@ -0,0 +1,41 @@
+-- Updates from version 0.5.x
+
+CREATE TABLE contacts_tmp (
+ contact_id integer NOT NULL PRIMARY KEY,
+ user_id integer NOT NULL default '0',
+ changed datetime NOT NULL default '0000-00-00 00:00:00',
+ del tinyint NOT NULL default '0',
+ name varchar(128) NOT NULL default '',
+ email varchar(255) NOT NULL default '',
+ firstname varchar(128) NOT NULL default '',
+ surname varchar(128) NOT NULL default '',
+ vcard text NOT NULL default ''
+);
+
+INSERT INTO contacts_tmp (contact_id, user_id, changed, del, name, email, firstname, surname, vcard)
+ SELECT contact_id, user_id, changed, del, name, email, firstname, surname, vcard FROM contacts;
+
+DROP TABLE contacts;
+CREATE TABLE contacts (
+ contact_id integer NOT NULL PRIMARY KEY,
+ user_id integer NOT NULL default '0',
+ changed datetime NOT NULL default '0000-00-00 00:00:00',
+ del tinyint NOT NULL default '0',
+ name varchar(128) NOT NULL default '',
+ email varchar(255) NOT NULL default '',
+ firstname varchar(128) NOT NULL default '',
+ surname varchar(128) NOT NULL default '',
+ vcard text NOT NULL default '',
+ words text NOT NULL default ''
+);
+
+INSERT INTO contacts (contact_id, user_id, changed, del, name, email, firstname, surname, vcard)
+ SELECT contact_id, user_id, changed, del, name, email, firstname, surname, vcard FROM contacts_tmp;
+
+CREATE INDEX ix_contacts_user_id ON contacts(user_id, email);
+DROP TABLE contacts_tmp;
+
+
+DELETE FROM messages;
+DELETE FROM cache;
+CREATE INDEX ix_contactgroupmembers_contact_id ON contactgroupmembers (contact_id);
diff --git a/SQL/sqlite/2011092800.sql b/SQL/sqlite/2011092800.sql
new file mode 100644
index 000000000..27dbffc1d
--- /dev/null
+++ b/SQL/sqlite/2011092800.sql
@@ -0,0 +1,54 @@
+-- Updates from version 0.6
+
+CREATE TABLE dictionary (
+ user_id integer DEFAULT NULL,
+ "language" varchar(5) NOT NULL,
+ data text NOT NULL
+);
+
+CREATE UNIQUE INDEX ix_dictionary_user_language ON dictionary (user_id, "language");
+
+CREATE TABLE searches (
+ search_id integer NOT NULL PRIMARY KEY,
+ user_id integer NOT NULL DEFAULT '0',
+ "type" smallint NOT NULL DEFAULT '0',
+ name varchar(128) NOT NULL,
+ data text NOT NULL
+);
+
+CREATE UNIQUE INDEX ix_searches_user_type_name ON searches (user_id, type, name);
+
+DROP TABLE messages;
+
+CREATE TABLE cache_index (
+ user_id integer NOT NULL,
+ mailbox varchar(255) NOT NULL,
+ changed datetime NOT NULL default '0000-00-00 00:00:00',
+ valid smallint NOT NULL DEFAULT '0',
+ data text NOT NULL,
+ PRIMARY KEY (user_id, mailbox)
+);
+
+CREATE INDEX ix_cache_index_changed ON cache_index (changed);
+
+CREATE TABLE cache_thread (
+ user_id integer NOT NULL,
+ mailbox varchar(255) NOT NULL,
+ changed datetime NOT NULL default '0000-00-00 00:00:00',
+ data text NOT NULL,
+ PRIMARY KEY (user_id, mailbox)
+);
+
+CREATE INDEX ix_cache_thread_changed ON cache_thread (changed);
+
+CREATE TABLE cache_messages (
+ user_id integer NOT NULL,
+ mailbox varchar(255) NOT NULL,
+ uid integer NOT NULL,
+ changed datetime NOT NULL default '0000-00-00 00:00:00',
+ data text NOT NULL,
+ flags integer NOT NULL DEFAULT '0',
+ PRIMARY KEY (user_id, mailbox, uid)
+);
+
+CREATE INDEX ix_cache_messages_changed ON cache_messages (changed);
diff --git a/SQL/sqlite/2011111600.sql b/SQL/sqlite/2011111600.sql
new file mode 100644
index 000000000..cea0d129e
--- /dev/null
+++ b/SQL/sqlite/2011111600.sql
@@ -0,0 +1,11 @@
+-- Updates from version 0.7-beta
+
+DROP TABLE session;
+CREATE TABLE session (
+ sess_id varchar(128) NOT NULL PRIMARY KEY,
+ created datetime NOT NULL default '0000-00-00 00:00:00',
+ changed datetime NOT NULL default '0000-00-00 00:00:00',
+ ip varchar(40) NOT NULL default '',
+ vars text NOT NULL
+);
+CREATE INDEX ix_session_changed ON session (changed);
diff --git a/SQL/sqlite/2011121400.sql b/SQL/sqlite/2011121400.sql
new file mode 100644
index 000000000..9d9227673
--- /dev/null
+++ b/SQL/sqlite/2011121400.sql
@@ -0,0 +1,38 @@
+-- Updates from version 0.7
+
+CREATE TABLE contacts_tmp (
+ contact_id integer NOT NULL PRIMARY KEY,
+ user_id integer NOT NULL,
+ changed datetime NOT NULL default '0000-00-00 00:00:00',
+ del tinyint NOT NULL default '0',
+ name varchar(128) NOT NULL default '',
+ email text NOT NULL default '',
+ firstname varchar(128) NOT NULL default '',
+ surname varchar(128) NOT NULL default '',
+ vcard text NOT NULL default '',
+ words text NOT NULL default ''
+);
+
+INSERT INTO contacts_tmp (contact_id, user_id, changed, del, name, email, firstname, surname, vcard, words)
+ SELECT contact_id, user_id, changed, del, name, email, firstname, surname, vcard, words FROM contacts;
+
+DROP TABLE contacts;
+
+CREATE TABLE contacts (
+ contact_id integer NOT NULL PRIMARY KEY,
+ user_id integer NOT NULL,
+ changed datetime NOT NULL default '0000-00-00 00:00:00',
+ del tinyint NOT NULL default '0',
+ name varchar(128) NOT NULL default '',
+ email text NOT NULL default '',
+ firstname varchar(128) NOT NULL default '',
+ surname varchar(128) NOT NULL default '',
+ vcard text NOT NULL default '',
+ words text NOT NULL default ''
+);
+
+INSERT INTO contacts (contact_id, user_id, changed, del, name, email, firstname, surname, vcard, words)
+ SELECT contact_id, user_id, changed, del, name, email, firstname, surname, vcard, words FROM contacts_tmp;
+
+CREATE INDEX ix_contacts_user_id ON contacts(user_id, del);
+DROP TABLE contacts_tmp;
diff --git a/SQL/sqlite/2012080700.sql b/SQL/sqlite/2012080700.sql
new file mode 100644
index 000000000..c6ede89e8
--- /dev/null
+++ b/SQL/sqlite/2012080700.sql
@@ -0,0 +1,44 @@
+-- Updates from version 0.8
+
+DROP TABLE cache;
+CREATE TABLE cache (
+ user_id integer NOT NULL default 0,
+ cache_key varchar(128) NOT NULL default '',
+ created datetime NOT NULL default '0000-00-00 00:00:00',
+ data text NOT NULL
+);
+
+CREATE INDEX ix_cache_user_cache_key ON cache(user_id, cache_key);
+CREATE INDEX ix_cache_created ON cache(created);
+
+CREATE TABLE tmp_users (
+ user_id integer NOT NULL PRIMARY KEY,
+ username varchar(128) NOT NULL default '',
+ mail_host varchar(128) NOT NULL default '',
+ created datetime NOT NULL default '0000-00-00 00:00:00',
+ last_login datetime DEFAULT NULL,
+ language varchar(5),
+ preferences text NOT NULL default ''
+);
+
+INSERT INTO tmp_users (user_id, username, mail_host, created, last_login, language, preferences)
+ SELECT user_id, username, mail_host, created, last_login, language, preferences FROM users;
+
+DROP TABLE users;
+
+CREATE TABLE users (
+ user_id integer NOT NULL PRIMARY KEY,
+ username varchar(128) NOT NULL default '',
+ mail_host varchar(128) NOT NULL default '',
+ created datetime NOT NULL default '0000-00-00 00:00:00',
+ last_login datetime DEFAULT NULL,
+ language varchar(5),
+ preferences text NOT NULL default ''
+);
+
+INSERT INTO users (user_id, username, mail_host, created, last_login, language, preferences)
+ SELECT user_id, username, mail_host, created, last_login, language, preferences FROM tmp_users;
+
+CREATE UNIQUE INDEX ix_users_username ON users(username, mail_host);
+
+CREATE INDEX ix_identities_email ON identities(email, del);
diff --git a/SQL/sqlite/2013011000.sql b/SQL/sqlite/2013011000.sql
new file mode 100644
index 000000000..2c66ef95e
--- /dev/null
+++ b/SQL/sqlite/2013011000.sql
@@ -0,0 +1,6 @@
+-- Updates from version 0.9-beta
+
+CREATE TABLE system (
+ name varchar(64) NOT NULL PRIMARY KEY,
+ value text NOT NULL
+);
diff --git a/bin/update.sh b/bin/update.sh
index 2015aa904..536bad9ba 100755
--- a/bin/update.sh
+++ b/bin/update.sh
@@ -1,188 +1,168 @@
#!/usr/bin/env php
<?php
/*
+-----------------------------------------------------------------------+
| bin/update.sh |
| |
| This file is part of the Roundcube Webmail client |
| Copyright (C) 2010-2011, The Roundcube Dev Team |
| |
| 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: |
| Check local configuration and database schema after upgrading |
| to a new version |
+-----------------------------------------------------------------------+
| Author: Thomas Bruederli <roundcube@gmail.com> |
+-----------------------------------------------------------------------+
*/
define('INSTALL_PATH', realpath(dirname(__FILE__) . '/..') . '/' );
require_once INSTALL_PATH . 'program/include/clisetup.php';
require_once INSTALL_PATH . 'installer/rcube_install.php';
// get arguments
$opts = rcube_utils::get_opt(array('v' => 'version'));
// ask user if no version is specified
if (!$opts['version']) {
echo "What version are you upgrading from? Type '?' if you don't know.\n";
if (($input = trim(fgets(STDIN))) && preg_match('/^[0-9.]+[a-z-]*$/', $input))
$opts['version'] = $input;
}
if ($opts['version'] && version_compare(version_parse($opts['version']), version_parse(RCMAIL_VERSION), '>'))
die("Nothing to be done here. Bye!\n");
$RCI = rcube_install::get_instance();
$RCI->load_config();
if ($RCI->configured) {
$success = true;
if ($messages = $RCI->check_config()) {
$success = false;
$err = 0;
// list missing config options
if (is_array($messages['missing'])) {
echo "WARNING: Missing config options:\n";
echo "(These config options should be present in the current configuration)\n";
foreach ($messages['missing'] as $msg) {
echo "- '" . $msg['prop'] . ($msg['name'] ? "': " . $msg['name'] : "'") . "\n";
$err++;
}
echo "\n";
}
// list old/replaced config options
if (is_array($messages['replaced'])) {
echo "WARNING: Replaced config options:\n";
echo "(These config options have been replaced or renamed)\n";
foreach ($messages['replaced'] as $msg) {
echo "- '" . $msg['prop'] . "' was replaced by '" . $msg['replacement'] . "'\n";
$err++;
}
echo "\n";
}
// list obsolete config options (just a notice)
if (is_array($messages['obsolete'])) {
echo "NOTICE: Obsolete config options:\n";
echo "(You still have some obsolete or inexistent properties set. This isn't a problem but should be noticed)\n";
foreach ($messages['obsolete'] as $msg) {
echo "- '" . $msg['prop'] . ($msg['name'] ? "': " . $msg['name'] : "'") . "\n";
$err++;
}
echo "\n";
}
// ask user to update config files
if ($err) {
echo "Do you want me to fix your local configuration? (y/N)\n";
$input = trim(fgets(STDIN));
// positive: let's merge the local config with the defaults
if (strtolower($input) == 'y') {
$copy1 = $copy2 = $write1 = $write2 = false;
// backup current config
echo ". backing up the current config files...\n";
$copy1 = copy(RCMAIL_CONFIG_DIR . '/main.inc.php', RCMAIL_CONFIG_DIR . '/main.old.php');
$copy2 = copy(RCMAIL_CONFIG_DIR . '/db.inc.php', RCMAIL_CONFIG_DIR . '/db.old.php');
if ($copy1 && $copy2) {
$RCI->merge_config();
echo ". writing " . RCMAIL_CONFIG_DIR . "/main.inc.php...\n";
$write1 = file_put_contents(RCMAIL_CONFIG_DIR . '/main.inc.php', $RCI->create_config('main', true));
echo ". writing " . RCMAIL_CONFIG_DIR . "/main.db.php...\n";
$write2 = file_put_contents(RCMAIL_CONFIG_DIR . '/db.inc.php', $RCI->create_config('db', true));
}
// Success!
if ($write1 && $write2) {
echo "Done.\n";
echo "Your configuration files are now up-to-date!\n";
if ($messages['missing']) {
echo "But you still need to add the following missing options:\n";
foreach ($messages['missing'] as $msg)
echo "- '" . $msg['prop'] . ($msg['name'] ? "': " . $msg['name'] : "'") . "\n";
}
}
else {
echo "Failed to write config files!\n";
echo "Grant write privileges to the current user or update the files manually according to the above messages.\n";
}
}
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";
}
}
// check dependencies based on the current configuration
if (is_array($messages['dependencies'])) {
echo "WARNING: Dependency check failed!\n";
echo "(Some of your configuration settings require other options to be configured or additional PHP modules to be installed)\n";
foreach ($messages['dependencies'] as $msg) {
echo "- " . $msg['prop'] . ': ' . $msg['explain'] . "\n";
}
echo "Please fix your config files and run this script again!\n";
echo "See ya.\n";
}
}
// 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";
}
}
else {
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 000000000..4b922ea83
--- /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 a596d647d..9bbf7d75b 100644
--- a/config/db.inc.php.dist
+++ b/config/db.inc.php.dist
@@ -1,61 +1,62 @@
<?php
/*
+-----------------------------------------------------------------------+
| Configuration file for database access |
| |
| This file is part of the Roundcube Webmail client |
| Copyright (C) 2005-2012, The Roundcube Dev Team |
| |
| 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. |
| |
+-----------------------------------------------------------------------+
*/
$rcmail_config = array();
// PEAR database DSN for read/write operations
// format is db_provider://user:password@host/database
// For examples see http://pear.php.net/manual/en/package.database.mdb2.intro-dsn.php
// currently supported db_providers: mysql, mysqli, pgsql, sqlite, mssql or sqlsrv
$rcmail_config['db_dsnw'] = 'mysql://roundcube:pass@localhost/roundcubemail';
// postgres example: 'pgsql://roundcube:pass@localhost/roundcubemail';
// Warning: for SQLite use absolute path in DSN:
// sqlite example: 'sqlite:////full/path/to/sqlite.db?mode=0646';
// PEAR database DSN for read only operations (if empty write database will be used)
// useful for database replication
$rcmail_config['db_dsnr'] = '';
// use persistent db-connections
// beware this will not "always" work as expected
// see: http://www.php.net/manual/en/features.persistent-connections.php
$rcmail_config['db_persistent'] = FALSE;
// you can define specific table names used to store webmail data
$rcmail_config['db_table_users'] = 'users';
$rcmail_config['db_table_identities'] = 'identities';
$rcmail_config['db_table_contacts'] = 'contacts';
$rcmail_config['db_table_contactgroups'] = 'contactgroups';
$rcmail_config['db_table_contactgroupmembers'] = 'contactgroupmembers';
$rcmail_config['db_table_session'] = 'session';
$rcmail_config['db_table_cache'] = 'cache';
$rcmail_config['db_table_cache_index'] = 'cache_index';
$rcmail_config['db_table_cache_thread'] = 'cache_thread';
$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';
$rcmail_config['db_sequence_identities'] = 'identity_ids';
$rcmail_config['db_sequence_contacts'] = 'contact_ids';
$rcmail_config['db_sequence_contactgroups'] = 'contactgroups_ids';
$rcmail_config['db_sequence_searches'] = 'search_ids';
// end db config file
diff --git a/installer/rcube_install.php b/installer/rcube_install.php
index e6fbc6251..33fd4c675 100644
--- a/installer/rcube_install.php
+++ b/installer/rcube_install.php
@@ -1,732 +1,703 @@
<?php
/*
+-----------------------------------------------------------------------+
| rcube_install.php |
| |
| This file is part of the Roundcube Webmail package |
| Copyright (C) 2008-2012, The Roundcube Dev Team |
| |
| 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. |
+-----------------------------------------------------------------------+
*/
/**
* Class to control the installation process of the Roundcube Webmail package
*
* @category Install
* @package Roundcube
* @author Thomas Bruederli
*/
class rcube_install
{
var $step;
var $is_post = false;
var $failures = 0;
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();
var $obsolete_config = array('db_backend', 'double_auth');
var $replaced_config = array(
'skin_path' => 'skin',
'locale_string' => 'language',
'multiple_identities' => 'identities_level',
'addrbook_show_images' => 'show_images',
'imap_root' => 'imap_ns_personal',
'pagesize' => 'mail_pagesize',
'default_imap_folders' => 'default_folders',
'top_posting' => 'reply_mode',
);
// these config options are required for a working system
var $required_config = array(
'db_dsnw', 'db_table_contactgroups', 'db_table_contactgroupmembers',
'des_key', 'session_lifetime', 'support_url',
);
// list of supported database drivers
var $supported_dbs = array(
'MySQL' => 'pdo_mysql',
'PostgreSQL' => 'pdo_pgsql',
'SQLite' => 'pdo_sqlite',
'SQLite (v2)' => 'pdo_sqlite2',
'SQL Server (SQLSRV)' => 'pdo_sqlsrv',
'SQL Server (DBLIB)' => 'pdo_dblib',
);
/**
* Constructor
*/
function rcube_install()
{
$this->step = intval($_REQUEST['_step']);
$this->is_post = $_SERVER['REQUEST_METHOD'] == 'POST';
}
/**
* Singleton getter
*/
function get_instance()
{
static $inst;
if (!$inst)
$inst = new rcube_install();
return $inst;
}
/**
* Read the default config files and store properties
*/
function load_defaults()
{
$this->_load_config('.php.dist');
}
/**
* Read the local config files and store properties
*/
function load_config()
{
$this->config = array();
$this->_load_config('.php');
$this->configured = !empty($this->config);
}
/**
* Read the default config file and store properties
* @access private
*/
function _load_config($suffix)
{
if (is_readable($main_inc = RCUBE_CONFIG_DIR . 'main.inc' . $suffix)) {
include($main_inc);
if (is_array($rcmail_config))
$this->config += $rcmail_config;
}
if (is_readable($db_inc = RCUBE_CONFIG_DIR . 'db.inc'. $suffix)) {
include($db_inc);
if (is_array($rcmail_config))
$this->config += $rcmail_config;
}
}
/**
* Getter for a certain config property
*
* @param string Property name
* @param string Default value
* @return string The property value
*/
function getprop($name, $default = '')
{
$value = $this->config[$name];
if ($name == 'des_key' && !$this->configured && !isset($_REQUEST["_$name"]))
$value = rcube_install::random_key(24);
return $value !== null && $value !== '' ? $value : $default;
}
/**
* Take the default config file and replace the parameters
* with the submitted form data
*
* @param string Which config file (either 'main' or 'db')
* @return string The complete config file content
*/
function create_config($which, $force = false)
{
$out = @file_get_contents(RCUBE_CONFIG_DIR . $which . '.inc.php.dist');
if (!$out)
return '[Warning: could not read the config template file]';
foreach ($this->config as $prop => $default) {
$is_default = !isset($_POST["_$prop"]);
$value = !$is_default || $this->bool_config_props[$prop] ? $_POST["_$prop"] : $default;
// convert some form data
if ($prop == 'debug_level' && !$is_default) {
if (is_array($value)) {
$val = 0;
foreach ($value as $dbgval)
$val += intval($dbgval);
$value = $val;
}
}
else if ($which == 'db' && $prop == 'db_dsnw' && !empty($_POST['_dbtype'])) {
if ($_POST['_dbtype'] == 'sqlite')
$value = sprintf('%s://%s?mode=0646', $_POST['_dbtype'], $_POST['_dbname']{0} == '/' ? '/' . $_POST['_dbname'] : $_POST['_dbname']);
else if ($_POST['_dbtype'])
$value = sprintf('%s://%s:%s@%s/%s', $_POST['_dbtype'],
rawurlencode($_POST['_dbuser']), rawurlencode($_POST['_dbpass']), $_POST['_dbhost'], $_POST['_dbname']);
}
else if ($prop == 'smtp_auth_type' && $value == '0') {
$value = '';
}
else if ($prop == 'default_host' && is_array($value)) {
$value = rcube_install::_clean_array($value);
if (count($value) <= 1)
$value = $value[0];
}
else if ($prop == 'mail_pagesize' || $prop == 'addressbook_pagesize') {
$value = max(2, intval($value));
}
else if ($prop == 'smtp_user' && !empty($_POST['_smtp_user_u'])) {
$value = '%u';
}
else if ($prop == 'smtp_pass' && !empty($_POST['_smtp_user_u'])) {
$value = '%p';
}
else if ($prop == 'default_folders') {
$value = array();
foreach ($this->config['default_folders'] as $_folder) {
switch ($_folder) {
case 'Drafts': $_folder = $this->config['drafts_mbox']; break;
case 'Sent': $_folder = $this->config['sent_mbox']; break;
case 'Junk': $_folder = $this->config['junk_mbox']; break;
case 'Trash': $_folder = $this->config['trash_mbox']; break;
}
if (!in_array($_folder, $value))
$value[] = $_folder;
}
}
else if (is_bool($default)) {
$value = (bool)$value;
}
else if (is_numeric($value)) {
$value = intval($value);
}
// skip this property
if (!$force && !$this->configured && ($value == $default))
continue;
// save change
$this->config[$prop] = $value;
// replace the matching line in config file
$out = preg_replace(
'/(\$rcmail_config\[\''.preg_quote($prop).'\'\])\s+=\s+(.+);/Uie',
"'\\1 = ' . rcube_install::_dump_var(\$value, \$prop) . ';'",
$out);
}
return trim($out);
}
/**
* Check the current configuration for missing properties
* and deprecated or obsolete settings
*
* @return array List with problems detected
*/
function check_config()
{
$this->config = array();
$this->load_defaults();
$defaults = $this->config;
$this->load_config();
if (!$this->configured)
return null;
$out = $seen = array();
$required = array_flip($this->required_config);
// iterate over the current configuration
foreach ($this->config as $prop => $value) {
if ($replacement = $this->replaced_config[$prop]) {
$out['replaced'][] = array('prop' => $prop, 'replacement' => $replacement);
$seen[$replacement] = true;
}
else if (!$seen[$prop] && in_array($prop, $this->obsolete_config)) {
$out['obsolete'][] = array('prop' => $prop);
$seen[$prop] = true;
}
}
// the old default mime_magic reference is obsolete
if ($this->config['mime_magic'] == '/usr/share/misc/magic') {
$out['obsolete'][] = array('prop' => 'mime_magic', 'explain' => "Set value to null in order to use system default");
}
// iterate over default config
foreach ($defaults as $prop => $value) {
if (!isset($seen[$prop]) && isset($required[$prop]) && !(is_bool($this->config[$prop]) || strlen($this->config[$prop])))
$out['missing'][] = array('prop' => $prop);
}
// check config dependencies and contradictions
if ($this->config['enable_spellcheck'] && $this->config['spellcheck_engine'] == 'pspell') {
if (!extension_loaded('pspell')) {
$out['dependencies'][] = array('prop' => 'spellcheck_engine',
'explain' => 'This requires the <tt>pspell</tt> extension which could not be loaded.');
}
else if (!empty($this->config['spellcheck_languages'])) {
foreach ($this->config['spellcheck_languages'] as $lang => $descr)
if (!@pspell_new($lang))
$out['dependencies'][] = array('prop' => 'spellcheck_languages',
'explain' => "You are missing pspell support for language $lang ($descr)");
}
}
if ($this->config['log_driver'] == 'syslog') {
if (!function_exists('openlog')) {
$out['dependencies'][] = array('prop' => 'log_driver',
'explain' => 'This requires the <tt>sylog</tt> extension which could not be loaded.');
}
if (empty($this->config['syslog_id'])) {
$out['dependencies'][] = array('prop' => 'syslog_id',
'explain' => 'Using <tt>syslog</tt> for logging requires a syslog ID to be configured');
}
}
// check ldap_public sources having global_search enabled
if (is_array($this->config['ldap_public']) && !is_array($this->config['autocomplete_addressbooks'])) {
foreach ($this->config['ldap_public'] as $ldap_public) {
if ($ldap_public['global_search']) {
$out['replaced'][] = array('prop' => 'ldap_public::global_search', 'replacement' => 'autocomplete_addressbooks');
break;
}
}
}
return $out;
}
/**
* Merge the current configuration with the defaults
* and copy replaced values to the new options.
*/
function merge_config()
{
$current = $this->config;
$this->config = array();
$this->load_defaults();
foreach ($this->replaced_config as $prop => $replacement) {
if (isset($current[$prop])) {
if ($prop == 'skin_path')
$this->config[$replacement] = preg_replace('#skins/(\w+)/?$#', '\\1', $current[$prop]);
else if ($prop == 'multiple_identities')
$this->config[$replacement] = $current[$prop] ? 2 : 0;
else
$this->config[$replacement] = $current[$prop];
}
unset($current[$prop]);
}
foreach ($this->obsolete_config as $prop) {
unset($current[$prop]);
}
// add all ldap_public sources having global_search enabled to autocomplete_addressbooks
if (is_array($current['ldap_public'])) {
foreach ($current['ldap_public'] as $key => $ldap_public) {
if ($ldap_public['global_search']) {
$this->config['autocomplete_addressbooks'][] = $key;
unset($current['ldap_public'][$key]['global_search']);
}
}
}
$this->config = array_merge($this->config, $current);
foreach ((array)$current['ldap_public'] as $key => $values) {
$this->config['ldap_public'][$key] = $current['ldap_public'][$key];
}
}
/**
* Compare the local database schema with the reference schema
* required for this version of Roundcube
*
* @param boolean True if the schema schould be updated
* @return boolean True if the schema is up-to-date, false if not or an error occured
*/
function db_schema_check($DB, $update = false)
{
if (!$this->configured)
return false;
// read reference schema from mysql.initial.sql
$db_schema = $this->db_read_schema(INSTALL_PATH . 'SQL/mysql.initial.sql');
$errors = array();
// check list of tables
$existing_tables = $DB->list_tables();
foreach ($db_schema as $table => $cols) {
$table = !empty($this->config['db_table_'.$table]) ? $this->config['db_table_'.$table] : $table;
if (!in_array($table, $existing_tables)) {
$errors[] = "Missing table '".$table."'";
}
else { // compare cols
$db_cols = $DB->list_cols($table);
$diff = array_diff(array_keys($cols), $db_cols);
if (!empty($diff))
$errors[] = "Missing columns in table '$table': " . join(',', $diff);
}
}
return !empty($errors) ? $errors : false;
}
/**
* Utility function to read database schema from an .sql file
*/
private function db_read_schema($schemafile)
{
$lines = file($schemafile);
$table_block = false;
$schema = array();
foreach ($lines as $line) {
if (preg_match('/^\s*create table `?([a-z0-9_]+)`?/i', $line, $m)) {
$table_block = $m[1];
}
else if ($table_block && preg_match('/^\s*`?([a-z0-9_-]+)`?\s+([a-z]+)/', $line, $m)) {
$col = $m[1];
if (!in_array(strtoupper($col), array('PRIMARY','KEY','INDEX','UNIQUE','CONSTRAINT','REFERENCES','FOREIGN'))) {
$schema[$table_block][$col] = $m[2];
}
}
}
return $schema;
}
/**
* Getter for the last error message
*
* @return string Error message or null if none exists
*/
function get_error()
{
return $this->last_error['message'];
}
/**
* Return a list with all imap hosts configured
*
* @return array Clean list with imap hosts
*/
function get_hostlist()
{
$default_hosts = (array)$this->getprop('default_host');
$out = array();
foreach ($default_hosts as $key => $name) {
if (!empty($name))
$out[] = rcube_parse_host(is_numeric($key) ? $name : $key);
}
return $out;
}
/**
* Create a HTML dropdown to select a previous version of Roundcube
*/
function versions_select($attrib = array())
{
$select = new html_select($attrib);
$select->add(array(
'0.1-stable', '0.1.1',
'0.2-alpha', '0.2-beta', '0.2-stable',
'0.3-stable', '0.3.1',
'0.4-beta', '0.4.2',
'0.5-beta', '0.5', '0.5.1',
'0.6-beta', '0.6',
'0.7-beta', '0.7', '0.7.1', '0.7.2', '0.7.3',
'0.8-beta', '0.8-rc', '0.8.0', '0.8.1', '0.8.2', '0.8.3', '0.8.4',
'0.9-beta',
));
return $select;
}
/**
* Return a list with available subfolders of the skin directory
*/
function list_skins()
{
$skins = array();
$skindir = INSTALL_PATH . 'skins/';
foreach (glob($skindir . '*') as $path) {
if (is_dir($path) && is_readable($path)) {
$skins[] = substr($path, strlen($skindir));
}
}
return $skins;
}
/**
* Display OK status
*
* @param string Test name
* @param string Confirm message
*/
function pass($name, $message = '')
{
echo Q($name) . ':&nbsp; <span class="success">OK</span>';
$this->_showhint($message);
}
/**
* Display an error status and increase failure count
*
* @param string Test name
* @param string Error message
* @param string URL for details
*/
function fail($name, $message = '', $url = '')
{
$this->failures++;
echo Q($name) . ':&nbsp; <span class="fail">NOT OK</span>';
$this->_showhint($message, $url);
}
/**
* Display an error status for optional settings/features
*
* @param string Test name
* @param string Error message
* @param string URL for details
*/
function optfail($name, $message = '', $url = '')
{
echo Q($name) . ':&nbsp; <span class="na">NOT OK</span>';
$this->_showhint($message, $url);
}
/**
* Display warning status
*
* @param string Test name
* @param string Warning message
* @param string URL for details
*/
function na($name, $message = '', $url = '')
{
echo Q($name) . ':&nbsp; <span class="na">NOT AVAILABLE</span>';
$this->_showhint($message, $url);
}
function _showhint($message, $url = '')
{
$hint = Q($message);
if ($url)
$hint .= ($hint ? '; ' : '') . 'See <a href="' . Q($url) . '" target="_blank">' . Q($url) . '</a>';
if ($hint)
echo '<span class="indent">(' . $hint . ')</span>';
}
static function _clean_array($arr)
{
$out = array();
foreach (array_unique($arr) as $k => $val) {
if (!empty($val)) {
if (is_numeric($k))
$out[] = $val;
else
$out[$k] = $val;
}
}
return $out;
}
static function _dump_var($var, $name=null) {
// special values
switch ($name) {
case 'syslog_facility':
$list = array(32 => 'LOG_AUTH', 80 => 'LOG_AUTHPRIV', 72 => ' LOG_CRON',
24 => 'LOG_DAEMON', 0 => 'LOG_KERN', 128 => 'LOG_LOCAL0',
136 => 'LOG_LOCAL1', 144 => 'LOG_LOCAL2', 152 => 'LOG_LOCAL3',
160 => 'LOG_LOCAL4', 168 => 'LOG_LOCAL5', 176 => 'LOG_LOCAL6',
184 => 'LOG_LOCAL7', 48 => 'LOG_LPR', 16 => 'LOG_MAIL',
56 => 'LOG_NEWS', 40 => 'LOG_SYSLOG', 8 => 'LOG_USER', 64 => 'LOG_UUCP');
if ($val = $list[$var])
return $val;
break;
}
if (is_array($var)) {
if (empty($var)) {
return 'array()';
}
else { // check if all keys are numeric
$isnum = true;
foreach ($var as $key => $value) {
if (!is_numeric($key)) {
$isnum = false;
break;
}
}
if ($isnum)
return 'array(' . join(', ', array_map(array('rcube_install', '_dump_var'), $var)) . ')';
}
}
return var_export($var, true);
}
/**
* Initialize the database with the according schema
*
* @param object rcube_db Database connection
* @return boolen True on success, False on error
*/
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";
if ($sql = @file_get_contents($fname)) {
$this->exec_sql($sql, $DB);
}
else {
$this->fail('DB Schema', "Cannot read the schema file: $fname");
return false;
}
if ($err = $this->get_error()) {
$this->fail('DB Schema', "Error creating database schema: $err");
return false;
}
return true;
}
/**
- * 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;
-
- // 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;
- }
+ system(INSTALL_PATH . "bin/updatedb.sh --package=roundcube --version=" . $version
+ . " --dir=" . INSTALL_PATH . "SQL", $result);
- return true;
+ return !$result;
}
/**
* Execute the given SQL queries on the database connection
*
* @param string SQL queries to execute
* @param object rcube_db Database connection
* @return boolen True on success, False on error
*/
function exec_sql($sql, $DB)
{
$buff = '';
foreach (explode("\n", $sql) as $line) {
if (preg_match('/^--/', $line) || trim($line) == '')
continue;
$buff .= $line . "\n";
if (preg_match('/(;|^GO)$/', trim($line))) {
$DB->query($buff);
$buff = '';
if ($DB->is_error())
break;
}
}
return !$DB->is_error();
}
/**
* Handler for Roundcube errors
*/
function raise_error($p)
{
$this->last_error = $p;
}
/**
* Generarte a ramdom string to be used as encryption key
*
* @param int Key length
* @return string The generated random string
* @static
*/
function random_key($length)
{
$alpha = 'ABCDEFGHIJKLMNOPQERSTUVXYZabcdefghijklmnopqrtsuvwxyz0123456789+*%&?!$-_=';
$out = '';
for ($i=0; $i < $length; $i++)
$out .= $alpha{rand(0, strlen($alpha)-1)};
return $out;
}
}
diff --git a/installer/test.php b/installer/test.php
index b118924e4..bd49ac523 100644
--- a/installer/test.php
+++ b/installer/test.php
@@ -1,439 +1,436 @@
<?php
if (!class_exists('rcube_install') || !is_object($RCI)) {
die("Not allowed! Please open installer/index.php instead.");
}
?>
<form action="index.php?_step=3" method="post">
<h3>Check config files</h3>
<?php
$read_main = is_readable(RCUBE_CONFIG_DIR . 'main.inc.php');
$read_db = is_readable(RCUBE_CONFIG_DIR . 'db.inc.php');
if ($read_main && !empty($RCI->config)) {
$RCI->pass('main.inc.php');
}
else if ($read_main) {
$RCI->fail('main.inc.php', 'Syntax error');
}
else if (!$read_main) {
$RCI->fail('main.inc.php', 'Unable to read file. Did you create the config files?');
}
echo '<br />';
if ($read_db && !empty($RCI->config['db_table_users'])) {
$RCI->pass('db.inc.php');
}
else if ($read_db) {
$RCI->fail('db.inc.php', 'Syntax error');
}
else if (!$read_db) {
$RCI->fail('db.inc.php', 'Unable to read file. Did you create the config files?');
}
if ($RCI->configured && ($messages = $RCI->check_config())) {
if (is_array($messages['missing'])) {
echo '<h3 class="warning">Missing config options</h3>';
echo '<p class="hint">The following config options are not present in the current configuration.<br/>';
echo 'Please check the default config files and add the missing properties to your local config files.</p>';
echo '<ul class="configwarings">';
foreach ($messages['missing'] as $msg) {
echo html::tag('li', null, html::span('propname', $msg['prop']) . ($msg['name'] ? ':&nbsp;' . $msg['name'] : ''));
}
echo '</ul>';
}
if (is_array($messages['replaced'])) {
echo '<h3 class="warning">Replaced config options</h3>';
echo '<p class="hint">The following config options have been replaced or renamed. ';
echo 'Please update them accordingly in your config files.</p>';
echo '<ul class="configwarings">';
foreach ($messages['replaced'] as $msg) {
echo html::tag('li', null, html::span('propname', $msg['prop']) .
' was replaced by ' . html::span('propname', $msg['replacement']));
}
echo '</ul>';
}
if (is_array($messages['obsolete'])) {
echo '<h3>Obsolete config options</h3>';
echo '<p class="hint">You still have some obsolete or inexistent properties set. This isn\'t a problem but should be noticed.</p>';
echo '<ul class="configwarings">';
foreach ($messages['obsolete'] as $msg) {
echo html::tag('li', null, html::span('propname', $msg['prop']) . ($msg['name'] ? ':&nbsp;' . $msg['name'] : ''));
}
echo '</ul>';
}
echo '<p class="suggestion">OK, lazy people can download the updated config files here: ';
echo html::a(array('href' => './?_mergeconfig=main'), 'main.inc.php') . ' &nbsp;';
echo html::a(array('href' => './?_mergeconfig=db'), 'db.inc.php');
echo "</p>";
if (is_array($messages['dependencies'])) {
echo '<h3 class="warning">Dependency check failed</h3>';
echo '<p class="hint">Some of your configuration settings require other options to be configured or additional PHP modules to be installed</p>';
echo '<ul class="configwarings">';
foreach ($messages['dependencies'] as $msg) {
echo html::tag('li', null, html::span('propname', $msg['prop']) . ': ' . $msg['explain']);
}
echo '</ul>';
}
}
?>
<h3>Check if directories are writable</h3>
<p>Roundcube may need to write/save files into these directories</p>
<?php
if ($RCI->configured) {
$pass = false;
$dirs[] = $RCI->config['temp_dir'] ? $RCI->config['temp_dir'] : 'temp';
if($RCI->config['log_driver'] != 'syslog')
$dirs[] = $RCI->config['log_dir'] ? $RCI->config['log_dir'] : 'logs';
foreach ($dirs as $dir) {
$dirpath = $dir[0] == '/' ? $dir : INSTALL_PATH . $dir;
if (is_writable(realpath($dirpath))) {
$RCI->pass($dir);
$pass = true;
}
else {
$RCI->fail($dir, 'not writeable for the webserver');
}
echo '<br />';
}
if (!$pass)
echo '<p class="hint">Use <tt>chmod</tt> or <tt>chown</tt> to grant write privileges to the webserver</p>';
}
else {
$RCI->fail('Config', 'Could not read config files');
}
?>
<h3>Check DB config</h3>
<?php
$db_working = false;
if ($RCI->configured) {
if (!empty($RCI->config['db_dsnw'])) {
$DB = rcube_db::factory($RCI->config['db_dsnw'], '', false);
$DB->db_connect('w');
if (!($db_error_msg = $DB->is_error())) {
$RCI->pass('DSN (write)');
echo '<br />';
$db_working = true;
}
else {
$RCI->fail('DSN (write)', $db_error_msg);
echo '<p class="hint">Make sure that the configured database exists and that the user has write privileges<br />';
echo 'DSN: ' . $RCI->config['db_dsnw'] . '</p>';
}
}
else {
$RCI->fail('DSN (write)', 'not set');
}
}
else {
$RCI->fail('Config', 'Could not read config files');
}
// initialize db with schema found in /SQL/*
if ($db_working && $_POST['initdb']) {
if (!($success = $RCI->init_db($DB))) {
$db_working = false;
echo '<p class="warning">Please try to inizialize the database manually as described in the INSTALL guide.
Make sure that the configured database extists and that the user as write privileges</p>';
}
}
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
if ($db_working) {
$db_read = $DB->query("SELECT count(*) FROM {$RCI->config['db_table_users']}");
if ($DB->is_error()) {
$RCI->fail('DB Schema', "Database not initialized");
echo '<p><input type="submit" name="initdb" value="Initialize database" /></p>';
$db_working = false;
}
else if ($err = $RCI->db_schema_check($DB, $update = !empty($_POST['updatedb']))) {
$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() . '&nbsp;<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() . '&nbsp;<input type="submit" name="updatedb" value="Update" /></p>';
$db_working = false;
}
else {
$RCI->pass('DB Schema');
echo '<br />';
}
}
// more database tests
if ($db_working) {
// write test
$insert_id = md5(uniqid());
$db_write = $DB->query("INSERT INTO {$RCI->config['db_table_session']} (sess_id, created, ip, vars) VALUES (?, ".$DB->now().", '127.0.0.1', 'foo')", $insert_id);
if ($db_write) {
$RCI->pass('DB Write');
$DB->query("DELETE FROM {$RCI->config['db_table_session']} WHERE sess_id=?", $insert_id);
}
else {
$RCI->fail('DB Write', $RCI->get_error());
}
echo '<br />';
// check timezone settings
$tz_db = 'SELECT ' . $DB->unixtimestamp($DB->now()) . ' AS tz_db';
$tz_db = $DB->query($tz_db);
$tz_db = $DB->fetch_assoc($tz_db);
$tz_db = (int) $tz_db['tz_db'];
$tz_local = (int) time();
$tz_diff = $tz_local - $tz_db;
// sometimes db and web servers are on separate hosts, so allow a 30 minutes delta
if (abs($tz_diff) > 1800) {
$RCI->fail('DB Time', "Database time differs {$td_ziff}s from PHP time");
}
else {
$RCI->pass('DB Time');
}
}
?>
<h3>Test SMTP config</h3>
<p>
Server: <?php echo rcube_parse_host($RCI->getprop('smtp_server', 'PHP mail()')); ?><br />
Port: <?php echo $RCI->getprop('smtp_port'); ?><br />
<?php
if ($RCI->getprop('smtp_server')) {
$user = $RCI->getprop('smtp_user', '(none)');
$pass = $RCI->getprop('smtp_pass', '(none)');
if ($user == '%u') {
$user_field = new html_inputfield(array('name' => '_smtp_user'));
$user = $user_field->show($_POST['_smtp_user']);
}
if ($pass == '%p') {
$pass_field = new html_passwordfield(array('name' => '_smtp_pass'));
$pass = $pass_field->show();
}
echo "User: $user<br />";
echo "Password: $pass<br />";
}
$from_field = new html_inputfield(array('name' => '_from', 'id' => 'sendmailfrom'));
$to_field = new html_inputfield(array('name' => '_to', 'id' => 'sendmailto'));
?>
</p>
<?php
if (isset($_POST['sendmail'])) {
echo '<p>Trying to send email...<br />';
$from = idn_to_ascii(trim($_POST['_from']));
$to = idn_to_ascii(trim($_POST['_to']));
if (preg_match('/^' . $RCI->email_pattern . '$/i', $from) &&
preg_match('/^' . $RCI->email_pattern . '$/i', $to)
) {
$headers = array(
'From' => $from,
'To' => $to,
'Subject' => 'Test message from Roundcube',
);
$body = 'This is a test to confirm that Roundcube can send email.';
$smtp_response = array();
// send mail using configured SMTP server
if ($RCI->getprop('smtp_server')) {
$CONFIG = $RCI->config;
if (!empty($_POST['_smtp_user'])) {
$CONFIG['smtp_user'] = $_POST['_smtp_user'];
}
if (!empty($_POST['_smtp_pass'])) {
$CONFIG['smtp_pass'] = $_POST['_smtp_pass'];
}
$mail_object = new Mail_mime();
$send_headers = $mail_object->headers($headers);
$SMTP = new rcube_smtp();
$SMTP->connect(rcube_parse_host($RCI->getprop('smtp_server')),
$RCI->getprop('smtp_port'), $CONFIG['smtp_user'], $CONFIG['smtp_pass']);
$status = $SMTP->send_mail($headers['From'], $headers['To'],
($foo = $mail_object->txtHeaders($send_headers)), $body);
$smtp_response = $SMTP->get_response();
}
else { // use mail()
$header_str = 'From: ' . $headers['From'];
if (ini_get('safe_mode'))
$status = mail($headers['To'], $headers['Subject'], $body, $header_str);
else
$status = mail($headers['To'], $headers['Subject'], $body, $header_str, '-f'.$headers['From']);
if (!$status)
$smtp_response[] = 'Mail delivery with mail() failed. Check your error logs for details';
}
if ($status) {
$RCI->pass('SMTP send');
}
else {
$RCI->fail('SMTP send', join('; ', $smtp_response));
}
}
else {
$RCI->fail('SMTP send', 'Invalid sender or recipient');
}
echo '</p>';
}
?>
<table>
<tbody>
<tr>
<td><label for="sendmailfrom">Sender</label></td>
<td><?php echo $from_field->show($_POST['_from']); ?></td>
</tr>
<tr>
<td><label for="sendmailto">Recipient</label></td>
<td><?php echo $to_field->show($_POST['_to']); ?></td>
</tr>
</tbody>
</table>
<p><input type="submit" name="sendmail" value="Send test mail" /></p>
<h3>Test IMAP config</h3>
<?php
$default_hosts = $RCI->get_hostlist();
if (!empty($default_hosts)) {
$host_field = new html_select(array('name' => '_host', 'id' => 'imaphost'));
$host_field->add($default_hosts);
}
else {
$host_field = new html_inputfield(array('name' => '_host', 'id' => 'imaphost'));
}
$user_field = new html_inputfield(array('name' => '_user', 'id' => 'imapuser'));
$pass_field = new html_passwordfield(array('name' => '_pass', 'id' => 'imappass'));
?>
<table>
<tbody>
<tr>
<td><label for="imaphost">Server</label></td>
<td><?php echo $host_field->show($_POST['_host']); ?></td>
</tr>
<tr>
<td>Port</td>
<td><?php echo $RCI->getprop('default_port'); ?></td>
</tr>
<tr>
<td><label for="imapuser">Username</label></td>
<td><?php echo $user_field->show($_POST['_user']); ?></td>
</tr>
<tr>
<td><label for="imappass">Password</label></td>
<td><?php echo $pass_field->show(); ?></td>
</tr>
</tbody>
</table>
<?php
if (isset($_POST['imaptest']) && !empty($_POST['_host']) && !empty($_POST['_user'])) {
echo '<p>Connecting to ' . Q($_POST['_host']) . '...<br />';
$imap_host = trim($_POST['_host']);
$imap_port = $RCI->getprop('default_port');
$a_host = parse_url($imap_host);
if ($a_host['host']) {
$imap_host = $a_host['host'];
$imap_ssl = (isset($a_host['scheme']) && in_array($a_host['scheme'], array('ssl','imaps','tls'))) ? $a_host['scheme'] : null;
if (isset($a_host['port']))
$imap_port = $a_host['port'];
else if ($imap_ssl && $imap_ssl != 'tls' && (!$imap_port || $imap_port == 143))
$imap_port = 993;
}
$imap_host = idn_to_ascii($imap_host);
$imap_user = idn_to_ascii($_POST['_user']);
$imap = new rcube_imap(null);
if ($imap->connect($imap_host, $imap_user, $_POST['_pass'], $imap_port, $imap_ssl)) {
$RCI->pass('IMAP connect', 'SORT capability: ' . ($imap->get_capability('SORT') ? 'yes' : 'no'));
$imap->close();
}
else {
$RCI->fail('IMAP connect', $RCI->get_error());
}
}
?>
<p><input type="submit" name="imaptest" value="Check login" /></p>
</form>
<hr />
<p class="warning">
After completing the installation and the final tests please <b>remove</b> the whole
installer folder from the document root of the webserver or make sure that
<tt>enable_installer</tt> option in config/main.inc.php is disabled.<br />
<br />
These files may expose sensitive configuration data like server passwords and encryption keys
to the public. Make sure you cannot access this installer from your browser.
</p>

File Metadata

Mime Type
text/x-diff
Expires
Sat, Mar 1, 7:03 AM (1 d, 8 h)
Storage Engine
blob
Storage Format
Raw Data
Storage Handle
166193
Default Alt Text
(172 KB)

Event Timeline