4 GEOIPDB=http://geolite.maxmind.com/download/geoip/database/GeoIPCountryCSV.zip
10 echo Usage: ${0##*/} [options]
13 echo " " -h display this help message
14 echo " " -s be more silent \(show only warnings\)
19 args=`getopt -o sh -l silent,help -- "$@"`
23 -h|--help) usage; exit 0 ;;
24 -s|--silent) SILENT="1"; shift ;;
26 *) echo "Invalid option: $1"; exit 1 ;;
30 if [ ${#SILENT} -ne 0 ]; then
31 # we won't see all the index creation notices when creating tables
32 export PGOPTIONS='--client_min_messages=warning'
35 psql --set "ON_ERROR_STOP=1" -f - <<EOF
38 DROP TABLE IF EXISTS geoip CASCADE;
40 id SERIAL PRIMARY KEY,
46 DROP FUNCTION IF EXISTS inet_to_bigint(INET);
47 CREATE OR REPLACE FUNCTION inet_to_bigint(ip INET)
57 sp := regexp_split_to_array(ip::text, E'\\\\.');
61 z := substring(sp[4], 0, strpos(sp[4], '/'));
62 return 16777216*w::bigint + 65536*x::bigint + 256*y::bigint + z::bigint;
64 \$\$ LANGUAGE plpgsql IMMUTABLE;
71 trap "rm -rf $DIR;" EXIT
74 GEOIPCVS=$(zipinfo -1 ${GEOIPDB##*/} | grep '\.csv$')
75 if [ $(echo $GEOIPCVS | wc -w) -lt "1" ]; then
76 echo There is no csv file in the archive. Canceling
77 elif [ $(echo $GEOIPCVS | wc -w) -gt "1" ]; then
78 echo There is more than one csv file in the archive. Which one should I pick ?
80 unzip ${GEOIPDB##*/} $GEOIPCVS
82 # insert all values from csv to database
83 sed -e 's/"\([^"]\+\)","\([^"]\+\)","\([^"]\+\)","\([^"]\+\)","\([^"]\+\)","\([^"]\+\)"/INSERT INTO geoip (begin_ip, end_ip, country) VALUES ('\''\3'\'','\''\4'\'','\''\5'\'');/' $GEOIPCVS | psql --set "ON_ERROR_STOP=1" -f -
84 psql --set "ON_ERROR_STOP=1" -c "VACUUM ANALYZE geoip;"