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