Skip to content

Commit 1fbaf62

Browse files
lukechildsbrianc
lukechilds
authored and
brianc
committed
Support REPLACE (brianc#368)
* Support REPLACE * Add REPLACE to default Postgres dialect * Make sure REPLACE doesn't get prepended with SELECT * * Make sure REPLACE doesn't use table.column syntax * REPLACE throws on unsupported dialects * Move replace to dialect prototypes * Copy MySQL insert syntax for repalce * Run insert tests against replace * Fix misplaced semicolon * Treat subquery parenthesis the same on INSERT and REPLACE
1 parent f135dfc commit 1fbaf62

9 files changed

Lines changed: 1197 additions & 2 deletions

File tree

‎lib/dialect/mssql.js‎

Lines changed: 4 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -30,6 +30,10 @@ Mssql.prototype._quoteCharacter = '[';
3030

3131
Mssql.prototype._arrayAggFunctionName = '';
3232

33+
Mssql.prototype.visitReplace = function(replace) {
34+
throw new Error('Mssql does not support REPLACE.');
35+
};
36+
3337
Mssql.prototype._getParameterPlaceholder = function(index, value) {
3438
if (this.config.questionMarkParameterPlaceholder) return '?';
3539
return '@' + index;

‎lib/dialect/mysql.js‎

Lines changed: 36 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -20,6 +20,42 @@ Mysql.prototype._quoteCharacter = '`';
2020

2121
Mysql.prototype._arrayAggFunctionName = 'GROUP_CONCAT';
2222

23+
Mysql.prototype.visitReplace = function(replace) {
24+
var self = this;
25+
// don't use table.column for replaces
26+
this._visitedReplace = true;
27+
28+
var result = ['REPLACE'];
29+
result = result.concat(replace.nodes.map(this.visit.bind(this)));
30+
result.push('INTO ' + this.visit(this._queryNode.table.toNode()));
31+
result.push('(' + replace.columns.map(this.visit.bind(this)).join(', ') + ')');
32+
33+
var paramNodes = replace.getParameters();
34+
35+
if (paramNodes.length > 0) {
36+
var paramText = paramNodes.map(function (paramSet) {
37+
return paramSet.map(function (param) {
38+
return self.visit(param);
39+
}).join(', ');
40+
}).map(function (param) {
41+
return '('+param+')';
42+
}).join(', ');
43+
44+
result.push('VALUES', paramText);
45+
46+
if (result.slice(2, 5).join(' ') === '() VALUES ()') {
47+
result.splice(2, 3, 'DEFAULT VALUES');
48+
}
49+
}
50+
51+
this._visitedReplace = false;
52+
53+
if (result[2] === 'DEFAULT VALUES') {
54+
result[2] = '() VALUES ()';
55+
}
56+
return result;
57+
};
58+
2359
Mysql.prototype._getParameterPlaceholder = function() {
2460
return '?';
2561
};

‎lib/dialect/oracle.js‎

Lines changed: 4 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -17,6 +17,10 @@ util.inherits(Oracle, Postgres);
1717

1818
Oracle.prototype._myClass = Oracle;
1919

20+
Oracle.prototype.visitReplace = function(replace) {
21+
throw new Error('Oracle does not support REPLACE.');
22+
};
23+
2024
Oracle.prototype._aliasText = ' ';
2125
Oracle.prototype._getParameterPlaceholder = function(index, value) {
2226
/* jshint unused: false */

‎lib/dialect/postgres.js‎

Lines changed: 8 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -131,6 +131,7 @@ Postgres.prototype.visit = function(node) {
131131
case 'SUBQUERY' : return this.visitSubquery(node);
132132
case 'SELECT' : return this.visitSelect(node);
133133
case 'INSERT' : return this.visitInsert(node);
134+
case 'REPLACE' : return this.visitReplace(node);
134135
case 'UPDATE' : return this.visitUpdate(node);
135136
case 'DELETE' : return this.visitDelete(node);
136137
case 'CREATE' : return this.visitCreate(node);
@@ -266,6 +267,10 @@ Postgres.prototype.visitInsert = function(insert) {
266267
return result;
267268
};
268269

270+
Postgres.prototype.visitReplace = function(replace) {
271+
throw new Error('Postgres does not support REPLACE.');
272+
};
273+
269274
Postgres.prototype.visitUpdate = function(update) {
270275
// don't auto-generate from clause
271276
var params = [];
@@ -624,6 +629,7 @@ Postgres.prototype.visitQuery = function(queryNode) {
624629
break;
625630
case "INDEXES":
626631
case "INSERT":
632+
case "REPLACE":
627633
case "UPDATE":
628634
case "CREATE":
629635
case "DROP":
@@ -725,7 +731,7 @@ Postgres.prototype.visitTable = function(tableNode) {
725731

726732
Postgres.prototype.visitColumn = function(columnNode) {
727733
var table = columnNode.table;
728-
var inInsertUpdateClause = this._visitedInsert || this._visitingUpdateTargetColumn;
734+
var inInsertUpdateClause = this._visitedInsert || this._visitedReplace || this._visitingUpdateTargetColumn;
729735
var inDdlClause = this._visitingAddColumn || this._visitingAlter || this._visitingCreate;
730736
var inSelectClause =
731737
this.visitingReturning ||
@@ -1211,7 +1217,7 @@ Postgres.prototype.handleDistinct = function(actions,filters) {
12111217
function dontParenthesizeSubQuery(parentQuery){
12121218
if (!parentQuery) return false;
12131219
if (parentQuery.nodes.length === 0) return false;
1214-
if (parentQuery.nodes[0].type != 'INSERT') return false;
1220+
if (['INSERT', 'REPLACE'].indexOf(parentQuery.nodes[0].type) === -1) return false;
12151221
return true;
12161222
}
12171223

‎lib/dialect/sqlite.js‎

Lines changed: 33 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -19,6 +19,39 @@ Sqlite.prototype._myClass = Sqlite;
1919

2020
Sqlite.prototype._arrayAggFunctionName = 'GROUP_CONCAT';
2121

22+
Sqlite.prototype.visitReplace = function(replace) {
23+
var self = this;
24+
// don't use table.column for replaces
25+
this._visitedReplace = true;
26+
27+
var result = ['REPLACE'];
28+
result = result.concat(replace.nodes.map(this.visit.bind(this)));
29+
result.push('INTO ' + this.visit(this._queryNode.table.toNode()));
30+
result.push('(' + replace.columns.map(this.visit.bind(this)).join(', ') + ')');
31+
32+
var paramNodes = replace.getParameters();
33+
34+
if (paramNodes.length > 0) {
35+
var paramText = paramNodes.map(function (paramSet) {
36+
return paramSet.map(function (param) {
37+
return self.visit(param);
38+
}).join(', ');
39+
}).map(function (param) {
40+
return '('+param+')';
41+
}).join(', ');
42+
43+
result.push('VALUES', paramText);
44+
45+
if (result.slice(2, 5).join(' ') === '() VALUES ()') {
46+
result.splice(2, 3, 'DEFAULT VALUES');
47+
}
48+
}
49+
50+
this._visitedReplace = false;
51+
52+
return result;
53+
};
54+
2255
Sqlite.prototype._getParameterValue = function(value) {
2356
if (Buffer.isBuffer(value)) {
2457
value = 'x' + this._getParameterValue(value.toString('hex'));

‎lib/node/query.js‎

Lines changed: 31 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -15,6 +15,7 @@ var OrderBy = require('./orderBy');
1515
var GroupBy = require('./groupBy');
1616
var Having = require('./having');
1717
var Insert = require('./insert');
18+
var Replace = require('./replace');
1819
var Update = require('./update');
1920
var Delete = require('./delete');
2021
var Returning = require('./returning');
@@ -224,6 +225,36 @@ var Query = Node.define({
224225

225226
},
226227

228+
replace: function(o) {
229+
var self = this;
230+
231+
var args = sliced(arguments);
232+
// object literal
233+
if (arguments.length === 1 && !o.toNode && !o.forEach) {
234+
args = [];
235+
Object.keys(o).forEach(function(key) {
236+
var col = self.table.get(key);
237+
if(col && !col.autoGenerated)
238+
args.push(col.value(o[key]));
239+
});
240+
} else if (o.forEach) {
241+
o.forEach(function(arg) {
242+
return self.replace.call(self, arg);
243+
});
244+
return self;
245+
}
246+
247+
if (self.replaceClause) {
248+
self.replaceClause.add(args);
249+
return self;
250+
} else {
251+
self.replaceClause = new Replace();
252+
self.replaceClause.add(args);
253+
return self.add(self.replaceClause);
254+
}
255+
256+
},
257+
227258
update: function(o) {
228259
var self = this;
229260
var update = new Update();

‎lib/node/replace.js‎

Lines changed: 70 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,70 @@
1+
'use strict';
2+
3+
var DefaultNode = require('./default');
4+
var Node = require('./');
5+
var ParameterNode = require('./parameter');
6+
7+
var Replace = Node.define({
8+
type: 'REPLACE',
9+
constructor: function () {
10+
Node.call(this);
11+
this.names = [];
12+
this.columns = [];
13+
this.valueSets = [];
14+
}
15+
});
16+
17+
module.exports = Replace;
18+
19+
Replace.prototype.add = function (nodes) {
20+
var hasColumns = false;
21+
var hasValues = false;
22+
var self = this;
23+
var values = {};
24+
nodes.forEach(function (node) {
25+
var column = node.toNode();
26+
var name = column.name;
27+
var idx = self.names.indexOf(name);
28+
if (idx < 0) {
29+
self.names.push(name);
30+
self.columns.push(column);
31+
}
32+
hasColumns = true;
33+
hasValues = hasValues || column.value !== undefined;
34+
values[name] = column;
35+
});
36+
37+
// When none of the columns have a value, it's ambiguous whether the user
38+
// intends to replace a row of default values or append a SELECT statement
39+
// later. Resolve the ambiguity by assuming that if no columns are specified
40+
// it is a row of default values, otherwise a SELECT will be added.
41+
if (hasValues || !hasColumns) {
42+
this.valueSets.push(values);
43+
}
44+
45+
return self;
46+
};
47+
48+
/*
49+
* Get parameters for all values to be replaced. This function
50+
* handles handles bulk replaces, where keys may be present
51+
* in some objects and not others. When keys are not present,
52+
* the replace should refer to the column value as DEFAULT.
53+
*/
54+
Replace.prototype.getParameters = function () {
55+
var self = this;
56+
return this.valueSets
57+
.map(function (nodeDict) {
58+
var set = [];
59+
self.names.forEach(function (name) {
60+
var node = nodeDict[name];
61+
if (node) {
62+
set.push(ParameterNode.getNodeOrParameterNode(node.value));
63+
}
64+
else {
65+
set.push(new DefaultNode());
66+
}
67+
});
68+
return set;
69+
});
70+
};

‎lib/table.js‎

Lines changed: 11 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -214,6 +214,17 @@ Table.prototype.insert = function() {
214214
return query;
215215
};
216216

217+
Table.prototype.replace = function() {
218+
var query = new Query(this);
219+
if(!arguments[0] || (util.isArray(arguments[0]) && arguments[0].length === 0)){
220+
query.select.call(query, this.star());
221+
query.where.apply(query,["1=2"]);
222+
} else {
223+
query.replace.apply(query, arguments);
224+
}
225+
return query;
226+
};
227+
217228
Table.prototype.toNode = function() {
218229
return new TableNode(this);
219230
};

0 commit comments

Comments
 (0)