The Battle for Wesnoth  1.19.13+dev
dbconn.cpp
Go to the documentation of this file.
1 /*
2  Copyright (C) 2020 - 2025
3  Part of the Battle for Wesnoth Project https://www.wesnoth.org/
4 
5  This program is free software; you can redistribute it and/or modify
6  it under the terms of the GNU General Public License as published by
7  the Free Software Foundation; either version 2 of the License, or
8  (at your option) any later version.
9  This program is distributed in the hope that it will be useful,
10  but WITHOUT ANY WARRANTY.
11 
12  See the COPYING file for more details.
13 */
14 
15 #ifdef HAVE_MYSQLPP
16 
17 #include "server/common/dbconn.hpp"
18 
19 #include "log.hpp"
20 #include "serialization/parser.hpp"
23 #include "utils/general.hpp"
24 
25 #include <type_traits>
26 
27 static lg::log_domain log_sql_handler("sql_executor");
28 #define ERR_SQL LOG_STREAM(err, log_sql_handler)
29 #define WRN_SQL LOG_STREAM(warn, log_sql_handler)
30 #define LOG_SQL LOG_STREAM(info, log_sql_handler)
31 #define DBG_SQL LOG_STREAM(debug, log_sql_handler)
32 
33 dbconn::dbconn(const config& c)
34  : db_users_table_(c["db_users_table"].str())
35  , db_banlist_table_(c["db_banlist_table"].str())
36  , db_extra_table_(c["db_extra_table"].str())
37  , db_game_info_table_(c["db_game_info_table"].str())
38  , db_game_player_info_table_(c["db_game_player_info_table"].str())
39  , db_game_content_info_table_(c["db_game_content_info_table"].str())
40  , db_user_group_table_(c["db_user_group_table"].str())
41  , db_tournament_query_(c["db_tournament_query"].str())
42  , db_topics_table_(c["db_topics_table"].str())
43  , db_addon_info_table_(c["db_addon_info_table"].str())
44  , db_connection_history_table_(c["db_connection_history_table"].str())
45  , db_addon_authors_table_(c["db_addon_authors_table"].str())
46 {
47  try
48  {
49  account_ = mariadb::account::create(c["db_host"].str(), c["db_user"].str(), c["db_password"].str());
50  account_->set_connect_option(mysql_option::MYSQL_SET_CHARSET_NAME, std::string("utf8mb4"));
51  account_->set_schema(c["db_name"].str());
52  // initialize the connection used to run synchronous queries.
53  connection_ = create_connection();
54  }
55  catch(const mariadb::exception::base& e)
56  {
57  log_sql_exception("Failed to connect to the database!", e);
58  }
59 }
60 
61 void dbconn::log_sql_exception(const std::string& text, const mariadb::exception::base& e)
62 {
63  ERR_SQL << text << '\n'
64  << "what: " << e.what() << '\n'
65  << "error id: " << e.error_id();
66 }
67 
68 mariadb::connection_ref dbconn::create_connection()
69 {
70  return mariadb::connection::create(account_);
71 }
72 
73 //
74 // queries
75 //
76 int dbconn::async_test_query(int limit)
77 {
78  std::string sql = "with recursive TEST(T) as "
79  "( "
80  "select 1 "
81  "union all "
82  "select T+1 from TEST where T < ? "
83  ") "
84  "select count(*) from TEST";
85  int t = get_single_long(create_connection(), sql, { limit });
86  return t;
87 }
88 
89 std::string dbconn::get_uuid()
90 {
91  try
92  {
93  return get_single_string(connection_, "SELECT UUID()", {});
94  }
95  catch(const mariadb::exception::base& e)
96  {
97  log_sql_exception("Could not retrieve a UUID!", e);
98  return "";
99  }
100 }
101 
102 std::string dbconn::get_tournaments()
103 {
104  if(db_tournament_query_ == "")
105  {
106  return "";
107  }
108 
109  try
110  {
111  mariadb::result_set_ref rslt = select(connection_, db_tournament_query_, {});
112  std::string text;
113  while(rslt->next()) {
114  text += "\n" + rslt->get_string("TEXT");
115  }
116  return text;
117  }
118  catch(const mariadb::exception::base& e)
119  {
120  log_sql_exception("Could not retrieve the tournaments!", e);
121  return "";
122  }
123 }
124 
125 std::unique_ptr<simple_wml::document> dbconn::get_game_history(int player_id, int offset, std::string search_game_name, int search_content_type, std::string search_content)
126 {
127  auto game_history_handler = [](const mariadb::result_set_ref& rslt) {
128  config c;
129 
130  while(rslt->next())
131  {
132  config& child = c.add_child("game");
133  child["game_name"] = rslt->get_string("GAME_NAME");
134  child["game_start"] = rslt->get_date_time("START_TIME").str();
135  child["scenario_name"] = rslt->get_string("SCENARIO_NAME");
136  child["era_name"] = rslt->get_string("ERA_NAME");
137  for(const auto& player_info : utils::split(rslt->get_string("PLAYERS")))
138  {
139  std::vector<std::string> info = utils::split(player_info, ':');
140  config& pchild = child.add_child("player");
141  if(info.size() == 2)
142  {
143  pchild["name"] = info[0];
144  pchild["faction"] = info[1];
145  }
146  else
147  {
148  ERR_SQL << "Expected player information to split into two fields, instead found the value `" << player_info << "`.";
149  }
150  }
151  for(const std::string& mod : utils::split(rslt->get_string("MODIFICATION_NAMES")))
152  {
153  config& mchild = child.add_child("modification");
154  mchild["name"] = mod;
155  }
156  child["replay_url"] = rslt->get_string("REPLAY_URL");
157  child["version"] = rslt->get_string("VERSION");
158  }
159 
160  return c;
161  };
162 
163  try
164  {
165  // if no parameters populated, return an error
166  if(player_id == 0 && search_game_name.empty() && search_content.empty())
167  {
168  ERR_SQL << "Skipping game history query due to lack of search parameters.";
169  auto doc = std::make_unique<simple_wml::document>();
170  doc->set_attr("error", "No search parameters provided.");
171  return doc;
172  }
173 
174  sql_parameters params;
175 
176  std::string game_history_query = "select "
177 " game.GAME_NAME, "
178 " game.START_TIME, "
179 " GROUP_CONCAT(CONCAT(player.USER_NAME, ':', player.FACTION)) as PLAYERS, "
180 " IFNULL(scenario.NAME, '') as SCENARIO_NAME, "
181 " IFNULL(era.NAME, '') as ERA_NAME, "
182 " IFNULL((select GROUP_CONCAT(distinct mods.NAME) from "+db_game_content_info_table_+" mods where mods.TYPE = 'modification' and mods.INSTANCE_UUID = game.INSTANCE_UUID and mods.GAME_ID = game.GAME_ID), '') as MODIFICATION_NAMES, "
183 " case "
184 " when game.PUBLIC = 1 and game.INSTANCE_VERSION != 'trunk' "
185 " then concat('https://replays.wesnoth.org/', substring(game.INSTANCE_VERSION, 1, 4), '/', year(game.END_TIME), '/', lpad(month(game.END_TIME), 2, '0'), '/', lpad(day(game.END_TIME), 2, '0'), '/', game.REPLAY_NAME) "
186 " when game.PUBLIC = 1 and game.INSTANCE_VERSION = 'trunk' "
187 " then concat('https://replays.wesnoth.org/', game.INSTANCE_VERSION, '/', year(game.END_TIME), '/', lpad(month(game.END_TIME), 2, '0'), '/', lpad(day(game.END_TIME), 2, '0'), '/', game.REPLAY_NAME) "
188 " else '' "
189 " end as REPLAY_URL, "
190 " case "
191 " when game.INSTANCE_VERSION != 'trunk' "
192 " then SUBSTRING(game.INSTANCE_VERSION, 1, 4) "
193 " else 'trunk' "
194 " end as VERSION "
196  // modification id optional parameter
197  if(search_content_type == 2 && !search_content.empty())
198  {
199  game_history_query += ", "+db_game_content_info_table_+" mods "
200 " where mods.TYPE = 'modification' "
201 " and mods.INSTANCE_UUID = game.INSTANCE_UUID "
202 " and mods.GAME_ID = game.GAME_ID "
203 " and mods.ID like ? ";
204 
205  utils::to_sql_wildcards(search_content, false);
206  params.emplace_back(search_content);
207  }
208  else
209  {
210  // put the where clause with an always true condition here so I don't need to check again a couple lines down whether it needs "where" vs "and"
211  game_history_query += "where true ";
212  }
213 
214  game_history_query += "and exists "
215 " ( "
216 " select 1 "
217 " from "+db_game_player_info_table_+" player1 "
218 " where game.INSTANCE_UUID = player1.INSTANCE_UUID "
219 " and game.GAME_ID = player1.GAME_ID ";
220 
221  if(player_id != 0)
222  {
223  game_history_query += " and player1.USER_ID = ? ";
224  params.emplace_back(player_id);
225  }
226 
227  game_history_query += " ) "
228 " and game.INSTANCE_UUID = player.INSTANCE_UUID "
229 " and game.GAME_ID = player.GAME_ID "
230 " and player.USER_ID != -1 "
231 " and game.END_TIME is not NULL "
232 " and scenario.TYPE = 'scenario' "
233 " and scenario.INSTANCE_UUID = game.INSTANCE_UUID "
234 " and scenario.GAME_ID = game.GAME_ID "
235 " and era.TYPE = 'era' "
236 " and era.INSTANCE_UUID = game.INSTANCE_UUID "
237 " and era.GAME_ID = game.GAME_ID ";
238  // game name optional paramenter
239  if(!search_game_name.empty())
240  {
241  game_history_query += "and game.GAME_NAME like ? ";
242 
243  utils::to_sql_wildcards(search_game_name, false);
244  params.emplace_back(search_game_name);
245  }
246 
247  // scenario id optional parameter
248  if(search_content_type == 0 && !search_content.empty())
249  {
250  game_history_query += "and scenario.ID like ? ";
251 
252  utils::to_sql_wildcards(search_content, false);
253  params.emplace_back(search_content);
254  }
255 
256  // era id optional parameter
257  if(search_content_type == 1 && !search_content.empty())
258  {
259  game_history_query += "and era.ID like ? ";
260 
261  utils::to_sql_wildcards(search_content, false);
262  params.emplace_back(search_content);
263  }
264 
265  game_history_query += "group by game.INSTANCE_UUID, game.GAME_ID, SCENARIO_NAME, ERA_NAME "
266 "order by game.START_TIME desc "
267 "limit 11 offset ? ";
268  params.emplace_back(offset);
269 
270  DBG_SQL << "before getting connection for game history query for player " << player_id;
271 
272  mariadb::connection_ref connection = create_connection();
273 
274  DBG_SQL << "game history query text for player " << player_id << ": " << game_history_query;
275 
276  config history = get_complex_results(connection, &game_history_handler, game_history_query, params);
277 
278  DBG_SQL << "after game history query for player " << player_id;
279 
280  auto doc = std::make_unique<simple_wml::document>();
281 
282  simple_wml::node& results_wml = doc->root().add_child("game_history_results");
283 
284  for(const auto& result : history.child_range("game"))
285  {
286  simple_wml::node& ghr = results_wml.add_child("game_history_result");
287  ghr.set_attr_dup("game_name", result["game_name"].str().c_str());
288  ghr.set_attr_dup("game_start", result["game_start"].str().c_str());
289  ghr.set_attr_dup("scenario_name", result["scenario_name"].str().c_str());
290  ghr.set_attr_dup("era_name", result["era_name"].str().c_str());
291  ghr.set_attr_dup("replay_url", result["replay_url"].str().c_str());
292  ghr.set_attr_dup("version", result["version"].str().c_str());
293  for(const auto& player : result.child_range("player"))
294  {
295  simple_wml::node& p = ghr.add_child("player");
296  p.set_attr_dup("name", player["name"].str().c_str());
297  p.set_attr_dup("faction", player["faction"].str().c_str());
298  }
299  for(const auto& mod : result.child_range("modification"))
300  {
301  simple_wml::node& m = ghr.add_child("modification");
302  m.set_attr_dup("name", mod["name"].str().c_str());
303  }
304  }
305 
306  DBG_SQL << "after parsing results of game history query for player " << player_id;
307 
308  return doc;
309  }
310  catch(const mariadb::exception::base& e)
311  {
312  log_sql_exception("Could not retrieve the game history for forum ID `"+std::to_string(player_id)+"`!", e);
313  auto doc = std::make_unique<simple_wml::document>();
314  doc->set_attr("error", "Error retrieving game history.");
315  return doc;
316  }
317 }
318 
319 bool dbconn::user_exists(const std::string& name)
320 {
321  try
322  {
323  return exists(connection_, "SELECT 1 FROM `"+db_users_table_+"` WHERE UPPER(username)=UPPER(?)", { name });
324  }
325  catch(const mariadb::exception::base& e)
326  {
327  log_sql_exception("Unable to check if user row for '"+name+"' exists!", e);
328  return false;
329  }
330 }
331 
332 long dbconn::get_forum_id(const std::string& name)
333 {
334  try
335  {
336  return get_single_long(connection_, "SELECT IFNULL((SELECT user_id FROM `"+db_users_table_+"` WHERE UPPER(username)=UPPER(?)), 0)", { name });
337  }
338  catch(const mariadb::exception::base& e)
339  {
340  log_sql_exception("Unable to get user_id for '"+name+"'!", e);
341  return 0;
342  }
343 }
344 
345 bool dbconn::extra_row_exists(const std::string& name)
346 {
347  try
348  {
349  return exists(connection_, "SELECT 1 FROM `"+db_extra_table_+"` WHERE UPPER(username)=UPPER(?)", { name });
350  }
351  catch(const mariadb::exception::base& e)
352  {
353  log_sql_exception("Unable to check if extra row for '"+name+"' exists!", e);
354  return false;
355  }
356 }
357 
358 bool dbconn::is_user_in_groups(const std::string& name, const std::vector<int>& group_ids)
359 {
360  std::vector<std::string> group_params;
361 
362  sql_parameters params;
363  params.emplace_back(name);
364  for(int group_id : group_ids) {
365  group_params.emplace_back("?");
366  params.emplace_back(group_id);
367  }
368 
369  try
370  {
371  return exists(connection_, "SELECT 1 FROM `"+db_users_table_+"` u, `"+db_user_group_table_+"` ug WHERE UPPER(u.username)=UPPER(?) AND u.USER_ID = ug.USER_ID AND ug.GROUP_ID in ("+utils::join(group_params)+")",
372  params);
373  }
374  catch(const mariadb::exception::base& e)
375  {
376  log_sql_exception("Unable to check if the user '"+name+"' is in groups!", e);
377  return false;
378  }
379 }
380 
381 config dbconn::get_ban_info(const std::string& name, const std::string& ip)
382 {
383  // selected ban_type value must be part of user_handler::BAN_TYPE
384  auto ban_info_handler = [](const mariadb::result_set_ref& rslt) {
385  config c;
386 
387  if(rslt->next()) {
388  c["ban_type"] = rslt->get_signed32("ban_type");
389  c["ban_end"] = rslt->get_signed32("ban_end");
390  c["user_id"] = rslt->get_signed32("ban_userid");
391  c["email"] = rslt->get_string("ban_email");
392  }
393 
394  return c;
395  };
396 
397  try
398  {
399  return get_complex_results(connection_, &ban_info_handler, "select ban_userid, ban_email, case when ban_ip != '' then 1 when ban_userid != 0 then 2 when ban_email != '' then 3 end as ban_type, ban_end from `"+db_banlist_table_+"` where (ban_ip = ? or ban_userid = (select user_id from `"+db_users_table_+"` where UPPER(username) = UPPER(?)) or UPPER(ban_email) = (select UPPER(user_email) from `"+db_users_table_+"` where UPPER(username) = UPPER(?))) AND ban_exclude = 0 AND (ban_end = 0 OR ban_end >= ?)",
400  { ip, name, name, std::chrono::system_clock::to_time_t(std::chrono::system_clock::now()) });
401  }
402  catch(const mariadb::exception::base& e)
403  {
404  log_sql_exception("Failed to check ban info for user '"+name+"' connecting from ip '"+ip+"'!", e);
405  return {};
406  }
407 }
408 
409 std::string dbconn::get_user_string(const std::string& table, const std::string& column, const std::string& name)
410 {
411  try
412  {
413  return get_single_string(connection_, "SELECT `"+column+"` from `"+table+"` WHERE UPPER(username)=UPPER(?)", { name });
414  }
415  catch(const mariadb::exception::base& e)
416  {
417  log_sql_exception("Unable to query column `"+column+"` from table `"+table+"` for user `"+name+"`", e);
418  return "";
419  }
420 }
421 int dbconn::get_user_int(const std::string& table, const std::string& column, const std::string& name)
422 {
423  try
424  {
425  return static_cast<int>(get_single_long(connection_, "SELECT `"+column+"` from `"+table+"` WHERE UPPER(username)=UPPER(?)", { name }));
426  }
427  catch(const mariadb::exception::base& e)
428  {
429  log_sql_exception("Unable to query column `"+column+"` from table `"+table+"` for user `"+name+"`", e);
430  return 0;
431  }
432 }
433 void dbconn::write_user_int(const std::string& column, const std::string& name, int value)
434 {
435  try
436  {
437  if(!extra_row_exists(name))
438  {
439  modify(connection_, "INSERT INTO `"+db_extra_table_+"` VALUES(?,?,'0')", { name, value });
440  }
441  modify(connection_, "UPDATE `"+db_extra_table_+"` SET "+column+"=? WHERE UPPER(username)=UPPER(?)", { value, name });
442  }
443  catch(const mariadb::exception::base& e)
444  {
445  log_sql_exception("Unable to write `"+std::to_string(value)+"` to column `"+column+"` on table `"+db_extra_table_+"` for user `"+name+"`", e);
446  }
447 }
448 
449 void dbconn::insert_game_info(const std::string& uuid, int game_id, const std::string& version, const std::string& name, int reload, int observers, int is_public, int has_password)
450 {
451  try
452  {
453  modify(connection_, "INSERT INTO `"+db_game_info_table_+"`(INSTANCE_UUID, GAME_ID, INSTANCE_VERSION, GAME_NAME, RELOAD, OBSERVERS, PUBLIC, PASSWORD) VALUES(?, ?, ?, ?, ?, ?, ?, ?)",
454  { uuid, game_id, version, name, reload, observers, is_public, has_password });
455  }
456  catch(const mariadb::exception::base& e)
457  {
458  log_sql_exception("Failed to insert game info row for UUID `"+uuid+"` and game ID `"+std::to_string(game_id)+"`", e);
459  }
460 }
461 void dbconn::update_game_end(const std::string& uuid, int game_id, const std::string& replay_location)
462 {
463  try
464  {
465  modify(connection_, "UPDATE `"+db_game_info_table_+"` SET END_TIME = CURRENT_TIMESTAMP, REPLAY_NAME = ? WHERE INSTANCE_UUID = ? AND GAME_ID = ?",
466  { replay_location, uuid, game_id });
467  }
468  catch(const mariadb::exception::base& e)
469  {
470  log_sql_exception("Failed to update the game end for game info row for UUID `"+uuid+"` and game ID `"+std::to_string(game_id)+"`", e);
471  }
472 }
473 void dbconn::insert_game_player_info(const std::string& uuid, int game_id, const std::string& username, int side_number, int is_host, const std::string& faction, const std::string& version, const std::string& source, const std::string& current_user, const std::string& leaders)
474 {
475  try
476  {
477  modify(connection_, "INSERT INTO `"+db_game_player_info_table_+"`(INSTANCE_UUID, GAME_ID, USER_ID, SIDE_NUMBER, IS_HOST, FACTION, CLIENT_VERSION, CLIENT_SOURCE, USER_NAME, LEADERS) VALUES(?, ?, IFNULL((SELECT user_id FROM `"+db_users_table_+"` WHERE username = ?), -1), ?, ?, ?, ?, ?, ?, ?)",
478  { uuid, game_id, username, side_number, is_host, faction, version, source, current_user, leaders });
479  }
480  catch(const mariadb::exception::base& e)
481  {
482  log_sql_exception("Failed to insert game player info row for UUID `"+uuid+"` and game ID `"+std::to_string(game_id)+"`", e);
483  }
484 }
485 unsigned long long dbconn::insert_game_content_info(const std::string& uuid, int game_id, const std::string& type, const std::string& name, const std::string& id, const std::string& addon_id, const std::string& addon_version)
486 {
487  try
488  {
489  return modify(connection_, "INSERT INTO `"+db_game_content_info_table_+"`(INSTANCE_UUID, GAME_ID, TYPE, NAME, ID, ADDON_ID, ADDON_VERSION) VALUES(?, ?, ?, ?, ?, ?, ?)",
490  { uuid, game_id, type, name, id, addon_id, addon_version });
491  }
492  catch(const mariadb::exception::base& e)
493  {
494  log_sql_exception("Failed to insert game content info row for UUID `"+uuid+"` and game ID `"+std::to_string(game_id)+"`", e);
495  return 0;
496  }
497 }
498 void dbconn::set_oos_flag(const std::string& uuid, int game_id)
499 {
500  try
501  {
502  modify(connection_, "UPDATE `"+db_game_info_table_+"` SET OOS = 1 WHERE INSTANCE_UUID = ? AND GAME_ID = ?",
503  { uuid, game_id });
504  }
505  catch(const mariadb::exception::base& e)
506  {
507  log_sql_exception("Failed to set the OOS flag for UUID `"+uuid+"` and game ID `"+std::to_string(game_id)+"`", e);
508  }
509 }
510 
511 bool dbconn::topic_id_exists(int topic_id) {
512  try
513  {
514  return exists(connection_, "SELECT 1 FROM `"+db_topics_table_+"` WHERE TOPIC_ID = ?",
515  { topic_id });
516  }
517  catch(const mariadb::exception::base& e)
518  {
519  log_sql_exception("Unable to check whether `"+std::to_string(topic_id)+"` exists.", e);
520  return true;
521  }
522 }
523 
524 void dbconn::insert_addon_info(const std::string& instance_version, const std::string& id, const std::string& name, const std::string& type, const std::string& version, bool forum_auth, int topic_id, const std::string& uploader)
525 {
526  try
527  {
528  modify(connection_, "INSERT INTO `"+db_addon_info_table_+"`(INSTANCE_VERSION, ADDON_ID, ADDON_NAME, TYPE, VERSION, FORUM_AUTH, FEEDBACK_TOPIC, UPLOADER) VALUES(?, ?, ?, ?, ?, ?, ?, ?)",
529  { instance_version, id, name, type, version, forum_auth, topic_id, uploader });
530  }
531  catch(const mariadb::exception::base& e)
532  {
533  log_sql_exception("Unable to insert add-on info for add-on `"+id+"` for instance `"+instance_version+"`.", e);
534  }
535 }
536 
537 unsigned long long dbconn::insert_login(const std::string& username, const std::string& ip, const std::string& version)
538 {
539  try
540  {
541  return modify_get_id(connection_, "INSERT INTO `"+db_connection_history_table_+"`(USER_NAME, IP, VERSION) values(lower(?), ?, ?)",
542  { username, ip, version });
543  }
544  catch(const mariadb::exception::base& e)
545  {
546  log_sql_exception("Unable to insert login row user `"+username+"` and ip `"+ip+"`.", e);
547  return 0;
548  }
549 }
550 
551 void dbconn::update_logout(unsigned long long login_id)
552 {
553  try
554  {
555  modify(connection_, "UPDATE `"+db_connection_history_table_+"` SET LOGOUT_TIME = CURRENT_TIMESTAMP WHERE LOGIN_ID = ?",
556  { login_id });
557  }
558  catch(const mariadb::exception::base& e)
559  {
560  log_sql_exception("Unable to update login row `"+std::to_string(login_id)+"`.", e);
561  }
562 }
563 
564 void dbconn::get_users_for_ip(const std::string& ip, std::ostringstream* out)
565 {
566  try
567  {
568  mariadb::result_set_ref rslt = select(connection_, "SELECT USER_NAME, IP, date_format(LOGIN_TIME, '%Y/%m/%d %h:%i:%s'), coalesce(date_format(LOGOUT_TIME, '%Y/%m/%d %h:%i:%s'), '(not set)') FROM `"+db_connection_history_table_+"` WHERE IP LIKE ? order by LOGIN_TIME",
569  { ip });
570 
571  *out << "\nCount of results for ip: " << rslt->row_count();
572 
573  while(rslt->next())
574  {
575  *out << "\nFound user " << rslt->get_string(0) << " with ip " << rslt->get_string(1)
576  << ", logged in at " << rslt->get_string(2) << " and logged out at " << rslt->get_string(3);
577  }
578  }
579  catch(const mariadb::exception::base& e)
580  {
581  log_sql_exception("Unable to select rows for ip `"+ip+"`.", e);
582  }
583 }
584 
585 void dbconn::get_ips_for_user(const std::string& username, std::ostringstream* out)
586 {
587  try
588  {
589  mariadb::result_set_ref rslt = select(connection_, "SELECT USER_NAME, IP, date_format(LOGIN_TIME, '%Y/%m/%d %h:%i:%s'), coalesce(date_format(LOGOUT_TIME, '%Y/%m/%d %h:%i:%s'), '(not set)') FROM `"+db_connection_history_table_+"` WHERE USER_NAME LIKE ? order by LOGIN_TIME",
590  { utf8::lowercase(username) });
591 
592  *out << "\nCount of results for user: " << rslt->row_count();
593 
594  while(rslt->next())
595  {
596  *out << "\nFound user " << rslt->get_string(0) << " with ip " << rslt->get_string(1)
597  << ", logged in at " << rslt->get_string(2) << " and logged out at " << rslt->get_string(3);
598  }
599  }
600  catch(const mariadb::exception::base& e)
601  {
602  log_sql_exception("Unable to select rows for player `"+username+"`.", e);
603  }
604 }
605 
606 void dbconn::update_addon_download_count(const std::string& instance_version, const std::string& id, const std::string& version)
607 {
608  try
609  {
610  modify(connection_, "UPDATE `"+db_addon_info_table_+"` SET DOWNLOAD_COUNT = DOWNLOAD_COUNT+1 WHERE INSTANCE_VERSION = ? AND ADDON_ID = ? AND VERSION = ?",
611  { instance_version, id, version });
612  }
613  catch(const mariadb::exception::base& e)
614  {
615  log_sql_exception("Unable to update download count for add-on "+id+" with version "+version+".", e);
616  }
617 }
618 
619 bool dbconn::is_user_author(const std::string& instance_version, const std::string& id, const std::string& username, int is_primary) {
620  try
621  {
622  return exists(connection_, "SELECT 1 FROM `"+db_addon_authors_table_+"` WHERE INSTANCE_VERSION = ? AND ADDON_ID = ? AND AUTHOR = ? AND IS_PRIMARY = ?",
623  { instance_version, id, username, is_primary });
624  }
625  catch(const mariadb::exception::base& e)
626  {
627  log_sql_exception("Unable to check whether `"+username+"` is an author of "+id+" for version "+instance_version+".", e);
628  return false;
629  }
630 }
631 
632 void dbconn::delete_addon_authors(const std::string& instance_version, const std::string& id) {
633  try
634  {
635  modify(connection_, "DELETE FROM `"+db_addon_authors_table_+"` WHERE INSTANCE_VERSION = ? AND ADDON_ID = ?",
636  { instance_version, id });
637  }
638  catch(const mariadb::exception::base& e)
639  {
640  log_sql_exception("Unable to delete addon authors for "+id+" and version "+instance_version+".", e);
641  }
642 }
643 
644 void dbconn::insert_addon_author(const std::string& instance_version, const std::string& id, const std::string& author, int is_primary) {
645  try
646  {
647  modify(connection_, "INSERT INTO `"+db_addon_authors_table_+"`(INSTANCE_VERSION, ADDON_ID, AUTHOR, IS_PRIMARY) VALUES(?,?,?,?)",
648  { instance_version, id, author, is_primary });
649  }
650  catch(const mariadb::exception::base& e)
651  {
652  log_sql_exception("Unable to delete addon authors for "+id+" and version "+instance_version+".", e);
653  }
654 }
655 
656 bool dbconn::do_any_authors_exist(const std::string& instance_version, const std::string& id) {
657  try
658  {
659  return exists(connection_, "SELECT 1 FROM `"+db_addon_authors_table_+"` WHERE INSTANCE_VERSION = ? AND ADDON_ID = ?",
660  { instance_version, id });
661  }
662  catch(const mariadb::exception::base& e)
663  {
664  log_sql_exception("Unable to check whether authors exist for "+id+" for version "+instance_version+".", e);
665  return true;
666  }
667 }
668 
669 config dbconn::get_addon_downloads_info(const std::string& instance_version, const std::string& id) {
670  auto addon_downloads_handler = [](const mariadb::result_set_ref& rslt) {
671  config c;
672 
673  while(rslt->next()) {
674  config& child = c.add_child("download_info");
675  child["name"] = rslt->get_string("ADDON_NAME");
676  child["version"] = rslt->get_string("VERSION");
677  child["uploaded"] = rslt->get_date_time("UPLOADED_ON").str();
678  child["downloads"] = rslt->get_unsigned32("DOWNLOAD_COUNT");
679  }
680 
681  return c;
682  };
683 
684  try
685  {
686  return get_complex_results(connection_, &addon_downloads_handler, "select ADDON_NAME, VERSION, UPLOADED_ON, DOWNLOAD_COUNT from "+db_addon_info_table_+" where INSTANCE_VERSION = ? and ADDON_ID = ? order by ADDON_NAME, UPLOADED_ON",
687  { instance_version, id });
688  }
689  catch(const mariadb::exception::base& e)
690  {
691  log_sql_exception("Failed to get addon download info!", e);
692  return {};
693  }
694 }
695 
696 config dbconn::get_forum_auth_usage(const std::string& instance_version) {
697  auto forum_auth_usage_handler = [](const mariadb::result_set_ref& rslt) {
698  config c;
699 
700  if(rslt->next()) {
701  c["all_count"] = rslt->get_signed64("ALL_COUNT");
702  c["forum_auth_count"] = rslt->get_signed64("FORUM_AUTH_COUNT");
703  } else {
704  throw mariadb::exception::base("Count query somehow returned no rows!");
705  }
706 
707  return c;
708  };
709 
710  try
711  {
712  return get_complex_results(connection_, &forum_auth_usage_handler, "select (select count(distinct ADDON_ID) from "+db_addon_info_table_+" where INSTANCE_VERSION = ?) as ALL_COUNT, "
713  "(select count(distinct ADDON_ID) from "+db_addon_info_table_+" where INSTANCE_VERSION = ? and FORUM_AUTH = 1) as FORUM_AUTH_COUNT from dual",
714  { instance_version, instance_version });
715  }
716  catch(const mariadb::exception::base& e)
717  {
718  log_sql_exception("Failed to get forum_auth usage!", e);
719  return {};
720  }
721 }
722 
723 config dbconn::get_addon_admins(int site_admin_group, int forum_admin_group) {
724  auto addon_admin_handler = [](const mariadb::result_set_ref& rslt) {
725  config c;
726 
727  while(rslt->next()) {
728  config& child = c.add_child("admin");
729  child["username"] = rslt->get_string("USERNAME");
730  }
731 
732  return c;
733  };
734 
735  try
736  {
737  return get_complex_results(connection_, &addon_admin_handler, "SELECT u.USERNAME FROM `"+db_users_table_+"` u, `"+db_user_group_table_+"` ug WHERE u.USER_ID = ug.USER_ID AND ug.GROUP_ID in (?, ?)",
738  { site_admin_group, forum_admin_group });
739  }
740  catch(const mariadb::exception::base& e)
741  {
742  log_sql_exception("Failed to get addon admins for groups '"+std::to_string(site_admin_group)+"' and '"+std::to_string(forum_admin_group)+"'!", e);
743  return {};
744  }
745 }
746 
747 //
748 // handle complex query results
749 //
750 template <typename F>
751 config dbconn::get_complex_results(const mariadb::connection_ref& connection, F* handler, const std::string& sql, const sql_parameters& params)
752 {
753  mariadb::result_set_ref rslt = select(connection, sql, params);
754  config c = (*handler)(rslt);
755  return c;
756 }
757 //
758 // handle single values
759 //
760 std::string dbconn::get_single_string(const mariadb::connection_ref& connection, const std::string& sql, const sql_parameters& params)
761 {
762  mariadb::result_set_ref rslt = select(connection, sql, params);
763  if(rslt->next())
764  {
765  return rslt->get_string(0);
766  }
767  else
768  {
769  throw mariadb::exception::base("No string value found in the database!");
770  }
771 }
772 long dbconn::get_single_long(const mariadb::connection_ref& connection, const std::string& sql, const sql_parameters& params)
773 {
774  mariadb::result_set_ref rslt = select(connection, sql, params);
775  if(rslt->next())
776  {
777  // mariadbpp checks for strict integral equivalence, but we don't care
778  // so check the type beforehand, call the associated getter, and let it silently get upcast to a long if needed
779  // subselects also apparently return a decimal
780  switch(rslt->column_type(0))
781  {
782  case mariadb::value::type::decimal:
783  return static_cast<long>(rslt->get_decimal(0).float32());
784  case mariadb::value::type::unsigned8:
785  case mariadb::value::type::signed8:
786  return rslt->get_signed8(0);
787  case mariadb::value::type::unsigned16:
788  case mariadb::value::type::signed16:
789  return rslt->get_signed16(0);
790  case mariadb::value::type::unsigned32:
791  case mariadb::value::type::signed32:
792  return rslt->get_signed32(0);
793  case mariadb::value::type::unsigned64:
794  case mariadb::value::type::signed64:
795  return rslt->get_signed64(0);
796  default:
797  throw mariadb::exception::base("Value retrieved was not a long!");
798  }
799  }
800  else
801  {
802  throw mariadb::exception::base("No long value found in the database!");
803  }
804 }
805 bool dbconn::exists(const mariadb::connection_ref& connection, const std::string& sql, const sql_parameters& params)
806 {
807  mariadb::result_set_ref rslt = select(connection, sql, params);
808  return rslt->next();
809 }
810 
811 //
812 // select or modify values
813 //
814 mariadb::result_set_ref dbconn::select(const mariadb::connection_ref& connection, const std::string& sql, const sql_parameters& params)
815 {
816  try
817  {
818  mariadb::statement_ref stmt = query(connection, sql, params);
819  mariadb::result_set_ref rslt = mariadb::result_set_ref(stmt->query());
820  return rslt;
821  }
822  catch(const mariadb::exception::base& e)
823  {
824  ERR_SQL << "SQL query failed for query: `"+sql+"`";
825  throw e;
826  }
827 }
828 unsigned long long dbconn::modify(const mariadb::connection_ref& connection, const std::string& sql, const sql_parameters& params)
829 {
830  try
831  {
832  mariadb::statement_ref stmt = query(connection, sql, params);
833  unsigned long long count = stmt->execute();
834  return count;
835  }
836  catch(const mariadb::exception::base& e)
837  {
838  ERR_SQL << "SQL query failed for query: `"+sql+"`";
839  throw e;
840  }
841 }
842 unsigned long long dbconn::modify_get_id(const mariadb::connection_ref& connection, const std::string& sql, const sql_parameters& params)
843 {
844  try
845  {
846  mariadb::statement_ref stmt = query(connection, sql, params);
847  unsigned long long count = stmt->insert();
848  return count;
849  }
850  catch(const mariadb::exception::base& e)
851  {
852  ERR_SQL << "SQL query failed for query: `"+sql+"`";
853  throw e;
854  }
855 }
856 
857 mariadb::statement_ref dbconn::query(const mariadb::connection_ref& connection, const std::string& sql, const sql_parameters& params)
858 {
859  mariadb::statement_ref stmt = connection->create_statement(sql);
860 
861  unsigned i = 0;
862  for(const auto& param : params)
863  {
864  std::visit([&i, &stmt](const auto& p) {
865  using T = std::remove_cv_t<std::remove_reference_t<decltype(p)>>;
866  if constexpr (std::is_same_v<T, bool>) {
867  stmt->set_boolean(i, p);
868  } else if constexpr (std::is_same_v<T, int>) {
869  stmt->set_signed32(i, p);
870  } else if constexpr (std::is_same_v<T, long>) {
871  stmt->set_signed64(i, p);
872  } else if constexpr (std::is_same_v<T, unsigned long long>) {
873  stmt->set_signed64(i, p);
874  } else if constexpr (std::is_same_v<T, std::string>) {
875  stmt->set_string(i, p);
876  } else if constexpr (std::is_same_v<T, const char*>) {
877  stmt->set_string(i, p);
878  } else {
879  static_assert(utils::dependent_false_v<T>, "Unsupported parameter type in SQL query");
880  }
881  }, param);
882  i++;
883  }
884 
885  return stmt;
886 }
887 
888 #endif //HAVE_MYSQLPP
double t
Definition: astarsearch.cpp:63
A config object defines a single node in a WML file, with access to child nodes.
Definition: config.hpp:158
child_itors child_range(config_key_type key)
Definition: config.cpp:268
config & add_child(config_key_type key)
Definition: config.cpp:436
void delete_addon_authors(const std::string &instance_version, const std::string &id)
void insert_addon_author(const std::string &instance_version, const std::string &id, const std::string &author, int is_primary)
bool do_any_authors_exist(const std::string &instance_version, const std::string &id)
void update_addon_download_count(const std::string &instance_version, const std::string &id, const std::string &version)
config get_addon_downloads_info(const std::string &instance_version, const std::string &id)
std::string db_tournament_query_
The text of the SQL query to use to retrieve any currently active tournaments.
Definition: dbconn.hpp:288
bool topic_id_exists(int topic_id)
bool is_user_in_groups(const std::string &name, const std::vector< int > &group_ids)
int async_test_query(int limit)
bool is_user_author(const std::string &instance_version, const std::string &id, const std::string &username, int is_primary)
mariadb::connection_ref connection_
The actual connection to the database.
Definition: dbconn.hpp:271
bool exists(const mariadb::connection_ref &connection, const std::string &sql, const sql_parameters &params)
long get_forum_id(const std::string &name)
config get_addon_admins(int site_admin_group, int forum_admin_group)
std::unique_ptr< simple_wml::document > get_game_history(int player_id, int offset, std::string search_game_name, int search_content_type, std::string search_content)
This is an asynchronous query that is executed on a separate connection to retrieve the game history ...
void get_ips_for_user(const std::string &username, std::ostringstream *out)
std::string db_addon_authors_table_
The name of the table that contains the add-on authors information.
Definition: dbconn.hpp:296
bool user_exists(const std::string &name)
void get_users_for_ip(const std::string &ip, std::ostringstream *out)
long get_single_long(const mariadb::connection_ref &connection, const std::string &sql, const sql_parameters &params)
std::string get_single_string(const mariadb::connection_ref &connection, const std::string &sql, const sql_parameters &params)
std::string db_banlist_table_
The name of the table that contains forum ban information.
Definition: dbconn.hpp:276
config get_complex_results(const mariadb::connection_ref &connection, F *handler, const std::string &sql, const sql_parameters &params)
unsigned long long insert_login(const std::string &username, const std::string &ip, const std::string &version)
mariadb::connection_ref create_connection()
Creates a new connection object from the account.
std::string db_extra_table_
The name of the table that contains additional user information.
Definition: dbconn.hpp:278
void update_logout(unsigned long long login_id)
std::string db_addon_info_table_
The name of the table that contains add-on information.
Definition: dbconn.hpp:292
void write_user_int(const std::string &column, const std::string &name, int value)
The provided value is updated if a row exists or a new row inserted otherwise.
std::string db_game_content_info_table_
The name of the table that contains game content information.
Definition: dbconn.hpp:284
bool extra_row_exists(const std::string &name)
void log_sql_exception(const std::string &text, const mariadb::exception::base &e)
This is used to write out error text when an SQL-related exception occurs.
std::string db_user_group_table_
The name of the table that contains forum group information.
Definition: dbconn.hpp:286
mariadb::account_ref account_
The account used to connect to the database.
Definition: dbconn.hpp:269
config get_ban_info(const std::string &name, const std::string &ip)
void set_oos_flag(const std::string &uuid, int game_id)
void update_game_end(const std::string &uuid, int game_id, const std::string &replay_location)
std::string db_game_info_table_
The name of the table that contains game-level information.
Definition: dbconn.hpp:280
std::string db_game_player_info_table_
The name of the table that contains player-level information per game.
Definition: dbconn.hpp:282
mariadb::statement_ref query(const mariadb::connection_ref &connection, const std::string &sql, const sql_parameters &params)
For a given connection, set the provided SQL and parameters on a statement.
std::string db_connection_history_table_
The name of the table that contains user connection history.
Definition: dbconn.hpp:294
int get_user_int(const std::string &table, const std::string &column, const std::string &name)
unsigned long long modify(const mariadb::connection_ref &connection, const std::string &sql, const sql_parameters &params)
Executes non-select statements (ie: insert, update, delete).
void insert_addon_info(const std::string &instance_version, const std::string &id, const std::string &name, const std::string &type, const std::string &version, bool forum_auth, int topic_id, const std::string &uploader)
unsigned long long modify_get_id(const mariadb::connection_ref &connection, const std::string &sql, const sql_parameters &params)
Executes non-select statements (ie: insert, update, delete), but primarily intended for inserts that ...
std::string db_topics_table_
The name of the table that contains phpbb forum thread information.
Definition: dbconn.hpp:290
std::string get_user_string(const std::string &table, const std::string &column, const std::string &name)
std::string db_users_table_
The name of the table that contains forum user information.
Definition: dbconn.hpp:274
dbconn(const config &c)
Initializes the synchronous query connection as well as the account object that has the connection se...
void insert_game_info(const std::string &uuid, int game_id, const std::string &version, const std::string &name, int reload, int observers, int is_public, int has_password)
config get_forum_auth_usage(const std::string &instance_version)
void insert_game_player_info(const std::string &uuid, int game_id, const std::string &username, int side_number, int is_host, const std::string &faction, const std::string &version, const std::string &source, const std::string &current_user, const std::string &leaders)
mariadb::result_set_ref select(const mariadb::connection_ref &connection, const std::string &sql, const sql_parameters &params)
Executes a select statement.
std::string get_uuid()
std::string get_tournaments()
unsigned long long insert_game_content_info(const std::string &uuid, int game_id, const std::string &type, const std::string &name, const std::string &id, const std::string &addon_id, const std::string &addon_version)
node & add_child(const char *name)
Definition: simple_wml.cpp:469
node & set_attr_dup(const char *key, const char *value)
Definition: simple_wml.cpp:427
std::vector< std::variant< bool, int, long, unsigned long long, std::string, const char * > > sql_parameters
Definition: dbconn.hpp:31
std::size_t i
Definition: function.cpp:1032
std::string id
Text to match against addon_info.tags()
Definition: manager.cpp:199
int side_number
Definition: game_info.hpp:40
logger & info()
Definition: log.cpp:351
std::string lowercase(std::string_view s)
Returns a lowercased version of the string.
Definition: unicode.cpp:50
void to_sql_wildcards(std::string &str, bool underscores)
Converts '*' to '' and optionally escapes '_'.
std::string join(const T &v, const std::string &s=",")
Generates a new string joining container items in a list.
std::vector< std::string > split(const config_attribute_value &val)
mock_char c
mock_party p
#define e