· 5 years ago · Jul 16, 2020, 02:06 PM
1-- #1. Create Schema
2DROP SCHEMA IF EXISTS `platform`;
3CREATE SCHEMA `platform`;
4USE `platform`;
5
6-- #2. Create Tables
7-- #2.1 Table: Users
8CREATE TABLE `users` (
9 `id` INT NOT NULL AUTO_INCREMENT,
10 `username` VARCHAR(16) NOT NULL,
11 `age` int,
12 `email` VARCHAR(255) NOT NULL UNIQUE,
13 `password` VARCHAR(32) NOT NULL,
14 `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
15 `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
16
17 PRIMARY KEY (`id`),
18 CONSTRAINT CHK_Person CHECK (`age` >= 18));
19
20-- #2.2 Table: Roles
21CREATE TABLE `roles`(
22 `id` INT NOT NULL AUTO_INCREMENT,
23 `role` VARCHAR(16) NOT NULL,
24
25 PRIMARY KEY (`id`));
26
27-- #2.3 Table: User-Roles Many to Many Relationship
28CREATE TABLE `user_roles` (
29 `id` INT NOT NULL AUTO_INCREMENT,
30 `user_id` INT NULL,
31 `role_id` INT NULL,
32 `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
33
34 PRIMARY KEY (`id`),
35 CONSTRAINT `user`
36 FOREIGN KEY (`user_id`) REFERENCES `users` (`id`),
37 CONSTRAINT `user_role`
38 FOREIGN KEY (`role_id`) REFERENCES `roles` (`id`));
39
40-- #2.4 Table: Videos
41CREATE TABLE `videos` (
42 `id` INT NOT NULL AUTO_INCREMENT,
43 `user_id` INT NOT NULL,
44 `title` VARCHAR(45) NOT NULL,
45 `description` VARCHAR(1023) NULL,
46 `video_url` VARCHAR(255) NOT NULL,
47 `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
48 `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
49
50 PRIMARY KEY (`id`),
51 CONSTRAINT `video_user`
52 FOREIGN KEY (`user_id`) REFERENCES `users` (`id`));
53
54-- #2.5 Table: Comments (for videos)
55CREATE TABLE `comments` (
56 `id` INT NOT NULL AUTO_INCREMENT,
57 `user_id` INT NOT NULL,
58 `video_id` INT NOT NULL,
59 `content` VARCHAR(255) NOT NULL,
60 `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
61 `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
62
63 PRIMARY KEY (`id`),
64 CONSTRAINT `comment_user`
65 FOREIGN KEY (`user_id`) REFERENCES `users` (`id`),
66 CONSTRAINT `comment_video`
67 FOREIGN KEY (`video_id`) REFERENCES `videos` (`id`));
68
69-- #2.6 Table: Video Likes
70CREATE TABLE `video_likes` (
71 `id` INT NOT NULL AUTO_INCREMENT,
72 `video_id` INT NULL,
73 `user_id` INT NULL,
74 `is_up` BOOLEAN NULL DEFAULT FALSE,
75 `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
76 `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
77
78 PRIMARY KEY (`id`),
79 CONSTRAINT `uv_video_like`
80 FOREIGN KEY (`video_id`) REFERENCES `videos` (`id`),
81 CONSTRAINT `uv_user_like`
82 FOREIGN KEY (`user_id`) REFERENCES `users` (`id`));
83
84
85-- #2.7 Table: Comment Likes
86CREATE TABLE `comment_likes` (
87 `id` INT NOT NULL AUTO_INCREMENT,
88 `comment_id` INT NULL,
89 `user_id` INT NULL,
90 `is_up` BOOLEAN NULL DEFAULT FALSE,
91 `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
92 `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
93
94 PRIMARY KEY (`id`),
95 CONSTRAINT `uc_comment_like`
96 FOREIGN KEY (`comment_id`) REFERENCES `comments` (`id`),
97 CONSTRAINT `uc_user_like`
98 FOREIGN KEY (`user_id`) REFERENCES `users` (`id`));
99
100-- #2.8 Table: Categories (for videos)
101CREATE TABLE `categories` (
102 `id` INT NOT NULL AUTO_INCREMENT,
103 `category` VARCHAR(45) NOT NULL,
104
105 PRIMARY KEY (`id`));
106
107-- #2.9 Table: Video-Categories Many to Many Relationship
108CREATE TABLE `video_category` (
109 `id` INT NOT NULL AUTO_INCREMENT,
110 `video_id` INT NULL,
111 `category_id` INT NULL,
112
113 PRIMARY KEY (`id`),
114 CONSTRAINT `fk_vc_video`
115 FOREIGN KEY (`video_id`) REFERENCES `videos` (`id`),
116 CONSTRAINT `fk_vc_category`
117 FOREIGN KEY (`category_id`) REFERENCES `categories` (`id`));
118
119-- #3 (Optional) Insert some data into tables
120-- #3.1 Users
121Insert into `users` (`username`, `age`, `email`, `password`)
122Values ('Senshi', 23, 'saba.pochkhua@gmail.com', '3668D391BECFEC1C0EC3B388CB82DCA6');
123
124Insert into `users` (`username`, `age`, `email`, `password`)
125Values ('pewdiepie', 27, 'pdp@gmail.com', 'C83E986814118CB34E3BDF0BBD12AB55');
126
127Insert into `users` (`username`, `age`, `email`, `password`)
128Values ('Eminem', 33, 'em@gmail.com', '26B637ED41273425BE243E8D42E5B461');
129
130Insert IGNORE into `users` (`username`, `age`, `email`, `password`)
131Values ('someone', 23, 'saba.pochkhua@gmail.com', '3668D391BECFEC1C0EC3B388CB82DCA6'); -- error: duplicate entry on mail
132
133Insert IGNORE into `users` (`username`, `age`, `email`, `password`)
134Values ('killer_boy', 13, 'best_boy_EU@gmail.com', '7745D9B1899F8C7316ECF065D8FC2469'); -- error: check constraint on age
135
136-- #3.2 roles
137Insert into `roles` (`role`)
138Values ('admin');
139
140Insert into `roles` (`role`)
141Values ('super_user');
142
143Insert into `roles` (`role`)
144Values ('user');
145
146-- #3.3 user_roles
147Insert into `user_roles` (`user_id`, `role_id`)
148Values ((SELECT `id` from `users` where `username` = 'Senshi'), (SELECT `id` from `roles` where `role` = 'admin'));
149
150Insert into `user_roles` (`user_id`, `role_id`)
151Values ((SELECT `id` from `users` where `username` = 'pewdiepie'), (SELECT `id` from `roles` where `role` = 'user'));
152
153Insert IGNORE into `user_roles` (`user_id`, `role_id`)
154Values (1000, (SELECT `id` from `roles` where `role` = 'user')); -- error: fk constraint on user id
155
156Insert IGNORE into `user_roles` (`user_id`, `role_id`)
157Values ((SELECT `id` from `users` where `username` = 'Senshi'), 1000); -- error: fk constraint on role id
158
159-- #3.4 Videos
160Insert into `videos` (`user_id`, `title`, `description`, `video_url`)
161values ((SELECT `id` from `users` where `username` = 'pewdiepie'), 'bitch lasagna', 'Track made by Party In Backyard ► https://www.youtube.com/channel/blablabla', '/videos/1.mp4');
162
163Insert into `videos` (`user_id`, `title`, `description`, `video_url`)
164values ((SELECT `id` from `users` where `username` = 'pewdiepie'), 'bitch lasagna *UPDATED*', 'GREATES MUSIC IN DA WORLD', '/videos/2.mp4');
165
166Insert IGNORE into `videos` (`user_id`, `title`, `description`, `video_url`)
167values (1000, 'Something', 'Something Desc', '/videos/3.mp4'); -- error: fk constraint on user id
168
169-- #3.5 Comments
170Insert into `comments` (`user_id`, `video_id`, `content`)
171values ((SELECT `id` from `users` where `username` = 'Senshi'), (SELECT `id` from `videos` where `title` = 'bitch lasagna'), 'Greates Music Video EVER!');
172
173Insert into `comments` (`user_id`, `video_id`, `content`)
174values ((SELECT `id` from `users` where `username` = 'Eminem'), (SELECT `id` from `videos` where `title` = 'bitch lasagna'), 'MY MUSIC IS BETTER!');
175
176Insert IGNORE into `comments` (`user_id`, `video_id`, `content`)
177values ((SELECT `id` from `users` where `username` = 'Eminem'), (SELECT `id` from `videos` where `title` = 'bitch lasagna'), null); -- error: content cannot be null
178
179-- #3.6 Vide Likes
180Insert into `video_likes` (`video_id`, `user_id`, `is_up`)
181values ((SELECT `id` from `videos` where `title` = 'bitch lasagna'), (SELECT `id` from `users` where `username` = 'Senshi'), true);
182
183Insert into `video_likes` (`video_id`, `user_id`, `is_up`)
184values ((SELECT `id` from `videos` where `title` = 'bitch lasagna'), (SELECT `id` from `users` where `username` = 'pewdiepie'), true);
185
186Insert into `video_likes` (`video_id`, `user_id`, `is_up`)
187values ((SELECT `id` from `videos` where `title` = 'bitch lasagna'), (SELECT `id` from `users` where `username` = 'Eminem'), false);
188
189-- #3.7 Comment Likes
190Insert into `comment_likes` (`comment_id`, `user_id`, `is_up`)
191values ((SELECT `id` from `comments` where `content` = 'Greates Music Video EVER!'), (SELECT `id` from `users` where `username` = 'pewdiepie'), true);
192
193Insert into `comment_likes` (`comment_id`, `user_id`, `is_up`)
194values ((SELECT `id` from `comments` where `content` = 'MY MUSIC IS BETTER!'), (SELECT `id` from `users` where `username` = 'pewdiepie'), false);
195
196Insert into `comment_likes` (`comment_id`, `user_id`, `is_up`)
197values ((SELECT `id` from `comments` where `content` = 'MY MUSIC IS BETTER!'), (SELECT `id` from `users` where `username` = 'Senshi'), false);
198
199-- #3.8 Categories
200Insert into `categories` (`category`)
201values ('Music');
202
203Insert into `categories` (`category`)
204values ('Sport');
205
206Insert into `categories` (`category`)
207values ('Funny');
208
209-- #3.9 Video Category Connection
210Insert into `video_category` (`video_id`, `category_id`)
211values ((SELECT `id` from `videos` where `title` = 'bitch lasagna'), (SELECT `id` from `categories` where `category` = 'Music'));
212
213Insert into `video_category` (`video_id`, `category_id`)
214values ((SELECT `id` from `videos` where `title` = 'bitch lasagna'), (SELECT `id` from `categories` where `category` = 'Funny'));
215
216
217-- #4. Create Views
218-- #4.1 View: User Information
219CREATE OR REPLACE VIEW UserInfo AS
220SELECT `username`, `email`, `age`, COALESCE(videos.count, 0) as 'Total Uploaded Videos', COALESCE(comments.count, 0) as 'Total Comments Written', COALESCE(comment_likes.count, 0) as 'Total Comments Liked/Disliked' FROM users
221LEFT OUTER JOIN (SELECT `user_id`, COUNT(*) as count FROM `videos` GROUP BY `user_id`) videos on users.id = videos.user_id
222LEFT OUTER JOIN (SELECT `user_id`, COUNT(*) as count FROM `comments` GROUP BY `user_id`) comments on users.id = comments.user_id
223LEFT OUTER JOIN (SELECT `user_id`, COUNT(*) as count FROM `comment_likes` GROUP BY `user_id`) comment_likes on users.id = comment_likes.user_id;
224
225select * from UserInfo;
226
227-- #4.2 View: Video Information
228CREATE OR REPLACE VIEW VideoInfo AS
229SELECT `title`, `description`, `video_url`, COALESCE(video_all_comments.count, 0) as 'Total Comments on Video', COALESCE(video_all_likes.count, 0) as 'Total Likes', COALESCE(video_all_dislikes.count, 0) as 'Total Disikes' FROM videos
230LEFT OUTER JOIN (SELECT `video_id`, COUNT(*) as count FROM `comments` GROUP BY `video_id`) video_all_comments on videos.id = video_all_comments .video_id
231LEFT OUTER JOIN (SELECT `video_id`, COUNT(*) as count FROM `video_likes` where is_up GROUP BY `video_id`) video_all_likes on videos.id = video_all_likes .video_id
232LEFT OUTER JOIN (SELECT `video_id`, COUNT(*) as count FROM `video_likes` where !is_up GROUP BY `video_id`) video_all_dislikes on videos.id = video_all_dislikes.video_id;
233
234select * from VideoInfo;
235
236-- #4.3 View: Category Info
237CREATE OR REPLACE VIEW CategoryInfo as
238SELECT categories.id, categories.category, COALESCE(cat.count,0) as 'Video Count' FROM categories
239LEFT OUTER JOIN (SELECT `category_id`, COUNT(*) as count FROM `video_category` GROUP BY `category_id`) cat on cat.category_id = categories.id;
240
241SELECT * FROM CategoryInfo;