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
|
package MasterServer::Database::Pg::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
################################################################################
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 < 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
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 < to_timestamp(?)' => (time-$o{added})) : (),
$o{after} ? ('added > to_timestamp(?)' => (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} : ()),
);
}
1;
|
