The Battle for Wesnoth  1.15.9+dev
dbconn.cpp
Go to the documentation of this file.
1 /*
2  Part of the Battle for Wesnoth Project https://www.wesnoth.org/
3 
4  This program is free software; you can redistribute it and/or modify
5  it under the terms of the GNU General Public License as published by
6  the Free Software Foundation; either version 2 of the License, or
7  (at your option) any later version.
8  This program is distributed in the hope that it will be useful,
9  but WITHOUT ANY WARRANTY.
10 
11  See the COPYING file for more details.
12 */
13 
14 #ifdef HAVE_MYSQLPP
15 
16 #include "server/common/dbconn.hpp"
20 #include "log.hpp"
21 
22 static lg::log_domain log_sql_handler("sql_executor");
23 #define ERR_SQL LOG_STREAM(err, log_sql_handler)
24 #define WRN_SQL LOG_STREAM(warn, log_sql_handler)
25 #define LOG_SQL LOG_STREAM(info, log_sql_handler)
26 #define DBG_SQL LOG_STREAM(debug, log_sql_handler)
27 
28 dbconn::dbconn(const config& c)
29  : db_users_table_(c["db_users_table"].str())
30  , db_banlist_table_(c["db_banlist_table"].str())
31  , db_extra_table_(c["db_extra_table"].str())
32  , db_game_info_table_(c["db_game_info_table"].str())
33  , db_game_player_info_table_(c["db_game_player_info_table"].str())
34  , db_game_content_info_table_(c["db_game_content_info_table"].str())
35  , db_user_group_table_(c["db_user_group_table"].str())
36  , db_tournament_query_(c["db_tournament_query"].str())
37 {
38  try
39  {
40  account_ = mariadb::account::create(c["db_host"].str(), c["db_user"].str(), c["db_password"].str());
41  account_->set_connect_option(mysql_option::MYSQL_SET_CHARSET_NAME, std::string("utf8mb4"));
42  account_->set_schema(c["db_name"].str());
43  // initialize the connection used to run synchronous queries.
44  connection_ = create_connection();
45  }
46  catch(const mariadb::exception::base& e)
47  {
48  log_sql_exception("Failed to connect to the database!", e);
49  }
50 }
51 
52 void dbconn::log_sql_exception(const std::string& text, const mariadb::exception::base& e)
53 {
54  ERR_SQL << text << '\n'
55  << "what: " << e.what() << '\n'
56  << "error id: " << e.error_id() << std::endl;
57 }
58 
59 mariadb::connection_ref dbconn::create_connection()
60 {
61  return mariadb::connection::create(account_);
62 }
63 
64 //
65 // queries
66 //
67 int dbconn::async_test_query(int limit)
68 {
69  std::string sql = "with recursive TEST(T) as "
70  "( "
71  "select 1 "
72  "union all "
73  "select T+1 from TEST where T < ? "
74  ") "
75  "select count(*) from TEST";
76  int t = get_single_long(create_connection(), sql, limit);
77  return t;
78 }
79 
80 std::string dbconn::get_uuid()
81 {
82  try
83  {
84  return get_single_string(connection_, "SELECT UUID()");
85  }
86  catch(const mariadb::exception::base& e)
87  {
88  log_sql_exception("Could not retrieve a UUID!", e);
89  return "";
90  }
91 }
92 
93 std::string dbconn::get_tournaments()
94 {
95  if(db_tournament_query_ == "")
96  {
97  return "";
98  }
99 
100  try
101  {
102  tournaments t;
103  get_complex_results(connection_, t, db_tournament_query_);
104  return t.str();
105  }
106  catch(const mariadb::exception::base& e)
107  {
108  log_sql_exception("Could not retrieve the tournaments!", e);
109  return "";
110  }
111 }
112 
113 std::unique_ptr<simple_wml::document> dbconn::get_game_history(int player_id, int offset)
114 {
115  try
116  {
117  std::string game_history_query = "select "
118 " game.GAME_NAME, "
119 " game.RELOAD, "
120 " game.START_TIME, "
121 " GROUP_CONCAT(CONCAT(player.USER_NAME, ':', player.FACTION)) as PLAYERS, "
122 " IFNULL(scenario.NAME, '') as SCENARIO_NAME, "
123 " IFNULL(scenario.ID, '') as SCENARIO_ID, "
124 " IFNULL(era.NAME, '') as ERA_NAME, "
125 " IFNULL(era.ID, '') as ERA_ID, "
126 " IFNULL(GROUP_CONCAT(distinct mods.NAME, '') as MODIFICATION_NAMES, "
127 " IFNULL(GROUP_CONCAT(distinct mods.ID), '') as MODIFICATION_IDS, "
128 " 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) as REPLAY_URL "
129 "from "+db_game_info_table_+" game "
130 "inner join "+db_game_player_info_table_+" player "
131 " on exists "
132 " ( "
133 " select 1 "
134 " from "+db_game_player_info_table_+" player1 "
135 " where game.INSTANCE_UUID = player1.INSTANCE_UUID "
136 " and game.GAME_ID = player1.GAME_ID "
137 " and player1.USER_ID = ? "
138 " ) "
139 " and game.INSTANCE_UUID = player.INSTANCE_UUID "
140 " and game.GAME_ID = player.GAME_ID "
141 " and player.USER_ID != -1 "
142 " and game.END_TIME is not NULL "
143 "inner join "+db_game_content_info_table_+" scenario "
144 " on scenario.TYPE = 'scenario' "
145 " and scenario.INSTANCE_UUID = game.INSTANCE_UUID "
146 " and scenario.GAME_ID = game.GAME_ID "
147 "inner join "+db_game_content_info_table_+" era "
148 " on era.TYPE = 'era' "
149 " and era.INSTANCE_UUID = game.INSTANCE_UUID "
150 " and era.GAME_ID = game.GAME_ID "
151 "left join "+db_game_content_info_table_+" mods "
152 " on mods.TYPE = 'modification' "
153 " and mods.INSTANCE_UUID = game.INSTANCE_UUID "
154 " and mods.GAME_ID = game.GAME_ID "
155 "group by game.INSTANCE_UUID, game.GAME_ID "
156 "order by game.START_TIME desc "
157 "limit 11 offset ? ";
158 
159  game_history gh;
160  get_complex_results(create_connection(), gh, game_history_query, player_id, offset);
161  return gh.to_doc();
162  }
163  catch(const mariadb::exception::base& e)
164  {
165  log_sql_exception("Could not retrieve the game history for forum ID `"+std::to_string(player_id)+"`!", e);
166  auto doc = std::make_unique<simple_wml::document>();
167  doc->set_attr("error", "Error retrieving game history.");
168  return doc;
169  }
170 }
171 
172 bool dbconn::user_exists(const std::string& name)
173 {
174  try
175  {
176  return exists(connection_, "SELECT 1 FROM `"+db_users_table_+"` WHERE UPPER(username)=UPPER(?)", name);
177  }
178  catch(const mariadb::exception::base& e)
179  {
180  log_sql_exception("Unable to check if user row for '"+name+"' exists!", e);
181  return false;
182  }
183 }
184 
185 long dbconn::get_forum_id(const std::string& name)
186 {
187  try
188  {
189  return get_single_long(connection_, "SELECT IFNULL((SELECT user_id FROM `"+db_users_table_+"` WHERE UPPER(username)=UPPER(?)), 0)", name);
190  }
191  catch(const mariadb::exception::base& e)
192  {
193  log_sql_exception("Unable to get user_id for '"+name+"'!", e);
194  return 0;
195  }
196 }
197 
198 bool dbconn::extra_row_exists(const std::string& name)
199 {
200  try
201  {
202  return exists(connection_, "SELECT 1 FROM `"+db_extra_table_+"` WHERE UPPER(username)=UPPER(?)", name);
203  }
204  catch(const mariadb::exception::base& e)
205  {
206  log_sql_exception("Unable to check if extra row for '"+name+"' exists!", e);
207  return false;
208  }
209 }
210 
211 bool dbconn::is_user_in_group(const std::string& name, int group_id)
212 {
213  try
214  {
215  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 = ?",
216  name, group_id);
217  }
218  catch(const mariadb::exception::base& e)
219  {
220  log_sql_exception("Unable to check if the user '"+name+"' is in group '"+std::to_string(group_id)+"'!", e);
221  return false;
222  }
223 }
224 
225 ban_check dbconn::get_ban_info(const std::string& name, const std::string& ip)
226 {
227  try
228  {
229  // selected ban_type value must be part of user_handler::BAN_TYPE
230  ban_check b;
231  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 >= ?)",
232  ip, name, name, std::time(nullptr));
233  return b;
234  }
235  catch(const mariadb::exception::base& e)
236  {
237  log_sql_exception("Failed to check ban info for user '"+name+"' connecting from ip '"+ip+"'!", e);
238  return ban_check();
239  }
240 }
241 
242 std::string dbconn::get_user_string(const std::string& table, const std::string& column, const std::string& name)
243 {
244  try
245  {
246  return get_single_string(connection_, "SELECT `"+column+"` from `"+table+"` WHERE UPPER(username)=UPPER(?)", name);
247  }
248  catch(const mariadb::exception::base& e)
249  {
250  log_sql_exception("Unable to query column `"+column+"` from table `"+table+"` for user `"+name+"`", e);
251  return "";
252  }
253 }
254 int dbconn::get_user_int(const std::string& table, const std::string& column, const std::string& name)
255 {
256  try
257  {
258  return static_cast<int>(get_single_long(connection_, "SELECT `"+column+"` from `"+table+"` WHERE UPPER(username)=UPPER(?)", name));
259  }
260  catch(const mariadb::exception::base& e)
261  {
262  log_sql_exception("Unable to query column `"+column+"` from table `"+table+"` for user `"+name+"`", e);
263  return 0;
264  }
265 }
266 void dbconn::write_user_int(const std::string& column, const std::string& name, int value)
267 {
268  try
269  {
270  if(!extra_row_exists(name))
271  {
272  modify(connection_, "INSERT INTO `"+db_extra_table_+"` VALUES(?,?,'0')", name, value);
273  }
274  modify(connection_, "UPDATE `"+db_extra_table_+"` SET "+column+"=? WHERE UPPER(username)=UPPER(?)", value, name);
275  }
276  catch(const mariadb::exception::base& e)
277  {
278  log_sql_exception("Unable to write `"+std::to_string(value)+"` to column `"+column+"` on table `"+db_extra_table_+"` for user `"+name+"`", e);
279  }
280 }
281 
282 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)
283 {
284  try
285  {
286  modify(connection_, "INSERT INTO `"+db_game_info_table_+"`(INSTANCE_UUID, GAME_ID, INSTANCE_VERSION, GAME_NAME, RELOAD, OBSERVERS, PUBLIC, PASSWORD) VALUES(?, ?, ?, ?, ?, ?, ?, ?)",
287  uuid, game_id, version, name, reload, observers, is_public, has_password);
288  }
289  catch(const mariadb::exception::base& e)
290  {
291  log_sql_exception("Failed to insert game info row for UUID `"+uuid+"` and game ID `"+std::to_string(game_id)+"`", e);
292  }
293 }
294 void dbconn::update_game_end(const std::string& uuid, int game_id, const std::string& replay_location)
295 {
296  try
297  {
298  modify(connection_, "UPDATE `"+db_game_info_table_+"` SET END_TIME = CURRENT_TIMESTAMP, REPLAY_NAME = ? WHERE INSTANCE_UUID = ? AND GAME_ID = ?",
299  replay_location, uuid, game_id);
300  }
301  catch(const mariadb::exception::base& e)
302  {
303  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);
304  }
305 }
306 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)
307 {
308  try
309  {
310  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), ?, ?, ?, ?, ?, ?)",
311  uuid, game_id, username, side_number, is_host, faction, version, source, current_user);
312  }
313  catch(const mariadb::exception::base& e)
314  {
315  log_sql_exception("Failed to insert game player info row for UUID `"+uuid+"` and game ID `"+std::to_string(game_id)+"`", e);
316  }
317 }
318 void 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& source, const std::string& version)
319 {
320  try
321  {
322  modify(connection_, "INSERT INTO `"+db_game_content_info_table_+"`(INSTANCE_UUID, GAME_ID, TYPE, NAME, ID, SOURCE, VERSION) VALUES(?, ?, ?, ?, ?, ?, ?)",
323  uuid, game_id, type, name, id, source, version);
324  }
325  catch(const mariadb::exception::base& e)
326  {
327  log_sql_exception("Failed to insert game content info row for UUID `"+uuid+"` and game ID `"+std::to_string(game_id)+"`", e);
328  }
329 }
330 void dbconn::set_oos_flag(const std::string& uuid, int game_id)
331 {
332  try
333  {
334  modify(connection_, "UPDATE `"+db_game_info_table_+"` SET OOS = 1 WHERE INSTANCE_UUID = ? AND GAME_ID = ?",
335  uuid, game_id);
336  }
337  catch(const mariadb::exception::base& e)
338  {
339  log_sql_exception("Failed to set the OOS flag for UUID `"+uuid+"` and game ID `"+std::to_string(game_id)+"`", e);
340  }
341 }
342 
343 //
344 // handle complex query results
345 //
346 template<typename... Args>
347 void dbconn::get_complex_results(mariadb::connection_ref connection, rs_base& base, const std::string& sql, Args&&... args)
348 {
349  mariadb::result_set_ref rslt = select(connection, sql, args...);
350  base.read(rslt);
351 }
352 //
353 // handle single values
354 //
355 template<typename... Args>
356 std::string dbconn::get_single_string(mariadb::connection_ref connection, const std::string& sql, Args&&... args)
357 {
358  mariadb::result_set_ref rslt = select(connection, sql, args...);
359  if(rslt->next())
360  {
361  return rslt->get_string(0);
362  }
363  else
364  {
365  throw mariadb::exception::base("No string value found in the database!");
366  }
367 }
368 template<typename... Args>
369 long dbconn::get_single_long(mariadb::connection_ref connection, const std::string& sql, Args&&... args)
370 {
371  mariadb::result_set_ref rslt = select(connection, sql, args...);
372  if(rslt->next())
373  {
374  // mariadbpp checks for strict integral equivalence, but we don't care
375  // so check the type beforehand, call the associated getter, and let it silently get upcast to a long if needed
376  // subselects also apparently return a decimal
377  switch(rslt->column_type(0))
378  {
379  case mariadb::value::type::decimal:
380  return static_cast<long>(rslt->get_decimal(0).float32());
381  case mariadb::value::type::unsigned8:
382  case mariadb::value::type::signed8:
383  return rslt->get_signed8(0);
384  case mariadb::value::type::unsigned16:
385  case mariadb::value::type::signed16:
386  return rslt->get_signed16(0);
387  case mariadb::value::type::unsigned32:
388  case mariadb::value::type::signed32:
389  return rslt->get_signed32(0);
390  case mariadb::value::type::unsigned64:
391  case mariadb::value::type::signed64:
392  return rslt->get_signed64(0);
393  default:
394  throw mariadb::exception::base("Value retrieved was not a long!");
395  }
396  }
397  else
398  {
399  throw mariadb::exception::base("No long value found in the database!");
400  }
401 }
402 template<typename... Args>
403 bool dbconn::exists(mariadb::connection_ref connection, const std::string& sql, Args&&... args)
404 {
405  mariadb::result_set_ref rslt = select(connection, sql, args...);
406  return rslt->next();
407 }
408 
409 //
410 // select or modify values
411 //
412 template<typename... Args>
413 mariadb::result_set_ref dbconn::select(mariadb::connection_ref connection, const std::string& sql, Args&&... args)
414 {
415  try
416  {
417  mariadb::statement_ref stmt = query(connection, sql, args...);
418  mariadb::result_set_ref rslt = mariadb::result_set_ref(stmt->query());
419  return rslt;
420  }
421  catch(const mariadb::exception::base& e)
422  {
423  ERR_SQL << "SQL query failed for query: `"+sql+"`" << std::endl;
424  throw e;
425  }
426 }
427 template<typename... Args>
428 int dbconn::modify(mariadb::connection_ref connection, const std::string& sql, Args&&... args)
429 {
430  try
431  {
432  mariadb::statement_ref stmt = query(connection, sql, args...);
433  int count = stmt->insert();
434  return count;
435  }
436  catch(const mariadb::exception::base& e)
437  {
438  ERR_SQL << "SQL query failed for query: `"+sql+"`" << std::endl;
439  throw e;
440  }
441 }
442 
443 
444 
445 
446 template<typename... Args>
447 mariadb::statement_ref dbconn::query(mariadb::connection_ref connection, const std::string& sql, Args&&... args)
448 {
449  mariadb::statement_ref stmt = connection->create_statement(sql);
450  prepare(stmt, 0, args...);
451  return stmt;
452 }
453 
454 template<typename Arg, typename... Args>
455 void dbconn::prepare(mariadb::statement_ref stmt, int i, Arg arg, Args&&... args)
456 {
457  i = prepare(stmt, i, arg);
458  prepare(stmt, i, args...);
459 }
460 
461 template<>
462 int dbconn::prepare(mariadb::statement_ref stmt, int i, int arg)
463 {
464  stmt->set_signed32(i++, arg);
465  return i;
466 }
467 template<>
468 int dbconn::prepare(mariadb::statement_ref stmt, int i, long arg)
469 {
470  stmt->set_signed64(i++, arg);
471  return i;
472 }
473 template<>
474 int dbconn::prepare(mariadb::statement_ref stmt, int i, const char* arg)
475 {
476  stmt->set_string(i++, arg);
477  return i;
478 }
479 template<>
480 int dbconn::prepare(mariadb::statement_ref stmt, int i, std::string arg)
481 {
482  stmt->set_string(i++, arg);
483  return i;
484 }
485 
486 void dbconn::prepare(mariadb::statement_ref, int){}
487 
488 #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)
std::string get_tournaments()
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 exists(const image::locator &i_locator)
Returns true if the given image actually exists, without loading it.
Definition: picture.cpp:1010
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_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 &source, const std::string &version)
long get_single_long(mariadb::connection_ref connection, const std::string &sql, Args &&... args)
bool is_user_in_group(const std::string &name, int group_id)
std::string get_uuid()
std::size_t i
Definition: function.cpp:933
void set_oos_flag(const std::string &uuid, int game_id)
bool user_exists(const std::string &name)
std::string name
Definition: sdl_ttf.cpp:70
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 ...
long get_forum_id(const std::string &name)
int modify(mariadb::connection_ref connection, const std::string &sql, Args &&... args)
Executes non-select statements (ie: insert, update, delete).
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:64
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:39
ban_check get_ban_info(const std::string &name, const std::string &ip)
A config object defines a single node in a WML file, with access to child nodes.
Definition: config.hpp:59
mock_char c
std::unique_ptr< simple_wml::document > to_doc()
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)