X2 Framework for Node.js Interface: DBDriver

Interface for database drivers.

Methods

booleanLiteral(val)

Get Boolean SQL literal.

Parameters:

Name	Type	Description
`val`	*	The ES value.

Returns:

String representing Boolean true or false in SQL.

Type: string

booleanToNull(expr)

Make SQL expression that evaluates the specified SQL expression and resolves into SQL TRUE or SQL NULL if the result is true or false respectively.

Parameters:

Name	Type	Description
`expr`	string	The Boolean SQL expression to evaluate.

Returns:

SQL expression that evaluates to TRUE or NULL.

Type: string

buildDeleteWithJoins(fromTableName, fromTableAlias [, refTables] [, filterExpr] [, filterExprParen])

Construct a SQL DELETE statement that can have a WHERE clause that uses columns from other joined tables. Note, that the statement still deletes rows only from a single table.

Parameters:

Name Type Argument Description

fromTableName string Name of the table, from which to delete.

fromTableAlias string Alias for the fromTableName used in the join conditions and the WHERE clause.

refTables

Array.<Object>

Array of descriptors of reference tables joined to the fromTableName table and used in the WHERE clause.

Properties

Name	Type	Description
`tableName`	string	Reference table name.
`tableAlias`	string	Reference table alias used in the join condition and the `WHERE` clause.
`joinCondition`	string	Boolean SQL expression for the reference table join condition.

filterExpr string <optional> Boolean SQL expression for the WHERE clause. The expression may use fromTableAlias and aliases of the reference tables to refer to the corresponding tables.

filterExprParen boolean <optional> true indicates that if the filterExpr is included in a logical conjunction (the "AND" Boolean operation), it needs to be enclosed in parenthesis.

Returns:

The resulting DELETE statement.

Type: string

buildLockTables( [exclusiveLockTables] [, sharedLockTables])

Construct statement for placing transaction-scoped full table locks. Not all databases support it (e.g. MySQL supports explicit table locks but they are not transaction-scoped but session-scoped).

Parameters:

Name	Type	Argument	Description
`exclusiveLockTables`	Array.<string>	<optional>	List of tables to lock in exclusive mode.
`sharedLockTables`	Array.<string>	<optional>	List of tables to lock in shared mode.

Throws:

If the database does not support it.
Type external:Error

Returns:

Statement to run to place the requested locks.

Type: string

buildUpdateWithJoins(updateTableName, updateTableAlias, sets [, refTables] [, filterExpr] [, filterExprParen])

Construct a SQL UPDATE statement that can have a WHERE clause that uses columns from other joined tables. Note, that the statement still updates rows of only a single table.

Parameters:

Name Type Argument Description

updateTableName string Name of the table to update.

updateTableAlias string Alias for the updateTableName used in the join conditions and the WHERE clause.

sets

Array.<Object>

The "set" statements, one for each column.

Properties

Name	Type	Description
`columnName`	string	Name of a column in the `updateTableName` table.
`value`	string	SQL value expression to set in the column.

refTables

Array.<Object>

Array of descriptors of reference tables joined to the updateTableName table and used in the WHERE clause.

Properties

Name	Type	Description
`tableName`	string	Reference table name.
`tableAlias`	string	Reference table alias used in the join condition and the `WHERE` clause.
`joinCondition`	string	Boolean SQL expression for the reference table join condition.

filterExpr string <optional> Boolean SQL expression for the WHERE clause. The expression may use updateTableAlias and aliases of the reference tables to refer to the corresponding tables.

filterExprParen boolean <optional> true indicates that if the filterExpr is included in a logical conjunction (the "AND" Boolean operation), it needs to be enclosed in parenthesis.

Returns:

The resulting UPDATE statement.

Type: string

buildUpsert(tableName, insertColumns, insertValues, uniqueColumn, sets)

Build a SQL statement that performs an "upsert" operation.

Parameters:

Name	Type	Description
`tableName`	string	Name of the table for the update/insert.
`insertColumns`	string	Comma-separated list of columns for the `INSERT` statement.
`insertValues`	string	Comma-separated list of values for the `INSERT` statement's `VALUES` clause.
`uniqueColumn`	string	Name of the column that has the unique constraint that is expected to be violated on the insert attempt.
`sets`	string	Comma-separated list of column/value assignments for the `UPDATE` statement's `SET` clause.

Returns:

The resulting SQL statement.

Type: string

