new server checking mechanism, complete recode of major functionsv2.4.0

8 files changed, 157 insertions, 415 deletions

diff --git a/lib/MasterServer/Database/SQLite/dbAddServers.pm b/lib/MasterServer/Database/SQLite/dbAddServers.pm
index 592ab7b..88b1bc8 100755
--- a/lib/MasterServer/Database/SQLite/dbAddServers.pm
+++ b/lib/MasterServer/Database/SQLite/dbAddServers.pm
@@ -1,163 +1,81 @@
-
 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 |;
+our @EXPORT = qw| insert_server
+                  update_server 
+                  insert_pending |;
 
 ################################################################################
-## Update an existing address or add a new address to the pending list.
-## opts: direct beacon, set update, game
+## Insert minimalistic game data into serverlist
+## params: ip, port, hostport
 ################################################################################
-sub add_server_new {
+sub insert_server {
   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})      : (),
-   
-    # some applets have incorrect gamename lists, let udpticker update this 
-    # entry instead. this way, applets don't overwrite with incorrect data
-    #$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{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);
+  my %o = (@_);
 
-  # 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);
+  return $self->{dbh}->do(
+    "INSERT INTO serverlist (ip, port, hostport, country) VALUES (?, ?, ?, ?)", 
+    undef, $o{ip}, $o{port}, $o{hostport}, $self->ip2country($o{ip}) );
 }
 
 ################################################################################
 ## Update the server info in the serverlist
+## required: id or ip + port/hostport
 ################################################################################
-sub update_server_list {
+sub update_server {
   my $self = shift;
-  my %o = (
-    updated  => time,
-    @_);
+  my %o = (updated  => time, @_);
+
+  # either id, ip+port or ip+hostport are provided.
+  my %W = (
+    $o{id}       ? (      'id = ?' => $o{id})       : (),
+    $o{ip}       ? (      'ip = ?' => $o{ip})       : (),
+    $o{port}     ? (    'port = ?' => $o{port})     : (),
+    $o{hostport} ? ('hostport = ?' => $o{hostport}) : (),
+  );
 
-  # try updating it in serverlist
+  # update where possible
   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})     : (),
+    $o{direct}   ? (  'b333ms = CAST(? AS BOOLEAN)' => $o{direct})  : (),
+    $o{direct}   ? (  'beacon = datetime(?, \'unixepoch\')'    => $o{updated}) : (),
+    $o{updated}  ? ( 'updated = datetime(?, \'unixepoch\')'    => $o{updated}) : (),
   );
 
-  my($q, @p) = sqlprint("UPDATE serverlist !H 
-    WHERE ip = ? AND port = ?", \%H, $o{ip}, $o{port});
-
+  my($q, @p) = sqlprint("UPDATE serverlist !H !W", \%H, \%W);
   return $self->{dbh}->do($q, undef, @p);
 }
 
 ################################################################################
-## beacon was verified or otherwise accepted and will now be added to the 
-## serverlist.
+## check if an ip, port/hostport combination is recent in the serverlist.
+## if not, add the address to the pending list
 ################################################################################
-sub add_server_list {
+sub insert_pending {
   my $self = shift;
-  my %o = @_;
+  my %o = (updated => 3600, @_ );
 
-  # 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 count(*) FROM serverlist 
-      WHERE ip = ? 
-      AND port = ?
-      AND updated > datetime(?, 'unixepoch')",
-      undef, $ip, $port, time-7200);
-
-  # if found, return 0
-  return 0 if ($u > 0);
+  # selection criteria
+  my %W = (
+    $o{ip}       ? (      'ip = ?' => $o{ip})       : (),
+    $o{port}     ? (    'port = ?' => $o{port})     : (),
+    $o{updated}  ? ('updated > datetime(?, \'unixepoch\')' => (time-$o{updated})) : (),
+  );
 
-  # 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);  
+  # determine if it already exsits
+  my($q, @p) = sqlprint("SELECT id FROM serverlist !W", \%W);
+  my $u = $self->{dbh}->do($q, undef, @p);
+  return 0 if int($u);
 
-  # 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;
+  # else, insert in pending (duplicates may exist -- see remove_pending)
+  return $self->{dbh}->do("INSERT INTO pending (ip, heartbeat) VALUES (?, ?)", 
+    undef, $o{ip}, $o{port} );
 }
 
 1;
