· 6 years ago · Mar 27, 2019, 07:20 PM
1/* ---------------------------------------------------- */
2/* Generated by Enterprise Architect Version 12.1 */
3/* Created On : 27-мар-2019 20:51:01 */
4/* DBMS : SQL Server 2012 */
5/* ---------------------------------------------------- */
6
7/* Drop Foreign Key Constraints */
8
9IF EXISTS (SELECT 1 FROM dbo.sysobjects WHERE id = object_id(N'[FK_chat_events_chats]') AND OBJECTPROPERTY(id, N'IsForeignKey') = 1)
10ALTER TABLE [chat_events] DROP CONSTRAINT [FK_chat_events_chats]
11GO
12
13IF EXISTS (SELECT 1 FROM dbo.sysobjects WHERE id = object_id(N'[FK_chat_events_events]') AND OBJECTPROPERTY(id, N'IsForeignKey') = 1)
14ALTER TABLE [chat_events] DROP CONSTRAINT [FK_chat_events_events]
15GO
16
17IF EXISTS (SELECT 1 FROM dbo.sysobjects WHERE id = object_id(N'[FK_chat_users_chats]') AND OBJECTPROPERTY(id, N'IsForeignKey') = 1)
18ALTER TABLE [chat_users] DROP CONSTRAINT [FK_chat_users_chats]
19GO
20
21IF EXISTS (SELECT 1 FROM dbo.sysobjects WHERE id = object_id(N'[FK_chat_users_users]') AND OBJECTPROPERTY(id, N'IsForeignKey') = 1)
22ALTER TABLE [chat_users] DROP CONSTRAINT [FK_chat_users_users]
23GO
24
25IF EXISTS (SELECT 1 FROM dbo.sysobjects WHERE id = object_id(N'[FK_chats_users]') AND OBJECTPROPERTY(id, N'IsForeignKey') = 1)
26ALTER TABLE [chats] DROP CONSTRAINT [FK_chats_users]
27GO
28
29IF EXISTS (SELECT 1 FROM dbo.sysobjects WHERE id = object_id(N'[FK_events_users]') AND OBJECTPROPERTY(id, N'IsForeignKey') = 1)
30ALTER TABLE [events] DROP CONSTRAINT [FK_events_users]
31GO
32
33IF EXISTS (SELECT 1 FROM dbo.sysobjects WHERE id = object_id(N'[FK_friend_requests_users]') AND OBJECTPROPERTY(id, N'IsForeignKey') = 1)
34ALTER TABLE [friend_requests] DROP CONSTRAINT [FK_friend_requests_users]
35GO
36
37IF EXISTS (SELECT 1 FROM dbo.sysobjects WHERE id = object_id(N'[FK_friend_requests_users_02]') AND OBJECTPROPERTY(id, N'IsForeignKey') = 1)
38ALTER TABLE [friend_requests] DROP CONSTRAINT [FK_friend_requests_users_02]
39GO
40
41IF EXISTS (SELECT 1 FROM dbo.sysobjects WHERE id = object_id(N'[FK_messages_chats]') AND OBJECTPROPERTY(id, N'IsForeignKey') = 1)
42ALTER TABLE [messages] DROP CONSTRAINT [FK_messages_chats]
43GO
44
45IF EXISTS (SELECT 1 FROM dbo.sysobjects WHERE id = object_id(N'[FK_messages_users]') AND OBJECTPROPERTY(id, N'IsForeignKey') = 1)
46ALTER TABLE [messages] DROP CONSTRAINT [FK_messages_users]
47GO
48
49IF EXISTS (SELECT 1 FROM dbo.sysobjects WHERE id = object_id(N'[FK_user_auth_users]') AND OBJECTPROPERTY(id, N'IsForeignKey') = 1)
50ALTER TABLE [user_auth] DROP CONSTRAINT [FK_user_auth_users]
51GO
52
53IF EXISTS (SELECT 1 FROM dbo.sysobjects WHERE id = object_id(N'[FK_user_events_events]') AND OBJECTPROPERTY(id, N'IsForeignKey') = 1)
54ALTER TABLE [user_events] DROP CONSTRAINT [FK_user_events_events]
55GO
56
57IF EXISTS (SELECT 1 FROM dbo.sysobjects WHERE id = object_id(N'[FK_user_events_users]') AND OBJECTPROPERTY(id, N'IsForeignKey') = 1)
58ALTER TABLE [user_events] DROP CONSTRAINT [FK_user_events_users]
59GO
60
61IF EXISTS (SELECT 1 FROM dbo.sysobjects WHERE id = object_id(N'[FK_user_friends_users]') AND OBJECTPROPERTY(id, N'IsForeignKey') = 1)
62ALTER TABLE [user_friends] DROP CONSTRAINT [FK_user_friends_users]
63GO
64
65IF EXISTS (SELECT 1 FROM dbo.sysobjects WHERE id = object_id(N'[FK_user_friends_users_02]') AND OBJECTPROPERTY(id, N'IsForeignKey') = 1)
66ALTER TABLE [user_friends] DROP CONSTRAINT [FK_user_friends_users_02]
67GO
68
69IF EXISTS (SELECT 1 FROM dbo.sysobjects WHERE id = object_id(N'[FK_user_languages_languages]') AND OBJECTPROPERTY(id, N'IsForeignKey') = 1)
70ALTER TABLE [user_languages] DROP CONSTRAINT [FK_user_languages_languages]
71GO
72
73IF EXISTS (SELECT 1 FROM dbo.sysobjects WHERE id = object_id(N'[FK_user_languages_users]') AND OBJECTPROPERTY(id, N'IsForeignKey') = 1)
74ALTER TABLE [user_languages] DROP CONSTRAINT [FK_user_languages_users]
75GO
76
77/* Drop Tables */
78
79IF EXISTS (SELECT 1 FROM dbo.sysobjects WHERE id = object_id(N'[chat_events]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
80DROP TABLE [chat_events]
81GO
82
83IF EXISTS (SELECT 1 FROM dbo.sysobjects WHERE id = object_id(N'[chat_users]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
84DROP TABLE [chat_users]
85GO
86
87IF EXISTS (SELECT 1 FROM dbo.sysobjects WHERE id = object_id(N'[chats]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
88DROP TABLE [chats]
89GO
90
91IF EXISTS (SELECT 1 FROM dbo.sysobjects WHERE id = object_id(N'[events]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
92DROP TABLE [events]
93GO
94
95IF EXISTS (SELECT 1 FROM dbo.sysobjects WHERE id = object_id(N'[friend_requests]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
96DROP TABLE [friend_requests]
97GO
98
99IF EXISTS (SELECT 1 FROM dbo.sysobjects WHERE id = object_id(N'[languages]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
100DROP TABLE [languages]
101GO
102
103IF EXISTS (SELECT 1 FROM dbo.sysobjects WHERE id = object_id(N'[messages]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
104DROP TABLE [messages]
105GO
106
107IF EXISTS (SELECT 1 FROM dbo.sysobjects WHERE id = object_id(N'[user_auth]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
108DROP TABLE [user_auth]
109GO
110
111IF EXISTS (SELECT 1 FROM dbo.sysobjects WHERE id = object_id(N'[user_events]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
112DROP TABLE [user_events]
113GO
114
115IF EXISTS (SELECT 1 FROM dbo.sysobjects WHERE id = object_id(N'[user_friends]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
116DROP TABLE [user_friends]
117GO
118
119IF EXISTS (SELECT 1 FROM dbo.sysobjects WHERE id = object_id(N'[user_languages]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
120DROP TABLE [user_languages]
121GO
122
123IF EXISTS (SELECT 1 FROM dbo.sysobjects WHERE id = object_id(N'[users]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
124DROP TABLE [users]
125GO
126
127/* Create Tables */
128
129CREATE TABLE [chat_events]
130(
131 [chat_id] int NOT NULL,
132 [event_id] int NOT NULL
133)
134GO
135
136CREATE TABLE [chat_users]
137(
138 [chat_id] int NOT NULL,
139 [user_id] int NOT NULL
140)
141GO
142
143CREATE TABLE [chats]
144(
145 [id] int NOT NULL,
146 [name] varchar(50) NULL,
147 [creator_id] int NULL
148)
149GO
150
151CREATE TABLE [events]
152(
153 [id] int NOT NULL IDENTITY (1, 1),
154 [name] text NULL,
155 [max_participants] int NULL,
156 [latitude] decimal(10,2) NULL,
157 [longitude] decimal(11,8) NULL,
158 [creator_id] int NULL,
159 [is_finished] BIT NULL,
160 [starttime] datetime NULL,
161 [endtime] datetime NULL,
162 [description] text NULL,
163 [type] tinyint NULL
164)
165GO
166
167CREATE TABLE [friend_requests]
168(
169 [id_to] int NOT NULL,
170 [id_from] int NOT NULL
171)
172GO
173
174CREATE TABLE [languages]
175(
176 [id] int NOT NULL IDENTITY (1, 1),
177 [name] varchar(30) NOT NULL
178)
179GO
180
181CREATE TABLE [messages]
182(
183 [sender_id] int NOT NULL,
184 [chat_id] int NOT NULL,
185 [content] text NULL,
186 [datetime] datetime NOT NULL
187)
188GO
189
190CREATE TABLE [user_auth]
191(
192 [pass_hash] text NULL,
193 [fb_token] varchar(50) NULL,
194 [vk_token] varchar(50) NULL,
195 [user_id] int NULL
196)
197GO
198
199CREATE TABLE [user_events]
200(
201 [user_id] int NOT NULL,
202 [event_id] int NOT NULL
203)
204GO
205
206CREATE TABLE [user_friends]
207(
208 [friend_id] int NOT NULL,
209 [user_id] int NOT NULL
210)
211GO
212
213CREATE TABLE [user_languages]
214(
215 [user_id] int NOT NULL,
216 [language_id] int NOT NULL,
217 [level] smallint NULL
218)
219GO
220
221CREATE TABLE [users]
222(
223 [id] int NOT NULL IDENTITY (1, 1),
224 [name] varchar(50) NOT NULL,
225 [email] varchar(50) NULL,
226 [birthdate] date NOT NULL,
227 [gender] BIT NOT NULL,
228 [latitude] decimal(10,2) NOT NULL,
229 [longitude] decimal(11,8) NOT NULL
230)
231GO
232
233/* Create Primary Keys, Indexes, Uniques, Checks */
234
235ALTER TABLE [chat_events]
236 ADD CONSTRAINT [PK_chat_events]
237 PRIMARY KEY CLUSTERED ([chat_id] ASC,[event_id] ASC)
238GO
239
240CREATE UNIQUE CLUSTERED INDEX [IDX_chatevents]
241 ON [chat_events] ([chat_id] ASC,[event_id] ASC)
242GO
243
244ALTER TABLE [chat_users]
245 ADD CONSTRAINT [PK_chat_users]
246 PRIMARY KEY CLUSTERED ([chat_id] ASC,[user_id] ASC)
247GO
248
249CREATE UNIQUE CLUSTERED INDEX [IDX_chatusers]
250 ON [chat_users] ([chat_id] ASC,[user_id] ASC)
251GO
252
253ALTER TABLE [chats]
254 ADD CONSTRAINT [PK_chats]
255 PRIMARY KEY CLUSTERED ([id] ASC)
256GO
257
258CREATE UNIQUE CLUSTERED INDEX [IDX_chats]
259 ON [chats] ([id] ASC)
260GO
261
262ALTER TABLE [events]
263 ADD CONSTRAINT [PK_events]
264 PRIMARY KEY CLUSTERED ([id] ASC)
265GO
266
267CREATE UNIQUE CLUSTERED INDEX [IDX_events_id]
268 ON [events] ([id] ASC)
269GO
270
271CREATE NONCLUSTERED INDEX [IDX_events_location]
272 ON [events] ([longitude] ASC,[latitude] ASC)
273GO
274
275CREATE NONCLUSTERED INDEX [IDX_date]
276 ON [events] ([starttime] ASC,[endtime] ASC)
277GO
278
279ALTER TABLE [friend_requests]
280 ADD CONSTRAINT [PK_friend_requests]
281 PRIMARY KEY CLUSTERED ([id_from] ASC,[id_to] ASC)
282GO
283
284CREATE UNIQUE CLUSTERED INDEX [IDX_requests]
285 ON [friend_requests] ([id_to] ASC,[id_from] ASC)
286GO
287
288ALTER TABLE [languages]
289 ADD CONSTRAINT [PK_languages]
290 PRIMARY KEY CLUSTERED ([id] ASC)
291GO
292
293ALTER TABLE [languages]
294 ADD CONSTRAINT [unique_name] UNIQUE NONCLUSTERED ([name] ASC)
295GO
296
297CREATE UNIQUE CLUSTERED INDEX [IDX_languages]
298 ON [languages] ([id] ASC)
299GO
300
301ALTER TABLE [messages]
302 ADD CONSTRAINT [PK_messages]
303 PRIMARY KEY CLUSTERED ([sender_id] ASC,[chat_id] ASC,[datetime] ASC)
304GO
305
306CREATE UNIQUE CLUSTERED INDEX [IDX_messages]
307 ON [messages] ([sender_id] ASC,[chat_id] ASC,[datetime] ASC)
308GO
309
310ALTER TABLE [user_auth]
311 ADD CONSTRAINT [tokens_unique] UNIQUE NONCLUSTERED ([fb_token] ASC,[vk_token] ASC)
312GO
313
314CREATE UNIQUE CLUSTERED INDEX [IDX_auth]
315 ON [user_auth] ([user_id] ASC)
316GO
317
318ALTER TABLE [user_events]
319 ADD CONSTRAINT [PK_user_events]
320 PRIMARY KEY CLUSTERED ([user_id] ASC,[event_id] ASC)
321GO
322
323CREATE UNIQUE CLUSTERED INDEX [IDX_user_events]
324 ON [user_events] ([user_id] ASC,[event_id] ASC)
325GO
326
327ALTER TABLE [user_friends]
328 ADD CONSTRAINT [PK_user_friends]
329 PRIMARY KEY CLUSTERED ([user_id] ASC,[friend_id] ASC)
330GO
331
332CREATE UNIQUE CLUSTERED INDEX [IDX_friends]
333 ON [user_friends] ([friend_id] ASC,[user_id] ASC)
334GO
335
336ALTER TABLE [user_languages]
337 ADD CONSTRAINT [PK_user_languages]
338 PRIMARY KEY CLUSTERED ([user_id] ASC,[language_id] ASC)
339GO
340
341CREATE UNIQUE CLUSTERED INDEX [IDX_userlanguages]
342 ON [user_languages] ([user_id] ASC,[language_id] ASC)
343GO
344
345ALTER TABLE [users]
346 ADD CONSTRAINT [PK_users]
347 PRIMARY KEY CLUSTERED ([id] ASC)
348GO
349
350ALTER TABLE [users]
351 ADD CONSTRAINT [unique_email] UNIQUE NONCLUSTERED ([email] ASC)
352GO
353
354CREATE UNIQUE CLUSTERED INDEX [IDX_userId]
355 ON [users] ([id] ASC)
356GO
357
358/* Create Foreign Key Constraints */
359
360ALTER TABLE [chat_events] ADD CONSTRAINT [FK_chat_events_chats]
361 FOREIGN KEY ([chat_id]) REFERENCES [chats] ([id]) ON DELETE Set Null ON UPDATE Set Null
362GO
363
364ALTER TABLE [chat_events] ADD CONSTRAINT [FK_chat_events_events]
365 FOREIGN KEY ([event_id]) REFERENCES [events] ([id]) ON DELETE Set Null ON UPDATE Set Null
366GO
367
368ALTER TABLE [chat_users] ADD CONSTRAINT [FK_chat_users_chats]
369 FOREIGN KEY ([chat_id]) REFERENCES [chats] ([id]) ON DELETE Cascade ON UPDATE Cascade
370GO
371
372ALTER TABLE [chat_users] ADD CONSTRAINT [FK_chat_users_users]
373 FOREIGN KEY ([user_id]) REFERENCES [users] ([id]) ON DELETE Cascade ON UPDATE Cascade
374GO
375
376ALTER TABLE [chats] ADD CONSTRAINT [FK_chats_users]
377 FOREIGN KEY ([creator_id]) REFERENCES [users] ([id]) ON DELETE Set Null ON UPDATE Set Null
378GO
379
380ALTER TABLE [events] ADD CONSTRAINT [FK_events_users]
381 FOREIGN KEY ([creator_id]) REFERENCES [users] ([id])
382GO
383
384ALTER TABLE [friend_requests] ADD CONSTRAINT [FK_friend_requests_users]
385 FOREIGN KEY ([id_from]) REFERENCES [users] ([id]) ON DELETE Cascade ON UPDATE Cascade
386GO
387
388ALTER TABLE [friend_requests] ADD CONSTRAINT [FK_friend_requests_users_02]
389 FOREIGN KEY ([id_to]) REFERENCES [users] ([id]) ON DELETE Cascade ON UPDATE Cascade
390GO
391
392ALTER TABLE [messages] ADD CONSTRAINT [FK_messages_chats]
393 FOREIGN KEY ([chat_id]) REFERENCES [chats] ([id]) ON DELETE No Action ON UPDATE No Action
394GO
395
396ALTER TABLE [messages] ADD CONSTRAINT [FK_messages_users]
397 FOREIGN KEY ([sender_id]) REFERENCES [users] ([id]) ON DELETE No Action ON UPDATE No Action
398GO
399
400ALTER TABLE [user_auth] ADD CONSTRAINT [FK_user_auth_users]
401 FOREIGN KEY ([user_id]) REFERENCES [users] ([id]) ON DELETE Cascade ON UPDATE Cascade
402GO
403
404ALTER TABLE [user_events] ADD CONSTRAINT [FK_user_events_events]
405 FOREIGN KEY ([event_id]) REFERENCES [events] ([id]) ON DELETE Cascade ON UPDATE Cascade
406GO
407
408ALTER TABLE [user_events] ADD CONSTRAINT [FK_user_events_users]
409 FOREIGN KEY ([user_id]) REFERENCES [users] ([id])
410GO
411
412ALTER TABLE [user_friends] ADD CONSTRAINT [FK_user_friends_users]
413 FOREIGN KEY ([user_id]) REFERENCES [users] ([id]) ON DELETE Cascade ON UPDATE Cascade
414GO
415
416ALTER TABLE [user_friends] ADD CONSTRAINT [FK_user_friends_users_02]
417 FOREIGN KEY ([friend_id]) REFERENCES [users] ([id]) ON DELETE Cascade ON UPDATE Cascade
418GO
419
420ALTER TABLE [user_languages] ADD CONSTRAINT [FK_user_languages_languages]
421 FOREIGN KEY ([language_id]) REFERENCES [languages] ([id])
422GO
423
424ALTER TABLE [user_languages] ADD CONSTRAINT [FK_user_languages_users]
425 FOREIGN KEY ([user_id]) REFERENCES [users] ([id]) ON DELETE Cascade ON UPDATE Cascade
426GO