#!/bin/bash  
#===============================================================================
#
#          FILE: generate_geoip_sql.sh
# 
#         USAGE: ./generate_geoip_sql.sh
# 
#   DESCRIPTION: Generates a optimized sql dump with geoipdata from maxmind.com
#
#         USAGE: import sql into a database
#
#  REQUIREMENTS: mysql database rights to create databases and LOAD DATA LOCAL INFILE
#
#       EXAMPLE: 
#                 SELECT country_code,country_name
#                 FROM   geo_ip
#                 WHERE  MBRCONTAINS(ip_poly, POINTFROMWKB(POINT(INET_ATON('1.2.3.4'), 0)))
#
#                 SELECT country_code,country_name
#                 FROM   geo_ip
#                 WHERE  INET_ATON('1.2.3.4') 
#                 BETWEEN ip_from AND ip_to;
#
#        AUTHOR:  Ryan Schulze (rs), ryan@dopefish.de
#
#===============================================================================

set -o nounset                              # Treat unset variables as an error

CSVFile="GeoIPCountryWhois.csv"

if [[ -e ${CSVFile} ]]
	then
	echo -en "\n${CSVFile} already exists, download a newer version? [Y/n]: " 
	read answer
	if [[ "$(echo $answer|tr "N" "n")" != "n" ]]
	then
		rm ${CSVFile}
	fi
fi

if [[ ! -e ${CSVFile} ]]
then
	tmpfile=$(mktemp)
	wget -O ${tmpfile} http://geolite.maxmind.com/download/geoip/database/GeoIPCountryCSV.zip
	unzip ${tmpfile} && rm ${tmpfile}
fi

echo -e "\ngenerating geoip.sql"

mysql -e "create database if not exists temp"
mysql -D temp -e "drop table if exists geo_ip"

echo '
CREATE TABLE geo_ip
(
  id           INT UNSIGNED  NOT NULL auto_increment,
  ip_poly      POLYGON       NOT NULL,
  ip_from      INT UNSIGNED  NOT NULL,
  ip_to        INT UNSIGNED  NOT NULL,
  country_code CHAR(2)       NOT NULL,
  country_name CHAR(50)      NOT NULL,
  PRIMARY KEY (id),
  SPATIAL INDEX (ip_poly)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
LOAD DATA LOCAL INFILE "GeoIPCountryWhois.csv"
INTO TABLE geo_ip
FIELDS
  TERMINATED BY ","
  ENCLOSED BY "\""
LINES
  TERMINATED BY "\n"
(
  @ip_from_string, @ip_to_string,
  @ip_from, @ip_to,
  @country_code, @country_string
)
SET
  id      := NULL,
  ip_from := @ip_from,
  ip_to   := @ip_to,
  ip_poly := GEOMFROMWKB(POLYGON(LINESTRING(
    /* clockwise, 4 points and back to 0 */
    POINT(@ip_from, -1), /* 0, top left */
    POINT(@ip_to,   -1), /* 1, top right */
    POINT(@ip_to,    1), /* 2, bottom right */
    POINT(@ip_from,  1), /* 3, bottom left */
    POINT(@ip_from, -1)  /* 0, back to start */
  ))),
  country_code := @country_code,
  country_name := @country_string
;
' | mysql --local-infile=1 -D temp

mysqldump --opt temp geo_ip > geoip.sql

echo -e "\ndone generating geoip.sql"