1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
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;
|
