- [feature] Added support for MSSQL INSERT,

UPDATE, and DELETE table hints, using
new with_hint() method on UpdateBase.
[ticket:2430]

2 files changed, 98 insertions, 6 deletions

diff --git a/lib/sqlalchemy/sql/compiler.py b/lib/sqlalchemy/sql/compiler.py
index 6f010ed54..c5c6f9ec8 100644
--- a/lib/sqlalchemy/sql/compiler.py
+++ b/lib/sqlalchemy/sql/compiler.py
@@ -855,6 +855,9 @@ class SQLCompiler(engine.Compiled):
     def get_from_hint_text(self, table, text):
         return None
 
+    def get_crud_hint_text(self, table, text):
+        return None
+
     def visit_select(self, select, asfrom=False, parens=True, 
                             iswrapper=False, fromhints=None, 
                             compound_index=1, **kwargs):
@@ -1048,12 +1051,26 @@ class SQLCompiler(engine.Compiled):
 
         text = "INSERT"
 
+
         prefixes = [self.process(x) for x in insert_stmt._prefixes]
         if prefixes:
             text += " " + " ".join(prefixes)
 
         text += " INTO " + preparer.format_table(insert_stmt.table)
 
+        if insert_stmt._hints:
+            dialect_hints = dict([
+                (table, hint_text)
+                for (table, dialect), hint_text in 
+                insert_stmt._hints.items()
+                if dialect in ('*', self.dialect.name)
+            ])
+            if insert_stmt.table in dialect_hints:
+                text += " " + self.get_crud_hint_text(
+                                    insert_stmt.table, 
+                                    dialect_hints[insert_stmt.table]
+                                )
+
         if colparams or not supports_default_values:
             text += " (%s)" % ', '.join([preparer.format_column(c[0])
                        for c in colparams])
@@ -1085,21 +1102,25 @@ class SQLCompiler(engine.Compiled):
                                             extra_froms, **kw):
         """Provide a hook to override the initial table clause
         in an UPDATE statement.
-        
+
         MySQL overrides this.
 
         """
         return self.preparer.format_table(from_table)
 
-    def update_from_clause(self, update_stmt, from_table, extra_froms, **kw):
+    def update_from_clause(self, update_stmt, 
+                                from_table, extra_froms, 
+                                from_hints,
+                                **kw):
         """Provide a hook to override the generation of an 
         UPDATE..FROM clause.
-        
+
         MySQL overrides this.
 
         """
         return "FROM " + ', '.join(
-                    t._compiler_dispatch(self, asfrom=True, **kw) 
+                    t._compiler_dispatch(self, asfrom=True, 
+                                    fromhints=from_hints, **kw) 
                     for t in extra_froms)
 
     def visit_update(self, update_stmt, **kw):
@@ -1116,6 +1137,21 @@ class SQLCompiler(engine.Compiled):
                                         update_stmt.table, 
                                         extra_froms, **kw)
 
+        if update_stmt._hints:
+            dialect_hints = dict([
+                (table, hint_text)
+                for (table, dialect), hint_text in 
+                update_stmt._hints.items()
+                if dialect in ('*', self.dialect.name)
+            ])
+            if update_stmt.table in dialect_hints:
+                text += " " + self.get_crud_hint_text(
+                                    update_stmt.table, 
+                                    dialect_hints[update_stmt.table]
+                                )
+        else:
+            dialect_hints = None
+
         text += ' SET '
         if extra_froms and self.render_table_with_column_in_update_from:
             text += ', '.join(
@@ -1138,7 +1174,8 @@ class SQLCompiler(engine.Compiled):
             extra_from_text = self.update_from_clause(
                                         update_stmt, 
                                         update_stmt.table, 
-                                        extra_froms, **kw)
+                                        extra_froms, 
+                                        dialect_hints, **kw)
             if extra_from_text:
                 text += " " + extra_from_text
 
@@ -1377,6 +1414,21 @@ class SQLCompiler(engine.Compiled):
 
         text = "DELETE FROM " + self.preparer.format_table(delete_stmt.table)
 
+        if delete_stmt._hints:
+            dialect_hints = dict([
+                (table, hint_text)
+                for (table, dialect), hint_text in 
+                delete_stmt._hints.items()
+                if dialect in ('*', self.dialect.name)
+            ])
+            if delete_stmt.table in dialect_hints:
+                text += " " + self.get_crud_hint_text(
+                                    delete_stmt.table, 
+                                    dialect_hints[delete_stmt.table]
+                                )
+        else:
+            dialect_hints = None
+
         if delete_stmt._returning:
             self.returning = delete_stmt._returning
             if self.returning_precedes_values:
diff --git a/lib/sqlalchemy/sql/expression.py b/lib/sqlalchemy/sql/expression.py
index 50b7375bf..aa67f44fa 100644
--- a/lib/sqlalchemy/sql/expression.py
+++ b/lib/sqlalchemy/sql/expression.py
@@ -4833,7 +4833,7 @@ class Select(_SelectBase):
         The text of the hint is rendered in the appropriate
         location for the database backend in use, relative
         to the given :class:`.Table` or :class:`.Alias` passed as the
-        *selectable* argument. The dialect implementation
+        ``selectable`` argument. The dialect implementation
         typically uses Python string substitution syntax
         with the token ``%(name)s`` to render the name of
         the table or alias. E.g. when using Oracle, the
@@ -5319,6 +5319,7 @@ class UpdateBase(Executable, ClauseElement):
     _execution_options = \
         Executable._execution_options.union({'autocommit': True})
     kwargs = util.immutabledict()
+    _hints = util.immutabledict()
 
     def _process_colparams(self, parameters):
         if isinstance(parameters, (list, tuple)):
@@ -5399,6 +5400,45 @@ class UpdateBase(Executable, ClauseElement):
         """
         self._returning = cols
 
+    @_generative
+    def with_hint(self, text, selectable=None, dialect_name="*"):
+        """Add a table hint for a single table to this 
+        INSERT/UPDATE/DELETE statement.
+
+        .. note::
+
+         :meth:`.UpdateBase.with_hint` currently applies only to 
+         Microsoft SQL Server.  For MySQL INSERT hints, use
+         :meth:`.Insert.prefix_with`.   UPDATE/DELETE hints for 
+         MySQL will be added in a future release.
+         
+        The text of the hint is rendered in the appropriate
+        location for the database backend in use, relative
+        to the :class:`.Table` that is the subject of this
+        statement, or optionally to that of the given 
+        :class:`.Table` passed as the ``selectable`` argument.
+
+        The ``dialect_name`` option will limit the rendering of a particular
+        hint to a particular backend. Such as, to add a hint
+        that only takes effect for SQL Server::
+
+            mytable.insert().with_hint("WITH (PAGLOCK)", dialect_name="mssql")
+
+        New in 0.7.6.
+
+        :param text: Text of the hint.
+        :param selectable: optional :class:`.Table` that specifies
+         an element of the FROM clause within an UPDATE or DELETE
+         to be the subject of the hint - applies only to certain backends.
+        :param dialect_name: defaults to ``*``, if specified as the name
+         of a particular dialect, will apply these hints only when
+         that dialect is in use.
+         """
+        if selectable is None:
+            selectable = self.table
+
+        self._hints = self._hints.union({(selectable, dialect_name):text})
+
 class ValuesBase(UpdateBase):
     """Supplies support for :meth:`.ValuesBase.values` to INSERT and UPDATE constructs."""