The Battle for Wesnoth  1.17.0-dev
dbconn.hpp
Go to the documentation of this file.
1 /*
2  Copyright (C) 2020 - 2021
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 #pragma once
16 
17 #include "config.hpp"
21 
22 #include <mysql/mysql.h>
23 #include "mariadb++/account.hpp"
24 #include "mariadb++/connection.hpp"
25 #include "mariadb++/statement.hpp"
26 #include "mariadb++/result_set.hpp"
27 #include "mariadb++/exceptions.hpp"
28 
29 #include <vector>
30 #include <unordered_map>
31 
32 /**
33  * This class is responsible for handling the database connections as well as executing queries and handling any results.
34  * @note The account and connection should never ever be provided to anything outside of this class.
35  * @note !DO NOT APPEND VALUES DIRECTLY TO THE SQL TEXT IN ANY QUERY!
36  */
37 class dbconn
38 {
39  public:
40  /**
41  * Initializes the synchronous query connection as well as the account object that has the connection settings.
42  *
43  * @param c The config object to read information from.
44  */
45  dbconn(const config& c);
46 
47  /**
48  * @see forum_user_handler::async_test_query().
49  */
50  int async_test_query(int limit);
51 
52  /**
53  * @see forum_user_handler::get_uuid().
54  */
55  std::string get_uuid();
56 
57  /**
58  * @see forum_user_handler::get_tournaments().
59  */
60  std::string get_tournaments();
61 
62  /**
63  * This is an asynchronous query that is executed on a separate connection to retrieve the game history for the provided player.
64  *
65  * @param player_id The forum ID of the player to get the game history for.
66  * @param offset The offset to provide to the database for where to start returning rows from.
67  * @return The simple_wml document containing the query results, or simply the @a error attribute if an exception is thrown.
68  */
69  std::unique_ptr<simple_wml::document> get_game_history(int player_id, int offset);
70 
71  /**
72  * @see forum_user_handler::user_exists().
73  */
74  bool user_exists(const std::string& name);
75 
76  /**
77  * @see forum_user_handler::get_forum_id().
78  */
79  long get_forum_id(const std::string& name);
80 
81  /**
82  * @param name The player's username.
83  * @return Whether the player has a row in the extra table.
84  */
85  bool extra_row_exists(const std::string& name);
86 
87  /**
88  * @see forum_user_handler::is_user_in_group().
89  */
90  bool is_user_in_group(const std::string& name, int group_id);
91 
92  /**
93  * @param table The table that will be queried.
94  * @param column The column that will be selected.
95  * @param name The player's username.
96  * @return The string value in the provided table and column for the provided username.
97  */
98  std::string get_user_string(const std::string& table, const std::string& column, const std::string& name);
99 
100  /**
101  * @param table The table that will be queried.
102  * @param column The column that will be selected.
103  * @param name The player's username.
104  * @return The int value in the provided table and column for the provided username.
105  */
106  int get_user_int(const std::string& table, const std::string& column, const std::string& name);
107 
108  /**
109  * The provided value is updated if a row exists or a new row inserted otherwise.
110  *
111  * @param column The column that the value will be put into.
112  * @param name The player's username.
113  * @param value The value to be put into the column.
114  */
115  void write_user_int(const std::string& column, const std::string& name, int value);
116 
117  /**
118  * @see forum_user_handler::user_is_banned().
119  */
120  ban_check get_ban_info(const std::string& name, const std::string& ip);
121 
122  /**
123  * @see forum_user_handler::db_insert_game_info().
124  */
125  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);
126 
127  /**
128  * @see forum_user_handler::db_update_game_end().
129  */
130  void update_game_end(const std::string& uuid, int game_id, const std::string& replay_location);
131 
132  /**
133  * @see forum_user_handler::db_insert_game_player_info().
134  */
135  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);
136 
137  /**
138  * @see forum_user_handler::db_insert_game_content_info().
139  */
140  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);
141 
142  /**
143  * @see forum_user_handler::db_set_oos_flag().
144  */
145  void set_oos_flag(const std::string& uuid, int game_id);
146 
147  /**
148  * @see forum_user_handler::db_topic_id_exists().
149  */
150  bool topic_id_exists(int topic_id);
151 
152  /**
153  * @see forum_user_handler::db_insert_addon_info().
154  */
155  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);
156 
157  /**
158  * @see forum_user_handler::db_insert_login().
159  */
160  unsigned long long insert_login(const std::string& username, const std::string& ip, const std::string& version);
161 
162  /**
163  * @see forum_user_handler::db_update_logout().
164  */
165  void update_logout(unsigned long long login_id);
166 
167  /**
168  * @see forum_user_handler::get_users_for_ip().
169  */
170  void get_users_for_ip(const std::string& ip, std::ostringstream* out);
171 
172  /**
173  * @see forum_user_handler::get_ips_for_users().
174  */
175  void get_ips_for_user(const std::string& username, std::ostringstream* out);
176 
177  private:
178  /**
179  * The account used to connect to the database.
180  * Also contains the connection settings.
181  * @note settings put on the connection, rather than the account, are NOT kept if a reconnect occurs!
182  */
183  mariadb::account_ref account_;
184  /** The actual connection to the database. */
185  mariadb::connection_ref connection_;
186 
187  /** The name of the table that contains forum user information. */
188  std::string db_users_table_;
189  /** The name of the table that contains forum ban information. */
190  std::string db_banlist_table_;
191  /** The name of the table that contains additional user information. */
192  std::string db_extra_table_;
193  /** The name of the table that contains game-level information. */
194  std::string db_game_info_table_;
195  /** The name of the table that contains player-level information per game. */
197  /** The name of the table that contains game content information. */
199  /** The name of the table that contains forum group information. */
200  std::string db_user_group_table_;
201  /** The text of the SQL query to use to retrieve any currently active tournaments. */
202  std::string db_tournament_query_;
203  /** The name of the table that contains phpbb forum thread information */
204  std::string db_topics_table_;
205  /** The name of the table that contains add-on information. */
206  std::string db_addon_info_table_;
207  /** The name of the table that contains user connection history. */
209 
210  /**
211  * This is used to write out error text when an SQL-related exception occurs.
212  *
213  * @param text Some custom text to log.
214  * @param e The exception that occurred which has information about what went wrong.
215  */
216  void log_sql_exception(const std::string& text, const mariadb::exception::base& e);
217 
218  /**
219  * Creates a new connection object from the account.
220  */
221  mariadb::connection_ref create_connection();
222 
223  /**
224  * Queries can return data with various types that can't be easily fit into a pre-determined structure.
225  * Therefore for queries that can return multiple rows with multiple columns, a class that extends @ref rs_base handles reading the results.
226  *
227  * @param connection The database connecion that will be used to execute the query.
228  * @param base The class that will handle reading the results.
229  * @param sql The SQL text to be executed.
230  * @param args The parameterized values to be inserted into the query.
231  */
232  template<typename... Args>
233  void get_complex_results(mariadb::connection_ref connection, rs_base& base, const std::string& sql, Args&&... args);
234 
235  /**
236  * @param connection The database connecion that will be used to execute the query.
237  * @param sql The SQL text to be executed.
238  * @param args The parameterized values to be inserted into the query.
239  * @return The single string value queried.
240  * @throws mariadb::exception::base when the query finds no value to be retrieved.
241  */
242  template<typename... Args>
243  std::string get_single_string(mariadb::connection_ref connection, const std::string& sql, Args&&... args);
244 
245  /**
246  * @param connection The database connecion that will be used to execute the query.
247  * @param sql The SQL text to be executed.
248  * @param args The parameterized values to be inserted into the query.
249  * @return The single long value queried.
250  * @throws mariadb::exception::base when the query finds no value to be retrieved.
251  */
252  template<typename... Args>
253  long get_single_long(mariadb::connection_ref connection, const std::string& sql, Args&&... args);
254 
255  /**
256  * @param connection The database connecion that will be used to execute the query.
257  * @param sql The SQL text to be executed.
258  * @param args The parameterized values to be inserted into the query.
259  * @return True if any data was returned by the query, otherwise false.
260  */
261  template<typename... Args>
262  bool exists(mariadb::connection_ref connection, const std::string& sql, Args&&... args);
263 
264  /**
265  * Executes a select statement.
266  *
267  * @param connection The database connecion that will be used to execute the query.
268  * @param sql The SQL text to be executed.
269  * @param args The parameterized values to be inserted into the query.
270  * @return A result set containing the results of the select statement executed.
271  */
272  template<typename... Args>
273  mariadb::result_set_ref select(mariadb::connection_ref connection, const std::string& sql, Args&&... args);
274 
275  /**
276  * Executes non-select statements (ie: insert, update, delete).
277  *
278  * @param connection The database connecion that will be used to execute the query.
279  * @param sql The SQL text to be executed.
280  * @param args The parameterized values to be inserted into the query.
281  * @return The number of rows modified.
282  */
283  template<typename... Args>
284  unsigned long long modify(mariadb::connection_ref connection, const std::string& sql, Args&&... args);
285 
286  /**
287  * Begins recursively unpacking of the parameter pack in order to be able to call the correct parameterized setters on the query.
288  *
289  * @param connection The database connecion that will be used to execute the query.
290  * @param sql The SQL text to be executed.
291  * @param args The parameterized values to be inserted into the query.
292  * @return A statement object with all parameterized values added. This will then be executed by either @ref modify() or @ref select().
293  */
294  template<typename... Args>
295  mariadb::statement_ref query(mariadb::connection_ref connection, const std::string& sql, Args&&... args);
296 
297  /**
298  * The next parameter to be added is split off from the parameter pack.
299  *
300  * @param stmt The statement that will have parameterized values set on it.
301  * @param i The index of the current parameterized value.
302  * @param arg The next parameter to be added.
303  * @param args The remaining parameters to be added.
304  */
305  template<typename Arg, typename... Args>
306  void prepare(mariadb::statement_ref stmt, int i, Arg arg, Args&&... args);
307 
308  /**
309  * Specializations for each type of value to be parameterized.
310  * There are other parameter setters than those currently implemented, but so far there hasn't been a reason to add them.
311  *
312  * @param stmt The statement that will have parameterized values set on it.
313  * @param i The index of the current parameterized value.
314  * @param arg The next parameter to be added.
315  * @return The index of the next parameter to add.
316  */
317  template<typename Arg>
318  int prepare(mariadb::statement_ref stmt, int i, Arg arg);
319 
320  /**
321  * Nothing left to parameterize, so break out of the recursion.
322  */
323  void prepare(mariadb::statement_ref stmt, int i);
324 };
std::string db_tournament_query_
The text of the SQL query to use to retrieve any currently active tournaments.
Definition: dbconn.hpp:202
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.
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)
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 db_game_info_table_
The name of the table that contains game-level information.
Definition: dbconn.hpp:194
void update_logout(unsigned long long login_id)
bool topic_id_exists(int topic_id)
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)
std::string db_banlist_table_
The name of the table that contains forum ban information.
Definition: dbconn.hpp:190
std::string db_topics_table_
The name of the table that contains phpbb forum thread information.
Definition: dbconn.hpp:204
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)
Definitions for the interface to Wesnoth Markup Language (WML).
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.
This class is responsible for handling the database connections as well as executing queries and hand...
Definition: dbconn.hpp:37
std::string db_addon_info_table_
The name of the table that contains add-on information.
Definition: dbconn.hpp:206
dbconn(const config &c)
Initializes the synchronous query connection as well as the account object that has the connection se...
std::string db_connection_history_table_
The name of the table that contains user connection history.
Definition: dbconn.hpp:208
mariadb::connection_ref connection_
The actual connection to the database.
Definition: dbconn.hpp:185
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)
mariadb::account_ref account_
The account used to connect to the database.
Definition: dbconn.hpp:183
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)
std::string db_game_player_info_table_
The name of the table that contains player-level information per game.
Definition: dbconn.hpp:196
std::string db_game_content_info_table_
The name of the table that contains game content information.
Definition: dbconn.hpp:198
std::string get_uuid()
std::size_t i
Definition: function.cpp:967
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)
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 db_user_group_table_
The name of the table that contains forum group information.
Definition: dbconn.hpp:200
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.
std::string db_users_table_
The name of the table that contains forum user information.
Definition: dbconn.hpp:188
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)
A config object defines a single node in a WML file, with access to child nodes.
Definition: config.hpp:61
mock_char c
unsigned long long insert_login(const std::string &username, const std::string &ip, const std::string &version)
std::string db_extra_table_
The name of the table that contains additional user information.
Definition: dbconn.hpp:192
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)