Full support for Postgres, MySQL and SQLite3 + minor bug fixes

4 files changed, 445 insertions, 0 deletions

diff --git a/lib/MasterServer/Database/mysql/dbBeacon.pm b/lib/MasterServer/Database/mysql/dbBeacon.pm
new file mode 100755
index 0000000..fd956b7
--- /dev/null
+++ b/lib/MasterServer/Database/mysql/dbBeacon.pm
@@ -0,0 +1,203 @@
+
+package MasterServer::Database::mysql::dbBeacon;
+
+use strict;
+use warnings;
+use Exporter 'import';
+
+our @EXPORT = qw| add_beacon
+                  add_pending
+                  remove_pending 
+                  get_pending_beacon 
+                  get_pending_info
+                  get_next_pending |;
+
+################################################################################
+## Update beacon in serverlist or pending list. Add if beacon does not exist in
+## either list. Return 0,1,2 if success in adding or -1 on error.
+################################################################################
+sub add_beacon {
+  my ($self, $ip, $beaconport, $heartbeat, $gamename, $secure) = @_;
+
+  # if address is in list, update the timestamp
+  my $u = $self->{dbh}->do(
+     "UPDATE serverlist 
+      SET beacon   = NOW(),
+          updated  = NOW(),
+          gamename = ?,
+          b333ms   = 1
+      WHERE ip = ? 
+      AND port = ?",
+      undef, lc $gamename, $ip, $heartbeat);
+  
+  # notify
+  $self->log("update", "beacon heartbeat for $ip:$heartbeat") if ($u > 0);
+  
+  # if serverlist was updated 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 added       = NOW(),
+          beaconport  = ?,
+          gamename    = ?,
+          secure      = ?
+      WHERE ip = ? 
+      AND heartbeat = ?",
+      undef, $beaconport, lc $gamename, $secure, $ip, $heartbeat);  
+                            
+  # notify
+  $self->log("update", "beacon heartbeat $ip:$beaconport pending $gamename:$heartbeat") if ($u > 0);
+  
+  # beacon was already in pending list and was updated
+  return 1 if ($u > 0);
+  
+  # if not found, add it
+  $u = $self->{dbh}->do(
+     "INSERT INTO pending (
+          ip, 
+          beaconport, 
+          heartbeat, 
+          gamename, 
+          secure) 
+      SELECT ?, ?, ?, ?, ?",
+      undef, $ip, $beaconport, $heartbeat, lc $gamename, $secure);
+                      
+   # notify 
+   $self->log("add", "beacon heartbeat $ip:$beaconport pending $gamename:$heartbeat") if ($u > 0);
+   
+   # it was added to pending
+   return 2 if ($u > 0);
+   
+  # or else report error
+  $self->log("error", "an error occurred adding beacon $ip:$beaconport with $gamename:$heartbeat to the pending list");
+  return -1;
+}
+
+################################################################################
+## Add an address to the database that was obtained via a method other than 
+## an udp beacon. Return 0,1,2 if success in adding or -1 on error.
+################################################################################
+sub add_pending {
+  my ($self, $ip, $port, $gamename, $secure) = @_;
+
+  # if address is in list, update the timestamp
+  my $u = $self->{dbh}->do(
+     "UPDATE serverlist 
+      SET updated = NOW()
+      WHERE ip = ? 
+      AND port = ?",
+      undef, $ip, $port);
+
+  # notify
+  $self->log("update", "updated serverlist with $ip:$port") if ($u > 0);
+  
+  # if updated, 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 added  = NOW(),
+          secure = ?
+      WHERE ip = ? 
+      AND heartbeat = ?",
+      undef, $secure, $ip, $port);
+      
+  # notify
+  $self->log("update", "updated pending with $ip:$port") if ($u > 0);
+  
+  # return 1 if updated
+  return 1 if ($u > 0);
+  
+  # if not found, add it
+  $u = $self->{dbh}->do(
+     "INSERT INTO pending (
+          ip, 
+          heartbeat, 
+          gamename, 
+          secure) 
+      SELECT ?, ?, ?, ?",
+      undef, $ip, $port, $gamename, $secure);
+      
+   # notify
+   $self->log("add", "$ip:$port added pending $gamename") if ($u > 0);
+   
+   # return 2 if added new
+   return 2 if ($u > 0);
+   
+  # else
+  return -1;
+}
+
+################################################################################
+## 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);
+  
+  # it was removed from pending
+  return 2 if ($u > 0);
+  
+  # or else report error
+  $self->log("error", "error deleting server $id from pending");
+  return -1;
+}
+
+################################################################################
+## Get pending server by ip, beacon port. Returns * or undef
+################################################################################
+sub get_pending_beacon {
+  my ($self, $ip, $port) = @_;
+  
+  # if address is in list, update the timestamp
+  return $self->{dbh}->selectall_arrayref(
+     "SELECT * FROM pending
+      WHERE ip = ?
+      AND beaconport = ?",
+      undef, $ip, $port)->[0];
+}
+
+################################################################################
+## Get pending server by ip, heartbeat port. Returns * or undef
+################################################################################
+sub get_pending_info {
+  my ($self, $ip, $port) = @_;
+  
+  # if address is in list, update the timestamp
+  return $self->{dbh}->selectall_arrayref(
+     "SELECT * FROM pending
+      WHERE ip = ? 
+      AND heartbeat = ?",
+      undef, $ip, $port)->[0];
+}
+
+################################################################################
+## Get server info from any entry with an id higher than the provided one. The
+## server is added to pending at least 15 seconds ago. Returns info or undef.
+################################################################################
+sub get_next_pending {
+  my ($self, $id) = @_;
+  
+  # get 1 pending id that is older than 15s
+  return $self->{dbh}->selectall_arrayref(
+     "SELECT id, ip, heartbeat, secure FROM pending
+      WHERE added < NOW() - INTERVAL 15 SECOND
+      AND id > ?
+      ORDER BY id ASC LIMIT 1", 
+      undef, $id)->[0];
+}
+
+
+1;
diff --git a/lib/MasterServer/Database/mysql/dbClientList.pm b/lib/MasterServer/Database/mysql/dbClientList.pm
new file mode 100755
index 0000000..898eb28
--- /dev/null
+++ b/lib/MasterServer/Database/mysql/dbClientList.pm
@@ -0,0 +1,45 @@
+
+package MasterServer::Database::mysql::dbClientList;
+
+use strict;
+use warnings;
+use Exporter 'import';
+
+our @EXPORT = qw| get_gamenames
+                  get_game_list |;
+
+
+################################################################################
+## 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.
+## 
+## returns: hashref of gamenames
+################################################################################
+sub get_gamenames {
+  my $self = shift;
+
+  return $self->{dbh}->selectall_arrayref(
+     "SELECT distinct gamename 
+      FROM serverlist");
+}
+
+################################################################################
+## get the list of games of a certain $gamename, excluding the ones excempted
+## via the blacklist
+## only returns server addresses that are no more than 1 hours old
+################################################################################
+sub get_game_list {
+  my ($self, $gamename) = @_;
+  
+  return $self->{dbh}->selectall_arrayref(
+    "SELECT ip, port 
+     FROM serverlist
+     WHERE updated > NOW() - INTERVAL 10800 SECOND
+     AND gamename = ?
+     AND NOT blacklisted", 
+     undef, lc $gamename);
+}
+
+
+1;
diff --git a/lib/MasterServer/Database/mysql/dbCore.pm b/lib/MasterServer/Database/mysql/dbCore.pm
new file mode 100755
index 0000000..30fe3ef
--- /dev/null
+++ b/lib/MasterServer/Database/mysql/dbCore.pm
@@ -0,0 +1,44 @@
+
+package MasterServer::Database::mysql::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.
+################################################################################
+sub database_login {
+  my $self = shift;
+
+  # 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("load","Connected to the MySQL 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();
+  }
+  
+  # unreachable
+  return undef;
+}
+
+1;
diff --git a/lib/MasterServer/Database/mysql/dbServerlist.pm b/lib/MasterServer/Database/mysql/dbServerlist.pm
new file mode 100755
index 0000000..5bc1cd3
--- /dev/null
+++ b/lib/MasterServer/Database/mysql/dbServerlist.pm
@@ -0,0 +1,153 @@
+
+package MasterServer::Database::mysql::dbServerlist;
+
+use strict;
+use warnings;
+use Exporter 'import';
+
+our @EXPORT = qw| add_to_serverlist 
+                  update_serverlist
+                  syncer_add
+                  get_next_server |;
+
+################################################################################
+## beacon was verified or otherwise accepted and will now be added to the 
+## serverlist.
+################################################################################
+sub add_to_serverlist {
+  my ($self, $ip, $port, $gamename) = @_;
+  
+  # update or add server to serverlist
+  my $u = $self->{dbh}->do("UPDATE serverlist 
+                            SET updated  = NOW()
+                            WHERE ip = ? 
+                            AND port = ?",
+                            undef, $ip, $port);
+
+  # notify
+  $self->log("update", "$ip:$port timestamp updated") if ($u > 0);
+  
+  # if found, updated; done
+  return 0 if ($u > 0);
+  
+  # if not found, add it.
+     $u = $self->{dbh}->do("INSERT INTO serverlist (ip, port, gamename, country) 
+                            SELECT ?, ?, ?, ?",
+                            undef, $ip, $port, $gamename, $self->ip2country($ip));
+
+  # notify
+  $self->log("add", "$ip:$port added to serverlist") if ($u > 0);
+  
+  # return added
+  return 1 if ($u > 0);
+
+  # or else report error
+  $self->log("error", "an error occurred adding server $ip:$port ($gamename) to the serverlist");
+  return -1;
+}
+
+################################################################################
+## same as add_to_serverlist above, but does not add the server to serverlist
+## if it does not exist in serverlist. it must be added by another function
+## first.
+################################################################################
+sub update_serverlist {
+  my ($self, $ip, $port, $s) = @_;
+  
+  # update server info
+  my $u = $self->{dbh}->do(
+           'UPDATE serverlist 
+            SET updated  = NOW(),
+              gamename = ?,
+              gamever  = ?,
+              hostname = ?,
+              hostport = ?
+            WHERE ip = ?
+            AND port = ?', undef, 
+          $s->{gamename}, $s->{gamever}, $s->{hostname}, $s->{hostport}, 
+          $ip, $port);
+
+  # notify
+  $self->log("update", "server $ip:$port info updated") if ($u > 0);
+  
+  # return 0 if updated
+  return 0 if ($u > 0);
+   
+  # or else report error
+  $self->log("error", "an error occurred updating server $ip:$port in the serverlist");
+  return -1;
+}
+
+
+################################################################################
+## 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 list, update the timestamp
+  my $u = $self->{dbh}->do(
+     "SELECT * FROM serverlist 
+      WHERE ip = ? 
+      AND port = ?",
+      undef, $ip, $port);
+ 
+  # notify
+  $self->log("read","syncer found entry for $ip:$port") if ($u > 0);
+  
+  # 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",
+          $self->{log_settings}->{db_updated}) if ($u > 0);
+  
+  # return 1 if found
+  return 1 if ($u > 0);
+  
+  # if not found, add it
+  $u = $self->{dbh}->do(
+     "INSERT INTO pending (ip, heartbeat, gamename, secure) 
+      SELECT ?, ?, ?, ?",
+      undef, $ip, $port, $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;
+}
+
+################################################################################
+## get a server address of the next server in line to be queried for game info. 
+## query must be older than 30 seconds (in case it just got added) and not 
+## older than 3 hours. FIXME: now older servers are ignored!
+################################################################################
+sub get_next_server {
+  my ($self, $id) = @_;
+  
+  return $self->{dbh}->selectall_arrayref(
+     "SELECT id, ip, port FROM serverlist
+      WHERE added < NOW() - INTERVAL 15 SECOND
+      AND updated > NOW() - INTERVAL 10800 SECOND
+      AND id > ?
+      AND NOT blacklisted
+      ORDER BY id ASC LIMIT 1", undef, $id)->[0];
+      
+}
+
+1;