Skip to content

Commit e55cb2f

Browse files
author
Paul Winkler
committed
add basic mysql support
1 parent baa59c9 commit e55cb2f

3 files changed

Lines changed: 369 additions & 27 deletions

File tree

‎lib/dialect/mysql.js‎

Lines changed: 338 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,338 @@
1+
'use strict';
2+
3+
var util = require('util');
4+
var assert = require('assert');
5+
var From = require(__dirname + '/../node/from');
6+
var Parameter = require(__dirname + '/../node/parameter');
7+
var Mysql = function() {
8+
this.output = [];
9+
this.params = [];
10+
};
11+
12+
Mysql.prototype.getQuery = function(queryNode) {
13+
this.visitQuery(queryNode);
14+
return { text: this.output.join(' '), values: this.params };
15+
};
16+
17+
18+
Mysql.prototype.visit = function(node) {
19+
switch(node.type) {
20+
case 'QUERY': return this.visitQuery(node);
21+
case 'SUBQUERY': return this.visitSubquery(node);
22+
case 'SELECT': return this.visitSelect(node);
23+
case 'INSERT': return this.visitInsert(node);
24+
case 'UPDATE': return this.visitUpdate(node);
25+
case 'DELETE': return this.visitDelete();
26+
case 'CREATE': return this.visitCreate(node);
27+
case 'DROP': return this.visitDrop(node);
28+
case 'ALTER': return this.visitAlter(node);
29+
case 'FROM': return this.visitFrom(node);
30+
case 'WHERE': return this.visitWhere(node);
31+
case 'ORDER BY': return this.visitOrderBy(node);
32+
case 'GROUP BY': return this.visitGroupBy(node);
33+
case 'RETURNING': return this.visitReturning(node);
34+
case 'BINARY': return this.visitBinary(node);
35+
case 'TABLE': return this.visitTable(node);
36+
case 'COLUMN': return this.visitColumn(node);
37+
case 'JOIN': return this.visitJoin(node);
38+
case 'TEXT': return node.text;
39+
case 'UNARY': return this.visitUnary(node);
40+
case 'PARAMETER': return this.visitParameter(node);
41+
case 'DEFAULT': return this.visitDefault(node);
42+
case 'IF EXISTS': return this.visitIfExists();
43+
case 'IF NOT EXISTS': return this.visitIfNotExists();
44+
case 'ADD COLUMN': return this.visitAddColumn(node);
45+
case 'DROP COLUMN': return this.visitDropColumn(node);
46+
case 'RENAME COLUMN': return this.visitRenameColumn(node);
47+
case 'LIMIT':
48+
case 'OFFSET':
49+
return this.visitModifier(node);
50+
default: throw new Error("Unrecognized node type " + node.type);
51+
}
52+
};
53+
54+
Mysql.prototype.quote = function(word) {
55+
return '`' + word + '`';
56+
};
57+
58+
Mysql.prototype.visitSelect = function(select) {
59+
var result = ['SELECT', select.nodes.map(this.visit.bind(this)).join(', ')];
60+
this._selectOrDeleteEndIndex = this.output.length + result.length;
61+
return result;
62+
};
63+
64+
Mysql.prototype.visitInsert = function(insert) {
65+
var self = this;
66+
this._visitedFrom = true;
67+
//don't use table.column for inserts
68+
this._visitedInsert = true;
69+
70+
var paramNodes = insert.getParameters()
71+
.map(function (paramSet) {
72+
return paramSet.map(function (param) {
73+
return self.visit(param);
74+
}).join(', ');
75+
}).map(function (param) {
76+
return '('+param+')';
77+
}).join(', ');
78+
79+
var result = [
80+
'INSERT INTO',
81+
this.visit(this._queryNode.table.toNode()),
82+
'(' + insert.columns.map(this.visit.bind(this)).join(', ') + ')',
83+
'VALUES', paramNodes
84+
];
85+
return result;
86+
};
87+
88+
Mysql.prototype.visitUpdate = function(update) {
89+
//don't auto-generate from clause
90+
this._visitedFrom = true;
91+
var params = [];
92+
/*jshint boss: true */
93+
for(var i = 0, node; node = update.nodes[i]; i++) {
94+
this._visitingUpdateTargetColumn = true;
95+
var target_col = this.visit(node);
96+
this._visitingUpdateTargetColumn = false;
97+
params = params.concat(target_col + ' = ' + this.visit(node.value));
98+
}
99+
var result = [
100+
'UPDATE',
101+
this.visit(this._queryNode.table.toNode()),
102+
'SET',
103+
params.join(', ')
104+
];
105+
return result;
106+
};
107+
108+
Mysql.prototype.visitDelete = function() {
109+
this._selectOrDeleteEndIndex = 1;
110+
return ['DELETE'];
111+
};
112+
113+
Mysql.prototype.visitCreate = function(create) {
114+
this._visitingCreate = true;
115+
//don't auto-generate from clause
116+
this._visitedFrom = true;
117+
var table = this._queryNode.table;
118+
var col_nodes = table.columns.map(function(col) { return col.toNode(); });
119+
120+
var result = ['CREATE TABLE'];
121+
result = result.concat(create.nodes.map(this.visit.bind(this)));
122+
result.push(this.visit(table.toNode()));
123+
result.push('(' + col_nodes.map(this.visit.bind(this)).join(', ') + ')');
124+
this._visitingCreate = false;
125+
return result;
126+
};
127+
128+
Mysql.prototype.visitDrop = function(drop) {
129+
//don't auto-generate from clause
130+
this._visitedFrom = true;
131+
var result = ['DROP TABLE'];
132+
result = result.concat(drop.nodes.map(this.visit.bind(this)));
133+
result.push(this.visit(this._queryNode.table.toNode()));
134+
return result;
135+
};
136+
137+
Mysql.prototype.visitAlter = function(alter) {
138+
this._visitingAlter = true;
139+
//don't auto-generate from clause
140+
this._visitedFrom = true;
141+
var table = this._queryNode.table;
142+
var col_nodes = table.columns.map(function(col) { return col.toNode(); });
143+
var result = [
144+
'ALTER TABLE',
145+
this.visit(table.toNode()),
146+
alter.nodes.map(this.visit.bind(this)).join(', ')
147+
];
148+
this._visitingAlter = false;
149+
return result;
150+
};
151+
152+
Mysql.prototype.visitFrom = function(from) {
153+
this._visitedFrom = true;
154+
var result = [];
155+
result.push('FROM');
156+
for(var i = 0; i < from.nodes.length; i++) {
157+
result = result.concat(this.visit(from.nodes[i]));
158+
}
159+
return result;
160+
};
161+
162+
Mysql.prototype.visitWhere = function(where) {
163+
var result = ['WHERE', where.nodes.map(this.visit.bind(this)).join(', ')];
164+
return result;
165+
};
166+
167+
Mysql.prototype.visitOrderBy = function(orderBy) {
168+
var result = ['ORDER BY', orderBy.nodes.map(this.visit.bind(this)).join(', ')];
169+
return result;
170+
};
171+
172+
Mysql.prototype.visitGroupBy = function(groupBy) {
173+
var result = ['GROUP BY', groupBy.nodes.map(this.visit.bind(this)).join(', ')];
174+
return result;
175+
};
176+
177+
Mysql.prototype.visitBinary = function(binary) {
178+
var self = this;
179+
var result = '(' + this.visit(binary.left) + ' ' + binary.operator + ' ';
180+
if (Array.isArray(binary.right)) {
181+
result += '(' + binary.right.map(function (node) {
182+
return self.visit(node);
183+
}).join(', ') + ')';
184+
}
185+
else {
186+
result += this.visit(binary.right);
187+
}
188+
result += ')';
189+
return result;
190+
};
191+
192+
Mysql.prototype.visitUnary = function(unary) {
193+
return '(' + this.visit(unary.left) + ' ' + unary.operator + ')';
194+
};
195+
196+
Mysql.prototype.visitQuery = function(queryNode) {
197+
this._queryNode = queryNode;
198+
for(var i = 0; i < queryNode.nodes.length; i ++) {
199+
var res = this.visit(queryNode.nodes[i]);
200+
this.output = this.output.concat(res);
201+
}
202+
//implicit 'from'
203+
if(!this._visitedFrom) {
204+
var select = this.output.slice(0, this._selectOrDeleteEndIndex);
205+
var from = this.visitFrom(new From().add(queryNode.table.toNode()));
206+
var rest = this.output.slice(this._selectOrDeleteEndIndex);
207+
this.output = select.concat(from).concat(rest);
208+
}
209+
return this;
210+
};
211+
212+
Mysql.prototype.visitSubquery = function(queryNode) {
213+
var result = [];
214+
for(var i = 0; i < queryNode.nodes.length; i ++) {
215+
var res = this.visit(queryNode.nodes[i]);
216+
result = result.concat(res);
217+
}
218+
//implicit 'from'
219+
if(!this._visitedFrom) {
220+
var select = result.slice(0, this._selectOrDeleteEndIndex);
221+
var from = this.visitFrom(new From().add(queryNode.table.toNode()));
222+
var rest = result.slice(this._selectOrDeleteEndIndex);
223+
result = select.concat(from).concat(rest);
224+
}
225+
result[0] = '('+result[0];
226+
result[result.length-1] = result[result.length-1] + ') ' + queryNode.alias;
227+
return result;
228+
};
229+
230+
Mysql.prototype.visitTable = function(tableNode) {
231+
var table = tableNode.table;
232+
var txt="";
233+
if(table.getSchema()) {
234+
txt = this.quote(table.getSchema());
235+
txt += '.';
236+
}
237+
txt += this.quote(table.getName());
238+
if(table.alias) {
239+
txt += ' AS ' + table.alias;
240+
}
241+
return txt;
242+
};
243+
244+
Mysql.prototype.visitColumn = function(columnNode) {
245+
var table = columnNode.table;
246+
var inSelectClause = !this._selectOrDeleteEndIndex;
247+
var txt = "";
248+
if(inSelectClause) {
249+
if (columnNode.asArray) {
250+
txt += 'array_agg(';
251+
} else if (columnNode.aggCount) {
252+
txt += 'COUNT(';
253+
}
254+
}
255+
if(!this._visitedInsert && !this._visitingUpdateTargetColumn && !this._visitingCreate && !this._visitingAlter) {
256+
if(table.alias) {
257+
txt = table.alias;
258+
} else {
259+
if(table.getSchema()) {
260+
txt = this.quote(table.getSchema());
261+
txt += '.';
262+
}
263+
txt += this.quote(table.getName());
264+
}
265+
txt += '.';
266+
}
267+
if (columnNode.star) {
268+
txt += '*';
269+
} else {
270+
txt += this.quote(columnNode.name);
271+
}
272+
if(inSelectClause && (columnNode.asArray || columnNode.aggCount)) {
273+
txt += ')';
274+
}
275+
if(inSelectClause && columnNode.alias) {
276+
txt += ' as ' + this.quote(columnNode.alias);
277+
}
278+
if(this._visitingCreate || this._visitingAddColumn) {
279+
assert(columnNode.dataType, 'dataType missing for column ' + columnNode.name +
280+
' (CREATE TABLE and ADD COLUMN statements require a dataType)');
281+
txt += ' ' + columnNode.dataType;
282+
}
283+
return txt;
284+
};
285+
286+
Mysql.prototype.visitParameter = function(parameter) {
287+
this.params.push(parameter.value());
288+
return "?";
289+
};
290+
291+
Mysql.prototype.visitDefault = function(parameter) {
292+
var params = this.params;
293+
this.params.push('DEFAULT');
294+
return "?";
295+
};
296+
297+
Mysql.prototype.visitAddColumn = function(addColumn) {
298+
this._visitingAddColumn = true;
299+
var result = ['ADD COLUMN ' + this.visit(addColumn.nodes[0])];
300+
this._visitingAddColumn = false;
301+
return result;
302+
};
303+
304+
Mysql.prototype.visitDropColumn = function(dropColumn) {
305+
return ['DROP COLUMN ' + this.visit(dropColumn.nodes[0])];
306+
};
307+
308+
Mysql.prototype.visitRenameColumn = function(renameColumn) {
309+
return ['RENAME COLUMN ' + this.visit(renameColumn.nodes[0]) + ' TO ' + this.visit(renameColumn.nodes[1])];
310+
};
311+
312+
Mysql.prototype.visitIfExists = function() {
313+
return ['IF EXISTS'];
314+
};
315+
316+
Mysql.prototype.visitIfNotExists = function() {
317+
return ['IF NOT EXISTS'];
318+
};
319+
320+
Mysql.prototype.visitJoin = function(join) {
321+
var result = [];
322+
result = result.concat(this.visit(join.from));
323+
result = result.concat(join.subType + ' JOIN');
324+
result = result.concat(this.visit(join.to));
325+
result = result.concat('ON');
326+
result = result.concat(this.visit(join.on));
327+
return result;
328+
};
329+
330+
Mysql.prototype.visitReturning = function(returning) {
331+
return ['RETURNING', returning.nodes.map(this.visit.bind(this)).join(', ')];
332+
};
333+
334+
Mysql.prototype.visitModifier = function(node) {
335+
return [node.type, node.count];
336+
};
337+
338+
module.exports = Mysql;

