CREATE TABLE geoip
(
  netblock cidr NOT NULL,
  country_code text NOT NULL,
  country_name text NOT NULL,
  CONSTRAINT geoip_pk PRIMARY KEY (netblock)
  USING INDEX TABLESPACE "NAMEOFINDEXTABLESPACE"
)
WITH (
  OIDS=FALSE
)
TABLESPACE "NAMEOFDATATABLESPACE";
ALTER TABLE geoip OWNER TO "mydatabaseuser";

##</code><code>##

#!/usr/bin/env bash

cd /home/myuser/src/geoip
wget http://geolite.maxmind.com/download/geoip/database/GeoIPCountryCSV.zip
wget http://geolite.maxmind.com/download/geoip/database/GeoIPv6.csv.gz

gunzip GeoIPv6.csv.gz
unzip GeoIPCountryCSV.zip

rm GeoIPCountryCSV.zip
perl inserttodb.pl

rm *.csv

##</code><code>##

#/usr/bin/env perl

use DBI;

use Net::CIDR;

my $dbh = DBI->connect("dbi:Pg:dbname=Cables_DB;host=localhost", 'mydatabaseuser', 'secretpassword', {AutoCommit => 0, RaiseError => 0})
        or die("can't connect to DB");

$dbh->do("TRUNCATE geoip");
my $insth = $dbh->prepare_cached("INSERT INTO geoip (netblock, country_code, country_name) VALUES (?,?,?)")
        or die($dbh->errstr);

foreach my $file (qw[GeoIPCountryWhois.csv GeoIPv6.csv]) {
    print "Running on file $file...\n";
    open(my $ifh, '<', $file) or die("Can't open $file");
    my $linecount = 0;

    while((my $line = <$ifh>)) {
        $linecount++;
        chomp $line;
        my ($firstip, $lastip, undef, undef, $countrycode, $countryname) = split/\"\,\ ?\"/, $line;
        $firstip = sanitize($firstip);
        $lastip = sanitize($lastip);
        $countrycode = sanitize($countrycode);
        $countryname = sanitize($countryname);

        my @cidr = Net::CIDR::range2cidr($firstip . '-' . $lastip);

        foreach my $subcidr (@cidr) {
            $insth->execute($subcidr, $countrycode, $countryname) or die("Fail on $linecount $line: " . $dbh->errstr);
        }

    }
    close $ifh;
}

$dbh->commit;

sub sanitize {
    my ($val) = @_;

    $val =~ s/^\"//;
    $val =~ s/\"$//;

    return $val;
}

##</code><code>##

20 1 * * 1 /bin/bash /home/myuser/src/geoip/updategeoip.sh

##</code><code>##


my $geoip_country = '';
my $geosth = $dbh->prepare("SELECT country_code FROM geoip WHERE ? << netblock LIMIT 1")
        or croak($dbh->errstr);
if(!$geosth->execute($host)) {
    $dbh->rollback; # Not a big problem, GEOIP is just for information anyway
} else {
    my $line = $geosth->fetchrow_hashref;
    if(defined($line->{country_code})) {
        $geoip_country = $line->{country_code};
    } else {
        $geoip_country = '??';
    }
    $dbh->rollback;
}