7GnQHVje

1DROP TABLE IF EXISTS users; -- pretty standard: just users with their information
2CREATE TABLE users (
3	id SERIAL PRIMARY KEY,
4	username VARCHAR(64) COMMENT 'log in name',
5	password_hash VARCHAR(100),
6	e_mail VARCHAR(255),
7	displayname VARCHAR(64),
8	is_deleted bit DEFAULT 0,
9	INDEX users_displayname_index (displayname)
10);
11
12CREATE TABLE IF NOT EXISTS viewers (
13	id SERIAL PRIMARY KEY,
14	user_id BIGINT UNSIGNED,
15	`role` ENUM('plebs', 'followers', 'subscribers'),
16	FOREIGN KEY (user_id) REFERENCES users(id) ON UPDATE CASCADE ON DELETE SET NULL
17);
18
19
20DROP TABLE IF EXISTS channels;
21CREATE TABLE channels (
22	id SERIAL PRIMARY KEY,
23	category_id BIGINT UNSIGNED,
24	game_genre_id BIGINT UNSIGNED,
25	owner_id BIGINT UNSIGNED,
26	moderator_id BIGINT UNSIGNED,
27	about TEXT,
28	chat TEXT,
29	viewer_list_id BIGINT UNSIGNED,
30	FOREIGN KEY (owner_id) REFERENCES users(id) ON UPDATE CASCADE ON DELETE SET NULL,
31	FOREIGN KEY (moderator_id) REFERENCES users(id) ON UPDATE CASCADE ON DELETE SET NULL,
32	FOREIGN KEY (category_id) REFERENCES channel_categories(id) ON UPDATE CASCADE ON DELETE SET NULL,
33	FOREIGN KEY (game_genre_id) REFERENCES game_genres(id) ON UPDATE CASCADE ON DELETE SET NULL,
34	FOREIGN KEY (viewer_list_id) REFERENCES viewers(id) ON UPDATE CASCADE ON DELETE SET NULL
35);
36
37DROP TABLE IF EXISTS history_of_view;
38CREATE TABLE IF NOT EXISTS history_of_view (
39	user_id BIGINT UNSIGNED,
40	channel_id BIGINT UNSIGNED,
41	`date` DATETIME DEFAULT NOW()
42) ENGINE = ARCHIVE;
43
44INSERT INTO `viewers` VALUES
45(1,1,'followers'),
46(2,2,'plebs'),
47(3,3,'followers'),
48(4,4,'followers'),
49(5,5,'followers'),
50(6,6,'subscribers'),
51(7,7,'plebs'),
52(8,8,'plebs'),
53(9,9,'subscribers'),
54(10,10,'subscribers');
55
56-- триггер для наполнения таблицы истории просмотров для задачи 5.
57DELIMITER //
58CREATE TRIGGER history AFTER INSERT ON viewers
59FOR EACH ROW
60BEGIN
61    INSERT INTO history_of_views (user_id, channel_id, date) VALUES (NEW.user_id, channel_id, NOW());
62END//
63DELIMITER ;