db Namespace Reference

Database abstraction layer Abstracts mysql C connector, supporting prepared statements and caching. More...

Classes
struct	resultset
	Definition of a result set. Supports iteration and accessing its rows via operator[] and at(). You can also insert new rows with emplace_back and push_back(). More...

Typedefs
using	row = std::map< std::string, std::string >
	Definition of a row in a result set. More...
using	sql_query_callback = std::function< void(const resultset &)>
	A callback which happens when an asynchronous SQL query is completed. More...
using	parameter_type = std::variant< float, std::string, uint64_t, int64_t, bool, int32_t, uint32_t, double >
	Possible parameter types for SQL parameters. More...
using	paramlist = std::vector< parameter_type >
	A list of database query parameters. These will be translated into prepared statement arguments. More...

Functions
void	init (dpp::cluster &bot)
	Initialise database connection. More...
bool	connect (const std::string &host, const std::string &user, const std::string &pass, const std::string &db, int port=3306, const std::string &socket="")
	Connect to database and set options. More...
bool	close ()
	Disconnect from database and free query cache. More...
resultset	query (const std::string &format, const paramlist &parameters={})
	Run a mysql query, with automatic escaping of parameters to prevent SQL injection. More...
void	query_callback (const std::string &format, const paramlist &parameters, const sql_query_callback &cb)
	Run a mysql query asynchronously, with automatic escaping of parameters to prevent SQL injection. Call the callback on completion. More...
dpp::async< resultset >	co_query (const std::string &format, const paramlist &parameters={})
	Run a mysql query, with automatic escaping of parameters to prevent SQL injection. This is the coroutine asynchronous version of db::query. It will complete its dpp::async once the query has finished without blocking, by means of a separate thread. More...
resultset	query (const std::string &format, const paramlist &parameters, double lifetime)
	Run a mysql query, with automatic escaping of parameters to prevent SQL injection. More...
size_t	affected_rows ()
	Returns number of affected rows from an UPDATE, INSERT, DELETE. More...
const std::string &	error ()
	Returns the last error string. More...
size_t	cache_size ()
	Returns the size of the query cache. More...
size_t	query_count ()
	Returns total number of queries executed since connection was established. More...
void	transaction (std::function< bool()> closure, sql_query_callback callback={})
	Start an SQL transaction. SQL transactions are atomic in nature, ALL other queries will be forced to wait. The transaction will be inserted into the queue to run as one atomic operation, meaning that db::co_query cannot disrupt it or insert queries in the middle of it, and db::query function calls that are not within the closure will be forced to wait. More...
dpp::async< resultset >	co_transaction (std::function< bool()> closure)
	Start an SQL transaction in a coroutine that can be awaited. SQL transactions are atomic in nature, ALL other queries will be forced to wait. The transaction will be inserted into the queue to run as one atomic operation, meaning that db::co_query cannot disrupt it or insert queries in the middle of it, and db::query function calls that are not within the closure will be forced to wait. More...

Detailed Description

Database abstraction layer Abstracts mysql C connector, supporting prepared statements and caching.

Typedef Documentation

parameter_type

using db::parameter_type = typedef std::variant<float, std::string, uint64_t, int64_t, bool, int32_t, uint32_t, double>

Possible parameter types for SQL parameters.

paramlist

using db::paramlist = typedef std::vector<parameter_type>

A list of database query parameters. These will be translated into prepared statement arguments.

row

using db::row = typedef std::map<std::string, std::string>

Definition of a row in a result set.

sql_query_callback

using db::sql_query_callback = typedef std::function<void(const resultset&)>

A callback which happens when an asynchronous SQL query is completed.

Function Documentation

affected_rows()

size_t db::affected_rows

(

)

Returns number of affected rows from an UPDATE, INSERT, DELETE.

Note

This value is by any db::query() call. Take a copy!

Returns

size_t Number of affected rows

Deprecated:

This is not coroutine-safe and you should use resultset::affected_rows instead

cache_size()

size_t db::cache_size

(

)

Returns the size of the query cache.

Prepared statement handles are stored in a std::map along with their metadata, so that they don't have to be re-prepared if they are executed repeatedly. This is a diagnostic and informational function which returns the size of that map.

Returns

size_t Cache size

close()

bool db::close

(

)

Disconnect from database and free query cache.

Returns

true on successful disconnection

co_query()

dpp::async<resultset> db::co_query	(	const std::string &	format,
		const paramlist &	parameters = {}
	)

Run a mysql query, with automatic escaping of parameters to prevent SQL injection. This is the coroutine asynchronous version of db::query. It will complete its dpp::async once the query has finished without blocking, by means of a separate thread.

Parameters

format	Format string, where each parameter should be indicated by a ? symbol
parameters	Parameters to prepare into the query in place of the ?'s

Returns

dpp::async which you can co_await to get the result set.

Note

It is the nature of asynchronous APIs like this that they cannot be atomic. Therefore do not combine transactions with asynchronous co_query. If you do, other queries may end up muddled into your transaction. This wrapper ensures the correct order of statements passed to co_query from the same origin thread, however statements from other threads, or statements via db::query() instead are not queued. Pick one interface, or the other!

