Skip to content

Instantly share code, notes, and snippets.

@diogenese

diogenese/nopaste

Created April 17, 2019 09:52
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/d62ef63dbf6f85478ed9609fb241f670 to your computer and use it in GitHub Desktop.
Save diogenese/d62ef63dbf6f85478ed9609fb241f670 to your computer and use it in GitHub Desktop.
Download ZIP
latest giolite sql
Raw
nopaste
-- Ensure database exists
CREATE DATABASE IF NOT EXISTS `geoipdb` DEFAULT CHARACTER SET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
USE `geoipdb`
DROP TABLE IF EXISTS `geo_csv_city_blocks_ipv4`;
CREATE TABLE `geo_csv_city_blocks_ipv4` (
`network` VARCHAR(20) NOT NULL DEFAULT "",
`ipbyte1` INT(3) unsigned NOT NULL,
`ipbyte2` INT(3) unsigned NOT NULL,
`ipbyte3` INT(3) unsigned NOT NULL,
`ipbyte4` INT(3) unsigned NOT NULL,
`netmask` INT(3) unsigned NOT NULL,
`geonameid` INT(10) unsigned NOT NULL,
`rgcgeoid` INT(10) unsigned NOT NULL,
`rpcgeoid` INT(10) unsigned NOT NULL,
`is_anon` TINYINT(1) unsigned NOT NULL,
`is_sat` 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 TEMPORARY TABLE IF EXISTS `geo_csv_city_blocks_ipv6`;
CREATE TEMPORARY TABLE `geo_csv_city_blocks_ipv6` (
`network` VARCHAR(32) NOT NULL DEFAULT "",
`ipword1` VARCHAR(4) NOT NULL DEFAULT "",
`ipword2` VARCHAR(4) NOT NULL DEFAULT "",
`ipword3` VARCHAR(4) NOT NULL DEFAULT "",
`ipword4` VARCHAR(4) NOT NULL DEFAULT "",
`ifword1` VARCHAR(4) NOT NULL DEFAULT "",
`ifword2` VARCHAR(4) NOT NULL DEFAULT "",
`ifword3` VARCHAR(4) NOT NULL DEFAULT "",
`ifword4` VARCHAR(4) NOT NULL DEFAULT "",
`netmask` INT(3) unsigned NOT NULL,
`geonameid` INT(10) unsigned NOT NULL,
`rgcgeoid` INT(10) unsigned NOT NULL,
`rpcgeoid` INT(10) unsigned NOT NULL,
`is_anon` TINYINT(1) unsigned NOT NULL,
`is_sat` 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 TEMPORARY TABLE IF EXISTS `geo_csv_country_blocks_ipv4`;
CREATE TEMPORARY TABLE `geo_csv_country_blocks_ipv4` (
`network` VARCHAR(20) NOT NULL DEFAULT "",
`ipbyte1` INT(3) unsigned NOT NULL,
`ipbyte2` INT(3) unsigned NOT NULL,
`ipbyte3` INT(3) unsigned NOT NULL,
`ipbyte4` INT(3) unsigned NOT NULL,
`netmask` INT(3) unsigned NOT NULL,
`geonameid` INT(10) unsigned NOT NULL,
`rgcgeoid` INT(10) unsigned NOT NULL,
`rpcgeoid` INT(10) unsigned NOT NULL,
`is_anon` TINYINT(1) unsigned NOT NULL,
`is_sat` TINYINT(1) unsigned NOT NULL
) ENGINE=Aria DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
DROP TEMPORARY TABLE IF EXISTS `geo_csv_country_blocks_ipv6`;
CREATE TEMPORARY TABLE `geo_csv_country_blocks_ipv6` (
`network` VARCHAR(32) NOT NULL DEFAULT "",
`ipword1` VARCHAR(4) NOT NULL DEFAULT "",
`ipword2` VARCHAR(4) NOT NULL DEFAULT "",
`ipword3` VARCHAR(4) NOT NULL DEFAULT "",
`ipword4` VARCHAR(4) NOT NULL DEFAULT "",
`ifword1` VARCHAR(4) NOT NULL DEFAULT "",
`ifword2` VARCHAR(4) NOT NULL DEFAULT "",
`ifword3` VARCHAR(4) NOT NULL DEFAULT "",
`ifword4` VARCHAR(4) NOT NULL DEFAULT "",
`netmask` INT(3) unsigned NOT NULL,
`geonameid` INT(10) unsigned NOT NULL,
`rgcgeoid` INT(10) unsigned NOT NULL,
`rpcgeoid` INT(10) unsigned NOT NULL,
`is_anon` TINYINT(1) unsigned NOT NULL,
`is_sat` TINYINT(1) unsigned NOT NULL
) ENGINE=Aria DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
DROP TEMPORARY TABLE IF EXISTS `geo_csv_asn_blocks_ipv4`;
CREATE TEMPORARY TABLE `geo_csv_asn_blocks_ipv4` (
`network` VARCHAR(20) NOT NULL DEFAULT "",
`ipbyte1` INT(3) unsigned NOT NULL,
`ipbyte2` INT(3) unsigned NOT NULL,
`ipbyte3` INT(3) unsigned NOT NULL,
`ipbyte4` INT(3) unsigned NOT NULL,
`netmask` INT(3) unsigned NOT NULL,
`as_number` INT(10) unsigned NOT NULL,
`as_org` VARCHAR(256) NOT NULL DEFAULT ""
) ENGINE=Aria DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
DROP TEMPORARY TABLE IF EXISTS `geo_csv_asn_blocks_ipv6`;
CREATE TEMPORARY TABLE `geo_csv_asn_blocks_ipv6` (
`network` VARCHAR(32) NOT NULL DEFAULT "",
`ipword1` VARCHAR(4) NOT NULL DEFAULT "",
`ipword2` VARCHAR(4) NOT NULL DEFAULT "",
`ipword3` VARCHAR(4) NOT NULL DEFAULT "",
`ipword4` VARCHAR(4) NOT NULL DEFAULT "",
`ifword1` VARCHAR(4) NOT NULL DEFAULT "",
`ifword2` VARCHAR(4) NOT NULL DEFAULT "",
`ifword3` VARCHAR(4) NOT NULL DEFAULT "",
`ifword4` VARCHAR(4) NOT NULL DEFAULT "",
`netmask` INT(3) unsigned NOT NULL,
`as_number` INT(10) unsigned NOT NULL,
`as_org` VARCHAR(256) NOT NULL DEFAULT ""
) ENGINE=Aria DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
DROP TEMPORARY TABLE IF EXISTS `geo_csv_country_locations`;
CREATE TEMPORARY TABLE `geo_csv_country_locations` (
`geonameid` INT(10) unsigned NOT NULL,
`locale_code` VARCHAR(4) NOT NULL DEFAULT "",
`cont_code` VARCHAR(4) NOT NULL DEFAULT "",
`cont_name` VARCHAR(32) NOT NULL DEFAULT "",
`cntr_iso` VARCHAR(4) NOT NULL DEFAULT "",
`cntr_name` VARCHAR(128) NOT NULL DEFAULT "",
`is_euro` TINYINT(1) unsigned NOT NULL
) ENGINE=Aria DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
DROP TEMPORARY TABLE IF EXISTS `geo_csv_city_locations`;
CREATE TEMPORARY TABLE `geo_csv_city_locations` (
`geonameid` INT(10) unsigned NOT NULL,
`locale_code` VARCHAR(4) NOT NULL DEFAULT "",
`cont_code` VARCHAR(4) NOT NULL DEFAULT "",
`cont_name` VARCHAR(20) NOT NULL DEFAULT "",
`cntr_iso` VARCHAR(4) NOT NULL DEFAULT "",
`cntr_name` VARCHAR(128) NOT NULL DEFAULT "",
`sub1_code` VARCHAR(4) NOT NULL DEFAULT "",
`sub1_name` VARCHAR(80) NOT NULL DEFAULT "",
`sub2_code` VARCHAR(4) NOT NULL DEFAULT "",
`sub2_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_euro` TINYINT(1) unsigned NOT NULL
) ENGINE=Aria DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
SELECT "Loading Data";
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/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;
SELECT "Create new tables";
DROP TABLE IF EXISTS `geo_city_blocks_ipv4`;
CREATE TABLE `geo_city_blocks_ipv4` (
`network` VARCHAR(32) NOT NULL DEFAULT "",
`ipbyte1` INT(3) unsigned NOT NULL,
`ipbyte2` INT(3) unsigned NOT NULL,
`ipbyte3` INT(3) unsigned NOT NULL,
`ipbyte4` INT(3) unsigned NOT NULL,
`netmask` INT(3) unsigned NOT NULL,
`ipstart` BIGINT(20) unsigned NOT NULL,
`ipend` BIGINT(20) unsigned NOT NULL,
`geonameid` INT(10) unsigned NOT NULL,
`rgcgeoid` INT(10) unsigned NOT NULL,
`rpcgeoid` INT(10) unsigned NOT NULL,
`is_anon` TINYINT(1) unsigned NOT NULL,
`is_sat` 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` )
) 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 "",
`ipword1` INT(5) unsigned NOT NULL,
`ipword2` INT(5) unsigned NOT NULL,
`ipword3` INT(5) unsigned NOT NULL,
`ipword4` INT(5) unsigned NOT NULL,
`ifword1` INT(5) unsigned NOT NULL,
`ifword2` INT(5) unsigned NOT NULL,
`ifword3` INT(5) unsigned NOT NULL,
`ifword4` INT(5) unsigned NOT NULL,
`netmask` INT(3) unsigned NOT NULL,
`ipstart` BIGINT(20) unsigned NOT NULL,
`ipend` BIGINT(20) unsigned NOT NULL,
`ifstart` BIGINT(20) unsigned NOT NULL,
`ifend` BIGINT(20) unsigned NOT NULL,
`geonameid` INT(10) unsigned NOT NULL,
`rgcgeoid` INT(10) unsigned NOT NULL,
`rpcgeoid` INT(10) unsigned NOT NULL,
`is_anon` TINYINT(1) unsigned NOT NULL,
`is_sat` 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` )
) 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 "",
`ipbyte1` INT(3) unsigned NOT NULL,
`ipbyte2` INT(3) unsigned NOT NULL,
`ipbyte3` INT(3) unsigned NOT NULL,
`ipbyte4` INT(3) unsigned NOT NULL,
`netmask` INT(3) unsigned NOT NULL,
`ipstart` BIGINT(20) unsigned NOT NULL,
`ipend` BIGINT(20) unsigned NOT NULL,
`geonameid` INT(10) unsigned NOT NULL,
`rgcgeoid` INT(10) unsigned NOT NULL,
`rpcgeoid` INT(10) unsigned NOT NULL,
`is_anon` TINYINT(1) unsigned NOT NULL,
`is_sat` TINYINT(1) unsigned NOT NULL,
PRIMARY KEY ( `network` )
) 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 "",
`ipword1` INT(5) unsigned NOT NULL,
`ipword2` INT(5) unsigned NOT NULL,
`ipword3` INT(5) unsigned NOT NULL,
`ipword4` INT(5) unsigned NOT NULL,
`ifword1` INT(5) unsigned NOT NULL,
`ifword2` INT(5) unsigned NOT NULL,
`ifword3` INT(5) unsigned NOT NULL,
`ifword4` INT(5) unsigned NOT NULL,
`netmask` INT(3) unsigned NOT NULL,
`ipstart` BIGINT(20) unsigned NOT NULL,
`ipend` BIGINT(20) unsigned NOT NULL,
`ifstart` BIGINT(20) unsigned NOT NULL,
`ifend` BIGINT(20) unsigned NOT NULL,
`geonameid` INT(10) unsigned NOT NULL,
`rgcgeoid` INT(10) unsigned NOT NULL,
`rpcgeoid` INT(10) unsigned NOT NULL,
`is_anon` TINYINT(1) unsigned NOT NULL,
`is_sat` TINYINT(1) unsigned NOT NULL,
PRIMARY KEY ( `network` )
) 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 "",
`ipbyte1` INT(3) unsigned NOT NULL,
`ipbyte2` INT(3) unsigned NOT NULL,
`ipbyte3` INT(3) unsigned NOT NULL,
`ipbyte4` INT(3) unsigned NOT NULL,
`netmask` INT(3) unsigned NOT NULL,
`ipstart` BIGINT(20) unsigned NOT NULL,
`ipend` BIGINT(20) unsigned NOT NULL,
`as_number` INT(10) unsigned NOT NULL,
`as_org` VARCHAR(128) NOT NULL DEFAULT "",
PRIMARY KEY ( `network` )
) 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 "",
`ipword1` INT(5) unsigned NOT NULL,
`ipword2` INT(5) unsigned NOT NULL,
`ipword3` INT(5) unsigned NOT NULL,
`ipword4` INT(5) unsigned NOT NULL,
`ifword1` INT(5) unsigned NOT NULL,
`ifword2` INT(5) unsigned NOT NULL,
`ifword3` INT(5) unsigned NOT NULL,
`ifword4` INT(5) unsigned NOT NULL,
`netmask` INT(3) unsigned NOT NULL,
`ipstart` BIGINT(20) unsigned NOT NULL,
`ipend` BIGINT(20) unsigned NOT NULL,
`ifstart` BIGINT(20) unsigned NOT NULL,
`ifend` BIGINT(20) unsigned NOT NULL,
`as_number` INT(10) unsigned NOT NULL,
`as_org` VARCHAR(128) NOT NULL DEFAULT "",
PRIMARY KEY ( `network` )
) ENGINE=Aria DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
DROP TABLE IF EXISTS `geo_city_locations`;
CREATE TABLE `geo_city_locations` (
`geonameid` INT(10) unsigned NOT NULL,
`locale_code` VARCHAR(4) NOT NULL DEFAULT "",
`cont_code` VARCHAR(4) NOT NULL DEFAULT "",
`cont_name` VARCHAR(20) NOT NULL DEFAULT "",
`cntr_iso` VARCHAR(4) NOT NULL DEFAULT "",
`cntr_name` VARCHAR(80) NOT NULL DEFAULT "",
`sub1_code` VARCHAR(4) NOT NULL DEFAULT "",
`sub1_name` VARCHAR(80) NOT NULL DEFAULT "",
`sub2_code` VARCHAR(4) NOT NULL DEFAULT "",
`sub2_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_euro` TINYINT(1) unsigned NOT NULL,
PRIMARY KEY ( `geonameid` )
) ENGINE=Aria DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
DROP TABLE IF EXISTS `geo_country_locations`;
CREATE TABLE `geo_country_locations` (
`geonameid` INT(10) unsigned NOT NULL,
`locale_code` VARCHAR(4) NOT NULL DEFAULT "",
`cont_code` VARCHAR(4) NOT NULL DEFAULT "",
`cont_name` VARCHAR(32) NOT NULL DEFAULT "",
`cntr_iso` VARCHAR(4) NOT NULL DEFAULT "",
`cntr_name` VARCHAR(80) NOT NULL DEFAULT "",
`is_euro` TINYINT(1) unsigned NOT NULL,
PRIMARY KEY ( `geonameid` )
) ENGINE=Aria DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
SELECT "Fill new tables";
-- Clean out any duplicates and create keys
INSERT IGNORE `geo_city_blocks_ipv6` ( network,ipword1,ipword2,ipword3,ipword4,ifword1,ifword2,ifword3,ifword4,netmask,
ipstart,ipend,ifstart,ifend,geonameid,rgcgeoid,rpcgeoid,is_anon,is_sat,postal_code,latitude,longitude,accuracy_radius )
SELECT network,@ipv1:=conv(if(length(ipword1),ipword1,"0"),16,10),@ipv2:=conv(if(length(ipword2),ipword2,"0"),16,10),
@ipv3:=conv(if(length(ipword3),ipword3,"0"),16,10),@ipv4:=conv(if(length(ipword4),ipword4,"0"),16,10),
@ifv1:=conv(if(length(ifword1),ifword1,"0"),16,10),@ifv2:=conv(if(length(ifword2),ifword2,"0"),16,10),
@ifv3:=conv(if(length(ifword3),ifword3,"0"),16,10),@ifv4:=conv(if(length(ifword4),ifword4,"0"),16,10),netmask,
@ipv:=((@ipv1<<48)+(@ipv2<<32)+(@ipv3<<16)+@ipv4), if(netmask>=64,@ipv,@ipv+(1<<64-netmask)-1),
@ifv:=((@ifv1<<48)+(@ifv2<<32)+(@ifv3<<16)+@ifv4), if(netmask>=128,@ifv,if(netmask<=64,convert(-1,unsigned),@ifv+(1<<(128-netmask))-1)),
geonameid,rgcgeoid,rpcgeoid,is_anon,is_sat,postal_code,latitude,longitude,accuracy_radius FROM `geo_csv_city_blocks_ipv6`;
INSERT IGNORE `geo_country_blocks_ipv6` ( network,ipword1,ipword2,ipword3,ipword4,ifword1,ifword2,ifword3,ifword4,netmask,
ipstart,ipend,ifstart,ifend,geonameid,rgcgeoid,rpcgeoid,is_anon,is_sat )
SELECT network,@ipv1:=conv(if(length(ipword1),ipword1,"0"),16,10),@ipv2:=conv(if(length(ipword2),ipword2,"0"),16,10),
@ipv3:=conv(if(length(ipword3),ipword3,"0"),16,10),@ipv4:=conv(if(length(ipword4),ipword4,"0"),16,10),
@ifv1:=conv(if(length(ifword1),ifword1,"0"),16,10),@ifv2:=conv(if(length(ifword2),ifword2,"0"),16,10),
@ifv3:=conv(if(length(ifword3),ifword3,"0"),16,10),@ifv4:=conv(if(length(ifword4),ifword4,"0"),16,10),netmask,
@ipv:=((@ipv1<<48)+(@ipv2<<32)+(@ipv3<<16)+@ipv4),if(netmask>=64,@ipv,@ipv+(1<<64-netmask)-1),
@ifv:=((@ifv1<<48)+(@ifv2<<32)+(@ifv3<<16)+@ifv4), if(netmask>=128,@ifv,if(netmask<=64,convert(-1,unsigned),@ifv+(1<<(128-netmask))-1)),
geonameid,rgcgeoid,rpcgeoid,is_anon,is_sat FROM `geo_csv_country_blocks_ipv6`;
INSERT IGNORE `geo_asn_blocks_ipv6` ( network,ipword1,ipword2,ipword3,ipword4,ifword1,ifword2,ifword3,ifword4,netmask,
ipstart,ipend,ifstart,ifend,as_number,as_org )
SELECT network,@ipv1:=conv(if(length(ipword1),ipword1,"0"),16,10),@ipv2:=conv(if(length(ipword2),ipword2,"0"),16,10),
@ipv3:=conv(if(length(ipword3),ipword3,"0"),16,10),@ipv4:=conv(if(length(ipword4),ipword4,"0"),16,10),
@ifv1:=conv(if(length(ifword1),ifword1,"0"),16,10),@ifv2:=conv(if(length(ifword2),ifword2,"0"),16,10),
@ifv3:=conv(if(length(ifword3),ifword3,"0"),16,10),@ifv4:=conv(if(length(ifword4),ifword4,"0"),16,10),netmask,
@ipv:=((@ipv1<<48)+(@ipv2<<32)+(@ipv3<<16)+@ipv4),if(netmask>=64,@ipv,@ipv+(1<<64-netmask)-1),
@ifv:=((@ifv1<<48)+(@ifv2<<32)+(@ifv3<<16)+@ifv4), if(netmask>=128,@ifv,if(netmask<=64,convert(-1,unsigned),@ifv+(1<<(128-netmask))-1)),
as_number,as_org FROM `geo_csv_asn_blocks_ipv6`;
INSERT IGNORE `geo_city_blocks_ipv4` ( network,ipbyte1,ipbyte2,ipbyte3,ipbyte4,netmask,ipstart,ipend,geonameid,
rgcgeoid,rpcgeoid,is_anon,is_sat,postal_code,latitude,longitude,accuracy_radius )
SELECT network,ipbyte1,ipbyte2,ipbyte3,ipbyte4,netmask,@ipv:=((ipbyte1<<24)+(ipbyte2<<16)+(ipbyte3<<8)+ipbyte4),@ipv+(1<<(32-netmask))-1,
geonameid,rgcgeoid,rpcgeoid,is_anon,is_sat,postal_code,latitude,longitude,accuracy_radius FROM `geo_csv_city_blocks_ipv4`;
INSERT IGNORE `geo_country_blocks_ipv4` ( network,ipbyte1,ipbyte2,ipbyte3,ipbyte4,netmask,ipstart,ipend,geonameid,rgcgeoid,rpcgeoid,
is_anon,is_sat )
SELECT network,ipbyte1,ipbyte2,ipbyte3,ipbyte4,netmask,@ipv:=((ipbyte1<<24)+(ipbyte2<<16)+(ipbyte3<<8)+ipbyte4),@ipv+(1<<(32-netmask))-1,
geonameid,rgcgeoid,rpcgeoid,is_anon,is_sat FROM `geo_csv_country_blocks_ipv4`;
INSERT IGNORE `geo_asn_blocks_ipv4` ( network,ipbyte1,ipbyte2,ipbyte3,ipbyte4,netmask,ipstart,ipend,as_number,as_org )
SELECT network,ipbyte1,ipbyte2,ipbyte3,ipbyte4,netmask,@ipv:=((ipbyte1<<24)+(ipbyte2<<16)+(ipbyte3<<8)+ipbyte4),@ipv+(1<<(32-netmask))-1,
as_number,as_org FROM `geo_csv_asn_blocks_ipv4`;
INSERT IGNORE `geo_city_locations` SELECT * FROM `geo_csv_city_locations`;
INSERT IGNORE `geo_country_locations` SELECT * FROM `geo_csv_country_locations`;
SELECT "Add table keys";
ALTER TABLE `geo_city_blocks_ipv4`
ADD UNIQUE KEY `ipnumkey` ( `ipstart`,`ipend` ),
ADD KEY `bytekey` ( `ipbyte1`,`ipbyte2`,`ipbyte3`,`ipbyte4`),
ADD KEY `geoidkey` ( `geonameid` ),
ADD KEY `regidkey` ( `rgcgeoid` ),
ADD KEY `repidkey` ( `rpcgeoid` ),
ADD KEY `anonkey` ( `is_anon` ),
ADD KEY `satkey` ( `is_sat` ),
ADD KEY `latkey` ( `latitude` ),
ADD KEY `lonkey` ( `longitude` );
ALTER TABLE `geo_city_blocks_ipv6`
ADD KEY `ipnumkey` ( `ipstart`,`ifstart`,`ipend`,`ifend` ),
ADD KEY `ipwkey` ( `ipword1`,`ipword2`,`ipword3`,`ipword4` ),
ADD KEY `geoidkey` ( `geonameid` ),
ADD KEY `regidkey` ( `rgcgeoid` ),
ADD KEY `repidkey` ( `rpcgeoid` ),
ADD KEY `anonkey` ( `is_anon` ),
ADD KEY `satkey` ( `is_sat` ),
ADD KEY `latkey` ( `latitude` ),
ADD KEY `lonkey` ( `longitude` );
ALTER TABLE `geo_country_blocks_ipv4`
ADD UNIQUE KEY `ipnumkey` ( `ipstart`,`ipend` ),
ADD KEY `bytekey` ( `ipbyte1`,`ipbyte2`,`ipbyte3`,`ipbyte4`),
ADD KEY `geoidkey` ( `geonameid` ),
ADD KEY `regidkey` ( `rgcgeoid` ),
ADD KEY `repidkey` ( `rpcgeoid` ),
ADD KEY `anonkey` ( `is_anon` ),
ADD KEY `satkey` ( `is_sat` );
ALTER TABLE `geo_country_blocks_ipv6`
ADD KEY `ipnumkey` ( `ipstart`,`ifstart`,`ipend`,`ifend` ),
ADD KEY `ipwkey` ( `ipword1`,`ipword2`,`ipword3`,`ipword4` ),
ADD KEY `geoidkey` ( `geonameid` ),
ADD KEY `regidkey` ( `rgcgeoid` ),
ADD KEY `repidkey` ( `rpcgeoid` ),
ADD KEY `anonkey` ( `is_anon` ),
ADD KEY `satkey` ( `is_sat` );
ALTER TABLE `geo_asn_blocks_ipv4`
ADD UNIQUE KEY `ipnumkey` ( `ipstart`,`ipend` ),
ADD KEY `bytekey` ( `ipbyte1`,`ipbyte2`,`ipbyte3`,`ipbyte4`),
ADD KEY `asnkey` ( `as_number` );
ALTER TABLE `geo_asn_blocks_ipv6`
ADD KEY `ipnumkey` ( `ipstart`,`ifstart`,`ipend`,`ifend` ),
ADD KEY `ipwkey` ( `ipword1`,`ipword2`,`ipword3`,`ipword4` ),
ADD KEY `asnkey` ( `as_number` );
ALTER TABLE `geo_city_locations`
ADD KEY `lockey` ( `locale_code` ),
ADD KEY `contkey` ( `cont_code` ),
ADD KEY `cisokey` ( `cntr_iso` ),
ADD KEY `cnamkey` ( `cont_name`,`cntr_name`,`city_name` ),
ADD KEY `rnamkey` ( `city_name`,`cntr_name`,`cont_name` ),
ADD KEY `eurokey` ( `is_euro` );
ALTER TABLE `geo_country_locations`
ADD KEY `lockey` ( `locale_code` ),
ADD KEY `contkey` ( `cont_code` ),
ADD KEY `cisokey` ( `cntr_iso` ),
ADD KEY `eurokey` ( `is_euro` ),
ADD KEY `cnamkey` ( `cont_name`,`cntr_name` );
Select "Drop temp tables";
DROP TEMPORARY TABLE IF EXISTS `geo_csv_city_blocks_ipv4`;
DROP TEMPORARY TABLE IF EXISTS `geo_csv_city_blocks_ipv6`;
DROP TEMPORARY TABLE IF EXISTS `geo_csv_country_blocks_ipv4`;
DROP TEMPORARY TABLE IF EXISTS `geo_csv_country_blocks_ipv6`;
DROP TEMPORARY TABLE IF EXISTS `geo_csv_asn_blocks_ipv6`;
DROP TEMPORARY TABLE IF EXISTS `geo_csv_country_locations`;
DROP TEMPORARY TABLE IF EXISTS `geo_csv_city_locations`;
SELECT "Creating functions and procedures";
DELIMITER ;;
DROP PROCEDURE IF EXISTS `ip2city`;;
CREATE PROCEDURE `ip2city`(
fp_ipstr VARCHAR(40)
)
READS SQL DATA
DETERMINISTIC
BEGIN
DECLARE v_ip, v_pos, v_b1 INT unsigned DEFAULT 0;
DECLARE v_place VARCHAR(250) DEFAULT "";
DECLARE v_ip6, v_if6 BIGINT unsigned DEFAULT 0;
SET v_pos = LOCATE( '.', fp_ipstr );
IF v_pos THEN
SET v_b1 = SUBSTRING(fp_ipstr,1,v_pos-1);
SET v_ip = ip4str_to_int( fp_ipstr );
SELECT CONCAT(cl.city_name, IF(LENGTH(cl.city_name), ', ', ''),
cl.sub1_name, IF(LENGTH(cl.sub1_name), ', ', ''),
cl.sub2_name, IF(LENGTH(cl.sub2_name), ', ', ''),
cl.cntr_name) INTO v_place
FROM `geo_city_blocks_ipv4` cb
JOIN `geo_city_locations` cl ON cb.geonameid = cl.geonameid
WHERE cb.ipbyte1 = v_b1 AND cb.ipstart<=v_ip AND cb.ipend>=v_ip;
IF ISNULL(v_place) OR NOT LENGTH( v_place ) THEN
SELECT ab.as_org INTO v_place
FROM `geo_asn_blocks_ipv4` ab
WHERE ab.ipbyte1 = v_b1 AND ab.ipstart<=v_ip AND ab.ipend>=v_ip;
END IF;
ELSE
SET v_pos = LOCATE( ':', fp_ipstr );
IF v_pos THEN
SET v_b1 = ip6wordval(fp_ipstr,1);
SET v_ip6 = (v_b1<<48)+(ip6wordval(fp_ipstr,2)<<32)+
(ip6wordval(fp_ipstr,3)<<16)+ip6wordval(fp_ipstr,4);
SET v_if6 = (ip6wordval(fp_ipstr,5)<<48)+(ip6wordval(fp_ipstr,6)<<32)+
(ip6wordval(fp_ipstr,7)<<16)+ip6wordval(fp_ipstr,8);
SELECT CONCAT(cl.city_name, IF(LENGTH(cl.city_name), ', ', ''),
cl.sub1_name, IF(LENGTH(cl.sub1_name), ', ', ''),
cl.sub2_name, IF(LENGTH(cl.sub2_name), ', ', ''),
cl.cntr_name) INTO v_place
FROM `geo_city_blocks_ipv6` cb
JOIN `geo_city_locations` cl ON cb.geonameid = cl.geonameid
WHERE cb.ipword1 = v_b1 AND cb.ipstart<=v_ip6 AND cb.ipend>=v_ip6 AND cb.ifstart<=v_if6 AND cb.ifend>=v_if6;
IF ISNULL(v_place) OR NOT LENGTH( v_place ) THEN
SELECT ab.as_org INTO v_place
FROM `geo_asn_blocks_ipv6` ab
WHERE ab.ipword1 = v_b1 AND ab.ipstart<=v_ip6 AND ab.ipend>=v_ip6 AND ab.ifstart<=v_if6 AND ab.ifend>=v_if6;
END IF;
END IF;
END IF;
SELECT IF(LENGTH(v_place),v_place,"Nothing found");
END ;;
DROP PROCEDURE IF EXISTS `ip2map`;;
CREATE PROCEDURE `ip2map`(
fp_ipstr VARCHAR(40)
)
READS SQL DATA
DETERMINISTIC
BEGIN
DECLARE v_ip, v_pos, v_b1 INT unsigned DEFAULT 0;
DECLARE v_place VARCHAR(250) DEFAULT "";
DECLARE v_ip6, v_if6 BIGINT unsigned DEFAULT 0;
SET v_pos = LOCATE( '.', fp_ipstr );
IF v_pos THEN
SET v_b1 = SUBSTRING(fp_ipstr,1,v_pos-1);
SET v_ip = ip4str_to_int( fp_ipstr );
IF v_ip AND V_b1 THEN
SELECT CONCAT("https://www.google.com/maps/@",cb.latitude-0.074,',',cb.longitude,',',gc.gtopo30+6500,'a,35y,358.42h,48.49t/data=!3m1!1e3') INTO v_place
FROM geo_city_blocks_ipv4 cb JOIN geo_cities gc ON gc.geonameid = cb.geonameid
WHERE cb.ipbyte1 = v_b1 AND cb.ipstart<=v_ip AND cb.ipend>=v_ip;
END IF;
ELSE
SET v_pos = LOCATE( ':', fp_ipstr );
IF v_pos THEN
SET v_b1 = ip6wordval(fp_ipstr,1);
SET v_ip6 = (v_b1<<48)+(ip6wordval(fp_ipstr,2)<<32)+
(ip6wordval(fp_ipstr,3)<<16)+ip6wordval(fp_ipstr,4);
SET v_if6 = (ip6wordval(fp_ipstr,5)<<48)+(ip6wordval(fp_ipstr,6)<<32)+
(ip6wordval(fp_ipstr,7)<<16)+ip6wordval(fp_ipstr,8);
SELECT CONCAT("https://www.google.com/maps/@",cb.latitude-0.074,',',cb.longitude,',',gc.gtopo30+6500,'a,35y,358.42h,48.49t/data=!3m1!1e3') INTO v_place
FROM `geo_city_blocks_ipv6` cb JOIN geo_cities gc ON gc.geonameid = cb.geonameid
WHERE cb.ipword1 = v_b1 AND cb.ipstart<=v_ip6 AND cb.ipend>=v_ip6 AND cb.ifstart<=v_if6 AND cb.ifend>=v_if6;
END IF;
END IF;
SELECT IF(LENGTH(v_place),v_place,"Nothing found");
END ;;
DROP FUNCTION IF EXISTS `ip4str_to_int`;;
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,v_cnt INT unsigned DEFAULT 0;
SET v_rem = fp_ipstr;
WHILE v_cnt < 4 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);
ELSE
SET v_part = v_rem;
SET v_rem = '';
END IF;
SET v_ret = ( v_ret << 8 ) + v_part;
SET v_cnt = v_cnt + 1;
END WHILE;
RETURN v_ret;
END ;;
DROP FUNCTION IF EXISTS `ip6str_to_int`;;
CREATE FUNCTION `ip6str_to_int`(
fp_ipstr VARCHAR(255)
) RETURNS BIGINT(20) unsigned
NO SQL
DETERMINISTIC
BEGIN
DECLARE v_part,v_rem VARCHAR(40) DEFAULT "";
DECLARE v_pos,v_cnt INT unsigned DEFAULT 0;
DECLARE v_ret BIGINT unsigned DEFAULT 0;
SET v_pos = LOCATE( '::', fp_ipstr );
IF v_pos > 0 THEN
SET v_rem = SUBSTRING( fp_ipstr, 1, v_pos - 1 );
ELSE
SET v_rem = fp_ipstr;
END IF;
WHILE v_cnt < 4 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);
ELSE
SET v_part = v_rem;
SET v_rem = '';
END IF;
SET v_ret = ( v_ret << 16 ) + IF(LENGTH(v_part),conv(v_part,16, 10),0);
SET v_cnt = v_cnt + 1;
END WHILE;
RETURN v_ret;
END ;;
DROP FUNCTION IF EXISTS `ip6words`;;
CREATE FUNCTION `ip6words`(
fp_ipstr VARCHAR(40),
fp_reverse TINYINT
) RETURNS INT(3) unsigned
NO SQL
DETERMINISTIC
BEGIN
DECLARE v_pos INT unsigned DEFAULT 1;
DECLARE v_cnt INT unsigned DEFAULT 0;
DECLARE v_str VARCHAR(255) DEFAULT "";
IF fp_reverse THEN
SET v_str = REVERSE( fp_ipstr );
ELSE
SET v_str = fp_ipstr;
END IF;
search: REPEAT
SET v_pos = LOCATE( ':', v_str, v_pos);
IF v_pos THEN
SET v_cnt = v_cnt + 1;
SET v_pos = v_pos + 1;
IF LOCATE( ':', v_str, v_pos ) = v_pos THEN
IF v_pos = 2 THEN
SET v_cnt = v_cnt - 1;
END IF;
LEAVE search;
END IF;
ELSE
SET v_cnt = v_cnt + IF(LENGTH(v_str),1,0);
END IF;
UNTIL NOT v_pos END REPEAT;
RETURN v_cnt;
END ;;
DROP FUNCTION IF EXISTS `ip6wordval`;;
CREATE FUNCTION `ip6wordval`(
fp_ipstr VARCHAR(40),
fp_index TINYINT
) RETURNS INT(5) unsigned
NO SQL
DETERMINISTIC
BEGIN
DECLARE v_pos,v_lpos INT unsigned DEFAULT 1;
DECLARE v_cntf,v_cntl,v_cnt,v_rdx INT unsigned DEFAULT 0;
DECLARE v_str,v_rstr VARCHAR(255) DEFAULT "";
SET v_cntf = ip6words( fp_ipstr, 0 );
SET v_cntl = ip6words( fp_ipstr, 1 );
SET v_rdx = 9 - fp_index;
IF fp_index <= v_cntf THEN
search: WHILE v_cnt < fp_index DO
SET v_lpos = v_pos;
SET v_pos = LOCATE( ':', fp_ipstr, v_pos);
SET v_cnt = v_cnt + 1;
IF v_cnt = fp_index THEN
SET v_str = SUBSTRING(fp_ipstr, v_lpos,if(v_pos,v_pos-v_lpos,LENGTH(fp_ipstr)-v_lpos));
LEAVE search;
END IF;
SET v_pos = v_pos + 1;
END WHILE;
ELSEIF v_rdx <= v_cntl THEN
SET v_rstr = REVERSE(fp_ipstr);
search2: WHILE v_cnt < v_rdx DO
SET v_lpos = v_pos;
SET v_pos = LOCATE( ':', v_rstr, v_pos);
SET v_cnt = v_cnt + 1;
IF v_cnt = v_rdx THEN
SET v_str = REVERSE(SUBSTRING(v_rstr, v_lpos,if(v_pos,v_pos-v_lpos,LENGTH(v_rstr)-v_lpos)));
LEAVE search2;
END IF;
SET v_pos = v_pos + 1;
END WHILE;
END IF;
RETURN IF(LENGTH(v_str),CONV(v_str,16,10),0);
END ;;
DELIMITER ;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment