Defined sql.js

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; //=> 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);

 sql.a.cast("boolean"); //=> 'CAST(a AS boolean)' //=> ((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))"case").sql;
     //=> SELECT (CASE WHEN b THEN (c = 1) ELSE 'f' END) FROM t;
     //=>  SELECT a FROM t

 ds.order(sql.a.desc(), sql.b.asc()).sql;
     //=>  SELECT  FROM t ORDER BY a DESC, b ASC"b")).sql;
     //=> SELECT a AS b FROM t


     //=> SELECT  FROM t WHERE CAST(a AS boolean)

 ds.filter(sql.a("b", 1)).sql
     //=> SELECT  FROM t WHERE a(b, 1)
     //=> 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)"'a'"))
    .group(sql.literal('a > ?', [1]))
     //=>"SELECT 'a' FROM t WHERE (a(3) AND (blah)) GROUP BY a > 1 HAVING 'f' ORDER BY true");

PropertyTypeDefault ValueDescription

Represents inverse boolean constants (currently only NOTNULL). A special class to allow for special behavior.

case Static Function Public

Defined sql.js

Returns a patio.sql.CaseExpression. See patio.sql.CaseExpression for argument types.

sql["case"]({a:sql.b}, sql.c, sql.d); //=> (CASE t.d WHEN t.a THEN t.b ELSE t.c END)
Arguments Source
function (hash,/*args**/opts){
   var args = argsToArray(arguments, 1);
   return CaseExpression.fromArgs([hashToArray(hash)].concat(args));

identifier Static Function Public

Defined sql.js

Returns a patio.sql.Identifier, patio.sql.QualifiedIdentifier, or patio.sql.ALiasedExpression depending on the format of the string passed in.

each portion of the identifier is optional. See example below

patio.sql.identifier("a") //= > new patio.sql.Identifier("a");
patio.sql.identifier("table__column"); //=> new patio.sql.QualifiedIdentifier(table, column);
     //=> new patio.sql.AliasedExpression(new patio.sql.QualifiedIdentifier(table, column), alias);
Arguments Returns Source
function (s){
   return sql.stringToIdentifier(s);

json Static Function Public

Defined sql.js

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.

Arguments Returns Source
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;

literal Static Function Public

Defined sql.js

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.

//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 Returns Source
function (s){
   var args = argsToArray(arguments);
   return args.length > 1 ? PlaceHolderLiteralString.fromArgs(args) : new LiteralString(s);

sqlStringJoin Static Function Public

Defined sql.js

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.

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 Source
function (arr,joiner){
   joiner = joiner || null;
   var args;
   arr = (a) {
       return isInstanceOf(a, Expression, LiteralString, Boolean) || isNull(a) ? a : sql.stringToIdentifier(a);
   if (joiner) {
       var newJoiner = [];
       for (var i = 0; i &lt; arr.length; i++) {
       args = array.flatten(, newJoiner));
   } else {
       args = arr;
   args = (a) {
       return isInstanceOf(a, Expression, LiteralString, Boolean) || isNull(a) ? a : "" + a;
   return StringExpression.fromArgs(["||"].concat(args));

stringToIdentifier Static Function Public

Defined sql.js

Arguments Source
function (name){
   !Dataset && (Dataset = require("./dataset"));
   return new Dataset().stringToIdentifier(name);

Documentation generated using coddoc.