SQL Query Extension/Portability Helpers

Enumerations
enum	M_sql_query_updlock_type_t {   M_SQL_QUERY_UPDLOCK_TABLE = 1 ,   M_SQL_QUERY_UPDLOCK_QUERYEND = 2 }
enum	M_sql_query_bitop_t {   M_SQL_BITOP_AND = 1 ,   M_SQL_BITOP_OR = 2 }

Functions
void	M_sql_query_append_updlock (M_sql_connpool_t *pool, M_buf_t *query, M_sql_query_updlock_type_t type, const char *table_name)
M_bool	M_sql_query_append_bitop (M_sql_connpool_t *pool, M_buf_t *query, M_sql_query_bitop_t op, const char *exp1, const char *exp2)
void	M_sql_query_append_datatype (M_sql_connpool_t *pool, M_buf_t *query, M_sql_data_type_t type, size_t max_len, M_bool is_cast)

Detailed Description

SQL Query Extension/Portability Helpers are used to assist in ensuring queries are portable across various database servers utilizing extensions they offer.

Enumeration Type Documentation

M_sql_query_updlock_type_t

enum M_sql_query_updlock_type_t

Row lock type to append to query to lock rows returned from a select statement for a future update with in a transaction. All values must be used within a single query

Enumerator
M_SQL_QUERY_UPDLOCK_TABLE	Apply SQL-specific lock to rows in the table being updated. This must be appended immediately after every referenced table name when row locking is desired. Must be used in conjunction with a later call for M_SQL_QUERY_UPDLOCK_QUERYEND
M_SQL_QUERY_UPDLOCK_QUERYEND	Apply the SQL-specific lock to the rows referenced by query, this must always be applied at the END of a query string. Must be used in conjunction with an earlier call for M_SQL_QUERY_UPDLOCK_TABLE

M_sql_query_bitop_t

enum M_sql_query_bitop_t

Type of bitwise operation to perform.

Enumerator
M_SQL_BITOP_AND	Perform a bitwise AND (&) operation
M_SQL_BITOP_OR	Perform a bitwise OR (|) operation

Function Documentation

M_sql_query_append_updlock()

void M_sql_query_append_updlock	(	M_sql_connpool_t *	pool,
		M_buf_t *	query,
		M_sql_query_updlock_type_t	type,
		const char *	table_name
	)

Append the SQL-server-specific row lock method to the proper point in the query to be updated by a later call within the same transaction.

Row locks are intended to block conflicting select statements until the current transaction has completed. It is an optimization to assist in reducing deadlocks which force rollback and retry cycles. For some database clustering solutions, like MySQL/MariaDB with Galera, it is necessary to use to prevent lost updates since updates cross-node lack the serializable isolation level guarantees.

Different databases utilize different row locking methods and the methods appear at different points in the query. Due to the complexity of SQL queries, it is not viable to offer automatic rewrite ability for such queries, and instead we provide methods for simply inserting the locking statements in a DB-specific way into your query.

Locking is for the duration of an SQL transaction, so row locking can only occur within a transaction, please see M_sql_trans_begin().

An example query that you want to lock the rows might look like:

SELECT * FROM "foo" WHERE "bar" = ? 

For a row lock for Microsoft SQL Server, the desired query with locks would look like:

SELECT * FROM "foo" WITH (ROWLOCK, XLOCK, HOLDLOCK) WHERE "bar" = ? 

For the equivalent on MySQL, it would look like this:

SELECT * FROM "foo" WHERE "bar" = ? FOR UPDATE 

For PostgreSQL it may either be like MySQL, or on instances where an outer left join is used a table name must also be specified, e.g.

SELECT * FROM "foo" WHERE "bar" = ? FOR UPDATE OF "foo" 

Clearly as the above example indicates, it would be undesirable to need to rewrite the query manually by detecting the database in use, using helpers makes this easier so you do not need to have SQL-server-specific logic in your own code. Converting that query above using the helpers could be done as the below:

M_sql_stmt_t *stmt  = M_sql_stmt_create();
M_buf_t      *query = M_buf_create();
M_sql_error_t err;
 
M_buf_add_str(query, "SELECT * FROM \"foo\"");
M_sql_query_append_updlock(pool, query, M_SQL_QUERY_UPDLOCK_TABLE, NULL);
M_buf_add_str(query, " WHERE \"bar\" = ?");
M_sql_stmt_bind_int32(stmt, 1);
M_sql_query_append_updlock(pool, query, M_SQL_QUERY_UPDLOCK_QUERYEND, NULL);
M_sql_stmt_prepare_buf(stmt, query);
err = M_sql_stmt_execute(pool, stmt);
//...
M_sql_stmt_destroy(stmt);

Note

At least one M_SQL_QUERY_UPDLOCK_TABLE must be appended per query. They will be appended immediately after each table reference (SELECT FROM ... table, or JOIN table). For the same query, at the end of the query, M_SQL_QUERY_UPDLOCK_QUERYEND must be appended.

Warning

