-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathGeoLite2-City-CSV.mysql.eatlog.ini
202 lines (168 loc) · 8.55 KB
/
GeoLite2-City-CSV.mysql.eatlog.ini
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
// Configuration file maxmind-geoip2-csv2sql-converter for converting GeoLite2-City-CSV for MySQL
// Section syntax: Settings: [Settings:key]; Template: [template_name]
// Merge next template line to current: \ on end line; Allowed: \n \r \t; Escaping: \\n \\r \\t
// Escape bracket char \{ or \[section name] if char '{' or section is a part of template
// Comments line: //
// Example template engine syntax:
// Field with getter or method(optional parameters): ${(.|..)fieldWithGetter|method("string param", longParam) key{value} key2{value2}}
// forEach: #{forEach{some text ${collection reference key{value} key2{value2}} ${collection2 reference key3{value3} key4{value4}} some textN} key5{value5} key6{value6}}
[Settings:Import]
locations_filenames=#{forEach{GeoLite2-City-Locations-${locales..code}.csv} Separator{,}}
ipv4_filename=GeoLite2-City-Blocks-IPv4.csv
ipv6_filename=GeoLite2-City-Blocks-IPv6.csv
[Settings:Export]
values_separator=,
values_end=;
values_end_of_file=
values_count_per_insert=
timezone_create_filename=timezone_create.sql
timezone_insert_filename=timezone_%03d.sql
timezone_indexes_filename=timezone_indexes.sql
country_create_filename=country_create.sql
country_insert_filename=country_%03d.sql
country_indexes_filename=country_indexes.sql
subdivision1_create_filename=subdivision1_create.sql
subdivision1_insert_filename=subdivision1_%03d.sql
subdivision1_indexes_filename=subdivision1_indexes.sql
subdivision2_create_filename=subdivision2_create.sql
subdivision2_insert_filename=subdivision2_%03d.sql
subdivision2_indexes_filename=subdivision2_indexes.sql
city_create_filename=city_create.sql
city_insert_filename=city_%03d.sql
city_indexes_filename=city_indexes.sql
ipv4_create_filename=ipv4_create.sql
ipv4_insert_filename=ipv4_%03d.sql
ipv4_indexes_filename=ipv4_indexes.sql
ipv6_create_filename=ipv6_create.sql
ipv6_insert_filename=ipv6_%03d.sql
ipv6_indexes_filename=ipv6_indexes.sql
// One of valid geoname_id for ipv4_values/ipv6_values templates from Blocks-IPv4/Blocks-IPv6 files (${.priorityGeonameId} placeholder);
ipblocks_priority_geonameId_groupNames=geoname_id,registered_country_geoname_id,represented_country_geoname_id
log_ignored_locations=false
log_ignored_ipblocks=false
log_undefined_all_geonameIds=true
log_undefined_all_subdivisions_and_city_name=false
schema_name=eatlog_maxmind_city
load_data_filename=load_data.sql
windows_loader_filename=loader.bat
unix_loader_filename=loader.sh
[load_data]
SET NAMES utf8mb4;
SET GLOBAL max_allowed_packet=${args.getBytesCountPerFile()};
SET FOREIGN_KEY_CHECKS=0;
DROP SCHEMA IF EXISTS `${config.get("Export").get("schema_name")}`;
CREATE SCHEMA `${config.get("Export").get("schema_name")}` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE `${config.get("Export").get("schema_name")}`;
#{forEach{\. ${createPaths..fileName}} Separator{\n}}
BEGIN;
#{forEach{\. ${insertPaths..fileName}} Separator{\n} Disable{EmptyCollection}}
COMMIT;
#{forEach{\. ${indexPaths..fileName}} Separator{\n}}
SET FOREIGN_KEY_CHECKS=1;
ANALYZE TABLE `city`,`country`,`ipv4`,`subdivision1`,`subdivision2`,`timezone`;
[windows_loader]
@echo off
chcp 65001
SET scriptPath=%~dp0
%scriptPath:~0,2%
cd %scriptPath:~0,-1%
"C:\\Program Files\\MySQL\\MySQL Server 8.0\\bin\\mysql.exe" --host=127.0.0.1 --port=3306 \
--user=root --password=root --max_allowed_packet=${args.getBytesCountPerFile()} < "${config.get("Export").get("load_data_filename")}"
[unix_loader]
#!/bin/bash
cd "$( cd -- "$(dirname "$0")" >/dev/null 2>&1 ; pwd -P )"
mysql --host=127.0.0.1 --port=3306 --user=root --password=root --max_allowed_packet=${args.getBytesCountPerFile()} < "${config.get("Export").get("load_data_filename")}"
[country_create]
CREATE TABLE `country` (
`id` smallint unsigned NOT NULL,
`emoji` char(2) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
#{forEach{`name_${locales..code}` varchar(${locales..getMaxLengthOrDefault("country_name", 77)}) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL} Separator{,\n}},
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
[country_indexes]
[country_insert]
insert into country(id,emoji,#{forEach{`name_${locales..code}`} Separator{,}})values
[country_values]
(${.keys.get("country.id")},${.emoji Prefix{'} Postfix{'}},\
#{forEach{${.localeValues.values()..get("country_name") Prefix{'} Postfix{'} Escape{[']} Escaper{\\$0}}} Separator{,}})
[subdivision1_create]
CREATE TABLE `subdivision1` (
`id` smallint unsigned NOT NULL,
#{forEach{`name_${locales..code}` varchar(${locales..getMaxLengthOrDefault("subdivision_1_name", 3)}) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL} Separator{,\n}},
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
[subdivision1_indexes]
[subdivision1_insert]
insert into subdivision1(id,#{forEach{`name_${locales..code}`} Separator{,}})values
[subdivision1_values]
(${.keys.get("subdivision1.id")},\
#{forEach{${.localeValues.values()..get("subdivision_1_name") Prefix{'} Postfix{'} Escape{[']} Escaper{\\$0}}} Separator{,}})
[subdivision2_create]
CREATE TABLE `subdivision2` (
`id` smallint unsigned NOT NULL,
#{forEach{`name_${locales..code}` varchar(${locales..getMaxLengthOrDefault("subdivision_2_name", 3)}) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL} Separator{,\n}},
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
[subdivision2_indexes]
[subdivision2_insert]
insert into subdivision2(id,#{forEach{`name_${locales..code}`} Separator{,}})values
[subdivision2_values]
(${.keys.get("subdivision2.id")},\
#{forEach{${.localeValues.values()..get("subdivision_2_name") Prefix{'} Postfix{'} Escape{[']} Escaper{\\$0}}} Separator{,}})
[city_create]
CREATE TABLE `city` (
`geoname_id` int unsigned NOT NULL,
`country_id` smallint unsigned NOT NULL,
`subdivision1_id` smallint unsigned DEFAULT NULL,
`subdivision2_id` smallint unsigned DEFAULT NULL,
#{forEach{`name_${locales..code}` varchar(${locales..getMaxLengthOrDefault("city_name", 120)}) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL} Separator{,\n}},
PRIMARY KEY (`geoname_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
[city_indexes]
ALTER TABLE `city`
ADD INDEX `city_country_id_fk_idx` (`country_id`),
ADD INDEX `city_subdivision1_id_fk_idx` (`subdivision1_id`),
ADD INDEX `city_subdivision2_id_fk_idx` (`subdivision2_id`),
ADD CONSTRAINT `city_country_id_fk` FOREIGN KEY (`country_id`) REFERENCES `country` (`id`) ON UPDATE CASCADE ON DELETE RESTRICT,
ADD CONSTRAINT `city_subdivision1_id_fk` FOREIGN KEY (`subdivision1_id`) REFERENCES `subdivision1` (`id`) ON UPDATE CASCADE ON DELETE RESTRICT,
ADD CONSTRAINT `city_subdivision2_id_fk` FOREIGN KEY (`subdivision2_id`) REFERENCES `subdivision2` (`id`) ON UPDATE CASCADE ON DELETE RESTRICT;
[city_insert]
insert into city(geoname_id,country_id,subdivision1_id,subdivision2_id,#{forEach{`name_${locales..code}`} Separator{,}})values
[city_values]
(${.values.get("geoname_id")},${.keys.get("country.id")},\
${.keys.get("subdivision1.id")},${.keys.get("subdivision2.id")},\
#{forEach{${.localeValues.values()..get("city_name") Prefix{'} Postfix{'} Escape{[']} Escaper{\\$0}}} Separator{,}})
[ipv4_create]
CREATE TABLE `ipv4` (
`start_int` int unsigned NOT NULL,
`last_int` int unsigned NOT NULL,
`v_geoname_id` int unsigned NOT NULL COMMENT 'One of valid geoname_id (from ipblocks_priority_geonameId_groupNames setting)'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
[ipv6_create]
[ipv4_indexes]
ALTER TABLE `ipv4`
ADD UNIQUE INDEX `ipv4_start_int_UNIQUE` (`start_int`),
ADD UNIQUE INDEX `ipv4_last_int_UNIQUE` (`last_int`),
ADD INDEX `ipv4_v_geoname_id_fk_idx` (`v_geoname_id`),
ADD CONSTRAINT `ipv4_v_geoname_id_fk` FOREIGN KEY (`v_geoname_id`) REFERENCES `city` (`geoname_id`) ON UPDATE CASCADE ON DELETE RESTRICT;
[ipv6_indexes]
[ipv4_insert]
insert into ipv4(start_int,last_int,v_geoname_id)values
[ipv6_insert]
[ipv4_values]
(${.range.start().asBigInteger()},${.range.end().asBigInteger()},\
${.priorityGeonameId})
[ipv6_values]
[timezone_create]
CREATE TABLE `timezone` (
`id` smallint unsigned NOT NULL,
`name` varchar(${locationMaxLengths.getOrDefault("time_zone", 30)}) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=1;
[timezone_indexes]
ALTER TABLE `timezone`
ADD UNIQUE INDEX `timezone_name_UNIQUE` (`name`);
[timezone_insert]
insert into timezone(id,name)values
[timezone_values]
(${.keys.get("timezone.id")},${.values.get("time_zone") Prefix{'} Postfix{'} Escape{[']} Escaper{\\$0}})