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
 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");
 

PropertyTypeDefault ValueDescription
NegativeBooleanConstant

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.

Example
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

Example
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 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.

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 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.

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 Source
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 &lt; 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));
       
}
    

stringToIdentifier Static Function Public


Defined sql.js

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

Documentation generated using coddoc.