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((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, "
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  // modification id optional parameter
157  if(search_content_type == 2 && !search_content.empty())
158  {
159  game_history_query += ", "+db_game_content_info_table_+" mods "
160 " where mods.TYPE = 'modification' "
161 " and mods.INSTANCE_UUID = game.INSTANCE_UUID "
162 " and mods.GAME_ID = game.GAME_ID "
163 " and mods.ID like ? ";
164 
165  utils::to_sql_wildcards(search_content, false);
166  params.emplace_back(search_content);
167  }
168  else
169  {
170  // 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"
171  game_history_query += "where true ";
172  }
173 
174  game_history_query += "and 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 && !search_content.empty())
209  {
210  game_history_query += "and scenario.ID like ? ";
211 
212  utils::to_sql_wildcards(search_content, false);
213  params.emplace_back(search_content);
214  }
215 
216  // era id optional parameter
217  if(search_content_type == 1 && !search_content.empty())
218  {
219  game_history_query += "and era.ID like ? ";
220 
221  utils::to_sql_wildcards(search_content, false);
222  params.emplace_back(search_content);
223  }
224 
225  game_history_query += "group by game.INSTANCE_UUID, game.GAME_ID, SCENARIO_NAME, ERA_NAME "
226 "order by game.START_TIME desc "
227 "limit 11 offset ? ";
228  params.emplace_back(offset);
229 
230  DBG_SQL << "before getting connection for game history query for player " << player_id;
231 
232  mariadb::connection_ref connection = create_connection();
233 
234  DBG_SQL << "game history query text for player " << player_id << ": " << game_history_query;
235 
236  game_history gh;
237  get_complex_results(connection, gh, game_history_query, params);
238 
239  DBG_SQL << "after game history query for player " << player_id;
240 
241  auto doc = gh.to_doc();
242 
243  DBG_SQL << "after parsing results of game history query for player " << player_id;
244 
245  return doc;
246  }
247  catch(const mariadb::exception::base& e)
248  {
249  log_sql_exception("Could not retrieve the game history for forum ID `"+std::to_string(player_id)+"`!", e);
250  auto doc = std::make_unique<simple_wml::document>();
251  doc->set_attr("error", "Error retrieving game history.");
252  return doc;
253  }
254 }
255 
256 bool dbconn::user_exists(const std::string& name)
257 {
258  try
259  {
260  return exists(connection_, "SELECT 1 FROM `"+db_users_table_+"` WHERE UPPER(username)=UPPER(?)", { name });
261  }
262  catch(const mariadb::exception::base& e)
263  {
264  log_sql_exception("Unable to check if user row for '"+name+"' exists!", e);
265  return false;
266  }
267 }
268 
269 long dbconn::get_forum_id(const std::string& name)
270 {
271  try
272  {
273  return get_single_long(connection_, "SELECT IFNULL((SELECT user_id FROM `"+db_users_table_+"` WHERE UPPER(username)=UPPER(?)), 0)", { name });
274  }
275  catch(const mariadb::exception::base& e)
276  {
277  log_sql_exception("Unable to get user_id for '"+name+"'!", e);
278  return 0;
279  }
280 }
281 
282 bool dbconn::extra_row_exists(const std::string& name)
283 {
284  try
285  {
286  return exists(connection_, "SELECT 1 FROM `"+db_extra_table_+"` WHERE UPPER(username)=UPPER(?)", { name });
287  }
288  catch(const mariadb::exception::base& e)
289  {
290  log_sql_exception("Unable to check if extra row for '"+name+"' exists!", e);
291  return false;
292  }
293 }
294 
295 bool dbconn::is_user_in_group(const std::string& name, int group_id)
296 {
297  try
298  {
299  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 = ?",
300  { name, group_id });
301  }
302  catch(const mariadb::exception::base& e)
303  {
304  log_sql_exception("Unable to check if the user '"+name+"' is in group '"+std::to_string(group_id)+"'!", e);
305  return false;
306  }
307 }
308 
309 ban_check dbconn::get_ban_info(const std::string& name, const std::string& ip)
310 {
311  try
312  {
313  // selected ban_type value must be part of user_handler::BAN_TYPE
314  ban_check b;
315  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 >= ?)",
316  { ip, name, name, std::time(nullptr) });
317  return b;
318  }
319  catch(const mariadb::exception::base& e)
320  {
321  log_sql_exception("Failed to check ban info for user '"+name+"' connecting from ip '"+ip+"'!", e);
322  return ban_check();
323  }
324 }
325 
326 std::string dbconn::get_user_string(const std::string& table, const std::string& column, const std::string& name)
327 {
328  try
329  {
330  return get_single_string(connection_, "SELECT `"+column+"` from `"+table+"` WHERE UPPER(username)=UPPER(?)", { name });
331  }
332  catch(const mariadb::exception::base& e)
333  {
334  log_sql_exception("Unable to query column `"+column+"` from table `"+table+"` for user `"+name+"`", e);
335  return "";
336  }
337 }
338 int dbconn::get_user_int(const std::string& table, const std::string& column, const std::string& name)
339 {
340  try
341  {
342  return static_cast<int>(get_single_long(connection_, "SELECT `"+column+"` from `"+table+"` WHERE UPPER(username)=UPPER(?)", { name }));
343  }
344  catch(const mariadb::exception::base& e)
345  {
346  log_sql_exception("Unable to query column `"+column+"` from table `"+table+"` for user `"+name+"`", e);
347  return 0;
348  }
349 }
350 void dbconn::write_user_int(const std::string& column, const std::string& name, int value)
351 {
352  try
353  {
354  if(!extra_row_exists(name))
355  {
356  modify(connection_, "INSERT INTO `"+db_extra_table_+"` VALUES(?,?,'0')", { name, value });
357  }
358  modify(connection_, "UPDATE `"+db_extra_table_+"` SET "+column+"=? WHERE UPPER(username)=UPPER(?)", { value, name });
359  }
360  catch(const mariadb::exception::base& e)
361  {
362  log_sql_exception("Unable to write `"+std::to_string(value)+"` to column `"+column+"` on table `"+db_extra_table_+"` for user `"+name+"`", e);
363  }
364 }
365 
366 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)
367 {
368  try
369  {
370  modify(connection_, "INSERT INTO `"+db_game_info_table_+"`(INSTANCE_UUID, GAME_ID, INSTANCE_VERSION, GAME_NAME, RELOAD, OBSERVERS, PUBLIC, PASSWORD) VALUES(?, ?, ?, ?, ?, ?, ?, ?)",
371  { uuid, game_id, version, name, reload, observers, is_public, has_password });
372  }
373  catch(const mariadb::exception::base& e)
374  {
375  log_sql_exception("Failed to insert game info row for UUID `"+uuid+"` and game ID `"+std::to_string(game_id)+"`", e);
376  }
377 }
378 void dbconn::update_game_end(const std::string& uuid, int game_id, const std::string& replay_location)
379 {
380  try
381  {
382  modify(connection_, "UPDATE `"+db_game_info_table_+"` SET END_TIME = CURRENT_TIMESTAMP, REPLAY_NAME = ? WHERE INSTANCE_UUID = ? AND GAME_ID = ?",
383  { replay_location, uuid, game_id });
384  }
385  catch(const mariadb::exception::base& e)
386  {
387  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);
388  }
389 }
390 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)
391 {
392  try
393  {
394  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), ?, ?, ?, ?, ?, ?, ?)",
395  { uuid, game_id, username, side_number, is_host, faction, version, source, current_user, leaders });
396  }
397  catch(const mariadb::exception::base& e)
398  {
399  log_sql_exception("Failed to insert game player info row for UUID `"+uuid+"` and game ID `"+std::to_string(game_id)+"`", e);
400  }
401 }
402 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)
403 {
404  try
405  {
406  return modify(connection_, "INSERT INTO `"+db_game_content_info_table_+"`(INSTANCE_UUID, GAME_ID, TYPE, NAME, ID, ADDON_ID, ADDON_VERSION) VALUES(?, ?, ?, ?, ?, ?, ?)",
407  { uuid, game_id, type, name, id, addon_id, addon_version });
408  }
409  catch(const mariadb::exception::base& e)
410  {
411  log_sql_exception("Failed to insert game content info row for UUID `"+uuid+"` and game ID `"+std::to_string(game_id)+"`", e);
412  return 0;
413  }
414 }
415 void dbconn::set_oos_flag(const std::string& uuid, int game_id)
416 {
417  try
418  {
419  modify(connection_, "UPDATE `"+db_game_info_table_+"` SET OOS = 1 WHERE INSTANCE_UUID = ? AND GAME_ID = ?",
420  { uuid, game_id });
421  }
422  catch(const mariadb::exception::base& e)
423  {
424  log_sql_exception("Failed to set the OOS flag for UUID `"+uuid+"` and game ID `"+std::to_string(game_id)+"`", e);
425  }
426 }
427 
428 bool dbconn::topic_id_exists(int topic_id) {
429  try
430  {
431  return exists(connection_, "SELECT 1 FROM `"+db_topics_table_+"` WHERE TOPIC_ID = ?",
432  { topic_id });
433  }
434  catch(const mariadb::exception::base& e)
435  {
436  log_sql_exception("Unable to check whether `"+std::to_string(topic_id)+"` exists.", e);
437  return true;
438  }
439 }
440 
441 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)
442 {
443  try
444  {
445  modify(connection_, "INSERT INTO `"+db_addon_info_table_+"`(INSTANCE_VERSION, ADDON_ID, ADDON_NAME, TYPE, VERSION, FORUM_AUTH, FEEDBACK_TOPIC, UPLOADER) VALUES(?, ?, ?, ?, ?, ?, ?, ?)",
446  { instance_version, id, name, type, version, forum_auth, topic_id, uploader });
447  }
448  catch(const mariadb::exception::base& e)
449  {
450  log_sql_exception("Unable to insert add-on info for add-on `"+id+"` for instance `"+instance_version+"`.", e);
451  }
452 }
453 
454 unsigned long long dbconn::insert_login(const std::string& username, const std::string& ip, const std::string& version)
455 {
456  try
457  {
458  return modify_get_id(connection_, "INSERT INTO `"+db_connection_history_table_+"`(USER_NAME, IP, VERSION) values(lower(?), ?, ?)",
459  { username, ip, version });
460  }
461  catch(const mariadb::exception::base& e)
462  {
463  log_sql_exception("Unable to insert login row user `"+username+"` and ip `"+ip+"`.", e);
464  return 0;
465  }
466 }
467 
468 void dbconn::update_logout(unsigned long long login_id)
469 {
470  try
471  {
472  modify(connection_, "UPDATE `"+db_connection_history_table_+"` SET LOGOUT_TIME = CURRENT_TIMESTAMP WHERE LOGIN_ID = ?",
473  { login_id });
474  }
475  catch(const mariadb::exception::base& e)
476  {
477  log_sql_exception("Unable to update login row `"+std::to_string(login_id)+"`.", e);
478  }
479 }
480 
481 void dbconn::get_users_for_ip(const std::string& ip, std::ostringstream* out)
482 {
483  try
484  {
485  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",
486  { ip });
487 
488  *out << "\nCount of results for ip: " << rslt->row_count();
489 
490  while(rslt->next())
491  {
492  *out << "\nFound user " << rslt->get_string(0) << " with ip " << rslt->get_string(1)
493  << ", logged in at " << rslt->get_string(2) << " and logged out at " << rslt->get_string(3);
494  }
495  }
496  catch(const mariadb::exception::base& e)
497  {
498  log_sql_exception("Unable to select rows for ip `"+ip+"`.", e);
499  }
500 }
501 
502 void dbconn::get_ips_for_user(const std::string& username, std::ostringstream* out)
503 {
504  try
505  {
506  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",
507  { utf8::lowercase(username) });
508 
509  *out << "\nCount of results for user: " << rslt->row_count();
510 
511  while(rslt->next())
512  {
513  *out << "\nFound user " << rslt->get_string(0) << " with ip " << rslt->get_string(1)
514  << ", logged in at " << rslt->get_string(2) << " and logged out at " << rslt->get_string(3);
515  }
516  }
517  catch(const mariadb::exception::base& e)
518  {
519  log_sql_exception("Unable to select rows for player `"+username+"`.", e);
520  }
521 }
522 
523 void dbconn::update_addon_download_count(const std::string& instance_version, const std::string& id, const std::string& version)
524 {
525  try
526  {
527  modify(connection_, "UPDATE `"+db_addon_info_table_+"` SET DOWNLOAD_COUNT = DOWNLOAD_COUNT+1 WHERE INSTANCE_VERSION = ? AND ADDON_ID = ? AND VERSION = ?",
528  { instance_version, id, version });
529  }
530  catch(const mariadb::exception::base& e)
531  {
532  log_sql_exception("Unable to update download count for add-on "+id+" with version "+version+".", e);
533  }
534 }
535 
536 bool dbconn::is_user_author(const std::string& instance_version, const std::string& id, const std::string& username, int is_primary) {
537  try
538  {
539  return exists(connection_, "SELECT 1 FROM `"+db_addon_authors_table_+"` WHERE INSTANCE_VERSION = ? AND ADDON_ID = ? AND AUTHOR = ? AND IS_PRIMARY = ?",
540  { instance_version, id, username, is_primary });
541  }
542  catch(const mariadb::exception::base& e)
543  {
544  log_sql_exception("Unable to check whether `"+username+"` is an author of "+id+" for version "+instance_version+".", e);
545  return false;
546  }
547 }
548 
549 void dbconn::delete_addon_authors(const std::string& instance_version, const std::string& id) {
550  try
551  {
552  modify(connection_, "DELETE FROM `"+db_addon_authors_table_+"` WHERE INSTANCE_VERSION = ? AND ADDON_ID = ?",
553  { instance_version, id });
554  }
555  catch(const mariadb::exception::base& e)
556  {
557  log_sql_exception("Unable to delete addon authors for "+id+" and version "+instance_version+".", e);
558  }
559 }
560 
561 void dbconn::insert_addon_author(const std::string& instance_version, const std::string& id, const std::string author, int is_primary) {
562  try
563  {
564  modify(connection_, "INSERT INTO `"+db_addon_authors_table_+"`(INSTANCE_VERSION, ADDON_ID, AUTHOR, IS_PRIMARY) VALUES(?,?,?,?)",
565  { instance_version, id, author, is_primary });
566  }
567  catch(const mariadb::exception::base& e)
568  {
569  log_sql_exception("Unable to delete addon authors for "+id+" and version "+instance_version+".", e);
570  }
571 }
572 
573 bool dbconn::do_any_authors_exist(const std::string& instance_version, const std::string& id) {
574  try
575  {
576  return exists(connection_, "SELECT 1 FROM `"+db_addon_authors_table_+"` WHERE INSTANCE_VERSION = ? AND ADDON_ID = ?",
577  { instance_version, id });
578  }
579  catch(const mariadb::exception::base& e)
580  {
581  log_sql_exception("Unable to check whether authors exist for "+id+" for version "+instance_version+".", e);
582  return true;
583  }
584 }
585 
586 //
587 // handle complex query results
588 //
589 void dbconn::get_complex_results(mariadb::connection_ref connection, rs_base& base, const std::string& sql, const sql_parameters& params)
590 {
591  mariadb::result_set_ref rslt = select(connection, sql, params);
592  base.read(rslt);
593 }
594 //
595 // handle single values
596 //
597 std::string dbconn::get_single_string(mariadb::connection_ref connection, const std::string& sql, const sql_parameters& params)
598 {
599  mariadb::result_set_ref rslt = select(connection, sql, params);
600  if(rslt->next())
601  {
602  return rslt->get_string(0);
603  }
604  else
605  {
606  throw mariadb::exception::base("No string value found in the database!");
607  }
608 }
609 long dbconn::get_single_long(mariadb::connection_ref connection, const std::string& sql, const sql_parameters& params)
610 {
611  mariadb::result_set_ref rslt = select(connection, sql, params);
612  if(rslt->next())
613  {
614  // mariadbpp checks for strict integral equivalence, but we don't care
615  // so check the type beforehand, call the associated getter, and let it silently get upcast to a long if needed
616  // subselects also apparently return a decimal
617  switch(rslt->column_type(0))
618  {
619  case mariadb::value::type::decimal:
620  return static_cast<long>(rslt->get_decimal(0).float32());
621  case mariadb::value::type::unsigned8:
622  case mariadb::value::type::signed8:
623  return rslt->get_signed8(0);
624  case mariadb::value::type::unsigned16:
625  case mariadb::value::type::signed16:
626  return rslt->get_signed16(0);
627  case mariadb::value::type::unsigned32:
628  case mariadb::value::type::signed32:
629  return rslt->get_signed32(0);
630  case mariadb::value::type::unsigned64:
631  case mariadb::value::type::signed64:
632  return rslt->get_signed64(0);
633  default:
634  throw mariadb::exception::base("Value retrieved was not a long!");
635  }
636  }
637  else
638  {
639  throw mariadb::exception::base("No long value found in the database!");
640  }
641 }
642 bool dbconn::exists(mariadb::connection_ref connection, const std::string& sql, const sql_parameters& params)
643 {
644  mariadb::result_set_ref rslt = select(connection, sql, params);
645  return rslt->next();
646 }
647 
648 //
649 // select or modify values
650 //
651 mariadb::result_set_ref dbconn::select(mariadb::connection_ref connection, const std::string& sql, const sql_parameters& params)
652 {
653  try
654  {
655  mariadb::statement_ref stmt = query(connection, sql, params);
656  mariadb::result_set_ref rslt = mariadb::result_set_ref(stmt->query());
657  return rslt;
658  }
659  catch(const mariadb::exception::base& e)
660  {
661  ERR_SQL << "SQL query failed for query: `"+sql+"`";
662  throw e;
663  }
664 }
665 unsigned long long dbconn::modify(mariadb::connection_ref connection, const std::string& sql, const sql_parameters& params)
666 {
667  try
668  {
669  mariadb::statement_ref stmt = query(connection, sql, params);
670  unsigned long long count = stmt->execute();
671  return count;
672  }
673  catch(const mariadb::exception::base& e)
674  {
675  ERR_SQL << "SQL query failed for query: `"+sql+"`";
676  throw e;
677  }
678 }
679 unsigned long long dbconn::modify_get_id(mariadb::connection_ref connection, const std::string& sql, const sql_parameters& params)
680 {
681  try
682  {
683  mariadb::statement_ref stmt = query(connection, sql, params);
684  unsigned long long count = stmt->insert();
685  return count;
686  }
687  catch(const mariadb::exception::base& e)
688  {
689  ERR_SQL << "SQL query failed for query: `"+sql+"`";
690  throw e;
691  }
692 }
693 
694 mariadb::statement_ref dbconn::query(mariadb::connection_ref connection, const std::string& sql, const sql_parameters& params)
695 {
696  mariadb::statement_ref stmt = connection->create_statement(sql);
697 
698  unsigned i = 0;
699  for(const auto& param : params)
700  {
701  if(std::holds_alternative<bool>(param))
702  {
703  stmt->set_boolean(i, std::get<bool>(param));
704  }
705  else if(std::holds_alternative<int>(param))
706  {
707  stmt->set_signed32(i, std::get<int>(param));
708  }
709  else if(std::holds_alternative<unsigned long long>(param))
710  {
711  stmt->set_signed64(i, std::get<unsigned long long>(param));
712  }
713  else if(std::holds_alternative<std::string>(param))
714  {
715  stmt->set_string(i, std::get<std::string>(param));
716  }
717  else if(std::holds_alternative<const char*>(param))
718  {
719  stmt->set_string(i, std::get<const char*>(param));
720  }
721  i++;
722  }
723 
724  return stmt;
725 }
726 
727 #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:205
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