diff options
Diffstat (limited to 'lib/MasterWebInterface/Database/Pg/ServerInfo.pm')
| -rwxr-xr-x | lib/MasterWebInterface/Database/Pg/ServerInfo.pm | 178 |
1 files changed, 178 insertions, 0 deletions
diff --git a/lib/MasterWebInterface/Database/Pg/ServerInfo.pm b/lib/MasterWebInterface/Database/Pg/ServerInfo.pm new file mode 100755 index 0000000..8c6ba6f --- /dev/null +++ b/lib/MasterWebInterface/Database/Pg/ServerInfo.pm @@ -0,0 +1,178 @@ +package MasterWebInterface::Database::Pg::ServerInfo; +use strict; +use warnings; +use Exporter 'import'; +our @EXPORT = qw| dbGetServerListInfo dbGetServerDetails dbGetPlayerInfo |; + +################################################################################ +## get server details for one or multiple servers +################################################################################ +sub dbGetServerListInfo { + my $s = shift; + my %o = ( sort => '', @_ ); + + my %where = ( + $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 serverlist.gamename gamever hostname hostport country b333ms blacklisted description |, + "extract('epoch' from added) as e_added", + "extract('epoch' from updated) as e_updated", + "extract('epoch' from CURRENT_TIMESTAMP - added) as addiff", + "extract('epoch' from CURRENT_TIMESTAMP - updated) as updiff", + ); + + my $order = sprintf { + id => 'id %s', + ip => 'ip %s', + port => 'port %s', + gamename => 'serverlist.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->dbAll( q|SELECT !s FROM serverlist + JOIN games ON serverlist.gamename = games.gamename + !W ORDER BY !s|.($o{limit} ? " LIMIT ?" : ""), + join(', ', @select), \%where, $order, ($o{limit} ? $o{limit} : ()), + ); +} + +################################################################################ +## get server details for one or multiple UT servers +################################################################################ +sub dbGetServerDetails { + my $s = shift; + my %o = (sort => '', @_ ); + + my %where = ( + $o{id} ? ('server_id = ?' => $o{id}) : (), + $o{minnetver} ? ('minnetver = ?' => $o{minnetver}) : (), + $o{location} ? ('location = ?' => $o{location}) : (), + $o{listenserver} ? ('listenserver = ?' => $o{listenserver}) : (), + $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} ? ('mutators ILIKE ?' => "%$o{mutators}%") : (), + $o{updated} ? ('updated > to_timestamp(?)'=> (time-$o{updated})) : (), + ); + + my @select = ( qw| server_id minnetver location listenserver adminname adminemail + password gametype gamestyle changelevels maptitle mapname numplayers maxplayers + minplayers botskill balanceteams playersbalanceteams friendlyfire maxteams + timelimit goalteamscore fraglimit mutators |, + "extract('epoch' from updated) as e_updated2", + "extract('epoch' from CURRENT_TIMESTAMP - updated) as updiff2", + ); + + my $order = sprintf { + server_id => 'server_id %s', + minnetver => 'minnetver %s', + location => 'location %s', + listenserver => 'listenserver %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->dbAll( q| + SELECT !s FROM extended_info + !W + ORDER BY !s| + .($o{limit} ? " LIMIT ?" : ""), + join(', ', @select), \%where, $order, ($o{limit} ? $o{limit} : ()), + ); +} + +################################################################################ +## get player details for one particular server +################################################################################ +sub dbGetPlayerInfo { + my $s = shift; + my %o = (sort => '', @_ ); + + my %where = ( + $o{server_id} ? ('server_id = ?' => $o{server_id}): (), + $o{player} ? ( 'player = ?' => $o{player}) : (), + $o{team} ? ( 'team = ?' => $o{team}) : (), + $o{frags} ? ( 'frags = ?' => $o{frags}) : (), + $o{mesh} ? ( 'mesh = ?' => $o{mesh}) : (), + $o{skin} ? ( 'skin = ?' => $o{skin}) : (), + $o{face} ? ( 'face = ?' => $o{face}) : (), + $o{ping} ? ( 'ping = ?' => $o{ping}) : (), + $o{ngsecret} ? ( 'ngsecret = ?' => $o{ngsecret}) : (), + ); + + my @select = ( qw| server_id player team frags mesh skin face ping ngsecret | ); + my $order = sprintf { + server_id => 'server_id %s', + player => 'player %s', + team => 'team %s', + frags => 'frags %s', + mesh => 'mesh %s', + skin => 'skin %s', + face => 'face %s', + ping => 'ping %s', + ngsecret => 'ngsecret %s', + }->{ $o{sort}||'team' }, $o{reverse} ? 'DESC' : 'ASC'; + + return $s->dbAll( q|SELECT !s FROM player_info !W ORDER BY !s|.($o{limit} ? " LIMIT ?" : ""), + join(', ', @select), \%where, $order, ($o{limit} ? $o{limit} : ()), + ); +} + +1; |