4tr7UbVE

1DROP DATABASE IF EXISTS `blog`;
2CREATE DATABASE if NOT EXISTS `blog` CHARACTER SET 'utf8';
3
4USE blog;
5
6CREATE TABLE IF NOT EXISTS `User`(
7	`id` SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
8	`username` VARCHAR(50) NOT NULL UNIQUE,
9	`mail` VARCHAR(50) NOT NULL UNIQUE,
10	`password` VARCHAR(255) NOT NULL,
11	`role` VARCHAR(255),
12	PRIMARY KEY (`id`)
13)
14ENGINE=INNODB;
15
16USE `blog`;
17
18CREATE TABLE IF NOT EXISTS `Chapitre`(
19	`id` SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
20	`title` VARCHAR(255) NOT NULL UNIQUE,
21	`chapitre` TEXT NOT NULL,
22	`created_at` DATETIME NOT NULL,
23	`published` TINYINT(1) NOT NULL DEFAULT 0,
24	PRIMARY KEY(`id`)
25)
26ENGINE=INNODB;
27
28USE `blog`;
29
30CREATE TABLE IF NOT EXISTS `Commentaire`(
31	`id` SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
32	`commentaire` TEXT NOT NULL,
33	`id_chapitre` SMALLINT NOT NULL,
34	`id_user` SMALLINT NOT NULL,
35	`id_parent` SMALLINT NULL,
36	`signaled` TINYINT(1) NOT NULL DEFAULT 0,
37	`banished` TINYINT(1) NOT NULL DEFAULT 0,
38	`created_at` DATETIME NOT NULL
39)
40ENGINE=INNODB;
41
42ALTER TABLE `Commentaire`
43ADD CONSTRAINT `fk_episode_id` FOREIGN KEY (`id_chapitre`) REFERENCES `chapitre`(`id`) ON DELETE CASCADE;
44ALTER TABLE `Commentaire`	
45ADD CONSTRAINT `fk_id_user` FOREIGN KEY (`id_user`) REFERENCES `User`(`id`) ON DELETE CASCADE;