The Battle for Wesnoth  1.15.3+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 "dbconn.hpp"
18 #include "resultsets/ban_check.hpp"
19 #include "log.hpp"
20 
21 static lg::log_domain log_sql_handler("sql_executor");
22 #define ERR_SQL LOG_STREAM(err, log_sql_handler)
23 #define WRN_SQL LOG_STREAM(warn, log_sql_handler)
24 #define LOG_SQL LOG_STREAM(info, log_sql_handler)
25 #define DBG_SQL LOG_STREAM(debug, log_sql_handler)
26 
27 dbconn::dbconn(const config& c)
28  : db_users_table_(c["db_users_table"].str())
29  , db_banlist_table_(c["db_banlist_table"].str())
30  , db_extra_table_(c["db_extra_table"].str())
31  , db_game_info_table_(c["db_game_info_table"].str())
32  , db_game_player_info_table_(c["db_game_player_info_table"].str())
33  , db_game_modification_info_table_(c["db_game_modification_info_table"].str())
34  , db_user_group_table_(c["db_user_group_table"].str())
35  , db_tournament_query_(c["db_tournament_query"].str())
36 {
37  try
38  {
39  // NOTE: settings put on the connection, rather than the account, are NOT kept if a reconnect occurs!
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  connection_ = mariadb::connection::create(account_);
44  }
45  catch(const mariadb::exception::base& e)
46  {
47  log_sql_exception("Failed to connect to the database!", e);
48  }
49 }
50 
52 {
53  ERR_SQL << text << '\n'
54  << "what: " << e.what() << '\n'
55  << "error id: " << e.error_id() << std::endl;
56 }
57 
58 //
59 // queries
60 //
62 {
63  try
64  {
65  return get_single_string("SELECT UUID()");
66  }
67  catch(const mariadb::exception::base& e)
68  {
69  log_sql_exception("Could not retrieve a UUID!", e);
70  return "";
71  }
72 }
73 
75 {
76  if(db_tournament_query_ == "")
77  {
78  return "";
79  }
80 
81  try
82  {
83  tournaments t;
84  get_complex_results(t, db_tournament_query_);
85  return t.str();
86  }
87  catch(const mariadb::exception::base& e)
88  {
89  log_sql_exception("Could not retrieve the tournaments!", e);
90  return "";
91  }
92 }
93 
94 bool dbconn::user_exists(const std::string& name)
95 {
96  try
97  {
98  return exists("SELECT 1 FROM `"+db_users_table_+"` WHERE UPPER(username)=UPPER(?)", name);
99  }
100  catch(const mariadb::exception::base& e)
101  {
102  log_sql_exception("Unable to check if user row for '"+name+"' exists!", e);
103  return false;
104  }
105 }
106 
107 bool dbconn::extra_row_exists(const std::string& name)
108 {
109  try
110  {
111  return exists("SELECT 1 FROM `"+db_extra_table_+"` WHERE UPPER(username)=UPPER(?)", name);
112  }
113  catch(const mariadb::exception::base& e)
114  {
115  log_sql_exception("Unable to check if extra row for '"+name+"' exists!", e);
116  return false;
117  }
118 }
119 
120 bool dbconn::is_user_in_group(const std::string& name, int group_id)
121 {
122  try
123  {
124  return exists("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 = ?", name, group_id);
125  }
126  catch(const mariadb::exception::base& e)
127  {
128  log_sql_exception("Unable to check if the user '"+name+"' is in group '"+std::to_string(group_id)+"'!", e);
129  return false;
130  }
131 }
132 
134 {
135  try
136  {
137  // selected ban_type value must be part of user_handler::BAN_TYPE
138  ban_check b;
139  get_complex_results(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 >= ?)", ip, name, name, std::time(nullptr));
140  return b;
141  }
142  catch(const mariadb::exception::base& e)
143  {
144  log_sql_exception("Failed to check ban info for user '"+name+"' connecting from ip '"+ip+"'!", e);
145  return ban_check();
146  }
147 }
148 
149 std::string dbconn::get_user_string(const std::string& table, const std::string& column, const std::string& name)
150 {
151  try
152  {
153  return get_single_string("SELECT `"+column+"` from `"+table+"` WHERE UPPER(username)=UPPER(?)", name);
154  }
155  catch(const mariadb::exception::base& e)
156  {
157  log_sql_exception("Unable to query column `"+column+"` from table `"+table+"` for user `"+name+"`", e);
158  return "";
159  }
160 }
161 int dbconn::get_user_int(const std::string& table, const std::string& column, const std::string& name)
162 {
163  try
164  {
165  return get_single_int("SELECT `"+column+"` from `"+table+"` WHERE UPPER(username)=UPPER(?)", name);
166  }
167  catch(const mariadb::exception::base& e)
168  {
169  log_sql_exception("Unable to query column `"+column+"` from table `"+table+"` for user `"+name+"`", e);
170  return 0;
171  }
172 }
173 void dbconn::write_user_int(const std::string& column, const std::string& name, int value)
174 {
175  try
176  {
177  if(!extra_row_exists(name))
178  {
179  modify("INSERT INTO `"+db_extra_table_+"` VALUES(?,?,'0')", name, value);
180  }
181  modify("UPDATE `"+db_extra_table_+"` SET "+column+"=? WHERE UPPER(username)=UPPER(?)", value, name);
182  }
183  catch(const mariadb::exception::base& e)
184  {
185  log_sql_exception("Unable to write `"+std::to_string(value)+"` to column `"+column+"` on table `"+db_extra_table_+"` for user `"+name+"`", e);
186  }
187 }
188 
189 void dbconn::insert_game_info(const std::string& uuid, int game_id, const std::string& version, const std::string& name, const std::string& map_name, const std::string& era_name, int reload, int observers, int is_public, int has_password, const std::string& map_source, const std::string& map_version, const std::string& era_source, const std::string& era_version)
190 {
191  try
192  {
193  modify("INSERT INTO `"+db_game_info_table_+"`(INSTANCE_UUID, GAME_ID, INSTANCE_VERSION, GAME_NAME, MAP_NAME, ERA_NAME, RELOAD, OBSERVERS, PUBLIC, PASSWORD, MAP_SOURCE_ADDON, MAP_VERSION, ERA_SOURCE_ADDON, ERA_VERSION) VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)",
194  uuid, game_id, version, name, map_name, era_name, reload, observers, is_public, has_password, map_source, map_version, era_source, era_version);
195  }
196  catch(const mariadb::exception::base& e)
197  {
198  log_sql_exception("Failed to insert game info row for UUID `"+uuid+"` and game ID `"+std::to_string(game_id)+"`", e);
199  }
200 }
201 void dbconn::update_game_end(const std::string& uuid, int game_id, const std::string& replay_location)
202 {
203  try
204  {
205  modify("UPDATE `"+db_game_info_table_+"` SET END_TIME = CURRENT_TIMESTAMP, REPLAY_NAME = ? WHERE INSTANCE_UUID = ? AND GAME_ID = ?",
206  replay_location, uuid, game_id);
207  }
208  catch(const mariadb::exception::base& e)
209  {
210  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);
211  }
212 }
213 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)
214 {
215  try
216  {
217  modify("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), ?, ?, ?, ?, ?, ?)",
218  uuid, game_id, username, side_number, is_host, faction, version, source, current_user);
219  }
220  catch(const mariadb::exception::base& e)
221  {
222  log_sql_exception("Failed to insert game player info row for UUID `"+uuid+"` and game ID `"+std::to_string(game_id)+"`", e);
223  }
224 }
225 void dbconn::insert_modification_info(const std::string& uuid, int game_id, const std::string& modification_name, const std::string& modification_source, const std::string& modification_version)
226 {
227  try
228  {
229  modify("INSERT INTO `"+db_game_modification_info_table_+"`(INSTANCE_UUID, GAME_ID, MODIFICATION_NAME, SOURCE_ADDON, VERSION) VALUES(?, ?, ?, ?, ?)",
230  uuid, game_id, modification_name, modification_source, modification_version);
231  }
232  catch(const mariadb::exception::base& e)
233  {
234  log_sql_exception("Failed to insert game modification info row for UUID `"+uuid+"` and game ID `"+std::to_string(game_id)+"`", e);
235  }
236 }
237 void dbconn::set_oos_flag(const std::string& uuid, int game_id)
238 {
239  try
240  {
241  modify("UPDATE `"+db_game_info_table_+"` SET OOS = 1 WHERE INSTANCE_UUID = ? AND GAME_ID = ?",
242  uuid, game_id);
243  }
244  catch(const mariadb::exception::base& e)
245  {
246  log_sql_exception("Failed to set the OOS flag for UUID `"+uuid+"` and game ID `"+std::to_string(game_id)+"`", e);
247  }
248 }
249 
250 //
251 // queries can return data with various types that can't be easily fit into a pre-determined structure
252 // therefore for queries that can return multiple rows of multiple columns, implement a class to define how the results should be read
253 //
254 template<typename... Args>
255 void dbconn::get_complex_results(rs_base& base, const std::string& sql, Args&&... args)
256 {
257  mariadb::result_set_ref rslt = select(sql, args...);
258  base.read(rslt);
259 }
260 //
261 // get single values
262 //
263 template<typename... Args>
264 std::string dbconn::get_single_string(const std::string& sql, Args&&... args)
265 {
266  mariadb::result_set_ref rslt = select(sql, args...);
267  if(rslt->next())
268  {
269  return rslt->get_string(0);
270  }
271  else
272  {
273  throw mariadb::exception::base("No string value found in the database!");
274  }
275 }
276 template<typename... Args>
277 int dbconn::get_single_int(const std::string& sql, Args&&... args)
278 {
279  mariadb::result_set_ref rslt = select(sql, args...);
280  if(rslt->next())
281  {
282  // mariadbpp checks for strict integral equivalence, but we don't care
283  // so check the type beforehand, call the associated getter, and let it silently get upcast to an int if needed
284  switch(rslt->column_type(0))
285  {
288  return rslt->get_signed8(0);
291  return rslt->get_signed16(0);
294  return rslt->get_signed32(0);
295  default:
296  throw mariadb::exception::base("Value retrieved was not an int!");
297  }
298  }
299  else
300  {
301  throw mariadb::exception::base("No int value found in the database!");
302  }
303 }
304 template<typename... Args>
305 bool dbconn::exists(const std::string& sql, Args&&... args)
306 {
307  mariadb::result_set_ref rslt = select(sql, args...);
308  return rslt->next();
309 }
310 
311 //
312 // select or modify values
313 //
314 template<typename... Args>
315 mariadb::result_set_ref dbconn::select(const std::string& sql, Args&&... args)
316 {
317  try
318  {
319  mariadb::statement_ref stmt = query(sql, args...);
320  return mariadb::result_set_ref(stmt->query());
321  }
322  catch(const mariadb::exception::base& e)
323  {
324  if(!connection_->connected())
325  {
326  ERR_SQL << "Connection is invalid!" << std::endl;
327  }
328  ERR_SQL << "SQL query failed for query: `"+sql+"`!" << std::endl;
329  throw e;
330  }
331 }
332 template<typename... Args>
333 int dbconn::modify(const std::string& sql, Args&&... args)
334 {
335  try
336  {
337  mariadb::statement_ref stmt = query(sql, args...);
338  return stmt->insert();
339  }
340  catch(const mariadb::exception::base& e)
341  {
342  if(!connection_->connected())
343  {
344  ERR_SQL << "Connection is invalid!" << std::endl;
345  }
346  ERR_SQL << "SQL query failed for query: `"+sql+"`!" << std::endl;
347  throw e;
348  }
349 }
350 
351 //
352 // start of recursive unpacking of variadic template in order to be able to call correct parameterized setters on query
353 //
354 template<typename... Args>
355 mariadb::statement_ref dbconn::query(const std::string& sql, Args&&... args)
356 {
357  mariadb::statement_ref stmt = connection_->create_statement(sql);
358  prepare(stmt, 0, args...);
359  return stmt;
360 }
361 // split off the next parameter
362 template<typename Arg, typename... Args>
363 void dbconn::prepare(mariadb::statement_ref stmt, int i, Arg arg, Args&&... args)
364 {
365  i = prepare(stmt, i, arg);
366  prepare(stmt, i, args...);
367 }
368 // template specialization for supported parameter types
369 // there are other parameter setters, but so far there hasn't been a reason to add them
370 template<>
371 int dbconn::prepare(mariadb::statement_ref stmt, int i, int arg)
372 {
373  stmt->set_signed32(i++, arg);
374  return i;
375 }
376 template<>
377 int dbconn::prepare(mariadb::statement_ref stmt, int i, long arg)
378 {
379  stmt->set_signed64(i++, arg);
380  return i;
381 }
382 template<>
383 int dbconn::prepare(mariadb::statement_ref stmt, int i, const char* arg)
384 {
385  stmt->set_string(i++, arg);
386  return i;
387 }
388 template<>
390 {
391  stmt->set_string(i++, arg);
392  return i;
393 }
394 // no more parameters, nothing left to do
396 
397 #endif //HAVE_MYSQLPP
void prepare(mariadb::statement_ref stmt, int i, Arg arg, Args &&... args)
std::shared_ptr< result_set > result_set_ref
Definition: result_set.hpp:219
std::string get_user_string(const std::string &table, const std::string &column, const std::string &name)
std::string get_tournaments()
void insert_modification_info(const std::string &uuid, int game_id, const std::string &modification_name, const std::string &modification_source, const std::string &modification_version)
int get_user_int(const std::string &table, const std::string &column, const std::string &name)
int modify(const std::string &sql, Args &&... args)
std::string get_single_string(const std::string &sql, Args &&... args)
std::string str
Definition: statement.cpp:110
dbconn(const config &c)
mariadb::statement_ref query(const std::string &sql, Args &&... args)
#define b
void insert_game_info(const std::string &uuid, int game_id, const std::string &version, const std::string &name, const std::string &map_name, const std::string &era_name, int reload, int observers, int is_public, int has_password, const std::string &map_source, const std::string &map_version, const std::string &era_source, const std::string &era_version)
bool exists(const image::locator &i_locator)
returns true if the given image actually exists, without loading it.
Definition: picture.cpp:1028
virtual void read(mariadb::result_set_ref rslt)=0
void write_user_int(const std::string &column, const std::string &name, int value)
virtual const char * what() const
Definition: exceptions.hpp:39
mariadb::result_set_ref select(const std::string &sql, Args &&... args)
static connection_ref create(const account_ref &account)
Creates a new connection using the given account.
Definition: connection.cpp:28
bool is_user_in_group(const std::string &name, int group_id)
std::string get_uuid()
std::size_t i
Definition: function.cpp:933
static account_ref create(const std::string &host_name, const std::string &user_name, const std::string &password, const std::string &schema="", u32 port=3306, const std::string &unix_socket="")
Create an account.
Definition: account.cpp:100
void set_oos_flag(const std::string &uuid, int game_id)
void get_complex_results(rs_base &base, const std::string &sql, Args &&... args)
bool user_exists(const std::string &name)
std::string str()
void log_sql_exception(const std::string &text, const mariadb::exception::base &e)
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)
#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:68
std::shared_ptr< statement > statement_ref
Definition: statement.hpp:109
mock_char c
bool exists(const std::string &sql, Args &&... args)
int get_single_int(const std::string &sql, Args &&... args)
void update_game_end(const std::string &uuid, int game_id, const std::string &replay_location)
bool extra_row_exists(const std::string &name)