diff --git a/lib/MasterServer/Database/SQLite/dbAppletActions.pm b/lib/MasterServer/Database/SQLite/dbAppletActions.pm
index d2421fc..1d9c975 100755
--- a/lib/MasterServer/Database/SQLite/dbAppletActions.pm
+++ b/lib/MasterServer/Database/SQLite/dbAppletActions.pm
@@ -3,7 +3,6 @@ package MasterServer::Database::SQLite::dbAppletActions;
 use strict;
 use warnings;
 use Exporter 'import';
-
 our @EXPORT = qw|  add_master_applet 
                 update_master_applet 
                  reset_master_applets 
@@ -18,19 +17,20 @@ sub add_master_applet {
   my %o = @_;
 
   my $u = $self->{dbh}->do(
-     "SELECT * FROM appletlist 
-      WHERE ip = ? 
-      AND port = ?
+    "SELECT * FROM appletlist 
+     WHERE     ip = ? 
+      AND hostport = ?
       AND gamename = ?",
-      undef, $o{ip}, $o{port}, lc $o{gamename});
+    undef, $o{ip}, $o{hostport}, lc $o{gamename});
   
   # return if found
   return if ($u > 0);
 
   # insert applet data 
-  return $self->{dbh}->do("INSERT INTO appletlist (ip, port, gamename) 
-                           SELECT ?, ?, ?", undef, 
-                           $o{ip}, $o{port}, lc $o{gamename});
+  return $self->{dbh}->do(
+  "INSERT INTO appletlist (ip, hostport, gamename) 
+   SELECT ?, ?, ?", 
+  undef, $o{ip}, $o{hostport}, lc $o{gamename});
 }
 
 ################################################################################
