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