SET v_count = 0;
OPEN co_csr;
+
co_loop: LOOP
FETCH co_csr INTO v_id,v_iso,v_local_name,v_in_location;
DECLARE v_geo_lat INT DEFAULT 0;
DECLARE v_geo_lng INT DEFAULT 0;
- DECLARE v_id_tmp INT DEFAULT 0;
+ DECLARE v_country_id INT DEFAULT 0;
DECLARE v_iso_tmp TEXT DEFAULT '';
- DECLARE v_local_name_tmp TEXT DEFAULT '';
- DECLARE v_type_tmp TEXT DEFAULT '';
- DECLARE v_in_location_tmp INT DEFAULT 0;
- DECLARE v_id_tmp_tmp INT DEFAULT 0;
+ DECLARE v_division_id INT DEFAULT 0;
DECLARE re_csr CURSOR FOR
SELECT
SET v_count = v_count + 1;
- SET v_id_tmp = 0;
+ SET v_country_id = 0;
+ SET v_division_id = 0;
+ SET v_iso_tmp = '';
- SELECT id INTO v_id_tmp FROM core_geoip_division WHERE name = v_local_name;
+ IF v_in_location IS NOT NULL THEN
+ SELECT iso INTO v_iso_tmp FROM meta_location WHERE id = v_in_location;
- IF(v_id_tmp = 0) THEN
- IF v_in_location IS NOT NULL THEN
- SELECT iso, local_name, type INTO v_iso_tmp, v_local_name_tmp, v_type_tmp FROM meta_location WHERE id = v_in_location;
-
- SELECT id INTO v_id_tmp FROM core_geoip_country WHERE code = v_iso_tmp;
+ SELECT id INTO v_country_id FROM core_geoip_country WHERE code = v_iso_tmp;
+
+ END IF;
- END IF;
+ SELECT
+ id
+ INTO
+ v_division_id
+ FROM
+ core_geoip_division
+ WHERE
+ (
+ name = v_local_name
+ OR
+ (
+ name LIKE (CONCAT('%', v_local_name, '%'))
+ AND
+ code = SUBSTRING_INDEX(v_iso, '-', -1)
+ )
+ )
+ AND country_id = v_country_id;
+
+ IF v_division_id = 0 THEN
- INSERT INTO core_geoip_division (code, name, country_id) VALUES (v_iso, v_local_name, v_id_tmp);
+ INSERT INTO core_geoip_division (code, name, country_id) VALUES (v_iso, v_local_name, v_country_id);
END IF;
-- ITERATE re_loop;
DECLARE v_geo_lat INT DEFAULT 0;
DECLARE v_geo_lng INT DEFAULT 0;
- DECLARE v_id_tmp INT DEFAULT 0;
+
DECLARE v_iso_tmp TEXT DEFAULT '';
+ DECLARE v_iso_tmp_tmp TEXT DEFAULT '';
DECLARE v_local_name_tmp TEXT DEFAULT '';
DECLARE v_type_tmp TEXT DEFAULT '';
DECLARE v_in_location_tmp INT DEFAULT 0;
- DECLARE v_id_tmp_tmp INT DEFAULT 0;
+ DECLARE v_country_id INT DEFAULT 0;
+ DECLARE v_division_id INT DEFAULT 0;
+ DECLARE v_city_id INT DEFAULT 0;
DECLARE ci_csr CURSOR FOR
SELECT
SET v_count = v_count + 1;
- SET v_id_tmp = 0;
- SET v_id_tmp_tmp = 0;
+ SET v_country_id = 0;
+ SET v_division_id = 0;
+ SET v_city_id = 0;
- SELECT id INTO v_id_tmp FROM core_geoip_city WHERE name = v_local_name LIMIT 1;
+ SET v_iso_tmp = '';
+ SET v_iso_tmp_tmp = '';
+ SET v_local_name_tmp = '';
+ SET v_type_tmp = '';
+ SET v_in_location_tmp = 0;
+
+ IF v_in_location IS NOT NULL THEN
+ 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;
- IF(v_id_tmp = 0) THEN
- IF v_in_location IS NOT NULL THEN
+ IF v_type_tmp = 'CO' THEN
+ SELECT id INTO v_country_id FROM core_geoip_country WHERE code = v_iso_tmp;
+ END IF;
- 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;
-
- IF v_type_tmp = 'CO' THEN
- SELECT id INTO v_id_tmp FROM core_geoip_country WHERE code = v_iso_tmp;
-
- INSERT INTO core_geoip_city (name, country_id) VALUES (v_local_name, v_id_tmp);
- END IF;
+ IF v_type_tmp = 'RE' THEN
+ IF v_in_location_tmp != 0 THEN
+
+ SELECT iso INTO v_iso_tmp_tmp FROM meta_location WHERE id = v_in_location_tmp;
+
+ SELECT id INTO v_country_id FROM core_geoip_country WHERE code = v_iso_tmp_tmp;
- IF v_type_tmp = 'RE' THEN
- SELECT id INTO v_id_tmp FROM core_geoip_division WHERE name = v_local_name_tmp;
-
- 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;
-
- SELECT id INTO v_id_tmp_tmp FROM core_geoip_country WHERE code = v_iso_tmp;
-
- INSERT INTO core_geoip_city (name, country_id, division_id) VALUES (v_local_name, v_id_tmp_tmp, v_id_tmp);
END IF;
-
+
+ SELECT id INTO v_division_id FROM core_geoip_division WHERE name = v_local_name_tmp AND country_id = v_country_id;
+
END IF;
- IF v_geo_lat IS NOT NULL OR v_geo_lng IS NOT NULL THEN
- SET v_id_tmp = LAST_INSERT_ID();
+ END IF;
+
- INSERT INTO core_geoip_location (latitude, longitude, city_id) VALUES (v_geo_lat, v_geo_lng, v_id_tmp);
+
+ 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;
+
+ IF(v_city_id = 0) THEN
+
+ INSERT INTO core_geoip_city (name, country_id, division_id) VALUES (v_local_name, v_country_id, v_division_id);
+
+ SET v_city_id = LAST_INSERT_ID();
+
+ IF v_geo_lat IS NOT NULL OR v_geo_lng IS NOT NULL THEN
+
+ INSERT INTO core_geoip_location (latitude, longitude, city_id) VALUES (v_geo_lat, v_geo_lng, v_city_id);
END IF;
-
END IF;
IF v_count = v_total THEN
DELIMITER ;
------------------------------------------------------------------------------------------------------------------
+-- ---------------------------------------------------------------------------------------------------------------
DROP FUNCTION IF EXISTS core_country_locations;
AND
network_start_ip REGEXP '::ffff:[0-9]+.[0-9]+.[0-9]+.[0-9]+$';
- 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]+$';
+ 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]+$';
SET v_count = 0;
OPEN csr;
+
read_loop: LOOP
+
FETCH csr INTO v_network_start_ip,v_network_mask_length,v_geoname_id,v_is_anonymous_proxy,v_is_satellite_provider;
SET v_count = v_count + 1;
projects / Pman.Core / blobdiff