From b7e7c8f9501850a38705e0a1f18a8ae6e25f1be1 Mon Sep 17 00:00:00 2001 From: Aleksander Machniak Date: Wed, 9 Jan 2013 15:57:29 +0100 Subject: Added new database upgrade script, converted DDL scripts to new format --- SQL/mssql/2009103100.sql | 87 ++++++++++++++++++++++++++++++++ SQL/mssql/2010100600.sql | 9 ++++ SQL/mssql/2011011200.sql | 10 ++++ SQL/mssql/2011092800.sql | 127 +++++++++++++++++++++++++++++++++++++++++++++++ SQL/mssql/2011111600.sql | 4 ++ SQL/mssql/2011121400.sql | 9 ++++ SQL/mssql/2012051800.sql | 18 +++++++ SQL/mssql/2012080700.sql | 8 +++ SQL/mssql/2013011000.sql | 14 ++++++ 9 files changed, 286 insertions(+) create mode 100644 SQL/mssql/2009103100.sql create mode 100644 SQL/mssql/2010100600.sql create mode 100644 SQL/mssql/2011011200.sql create mode 100644 SQL/mssql/2011092800.sql create mode 100644 SQL/mssql/2011111600.sql create mode 100644 SQL/mssql/2011121400.sql create mode 100644 SQL/mssql/2012051800.sql create mode 100644 SQL/mssql/2012080700.sql create mode 100644 SQL/mssql/2013011000.sql (limited to 'SQL/mssql') 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 -- cgit v1.2.3