‎lib/index.js‎

Lines changed: 28 additions & 26 deletions
Original file line numberDiff line numberDiff line change
@@ -1,26 +1,28 @@
1-
'use strict';
2-
3-
var Table = require(__dirname + '/table');
4-
5-
var sql = {
6-
Table: Table,
7-
define: Table.define,
8-
select: function() {
9-
var Query = require(__dirname + '/node/query');
10-
var query = new Query();
11-
query.select.apply(query, arguments);
12-
return query;
13-
},
14-
setDialect: function(dialect) {
15-
switch(dialect.toLowerCase()) {
16-
case 'postgres':
17-
this.dialect = require(__dirname + '/dialect/postgres');
18-
break;
19-
default:
20-
throw new Error(dialect + ' is unsupported');
21-
}
22-
return this;
23-
}
24-
};
25-
26-
module.exports = sql.setDialect('postgres');
1+
'use strict';
2+
3+
var Table = require(__dirname + '/table');
4+
5+
var sql = {
6+
Table: Table,
7+
define: Table.define,
8+
select: function() {
9+
var Query = require(__dirname + '/node/query');
10+
var query = new Query();
11+
query.select.apply(query, arguments);
12+
return query;
13+
},
14+
setDialect: function(dialect) {
15+
switch(dialect.toLowerCase()) {
16+
case 'postgres':
17+
this.dialect = require(__dirname + '/dialect/postgres');
18+
case 'mysql':
19+
this.dialect = require(__dirname + '/dialect/mysql');
20+
break;
21+
default:
22+
throw new Error(dialect + ' is unsupported');
23+
}
24+
return this;
25+
}
26+
};
27+
28+
module.exports = sql.setDialect('postgres');

0 commit comments

Comments
 (0)