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

9 files changed, 241 insertions, 529 deletions

diff --git a/lib/MasterServer/Database/Pg/dbAddServers.pm b/lib/MasterServer/Database/Pg/dbAddServers.pm
index 31deb08..6befd83 100755
--- a/lib/MasterServer/Database/Pg/dbAddServers.pm
+++ b/lib/MasterServer/Database/Pg/dbAddServers.pm
@@ -4,159 +4,78 @@ 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 = to_timestamp(?)'    => $o{updated})     : (),
-    $o{beacon}   ? (  'beacon = to_timestamp(?)'    => $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 = to_timestamp(?)' => $o{updated})     : (),
+    $o{direct}   ? (  'b333ms = CAST(? AS BOOLEAN)' => $o{direct})  : (),
+    $o{direct}   ? (  'beacon = to_timestamp(?)'    => $o{updated}) : (),
+    $o{updated}  ? ( 'updated = to_timestamp(?)'    => $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 > to_timestamp(?)",
-      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 > to_timestamp(?)' => (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 (debug)
-  #$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 (debug)
-   #$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/Pg/dbAppletActions.pm b/lib/MasterServer/Database/Pg/dbAppletActions.pm
index dc7d941..9dcb3e7 100755
--- a/lib/MasterServer/Database/Pg/dbAppletActions.pm
+++ b/lib/MasterServer/Database/Pg/dbAppletActions.pm
@@ -3,7 +3,6 @@ package MasterServer::Database::Pg::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 = to_timestamp(?),
-        updated = to_timestamp(?)",
-    undef, time, time);
+  return $self->{dbh}->do(
+   "UPDATE appletlist 
+     SET added = to_timestamp(?),
+       updated = to_timestamp(?)",
+   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 = to_timestamp(?) 
-    WHERE ip = ?
-      AND port = ?
+  return $self->{dbh}->do(
+    "UPDATE appletlist 
+     SET updated = to_timestamp(?) 
+     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 > to_timestamp(?)",
-      time-604800);
+    "SELECT * 
+     FROM appletlist
+     WHERE updated > to_timestamp(?)",
+    time-604800);
 }
 
 ################################################################################
@@ -82,11 +84,12 @@ sub remove_unresponsive_applets {
 
   # remove entries
   my $u = $self->{dbh}->do(
-     "DELETE FROM appletlist 
-      WHERE updated < to_timestamp(?)", undef, time-604800);
+  "DELETE FROM appletlist 
+   WHERE updated < to_timestamp(?)", 
+  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/Pg/dbCiphers.pm b/lib/MasterServer/Database/Pg/dbCiphers.pm
index 5343065..6dde097 100755
--- a/lib/MasterServer/Database/Pg/dbCiphers.pm
+++ b/lib/MasterServer/Database/Pg/dbCiphers.pm
@@ -3,19 +3,17 @@ package MasterServer::Database::Pg::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/Pg/dbCore.pm b/lib/MasterServer/Database/Pg/dbCore.pm
index 0891012..4b2d16f 100755
--- a/lib/MasterServer/Database/Pg/dbCore.pm
+++ b/lib/MasterServer/Database/Pg/dbCore.pm
@@ -4,50 +4,30 @@ 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]");
-    
-  # create the dbi object
+  
+  # connect to Pg 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 $db_type[1] database $db_type[2]");
     $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;
 }
 
 ################################################################################
