1.1 --- /dev/null	Thu Jan 01 00:00:00 1970 +0000
     1.2 +++ b/lib/selector.js	Sat Sep 10 23:31:20 2011 +0200
     1.3 @@ -0,0 +1,422 @@
     1.4 +var stringthesizer = require('./stringthesizer.js');
     1.5 +
     1.6 +var quoteLiteral = function(str) {
     1.7 +  if (str.search(/^"[^"]*"/) >= 0) return str;
     1.8 +  else return str.replace(/"/g, '""').replace(/[^\.]+/g, '"$&"');
     1.9 +};
    1.10 +
    1.11 +function SQLQuery() {
    1.12 +}
    1.13 +SQLQuery.prototype.assemble = function(nextPlaceholder) {
    1.14 +  return stringthesizer.stringthesizer(
    1.15 +    {
    1.16 +      nextPlaceholder: nextPlaceholder,
    1.17 +      valueSeparator: ",",
    1.18 +      coerce: function(value) {
    1.19 +        if (value instanceof Selector) return value.toStructure();
    1.20 +        return value;
    1.21 +      }
    1.22 +    },
    1.23 +    this.toStructure()
    1.24 +  );
    1.25 +}
    1.26 +
    1.27 +function Selector(from) {
    1.28 +  this._with = [];
    1.29 +  this._fields = [];
    1.30 +  this._distinct = false;
    1.31 +  this._distinctOn = [];
    1.32 +  this._from = [];
    1.33 +  this._where = [];
    1.34 +  this._groupBy = [];
    1.35 +  this._having = [];
    1.36 +  this._combine = [];
    1.37 +  this._orderBy = [];
    1.38 +  this._limit = null;
    1.39 +  this._offset = null;
    1.40 +  this._readLock = [];
    1.41 +  this._readLockAll = false;
    1.42 +  this._writeLock = [];
    1.43 +  this._writeLockAll = false;
    1.44 +  if (from != null) this.from(from);
    1.45 +}
    1.46 +Selector.prototype = new SQLQuery();
    1.47 +Selector.prototype.addWith = function(expression, selector) {
    1.48 +  this._with.push(['$ AS ($)', expression, selector]);
    1.49 +  return this;
    1.50 +};
    1.51 +Selector.prototype.addDistinctOn = function(expression) {
    1.52 +  if (this._distinct) throw "Cannot combine DISTINCT with DISTINCT ON.";
    1.53 +  this._distinctOn.push(expression);
    1.54 +  return this;
    1.55 +};
    1.56 +Selector.prototype.setDistinct = function() {
    1.57 +  if (this._distinctOn.length > 0) throw "Cannot combine DISTINCT with DISTINCT ON.";
    1.58 +  this._distinct = true;
    1.59 +  return this;
    1.60 +};
    1.61 +Selector.prototype.addFrom = function(expression, alias, condition) {
    1.62 +  var first = this._from.length == 0;
    1.63 +  if (!first) {
    1.64 +    if (condition == null) this._from.push('CROSS JOIN')
    1.65 +    else this._from.push('INNER JOIN')
    1.66 +  }
    1.67 +  if (expression instanceof Selector) {
    1.68 +    if (alias == null) this._from.push(['($) AS "subquery"', expression]);
    1.69 +    else this._from.push(['($) AS "$"', expression, alias]);
    1.70 +  } else {
    1.71 +    if (alias == null) this._from.push(expression);
    1.72 +    else this._from.push(['$ AS "$"', expression, alias]);
    1.73 +  }
    1.74 +  if (condition != null) {
    1.75 +    if (first) {
    1.76 +      this.addWhere(condition);
    1.77 +    } else {
    1.78 +      this._from.push('ON');
    1.79 +      this._from.push(condition);
    1.80 +    }
    1.81 +  }
    1.82 +  return this;
    1.83 +};
    1.84 +Selector.prototype.addWhere = function(expression) {
    1.85 +  this._where.push(['($)', expression]);
    1.86 +  return this;
    1.87 +};
    1.88 +Selector.prototype.addGroupBy = function(expression) {
    1.89 +  this._groupBy.push(expression);
    1.90 +  return this;
    1.91 +};
    1.92 +Selector.prototype.addHaving = function(expression) {
    1.93 +  this._having.push(['($)', expression]);
    1.94 +  return this;
    1.95 +};
    1.96 +Selector.prototype.addCombine = function(expression) {
    1.97 +  this._combine.push(expression);
    1.98 +  return this;
    1.99 +};
   1.100 +Selector.prototype.addOrderBy = function(expression) {
   1.101 +  this._orderBy.push(expression);
   1.102 +  return this;
   1.103 +};
   1.104 +Selector.prototype.limit = function(count) {
   1.105 +  this._limit = count;
   1.106 +  return this;
   1.107 +};
   1.108 +Selector.prototype.offset = function(count) {
   1.109 +  this._offset = count;
   1.110 +  return this;
   1.111 +};
   1.112 +Selector.prototype.forShare = function() {
   1.113 +  this._readLockAll = true;
   1.114 +  return this;
   1.115 +};
   1.116 +Selector.prototype.forShareOf = function(expression) {
   1.117 +  this._readLock.push(expression);
   1.118 +  return this;
   1.119 +};
   1.120 +Selector.prototype.forUpdate = function() {
   1.121 +  this._writeLockAll = true;
   1.122 +  return this;
   1.123 +};
   1.124 +Selector.prototype.forUpdateOf = function(expression) {
   1.125 +  this._writeLock.push(expression);
   1.126 +  return this;
   1.127 +};
   1.128 +Selector.prototype.resetFields = function() {
   1.129 +  this._fields = [];
   1.130 +  return this;
   1.131 +};
   1.132 +Selector.prototype.addField = function(expression, alias, options) {
   1.133 +  var self = this;
   1.134 +  var processOption = function(option) {
   1.135 +    if (option == "distinct") {
   1.136 +      if (alias == null) self.addDistinctOn(expression);
   1.137 +      else self.addDistinctOn(['"$"', alias]);
   1.138 +    } else if (option == "grouped") {
   1.139 +      if (alias == null) self.addGroupBy(expression);
   1.140 +      else self.addGroupBy(['"$"', alias]);
   1.141 +    } else {
   1.142 +      throw "Unexpected option passed to addField(...).";
   1.143 +    }
   1.144 +  }
   1.145 +  if (alias == null) this._fields.push(expression);
   1.146 +  else this._fields.push(['$ AS "$"', expression, alias]);
   1.147 +  if (options != null) {
   1.148 +    if (options instanceof Array) options.forEach(processOption);
   1.149 +    else processOption(options);
   1.150 +  }
   1.151 +  return this;
   1.152 +};
   1.153 +Selector.prototype.join = Selector.prototype.addFrom;
   1.154 +Selector.prototype.from = function(expression, alias, condition) {
   1.155 +  if (this._from.length > 0) {
   1.156 +    error();
   1.157 +  }
   1.158 +  return this.addFrom(expression, alias, condition);
   1.159 +};
   1.160 +Selector.prototype.leftJoin = function(expression, alias, condition) {
   1.161 +  var first = this._from.length == 0;
   1.162 +  if (!first) this._from.push('LEFT OUTER JOIN');
   1.163 +  if (alias == null) this._from.push(expression);
   1.164 +  else this._from.push(['$ AS "$"', expression, alias]);
   1.165 +  if (condition != null) {
   1.166 +    if (first) {
   1.167 +      this.addWhere(condition);
   1.168 +    } else {
   1.169 +      this._from.push('ON');
   1.170 +      this._from.push(condition);
   1.171 +    }
   1.172 +  }
   1.173 +};
   1.174 +Selector.prototype.union = function(expression) {
   1.175 +  this.addCombine(['UNION $', expression]);
   1.176 +  return this;
   1.177 +};
   1.178 +Selector.prototype.unionAll = function(expression) {
   1.179 +  this.addCombine(['UNION ALL $', expression]);
   1.180 +  return this;
   1.181 +};
   1.182 +Selector.prototype.intersect = function(expression) {
   1.183 +  this.addCombine(['INTERSECT $', expression]);
   1.184 +  return this;
   1.185 +};
   1.186 +Selector.prototype.intersectAll = function(expression) {
   1.187 +  this.addCombine(['INTERSECT ALL $', expression]);
   1.188 +  return this;
   1.189 +};
   1.190 +Selector.prototype.except = function(expression) {
   1.191 +  this.addCombine(['EXCEPT $', expression]);
   1.192 +  return this;
   1.193 +};
   1.194 +Selector.prototype.exceptAll = function(expression) {
   1.195 +  this.addCombine(['EXCEPT ALL $', expression]);
   1.196 +  return this;
   1.197 +};
   1.198 +Selector.prototype.toStructure = function() {
   1.199 +  var parts = [];
   1.200 +  parts.push('SELECT');
   1.201 +  if (this._distinct) parts.push('DISTINCT');
   1.202 +  else if (this._distinctOn.length > 0)
   1.203 +    parts.push(['DISTINCT ON ($$)', ', ', this._distinctOn]);
   1.204 +  parts.push(["$$", ", ", this._fields]);
   1.205 +  if (this._from.length > 0)    parts.push(['FROM $$',     ' ',     this._from]);
   1.206 +  if (this._where.length > 0)   parts.push(['WHERE $$',    ' AND ', this._where]);
   1.207 +  if (this._groupBy.length > 0) parts.push(['GROUP BY $$', ', ',    this._groupBy]);
   1.208 +  if (this._having.length > 0)  parts.push(['HAVING $$',   ' AND ', this._having]);
   1.209 +  this._combine.forEach(function(entry) { parts.push(entry); });
   1.210 +  if (this._orderBy.length > 0) parts.push(['ORDER BY $$', ', ',    this._orderBy]);
   1.211 +  if (this._limit != null)      parts.push(['LIMIT ?',  this._limit]);
   1.212 +  if (this._offset != null)     parts.push(['OFFSET ?', this._offset]);
   1.213 +  if (this._writeLockAll) parts.push('FOR UPDATE');
   1.214 +  else {
   1.215 +    if (this._readLockAll) parts.push('FOR SHARE');
   1.216 +    else if (this._readLock.length > 0)
   1.217 +      parts.push(['FOR SHARE OF $$', ', ', this._readLock]);
   1.218 +    if (this._writeLock.length > 0)
   1.219 +      parts.push(['FOR UPDATE OF $$', ', ', this._writeLock]);
   1.220 +  }
   1.221 +  return ["$$", " ", parts];
   1.222 +};
   1.223 +
   1.224 +function SQLInsert(table) {
   1.225 +  this._with = [];
   1.226 +  if (table == null) this._table = null;
   1.227 +  else this._table = table;
   1.228 +  this._columns = [];
   1.229 +  this._values = [];
   1.230 +  this._query = null;
   1.231 +  this._returning = [];
   1.232 +}
   1.233 +SQLInsert.prototype = new SQLQuery();
   1.234 +SQLInsert.prototype.addWith = Selector.prototype.addWith;
   1.235 +SQLInsert.prototype.table = function(expression) {
   1.236 +  this._table = expression;
   1.237 +  return this;
   1.238 +};
   1.239 +SQLInsert.prototype.addValues = function(mapping) {
   1.240 +  if (this._query != null) throw "Cannot combine query with values.";
   1.241 +  for (key in mapping) {
   1.242 +    this._columns.push(key);
   1.243 +    this._values.push(['?', mapping[key]]);
   1.244 +  }
   1.245 +};
   1.246 +SQLInsert.prototype.addValueExpressions = function(mapping) {
   1.247 +  if (this._query != null) throw "Cannot combine query with values.";
   1.248 +  for (key in mapping) {
   1.249 +    this._columns.push(key);
   1.250 +    this._values.push(mapping[key]);
   1.251 +  }
   1.252 +};
   1.253 +SQLInsert.prototype.query = function(columns, expression) {
   1.254 +  if (this._values.length > 0) throw "Cannot combine query with values.";
   1.255 +  this._columns = columns;
   1.256 +  this._select = expression;
   1.257 +};
   1.258 +SQLInsert.prototype.addReturning = function(expression, alias) {
   1.259 +  if (alias == null) this._returning.push(expression);
   1.260 +  else this._returning.push(['$ AS "$"', expression, alias]);
   1.261 +};
   1.262 +SQLInsert.prototype.toStructure = function() {
   1.263 +  var parts = [];
   1.264 +  parts.push('INSERT INTO');
   1.265 +  if (this._table == null) throw "Missing table for INSERT.";
   1.266 +  parts.push(this._table);
   1.267 +  if (this._columns.length > 0) parts.push(['($$)', ', ', this._columns]);
   1.268 +  if (this._values.length > 0) parts.push(['VALUES ($$)', ', ', this._values]);
   1.269 +  else if (this._query == null) parts.push('DEFAULT VALUES');
   1.270 +  else parts.push(this._query);
   1.271 +  if (this._returning.length > 0)
   1.272 +    parts.push(['RETURNING $$', ', ', this._returning]);
   1.273 +  return ["$$", " ", parts];
   1.274 +};
   1.275 +
   1.276 +function SQLUpdate(table) {
   1.277 +  this._with = [];
   1.278 +  if (table == null) this._table = null;
   1.279 +  else this._table = table;
   1.280 +  this._columns = [];
   1.281 +  this._values = [];
   1.282 +  this._query = null;
   1.283 +  this._from = [];
   1.284 +  this._where = [];
   1.285 +  this._returning = [];
   1.286 +}
   1.287 +SQLUpdate.prototype = new SQLQuery();
   1.288 +SQLUpdate.prototype.addWith = Selector.prototype.addWith;
   1.289 +SQLUpdate.prototype.table = function(expression, alias) {
   1.290 +  if (alias == null) this._table = expression;
   1.291 +  else this._table = ['$ AS "$"', expression, alias];
   1.292 +  return this;
   1.293 +}
   1.294 +SQLUpdate.prototype.addValues = SQLInsert.prototype.addValues;
   1.295 +SQLUpdate.prototype.addValueExpressions = SQLInsert.prototype.addValueExpressions;
   1.296 +SQLUpdate.prototype.query = SQLInsert.prototype.query;
   1.297 +SQLUpdate.prototype.addFrom = Selector.prototype.addFrom;
   1.298 +SQLUpdate.prototype.join = Selector.prototype.addFrom;
   1.299 +SQLUpdate.prototype.leftJoin = function(expression, alias, condition) {
   1.300 +  if (this._from.length == 0)
   1.301 +    throw "First join for UPDATE or DELETE must not be a left join.";
   1.302 +  this._from.push('LEFT OUTER JOIN');
   1.303 +  if (alias == null) this._from.push(expression);
   1.304 +  else this._from.push(['$ AS "$"', expression, alias]);
   1.305 +  if (condition != null) {
   1.306 +    this._from.push('ON');
   1.307 +    this._from.push(condition);
   1.308 +  }
   1.309 +};
   1.310 +SQLUpdate.prototype.addWhere = Selector.prototype.addWhere;
   1.311 +SQLUpdate.prototype.addReturning = SQLInsert.prototype.addReturning;
   1.312 +SQLUpdate.prototype.toStructure = function() {
   1.313 +  var parts = [];
   1.314 +  parts.push('UPDATE');
   1.315 +  if (this._table == null) throw "Missing table for UPDATE.";
   1.316 +  parts.push(this._table);
   1.317 +  parts.push('SET');
   1.318 +  if (this._columns.length == 0) throw "Missing columns for UPDATE.";
   1.319 +  if (this._query == null) {
   1.320 +    for (var i=0; i<this._columns.length; i++) {
   1.321 +      parts.push(
   1.322 +        [ (i==this._columns.length-1) ? '$ = $' : '$ = $,',
   1.323 +          this._columns[i],
   1.324 +          this._values[i]]
   1.325 +      );
   1.326 +    }
   1.327 +  } else {
   1.328 +    parts.push(['($$) = ($$)', ', ', this._columns, ', ', this._values]);
   1.329 +  }
   1.330 +  if (this._from.length > 0) parts.push(['FROM $$', ' ', this._from]);
   1.331 +  if (this._where.length > 0) parts.push(['WHERE $$', ' AND ', this._where]);
   1.332 +  if (this._returning.length > 0)
   1.333 +    parts.push(['RETURNING $$', ', ', this._returning]);
   1.334 +  return ["$$", " ", parts];
   1.335 +};
   1.336 +
   1.337 +function SQLDelete(table) {
   1.338 +  this._with = [];
   1.339 +  if (table == null) this._table = null;
   1.340 +  else this._table = table;
   1.341 +  this._from = [];  // USING clause
   1.342 +  this._where = [];
   1.343 +  this._returning = [];
   1.344 +}
   1.345 +SQLDelete.prototype = new SQLQuery();
   1.346 +SQLDelete.prototype.addWith = Selector.prototype.addWith;
   1.347 +SQLDelete.prototype.table = SQLUpdate.prototype.table;
   1.348 +SQLDelete.prototype.addValues = SQLInsert.prototype.addValues;
   1.349 +SQLDelete.prototype.addValueExpressions = SQLInsert.prototype.addValueExpressions;
   1.350 +SQLDelete.prototype.addFrom = Selector.prototype.addFrom;
   1.351 +SQLDelete.prototype.addUsing = Selector.prototype.addFrom;
   1.352 +SQLDelete.prototype.join = Selector.prototype.addFrom;
   1.353 +SQLDelete.prototype.leftJoin = SQLUpdate.prototype.leftJoin;
   1.354 +SQLDelete.prototype.addWhere = Selector.prototype.addWhere;
   1.355 +SQLDelete.prototype.addReturning = SQLInsert.prototype.addReturning;
   1.356 +SQLDelete.prototype.toStructure = function() {
   1.357 +  var parts = [];
   1.358 +  parts.push('DELETE FROM');
   1.359 +  if (this._table == null) throw "Missing table for DELETE.";
   1.360 +  parts.push(this._table);
   1.361 +  if (this._from.length > 0) parts.push(['USING $$', ' ', this._from]);
   1.362 +  if (this._where.length > 0) parts.push(['WHERE $$', ' AND ', this._where]);
   1.363 +  if (this._returning.length > 0)
   1.364 +    parts.push(['RETURNING $$', ', ', this._returning]);
   1.365 +  return ["$$", " ", parts];
   1.366 +};
   1.367 +
   1.368 +function Upserter(table, keys) {
   1.369 +  if (table == null) this._table = null;
   1.370 +  else this._table = table;
   1.371 +  this._columns = [];
   1.372 +  this._values = [];
   1.373 +  this._keys = [];
   1.374 +  if (keys) this.addKeys(keys);
   1.375 +}
   1.376 +
   1.377 +Upserter.prototype.addValues = SQLInsert.prototype.addValues;
   1.378 +Upserter.prototype.addValueExpressions = SQLInsert.prototype.addValueExpressions;
   1.379 +Upserter.prototype.addKeys = function(keys) {
   1.380 +  var self = this;
   1.381 +  keys.forEach(function(key) { self._keys.push(key); });
   1.382 +};
   1.383 +Upserter.prototype.applyWhere = function(sqlQuery) {
   1.384 +  for (var i=0; i<this._columns.length; i++) {
   1.385 +    var column = this._columns[i];
   1.386 +    var value = this._values[i];
   1.387 +    for (var j=0; j<this._keys.length; j++) if (this._keys[j] == column) break;
   1.388 +    if (j<this._keys.length) sqlQuery.addWhere(['$ = $', column, value]);
   1.389 +  }
   1.390 +}
   1.391 +Upserter.prototype.getSelector = function() {
   1.392 +  var selector = new Selector(this._table).addField('NULL');
   1.393 +  this.applyWhere(selector);
   1.394 +  return selector;
   1.395 +}
   1.396 +Upserter.prototype.getSQLInsert = function() {
   1.397 +  var sqlInsert = new SQLInsert(this._table);
   1.398 +  sqlInsert._columns = this._columns;
   1.399 +  sqlInsert._values = this._values;
   1.400 +  return sqlInsert;
   1.401 +}
   1.402 +Upserter.prototype.getSQLUpdate = function() {
   1.403 +  var sqlUpdate = new SQLUpdate(this._table);
   1.404 +  for (var i =0; i<this._columns.length; i++) {
   1.405 +    var column = this._columns[i];
   1.406 +    var value = this._values[i];
   1.407 +    for (var j=0; j<this._keys.length; j++) if (this._keys[j] == column) break;
   1.408 +    if (j==this._keys.length) {
   1.409 +      sqlUpdate._columns.push(column);
   1.410 +      sqlUpdate._values.push(value);
   1.411 +    }
   1.412 +  }
   1.413 +  if (sqlUpdate._columns.length == 0) return null;
   1.414 +  this.applyWhere(sqlUpdate);
   1.415 +  return sqlUpdate;
   1.416 +}
   1.417 +
   1.418 +
   1.419 +exports.SQLQuery = SQLQuery;
   1.420 +exports.Selector = Selector;
   1.421 +exports.Upserter = Upserter;
   1.422 +exports.SQLInsert = SQLInsert;
   1.423 +exports.SQLUpdate = SQLUpdate;
   1.424 +exports.SQLDelete = SQLDelete;
   1.425 +