bDt2Jgcp

1DROP TABLE IF EXISTS Video;
2
3
4
5DROP TABLE IF EXISTS Comments;
6
7
8
9DROP TABLE IF EXISTS URL;
10
11
12
13DROP TABLE IF EXISTS Photo;
14
15
16
17DROP TABLE IF EXISTS Text1;
18
19
20
21DROP TABLE IF EXISTS Post;
22
23
24
25DROP TABLE IF EXISTS Member;
26
27
28
29DROP TABLE IF EXISTS Channel;
30
31
32
33DROP TABLE IF EXISTS Group1;
34
35
36
37DROP TABLE IF EXISTS Block1;
38
39
40
41DROP TABLE IF EXISTS Connection1;
42
43
44
45DROP TABLE IF EXISTS Settings;
46
47
48
49DROP TABLE IF EXISTS Password1;
50
51
52
53DROP TABLE IF EXISTS User;
54
55
56
57DROP SCHEMA dbo;
58
59
60
61DROP SCHEMA guest;
62
63
64
65DROP SCHEMA db_owner;
66
67
68
69DROP SCHEMA db_accessadmin;
70
71
72
73DROP SCHEMA db_securityadmin;
74
75
76
77DROP SCHEMA db_ddladmin;
78
79
80
81DROP SCHEMA db_backupoperator;
82
83
84
85DROP SCHEMA db_datareader;
86
87
88
89DROP SCHEMA db_datawriter;
90
91
92
93DROP SCHEMA db_denydatareader;
94
95
96
97DROP SCHEMA db_denydatawriter;
98
99
100
101CREATE SCHEMA dbo;
102
103
104
105CREATE SCHEMA guest;
106
107
108
109CREATE SCHEMA db_owner;
110
111
112
113CREATE SCHEMA db_accessadmin;
114
115
116
117CREATE SCHEMA db_securityadmin;
118
119
120
121CREATE SCHEMA db_ddladmin;
122
123
124
125CREATE SCHEMA db_backupoperator;
126
127
128
129CREATE SCHEMA db_datareader;
130
131
132
133CREATE SCHEMA db_datawriter;
134
135
136
137CREATE SCHEMA db_denydatareader;
138
139
140
141CREATE SCHEMA db_denydatawriter;
142
143
144CREATE TABLE User (
145 UserID      NUMERIC(14,0) NOT NULL ,
146 Email       VARCHAR(320) NOT NULL ,
147 Phone       VARCHAR(20) NOT NULL ,
148 Location    NVARCHAR(50) NULL ,
149 Birthday    DATE NOT NULL ,
150 School      NVARCHAR(50) NULL ,
151 Employer    NVARCHAR(50) NULL ,
152 Bio         NVARCHAR(1000) NULL ,
153 Sex         BIT NULL ,
154 Gender      TINYINT NULL ,
155 HomepageURL VARCHAR(512) NOT NULL ,
156
157 CONSTRAINT PK_User PRIMARY KEY CLUSTERED (UserID ASC)
158);
159
160
161
162CREATE TABLE Group1 (
163
164 GroupID     NUMERIC(12,0) NOT NULL ,
165 CreatorID   NUMERIC(14,0) NOT NULL ,
166 Description NVARCHAR(5000) NOT NULL ,
167
168 CONSTRAINT PK_Group PRIMARY KEY CLUSTERED (GroupID ASC),
169 CONSTRAINT FK_317 FOREIGN KEY (CreatorID)
170  REFERENCES User(UserID)
171);
172
173
174
175CREATE TABLE Block1
176(
177 BlockID     NUMERIC(17,0) NOT NULL ,
178 IssuerID    NUMERIC(14,0) NOT NULL ,
179 TargetID    NUMERIC(14,0) NOT NULL ,
180 DateAdded   DATETIME(6) NOT NULL ,
181 DateRemoved DATETIME(6) NOT NULL ,
182
183 CONSTRAINT PK_Block PRIMARY KEY CLUSTERED (BlockID ASC),
184 CONSTRAINT FK_262 FOREIGN KEY (IssuerID)
185  REFERENCES User(UserID),
186 CONSTRAINT FK_266 FOREIGN KEY (TargetID)
187  REFERENCES User(UserID)
188);
189
190
191
192
193CREATE TABLE Connection1
194(
195 ConnectionID NUMERIC(17,0) NOT NULL ,
196 ReqUserID    NUMERIC(14,0) NOT NULL ,
197 AckUserID    NUMERIC(14,0) NOT NULL ,
198 DateReq      DATETIME(6) NOT NULL ,
199 DateAck      DATETIME(6) NULL ,
200 Success      BIT NULL ,
201 DateEnded    DATETIME(6) NULL ,
202 WhoEnded     BIT NULL ,
203
204 CONSTRAINT PK_Relationship PRIMARY KEY CLUSTERED (ConnectionID ASC),
205 CONSTRAINT FK_237 FOREIGN KEY (ReqUserID)
206  REFERENCES User(UserID),
207 CONSTRAINT FK_241 FOREIGN KEY (AckUserID)
208  REFERENCES User(UserID)
209);
210
211
212
213CREATE TABLE Settings
214(
215 UserID          NUMERIC(14,0) NOT NULL ,
216 EmailPrivacy    BIT NULL ,
217 PhonePrivacy    BIT NULL ,
218 HomepagePrivacy BIT NULL ,
219 DetailsPrivacy  BIT NULL ,
220
221 CONSTRAINT PK_Settings PRIMARY KEY CLUSTERED (UserID ASC),
222 CONSTRAINT FK_151 FOREIGN KEY (UserID)
223  REFERENCES User(UserID)
224);
225
226
227
228
229CREATE TABLE Password1
230(
231 Hash        CHAR(60) NOT NULL ,
232 UserID      NUMERIC(14,0) NOT NULL ,
233 DateCreated DATETIME NOT NULL ,
234 IsActive    BIT NOT NULL ,
235
236 CONSTRAINT PK_Password PRIMARY KEY CLUSTERED (Hash ASC, UserID ASC),
237 CONSTRAINT FK_124 FOREIGN KEY (UserID)
238  REFERENCES User(UserID)
239);
240
241
242
243
244
245CREATE TABLE Member
246(
247 UserID     NUMERIC(14,0) NOT NULL ,
248 GroupID    NUMERIC(12,0) NOT NULL ,
249 DateJoined DATETIME(6) NOT NULL ,
250 DateLeft   DATETIME(6) NULL ,
251 Nickname   NVARCHAR(50) NULL ,
252 MemberID   NUMERIC(15,0) NOT NULL ,
253
254 CONSTRAINT PK_Member PRIMARY KEY CLUSTERED (MemberID ASC),
255 CONSTRAINT FK_301 FOREIGN KEY (UserID)
256  REFERENCES User(UserID),
257 CONSTRAINT FK_306 FOREIGN KEY (GroupID)
258  REFERENCES Group1(GroupID)
259);
260
261
262
263
264
265CREATE TABLE Channel
266(
267 ChannelID   NUMERIC(15,0) NOT NULL ,
268 GroupID     NUMERIC(12,0) NULL ,
269 DateCreated DATETIME(6) NOT NULL ,
270 Description VARCHAR(1000) NOT NULL ,
271 Visible     BIT NOT NULL ,
272
273 CONSTRAINT PK_Group PRIMARY KEY CLUSTERED (ChannelID ASC),
274 CONSTRAINT FK_291 FOREIGN KEY (GroupID)
275  REFERENCES Group1(GroupID)
276);
277
278
279
280
281CREATE TABLE Post
282(
283 PostID          NUMERIC(16,0) NOT NULL ,
284 AuthorID        NUMERIC(14,0) NOT NULL ,
285 ChannelID       NUMERIC(15,0) NOT NULL ,
286 DateCreated     DATETIME(6) NOT NULL ,
287 PostType        TINYINT NOT NULL ,
288 Scope           TINYINT NOT NULL ,
289 Deleted         BIT NOT NULL ,
290 AllowComments   BIT NULL ,
291 AllowRatings    BIT NULL ,
292 PreviousVersion NUMERIC(16,0) NULL ,
293
294 CONSTRAINT PK_Post PRIMARY KEY CLUSTERED (PostID ASC),
295 CONSTRAINT FK_94 FOREIGN KEY (AuthorID)
296  REFERENCES User(UserID),
297 CONSTRAINT FK_160 FOREIGN KEY (ChannelID)
298  REFERENCES Channel(ChannelID),
299 CONSTRAINT FK_321 FOREIGN KEY (PreviousVersion)
300  REFERENCES Post(PostID)
301);
302
303
304
305
306CREATE TABLE Video
307(
308 PostID NUMERIC(16,0) NOT NULL ,
309 URL    VARCHAR(512) NOT NULL ,
310
311 CONSTRAINT PK_Video PRIMARY KEY CLUSTERED (PostID ASC),
312 CONSTRAINT FK_213 FOREIGN KEY (PostID)
313  REFERENCES Post(PostID)
314);
315
316
317
318
319CREATE TABLE Comments
320(
321 CommentID       NUMERIC(18,0) NOT NULL ,
322 AuthorID        NUMERIC(14,0) NOT NULL ,
323 PostID          NUMERIC(16,0) NOT NULL ,
324 DateCreated     DATETIME(6) NOT NULL ,
325 Body            NVARCHAR(5000) NOT NULL ,
326 ParentID        NUMERIC(18,0) NULL ,
327 PreviousVersion NUMERIC(18,0) NOT NULL ,
328
329 CONSTRAINT PK_Comments PRIMARY KEY CLUSTERED (CommentID ASC),
330 CONSTRAINT FK_195 FOREIGN KEY (PostID)
331  REFERENCES Post(PostID),
332 CONSTRAINT FK_203 FOREIGN KEY (ParentID)
333  REFERENCES Comments(CommentID),
334 CONSTRAINT FK_207 FOREIGN KEY (AuthorID)
335  REFERENCES User(UserID),
336 CONSTRAINT FK_325 FOREIGN KEY (PreviousVersion)
337  REFERENCES Comments(CommentID)
338);
339
340
341
342
343
344CREATE TABLE URL
345(
346 PostID NUMERIC(16,0) NOT NULL ,
347 URL    VARCHAR(512) NOT NULL ,
348 Label  VARCHAR(100) NULL ,
349
350 CONSTRAINT PK_URL PRIMARY KEY CLUSTERED (PostID ASC),
351 CONSTRAINT FK_185 FOREIGN KEY (PostID)
352  REFERENCES Post(PostID)
353);
354
355
356
357CREATE TABLE Photo
358(
359 PostID    NUMERIC(16,0) NOT NULL ,
360 URL       VARCHAR(512) NOT NULL ,
361 Caption   NVARCHAR(5000) NOT NULL ,
362 DateTaken DATETIME(6) NULL ,
363
364 CONSTRAINT PK_Photo PRIMARY KEY CLUSTERED (PostID ASC),
365 CONSTRAINT FK_174 FOREIGN KEY (PostID)
366  REFERENCES Post(PostID)
367);
368
369
370
371CREATE TABLE Text1
372(
373 PostID NUMERIC(16,0) NOT NULL ,
374 Body   TEXT(50000) NOT NULL ,
375 Title  NVARCHAR(100) NOT NULL ,
376
377 CONSTRAINT PK_Text PRIMARY KEY CLUSTERED (PostID ASC),
378 CONSTRAINT FK_138 FOREIGN KEY (PostID)
379  REFERENCES Post(PostID)
380);