Add ON DUPLICATE KEY support for MySQL

dickfickling · dickfickling · commit 1385c77cf94f · 2015-03-26T14:39:12.000-07:00
diff --git a/lib/dialect/mssql.js b/lib/dialect/mssql.js
@@ -357,6 +357,10 @@ Mssql.prototype.visitQueryHelper=function(actions,targets,filters){
 //  return "SHOW INDEX FROM " + tableName;
 //};
 
+Mssql.prototype.visitOnDuplicate = function(onDuplicate) {
+  throw new Error('MSSQL does not allow onDuplicate clause.');
+};
+
 Mssql.prototype.visitReturning = function() {
   // TODO: need to add some code to the INSERT clause to support this since its the equivalent of the OUTPUT clause
   // in MS SQL which appears before the values, not at the end of the statement.
diff --git a/lib/dialect/mysql.js b/lib/dialect/mysql.js
@@ -31,6 +31,20 @@ Mysql.prototype._getParameterValue = function(value) {
   return value;
 };
 
+Mysql.prototype.visitOnDuplicate = function(onDuplicate) {
+  var params = [];
+  /* jshint boss: true */
+  for(var i = 0, node; node = onDuplicate.nodes[i]; i++) {
+    var target_col = this.visit(node);
+    params = params.concat(target_col + ' = ' + this.visit(node.value));
+  }
+  var result = [
+    'ON DUPLICATE KEY UPDATE',
+    params.join(', ')
+  ];
+  return result;
+};
+
 Mysql.prototype.visitReturning = function() {
   throw new Error('MySQL does not allow returning clause.');
 };
diff --git a/lib/dialect/postgres.js b/lib/dialect/postgres.js
@@ -117,6 +117,7 @@ Postgres.prototype.visit = function(node) {
     case 'GROUP BY'        : return this.visitGroupBy(node);
     case 'HAVING'          : return this.visitHaving(node);
     case 'RETURNING'       : return this.visitReturning(node);
+    case 'ONDUPLICATE'     : return this.visitOnDuplicate(node);
     case 'FOR UPDATE'      : return this.visitForUpdate();
     case 'FOR SHARE'       : return this.visitForShare();
     case 'TABLE'           : return this.visitTable(node);
@@ -826,6 +827,10 @@ Postgres.prototype.visitReturning = function(returning) {
   return r;
 };
 
+Postgres.prototype.visitOnDuplicate = function(onDuplicate) {
+  throw new Error('PostgreSQL does not allow onDuplicate clause.');
+};
+
 Postgres.prototype.visitModifier = function(node) {
   return [node.type, node.count.type ? this.visit(node.count) : node.count];
 };
diff --git a/lib/dialect/sqlite.js b/lib/dialect/sqlite.js
@@ -38,6 +38,10 @@ Sqlite.prototype.visitRenameColumn = function() {
   throw new Error('SQLite does not allow renaming columns.');
 };
 
+Sqlite.prototype.visitOnDuplicate = function() {
+  throw new Error('SQLite does not allow onDuplicate clause.');
+};
+
 Sqlite.prototype.visitReturning = function() {
   throw new Error('SQLite does not allow returning clause.');
 };
diff --git a/lib/node/onDuplicate.js b/lib/node/onDuplicate.js
@@ -0,0 +1,7 @@
+'use strict';
+
+var Node = require(__dirname);
+
+module.exports = Node.define({
+  type: 'ONDUPLICATE'
+});
diff --git a/lib/node/query.js b/lib/node/query.js
@@ -15,6 +15,7 @@ var Insert          = require('./insert');
 var Update          = require('./update');
 var Delete          = require('./delete');
 var Returning       = require('./returning');
+var OnDuplicate     = require('./onDuplicate');
 var ForUpdate       = require('./forUpdate');
 var ForShare        = require('./forShare');
 var Create          = require('./create');
@@ -258,6 +259,21 @@ var Query = Node.define({
     return this.add(returning);
   },
 
+  onDuplicate: function(o) {
+    var self = this;
+
+    var onDuplicate = new OnDuplicate();
+    Object.keys(o).forEach(function(key) {
+      var col = self.table.get(key);
+      if(col && !col.autoGenerated)
+        var val = o[key];
+        onDuplicate.add(col.value(ParameterNode.getNodeOrParameterNode(val)));
+    });
+
+    return self.add(onDuplicate);
+  },
+
+
   forUpdate: function() {
     assert(typeof this._select !== 'undefined', 'FOR UPDATE can be used only in a select statement');
     this.add(new ForUpdate());
diff --git a/test/dialects/insert-tests.js b/test/dialects/insert-tests.js
@@ -481,6 +481,29 @@ Harness.test({
   params: [new Buffer('whoah'), new Buffer('hey')]
 });
 
+Harness.test({
+  query: post.insert({
+    content: 'test',
+    userId: 2
+  }).onDuplicate({
+    content: 'testupdate',
+  }),
+  pg: {
+    throws: true
+  },
+  sqlite: {
+    throws: true
+  },
+  mysql: {
+    text  : 'INSERT INTO `post` (`content`, `userId`) VALUES (?, ?) ON DUPLICATE KEY UPDATE `post`.`content` = ?',
+    string: 'INSERT INTO `post` (`content`, `userId`) VALUES (\'test\', 2) ON DUPLICATE KEY UPDATE `post`.`content` = \'testupdate\''
+  },
+  mssql: {
+    throws: true
+  },
+  params: ['test', 2, 'testupdate']
+});
+
 Harness.test({
   query: post.insert([]),
 
