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) 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())
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());
44 connection_ = create_connection();
46 catch(
const mariadb::exception::base&
e)
48 log_sql_exception(
"Failed to connect to the database!", e);
54 ERR_SQL << text <<
'\n' 55 <<
"what: " << e.what() <<
'\n' 56 <<
"error id: " << e.error_id() << std::endl;
61 return mariadb::connection::create(account_);
69 std::string sql =
"with recursive TEST(T) as " 73 "select T+1 from TEST where T < ? " 75 "select count(*) from TEST";
76 int t = get_single_long(create_connection(), sql, limit);
84 return get_single_string(connection_,
"SELECT UUID()");
86 catch(
const mariadb::exception::base& e)
88 log_sql_exception(
"Could not retrieve a UUID!", e);
95 if(db_tournament_query_ ==
"")
103 get_complex_results(connection_, t, db_tournament_query_);
106 catch(
const mariadb::exception::base& e)
108 log_sql_exception(
"Could not retrieve the tournaments!", e);
117 std::string game_history_query =
"select " 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 " 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 = ? " 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 ? ";
160 get_complex_results(create_connection(), gh, game_history_query, player_id, offset);
163 catch(
const mariadb::exception::base& e)
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.");
176 return exists(connection_,
"SELECT 1 FROM `"+db_users_table_+
"` WHERE UPPER(username)=UPPER(?)", name);
178 catch(
const mariadb::exception::base& e)
180 log_sql_exception(
"Unable to check if user row for '"+name+
"' exists!", e);
189 return get_single_long(connection_,
"SELECT IFNULL((SELECT user_id FROM `"+db_users_table_+
"` WHERE UPPER(username)=UPPER(?)), 0)", name);
191 catch(
const mariadb::exception::base& e)
193 log_sql_exception(
"Unable to get user_id for '"+name+
"'!", e);
202 return exists(connection_,
"SELECT 1 FROM `"+db_extra_table_+
"` WHERE UPPER(username)=UPPER(?)", name);
204 catch(
const mariadb::exception::base& e)
206 log_sql_exception(
"Unable to check if extra row for '"+name+
"' exists!", e);
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 = ?",
218 catch(
const mariadb::exception::base& e)
220 log_sql_exception(
"Unable to check if the user '"+name+
"' is in group '"+std::to_string(group_id)+
"'!", e);
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));
235 catch(
const mariadb::exception::base& e)
237 log_sql_exception(
"Failed to check ban info for user '"+name+
"' connecting from ip '"+ip+
"'!", e);
242 std::string
dbconn::get_user_string(
const std::string& table,
const std::string& column,
const std::string& name)
246 return get_single_string(connection_,
"SELECT `"+column+
"` from `"+table+
"` WHERE UPPER(username)=UPPER(?)", name);
248 catch(
const mariadb::exception::base& e)
250 log_sql_exception(
"Unable to query column `"+column+
"` from table `"+table+
"` for user `"+name+
"`", e);
254 int dbconn::get_user_int(
const std::string& table,
const std::string& column,
const std::string& name)
258 return static_cast<int>(get_single_long(connection_,
"SELECT `"+column+
"` from `"+table+
"` WHERE UPPER(username)=UPPER(?)", name));
260 catch(
const mariadb::exception::base& e)
262 log_sql_exception(
"Unable to query column `"+column+
"` from table `"+table+
"` for user `"+name+
"`", e);
270 if(!extra_row_exists(name))
272 modify(connection_,
"INSERT INTO `"+db_extra_table_+
"` VALUES(?,?,'0')", name, value);
274 modify(connection_,
"UPDATE `"+db_extra_table_+
"` SET "+column+
"=? WHERE UPPER(username)=UPPER(?)", value, name);
276 catch(
const mariadb::exception::base& e)
278 log_sql_exception(
"Unable to write `"+std::to_string(value)+
"` to column `"+column+
"` on table `"+db_extra_table_+
"` for user `"+name+
"`", e);
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)
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);
289 catch(
const mariadb::exception::base& e)
291 log_sql_exception(
"Failed to insert game info row for UUID `"+uuid+
"` and game ID `"+std::to_string(game_id)+
"`", e);
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);
301 catch(
const mariadb::exception::base& e)
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);
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)
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);
313 catch(
const mariadb::exception::base& e)
315 log_sql_exception(
"Failed to insert game player info row for UUID `"+uuid+
"` and game ID `"+std::to_string(game_id)+
"`", e);
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)
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);
325 catch(
const mariadb::exception::base& e)
327 log_sql_exception(
"Failed to insert game content info row for UUID `"+uuid+
"` and game ID `"+std::to_string(game_id)+
"`", e);
334 modify(connection_,
"UPDATE `"+db_game_info_table_+
"` SET OOS = 1 WHERE INSTANCE_UUID = ? AND GAME_ID = ?",
337 catch(
const mariadb::exception::base& e)
339 log_sql_exception(
"Failed to set the OOS flag for UUID `"+uuid+
"` and game ID `"+std::to_string(game_id)+
"`", e);
346 template<
typename... Args>
349 mariadb::result_set_ref rslt = select(connection, sql, args...);
355 template<
typename... Args>
358 mariadb::result_set_ref rslt = select(connection, sql, args...);
361 return rslt->get_string(0);
365 throw mariadb::exception::base(
"No string value found in the database!");
368 template<
typename... Args>
371 mariadb::result_set_ref rslt = select(connection, sql, args...);
377 switch(rslt->column_type(0))
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);
394 throw mariadb::exception::base(
"Value retrieved was not a long!");
399 throw mariadb::exception::base(
"No long value found in the database!");
402 template<
typename... Args>
403 bool dbconn::exists(mariadb::connection_ref connection,
const std::string& sql, Args&&... args)
405 mariadb::result_set_ref rslt = select(connection, sql, args...);
412 template<
typename... Args>
413 mariadb::result_set_ref
dbconn::select(mariadb::connection_ref connection,
const std::string& sql, Args&&... args)
417 mariadb::statement_ref stmt = query(connection, sql, args...);
418 mariadb::result_set_ref rslt = mariadb::result_set_ref(stmt->query());
421 catch(
const mariadb::exception::base& e)
423 ERR_SQL <<
"SQL query failed for query: `"+sql+
"`" << std::endl;
427 template<
typename... Args>
428 int dbconn::modify(mariadb::connection_ref connection,
const std::string& sql, Args&&... args)
432 mariadb::statement_ref stmt = query(connection, sql, args...);
433 int count = stmt->insert();
436 catch(
const mariadb::exception::base& e)
438 ERR_SQL <<
"SQL query failed for query: `"+sql+
"`" << std::endl;
446 template<
typename... Args>
447 mariadb::statement_ref
dbconn::query(mariadb::connection_ref connection,
const std::string& sql, Args&&... args)
449 mariadb::statement_ref stmt = connection->create_statement(sql);
450 prepare(stmt, 0, args...);
454 template<
typename Arg,
typename... Args>
455 void dbconn::prepare(mariadb::statement_ref stmt,
int i, Arg arg, Args&&... args)
457 i = prepare(stmt, i, arg);
458 prepare(stmt, i, args...);
464 stmt->set_signed32(i++, arg);
470 stmt->set_signed64(i++, arg);
474 int dbconn::prepare(mariadb::statement_ref stmt,
int i,
const char* arg)
476 stmt->set_string(i++, arg);
480 int dbconn::prepare(mariadb::statement_ref stmt,
int i, std::string arg)
482 stmt->set_string(i++, arg);
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'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...
bool exists(const image::locator &i_locator)
Returns true if the given image actually exists, without loading it.
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)
void set_oos_flag(const std::string &uuid, int game_id)
bool user_exists(const std::string &name)
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.
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 ¤t_user)
mariadb::result_set_ref select(mariadb::connection_ref connection, const std::string &sql, Args &&... args)
Executes a select statement.
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.
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)