BF2MC-Matchmaker
leaderboard_ratio.cpp
1 #include <iostream>
2 #include <mysql/mysql.h>
3 #include <string>
4 
5 #include <settings.h>
6 #include <logger.h>
7 #include <util.h>
8 
9 #include <database.h>
10 
11 bool Database::queryLeaderboardRatio(Battlefield::RankPlayers& rank_players, const std::string& k, const std::string& s,
12  uint32_t limit, uint32_t offset)
13 {
14  std::lock_guard<std::mutex> guard(this->_mutex); // database lock
15 
16  std::string query = "";
17  query += "SELECT ";
18  query += " `rank`, `profileid`, `uniquenick`, `ratio`, `" + k + "`, `" + s + "` ";
19  query += "FROM ";
20  query += " `Leaderboard_ratio_" + k + "_" + s + "` ";
21  query += "ORDER BY ";
22  query += " `rank` ASC, ";
23  query += " `ratio` DESC ";
24  query += "LIMIT ? OFFSET ?";
25 
26  int output_rank;
27  int output_profileid;
28  char output_uniquenick[VARCHAR_LEN(32)];
29  int output_ratio;
30  uint32_t output_k;
31  uint32_t output_s;
32 
33  // Allocate input binds
34  MYSQL_BIND* input_bind = (MYSQL_BIND *)calloc(2, sizeof(MYSQL_BIND));
35  input_bind[0].buffer_type = MYSQL_TYPE_LONG;
36  input_bind[0].buffer = &limit;
37  input_bind[0].is_unsigned = true;
38  input_bind[1].buffer_type = MYSQL_TYPE_LONG;
39  input_bind[1].buffer = &offset;
40  input_bind[1].is_unsigned = true;
41 
42  // Allocate output binds
43  MYSQL_BIND* output_bind = (MYSQL_BIND *)calloc(6, sizeof(MYSQL_BIND));
44  output_bind[0].buffer_type = MYSQL_TYPE_LONG;
45  output_bind[0].buffer = &output_rank;
46  output_bind[0].is_unsigned = false;
47  output_bind[1].buffer_type = MYSQL_TYPE_LONG;
48  output_bind[1].buffer = &output_profileid;
49  output_bind[1].is_unsigned = false;
50  output_bind[2].buffer_type = MYSQL_TYPE_VAR_STRING;
51  output_bind[2].buffer = &output_uniquenick;
52  output_bind[2].buffer_length = VARCHAR_LEN(32);
53  output_bind[3].buffer_type = MYSQL_TYPE_LONG;
54  output_bind[3].buffer = &output_ratio;
55  output_bind[3].is_unsigned = false;
56  output_bind[4].buffer_type = MYSQL_TYPE_LONG;
57  output_bind[4].buffer = &output_k;
58  output_bind[4].is_unsigned = true;
59  output_bind[5].buffer_type = MYSQL_TYPE_LONG;
60  output_bind[5].buffer = &output_s;
61  output_bind[5].is_unsigned = true;
62 
63  // Prepare and execute with binds
64  MYSQL_STMT* statement;
65 
66  if(
67  !this->_init(&statement) ||
68  !this->_prepare(statement, query, input_bind) ||
69  !this->_execute(statement, output_bind)
70  )
71  {
72  // Cleanup
73  free(input_bind);
74  free(output_bind);
75 
76  return false;
77  }
78 
79  // Fetch and process rows
80  while (true)
81  {
82  int status = mysql_stmt_fetch(statement);
83 
84  if (status == 1 || status == MYSQL_NO_DATA)
85  break;
86 
87  Battlefield::Player player;
88 
89  player.SetProfileId(output_profileid);
90  player.SetUniquenick(output_uniquenick);
91 
92  auto it = Battlefield::PlayerStats::SetterMap.find(k);
93  if (it != Battlefield::PlayerStats::SetterMap.end()) {
94  (player.*(it->second))(output_k);
95  }
96 
97  it = Battlefield::PlayerStats::SetterMap.find(s);
98  if (it != Battlefield::PlayerStats::SetterMap.end()) {
99  (player.*(it->second))(output_s);
100  }
101 
102  rank_players.insert(std::make_pair(output_rank, player));
103  }
104 
105  // Cleanup
106  mysql_stmt_free_result(statement);
107  mysql_stmt_close(statement);
108  free(input_bind);
109  free(output_bind);
110 
111  return true;
112 }
113 
114 bool Database::queryLeaderboardRatioByProfileid(Battlefield::RankPlayers& rank_players, int profileid, const std::string& k,
115  const std::string& s)
116 {
117  std::lock_guard<std::mutex> guard(this->_mutex); // database lock
118 
119  std::string query = "";
120  query += "SELECT `rp`.* ";
121  query += "FROM ";
122  query += " `Leaderboard_ratio_" + k + "_" + s + "` AS `rp` ";
123  query += "JOIN ( ";
124  query += " SELECT ";
125  query += " `profileid`, ";
126  query += " `rank` AS `start_rank` ";
127  query += " FROM ";
128  query += " `Leaderboard_ratio_" + k + "_" + s + "` ";
129  query += " WHERE ";
130  query += " `profileid` = ? ";
131  query += ") AS `start` ON rp.rank >= CASE WHEN start.start_rank <= 4 THEN 1 ELSE start.start_rank - 4 END ";
132  query += "JOIN ( ";
133  query += " SELECT ";
134  query += " `profileid`, ";
135  query += " `rank` AS `end_rank` ";
136  query += " FROM ";
137  query += " `Leaderboard_ratio_" + k + "_" + s + "` ";
138  query += " WHERE ";
139  query += " `profileid` = ? ";
140  query += ") AS `end` ON rp.rank <= CASE WHEN end.end_rank <= 4 THEN 10 ELSE end.end_rank + 5 END ";
141  query += "ORDER BY ";
142  query += " `rank` ASC, ";
143  query += " `ratio` DESC";
144 
145  int input_profileid = profileid;
146 
147  int output_rank;
148  int output_profileid;
149  char output_uniquenick[VARCHAR_LEN(32)];
150  int output_ratio;
151  uint32_t output_k;
152  uint32_t output_s;
153 
154  // Allocate input binds
155  MYSQL_BIND* input_bind = (MYSQL_BIND *)calloc(2, sizeof(MYSQL_BIND));
156  input_bind[0].buffer_type = MYSQL_TYPE_LONG;
157  input_bind[0].buffer = &input_profileid;
158  input_bind[0].is_unsigned = false;
159  input_bind[1].buffer_type = MYSQL_TYPE_LONG;
160  input_bind[1].buffer = &input_profileid;
161  input_bind[1].is_unsigned = false;
162 
163  // Allocate output binds
164  MYSQL_BIND* output_bind = (MYSQL_BIND *)calloc(6, sizeof(MYSQL_BIND));
165  output_bind[0].buffer_type = MYSQL_TYPE_LONG;
166  output_bind[0].buffer = &output_rank;
167  output_bind[0].is_unsigned = false;
168  output_bind[1].buffer_type = MYSQL_TYPE_LONG;
169  output_bind[1].buffer = &output_profileid;
170  output_bind[1].is_unsigned = false;
171  output_bind[2].buffer_type = MYSQL_TYPE_VAR_STRING;
172  output_bind[2].buffer = &output_uniquenick;
173  output_bind[2].buffer_length = VARCHAR_LEN(32);
174  output_bind[3].buffer_type = MYSQL_TYPE_LONG;
175  output_bind[3].buffer = &output_ratio;
176  output_bind[3].is_unsigned = false;
177  output_bind[4].buffer_type = MYSQL_TYPE_LONG;
178  output_bind[4].buffer = &output_k;
179  output_bind[4].is_unsigned = true;
180  output_bind[5].buffer_type = MYSQL_TYPE_LONG;
181  output_bind[5].buffer = &output_s;
182  output_bind[5].is_unsigned = true;
183 
184  // Prepare and execute with binds
185  MYSQL_STMT* statement;
186 
187  if(
188  !this->_init(&statement) ||
189  !this->_prepare(statement, query, input_bind) ||
190  !this->_execute(statement, output_bind)
191  )
192  {
193  // Cleanup
194  free(input_bind);
195  free(output_bind);
196 
197  return false;
198  }
199 
200  // Fetch and process rows
201  while (true)
202  {
203  int status = mysql_stmt_fetch(statement);
204 
205  if (status == 1 || status == MYSQL_NO_DATA)
206  break;
207 
208  Battlefield::Player player;
209 
210  player.SetProfileId(output_profileid);
211  player.SetUniquenick(output_uniquenick);
212 
213  auto it = Battlefield::PlayerStats::SetterMap.find(k);
214  if (it != Battlefield::PlayerStats::SetterMap.end()) {
215  (player.*(it->second))(output_k);
216  }
217 
218  it = Battlefield::PlayerStats::SetterMap.find(s);
219  if (it != Battlefield::PlayerStats::SetterMap.end()) {
220  (player.*(it->second))(output_s);
221  }
222 
223  rank_players.insert(std::make_pair(output_rank, player));
224  }
225 
226  // Cleanup
227  mysql_stmt_free_result(statement);
228  mysql_stmt_close(statement);
229  free(input_bind);
230  free(output_bind);
231 
232  return true;
233 }
234 
235 bool Database::queryLeaderboardRatioByFriends(Battlefield::RankPlayers& rank_players, const std::vector<int>& friends,
236  const std::string& k, const std::string& s)
237 {
238  std::lock_guard<std::mutex> guard(this->_mutex); // database lock
239 
240  std::string query = "";
241  query += "SELECT ";
242  query += " ROW_NUMBER() OVER (";
243  query += " ORDER BY ";
244  query += " PlayerStats." + k + " / PlayerStats." + s + " DESC ";
245  query += " ) AS `rank`, ";
246  query += " Players.profileid AS `profileid`, ";
247  query += " Players.uniquenick AS `uniquenick`, ";
248  query += " PlayerStats." + k + " / PlayerStats." + s + " AS `ratio`, ";
249  query += " PlayerStats." + k + " AS `" + k + "`, ";
250  query += " PlayerStats." + s + " AS `" + s + "` ";
251  query += "FROM ";
252  query += " `Players`, ";
253  query += " `PlayerStats` ";
254  query += "WHERE ";
255  query += " Players.profileid = PlayerStats.profileid AND ";
256  query += " PlayerStats." + k + " != 0 AND ";
257  query += " PlayerStats." + s + " != 0 ";
258  query += "AND ";
259  query += " Players.profileid IN (" + Util::ToString(friends) + ") ";
260  query += "ORDER BY ";
261  query += " `rank` ASC, ";
262  query += " `ratio` DESC ";
263  query += "LIMIT 10;";
264 
265  int output_rank;
266  int output_profileid;
267  char output_uniquenick[VARCHAR_LEN(32)];
268  int output_ratio;
269  uint32_t output_k;
270  uint32_t output_s;
271 
272  // Allocate output binds
273  MYSQL_BIND* output_bind = (MYSQL_BIND *)calloc(6, sizeof(MYSQL_BIND));
274  output_bind[0].buffer_type = MYSQL_TYPE_LONG;
275  output_bind[0].buffer = &output_rank;
276  output_bind[0].is_unsigned = false;
277  output_bind[1].buffer_type = MYSQL_TYPE_LONG;
278  output_bind[1].buffer = &output_profileid;
279  output_bind[1].is_unsigned = false;
280  output_bind[2].buffer_type = MYSQL_TYPE_VAR_STRING;
281  output_bind[2].buffer = &output_uniquenick;
282  output_bind[2].buffer_length = VARCHAR_LEN(32);
283  output_bind[3].buffer_type = MYSQL_TYPE_LONG;
284  output_bind[3].buffer = &output_ratio;
285  output_bind[3].is_unsigned = false;
286  output_bind[4].buffer_type = MYSQL_TYPE_LONG;
287  output_bind[4].buffer = &output_k;
288  output_bind[4].is_unsigned = true;
289  output_bind[5].buffer_type = MYSQL_TYPE_LONG;
290  output_bind[5].buffer = &output_s;
291  output_bind[5].is_unsigned = true;
292 
293  // Prepare and execute with binds
294  MYSQL_STMT* statement;
295 
296  if(
297  !this->_init(&statement) ||
298  !this->_prepare(statement, query) ||
299  !this->_execute(statement, output_bind)
300  )
301  {
302  // Cleanup
303  free(output_bind);
304 
305  return false;
306  }
307 
308  // Fetch and process rows
309  while (true)
310  {
311  int status = mysql_stmt_fetch(statement);
312 
313  if (status == 1 || status == MYSQL_NO_DATA)
314  break;
315 
316  Battlefield::Player player;
317 
318  player.SetProfileId(output_profileid);
319  player.SetUniquenick(output_uniquenick);
320 
321  auto it = Battlefield::PlayerStats::SetterMap.find(k);
322  if (it != Battlefield::PlayerStats::SetterMap.end()) {
323  (player.*(it->second))(output_k);
324  }
325 
326  it = Battlefield::PlayerStats::SetterMap.find(s);
327  if (it != Battlefield::PlayerStats::SetterMap.end()) {
328  (player.*(it->second))(output_s);
329  }
330 
331  rank_players.insert(std::make_pair(output_rank, player));
332  }
333 
334  // Cleanup
335  mysql_stmt_free_result(statement);
336  mysql_stmt_close(statement);
337  free(output_bind);
338 
339  return true;
340 }
Battlefield::Player
Represents a player with extended statistics.
Definition: player.h:38
Database::queryLeaderboardRatioByFriends
bool queryLeaderboardRatioByFriends(Battlefield::RankPlayers &rank_players, const std::vector< int > &friends, const std::string &k, const std::string &s)
Queries the leaderboard rank of players by kills-to-spawns ratio filtered by friends.
Definition: leaderboard_ratio.cpp:235
Database::_prepare
bool _prepare(MYSQL_STMT *statement, const std::string &query)
Prepares a MySQL statement with a query.
Definition: database.cpp:59
Database::queryLeaderboardRatioByProfileid
bool queryLeaderboardRatioByProfileid(Battlefield::RankPlayers &rank_players, int profileid, const std::string &k, const std::string &s)
Queries the leaderboard rank of players by kills-to-spawns ratio with a specified profile ID.
Definition: leaderboard_ratio.cpp:114
Database::queryLeaderboardRatio
bool queryLeaderboardRatio(Battlefield::RankPlayers &rank_players, const std::string &k, const std::string &s, uint32_t limit=10, uint32_t offset=0)
Queries the leaderboard rank of players by kills-to-spawns ratio.
Definition: leaderboard_ratio.cpp:11
Database::_init
bool _init(MYSQL_STMT **statement)
Initializes a MySQL statement object.
Definition: database.cpp:45
Database::_execute
bool _execute(MYSQL_STMT *statement)
Executes a prepared MySQL statement.
Definition: database.cpp:94
Database::_mutex
std::mutex _mutex
Mutex for thread-safe access to the database connection.
Definition: database.h:42
Generated by doxygen 1.9.1