UVLB2TSv

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='TRADITIONAL,ALLOW_INVALID_DATES';
6
7-- -----------------------------------------------------
8-- Schema wypok
9-- -----------------------------------------------------
10
11-- -----------------------------------------------------
12-- Schema wypok
13-- -----------------------------------------------------
14CREATE SCHEMA IF NOT EXISTS `wypok` DEFAULT CHARACTER SET utf8 ;
15USE `wypok` ;
16
17-- -----------------------------------------------------
18-- Table `wypok`.`user`
19-- -----------------------------------------------------
20CREATE TABLE IF NOT EXISTS `wypok`.`user` (
21  `user_id` INT NOT NULL AUTO_INCREMENT,
22  `username` VARCHAR(45) NOT NULL,
23  `email` VARCHAR(60) NOT NULL,
24  `is_active` TINYINT(1) NOT NULL,
25  `password` VARCHAR(45) NOT NULL,
26  PRIMARY KEY (`user_id`),
27  UNIQUE INDEX `user_id_UNIQUE` (`user_id` ASC),
28  UNIQUE INDEX `username_UNIQUE` (`username` ASC),
29  UNIQUE INDEX `email_UNIQUE` (`email` ASC))
30ENGINE = InnoDB;
31
32
33-- -----------------------------------------------------
34-- Table `wypok`.`discovery`
35-- -----------------------------------------------------
36CREATE TABLE IF NOT EXISTS `wypok`.`discovery` (
37  `discovery_id` INT NOT NULL AUTO_INCREMENT,
38  `name` VARCHAR(100) NOT NULL,
39  `description` VARCHAR(250) NOT NULL,
40  `url` VARCHAR(200) NOT NULL,
41  `user_id` INT NOT NULL,
42  `date` TIMESTAMP NOT NULL,
43  `up_vote` INT NOT NULL,
44  `down_vote` INT NOT NULL,
45  PRIMARY KEY (`discovery_id`, `user_id`),
46  UNIQUE INDEX `discovery_id_UNIQUE` (`discovery_id` ASC),
47  UNIQUE INDEX `url_UNIQUE` (`url` ASC),
48  INDEX `fk_discovery_user_idx` (`user_id` ASC),
49  CONSTRAINT `fk_discovery_user`
50    FOREIGN KEY (`user_id`)
51    REFERENCES `wypok`.`user` (`user_id`)
52    ON DELETE NO ACTION
53    ON UPDATE NO ACTION)
54ENGINE = InnoDB;
55
56
57-- -----------------------------------------------------
58-- Table `wypok`.`role`
59-- -----------------------------------------------------
60CREATE TABLE IF NOT EXISTS `wypok`.`role` (
61  `role_name` VARCHAR(45) NOT NULL,
62  `description` VARCHAR(120) NULL,
63  PRIMARY KEY (`role_name`),
64  UNIQUE INDEX `role_name_UNIQUE` (`role_name` ASC))
65ENGINE = InnoDB;
66
67
68-- -----------------------------------------------------
69-- Table `wypok`.`vote`
70-- -----------------------------------------------------
71CREATE TABLE IF NOT EXISTS `wypok`.`vote` (
72  `vote_id` INT NOT NULL AUTO_INCREMENT,
73  `discovery_id` INT NOT NULL,
74  `user_id` INT NOT NULL,
75  `date` TIMESTAMP NOT NULL,
76  `type` VARCHAR(30) NOT NULL,
77  PRIMARY KEY (`vote_id`, `discovery_id`, `user_id`),
78  INDEX `fk_user_has_discovery_discovery1_idx` (`discovery_id` ASC),
79  INDEX `fk_user_has_discovery_user1_idx` (`user_id` ASC),
80  CONSTRAINT `fk_user_has_discovery_user1`
81    FOREIGN KEY (`user_id`)
82    REFERENCES `wypok`.`user` (`user_id`)
83    ON DELETE NO ACTION
84    ON UPDATE NO ACTION,
85  CONSTRAINT `fk_user_has_discovery_discovery1`
86    FOREIGN KEY (`discovery_id`)
87    REFERENCES `wypok`.`discovery` (`discovery_id`)
88    ON DELETE NO ACTION
89    ON UPDATE NO ACTION)
90ENGINE = InnoDB;
91
92
93-- -----------------------------------------------------
94-- Table `wypok`.`user_role`
95-- -----------------------------------------------------
96CREATE TABLE IF NOT EXISTS `wypok`.`user_role` (
97  `role_name` VARCHAR(45) NOT NULL,
98  `username` VARCHAR(45) NOT NULL,
99  PRIMARY KEY (`role_name`, `username`),
100  INDEX `fk_role_has_user_role1_idx` (`role_name` ASC),
101  INDEX `fk_user_role_user_username_idx` (`username` ASC),
102  CONSTRAINT `fk_role_has_user_role1`
103    FOREIGN KEY (`role_name`)
104    REFERENCES `wypok`.`role` (`role_name`)
105    ON DELETE NO ACTION
106    ON UPDATE NO ACTION,
107  CONSTRAINT `fk_user_role_user_username`
108    FOREIGN KEY (`username`)
109    REFERENCES `wypok`.`user` (`username`)
110    ON DELETE NO ACTION
111    ON UPDATE NO ACTION)
112ENGINE = InnoDB;
113
114
115SET SQL_MODE=@OLD_SQL_MODE;
116SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
117SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;