Socket
Socket
Sign inDemoInstall

sequel-builder

Package Overview
Dependencies
64
Maintainers
1
Versions
17
Alerts
File Explorer

Advanced tools

npm Scripts

Install Socket

Detect and block malicious and high-risk dependencies

Install

Comparing version 0.0.2 to 0.0.3

500

dist/classes/query.builder.class.js

@@ -18,2 +18,6 @@ 'use strict';

var _strtr = require('phpjs/strtr');
var _strtr2 = _interopRequireDefault(_strtr);
function _interopRequireDefault(obj) { return obj && obj.__esModule ? obj : { default: obj }; }

@@ -27,5 +31,15 @@

/**
* Connection the database connection.
*/
this._db = db;
/**
* The separator between different fragments of a SQL statement.
* Defaults to an empty space. This is mainly used by [[build()]] when generating a SQL statement.
*/
this._separator = " ";
this._typeMap = [];
/**
* Array map of query condition to builder methods.
* These methods are used by [[buildCondition]] to build SQL conditions from array syntax.
*/
this._conditionBuilders = {

@@ -48,2 +62,13 @@ 'NOT': 'buildNotCondition',

/**
* Generates a SELECT SQL statement from a [[Query]] object.
* @param {Query} query the [[Query]] object from which the SQL statement will be generated.
* @param {Object} params the parameters to be bound to the generated SQL statement. These parameters will
* be included in the result with the additional parameters generated during the query building process.
* @return {Array} the generated SQL statement (the first array element) and the corresponding
* parameters to be bound to the SQL statement (the second array element). The parameters returned
* include those provided in `$params`.
*/
_createClass(QueryBuilder, [{

@@ -73,2 +98,11 @@ key: 'build',

}
/**
* @param {Array|Object} columns
* @param {Object} params the binding parameters to be populated
* @param {boolean} distinct
* @param {string} selectOption
* @return {string} the SELECT clause built from [[Query::select]].
*/
}, {

@@ -119,2 +153,9 @@ key: 'buildSelect',

}
/**
* @param {Array|Object} tables
* @param {Object} params the binding parameters to be populated
* @return {String} the FROM clause built from [[Query::from]].
*/
}, {

@@ -133,2 +174,10 @@ key: 'buildFrom',

}
/**
* @param {Array} joins
* @param {Object} params the binding parameters to be populated
* @return {String} the JOIN clause built from [[Query::join]].
* @throws Exception if the joins parameter is not in proper format
*/
}, {

@@ -152,3 +201,3 @@ key: 'buildJoin',

var tables = _this2._quoteTableNames(table, params);
//table = reset($tables); @todo figure out conversion.
table = tables.shift(); //@todo figure out conversion.
joins[i] = joinType + ' ' + table;

@@ -165,2 +214,11 @@ if (join[2] !== undefined) {

}
/**
* Quotes table names passed
*
* @param {Array|Object} tables
* @param {Object} params
* @return {Array|Object}
*/
}, {

@@ -201,2 +259,9 @@ key: '_quoteTableNames',

}
/**
* @param {String|Array} condition
* @param {Object} params the binding parameters to be populated
* @return {String} the WHERE clause built from [[Query::where]].
*/
}, {

@@ -208,2 +273,8 @@ key: 'buildWhere',

}
/**
* @param {Array|Object} columns
* @return {String} the GROUP BY clause
*/
}, {

@@ -214,2 +285,9 @@ key: 'buildGroupBy',

}
/**
* @param {String|Array|Object} condition
* @param {Object} params the binding parameters to be populated
* @return string the HAVING clause built from [[Query::having]].
*/
}, {

@@ -221,3 +299,163 @@ key: 'buildHaving',

}
/**
* Builds the ORDER BY and LIMIT/OFFSET clauses and appends them to the given SQL.
* @param {String} sql the existing SQL (without ORDER BY/LIMIT/OFFSET)
* @param {Array|Object} orderBy the order by columns. See [[Query::orderBy]] for more details on how to specify this parameter.
* @param {int} limit the limit number. See [[Query::limit]] for more details.
* @param {int} offset the offset number. See [[Query::offset]] for more details.
* @return {String} the SQL completed with ORDER BY/LIMIT/OFFSET (if any)
*/
}, {
key: 'buildOrderByAndLimit',
value: function buildOrderByAndLimit(sql, orderBy, limit, offset) {
orderBy = this.buildOrderBy(orderBy);
if (orderBy !== '') {
sql += this._separator + orderBy;
}
limit = this.buildLimit(limit, offset);
if (limit !== '') {
sql += this._separator + limit;
}
return sql;
}
/**
* @param {Array|Object} columns
* @return {String} the ORDER BY clause built from [[Query::$orderBy]].
*/
}, {
key: 'buildOrderBy',
value: function buildOrderBy(columns) {
var _this4 = this;
if (_lodash2.default.isEmpty(columns)) {
return '';
}
var orders = [];
Object.keys(columns).forEach(function (name) {
orders.push(_this4._db.quoteColumnName(name) + (columns[name] === _constants.SORT_DESC ? ' DESC' : ''));
});
return 'ORDER BY ' + orders.join(', ');
}
/**
* @param {int} limit
* @param {int} offset
* @return {String} the LIMIT and OFFSET clauses
*/
}, {
key: 'buildLimit',
value: function buildLimit(limit, offset) {
var sql = '';
if (this.hasLimit(limit)) {
sql = 'LIMIT ' + limit;
}
if (this.hasOffset(offset)) {
sql += ' OFFSET ' + offset;
}
return _lodash2.default.trimStart(sql);
}
/**
* Checks to see if the given limit is effective.
* @param {String} limit the given limit
* @return {boolean} whether the limit is effective
*/
}, {
key: 'hasLimit',
value: function hasLimit(limit) {
return (/^\d+$/.test(limit)
);
}
/**
* Checks to see if the given offset is effective.
* @param {String} offset the given offset
* @return {boolean} whether the offset is effective
*/
}, {
key: 'hasOffset',
value: function hasOffset(offset) {
return (/^\d+$/.test(offset) && offset != 0
);
}
/**
* @param {Array|Object} unions
* @param {Object} params the binding parameters to be populated
* @return {String} the UNION clause built from [[Query::union]].
*/
}, {
key: 'buildUnion',
value: function buildUnion(unions, params) {
var _this5 = this;
if (_lodash2.default.isEmpty(unions)) {
return '';
}
var result = '';
unions.forEach(function (union, i) {
var query = union['query'];
if (query instanceof _query.Query) {
var data = _this5.build(query, params);
unions[i]['query'] = data[0];
params = data[1];
}
result += 'UNION ' + (union['all'] ? 'ALL ' : '') + '( ' + unions[i]['query'] + ' ) ';
});
return result.trim();
}
/**
* Processes columns and properly quotes them if necessary.
* It will join all columns into a string with comma as separators.
* @param {String|Array|Object} columns the columns to be processed
* @return {String} the processing result
*/
}, {
key: 'buildColumns',
value: function buildColumns(columns) {
var _this6 = this;
if (!_lodash2.default.isArray(columns)) {
if (columns.indexOf(columns, '(') !== -1) {
return columns;
} else {
columns = columns.split('/\s*,\s*/').filter(function (value) {
return value.length > 0;
});
}
}
columns.forEach(function (column, i) {
if (column.indexOf('(') === -1) {
columns[i] = _this6._db.quoteColumnName(column);
}
});
return _lodash2.default.isArray(columns) ? columns.join(', ') : columns;
}
/**
* Parses the condition specification and generates the corresponding SQL expression.
* @param {String|Array|Object} condition the condition specification. Please refer to [[Query::where()]]
* on how to specify a condition.
* @param {Object} params the binding parameters to be populated
* @return {String} the generated SQL expression
*/
}, {
key: 'buildCondition',

@@ -252,28 +490,14 @@ value: function buildCondition(condition, params) {

}
}, {
key: 'buildColumns',
value: function buildColumns(columns) {
var _this4 = this;
if (!_lodash2.default.isArray(columns)) {
if (columns.indexOf(columns, '(') !== -1) {
return columns;
} else {
columns = columns.split('/\s*,\s*/').filter(function (value) {
return value.length > 0;
});
}
}
columns.forEach(function (column, i) {
if (column.indexOf('(') === -1) {
columns[i] = _this4._db.quoteColumnName(column);
}
});
/**
* Creates a condition based on column-value pairs.
* @param {Array|Object} condition the condition specification.
* @param {Object} params the binding parameters to be populated
* @return {String} the generated SQL expression
*/
return _lodash2.default.isArray(columns) ? columns.join(', ') : columns;
}
}, {
key: 'buildHashCondition',
value: function buildHashCondition(condition, params) {
var _this5 = this;
var _this7 = this;

@@ -285,6 +509,6 @@ var parts = [];

// IN condition
parts.push(_this5.buildInCondition('IN', [column, value], params));
parts.push(_this7.buildInCondition('IN', [column, value], params));
} else {
if (column.indexOf('(') === -1) {
column = _this5._db.quoteColumnName(column);
column = _this7._db.quoteColumnName(column);
}

@@ -303,6 +527,15 @@ if (value === null) {

}
/**
* Connects two or more SQL expressions with the `AND` or `OR` operator.
* @param {String} operator the operator to use for connecting the given operands
* @param {Array} operands the SQL expressions to connect.
* @param {Object} params the binding parameters to be populated
* @return {String} the generated SQL expression
*/
}, {
key: 'buildAndCondition',
value: function buildAndCondition(operator, operands, params) {
var _this6 = this;
var _this8 = this;

@@ -312,3 +545,3 @@ var parts = [];

if (_lodash2.default.isObject(operand)) {
operand = _this6.buildCondition(operand, params);
operand = _this8.buildCondition(operand, params);
}

@@ -325,2 +558,12 @@ if (operand !== '') {

}
/**
* Inverts an SQL expressions with `NOT` operator.
* @param {String} operator the operator to use for connecting the given operands
* @param {Array} operands the SQL expressions to connect.
* @param {Array} params the binding parameters to be populated
* @return {String} the generated SQL expression
* @throws Error if wrong number of operands have been given.
*/
}, {

@@ -343,2 +586,13 @@ key: 'buildNotCondition',

}
/**
* Creates an SQL expressions with the `BETWEEN` operator.
* @param {String} operator the operator to use (e.g. `BETWEEN` or `NOT BETWEEN`)
* @param {Array} operands the first operand is the column name. The second and third operands
* describe the interval that column value should be in.
* @param {Object|Array} params the binding parameters to be populated
* @return {String} the generated SQL expression
* @throws InvalidParamException if wrong number of operands have been given.
*/
}, {

@@ -369,2 +623,16 @@ key: 'buildBetweenCondition',

}
/**
* Creates an SQL expressions with the `IN` operator.
* @param {String} operator the operator to use (e.g. `IN` or `NOT IN`)
* @param {Array} operands the first operand is the column name. If it is an array
* a composite IN condition will be generated.
* The second operand is an array of values that column value should be among.
* If it is an empty array the generated expression will be a `false` value if
* operator is `IN` and empty if operator is `NOT IN`.
* @param {Object} params the binding parameters to be populated
* @return {String} the generated SQL expression
* @throws Error if wrong number of operands have been given.
*/
}, {

@@ -420,6 +688,17 @@ key: 'buildInCondition',

}
/**
* Builds SQL for IN condition
*
* @param {String} operator
* @param {Array} columns
* @param {Query} values
* @param {Array} params
* @return {String} SQL
*/
}, {
key: 'buildSubqueryInCondition',
value: function buildSubqueryInCondition(operator, columns, values, params) {
var _this7 = this;
var _this9 = this;

@@ -433,3 +712,3 @@ var _build3 = this.build(values, params);

if (col.indexOf(col, '(') === -1) {
columns[i] = _this7._db.quoteColumnName(col);
columns[i] = _this9._db.quoteColumnName(col);
}

@@ -445,6 +724,17 @@ });

}
/**
* Builds SQL for IN condition
*
* @param {String} operator
* @param {Array} columns
* @param {Query} values
* @param {Array} params
* @return {String} SQL
*/
}, {
key: 'buildCompositeInCondition',
value: function buildCompositeInCondition(operator, columns, values, params) {
var _this8 = this;
var _this10 = this;

@@ -468,3 +758,3 @@ var vss = [];

if (column.indexOf('(') === -1) {
columns[i] = _this8._db.quoteColumnName(column);
columns[i] = _this10._db.quoteColumnName(column);
}

@@ -475,80 +765,102 @@ });

}
/**
* Creates an SQL expressions with the `LIKE` operator.
* @param {String} operator the operator to use (e.g. `LIKE`, `NOT LIKE`, `OR LIKE` or `OR NOT LIKE`)
* @param {Array} operands an array of two or three operands
*
* - The first operand is the column name.
* - The second operand is a single value or an array of values that column value
* should be compared with. If it is an empty array the generated expression will
* be a `false` value if operator is `LIKE` or `OR LIKE`, and empty if operator
* is `NOT LIKE` or `OR NOT LIKE`.
* - An optional third operand can also be provided to specify how to escape special characters
* in the value(s). The operand should be an array of mappings from the special characters to their
* escaped counterparts. If this operand is not provided, a default escape mapping will be used.
* You may use `false` or an empty array to indicate the values are already escaped and no escape
* should be applied. Note that when using an escape mapping (or the third operand is not provided),
* the values will be automatically enclosed within a pair of percentage characters.
* @param {Object} params the binding parameters to be populated
* @return {String} the generated SQL expression
* @throws Error if wrong number of operands have been given.
*/
}, {
key: 'buildOrderByAndLimit',
value: function buildOrderByAndLimit(sql, orderBy, limit, offset) {
orderBy = this.buildOrderBy(orderBy);
if (orderBy !== '') {
sql += this._separator + orderBy;
key: 'buildLikeCondition',
value: function buildLikeCondition(operator, operands, params) {
if (operands[0] === undefined || operands[1] === undefined) {
throw new Error('Operator \'' + operator + '\' requires two operands.');
}
limit = this.buildLimit(limit, offset);
if (limit !== '') {
sql += this._separator + limit;
var escape = operands[2] !== undefined ? operands[2] : { '%': '\%', '_': '\_', '\\': '\\\\' };
delete operands[2];
var matches = operator.match(/^(AND |OR |)(((NOT |))I?LIKE)/);
if (!matches) {
throw new Error('Invalid operator \'' + operator + '\'.');
}
return sql;
}
}, {
key: 'buildUnion',
value: function buildUnion(unions, params) {
var _this9 = this;
var andor = ' ' + (!_lodash2.default.isEmpty(matches[1]) ? matches[1] : 'AND ');
var not = !_lodash2.default.isEmpty(matches[3]);
operator = matches[2];
if (_lodash2.default.isEmpty(unions)) {
return '';
var column = operands[0];
var values = operands[1];
if (!_lodash2.default.isArray(values)) {
values = [values];
}
var result = '';
if (_lodash2.default.isEmpty(values)) {
return not ? '' : '0=1';
}
unions.forEach(function (union, i) {
var query = union['query'];
if (query instanceof _query.Query) {
var data = _this9.build(query, params);
unions[i]['query'] = data[0];
params = data[1];
}
if (column.indexOf('(') === -1) {
column = this._db.quoteColumnName(column);
}
result += 'UNION ' + (union['all'] ? 'ALL ' : '') + '( ' + unions[i]['query'] + ' ) ';
var parts = [];
values.forEach(function (value) {
var index = _lodash2.default.isArray(params) ? params.length : Object.keys(params).length;
var phName = '' + QueryBuilder.PARAM_PREFIX + index;
params[phName] = _lodash2.default.isEmpty(escape) ? value : '%' + (0, _strtr2.default)(value, escape) + '%';
parts.push(column + ' ' + operator + ' ' + phName);
});
return trim(result);
return parts.join(andor);
}
/**
* Creates an SQL expressions with the `EXISTS` operator.
* @param {String} operator the operator to use (e.g. `EXISTS` or `NOT EXISTS`)
* @param {Array} operands contains only one element which is a [[Query]] object representing the sub-query.
* @param {Object} params the binding parameters to be populated
* @return {String} the generated SQL expression
* @throws Error if the operand is not a [[Query]] object.
*/
}, {
key: 'buildOrderBy',
value: function buildOrderBy(columns) {
var _this10 = this;
key: 'buildExistsCondition',
value: function buildExistsCondition(operator, operands, params) {
if (operands[0] instanceof _query.Query) {
var _build4 = this.build(operands[0], params);
if (_lodash2.default.isEmpty(columns)) {
return '';
var sql = _build4[0];
return operator + ' (' + sql + ')';
} else {
throw new Error('Subquery for EXISTS operator must be a Query object.');
}
var orders = [];
Object.keys(columns).forEach(function (name) {
orders.push(_this10._db.quoteColumnName(name) + (columns[name] === _constants.SORT_DESC ? ' DESC' : ''));
});
return 'ORDER BY ' + orders.join(', ');
}
}, {
key: 'buildLimit',
value: function buildLimit(limit, offset) {
var sql = '';
if (this.hasLimit(limit)) {
sql = 'LIMIT ' + limit;
}
if (this.hasOffset(offset)) {
sql += ' OFFSET ' + offset;
}
return _lodash2.default.trimStart(sql);
}
/**
* Creates an SQL expressions like `"column" operator value`.
* @param {String} operator the operator to use. Anything could be used e.g. `>`, `<=`, etc.
* @param {Array} operands contains two column names.
* @param {Array} params the binding parameters to be populated
* @return string the generated SQL expression
* @throws InvalidParamException if wrong number of operands have been given.
*/
}, {
key: 'hasLimit',
value: function hasLimit(limit) {
return (/^\d+$/.test(limit)
);
}
}, {
key: 'hasOffset',
value: function hasOffset(offset) {
return (/^\d+$/.test(offset) && offset != 0
);
}
}, {
key: 'buildSimpleCondition',

@@ -571,5 +883,5 @@ value: function buildSimpleCondition(operator, operands, params) {

} else if (value instanceof _query.Query) {
var _build4 = this.build(value, params);
var _build5 = this.build(value, params);
var sql = _build4.sql;
var sql = _build5.sql;

@@ -576,0 +888,0 @@ return column + ' ' + operator + ' (' + sql + ')';

5

package.json
{
"name": "sequel-builder",
"version": "0.0.2",
"version": "0.0.3",
"description": "Ported query library from php yii2 to nodejs. currently only supporting mysql.",

@@ -14,3 +14,4 @@ "main": "index.js",

"lodash": "^4.7.0",
"mysql": "^2.10.2"
"mysql": "^2.10.2",
"phpjs": "^1.3.2"
},

@@ -17,0 +18,0 @@ "devDependencies": {

390

src/classes/query.builder.class.js
import _ from 'lodash';
import {Query} from './query.class';
import {SORT_DESC} from '../constants';
import strtr from 'phpjs/strtr';

@@ -11,5 +12,15 @@ export class QueryBuilder {

/**
* Connection the database connection.
*/
this._db = db;
/**
* The separator between different fragments of a SQL statement.
* Defaults to an empty space. This is mainly used by [[build()]] when generating a SQL statement.
*/
this._separator = " ";
this._typeMap = [];
/**
* Array map of query condition to builder methods.
* These methods are used by [[buildCondition]] to build SQL conditions from array syntax.
*/
this._conditionBuilders = {

@@ -32,2 +43,11 @@ 'NOT': 'buildNotCondition',

/**
* Generates a SELECT SQL statement from a [[Query]] object.
* @param {Query} query the [[Query]] object from which the SQL statement will be generated.
* @param {Object} params the parameters to be bound to the generated SQL statement. These parameters will
* be included in the result with the additional parameters generated during the query building process.
* @return {Array} the generated SQL statement (the first array element) and the corresponding
* parameters to be bound to the SQL statement (the second array element). The parameters returned
* include those provided in `$params`.
*/
build(query, params={}) {

@@ -59,2 +79,9 @@

/**
* @param {Array|Object} columns
* @param {Object} params the binding parameters to be populated
* @param {boolean} distinct
* @param {string} selectOption
* @return {string} the SELECT clause built from [[Query::select]].
*/
buildSelect(columns, params, distinct, selectOption) {

@@ -98,2 +125,7 @@

/**
* @param {Array|Object} tables
* @param {Object} params the binding parameters to be populated
* @return {String} the FROM clause built from [[Query::from]].
*/
buildFrom(tables, params) {

@@ -109,2 +141,8 @@ if (_.isEmpty(tables)) {

/**
* @param {Array} joins
* @param {Object} params the binding parameters to be populated
* @return {String} the JOIN clause built from [[Query::join]].
* @throws Exception if the joins parameter is not in proper format
*/
buildJoin(joins, params) {

@@ -122,3 +160,3 @@ if (_.isEmpty(joins)) {

var tables = this._quoteTableNames(table, params);
//table = reset($tables); @todo figure out conversion.
table = tables.shift(); //@todo figure out conversion.
joins[i] = `${joinType} ${table}`;

@@ -136,2 +174,9 @@ if (join[2] !== undefined) {

/**
* Quotes table names passed
*
* @param {Array|Object} tables
* @param {Object} params
* @return {Array|Object}
*/
_quoteTableNames(tables, params) {

@@ -164,2 +209,7 @@ Object.keys(tables).forEach ((i) => {

/**
* @param {String|Array} condition
* @param {Object} params the binding parameters to be populated
* @return {String} the WHERE clause built from [[Query::where]].
*/
buildWhere(condition, params) {

@@ -170,2 +220,6 @@ var where = this.buildCondition(condition, params);

/**
* @param {Array|Object} columns
* @return {String} the GROUP BY clause
*/
buildGroupBy(columns) {

@@ -175,2 +229,7 @@ return _.isEmpty(columns) ? '' : 'GROUP BY ' + this.buildColumns(columns);

/**
* @param {String|Array|Object} condition
* @param {Object} params the binding parameters to be populated
* @return string the HAVING clause built from [[Query::having]].
*/
buildHaving(condition, params) {

@@ -181,2 +240,129 @@ var having = this.buildCondition(condition, params);

/**
* Builds the ORDER BY and LIMIT/OFFSET clauses and appends them to the given SQL.
* @param {String} sql the existing SQL (without ORDER BY/LIMIT/OFFSET)
* @param {Array|Object} orderBy the order by columns. See [[Query::orderBy]] for more details on how to specify this parameter.
* @param {int} limit the limit number. See [[Query::limit]] for more details.
* @param {int} offset the offset number. See [[Query::offset]] for more details.
* @return {String} the SQL completed with ORDER BY/LIMIT/OFFSET (if any)
*/
buildOrderByAndLimit(sql, orderBy, limit, offset) {
orderBy = this.buildOrderBy(orderBy);
if (orderBy !== '') {
sql += this._separator + orderBy;
}
limit = this.buildLimit(limit, offset);
if (limit !== '') {
sql += this._separator + limit;
}
return sql;
}
/**
* @param {Array|Object} columns
* @return {String} the ORDER BY clause built from [[Query::$orderBy]].
*/
buildOrderBy(columns) {
if (_.isEmpty(columns)) {
return '';
}
var orders = [];
Object.keys(columns).forEach((name) => {
orders.push(this._db.quoteColumnName(name) + (columns[name] === SORT_DESC ? ' DESC' : ''));
});
return 'ORDER BY ' + orders.join(', ');
}
/**
* @param {int} limit
* @param {int} offset
* @return {String} the LIMIT and OFFSET clauses
*/
buildLimit(limit, offset) {
var sql = '';
if (this.hasLimit(limit)) {
sql = 'LIMIT ' + limit;
}
if (this.hasOffset(offset)) {
sql += ' OFFSET ' + offset;
}
return _.trimStart(sql);
}
/**
* Checks to see if the given limit is effective.
* @param {String} limit the given limit
* @return {boolean} whether the limit is effective
*/
hasLimit(limit) {
return /^\d+$/.test(limit);
}
/**
* Checks to see if the given offset is effective.
* @param {String} offset the given offset
* @return {boolean} whether the offset is effective
*/
hasOffset(offset) {
return /^\d+$/.test(offset) && offset != 0;
}
/**
* @param {Array|Object} unions
* @param {Object} params the binding parameters to be populated
* @return {String} the UNION clause built from [[Query::union]].
*/
buildUnion(unions, params) {
if (_.isEmpty(unions)) {
return '';
}
var result = '';
unions.forEach((union, i) => {
var query = union['query'];
if (query instanceof Query) {
var data = this.build(query, params);
unions[i]['query'] = data[0];
params = data[1];
}
result += 'UNION ' + (union['all'] ? 'ALL ' : '') + '( ' + unions[i]['query'] + ' ) ';
});
return result.trim();
}
/**
* Processes columns and properly quotes them if necessary.
* It will join all columns into a string with comma as separators.
* @param {String|Array|Object} columns the columns to be processed
* @return {String} the processing result
*/
buildColumns(columns) {
if (!_.isArray(columns)) {
if (columns.indexOf(columns, '(') !== -1) {
return columns;
} else {
columns = columns.split('/\s*,\s*/').filter((value) => value.length > 0);
}
}
columns.forEach((column, i) => {
if (column.indexOf('(') === -1) {
columns[i] = this._db.quoteColumnName(column);
}
});
return _.isArray(columns) ? columns.join(', ') : columns;
}
/**
* Parses the condition specification and generates the corresponding SQL expression.
* @param {String|Array|Object} condition the condition specification. Please refer to [[Query::where()]]
* on how to specify a condition.
* @param {Object} params the binding parameters to be populated
* @return {String} the generated SQL expression
*/
buildCondition(condition, params) {

@@ -209,19 +395,8 @@

buildColumns(columns) {
if (!_.isArray(columns)) {
if (columns.indexOf(columns, '(') !== -1) {
return columns;
} else {
columns = columns.split('/\s*,\s*/').filter((value) => value.length > 0);
}
}
columns.forEach((column, i) => {
if (column.indexOf('(') === -1) {
columns[i] = this._db.quoteColumnName(column);
}
});
return _.isArray(columns) ? columns.join(', ') : columns;
}
/**
* Creates a condition based on column-value pairs.
* @param {Array|Object} condition the condition specification.
* @param {Object} params the binding parameters to be populated
* @return {String} the generated SQL expression
*/
buildHashCondition(condition, params) {

@@ -251,2 +426,9 @@ var parts = [];

/**
* Connects two or more SQL expressions with the `AND` or `OR` operator.
* @param {String} operator the operator to use for connecting the given operands
* @param {Array} operands the SQL expressions to connect.
* @param {Object} params the binding parameters to be populated
* @return {String} the generated SQL expression
*/
buildAndCondition(operator, operands, params)

@@ -270,2 +452,10 @@ {

/**
* Inverts an SQL expressions with `NOT` operator.
* @param {String} operator the operator to use for connecting the given operands
* @param {Array} operands the SQL expressions to connect.
* @param {Array} params the binding parameters to be populated
* @return {String} the generated SQL expression
* @throws Error if wrong number of operands have been given.
*/
buildNotCondition(operator, operands, params) {

@@ -287,2 +477,11 @@ if (operands.length != 1) {

/**
* Creates an SQL expressions with the `BETWEEN` operator.
* @param {String} operator the operator to use (e.g. `BETWEEN` or `NOT BETWEEN`)
* @param {Array} operands the first operand is the column name. The second and third operands
* describe the interval that column value should be in.
* @param {Object|Array} params the binding parameters to be populated
* @return {String} the generated SQL expression
* @throws InvalidParamException if wrong number of operands have been given.
*/
buildBetweenCondition(operator, operands, params) {

@@ -309,2 +508,14 @@ if (operands[0] === undefined || operands[1] === undefined || operands[2] == undefined) {

/**
* Creates an SQL expressions with the `IN` operator.
* @param {String} operator the operator to use (e.g. `IN` or `NOT IN`)
* @param {Array} operands the first operand is the column name. If it is an array
* a composite IN condition will be generated.
* The second operand is an array of values that column value should be among.
* If it is an empty array the generated expression will be a `false` value if
* operator is `IN` and empty if operator is `NOT IN`.
* @param {Object} params the binding parameters to be populated
* @return {String} the generated SQL expression
* @throws Error if wrong number of operands have been given.
*/
buildInCondition(operator, operands, params) {

@@ -357,2 +568,11 @@ if (operands[0] === undefined || operands[1] === undefined) {

/**
* Builds SQL for IN condition
*
* @param {String} operator
* @param {Array} columns
* @param {Query} values
* @param {Array} params
* @return {String} SQL
*/
buildSubqueryInCondition(operator, columns, values, params) {

@@ -375,2 +595,11 @@ var {0: sql} = this.build(values, params);

/**
* Builds SQL for IN condition
*
* @param {String} operator
* @param {Array} columns
* @param {Query} values
* @param {Array} params
* @return {String} SQL
*/
buildCompositeInCondition(operator, columns, values, params) {

@@ -401,67 +630,90 @@ var vss = [];

buildOrderByAndLimit(sql, orderBy, limit, offset) {
orderBy = this.buildOrderBy(orderBy);
if (orderBy !== '') {
sql += this._separator + orderBy;
/**
* Creates an SQL expressions with the `LIKE` operator.
* @param {String} operator the operator to use (e.g. `LIKE`, `NOT LIKE`, `OR LIKE` or `OR NOT LIKE`)
* @param {Array} operands an array of two or three operands
*
* - The first operand is the column name.
* - The second operand is a single value or an array of values that column value
* should be compared with. If it is an empty array the generated expression will
* be a `false` value if operator is `LIKE` or `OR LIKE`, and empty if operator
* is `NOT LIKE` or `OR NOT LIKE`.
* - An optional third operand can also be provided to specify how to escape special characters
* in the value(s). The operand should be an array of mappings from the special characters to their
* escaped counterparts. If this operand is not provided, a default escape mapping will be used.
* You may use `false` or an empty array to indicate the values are already escaped and no escape
* should be applied. Note that when using an escape mapping (or the third operand is not provided),
* the values will be automatically enclosed within a pair of percentage characters.
* @param {Object} params the binding parameters to be populated
* @return {String} the generated SQL expression
* @throws Error if wrong number of operands have been given.
*/
buildLikeCondition(operator, operands, params)
{
if (operands[0] === undefined || operands[1] === undefined) {
throw new Error(`Operator '${operator}' requires two operands.`);
}
limit = this.buildLimit(limit, offset);
if (limit !== '') {
sql += this._separator + limit;
}
return sql;
}
buildUnion(unions, params) {
if (_.isEmpty(unions)) {
return '';
var escape = operands[2] !== undefined ? operands[2] : {'%': '\%', '_': '\_', '\\': '\\\\'};
delete operands[2];
var matches = operator.match(/^(AND |OR |)(((NOT |))I?LIKE)/);
if (!matches) {
throw new Error(`Invalid operator '${operator}'.`);
}
var andor = ' ' + (!_.isEmpty(matches[1]) ? matches[1] : 'AND ');
var not = !_.isEmpty(matches[3]);
operator = matches[2];
var result = '';
var {0: column, 1: values} = operands;
unions.forEach((union, i) => {
var query = union['query'];
if (query instanceof Query) {
var data = this.build(query, params);
unions[i]['query'] = data[0];
params = data[1];
}
if (!_.isArray(values)) {
values = [values];
}
result += 'UNION ' + (union['all'] ? 'ALL ' : '') + '( ' + unions[i]['query'] + ' ) ';
});
if (_.isEmpty(values)) {
return not ? '' : '0=1';
}
return trim(result);
}
if (column.indexOf('(') === -1) {
column = this._db.quoteColumnName(column);
}
buildOrderBy(columns) {
if (_.isEmpty(columns)) {
return '';
}
var orders = [];
Object.keys(columns).forEach((name) => {
orders.push(this._db.quoteColumnName(name) + (columns[name] === SORT_DESC ? ' DESC' : ''));
var parts = [];
values.forEach((value) => {
var index = _.isArray(params) ? params.length : Object.keys(params).length;
var phName = `${QueryBuilder.PARAM_PREFIX}${index}`;
params[phName] = _.isEmpty(escape) ? value : ('%' + strtr(value, escape) + '%');
parts.push(`${column} ${operator} ${phName}`);
});
return 'ORDER BY ' + orders.join(', ');
return parts.join(andor);
}
buildLimit(limit, offset) {
var sql = '';
if (this.hasLimit(limit)) {
sql = 'LIMIT ' + limit;
/**
* Creates an SQL expressions with the `EXISTS` operator.
* @param {String} operator the operator to use (e.g. `EXISTS` or `NOT EXISTS`)
* @param {Array} operands contains only one element which is a [[Query]] object representing the sub-query.
* @param {Object} params the binding parameters to be populated
* @return {String} the generated SQL expression
* @throws Error if the operand is not a [[Query]] object.
*/
buildExistsCondition(operator, operands, params)
{
if (operands[0] instanceof Query) {
var {0: sql} = this.build(operands[0], params);
return `${operator} (${sql})`;
} else {
throw new Error('Subquery for EXISTS operator must be a Query object.');
}
if (this.hasOffset(offset)) {
sql += ' OFFSET ' + offset;
}
return _.trimStart(sql);
}
hasLimit(limit) {
return /^\d+$/.test(limit);
}
hasOffset(offset) {
return /^\d+$/.test(offset) && offset != 0;
}
/**
* Creates an SQL expressions like `"column" operator value`.
* @param {String} operator the operator to use. Anything could be used e.g. `>`, `<=`, etc.
* @param {Array} operands contains two column names.
* @param {Array} params the binding parameters to be populated
* @return string the generated SQL expression
* @throws InvalidParamException if wrong number of operands have been given.
*/
buildSimpleCondition(operator, operands, params)

@@ -468,0 +720,0 @@ {

SocketSocket SOC 2 Logo

Product

  • Package Alerts
  • Integrations
  • Docs
  • Pricing
  • FAQ
  • Roadmap

About

Packages

Stay in touch

Get open source security insights delivered straight into your inbox.

  • Terms
  • Privacy
  • Security

Made with ⚡️ by Socket Inc