diff options
63 files changed, 1325 insertions, 1157 deletions
diff --git a/SQL/mssql.initial.sql b/SQL/mssql.initial.sql index 85b8e4ef6..e312cfbcd 100644 --- a/SQL/mssql.initial.sql +++ b/SQL/mssql.initial.sql @@ -115,6 +115,12 @@ CREATE TABLE [dbo].[searches] ( ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
+CREATE TABLE [dbo].[system] (
+ [name] [varchar] (64) COLLATE Latin1_General_CI_AI NOT NULL ,
+ [value] [text] COLLATE Latin1_General_CI_AI NOT NULL
+) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
+GO
+
ALTER TABLE [dbo].[cache_index] WITH NOCHECK ADD
PRIMARY KEY CLUSTERED
(
@@ -185,6 +191,13 @@ ALTER TABLE [dbo].[searches] WITH NOCHECK ADD ) ON [PRIMARY]
GO
+ALTER TABLE [dbo].[system] WITH NOCHECK ADD
+ CONSTRAINT [PK_system_name] PRIMARY KEY CLUSTERED
+ (
+ [name]
+ ) ON [PRIMARY]
+GO
+
ALTER TABLE [dbo].[cache] ADD
CONSTRAINT [DF_cache_user_id] DEFAULT ('0') FOR [user_id],
CONSTRAINT [DF_cache_cache_key] DEFAULT ('') FOR [cache_key],
@@ -358,3 +371,6 @@ CREATE TRIGGER [contact_delete_member] ON [dbo].[contacts] WHERE [contact_id] IN (SELECT [contact_id] FROM deleted)
GO
+INSERT INTO [dbo].[system] ([name], [value]) VALUES ('roundcube-version', '2013011000.sql')
+GO
+
\ No newline at end of file diff --git a/SQL/mssql.upgrade.sql b/SQL/mssql.upgrade.sql deleted file mode 100644 index 26001e713..000000000 --- a/SQL/mssql.upgrade.sql +++ /dev/null @@ -1,273 +0,0 @@ --- Roundcube Webmail update script for MSSQL databases
-
--- Updates from version 0.3.1
-
-ALTER TABLE [dbo].[messages] ADD CONSTRAINT [FK_messages_user_id]
- FOREIGN KEY ([user_id]) REFERENCES [dbo].[users] ([user_id])
- ON DELETE CASCADE ON UPDATE CASCADE
-GO
-
-ALTER TABLE [dbo].[cache] ADD CONSTRAINT [FK_cache_user_id]
- FOREIGN KEY ([user_id]) REFERENCES [dbo].[users] ([user_id])
- ON DELETE CASCADE ON UPDATE CASCADE
-GO
-
-ALTER TABLE [dbo].[contacts] ADD CONSTRAINT [FK_contacts_user_id]
- FOREIGN KEY ([user_id]) REFERENCES [dbo].[users] ([user_id])
- ON DELETE CASCADE ON UPDATE CASCADE
-GO
-
-ALTER TABLE [dbo].[identities] ADD CONSTRAINT [FK_identities_user_id]
- FOREIGN KEY ([user_id]) REFERENCES [dbo].[users] ([user_id])
- ON DELETE CASCADE ON UPDATE CASCADE
-GO
-
-ALTER TABLE [dbo].[identities] ADD [changed] [datetime] NULL
-GO
-
-CREATE TABLE [dbo].[contactgroups] (
- [contactgroup_id] [int] IDENTITY (1, 1) NOT NULL ,
- [user_id] [int] NOT NULL ,
- [changed] [datetime] NOT NULL ,
- [del] [char] (1) COLLATE Latin1_General_CI_AI NOT NULL ,
- [name] [varchar] (128) COLLATE Latin1_General_CI_AI NOT NULL
-) ON [PRIMARY]
-GO
-
-CREATE TABLE [dbo].[contactgroupmembers] (
- [contactgroup_id] [int] NOT NULL ,
- [contact_id] [int] NOT NULL ,
- [created] [datetime] NOT NULL
-) ON [PRIMARY]
-GO
-
-ALTER TABLE [dbo].[contactgroups] WITH NOCHECK ADD
- CONSTRAINT [PK_contactgroups_contactgroup_id] PRIMARY KEY CLUSTERED
- (
- [contactgroup_id]
- ) ON [PRIMARY]
-GO
-
-ALTER TABLE [dbo].[contactgroupmembers] WITH NOCHECK ADD
- CONSTRAINT [PK_contactgroupmembers_id] PRIMARY KEY CLUSTERED
- (
- [contactgroup_id], [contact_id]
- ) ON [PRIMARY]
-GO
-
-ALTER TABLE [dbo].[contactgroups] ADD
- CONSTRAINT [DF_contactgroups_user_id] DEFAULT (0) FOR [user_id],
- CONSTRAINT [DF_contactgroups_changed] DEFAULT (getdate()) FOR [changed],
- CONSTRAINT [DF_contactgroups_del] DEFAULT ('0') FOR [del],
- CONSTRAINT [DF_contactgroups_name] DEFAULT ('') FOR [name],
- CONSTRAINT [CK_contactgroups_del] CHECK ([del] = '1' or [del] = '0')
-GO
-
-CREATE INDEX [IX_contactgroups_user_id] ON [dbo].[contacts]([user_id]) ON [PRIMARY]
-GO
-
-ALTER TABLE [dbo].[contactgroupmembers] ADD
- CONSTRAINT [DF_contactgroupmembers_contactgroup_id] DEFAULT (0) FOR [contactgroup_id],
- CONSTRAINT [DF_contactgroupmembers_contact_id] DEFAULT (0) FOR [contact_id],
- CONSTRAINT [DF_contactgroupmembers_created] DEFAULT (getdate()) FOR [created]
-GO
-
-ALTER TABLE [dbo].[contactgroupmembers] ADD CONSTRAINT [FK_contactgroupmembers_contactgroup_id]
- FOREIGN KEY ([contactgroup_id]) REFERENCES [dbo].[contactgroups] ([contactgroup_id])
- ON DELETE CASCADE ON UPDATE CASCADE
-GO
-
-CREATE TRIGGER [contact_delete_member] ON [dbo].[contacts]
- AFTER DELETE AS
- DELETE FROM [dbo].[contactgroupmembers]
- WHERE [contact_id] IN (SELECT [contact_id] FROM deleted)
-GO
-
-ALTER TABLE [dbo].[contactgroups] ADD CONSTRAINT [FK_contactgroups_user_id]
- FOREIGN KEY ([user_id]) REFERENCES [dbo].[users] ([user_id])
- ON DELETE CASCADE ON UPDATE CASCADE
-GO
-
--- Updates from version 0.4.2
-
-DROP INDEX [IX_users_username]
-GO
-CREATE UNIQUE INDEX [IX_users_username] ON [dbo].[users]([username],[mail_host]) ON [PRIMARY]
-GO
-ALTER TABLE [dbo].[contacts] ALTER COLUMN [email] [varchar] (255) COLLATE Latin1_General_CI_AI NOT NULL
-GO
-
--- Updates from version 0.5.1
--- Updates from version 0.5.2
--- Updates from version 0.5.3
--- Updates from version 0.5.4
-
-ALTER TABLE [dbo].[contacts] ADD [words] [text] COLLATE Latin1_General_CI_AI NULL
-GO
-CREATE INDEX [IX_contactgroupmembers_contact_id] ON [dbo].[contactgroupmembers]([contact_id]) ON [PRIMARY]
-GO
-DELETE FROM [dbo].[messages]
-GO
-DELETE FROM [dbo].[cache]
-GO
-
--- Updates from version 0.6
-
-CREATE TABLE [dbo].[dictionary] (
- [user_id] [int] ,
- [language] [varchar] (5) COLLATE Latin1_General_CI_AI NOT NULL ,
- [data] [text] COLLATE Latin1_General_CI_AI NOT NULL
-) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
-GO
-CREATE UNIQUE INDEX [IX_dictionary_user_language] ON [dbo].[dictionary]([user_id],[language]) ON [PRIMARY]
-GO
-
-CREATE TABLE [dbo].[searches] (
- [search_id] [int] IDENTITY (1, 1) NOT NULL ,
- [user_id] [int] NOT NULL ,
- [type] [tinyint] NOT NULL ,
- [name] [varchar] (128) COLLATE Latin1_General_CI_AI NOT NULL ,
- [data] [text] COLLATE Latin1_General_CI_AI NOT NULL
-) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
-GO
-
-ALTER TABLE [dbo].[searches] WITH NOCHECK ADD
- CONSTRAINT [PK_searches_search_id] PRIMARY KEY CLUSTERED
- (
- [search_id]
- ) ON [PRIMARY]
-GO
-
-ALTER TABLE [dbo].[searches] ADD
- CONSTRAINT [DF_searches_user] DEFAULT (0) FOR [user_id],
- CONSTRAINT [DF_searches_type] DEFAULT (0) FOR [type],
-GO
-
-CREATE UNIQUE INDEX [IX_searches_user_type_name] ON [dbo].[searches]([user_id],[type],[name]) ON [PRIMARY]
-GO
-
-ALTER TABLE [dbo].[searches] ADD CONSTRAINT [FK_searches_user_id]
- FOREIGN KEY ([user_id]) REFERENCES [dbo].[users] ([user_id])
- ON DELETE CASCADE ON UPDATE CASCADE
-GO
-
-DROP TABLE [dbo].[messages]
-GO
-CREATE TABLE [dbo].[cache_index] (
- [user_id] [int] NOT NULL ,
- [mailbox] [varchar] (128) COLLATE Latin1_General_CI_AI NOT NULL ,
- [changed] [datetime] NOT NULL ,
- [valid] [char] (1) COLLATE Latin1_General_CI_AI NOT NULL ,
- [data] [text] COLLATE Latin1_General_CI_AI NOT NULL
-) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
-GO
-
-CREATE TABLE [dbo].[cache_thread] (
- [user_id] [int] NOT NULL ,
- [mailbox] [varchar] (128) COLLATE Latin1_General_CI_AI NOT NULL ,
- [changed] [datetime] NOT NULL ,
- [data] [text] COLLATE Latin1_General_CI_AI NOT NULL
-) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
-GO
-
-CREATE TABLE [dbo].[cache_messages] (
- [user_id] [int] NOT NULL ,
- [mailbox] [varchar] (128) COLLATE Latin1_General_CI_AI NOT NULL ,
- [uid] [int] NOT NULL ,
- [changed] [datetime] NOT NULL ,
- [data] [text] COLLATE Latin1_General_CI_AI NOT NULL ,
- [flags] [int] NOT NULL
-) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
-GO
-
-ALTER TABLE [dbo].[cache_index] WITH NOCHECK ADD
- PRIMARY KEY CLUSTERED
- (
- [user_id],[mailbox]
- ) ON [PRIMARY]
-GO
-
-ALTER TABLE [dbo].[cache_thread] WITH NOCHECK ADD
- PRIMARY KEY CLUSTERED
- (
- [user_id],[mailbox]
- ) ON [PRIMARY]
-GO
-
-ALTER TABLE [dbo].[cache_messages] WITH NOCHECK ADD
- PRIMARY KEY CLUSTERED
- (
- [user_id],[mailbox],[uid]
- ) ON [PRIMARY]
-GO
-
-ALTER TABLE [dbo].[cache_index] ADD
- CONSTRAINT [DF_cache_index_changed] DEFAULT (getdate()) FOR [changed],
- CONSTRAINT [DF_cache_index_valid] DEFAULT ('0') FOR [valid]
-GO
-
-CREATE INDEX [IX_cache_index_user_id] ON [dbo].[cache_index]([user_id]) ON [PRIMARY]
-GO
-
-ALTER TABLE [dbo].[cache_thread] ADD
- CONSTRAINT [DF_cache_thread_changed] DEFAULT (getdate()) FOR [changed]
-GO
-
-CREATE INDEX [IX_cache_thread_user_id] ON [dbo].[cache_thread]([user_id]) ON [PRIMARY]
-GO
-
-ALTER TABLE [dbo].[cache_messages] ADD
- CONSTRAINT [DF_cache_messages_changed] DEFAULT (getdate()) FOR [changed],
- CONSTRAINT [DF_cache_messages_flags] DEFAULT (0) FOR [flags]
-GO
-
-CREATE INDEX [IX_cache_messages_user_id] ON [dbo].[cache_messages]([user_id]) ON [PRIMARY]
-GO
-
-ALTER TABLE [dbo].[cache_index] ADD CONSTRAINT [FK_cache_index_user_id]
- FOREIGN KEY ([user_id]) REFERENCES [dbo].[users] ([user_id])
- ON DELETE CASCADE ON UPDATE CASCADE
-GO
-
-ALTER TABLE [dbo].[cache_thread] ADD CONSTRAINT [FK_cache_thread_user_id]
- FOREIGN KEY ([user_id]) REFERENCES [dbo].[users] ([user_id])
- ON DELETE CASCADE ON UPDATE CASCADE
-GO
-
-ALTER TABLE [dbo].[cache_messages] ADD CONSTRAINT [FK_cache_messages_user_id]
- FOREIGN KEY ([user_id]) REFERENCES [dbo].[users] ([user_id])
- ON DELETE CASCADE ON UPDATE CASCADE
-GO
-
--- Updates from version 0.7-beta
-
-ALTER TABLE [dbo].[session] ALTER COLUMN [sess_id] [varchar] (128) COLLATE Latin1_General_CI_AI NOT NULL
-GO
-
--- Updates from version 0.7
-
-ALTER TABLE [dbo].[contacts] DROP CONSTRAINT [DF_contacts_email]
-GO
-ALTER TABLE [dbo].[contacts] ALTER COLUMN [email] [text] COLLATE Latin1_General_CI_AI NOT NULL
-GO
-ALTER TABLE [dbo].[contacts] ADD CONSTRAINT [DF_contacts_email] DEFAULT ('') FOR [email]
-GO
-
--- Updates from version 0.8-rc
-
-ALTER TABLE [dbo].[contacts] DROP CONSTRAINT [DF_contacts_email]
-GO
-ALTER TABLE [dbo].[contacts] ALTER COLUMN [email] [varchar] (8000) COLLATE Latin1_General_CI_AI NOT NULL
-GO
-ALTER TABLE [dbo].[contacts] ADD CONSTRAINT [DF_contacts_email] DEFAULT ('') FOR [email]
-GO
-
--- Updates from version 0.8
-
-ALTER TABLE [dbo].[cache] DROP COLUMN [cache_id]
-GO
-ALTER TABLE [dbo].[users] DROP COLUMN [alias]
-GO
-CREATE INDEX [IX_identities_email] ON [dbo].[identities]([email],[del]) ON [PRIMARY]
-GO
-
\ No newline at end of file diff --git a/SQL/mssql/2009103100.sql b/SQL/mssql/2009103100.sql new file mode 100644 index 000000000..646fe38af --- /dev/null +++ b/SQL/mssql/2009103100.sql @@ -0,0 +1,87 @@ +-- Updates from version 0.3.1
+
+ALTER TABLE [dbo].[messages] ADD CONSTRAINT [FK_messages_user_id]
+ FOREIGN KEY ([user_id]) REFERENCES [dbo].[users] ([user_id])
+ ON DELETE CASCADE ON UPDATE CASCADE
+GO
+
+ALTER TABLE [dbo].[cache] ADD CONSTRAINT [FK_cache_user_id]
+ FOREIGN KEY ([user_id]) REFERENCES [dbo].[users] ([user_id])
+ ON DELETE CASCADE ON UPDATE CASCADE
+GO
+
+ALTER TABLE [dbo].[contacts] ADD CONSTRAINT [FK_contacts_user_id]
+ FOREIGN KEY ([user_id]) REFERENCES [dbo].[users] ([user_id])
+ ON DELETE CASCADE ON UPDATE CASCADE
+GO
+
+ALTER TABLE [dbo].[identities] ADD CONSTRAINT [FK_identities_user_id]
+ FOREIGN KEY ([user_id]) REFERENCES [dbo].[users] ([user_id])
+ ON DELETE CASCADE ON UPDATE CASCADE
+GO
+
+ALTER TABLE [dbo].[identities] ADD [changed] [datetime] NULL
+GO
+
+CREATE TABLE [dbo].[contactgroups] (
+ [contactgroup_id] [int] IDENTITY (1, 1) NOT NULL ,
+ [user_id] [int] NOT NULL ,
+ [changed] [datetime] NOT NULL ,
+ [del] [char] (1) COLLATE Latin1_General_CI_AI NOT NULL ,
+ [name] [varchar] (128) COLLATE Latin1_General_CI_AI NOT NULL
+) ON [PRIMARY]
+GO
+
+CREATE TABLE [dbo].[contactgroupmembers] (
+ [contactgroup_id] [int] NOT NULL ,
+ [contact_id] [int] NOT NULL ,
+ [created] [datetime] NOT NULL
+) ON [PRIMARY]
+GO
+
+ALTER TABLE [dbo].[contactgroups] WITH NOCHECK ADD
+ CONSTRAINT [PK_contactgroups_contactgroup_id] PRIMARY KEY CLUSTERED
+ (
+ [contactgroup_id]
+ ) ON [PRIMARY]
+GO
+
+ALTER TABLE [dbo].[contactgroupmembers] WITH NOCHECK ADD
+ CONSTRAINT [PK_contactgroupmembers_id] PRIMARY KEY CLUSTERED
+ (
+ [contactgroup_id], [contact_id]
+ ) ON [PRIMARY]
+GO
+
+ALTER TABLE [dbo].[contactgroups] ADD
+ CONSTRAINT [DF_contactgroups_user_id] DEFAULT (0) FOR [user_id],
+ CONSTRAINT [DF_contactgroups_changed] DEFAULT (getdate()) FOR [changed],
+ CONSTRAINT [DF_contactgroups_del] DEFAULT ('0') FOR [del],
+ CONSTRAINT [DF_contactgroups_name] DEFAULT ('') FOR [name],
+ CONSTRAINT [CK_contactgroups_del] CHECK ([del] = '1' or [del] = '0')
+GO
+
+CREATE INDEX [IX_contactgroups_user_id] ON [dbo].[contacts]([user_id]) ON [PRIMARY]
+GO
+
+ALTER TABLE [dbo].[contactgroupmembers] ADD
+ CONSTRAINT [DF_contactgroupmembers_contactgroup_id] DEFAULT (0) FOR [contactgroup_id],
+ CONSTRAINT [DF_contactgroupmembers_contact_id] DEFAULT (0) FOR [contact_id],
+ CONSTRAINT [DF_contactgroupmembers_created] DEFAULT (getdate()) FOR [created]
+GO
+
+ALTER TABLE [dbo].[contactgroupmembers] ADD CONSTRAINT [FK_contactgroupmembers_contactgroup_id]
+ FOREIGN KEY ([contactgroup_id]) REFERENCES [dbo].[contactgroups] ([contactgroup_id])
+ ON DELETE CASCADE ON UPDATE CASCADE
+GO
+
+CREATE TRIGGER [contact_delete_member] ON [dbo].[contacts]
+ AFTER DELETE AS
+ DELETE FROM [dbo].[contactgroupmembers]
+ WHERE [contact_id] IN (SELECT [contact_id] FROM deleted)
+GO
+
+ALTER TABLE [dbo].[contactgroups] ADD CONSTRAINT [FK_contactgroups_user_id]
+ FOREIGN KEY ([user_id]) REFERENCES [dbo].[users] ([user_id])
+ ON DELETE CASCADE ON UPDATE CASCADE
+GO
diff --git a/SQL/mssql/2010100600.sql b/SQL/mssql/2010100600.sql new file mode 100644 index 000000000..fb045e303 --- /dev/null +++ b/SQL/mssql/2010100600.sql @@ -0,0 +1,9 @@ +-- Updates from version 0.4.2
+
+DROP INDEX [IX_users_username]
+GO
+CREATE UNIQUE INDEX [IX_users_username] ON [dbo].[users]([username],[mail_host]) ON [PRIMARY]
+GO
+ALTER TABLE [dbo].[contacts] ALTER COLUMN [email] [varchar] (255) COLLATE Latin1_General_CI_AI NOT NULL
+GO
+
\ No newline at end of file diff --git a/SQL/mssql/2011011200.sql b/SQL/mssql/2011011200.sql new file mode 100644 index 000000000..8715455df --- /dev/null +++ b/SQL/mssql/2011011200.sql @@ -0,0 +1,10 @@ +-- Updates from version 0.5.x
+
+ALTER TABLE [dbo].[contacts] ADD [words] [text] COLLATE Latin1_General_CI_AI NULL
+GO
+CREATE INDEX [IX_contactgroupmembers_contact_id] ON [dbo].[contactgroupmembers]([contact_id]) ON [PRIMARY]
+GO
+DELETE FROM [dbo].[messages]
+GO
+DELETE FROM [dbo].[cache]
+GO
diff --git a/SQL/mssql/2011092800.sql b/SQL/mssql/2011092800.sql new file mode 100644 index 000000000..00f79bbc0 --- /dev/null +++ b/SQL/mssql/2011092800.sql @@ -0,0 +1,127 @@ +-- Updates from version 0.6
+
+CREATE TABLE [dbo].[dictionary] (
+ [user_id] [int] ,
+ [language] [varchar] (5) COLLATE Latin1_General_CI_AI NOT NULL ,
+ [data] [text] COLLATE Latin1_General_CI_AI NOT NULL
+) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
+GO
+CREATE UNIQUE INDEX [IX_dictionary_user_language] ON [dbo].[dictionary]([user_id],[language]) ON [PRIMARY]
+GO
+
+CREATE TABLE [dbo].[searches] (
+ [search_id] [int] IDENTITY (1, 1) NOT NULL ,
+ [user_id] [int] NOT NULL ,
+ [type] [tinyint] NOT NULL ,
+ [name] [varchar] (128) COLLATE Latin1_General_CI_AI NOT NULL ,
+ [data] [text] COLLATE Latin1_General_CI_AI NOT NULL
+) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
+GO
+
+ALTER TABLE [dbo].[searches] WITH NOCHECK ADD
+ CONSTRAINT [PK_searches_search_id] PRIMARY KEY CLUSTERED
+ (
+ [search_id]
+ ) ON [PRIMARY]
+GO
+
+ALTER TABLE [dbo].[searches] ADD
+ CONSTRAINT [DF_searches_user] DEFAULT (0) FOR [user_id],
+ CONSTRAINT [DF_searches_type] DEFAULT (0) FOR [type],
+GO
+
+CREATE UNIQUE INDEX [IX_searches_user_type_name] ON [dbo].[searches]([user_id],[type],[name]) ON [PRIMARY]
+GO
+
+ALTER TABLE [dbo].[searches] ADD CONSTRAINT [FK_searches_user_id]
+ FOREIGN KEY ([user_id]) REFERENCES [dbo].[users] ([user_id])
+ ON DELETE CASCADE ON UPDATE CASCADE
+GO
+
+DROP TABLE [dbo].[messages]
+GO
+CREATE TABLE [dbo].[cache_index] (
+ [user_id] [int] NOT NULL ,
+ [mailbox] [varchar] (128) COLLATE Latin1_General_CI_AI NOT NULL ,
+ [changed] [datetime] NOT NULL ,
+ [valid] [char] (1) COLLATE Latin1_General_CI_AI NOT NULL ,
+ [data] [text] COLLATE Latin1_General_CI_AI NOT NULL
+) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
+GO
+
+CREATE TABLE [dbo].[cache_thread] (
+ [user_id] [int] NOT NULL ,
+ [mailbox] [varchar] (128) COLLATE Latin1_General_CI_AI NOT NULL ,
+ [changed] [datetime] NOT NULL ,
+ [data] [text] COLLATE Latin1_General_CI_AI NOT NULL
+) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
+GO
+
+CREATE TABLE [dbo].[cache_messages] (
+ [user_id] [int] NOT NULL ,
+ [mailbox] [varchar] (128) COLLATE Latin1_General_CI_AI NOT NULL ,
+ [uid] [int] NOT NULL ,
+ [changed] [datetime] NOT NULL ,
+ [data] [text] COLLATE Latin1_General_CI_AI NOT NULL ,
+ [flags] [int] NOT NULL
+) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
+GO
+
+ALTER TABLE [dbo].[cache_index] WITH NOCHECK ADD
+ PRIMARY KEY CLUSTERED
+ (
+ [user_id],[mailbox]
+ ) ON [PRIMARY]
+GO
+
+ALTER TABLE [dbo].[cache_thread] WITH NOCHECK ADD
+ PRIMARY KEY CLUSTERED
+ (
+ [user_id],[mailbox]
+ ) ON [PRIMARY]
+GO
+
+ALTER TABLE [dbo].[cache_messages] WITH NOCHECK ADD
+ PRIMARY KEY CLUSTERED
+ (
+ [user_id],[mailbox],[uid]
+ ) ON [PRIMARY]
+GO
+
+ALTER TABLE [dbo].[cache_index] ADD
+ CONSTRAINT [DF_cache_index_changed] DEFAULT (getdate()) FOR [changed],
+ CONSTRAINT [DF_cache_index_valid] DEFAULT ('0') FOR [valid]
+GO
+
+CREATE INDEX [IX_cache_index_user_id] ON [dbo].[cache_index]([user_id]) ON [PRIMARY]
+GO
+
+ALTER TABLE [dbo].[cache_thread] ADD
+ CONSTRAINT [DF_cache_thread_changed] DEFAULT (getdate()) FOR [changed]
+GO
+
+CREATE INDEX [IX_cache_thread_user_id] ON [dbo].[cache_thread]([user_id]) ON [PRIMARY]
+GO
+
+ALTER TABLE [dbo].[cache_messages] ADD
+ CONSTRAINT [DF_cache_messages_changed] DEFAULT (getdate()) FOR [changed],
+ CONSTRAINT [DF_cache_messages_flags] DEFAULT (0) FOR [flags]
+GO
+
+CREATE INDEX [IX_cache_messages_user_id] ON [dbo].[cache_messages]([user_id]) ON [PRIMARY]
+GO
+
+ALTER TABLE [dbo].[cache_index] ADD CONSTRAINT [FK_cache_index_user_id]
+ FOREIGN KEY ([user_id]) REFERENCES [dbo].[users] ([user_id])
+ ON DELETE CASCADE ON UPDATE CASCADE
+GO
+
+ALTER TABLE [dbo].[cache_thread] ADD CONSTRAINT [FK_cache_thread_user_id]
+ FOREIGN KEY ([user_id]) REFERENCES [dbo].[users] ([user_id])
+ ON DELETE CASCADE ON UPDATE CASCADE
+GO
+
+ALTER TABLE [dbo].[cache_messages] ADD CONSTRAINT [FK_cache_messages_user_id]
+ FOREIGN KEY ([user_id]) REFERENCES [dbo].[users] ([user_id])
+ ON DELETE CASCADE ON UPDATE CASCADE
+GO
diff --git a/SQL/mssql/2011111600.sql b/SQL/mssql/2011111600.sql new file mode 100644 index 000000000..387877604 --- /dev/null +++ b/SQL/mssql/2011111600.sql @@ -0,0 +1,4 @@ +-- Updates from version 0.7-beta
+
+ALTER TABLE [dbo].[session] ALTER COLUMN [sess_id] [varchar] (128) COLLATE Latin1_General_CI_AI NOT NULL
+GO
diff --git a/SQL/mssql/2011121400.sql b/SQL/mssql/2011121400.sql new file mode 100644 index 000000000..fde63ea79 --- /dev/null +++ b/SQL/mssql/2011121400.sql @@ -0,0 +1,9 @@ +-- Updates from version 0.7
+
+ALTER TABLE [dbo].[contacts] DROP CONSTRAINT [DF_contacts_email]
+GO
+ALTER TABLE [dbo].[contacts] ALTER COLUMN [email] [text] COLLATE Latin1_General_CI_AI NOT NULL
+GO
+ALTER TABLE [dbo].[contacts] ADD CONSTRAINT [DF_contacts_email] DEFAULT ('') FOR [email]
+GO
+
\ No newline at end of file diff --git a/SQL/mssql/2012051800.sql b/SQL/mssql/2012051800.sql new file mode 100644 index 000000000..8dcf7bfc0 --- /dev/null +++ b/SQL/mssql/2012051800.sql @@ -0,0 +1,18 @@ +-- Updates from version 0.8-rc
+
+ALTER TABLE [dbo].[contacts] DROP CONSTRAINT [DF_contacts_email]
+GO
+ALTER TABLE [dbo].[contacts] ALTER COLUMN [email] [varchar] (8000) COLLATE Latin1_General_CI_AI NOT NULL
+GO
+ALTER TABLE [dbo].[contacts] ADD CONSTRAINT [DF_contacts_email] DEFAULT ('') FOR [email]
+GO
+
+-- Updates from version 0.8
+
+ALTER TABLE [dbo].[cache] DROP COLUMN [cache_id]
+GO
+ALTER TABLE [dbo].[users] DROP COLUMN [alias]
+GO
+CREATE INDEX [IX_identities_email] ON [dbo].[identities]([email],[del]) ON [PRIMARY]
+GO
+
\ No newline at end of file diff --git a/SQL/mssql/2012080700.sql b/SQL/mssql/2012080700.sql new file mode 100644 index 000000000..33e6133b3 --- /dev/null +++ b/SQL/mssql/2012080700.sql @@ -0,0 +1,8 @@ +-- Updates from version 0.8
+
+ALTER TABLE [dbo].[cache] DROP COLUMN [cache_id]
+GO
+ALTER TABLE [dbo].[users] DROP COLUMN [alias]
+GO
+CREATE INDEX [IX_identities_email] ON [dbo].[identities]([email],[del]) ON [PRIMARY]
+GO
diff --git a/SQL/mssql/2013011000.sql b/SQL/mssql/2013011000.sql new file mode 100644 index 000000000..2eb51e6b5 --- /dev/null +++ b/SQL/mssql/2013011000.sql @@ -0,0 +1,14 @@ +-- Upgrades from 0.9-beta + +CREATE TABLE [dbo].[system] (
+ [name] [varchar] (64) COLLATE Latin1_General_CI_AI NOT NULL ,
+ [value] [text] COLLATE Latin1_General_CI_AI
+) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
+GO
+
+ALTER TABLE [dbo].[system] WITH NOCHECK ADD
+ CONSTRAINT [PK_system_name] PRIMARY KEY CLUSTERED
+ (
+ [name]
+ ) ON [PRIMARY]
+GO
diff --git a/SQL/mysql.initial.sql b/SQL/mysql.initial.sql index 47d9db4a2..bb1856cf7 100644 --- a/SQL/mysql.initial.sql +++ b/SQL/mysql.initial.sql @@ -186,4 +186,14 @@ CREATE TABLE `searches` ( ) /*!40000 ENGINE=INNODB */ /*!40101 CHARACTER SET utf8 COLLATE utf8_general_ci */; +-- Table structure for table `system` + +CREATE TABLE `system` ( + `name` varchar(64) NOT NULL, + `value` mediumtext, + PRIMARY KEY(`name`) +) /*!40000 ENGINE=INNODB */ /*!40101 CHARACTER SET utf8 COLLATE utf8_general_ci */; + /*!40014 SET FOREIGN_KEY_CHECKS=1 */; + +INSERT INTO system (name, value) VALUES ('roundcube-version', '2013011000.sql'); diff --git a/SQL/mysql.update.sql b/SQL/mysql.update.sql deleted file mode 100644 index 237aa3e38..000000000 --- a/SQL/mysql.update.sql +++ /dev/null @@ -1,247 +0,0 @@ --- Roundcube Webmail update script for MySQL databases - --- Updates from version 0.1-stable - -TRUNCATE TABLE `messages`; - -ALTER TABLE `messages` - DROP INDEX `idx`, - DROP INDEX `uid`; - -ALTER TABLE `cache` - DROP INDEX `cache_key`, - DROP INDEX `session_id`, - ADD INDEX `user_cache_index` (`user_id`,`cache_key`); - -ALTER TABLE `users` - ADD INDEX `username_index` (`username`), - ADD INDEX `alias_index` (`alias`); - --- Updates from version 0.1.1 - -ALTER TABLE `identities` - MODIFY `signature` text, - MODIFY `bcc` varchar(128) NOT NULL DEFAULT '', - MODIFY `reply-to` varchar(128) NOT NULL DEFAULT '', - MODIFY `organization` varchar(128) NOT NULL DEFAULT '', - MODIFY `name` varchar(128) NOT NULL, - MODIFY `email` varchar(128) NOT NULL; - --- Updates from version 0.2-alpha - -ALTER TABLE `messages` - ADD INDEX `created_index` (`created`); - --- Updates from version 0.2-beta (InnoDB required) - -ALTER TABLE `cache` - DROP `session_id`; - -ALTER TABLE `session` - ADD INDEX `changed_index` (`changed`); - -ALTER TABLE `cache` - ADD INDEX `created_index` (`created`); - -ALTER TABLE `users` - CHANGE `language` `language` varchar(5); - -ALTER TABLE `cache` ENGINE=InnoDB; -ALTER TABLE `session` ENGINE=InnoDB; -ALTER TABLE `messages` ENGINE=InnoDB; -ALTER TABLE `users` ENGINE=InnoDB; -ALTER TABLE `contacts` ENGINE=InnoDB; -ALTER TABLE `identities` ENGINE=InnoDB; - --- Updates from version 0.3-stable - -TRUNCATE `messages`; - -ALTER TABLE `messages` - ADD INDEX `index_index` (`user_id`, `cache_key`, `idx`); - -ALTER TABLE `session` - CHANGE `vars` `vars` MEDIUMTEXT NOT NULL; - -ALTER TABLE `contacts` - ADD INDEX `user_contacts_index` (`user_id`,`email`); - --- Updates from version 0.3.1 --- WARNING: Make sure that all tables are using InnoDB engine!!! --- If not, use: ALTER TABLE xxx ENGINE=InnoDB; - -/* MySQL bug workaround: http://bugs.mysql.com/bug.php?id=46293 */ -/*!40014 SET FOREIGN_KEY_CHECKS=0 */; - -ALTER TABLE `messages` DROP FOREIGN KEY `user_id_fk_messages`; -ALTER TABLE `cache` DROP FOREIGN KEY `user_id_fk_cache`; -ALTER TABLE `contacts` DROP FOREIGN KEY `user_id_fk_contacts`; -ALTER TABLE `identities` DROP FOREIGN KEY `user_id_fk_identities`; - -ALTER TABLE `messages` ADD CONSTRAINT `user_id_fk_messages` FOREIGN KEY (`user_id`) - REFERENCES `users`(`user_id`) ON DELETE CASCADE ON UPDATE CASCADE; -ALTER TABLE `cache` ADD CONSTRAINT `user_id_fk_cache` FOREIGN KEY (`user_id`) - REFERENCES `users`(`user_id`) ON DELETE CASCADE ON UPDATE CASCADE; -ALTER TABLE `contacts` ADD CONSTRAINT `user_id_fk_contacts` FOREIGN KEY (`user_id`) - REFERENCES `users`(`user_id`) ON DELETE CASCADE ON UPDATE CASCADE; -ALTER TABLE `identities` ADD CONSTRAINT `user_id_fk_identities` FOREIGN KEY (`user_id`) - REFERENCES `users`(`user_id`) ON DELETE CASCADE ON UPDATE CASCADE; - -ALTER TABLE `contacts` ALTER `name` SET DEFAULT ''; -ALTER TABLE `contacts` ALTER `firstname` SET DEFAULT ''; -ALTER TABLE `contacts` ALTER `surname` SET DEFAULT ''; - -ALTER TABLE `identities` ADD INDEX `user_identities_index` (`user_id`, `del`); -ALTER TABLE `identities` ADD `changed` datetime NOT NULL DEFAULT '1000-01-01 00:00:00' AFTER `user_id`; - -CREATE TABLE `contactgroups` ( - `contactgroup_id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT, - `user_id` int(10) UNSIGNED NOT NULL DEFAULT '0', - `changed` datetime NOT NULL DEFAULT '1000-01-01 00:00:00', - `del` tinyint(1) NOT NULL DEFAULT '0', - `name` varchar(128) NOT NULL DEFAULT '', - PRIMARY KEY(`contactgroup_id`), - CONSTRAINT `user_id_fk_contactgroups` FOREIGN KEY (`user_id`) - REFERENCES `users`(`user_id`) ON DELETE CASCADE ON UPDATE CASCADE, - INDEX `contactgroups_user_index` (`user_id`,`del`) -) /*!40000 ENGINE=INNODB */ /*!40101 CHARACTER SET utf8 COLLATE utf8_general_ci */; - -CREATE TABLE `contactgroupmembers` ( - `contactgroup_id` int(10) UNSIGNED NOT NULL, - `contact_id` int(10) UNSIGNED NOT NULL DEFAULT '0', - `created` datetime NOT NULL DEFAULT '1000-01-01 00:00:00', - PRIMARY KEY (`contactgroup_id`, `contact_id`), - CONSTRAINT `contactgroup_id_fk_contactgroups` FOREIGN KEY (`contactgroup_id`) - REFERENCES `contactgroups`(`contactgroup_id`) ON DELETE CASCADE ON UPDATE CASCADE, - CONSTRAINT `contact_id_fk_contacts` FOREIGN KEY (`contact_id`) - REFERENCES `contacts`(`contact_id`) ON DELETE CASCADE ON UPDATE CASCADE -) /*!40000 ENGINE=INNODB */; - -/*!40014 SET FOREIGN_KEY_CHECKS=1 */; - --- Updates from version 0.4-beta - -ALTER TABLE `users` CHANGE `last_login` `last_login` datetime DEFAULT NULL; -UPDATE `users` SET `last_login` = NULL WHERE `last_login` = '1000-01-01 00:00:00'; - --- Updates from version 0.4.2 - -ALTER TABLE `users` DROP INDEX `username_index`; -ALTER TABLE `users` ADD UNIQUE `username` (`username`, `mail_host`); - -ALTER TABLE `contacts` MODIFY `email` varchar(255) NOT NULL; - -TRUNCATE TABLE `messages`; - --- Updates from version 0.5.1 --- Updates from version 0.5.2 --- Updates from version 0.5.3 --- Updates from version 0.5.4 - -ALTER TABLE `contacts` ADD `words` TEXT NULL AFTER `vcard`; -ALTER TABLE `contacts` CHANGE `vcard` `vcard` LONGTEXT /*!40101 CHARACTER SET utf8 */ NULL DEFAULT NULL; -ALTER TABLE `contactgroupmembers` ADD INDEX `contactgroupmembers_contact_index` (`contact_id`); - -TRUNCATE TABLE `messages`; -TRUNCATE TABLE `cache`; - --- Updates from version 0.6 - -/*!40014 SET FOREIGN_KEY_CHECKS=0 */; - -ALTER TABLE `users` CHANGE `alias` `alias` varchar(128) BINARY NOT NULL; -ALTER TABLE `users` CHANGE `username` `username` varchar(128) BINARY NOT NULL; - -CREATE TABLE `dictionary` ( - `user_id` int(10) UNSIGNED DEFAULT NULL, - `language` varchar(5) NOT NULL, - `data` longtext NOT NULL, - CONSTRAINT `user_id_fk_dictionary` FOREIGN KEY (`user_id`) - REFERENCES `users`(`user_id`) ON DELETE CASCADE ON UPDATE CASCADE, - UNIQUE `uniqueness` (`user_id`, `language`) -) /*!40000 ENGINE=INNODB */ /*!40101 CHARACTER SET utf8 COLLATE utf8_general_ci */; - -CREATE TABLE `searches` ( - `search_id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT, - `user_id` int(10) UNSIGNED NOT NULL DEFAULT '0', - `type` int(3) NOT NULL DEFAULT '0', - `name` varchar(128) NOT NULL, - `data` text, - PRIMARY KEY(`search_id`), - CONSTRAINT `user_id_fk_searches` FOREIGN KEY (`user_id`) - REFERENCES `users`(`user_id`) ON DELETE CASCADE ON UPDATE CASCADE, - UNIQUE `uniqueness` (`user_id`, `type`, `name`) -) /*!40000 ENGINE=INNODB */ /*!40101 CHARACTER SET utf8 COLLATE utf8_general_ci */; - -DROP TABLE `messages`; - -CREATE TABLE `cache_index` ( - `user_id` int(10) UNSIGNED NOT NULL DEFAULT '0', - `mailbox` varchar(255) BINARY NOT NULL, - `changed` datetime NOT NULL DEFAULT '1000-01-01 00:00:00', - `valid` tinyint(1) NOT NULL DEFAULT '0', - `data` longtext NOT NULL, - CONSTRAINT `user_id_fk_cache_index` FOREIGN KEY (`user_id`) - REFERENCES `users`(`user_id`) ON DELETE CASCADE ON UPDATE CASCADE, - INDEX `changed_index` (`changed`), - PRIMARY KEY (`user_id`, `mailbox`) -) /*!40000 ENGINE=INNODB */ /*!40101 CHARACTER SET utf8 COLLATE utf8_general_ci */; - -CREATE TABLE `cache_thread` ( - `user_id` int(10) UNSIGNED NOT NULL DEFAULT '0', - `mailbox` varchar(255) BINARY NOT NULL, - `changed` datetime NOT NULL DEFAULT '1000-01-01 00:00:00', - `data` longtext NOT NULL, - CONSTRAINT `user_id_fk_cache_thread` FOREIGN KEY (`user_id`) - REFERENCES `users`(`user_id`) ON DELETE CASCADE ON UPDATE CASCADE, - INDEX `changed_index` (`changed`), - PRIMARY KEY (`user_id`, `mailbox`) -) /*!40000 ENGINE=INNODB */ /*!40101 CHARACTER SET utf8 COLLATE utf8_general_ci */; - -CREATE TABLE `cache_messages` ( - `user_id` int(10) UNSIGNED NOT NULL DEFAULT '0', - `mailbox` varchar(255) BINARY NOT NULL, - `uid` int(11) UNSIGNED NOT NULL DEFAULT '0', - `changed` datetime NOT NULL DEFAULT '1000-01-01 00:00:00', - `data` longtext NOT NULL, - `flags` int(11) NOT NULL DEFAULT '0', - CONSTRAINT `user_id_fk_cache_messages` FOREIGN KEY (`user_id`) - REFERENCES `users`(`user_id`) ON DELETE CASCADE ON UPDATE CASCADE, - INDEX `changed_index` (`changed`), - PRIMARY KEY (`user_id`, `mailbox`, `uid`) -) /*!40000 ENGINE=INNODB */ /*!40101 CHARACTER SET utf8 COLLATE utf8_general_ci */; - -/*!40014 SET FOREIGN_KEY_CHECKS=1 */; - --- Updates from version 0.7-beta - -ALTER TABLE `session` CHANGE `sess_id` `sess_id` varchar(128) NOT NULL; - --- Updates from version 0.7 - -/*!40014 SET FOREIGN_KEY_CHECKS=0 */; - -ALTER TABLE `contacts` DROP FOREIGN KEY `user_id_fk_contacts`; -ALTER TABLE `contacts` DROP INDEX `user_contacts_index`; -ALTER TABLE `contacts` MODIFY `email` text NOT NULL; -ALTER TABLE `contacts` ADD INDEX `user_contacts_index` (`user_id`,`del`); -ALTER TABLE `contacts` ADD CONSTRAINT `user_id_fk_contacts` FOREIGN KEY (`user_id`) - REFERENCES `users`(`user_id`) ON DELETE CASCADE ON UPDATE CASCADE; - -ALTER TABLE `cache` ALTER `user_id` DROP DEFAULT; -ALTER TABLE `cache_index` ALTER `user_id` DROP DEFAULT; -ALTER TABLE `cache_thread` ALTER `user_id` DROP DEFAULT; -ALTER TABLE `cache_messages` ALTER `user_id` DROP DEFAULT; -ALTER TABLE `contacts` ALTER `user_id` DROP DEFAULT; -ALTER TABLE `contactgroups` ALTER `user_id` DROP DEFAULT; -ALTER TABLE `contactgroupmembers` ALTER `contact_id` DROP DEFAULT; -ALTER TABLE `identities` ALTER `user_id` DROP DEFAULT; -ALTER TABLE `searches` ALTER `user_id` DROP DEFAULT; - -/*!40014 SET FOREIGN_KEY_CHECKS=1 */; - --- Updates from version 0.8 - -ALTER TABLE `cache` DROP COLUMN `cache_id`; -ALTER TABLE `users` DROP COLUMN `alias`; -ALTER TABLE `identities` ADD INDEX `email_identities_index` (`email`, `del`); diff --git a/SQL/mysql/2008030300.sql b/SQL/mysql/2008030300.sql new file mode 100644 index 000000000..9a3d04847 --- /dev/null +++ b/SQL/mysql/2008030300.sql @@ -0,0 +1,16 @@ +-- Updates from version 0.1-stable + +TRUNCATE TABLE `messages`; + +ALTER TABLE `messages` + DROP INDEX `idx`, + DROP INDEX `uid`; + +ALTER TABLE `cache` + DROP INDEX `cache_key`, + DROP INDEX `session_id`, + ADD INDEX `user_cache_index` (`user_id`,`cache_key`); + +ALTER TABLE `users` + ADD INDEX `username_index` (`username`), + ADD INDEX `alias_index` (`alias`); diff --git a/SQL/mysql/2008040500.sql b/SQL/mysql/2008040500.sql new file mode 100644 index 000000000..f538f6378 --- /dev/null +++ b/SQL/mysql/2008040500.sql @@ -0,0 +1,9 @@ +-- Updates from version 0.1.1 + +ALTER TABLE `identities` + MODIFY `signature` text, + MODIFY `bcc` varchar(128) NOT NULL DEFAULT '', + MODIFY `reply-to` varchar(128) NOT NULL DEFAULT '', + MODIFY `organization` varchar(128) NOT NULL DEFAULT '', + MODIFY `name` varchar(128) NOT NULL, + MODIFY `email` varchar(128) NOT NULL; diff --git a/SQL/mysql/2008060900.sql b/SQL/mysql/2008060900.sql new file mode 100644 index 000000000..9f8de0a54 --- /dev/null +++ b/SQL/mysql/2008060900.sql @@ -0,0 +1,4 @@ +-- Updates from version 0.2-alpha + +ALTER TABLE `messages` + ADD INDEX `created_index` (`created`); diff --git a/SQL/mysql/2008092100.sql b/SQL/mysql/2008092100.sql new file mode 100644 index 000000000..3989c75f2 --- /dev/null +++ b/SQL/mysql/2008092100.sql @@ -0,0 +1,20 @@ +-- Updates from version 0.2-beta (InnoDB required) + +ALTER TABLE `cache` + DROP `session_id`; + +ALTER TABLE `session` + ADD INDEX `changed_index` (`changed`); + +ALTER TABLE `cache` + ADD INDEX `created_index` (`created`); + +ALTER TABLE `users` + CHANGE `language` `language` varchar(5); + +ALTER TABLE `cache` ENGINE=InnoDB; +ALTER TABLE `session` ENGINE=InnoDB; +ALTER TABLE `messages` ENGINE=InnoDB; +ALTER TABLE `users` ENGINE=InnoDB; +ALTER TABLE `contacts` ENGINE=InnoDB; +ALTER TABLE `identities` ENGINE=InnoDB; diff --git a/SQL/mysql/2009090400.sql b/SQL/mysql/2009090400.sql new file mode 100644 index 000000000..fd31bed5b --- /dev/null +++ b/SQL/mysql/2009090400.sql @@ -0,0 +1,12 @@ +-- Updates from version 0.3-stable + +TRUNCATE `messages`; + +ALTER TABLE `messages` + ADD INDEX `index_index` (`user_id`, `cache_key`, `idx`); + +ALTER TABLE `session` + CHANGE `vars` `vars` MEDIUMTEXT NOT NULL; + +ALTER TABLE `contacts` + ADD INDEX `user_contacts_index` (`user_id`,`email`); diff --git a/SQL/mysql/2009103100.sql b/SQL/mysql/2009103100.sql new file mode 100644 index 000000000..aafbe238f --- /dev/null +++ b/SQL/mysql/2009103100.sql @@ -0,0 +1,52 @@ +-- Updates from version 0.3.1 +-- WARNING: Make sure that all tables are using InnoDB engine!!! +-- If not, use: ALTER TABLE xxx ENGINE=InnoDB; + +/* MySQL bug workaround: http://bugs.mysql.com/bug.php?id=46293 */ +/*!40014 SET FOREIGN_KEY_CHECKS=0 */; + +ALTER TABLE `messages` DROP FOREIGN KEY `user_id_fk_messages`; +ALTER TABLE `cache` DROP FOREIGN KEY `user_id_fk_cache`; +ALTER TABLE `contacts` DROP FOREIGN KEY `user_id_fk_contacts`; +ALTER TABLE `identities` DROP FOREIGN KEY `user_id_fk_identities`; + +ALTER TABLE `messages` ADD CONSTRAINT `user_id_fk_messages` FOREIGN KEY (`user_id`) + REFERENCES `users`(`user_id`) ON DELETE CASCADE ON UPDATE CASCADE; +ALTER TABLE `cache` ADD CONSTRAINT `user_id_fk_cache` FOREIGN KEY (`user_id`) + REFERENCES `users`(`user_id`) ON DELETE CASCADE ON UPDATE CASCADE; +ALTER TABLE `contacts` ADD CONSTRAINT `user_id_fk_contacts` FOREIGN KEY (`user_id`) + REFERENCES `users`(`user_id`) ON DELETE CASCADE ON UPDATE CASCADE; +ALTER TABLE `identities` ADD CONSTRAINT `user_id_fk_identities` FOREIGN KEY (`user_id`) + REFERENCES `users`(`user_id`) ON DELETE CASCADE ON UPDATE CASCADE; + +ALTER TABLE `contacts` ALTER `name` SET DEFAULT ''; +ALTER TABLE `contacts` ALTER `firstname` SET DEFAULT ''; +ALTER TABLE `contacts` ALTER `surname` SET DEFAULT ''; + +ALTER TABLE `identities` ADD INDEX `user_identities_index` (`user_id`, `del`); +ALTER TABLE `identities` ADD `changed` datetime NOT NULL DEFAULT '1000-01-01 00:00:00' AFTER `user_id`; + +CREATE TABLE `contactgroups` ( + `contactgroup_id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT, + `user_id` int(10) UNSIGNED NOT NULL DEFAULT '0', + `changed` datetime NOT NULL DEFAULT '1000-01-01 00:00:00', + `del` tinyint(1) NOT NULL DEFAULT '0', + `name` varchar(128) NOT NULL DEFAULT '', + PRIMARY KEY(`contactgroup_id`), + CONSTRAINT `user_id_fk_contactgroups` FOREIGN KEY (`user_id`) + REFERENCES `users`(`user_id`) ON DELETE CASCADE ON UPDATE CASCADE, + INDEX `contactgroups_user_index` (`user_id`,`del`) +) /*!40000 ENGINE=INNODB */ /*!40101 CHARACTER SET utf8 COLLATE utf8_general_ci */; + +CREATE TABLE `contactgroupmembers` ( + `contactgroup_id` int(10) UNSIGNED NOT NULL, + `contact_id` int(10) UNSIGNED NOT NULL DEFAULT '0', + `created` datetime NOT NULL DEFAULT '1000-01-01 00:00:00', + PRIMARY KEY (`contactgroup_id`, `contact_id`), + CONSTRAINT `contactgroup_id_fk_contactgroups` FOREIGN KEY (`contactgroup_id`) + REFERENCES `contactgroups`(`contactgroup_id`) ON DELETE CASCADE ON UPDATE CASCADE, + CONSTRAINT `contact_id_fk_contacts` FOREIGN KEY (`contact_id`) + REFERENCES `contacts`(`contact_id`) ON DELETE CASCADE ON UPDATE CASCADE +) /*!40000 ENGINE=INNODB */; + +/*!40014 SET FOREIGN_KEY_CHECKS=1 */; diff --git a/SQL/mysql/2010042300.sql b/SQL/mysql/2010042300.sql new file mode 100644 index 000000000..8b90af45e --- /dev/null +++ b/SQL/mysql/2010042300.sql @@ -0,0 +1,4 @@ +-- Updates from version 0.4-beta + +ALTER TABLE `users` CHANGE `last_login` `last_login` datetime DEFAULT NULL; +UPDATE `users` SET `last_login` = NULL WHERE `last_login` = '1000-01-01 00:00:00'; diff --git a/SQL/mysql/2010100600.sql b/SQL/mysql/2010100600.sql new file mode 100644 index 000000000..ca0fec858 --- /dev/null +++ b/SQL/mysql/2010100600.sql @@ -0,0 +1,8 @@ +-- Updates from version 0.4.2 + +ALTER TABLE `users` DROP INDEX `username_index`; +ALTER TABLE `users` ADD UNIQUE `username` (`username`, `mail_host`); + +ALTER TABLE `contacts` MODIFY `email` varchar(255) NOT NULL; + +TRUNCATE TABLE `messages`; diff --git a/SQL/mysql/2011011200.sql b/SQL/mysql/2011011200.sql new file mode 100644 index 000000000..6597034c2 --- /dev/null +++ b/SQL/mysql/2011011200.sql @@ -0,0 +1,8 @@ +-- Updates from version 0.5.x + +ALTER TABLE `contacts` ADD `words` TEXT NULL AFTER `vcard`; +ALTER TABLE `contacts` CHANGE `vcard` `vcard` LONGTEXT /*!40101 CHARACTER SET utf8 */ NULL DEFAULT NULL; +ALTER TABLE `contactgroupmembers` ADD INDEX `contactgroupmembers_contact_index` (`contact_id`); + +TRUNCATE TABLE `messages`; +TRUNCATE TABLE `cache`; diff --git a/SQL/mysql/2011092800.sql b/SQL/mysql/2011092800.sql new file mode 100644 index 000000000..6b7cbe1f3 --- /dev/null +++ b/SQL/mysql/2011092800.sql @@ -0,0 +1,67 @@ +-- Updates from version 0.6 + +/*!40014 SET FOREIGN_KEY_CHECKS=0 */; + +ALTER TABLE `users` CHANGE `alias` `alias` varchar(128) BINARY NOT NULL; +ALTER TABLE `users` CHANGE `username` `username` varchar(128) BINARY NOT NULL; + +CREATE TABLE `dictionary` ( + `user_id` int(10) UNSIGNED DEFAULT NULL, + `language` varchar(5) NOT NULL, + `data` longtext NOT NULL, + CONSTRAINT `user_id_fk_dictionary` FOREIGN KEY (`user_id`) + REFERENCES `users`(`user_id`) ON DELETE CASCADE ON UPDATE CASCADE, + UNIQUE `uniqueness` (`user_id`, `language`) +) /*!40000 ENGINE=INNODB */ /*!40101 CHARACTER SET utf8 COLLATE utf8_general_ci */; + +CREATE TABLE `searches` ( + `search_id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT, + `user_id` int(10) UNSIGNED NOT NULL DEFAULT '0', + `type` int(3) NOT NULL DEFAULT '0', + `name` varchar(128) NOT NULL, + `data` text, + PRIMARY KEY(`search_id`), + CONSTRAINT `user_id_fk_searches` FOREIGN KEY (`user_id`) + REFERENCES `users`(`user_id`) ON DELETE CASCADE ON UPDATE CASCADE, + UNIQUE `uniqueness` (`user_id`, `type`, `name`) +) /*!40000 ENGINE=INNODB */ /*!40101 CHARACTER SET utf8 COLLATE utf8_general_ci */; + +DROP TABLE `messages`; + +CREATE TABLE `cache_index` ( + `user_id` int(10) UNSIGNED NOT NULL DEFAULT '0', + `mailbox` varchar(255) BINARY NOT NULL, + `changed` datetime NOT NULL DEFAULT '1000-01-01 00:00:00', + `valid` tinyint(1) NOT NULL DEFAULT '0', + `data` longtext NOT NULL, + CONSTRAINT `user_id_fk_cache_index` FOREIGN KEY (`user_id`) + REFERENCES `users`(`user_id`) ON DELETE CASCADE ON UPDATE CASCADE, + INDEX `changed_index` (`changed`), + PRIMARY KEY (`user_id`, `mailbox`) +) /*!40000 ENGINE=INNODB */ /*!40101 CHARACTER SET utf8 COLLATE utf8_general_ci */; + +CREATE TABLE `cache_thread` ( + `user_id` int(10) UNSIGNED NOT NULL DEFAULT '0', + `mailbox` varchar(255) BINARY NOT NULL, + `changed` datetime NOT NULL DEFAULT '1000-01-01 00:00:00', + `data` longtext NOT NULL, + CONSTRAINT `user_id_fk_cache_thread` FOREIGN KEY (`user_id`) + REFERENCES `users`(`user_id`) ON DELETE CASCADE ON UPDATE CASCADE, + INDEX `changed_index` (`changed`), + PRIMARY KEY (`user_id`, `mailbox`) +) /*!40000 ENGINE=INNODB */ /*!40101 CHARACTER SET utf8 COLLATE utf8_general_ci */; + +CREATE TABLE `cache_messages` ( + `user_id` int(10) UNSIGNED NOT NULL DEFAULT '0', + `mailbox` varchar(255) BINARY NOT NULL, + `uid` int(11) UNSIGNED NOT NULL DEFAULT '0', + `changed` datetime NOT NULL DEFAULT '1000-01-01 00:00:00', + `data` longtext NOT NULL, + `flags` int(11) NOT NULL DEFAULT '0', + CONSTRAINT `user_id_fk_cache_messages` FOREIGN KEY (`user_id`) + REFERENCES `users`(`user_id`) ON DELETE CASCADE ON UPDATE CASCADE, + INDEX `changed_index` (`changed`), + PRIMARY KEY (`user_id`, `mailbox`, `uid`) +) /*!40000 ENGINE=INNODB */ /*!40101 CHARACTER SET utf8 COLLATE utf8_general_ci */; + +/*!40014 SET FOREIGN_KEY_CHECKS=1 */; diff --git a/SQL/mysql/2011111600.sql b/SQL/mysql/2011111600.sql new file mode 100644 index 000000000..6f53daa9c --- /dev/null +++ b/SQL/mysql/2011111600.sql @@ -0,0 +1,3 @@ +-- Updates from version 0.7-beta + +ALTER TABLE `session` CHANGE `sess_id` `sess_id` varchar(128) NOT NULL; diff --git a/SQL/mysql/2011121400.sql b/SQL/mysql/2011121400.sql new file mode 100644 index 000000000..5aee80601 --- /dev/null +++ b/SQL/mysql/2011121400.sql @@ -0,0 +1,22 @@ +-- Updates from version 0.7 + +/*!40014 SET FOREIGN_KEY_CHECKS=0 */; + +ALTER TABLE `contacts` DROP FOREIGN KEY `user_id_fk_contacts`; +ALTER TABLE `contacts` DROP INDEX `user_contacts_index`; +ALTER TABLE `contacts` MODIFY `email` text NOT NULL; +ALTER TABLE `contacts` ADD INDEX `user_contacts_index` (`user_id`,`del`); +ALTER TABLE `contacts` ADD CONSTRAINT `user_id_fk_contacts` FOREIGN KEY (`user_id`) + REFERENCES `users`(`user_id`) ON DELETE CASCADE ON UPDATE CASCADE; + +ALTER TABLE `cache` ALTER `user_id` DROP DEFAULT; +ALTER TABLE `cache_index` ALTER `user_id` DROP DEFAULT; +ALTER TABLE `cache_thread` ALTER `user_id` DROP DEFAULT; +ALTER TABLE `cache_messages` ALTER `user_id` DROP DEFAULT; +ALTER TABLE `contacts` ALTER `user_id` DROP DEFAULT; +ALTER TABLE `contactgroups` ALTER `user_id` DROP DEFAULT; +ALTER TABLE `contactgroupmembers` ALTER `contact_id` DROP DEFAULT; +ALTER TABLE `identities` ALTER `user_id` DROP DEFAULT; +ALTER TABLE `searches` ALTER `user_id` DROP DEFAULT; + +/*!40014 SET FOREIGN_KEY_CHECKS=1 */; diff --git a/SQL/mysql/2012080700.sql b/SQL/mysql/2012080700.sql new file mode 100644 index 000000000..789b7122c --- /dev/null +++ b/SQL/mysql/2012080700.sql @@ -0,0 +1,5 @@ +-- Updates from version 0.8 + +ALTER TABLE `cache` DROP COLUMN `cache_id`; +ALTER TABLE `users` DROP COLUMN `alias`; +ALTER TABLE `identities` ADD INDEX `email_identities_index` (`email`, `del`); diff --git a/SQL/mysql/2013011000.sql b/SQL/mysql/2013011000.sql new file mode 100644 index 000000000..f0b5e6a03 --- /dev/null +++ b/SQL/mysql/2013011000.sql @@ -0,0 +1,7 @@ +-- Upgrade from 0.9-beta + +CREATE TABLE `system` ( + `name` varchar(64) NOT NULL, + `value` mediumtext, + PRIMARY KEY(`name`) +) /*!40000 ENGINE=INNODB */ /*!40101 CHARACTER SET utf8 COLLATE utf8_general_ci */; diff --git a/SQL/postgres.initial.sql b/SQL/postgres.initial.sql index f7b2d96d9..d7dc08f90 100644 --- a/SQL/postgres.initial.sql +++ b/SQL/postgres.initial.sql @@ -262,3 +262,16 @@ CREATE TABLE searches ( data text NOT NULL, CONSTRAINT searches_user_id_key UNIQUE (user_id, "type", name) ); + + +-- +-- Table "system" +-- Name: system; Type: TABLE; Schema: public; Owner: postgres +-- + +CREATE TABLE "system" ( + name varchar(64) NOT NULL PRIMARY KEY, + value text +); + +INSERT INTO system (name, value) VALUES ('roundcube-version', '2013011000.sql'); diff --git a/SQL/postgres.update.sql b/SQL/postgres.update.sql deleted file mode 100644 index 11ab93bfc..000000000 --- a/SQL/postgres.update.sql +++ /dev/null @@ -1,185 +0,0 @@ --- Roundcube Webmail update script for Postgres databases --- Updates from version 0.1-stable to 0.1.1 - -CREATE INDEX cache_user_id_idx ON cache (user_id, cache_key); -CREATE INDEX contacts_user_id_idx ON contacts (user_id); -CREATE INDEX identities_user_id_idx ON identities (user_id); - -CREATE INDEX users_username_id_idx ON users (username); -CREATE INDEX users_alias_id_idx ON users (alias); - --- added ON DELETE/UPDATE actions -ALTER TABLE messages DROP CONSTRAINT messages_user_id_fkey; -ALTER TABLE messages ADD FOREIGN KEY (user_id) REFERENCES users(user_id) ON DELETE CASCADE ON UPDATE CASCADE; -ALTER TABLE identities DROP CONSTRAINT identities_user_id_fkey; -ALTER TABLE identities ADD FOREIGN KEY (user_id) REFERENCES users(user_id) ON DELETE CASCADE ON UPDATE CASCADE; -ALTER TABLE contacts DROP CONSTRAINT contacts_user_id_fkey; -ALTER TABLE contacts ADD FOREIGN KEY (user_id) REFERENCES users(user_id) ON DELETE CASCADE ON UPDATE CASCADE; -ALTER TABLE cache DROP CONSTRAINT cache_user_id_fkey; -ALTER TABLE cache ADD FOREIGN KEY (user_id) REFERENCES users(user_id) ON DELETE CASCADE ON UPDATE CASCADE; - --- Updates from version 0.2-alpha - -CREATE INDEX messages_created_idx ON messages (created); - --- Updates from version 0.2-beta - -ALTER TABLE cache DROP session_id; - -CREATE INDEX session_changed_idx ON session (changed); -CREATE INDEX cache_created_idx ON "cache" (created); - -ALTER TABLE users ALTER "language" DROP NOT NULL; -ALTER TABLE users ALTER "language" DROP DEFAULT; - -ALTER TABLE identities ALTER del TYPE smallint; -ALTER TABLE identities ALTER standard TYPE smallint; -ALTER TABLE contacts ALTER del TYPE smallint; -ALTER TABLE messages ALTER del TYPE smallint; - --- Updates from version 0.3-stable - -TRUNCATE messages; -CREATE INDEX messages_index_idx ON messages (user_id, cache_key, idx); -DROP INDEX contacts_user_id_idx; -CREATE INDEX contacts_user_id_idx ON contacts (user_id, email); - --- Updates from version 0.3.1 - -DROP INDEX identities_user_id_idx; -CREATE INDEX identities_user_id_idx ON identities (user_id, del); - -ALTER TABLE identities ADD changed timestamp with time zone DEFAULT now() NOT NULL; - -CREATE SEQUENCE contactgroups_ids - INCREMENT BY 1 - NO MAXVALUE - NO MINVALUE - CACHE 1; - -CREATE TABLE contactgroups ( - contactgroup_id integer DEFAULT nextval('contactgroups_ids'::text) PRIMARY KEY, - user_id integer NOT NULL - REFERENCES users(user_id) ON DELETE CASCADE ON UPDATE CASCADE, - changed timestamp with time zone DEFAULT now() NOT NULL, - del smallint NOT NULL DEFAULT 0, - name varchar(128) NOT NULL DEFAULT '' -); - -CREATE INDEX contactgroups_user_id_idx ON contactgroups (user_id, del); - -CREATE TABLE contactgroupmembers ( - contactgroup_id integer NOT NULL - REFERENCES contactgroups(contactgroup_id) ON DELETE CASCADE ON UPDATE CASCADE, - contact_id integer NOT NULL - REFERENCES contacts(contact_id) ON DELETE CASCADE ON UPDATE CASCADE, - created timestamp with time zone DEFAULT now() NOT NULL, - PRIMARY KEY (contactgroup_id, contact_id) -); - --- Updates from version 0.4-beta - -ALTER TABLE users ALTER last_login DROP NOT NULL; -ALTER TABLE users ALTER last_login SET DEFAULT NULL; - --- Updates from version 0.4.2 - -DROP INDEX users_username_id_idx; -ALTER TABLE users ADD CONSTRAINT users_username_key UNIQUE (username, mail_host); -ALTER TABLE contacts ALTER email TYPE varchar(255); - -TRUNCATE messages; - --- Updates from version 0.5.1 --- Updates from version 0.5.2 --- Updates from version 0.5.3 --- Updates from version 0.5.4 - -ALTER TABLE contacts ADD words TEXT NULL; -CREATE INDEX contactgroupmembers_contact_id_idx ON contactgroupmembers (contact_id); - -TRUNCATE messages; -TRUNCATE cache; - --- Updates from version 0.6 - -CREATE TABLE dictionary ( - user_id integer DEFAULT NULL - REFERENCES users (user_id) ON DELETE CASCADE ON UPDATE CASCADE, - "language" varchar(5) NOT NULL, - data text NOT NULL, - CONSTRAINT dictionary_user_id_language_key UNIQUE (user_id, "language") -); - -CREATE SEQUENCE search_ids - INCREMENT BY 1 - NO MAXVALUE - NO MINVALUE - CACHE 1; - -CREATE TABLE searches ( - search_id integer DEFAULT nextval('search_ids'::text) PRIMARY KEY, - user_id integer NOT NULL - REFERENCES users (user_id) ON DELETE CASCADE ON UPDATE CASCADE, - "type" smallint DEFAULT 0 NOT NULL, - name varchar(128) NOT NULL, - data text NOT NULL, - CONSTRAINT searches_user_id_key UNIQUE (user_id, "type", name) -); - -DROP SEQUENCE message_ids; -DROP TABLE messages; - -CREATE TABLE cache_index ( - user_id integer NOT NULL - REFERENCES users (user_id) ON DELETE CASCADE ON UPDATE CASCADE, - mailbox varchar(255) NOT NULL, - changed timestamp with time zone DEFAULT now() NOT NULL, - valid smallint NOT NULL DEFAULT 0, - data text NOT NULL, - PRIMARY KEY (user_id, mailbox) -); - -CREATE INDEX cache_index_changed_idx ON cache_index (changed); - -CREATE TABLE cache_thread ( - user_id integer NOT NULL - REFERENCES users (user_id) ON DELETE CASCADE ON UPDATE CASCADE, - mailbox varchar(255) NOT NULL, - changed timestamp with time zone DEFAULT now() NOT NULL, - data text NOT NULL, - PRIMARY KEY (user_id, mailbox) -); - -CREATE INDEX cache_thread_changed_idx ON cache_thread (changed); - -CREATE TABLE cache_messages ( - user_id integer NOT NULL - REFERENCES users (user_id) ON DELETE CASCADE ON UPDATE CASCADE, - mailbox varchar(255) NOT NULL, - uid integer NOT NULL, - changed timestamp with time zone DEFAULT now() NOT NULL, - data text NOT NULL, - flags integer NOT NULL DEFAULT 0, - PRIMARY KEY (user_id, mailbox, uid) -); - -CREATE INDEX cache_messages_changed_idx ON cache_messages (changed); - --- Updates from version 0.7-beta - -ALTER TABLE "session" ALTER sess_id TYPE varchar(128); - --- Updates from version 0.7 - -DROP INDEX contacts_user_id_idx; -CREATE INDEX contacts_user_id_idx ON contacts USING btree (user_id, del); -ALTER TABLE contacts ALTER email TYPE text; - --- Updates from version 0.8 - -ALTER TABLE cache DROP COLUMN cache_id; -DROP SEQUENCE cache_ids; - -ALTER TABLE users DROP COLUMN alias; -CREATE INDEX identities_email_idx ON identities (email, del); diff --git a/SQL/postgres/2008030300.sql b/SQL/postgres/2008030300.sql new file mode 100644 index 000000000..67a8f158f --- /dev/null +++ b/SQL/postgres/2008030300.sql @@ -0,0 +1,18 @@ +-- Updates from version 0.1-stable to 0.1.1 + +CREATE INDEX cache_user_id_idx ON cache (user_id, cache_key); +CREATE INDEX contacts_user_id_idx ON contacts (user_id); +CREATE INDEX identities_user_id_idx ON identities (user_id); + +CREATE INDEX users_username_id_idx ON users (username); +CREATE INDEX users_alias_id_idx ON users (alias); + +-- added ON DELETE/UPDATE actions +ALTER TABLE messages DROP CONSTRAINT messages_user_id_fkey; +ALTER TABLE messages ADD FOREIGN KEY (user_id) REFERENCES users(user_id) ON DELETE CASCADE ON UPDATE CASCADE; +ALTER TABLE identities DROP CONSTRAINT identities_user_id_fkey; +ALTER TABLE identities ADD FOREIGN KEY (user_id) REFERENCES users(user_id) ON DELETE CASCADE ON UPDATE CASCADE; +ALTER TABLE contacts DROP CONSTRAINT contacts_user_id_fkey; +ALTER TABLE contacts ADD FOREIGN KEY (user_id) REFERENCES users(user_id) ON DELETE CASCADE ON UPDATE CASCADE; +ALTER TABLE cache DROP CONSTRAINT cache_user_id_fkey; +ALTER TABLE cache ADD FOREIGN KEY (user_id) REFERENCES users(user_id) ON DELETE CASCADE ON UPDATE CASCADE; diff --git a/SQL/postgres/2008060900.sql b/SQL/postgres/2008060900.sql new file mode 100644 index 000000000..7c60a4422 --- /dev/null +++ b/SQL/postgres/2008060900.sql @@ -0,0 +1,3 @@ +-- Updates from version 0.2-alpha + +CREATE INDEX messages_created_idx ON messages (created); diff --git a/SQL/postgres/2008092100.sql b/SQL/postgres/2008092100.sql new file mode 100644 index 000000000..6cd19290c --- /dev/null +++ b/SQL/postgres/2008092100.sql @@ -0,0 +1,14 @@ +-- Updates from version 0.2-beta + +ALTER TABLE cache DROP session_id; + +CREATE INDEX session_changed_idx ON session (changed); +CREATE INDEX cache_created_idx ON "cache" (created); + +ALTER TABLE users ALTER "language" DROP NOT NULL; +ALTER TABLE users ALTER "language" DROP DEFAULT; + +ALTER TABLE identities ALTER del TYPE smallint; +ALTER TABLE identities ALTER standard TYPE smallint; +ALTER TABLE contacts ALTER del TYPE smallint; +ALTER TABLE messages ALTER del TYPE smallint; diff --git a/SQL/postgres/2009090400.sql b/SQL/postgres/2009090400.sql new file mode 100644 index 000000000..8eb4949e8 --- /dev/null +++ b/SQL/postgres/2009090400.sql @@ -0,0 +1,6 @@ +-- Updates from version 0.3-stable + +TRUNCATE messages; +CREATE INDEX messages_index_idx ON messages (user_id, cache_key, idx); +DROP INDEX contacts_user_id_idx; +CREATE INDEX contacts_user_id_idx ON contacts (user_id, email); diff --git a/SQL/postgres/2009103100.sql b/SQL/postgres/2009103100.sql new file mode 100644 index 000000000..2350f79f7 --- /dev/null +++ b/SQL/postgres/2009103100.sql @@ -0,0 +1,32 @@ +-- Updates from version 0.3.1 + +DROP INDEX identities_user_id_idx; +CREATE INDEX identities_user_id_idx ON identities (user_id, del); + +ALTER TABLE identities ADD changed timestamp with time zone DEFAULT now() NOT NULL; + +CREATE SEQUENCE contactgroups_ids + INCREMENT BY 1 + NO MAXVALUE + NO MINVALUE + CACHE 1; + +CREATE TABLE contactgroups ( + contactgroup_id integer DEFAULT nextval('contactgroups_ids'::text) PRIMARY KEY, + user_id integer NOT NULL + REFERENCES users(user_id) ON DELETE CASCADE ON UPDATE CASCADE, + changed timestamp with time zone DEFAULT now() NOT NULL, + del smallint NOT NULL DEFAULT 0, + name varchar(128) NOT NULL DEFAULT '' +); + +CREATE INDEX contactgroups_user_id_idx ON contactgroups (user_id, del); + +CREATE TABLE contactgroupmembers ( + contactgroup_id integer NOT NULL + REFERENCES contactgroups(contactgroup_id) ON DELETE CASCADE ON UPDATE CASCADE, + contact_id integer NOT NULL + REFERENCES contacts(contact_id) ON DELETE CASCADE ON UPDATE CASCADE, + created timestamp with time zone DEFAULT now() NOT NULL, + PRIMARY KEY (contactgroup_id, contact_id) +); diff --git a/SQL/postgres/2010042300.sql b/SQL/postgres/2010042300.sql new file mode 100644 index 000000000..bc9bd826a --- /dev/null +++ b/SQL/postgres/2010042300.sql @@ -0,0 +1,4 @@ +-- Updates from version 0.4-beta + +ALTER TABLE users ALTER last_login DROP NOT NULL; +ALTER TABLE users ALTER last_login SET DEFAULT NULL; diff --git a/SQL/postgres/2010100600.sql b/SQL/postgres/2010100600.sql new file mode 100644 index 000000000..8dfcc12df --- /dev/null +++ b/SQL/postgres/2010100600.sql @@ -0,0 +1,7 @@ +-- Updates from version 0.4.2 + +DROP INDEX users_username_id_idx; +ALTER TABLE users ADD CONSTRAINT users_username_key UNIQUE (username, mail_host); +ALTER TABLE contacts ALTER email TYPE varchar(255); + +TRUNCATE messages; diff --git a/SQL/postgres/2011011200.sql b/SQL/postgres/2011011200.sql new file mode 100644 index 000000000..db468d8e8 --- /dev/null +++ b/SQL/postgres/2011011200.sql @@ -0,0 +1,7 @@ +-- Updates from version 0.5.x + +ALTER TABLE contacts ADD words TEXT NULL; +CREATE INDEX contactgroupmembers_contact_id_idx ON contactgroupmembers (contact_id); + +TRUNCATE messages; +TRUNCATE cache; diff --git a/SQL/postgres/2011092800.sql b/SQL/postgres/2011092800.sql new file mode 100644 index 000000000..fac3cd371 --- /dev/null +++ b/SQL/postgres/2011092800.sql @@ -0,0 +1,64 @@ +-- Updates from version 0.6 + +CREATE TABLE dictionary ( + user_id integer DEFAULT NULL + REFERENCES users (user_id) ON DELETE CASCADE ON UPDATE CASCADE, + "language" varchar(5) NOT NULL, + data text NOT NULL, + CONSTRAINT dictionary_user_id_language_key UNIQUE (user_id, "language") +); + +CREATE SEQUENCE search_ids + INCREMENT BY 1 + NO MAXVALUE + NO MINVALUE + CACHE 1; + +CREATE TABLE searches ( + search_id integer DEFAULT nextval('search_ids'::text) PRIMARY KEY, + user_id integer NOT NULL + REFERENCES users (user_id) ON DELETE CASCADE ON UPDATE CASCADE, + "type" smallint DEFAULT 0 NOT NULL, + name varchar(128) NOT NULL, + data text NOT NULL, + CONSTRAINT searches_user_id_key UNIQUE (user_id, "type", name) +); + +DROP SEQUENCE message_ids; +DROP TABLE messages; + +CREATE TABLE cache_index ( + user_id integer NOT NULL + REFERENCES users (user_id) ON DELETE CASCADE ON UPDATE CASCADE, + mailbox varchar(255) NOT NULL, + changed timestamp with time zone DEFAULT now() NOT NULL, + valid smallint NOT NULL DEFAULT 0, + data text NOT NULL, + PRIMARY KEY (user_id, mailbox) +); + +CREATE INDEX cache_index_changed_idx ON cache_index (changed); + +CREATE TABLE cache_thread ( + user_id integer NOT NULL + REFERENCES users (user_id) ON DELETE CASCADE ON UPDATE CASCADE, + mailbox varchar(255) NOT NULL, + changed timestamp with time zone DEFAULT now() NOT NULL, + data text NOT NULL, + PRIMARY KEY (user_id, mailbox) +); + +CREATE INDEX cache_thread_changed_idx ON cache_thread (changed); + +CREATE TABLE cache_messages ( + user_id integer NOT NULL + REFERENCES users (user_id) ON DELETE CASCADE ON UPDATE CASCADE, + mailbox varchar(255) NOT NULL, + uid integer NOT NULL, + changed timestamp with time zone DEFAULT now() NOT NULL, + data text NOT NULL, + flags integer NOT NULL DEFAULT 0, + PRIMARY KEY (user_id, mailbox, uid) +); + +CREATE INDEX cache_messages_changed_idx ON cache_messages (changed); diff --git a/SQL/postgres/2011111600.sql b/SQL/postgres/2011111600.sql new file mode 100644 index 000000000..a382ba0ba --- /dev/null +++ b/SQL/postgres/2011111600.sql @@ -0,0 +1,3 @@ +-- Updates from version 0.7-beta + +ALTER TABLE "session" ALTER sess_id TYPE varchar(128); diff --git a/SQL/postgres/2011121400.sql b/SQL/postgres/2011121400.sql new file mode 100644 index 000000000..81a0cb880 --- /dev/null +++ b/SQL/postgres/2011121400.sql @@ -0,0 +1,5 @@ +-- Updates from version 0.7 + +DROP INDEX contacts_user_id_idx; +CREATE INDEX contacts_user_id_idx ON contacts USING btree (user_id, del); +ALTER TABLE contacts ALTER email TYPE text; diff --git a/SQL/postgres/2012080700.sql b/SQL/postgres/2012080700.sql new file mode 100644 index 000000000..41f60168e --- /dev/null +++ b/SQL/postgres/2012080700.sql @@ -0,0 +1,7 @@ +-- Updates from version 0.8 + +ALTER TABLE cache DROP COLUMN cache_id; +DROP SEQUENCE cache_ids; + +ALTER TABLE users DROP COLUMN alias; +CREATE INDEX identities_email_idx ON identities (email, del); diff --git a/SQL/postgres/2013011000.sql b/SQL/postgres/2013011000.sql new file mode 100644 index 000000000..a8cf917d0 --- /dev/null +++ b/SQL/postgres/2013011000.sql @@ -0,0 +1,4 @@ +CREATE TABLE "system" ( + name varchar(64) NOT NULL PRIMARY KEY, + value text +); diff --git a/SQL/sqlite.initial.sql b/SQL/sqlite.initial.sql index f5b5615d8..76913e89c 100644 --- a/SQL/sqlite.initial.sql +++ b/SQL/sqlite.initial.sql @@ -197,3 +197,16 @@ CREATE TABLE cache_messages ( ); CREATE INDEX ix_cache_messages_changed ON cache_messages (changed); + +-- -------------------------------------------------------- + +-- +-- Table structure for table system +-- + +CREATE TABLE system ( + name varchar(64) NOT NULL PRIMARY KEY, + value text NOT NULL +); + +INSERT INTO system (name, value) VALUES ('roundcube-version', '2013011000.sql'); diff --git a/SQL/sqlite.update.sql b/SQL/sqlite.update.sql deleted file mode 100644 index 72a29e9ae..000000000 --- a/SQL/sqlite.update.sql +++ /dev/null @@ -1,380 +0,0 @@ --- Roundcube Webmail update script for SQLite databases --- Updates from version 0.1-stable to 0.1.1 - -DROP TABLE messages; - -CREATE TABLE messages ( - message_id integer NOT NULL PRIMARY KEY, - user_id integer NOT NULL default '0', - del tinyint NOT NULL default '0', - cache_key varchar(128) NOT NULL default '', - created datetime NOT NULL default '0000-00-00 00:00:00', - idx integer NOT NULL default '0', - uid integer NOT NULL default '0', - subject varchar(255) NOT NULL default '', - "from" varchar(255) NOT NULL default '', - "to" varchar(255) NOT NULL default '', - "cc" varchar(255) NOT NULL default '', - "date" datetime NOT NULL default '0000-00-00 00:00:00', - size integer NOT NULL default '0', - headers text NOT NULL, - structure text -); - -CREATE INDEX ix_messages_user_cache_uid ON messages(user_id,cache_key,uid); -CREATE INDEX ix_users_username ON users(username); -CREATE INDEX ix_users_alias ON users(alias); - --- Updates from version 0.2-alpha - -CREATE INDEX ix_messages_created ON messages (created); - --- Updates from version 0.2-beta - -CREATE INDEX ix_session_changed ON session (changed); -CREATE INDEX ix_cache_created ON cache (created); - --- Updates from version 0.3-stable - -DELETE FROM messages; -DROP INDEX ix_messages_user_cache_uid; -CREATE UNIQUE INDEX ix_messages_user_cache_uid ON messages (user_id,cache_key,uid); -CREATE INDEX ix_messages_index ON messages (user_id,cache_key,idx); -DROP INDEX ix_contacts_user_id; -CREATE INDEX ix_contacts_user_id ON contacts(user_id, email); - --- Updates from version 0.3.1 - --- ALTER TABLE identities ADD COLUMN changed datetime NOT NULL default '0000-00-00 00:00:00'; -- - -CREATE TABLE temp_identities ( - identity_id integer NOT NULL PRIMARY KEY, - user_id integer NOT NULL default '0', - standard tinyint NOT NULL default '0', - name varchar(128) NOT NULL default '', - organization varchar(128) default '', - email varchar(128) NOT NULL default '', - "reply-to" varchar(128) NOT NULL default '', - bcc varchar(128) NOT NULL default '', - signature text NOT NULL default '', - html_signature tinyint NOT NULL default '0' -); -INSERT INTO temp_identities (identity_id, user_id, standard, name, organization, email, "reply-to", bcc, signature, html_signature) - SELECT identity_id, user_id, standard, name, organization, email, "reply-to", bcc, signature, html_signature - FROM identities WHERE del=0; - -DROP INDEX ix_identities_user_id; -DROP TABLE identities; - -CREATE TABLE identities ( - identity_id integer NOT NULL PRIMARY KEY, - user_id integer NOT NULL default '0', - changed datetime NOT NULL default '0000-00-00 00:00:00', - del tinyint NOT NULL default '0', - standard tinyint NOT NULL default '0', - name varchar(128) NOT NULL default '', - organization varchar(128) default '', - email varchar(128) NOT NULL default '', - "reply-to" varchar(128) NOT NULL default '', - bcc varchar(128) NOT NULL default '', - signature text NOT NULL default '', - html_signature tinyint NOT NULL default '0' -); -CREATE INDEX ix_identities_user_id ON identities(user_id, del); - -INSERT INTO identities (identity_id, user_id, standard, name, organization, email, "reply-to", bcc, signature, html_signature) - SELECT identity_id, user_id, standard, name, organization, email, "reply-to", bcc, signature, html_signature - FROM temp_identities; - -DROP TABLE temp_identities; - -CREATE TABLE contactgroups ( - contactgroup_id integer NOT NULL PRIMARY KEY, - user_id integer NOT NULL default '0', - changed datetime NOT NULL default '0000-00-00 00:00:00', - del tinyint NOT NULL default '0', - name varchar(128) NOT NULL default '' -); - -CREATE INDEX ix_contactgroups_user_id ON contactgroups(user_id, del); - -CREATE TABLE contactgroupmembers ( - contactgroup_id integer NOT NULL, - contact_id integer NOT NULL default '0', - created datetime NOT NULL default '0000-00-00 00:00:00', - PRIMARY KEY (contactgroup_id, contact_id) -); - --- Updates from version 0.3.1 - -CREATE TABLE tmp_users ( - user_id integer NOT NULL PRIMARY KEY, - username varchar(128) NOT NULL default '', - mail_host varchar(128) NOT NULL default '', - alias varchar(128) NOT NULL default '', - created datetime NOT NULL default '0000-00-00 00:00:00', - last_login datetime NOT NULL default '0000-00-00 00:00:00', - language varchar(5), - preferences text NOT NULL default '' -); - -INSERT INTO tmp_users (user_id, username, mail_host, alias, created, last_login, language, preferences) - SELECT user_id, username, mail_host, alias, created, last_login, language, preferences FROM users; - -DROP TABLE users; - -CREATE TABLE users ( - user_id integer NOT NULL PRIMARY KEY, - username varchar(128) NOT NULL default '', - mail_host varchar(128) NOT NULL default '', - alias varchar(128) NOT NULL default '', - created datetime NOT NULL default '0000-00-00 00:00:00', - last_login datetime DEFAULT NULL, - language varchar(5), - preferences text NOT NULL default '' -); - -INSERT INTO users (user_id, username, mail_host, alias, created, last_login, language, preferences) - SELECT user_id, username, mail_host, alias, created, last_login, language, preferences FROM tmp_users; - -CREATE INDEX ix_users_username ON users(username); -CREATE INDEX ix_users_alias ON users(alias); -DROP TABLE tmp_users; - --- Updates from version 0.4.2 - -DROP INDEX ix_users_username; -CREATE UNIQUE INDEX ix_users_username ON users(username, mail_host); - -CREATE TABLE contacts_tmp ( - contact_id integer NOT NULL PRIMARY KEY, - user_id integer NOT NULL default '0', - changed datetime NOT NULL default '0000-00-00 00:00:00', - del tinyint NOT NULL default '0', - name varchar(128) NOT NULL default '', - email varchar(255) NOT NULL default '', - firstname varchar(128) NOT NULL default '', - surname varchar(128) NOT NULL default '', - vcard text NOT NULL default '' -); - -INSERT INTO contacts_tmp (contact_id, user_id, changed, del, name, email, firstname, surname, vcard) - SELECT contact_id, user_id, changed, del, name, email, firstname, surname, vcard FROM contacts; - -DROP TABLE contacts; -CREATE TABLE contacts ( - contact_id integer NOT NULL PRIMARY KEY, - user_id integer NOT NULL default '0', - changed datetime NOT NULL default '0000-00-00 00:00:00', - del tinyint NOT NULL default '0', - name varchar(128) NOT NULL default '', - email varchar(255) NOT NULL default '', - firstname varchar(128) NOT NULL default '', - surname varchar(128) NOT NULL default '', - vcard text NOT NULL default '' -); - -INSERT INTO contacts (contact_id, user_id, changed, del, name, email, firstname, surname, vcard) - SELECT contact_id, user_id, changed, del, name, email, firstname, surname, vcard FROM contacts_tmp; - -CREATE INDEX ix_contacts_user_id ON contacts(user_id, email); -DROP TABLE contacts_tmp; - -DELETE FROM messages; - - --- Updates from version 0.5.1 --- Updates from version 0.5.2 --- Updates from version 0.5.3 --- Updates from version 0.5.4 - -CREATE TABLE contacts_tmp ( - contact_id integer NOT NULL PRIMARY KEY, - user_id integer NOT NULL default '0', - changed datetime NOT NULL default '0000-00-00 00:00:00', - del tinyint NOT NULL default '0', - name varchar(128) NOT NULL default '', - email varchar(255) NOT NULL default '', - firstname varchar(128) NOT NULL default '', - surname varchar(128) NOT NULL default '', - vcard text NOT NULL default '' -); - -INSERT INTO contacts_tmp (contact_id, user_id, changed, del, name, email, firstname, surname, vcard) - SELECT contact_id, user_id, changed, del, name, email, firstname, surname, vcard FROM contacts; - -DROP TABLE contacts; -CREATE TABLE contacts ( - contact_id integer NOT NULL PRIMARY KEY, - user_id integer NOT NULL default '0', - changed datetime NOT NULL default '0000-00-00 00:00:00', - del tinyint NOT NULL default '0', - name varchar(128) NOT NULL default '', - email varchar(255) NOT NULL default '', - firstname varchar(128) NOT NULL default '', - surname varchar(128) NOT NULL default '', - vcard text NOT NULL default '', - words text NOT NULL default '' -); - -INSERT INTO contacts (contact_id, user_id, changed, del, name, email, firstname, surname, vcard) - SELECT contact_id, user_id, changed, del, name, email, firstname, surname, vcard FROM contacts_tmp; - -CREATE INDEX ix_contacts_user_id ON contacts(user_id, email); -DROP TABLE contacts_tmp; - - -DELETE FROM messages; -DELETE FROM cache; -CREATE INDEX ix_contactgroupmembers_contact_id ON contactgroupmembers (contact_id); - --- Updates from version 0.6 - -CREATE TABLE dictionary ( - user_id integer DEFAULT NULL, - "language" varchar(5) NOT NULL, - data text NOT NULL -); - -CREATE UNIQUE INDEX ix_dictionary_user_language ON dictionary (user_id, "language"); - -CREATE TABLE searches ( - search_id integer NOT NULL PRIMARY KEY, - user_id integer NOT NULL DEFAULT '0', - "type" smallint NOT NULL DEFAULT '0', - name varchar(128) NOT NULL, - data text NOT NULL -); - -CREATE UNIQUE INDEX ix_searches_user_type_name ON searches (user_id, type, name); - -DROP TABLE messages; - -CREATE TABLE cache_index ( - user_id integer NOT NULL, - mailbox varchar(255) NOT NULL, - changed datetime NOT NULL default '0000-00-00 00:00:00', - valid smallint NOT NULL DEFAULT '0', - data text NOT NULL, - PRIMARY KEY (user_id, mailbox) -); - -CREATE INDEX ix_cache_index_changed ON cache_index (changed); - -CREATE TABLE cache_thread ( - user_id integer NOT NULL, - mailbox varchar(255) NOT NULL, - changed datetime NOT NULL default '0000-00-00 00:00:00', - data text NOT NULL, - PRIMARY KEY (user_id, mailbox) -); - -CREATE INDEX ix_cache_thread_changed ON cache_thread (changed); - -CREATE TABLE cache_messages ( - user_id integer NOT NULL, - mailbox varchar(255) NOT NULL, - uid integer NOT NULL, - changed datetime NOT NULL default '0000-00-00 00:00:00', - data text NOT NULL, - flags integer NOT NULL DEFAULT '0', - PRIMARY KEY (user_id, mailbox, uid) -); - -CREATE INDEX ix_cache_messages_changed ON cache_messages (changed); - --- Updates from version 0.7-beta - -DROP TABLE session; -CREATE TABLE session ( - sess_id varchar(128) NOT NULL PRIMARY KEY, - created datetime NOT NULL default '0000-00-00 00:00:00', - changed datetime NOT NULL default '0000-00-00 00:00:00', - ip varchar(40) NOT NULL default '', - vars text NOT NULL -); -CREATE INDEX ix_session_changed ON session (changed); - --- Updates from version 0.7 - -CREATE TABLE contacts_tmp ( - contact_id integer NOT NULL PRIMARY KEY, - user_id integer NOT NULL, - changed datetime NOT NULL default '0000-00-00 00:00:00', - del tinyint NOT NULL default '0', - name varchar(128) NOT NULL default '', - email text NOT NULL default '', - firstname varchar(128) NOT NULL default '', - surname varchar(128) NOT NULL default '', - vcard text NOT NULL default '', - words text NOT NULL default '' -); - -INSERT INTO contacts_tmp (contact_id, user_id, changed, del, name, email, firstname, surname, vcard, words) - SELECT contact_id, user_id, changed, del, name, email, firstname, surname, vcard, words FROM contacts; - -DROP TABLE contacts; - -CREATE TABLE contacts ( - contact_id integer NOT NULL PRIMARY KEY, - user_id integer NOT NULL, - changed datetime NOT NULL default '0000-00-00 00:00:00', - del tinyint NOT NULL default '0', - name varchar(128) NOT NULL default '', - email text NOT NULL default '', - firstname varchar(128) NOT NULL default '', - surname varchar(128) NOT NULL default '', - vcard text NOT NULL default '', - words text NOT NULL default '' -); - -INSERT INTO contacts (contact_id, user_id, changed, del, name, email, firstname, surname, vcard, words) - SELECT contact_id, user_id, changed, del, name, email, firstname, surname, vcard, words FROM contacts_tmp; - -CREATE INDEX ix_contacts_user_id ON contacts(user_id, del); -DROP TABLE contacts_tmp; - --- Updates from version 0.8 - -DROP TABLE cache; -CREATE TABLE cache ( - user_id integer NOT NULL default 0, - cache_key varchar(128) NOT NULL default '', - created datetime NOT NULL default '0000-00-00 00:00:00', - data text NOT NULL -); - -CREATE INDEX ix_cache_user_cache_key ON cache(user_id, cache_key); -CREATE INDEX ix_cache_created ON cache(created); - -CREATE TABLE tmp_users ( - user_id integer NOT NULL PRIMARY KEY, - username varchar(128) NOT NULL default '', - mail_host varchar(128) NOT NULL default '', - created datetime NOT NULL default '0000-00-00 00:00:00', - last_login datetime DEFAULT NULL, - language varchar(5), - preferences text NOT NULL default '' -); - -INSERT INTO tmp_users (user_id, username, mail_host, created, last_login, language, preferences) - SELECT user_id, username, mail_host, created, last_login, language, preferences FROM users; - -DROP TABLE users; - -CREATE TABLE users ( - user_id integer NOT NULL PRIMARY KEY, - username varchar(128) NOT NULL default '', - mail_host varchar(128) NOT NULL default '', - created datetime NOT NULL default '0000-00-00 00:00:00', - last_login datetime DEFAULT NULL, - language varchar(5), - preferences text NOT NULL default '' -); - -INSERT INTO users (user_id, username, mail_host, created, last_login, language, preferences) - SELECT user_id, username, mail_host, created, last_login, language, preferences FROM tmp_users; - -CREATE UNIQUE INDEX ix_users_username ON users(username, mail_host); - -CREATE INDEX ix_identities_email ON identities(email, del); diff --git a/SQL/sqlite/2008030300.sql b/SQL/sqlite/2008030300.sql new file mode 100644 index 000000000..b5b4890bf --- /dev/null +++ b/SQL/sqlite/2008030300.sql @@ -0,0 +1,25 @@ +-- Updates from version 0.1-stable to 0.1.1 + +DROP TABLE messages; + +CREATE TABLE messages ( + message_id integer NOT NULL PRIMARY KEY, + user_id integer NOT NULL default '0', + del tinyint NOT NULL default '0', + cache_key varchar(128) NOT NULL default '', + created datetime NOT NULL default '0000-00-00 00:00:00', + idx integer NOT NULL default '0', + uid integer NOT NULL default '0', + subject varchar(255) NOT NULL default '', + "from" varchar(255) NOT NULL default '', + "to" varchar(255) NOT NULL default '', + "cc" varchar(255) NOT NULL default '', + "date" datetime NOT NULL default '0000-00-00 00:00:00', + size integer NOT NULL default '0', + headers text NOT NULL, + structure text +); + +CREATE INDEX ix_messages_user_cache_uid ON messages(user_id,cache_key,uid); +CREATE INDEX ix_users_username ON users(username); +CREATE INDEX ix_users_alias ON users(alias); diff --git a/SQL/sqlite/2008060900.sql b/SQL/sqlite/2008060900.sql new file mode 100644 index 000000000..157029c28 --- /dev/null +++ b/SQL/sqlite/2008060900.sql @@ -0,0 +1,3 @@ +-- Updates from version 0.2-alpha + +CREATE INDEX ix_messages_created ON messages (created); diff --git a/SQL/sqlite/2008092100.sql b/SQL/sqlite/2008092100.sql new file mode 100644 index 000000000..8bc081c79 --- /dev/null +++ b/SQL/sqlite/2008092100.sql @@ -0,0 +1,4 @@ +-- Updates from version 0.2-beta + +CREATE INDEX ix_session_changed ON session (changed); +CREATE INDEX ix_cache_created ON cache (created); diff --git a/SQL/sqlite/2009090400.sql b/SQL/sqlite/2009090400.sql new file mode 100644 index 000000000..c8a53edf1 --- /dev/null +++ b/SQL/sqlite/2009090400.sql @@ -0,0 +1,8 @@ +-- Updates from version 0.3-stable + +DELETE FROM messages; +DROP INDEX ix_messages_user_cache_uid; +CREATE UNIQUE INDEX ix_messages_user_cache_uid ON messages (user_id,cache_key,uid); +CREATE INDEX ix_messages_index ON messages (user_id,cache_key,idx); +DROP INDEX ix_contacts_user_id; +CREATE INDEX ix_contacts_user_id ON contacts(user_id, email); diff --git a/SQL/sqlite/2009103100.sql b/SQL/sqlite/2009103100.sql new file mode 100644 index 000000000..bd7b1741c --- /dev/null +++ b/SQL/sqlite/2009103100.sql @@ -0,0 +1,61 @@ +-- Updates from version 0.3.1 + +-- ALTER TABLE identities ADD COLUMN changed datetime NOT NULL default '0000-00-00 00:00:00'; -- + +CREATE TABLE temp_identities ( + identity_id integer NOT NULL PRIMARY KEY, + user_id integer NOT NULL default '0', + standard tinyint NOT NULL default '0', + name varchar(128) NOT NULL default '', + organization varchar(128) default '', + email varchar(128) NOT NULL default '', + "reply-to" varchar(128) NOT NULL default '', + bcc varchar(128) NOT NULL default '', + signature text NOT NULL default '', + html_signature tinyint NOT NULL default '0' +); +INSERT INTO temp_identities (identity_id, user_id, standard, name, organization, email, "reply-to", bcc, signature, html_signature) + SELECT identity_id, user_id, standard, name, organization, email, "reply-to", bcc, signature, html_signature + FROM identities WHERE del=0; + +DROP INDEX ix_identities_user_id; +DROP TABLE identities; + +CREATE TABLE identities ( + identity_id integer NOT NULL PRIMARY KEY, + user_id integer NOT NULL default '0', + changed datetime NOT NULL default '0000-00-00 00:00:00', + del tinyint NOT NULL default '0', + standard tinyint NOT NULL default '0', + name varchar(128) NOT NULL default '', + organization varchar(128) default '', + email varchar(128) NOT NULL default '', + "reply-to" varchar(128) NOT NULL default '', + bcc varchar(128) NOT NULL default '', + signature text NOT NULL default '', + html_signature tinyint NOT NULL default '0' +); +CREATE INDEX ix_identities_user_id ON identities(user_id, del); + +INSERT INTO identities (identity_id, user_id, standard, name, organization, email, "reply-to", bcc, signature, html_signature) + SELECT identity_id, user_id, standard, name, organization, email, "reply-to", bcc, signature, html_signature + FROM temp_identities; + +DROP TABLE temp_identities; + +CREATE TABLE contactgroups ( + contactgroup_id integer NOT NULL PRIMARY KEY, + user_id integer NOT NULL default '0', + changed datetime NOT NULL default '0000-00-00 00:00:00', + del tinyint NOT NULL default '0', + name varchar(128) NOT NULL default '' +); + +CREATE INDEX ix_contactgroups_user_id ON contactgroups(user_id, del); + +CREATE TABLE contactgroupmembers ( + contactgroup_id integer NOT NULL, + contact_id integer NOT NULL default '0', + created datetime NOT NULL default '0000-00-00 00:00:00', + PRIMARY KEY (contactgroup_id, contact_id) +); diff --git a/SQL/sqlite/2010042300.sql b/SQL/sqlite/2010042300.sql new file mode 100644 index 000000000..1a9023cfc --- /dev/null +++ b/SQL/sqlite/2010042300.sql @@ -0,0 +1,35 @@ +-- Updates from version 0.4-beta + +CREATE TABLE tmp_users ( + user_id integer NOT NULL PRIMARY KEY, + username varchar(128) NOT NULL default '', + mail_host varchar(128) NOT NULL default '', + alias varchar(128) NOT NULL default '', + created datetime NOT NULL default '0000-00-00 00:00:00', + last_login datetime NOT NULL default '0000-00-00 00:00:00', + language varchar(5), + preferences text NOT NULL default '' +); + +INSERT INTO tmp_users (user_id, username, mail_host, alias, created, last_login, language, preferences) + SELECT user_id, username, mail_host, alias, created, last_login, language, preferences FROM users; + +DROP TABLE users; + +CREATE TABLE users ( + user_id integer NOT NULL PRIMARY KEY, + username varchar(128) NOT NULL default '', + mail_host varchar(128) NOT NULL default '', + alias varchar(128) NOT NULL default '', + created datetime NOT NULL default '0000-00-00 00:00:00', + last_login datetime DEFAULT NULL, + language varchar(5), + preferences text NOT NULL default '' +); + +INSERT INTO users (user_id, username, mail_host, alias, created, last_login, language, preferences) + SELECT user_id, username, mail_host, alias, created, last_login, language, preferences FROM tmp_users; + +CREATE INDEX ix_users_username ON users(username); +CREATE INDEX ix_users_alias ON users(alias); +DROP TABLE tmp_users; diff --git a/SQL/sqlite/2010100600.sql b/SQL/sqlite/2010100600.sql new file mode 100644 index 000000000..5c7259f37 --- /dev/null +++ b/SQL/sqlite/2010100600.sql @@ -0,0 +1,40 @@ +-- Updates from version 0.4.2 + +DROP INDEX ix_users_username; +CREATE UNIQUE INDEX ix_users_username ON users(username, mail_host); + +CREATE TABLE contacts_tmp ( + contact_id integer NOT NULL PRIMARY KEY, + user_id integer NOT NULL default '0', + changed datetime NOT NULL default '0000-00-00 00:00:00', + del tinyint NOT NULL default '0', + name varchar(128) NOT NULL default '', + email varchar(255) NOT NULL default '', + firstname varchar(128) NOT NULL default '', + surname varchar(128) NOT NULL default '', + vcard text NOT NULL default '' +); + +INSERT INTO contacts_tmp (contact_id, user_id, changed, del, name, email, firstname, surname, vcard) + SELECT contact_id, user_id, changed, del, name, email, firstname, surname, vcard FROM contacts; + +DROP TABLE contacts; +CREATE TABLE contacts ( + contact_id integer NOT NULL PRIMARY KEY, + user_id integer NOT NULL default '0', + changed datetime NOT NULL default '0000-00-00 00:00:00', + del tinyint NOT NULL default '0', + name varchar(128) NOT NULL default '', + email varchar(255) NOT NULL default '', + firstname varchar(128) NOT NULL default '', + surname varchar(128) NOT NULL default '', + vcard text NOT NULL default '' +); + +INSERT INTO contacts (contact_id, user_id, changed, del, name, email, firstname, surname, vcard) + SELECT contact_id, user_id, changed, del, name, email, firstname, surname, vcard FROM contacts_tmp; + +CREATE INDEX ix_contacts_user_id ON contacts(user_id, email); +DROP TABLE contacts_tmp; + +DELETE FROM messages; diff --git a/SQL/sqlite/2011011200.sql b/SQL/sqlite/2011011200.sql new file mode 100644 index 000000000..4cca74076 --- /dev/null +++ b/SQL/sqlite/2011011200.sql @@ -0,0 +1,41 @@ +-- Updates from version 0.5.x + +CREATE TABLE contacts_tmp ( + contact_id integer NOT NULL PRIMARY KEY, + user_id integer NOT NULL default '0', + changed datetime NOT NULL default '0000-00-00 00:00:00', + del tinyint NOT NULL default '0', + name varchar(128) NOT NULL default '', + email varchar(255) NOT NULL default '', + firstname varchar(128) NOT NULL default '', + surname varchar(128) NOT NULL default '', + vcard text NOT NULL default '' +); + +INSERT INTO contacts_tmp (contact_id, user_id, changed, del, name, email, firstname, surname, vcard) + SELECT contact_id, user_id, changed, del, name, email, firstname, surname, vcard FROM contacts; + +DROP TABLE contacts; +CREATE TABLE contacts ( + contact_id integer NOT NULL PRIMARY KEY, + user_id integer NOT NULL default '0', + changed datetime NOT NULL default '0000-00-00 00:00:00', + del tinyint NOT NULL default '0', + name varchar(128) NOT NULL default '', + email varchar(255) NOT NULL default '', + firstname varchar(128) NOT NULL default '', + surname varchar(128) NOT NULL default '', + vcard text NOT NULL default '', + words text NOT NULL default '' +); + +INSERT INTO contacts (contact_id, user_id, changed, del, name, email, firstname, surname, vcard) + SELECT contact_id, user_id, changed, del, name, email, firstname, surname, vcard FROM contacts_tmp; + +CREATE INDEX ix_contacts_user_id ON contacts(user_id, email); +DROP TABLE contacts_tmp; + + +DELETE FROM messages; +DELETE FROM cache; +CREATE INDEX ix_contactgroupmembers_contact_id ON contactgroupmembers (contact_id); diff --git a/SQL/sqlite/2011092800.sql b/SQL/sqlite/2011092800.sql new file mode 100644 index 000000000..27dbffc1d --- /dev/null +++ b/SQL/sqlite/2011092800.sql @@ -0,0 +1,54 @@ +-- Updates from version 0.6 + +CREATE TABLE dictionary ( + user_id integer DEFAULT NULL, + "language" varchar(5) NOT NULL, + data text NOT NULL +); + +CREATE UNIQUE INDEX ix_dictionary_user_language ON dictionary (user_id, "language"); + +CREATE TABLE searches ( + search_id integer NOT NULL PRIMARY KEY, + user_id integer NOT NULL DEFAULT '0', + "type" smallint NOT NULL DEFAULT '0', + name varchar(128) NOT NULL, + data text NOT NULL +); + +CREATE UNIQUE INDEX ix_searches_user_type_name ON searches (user_id, type, name); + +DROP TABLE messages; + +CREATE TABLE cache_index ( + user_id integer NOT NULL, + mailbox varchar(255) NOT NULL, + changed datetime NOT NULL default '0000-00-00 00:00:00', + valid smallint NOT NULL DEFAULT '0', + data text NOT NULL, + PRIMARY KEY (user_id, mailbox) +); + +CREATE INDEX ix_cache_index_changed ON cache_index (changed); + +CREATE TABLE cache_thread ( + user_id integer NOT NULL, + mailbox varchar(255) NOT NULL, + changed datetime NOT NULL default '0000-00-00 00:00:00', + data text NOT NULL, + PRIMARY KEY (user_id, mailbox) +); + +CREATE INDEX ix_cache_thread_changed ON cache_thread (changed); + +CREATE TABLE cache_messages ( + user_id integer NOT NULL, + mailbox varchar(255) NOT NULL, + uid integer NOT NULL, + changed datetime NOT NULL default '0000-00-00 00:00:00', + data text NOT NULL, + flags integer NOT NULL DEFAULT '0', + PRIMARY KEY (user_id, mailbox, uid) +); + +CREATE INDEX ix_cache_messages_changed ON cache_messages (changed); diff --git a/SQL/sqlite/2011111600.sql b/SQL/sqlite/2011111600.sql new file mode 100644 index 000000000..cea0d129e --- /dev/null +++ b/SQL/sqlite/2011111600.sql @@ -0,0 +1,11 @@ +-- Updates from version 0.7-beta + +DROP TABLE session; +CREATE TABLE session ( + sess_id varchar(128) NOT NULL PRIMARY KEY, + created datetime NOT NULL default '0000-00-00 00:00:00', + changed datetime NOT NULL default '0000-00-00 00:00:00', + ip varchar(40) NOT NULL default '', + vars text NOT NULL +); +CREATE INDEX ix_session_changed ON session (changed); diff --git a/SQL/sqlite/2011121400.sql b/SQL/sqlite/2011121400.sql new file mode 100644 index 000000000..9d9227673 --- /dev/null +++ b/SQL/sqlite/2011121400.sql @@ -0,0 +1,38 @@ +-- Updates from version 0.7 + +CREATE TABLE contacts_tmp ( + contact_id integer NOT NULL PRIMARY KEY, + user_id integer NOT NULL, + changed datetime NOT NULL default '0000-00-00 00:00:00', + del tinyint NOT NULL default '0', + name varchar(128) NOT NULL default '', + email text NOT NULL default '', + firstname varchar(128) NOT NULL default '', + surname varchar(128) NOT NULL default '', + vcard text NOT NULL default '', + words text NOT NULL default '' +); + +INSERT INTO contacts_tmp (contact_id, user_id, changed, del, name, email, firstname, surname, vcard, words) + SELECT contact_id, user_id, changed, del, name, email, firstname, surname, vcard, words FROM contacts; + +DROP TABLE contacts; + +CREATE TABLE contacts ( + contact_id integer NOT NULL PRIMARY KEY, + user_id integer NOT NULL, + changed datetime NOT NULL default '0000-00-00 00:00:00', + del tinyint NOT NULL default '0', + name varchar(128) NOT NULL default '', + email text NOT NULL default '', + firstname varchar(128) NOT NULL default '', + surname varchar(128) NOT NULL default '', + vcard text NOT NULL default '', + words text NOT NULL default '' +); + +INSERT INTO contacts (contact_id, user_id, changed, del, name, email, firstname, surname, vcard, words) + SELECT contact_id, user_id, changed, del, name, email, firstname, surname, vcard, words FROM contacts_tmp; + +CREATE INDEX ix_contacts_user_id ON contacts(user_id, del); +DROP TABLE contacts_tmp; diff --git a/SQL/sqlite/2012080700.sql b/SQL/sqlite/2012080700.sql new file mode 100644 index 000000000..c6ede89e8 --- /dev/null +++ b/SQL/sqlite/2012080700.sql @@ -0,0 +1,44 @@ +-- Updates from version 0.8 + +DROP TABLE cache; +CREATE TABLE cache ( + user_id integer NOT NULL default 0, + cache_key varchar(128) NOT NULL default '', + created datetime NOT NULL default '0000-00-00 00:00:00', + data text NOT NULL +); + +CREATE INDEX ix_cache_user_cache_key ON cache(user_id, cache_key); +CREATE INDEX ix_cache_created ON cache(created); + +CREATE TABLE tmp_users ( + user_id integer NOT NULL PRIMARY KEY, + username varchar(128) NOT NULL default '', + mail_host varchar(128) NOT NULL default '', + created datetime NOT NULL default '0000-00-00 00:00:00', + last_login datetime DEFAULT NULL, + language varchar(5), + preferences text NOT NULL default '' +); + +INSERT INTO tmp_users (user_id, username, mail_host, created, last_login, language, preferences) + SELECT user_id, username, mail_host, created, last_login, language, preferences FROM users; + +DROP TABLE users; + +CREATE TABLE users ( + user_id integer NOT NULL PRIMARY KEY, + username varchar(128) NOT NULL default '', + mail_host varchar(128) NOT NULL default '', + created datetime NOT NULL default '0000-00-00 00:00:00', + last_login datetime DEFAULT NULL, + language varchar(5), + preferences text NOT NULL default '' +); + +INSERT INTO users (user_id, username, mail_host, created, last_login, language, preferences) + SELECT user_id, username, mail_host, created, last_login, language, preferences FROM tmp_users; + +CREATE UNIQUE INDEX ix_users_username ON users(username, mail_host); + +CREATE INDEX ix_identities_email ON identities(email, del); diff --git a/SQL/sqlite/2013011000.sql b/SQL/sqlite/2013011000.sql new file mode 100644 index 000000000..2c66ef95e --- /dev/null +++ b/SQL/sqlite/2013011000.sql @@ -0,0 +1,6 @@ +-- Updates from version 0.9-beta + +CREATE TABLE system ( + name varchar(64) NOT NULL PRIMARY KEY, + value text NOT NULL +); diff --git a/bin/update.sh b/bin/update.sh index 2015aa904..536bad9ba 100755 --- a/bin/update.sh +++ b/bin/update.sh @@ -124,7 +124,7 @@ if ($RCI->configured) { } } else { - echo "Please update your config files manually according to the above messages.\n\n"; + echo "Please update your config files manually according to the above messages.\n"; } } @@ -143,36 +143,18 @@ if ($RCI->configured) { // 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"; @@ -183,6 +165,4 @@ else { 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 @@ -49,6 +49,7 @@ $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'; 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 @@ -29,7 +29,6 @@ class rcube_install var $config = array(); var $configured = false; var $last_error = null; - var $db_map = array('pgsql' => 'postgres', 'mysqli' => 'mysql', 'sqlsrv' => 'mssql'); var $email_pattern = '([a-z0-9][a-z0-9\-\.\+\_]*@[a-z0-9]([a-z0-9\-][.]?)*[a-z0-9])'; var $bool_config_props = array(); @@ -609,7 +608,7 @@ class rcube_install */ function init_db($DB) { - $engine = isset($this->db_map[$DB->db_provider]) ? $this->db_map[$DB->db_provider] : $DB->db_provider; + $engine = $DB->db_provider; // read schema file from /SQL/* $fname = INSTALL_PATH . "SQL/$engine.initial.sql"; @@ -631,46 +630,18 @@ class rcube_install /** - * 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; } diff --git a/installer/test.php b/installer/test.php index b118924e4..bd49ac523 100644 --- a/installer/test.php +++ b/installer/test.php @@ -164,11 +164,9 @@ if ($db_working && $_POST['initdb']) { } else if ($db_working && $_POST['updatedb']) { - if (!($success = $RCI->update_db($DB, $_POST['version']))) { - $updatefile = INSTALL_PATH . 'SQL/' . (isset($RCI->db_map[$DB->db_provider]) ? $RCI->db_map[$DB->db_provider] : $DB->db_provider) . '.update.sql'; - echo '<p class="warning">Please manually execute the SQL statements from '.$updatefile.' on your database.<br/>'; - echo 'See comments in the file and execute queries below the comment with the currently installed version number.</p>'; - } + if (!($success = $RCI->update_db($_POST['version']))) { + echo '<p class="warning">Database schema update failed.</p>'; + } } // test database @@ -183,9 +181,8 @@ if ($db_working) { $RCI->fail('DB Schema', "Database schema differs"); echo '<ul style="margin:0"><li>' . join("</li>\n<li>", $err) . "</li></ul>"; $select = $RCI->versions_select(array('name' => 'version')); - echo '<p class="suggestion">You should run the update queries to get the schmea fixed.<br/><br/>Version to update from: ' . $select->show() . ' <input type="submit" name="updatedb" value="Update" /></p>'; -// echo '<p class="warning">Please manually execute the SQL statements from '.$updatefile.' on your database.<br/>'; -// echo 'See comments in the file and execute queries that are superscribed with the currently installed version number.</p>'; + $select->add('0.9 or newer', ''); + echo '<p class="suggestion">You should run the update queries to get the schema fixed.<br/><br/>Version to update from: ' . $select->show() . ' <input type="submit" name="updatedb" value="Update" /></p>'; $db_working = false; } else { diff --git a/program/lib/Roundcube/rcube_db.php b/program/lib/Roundcube/rcube_db.php index 3e4617948..086a38ab4 100644 --- a/program/lib/Roundcube/rcube_db.php +++ b/program/lib/Roundcube/rcube_db.php @@ -402,6 +402,7 @@ class rcube_db // destroy reference to previous result, required for SQLite driver (#1488874) $this->last_result = null; + $this->db_error_msg = null; // send query $query = $this->dbh->query($query); |