ctcrPVL7

1-- MySQL Script generated by MySQL Workbench
2-- 01/24/17 10:10:26
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 feiten
12-- -----------------------------------------------------
13
14-- -----------------------------------------------------
15-- Schema feiten
16-- -----------------------------------------------------
17CREATE SCHEMA IF NOT EXISTS `feiten` DEFAULT CHARACTER SET utf8 ;
18USE `feiten` ;
19
20-- -----------------------------------------------------
21-- Table `feiten`.`groups`
22-- -----------------------------------------------------
23CREATE TABLE IF NOT EXISTS `feiten`.`groups` (
24  `id` MEDIUMINT(8) UNSIGNED NOT NULL AUTO_INCREMENT,
25  `name` VARCHAR(20) NOT NULL,
26  `description` VARCHAR(100) NOT NULL,
27  PRIMARY KEY (`id`))
28ENGINE = InnoDB
29DEFAULT CHARACTER SET = utf8;
30
31
32-- -----------------------------------------------------
33-- Table `feiten`.`users`
34-- -----------------------------------------------------
35CREATE TABLE IF NOT EXISTS `feiten`.`users` (
36  `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
37  `ip_address` VARCHAR(45) NOT NULL,
38  `username` VARCHAR(100) NULL DEFAULT NULL,
39  `password` VARCHAR(255) NOT NULL,
40  `salt` VARCHAR(255) NULL DEFAULT NULL,
41  `email` VARCHAR(100) NOT NULL,
42  `activation_code` VARCHAR(40) NULL DEFAULT NULL,
43  `forgotten_password_code` VARCHAR(40) NULL DEFAULT NULL,
44  `forgotten_password_time` INT(11) UNSIGNED NULL DEFAULT NULL,
45  `remember_code` VARCHAR(40) NULL DEFAULT NULL,
46  `created_on` INT(11) UNSIGNED NOT NULL,
47  `last_login` INT(11) UNSIGNED NULL DEFAULT NULL,
48  `active` TINYINT(1) UNSIGNED NULL DEFAULT NULL,
49  `first_name` VARCHAR(50) NULL DEFAULT NULL,
50  `last_name` VARCHAR(50) NULL DEFAULT NULL,
51  `company` VARCHAR(100) NULL DEFAULT NULL,
52  `phone` VARCHAR(20) NULL DEFAULT NULL,
53  PRIMARY KEY (`id`))
54ENGINE = InnoDB
55DEFAULT CHARACTER SET = utf8;
56
57
58-- -----------------------------------------------------
59-- Table `feiten`.`users_groups`
60-- -----------------------------------------------------
61CREATE TABLE IF NOT EXISTS `feiten`.`users_groups` (
62  `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
63  `user_id` INT(11) UNSIGNED NOT NULL,
64  `group_id` MEDIUMINT(8) UNSIGNED NOT NULL,
65  PRIMARY KEY (`id`),
66  INDEX `fk_users_groups_users1_idx` (`user_id` ASC),
67  INDEX `fk_users_groups_groups1_idx` (`group_id` ASC),
68  UNIQUE INDEX `uc_users_groups` ( ASC, `group_id` ASC),
69  CONSTRAINT `fk_users_groups_users1`
70    FOREIGN KEY (`user_id`)
71    REFERENCES `feiten`.`users` (`id`)
72    ON DELETE CASCADE
73    ON UPDATE NO ACTION,
74  CONSTRAINT `fk_users_groups_groups1`
75    FOREIGN KEY (`group_id`)
76    REFERENCES `feiten`.`groups` (`id`)
77    ON DELETE CASCADE
78    ON UPDATE NO ACTION)
79ENGINE = InnoDB
80DEFAULT CHARACTER SET = utf8;
81
82
83-- -----------------------------------------------------
84-- Table `feiten`.`login_attempts`
85-- -----------------------------------------------------
86CREATE TABLE IF NOT EXISTS `feiten`.`login_attempts` (
87  `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
88  `ip_address` VARCHAR(15) NOT NULL,
89  `login` VARCHAR(100) NOT NULL,
90  `time` INT(11) UNSIGNED NULL DEFAULT NULL,
91  PRIMARY KEY (`id`))
92ENGINE = InnoDB
93DEFAULT CHARACTER SET = utf8;
94
95
96-- -----------------------------------------------------
97-- Table `feiten`.`editorials`
98-- -----------------------------------------------------
99CREATE TABLE IF NOT EXISTS `feiten`.`editorials` (
100  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
101  `title` VARCHAR(200) NOT NULL,
102  `content` VARCHAR(700) NOT NULL,
103  `date_time` DATETIME NOT NULL,
104  `ready_to_publish` TINYINT(1) NULL DEFAULT 0,
105  `author_id` INT(11) UNSIGNED NOT NULL,
106  `reviewer_id` INT(11) UNSIGNED NULL DEFAULT NULL,
107  PRIMARY KEY (`id`, `author_id`),
108  INDEX `fk_editorials_users1_idx` (`author_id` ASC),
109  INDEX `fk_editorials_users2_idx` (`reviewer_id` ASC),
110  CONSTRAINT `fk_editorials_users1`
111    FOREIGN KEY (`author_id`)
112    REFERENCES `feiten`.`users` (`id`)
113    ON DELETE NO ACTION
114    ON UPDATE NO ACTION,
115  CONSTRAINT `fk_editorials_users2`
116    FOREIGN KEY (`reviewer_id`)
117    REFERENCES `feiten`.`users` (`id`)
118    ON DELETE NO ACTION
119    ON UPDATE NO ACTION)
120ENGINE = InnoDB
121DEFAULT CHARACTER SET = utf8;
122
123
124SET SQL_MODE=@OLD_SQL_MODE;
125SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
126SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;