@@ -56,23 +36,15 @@ sub database_login {
 ################################################################################
 sub dump_database {
   my $self = shift;
-  
-  # filename / time
   my $time = strftime('%Y-%m-%d-%H-%M',localtime);
-  
-  # FIXME
-  # separate absolute path and relative path, 
-  # split database filename for dump filename.
-  
+
   # read db credentials from db login
   my @db_type = split(':', $self->{dblogin}->[0]);
   $db_type[2] =~ s/dbname=//;
   
   # use pg_dump to dump Postgresql databases
   system("pg_dump $db_type[2] -U $self->{dblogin}->[1] > $self->{root}/data/dumps/Pg-$time-$db_type[2].db");
-  
-  # log
-  $self->log("dump", "Dumping database to /data/dumps/$db_type[1]-$time.db");
+  $self->log("dump", "Dumping database to /data/dumps/Pg-$time-$db_type[2].db");
 }
 
 
diff --git a/lib/MasterServer/Database/Pg/dbExtendedInfo.pm b/lib/MasterServer/Database/Pg/dbExtendedInfo.pm
new file mode 100755
index 0000000..8ecc624
--- /dev/null
+++ b/lib/MasterServer/Database/Pg/dbExtendedInfo.pm
@@ -0,0 +1,88 @@
+package MasterServer::Database::Pg::dbExtendedInfo;
+
+use strict;
+use warnings;
+use MasterServer::Core::Util 'sqlprint';
+use Exporter 'import';
+our @EXPORT = qw| insert_extended
+                  update_extended
+                  delete_players
+                  insert_players |;
+
+################################################################################
+## Add extended server information for a new server.
+## opts: ipm hostport
+################################################################################
+sub insert_extended {
+  my $self = shift;
+  my %o = ( @_);
+  return $self->{dbh}->do(
+    "INSERT INTO extended_info (server_id)
+     SELECT (SELECT id FROM serverlist WHERE ip = ? AND hostport = ?)", 
+    undef, $o{ip}, $o{hostport});
+}
+
+################################################################################
+## Update serverinfo for an existing address to the utserver list.
+## opts: all server info data fields.
+################################################################################
+sub update_extended {
+  my $self = shift;
+  my %o = (updated => time, @_);
+
+  # try updating it in serverlist
+  my %H = (
+    $o{minnetver}           ? (            'minnetver = ?' => $o{minnetver} )           : (),
+    $o{location}            ? (             'location = ?' => $o{location} )            : (),
+    $o{listenserver}        ? (         'listenserver = ?' => $o{listenserver})         : (),
+    $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})           : ('numplayers = ?' => 0),
+    $o{maxplayers}          ? (           'maxplayers = ?' => $o{maxplayers})           : ('maxplayers = ?' => 0),
+    $o{minplayers}          ? (           'minplayers = ?' => $o{minplayers})           : ('minplayers = ?' => 0),
+    $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}            ? (             'mutators = ?' => $o{mutators})             : ('mutators = ?' => "None"),
+    $o{updated}             ? ('updated = to_timestamp(?)' => $o{updated})              : (),
+  );
+  
+  my($q, @p) = sqlprint("UPDATE extended_info !H WHERE server_id = ?", \%H, $o{sid});
+  return $self->{dbh}->do($q, undef, @p);
+}
+
+################################################################################
+## Delete all players from a certain server ID
+## opts: server id
+################################################################################
+sub delete_players {
+  my ($self, $sid) = @_;
+  
+  # delete players with server_id
+  return $self->{dbh}->do(
+    "DELETE FROM player_info WHERE server_id = ?",
+    undef, $sid);
+}
+
+################################################################################
+## Insert player info for a single player in server sid
+## opts: server id, player info
+################################################################################
+sub insert_players {
+  my ($self, @pl) = @_;
+  my($q, @p) = sqlprint("INSERT INTO player_info (server_id, player, team, frags, mesh, skin, face, ping, ngsecret) VALUES (!l)", \@pl);
+  return $self->{dbh}->do($q, undef, @p);
+}
+
+1;
diff --git a/lib/MasterServer/Database/Pg/dbGetServers.pm b/lib/MasterServer/Database/Pg/dbGetServers.pm
index e9bfaec..827443a 100755
--- a/lib/MasterServer/Database/Pg/dbGetServers.pm
+++ b/lib/MasterServer/Database/Pg/dbGetServers.pm
@@ -3,37 +3,35 @@ package MasterServer::Database::Pg::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 < to_timestamp(?)' => (time-$o{added}))   : (),
     $o{beacon}      ? ( 'beacon > to_timestamp(?)' => (time-$o{beacon}))  : (),
     $o{updated}     ? ('updated > to_timestamp(?)' => (time-$o{updated})) : (),
     $o{before}      ? ('updated < to_timestamp(?)' => (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 < to_timestamp(?)' => (time-$o{added})) : (),
-    $o{after} ? ('added > to_timestamp(?)' => (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/Pg/dbMaintenance.pm b/lib/MasterServer/Database/Pg/dbMaintenance.pm
index 7a4fc23..f6c39b7 100755
--- a/lib/MasterServer/Database/Pg/dbMaintenance.pm
+++ b/lib/MasterServer/Database/Pg/dbMaintenance.pm
@@ -3,7 +3,6 @@ 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 < to_timestamp(?)", undef, time-3600);
-  
-  # notify 
   $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/Pg/dbStats.pm b/lib/MasterServer/Database/Pg/dbStats.pm
index 6e92ab6..cfd61a4 100755
--- a/lib/MasterServer/Database/Pg/dbStats.pm
+++ b/lib/MasterServer/Database/Pg/dbStats.pm
@@ -3,41 +3,47 @@ package MasterServer::Database::Pg::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 > to_timestamp(?), FALSE)) AS numdirect,
-            COUNT(NULLIF(updated > to_timestamp(?), FALSE)) 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 > to_timestamp(?)",
+      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 = CAST(0 AS BOOLEAN)
-     WHERE beacon < to_timestamp(?) 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 < to_timestamp(?) 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/Pg/dbUTServerInfo.pm b/lib/MasterServer/Database/Pg/dbUTServerInfo.pm
deleted file mode 100755
index 0bf005e..0000000
--- a/lib/MasterServer/Database/Pg/dbUTServerInfo.pm
+++ /dev/null
@@ -1,237 +0,0 @@
-package MasterServer::Database::Pg::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 > to_timestamp(?)'=> (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 = to_timestamp(?)' =>      $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 = to_timestamp(?)' => $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;