9XTU90X1

1USE master
2IF EXISTS( SELECT * FROM sys.databases WHERE name = 'projeto')
3	DROP DATABASE projeto;
4
5GO
6CREATE DATABASE projeto
7ON PRIMARY
8	(NAME = projeto_dat,
9	 FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\projetodat.mdf',
10	 SIZE = 10,
11	 MAXSIZE = 50,
12	 FILEGROWTH = 5)
13LOG ON 
14	(NAME = projeto_log,
15	 FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\projetolog.ldf',
16	 SIZE = 5,
17	 MAXSIZE = 25,
18	 FILEGROWTH = 5);
19GO
20
21USE projeto;
22GO
23
24CREATE TABLE [User](
25id INT IDENTITY(1,1) PRIMARY KEY,
26name VARCHAR(50) NOT NULL,
27email VARCHAR(50) UNIQUE NOT NULL,
28password_hash VARBINARY(128) NOT NULL,
29birthdate DATE NOT NULL,
30profile_image_id INT,
31bio VARCHAR(500),
32type VARCHAR(50) CHECK (type IN('User','Moderator','Admin')) NOT NULL,
33status VARCHAR(50) CHECK (status IN('Normal', 'Observation', 'Banned')) NOT NULL
34);
35
36
37CREATE TABLE Project(
38id int IDENTITY(1,1) PRIMARY KEY,
39user_id int NOT NULL,
40category_name VARCHAR(50) NOT NULL,
41title VARCHAR(50) NOT NULL,
42description VARCHAR(500),
43gallery_id int NOT NULL, 
44date DATETIME NOT NULL,
45status VARCHAR(50) CHECK(status IN('Approved', 'Not approved', 'Approving')) NOT NULL
46);
47
48
49CREATE TABLE Gallery(
50id int IDENTITY(1,1) PRIMARY KEY,
51description VARCHAR(50)
52);
53
54CREATE TABLE [Image](
55id int IDENTITY(1,1) PRIMARY KEY,
56content VARCHAR(MAX) NOT NULL
57); 
58
59CREATE TABLE Category(
60name VARCHAR(50) PRIMARY KEY,
61parent_category_name VARCHAR(50),
62);
63
64CREATE TABLE Comment(
65id INT IDENTITY(1,1) PRIMARY KEY,
66user_id INT NOT NULL,
67project_id INT NOT NULL,
68content VARCHAR(300) NOT NULL,
69parent_comment_id INT,
70date DATETIME NOT NULL,
71status VARCHAR(50) CHECK (status IN ('Visivel', 'Invisivel'))
72);
73 
74
75 /** 
76 
77 AO ALTERAR O VALOR DE COMMENT.STATUS
78 CONTAR O TOTAL DE COMENTARIOS DE USER X QUE SEJAM INVISIVEL
79 SE >= 10
80 ALTERAR O VALOR DE USER.STATUS
81 <-- aqui ter trigger para inserir no StatusChange
82 **/
83
84CREATE TABLE [Login](
85id int IDENTITY(1,1) PRIMARY KEY,
86user_id INT NOT NULL,
87device VARCHAR(50) NOT NULL,
88date DATETIME NOT NULL
89);
90
91CREATE TABLE [Notification](
92id INT IDENTITY(1,1) PRIMARY KEY,
93user_id INT NOT NULL,
94text VARCHAR(100) NOT NULL, 
95date DATETIME NOT NULL
96);
97
98CREATE TABLE Download(
99id INT IDENTITY(1,1) PRIMARY KEY,
100user_id INT NOT NULL,
101gallery_id INT NOT NULL,
102date DATETIME NOT NULL
103);
104
105CREATE TABLE Advertisement(
106id INT IDENTITY(1,1) PRIMARY KEY,
107company VARCHAR(50) NOT NULL,
108image_id INT NOT NULL,
109totalGained int,
110link VARCHAR(200) NOT NULL
111);
112
113
114CREATE TABLE [View](
115id INT IDENTITY(1,1) PRIMARY KEY,
116user_id INT NOT NULL UNIQUE,
117project_id INT NOT NULL,
118date DATETIME NOT NULL
119);
120
121
122CREATE TABLE StatusChange(
123id INT IDENTITY(1,1) PRIMARY KEY,
124user_id INT NOT NULL UNIQUE,
125moderator_id INT NOT NULL,
126new_status VARCHAR(50) CHECK (new_status IN('Normal', 'Observation', 'Banned')) NOT NULL,
127date DATETIME NOT NULL
128);
129
130CREATE TABLE UserFollowCategory(
131id INT IDENTITY(1,1) PRIMARY KEY,
132user_id INT NOT NULL,
133category_name VARCHAR(50) NOT NULL,
134date DATETIME NOT NULL,
135);
136
137CREATE TABLE UserFollowUser(
138id INT IDENTITY(1,1) PRIMARY KEY,
139user_id INT NOT NULL,
140followed_user_id INT NOT NULL,
141date DATETIME NOT NULL
142);
143
144CREATE TABLE UserRateProject(
145id INT IDENTITY(1,1) PRIMARY KEY,
146user_id INT NOT NULL,
147project_id INT NOT NULL,
148value INT CHECK(value IN (0,1,2,3,4,5,6,7,8,9,10)) NOT NULL
149);
150
151CREATE TABLE GalleryImage(
152id INT IDENTITY(1,1) PRIMARY KEY,
153gallery_id INT NOT NULL,
154image_id INT NOT NULL
155);
156
157CREATE TABLE ProjectAdvertisement(
158id INT IDENTITY(1,1) PRIMARY KEY,
159project_id INT NOT NULL,
160advertisement_id INT NOT NULL
161);
162
163
164ALTER TABLE [User]
165ADD FOREIGN KEY (profile_image_id) REFERENCES [Image](id);
166
167ALTER TABLE Project
168ADD FOREIGN KEY (user_id) REFERENCES [User](id);
169
170ALTER TABLE Project
171ADD FOREIGN KEY (category_name) REFERENCES Category(name);
172
173ALTER TABLE Project
174ADD FOREIGN KEY (gallery_id) REFERENCES Gallery(id);
175
176ALTER TABLE Category
177ADD FOREIGN KEY (parent_category_name) REFERENCES Category(name);
178
179ALTER TABLE Comment
180ADD FOREIGN KEY (user_id) REFERENCES [User](id);
181
182ALTER TABLE Comment
183ADD FOREIGN KEY (project_id) REFERENCES Project(id);
184
185ALTER TABLE Comment 
186ADD FOREIGN KEY (parent_comment_id) REFERENCES Comment(id);
187
188ALTER TABLE [Login]
189ADD FOREIGN KEY (user_id) REFERENCES [User](id);
190
191ALTER TABLE [Notification]
192ADD FOREIGN KEY (user_id) REFERENCES [User](id);
193
194ALTER TABLE Download
195ADD FOREIGN KEY (user_id) REFERENCES [User](id);
196
197ALTER TABLE Download
198ADD FOREIGN KEY (gallery_id) REFERENCES Gallery(id);
199
200ALTER TABLE Advertisement
201ADD FOREIGN KEY (image_id) REFERENCES [Image](id);
202
203ALTER TABLE [View]
204ADD FOREIGN KEY (user_id) REFERENCES [User](id);
205
206ALTER TABLE [View]
207ADD FOREIGN KEY (project_id) REFERENCES Project(id);
208
209ALTER TABLE StatusChange
210ADD FOREIGN KEY (user_id) REFERENCES [User](id);
211
212ALTER TABLE StatusChange
213ADD FOREIGN KEY (moderator_id) REFERENCES [User](id);
214
215ALTER TABLE UserFollowCategory
216ADD FOREIGN KEY (user_id) REFERENCES [User](id);
217
218ALTER TABLE UserFollowCategory
219ADD FOREIGN KEY (category_name) REFERENCES Category(name);
220
221ALTER TABLE UserFollowUser
222ADD FOREIGN KEY (user_id) REFERENCES [User](id);
223
224ALTER TABLE UserFollowUser
225ADD FOREIGN KEY (followed_user_id) REFERENCES [User](id);
226
227
228ALTER TABLE UserRateProject
229ADD FOREIGN KEY (user_id) REFERENCES [User](id);
230
231ALTER TABLE UserRateProject
232ADD FOREIGN KEY (project_id) REFERENCES Project(id);
233
234ALTER TABLE GalleryImage
235ADD FOREIGN KEY (gallery_id) REFERENCES Gallery(id);
236
237ALTER TABLE GalleryImage
238ADD FOREIGN KEY (image_id) REFERENCES [Image](id);
239
240ALTER TABLE ProjectAdvertisement
241ADD FOREIGN KEY (project_id) REFERENCES Project(id);
242
243ALTER TABLE ProjectAdvertisement
244ADD FOREIGN KEY (advertisement_id) REFERENCES Advertisement(id);
245
246
247-- trigger password hash
248
249IF OBJECT_ID('trgPasswordHash') IS NOT NULL
250	DROP TRIGGER trgPasswordHash;
251
252GO
253CREATE TRIGGER trgPasswordHash ON [User] 
254FOR INSERT
255AS
256BEGIN
257	UPDATE [User]
258	SET password_hash = HASHBYTES('SHA1', password_hash);
259END
260GO
261 
262--teste password hash
263/*
264
265
266INSERT INTO [User](name, email, password_hash, birthdate, type, status)
267		    VALUES('Tiago Santos', 'tiago.afsantos@hotmail.com', CAST('passwordteste' AS VARBINARY(128)), '19981113', 'User', 'Normal');
268			
269
270SELECT * FROM [User]
271
272*/
273
274--trigger notification
275-- cada vez que um project e upload ir ver a tabela user follow user para ver quem segue o gajo que deu upload e mandar notificaçao a esses id's
276
277IF OBJECT_ID('trgNotificationOnUserUpload') IS NOT NULL
278	DROP TRIGGER trgNotificationOnUserUpload;
279
280GO
281CREATE TRIGGER trgNotificationOnUserUpload ON Project
282AFTER INSERT 
283AS
284BEGIN
285
286	DECLARE @uploaderId int;
287
288	SELECT @uploaderId = (SELECT user_id FROM Project WHERE id = (SELECT MAX(id) FROM Project));
289	
290	DECLARE @cursorFollow as CURSOR;
291
292	SET @cursorFollow = CURSOR FOR 
293						(SELECT user_id FROM UserFollowUser WHERE followed_user_id = @uploaderId);
294	
295	DECLARE @userFollows int;
296	
297	OPEN @cursorFollow;
298	
299	FETCH NEXT FROM @cursorFollow into @userFollows;
300
301	WHILE @@FETCH_STATUS = 0
302
303		BEGIN
304
305			INSERT INTO [Notification] (user_id, text, date)
306								VALUES (@userFollows, 'Um utilizador que voce segue carregou um novo projeto!', GETDATE());
307
308		FETCH NEXT FROM @cursorFollow into @userFollows;
309
310		END
311
312	CLOSE @cursorFollow;
313
314	DEALLOCATE @cursorFollow;
315
316END
317GO
318
319
320IF OBJECT_ID('trgNotificationOnCategoryUpload') IS NOT NULL
321	DROP TRIGGER trgNotificationOnCategoryUpload;
322
323GO
324CREATE TRIGGER trgNotificationOnCategoryUpload ON Project
325AFTER INSERT 
326AS
327BEGIN
328
329	DECLARE @categoryName VARCHAR(50);
330	DECLARE @uploaderId int;
331
332	SELECT @categoryName = (SELECT category_name FROM Project WHERE id = (SELECT MAX(id) FROM Project));
333	SELECT @uploaderId = (SELECT user_id FROM Project WHERE id = (SELECT MAX(id) FROM Project));
334	
335	DECLARE @cursorFollow as CURSOR;
336
337	SET @cursorFollow = CURSOR FOR 
338						(SELECT user_id FROM UserFollowCategory WHERE category_name = @categoryName);
339	
340	DECLARE @userFollows int;
341	
342	OPEN @cursorFollow;
343	
344	FETCH NEXT FROM @cursorFollow into @userFollows;
345
346	WHILE @@FETCH_STATUS = 0
347
348		BEGIN
349			
350			IF (@userFollows != @uploaderId)
351				INSERT INTO [Notification] (user_id, text, date)
352									VALUES (@userFollows, 'Uma categoria que voce segue (' + @categoryName + ') tem um novo projeto!', GETDATE());
353
354		FETCH NEXT FROM @cursorFollow into @userFollows;
355
356		END
357
358	CLOSE @cursorFollow;
359
360	DEALLOCATE @cursorFollow;
361
362END
363GO
364
365--testes do trigger
366/*
367INSERT INTO [User](name, email, password_hash, birthdate, type, status)
368		    VALUES('Tiago Santos', 'tiago.afsantos@hotmail.com', CAST('passwordteste' AS VARBINARY(128)), '19981113', 'User', 'Normal')
369
370INSERT INTO [User](name, email, password_hash, birthdate, type, status)
371		    VALUES('Tiago 1234', 'tiago.1234@hotmail.com', CAST('passwordteste' AS VARBINARY(128)), '19981113', 'User', 'Normal');
372
373INSERT INTO Category(name) values ('Digital Art');
374
375INSERT INTO UserFollowCategory(user_id, category_name, date) VALUES (1, 'Digital Art', GETDATE());
376
377INSERT INTO Gallery(description) values (null);
378
379INSERT INTO Project(user_id, category_name, title, description, gallery_id, date, status) 
380		     VALUES(2, 'Digital Art', 'Arte', 'Descricao', 1, GETDATE(), 'Approved');
381
382SELECT * FROM [Notification];
383*/
384
385
386IF OBJECT_ID('trgCommentNotification') IS NOT NULL
387	DROP TRIGGER trgCommentNotification;
388
389GO
390CREATE TRIGGER trgCommentNotification ON Comment
391AFTER INSERT 
392AS
393BEGIN
394
395	IF (SELECT parent_comment_id FROM Comment WHERE id = (SELECT MAX(id) FROM Comment)) IS NOT NULL
396	BEGIN
397
398		DECLARE @replyUsername VARCHAR(50);
399		DECLARE @replyUserId int;
400		DECLARE @originalUserId int;
401
402		SELECT @replyUserId = (SELECT user_id FROM Comment WHERE id = (SELECT MAX(id) FROM Comment));
403		SELECT @replyUsername = (SELECT name FROM [User] WHERE id = @replyUserId);
404		SELECT @originalUserId = (SELECT user_id FROM Comment WHERE id = ( ( SELECT parent_comment_id FROM Comment WHERE id = (SELECT MAX(id) FROM Comment))));
405		
406
407		INSERT INTO [Notification] (user_id, text, date)
408		VALUES (@replyUserId, '' + @replyUsername + ' respondeu ao seu comentario', GETDATE());
409	
410	END
411
412END
413GO
414
415/** UTILIZADORES **/
416INSERT INTO [User](name, email, password_hash, birthdate, type, status)
417		    VALUES('Tiago Santos', 'tiago.afsantos@hotmail.com', CAST('passwordteste' AS VARBINARY(128)), '19981113', 'User', 'Normal')
418
419INSERT INTO [User](name, email, password_hash, birthdate, type, status)
420		    VALUES('Ruben Amendoeira', 'ruben.amendoeira@gmail.com', CAST('passwordteste2' AS VARBINARY(128)), '19961206', 'User', 'Normal');
421
422INSERT INTO [User](name, email, password_hash, birthdate, type, status)
423		    VALUES('Hugo Ferreira', 'hugoferreira@gmail.com', CAST('benfica123' AS VARBINARY(128)), '11980216', 'User', 'Normal');
424
425INSERT INTO [User](name, email, password_hash, birthdate, type, status)
426		    VALUES('Tiago Neto', 'tiagoneto@gmail.com', CAST('pokemon1998' AS VARBINARY(128)), '11980121', 'User', 'Normal');
427
428INSERT INTO [User](name, email, password_hash, birthdate, type, status)
429		    VALUES('Tomás Santos', 'tomas.santos24@gmail.com', CAST('password123' AS VARBINARY(128)), '11961103', 'User', 'Normal');
430			
431INSERT INTO [User](name, email, password_hash, birthdate, type, status)
432		    VALUES('João Almeida', 'joaoalmeida@gmail.com', CAST('password001' AS VARBINARY(128)), '11951123', 'Moderator', 'Normal');
433		
434INSERT INTO [User](name, email, password_hash, birthdate, type, status)
435		    VALUES('Pedro Nunes', 'nunes1121@gmail.com', CAST('helloworld' AS VARBINARY(128)), '11950114', 'Moderator', 'Normal');
436			
437INSERT INTO [User](name, email, password_hash, birthdate, type, status)
438		    VALUES('Diogo Silva', 'silvadiogo@gmail.com', CAST('helloworld12' AS VARBINARY(128)), '11960704', 'Admin', 'Normal');
439
440
441/** CATEGORIAS **/
442INSERT INTO Category(name) VALUES ('Digital Art');
443INSERT INTO Category(name) VALUES ('Photography');
444
445INSERT INTO Category(name, parent_category_name) VALUES ('3D Art', 'Digital Art');
446INSERT INTO Category(name, parent_category_name) VALUES ('Animation', 'Digital Art')
447INSERT INTO Category(name, parent_category_name) VALUES ('Pixel Art', 'Digital Art');
448INSERT INTO Category(name, parent_category_name) VALUES ('Typography', 'Digital Art');
449
450INSERT INTO Category(name, parent_category_name) VALUES ('Landscapes', 'Photography');
451INSERT INTO Category(name, parent_category_name) VALUES ('Portraits', 'Photography');
452INSERT INTO Category(name, parent_category_name) VALUES ('Photomanipulation', 'Photography');
453
454/** GALERIAS **/
455INSERT INTO Gallery(description) VALUES (null);
456INSERT INTO Gallery(description) VALUES (null);
457INSERT INTO Gallery(description) VALUES (null);
458INSERT INTO Gallery(description) VALUES (null);
459
460/** IMAGENS **/
461INSERT INTO Image(content) VALUES('');
462INSERT INTO Image(content) VALUES('');
463INSERT INTO Image(content) VALUES('');
464INSERT INTO Image(content) VALUES('');
465INSERT INTO Image(content) VALUES('');
466INSERT INTO Image(content) VALUES('');
467INSERT INTO Image(content) VALUES('');
468INSERT INTO Image(content) VALUES('');
469INSERT INTO Image(content) VALUES('');
470INSERT INTO Image(content) VALUES('');
471
472/** INSERIR IMAGENS NUMA GALERIA **/
473INSERT INTO GalleryImage(gallery_id, image_id) VALUES(1, 1);
474INSERT INTO GalleryImage(gallery_id, image_id) VALUES(1, 2);
475INSERT INTO GalleryImage(gallery_id, image_id) VALUES(1, 3);
476INSERT INTO GalleryImage(gallery_id, image_id) VALUES(2, 4);
477INSERT INTO GalleryImage(gallery_id, image_id) VALUES(2, 5);
478INSERT INTO GalleryImage(gallery_id, image_id) VALUES(3, 6);
479INSERT INTO GalleryImage(gallery_id, image_id) VALUES(3, 7);
480INSERT INTO GalleryImage(gallery_id, image_id) VALUES(3, 8);
481INSERT INTO GalleryImage(gallery_id, image_id) VALUES(3, 9);
482INSERT INTO GalleryImage(gallery_id, image_id) VALUES(4, 10);
483
484/** UTILIZADOR SEGUE UMA CATEGORIA **/
485INSERT INTO UserFollowCategory(user_id, category_name, date) VALUES(1, 'Typography', GETDATE());
486INSERT INTO UserFollowCategory(user_id, category_name, date) VALUES(2, 'Animation', GETDATE());
487
488/** UTILIZADOR SEGUE OUTRO UTILIZADOR **/
489INSERT INTO UserFollowUser(user_id, followed_user_id, date) VALUES(1, 2, GETDATE());
490INSERT INTO UserFollowUser(user_id, followed_user_id, date) VALUES(2, 4, GETDATE());
491
492/** PROJETOS **/
493INSERT INTO Project(user_id, category_name, title, description, gallery_id, date, status) 
494		     VALUES(2, 'Landscapes', 'Serra da Estrela', 'Fotos do fim de semana.', 1, GETDATE(), 'Approved');
495INSERT INTO Project(user_id, category_name, title, description, gallery_id, date, status) 
496		     VALUES(3, 'Typography', 'Branding IPS', 'Branding showcase para o IPS.', 2, GETDATE(), 'Approved');
497INSERT INTO Project(user_id, category_name, title, description, gallery_id, date, status) 
498		     VALUES(3, 'Animation', 'Teste de animação de liquidos.', '', 3, GETDATE(), 'Approved');
499INSERT INTO Project(user_id, category_name, title, description, gallery_id, date, status) 
500		     VALUES(4, 'Digital Art', 'Financial App UI', '', 4, GETDATE(), 'Approved');
501
502/** VISUALIZAÇÕES **/
503INSERT INTO [View](user_id, project_id, date) VALUES(1, 1, GETDATE());
504INSERT INTO [View](user_id, project_id, date) VALUES(2, 1, GETDATE());
505INSERT INTO [View](user_id, project_id, date) VALUES(3, 2, GETDATE());
506INSERT INTO [View](user_id, project_id, date) VALUES(4, 3, GETDATE());
507INSERT INTO [View](user_id, project_id, date) VALUES(2, 4, GETDATE());
508
509/** AVALIAÇÕES **/
510INSERT INTO UserRateProject(user_id, project_id, value) VALUES(1, 3, 7);
511INSERT INTO UserRateProject(user_id, project_id, value) VALUES(3, 2, 9);
512
513
514/** COMENTÁRIOS **/
515INSERT INTO Comment(user_id, project_id, content, date) VALUES (1, 1, 'teste', GETDATE());
516INSERT INTO Comment(user_id, project_id, content, parent_comment_id, date) VALUES (2, 1, 'teste2', 4,  GETDATE());
517INSERT INTO Comment(user_id, project_id, content, date) VALUES (3, 2, 'teste3',  GETDATE());
518
519/** PUBLICIDADE **/
520INSERT INTO Advertisement(company, image_id, link) VALUES('Coca Cola', 3, 'http://www.cocacola.com');
521INSERT INTO Advertisement(company, image_id, link) VALUES('McDonalds', 6, 'http://www.mcdonalds.com/promotions');
522
523/** CONECTAR PUBLICIDADES A PROJETOS **/
524INSERT INTO ProjectAdvertisement(project_id, advertisement_id) VALUES(2, 1);
525INSERT INTO ProjectAdvertisement(project_id, advertisement_id) VALUES(4, 2);
526
527/** DOWNLOADS **/
528INSERT INTO Download(user_id, gallery_id, date) VALUES(2, 1, GETDATE());
529INSERT INTO Download(user_id, gallery_id, date) VALUES(3, 2, GETDATE());
530INSERT INTO Download(user_id, gallery_id, date) VALUES(3, 4, GETDATE());
531
532/** INICIOS DE SESSÃO **/
533INSERT INTO Login(user_id, device, date) VALUES(3, 'Samsung-673D', GETDATE());
534INSERT INTO Login(user_id, device, date) VALUES(1, 'Acer 524', GETDATE());
535INSERT INTO Login(user_id, device, date) VALUES(2, 'iPhone X 8719', GETDATE());
536
537SELECT * FROM [Notification];