castToString(expr)

Make SQL expression that converts the specified SQL expression to string.

Parameters:

Name	Type	Description
`expr`	string	The SQL expression to convert to string.

Returns:

SQL expression that evaluates to a string value.

Type: string

coalesce(exprs)

Make SQL expression that evaluates to the result of the first of the specified SQL expressions that is not NULL, or NULL if all evaluate to NULL.

Parameters:

Name	Type	Argument	Description
`exprs`	string	<repeatable>	SQL expressions to evaluate.

Returns:

SQL expression that evaluates to the first non-NULL.

Type: string

commitTransaction(connection, handler)

Commit transaction on the specified database connection.

Parameters:

Name Type Description

connection * Driver-specific database connection object.

handler

Object

The operation result handler.

Properties

Name	Type	Description
`onSuccess`	function	Function that gets called upon operation success.
`onError`	function	Function that gets called upon operation failure. The function receives a single argument with the error object.

connect(source, handler)

Acquire database connection.

Parameters:

Name Type Description

source * Driver-specific database connections source.

handler

Object

Connection result handler.

Properties

Name	Type	Description
`onSuccess`	function	Function that gets called when the connection is acquired. The connection is passed to it as the only argument.
`onError`	function	Function that gets called when connection could not be acquired. The error is passed to is as the only argument.

createVersionTableIfNotExists(connection, tableName, itemNames, handler)

Create special table used to track versions if it does not exist yet. The table has three columns: name (varchar 64, primary key), modified_on (timestamp) and version (unsigned integer). Also make sure that the table contains rows for every item name. The method creates and populates the table using correct locking so that multiple instances can be attempting to do it concurrently.

Parameters:

Name Type Description

connection * Driver-specific database connection object.

tableName string Table name.

itemNames Iterable.<string> Item names to make sure are represented in the table.

handler

Object

The operation result handler.

Properties

Name	Type	Argument	Description
`onSuccess`	function		Function that gets called upon operation success.
`onError`	function		Function that gets called upon operation failure. The function receives a single argument with the error object. The handler must ensure that the database connection is destroyed if the error is reported.
`trace`	function	<optional>	Function that gets called before the method sends a SQL command to the database. The command SQL is passed to the function as its only argument.

executeInsert(connection, statement, handler [, idColumn])

Execute specified INSERT statement.

Parameters:

Name Type Argument Description

connection * Driver-specific database connection object.

statement string The INSERT statement to execute.

handler

Object

The operation result handler.

Properties

Name	Type	Description
`onSuccess`	function	Function that gets called upon operation success. If `idColumn` argument is provided, the function gets the inserted record id as its only argument.
`onError`	function	Function that gets called upon operation failure. The function receives a single argument with the error object.

idColumn string <optional> Name of the auto-generated id column in the table. If specified, the result promise will resovle to the generated id value.

executeQuery(connection, statement, handler)

Execute specified statement on the specified database connection. The statement may be returning a result set.

Parameters:

Name Type Description

connection * Driver-specific database connection object.

statement string The statement to execute.

handler

Object

The operation result handler.

Properties

Name	Type	Argument	Description
`onSuccess`	function		Function that gets called upon operation success.
`onError`	function		Function that gets called upon operation failure. The function receives a single argument with the error object.
`onHeader`	function	<optional>	Function that gets called when the `SELECT` query result set descriptor is received from the database. The function receives a single argument, which is an array of strings corresponding to the column names in the result set.
`onRow`	function	<optional>	Function that gets called when the `SELECT` query result set row is received from the database. The function gets a single argument, which, depending on the driver, can be an array of values, one for each column, or an object with column names as the keys and the corresponding values as the values.

executeUpdate(connection, statement, handler)

Execute specified statement on the specified database connection. The statement is intended to modify data (such as UPDATE and DELETE statements).

Parameters:

Name Type Description

connection * Driver-specific database connection object.

statement string The statement to execute.

handler

Object

The operation result handler.

Properties

Name	Type	Description
`onSuccess`	function	Function that gets called upon operation success. The function gets the number of affected rows as its only argument.
`onError`	function	Function that gets called upon operation failure. The function receives a single argument with the error object.

getSessionVariable(connection, varName, type, handler)

Get server-side session variable.

Parameters:

Name Type Description

connection * Driver-specific database connection object.

varName string Variable name.

type string Expected variable type: "number", "boolean" or "string".

handler

Object

