Skip to content

Commit 78f3221

Browse files
briancbrianc
committed
allow SELECT/FROM/WHERE in any order
1 parent 980207e commit 78f3221

5 files changed

Lines changed: 84 additions & 25 deletions

File tree

‎lib/dialect/postgres.js‎

Lines changed: 45 additions & 14 deletions
Original file line numberDiff line numberDiff line change
@@ -3,6 +3,7 @@
33
var util = require('util');
44
var assert = require('assert');
55
var From = require(__dirname + '/../node/from');
6+
var Select = require(__dirname + '/../node/select');
67
var Parameter = require(__dirname + '/../node/parameter');
78
var Postgres = function() {
89
this.output = [];
@@ -70,7 +71,6 @@ Postgres.prototype.visitSelect = function(select) {
7071

7172
Postgres.prototype.visitInsert = function(insert) {
7273
var self = this;
73-
this._visitedFrom = true;
7474
//don't use table.column for inserts
7575
this._visitedInsert = true;
7676

@@ -98,7 +98,6 @@ Postgres.prototype.visitInsert = function(insert) {
9898

9999
Postgres.prototype.visitUpdate = function(update) {
100100
//don't auto-generate from clause
101-
this._visitedFrom = true;
102101
var params = [];
103102
/*jshint boss: true */
104103
for(var i = 0, node; node = update.nodes[i]; i++) {
@@ -124,7 +123,6 @@ Postgres.prototype.visitDelete = function() {
124123
Postgres.prototype.visitCreate = function(create) {
125124
this._visitingCreate = true;
126125
//don't auto-generate from clause
127-
this._visitedFrom = true;
128126
var table = this._queryNode.table;
129127
var col_nodes = table.columns.map(function(col) { return col.toNode(); });
130128

@@ -138,7 +136,6 @@ Postgres.prototype.visitCreate = function(create) {
138136

139137
Postgres.prototype.visitDrop = function(drop) {
140138
//don't auto-generate from clause
141-
this._visitedFrom = true;
142139
var result = ['DROP TABLE'];
143140
result = result.concat(drop.nodes.map(this.visit.bind(this)));
144141
result.push(this.visit(this._queryNode.table.toNode()));
@@ -148,7 +145,6 @@ Postgres.prototype.visitDrop = function(drop) {
148145
Postgres.prototype.visitAlter = function(alter) {
149146
this._visitingAlter = true;
150147
//don't auto-generate from clause
151-
this._visitedFrom = true;
152148
var table = this._queryNode.table;
153149
var col_nodes = table.columns.map(function(col) { return col.toNode(); });
154150
var result = [
@@ -161,7 +157,6 @@ Postgres.prototype.visitAlter = function(alter) {
161157
};
162158

163159
Postgres.prototype.visitFrom = function(from) {
164-
this._visitedFrom = true;
165160
var result = [];
166161
result.push('FROM');
167162
for(var i = 0; i < from.nodes.length; i++) {
@@ -214,17 +209,53 @@ Postgres.prototype.visitUnary = function(unary) {
214209

215210
Postgres.prototype.visitQuery = function(queryNode) {
216211
this._queryNode = queryNode;
217-
for(var i = 0; i < queryNode.nodes.length; i ++) {
218-
var res = this.visit(queryNode.nodes[i]);
212+
//need to sort the top level query nodes on visitation priority
213+
//so select/insert/update/delete comes before from comes before where
214+
var sortedNodes = [];
215+
var missingFrom = true;
216+
var actions = [];
217+
var targets = [];
218+
var filters = [];
219+
for(var i = 0; i < queryNode.nodes.length; i++) {
220+
var node = queryNode.nodes[i];
221+
switch(node.type) {
222+
case "SELECT":
223+
case "DELETE":
224+
actions.push(node);
225+
break;
226+
case "INSERT":
227+
case "UPDATE":
228+
case "CREATE":
229+
case "DROP":
230+
case "ALTER":
231+
actions.push(node);
232+
missingFrom = false;
233+
break;
234+
case "FROM":
235+
missingFrom = false;
236+
targets.push(node);
237+
break;
238+
default:
239+
filters.push(node);
240+
break;
241+
}
242+
}
243+
if(!actions.length) {
244+
console.log('missing select')
245+
//if no actions are given, guess it's a select
246+
actions.push(new Select().add('*'));
247+
}
248+
if(missingFrom) {
249+
console.log('missing from')
250+
targets.push(new From().add(queryNode.table));
251+
}
252+
//lazy-man sorting
253+
var sortedNodes = actions.concat(targets).concat(filters);
254+
for(var i = 0; i < sortedNodes.length; i++) {
255+
var res = this.visit(sortedNodes[i]);
219256
this.output = this.output.concat(res);
220257
}
221258
//implicit 'from'
222-
if(!this._visitedFrom) {
223-
var select = this.output.slice(0, this._selectOrDeleteEndIndex);
224-
var from = this.visitFrom(new From().add(queryNode.table.toNode()));
225-
var rest = this.output.slice(this._selectOrDeleteEndIndex);
226-
this.output = select.concat(from).concat(rest);
227-
}
228259
return this;
229260
};
230261

‎lib/table.js‎

Lines changed: 7 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -70,6 +70,12 @@ Table.prototype.select = function() {
7070
return query;
7171
};
7272

73+
Table.prototype.from = function() {
74+
var query = new Query(this);
75+
query.from.apply(query, arguments);
76+
return query;
77+
}
78+
7379
Table.prototype.subQuery = function(alias) {
7480
//create the query and pass it off
7581
var query = new Query(this);
@@ -139,7 +145,7 @@ Table.prototype.__defineGetter__("nodes", function() {
139145
});
140146

141147
Table.prototype.where = function() {
142-
var query = this.select(this.star());
148+
var query = new Query(this);
143149
query.where.apply(query, arguments);
144150
return query;
145151
};

‎package.json‎

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -2,7 +2,7 @@
22
"author": "brianc <brian.m.carlson@gmail.com>",
33
"name": "sql",
44
"description": "sql builder",
5-
"version": "0.7.1",
5+
"version": "0.8.0",
66
"homepage": "https://github.com/brianc/node-sql",
77
"repository": {
88
"type": "git",

‎test/dialects/clause-ordering-tests.js‎

Lines changed: 22 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,22 @@
1+
'use strict';
2+
3+
var Harness = require('./support');
4+
var user = Harness.defineUserTable();
5+
var post = Harness.definePostTable();
6+
7+
//FROM - SELECT
8+
Harness.test({
9+
query : user.from(user.join(post).on(user.id.equals(post.userId))).select(user.name, post.content),
10+
pg : 'SELECT "user"."name", "post"."content" FROM "user" INNER JOIN "post" ON ("user"."id" = "post"."userId")',
11+
sqlite: 'SELECT "user"."name", "post"."content" FROM "user" INNER JOIN "post" ON ("user"."id" = "post"."userId")',
12+
mysql : 'SELECT `user`.`name`, `post`.`content` FROM `user` INNER JOIN `post` ON (`user`.`id` = `post`.`userId`)'
13+
});
14+
15+
//WHERE - FROM - SELECT
16+
Harness.test({
17+
query : user.where({name: ''}).from(user).select(user.id),
18+
pg : 'SELECT "user"."id" FROM "user" WHERE ("user"."name" = $1)',
19+
sqlite: 'SELECT "user"."id" FROM "user" WHERE ("user"."name" = $1)',
20+
mysql : 'SELECT `user`.`id` FROM `user` WHERE (`user`.`name` = ?)',
21+
params: ['']
22+
});

‎test/dialects/shortcut-tests.js‎

Lines changed: 9 additions & 9 deletions
Original file line numberDiff line numberDiff line change
@@ -14,17 +14,17 @@ Harness.test({
1414

1515
Harness.test({
1616
query : user.where(user.name.equals(3)),
17-
pg : 'SELECT "user".* FROM "user" WHERE ("user"."name" = $1)',
18-
sqlite: 'SELECT "user".* FROM "user" WHERE ("user"."name" = $1)',
19-
mysql : 'SELECT `user`.* FROM `user` WHERE (`user`.`name` = ?)',
17+
pg : 'SELECT * FROM "user" WHERE ("user"."name" = $1)',
18+
sqlite: 'SELECT * FROM "user" WHERE ("user"."name" = $1)',
19+
mysql : 'SELECT * FROM `user` WHERE (`user`.`name` = ?)',
2020
params : [3]
2121
});
2222

2323
Harness.test({
2424
query : user.where(user.name.equals(3)).where(user.id.equals(1)),
25-
pg : 'SELECT "user".* FROM "user" WHERE (("user"."name" = $1) AND ("user"."id" = $2))',
26-
sqlite: 'SELECT "user".* FROM "user" WHERE (("user"."name" = $1) AND ("user"."id" = $2))',
27-
mysql : 'SELECT `user`.* FROM `user` WHERE ((`user`.`name` = ?) AND (`user`.`id` = ?))',
25+
pg : 'SELECT * FROM "user" WHERE (("user"."name" = $1) AND ("user"."id" = $2))',
26+
sqlite: 'SELECT * FROM "user" WHERE (("user"."name" = $1) AND ("user"."id" = $2))',
27+
mysql : 'SELECT * FROM `user` WHERE ((`user`.`name` = ?) AND (`user`.`id` = ?))',
2828
params: [3,1]
2929
});
3030

@@ -46,8 +46,8 @@ Harness.test({
4646

4747
Harness.test({
4848
query : post.where(post.content.isNull()).or({content: ''}).and({userId: 1}),
49-
pg : 'SELECT "post".* FROM "post" WHERE ((("post"."content" IS NULL) OR ("post"."content" = $1)) AND ("post"."userId" = $2))',
50-
sqlite: 'SELECT "post".* FROM "post" WHERE ((("post"."content" IS NULL) OR ("post"."content" = $1)) AND ("post"."userId" = $2))',
51-
mysql : 'SELECT `post`.* FROM `post` WHERE (((`post`.`content` IS NULL) OR (`post`.`content` = ?)) AND (`post`.`userId` = ?))',
49+
pg : 'SELECT * FROM "post" WHERE ((("post"."content" IS NULL) OR ("post"."content" = $1)) AND ("post"."userId" = $2))',
50+
sqlite: 'SELECT * FROM "post" WHERE ((("post"."content" IS NULL) OR ("post"."content" = $1)) AND ("post"."userId" = $2))',
51+
mysql : 'SELECT * FROM `post` WHERE (((`post`.`content` IS NULL) OR (`post`.`content` = ?)) AND (`post`.`userId` = ?))',
5252
params: ['', 1]
5353
});

0 commit comments

Comments
 (0)