Class that is used for querying/retrieving datasets from a database.

Dynamically generated methods include

• Join methods from patio.Dataset.CONDITIONED_JOIN_TYPES and patio.Dataset.UNCONDITIONED_JOIN_TYPES, these methods handle the type call to patio.Dataset#joinTable, so to invoke include all arguments that patio.Dataset#joinTable requires except the type parameter. The default list includes.
  • Conditioned join types that accept conditions.
    • inner - INNER JOIN
    • fullOuter - FULL OUTER
    • rightOuter - RIGHT OUTER JOIN
    • leftOuter - LEFT OUTER JOIN
    • full - FULL JOIN
    • right - RIGHT JOIN
    • left - LEFT JOIN
  • Unconditioned join types that do not accept join conditions
    • natural - NATURAL JOIN
    • naturalLeft - NATURAL LEFT JOIN
    • naturalRight - NATURAL RIGHT JOIN
    • naturalFull - NATURA FULLL JOIN
    • cross - CROSS JOIN

Features:

Features that a particular patio.Dataset supports are shown in the example below. If you wish to implement an adapter please override these values depending on the database that you are developing the adapter for.

         var ds = DB.from("test");

         //The default values returned

         //Whether this dataset quotes identifiers.
         //Whether this dataset quotes identifiers.
         ds.quoteIdentifiers //=>true

         //Whether this dataset will provide accurate number of rows matched for
         //delete and update statements.  Accurate in this case is the number of
         //rows matched by the dataset's filter.
         ds.providesAccurateRowsMatched; //=>true

         //Times Whether the dataset requires SQL standard datetimes (false by default,
         // as most allow strings with ISO 8601 format).
         ds.requiresSqlStandardDate; //=>false

         //Whether the dataset supports common table expressions (the WITH clause).
         ds.supportsCte; //=>true

         //Whether the dataset supports the DISTINCT ON clause, false by default.
         ds.supportsDistinctOn; //=>false

         //Whether the dataset supports the INTERSECT and EXCEPT compound operations, true by default.
         ds.supportsIntersectExcept; //=>true

         //Whether the dataset supports the INTERSECT ALL and EXCEPT ALL compound operations, true by default
         ds.supportsIntersectExceptAll; //=>true

         //Whether the dataset supports the IS TRUE syntax.
         ds.supportsIsTrue; //=>true

         //Whether the dataset supports the JOIN table USING (column1, ...) syntax.
         ds.supportsJoinUsing; //=>true

         //Whether modifying joined datasets is supported.
         ds.supportsModifyingJoin; //=>false

         //Whether the IN/NOT IN operators support multiple columns when an
         ds.supportsMultipleColumnIn; //=>true

         //Whether the dataset supports timezones in literal timestamps
         ds.supportsTimestampTimezone; //=>false

         //Whether the dataset supports fractional seconds in literal timestamps
         ds.supportsTimestampUsecs; //=>true

         //Whether the dataset supports window functions.
         ds.supportsWindowFunctions; //=>false
      

Actions

