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

Dynamically generated methods include

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:

Static Properties
PropertyTypeDefault ValueDescription
ACTION_METHODSString[] ['all', 'one', 'avg', 'count', 'columns', 'remove', 'forEach', 'isEmpty', 'first', 'get', 'import', 'insert', 'save', 'insertMultiple', 'saveMultiple', 'interval', 'last', 'map', 'max', 'min', 'multiInsert', 'range', 'selectHash', 'selectMap', 'selectOrderMap', 'set', 'singleRecord', 'singleValue', 'sum', 'toCsv', 'toHash', 'truncate', 'update', 'stream']

List of action methods avaiable on the dataset.

AND_SEPARATORproperty" AND "

Default SQL AND separator.

BOOL_FALSEproperty"'f'"

Default SQL boolean false operator.

BOOL_TRUEproperty"'t'"

Default SQL boolean true operator.

COLUMN_CHANGE_OPTSproperty["select", "sql", "from", "join"]

The dataset options that require the removal of cached columns if changed.

COMMA_SEPARATORproperty'

Default SQL comma sperator.

CONDITIONED_JOIN_TYPESproperty["inner", "fullOuter", "rightOuter", "leftOuter", "full", "right", "left"]

These strings have {name}Join methods created (e.g. patio.Dataset#innerJoin) that call patio.Dataset#joinTable with the string, passing along the arguments and block from the method call.

COUNT_FROM_SELF_OPTSString[] ["distinct", "group", "sql", "limit", "compounds"]

List of options that can interfere with the aggregation of a patio.Dataset

COUNT_OF_ALL_AS_COUNTpropertysql.count(sql.literal('*')).as("count")

Default COUNT expression.

DATASET_ALIAS_BASE_NAMEproperty't'

Default alias for datasets.

DELETE_CLAUSE_METHODSpropertyclauseMethods("delete"

Default SQL DELETE clause methods. This may be overrode by adapters.

FEATURESproperty ["quoteIdentifiers","providesAccurateRowsMatched","requiresSqlStandardDateTimes","supportsCte", "supportsDistinctOn","supportsIntersectExcept","supportsIntersectExceptAll","supportsIsTrue","supportsJoinUsing", "supportsModifyingJoins","supportsMultipleColumnIn","supportsTimestampTimezones","supportsTimestampUsecs", "supportsWindowFunctions"] Array of features.
FOR_UPDATEproperty' FOR UPDATE'

Default FOR UPDATE SQL fragment.

FROMproperty"FROM"

Default FROM clause

INSERT_CLAUSE_METHODSpropertyclauseMethods("insert"

Default SQL INSERT clause. This may be overrode by adapters.

IS_LITERALSproperty{NULL: 'NULL', true: 'TRUE', false: 'FALSE'}

Hash of IS literals

IS_OPERATORSpropertyComplexExpression.IS_OPERATORS

Defaults IS OPERATORS. See patio.sql.ComplexExpression.IS_OPERATORS.

JOIN_METHODSproperty["join", "joinTable"]

All methods that return modified datasets with a joined table added.

NON_SQL_OPTIONSproperty["server", "defaults", "overrides", "graph", "eagerGraph", "graphAliases"]

Which options don't affect the SQL generation. Used by patio.Dataset#simpleSelectAll to determine if this is a simple SELECT * FROM table.

NULLproperty"NULL"

Defaults SQL NULL.

N_ARITY_OPERATORSpropertyComplexExpression.N_ARITY_OPERATORS

Defaults N(Multi arity) OPERATORS. See patio.sql.ComplexExpression.N_ARITY_OPERATORS.

QUALIFY_KEYSproperty["select", "where", "having", "order", "group"]

Default SQL clauses that need qualifying. This may be overrode by adapters.

QUERY_METHODSproperty['addGraphAliases', "and", "distinct", "eager", "except", "exclude", "filter", "find", "is", "isNot", "eq", "neq", "lt", "lte", "gt", "gte", "forUpdate", "from", "fromSelf", "graph", "grep", "group", "groupAndCount", "groupBy", "having", "intersect", "invert", "limit", "lockStyle", "naked", "or", "order", "orderAppend", "orderBy", "orderMore", "orderPrepend", "qualify", "reverse", "reverseOrder", "select", "selectAll", "selectAppend", "selectMore", "setDefaults", "setGraphAliases", "setOverrides", "unfiltered", "ungraphed", "ungrouped", "union", "unlimited", "unordered", "where", "with", "withRecursive", "withSql"]

Methods that return modified datasets

QUESTION_MARKproperty/\?/g

Regexp used to replace '?' in patio.sql.PlaceHolderLiteralString

RETURNINGproperty" RETURNING "

Default SQL 'RETURNING' literal string

SELECT_CLAUSE_METHODSpropertyclauseMethods("select"

Default SQL SELECT clause. This may be overrode by adapters.

SPACEproperty" "

Default space to use when building SQL queries

SQL_WITHproperty"WITH"

Default SQL WITH base. This may be overrode by adapters.

TWO_ARITY_OPERATORSpropertyComplexExpression.TWO_ARITY_OPERATORS

Defaults TWO OPERATORS. See patio.sql.ComplexExpression.TWO_ARITY_OPERATORS.

UNCONDITIONED_JOIN_TYPESproperty["natural", "naturalLeft", "naturalRight", "naturalFull", "cross"]

These strings have {name}Join methods created (e.g. naturalJoin) that call patio.Dataset#joinTable. They only accept a single table argument which is passed to patio.Dataset#joinTable, and they throw an error if called with a block.

UPDATE_CLAUSE_METHODSpropertyclauseMethods("update"

Default SQL UPDATE clause. This may be overrode by adapters.

WILDCARDpropertynew LiteralString('*')

Default SQL '*' literal string.

Instance Properties
PropertyTypeDefault ValueDescription
columns{comb.Promise} Returns a promise that is resolved with the columns in the result set in order as an array of strings. If the columns are currently cached, then the promise is immediately resolved with the cached value. Otherwise, a SELECT query is performed to retrieve a single row in order to get the columns. If you are looking for all columns for a single table and maybe some information about each column (e.g. database type), see {@link patio.Database#schema}.
 DB.from("table").columns.chain(function(columns){
       // => ["id", "name"]
  });
function (){ if (this.__columns) { return asyncArray(this.__columns); } else { var ds = this.unfiltered().unordered().mergeOptions({distinct: null, limit: 1}); return asyncArray(ds.forEach().chain(function () { var columns = this.__columns = ds.__columns || []; return columns; }.bind(this))); } }
firstSourceAliasString

The first source (primary table) for this dataset. If the table is aliased, returns the aliased name. throws a {patio.DatasetError} tf the dataset doesn't have a table.

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

firstSourceTableString

The first source (primary table) for this dataset. If the dataset doesn't have a table, raises a patio.erros.DatasetError.

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

hasSelectSourceBoolean

true if this dataset already has a select sources.

identifierInputMethodString

this is the method that will be called on each identifier returned from the database. This value will be defaulted to whatever the identifierInputMethod is on the database used in initialization.

identifierOutputMethodString

this is the method that will be called on each identifier sent to the database. This value will be defaulted to whatever the identifierOutputMethod is on the database used in initialization.

isSimpleSelectAllBoolean

Returns true if this dataset is a simple SELECT * FROM {table}, otherwise false.

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

joinSourceListpatio.sql.Identifier[][]

a list of join sources

providesAccurateRowsMatchedbooleantrue

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.

quoteIdentifiersbooleantrue

Whether this dataset quotes identifiers.

requiresSqlStandardDatebooleanfalse

Whether the dataset requires SQL standard datetimes (false by default, as most allow strings with ISO 8601 format).

rowCbFunction

callback to be invoked for each row returned from the database. the return value will be used as the result of query. The rowCb can also return a promise, The resolved value of the promise will be used as result.

sourceListpatio.sql.Identifier[][]

a list of sources for this dataset.

supportsCtebooleantrue

Whether the dataset supports common table expressions (the WITH clause).

supportsDistinctOnbooleanfalse

Whether the dataset supports the DISTINCT ON clause, false by default.

supportsIntersectExceptbooleantrue

Whether the dataset supports the INTERSECT and EXCEPT compound operations, true by default.

supportsIntersectExceptAllbooleantrue

Whether the dataset supports the INTERSECT ALL and EXCEPT ALL compound operations, true by default.

supportsIsTruebooleantrue

Whether the dataset supports the IS TRUE syntax.

supportsJoinUsingbooleantrue

Whether the dataset supports the JOIN table USING (column1, ...) syntax.

supportsModifyingJoinbooleanfalse

Whether modifying joined datasets is supported.

supportsMultipleColumnInbooleantrue

Whether the IN/NOT IN operators support multiple columns when an

supportsTimestampTimezonebooleanfalse

Whether the dataset supports timezones in literal timestamps

supportsTimestampUsecsbooleantrue

Whether the dataset supports fractional seconds in literal timestamps

supportsWindowFunctionsbooleanfalse

Whether the dataset supports window functions.

Constructor

Defined dataset/index.js Source
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


Defined dataset/actions.js

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

__argumentList Function Private


Defined dataset/sql.js

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

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

__arrayToConditionSpecifier Function Private


Defined dataset/query.js

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


Defined dataset/sql.js

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

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

__columnList Function Private


Defined dataset/sql.js

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

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

__createBetweenExpression Function Private


Defined dataset/query.js

Example
ds.__createBetweenExpression({x : [1,2]}) => //=> WHERE ((x >= 1) AND (x <= 10))
ds.__createBetweenExpression({x : [1,2]}, true) => //=> WHERE ((x < 1) OR (x > 10))
        
Arguments Returns Source
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


Defined dataset/query.js

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

Example
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))
        
Arguments Returns Source
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


Defined dataset/actions.js

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

__expressionList Function Private


Defined dataset/sql.js

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

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

__hashIdentifierToName Function Private


Defined dataset/actions.js

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


Defined dataset/sql.js

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

_addGroupedCondition Function Private


Defined dataset/query.js

Example
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))
        
Arguments Returns Source
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


Defined dataset/sql.js

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.

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

_datasetAlias Function Private


Defined dataset/sql.js

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

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

_deleteFromSql Function Private


Defined dataset/sql.js

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

_deleteOrderSql Function Private


Defined dataset/sql.js

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

_deleteWhereSql Function Private


Defined dataset/sql.js

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

_filter Function Private


Defined dataset/query.js

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


Defined dataset/query.js

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


Defined dataset/query.js

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


Defined dataset/sql.js

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

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

_literalArray Function Private


Defined dataset/sql.js

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

_literalBoolean Function Private


Defined dataset/sql.js

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

_literalBuffer Function Private


Defined dataset/sql.js

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

_literalDataset Function Private


Defined dataset/sql.js

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

_literalDate Function Private


Defined dataset/sql.js

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

_literalExpression Function Private


Defined dataset/sql.js

Arguments Returns Source
function (v){
   return v.toString(this);
           
}
    

_literalHash Function Private


Defined dataset/sql.js

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

_literalJson Function Private


Defined dataset/sql.js

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

_literalNull Function Private


Defined dataset/sql.js

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

_literalNumber Function Private


Defined dataset/sql.js

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

_literalObject Function Private


Defined dataset/sql.js

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

_literalOther Function Private


Defined dataset/sql.js

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


Defined dataset/sql.js

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


Defined dataset/sql.js

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

_literalTimestamp Function Private


Defined dataset/sql.js

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

_literalYear Function Private


Defined dataset/sql.js

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

_optionsOverlap Function Private


Defined dataset/sql.js

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

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


Defined dataset/sql.js

Qualify the given expression to the given table.

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


Defined dataset/sql.js

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

_selectCompoundsSql Function Private


Defined dataset/sql.js

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


Defined dataset/sql.js

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


Defined dataset/sql.js

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

_selectGroupSql Function Private


Defined dataset/sql.js

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

_selectHavingSql Function Private


Defined dataset/sql.js

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

_selectJoinSql Function Private


Defined dataset/sql.js

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


Defined dataset/sql.js

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


Defined dataset/sql.js

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


Defined dataset/sql.js

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

_selectWhereSql Function Private


Defined dataset/sql.js

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

_selectWithSql Function Private


Defined dataset/sql.js

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


Defined dataset/sql.js

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

_sourceList Function Private


Defined dataset/sql.js

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

Arguments 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


Defined dataset/query.js

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

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


Defined dataset/index.js

Can either be a string or null.

Example
//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
        
Arguments Returns Source
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


Defined dataset/sql.js

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

_subselectSql Function Private


Defined dataset/sql.js

Arguments Returns Source
function (ds){
   return ds.sql;
           
}
    

_toTableName Function Private


Defined dataset/sql.js

Returns a string that is the name of the table.

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


Defined dataset/sql.js

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.

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


Defined dataset/sql.js

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

_updateSetSql Function Private


Defined dataset/sql.js

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


Defined dataset/sql.js

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

_updateWhereSql Function Private


Defined dataset/sql.js

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

addGraphAliases Function Public


Defined dataset/graph.js

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.

Example
var DB = patio.defaultDatabase;
 // SELECT ..., table.column AS someAlias
  DB.from("table").addGraphAliases({someAlias : ["table", "column"]);
  //returns from graphing
  // => {table : {column : someAlias_value, ...}, ...}
        
Arguments Returns Source
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


Defined dataset/sql.js

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

all Function Public


Defined dataset/actions.js

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.

Example
// 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;
});
        
Arguments Returns Source
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


Defined dataset/query.js

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

Example
//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)
        
Arguments Returns Source
function (table){
   return this.mergeOptions({alwaysQualify: table || this.firstSourceAlias});
           
}
    

and Function Public


Defined dataset/query.js

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.

Example
DB.from("table").filter("a").and("b").sql;
     //=>SELECT * FROM table WHERE a AND b
        
Returns Throws Source
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


Defined dataset/query.js

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.

Example
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))
        
