· 9 years ago · Oct 17, 2016, 09:16 PM
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 mydb
9-- -----------------------------------------------------
10-- -----------------------------------------------------
11-- Schema blog
12-- -----------------------------------------------------
13
14-- -----------------------------------------------------
15-- Schema blog
16-- -----------------------------------------------------
17CREATE SCHEMA IF NOT EXISTS `blog` DEFAULT CHARACTER SET utf8 ;
18USE `blog` ;
19
20-- -----------------------------------------------------
21-- Table `blog`.`authors`
22-- -----------------------------------------------------
23CREATE TABLE IF NOT EXISTS `blog`.`authors` (
24 `id` INT(11) NOT NULL AUTO_INCREMENT,
25 `name` VARCHAR(45) NULL DEFAULT NULL,
26 `perfil` TEXT NULL DEFAULT NULL,
27 `birth_date` VARCHAR(45) NULL DEFAULT NULL,
28 `created` DATETIME NULL DEFAULT CURRENT_TIMESTAMP,
29 `modified` DATETIME NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
30 PRIMARY KEY (`id`))
31ENGINE = InnoDB
32AUTO_INCREMENT = 3
33DEFAULT CHARACTER SET = utf8;
34
35
36-- -----------------------------------------------------
37-- Table `blog`.`users`
38-- -----------------------------------------------------
39CREATE TABLE IF NOT EXISTS `blog`.`users` (
40 `id` INT(11) NOT NULL AUTO_INCREMENT,
41 `username` VARCHAR(45) NULL DEFAULT NULL,
42 `password` VARCHAR(255) NULL DEFAULT NULL,
43 `role` VARCHAR(20) NULL DEFAULT NULL,
44 `photo` VARCHAR(255) NULL DEFAULT NULL,
45 `photo_dir` VARCHAR(255) NULL DEFAULT NULL,
46 `created` DATETIME NULL DEFAULT CURRENT_TIMESTAMP,
47 `modified` DATETIME NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
48 PRIMARY KEY (`id`))
49ENGINE = InnoDB
50AUTO_INCREMENT = 2
51DEFAULT CHARACTER SET = utf8;
52
53
54-- -----------------------------------------------------
55-- Table `blog`.`articles`
56-- -----------------------------------------------------
57CREATE TABLE IF NOT EXISTS `blog`.`articles` (
58 `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
59 `title` VARCHAR(50) NULL DEFAULT NULL,
60 `body` TEXT NULL DEFAULT NULL,
61 `created` DATETIME NULL DEFAULT NULL,
62 `modified` DATETIME NULL DEFAULT NULL,
63 `authors_id` INT(11) NOT NULL,
64 `user_id` INT(11) NOT NULL,
65 PRIMARY KEY (`id`),
66 INDEX `fk_articles_authors_idx` (`authors_id` ASC),
67 INDEX `fk_articles_users1_idx` (`user_id` ASC),
68 CONSTRAINT `fk_articles_authors`
69 FOREIGN KEY (`authors_id`)
70 REFERENCES `blog`.`authors` (`id`)
71 ON DELETE NO ACTION
72 ON UPDATE NO ACTION,
73 CONSTRAINT `fk_articles_users1`
74 FOREIGN KEY (`user_id`)
75 REFERENCES `blog`.`users` (`id`)
76 ON DELETE NO ACTION
77 ON UPDATE NO ACTION)
78ENGINE = InnoDB
79AUTO_INCREMENT = 15
80DEFAULT CHARACTER SET = utf8;
81
82
83-- -----------------------------------------------------
84-- Table `blog`.`subjects`
85-- -----------------------------------------------------
86CREATE TABLE IF NOT EXISTS `blog`.`subjects` (
87 `id` INT(11) NOT NULL AUTO_INCREMENT,
88 `description` VARCHAR(45) NULL DEFAULT NULL,
89 `created` DATETIME NULL DEFAULT CURRENT_TIMESTAMP,
90 `modified` DATETIME NULL DEFAULT NULL,
91 PRIMARY KEY (`id`))
92ENGINE = InnoDB
93DEFAULT CHARACTER SET = utf8;
94
95
96-- -----------------------------------------------------
97-- Table `blog`.`authors_subjects`
98-- -----------------------------------------------------
99CREATE TABLE IF NOT EXISTS `blog`.`authors_subjects` (
100 `authors_id` INT(11) NOT NULL,
101 `subjects_id` INT(11) NOT NULL,
102 PRIMARY KEY (`authors_id`, `subjects_id`),
103 INDEX `fk_authors_has_subjects_subjects1_idx` (`subjects_id` ASC),
104 INDEX `fk_authors_has_subjects_authors1_idx` (`authors_id` ASC),
105 CONSTRAINT `fk_authors_has_subjects_authors1`
106 FOREIGN KEY (`authors_id`)
107 REFERENCES `blog`.`authors` (`id`)
108 ON DELETE NO ACTION
109 ON UPDATE NO ACTION,
110 CONSTRAINT `fk_authors_has_subjects_subjects1`
111 FOREIGN KEY (`subjects_id`)
112 REFERENCES `blog`.`subjects` (`id`)
113 ON DELETE NO ACTION
114 ON UPDATE NO ACTION)
115ENGINE = InnoDB
116DEFAULT CHARACTER SET = utf8;
117
118
119-- -----------------------------------------------------
120-- Table `blog`.`comments`
121-- -----------------------------------------------------
122CREATE TABLE IF NOT EXISTS `blog`.`comments` (
123 `id` INT(11) NOT NULL AUTO_INCREMENT,
124 `name` VARCHAR(45) NULL DEFAULT NULL,
125 `text` TEXT NULL DEFAULT NULL,
126 `created` DATETIME NULL DEFAULT CURRENT_TIMESTAMP,
127 `modified` DATETIME NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
128 `articles_id` INT(10) UNSIGNED NOT NULL,
129 PRIMARY KEY (`id`),
130 INDEX `fk_comments_articles1_idx` (`articles_id` ASC),
131 CONSTRAINT `fk_comments_articles1`
132 FOREIGN KEY (`articles_id`)
133 REFERENCES `blog`.`articles` (`id`)
134 ON DELETE NO ACTION
135 ON UPDATE NO ACTION)
136ENGINE = InnoDB
137DEFAULT CHARACTER SET = utf8;
138
139
140-- -----------------------------------------------------
141-- Table `blog`.`tags`
142-- -----------------------------------------------------
143CREATE TABLE IF NOT EXISTS `blog`.`tags` (
144 `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
145 `description` VARCHAR(45) NULL DEFAULT NULL,
146 `created` DATETIME NULL DEFAULT CURRENT_TIMESTAMP,
147 `modified` DATETIME NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
148 PRIMARY KEY (`id`))
149ENGINE = InnoDB
150AUTO_INCREMENT = 3
151DEFAULT CHARACTER SET = utf8;
152
153
154-- -----------------------------------------------------
155-- Table `blog`.`tags_articles`
156-- -----------------------------------------------------
157CREATE TABLE IF NOT EXISTS `blog`.`tags_articles` (
158 `tags_id` INT(10) UNSIGNED NOT NULL,
159 `articles_id` INT(10) UNSIGNED NOT NULL,
160 PRIMARY KEY (`tags_id`, `articles_id`),
161 INDEX `fk_tags_has_articles_articles1_idx` (`articles_id` ASC),
162 INDEX `fk_tags_has_articles_tags1_idx` (`tags_id` ASC),
163 CONSTRAINT `fk_tags_has_articles_articles1`
164 FOREIGN KEY (`articles_id`)
165 REFERENCES `blog`.`articles` (`id`)
166 ON DELETE NO ACTION
167 ON UPDATE NO ACTION,
168 CONSTRAINT `fk_tags_has_articles_tags1`
169 FOREIGN KEY (`tags_id`)
170 REFERENCES `blog`.`tags` (`id`)
171 ON DELETE NO ACTION
172 ON UPDATE NO ACTION)
173ENGINE = InnoDB
174DEFAULT CHARACTER SET = utf8;
175
176
177SET SQL_MODE=@OLD_SQL_MODE;
178SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
179SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;