Collection of SQL related types, and expressions.
The patio.sql object can be used directly to create patio.sql.Expressions, patio.sql.Identifiers, patio.sql.SQLFunctions, and patio.sql.QualifiedIdentifiers.
var sql = patio.sql;
//creating an identifier
sql.a; //=> a;
//creating a qualified identifier
sql.table__column; //table.column;
//BooleanExpression
sql.a.lt(sql.b); //=> a < 'b';
//SQL Functions
sql.sum(sql.a); //=> sum(a)
sql.avg(sql.b); //=> avg(b)
sql.a("b", 1); //=> a(b, 1)
sql.myDatabasesObjectFunction(sql.a, sql.b, sql.c); //=> myDatabasesObjectFunction(a, b, c);
//combined
sql.a.cast("boolean"); //=> 'CAST(a AS boolean)'
sql.a.plus(sql.b).lt(sql.c.minus(3) //=> ((a + b) < (c - 3))
This is useful when combined with dataset filtering
var ds = DB.from("t");
ds.filter({a:[sql.b, sql.c]}).sql;
//=> SELECT FROM t WHERE (a IN (b, c))
ds.select(sql"case").sql;
//=> SELECT (CASE WHEN b THEN (c = 1) ELSE 'f' END) FROM t
ds.select(sql.a).qualifyToFirstSource().sql;
//=> SELECT a FROM t
ds.order(sql.a.desc(), sql.b.asc()).sql;
//=> SELECT FROM t ORDER BY a DESC, b ASC
ds.select(sql.a.as("b")).sql;
//=> SELECT a AS b FROM t
ds.filter(sql"case").sql
//=> SELECT FROM t WHERE (CASE d WHEN a THEN b ELSE c END)
ds.filter(sql.a.cast("boolean")).sql;
//=> SELECT FROM t WHERE CAST(a AS boolean)
ds.filter(sql.a("b", 1)).sql
//=> SELECT FROM t WHERE a(b, 1)
ds.filter(sql.a.plus(sql.b).lt(sql.c.minus(3)).sql;
//=> SELECT FROM t WHERE ((a + b) < (c - 3))
ds.filter(sql.a.sqlSubscript(sql.b, 3)).sql;
//=> SELECT FROM t WHERE a[b, 3]
ds.filter('? > ?', sql.a, 1).sql;
//=> SELECT FROM t WHERE (a > 1);
ds.filter('{a} > {b}', {a:sql.c, b:1}).sql;
//=>SELECT * FROM t WHERE (c > 1)
ds.select(sql.literal("'a'"))
.filter(sql.a(3))
.filter('blah')
.order(sql.literal(true))
.group(sql.literal('a > ?', [1]))
.having(false).sql;
//=>"SELECT 'a' FROM t WHERE (a(3) AND (blah)) GROUP BY a > 1 HAVING 'f' ORDER BY true");
| Property | Type | Default Value | Description |
| NegativeBooleanConstant | Represents inverse boolean constants (currently only NOTNULL). A special class to allow for special behavior. | ||
Returns a patio.sql.CaseExpression. See patio.sql.CaseExpression for argument types.
Example
sql["case"]({a:sql.b}, sql.c, sql.d); //=> (CASE t.d WHEN t.a THEN t.b ELSE t.c END)
Arguments
function (hash,/*args**/opts){
var args = argsToArray(arguments, 1);
return CaseExpression.fromArgs([hashToArray(hash)].concat(args));
}
Returns a patio.sql.Identifier, patio.sql.QualifiedIdentifier, or patio.sql.ALiasedExpression depending on the format of the string passed in.
patio.sql.identifier("a") //= > new patio.sql.Identifier("a");
patio.sql.identifier("table__column"); //=> new patio.sql.QualifiedIdentifier(table, column);
patio.sql.identifier("table__column___alias");
//=> new patio.sql.AliasedExpression(new patio.sql.QualifiedIdentifier(table, column), alias);
Arguments
String : the name to covert to an an patio.sql.Identifier, patio.sql.QualifiedIdentifier, or patio.sql.AliasedExpression.
patio.sql.Identifier|patio.sql.QualifiedIdentifier|patio.sql.AliasedExpression an identifier generated based on the name string.
function (s){
return sql.stringToIdentifier(s);
}
Creates a patio.sql.Json depending on the arguments passed in. If a single string is passed in then it is assumed that it's a valid json string. If an objects passed in it will stringify it.
ArgumentsString or Object ... : object or string.
patio.sql.Json an expression that can be used as an argument for patio.Dataset query methods.
function (json){
var ret = json;
if (!(isInstanceOf(ret, Json, JsonArray))) {
if (isString(ret)) {
ret = JSON.parse(ret);
}
if (isUndefinedOrNull(ret)) {
ret = null;
} else if (isArray(ret)) {
ret = new JsonArray(ret);
} else if (isObject(ret)) {
ret = new Json(ret);
} else {
throw new ExpressionError("Invalid value for json " + ret);
}
}
return ret;
}
Creates a patio.sql.LiteralString or patio.sql.PlaceHolderLiteralString depending on the arguments passed in. If a single string is passed in then it is assumed to be a patio.sql.LiteralString. If more than one argument is passed in then it is assumed to be a patio.sql.PlaceHolderLiteralString.
Example
//a literal string that will be placed in an SQL query with out quoting.
patio.sql.literal("a"); //=> new patio.sql.LiteralString('a');
//a placeholder string that will have ? replaced with the {@link patio.Dataset#literal} version of
//the arugment and replaced in the string.
patio.sql.literal("a = ?", 1) //=> a = 1
patio.sql.literal("a = ?", "b"); //=> a = 'b'
patio.sql.literal("a = {a} AND b = {b}", {a : 1, b : 2}); //=> a = 1 AND b = 2
Arguments
variable number of arguments where the first argument is a string. If multiple arguments are passed it is a assumed to be a patio.sql.PlaceHolderLiteralString
patio.sql.LiteralString|patio.sql.PlaceHolderLiteralString an expression that can be used as an argument for patio.Dataset query methods.
function (s){
var args = argsToArray(arguments);
return args.length > 1 ? PlaceHolderLiteralString.fromArgs(args) : new LiteralString(s);
}
Creates a patio.sql.StringExpression
Return a patio.sql.StringExpression representing an SQL string made up of the concatenation of this array's elements. If an joiner is passed it is used in between each element of the array in the SQL concatenation.
Example
patio.sql.sqlStringJoin(["a"]); //=> a
//you can use sql.* as a shortcut to get an identifier
patio.sql.sqlStringJoin([sql.identifier("a"), sql.b]);//=> a || b
patio.sql.sqlStringJoin([sql.a, 'b']) # SQL: a || 'b'
patio.sql.sqlStringJoin(['a', sql.b], ' '); //=> 'a' || ' ' || b
Arguments
function (arr,joiner){
joiner = joiner || null;
var args;
arr = arr.map(function (a) {
return isInstanceOf(a, Expression, LiteralString, Boolean) || isNull(a) ? a : sql.stringToIdentifier(a);
});
if (joiner) {
var newJoiner = [];
for (var i = 0; i < arr.length; i++) {
newJoiner.push(joiner);
}
args = array.flatten(array.zip(arr, newJoiner));
args.pop();
} else {
args = arr;
}
args = args.map(function (a) {
return isInstanceOf(a, Expression, LiteralString, Boolean) || isNull(a) ? a : "" + a;
});
return StringExpression.fromArgs(["||"].concat(args));
}
function (name){
!Dataset && (Dataset = require("./dataset"));
return new Dataset().stringToIdentifier(name);
}