- Added with_hint() method to Query() construct. This calls

directly down to select().with_hint() and also accepts
entities as well as tables and aliases.  See with_hint() in the
SQL section below. [ticket:921]
- Added with_hint() method to select() construct.  Specify
a table/alias, hint text, and optional dialect name, and
"hints" will be rendered in the appropriate place in the
statement.  Works for Oracle, Sybase, MySQL.  [ticket:921]

2 files changed, 84 insertions, 13 deletions

diff --git a/lib/sqlalchemy/sql/compiler.py b/lib/sqlalchemy/sql/compiler.py
index 75b3f79f0..78c65771b 100644
--- a/lib/sqlalchemy/sql/compiler.py
+++ b/lib/sqlalchemy/sql/compiler.py
@@ -628,13 +628,22 @@ class SQLCompiler(engine.Compiled):
         else:
             return self.bindtemplate % {'name':name}
 
-    def visit_alias(self, alias, asfrom=False, **kwargs):
-        if asfrom:
+    def visit_alias(self, alias, asfrom=False, ashint=False, fromhints=None, **kwargs):
+        if asfrom or ashint:
             alias_name = isinstance(alias.name, sql._generated_label) and \
                             self._truncated_identifier("alias", alias.name) or alias.name
-            
-            return self.process(alias.original, asfrom=True, **kwargs) + " AS " + \
+        if ashint:
+            return self.preparer.format_alias(alias, alias_name)
+        elif asfrom:
+            ret = self.process(alias.original, asfrom=True, **kwargs) + " AS " + \
                     self.preparer.format_alias(alias, alias_name)
+                    
+            if fromhints and alias in fromhints:
+                hinttext = self.get_from_hint_text(alias, fromhints[alias])
+                if hinttext:
+                    ret += " " + hinttext
+            
+            return ret
         else:
             return self.process(alias.original, **kwargs)
 
@@ -661,8 +670,15 @@ class SQLCompiler(engine.Compiled):
         else:
             return column
 
+    def get_select_hint_text(self, byfroms):
+        return None
+    
+    def get_from_hint_text(self, table, text):
+        return None
+        
     def visit_select(self, select, asfrom=False, parens=True, 
-                            iswrapper=False, compound_index=1, **kwargs):
+                            iswrapper=False, fromhints=None, 
+                            compound_index=1, **kwargs):
 
         entry = self.stack and self.stack[-1] or {}
         
@@ -697,6 +713,18 @@ class SQLCompiler(engine.Compiled):
         ]
         
         text = "SELECT "  # we're off to a good start !
+
+        if select._hints:
+            byfrom = dict([
+                            (from_, hinttext % {'name':self.process(from_, ashint=True)}) 
+                            for (from_, dialect), hinttext in 
+                            select._hints.iteritems() 
+                            if dialect in ('*', self.dialect.name)
+                        ])
+            hint_text = self.get_select_hint_text(byfrom)
+            if hint_text:
+                text += hint_text + " "
+                
         if select._prefixes:
             text += " ".join(self.process(x, **kwargs) for x in select._prefixes) + " "
         text += self.get_select_precolumns(select)
@@ -704,7 +732,16 @@ class SQLCompiler(engine.Compiled):
 
         if froms:
             text += " \nFROM "
-            text += ', '.join(self.process(f, asfrom=True, **kwargs) for f in froms)
+            
+            if select._hints:
+                text += ', '.join([self.process(f, 
+                                    asfrom=True, fromhints=byfrom, 
+                                    **kwargs) 
+                                for f in froms])
+            else:
+                text += ', '.join([self.process(f, 
+                                    asfrom=True, **kwargs) 
+                                for f in froms])
         else:
             text += self.default_from()
 
@@ -767,20 +804,26 @@ class SQLCompiler(engine.Compiled):
             text += " OFFSET " + str(select._offset)
         return text
 
-    def visit_table(self, table, asfrom=False, **kwargs):
-        if asfrom:
+    def visit_table(self, table, asfrom=False, ashint=False, fromhints=None, **kwargs):
+        if asfrom or ashint:
             if getattr(table, "schema", None):
-                return self.preparer.quote_schema(table.schema, table.quote_schema) + \
+                ret = self.preparer.quote_schema(table.schema, table.quote_schema) + \
                                 "." + self.preparer.quote(table.name, table.quote)
             else:
-                return self.preparer.quote(table.name, table.quote)
+                ret = self.preparer.quote(table.name, table.quote)
+            if fromhints and table in fromhints:
+                hinttext = self.get_from_hint_text(table, fromhints[table])
+                if hinttext:
+                    ret += " " + hinttext
+            return ret
         else:
             return ""
 
     def visit_join(self, join, asfrom=False, **kwargs):
-        return (self.process(join.left, asfrom=True) + \
+        return (self.process(join.left, asfrom=True, **kwargs) + \
                 (join.isouter and " LEFT OUTER JOIN " or " JOIN ") + \
-            self.process(join.right, asfrom=True) + " ON " + self.process(join.onclause))
+            self.process(join.right, asfrom=True, **kwargs) + " ON " + \
+            self.process(join.onclause, **kwargs))
 
     def visit_sequence(self, seq):
         return None
diff --git a/lib/sqlalchemy/sql/expression.py b/lib/sqlalchemy/sql/expression.py
index 1e02ba96a..3aaa06fd6 100644
--- a/lib/sqlalchemy/sql/expression.py
+++ b/lib/sqlalchemy/sql/expression.py
@@ -3557,6 +3557,7 @@ class Select(_SelectBaseMixin, FromClause):
     __visit_name__ = 'select'
     
     _prefixes = ()
+    _hints = util.frozendict()
     
     def __init__(self, 
                 columns, 
@@ -3659,7 +3660,34 @@ class Select(_SelectBaseMixin, FromClause):
         """Return the displayed list of FromClause elements."""
 
         return self._get_display_froms()
-
+    
+    @_generative
+    def with_hint(self, selectable, text, dialect_name=None):
+        """Add an indexing hint for the given selectable to this :class:`Select`.
+        
+        The text of the hint is written specific to a specific backend, and
+        typically uses Python string substitution syntax to render the name
+        of the table or alias, such as for Oracle::
+        
+            select([mytable]).with_hint(mytable, "+ index(%(name)s ix_mytable)")
+            
+        Would render SQL as::
+        
+            select /*+ index(mytable ix_mytable) */ ... from mytable
+            
+        The ``dialect_name`` option will limit the rendering of a particular hint
+        to a particular backend.  Such as, to add hints for both Oracle and
+        Sybase simultaneously::
+        
+            select([mytable]).\
+                with_hint(mytable, "+ index(%(name)s ix_mytable)", 'oracle').\
+                with_hint(mytable, "WITH INDEX ix_mytable", 'sybase')
+        
+        """
+        if not dialect_name:
+            dialect_name = '*'
+        self._hints = self._hints.union({(selectable, dialect_name):text})
+        
     @property
     def type(self):
         raise exc.InvalidRequestError("Select objects don't have a type.  "