The operation result handler.

Properties

Name	Type	Description
`onSuccess`	function	Function that gets called upon operation success. The function receives the variable value as its only argument. If the variable is not set, `undefined` is passed to the function.
`onError`	function	Function that gets called upon operation failure. The function receives a single argument with the error object.

makeRangedSelect(selectStmt, offset, limit)

Make specified SELECT statement ranged.

Parameters:

Name	Type	Description
`selectStmt`	string	The `SELECT` statement to make ranged.
`offset`	number	Zero-based number of the first row in the range.
`limit`	number	Maximum number of rows in the range.

Returns:

Ranged SELECT statement.

Type: string

makeSelectWithLocks(selectStmt [, exclusiveLockTables] [, sharedLockTables])

Make the specified SELECT statement explicitely lock involved rows.

Parameters:

Name	Type	Argument	Description
`selectStmt`	string		The `SELECT` statement.
`exclusiveLockTables`	Array.<Object>	<optional>	Descriptors of tables to lock in exclusive mode. Descriptors have two properties: `tableName` and `tableAlias`.
`sharedLockTables`	Array.<Object>	<optional>	Descriptors of tables to lock in shared mode. Descriptors have two properties: `tableName` and `tableAlias`.

Returns:

SELECT statement based on the provided one that places the requested locks. If provided table lists were empty, the original query is returned unchanged.

nullableConcat(exprs)

Make SQL expression that concatenates the specified string SQL expressions and evaluates to NULL if any of them is NULL.

Parameters:

Name	Type	Argument	Description
`exprs`	string	<repeatable>	String SQL expressions to concatenate.

Returns:

SQL expression that concatenates the specified string SQL expressions.

Type: string

patternMatch(expr, pattern, invert, caseSensitive)

Make Boolean SQL expression that matches the specified character value expression against a LIKE pattern.

Parameters:

Name	Type	Description
`expr`	string	SQL character expression to match.
`pattern`	string	SQL expression for the `LIKE` pattern.
`invert`	boolean	`true` to test if the value does not match the pattern.
`caseSensitive`	boolean	`true` to make the test case-sensitive.

Returns:

Boolean SQL expression for the test.

Type: string

regexpMatch(expr, regexp, invert, caseSensitive)

Make Boolean SQL expression that matches the specified character value expression against a regular expression.

Parameters:

Name	Type	Description
`expr`	string	SQL character expression to match.
`regexp`	string	SQL expression for the regular expression.
`invert`	boolean	`true` to test if the value does not match the regular expression.
`caseSensitive`	boolean	`true` to make the test case-sensitive.

Returns:

Boolean SQL expression for the test.

Type: string

releaseConnection(source, connection [, err])

Release database connection previously acquired using the connect() method.

Parameters:

Name	Type	Argument	Description
`source`	*		Driver-specific database connections source.
`connection`	*		The connection to release.
`err`	external:Error	<optional>	Optional error object if the connection is being released after a database error, in which case the connection is destroyed.

rollbackTransaction(connection, handler)

Roll back transaction on the specified database connection.

Parameters:

Name Type Description

connection * Driver-specific database connection object.

handler

Object

The operation result handler.

Properties

Name	Type	Description
`onSuccess`	function	Function that gets called upon operation success.
`onError`	function	Function that gets called upon operation failure. The function receives a single argument with the error object.

safeLabel(label)

Get SQL fragment that can be used as a column label in a SELECT clause from the specified string.

Parameters:

Name	Type	Description
`label`	string	The ES string to use as a column label.

Returns:

SQL fragment for the label.

Type: string

safeLikePatternFromString(str)

Make necessary escapes in the specified string to make it safe to use as a SQL LIKE condition pattern.

Parameters:

Name	Type	Description
`str`	string	The ES string to make safe to use as a pattern.

Returns:

The string with all the necessary escapes.

Type: string

selectIntoAnchorTable(connection, anchorTableName, topTableName, idColumnName, idExpr, statementStump, handler)

Load the results of a SELECT query that is used to select record ids into a temporary anchor table. The resulting table will have two columns: "id" for the record ids returned by the SELECT query, and "ord" with sequential numbers reflecting the order, in which the SELECT query returned the ids.

Parameters:

Name Type Description

connection * Driver-specific database connection object.

anchorTableName string Name of the anchor table to use. Must be unique for the transaction.

topTableName string Name of the top table in the SELECT query. This is the table that contains the ids.

