JnSSujGF

1DROP DATABASE IF EXISTS vk;
2CREATE DATABASE vk;
3USE vk;
4
5DROP TABLE IF EXISTS users;
6CREATE TABLE users (
7	id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, 
8    firstname VARCHAR(50),
9    lastname VARCHAR(50),
10    email VARCHAR(120) UNIQUE,
11 	password_hash VARCHAR(100), 
12	phone BIGINT UNSIGNED UNIQUE, 
13	
14    INDEX users_firstname_lastname_idx(firstname, lastname)
15) COMMENT 'юзеры';
16
17DROP TABLE IF EXISTS `profiles`;
18CREATE TABLE `profiles` (
19	user_id BIGINT UNSIGNED NOT NULL UNIQUE,
20    gender CHAR(1),
21    birthday DATE,
22	photo_id BIGINT UNSIGNED NULL,
23    created_at DATETIME DEFAULT NOW(),
24    hometown VARCHAR(100)
25
26);
27
28ALTER TABLE `profiles` ADD CONSTRAINT fk_user_id
29    FOREIGN KEY (user_id) REFERENCES users(id)
30    ON UPDATE CASCADE -- (значение по умолчанию)
31    ON DELETE CASCADE; -- (значение по умолчанию)
32
33DROP TABLE IF EXISTS messages;
34CREATE TABLE messages (
35	id SERIAL, -- SERIAL = BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE
36	from_user_id BIGINT UNSIGNED NOT NULL,
37    to_user_id BIGINT UNSIGNED NOT NULL,
38    body TEXT,
39    created_at DATETIME DEFAULT NOW(), -- можно будет даже не упоминать это поле при вставке
40
41    FOREIGN KEY (from_user_id) REFERENCES users(id)
42    ON DELETE CASCADE,
43    FOREIGN KEY (to_user_id) REFERENCES users(id)
44    ON DELETE CASCADE
45
46);
47
48DROP TABLE IF EXISTS friend_requests;
49CREATE TABLE friend_requests (
50	-- id SERIAL, -- изменили на составной ключ (initiator_user_id, target_user_id)
51	initiator_user_id BIGINT UNSIGNED NOT NULL,
52    target_user_id BIGINT UNSIGNED NOT NULL,
53    `status` ENUM('requested', 'approved', 'unfriended', 'declined'),
54    -- `status` TINYINT(1) UNSIGNED, -- в этом случае в коде хранили бы цифирный enum (0, 1, 2, 3...)
55	requested_at DATETIME DEFAULT NOW(),
56	confirmed_at DATETIME ON UPDATE NOW(), -- можно будет даже не упоминать это поле при обновлении
57	
58    PRIMARY KEY (initiator_user_id, target_user_id),
59    FOREIGN KEY (initiator_user_id) REFERENCES users(id)
60    ON DELETE CASCADE,
61    FOREIGN KEY (target_user_id) REFERENCES users(id)
62    ON DELETE CASCADE,
63    CHECK (initiator_user_id <> target_user_id)
64);
65-- чтобы пользователь сам себе не отправил запрос в друзья
66ALTER TABLE friend_requests 
67ADD CHECK(initiator_user_id <> target_user_id);
68
69DROP TABLE IF EXISTS communities;
70CREATE TABLE communities(
71	id SERIAL,
72	name VARCHAR(150),
73	admin_user_id BIGINT UNSIGNED NOT NULL,
74	
75	INDEX communities_name_idx(name), -- индексу можно давать свое имя (communities_name_idx)
76	foreign key (admin_user_id) references users(id)
77    ON DELETE CASCADE
78);
79
80DROP TABLE IF EXISTS users_communities;
81CREATE TABLE users_communities(
82	user_id BIGINT UNSIGNED NOT NULL,
83	community_id BIGINT UNSIGNED NOT NULL,
84  
85	PRIMARY KEY (user_id, community_id), -- чтобы не было 2 записей о пользователе и сообществе
86    FOREIGN KEY (user_id) REFERENCES users(id)
87    ON DELETE CASCADE,
88    FOREIGN KEY (community_id) REFERENCES communities(id)
89);
90
91DROP TABLE IF EXISTS media_types;
92CREATE TABLE media_types(
93	id SERIAL,
94    name VARCHAR(255), -- записей мало, поэтому в индексе нет необходимости
95    created_at DATETIME DEFAULT NOW(),
96    updated_at DATETIME ON UPDATE CURRENT_TIMESTAMP
97);
98
99DROP TABLE IF EXISTS media;
100CREATE TABLE media(
101	id SERIAL,
102    media_type_id BIGINT UNSIGNED NOT NULL,
103    user_id BIGINT UNSIGNED NOT NULL,
104  	body text,
105    filename VARCHAR(255),
106    -- file blob,    	
107    size INT,
108	metadata JSON,
109    created_at DATETIME DEFAULT NOW(),
110    updated_at DATETIME ON UPDATE CURRENT_TIMESTAMP,
111
112    FOREIGN KEY (user_id) REFERENCES users(id)
113    ON DELETE CASCADE,
114    FOREIGN KEY (media_type_id) REFERENCES media_types(id)
115);
116
117DROP TABLE IF EXISTS likes;
118CREATE TABLE likes(
119	id SERIAL,
120    user_id BIGINT UNSIGNED NOT NULL,
121    media_id BIGINT UNSIGNED NOT NULL,
122    created_at DATETIME DEFAULT NOW()
123
124    -- PRIMARY KEY (user_id, media_id) – можно было и так вместо id в качестве PK
125  	-- слишком увлекаться индексами тоже опасно, рациональнее их добавлять по мере необходимости (напр., провисают по времени какие-то запросы)  
126
127
128    , FOREIGN KEY (user_id) REFERENCES users(id)
129    , FOREIGN KEY (media_id) REFERENCES media(id)
130
131);
132
133DROP TABLE IF EXISTS `photo_albums`;
134CREATE TABLE `photo_albums` (
135	`id` SERIAL,
136	`name` varchar(255) DEFAULT NULL,
137    `user_id` BIGINT UNSIGNED DEFAULT NULL,
138
139    FOREIGN KEY (user_id) REFERENCES users(id)
140    ON DELETE CASCADE,
141  	PRIMARY KEY (`id`)
142);
143
144DROP TABLE IF EXISTS `photos`;
145CREATE TABLE `photos` (
146	id SERIAL,
147	`album_id` BIGINT unsigned NOT NULL,
148	`media_id` BIGINT unsigned NOT NULL,
149
150	FOREIGN KEY (album_id) REFERENCES photo_albums(id),
151    FOREIGN KEY (media_id) REFERENCES media(id)
152);
153
154DROP TABLE IF EXISTS `audios`;
155CREATE TABLE `audios` (
156	id SERIAL,
157	`title` varchar(255) DEFAULT NULL,
158	`user_id` BIGINT UNSIGNED NOT NULL,
159	`media_id` BIGINT UNSIGNED NOT NULL,
160
161	FOREIGN KEY (user_id) REFERENCES users(id)
162    ON DELETE CASCADE,
163  	FOREIGN KEY (media_id) REFERENCES media(id)
164);
165
166DROP TABLE IF EXISTS `apps`;
167CREATE TABLE `apps` (
168	id SERIAL,
169	`name` varchar(255) DEFAULT NULL,
170	`user_id` BIGINT UNSIGNED NOT NULL,
171	`media_id` BIGINT UNSIGNED NOT NULL,
172
173	FOREIGN KEY (user_id) REFERENCES users(id)
174    ON DELETE CASCADE,
175  	FOREIGN KEY (media_id) REFERENCES media(id)
176);
177
178DROP TABLE IF EXISTS `community_requests`;
179CREATE TABLE `community_requests` (
180	initiator_user_id BIGINT UNSIGNED NOT NULL,
181	target_user_id BIGINT UNSIGNED NOT NULL,
182	`status` ENUM('requested', 'approved', 'unfollowed', 'declined'),
183	request_sent_at DATETIME DEFAULT NOW(),
184	confirmed_at DATETIME ON UPDATE NOW(),
185
186	PRIMARY KEY (initiator_user_id, target_user_id),
187	FOREIGN KEY (initiator_user_id) REFERENCES users(id)
188    ON DELETE CASCADE,
189	FOREIGN KEY (target_user_id) REFERENCES users(id)
190    ON DELETE CASCADE,
191	CHECK (initiator_user_id <> target_user_id)
192);
193
194ALTER TABLE community_requests
195ADD CHECK(initiator_user_id <> target_user_id);
196
197ALTER TABLE vk.likes 
198ADD CONSTRAINT likes_fk 
199FOREIGN KEY (media_id) REFERENCES vk.media(id);
200
201ALTER TABLE vk.likes 
202ADD CONSTRAINT likes_fk_1 
203FOREIGN KEY (user_id) REFERENCES vk.users(id);
204
205
206insert users (firstname, lastname, email, phone)
207values 
208('sql','my', 'lm5ao@noitsnot.lol', '88005553535'),
209('python','3.9SOON', 'lmao@yesitis.lol', '12358132143'),
210('git','bash', 'lma7o@relatively.lol', '98765432155'),
211('java','enterprise', 'send@help().lol', '85274196355'),
212('machine','learning', 'lmao@itisnt.lol', '515442221'),
213('artificial','intelligence', '01010011@killallhumans.lol', '01101110101'),
214('dev','ops', 'kernel@panic.lol', null),
215('freelance','bigmoney', '5$@perhour.lol', '555555555'),
216('big','data', 'low@buget.lol', '000000001'),
217('agile','scrum', 'office@religion.lol', '191919191'),
218('kewl','h@cker', 'whois@traceip.lol', '133713371337'),
219('ihavea','fever', 'andsql@hastosuffer.lol', '753951852');
220
221insert profiles (user_id, gender, birthday, photo_id, hometown)
222values
223(1, 'M', 11/1/1111, 1, 'Onetown'),
224(2, 'F', 12/1/1111, 2, 'Twotown'),
225(3, 'M', 13/1/1111, 3, 'Threetown'),
226(4, 'F', 14/1/1111, 4, 'Fourtown'),
227(5, 'M', 15/1/1111, 5, 'Fivetown'),
228(6, 'F', 16/1/1111, 6, 'Sixtown'),
229(7, 'M', 17/1/1111, 7, 'Seventown'),
230(8, 'F', 18/1/1111, 8, 'Eighttown'),
231(9, 'M', 19/1/1111, 9, 'Ninetown'),
232(10, 'F', 20/1/1111, 10, 'Tentown'),
233(11, 'M', 21/1/1111, 11, 'Eleventown'),
234(12, 'F', 22/1/1111, 12, 'Twelvetown');
235
236insert messages (from_user_id, to_user_id, body)
237values
238(1, 2, 'lol'),
239(2, 3, 'lol'),
240(3, 4, 'lol'),
241(4, 5, 'lol'),
242(5, 6, 'lol'),
243(6, 7, 'lol'),
244(7, 8, 'lol'),
245(8, 9, 'lol'),
246(9, 10, 'lol'),
247(10, 11, 'lol'),
248(11, 12, 'lol'),
249(12, 1, 'lol');
250
251insert into friend_requests (initiator_user_id, target_user_id, `status`)
252values
253(1, 2, 'requested'),
254(2, 3, 'approved'),
255(3, 4, 'requested'),
256(4, 5, 'approved'),
257(5, 6, 'approved'),
258(6, 7, 'declined'),
259(7, 8, 'approved'),
260(8, 9, 'requested'),
261(9, 10, 'declined'),
262(10, 11, 'approved'),
263(11, 12, 'approved'),
264(12, 1, 'declined');
265
266insert communities (`name`, admin_user_id)
267values
268('databases', 1),
269('programming languages', 2),
270('utilities', 3),
271('fields', 4),
272('jokes', 5),
273('ambient', 6),
274('dark ambient', 7),
275('rock', 8),
276('jazz', 9),
277('old', 10),
278('crocodiles', 11),
279('monkeys', 12);
280
281insert users_communities (user_id, community_id)
282values 
283(1, 1),
284(2, 2),
285(3, 3),
286(4, 4),
287(5, 5),
288(6, 6),
289(7, 7),
290(8, 8),
291(9, 9),
292(10, 10),
293(11, 11),
294(12, 12);
295
296insert media_types (`name`)
297values
298('photo'),
299('video'),
300('audio'),
301('app');
302
303insert media (user_id, media_type_id, body, filename, `size`, metadata)
304values
305(1, 1, 'a photo', 'photo1.jpg', 512, null),
306(2, 1, 'a photo', 'photo2.jpg', 512, null),
307(3, 1, 'a photo', 'photo3.jpg', 512, null),
308(4, 1, 'a photo', 'photo4.jpg', 512, null),
309(5, 2, 'a video', 'video1.mp4', 512, null),
310(6, 2, 'a video', 'video2.mp4', 512, null),
311(7, 2, 'a video', 'video3.mp4', 512, null),
312(8, 2, 'a video', 'video4.mp4', 512, null),
313(9, 3, 'an audio', 'audio1.mp3', 512, null),
314(10, 3, 'an audio', 'audio2.mp3', 512, null),
315(11, 4, 'an app', 'win32.exe', 2048, null),
316(12, 4, 'an app', 'launch.exe', 8128, null);
317
318insert likes (user_id, media_id)
319values
320(1, 24),
321(2, 13),
322(3, 14),
323(4, 15),
324(5, 16),
325(6, 17),
326(7, 18),
327(8, 19),
328(10, 21),
329(12, 22);
330
331insert photo_albums (`name`, user_id)
332values
333('a', 1),
334('b', 2),
335('c', 3),
336('adsa', 4),
337('adsa', 5),
338('ssa', 6),
339('ssssa', 7),
340('awde', 8),
341('aewqe', 9),
342('qwewqea', 10),
343('awqewqre', 11),
344('erea', 12);
345
346insert photos (album_id, media_id)
347values
348(1, 24),
349(2, 13),
350(3, 14),
351(4, 15),
352(5, 16),
353(6, 17),
354(7, 18),
355(8, 19),
356(10, 21),
357(12, 22);
358
359insert audios (title, user_id, media_id)
360values
361('asd', 1, 24),
362('afdsd', 2, 13),
363('afdfsd', 3, 14),
364('aseed', 4, 15),
365('aeefdsd', 5, 16),
366('aeesd', 6, 17),
367('aeewsd', 7, 18),
368('aewesd', 8, 19),
369('afdwsd', 10, 21),
370('dfdf', 12, 22);
371
372insert apps (`name`, user_id, media_id)
373values
374('asd', 1, 24),
375('afdsd', 2, 13),
376('afdfsd', 3, 14),
377('aseed', 4, 15),
378('aeefdsd', 5, 16),
379('aeesd', 6, 17),
380('aeewsd', 7, 18),
381('aewesd', 8, 19),
382('afdwsd', 10, 21),
383('dfdf', 12, 22);
384
385insert community_requests (initiator_user_id, target_user_id, `status`)
386values
387(1, 2, 'requested'),
388(2, 3, 'approved'),
389(3, 4, 'requested'),
390(4, 5, 'approved'),
391(5, 6, 'approved'),
392(6, 7, 'declined'),
393(7, 8, 'approved'),
394(8, 9, 'requested'),
395(9, 10, 'declined'),
396(10, 11, 'approved'),
397(11, 12, 'approved'),
398(12, 1, 'declined');
399