Add ability to preserve order in MySQL ON DUPLICATE KEY UPDATE.

Added support for the parameters in an ON DUPLICATE KEY UPDATE statement on
MySQL to be ordered, since parameter order in a MySQL UPDATE clause is
significant, in a similar manner as that described at
:ref:`updates_order_parameters`.  Pull request courtesy Maxim Bublis.

Pull-request: https://github.com/zzzeek/sqlalchemy/pull/462
Change-Id: If508d8e26dbd3c55ab1e83cf573fb4021e9d091e

2 files changed, 94 insertions, 8 deletions

diff --git a/lib/sqlalchemy/dialects/mysql/base.py b/lib/sqlalchemy/dialects/mysql/base.py
index 2bb5b5beb..65a75f9b7 100644
--- a/lib/sqlalchemy/dialects/mysql/base.py
+++ b/lib/sqlalchemy/dialects/mysql/base.py
@@ -339,7 +339,7 @@ an error or to skip performing an UPDATE.
 
 ``ON DUPLICATE KEY UPDATE`` is used to perform an update of the already
 existing row, using any combination of new values as well as values
-from the proposed insertion.   These values are specified using
+from the proposed insertion.   These values are normally specified using
 keyword arguments passed to the
 :meth:`~.mysql.dml.Insert.on_duplicate_key_update`
 given column key values (usually the name of the column, unless it
@@ -347,10 +347,33 @@ specifies :paramref:`.Column.key`) as keys and literal or SQL expressions
 as values::
 
     on_duplicate_key_stmt = insert_stmt.on_duplicate_key_update(
-        data="some data"
-        updated_at=func.current_timestamp()
+        data="some data",
+        updated_at=func.current_timestamp(),
     )
 
+In a manner similar to that of :meth:`.UpdateBase.values`, other parameter
+forms are accepted, including a single dictionary::
+
+    on_duplicate_key_stmt = insert_stmt.on_duplicate_key_update(
+        {"data": "some data", "updated_at": func.current_timestamp()},
+    )
+
+as well as a list of 2-tuples, which will automatically provide
+a parameter-ordered UPDATE statement in a manner similar to that described
+at :ref:`updates_order_parameters`.  Unlike the :class:`.Update` object,
+no special flag is needed to specify the intent since the argument form is
+this context is unambiguous::
+
+    on_duplicate_key_stmt = insert_stmt.on_duplicate_key_update(
+        [
+            ("data", "some data"),
+            ("updated_at", func.current_timestamp()),
+        ],
+    )
+
+.. versionchanged:: 1.3 support for parameter-ordered UPDATE clause within
+   MySQL ON DUPLICATE KEY UPDATE
+
 .. warning::
 
     The :meth:`.Insert.on_duplicate_key_update` method does **not** take into
@@ -359,6 +382,8 @@ as values::
     These values will not be exercised for an ON DUPLICATE KEY style of UPDATE,
     unless they are manually specified explicitly in the parameters.
 
+
+
 In order to refer to the proposed insertion row, the special alias
 :attr:`~.mysql.dml.Insert.inserted` is available as an attribute on
 the :class:`.mysql.dml.Insert` object; this object is a
@@ -899,10 +924,23 @@ class MySQLCompiler(compiler.SQLCompiler):
             self.process(binary.right, **kw))
 
     def visit_on_duplicate_key_update(self, on_duplicate, **kw):
-        cols = self.statement.table.c
+        if on_duplicate._parameter_ordering:
+            parameter_ordering = [
+                elements._column_as_key(key)
+                for key in on_duplicate._parameter_ordering
+            ]
+            ordered_keys = set(parameter_ordering)
+            cols = [
+                self.statement.table.c[key] for key in parameter_ordering
+                if key in self.statement.table.c
+            ] + [
+                c for c in self.statement.table.c if c.key not in ordered_keys
+            ]
+        else:
+            # traverse in table column order
+            cols = self.statement.table.c
 
         clauses = []
-        # traverse in table column order
         for column in cols:
             val = on_duplicate.update.get(column.key)
             if val is None:
