· 8 years ago · Jul 24, 2018, 06:22 PM
1-- MySQL Script generated by MySQL Workbench
2-- Tue Jul 24 21:08:43 2018
3-- Model: New Model Version: 1.0
4-- MySQL Workbench Forward Engineering
5
6SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
7SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
8SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES';
9
10-- -----------------------------------------------------
11-- Schema mydb
12-- -----------------------------------------------------
13DROP SCHEMA IF EXISTS `mydb` ;
14
15-- -----------------------------------------------------
16-- Schema mydb
17-- -----------------------------------------------------
18CREATE SCHEMA IF NOT EXISTS `mydb` DEFAULT CHARACTER SET utf8 ;
19USE `mydb` ;
20
21-- -----------------------------------------------------
22-- Table `mydb`.`users`
23-- -----------------------------------------------------
24DROP TABLE IF EXISTS `mydb`.`users` ;
25
26CREATE TABLE IF NOT EXISTS `mydb`.`users` (
27 `id` INT(11) NOT NULL AUTO_INCREMENT,
28 `login` VARCHAR(255) NOT NULL,
29 `email` VARCHAR(255) NOT NULL,
30 `password_hash` CHAR(64) NOT NULL,
31 `avatar` VARCHAR(255) NULL DEFAULT NULL,
32 `phone` VARCHAR(128) NULL DEFAULT NULL,
33 `name` VARCHAR(128) NOT NULL,
34 `status` ENUM('ACTIVE', 'DISABLED', 'BANNED') NOT NULL DEFAULT 'ACTIVE',
35 `role` ENUM('USER', 'ADMIN') NOT NULL DEFAULT 'USER',
36 `access_token_hash` CHAR(64) NULL DEFAULT NULL,
37 `acc_action_token_hash` CHAR(64) NULL DEFAULT NULL,
38 `refresh_token_hash` CHAR(64) NULL DEFAULT NULL,
39 `regstamp` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
40 `last_visit` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
41 PRIMARY KEY (`id`),
42 UNIQUE INDEX `login` (`login` ASC),
43 UNIQUE INDEX `email` (`email` ASC))
44ENGINE = InnoDB
45AUTO_INCREMENT = 11
46DEFAULT CHARACTER SET = utf8;
47
48
49-- -----------------------------------------------------
50-- Table `mydb`.`questions`
51-- -----------------------------------------------------
52DROP TABLE IF EXISTS `mydb`.`questions` ;
53
54CREATE TABLE IF NOT EXISTS `mydb`.`questions` (
55 `id` INT(11) NOT NULL AUTO_INCREMENT,
56 `user_id` INT(11) NOT NULL,
57 `subject` VARCHAR(255) NOT NULL,
58 `question` TEXT NOT NULL,
59 `views` INT(10) NOT NULL DEFAULT 0,
60 `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
61 `updated_at` TIMESTAMP NULL DEFAULT NULL,
62 PRIMARY KEY (`id`),
63 INDEX `user_id` (`user_id` ASC),
64 FULLTEXT INDEX `subject` (`subject` ASC),
65 FULLTEXT INDEX `question` (`question` ASC),
66 CONSTRAINT `questions_ibfk_1`
67 FOREIGN KEY (`user_id`)
68 REFERENCES `mydb`.`users` (`id`)
69 ON DELETE SET NULL
70 ON UPDATE CASCADE)
71ENGINE = InnoDB
72AUTO_INCREMENT = 13
73DEFAULT CHARACTER SET = utf8;
74
75
76-- -----------------------------------------------------
77-- Table `mydb`.`answers`
78-- -----------------------------------------------------
79DROP TABLE IF EXISTS `mydb`.`answers` ;
80
81CREATE TABLE IF NOT EXISTS `mydb`.`answers` (
82 `id` INT(11) NOT NULL AUTO_INCREMENT,
83 `user_id` INT(11) NOT NULL,
84 `question_id` INT(11) NOT NULL,
85 `answer` TEXT NOT NULL,
86 `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
87 `updated_at` TIMESTAMP NULL DEFAULT NULL,
88 PRIMARY KEY (`id`),
89 INDEX `user_id` (`user_id` ASC),
90 INDEX `question_id` (`question_id` ASC),
91 CONSTRAINT `answers_ibfk_1`
92 FOREIGN KEY (`user_id`)
93 REFERENCES `mydb`.`users` (`id`)
94 ON DELETE CASCADE
95 ON UPDATE CASCADE,
96 CONSTRAINT `answers_ibfk_2`
97 FOREIGN KEY (`question_id`)
98 REFERENCES `mydb`.`questions` (`id`)
99 ON DELETE CASCADE
100 ON UPDATE CASCADE)
101ENGINE = InnoDB
102AUTO_INCREMENT = 18
103DEFAULT CHARACTER SET = utf8;
104
105
106-- -----------------------------------------------------
107-- Table `mydb`.`categories`
108-- -----------------------------------------------------
109DROP TABLE IF EXISTS `mydb`.`categories` ;
110
111CREATE TABLE IF NOT EXISTS `mydb`.`categories` (
112 `id` INT(11) NOT NULL AUTO_INCREMENT,
113 `name` VARCHAR(128) NOT NULL,
114 `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
115 PRIMARY KEY (`id`))
116ENGINE = InnoDB
117AUTO_INCREMENT = 5
118DEFAULT CHARACTER SET = utf8;
119
120
121-- -----------------------------------------------------
122-- Table `mydb`.`questions_categories`
123-- -----------------------------------------------------
124DROP TABLE IF EXISTS `mydb`.`questions_categories` ;
125
126CREATE TABLE IF NOT EXISTS `mydb`.`questions_categories` (
127 `question_id` INT(11) NOT NULL,
128 `category_id` INT(11) NOT NULL,
129 PRIMARY KEY (`question_id`, `category_id`),
130 INDEX `category_id` (`category_id` ASC),
131 INDEX `question_id` (`question_id` ASC),
132 CONSTRAINT `questions_categories_ibfk_1`
133 FOREIGN KEY (`question_id`)
134 REFERENCES `mydb`.`questions` (`id`)
135 ON DELETE CASCADE
136 ON UPDATE CASCADE,
137 CONSTRAINT `questions_categories_ibfk_2`
138 FOREIGN KEY (`category_id`)
139 REFERENCES `mydb`.`categories` (`id`)
140 ON DELETE CASCADE
141 ON UPDATE CASCADE)
142ENGINE = InnoDB
143DEFAULT CHARACTER SET = utf8;
144
145
146-- -----------------------------------------------------
147-- Table `mydb`.`tags`
148-- -----------------------------------------------------
149DROP TABLE IF EXISTS `mydb`.`tags` ;
150
151CREATE TABLE IF NOT EXISTS `mydb`.`tags` (
152 `id` INT(11) NOT NULL AUTO_INCREMENT,
153 `name` VARCHAR(128) NOT NULL,
154 `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
155 PRIMARY KEY (`id`))
156ENGINE = InnoDB
157DEFAULT CHARACTER SET = utf8;
158
159
160-- -----------------------------------------------------
161-- Table `mydb`.`questions_tags`
162-- -----------------------------------------------------
163DROP TABLE IF EXISTS `mydb`.`questions_tags` ;
164
165CREATE TABLE IF NOT EXISTS `mydb`.`questions_tags` (
166 `question_id` INT(11) NOT NULL,
167 `tag_id` INT(11) NOT NULL,
168 PRIMARY KEY (`question_id`, `tag_id`),
169 INDEX `tag_id` (`tag_id` ASC),
170 INDEX `question_id` (`question_id` ASC),
171 CONSTRAINT `questions_tags_ibfk_1`
172 FOREIGN KEY (`question_id`)
173 REFERENCES `mydb`.`questions` (`id`)
174 ON DELETE CASCADE
175 ON UPDATE CASCADE,
176 CONSTRAINT `questions_tags_ibfk_2`
177 FOREIGN KEY (`tag_id`)
178 REFERENCES `mydb`.`tags` (`id`)
179 ON DELETE CASCADE
180 ON UPDATE CASCADE)
181ENGINE = InnoDB
182DEFAULT CHARACTER SET = utf8;
183
184
185-- -----------------------------------------------------
186-- Table `mydb`.`votes`
187-- -----------------------------------------------------
188DROP TABLE IF EXISTS `mydb`.`votes` ;
189
190CREATE TABLE IF NOT EXISTS `mydb`.`votes` (
191 `id` INT(11) NOT NULL AUTO_INCREMENT,
192 `user_id` INT(11) NOT NULL,
193 `answer_id` INT(11) NOT NULL,
194 `vote` TINYINT(1) NOT NULL,
195 `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
196 `updated_at` DATETIME NULL DEFAULT NULL,
197 PRIMARY KEY (`id`),
198 INDEX `answer_id` (`answer_id` ASC),
199 INDEX `user_id` (`user_id` ASC),
200 UNIQUE INDEX `answer_user_index` (`user_id` ASC, `answer_id` ASC),
201 CONSTRAINT `votes_ibfk_1`
202 FOREIGN KEY (`answer_id`)
203 REFERENCES `mydb`.`answers` (`id`)
204 ON DELETE CASCADE
205 ON UPDATE CASCADE,
206 CONSTRAINT `votes_ibfk_2`
207 FOREIGN KEY (`user_id`)
208 REFERENCES `mydb`.`users` (`id`)
209 ON DELETE CASCADE
210 ON UPDATE CASCADE)
211ENGINE = InnoDB
212AUTO_INCREMENT = 4
213DEFAULT CHARACTER SET = utf8;
214
215
216SET SQL_MODE=@OLD_SQL_MODE;
217SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
218SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;