--- not sure if this is used anywhere...
-
-
-DROP FUNCTION IF EXISTS core_cities_merge_country;
-DELIMITER $$
-CREATE FUNCTION core_cities_merge_country() RETURNS INT DETERMINISTIC
- BEGIN
- DECLARE co_done INT DEFAULT FALSE;
-
- DECLARE v_count INT DEFAULT 0;
- DECLARE v_total INT DEFAULT 0;
-
- DECLARE v_id INT DEFAULT 0;
- DECLARE v_iso TEXT DEFAULT '';
- DECLARE v_local_name TEXT DEFAULT '';
--- DECLARE v_type TEXT DEFAULT '';
- DECLARE v_in_location INT DEFAULT 0;
- 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_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 co_csr CURSOR FOR
- SELECT
- id,iso,local_name,in_location
- FROM
- meta_location
- WHERE
- type = 'CO';
-
- SELECT COUNT(id) INTO v_total FROM meta_location WHERE type = 'CO';
-
- SET v_count = 0;
-
- OPEN co_csr;
-
- co_loop: LOOP
- FETCH co_csr INTO v_id,v_iso,v_local_name,v_in_location;
-
- SET v_count = v_count + 1;
-
- SET v_id_tmp = 0;
-
- SELECT id INTO v_id_tmp FROM core_geoip_country WHERE code = v_iso;
-
- IF(v_id_tmp = 0) THEN
- INSERT INTO core_geoip_country (code, name, continent_id) VALUES (v_iso, v_local_name, 0);
- END IF;
-
- IF v_count = v_total THEN
- LEAVE co_loop;
- END IF;
-
- END LOOP;
- CLOSE co_csr;
-
-
- RETURN v_count;
- END $$
-DELIMITER ;
-
-
-
-
-DROP FUNCTION IF EXISTS core_cities_merge_division;
-DELIMITER $$
-CREATE FUNCTION core_cities_merge_division() RETURNS INT DETERMINISTIC
- BEGIN
- DECLARE re_done INT DEFAULT FALSE;
-
- DECLARE v_count INT DEFAULT 0;
- DECLARE v_total INT DEFAULT 0;
-
- DECLARE v_id INT DEFAULT 0;
- DECLARE v_iso TEXT DEFAULT '';
- DECLARE v_local_name TEXT DEFAULT '';
--- DECLARE v_type TEXT DEFAULT '';
- DECLARE v_in_location INT DEFAULT 0;
- DECLARE v_geo_lat INT DEFAULT 0;
- DECLARE v_geo_lng INT DEFAULT 0;
-
- DECLARE v_country_id INT DEFAULT 0;
- DECLARE v_iso_tmp TEXT DEFAULT '';
-
- DECLARE v_division_id INT DEFAULT 0;
-
- DECLARE re_csr CURSOR FOR
- SELECT
- id,iso,local_name,in_location
- FROM
- meta_location
- WHERE
- type = 'RE';
-
- SELECT COUNT(id) INTO v_total FROM meta_location WHERE type = 'RE';
-
- SET v_count = 0;
-
- OPEN re_csr;
- re_loop: LOOP
- FETCH re_csr INTO v_id,v_iso,v_local_name,v_in_location;
-
- SET v_count = v_count + 1;
-
- SET v_country_id = 0;
- SET v_division_id = 0;
- SET v_iso_tmp = '';
-
- IF v_in_location IS NOT NULL THEN
- SELECT iso INTO v_iso_tmp FROM meta_location WHERE id = v_in_location;
-
- SELECT id INTO v_country_id FROM core_geoip_country WHERE code = v_iso_tmp;
-
- 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_country_id);
- END IF;
-
--- ITERATE re_loop;
-
- IF v_count = v_total THEN
- LEAVE re_loop;
- END IF;
-
- END LOOP;
- CLOSE re_csr;
-
- RETURN v_count;
- END $$
-DELIMITER ;
-
-
-
-DROP FUNCTION IF EXISTS core_cities_merge_city;
-DELIMITER $$
-CREATE FUNCTION core_cities_merge_city() RETURNS INT DETERMINISTIC
- BEGIN
- DECLARE ci_done INT DEFAULT FALSE;
-
- DECLARE v_count INT DEFAULT 0;
- DECLARE v_total INT DEFAULT 0;
-
- DECLARE v_id INT DEFAULT 0;
- DECLARE v_iso TEXT DEFAULT '';
- DECLARE v_local_name TEXT DEFAULT '';
--- DECLARE v_type TEXT DEFAULT '';
- DECLARE v_in_location INT DEFAULT 0;
- DECLARE v_geo_lat INT DEFAULT 0;
- DECLARE v_geo_lng 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_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
- id,iso,local_name,in_location,geo_lat,geo_lng
- FROM
- meta_location
- WHERE
- type = 'CI';
-
- SELECT COUNT(id) INTO v_total FROM meta_location WHERE type = 'CI';
-
- SET v_count = 0;
-
- OPEN ci_csr;
- ci_loop: LOOP
- FETCH ci_csr INTO v_id,v_iso,v_local_name,v_in_location,v_geo_lat,v_geo_lng;
-
- SET v_count = v_count + 1;
-
- SET v_country_id = 0;
- SET v_division_id = 0;
- SET v_city_id = 0;
-
- 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_type_tmp = 'CO' THEN
- SELECT id INTO v_country_id FROM core_geoip_country WHERE code = v_iso_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;
-
- 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;
-
- END IF;
-
-
-
- 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
- LEAVE ci_loop;
- END IF;
-
- END LOOP;
- CLOSE ci_csr;
-
- RETURN v_count;
- END $$
-DELIMITER ;
-
-
--- ---------------------------------------------------------------------------------------------------------------
-
-
-DROP FUNCTION IF EXISTS core_country_locations;
-DELIMITER $$
-CREATE FUNCTION core_country_locations() RETURNS INT DETERMINISTIC
- BEGIN
- DECLARE v_count INT DEFAULT 0;
- DECLARE v_total INT DEFAULT 0;
-
- DECLARE v_geoname_id INT DEFAULT 0;
- DECLARE v_continent_code TEXT DEFAULT '';
- DECLARE v_continent_name TEXT DEFAULT '';
- DECLARE v_country_iso_code TEXT DEFAULT '';
- DECLARE v_country_name TEXT DEFAULT '';
-
- DECLARE v_country_id INT DEFAULT 0;
- DECLARE v_continent_id INT DEFAULT 0;
-
- DECLARE csr CURSOR FOR
- SELECT
- geoname_id,continent_code,continent_name,country_iso_code,country_name
- FROM
- country_locations;
-
- SELECT COUNT(geoname_id) INTO v_total FROM country_locations;
-
- SET v_count = 0;
-
- OPEN csr;
- read_loop: LOOP
- FETCH csr INTO v_geoname_id,v_continent_code,v_continent_name,v_country_iso_code,v_country_name;
-
- SET v_count = v_count + 1;
-
- SET v_country_id = 0;
- SET v_continent_id = 0;
-
- IF (v_continent_code != '') THEN
- SELECT id INTO v_continent_id FROM core_geoip_continent WHERE code = v_continent_code;
-
- IF v_continent_id = 0 THEN
- INSERT INTO core_geoip_continent (code, name) VALUES (v_continent_code, v_continent_name);
- SET v_continent_id = LAST_INSERT_ID();
- END IF;
-
- END IF;
-
- IF (v_country_iso_code != '') THEN
-
- SELECT id INTO v_country_id FROM core_geoip_country WHERE code = v_country_iso_code;
-
- IF v_country_id = 0 THEN
- INSERT INTO core_geoip_country (code, name, continent_id) VALUES (v_country_iso_code, v_country_name, v_continent_id);
- END IF;
-
- END IF;
-
- IF v_count = v_total THEN
- LEAVE read_loop;
- END IF;
-
- END LOOP;
- CLOSE csr;
-
-
- RETURN v_count;
- END $$
-DELIMITER ;
-
-
-DROP FUNCTION IF EXISTS core_country_blocks;
-DELIMITER $$
-CREATE FUNCTION core_country_blocks() RETURNS INT DETERMINISTIC
- BEGIN
- DECLARE v_count INT DEFAULT 0;
- DECLARE v_total INT DEFAULT 0;
-
- DECLARE v_geoname_id INT DEFAULT 0;
- DECLARE v_network_start_ip TEXT DEFAULT '';
- DECLARE v_network_mask_length INT DEFAULT 0;
- DECLARE v_is_anonymous_proxy INT DEFAULT 0;
- DECLARE v_is_satellite_provider INT DEFAULT 0;
-
- DECLARE v_country_iso_code TEXT DEFAULT '';
-
- DECLARE v_country_id INT DEFAULT 0;
-
- DECLARE csr CURSOR FOR
- SELECT
- network_start_ip,network_mask_length,geoname_id,is_anonymous_proxy,is_satellite_provider
- 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;
-
- SET v_country_id = 0;
-
- SELECT country_iso_code INTO v_country_iso_code FROM country_locations WHERE geoname_id = v_geoname_id;
-
- IF v_country_iso_code != '' THEN
- SELECT id INTO v_country_id FROM core_geoip_country WHERE code = v_country_iso_code;
-
- IF v_country_id != 0 THEN
- 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);
- END IF;
-
- END IF;
-
- IF v_count = v_total THEN
- LEAVE read_loop;
- END IF;
-
- END LOOP;
- CLOSE csr;
-
-
- RETURN v_count;
- END $$
-DELIMITER ;
-
-
-
-
-
-DROP FUNCTION IF EXISTS core_city_locations;
-DELIMITER $$
-CREATE FUNCTION core_city_locations() RETURNS INT DETERMINISTIC
- BEGIN
- DECLARE v_count INT DEFAULT 0;
- DECLARE v_total INT DEFAULT 0;
-
- DECLARE v_country_iso_code TEXT DEFAULT '';
- DECLARE v_subdivision_iso_code TEXT DEFAULT '';
- DECLARE v_subdivision_name TEXT DEFAULT '';
- DECLARE v_city_name TEXT DEFAULT '';
- DECLARE v_metro_code TEXT DEFAULT '';
- DECLARE v_time_zone TEXT DEFAULT '';
-
- DECLARE v_country_id INT DEFAULT 0;
- DECLARE v_division_id INT DEFAULT 0;
- DECLARE v_city_id INT DEFAULT 0;
-
- DECLARE csr CURSOR FOR
- SELECT
- country_iso_code,subdivision_iso_code,subdivision_name,city_name,metro_code,time_zone
- FROM
- city_locations
- WHERE
- subdivision_name != '' OR city_name != '';
-
- SELECT COUNT(geoname_id) INTO v_total FROM city_locations WHERE subdivision_name != '' OR city_name != '';;
-
- SET v_count = 0;
-
- OPEN csr;
- read_loop: LOOP
- FETCH csr INTO v_country_iso_code,v_subdivision_iso_code,v_subdivision_name,v_city_name,v_metro_code,v_time_zone;
-
- SET v_count = v_count + 1;
-
- SET v_country_id = 0;
- SET v_division_id = 0;
- SET v_city_id = 0;
-
-
- IF (v_country_iso_code != '') THEN
- SELECT id INTO v_country_id FROM core_geoip_country WHERE code = v_country_iso_code;
- END IF;
-
- IF v_subdivision_name != '' THEN
- SELECT id INTO v_division_id FROM core_geoip_division WHERE name = v_subdivision_name AND country_id = v_country_id;
-
- IF v_division_id = 0 THEN
- INSERT INTO core_geoip_division (code, name, country_id) VALUES (v_subdivision_iso_code, v_subdivision_name, v_country_id);
- SET v_division_id = LAST_INSERT_ID();
- END IF;
-
-
- END IF;
-
- IF v_city_name != '' THEN
-
- 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;
-
- IF v_city_id = 0 THEN
- 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);
-
- END IF;
-
- END IF;
-
- IF v_count = v_total THEN
- LEAVE read_loop;
- END IF;
-
- END LOOP;
- CLOSE csr;
-
-
- RETURN v_count;
- END $$
-DELIMITER ;
-
-
-
-DROP FUNCTION IF EXISTS core_city_blocks_mapping;
-DELIMITER $$
-CREATE FUNCTION core_city_blocks_mapping() RETURNS INT DETERMINISTIC
- BEGIN
- DECLARE v_count INT DEFAULT 0;
- DECLARE v_total INT DEFAULT 0;
-
-
- DECLARE v_geoname_id INT DEFAULT 0;
-
- DECLARE v_country_id INT DEFAULT 0;
- DECLARE v_divison_id INT DEFAULT 0;
- DECLARE v_city_id INT DEFAULT 0;
- DECLARE v_mapping_id INT DEFAULT 0;
- DECLARE v_country_iso_code TEXT DEFAULT '';
- DECLARE v_subdivision_name TEXT DEFAULT '';
- DECLARE v_city_name TEXT DEFAULT '';
-
- DECLARE csr CURSOR FOR
- SELECT
- DISTINCT(geoname_id)
- FROM
- city_blocks
- WHERE
- geoname_id != 0
- AND
- network_start_ip REGEXP '::ffff:[0-9]+.[0-9]+.[0-9]+.[0-9]+$';
-
- 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]+$';
-
- SET v_count = 0;
-
- OPEN csr;
- read_loop: LOOP
- FETCH csr INTO v_geoname_id;
-
- SET v_count = v_count + 1;
-
- SET v_country_id = 0;
- SET v_divison_id = 0;
- SET v_city_id = 0;
- SET v_mapping_id = 0;
-
- SET v_country_iso_code = '';
- SET v_subdivision_name = '';
- SET v_city_name = '';
-
- SELECT id INTO v_mapping_id FROM city_blocks_mapping WHERE geoname_id = v_geoname_id AND city_id = v_city_id;
-
- IF v_mapping_id = 0 THEN
- 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;
-
- IF v_country_iso_code != '' THEN
- SELECT id INTO v_country_id FROM core_geoip_country WHERE code = v_country_iso_code;
- END IF;
-
- IF v_subdivision_name != '' THEN
- SELECT id INTO v_divison_id FROM core_geoip_division WHERE name = v_subdivision_name AND country_id = v_country_id;
- END IF;
-
- 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;
-
- IF v_city_id != 0 THEN
- INSERT INTO city_blocks_mapping (geoname_id, city_id) VALUES (v_geoname_id, v_city_id);
-
- END IF;
-
- END IF;
-
-
- IF v_count = v_total THEN
- LEAVE read_loop;
- END IF;
-
- END LOOP;
- CLOSE csr;
-
- RETURN v_count;
- END $$
-DELIMITER ;
-
-
+-- -- not sure if this is used anywhere...
--
--
--- DROP FUNCTION IF EXISTS core_city_blocks;
+-- DROP FUNCTION IF EXISTS core_cities_merge_country;
-- DELIMITER $$
--- CREATE FUNCTION core_city_blocks() RETURNS INT DETERMINISTIC
+-- CREATE FUNCTION core_cities_merge_country() RETURNS INT DETERMINISTIC
-- BEGIN
+-- DECLARE co_done INT DEFAULT FALSE;
+--
-- DECLARE v_count INT DEFAULT 0;
-- DECLARE v_total INT DEFAULT 0;
+--
+-- DECLARE v_id INT DEFAULT 0;
+-- DECLARE v_iso TEXT DEFAULT '';
+-- DECLARE v_local_name TEXT DEFAULT '';
+-- -- DECLARE v_type TEXT DEFAULT '';
+-- DECLARE v_in_location INT DEFAULT 0;
+-- 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_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 co_csr CURSOR FOR
+-- SELECT
+-- id,iso,local_name,in_location
+-- FROM
+-- meta_location
+-- WHERE
+-- type = 'CO';
--
+-- SELECT COUNT(id) INTO v_total FROM meta_location WHERE type = 'CO';
+--
+-- SET v_count = 0;
+--
+-- OPEN co_csr;
+--
+-- co_loop: LOOP
+-- FETCH co_csr INTO v_id,v_iso,v_local_name,v_in_location;
+--
+-- SET v_count = v_count + 1;
+--
+-- SET v_id_tmp = 0;
+--
+-- SELECT id INTO v_id_tmp FROM core_geoip_country WHERE code = v_iso;
+--
+-- IF(v_id_tmp = 0) THEN
+-- INSERT INTO core_geoip_country (code, name, continent_id) VALUES (v_iso, v_local_name, 0);
+-- END IF;
+--
+-- IF v_count = v_total THEN
+-- LEAVE co_loop;
+-- END IF;
+--
+-- END LOOP;
+-- CLOSE co_csr;
+--
+--
+-- RETURN v_count;
+-- END $$
+-- DELIMITER ;
+--
+--
+--
+--
+-- DROP FUNCTION IF EXISTS core_cities_merge_division;
+-- DELIMITER $$
+-- CREATE FUNCTION core_cities_merge_division() RETURNS INT DETERMINISTIC
+-- BEGIN
+-- DECLARE re_done INT DEFAULT FALSE;
+--
+-- DECLARE v_count INT DEFAULT 0;
+-- DECLARE v_total INT DEFAULT 0;
+--
+-- DECLARE v_id INT DEFAULT 0;
+-- DECLARE v_iso TEXT DEFAULT '';
+-- DECLARE v_local_name TEXT DEFAULT '';
+-- -- DECLARE v_type TEXT DEFAULT '';
+-- DECLARE v_in_location INT DEFAULT 0;
+-- DECLARE v_geo_lat INT DEFAULT 0;
+-- DECLARE v_geo_lng INT DEFAULT 0;
+--
+-- DECLARE v_country_id INT DEFAULT 0;
+-- DECLARE v_iso_tmp TEXT DEFAULT '';
+--
+-- DECLARE v_division_id INT DEFAULT 0;
+--
+-- DECLARE re_csr CURSOR FOR
+-- SELECT
+-- id,iso,local_name,in_location
+-- FROM
+-- meta_location
+-- WHERE
+-- type = 'RE';
+--
+-- SELECT COUNT(id) INTO v_total FROM meta_location WHERE type = 'RE';
+--
+-- SET v_count = 0;
+--
+-- OPEN re_csr;
+-- re_loop: LOOP
+-- FETCH re_csr INTO v_id,v_iso,v_local_name,v_in_location;
+--
+-- SET v_count = v_count + 1;
+--
+-- SET v_country_id = 0;
+-- SET v_division_id = 0;
+-- SET v_iso_tmp = '';
+--
+-- IF v_in_location IS NOT NULL THEN
+-- SELECT iso INTO v_iso_tmp FROM meta_location WHERE id = v_in_location;
+--
+-- SELECT id INTO v_country_id FROM core_geoip_country WHERE code = v_iso_tmp;
+--
+-- 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_country_id);
+-- END IF;
+--
+-- -- ITERATE re_loop;
+--
+-- IF v_count = v_total THEN
+-- LEAVE re_loop;
+-- END IF;
+--
+-- END LOOP;
+-- CLOSE re_csr;
+--
+-- RETURN v_count;
+-- END $$
+-- DELIMITER ;
+--
+--
+--
+-- DROP FUNCTION IF EXISTS core_cities_merge_city;
+-- DELIMITER $$
+-- CREATE FUNCTION core_cities_merge_city() RETURNS INT DETERMINISTIC
+-- BEGIN
+-- DECLARE ci_done INT DEFAULT FALSE;
+--
+-- DECLARE v_count INT DEFAULT 0;
+-- DECLARE v_total INT DEFAULT 0;
+--
+-- DECLARE v_id INT DEFAULT 0;
+-- DECLARE v_iso TEXT DEFAULT '';
+-- DECLARE v_local_name TEXT DEFAULT '';
+-- -- DECLARE v_type TEXT DEFAULT '';
+-- DECLARE v_in_location INT DEFAULT 0;
+-- DECLARE v_geo_lat INT DEFAULT 0;
+-- DECLARE v_geo_lng 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_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
+-- id,iso,local_name,in_location,geo_lat,geo_lng
+-- FROM
+-- meta_location
+-- WHERE
+-- type = 'CI';
+--
+-- SELECT COUNT(id) INTO v_total FROM meta_location WHERE type = 'CI';
+--
+-- SET v_count = 0;
+--
+-- OPEN ci_csr;
+-- ci_loop: LOOP
+-- FETCH ci_csr INTO v_id,v_iso,v_local_name,v_in_location,v_geo_lat,v_geo_lng;
+--
+-- SET v_count = v_count + 1;
+--
+-- SET v_country_id = 0;
+-- SET v_division_id = 0;
+-- SET v_city_id = 0;
+--
+-- 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_type_tmp = 'CO' THEN
+-- SELECT id INTO v_country_id FROM core_geoip_country WHERE code = v_iso_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;
+--
+-- 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;
+--
+-- END IF;
+--
+--
+--
+-- 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
+-- LEAVE ci_loop;
+-- END IF;
+--
+-- END LOOP;
+-- CLOSE ci_csr;
+--
+-- RETURN v_count;
+-- END $$
+-- DELIMITER ;
+--
+--
+-- -- ---------------------------------------------------------------------------------------------------------------
+--
+--
+-- DROP FUNCTION IF EXISTS core_country_locations;
+-- DELIMITER $$
+-- CREATE FUNCTION core_country_locations() RETURNS INT DETERMINISTIC
+-- BEGIN
+-- DECLARE v_count INT DEFAULT 0;
+-- DECLARE v_total INT DEFAULT 0;
+--
+-- DECLARE v_geoname_id INT DEFAULT 0;
+-- DECLARE v_continent_code TEXT DEFAULT '';
+-- DECLARE v_continent_name TEXT DEFAULT '';
+-- DECLARE v_country_iso_code TEXT DEFAULT '';
+-- DECLARE v_country_name TEXT DEFAULT '';
+--
+-- DECLARE v_country_id INT DEFAULT 0;
+-- DECLARE v_continent_id INT DEFAULT 0;
+--
+-- DECLARE csr CURSOR FOR
+-- SELECT
+-- geoname_id,continent_code,continent_name,country_iso_code,country_name
+-- FROM
+-- country_locations;
+--
+-- SELECT COUNT(geoname_id) INTO v_total FROM country_locations;
+--
+-- SET v_count = 0;
+--
+-- OPEN csr;
+-- read_loop: LOOP
+-- FETCH csr INTO v_geoname_id,v_continent_code,v_continent_name,v_country_iso_code,v_country_name;
+--
+-- SET v_count = v_count + 1;
+--
+-- SET v_country_id = 0;
+-- SET v_continent_id = 0;
+--
+-- IF (v_continent_code != '') THEN
+-- SELECT id INTO v_continent_id FROM core_geoip_continent WHERE code = v_continent_code;
+--
+-- IF v_continent_id = 0 THEN
+-- INSERT INTO core_geoip_continent (code, name) VALUES (v_continent_code, v_continent_name);
+-- SET v_continent_id = LAST_INSERT_ID();
+-- END IF;
+--
+-- END IF;
+--
+-- IF (v_country_iso_code != '') THEN
+--
+-- SELECT id INTO v_country_id FROM core_geoip_country WHERE code = v_country_iso_code;
+--
+-- IF v_country_id = 0 THEN
+-- INSERT INTO core_geoip_country (code, name, continent_id) VALUES (v_country_iso_code, v_country_name, v_continent_id);
+-- END IF;
+--
+-- END IF;
+--
+-- IF v_count = v_total THEN
+-- LEAVE read_loop;
+-- END IF;
+--
+-- END LOOP;
+-- CLOSE csr;
+--
+--
+-- RETURN v_count;
+-- END $$
+-- DELIMITER ;
+--
+--
+-- DROP FUNCTION IF EXISTS core_country_blocks;
+-- DELIMITER $$
+-- CREATE FUNCTION core_country_blocks() RETURNS INT DETERMINISTIC
+-- BEGIN
+-- DECLARE v_count INT DEFAULT 0;
+-- DECLARE v_total INT DEFAULT 0;
+--
+-- DECLARE v_geoname_id INT DEFAULT 0;
-- DECLARE v_network_start_ip TEXT DEFAULT '';
-- DECLARE v_network_mask_length INT DEFAULT 0;
--- DECLARE v_geoname_id INT DEFAULT 0;
--- DECLARE v_latitude DECIMAL(11,8) DEFAULT 0;
--- DECLARE v_longitude DECIMAL(11,8) DEFAULT 0;
-- DECLARE v_is_anonymous_proxy INT DEFAULT 0;
-- DECLARE v_is_satellite_provider INT DEFAULT 0;
--
+-- DECLARE v_country_iso_code TEXT DEFAULT '';
+--
+-- DECLARE v_country_id INT DEFAULT 0;
+--
+-- DECLARE csr CURSOR FOR
+-- SELECT
+-- network_start_ip,network_mask_length,geoname_id,is_anonymous_proxy,is_satellite_provider
+-- 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;
+--
+-- SET v_country_id = 0;
+--
+-- SELECT country_iso_code INTO v_country_iso_code FROM country_locations WHERE geoname_id = v_geoname_id;
+--
+-- IF v_country_iso_code != '' THEN
+-- SELECT id INTO v_country_id FROM core_geoip_country WHERE code = v_country_iso_code;
+--
+-- IF v_country_id != 0 THEN
+-- 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);
+-- END IF;
+--
+-- END IF;
+--
+-- IF v_count = v_total THEN
+-- LEAVE read_loop;
+-- END IF;
+--
+-- END LOOP;
+-- CLOSE csr;
+--
+--
+-- RETURN v_count;
+-- END $$
+-- DELIMITER ;
+--
+--
+--
+--
+--
+-- DROP FUNCTION IF EXISTS core_city_locations;
+-- DELIMITER $$
+-- CREATE FUNCTION core_city_locations() RETURNS INT DETERMINISTIC
+-- BEGIN
+-- DECLARE v_count INT DEFAULT 0;
+-- DECLARE v_total INT DEFAULT 0;
+--
+-- DECLARE v_country_iso_code TEXT DEFAULT '';
+-- DECLARE v_subdivision_iso_code TEXT DEFAULT '';
+-- DECLARE v_subdivision_name TEXT DEFAULT '';
+-- DECLARE v_city_name TEXT DEFAULT '';
+-- DECLARE v_metro_code TEXT DEFAULT '';
+-- DECLARE v_time_zone TEXT DEFAULT '';
+--
+-- DECLARE v_country_id INT DEFAULT 0;
+-- DECLARE v_division_id INT DEFAULT 0;
+-- DECLARE v_city_id INT DEFAULT 0;
+--
+-- DECLARE csr CURSOR FOR
+-- SELECT
+-- country_iso_code,subdivision_iso_code,subdivision_name,city_name,metro_code,time_zone
+-- FROM
+-- city_locations
+-- WHERE
+-- subdivision_name != '' OR city_name != '';
+--
+-- SELECT COUNT(geoname_id) INTO v_total FROM city_locations WHERE subdivision_name != '' OR city_name != '';;
+--
+-- SET v_count = 0;
+--
+-- OPEN csr;
+-- read_loop: LOOP
+-- FETCH csr INTO v_country_iso_code,v_subdivision_iso_code,v_subdivision_name,v_city_name,v_metro_code,v_time_zone;
+--
+-- SET v_count = v_count + 1;
+--
+-- SET v_country_id = 0;
+-- SET v_division_id = 0;
+-- SET v_city_id = 0;
+--
+--
+-- IF (v_country_iso_code != '') THEN
+-- SELECT id INTO v_country_id FROM core_geoip_country WHERE code = v_country_iso_code;
+-- END IF;
+--
+-- IF v_subdivision_name != '' THEN
+-- SELECT id INTO v_division_id FROM core_geoip_division WHERE name = v_subdivision_name AND country_id = v_country_id;
+--
+-- IF v_division_id = 0 THEN
+-- INSERT INTO core_geoip_division (code, name, country_id) VALUES (v_subdivision_iso_code, v_subdivision_name, v_country_id);
+-- SET v_division_id = LAST_INSERT_ID();
+-- END IF;
+--
+--
+-- END IF;
+--
+-- IF v_city_name != '' THEN
+--
+-- 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;
+--
+-- IF v_city_id = 0 THEN
+-- 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);
+--
+-- END IF;
+--
+-- END IF;
+--
+-- IF v_count = v_total THEN
+-- LEAVE read_loop;
+-- END IF;
+--
+-- END LOOP;
+-- CLOSE csr;
+--
+--
+-- RETURN v_count;
+-- END $$
+-- DELIMITER ;
+--
+--
+--
+-- DROP FUNCTION IF EXISTS core_city_blocks_mapping;
+-- DELIMITER $$
+-- CREATE FUNCTION core_city_blocks_mapping() RETURNS INT DETERMINISTIC
+-- BEGIN
+-- DECLARE v_count INT DEFAULT 0;
+-- DECLARE v_total INT DEFAULT 0;
+--
--
+-- DECLARE v_geoname_id INT DEFAULT 0;
+--
+-- DECLARE v_country_id INT DEFAULT 0;
+-- DECLARE v_divison_id INT DEFAULT 0;
-- DECLARE v_city_id INT DEFAULT 0;
-- DECLARE v_mapping_id INT DEFAULT 0;
+-- DECLARE v_country_iso_code TEXT DEFAULT '';
+-- DECLARE v_subdivision_name TEXT DEFAULT '';
+-- DECLARE v_city_name TEXT DEFAULT '';
--
-- DECLARE csr CURSOR FOR
-- SELECT
--- network_start_ip,network_mask_length,geoname_id,latitude, longitude, is_anonymous_proxy,is_satellite_provider
+-- DISTINCT(geoname_id)
-- FROM
-- city_blocks
-- WHERE
-- AND
-- network_start_ip REGEXP '::ffff:[0-9]+.[0-9]+.[0-9]+.[0-9]+$';
--
--- 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]+$';
+-- 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]+$';
--
-- SET v_count = 0;
--
-- OPEN csr;
-- read_loop: LOOP
--- 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;
+-- FETCH csr INTO v_geoname_id;
--
-- SET v_count = v_count + 1;
--
+-- SET v_country_id = 0;
+-- SET v_divison_id = 0;
-- SET v_city_id = 0;
-- SET v_mapping_id = 0;
--
--- SELECT id, city_id INTO v_mapping_id, v_city_id FROM city_blocks_mapping WHERE geoname_id = v_geoname_id;
+-- SET v_country_iso_code = '';
+-- SET v_subdivision_name = '';
+-- SET v_city_name = '';
--
--- IF v_mapping_id != 0 THEN
+-- SELECT id INTO v_mapping_id FROM city_blocks_mapping WHERE geoname_id = v_geoname_id AND city_id = v_city_id;
+--
+-- IF v_mapping_id = 0 THEN
+-- 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;
--
--- INSERT INTO core_geoip_location (latitude, longitude, city_id) VALUES (v_latitude, v_longitude, v_city_id);
+-- IF v_country_iso_code != '' THEN
+-- SELECT id INTO v_country_id FROM core_geoip_country WHERE code = v_country_iso_code;
+-- END IF;
+--
+-- IF v_subdivision_name != '' THEN
+-- SELECT id INTO v_divison_id FROM core_geoip_division WHERE name = v_subdivision_name AND country_id = v_country_id;
+-- END IF;
+--
+-- 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;
+--
+-- IF v_city_id != 0 THEN
+-- INSERT INTO city_blocks_mapping (geoname_id, city_id) VALUES (v_geoname_id, v_city_id);
+--
+-- END IF;
--
--- 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);
---
-- END IF;
--
+--
-- IF v_count = v_total THEN
-- LEAVE read_loop;
-- END IF;
-- RETURN v_count;
-- END $$
-- DELIMITER ;
+--
+--
+-- --
+-- --
+-- -- DROP FUNCTION IF EXISTS core_city_blocks;
+-- -- DELIMITER $$
+-- -- CREATE FUNCTION core_city_blocks() RETURNS INT DETERMINISTIC
+-- -- BEGIN
+-- -- DECLARE v_count INT DEFAULT 0;
+-- -- DECLARE v_total INT DEFAULT 0;
+-- --
+-- -- DECLARE v_network_start_ip TEXT DEFAULT '';
+-- -- DECLARE v_network_mask_length INT DEFAULT 0;
+-- -- DECLARE v_geoname_id INT DEFAULT 0;
+-- -- DECLARE v_latitude DECIMAL(11,8) DEFAULT 0;
+-- -- DECLARE v_longitude DECIMAL(11,8) DEFAULT 0;
+-- -- DECLARE v_is_anonymous_proxy INT DEFAULT 0;
+-- -- DECLARE v_is_satellite_provider INT DEFAULT 0;
+-- --
+-- --
+-- -- DECLARE v_city_id INT DEFAULT 0;
+-- -- DECLARE v_mapping_id INT DEFAULT 0;
+-- --
+-- -- DECLARE csr CURSOR FOR
+-- -- SELECT
+-- -- network_start_ip,network_mask_length,geoname_id,latitude, longitude, is_anonymous_proxy,is_satellite_provider
+-- -- FROM
+-- -- city_blocks
+-- -- WHERE
+-- -- geoname_id != 0
+-- -- AND
+-- -- network_start_ip REGEXP '::ffff:[0-9]+.[0-9]+.[0-9]+.[0-9]+$';
+-- --
+-- -- 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]+$';
+-- --
+-- -- SET v_count = 0;
+-- --
+-- -- OPEN csr;
+-- -- read_loop: LOOP
+-- -- 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;
+-- --
+-- -- SET v_count = v_count + 1;
+-- --
+-- -- SET v_city_id = 0;
+-- -- SET v_mapping_id = 0;
+-- --
+-- -- SELECT id, city_id INTO v_mapping_id, v_city_id FROM city_blocks_mapping WHERE geoname_id = v_geoname_id;
+-- --
+-- -- IF v_mapping_id != 0 THEN
+-- --
+-- -- INSERT INTO core_geoip_location (latitude, longitude, city_id) VALUES (v_latitude, v_longitude, v_city_id);
+-- --
+-- -- 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);
+-- --
+-- -- END IF;
+-- --
+-- -- IF v_count = v_total THEN
+-- -- LEAVE read_loop;
+-- -- END IF;
+-- --
+-- -- END LOOP;
+-- -- CLOSE csr;
+-- --
+-- -- RETURN v_count;
+-- -- END $$
+-- -- DELIMITER ;
+--
\ No newline at end of file
projects / Pman.Core / commitdiff