fbLt7nFQ

1-- MySQL Script generated by MySQL Workbench
2-- Wed May  2 14:39:38 2018
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='TRADITIONAL,ALLOW_INVALID_DATES';
9
10-- -----------------------------------------------------
11-- Schema mydb
12-- -----------------------------------------------------
13DROP SCHEMA IF EXISTS `mydb` ;
14
15-- -----------------------------------------------------
16-- Schema mydb
17-- -----------------------------------------------------
18CREATE SCHEMA IF NOT EXISTS `mydb` DEFAULT CHARACTER SET utf8 ;
19SHOW WARNINGS;
20-- -----------------------------------------------------
21-- Schema geodata
22-- -----------------------------------------------------
23DROP SCHEMA IF EXISTS `geodata` ;
24
25-- -----------------------------------------------------
26-- Schema geodata
27-- -----------------------------------------------------
28CREATE SCHEMA IF NOT EXISTS `geodata` DEFAULT CHARACTER SET utf8 ;
29SHOW WARNINGS;
30USE `mydb` ;
31
32-- -----------------------------------------------------
33-- Table `category`
34-- -----------------------------------------------------
35DROP TABLE IF EXISTS `category` ;
36
37SHOW WARNINGS;
38CREATE TABLE IF NOT EXISTS `category` (
39  `category_id` INT NOT NULL,
40  `name` VARCHAR(255) NOT NULL,
41  PRIMARY KEY (`category_id`));
42
43SHOW WARNINGS;
44USE `geodata` ;
45
46-- -----------------------------------------------------
47-- Table `countries`
48-- -----------------------------------------------------
49DROP TABLE IF EXISTS `countries` ;
50
51SHOW WARNINGS;
52CREATE TABLE IF NOT EXISTS `countries` (
53  `id` INT NOT NULL AUTO_INCREMENT,
54  `name` VARCHAR(50) NOT NULL,
55  PRIMARY KEY (`id`));
56
57SHOW WARNINGS;
58
59-- -----------------------------------------------------
60-- Table `regions`
61-- -----------------------------------------------------
62DROP TABLE IF EXISTS `regions` ;
63
64SHOW WARNINGS;
65CREATE TABLE IF NOT EXISTS `regions` (
66  `id` INT NOT NULL AUTO_INCREMENT,
67  `name` VARCHAR(50) NOT NULL,
68  `country_id` INT NOT NULL,
69  PRIMARY KEY (`id`),
70  CONSTRAINT `fk_country`
71    FOREIGN KEY (`country_id`)
72    REFERENCES `countries` (`id`)
73    ON DELETE CASCADE
74    ON UPDATE CASCADE);
75
76SHOW WARNINGS;
77CREATE INDEX `fk_country_idx` ON `regions` (`country_id` ASC);
78
79SHOW WARNINGS;
80
81-- -----------------------------------------------------
82-- Table `_cities`
83-- -----------------------------------------------------
84DROP TABLE IF EXISTS `_cities` ;
85
86SHOW WARNINGS;
87CREATE TABLE IF NOT EXISTS `_cities` (
88  `city_id` INT NULL AUTO_INCREMENT,
89  `country_id` INT NULL,
90  `important` TINYINT(1) NULL,
91  `region_id` INT NULL DEFAULT NULL,
92  `title_ru` VARCHAR(150) NULL DEFAULT NULL,
93  `area_ru` VARCHAR(150) NULL DEFAULT NULL,
94  `region_ru` VARCHAR(150) NULL DEFAULT NULL,
95  `title_ua` VARCHAR(150) NULL DEFAULT NULL,
96  `area_ua` VARCHAR(150) NULL DEFAULT NULL,
97  `region_ua` VARCHAR(150) NULL DEFAULT NULL,
98  `title_be` VARCHAR(150) NULL DEFAULT NULL,
99  `area_be` VARCHAR(150) NULL DEFAULT NULL,
100  `region_be` VARCHAR(150) NULL DEFAULT NULL,
101  `title_en` VARCHAR(150) NULL DEFAULT NULL,
102  `area_en` VARCHAR(150) NULL DEFAULT NULL,
103  `region_en` VARCHAR(150) NULL DEFAULT NULL,
104  `title_es` VARCHAR(150) NULL DEFAULT NULL,
105  `area_es` VARCHAR(150) NULL DEFAULT NULL,
106  `region_es` VARCHAR(150) NULL DEFAULT NULL,
107  `title_pt` VARCHAR(150) NULL DEFAULT NULL,
108  `area_pt` VARCHAR(150) NULL DEFAULT NULL,
109  `region_pt` VARCHAR(150) NULL DEFAULT NULL,
110  `title_de` VARCHAR(150) NULL DEFAULT NULL,
111  `area_de` VARCHAR(150) NULL DEFAULT NULL,
112  `region_de` VARCHAR(150) NULL DEFAULT NULL,
113  `title_fr` VARCHAR(150) NULL DEFAULT NULL,
114  `area_fr` VARCHAR(150) NULL DEFAULT NULL,
115  `region_fr` VARCHAR(150) NULL DEFAULT NULL,
116  `title_it` VARCHAR(150) NULL DEFAULT NULL,
117  `area_it` VARCHAR(150) NULL DEFAULT NULL,
118  `region_it` VARCHAR(150) NULL DEFAULT NULL,
119  `title_pl` VARCHAR(150) NULL DEFAULT NULL,
120  `area_pl` VARCHAR(150) NULL DEFAULT NULL,
121  `region_pl` VARCHAR(150) NULL DEFAULT NULL,
122  `title_ja` VARCHAR(150) NULL DEFAULT NULL,
123  `area_ja` VARCHAR(150) NULL DEFAULT NULL,
124  `region_ja` VARCHAR(150) NULL DEFAULT NULL,
125  `title_lt` VARCHAR(150) NULL DEFAULT NULL,
126  `area_lt` VARCHAR(150) NULL DEFAULT NULL,
127  `region_lt` VARCHAR(150) NULL DEFAULT NULL,
128  `title_lv` VARCHAR(150) NULL DEFAULT NULL,
129  `area_lv` VARCHAR(150) NULL DEFAULT NULL,
130  `region_lv` VARCHAR(150) NULL DEFAULT NULL,
131  `title_cz` VARCHAR(150) NULL DEFAULT NULL,
132  `area_cz` VARCHAR(150) NULL DEFAULT NULL,
133  `region_cz` VARCHAR(150) NULL DEFAULT NULL,
134  PRIMARY KEY (`city_id`),
135  CONSTRAINT `Foreign Key_countries (id)`
136    FOREIGN KEY (`country_id`)
137    REFERENCES `countries` (`id`)
138    ON DELETE NO ACTION
139    ON UPDATE NO ACTION,
140  CONSTRAINT `Foreign Key_regions (id)`
141    FOREIGN KEY (`region_id`)
142    REFERENCES `regions` (`id`)
143    ON DELETE NO ACTION
144    ON UPDATE NO ACTION);
145
146SHOW WARNINGS;
147CREATE INDEX `Foreign Key_countries (id)_idx` ON `_cities` (`country_id` ASC);
148
149SHOW WARNINGS;
150CREATE INDEX `Foreign Key_regions (id)_idx` ON `_cities` (`region_id` ASC);
151
152SHOW WARNINGS;
153CREATE INDEX `Index` ON `_cities` (`title_ru` ASC, `area_ru` ASC, `region_ru` ASC, `title_ua` ASC, `area_ua` ASC, `region_ua` ASC, `title_be` ASC, `area_be` ASC, `region_be` ASC, `title_en` ASC, `area_en` ASC, `region_en` ASC, `title_es` ASC, `area_es` ASC, `region_es` ASC, `title_pt` ASC, `area_pt` ASC, `region_pt` ASC, `title_de` ASC, `area_de` ASC, `region_de` ASC, `title_fr` ASC, `area_fr` ASC, `region_fr` ASC, `title_it` ASC, `area_it` ASC, `region_it` ASC, `title_pl` ASC, `area_pl` ASC, `title_ja` ASC, `region_pl` ASC, `area_ja` ASC, `region_ja` ASC, `title_lt` ASC, `area_lt` ASC, `region_lt` ASC, `title_lv` ASC, `area_lv` ASC, `region_lv` ASC, `title_cz` ASC, `area_cz` ASC, `region_cz` ASC);
154
155SHOW WARNINGS;
156
157-- -----------------------------------------------------
158-- Table `_regions`
159-- -----------------------------------------------------
160DROP TABLE IF EXISTS `_regions` ;
161
162SHOW WARNINGS;
163CREATE TABLE IF NOT EXISTS `_regions` (
164  `region_id` INT NULL AUTO_INCREMENT,
165  `country_id` INT NULL,
166  `title_ru` VARCHAR(150) NULL DEFAULT NULL,
167  `title_ua` VARCHAR(150) NULL DEFAULT NULL,
168  `title_be` VARCHAR(150) NULL DEFAULT NULL,
169  `title_en` VARCHAR(150) NULL DEFAULT NULL,
170  `title_es` VARCHAR(150) NULL DEFAULT NULL,
171  `title_pt` VARCHAR(150) NULL DEFAULT NULL,
172  `title_de` VARCHAR(150) NULL DEFAULT NULL,
173  `title_fr` VARCHAR(150) NULL DEFAULT NULL,
174  `title_it` VARCHAR(150) NULL DEFAULT NULL,
175  `title_pl` VARCHAR(150) NULL DEFAULT NULL,
176  `title_ja` VARCHAR(150) NULL DEFAULT NULL,
177  `title_lt` VARCHAR(150) NULL DEFAULT NULL,
178  `title_lv` VARCHAR(150) NULL DEFAULT NULL,
179  `title_cz` VARCHAR(150) NULL DEFAULT NULL,
180  PRIMARY KEY (`region_id`),
181  CONSTRAINT `Key_countries (id)`
182    FOREIGN KEY (`country_id`)
183    REFERENCES `countries` (`id`)
184    ON DELETE NO ACTION
185    ON UPDATE NO ACTION);
186
187SHOW WARNINGS;
188CREATE INDEX `INDEX` ON `_regions` (`title_ru` ASC, `title_ua` ASC, `title_be` ASC, `title_en` ASC, `title_es` ASC, `title_pt` ASC, `title_de` ASC, `title_fr` ASC, `title_it` ASC, `title_pl` ASC, `title_ja` ASC, `title_lt` ASC, `title_lv` ASC, `title_cz` ASC);
189
190SHOW WARNINGS;
191
192-- -----------------------------------------------------
193-- Table `_countries`
194-- -----------------------------------------------------
195DROP TABLE IF EXISTS `_countries` ;
196
197SHOW WARNINGS;
198CREATE TABLE IF NOT EXISTS `_countries` (
199  `country_id` INT NOT NULL AUTO_INCREMENT,
200  `title_ru` VARCHAR(150) NULL DEFAULT NULL,
201  `title_ua` VARCHAR(150) NULL DEFAULT NULL,
202  `title_be` VARCHAR(150) NULL DEFAULT NULL,
203  `title_en` VARCHAR(150) NULL DEFAULT NULL,
204  `title_es` VARCHAR(150) NULL DEFAULT NULL,
205  `title_pt` VARCHAR(150) NULL DEFAULT NULL,
206  `title_de` VARCHAR(150) NULL DEFAULT NULL,
207  `title_fr` VARCHAR(150) NULL DEFAULT NULL,
208  `title_it` VARCHAR(150) NULL DEFAULT NULL,
209  `title_pl` VARCHAR(150) NULL DEFAULT NULL,
210  `title_ja` VARCHAR(150) NULL DEFAULT NULL,
211  `title_lt` VARCHAR(150) NULL DEFAULT NULL,
212  `title_lv` VARCHAR(150) NULL DEFAULT NULL,
213  `title_cz` VARCHAR(150) NULL DEFAULT NULL,
214  PRIMARY KEY (`country_id`));
215
216SHOW WARNINGS;
217CREATE INDEX `INDEX` ON `_countries` (`title_ru` ASC, `title_ua` ASC, `title_be` ASC, `title_en` ASC, `title_es` ASC, `title_pt` ASC, `title_de` ASC, `title_fr` ASC, `title_it` ASC, `title_pl` ASC, `title_ja` ASC, `title_lt` ASC, `title_cz` ASC, `title_lv` ASC);
218
219SHOW WARNINGS;
220
221SET SQL_MODE=@OLD_SQL_MODE;
222SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
223SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;