The parameters given should be a vector of strings. You can instantiate this using "{}". The queries are cached as prepared statements and therefore do not need quote symbols to be placed around parameters in the query. These will be automatically added if required.

For example:

auto rs = co_await db::co_query("SELECT * FROM bigtable WHERE bar = ?", { "baz" });

co_transaction()

dpp::async<resultset> db::co_transaction

(

std::function< bool()>

closure

)

Start an SQL transaction in a coroutine that can be awaited. SQL transactions are atomic in nature, ALL other queries will be forced to wait. The transaction will be inserted into the queue to run as one atomic operation, meaning that db::co_query cannot disrupt it or insert queries in the middle of it, and db::query function calls that are not within the closure will be forced to wait.

Parameters

closure

The transactional code to execute.

Returns

Awaitable, returns an empty resultset on completion of transaction

Note

The closure should only ever execute queries using db::query(), it should NOT use async queries/co_query() as these cannot be executed atomically. Returning false from the closure, or throwing any exception at all will roll back the transaction, else it will be committed when the closure ends.

connect()

bool db::connect	(	const std::string &	host,
		const std::string &	user,
		const std::string &	pass,
		const std::string &	db,
		int	port = 3306,
		const std::string &	socket = ""
	)

Connect to database and set options.

Parameters

host	Database hostname
user	Database username
pass	Database password
db	Database schema name
port	Database port number
socket	unix socket path

Returns

True if the database connection succeeded

Note

Unix socket and port number are mutually exclusive. If you set socket to a non-empty string, you should set port to 0 and host to localhost. This is a special value in the mysql client and causes a unix socket connection to occur. If you do not want to use unix sockets, keep the value as an empty string.

error()

const std::string& db::error

(

)

Returns the last error string.

Note

This value is by any db::query() call. Take a copy!

Returns

const std::string& Error mesage

Deprecated:

This is not coroutine-safe and you should use resultset::error instead

init()

void db::init

(

dpp::cluster &

bot

)

Initialise database connection.

Parameters

bot	creating D++ cluster

query() [1/2]

resultset db::query	(	const std::string &	format,
		const paramlist &	parameters,
		double	lifetime
	)

Run a mysql query, with automatic escaping of parameters to prevent SQL injection.

Parameters

format	Format string, where each parameter should be indicated by a ? symbol
parameters	Parameters to prepare into the query in place of the ?'s
lifetime	How long to cache this query's resultset in memory for

Returns

result set

Note

If the query is already cached in memory, the cached resultset will be returned instead of querying the database.

The parameters given should be a vector of strings. You can instantiate this using "{}". The queries are cached as prepared statements and therefore do not need quote symbols to be placed around parameters in the query. These will be automatically added if required.

query() [2/2]

resultset db::query	(	const std::string &	format,
		const paramlist &	parameters = {}
	)

Run a mysql query, with automatic escaping of parameters to prevent SQL injection.

Parameters

format	Format string, where each parameter should be indicated by a ? symbol
parameters	Parameters to prepare into the query in place of the ?'s

Returns

result set

The parameters given should be a vector of strings. You can instantiate this using "{}". The queries are cached as prepared statements and therefore do not need quote symbols to be placed around parameters in the query. These will be automatically added if required.

For example:

db::query("UPDATE foo SET bar = ? WHERE id = ?", { "baz", 3 });

query_callback()

void db::query_callback	(	const std::string &	format,
		const paramlist &	parameters,
		const sql_query_callback &	cb
	)

Run a mysql query asynchronously, with automatic escaping of parameters to prevent SQL injection. Call the callback on completion.

Parameters

format	Format string, where each parameter should be indicated by a ? symbol
parameters	Parameters to prepare into the query in place of the ?'s
cb	Callback to call on completion of the query. The callback will be passed the resultset as its parameter.

The parameters given should be a vector of strings. You can instantiate this using "{}". The queries are cached as prepared statements and therefore do not need quote symbols to be placed around parameters in the query. These will be automatically added if required.

Note

If you can you should use co_query instead to avoid callback hell. co_query uses this internally, wrapping it with dpp::async<>.

query_count()

size_t db::query_count

(

)

Returns total number of queries executed since connection was established.

Returns

size_t Query counter

transaction()

void db::transaction	(	std::function< bool()>	closure,
		sql_query_callback	callback = {}
	)

Start an SQL transaction. SQL transactions are atomic in nature, ALL other queries will be forced to wait. The transaction will be inserted into the queue to run as one atomic operation, meaning that db::co_query cannot disrupt it or insert queries in the middle of it, and db::query function calls that are not within the closure will be forced to wait.

Parameters

closure	The transactional code to execute.
callback	Callback to call when the transaction completes

Note

The closure should only ever execute queries using db::query(), it should NOT use async queries/co_query() as these cannot be executed atomically. Returning false from the closure, or throwing any exception at all will roll back the transaction, else it will be committed when the closure ends.

Warning

The coroutine will execute asynchronously in a different thread. You should use the callback to be notified when it completes. The resultset passed as the parameter will be empty.