· 7 years ago · Nov 20, 2018, 05:46 PM
1DROP DATABASE iEgypt_db
2--Creating database
3CREATE DATABASE iEgypt_db;
4GO
5--Using the database
6USE iEgypt_db;
7-----------------------------------------------------------------------------------------------------------------------
8--Creating tables
9
10--Users
11CREATE TABLE "User" (
12 ID INTEGER PRIMARY KEY IDENTITY,
13 email VARCHAR(255) UNIQUE NOT NULL,
14 first_name VARCHAR(255) NOT NULL,
15 middle_name VARCHAR(255),
16 last_name VARCHAR(255) NOT NULL,
17 birth_date DATE NOT NULL,
18 age AS (YEAR(CURRENT_TIMESTAMP) - YEAR(birth_date)),
19 password VARCHAR(255) NOT NULL,
20 last_login DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
21 active BIT NOT NULL DEFAULT 1
22);
23GO ;
24
25--Viewers
26CREATE TABLE "Viewer" (
27 ID INTEGER PRIMARY KEY REFERENCES "User" (ID),
28 working_place VARCHAR(255),
29 working_place_type VARCHAR(255),
30 working_place_description TEXT
31);
32GO ;
33
34--Notified Person
35CREATE TABLE "Notified_Person" (
36 ID INTEGER PRIMARY KEY
37);
38GO ;
39
40--Contributors
41CREATE TABLE "Contributor" (
42 ID INTEGER PRIMARY KEY REFERENCES "User" (ID),
43 years_of_experience INTEGER,
44 portfolio_link TEXT,
45 specialization TEXT,
46 notified_id INTEGER NOT NULL REFERENCES "Notified_Person" (ID),
47 average_upload_time DATETIME
48);
49GO ;
50
51--Staff
52CREATE TABLE "Staff" (
53 ID INTEGER PRIMARY KEY REFERENCES "User" (ID),
54 hire_date DATE NOT NULL,
55 working_hours INT NOT NULL,
56 payment_rate REAL NOT NULL,
57 total_salary AS (working_hours * payment_rate),
58 notified_id INTEGER NOT NULL REFERENCES "Notified_Person" (ID)
59);
60GO ;
61
62--Content type
63CREATE TABLE "Content_type" (
64 type VARCHAR(255) PRIMARY KEY NOT NULL
65);
66GO ;
67
68--Content Manager
69CREATE TABLE "Content_Manager" (
70 ID INTEGER PRIMARY KEY REFERENCES "Staff" (ID),
71 type VARCHAR(255) REFERENCES "Content_type" (type)
72);
73GO ;
74
75--Reviewer
76CREATE TABLE "Reviewer" (
77 ID INTEGER PRIMARY KEY REFERENCES "Staff" (ID),
78);
79GO ;
80
81--Messages
82CREATE TABLE "Message" (
83 sent_at DATETIME NOT NULL,
84 contributor_id INTEGER NOT NULL REFERENCES "Contributor" (ID),
85 viewer_id INTEGER NOT NULL REFERENCES "Viewer" (ID),
86 sender_type BIT NOT NULL,
87 read_at DATETIME NOT NULL,
88 text TEXT NOT NULL,
89 read_status TEXT NOT NULL DEFAULT 'NOT READ',
90 PRIMARY KEY (sent_at, contributor_id, viewer_id, sender_type)
91);
92GO ;
93
94--Category
95CREATE TABLE "Category" (
96 type VARCHAR(255) PRIMARY KEY NOT NULL,
97 description TEXT
98);
99GO ;
100
101--Subcategory
102CREATE TABLE "Sub_Category" (
103 category_type VARCHAR(255) NOT NULL REFERENCES "Category" (type),
104 name VARCHAR(255) NOT NULL,
105 CONSTRAINT PK_Subcategory PRIMARY KEY (category_type, name)
106);
107GO ;
108
109--Notification object
110CREATE TABLE "Notification_Object" (
111 ID INTEGER PRIMARY KEY NOT NULL IDENTITY
112);
113GO ;
114
115--New request
116CREATE TABLE "New_Request" (
117 ID INTEGER PRIMARY KEY NOT NULL IDENTITY,
118 accept_status BIT,
119 accept_time DATETIME,
120 specified BIT NOT NULL,
121 information TEXT NOT NULL,
122 viewer_id INTEGER NOT NULL REFERENCES "Viewer" (ID),
123 notif_obj_id INTEGER NOT NULL UNIQUE REFERENCES "Notification_Object" (ID),
124 contributor_id INTEGER REFERENCES "Contributor" (ID)
125);
126GO ;
127
128--Content
129CREATE TABLE "Content" (
130 ID INTEGER PRIMARY KEY NOT NULL IDENTITY,
131 uploaded_at DATETIME NOT NULL,
132 contributor_id INTEGER NOT NULL REFERENCES "Contributor" (ID),
133 category_type VARCHAR(255) NOT NULL,
134 subcategory_name VARCHAR(255) NOT NULL,
135 type VARCHAR(255) REFERENCES "Content_type" (type),
136 link VARCHAR(255),
137 CONSTRAINT FK FOREIGN KEY (category_type, subcategory_name) REFERENCES "Sub_Category" (category_type, name)
138);
139GO ;
140
141--Original content
142CREATE TABLE "Original_Content" (
143 ID INTEGER PRIMARY KEY NOT NULL REFERENCES "Content" (ID)
144 ON DELETE CASCADE,
145 content_manager_id INTEGER REFERENCES "Content_Manager" (ID),
146 reviewer_id INTEGER REFERENCES "Reviewer" (ID),
147 review_status BIT,
148 filter_status BIT,
149 rating INTEGER
150);
151GO ;
152
153--Existing request
154CREATE TABLE "Existing_Request" (
155 ID INTEGER PRIMARY KEY NOT NULL IDENTITY,
156 original_content_id INTEGER NOT NULL REFERENCES "Original_Content" (ID)
157 ON DELETE CASCADE,
158 viewer_id INTEGER NOT NULL REFERENCES "Viewer" (ID)
159 ON DELETE CASCADE
160);
161GO ;
162
163--New content
164CREATE TABLE "New_Content" (
165 ID INTEGER PRIMARY KEY NOT NULL REFERENCES "Content" (ID)
166 ON DELETE CASCADE,
167 new_request_id INTEGER NOT NULL REFERENCES "New_Request" (ID)
168 ON DELETE CASCADE
169);
170GO ;
171
172--Comment
173CREATE TABLE "Comment" (
174 viewer_id INTEGER NOT NULL REFERENCES "Viewer" (ID)
175 ON DELETE CASCADE,
176 original_content_id INTEGER NOT NULL REFERENCES "Original_Content" (ID)
177 ON DELETE CASCADE,
178 date DATETIME NOT NULL,
179 text TEXT NOT NULL,
180 CONSTRAINT PK_Comment PRIMARY KEY (viewer_id, original_content_id, date)
181);
182GO ;
183
184--Rate
185CREATE TABLE "Rate" (
186 viewer_id INTEGER NOT NULL REFERENCES "Viewer" (ID)
187 ON DELETE CASCADE,
188 original_content_id INTEGER NOT NULL REFERENCES "Original_Content" (ID)
189 ON DELETE CASCADE,
190 date DATETIME NOT NULL,
191 rate INTEGER,
192 CONSTRAINT PK_Rate PRIMARY KEY (viewer_id, original_content_id)
193);
194GO ;
195
196--Event
197CREATE TABLE "Event" (
198 ID INTEGER PRIMARY KEY NOT NULL IDENTITY,
199 description TEXT NOT NULL,
200 location VARCHAR(255) NOT NULL,
201 city VARCHAR(255) NOT NULL,
202 time DATETIME NOT NULL,
203 entertainer VARCHAR(255) NOT NULL,
204 notification_object_id INTEGER NOT NULL UNIQUE REFERENCES "Notification_Object" (ID),
205 viewer_id INTEGER NOT NULL REFERENCES "Viewer" (ID)
206);
207GO ;
208
209--Event photos link
210CREATE TABLE "Event_Photos_Link" (
211 event_id INTEGER NOT NULL REFERENCES "Event" (ID),
212 link VARCHAR(255) NOT NULL,
213 CONSTRAINT PK_Event_Photos_Link PRIMARY KEY (event_id, link)
214);
215GO ;
216
217--Event videos link
218CREATE TABLE "Event_Videos_Link" (
219 event_id INTEGER NOT NULL REFERENCES "Event" (ID),
220 link VARCHAR(255) NOT NULL,
221 CONSTRAINT PK_Event_Videos_Link PRIMARY KEY (event_id, link)
222);
223GO ;
224
225--Advertisement
226CREATE TABLE "Advertisement" (
227 ID INTEGER PRIMARY KEY NOT NULL IDENTITY,
228 description TEXT,
229 location VARCHAR(255),
230 event_id INTEGER REFERENCES "Event" (ID),
231 viewer_id INTEGER NOT NULL REFERENCES "Viewer" (ID)
232 ON DELETE CASCADE
233)
234GO ;
235
236--Ad video link
237CREATE TABLE "Ads_Video_Link" (
238 advertisement_id INTEGER NOT NULL REFERENCES "Advertisement" (ID)
239 ON DELETE CASCADE,
240 link VARCHAR(255) NOT NULL,
241 CONSTRAINT PK_Ads_Video_Link PRIMARY KEY (advertisement_id, link)
242);
243GO ;
244
245--Ad photos link
246CREATE TABLE "Ads_Photos_Link" (
247 advertisement_id INTEGER NOT NULL REFERENCES "Advertisement" (ID)
248 ON DELETE CASCADE,
249 link VARCHAR(255) NOT NULL,
250 CONSTRAINT PK_Ads_Photos_Link PRIMARY KEY (advertisement_id, link)
251);
252GO ;
253
254--Announcement
255CREATE TABLE "Announcement" (
256 ID INTEGER PRIMARY KEY NOT NULL IDENTITY,
257 seen_at DATETIME,
258 sent_at DATETIME,
259 notified_person_id INTEGER NOT NULL REFERENCES "Notified_Person" (ID)
260 ON DELETE CASCADE,
261 notification_object_id INTEGER NOT NULL REFERENCES "Notification_Object" (ID)
262 ON DELETE CASCADE
263);
264GO ;
265
266-----------------------------------------------------------------------------------------------------------------------
267--Insert categories >= 3
268INSERT INTO "Category" (type) VALUES
269 ('Pictures'),
270 ('Music'),
271 ('Video Games');
272
273--Insert subcategories >= 2
274INSERT INTO "Sub_Category" (category_type, name) VALUES
275 ('Music', 'Blues'),
276 ('Music', 'Swing'),
277 ('Video Games', 'RPG'),
278 ('Video Games', 'Indie'),
279 ('Pictures', 'Abstract'),
280 ('Pictures', 'Nature');
281
282--Insert Users >= 13
283INSERT INTO "User" (email, first_name, last_name, birth_date, password) VALUES
284 ('viewer1@mail', 'Viewer1', 'lastname', '1/1/1990', 'passwd'), --1
285 ('viewer2@mail', 'Viewer2', 'lastname', '1/1/1990', 'passwd'), --2
286 ('viewer3@mail', 'Viewer3', 'lastname', '1/1/1990', 'passwd'), --3
287
288 ('cont1@mail', 'cont1', 'lastname', '1/1/1990', 'passwd'), --4
289 ('cont2@mail', 'cont2', 'lastname', '1/1/1990', 'passwd'), --5
290 ('cont3@mail', 'cont3', 'lastname', '1/1/1990', 'passwd'), --6
291 ('cont4@mail', 'cont4', 'lastname', '1/1/1990', 'passwd'), --7
292 ('cont5@mail', 'cont5', 'lastname', '1/1/1990', 'passwd'), --8
293
294 ('authrev1@mail', 'authrev1', 'lastname', '1/1/1990', 'passwd'), --9
295 ('authrev2@mail', 'authrev2', 'lastname', '1/1/1990', 'passwd'), --10
296
297 ('cman1@mail', 'cman1', 'lastname', '1/1/1990', 'passwd'), --11
298 ('cman2@mail', 'cman2', 'lastname', '1/1/1990', 'passwd'), --12
299 ('cman3@mail', 'cman3', 'lastname', '1/1/1990', 'passwd'); --13
300
301--Notified person >= 10
302INSERT INTO "Notified_Person" (ID) VALUES--THIS DOESN'T MAKE MUCH SENSE
303 (1),
304 (2),
305 (3),
306 (4),
307 (5),
308 (6),
309 (7),
310 (8),
311 (9),
312 (10);
313
314--Viewers >= 3
315INSERT INTO "Viewer" (ID, working_place, working_place_type) VALUES
316 (1, 'wp1', 'wpt1'),
317 (2, 'wp2', 'wpt2'),
318 (3, 'wp3', 'wpt3');
319
320--Contributors >= 5
321INSERT INTO "Contributor" (ID, notified_id) VALUES
322 (4, 1),
323 (5, 2),
324 (6, 3),
325 (7, 4),
326 (8, 5);
327
328--Adding staff >= 2+3
329INSERT INTO "Staff" (ID, hire_date, working_hours, payment_rate, notified_id) VALUES
330 (9, '1/1/2002', 8, 100, 6),
331 (10, '1/1/2002', 8, 100, 7),
332 (11, '1/1/2002', 8, 100, 8),
333 (12, '1/1/2002', 8, 100, 9),
334 (13, '1/1/2002', 8, 100, 10);
335
336--Reviewers >= 2
337INSERT INTO "Reviewer" (ID) VALUES
338 (9),
339 (10);
340
341--Content managers >= 3
342--Content_type needed first
343INSERT INTO "Content_type" (type) VALUES
344 ('Art');
345
346INSERT INTO "Content_Manager" (ID, type) VALUES
347 (11, 'Art'),
348 (12, 'Art'),
349 (13, 'Art');
350
351--Content >= 3 + 3
352INSERT INTO "Content" (uploaded_at, contributor_id, category_type, subcategory_name, type) VALUES
353 ('2010-01-01T00:00:00', 4, 'Music', 'Blues', 'Art'),--1
354 ('2010-01-01T00:00:00', 4, 'Music', 'Blues', 'Art'),--2
355 ('2010-01-01T00:00:00', 5, 'Music', 'Blues', 'Art'),--3
356
357 ('2010-01-01T00:00:00', 6, 'Music', 'Blues', 'Art'),--4
358 ('2010-01-01T00:00:00', 6, 'Music', 'Blues', 'Art'),--5
359 ('2010-01-01T00:00:00', 6, 'Music', 'Blues', 'Art');--6
360
361--Original content >= 3
362INSERT INTO "Original_Content" (ID, content_manager_id, reviewer_id, review_status, filter_status, rating) VALUES
363 (4, 11, 9, 1, 1, 5),
364 (5, 11, 9, 1, 1, 5),
365 (6, 11, 9, 1, 1, 5);
366
367--Existing requests >= 2
368INSERT INTO "Existing_Request" (original_content_id, viewer_id) VALUES
369 (4, 1),
370 (6, 2);
371
372--Notification objects >= 13
373SET IDENTITY_INSERT "Notification_Object" ON;
374INSERT INTO "Notification_Object" (ID) VALUES
375 (1),
376 (2),
377 (3),
378 (4),
379 (5),
380 (6),
381 (7),
382 (8),
383 (9),
384 (10),
385 (11),
386
387 (12),
388 (13);
389SET IDENTITY_INSERT "Notification_Object" OFF;
390
391--New requests >= 11
392-- Two of them should have the same contributor and
393-- the requests are accepted. Three of the new requests have another contributor and accepted and
394-- each has a new content related to it. Three new requests have different contributors and accepted
395-- but do not have content. Three new requests don't have a certain contributor and aren't accepted.
396INSERT INTO "New_Request" (accept_status, specified, information, viewer_id, notif_obj_id, contributor_id) VALUES
397 (1, 1, 'req1', 1, 1, 4),--1
398 (1, 1, 'req2', 1, 2, 4),--2
399
400 (1, 1, 'req3', 1, 3, 5),--3
401 (1, 1, 'req4', 1, 4, 5),--4
402 (1, 1, 'req5', 1, 5, 5),--5
403
404 (1, 1, 'req6', 1, 6, 6),--6
405 (1, 1, 'req7', 1, 7, 7),--7
406 (1, 1, 'req8', 1, 8, 8),--8
407
408 (0, 0, 'req9', 1, 9, NULL),--9
409 (0, 0, 'req10', 1, 10, NULL),--10
410 (0, 0, 'req11', 1, 11, NULL);--11
411
412--New content >= 3
413INSERT INTO "New_Content" (ID, new_request_id) VALUES
414 (1, 3),
415 (2, 4),
416 (3, 5);
417
418--Events >= 2
419INSERT INTO "Event" (description, location, city, time, entertainer, notification_object_id, viewer_id) VALUES
420 ('Event1', 'avenue', 'city', '2010-01-01T00:00:00', 'entertainer', 12, 1),--1
421 ('Event2', 'avenue', 'city', '2010-01-01T00:00:00', 'entertainer', 13, 2);--2
422
423--Announcements
424INSERT INTO "Announcement" (notification_object_id, notified_person_id) VALUES
425(1, 1),
426(1, 1),
427
428(3, 2),
429(4, 2),
430(5, 2),
431
432(6, 3),
433(7, 4),
434(8, 5),
435
436(9, 1),
437(9, 2),
438(9, 3),
439(9, 4),
440(9, 5),
441(9, 6),
442(9, 7),
443(9, 8),
444(9, 9),
445(9, 10),
446(10, 1),
447(10, 2),
448(10, 3),
449(10, 4),
450(10, 5),
451(10, 6),
452(10, 7),
453(10, 8),
454(10, 9),
455(10, 10),
456(11, 1),
457(11, 2),
458(11, 3),
459(11, 4),
460(11, 5),
461(11, 6),
462(11, 7),
463(11, 8),
464(11, 9),
465(11, 10),
466(12, 1),
467(12, 2),
468(12, 3),
469(12, 4),
470(12, 5),
471(12, 6),
472(12, 7),
473(12, 8),
474(12, 9),
475(12, 10),
476(13, 1),
477(13, 2),
478(13, 3),
479(13, 4),
480(13, 5),
481(13, 6),
482(13, 7),
483(13, 8),
484(13, 9),
485(13, 10);
486
487--Advertisements >= 2
488INSERT INTO "Advertisement" (description, location, event_id, viewer_id) VALUES
489 ('Ad1', 'avenue', 1, 1),
490 ('Ad2', 'avenue', 2, 2);
491-----------------------------------------------------------------------------------------------------------------------
492GO;
493CREATE PROC Original_Content_Search(@typename VARCHAR(255) = NULL, @categoryname VARCHAR(255) = NULL) AS
494 BEGIN
495 SELECT oc.*
496 FROM "Original_Content" oc,
497 "Content" c
498 WHERE (c.category_type LIKE '%' + @categoryname + '%' OR
499 c.type LIKE '%' + @typename + '%' AND c.ID = oc.ID AND oc.filter_status = 1 AND oc.review_status = 1);
500 END
501GO ;
502
503CREATE PROC Contributor_Search(@fullname VARCHAR(255)) AS
504 BEGIN
505 SELECT c.*
506 FROM "User" u,
507 "Contributor" c
508 WHERE (u.ID = c.ID AND (u.first_name + ' ' + u.last_name) LIKE '%' + @fullname + '%');
509 END
510GO ;
511
512CREATE PROC Register_User @usertype VARCHAR(255), @email VARCHAR(255), @password VARCHAR(255),
513 @firstname VARCHAR(255), @middlename VARCHAR(255),
514 @lastname VARCHAR(255), @birth_date DATETIME,
515 @working_place_name VARCHAR(255) = NULL,
516 @working_place_type VARCHAR(255) = NULL,
517 @wokring_place_description VARCHAR(255) = NULL,
518 @specilization VARCHAR(255) = NULl, @portofolio_link VARCHAR(255) = NULl,
519 @years_experience INTEGER = NULl, @hire_date DATETIME = NULl,
520 @working_hours INTEGER = NULl, @payment_rate REAL = NULl,
521 @user_id INTEGER OUTPUT
522AS
523 BEGIN
524 IF (@usertype = 'Viewer' OR @usertype = 'Contributor' OR @usertype = 'Authorized Reviewer' OR
525 @usertype = 'Content Manager')
526 BEGIN
527 DECLARE @temp INTEGER;
528 --ADD USER
529 INSERT INTO "User" (email, first_name, middle_name, last_name, birth_date, password)
530 VALUES (@email, @firstname, @middlename, @lastname, @birth_date, @password);
531 --GETTING ID
532 SELECT @user_id = u.ID FROM "User" u WHERE u.email = @email;
533 --Add USER type
534 IF @usertype = 'Viewer'
535 BEGIN
536 INSERT INTO "Viewer" (ID, working_place, working_place_type, working_place_description)
537 VALUES (@user_id, @working_place_name, @working_place_type, @wokring_place_description);
538 END
539 ELSE IF @usertype = 'Contributor'
540 BEGIN
541 --Creating new notified person entry
542 SELECT @temp = max(ID) FROM "Notified_Person";
543 SET IDENTITY_INSERT "Notified_Person" ON;
544 INSERT INTO "Notified_Person" (ID) VALUES (@temp + 1);
545 SET IDENTITY_INSERT "Notified_Person" OFF;
546 --Creating the contributor entry
547 INSERT INTO "Contributor" (ID, years_of_experience, portfolio_link, specialization, notified_id)
548 VALUES (@user_id, @years_experience, @portofolio_link, @specilization, @temp + 1);
549 END
550 ELSE IF @usertype = 'Authorized Reviewer'
551 BEGIN
552 --DECLARE @temp INTEGER;
553 --Creating new notified person entry
554 SELECT @temp = max(ID) FROM "Notified_Person";
555 SET IDENTITY_INSERT "Notified_Person" ON;
556 INSERT INTO "Notified_Person" (ID) VALUES (@temp + 1);
557 SET IDENTITY_INSERT "Notified_Person" OFF;
558 --Creating a new staff entry
559 INSERT INTO "Staff" (ID, hire_date, working_hours, payment_rate, notified_id)
560 VALUES (@user_id, @hire_date, @working_hours, @payment_rate, @temp + 1);
561 --Creating the reviewer entry
562 INSERT INTO "Reviewer" (ID) VALUES (@user_id);
563 END
564 ELSE IF @usertype = 'Content Manager'
565 BEGIN
566 --DECLARE @temp INTEGER;
567 --Creating new notified person entry
568 SELECT @temp = max(ID) FROM "Notified_Person";
569 SET IDENTITY_INSERT "Notified_Person" ON;
570 INSERT INTO "Notified_Person" (ID) VALUES (@temp + 1);
571 SET IDENTITY_INSERT "Notified_Person" OFF;
572 --Creating a new staff entry
573 INSERT INTO "Staff" (ID, hire_date, working_hours, payment_rate, notified_id)
574 VALUES (@user_id, @hire_date, @working_hours, @payment_rate, @temp + 1);
575 --Creating the Content Manager entry
576 INSERT INTO "Content_Manager" (ID) VALUES (@user_id);
577 END
578 END
579 END;
580GO ;
581
582CREATE PROC Check_Type @typename VARCHAR(255), @user_id INTEGER
583AS
584 BEGIN
585 IF EXISTS(SELECT type FROM "Content_type" WHERE type = @typename)
586 BEGIN
587 UPDATE "Content_Manager" SET type = @typename WHERE ID = @user_id;
588 END
589 END;
590GO ;
591
592CREATE PROC Order_Contributor
593AS
594 BEGIN
595 SELECT years_of_experience
596 FROM "Contributor"
597 WHERE years_of_experience IS NOT NULL
598 ORDER BY years_of_experience DESC;
599 END
600GO ;
601
602CREATE PROC Show_Original_Content @contributor_id INTEGER = NULL
603AS
604 BEGIN
605 IF @contributor_id IS NULL
606 BEGIN
607 SELECT cr.ID,
608 u.first_name,
609 u.middle_name,
610 u.last_name,
611 u.email,
612 u.birth_date,
613 u.age,
614 cr.years_of_experience,
615 cr.portfolio_link,
616 cr.specialization,
617 ct.ID,
618 ct.category_type,
619 ct.subcategory_name,
620 ct.uploaded_at
621 FROM "User" u -- "Original_Content" oc, "Content" ct
622 JOIN "Contributor" cr ON u.ID = cr.ID
623 JOIN "Content" ct ON ct.contributor_id = cr.ID
624 JOIN "Original_Content" oc ON oc.ID = ct.ID
625 END
626 ELSE
627 BEGIN
628 SELECT cr.ID,
629 u.first_name,
630 u.middle_name,
631 u.last_name,
632 u.email,
633 u.birth_date,
634 u.age,
635 cr.years_of_experience,
636 cr.portfolio_link,
637 cr.specialization,
638 ct.ID,
639 ct.category_type,
640 ct.subcategory_name,
641 ct.uploaded_at
642 FROM "User" u -- "Original_Content" oc, "Content" ct
643 JOIN "Contributor" cr ON u.ID = cr.ID
644 JOIN "Content" ct ON ct.contributor_id = cr.ID
645 JOIN "Original_Content" oc ON oc.ID = ct.ID
646 WHERE @contributor_id = cr.ID;
647 END
648 END
649GO ;
650
651CREATE PROC Can_reactivate @user_id INTEGER, @active BIT OUT
652AS
653 BEGIN
654 DECLARE @elapsed INTEGER;
655 SELECT @elapsed = datediff(WEEK, CURRENT_TIMESTAMP, last_login) FROM "User" WHERE ID = @user_id;
656 IF @elapsed > 2
657 SET @active = 0;
658 ELSE
659 SET @active = 1;
660 END
661GO ;
662
663CREATE PROC User_login @email VARCHAR(255), @password VARCHAR(255), @user_id INTEGER OUT
664AS
665 BEGIN
666 --Check if the user exists
667 SET @user_id = -1;
668 IF EXISTS(SELECT * FROM "User" WHERE email = @email
669 AND password = @password)
670 BEGIN
671 --Get ID
672 SELECT @user_id = ID FROM "User" WHERE email = @email
673 AND password = @password;
674 --Check if the account is deactivated
675 IF EXISTS(SELECT * FROM "User" WHERE ID = @user_id
676 AND active = 0)
677 BEGIN
678 DECLARE @can_activate BIT;
679 EXEC Can_reactivate @user_id, @can_activate OUT;
680 IF @can_activate = 0
681 SET @user_id = -1;
682 END
683 END
684 END
685GO ;
686
687CREATE PROC Show_Profile @user_id INTEGER, @email VARCHAR(255) OUTPUT,
688 @password VARCHAR(255) OUTPUT,
689 @firstname VARCHAR(255) OUTPUT, @middlename VARCHAR(255) OUTPUT,
690 @lastname VARCHAR(255) OUTPUT, @birth_date DATETIME OUTPUT,
691 @working_place_name VARCHAR(255) = NULL OUTPUT,
692 @working_place_type VARCHAR(255) = NULL OUTPUT,
693 @wokring_place_description TEXT = NULL OUTPUT,
694 @specilization VARCHAR(255) = NULL OUTPUT,
695 @portofolio_link VARCHAR(255) = NULL OUTPUT,
696 @years_experience INTEGER = NULL OUTPUT,
697 @hire_date DATETIME = NULL OUTPUT,
698 @working_hours INTEGER = NULL OUTPUT,
699 @payment_rate REAL = NULL OUTPUT
700AS
701 BEGIN
702 --Check if the account is deleted
703 SET @user_id = NULL;
704 SET @email = NULL;
705 SET @password = NULL;
706 SET @firstname = NULL;
707 SET @middlename = NULL;
708 SET @lastname = NULL;
709 SET @birth_date = NULL;
710 SET @working_place_name = NULL; --Viewer
711 SET @working_place_type = NULL; --Viewer
712 SET @wokring_place_description = NULL; --Viewer
713 SET @specilization = NULL; --Cont
714 SET @portofolio_link = NULL; --Cont
715 SET @years_experience = NULL; --Cont
716 SET @hire_date = NULL; --staff
717 SET @working_hours = NULL; --staff
718 SET @payment_rate = NULL; --staff
719 IF EXISTS(SELECT ID FROM "User" WHERE ID = @user_id) --This query might be different
720 --Check if the account is active
721 BEGIN
722 DECLARE @active BIT = 0;
723 EXEC Check_Active @user_id, @active OUT;
724 IF @active = 1
725 BEGIN
726 -- Values from users table
727 SELECT @email = email,
728 @password = password,
729 @firstname = first_name,
730 @middlename = middle_name,
731 @lastname = last_name,
732 @birth_date = birth_date
733 FROM "User"
734 WHERE ID = @user_id;
735 -- Values from other tables
736 IF EXISTS(SELECT ID FROM "Viewer" WHERE @user_id = ID) --User is a viewer
737 SELECT @working_place_name = working_place,
738 @working_place_type = working_place_type,
739 @wokring_place_description = working_place_description
740 FROM "Viewer"
741 WHERE ID = @user_id;
742 ELSE IF EXISTS(SELECT ID FROM "Contributor" WHERE @user_id = ID) --User is a Contributor
743 SELECT @specilization = specialization,
744 @portofolio_link = portfolio_link,
745 @years_experience = years_of_experience
746 FROM "Contributor"
747 WHERE ID = @user_id;
748 ELSE IF EXISTS(SELECT ID FROM "Staff" WHERE @user_id = ID) --User is a Staff member
749 SELECT @hire_date = hire_date, @working_hours = working_hours, @payment_rate = payment_rate
750 FROM "Staff"
751 WHERE @user_id = ID;
752 END
753 END
754 END
755GO ;
756
757CREATE PROC Deactivate_Profile @user_id INTEGER
758AS
759 BEGIN
760 UPDATE "User" SET active = 0 WHERE ID = @user_id;
761 END
762GO ;
763
764CREATE PROC Edit_Profile @user_id VARCHAR(255), @email VARCHAR(255) = NULL,
765 @password VARCHAR(255) = NULL, @firstname VARCHAR(255) = NULL,
766 @middlename VARCHAR(255) = NULL, @lastname VARCHAR(255) = NULL,
767 @birth_date DATETIME = NULL,
768 @working_place_name VARCHAR(255) = NULL, @working_place_type VARCHAR(255) = NULL,
769 @working_place_description TEXT = NULL, @specilization VARCHAR(255) = NULL,
770 @portfolio_link VARCHAR(255) = NULL, @years_experience INTEGER = NULL,
771 @hire_date DATETIME = NULL, @working_hours INTEGER = NULL,
772 @payment_rate REAL = NULL
773AS
774 BEGIN
775 IF EXISTS(SELECT ID FROM "User" WHERE ID = @user_id) --This query might be different
776 BEGIN
777 -- Values from users table
778 IF @password IS NOT NULL --Edit password
779 UPDATE "User" SET password = @password WHERE ID = @user_id;
780 IF @email IS NOT NULL --Edit email
781 UPDATE "User" SET email = @email WHERE ID = @user_id;
782 IF @firstname IS NOT NULL --Edit firstname
783 UPDATE "User" SET first_name = @firstname WHERE ID = @user_id;
784 IF @middlename IS NOT NULL --Edit middlename
785 UPDATE "User" SET middle_name = @middlename WHERE ID = @user_id;
786 IF @lastname IS NOT NULL --Edit lastname
787 UPDATE "User" SET last_name = @lastname WHERE ID = @user_id;
788 IF @birth_date IS NOT NULL --Edit birthdate
789 UPDATE "User" SET birth_date = @birth_date WHERE ID = @user_id;
790 -- Values from other tables
791 IF EXISTS(SELECT ID FROM "Viewer" WHERE @user_id = ID) --User is a viewer
792 BEGIN
793 IF @working_place_name IS NOT NULL --Edit working place name
794 UPDATE "Viewer" SET working_place = @working_place_name WHERE ID = @user_id;
795 IF @working_place_type IS NOT NULL --Edit working place type
796 UPDATE "Viewer" SET working_place_type = @working_place_type WHERE ID = @user_id;
797 IF @working_place_description IS NOT NULL --Edit working place description
798 UPDATE "Viewer" SET working_place_description = @working_place_description WHERE ID = @user_id;
799 END
800 ELSE IF EXISTS(SELECT ID FROM "Contributor" WHERE @user_id = ID) --User is a Contributor
801 BEGIN
802 IF @years_experience IS NOT NULL --Edit years of experience
803 UPDATE "Contributor" SET years_of_experience = @years_experience WHERE ID = @user_id;
804 IF @portfolio_link IS NOT NULL --Edit portfolio link
805 UPDATE "Contributor" SET portfolio_link = @portfolio_link WHERE ID = @user_id;
806 IF @specilization IS NOT NULL --Edit specialization
807 UPDATE "Contributor" SET specialization = @specilization WHERE ID = @user_id;
808 END
809 ELSE IF EXISTS(SELECT ID FROM "Staff" WHERE @user_id = ID) --User is a Staff member
810 BEGIN
811 IF @hire_date IS NOT NULL --Edit hire date
812 UPDATE "Staff" SET hire_date = @hire_date WHERE ID = @user_id;
813 IF @working_hours IS NOT NULL --Edit working hours
814 UPDATE "Staff" SET working_hours = @working_hours WHERE ID = @user_id;
815 IF @payment_rate IS NOT NULL --Edit payment rate
816 UPDATE "Staff" SET payment_rate = @payment_rate WHERE ID = @user_id;
817 END
818 END
819 END
820GO ;
821
822CREATE PROC Show_Event @event_id INTEGER = NULL
823AS
824 BEGIN
825 IF @event_id IS NOT NULL
826 BEGIN
827 SELECT u.first_name,
828 u.middle_name,
829 u.last_name,
830 e.ID,
831 e.description,
832 e.location,
833 e.city,
834 e.time,
835 e.entertainer,
836 e.notification_object_id
837
838 FROM "Event" e
839 INNER JOIN "User" u ON e.viewer_id = u.ID
840 WHERE e.ID = @event_id
841 AND e.time > CURRENT_TIMESTAMP;
842 END
843 ELSE
844 BEGIN
845 SELECT u.first_name,
846 u.middle_name,
847 u.last_name,
848 e.ID,
849 e.description,
850 e.location,
851 e.city,
852 e.time,
853 e.entertainer,
854 e.notification_object_id
855 FROM "EVENT" e
856 INNER JOIN "User" u ON e.viewer_id = u.ID;
857 END
858 END
859GO ;
860
861CREATE PROC Show_Notification @user_id INTEGER
862AS
863 BEGIN
864 DECLARE @notified_person_id INTEGER = NULL;
865 IF EXISTS(SELECT ID FROM "Contributor" WHERE ID = @user_id) --User is a contributor
866 BEGIN
867 --Getting the notified person id
868 SELECT @notified_person_id = notified_id from "Contributor" WHERE ID = @user_id;
869 --Getting new requests
870 SELECT r.ID, r.accept_status, r.specified, r.information
871 FROM (SELECT notification_object_id FROM "Announcement" WHERE notified_person_id = @notified_person_id) a
872 INNER JOIN "New_Request" r ON a.notification_object_id = r.notif_obj_id;
873 --Getting events
874 SELECT u.first_name,
875 u.middle_name,
876 u.last_name,
877 e.ID,
878 e.description,
879 e.location,
880 e.city,
881 e.time,
882 e.entertainer,
883 e.notification_object_id
884 FROM (SELECT notification_object_id FROM "Announcement" WHERE notified_person_id = @notified_person_id) a
885 INNER JOIN "Event" e ON a.notification_object_id = e.notification_object_id
886 INNER JOIN "User" u ON e.viewer_id = u.ID;
887 END
888 ELSE IF EXISTS(SELECT ID FROM "Staff" WHERE ID = @user_id) --User is a staff member
889 BEGIN
890 --Getting the notified person id
891 SELECT @notified_person_id = notified_id from "Staff" WHERE ID = @user_id;
892 --Getting events
893 SELECT u.first_name,
894 u.middle_name,
895 u.last_name,
896 e.ID,
897 e.description,
898 e.location,
899 e.city,
900 e.time,
901 e.entertainer,
902 e.notification_object_id
903 FROM (SELECT notification_object_id FROM "Announcement" WHERE notified_person_id = @notified_person_id) a
904 INNER JOIN "Event" e ON a.notification_object_id = e.notification_object_id
905 INNER JOIN "User" u ON e.viewer_id = u.ID;
906 END
907 END
908GO ;
909
910CREATE PROC Show_New_Content @viewer_id INTEGER, @content_id INTEGER = NULL
911AS
912 BEGIN
913 IF @content_id = NULL
914 BEGIN
915 SELECT u.ID,
916 u.first_name,
917 u.middle_name,
918 u.last_name,
919 ct.ID,
920 ct.uploaded_at,
921 ct.contributor_id,
922 ct.category_type,
923 ct.subcategory_name,
924 ct.type
925 FROM (SELECT ID, first_name, middle_name, last_name FROM "User" WHERE ID = @viewer_id) u
926 JOIN (SELECT ID, viewer_id FROM "New_Request") r ON r.viewer_id = u.ID
927 JOIN "New_Content" nc ON nc.new_request_id = r.ID
928 JOIN "Content" ct ON nc.ID = ct.ID
929 END
930 ELSE
931 BEGIN
932 SELECT u.ID,
933 u.first_name,
934 u.middle_name,
935 u.last_name,
936 ct.ID,
937 ct.uploaded_at,
938 ct.contributor_id,
939 ct.category_type,
940 ct.subcategory_name,
941 ct.type
942 FROM (SELECT ID, first_name, middle_name, last_name FROM "User" WHERE ID = @viewer_id) u
943 JOIN (SELECT ID, viewer_id FROM "New_Request") r ON r.viewer_id = u.ID
944 JOIN (SELECT * FROM "New_Content" WHERE ID = @content_id) nc ON nc.new_request_id = r.ID
945 JOIN "Content" ct ON nc.ID = ct.ID
946 END
947 END
948GO ;
949
950CREATE PROC Receive_New_Requests @request_id INTEGER, @contributor_id INTEGER
951AS
952 BEGIN
953 DECLARE @notified_person_id INTEGER = NULL;
954 SELECT @notified_person_id = notified_id FROM "Contributor" WHERE ID = @contributor_id
955 IF @request_id IS NULL
956 BEGIN
957 SELECT R.*
958 FROM "New_Request" R
959 JOIN "Notification_Object" O ON R.notif_obj_id = O.ID
960 JOIN "Announcement" A ON O.ID = A.notification_object_id
961 WHERE A.notified_person_id = @notified_person_id;
962 END
963 ELSE
964 BEGIN
965 SELECT R.*
966 FROM "New_Request" R
967 JOIN "Notification_Object" O ON R.notif_obj_id = O.ID
968 JOIN "Announcement" A ON O.ID = A.notification_object_id
969 WHERE A.notified_person_id = @notified_person_id
970 AND R.ID = @request_id;
971 END
972 END
973GO ;
974
975CREATE PROC Receive_New_Request @contributor_id INTEGER, @can_receive INTEGER OUT
976AS
977 BEGIN
978 SELECT @can_receive = COUNT(*)
979 FROM "New_Request" r
980 WHERE r.contributor_id = @contributor_id
981 AND r.accept_status = 1
982 AND NOT EXISTS(
983 SELECT * FROM "Content" c
984 JOIN "New_Content" nc on c.ID = nc.ID AND nc.new_request_id = r.ID
985 );
986 IF @can_receive < 3
987 SET @can_receive = 1;
988 ELSE
989 SET @can_receive = 0;
990 END
991GO ;
992
993CREATE PROC Respond_New_Request @contributor_id INTEGER, @accept_status BIT, @request_id INTEGER
994AS
995 BEGIN
996 --Check if the request isn't accepted
997 IF EXISTS(SELECT * FROM "New_Request" WHERE ID = @request_id
998 AND accept_status = 0)
999 BEGIN
1000 --Check if the contributor is eligible for the request
1001 IF EXISTS(SELECT *
1002 FROM "New_Request"
1003 WHERE ID = @request_id
1004 AND ((specified = 1 AND contributor_id = @contributor_id)
1005 OR (specified = 0 AND accept_status IS NULL)))
1006 BEGIN
1007 --Check if the contributor has less than three current new requests
1008 DECLARE @can_receive INTEGER;
1009 EXEC Receive_New_Request @contributor_id, @can_receive OUT;
1010 IF @can_receive = 1 --Can take request
1011 BEGIN
1012 --If accepted
1013 IF @accept_status = 1
1014 BEGIN
1015 UPDATE "New_Request"
1016 SET accept_status = @accept_status,
1017 contributor_id = @contributor_id,
1018 accept_time = CURRENT_TIMESTAMP
1019 WHERE ID = @request_id;
1020 END
1021 ELSE
1022 --If the request was specified decline it
1023 BEGIN
1024 IF EXISTS(SELECT * FROM "New_Request" WHERE ID = @request_id
1025 AND specified = 1)
1026 BEGIN
1027 UPDATE "New_Request" SET accept_status = @accept_status WHERE ID = @request_id;
1028 END
1029 END
1030 END
1031 END
1032 END
1033 END
1034GO ;
1035
1036CREATE PROC Upload_Original_Content @type_id VARCHAR(255), @subcategory_name VARCHAR(255),
1037 @category_id VARCHAR(255),
1038 @contributor_id INTEGER, @link VARCHAR(255)
1039AS
1040 BEGIN
1041 -- Create new entry for content
1042 INSERT INTO "Content" (uploaded_at, contributor_id, category_type, subcategory_name, type, link)
1043 VALUES (CURRENT_TIMESTAMP, @contributor_id, @category_id, @subcategory_name, @type_id, @link);
1044 -- Get the id of the new content
1045 DECLARE @content_id INTEGER = (SELECT max(ID) FROM "Content")
1046 -- Create new entry for original content
1047 INSERT INTO "Original_Content" (ID) VALUES (@content_id);
1048 END
1049GO ;
1050
1051CREATE PROC Update_average_update_time @contributor_id INTEGER
1052AS
1053 BEGIN
1054 DECLARE @AVERAGE INTEGER;
1055 SELECT @AVERAGE = AVG(DATEDIFF(hh, "Content".uploaded_at, "New_Request".accept_time))
1056 FROM "New_Request"
1057 JOIN "New_Content" ON New_Request.ID = "New_Content".new_request_id
1058 JOIN "Content" ON "New_Content".ID = "Content".ID
1059 WHERE "New_Request".contributor_id = @contributor_id
1060 AND "Content".contributor_id = @contributor_id;
1061 UPDATE "Contributor" SET average_upload_time = @AVERAGE WHERE ID = @contributor_id;
1062 END
1063GO ;
1064
1065CREATE PROC Upload_New_Content @new_request_id INTEGER, @contributor_id INTEGER,
1066 @subcategory_name VARCHAR(255), @category_id VARCHAR(255), @link VARCHAR(255)
1067AS
1068 BEGIN
1069 -- Create entry in content
1070 INSERT INTO "Content" (uploaded_at, contributor_id, category_type, subcategory_name, link)
1071 VALUES (CURRENT_TIMESTAMP, @contributor_id, @category_id, @subcategory_name, @link);
1072 -- Get the id of the new content
1073 DECLARE @content_id INTEGER = (SELECT max(ID) FROM "Content")
1074 -- Create entry in new content
1075 INSERT INTO "New_Content" (ID, new_request_id) VALUES (@content_id, @new_request_id);
1076 -- Update the average time taken for the contributor to upload new content
1077 EXEC Update_average_update_time @contributor_id
1078 END
1079GO ;
1080
1081CREATE PROC reviewer_filter_content @reviewer_id INTEGER, @original_content INTEGER, @status BIT
1082AS
1083 BEGIN
1084 UPDATE "Original_Content"
1085 SET reviewer_id = @reviewer_id,
1086 review_status = @status
1087 WHERE ID = @original_content;
1088 END
1089GO ;
1090
1091CREATE PROC content_manager_filter_content @content_manager_id INTEGER, @original_content INTEGER, @status BIT
1092AS
1093 BEGIN
1094 UPDATE "Original_Content"
1095 SET content_manager_id = @content_manager_id,
1096 filter_status = @status
1097 WHERE ID = @original_content;
1098 END
1099GO ;
1100
1101CREATE PROC Staff_Create_Category @category_name VARCHAR(255)
1102AS
1103 BEGIN
1104 INSERT INTO "Category" (type) VALUES (@category_name);
1105 END
1106GO ;
1107
1108CREATE PROC Staff_Create_Subcategory @category_name VARCHAR(255), @subcategory_name VARCHAR(255)
1109AS
1110 BEGIN
1111 INSERT INTO "Sub_Category" (category_type, name) VALUES (@category_name, @subcategory_name);
1112 END
1113GO ;
1114
1115CREATE PROC Staff_Create_Type @type_name VARCHAR(255)
1116AS
1117 BEGIN
1118 INSERT INTO "Content_type" (type) VALUES (@type_name);
1119 END
1120GO ;
1121
1122CREATE PROC Most_Requested_Content
1123AS
1124 BEGIN
1125 SELECT c.ID, COUNT(r.ID) AS "Requests"
1126 FROM "Original_Content" c
1127 Join "Existing_Request" r ON r.original_content_id = c.ID
1128 GROUP BY c.ID
1129 END
1130GO ;
1131
1132CREATE PROC Viewer_Create_Event @city VARCHAR(255), @event_date_time DATETIME, @description TEXT,
1133 @entartainer VARCHAR(255), @viewer_id INTEGER, @location VARCHAR(255),
1134 @event_id INTEGER OUT
1135AS
1136 BEGIN
1137 -- Create a new notification object entry
1138 DECLARE @obj_id INTEGER;
1139 SELECT @obj_id = MAX(ID) + 1 FROM "Notification_Object";
1140 SET IDENTITY_INSERT "Notification_Object" ON;
1141 INSERT INTO "Notification_Object" (ID) VALUES (@obj_id);
1142 SET IDENTITY_INSERT "Notification_Object" OFF;
1143 -- Create new event
1144 INSERT INTO "Event" (description, location, city, time, entertainer, notification_object_id, viewer_id)
1145 VALUES (@description, @location, @city, @event_date_time, @entartainer, @obj_id, @viewer_id);
1146 -- Getting the event ID
1147 SELECT @event_id = MAX(ID) FROM "Event";
1148 --Create announcements
1149 DECLARE notified_cursor CURSOR FOR (SELECT ID FROM "Notified_Person");
1150 OPEN notified_cursor;
1151 DECLARE @notified_person_id INTEGER = NULL;
1152 FETCH NEXT FROM notified_cursor
1153 INTO @notified_person_id;
1154 IF @notified_person_id IS NOT NULL
1155 INSERT INTO "Announcement" (sent_at, notified_person_id, notification_object_id)
1156 VALUES (CURRENT_TIMESTAMP, @notified_person_id, @obj_id);
1157 WHILE @@FETCH_STATUS = 0
1158 BEGIN
1159 FETCH NEXT FROM notified_cursor
1160 INTO @notified_person_id;
1161 IF @notified_person_id IS NOT NULL
1162 INSERT INTO "Announcement" (sent_at, notified_person_id, notification_object_id)
1163 VALUES (CURRENT_TIMESTAMP, @notified_person_id, @obj_id);
1164 END
1165 CLOSE notified_cursor;
1166 DEALLOCATE notified_cursor;
1167 END
1168GO ;
1169
1170CREATE PROC Apply_Existing_Request @viewer_id INTEGER, @original_content_id INTEGER
1171AS
1172 BEGIN
1173 IF EXISTS(SELECT * FROM "Original_Content" WHERE ID = @original_content_id
1174 AND rating >= 4)
1175 BEGIN
1176 -- Creating existing request
1177 INSERT INTO "Existing_Request" (original_content_id, viewer_id) VALUES (@original_content_id, @viewer_id);
1178 END
1179 END
1180GO ;
1181
1182CREATE PROC Viewer_Create_Ad_From_Event @event_id INTEGER
1183AS
1184 BEGIN
1185 DECLARE @viewer_id INTEGER;
1186 SELECT @viewer_id = viewer_id FROM "Event" WHERE ID = @event_id;
1187 INSERT INTO "Advertisement" (event_id, viewer_id) VALUES (@event_id, @viewer_id);
1188 END
1189GO ;
1190
1191CREATE PROC Apply_New_Request @information TEXT, @contributor_id INTEGER, @viewer_id INTEGER
1192AS
1193 BEGIN
1194 DECLARE @obj_id INTEGER;
1195 IF @contributor_id IS NULL
1196 BEGIN
1197 --Create new notification object
1198 SELECT @obj_id = MAX(ID) + 1 FROM "Notification_Object";
1199 SET IDENTITY_INSERT "Notification_Object" ON;
1200 INSERT INTO "Notification_Object" (ID) VALUES (@obj_id);
1201 SET IDENTITY_INSERT "Notification_Object" OFF;
1202 INSERT INTO "New_Request" (specified, information, viewer_id, notif_obj_id)
1203 VALUES (0, @information, @viewer_id, @obj_id);
1204 -- Creating announcements
1205 DECLARE notified_cursor CURSOR FOR (SELECT P.ID FROM "Notified_person" P
1206 JOIN "Contributor" C2 on P.ID = C2.notified_id);
1207 OPEN notified_cursor;
1208 DECLARE @notified_person_id INTEGER = NULL;
1209 FETCH NEXT FROM notified_cursor
1210 INTO @notified_person_id;
1211 IF @notified_person_id IS NOT NULL
1212 INSERT INTO "Announcement" (sent_at, notified_person_id, notification_object_id)
1213 VALUES (CURRENT_TIMESTAMP, @notified_person_id, @obj_id);
1214 WHILE @@FETCH_STATUS = 0
1215 BEGIN
1216 FETCH NEXT FROM notified_cursor
1217 INTO @notified_person_id;
1218 IF @notified_person_id IS NOT NULL
1219 INSERT INTO "Announcement" (sent_at, notified_person_id, notification_object_id)
1220 VALUES (CURRENT_TIMESTAMP, @notified_person_id, @obj_id);
1221 END
1222 CLOSE notified_cursor;
1223 DEALLOCATE notified_cursor;
1224 END
1225 ELSE
1226 --Check if the user is eligible for the request
1227 IF NOT EXISTS(SELECT *
1228 FROM "New_Request"
1229 WHERE viewer_id = @viewer_id
1230 AND specified = 1
1231 AND contributor_id = @contributor_id
1232 AND accept_status = 0)
1233 BEGIN
1234 --Create new notification object
1235 SELECT @obj_id = MAX(ID) + 1 FROM "Notification_Object";
1236 SET IDENTITY_INSERT "Notification_Object" ON;
1237 INSERT INTO "Notification_Object" (ID) VALUES (@obj_id);
1238 SET IDENTITY_INSERT "Notification_Object" OFF;
1239 INSERT INTO "New_Request" (specified, information, viewer_id, notif_obj_id, contributor_id)
1240 VALUES (1, @information, @viewer_id, @obj_id, @contributor_id);
1241 --Create notification
1242 INSERT INTO "Announcement" (notification_object_id, notified_person_id) VALUES (@obj_id, @contributor_id);
1243 END
1244 END
1245GO ;
1246
1247CREATE PROC "Delete_New_Request" @request_id INTEGER
1248AS
1249 BEGIN
1250 IF EXISTS(SELECT * FROM "New_Request" nr WHERE nr.ID = @request_id
1251 AND accept_status IS NULL)
1252 BEGIN
1253 DELETE FROM "Notification_Object" WHERE "Notification_Object".ID = (SELECT notif_obj_id FROM "New_Request" WHERE ID = @request_id);
1254 DELETE FROM "New_Request" WHERE ID = @request_id;
1255 END
1256 END
1257GO ;
1258
1259CREATE PROC Rating_Original_Content @original_content_id INTEGER, @rating_value INTEGER, @viewer_id INTEGER
1260AS
1261 BEGIN
1262 INSERT INTO "Rate" (viewer_id, original_content_id, date, rate)
1263 VALUES (@viewer_id, @original_content_id, CURRENT_TIMESTAMP, @rating_value)
1264 --update the rating on the content
1265 UPDATE "Original_Content"
1266 SET rating = (SELECT AVG(rate) FROM "Rate" WHERE original_content_id = @original_content_id)
1267 WHERE ID = @original_content_id;
1268 END
1269GO ;
1270
1271CREATE PROC Write_Comment @comment_text TEXT, @viewer_id INTEGER, @original_content_id INTEGER, @written_time DATETIME
1272AS
1273 BEGIN
1274 INSERT INTO "Comment" (viewer_id, original_content_id, date, text)
1275 VALUES (@viewer_id, @original_content_id, @written_time, @comment_text);
1276 END
1277GO ;
1278
1279CREATE PROC Delete_Comment @viewer_id INTEGER, @original_content_id INTEGER, @written_time DATETIME
1280AS
1281 BEGIN
1282 DELETE FROM "Comment"
1283 WHERE viewer_id = @viewer_id
1284 AND original_content_id = @original_content_id
1285 AND date = @written_time;
1286 END
1287GO ;
1288
1289CREATE PROC Delete_Original_Content @content_id INTEGER
1290AS
1291 BEGIN
1292 IF EXISTS(SELECT * FROM "Original_Content" WHERE ID=@content_id AND reviewer_id IS NULL AND content_manager_id IS NULL)
1293 DELETE FROM "Content" WHERE ID = @content_id;
1294 END
1295GO ;
1296
1297CREATE PROC Assign_Contributor_Request @contributor_id INTEGER, @new_request_id INTEGER
1298AS
1299 BEGIN
1300 UPDATE "New_Request" SET contributor_id = @contributor_id WHERE ID = @new_request_id;
1301 END
1302GO ;
1303
1304CREATE PROC Delete_New_Content @content_id INTEGER
1305AS
1306 BEGIN
1307 DELETE FROM "New_Request" WHERE ID = (SELECT new_request_id FROM "New_Content" WHERE ID = @content_id);
1308 DELETE FROM "Content" WHERE ID = @content_id;
1309 END
1310GO ;
1311
1312CREATE PROC Delete_Content @content_id INTEGER
1313AS
1314 BEGIN
1315 DELETE FROM "Content" WHERE ID = @content_id;
1316 END
1317GO ;
1318
1319CREATE PROC Edit_Comment @comment_text TEXT, @viewer_id INTEGER, @original_content_id INTEGER,
1320 @last_written_time DATETIME, @updated_written_time DATETIME
1321AS
1322 BEGIN
1323 UPDATE "Comment"
1324 SET text = @comment_text,
1325 date = @updated_written_time
1326 WHERE viewer_id = @viewer_id
1327 AND original_content_id = @original_content_id
1328 AND date = @last_written_time;
1329 END
1330GO ;
1331
1332CREATE PROC Edit_Ad @ad_id INTEGER, @description TEXT, @location VARCHAR(255)
1333AS
1334 BEGIN
1335 IF @description IS NOT NULL
1336 UPDATE "Advertisement" SET description = @description WHERE ID = @ad_id;
1337 IF @location IS NOT NULL
1338 UPDATE "Advertisement" SET location = @location WHERE ID = @ad_id;
1339
1340 END
1341GO ;
1342
1343CREATE PROC Create_Ads @viewer_id INTEGER, @description TEXT, @location VARCHAR(255)
1344AS
1345 BEGIN
1346 INSERT INTO "Advertisement" (description, location, viewer_id) VALUES (@description, @location, @viewer_id);
1347 END
1348GO ;
1349
1350CREATE PROC Delete_Ads @ad_id INTEGER
1351AS
1352 BEGIN
1353 DELETE FROM "Advertisement" WHERE ID = @ad_id;
1354 END
1355GO ;
1356
1357CREATE PROC Send_Message @msg_text TEXT, @viewer_id INTEGER, @contributor_id INTEGER, @sender_type BIT,
1358 @sent_at DATETIME
1359AS
1360 BEGIN
1361 INSERT INTO "Message" (sent_at, contributor_id, viewer_id, sender_type, text)
1362 VALUES (@sent_at, @contributor_id, @viewer_id, @sender_type, @msg_text);
1363 END
1364GO ;
1365
1366CREATE PROC Show_Message @contributor_id INTEGER
1367AS
1368 BEGIN
1369 SELECT * FROM "Message" WHERE contributor_id = @contributor_id;
1370 END
1371GO ;
1372
1373CREATE PROC Highest_Rating_Original_content
1374AS
1375 BEGIN
1376 SELECT * FROM "Content"c
1377 JOIN "Original_Content" oc ON c.ID = oc.ID ORDER BY oc.rating DESC;
1378 END
1379GO ;
1380
1381CREATE PROC Assign_New_Request @request_id INTEGER, @contributor_id INTEGER
1382AS
1383 BEGIN
1384 IF EXISTS(SELECT * FROM "New_Request" WHERE ID = @request_id
1385 AND NOT accept_status = 0)
1386 UPDATE "New_Request"
1387 SET specified = 1,
1388 contributor_id = @contributor_id
1389 WHERE ID = @request_id;
1390 END
1391GO ;
1392
1393CREATE PROC Working_Category_Relation
1394AS
1395 BEGIN
1396 SELECT working_place_type,-- AS "Working Place Type",
1397 category_type,-- AS "Category Name",
1398 COUNT(*) AS requests-- AS "Number Of Requests"
1399 FROM (SELECT *
1400 FROM (SELECT V1.ID AS VID,
1401 V1.working_place_type AS working_place_type,
1402 C.category_type AS category_type,
1403 C.ID AS CID
1404 FROM ("Viewer" V1
1405 JOIN "Existing_Request" R ON V1.ID = R.viewer_id
1406 JOIN "Content" C ON R.original_content_id = C.ID)) T1
1407 UNION
1408 (SELECT V2.ID AS VID,
1409 V2.working_place_type AS working_place_type,
1410 C3.category_type AS category_type,
1411 C3.ID AS CID
1412 FROM ("Viewer" V2
1413 JOIN "New_Request" N2 ON V2.ID = N2.viewer_id
1414 JOIN "New_Content" C2 ON N2.ID = C2.new_request_id
1415 JOIN "Content" C3 ON C2.ID = C3.ID))) T2
1416 GROUP BY working_place_type, category_type
1417 END
1418GO;
1419
1420 CREATE PROC Show_Possible_Contributors
1421AS
1422 BEGIN
1423 SELECT C.ID, COUNT(*) AS "number of new requests"
1424 FROM "Contributor" C
1425 JOIN "New_Request" R ON C.ID = R.contributor_id
1426 JOIN "New_Content" C2 ON C2.new_request_id = R.ID
1427 GROUP BY C.ID, C.average_upload_time
1428 HAVING 3 > (
1429 SELECT COUNT(*)
1430 FROM "New_Request" r
1431 WHERE r.contributor_id = C.id
1432 AND r.accept_status = 1
1433 AND NOT EXISTS(
1434 SELECT * FROM "Content" c
1435 JOIN "New_Content" nc on c.ID = nc.ID AND nc.new_request_id = r.ID
1436 )
1437 )
1438 ORDER BY C.average_upload_time, "number of new requests"
1439 END
1440GO ;
1441-----------------------------------------------------------------------------------------------------------------------
1442USE TEMP