diff options
| -rw-r--r-- | doc/build/changelog/changelog_10.rst | 8 | ||||
| -rw-r--r-- | lib/sqlalchemy/orm/query.py | 29 | ||||
| -rw-r--r-- | lib/sqlalchemy/sql/compiler.py | 12 | ||||
| -rw-r--r-- | lib/sqlalchemy/sql/selectable.py | 36 | ||||
| -rw-r--r-- | test/orm/test_query.py | 22 | ||||
| -rw-r--r-- | test/sql/test_compiler.py | 17 |
6 files changed, 118 insertions, 6 deletions
diff --git a/doc/build/changelog/changelog_10.rst b/doc/build/changelog/changelog_10.rst index d6782c917..7d7548e11 100644 --- a/doc/build/changelog/changelog_10.rst +++ b/doc/build/changelog/changelog_10.rst @@ -22,6 +22,14 @@ on compatibility concerns, see :doc:`/changelog/migration_10`. .. change:: + :tags: feature, sql + :tickets: 3206 + + Added new method :meth:`.Select.with_statement_hint` and ORM + method :meth:`.Query.with_statement_hint` to support statement-level + hints that are not specific to a table. + + .. change:: :tags: bug, sqlite :tickets: 3203 :pullreq: bitbucket:31 diff --git a/lib/sqlalchemy/orm/query.py b/lib/sqlalchemy/orm/query.py index 60948293b..e6b2bf537 100644 --- a/lib/sqlalchemy/orm/query.py +++ b/lib/sqlalchemy/orm/query.py @@ -1145,7 +1145,8 @@ class Query(object): @_generative() def with_hint(self, selectable, text, dialect_name='*'): - """Add an indexing hint for the given entity or selectable to + """Add an indexing or other executional context + hint for the given entity or selectable to this :class:`.Query`. Functionality is passed straight through to @@ -1153,11 +1154,35 @@ class Query(object): with the addition that ``selectable`` can be a :class:`.Table`, :class:`.Alias`, or ORM entity / mapped class /etc. + + .. seealso:: + + :meth:`.Query.with_statement_hint` + """ - selectable = inspect(selectable).selectable + if selectable is not None: + selectable = inspect(selectable).selectable self._with_hints += ((selectable, text, dialect_name),) + def with_statement_hint(self, text, dialect_name='*'): + """add a statement hint to this :class:`.Select`. + + This method is similar to :meth:`.Select.with_hint` except that + it does not require an individual table, and instead applies to the + statement as a whole. + + This feature calls down into :meth:`.Select.with_statement_hint`. + + .. versionadded:: 1.0.0 + + .. seealso:: + + :meth:`.Query.with_hint` + + """ + return self.with_hint(None, text, dialect_name) + @_generative() def execution_options(self, **kwargs): """ Set non-SQL options which take effect during execution. diff --git a/lib/sqlalchemy/sql/compiler.py b/lib/sqlalchemy/sql/compiler.py index 5149fa4fe..abda31358 100644 --- a/lib/sqlalchemy/sql/compiler.py +++ b/lib/sqlalchemy/sql/compiler.py @@ -1331,6 +1331,9 @@ class SQLCompiler(Compiled): def get_crud_hint_text(self, table, text): return None + def get_statement_hint_text(self, hint_texts): + return " ".join(hint_texts) + def _transform_select_for_nested_joins(self, select): """Rewrite any "a JOIN (b JOIN c)" expression as "a JOIN (select * from b JOIN c) AS anon", to support @@ -1609,6 +1612,15 @@ class SQLCompiler(Compiled): if select._for_update_arg is not None: text += self.for_update_clause(select, **kwargs) + if select._statement_hints: + per_dialect = [ + ht for (dialect_name, ht) + in select._statement_hints + if dialect_name in ('*', self.dialect.name) + ] + if per_dialect: + text += " " + self.get_statement_hint_text(per_dialect) + if self.ctes and \ compound_index == 0 and toplevel: text = self._render_cte_clause() + text diff --git a/lib/sqlalchemy/sql/selectable.py b/lib/sqlalchemy/sql/selectable.py index 9e8cb3bc5..248048662 100644 --- a/lib/sqlalchemy/sql/selectable.py +++ b/lib/sqlalchemy/sql/selectable.py @@ -2153,6 +2153,7 @@ class Select(HasPrefixes, GenerativeSelect): _prefixes = () _hints = util.immutabledict() + _statement_hints = () _distinct = False _from_cloned = None _correlate = () @@ -2525,10 +2526,30 @@ class Select(HasPrefixes, GenerativeSelect): return self._get_display_froms() + def with_statement_hint(self, text, dialect_name='*'): + """add a statement hint to this :class:`.Select`. + + This method is similar to :meth:`.Select.with_hint` except that + it does not require an individual table, and instead applies to the + statement as a whole. + + Hints here are specific to the backend database and may include + directives such as isolation levels, file directives, fetch directives, + etc. + + .. versionadded:: 1.0.0 + + .. seealso:: + + :meth:`.Select.with_hint` + + """ + return self.with_hint(None, text, dialect_name) + @_generative def with_hint(self, selectable, text, dialect_name='*'): - """Add an indexing hint for the given selectable to this - :class:`.Select`. + """Add an indexing or other executional context hint for the given + selectable to this :class:`.Select`. The text of the hint is rendered in the appropriate location for the database backend in use, relative @@ -2555,9 +2576,16 @@ class Select(HasPrefixes, GenerativeSelect): mytable, "+ index(%(name)s ix_mytable)", 'oracle').\\ with_hint(mytable, "WITH INDEX ix_mytable", 'sybase') + .. seealso:: + + :meth:`.Select.with_statement_hint` + """ - self._hints = self._hints.union( - {(selectable, dialect_name): text}) + if selectable is None: + self._statement_hints += ((dialect_name, text), ) + else: + self._hints = self._hints.union( + {(selectable, dialect_name): text}) @property def type(self): diff --git a/test/orm/test_query.py b/test/orm/test_query.py index c9f0a5db0..52e266a08 100644 --- a/test/orm/test_query.py +++ b/test/orm/test_query.py @@ -2517,6 +2517,28 @@ class HintsTest(QueryTest, AssertsCompiledSQL): "ON users_1.id > users.id", dialect=dialect ) + def test_statement_hints(self): + User = self.classes.User + + sess = create_session() + stmt = sess.query(User).\ + with_statement_hint("test hint one").\ + with_statement_hint("test hint two").\ + with_statement_hint("test hint three", "postgresql") + + self.assert_compile( + stmt, + "SELECT users.id AS users_id, users.name AS users_name " + "FROM users test hint one test hint two", + ) + + self.assert_compile( + stmt, + "SELECT users.id AS users_id, users.name AS users_name " + "FROM users test hint one test hint two test hint three", + dialect='postgresql' + ) + class TextTest(QueryTest, AssertsCompiledSQL): __dialect__ = 'default' diff --git a/test/sql/test_compiler.py b/test/sql/test_compiler.py index d47b58f1f..3e6b87351 100644 --- a/test/sql/test_compiler.py +++ b/test/sql/test_compiler.py @@ -2420,6 +2420,23 @@ class SelectTest(fixtures.TestBase, AssertsCompiledSQL): dialect=dialect ) + def test_statement_hints(self): + + stmt = select([table1.c.myid]).\ + with_statement_hint("test hint one").\ + with_statement_hint("test hint two", 'mysql') + + self.assert_compile( + stmt, + "SELECT mytable.myid FROM mytable test hint one", + ) + + self.assert_compile( + stmt, + "SELECT mytable.myid FROM mytable test hint one test hint two", + dialect='mysql' + ) + def test_literal_as_text_fromstring(self): self.assert_compile( and_(text("a"), text("b")), |