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';
42 FETCH co_csr INTO v_id,v_iso,v_local_name,v_in_location;
44 SET v_count = v_count + 1;
48 SELECT id INTO v_id_tmp FROM core_geoip_country WHERE code = v_iso;
51 INSERT INTO core_geoip_country (code, name, continent_id) VALUES (v_iso, v_local_name, 0);
54 IF v_count = v_total THEN
69 DROP FUNCTION IF EXISTS core_cities_merge_division;
71 CREATE FUNCTION core_cities_merge_division() RETURNS INT DETERMINISTIC
73 DECLARE re_done INT DEFAULT FALSE;
75 DECLARE v_count INT DEFAULT 0;
76 DECLARE v_total INT DEFAULT 0;
78 DECLARE v_id INT DEFAULT 0;
79 DECLARE v_iso TEXT DEFAULT '';
80 DECLARE v_local_name TEXT DEFAULT '';
81 -- DECLARE v_type TEXT DEFAULT '';
82 DECLARE v_in_location INT DEFAULT 0;
83 DECLARE v_geo_lat INT DEFAULT 0;
84 DECLARE v_geo_lng INT DEFAULT 0;
86 DECLARE v_country_id INT DEFAULT 0;
87 DECLARE v_iso_tmp TEXT DEFAULT '';
89 DECLARE v_division_id INT DEFAULT 0;
91 DECLARE re_csr CURSOR FOR
93 id,iso,local_name,in_location
99 SELECT COUNT(id) INTO v_total FROM meta_location WHERE type = 'RE';
105 FETCH re_csr INTO v_id,v_iso,v_local_name,v_in_location;
107 SET v_count = v_count + 1;
109 SET v_country_id = 0;
110 SET v_division_id = 0;
113 IF v_in_location IS NOT NULL THEN
114 SELECT iso INTO v_iso_tmp FROM meta_location WHERE id = v_in_location;
116 SELECT id INTO v_country_id FROM core_geoip_country WHERE code = v_iso_tmp;
131 name LIKE (CONCAT('%', v_local_name, '%'))
133 code = SUBSTRING_INDEX(v_iso, '-', -1)
136 AND country_id = v_country_id;
138 IF v_division_id = 0 THEN
140 INSERT INTO core_geoip_division (code, name, country_id) VALUES (v_iso, v_local_name, v_country_id);
145 IF v_count = v_total THEN
158 DROP FUNCTION IF EXISTS core_cities_merge_city;
160 CREATE FUNCTION core_cities_merge_city() RETURNS INT DETERMINISTIC
162 DECLARE ci_done INT DEFAULT FALSE;
164 DECLARE v_count INT DEFAULT 0;
165 DECLARE v_total INT DEFAULT 0;
167 DECLARE v_id INT DEFAULT 0;
168 DECLARE v_iso TEXT DEFAULT '';
169 DECLARE v_local_name TEXT DEFAULT '';
170 -- DECLARE v_type TEXT DEFAULT '';
171 DECLARE v_in_location INT DEFAULT 0;
172 DECLARE v_geo_lat INT DEFAULT 0;
173 DECLARE v_geo_lng INT DEFAULT 0;
176 DECLARE v_iso_tmp TEXT DEFAULT '';
177 DECLARE v_iso_tmp_tmp TEXT DEFAULT '';
178 DECLARE v_local_name_tmp TEXT DEFAULT '';
179 DECLARE v_type_tmp TEXT DEFAULT '';
180 DECLARE v_in_location_tmp INT DEFAULT 0;
182 DECLARE v_country_id INT DEFAULT 0;
183 DECLARE v_division_id INT DEFAULT 0;
184 DECLARE v_city_id INT DEFAULT 0;
186 DECLARE ci_csr CURSOR FOR
188 id,iso,local_name,in_location,geo_lat,geo_lng
194 SELECT COUNT(id) INTO v_total FROM meta_location WHERE type = 'CI';
200 FETCH ci_csr INTO v_id,v_iso,v_local_name,v_in_location,v_geo_lat,v_geo_lng;
202 SET v_count = v_count + 1;
204 SET v_country_id = 0;
205 SET v_division_id = 0;
209 SET v_iso_tmp_tmp = '';
210 SET v_local_name_tmp = '';
212 SET v_in_location_tmp = 0;
214 IF v_in_location IS NOT NULL THEN
215 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;
217 IF v_type_tmp = 'CO' THEN
218 SELECT id INTO v_country_id FROM core_geoip_country WHERE code = v_iso_tmp;
221 IF v_type_tmp = 'RE' THEN
222 IF v_in_location_tmp != 0 THEN
224 SELECT iso INTO v_iso_tmp_tmp FROM meta_location WHERE id = v_in_location_tmp;
226 SELECT id INTO v_country_id FROM core_geoip_country WHERE code = v_iso_tmp_tmp;
230 SELECT id INTO v_division_id FROM core_geoip_division WHERE name = v_local_name_tmp AND country_id = v_country_id;
238 SELECT id INTO v_city_id FROM core_geoip_city WHERE name = v_local_name AND country_id = v_country_id AND division_id = v_division_id;
240 IF(v_city_id = 0) THEN
242 INSERT INTO core_geoip_city (name, country_id, division_id) VALUES (v_local_name, v_country_id, v_division_id);
244 SET v_city_id = LAST_INSERT_ID();
246 IF v_geo_lat IS NOT NULL OR v_geo_lng IS NOT NULL THEN
248 INSERT INTO core_geoip_location (latitude, longitude, city_id) VALUES (v_geo_lat, v_geo_lng, v_city_id);
253 IF v_count = v_total THEN
265 -- ---------------------------------------------------------------------------------------------------------------
268 DROP FUNCTION IF EXISTS core_country_locations;
270 CREATE FUNCTION core_country_locations() RETURNS INT DETERMINISTIC
272 DECLARE v_count INT DEFAULT 0;
273 DECLARE v_total INT DEFAULT 0;
275 DECLARE v_geoname_id INT DEFAULT 0;
276 DECLARE v_continent_code TEXT DEFAULT '';
277 DECLARE v_continent_name TEXT DEFAULT '';
278 DECLARE v_country_iso_code TEXT DEFAULT '';
279 DECLARE v_country_name TEXT DEFAULT '';
281 DECLARE v_country_id INT DEFAULT 0;
282 DECLARE v_continent_id INT DEFAULT 0;
284 DECLARE csr CURSOR FOR
286 geoname_id,continent_code,continent_name,country_iso_code,country_name
290 SELECT COUNT(geoname_id) INTO v_total FROM country_locations;
296 FETCH csr INTO v_geoname_id,v_continent_code,v_continent_name,v_country_iso_code,v_country_name;
298 SET v_count = v_count + 1;
300 SET v_country_id = 0;
301 SET v_continent_id = 0;
303 IF (v_continent_code != '') THEN
304 SELECT id INTO v_continent_id FROM core_geoip_continent WHERE code = v_continent_code;
306 IF v_continent_id = 0 THEN
307 INSERT INTO core_geoip_continent (code, name) VALUES (v_continent_code, v_continent_name);
308 SET v_continent_id = LAST_INSERT_ID();
313 IF (v_country_iso_code != '') THEN
315 SELECT id INTO v_country_id FROM core_geoip_country WHERE code = v_country_iso_code;
317 IF v_country_id = 0 THEN
318 INSERT INTO core_geoip_country (code, name, continent_id) VALUES (v_country_iso_code, v_country_name, v_continent_id);
323 IF v_count = v_total THEN
336 DROP FUNCTION IF EXISTS core_country_blocks;
338 CREATE FUNCTION core_country_blocks() RETURNS INT DETERMINISTIC
340 DECLARE v_count INT DEFAULT 0;
341 DECLARE v_total INT DEFAULT 0;
343 DECLARE v_geoname_id INT DEFAULT 0;
344 DECLARE v_network_start_ip TEXT DEFAULT '';
345 DECLARE v_network_mask_length INT DEFAULT 0;
346 DECLARE v_is_anonymous_proxy INT DEFAULT 0;
347 DECLARE v_is_satellite_provider INT DEFAULT 0;
349 DECLARE v_country_iso_code TEXT DEFAULT '';
351 DECLARE v_country_id INT DEFAULT 0;
353 DECLARE csr CURSOR FOR
355 network_start_ip,network_mask_length,geoname_id,is_anonymous_proxy,is_satellite_provider
361 registered_country_geoname_id != 0
363 geoname_id = registered_country_geoname_id
365 network_start_ip REGEXP '::ffff:[0-9]+.[0-9]+.[0-9]+.[0-9]+$';
367 SELECT COUNT(network_start_ip) INTO v_total FROM country_blocks
368 WHERE geoname_id != 0 AND registered_country_geoname_id != 0
369 AND geoname_id = registered_country_geoname_id AND network_start_ip REGEXP '::ffff:[0-9]+.[0-9]+.[0-9]+.[0-9]+$';
377 FETCH csr INTO v_network_start_ip,v_network_mask_length,v_geoname_id,v_is_anonymous_proxy,v_is_satellite_provider;
379 SET v_count = v_count + 1;
381 SET v_country_id = 0;
383 SELECT country_iso_code INTO v_country_iso_code FROM country_locations WHERE geoname_id = v_geoname_id;
385 IF v_country_iso_code != '' THEN
386 SELECT id INTO v_country_id FROM core_geoip_country WHERE code = v_country_iso_code;
388 IF v_country_id != 0 THEN
389 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);
394 IF v_count = v_total THEN
410 DROP FUNCTION IF EXISTS core_city_locations;
412 CREATE FUNCTION core_city_locations() RETURNS INT DETERMINISTIC
414 DECLARE v_count INT DEFAULT 0;
415 DECLARE v_total INT DEFAULT 0;
417 DECLARE v_country_iso_code TEXT DEFAULT '';
418 DECLARE v_subdivision_iso_code TEXT DEFAULT '';
419 DECLARE v_subdivision_name TEXT DEFAULT '';
420 DECLARE v_city_name TEXT DEFAULT '';
421 DECLARE v_metro_code TEXT DEFAULT '';
422 DECLARE v_time_zone TEXT DEFAULT '';
424 DECLARE v_country_id INT DEFAULT 0;
425 DECLARE v_division_id INT DEFAULT 0;
426 DECLARE v_city_id INT DEFAULT 0;
428 DECLARE csr CURSOR FOR
430 country_iso_code,subdivision_iso_code,subdivision_name,city_name,metro_code,time_zone
434 subdivision_name != '' OR city_name != '';
436 SELECT COUNT(geoname_id) INTO v_total FROM city_locations WHERE subdivision_name != '' OR city_name != '';;
442 FETCH csr INTO v_country_iso_code,v_subdivision_iso_code,v_subdivision_name,v_city_name,v_metro_code,v_time_zone;
444 SET v_count = v_count + 1;
446 SET v_country_id = 0;
447 SET v_division_id = 0;
451 IF (v_country_iso_code != '') THEN
452 SELECT id INTO v_country_id FROM core_geoip_country WHERE code = v_country_iso_code;
455 IF v_subdivision_name != '' THEN
456 SELECT id INTO v_division_id FROM core_geoip_division WHERE name = v_subdivision_name AND country_id = v_country_id;
458 IF v_division_id = 0 THEN
459 INSERT INTO core_geoip_division (code, name, country_id) VALUES (v_subdivision_iso_code, v_subdivision_name, v_country_id);
460 SET v_division_id = LAST_INSERT_ID();
466 IF v_city_name != '' THEN
468 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;
470 IF v_city_id = 0 THEN
471 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);
477 IF v_count = v_total THEN
491 DROP FUNCTION IF EXISTS core_city_blocks_mapping;
493 CREATE FUNCTION core_city_blocks_mapping() RETURNS INT DETERMINISTIC
495 DECLARE v_count INT DEFAULT 0;
496 DECLARE v_total INT DEFAULT 0;
499 DECLARE v_geoname_id INT DEFAULT 0;
501 DECLARE v_country_id INT DEFAULT 0;
502 DECLARE v_divison_id INT DEFAULT 0;
503 DECLARE v_city_id INT DEFAULT 0;
504 DECLARE v_mapping_id INT DEFAULT 0;
505 DECLARE v_country_iso_code TEXT DEFAULT '';
506 DECLARE v_subdivision_name TEXT DEFAULT '';
507 DECLARE v_city_name TEXT DEFAULT '';
509 DECLARE csr CURSOR FOR
517 network_start_ip REGEXP '::ffff:[0-9]+.[0-9]+.[0-9]+.[0-9]+$';
519 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]+$';
525 FETCH csr INTO v_geoname_id;
527 SET v_count = v_count + 1;
529 SET v_country_id = 0;
530 SET v_divison_id = 0;
532 SET v_mapping_id = 0;
534 SET v_country_iso_code = '';
535 SET v_subdivision_name = '';
536 SET v_city_name = '';
538 SELECT id INTO v_mapping_id FROM city_blocks_mapping WHERE geoname_id = v_geoname_id AND city_id = v_city_id;
540 IF v_mapping_id = 0 THEN
541 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;
543 IF v_country_iso_code != '' THEN
544 SELECT id INTO v_country_id FROM core_geoip_country WHERE code = v_country_iso_code;
547 IF v_subdivision_name != '' THEN
548 SELECT id INTO v_divison_id FROM core_geoip_division WHERE name = v_subdivision_name AND country_id = v_country_id;
551 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;
553 IF v_city_id != 0 THEN
554 INSERT INTO city_blocks_mapping (geoname_id, city_id) VALUES (v_geoname_id, v_city_id);
561 IF v_count = v_total THEN
575 -- DROP FUNCTION IF EXISTS core_city_blocks;
577 -- CREATE FUNCTION core_city_blocks() RETURNS INT DETERMINISTIC
579 -- DECLARE v_count INT DEFAULT 0;
580 -- DECLARE v_total INT DEFAULT 0;
582 -- DECLARE v_network_start_ip TEXT DEFAULT '';
583 -- DECLARE v_network_mask_length INT DEFAULT 0;
584 -- DECLARE v_geoname_id INT DEFAULT 0;
585 -- DECLARE v_latitude DECIMAL(11,8) DEFAULT 0;
586 -- DECLARE v_longitude DECIMAL(11,8) DEFAULT 0;
587 -- DECLARE v_is_anonymous_proxy INT DEFAULT 0;
588 -- DECLARE v_is_satellite_provider INT DEFAULT 0;
591 -- DECLARE v_city_id INT DEFAULT 0;
592 -- DECLARE v_mapping_id INT DEFAULT 0;
594 -- DECLARE csr CURSOR FOR
596 -- network_start_ip,network_mask_length,geoname_id,latitude, longitude, is_anonymous_proxy,is_satellite_provider
602 -- network_start_ip REGEXP '::ffff:[0-9]+.[0-9]+.[0-9]+.[0-9]+$';
604 -- 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]+$';
610 -- 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;
612 -- SET v_count = v_count + 1;
614 -- SET v_city_id = 0;
615 -- SET v_mapping_id = 0;
617 -- SELECT id, city_id INTO v_mapping_id, v_city_id FROM city_blocks_mapping WHERE geoname_id = v_geoname_id;
619 -- IF v_mapping_id != 0 THEN
621 -- INSERT INTO core_geoip_location (latitude, longitude, city_id) VALUES (v_latitude, v_longitude, v_city_id);
623 -- 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);
627 -- IF v_count = v_total THEN