From 702d1898b012d6d992d66b996508610890bf2963 Mon Sep 17 00:00:00 2001 From: Darkelarious Date: Sun, 20 Nov 2016 19:30:58 +0100 Subject: Restore SQLite support --- lib/MasterServer/Core/Core.pm | 27 ++- lib/MasterServer/Core/Version.pm | 4 +- lib/MasterServer/Database/Pg/dbAddServers.pm | 162 ++++++++++++++ lib/MasterServer/Database/Pg/dbCiphers.pm | 57 +++++ lib/MasterServer/Database/Pg/dbCore.pm | 50 +++++ lib/MasterServer/Database/Pg/dbGetServers.pm | 139 ++++++++++++ lib/MasterServer/Database/Pg/dbMaintenance.pm | 40 ++++ lib/MasterServer/Database/Pg/dbStats.pm | 117 ++++++++++ lib/MasterServer/Database/Pg/dbUTServerInfo.pm | 238 +++++++++++++++++++++ lib/MasterServer/Database/SQLite/dbAddServers.pm | 162 ++++++++++++++ lib/MasterServer/Database/SQLite/dbCiphers.pm | 57 +++++ lib/MasterServer/Database/SQLite/dbCore.pm | 75 +++++++ lib/MasterServer/Database/SQLite/dbGetServers.pm | 139 ++++++++++++ lib/MasterServer/Database/SQLite/dbMaintenance.pm | 40 ++++ lib/MasterServer/Database/SQLite/dbStats.pm | 117 ++++++++++ lib/MasterServer/Database/SQLite/dbUTServerInfo.pm | 238 +++++++++++++++++++++ lib/MasterServer/Database/dbAddServers.pm | 162 -------------- lib/MasterServer/Database/dbCiphers.pm | 57 ----- lib/MasterServer/Database/dbCore.pm | 83 ------- lib/MasterServer/Database/dbGetServers.pm | 139 ------------ lib/MasterServer/Database/dbMaintenance.pm | 40 ---- lib/MasterServer/Database/dbStats.pm | 117 ---------- lib/MasterServer/Database/dbUTServerInfo.pm | 238 --------------------- 23 files changed, 1656 insertions(+), 842 deletions(-) create mode 100755 lib/MasterServer/Database/Pg/dbAddServers.pm create mode 100755 lib/MasterServer/Database/Pg/dbCiphers.pm create mode 100755 lib/MasterServer/Database/Pg/dbCore.pm create mode 100755 lib/MasterServer/Database/Pg/dbGetServers.pm create mode 100755 lib/MasterServer/Database/Pg/dbMaintenance.pm create mode 100755 lib/MasterServer/Database/Pg/dbStats.pm create mode 100755 lib/MasterServer/Database/Pg/dbUTServerInfo.pm create mode 100755 lib/MasterServer/Database/SQLite/dbAddServers.pm create mode 100755 lib/MasterServer/Database/SQLite/dbCiphers.pm create mode 100755 lib/MasterServer/Database/SQLite/dbCore.pm create mode 100755 lib/MasterServer/Database/SQLite/dbGetServers.pm create mode 100755 lib/MasterServer/Database/SQLite/dbMaintenance.pm create mode 100755 lib/MasterServer/Database/SQLite/dbStats.pm create mode 100755 lib/MasterServer/Database/SQLite/dbUTServerInfo.pm delete mode 100755 lib/MasterServer/Database/dbAddServers.pm delete mode 100755 lib/MasterServer/Database/dbCiphers.pm delete mode 100755 lib/MasterServer/Database/dbCore.pm delete mode 100755 lib/MasterServer/Database/dbGetServers.pm delete mode 100755 lib/MasterServer/Database/dbMaintenance.pm delete mode 100755 lib/MasterServer/Database/dbStats.pm delete mode 100755 lib/MasterServer/Database/dbUTServerInfo.pm (limited to 'lib/MasterServer') diff --git a/lib/MasterServer/Core/Core.pm b/lib/MasterServer/Core/Core.pm index 3f99a3a..be0646b 100755 --- a/lib/MasterServer/Core/Core.pm +++ b/lib/MasterServer/Core/Core.pm @@ -35,15 +35,34 @@ sub halt { ################################################################################ ## Set up the database connection +## determine the type of database and load the appropriate module ################################################################################ sub select_database_type { my $self = shift; + + # read from login + my @db_type = split(':', $self->{dblogin}->[0]); - # Connect to database - $self->{dbh} = $self->database_login(); + # format supported? + if ( "Pg SQLite mysql" =~ m/$db_type[1]/i) { - # and test whether we succeeded. - $self->halt() unless (defined $self->{dbh}); + # inform us what DB we try to load + $self->log("load","Loading $db_type[1] database module."); + + # load dbd and tables/queries for this db type + MasterServer::load_recursive("MasterServer::Database::$db_type[1]"); + + # Connect to database + $self->{dbh} = $self->database_login(); + + # and test whether we succeeded. + $self->halt() unless (defined $self->{dbh}); + } + else { + # raise error and halt + $self->log("fatal", "The masterserver could not determine the chosen database type."); + $self->halt(); + } } ################################################################################ diff --git a/lib/MasterServer/Core/Version.pm b/lib/MasterServer/Core/Version.pm index bf12f21..718b8c6 100755 --- a/lib/MasterServer/Core/Version.pm +++ b/lib/MasterServer/Core/Version.pm @@ -30,13 +30,13 @@ sub version { $self->{build_type} = "333networks Masterserver-Perl pre-release"; # version - $self->{build_version} = "2.2.4"; + $self->{build_version} = "2.2.5"; # short version for uplinks $self->{short_version} = "MS-perl $self->{build_version}"; # date yyyy-mm-dd - $self->{build_date} = "2016-11-17"; + $self->{build_date} = "2016-11-19"; #author, email $self->{build_author} = "Darkelarious, darkelarious\@333networks.com"; diff --git a/lib/MasterServer/Database/Pg/dbAddServers.pm b/lib/MasterServer/Database/Pg/dbAddServers.pm new file mode 100755 index 0000000..33785da --- /dev/null +++ b/lib/MasterServer/Database/Pg/dbAddServers.pm @@ -0,0 +1,162 @@ + +package MasterServer::Database::Pg::dbAddServers; + +use strict; +use warnings; +use MasterServer::Core::Util 'sqlprint'; +use Exporter 'import'; + +our @EXPORT = qw| add_server_new + add_server_list + update_server_list + syncer_add |; + +################################################################################ +## Update an existing address or add a new address to the pending list. +## opts: direct beacon, set update, game +################################################################################ +sub add_server_new { + my $self = shift; + my %o = ( + updated => time, + @_); + + # try updating it in serverlist + my %H = ( + $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}) : (), + ); + + my($q, @p) = sqlprint("UPDATE serverlist !H + WHERE ip = ? AND port = ?", \%H, $o{ip}, $o{heartbeat}); + + my $n = $self->{dbh}->do($q, undef, @p); + + # 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}) : (), + ); + + ($q, @p) = sqlprint("UPDATE pending !H + WHERE ip = ? AND heartbeat = ?", \%H, $o{ip}, $o{heartbeat}); + + # exec query + $n = $self->{dbh}->do($q, undef, @p); + + # if beacon was updated + return 1 if ($n > 0); + + # if not found at all, add to pending + $n = $self->{dbh}->do( + "INSERT INTO pending ( + ip, + beaconport, + heartbeat, + gamename, + secure) + SELECT ?, ?, ?, ?, ?", + undef, $o{ip}, $o{beaconport}, $o{heartbeat}, lc $o{gamename}, $o{secure}); + + # it was added to pending + return 2 if ($n > 0); +} + +################################################################################ +## Update the server info in the serverlist +################################################################################ +sub update_server_list { + my $self = shift; + my %o = ( + updated => time, + @_); + + # try updating it in serverlist + my %H = ( + $o{gamename} ? ('gamename = ?' => lc $o{gamename}) : (), + $o{gamever} ? ( 'gamever = ?' => $o{gamever}) : (), + $o{hostname} ? ('hostname = ?' => $o{hostname}) : (), + $o{hostport} ? ('hostport = ?' => $o{hostport}) : (), + $o{updated} ? ( 'updated = to_timestamp(?)' => $o{updated}) : (), + ); + + my($q, @p) = sqlprint("UPDATE serverlist !H + WHERE ip = ? AND port = ?", \%H, $o{ip}, $o{port}); + + return $self->{dbh}->do($q, undef, @p); +} + +################################################################################ +## beacon was verified or otherwise accepted and will now be added to the +## serverlist. +################################################################################ +sub add_server_list { + my $self = shift; + my %o = @_; + + # insert basic data + return $self->{dbh}->do("INSERT INTO serverlist (ip, port, gamename, country) + SELECT ?, ?, ?, ?", undef, + $o{ip}, $o{port}, lc $o{gamename}, $self->ip2country($o{ip})); +} + +################################################################################ +## 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 the list AND up to date, + # acknowledge its existance but don't do anything with it + my $u = $self->{dbh}->do( + "SELECT * FROM serverlist + WHERE ip = ? + AND port = ? + AND updated > to_timestamp(?)", + undef, $ip, $port, time-7200); + + # 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") if ($u > 0); + + # return 1 if found + return 1 if ($u > 0); + + # if not found or out of date, add it to pending to be checked again + $u = $self->{dbh}->do( + "INSERT INTO pending (ip, heartbeat, gamename, secure) + SELECT ?, ?, ?, ?", + undef, $ip, $port, lc $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; +} + +1; diff --git a/lib/MasterServer/Database/Pg/dbCiphers.pm b/lib/MasterServer/Database/Pg/dbCiphers.pm new file mode 100755 index 0000000..e2a5784 --- /dev/null +++ b/lib/MasterServer/Database/Pg/dbCiphers.pm @@ -0,0 +1,57 @@ + +package MasterServer::Database::Pg::dbCiphers; + +use strict; +use warnings; +use Exporter 'import'; + +our @EXPORT = qw| clear_ciphers + insert_cipher + get_game_props |; + +################################################################################ +## Clear all existing ciphers from the database +################################################################################ +sub clear_ciphers { + my $self = shift; + + # delete ALL entries + my $u = $self->{dbh}->do("DELETE FROM games"); +} + +################################################################################ +## Insert the list of supported games and their ciphers / default ports / +## descriptions included from the data/supportedgames.pl file. +################################################################################ +sub insert_cipher { + my ($self, %opt) = @_; + + # insert a single cipher/key combo + my $u = $self->{dbh}->do( + "INSERT INTO games ( + gamename, + cipher, + description, + default_qport) + VALUES(?, ?, ?, ?)", undef, + lc $opt{gamename}, $opt{cipher}, $opt{description}, $opt{default_qport}); + return 1 if ($u and $u > 0); + + # or else report error + $self->log("error", "An error occurred adding a cipher for $opt{gamename}"); +} + +################################################################################ +## get the cipher, description and default port that goes with given gamename +################################################################################ +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]; +} + +1; diff --git a/lib/MasterServer/Database/Pg/dbCore.pm b/lib/MasterServer/Database/Pg/dbCore.pm new file mode 100755 index 0000000..90899f7 --- /dev/null +++ b/lib/MasterServer/Database/Pg/dbCore.pm @@ -0,0 +1,50 @@ + +package MasterServer::Database::Pg::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. +## +## Recommended database types: Postgresql, MySQL or SQLite. Warranty void if +## other database types are used. Use at your own risk. +################################################################################ +sub database_login { + my $self = shift; + + # get db info + my @db_type = split(':', $self->{dblogin}->[0]); + + # create the dbi object + my $dbh = DBI->connect(@{$self->{dblogin}}, {PrintError => 1}); + + # verify that the database connected + if (defined $dbh) { + + # log the event + $self->log("info","Connected to the $db_type[1] 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(); + } + + # in case of any other error, return undef. + return undef; +} + +1; diff --git a/lib/MasterServer/Database/Pg/dbGetServers.pm b/lib/MasterServer/Database/Pg/dbGetServers.pm new file mode 100755 index 0000000..a33582a --- /dev/null +++ b/lib/MasterServer/Database/Pg/dbGetServers.pm @@ -0,0 +1,139 @@ +package MasterServer::Database::Pg::dbGetServers; + +use strict; +use warnings; +use Exporter 'import'; + +our @EXPORT = qw| get_server + get_pending + get_gamenames |; + +################################################################################ +## get server details for one or multiple servers +## opts: limit, see $order +################################################################################ +sub get_server { + my $s = shift; + my %o = ( + sort => '', + @_ + ); + + my %where = ( + $o{next_id} ? ( 'id > ?' => $o{next_id}) : (), + $o{id} ? ( 'id = ?' => $o{id}) : (), + $o{ip} ? ( 'ip = ?' => $o{ip}) : (), + $o{port} ? ( 'port = ?' => $o{port}) : (), + $o{gamename} ? ( 'gamename = ?' => lc $o{gamename}) : (), + $o{gamever} ? ( 'gamever = ?' => $o{gamever}) : (), + $o{hostname} ? ( 'hostname = ?' => $o{hostname}) : (), + $o{hostport} ? ( 'hostport = ?' => $o{hostport}) : (), + $o{country} ? ( 'country = ?' => $o{country}) : (), + $o{b333ms} ? ( 'b333ms = ?' => $o{b333ms}) : (), + $o{blacklisted} ? ('blacklisted = ?' => $o{blacklisted}) : (), + $o{added} ? ( 'added < to_timestamp(?)' => (time-$o{added})) : (), + $o{beacon} ? ( 'beacon > to_timestamp(?)' => (time-$o{beacon})) : (), + $o{updated} ? ('updated > to_timestamp(?)' => (time-$o{updated})) : (), + $o{before} ? ('updated < to_timestamp(?)' => (time-$o{before})) : (), + ); + + my @select = ( qw| + id + ip + port + gamename + gamever + hostname + hostport + country + b333ms + blacklisted + added + beacon + updated + |); + + my $order = sprintf { + id => 'id %s', + ip => 'ip %s', + port => 'port %s', + gamename => 'gamename %s', + gamever => 'gamever %s', + hostname => 'hostname %s', + hostport => 'hostport %s', + country => 'country %s', + b333ms => 'b333ms %s', + blacklisted => 'blacklisted %s', + added => 'added %s', + beacon => 'beacon %s', + updated => 'updated %s', + }->{ $o{sort}||'id' }, $o{reverse} ? 'DESC' : 'ASC'; + + return $s->db_all( q| + SELECT !s FROM serverlist + !W + ORDER BY !s| + .($o{limit} ? " LIMIT ?" : ""), + join(', ', @select), \%where, $order, ($o{limit} ? $o{limit} : ()), + ); +} + +################################################################################ +## get server details for one or multiple pending servers +## opts: limit, next_id, beaconport, heartbeat, gamename, secure, enctype, added +################################################################################ +sub get_pending { + my $s = shift; + my %o = ( + sort => '', + @_ + ); + + my %where = ( + $o{next_id} ? ( 'id > ?' => $o{next_id}) : (), + $o{id} ? ( 'id = ?' => $o{id}) : (), + $o{ip} ? ( 'ip = ?' => $o{ip}) : (), + $o{beaconport} ? ('beaconport = ?' => $o{beaconport}) : (), + $o{heartbeat} ? ( 'heartbeat = ?' => $o{heartbeat}) : (), + $o{gamename} ? ( 'gamename = ?' => lc $o{gamename}) : (), + $o{secure} ? ( 'secure = ?' => $o{secure}) : (), + $o{enctype} ? ( 'enctype = ?' => $o{enctype}) : (), + $o{added} ? ('added < to_timestamp(?)' => (time-$o{added})) : (), + $o{after} ? ('added > to_timestamp(?)' => (time-$o{after})) : (), + ); + + my @select = ( qw| id ip beaconport heartbeat gamename secure enctype added |,); + my $order = sprintf { + id => 'id %s', + ip => 'ip %s', + beaconport => 'beaconport %s', + heartbeat => 'heartbeat %s', + gamename => 'gamename %s', + secure => 'secure %s', + enctype => 'enctype %s', + added => 'added %s', + }->{ $o{sort}||'id' }, $o{reverse} ? 'DESC' : 'ASC'; + + return $s->db_all( q| + SELECT !s FROM pending + !W + ORDER BY !s| + .($o{limit} ? " LIMIT ?" : ""), + join(', ', @select), \%where, $order, ($o{limit} ? $o{limit} : ()), + ); +} + +################################################################################ +## 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. +################################################################################ +sub get_gamenames { + my $self = shift; + + return $self->{dbh}->selectall_arrayref( + "SELECT distinct gamename + FROM serverlist"); +} + +1; diff --git a/lib/MasterServer/Database/Pg/dbMaintenance.pm b/lib/MasterServer/Database/Pg/dbMaintenance.pm new file mode 100755 index 0000000..90e4d34 --- /dev/null +++ b/lib/MasterServer/Database/Pg/dbMaintenance.pm @@ -0,0 +1,40 @@ +package MasterServer::Database::Pg::dbMaintenance; + +use strict; +use warnings; +use Exporter 'import'; + +our @EXPORT = qw| delete_old_pending + remove_pending |; + +################################################################################ +## delete unresponsive servers from the pending list +## where the server is unresponsive for more than 1 hour +################################################################################ +sub delete_old_pending { + my ($self) = shift; + + # remove servers + my $u = $self->{dbh}->do( + "DELETE FROM pending + WHERE added < to_timestamp(?)", undef, time-3600); + + # notify + $self->log("delete", "Removed $u entries from pending.") if ($u > 0); +} + +################################################################################ +## 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); +} + +1; diff --git a/lib/MasterServer/Database/Pg/dbStats.pm b/lib/MasterServer/Database/Pg/dbStats.pm new file mode 100755 index 0000000..6e92ab6 --- /dev/null +++ b/lib/MasterServer/Database/Pg/dbStats.pm @@ -0,0 +1,117 @@ +package MasterServer::Database::Pg::dbStats; + +use strict; +use warnings; +use Exporter 'import'; + +our @EXPORT = qw| get_gamelist_stats + write_direct_beacons + write_stat + write_kfstats |; + +################################################################################ +# calculate stats for all individual games +################################################################################ +sub get_gamelist_stats { + my $self = shift; + + return $self->{dbh}->selectall_arrayref( + "SELECT DISTINCT gamename AS gamename, + COUNT(NULLIF(b333ms AND updated > to_timestamp(?), FALSE)) AS numdirect, + COUNT(NULLIF(updated > to_timestamp(?), FALSE)) AS numtotal + FROM serverlist + GROUP BY gamename", undef, time-7200, time-7200); +} + +################################################################################ +# Determine from the last beacon whether the server is still uplinking +# directly to us, or whether it stopped uplinking and is now artificially +# kept in the database. +################################################################################ +sub write_direct_beacons { + my $self = shift; + my $u = $self->{dbh}->do( + "UPDATE serverlist + SET b333ms = CAST(0 AS BOOLEAN) + WHERE beacon < to_timestamp(?) AND b333ms", + undef, time-3600); + + # notify + $self->log("unset", "Lost $u direct beacons.") if ($u > 0); +} + +################################################################################ +# Write the stats to the games table +# A stat can not exist without existing gamename. Was inserted by cipher loader. +################################################################################ +sub write_stat { + my ($self, %opt) = @_; + + # if it is already in the pending list, update it with a new challenge + my $u = $self->{dbh}->do( + "UPDATE games + SET num_uplink = ?, + num_total = ? + WHERE gamename = ?", + undef, $opt{num_uplink}, $opt{num_total}, lc $opt{gamename}); + + # notify + $self->log("update", "Updated stats for $opt{gamename}.") if ($u > 0); + +} + +################################################################################ +## Write the KFStats to the database +################################################################################ +sub write_kfstats { + my ($self, $h) = @_; + + # check if entry already excists. + my $u = $self->{dbh}->selectall_arrayref( + "SELECT * FROM kfstats WHERE UTkey = ? ", undef, $h->{UTkey}); + + if ( !defined $u->[0] ) { + $u = $self->{dbh}->do( + "INSERT INTO kfstats (UTkey, Username) VALUES (?,?)", + undef, $h->{UTkey}, $h->{Username}); + + # notify + $self->log("kfnew", "New KF Player $h->{Username} added"); + } + + # update existing information + $u = $self->{dbh}->do("UPDATE kfstats SET + Username = ?, + CurrentVeterancy = ?, + TotalKills = ?, + DecaptedKills = ?, + TotalMeleeDamage = ?, + MeleeKills = ?, + PowerWpnKills = ?, + BullpupDamage = ?, + StalkerKills = ?, + TotalWelded = ?, + TotalHealed = ?, + TotalPlaytime =?, + GamesWon = ?, + GamesLost = ? + WHERE UTkey = ?", undef, + $h->{Username}, + $h->{CurrentVeterancy}, + $h->{TotalKills}, + $h->{DecaptedKills}, + $h->{TotalMeleeDamage}, + $h->{MeleeKills}, + $h->{PowerWpnKills}, + $h->{BullpupDamage}, + $h->{StalkerKills}, + $h->{TotalWelded}, + $h->{TotalHealed}, + $h->{TotalPlaytime}, + $h->{GamesWon}, + $h->{GamesLost}, + $h->{UTkey} + ); +} + +1; diff --git a/lib/MasterServer/Database/Pg/dbUTServerInfo.pm b/lib/MasterServer/Database/Pg/dbUTServerInfo.pm new file mode 100755 index 0000000..12ba05f --- /dev/null +++ b/lib/MasterServer/Database/Pg/dbUTServerInfo.pm @@ -0,0 +1,238 @@ + +package MasterServer::Database::Pg::dbUTServerInfo; + +use strict; +use warnings; +use MasterServer::Core::Util 'sqlprint'; +use Exporter 'import'; + +our @EXPORT = qw| get_utserver + add_utserver + update_utserver + delete_utplayers + insert_utplayer |; + +################################################################################ +## get server details for one or multiple UT servers +## opts: limit, see $order +################################################################################ +sub get_utserver { + my $s = shift; + my %o = ( + sort => '', + @_ + ); + + my %where = ( + $o{id} ? ('server_id = ?' => $o{id}) : (), + $o{minnetver} ? ('minnetver = ?' => $o{minnetver}) : (), + $o{gamever} ? ('gamever = ?' => $o{gamever}) : (), + $o{location} ? ('location = ?' => $o{location}) : (), + $o{listenserver} ? ('listenserver = ?' => $o{listenserver}) : (), + $o{hostport} ? ('hostport = ?' => $o{hostport}) : (), + $o{hostname} ? ('hostname = ?' => $o{hostname}) : (), + $o{adminname} ? ('adminname = ?' => $o{adminname}) : (), + $o{adminemail} ? ('adminemail = ?' => $o{adminemail}) : (), + $o{password} ? ('password = ?' => $o{password}) : (), + $o{gametype} ? ('gametype = ?' => $o{gametype}) : (), + $o{gamestyle} ? ('gamestyle = ?' => $o{gamestyle}) : (), + $o{changelevels} ? ('changelevels = ?' => $o{changelevels}) : (), + $o{maptitle} ? ('maptitle = ?' => $o{maptitle}) : (), + $o{mapname} ? ('mapname = ?' => $o{mapname}) : (), + $o{numplayers} ? ('numplayers = ?' => $o{numplayers}) : (), + $o{maxplayers} ? ('maxplayers = ?' => $o{maxplayers}) : (), + $o{minplayers} ? ('minplayers = ?' => $o{minplayers}) : (), + $o{botskill} ? ('botskill = ?' => $o{botskill}) : (), + $o{balanceteams} ? ('balanceteams = ?' => $o{balanceteams}) : (), + $o{playersbalanceteams} ? ('playersbalanceteams = ?' => $o{playersbalanceteams}) : (), + $o{friendlyfire} ? ('friendlyfire = ?' => $o{friendlyfire}) : (), + $o{maxteams} ? ('maxteams = ?' => $o{maxteams}) : (), + $o{timelimit} ? ('timelimit = ?' => $o{timelimit}) : (), + $o{goalteamscore} ? ('goalteamscore = ?' => $o{goalteamscore}) : (), + $o{fraglimit} ? ('fraglimit = ?' => $o{fraglimit}) : (), + $o{mutators} ? ('hostname ILIKE ?' => "%$o{mutators}%") : (), + $o{updated} ? ('updated > to_timestamp(?)'=> (time-$o{updated})) : (), + ); + + my @select = ( qw| + server_id + minnetver + gamever + location + listenserver + hostport + hostname + adminname + adminemail + password + gametype + gamestyle + changelevels + maptitle + mapname + numplayers + maxplayers + minplayers + botskill + balanceteams + playersbalanceteams + friendlyfire + maxteams + timelimit + goalteamscore + fraglimit + mutators + updated + |); + + my $order = sprintf { + server_id => 'server_id %s', + minnetver => 'minnetver %s', + gamever => 'gamever %s', + location => 'location %s', + listenserver => 'listenserver %s', + hostport => 'hostport %s', + hostname => 'hostname %s', + adminname => 'adminname %s', + adminemail => 'adminemail %s', + password => 'password %s', + gametype => 'gametype %s', + gamestyle => 'gamestyle %s', + changelevels => 'changelevels %s', + maptitle => 'maptitle %s', + mapname => 'mapname %s', + numplayers => 'numplayers %s', + maxplayers => 'maxplayers %s', + minplayers => 'minplayers %s', + botskill => 'botskill %s', + balanceteams => 'balanceteams %s', + playersbalanceteams => 'playersbalanceteams %s', + friendlyfire => 'friendlyfire %s', + maxteams => 'maxteams %s', + timelimit => 'timelimit %s', + goalteamscore => 'goalteamscore %s', + fraglimit => 'fraglimit %s', + mutators => 'mutators %s', + updated => 'updated %s', + }->{ $o{sort}||'server_id' }, $o{reverse} ? 'DESC' : 'ASC'; + + return $s->db_all( q| + SELECT !s FROM utserver_info + !W + ORDER BY !s| + .($o{limit} ? " LIMIT ?" : ""), + join(', ', @select), \%where, $order, ($o{limit} ? $o{limit} : ()), + ); +} + + +################################################################################ +## Update serverinfo for an existing address to the utserver list. +## opts: all server info data fields. +################################################################################ +sub update_utserver { + my $self = shift; + my $id = shift; + my %s = ( + # defaults + updated => time, + @_); + + # try updating it in serverlist + my %H = ( + $s{minnetver} ? ( 'minnetver = ?' => $s{minnetver} ) : (), + $s{gamever} ? ( 'gamever = ?' => int( $s{gamever}) ) : (), + $s{location} ? ( 'location = ?' => $s{location} ) : (), + $s{listenserver} ? ( 'listenserver = ?' => ( $s{listenserver} ? 1 : 0) ) : (), + $s{hostport} ? ( 'hostport = ?' => $s{hostport}) : (), + $s{hostname} ? ( 'hostname = ?' => $s{hostname}) : (), + $s{AdminName} ? ( 'adminname = ?' => $s{AdminName}) : (), + $s{AdminEMail} ? ( 'adminemail = ?' => $s{AdminEMail}) : (), + $s{password} ? ( 'password = ?' => ( $s{password} ? 1 : 0) ) : (), + $s{gametype} ? ( 'gametype = ?' => $s{gametype}) : (), + $s{gamestyle} ? ( 'gamestyle = ?' => $s{gamestyle}) : (), + $s{changelevels} ? ( 'changelevels = ?' => ( $s{changelevels} ? 1 : 0) ) : (), + $s{maptitle} ? ( 'maptitle = ?' => $s{maptitle}) : (), + $s{mapname} ? ( 'mapname = ?' => $s{mapname}) : (), + $s{numplayers} ? ( 'numplayers = ?' => $s{numplayers}) : ('numplayers = ?' => 0), + $s{maxplayers} ? ( 'maxplayers = ?' => $s{maxplayers}) : ('maxplayers = ?' => 0), + $s{minplayers} ? ( 'minplayers = ?' => $s{minplayers}) : ('minplayers = ?' => 0), + $s{botskill} ? ( 'botskill = ?' => $s{botskill}) : (), + $s{balanceteams} ? ( 'balanceteams = ?' => ( $s{balanceteams} ? 1 : 0) ) : (), + $s{playersbalanceteams} ? ( 'playersbalanceteams = ?' => ( $s{playersbalanceteams} ? 1 : 0) ) : (), + $s{friendlyfire} ? ( 'friendlyfire = ?' => $s{friendlyfire}) : (), + $s{maxteams} ? ( 'maxteams = ?' => $s{maxteams}) : (), + $s{timelimit} ? ( 'timelimit = ?' => $s{timelimit}) : (), + $s{goalteamscore} ? ( 'goalteamscore = ?' => int( $s{goalteamscore}) ) : (), + $s{fraglimit} ? ( 'fraglimit = ?' => int( $s{fraglimit}) ) : (), + $s{mutators} ? ( 'mutators = ?' => $s{mutators}) : ('mutators = ?' => "None"), + $s{updated} ? ('updated = to_timestamp(?)' => $s{updated}) : (), + ); + + my($q, @p) = sqlprint("UPDATE utserver_info !H WHERE server_id = ?", \%H, $id); + return $self->{dbh}->do($q, undef, @p); +} + + +################################################################################ +## Add a new utserver and trigger the update routine above. +## opts: id, server info data +################################################################################ +sub add_utserver { + my ($self, $ip, $port) = @_; + + # create new entry + return $self->{dbh}->do( + "INSERT INTO utserver_info (server_id) + SELECT (SELECT id FROM serverlist WHERE ip = ? AND port = ?)", + undef, $ip, $port); +} + + +################################################################################ +## Delete all players from a certain server ID +## opts: server id +################################################################################ +sub delete_utplayers { + my ($self, $sid) = @_; + + # delete players for server_id + return $self->{dbh}->do( + "DELETE FROM utplayer_info WHERE server_id = ?", + undef, $sid); +} + +################################################################################ +## Insert player info for a single player in server sid +## opts: server id, player info +################################################################################ +sub insert_utplayer { + my $self = shift; + my $sid = shift; + my %s = ( + updated => time, + @_); + + # apparently useless chunk of code + # FIXME move to site part + my %H = ( + $s{server_id} ? ( 'server_id = ?' => $s{server_id}) : (), + $s{player} ? ( 'player = ?' => $s{player}) : (), + $s{team} ? ( 'team = ?' => int( $s{team})) : (), + $s{frags} ? ( 'frags = ?' => int( $s{frags})) : (), + $s{mesh} ? ( 'mesh = ?' => $s{mesh}) : (), + $s{skin} ? ( 'skin = ?' => $s{skin}) : (), + $s{face} ? ( 'face = ?' => $s{face}) : (), + $s{ping} ? ( 'ping = ?' => int( $s{ping})) : (), + $s{ngsecret} ? ( 'ngsecret = ?' => $s{ngsecret}) : (), + $s{updated} ? ('updated = to_timestamp(?)' => $s{updated}) : (), + ); + + # insert + return $self->{dbh}->do( + "INSERT INTO utplayer_info (server_id, player, team, frags, mesh, skin, face, ping, ngsecret) + VALUES (?,?,?,?,?,?,?,?,?)", + undef, $sid, $s{player}, $s{team}, $s{frags}, $s{mesh}, $s{skin}, $s{face}, $s{ping}, $s{ngsecret}); +} + +1; diff --git a/lib/MasterServer/Database/SQLite/dbAddServers.pm b/lib/MasterServer/Database/SQLite/dbAddServers.pm new file mode 100755 index 0000000..9fccead --- /dev/null +++ b/lib/MasterServer/Database/SQLite/dbAddServers.pm @@ -0,0 +1,162 @@ + +package MasterServer::Database::SQLite::dbAddServers; + +use strict; +use warnings; +use MasterServer::Core::Util 'sqlprint'; +use Exporter 'import'; + +our @EXPORT = qw| add_server_new + add_server_list + update_server_list + syncer_add |; + +################################################################################ +## Update an existing address or add a new address to the pending list. +## opts: direct beacon, set update, game +################################################################################ +sub add_server_new { + my $self = shift; + my %o = ( + updated => time, + @_); + + # try updating it in serverlist + my %H = ( + $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}) : (), + ); + + my($q, @p) = sqlprint("UPDATE serverlist !H + WHERE ip = ? AND port = ?", \%H, $o{ip}, $o{heartbeat}); + + my $n = $self->{dbh}->do($q, undef, @p); + + # 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}) : (), + ); + + ($q, @p) = sqlprint("UPDATE pending !H + WHERE ip = ? AND heartbeat = ?", \%H, $o{ip}, $o{heartbeat}); + + # exec query + $n = $self->{dbh}->do($q, undef, @p); + + # if beacon was updated + return 1 if ($n > 0); + + # if not found at all, add to pending + $n = $self->{dbh}->do( + "INSERT INTO pending ( + ip, + beaconport, + heartbeat, + gamename, + secure) + SELECT ?, ?, ?, ?, ?", + undef, $o{ip}, $o{beaconport}, $o{heartbeat}, lc $o{gamename}, $o{secure}); + + # it was added to pending + return 2 if ($n > 0); +} + +################################################################################ +## Update the server info in the serverlist +################################################################################ +sub update_server_list { + my $self = shift; + my %o = ( + updated => time, + @_); + + # try updating it in serverlist + my %H = ( + $o{gamename} ? ('gamename = ?' => lc $o{gamename}) : (), + $o{gamever} ? ( 'gamever = ?' => $o{gamever}) : (), + $o{hostname} ? ('hostname = ?' => $o{hostname}) : (), + $o{hostport} ? ('hostport = ?' => $o{hostport}) : (), + $o{updated} ? ( 'updated = datetime(?, \'unixepoch\')' => $o{updated}) : (), + ); + + my($q, @p) = sqlprint("UPDATE serverlist !H + WHERE ip = ? AND port = ?", \%H, $o{ip}, $o{port}); + + return $self->{dbh}->do($q, undef, @p); +} + +################################################################################ +## beacon was verified or otherwise accepted and will now be added to the +## serverlist. +################################################################################ +sub add_server_list { + my $self = shift; + my %o = @_; + + # insert basic data + return $self->{dbh}->do("INSERT INTO serverlist (ip, port, gamename, country) + SELECT ?, ?, ?, ?", undef, + $o{ip}, $o{port}, lc $o{gamename}, $self->ip2country($o{ip})); +} + +################################################################################ +## 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 the list AND up to date, + # acknowledge its existance but don't do anything with it + my $u = $self->{dbh}->do( + "SELECT * FROM serverlist + WHERE ip = ? + AND port = ? + AND updated > datetime(?, 'unixepoch')", + undef, $ip, $port, time-7200); + + # 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") if ($u > 0); + + # return 1 if found + return 1 if ($u > 0); + + # if not found or out of date, add it to pending to be checked again + $u = $self->{dbh}->do( + "INSERT INTO pending (ip, heartbeat, gamename, secure) + SELECT ?, ?, ?, ?", + undef, $ip, $port, lc $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; +} + +1; diff --git a/lib/MasterServer/Database/SQLite/dbCiphers.pm b/lib/MasterServer/Database/SQLite/dbCiphers.pm new file mode 100755 index 0000000..c98eb05 --- /dev/null +++ b/lib/MasterServer/Database/SQLite/dbCiphers.pm @@ -0,0 +1,57 @@ + +package MasterServer::Database::SQLite::dbCiphers; + +use strict; +use warnings; +use Exporter 'import'; + +our @EXPORT = qw| clear_ciphers + insert_cipher + get_game_props |; + +################################################################################ +## Clear all existing ciphers from the database +################################################################################ +sub clear_ciphers { + my $self = shift; + + # delete ALL entries + my $u = $self->{dbh}->do("DELETE FROM games"); +} + +################################################################################ +## Insert the list of supported games and their ciphers / default ports / +## descriptions included from the data/supportedgames.pl file. +################################################################################ +sub insert_cipher { + my ($self, %opt) = @_; + + # insert a single cipher/key combo + my $u = $self->{dbh}->do( + "INSERT INTO games ( + gamename, + cipher, + description, + default_qport) + VALUES(?, ?, ?, ?)", undef, + lc $opt{gamename}, $opt{cipher}, $opt{description}, $opt{default_qport}); + return 1 if ($u and $u > 0); + + # or else report error + $self->log("error", "An error occurred adding a cipher for $opt{gamename}"); +} + +################################################################################ +## get the cipher, description and default port that goes with given gamename +################################################################################ +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]; +} + +1; diff --git a/lib/MasterServer/Database/SQLite/dbCore.pm b/lib/MasterServer/Database/SQLite/dbCore.pm new file mode 100755 index 0000000..f58d535 --- /dev/null +++ b/lib/MasterServer/Database/SQLite/dbCore.pm @@ -0,0 +1,75 @@ + +package MasterServer::Database::SQLite::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. +## +## Recommended database types: Postgresql, MySQL or SQLite. Warranty void if +## other database types are used. Use at your own risk. +################################################################################ +sub database_login { + my $self = shift; + + # get db info + my @db_type = split(':', $self->{dblogin}->[0]); + + # 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(); + } + + # 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}); + + # verify that the database connected + if (defined $dbh) { + + # log the event + $self->log("info","Connected to the $db_type[1] database."); + + # turn on error printing + $dbh->{printerror} = 1; + + # synchronous read/writing to the SQLite 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"); + + # allow the use of foreign keys (referencing) + $dbh->do("PRAGMA foreign_keys = ON"); + + # return the dbi object for further use + return $dbh; + } + else { + # fatal error + $self->log("fatal", "$DBI::errstr!"); + + # end program + $self->halt(); + } + + # in case of any other error, return undef. + return undef; +} + +1; diff --git a/lib/MasterServer/Database/SQLite/dbGetServers.pm b/lib/MasterServer/Database/SQLite/dbGetServers.pm new file mode 100755 index 0000000..1884601 --- /dev/null +++ b/lib/MasterServer/Database/SQLite/dbGetServers.pm @@ -0,0 +1,139 @@ +package MasterServer::Database::SQLite::dbGetServers; + +use strict; +use warnings; +use Exporter 'import'; + +our @EXPORT = qw| get_server + get_pending + get_gamenames |; + +################################################################################ +## get server details for one or multiple servers +## opts: limit, see $order +################################################################################ +sub get_server { + my $s = shift; + my %o = ( + sort => '', + @_ + ); + + my %where = ( + $o{next_id} ? ( 'id > ?' => $o{next_id}) : (), + $o{id} ? ( 'id = ?' => $o{id}) : (), + $o{ip} ? ( 'ip = ?' => $o{ip}) : (), + $o{port} ? ( 'port = ?' => $o{port}) : (), + $o{gamename} ? ( 'gamename = ?' => lc $o{gamename}) : (), + $o{gamever} ? ( 'gamever = ?' => $o{gamever}) : (), + $o{hostname} ? ( 'hostname = ?' => $o{hostname}) : (), + $o{hostport} ? ( 'hostport = ?' => $o{hostport}) : (), + $o{country} ? ( 'country = ?' => $o{country}) : (), + $o{b333ms} ? ( 'b333ms = ?' => $o{b333ms}) : (), + $o{blacklisted} ? ('blacklisted = ?' => $o{blacklisted}) : (), + $o{added} ? ( 'added < datetime(?, \'unixepoch\')' => (time-$o{added})) : (), + $o{beacon} ? ( 'beacon > datetime(?, \'unixepoch\')' => (time-$o{beacon})) : (), + $o{updated} ? ('updated > datetime(?, \'unixepoch\')' => (time-$o{updated})) : (), + $o{before} ? ('updated < datetime(?, \'unixepoch\')' => (time-$o{before})) : (), + ); + + my @select = ( qw| + id + ip + port + gamename + gamever + hostname + hostport + country + b333ms + blacklisted + added + beacon + updated + |); + + my $order = sprintf { + id => 'id %s', + ip => 'ip %s', + port => 'port %s', + gamename => 'gamename %s', + gamever => 'gamever %s', + hostname => 'hostname %s', + hostport => 'hostport %s', + country => 'country %s', + b333ms => 'b333ms %s', + blacklisted => 'blacklisted %s', + added => 'added %s', + beacon => 'beacon %s', + updated => 'updated %s', + }->{ $o{sort}||'id' }, $o{reverse} ? 'DESC' : 'ASC'; + + return $s->db_all( q| + SELECT !s FROM serverlist + !W + ORDER BY !s| + .($o{limit} ? " LIMIT ?" : ""), + join(', ', @select), \%where, $order, ($o{limit} ? $o{limit} : ()), + ); +} + +################################################################################ +## get server details for one or multiple pending servers +## opts: limit, next_id, beaconport, heartbeat, gamename, secure, enctype, added +################################################################################ +sub get_pending { + my $s = shift; + my %o = ( + sort => '', + @_ + ); + + my %where = ( + $o{next_id} ? ( 'id > ?' => $o{next_id}) : (), + $o{id} ? ( 'id = ?' => $o{id}) : (), + $o{ip} ? ( 'ip = ?' => $o{ip}) : (), + $o{beaconport} ? ('beaconport = ?' => $o{beaconport}) : (), + $o{heartbeat} ? ( 'heartbeat = ?' => $o{heartbeat}) : (), + $o{gamename} ? ( 'gamename = ?' => lc $o{gamename}) : (), + $o{secure} ? ( 'secure = ?' => $o{secure}) : (), + $o{enctype} ? ( 'enctype = ?' => $o{enctype}) : (), + $o{added} ? ('added < datetime(?, \'unixepoch\')' => (time-$o{added})) : (), + $o{after} ? ('added > datetime(?, \'unixepoch\')' => (time-$o{after})) : (), + ); + + my @select = ( qw| id ip beaconport heartbeat gamename secure enctype added |,); + my $order = sprintf { + id => 'id %s', + ip => 'ip %s', + beaconport => 'beaconport %s', + heartbeat => 'heartbeat %s', + gamename => 'gamename %s', + secure => 'secure %s', + enctype => 'enctype %s', + added => 'added %s', + }->{ $o{sort}||'id' }, $o{reverse} ? 'DESC' : 'ASC'; + + return $s->db_all( q| + SELECT !s FROM pending + !W + ORDER BY !s| + .($o{limit} ? " LIMIT ?" : ""), + join(', ', @select), \%where, $order, ($o{limit} ? $o{limit} : ()), + ); +} + +################################################################################ +## 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. +################################################################################ +sub get_gamenames { + my $self = shift; + + return $self->{dbh}->selectall_arrayref( + "SELECT distinct gamename + FROM serverlist"); +} + +1; diff --git a/lib/MasterServer/Database/SQLite/dbMaintenance.pm b/lib/MasterServer/Database/SQLite/dbMaintenance.pm new file mode 100755 index 0000000..06a8db4 --- /dev/null +++ b/lib/MasterServer/Database/SQLite/dbMaintenance.pm @@ -0,0 +1,40 @@ +package MasterServer::Database::SQLite::dbMaintenance; + +use strict; +use warnings; +use Exporter 'import'; + +our @EXPORT = qw| delete_old_pending + remove_pending |; + +################################################################################ +## delete unresponsive servers from the pending list +## where the server is unresponsive for more than 1 hour +################################################################################ +sub delete_old_pending { + my ($self) = shift; + + # remove servers + my $u = $self->{dbh}->do( + "DELETE FROM pending + WHERE added < datetime(?, 'unixepoch')", undef, time-3600); + + # notify + $self->log("delete", "Removed $u entries from pending.") if ($u > 0); +} + +################################################################################ +## 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); +} + +1; diff --git a/lib/MasterServer/Database/SQLite/dbStats.pm b/lib/MasterServer/Database/SQLite/dbStats.pm new file mode 100755 index 0000000..1cf94e6 --- /dev/null +++ b/lib/MasterServer/Database/SQLite/dbStats.pm @@ -0,0 +1,117 @@ +package MasterServer::Database::SQLite::dbStats; + +use strict; +use warnings; +use Exporter 'import'; + +our @EXPORT = qw| get_gamelist_stats + write_direct_beacons + write_stat + write_kfstats |; + +################################################################################ +# calculate stats for all individual games +################################################################################ +sub get_gamelist_stats { + my $self = shift; + + return $self->{dbh}->selectall_arrayref( + "SELECT DISTINCT gamename AS gamename, + COUNT(NULLIF(b333ms AND updated > datetime(?, 'unixepoch'), 0)) AS numdirect, + COUNT(NULLIF(updated > datetime(?, 'unixepoch'), 0)) AS numtotal + FROM serverlist + GROUP BY gamename", undef, time-7200, time-7200); +} + +################################################################################ +# Determine from the last beacon whether the server is still uplinking +# directly to us, or whether it stopped uplinking and is now artificially +# kept in the database. +################################################################################ +sub write_direct_beacons { + my $self = shift; + my $u = $self->{dbh}->do( + "UPDATE serverlist + SET b333ms = 0 + WHERE beacon < datetime(?, 'unixepoch') AND b333ms", + undef, time-3600); + + # notify + $self->log("unset", "Lost $u direct beacons.") if ($u > 0); +} + +################################################################################ +# Write the stats to the games table +# A stat can not exist without existing gamename. Was inserted by cipher loader. +################################################################################ +sub write_stat { + my ($self, %opt) = @_; + + # if it is already in the pending list, update it with a new challenge + my $u = $self->{dbh}->do( + "UPDATE games + SET num_uplink = ?, + num_total = ? + WHERE gamename = ?", + undef, $opt{num_uplink}, $opt{num_total}, lc $opt{gamename}); + + # notify + $self->log("update", "Updated stats for $opt{gamename}.") if ($u > 0); + +} + +################################################################################ +## Write the KFStats to the database +################################################################################ +sub write_kfstats { + my ($self, $h) = @_; + + # check if entry already excists. + my $u = $self->{dbh}->selectall_arrayref( + "SELECT * FROM kfstats WHERE UTkey = ? ", undef, $h->{UTkey}); + + if ( !defined $u->[0] ) { + $u = $self->{dbh}->do( + "INSERT INTO kfstats (UTkey, Username) VALUES (?,?)", + undef, $h->{UTkey}, $h->{Username}); + + # notify + $self->log("kfnew", "New KF Player $h->{Username} added"); + } + + # update existing information + $u = $self->{dbh}->do("UPDATE kfstats SET + Username = ?, + CurrentVeterancy = ?, + TotalKills = ?, + DecaptedKills = ?, + TotalMeleeDamage = ?, + MeleeKills = ?, + PowerWpnKills = ?, + BullpupDamage = ?, + StalkerKills = ?, + TotalWelded = ?, + TotalHealed = ?, + TotalPlaytime =?, + GamesWon = ?, + GamesLost = ? + WHERE UTkey = ?", undef, + $h->{Username}, + $h->{CurrentVeterancy}, + $h->{TotalKills}, + $h->{DecaptedKills}, + $h->{TotalMeleeDamage}, + $h->{MeleeKills}, + $h->{PowerWpnKills}, + $h->{BullpupDamage}, + $h->{StalkerKills}, + $h->{TotalWelded}, + $h->{TotalHealed}, + $h->{TotalPlaytime}, + $h->{GamesWon}, + $h->{GamesLost}, + $h->{UTkey} + ); +} + +1; diff --git a/lib/MasterServer/Database/SQLite/dbUTServerInfo.pm b/lib/MasterServer/Database/SQLite/dbUTServerInfo.pm new file mode 100755 index 0000000..f1577b2 --- /dev/null +++ b/lib/MasterServer/Database/SQLite/dbUTServerInfo.pm @@ -0,0 +1,238 @@ + +package MasterServer::Database::SQLite::dbUTServerInfo; + +use strict; +use warnings; +use MasterServer::Core::Util 'sqlprint'; +use Exporter 'import'; + +our @EXPORT = qw| get_utserver + add_utserver + update_utserver + delete_utplayers + insert_utplayer |; + +################################################################################ +## get server details for one or multiple UT servers +## opts: limit, see $order +################################################################################ +sub get_utserver { + my $s = shift; + my %o = ( + sort => '', + @_ + ); + + my %where = ( + $o{id} ? ('server_id = ?' => $o{id}) : (), + $o{minnetver} ? ('minnetver = ?' => $o{minnetver}) : (), + $o{gamever} ? ('gamever = ?' => $o{gamever}) : (), + $o{location} ? ('location = ?' => $o{location}) : (), + $o{listenserver} ? ('listenserver = ?' => $o{listenserver}) : (), + $o{hostport} ? ('hostport = ?' => $o{hostport}) : (), + $o{hostname} ? ('hostname = ?' => $o{hostname}) : (), + $o{adminname} ? ('adminname = ?' => $o{adminname}) : (), + $o{adminemail} ? ('adminemail = ?' => $o{adminemail}) : (), + $o{password} ? ('password = ?' => $o{password}) : (), + $o{gametype} ? ('gametype = ?' => $o{gametype}) : (), + $o{gamestyle} ? ('gamestyle = ?' => $o{gamestyle}) : (), + $o{changelevels} ? ('changelevels = ?' => $o{changelevels}) : (), + $o{maptitle} ? ('maptitle = ?' => $o{maptitle}) : (), + $o{mapname} ? ('mapname = ?' => $o{mapname}) : (), + $o{numplayers} ? ('numplayers = ?' => $o{numplayers}) : (), + $o{maxplayers} ? ('maxplayers = ?' => $o{maxplayers}) : (), + $o{minplayers} ? ('minplayers = ?' => $o{minplayers}) : (), + $o{botskill} ? ('botskill = ?' => $o{botskill}) : (), + $o{balanceteams} ? ('balanceteams = ?' => $o{balanceteams}) : (), + $o{playersbalanceteams} ? ('playersbalanceteams = ?' => $o{playersbalanceteams}) : (), + $o{friendlyfire} ? ('friendlyfire = ?' => $o{friendlyfire}) : (), + $o{maxteams} ? ('maxteams = ?' => $o{maxteams}) : (), + $o{timelimit} ? ('timelimit = ?' => $o{timelimit}) : (), + $o{goalteamscore} ? ('goalteamscore = ?' => $o{goalteamscore}) : (), + $o{fraglimit} ? ('fraglimit = ?' => $o{fraglimit}) : (), + $o{mutators} ? ('hostname ILIKE ?' => "%$o{mutators}%") : (), + $o{updated} ? ('updated > datetime(?, \'unixepoch\')'=> (time-$o{updated})) : (), + ); + + my @select = ( qw| + server_id + minnetver + gamever + location + listenserver + hostport + hostname + adminname + adminemail + password + gametype + gamestyle + changelevels + maptitle + mapname + numplayers + maxplayers + minplayers + botskill + balanceteams + playersbalanceteams + friendlyfire + maxteams + timelimit + goalteamscore + fraglimit + mutators + updated + |); + + my $order = sprintf { + server_id => 'server_id %s', + minnetver => 'minnetver %s', + gamever => 'gamever %s', + location => 'location %s', + listenserver => 'listenserver %s', + hostport => 'hostport %s', + hostname => 'hostname %s', + adminname => 'adminname %s', + adminemail => 'adminemail %s', + password => 'password %s', + gametype => 'gametype %s', + gamestyle => 'gamestyle %s', + changelevels => 'changelevels %s', + maptitle => 'maptitle %s', + mapname => 'mapname %s', + numplayers => 'numplayers %s', + maxplayers => 'maxplayers %s', + minplayers => 'minplayers %s', + botskill => 'botskill %s', + balanceteams => 'balanceteams %s', + playersbalanceteams => 'playersbalanceteams %s', + friendlyfire => 'friendlyfire %s', + maxteams => 'maxteams %s', + timelimit => 'timelimit %s', + goalteamscore => 'goalteamscore %s', + fraglimit => 'fraglimit %s', + mutators => 'mutators %s', + updated => 'updated %s', + }->{ $o{sort}||'server_id' }, $o{reverse} ? 'DESC' : 'ASC'; + + return $s->db_all( q| + SELECT !s FROM utserver_info + !W + ORDER BY !s| + .($o{limit} ? " LIMIT ?" : ""), + join(', ', @select), \%where, $order, ($o{limit} ? $o{limit} : ()), + ); +} + + +################################################################################ +## Update serverinfo for an existing address to the utserver list. +## opts: all server info data fields. +################################################################################ +sub update_utserver { + my $self = shift; + my $id = shift; + my %s = ( + # defaults + updated => time, + @_); + + # try updating it in serverlist + my %H = ( + $s{minnetver} ? ( 'minnetver = ?' => $s{minnetver} ) : (), + $s{gamever} ? ( 'gamever = ?' => int( $s{gamever}) ) : (), + $s{location} ? ( 'location = ?' => $s{location} ) : (), + $s{listenserver} ? ( 'listenserver = ?' => ( $s{listenserver} ? 1 : 0) ) : (), + $s{hostport} ? ( 'hostport = ?' => $s{hostport}) : (), + $s{hostname} ? ( 'hostname = ?' => $s{hostname}) : (), + $s{AdminName} ? ( 'adminname = ?' => $s{AdminName}) : (), + $s{AdminEMail} ? ( 'adminemail = ?' => $s{AdminEMail}) : (), + $s{password} ? ( 'password = ?' => ( $s{password} ? 1 : 0) ) : (), + $s{gametype} ? ( 'gametype = ?' => $s{gametype}) : (), + $s{gamestyle} ? ( 'gamestyle = ?' => $s{gamestyle}) : (), + $s{changelevels} ? ( 'changelevels = ?' => ( $s{changelevels} ? 1 : 0) ) : (), + $s{maptitle} ? ( 'maptitle = ?' => $s{maptitle}) : (), + $s{mapname} ? ( 'mapname = ?' => $s{mapname}) : (), + $s{numplayers} ? ( 'numplayers = ?' => $s{numplayers}) : ('numplayers = ?' => 0), + $s{maxplayers} ? ( 'maxplayers = ?' => $s{maxplayers}) : ('maxplayers = ?' => 0), + $s{minplayers} ? ( 'minplayers = ?' => $s{minplayers}) : ('minplayers = ?' => 0), + $s{botskill} ? ( 'botskill = ?' => $s{botskill}) : (), + $s{balanceteams} ? ( 'balanceteams = ?' => ( $s{balanceteams} ? 1 : 0) ) : (), + $s{playersbalanceteams} ? ( 'playersbalanceteams = ?' => ( $s{playersbalanceteams} ? 1 : 0) ) : (), + $s{friendlyfire} ? ( 'friendlyfire = ?' => $s{friendlyfire}) : (), + $s{maxteams} ? ( 'maxteams = ?' => $s{maxteams}) : (), + $s{timelimit} ? ( 'timelimit = ?' => $s{timelimit}) : (), + $s{goalteamscore} ? ( 'goalteamscore = ?' => int( $s{goalteamscore}) ) : (), + $s{fraglimit} ? ( 'fraglimit = ?' => int( $s{fraglimit}) ) : (), + $s{mutators} ? ( 'mutators = ?' => $s{mutators}) : ('mutators = ?' => "None"), + $s{updated} ? ('updated = datetime(?, \'unixepoch\')' => $s{updated}) : (), + ); + + my($q, @p) = sqlprint("UPDATE utserver_info !H WHERE server_id = ?", \%H, $id); + return $self->{dbh}->do($q, undef, @p); +} + + +################################################################################ +## Add a new utserver and trigger the update routine above. +## opts: id, server info data +################################################################################ +sub add_utserver { + my ($self, $ip, $port) = @_; + + # create new entry + return $self->{dbh}->do( + "INSERT INTO utserver_info (server_id) + SELECT (SELECT id FROM serverlist WHERE ip = ? AND port = ?)", + undef, $ip, $port); +} + + +################################################################################ +## Delete all players from a certain server ID +## opts: server id +################################################################################ +sub delete_utplayers { + my ($self, $sid) = @_; + + # delete players for server_id + return $self->{dbh}->do( + "DELETE FROM utplayer_info WHERE server_id = ?", + undef, $sid); +} + +################################################################################ +## Insert player info for a single player in server sid +## opts: server id, player info +################################################################################ +sub insert_utplayer { + my $self = shift; + my $sid = shift; + my %s = ( + updated => time, + @_); + + # apparently useless chunk of code + # FIXME move to site part + my %H = ( + $s{server_id} ? ( 'server_id = ?' => $s{server_id}) : (), + $s{player} ? ( 'player = ?' => $s{player}) : (), + $s{team} ? ( 'team = ?' => int( $s{team})) : (), + $s{frags} ? ( 'frags = ?' => int( $s{frags})) : (), + $s{mesh} ? ( 'mesh = ?' => $s{mesh}) : (), + $s{skin} ? ( 'skin = ?' => $s{skin}) : (), + $s{face} ? ( 'face = ?' => $s{face}) : (), + $s{ping} ? ( 'ping = ?' => int( $s{ping})) : (), + $s{ngsecret} ? ( 'ngsecret = ?' => $s{ngsecret}) : (), + $s{updated} ? ('updated = datetime(?, \'unixepoch\')' => $s{updated}) : (), + ); + + # insert + return $self->{dbh}->do( + "INSERT INTO utplayer_info (server_id, player, team, frags, mesh, skin, face, ping, ngsecret) + VALUES (?,?,?,?,?,?,?,?,?)", + undef, $sid, $s{player}, $s{team}, $s{frags}, $s{mesh}, $s{skin}, $s{face}, $s{ping}, $s{ngsecret}); +} + +1; diff --git a/lib/MasterServer/Database/dbAddServers.pm b/lib/MasterServer/Database/dbAddServers.pm deleted file mode 100755 index 4898316..0000000 --- a/lib/MasterServer/Database/dbAddServers.pm +++ /dev/null @@ -1,162 +0,0 @@ - -package MasterServer::Database::dbAddServers; - -use strict; -use warnings; -use MasterServer::Core::Util 'sqlprint'; -use Exporter 'import'; - -our @EXPORT = qw| add_server_new - add_server_list - update_server_list - syncer_add |; - -################################################################################ -## Update an existing address or add a new address to the pending list. -## opts: direct beacon, set update, game -################################################################################ -sub add_server_new { - my $self = shift; - my %o = ( - updated => time, - @_); - - # try updating it in serverlist - my %H = ( - $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}) : (), - ); - - my($q, @p) = sqlprint("UPDATE serverlist !H - WHERE ip = ? AND port = ?", \%H, $o{ip}, $o{heartbeat}); - - my $n = $self->{dbh}->do($q, undef, @p); - - # 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}) : (), - ); - - ($q, @p) = sqlprint("UPDATE pending !H - WHERE ip = ? AND heartbeat = ?", \%H, $o{ip}, $o{heartbeat}); - - # exec query - $n = $self->{dbh}->do($q, undef, @p); - - # if beacon was updated - return 1 if ($n > 0); - - # if not found at all, add to pending - $n = $self->{dbh}->do( - "INSERT INTO pending ( - ip, - beaconport, - heartbeat, - gamename, - secure) - SELECT ?, ?, ?, ?, ?", - undef, $o{ip}, $o{beaconport}, $o{heartbeat}, lc $o{gamename}, $o{secure}); - - # it was added to pending - return 2 if ($n > 0); -} - -################################################################################ -## Update the server info in the serverlist -################################################################################ -sub update_server_list { - my $self = shift; - my %o = ( - updated => time, - @_); - - # try updating it in serverlist - my %H = ( - $o{gamename} ? ('gamename = ?' => lc $o{gamename}) : (), - $o{gamever} ? ( 'gamever = ?' => $o{gamever}) : (), - $o{hostname} ? ('hostname = ?' => $o{hostname}) : (), - $o{hostport} ? ('hostport = ?' => $o{hostport}) : (), - $o{updated} ? ( 'updated = to_timestamp(?)' => $o{updated}) : (), - ); - - my($q, @p) = sqlprint("UPDATE serverlist !H - WHERE ip = ? AND port = ?", \%H, $o{ip}, $o{port}); - - return $self->{dbh}->do($q, undef, @p); -} - -################################################################################ -## beacon was verified or otherwise accepted and will now be added to the -## serverlist. -################################################################################ -sub add_server_list { - my $self = shift; - my %o = @_; - - # insert basic data - return $self->{dbh}->do("INSERT INTO serverlist (ip, port, gamename, country) - SELECT ?, ?, ?, ?", undef, - $o{ip}, $o{port}, lc $o{gamename}, $self->ip2country($o{ip})); -} - -################################################################################ -## 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 the list AND up to date, - # acknowledge its existance but don't do anything with it - my $u = $self->{dbh}->do( - "SELECT * FROM serverlist - WHERE ip = ? - AND port = ? - AND updated > to_timestamp(?)", - undef, $ip, $port, time-7200); - - # 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") if ($u > 0); - - # return 1 if found - return 1 if ($u > 0); - - # if not found or out of date, add it to pending to be checked again - $u = $self->{dbh}->do( - "INSERT INTO pending (ip, heartbeat, gamename, secure) - SELECT ?, ?, ?, ?", - undef, $ip, $port, lc $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; -} - -1; diff --git a/lib/MasterServer/Database/dbCiphers.pm b/lib/MasterServer/Database/dbCiphers.pm deleted file mode 100755 index e62552d..0000000 --- a/lib/MasterServer/Database/dbCiphers.pm +++ /dev/null @@ -1,57 +0,0 @@ - -package MasterServer::Database::dbCiphers; - -use strict; -use warnings; -use Exporter 'import'; - -our @EXPORT = qw| clear_ciphers - insert_cipher - get_game_props |; - -################################################################################ -## Clear all existing ciphers from the database -################################################################################ -sub clear_ciphers { - my $self = shift; - - # delete ALL entries - my $u = $self->{dbh}->do("DELETE FROM games"); -} - -################################################################################ -## Insert the list of supported games and their ciphers / default ports / -## descriptions included from the data/supportedgames.pl file. -################################################################################ -sub insert_cipher { - my ($self, %opt) = @_; - - # insert a single cipher/key combo - my $u = $self->{dbh}->do( - "INSERT INTO games ( - gamename, - cipher, - description, - default_qport) - VALUES(?, ?, ?, ?)", undef, - lc $opt{gamename}, $opt{cipher}, $opt{description}, $opt{default_qport}); - return 1 if ($u and $u > 0); - - # or else report error - $self->log("error", "An error occurred adding a cipher for $opt{gamename}"); -} - -################################################################################ -## get the cipher, description and default port that goes with given gamename -################################################################################ -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]; -} - -1; diff --git a/lib/MasterServer/Database/dbCore.pm b/lib/MasterServer/Database/dbCore.pm deleted file mode 100755 index 4ea0c79..0000000 --- a/lib/MasterServer/Database/dbCore.pm +++ /dev/null @@ -1,83 +0,0 @@ - -package MasterServer::Database::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. -## -## Recommended database types: Postgresql, MySQL or SQLite. Warranty void if -## other database types are used. Use at your own risk. -################################################################################ -sub database_login { - my $self = shift; - - # read db type from db login - my @db_type = split(':', $self->{dblogin}->[0]); - - # if the type is an SQLite database, check if the database file exists - if ( "SQLite" =~ m/$db_type[1]/i) { - - # 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(); - } - } - - # 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 => $self->{db_print}}); - - # verify that the database connected - if (defined $dbh) { - - # log the event - $self->log("info","Connected to the $db_type[1] database."); - - # turn on error printing - $dbh->{printerror} = 1; - - # if the type is an SQLite database, disable sync rw. - if ( "SQLite" =~ m/$db_type[1]/i) { - - # synchronous read/writing to the SQLite 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"); - - # allow the use of foreign keys (referencing) - $dbh->do("PRAGMA foreign_keys = ON"); - } - - # return the dbi object for further use - return $dbh; - } - else { - # fatal error - $self->log("fatal", "$DBI::errstr!"); - - # end program - $self->halt(); - } - - # in case of any other error, return undef. - return undef; -} - -1; diff --git a/lib/MasterServer/Database/dbGetServers.pm b/lib/MasterServer/Database/dbGetServers.pm deleted file mode 100755 index 5069462..0000000 --- a/lib/MasterServer/Database/dbGetServers.pm +++ /dev/null @@ -1,139 +0,0 @@ -package MasterServer::Database::dbGetServers; - -use strict; -use warnings; -use Exporter 'import'; - -our @EXPORT = qw| get_server - get_pending - get_gamenames |; - -################################################################################ -## get server details for one or multiple servers -## opts: limit, see $order -################################################################################ -sub get_server { - my $s = shift; - my %o = ( - sort => '', - @_ - ); - - my %where = ( - $o{next_id} ? ( 'id > ?' => $o{next_id}) : (), - $o{id} ? ( 'id = ?' => $o{id}) : (), - $o{ip} ? ( 'ip = ?' => $o{ip}) : (), - $o{port} ? ( 'port = ?' => $o{port}) : (), - $o{gamename} ? ( 'gamename = ?' => lc $o{gamename}) : (), - $o{gamever} ? ( 'gamever = ?' => $o{gamever}) : (), - $o{hostname} ? ( 'hostname = ?' => $o{hostname}) : (), - $o{hostport} ? ( 'hostport = ?' => $o{hostport}) : (), - $o{country} ? ( 'country = ?' => $o{country}) : (), - $o{b333ms} ? ( 'b333ms = ?' => $o{b333ms}) : (), - $o{blacklisted} ? ('blacklisted = ?' => $o{blacklisted}) : (), - $o{added} ? ( 'added < to_timestamp(?)' => (time-$o{added})) : (), - $o{beacon} ? ( 'beacon > to_timestamp(?)' => (time-$o{beacon})) : (), - $o{updated} ? ('updated > to_timestamp(?)' => (time-$o{updated})) : (), - $o{before} ? ('updated < to_timestamp(?)' => (time-$o{before})) : (), - ); - - my @select = ( qw| - id - ip - port - gamename - gamever - hostname - hostport - country - b333ms - blacklisted - added - beacon - updated - |); - - my $order = sprintf { - id => 'id %s', - ip => 'ip %s', - port => 'port %s', - gamename => 'gamename %s', - gamever => 'gamever %s', - hostname => 'hostname %s', - hostport => 'hostport %s', - country => 'country %s', - b333ms => 'b333ms %s', - blacklisted => 'blacklisted %s', - added => 'added %s', - beacon => 'beacon %s', - updated => 'updated %s', - }->{ $o{sort}||'id' }, $o{reverse} ? 'DESC' : 'ASC'; - - return $s->db_all( q| - SELECT !s FROM serverlist - !W - ORDER BY !s| - .($o{limit} ? " LIMIT ?" : ""), - join(', ', @select), \%where, $order, ($o{limit} ? $o{limit} : ()), - ); -} - -################################################################################ -## get server details for one or multiple pending servers -## opts: limit, next_id, beaconport, heartbeat, gamename, secure, enctype, added -################################################################################ -sub get_pending { - my $s = shift; - my %o = ( - sort => '', - @_ - ); - - my %where = ( - $o{next_id} ? ( 'id > ?' => $o{next_id}) : (), - $o{id} ? ( 'id = ?' => $o{id}) : (), - $o{ip} ? ( 'ip = ?' => $o{ip}) : (), - $o{beaconport} ? ('beaconport = ?' => $o{beaconport}) : (), - $o{heartbeat} ? ( 'heartbeat = ?' => $o{heartbeat}) : (), - $o{gamename} ? ( 'gamename = ?' => lc $o{gamename}) : (), - $o{secure} ? ( 'secure = ?' => $o{secure}) : (), - $o{enctype} ? ( 'enctype = ?' => $o{enctype}) : (), - $o{added} ? ('added < to_timestamp(?)' => (time-$o{added})) : (), - $o{after} ? ('added > to_timestamp(?)' => (time-$o{after})) : (), - ); - - my @select = ( qw| id ip beaconport heartbeat gamename secure enctype added |,); - my $order = sprintf { - id => 'id %s', - ip => 'ip %s', - beaconport => 'beaconport %s', - heartbeat => 'heartbeat %s', - gamename => 'gamename %s', - secure => 'secure %s', - enctype => 'enctype %s', - added => 'added %s', - }->{ $o{sort}||'id' }, $o{reverse} ? 'DESC' : 'ASC'; - - return $s->db_all( q| - SELECT !s FROM pending - !W - ORDER BY !s| - .($o{limit} ? " LIMIT ?" : ""), - join(', ', @select), \%where, $order, ($o{limit} ? $o{limit} : ()), - ); -} - -################################################################################ -## 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. -################################################################################ -sub get_gamenames { - my $self = shift; - - return $self->{dbh}->selectall_arrayref( - "SELECT distinct gamename - FROM serverlist"); -} - -1; diff --git a/lib/MasterServer/Database/dbMaintenance.pm b/lib/MasterServer/Database/dbMaintenance.pm deleted file mode 100755 index 41ea93a..0000000 --- a/lib/MasterServer/Database/dbMaintenance.pm +++ /dev/null @@ -1,40 +0,0 @@ -package MasterServer::Database::dbMaintenance; - -use strict; -use warnings; -use Exporter 'import'; - -our @EXPORT = qw| delete_old_pending - remove_pending |; - -################################################################################ -## delete unresponsive servers from the pending list -## where the server is unresponsive for more than 1 hour -################################################################################ -sub delete_old_pending { - my ($self) = shift; - - # remove servers - my $u = $self->{dbh}->do( - "DELETE FROM pending - WHERE added < to_timestamp(?)", undef, time-3600); - - # notify - $self->log("delete", "Removed $u entries from pending.") if ($u > 0); -} - -################################################################################ -## 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); -} - -1; diff --git a/lib/MasterServer/Database/dbStats.pm b/lib/MasterServer/Database/dbStats.pm deleted file mode 100755 index 70962dc..0000000 --- a/lib/MasterServer/Database/dbStats.pm +++ /dev/null @@ -1,117 +0,0 @@ -package MasterServer::Database::dbStats; - -use strict; -use warnings; -use Exporter 'import'; - -our @EXPORT = qw| get_gamelist_stats - write_direct_beacons - write_stat - write_kfstats |; - -################################################################################ -# calculate stats for all individual games -################################################################################ -sub get_gamelist_stats { - my $self = shift; - - return $self->{dbh}->selectall_arrayref( - "SELECT DISTINCT gamename AS gamename, - COUNT(NULLIF(b333ms AND updated > to_timestamp(?), FALSE)) AS numdirect, - COUNT(NULLIF(updated > to_timestamp(?), FALSE)) AS numtotal - FROM serverlist - GROUP BY gamename", undef, time-7200, time-7200); -} - -################################################################################ -# Determine from the last beacon whether the server is still uplinking -# directly to us, or whether it stopped uplinking and is now artificially -# kept in the database. -################################################################################ -sub write_direct_beacons { - my $self = shift; - my $u = $self->{dbh}->do( - "UPDATE serverlist - SET b333ms = CAST(0 AS BOOLEAN) - WHERE beacon < to_timestamp(?) AND b333ms", - undef, time-3600); - - # notify - $self->log("unset", "Lost $u direct beacons.") if ($u > 0); -} - -################################################################################ -# Write the stats to the games table -# A stat can not exist without existing gamename. Was inserted by cipher loader. -################################################################################ -sub write_stat { - my ($self, %opt) = @_; - - # if it is already in the pending list, update it with a new challenge - my $u = $self->{dbh}->do( - "UPDATE games - SET num_uplink = ?, - num_total = ? - WHERE gamename = ?", - undef, $opt{num_uplink}, $opt{num_total}, lc $opt{gamename}); - - # notify - $self->log("update", "Updated stats for $opt{gamename}.") if ($u > 0); - -} - -################################################################################ -## Write the KFStats to the database -################################################################################ -sub write_kfstats { - my ($self, $h) = @_; - - # check if entry already excists. - my $u = $self->{dbh}->selectall_arrayref( - "SELECT * FROM kfstats WHERE UTkey = ? ", undef, $h->{UTkey}); - - if ( !defined $u->[0] ) { - $u = $self->{dbh}->do( - "INSERT INTO kfstats (UTkey, Username) VALUES (?,?)", - undef, $h->{UTkey}, $h->{Username}); - - # notify - $self->log("kfnew", "New KF Player $h->{Username} added"); - } - - # update existing information - $u = $self->{dbh}->do("UPDATE kfstats SET - Username = ?, - CurrentVeterancy = ?, - TotalKills = ?, - DecaptedKills = ?, - TotalMeleeDamage = ?, - MeleeKills = ?, - PowerWpnKills = ?, - BullpupDamage = ?, - StalkerKills = ?, - TotalWelded = ?, - TotalHealed = ?, - TotalPlaytime =?, - GamesWon = ?, - GamesLost = ? - WHERE UTkey = ?", undef, - $h->{Username}, - $h->{CurrentVeterancy}, - $h->{TotalKills}, - $h->{DecaptedKills}, - $h->{TotalMeleeDamage}, - $h->{MeleeKills}, - $h->{PowerWpnKills}, - $h->{BullpupDamage}, - $h->{StalkerKills}, - $h->{TotalWelded}, - $h->{TotalHealed}, - $h->{TotalPlaytime}, - $h->{GamesWon}, - $h->{GamesLost}, - $h->{UTkey} - ); -} - -1; diff --git a/lib/MasterServer/Database/dbUTServerInfo.pm b/lib/MasterServer/Database/dbUTServerInfo.pm deleted file mode 100755 index 9352e38..0000000 --- a/lib/MasterServer/Database/dbUTServerInfo.pm +++ /dev/null @@ -1,238 +0,0 @@ - -package MasterServer::Database::dbUTServerInfo; - -use strict; -use warnings; -use MasterServer::Core::Util 'sqlprint'; -use Exporter 'import'; - -our @EXPORT = qw| get_utserver - add_utserver - update_utserver - delete_utplayers - insert_utplayer |; - -################################################################################ -## get server details for one or multiple UT servers -## opts: limit, see $order -################################################################################ -sub get_utserver { - my $s = shift; - my %o = ( - sort => '', - @_ - ); - - my %where = ( - $o{id} ? ('server_id = ?' => $o{id}) : (), - $o{minnetver} ? ('minnetver = ?' => $o{minnetver}) : (), - $o{gamever} ? ('gamever = ?' => $o{gamever}) : (), - $o{location} ? ('location = ?' => $o{location}) : (), - $o{listenserver} ? ('listenserver = ?' => $o{listenserver}) : (), - $o{hostport} ? ('hostport = ?' => $o{hostport}) : (), - $o{hostname} ? ('hostname = ?' => $o{hostname}) : (), - $o{adminname} ? ('adminname = ?' => $o{adminname}) : (), - $o{adminemail} ? ('adminemail = ?' => $o{adminemail}) : (), - $o{password} ? ('password = ?' => $o{password}) : (), - $o{gametype} ? ('gametype = ?' => $o{gametype}) : (), - $o{gamestyle} ? ('gamestyle = ?' => $o{gamestyle}) : (), - $o{changelevels} ? ('changelevels = ?' => $o{changelevels}) : (), - $o{maptitle} ? ('maptitle = ?' => $o{maptitle}) : (), - $o{mapname} ? ('mapname = ?' => $o{mapname}) : (), - $o{numplayers} ? ('numplayers = ?' => $o{numplayers}) : (), - $o{maxplayers} ? ('maxplayers = ?' => $o{maxplayers}) : (), - $o{minplayers} ? ('minplayers = ?' => $o{minplayers}) : (), - $o{botskill} ? ('botskill = ?' => $o{botskill}) : (), - $o{balanceteams} ? ('balanceteams = ?' => $o{balanceteams}) : (), - $o{playersbalanceteams} ? ('playersbalanceteams = ?' => $o{playersbalanceteams}) : (), - $o{friendlyfire} ? ('friendlyfire = ?' => $o{friendlyfire}) : (), - $o{maxteams} ? ('maxteams = ?' => $o{maxteams}) : (), - $o{timelimit} ? ('timelimit = ?' => $o{timelimit}) : (), - $o{goalteamscore} ? ('goalteamscore = ?' => $o{goalteamscore}) : (), - $o{fraglimit} ? ('fraglimit = ?' => $o{fraglimit}) : (), - $o{mutators} ? ('hostname ILIKE ?' => "%$o{mutators}%") : (), - $o{updated} ? ('updated > to_timestamp(?)'=> (time-$o{updated})) : (), - ); - - my @select = ( qw| - server_id - minnetver - gamever - location - listenserver - hostport - hostname - adminname - adminemail - password - gametype - gamestyle - changelevels - maptitle - mapname - numplayers - maxplayers - minplayers - botskill - balanceteams - playersbalanceteams - friendlyfire - maxteams - timelimit - goalteamscore - fraglimit - mutators - updated - |); - - my $order = sprintf { - server_id => 'server_id %s', - minnetver => 'minnetver %s', - gamever => 'gamever %s', - location => 'location %s', - listenserver => 'listenserver %s', - hostport => 'hostport %s', - hostname => 'hostname %s', - adminname => 'adminname %s', - adminemail => 'adminemail %s', - password => 'password %s', - gametype => 'gametype %s', - gamestyle => 'gamestyle %s', - changelevels => 'changelevels %s', - maptitle => 'maptitle %s', - mapname => 'mapname %s', - numplayers => 'numplayers %s', - maxplayers => 'maxplayers %s', - minplayers => 'minplayers %s', - botskill => 'botskill %s', - balanceteams => 'balanceteams %s', - playersbalanceteams => 'playersbalanceteams %s', - friendlyfire => 'friendlyfire %s', - maxteams => 'maxteams %s', - timelimit => 'timelimit %s', - goalteamscore => 'goalteamscore %s', - fraglimit => 'fraglimit %s', - mutators => 'mutators %s', - updated => 'updated %s', - }->{ $o{sort}||'server_id' }, $o{reverse} ? 'DESC' : 'ASC'; - - return $s->db_all( q| - SELECT !s FROM utserver_info - !W - ORDER BY !s| - .($o{limit} ? " LIMIT ?" : ""), - join(', ', @select), \%where, $order, ($o{limit} ? $o{limit} : ()), - ); -} - - -################################################################################ -## Update serverinfo for an existing address to the utserver list. -## opts: all server info data fields. -################################################################################ -sub update_utserver { - my $self = shift; - my $id = shift; - my %s = ( - # defaults - updated => time, - @_); - - # try updating it in serverlist - my %H = ( - $s{minnetver} ? ( 'minnetver = ?' => $s{minnetver} ) : (), - $s{gamever} ? ( 'gamever = ?' => int( $s{gamever}) ) : (), - $s{location} ? ( 'location = ?' => $s{location} ) : (), - $s{listenserver} ? ( 'listenserver = ?' => ( $s{listenserver} ? 1 : 0) ) : (), - $s{hostport} ? ( 'hostport = ?' => $s{hostport}) : (), - $s{hostname} ? ( 'hostname = ?' => $s{hostname}) : (), - $s{AdminName} ? ( 'adminname = ?' => $s{AdminName}) : (), - $s{AdminEMail} ? ( 'adminemail = ?' => $s{AdminEMail}) : (), - $s{password} ? ( 'password = ?' => ( $s{password} ? 1 : 0) ) : (), - $s{gametype} ? ( 'gametype = ?' => $s{gametype}) : (), - $s{gamestyle} ? ( 'gamestyle = ?' => $s{gamestyle}) : (), - $s{changelevels} ? ( 'changelevels = ?' => ( $s{changelevels} ? 1 : 0) ) : (), - $s{maptitle} ? ( 'maptitle = ?' => $s{maptitle}) : (), - $s{mapname} ? ( 'mapname = ?' => $s{mapname}) : (), - $s{numplayers} ? ( 'numplayers = ?' => $s{numplayers}) : ('numplayers = ?' => 0), - $s{maxplayers} ? ( 'maxplayers = ?' => $s{maxplayers}) : ('maxplayers = ?' => 0), - $s{minplayers} ? ( 'minplayers = ?' => $s{minplayers}) : ('minplayers = ?' => 0), - $s{botskill} ? ( 'botskill = ?' => $s{botskill}) : (), - $s{balanceteams} ? ( 'balanceteams = ?' => ( $s{balanceteams} ? 1 : 0) ) : (), - $s{playersbalanceteams} ? ( 'playersbalanceteams = ?' => ( $s{playersbalanceteams} ? 1 : 0) ) : (), - $s{friendlyfire} ? ( 'friendlyfire = ?' => $s{friendlyfire}) : (), - $s{maxteams} ? ( 'maxteams = ?' => $s{maxteams}) : (), - $s{timelimit} ? ( 'timelimit = ?' => $s{timelimit}) : (), - $s{goalteamscore} ? ( 'goalteamscore = ?' => int( $s{goalteamscore}) ) : (), - $s{fraglimit} ? ( 'fraglimit = ?' => int( $s{fraglimit}) ) : (), - $s{mutators} ? ( 'mutators = ?' => $s{mutators}) : ('mutators = ?' => "None"), - $s{updated} ? ('updated = to_timestamp(?)' => $s{updated}) : (), - ); - - my($q, @p) = sqlprint("UPDATE utserver_info !H WHERE server_id = ?", \%H, $id); - return $self->{dbh}->do($q, undef, @p); -} - - -################################################################################ -## Add a new utserver and trigger the update routine above. -## opts: id, server info data -################################################################################ -sub add_utserver { - my ($self, $ip, $port) = @_; - - # create new entry - return $self->{dbh}->do( - "INSERT INTO utserver_info (server_id) - SELECT (SELECT id FROM serverlist WHERE ip = ? AND port = ?)", - undef, $ip, $port); -} - - -################################################################################ -## Delete all players from a certain server ID -## opts: server id -################################################################################ -sub delete_utplayers { - my ($self, $sid) = @_; - - # delete players for server_id - return $self->{dbh}->do( - "DELETE FROM utplayer_info WHERE server_id = ?", - undef, $sid); -} - -################################################################################ -## Insert player info for a single player in server sid -## opts: server id, player info -################################################################################ -sub insert_utplayer { - my $self = shift; - my $sid = shift; - my %s = ( - updated => time, - @_); - - # apparently useless chunk of code - # FIXME move to site part - my %H = ( - $s{server_id} ? ( 'server_id = ?' => $s{server_id}) : (), - $s{player} ? ( 'player = ?' => $s{player}) : (), - $s{team} ? ( 'team = ?' => int( $s{team})) : (), - $s{frags} ? ( 'frags = ?' => int( $s{frags})) : (), - $s{mesh} ? ( 'mesh = ?' => $s{mesh}) : (), - $s{skin} ? ( 'skin = ?' => $s{skin}) : (), - $s{face} ? ( 'face = ?' => $s{face}) : (), - $s{ping} ? ( 'ping = ?' => int( $s{ping})) : (), - $s{ngsecret} ? ( 'ngsecret = ?' => $s{ngsecret}) : (), - $s{updated} ? ('updated = to_timestamp(?)' => $s{updated}) : (), - ); - - # insert - return $self->{dbh}->do( - "INSERT INTO utplayer_info (server_id, player, team, frags, mesh, skin, face, ping, ngsecret) - VALUES (?,?,?,?,?,?,?,?,?)", - undef, $sid, $s{player}, $s{team}, $s{frags}, $s{mesh}, $s{skin}, $s{face}, $s{ping}, $s{ngsecret}); -} - -1; -- cgit v1.2.3