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