The Battle for Wesnoth  1.19.13+dev
dbconn.hpp
Go to the documentation of this file.
1 /*
2  Copyright (C) 2020 - 2025
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"
19 
20 #include <mysql/mysql.h>
21 #include "mariadb++/account.hpp"
22 #include "mariadb++/connection.hpp"
23 #include "mariadb++/statement.hpp"
24 #include "mariadb++/result_set.hpp"
25 #include "mariadb++/exceptions.hpp"
26 
27 #include <vector>
28 #include <unordered_map>
29 
30 // make sure to update dbconn::query() as well when changing these listed values
31 typedef std::vector<std::variant<bool, int, long, unsigned long long, std::string, const char*>> sql_parameters;
32 
33 /**
34  * This class is responsible for handling the database connections as well as executing queries and handling any results.
35  * @note The account and connection should never ever be provided to anything outside of this class.
36  * @note !DO NOT APPEND VALUES DIRECTLY TO THE SQL TEXT IN ANY QUERY!
37  */
38 class dbconn
39 {
40 public:
41  /**
42  * Initializes the synchronous query connection as well as the account object that has the connection settings.
43  *
44  * @param c The config object to read information from.
45  */
46  dbconn(const config& c);
47 
48  /**
49  * @see forum_user_handler::async_test_query().
50  */
51  int async_test_query(int limit);
52 
53  /**
54  * @see forum_user_handler::get_uuid().
55  */
56  std::string get_uuid();
57 
58  /**
59  * @see forum_user_handler::get_tournaments().
60  */
61  std::string get_tournaments();
62 
63  /**
64  * This is an asynchronous query that is executed on a separate connection to retrieve the game history for the
65  * 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
73  * thrown.
74  */
75  std::unique_ptr<simple_wml::document> get_game_history(
76  int player_id, int offset, std::string search_game_name, int search_content_type, std::string search_content);
77 
78  /**
79  * @see forum_user_handler::user_exists().
80  */
81  bool user_exists(const std::string& name);
82 
83  /**
84  * @see forum_user_handler::get_forum_id().
85  */
86  long get_forum_id(const std::string& name);
87 
88  /**
89  * @param name The player's username.
90  * @return Whether the player has a row in the extra table.
91  */
92  bool extra_row_exists(const std::string& name);
93 
94  /**
95  * @param name The player's username.
96  * @param group_ids The forum group IDs to check if the user is part of.
97  * @return Whether the user is a member of the forum group.
98  */
99  bool is_user_in_groups(const std::string& name, const std::vector<int>& group_ids);
100 
101  /**
102  * @param table The table that will be queried.
103  * @param column The column that will be selected.
104  * @param name The player's username.
105  * @return The string value in the provided table and column for the provided username.
106  */
107  std::string get_user_string(const std::string& table, const std::string& column, const std::string& name);
108 
109  /**
110  * @param table The table that will be queried.
111  * @param column The column that will be selected.
112  * @param name The player's username.
113  * @return The int value in the provided table and column for the provided username.
114  */
115  int get_user_int(const std::string& table, const std::string& column, const std::string& name);
116 
117  /**
118  * The provided value is updated if a row exists or a new row inserted otherwise.
119  *
120  * @param column The column that the value will be put into.
121  * @param name The player's username.
122  * @param value The value to be put into the column.
123  */
124  void write_user_int(const std::string& column, const std::string& name, int value);
125 
126  /**
127  * @see forum_user_handler::user_is_banned().
128  */
129  config get_ban_info(const std::string& name, const std::string& ip);
130 
131  /**
132  * @see forum_user_handler::db_insert_game_info().
133  */
135  const std::string& uuid,
136  int game_id,
137  const std::string& version,
138  const std::string& name,
139  int reload,
140  int observers,
141  int is_public,
142  int has_password);
143 
144  /**
145  * @see forum_user_handler::db_update_game_end().
146  */
147  void update_game_end(const std::string& uuid, int game_id, const std::string& replay_location);
148 
149  /**
150  * @see forum_user_handler::db_insert_game_player_info().
151  */
153  const std::string& uuid,
154  int game_id,
155  const std::string& username,
156  int side_number,
157  int is_host,
158  const std::string& faction,
159  const std::string& version,
160  const std::string& source,
161  const std::string& current_user,
162  const std::string& leaders);
163 
164  /**
165  * @see forum_user_handler::db_insert_game_content_info().
166  */
167  unsigned long long insert_game_content_info(
168  const std::string& uuid,
169  int game_id,
170  const std::string& type,
171  const std::string& name,
172  const std::string& id,
173  const std::string& addon_id,
174  const std::string& addon_version);
175 
176  /**
177  * @see forum_user_handler::db_set_oos_flag().
178  */
179  void set_oos_flag(const std::string& uuid, int game_id);
180 
181  /**
182  * @see forum_user_handler::db_topic_id_exists().
183  */
184  bool topic_id_exists(int topic_id);
185 
186  /**
187  * @see forum_user_handler::db_insert_addon_info().
188  */
190  const std::string& instance_version,
191  const std::string& id,
192  const std::string& name,
193  const std::string& type,
194  const std::string& version,
195  bool forum_auth,
196  int topic_id,
197  const std::string& uploader);
198 
199  /**
200  * @see forum_user_handler::db_insert_login().
201  */
202  unsigned long long insert_login(const std::string& username, const std::string& ip, const std::string& version);
203 
204  /**
205  * @see forum_user_handler::db_update_logout().
206  */
207  void update_logout(unsigned long long login_id);
208 
209  /**
210  * @see forum_user_handler::get_users_for_ip().
211  */
212  void get_users_for_ip(const std::string& ip, std::ostringstream* out);
213 
214  /**
215  * @see forum_user_handler::get_ips_for_users().
216  */
217  void get_ips_for_user(const std::string& username, std::ostringstream* out);
218 
219  /**
220  * @see forum_user_handler::db_update_addon_download_count().
221  */
223  const std::string& instance_version, const std::string& id, const std::string& version);
224 
225  /**
226  * @see forum_user_handler::is_user_primary_author().
227  * @see forum_user_handler::is_user_secondary_author().
228  */
230  const std::string& instance_version, const std::string& id, const std::string& username, int is_primary);
231 
232  /**
233  * @see forum_user_handler::db_delete_addon_authors().
234  */
235  void delete_addon_authors(const std::string& instance_version, const std::string& id);
236 
237  /**
238  * @see forum_user_handler::db_insert_addon_authors().
239  */
241  const std::string& instance_version, const std::string& id, const std::string& author, int is_primary);
242 
243  /**
244  * @see forum_user_handler::do_any_authors_exist().
245  */
246  bool do_any_authors_exist(const std::string& instance_version, const std::string& id);
247 
248  /**
249  * @see forum_user_handler::get_addon_downloads_info().
250  */
251  config get_addon_downloads_info(const std::string& instance_version, const std::string& id);
252 
253  /**
254  * @see forum_user_handler::get_forum_auth_usage().
255  */
256  config get_forum_auth_usage(const std::string& instance_version);
257 
258  /**
259  * @see forum_user_handler::get_addon_admins().
260  */
261  config get_addon_admins(int site_admin_group, int forum_admin_group);
262 
263 private:
264  /**
265  * The account used to connect to the database.
266  * Also contains the connection settings.
267  * @note settings put on the connection, rather than the account, are NOT kept if a reconnect occurs!
268  */
269  mariadb::account_ref account_;
270  /** The actual connection to the database. */
271  mariadb::connection_ref connection_;
272 
273  /** The name of the table that contains forum user information. */
274  std::string db_users_table_;
275  /** The name of the table that contains forum ban information. */
276  std::string db_banlist_table_;
277  /** The name of the table that contains additional user information. */
278  std::string db_extra_table_;
279  /** The name of the table that contains game-level information. */
280  std::string db_game_info_table_;
281  /** The name of the table that contains player-level information per game. */
283  /** The name of the table that contains game content information. */
285  /** The name of the table that contains forum group information. */
286  std::string db_user_group_table_;
287  /** The text of the SQL query to use to retrieve any currently active tournaments. */
288  std::string db_tournament_query_;
289  /** The name of the table that contains phpbb forum thread information */
290  std::string db_topics_table_;
291  /** The name of the table that contains add-on information. */
292  std::string db_addon_info_table_;
293  /** The name of the table that contains user connection history. */
295  /** The name of the table that contains the add-on authors information */
297 
298  /**
299  * This is used to write out error text when an SQL-related exception occurs.
300  *
301  * @param text Some custom text to log.
302  * @param e The exception that occurred which has information about what went wrong.
303  */
304  void log_sql_exception(const std::string& text, const mariadb::exception::base& e);
305 
306  /**
307  * Creates a new connection object from the account.
308  */
309  mariadb::connection_ref create_connection();
310 
311  /**
312  * @param connection The database connection that will be used to execute the query.
313  * @param handler The lambda that will handle reading the results into a config.
314  * @param sql The SQL text to be executed.
315  * @param params The parameterized values to be inserted into the query.
316  */
317  template <typename F>
319  const mariadb::connection_ref& connection, F* handler, const std::string& sql, const sql_parameters& params);
320 
321  /**
322  * @param connection The database connection that will be used to execute the query.
323  * @param sql The SQL text to be executed.
324  * @param params The parameterized values to be inserted into the query.
325  * @return The single string value queried.
326  * @throws mariadb::exception::base when the query finds no value to be retrieved.
327  */
328  std::string get_single_string(
329  const mariadb::connection_ref& connection, const std::string& sql, const sql_parameters& params);
330 
331  /**
332  * @param connection The database connection that will be used to execute the query.
333  * @param sql The SQL text to be executed.
334  * @param params The parameterized values to be inserted into the query.
335  * @return The single long value queried.
336  * @throws mariadb::exception::base when the query finds no value to be retrieved.
337  */
338  long get_single_long(const mariadb::connection_ref& connection, const std::string& sql, const sql_parameters& params);
339 
340  /**
341  * @param connection The database connection that will be used to execute the query.
342  * @param sql The SQL text to be executed.
343  * @param params The parameterized values to be inserted into the query.
344  * @return True if any data was returned by the query, otherwise false.
345  */
346  bool exists(const mariadb::connection_ref& connection, const std::string& sql, const sql_parameters& params);
347 
348  /**
349  * Executes a select statement.
350  *
351  * @param connection The database connection that will be used to execute the query.
352  * @param sql The SQL text to be executed.
353  * @param params The parameterized values to be inserted into the query.
354  * @return A result set containing the results of the select statement executed.
355  */
356  mariadb::result_set_ref select(
357  const mariadb::connection_ref& connection, const std::string& sql, const sql_parameters& params);
358 
359  /**
360  * Executes non-select statements (ie: insert, update, delete).
361  *
362  * @param connection The database connection that will be used to execute the query.
363  * @param sql The SQL text to be executed.
364  * @param params The parameterized values to be inserted into the query.
365  * @return The number of rows modified.
366  */
367  unsigned long long modify(const mariadb::connection_ref& connection, const std::string& sql, const sql_parameters& params);
368 
369  /**
370  * Executes non-select statements (ie: insert, update, delete), but primarily intended for inserts that return a
371  * generated ID.
372  *
373  * @param connection The database connection that will be used to execute the query.
374  * @param sql The SQL text to be executed.
375  * @param params The parameterized values to be inserted into the query.
376  * @return The value of an AUTO_INCREMENT column on the table being modified.
377  */
378  unsigned long long modify_get_id(
379  const mariadb::connection_ref& connection, const std::string& sql, const sql_parameters& params);
380 
381  /**
382  * For a given connection, set the provided SQL and parameters on a statement.
383  *
384  * @param connection The database connection that will be used to execute the query.
385  * @param sql The SQL text to be executed.
386  * @param params The parameterized values to be inserted into the query.
387  * @return A statement ready to be executed.
388  */
389  mariadb::statement_ref query(
390  const mariadb::connection_ref& connection, const std::string& sql, const sql_parameters& params);
391 };
A config object defines a single node in a WML file, with access to child nodes.
Definition: config.hpp:158
This class is responsible for handling the database connections as well as executing queries and hand...
Definition: dbconn.hpp:39
void delete_addon_authors(const std::string &instance_version, const std::string &id)
void insert_addon_author(const std::string &instance_version, const std::string &id, const std::string &author, int is_primary)
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)
config get_addon_downloads_info(const std::string &instance_version, const std::string &id)
std::string db_tournament_query_
The text of the SQL query to use to retrieve any currently active tournaments.
Definition: dbconn.hpp:288
bool topic_id_exists(int topic_id)
bool is_user_in_groups(const std::string &name, const std::vector< int > &group_ids)
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:271
bool exists(const mariadb::connection_ref &connection, const std::string &sql, const sql_parameters &params)
long get_forum_id(const std::string &name)
config get_addon_admins(int site_admin_group, int forum_admin_group)
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 ...
void get_ips_for_user(const std::string &username, std::ostringstream *out)
std::string db_addon_authors_table_
The name of the table that contains the add-on authors information.
Definition: dbconn.hpp:296
bool user_exists(const std::string &name)
void get_users_for_ip(const std::string &ip, std::ostringstream *out)
long get_single_long(const mariadb::connection_ref &connection, const std::string &sql, const sql_parameters &params)
std::string get_single_string(const mariadb::connection_ref &connection, const std::string &sql, const sql_parameters &params)
std::string db_banlist_table_
The name of the table that contains forum ban information.
Definition: dbconn.hpp:276
config get_complex_results(const mariadb::connection_ref &connection, F *handler, const std::string &sql, const sql_parameters &params)
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:278
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:292
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.
std::string db_game_content_info_table_
The name of the table that contains game content information.
Definition: dbconn.hpp:284
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:286
mariadb::account_ref account_
The account used to connect to the database.
Definition: dbconn.hpp:269
config get_ban_info(const std::string &name, const std::string &ip)
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:280
std::string db_game_player_info_table_
The name of the table that contains player-level information per game.
Definition: dbconn.hpp:282
mariadb::statement_ref query(const 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_connection_history_table_
The name of the table that contains user connection history.
Definition: dbconn.hpp:294
int get_user_int(const std::string &table, const std::string &column, const std::string &name)
unsigned long long modify(const mariadb::connection_ref &connection, const std::string &sql, const sql_parameters &params)
Executes non-select statements (ie: insert, update, delete).
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)
unsigned long long modify_get_id(const 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_topics_table_
The name of the table that contains phpbb forum thread information.
Definition: dbconn.hpp:290
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:274
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)
config get_forum_auth_usage(const std::string &instance_version)
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)
mariadb::result_set_ref select(const mariadb::connection_ref &connection, const std::string &sql, const sql_parameters &params)
Executes a select statement.
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)
Definitions for the interface to Wesnoth Markup Language (WML).
std::vector< std::variant< bool, int, long, unsigned long long, std::string, const char * > > sql_parameters
Definition: dbconn.hpp:31
int side_number
Definition: game_info.hpp:40
mock_char c
#define e