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
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
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;
|
