1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
|
package MasterWebInterface::Database::Pg::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 > to_timestamp(?)' => (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(SUBSTRING(hostname from 1 for 1)) = ?' => $o{firstchar} ) : (),
!$o{firstchar} && defined $o{firstchar}
? ('ASCII(hostname) < 97 OR ASCII(hostname) > 122' => 1 ) : (),
$o{search}
? ('hostname ILIKE ?' => "%$o{search}%") : (),
$o{updated}
? ('serverlist.updated > to_timestamp(?)' => (time-$o{updated}) ) : (),
$o{filter}
? ('blacklisted = CAST(? AS BOOLEAN)' => 0) : (),
('length(hostname) > ?' => 1), # don't allow 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 |,
"extract('epoch' from CURRENT_TIMESTAMP - serverlist.updated) as diff",
"extract('epoch' from serverlist.updated) as updated",
"extract('epoch' from 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;
|
