BdpkfvLn

1CREATE DATABASE `proyecto_test`
2
3USE `proyecto_test`;
4
5DROP TABLE IF EXISTS `configuration`;
6CREATE TABLE `configuration`(
7    `id` int NOT NULL AUTO_INCREMENT PRIMARY KEY,
8    `title` VARCHAR(255) NOT NULL,
9    `description` TEXT NOT NULL,
10    `contact` VARCHAR(255) NOT NULL,
11    `activated` TINYINT(1) DEFAULT 0,
12    `items_per_page` INT NOT NULL DEFAULT 10
13)ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
14
15INSERT INTO `configuration` VALUES(NULL, 'Titulo default', 'descripcion', 'contacto@contacto.com', 0, 10);
16
17-- ------------------------------------------------------------
18--
19-- Estructura de tabla para la tabla usuario
20-- 
21
22DROP TABLE IF EXISTS `users`;
23CREATE TABLE `users`(
24    `id` INT(11) NOT NULL AUTO_INCREMENT,
25    `email` VARCHAR(255) COLLATE utf8_unicode_ci NOT NULL,
26    `username` VARCHAR(255) COLLATE utf8_unicode_ci NOT NULL,
27    `password` VARCHAR(255) COLLATE utf8_unicode_ci NOT NULL,
28    `active` TINYINT(1) NOT NULL DEFAULT 0,
29    `updated_at` datetime DEFAULT NULL,
30    `created_at` datetime DEFAULT DATE(),
31    `first_name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
32    `last_name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
33    PRIMARY KEY(id),
34    UNIQUE KEY unique_email(email),
35    UNIQUE KEY unique_username(username)
36)ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
37
38-- ------------------------------------------------------------
39--
40-- Estructura de tabla para la tabla usuario
41-- 
42
43DROP TABLE IF EXISTS `roles`;
44CREATE TABLE `roles` (
45  `id` int(11) NOT NULL AUTO_INCREMENT,
46  `name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
47  PRIMARY KEY (id)
48) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
49
50-- --------------------------------------------------------
51
52--
53-- Estructura de tabla para la tabla `permiso`
54--
55DROP TABLE IF EXISTS `permits`;
56CREATE TABLE `permits` (
57  `id` int(11) NOT NULL AUTO_INCREMENT,
58  `name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
59  PRIMARY KEY (id)
60) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
61
62-- --------------------------------------------------------
63
64--
65-- Estructura de tabla para la tabla `rol_tiene_permiso`
66--
67DROP TABLE IF EXISTS `role_has_permissions`;
68CREATE TABLE `role_has_permissions` (
69  `role_id` int(11) NOT NULL,
70  `permit_id` int(11) NOT NULL,
71  PRIMARY KEY (rol_id, permiso_id),
72  CONSTRAINT FK_role_id FOREIGN KEY (role_id) REFERENCES roles(id),
73  CONSTRAINT FK_permit_id FOREIGN KEY (permit_id) REFERENCES permits(id)
74) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
75
76-- --------------------------------------------------------
77
78--
79-- Estructura de tabla para la tabla `rol_tiene_permiso`
80--
81DROP TABLE IF EXISTS `user_has_roles`;
82CREATE TABLE `role_has_permissions` (
83  `role_id` int(11) NOT NULL,
84  `user_id` int(11) NOT NULL,
85  PRIMARY KEY (role_id, user_id),
86  CONSTRAINT FK_role_id FOREIGN KEY (role_id) REFERENCES roles(id),
87  CONSTRAINT FK_user_id FOREIGN KEY (user_id) REFERENCES users(id)
88) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;