Interface: DBDriver

x2node-dbos. 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> <optional>
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> <optional>
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, 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).
Parameters:
Name Type Description
connection * Driver-specific database connection object.
tableName string Table name.
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.

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 release after a database error.

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 for the temporary anchor table.
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 the number of ids loaded into the anchor table as its only argument.
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.