· 4 years ago · Mar 30, 2021, 04:32 PM
1drop database if exists chatbook;
2create database chatbook;
3
4use chatbook;
5
6-- create tables
7
8-- files table
9DROP TABLE IF EXISTS `files`;
10CREATE TABLE `files` (
11 `id` int NOT NULL AUTO_INCREMENT,
12 `link` varchar(255) NOT NULL,
13 PRIMARY KEY (`id`)
14);
15
16-- users table
17DROP TABLE IF EXISTS `users`;
18CREATE TABLE `users` (
19 `id` int NOT NULL AUTO_INCREMENT,
20 `first_name` varchar(45) NOT NULL,
21 `last_name` varchar(45) NOT NULL,
22 `email` varchar(45) NOT NULL,
23 `bio` varchar(255) DEFAULT 'This is default bio.',
24 `password` char(64) NOT NULL,
25 `gender` enum('male','female') DEFAULT NULL,
26 `year_of_birth` int DEFAULT NULL,
27 `phone` int DEFAULT NULL,
28 `profile_image` int DEFAULT NULL,
29 `cover_image` int DEFAULT NULL,
30 `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
31 `updated_at` datetime DEFAULT CURRENT_TIMESTAMP,
32 PRIMARY KEY (`id`),
33 UNIQUE KEY (`email`),
34 KEY `fk_users_cover_id_idx` (`cover_image`),
35 KEY `fk_users_profile_id_idx` (`profile_image`),
36 CONSTRAINT `fk_users_cover_id` FOREIGN KEY (`cover_image`) REFERENCES `files` (`id`),
37 CONSTRAINT `fk_users_profile_id` FOREIGN KEY (`profile_image`) REFERENCES `files` (`id`)
38);
39
40-- friends table
41DROP TABLE IF EXISTS `friends`;
42CREATE TABLE `friends` (
43 `user_id` int NOT NULL,
44 `friend_id` int NOT NULL,
45 `is_following` tinyint DEFAULT '1',
46 PRIMARY KEY (`user_id`,`friend_id`),
47 KEY `fk_friends_friend_id_idx` (`friend_id`),
48 CONSTRAINT `fk_friends_friend_id` FOREIGN KEY (`friend_id`) REFERENCES `users` (`id`),
49 CONSTRAINT `fk_friends_user_id` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)
50);
51
52-- receiver table
53DROP TABLE IF EXISTS `receiver`;
54CREATE TABLE `receiver` (
55 `id` int NOT NULL AUTO_INCREMENT,
56 `name` varchar(45) NOT NULL,
57 `description` text NOT NULL,
58 `creator_id` int NOT NULL,
59 `profile_image` int DEFAULT NULL,
60 `cover_image` int DEFAULT NULL,
61 `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
62 `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
63 `type` enum('groups','posts') NOT NULL,
64 PRIMARY KEY (`id`),
65 KEY `fk_receiver_creator_id_idx` (`creator_id`),
66 KEY `fk_receiver_cover_image_idx` (`cover_image`),
67 KEY `fk_receiver_profile_image_idx` (`profile_image`),
68 CONSTRAINT `fk_receiver_cover_image` FOREIGN KEY (`cover_image`) REFERENCES `files` (`id`),
69 CONSTRAINT `fk_receiver_creator_id` FOREIGN KEY (`creator_id`) REFERENCES `users` (`id`),
70 CONSTRAINT `fk_receiver_profile_image` FOREIGN KEY (`profile_image`) REFERENCES `files` (`id`)
71);
72
73-- posts table
74DROP TABLE IF EXISTS `posts`;
75CREATE TABLE `posts` (
76 `id` int NOT NULL AUTO_INCREMENT,
77 `creator_id` int NOT NULL,
78 `receiver_id` int DEFAULT NULL,
79 `replied_to` int DEFAULT NULL,
80 `text` text NOT NULL,
81 `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
82 `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
83 PRIMARY KEY (`id`),
84 CONSTRAINT FOREIGN KEY (`creator_id`) REFERENCES `users` (`id`),
85 CONSTRAINT FOREIGN KEY (`receiver_id`) REFERENCES `receiver` (`id`),
86 CONSTRAINT FOREIGN KEY (`replied_to`) REFERENCES `posts` (`id`)
87);
88
89
90-- groups table
91DROP TABLE IF EXISTS `groups`;
92CREATE TABLE `groups` (
93 `id` int NOT NULL,
94 PRIMARY KEY (`id`),
95 CONSTRAINT `fk_receiver_id` FOREIGN KEY (`id`) REFERENCES `receiver` (`id`)
96);
97
98-- pages tablet
99DROP TABLE IF EXISTS `pages`;
100CREATE TABLE `pages` (
101 `id` int NOT NULL,
102 PRIMARY KEY (`id`),
103 CONSTRAINT `fk_page_receiver_id` FOREIGN KEY (`id`) REFERENCES `receiver` (`id`)
104);
105
106-- membership table
107DROP TABLE IF EXISTS `members`;
108CREATE TABLE `members` (
109 `user_id` int NOT NULL,
110 `group_id` int NOT NULL,
111 `is_admin` tinyint NOT NULL DEFAULT '0',
112 `is_blocked` tinyint NOT NULL DEFAULT '0',
113 PRIMARY KEY (`user_id`,`group_id`),
114 CONSTRAINT `fk_members_group_id` FOREIGN KEY (`group_id`) REFERENCES `groups` (`id`),
115 CONSTRAINT `fk_members_user_id` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)
116);
117
118-- page_likes table
119DROP TABLE IF EXISTS `page_likes`;
120CREATE TABLE `page_likes` (
121 `user_id` int NOT NULL,
122 `page_id` int NOT NULL,
123 `is_admin` tinyint NOT NULL DEFAULT '0',
124 PRIMARY KEY (`user_id`,`page_id`),
125 CONSTRAINT `fk_page_like_page_id` FOREIGN KEY (`page_id`) REFERENCES `pages` (`id`),
126 CONSTRAINT `fk_page_like_user_id` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)
127);
128
129-- post_files table
130DROP TABLE IF EXISTS `post_files`;
131CREATE TABLE `post_files` (
132 `post_id` int NOT NULL,
133 `file_id` int NOT NULL,
134 PRIMARY KEY (`post_id`,`file_id`),
135 CONSTRAINT `fk_post_files_post_id` FOREIGN KEY (`post_id`) REFERENCES `posts` (`id`)
136);
137
138-- polls table
139DROP TABLE IF EXISTS `polls`;
140CREATE TABLE `polls` (
141 `id` int NOT NULL,
142 PRIMARY KEY (`id`),
143 CONSTRAINT `fk_poll_post_id` FOREIGN KEY (`id`) REFERENCES `posts` (`id`)
144);
145
146-- poll_answers table
147DROP TABLE IF EXISTS `poll_answers`;
148CREATE TABLE `poll_answers` (
149 `id` int NOT NULL AUTO_INCREMENT,
150 `poll_id` int NOT NULL,
151 `text` VARCHAR(255) NOT NULL,
152 PRIMARY KEY (`id`),
153 CONSTRAINT `fk_poll_answer_poll_id` FOREIGN KEY (`poll_id`) REFERENCES `polls` (`id`)
154);
155
156-- poll_votes table
157DROP TABLE IF EXISTS `poll_votes`;
158CREATE TABLE `poll_votes` (
159 `poll_id` int NOT NULL,
160 `answer_id` int NOT NULL,
161 `user_id` int NOT NULL,
162 PRIMARY KEY (`poll_id`, `user_id`, `answer_id`),
163 CONSTRAINT `fk_poll_vote_poll_id` FOREIGN KEY (`poll_id`) REFERENCES `polls` (`id`),
164 CONSTRAINT `fk_poll_vote_answer_id` FOREIGN KEY (`answer_id`) REFERENCES `poll_answers` (`id`),
165 CONSTRAINT `fk_poll_vote_user_id` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)
166);
167
168-- user_messages table
169DROP TABLE IF EXISTS `user_messages`;
170CREATE TABLE `user_messages` (
171 `id` int NOT NULL AUTO_INCREMENT,
172 `sender_id` int NOT NULL,
173 `user_id` int NOT NULL,
174 `text` text NOT NULL,
175 PRIMARY KEY (`id`),
176 CONSTRAINT `fk_user_message_sender_id` FOREIGN KEY (`sender_id`) REFERENCES `users` (`id`),
177 CONSTRAINT `fk_user_message_user_id` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)
178);
179
180-- stories table
181DROP TABLE IF EXISTS `stories`;
182CREATE TABLE `stories` (
183 `id` int NOT NULL AUTO_INCREMENT,
184 `user_id` int NOT NULL,
185 `file_id` int NOT NULL,
186 `text` text NOT NULL,
187 PRIMARY KEY (`id`),
188 CONSTRAINT `fk_story_file_id` FOREIGN KEY (`file_id`) REFERENCES `files` (`id`),
189 CONSTRAINT `fk_story_user_id` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)
190);
191
192-- shares table
193DROP TABLE IF EXISTS `shares`;
194CREATE TABLE `shares` (
195 `post_id` int NOT NULL,
196 `user_id` int NOT NULL,
197 `created_at` datetime default CURRENT_TIMESTAMP,
198 `is_private` tinyint DEFAULT '0',
199 PRIMARY KEY(`post_id`, `user_id`),
200 CONSTRAINT `fk_share_post_id` FOREIGN KEY (`post_id`) REFERENCES `posts` (`id`),
201 CONSTRAINT `fk_share_user_id` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)
202);
203
204-- reacts table
205DROP TABLE IF EXISTS `reacts`;
206CREATE TABLE `reacts` (
207 `post_id` int NOT NULL,
208 `user_id` int NOT NULL,
209 `type` enum('like', 'heart', 'laugh', 'sad', 'angry') NOT NULL DEFAULT 'like',
210 PRIMARY KEY(`post_id`, `user_id`),
211 CONSTRAINT `fk_react_post_id` FOREIGN KEY (`post_id`) REFERENCES `posts` (`id`),
212 CONSTRAINT `fk_react_user_id` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)
213);
214
215-- user_settings table
216DROP TABLE IF EXISTS `user_settings`;
217CREATE TABLE `user_settings` (
218 `id` int NOT NULL,
219 `theme` enum('light', 'dark') NOT NULL DEFAULT 'light',
220 `value1` int DEFAULT NULL,
221 `value2` int DEFAULT NULL,
222 `value3` int DEFAULT NULL,
223 PRIMARY KEY(`id`),
224 CONSTRAINT `fk_settings_user_id` FOREIGN KEY (`id`) REFERENCES `users` (`id`)
225);
226
227
228
229-- insert user
230INSERT INTO users(first_name, last_name, email, password)
231 VALUES('First', 'Last', 'test@gmail.com', '40bd001563085fc35165329ea1ff5c5ecbdbbeef'), -- password is '123'
232 ('First2', 'Last2', 'another@gmail.com', '40bd001563085fc35165329ea1ff5c5ecbdbbeef'), -- password is '123'
233 ('First3', 'Last3', 'thirdUser@gmail.com', '40bd001563085fc35165329ea1ff5c5ecbdbbeef'), -- password is '123'
234 ('First4', 'Last4', 'another.2@gmail.com', '40bd001563085fc35165329ea1ff5c5ecbdbbeef'); -- password is '123'
235
236SELECT * FROM users;
237
238-- delete user
239DELETE FROM users WHERE id=1;
240
241-- edit user
242UPDATE users SET
243 first_name = 'ChangedFirst',
244 gender = 'male',
245 bio='this is my changed bio.',
246 year_of_birth=1995,
247 phone=712345678,
248 profile_image=1,
249 cover_image=2
250WHERE id = 3;
251
252
253-- insert post
254-- edit post
255-- delete post
256
257-- add friend request
258-- accept friend request
259
260-- add, edit and delete story
261
262
263
264-- get user
265-- get post
266-- get members of group
267-- get all groups of a user
268-- get all pages that a user liked
269-- get friends of a user
270
271-- add file to database
272-- add file to post using file id.
273
274-- create, edit, delete group
275-- for creating a group we must create receiver first:
276
277-- after creating a group or page we must add creator as a member to `members` or `page_likes`.
278
279INSERT INTO receiver(name, creator_id, type) VALUES ('Programming Group', 1, 'group'), -- id = 1
280INSERT INTO groups(id) VALUES(1);
281INSERT INTO members(user_id, group_id, is_admin) VALUES(1, 1, 1) -- user with id 1 is admin of group with id 1.
282
283
284
285INSERT INTO receiver(name, creator_id, type) VALUES ('English, Class 4', 2, 'page'), -- id = 2
286INSERT INTO pages(id) VALUES(2);
287INSERT INTO page_likes(user_id, page_id, is_admin) VALUES(2, 2, 1) -- user with id 2 likes page with id 2 and is admin of that page too.
288
289
290INSERT INTO receiver(name, creator_id, type) VALUES ('Math, Class 3', 2, 'page'), -- id = 3
291INSERT INTO pages(id) VALUES(3);
292INSERT INTO page_likes(user_id, page_id, is_admin) VALUES(2, 2, 1) -- user with id likes page with id 3 and is admin of that page too.
293
294
295INSERT INTO receiver(name, creator_id, type) VALUES ('Math', 4, 'group'); -- id = 4
296INSERT INTO groups(id) VALUES(4);
297INSERT INTO members(user_id, group_id, is_admin) VALUES(4, 4, 1) -- user with id 4 is admin of group with id 4.
298
299
300-- edit and delete group and pages
301
302
303-- upload profile image and cover image
304INSERT INTO files('mathematic-group-profile.png') -- id = 3
305INSERT INTO files('mathematic-group-cover.png') -- id = 4
306
307-- change Math Group(id=4) and add a description.
308
309
310UPDATE receiver SET
311 description = 'this group created to solve Mathematic problems.'
312 profile_image = 3
313 cover_image = 4
314 updated_at = CURRENT_TIMESTAMP
315
316WHERE id=4; -- we know math group id is 4
317
318
319-- delete programming group (id=1)
320DELETE FROM groups WHERE id=1;
321DELETE FROM receiver WHERE id=1;
322
323
324-- a user can join to a group.
325-- user with id 2 (First2 Last2) wants to join math Group(id=4)
326INSERT INTO members(user_id, group_id) VALUES(2, 4);
327-- He also wants to like 'Math, Class 3' page(id=3).
328INSERT INTO page_likes(user_id, page_id) VALUES(2, 3);
329
330
331
332-- create poll with variable answer
333-- user answer to a poll
334
335-- send private message from user1 to user2
336
337-- react to post or comment. (what about story??)
338
339-- a user shares a post (post must not be private or comment)
340
341