Beta with support for Pg and SQLite

4 files changed, 406 insertions, 4 deletions

diff --git a/lib/MasterServer/Database/SQLite/dbBeacon.pm b/lib/MasterServer/Database/SQLite/dbBeacon.pm
new file mode 100755
index 0000000..09eeec3
--- /dev/null
+++ b/lib/MasterServer/Database/SQLite/dbBeacon.pm
@@ -0,0 +1,203 @@
+
+package MasterServer::Database::SQLite::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   = CURRENT_TIMESTAMP,
+          updated  = CURRENT_TIMESTAMP,
+          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       = CURRENT_TIMESTAMP,
+          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 = CURRENT_TIMESTAMP
+      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  = CURRENT_TIMESTAMP,
+          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 < datetime(CURRENT_TIMESTAMP, '-15 seconds')
+      AND id > ?
+      ORDER BY id ASC LIMIT 1", 
+      undef, $id)->[0];
+}
+
+
+1;
diff --git a/lib/MasterServer/Database/SQLite/dbClientList.pm b/lib/MasterServer/Database/SQLite/dbClientList.pm
new file mode 100755
index 0000000..58c1392
--- /dev/null
+++ b/lib/MasterServer/Database/SQLite/dbClientList.pm
@@ -0,0 +1,45 @@
+
+package MasterServer::Database::SQLite::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 > datetime(CURRENT_TIMESTAMP, '-3600 seconds')
+     AND gamename = ?
+     AND NOT blacklisted", 
+     undef, lc $gamename);
+}
+
+
+1;
diff --git a/lib/MasterServer/Database/SQLite/dbCore.pm b/lib/MasterServer/Database/SQLite/dbCore.pm
index a75f7a0..27c9b35 100755
--- a/lib/MasterServer/Database/SQLite/dbCore.pm
+++ b/lib/MasterServer/Database/SQLite/dbCore.pm
@@ -8,9 +8,8 @@ use Exporter 'import';
 our @EXPORT = qw| database_login |;
 
 ################################################################################
-## database_login
 ## login to the database with credentials provided in the config file.
-## returns dbh object
+## returns dbh object or quits application on error.
 ################################################################################
 sub database_login {
   my $self = shift;
@@ -28,12 +27,12 @@ sub database_login {
     }
     
   # create the dbi object
-  my $dbh = DBI->connect(@{$self->{dblogin}}, {PrintError => 0});
+  my $dbh = DBI->connect(@{$self->{dblogin}}, {PrintError => $self->{db_print}});
   
   # verify that the database connected
   if (defined $dbh) {
     # log the event
-    $self->log("database","Connected to the SQLite database.");
+    $self->log("load","Connected to the SQLite database.");
     
     # turn on error printing
     $dbh->{printerror} = 1;
@@ -44,6 +43,9 @@ sub database_login {
     # it takes too long to write to the database, which means that new beacons, 
     # requests and servers cannot be processed. You don't have a choice, really..
     $dbh->do("PRAGMA synchronous = OFF");
+    
+    # allow the use of foreign keys (referencing)
+    $dbh->do("PRAGMA foreign_keys = ON");
   
     # return the dbi object for further use
     return $dbh;
diff --git a/lib/MasterServer/Database/SQLite/dbServerlist.pm b/lib/MasterServer/Database/SQLite/dbServerlist.pm
new file mode 100755
index 0000000..436a788
--- /dev/null
+++ b/lib/MasterServer/Database/SQLite/dbServerlist.pm
@@ -0,0 +1,152 @@
+
+package MasterServer::Database::SQLite::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  = CURRENT_TIMESTAMP
+                            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  = CURRENT_TIMESTAMP,
+              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.
+################################################################################
+sub get_next_server {
+  my ($self, $id) = @_;
+  
+  return $self->{dbh}->selectall_arrayref(
+     "SELECT id, ip, port FROM serverlist
+      WHERE added < datetime(CURRENT_TIMESTAMP, '-15 seconds')
+      AND updated > datetime(CURRENT_TIMESTAMP, '-10800 seconds')
+      AND id > ?
+      AND NOT blacklisted
+      ORDER BY id ASC LIMIT 1", undef, $id)->[0];
+}
+
+1;