Not all databases support row-lock hints and instead rely on consistency guarantees by the underlying database for the isolation method in use. If you need these guarantees, please ensure you are using the M_SQL_ISOLATION_SERIALIZABLE isolation method as well.

Parameters

[in]	pool	Initialized M_sql_connpool_t object
[in,out]	query	A pointer to an already populated M_buf_t with a partial (or complete for M_SQL_QUERY_UPDLOCK_QUERYEND) request.
[in]	type	Type of sql-specific lock to append to the query.
[in]	table_name	Optional. For databases that support "FOR UPDATE OF" this will specify the explicit table name to use. If NULL, then will not emit the "OF" clause. This may be necessary for left outer joins on PostgreSQL. Multiple tables may be referenced in a comma-delimited manner, if table aliases are used the alias must be referenced not the actual table name. Table names are NOT automatically quoted.

M_sql_query_append_bitop()

M_bool M_sql_query_append_bitop	(	M_sql_connpool_t *	pool,
		M_buf_t *	query,
		M_sql_query_bitop_t	op,
		const char *	exp1,
		const char *	exp2
	)

Perform a bitwise operation on the database using an SQL-server-specific format.

A classic bitwise operation checking to see if a bit is set may look like the below:

(exp1 & exp2) != 0 

Or a bitwise operation, setting a bit or set of bits may look like:

exp1 = exp1 | exp2 

Some database servers take the expressions listed above exactly, however, others may require functions like BITAND() and BITOR() to accomplish the same thing.

Taking an example of selecting all rows where "bar" has bit 4 (0x8) set:

SELECT * FROM "foo" WHERE ("bar" & 8) <> 0 

Might look like this:

M_buf_t      *buf  = M_buf_create();
M_sql_stmt_t *stmt = M_sql_stmt_create();
M_sql_error_t err;
 
M_buf_add_str(buf, "SELECT * FROM \"foo\" WHERE (");
M_sql_query_append_bitop(pool, buf, M_SQL_BITOP_AND, "\"bar\"", "?");
M_sql_stmt_bind_int32(stmt, 8);
M_buf_add_str(buf, ") <> 0");
M_sql_stmt_prepare_buf(stmt, buf);
err = M_sql_stmt_execute(pool, stmt);
//...
M_sql_stmt_destroy(stmt);

Of course, more complex things are possible as well, such as unsetting bits and setting others in a single request by embedding operations within eachother. Take the below example that keeps bits 2 (0x2) and 3 (0x4) while clearing the rest and also sets bit 4 (0x8):

UPDATE "foo" SET "bar" = ( "bar" & 6 ) | 8; 

Might look like this:

M_buf_t      *buf  = M_buf_create();
M_sql_stmt_t *stmt = M_sql_stmt_create();
M_sql_error_t err;
 
M_buf_add_str(buf, "UPDATE \"foo\" SET \"bar\" = ");
 
  // Do inner-first ( "bar" & 6 )
  M_buf_t *inner = M_buf_create();
  M_buf_add_str(inner, "( ");
  M_sql_query_append_bitop(pool, inner, M_SQL_BITOP_AND, "\"bar\"", "?");
  M_sql_stmt_bind_int32(stmt, 6);
  M_buf_add_str(inner, " )");
 
// Do outer, embedding inner
M_sql_query_append_bitop(pool, buf, M_SQL_BITOP_OR, M_buf_peek(inner), "?");
M_sql_stmt_bind_int32(stmt, 8);
M_buf_cancel(inner);  // We peeked, throw it away
 
M_sql_stmt_prepare_buf(stmt, buf);
err = M_sql_stmt_execute(pool, stmt);
//...
M_sql_stmt_destroy(stmt);

Warning

Most databases do not allow bitwise operations to be used for 'truth' values (e.g as a boolean). Instead, an integrator should compare the result to 0 to turn it into a boolean operation if needed.

Parameters

[in]	pool	Initialized M_sql_connpool_t object
[in,out]	query	A pointer to an already populated M_buf_t with a partial request.
[in]	op	Bitwise operation to perform.
[in]	exp1	Left-hand side of SQL expression.
[in]	exp2	Right-hande size of SQL expression.

Returns

M_TRUE on success, M_FALSE on misuse

M_sql_query_append_datatype()

void M_sql_query_append_datatype	(	M_sql_connpool_t *	pool,
		M_buf_t *	query,
		M_sql_data_type_t	type,
		size_t	max_len,
		M_bool	is_cast
	)

It may be necessary to know the data type name mapping for an mstdlib datatype. This function can be used to retrieve that value. Data types may differ depending on an operation. For instance, MySQL can use VARCHAR for column creation but only CHAR for CAST operations

Parameters

[in]	pool	Initialized M_sql_connpool_t object
[in,out]	query	A pointer to an already populated M_buf_t with a partial request.
[in]	type	mstdlib datatype
[in]	max_len	Maximum length of data type if a TEXT or BINARY type.
[in]	is_cast	If this datatype is used during a CAST operation.