@@ -922,7 +960,7 @@ class MySQLCompiler(compiler.SQLCompiler):
             name_text = self.preparer.quote(column.name)
             clauses.append("%s = %s" % (name_text, value_text))
 
-        non_matching = set(on_duplicate.update) - set(cols.keys())
+        non_matching = set(on_duplicate.update) - set(c.key for c in cols)
         if non_matching:
             util.warn(
                 'Additional column names not matching '
diff --git a/lib/sqlalchemy/dialects/mysql/dml.py b/lib/sqlalchemy/dialects/mysql/dml.py
index 217dc7a2a..130ef2347 100644
--- a/lib/sqlalchemy/dialects/mysql/dml.py
+++ b/lib/sqlalchemy/dialects/mysql/dml.py
@@ -3,6 +3,7 @@ from ...sql.dml import Insert as StandardInsert
 from ...sql.expression import alias
 from ...util.langhelpers import public_factory
 from ...sql.base import _generative
+from ... import exc
 from ... import util
 
 __all__ = ('Insert', 'insert')
@@ -41,7 +42,7 @@ class Insert(StandardInsert):
         return alias(self.table, name='inserted')
 
     @_generative
-    def on_duplicate_key_update(self, **kw):
+    def on_duplicate_key_update(self, *args, **kw):
         r"""
         Specifies the ON DUPLICATE KEY UPDATE clause.
 
@@ -55,6 +56,28 @@ class Insert(StandardInsert):
            These values will not be exercised for an ON DUPLICATE KEY UPDATE
            style of UPDATE, unless values are manually specified here.
 
+        :param \*args: As an alternative to passing key/value parameters,
+         a dictionary or list of 2-tuples can be passed as a single positional
+         argument.
+
+         Passing a single dictionary is equivalent to the keyword argument
+         form::
+
+            insert().on_duplicate_key_update({"name": "some name"})
+
+         Passing a list of 2-tuples indicates that the parameter assignments
+         in the UPDATE clause should be ordered as sent, in a manner similar
+         to that described for the :class:`.Update` contruct overall
+         in :ref:`updates_order_parameters`::
+
+            insert().on_duplicate_key_update(
+                [("name": "some name"), ("value", "some value")])
+
+         .. versionchanged:: 1.3 parameters can be specified as a dictionary
+            or list of 2-tuples; the latter form provides for parameter
+            ordering.
+
+
         .. versionadded:: 1.2
 
         .. seealso::
@@ -62,8 +85,21 @@ class Insert(StandardInsert):
             :ref:`mysql_insert_on_duplicate_key_update`
 
         """
+        if args and kw:
+            raise exc.ArgumentError(
+                "Can't pass kwargs and positional arguments simultaneously")
+
+        if args:
+            if len(args) > 1:
+                raise exc.ArgumentError(
+                    "Only a single dictionary or list of tuples "
+                    "is accepted positionally.")
+            values = args[0]
+        else:
+            values = kw
+
         inserted_alias = getattr(self, 'inserted_alias', None)
-        self._post_values_clause = OnDuplicateClause(inserted_alias, kw)
+        self._post_values_clause = OnDuplicateClause(inserted_alias, values)
         return self
 
 
@@ -73,8 +109,20 @@ insert = public_factory(Insert, '.dialects.mysql.insert')
 class OnDuplicateClause(ClauseElement):
     __visit_name__ = 'on_duplicate_key_update'
 
+    _parameter_ordering = None
+
     def __init__(self, inserted_alias, update):
         self.inserted_alias = inserted_alias
+
+        # auto-detect that parameters should be ordered.   This is copied from
+        # Update._proces_colparams(), however we don't look for a special flag
+        # in this case since we are not disambiguating from other use cases as
+        # we are in Update.values().
+        if isinstance(update, list) and \
+                (update and isinstance(update[0], tuple)):
+            self._parameter_ordering = [key for key, value in update]
+            update = dict(update)
+
         if not update or not isinstance(update, dict):
             raise ValueError('update parameter must be a non-empty dictionary')
         self.update = update