idColumnName string Name of the column in topTableName table that contains the ids being loaded into the anchor table.

idExpr string SQL value expression for the ids returned by the SELECT query.

statementStump string The SELECT query with "{*}" placeholder in the place of the select list.

handler

Object

The operation result handler.

Properties

Name	Type	Argument	Description
`onSuccess`	function		Function that gets called upon operation success. The function receives two arguments: the name of the anchor table and the number of ids loaded into the anchor table.
`onError`	function		Function that gets called upon operation failure. The function receives a single argument with the error object.
`trace`	function	<optional>	Function that gets called before the method sends a SQL command to the database. The command SQL is passed to the function as its only argument.

setSessionVariable(connection, varName, valueExpr, handler)

Set server-side session variable.

Parameters:

Name Type Description

connection * Driver-specific database connection object.

varName string Variable name.

valueExpr string SQL expression for the variable value.

handler

Object

The operation result handler.

Properties

Name	Type	Description
`onSuccess`	function	Function that gets called upon operation success.
`onError`	function	Function that gets called upon operation failure. The function receives a single argument with the error object.

sql(val)

Get SQL for the specified ES value.

Parameters:

Name	Type	Description
`val`	*	The ES value. If object, `toString()` is called on it and the result is returned as is. If `null`, string "NULL" is returned.

Returns:

String representing the value in SQL, or null if the value cannot be represented in SQL (includes undefined, NaN, Infinity and arrays).

Type: string

startTransaction(connection, handler)

Start transaction on the specified database connection.

Parameters:

Name Type Description

connection * Driver-specific database connection object.

handler

Object

The operation result handler.

Properties

Name	Type	Description
`onSuccess`	function	Function that gets called upon operation success.
`onError`	function	Function that gets called upon operation failure. The function receives a single argument with the error object.

stringLeftPad(expr, width, pad)

Make SQL expression that pads the specified string SQL expression with the specified character on the left until the specified length is achieved.

Parameters:

Name	Type	Description
`expr`	string	The string SQL expression.
`width`	number	Minimum resulting string length to achieve with padding.
`pad`	string	The character to use as the padding.

Returns:

SQL expression that pads the string on the left.

Type: string

stringLength(expr)

Make SQL expression that gets the string length for the specified string SQL expression.

Parameters:

Name	Type	Description
`expr`	string	The string SQL expression.

Returns:

SQL expression that gets the string length.

Type: string

stringLiteral(val)

Get string SQL literal.

Parameters:

Name	Type	Description
`val`	string	The ES string.

Returns:

String representing the string in SQL.

Type: string

stringLowercase(expr)

Make SQL expression that converts the specified string SQL expression to lower case.

Parameters:

Name	Type	Description
`expr`	string	The string SQL expression.

Returns:

SQL expression that converts the string to lower case.

Type: string

stringSubstring(expr, from [, len])

Make SQL expression for getting a substring of the specified string SQL expression.

Parameters:

Name	Type	Argument	Description
`expr`	string		SQL string expression.
`from`	number \| string		Zero-based first character to include in the substring. If not number, assumed to be a SQL expression.
`len`	number \| string	<optional>	Optional maximum length of the substring. If not number, assumed to be a SQL expression. If not specified, the end of the string is assumed.

Returns:

SQL expression for the substring.

Type: string

stringUppercase(expr)

Make SQL expression that converts the specified string SQL expression to upper case.

Parameters:

Name	Type	Description
`expr`	string	The string SQL expression.

Returns:

SQL expression that converts the string to upper case.

Type: string

supportsRowLocksWithAggregates()

Tell if the underlying database supports explicit row locking in SELECT queries with aggregates (e.g. MySQL) or not (e.g. PostgreSQL).

Returns:

true if supported.

Type: boolean

updateVersionTable(connection, tableName, itemNames, modificationTimestamp, handler)

Update versions tracking table.

Parameters:

Name Type Description

connection * Driver-specific database connection object.

tableName string Table name.

itemNames Array.<string> Names of the items to update.

modificationTimestamp string Modification timestamp.

handler

Object

The operation result handler.

Properties

Name	Type	Argument	Description
`onSuccess`	function		Function that gets called upon operation success.
`onError`	function		Function that gets called upon operation failure. The function receives a single argument with the error object.
`trace`	function	<optional>	Function that gets called before the method sends a SQL command to the database. The command SQL is passed to the function as its only argument.