The Battle for Wesnoth  1.19.0-dev
dbconn.cpp
Go to the documentation of this file.
1 /*
2  Copyright (C) 2020 - 2024
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"
21 
22 #include "log.hpp"
25 
26 static lg::log_domain log_sql_handler("sql_executor");
27 #define ERR_SQL LOG_STREAM(err, log_sql_handler)
28 #define WRN_SQL LOG_STREAM(warn, log_sql_handler)
29 #define LOG_SQL LOG_STREAM(info, log_sql_handler)
30 #define DBG_SQL LOG_STREAM(debug, log_sql_handler)
31 
32 dbconn::dbconn(const config& c)
33  : db_users_table_(c["db_users_table"].str())
34  , db_banlist_table_(c["db_banlist_table"].str())
35  , db_extra_table_(c["db_extra_table"].str())
36  , db_game_info_table_(c["db_game_info_table"].str())
37  , db_game_player_info_table_(c["db_game_player_info_table"].str())
38  , db_game_content_info_table_(c["db_game_content_info_table"].str())
39  , db_user_group_table_(c["db_user_group_table"].str())
40  , db_tournament_query_(c["db_tournament_query"].str())
41  , db_topics_table_(c["db_topics_table"].str())
42  , db_addon_info_table_(c["db_addon_info_table"].str())
43  , db_connection_history_table_(c["db_connection_history_table"].str())
44  , db_addon_authors_table_(c["db_addon_authors_table"].str())
45 {
46  try
47  {
48  account_ = mariadb::account::create(c["db_host"].str(), c["db_user"].str(), c["db_password"].str());
49  account_->set_connect_option(mysql_option::MYSQL_SET_CHARSET_NAME, std::string("utf8mb4"));
50  account_->set_schema(c["db_name"].str());
51  // initialize the connection used to run synchronous queries.
52  connection_ = create_connection();
53  }
54  catch(const mariadb::exception::base& e)
55  {
56  log_sql_exception("Failed to connect to the database!", e);
57  }
58 }
59 
60 void dbconn::log_sql_exception(const std::string& text, const mariadb::exception::base& e)
61 {
62  ERR_SQL << text << '\n'
63  << "what: " << e.what() << '\n'
64  << "error id: " << e.error_id();
65 }
66 
67 mariadb::connection_ref dbconn::create_connection()
68 {
69  return mariadb::connection::create(account_);
70 }
71 
72 //
73 // queries
74 //
75 int dbconn::async_test_query(int limit)
76 {
77  std::string sql = "with recursive TEST(T) as "
78  "( "
79  "select 1 "
80  "union all "
81  "select T+1 from TEST where T < ? "
82  ") "
83  "select count(*) from TEST";
84  int t = get_single_long(create_connection(), sql, { limit });
85  return t;
86 }
87 
88 std::string dbconn::get_uuid()
89 {
90  try
91  {
92  return get_single_string(connection_, "SELECT UUID()", {});
93  }
94  catch(const mariadb::exception::base& e)
95  {
96  log_sql_exception("Could not retrieve a UUID!", e);
97  return "";
98  }
99 }
100 
101 std::string dbconn::get_tournaments()
102 {
103  if(db_tournament_query_ == "")
104  {
105  return "";
106  }
107 
108  try
109  {
110  tournaments t;
112  return t.str();
113  }
114  catch(const mariadb::exception::base& e)
115  {
116  log_sql_exception("Could not retrieve the tournaments!", e);
117  return "";
118  }
119 }
120 
121 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)
122 {
123  try
124  {
125  // if no parameters populated, return an error
126  if(player_id == 0 && search_game_name.empty() && search_content.empty())
127  {
128  ERR_SQL << "Skipping game history query due to lack of search parameters.";
129  auto doc = std::make_unique<simple_wml::document>();
130  doc->set_attr("error", "No search parameters provided.");
131  return doc;
132  }
133 
134  sql_parameters params;
135 
136  std::string game_history_query = "select "
137 " game.GAME_NAME, "
138 " game.START_TIME, "
139 " GROUP_CONCAT(CONCAT(player.USER_NAME, ':', player.FACTION)) as PLAYERS, "
140 " IFNULL(scenario.NAME, '') as SCENARIO_NAME, "
141 " IFNULL(era.NAME, '') as ERA_NAME, "
142 " IFNULL(GROUP_CONCAT(distinct mods.NAME), '') as MODIFICATION_NAMES, "
143 " case "
144 " when game.PUBLIC = 1 and game.INSTANCE_VERSION != 'trunk' "
145 " 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) "
146 " when game.PUBLIC = 1 and game.INSTANCE_VERSION = 'trunk' "
147 " 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) "
148 " else '' "
149 " end as REPLAY_URL, "
150 " case "
151 " when game.INSTANCE_VERSION != 'trunk' "
152 " then SUBSTRING(game.INSTANCE_VERSION, 1, 4) "
153 " else 'trunk' "
154 " end as VERSION "
156  // using a left join when searching by modification won't exclude anything
157  game_history_query += search_content_type == 2 && !search_content.empty() ? "inner join " : "left join ";
158  game_history_query += db_game_content_info_table_+" mods "
159 " on mods.TYPE = 'modification' "
160 " and mods.INSTANCE_UUID = game.INSTANCE_UUID "
161 " and mods.GAME_ID = game.GAME_ID ";
162  // modification id optional parameter
163  if(search_content_type == 2)
164  {
165  if(!search_content.empty())
166  {
167  game_history_query += "and mods.ID like ? ";
168 
169  utils::to_sql_wildcards(search_content, false);
170  params.emplace_back(search_content);
171  }
172  }
173 
174  game_history_query += "where exists "
175 " ( "
176 " select 1 "
177 " from "+db_game_player_info_table_+" player1 "
178 " where game.INSTANCE_UUID = player1.INSTANCE_UUID "
179 " and game.GAME_ID = player1.GAME_ID ";
180 
181  if(player_id != 0)
182  {
183  game_history_query += " and player1.USER_ID = ? ";
184  params.emplace_back(player_id);
185  }
186 
187  game_history_query += " ) "
188 " and game.INSTANCE_UUID = player.INSTANCE_UUID "
189 " and game.GAME_ID = player.GAME_ID "
190 " and player.USER_ID != -1 "
191 " and game.END_TIME is not NULL "
192 " and scenario.TYPE = 'scenario' "
193 " and scenario.INSTANCE_UUID = game.INSTANCE_UUID "
194 " and scenario.GAME_ID = game.GAME_ID "
195 " and era.TYPE = 'era' "
196 " and era.INSTANCE_UUID = game.INSTANCE_UUID "
197 " and era.GAME_ID = game.GAME_ID ";
198  // game name optional paramenter
199  if(!search_game_name.empty())
200  {
201  game_history_query += "and game.GAME_NAME like ? ";
202 
203  utils::to_sql_wildcards(search_game_name, false);
204  params.emplace_back(search_game_name);
205  }
206 
207  // scenario id optional parameter
208  if(search_content_type == 0)
209  {
210  if(!search_content.empty())
211  {
212  game_history_query += "and scenario.ID like ? ";
213 
214  utils::to_sql_wildcards(search_content, false);
215  params.emplace_back(search_content);
216  }
217  }
218 
219  // era id optional parameter
220  if(search_content_type == 1)
221  {
222  if(!search_content.empty())
223  {
224  game_history_query += "and era.ID like ? ";
225 
226  utils::to_sql_wildcards(search_content, false);
227  params.emplace_back(search_content);
228  }
229  }
230 
231  game_history_query += "group by game.INSTANCE_UUID, game.GAME_ID "
232 "order by game.START_TIME desc "
233 "limit 11 offset ? ";
234  params.emplace_back(offset);
235 
236  DBG_SQL << "before getting connection for game history query for player " << player_id;
237 
238  mariadb::connection_ref connection = create_connection();
239 
240  DBG_SQL << "game history query text for player " << player_id << ": " << game_history_query;
241 
242  game_history gh;
243  get_complex_results(connection, gh, game_history_query, params);
244 
245  DBG_SQL << "after game history query for player " << player_id;
246 
247  auto doc = gh.to_doc();
248 
249  DBG_SQL << "after parsing results of game history query for player " << player_id;
250 
251  return doc;
252  }
253  catch(const mariadb::exception::base& e)
254  {
255  log_sql_exception("Could not retrieve the game history for forum ID `"+std::to_string(player_id)+"`!", e);
256  auto doc = std::make_unique<simple_wml::document>();
257  doc->set_attr("error", "Error retrieving game history.");
258  return doc;
259  }
260 }
261 
262 bool dbconn::user_exists(const std::string& name)
263 {
264  try
265  {
266  return exists(connection_, "SELECT 1 FROM `"+db_users_table_+"` WHERE UPPER(username)=UPPER(?)", { name });
267  }
268  catch(const mariadb::exception::base& e)
269  {
270  log_sql_exception("Unable to check if user row for '"+name+"' exists!", e);
271  return false;
272  }
273 }
274 
275 long dbconn::get_forum_id(const std::string& name)
276 {
277  try
278  {
279  return get_single_long(connection_, "SELECT IFNULL((SELECT user_id FROM `"+db_users_table_+"` WHERE UPPER(username)=UPPER(?)), 0)", { name });
280  }
281  catch(const mariadb::exception::base& e)
282  {
283  log_sql_exception("Unable to get user_id for '"+name+"'!", e);
284  return 0;
285  }
286 }
287 
288 bool dbconn::extra_row_exists(const std::string& name)
289 {
290  try
291  {
292  return exists(connection_, "SELECT 1 FROM `"+db_extra_table_+"` WHERE UPPER(username)=UPPER(?)", { name });
293  }
294  catch(const mariadb::exception::base& e)
295  {
296  log_sql_exception("Unable to check if extra row for '"+name+"' exists!", e);
297  return false;
298  }
299 }
300 
301 bool dbconn::is_user_in_group(const std::string& name, int group_id)
302 {
303  try
304  {
305  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 = ?",
306  { name, group_id });
307  }
308  catch(const mariadb::exception::base& e)
309  {
310  log_sql_exception("Unable to check if the user '"+name+"' is in group '"+std::to_string(group_id)+"'!", e);
311  return false;
312  }
313 }
314 
315 ban_check dbconn::get_ban_info(const std::string& name, const std::string& ip)
316 {
317  try
318  {
319  // selected ban_type value must be part of user_handler::BAN_TYPE
320  ban_check b;
321  get_complex_results(connection_, b, "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 >= ?)",
322  { ip, name, name, std::time(nullptr) });
323  return b;
324  }
325  catch(const mariadb::exception::base& e)
326  {
327  log_sql_exception("Failed to check ban info for user '"+name+"' connecting from ip '"+ip+"'!", e);
328  return ban_check();
329  }
330 }
331 
332 std::string dbconn::get_user_string(const std::string& table, const std::string& column, const std::string& name)
333 {
334  try
335  {
336  return get_single_string(connection_, "SELECT `"+column+"` from `"+table+"` WHERE UPPER(username)=UPPER(?)", { name });
337  }
338  catch(const mariadb::exception::base& e)
339  {
340  log_sql_exception("Unable to query column `"+column+"` from table `"+table+"` for user `"+name+"`", e);
341  return "";
342  }
343 }
344 int dbconn::get_user_int(const std::string& table, const std::string& column, const std::string& name)
345 {
346  try
347  {
348  return static_cast<int>(get_single_long(connection_, "SELECT `"+column+"` from `"+table+"` WHERE UPPER(username)=UPPER(?)", { name }));
349  }
350  catch(const mariadb::exception::base& e)
351  {
352  log_sql_exception("Unable to query column `"+column+"` from table `"+table+"` for user `"+name+"`", e);
353  return 0;
354  }
355 }
356 void dbconn::write_user_int(const std::string& column, const std::string& name, int value)
357 {
358  try
359  {
360  if(!extra_row_exists(name))
361  {
362  modify(connection_, "INSERT INTO `"+db_extra_table_+"` VALUES(?,?,'0')", { name, value });
363  }
364  modify(connection_, "UPDATE `"+db_extra_table_+"` SET "+column+"=? WHERE UPPER(username)=UPPER(?)", { value, name });
365  }
366  catch(const mariadb::exception::base& e)
367  {
368  log_sql_exception("Unable to write `"+std::to_string(value)+"` to column `"+column+"` on table `"+db_extra_table_+"` for user `"+name+"`", e);
369  }
370 }
371 
372 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)
373 {
374  try
375  {
376  modify(connection_, "INSERT INTO `"+db_game_info_table_+"`(INSTANCE_UUID, GAME_ID, INSTANCE_VERSION, GAME_NAME, RELOAD, OBSERVERS, PUBLIC, PASSWORD) VALUES(?, ?, ?, ?, ?, ?, ?, ?)",
377  { uuid, game_id, version, name, reload, observers, is_public, has_password });
378  }
379  catch(const mariadb::exception::base& e)
380  {
381  log_sql_exception("Failed to insert game info row for UUID `"+uuid+"` and game ID `"+std::to_string(game_id)+"`", e);
382  }
383 }
384 void dbconn::update_game_end(const std::string& uuid, int game_id, const std::string& replay_location)
385 {
386  try
387  {
388  modify(connection_, "UPDATE `"+db_game_info_table_+"` SET END_TIME = CURRENT_TIMESTAMP, REPLAY_NAME = ? WHERE INSTANCE_UUID = ? AND GAME_ID = ?",
389  { replay_location, uuid, game_id });
390  }
391  catch(const mariadb::exception::base& e)
392  {
393  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);
394  }
395 }
396 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)
397 {
398  try
399  {
400  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), ?, ?, ?, ?, ?, ?, ?)",
401  { uuid, game_id, username, side_number, is_host, faction, version, source, current_user, leaders });
402  }
403  catch(const mariadb::exception::base& e)
404  {
405  log_sql_exception("Failed to insert game player info row for UUID `"+uuid+"` and game ID `"+std::to_string(game_id)+"`", e);
406  }
407 }
408 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)
409 {
410  try
411  {
412  return modify(connection_, "INSERT INTO `"+db_game_content_info_table_+"`(INSTANCE_UUID, GAME_ID, TYPE, NAME, ID, ADDON_ID, ADDON_VERSION) VALUES(?, ?, ?, ?, ?, ?, ?)",
413  { uuid, game_id, type, name, id, addon_id, addon_version });
414  }
415  catch(const mariadb::exception::base& e)
416  {
417  log_sql_exception("Failed to insert game content info row for UUID `"+uuid+"` and game ID `"+std::to_string(game_id)+"`", e);
418  return 0;
419  }
420 }
421 void dbconn::set_oos_flag(const std::string& uuid, int game_id)
422 {
423  try
424  {
425  modify(connection_, "UPDATE `"+db_game_info_table_+"` SET OOS = 1 WHERE INSTANCE_UUID = ? AND GAME_ID = ?",
426  { uuid, game_id });
427  }
428  catch(const mariadb::exception::base& e)
429  {
430  log_sql_exception("Failed to set the OOS flag for UUID `"+uuid+"` and game ID `"+std::to_string(game_id)+"`", e);
431  }
432 }
433 
434 bool dbconn::topic_id_exists(int topic_id) {
435  try
436  {
437  return exists(connection_, "SELECT 1 FROM `"+db_topics_table_+"` WHERE TOPIC_ID = ?",
438  { topic_id });
439  }
440  catch(const mariadb::exception::base& e)
441  {
442  log_sql_exception("Unable to check whether `"+std::to_string(topic_id)+"` exists.", e);
443  return true;
444  }
445 }
446 
447 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)
448 {
449  try
450  {
451  modify(connection_, "INSERT INTO `"+db_addon_info_table_+"`(INSTANCE_VERSION, ADDON_ID, ADDON_NAME, TYPE, VERSION, FORUM_AUTH, FEEDBACK_TOPIC, UPLOADER) VALUES(?, ?, ?, ?, ?, ?, ?, ?)",
452  { instance_version, id, name, type, version, forum_auth, topic_id, uploader });
453  }
454  catch(const mariadb::exception::base& e)
455  {
456  log_sql_exception("Unable to insert add-on info for add-on `"+id+"` for instance `"+instance_version+"`.", e);
457  }
458 }
459 
460 unsigned long long dbconn::insert_login(const std::string& username, const std::string& ip, const std::string& version)
461 {
462  try
463  {
464  return modify_get_id(connection_, "INSERT INTO `"+db_connection_history_table_+"`(USER_NAME, IP, VERSION) values(lower(?), ?, ?)",
465  { username, ip, version });
466  }
467  catch(const mariadb::exception::base& e)
468  {
469  log_sql_exception("Unable to insert login row user `"+username+"` and ip `"+ip+"`.", e);
470  return 0;
471  }
472 }
473 
474 void dbconn::update_logout(unsigned long long login_id)
475 {
476  try
477  {
478  modify(connection_, "UPDATE `"+db_connection_history_table_+"` SET LOGOUT_TIME = CURRENT_TIMESTAMP WHERE LOGIN_ID = ?",
479  { login_id });
480  }
481  catch(const mariadb::exception::base& e)
482  {
483  log_sql_exception("Unable to update login row `"+std::to_string(login_id)+"`.", e);
484  }
485 }
486 
487 void dbconn::get_users_for_ip(const std::string& ip, std::ostringstream* out)
488 {
489  try
490  {
491  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",
492  { ip });
493 
494  *out << "\nCount of results for ip: " << rslt->row_count();
495 
496  while(rslt->next())
497  {
498  *out << "\nFound user " << rslt->get_string(0) << " with ip " << rslt->get_string(1)
499  << ", logged in at " << rslt->get_string(2) << " and logged out at " << rslt->get_string(3);
500  }
501  }
502  catch(const mariadb::exception::base& e)
503  {
504  log_sql_exception("Unable to select rows for ip `"+ip+"`.", e);
505  }
506 }
507 
508 void dbconn::get_ips_for_user(const std::string& username, std::ostringstream* out)
509 {
510  try
511  {
512  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",
513  { utf8::lowercase(username) });
514 
515  *out << "\nCount of results for user: " << rslt->row_count();
516 
517  while(rslt->next())
518  {
519  *out << "\nFound user " << rslt->get_string(0) << " with ip " << rslt->get_string(1)
520  << ", logged in at " << rslt->get_string(2) << " and logged out at " << rslt->get_string(3);
521  }
522  }
523  catch(const mariadb::exception::base& e)
524  {
525  log_sql_exception("Unable to select rows for player `"+username+"`.", e);
526  }
527 }
528 
529 void dbconn::update_addon_download_count(const std::string& instance_version, const std::string& id, const std::string& version)
530 {
531  try
532  {
533  modify(connection_, "UPDATE `"+db_addon_info_table_+"` SET DOWNLOAD_COUNT = DOWNLOAD_COUNT+1 WHERE INSTANCE_VERSION = ? AND ADDON_ID = ? AND VERSION = ?",
534  { instance_version, id, version });
535  }
536  catch(const mariadb::exception::base& e)
537  {
538  log_sql_exception("Unable to update download count for add-on "+id+" with version "+version+".", e);
539  }
540 }
541 
542 bool dbconn::is_user_author(const std::string& instance_version, const std::string& id, const std::string& username, int is_primary) {
543  try
544  {
545  return exists(connection_, "SELECT 1 FROM `"+db_addon_authors_table_+"` WHERE INSTANCE_VERSION = ? AND ADDON_ID = ? AND AUTHOR = ? AND IS_PRIMARY = ?",
546  { instance_version, id, username, is_primary });
547  }
548  catch(const mariadb::exception::base& e)
549  {
550  log_sql_exception("Unable to check whether `"+username+"` is an author of "+id+" for version "+instance_version+".", e);
551  return false;
552  }
553 }
554 
555 void dbconn::delete_addon_authors(const std::string& instance_version, const std::string& id) {
556  try
557  {
558  modify(connection_, "DELETE FROM `"+db_addon_authors_table_+"` WHERE INSTANCE_VERSION = ? AND ADDON_ID = ?",
559  { instance_version, id });
560  }
561  catch(const mariadb::exception::base& e)
562  {
563  log_sql_exception("Unable to delete addon authors for "+id+" and version "+instance_version+".", e);
564  }
565 }
566 
567 void dbconn::insert_addon_author(const std::string& instance_version, const std::string& id, const std::string author, int is_primary) {
568  try
569  {
570  modify(connection_, "INSERT INTO `"+db_addon_authors_table_+"`(INSTANCE_VERSION, ADDON_ID, AUTHOR, IS_PRIMARY) VALUES(?,?,?,?)",
571  { instance_version, id, author, is_primary });
572  }
573  catch(const mariadb::exception::base& e)
574  {
575  log_sql_exception("Unable to delete addon authors for "+id+" and version "+instance_version+".", e);
576  }
577 }
578 
579 bool dbconn::do_any_authors_exist(const std::string& instance_version, const std::string& id) {
580  try
581  {
582  return exists(connection_, "SELECT 1 FROM `"+db_addon_authors_table_+"` WHERE INSTANCE_VERSION = ? AND ADDON_ID = ?",
583  { instance_version, id });
584  }
585  catch(const mariadb::exception::base& e)
586  {
587  log_sql_exception("Unable to check whether authors exist for "+id+" for version "+instance_version+".", e);
588  return true;
589  }
590 }
591 
592 //
593 // handle complex query results
594 //
595 void dbconn::get_complex_results(mariadb::connection_ref connection, rs_base& base, const std::string& sql, const sql_parameters& params)
596 {
597  mariadb::result_set_ref rslt = select(connection, sql, params);
598  base.read(rslt);
599 }
600 //
601 // handle single values
602 //
603 std::string dbconn::get_single_string(mariadb::connection_ref connection, const std::string& sql, const sql_parameters& params)
604 {
605  mariadb::result_set_ref rslt = select(connection, sql, params);
606  if(rslt->next())
607  {
608  return rslt->get_string(0);
609  }
610  else
611  {
612  throw mariadb::exception::base("No string value found in the database!");
613  }
614 }
615 long dbconn::get_single_long(mariadb::connection_ref connection, const std::string& sql, const sql_parameters& params)
616 {
617  mariadb::result_set_ref rslt = select(connection, sql, params);
618  if(rslt->next())
619  {
620  // mariadbpp checks for strict integral equivalence, but we don't care
621  // so check the type beforehand, call the associated getter, and let it silently get upcast to a long if needed
622  // subselects also apparently return a decimal
623  switch(rslt->column_type(0))
624  {
625  case mariadb::value::type::decimal:
626  return static_cast<long>(rslt->get_decimal(0).float32());
627  case mariadb::value::type::unsigned8:
628  case mariadb::value::type::signed8:
629  return rslt->get_signed8(0);
630  case mariadb::value::type::unsigned16:
631  case mariadb::value::type::signed16:
632  return rslt->get_signed16(0);
633  case mariadb::value::type::unsigned32:
634  case mariadb::value::type::signed32:
635  return rslt->get_signed32(0);
636  case mariadb::value::type::unsigned64:
637  case mariadb::value::type::signed64:
638  return rslt->get_signed64(0);
639  default:
640  throw mariadb::exception::base("Value retrieved was not a long!");
641  }
642  }
643  else
644  {
645  throw mariadb::exception::base("No long value found in the database!");
646  }
647 }
648 bool dbconn::exists(mariadb::connection_ref connection, const std::string& sql, const sql_parameters& params)
649 {
650  mariadb::result_set_ref rslt = select(connection, sql, params);
651  return rslt->next();
652 }
653 
654 //
655 // select or modify values
656 //
657 mariadb::result_set_ref dbconn::select(mariadb::connection_ref connection, const std::string& sql, const sql_parameters& params)
658 {
659  try
660  {
661  mariadb::statement_ref stmt = query(connection, sql, params);
662  mariadb::result_set_ref rslt = mariadb::result_set_ref(stmt->query());
663  return rslt;
664  }
665  catch(const mariadb::exception::base& e)
666  {
667  ERR_SQL << "SQL query failed for query: `"+sql+"`";
668  throw e;
669  }
670 }
671 unsigned long long dbconn::modify(mariadb::connection_ref connection, const std::string& sql, const sql_parameters& params)
672 {
673  try
674  {
675  mariadb::statement_ref stmt = query(connection, sql, params);
676  unsigned long long count = stmt->execute();
677  return count;
678  }
679  catch(const mariadb::exception::base& e)
680  {
681  ERR_SQL << "SQL query failed for query: `"+sql+"`";
682  throw e;
683  }
684 }
685 unsigned long long dbconn::modify_get_id(mariadb::connection_ref connection, const std::string& sql, const sql_parameters& params)
686 {
687  try
688  {
689  mariadb::statement_ref stmt = query(connection, sql, params);
690  unsigned long long count = stmt->insert();
691  return count;
692  }
693  catch(const mariadb::exception::base& e)
694  {
695  ERR_SQL << "SQL query failed for query: `"+sql+"`";
696  throw e;
697  }
698 }
699 
700 mariadb::statement_ref dbconn::query(mariadb::connection_ref connection, const std::string& sql, const sql_parameters& params)
701 {
702  mariadb::statement_ref stmt = connection->create_statement(sql);
703 
704  unsigned i = 0;
705  for(const auto& param : params)
706  {
707  if(std::holds_alternative<bool>(param))
708  {
709  stmt->set_boolean(i, std::get<bool>(param));
710  }
711  else if(std::holds_alternative<int>(param))
712  {
713  stmt->set_signed32(i, std::get<int>(param));
714  }
715  else if(std::holds_alternative<unsigned long long>(param))
716  {
717  stmt->set_signed64(i, std::get<unsigned long long>(param));
718  }
719  else if(std::holds_alternative<std::string>(param))
720  {
721  stmt->set_string(i, std::get<std::string>(param));
722  }
723  else if(std::holds_alternative<const char*>(param))
724  {
725  stmt->set_string(i, std::get<const char*>(param));
726  }
727  i++;
728  }
729 
730  return stmt;
731 }
732 
733 #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:159
void delete_addon_authors(const std::string &instance_version, const std::string &id)
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)
mariadb::statement_ref query(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_tournament_query_
The text of the SQL query to use to retrieve any currently active tournaments.
Definition: dbconn.hpp:235
bool topic_id_exists(int topic_id)
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:218
long get_forum_id(const std::string &name)
long get_single_long(mariadb::connection_ref connection, const std::string &sql, const sql_parameters &params)
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 ...
unsigned long long modify(mariadb::connection_ref connection, const std::string &sql, const sql_parameters &params)
Executes non-select statements (ie: insert, update, delete).
void get_ips_for_user(const std::string &username, std::ostringstream *out)
bool is_user_in_group(const std::string &name, int group_id)
std::string db_addon_authors_table_
The name of the table that contains the add-on authors information.
Definition: dbconn.hpp:243
bool user_exists(const std::string &name)
void get_users_for_ip(const std::string &ip, std::ostringstream *out)
mariadb::result_set_ref select(mariadb::connection_ref connection, const std::string &sql, const sql_parameters &params)
Executes a select statement.
void insert_addon_author(const std::string &instance_version, const std::string &id, const std::string author, int is_primary)
std::string get_single_string(mariadb::connection_ref connection, const std::string &sql, const sql_parameters &params)
unsigned long long modify_get_id(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_banlist_table_
The name of the table that contains forum ban information.
Definition: dbconn.hpp:223
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:225
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:239
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.
bool exists(mariadb::connection_ref connection, const std::string &sql, const sql_parameters &params)
std::string db_game_content_info_table_
The name of the table that contains game content information.
Definition: dbconn.hpp:231
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:233
void get_complex_results(mariadb::connection_ref connection, rs_base &base, const std::string &sql, const sql_parameters &params)
Queries can return data with various types that can't be easily fit into a pre-determined structure.
mariadb::account_ref account_
The account used to connect to the database.
Definition: dbconn.hpp:216
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:227
std::string db_game_player_info_table_
The name of the table that contains player-level information per game.
Definition: dbconn.hpp:229
std::string db_connection_history_table_
The name of the table that contains user connection history.
Definition: dbconn.hpp:241
int get_user_int(const std::string &table, const std::string &column, const std::string &name)
ban_check get_ban_info(const std::string &name, const std::string &ip)
std::string db_topics_table_
The name of the table that contains phpbb forum thread information.
Definition: dbconn.hpp:237
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)
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:221
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)
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)
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)
std::unique_ptr< simple_wml::document > to_doc()
virtual void read(mariadb::result_set_ref rslt)=0
std::vector< std::variant< bool, int, long, unsigned long long, std::string, const char * > > sql_parameters
Definition: dbconn.hpp:32
std::size_t i
Definition: function.cpp:968
std::string id
Text to match against addon_info.tags()
Definition: manager.cpp:207
int side_number
Definition: game_info.hpp:40
std::string lowercase(const std::string &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 '_'.
mock_char c
#define e
#define b