diff options
Diffstat (limited to 'lib/MasterServer/Database/Pg')
| -rwxr-xr-x | lib/MasterServer/Database/Pg/dbAddServers.pm | 18 | ||||
| -rwxr-xr-x | lib/MasterServer/Database/Pg/dbAppletActions.pm | 92 | ||||
| -rwxr-xr-x | lib/MasterServer/Database/Pg/dbCiphers.pm | 18 | ||||
| -rwxr-xr-x | lib/MasterServer/Database/Pg/dbCore.pm | 33 | ||||
| -rwxr-xr-x | lib/MasterServer/Database/Pg/dbMaintenance.pm | 2 | ||||
| -rwxr-xr-x | lib/MasterServer/Database/Pg/dbUTServerInfo.pm | 1 |
6 files changed, 145 insertions, 19 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; |