Postgresql beacon receiving procedure complete and working

4 files changed, 176 insertions, 23 deletions

diff --git a/lib/MasterServer/Database/Pg/dbBeacon.pm b/lib/MasterServer/Database/Pg/dbBeacon.pm
index a234b25..e396810 100755
--- a/lib/MasterServer/Database/Pg/dbBeacon.pm
+++ b/lib/MasterServer/Database/Pg/dbBeacon.pm
@@ -5,7 +5,12 @@ use strict;
 use warnings;
 use Exporter 'import';
 
-our @EXPORT = qw| add_beacon get_pending_beacon remove_pending set_direct_beacon |;
+our @EXPORT = qw| add_beacon
+                  remove_pending 
+                  set_direct_beacon 
+                  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
@@ -24,7 +29,7 @@ sub add_beacon {
       undef, lc $gamename, $ip, $heartbeat);
   
   # notify
-  $self->log("updated", "beacon heartbeat for $ip:$heartbeat") if ($u > 0);
+  $self->log("update", "beacon heartbeat for $ip:$heartbeat") if ($u > 0);
   
   # if serverlist was updated return 0
   return 0 if ($u > 0);
@@ -41,7 +46,7 @@ sub add_beacon {
       undef, $beaconport, lc $gamename, $secure, $ip, $heartbeat);  
                             
   # notify
-  $self->log("updated", "beacon heartbeat $ip:$beaconport pending $gamename:$heartbeat") if ($u > 0);
+  $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);
@@ -53,7 +58,7 @@ sub add_beacon {
       undef, $ip, $beaconport, $heartbeat, lc $gamename, $secure);
                       
    # notify 
-   $self->log("added", "beacon heartbeat $ip:$beaconport pending $gamename:$heartbeat") if ($u > 0);
+   $self->log("add", "beacon heartbeat $ip:$beaconport pending $gamename:$heartbeat") if ($u > 0);
    
    # it was added to pending
    return 2 if ($u > 0);
@@ -63,20 +68,6 @@ sub add_beacon {
   return -1;
 }
 
-
-##   Get pending server by ip, beacon port.
-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];
-}
-
-
 ##  server checks out, remove entry from the pending list.
 sub remove_pending {
   my ($self, $id) = @_;
@@ -108,7 +99,7 @@ sub set_direct_beacon {
                             undef, $ip, $port);
 
   # notify
-  $self->log("updated", "$ip:$port is a direct beacon.") if ($u > 0);
+  $self->log("update", "$ip:$port is a direct beacon.") if ($u > 0);
   
   # if found, updated; done
   return 0 if ($u > 0);
@@ -120,4 +111,41 @@ sub set_direct_beacon {
 
 
 
+##   Get pending server by ip, beacon port.
+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];
+}
+
+##   Same as get_pending_beacon, but with heartbeat port as identifier
+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.
+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 SECONDS')
+             AND id > ?
+             ORDER BY id ASC LIMIT 1", undef, $id)->[0];
+}
+
+
 1;
