jqSAdHgd

1CREATE DATABASE IF NOT EXISTS zblog;
2USE zblog;
3
4CREATE TABLE IF NOT EXISTS zblog.categoria(
5  id INT NOT NULL AUTO_INCREMENT,
6  id_padre INT NOT NULL,
7  nombre_categoria VARCHAR(50) NOT NULL,
8  estado ENUM('0','1') NOT NULL DEFAULT '1',
9  CONSTRAINT pk_idcategoria PRIMARY KEY (id)
10)
11ENGINE = INNODB
12COLLATE = utf8_spanish2_ci;
13
14CREATE TABLE IF NOT EXISTS zblog.tipo(
15  id INT NOT NULL AUTO_INCREMENT,
16  nombre_tipo VARCHAR(50) NOT NULL,
17  estado ENUM('0','1') NOT NULL DEFAULT '1',
18  CONSTRAINT pk_idtipo PRIMARY KEY (id)
19)
20ENGINE = INNODB
21COLLATE = utf8_spanish2_ci;
22
23CREATE TABLE IF NOT EXISTS zblog.tipo_referencia(
24  id INT NOT NULL AUTO_INCREMENT,
25  nombre_referencia VARCHAR(50) NOT NULL,
26  estado ENUM('0','1') NOT NULL DEFAULT '1',
27  CONSTRAINT pk_idtiporeferencia PRIMARY KEY (id)
28)
29ENGINE = INNODB
30COLLATE = utf8_spanish2_ci;
31
32CREATE TABLE IF NOT EXISTS zblog.permiso(
33  id INT NOT NULL AUTO_INCREMENT,
34  nombre_permiso VARCHAR(50) NOT NULL,
35  CONSTRAINT pk_idpermisos PRIMARY KEY (id)
36)
37ENGINE = INNODB
38COLLATE = utf8_spanish2_ci;
39
40CREATE TABLE IF NOT EXISTS zblog.usuario (
41  id INT NOT NULL AUTO_INCREMENT,
42  role VARCHAR(20) NOT NULL,
43  name VARCHAR(100) NOT NULL,
44  surname VARCHAR(200) NOT NULL,
45  nick VARCHAR(200) NOT NULL,
46  email VARCHAR(255) NOT NULL,
47  password VARCHAR(255) NOT NULL,
48  image VARCHAR(255) NULL DEFAULT 'default_user.png',
49  created_at DATETIME NOT NULL,
50  updated_at DATETIME NOT NULL,
51  remember_token VARCHAR(255) NULL DEFAULT NULL,
52  estado ENUM('0', '1') NOT NULL DEFAULT '1',
53  CONSTRAINT pk_idusuario PRIMARY KEY (id)
54)
55ENGINE = INNODB
56COLLATE = utf8_spanish2_ci;
57
58CREATE TABLE IF NOT EXISTS zblog.articulos (
59  id INT NOT NULL AUTO_INCREMENT,
60  id_tipo INT NOT NULL,
61  id_categoria INT NOT NULL,
62  titulo VARCHAR(45) NOT NULL,
63  fecha DATETIME NOT NULL,
64  id_usuario INT NOT NULL,
65  extracto VARCHAR(200) NOT NULL,
66  texto TEXT NULL DEFAULT NULL,
67  thumb VARCHAR(45) NOT NULL,
68  fuente VARCHAR(45) NULL DEFAULT NULL,
69  created_at DATETIME NOT NULL,
70  updated_at DATETIME NOT NULL,
71  estado ENUM('0', '1') NOT NULL DEFAULT '1',
72  CONSTRAINT pk_idarticulos PRIMARY KEY (id),
73  INDEX fk_articulos_categoria (id_categoria),
74  INDEX fk_articulos_tipo (id_tipo),
75  INDEX fk_articulos_usuario (id_usuario),
76  CONSTRAINT fk_articulos_categoria FOREIGN KEY (id_categoria) REFERENCES zblog.categoria (id) ON DELETE NO ACTION ON UPDATE NO ACTION,
77  CONSTRAINT fk_articulos_tipo FOREIGN KEY (id_tipo) REFERENCES zblog.tipo (id) ON DELETE NO ACTION ON UPDATE NO ACTION,
78  CONSTRAINT fk_articulos_usuario FOREIGN KEY (id_usuario) REFERENCES zblog.usuario (id) ON DELETE NO ACTION ON UPDATE NO ACTION
79)
80ENGINE = INNODB
81COLLATE = utf8_spanish2_ci;
82
83CREATE TABLE IF NOT EXISTS zblog.comments (
84  id INT NOT NULL AUTO_INCREMENT,
85  id_padre INT NOT NULL,
86  id_usuario INT NOT NULL,
87  id_articulo INT NOT NULL,
88  content TEXT NOT NULL,
89  created_at DATETIME NOT NULL,
90  updated_at DATETIME NOT NULL,
91  estado ENUM('0', '1') NOT NULL DEFAULT '1',
92  CONSTRAINT pk_idcomments PRIMARY KEY (id),
93  INDEX fk_comments_articulos (id_articulo),
94  INDEX fk_comments_usuario (id_usuario),
95  CONSTRAINT fk_comments_articulos FOREIGN KEY (id_articulo) REFERENCES zblog.articulos (id) ON DELETE NO ACTION ON UPDATE NO ACTION,
96  CONSTRAINT fk_comments_usuario FOREIGN KEY (id_usuario) REFERENCES zblog.usuario (id) ON DELETE NO ACTION ON UPDATE NO ACTION
97)
98ENGINE = INNODB
99COLLATE = utf8_spanish2_ci;
100
101CREATE TABLE IF NOT EXISTS zblog.likes (
102  id INT NOT NULL AUTO_INCREMENT,
103  id_usuario INT NOT NULL,
104  id_articulo INT NOT NULL,
105  created_at DATETIME NOT NULL,
106  updated_at DATETIME NOT NULL,
107  estado ENUM('0', '1') NOT NULL DEFAULT '1',
108  CONSTRAINT pk_idlikes PRIMARY KEY (id),
109  INDEX fk_likes_articulos (id_articulo),
110  INDEX fk_likes_usuario (id_usuario),
111  CONSTRAINT fk_likes_articulos FOREIGN KEY (id_articulo) REFERENCES zblog.articulos (id) ON DELETE NO ACTION ON UPDATE NO ACTION,
112  CONSTRAINT fk_likes_usuario FOREIGN KEY (id_usuario) REFERENCES zblog.usuario (id) ON DELETE NO ACTION ON UPDATE NO ACTION
113)
114ENGINE = INNODB
115COLLATE = utf8_spanish2_ci;
116
117CREATE TABLE IF NOT EXISTS zblog.favoritos (
118  id INT NOT NULL AUTO_INCREMENT,
119  id_usuario INT NOT NULL,
120  id_articulo INT NOT NULL,
121  created_at DATETIME NOT NULL,
122  updated_at DATETIME NOT NULL,
123  estado ENUM('0','1') NOT NULL DEFAULT '1',
124  CONSTRAINT pk_idfavoritos PRIMARY KEY (id),
125  INDEX fk_favoritos_usuario (id_usuario),
126  INDEX fk_favoritos_articulos (id_articulo),
127  CONSTRAINT fk_favoritos_usuario FOREIGN KEY (id_usuario) REFERENCES zblog.usuario (id) ON DELETE NO ACTION ON UPDATE NO ACTION,
128  CONSTRAINT fk_favoritos_articulos FOREIGN KEY (id_articulo) REFERENCES zblog.articulos (id) ON DELETE NO ACTION ON UPDATE NO ACTION
129)
130ENGINE = INNODB
131COLLATE = utf8_spanish2_ci;
132
133CREATE TABLE IF NOT EXISTS zblog.referencias (
134  id INT NOT NULL AUTO_INCREMENT,
135  autor VARCHAR(100) NULL,
136  id_tipo_referencia INT NOT NULL,
137  fecha_publicacion DATE NULL,
138  titulo_referencia VARCHAR(100) NOT NULL,
139  titulo_articulo VARCHAR(100) NULL,
140  recuperado VARCHAR(250) NULL,
141  vol_edit VARCHAR(50) NULL,
142  fecha_recuperacion DATE NOT NULL,
143  CONSTRAINT pk_idreferencias PRIMARY KEY (id),
144  INDEX fk_referencias_tiporeferencia (id_tipo_referencia),
145  CONSTRAINT fk_referencias_tiporeferencia FOREIGN KEY (id_tipo_referencia) REFERENCES zblog.tipo_referencia (id) ON DELETE NO ACTION ON UPDATE NO ACTION
146)
147ENGINE = INNODB
148COLLATE = utf8_spanish2_ci;
149
150CREATE TABLE IF NOT EXISTS zblog.referencia_articulo (
151  id INT NOT NULL AUTO_INCREMENT,
152  id_articulo INT NOT NULL,
153  id_referencia INT NOT NULL,
154  CONSTRAINT pk_idreferenciaarticulo PRIMARY KEY (id),
155  INDEX fk_refartc_articulos (id_articulo),
156  INDEX fk_refartc_referencias (id_referencia),
157  CONSTRAINT fk_refartc_articulos FOREIGN KEY (id_articulo) REFERENCES zblog.articulos (id) ON DELETE NO ACTION ON UPDATE NO ACTION,
158  CONSTRAINT fk_refartc_referencias FOREIGN KEY (id_referencia) REFERENCES zblog.referencias (id) ON DELETE NO ACTION ON UPDATE NO ACTION
159)
160ENGINE = INNODB
161COLLATE = utf8_spanish2_ci;
162
163CREATE TABLE IF NOT EXISTS zblog.permisos (
164  id INT NOT NULL AUTO_INCREMENT,
165  id_usuario INT NOT NULL,
166  id_permiso INT NOT NULL,
167  CONSTRAINT pk_idpermisos PRIMARY KEY (id),
168  INDEX fk_permisos_usuario (id_usuario),
169  INDEX fk_permisos_permiso (id_permiso),
170  CONSTRAINT fk_permisos_usuario FOREIGN KEY (id_usuario) REFERENCES zblog.usuario (id) ON DELETE NO ACTION ON UPDATE NO ACTION,
171  CONSTRAINT fk_permisos_permiso FOREIGN KEY (id_permiso) REFERENCES zblog.permiso (id) ON DELETE NO ACTION ON UPDATE NO ACTION
172)
173ENGINE = INNODB
174COLLATE = utf8_spanish2_ci;