revised synchronization methods, config settings and bug fixesv2.3.0

12 files changed, 288 insertions, 40 deletions

diff --git a/lib/MasterServer/Database/Pg/dbAddServers.pm b/lib/MasterServer/Database/Pg/dbAddServers.pm
index 33785da..31deb08 100755
--- a/lib/MasterServer/Database/Pg/dbAddServers.pm
+++ b/lib/MasterServer/Database/Pg/dbAddServers.pm
@@ -1,4 +1,3 @@
-
 package MasterServer::Database::Pg::dbAddServers;
 
 use strict;
@@ -26,7 +25,10 @@ sub add_server_new {
     $o{direct}   ? (  'b333ms = CAST(? AS BOOLEAN)' => $o{direct})      : (),
     $o{updated}  ? ( 'updated = to_timestamp(?)'    => $o{updated})     : (),
     $o{beacon}   ? (  'beacon = to_timestamp(?)'    => $o{beacon})      : (),
-    $o{gamename} ? ('gamename = ?'                  => lc $o{gamename}) : (),
+    
+    # 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 
@@ -37,12 +39,10 @@ sub add_server_new {
   # 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{gamename}   ? (  'gamename = ?' => lc $o{gamename}) : (),
     $o{beaconport} ? ('beaconport = ?' => $o{beaconport})  : (),
   );
 
@@ -119,7 +119,7 @@ sub syncer_add {
   # 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 * FROM serverlist 
+     "SELECT count(*) FROM serverlist 
       WHERE ip = ? 
       AND port = ?
       AND updated > to_timestamp(?)",
@@ -136,8 +136,8 @@ sub syncer_add {
       AND heartbeat = ?",
       undef, $secure, $ip, $port);  
 
-  # notify
-  $self->log("update","$ip:$port was updated by syncer") if ($u > 0);
+  # notify (debug)
+  #$self->log("update","$ip:$port was updated by syncer") if ($u > 0);
   
   # return 1 if found
   return 1 if ($u > 0);
@@ -148,8 +148,8 @@ sub syncer_add {
       SELECT ?, ?, ?, ?",
       undef, $ip, $port, lc $gamename, $secure);
                             
-   # notify
-   $self->log("add","beacon: $ip:$port was added for $gamename after sync") if ($u > 0);
+   # 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);
diff --git a/lib/MasterServer/Database/Pg/dbAppletActions.pm b/lib/MasterServer/Database/Pg/dbAppletActions.pm
new file mode 100755
index 0000000..dc7d941
--- /dev/null
+++ b/lib/MasterServer/Database/Pg/dbAppletActions.pm
@@ -0,0 +1,92 @@
+package MasterServer::Database::Pg::dbAppletActions;
+
+use strict;
+use warnings;
+use Exporter 'import';
+
+our @EXPORT = qw|  add_master_applet 
+                update_master_applet 
+                 reset_master_applets 
+             get_masterserver_applets 
+          remove_unresponsive_applets |;
+
+################################################################################
+## Add a remote master server applet 
+################################################################################
+sub add_master_applet {
+  my $self = shift;
+  my %o = @_;
+
+  my $u = $self->{dbh}->do(
+     "SELECT * FROM appletlist 
+      WHERE ip = ? 
+      AND port = ?
+      AND gamename = ?",
+      undef, $o{ip}, $o{port}, 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});
+}
+
+################################################################################
+## reset added/updated time after restart
+################################################################################
+sub reset_master_applets {
+  my $self = shift;
+  return $self->{dbh}->do("UPDATE appletlist 
+    SET   added = to_timestamp(?),
+        updated = to_timestamp(?)",
+    undef, time, time);
+}
+
+################################################################################
+## update time on master applet
+################################################################################
+sub update_master_applet {
+  my ($self, %o) = @_;
+
+  return $self->{dbh}->do("UPDATE appletlist 
+    SET updated = to_timestamp(?) 
+    WHERE ip = ?
+      AND port = ?
+      AND gamename = ?", 
+    undef, time, $o{ip}, $o{port}, lc $o{gamename});
+}
+
+################################################################################
+## get a list of master server applets that were online in the past week
+##
+################################################################################
+sub get_masterserver_applets {
+  my $self = shift;
+
+  return $self->db_all(
+     "SELECT * 
+      FROM appletlist
+      WHERE updated > to_timestamp(?)",
+      time-604800);
+}
+
+################################################################################
+## Clear out applet entries that have been unresponsive or without servers for 
+## more than a week. Servers with multiple entries only have the entry of this
+## specific gamename removed.
+################################################################################
+sub remove_unresponsive_applets {
+  my $self = shift;
+
+  # remove entries
+  my $u = $self->{dbh}->do(
+     "DELETE FROM appletlist 
+      WHERE updated < to_timestamp(?)", undef, time-604800);
+  
+  # notify 
+  $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 e2a5784..e099f88 100755
--- a/lib/MasterServer/Database/Pg/dbCiphers.pm
+++ b/lib/MasterServer/Database/Pg/dbCiphers.pm
@@ -1,15 +1,23 @@
-
 package MasterServer::Database::Pg::dbCiphers;
 
 use strict;
 use warnings;
 use Exporter 'import';
 
-our @EXPORT = qw| clear_ciphers 
+our @EXPORT = qw| check_cipher_count
+                  clear_ciphers 
                   insert_cipher 
                   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};
+}
+
+################################################################################
 ## Clear all existing ciphers from the database
 ################################################################################
 sub clear_ciphers {
@@ -43,15 +51,13 @@ sub insert_cipher {
 
 ################################################################################
 ## get the cipher, description and default port that goes with given gamename
+## returns only the first item if multiple items
 ################################################################################
 sub get_game_props {
   my ($self, $gn) = @_;
   
   # get cipher from db if gamename exists
-  return $self->{dbh}->selectall_arrayref(
-    'SELECT * FROM games WHERE gamename = ?', 
-    {Slice=>{}}, 
-    lc $gn)->[0];
+  return $self->db_all('SELECT * FROM games WHERE gamename = ?', lc $gn)->[0];
 }
 
 1;
diff --git a/lib/MasterServer/Database/Pg/dbCore.pm b/lib/MasterServer/Database/Pg/dbCore.pm
index 90899f7..0891012 100755
--- a/lib/MasterServer/Database/Pg/dbCore.pm
+++ b/lib/MasterServer/Database/Pg/dbCore.pm
@@ -1,11 +1,11 @@
-
 package MasterServer::Database::Pg::dbCore;
 
 use strict;
 use warnings;
+use POSIX qw/strftime/;
 use Exporter 'import';
 
-our @EXPORT = qw| database_login |;
+our @EXPORT = qw| database_login dump_database |;
 
 ################################################################################
 ## login to the database with credentials provided in the config file.
@@ -19,6 +19,9 @@ sub database_login {
 
   # 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
   my $dbh = DBI->connect(@{$self->{dblogin}}, {PrintError => 1});
@@ -47,4 +50,30 @@ sub database_login {
   return undef;
 }
 
+################################################################################
+## Dump the database in the data/dump folder.
+## useful for backups, historical data
+################################################################################
+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");
+}
+
+
 1;
diff --git a/lib/MasterServer/Database/Pg/dbMaintenance.pm b/lib/MasterServer/Database/Pg/dbMaintenance.pm
index 90e4d34..7a4fc23 100755
--- a/lib/MasterServer/Database/Pg/dbMaintenance.pm
+++ b/lib/MasterServer/Database/Pg/dbMaintenance.pm
@@ -5,7 +5,7 @@ use warnings;
 use Exporter 'import';
 
 our @EXPORT = qw| delete_old_pending 
-                  remove_pending |;
+                      remove_pending |;
 
 ################################################################################
 ## delete unresponsive servers from the pending list
diff --git a/lib/MasterServer/Database/Pg/dbUTServerInfo.pm b/lib/MasterServer/Database/Pg/dbUTServerInfo.pm
index 12ba05f..0bf005e 100755
--- a/lib/MasterServer/Database/Pg/dbUTServerInfo.pm
+++ b/lib/MasterServer/Database/Pg/dbUTServerInfo.pm
@@ -1,4 +1,3 @@
-
 package MasterServer::Database::Pg::dbUTServerInfo;
 
 use strict;
diff --git a/lib/MasterServer/Database/SQLite/dbAddServers.pm b/lib/MasterServer/Database/SQLite/dbAddServers.pm
index 9fccead..592ab7b 100755
--- a/lib/MasterServer/Database/SQLite/dbAddServers.pm
+++ b/lib/MasterServer/Database/SQLite/dbAddServers.pm
@@ -26,7 +26,10 @@ sub add_server_new {
     $o{direct}   ? (  'b333ms = CAST(? AS BOOLEAN)' => $o{direct})      : (),
     $o{updated}  ? ( 'updated = datetime(?, \'unixepoch\')'    => $o{updated})     : (),
     $o{beacon}   ? (  'beacon = datetime(?, \'unixepoch\')'    => $o{beacon})      : (),
-    $o{gamename} ? ('gamename = ?'                  => lc $o{gamename}) : (),
+   
+    # 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 
@@ -37,12 +40,10 @@ sub add_server_new {
   # 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{gamename}   ? (  'gamename = ?' => lc $o{gamename}) : (),
     $o{beaconport} ? ('beaconport = ?' => $o{beaconport})  : (),
   );
 
@@ -119,7 +120,7 @@ sub syncer_add {
   # 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 * FROM serverlist 
+     "SELECT count(*) FROM serverlist 
       WHERE ip = ? 
       AND port = ?
       AND updated > datetime(?, 'unixepoch')",
@@ -137,7 +138,7 @@ sub syncer_add {
       undef, $secure, $ip, $port);  
 
   # notify
-  $self->log("update","$ip:$port was updated by syncer") if ($u > 0);
+  #$self->log("update","$ip:$port was updated by syncer") if ($u > 0);
   
   # return 1 if found
   return 1 if ($u > 0);
@@ -149,7 +150,7 @@ sub syncer_add {
       undef, $ip, $port, lc $gamename, $secure);
                             
    # notify
-   $self->log("add","beacon: $ip:$port was added for $gamename after sync") if ($u > 0);
+   #$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);
diff --git a/lib/MasterServer/Database/SQLite/dbAppletActions.pm b/lib/MasterServer/Database/SQLite/dbAppletActions.pm
new file mode 100755
index 0000000..d2421fc
--- /dev/null
+++ b/lib/MasterServer/Database/SQLite/dbAppletActions.pm
@@ -0,0 +1,92 @@
+package MasterServer::Database::SQLite::dbAppletActions;
+
+use strict;
+use warnings;
+use Exporter 'import';
+
+our @EXPORT = qw|  add_master_applet 
+                update_master_applet 
+                 reset_master_applets 
+             get_masterserver_applets 
+          remove_unresponsive_applets |;
+
+################################################################################
+## Add a remote master server applet 
+################################################################################
+sub add_master_applet {
+  my $self = shift;
+  my %o = @_;
+
+  my $u = $self->{dbh}->do(
+     "SELECT * FROM appletlist 
+      WHERE ip = ? 
+      AND port = ?
+      AND gamename = ?",
+      undef, $o{ip}, $o{port}, 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});
+}
+
+################################################################################
+## reset added/updated time after restart
+################################################################################
+sub reset_master_applets {
+  my $self = shift;
+  return $self->{dbh}->do("UPDATE appletlist 
+    SET   added = datetime(?, \'unixepoch\'),
+        updated = datetime(?, \'unixepoch\')",
+    undef, time, time);
+}
+
+################################################################################
+## update time on master applet
+################################################################################
+sub update_master_applet {
+  my ($self, %o) = @_;
+
+  return $self->{dbh}->do("UPDATE appletlist 
+    SET updated = datetime(?, \'unixepoch\') 
+    WHERE ip = ?
+      AND port = ?
+      AND gamename = ?", 
+    undef, time, $o{ip}, $o{port}, lc $o{gamename});
+}
+
+################################################################################
+## get a list of master server applets that were online in the past week
+##
+################################################################################
+sub get_masterserver_applets {
+  my $self = shift;
+
+  return $self->db_all(
+     "SELECT * 
+      FROM appletlist
+      WHERE updated > datetime(?, \'unixepoch\')",
+      time-604800);
+}
+
+################################################################################
+## Clear out applet entries that have been unresponsive or without servers for 
+## more than a week. Servers with multiple entries only have the entry of this
+## specific gamename removed.
+################################################################################
+sub remove_unresponsive_applets {
+  my $self = shift;
+
+  # remove entries
+  my $u = $self->{dbh}->do(
+     "DELETE FROM appletlist 
+      WHERE updated < datetime(?, \'unixepoch\')", undef, time-604800);
+  
+  # notify 
+  $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 c98eb05..5b88944 100755
--- a/lib/MasterServer/Database/SQLite/dbCiphers.pm
+++ b/lib/MasterServer/Database/SQLite/dbCiphers.pm
@@ -1,15 +1,23 @@
-
 package MasterServer::Database::SQLite::dbCiphers;
 
 use strict;
 use warnings;
 use Exporter 'import';
 
-our @EXPORT = qw| clear_ciphers 
+our @EXPORT = qw| check_cipher_count
+                  clear_ciphers 
                   insert_cipher 
                   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};
+}
+
+################################################################################
 ## Clear all existing ciphers from the database
 ################################################################################
 sub clear_ciphers {
@@ -43,15 +51,13 @@ sub insert_cipher {
 
 ################################################################################
 ## get the cipher, description and default port that goes with given gamename
+## returns only the first item if multiple items
 ################################################################################
 sub get_game_props {
   my ($self, $gn) = @_;
   
   # get cipher from db if gamename exists
-  return $self->{dbh}->selectall_arrayref(
-    'SELECT * FROM games WHERE gamename = ?', 
-    {Slice=>{}}, 
-    lc $gn)->[0];
+  return $self->db_all('SELECT * FROM games WHERE gamename = ?', lc $gn)->[0];
 }
 
 1;
diff --git a/lib/MasterServer/Database/SQLite/dbCore.pm b/lib/MasterServer/Database/SQLite/dbCore.pm
index f58d535..0772a4e 100755
--- a/lib/MasterServer/Database/SQLite/dbCore.pm
+++ b/lib/MasterServer/Database/SQLite/dbCore.pm
@@ -1,11 +1,11 @@
-
 package MasterServer::Database::SQLite::dbCore;
 
 use strict;
 use warnings;
+use POSIX qw/strftime/;
 use Exporter 'import';
 
-our @EXPORT = qw| database_login |;
+our @EXPORT = qw| database_login dump_database |;
 
 ################################################################################
 ## login to the database with credentials provided in the config file.
@@ -19,7 +19,10 @@ sub database_login {
 
   # 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;
@@ -31,10 +34,7 @@ sub database_login {
     # end program
     $self->halt();
   }
-  
-  # inform what DB we try to load
-  # $self->log("info","Database: $db_type[1]");
-    
+
   # create the dbi object
   my $dbh = DBI->connect(@{$self->{dblogin}}, {PrintError => 1});
   
@@ -72,4 +72,28 @@ sub database_login {
   return undef;
 }
 
+################################################################################
+## Dump the database in the data/dump folder.
+## useful for backups, historical data
+################################################################################
+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
+  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]");
+}
+
 1;
diff --git a/lib/MasterServer/Database/SQLite/dbMaintenance.pm b/lib/MasterServer/Database/SQLite/dbMaintenance.pm
index 06a8db4..019f31f 100755
--- a/lib/MasterServer/Database/SQLite/dbMaintenance.pm
+++ b/lib/MasterServer/Database/SQLite/dbMaintenance.pm
@@ -5,7 +5,7 @@ use warnings;
 use Exporter 'import';
 
 our @EXPORT = qw| delete_old_pending 
-                  remove_pending |;
+                      remove_pending |;
 
 ################################################################################
 ## delete unresponsive servers from the pending list
diff --git a/lib/MasterServer/Database/SQLite/dbUTServerInfo.pm b/lib/MasterServer/Database/SQLite/dbUTServerInfo.pm
index f1577b2..119900b 100755
--- a/lib/MasterServer/Database/SQLite/dbUTServerInfo.pm
+++ b/lib/MasterServer/Database/SQLite/dbUTServerInfo.pm
@@ -1,4 +1,3 @@
-
 package MasterServer::Database::SQLite::dbUTServerInfo;
 
 use strict;