lfapi
view lib/selector.js @ 0:ce6f95d23e1c
Initial checkin
| author | bsw |
|---|---|
| date | Sat Sep 10 23:31:20 2011 +0200 (2011-09-10) |
| parents | |
| children |
line source
1 var stringthesizer = require('./stringthesizer.js');
3 var quoteLiteral = function(str) {
4 if (str.search(/^"[^"]*"/) >= 0) return str;
5 else return str.replace(/"/g, '""').replace(/[^\.]+/g, '"$&"');
6 };
8 function SQLQuery() {
9 }
10 SQLQuery.prototype.assemble = function(nextPlaceholder) {
11 return stringthesizer.stringthesizer(
12 {
13 nextPlaceholder: nextPlaceholder,
14 valueSeparator: ",",
15 coerce: function(value) {
16 if (value instanceof Selector) return value.toStructure();
17 return value;
18 }
19 },
20 this.toStructure()
21 );
22 }
24 function Selector(from) {
25 this._with = [];
26 this._fields = [];
27 this._distinct = false;
28 this._distinctOn = [];
29 this._from = [];
30 this._where = [];
31 this._groupBy = [];
32 this._having = [];
33 this._combine = [];
34 this._orderBy = [];
35 this._limit = null;
36 this._offset = null;
37 this._readLock = [];
38 this._readLockAll = false;
39 this._writeLock = [];
40 this._writeLockAll = false;
41 if (from != null) this.from(from);
42 }
43 Selector.prototype = new SQLQuery();
44 Selector.prototype.addWith = function(expression, selector) {
45 this._with.push(['$ AS ($)', expression, selector]);
46 return this;
47 };
48 Selector.prototype.addDistinctOn = function(expression) {
49 if (this._distinct) throw "Cannot combine DISTINCT with DISTINCT ON.";
50 this._distinctOn.push(expression);
51 return this;
52 };
53 Selector.prototype.setDistinct = function() {
54 if (this._distinctOn.length > 0) throw "Cannot combine DISTINCT with DISTINCT ON.";
55 this._distinct = true;
56 return this;
57 };
58 Selector.prototype.addFrom = function(expression, alias, condition) {
59 var first = this._from.length == 0;
60 if (!first) {
61 if (condition == null) this._from.push('CROSS JOIN')
62 else this._from.push('INNER JOIN')
63 }
64 if (expression instanceof Selector) {
65 if (alias == null) this._from.push(['($) AS "subquery"', expression]);
66 else this._from.push(['($) AS "$"', expression, alias]);
67 } else {
68 if (alias == null) this._from.push(expression);
69 else this._from.push(['$ AS "$"', expression, alias]);
70 }
71 if (condition != null) {
72 if (first) {
73 this.addWhere(condition);
74 } else {
75 this._from.push('ON');
76 this._from.push(condition);
77 }
78 }
79 return this;
80 };
81 Selector.prototype.addWhere = function(expression) {
82 this._where.push(['($)', expression]);
83 return this;
84 };
85 Selector.prototype.addGroupBy = function(expression) {
86 this._groupBy.push(expression);
87 return this;
88 };
89 Selector.prototype.addHaving = function(expression) {
90 this._having.push(['($)', expression]);
91 return this;
92 };
93 Selector.prototype.addCombine = function(expression) {
94 this._combine.push(expression);
95 return this;
96 };
97 Selector.prototype.addOrderBy = function(expression) {
98 this._orderBy.push(expression);
99 return this;
100 };
101 Selector.prototype.limit = function(count) {
102 this._limit = count;
103 return this;
104 };
105 Selector.prototype.offset = function(count) {
106 this._offset = count;
107 return this;
108 };
109 Selector.prototype.forShare = function() {
110 this._readLockAll = true;
111 return this;
112 };
113 Selector.prototype.forShareOf = function(expression) {
114 this._readLock.push(expression);
115 return this;
116 };
117 Selector.prototype.forUpdate = function() {
118 this._writeLockAll = true;
119 return this;
120 };
121 Selector.prototype.forUpdateOf = function(expression) {
122 this._writeLock.push(expression);
123 return this;
124 };
125 Selector.prototype.resetFields = function() {
126 this._fields = [];
127 return this;
128 };
129 Selector.prototype.addField = function(expression, alias, options) {
130 var self = this;
131 var processOption = function(option) {
132 if (option == "distinct") {
133 if (alias == null) self.addDistinctOn(expression);
134 else self.addDistinctOn(['"$"', alias]);
135 } else if (option == "grouped") {
136 if (alias == null) self.addGroupBy(expression);
137 else self.addGroupBy(['"$"', alias]);
138 } else {
139 throw "Unexpected option passed to addField(...).";
140 }
141 }
142 if (alias == null) this._fields.push(expression);
143 else this._fields.push(['$ AS "$"', expression, alias]);
144 if (options != null) {
145 if (options instanceof Array) options.forEach(processOption);
146 else processOption(options);
147 }
148 return this;
149 };
150 Selector.prototype.join = Selector.prototype.addFrom;
151 Selector.prototype.from = function(expression, alias, condition) {
152 if (this._from.length > 0) {
153 error();
154 }
155 return this.addFrom(expression, alias, condition);
156 };
157 Selector.prototype.leftJoin = function(expression, alias, condition) {
158 var first = this._from.length == 0;
159 if (!first) this._from.push('LEFT OUTER JOIN');
160 if (alias == null) this._from.push(expression);
161 else this._from.push(['$ AS "$"', expression, alias]);
162 if (condition != null) {
163 if (first) {
164 this.addWhere(condition);
165 } else {
166 this._from.push('ON');
167 this._from.push(condition);
168 }
169 }
170 };
171 Selector.prototype.union = function(expression) {
172 this.addCombine(['UNION $', expression]);
173 return this;
174 };
175 Selector.prototype.unionAll = function(expression) {
176 this.addCombine(['UNION ALL $', expression]);
177 return this;
178 };
179 Selector.prototype.intersect = function(expression) {
180 this.addCombine(['INTERSECT $', expression]);
181 return this;
182 };
183 Selector.prototype.intersectAll = function(expression) {
184 this.addCombine(['INTERSECT ALL $', expression]);
185 return this;
186 };
187 Selector.prototype.except = function(expression) {
188 this.addCombine(['EXCEPT $', expression]);
189 return this;
190 };
191 Selector.prototype.exceptAll = function(expression) {
192 this.addCombine(['EXCEPT ALL $', expression]);
193 return this;
194 };
195 Selector.prototype.toStructure = function() {
196 var parts = [];
197 parts.push('SELECT');
198 if (this._distinct) parts.push('DISTINCT');
199 else if (this._distinctOn.length > 0)
200 parts.push(['DISTINCT ON ($$)', ', ', this._distinctOn]);
201 parts.push(["$$", ", ", this._fields]);
202 if (this._from.length > 0) parts.push(['FROM $$', ' ', this._from]);
203 if (this._where.length > 0) parts.push(['WHERE $$', ' AND ', this._where]);
204 if (this._groupBy.length > 0) parts.push(['GROUP BY $$', ', ', this._groupBy]);
205 if (this._having.length > 0) parts.push(['HAVING $$', ' AND ', this._having]);
206 this._combine.forEach(function(entry) { parts.push(entry); });
207 if (this._orderBy.length > 0) parts.push(['ORDER BY $$', ', ', this._orderBy]);
208 if (this._limit != null) parts.push(['LIMIT ?', this._limit]);
209 if (this._offset != null) parts.push(['OFFSET ?', this._offset]);
210 if (this._writeLockAll) parts.push('FOR UPDATE');
211 else {
212 if (this._readLockAll) parts.push('FOR SHARE');
213 else if (this._readLock.length > 0)
214 parts.push(['FOR SHARE OF $$', ', ', this._readLock]);
215 if (this._writeLock.length > 0)
216 parts.push(['FOR UPDATE OF $$', ', ', this._writeLock]);
217 }
218 return ["$$", " ", parts];
219 };
221 function SQLInsert(table) {
222 this._with = [];
223 if (table == null) this._table = null;
224 else this._table = table;
225 this._columns = [];
226 this._values = [];
227 this._query = null;
228 this._returning = [];
229 }
230 SQLInsert.prototype = new SQLQuery();
231 SQLInsert.prototype.addWith = Selector.prototype.addWith;
232 SQLInsert.prototype.table = function(expression) {
233 this._table = expression;
234 return this;
235 };
236 SQLInsert.prototype.addValues = function(mapping) {
237 if (this._query != null) throw "Cannot combine query with values.";
238 for (key in mapping) {
239 this._columns.push(key);
240 this._values.push(['?', mapping[key]]);
241 }
242 };
243 SQLInsert.prototype.addValueExpressions = function(mapping) {
244 if (this._query != null) throw "Cannot combine query with values.";
245 for (key in mapping) {
246 this._columns.push(key);
247 this._values.push(mapping[key]);
248 }
249 };
250 SQLInsert.prototype.query = function(columns, expression) {
251 if (this._values.length > 0) throw "Cannot combine query with values.";
252 this._columns = columns;
253 this._select = expression;
254 };
255 SQLInsert.prototype.addReturning = function(expression, alias) {
256 if (alias == null) this._returning.push(expression);
257 else this._returning.push(['$ AS "$"', expression, alias]);
258 };
259 SQLInsert.prototype.toStructure = function() {
260 var parts = [];
261 parts.push('INSERT INTO');
262 if (this._table == null) throw "Missing table for INSERT.";
263 parts.push(this._table);
264 if (this._columns.length > 0) parts.push(['($$)', ', ', this._columns]);
265 if (this._values.length > 0) parts.push(['VALUES ($$)', ', ', this._values]);
266 else if (this._query == null) parts.push('DEFAULT VALUES');
267 else parts.push(this._query);
268 if (this._returning.length > 0)
269 parts.push(['RETURNING $$', ', ', this._returning]);
270 return ["$$", " ", parts];
271 };
273 function SQLUpdate(table) {
274 this._with = [];
275 if (table == null) this._table = null;
276 else this._table = table;
277 this._columns = [];
278 this._values = [];
279 this._query = null;
280 this._from = [];
281 this._where = [];
282 this._returning = [];
283 }
284 SQLUpdate.prototype = new SQLQuery();
285 SQLUpdate.prototype.addWith = Selector.prototype.addWith;
286 SQLUpdate.prototype.table = function(expression, alias) {
287 if (alias == null) this._table = expression;
288 else this._table = ['$ AS "$"', expression, alias];
289 return this;
290 }
291 SQLUpdate.prototype.addValues = SQLInsert.prototype.addValues;
292 SQLUpdate.prototype.addValueExpressions = SQLInsert.prototype.addValueExpressions;
293 SQLUpdate.prototype.query = SQLInsert.prototype.query;
294 SQLUpdate.prototype.addFrom = Selector.prototype.addFrom;
295 SQLUpdate.prototype.join = Selector.prototype.addFrom;
296 SQLUpdate.prototype.leftJoin = function(expression, alias, condition) {
297 if (this._from.length == 0)
298 throw "First join for UPDATE or DELETE must not be a left join.";
299 this._from.push('LEFT OUTER JOIN');
300 if (alias == null) this._from.push(expression);
301 else this._from.push(['$ AS "$"', expression, alias]);
302 if (condition != null) {
303 this._from.push('ON');
304 this._from.push(condition);
305 }
306 };
307 SQLUpdate.prototype.addWhere = Selector.prototype.addWhere;
308 SQLUpdate.prototype.addReturning = SQLInsert.prototype.addReturning;
309 SQLUpdate.prototype.toStructure = function() {
310 var parts = [];
311 parts.push('UPDATE');
312 if (this._table == null) throw "Missing table for UPDATE.";
313 parts.push(this._table);
314 parts.push('SET');
315 if (this._columns.length == 0) throw "Missing columns for UPDATE.";
316 if (this._query == null) {
317 for (var i=0; i<this._columns.length; i++) {
318 parts.push(
319 [ (i==this._columns.length-1) ? '$ = $' : '$ = $,',
320 this._columns[i],
321 this._values[i]]
322 );
323 }
324 } else {
325 parts.push(['($$) = ($$)', ', ', this._columns, ', ', this._values]);
326 }
327 if (this._from.length > 0) parts.push(['FROM $$', ' ', this._from]);
328 if (this._where.length > 0) parts.push(['WHERE $$', ' AND ', this._where]);
329 if (this._returning.length > 0)
330 parts.push(['RETURNING $$', ', ', this._returning]);
331 return ["$$", " ", parts];
332 };
334 function SQLDelete(table) {
335 this._with = [];
336 if (table == null) this._table = null;
337 else this._table = table;
338 this._from = []; // USING clause
339 this._where = [];
340 this._returning = [];
341 }
342 SQLDelete.prototype = new SQLQuery();
343 SQLDelete.prototype.addWith = Selector.prototype.addWith;
344 SQLDelete.prototype.table = SQLUpdate.prototype.table;
345 SQLDelete.prototype.addValues = SQLInsert.prototype.addValues;
346 SQLDelete.prototype.addValueExpressions = SQLInsert.prototype.addValueExpressions;
347 SQLDelete.prototype.addFrom = Selector.prototype.addFrom;
348 SQLDelete.prototype.addUsing = Selector.prototype.addFrom;
349 SQLDelete.prototype.join = Selector.prototype.addFrom;
350 SQLDelete.prototype.leftJoin = SQLUpdate.prototype.leftJoin;
351 SQLDelete.prototype.addWhere = Selector.prototype.addWhere;
352 SQLDelete.prototype.addReturning = SQLInsert.prototype.addReturning;
353 SQLDelete.prototype.toStructure = function() {
354 var parts = [];
355 parts.push('DELETE FROM');
356 if (this._table == null) throw "Missing table for DELETE.";
357 parts.push(this._table);
358 if (this._from.length > 0) parts.push(['USING $$', ' ', this._from]);
359 if (this._where.length > 0) parts.push(['WHERE $$', ' AND ', this._where]);
360 if (this._returning.length > 0)
361 parts.push(['RETURNING $$', ', ', this._returning]);
362 return ["$$", " ", parts];
363 };
365 function Upserter(table, keys) {
366 if (table == null) this._table = null;
367 else this._table = table;
368 this._columns = [];
369 this._values = [];
370 this._keys = [];
371 if (keys) this.addKeys(keys);
372 }
374 Upserter.prototype.addValues = SQLInsert.prototype.addValues;
375 Upserter.prototype.addValueExpressions = SQLInsert.prototype.addValueExpressions;
376 Upserter.prototype.addKeys = function(keys) {
377 var self = this;
378 keys.forEach(function(key) { self._keys.push(key); });
379 };
380 Upserter.prototype.applyWhere = function(sqlQuery) {
381 for (var i=0; i<this._columns.length; i++) {
382 var column = this._columns[i];
383 var value = this._values[i];
384 for (var j=0; j<this._keys.length; j++) if (this._keys[j] == column) break;
385 if (j<this._keys.length) sqlQuery.addWhere(['$ = $', column, value]);
386 }
387 }
388 Upserter.prototype.getSelector = function() {
389 var selector = new Selector(this._table).addField('NULL');
390 this.applyWhere(selector);
391 return selector;
392 }
393 Upserter.prototype.getSQLInsert = function() {
394 var sqlInsert = new SQLInsert(this._table);
395 sqlInsert._columns = this._columns;
396 sqlInsert._values = this._values;
397 return sqlInsert;
398 }
399 Upserter.prototype.getSQLUpdate = function() {
400 var sqlUpdate = new SQLUpdate(this._table);
401 for (var i =0; i<this._columns.length; i++) {
402 var column = this._columns[i];
403 var value = this._values[i];
404 for (var j=0; j<this._keys.length; j++) if (this._keys[j] == column) break;
405 if (j==this._keys.length) {
406 sqlUpdate._columns.push(column);
407 sqlUpdate._values.push(value);
408 }
409 }
410 if (sqlUpdate._columns.length == 0) return null;
411 this.applyWhere(sqlUpdate);
412 return sqlUpdate;
413 }
416 exports.SQLQuery = SQLQuery;
417 exports.Selector = Selector;
418 exports.Upserter = Upserter;
419 exports.SQLInsert = SQLInsert;
420 exports.SQLUpdate = SQLUpdate;
421 exports.SQLDelete = SQLDelete;