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_count INT DEFAULT 0;
78 DECLARE v_id INT DEFAULT 0;
79 DECLARE v_iso TEXT DEFAULT '';
80 DECLARE v_local_name TEXT DEFAULT '';
81 -- DECLARE v_type TEXT DEFAULT '';
82 DECLARE v_in_location INT DEFAULT 0;
83 DECLARE v_geo_lat INT DEFAULT 0;
84 DECLARE v_geo_lng INT DEFAULT 0;
86 DECLARE v_id_tmp INT DEFAULT 0;
87 DECLARE v_iso_tmp TEXT DEFAULT '';
88 DECLARE v_local_name_tmp TEXT DEFAULT '';
89 DECLARE v_type_tmp TEXT DEFAULT '';
90 DECLARE v_in_location_tmp INT DEFAULT 0;
92 DECLARE v_id_tmp_tmp 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;
114 SELECT id INTO v_id_tmp FROM core_geoip_division WHERE name = v_local_name;
116 IF(v_id_tmp = 0) THEN
117 IF v_in_location IS NOT NULL THEN
118 SELECT iso, local_name, type INTO v_iso_tmp, v_local_name_tmp, v_type_tmp FROM meta_location WHERE id = v_in_location;
120 SELECT id INTO v_id_tmp FROM core_geoip_country WHERE code = v_iso_tmp;
124 INSERT INTO core_geoip_division (code, name, country_id) VALUES (v_iso, v_local_name, v_id_tmp);
129 IF v_count = v_total THEN
142 DROP FUNCTION IF EXISTS core_cities_merge_city;
144 CREATE FUNCTION core_cities_merge_city() RETURNS INT DETERMINISTIC
146 DECLARE ci_done INT DEFAULT FALSE;
148 DECLARE v_count 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
173 DECLARE CONTINUE HANDLER FOR NOT FOUND SET ci_done = TRUE;
179 FETCH ci_csr INTO v_id,v_iso,v_local_name,v_in_location,v_geo_lat,v_geo_lng;
181 SET v_count = v_count + 1;
184 SET v_id_tmp_tmp = 0;
186 SELECT id INTO v_id_tmp FROM core_geoip_city WHERE name = v_local_name LIMIT 1;
188 IF(v_id_tmp = 0) THEN
189 IF v_in_location IS NOT NULL THEN
191 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;
193 IF v_type_tmp = 'CO' THEN
194 SELECT id INTO v_id_tmp FROM core_geoip_country WHERE code = v_iso_tmp;
196 INSERT INTO core_geoip_city (name, country_id) VALUES (v_local_name, v_id_tmp);
199 IF v_type_tmp = 'RE' THEN
200 SELECT id INTO v_id_tmp FROM core_geoip_division WHERE name = v_local_name_tmp;
202 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;
204 SELECT id INTO v_id_tmp_tmp FROM core_geoip_country WHERE code = v_iso_tmp;
206 INSERT INTO core_geoip_city (name, country_id, division_id) VALUES (v_local_name, v_id_tmp_tmp, v_id_tmp);
211 IF v_geo_lat IS NOT NULL OR v_geo_lng IS NOT NULL THEN
212 SET v_id_tmp = LAST_INSERT_ID();
214 INSERT INTO core_geoip_location (latitude, longitude, city_id) VALUES (v_geo_lat, v_geo_lng, v_id_tmp);