· 7 years ago · Oct 17, 2018, 03:54 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='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';
6
7-- -----------------------------------------------------
8-- Schema mydb
9-- -----------------------------------------------------
10-- -----------------------------------------------------
11-- Schema trekhuntv2
12-- -----------------------------------------------------
13
14-- -----------------------------------------------------
15-- Schema trekhuntv2
16-- -----------------------------------------------------
17CREATE SCHEMA IF NOT EXISTS `trekhuntv2` DEFAULT CHARACTER SET latin1 ;
18USE `trekhuntv2` ;
19
20-- -----------------------------------------------------
21-- Table `trekhuntv2`.`Provider`
22-- -----------------------------------------------------
23CREATE TABLE IF NOT EXISTS `trekhuntv2`.`Provider` (
24 `id` VARCHAR(255) NOT NULL,
25 `displayName` VARCHAR(255) NULL DEFAULT NULL,
26 `fullName` VARCHAR(511) NULL DEFAULT NULL,
27 `url` VARCHAR(511) NULL DEFAULT NULL,
28 `primaryEmail` VARCHAR(255) NULL DEFAULT NULL,
29 `primaryPhone` VARCHAR(255) NULL DEFAULT NULL,
30 `shortDescription` MEDIUMTEXT NULL DEFAULT NULL,
31 `currency` VARCHAR(45) NULL DEFAULT NULL,
32 `language` VARCHAR(255) NULL DEFAULT NULL,
33 `timezone` VARCHAR(255) NULL DEFAULT NULL,
34 `logoUrl` VARCHAR(511) NULL DEFAULT NULL,
35 `plan` VARCHAR(255) NULL DEFAULT NULL,
36 `comissionType` VARCHAR(255) NULL DEFAULT NULL,
37 `bookingNotificationEmail` VARCHAR(255) NULL DEFAULT NULL,
38 PRIMARY KEY (`id`))
39ENGINE = InnoDB
40DEFAULT CHARACTER SET = latin1;
41
42
43-- -----------------------------------------------------
44-- Table `trekhuntv2`.`Trek`
45-- -----------------------------------------------------
46CREATE TABLE IF NOT EXISTS `trekhuntv2`.`Trek` (
47 `id` INT(11) NOT NULL AUTO_INCREMENT,
48 `link` VARCHAR(511) NULL DEFAULT NULL,
49 `endpoint` VARCHAR(255) NULL DEFAULT NULL,
50 `status` VARCHAR(127) NOT NULL,
51 `isScraped` TINYINT(4) NULL DEFAULT NULL,
52 `ownerId` VARCHAR(255) NULL DEFAULT NULL,
53 `Provider_id` VARCHAR(255) NULL DEFAULT NULL,
54 PRIMARY KEY (`id`),
55 UNIQUE INDEX `endpoint_UNIQUE` (`endpoint` ASC) VISIBLE,
56 INDEX `fk_Trek_Provider_idx` (`Provider_id` ASC) VISIBLE,
57 CONSTRAINT `fk_Trek_Provider`
58 FOREIGN KEY (`Provider_id`)
59 REFERENCES `trekhuntv2`.`Provider` (`id`)
60 ON DELETE NO ACTION
61 ON UPDATE NO ACTION)
62ENGINE = InnoDB
63AUTO_INCREMENT = 6
64DEFAULT CHARACTER SET = latin1;
65
66
67-- -----------------------------------------------------
68-- Table `trekhuntv2`.`Version`
69-- -----------------------------------------------------
70CREATE TABLE IF NOT EXISTS `trekhuntv2`.`Version` (
71 `id` INT(11) NOT NULL AUTO_INCREMENT,
72 `version` INT(11) NOT NULL,
73 `isActual` TINYINT(4) NOT NULL,
74 `timestamp` DATETIME NULL DEFAULT NULL,
75 `Trek_id` INT(11) NOT NULL,
76 PRIMARY KEY (`id`),
77 INDEX `fk_GlobalVersion_Trek1_idx` (`Trek_id` ASC) VISIBLE,
78 CONSTRAINT `fk_GlobalVersion_Trek1`
79 FOREIGN KEY (`Trek_id`)
80 REFERENCES `trekhuntv2`.`Trek` (`id`)
81 ON DELETE CASCADE
82 ON UPDATE NO ACTION)
83ENGINE = InnoDB
84AUTO_INCREMENT = 14
85DEFAULT CHARACTER SET = latin1;
86
87
88-- -----------------------------------------------------
89-- Table `trekhuntv2`.`TrekAvailability`
90-- -----------------------------------------------------
91CREATE TABLE IF NOT EXISTS `trekhuntv2`.`TrekAvailability` (
92 `id` INT(11) NOT NULL AUTO_INCREMENT,
93 `blob` LONGTEXT NULL DEFAULT NULL,
94 `Version_id` INT(11) NOT NULL,
95 PRIMARY KEY (`id`),
96 INDEX `fk_TrekAvailability_Version1_idx` (`Version_id` ASC) VISIBLE,
97 CONSTRAINT `fk_TrekAvailability_Version1`
98 FOREIGN KEY (`Version_id`)
99 REFERENCES `trekhuntv2`.`Version` (`id`)
100 ON DELETE CASCADE
101 ON UPDATE NO ACTION)
102ENGINE = InnoDB
103DEFAULT CHARACTER SET = utf8
104COLLATE = utf8_hungarian_ci;
105
106
107-- -----------------------------------------------------
108-- Table `trekhuntv2`.`TrekAvailabilityView`
109-- -----------------------------------------------------
110CREATE TABLE IF NOT EXISTS `trekhuntv2`.`TrekAvailabilityView` (
111 `id` INT(11) NOT NULL AUTO_INCREMENT,
112 `Trek_id` INT(11) NULL DEFAULT NULL,
113 `startDate` DATETIME NULL DEFAULT NULL,
114 `endDate` DATETIME NULL DEFAULT NULL,
115 `duration` INT(11) NULL DEFAULT NULL,
116 `price` DECIMAL(11,2) NULL DEFAULT NULL,
117 `seats` INT(11) NULL DEFAULT NULL,
118 `groupId` VARCHAR(255) NULL DEFAULT NULL,
119 PRIMARY KEY (`id`),
120 INDEX `FK_AVAILABILITY_TREK_idx` (`Trek_id` ASC) VISIBLE,
121 CONSTRAINT `FK_AVAILABILITY_TREK`
122 FOREIGN KEY (`Trek_id`)
123 REFERENCES `trekhuntv2`.`Trek` (`id`)
124 ON DELETE CASCADE
125 ON UPDATE NO ACTION)
126ENGINE = InnoDB
127DEFAULT CHARACTER SET = latin1;
128
129
130-- -----------------------------------------------------
131-- Table `trekhuntv2`.`TrekDetails`
132-- -----------------------------------------------------
133CREATE TABLE IF NOT EXISTS `trekhuntv2`.`TrekDetails` (
134 `id` INT(11) NOT NULL AUTO_INCREMENT,
135 `title` VARCHAR(127) NULL DEFAULT NULL,
136 `subtitle` VARCHAR(255) NULL DEFAULT NULL,
137 `description` TEXT NULL DEFAULT NULL,
138 `activities` VARCHAR(511) NULL DEFAULT NULL,
139 `spokenLanguages` VARCHAR(255) NULL DEFAULT NULL,
140 `textLanguage` VARCHAR(45) NULL DEFAULT NULL,
141 `isFree` TINYINT(4) NULL DEFAULT NULL,
142 `Version_id` INT(11) NOT NULL,
143 PRIMARY KEY (`id`),
144 INDEX `fk_TrekDetails_Version1_idx` (`Version_id` ASC) VISIBLE,
145 CONSTRAINT `fk_TrekDetails_Version1`
146 FOREIGN KEY (`Version_id`)
147 REFERENCES `trekhuntv2`.`Version` (`id`)
148 ON DELETE CASCADE
149 ON UPDATE NO ACTION)
150ENGINE = InnoDB
151AUTO_INCREMENT = 3
152DEFAULT CHARACTER SET = utf8
153COLLATE = utf8_hungarian_ci;
154
155
156-- -----------------------------------------------------
157-- Table `trekhuntv2`.`TrekItinerary`
158-- -----------------------------------------------------
159CREATE TABLE IF NOT EXISTS `trekhuntv2`.`TrekItinerary` (
160 `id` INT(11) NOT NULL AUTO_INCREMENT,
161 `blob` LONGTEXT NULL DEFAULT NULL,
162 `Version_id` INT(11) NOT NULL,
163 PRIMARY KEY (`id`),
164 INDEX `fk_TrekItinerary_Version1_idx` (`Version_id` ASC) VISIBLE,
165 CONSTRAINT `fk_TrekItinerary_Version1`
166 FOREIGN KEY (`Version_id`)
167 REFERENCES `trekhuntv2`.`Version` (`id`)
168 ON DELETE CASCADE
169 ON UPDATE NO ACTION)
170ENGINE = InnoDB
171AUTO_INCREMENT = 46
172DEFAULT CHARACTER SET = utf8
173COLLATE = utf8_hungarian_ci;
174
175
176-- -----------------------------------------------------
177-- Table `trekhuntv2`.`TrekItineraryView`
178-- -----------------------------------------------------
179CREATE TABLE IF NOT EXISTS `trekhuntv2`.`TrekItineraryView` (
180 `id` INT(11) NOT NULL AUTO_INCREMENT,
181 `Trek_id` INT(11) NULL DEFAULT NULL,
182 `order` INT(11) NULL DEFAULT NULL,
183 `title` VARCHAR(255) NULL DEFAULT NULL,
184 `subtitle` VARCHAR(255) NULL DEFAULT NULL,
185 `description` TEXT NULL DEFAULT NULL,
186 PRIMARY KEY (`id`),
187 INDEX `FK_ITINERARY_TREK_idx` (`Trek_id` ASC) VISIBLE,
188 CONSTRAINT `FK_ITINERARY_TREK`
189 FOREIGN KEY (`Trek_id`)
190 REFERENCES `trekhuntv2`.`Trek` (`id`)
191 ON DELETE CASCADE
192 ON UPDATE NO ACTION)
193ENGINE = InnoDB
194AUTO_INCREMENT = 46
195DEFAULT CHARACTER SET = utf8
196COLLATE = utf8_hungarian_ci;
197
198
199-- -----------------------------------------------------
200-- Table `trekhuntv2`.`TrekLocation`
201-- -----------------------------------------------------
202CREATE TABLE IF NOT EXISTS `trekhuntv2`.`TrekLocation` (
203 `id` INT(11) NOT NULL AUTO_INCREMENT,
204 `startString` VARCHAR(255) NULL DEFAULT NULL,
205 `startLat` DECIMAL(11,6) NULL DEFAULT NULL,
206 `startLon` DECIMAL(11,6) NULL DEFAULT NULL,
207 `endString` VARCHAR(255) NULL DEFAULT NULL,
208 `endLat` DECIMAL(11,6) NULL DEFAULT NULL,
209 `endLon` DECIMAL(11,6) NULL DEFAULT NULL,
210 `geoPoints` MEDIUMTEXT NULL DEFAULT NULL,
211 `countryCode` VARCHAR(45) NULL DEFAULT NULL,
212 `displayString` VARCHAR(255) NULL DEFAULT NULL,
213 `Version_id` INT(11) NOT NULL,
214 PRIMARY KEY (`id`),
215 INDEX `fk_TrekLocation_Version1_idx` (`Version_id` ASC) VISIBLE,
216 CONSTRAINT `fk_TrekLocation_Version1`
217 FOREIGN KEY (`Version_id`)
218 REFERENCES `trekhuntv2`.`Version` (`id`)
219 ON DELETE CASCADE
220 ON UPDATE NO ACTION)
221ENGINE = InnoDB
222DEFAULT CHARACTER SET = utf8
223COLLATE = utf8_hungarian_ci;
224
225
226-- -----------------------------------------------------
227-- Table `trekhuntv2`.`TrekPhoto`
228-- -----------------------------------------------------
229CREATE TABLE IF NOT EXISTS `trekhuntv2`.`TrekPhoto` (
230 `id` INT(11) NOT NULL AUTO_INCREMENT,
231 `blob` LONGTEXT NULL DEFAULT NULL,
232 `Version_id` INT(11) NOT NULL,
233 PRIMARY KEY (`id`),
234 INDEX `fk_TrekPhoto_Version1_idx` (`Version_id` ASC) VISIBLE,
235 CONSTRAINT `fk_TrekPhoto_Version1`
236 FOREIGN KEY (`Version_id`)
237 REFERENCES `trekhuntv2`.`Version` (`id`)
238 ON DELETE CASCADE
239 ON UPDATE NO ACTION)
240ENGINE = InnoDB
241AUTO_INCREMENT = 2
242DEFAULT CHARACTER SET = latin1;
243
244
245-- -----------------------------------------------------
246-- Table `trekhuntv2`.`TrekPhotoView`
247-- -----------------------------------------------------
248CREATE TABLE IF NOT EXISTS `trekhuntv2`.`TrekPhotoView` (
249 `id` INT(11) NOT NULL AUTO_INCREMENT,
250 `Trek_id` INT(11) NULL DEFAULT NULL,
251 `url` VARCHAR(511) NULL DEFAULT NULL,
252 `thumbUrl` VARCHAR(511) NULL DEFAULT NULL,
253 `caption` VARCHAR(511) NULL DEFAULT NULL,
254 `itineraryOrder` INT(11) NULL DEFAULT NULL,
255 `isHero` TINYINT(4) NULL DEFAULT NULL,
256 `isCardPhoto` TINYINT(4) NULL DEFAULT NULL,
257 PRIMARY KEY (`id`),
258 INDEX `FK_TREK_PHOTO_idx` (`Trek_id` ASC) VISIBLE,
259 CONSTRAINT `FK_TREKPHOTOVIEW_TREK`
260 FOREIGN KEY (`Trek_id`)
261 REFERENCES `trekhuntv2`.`Trek` (`id`)
262 ON DELETE CASCADE
263 ON UPDATE NO ACTION)
264ENGINE = InnoDB
265AUTO_INCREMENT = 2
266DEFAULT CHARACTER SET = latin1;
267
268
269-- -----------------------------------------------------
270-- Table `trekhuntv2`.`TrekPremiumInfo`
271-- -----------------------------------------------------
272CREATE TABLE IF NOT EXISTS `trekhuntv2`.`TrekPremiumInfo` (
273 `id` INT(11) NOT NULL AUTO_INCREMENT,
274 `blob` LONGTEXT NULL DEFAULT NULL,
275 `Version_id` INT(11) NOT NULL,
276 PRIMARY KEY (`id`),
277 INDEX `fk_TrekPremiumInfo_Version1_idx` (`Version_id` ASC) VISIBLE,
278 CONSTRAINT `fk_TrekPremiumInfo_Version1`
279 FOREIGN KEY (`Version_id`)
280 REFERENCES `trekhuntv2`.`Version` (`id`)
281 ON DELETE CASCADE
282 ON UPDATE NO ACTION)
283ENGINE = InnoDB
284DEFAULT CHARACTER SET = utf8
285COLLATE = utf8_hungarian_ci;
286
287
288-- -----------------------------------------------------
289-- Table `trekhuntv2`.`TrekPremiumInfoView`
290-- -----------------------------------------------------
291CREATE TABLE IF NOT EXISTS `trekhuntv2`.`TrekPremiumInfoView` (
292 `id` INT(11) NOT NULL AUTO_INCREMENT,
293 `Trek_id` INT(11) NULL DEFAULT NULL,
294 `title` VARCHAR(255) NULL DEFAULT NULL,
295 `description` TEXT NULL DEFAULT NULL,
296 `isDisplayed` TINYINT(4) NULL DEFAULT NULL,
297 `order` INT(11) NULL DEFAULT NULL,
298 PRIMARY KEY (`id`),
299 INDEX `FK_PREMIUM_TREK_idx` (`Trek_id` ASC) VISIBLE,
300 CONSTRAINT `FK_PREMIUM_TREK`
301 FOREIGN KEY (`Trek_id`)
302 REFERENCES `trekhuntv2`.`Trek` (`id`)
303 ON DELETE CASCADE
304 ON UPDATE NO ACTION)
305ENGINE = InnoDB
306DEFAULT CHARACTER SET = utf8
307COLLATE = utf8_hungarian_ci;
308
309
310SET SQL_MODE=@OLD_SQL_MODE;
311SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
312SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;