Skip to content

Instantly share code, notes, and snippets.

@diogenese

diogenese/nopaste

Created April 13, 2019 01:26
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save diogenese/5a8f41363c3d54d7311ee9c3be3e9d05 to your computer and use it in GitHub Desktop.
Save diogenese/5a8f41363c3d54d7311ee9c3be3e9d05 to your computer and use it in GitHub Desktop.
Download ZIP
Latest tables
Raw
nopaste
USE `geoipdb`;
SELECT "Creating functions and procedures";
DELIMITER ;;
DROP PROCEDURE IF EXISTS `city2ip`;;
CREATE PROCEDURE `city2ip`(
fp_city VARCHAR(15)
)
READS SQL DATA
DETERMINISTIC
BEGIN
SELECT CONCAT(l.city,IF(LENGTH(city), ', ', ''),r.region,IF(LENGTH(r.region), ', ', ''),c.country), i.ipstart, i.ipend
FROM `geo_iprange` i JOIN `geo_blocks` b ON i.id = b.rangeid
JOIN `geo_locations` l ON i.locid=l.id
JOIN `geo_countries` c ON c.iso=l.iso
JOIN `geo_regions` r ON r.iso=l.iso AND r.admin=l.admin
WHERE l.city like fp_city;
END ;;
DROP PROCEDURE IF EXISTS `ip2city`;;
CREATE PROCEDURE `ip2city`(
fp_ipstr VARCHAR(15)
)
READS SQL DATA
DETERMINISTIC
BEGIN
DECLARE v_ip INT unsigned DEFAULT 0;
SET v_ip = ip4str_to_int( fp_ipstr );
SELECT CONCAT(l.city,IF(LENGTH(city), ', ', ''),
r.region,IF(LENGTH(r.region), ', ', ''),c.country)
FROM `geo_iprange` i JOIN `geo_blocks` b ON i.id = b.rangeid
JOIN `geo_locations` l ON i.locid=l.id
JOIN `geo_countries` c ON c.iso=l.iso
JOIN `geo_regions` r ON r.iso=l.iso AND r.admin=l.admin
WHERE i.ipstart<=v_ip AND i.ipend>=v_ip AND geoblock=(v_ip>>16)<<16;
END ;;
CREATE FUNCTION `ip4str_to_int`(
fp_ipstr VARCHAR(255)
) RETURNS int(10) unsigned
NO SQL
DETERMINISTIC
BEGIN
DECLARE v_part, v_rem VARCHAR(15) DEFAULT "";
DECLARE v_ret,v_pos INT unsigned DEFAULT 0;
SET v_rem = fp_ipstr;
WHILE LENGTH( v_rem ) DO
SET v_pos = LOCATE( '.', v_rem );
IF v_pos > 0 THEN
SET v_part = SUBSTRING( v_rem, 1, v_pos - 1);
SET v_rem = SUBSTRING( v_rem, v_pos + 1);
SET v_ret = ( v_ret << 8 ) + v_part;
ELSE
SET v_ret = ( v_ret << 8 ) + v_rem;
SET v_rem = '';
END IF;
END WHILE;
RETURN v_ret;
END ;;
DELIMITER ;
SELECT "Createing temp tables";
DROP TABLE IF EXISTS `geo_csv_city_blocks_ipv4`;
CREATE TABLE `geo_csv_city_blocks_ipv4` (
`network` VARCHAR(20) NOT NULL DEFAULT "",
`geoname_id` INT(10) unsigned NOT NULL,
`registered_country_geoname_id` INT(10) unsigned NOT NULL,
`represented_country_geoname_id` INT(10) unsigned NOT NULL,
`is_anonymous_proxy` TINYINT(1) unsigned NOT NULL,
`is_satellite_procider` TINYINT(1) unsigned NOT NULL,
`postal_code` VARCHAR(32) NOT NULL DEFAULT "",
`latitude` DECIMAL(8,4) NOT NULL DEFAULT "0.0",
`longitude` DECIMAL(8,4) NOT NULL DEFAULT "0.0",
`accuracy_radius` INT(6) unsigned NOT NULL
) ENGINE=Aria DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
DROP TABLE IF EXISTS `geo_csv_city_blocks_ipv6`;
CREATE TABLE `geo_csv_city_blocks_ipv6` (
`network` VARCHAR(32) NOT NULL DEFAULT "",
`geoname_id` INT(10) unsigned NOT NULL,
`registered_country_geoname_id` INT(10) unsigned NOT NULL,
`represented_country_geoname_id` INT(10) unsigned NOT NULL,
`is_anonymous_proxy` TINYINT(1) unsigned NOT NULL,
`is_satellite_procider` TINYINT(1) unsigned NOT NULL,
`postal_code` VARCHAR(32) NOT NULL DEFAULT "",
`latitude` DECIMAL(8,4) NOT NULL DEFAULT "0.0",
`longitude` DECIMAL(8,4) NOT NULL DEFAULT "0.0",
`accuracy_radius` INT(6) unsigned NOT NULL
) ENGINE=Aria DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
DROP TABLE IF EXISTS `geo_csv_country_blocks_ipv4`;
CREATE TABLE `geo_csv_country_blocks_ipv4` (
`network` VARCHAR(20) NOT NULL DEFAULT "",
`geoname_id` INT(10) unsigned NOT NULL,
`registered_country_geoname_id` INT(10) unsigned NOT NULL,
`represented_country_geoname_id` INT(10) unsigned NOT NULL,
`is_anonymous_proxy` TINYINT(1) unsigned NOT NULL,
`is_satellite_procider` TINYINT(1) unsigned NOT NULL
) ENGINE=Aria DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
DROP TABLE IF EXISTS `geo_csv_country_blocks_ipv6`;
CREATE TABLE `geo_csv_country_blocks_ipv6` (
`network` VARCHAR(32) NOT NULL DEFAULT "",
`geoname_id` INT(10) unsigned NOT NULL,
`registered_country_geoname_id` INT(10) unsigned NOT NULL,
`represented_country_geoname_id` INT(10) unsigned NOT NULL,
`is_anonymous_proxy` TINYINT(1) unsigned NOT NULL,
`is_satellite_procider` TINYINT(1) unsigned NOT NULL
) ENGINE=Aria DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
DROP TABLE IF EXISTS `geo_csv_asn_blocks_ipv4`;
CREATE TABLE `geo_csv_asn_blocks_ipv4` (
`network` VARCHAR(20) NOT NULL DEFAULT "",
`autonomous_system_number` INT(10) unsigned NOT NULL,
`autonomous_system_organization` VARCHAR(256) NOT NULL DEFAULT ""
) ENGINE=Aria DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
DROP TABLE IF EXISTS `geo_csv_asn_blocks_ipv6`;
CREATE TABLE `geo_csv_asn_blocks_ipv6` (
`network` VARCHAR(32) NOT NULL DEFAULT "",
`autonomous_system_number` INT(10) unsigned NOT NULL,
`autonomous_system_organization` VARCHAR(256) NOT NULL DEFAULT ""
) ENGINE=Aria DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
DROP TABLE IF EXISTS `geo_csv_country_locations`;
CREATE TABLE `geo_csv_country_locations` (
`geoname_id` INT(10) unsigned NOT NULL,
`locale_code` VARCHAR(4) NOT NULL DEFAULT "",
`continent_code` VARCHAR(4) NOT NULL DEFAULT "",
`continent_name` VARCHAR(32) NOT NULL DEFAULT "",
`country_iso_code` VARCHAR(4) NOT NULL DEFAULT "",
`country_name` VARCHAR(128) NOT NULL DEFAULT "",
`is_in_european_union` TINYINT(1) unsigned NOT NULL
) ENGINE=Aria DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
DROP TABLE IF EXISTS `geo_csv_city_locations`;
CREATE TABLE `geo_csv_city_locations` (
`geoname_id` INT(10) unsigned NOT NULL,
`locale_code` VARCHAR(4) NOT NULL DEFAULT "",
`continent_code` VARCHAR(4) NOT NULL DEFAULT "",
`continent_name` VARCHAR(20) NOT NULL DEFAULT "",
`country_iso_code` VARCHAR(4) NOT NULL DEFAULT "",
`country_name` VARCHAR(128) NOT NULL DEFAULT "",
`subdivision_1_iso_code` VARCHAR(4) NOT NULL DEFAULT "",
`subdivision_1_iso_name` VARCHAR(80) NOT NULL DEFAULT "",
`subdivision_2_iso_code` VARCHAR(4) NOT NULL DEFAULT "",
`subdivision_2_iso_name` VARCHAR(80) NOT NULL DEFAULT "",
`city_name` VARCHAR(128) NOT NULL DEFAULT "",
`metro_code` VARCHAR(8) NOT NULL DEFAULT "",
`time_zone` VARCHAR(80) NOT NULL DEFAULT "",
`is_in_european_union` TINYINT(1) unsigned NOT NULL
) ENGINE=Aria DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
DROP TABLE IF EXISTS `geo_txt_timezones`;
CREATE TABLE `geo_txt_timezones` (
`country_iso_code` VARCHAR(4) NOT NULL DEFAULT "",
`time_zone` VARCHAR(80) NOT NULL DEFAULT "",
`gmt_offset` DECIMAL(5,2) NOT NULL DEFAULT "0.0",
`dst_offset` DECIMAL(5,2) NOT NULL DEFAULT "0.0",
`raw_offset` DECIMAL(5,2) NOT NULL DEFAULT "0.0"
) ENGINE=Aria DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
SELECT "Loading Data";
LOAD DATA LOCAL INFILE "/storage/geoip/data/GeoLite2-CSV/GeoLite2-ASN-Blocks-IPv4.csv"
INTO TABLE `geo_csv_asn_blocks_ipv4` FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n'
IGNORE 1 LINES;
LOAD DATA LOCAL INFILE "/storage/geoip/data/GeoLite2-CSV/GeoLite2-ASN-Blocks-IPv6.csv"
INTO TABLE `geo_csv_asn_blocks_ipv6` FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n'
IGNORE 1 LINES;
LOAD DATA LOCAL INFILE "/storage/geoip/data/GeoLite2-CSV/GeoLite2-City-Blocks-IPv4.csv"
INTO TABLE `geo_csv_city_blocks_ipv4` FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n'
IGNORE 1 LINES;
LOAD DATA LOCAL INFILE "/storage/geoip/data/GeoLite2-CSV/GeoLite2-City-Blocks-IPv6.csv"
INTO TABLE `geo_csv_city_blocks_ipv6` FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n'
IGNORE 1 LINES;
LOAD DATA LOCAL INFILE "/storage/geoip/data/GeoLite2-CSV/GeoLite2-Country-Blocks-IPv4.csv"
INTO TABLE `geo_csv_country_blocks_ipv4` FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n'
IGNORE 1 LINES;
LOAD DATA LOCAL INFILE "/storage/geoip/data/GeoLite2-CSV/GeoLite2-Country-Blocks-IPv6.csv"
INTO TABLE `geo_csv_country_blocks_ipv6` FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n'
IGNORE 1 LINES;
LOAD DATA LOCAL INFILE "/storage/geoip/data/GeoLite2-CSV/GeoLite2-City-Locations-en.csv"
INTO TABLE `geo_csv_city_locations` FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n'
IGNORE 1 LINES;
LOAD DATA LOCAL INFILE "/storage/geoip/data/GeoLite2-CSV/GeoLite2-Country-Locations-en.csv"
INTO TABLE `geo_csv_country_locations` FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n'
IGNORE 1 LINES;
LOAD DATA LOCAL INFILE "/storage/geoip/data/timeZones.txt"
INTO TABLE `geo_txt_timezones` FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n'
IGNORE 1 LINES;
SELECT "Create new tables";
DROP TABLE IF EXISTS `geo_city_blocks_ipv4`;
CREATE TABLE `geo_city_blocks_ipv4` (
`network` VARCHAR(20) NOT NULL DEFAULT "",
`netmask` INT(3) unsigned NOT NULL,
`netval1` INT(3) unsigned NOT NULL,
`netval2` INT(3) unsigned NOT NULL,
`netval3` INT(3) unsigned NOT NULL,
`netval` INT(10) unsigned NOT NULL,
`geoname_id` INT(10) unsigned NOT NULL,
`registered_country_geoname_id` INT(10) unsigned NOT NULL,
`represented_country_geoname_id` INT(10) unsigned NOT NULL,
`is_anonymous_proxy` TINYINT(1) unsigned NOT NULL,
`is_satellite_provider` TINYINT(1) unsigned NOT NULL,
`postal_code` VARCHAR(32) NOT NULL DEFAULT "",
`latitude` DECIMAL(8,4) NOT NULL DEFAULT "0.0",
`longitude` DECIMAL(8,4) NOT NULL DEFAULT "0.0",
`accuracy_radius` INT(6) unsigned NOT NULL,
PRIMARY KEY ( `network` ),
KEY `geoidkey` ( `geoname_id` ),
KEY `regidkey` ( `registered_country_geoname_id` ),
KEY `repidkey` ( `represented_country_geoname_id` ),
KEY `anonkey` ( `is_anonymous_proxy` ),
KEY `satkey` ( `is_satellite_provider` ),
KEY `latkey` ( `latitude` ),
KEY `lonkey` ( `longitude` )
) ENGINE=Aria DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
DROP TABLE IF EXISTS `geo_city_blocks_ipv6`;
CREATE TABLE `geo_city_blocks_ipv6` (
`network` VARCHAR(32) NOT NULL DEFAULT "",
`geoname_id` INT(10) unsigned NOT NULL,
`registered_country_geoname_id` INT(10) unsigned NOT NULL,
`represented_country_geoname_id` INT(10) unsigned NOT NULL,
`is_anonymous_proxy` TINYINT(1) unsigned NOT NULL,
`is_satellite_provider` TINYINT(1) unsigned NOT NULL,
`postal_code` VARCHAR(32) NOT NULL DEFAULT "",
`latitude` DECIMAL(8,4) NOT NULL DEFAULT "0.0",
`longitude` DECIMAL(8,4) NOT NULL DEFAULT "0.0",
`accuracy_radius` INT(6) unsigned NOT NULL,
PRIMARY KEY ( `network` ),
KEY `geoidkey` ( `geoname_id` ),
KEY `regidkey` ( `registered_country_geoname_id` ),
KEY `repidkey` ( `represented_country_geoname_id` ),
KEY `anonkey` ( `is_anonymous_proxy` ),
KEY `satkey` ( `is_satellite_provider` ),
KEY `latkey` ( `latitude` ),
KEY `lonkey` ( `longitude` )
) ENGINE=Aria DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
DROP TABLE IF EXISTS `geo_country_blocks_ipv4`;
CREATE TABLE `geo_country_blocks_ipv4` (
`network` VARCHAR(20) NOT NULL DEFAULT "",
`geoname_id` INT(10) unsigned NOT NULL,
`registered_country_geoname_id` INT(10) unsigned NOT NULL,
`represented_country_geoname_id` INT(10) unsigned NOT NULL,
`is_anonymous_proxy` TINYINT(1) unsigned NOT NULL,
`is_satellite_provider` TINYINT(1) unsigned NOT NULL,
PRIMARY KEY ( `network` ),
KEY `geoidkey` ( `geoname_id` ),
KEY `regidkey` ( `registered_country_geoname_id` ),
KEY `repidkey` ( `represented_country_geoname_id` ),
KEY `anonkey` ( `is_anonymous_proxy` ),
KEY `satkey` ( `is_satellite_provider` )
) ENGINE=Aria DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
DROP TABLE IF EXISTS `geo_country_blocks_ipv6`;
CREATE TABLE `geo_country_blocks_ipv6` (
`network` VARCHAR(32) NOT NULL DEFAULT "",
`geoname_id` INT(10) unsigned NOT NULL,
`registered_country_geoname_id` INT(10) unsigned NOT NULL,
`represented_country_geoname_id` INT(10) unsigned NOT NULL,
`is_anonymous_proxy` TINYINT(1) unsigned NOT NULL,
`is_satellite_provider` TINYINT(1) unsigned NOT NULL,
PRIMARY KEY ( `network` ),
KEY `geoidkey` ( `geoname_id` ),
KEY `regidkey` ( `registered_country_geoname_id` ),
KEY `repidkey` ( `represented_country_geoname_id` ),
KEY `anonkey` ( `is_anonymous_proxy` ),
KEY `satkey` ( `is_satellite_provider` )
) ENGINE=Aria DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
DROP TABLE IF EXISTS `geo_asn_blocks_ipv4`;
CREATE TABLE `geo_asn_blocks_ipv4` (
`network` VARCHAR(20) NOT NULL DEFAULT "",
`autonomous_system_number` INT(10) unsigned NOT NULL,
`autonomous_system_organization` VARCHAR(128) NOT NULL DEFAULT "",
PRIMARY KEY ( `network` ),
KEY `asnkey` ( `autonomous_system_number` )
) ENGINE=Aria DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
DROP TABLE IF EXISTS `geo_asn_blocks_ipv6`;
CREATE TABLE `geo_asn_blocks_ipv6` (
`network` VARCHAR(32) NOT NULL DEFAULT "",
`autonomous_system_number` INT(10) unsigned NOT NULL,
`autonomous_system_organization` VARCHAR(128) NOT NULL DEFAULT "",
PRIMARY KEY ( `network` ),
KEY `asnkey` ( `autonomous_system_number` )
) ENGINE=Aria DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
DROP TABLE IF EXISTS `geo_country_locations`;
CREATE TABLE `geo_country_locations` (
`geoname_id` INT(10) unsigned NOT NULL,
`locale_code` VARCHAR(4) NOT NULL DEFAULT "",
`continent_code` VARCHAR(4) NOT NULL DEFAULT "",
`continent_name` VARCHAR(32) NOT NULL DEFAULT "",
`country_iso_code` VARCHAR(4) NOT NULL DEFAULT "",
`country_name` VARCHAR(80) NOT NULL DEFAULT "",
`is_in_european_union` TINYINT(1) unsigned NOT NULL,
PRIMARY KEY ( `geoname_id` ),
KEY `lockey` ( `locale_code` ),
KEY `contkey` ( `continent_code` ),
KEY `cisokey` ( `country_iso_code` ),
KEY `cnamkey` ( `continent_name`,`country_name` ),
KEY `eurokey` ( `is_in_european_union` )
) ENGINE=Aria DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
DROP TABLE IF EXISTS `geo_city_locations`;
CREATE TABLE `geo_city_locations` (
`geoname_id` INT(10) unsigned NOT NULL,
`locale_code` VARCHAR(4) NOT NULL DEFAULT "",
`continent_code` VARCHAR(4) NOT NULL DEFAULT "",
`continent_name` VARCHAR(20) NOT NULL DEFAULT "",
`country_iso_code` VARCHAR(4) NOT NULL DEFAULT "",
`country_name` VARCHAR(80) NOT NULL DEFAULT "",
`subdivision_1_iso_code` VARCHAR(4) NOT NULL DEFAULT "",
`subdivision_1_iso_name` VARCHAR(80) NOT NULL DEFAULT "",
`subdivision_2_iso_code` VARCHAR(4) NOT NULL DEFAULT "",
`subdivision_2_iso_name` VARCHAR(80) NOT NULL DEFAULT "",
`city_name` VARCHAR(80) NOT NULL DEFAULT "",
`metro_code` VARCHAR(8) NOT NULL DEFAULT "",
`time_zone` VARCHAR(80) NOT NULL DEFAULT "",
`is_in_european_union` TINYINT(1) unsigned NOT NULL,
PRIMARY KEY ( `geoname_id` ),
KEY `lockey` ( `locale_code` ),
KEY `contkey` ( `continent_code` ),
KEY `cisokey` ( `country_iso_code` ),
KEY `cnamkey` ( `continent_name`,`country_name`,`city_name` ),
KEY `rnamkey` ( `city_name`,`country_name`,`continent_name` ),
KEY `eurokey` ( `is_in_european_union` )
) ENGINE=Aria DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
DROP TABLE IF EXISTS `geo_timezones`;
CREATE TABLE `geo_timezones` (
`country_iso_code` VARCHAR(4) NOT NULL DEFAULT "",
`time_zone` VARCHAR(80) NOT NULL DEFAULT "",
`gmt_offset` DECIMAL(5,2) NOT NULL DEFAULT "0.0",
`dst_offset` DECIMAL(5,2) NOT NULL DEFAULT "0.0",
`raw_offset` DECIMAL(5,2) NOT NULL DEFAULT "0.0",
PRIMARY KEY ( `time_zone` ),
KEY `isokey` ( `country_iso_code` )
) ENGINE=Aria DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
SELECT "Fill new tables";
-- Clean out any duplicates and create keys
INSERT IGNORE `geo_asn_blocks_ipv4` SELECT * FROM `geo_csv_asn_blocks_ipv4`;
INSERT IGNORE `geo_asn_blocks_ipv6` SELECT * FROM `geo_csv_asn_blocks_ipv6`;
INSERT IGNORE `geo_city_blocks_ipv4` SELECT * FROM `geo_csv_city_blocks_ipv4`;
INSERT IGNORE `geo_city_blocks_ipv6` SELECT * FROM `geo_csv_city_blocks_ipv6`;
INSERT IGNORE `geo_country_blocks_ipv4` SELECT * FROM `geo_csv_country_blocks_ipv4`;
INSERT IGNORE `geo_country_blocks_ipv6` SELECT * FROM `geo_csv_country_blocks_ipv6`;
INSERT IGNORE `geo_city_locations` SELECT * FROM `geo_csv_city_locations`;
INSERT IGNORE `geo_country_locations` SELECT * FROM `geo_csv_country_locations`;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment