Restore SQLite support

15 files changed, 884 insertions, 89 deletions

diff --git a/lib/MasterServer/Database/dbAddServers.pm b/lib/MasterServer/Database/Pg/dbAddServers.pm
index 4898316..33785da 100755
--- a/lib/MasterServer/Database/dbAddServers.pm
+++ b/lib/MasterServer/Database/Pg/dbAddServers.pm
@@ -1,5 +1,5 @@
 
-package MasterServer::Database::dbAddServers;
+package MasterServer::Database::Pg::dbAddServers;
 
 use strict;
 use warnings;
diff --git a/lib/MasterServer/Database/dbCiphers.pm b/lib/MasterServer/Database/Pg/dbCiphers.pm
index e62552d..e2a5784 100755
--- a/lib/MasterServer/Database/dbCiphers.pm
+++ b/lib/MasterServer/Database/Pg/dbCiphers.pm
@@ -1,5 +1,5 @@
 
-package MasterServer::Database::dbCiphers;
+package MasterServer::Database::Pg::dbCiphers;
 
 use strict;
 use warnings;
diff --git a/lib/MasterServer/Database/Pg/dbCore.pm b/lib/MasterServer/Database/Pg/dbCore.pm
new file mode 100755
index 0000000..90899f7
--- /dev/null
+++ b/lib/MasterServer/Database/Pg/dbCore.pm
@@ -0,0 +1,50 @@
+
+package MasterServer::Database::Pg::dbCore;
+
+use strict;
+use warnings;
+use Exporter 'import';
+
+our @EXPORT = qw| database_login |;
+
+################################################################################
+## login to the database with credentials provided in the config file.
+## returns dbh object or quits application on error.
+##
+## Recommended database types: Postgresql, MySQL or SQLite. Warranty void if
+## other database types are used. Use at your own risk.
+################################################################################
+sub database_login {
+  my $self = shift;
+
+  # get db info
+  my @db_type = split(':', $self->{dblogin}->[0]);
+    
+  # create the dbi object
+  my $dbh = DBI->connect(@{$self->{dblogin}}, {PrintError => 1});
+  
+  # verify that the database connected
+  if (defined $dbh) {
+  
+    # log the event
+    $self->log("info","Connected to the $db_type[1] database.");
+    
+    # turn on error printing
+    $dbh->{printerror} = 1;
+    
+    # return the dbi object for further use
+    return $dbh;
+  }
+  else {
+    # fatal error
+    $self->log("fatal", "$DBI::errstr!");
+    
+    # end program
+    $self->halt();
+  }
+  
+  # in case of any other error, return undef.
+  return undef;
+}
+
+1;
diff --git a/lib/MasterServer/Database/dbGetServers.pm b/lib/MasterServer/Database/Pg/dbGetServers.pm
index 5069462..a33582a 100755
--- a/lib/MasterServer/Database/dbGetServers.pm
+++ b/lib/MasterServer/Database/Pg/dbGetServers.pm
@@ -1,4 +1,4 @@
-package MasterServer::Database::dbGetServers;
+package MasterServer::Database::Pg::dbGetServers;
 
 use strict;
 use warnings;
diff --git a/lib/MasterServer/Database/dbMaintenance.pm b/lib/MasterServer/Database/Pg/dbMaintenance.pm
index 41ea93a..90e4d34 100755
--- a/lib/MasterServer/Database/dbMaintenance.pm
+++ b/lib/MasterServer/Database/Pg/dbMaintenance.pm
@@ -1,4 +1,4 @@
-package MasterServer::Database::dbMaintenance;
+package MasterServer::Database::Pg::dbMaintenance;
 
 use strict;
 use warnings;
diff --git a/lib/MasterServer/Database/dbStats.pm b/lib/MasterServer/Database/Pg/dbStats.pm
index 70962dc..6e92ab6 100755
--- a/lib/MasterServer/Database/dbStats.pm
+++ b/lib/MasterServer/Database/Pg/dbStats.pm
@@ -1,4 +1,4 @@
-package MasterServer::Database::dbStats;
+package MasterServer::Database::Pg::dbStats;
 
 use strict;
 use warnings;
diff --git a/lib/MasterServer/Database/dbUTServerInfo.pm b/lib/MasterServer/Database/Pg/dbUTServerInfo.pm
index 9352e38..12ba05f 100755
--- a/lib/MasterServer/Database/dbUTServerInfo.pm
+++ b/lib/MasterServer/Database/Pg/dbUTServerInfo.pm
@@ -1,5 +1,5 @@
 
-package MasterServer::Database::dbUTServerInfo;
+package MasterServer::Database::Pg::dbUTServerInfo;
 
 use strict;
 use warnings;
