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';
41 FETCH co_csr INTO v_id,v_iso,v_local_name,v_in_location;
43 SET v_count = v_count + 1;
47 SELECT id INTO v_id_tmp FROM core_geoip_country WHERE code = v_iso;
50 INSERT INTO core_geoip_country (code, name, continent_id) VALUES (v_iso, v_local_name, 0);
53 IF v_count = v_total THEN
68 DROP FUNCTION IF EXISTS core_cities_merge_division;
70 CREATE FUNCTION core_cities_merge_division() RETURNS INT DETERMINISTIC
72 DECLARE re_done INT DEFAULT FALSE;
74 DECLARE v_count INT DEFAULT 0;
75 DECLARE v_total INT DEFAULT 0;
77 DECLARE v_id INT DEFAULT 0;
78 DECLARE v_iso TEXT DEFAULT '';
79 DECLARE v_local_name TEXT DEFAULT '';
80 -- DECLARE v_type TEXT DEFAULT '';
81 DECLARE v_in_location INT DEFAULT 0;
82 DECLARE v_geo_lat INT DEFAULT 0;
83 DECLARE v_geo_lng INT DEFAULT 0;
85 DECLARE v_id_tmp INT DEFAULT 0;
86 DECLARE v_iso_tmp TEXT DEFAULT '';
87 DECLARE v_local_name_tmp TEXT DEFAULT '';
88 DECLARE v_type_tmp TEXT DEFAULT '';
89 DECLARE v_in_location_tmp INT DEFAULT 0;
91 DECLARE v_id_tmp_tmp INT DEFAULT 0;
93 DECLARE re_csr CURSOR FOR
95 id,iso,local_name,in_location
101 SELECT COUNT(id) INTO v_total FROM meta_location WHERE type = 'RE';
107 FETCH re_csr INTO v_id,v_iso,v_local_name,v_in_location;
109 SET v_count = v_count + 1;
113 SELECT id INTO v_id_tmp FROM core_geoip_division WHERE name = v_local_name;
115 IF(v_id_tmp = 0) THEN
116 IF v_in_location IS NOT NULL THEN
117 SELECT iso, local_name, type INTO v_iso_tmp, v_local_name_tmp, v_type_tmp FROM meta_location WHERE id = v_in_location;
119 SELECT id INTO v_id_tmp FROM core_geoip_country WHERE code = v_iso_tmp;
123 INSERT INTO core_geoip_division (code, name, country_id) VALUES (v_iso, v_local_name, v_id_tmp);
128 IF v_count = v_total THEN
141 DROP FUNCTION IF EXISTS core_cities_merge_city;
143 CREATE FUNCTION core_cities_merge_city() RETURNS INT DETERMINISTIC
145 DECLARE ci_done INT DEFAULT FALSE;
147 DECLARE v_count INT DEFAULT 0;
148 DECLARE v_total INT DEFAULT 0;
150 DECLARE v_id INT DEFAULT 0;
151 DECLARE v_iso TEXT DEFAULT '';
152 DECLARE v_local_name TEXT DEFAULT '';
153 -- DECLARE v_type TEXT DEFAULT '';
154 DECLARE v_in_location INT DEFAULT 0;
155 DECLARE v_geo_lat INT DEFAULT 0;
156 DECLARE v_geo_lng INT DEFAULT 0;
158 DECLARE v_id_tmp INT DEFAULT 0;
159 DECLARE v_iso_tmp TEXT DEFAULT '';
160 DECLARE v_local_name_tmp TEXT DEFAULT '';
161 DECLARE v_type_tmp TEXT DEFAULT '';
162 DECLARE v_in_location_tmp INT DEFAULT 0;
164 DECLARE v_id_tmp_tmp INT DEFAULT 0;
166 DECLARE ci_csr CURSOR FOR
168 id,iso,local_name,in_location,geo_lat,geo_lng
174 SELECT COUNT(id) INTO v_total FROM meta_location WHERE type = 'CI';
180 FETCH ci_csr INTO v_id,v_iso,v_local_name,v_in_location,v_geo_lat,v_geo_lng;
182 SET v_count = v_count + 1;
185 SET v_id_tmp_tmp = 0;
187 SELECT id INTO v_id_tmp FROM core_geoip_city WHERE name = v_local_name LIMIT 1;
189 IF(v_id_tmp = 0) THEN
190 IF v_in_location IS NOT NULL THEN
192 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;
194 IF v_type_tmp = 'CO' THEN
195 SELECT id INTO v_id_tmp FROM core_geoip_country WHERE code = v_iso_tmp;
197 INSERT INTO core_geoip_city (name, country_id) VALUES (v_local_name, v_id_tmp);
200 IF v_type_tmp = 'RE' THEN
201 SELECT id INTO v_id_tmp FROM core_geoip_division WHERE name = v_local_name_tmp;
203 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;
205 SELECT id INTO v_id_tmp_tmp FROM core_geoip_country WHERE code = v_iso_tmp;
207 INSERT INTO core_geoip_city (name, country_id, division_id) VALUES (v_local_name, v_id_tmp_tmp, v_id_tmp);
212 IF v_geo_lat IS NOT NULL OR v_geo_lng IS NOT NULL THEN
213 SET v_id_tmp = LAST_INSERT_ID();
215 INSERT INTO core_geoip_location (latitude, longitude, city_id) VALUES (v_geo_lat, v_geo_lng, v_id_tmp);
221 IF v_count = v_total THEN
233 -----------------------------------------------------------------------------------------------------------------
236 DROP FUNCTION IF EXISTS core_country_locations;
238 CREATE FUNCTION core_country_locations() RETURNS TEXT DETERMINISTIC
240 DECLARE v_count INT DEFAULT 0;
241 DECLARE v_total INT DEFAULT 0;
243 DECLARE v_geoname_id INT DEFAULT 0;
244 DECLARE v_continent_code TEXT DEFAULT '';
245 DECLARE v_continent_name TEXT DEFAULT '';
246 DECLARE v_country_iso_code INT DEFAULT 0;
247 DECLARE v_country_name INT DEFAULT 0;
249 DECLARE v_country_id INT DEFAULT 0;
250 DECLARE v_continent_id INT DEFAULT 0;
252 DECLARE csr CURSOR FOR
254 geoname_id,continent_code,continent_name,country_iso_code,country_name
258 SELECT COUNT(geoname_id) INTO v_total FROM country_locations;
264 FETCH csr INTO v_geoname_id,v_continent_code,v_continent_name,v_country_iso_code,v_country_name;
265 RETURN v_continent_code;
266 SET v_count = v_count + 1;
268 SET v_country_id = 0;
269 SET v_continent_id = 0;
271 IF (v_continent_code != '') THEN
272 SELECT id INTO v_continent_id FROM core_geoip_continent WHERE code = v_continent_code;
274 IF v_continent_id = 0 THEN
275 INSERT INTO core_geoip_continent (code, name) VALUES (v_continent_code, v_continent_name);
276 SET v_continent_id = LAST_INSERT_ID();
281 IF (v_country_iso_code != '') THEN
283 SELECT id INTO v_country_id FROM core_geoip_country WHERE code = v_country_iso_code;
285 IF v_continent_id = 0 THEN
286 INSERT INTO core_geoip_country (code, name, continent_id) VALUES (v_country_iso_code, v_country_name, v_continent_id);
291 IF v_count = v_total THEN