lfapi

annotate lib/selector.js @ 34:a5a5de8dbac2

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

Impressum / About Us