diff --git a/lib/MasterServer/Database/Pg/dbCore.pm b/lib/MasterServer/Database/Pg/dbCore.pm
index c6e3182..f89cc68 100755
--- a/lib/MasterServer/Database/Pg/dbCore.pm
+++ b/lib/MasterServer/Database/Pg/dbCore.pm
@@ -21,7 +21,7 @@ sub database_login {
   # verify that the database connected
   if (defined $dbh) {
     # log the event
-    $self->log("database","Connected to the Postgres database.");
+    $self->log("load","Connected to the Postgres database.");
     
     # turn on error printing
     $dbh->{printerror} = 1;
diff --git a/lib/MasterServer/Database/Pg/dbServerlist.pm b/lib/MasterServer/Database/Pg/dbServerlist.pm
index 8d1a2b2..b1a787e 100755
--- a/lib/MasterServer/Database/Pg/dbServerlist.pm
+++ b/lib/MasterServer/Database/Pg/dbServerlist.pm
@@ -5,7 +5,9 @@ use strict;
 use warnings;
 use Exporter 'import';
 
-our @EXPORT = qw| add_to_serverlist |;
+our @EXPORT = qw| add_to_serverlist 
+                  update_serverlist
+                  get_next_server |;
 
 ## beacon was verified or otherwise accepted and will noe now be added to the 
 ## serverlist.
@@ -20,7 +22,7 @@ sub add_to_serverlist {
                             undef, $ip, $port);
 
   # notify
-  $self->log("updated", "$ip:$port timestamp updated") if ($u > 0);
+  $self->log("update", "$ip:$port timestamp updated") if ($u > 0);
   
   # if found, updated; done
   return 0 if ($u > 0);
@@ -31,7 +33,7 @@ sub add_to_serverlist {
                             undef, $ip, $port, $gamename, $self->ip2country($ip));
 
   # notify
-  $self->log("added", "$ip:$port added to serverlist") if ($u > 0);
+  $self->log("add", "$ip:$port added to serverlist") if ($u > 0);
   
   # return added
   return 1 if ($u > 0);
@@ -41,4 +43,63 @@ sub add_to_serverlist {
   return -1;
 }
 
+################################################################################
+##
+##   Subroutine update_serverlist
+##
+## Same as add_to_serverlist (above), but does not add the server to serverlist
+## if it does not exist in serverlist.
+##
+## Args: ip, port %info
+################################################################################
+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 was updated: $s->{hostname}") 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;
+}
+
+################################################################################
+##
+##   Subroutine get_next_server
+##
+##  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.
+##
+##  Args: $id --> id of a server address entry
+##  Returns: hash {id, ip, port} of the NEXT entry in line.
+################################################################################
+sub get_next_server {
+  my ($self, $id) = @_;
+  
+  return $self->{dbh}->selectall_arrayref(
+     "SELECT id, ip, port FROM serverlist
+      WHERE added < (NOW() - INTERVAL '15 SECONDS')
+      AND updated > (NOW() - INTERVAL '3 HOUR')
+      AND id > ?
+      AND NOT blacklisted
+      ORDER BY id ASC LIMIT 1", undef, $id)->[0];
+}
+
 1;
diff --git a/lib/MasterServer/Database/SQLite/dbCore.pm b/lib/MasterServer/Database/SQLite/dbCore.pm
new file mode 100755
index 0000000..a75f7a0
--- /dev/null
+++ b/lib/MasterServer/Database/SQLite/dbCore.pm
@@ -0,0 +1,64 @@
+
+package MasterServer::Database::SQLite::dbCore;
+
+use strict;
+use warnings;
+use Exporter 'import';
+
+our @EXPORT = qw| database_login |;
+
+################################################################################
+## database_login
+## login to the database with credentials provided in the config file.
+## returns dbh object
+################################################################################
+sub database_login {
+  my $self = shift;
+  
+    # check if database file exists
+    my $db_file = [split(':', $self->{dblogin}->[0])]->[2];
+       $db_file =~ s/dbname=//i;
+
+    unless (-e $db_file) {
+      # fatal error
+      $self->log("fatal", "Database file $db_file does not exist!");
+      
+      # end program
+      $self->halt();
+    }
+    
+  # create the dbi object
+  my $dbh = DBI->connect(@{$self->{dblogin}}, {PrintError => 0});
+  
+  # verify that the database connected
+  if (defined $dbh) {
+    # log the event
+    $self->log("database","Connected to the SQLite database.");
+    
+    # turn on error printing
+    $dbh->{printerror} = 1;
+    
+    # synchronous read/writing to the sql file OFF. That means: when the script 
+    # shuts down unexpectedly, i.e. because of power failure or a crash, changes 
+    # to the database are NOT SAVED. However, if this setting is not turned OFF, 
+    # it takes too long to write to the database, which means that new beacons, 
+    # requests and servers cannot be processed. You don't have a choice, really..
+    $dbh->do("PRAGMA synchronous = OFF");
+  
+    # 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;