bV8viszu

1SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
2SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
3SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES';
4
5CREATE SCHEMA IF NOT EXISTS `pxandcode` DEFAULT CHARACTER SET utf8 DEFAULT COLLATE utf8_general_ci;
6USE `pxandcode` ;
7
8CREATE TABLE IF NOT EXISTS `pxandcode`.`categorias` (
9  `id` INT(4) NOT NULL AUTO_INCREMENT,
10  `nombre` VARCHAR(60) NOT NULL,
11  PRIMARY KEY (`id`))
12ENGINE = InnoDB;
13
14CREATE TABLE IF NOT EXISTS `pxandcode`.`estados_com` (
15  `id` INT(4) NOT NULL AUTO_INCREMENT,
16  `nombre` VARCHAR(20) NOT NULL,
17  PRIMARY KEY (`id`))
18ENGINE = InnoDB;
19
20CREATE TABLE IF NOT EXISTS `pxandcode`.`estados_usu` (
21  `id` INT(4) NOT NULL AUTO_INCREMENT,
22  `nombre` VARCHAR(20) NOT NULL,
23  PRIMARY KEY (`id`))
24ENGINE = InnoDB;
25
26
27CREATE TABLE IF NOT EXISTS `pxandcode`.`tipos_usu` (
28  `id` INT(4) NOT NULL AUTO_INCREMENT,
29  `nombre` VARCHAR(20) NOT NULL,
30  PRIMARY KEY (`id`))
31ENGINE = InnoDB;
32
33
34CREATE TABLE IF NOT EXISTS `pxandcode`.`usuarios` (
35  `id` INT(4) NOT NULL AUTO_INCREMENT,
36  `estado_usu_id` INT(4) NOT NULL,
37  `tipo_usu_id` INT(4) NOT NULL,
38  `email` VARCHAR(60) NOT NULL,
39  `password` VARCHAR(41) NOT NULL,
40  `nombre` VARCHAR(60) NOT NULL,
41  `apodo` VARCHAR(20) NOT NULL,
42  `imagen` VARCHAR(200) NOT NULL DEFAULT 'img/perfil/blank.png',
43  `modificado` DATETIME NOT NULL,
44  `creado` DATETIME NOT NULL,
45  PRIMARY KEY (`id`),
46  CONSTRAINT `usuario_estado`
47    FOREIGN KEY (`estado_usu_id`)
48    REFERENCES `pxandcode`.`estados_usu` (`id`)
49    ON DELETE NO ACTION
50    ON UPDATE NO ACTION,
51  CONSTRAINT `usuario_tipo`
52    FOREIGN KEY (`tipo_usu_id`)
53    REFERENCES `pxandcode`.`tipos_usu` (`id`)
54    ON DELETE NO ACTION
55    ON UPDATE NO ACTION)
56ENGINE = InnoDB;
57
58
59CREATE TABLE IF NOT EXISTS `pxandcode`.`tipos_posts` (
60  `id` INT(4) NOT NULL AUTO_INCREMENT,
61  `nombre` VARCHAR(20) NOT NULL,
62  PRIMARY KEY (`id`))
63ENGINE = InnoDB;
64
65CREATE TABLE IF NOT EXISTS `pxandcode`.`posts` (
66  `id` INT(4) NOT NULL AUTO_INCREMENT,
67  `usuario_id` INT(4) NOT NULL,
68  `categoria_id` INT(4) NOT NULL,
69  `tipo_id` INT(4) NOT NULL,
70  `titulo` VARCHAR(60) NOT NULL,
71  `contenido` TEXT(2000) NOT NULL,
72  `descripcion` VARCHAR(200) NULL,
73  `modificado` DATETIME NOT NULL,
74  `creado` DATETIME NOT NULL,
75  PRIMARY KEY (`id`),
76  CONSTRAINT `post_usuario_fk`
77    FOREIGN KEY (`usuario_id`)
78    REFERENCES `pxandcode`.`usuarios` (`id`)
79    ON DELETE NO ACTION
80    ON UPDATE NO ACTION,
81  CONSTRAINT `post_categoria_fk`
82    FOREIGN KEY (`categoria_id`)
83    REFERENCES `pxandcode`.`categorias` (`id`)
84    ON DELETE NO ACTION
85    ON UPDATE NO ACTION,
86  CONSTRAINT `post_tipo_fk`
87    FOREIGN KEY (`tipo_id`)
88    REFERENCES `pxandcode`.`tipos_posts` (`id`)
89    ON DELETE NO ACTION
90    ON UPDATE NO ACTION)
91ENGINE = InnoDB;
92
93CREATE TABLE IF NOT EXISTS `pxandcode`.`comentarios` (
94  `id` INT(4) NOT NULL AUTO_INCREMENT,
95  `usuario_id` INT(4) NOT NULL,
96  `post_id` INT(4) NOT NULL,
97  `estado_com_id` INT(4) NOT NULL,
98  `data` TEXT(1000) NOT NULL,
99  `modificado` DATETIME NOT NULL,
100  `creado` DATETIME NOT NULL,
101  PRIMARY KEY (`id`),
102  CONSTRAINT `comentario_estado_fk`
103    FOREIGN KEY (`estado_com_id`)
104    REFERENCES `pxandcode`.`estados_com` (`id`),
105  CONSTRAINT `comentario_usuario_fk`
106    FOREIGN KEY (`usuario_id`)
107    REFERENCES `pxandcode`.`usuarios` (`id`),
108  CONSTRAINT `comentario_post_fk`
109    FOREIGN KEY (`post_id`)
110    REFERENCES `pxandcode`.`posts` (`id`)
111    ON DELETE CASCADE
112    ON UPDATE NO ACTION)
113ENGINE = InnoDB;
114
115SET SQL_MODE=@OLD_SQL_MODE;
116SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
117SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;