Arguments Returns Source
function (filterExp){
   return this._addGroupedCondition("AND", "AND", filterExp);
           
}
    

andGroupedOr Function Public


Defined dataset/query.js

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.

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").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)))
        
Arguments Returns Source
function (filterExp){
   return this._addGroupedCondition("AND", "OR", filterExp);
           
}
    

avg Function Public


Defined dataset/actions.js

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

Example
// SELECT avg(number) FROM table LIMIT 1
DB.from("table").avg("number").chain(function(avg){
     // avg === 3
});
        
Arguments Returns Source
function (column,cb){
   return this.__aggregateDataset().get(sql.avg(this.stringToIdentifier(column)), cb);
           
}
    

between Function Public


Defined dataset/query.js

Returns a cloned dataset with a 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 Returns Source
function (obj){
   return this.filter(this.__createBetweenExpression(obj));
           
}
    

booleanConstantSql Function Private


Defined dataset/sql.js

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

caseExpressionSql Function Private


Defined dataset/sql.js

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


Defined dataset/sql.js

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

columnAllSql Function Private


Defined dataset/sql.js

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

complexExpressionSql Function Private


Defined dataset/sql.js

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


Defined dataset/query.js

Add the dataset to the list of compounds

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


Defined dataset/sql.js

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

count Function Public