@@ -38,10 +38,11 @@ sub add_master_applet {
 ################################################################################
 sub reset_master_applets {
   my $self = shift;
-  return $self->{dbh}->do("UPDATE appletlist 
-    SET   added = datetime(?, \'unixepoch\'),
-        updated = datetime(?, \'unixepoch\')",
-    undef, time, time);
+  return $self->{dbh}->do(
+   "UPDATE appletlist 
+     SET added = datetime(?, \'unixepoch\'),
+       updated = datetime(?, \'unixepoch\')",
+   undef, time, time);
 }
 
 ################################################################################
@@ -50,12 +51,13 @@ sub reset_master_applets {
 sub update_master_applet {
   my ($self, %o) = @_;
 
-  return $self->{dbh}->do("UPDATE appletlist 
-    SET updated = datetime(?, \'unixepoch\') 
-    WHERE ip = ?
-      AND port = ?
+  return $self->{dbh}->do(
+    "UPDATE appletlist 
+     SET updated = datetime(?, \'unixepoch\') 
+     WHERE       ip = ?
+      AND hostport = ?
       AND gamename = ?", 
-    undef, time, $o{ip}, $o{port}, lc $o{gamename});
+    undef, time, $o{ip}, $o{hostport}, lc $o{gamename});
 }
 
 ################################################################################
@@ -66,10 +68,10 @@ sub get_masterserver_applets {
   my $self = shift;
 
   return $self->db_all(
-     "SELECT * 
-      FROM appletlist
-      WHERE updated > datetime(?, \'unixepoch\')",
-      time-604800);
+    "SELECT * 
+     FROM appletlist
+     WHERE updated > datetime(?, \'unixepoch\')",
+    time-604800);
 }
 
 ################################################################################
@@ -82,11 +84,12 @@ sub remove_unresponsive_applets {
 
   # remove entries
   my $u = $self->{dbh}->do(
-     "DELETE FROM appletlist 
-      WHERE updated < datetime(?, \'unixepoch\')", undef, time-604800);
+  "DELETE FROM appletlist 
+   WHERE updated < datetime(?, \'unixepoch\')", 
+  undef, time-604800);
   
   # notify 
-  $self->log("delete", "Removed $u entries from applet list.") if ($u > 0);
+  $self->log("delete", "Removed $u entries from applet list") if ($u > 0);
 }
 
 1;
diff --git a/lib/MasterServer/Database/SQLite/dbCiphers.pm b/lib/MasterServer/Database/SQLite/dbCiphers.pm
index f257b7b..1032b4e 100755
--- a/lib/MasterServer/Database/SQLite/dbCiphers.pm
+++ b/lib/MasterServer/Database/SQLite/dbCiphers.pm
@@ -3,19 +3,17 @@ package MasterServer::Database::SQLite::dbCiphers;
 use strict;
 use warnings;
 use Exporter 'import';
-
 our @EXPORT = qw| check_cipher_count
                   clear_ciphers 
                   insert_cipher 
-                  get_game_props 
-                  get_gamenames |;
+                  get_game_props |;
 
 ################################################################################
 ## Check if ciphers exist
 ################################################################################
 sub check_cipher_count {
   my $self = shift;
-  return $self->db_all('SELECT count(*) as num from games')->[0]->{num};
+  return $self->db_all('SELECT count(gamename) as num from games')->[0]->{num};
 }
 
 ################################################################################
@@ -23,9 +21,7 @@ sub check_cipher_count {
 ################################################################################
 sub clear_ciphers {
   my $self = shift;
-
-  # delete ALL entries
-  my $u = $self->{dbh}->do("DELETE FROM games");
+  $self->{dbh}->do("DELETE FROM games");
 }
 
 ################################################################################
@@ -56,10 +52,7 @@ sub insert_cipher {
 ################################################################################
 sub get_game_props {
   my $s = shift;
-  my %o = (
-    sort => '', 
-    @_
-  );
+  my %o = (sort => '', @_);
   
   my %where = (
     $o{gamename}      ? ('gamename = ?'       => lc $o{gamename})   : (),
@@ -90,18 +83,4 @@ sub get_game_props {
   );
 }
 
-
-################################################################################
-## 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/dbCore.pm b/lib/MasterServer/Database/SQLite/dbCore.pm
index 0772a4e..d16cb26 100755
--- a/lib/MasterServer/Database/SQLite/dbCore.pm
+++ b/lib/MasterServer/Database/SQLite/dbCore.pm
@@ -4,72 +4,47 @@ use strict;
 use warnings;
 use POSIX qw/strftime/;
 use Exporter 'import';
-
 our @EXPORT = qw| database_login dump_database |;
 
 ################################################################################
 ## 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]);
 
-  # inform what db we try to load
-  $self->log("info","Database: $db_type[1], $db_type[2]");
-
   # check if database file exists
   my $db_file = [split(':', $self->{dblogin}->[0])]->[2];
      $db_file =~ s/dbname=//i;
 
+  # fatal error if database does not exist
   unless (-e $db_file) {
-    # fatal error
     $self->log("fatal", "Database file $db_file does not exist!");
-    
-    # end program
     $self->halt();
   }
 
-  # create the dbi object
+  # connect to SQLite database
   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
+    $self->log("info","Connected to the $db_type[1] database $db_type[2]");
     $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..
+    # synchronous read/writing to the SQLite file OFF. Faster, but risk on data
+    # loss on crashes, premature exits or power failure.
     $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;
 }
 
 ################################################################################
@@ -78,21 +53,15 @@ sub database_login {
 ################################################################################
 sub dump_database {
   my $self = shift;
-  
-  # filename / time
   my $time = strftime('%Y-%m-%d-%H-%M',localtime);
   
   # read db credentials from db login
   my @db_type = split ':', $self->{dblogin}->[0];
   $db_type[2] =~ s/dbname=//;
-  
-  # split db path
   my @db_path = split '/', $db_type[2];
   
-  # use pg_dump to dump Postgresql databases
+  # make a copy of the database file
   system("cp $db_type[2] $self->{root}/data/dumps/SQLite-$time-$db_path[-1]");
-  
-  # log
   $self->log("dump", "Dumping database to /data/dumps/SQLite-$time-$db_path[-1]");
 }
 
diff --git a/lib/MasterServer/Database/SQLite/dbGetServers.pm b/lib/MasterServer/Database/SQLite/dbGetServers.pm
index 719e00a..7ddce2b 100755
--- a/lib/MasterServer/Database/SQLite/dbGetServers.pm
+++ b/lib/MasterServer/Database/SQLite/dbGetServers.pm
@@ -3,37 +3,35 @@ package MasterServer::Database::SQLite::dbGetServers;
 use strict;
 use warnings;
 use Exporter 'import';
-
 our @EXPORT = qw| get_server 
                   get_pending |;
 
 ################################################################################
 ## get server details for one or multiple servers
-## opts: limit, see $order
+## opts: limit, see %where or $order
 ################################################################################
 sub get_server {
   my $s = shift;
-  my %o = (
-    sort => '', 
-    @_
-  );
+  my %o = (sort => '', blacklisted => 0, @_);
   
   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{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{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}))  : (),
+    
+    # never process blacklisted servers, unless explicitly specified
+    ('blacklisted = CAST(? AS BOOLEAN)' => $o{blacklisted}),
   );
   
   my @select = ( qw|
@@ -79,45 +77,29 @@ sub get_server {
 
 ################################################################################
 ## get server details for one or multiple pending servers
-## opts: limit, next_id, beaconport, heartbeat, gamename, secure, enctype, added
+## opts: limit, next_id, ip, heartbeat port
 ################################################################################
 sub get_pending {
   my $s = shift;
-  my %o = (
-    sort => '', 
-    @_
-  );
+  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 @select = ( qw| id ip heartbeat 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 ?" : ""),
+  return $s->db_all( q| SELECT !s FROM pending !W ORDER BY !s|
+    .($o{limit} ? " LIMIT ?" : ""),
     join(', ', @select), \%where, $order, ($o{limit} ? $o{limit} : ()),
   );
 }
diff --git a/lib/MasterServer/Database/SQLite/dbMaintenance.pm b/lib/MasterServer/Database/SQLite/dbMaintenance.pm
index 019f31f..ec9b5cc 100755
--- a/lib/MasterServer/Database/SQLite/dbMaintenance.pm
+++ b/lib/MasterServer/Database/SQLite/dbMaintenance.pm
@@ -1,9 +1,8 @@
-package MasterServer::Database::SQLite::dbMaintenance;
+package MasterServer::Database::Pg::dbMaintenance;
 
 use strict;
 use warnings;
 use Exporter 'import';
-
 our @EXPORT = qw| delete_old_pending 
                       remove_pending |;
 
@@ -12,14 +11,10 @@ our @EXPORT = qw| delete_old_pending
 ## where the server is unresponsive for more than 1 hour
 ################################################################################
 sub delete_old_pending {
-  my ($self) = shift;
-
-  # remove servers
+  my $self = shift;
   my $u = $self->{dbh}->do(
      "DELETE FROM pending 
-      WHERE added < datetime(?, 'unixepoch')", undef, time-3600);
-  
-  # notify 
+      WHERE added < datetime(?, \'unixepoch\')", undef, time-3600);
   $self->log("delete", "Removed $u entries from pending.") if ($u > 0);
 }
 
@@ -28,13 +23,11 @@ sub delete_old_pending {
 ## 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);
+  my $self = shift;
+  my %o = ( @_); 
+  my $u = $self->{dbh}->do("DELETE FROM pending WHERE ip = ? AND heartbeat = ?", 
+    undef, $o{ip}, $o{port});
+  $self->log("delete", "removed $o{ip}, $o{port} from pending (".$u."x)") if ($u > 0);
 }
 
 1;
diff --git a/lib/MasterServer/Database/SQLite/dbStats.pm b/lib/MasterServer/Database/SQLite/dbStats.pm
index 1cf94e6..57d3100 100755
--- a/lib/MasterServer/Database/SQLite/dbStats.pm
+++ b/lib/MasterServer/Database/SQLite/dbStats.pm
@@ -3,41 +3,47 @@ package MasterServer::Database::SQLite::dbStats;
 use strict;
 use warnings;
 use Exporter 'import';
-
-our @EXPORT = qw| get_gamelist_stats 
-                  write_direct_beacons
+our @EXPORT = qw| get_gamenames
+                  get_gamestats
+                  get_listedstats
                   write_stat 
+                  write_direct_beacons
                   write_kfstats |;
 
 ################################################################################
-# calculate stats for all individual games
+## get a list of distinct gamenames currently in the server list
 ################################################################################
-sub get_gamelist_stats {
+sub get_gamenames {
   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);
+     "SELECT distinct gamename 
+      FROM serverlist");
 }
 
 ################################################################################
-# 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.
+## get statistics (num_direct, num_total) per gamename
 ################################################################################
-sub write_direct_beacons {
+sub get_gamestats {
+  my ($self, $gn) = @_;
+  return $self->db_all(
+     "SELECT COUNT(CASE WHEN b333ms THEN 1 END) as num_uplink, count(*) as num_total
+      FROM serverlist
+      WHERE gamename = ? AND updated > datetime(?, \'unixepoch\')",
+      lc $gn, time-7200);
+}
+
+################################################################################
+## 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_listedstats {
   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);
+  return $self->{dbh}->selectall_arrayref(
+     "SELECT gamename 
+      FROM games
+      WHERE num_uplink > 0
+         OR num_total  > 0");
 }
 
 ################################################################################
@@ -45,19 +51,28 @@ sub write_direct_beacons {
 # 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(
+  my ($self, %o) = @_;
+  return $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);
+      undef, $o{num_uplink}, $o{num_total}, lc $o{gamename});
+}
 
+################################################################################
+# 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 = CAST(0 AS BOOLEAN)
+     WHERE beacon < datetime(?, \'unixepoch\') AND b333ms", 
+     undef, time-3600);
+  $self->log("unset", "Lost $u direct beacons.") if ($u > 0);
 }
 
 ################################################################################
@@ -66,7 +81,7 @@ sub write_stat {
 sub write_kfstats {
   my ($self, $h) = @_;
 
-  # check if entry already excists.
+  # check if entry already exists.
   my $u = $self->{dbh}->selectall_arrayref(
     "SELECT * FROM kfstats WHERE UTkey = ? ", undef, $h->{UTkey});
   
@@ -74,13 +89,11 @@ sub write_kfstats {
     $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 
+  $self->{dbh}->do("UPDATE kfstats SET 
       Username = ?, 
       CurrentVeterancy = ?, 
       TotalKills = ?, 
diff --git a/lib/MasterServer/Database/SQLite/dbUTServerInfo.pm b/lib/MasterServer/Database/SQLite/dbUTServerInfo.pm
index 119900b..5a579ac 100755
--- a/lib/MasterServer/Database/SQLite/dbUTServerInfo.pm
+++ b/lib/MasterServer/Database/SQLite/dbUTServerInfo.pm
@@ -5,127 +5,12 @@ use warnings;
 use MasterServer::Core::Util 'sqlprint';
 use Exporter 'import';
 
-our @EXPORT = qw| get_utserver
-                  add_utserver
+our @EXPORT = qw| 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.
 ################################################################################