Each dataset does not actually send any query to the database until an action method has been called upon it(with the exception of patio.Dataset#graph because columns from the other table might need retrived in order to set up the graph). Each action returns a comb.Promise that will be resolved with the result or errback, it is important that you account for errors otherwise it can be difficult to track down issues. The list of action methods is:

• patio.Dataset#all
• patio.Dataset#one
• patio.Dataset#avg
• patio.Dataset#count
• patio.Dataset#columns
• patio.Dataset#remove
• patio.Dataset#forEach
• patio.Dataset#empty
• patio.Dataset#first
• patio.Dataset#get
• patio.Dataset#import
• patio.Dataset#insert
• patio.Dataset#save
• patio.Dataset#insertMultiple
• patio.Dataset#saveMultiple
• patio.Dataset#interval
• patio.Dataset#last
• patio.Dataset#map
• patio.Dataset#max
• patio.Dataset#min
• patio.Dataset#multiInsert
• patio.Dataset#range
• patio.Dataset#selectHash
• patio.Dataset#selectMap
• patio.Dataset#selectOrderMap
• patio.Dataset#set
• patio.Dataset#singleRecord
• patio.Dataset#singleValue
• patio.Dataset#sum
• patio.Dataset#toCsv
• patio.Dataset#toHash
• patio.Dataset#truncate
• patio.Dataset#update

 DB.from("table").columns.chain(function(columns){
       // => ["id", "name"]
  });

   DB.from("table").firstSourceAlias;   //=> "table"    DB.from("table___t").firstSourceAlias;   //=> "t" 

   DB.from("table").firstSourceTable;         //=> "table"   DB.from("table___t").firstSourceTable;         //=> "t" 

     DB.from("items").isSimpleSelectAll; //=> true     DB.from("items").filter({a : 1}).isSimpleSelectAll; //=> false 

Constructor

function (db,opts){
   this._super(arguments);
   this.db = db;
   this.__opts = {};
   this.__rowCb = null;
   if (db) {
       this.__quoteIdentifiers = db.quoteIdentifiers;
       this.__identifierInputMethod = db.identifierInputMethod;
       this.__identifierOutputMethod = db.identifierOutputMethod;
   }
           
}
            

__aggregateDataset Function Private

function (){
   return this._optionsOverlap(this._static.COUNT_FROM_SELF_OPTS) ? this.fromSelf() : this.unordered();
           
}
    

__argumentList Function Private

Do a simple join of the arguments (which should be strings) separated by commas

• args :

function (args){
   return args.join(this._static.COMMA_SEPARATOR);
           
}
    

__arrayToConditionSpecifier Function Private

• arr :

  array of elements to make a condition specifier out of

• defaultOp :

  the value to assign a value if one is not provided.

• [[]]

  an array of two element arrays.

function (arr,defaultOp){
   var ret = [];
   arr.forEach(function (a) {
       if (isString(a)) {
           a = this.stringToIdentifier(a);
       }
       if (isInstanceOf(a, Identifier)) {
           ret.push([a, defaultOp]);
       } else if (isHash(a)) {
           ret = ret.concat(array.toArray(a));
       } else {
           throw new QueryError("Invalid condition specifier " + a);
       }
   }, this);
   return ret;
           
}
    

__asSql Function Private

SQL fragment for specifying an alias. expression should already be literalized.

• expression :
• alias :

function (expression,alias){
   return string.format("%s AS %s", expression, this.quoteIdentifier(alias));
           
}
    

__columnList Function Private

Converts an array of column names into a comma seperated string of column names. If the array is empty, a wildcard (*) is returned.

• columns :

function (columns){
   return (!columns || columns.length === 0) ? this._static.WILDCARD : this.__expressionList(columns);
           
}
    

__createBetweenExpression Function Private

ds.__createBetweenExpression({x : [1,2]}) => //=> WHERE ((x >= 1) AND (x <= 10))
ds.__createBetweenExpression({x : [1,2]}, true) => //=> WHERE ((x < 1) OR (x > 10))
        

• obj :

  object where the keys are columns and the values are two element arrays.

• invert? :

  if set to true it inverts the between to make it not between the two values

• patio.sql.BooleanExpression

  a boolean expression containing the between expression.

function (obj,invert){
   var pairs = [];
   for (var i in obj) {
       var v = obj[i];
       if (isArray(v) && v.length) {
           var ident = this.stringToIdentifier(i);
           pairs.push(new BooleanExpression("AND", new BooleanExpression("gte", ident, v[0]), new BooleanExpression("lte", ident, v[1])));
       } else {
           throw new QueryError("Between requires an array for the value");
       }
   }
   var ret = pairs.length === 1 ? pairs[0] : BooleanExpression.fromArgs(["AND"].concat(pairs));
   return invert ? BooleanExpression.invert(ret) : ret;
           
}
    

__createBoolExpression Function Private

Creates a boolean expression that each key is compared to its value using the provided operator.

ds.__createBoolExpression("gt", {x : 1, y:2, z : 5}) //=> WHERE ((x > 1) AND (y > 2) AND (z > 5))
ds.__createBoolExpression("gt", [[x, 1], [y,2], [z, 5]) //=> WHERE ((x > 1) AND (y > 2) AND (z > 5))
ds.__createBoolExpression("lt", {x : 1, y:2, z : 5}) //=> WHERE ((x < 1) AND (y < 2) AND (z < 5))
ds.__createBoolExpression("lt", [[x, 1], [y,2], [z, 5]) //=> WHERE ((x < 1) AND (y < 2) AND (z < 5))
        

• op :

  valid boolean expression operator to capare each K,V pair with

• obj :

  object or two dimensional array containing key value pairs

• patio.sql.BooleanExpression

  boolean expression joined by a AND of each key value pair compared by the op

function (op,obj){
   var pairs = [];
   if (Expression.isConditionSpecifier(obj)) {
       if (isHash(obj)) {
           obj = array.toArray(obj);
       }
       obj.forEach(function (pair) {
           pairs.push(new BooleanExpression(op, new Identifier(pair[0]), pair[1]));
       });
   }
   return pairs.length === 1 ? pairs[0] : BooleanExpression.fromArgs(["AND"].concat(pairs));
           
}
    

__defaultServerOpts Function Private

• opts :

function (opts){
   return merge({server: this.__opts.server || "default"}, opts || {});
           
}
    

__expressionList Function Private

Converts an array of expressions into a comma separated string of expressions.

• columns :

function (columns){
   return columns.map(this.literal, this).join(this._static.COMMA_SEPARATOR);
           
}
    

__hashIdentifierToName Function Private

• identifier :

  identifier to resolve to a string.

• String

  the string version of the identifier.

function (identifier){
   return isString(identifier) ? this.__hashIdentifierToName(this.stringToIdentifier(identifier)) :
       isInstanceOf(identifier, Identifier) ? identifier.value :
           isInstanceOf(identifier, QualifiedIdentifier) ? identifier.column :
               isInstanceOf(identifier, AliasedExpression) ? identifier.alias : identifier;
           
}
    

__tableRef Function Private

• t :

• SQL fragment specifying a table name.

function (t){
   return isString(t) ? this._quotedIdentifier(t) : this.literal(t);
           
}
    

_addGroupedCondition Function Private

DB.from("items").filter({id, [1,2,3]})._addGroupedCondition("AND", "OR", [{price: {lt : 0}}, {price: {gt: 10}]).sql;
     //=> SELECT
              *
          FROM
              items
          WHERE
              ((id IN (1, 2, 3)) AND ((price < 0) OR (price > 10)))

DB.from("items")._addGroupedCondition("AND", "OR", [{price: {lt : 0}}, {price: {gt: 10}]).sql;
     //=> SELECT
              *
          FROM
              items
          WHERE
              ((price < 0) OR (price > 10))
        

• addedByBool :
• groupedByBool :
• If String :

  there is an existing where/having clause, this arg will join the new condition group to it. "AND" if undef. Should be "AND" or "OR"

• The String :

  conditions will be joined this. Same expectations as the first param.

• See :

  patio.Dataset#filter.

• patio.Dataset

  a cloned dataset with the condition group added to the WHERE/HAVING clause.

function (addedByBool,groupedByBool){
   groupedByBool = isUndefined(groupedByBool) ? "AND" : groupedByBool;
   var tOpts = this.__opts,
       clause = (tOpts.having ? "having" : "where"),
       clauseObj = tOpts[clause];
   var args = argsToArray(arguments, 2);
   args = args.length === 1 ? args[0] : args;
   var opts = {};
   if (clauseObj) {
       addedByBool = isUndefined(addedByBool) ? "AND" : addedByBool;
       opts[clause] = new BooleanExpression(addedByBool, clauseObj, this._filterExpr(args, null, groupedByBool));
   } else {
       opts[clause] = this._filterExpr(args, null, groupedByBool);
   }
   return this.mergeOptions(opts);
           
}
    

_compoundFromSelf Function Private

Return a [@link patio.sql._Query#fromSelf} dataset if an order or limit is specified, so it works as expected with UNION, EXCEPT, and INTERSECT clauses.

function (){
   var opts = this.__opts;
   return (opts["limit"] || opts["order"]) ? this.fromSelf() : this;
           
}
    

_datasetAlias Function Private

The alias to use for datasets, takes a number to make sure the name is unique.

• number :

function (number){
   return this._static.DATASET_ALIAS_BASE_NAME + number;
           
}
    

_deleteFromSql Function Private

function (){
   return this._selectFromSql();
           
}
    

_deleteOrderSql Function Private

function (){
   return this._selectOrderSql();
           
}
    

_deleteWhereSql Function Private

function (){
   return this._selectWhereSql();
           
}
    

_filter Function Private

• clause :

function (clause){
   var cond = argsToArray(arguments).slice(1), cb;
   if (cond.length && isFunction(cond[cond.length - 1])) {
       cb = cond.pop();
   }
   cond = cond.length === 1 ? cond[0] : cond;
   if ((cond == null || cond === undefined || cond === "") || (isArray(cond) && cond.length === 0 && !cb) || (isObject(cond) && isEmpty(cond) && !cb)) {
       return this.mergeOptions();
   } else {
       cond = this._filterExpr(cond, cb);
       var cl = this.__opts[clause];
       cl && (cond = new BooleanExpression("AND", cl, cond));
       var opts = {};
       opts[clause] = cond;
       return this.mergeOptions(opts);
   }
           
}
    

_filterExpr Function Private

• expr :
• cb :
• joinCond :

function (expr,cb,joinCond){
   expr = (isUndefined(expr) || isNull(expr) || (isArray(expr) && !expr.length)) ? null : expr;
   if (expr && cb) {
       return new BooleanExpression(joinCond || "AND", this._filterExpr(expr, null, joinCond), this._filterExpr(cb, null, joinCond));
   } else if (cb) {
       expr = cb;
   }
   if (isInstanceOf(expr, Expression)) {
       if (isInstanceOf(expr, NumericExpression, StringExpression)) {
           throw new QueryError("Invalid SQL Expression type : " + expr);
       }
       return expr;
   } else if (isArray(expr)) {
       if (expr.length) {
           var first = expr[0];
           if (isString(first)) {
               return new PlaceHolderLiteralString(first, expr.slice(1), true);
           } else if (Expression.isConditionSpecifier(expr)) {
               return BooleanExpression.fromValuePairs(expr, joinCond);
           } else {
               return BooleanExpression.fromArgs([joinCond || "AND"].concat(expr.map(function (e) {
                   return this._filterExpr(e, null, "AND");
               }, this)));
           }
       }
   } else if (isFunction(expr)) {
       return this._filterExpr(expr.call(sql, sql), null, joinCond);
   } else if (isBoolean(expr)) {
       return new BooleanExpression("NOOP", expr);
   } else if (isString(expr)) {
       return this.stringToIdentifier(expr);
   } else if (isInstanceOf(expr, LiteralString)) {
       return new LiteralString("(" + expr + ")");
   } else if (isHash(expr)) {
       return BooleanExpression.fromValuePairs(expr, joinCond);
   } else {
       throw new QueryError("Invalid filter argument");
   }
           
}
    

_invertOrder Function Private

• order :

function (order){
   var ret = order;
   if (order) {
       ret = order.map(function (o) {
           if (isInstanceOf(o, OrderedExpression)) {
               return o.invert();
           } else {
               return new OrderedExpression(isString(o) ? new Identifier(o) : o);
           }
       }, this);
   }
   return ret;
           
}
    

_joinTypeSql Function Private

SQL fragment specifying a JOIN type, splits a camelCased join type and converts to uppercase/

• joinType :

function (joinType){
   return (joinType || "").replace(/([a-z]+)|([A-Z][a-z]+)/g, function (m) {
           return m.toUpperCase() + " ";
       }).trimRight() + " JOIN";
           
}
    

_literalArray Function Private

• v :

• SQL fragment for Array. Treats as an expression if an array of all two pairs, or as a SQL array otherwise.

function (v){
   return Expression.isConditionSpecifier(v) ? this._literalExpression(BooleanExpression.fromValuePairs(v)) : this._arraySql(v);
           
}
    

_literalBoolean Function Private

• b :

• SQL fragment for a boolean.

function (b){
   return b ? this._static.BOOL_TRUE : this._static.BOOL_FALSE;
           
}
    

_literalBuffer Function Private

• b :

• SQL fragment for Buffer, treated as an expression

function (b){
   return "X'" + b.toString("hex") + "'";
           
}
    

_literalDataset Function Private

• dataset :

• SQL fragment for Dataset. Does a subselect inside parantheses.

function (dataset){
   return string.format("(%s)", this._subselectSql(dataset));
           
}
    

_literalDate Function Private

• date :

• SQL fragment for Date, using the ISO8601 format.

function (date){
   return (this.requiresSqlStandardDateTimes ? "DATE '" : "'") + patio.dateToString(date) + "'";
           
}
    

_literalExpression Function Private

• v :

• SQL fragment for SQL::Expression, result depends on the specific type of expression.

function (v){
   return v.toString(this);
           
}
    

_literalHash Function Private

• v :

• SQL fragment for Hash, treated as an expression

function (v){
   return this._literalExpression(BooleanExpression.fromValuePairs(v));
           
}
    

_literalJson Function Private

• v :

• SQL fragment for json. Doubles ' by default.

function (v){
   throw new QueryError("Json not supported.");
           
}
    

_literalNull Function Private

• SQL fragment for null

function (){
   return this._static.NULL;
           
}
    

_literalNumber Function Private

• num :

• SQL fragment for a number.

function (num){
   var ret = "" + num;
   if (isNaN(num) || num === Infinity) {
       ret = string.format("'%s'", ret);
   }
   return ret;
           
}
    

_literalObject Function Private

• v :

• SQL fragment for Hash, treated as an expression

function (v){
   return this._literalExpression(BooleanExpression.fromValuePairs(v));
           
}
    

_literalOther Function Private

• v :

• SQL fragment for a type of object not handled by patio.dataset._Sql#literal. If object has a method sqlLiteral then it is called with this dataset as the first argument, otherwise raises an error. Classes implementing sqlLiteral should call a class-specific method on the dataset provided and should add that method to patio.dataset.Dataset, allowing for adapters to provide customized literalizations. If a database specific type is allowed, this should be overriden in a subclass.

function (v){
   if (isFunction(v.sqlLiteral)) {
       return v.sqlLiteral(this);
   } else {
       throw new QueryError(string.format("can't express %j as a SQL literal", [v]));
   }
           
}
    

_literalString Function Private

• v :

• SQL fragment for String. Doubles \ and ' by default.

function (v){
   var parts = this._splitString(v);
   var table = parts[0], column = parts[1], alias = parts[2], ret;
   if (!alias) {
       if (column && table) {
           ret = this._literalExpression(QualifiedIdentifier.fromArgs([table, column]));
       } else {
           ret = "'" + v.replace(/\\/g, "\\\\").replace(/'/g, "''") + "'";
       }
   } else {
       if (column && table) {
           ret = new AliasedExpression(QualifiedIdentifier.fromArgs([table, column]), alias);
       } else {
           ret = new AliasedExpression(new Identifier(column), alias);
       }
       ret = this.literal(ret);
   }
   return ret;
           
}
    

_literalTime Function Private

• v :

• SQL fragment for a timestamp, using the ISO8601 format.

function (v){
   return this.formatTimestamp(v, this._static.TIME_FORMAT);
           
}
    

_literalTimestamp Function Private

• v :

• SQL fragment for a timestamp, using the ISO8601 format.

function (v){
   return this.formatTimestamp(v, this._static.TIMESTAMP_FORMAT);
           
}
    

_literalYear Function Private

• o :

• SQL fragment for a year.

function (o){
   return patio.dateToString(o, this._static.YEAR_FORMAT);
           
}
    

_optionsOverlap Function Private

Return true if the dataset has a non-null value for any key in opts.

• opts :

  the options to compate this datasets options to

• Boolean

  true if the dataset has a non-null value for any key in opts.

function (opts){
   var o = [];
   for (var i in this.__opts) {
       if (!isUndefinedOrNull(this.__opts[i])) {
           o.push(i);
       }
   }
   return intersect(compact(o), opts).length !== 0;
           
}
    

_qualifiedExpression Function Private

Qualify the given expression to the given table.

• e :
• table :

  the table to qualify the expression to

• column patio.sql.Expression :

  the expression to qualify

function (e,table){
   var h, i, args;
   if (isString(e)) {
       //this should not be hit but here just for completeness
       return this.stringToIdentifier(e);
   } else if (isArray(e)) {
       return e.map(function (exp) {
           return this._qualifiedExpression(exp, table);
       }, this);
   } else if (isInstanceOf(e, Identifier)) {
       return new QualifiedIdentifier(table, e);
   } else if (isInstanceOf(e, OrderedExpression)) {
       return new OrderedExpression(this._qualifiedExpression(e.expression, table), e.descending,
           {nulls: e.nulls});
   } else if (isInstanceOf(e, AliasedExpression)) {
       return new AliasedExpression(this._qualifiedExpression(e.expression, table), e.alias);
   } else if (isInstanceOf(e, CaseExpression)) {
       args = [this._qualifiedExpression(e.conditions, table), this._qualifiedExpression(e.def, table)];
       if (e.hasExpression) {
           args.push(this._qualifiedExpression(e.expression, table));
       }
       return CaseExpression.fromArgs(args);
   } else if (isInstanceOf(e, Cast)) {
       return new Cast(this._qualifiedExpression(e.expr, table), e.type);
   } else if (isInstanceOf(e, SQLFunction)) {
       return SQLFunction.fromArgs([e.f].concat(this._qualifiedExpression(e.args, table)));
   } else if (isInstanceOf(e, ComplexExpression)) {
       return ComplexExpression.fromArgs([e.op].concat(this._qualifiedExpression(e.args, table)));
   } else if (isInstanceOf(e, SubScript)) {
       return new SubScript(this._qualifiedExpression(e.f, table), this._qualifiedExpression(e.sub, table));
   } else if (isInstanceOf(e, PlaceHolderLiteralString)) {
       args = [];
       var eArgs = e.args;
       if (isHash(eArgs)) {
           h = {};
           for (i in eArgs) {
               h[i] = this._qualifiedExpression(eArgs[i], table);
           }
           args = h;
       } else {
           args = this._qualifiedExpression(eArgs, table);
       }
       return new PlaceHolderLiteralString(e.str, args, e.parens);
   } else if (isHash(e)) {
       h = {};
       for (i in e) {
           h[this._qualifiedExpression(i, table) + ""] = this._qualifiedExpression(e[i], table);
       }
       return h;
   } else {
       return e;
   }
           
}
    

_selectColumnsSql Function Private

• the columns selected

function (){
   return " " + this.__columnList(this.__opts.select);
           
}
    

_selectCompoundsSql Function Private

• the EXCEPT, INTERSECT, or UNION clause. This uses a subselect for the compound datasets used, because using parantheses doesn't work on all databases.

function (){
   var opts = this.__opts, compounds = opts.compounds, ret = [];
   if (compounds) {
       compounds.forEach(function (c) {
           var type = c[0], dataset = c[1], all = c[2];
           ret.push(string.format(" %s%s %s", type.toUpperCase(), all ? " ALL" : "", this._subselectSql(dataset)));
       }, this);
   }
   return ret.join("");
           
}
    

_selectDistinctSql Function Private

• the DISTINCT clause.

function (){
   var distinct = this.__opts.distinct, ret = [];
   if (distinct) {
       ret.push(" DISTINCT");
       if (distinct.length) {
           ret.push(format(" ON (%s)", this.__expressionList(distinct)));
       }
   }
   return ret.join("");
           
}
    

_selectFromSql Function Private

• the sql to add the list of tables to select FROM

function (){
   var from = this.__opts.from;
   return from ? string.format(" %s%s", this._static.FROM, this._sourceList(from)) : "";
           
}
    

_selectGroupSql Function Private

• the GROUP BY clause

function (){
   var group = this.__opts.group;
   return group ? string.format(" GROUP BY %s", this.__expressionList(group)) : "";
           
}
    

_selectHavingSql Function Private

• the sql to add the filter criteria in the HAVING clause

function (){
   var having = this.__opts.having;
   return having ? string.format(" HAVING %s", this.literal(having)) : "";
           
}
    

_selectJoinSql Function Private

• the JOIN clause.

function (){
   var join = this.__opts.join, ret = [];
   if (join) {
       join.forEach(function (j) {
           ret.push(this.literal(j));
       }, this);
   }
   return ret.join("");
           
}
    

_selectLimitSql Function Private

• the LIMIT and OFFSET clauses.

function (){
   var ret = [], limit = this.__opts.limit, offset = this.__opts.offset;
   !isUndefined(limit) && !isNull(limit) && (ret.push(format(" LIMIT %s", this.literal(limit))));
   !isUndefined(offset) && !isNull(offset) && (ret.push(format(" OFFSET %s", this.literal(offset))));
   return ret.join("");
           
}
    

_selectLockSql Function Private

• SQL for different locking modes.

function (){
   var lock = this.__opts.lock, ret = [];
   if (lock) {
       if (lock === "update") {
           ret.push(this._static.FOR_UPDATE);
       } else {
           ret.push(" ", lock);
       }
   }
   return ret.join("");
           
}
    

_selectOrderSql Function Private

• the SQL ORDER BY clause fragment.

function (){
   var order = this.__opts.order;
   return order ? string.format(" ORDER BY %s", this.__expressionList(order)) : "";
           
}
    

_selectWhereSql Function Private

• the SQL WHERE clause fragment.

function (){
   var where = this.__opts.where;
   return where ? string.format(" WHERE %s", this.literal(where)) : "";
           
}
    

_selectWithSql Function Private

• sql :

• SQL WITH clause fragment.

function (sql){
   var wit = this.__opts["with"];
   if (wit && wit.length) {
       //sql.length = 0;
       var base = sql.join("");
       sql.length = 0;
       sql.push([this._selectWithSqlBase(), wit.map(function (w) {
           return [
               this.quoteIdentifier(w.name),
               (w.args ? ("(" + this.__argumentList(w.args) + ")") : ""),
               " AS ",
               this._literalDataset(w.dataset)
           ].join("");
       }, this).join(this._static.COMMA_SEPARATOR), base].join(" "));
   }
           
}
    

_selectWithSqlBase Function Private

• The base keyword to use for the SQL WITH clause

function (){
   return this._static.SQL_WITH;
           
}
    

_sourceList Function Private

Converts an array of source names into into a comma separated list.

• source :

function (source){
   if (!Array.isArray(source)) {
       source = [source];
   }
   if (!source || !source.length) {
       throw new QueryError("No source specified for the query");
   }
   return " " + source.map(
           function (s) {
               return this.__tableRef(s);
           }, this).join(this._static.COMMA_SEPARATOR);
           
}
    

_splitAlias Function Private

Splits a possible implicit alias, handling both patio.sql.AliasedExpressions and strings. Returns an array of two elements, with the first being the main expression, and the second being the alias. Alias may be null if it is a string that does not contain an alias {table}_{alias}.

• c :

function (c){
   var ret;
   if (isInstanceOf(c, AliasedExpression)) {
       ret = [c.expression, c.alias];
   } else if (isString(c)) {
       var parts = this._splitString(c), cTable = parts[0], column = parts[1], alias = parts[2];
       if (alias) {
           ret = [cTable ? new QualifiedIdentifier(cTable, column) : column, alias];
       } else {
           ret = [c, null];
       }
   } else {
       ret = [c, null];
   }
   return ret;
           
}
    

_splitString Function Private

Can either be a string or null.

//columns
 table__column___alias //=> table.column as alias
 table__column //=> table.column
 //tables
 schema__table___alias //=> schema.table as alias
 schema__table //=> schema.table

//name and alias
columnOrTable___alias //=> columnOrTable as alias
        

• s :

• String[]

  an array with the elements being:

  • For columns :[table, column, alias].
  • For tables : [schema, table, alias].

function (s){
   var ret, m;
   if ((m = s.match(this._static.COLUMN_REF_RE1)) !== null) {
       ret = m.slice(1);
   }
   else if ((m = s.match(this._static.COLUMN_REF_RE2)) !== null) {
       ret = [null, m[1], m[2]];
   }
   else if ((m = s.match(this._static.COLUMN_REF_RE3)) !== null) {
       ret = [m[1], m[2], null];
   }
   else {
       ret = [null, s, null];
   }
   return ret;
           
}
    

_staticSql Function Private

• sql :

• SQL to use if this dataset uses static SQL. Since static SQL can be a PlaceholderLiteralString in addition to a String, we literalize nonstrings.

function (sql){
   return isString(sql) ? sql : this.literal(sql);
           
}
    

_subselectSql Function Private

• ds :

• SQL fragment for a subselect using the given database's SQL.

function (ds){
   return ds.sql;
           
}
    

_toTableName Function Private

Returns a string that is the name of the table.

• name :

  the object to get the table name from.

• String

  the name of the table.

• patio.QueryError

  If the name is not a String patio.sql.Identifier, patio.sql.QualifiedIdentifier or patio.sql.AliasedExpression.

function (name){
   var ret;
   if (isString(name)) {
       var parts = this._splitString(name);
       var schema = parts[0], table = parts[1], alias = parts[2];
       ret = (schema || alias) ? alias || table : table;
   } else if (isInstanceOf(name, Identifier)) {
       ret = name.value;
   } else if (isInstanceOf(name, QualifiedIdentifier)) {
       ret = this._toTableName(name.column);
   } else if (isInstanceOf(name, AliasedExpression)) {
       ret = this.__toAliasedTableName(name.alias);
   } else {
       throw new QueryError("Invalid object to retrieve the table name from");
   }
   return ret;
           
}
    

_unaliasedIdentifier Function Private

Return the unaliased part of the identifier. Handles both implicit aliases in strings, as well as patio.sql.AliasedExpressions. Other objects are returned as is.

• c :
• tableAlias String|patio.sql.AliasedExpression|* :

  the object to un alias

• patio.sql.QualifiedIdentifier|String|*

  the unaliased portion of the identifier

function (c){
   if (isString(c)) {
       var parts = this._splitString(c);
       var table = parts[0], column = parts[1];
       if (table) {
           return new QualifiedIdentifier(table, column);
       }
       return column;
   } else if (isInstanceOf(c, AliasedExpression)) {
       return c.expression;
   } else {
       return c;
   }
           
}
    

_updateOrderSql Function Private

function (){
   return this._selectOrderSql();
           
}
    

_updateSetSql Function Private

• The SQL fragment specifying the columns and values to SET.

function (){
   var values = this.__opts.values, defs = this.__opts.defaults, overrides = this.__opts.overrides;
   var st = [" SET "];
   if (isArray(values)) {
       var v = [], mergedDefsAndOverrides = false, length = values.length, ident, val;
       for (var i = 0; i < length; i++) {
           val = values[i];
           if (isHash(val)) {
               mergedDefsAndOverrides = true;
               val = merge({}, defs || {}, val);
               val = merge({}, val, overrides || {});
               for (var j in val) {
                   ident = this.stringToIdentifier(j);
                   v.push(this.quoteIdentifier(ident) + " = " + this.literal(val[j]));
               }
           } else if (isInstanceOf(val, Expression)) {
               v.push(this._literalExpression(val).replace(/^\(|\)$/g, ""));
           } else {
               v.push(val);
           }
       }
       if (!mergedDefsAndOverrides) {
           val = merge({}, defs || {});
           val = merge({}, val, overrides || {});
           for (i in val) {
               ident = this.stringToIdentifier(i);
               v.push(this.quoteIdentifier(ident) + " = " + this.literal(val[i]));
           }
       }
       st.push(v.join(this._static.COMMA_SEPARATOR));
   } else {
       st.push(values);
   }
   return st.join("");
           
}
    

_updateTableSql Function Private

• sql :

• SQL fragment specifying the tables to delete from. Includes join table if modifying joins is allowed.

function (sql){
   var ret = [this._sourceList(this.__opts.from)];
   if (this.supportsModifyingJoins) {
       ret.push(this._selectJoinSql());
   }
   return ret.join("");
           
}
    

_updateWhereSql Function Private

function (){
   return this._selectWhereSql();
           
}
    

addGraphAliases Function Public

Adds the given graph aliases to the list of graph aliases to use, unlike patio.Dataset#setGraphAliases, which replaces the list (the equivalent of patio.Dataset#selectMore when graphing). See patio.Dataset#setGraphAliases.

var DB = patio.defaultDatabase;
 // SELECT ..., table.column AS someAlias
  DB.from("table").addGraphAliases({someAlias : ["table", "column"]);
  //returns from graphing
  // => {table : {column : someAlias_value, ...}, ...}
        

• graphAliases :

  the graph aliases to use. Where key is the alias name and the value is an array where arr[0] = 'tableName' arr[1] = "colName'.

• patio.Dataset

  deep copy of the original dataset with the added graphAliases.

function (graphAliases){
   var ds = this.selectMore.apply(this, this.__graphAliasColumns(graphAliases));
   ds.__opts.graphAliases = merge((ds.__opts.graphAliases || (ds.__opts.graph ? ds.__opts.graph.columnAliases : {}) || {}), graphAliases);
   return ds;
           
}
    

aliasedExpressionSql Function Private

• ae :

function (ae){
   return this.__asSql(this.literal(ae.expression), ae.alias);
           
}
    

all Function Public

Returns a Promise that is resolved with an array with all records in the dataset. If a block is given, the array is iterated over after all items have been loaded.

// SELECT * FROM table
DB.from("table").all().chain(function(res){
     //res === [{id : 1, ...}, {id : 2, ...}, ...];
});
// Iterate over all rows in the table
var myArr = [];
var rowPromise = DB.from("table").all(function(row){ myArr.push(row);});
rowPromise.chain(function(rows){
   //=> rows == myArr;
});
        

• block :

  a block to be called with each item. The return value of the block is ignored.

• cb? :

  a block to invoke when the action is done

• comb.Promise

  a promise that is resolved with an array of rows.

function (block,cb){
   var self = this;
   var ret = asyncArray(this.forEach().chain(function (records) {
       return self.postLoad(records);
   }));
   if (block) {
       ret = ret.forEach(block);
   }
   return ret.classic(cb).promise();
           
}
    

alwaysQualify Function Public

Same as patio.Dataset@qualifyTo except that it forces qualification on methods called after it has been called.

//qualfyTo would generate
DB.from("items").qualifyTo("i").filter({id : 1});
     //=> SELECT i.* FROM items WHERE (id = 1)

//alwaysQualify qualifies filter also.
DB.from("items").alwaysQualify("i").filter({id : 1});
     //=> SELECT i.* FROM items WHERE (i.id = 1)
        

• [table= this.firstSourceAlias] :

  the table to qualify to.

• patio.Dataset

  a cloned dataset that will always qualify.

function (table){
   return this.mergeOptions({alwaysQualify: table || this.firstSourceAlias});
           
}
    

and Function Public

Adds a further filter to an existing filter using AND. This method is identical to patio.Dataset#filter except it expects an existing filter.

For parameter types see patio.Dataset#filter.

DB.from("table").filter("a").and("b").sql;
     //=>SELECT * FROM table WHERE a AND b
        

• patio.Dataset

  a cloned dataset with the condition added to the WHERE/HAVING clause added.

• patio.QueryError

  If no WHERE?HAVING clause exists.

function (){
   var tOpts = this.__opts, clauseObj = tOpts[tOpts.having ? "having" : "where"];
   if (clauseObj) {
       return this.filter.apply(this, arguments);
   } else {
       throw new QueryError("No existing filter found");
   }
           
}
    

andGroupedAnd Function Public

Adds a group of ANDed conditions wrapped in parens to an existing where/having clause by an AND. If there isn't yet a clause, a where clause is created with the ANDed conditions

For parameter types see patio.Dataset#filter.

DB.from("items").filter({id, [1,2,3]}).andGroupedAnd([{price: {gt : 0}}, {price: {lt: 10}]).sql;
     //=> SELECT
              *
          FROM
              items
          WHERE
              ((id IN (1, 2, 3)) AND ((price > 0) AND (price < 10)))

DB.from("items").andGroupedAnd([{price: {gt : 0}}, {price: {lt: 10}]).sql;
     //=> SELECT
              *
          FROM
              items
          WHERE
              ((price > 0) AND (price < 10))
        

• filterExp :

• patio.Dataset

  a cloned dataset with the condition 'and group' added to the WHERE/HAVING clause.

function (filterExp){
   return this._addGroupedCondition("AND", "AND", filterExp);
           
}
    

andGroupedOr Function Public

Adds a group of ORed conditions wrapped in parens, connected to an existing where/having clause by an AND. If the where/having clause doesn't yet exist, a where clause is created with the ORed group.

For parameter types see patio.Dataset#filter.

DB.from("items").filter({id, [1,2,3]}).andGroupedOr([{price: {lt : 0}}, {price: {gt: 10}]).sql;
     //=> SELECT
              *
          FROM
              items
          WHERE
              ((id IN (1, 2, 3)) AND ((price < 0) OR (price > 10)))

DB.from("items").andGroupedOr([{price: {lt : 0}}, {price: {gt: 10}]).sql;
     //=> SELECT
              *
          FROM
              items
          WHERE
              ((price < 0) OR (price > 10))

DB.from("items").filter({x:1}).andGroupedOr([{a:1, b:2}, {c:3, d:4}]).sql;
     //=> SELECT
              *
          FROM
              items
          WHERE
              ((x = 1) AND (((a = 1) AND (b = 2)) OR ((c = 3) AND (d = 4)))
        

• filterExp :

• patio.Dataset

  a cloned dataset with the condition 'or group' added to the WHERE/HAVING clause.

function (filterExp){
   return this._addGroupedCondition("AND", "OR", filterExp);
           
}
    

avg Function Public

Returns a promise that is resolved with the average value for the given column.

// SELECT avg(number) FROM table LIMIT 1
DB.from("table").avg("number").chain(function(avg){
     // avg === 3
});
        

• column :

  the column to average

• cb? :

  the callback to invoke when the action is done.

• comb.Promise

  a promise that is resolved with the average value of the column.

function (column,cb){
   return this.__aggregateDataset().get(sql.avg(this.stringToIdentifier(column)), cb);
           
}
    

between Function Public

Returns a cloned dataset with a between clause added to the where clause.

ds.notBetween({x:[1, 2]}).sql;
         //=> SELECT * FROM test WHERE ((x >= 1) OR (x <= 2))

 ds.find({x:{notBetween:[1, 2]}}).sql;
         //=> SELECT * FROM test WHERE ((x >= 1) OR (x <= 2))
        

• obj :

  object where the key is the column and the value is an array where the first element is the item to be greater than or equal to than and the second item is less than or equal to than.

• patio.Dataset

  a cloned dataset with a between clause added to the where clause.

function (obj){
   return this.filter(this.__createBetweenExpression(obj));
           
}
    

booleanConstantSql Function Private

• constant :

function (constant){
   return this.literal(constant);
           
}
    

caseExpressionSql Function Private

• ce :

function (ce){
   var sql = ['(CASE '];
   if (ce.expression) {
       sql.push(this.literal(ce.expression), " ");
   }
   var conds = ce.conditions;
   if (isArray(conds)) {
       conds.forEach(function (cond) {
           sql.push(format("WHEN %s THEN %s", this.literal(cond[0]), this.literal(cond[1])));
       }, this);
   } else if (isHash(conds)) {
       for (var i in conds) {
           sql.push(format("WHEN %s THEN %s", this.literal(i), this.literal(conds[i])));
       }
   }
   return format("%s ELSE %s END)", sql.join(""), this.literal(ce.def));
           
}
    

castSql Function Private

• expr :
• type :

function (expr,type){
   return string.format("CAST(%s AS %s)", this.literal(expr), this.db.castTypeLiteral(type));
           
}
    

columnAllSql Function Private

• ca :

function (ca){
   return string.format("%s.*", this.quoteSchemaTable(ca.table));
           
}
    

complexExpressionSql Function Private

• op :
• args :

function (op,args){
   var newOp;
   var isOperators = this._static.IS_OPERATORS, isLiterals = this._static.IS_LITERALS, l;
   if ((newOp = isOperators[op]) != null) {
       var r = args[1], v = isNull(r) ? isLiterals.NULL : isLiterals[r];
       if (r == null || this.supportsIsTrue) {
           if (isUndefined(v)) {
               throw new QueryError(string.format("Invalid argument('%s') used for IS operator", r));
           }
           l = args[0];
           return string.format("(%s %s %s)", isString(l) ? l : this.literal(l), newOp, v);
       } else if (op === "IS") {
           return this.complexExpressionSql("EQ", args);
       } else {
           return this.complexExpressionSql("OR",
               [BooleanExpression.fromArgs(["NEQ"].concat(args)), new BooleanExpression("IS", args[0],
                   null)]);
       }
   } else if (["IN", "NOTIN"].indexOf(op) !== -1) {
       var cols = args[0], vals = args[1], colArray = isArray(cols), valArray = false, emptyValArray = false;
       if (isArray(vals)) {
           valArray = true;
           emptyValArray = vals.length === 0;
       }
       if (colArray) {
           if (emptyValArray) {
               if (op === "IN") {
                   return this.literal(BooleanExpression.fromValuePairs(cols.map(function (x) {
                       return [x, x];
                   }), "AND", true));
               } else {
                   return this.literal({1: 1});
               }
           } else if (!this.supportsMultipleColumnIn) {
               if (valArray) {
                   var expr = BooleanExpression.fromArgs(["OR"].concat(vals.map(function (vs) {
                       return BooleanExpression.fromValuePairs(array.zip(cols, vs));
                   })));
                   return this.literal(op === "IN" ? expr : expr.invert());
               }
           } else {
               //If the columns and values are both arrays, use _arraySql instead of
               //literal so that if values is an array of two element arrays, it
               //will be treated as a value list instead of a condition specifier.
               return format("(%s %s %s)", isString(cols) ? cols : this.literal(cols),
                   ComplexExpression.IN_OPERATORS[op],
                   valArray ? this._arraySql(vals) : this.literal(vals));
           }
       }
       else {
           if (emptyValArray) {
               if (op === "IN") {
                   return this.literal(BooleanExpression.fromValuePairs([
                       [cols, cols]
                   ], "AND", true));
               } else {
                   return this.literal({1: 1});
               }
           } else {
               return format("(%s %s %s)", isString(cols) ? cols : this.literal(cols),
                   ComplexExpression.IN_OPERATORS[op], this.literal(vals));
           }
       }
   } else if ((newOp = this._static.TWO_ARITY_OPERATORS[op]) != null) {
       l = args[0];
       return format("(%s %s %s)", isString(l) ? l : this.literal(l), newOp,
           this.literal(args[1]));
   } else if ((newOp = this._static.N_ARITY_OPERATORS[op]) != null) {
       return string.format("(%s)", args.map(this.literal, this).join(" " + newOp + " "));
   } else if (op === "NOT") {
       return string.format("NOT %s", this.literal(args[0]));
   } else if (op === "NOOP") {
       return this.literal(args[0]);
   } else {
       throw new QueryError("Invalid operator " + op);
   }
           
}
    

compoundClone Function Public

Add the dataset to the list of compounds

• type :

  the type of compound (i.e. "union", "intersect")

• dataset :

  the dataset to add to

• options :
• Object? :

  [options={}] compound option to use (i.e {all : true})

• patio.Dataset

  ds with the dataset added to the compounds.

function (type,dataset,options){
   var ds = this._compoundFromSelf().mergeOptions({compounds: (array.toArray(this.__opts.compounds || [])).concat([
       [type, dataset._compoundFromSelf(), options.all]
   ])});
   return options.fromSelf === false ? ds : ds.fromSelf(options);
           
}
    

constantSql Function Private

• constant :

function (constant){
   return "" + constant;
           
}
    

count Function Public

Returns a promise that is resolved with the number of records in the dataset.

// SELECT COUNT(*) AS count FROM table LIMIT 1
DB.from("table").count().chain(function(count){
    //count === 3;
});
        

• cb? :

  the callback to invoke when the action is done.

• comb.Promise

  a promise that is resolved with the the number of records in the dataset.

function (cb){
   return this.__aggregateDataset().get(sql.COUNT(sql.literal("*")).as("count")).chain(function (res) {
       return parseInt(res, 10);
   }).classic(cb);
           
}
    

distinct Function Public

Returns a copy of the dataset with the SQL DISTINCT clause. The DISTINCT clause is used to remove duplicate rows from the output. If arguments are provided, uses a DISTINCT ON clause, in which case it will only be distinct on those columns, instead of all returned columns.

DB.from("items").distinct().sqll
     //=> SELECT DISTINCT * FROM items
DB.from("items").order("id").distinct("id").sql;
     //=> SELECT DISTINCT ON (id) * FROM items ORDER BY id
        

• args :

  variable number of arguments used to create the DISTINCT ON clause.

• patio.Dataset

  a cloned dataset with the DISTINCT/DISTINCT ON clause added.

• patio.QueryError

  If arguments are given and DISTINCT ON is not supported.

function (args){
   args = argsToArray(arguments);
   if (args.length && !this.supportsDistinctOn) {
       throw new QueryError("DISTICT ON is not supported");
   }
   args = args.map(function (a) {
       return isString(a) ? new Identifier(a) : a;
   });
   return this.mergeOptions({distinct: args});
           
}
    

eager Function Public

Allows the loading of another query and combining them in one patio command. Queries can be related or completely unrelated. All data comes back as JSON NOT Patio models.

DB.from('company').filter({name: 'Amazon'})
 .eager({
     // company from parent query is passed in and usable in the eager query.
     leader: (company) => DB.from('leader').filter({id: company.leaderId}).one()
 })
 // Load completely unrelated data.
 .eager({org: () => DB.from('organization').one() })
 .one()})

 { id: 1,
   name: 'Amazon.com',
   leader: {
     id: 1,
     name: 'Jeff'
   },
   org: {
     id: 1,
     name: 'Google Inc.'
   }
 }

Can do one to many loading for every item in the parent dataset.
Be careful doing this because it can lead to lots of extra queries.

DB.from('company').filter({state: 'IA'})
 .eager({invoices: (company) => DB.from('invoices').filter({companyId: company.id}).one() })
 .all()})

 [
  { id: 1,
   name: 'Principal',
   invoices: [
     { id: 1, amount: 200},
     { id: 2, amount: 300},
   ]
  },
  { id: 2,
   name: 'John Deere',
   invoices: [
     { id: 3, amount: 200},
     { id: 4, amount: 300},
   ]
  }
 ]
        

• includeDatasets :
• fromModel :

function (includeDatasets,fromModel){
   var ds = this.mergeOptions({}),
       originalRowCb = ds.rowCb;
   if(!ds.__opts._eagerAssoc) {
       ds.__opts._eagerAssoc = includeDatasets;
       ds.rowCb = function (topLevelResults) {
           function toObject(thing) {
               if (!thing) {
                   return comb.when(thing);
               }
               if (Array.isArray(thing)) {
                   return comb.when(thing.map(function(item) {
                       return toObject(item);
                   }));
               }
               if ('toObject' in thing) {
                   return comb.when(thing.toObject());
               }
               return comb.when(thing);
           }
           var eagerResults = {},
               whens = [];
               if (!originalRowCb) {
                   // pass through for when topLevelResults is already resolved
                   originalRowCb = function(r){return r;};
               }
               return comb.when(originalRowCb(topLevelResults)).chain(function(maybeModel) {
                   whens = Object.keys(ds.__opts._eagerAssoc).map(function(key) {
                       return ds.__opts._eagerAssoc[key](maybeModel).chain(function(result) {
                           return toObject(result).chain(function(res) {
                               eagerResults[key] = res;
                               return result;
                           });
                       });
                   });
                   return comb.when(whens).chain(function () {
                       return toObject(maybeModel).chain(function(json) {
                           // merge associations on to main data
                           return Object.assign(json, eagerResults);
                       });
                   });
               });
       };
   }
   return ds.mergeOptions({
       _eagerAssoc: Object.assign(ds.__opts._eagerAssoc, includeDatasets)
   });
           
}
    

eq Function Public

Returns a cloned dataset with an equal expression added to the WHERE clause.

DB.from("test").eq({x : 1});
         //=> SELECT * FROM test WHERE (x = 1)
 DB.from("test").eq({x : 1, y : 10});
         //=> SELECT * FROM test WHERE ((x = 1) AND (y = 10))
        

• obj :

  object used to create the equal expression

• patio.Dataset

  a cloned dataset with the equal expression added to the WHERE clause.

function (obj){
   return this.filter(this.__createBoolExpression("eq", obj));
           
}
    

except Function Public

Adds an EXCEPT clause using a second dataset object. An EXCEPT compound dataset returns all rows in the current dataset that are not in the given dataset.

DB.from("items").except(DB.from("other_items")).sql;
     //=> SELECT * FROM items EXCEPT SELECT * FROM other_items

DB.from("items").except(DB.from("other_items"),
                       {all : true, fromSelf : false}).sql;
     //=> SELECT * FROM items EXCEPT ALL SELECT * FROM other_items

DB.from("items").except(DB.from("other_items"),
                       {alias : "i"}).sql;
     //=>SELECT * FROM (SELECT * FROM items EXCEPT SELECT * FROM other_items) AS i
        

• dataset :

  the dataset to use to create the EXCEPT clause.

• opts? :

  options to use when creating the EXCEPT clause

• opt.alias? String|patio.sql.Identifier :

  Use the given value as the patio.Dataset#fromSelf alias.

• opts.all? Boolean :

  Set to true to use EXCEPT ALL instead of EXCEPT, so duplicate rows can occur

• opts.fromSelf? Boolean :

  Set to false to not wrap the returned dataset in a patio.Dataset#fromSelf, use with care.

• patio.Dataset

  a cloned dataset with the EXCEPT clause added.

• patio.QueryError

  if the operation is not supported.

function (dataset,opts){
   opts = isUndefined(opts) ? {} : opts;
   if (!isHash(opts)) {
       opts = {all: true};
   }
   if (!this.supportsIntersectExcept) {
       throw new QueryError("EXCEPT not supoorted");
   } else if (opts.hasOwnProperty("all") && !this.supportsIntersectExceptAll) {
       throw new QueryError("EXCEPT ALL not supported");
   }
   return this.compoundClone("except", dataset, opts);
           
}
    

exclude Function Public

Performs the inverse of patio.Dataset#filter. Note that if you have multiple filter conditions, this is not the same as a negation of all conditions. For argument types see patio.Dataset#filter

DB.from("items").exclude({category : "software").sql;
     //=> SELECT * FROM items WHERE (category != 'software')

DB.from("items").exclude({category : 'software', id : 3}).sql;
     //=> SELECT * FROM items WHERE ((category != 'software') OR (id != 3))
        

• patio.Dataset

  a cloned dataset with the excluded conditions applied to the HAVING/WHERE clause.

function (){
   var cond = argsToArray(arguments), tOpts = this.__opts;
   var clause = (tOpts["having"] ? "having" : "where"), clauseObj = tOpts[clause];
   cond = cond.length > 1 ? cond : cond[0];
   cond = this._filterExpr.call(this, cond);
   cond = BooleanExpression.invert(cond);
   if (clauseObj) {
       cond = new BooleanExpression("AND", clauseObj, cond);
   }
   var opts = {};
   opts[clause] = cond;
   return this.mergeOptions(opts);
           
}
    

execute Function Private

• sql :
• opts :

function (sql,opts){
   return this.db.execute(sql, merge({server: this.__opts.server || "readOnly"}, opts || {}));
           
}
    

executeDdl Function Private

• sql :
• opts :

function (sql,opts){
   return this.db.executeDdl(sql, this.__defaultServerOpts(opts || {}));
           
}
    

executeDui Function Private

• sql :
• opts :

function (sql,opts){
   return this.db.executeDui(sql, this.__defaultServerOpts(opts || {}));
           
}
    

executeInsert Function Private

• sql :
• opts :

function (sql,opts){
   return this.db.executeInsert(sql, this.__defaultServerOpts(opts || {}));
           
}
    

fetchRows Function Private

• sql :
• opts :

function (sql,opts){
   opts = opts || {};
   var ret;
   if (opts.stream) {
       ret = this.fetchStreamedRows(sql, opts);
   } else {
       ret = this.fetchPromisedRows(sql, opts);
   }
   return ret;
           
}
    

filter Function Public

Returns a copy of the dataset with the given conditions applied to it. If the query already has a HAVING clause, then the conditions are applied to the HAVING clause otherwise they are applied to the WHERE clause.

DB.from("items").filter({id : 3}).sql;
     //=> SELECT * FROM items WHERE (id = 3)

DB.from("items").filter('price < ?', 100)
     //=> SELECT * FROM items WHERE price < 100

DB.from("items").filter({id, [1,2,3]}).filter({id : {between : [0, 10]}}).sql;
     //=> SELECT
              *
          FROM
              items
          WHERE
              ((id IN (1, 2, 3)) AND ((id >= 0) AND (id <= 10)))

DB.from("items").filter('price < 100');
     //=> SELECT * FROM items WHERE price < 100

DB.from("items").filter("active").sql;
     //=> SELECT * FROM items WHERE active

DB.from("items").filter(function(){
     return this.price.lt(100);
});
     //=> SELECT * FROM items WHERE (price < 100)

//Multiple filter calls can be chained for scoping:
DB.from("items").filter(:category => 'software').filter{price < 100}
     //=> SELECT * FROM items WHERE ((category = 'software') AND (price < 100))
        

• args :

  filters to apply to the WHERE/HAVING clause. Description of each:

  • Hash - list of equality/inclusion expressions
  • Array - depends:
    • If first member is a string, assumes the rest of the arguments are parameters and interpolates them into the string.
    • If all members are arrays of length two, treats the same way as a hash, except it allows for duplicate keys to be specified.
    • Otherwise, treats each argument as a separate condition.
  • String - taken literally
  • patio.sql.Identifier - taken as a boolean column argument (e.g. WHERE active)
  • patio.sql.BooleanExpression - an existing condition expression, probably created using the patio.sql methods.
  • cb? :

    filter also takes a cb, which should return one of the above argument types, and is treated the same way. This block is called with an patio.sql object which can be used to dynaically create expression. For more details on the sql object see patio.sql

    NOTE:If both a cb and regular arguments are provided, they get ANDed together.

  Returns
  • patio.Dataset

    a cloned dataset with the filter arumgents applied to the WHERE/HAVING clause.

  Source

  function (args,cb){
     args = [this.__opts["having"] ? "having" : "where"].concat(argsToArray(arguments));
     return this._filter.apply(this, args);
             
  }
      

  find Function Public

  ---

  Defined dataset/query.js
  Source

  function (){
     var args = [this.__opts["having"] ? "having" : "where"].concat(argsToArray(arguments));
     return this._filter.apply(this, args);
             
  }
      

  first Function Public

  ---

  Defined dataset/actions.js

  If a integer argument is given, it is interpreted as a limit, and then returns all matching records up to that limit.

  If no arguments are passed, it returns the first matching record.

  If a function taking no arguments is passed in as the last parameter then it is assumed to be a filter block. If the a funciton is passed in that takes arguments then it is assumed to be a callback. You may also pass in both the second to last argument being a filter function, and the last being a callback.

  If any other type of argument(s) is passed, it is given to patio.Dataset#filter and the first matching record is returned. Examples:

  Example

  comb.executeInOrder(DB.from("table"), function(ds){
    // SELECT * FROM table LIMIT 1
    ds.first(); // => {id : 7}
  
    // SELECT * FROM table LIMIT 2
    ds.first(2); // => [{id : 6}, {id : 4}]
  
    // SELECT * FROM table WHERE (id = 2) LIMIT 1
    ds.first({id : 2}) // => {id : 2}
  
  
   // SELECT * FROM table WHERE (id = 3) LIMIT 1
    ds.first("id = 3"); // => {id : 3}
  
    // SELECT * FROM table WHERE (id = 4) LIMIT 1
    ds.first("id = ?", 4); // => {id : 4}
  
    // SELECT * FROM table WHERE (id > 2) LIMIT 1
    ds.first(function(){return this.id.gt(2);}); // => {id : 5}
  
  
    // SELECT * FROM table WHERE ((id > 4) AND (id < 6)) LIMIT 1
    ds.first("id > ?", 4, function(){
           return this.id.lt(6);
    }); // => {id : 5}
  
     // SELECT * FROM table WHERE (id < 2) LIMIT 2
    ds.first(2, function(){
           return this.id.lt(2)
    }); // => [{id:1}]
  });
          

  Arguments
  • args :

    varargs to be used to limit/filter the result set.

  Returns
  • comb.Promise

    a promise that is resolved with the either the first matching record. Or an array of items if a limit was provided as the first argument.

  Source

  function (args){
     args = comb(arguments).toArray();
     var cb,
         block = isFunction(args[args.length - 1]) ? args.pop() : null;
     if (block && block.length > 0) {
         cb = block;
         block = isFunction(args[args.length - 1]) ? args.pop() : null;
     }
     var ds = block ? this.filter(block) : this;
     if (!args.length) {
         return ds.singleRecord(cb);
     } else {
         args = (args.length === 1) ? args[0] : args;
         if (isNumber(args)) {
             return ds.limit(args).all(null, cb);
         } else {
             return ds.filter(args).singleRecord(cb);
         }
     }
             
  }
      

  forEach Function Public

  ---

  Defined dataset/actions.js

  Iterates over the records in the dataset as they are returned from the database adapter.

  Example

  // SELECT * FROM table
  DB.from("table").forEach(function(row){
       //....do something
  });
          

  Arguments
  • block? :

    the block to invoke for each row.

  • cb? :

    the callback to invoke when the action is done.

  Returns
  • comb.Promise

    a promise that is resolved when the action has completed.

  Source

  function (block,cb){
     var rowCb, ret;
     if (this.__opts.graph) {
         ret = this.graphEach(block);
     } else {
         ret = this.fetchRows(this.selectSql);
         if ((rowCb = this.rowCb)) {
             ret = ret.map(function (r) {
                 return rowCb(r);
             });
         }
         if (block) {
             ret = ret.forEach(block);
         }
     }
     return ret.classic(cb);
             
  }
      

  forUpdate Function Public

  ---

  Defined dataset/query.js
  Example

  DB.from("table").forUpdate()
           //=> SELECT * FROM table FOR UPDATE
          

  Returns
  • patio.Dataset

    a cloned dataset with a "update" lock style.

  Source

  function (){
     return this.lockStyle("update");
             
  }
      

  from Function Public

  ---

  Defined dataset/query.js

  Returns a copy of the dataset with the source changed. If no source is given, removes all tables. If multiple sources are given, it is the same as using a CROSS JOIN (cartesian product) between all tables.

  Example

  var dataset = DB.from("items");
  
  dataset.from().sql;
       //=> SELECT *
  
  dataset.from("blah").sql
       //=> SELECT * FROM blah
  
  dataset.from("blah", "foo")
       //=> SELECT * FROM blah, foo
  
  dataset.from({a:"b"}).sql;
       //=> SELECT * FROM a AS b
  
  dataset.from(dataset.from("a").group("b").as("c")).sql;
           //=> "SELECT * FROM (SELECT * FROM a GROUP BY b) AS c"
          

  Arguments
  • source? :

    tables to select from

  Returns
  • patio.Dataset

    a cloned dataset with the FROM clause overridden.

  Source

  function (source){
     source = argsToArray(arguments);
     var tableAliasNum = 0, sources = [];
     source.forEach(function (s) {
         if (isInstanceOf(s, Dataset)) {
             sources.push(new AliasedExpression(s, this._datasetAlias(++tableAliasNum)));
         } else if (isHash(s)) {
             for (var i in s) {
                 sources.push(new AliasedExpression(new Identifier(i), s[i]));
             }
         } else if (isString(s)) {
             sources.push(this.stringToIdentifier(s));
         } else {
             sources.push(s);
         }
     }, this);
     var o = {from: sources.length ? sources : null};
     if (tableAliasNum) {
         o.numDatasetSources = tableAliasNum;
     }
     return this.mergeOptions(o);
             
  }
      

  fromSelf Function Public

  ---

  Defined dataset/query.js

  Returns a dataset selecting from the current dataset. Supplying the alias option controls the alias of the result.

  Example

  ds = DB.from("items").order("name").select("id", "name")
       //=> SELECT id,name FROM items ORDER BY name
  
  ds.fromSelf().sql;
       //=> SELECT * FROM (SELECT id, name FROM items ORDER BY name) AS t1
  
  ds.fromSelf({alias : "foo"}).sql;
       //=> SELECT * FROM (SELECT id, name FROM items ORDER BY name) AS foo
          

  Arguments
  • opts? :

    options

  • opts.alias? String|patio.sql.Identifier :

    alias to use

  Returns
  • patio.Dataset

    a cloned dataset with the FROM clause set as the current dataset.

  Source

  function (opts){
     opts = isUndefined(opts) ? {} : opts;
     var fs = {};
     var nonSqlOptions = this._static.NON_SQL_OPTIONS;
     Object.keys(this.__opts).forEach(function (k) {
         if (nonSqlOptions.indexOf(k) === -1) {
             fs[k] = null;
         }
     });
     return this.mergeOptions(fs).from(opts["alias"] ? this.as(opts["alias"]) : this);
             
  }
      

  functionSql Function Private

  ---

  Defined dataset/sql.js
  Arguments
  • f :
  Source

  function (f){
     var args = f.args;
     return string.format("%s%s", f.f, args.length === 0 ? '()' : this.literal(args));
             
  }
      

  get Function Public

  ---

  Defined dataset/actions.js

  Return the column value for the first matching record in the dataset.

  Example

  // SELECT id FROM table LIMIT 1
   DB.from("table").get("id").chain(function(val){
    // val === 3
   });
  
  
  // SELECT sum(id) FROM table LIMIT 1
  ds.get(sql.sum("id")).chain(function(val){
       // val === 6;
  });
  
  // SELECT sum(id) FROM table LIMIT 1
  ds.get(function(){
       return this.sum("id");
  }).chain(function(val){
       // val === 6;
  });
          

  Arguments
  • column :

    the column to filter on can be anything that patio.Dataset#select accepts.

  • cb? :

    the callback to invoke when the action is done.

  Returns
  • comb.Promise

    a promise that will be resolved will the value requested.

  Source

  function (column,cb){
     return this.select(column).singleValue(cb);
             
  }
      

  graph Function Public

  ---

  Defined dataset/graph.js

  Allows you to join multiple datasets/tables and have the result set split into component tables.

  This differs from the usual usage of join, which returns the result set as a single hash.

  
  //CREATE TABLE artists (id INTEGER, name TEXT);
  //CREATE TABLE albums (id INTEGER, name TEXT, artist_id INTEGER);
  
  var DB = patio.defaultDatabase, ds = db.from("artists");
  ds.leftOuterJoin("albums", {artistId : "id"}).first
    //=> {id : albums.id, name : albums.name, artist_id : albums.artist_id}
  
  var p = comb.executeInOrder(ds, function(ds){
    var graphedDs = ds.graph("albums", {artist_id : "id"});
    return graphedDs.first();
  });
  p.chain(function(obj){
     //obj == {artists : {
                   id : artists.id,
                   name : artists.name
                   },
              albums : {
                   id : albums.id,
                   name : albums.name,
                   artist_id=>albums.artist_id
                   }
              }
  });
  

  Using a join such as leftOuterJoin, the attribute names that are shared between the tables are combined in the single return hash. You can get around that by using patio.Dataset#select with correct aliases for all of the columns, but it is simpler to use patio.Dataset#graph and have the result set split for you. In addition, patio.Dataset#graph respects any patio.Dataset#rowCb of the current dataset and the datasets you use with patio.Dataset#graph.

  If you are graphing a table and all columns for that table are null, this indicates that no matching rows existed in the table, so graph will return null. instead of a hash with all nil values:

    // Psuedo code there will be promises returned
    /// If the artist doesn't have any albums
  
    var DB = patio.defaultDatabase, ds = db.from("artists");
    var obj = ds.graph(:albums, :artist_id=>:id).first()
    //obj == { artists : {id : artists.id, name : artists.name}, albums : null};
  

  Arguments
  • dataset :

    This can be a string (representing a table), another patio.Dataset, or an object that has a dataset property that returns a string or dataset.

  • joinConditions :

    Any condition(s) allowed by patio.Dataset#joinTable.

  • options? :

    options to use when creating the graph

  • block :

    A function that is passed to patio.Dataset#joinTable.

  • options.fromSelfAlias? String|sql.LiteralString|sql.Identifier :

    The alias to use when the receiver is not a graphed dataset but it contains multiple FROM tables or a JOIN. In this case, the receiver is wrapped in a patio.Dataset#fromSelf before graphing, and this option determines the alias to use.

  • options.implicitQualifier? String|sql.LiteralString|sql.Identifier :

    The qualifier of implicit conditions, see patio.Dataset#joinTable.

  • String? :

    [options.joinType="leftOuter"] The type of join to use (passed to patio.Dataset#joinTable.).

  • String[]|sql.LiteralString[]|sql.Identifier[]|Boolean? :

    [options.select] An array of columns to select. When not used, selects all columns in the given dataset. When set to false, selects no columns and is like simply joining the tables, though graph keeps some metadata about the join that makes it important to use patio.Dataset#graph instead of patio.Dataset#joinTable

  • options.tableAlias? String|sql.LiteralString|sql.Identifier :

    The alias to use for the table. If not specified, doesn't alias the table. You will get an error if the the alias (or table) name is used more than once.

  Source

  function (dataset,joinConditions,options,block){
     var ret = new Promise();
     var args = argsToArray(arguments, 1);
     block = isFunction(args[args.length - 1]) ? args.pop() : null;
     joinConditions = args.shift() || null;
     options = args.shift() || {};
     // Allow the use of a model, dataset, or string as the first argument
     // Find the table name/dataset based on the argument
     dataset.hasOwnProperty("dataset") && (dataset = dataset.dataset);
     var tableAlias = options.tableAlias, table;
     if (isString(dataset)) {
         table = sql.identifier(dataset);
         dataset = this.db.from(dataset);
         isUndefinedOrNull(tableAlias) && (tableAlias = table);
     } else if (isInstanceOf(dataset, Dataset)) {
         if (dataset.isSimpleSelectAll) {
             table = dataset.__opts.from[0];
             isUndefinedOrNull(tableAlias) && (tableAlias = table);
         } else {
             table = dataset;
             isUndefinedOrNull(tableAlias) && (tableAlias = this._datasetAlias((this.__opts.numDatasetSources || 0) + 1));
         }
     } else {
         throw new QueryError("The dataset arg should be a string, dataset or model");
     }
     var aliases;
     // Only allow table aliases that haven't been used
     var thisOpts = this.__opts, thisOptsGraph = thisOpts.graph;
     if (isObject(thisOptsGraph) && isHash((aliases = thisOptsGraph.tableAliases)) && !isUndefinedOrNull(aliases[tableAlias.value])) {
         raiseAliasError(options);
     }
     // Use a from_self if this is already a joined table
     var ds = (!thisOptsGraph && (thisOpts.from.length > 1 || thisOpts.join)) ? this.fromSelf({alias: options.fromSelfAlias || this.firstSourceAlias}) : this;
     // Join the table early in order to avoid cloning the dataset twice
     ds = ds.joinTable(options.joinType || "leftOuter", table, joinConditions, {tableAlias: tableAlias, implicitQualifier: options.implicitQualifier}, block);
     var opts = ds.__opts;
     // Whether to include the table in the result set
     var addTable = isBoolean(options.select) ? options.select : true;
     // Whether to add the columns to the list of column aliases
     var addColumns = isUndefinedOrNull(opts.graphAliases);
     // Setup the initial graph data structure if it doesn't exist
     var graph;
     var populateGraphPromise;
     if (isUndefinedOrNull((graph = opts.graph))) {
         var master = this._toTableName(ds.firstSourceAlias);
         ("" + master === "" + tableAlias) && raiseAliasError(options);
         // Master hash storing all .graph related information
         graph = opts.graph = {};
         // Associates column aliases back to tables and columns
         var columnAliases = graph.columnAliases = {};
         // Associates table alias (the master is never aliased)
         var tableAliases = graph.tableAliases = {};
         tableAliases[master] = this;
         // All columns in the master table are never
         // aliased, but are not included if set_graph_aliases
         // has been used.
         if (addColumns) {
             var select = opts.select = [];
             populateGraphPromise = this.columns.chain(function (cols) {
                 cols.forEach(function (column) {
                     columnAliases[column] = [master, column];
                     select.push(new sql.QualifiedIdentifier(master, column));
                 });
                 return graph;
             });
         } else {
             populateGraphPromise = new Promise().callback(graph);
         }
     } else {
         populateGraphPromise = new Promise().callback(graph);
     }
     return populateGraphPromise.chain(function (graph) {
         var ret;
         // Add the table alias to the list of aliases
         // Even if it isn't been used in the result set,
         // we add a key for it with a nil value so we can check if it
         // is used more than once
         var tableAliases = graph.tableAliases;
         tableAliases[tableAlias] = addTable ? dataset : null;
         // Add the columns to the selection unless we are ignoring them
         if (addTable && addColumns) {
             var select = opts.select;
             var columnAliases = graph.columnAliases;
             // Which columns to add to the result set
             var dsColPromise;
             if (options.select) {
                 dsColPromise = new Promise().callback(options.select);
             } else {
                 dsColPromise = dataset.columns;
             }
             // If the column hasn't been used yet, don't alias it.
             // If it has been used, try tableColumn.
             ret = dsColPromise.chain(function (cols) {
                 cols.forEach(function (column) {
                     var colAlias, identifier;
                     if (columnAliases[column]) {
                         var columnAlias = format("%s_%s", [tableAlias, column]);
                         colAlias = columnAlias;
                         identifier = new sql.QualifiedIdentifier(tableAlias, column).as(columnAlias);
                     } else {
                         colAlias = column;
                         identifier = new sql.QualifiedIdentifier(tableAlias, column);
                     }
                     columnAliases[colAlias] = [tableAlias, column];
                     select.push(identifier);
                 });
                 return ds;
             });
         } else {
             ret = ds;
         }
         return ret;
     });
             
  }
      

  graphEach Function Public

  ---

  Defined dataset/graph.js

  Fetch the rows, split them into component table parts, transform and run the patio.Dataset#rowCb on each part (if applicable), and yield a hash of the parts.

  Arguments
  • cb :
  Source

  function (cb){
     // Reject tables with nil datasets, as they are excluded from
     // the result set
     var datasets = toArray(this.__opts.graph.tableAliases).filter(function (e) {
         return !isUndefinedOrNull(e[1]);
     });
     // Get just the list of table aliases into a local variable, for speed
     var tableAliases = datasets.map(function (e) {
         return e[0];
     });
     datasets = datasets.map(function (e) {
         return [e[0], e[1], e[1].rowCb];
     });
     // Use the manually set graph aliases, if any, otherwise
     // use the ones automatically created by .graph
     var columnAliases = this.__opts.graphAliases || this.__opts.graph.columnAliases;
     var ret = this.fetchRows(this.selectSql).map(function (r) {
         var graph = {};
         // Create the sub hashes, one per table
         tableAliases.forEach(function (ta) {
             graph[ta] = {};
         });
         // Split the result set based on the column aliases
         // If there are columns in the result set that are
         // not in column_aliases, they are ignored
         for (var colAlias in columnAliases) {
             var tc = columnAliases[colAlias];
             var ta = tc[0], column = tc[1];
             !graph[ta] && (graph[ta] = {});
             graph[ta][column] = r[colAlias];
         }
         datasets.forEach(function (d) {
             var ta = d[0], ds = d[1], dsCb = d[2];
             var g = graph[ta];
             if (!isEmpty(g) && Object.keys(g).some(function (x) {
                     return !isUndefinedOrNull(g[x]);
                 })) {
                 graph[ta] = dsCb ? dsCb(g) : g;
             } else {
                 graph[ta] = null;
             }
         });
         return graph;
     });
     if (cb) {
         ret.forEach(cb);
     }
     return ret;
             
  }
      

  grep Function Public

  ---

  Defined dataset/query.js

  Match any of the columns to any of the patterns. The terms can be strings (which use LIKE) or regular expressions (which are only supported on MySQL and PostgreSQL). Note that the total number of pattern matches will be columns[].length * terms[].length, which could cause performance issues.

  Example

  DB.from("items").grep("a", "%test%").sql;
       //=> SELECT * FROM items WHERE (a LIKE '%test%');
  
  DB.from("items").grep(["a", "b"], ["%test%" "foo"]).sql;
       //=> SELECT * FROM items WHERE ((a LIKE '%test%') OR (a LIKE 'foo') OR (b LIKE '%test%') OR (b LIKE 'foo'))
  
  DB.from("items").grep(['a', 'b'], ["%foo%", "%bar%"], {allPatterns : true}).sql;
       //=> SELECT * FROM a WHERE (((a LIKE '%foo%') OR (b LIKE '%foo%')) AND ((a LIKE '%bar%') OR (b LIKE '%bar%')))
  
  DB.from("items").grep(["a", "b"], ['%foo%", "%bar%", {allColumns : true})sql;
       //=> SELECT * FROM a WHERE (((a LIKE '%foo%') OR (a LIKE '%bar%')) AND ((b LIKE '%foo%') OR (b LIKE '%bar%')))
  
  DB.from("items").grep(["a", "b"], ["%foo%", "%bar%"], {allPatterns : true, allColumns : true}).sql;
       //=> SELECT * FROM a WHERE ((a LIKE '%foo%') AND (b LIKE '%foo%') AND (a LIKE '%bar%') AND (b LIKE '%bar%'))
          

  Arguments
  • columns :

    columns to search

  • patterns :

    patters to search with

  • opts? :

    options to use when searching. NOTE If both allColumns and allPatterns are true, all columns must match all patterns

  • opts.allColumns? Boolean :

    All columns must be matched to any of the given patterns.

  • opts.allPatterns? Boolean :

    All patterns must match at least one of the columns.

  • opts.caseInsensitive? Boolean :

    Use a case insensitive pattern match (the default is case sensitive if the database supports it).

  Returns
  • patio.Dataset

    a dataset with the LIKE clauses added

  Source

  function (columns,patterns,opts){
     opts = isUndefined(opts) ? {} : opts;
     var conds;
     if (opts.hasOwnProperty("allPatterns")) {
         conds = array.toArray(patterns).map(function (pat) {
             return BooleanExpression.fromArgs(
                 [(opts.allColumns ? "AND" : "OR")]
                     .concat(array.toArray(columns)
                         .map(function (c) {
                             return StringExpression.like(c, pat, opts);
                         })));
         });
         return this.filter(BooleanExpression.fromArgs([opts.allPatterns ? "AND" : "OR"].concat(conds)));
     } else {
         conds = array.toArray(columns)
             .map(function (c) {
                 return BooleanExpression.fromArgs(["OR"].concat(array.toArray(patterns).map(function (pat) {
                     return StringExpression.like(c, pat, opts);
                 })));
             });
         return this.filter(BooleanExpression.fromArgs([opts.allColumns ? "AND" : "OR"].concat(conds)));
     }
             
  }
      

  group Function Public

  ---

  Defined dataset/query.js

  Returns a copy of the dataset with the results grouped by the value of the given columns.

  Example

  DB.from("items").group("id")
           //=>SELECT * FROM items GROUP BY id
    DB.from("items").group("id", "name")
           //=> SELECT * FROM items GROUP BY id, name
          

  Arguments
  • columns :

    columns to group by.

  Returns
  • patio.Dataset

    a cloned dataset with the GROUP BY clause added.

  Source

  function (columns){
     columns = argsToArray(arguments);
     var self = this;
     return this.mergeOptions({group: (array.compact(columns).length === 0 ? null : columns.map(function (c) {
         return isString(c) ? self.stringToIdentifier(c) : c;
     }))});
             
  }
      

  groupAndCount Function Public

  ---

  Defined dataset/query.js

  Returns a dataset grouped by the given column with count by group. Column aliases may be supplied, and will be included in the select clause.

  Example

  DB.from("items").groupAndCount("name").all()
       //=> SELECT name, count(*) AS count FROM items GROUP BY name
       //=> [{name : 'a', count : 1}, ...]
  
  DB.from("items").groupAndCount("first_name", "last_name").all()
       //SELECT first_name, last_name, count(*) AS count FROM items GROUP BY first_name, last_name
       //=> [{first_name : 'a', last_name : 'b', count : 1}, ...]
  
  DB.from("items").groupAndCount("first_name___name").all()
       //=> SELECT first_name AS name, count(*) AS count FROM items GROUP BY first_name
       //=> [{name : 'a', count:1}, ...]
          

  Arguments
  • columns :

    columns to croup and count on.

  Returns
  • patio.Dataset

    a cloned dataset with the GROUP clause and count added.

  Source

  function (columns){
     columns = argsToArray(arguments);
     var group = this.group.apply(this, columns.map(function (c) {
         return this._unaliasedIdentifier(c);
     }, this));
     return group.select.apply(group, columns.concat([this._static.COUNT_OF_ALL_AS_COUNT]));
             
  }
      

  groupBy Function Public

  ---

  Defined dataset/query.js
  Source

  function (){
     return this.group.apply(this, arguments);
             
  }
      

  gt Function Public

  ---

  Defined dataset/query.js

  Returns a cloned dataset with a greater than expression added to the WHERE clause.

  Example

  DB.from("test").gt({x : 1});
           //=> SELECT * FROM test WHERE (x > 1)
   DB.from("test").gt({x : 1, y : 10});
           //=> SELECT * FROM test WHERE ((x > 1) AND (y > 10))
          

  Arguments
  • obj :

    object used to create the greater than expression.

  Returns
  • patio.Dataset

    a cloned dataset with the greater than expression added to the WHERE clause.

  Source

  function (obj){
     return this.filter(this.__createBoolExpression("gt", obj));
             
  }
      

  gte Function Public

  ---

  Defined dataset/query.js

  Returns a cloned dataset with a greater than or equal to expression added to the WHERE clause.

  Example

  DB.from("test").gte({x : 1});
           //=> SELECT * FROM test WHERE (x >= 1)
   DB.from("test").gte({x : 1, y : 10});
           //=> SELECT * FROM test WHERE ((x >= 1) AND (y >= 10))
          

  Arguments
  • arr :
  • obj Object :

    object used to create the greater than or equal to expression.

  Returns
  • patio.Dataset

    a cloned dataset with the greater than or equal to expression added to the WHERE clause.

  Source

  function (arr){
     arr = this.__arrayToConditionSpecifier(argsToArray(arguments), "gte");
     return this.filter(this.__createBoolExpression("gte", arr));
             
  }
      

  having Function Public

  ---

  Defined dataset/query.js

  Returns a copy of the dataset with the HAVING conditions changed. See patio.Dataset#filter for argument types.

  Example

  DB.from("items").group("sum").having({sum : 10}).sql;
       //=> SELECT * FROM items GROUP BY sum HAVING (sum = 10)
          

  Returns
  • patio.Dataset

    a cloned dataset with HAVING clause changed or added.

  Source

  function (){
     var cond = argsToArray(arguments).map(function (s) {
         return isString(s) && s !== '' ? this.stringToIdentifier(s) : s;
     }, this);
     return this._filter.apply(this, ["having"].concat(cond));
             
  }
      

  import Function Public

  ---

  Defined dataset/actions.js

  Inserts multiple records into the associated table. This method can be used to efficiently insert a large number of records into a table in a single query if the database supports it. Inserts are automatically wrapped in a transaction.

  This method is called with a columns array and an array of value arrays:

    // INSERT INTO table (x, y) VALUES (1, 2)
    // INSERT INTO table (x, y) VALUES (3, 4)
    DB.from("table").import(["x", "y"], [[1, 2], [3, 4]]).
  

  This method also accepts a dataset instead of an array of value arrays:

   // INSERT INTO table (x, y) SELECT a, b FROM table2
   DB.from("table").import(["x", "y"], DB.from("table2").select("a", "b"));
  

  The method also accepts a commitEvery option that specifies the number of records to insert per transaction. This is useful especially when inserting a large number of records, e.g.:

    // this will commit every 50 records
   DB.from("table").import(["x", "y"], [[1, 2], [3, 4], ...], {commitEvery : 50});
  

  Arguments
  • columns :

    The columns to insert values for. This array will be used as the base for each values item in the values array.

  • values :

    Array of arrays of values to insert into the columns.

  • opts? :

    options

  • cb? :

    the callback to invoke when the action is done.

  • opts.commitEvery? Number :

    the number of records to insert per transaction.

  Returns
  • comb.Promise

    a promise that is resolved once all records have been inserted.

  Source

  function (columns,values,opts,cb){
     if (isFunction(opts)) {
         cb = opts;
         opts = null;
     }
     opts = opts || {};
     var ret, self = this;
     if (isInstanceOf(values, Dataset)) {
         ret = this.db.transaction(function () {
             return self.insert(columns, values);
         });
     } else {
         if (!values.length) {
             ret = new Promise().callback();
         } else if (!columns.length) {
             throw new QueryError("Invalid columns in import");
         }
         var sliceSize = opts.commitEvery || opts.slice, result = [];
         if (sliceSize) {
             ret = asyncArray(partition(values, sliceSize)).forEach(function (entries, offset) {
                 offset = (offset * sliceSize);
                 return self.db.transaction(opts, function () {
                     return when(self.multiInsertSql(columns, entries).map(function (st, index) {
                         return self.executeDui(st).chain(function (res) {
                             result[offset + index] = res;
                         });
                     }));
                 });
             }, 1);
         } else {
             var statements = this.multiInsertSql(columns, values);
             ret = this.db.transaction(function () {
                 return when(statements.map(function (st, index) {
                     return self.executeDui(st).chain(function (res) {
                         result[index] = res;
                     });
                 }));
             });
         }
     }
     return ret.chain(function () {
         return flatten(result);
     }).classic(cb).promise();
             
  }
      

  inputIdentifier Function Private

  ---

  Defined dataset/sql.js
  Arguments
  • v :
  Source

  function (v){
     var i = this.__identifierInputMethod;
     v = v.toString(this);
     return !isUndefinedOrNull(i) ?
         isFunction(v[i]) ?
             v[i]() :
             isFunction(comb[i]) ?
                 comb[i](v)
                 : v
         : v;
             
  }
      

  insert Function Public

  ---

  Defined dataset/actions.js

  Inserts values into the associated table. The returned value is generally the value of the primary key for the inserted row, but that is adapter dependent.

  Example

  // INSERT INTO items DEFAULT VALUES
  DB.from("items").insert()
  
  // INSERT INTO items DEFAULT VALUES
  DB.from("items").insert({});
  
  // INSERT INTO items VALUES (1, 2, 3)
  DB.from("items").insert([1,2,3]);
  
  // INSERT INTO items (a, b) VALUES (1, 2)
  DB.from("items").insert(["a", "b"], [1,2]);
  
  // INSERT INTO items (a, b) VALUES (1, 2)
  DB.from("items").insert({a : 1, b : 2});
  
  // INSERT INTO items SELECT * FROM old_items
  DB.from("items").insert(DB.from("old_items"));
  
  // INSERT INTO items (a, b) SELECT * FROM old_items
  DB.from("items").insert(["a", "b"], DB.from("old_items"));
          

  Arguments
  • values patio.Dataset|patio.sql.LiteralString|Array|Object|patio.sql.BooleanExpression|... :

    values to insert into the database. The INSERT statement generated depends on the type.

    • Empty object| Or no arugments: then DEFAULT VALUES is used.
    • Object: the keys will be used as the columns, and values will be the values inserted.
    • Single patio.Dataset : an insert with subselect will be performed.
    • Array with patio.Dataset : The array will be used for columns and a subselect will performed with the dataset for the values.
    • patio.sql.LiteralString : the literal value will be used.
    • Single Array : the values in the array will be used as the VALUES clause.
    • Two Arrays: the first array is the columns the second array is the values.
    • patio.sql.BooleanExpression : the expression will be used as the values.
    • An arbitrary number of arguments : the patio.Dataset#literal version of the values will be used

  • cb? Function :

    the callback to invoke when the action is done.

  Returns
  • comb.Promise

    a promise that is typically resolved with the ID of the inserted row.

  Source

  function (){
     var args = argsToArray(arguments);
     var cb = isFunction(args[args.length - 1]) ? args.pop() : null;
     return this.executeInsert(this.insertSql.apply(this, args)).classic(cb);
             
  }
      

  insertMultiple Function Public

  ---

  Defined dataset/actions.js

  Inserts multiple values. If a block is given it is invoked for each item in the given array before inserting it. See patio.Dataset#multiInsert as a possible faster version that inserts multiple records in one SQL statement.

  Params see @link patio.Dataset#insert

  Example

  DB.from("table").insertMultiple([{x : 1}, {x : 2}]);
       //=> INSERT INTO table (x) VALUES (1)
       //=> INSERT INTO table (x) VALUES (2)
  
  DB.from("table").insertMultiple([{x : 1}, {x : 2}], function(row){
       row.y = row.x * 2;
  });
       //=> INSERT INTO table (x, y) VALUES (1, 2)
       //=> INSERT INTO table (x, y) VALUES (2, 4)
          

  Arguments
  • array :

    See patio.Dataset#insert for possible values.

  • block? :

    a function to be called before each item is inserted.

  • cb? :

    a function to be called when the aciton is complete

  Returns
  • comb.PromiseList

    a promiseList that should be resolved with the id of each item inserted in the order that was in the array.

  Source

  function (array,block,cb){
     var promises, ret;
     if (block) {
         ret = when(array.map(function (i) {
             return this.insert(block(i));
         }, this));
     } else {
         ret = when(array.map(function (i) {
             return this.insert(i);
         }, this));
     }
     return ret.classic(cb).promise();
             
  }
      

  insertSql Function Public

  ---

  Defined dataset/sql.js

  Returns an INSERT SQL query string. See patio.dataset._Actions#insert

  Example

  DB.from("items").insertSql({a : 1});
       //=> INSERT INTO items (a) VALUES (1)
  
  var ds = DB.from("test");
  
  //default values
  ds.insertSql();
       //=> INSERT INTO test DEFAULT VALUES
  
  //with hash
  ds.insertSql({name:'wxyz', price:342});
       //=> INSERT INTO test (name, price) VALUES ('wxyz', 342)
  ds.insertSql({});
       //=> INSERT INTO test DEFAULT VALUES
  
  //object that has a values property
  ds.insertSql({values:{a:1}});
       //=> INSERT INTO test (a) VALUES (1)
  
  //arbitrary value
  ds.insertSql(123);
       //=> INSERT INTO test VALUES (123)
  
  //with dataset
  ds.insertSql(DB.from("something").filter({x:2}));
       //=> INSERT INTO test SELECT * FROM something WHERE (x = 2)
  
  //with array
  ds.insertSql('a', 2, 6.5);
       //=> INSERT INTO test VALUES ('a', 2, 6.5)
          

  Arguments
  • values :

    values to insert into the database. The INSERT statement generated depends on the type.

    • Empty object| Or no arugments: then DEFAULT VALUES is used.
    • Object: the keys will be used as the columns, and values will be the values inserted.
    • Single patio.Dataset : an insert with subselect will be performed.
    • Array with patio.Dataset : The array will be used for columns and a subselect will performed with the dataset for the values.
    • patio.sql.LiteralString : the literal value will be used.
    • Single Array : the values in the array will be used as the VALUES clause.
    • Two Arrays: the first array is the columns the second array is the values.
    • patio.sql.BooleanExpression : the expression will be used as the values.
    • An arbitrary number of arguments : the patio.Dataset#literal version of the values will be used

  Returns
  • String

    a INSERT SQL query string

  Throws
  • patio.QueryError

    if there are Different number of values and columns given to insertSql or if an invalid BooleanExpresion is given.

  Source

  function (values){
     values = argsToArray(arguments);
     var opts = this.__opts;
     if (opts.sql) {
         return this._staticSql(opts.sql);
     }
     this.__checkModificationAllowed();
     var columns = [];
     switch (values.length) {
     case 0 :
         //we have no values
         return this.insertSql({});
     case 1 :
         var vals = values[0], v;
         if (isInstanceOf(vals, Dataset, LiteralString) || isArray(vals)) {
             values = vals;
         } else if (vals.hasOwnProperty("values") && isObject((v = vals.values))) {
             return this.insertSql(v);
         } else if (isHash(vals)) {
             vals = merge({}, opts.defaults || {}, vals);
             vals = merge({}, vals, opts.overrides || {});
             values = [];
             for (var i in vals) {
                 columns.push(i);
                 values.push(vals[i]);
             }
         } else if (isInstanceOf(vals, BooleanExpression)) {
             var op = vals.op;
             values = [];
             if (!isUndefinedOrNull(this._static.TWO_ARITY_OPERATORS[op])) {
                 var args = vals.args;
                 columns.push(args[0]);
                 values.push(args[1]);
             } else {
                 throw new QueryError("Invalid Expression op: " + op);
             }
         }
         break;
     case 2 :
         var v0 = values[0], v1 = values[1];
         if (isArray(v0) && isArray(v1) || isInstanceOf(v1, Dataset, LiteralString)) {
             columns = v0, values = v1;
             if (isArray(values) && columns.length !== values.length) {
                 throw new QueryError("Different number of values and columns given to insertSql");
             }
         }
         break;
     }
     columns = columns.map(function (k) {
         return isString(k) ? new Identifier(k) : k;
     }, this);
     return this.mergeOptions({columns: columns, values: values})._insertSql();
             
  }
      

  intersect Function Public

  ---

  Defined dataset/query.js

  Adds an INTERSECT clause using a second dataset object. An INTERSECT compound dataset returns all rows in both the current dataset and the given dataset.

  Example

  DB.from("items").intersect(DB.from("other_items")).sql;
    //=> SELECT * FROM (SELECT * FROM items INTERSECT SELECT * FROM other_items) AS t1
  
  DB.from("items").intersect(DB.from("other_items"), {all : true, fromSelf : false}).sql;
    //=> SELECT * FROM items INTERSECT ALL SELECT * FROM other_items
  
  DB.from("items").intersect(DB.from("other_items"), {alias : "i"}).sql;
    //=> SELECT * FROM (SELECT * FROM items INTERSECT SELECT * FROM other_items) AS i
          

  Arguments
  • dataset :

    the dataset to intersect

  • opts? :

    options

  • opts.alias? String|patio.sql.Identifier :

    Use the given value as the patio.Dataset#fromSelf alias

  • opts.all? Boolean :

    Set to true to use INTERSECT ALL instead of INTERSECT, so duplicate rows can occur

  • opts.fromSelf? Boolean :

    Set to false to not wrap the returned dataset in a patio.Dataset#fromSelf.

  Returns
  • patio.Dataset

    a cloned dataset with the INTERSECT clause.

  Throws
  • patio.QueryError

    if the operation is not supported.

  Source

  function (dataset,opts){
     opts = isUndefined(opts) ? {} : opts;
     if (!isHash(opts)) {
         opts = {all: opts};
     }
     if (!this.supportsIntersectExcept) {
         throw new QueryError("INTERSECT not supported");
     } else if (opts.all && !this.supportsIntersectExceptAll) {
         throw new QueryError("INTERSECT ALL not supported");
     }
     return this.compoundClone("intersect", dataset, opts);
             
  }
      

  interval Function Public

  ---

  Defined dataset/actions.js

  Returns a promise that is resolved with the interval between minimum and maximum values for the given column.

  Example

  // SELECT (max(id) - min(id)) FROM table LIMIT 1
    DB.from("table").interval("id").chain(function(interval){
       //(e.g) interval === 6
    });
          

  Arguments
  • column :

    to find the interval of.

  • cb? :

    a function to be called when the aciton is complete

  Returns
  • comb.Promise

    a promise that will be resolved with the interval between the min and max values of the column.

  Source

  function (column,cb){
     return this.__aggregateDataset().get(sql.max(column).minus(sql.min(column)), cb);
             
  }
      

  invert Function Public

  ---

  Defined dataset/query.js

  Inverts the current filter.

  Example

  DB.from("items").filter({category : 'software'}).invert()
       //=> SELECT * FROM items WHERE (category != 'software')
          

  Example

  DB.from("items").filter({category : 'software', id : 3}).invert()
       //=> SELECT * FROM items WHERE ((category != 'software') OR (id != 3))
          

  Returns
  • patio.Dataset

    a cloned dataset with the filter inverted.

  Source

  function (){
     var having = this.__opts.having, where = this.__opts.where;
     if (!(having || where)) {
         throw new QueryError("No current filter");
     }
     var o = {};
     if (having) {
         o.having = BooleanExpression.invert(having);
     }
     if (where) {
         o.where = BooleanExpression.invert(where);
     }
     return this.mergeOptions(o);
             
  }
      

  is Function Public

  ---

  Defined dataset/query.js

  Returnes a cloned dataset with the IS expression added to the WHERE clause.

  Example

  DB.from("test").is({boolFlag : null});
       => SELECT * FROM test WHERE (boolFlag IS NULL);
  DB.from("test").is({boolFlag : false, otherFlag : true, name : null});
       => SELECT * FROM test WHERE ((boolFlag IS FALSE) AND (otherFlag IS TRUE) AND (name IS NULL));
          

  Arguments
  • obj :

    object used to create the IS expression for.

  Returns
  • patio.Dataset

    a cloned dataset with the IS expression added to the WHERE clause.

  Source

  function (obj){
     return this.filter(this.__createBoolExpression("is", obj));
             
  }
      

  isEmpty Function Public

  ---

  Defined dataset/actions.js

  Returns a promise that is resolved with true if no records exist in the dataset, false otherwise.

  Example

  // SELECT 1 FROM table LIMIT 1
  DB.from("table").isEmpty().chain(function(isEmpty){
    // isEmpty === false
  });
          

  Arguments
  • cb? :

    a function to callback when action is done

  Returns
  • comb.Promise

    a promise that is resolved with a boolean indicating if the table is empty.

  Source

  function (cb){
     return this.get(1).chain(function (res) {
         return isUndefinedOrNull(res) || res.length === 0;
     }.bind(this)).classic(cb);
             
  }
      

  isFalse Function Public

  ---

  Defined dataset/query.js

  Returnes a cloned dataset with the IS FALSE boolean expression added to the WHERE clause.

  Example

  DB.from("test").isFalse("boolFlag");
       => SELECT * FROM test WHERE (boolFlag IS FALSE);
  DB.from("test").isFalse("boolFlag", "otherFlag");
       => SELECT * FROM test WHERE (boolFlag IS FALSE AND otherFlag IS FALSE);
          

  Arguments
  • arr :

    variable number of arguments to create an IS FALSE expression for.

  Returns
  • patio.Dataset

    a cloned dataset with the IS FALSE expression added to the WHERE clause.

  Source

  function (arr){
     arr = this.__arrayToConditionSpecifier(argsToArray(arguments), false);
     return this.filter(this.__createBoolExpression("is", arr));
             
  }
      

  isNot Function Public

  ---

  Defined dataset/query.js

  Returnes a cloned dataset with the IS NOT expression added to the WHERE clause.

  Example

  DB.from("test").isNot({boolFlag : null});
       => SELECT * FROM test WHERE (boolFlag IS NOT NULL);
  DB.from("test").isNot({boolFlag : false, otherFlag : true, name : null});
       => SELECT * FROM test WHERE ((boolFlag IS NOT FALSE) AND (otherFlag IS NOT TRUE) AND (name IS NOT NULL));
          

  Arguments
  • obj :

    object used to create the IS NOT expression for.

  Returns
  • patio.Dataset

    a cloned dataset with the IS NOT expression added to the WHERE clause.

  Source

  function (obj){
     return this.filter(this.__createBoolExpression("isNot", obj));
             
  }
      

  isNotFalse Function Public

  ---

  Defined dataset/query.js

  Returnes a cloned dataset with the IS NOT FALSE boolean expression added to the WHERE clause.

  Example

  DB.from("test").isNotFalse("boolFlag");
       => SELECT * FROM test WHERE (boolFlag IS NOT FALSE);
  DB.from("test").isNotFalse("boolFlag", "otherFlag");
       => SELECT * FROM test WHERE (boolFlag IS NOT FALSE AND otherFlag IS NOT FALSE);
          

  Arguments
  • arr :

    variable number of arguments to create an IS NOT FALSE expression for.

  Returns
  • patio.Dataset

    a cloned dataset with the IS NOT FALSE expression added to the WHERE clause.

  Source

  function (arr){
     arr = this.__arrayToConditionSpecifier(argsToArray(arguments), false);
     return this.filter(this.__createBoolExpression("isNot", arr));
             
  }
      

  isNotNull Function Public

  ---

  Defined dataset/query.js

  Returnes a cloned dataset with the IS NOT NULL boolean expression added to the WHERE clause.

  Example

  DB.from("test").isNotNull("boolFlag");
       => SELECT * FROM test WHERE (boolFlag IS NOT NULL);
  DB.from("test").isNotNull("boolFlag", "otherFlag");
       => SELECT * FROM test WHERE (boolFlag IS NOT NULL AND otherFlag IS NOT NULL);
          

  Arguments
  • arr :

    variable number of arguments to create an IS NOT NULL expression for.

  Returns
  • patio.Dataset

    a cloned dataset with the IS NOT NULL expression added to the WHERE clause.

  Source

  function (arr){
     arr = this.__arrayToConditionSpecifier(argsToArray(arguments), null);
     return this.filter(this.__createBoolExpression("isNot", arr));
             
  }
      

  isNotTrue Function Public

  ---

  Defined dataset/query.js

  Returnes a cloned dataset with the IS NOT TRUE boolean expression added to the WHERE clause.

  Example

  DB.from("test").isNotTrue("boolFlag");
       => SELECT * FROM test WHERE (boolFlag IS NOT TRUE);
  DB.from("test").isNotTrue("boolFlag", "otherFlag");
       => SELECT * FROM test WHERE (boolFlag IS NOT TRUE AND otherFlag IS NOT TRUE);
          

  Arguments
  • arr :

    variable number of arguments to create an IS NOT TRUE expression for.

  Returns
  • patio.Dataset

    a cloned dataset with the IS NOT TRUE expression added to the WHERE clause.

  Source

  function (arr){
     arr = this.__arrayToConditionSpecifier(argsToArray(arguments), true);
     return this.filter(this.__createBoolExpression("isNot", arr));
             
  }
      

  isNull Function Public

  ---

  Defined dataset/query.js

  Returnes a cloned dataset with the IS NULL boolean expression added to the WHERE clause.

  Example

  DB.from("test").isNull("boolFlag");
       => SELECT * FROM test WHERE (boolFlag IS NULL);
  DB.from("test").isNull("boolFlag", "otherFlag");
       => SELECT * FROM test WHERE (boolFlag IS NULL AND otherFlag IS NULL);
          

  Arguments
  • arr :

    variable number of arguments to create an IS NULL expression for.

  Returns
  • patio.Dataset

    a cloned dataset with the IS NULL expression added to the WHERE clause.

  Source

  function (arr){
     arr = this.__arrayToConditionSpecifier(argsToArray(arguments), null);
     return this.filter(this.__createBoolExpression("is", arr));
             
  }
      

  isTrue Function Public

  ---

  Defined dataset/query.js

  Returnes a cloned dataset with the IS TRUE boolean expression added to the WHERE clause.

  Example

  DB.from("test").isTrue("boolFlag");
       => SELECT * FROM test WHERE (boolFlag IS TRUE);
  DB.from("test").isTrue("boolFlag", "otherFlag");
       => SELECT * FROM test WHERE (boolFlag IS TRUE AND otherFlag IS TRUE);
          

  Arguments
  • arr :

    variable number of arguments to create an IS TRUE expression for.

  Returns
  • patio.Dataset

    a cloned dataset with the IS TRUE expression added to the WHERE clause.

  Source

  function (arr){
     arr = this.__arrayToConditionSpecifier(argsToArray(arguments), true);
     return this.filter(this.__createBoolExpression("is", arr));
             
  }
      

  join Function Public

  ---

  Defined dataset/query.js

  Returns a cloned dataset with an inner join applied.

  Source

  function (){
     return this.innerJoin.apply(this, arguments);
             
  }
      

  joinClauseSql Function Private

  ---

  Defined dataset/sql.js
  Arguments
  • jc :
  Source

  function (jc){
     var table = jc.table,
         tableAlias = jc.tableAlias;
     if (table === tableAlias) {
         tableAlias = null;
     }
     var tref = this.__tableRef(table);
     return string.format(" %s %s", this._joinTypeSql(jc.joinType), tableAlias ? this.__asSql(tref, tableAlias) : tref);
             
  }
      

  joinOnClauseSql Function Private

  ---

  Defined dataset/sql.js
  Arguments
  • jc :
  Source

  function (jc){
     return string.format("%s ON %s", this.joinClauseSql(jc), this.literal(this._filterExpr(jc.on)));
             
  }
      

  joinTable Function Public

  ---

  Defined dataset/query.js

  Returns a joined dataset. Uses the following arguments:

  Example

  DB.from("items").joinTable("leftOuter", "categories", [["categoryId", "id"],["categoryId", [1, 2, 3]]]).sql;
       //=>'SELECT
              *
             FROM
               `items`
                   LEFT OUTER JOIN
               `categories` ON (
                           (`categories`.`categoryId` = `items`.`id`)
                               AND
                           (`categories`.`categoryId` IN (1,2, 3))
                           )
  DB.from("items").leftOuter("categories", [["categoryId", "id"],["categoryId", [1, 2, 3]]]).sql;
       //=>'SELECT
              *
             FROM
               `items`
                   LEFT OUTER JOIN
               `categories` ON (
                           (`categories`.`categoryId` = `items`.`id`)
                               AND
                           (`categories`.`categoryId` IN (1,2, 3))
                           )
  
  DB.from("items").leftOuterJoin("categories", {categoryId:"id"}).sql
           //=> SELECT * FROM "items" LEFT OUTER JOIN "categories" ON ("categories"."categoryId" = "items"."id")
  
  DB.from("items").rightOuterJoin("categories", {categoryId:"id"}).sql
           //=> SELECT * FROM "items" RIGHT OUTER JOIN "categories" ON ("categories"."categoryId" = "items"."id")
  
  DB.from("items").fullOuterJoin("categories", {categoryId:"id"}).sql
           //=> SELECT * FROM "items" FULL OUTER JOIN "categories" ON ("categories"."categoryId" = "items"."id")
  
  DB.from("items").innerJoin("categories", {categoryId:"id"}).sql
           //=> SELECT * FROM "items" INNER JOIN "categories" ON ("categories"."categoryId" = "items"."id")
  
  DB.from("items").leftJoin("categories", {categoryId:"id"}).sql
           //=> SELECT * FROM "items" LEFT JOIN "categories" ON ("categories"."categoryId" = "items"."id")
  
  DB.from("items").rightJoin("categories", {categoryId:"id"}).sql
           //=> SELECT * FROM "items" RIGHT JOIN "categories" ON ("categories"."categoryId" = "items"."id")
  
  DB.from("items").fullJoin("categories", {categoryId:"id"}).sql
           //=> SELECT * FROM "items" FULL JOIN "categories" ON ("categories"."categoryId" = "items"."id")
  
  DB.from("items").naturalJoin("categories").sql
           //=> SELECT * FROM "items" NATURAL JOIN "categories"
  
  DB.from("items").naturalLeftJoin("categories").sql
           //=> SELECT * FROM "items" NATURAL LEFT JOIN "categories"
  
  DB.from("items").naturalRightJoin("categories").sql
           //=> SELECT * FROM "items" NATURAL RIGHT JOIN "categories"
  
  DB.from("items").naturalFullJoin("categories").sql
           //=> SELECT * FROM "items" NATURAL FULL JOIN "categories"'
  
  DB.from("items").crossJoin("categories").sql
           //=> SELECT * FROM "items" CROSS JOIN "categories"
          

  Arguments
  • type :

    the type of join to do.

  • table :

    depends on the type.

    • patio.Dataset - a subselect is performed with an alias
    • Object - an object that has a tableName property.
    • String| patio.sql.Identifier - the name of the table

  • expr? :
    • depends on type
      • Object|Array of two element arrays - Assumes key (1st arg) is column of joined table (unless already qualified), and value (2nd arg) is column of the last joined or primary table (or the implicitQualifier option
      .
      • Array - If all members of the array are string or patio.sql.Identifier, considers them as columns and uses a JOIN with a USING clause. Most databases will remove duplicate columns from the result set if this is used.
      • null|undefined(not passed in) - If a cb is not given, doesn't use ON or USING, so the JOIN should be a NATURAL or CROSS join. If a block is given, uses an ON clause based on the block, see below.
      • Everything else - pretty much the same as a using the argument in a call to patio.Dataset#filter, so strings are considered literal, patio.sql.Identifiers specify boolean columns, and patio.sql expressions can be used. Uses a JOIN with an ON clause.
  • options :

    an object of options.

  • cb? :

    cb - The cb argument should only be given if a JOIN with an ON clause is used, in which case it is called with

    • table alias/name for the table currently being joined
    • the table alias/name for the last joined (or first table)
    • array of previous
    the cb should return an expression to be used in the ON clause.

  • [options.tableAlias= undefined] String|patio.sql.Identifier :

    the name of the table's alias when joining, necessary for joining to the same table more than once. No alias is used by default.

  • [options.implicitQualifier= undefined] String|patio.sql.Identifier :

    The name to use for qualifying implicit conditions. By default, the last joined or primary table is used.

  Returns
  • patio.Dataset

    a cloned dataset joined using the arguments.

  Source

  function (type,table,expr,options,cb){
     var args = argsToArray(arguments);
     if (isFunction(args[args.length - 1])) {
         cb = args[args.length - 1];
         args.pop();
     } else {
         cb = null;
     }
     type = args.shift(), table = args.shift(), expr = args.shift(), options = args.shift();
     expr = isUndefined(expr) ? null : expr, options = isUndefined(options) ? {} : options;
     var h;
     var usingJoin = isArray(expr) && expr.length && expr.every(function (x) {
         return isString(x) || isInstanceOf(x, Identifier);
     });
     if (usingJoin && !this.supportsJoinUsing) {
         h = {};
         expr.forEach(function (s) {
             h[s] = s;
         });
         return this.joinTable(type, table, h, options);
     }
     var tableAlias, lastAlias;
     if (isHash(options)) {
         tableAlias = options.tableAlias;
         lastAlias = options.implicitQualifier;
     } else if (isString(options) || isInstanceOf(options, Identifier)) {
         tableAlias = options;
         lastAlias = null;
     } else {
         throw new QueryError("Invalid options format for joinTable %j4", [options]);
     }
     var tableAliasNum, tableName;
     if (isInstanceOf(table, Dataset)) {
         if (!tableAlias) {
             tableAliasNum = (this.__opts.numDatasetSources || 0) + 1;
             tableAlias = this._datasetAlias(tableAliasNum);
         }
         tableName = tableAlias;
     } else {
         if (!isUndefined(table.tableName)) {
             table = table.tableName;
         }
         if (isArray(table)) {
             table = table.map(this.stringToIdentifier, this);
         } else {
             table = isString(table) ? this.stringToIdentifier(table) : table;
             var parts = this._splitAlias(table), implicitTableAlias = parts[1];
             table = parts[0];
             tableAlias = tableAlias || implicitTableAlias;
             tableName = tableAlias || table;
         }
     }
     var join;
     if (!expr && !cb) {
         join = new JoinClause(type, table, tableAlias);
     } else if (usingJoin) {
         if (cb) {
             throw new QueryError("cant use a cb if an array is given");
         }
         join = new JoinUsingClause(expr, type, table, tableAlias);
     } else {
         lastAlias = lastAlias || this.__opts["lastJoinedTable"] || this.firstSourceAlias;
         if (Expression.isConditionSpecifier(expr)) {
             var newExpr = [];
             for (var i in expr) {
                 var val = expr[i];
                 if (isArray(val) && val.length === 2) {
                     i = val[0], val = val[1];
                 }
                 var k = this.qualifiedColumnName(i, tableName), v;
                 if (isInstanceOf(val, Identifier)) {
                     v = val.qualify(lastAlias);
                 } else {
                     v = val;
                 }
                 newExpr.push([k, v]);
             }
             expr = newExpr;
         }
         if (isFunction(cb)) {
             var expr2 = cb.apply(sql, [tableName, lastAlias, this.__opts.join || []]);
             expr = expr ? new BooleanExpression("AND", expr, expr2) : expr2;
         }
         join = new JoinOnClause(expr, type, table, tableAlias);
     }
     var opts = {join: (this.__opts.join || []).concat([join]), lastJoinedTable: tableName};
     if (tableAliasNum) {
         opts.numDatasetSources = tableAliasNum;
     }
     return this.mergeOptions(opts);
             
  }
      

  joinUsingClauseSql Function Private

  ---

  Defined dataset/sql.js
  Arguments
  • jc :
  Source

  function (jc){
     return string.format("%s USING (%s)", this.joinClauseSql(jc), this.__columnList(jc.using));
             
  }
      

  last Function Public

  ---

  Defined dataset/actions.js

  Reverses the order and then runs first. Note that this will not necessarily give you the last record in the dataset, unless you have an unambiguous order.

  Example

  // SELECT * FROM table ORDER BY id DESC LIMIT 1
   DB.from("table").order("id").last().chain(function(lastItem){
       //...(e.g lastItem === {id : 10})
   });
  
   // SELECT * FROM table ORDER BY id ASC LIMIT 2
    DB.from("table").order(sql.id.desc()).last(2).chain(function(lastItems){
       //...(e.g lastItems === [{id : 1}, {id : 2});
   });
          

  Arguments
  • args :

    See patio.Dataset#first for argument types.

  Returns
  • comb.Promise

    a promise that will be resolved with a single object or array depending on the arguments provided.

  Throws
  • patio.error.QueryError

    If there is not currently an order for this dataset.

  Source

  function (args){
     if (!this.__opts.order) {
         throw new QueryError("No order specified");
     }
     var ds = this.reverse();
     return ds.first.apply(ds, arguments);
             
  }
      

  like Function Public

  ---

  Defined dataset/query.js
  Source

  function (){
     return this.grep.apply(this, arguments);
             
  }
      

  limit Function Public

  ---

  Defined dataset/query.js

  If given an integer, the dataset will contain only the first l results. If a second argument is given, it is used as an offset. To use an offset without a limit, pass null as the first argument.

  Example

  DB.from("items").limit(10)
       //=> SELECT * FROM items LIMIT 10
  DB.from("items").limit(10, 20)
       //=> SELECT * FROM items LIMIT 10 OFFSET 20
  DB.from("items").limit([3, 7]).sql
       //=> SELECT * FROM items LIMIT 5 OFFSET 3');
  DB.from("items").limit(null, 20)
       //=> SELECT * FROM items OFFSET 20
  
  DB.from("items").limit('6', sql['a() - 1']).sql
       => 'SELECT * FROM items LIMIT 6 OFFSET a() - 1');
          

  Arguments
  • limit :

    the limit to apply

  • offset :

    the offset to apply

  Returns
  • patio.Dataset

    a cloned dataset witht the LIMIT and OFFSET applied.

  Source

  function (limit,offset){
     if (this.__opts.sql) {
         return this.fromSelf().limit(limit, offset);
     }
     if (isArray(limit) && limit.length === 2) {
         offset = limit[0];
         limit = limit[1] - limit[0] + 1;
     }
     if (isString(limit) || isInstanceOf(limit, LiteralString)) {
         limit = parseInt("" + limit, 10);
     }
     if (isNumber(limit) && limit < 1) {
         throw new QueryError("Limit must be >= 1");
     }
     var opts = {limit: limit};
     if (offset) {
         if (isString(offset) || isInstanceOf(offset, LiteralString)) {
             offset = parseInt("" + offset, 10);
             isNaN(offset) && (offset = 0);
         }
         if (isNumber(offset) && offset < 0) {
             throw new QueryError("Offset must be >= 0");
         }
         opts.offset = offset;
     }
     return this.mergeOptions(opts);
             
  }
      

  literal Function Public

  ---

  Defined dataset/sql.js

  Returns a literal representation of a value to be used as part of an SQL expression.

  Example

  DB.from("items").literal("abc'def\\") //=> "'abc''def\\\\'"
   DB.from("items").literal("items__id") //=> "items.id"
   DB.from("items").literal([1, 2, 3]) //=> "(1, 2, 3)"
   DB.from("items").literal(DB.from("items")) //=> "(SELECT * FROM items)"
   DB.from("items").literal(sql.x.plus(1).gt("y")); //=> "((x + 1) > y)"
          

  Arguments
  • v :

    the value to convert the the SQL literal representation

  Returns
  • String

    a literal representation of the value.

  Throws
  • patio.QueryError

    If an unsupported object is given.

  Source

  function (v){
     if (isInstanceOf(v, Json, JsonArray)) {
         return this._literalJson(v);
     } else if (isInstanceOf(v, LiteralString)) {
         return "" + v;
     } else if (isString(v)) {
         return this._literalString(v);
     } else if (isNumber(v)) {
         return this._literalNumber(v);
     } else if (isInstanceOf(v, Expression)) {
         return this._literalExpression(v);
     } else if (isInstanceOf(v, Dataset)) {
         return this._literalDataset(v);
     } else if (isArray(v)) {
         return this._literalArray(v);
     } else if (isInstanceOf(v, sql.Year)) {
         return this._literalYear(v);
     } else if (isInstanceOf(v, sql.TimeStamp, sql.DateTime)) {
         return this._literalTimestamp(v);
     } else if (isDate(v)) {
         return this._literalDate(v);
     } else if (isInstanceOf(v, sql.Time)) {
         return this._literalTime(v);
     } else if (Buffer.isBuffer(v)) {
         return this._literalBuffer(v);
     } else if (isNull(v)) {
         return this._literalNull();
     } else if (isBoolean(v)) {
         return this._literalBoolean(v);
     } else if (isHash(v)) {
         return this._literalObject(v);
     } else {
         return this._literalOther(v);
     }
             
  }
      

  lockStyle Function Public

  ---

  Defined dataset/query.js

  Returns a cloned dataset with the given lock style. If style is a string, it will be used directly.Currently "update" is respected by most databases, and "share" is supported by some.

  Example

  DB.from("items").lockStyle('FOR SHARE') # SELECT * FROM items FOR SHARE
          

  Arguments
  • style :

    the lock style to use.

  Returns
  • patio.Dataset

    a cloned datase with the given lock style.

  Source

  function (style){
     return this.mergeOptions({lock: style});
             
  }
      

  lt Function Public

  ---

  Defined dataset/query.js

  Returns a cloned dataset with a less than expression added to the WHERE clause.

  Example

  DB.from("test").lt({x : 1});
           //=> SELECT * FROM test WHERE (x < 1)
   DB.from("test").lt({x : 1, y : 10});
           //=> SELECT * FROM test WHERE ((x < 1) AND (y < 10))
          

  Arguments
  • obj :

    object used to create the less than expression.

  Returns
  • patio.Dataset

    a cloned dataset with the less than expression added to the WHERE clause.

  Source

  function (obj){
     return this.filter(this.__createBoolExpression("lt", obj));
             
  }
      

  lte Function Public

  ---

  Defined dataset/query.js

  Returns a cloned dataset with a less than or equal to expression added to the WHERE clause.

  Example

  DB.from("test").gte({x : 1});
           //=> SELECT * FROM test WHERE (x <= 1)
   DB.from("test").gte({x : 1, y : 10});
           //=> SELECT * FROM test WHERE ((x <= 1) AND (y <= 10))
          

  Arguments
  • obj :

    object used to create the less than or equal to expression.

  Returns
  • patio.Dataset

    a cloned dataset with the less than or equal to expression added to the WHERE clause.

  Source

  function (obj){
     var arr = this.__arrayToConditionSpecifier(argsToArray(arguments), "lte");
     return this.filter(this.__createBoolExpression("lte", obj));
             
  }
      

  map Function Public

  ---

  Defined dataset/actions.js

  Maps column values for each record in the dataset (if a column name is given).

  Example

  // SELECT * FROM table
  DB.from("table").map("id").chain(function(ids){
    // e.g. ids === [1, 2, 3, ...]
  });
  
   // SELECT * FROM table
  DB.from("table").map(function(r){
       return r.id * 2;
  }).chain(function(ids){
      // e.g. ids === [2, 4, 6, ...]
  });
          

  Arguments
  • column :

    if a string is provided then then it is assumed to be the name of a column in that table and the value of the column for each row will be returned. If column is a function then the return value of the function will be used.

  • cb? :

    a function to be called when the aciton is complete

  Returns
  • comb.Promise

    a promise resolved with the array of mapped values.

  Source

  function (column,cb){
     var ret = this.forEach();
     column && (ret = ret[isFunction(column) ? "map" : "pluck"](column));
     return ret.classic(cb).promise();
             
  }
      

  max Function Public

  ---

  Defined dataset/actions.js

  Returns a promise resolved with the maximum value for the given column.

  Example

  // SELECT max(id) FROM table LIMIT 1
  DB.from("table").max("id").chain(function(max){
    // e.g. max === 10.
  });
          

  Arguments
  • column :

    the column to find the maximum value for.

  • cb? :

    callback to invoke when action is done

  Returns
  • *

    the maximum value for the column.

  Source

  function (column,cb){
     return this.__aggregateDataset().get(sql.max(this.stringToIdentifier(column)), cb);
             
  }
      

  mergeOptions Function Public

  ---

  Defined dataset/index.js

  Returns a new clone of the dataset with with the given options merged into the current datasets options. If the options changed include options in patio.dataset.Query#COLUMN_CHANGE_OPTS, the cached columns are deleted. This method should generally not be called directly by user code.

  Arguments
  • opts :

    options to merge into the curred datasets options and applied to the returned dataset.

  Returns

  • [patio.Dataset] a cloned dataset with the merged options

  Source

  function (opts){
     opts = isUndefined(opts) ? {} : opts;
     var ds = new this._static(this.db, {});
     ds.rowCb = this.rowCb;
     this._static.FEATURES.forEach(function (f) {
         ds[f] = this[f];
     }, this);
     var dsOpts = ds.__opts = merge({}, this.__opts, opts);
     ds.identifierInputMethod = this.identifierInputMethod;
     ds.identifierOutputMethod = this.identifierOutputMethod;
     var columnChangeOpts = this._static.COLUMN_CHANGE_OPTS;
     if (Object.keys(opts).some(function (o) {
             return columnChangeOpts.indexOf(o) !== -1;
         })) {
         dsOpts.columns = null;
     }
     return ds;
             
  }
      

  min Function Public

  ---

  Defined dataset/actions.js

  Returns a promise resolved with the minimum value for the given column.

  Example

  // SELECT min(id) FROM table LIMIT 1
  DB.from("table").min("id").chain(function(min){
    // e.g. max === 0.
  });
          

  Arguments
  • column :

    the column to find the minimum value for.

  • cb? :

    callback to invoke when action is done

  Returns
  • *

    the minimum value for the column.

  Source

  function (column,cb){
     return this.__aggregateDataset().get(sql.min(this.stringToIdentifier(column)), cb);
             
  }
      

  multiInsert Function Public

  ---

  Defined dataset/actions.js

  This is the recommended function to do the insert of multiple items into the database. This acts as a proxy to the patio.Dataset#import method so one can use an array of hashes rather than an array of columns and an array of values. See patio.Dataset#import for more information regarding the method of inserting.

  NOTE:All hashes should have the same keys other wise some values could be missed

  Example

  // INSERT INTO table (x) VALUES (1)
  // INSERT INTO table (x) VALUES (2)
  DB.from("table").multiInsert([{x : 1}, {x : 2}]).chain(function(){
      //...do something
  })
  
  //commit every 50 inserts
  DB.from("table").multiInsert([{x : 1}, {x : 2},....], {commitEvery : 50}).chain(function(){
      //...do something
  });
          

  Arguments
  • hashes :

    an array of objects to insert into the database. The keys of the first item in the array will be used to look up columns in all subsequent objects. If the array is empty then the promise is resolved immediatly.

  • opts :

    See patio.Dataset#import.

  • cb? :

    the callback to invoke when the action is done.

  Returns
  • comb.Promise

    See patio.Dataset#import for return functionality.

  Source

  function (hashes,opts,cb){
     if (isFunction(opts)) {
         cb = opts;
         opts = null;
     }
     opts = opts || {};
     hashes = hashes || [];
     var ret = new Promise();
     if (!hashes.length) {
         ret.callback();
     } else {
         var columns = Object.keys(hashes[0]);
         ret = this["import"](columns, hashes.map(function (h) {
             return columns.map(function (c) {
                 return h[c];
             });
         }), opts, cb);
     }
     return ret.classic(cb).promise();
             
  }
      

  multiInsertSql Function Public

  ---

  Defined dataset/sql.js

  Returns an array of insert statements for inserting multiple records. This method is used by patio.dataset._Actions#multiInsert to format insert statements. This method is not typically used directly.

  Note:This method should be overridden by descendants if there is support for inserting multiple records in a single SQL statement.

  Arguments
  • columns :

    The columns to insert values for. This array will be used as the base for each values item in the values array.

  • values :

    Array of arrays of values to insert into the columns.

  Returns
  • String[]

    array of insert statements.

  Source

  function (columns,values){
     return values.map(function (r) {
         return this.insertSql(columns, r);
     }, this);
             
  }
      

  naked Function Public

  ---

  Defined dataset/query.js

  Returns the a cloned dataset with out the patio.Dataset#rowCb

  Example

  var ds = DB.from("test");
  ds.rowCb = function(r){
      r.a = r.a * 2;
  }
  
  ds.all().chain(function(ret){
       //ret === [{a : 4}, {a : 6}]
  });
  ds.naked().all().chain(function(ret){
       //ret ===  [{a : 2}, {a : 3}];
  });
          

  Returns
  • patio.Dataset

    a cloned dataset with out the patio.Dataset#rowCb

  Source

  function (){
     var ds = this.mergeOptions({});
     ds.rowCb = null;
     return ds;
             
  }
      

  negativeBooleanConstantSql Function Private

  ---

  Defined dataset/sql.js
  Arguments
  • constant :
  Source

  function (constant){
     return string.format("NOT %s", this.booleanConstantSql(constant));
             
  }
      

  neq Function Public

  ---

  Defined dataset/query.js

  Returns a cloned dataset with a not equal expression added to the WHERE clause.

  Example

  DB.from("test").neq({x : 1});
           //=> SELECT * FROM test WHERE (x != 1)
   DB.from("test").neq({x : 1, y : 10});
           //=> SELECT * FROM test WHERE ((x != 1) AND (y != 10))
          

  Arguments
  • obj :

    object used to create the not equal expression

  Returns
  • patio.Dataset

    a cloned dataset with the not equal expression added to the WHERE clause.

  Source

  function (obj){
     return this.filter(this.__createBoolExpression("neq", obj));
             
  }
      

  notBetween Function Public

  ---

  Defined dataset/query.js

  Returns a cloned dataset with a not between clause added to the where clause.

  Example

  ds.notBetween({x:[1, 2]}).sql;
           //=> SELECT * FROM test WHERE ((x < 1) OR (x > 2))
  
   ds.find({x:{notBetween:[1, 2]}}).sql;
           //=> SELECT * FROM test WHERE ((x < 1) OR (x > 2))
          

  Arguments
  • obj :

    object where the key is the column and the value is an array where the first element is the item to be less than and the second item is greater than.

  Returns
  • patio.Dataset

    a cloned dataset with a not between clause added to the where clause.

  Source

  function (obj){
     return this.filter(this.__createBetweenExpression(obj, true));
             
  }
      

  one Function Public

  ---

  Defined dataset/actions.js

  Same as patio.Dataset#singleRecord but accepts arguments to filter the dataset. See patio.Dataset#filter for argument types.

  NOTE If the last argument is a function that accepts arguments it is not assumed to be a filter function but instead a callback.

  Returns
  • comb.Promise

    a promise resolved with a single row from the database that matched the filter.

  Source

  function (){
     var args = comb(arguments).toArray(), cb;
     var last = args[args.length - 1];
     if (isFunction(last) && last.length > 0) {
         cb = args.pop();
     }
     var ret = this;
     if (args.length) {
         ret = ret.filter.apply(ret, args);
     }
     return ret.singleRecord(cb);
             
  }
      

  or Function Public

  ---

  Defined dataset/query.js

  Adds an alternate filter to an existing WHERE/HAVING using OR.

  For parameter types see patio.Dataset#filter.

  Example

  DB.from("items").filter("a").or("b")
       //=> SELECT * FROM items WHERE a OR b
          

  Returns
  • patio.Dataset

    a cloned dataset with the condition added to the WHERE/HAVING clause added.

  Throws
  • patio.QueryError

    If no WHERE?HAVING clause exists.

  Source

  function (){
     var tOpts = this.__opts;
     var clause = (tOpts.having ? "having" : "where"), clauseObj = tOpts[clause];
     if (clauseObj) {
         var args = argsToArray(arguments);
         args = args.length === 1 ? args[0] : args;
         var opts = {};
         opts[clause] = new BooleanExpression("OR", clauseObj, this._filterExpr(args));
         return this.mergeOptions(opts);
     } else {
         throw new QueryError("No existing filter found");
     }
             
  }
      

  orGroupedAnd Function Public

  ---

  Defined dataset/query.js

  Adds a group of ANDed conditions wrapped in parens to an existing where/having clause by an OR. If there isn't a where/having clause, a where clause is created with the ANDed conditions.

  For parameter types see patio.Dataset#filter.

  Example

  DB.from("items").filter({id, [1,2,3]}).orGroupedAnd([{price: {lt : 0}}, {price: {gt: 10}]).sql;
       //=> SELECT
                *
            FROM
                items
            WHERE
                ((id IN (1, 2, 3)) OR ((price > 0) AND (price < 10)))
  
  DB.from("items").orGroupedAnd([{price: {gt : 0}}, {price: {gt: 10}]).sql;
       //=> SELECT
                *
            FROM
                items
            WHERE
                ((price > 0) AND (price < 10))
          

  Returns
  • patio.Dataset

    a cloned dataset with the condition 'and group' added to the WHERE/HAVING clause.

  Source

  function (){
     var tOpts = this.__opts,
         clause = (tOpts.having ? "having" : "where"),
         clauseObj = tOpts[clause];
     if (clauseObj) {
         return this.or.apply(this, arguments);
     } else {
         var args = argsToArray(arguments);
         args = args.length === 1 ? args[0] : args;
         var opts = {};
         opts[clause] = this._filterExpr(args, null, "AND");
         return this.mergeOptions(opts);
     }
             
  }
      

  orGroupedOr Function Public

  ---

  Defined dataset/query.js

  Adds a group of ORed conditions wrapped in parens to an existing having/where clause with an OR. If there isn't already a clause, a where clause is created with the ORed group.

  For parameter types see patio.Dataset#filter.

  Example

  DB.from("items").filter({id, [1,2,3]}).andGroupedOr([{price: {lt : 0}}, {price: {gt: 10}]).sql;
       //=> SELECT
                *
            FROM
                items
            WHERE
                ((id IN (1, 2, 3)) AND ((price < 0) OR (price > 10)))
  
  DB.from("items").orGroupedOr([{price: {lt : 0}}, {price: {gt: 10}]).sql;
       //=> SELECT
                *
            FROM
                items
            WHERE
                ((price < 0) OR (price > 10))
          

  Arguments
  • filterExp :
  Returns
  • patio.Dataset

    a cloned dataset with the condition 'or group' added to the WHERE/HAVING clause.

  Source

  function (filterExp){
     return this._addGroupedCondition("OR", "OR", filterExp);
             
  }
      

  order Function Public

  ---

  Defined dataset/query.js

  Returns a copy of the dataset with the order changed. If the dataset has an existing order, it is ignored and overwritten with this order. If null is given the returned dataset has no order. This can accept multiple arguments of varying kinds, such as SQL functions. This also takes a function similar to patio.Dataset#filter

  Example

  DB.from("items").order("name")
           //=> SELECT * FROM items ORDER BY name
  
   DB.from("items").order("a", "b")
           //=> SELECT * FROM items ORDER BY a, b
  
   DB.from("items").order(sql.literal('a + b'))
           //=> SELECT * FROM items ORDER BY a + b
  
   DB.from("items").order(sql.identifier("a").plus("b"))
           //=> SELECT * FROM items ORDER BY (a + b)
  
   DB.from("items").order(sql.identifier("name").desc())
           //=> SELECT * FROM items ORDER BY name DESC
  
   DB.from("items").order(sql.identifier("name").asc({nulls : "last"))
           //=> SELECT * FROM items ORDER BY name ASC NULLS LAST
  
   DB.from("items").order(function(){
           return this.sum("name").desc();
   }); //=> SELECT * FROM items ORDER BY sum(name) DESC
  
   DB.from("items").order(null)
           //=>SELECT * FROM items
          

  Arguments
  • args :
  • arg :

    variable number of arguments similar to patio.Dataset#filter

  Returns
  • patio.Dataset

    a cloned dataset with the order changed.

  Source

  function (args){
     args = argsToArray(arguments);
     var order = [];
     args = compact(args).length ? args : null;
     if (args) {
         args.forEach(function (a) {
             if (isString(a)) {
                 order.push(this.stringToIdentifier(a));
             } else if (isFunction(a)) {
                 var res = a.apply(sql, [sql]);
                 order = order.concat(isArray(res) ? res : [res]);
             } else {
                 order.push(a);
             }
         }, this);
     } else {
         order = null;
     }
     return this.mergeOptions({order: order});
             
  }
      

  orderAppend Function Public

  ---

  Defined dataset/query.js

  Alias of patio.Dataset#orderMore;

  Source

  function (){
     return this.orderMore.apply(this, arguments);
             
  }
      

  orderBy Function Public

  ---

  Defined dataset/query.js
  Source

  function (){
     return this.order.apply(this, arguments);
             
  }
      

  orderMore Function Public

  ---

  Defined dataset/query.js

  Returns a copy of the dataset with the order columns added to the end of the existing order. For more detail

  Example

  DB.from("items").order("a").order("b");
       //=> SELECT * FROM items ORDER BY b
  
  DB.from("items").order("a").orderMore("b");
       //=>SELECT * FROM items ORDER BY a, b
          

  Source

  function (){
     var args = argsToArray(arguments);
     if (this.__opts.order) {
         args = this.__opts.order.concat(args);
     }
     return this.order.apply(this, args);
             
  }
      

  orderPrepend Function Public

  ---

  Defined dataset/query.js

  Returns a copy of the dataset with the order columns added to the beginning of the existing order. For more detail

  Example

  DB.from("items").order("a").order("b");
       //=> SELECT * FROM items ORDER BY b
  
  DB.from("items").order("a").orderPrepend("b");
       //=>SELECT * FROM items ORDER BY b, a
          

  Source

  function (){
     var ds = this.order.apply(this, arguments);
     return this.__opts.order ? ds.orderMore.apply(ds, this.__opts.order) : ds;
             
  }
      

  orderedExpressionSql Function Private

  ---

  Defined dataset/sql.js
  Arguments
  • oe :
  Source

  function (oe){
     var s = string.format("%s %s", this.literal(oe.expression), oe.descending ? "DESC" : "ASC");
     if (oe.nulls) {
         s = string.format("%s NULLS %s", s, oe.nulls === "first" ? "FIRST" : "LAST");
     }
     return s;
             
  }
      

  outputIdentifier Function Private

  ---

  Defined dataset/sql.js
  Arguments
  • v :
  Source

  function (v){
     (v === '' && (v = 'untitled'));
     var i = this.__identifierOutputMethod;
     return !isUndefinedOrNull(i) ?
         isFunction(v[i]) ?
             v[i]() :
             isFunction(comb[i]) ?
                 comb[i](v)
                 : v
         : v;
             
  }
      

  placeholderLiteralStringSql Function Private

  ---

  Defined dataset/sql.js
  Arguments
  • pls :
  Source

  function (pls){
     var args = pls.args;
     var s;
     if (isHash(args)) {
         for (var i in args) {
             args[i] = this.literal(args[i]);
         }
         s = string.format(pls.str, args);
     } else {
         s = pls.str.replace(this._static.QUESTION_MARK, "%s");
         args = toArray(args).map(this.literal, this);
         s = string.format(s, args);
     }
     if (pls.parens) {
         s = string.format("(%s)", s);
     }
     return s;
             
  }
      

  postLoad Function Public

  ---

  Defined dataset/actions.js

  This is run inside patio.Dataset#all, after all of the records have been loaded via patio.Dataset#forEach, but before any block passed to all is called. It is called with a single argument, an array of all returned records. Does nothing by default.

  Arguments
  • allRecords :
  Source

  function (allRecords){
     return allRecords;
             
  }
      

  qualifiedColumnName Function Public

  ---

  Defined dataset/sql.js

  Returns a qualified column name (including a table name) if the column name isn't already qualified.

  Example

  dataset.qualifiedColumnName("b1", "items");
       //=> items.b1
  
  dataset.qualifiedColumnName("ccc__b"));
       //=> 'ccc.b'
  
  dataset.qualifiedColumnName("ccc__b", "items"));
       //=> 'ccc.b'
          

  Arguments
  • column :

    the column to qualify. If the column is already qualified (e.g. ccc__b) then the table name (e.g. ccc) will override the provided table.

  • table :

    the name of the table to qualify the column to.

  Returns
  • String

    the qualified column name..

  Source

  function (column,table){
     if (isString(column)) {
         var parts = this._splitString(column);
         var columnTable = parts[0], alias = parts[2], schema, tableAlias;
         column = parts[1];
         if (!columnTable) {
             if (isInstanceOf(table, Identifier)) {
                 table = table.value;
             }
             if (isInstanceOf(table, AliasedExpression)) {
                 tableAlias = table.alias;
             } else if (isInstanceOf(table, QualifiedIdentifier)) {
                 tableAlias = table;
             } else {
                 parts = this._splitString(table);
                 schema = parts[0];
                 tableAlias = parts[2];
                 table = parts[1];
                 if (schema) {
                     tableAlias = new Identifier(tableAlias) || new QualifiedIdentifier(schema, table);
                 }
             }
             columnTable = tableAlias || table;
         }
         return new QualifiedIdentifier(columnTable, column);
     } else if (isInstanceOf(column, Identifier)) {
         return column.qualify(table);
     } else {
         return column;
     }
             
  }
      

  qualifiedIdentifierSql Function Private

  ---

  Defined dataset/sql.js
  Arguments
  • qcr :
  Source

  function (qcr){
     return [qcr.table, qcr.column].map(function (x) {
         var isLiteral = [QualifiedIdentifier, Identifier, String].some(function (c) {
             return x instanceof c;
         }), ret;
         if (isLiteral) {
             ret = this.literal(x);
         } else {
             ret = this.quoteIdentifier(x);
         }
         return ret;
     }, this).join('.');
             
  }
      

  qualify Function Public

  ---

  Defined dataset/query.js

  Qualify to the given table, or patio.Dataset#firstSourceAlias if not table is given.

  Example

  DB.from("items").filter({id : 1}).qualify();
     //=> SELECT items.* FROM items WHERE (items.id = 1)
  
  DB.from("items").filter({id : 1}).qualify("i");
    //=> SELECT i.* FROM items WHERE (i.id = 1)
          

  Arguments
  • [table= {@link patio.Dataset#firstSourceAlias}] :

    the table name to qualify to.

  Returns
  • patio.Dataset

    a cloned dataset qualified to the table or patio.Dataset#firstSourceAlias

  Source

  function (table){
     table = table || this.firstSourceAlias;
     return this.qualifyTo(table);
             
  }
      

  qualifyTo Function Public

  ---

  Defined dataset/query.js

  Return a copy of the dataset with unqualified identifiers in the SELECT, WHERE, GROUP, HAVING, and ORDER clauses qualified by the given table. If no columns are currently selected, select all columns of the given table.

  Example

  DB.from("items").filter({id : 1}).qualifyTo("i");
       //=> SELECT i.* FROM items WHERE (i.id = 1)
          

  Arguments
  • table :

    the name to qualify identifier to.

  Returns
  • patio.Dataset

    a cloned dataset with unqualified identifiers qualified.

  Source

  function (table){
     var o = this.__opts;
     if (o.sql) {
         return this.mergeOptions();
     }
     var h = {};
     array.intersect(Object.keys(o), this._static.QUALIFY_KEYS).forEach(function (k) {
         h[k] = this._qualifiedExpression(o[k], table);
     }, this);
     if (!o.select || isEmpty(o.select)) {
         h.select = [new ColumnAll(table)];
     }
     return this.mergeOptions(h);
             
  }
      

  qualifyToFirstSource Function Public

  ---

  Defined dataset/query.js

  Qualify the dataset to its current first source(first from clause). This is useful if you have unqualified identifiers in the query that all refer to the first source, and you want to join to another table which has columns with the same name as columns in the current dataset. See patio.Dataset#qualifyTo

  Example

  DB.from("items").filter({id : 1}).qualifyToFirstSource();
       //=> SELECT items.* FROM items WHERE (items.id = 1)
          

  Returns
  • patio.Dataset

    a cloned dataset that is qualified with the first source.

  Source

  function (){
     return this.qualifyTo(this.firstSourceAlias);
             
  }
      

  quoteIdentifier Function Private

  ---

  Defined dataset/sql.js
  Arguments
  • name :
  Source

  function (name){
     if (isInstanceOf(name, LiteralString)) {
         return name;
     } else {
         if (isInstanceOf(name, Identifier)) {
             name = name.value;
         }
         name = this.inputIdentifier(name);
         if (this.quoteIdentifiers) {
             name = this._quotedIdentifier(name);
         }
     }
     return name;
             
  }
      

  quoteSchemaTable Function Private

  ---

  Defined dataset/sql.js
  Arguments
  • table :
  Source

  function (table){
     var parts = this.schemaAndTable(table);
     var schema = parts[0];
     table = parts[1];
     return string.format("%s%s", schema ? this.quoteIdentifier(schema) + "." : "", this.quoteIdentifier(table));
             
  }
      

  range Function Public

  ---

  Defined dataset/actions.js

  Returns a promise resolved with a range from the minimum and maximum values for the given column.

  Example

  // SELECT max(id) AS v1, min(id) AS v2 FROM table LIMIT 1
   DB.from("table").range("id").chain(function(min, max){
       //e.g min === 1 AND max === 10
   });
          

  Arguments
  • column :

    the column to find the min and max value for.

  • cb? :

    the callback to invoke when the action is done.

  Returns
  • comb.Promise

    a promise that is resolved with the min and max value, as the first and second args respectively.

  Source

  function (column,cb){
     var ret = new Promise();
     this.__aggregateDataset()
         .select(sql.min(this.stringToIdentifier(column)).as("v1"), sql.max(this.stringToIdentifier(column)).as("v2"))
         .first()
         .chain(function (r) {
             ret.callback(r.v1, r.v2);
         }, ret.errback);
     return ret.classic(cb).promise();
             
  }
      

  remove Function Public

  ---

  Defined dataset/actions.js

  Deletes the records in the dataset. The returned Promise should be resolved with the number of records deleted, but that is adapter dependent.

  Example

  // DELETE * FROM table
  DB.from("table").remove().chain(function(numDeleted){
      //numDeleted === 3
  });
          

  Arguments
  • cb? :

    the callback to invoke when the action is done.

  Returns
  • comb.Promise

    a promise resolved with the number of records deleted, but that is adapter dependent.

  Source

  function (cb){
     return this.executeDui(this.deleteSql).classic(cb).promise();
             
  }
      

  returning Function Public

  ---

  Defined dataset/query.js

  Adds a RETURNING clause, which is not supported by all databases. If returning is used instead of returning the autogenerated primary key or update/delete returning the number of rows modified.

  Example

  ds.from("items").returning() //"RETURNING *"
  ds.from("items").returning(null) //"RETURNING NULL"
  ds.from("items").returning("id", "name") //"RETURNING id, name"
  ds.from("items").returning(["id", "name"]) //"RETURNING id, name"
          

  Arguments
  • values :

    columns to return. If values is an array then the array is assumed to contain the columns to return. Otherwise the arguments will be used.

  Returns
  • patio.Dataset

    a new dataset with the retuning option added.

  Source

  function (values){
     var args;
     if (Array.isArray(values)) {
         args = values;
     } else {
         args = argsToArray(arguments);
     }
     return this.mergeOptions({returning: args.map(function (v) {
         return isString(v) ? sql.stringToIdentifier(v) : v;
     })});
             
  }
      

  reverse Function Public

  ---

  Defined dataset/query.js

  Returns a copy of the dataset with the order reversed. If no order is given, the existing order is inverted.

  Example

  DB.from("items").reverse("id");
       //=> SELECT * FROM items ORDER BY id DESC
  
  DB.from("items").order("id").reverse();
       //=> SELECT * FROM items ORDER BY id DESC
  
  DB.from("items").order("id").reverse(sql.identifier("name").asc);
       //=> SELECT * FROM items ORDER BY name ASC
          

  Arguments
  • args :

    variable number of columns add to order before reversing.

  Returns
  • patio.Dataset

    a cloned dataset with the order reversed.

  Source

  function (args){
     args = argsToArray(arguments);
     return this.order.apply(this, this._invertOrder(args.length ? args : this.__opts.order));
             
  }
      

  reverseOrder Function Public

  ---

  Defined dataset/query.js
  Source

  function (){
     return this.reverse.apply(this, arguments);
             
  }
      

  save Function Public

  ---

  Defined dataset/actions.js
  Source

  function (){
     return this.insert.apply(this, arguments);
             
  }
      

  saveMultiple Function Public

  ---

  Defined dataset/actions.js
  Source

  function (){
     return this.insertMultiple.apply(this, arguments);
             
  }
      

  schemaAndTable Function Private

  ---

  Defined dataset/sql.js
  Arguments
  • tableName :
  Source

  function (tableName){
     var sch = this.db ? this.db.defaultSchema || null : null;
     if (isString(tableName)) {
         var parts = this._splitString(tableName);
         var s = parts[0], table = parts[1];
         return [s || sch, table];
     } else if (isInstanceOf(tableName, QualifiedIdentifier)) {
         return [tableName.table, tableName.column];
     } else if (isInstanceOf(tableName, Identifier)) {
         return [null, tableName.value];
     } else if (isInstanceOf(tableName, LiteralString)) {
         return [null, tableName];
     } else {
         throw new QueryError("table should be a QualifiedIdentifier, Identifier, or String");
     }
             
  }
      

  select Function Public

  ---

  Defined dataset/query.js

  Returns a copy of the dataset with the columns selected changed to the given columns. This also takes a function similar to patio.Dataset#filter

  Example

  DB.from("items").select("a");
       //=> SELECT a FROM items
  
    DB.from("items").select("a", "b");
       //=> SELECT a, b FROM items
  
    DB.from("items").select("a", function(){
           return this.sum("b")
    }).sql;  //=> SELECT a, sum(b) FROM items
          

  Arguments
  • args :

    variable number of colums to select

  Returns
  • patio.Dataset

    a cloned dataset with the columns selected changed.

  Source

  function (args){
     args = flatten(argsToArray(arguments));
     var columns = [];
     args.forEach(function (c) {
         if (isFunction(c)) {
             var res = c.apply(sql, [sql]);
             columns = columns.concat(isArray(res) ? res : [res]);
         } else {
             columns.push(c);
         }
     });
     var select = [];
     columns.forEach(function (c) {
         if (isHash(c)) {
             for (var i in c) {
                 select.push(new AliasedExpression(this.stringToIdentifier(i), c[i]));
             }
         } else if (isString(c)) {
             select.push(this.stringToIdentifier(c));
         } else {
             select.push(c);
         }
     }, this);
     return this.mergeOptions({select: select});
             
  }
      

  selectAll Function Public

  ---

  Defined dataset/query.js

  Returns a cloned dataset that selects *.

  Returns
  • patio.Dataset

    a cloned dataset that selects *.

  Source

  function (){
     return this.mergeOptions({select: null});
             
  }
      

  selectAppend Function Public

  ---

  Defined dataset/query.js

  Returns a copy of the dataset with the given columns added to the existing selected columns. If no columns are currently selected, it will select the columns given in addition to *.

  Example

  DB.from("items").select("a").selectAppend("b").sql;
       //=> SELECT b FROM items
  
    DB.from("items").select("a").selectAppend("b", "c", "d").sql
       //=> SELECT a, b, c, d FROM items
  
    DB.from("items").selectAppend("b").sql
       //=> SELECT *, b FROM items
          

  Arguments
  • cols :
  • ...? :

    cols variable number of columns to add to the select statement

  Returns
  • patio.Dataset

    returns a cloned dataset with the new select columns appended.

  Source

  function (cols){
     cols = argsToArray(arguments);
     var currentSelect = this.__opts.select;
     if (!currentSelect || !currentSelect.length) {
         currentSelect = [this._static.WILDCARD];
     }
     return this.select.apply(this, currentSelect.concat(cols));
             
  }
      

  selectHash Function Public

  ---

  Defined dataset/actions.js

  Returns a promise resolved with a hash with keyColumn values as keys and valueColumn values as values. Similar to patio.Dataset#toHash, but only selects the two columns.

  Example

  // SELECT id, name FROM table
   DB.from("table").selectHash("id", "name").chain(function(hash){
    // e.g {1 : 'a', 2 : 'b', ...}
   });
          

  Arguments
  • keyColumn :

    the column to use as the key in the hash.

  • valueColumn :

    the column to use as the value in the hash.

  • cb? :

    the callback to invoke when the action is done.

  Returns
  • comb.Promise

    a promise that is resolved with an array of hashes, that have the keyColumn as the key and the valueColumn as the value.

  Source

  function (keyColumn,valueColumn,cb){
     var map = {}, args = comb.argsToArray(arguments);
     cb = isFunction(args[args.length - 1]) ? args.pop() : null;
     var k = this.__hashIdentifierToName(keyColumn),
         v = this.__hashIdentifierToName(valueColumn);
     return this.select.apply(this, args).map(function (r) {
         map[r[k]] = v ? r[v] : r;
     }).chain(function () {
         return map;
     }).classic(cb).promise();
             
  }
      

  selectIfNoSource Function Public

  ---

  Defined dataset/query.js

  Selects the columns if only if there is not already select sources.

  Example

  var ds = DB.from("items"); //SELECT * FROM items
  
  ds.select("a"); //SELECT a FROM items;
  ds.select("a").selectIfNoSource("a", "b"). //SELECT a FROM items;
  ds.selectIfNoSource("a", "b"). //SELECT a, b FROM items;
          

  Arguments
  • cols :

    columns to select if there is not already select sources.

  Returns
  • patio.Dataset

    a cloned dataset with the appropriate select sources.

  Source

  function (cols){
     var ret;
     if (!this.hasSelectSource) {
         ret = this.select.apply(this, arguments);
     } else {
         ret = this.mergeOptions();
     }
     return ret;
             
  }
      

  selectMap Function Public

  ---

  Defined dataset/actions.js

  Selects the column given (either as an argument or as a callback), and returns an array of all values of that column in the dataset. If you give a block argument that returns an array with multiple entries, the contents of the resulting array are undefined.

  Example

  // SELECT id FROM table
   DB.from("table").selectMap("id").chain(function(selectMap){
    // e,g. selectMap === [3, 5, 8, 1, ...]
  });
  
  // SELECT abs(id) FROM table
  DB.from("table").selectMap(function(){
       return this.abs("id");
  }).chain(function(selectMap){
    //e.g selectMap === [3, 5, 8, 1, ...]
  });
          

  Arguments
  • column :

    The column to return the values for. See patio.Dataset#select for valid column values.

  • cb? :

    a function to be called when the aciton is complete

  Returns
  • comb.Promise

    a promise resolved with the array of mapped values.

  Source

  function (column,cb){
     var ds = this.naked().ungraphed().select(column), col;
     return ds.map(function (r) {
         return r[col || (col = Object.keys(r)[0])];
     }, cb);
             
  }
      

  selectMore Function Public

  ---

  Defined dataset/query.js

  Returns a copy of the dataset with the given columns added to the existing selected columns. If no columns are currently selected it will just select the columns given.

  Example

  DB.from("items").select("a").select("b").sql;
       //=> SELECT b FROM items
  
    DB.from("items").select("a").selectMore("b", "c", "d").sql
       //=> SELECT a, b, c, d FROM items
  
    DB.from("items").selectMore("b").sql
       //=> SELECT b FROM items
          

  Arguments
  • cols :
  • ...? :

    cols variable number of columns to add to the select statement

  Returns
  • patio.Dataset

    returns a cloned dataset with the new select columns appended.

  Source

  function (cols){
     cols = argsToArray(arguments);
     var currentSelect = this.__opts.select;
     return this.select.apply(this, (currentSelect || []).concat(cols));
             
  }
      

  selectOrderMap Function Public

  ---

  Defined dataset/actions.js

  The same as patio.Dataset#selectMap, but in addition orders the array by the column.

  Example

  // SELECT id FROM table ORDER BY id
  DB.from("table").selectOrderMap("id").chain(function(mappedIds){
    //e.g. [1, 2, 3, 4, ...]
  });
  
   // SELECT abs(id) FROM table ORDER BY abs(id)
   DB.from("table").selectOrderMap(function(){
           return this.abs("id");
   }).chain(function(mappedIds){
       //e.g. [1, 2, 3, 4, ...]
   });
          

  Arguments
  • column :

    The column to return the values for. See patio.Dataset#select for valid column values.

  • cb :
  Returns
  • comb.Promise

    a promise resolved with the array of mapped values.

  Source

  function (column,cb){
     var col, ds = this.naked()
         .ungraphed()
         .select(column)
         .order(isFunction(column) ? column : this._unaliasedIdentifier(column));
     return ds.map(function (r) {
         return r[col || (col = Object.keys(r)[0])];
     }, cb);
             
  }
      

  set Function Public

  ---

  Defined dataset/actions.js
  Source

  function (){
     this.update.apply(this, arguments);
             
  }
      

  setDefaults Function Public

  ---

  Defined dataset/query.js

  Set the default values for insert and update statements. The values hash passed to insert or update are merged into this hash, so any values in the hash passed to insert or update will override values passed to this method.

  Example

  DB.from("items").setDefaults({a : 'a', c : 'c'}).insert({a : 'd', b : 'b'}).insertSql();
       //=> INSERT INTO items (a, c, b) VALUES ('d', 'c', 'b')
          

  Arguments
  • hash :

    object with key value pairs to use as override values

  Returns
  • patio.Dataset

    a cloned dataset with the defaults added to the current datasets defaults.

  Source

  function (hash){
     return this.mergeOptions({defaults: merge({}, this.__opts.defaults || {}, hash)});
             
  }
      

  setGraphAliases Function Public

  ---

  Defined dataset/graph.js

  This allows you to manually specify the graph aliases to use when using graph. You can use it to only select certain columns, and have those columns mapped to specific aliases in the result set. This is the equivalent of patio.Dataset#select for a graphed dataset, and must be used instead of patio.Dataset#select whenever graphing is used.

  Example

  var DB = patio.defaultDatabase, ds = DB.from("artists");
  var p = comb.executeInOrder(ds, function(ds){
      var graphedDs = ds.graph("albums", {artist_id : id});
      //SELECT artists.name AS artist_name, albums.name AS album_name, 42 AS forty_two FROM table
      return graphedDs.setGraphAliases({artist_name : ["artists", "name"],
                        album_name : ["albums", "name"],
                        forty_two : ["albums", "fourtwo", 42]).first();
  });
  
  p.chain(function(obj){
     //obj == {artists : {name : artists.name}, albums : {name : albums.name, fourtwo : 42}}
  });
          

  Arguments
  • graphAliases :

    Should be a hash with keys being column aliases, and values being arrays with two or three elements. The first element of the array should be the table alias, and the second should be the actual column name. If the array has a third element, it is used as the value returned, instead of tableAlias.columnName.

  Source

  function (graphAliases){
     var ds = this.select.apply(this, this.__graphAliasColumns(graphAliases));
     ds.__opts.graphAliases = graphAliases;
     return ds;
             
  }
      

  setOverrides Function Public

  ---

  Defined dataset/query.js

  Set values that override hash arguments given to insert and update statements. This hash is merged into the hash provided to insert or update, so values will override any values given in the insert/update hashes.

  Example

  DB.from("items").setOverrides({a : 'a', c : 'c'}).insert({a : 'd', b : 'b'}).insertSql();
       //=> INSERT INTO items (a, c, b) VALUES ('a', 'c', 'b')
          

  Arguments
  • hash :

    object with key value pairs to use as override values

  Returns
  • patio.Dataset

    a cloned dataset with the overrides added to the current datasets overrides.

  Source

  function (hash){
     return this.mergeOptions({overrides: merge({}, this.__opts.overrides || {}, hash)});
             
  }
      

  singleRecord Function Public

  ---

  Defined dataset/actions.js

  Returns a promise resolved with the first record in the dataset, or null if the dataset has no records. Users should probably use patio.Dataset#first instead of this method.

  Example

  //'SELECT * FROM test LIMIT 1'
  DB.from("test").singleRecord().chain(function(r) {
      //e.g r === {id : 1, name : "firstName"}
  });
          

  Arguments
  • cb? :

    a function to be called when the aciton is complete

  Returns
  • comb.Promise

    a promise resolved with the first record returned from the query.

  Source

  function (cb){
     return this.mergeOptions({limit: 1}).all().chain(function (r) {
         return r && r.length ? r[0] : null;
     }).classic(cb).promise();
             
  }
      

  singleValue Function Public

  ---

  Defined dataset/actions.js

  Returns a promise resolved with the first value of the first record in the dataset. Returns null if dataset is empty. Users should generally use patio.Dataset#get instead of this method.

  Example

  //'SELECT * FROM test LIMIT 1'
  DB.from("test").singleValue().chain(function(r) {
      //e.g r === 1
  });
          

  Arguments
  • cb? :

    the callback to invoke when the action is done.

  Returns
  • comb.Promise

    a promise that will be resolved with the first value of the first row returned from the dataset.

  Source

  function (cb){
     return this.naked().ungraphed().singleRecord().chain(function (r) {
         return r ? r[Object.keys(r)[0]] : null;
     }).classic(cb).promise();
             
  }
      

  stream Function Public

  ---

  Defined dataset/actions.js

  Returns a Stream for streaming data from the database.

  User
    .stream()
    .on("data", function(record){
        console.log(record);
     })
    .on("error", errorHandler)
    .on("end", function(){
       console.log("all done")
    });
  
  //postgres options
  User
    .stream({batchSize: 100, highWaterMark: 1000})
    .on("data", function(record){
        console.log(record);
     })
    .on("error", errorHandler)
    .on("end", function(){
       console.log("all done")
    });

  Arguments
  • opts :

    an object to pass to the adapters connection stream implementation

  Returns
  • Stream
  Source

  function (opts){
     var queryStream = this.fetchRows(this.selectSql, merge(opts || {}, {stream: true})), rowCb, ret;
     if ((rowCb = this.rowCb)) {
         ret = new TransformStream({objectMode: true});
         ret._transform = function (data, encoding, done) {
             when(rowCb(data)).chain(function (data) {
                 ret.push(data);
                 done();
             }, done);
         };
         pipeAll(queryStream, ret);
     } else {
         ret = queryStream;
     }
     return ret;
             
  }
      

  stringToIdentifier Function Public

  ---

  Defined dataset/index.js

  Converts a string to an patio.sql.Identifier, patio.sql.QualifiedIdentifier, or patio.sql.AliasedExpression, depending on the format:

  • For columns : tablecolumn_alias.
  • For tables : schematable_alias.
  each portion of the identifier is optional. See example below
  Example

  ds.stringToIdentifier("a") //= > new patio.sql.Identifier("a");
  ds.stringToIdentifier("table__column"); //=> new patio.sql.QualifiedIdentifier(table, column);
  ds.stringToIdentifier("table__column___alias");
       //=> new patio.sql.AliasedExpression(new patio.sql.QualifiedIdentifier(table, column), alias);
          

  Arguments
  • name :

    the name to covert to an an patio.sql.Identifier, patio.sql.QualifiedIdentifier, or patio.sql.AliasedExpression.

  Returns
  • patio.sql.Identifier|patio.sql.QualifiedIdentifier|patio.sql.AliasedExpression

    an identifier generated based on the name string.

  Source

  function (name){
     if (isString(name)) {
         var parts = this._splitString(name),
             schema = parts[0], table = parts[1], alias = parts[2],
             identifier;
         if (schema && table && alias) {
             identifier = new AliasedExpression(new QualifiedIdentifier(schema, table), alias);
         } else if (schema && table) {
             identifier = new QualifiedIdentifier(schema, table);
         } else if (table && alias) {
             identifier = new AliasedExpression(new Identifier(table), alias);
         } else {
             identifier = new Identifier(table);
         }
         return identifier;
     } else {
         return name;
     }
             
  }
      

  subscriptSql Function Private

  ---

  Defined dataset/sql.js
  Arguments
  • s :
  Source

  function (s){
     return string.format("%s[%s]", this.literal(s.f), this.__expressionList(s.sub));
             
  }
      

  sum Function Public

  ---

  Defined dataset/actions.js

  Returns a promise resolved the sum for the given column.

  Example

  // SELECT sum(id) FROM table LIMIT 1
  DB.from("table").sum("id").chain(function(sum){
    // e.g sum === 55
  });
          

  Arguments
  • column :

    the column to find the sum of.

  • cb? :

    the callback to invoke when the action is done.

  Returns
  • comb.Promise

    a promise resolved with the sum of the column.

  Source

  function (column,cb){
     return this.__aggregateDataset().get(sql.sum(this.stringToIdentifier(column)), cb);
             
  }
      

  toCsv Function Public

  ---

  Defined dataset/actions.js

  Returns a promise resolved with a string in CSV format containing the dataset records. By default the CSV representation includes the column titles in the first line. You can turn that off by passing false as the includeColumnTitles argument.

  NOTE: This does not use a CSV library or handle quoting of values in any way. If any values in any of the rows could include commas or line endings, you shouldn't use this.

  Example

  // SELECT * FROM table
   DB.from("table").toCsv().chain(function(csv){
       console.log(csv);
       //outputs
         id,name
         1,Jim
         2,Bob
   });
  
     // SELECT * FROM table
   DB.from("table").toCsv(false).chain(function(csv){
       console.log(csv);
       //outputs
         1,Jim
         2,Bob
   });
          

  Arguments
  • [includeColumnTitles= true] :

    Set to false to prevent the printing of the column titles as the first line.

  • cb? :

    the callback to invoke when the action is done.

  Returns
  • comb.Promise

    a promise that will be resolved with the CSV string of the results of the query.

  Source

  function (includeColumnTitles,cb){
     var n = this.naked();
     if (isFunction(includeColumnTitles)) {
         cb = includeColumnTitles;
         includeColumnTitles = true;
     }
     includeColumnTitles = isBoolean(includeColumnTitles) ? includeColumnTitles : true;
     return n.columns.chain(function (cols) {
         var vals = [];
         if (includeColumnTitles) {
             vals.push(cols.join(", "));
         }
         return n.forEach(function (r) {
             vals.push(cols.map(function (c) {
                 return r[c] || "";
             }).join(", "));
         }).chain(function () {
             return vals.join("\r\n") + "\r\n";
         });
     }.bind(this)).classic(cb).promise();
             
  }
      

  toHash Function Public

  ---

  Defined dataset/actions.js

  Returns a promise resolved with a hash with one column used as key and another used as value. If rows have duplicate values for the key column, the latter row(s) will overwrite the value of the previous row(s). If the valueColumn is not given or null, uses the entire hash as the value.

  Example

  // SELECT * FROM table
   DB.from("table").toHash("id", "name").chain(function(hash){
     // {1 : 'Jim', 2 : 'Bob', ...}
   });
  
  // SELECT * FROM table
  DB.from("table").toHash("id").chain(function(hash){
    // {1 : {id : 1, name : 'Jim'}, 2 : {id : 2, name : 'Bob'}, ...}
  });
          

  Arguments
  • keyColumn :

    the column to use as the key in the returned hash.

  • valueColumn :
  • cb? :

    the callback to invoke when the action is done.

  • [keyValue= null] String|patio.sql.Identifier\patio.sql.QualifiedIdentifier|patio.sql.AliasedExpression :

    the key of the column to use as the value in the hash

  Returns
  • comb.Promise

    a promise that will be resolved with the resulting hash.

  Source

  function (keyColumn,valueColumn,cb){
     var ret = new Promise(), map = {};
     if (isFunction(valueColumn)) {
         cb = valueColumn;
         valueColumn = null;
     }
     var k = this.__hashIdentifierToName(keyColumn), v = this.__hashIdentifierToName(valueColumn);
     return this.map(function (r) {
         map[r[k]] = v ? r[v] : r;
     }).chain(function () {
         return map;
     }).classic(cb).promise();
             
  }
      

  truncate Function Public

  ---

  Defined dataset/actions.js

  Truncates the dataset. Returns a promise that is resolved once truncation is complete.

  Example

  // TRUNCATE table
  DB.from("table").truncate().chain(function(){
      //...do something
  });
          

  Arguments
  • cb? :

    the callback to invoke when the action is done.

  Returns
  • comb.Promise

    a promise that is resolved once truncation is complete.

  Source

  function (cb){
     return this.executeDdl(this.truncateSql).classic(cb);
             
  }
      

  unfiltered Function Public

  ---

  Defined dataset/query.js

  Returns a copy of the dataset with no filters (HAVING or WHERE clause) applied.

  Example

  DB.from("items").group("a").having({a : 1}).where("b").unfiltered().sql;
       //=> SELECT * FROM items GROUP BY a
          

  Returns
  • patio.Dataset

    a cloned dataset with no HAVING or WHERE clause.

  Source

  function (){
     return this.mergeOptions({where: null, having: null});
             
  }
      

  ungraphed Function Public

  ---

  Defined dataset/graph.js

  Remove the splitting of results into subhashes, and all metadata related to the current graph (if any).

  Source

  function (){
     return this.mergeOptions({graph: null});
             
  }
      

  ungrouped Function Public

  ---

  Defined dataset/query.js

  Returns a copy of the dataset with no GROUP or HAVING clause.

  Example

  DB.from("t").group("a").having({a : 1}).where("b").ungrouped().sql;
       //=> SELECT * FROM t WHERE b
          

  Returns
  • patio.Dataset

    a cloned dataset with no GROUP or HAVING clause.

  Source

  function (){
     return this.mergeOptions({group: null, having: null});
             
  }
      

  union Function Public

  ---

  Defined dataset/query.js

  Adds a UNION clause using a second dataset object. A UNION compound dataset returns all rows in either the current dataset or the given dataset. Options: :alias :: Use the given value as the from_self alias :all :: Set to true to use UNION ALL instead of UNION, so duplicate rows can occur :from_self :: Set to false to not wrap the returned dataset in a from_self, use with care.

  Example

  DB.from("items").union(DB.from("otherItems")).sql;
       //=> SELECT * FROM items UNION SELECT * FROM other_items
  
  DB.from("items").union(DB.from("otherItems"), {all : true, fromSelf : false}).sql;
       //=> SELECT * FROM items UNION ALL SELECT * FROM other_items
  
  DB.from("items").union(DB.from("otherItems"), {alias : "i"})
       //=> SELECT * FROM (SELECT * FROM items UNION SELECT * FROM other_items) AS i
          

  Arguments
  • dataset :

    dataset to union with

  • opts :

    addional options

  • opts.alias? String|patio.sql.Identifier :

    Alias to use as the fromSelf alias.

  • [opt.all= false] Boolean :

    Set to true to use UNION ALL instead of UNION so duplicate rows can occur

  • [opts.fromSelf= true] Boolean :

    Set to false to not wrap the returned dataset in a fromSelf.

  Returns
  • patio.Dataset

    a cloned dataset with the union.

  Source

  function (dataset,opts){
     opts = isUndefined(opts) ? {} : opts;
     if (!isHash(opts)) {
         opts = {all: opts};
     }
     return this.compoundClone("union", dataset, opts);
             
  }
      

  unlimited Function Public

  ---

  Defined dataset/query.js

  Returns a copy of the dataset with no limit or offset.

  Example

  DB.from("t").limit(10, 20).unlimited().sql;
       //=> SELECT * FROM t
          

  Returns
  • patio.Dataset

    a cloned dataset with no limit or offset.

  Source

  function (){
     return this.mergeOptions({limit: null, offset: null});
             
  }
      

  unordered Function Public

  ---

  Defined dataset/query.js

  Returns a copy of the dataset with no order.

  Example

  DB.from("t").order("a", sql.identifier("b").desc()).unordered().sql;
       //=> SELECT * FROM t
          

  Returns
  • patio.Dataset

    a cloned dataset with no order.

  Source

  function (){
     return this.order(null);
             
  }
      

  unusedTableAlias Function Public

  ---

  Defined dataset/sql.js

  Creates a unique table alias that hasn't already been used in this dataset.

  Example

  DB.from("table").unusedTableAlias("t");
        //=> "t"
  
  DB.from("table").unusedTableAlias("table");
    //=> "table0"
  
  DB.from("table", "table0"]).unusedTableAlias("table");
    //=> "table1"
          

  Arguments
  • tableAlias :

    the table to get an unused alias for.

  Returns
  • String

    the implicit alias that is in tableAlias with a possible "N" if the alias has already been used, where N is an integer starting at 0.

  Source

  function (tableAlias){
     tableAlias = this._toTableName(tableAlias);
     var usedAliases = [], from, join;
     if ((from = this.__opts.from) != null) {
         usedAliases = usedAliases.concat(from.map(function (n) {
             return this._toTableName(n);
         }, this));
     }
     if ((join = this.__opts.join) != null) {
         usedAliases = usedAliases.concat(join.map(function (join) {
             if (join.tableAlias) {
                 return this.__toAliasedTableName(join.tableAlias);
             } else {
                 return this._toTableName(join.table);
             }
         }, this));
     }
     if (usedAliases.indexOf(tableAlias) !== -1) {
         var base = tableAlias, i = 0;
         do {
             tableAlias = string.format("%s%d", base, i++);
         } while (usedAliases.indexOf(tableAlias) !== -1);
     }
     return tableAlias;
             
  }
      

  update Function Public

  ---

  Defined dataset/actions.js

  Updates values for the dataset. The returned promise is resolved with a value that is generally the number of rows updated, but that is adapter dependent.

  Example

  // UPDATE table SET x = NULL
   DB.from("table").update({x : null}).chain(function(numRowsUpdated){
       //e.g. numRowsUpdated === 10
   });
  
  // UPDATE table SET x = (x + 1), y = 0
  DB.from("table").update({ x : sql.x.plus(1), y : 0}).chain(function(numRowsUpdated){
    // e.g. numRowsUpdated === 10
  });
          

  Arguments
  • values :

    See patio.Dataset#updateSql for parameter types.

  • cb? :

    the callback to invoke when the action is done.

  Returns
  • comb.Promise

    a promise that is generally resolved with the number of rows updated, but that is adapter dependent.

  Source

  function (values,cb){
     return this.executeDui(this.updateSql(values)).classic(cb);
             
  }
      

  updateSql Function Public

  ---

  Defined dataset/sql.js

  Formats an UPDATE statement using the given values. See patio.dataset._Actions#update.

  Example

  DB.from("items").updateSql({price : 100, category : 'software'});
       //=> "UPDATE items SET price = 100, category = 'software'
          

  Arguments
  • values :
  • Variable *... :

    number of values to update the table with. The UPDATE statement created depends on the values passed in.

    • Object : the keys will be used as the columns and the values will be the values to set to columns to
    • patio.sql.Expression : the patio.dataset._Sql#literal representation of the patio.sql.Expression will be used as the value
    Other : the patio.dataset._Sql#literal value will be used as the value

  Returns
  • String

    the UPDATE statement.

  Throws
  • QueryError

    If the dataset is grouped or includes more than one table.

  Source

  function (values){
     values = argsToArray(arguments);
     var update;
     if (this.__opts.sql) {
         update = this._staticSql(this.__opts.sql);
     } else {
         this.__checkModificationAllowed();
         update = this.mergeOptions({values: values})._updateSql();
     }
     return update;
             
  }
      

  where Function Public

  ---

  Defined dataset/query.js

  Add a condition to the WHERE clause. See patio.Dataset#filter for argument types.

  Example

  DB.from("test").where('price < ? AND id in ?', 100, [1, 2, 3]).sql;
       //=> "SELECT * FROM test WHERE (price < 100 AND id in (1, 2, 3))"
    DB.from("test").where('price < {price} AND id in {ids}', {price:100, ids:[1, 2, 3]}).sql;
       //=> "SELECT * FROM test WHERE (price < 100 AND id in (1, 2, 3))")
          

  Source

  function (){
     return this._filter.apply(this, ["where"].concat(argsToArray(arguments)));
             
  }
      

  with Function Public

  ---

  Defined dataset/query.js

  Add a common table expression (CTE) with the given name and a dataset that defines the CTE. A common table expression acts as an inline view for the query.

  Example

  DB.from("t")["with"]("t", db.from("x"))["with"]("j", db.from("y")).sql;
       //=> 'WITH t AS (SELECT * FROM x), j AS (SELECT * FROM y) SELECT * FROM t'
  
  DB.from("t")["with"]("t", db.from("x")).withRecursive("j", db.from("y"), db.from("j")).sql;
       //=> 'WITH t AS (SELECT * FROM x), j AS (SELECT * FROM y UNION ALL SELECT * FROM j) SELECT * FROM t'
  
  DB.from("t")["with"]("t", db.from("x"), {args:["b"]}).sql;
       //=> 'WITH t(b) AS (SELECT * FROM x) SELECT * FROM t'
          

  Arguments
  • name :

    the name of the to assign to the CTE.

  • dataset :

    the dataset to use for the CTE.

  • opts :

    extra options.

  • opts.args? String[] :

    colums/args for the CTE.

  • opts.recursive? Boolean :

    set to true that the CTE is recursive.

  Returns
  • patio.Dataset

    a cloned dataset with the CTE.

  Source

  function (name,dataset,opts){
     if (!this.supportsCte) {
         throw new QueryError("this dataset does not support common table expressions");
     }
     return this.mergeOptions({
         "with": (this.__opts["with"] || []).concat([merge(opts || {}, {name: this.stringToIdentifier(name), dataset: dataset})])
     });
             
  }
      

  withRecursive Function Public

  ---

  Defined dataset/query.js

  Add a recursive common table expression (CTE) with the given name, a dataset that defines the nonrecursive part of the CTE, and a dataset that defines the recursive part of the CTE.

  Example

  //Sing withRecursive call.
  DB.from("t").withRecursive("t", db.from("x"), db.from("t")).sql;
           //=> 'WITH t AS (SELECT * FROM x UNION ALL SELECT * FROM t) SELECT * FROM t'
  
  //Multiple withRecursive calls.
  DB.from("t").withRecursive("t", db.from("x"), db.from("t"))
     .withRecursive("j", db.from("y"), db.from("j")).sql;
           //=> 'WITH t AS (SELECT * FROM x UNION ALL SELECT * FROM t),
                      j AS (SELECT * FROM y UNION ALL SELECT * FROM j) SELECT * FROM t';
  
  //Adding args
  DB.from("t").withRecursive("t", db.from("x"), db.from("t"), {args:["b", "c"]}).sql;
           //=> 'WITH t(b, c) AS (SELECT * FROM x UNION ALL SELECT * FROM t) SELECT * FROM t'
  
  //Setting union all to false
  DB.from("t").withRecursive("t", db.from("x"), db.from("t"), {unionAll:false}).sql;
           //=> 'WITH t AS (SELECT * FROM x UNION SELECT * FROM t) SELECT * FROM t');
          

  Arguments
  • name :

    the name to assign to the CTE

  • nonRecursive :

    the non-recursive part of the CTE

  • recursive :

    the recursive part of the CTE

  • [opts= {}] :

    extra options

  • opts.args? String[] :

    columns to include with the CTE

  • opts.unionAll? Boolena :

    set to false to use UNION instead of UNION ALL when combining non recursive with recursive.

  Returns
  • patio.Dataset

    a cloned dataset with the CTE.

  Source

  function (name,nonRecursive,recursive,opts){
     if (!this.supportsCte) {
         throw new QueryError("This dataset does not support common table expressions");
     }
     opts = opts || {};
     var wit = (this.__opts["with"] || []).concat([merge(opts, {recursive: true, name: this.stringToIdentifier(name), dataset: nonRecursive.union(recursive, {all: opts.unionAll !== false, fromSelf: false})})]);
     return this.mergeOptions({"with": wit});
             
  }
      

  withSql Function Public

  ---

  Defined dataset/query.js

  Returns a copy of the dataset with the static SQL used. This is useful if you want to keep the same patio.Dataset#rowCb/ patio.Dataset#graph, but change the SQL used to custom SQL.

  Example

  DB.from("items").withSql('SELECT * FROM foo')
           //=> SELECT * FROM foo
          

  Arguments
  • sql :

    sql for the dataset to use.

  Returns
  • patio.Dataset

    a cloned dataset with the static sql set.

  Source

  function (sql){
     var args = argsToArray(arguments).slice(1);
     if (args.length) {
         sql = new PlaceHolderLiteralString(sql, args);
     }
     return this.mergeOptions({sql: sql});
             
  }
      

  ---

  Documentation generated using coddoc.