country lookup

1 files changed, 96 insertions, 0 deletions

diff --git a/util/listcountry.pl b/util/listcountry.pl
new file mode 100755
index 0000000..3196fe1
--- /dev/null
+++ b/util/listcountry.pl
@@ -0,0 +1,96 @@
+#!/usr/bin/perl
+
+use strict;
+use warnings;
+use AnyEvent;
+use DBI;
+use LWP::Simple;
+use JSON;
+use Data::Dumper 'Dumper';
+$|++;
+
+# intro
+print "Looking up IPs to countries using ip-api.com.\n";
+
+# open db
+my $dbpath = "dbi:SQLite:dbname=../data/masterserver.db";
+my $dbh = DBI->connect($dbpath, '', '') or die "Cannot connect: $DBI::errstr\n";
+
+# loop counter
+my $index = -1;
+
+# loop
+my $cv = AnyEvent->condvar;
+
+# send out heartbeats
+my $timer = AnyEvent->timer (
+    after     => 0, # seconds
+    interval  => 10, # seconds
+    cb        => sub 
+    {
+        my $next = $dbh->selectall_arrayref(
+            "SELECT id, ip FROM serverlist ".
+            "LEFT JOIN serverinfo ON serverlist.id = serverinfo.sid ".
+            "WHERE id > ? ".
+            "AND sid > 0 ".
+            "AND country IS NULL ".
+            "ORDER BY id ASC ".
+            "LIMIT 1",
+            undef,
+            $index
+        )->[0];
+
+        # get country code
+        if ( $next->[0] && $next->[0] >= 0)
+        {
+            # parse
+            my $id   = $next->[0];
+            my $addr = $next->[1];
+            
+            if ($addr eq "::1")
+            {
+                #print "skip ::1\n";
+                $index = $id;
+                return;
+            }
+            
+            # get country data from API (throttle 45 requests per min)
+            my $lwp = get("http://ip-api.com/json/$addr");
+            my $data = decode_json $lwp;
+            
+            # insert in database
+            if ( $data->{countryCode} )
+            {
+                $dbh->do(
+                    "UPDATE serverinfo ".
+                    "SET country = ? ".
+                    "WHERE sid IN (".
+                    "SELECT id FROM serverlist ".
+                    "WHERE ip = ?)",
+                    undef, $data->{countryCode}, $addr
+                );
+                
+                print "$id\t$data->{countryCode}\t $addr\t$data->{query}\n";
+            }
+            else 
+            {
+                print "skip $id\t$addr\n";
+                print Dumper $data;
+            }
+            
+            # update index
+            $index = $id;
+        }
+        else
+        {
+            # cycle complete, restart to re-read inserted serverinfo rows
+            $index = -1;
+        }
+    },
+);
+
+
+
+$cv->recv;
+
+1;