eQPUY0td

1-- MySQL Workbench Forward Engineering
2
3SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
4SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
5SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';
6
7-- -----------------------------------------------------
8-- Schema mydb
9-- -----------------------------------------------------
10DROP SCHEMA IF EXISTS `mydb` ;
11
12-- -----------------------------------------------------
13-- Schema mydb
14-- -----------------------------------------------------
15CREATE SCHEMA IF NOT EXISTS `mydb` DEFAULT CHARACTER SET utf8 ;
16USE `mydb` ;
17
18-- -----------------------------------------------------
19-- Table `mydb`.`users`
20-- -----------------------------------------------------
21DROP TABLE IF EXISTS `mydb`.`users` ;
22
23CREATE TABLE IF NOT EXISTS `mydb`.`users` (
24  `user_id` SMALLINT NOT NULL AUTO_INCREMENT,
25  `email` VARCHAR(254) NOT NULL,
26  `password` VARCHAR(120) NOT NULL,
27  `is_admin` TINYINT NOT NULL DEFAULT 0,
28  `created_at` DATETIME NULL,
29  `updated_at` DATETIME NULL,
30  PRIMARY KEY (`user_id`),
31  UNIQUE INDEX `email_UNIQUE` (`email` ASC) VISIBLE)
32ENGINE = InnoDB;
33
34
35-- -----------------------------------------------------
36-- Table `mydb`.`shitposts`
37-- -----------------------------------------------------
38DROP TABLE IF EXISTS `mydb`.`shitposts` ;
39
40CREATE TABLE IF NOT EXISTS `mydb`.`shitposts` (
41  `shitpost_id` MEDIUMINT NOT NULL AUTO_INCREMENT,
42  `text` VARCHAR(150) NOT NULL,
43  `sfw` TINYINT NOT NULL,
44  `is_enabled` TINYINT NOT NULL DEFAULT 0,
45  `created_by` SMALLINT NULL,
46  `created_at` DATETIME NULL,
47  `updated_at` DATETIME NULL,
48  PRIMARY KEY (`shitpost_id`),
49  UNIQUE INDEX `text_UNIQUE` (`text` ASC) VISIBLE,
50  INDEX `fk_shitposts_users2_idx` (`created_by` ASC) VISIBLE,
51  CONSTRAINT `fk_shitposts_users2`
52    FOREIGN KEY (`created_by`)
53    REFERENCES `mydb`.`users` (`user_id`)
54    ON DELETE NO ACTION
55    ON UPDATE NO ACTION)
56ENGINE = InnoDB;
57
58
59-- -----------------------------------------------------
60-- Table `mydb`.`names`
61-- -----------------------------------------------------
62DROP TABLE IF EXISTS `mydb`.`names` ;
63
64CREATE TABLE IF NOT EXISTS `mydb`.`names` (
65  `name_id` MEDIUMINT NOT NULL AUTO_INCREMENT,
66  `name` VARCHAR(80) NOT NULL,
67  `gender` INT NOT NULL,
68  `is_enabled` TINYINT NOT NULL DEFAULT 0,
69  `created_by` SMALLINT NULL,
70  `created_at` DATETIME NULL,
71  `updated_at` DATETIME NULL,
72  PRIMARY KEY (`name_id`),
73  UNIQUE INDEX `name_UNIQUE` (`name` ASC) VISIBLE,
74  INDEX `fk_names_users2_idx` (`created_by` ASC) VISIBLE,
75  CONSTRAINT `fk_names_users2`
76    FOREIGN KEY (`created_by`)
77    REFERENCES `mydb`.`users` (`user_id`)
78    ON DELETE NO ACTION
79    ON UPDATE NO ACTION)
80ENGINE = InnoDB;
81
82
83-- -----------------------------------------------------
84-- Table `mydb`.`shitpost_tags`
85-- -----------------------------------------------------
86DROP TABLE IF EXISTS `mydb`.`shitpost_tags` ;
87
88CREATE TABLE IF NOT EXISTS `mydb`.`shitpost_tags` (
89  `tag_id` SMALLINT NOT NULL AUTO_INCREMENT,
90  `tag` VARCHAR(50) NOT NULL,
91  `sfw` TINYINT NOT NULL,
92  `created_by` SMALLINT NULL,
93  `created_at` DATETIME NULL,
94  `updated_at` DATETIME NULL,
95  PRIMARY KEY (`tag_id`),
96  INDEX `fk_shitpost_tags_users2_idx` (`created_by` ASC) VISIBLE,
97  UNIQUE INDEX `tag_UNIQUE` (`tag` ASC) VISIBLE,
98  CONSTRAINT `fk_shitpost_tags_users2`
99    FOREIGN KEY (`created_by`)
100    REFERENCES `mydb`.`users` (`user_id`)
101    ON DELETE NO ACTION
102    ON UPDATE NO ACTION)
103ENGINE = InnoDB;
104
105
106-- -----------------------------------------------------
107-- Table `mydb`.`name_tags`
108-- -----------------------------------------------------
109DROP TABLE IF EXISTS `mydb`.`name_tags` ;
110
111CREATE TABLE IF NOT EXISTS `mydb`.`name_tags` (
112  `tag_id` SMALLINT NOT NULL AUTO_INCREMENT,
113  `tag` VARCHAR(50) NOT NULL,
114  `sfw` TINYINT NOT NULL,
115  `created_by` SMALLINT NULL,
116  `created_at` DATETIME NULL,
117  `updated_at` DATETIME NULL,
118  PRIMARY KEY (`tag_id`),
119  INDEX `fk_name_tags_users2_idx` (`created_by` ASC) VISIBLE,
120  UNIQUE INDEX `tag_UNIQUE` (`tag` ASC) VISIBLE,
121  CONSTRAINT `fk_name_tags_users2`
122    FOREIGN KEY (`created_by`)
123    REFERENCES `mydb`.`users` (`user_id`)
124    ON DELETE NO ACTION
125    ON UPDATE NO ACTION)
126ENGINE = InnoDB;
127
128
129-- -----------------------------------------------------
130-- Table `mydb`.`log`
131-- -----------------------------------------------------
132DROP TABLE IF EXISTS `mydb`.`log` ;
133
134CREATE TABLE IF NOT EXISTS `mydb`.`log` (
135  `log_id` BIGINT NOT NULL AUTO_INCREMENT,
136  `user` VARCHAR(80) NOT NULL,
137  `action` VARCHAR(10) NOT NULL,
138  `table_name` VARCHAR(20) NOT NULL,
139  `log_time` DATETIME NOT NULL,
140  `data` TEXT NOT NULL,
141  PRIMARY KEY (`log_id`))
142ENGINE = InnoDB;
143
144
145-- -----------------------------------------------------
146-- Table `mydb`.`names_has_name_tags`
147-- -----------------------------------------------------
148DROP TABLE IF EXISTS `mydb`.`names_has_name_tags` ;
149
150CREATE TABLE IF NOT EXISTS `mydb`.`names_has_name_tags` (
151  `names_name_id` MEDIUMINT NOT NULL AUTO_INCREMENT,
152  `name_tags_tag_id` SMALLINT NOT NULL,
153  PRIMARY KEY (`names_name_id`, `name_tags_tag_id`),
154  INDEX `fk_names_has_name_tags_name_tags1_idx` (`name_tags_tag_id` ASC) VISIBLE,
155  INDEX `fk_names_has_name_tags_names1_idx` (`names_name_id` ASC) VISIBLE,
156  CONSTRAINT `fk_names_has_name_tags_names1`
157    FOREIGN KEY (`names_name_id`)
158    REFERENCES `mydb`.`names` (`name_id`)
159    ON DELETE NO ACTION
160    ON UPDATE NO ACTION,
161  CONSTRAINT `fk_names_has_name_tags_name_tags1`
162    FOREIGN KEY (`name_tags_tag_id`)
163    REFERENCES `mydb`.`name_tags` (`tag_id`)
164    ON DELETE NO ACTION
165    ON UPDATE NO ACTION)
166ENGINE = InnoDB;
167
168
169-- -----------------------------------------------------
170-- Table `mydb`.`shitposts_has_shitpost_tags`
171-- -----------------------------------------------------
172DROP TABLE IF EXISTS `mydb`.`shitposts_has_shitpost_tags` ;
173
174CREATE TABLE IF NOT EXISTS `mydb`.`shitposts_has_shitpost_tags` (
175  `shitposts_shitpost_id` MEDIUMINT NOT NULL AUTO_INCREMENT,
176  `shitpost_tags_tag_id` SMALLINT NOT NULL,
177  PRIMARY KEY (`shitposts_shitpost_id`, `shitpost_tags_tag_id`),
178  INDEX `fk_shitposts_has_shitpost_tags_shitpost_tags1_idx` (`shitpost_tags_tag_id` ASC) VISIBLE,
179  INDEX `fk_shitposts_has_shitpost_tags_shitposts1_idx` (`shitposts_shitpost_id` ASC) VISIBLE,
180  CONSTRAINT `fk_shitposts_has_shitpost_tags_shitposts1`
181    FOREIGN KEY (`shitposts_shitpost_id`)
182    REFERENCES `mydb`.`shitposts` (`shitpost_id`)
183    ON DELETE NO ACTION
184    ON UPDATE NO ACTION,
185  CONSTRAINT `fk_shitposts_has_shitpost_tags_shitpost_tags1`
186    FOREIGN KEY (`shitpost_tags_tag_id`)
187    REFERENCES `mydb`.`shitpost_tags` (`tag_id`)
188    ON DELETE NO ACTION
189    ON UPDATE NO ACTION)
190ENGINE = InnoDB;
191
192USE `mydb` ;
193
194-- -----------------------------------------------------
195-- Placeholder table for view `mydb`.`sfw_shitposts`
196-- -----------------------------------------------------
197CREATE TABLE IF NOT EXISTS `mydb`.`sfw_shitposts` (`text` INT);
198
199-- -----------------------------------------------------
200-- Placeholder table for view `mydb`.`nsfw_shitposts`
201-- -----------------------------------------------------
202CREATE TABLE IF NOT EXISTS `mydb`.`nsfw_shitposts` (`text` INT);
203
204-- -----------------------------------------------------
205-- procedure SelectShitpostsWithoutTags
206-- -----------------------------------------------------
207
208USE `mydb`;
209DROP procedure IF EXISTS `mydb`.`SelectShitpostsWithoutTags`;
210
211DELIMITER $$
212USE `mydb`$$
213CREATE PROCEDURE `SelectShitpostsWithoutTags`()
214BEGIN
215	SELECT shitpost_id FROM shitposts WHERE NOT EXISTS (SELECT shitposts_shitpost_id FROM shitposts_has_shitpost_tags as tags WHERE tags.shitposts_shitpost_id = shitposts.shitpost_id);
216END;$$
217
218DELIMITER ;
219
220-- -----------------------------------------------------
221-- procedure SelectNamesWithoutTags
222-- -----------------------------------------------------
223
224USE `mydb`;
225DROP procedure IF EXISTS `mydb`.`SelectNamesWithoutTags`;
226
227DELIMITER $$
228USE `mydb`$$
229CREATE PROCEDURE `SelectNamesWithoutTags`()
230BEGIN
231	SELECT name_id FROM names WHERE NOT EXISTS (SELECT names_name_id FROM names_has_name_tags as tags WHERE tags.names_name_id = names.name_id);
232END;$$
233
234DELIMITER ;
235
236-- -----------------------------------------------------
237-- function users_title
238-- -----------------------------------------------------
239
240USE `mydb`;
241DROP function IF EXISTS `mydb`.`users_title`;
242
243DELIMITER $$
244USE `mydb`$$
245CREATE FUNCTION users_title(user_id SMALLINT) RETURNS VARCHAR(20) DETERMINISTIC
246BEGIN
247    DECLARE title VARCHAR(20);
248    DECLARE shitpost_count SMALLINT;
249    SET title = 'newbiw';
250SELECT 
251    COUNT(shitpost_id)
252FROM
253    shitposts
254WHERE
255    users.user_id = user_id;
256    IF shitpost_count = 0 THEN
257        SET title = 'newbie';
258    ELSEIF shitpost_count < 10 THEN
259        SET title = 'beginner';
260    ELSEIF shitpost_count < 50 THEN
261        SET title = 'Worthy';
262    ELSEIF shitpost_count > 50 THEN
263        SET title = 'Chosen';
264    END IF;
265    RETURN title;
266end;$$
267
268DELIMITER ;
269
270-- -----------------------------------------------------
271-- View `mydb`.`sfw_shitposts`
272-- -----------------------------------------------------
273DROP TABLE IF EXISTS `mydb`.`sfw_shitposts`;
274DROP VIEW IF EXISTS `mydb`.`sfw_shitposts` ;
275USE `mydb`;
276CREATE  OR REPLACE VIEW `sfw_shitposts` AS
277    SELECT 
278        text
279    FROM
280        shitposts
281    WHERE
282        sfw = TRUE;
283
284-- -----------------------------------------------------
285-- View `mydb`.`nsfw_shitposts`
286-- -----------------------------------------------------
287DROP TABLE IF EXISTS `mydb`.`nsfw_shitposts`;
288DROP VIEW IF EXISTS `mydb`.`nsfw_shitposts` ;
289USE `mydb`;
290CREATE  OR REPLACE VIEW `nsfw_shitposts` AS
291    SELECT 
292        text
293    FROM
294        shitposts
295    WHERE
296        sfw = FALSE;
297USE `mydb`;
298
299DELIMITER $$
300
301USE `mydb`$$
302DROP TRIGGER IF EXISTS `mydb`.`users_AFTER_INSERT` $$
303USE `mydb`$$
304CREATE DEFINER = CURRENT_USER TRIGGER `mydb`.`users_AFTER_INSERT` AFTER INSERT ON `users` FOR EACH ROW
305BEGIN
306 INSERT INTO log(user_id, action, table_name, log_time, data)
307    VALUES (USER(),
308            'insert',
309            'user',
310            CURRENT_TIME(6),
311            CONCAT(NEW.email, ' | ', NEW.password, ' | ', NEW.is_admin));
312END$$
313
314
315USE `mydb`$$
316DROP TRIGGER IF EXISTS `mydb`.`users_BEFORE_UPDATE` $$
317USE `mydb`$$
318CREATE DEFINER = CURRENT_USER TRIGGER `mydb`.`users_BEFORE_UPDATE` BEFORE UPDATE ON `users` FOR EACH ROW
319BEGIN
320  INSERT INTO log(user_id, action, table_name, log_time, data)
321    VALUES (USER(),
322            'update',
323            'user',
324            CURRENT_TIME(6),
325            CONCAT(NEW.email, ' | ', NEW.password, ' | ', NEW.is_admin));
326END$$
327
328
329USE `mydb`$$
330DROP TRIGGER IF EXISTS `mydb`.`users_BEFORE_DELETE` $$
331USE `mydb`$$
332CREATE DEFINER = CURRENT_USER TRIGGER `mydb`.`users_BEFORE_DELETE` BEFORE DELETE ON `users` FOR EACH ROW
333BEGIN
334 INSERT INTO log(user_id, action, table_name, log_time, data)
335    VALUES (USER(),
336            'delete',
337            'user',
338            CURRENT_TIME(6),
339            CONCAT(OLD.email, ' | ', OLD.password, ' | ', OLD.is_admin));
340END$$
341
342
343DELIMITER ;
344
345SET SQL_MODE=@OLD_SQL_MODE;
346SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
347SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
348