- 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]

6 files changed, 135 insertions, 24 deletions

diff --git a/lib/sqlalchemy/dialects/mysql/base.py b/lib/sqlalchemy/dialects/mysql/base.py
index 873dfd16c..f9bb48235 100644
--- a/lib/sqlalchemy/dialects/mysql/base.py
+++ b/lib/sqlalchemy/dialects/mysql/base.py
@@ -1154,7 +1154,10 @@ class MySQLCompiler(compiler.SQLCompiler):
         
     def visit_match_op(self, binary, **kw):
         return "MATCH (%s) AGAINST (%s IN BOOLEAN MODE)" % (self.process(binary.left), self.process(binary.right))
-        
+
+    def get_from_hint_text(self, table, text):
+        return text
+
     def visit_typeclause(self, typeclause):
         type_ = typeclause.type.dialect_impl(self.dialect)
         if isinstance(type_, sqltypes.Integer):
@@ -1204,11 +1207,11 @@ class MySQLCompiler(compiler.SQLCompiler):
         # support can be added, preferably after dialects are
         # refactored to be version-sensitive.
         return ''.join(
-            (self.process(join.left, asfrom=True),
+            (self.process(join.left, asfrom=True, **kwargs),
              (join.isouter and " LEFT OUTER JOIN " or " INNER JOIN "),
-             self.process(join.right, asfrom=True),
+             self.process(join.right, asfrom=True, **kwargs),
              " ON ",
-             self.process(join.onclause)))
+             self.process(join.onclause, **kwargs)))
 
     def for_update_clause(self, select):
         if select.for_update == 'read':
diff --git a/lib/sqlalchemy/dialects/oracle/base.py b/lib/sqlalchemy/dialects/oracle/base.py
index 332fa805d..475730988 100644
--- a/lib/sqlalchemy/dialects/oracle/base.py
+++ b/lib/sqlalchemy/dialects/oracle/base.py
@@ -342,6 +342,11 @@ class OracleCompiler(compiler.SQLCompiler):
     def visit_match_op(self, binary, **kw):
         return "CONTAINS (%s, %s)" % (self.process(binary.left), self.process(binary.right))
     
+    def get_select_hint_text(self, byfroms):
+        return " ".join(
+            "/*+ %s */" % text for table, text in byfroms.items()
+        )
+        
     def function_argspec(self, fn, **kw):
         if len(fn.clauses) > 0:
             return compiler.SQLCompiler.function_argspec(self, fn, **kw)
@@ -360,7 +365,9 @@ class OracleCompiler(compiler.SQLCompiler):
         if self.dialect.use_ansi:
             return compiler.SQLCompiler.visit_join(self, join, **kwargs)
         else:
-            return self.process(join.left, asfrom=True) + ", " + self.process(join.right, asfrom=True)
+            kwargs['asfrom'] = True
+            return self.process(join.left, **kwargs) + \
+                        ", " + self.process(join.right, **kwargs)
 
     def _get_nonansi_join_whereclause(self, froms):
         clauses = []
@@ -392,14 +399,18 @@ class OracleCompiler(compiler.SQLCompiler):
     def visit_sequence(self, seq):
         return self.dialect.identifier_preparer.format_sequence(seq) + ".nextval"
 
-    def visit_alias(self, alias, asfrom=False, **kwargs):
+    def visit_alias(self, alias, asfrom=False, ashint=False, **kwargs):
         """Oracle doesn't like ``FROM table AS alias``.  Is the AS standard SQL??"""
-
-        if asfrom:
+        
+        if asfrom or ashint:
             alias_name = isinstance(alias.name, expression._generated_label) and \
                             self._truncated_identifier("alias", alias.name) or alias.name
-            
-            return self.process(alias.original, asfrom=asfrom, **kwargs) + " " + self.preparer.format_alias(alias, alias_name)
+        
+        if ashint:
+            return alias_name
+        elif asfrom:
+            return self.process(alias.original, asfrom=asfrom, **kwargs) + \
+                            " " + self.preparer.format_alias(alias, alias_name)
         else:
             return self.process(alias.original, **kwargs)
 
diff --git a/lib/sqlalchemy/dialects/sybase/base.py b/lib/sqlalchemy/dialects/sybase/base.py
index aaec7a504..79e32b968 100644
--- a/lib/sqlalchemy/dialects/sybase/base.py
+++ b/lib/sqlalchemy/dialects/sybase/base.py
@@ -277,6 +277,9 @@ class SybaseSQLCompiler(compiler.SQLCompiler):
             s += "START AT %s " % (select._offset+1,)
         return s
 
+    def get_from_hint_text(self, table, text):
+        return text
+
     def limit_clause(self, select):
         # Limit in sybase is after the select keyword
         return ""
diff --git a/lib/sqlalchemy/orm/query.py b/lib/sqlalchemy/orm/query.py
index 5b9169c2e..e98ad8937 100644
--- a/lib/sqlalchemy/orm/query.py
+++ b/lib/sqlalchemy/orm/query.py
@@ -84,6 +84,7 @@ class Query(object):
     _params = util.frozendict()
     _attributes = util.frozendict()
     _with_options = ()
+    _with_hints = ()
     
     def __init__(self, entities, session=None):
         self.session = session
@@ -719,6 +720,21 @@ class Query(object):
                 opt.process_query(self)
 
     @_generative()
+    def with_hint(self, selectable, text, dialect_name=None):
+        """Add an indexing hint for the given entity or selectable to 
+        this :class:`Query`.
+        
+        Functionality is passed straight through to 
+        :meth:`~sqlalchemy.sql.expression.Select.with_hint`, 
+        with the addition that ``selectable`` can be a 
+        :class:`Table`, :class:`Alias`, or ORM entity / mapped class 
+        /etc.
+        """
+        mapper, selectable, is_aliased_class = _entity_info(selectable)
+        
+        self._with_hints += ((selectable, text, dialect_name),)
+        
+    @_generative()
     def execution_options(self, **kwargs):
         """ Set non-SQL options which take effect during execution.
         
@@ -2053,7 +2069,10 @@ class Query(object):
                         order_by=context.order_by,
                         **self._select_args
                     )
-
+            
+            for hint in self._with_hints:
+                inner = inner.with_hint(*hint)
+                
             if self._correlate:
                 inner = inner.correlate(*self._correlate)
 
@@ -2108,6 +2127,10 @@ class Query(object):
                             order_by=context.order_by,
                             **self._select_args
                         )
+
+            for hint in self._with_hints:
+                statement = statement.with_hint(*hint)
+                        
             if self._execution_options:
                 statement = statement.execution_options(**self._execution_options)
 
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.  "