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