bsw@0: var stringthesizer = require('./stringthesizer.js');
bsw@0: 
bsw@0: var quoteLiteral = function(str) {
bsw@0:   if (str.search(/^"[^"]*"/) >= 0) return str;
bsw@0:   else return str.replace(/"/g, '""').replace(/[^\.]+/g, '"$&"');
bsw@0: };
bsw@0: 
bsw@0: function SQLQuery() {
bsw@0: }
bsw@0: SQLQuery.prototype.assemble = function(nextPlaceholder) {
bsw@0:   return stringthesizer.stringthesizer(
bsw@0:     {
bsw@0:       nextPlaceholder: nextPlaceholder,
bsw@0:       valueSeparator: ",",
bsw@0:       coerce: function(value) {
bsw@0:         if (value instanceof Selector) return value.toStructure();
bsw@0:         return value;
bsw@0:       }
bsw@0:     },
bsw@0:     this.toStructure()
bsw@0:   );
bsw@0: }
bsw@0: 
bsw@0: function Selector(from) {
bsw@0:   this._with = [];
bsw@0:   this._fields = [];
bsw@0:   this._distinct = false;
bsw@0:   this._distinctOn = [];
bsw@0:   this._from = [];
bsw@0:   this._where = [];
bsw@0:   this._groupBy = [];
bsw@0:   this._having = [];
bsw@0:   this._combine = [];
bsw@0:   this._orderBy = [];
bsw@0:   this._limit = null;
bsw@0:   this._offset = null;
bsw@0:   this._readLock = [];
bsw@0:   this._readLockAll = false;
bsw@0:   this._writeLock = [];
bsw@0:   this._writeLockAll = false;
bsw@0:   if (from != null) this.from(from);
bsw@0: }
bsw@0: Selector.prototype = new SQLQuery();
bsw@0: Selector.prototype.addWith = function(expression, selector) {
bsw@0:   this._with.push(['$ AS ($)', expression, selector]);
bsw@0:   return this;
bsw@0: };
bsw@0: Selector.prototype.addDistinctOn = function(expression) {
bsw@0:   if (this._distinct) throw "Cannot combine DISTINCT with DISTINCT ON.";
bsw@0:   this._distinctOn.push(expression);
bsw@0:   return this;
bsw@0: };
bsw@0: Selector.prototype.setDistinct = function() {
bsw@0:   if (this._distinctOn.length > 0) throw "Cannot combine DISTINCT with DISTINCT ON.";
bsw@0:   this._distinct = true;
bsw@0:   return this;
bsw@0: };
bsw@0: Selector.prototype.addFrom = function(expression, alias, condition) {
bsw@0:   var first = this._from.length == 0;
bsw@0:   if (!first) {
bsw@0:     if (condition == null) this._from.push('CROSS JOIN')
bsw@0:     else this._from.push('INNER JOIN')
bsw@0:   }
bsw@0:   if (expression instanceof Selector) {
bsw@0:     if (alias == null) this._from.push(['($) AS "subquery"', expression]);
bsw@0:     else this._from.push(['($) AS "$"', expression, alias]);
bsw@0:   } else {
bsw@0:     if (alias == null) this._from.push(expression);
bsw@0:     else this._from.push(['$ AS "$"', expression, alias]);
bsw@0:   }
bsw@0:   if (condition != null) {
bsw@0:     if (first) {
bsw@0:       this.addWhere(condition);
bsw@0:     } else {
bsw@0:       this._from.push('ON');
bsw@0:       this._from.push(condition);
bsw@0:     }
bsw@0:   }
bsw@0:   return this;
bsw@0: };
bsw@0: Selector.prototype.addWhere = function(expression) {
bsw@0:   this._where.push(['($)', expression]);
bsw@0:   return this;
bsw@0: };
bsw@0: Selector.prototype.addGroupBy = function(expression) {
bsw@0:   this._groupBy.push(expression);
bsw@0:   return this;
bsw@0: };
bsw@0: Selector.prototype.addHaving = function(expression) {
bsw@0:   this._having.push(['($)', expression]);
bsw@0:   return this;
bsw@0: };
bsw@0: Selector.prototype.addCombine = function(expression) {
bsw@0:   this._combine.push(expression);
bsw@0:   return this;
bsw@0: };
bsw@0: Selector.prototype.addOrderBy = function(expression) {
bsw@0:   this._orderBy.push(expression);
bsw@0:   return this;
bsw@0: };
bsw@0: Selector.prototype.limit = function(count) {
bsw@0:   this._limit = count;
bsw@0:   return this;
bsw@0: };
bsw@0: Selector.prototype.offset = function(count) {
bsw@0:   this._offset = count;
bsw@0:   return this;
bsw@0: };
bsw@0: Selector.prototype.forShare = function() {
bsw@0:   this._readLockAll = true;
bsw@0:   return this;
bsw@0: };
bsw@0: Selector.prototype.forShareOf = function(expression) {
bsw@0:   this._readLock.push(expression);
bsw@0:   return this;
bsw@0: };
bsw@0: Selector.prototype.forUpdate = function() {
bsw@0:   this._writeLockAll = true;
bsw@0:   return this;
bsw@0: };
bsw@0: Selector.prototype.forUpdateOf = function(expression) {
bsw@0:   this._writeLock.push(expression);
bsw@0:   return this;
bsw@0: };
bsw@0: Selector.prototype.resetFields = function() {
bsw@0:   this._fields = [];
bsw@0:   return this;
bsw@0: };
bsw@0: Selector.prototype.addField = function(expression, alias, options) {
bsw@0:   var self = this;
bsw@0:   var processOption = function(option) {
bsw@0:     if (option == "distinct") {
bsw@0:       if (alias == null) self.addDistinctOn(expression);
bsw@0:       else self.addDistinctOn(['"$"', alias]);
bsw@0:     } else if (option == "grouped") {
bsw@0:       if (alias == null) self.addGroupBy(expression);
bsw@0:       else self.addGroupBy(['"$"', alias]);
bsw@0:     } else {
bsw@0:       throw "Unexpected option passed to addField(...).";
bsw@0:     }
bsw@0:   }
bsw@0:   if (alias == null) this._fields.push(expression);
bsw@0:   else this._fields.push(['$ AS "$"', expression, alias]);
bsw@0:   if (options != null) {
bsw@0:     if (options instanceof Array) options.forEach(processOption);
bsw@0:     else processOption(options);
bsw@0:   }
bsw@0:   return this;
bsw@0: };
bsw@0: Selector.prototype.join = Selector.prototype.addFrom;
bsw@0: Selector.prototype.from = function(expression, alias, condition) {
bsw@0:   if (this._from.length > 0) {
bsw@0:     error();
bsw@0:   }
bsw@0:   return this.addFrom(expression, alias, condition);
bsw@0: };
bsw@0: Selector.prototype.leftJoin = function(expression, alias, condition) {
bsw@0:   var first = this._from.length == 0;
bsw@0:   if (!first) this._from.push('LEFT OUTER JOIN');
bsw@0:   if (alias == null) this._from.push(expression);
bsw@0:   else this._from.push(['$ AS "$"', expression, alias]);
bsw@0:   if (condition != null) {
bsw@0:     if (first) {
bsw@0:       this.addWhere(condition);
bsw@0:     } else {
bsw@0:       this._from.push('ON');
bsw@0:       this._from.push(condition);
bsw@0:     }
bsw@0:   }
bsw@0: };
bsw@0: Selector.prototype.union = function(expression) {
bsw@0:   this.addCombine(['UNION $', expression]);
bsw@0:   return this;
bsw@0: };
bsw@0: Selector.prototype.unionAll = function(expression) {
bsw@0:   this.addCombine(['UNION ALL $', expression]);
bsw@0:   return this;
bsw@0: };
bsw@0: Selector.prototype.intersect = function(expression) {
bsw@0:   this.addCombine(['INTERSECT $', expression]);
bsw@0:   return this;
bsw@0: };
bsw@0: Selector.prototype.intersectAll = function(expression) {
bsw@0:   this.addCombine(['INTERSECT ALL $', expression]);
bsw@0:   return this;
bsw@0: };
bsw@0: Selector.prototype.except = function(expression) {
bsw@0:   this.addCombine(['EXCEPT $', expression]);
bsw@0:   return this;
bsw@0: };
bsw@0: Selector.prototype.exceptAll = function(expression) {
bsw@0:   this.addCombine(['EXCEPT ALL $', expression]);
bsw@0:   return this;
bsw@0: };
bsw@0: Selector.prototype.toStructure = function() {
bsw@0:   var parts = [];
bsw@0:   parts.push('SELECT');
bsw@0:   if (this._distinct) parts.push('DISTINCT');
bsw@0:   else if (this._distinctOn.length > 0)
bsw@0:     parts.push(['DISTINCT ON ($$)', ', ', this._distinctOn]);
bsw@0:   parts.push(["$$", ", ", this._fields]);
bsw@0:   if (this._from.length > 0)    parts.push(['FROM $$',     ' ',     this._from]);
bsw@0:   if (this._where.length > 0)   parts.push(['WHERE $$',    ' AND ', this._where]);
bsw@0:   if (this._groupBy.length > 0) parts.push(['GROUP BY $$', ', ',    this._groupBy]);
bsw@0:   if (this._having.length > 0)  parts.push(['HAVING $$',   ' AND ', this._having]);
bsw@0:   this._combine.forEach(function(entry) { parts.push(entry); });
bsw@0:   if (this._orderBy.length > 0) parts.push(['ORDER BY $$', ', ',    this._orderBy]);
bsw@0:   if (this._limit != null)      parts.push(['LIMIT ?',  this._limit]);
bsw@0:   if (this._offset != null)     parts.push(['OFFSET ?', this._offset]);
bsw@0:   if (this._writeLockAll) parts.push('FOR UPDATE');
bsw@0:   else {
bsw@0:     if (this._readLockAll) parts.push('FOR SHARE');
bsw@0:     else if (this._readLock.length > 0)
bsw@0:       parts.push(['FOR SHARE OF $$', ', ', this._readLock]);
bsw@0:     if (this._writeLock.length > 0)
bsw@0:       parts.push(['FOR UPDATE OF $$', ', ', this._writeLock]);
bsw@0:   }
bsw@0:   return ["$$", " ", parts];
bsw@0: };
bsw@0: 
bsw@0: function SQLInsert(table) {
bsw@0:   this._with = [];
bsw@0:   if (table == null) this._table = null;
bsw@0:   else this._table = table;
bsw@0:   this._columns = [];
bsw@0:   this._values = [];
bsw@0:   this._query = null;
bsw@0:   this._returning = [];
bsw@0: }
bsw@0: SQLInsert.prototype = new SQLQuery();
bsw@0: SQLInsert.prototype.addWith = Selector.prototype.addWith;
bsw@0: SQLInsert.prototype.table = function(expression) {
bsw@0:   this._table = expression;
bsw@0:   return this;
bsw@0: };
bsw@0: SQLInsert.prototype.addValues = function(mapping) {
bsw@0:   if (this._query != null) throw "Cannot combine query with values.";
bsw@0:   for (key in mapping) {
bsw@0:     this._columns.push(key);
bsw@0:     this._values.push(['?', mapping[key]]);
bsw@0:   }
bsw@0: };
bsw@0: SQLInsert.prototype.addValueExpressions = function(mapping) {
bsw@0:   if (this._query != null) throw "Cannot combine query with values.";
bsw@0:   for (key in mapping) {
bsw@0:     this._columns.push(key);
bsw@0:     this._values.push(mapping[key]);
bsw@0:   }
bsw@0: };
bsw@0: SQLInsert.prototype.query = function(columns, expression) {
bsw@0:   if (this._values.length > 0) throw "Cannot combine query with values.";
bsw@0:   this._columns = columns;
bsw@0:   this._select = expression;
bsw@0: };
bsw@0: SQLInsert.prototype.addReturning = function(expression, alias) {
bsw@0:   if (alias == null) this._returning.push(expression);
bsw@0:   else this._returning.push(['$ AS "$"', expression, alias]);
bsw@0: };
bsw@0: SQLInsert.prototype.toStructure = function() {
bsw@0:   var parts = [];
bsw@0:   parts.push('INSERT INTO');
bsw@0:   if (this._table == null) throw "Missing table for INSERT.";
bsw@0:   parts.push(this._table);
bsw@0:   if (this._columns.length > 0) parts.push(['($$)', ', ', this._columns]);
bsw@0:   if (this._values.length > 0) parts.push(['VALUES ($$)', ', ', this._values]);
bsw@0:   else if (this._query == null) parts.push('DEFAULT VALUES');
bsw@0:   else parts.push(this._query);
bsw@0:   if (this._returning.length > 0)
bsw@0:     parts.push(['RETURNING $$', ', ', this._returning]);
bsw@0:   return ["$$", " ", parts];
bsw@0: };
bsw@0: 
bsw@0: function SQLUpdate(table) {
bsw@0:   this._with = [];
bsw@0:   if (table == null) this._table = null;
bsw@0:   else this._table = table;
bsw@0:   this._columns = [];
bsw@0:   this._values = [];
bsw@0:   this._query = null;
bsw@0:   this._from = [];
bsw@0:   this._where = [];
bsw@0:   this._returning = [];
bsw@0: }
bsw@0: SQLUpdate.prototype = new SQLQuery();
bsw@0: SQLUpdate.prototype.addWith = Selector.prototype.addWith;
bsw@0: SQLUpdate.prototype.table = function(expression, alias) {
bsw@0:   if (alias == null) this._table = expression;
bsw@0:   else this._table = ['$ AS "$"', expression, alias];
bsw@0:   return this;
bsw@0: }
bsw@0: SQLUpdate.prototype.addValues = SQLInsert.prototype.addValues;
bsw@0: SQLUpdate.prototype.addValueExpressions = SQLInsert.prototype.addValueExpressions;
bsw@0: SQLUpdate.prototype.query = SQLInsert.prototype.query;
bsw@0: SQLUpdate.prototype.addFrom = Selector.prototype.addFrom;
bsw@0: SQLUpdate.prototype.join = Selector.prototype.addFrom;
bsw@0: SQLUpdate.prototype.leftJoin = function(expression, alias, condition) {
bsw@0:   if (this._from.length == 0)
bsw@0:     throw "First join for UPDATE or DELETE must not be a left join.";
bsw@0:   this._from.push('LEFT OUTER JOIN');
bsw@0:   if (alias == null) this._from.push(expression);
bsw@0:   else this._from.push(['$ AS "$"', expression, alias]);
bsw@0:   if (condition != null) {
bsw@0:     this._from.push('ON');
bsw@0:     this._from.push(condition);
bsw@0:   }
bsw@0: };
bsw@0: SQLUpdate.prototype.addWhere = Selector.prototype.addWhere;
bsw@0: SQLUpdate.prototype.addReturning = SQLInsert.prototype.addReturning;
bsw@0: SQLUpdate.prototype.toStructure = function() {
bsw@0:   var parts = [];
bsw@0:   parts.push('UPDATE');
bsw@0:   if (this._table == null) throw "Missing table for UPDATE.";
bsw@0:   parts.push(this._table);
bsw@0:   parts.push('SET');
bsw@0:   if (this._columns.length == 0) throw "Missing columns for UPDATE.";
bsw@0:   if (this._query == null) {
bsw@0:     for (var i=0; i<this._columns.length; i++) {
bsw@0:       parts.push(
bsw@0:         [ (i==this._columns.length-1) ? '$ = $' : '$ = $,',
bsw@0:           this._columns[i],
bsw@0:           this._values[i]]
bsw@0:       );
bsw@0:     }
bsw@0:   } else {
bsw@0:     parts.push(['($$) = ($$)', ', ', this._columns, ', ', this._values]);
bsw@0:   }
bsw@0:   if (this._from.length > 0) parts.push(['FROM $$', ' ', this._from]);
bsw@0:   if (this._where.length > 0) parts.push(['WHERE $$', ' AND ', this._where]);
bsw@0:   if (this._returning.length > 0)
bsw@0:     parts.push(['RETURNING $$', ', ', this._returning]);
bsw@0:   return ["$$", " ", parts];
bsw@0: };
bsw@0: 
bsw@0: function SQLDelete(table) {
bsw@0:   this._with = [];
bsw@0:   if (table == null) this._table = null;
bsw@0:   else this._table = table;
bsw@0:   this._from = [];  // USING clause
bsw@0:   this._where = [];
bsw@0:   this._returning = [];
bsw@0: }
bsw@0: SQLDelete.prototype = new SQLQuery();
bsw@0: SQLDelete.prototype.addWith = Selector.prototype.addWith;
bsw@0: SQLDelete.prototype.table = SQLUpdate.prototype.table;
bsw@0: SQLDelete.prototype.addValues = SQLInsert.prototype.addValues;
bsw@0: SQLDelete.prototype.addValueExpressions = SQLInsert.prototype.addValueExpressions;
bsw@0: SQLDelete.prototype.addFrom = Selector.prototype.addFrom;
bsw@0: SQLDelete.prototype.addUsing = Selector.prototype.addFrom;
bsw@0: SQLDelete.prototype.join = Selector.prototype.addFrom;
bsw@0: SQLDelete.prototype.leftJoin = SQLUpdate.prototype.leftJoin;
bsw@0: SQLDelete.prototype.addWhere = Selector.prototype.addWhere;
bsw@0: SQLDelete.prototype.addReturning = SQLInsert.prototype.addReturning;
bsw@0: SQLDelete.prototype.toStructure = function() {
bsw@0:   var parts = [];
bsw@0:   parts.push('DELETE FROM');
bsw@0:   if (this._table == null) throw "Missing table for DELETE.";
bsw@0:   parts.push(this._table);
bsw@0:   if (this._from.length > 0) parts.push(['USING $$', ' ', this._from]);
bsw@0:   if (this._where.length > 0) parts.push(['WHERE $$', ' AND ', this._where]);
bsw@0:   if (this._returning.length > 0)
bsw@0:     parts.push(['RETURNING $$', ', ', this._returning]);
bsw@0:   return ["$$", " ", parts];
bsw@0: };
bsw@0: 
bsw@0: function Upserter(table, keys) {
bsw@0:   if (table == null) this._table = null;
bsw@0:   else this._table = table;
bsw@0:   this._columns = [];
bsw@0:   this._values = [];
bsw@0:   this._keys = [];
bsw@0:   if (keys) this.addKeys(keys);
bsw@0: }
bsw@0: 
bsw@0: Upserter.prototype.addValues = SQLInsert.prototype.addValues;
bsw@0: Upserter.prototype.addValueExpressions = SQLInsert.prototype.addValueExpressions;
bsw@0: Upserter.prototype.addKeys = function(keys) {
bsw@0:   var self = this;
bsw@0:   keys.forEach(function(key) { self._keys.push(key); });
bsw@0: };
bsw@0: Upserter.prototype.applyWhere = function(sqlQuery) {
bsw@0:   for (var i=0; i<this._columns.length; i++) {
bsw@0:     var column = this._columns[i];
bsw@0:     var value = this._values[i];
bsw@0:     for (var j=0; j<this._keys.length; j++) if (this._keys[j] == column) break;
bsw@0:     if (j<this._keys.length) sqlQuery.addWhere(['$ = $', column, value]);
bsw@0:   }
bsw@0: }
bsw@0: Upserter.prototype.getSelector = function() {
bsw@0:   var selector = new Selector(this._table).addField('NULL');
bsw@0:   this.applyWhere(selector);
bsw@0:   return selector;
bsw@0: }
bsw@0: Upserter.prototype.getSQLInsert = function() {
bsw@0:   var sqlInsert = new SQLInsert(this._table);
bsw@0:   sqlInsert._columns = this._columns;
bsw@0:   sqlInsert._values = this._values;
bsw@0:   return sqlInsert;
bsw@0: }
bsw@0: Upserter.prototype.getSQLUpdate = function() {
bsw@0:   var sqlUpdate = new SQLUpdate(this._table);
bsw@0:   for (var i =0; i<this._columns.length; i++) {
bsw@0:     var column = this._columns[i];
bsw@0:     var value = this._values[i];
bsw@0:     for (var j=0; j<this._keys.length; j++) if (this._keys[j] == column) break;
bsw@0:     if (j==this._keys.length) {
bsw@0:       sqlUpdate._columns.push(column);
bsw@0:       sqlUpdate._values.push(value);
bsw@0:     }
bsw@0:   }
bsw@0:   if (sqlUpdate._columns.length == 0) return null;
bsw@0:   this.applyWhere(sqlUpdate);
bsw@0:   return sqlUpdate;
bsw@0: }
bsw@0: 
bsw@0: 
bsw@0: exports.SQLQuery = SQLQuery;
bsw@0: exports.Selector = Selector;
bsw@0: exports.Upserter = Upserter;
bsw@0: exports.SQLInsert = SQLInsert;
bsw@0: exports.SQLUpdate = SQLUpdate;
bsw@0: exports.SQLDelete = SQLDelete;
bsw@0: