· 6 years ago · Jun 10, 2019, 09:06 PM
1/* ----- 1 ----- */
2-- -----------------------------------------------------
3-- Schema forum
4-- -----------------------------------------------------
5CREATE SCHEMA IF NOT EXISTS `forum` DEFAULT CHARACTER SET utf8;
6USE `forum` ;
7
8-- -----------------------------------------------------
9-- Table `forum`.`users`
10-- -----------------------------------------------------
11CREATE TABLE IF NOT EXISTS `forum`.`users` (
12 `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
13 `first_name` VARCHAR(255) NOT NULL,
14 `last_name` VARCHAR(255) NOT NULL,
15 `email` VARCHAR(255) NOT NULL,
16 `password` VARCHAR(32) NOT NULL,
17 `is_admin` TINYINT NOT NULL DEFAULT 0,
18 `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
19 PRIMARY KEY (`id`),
20 UNIQUE INDEX `id_UNIQUE` (`id` ASC) VISIBLE,
21 UNIQUE INDEX `email_UNIQUE` (`email` ASC) VISIBLE)
22ENGINE = InnoDB;
23
24
25-- -----------------------------------------------------
26-- Table `forum`.`articles`
27-- -----------------------------------------------------
28CREATE TABLE IF NOT EXISTS `forum`.`articles` (
29 `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
30 `title` VARCHAR(255) NOT NULL,
31 `content` LONGTEXT NOT NULL,
32 `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
33 PRIMARY KEY (`id`),
34 UNIQUE INDEX `id_UNIQUE` (`id` ASC) VISIBLE)
35ENGINE = InnoDB;
36
37
38-- -----------------------------------------------------
39-- Table `forum`.`article_comments`
40-- -----------------------------------------------------
41CREATE TABLE IF NOT EXISTS `forum`.`article_comments` (
42 `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
43 `user_id` INT UNSIGNED NOT NULL,
44 `article_id` INT UNSIGNED NOT NULL,
45 `content` MEDIUMTEXT NOT NULL,
46 `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
47 PRIMARY KEY (`id`, `user_id`, `article_id`),
48 UNIQUE INDEX `id_UNIQUE` (`id` ASC) VISIBLE,
49 INDEX `fk_article_comments_users_idx` (`user_id` ASC) VISIBLE,
50 INDEX `fk_article_comments_articles1_idx` (`article_id` ASC) VISIBLE,
51 CONSTRAINT `fk_article_comments_users`
52 FOREIGN KEY (`user_id`)
53 REFERENCES `forum`.`users` (`id`)
54 ON DELETE NO ACTION
55 ON UPDATE NO ACTION,
56 CONSTRAINT `fk_article_comments_articles1`
57 FOREIGN KEY (`article_id`)
58 REFERENCES `forum`.`articles` (`id`)
59 ON DELETE NO ACTION
60 ON UPDATE NO ACTION)
61ENGINE = InnoDB;
62
63
64-- -----------------------------------------------------
65-- Table `forum`.`user_articles`
66-- -----------------------------------------------------
67CREATE TABLE IF NOT EXISTS `forum`.`user_articles` (
68 `user_id` INT UNSIGNED NOT NULL,
69 `article_id` INT UNSIGNED NOT NULL,
70 PRIMARY KEY (`user_id`, `article_id`),
71 INDEX `fk_user_articles_articles1_idx` (`article_id` ASC) VISIBLE,
72 CONSTRAINT `fk_user_articles_users1`
73 FOREIGN KEY (`user_id`)
74 REFERENCES `forum`.`users` (`id`)
75 ON DELETE NO ACTION
76 ON UPDATE NO ACTION,
77 CONSTRAINT `fk_user_articles_articles1`
78 FOREIGN KEY (`article_id`)
79 REFERENCES `forum`.`articles` (`id`)
80 ON DELETE NO ACTION
81 ON UPDATE NO ACTION)
82ENGINE = InnoDB;
83
84
85SET SQL_MODE=@OLD_SQL_MODE;
86SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
87SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
88
89-- -----------------------------------------------------
90-- Data for table `forum`.`users`
91-- -----------------------------------------------------
92START TRANSACTION;
93USE `forum`;
94INSERT INTO `forum`.`users` (`id`, `first_name`, `last_name`, `email`, `password`, `is_admin`, `created_at`) VALUES (1, 'John', 'Doe', 'john@doe.com', 'hash1', 1, NULL);
95INSERT INTO `forum`.`users` (`id`, `first_name`, `last_name`, `email`, `password`, `is_admin`, `created_at`) VALUES (2, 'Jane', 'Doe', 'jane@doe.com', 'hash2', 0, NULL);
96INSERT INTO `forum`.`users` (`id`, `first_name`, `last_name`, `email`, `password`, `is_admin`, `created_at`) VALUES (3, 'Ben', 'Johnson', 'ben@johnson.com', 'hash3', 0, NULL);
97
98COMMIT;
99
100
101-- -----------------------------------------------------
102-- Data for table `forum`.`articles`
103-- -----------------------------------------------------
104START TRANSACTION;
105USE `forum`;
106INSERT INTO `forum`.`articles` (`id`, `title`, `content`, `created_at`) VALUES (1, 'Article 1', 'My very first article here...', NULL);
107INSERT INTO `forum`.`articles` (`id`, `title`, `content`, `created_at`) VALUES (2, '2nd Article', 'Really cool article to show everyone here :)', NULL);
108
109COMMIT;
110
111
112-- -----------------------------------------------------
113-- Data for table `forum`.`article_comments`
114-- -----------------------------------------------------
115START TRANSACTION;
116USE `forum`;
117INSERT INTO `forum`.`article_comments` (`id`, `user_id`, `article_id`, `content`, `created_at`) VALUES (1, 1, 1, 'test comment', NULL);
118INSERT INTO `forum`.`article_comments` (`id`, `user_id`, `article_id`, `content`, `created_at`) VALUES (2, 1, 1, 'test comment 2', NULL);
119INSERT INTO `forum`.`article_comments` (`id`, `user_id`, `article_id`, `content`, `created_at`) VALUES (3, 2, 1, 'awesome!', NULL);
120INSERT INTO `forum`.`article_comments` (`id`, `user_id`, `article_id`, `content`, `created_at`) VALUES (4, 3, 2, 'nah... awful shit...', NULL);
121
122COMMIT;
123
124
125-- -----------------------------------------------------
126-- Data for table `forum`.`user_articles`
127-- -----------------------------------------------------
128START TRANSACTION;
129USE `forum`;
130INSERT INTO `forum`.`user_articles` (`user_id`, `article_id`) VALUES (1, 1);
131INSERT INTO `forum`.`user_articles` (`user_id`, `article_id`) VALUES (1, 2);
132
133COMMIT;
134
135/* ----- 2 ----- */
136select *
137from `users`
138where `is_admin` = 1;
139
140/* ----- 3 ----- */
141select `user_id`, COUNT(*) as `comment_count`
142from `article_comments`
143group by `user_id`;
144
145/* ----- 4 ----- */
146select `u`.`first_name` as `user`, `a`.`title` as `article`, `c`.`content` as `comment`
147from `users` `u`
148left outer join `articles` `a` on `a`.`id` in (select `article_id` from `user_articles` where `user_id` = `u`.`id`)
149inner join `article_comments` `c` on `c`.`user_id` = `u`.`id` and (`a`.`id` = `c`.`article_id` or `a`.`id` is null);
150
151/* ----- 5 ----- */
152select `u`.`first_name` as `user`, COUNT(*) as `comment_count`
153from `users` `u`
154join `article_comments` `c` on `u`.`id` = `c`.`user_id`
155group by `u`.`id`