brianc · brianc · Jul 31, 2017 · Jul 31, 2017 · Jul 31, 2017 · Jul 31, 2017
diff --git a/lib/dialect/mssql.js b/lib/dialect/mssql.js
@@ -30,6 +30,10 @@ Mssql.prototype._quoteCharacter = '[';
 
 Mssql.prototype._arrayAggFunctionName = '';
 
+Mssql.prototype.visitReplace = function(replace) {
+  throw new Error('Mssql does not support REPLACE.');
+};
+
 Mssql.prototype._getParameterPlaceholder = function(index, value) {
   if (this.config.questionMarkParameterPlaceholder) return '?';
   return '@' + index;

diff --git a/lib/dialect/mysql.js b/lib/dialect/mysql.js
@@ -20,6 +20,42 @@ Mysql.prototype._quoteCharacter = '`';
 
 Mysql.prototype._arrayAggFunctionName = 'GROUP_CONCAT';
 
+Mysql.prototype.visitReplace = function(replace) {
+  var self = this;
+  // don't use table.column for replaces
+  this._visitedReplace = true;
+
+  var result = ['REPLACE'];
+  result = result.concat(replace.nodes.map(this.visit.bind(this)));
+  result.push('INTO ' + this.visit(this._queryNode.table.toNode()));
+  result.push('(' + replace.columns.map(this.visit.bind(this)).join(', ') + ')');
+
+  var paramNodes = replace.getParameters();
+
+  if (paramNodes.length > 0) {
+    var paramText = paramNodes.map(function (paramSet) {
+        return paramSet.map(function (param) {
+          return self.visit(param);
+        }).join(', ');
+      }).map(function (param) {
+        return '('+param+')';
+      }).join(', ');
+
+    result.push('VALUES', paramText);
+
+    if (result.slice(2, 5).join(' ') === '() VALUES ()') {
+      result.splice(2, 3, 'DEFAULT VALUES');
+    }
+  }
+
+  this._visitedReplace = false;
+
+  if (result[2] === 'DEFAULT VALUES') {
+    result[2] = '() VALUES ()';
+  }
+  return result;
+};
+
 Mysql.prototype._getParameterPlaceholder = function() {
   return '?';
 };

diff --git a/lib/dialect/oracle.js b/lib/dialect/oracle.js
@@ -17,6 +17,10 @@ util.inherits(Oracle, Postgres);
 
 Oracle.prototype._myClass = Oracle;
 
+Oracle.prototype.visitReplace = function(replace) {
+  throw new Error('Oracle does not support REPLACE.');
+};
+
 Oracle.prototype._aliasText = ' ';
 Oracle.prototype._getParameterPlaceholder = function(index, value) {
   /* jshint unused: false */

diff --git a/lib/dialect/postgres.js b/lib/dialect/postgres.js
@@ -131,6 +131,7 @@ Postgres.prototype.visit = function(node) {
     case 'SUBQUERY'        : return this.visitSubquery(node);
     case 'SELECT'          : return this.visitSelect(node);
     case 'INSERT'          : return this.visitInsert(node);
+    case 'REPLACE'         : return this.visitReplace(node);
     case 'UPDATE'          : return this.visitUpdate(node);
     case 'DELETE'          : return this.visitDelete(node);
     case 'CREATE'          : return this.visitCreate(node);
@@ -266,6 +267,10 @@ Postgres.prototype.visitInsert = function(insert) {
   return result;
 };
 
+Postgres.prototype.visitReplace = function(replace) {
+  throw new Error('Postgres does not support REPLACE.');
+};
+
 Postgres.prototype.visitUpdate = function(update) {
   // don't auto-generate from clause
   var params = [];
@@ -624,6 +629,7 @@ Postgres.prototype.visitQuery = function(queryNode) {
         break;
       case "INDEXES":
       case "INSERT":
+      case "REPLACE":
       case "UPDATE":
       case "CREATE":
       case "DROP":
@@ -725,7 +731,7 @@ Postgres.prototype.visitTable = function(tableNode) {
 
 Postgres.prototype.visitColumn = function(columnNode) {
   var table = columnNode.table;
-  var inInsertUpdateClause = this._visitedInsert || this._visitingUpdateTargetColumn;
+  var inInsertUpdateClause = this._visitedInsert || this._visitedReplace || this._visitingUpdateTargetColumn;
   var inDdlClause = this._visitingAddColumn || this._visitingAlter || this._visitingCreate;
   var inSelectClause =
     this.visitingReturning ||
@@ -1211,7 +1217,7 @@ Postgres.prototype.handleDistinct = function(actions,filters) {
 function dontParenthesizeSubQuery(parentQuery){
 	if (!parentQuery) return false;
 	if (parentQuery.nodes.length === 0) return false;
-	if (parentQuery.nodes[0].type != 'INSERT') return false;
+	if (['INSERT', 'REPLACE'].indexOf(parentQuery.nodes[0].type) === -1)  return false;
 	return true;
 }
 

diff --git a/lib/dialect/sqlite.js b/lib/dialect/sqlite.js
@@ -19,6 +19,39 @@ Sqlite.prototype._myClass = Sqlite;
 
 Sqlite.prototype._arrayAggFunctionName = 'GROUP_CONCAT';
 
+Sqlite.prototype.visitReplace = function(replace) {
+  var self = this;
+  // don't use table.column for replaces
+  this._visitedReplace = true;
+
+  var result = ['REPLACE'];
+  result = result.concat(replace.nodes.map(this.visit.bind(this)));
+  result.push('INTO ' + this.visit(this._queryNode.table.toNode()));
+  result.push('(' + replace.columns.map(this.visit.bind(this)).join(', ') + ')');
+
+  var paramNodes = replace.getParameters();
+
+  if (paramNodes.length > 0) {
+    var paramText = paramNodes.map(function (paramSet) {
+        return paramSet.map(function (param) {
+          return self.visit(param);
+        }).join(', ');
+      }).map(function (param) {
+        return '('+param+')';
+      }).join(', ');
+
+    result.push('VALUES', paramText);
+
+    if (result.slice(2, 5).join(' ') === '() VALUES ()') {
+      result.splice(2, 3, 'DEFAULT VALUES');
+    }
+  }
+
+  this._visitedReplace = false;
+
+  return result;
+};
+
 Sqlite.prototype._getParameterValue = function(value) {
   if (Buffer.isBuffer(value)) {
     value = 'x' + this._getParameterValue(value.toString('hex'));

diff --git a/lib/node/query.js b/lib/node/query.js
@@ -15,6 +15,7 @@ var OrderBy         = require('./orderBy');
 var GroupBy         = require('./groupBy');
 var Having          = require('./having');
 var Insert          = require('./insert');
+var Replace         = require('./replace');
 var Update          = require('./update');
 var Delete          = require('./delete');
 var Returning       = require('./returning');
@@ -224,6 +225,36 @@ var Query = Node.define({
 
   },
 
+  replace: function(o) {
+    var self = this;
+
+    var args = sliced(arguments);
+    // object literal
+    if (arguments.length === 1 && !o.toNode && !o.forEach) {
+      args = [];
+      Object.keys(o).forEach(function(key) {
+        var col = self.table.get(key);
+        if(col && !col.autoGenerated)
+          args.push(col.value(o[key]));
+      });
+    } else if (o.forEach) {
+      o.forEach(function(arg) {
+        return self.replace.call(self, arg);
+      });
+      return self;
+    }
+
+    if (self.replaceClause) {
+      self.replaceClause.add(args);
+      return self;
+    } else {
+      self.replaceClause = new Replace();
+      self.replaceClause.add(args);
+      return self.add(self.replaceClause);
+    }
+
+  },
+
   update: function(o) {
     var self = this;
     var update = new Update();

diff --git a/lib/node/replace.js b/lib/node/replace.js
@@ -0,0 +1,70 @@
+'use strict';
+
+var DefaultNode   = require('./default');
+var Node          = require('./');
+var ParameterNode = require('./parameter');
+
+var Replace = Node.define({
+  type: 'REPLACE',
+  constructor: function () {
+    Node.call(this);
+    this.names = [];
+    this.columns = [];
+    this.valueSets = [];
+  }
+});
+
+module.exports = Replace;
+
+Replace.prototype.add = function (nodes) {
+  var hasColumns = false;
+  var hasValues = false;
+  var self = this;
+  var values = {};
+  nodes.forEach(function (node) {
+    var column = node.toNode();
+    var name = column.name;
+    var idx = self.names.indexOf(name);
+    if (idx < 0) {
+      self.names.push(name);
+      self.columns.push(column);
+    }
+    hasColumns = true;
+    hasValues = hasValues || column.value !== undefined;
+    values[name] = column;
+  });
+
+  // When none of the columns have a value, it's ambiguous whether the user
+  // intends to replace a row of default values or append a SELECT statement
+  // later.  Resolve the ambiguity by assuming that if no columns are specified
+  // it is a row of default values, otherwise a SELECT will be added.
+  if (hasValues || !hasColumns) {
+    this.valueSets.push(values);
+  }
+
+  return self;
+};
+
+/*
+ * Get parameters for all values to be replaced. This function
+ * handles handles bulk replaces, where keys may be present
+ * in some objects and not others. When keys are not present,
+ * the replace should refer to the column value as DEFAULT.
+ */
+Replace.prototype.getParameters = function () {
+  var self = this;
+  return this.valueSets
+    .map(function (nodeDict) {
+      var set = [];
+      self.names.forEach(function (name) {
+        var node = nodeDict[name];
+        if (node) {
+          set.push(ParameterNode.getNodeOrParameterNode(node.value));
+        }
+        else {
+          set.push(new DefaultNode());
+        }
+      });
+      return set;
+    });
+};
diff --git a/lib/table.js b/lib/table.js
@@ -214,6 +214,17 @@ Table.prototype.insert = function() {
   return query;
 };
 
+Table.prototype.replace = function() {
+  var query = new Query(this);
+  if(!arguments[0] || (util.isArray(arguments[0]) && arguments[0].length === 0)){
+    query.select.call(query, this.star());
+    query.where.apply(query,["1=2"]);
+  } else {
+    query.replace.apply(query, arguments);
+  }
+  return query;
+};
+
 Table.prototype.toNode = function() {
   return new TableNode(this);
 };