T0PZxFat

1```
2-- MySQL Script generated by MySQL Workbench
3-- Thu Apr 12 14:46:20 2018
4-- Model: New Model    Version: 1.0
5-- MySQL Workbench Forward Engineering
6
7SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
8SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
9SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES';
10
11-- -----------------------------------------------------
12-- Schema tdb
13-- -----------------------------------------------------
14
15-- -----------------------------------------------------
16-- Schema tdb
17-- -----------------------------------------------------
18CREATE SCHEMA IF NOT EXISTS `tdb` DEFAULT CHARACTER SET utf8 ;
19USE `tdb` ;
20
21-- -----------------------------------------------------
22-- Table `tdb`.`user_roles`
23-- -----------------------------------------------------
24CREATE TABLE IF NOT EXISTS `tdb`.`user_roles` (
25  `URID` INT NOT NULL AUTO_INCREMENT,
26  `name` CHAR(49) NULL,
27  PRIMARY KEY (`URID`),
28  UNIQUE INDEX `URID_UNIQUE` (`URID` ASC))
29ENGINE = InnoDB;
30
31
32-- -----------------------------------------------------
33-- Table `tdb`.`countries`
34-- -----------------------------------------------------
35CREATE TABLE IF NOT EXISTS `tdb`.`countries` (
36  `country_code` CHAR(2) NOT NULL,
37  `country_name` CHAR(100) NOT NULL,
38  PRIMARY KEY (`country_code`))
39ENGINE = InnoDB
40DEFAULT CHARACTER SET = utf8;
41
42
43-- -----------------------------------------------------
44-- Table `tdb`.`languages`
45-- -----------------------------------------------------
46CREATE TABLE IF NOT EXISTS `tdb`.`languages` (
47  `locale_code` CHAR(10) NOT NULL,
48  `name` CHAR(64) NOT NULL,
49  PRIMARY KEY (`locale_code`),
50  UNIQUE INDEX `locale_code_UNIQUE` (`locale_code` ASC))
51ENGINE = InnoDB;
52
53
54-- -----------------------------------------------------
55-- Table `tdb`.`users`
56-- -----------------------------------------------------
57CREATE TABLE IF NOT EXISTS `tdb`.`users` (
58  `UID` INT NOT NULL AUTO_INCREMENT,
59  `email` CHAR(255) NOT NULL,
60  `username` CHAR(64) NOT NULL,
61  `password` CHAR(255) NOT NULL,
62  `user_role` INT NOT NULL DEFAULT 2,
63  `primary_language` CHAR(15) NOT NULL,
64  `additional_languages` CHAR(255) NULL DEFAULT NULL,
65  `programming_languages` CHAR(255) NULL DEFAULT NULL,
66  `regtime` DATETIME NOT NULL,
67  `lastaccess` DATETIME NOT NULL,
68  `SID` CHAR(50) NULL,
69  `activationhash` CHAR(15) NULL,
70  `country` CHAR(2) NULL DEFAULT NULL,
71  PRIMARY KEY (`UID`),
72  UNIQUE INDEX `Username_UNIQUE` (`username` ASC),
73  UNIQUE INDEX `UID_UNIQUE` (`UID` ASC),
74  INDEX `LID_idx` (`primary_language` ASC),
75  UNIQUE INDEX `email_UNIQUE` (`email` ASC),
76  INDEX `URID_idx` (`user_role` ASC),
77  INDEX `country_code_idx` (`country` ASC),
78  CONSTRAINT `URID`
79    FOREIGN KEY (`user_role`)
80    REFERENCES `tdb`.`user_roles` (`URID`)
81    ON DELETE NO ACTION
82    ON UPDATE NO ACTION,
83  CONSTRAINT `country_code`
84    FOREIGN KEY (`country`)
85    REFERENCES `tdb`.`countries` (`country_code`)
86    ON DELETE NO ACTION
87    ON UPDATE NO ACTION,
88  CONSTRAINT `locale_code`
89    FOREIGN KEY (`primary_language`)
90    REFERENCES `tdb`.`languages` (`locale_code`)
91    ON DELETE NO ACTION
92    ON UPDATE NO ACTION)
93ENGINE = InnoDB;
94
95
96-- -----------------------------------------------------
97-- Table `tdb`.`programming_languages`
98-- -----------------------------------------------------
99CREATE TABLE IF NOT EXISTS `tdb`.`programming_languages` (
100  `PLID` INT NOT NULL AUTO_INCREMENT,
101  `name` CHAR(49) NULL DEFAULT NULL,
102  PRIMARY KEY (`PLID`),
103  UNIQUE INDEX `name_UNIQUE` (`name` ASC),
104  UNIQUE INDEX `PLID_UNIQUE` (`PLID` ASC))
105ENGINE = InnoDB;
106
107
108SET SQL_MODE=@OLD_SQL_MODE;
109SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
110SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
111
112 ```