2 -- -- not sure if this is used anywhere...
5 -- DROP FUNCTION IF EXISTS core_cities_merge_country;
7 -- CREATE FUNCTION core_cities_merge_country() RETURNS INT DETERMINISTIC
9 -- DECLARE co_done INT DEFAULT FALSE;
11 -- DECLARE v_count INT DEFAULT 0;
12 -- DECLARE v_total INT DEFAULT 0;
14 -- DECLARE v_id INT DEFAULT 0;
15 -- DECLARE v_iso TEXT DEFAULT '';
16 -- DECLARE v_local_name TEXT DEFAULT '';
17 -- -- DECLARE v_type TEXT DEFAULT '';
18 -- DECLARE v_in_location INT DEFAULT 0;
19 -- DECLARE v_geo_lat INT DEFAULT 0;
20 -- DECLARE v_geo_lng INT DEFAULT 0;
22 -- DECLARE v_id_tmp INT DEFAULT 0;
23 -- DECLARE v_iso_tmp TEXT DEFAULT '';
24 -- DECLARE v_local_name_tmp TEXT DEFAULT '';
25 -- DECLARE v_type_tmp TEXT DEFAULT '';
26 -- DECLARE v_in_location_tmp INT DEFAULT 0;
28 -- DECLARE v_id_tmp_tmp INT DEFAULT 0;
30 -- DECLARE co_csr CURSOR FOR
32 -- id,iso,local_name,in_location
38 -- SELECT COUNT(id) INTO v_total FROM meta_location WHERE type = 'CO';
45 -- FETCH co_csr INTO v_id,v_iso,v_local_name,v_in_location;
47 -- SET v_count = v_count + 1;
51 -- SELECT id INTO v_id_tmp FROM core_geoip_country WHERE code = v_iso;
53 -- IF(v_id_tmp = 0) THEN
54 -- INSERT INTO core_geoip_country (code, name, continent_id) VALUES (v_iso, v_local_name, 0);
57 -- IF v_count = v_total THEN
72 -- DROP FUNCTION IF EXISTS core_cities_merge_division;
74 -- CREATE FUNCTION core_cities_merge_division() RETURNS INT DETERMINISTIC
76 -- DECLARE re_done INT DEFAULT FALSE;
78 -- DECLARE v_count INT DEFAULT 0;
79 -- DECLARE v_total INT DEFAULT 0;
81 -- DECLARE v_id INT DEFAULT 0;
82 -- DECLARE v_iso TEXT DEFAULT '';
83 -- DECLARE v_local_name TEXT DEFAULT '';
84 -- -- DECLARE v_type TEXT DEFAULT '';
85 -- DECLARE v_in_location INT DEFAULT 0;
86 -- DECLARE v_geo_lat INT DEFAULT 0;
87 -- DECLARE v_geo_lng INT DEFAULT 0;
89 -- DECLARE v_country_id INT DEFAULT 0;
90 -- DECLARE v_iso_tmp TEXT DEFAULT '';
92 -- DECLARE v_division_id INT DEFAULT 0;
94 -- DECLARE re_csr CURSOR FOR
96 -- id,iso,local_name,in_location
102 -- SELECT COUNT(id) INTO v_total FROM meta_location WHERE type = 'RE';
108 -- FETCH re_csr INTO v_id,v_iso,v_local_name,v_in_location;
110 -- SET v_count = v_count + 1;
112 -- SET v_country_id = 0;
113 -- SET v_division_id = 0;
114 -- SET v_iso_tmp = '';
116 -- IF v_in_location IS NOT NULL THEN
117 -- SELECT iso INTO v_iso_tmp FROM meta_location WHERE id = v_in_location;
119 -- SELECT id INTO v_country_id FROM core_geoip_country WHERE code = v_iso_tmp;
128 -- core_geoip_division
131 -- name = v_local_name
134 -- name LIKE (CONCAT('%', v_local_name, '%'))
136 -- code = SUBSTRING_INDEX(v_iso, '-', -1)
139 -- AND country_id = v_country_id;
141 -- IF v_division_id = 0 THEN
143 -- INSERT INTO core_geoip_division (code, name, country_id) VALUES (v_iso, v_local_name, v_country_id);
146 -- -- ITERATE re_loop;
148 -- IF v_count = v_total THEN
161 -- DROP FUNCTION IF EXISTS core_cities_merge_city;
163 -- CREATE FUNCTION core_cities_merge_city() RETURNS INT DETERMINISTIC
165 -- DECLARE ci_done INT DEFAULT FALSE;
167 -- DECLARE v_count INT DEFAULT 0;
168 -- DECLARE v_total INT DEFAULT 0;
170 -- DECLARE v_id INT DEFAULT 0;
171 -- DECLARE v_iso TEXT DEFAULT '';
172 -- DECLARE v_local_name TEXT DEFAULT '';
173 -- -- DECLARE v_type TEXT DEFAULT '';
174 -- DECLARE v_in_location INT DEFAULT 0;
175 -- DECLARE v_geo_lat INT DEFAULT 0;
176 -- DECLARE v_geo_lng INT DEFAULT 0;
179 -- DECLARE v_iso_tmp TEXT DEFAULT '';
180 -- DECLARE v_iso_tmp_tmp TEXT DEFAULT '';
181 -- DECLARE v_local_name_tmp TEXT DEFAULT '';
182 -- DECLARE v_type_tmp TEXT DEFAULT '';
183 -- DECLARE v_in_location_tmp INT DEFAULT 0;
185 -- DECLARE v_country_id INT DEFAULT 0;
186 -- DECLARE v_division_id INT DEFAULT 0;
187 -- DECLARE v_city_id INT DEFAULT 0;
189 -- DECLARE ci_csr CURSOR FOR
191 -- id,iso,local_name,in_location,geo_lat,geo_lng
197 -- SELECT COUNT(id) INTO v_total FROM meta_location WHERE type = 'CI';
203 -- FETCH ci_csr INTO v_id,v_iso,v_local_name,v_in_location,v_geo_lat,v_geo_lng;
205 -- SET v_count = v_count + 1;
207 -- SET v_country_id = 0;
208 -- SET v_division_id = 0;
209 -- SET v_city_id = 0;
211 -- SET v_iso_tmp = '';
212 -- SET v_iso_tmp_tmp = '';
213 -- SET v_local_name_tmp = '';
214 -- SET v_type_tmp = '';
215 -- SET v_in_location_tmp = 0;
217 -- IF v_in_location IS NOT NULL THEN
218 -- 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;
220 -- IF v_type_tmp = 'CO' THEN
221 -- SELECT id INTO v_country_id FROM core_geoip_country WHERE code = v_iso_tmp;
224 -- IF v_type_tmp = 'RE' THEN
225 -- IF v_in_location_tmp != 0 THEN
227 -- SELECT iso INTO v_iso_tmp_tmp FROM meta_location WHERE id = v_in_location_tmp;
229 -- SELECT id INTO v_country_id FROM core_geoip_country WHERE code = v_iso_tmp_tmp;
233 -- SELECT id INTO v_division_id FROM core_geoip_division WHERE name = v_local_name_tmp AND country_id = v_country_id;
241 -- 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;
243 -- IF(v_city_id = 0) THEN
245 -- INSERT INTO core_geoip_city (name, country_id, division_id) VALUES (v_local_name, v_country_id, v_division_id);
247 -- SET v_city_id = LAST_INSERT_ID();
249 -- IF v_geo_lat IS NOT NULL OR v_geo_lng IS NOT NULL THEN
251 -- INSERT INTO core_geoip_location (latitude, longitude, city_id) VALUES (v_geo_lat, v_geo_lng, v_city_id);
256 -- IF v_count = v_total THEN
268 -- -- ---------------------------------------------------------------------------------------------------------------
271 -- DROP FUNCTION IF EXISTS core_country_locations;
273 -- CREATE FUNCTION core_country_locations() RETURNS INT DETERMINISTIC
275 -- DECLARE v_count INT DEFAULT 0;
276 -- DECLARE v_total INT DEFAULT 0;
278 -- DECLARE v_geoname_id INT DEFAULT 0;
279 -- DECLARE v_continent_code TEXT DEFAULT '';
280 -- DECLARE v_continent_name TEXT DEFAULT '';
281 -- DECLARE v_country_iso_code TEXT DEFAULT '';
282 -- DECLARE v_country_name TEXT DEFAULT '';
284 -- DECLARE v_country_id INT DEFAULT 0;
285 -- DECLARE v_continent_id INT DEFAULT 0;
287 -- DECLARE csr CURSOR FOR
289 -- geoname_id,continent_code,continent_name,country_iso_code,country_name
291 -- country_locations;
293 -- SELECT COUNT(geoname_id) INTO v_total FROM country_locations;
299 -- FETCH csr INTO v_geoname_id,v_continent_code,v_continent_name,v_country_iso_code,v_country_name;
301 -- SET v_count = v_count + 1;
303 -- SET v_country_id = 0;
304 -- SET v_continent_id = 0;
306 -- IF (v_continent_code != '') THEN
307 -- SELECT id INTO v_continent_id FROM core_geoip_continent WHERE code = v_continent_code;
309 -- IF v_continent_id = 0 THEN
310 -- INSERT INTO core_geoip_continent (code, name) VALUES (v_continent_code, v_continent_name);
311 -- SET v_continent_id = LAST_INSERT_ID();
316 -- IF (v_country_iso_code != '') THEN
318 -- SELECT id INTO v_country_id FROM core_geoip_country WHERE code = v_country_iso_code;
320 -- IF v_country_id = 0 THEN
321 -- INSERT INTO core_geoip_country (code, name, continent_id) VALUES (v_country_iso_code, v_country_name, v_continent_id);
326 -- IF v_count = v_total THEN
339 -- DROP FUNCTION IF EXISTS core_country_blocks;
341 -- CREATE FUNCTION core_country_blocks() RETURNS INT DETERMINISTIC
343 -- DECLARE v_count INT DEFAULT 0;
344 -- DECLARE v_total INT DEFAULT 0;
346 -- DECLARE v_geoname_id INT DEFAULT 0;
347 -- DECLARE v_network_start_ip TEXT DEFAULT '';
348 -- DECLARE v_network_mask_length INT DEFAULT 0;
349 -- DECLARE v_is_anonymous_proxy INT DEFAULT 0;
350 -- DECLARE v_is_satellite_provider INT DEFAULT 0;
352 -- DECLARE v_country_iso_code TEXT DEFAULT '';
354 -- DECLARE v_country_id INT DEFAULT 0;
356 -- DECLARE csr CURSOR FOR
358 -- network_start_ip,network_mask_length,geoname_id,is_anonymous_proxy,is_satellite_provider
364 -- registered_country_geoname_id != 0
366 -- geoname_id = registered_country_geoname_id
368 -- network_start_ip REGEXP '::ffff:[0-9]+.[0-9]+.[0-9]+.[0-9]+$';
370 -- SELECT COUNT(network_start_ip) INTO v_total FROM country_blocks
371 -- WHERE geoname_id != 0 AND registered_country_geoname_id != 0
372 -- AND geoname_id = registered_country_geoname_id AND network_start_ip REGEXP '::ffff:[0-9]+.[0-9]+.[0-9]+.[0-9]+$';
380 -- FETCH csr INTO v_network_start_ip,v_network_mask_length,v_geoname_id,v_is_anonymous_proxy,v_is_satellite_provider;
382 -- SET v_count = v_count + 1;
384 -- SET v_country_id = 0;
386 -- SELECT country_iso_code INTO v_country_iso_code FROM country_locations WHERE geoname_id = v_geoname_id;
388 -- IF v_country_iso_code != '' THEN
389 -- SELECT id INTO v_country_id FROM core_geoip_country WHERE code = v_country_iso_code;
391 -- IF v_country_id != 0 THEN
392 -- 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);
397 -- IF v_count = v_total THEN
413 -- DROP FUNCTION IF EXISTS core_city_locations;
415 -- CREATE FUNCTION core_city_locations() RETURNS INT DETERMINISTIC
417 -- DECLARE v_count INT DEFAULT 0;
418 -- DECLARE v_total INT DEFAULT 0;
420 -- DECLARE v_country_iso_code TEXT DEFAULT '';
421 -- DECLARE v_subdivision_iso_code TEXT DEFAULT '';
422 -- DECLARE v_subdivision_name TEXT DEFAULT '';
423 -- DECLARE v_city_name TEXT DEFAULT '';
424 -- DECLARE v_metro_code TEXT DEFAULT '';
425 -- DECLARE v_time_zone TEXT DEFAULT '';
427 -- DECLARE v_country_id INT DEFAULT 0;
428 -- DECLARE v_division_id INT DEFAULT 0;
429 -- DECLARE v_city_id INT DEFAULT 0;
431 -- DECLARE csr CURSOR FOR
433 -- country_iso_code,subdivision_iso_code,subdivision_name,city_name,metro_code,time_zone
437 -- subdivision_name != '' OR city_name != '';
439 -- SELECT COUNT(geoname_id) INTO v_total FROM city_locations WHERE subdivision_name != '' OR city_name != '';;
445 -- FETCH csr INTO v_country_iso_code,v_subdivision_iso_code,v_subdivision_name,v_city_name,v_metro_code,v_time_zone;
447 -- SET v_count = v_count + 1;
449 -- SET v_country_id = 0;
450 -- SET v_division_id = 0;
451 -- SET v_city_id = 0;
454 -- IF (v_country_iso_code != '') THEN
455 -- SELECT id INTO v_country_id FROM core_geoip_country WHERE code = v_country_iso_code;
458 -- IF v_subdivision_name != '' THEN
459 -- SELECT id INTO v_division_id FROM core_geoip_division WHERE name = v_subdivision_name AND country_id = v_country_id;
461 -- IF v_division_id = 0 THEN
462 -- INSERT INTO core_geoip_division (code, name, country_id) VALUES (v_subdivision_iso_code, v_subdivision_name, v_country_id);
463 -- SET v_division_id = LAST_INSERT_ID();
469 -- IF v_city_name != '' THEN
471 -- 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;
473 -- IF v_city_id = 0 THEN
474 -- 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);
480 -- IF v_count = v_total THEN
494 -- DROP FUNCTION IF EXISTS core_city_blocks_mapping;
496 -- CREATE FUNCTION core_city_blocks_mapping() RETURNS INT DETERMINISTIC
498 -- DECLARE v_count INT DEFAULT 0;
499 -- DECLARE v_total INT DEFAULT 0;
502 -- DECLARE v_geoname_id INT DEFAULT 0;
504 -- DECLARE v_country_id INT DEFAULT 0;
505 -- DECLARE v_divison_id INT DEFAULT 0;
506 -- DECLARE v_city_id INT DEFAULT 0;
507 -- DECLARE v_mapping_id INT DEFAULT 0;
508 -- DECLARE v_country_iso_code TEXT DEFAULT '';
509 -- DECLARE v_subdivision_name TEXT DEFAULT '';
510 -- DECLARE v_city_name TEXT DEFAULT '';
512 -- DECLARE csr CURSOR FOR
514 -- DISTINCT(geoname_id)
520 -- network_start_ip REGEXP '::ffff:[0-9]+.[0-9]+.[0-9]+.[0-9]+$';
522 -- 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]+$';
528 -- FETCH csr INTO v_geoname_id;
530 -- SET v_count = v_count + 1;
532 -- SET v_country_id = 0;
533 -- SET v_divison_id = 0;
534 -- SET v_city_id = 0;
535 -- SET v_mapping_id = 0;
537 -- SET v_country_iso_code = '';
538 -- SET v_subdivision_name = '';
539 -- SET v_city_name = '';
541 -- SELECT id INTO v_mapping_id FROM city_blocks_mapping WHERE geoname_id = v_geoname_id AND city_id = v_city_id;
543 -- IF v_mapping_id = 0 THEN
544 -- 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;
546 -- IF v_country_iso_code != '' THEN
547 -- SELECT id INTO v_country_id FROM core_geoip_country WHERE code = v_country_iso_code;
550 -- IF v_subdivision_name != '' THEN
551 -- SELECT id INTO v_divison_id FROM core_geoip_division WHERE name = v_subdivision_name AND country_id = v_country_id;
554 -- 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;
556 -- IF v_city_id != 0 THEN
557 -- INSERT INTO city_blocks_mapping (geoname_id, city_id) VALUES (v_geoname_id, v_city_id);
564 -- IF v_count = v_total THEN
578 -- -- DROP FUNCTION IF EXISTS core_city_blocks;
580 -- -- CREATE FUNCTION core_city_blocks() RETURNS INT DETERMINISTIC
582 -- -- DECLARE v_count INT DEFAULT 0;
583 -- -- DECLARE v_total INT DEFAULT 0;
585 -- -- DECLARE v_network_start_ip TEXT DEFAULT '';
586 -- -- DECLARE v_network_mask_length INT DEFAULT 0;
587 -- -- DECLARE v_geoname_id INT DEFAULT 0;
588 -- -- DECLARE v_latitude DECIMAL(11,8) DEFAULT 0;
589 -- -- DECLARE v_longitude DECIMAL(11,8) DEFAULT 0;
590 -- -- DECLARE v_is_anonymous_proxy INT DEFAULT 0;
591 -- -- DECLARE v_is_satellite_provider INT DEFAULT 0;
594 -- -- DECLARE v_city_id INT DEFAULT 0;
595 -- -- DECLARE v_mapping_id INT DEFAULT 0;
597 -- -- DECLARE csr CURSOR FOR
599 -- -- network_start_ip,network_mask_length,geoname_id,latitude, longitude, is_anonymous_proxy,is_satellite_provider
603 -- -- geoname_id != 0
605 -- -- network_start_ip REGEXP '::ffff:[0-9]+.[0-9]+.[0-9]+.[0-9]+$';
607 -- -- 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]+$';
609 -- -- SET v_count = 0;
612 -- -- read_loop: LOOP
613 -- -- 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;
615 -- -- SET v_count = v_count + 1;
617 -- -- SET v_city_id = 0;
618 -- -- SET v_mapping_id = 0;
620 -- -- SELECT id, city_id INTO v_mapping_id, v_city_id FROM city_blocks_mapping WHERE geoname_id = v_geoname_id;
622 -- -- IF v_mapping_id != 0 THEN
624 -- -- INSERT INTO core_geoip_location (latitude, longitude, city_id) VALUES (v_latitude, v_longitude, v_city_id);
626 -- -- 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);
630 -- -- IF v_count = v_total THEN
631 -- -- LEAVE read_loop;
637 -- -- RETURN v_count;