3 files changed, 320 insertions, 0 deletions
diff --git a/lib/MasterWebInterface/Database/SQLite/Games.pm b/lib/MasterWebInterface/Database/SQLite/Games.pm
new file mode 100755
index 0000000..db820f6
--- /dev/null
+++ b/lib/MasterWebInterface/Database/SQLite/Games.pm
@@ -0,0 +1,53 @@
+package MasterWebInterface::Database::SQLite::Games;
+use strict;
+use warnings;
+use Exporter 'import';
+our @EXPORT = qw| dbGameListGet dbGetGameDesc |;
+
+################################################################################
+## get list of game details
+## opt: filter first letter
+################################################################################
+sub dbGameListGet {
+  my $s = shift;
+  my %o = (page => 1, results => 50, sort => '', @_);
+  
+  my %where = (
+    $o{firstchar} 
+      ? ('upper(SUBSTR(description, 1, 1)) = ?' => $o{firstchar} ) : (),
+    $o{search} 
+      ? ('lower(description) LIKE lower(?)' => "%$o{search}%") : (),
+  );
+  
+  my @select = ( qw| description gamename num_uplink num_total |);
+  my $order = sprintf {
+    description => 'description %s',
+    gamename    => 'gamename %s',
+    num_uplink  => 'num_uplink %s',
+    num_total   => 'num_total %s',
+  }->{ $o{sort}||'num_total' }, $o{reverse} ? 'DESC' : 'ASC';
+
+  my($r, $np) = $s->dbPage(\%o, q|
+    SELECT !s FROM games
+      !W
+      ORDER BY !s|,
+    join(', ', @select), \%where, $order
+  );
+
+  my $p = $s->dbAll( q|
+    SELECT COUNT(*) AS num
+    FROM games
+    !W|, \%where,
+  )->[0]{num};
+  return wantarray ? ($r, $np, $p) : $r;
+}
+
+################################################################################
+## get description for a game by gamename
+################################################################################
+sub dbGetGameDesc {
+my ($self, $gn) = @_;
+  return $self->dbAll("SELECT description FROM games WHERE gamename = ?", $gn)->[0]{description};
+}
+
+1;
diff --git a/lib/MasterWebInterface/Database/SQLite/ServerInfo.pm b/lib/MasterWebInterface/Database/SQLite/ServerInfo.pm
new file mode 100755
index 0000000..31c9d57
--- /dev/null
+++ b/lib/MasterWebInterface/Database/SQLite/ServerInfo.pm
@@ -0,0 +1,178 @@
+package MasterWebInterface::Database::SQLite::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 < 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 serverlist.gamename gamever hostname hostport country b333ms blacklisted description |,
+    "strftime('\%s', added)   as e_added",
+    "strftime('\%s', updated) as e_updated",
+    "strftime('\%s', CURRENT_TIMESTAMP) - strftime('\%s', added)    as addiff",
+    "strftime('\%s', CURRENT_TIMESTAMP) - strftime('\%s', 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 |,
+    "strftime('\%s', updated) as e_updated2",
+    "strftime('\%s', CURRENT_TIMESTAMP) - strftime('\%s', 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;
diff --git a/lib/MasterWebInterface/Database/SQLite/Servers.pm b/lib/MasterWebInterface/Database/SQLite/Servers.pm
new file mode 100755
index 0000000..041c4d9
--- /dev/null
+++ b/lib/MasterWebInterface/Database/SQLite/Servers.pm
@@ -0,0 +1,89 @@
+package MasterWebInterface::Database::SQLite::Servers;
+use strict;
+use warnings;
+use Exporter 'import';
+our @EXPORT = qw| dbAddServer dbServerListGet |;
+
+################################################################################
+## check if an ip, port/hostport combination is recent in the serverlist.
+## if not, add the address to the pending list
+################################################################################
+sub dbAddServer {
+  my $self = shift;
+  my %o = (updated => 3600, @_ );
+
+  my %where = (
+    $o{ip}       ? (      'ip = ?' => $o{ip})       : (),
+    $o{port}     ? (    'port = ?' => $o{port})     : (),
+    $o{updated}  ? ('updated > datetime(?, \'unixepoch\')' => (time-$o{updated})) : (),
+  );
+
+  # determine if it already exsits
+  my $u = $self->dbAll("SELECT id FROM serverlist !W", \%where)->[0];
+  return 0 if $u;
+
+  # else, insert in pending (duplicates may exist -- see remove_pending)
+  $self->dbExec("INSERT INTO pending (ip, heartbeat) VALUES (?, ?)", $o{ip}, $o{port});
+  return 1;
+}
+
+################################################################################
+## get the serverlist. default 2 hours time limit
+################################################################################
+sub dbServerListGet {
+  my $s = shift;
+  my %o = ( page => 1, results => 50, sort => '', updated => '7200', @_ );
+  
+  my %where = (
+    defined $o{gamename} && $o{gamename} !~ /all/ 
+      ? ('serverlist.gamename = ?' => $o{gamename}) : (),
+    $o{firstchar} 
+      ? ('upper(SUBSTR(hostname, 1, 1)) = ?' => $o{firstchar} ) : (),
+    $o{search} 
+      ? ('lower(hostname) LIKE lower(?)' => "%$o{search}%") : (),
+    $o{updated}  
+      ? ('serverlist.updated > datetime(?, \'unixepoch\')' => (time-$o{updated})) : (),
+#    ('length(hostname) > ?' => 1),  # don't show empty hostnames
+    ('hostport > ?' => 0),          # or games with empty hostport
+  );
+  
+  my @select = ( qw| id ip hostport hostname serverlist.gamename country numplayers maxplayers maptitle mapname gametype added description |,
+    "strftime('\%s', CURRENT_TIMESTAMP) - strftime('\%s', serverlist.updated) as diff",
+    "strftime('\%s', serverlist.updated) as updated",
+    "strftime('\%s', serverlist.added)   as added");
+
+  my $order = sprintf {
+    id          => 'id %s',
+    ip          => 'ip %s',
+    hostport    => 'hostport %s',
+    hostname    => 'hostname %s',
+    gamename    => 'serverlist.gamename %s',
+    country     => 'country %s',
+    diff        => 'diff %s',
+    added       => 'serverlist.added %s',
+    updated     => 'updated %s',
+    gametype    => 'gametype %s',
+    numplayers  => 'numplayers %s',
+    maxplayers  => 'maxplayers %s',
+    mapname     => 'mapname %s',
+    description => 'description %s',
+  }->{ $o{sort}||'hostname' }, $o{reverse} ? 'DESC' : 'ASC';
+
+  my($r, $np) = $s->dbPage(\%o, q|
+    SELECT !s FROM serverlist
+      JOIN games ON serverlist.gamename = games.gamename
+      JOIN extended_info ON serverlist.id = extended_info.server_id
+      !W
+      ORDER BY !s|,
+    join(', ', @select), \%where, $order
+  );
+
+  my $p = $s->dbAll( q|
+    SELECT COUNT(*) AS num
+    FROM serverlist
+    !W|, \%where,
+  )->[0]{num};
+  return wantarray ? ($r, $np, $p) : $r;
+}
+
+1;