Defined dataset/actions.js

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

Example
// SELECT COUNT(*) AS count FROM table LIMIT 1
DB.from("table").count().chain(function(count){
    //count === 3;
});
        
Arguments Returns Source
function (cb){
   return this.__aggregateDataset().get(sql.COUNT(sql.literal("*")).as("count")).chain(function (res) {
       return parseInt(res, 10);
   }).classic(cb);
           
}
    

distinct Function Public


Defined dataset/query.js

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.

Example
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
        
Arguments Returns Throws Source
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


Defined dataset/query.js

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.

Example
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},
   ]
  }
 ]
        
Arguments Source
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


Defined dataset/query.js

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

Example
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))
        
Arguments Returns Source
function (obj){
   return this.filter(this.__createBoolExpression("eq", obj));
           
}
    

except Function Public


Defined dataset/query.js

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.

Example
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
        
Arguments Returns Throws Source
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


Defined dataset/query.js

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

Example
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))
        
Returns Source
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


Defined dataset/actions.js

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

executeDdl Function Private


Defined dataset/actions.js

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

executeDui Function Private


Defined dataset/actions.js

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

executeInsert Function Private


Defined dataset/actions.js

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

fetchRows Function Private


Defined dataset/actions.js

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


Defined dataset/query.js

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.

Example
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))
        
Arguments