git.roojs.org Git - Pman.Core/blob - mysql/core_cities_merge.sql

   1
   2 DROP FUNCTION IF EXISTS core_cities_merge_country;
   3 DELIMITER $$
   4 CREATE FUNCTION core_cities_merge_country()  RETURNS INT DETERMINISTIC
   5     BEGIN
   6         DECLARE co_done INT DEFAULT FALSE;
   7
   8         DECLARE v_count INT DEFAULT 0;
   9         DECLARE v_total INT DEFAULT 0;
  10
  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;
  18
  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;
  24
  25         DECLARE v_id_tmp_tmp INT DEFAULT 0;
  26
  27         DECLARE co_csr CURSOR FOR
  28         SELECT
  29             id,iso,local_name,in_location
  30         FROM
  31             meta_location
  32         WHERE
  33             type = 'CO';
  34
  35         SELECT COUNT(id) INTO v_total FROM meta_location WHERE type = 'CO';
  36
  37         SET v_count = 0;
  38
  39         OPEN co_csr;
  40         co_loop: LOOP
  41             FETCH co_csr INTO v_id,v_iso,v_local_name,v_in_location;
  42
  43             SET v_count = v_count + 1;
  44
  45             SET v_id_tmp = 0;
  46
  47             SELECT id INTO v_id_tmp FROM core_geoip_country WHERE code = v_iso;
  48
  49             IF(v_id_tmp = 0) THEN
  50                 INSERT INTO core_geoip_country (code, name, continent_id) VALUES (v_iso, v_local_name, 0);
  51             END IF;
  52
  53             IF v_count = v_total THEN
  54               LEAVE co_loop;
  55             END IF;
  56
  57         END LOOP;
  58         CLOSE co_csr;
  59
  60
  61         RETURN v_count;
  62     END $$
  63 DELIMITER ;
  64
  65
  66
  67
  68 DROP FUNCTION IF EXISTS core_cities_merge_division;
  69 DELIMITER $$
  70 CREATE FUNCTION core_cities_merge_division()  RETURNS INT DETERMINISTIC
  71     BEGIN
  72         DECLARE re_done INT DEFAULT FALSE;
  73
  74         DECLARE v_count INT DEFAULT 0;
  75         DECLARE v_total INT DEFAULT 0;
  76
  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;
  84
  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;
  90
  91         DECLARE v_id_tmp_tmp INT DEFAULT 0;
  92
  93         DECLARE re_csr CURSOR FOR
  94         SELECT
  95             id,iso,local_name,in_location
  96         FROM
  97             meta_location
  98         WHERE
  99             type = 'RE';
 100
 101         SELECT COUNT(id) INTO v_total FROM meta_location WHERE type = 'RE';
 102
 103         SET v_count = 0;
 104
 105         OPEN re_csr;
 106         re_loop: LOOP
 107             FETCH re_csr INTO v_id,v_iso,v_local_name,v_in_location;
 108
 109             SET v_count = v_count + 1;
 110
 111             SET v_id_tmp = 0;
 112
 113             SELECT id INTO v_id_tmp FROM core_geoip_division WHERE name = v_local_name;
 114
 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;
 118
 119                     SELECT id INTO v_id_tmp FROM core_geoip_country WHERE code = v_iso_tmp;
 120
 121                 END IF;
 122
 123                 INSERT INTO core_geoip_division (code, name, country_id) VALUES (v_iso, v_local_name, v_id_tmp);
 124             END IF;
 125
 126 --             ITERATE re_loop;
 127
 128             IF v_count = v_total THEN
 129               LEAVE re_loop;
 130             END IF;
 131
 132         END LOOP;
 133         CLOSE re_csr;
 134
 135         RETURN v_count;
 136     END $$
 137 DELIMITER ;
 138
 139
 140
 141 DROP FUNCTION IF EXISTS core_cities_merge_city;
 142 DELIMITER $$
 143 CREATE FUNCTION core_cities_merge_city()  RETURNS INT DETERMINISTIC
 144     BEGIN
 145         DECLARE ci_done INT DEFAULT FALSE;
 146
 147         DECLARE v_count INT DEFAULT 0;
 148         DECLARE v_total INT DEFAULT 0;
 149
 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;
 157
 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;
 163
 164         DECLARE v_id_tmp_tmp INT DEFAULT 0;
 165
 166         DECLARE ci_csr CURSOR FOR
 167         SELECT
 168             id,iso,local_name,in_location,geo_lat,geo_lng
 169         FROM
 170             meta_location
 171         WHERE
 172             type = 'CI';
 173
 174         SELECT COUNT(id) INTO v_total FROM meta_location WHERE type = 'CI';
 175
 176         SET v_count = 0;
 177
 178         OPEN ci_csr;
 179         ci_loop: LOOP
 180             FETCH ci_csr INTO v_id,v_iso,v_local_name,v_in_location,v_geo_lat,v_geo_lng;
 181
 182             SET v_count = v_count + 1;
 183
 184             SET v_id_tmp = 0;
 185             SET v_id_tmp_tmp = 0;
 186
 187             SELECT id INTO v_id_tmp FROM core_geoip_city WHERE name = v_local_name LIMIT 1;
 188
 189             IF(v_id_tmp = 0) THEN
 190                 IF v_in_location IS NOT NULL THEN
 191
 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;
 193
 194                     IF v_type_tmp = 'CO' THEN
 195                         SELECT id INTO v_id_tmp FROM core_geoip_country WHERE code = v_iso_tmp;
 196
 197                         INSERT INTO core_geoip_city (name, country_id) VALUES (v_local_name, v_id_tmp);
 198                     END IF;
 199
 200                     IF v_type_tmp = 'RE' THEN
 201                         SELECT id INTO v_id_tmp FROM core_geoip_division WHERE name = v_local_name_tmp;
 202
 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;
 204
 205                         SELECT id INTO v_id_tmp_tmp FROM core_geoip_country WHERE code = v_iso_tmp;
 206
 207                         INSERT INTO core_geoip_city (name, country_id, division_id) VALUES (v_local_name, v_id_tmp_tmp, v_id_tmp);
 208                     END IF;
 209
 210                 END IF;
 211
 212                 IF v_geo_lat IS NOT NULL OR v_geo_lng IS NOT NULL THEN
 213                     SET v_id_tmp = LAST_INSERT_ID();
 214
 215                     INSERT INTO core_geoip_location (latitude, longitude, city_id) VALUES (v_geo_lat, v_geo_lng, v_id_tmp);
 216                 END IF;
 217
 218
 219             END IF;
 220
 221             IF v_count = v_total THEN
 222               LEAVE ci_loop;
 223             END IF;
 224
 225         END LOOP;
 226         CLOSE ci_csr;
 227
 228         RETURN v_count;
 229     END $$
 230 DELIMITER ;
 231
 232
 233 -----------------------------------------------------------------------------------------------------------------
 234
 235
 236 DROP FUNCTION IF EXISTS core_country_locations;
 237 DELIMITER $$
 238 CREATE FUNCTION core_country_locations()  RETURNS INT DETERMINISTIC
 239     BEGIN
 240         DECLARE v_count INT DEFAULT 0;
 241         DECLARE v_total INT DEFAULT 0;
 242
 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 TEXT DEFAULT '';
 247         DECLARE v_country_name TEXT DEFAULT '';
 248
 249         DECLARE v_country_id INT DEFAULT 0;
 250         DECLARE v_continent_id INT DEFAULT 0;
 251
 252         DECLARE csr CURSOR FOR
 253         SELECT
 254             geoname_id,continent_code,continent_name,country_iso_code,country_name
 255         FROM
 256             country_locations;
 257
 258         SELECT COUNT(geoname_id) INTO v_total FROM country_locations;
 259
 260         SET v_count = 0;
 261
 262         OPEN csr;
 263         read_loop: LOOP
 264             FETCH csr INTO v_geoname_id,v_continent_code,v_continent_name,v_country_iso_code,v_country_name;
 265
 266             SET v_count = v_count + 1;
 267
 268             SET v_country_id = 0;
 269             SET v_continent_id = 0;
 270
 271             IF (v_continent_code != '') THEN
 272                 SELECT id INTO v_continent_id FROM core_geoip_continent WHERE code = v_continent_code;
 273
 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();
 277                 END IF;
 278
 279             END IF;
 280
 281             IF (v_country_iso_code != '') THEN
 282
 283                 SELECT id INTO v_country_id FROM core_geoip_country WHERE code = v_country_iso_code;
 284
 285                 IF v_country_id = 0 THEN
 286                     INSERT INTO core_geoip_country (code, name, continent_id) VALUES (v_country_iso_code, v_country_name, v_continent_id);
 287                 END IF;
 288
 289             END IF;
 290
 291             IF v_count = v_total THEN
 292               LEAVE read_loop;
 293             END IF;
 294
 295         END LOOP;
 296         CLOSE csr;
 297
 298
 299         RETURN v_count;
 300     END $$
 301 DELIMITER ;
 302
 303
 304 DROP FUNCTION IF EXISTS core_country_blocks;
 305 DELIMITER $$
 306 CREATE FUNCTION core_country_blocks()  RETURNS INT DETERMINISTIC
 307     BEGIN
 308         DECLARE v_count INT DEFAULT 0;
 309         DECLARE v_total INT DEFAULT 0;
 310
 311         DECLARE v_geoname_id INT DEFAULT 0;
 312         DECLARE v_network_start_ip TEXT DEFAULT '';
 313         DECLARE v_network_mask_length INT DEFAULT 0;
 314         DECLARE v_is_anonymous_proxy INT DEFAULT 0;
 315         DECLARE v_is_satellite_provider INT DEFAULT 0;
 316
 317         DECLARE v_country_iso_code TEXT DEFAULT '';
 318
 319         DECLARE v_country_id INT DEFAULT 0;
 320
 321         DECLARE csr CURSOR FOR
 322         SELECT
 323             network_start_ip,network_mask_length,geoname_id,is_anonymous_proxy,is_satellite_provider
 324         FROM
 325             country_blocks
 326         WHERE
 327                 geoname_id != 0
 328             AND
 329                 registered_country_geoname_id != 0
 330             AND
 331                 geoname_id = registered_country_geoname_id
 332             AND
 333                 network_start_ip REGEXP '::ffff:[0-9]+.[0-9]+.[0-9]+.[0-9]+$';
 334
 335         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]+$';
 336
 337         SET v_count = 0;
 338
 339         OPEN csr;
 340         read_loop: LOOP
 341             FETCH csr INTO v_network_start_ip,v_network_mask_length,v_geoname_id,v_is_anonymous_proxy,v_is_satellite_provider;
 342
 343             SET v_count = v_count + 1;
 344
 345             SET v_country_id = 0;
 346
 347             SELECT country_iso_code INTO v_country_iso_code FROM country_locations WHERE geoname_id = v_geoname_id;
 348
 349             IF v_country_iso_code != '' THEN
 350                 SELECT id INTO v_country_id FROM core_geoip_country WHERE code = v_country_iso_code;
 351
 352                 IF v_country_id != 0 THEN
 353                     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);
 354                 END IF;
 355
 356             END IF;
 357
 358             IF v_count = v_total THEN
 359               LEAVE read_loop;
 360             END IF;
 361
 362         END LOOP;
 363         CLOSE csr;
 364
 365
 366         RETURN v_count;
 367     END $$
 368 DELIMITER ;
 369
 370
 371
 372
 373
 374 DROP FUNCTION IF EXISTS core_city_locations;
 375 DELIMITER $$
 376 CREATE FUNCTION core_city_locations()  RETURNS INT DETERMINISTIC
 377     BEGIN
 378         DECLARE v_count INT DEFAULT 0;
 379         DECLARE v_total INT DEFAULT 0;
 380
 381         DECLARE v_country_iso_code TEXT DEFAULT '';
 382         DECLARE v_subdivision_iso_code TEXT DEFAULT '';
 383         DECLARE v_subdivision_name TEXT DEFAULT '';
 384         DECLARE v_city_name TEXT DEFAULT '';
 385         DECLARE v_metro_code TEXT DEFAULT '';
 386         DECLARE v_time_zone TEXT DEFAULT '';
 387
 388         DECLARE v_country_id INT DEFAULT 0;
 389         DECLARE v_division_id INT DEFAULT 0;
 390         DECLARE v_city_id INT DEFAULT 0;
 391
 392         DECLARE csr CURSOR FOR
 393         SELECT
 394             country_iso_code,subdivision_iso_code,subdivision_name,city_name,metro_code,time_zone
 395         FROM
 396             city_locations
 397         WHERE
 398             subdivision_name != '' OR city_name != '';
 399
 400         SELECT COUNT(geoname_id) INTO v_total FROM city_locations WHERE subdivision_name != '' OR city_name != '';;
 401
 402         SET v_count = 0;
 403
 404         OPEN csr;
 405         read_loop: LOOP
 406             FETCH csr INTO v_country_iso_code,v_subdivision_iso_code,v_subdivision_name,v_city_name,v_metro_code,v_time_zone;
 407
 408             SET v_count = v_count + 1;
 409
 410             SET v_country_id = 0;
 411             SET v_division_id = 0;
 412             SET v_city_id = 0;
 413
 414
 415             IF (v_country_iso_code != '') THEN
 416                 SELECT id INTO v_country_id FROM core_geoip_country WHERE code = v_country_iso_code;
 417             END IF;
 418
 419             IF v_subdivision_name != '' THEN
 420                 SELECT id INTO v_division_id FROM core_geoip_division WHERE name = v_subdivision_name AND country_id = v_country_id;
 421
 422                 IF v_division_id = 0 THEN
 423                     INSERT INTO core_geoip_division (code, name, country_id) VALUES (v_subdivision_iso_code, v_subdivision_name, v_country_id);
 424                     SET v_division_id = LAST_INSERT_ID();
 425                 END IF;
 426
 427
 428             END IF;
 429
 430             IF v_city_name != '' THEN
 431
 432                 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;
 433
 434                 IF v_city_id = 0 THEN
 435                     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);
 436
 437                 END IF;
 438
 439             END IF;
 440
 441             IF v_count = v_total THEN
 442               LEAVE read_loop;
 443             END IF;
 444
 445         END LOOP;
 446         CLOSE csr;
 447
 448
 449         RETURN v_count;
 450     END $$
 451 DELIMITER ;
 452
 453
 454
 455
 456 DROP FUNCTION IF EXISTS core_city_blocks;
 457 DELIMITER $$
 458 CREATE FUNCTION core_city_blocks()  RETURNS INT DETERMINISTIC
 459     BEGIN
 460         DECLARE v_count INT DEFAULT 0;
 461         DECLARE v_total INT DEFAULT 0;
 462
 463         DECLARE v_network_start_ip TEXT DEFAULT '';
 464         DECLARE v_network_mask_length INT DEFAULT 0;
 465         DECLARE v_geoname_id INT DEFAULT 0;
 466         DECLARE v_latitude DECIMAL(11,8) DEFAULT 0;
 467         DECLARE v_longitude DECIMAL(11,8) DEFAULT 0;
 468         DECLARE v_is_anonymous_proxy INT DEFAULT 0;
 469         DECLARE v_is_satellite_provider INT DEFAULT 0;
 470
 471         DECLARE v_country_id INT DEFAULT 0;
 472         DECLARE v_divison_id INT DEFAULT 0;
 473         DECLARE v_city_id INT DEFAULT 0;
 474         DECLARE v_country_iso_code TEXT DEFAULT '';
 475         DECLARE v_subdivision_name TEXT DEFAULT '';
 476         DECLARE v_city_name TEXT DEFAULT '';
 477
 478         DECLARE csr CURSOR FOR
 479         SELECT
 480             network_start_ip,network_mask_length,geoname_id,latitude, longitude, is_anonymous_proxy,is_satellite_provider
 481         FROM
 482             city_blocks
 483         WHERE
 484                 geoname_id != 0
 485             AND
 486                 network_start_ip REGEXP '::ffff:[0-9]+.[0-9]+.[0-9]+.[0-9]+$';
 487
 488         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]+$';
 489
 490         SET v_count = 0;
 491
 492         OPEN csr;
 493         read_loop: LOOP
 494             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;
 495
 496             SET v_count = v_count + 1;
 497
 498             SET v_country_id = 0;
 499             SET v_divison_id = 0;
 500             SET v_city_id = 0;
 501
 502             SET v_country_iso_code = '';
 503             SET v_subdivision_name = '';
 504             SET v_city_name = '';
 505
 506             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;
 507
 508             IF v_country_iso_code != '' THEN
 509                 SELECT id INTO v_country_id FROM core_geoip_country WHERE code = v_country_iso_code;
 510             END IF;
 511
 512             IF v_subdivision_name != '' THEN
 513                 SELECT id INTO v_divison_id FROM core_geoip_division WHERE name = v_subdivision_name AND country_id = v_country_id;
 514             END IF;
 515
 516             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;
 517
 518             IF v_city_id != 0 THEN
 519
 520                 INSERT INTO core_geoip_location (latitude, longitude, city_id) VALUES (v_latitude, v_longitude, v_city_id);
 521
 522                 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);
 523
 524             END IF;
 525
 526             IF v_count = v_total THEN
 527               LEAVE read_loop;
 528             END IF;
 529
 530         END LOOP;
 531         CLOSE csr;
 532
 533
 534         RETURN v_count;
 535     END $$
 536 DELIMITER ;