lfapi

annotate lib/selector.js @ 0:ce6f95d23e1c

find changesets by author, revision, files, or words in the commit message
Initial checkin
author bsw
date Sat Sep 10 23:31:20 2011 +0200 (2011-09-10)
parents
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