diff --git a/lib/MasterServer/Database/SQLite/dbAddServers.pm b/lib/MasterServer/Database/SQLite/dbAddServers.pm
new file mode 100755
index 0000000..9fccead
--- /dev/null
+++ b/lib/MasterServer/Database/SQLite/dbAddServers.pm
@@ -0,0 +1,162 @@
+
+package MasterServer::Database::SQLite::dbAddServers;
+
+use strict;
+use warnings;
+use MasterServer::Core::Util 'sqlprint';
+use Exporter 'import';
+
+our @EXPORT = qw| add_server_new
+                  add_server_list
+               update_server_list 
+                       syncer_add |;
+
+################################################################################
+## Update an existing address or add a new address to the pending list.
+## opts: direct beacon, set update, game
+################################################################################
+sub add_server_new {
+  my $self = shift;
+  my %o = (
+   updated  => time,
+   @_);
+
+  # try updating it in serverlist
+  my %H = (
+    $o{direct}   ? (  'b333ms = CAST(? AS BOOLEAN)' => $o{direct})      : (),
+    $o{updated}  ? ( 'updated = datetime(?, \'unixepoch\')'    => $o{updated})     : (),
+    $o{beacon}   ? (  'beacon = datetime(?, \'unixepoch\')'    => $o{beacon})      : (),
+    $o{gamename} ? ('gamename = ?'                  => lc $o{gamename}) : (),
+  );
+
+  my($q, @p) = sqlprint("UPDATE serverlist !H 
+    WHERE ip = ? AND port = ?", \%H, $o{ip}, $o{heartbeat});
+
+  my $n = $self->{dbh}->do($q, undef, @p);
+  
+  # if serverlist was updated
+  return 0 if ($n > 0);
+  
+  
+  # try updating it in pending
+  %H = (
+    $o{added}      ? (     'added = ?' => $o{added})       : (),
+    $o{secure}     ? (    'secure = ?' => $o{secure})      : (),
+    $o{gamename}   ? (  'gamename = ?' => lc $o{gamename}) : (),
+    $o{beaconport} ? ('beaconport = ?' => $o{beaconport})  : (),
+  );
+
+  ($q, @p) = sqlprint("UPDATE pending !H 
+    WHERE ip = ? AND heartbeat = ?", \%H, $o{ip}, $o{heartbeat});
+
+  # exec query
+  $n = $self->{dbh}->do($q, undef, @p);
+
+  # if beacon was updated
+  return 1 if ($n > 0);
+  
+  # if not found at all, add to pending
+  $n = $self->{dbh}->do(
+     "INSERT INTO pending (
+          ip, 
+          beaconport, 
+          heartbeat, 
+          gamename, 
+          secure) 
+      SELECT ?, ?, ?, ?, ?",
+      undef, $o{ip}, $o{beaconport}, $o{heartbeat}, lc $o{gamename}, $o{secure});
+      
+   # it was added to pending
+   return 2 if ($n > 0);
+}
+
+################################################################################
+## Update the server info in the serverlist
+################################################################################
+sub update_server_list {
+  my $self = shift;
+  my %o = (
+    updated  => time,
+    @_);
+
+  # try updating it in serverlist
+  my %H = (
+    $o{gamename} ? ('gamename = ?' => lc $o{gamename}) : (),
+    $o{gamever}  ? ( 'gamever = ?' => $o{gamever})     : (),
+    $o{hostname} ? ('hostname = ?' => $o{hostname})    : (),
+    $o{hostport} ? ('hostport = ?' => $o{hostport})    : (),
+    $o{updated}  ? ( 'updated = datetime(?, \'unixepoch\')' => $o{updated})     : (),
+  );
+
+  my($q, @p) = sqlprint("UPDATE serverlist !H 
+    WHERE ip = ? AND port = ?", \%H, $o{ip}, $o{port});
+
+  return $self->{dbh}->do($q, undef, @p);
+}
+
+################################################################################
+## beacon was verified or otherwise accepted and will now be added to the 
+## serverlist.
+################################################################################
+sub add_server_list {
+  my $self = shift;
+  my %o = @_;
+
+  # insert basic data
+  return $self->{dbh}->do("INSERT INTO serverlist (ip, port, gamename, country) 
+                           SELECT ?, ?, ?, ?", undef, 
+                           $o{ip}, $o{port}, lc $o{gamename}, $self->ip2country($o{ip}));
+}
+
+################################################################################
+## add new addresses to the pending list, but do not update timestamps. masters
+## that sync with each other would otherwise update the timestamp for a server
+## which is no longer online.
+################################################################################
+sub syncer_add {
+  my ($self, $ip, $port, $gamename, $secure) = @_;
+
+  # if address is in the list AND up to date, 
+  # acknowledge its existance but don't do anything with it
+  my $u = $self->{dbh}->do(
+     "SELECT * FROM serverlist 
+      WHERE ip = ? 
+      AND port = ?
+      AND updated > datetime(?, 'unixepoch')",
+      undef, $ip, $port, time-7200);
+
+  # if found, return 0
+  return 0 if ($u > 0);
+
+  # if it is already in the pending list, update it with a new challenge
+  $u = $self->{dbh}->do(
+     "UPDATE pending 
+      SET secure = ?
+      WHERE ip = ? 
+      AND heartbeat = ?",
+      undef, $secure, $ip, $port);  
+
+  # notify
+  $self->log("update","$ip:$port was updated by syncer") if ($u > 0);
+  
+  # return 1 if found
+  return 1 if ($u > 0);
+  
+  # if not found or out of date, add it to pending to be checked again
+  $u = $self->{dbh}->do(
+     "INSERT INTO pending (ip, heartbeat, gamename, secure) 
+      SELECT ?, ?, ?, ?",
+      undef, $ip, $port, lc $gamename, $secure);
+                            
+   # notify
+   $self->log("add","beacon: $ip:$port was added for $gamename after sync") if ($u > 0);
+   
+   # return 2 if added new
+   return 2 if ($u > 0);
+   
+  # or else report error
+  $self->log("error", "an error occurred adding $ip:$port after sync");
+  return -1;
+}
+
+1;
diff --git a/lib/MasterServer/Database/SQLite/dbCiphers.pm b/lib/MasterServer/Database/SQLite/dbCiphers.pm
new file mode 100755
index 0000000..c98eb05
--- /dev/null
+++ b/lib/MasterServer/Database/SQLite/dbCiphers.pm
@@ -0,0 +1,57 @@
+
+package MasterServer::Database::SQLite::dbCiphers;
+
+use strict;
+use warnings;
+use Exporter 'import';
+
+our @EXPORT = qw| clear_ciphers 
+                  insert_cipher 
+                  get_game_props |;
+
+################################################################################
+## Clear all existing ciphers from the database
+################################################################################
+sub clear_ciphers {
+  my $self = shift;
+
+  # delete ALL entries
+  my $u = $self->{dbh}->do("DELETE FROM games");
+}
+
+################################################################################
+## Insert the list of supported games and their ciphers / default ports / 
+## descriptions included from the data/supportedgames.pl file.
+################################################################################
+sub insert_cipher {
+  my ($self, %opt) = @_;
+  
+  # insert a single cipher/key combo
+  my $u = $self->{dbh}->do(
+     "INSERT INTO games (
+          gamename, 
+          cipher, 
+          description, 
+          default_qport) 
+      VALUES(?, ?, ?, ?)", undef, 
+      lc $opt{gamename}, $opt{cipher}, $opt{description}, $opt{default_qport});
+  return 1 if ($u and $u > 0);
+  
+  # or else report error
+  $self->log("error", "An error occurred adding a cipher for $opt{gamename}");
+}
+
+################################################################################
+## get the cipher, description and default port that goes with given gamename
+################################################################################
+sub get_game_props {
+  my ($self, $gn) = @_;
+  
+  # get cipher from db if gamename exists
+  return $self->{dbh}->selectall_arrayref(
+    'SELECT * FROM games WHERE gamename = ?', 
+    {Slice=>{}}, 
+    lc $gn)->[0];
+}
+
+1;
diff --git a/lib/MasterServer/Database/SQLite/dbCore.pm b/lib/MasterServer/Database/SQLite/dbCore.pm
new file mode 100755
index 0000000..f58d535
--- /dev/null
+++ b/lib/MasterServer/Database/SQLite/dbCore.pm
@@ -0,0 +1,75 @@
+
+package MasterServer::Database::SQLite::dbCore;
+
+use strict;
+use warnings;
+use Exporter 'import';
+
+our @EXPORT = qw| database_login |;
+
+################################################################################
+## login to the database with credentials provided in the config file.
+## returns dbh object or quits application on error.
+##
+## Recommended database types: Postgresql, MySQL or SQLite. Warranty void if
+## other database types are used. Use at your own risk.
+################################################################################
+sub database_login {
+  my $self = shift;
+
+  # get db info
+  my @db_type = split(':', $self->{dblogin}->[0]);
+  
+  # check if database file exists
+  my $db_file = [split(':', $self->{dblogin}->[0])]->[2];
+     $db_file =~ s/dbname=//i;
+
+  unless (-e $db_file) {
+    # fatal error
+    $self->log("fatal", "Database file $db_file does not exist!");
+    
+    # end program
+    $self->halt();
+  }
+  
+  # inform what DB we try to load
+  # $self->log("info","Database: $db_type[1]");
+    
+  # create the dbi object
+  my $dbh = DBI->connect(@{$self->{dblogin}}, {PrintError => 1});
+  
+  # verify that the database connected
+  if (defined $dbh) {
+  
+    # log the event
+    $self->log("info","Connected to the $db_type[1] database.");
+    
+    # turn on error printing
+    $dbh->{printerror} = 1;
+    
+    # synchronous read/writing to the SQLite file OFF. That means: when the script 
+    # shuts down unexpectedly, i.e. because of power failure or a crash, changes 
+    # to the database are NOT SAVED. However, if this setting is not turned OFF, 
+    # it takes too long to write to the database, which means that new beacons, 
+    # requests and servers cannot be processed. You don't have a choice, really..
+    $dbh->do("PRAGMA synchronous = OFF");
+    
+    # allow the use of foreign keys (referencing)
+    $dbh->do("PRAGMA foreign_keys = ON");
+    
+    # return the dbi object for further use
+    return $dbh;
+  }
+  else {
+    # fatal error
+    $self->log("fatal", "$DBI::errstr!");
+    
+    # end program
+    $self->halt();
+  }
+  
+  # in case of any other error, return undef.
+  return undef;
+}
+
+1;
diff --git a/lib/MasterServer/Database/SQLite/dbGetServers.pm b/lib/MasterServer/Database/SQLite/dbGetServers.pm
new file mode 100755
index 0000000..1884601
--- /dev/null
+++ b/lib/MasterServer/Database/SQLite/dbGetServers.pm
@@ -0,0 +1,139 @@
+package MasterServer::Database::SQLite::dbGetServers;
+
+use strict;
+use warnings;
+use Exporter 'import';
+
+our @EXPORT = qw| get_server 
+                  get_pending 
+                  get_gamenames |;
+
+################################################################################
+## get server details for one or multiple servers
+## opts: limit, see $order
+################################################################################
+sub get_server {
+  my $s = shift;
+  my %o = (
+    sort => '', 
+    @_
+  );
+  
+  my %where = (
+    $o{next_id}     ? (         'id > ?' => $o{next_id})      : (),
+    $o{id}          ? (         'id = ?' => $o{id})           : (),
+    $o{ip}          ? (         'ip = ?' => $o{ip})           : (),
+    $o{port}        ? (       'port = ?' => $o{port})         : (),
+    $o{gamename}    ? (   'gamename = ?' => lc $o{gamename})  : (),
+    $o{gamever}     ? (    'gamever = ?' => $o{gamever})      : (),
+    $o{hostname}    ? (   'hostname = ?' => $o{hostname})     : (),
+    $o{hostport}    ? (   'hostport = ?' => $o{hostport})     : (),
+    $o{country}     ? (    'country = ?' => $o{country})      : (),
+    $o{b333ms}      ? (     'b333ms = ?' => $o{b333ms})       : (),
+    $o{blacklisted} ? ('blacklisted = ?' => $o{blacklisted})  : (),
+    $o{added}       ? (  'added < datetime(?, \'unixepoch\')' => (time-$o{added}))   : (),
+    $o{beacon}      ? ( 'beacon > datetime(?, \'unixepoch\')' => (time-$o{beacon}))  : (),
+    $o{updated}     ? ('updated > datetime(?, \'unixepoch\')' => (time-$o{updated})) : (),
+    $o{before}      ? ('updated < datetime(?, \'unixepoch\')' => (time-$o{before}))  : (),
+  );
+  
+  my @select = ( qw|
+    id 
+    ip 
+    port 
+    gamename 
+    gamever 
+    hostname 
+    hostport 
+    country 
+    b333ms 
+    blacklisted 
+    added 
+    beacon 
+    updated
+  |);
+
+  my $order = sprintf {
+      id          => 'id %s',
+      ip          => 'ip %s',
+      port        => 'port %s',
+      gamename    => 'gamename %s',
+      gamever     => 'gamever %s',
+      hostname    => 'hostname %s',
+      hostport    => 'hostport %s',
+      country     => 'country %s',
+      b333ms      => 'b333ms %s',
+      blacklisted => 'blacklisted %s',
+      added       => 'added %s',
+      beacon      => 'beacon %s',
+      updated     => 'updated %s',
+  }->{ $o{sort}||'id' }, $o{reverse} ? 'DESC' : 'ASC';
+
+  return $s->db_all( q|
+    SELECT !s FROM serverlist
+      !W
+      ORDER BY !s|
+      .($o{limit} ? " LIMIT ?" : ""),
+    join(', ', @select), \%where, $order, ($o{limit} ? $o{limit} : ()),
+  );
+}
+
+################################################################################
+## get server details for one or multiple pending servers
+## opts: limit, next_id, beaconport, heartbeat, gamename, secure, enctype, added
+################################################################################
+sub get_pending {
+  my $s = shift;
+  my %o = (
+    sort => '', 
+    @_
+  );
+  
+  my %where = (
+    $o{next_id}     ? (        'id > ?' => $o{next_id})     : (),
+    $o{id}          ? (        'id = ?' => $o{id})          : (),
+    $o{ip}          ? (        'ip = ?' => $o{ip})          : (),
+    $o{beaconport}  ? ('beaconport = ?' => $o{beaconport})  : (),
+    $o{heartbeat}   ? ( 'heartbeat = ?' => $o{heartbeat})   : (),
+    $o{gamename}    ? (  'gamename = ?' => lc $o{gamename}) : (),
+    $o{secure}      ? (    'secure = ?' => $o{secure})      : (),
+    $o{enctype}     ? (   'enctype = ?' => $o{enctype})     : (),
+    $o{added} ? ('added < datetime(?, \'unixepoch\')' => (time-$o{added})) : (),
+    $o{after} ? ('added > datetime(?, \'unixepoch\')' => (time-$o{after})) : (),
+  );
+  
+  my @select = ( qw| id ip beaconport heartbeat gamename secure enctype added |,);
+  my $order = sprintf {
+      id          => 'id %s',
+      ip          => 'ip %s',
+      beaconport  => 'beaconport %s',
+      heartbeat   => 'heartbeat %s',
+      gamename    => 'gamename %s',
+      secure      => 'secure %s',
+      enctype     => 'enctype %s',
+      added       => 'added %s',
+  }->{ $o{sort}||'id' }, $o{reverse} ? 'DESC' : 'ASC';
+
+  return $s->db_all( q|
+    SELECT !s FROM pending
+      !W
+      ORDER BY !s|
+      .($o{limit} ? " LIMIT ?" : ""),
+    join(', ', @select), \%where, $order, ($o{limit} ? $o{limit} : ()),
+  );
+}
+
+################################################################################
+## get a list of distinct gamenames currently in the database. it does not 
+## matter whether they are recent or old, as long as the game is currently in
+## the database.
+################################################################################
+sub get_gamenames {
+  my $self = shift;
+
+  return $self->{dbh}->selectall_arrayref(
+     "SELECT distinct gamename 
+      FROM serverlist");
+}
+
+1;
diff --git a/lib/MasterServer/Database/SQLite/dbMaintenance.pm b/lib/MasterServer/Database/SQLite/dbMaintenance.pm
new file mode 100755
index 0000000..06a8db4
--- /dev/null
+++ b/lib/MasterServer/Database/SQLite/dbMaintenance.pm
@@ -0,0 +1,40 @@
+package MasterServer::Database::SQLite::dbMaintenance;
+
+use strict;
+use warnings;
+use Exporter 'import';
+
+our @EXPORT = qw| delete_old_pending 
+                  remove_pending |;
+
+################################################################################
+## delete unresponsive servers from the pending list
+## where the server is unresponsive for more than 1 hour
+################################################################################
+sub delete_old_pending {
+  my ($self) = shift;
+
+  # remove servers
+  my $u = $self->{dbh}->do(
+     "DELETE FROM pending 
+      WHERE added < datetime(?, 'unixepoch')", undef, time-3600);
+  
+  # notify 
+  $self->log("delete", "Removed $u entries from pending.") if ($u > 0);
+}
+
+################################################################################
+## Remove an entry from the pending list. Returns 0 if removed or -1 in case
+## of error(s).
+################################################################################
+sub remove_pending {
+  my ($self, $id) = @_;
+  
+  # if address is in list, update the timestamp
+  my $u = $self->{dbh}->do("DELETE FROM pending WHERE id = ?", undef, $id);
+  
+  # notify 
+  $self->log("delete", "removed pending id $id from pending") if ($u > 0);
+}
+
+1;
diff --git a/lib/MasterServer/Database/SQLite/dbStats.pm b/lib/MasterServer/Database/SQLite/dbStats.pm
new file mode 100755
index 0000000..1cf94e6
--- /dev/null
+++ b/lib/MasterServer/Database/SQLite/dbStats.pm
@@ -0,0 +1,117 @@
+package MasterServer::Database::SQLite::dbStats;
+
+use strict;
+use warnings;
+use Exporter 'import';
+
+our @EXPORT = qw| get_gamelist_stats 
+                  write_direct_beacons
+                  write_stat 
+                  write_kfstats |;
+
+################################################################################
+# calculate stats for all individual games
+################################################################################
+sub get_gamelist_stats {
+  my $self = shift;
+
+  return $self->{dbh}->selectall_arrayref(
+     "SELECT DISTINCT gamename AS gamename, 
+            COUNT(NULLIF(b333ms AND updated > datetime(?, 'unixepoch'), 0)) AS numdirect,
+            COUNT(NULLIF(updated > datetime(?, 'unixepoch'), 0)) AS numtotal
+     FROM serverlist 
+     GROUP BY gamename", undef, time-7200, time-7200);
+}
+
+################################################################################
+# Determine from the last beacon whether the server is still uplinking
+# directly to us, or whether it stopped uplinking and is now artificially
+# kept in the database.
+################################################################################
+sub write_direct_beacons {
+  my $self = shift;
+  my $u = $self->{dbh}->do(
+    "UPDATE serverlist 
+     SET b333ms = 0
+     WHERE beacon < datetime(?, 'unixepoch') AND b333ms", 
+     undef, time-3600);
+     
+  # notify
+  $self->log("unset", "Lost $u direct beacons.") if ($u > 0);
+}
+
+################################################################################
+# Write the stats to the games table
+# A stat can not exist without existing gamename. Was inserted by cipher loader.
+################################################################################
+sub write_stat {
+  my ($self, %opt) = @_;
+
+  # if it is already in the pending list, update it with a new challenge
+  my $u = $self->{dbh}->do(
+     "UPDATE games 
+      SET num_uplink = ?,
+          num_total  = ?
+      WHERE gamename = ?",
+      undef, $opt{num_uplink}, $opt{num_total}, lc $opt{gamename});
+      
+  # notify
+  $self->log("update", "Updated stats for $opt{gamename}.") if ($u > 0);
+
+}
+
+################################################################################
+## Write the KFStats to the database
+################################################################################
+sub write_kfstats {
+  my ($self, $h) = @_;
+
+  # check if entry already excists.
+  my $u = $self->{dbh}->selectall_arrayref(
+    "SELECT * FROM kfstats WHERE UTkey = ? ", undef, $h->{UTkey});
+  
+  if ( !defined $u->[0] ) {
+    $u = $self->{dbh}->do(
+      "INSERT INTO kfstats (UTkey, Username) VALUES (?,?)", 
+      undef, $h->{UTkey}, $h->{Username});
+                                   
+    # notify
+    $self->log("kfnew", "New KF Player $h->{Username} added");
+  }
+
+  # update existing information
+  $u = $self->{dbh}->do("UPDATE kfstats SET 
+      Username = ?, 
+      CurrentVeterancy = ?, 
+      TotalKills = ?, 
+      DecaptedKills = ?, 
+      TotalMeleeDamage = ?, 
+      MeleeKills = ?, 
+      PowerWpnKills = ?, 
+      BullpupDamage = ?, 
+      StalkerKills = ?, 
+      TotalWelded = ?, 
+      TotalHealed = ?, 
+      TotalPlaytime =?, 
+      GamesWon = ?, 
+      GamesLost = ?
+   WHERE UTkey = ?", undef,
+      $h->{Username}, 
+      $h->{CurrentVeterancy}, 
+      $h->{TotalKills},
+      $h->{DecaptedKills}, 
+      $h->{TotalMeleeDamage}, 
+      $h->{MeleeKills}, 
+      $h->{PowerWpnKills}, 
+      $h->{BullpupDamage}, 
+      $h->{StalkerKills}, 
+      $h->{TotalWelded}, 
+      $h->{TotalHealed}, 
+      $h->{TotalPlaytime}, 
+      $h->{GamesWon}, 
+      $h->{GamesLost}, 
+      $h->{UTkey}
+  );
+}
+
+1;
diff --git a/lib/MasterServer/Database/SQLite/dbUTServerInfo.pm b/lib/MasterServer/Database/SQLite/dbUTServerInfo.pm
new file mode 100755
index 0000000..f1577b2
--- /dev/null
+++ b/lib/MasterServer/Database/SQLite/dbUTServerInfo.pm
@@ -0,0 +1,238 @@
+
+package MasterServer::Database::SQLite::dbUTServerInfo;
+
+use strict;
+use warnings;
+use MasterServer::Core::Util 'sqlprint';
+use Exporter 'import';
+
+our @EXPORT = qw| get_utserver
+                  add_utserver
+               update_utserver 
+               delete_utplayers
+               insert_utplayer |;
+
+################################################################################
+## get server details for one or multiple UT servers
+## opts: limit, see $order
+################################################################################
+sub get_utserver {
+  my $s = shift;
+  my %o = (
+    sort => '', 
+    @_
+  );
+  
+  my %where = (
+    $o{id}                  ? ('server_id = ?'            => $o{id})                  : (),
+    $o{minnetver}           ? ('minnetver = ?'            => $o{minnetver})           : (),
+    $o{gamever}             ? ('gamever = ?'              => $o{gamever})             : (),
+    $o{location}            ? ('location = ?'             => $o{location})            : (),
+    $o{listenserver}        ? ('listenserver = ?'         => $o{listenserver})        : (),
+    $o{hostport}            ? ('hostport = ?'             => $o{hostport})            : (),
+    $o{hostname}            ? ('hostname = ?'             => $o{hostname})            : (),
+    $o{adminname}           ? ('adminname = ?'            => $o{adminname})           : (),
+    $o{adminemail}          ? ('adminemail = ?'           => $o{adminemail})          : (),
+    $o{password}            ? ('password = ?'             => $o{password})            : (),
+    $o{gametype}            ? ('gametype = ?'             => $o{gametype})            : (),
+    $o{gamestyle}           ? ('gamestyle = ?'            => $o{gamestyle})           : (),
+    $o{changelevels}        ? ('changelevels = ?'         => $o{changelevels})        : (),
+    $o{maptitle}            ? ('maptitle = ?'             => $o{maptitle})            : (),
+    $o{mapname}             ? ('mapname = ?'              => $o{mapname})             : (),
+    $o{numplayers}          ? ('numplayers = ?'           => $o{numplayers})          : (),
+    $o{maxplayers}          ? ('maxplayers = ?'           => $o{maxplayers})          : (),
+    $o{minplayers}          ? ('minplayers = ?'           => $o{minplayers})          : (),
+    $o{botskill}            ? ('botskill = ?'             => $o{botskill})            : (),
+    $o{balanceteams}        ? ('balanceteams = ?'         => $o{balanceteams})        : (),
+    $o{playersbalanceteams} ? ('playersbalanceteams = ?'  => $o{playersbalanceteams}) : (),
+    $o{friendlyfire}        ? ('friendlyfire = ?'         => $o{friendlyfire})        : (),
+    $o{maxteams}            ? ('maxteams = ?'             => $o{maxteams})            : (),
+    $o{timelimit}           ? ('timelimit = ?'            => $o{timelimit})           : (),
+    $o{goalteamscore}       ? ('goalteamscore = ?'        => $o{goalteamscore})       : (),
+    $o{fraglimit}           ? ('fraglimit = ?'            => $o{fraglimit})           : (),
+    $o{mutators}            ? ('hostname ILIKE ?'         => "%$o{mutators}%")        : (),
+    $o{updated}             ? ('updated > datetime(?, \'unixepoch\')'=> (time-$o{updated}))      : (),
+  );
+  
+  my @select = ( qw|
+    server_id
+    minnetver
+    gamever
+    location
+    listenserver
+    hostport
+    hostname
+    adminname
+    adminemail
+    password
+    gametype
+    gamestyle
+    changelevels
+    maptitle
+    mapname
+    numplayers
+    maxplayers
+    minplayers
+    botskill
+    balanceteams
+    playersbalanceteams
+    friendlyfire
+    maxteams
+    timelimit
+    goalteamscore
+    fraglimit
+    mutators
+    updated
+  |);
+
+  my $order = sprintf {
+    server_id     => 'server_id %s',
+    minnetver     => 'minnetver %s',
+    gamever       => 'gamever %s',
+    location      => 'location %s',
+    listenserver  => 'listenserver %s',
+    hostport      => 'hostport %s',
+    hostname      => 'hostname %s',
+    adminname     => 'adminname %s',
+    adminemail    => 'adminemail %s',
+    password      => 'password %s',
+    gametype      => 'gametype %s',
+    gamestyle     => 'gamestyle %s',
+    changelevels  => 'changelevels %s',
+    maptitle      => 'maptitle %s',
+    mapname       => 'mapname %s',
+    numplayers    => 'numplayers %s',
+    maxplayers    => 'maxplayers %s',
+    minplayers    => 'minplayers %s',
+    botskill      => 'botskill %s',
+    balanceteams  => 'balanceteams %s',
+    playersbalanceteams => 'playersbalanceteams %s',
+    friendlyfire  => 'friendlyfire %s',
+    maxteams      => 'maxteams %s',
+    timelimit     => 'timelimit %s',
+    goalteamscore => 'goalteamscore %s',
+    fraglimit     => 'fraglimit %s',
+    mutators      => 'mutators %s',
+    updated       => 'updated %s',
+  }->{ $o{sort}||'server_id' }, $o{reverse} ? 'DESC' : 'ASC';
+
+  return $s->db_all( q|
+    SELECT !s FROM utserver_info
+      !W
+      ORDER BY !s|
+      .($o{limit} ? " LIMIT ?" : ""),
+    join(', ', @select), \%where, $order, ($o{limit} ? $o{limit} : ()),
+  );
+}
+
+
+################################################################################
+## Update serverinfo for an existing address to the utserver list.
+## opts: all server info data fields.
+################################################################################
+sub update_utserver {
+  my $self = shift;
+  my $id   = shift;
+  my %s = (
+   # defaults
+   updated    => time,
+   @_);
+
+  # try updating it in serverlist
+  my %H = (
+    $s{minnetver}           ? (            'minnetver = ?' =>      $s{minnetver} )                    : (),
+    $s{gamever}             ? (              'gamever = ?' => int( $s{gamever}) )                     : (),
+    $s{location}            ? (             'location = ?' =>      $s{location} )                     : (),
+    $s{listenserver}        ? (         'listenserver = ?' =>    ( $s{listenserver} ? 1 : 0) )        : (),
+    $s{hostport}            ? (             'hostport = ?' =>      $s{hostport})                      : (),
+    $s{hostname}            ? (             'hostname = ?' =>      $s{hostname})                      : (),
+    $s{AdminName}           ? (            'adminname = ?' =>      $s{AdminName})                     : (),
+    $s{AdminEMail}          ? (           'adminemail = ?' =>      $s{AdminEMail})                    : (),
+    $s{password}            ? (             'password = ?' =>    ( $s{password} ? 1 : 0) )            : (),
+    $s{gametype}            ? (             'gametype = ?' =>      $s{gametype})                      : (),
+    $s{gamestyle}           ? (            'gamestyle = ?' =>      $s{gamestyle})                     : (),
+    $s{changelevels}        ? (         'changelevels = ?' =>    ( $s{changelevels} ? 1 : 0) )        : (),
+    $s{maptitle}            ? (             'maptitle = ?' =>      $s{maptitle})                      : (),
+    $s{mapname}             ? (              'mapname = ?' =>      $s{mapname})                       : (),
+    $s{numplayers}          ? (           'numplayers = ?' =>      $s{numplayers})                    : ('numplayers = ?' => 0),
+    $s{maxplayers}          ? (           'maxplayers = ?' =>      $s{maxplayers})                    : ('maxplayers = ?' => 0),
+    $s{minplayers}          ? (           'minplayers = ?' =>      $s{minplayers})                    : ('minplayers = ?' => 0),
+    $s{botskill}            ? (             'botskill = ?' =>      $s{botskill})                      : (),
+    $s{balanceteams}        ? (         'balanceteams = ?' =>    ( $s{balanceteams} ? 1 : 0) )        : (),
+    $s{playersbalanceteams} ? (  'playersbalanceteams = ?' =>    ( $s{playersbalanceteams} ? 1 : 0) ) : (),
+    $s{friendlyfire}        ? (         'friendlyfire = ?' =>      $s{friendlyfire})                  : (),
+    $s{maxteams}            ? (             'maxteams = ?' =>      $s{maxteams})                      : (),
+    $s{timelimit}           ? (            'timelimit = ?' =>      $s{timelimit})                     : (),
+    $s{goalteamscore}       ? (        'goalteamscore = ?' => int( $s{goalteamscore}) )               : (),
+    $s{fraglimit}           ? (            'fraglimit = ?' => int( $s{fraglimit}) )                   : (),
+    $s{mutators}            ? (             'mutators = ?' =>      $s{mutators})                      : ('mutators = ?' => "None"),
+    $s{updated}             ? ('updated = datetime(?, \'unixepoch\')' =>      $s{updated})            : (),
+  );
+  
+  my($q, @p) = sqlprint("UPDATE utserver_info !H WHERE server_id = ?", \%H, $id);
+  return $self->{dbh}->do($q, undef, @p);
+}
+
+
+################################################################################
+## Add a new utserver and trigger the update routine above.
+## opts: id, server info data
+################################################################################
+sub add_utserver {
+  my ($self, $ip, $port) = @_;
+
+  # create new entry
+  return $self->{dbh}->do(
+    "INSERT INTO utserver_info (server_id)
+     SELECT (SELECT id FROM serverlist WHERE ip = ? AND port = ?)", 
+     undef, $ip, $port);
+}
+
+
+################################################################################
+## Delete all players from a certain server ID
+## opts: server id
+################################################################################
+sub delete_utplayers {
+  my ($self, $sid) = @_;
+  
+  # delete players for server_id
+  return $self->{dbh}->do(
+    "DELETE FROM utplayer_info WHERE server_id = ?",
+    undef, $sid);
+}
+
+################################################################################
+## Insert player info for a single player in server sid
+## opts: server id, player info
+################################################################################
+sub insert_utplayer {
+  my $self = shift;
+  my $sid  = shift;
+  my %s = (
+   updated  => time,
+   @_);
+
+  # apparently useless chunk of code
+  # FIXME move to site part
+  my %H = (
+    $s{server_id} ? ( 'server_id = ?' =>      $s{server_id})  : (),
+    $s{player}    ? (    'player = ?' =>      $s{player})     : (),
+    $s{team}      ? (      'team = ?' => int( $s{team}))      : (),
+    $s{frags}     ? (     'frags = ?' => int( $s{frags}))     : (),
+    $s{mesh}      ? (      'mesh = ?' =>      $s{mesh})       : (),
+    $s{skin}      ? (      'skin = ?' =>      $s{skin})       : (),
+    $s{face}      ? (      'face = ?' =>      $s{face})       : (),
+    $s{ping}      ? (      'ping = ?' => int( $s{ping}))      : (),
+    $s{ngsecret}  ? (  'ngsecret = ?' =>      $s{ngsecret})   : (),
+    $s{updated}   ? ('updated = datetime(?, \'unixepoch\')' => $s{updated}) : (),
+  );
+  
+  # insert
+  return $self->{dbh}->do(
+    "INSERT INTO utplayer_info (server_id, player, team, frags, mesh, skin, face, ping, ngsecret) 
+     VALUES (?,?,?,?,?,?,?,?,?)", 
+     undef, $sid, $s{player}, $s{team}, $s{frags}, $s{mesh}, $s{skin}, $s{face}, $s{ping}, $s{ngsecret});
+}
+
+1;
diff --git a/lib/MasterServer/Database/dbCore.pm b/lib/MasterServer/Database/dbCore.pm
deleted file mode 100755
index 4ea0c79..0000000
--- a/lib/MasterServer/Database/dbCore.pm
+++ /dev/null
@@ -1,83 +0,0 @@
-
-package MasterServer::Database::dbCore;
-
-use strict;
-use warnings;
-use Exporter 'import';
-
-our @EXPORT = qw| database_login |;
-
-################################################################################
-## login to the database with credentials provided in the config file.
-## returns dbh object or quits application on error.
-##
-## Recommended database types: Postgresql, MySQL or SQLite. Warranty void if
-## other database types are used. Use at your own risk.
-################################################################################
-sub database_login {
-  my $self = shift;
-
-  # read db type from db login
-  my @db_type = split(':', $self->{dblogin}->[0]);
-  
-  # if the type is an SQLite database, check if the database file exists
-  if ( "SQLite" =~ m/$db_type[1]/i) {
-    
-    # check if database file exists
-    my $db_file = [split(':', $self->{dblogin}->[0])]->[2];
-       $db_file =~ s/dbname=//i;
-
-    unless (-e $db_file) {
-      # fatal error
-      $self->log("fatal", "Database file $db_file does not exist!");
-      
-      # end program
-      $self->halt();
-    }
-  }
-  
-  # inform what DB we try to load
-  # $self->log("info","Database: $db_type[1]");
-    
-  # create the dbi object
-  my $dbh = DBI->connect(@{$self->{dblogin}}, {PrintError => $self->{db_print}});
-  
-  # verify that the database connected
-  if (defined $dbh) {
-  
-    # log the event
-    $self->log("info","Connected to the $db_type[1] database.");
-    
-    # turn on error printing
-    $dbh->{printerror} = 1;
-    
-    # if the type is an SQLite database, disable sync rw.
-    if ( "SQLite" =~ m/$db_type[1]/i) {
-  
-      # synchronous read/writing to the SQLite file OFF. That means: when the script 
-      # shuts down unexpectedly, i.e. because of power failure or a crash, changes 
-      # to the database are NOT SAVED. However, if this setting is not turned OFF, 
-      # it takes too long to write to the database, which means that new beacons, 
-      # requests and servers cannot be processed. You don't have a choice, really..
-      $dbh->do("PRAGMA synchronous = OFF");
-      
-      # allow the use of foreign keys (referencing)
-      $dbh->do("PRAGMA foreign_keys = ON");
-    }
-    
-    # return the dbi object for further use
-    return $dbh;
-  }
-  else {
-    # fatal error
-    $self->log("fatal", "$DBI::errstr!");
-    
-    # end program
-    $self->halt();
-  }
-  
-  # in case of any other error, return undef.
-  return undef;
-}
-
-1;