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