2 DROP FUNCTION IF EXISTS core_cities_merge_country;
4 CREATE FUNCTION core_cities_merge_country() RETURNS TEXT DETERMINISTIC
6 DECLARE co_done INT DEFAULT FALSE;
8 DECLARE v_id INT DEFAULT 0;
9 DECLARE v_iso TEXT DEFAULT '';
10 DECLARE v_local_name TEXT DEFAULT '';
11 -- DECLARE v_type TEXT DEFAULT '';
12 DECLARE v_in_location INT DEFAULT 0;
13 DECLARE v_geo_lat INT DEFAULT 0;
14 DECLARE v_geo_lng INT DEFAULT 0;
16 DECLARE v_id_tmp INT DEFAULT 0;
17 DECLARE v_iso_tmp TEXT DEFAULT '';
18 DECLARE v_local_name_tmp TEXT DEFAULT '';
19 DECLARE v_type_tmp TEXT DEFAULT '';
20 DECLARE v_in_location_tmp INT DEFAULT 0;
22 DECLARE v_id_tmp_tmp INT DEFAULT 0;
24 DECLARE co_csr CURSOR FOR
26 id,iso,local_name,in_location
31 DECLARE CONTINUE HANDLER FOR NOT FOUND SET co_done = TRUE;
36 FETCH co_csr INTO v_id,v_iso,v_local_name,v_in_location;
40 SELECT id INTO v_id_tmp FROM core_geoip_country WHERE code = v_iso;
43 INSERT INTO core_geoip_country (code, name, continent_id) VALUES (v_iso, v_local_name, 0);
63 DROP FUNCTION IF EXISTS core_cities_merge_division;
65 CREATE FUNCTION core_cities_merge_division() RETURNS TEXT DETERMINISTIC
67 DECLARE re_done INT DEFAULT FALSE;
69 DECLARE v_id INT DEFAULT 0;
70 DECLARE v_iso TEXT DEFAULT '';
71 DECLARE v_local_name TEXT DEFAULT '';
72 -- DECLARE v_type TEXT DEFAULT '';
73 DECLARE v_in_location INT DEFAULT 0;
74 DECLARE v_geo_lat INT DEFAULT 0;
75 DECLARE v_geo_lng INT DEFAULT 0;
77 DECLARE v_id_tmp INT DEFAULT 0;
78 DECLARE v_iso_tmp TEXT DEFAULT '';
79 DECLARE v_local_name_tmp TEXT DEFAULT '';
80 DECLARE v_type_tmp TEXT DEFAULT '';
81 DECLARE v_in_location_tmp INT DEFAULT 0;
83 DECLARE v_id_tmp_tmp INT DEFAULT 0;
85 DECLARE re_csr CURSOR FOR
87 id,iso,local_name,in_location
92 DECLARE CONTINUE HANDLER FOR NOT FOUND SET re_done = TRUE;
96 FETCH re_csr INTO v_id,v_iso,v_local_name,v_in_location;
100 SELECT id INTO v_id_tmp FROM core_geoip_division WHERE name = v_local_name;
102 IF(v_id_tmp = 0) THEN
103 IF v_in_location IS NOT NULL THEN
104 SELECT iso, local_name, type INTO v_iso_tmp, v_local_name_tmp, v_type_tmp FROM meta_location WHERE id = v_in_location;
106 SELECT id INTO v_id_tmp FROM core_geoip_country WHERE code = v_iso_tmp;
110 INSERT INTO core_geoip_division (code, name, country_id) VALUES (v_iso, v_local_name, v_id_tmp);
128 DROP FUNCTION IF EXISTS core_cities_merge_city;
130 CREATE FUNCTION core_cities_merge_city() RETURNS INT DETERMINISTIC
132 DECLARE ci_done INT DEFAULT FALSE;
134 DECLARE v_count INT DEFAULT 0;
136 DECLARE v_id INT DEFAULT 0;
137 DECLARE v_iso TEXT DEFAULT '';
138 DECLARE v_local_name TEXT DEFAULT '';
139 -- DECLARE v_type TEXT DEFAULT '';
140 DECLARE v_in_location INT DEFAULT 0;
141 DECLARE v_geo_lat INT DEFAULT 0;
142 DECLARE v_geo_lng INT DEFAULT 0;
144 DECLARE v_id_tmp INT DEFAULT 0;
145 DECLARE v_iso_tmp TEXT DEFAULT '';
146 DECLARE v_local_name_tmp TEXT DEFAULT '';
147 DECLARE v_type_tmp TEXT DEFAULT '';
148 DECLARE v_in_location_tmp INT DEFAULT 0;
150 DECLARE v_id_tmp_tmp INT DEFAULT 0;
152 DECLARE ci_csr CURSOR FOR
154 id,iso,local_name,in_location,geo_lat,geo_lng
159 DECLARE CONTINUE HANDLER FOR NOT FOUND SET ci_done = TRUE;
165 FETCH ci_csr INTO v_id,v_iso,v_local_name,v_in_location,v_geo_lat,v_geo_lng;
167 SET v_count = v_count + 1;
170 SET v_id_tmp_tmp = 0;
172 SELECT id INTO v_id_tmp FROM core_geoip_city WHERE name = v_local_name LIMIT 1;
174 IF(v_id_tmp = 0) THEN
175 IF v_in_location IS NOT NULL THEN
177 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;
179 IF v_type_tmp = 'CO' THEN
180 SELECT id INTO v_id_tmp FROM core_geoip_country WHERE code = v_iso_tmp;
182 INSERT INTO core_geoip_city (name, country_id) VALUES (v_local_name, v_id_tmp);
185 IF v_type_tmp = 'RE' THEN
186 SELECT id INTO v_id_tmp FROM core_geoip_division WHERE name = v_local_name_tmp;
188 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;
190 SELECT id INTO v_id_tmp_tmp FROM core_geoip_country WHERE code = v_iso_tmp;
192 INSERT INTO core_geoip_city (name, country_id, division_id) VALUES (v_local_name, v_id_tmp_tmp, v_id_tmp);
197 IF v_geo_lat IS NOT NULL OR v_geo_lng IS NOT NULL THEN
198 SET v_id_tmp = LAST_INSERT_ID();
200 INSERT INTO core_geoip_location (latitude, longitude, city_id) VALUES (v_geo_lat, v_geo_lng, v_id_tmp);