Skip to content
Newer
Older
100644 274 lines (222 sloc) 8.51 KB
e019f2d @alecpl - s/RoundCube/Roundcube/
alecpl authored
1 -- Roundcube Webmail update script for MSSQL databases
ac18981 @alecpl - Fixes in SQL init script + added update script for MSSQL database
alecpl authored
2
3 -- Updates from version 0.3.1
4
5 ALTER TABLE [dbo].[messages] ADD CONSTRAINT [FK_messages_user_id]
6 FOREIGN KEY ([user_id]) REFERENCES [dbo].[users] ([user_id])
7 ON DELETE CASCADE ON UPDATE CASCADE
8 GO
9
10 ALTER TABLE [dbo].[cache] ADD CONSTRAINT [FK_cache_user_id]
11 FOREIGN KEY ([user_id]) REFERENCES [dbo].[users] ([user_id])
12 ON DELETE CASCADE ON UPDATE CASCADE
13 GO
14
15 ALTER TABLE [dbo].[contacts] ADD CONSTRAINT [FK_contacts_user_id]
16 FOREIGN KEY ([user_id]) REFERENCES [dbo].[users] ([user_id])
17 ON DELETE CASCADE ON UPDATE CASCADE
18 GO
19
20 ALTER TABLE [dbo].[identities] ADD CONSTRAINT [FK_identities_user_id]
21 FOREIGN KEY ([user_id]) REFERENCES [dbo].[users] ([user_id])
22 ON DELETE CASCADE ON UPDATE CASCADE
23 GO
24
6f09681 @alecpl - Support contact's email addresses up to 255 characters long (#1487095)
alecpl authored
25 ALTER TABLE [dbo].[identities] ADD [changed] [datetime] NULL
ac18981 @alecpl - Fixes in SQL init script + added update script for MSSQL database
alecpl authored
26 GO
27
28 CREATE TABLE [dbo].[contactgroups] (
29 [contactgroup_id] [int] IDENTITY (1, 1) NOT NULL ,
30 [user_id] [int] NOT NULL ,
31 [changed] [datetime] NOT NULL ,
32 [del] [char] (1) COLLATE Latin1_General_CI_AI NOT NULL ,
33 [name] [varchar] (128) COLLATE Latin1_General_CI_AI NOT NULL
34 ) ON [PRIMARY]
35 GO
36
37 CREATE TABLE [dbo].[contactgroupmembers] (
38 [contactgroup_id] [int] NOT NULL ,
39 [contact_id] [int] NOT NULL ,
40 [created] [datetime] NOT NULL
41 ) ON [PRIMARY]
42 GO
43
44 ALTER TABLE [dbo].[contactgroups] WITH NOCHECK ADD
45 CONSTRAINT [PK_contactgroups_contactgroup_id] PRIMARY KEY CLUSTERED
46 (
47 [contactgroup_id]
48 ) ON [PRIMARY]
49 GO
50
51 ALTER TABLE [dbo].[contactgroupmembers] WITH NOCHECK ADD
52 CONSTRAINT [PK_contactgroupmembers_id] PRIMARY KEY CLUSTERED
53 (
54 [contactgroup_id], [contact_id]
55 ) ON [PRIMARY]
56 GO
57
58 ALTER TABLE [dbo].[contactgroups] ADD
59 CONSTRAINT [DF_contactgroups_user_id] DEFAULT (0) FOR [user_id],
60 CONSTRAINT [DF_contactgroups_changed] DEFAULT (getdate()) FOR [changed],
61 CONSTRAINT [DF_contactgroups_del] DEFAULT ('0') FOR [del],
62 CONSTRAINT [DF_contactgroups_name] DEFAULT ('') FOR [name],
63 CONSTRAINT [CK_contactgroups_del] CHECK ([del] = '1' or [del] = '0')
64 GO
65
66 CREATE INDEX [IX_contactgroups_user_id] ON [dbo].[contacts]([user_id]) ON [PRIMARY]
67 GO
68
69 ALTER TABLE [dbo].[contactgroupmembers] ADD
70 CONSTRAINT [DF_contactgroupmembers_contactgroup_id] DEFAULT (0) FOR [contactgroup_id],
71 CONSTRAINT [DF_contactgroupmembers_contact_id] DEFAULT (0) FOR [contact_id],
72 CONSTRAINT [DF_contactgroupmembers_created] DEFAULT (getdate()) FOR [created]
73 GO
74
75 ALTER TABLE [dbo].[contactgroupmembers] ADD CONSTRAINT [FK_contactgroupmembers_contactgroup_id]
76 FOREIGN KEY ([contactgroup_id]) REFERENCES [dbo].[contactgroups] ([contactgroup_id])
77 ON DELETE CASCADE ON UPDATE CASCADE
78 GO
79
8710133 @alecpl - Fix error in MSSQL DDL scripts (#1487112): use trigger instead of f…
alecpl authored
80 CREATE TRIGGER [contact_delete_member] ON [dbo].[contacts]
81 AFTER DELETE AS
82 DELETE FROM [dbo].[contactgroupmembers]
83 WHERE [contact_id] IN (SELECT [contact_id] FROM deleted)
ac18981 @alecpl - Fixes in SQL init script + added update script for MSSQL database
alecpl authored
84 GO
85
86 ALTER TABLE [dbo].[contactgroups] ADD CONSTRAINT [FK_contactgroups_user_id]
87 FOREIGN KEY ([user_id]) REFERENCES [dbo].[users] ([user_id])
88 ON DELETE CASCADE ON UPDATE CASCADE
89 GO
90
ace511a @alecpl - Add unique index on users.username+users.mail_host
alecpl authored
91 -- Updates from version 0.4.2
92
93 DROP INDEX [IX_users_username]
94 GO
95 CREATE UNIQUE INDEX [IX_users_username] ON [dbo].[users]([username],[mail_host]) ON [PRIMARY]
96 GO
6f09681 @alecpl - Support contact's email addresses up to 255 characters long (#1487095)
alecpl authored
97 ALTER TABLE [dbo].[contacts] ALTER COLUMN [email] [varchar] (255) COLLATE Latin1_General_CI_AI NOT NULL
98 GO
3e26373 @thomascube Fulltext search over contact fields. Attention: DATABASE SCHEMA CHANG…
thomascube authored
99
faf10e8 @thomascube Fix automatic db updates; trigger contact indexing after upgrade
thomascube authored
100 -- Updates from version 0.5.1
101 -- Updates from version 0.5.2
102 -- Updates from version 0.5.3
103 -- Updates from version 0.5.4
3e26373 @thomascube Fulltext search over contact fields. Attention: DATABASE SCHEMA CHANG…
thomascube authored
104
105 ALTER TABLE [dbo].[contacts] ADD [words] [text] COLLATE Latin1_General_CI_AI NULL
106 GO
d08333e @alecpl - Fix problems with subfolders of INBOX folder on some IMAP servers (…
alecpl authored
107 CREATE INDEX [IX_contactgroupmembers_contact_id] ON [dbo].[contactgroupmembers]([contact_id]) ON [PRIMARY]
108 GO
109 DELETE FROM [dbo].[messages]
110 GO
111 DELETE FROM [dbo].[cache]
3a5476d @alecpl - Add index on contactgroupmembers.contact_id column.
alecpl authored
112 GO
66df084 @alecpl - Merge devel-spellcheck branch:
alecpl authored
113
502436d @thomascube We're on the road towards 0.7 now
thomascube authored
114 -- Updates from version 0.6
66df084 @alecpl - Merge devel-spellcheck branch:
alecpl authored
115
116 CREATE TABLE [dbo].[dictionary] (
117 [user_id] [int] ,
118 [language] [varchar] (5) COLLATE Latin1_General_CI_AI NOT NULL ,
119 [data] [text] COLLATE Latin1_General_CI_AI NOT NULL
120 ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
121 GO
122 CREATE UNIQUE INDEX [IX_dictionary_user_language] ON [dbo].[dictionary]([user_id],[language]) ON [PRIMARY]
123 GO
f8e48df @alecpl - Merge devel-saved_search branch (Addressbook Saved Searches)
alecpl authored
124
125 CREATE TABLE [dbo].[searches] (
126 [search_id] [int] IDENTITY (1, 1) NOT NULL ,
127 [user_id] [int] NOT NULL ,
128 [type] [tinyint] NOT NULL ,
129 [name] [varchar] (128) COLLATE Latin1_General_CI_AI NOT NULL ,
130 [data] [text] COLLATE Latin1_General_CI_AI NOT NULL
131 ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
132 GO
133
134 ALTER TABLE [dbo].[searches] WITH NOCHECK ADD
135 CONSTRAINT [PK_searches_search_id] PRIMARY KEY CLUSTERED
136 (
137 [search_id]
138 ) ON [PRIMARY]
139 GO
140
141 ALTER TABLE [dbo].[searches] ADD
142 CONSTRAINT [DF_searches_user] DEFAULT (0) FOR [user_id],
143 CONSTRAINT [DF_searches_type] DEFAULT (0) FOR [type],
144 GO
145
146 CREATE UNIQUE INDEX [IX_searches_user_type_name] ON [dbo].[searches]([user_id],[type],[name]) ON [PRIMARY]
147 GO
148
149 ALTER TABLE [dbo].[searches] ADD CONSTRAINT [FK_searches_user_id]
150 FOREIGN KEY ([user_id]) REFERENCES [dbo].[users] ([user_id])
151 ON DELETE CASCADE ON UPDATE CASCADE
152 GO
153
80152b3 @alecpl - Rewritten messages caching (merged devel-mcache branch):
alecpl authored
154 DROP TABLE [dbo].[messages]
155 GO
156 CREATE TABLE [dbo].[cache_index] (
157 [user_id] [int] NOT NULL ,
158 [mailbox] [varchar] (128) COLLATE Latin1_General_CI_AI NOT NULL ,
159 [changed] [datetime] NOT NULL ,
7b2bf71 @alecpl - Add lost cache_index.valid column definition
alecpl authored
160 [valid] [char] (1) COLLATE Latin1_General_CI_AI NOT NULL ,
80152b3 @alecpl - Rewritten messages caching (merged devel-mcache branch):
alecpl authored
161 [data] [text] COLLATE Latin1_General_CI_AI NOT NULL
162 ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
163 GO
164
165 CREATE TABLE [dbo].[cache_thread] (
166 [user_id] [int] NOT NULL ,
167 [mailbox] [varchar] (128) COLLATE Latin1_General_CI_AI NOT NULL ,
168 [changed] [datetime] NOT NULL ,
169 [data] [text] COLLATE Latin1_General_CI_AI NOT NULL
170 ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
171 GO
172
173 CREATE TABLE [dbo].[cache_messages] (
174 [user_id] [int] NOT NULL ,
175 [mailbox] [varchar] (128) COLLATE Latin1_General_CI_AI NOT NULL ,
176 [uid] [int] NOT NULL ,
177 [changed] [datetime] NOT NULL ,
ccce87d @alecpl - Fix parse errors in DDL files for MS SQL Server
alecpl authored
178 [data] [text] COLLATE Latin1_General_CI_AI NOT NULL ,
179 [flags] [int] NOT NULL
80152b3 @alecpl - Rewritten messages caching (merged devel-mcache branch):
alecpl authored
180 ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
181 GO
182
183 ALTER TABLE [dbo].[cache_index] WITH NOCHECK ADD
184 PRIMARY KEY CLUSTERED
185 (
186 [user_id],[mailbox]
187 ) ON [PRIMARY]
188 GO
189
190 ALTER TABLE [dbo].[cache_thread] WITH NOCHECK ADD
191 PRIMARY KEY CLUSTERED
192 (
193 [user_id],[mailbox]
194 ) ON [PRIMARY]
195 GO
196
197 ALTER TABLE [dbo].[cache_messages] WITH NOCHECK ADD
198 PRIMARY KEY CLUSTERED
199 (
200 [user_id],[mailbox],[uid]
201 ) ON [PRIMARY]
202 GO
203
204 ALTER TABLE [dbo].[cache_index] ADD
7b2bf71 @alecpl - Add lost cache_index.valid column definition
alecpl authored
205 CONSTRAINT [DF_cache_index_changed] DEFAULT (getdate()) FOR [changed],
206 CONSTRAINT [DF_cache_index_valid] DEFAULT ('0') FOR [valid]
80152b3 @alecpl - Rewritten messages caching (merged devel-mcache branch):
alecpl authored
207 GO
208
209 CREATE INDEX [IX_cache_index_user_id] ON [dbo].[cache_index]([user_id]) ON [PRIMARY]
210 GO
211
212 ALTER TABLE [dbo].[cache_thread] ADD
213 CONSTRAINT [DF_cache_thread_changed] DEFAULT (getdate()) FOR [changed]
214 GO
215
216 CREATE INDEX [IX_cache_thread_user_id] ON [dbo].[cache_thread]([user_id]) ON [PRIMARY]
217 GO
218
219 ALTER TABLE [dbo].[cache_messages] ADD
609d392 @alecpl - Cache synchronization using QRESYNC/CONDSTORE
alecpl authored
220 CONSTRAINT [DF_cache_messages_changed] DEFAULT (getdate()) FOR [changed],
221 CONSTRAINT [DF_cache_messages_flags] DEFAULT (0) FOR [flags]
80152b3 @alecpl - Rewritten messages caching (merged devel-mcache branch):
alecpl authored
222 GO
223
224 CREATE INDEX [IX_cache_messages_user_id] ON [dbo].[cache_messages]([user_id]) ON [PRIMARY]
225 GO
226
227 ALTER TABLE [dbo].[cache_index] ADD CONSTRAINT [FK_cache_index_user_id]
228 FOREIGN KEY ([user_id]) REFERENCES [dbo].[users] ([user_id])
229 ON DELETE CASCADE ON UPDATE CASCADE
230 GO
231
232 ALTER TABLE [dbo].[cache_thread] ADD CONSTRAINT [FK_cache_thread_user_id]
233 FOREIGN KEY ([user_id]) REFERENCES [dbo].[users] ([user_id])
234 ON DELETE CASCADE ON UPDATE CASCADE
235 GO
236
237 ALTER TABLE [dbo].[cache_messages] ADD CONSTRAINT [FK_cache_messages_user_id]
238 FOREIGN KEY ([user_id]) REFERENCES [dbo].[users] ([user_id])
239 ON DELETE CASCADE ON UPDATE CASCADE
240 GO
502436d @thomascube We're on the road towards 0.7 now
thomascube authored
241
f450743 @alecpl - Add version markers
alecpl authored
242 -- Updates from version 0.7-beta
243
b8ae0ec @alecpl - Fixed bug where session ID's length was limited to 40 characters. S…
alecpl authored
244 ALTER TABLE [dbo].[session] ALTER COLUMN [sess_id] [varchar] (128) COLLATE Latin1_General_CI_AI NOT NULL
245 GO
246
48be8f6 @alecpl - Fix SQL Error when saving a contact with many email addresses (#148…
alecpl authored
247 -- Updates from version 0.7
248
7ac99af @alecpl Fix DDL update from 0.7 for MS SQL Server
alecpl authored
249 ALTER TABLE [dbo].[contacts] DROP CONSTRAINT [DF_contacts_email]
250 GO
48be8f6 @alecpl - Fix SQL Error when saving a contact with many email addresses (#148…
alecpl authored
251 ALTER TABLE [dbo].[contacts] ALTER COLUMN [email] [text] COLLATE Latin1_General_CI_AI NOT NULL
252 GO
7ac99af @alecpl Fix DDL update from 0.7 for MS SQL Server
alecpl authored
253 ALTER TABLE [dbo].[contacts] ADD CONSTRAINT [DF_contacts_email] DEFAULT ('') FOR [email]
254 GO
48be8f6 @alecpl - Fix SQL Error when saving a contact with many email addresses (#148…
alecpl authored
255
6237c94 @alecpl Fix operator incompat. in some queries on contacts table using MS SQL…
alecpl authored
256 -- Updates from version 0.8-rc
257
258 ALTER TABLE [dbo].[contacts] DROP CONSTRAINT [DF_contacts_email]
259 GO
260 ALTER TABLE [dbo].[contacts] ALTER COLUMN [email] [varchar] (8000) COLLATE Latin1_General_CI_AI NOT NULL
261 GO
262 ALTER TABLE [dbo].[contacts] ADD CONSTRAINT [DF_contacts_email] DEFAULT ('') FOR [email]
263 GO
565c472 @alecpl Removed users.alias column, added option ('user_aliases')
alecpl authored
264
265 -- Updates from version 0.8
266
267 ALTER TABLE [dbo].[cache] DROP COLUMN [cache_id]
268 GO
269 ALTER TABLE [dbo].[users] DROP COLUMN [alias]
270 GO
271 CREATE INDEX [IX_identities_email] ON [dbo].[identities]([email],[del]) ON [PRIMARY]
272 GO
6237c94 @alecpl Fix operator incompat. in some queries on contacts table using MS SQL…
alecpl authored
273
Something went wrong with that request. Please try again.