Mercurial > hg > rc1

comparison SQL/mssql.initial.sql @ 0:1e000243b222

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
vanilla 1.3.3 distro, I hope
author Charlie Root
date Thu, 04 Jan 2018 15:50:29 -0500
parents
children
comparison
equal deleted inserted replaced
-1:000000000000 0:1e000243b222
1 CREATE TABLE [dbo].[cache] (
2 [user_id] [int] NOT NULL ,
3 [cache_key] [varchar] (128) COLLATE Latin1_General_CI_AI NOT NULL ,
4 [expires] [datetime] NULL ,
5 [data] [text] COLLATE Latin1_General_CI_AI NOT NULL
6 ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
7 GO
8
9 CREATE TABLE [dbo].[cache_shared] (
10 [cache_key] [varchar] (255) COLLATE Latin1_General_CI_AI NOT NULL ,
11 [expires] [datetime] NULL ,
12 [data] [text] COLLATE Latin1_General_CI_AI NOT NULL
13 ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
14 GO
15
16 CREATE TABLE [dbo].[cache_index] (
17 [user_id] [int] NOT NULL ,
18 [mailbox] [varchar] (128) COLLATE Latin1_General_CI_AI NOT NULL ,
19 [expires] [datetime] NULL ,
20 [valid] [char] (1) COLLATE Latin1_General_CI_AI NOT NULL ,
21 [data] [text] COLLATE Latin1_General_CI_AI NOT NULL
22 ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
23 GO
24
25 CREATE TABLE [dbo].[cache_thread] (
26 [user_id] [int] NOT NULL ,
27 [mailbox] [varchar] (128) COLLATE Latin1_General_CI_AI NOT NULL ,
28 [expires] [datetime] NULL ,
29 [data] [text] COLLATE Latin1_General_CI_AI NOT NULL
30 ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
31 GO
32
33 CREATE TABLE [dbo].[cache_messages] (
34 [user_id] [int] NOT NULL ,
35 [mailbox] [varchar] (128) COLLATE Latin1_General_CI_AI NOT NULL ,
36 [uid] [int] NOT NULL ,
37 [expires] [datetime] NULL ,
38 [data] [text] COLLATE Latin1_General_CI_AI NOT NULL ,
39 [flags] [int] NOT NULL
40 ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
41 GO
42
43 CREATE TABLE [dbo].[contacts] (
44 [contact_id] [int] IDENTITY (1, 1) NOT NULL ,
45 [user_id] [int] NOT NULL ,
46 [changed] [datetime] NOT NULL ,
47 [del] [char] (1) COLLATE Latin1_General_CI_AI NOT NULL ,
48 [name] [varchar] (128) COLLATE Latin1_General_CI_AI NOT NULL ,
49 [email] [varchar] (8000) COLLATE Latin1_General_CI_AI NOT NULL ,
50 [firstname] [varchar] (128) COLLATE Latin1_General_CI_AI NOT NULL ,
51 [surname] [varchar] (128) COLLATE Latin1_General_CI_AI NOT NULL ,
52 [vcard] [text] COLLATE Latin1_General_CI_AI NULL ,
53 [words] [text] COLLATE Latin1_General_CI_AI NULL
54 ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
55 GO
56
57 CREATE TABLE [dbo].[contactgroups] (
58 [contactgroup_id] [int] IDENTITY (1, 1) NOT NULL ,
59 [user_id] [int] NOT NULL ,
60 [changed] [datetime] NOT NULL ,
61 [del] [char] (1) COLLATE Latin1_General_CI_AI NOT NULL ,
62 [name] [varchar] (128) COLLATE Latin1_General_CI_AI NOT NULL
63 ) ON [PRIMARY]
64 GO
65
66 CREATE TABLE [dbo].[contactgroupmembers] (
67 [contactgroup_id] [int] NOT NULL ,
68 [contact_id] [int] NOT NULL ,
69 [created] [datetime] NOT NULL
70 ) ON [PRIMARY]
71 GO
72
73 CREATE TABLE [dbo].[identities] (
74 [identity_id] [int] IDENTITY (1, 1) NOT NULL ,
75 [user_id] [int] NOT NULL ,
76 [changed] [datetime] NOT NULL ,
77 [del] [char] (1) COLLATE Latin1_General_CI_AI NOT NULL ,
78 [standard] [char] (1) COLLATE Latin1_General_CI_AI NOT NULL ,
79 [name] [varchar] (128) COLLATE Latin1_General_CI_AI NOT NULL ,
80 [organization] [varchar] (128) COLLATE Latin1_General_CI_AI NOT NULL ,
81 [email] [varchar] (128) COLLATE Latin1_General_CI_AI NOT NULL ,
82 [reply-to] [varchar] (128) COLLATE Latin1_General_CI_AI NOT NULL ,
83 [bcc] [varchar] (128) COLLATE Latin1_General_CI_AI NOT NULL ,
84 [signature] [text] COLLATE Latin1_General_CI_AI NULL,
85 [html_signature] [char] (1) COLLATE Latin1_General_CI_AI NOT NULL
86 ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
87 GO
88
89 CREATE TABLE [dbo].[session] (
90 [sess_id] [varchar] (128) COLLATE Latin1_General_CI_AI NOT NULL ,
91 [changed] [datetime] NULL ,
92 [ip] [varchar] (40) COLLATE Latin1_General_CI_AI NOT NULL ,
93 [vars] [text] COLLATE Latin1_General_CI_AI NOT NULL
94 ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
95 GO
96
97 CREATE TABLE [dbo].[users] (
98 [user_id] [int] IDENTITY (1, 1) NOT NULL ,
99 [username] [varchar] (128) COLLATE Latin1_General_CI_AI NOT NULL ,
100 [mail_host] [varchar] (128) COLLATE Latin1_General_CI_AI NOT NULL ,
101 [created] [datetime] NOT NULL ,
102 [last_login] [datetime] NULL ,
103 [failed_login] [datetime] NULL ,
104 [failed_login_counter] [int] NULL ,
105 [language] [varchar] (5) COLLATE Latin1_General_CI_AI NULL ,
106 [preferences] [text] COLLATE Latin1_General_CI_AI NULL
107 ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
108 GO
109
110 CREATE TABLE [dbo].[dictionary] (
111 [user_id] [int] ,
112 [language] [varchar] (5) COLLATE Latin1_General_CI_AI NOT NULL ,
113 [data] [text] COLLATE Latin1_General_CI_AI NOT NULL
114 ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
115 GO
116
117 CREATE TABLE [dbo].[searches] (
118 [search_id] [int] IDENTITY (1, 1) NOT NULL ,
119 [user_id] [int] NOT NULL ,
120 [type] [tinyint] NOT NULL ,
121 [name] [varchar] (128) COLLATE Latin1_General_CI_AI NOT NULL ,
122 [data] [text] COLLATE Latin1_General_CI_AI NOT NULL
123 ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
124 GO
125
126 CREATE TABLE [dbo].[system] (
127 [name] [varchar] (64) COLLATE Latin1_General_CI_AI NOT NULL ,
128 [value] [text] COLLATE Latin1_General_CI_AI NOT NULL
129 ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
130 GO
131
132 ALTER TABLE [dbo].[cache] WITH NOCHECK ADD
133 PRIMARY KEY CLUSTERED
134 (
135 [user_id],[cache_key]
136 ) ON [PRIMARY]
137 GO
138
139 ALTER TABLE [dbo].[cache_shared] WITH NOCHECK ADD
140 PRIMARY KEY CLUSTERED
141 (
142 [cache_key]
143 ) ON [PRIMARY]
144 GO
145
146 ALTER TABLE [dbo].[cache_index] WITH NOCHECK ADD
147 PRIMARY KEY CLUSTERED
148 (
149 [user_id],[mailbox]
150 ) ON [PRIMARY]
151 GO
152
153 ALTER TABLE [dbo].[cache_thread] WITH NOCHECK ADD
154 PRIMARY KEY CLUSTERED
155 (
156 [user_id],[mailbox]
157 ) ON [PRIMARY]
158 GO
159
160 ALTER TABLE [dbo].[cache_messages] WITH NOCHECK ADD
161 PRIMARY KEY CLUSTERED
162 (
163 [user_id],[mailbox],[uid]
164 ) ON [PRIMARY]
165 GO
166
167 ALTER TABLE [dbo].[contacts] WITH NOCHECK ADD
168 CONSTRAINT [PK_contacts_contact_id] PRIMARY KEY CLUSTERED
169 (
170 [contact_id]
171 ) ON [PRIMARY]
172 GO
173
174 ALTER TABLE [dbo].[contactgroups] WITH NOCHECK ADD
175 CONSTRAINT [PK_contactgroups_contactgroup_id] PRIMARY KEY CLUSTERED
176 (
177 [contactgroup_id]
178 ) ON [PRIMARY]
179 GO
180
181 ALTER TABLE [dbo].[contactgroupmembers] WITH NOCHECK ADD
182 CONSTRAINT [PK_contactgroupmembers_id] PRIMARY KEY CLUSTERED
183 (
184 [contactgroup_id], [contact_id]
185 ) ON [PRIMARY]
186 GO
187
188 ALTER TABLE [dbo].[identities] WITH NOCHECK ADD
189 PRIMARY KEY CLUSTERED
190 (
191 [identity_id]
192 ) ON [PRIMARY]
193 GO
194
195 ALTER TABLE [dbo].[session] WITH NOCHECK ADD
196 CONSTRAINT [PK_session_sess_id] PRIMARY KEY CLUSTERED
197 (
198 [sess_id]
199 ) ON [PRIMARY]
200 GO
201
202 ALTER TABLE [dbo].[users] WITH NOCHECK ADD
203 CONSTRAINT [PK_users_user_id] PRIMARY KEY CLUSTERED
204 (
205 [user_id]
206 ) ON [PRIMARY]
207 GO
208
209 ALTER TABLE [dbo].[searches] WITH NOCHECK ADD
210 CONSTRAINT [PK_searches_search_id] PRIMARY KEY CLUSTERED
211 (
212 [search_id]
213 ) ON [PRIMARY]
214 GO
215
216 ALTER TABLE [dbo].[system] WITH NOCHECK ADD
217 CONSTRAINT [PK_system_name] PRIMARY KEY CLUSTERED
218 (
219 [name]
220 ) ON [PRIMARY]
221 GO
222
223 ALTER TABLE [dbo].[cache] ADD
224 CONSTRAINT [DF_cache_user_id] DEFAULT ('0') FOR [user_id],
225 CONSTRAINT [DF_cache_cache_key] DEFAULT ('') FOR [cache_key],
226 GO
227
228 ALTER TABLE [dbo].[cache_index] ADD
229 CONSTRAINT [DF_cache_index_valid] DEFAULT ('0') FOR [valid]
230 GO
231
232 ALTER TABLE [dbo].[cache_messages] ADD
233 CONSTRAINT [DF_cache_messages_flags] DEFAULT (0) FOR [flags]
234 GO
235
236 CREATE INDEX [IX_cache_shared_cache_key] ON [dbo].[cache_shared]([cache_key]) ON [PRIMARY]
237 GO
238
239 CREATE INDEX [IX_cache_index_user_id] ON [dbo].[cache_index]([user_id]) ON [PRIMARY]
240 GO
241
242 CREATE INDEX [IX_cache_thread_user_id] ON [dbo].[cache_thread]([user_id]) ON [PRIMARY]
243 GO
244
245 CREATE INDEX [IX_cache_messages_user_id] ON [dbo].[cache_messages]([user_id]) ON [PRIMARY]
246 GO
247
248 CREATE INDEX [IX_cache_expires] ON [dbo].[cache]([expires]) ON [PRIMARY]
249 GO
250
251 CREATE INDEX [IX_cache_shared_expires] ON [dbo].[cache_shared]([expires]) ON [PRIMARY]
252 GO
253
254 CREATE INDEX [IX_cache_index_expires] ON [dbo].[cache_index]([expires]) ON [PRIMARY]
255 GO
256
257 CREATE INDEX [IX_cache_thread_expires] ON [dbo].[cache_thread]([expires]) ON [PRIMARY]
258 GO
259
260 CREATE INDEX [IX_cache_messages_expires] ON [dbo].[cache_messages]([expires]) ON [PRIMARY]
261 GO
262
263 ALTER TABLE [dbo].[contacts] ADD
264 CONSTRAINT [DF_contacts_user_id] DEFAULT (0) FOR [user_id],
265 CONSTRAINT [DF_contacts_changed] DEFAULT (getdate()) FOR [changed],
266 CONSTRAINT [DF_contacts_del] DEFAULT ('0') FOR [del],
267 CONSTRAINT [DF_contacts_name] DEFAULT ('') FOR [name],
268 CONSTRAINT [DF_contacts_email] DEFAULT ('') FOR [email],
269 CONSTRAINT [DF_contacts_firstname] DEFAULT ('') FOR [firstname],
270 CONSTRAINT [DF_contacts_surname] DEFAULT ('') FOR [surname],
271 CONSTRAINT [CK_contacts_del] CHECK ([del] = '1' or [del] = '0')
272 GO
273
274 CREATE INDEX [IX_contacts_user_id] ON [dbo].[contacts]([user_id]) ON [PRIMARY]
275 GO
276
277 ALTER TABLE [dbo].[contactgroups] ADD
278 CONSTRAINT [DF_contactgroups_user_id] DEFAULT (0) FOR [user_id],
279 CONSTRAINT [DF_contactgroups_changed] DEFAULT (getdate()) FOR [changed],
280 CONSTRAINT [DF_contactgroups_del] DEFAULT ('0') FOR [del],
281 CONSTRAINT [DF_contactgroups_name] DEFAULT ('') FOR [name],
282 CONSTRAINT [CK_contactgroups_del] CHECK ([del] = '1' or [del] = '0')
283 GO
284
285 CREATE INDEX [IX_contactgroups_user_id] ON [dbo].[contactgroups]([user_id]) ON [PRIMARY]
286 GO
287
288 ALTER TABLE [dbo].[contactgroupmembers] ADD
289 CONSTRAINT [DF_contactgroupmembers_contactgroup_id] DEFAULT (0) FOR [contactgroup_id],
290 CONSTRAINT [DF_contactgroupmembers_contact_id] DEFAULT (0) FOR [contact_id],
291 CONSTRAINT [DF_contactgroupmembers_created] DEFAULT (getdate()) FOR [created]
292 GO
293
294 CREATE INDEX [IX_contactgroupmembers_contact_id] ON [dbo].[contactgroupmembers]([contact_id]) ON [PRIMARY]
295 GO
296
297 ALTER TABLE [dbo].[identities] ADD
298 CONSTRAINT [DF_identities_user] DEFAULT ('0') FOR [user_id],
299 CONSTRAINT [DF_identities_del] DEFAULT ('0') FOR [del],
300 CONSTRAINT [DF_identities_standard] DEFAULT ('0') FOR [standard],
301 CONSTRAINT [DF_identities_name] DEFAULT ('') FOR [name],
302 CONSTRAINT [DF_identities_organization] DEFAULT ('') FOR [organization],
303 CONSTRAINT [DF_identities_email] DEFAULT ('') FOR [email],
304 CONSTRAINT [DF_identities_reply] DEFAULT ('') FOR [reply-to],
305 CONSTRAINT [DF_identities_bcc] DEFAULT ('') FOR [bcc],
306 CONSTRAINT [DF_identities_html_signature] DEFAULT ('0') FOR [html_signature],
307 CHECK ([standard] = '1' or [standard] = '0'),
308 CHECK ([del] = '1' or [del] = '0')
309 GO
310
311 CREATE INDEX [IX_identities_user_id] ON [dbo].[identities]([user_id]) ON [PRIMARY]
312 GO
313 CREATE INDEX [IX_identities_email] ON [dbo].[identities]([email],[del]) ON [PRIMARY]
314 GO
315
316 ALTER TABLE [dbo].[session] ADD
317 CONSTRAINT [DF_session_sess_id] DEFAULT ('') FOR [sess_id],
318 CONSTRAINT [DF_session_ip] DEFAULT ('') FOR [ip]
319 GO
320
321 CREATE INDEX [IX_session_changed] ON [dbo].[session]([changed]) ON [PRIMARY]
322 GO
323
324 ALTER TABLE [dbo].[users] ADD
325 CONSTRAINT [DF_users_username] DEFAULT ('') FOR [username],
326 CONSTRAINT [DF_users_mail_host] DEFAULT ('') FOR [mail_host],
327 CONSTRAINT [DF_users_created] DEFAULT (getdate()) FOR [created]
328 GO
329
330 CREATE UNIQUE INDEX [IX_users_username] ON [dbo].[users]([username],[mail_host]) ON [PRIMARY]
331 GO
332
333 CREATE UNIQUE INDEX [IX_dictionary_user_language] ON [dbo].[dictionary]([user_id],[language]) ON [PRIMARY]
334 GO
335
336 ALTER TABLE [dbo].[searches] ADD
337 CONSTRAINT [DF_searches_user] DEFAULT (0) FOR [user_id],
338 CONSTRAINT [DF_searches_type] DEFAULT (0) FOR [type]
339 GO
340
341 CREATE UNIQUE INDEX [IX_searches_user_type_name] ON [dbo].[searches]([user_id],[type],[name]) ON [PRIMARY]
342 GO
343
344 ALTER TABLE [dbo].[identities] ADD CONSTRAINT [FK_identities_user_id]
345 FOREIGN KEY ([user_id]) REFERENCES [dbo].[users] ([user_id])
346 ON DELETE CASCADE ON UPDATE CASCADE
347 GO
348
349 ALTER TABLE [dbo].[contacts] ADD CONSTRAINT [FK_contacts_user_id]
350 FOREIGN KEY ([user_id]) REFERENCES [dbo].[users] ([user_id])
351 ON DELETE CASCADE ON UPDATE CASCADE
352 GO
353
354 ALTER TABLE [dbo].[contactgroups] ADD CONSTRAINT [FK_contactgroups_user_id]
355 FOREIGN KEY ([user_id]) REFERENCES [dbo].[users] ([user_id])
356 ON DELETE CASCADE ON UPDATE CASCADE
357 GO
358
359 ALTER TABLE [dbo].[cache] ADD CONSTRAINT [FK_cache_user_id]
360 FOREIGN KEY ([user_id]) REFERENCES [dbo].[users] ([user_id])
361 ON DELETE CASCADE ON UPDATE CASCADE
362 GO
363
364 ALTER TABLE [dbo].[cache_index] ADD CONSTRAINT [FK_cache_index_user_id]
365 FOREIGN KEY ([user_id]) REFERENCES [dbo].[users] ([user_id])
366 ON DELETE CASCADE ON UPDATE CASCADE
367 GO
368
369 ALTER TABLE [dbo].[cache_thread] ADD CONSTRAINT [FK_cache_thread_user_id]
370 FOREIGN KEY ([user_id]) REFERENCES [dbo].[users] ([user_id])
371 ON DELETE CASCADE ON UPDATE CASCADE
372 GO
373
374 ALTER TABLE [dbo].[cache_messages] ADD CONSTRAINT [FK_cache_messages_user_id]
375 FOREIGN KEY ([user_id]) REFERENCES [dbo].[users] ([user_id])
376 ON DELETE CASCADE ON UPDATE CASCADE
377 GO
378
379 ALTER TABLE [dbo].[contactgroupmembers] ADD CONSTRAINT [FK_contactgroupmembers_contactgroup_id]
380 FOREIGN KEY ([contactgroup_id]) REFERENCES [dbo].[contactgroups] ([contactgroup_id])
381 ON DELETE CASCADE ON UPDATE CASCADE
382 GO
383
384 ALTER TABLE [dbo].[searches] ADD CONSTRAINT [FK_searches_user_id]
385 FOREIGN KEY ([user_id]) REFERENCES [dbo].[users] ([user_id])
386 ON DELETE CASCADE ON UPDATE CASCADE
387 GO
388
389 -- Use trigger instead of foreign key (#1487112)
390 -- "Introducing FOREIGN KEY constraint ... may cause cycles or multiple cascade paths."
391 CREATE TRIGGER [contact_delete_member] ON [dbo].[contacts]
392 AFTER DELETE AS
393 DELETE FROM [dbo].[contactgroupmembers]
394 WHERE [contact_id] IN (SELECT [contact_id] FROM deleted)
395 GO
396
397 INSERT INTO [dbo].[system] ([name], [value]) VALUES ('roundcube-version', '2016112200')
398 GO
399