· 5 years ago · Mar 21, 2020, 10:40 PM
1-- MySQL Script generated by MySQL Workbench
2-- Sat Mar 21 19:36:12 2020
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='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';
9
10-- -----------------------------------------------------
11-- Schema anunciados_api
12-- -----------------------------------------------------
13
14-- -----------------------------------------------------
15-- Schema anunciados_api
16-- -----------------------------------------------------
17CREATE SCHEMA IF NOT EXISTS `anunciados_api` DEFAULT CHARACTER SET utf8 ;
18USE `anunciados_api` ;
19
20-- -----------------------------------------------------
21-- Table `anunciados_api`.`USER_STATUS`
22-- -----------------------------------------------------
23CREATE TABLE IF NOT EXISTS `anunciados_api`.`USER_STATUS` (
24 `USER_STATUS_ID` INT NOT NULL,
25 `CODE` VARCHAR(45) NULL,
26 `DESCRIPTION` VARCHAR(45) NOT NULL,
27 PRIMARY KEY (`USER_STATUS_ID`))
28ENGINE = InnoDB;
29
30
31-- -----------------------------------------------------
32-- Table `anunciados_api`.`USER`
33-- -----------------------------------------------------
34CREATE TABLE IF NOT EXISTS `anunciados_api`.`USER` (
35 `USERID` INT NOT NULL,
36 `NAME` VARCHAR(45) NULL,
37 `LASTNAME` VARCHAR(45) NULL,
38 `EMAIL` VARCHAR(45) NOT NULL,
39 `REGISTER_DATE` TIMESTAMP NULL,
40 `PASSWORD` VARCHAR(500) NOT NULL,
41 `ISDELETE` TINYINT NULL,
42 `USER_STATUS_ID` INT NOT NULL,
43 PRIMARY KEY (`USERID`),
44 INDEX `USER_STATUS_FK_idx` (`USER_STATUS_ID` ASC) VISIBLE,
45 CONSTRAINT `USER_STATUS_FK`
46 FOREIGN KEY (`USER_STATUS_ID`)
47 REFERENCES `anunciados_api`.`USER_STATUS` (`USER_STATUS_ID`)
48 ON DELETE NO ACTION
49 ON UPDATE NO ACTION)
50ENGINE = InnoDB;
51
52
53-- -----------------------------------------------------
54-- Table `anunciados_api`.`CATEGORY`
55-- -----------------------------------------------------
56CREATE TABLE IF NOT EXISTS `anunciados_api`.`CATEGORY` (
57 `CATEGORYID` INT NOT NULL,
58 `CODE` VARCHAR(255) NULL,
59 `NAME` VARCHAR(45) NULL,
60 `DESCRIPTION` VARCHAR(45) NULL,
61 `IMAGE_PATH` VARCHAR(255) NULL,
62 `ACTIVE` TINYINT NULL DEFAULT 1,
63 `MARKER_IMAGE_PATH` VARCHAR(255) NULL,
64 `ICON_PATH` VARCHAR(100) NULL,
65 PRIMARY KEY (`CATEGORYID`))
66ENGINE = InnoDB;
67
68
69-- -----------------------------------------------------
70-- Table `anunciados_api`.`ADVERTISEMENT_STATE`
71-- -----------------------------------------------------
72CREATE TABLE IF NOT EXISTS `anunciados_api`.`ADVERTISEMENT_STATE` (
73 `ADVERTISEMENTSTATEID` INT NOT NULL,
74 `CODE` VARCHAR(100) NOT NULL,
75 `DESCRIPTION` VARCHAR(100) NOT NULL,
76 PRIMARY KEY (`ADVERTISEMENTSTATEID`))
77ENGINE = InnoDB;
78
79
80-- -----------------------------------------------------
81-- Table `anunciados_api`.`ADVERTISEMENTS`
82-- -----------------------------------------------------
83CREATE TABLE IF NOT EXISTS `anunciados_api`.`ADVERTISEMENTS` (
84 `ADVERTISEMENTSID` INT NOT NULL,
85 `CATEGORYID` INT NOT NULL,
86 `USERID` INT NOT NULL,
87 `DESCRIPTION` VARCHAR(255) GENERATED ALWAYS AS () VIRTUAL,
88 `PUBLISH_DATE` TIMESTAMP NOT NULL,
89 `LATITUDE` FLOAT NOT NULL,
90 `LONGITUDE` FLOAT NOT NULL,
91 `ISDELETE` TINYINT NULL DEFAULT 0,
92 `ADVERTISEMENTSTATEID` INT NOT NULL,
93 INDEX `ADVERTISEMENTS_USER_idx` (`USERID` ASC) VISIBLE,
94 INDEX `ADVERTISEMENT_SUBCAT_FK_idx` (`CATEGORYID` ASC) VISIBLE,
95 PRIMARY KEY (`ADVERTISEMENTSID`),
96 INDEX `ADVERTISEMENT_STATE_idx` (`ADVERTISEMENTSTATEID` ASC) VISIBLE,
97 CONSTRAINT `ADVERTISEMENTS_USER_FK`
98 FOREIGN KEY (`USERID`)
99 REFERENCES `anunciados_api`.`USER` (`USERID`)
100 ON DELETE NO ACTION
101 ON UPDATE NO ACTION,
102 CONSTRAINT `ADVERTISEMENT_CAT_FK`
103 FOREIGN KEY (`CATEGORYID`)
104 REFERENCES `anunciados_api`.`CATEGORY` (`CATEGORYID`)
105 ON DELETE NO ACTION
106 ON UPDATE NO ACTION,
107 CONSTRAINT `ADVERTISEMENT_STATE`
108 FOREIGN KEY (`ADVERTISEMENTSTATEID`)
109 REFERENCES `anunciados_api`.`ADVERTISEMENT_STATE` (`ADVERTISEMENTSTATEID`)
110 ON DELETE NO ACTION
111 ON UPDATE NO ACTION)
112ENGINE = InnoDB;
113
114
115-- -----------------------------------------------------
116-- Table `anunciados_api`.`RECORD`
117-- -----------------------------------------------------
118CREATE TABLE IF NOT EXISTS `anunciados_api`.`RECORD` (
119 `RECORID` INT NOT NULL,
120 `DESCRIPTION` VARCHAR(255) NOT NULL,
121 `USERID` INT NOT NULL,
122 `RECORD_DATE` TIMESTAMP NULL,
123 PRIMARY KEY (`RECORID`),
124 INDEX `RECORD_USER_FK_idx` (`USERID` ASC) VISIBLE,
125 CONSTRAINT `RECORD_USER_FK`
126 FOREIGN KEY (`USERID`)
127 REFERENCES `anunciados_api`.`USER` (`USERID`)
128 ON DELETE NO ACTION
129 ON UPDATE NO ACTION)
130ENGINE = InnoDB;
131
132
133-- -----------------------------------------------------
134-- Table `anunciados_api`.`ADVERTISEMENT_GALLERY`
135-- -----------------------------------------------------
136CREATE TABLE IF NOT EXISTS `anunciados_api`.`ADVERTISEMENT_GALLERY` (
137 `ADVERTISEMENTSGALLERYID` INT NOT NULL,
138 `IMAGE_PATH` VARCHAR(255) NOT NULL,
139 `ADVERTISEMENTSID` INT NOT NULL,
140 `ORDER` SMALLINT(3) NULL,
141 `DESCRIPTION` VARCHAR(100) NULL,
142 PRIMARY KEY (`ADVERTISEMENTSGALLERYID`),
143 INDEX `ADVERTISEMENT_GALLERY_idx` (`ADVERTISEMENTSID` ASC) VISIBLE,
144 CONSTRAINT `ADVERTISEMENT_GALLERY`
145 FOREIGN KEY (`ADVERTISEMENTSID`)
146 REFERENCES `anunciados_api`.`ADVERTISEMENTS` (`ADVERTISEMENTSID`)
147 ON DELETE NO ACTION
148 ON UPDATE NO ACTION)
149ENGINE = InnoDB;
150
151
152SET SQL_MODE=@OLD_SQL_MODE;
153SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
154SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
155
156-- -----------------------------------------------------
157-- Data for table `anunciados_api`.`USER_STATUS`
158-- -----------------------------------------------------
159START TRANSACTION;
160USE `anunciados_api`;
161INSERT INTO `anunciados_api`.`USER_STATUS` (`USER_STATUS_ID`, `CODE`, `DESCRIPTION`) VALUES (1, 'anunciados.userstatus.pendingvalidation', 'Pendiente de validación');
162INSERT INTO `anunciados_api`.`USER_STATUS` (`USER_STATUS_ID`, `CODE`, `DESCRIPTION`) VALUES (2, 'anunciados.userstatus.active', 'Activo');
163INSERT INTO `anunciados_api`.`USER_STATUS` (`USER_STATUS_ID`, `CODE`, `DESCRIPTION`) VALUES (3, 'anunciados.userstatus.inactive', 'Inactivo');
164INSERT INTO `anunciados_api`.`USER_STATUS` (`USER_STATUS_ID`, `CODE`, `DESCRIPTION`) VALUES (4, 'anunciados.usertstatus.blocked', 'Bloqueado');
165
166COMMIT;
167
168
169-- -----------------------------------------------------
170-- Data for table `anunciados_api`.`CATEGORY`
171-- -----------------------------------------------------
172START TRANSACTION;
173USE `anunciados_api`;
174INSERT INTO `anunciados_api`.`CATEGORY` (`CATEGORYID`, `CODE`, `NAME`, `DESCRIPTION`, `IMAGE_PATH`, `ACTIVE`, `MARKER_IMAGE_PATH`, `ICON_PATH`) VALUES (1, 'anunciados.category.categorytest', 'Categoria de pruebla', 'Descripción de categoria de prueba', 'sin-imagen', 1, 'sin-imagen', 'sin-imagen');
175
176COMMIT;