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