aboutsummaryrefslogtreecommitdiff
path: root/lib/MasterServer/Database/SQLite
diff options
context:
space:
mode:
Diffstat (limited to 'lib/MasterServer/Database/SQLite')
-rwxr-xr-xlib/MasterServer/Database/SQLite/dbAddServers.pm168
-rwxr-xr-xlib/MasterServer/Database/SQLite/dbAppletActions.pm51
-rwxr-xr-xlib/MasterServer/Database/SQLite/dbCiphers.pm29
-rwxr-xr-xlib/MasterServer/Database/SQLite/dbCore.pm43
-rwxr-xr-xlib/MasterServer/Database/SQLite/dbGetServers.pm58
-rwxr-xr-xlib/MasterServer/Database/SQLite/dbMaintenance.pm23
-rwxr-xr-xlib/MasterServer/Database/SQLite/dbStats.pm83
-rwxr-xr-xlib/MasterServer/Database/SQLite/dbUTServerInfo.pm117
8 files changed, 157 insertions, 415 deletions
diff --git a/lib/MasterServer/Database/SQLite/dbAddServers.pm b/lib/MasterServer/Database/SQLite/dbAddServers.pm
index 592ab7b..88b1bc8 100755
--- a/lib/MasterServer/Database/SQLite/dbAddServers.pm
+++ b/lib/MasterServer/Database/SQLite/dbAddServers.pm
@@ -1,163 +1,81 @@
-
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 |;
+our @EXPORT = qw| insert_server
+ update_server
+ insert_pending |;
################################################################################
-## Update an existing address or add a new address to the pending list.
-## opts: direct beacon, set update, game
+## Insert minimalistic game data into serverlist
+## params: ip, port, hostport
################################################################################
-sub add_server_new {
+sub insert_server {
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}) : (),
-
- # some applets have incorrect gamename lists, let udpticker update this
- # entry instead. this way, applets don't overwrite with incorrect data
- #$o{gamename} ? ('gamename = ?' => lc $o{gamename}) : (),
- );
-
- my($q, @p) = sqlprint("UPDATE serverlist !H
- 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{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);
+ my %o = (@_);
- # 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);
+ return $self->{dbh}->do(
+ "INSERT INTO serverlist (ip, port, hostport, country) VALUES (?, ?, ?, ?)",
+ undef, $o{ip}, $o{port}, $o{hostport}, $self->ip2country($o{ip}) );
}
################################################################################
## Update the server info in the serverlist
+## required: id or ip + port/hostport
################################################################################
-sub update_server_list {
+sub update_server {
my $self = shift;
- my %o = (
- updated => time,
- @_);
+ my %o = (updated => time, @_);
+
+ # either id, ip+port or ip+hostport are provided.
+ my %W = (
+ $o{id} ? ( 'id = ?' => $o{id}) : (),
+ $o{ip} ? ( 'ip = ?' => $o{ip}) : (),
+ $o{port} ? ( 'port = ?' => $o{port}) : (),
+ $o{hostport} ? ('hostport = ?' => $o{hostport}) : (),
+ );
- # try updating it in serverlist
+ # update where possible
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}) : (),
+ $o{direct} ? ( 'b333ms = CAST(? AS BOOLEAN)' => $o{direct}) : (),
+ $o{direct} ? ( 'beacon = datetime(?, \'unixepoch\')' => $o{updated}) : (),
+ $o{updated} ? ( 'updated = datetime(?, \'unixepoch\')' => $o{updated}) : (),
);
- my($q, @p) = sqlprint("UPDATE serverlist !H
- WHERE ip = ? AND port = ?", \%H, $o{ip}, $o{port});
-
+ my($q, @p) = sqlprint("UPDATE serverlist !H !W", \%H, \%W);
return $self->{dbh}->do($q, undef, @p);
}
################################################################################
-## beacon was verified or otherwise accepted and will now be added to the
-## serverlist.
+## check if an ip, port/hostport combination is recent in the serverlist.
+## if not, add the address to the pending list
################################################################################
-sub add_server_list {
+sub insert_pending {
my $self = shift;
- my %o = @_;
+ my %o = (updated => 3600, @_ );
- # 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 count(*) FROM serverlist
- WHERE ip = ?
- AND port = ?
- AND updated > datetime(?, 'unixepoch')",
- undef, $ip, $port, time-7200);
-
- # if found, return 0
- return 0 if ($u > 0);
+ # selection criteria
+ my %W = (
+ $o{ip} ? ( 'ip = ?' => $o{ip}) : (),
+ $o{port} ? ( 'port = ?' => $o{port}) : (),
+ $o{updated} ? ('updated > datetime(?, \'unixepoch\')' => (time-$o{updated})) : (),
+ );
- # 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);
+ # determine if it already exsits
+ my($q, @p) = sqlprint("SELECT id FROM serverlist !W", \%W);
+ my $u = $self->{dbh}->do($q, undef, @p);
+ return 0 if int($u);
- # 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;
+ # else, insert in pending (duplicates may exist -- see remove_pending)
+ return $self->{dbh}->do("INSERT INTO pending (ip, heartbeat) VALUES (?, ?)",
+ undef, $o{ip}, $o{port} );
}
1;
diff --git a/lib/MasterServer/Database/SQLite/dbAppletActions.pm b/lib/MasterServer/Database/SQLite/dbAppletActions.pm
index d2421fc..1d9c975 100755
--- a/lib/MasterServer/Database/SQLite/dbAppletActions.pm
+++ b/lib/MasterServer/Database/SQLite/dbAppletActions.pm
@@ -3,7 +3,6 @@ package MasterServer::Database::SQLite::dbAppletActions;
use strict;
use warnings;
use Exporter 'import';
-
our @EXPORT = qw| add_master_applet
update_master_applet
reset_master_applets
@@ -18,19 +17,20 @@ sub add_master_applet {
my %o = @_;
my $u = $self->{dbh}->do(
- "SELECT * FROM appletlist
- WHERE ip = ?
- AND port = ?
+ "SELECT * FROM appletlist
+ WHERE ip = ?
+ AND hostport = ?
AND gamename = ?",
- undef, $o{ip}, $o{port}, lc $o{gamename});
+ undef, $o{ip}, $o{hostport}, lc $o{gamename});
# return if found
return if ($u > 0);
# insert applet data
- return $self->{dbh}->do("INSERT INTO appletlist (ip, port, gamename)
- SELECT ?, ?, ?", undef,
- $o{ip}, $o{port}, lc $o{gamename});
+ return $self->{dbh}->do(
+ "INSERT INTO appletlist (ip, hostport, gamename)
+ SELECT ?, ?, ?",
+ undef, $o{ip}, $o{hostport}, lc $o{gamename});
}
################################################################################
@@ -38,10 +38,11 @@ sub add_master_applet {
################################################################################
sub reset_master_applets {
my $self = shift;
- return $self->{dbh}->do("UPDATE appletlist
- SET added = datetime(?, \'unixepoch\'),
- updated = datetime(?, \'unixepoch\')",
- undef, time, time);
+ return $self->{dbh}->do(
+ "UPDATE appletlist
+ SET added = datetime(?, \'unixepoch\'),
+ updated = datetime(?, \'unixepoch\')",
+ undef, time, time);
}
################################################################################
@@ -50,12 +51,13 @@ sub reset_master_applets {
sub update_master_applet {
my ($self, %o) = @_;
- return $self->{dbh}->do("UPDATE appletlist
- SET updated = datetime(?, \'unixepoch\')
- WHERE ip = ?
- AND port = ?
+ return $self->{dbh}->do(
+ "UPDATE appletlist
+ SET updated = datetime(?, \'unixepoch\')
+ WHERE ip = ?
+ AND hostport = ?
AND gamename = ?",
- undef, time, $o{ip}, $o{port}, lc $o{gamename});
+ undef, time, $o{ip}, $o{hostport}, lc $o{gamename});
}
################################################################################
@@ -66,10 +68,10 @@ sub get_masterserver_applets {
my $self = shift;
return $self->db_all(
- "SELECT *
- FROM appletlist
- WHERE updated > datetime(?, \'unixepoch\')",
- time-604800);
+ "SELECT *
+ FROM appletlist
+ WHERE updated > datetime(?, \'unixepoch\')",
+ time-604800);
}
################################################################################
@@ -82,11 +84,12 @@ sub remove_unresponsive_applets {
# remove entries
my $u = $self->{dbh}->do(
- "DELETE FROM appletlist
- WHERE updated < datetime(?, \'unixepoch\')", undef, time-604800);
+ "DELETE FROM appletlist
+ WHERE updated < datetime(?, \'unixepoch\')",
+ undef, time-604800);
# notify
- $self->log("delete", "Removed $u entries from applet list.") if ($u > 0);
+ $self->log("delete", "Removed $u entries from applet list") if ($u > 0);
}
1;
diff --git a/lib/MasterServer/Database/SQLite/dbCiphers.pm b/lib/MasterServer/Database/SQLite/dbCiphers.pm
index f257b7b..1032b4e 100755
--- a/lib/MasterServer/Database/SQLite/dbCiphers.pm
+++ b/lib/MasterServer/Database/SQLite/dbCiphers.pm
@@ -3,19 +3,17 @@ package MasterServer::Database::SQLite::dbCiphers;
use strict;
use warnings;
use Exporter 'import';
-
our @EXPORT = qw| check_cipher_count
clear_ciphers
insert_cipher
- get_game_props
- get_gamenames |;
+ get_game_props |;
################################################################################
## Check if ciphers exist
################################################################################
sub check_cipher_count {
my $self = shift;
- return $self->db_all('SELECT count(*) as num from games')->[0]->{num};
+ return $self->db_all('SELECT count(gamename) as num from games')->[0]->{num};
}
################################################################################
@@ -23,9 +21,7 @@ sub check_cipher_count {
################################################################################
sub clear_ciphers {
my $self = shift;
-
- # delete ALL entries
- my $u = $self->{dbh}->do("DELETE FROM games");
+ $self->{dbh}->do("DELETE FROM games");
}
################################################################################
@@ -56,10 +52,7 @@ sub insert_cipher {
################################################################################
sub get_game_props {
my $s = shift;
- my %o = (
- sort => '',
- @_
- );
+ my %o = (sort => '', @_);
my %where = (
$o{gamename} ? ('gamename = ?' => lc $o{gamename}) : (),
@@ -90,18 +83,4 @@ sub get_game_props {
);
}
-
-################################################################################
-## 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/dbCore.pm b/lib/MasterServer/Database/SQLite/dbCore.pm
index 0772a4e..d16cb26 100755
--- a/lib/MasterServer/Database/SQLite/dbCore.pm
+++ b/lib/MasterServer/Database/SQLite/dbCore.pm
@@ -4,72 +4,47 @@ use strict;
use warnings;
use POSIX qw/strftime/;
use Exporter 'import';
-
our @EXPORT = qw| database_login dump_database |;
################################################################################
## 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]);
- # inform what db we try to load
- $self->log("info","Database: $db_type[1], $db_type[2]");
-
# check if database file exists
my $db_file = [split(':', $self->{dblogin}->[0])]->[2];
$db_file =~ s/dbname=//i;
+ # fatal error if database does not exist
unless (-e $db_file) {
- # fatal error
$self->log("fatal", "Database file $db_file does not exist!");
-
- # end program
$self->halt();
}
- # create the dbi object
+ # connect to SQLite database
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
+ $self->log("info","Connected to the $db_type[1] database $db_type[2]");
$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..
+ # synchronous read/writing to the SQLite file OFF. Faster, but risk on data
+ # loss on crashes, premature exits or power failure.
$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;
}
################################################################################
@@ -78,21 +53,15 @@ sub database_login {
################################################################################
sub dump_database {
my $self = shift;
-
- # filename / time
my $time = strftime('%Y-%m-%d-%H-%M',localtime);
# read db credentials from db login
my @db_type = split ':', $self->{dblogin}->[0];
$db_type[2] =~ s/dbname=//;
-
- # split db path
my @db_path = split '/', $db_type[2];
- # use pg_dump to dump Postgresql databases
+ # make a copy of the database file
system("cp $db_type[2] $self->{root}/data/dumps/SQLite-$time-$db_path[-1]");
-
- # log
$self->log("dump", "Dumping database to /data/dumps/SQLite-$time-$db_path[-1]");
}
diff --git a/lib/MasterServer/Database/SQLite/dbGetServers.pm b/lib/MasterServer/Database/SQLite/dbGetServers.pm
index 719e00a..7ddce2b 100755
--- a/lib/MasterServer/Database/SQLite/dbGetServers.pm
+++ b/lib/MasterServer/Database/SQLite/dbGetServers.pm
@@ -3,37 +3,35 @@ package MasterServer::Database::SQLite::dbGetServers;
use strict;
use warnings;
use Exporter 'import';
-
our @EXPORT = qw| get_server
get_pending |;
################################################################################
## get server details for one or multiple servers
-## opts: limit, see $order
+## opts: limit, see %where or $order
################################################################################
sub get_server {
my $s = shift;
- my %o = (
- sort => '',
- @_
- );
+ my %o = (sort => '', blacklisted => 0, @_);
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{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{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})) : (),
+
+ # never process blacklisted servers, unless explicitly specified
+ ('blacklisted = CAST(? AS BOOLEAN)' => $o{blacklisted}),
);
my @select = ( qw|
@@ -79,45 +77,29 @@ sub get_server {
################################################################################
## get server details for one or multiple pending servers
-## opts: limit, next_id, beaconport, heartbeat, gamename, secure, enctype, added
+## opts: limit, next_id, ip, heartbeat port
################################################################################
sub get_pending {
my $s = shift;
- my %o = (
- sort => '',
- @_
- );
+ 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 @select = ( qw| id ip heartbeat 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 ?" : ""),
+ return $s->db_all( q| SELECT !s FROM pending !W ORDER BY !s|
+ .($o{limit} ? " LIMIT ?" : ""),
join(', ', @select), \%where, $order, ($o{limit} ? $o{limit} : ()),
);
}
diff --git a/lib/MasterServer/Database/SQLite/dbMaintenance.pm b/lib/MasterServer/Database/SQLite/dbMaintenance.pm
index 019f31f..ec9b5cc 100755
--- a/lib/MasterServer/Database/SQLite/dbMaintenance.pm
+++ b/lib/MasterServer/Database/SQLite/dbMaintenance.pm
@@ -1,9 +1,8 @@
-package MasterServer::Database::SQLite::dbMaintenance;
+package MasterServer::Database::Pg::dbMaintenance;
use strict;
use warnings;
use Exporter 'import';
-
our @EXPORT = qw| delete_old_pending
remove_pending |;
@@ -12,14 +11,10 @@ our @EXPORT = qw| delete_old_pending
## where the server is unresponsive for more than 1 hour
################################################################################
sub delete_old_pending {
- my ($self) = shift;
-
- # remove servers
+ my $self = shift;
my $u = $self->{dbh}->do(
"DELETE FROM pending
- WHERE added < datetime(?, 'unixepoch')", undef, time-3600);
-
- # notify
+ WHERE added < datetime(?, \'unixepoch\')", undef, time-3600);
$self->log("delete", "Removed $u entries from pending.") if ($u > 0);
}
@@ -28,13 +23,11 @@ sub delete_old_pending {
## 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);
+ my $self = shift;
+ my %o = ( @_);
+ my $u = $self->{dbh}->do("DELETE FROM pending WHERE ip = ? AND heartbeat = ?",
+ undef, $o{ip}, $o{port});
+ $self->log("delete", "removed $o{ip}, $o{port} from pending (".$u."x)") if ($u > 0);
}
1;
diff --git a/lib/MasterServer/Database/SQLite/dbStats.pm b/lib/MasterServer/Database/SQLite/dbStats.pm
index 1cf94e6..57d3100 100755
--- a/lib/MasterServer/Database/SQLite/dbStats.pm
+++ b/lib/MasterServer/Database/SQLite/dbStats.pm
@@ -3,41 +3,47 @@ package MasterServer::Database::SQLite::dbStats;
use strict;
use warnings;
use Exporter 'import';
-
-our @EXPORT = qw| get_gamelist_stats
- write_direct_beacons
+our @EXPORT = qw| get_gamenames
+ get_gamestats
+ get_listedstats
write_stat
+ write_direct_beacons
write_kfstats |;
################################################################################
-# calculate stats for all individual games
+## get a list of distinct gamenames currently in the server list
################################################################################
-sub get_gamelist_stats {
+sub get_gamenames {
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);
+ "SELECT distinct gamename
+ FROM serverlist");
}
################################################################################
-# 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.
+## get statistics (num_direct, num_total) per gamename
################################################################################
-sub write_direct_beacons {
+sub get_gamestats {
+ my ($self, $gn) = @_;
+ return $self->db_all(
+ "SELECT COUNT(CASE WHEN b333ms THEN 1 END) as num_uplink, count(*) as num_total
+ FROM serverlist
+ WHERE gamename = ? AND updated > datetime(?, \'unixepoch\')",
+ lc $gn, time-7200);
+}
+
+################################################################################
+## 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_listedstats {
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);
+ return $self->{dbh}->selectall_arrayref(
+ "SELECT gamename
+ FROM games
+ WHERE num_uplink > 0
+ OR num_total > 0");
}
################################################################################
@@ -45,19 +51,28 @@ sub write_direct_beacons {
# 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(
+ my ($self, %o) = @_;
+ return $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);
+ undef, $o{num_uplink}, $o{num_total}, lc $o{gamename});
+}
+################################################################################
+# 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 < datetime(?, \'unixepoch\') AND b333ms",
+ undef, time-3600);
+ $self->log("unset", "Lost $u direct beacons.") if ($u > 0);
}
################################################################################
@@ -66,7 +81,7 @@ sub write_stat {
sub write_kfstats {
my ($self, $h) = @_;
- # check if entry already excists.
+ # check if entry already exists.
my $u = $self->{dbh}->selectall_arrayref(
"SELECT * FROM kfstats WHERE UTkey = ? ", undef, $h->{UTkey});
@@ -74,13 +89,11 @@ sub write_kfstats {
$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
+ $self->{dbh}->do("UPDATE kfstats SET
Username = ?,
CurrentVeterancy = ?,
TotalKills = ?,
diff --git a/lib/MasterServer/Database/SQLite/dbUTServerInfo.pm b/lib/MasterServer/Database/SQLite/dbUTServerInfo.pm
index 119900b..5a579ac 100755
--- a/lib/MasterServer/Database/SQLite/dbUTServerInfo.pm
+++ b/lib/MasterServer/Database/SQLite/dbUTServerInfo.pm
@@ -5,127 +5,12 @@ use warnings;
use MasterServer::Core::Util 'sqlprint';
use Exporter 'import';
-our @EXPORT = qw| get_utserver
- add_utserver
+our @EXPORT = qw| 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.
################################################################################
generated by cgit v1.2.3 (git 2.25.1) at 2026-03-13 19:52:01 +0000