2 DROP FUNCTION IF EXISTS core_cities_merge_country;
4 CREATE FUNCTION core_cities_merge_country() RETURNS INT DETERMINISTIC
6 DECLARE co_done INT DEFAULT FALSE;
8 DECLARE v_count INT DEFAULT 0;
9 DECLARE v_total INT DEFAULT 0;
11 DECLARE v_id INT DEFAULT 0;
12 DECLARE v_iso TEXT DEFAULT '';
13 DECLARE v_local_name TEXT DEFAULT '';
14 -- DECLARE v_type TEXT DEFAULT '';
15 DECLARE v_in_location INT DEFAULT 0;
16 DECLARE v_geo_lat INT DEFAULT 0;
17 DECLARE v_geo_lng INT DEFAULT 0;
19 DECLARE v_id_tmp INT DEFAULT 0;
20 DECLARE v_iso_tmp TEXT DEFAULT '';
21 DECLARE v_local_name_tmp TEXT DEFAULT '';
22 DECLARE v_type_tmp TEXT DEFAULT '';
23 DECLARE v_in_location_tmp INT DEFAULT 0;
25 DECLARE v_id_tmp_tmp INT DEFAULT 0;
27 DECLARE co_csr CURSOR FOR
29 id,iso,local_name,in_location
35 SELECT COUNT(id) INTO v_total FROM meta_location WHERE type = 'CO';
41 FETCH co_csr INTO v_id,v_iso,v_local_name,v_in_location;
43 SET v_count = v_count + 1;
47 SELECT id INTO v_id_tmp FROM core_geoip_country WHERE code = v_iso;
50 INSERT INTO core_geoip_country (code, name, continent_id) VALUES (v_iso, v_local_name, 0);
53 IF v_count = v_total THEN
68 DROP FUNCTION IF EXISTS core_cities_merge_division;
70 CREATE FUNCTION core_cities_merge_division() RETURNS INT DETERMINISTIC
72 DECLARE re_done INT DEFAULT FALSE;
74 DECLARE v_count INT DEFAULT 0;
75 DECLARE v_total INT DEFAULT 0;
77 DECLARE v_id INT DEFAULT 0;
78 DECLARE v_iso TEXT DEFAULT '';
79 DECLARE v_local_name TEXT DEFAULT '';
80 -- DECLARE v_type TEXT DEFAULT '';
81 DECLARE v_in_location INT DEFAULT 0;
82 DECLARE v_geo_lat INT DEFAULT 0;
83 DECLARE v_geo_lng INT DEFAULT 0;
85 DECLARE v_country_id INT DEFAULT 0;
86 DECLARE v_iso_tmp TEXT DEFAULT '';
88 DECLARE v_division_id INT DEFAULT 0;
90 DECLARE re_csr CURSOR FOR
92 id,iso,local_name,in_location
98 SELECT COUNT(id) INTO v_total FROM meta_location WHERE type = 'RE';
104 FETCH re_csr INTO v_id,v_iso,v_local_name,v_in_location;
106 SET v_count = v_count + 1;
108 SET v_country_id = 0;
109 SET v_division_id = 0;
112 IF v_in_location IS NOT NULL THEN
113 SELECT iso INTO v_iso_tmp FROM meta_location WHERE id = v_in_location;
115 SELECT id INTO v_country_id FROM core_geoip_country WHERE code = v_iso_tmp;
130 name LIKE (CONCAT('%', v_local_name, '%'))
132 code = SUBSTRING_INDEX(v_iso, '-', -1)
135 AND country_id = v_country_id;
137 IF v_division_id = 0 THEN
139 INSERT INTO core_geoip_division (code, name, country_id) VALUES (v_iso, v_local_name, v_country_id);
144 IF v_count = v_total THEN
157 DROP FUNCTION IF EXISTS core_cities_merge_city;
159 CREATE FUNCTION core_cities_merge_city() RETURNS INT DETERMINISTIC
161 DECLARE ci_done INT DEFAULT FALSE;
163 DECLARE v_count INT DEFAULT 0;
164 DECLARE v_total INT DEFAULT 0;
166 DECLARE v_id INT DEFAULT 0;
167 DECLARE v_iso TEXT DEFAULT '';
168 DECLARE v_local_name TEXT DEFAULT '';
169 -- DECLARE v_type TEXT DEFAULT '';
170 DECLARE v_in_location INT DEFAULT 0;
171 DECLARE v_geo_lat INT DEFAULT 0;
172 DECLARE v_geo_lng INT DEFAULT 0;
174 DECLARE v_id_tmp INT DEFAULT 0;
175 DECLARE v_iso_tmp TEXT DEFAULT '';
176 DECLARE v_local_name_tmp TEXT DEFAULT '';
177 DECLARE v_type_tmp TEXT DEFAULT '';
178 DECLARE v_in_location_tmp INT DEFAULT 0;
180 DECLARE v_id_tmp_tmp INT DEFAULT 0;
182 DECLARE ci_csr CURSOR FOR
184 id,iso,local_name,in_location,geo_lat,geo_lng
190 SELECT COUNT(id) INTO v_total FROM meta_location WHERE type = 'CI';
196 FETCH ci_csr INTO v_id,v_iso,v_local_name,v_in_location,v_geo_lat,v_geo_lng;
198 SET v_count = v_count + 1;
201 SET v_id_tmp_tmp = 0;
203 SELECT id INTO v_id_tmp FROM core_geoip_city WHERE name = v_local_name LIMIT 1;
205 IF(v_id_tmp = 0) THEN
206 IF v_in_location IS NOT NULL THEN
208 SELECT iso, local_name, type, in_location INTO v_iso_tmp, v_local_name_tmp, v_type_tmp, v_in_location_tmp FROM meta_location WHERE id = v_in_location;
210 IF v_type_tmp = 'CO' THEN
211 SELECT id INTO v_id_tmp FROM core_geoip_country WHERE code = v_iso_tmp;
213 INSERT INTO core_geoip_city (name, country_id) VALUES (v_local_name, v_id_tmp);
216 IF v_type_tmp = 'RE' THEN
217 SELECT id INTO v_id_tmp FROM core_geoip_division WHERE name = v_local_name_tmp;
219 SELECT iso, local_name, type INTO v_iso_tmp, v_local_name_tmp, v_type_tmp FROM meta_location WHERE id = v_in_location_tmp;
221 SELECT id INTO v_id_tmp_tmp FROM core_geoip_country WHERE code = v_iso_tmp;
223 INSERT INTO core_geoip_city (name, country_id, division_id) VALUES (v_local_name, v_id_tmp_tmp, v_id_tmp);
228 IF v_geo_lat IS NOT NULL OR v_geo_lng IS NOT NULL THEN
229 SET v_id_tmp = LAST_INSERT_ID();
231 INSERT INTO core_geoip_location (latitude, longitude, city_id) VALUES (v_geo_lat, v_geo_lng, v_id_tmp);
237 IF v_count = v_total THEN
249 -----------------------------------------------------------------------------------------------------------------
252 DROP FUNCTION IF EXISTS core_country_locations;
254 CREATE FUNCTION core_country_locations() RETURNS INT DETERMINISTIC
256 DECLARE v_count INT DEFAULT 0;
257 DECLARE v_total INT DEFAULT 0;
259 DECLARE v_geoname_id INT DEFAULT 0;
260 DECLARE v_continent_code TEXT DEFAULT '';
261 DECLARE v_continent_name TEXT DEFAULT '';
262 DECLARE v_country_iso_code TEXT DEFAULT '';
263 DECLARE v_country_name TEXT DEFAULT '';
265 DECLARE v_country_id INT DEFAULT 0;
266 DECLARE v_continent_id INT DEFAULT 0;
268 DECLARE csr CURSOR FOR
270 geoname_id,continent_code,continent_name,country_iso_code,country_name
274 SELECT COUNT(geoname_id) INTO v_total FROM country_locations;
280 FETCH csr INTO v_geoname_id,v_continent_code,v_continent_name,v_country_iso_code,v_country_name;
282 SET v_count = v_count + 1;
284 SET v_country_id = 0;
285 SET v_continent_id = 0;
287 IF (v_continent_code != '') THEN
288 SELECT id INTO v_continent_id FROM core_geoip_continent WHERE code = v_continent_code;
290 IF v_continent_id = 0 THEN
291 INSERT INTO core_geoip_continent (code, name) VALUES (v_continent_code, v_continent_name);
292 SET v_continent_id = LAST_INSERT_ID();
297 IF (v_country_iso_code != '') THEN
299 SELECT id INTO v_country_id FROM core_geoip_country WHERE code = v_country_iso_code;
301 IF v_country_id = 0 THEN
302 INSERT INTO core_geoip_country (code, name, continent_id) VALUES (v_country_iso_code, v_country_name, v_continent_id);
307 IF v_count = v_total THEN
320 DROP FUNCTION IF EXISTS core_country_blocks;
322 CREATE FUNCTION core_country_blocks() RETURNS INT DETERMINISTIC
324 DECLARE v_count INT DEFAULT 0;
325 DECLARE v_total INT DEFAULT 0;
327 DECLARE v_geoname_id INT DEFAULT 0;
328 DECLARE v_network_start_ip TEXT DEFAULT '';
329 DECLARE v_network_mask_length INT DEFAULT 0;
330 DECLARE v_is_anonymous_proxy INT DEFAULT 0;
331 DECLARE v_is_satellite_provider INT DEFAULT 0;
333 DECLARE v_country_iso_code TEXT DEFAULT '';
335 DECLARE v_country_id INT DEFAULT 0;
337 DECLARE csr CURSOR FOR
339 network_start_ip,network_mask_length,geoname_id,is_anonymous_proxy,is_satellite_provider
345 registered_country_geoname_id != 0
347 geoname_id = registered_country_geoname_id
349 network_start_ip REGEXP '::ffff:[0-9]+.[0-9]+.[0-9]+.[0-9]+$';
351 SELECT COUNT(network_start_ip) INTO v_total FROM country_blocks WHERE geoname_id != 0 AND registered_country_geoname_id != 0 AND geoname_id = registered_country_geoname_id AND network_start_ip REGEXP '::ffff:[0-9]+.[0-9]+.[0-9]+.[0-9]+$';
357 FETCH csr INTO v_network_start_ip,v_network_mask_length,v_geoname_id,v_is_anonymous_proxy,v_is_satellite_provider;
359 SET v_count = v_count + 1;
361 SET v_country_id = 0;
363 SELECT country_iso_code INTO v_country_iso_code FROM country_locations WHERE geoname_id = v_geoname_id;
365 IF v_country_iso_code != '' THEN
366 SELECT id INTO v_country_id FROM core_geoip_country WHERE code = v_country_iso_code;
368 IF v_country_id != 0 THEN
369 INSERT INTO core_geoip_country_network_mapping (start_ip, mask_length, country_id, is_anonymous_proxy, is_satellite_provider) VALUES (SUBSTRING_INDEX(v_network_start_ip,':','-1'), POW(2, 128-v_network_mask_length),v_country_id,v_is_anonymous_proxy,v_is_satellite_provider);
374 IF v_count = v_total THEN
390 DROP FUNCTION IF EXISTS core_city_locations;
392 CREATE FUNCTION core_city_locations() RETURNS INT DETERMINISTIC
394 DECLARE v_count INT DEFAULT 0;
395 DECLARE v_total INT DEFAULT 0;
397 DECLARE v_country_iso_code TEXT DEFAULT '';
398 DECLARE v_subdivision_iso_code TEXT DEFAULT '';
399 DECLARE v_subdivision_name TEXT DEFAULT '';
400 DECLARE v_city_name TEXT DEFAULT '';
401 DECLARE v_metro_code TEXT DEFAULT '';
402 DECLARE v_time_zone TEXT DEFAULT '';
404 DECLARE v_country_id INT DEFAULT 0;
405 DECLARE v_division_id INT DEFAULT 0;
406 DECLARE v_city_id INT DEFAULT 0;
408 DECLARE csr CURSOR FOR
410 country_iso_code,subdivision_iso_code,subdivision_name,city_name,metro_code,time_zone
414 subdivision_name != '' OR city_name != '';
416 SELECT COUNT(geoname_id) INTO v_total FROM city_locations WHERE subdivision_name != '' OR city_name != '';;
422 FETCH csr INTO v_country_iso_code,v_subdivision_iso_code,v_subdivision_name,v_city_name,v_metro_code,v_time_zone;
424 SET v_count = v_count + 1;
426 SET v_country_id = 0;
427 SET v_division_id = 0;
431 IF (v_country_iso_code != '') THEN
432 SELECT id INTO v_country_id FROM core_geoip_country WHERE code = v_country_iso_code;
435 IF v_subdivision_name != '' THEN
436 SELECT id INTO v_division_id FROM core_geoip_division WHERE name = v_subdivision_name AND country_id = v_country_id;
438 IF v_division_id = 0 THEN
439 INSERT INTO core_geoip_division (code, name, country_id) VALUES (v_subdivision_iso_code, v_subdivision_name, v_country_id);
440 SET v_division_id = LAST_INSERT_ID();
446 IF v_city_name != '' THEN
448 SELECT id INTO v_city_id FROM core_geoip_city WHERE name = v_city_name AND country_id = v_country_id AND division_id = v_division_id;
450 IF v_city_id = 0 THEN
451 INSERT INTO core_geoip_city (name, country_id, division_id, metro_code, time_zone) VALUES (v_city_name, v_country_id, v_division_id, v_metro_code, v_time_zone);
457 IF v_count = v_total THEN
471 DROP FUNCTION IF EXISTS core_city_blocks_mapping;
473 CREATE FUNCTION core_city_blocks_mapping() RETURNS INT DETERMINISTIC
475 DECLARE v_count INT DEFAULT 0;
476 DECLARE v_total INT DEFAULT 0;
479 DECLARE v_geoname_id INT DEFAULT 0;
481 DECLARE v_country_id INT DEFAULT 0;
482 DECLARE v_divison_id INT DEFAULT 0;
483 DECLARE v_city_id INT DEFAULT 0;
484 DECLARE v_mapping_id INT DEFAULT 0;
485 DECLARE v_country_iso_code TEXT DEFAULT '';
486 DECLARE v_subdivision_name TEXT DEFAULT '';
487 DECLARE v_city_name TEXT DEFAULT '';
489 DECLARE csr CURSOR FOR
497 network_start_ip REGEXP '::ffff:[0-9]+.[0-9]+.[0-9]+.[0-9]+$';
499 SELECT COUNT(DISTINCT(geoname_id)) INTO v_total FROM city_blocks WHERE geoname_id != 0 AND network_start_ip REGEXP '::ffff:[0-9]+.[0-9]+.[0-9]+.[0-9]+$';
505 FETCH csr INTO v_geoname_id;
507 SET v_count = v_count + 1;
509 SET v_country_id = 0;
510 SET v_divison_id = 0;
512 SET v_mapping_id = 0;
514 SET v_country_iso_code = '';
515 SET v_subdivision_name = '';
516 SET v_city_name = '';
518 SELECT id INTO v_mapping_id FROM city_blocks_mapping WHERE geoname_id = v_geoname_id AND city_id = v_city_id;
520 IF v_mapping_id = 0 THEN
521 SELECT country_iso_code,subdivision_name,city_name INTO v_country_iso_code, v_subdivision_name, v_city_name FROM city_locations WHERE geoname_id = v_geoname_id;
523 IF v_country_iso_code != '' THEN
524 SELECT id INTO v_country_id FROM core_geoip_country WHERE code = v_country_iso_code;
527 IF v_subdivision_name != '' THEN
528 SELECT id INTO v_divison_id FROM core_geoip_division WHERE name = v_subdivision_name AND country_id = v_country_id;
531 SELECT id INTO v_city_id FROM core_geoip_city WHERE name = v_city_name AND country_id = v_country_id AND division_id = v_divison_id;
533 IF v_city_id != 0 THEN
534 INSERT INTO city_blocks_mapping (geoname_id, city_id) VALUES (v_geoname_id, v_city_id);
541 IF v_count = v_total THEN
555 -- DROP FUNCTION IF EXISTS core_city_blocks;
557 -- CREATE FUNCTION core_city_blocks() RETURNS INT DETERMINISTIC
559 -- DECLARE v_count INT DEFAULT 0;
560 -- DECLARE v_total INT DEFAULT 0;
562 -- DECLARE v_network_start_ip TEXT DEFAULT '';
563 -- DECLARE v_network_mask_length INT DEFAULT 0;
564 -- DECLARE v_geoname_id INT DEFAULT 0;
565 -- DECLARE v_latitude DECIMAL(11,8) DEFAULT 0;
566 -- DECLARE v_longitude DECIMAL(11,8) DEFAULT 0;
567 -- DECLARE v_is_anonymous_proxy INT DEFAULT 0;
568 -- DECLARE v_is_satellite_provider INT DEFAULT 0;
571 -- DECLARE v_city_id INT DEFAULT 0;
572 -- DECLARE v_mapping_id INT DEFAULT 0;
574 -- DECLARE csr CURSOR FOR
576 -- network_start_ip,network_mask_length,geoname_id,latitude, longitude, is_anonymous_proxy,is_satellite_provider
582 -- network_start_ip REGEXP '::ffff:[0-9]+.[0-9]+.[0-9]+.[0-9]+$';
584 -- SELECT COUNT(network_start_ip) INTO v_total FROM city_blocks WHERE geoname_id != 0 AND network_start_ip REGEXP '::ffff:[0-9]+.[0-9]+.[0-9]+.[0-9]+$';
590 -- FETCH csr INTO v_network_start_ip,v_network_mask_length,v_geoname_id,v_latitude,v_longitude,v_is_anonymous_proxy,v_is_satellite_provider;
592 -- SET v_count = v_count + 1;
594 -- SET v_city_id = 0;
595 -- SET v_mapping_id = 0;
597 -- SELECT id, city_id INTO v_mapping_id, v_city_id FROM city_blocks_mapping WHERE geoname_id = v_geoname_id;
599 -- IF v_mapping_id != 0 THEN
601 -- INSERT INTO core_geoip_location (latitude, longitude, city_id) VALUES (v_latitude, v_longitude, v_city_id);
603 -- INSERT INTO core_geoip_city_network_mapping (start_ip, mask_length, city_id, is_anonymous_proxy, is_satellite_provider) VALUES (SUBSTRING_INDEX(v_network_start_ip,':','-1'), POW(2, 128-v_network_mask_length),v_city_id,v_is_anonymous_proxy,v_is_satellite_provider);
607 -- IF v_count = v_total THEN