The Battle for Wesnoth  1.17.12+dev
dbconn.hpp
Go to the documentation of this file.
1 /*
2  Copyright (C) 2020 - 2022
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  * @param name The player's username.
89  * @param group_id The forum group ID to check if the user is part of.
90  * @return Whether the user is a member of the forum group.
91  */
92  bool is_user_in_group(const std::string& name, int group_id);
93 
94  /**
95  * @param table The table that will be queried.
96  * @param column The column that will be selected.
97  * @param name The player's username.
98  * @return The string value in the provided table and column for the provided username.
99  */
100  std::string get_user_string(const std::string& table, const std::string& column, const std::string& name);
101 
102  /**
103  * @param table The table that will be queried.
104  * @param column The column that will be selected.
105  * @param name The player's username.
106  * @return The int value in the provided table and column for the provided username.
107  */
108  int get_user_int(const std::string& table, const std::string& column, const std::string& name);
109 
110  /**
111  * The provided value is updated if a row exists or a new row inserted otherwise.
112  *
113  * @param column The column that the value will be put into.
114  * @param name The player's username.
115  * @param value The value to be put into the column.
116  */
117  void write_user_int(const std::string& column, const std::string& name, int value);
118 
119  /**
120  * @see forum_user_handler::user_is_banned().
121  */
122  ban_check get_ban_info(const std::string& name, const std::string& ip);
123 
124  /**
125  * @see forum_user_handler::db_insert_game_info().
126  */
127  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);
128 
129  /**
130  * @see forum_user_handler::db_update_game_end().
131  */
132  void update_game_end(const std::string& uuid, int game_id, const std::string& replay_location);
133 
134  /**
135  * @see forum_user_handler::db_insert_game_player_info().
136  */
137  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);
138 
139  /**
140  * @see forum_user_handler::db_insert_game_content_info().
141  */
142  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);
143 
144  /**
145  * @see forum_user_handler::db_set_oos_flag().
146  */
147  void set_oos_flag(const std::string& uuid, int game_id);
148 
149  /**
150  * @see forum_user_handler::db_topic_id_exists().
151  */
152  bool topic_id_exists(int topic_id);
153 
154  /**
155  * @see forum_user_handler::db_insert_addon_info().
156  */
157  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);
158 
159  /**
160  * @see forum_user_handler::db_insert_login().
161  */
162  unsigned long long insert_login(const std::string& username, const std::string& ip, const std::string& version);
163 
164  /**
165  * @see forum_user_handler::db_update_logout().
166  */
167  void update_logout(unsigned long long login_id);
168 
169  /**
170  * @see forum_user_handler::get_users_for_ip().
171  */
172  void get_users_for_ip(const std::string& ip, std::ostringstream* out);
173 
174  /**
175  * @see forum_user_handler::get_ips_for_users().
176  */
177  void get_ips_for_user(const std::string& username, std::ostringstream* out);
178 
179  /**
180  * @see forum_user_handler::db_update_addon_download_count().
181  */
182  void update_addon_download_count(const std::string& instance_version, const std::string& id, const std::string& version);
183 
184  /**
185  * @see forum_user_handler::is_user_primary_author().
186  * @see forum_user_handler::is_user_secondary_author().
187  */
188  bool is_user_author(const std::string& instance_version, const std::string& id, const std::string& username, int is_primary);
189 
190  /**
191  * @see forum_user_handler::db_delete_addon_authors().
192  */
193  void delete_addon_authors(const std::string& instance_version, const std::string& id);
194 
195  /**
196  * @see forum_user_handler::db_insert_addon_authors().
197  */
198  void insert_addon_author(const std::string& instance_version, const std::string& id, const std::string author, int is_primary);
199 
200  /**
201  * @see forum_user_handler::do_any_authors_exist().
202  */
203  bool do_any_authors_exist(const std::string& instance_version, const std::string& id);
204 
205  private:
206  /**
207  * The account used to connect to the database.
208  * Also contains the connection settings.
209  * @note settings put on the connection, rather than the account, are NOT kept if a reconnect occurs!
210  */
211  mariadb::account_ref account_;
212  /** The actual connection to the database. */
213  mariadb::connection_ref connection_;
214 
215  /** The name of the table that contains forum user information. */
216  std::string db_users_table_;
217  /** The name of the table that contains forum ban information. */
218  std::string db_banlist_table_;
219  /** The name of the table that contains additional user information. */
220  std::string db_extra_table_;
221  /** The name of the table that contains game-level information. */
222  std::string db_game_info_table_;
223  /** The name of the table that contains player-level information per game. */
225  /** The name of the table that contains game content information. */
227  /** The name of the table that contains forum group information. */
228  std::string db_user_group_table_;
229  /** The text of the SQL query to use to retrieve any currently active tournaments. */
230  std::string db_tournament_query_;
231  /** The name of the table that contains phpbb forum thread information */
232  std::string db_topics_table_;
233  /** The name of the table that contains add-on information. */
234  std::string db_addon_info_table_;
235  /** The name of the table that contains user connection history. */
237  /** The name of the table that contains the add-on authors information */
239 
240  /**
241  * This is used to write out error text when an SQL-related exception occurs.
242  *
243  * @param text Some custom text to log.
244  * @param e The exception that occurred which has information about what went wrong.
245  */
246  void log_sql_exception(const std::string& text, const mariadb::exception::base& e);
247 
248  /**
249  * Creates a new connection object from the account.
250  */
251  mariadb::connection_ref create_connection();
252 
253  /**
254  * Queries can return data with various types that can't be easily fit into a pre-determined structure.
255  * Therefore for queries that can return multiple rows with multiple columns, a class that extends @ref rs_base handles reading the results.
256  *
257  * @param connection The database connecion that will be used to execute the query.
258  * @param base The class that will handle reading the results.
259  * @param sql The SQL text to be executed.
260  * @param args The parameterized values to be inserted into the query.
261  */
262  template<typename... Args>
263  void get_complex_results(mariadb::connection_ref connection, rs_base& base, const std::string& sql, Args&&... args);
264 
265  /**
266  * @param connection The database connecion that will be used to execute the query.
267  * @param sql The SQL text to be executed.
268  * @param args The parameterized values to be inserted into the query.
269  * @return The single string value queried.
270  * @throws mariadb::exception::base when the query finds no value to be retrieved.
271  */
272  template<typename... Args>
273  std::string get_single_string(mariadb::connection_ref connection, const std::string& sql, Args&&... args);
274 
275  /**
276  * @param connection The database connecion that will be used to execute the query.
277  * @param sql The SQL text to be executed.
278  * @param args The parameterized values to be inserted into the query.
279  * @return The single long value queried.
280  * @throws mariadb::exception::base when the query finds no value to be retrieved.
281  */
282  template<typename... Args>
283  long get_single_long(mariadb::connection_ref connection, const std::string& sql, Args&&... args);
284 
285  /**
286  * @param connection The database connecion that will be used to execute the query.
287  * @param sql The SQL text to be executed.
288  * @param args The parameterized values to be inserted into the query.
289  * @return True if any data was returned by the query, otherwise false.
290  */
291  template<typename... Args>
292  bool exists(mariadb::connection_ref connection, const std::string& sql, Args&&... args);
293 
294  /**
295  * Executes a select statement.
296  *
297  * @param connection The database connecion that will be used to execute the query.
298  * @param sql The SQL text to be executed.
299  * @param args The parameterized values to be inserted into the query.
300  * @return A result set containing the results of the select statement executed.
301  */
302  template<typename... Args>
303  mariadb::result_set_ref select(mariadb::connection_ref connection, const std::string& sql, Args&&... args);
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 args The parameterized values to be inserted into the query.
311  * @return The number of rows modified.
312  */
313  template<typename... Args>
314  unsigned long long modify(mariadb::connection_ref connection, const std::string& sql, Args&&... args);
315 
316  /**
317  * Executes non-select statements (ie: insert, update, delete), but primarily intended for inserts that return a generated ID.
318  *
319  * @param connection The database connecion that will be used to execute the query.
320  * @param sql The SQL text to be executed.
321  * @param args The parameterized values to be inserted into the query.
322  * @return The value of an AUTO_INCREMENT column on the table being modified.
323  */
324  template<typename... Args>
325  unsigned long long modify_get_id(mariadb::connection_ref connection, const std::string& sql, Args&&... args);
326 
327  /**
328  * Begins recursively unpacking of the parameter pack in order to be able to call the correct parameterized setters on the query.
329  *
330  * @param connection The database connecion that will be used to execute the query.
331  * @param sql The SQL text to be executed.
332  * @param args The parameterized values to be inserted into the query.
333  * @return A statement object with all parameterized values added. This will then be executed by either @ref modify() or @ref select().
334  */
335  template<typename... Args>
336  mariadb::statement_ref query(mariadb::connection_ref connection, const std::string& sql, Args&&... args);
337 
338  /**
339  * The next parameter to be added is split off from the parameter pack.
340  *
341  * @param stmt The statement that will have parameterized values set on it.
342  * @param i The index of the current parameterized value.
343  * @param arg The next parameter to be added.
344  * @param args The remaining parameters to be added.
345  */
346  template<typename Arg, typename... Args>
347  void prepare(mariadb::statement_ref stmt, int i, Arg arg, Args&&... args);
348 
349  /**
350  * Specializations for each type of value to be parameterized.
351  * There are other parameter setters than those currently implemented, but so far there hasn't been a reason to add them.
352  *
353  * @param stmt The statement that will have parameterized values set on it.
354  * @param i The index of the current parameterized value.
355  * @param arg The next parameter to be added.
356  * @return The index of the next parameter to add.
357  */
358  template<typename Arg>
359  int prepare(mariadb::statement_ref stmt, int i, Arg arg);
360 
361  /**
362  * Nothing left to parameterize, so break out of the recursion.
363  */
364  void prepare(mariadb::statement_ref stmt, int i);
365 };
std::string db_tournament_query_
The text of the SQL query to use to retrieve any currently active tournaments.
Definition: dbconn.hpp:230
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 db_game_info_table_
The name of the table that contains game-level information.
Definition: dbconn.hpp:222
void update_logout(unsigned long long login_id)
void insert_addon_author(const std::string &instance_version, const std::string &id, const std::string author, int is_primary)
bool topic_id_exists(int topic_id)
void delete_addon_authors(const std::string &instance_version, const std::string &id)
std::string get_tournaments()
unsigned long long modify_get_id(mariadb::connection_ref connection, const std::string &sql, Args &&... args)
Executes non-select statements (ie: insert, update, delete), but primarily intended for inserts that ...
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:218
std::string db_topics_table_
The name of the table that contains phpbb forum thread information.
Definition: dbconn.hpp:232
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:234
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:236
bool do_any_authors_exist(const std::string &instance_version, const std::string &id)
mariadb::connection_ref connection_
The actual connection to the database.
Definition: dbconn.hpp:213
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_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)
mariadb::account_ref account_
The account used to connect to the database.
Definition: dbconn.hpp:211
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)
void update_addon_download_count(const std::string &instance_version, const std::string &id, const std::string &version)
std::string db_game_player_info_table_
The name of the table that contains player-level information per game.
Definition: dbconn.hpp:224
std::string db_game_content_info_table_
The name of the table that contains game content information.
Definition: dbconn.hpp:226
std::string get_uuid()
std::size_t i
Definition: function.cpp:968
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)
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::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:228
std::string db_addon_authors_table_
The name of the table that contains the add-on authors information.
Definition: dbconn.hpp:238
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:216
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)
bool is_user_author(const std::string &instance_version, const std::string &id, const std::string &username, int is_primary)
A config object defines a single node in a WML file, with access to child nodes.
Definition: config.hpp:60
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:220
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)