country lookup

3 files changed, 112 insertions, 7 deletions

diff --git a/Changelog b/Changelog
index d893f6a..e48d586 100755
--- a/Changelog
+++ b/Changelog
@@ -1,5 +1,9 @@
 Changelog:
 
+04-10-2021: country lookup
+    * backend Perl script for ip to country lookup (external script)
+    * update readme with ip to country and perl/cpan modules
+
 04-10-2021: style rotation through setting
     * enable/disable style rotation with the setting
 
diff --git a/README.md b/README.md
index 8b2159d..314e445 100755
--- a/README.md
+++ b/README.md
@@ -13,10 +13,13 @@ This repository contains software for a web interface to display information obt
 * Apache/httpd
 * Perl 5.10 or above
 * The following CPAN modules:
-    * DBI
-    * DBD::SQLite
-    * TUWF
-    * Image::Size (optional for style generation)
+    * `DBI`
+    * `DBD::SQLite`
+    * `TUWF`
+    * `JSON`
+    * `Image::Size` (optional for style generation)
+    * `AnyEvent` (optional for IP to Country lookup)
+    * `LWP::Simple` (optional for IP to Country lookup)
 
 ## INSTALL
 This repository consists of Perl modules and is run by a http deamon. First, the MasterServer-Qt5 repository should be installed and configured in order to run this web interface. This web interface requires access to the database generated and updated by MasterServer-Qt5.
@@ -30,7 +33,7 @@ The 333networks masterserver interface comes with options. These options are fou
   email     => 'master@333networks.com',
 ```
 
-Configure the path to the database that the masterserver uses. The location of this database is found in the documentation of `MasterServer-Qt5`.
+Configure the path to the database that the masterserver uses. The location of this database is found in the documentation of `MasterServer-Qt5`. Depending on your configuration, you may have to apply user permissions with `chmod 644 masterserver.db` before the website can interact with it.
 ```
   # database connection
   db_login => ["dbi:SQLite:dbname=/path/to/your/data/masterserver.db",'',''],
@@ -86,6 +89,9 @@ Some parameters can be colors, textures or both. Fields with the (texture) indic
 
 To compile a skin, run the command `./skingen.pl SKINNAME` from the `util` directory, where skinname is the lowercase folder name of your skin. The generated stylesheet can now be used in your webinterface config file under the `style => skinname` option. Stylesheets may be edited manually, but running the command again will overwrite previous changes without confirmation.
 
+## Optional: IP to Country lookup
+The masterserver does not perform an IP to Country lookup. The `listcountry.pl` Perl script queries an IP every 10 seconds through the <ip-api.com> public Json API and inserts successful queries in the masterserver database.
+
 ## Apache settings
 Update the vhost configuration for the Web Interface to match your repository folder path. You may be required to enable modules such as `mod_rewrite` and `fcgi`.
 
@@ -117,8 +123,7 @@ CustomLog /path/to/WebInterface-Perl-MS-Qt5/log/Access.log combined
 
 ## KNOWN ISSUES
 There are a few known issues that will be resolved in future versions. The following issues are listed and do not need to be reported.
-* No additional styling available for third parties. Will come when the rest of the website is stable.
-* Country name/flag is always "Earth". There is a third party script that updates these in the database, which may or may not be released in the future.
+* no issues tracked!
 
 ## COPYING
 See COPYING file
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;