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