diff options
Diffstat (limited to 'lib')
17 files changed, 909 insertions, 95 deletions
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/dbAddServers.pm b/lib/MasterServer/Database/Pg/dbAddServers.pm index 4898316..33785da 100755 --- a/lib/MasterServer/Database/dbAddServers.pm +++ b/lib/MasterServer/Database/Pg/dbAddServers.pm @@ -1,5 +1,5 @@ -package MasterServer::Database::dbAddServers; +package MasterServer::Database::Pg::dbAddServers; use strict; use warnings; diff --git a/lib/MasterServer/Database/dbCiphers.pm b/lib/MasterServer/Database/Pg/dbCiphers.pm index e62552d..e2a5784 100755 --- a/lib/MasterServer/Database/dbCiphers.pm +++ b/lib/MasterServer/Database/Pg/dbCiphers.pm @@ -1,5 +1,5 @@ -package MasterServer::Database::dbCiphers; +package MasterServer::Database::Pg::dbCiphers; use strict; use warnings; 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/dbGetServers.pm b/lib/MasterServer/Database/Pg/dbGetServers.pm index 5069462..a33582a 100755 --- a/lib/MasterServer/Database/dbGetServers.pm +++ b/lib/MasterServer/Database/Pg/dbGetServers.pm @@ -1,4 +1,4 @@ -package MasterServer::Database::dbGetServers; +package MasterServer::Database::Pg::dbGetServers; use strict; use warnings; diff --git a/lib/MasterServer/Database/dbMaintenance.pm b/lib/MasterServer/Database/Pg/dbMaintenance.pm index 41ea93a..90e4d34 100755 --- a/lib/MasterServer/Database/dbMaintenance.pm +++ b/lib/MasterServer/Database/Pg/dbMaintenance.pm @@ -1,4 +1,4 @@ -package MasterServer::Database::dbMaintenance; +package MasterServer::Database::Pg::dbMaintenance; use strict; use warnings; diff --git a/lib/MasterServer/Database/dbStats.pm b/lib/MasterServer/Database/Pg/dbStats.pm index 70962dc..6e92ab6 100755 --- a/lib/MasterServer/Database/dbStats.pm +++ b/lib/MasterServer/Database/Pg/dbStats.pm @@ -1,4 +1,4 @@ -package MasterServer::Database::dbStats; +package MasterServer::Database::Pg::dbStats; use strict; use warnings; diff --git a/lib/MasterServer/Database/dbUTServerInfo.pm b/lib/MasterServer/Database/Pg/dbUTServerInfo.pm index 9352e38..12ba05f 100755 --- a/lib/MasterServer/Database/dbUTServerInfo.pm +++ b/lib/MasterServer/Database/Pg/dbUTServerInfo.pm @@ -1,5 +1,5 @@ -package MasterServer::Database::dbUTServerInfo; +package MasterServer::Database::Pg::dbUTServerInfo; use strict; use warnings; 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/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; |