mirror of
https://github.com/sqlalchemy/sqlalchemy.git
synced 2026-05-06 17:01:07 -04:00
Mike Bayer
8be3b096a6
Added the ability to create custom SQL constructs that can define new clauses within SELECT, INSERT, UPDATE, and DELETE statements without needing to modify the construction or compilation code of of :class:`.Select`, :class:`.Insert`, :class:`.Update`, or :class:`.Delete` directly. Support for testing these constructs, including caching support, is present along with an example test suite. The use case for these constructs is expected to be third party dialects for NewSQL or other novel styles of database that introduce new clauses to these statements. A new example suite is included which illustrates the ``QUALIFY`` SQL construct used by several NewSQL databases which includes a cachable implementation as well as a test suite. Since these extensions start to make it a bit crowded with how many kinds of "options" we have on statements, did some naming / documentation changes with existing constructs on Executable, in particular to distinguish ExecutableOption from SyntaxExtension. Fixes: #12195 Change-Id: I4a44ee5bbc3d8b1b640837680c09d25b1b7077af
68 lines
2.3 KiB
Python
68 lines
2.3 KiB
Python
from __future__ import annotations
|
|
|
|
from sqlalchemy.ext.compiler import compiles
|
|
from sqlalchemy.sql import ClauseElement
|
|
from sqlalchemy.sql import coercions
|
|
from sqlalchemy.sql import ColumnElement
|
|
from sqlalchemy.sql import ColumnExpressionArgument
|
|
from sqlalchemy.sql import roles
|
|
from sqlalchemy.sql import Select
|
|
from sqlalchemy.sql import SyntaxExtension
|
|
from sqlalchemy.sql import visitors
|
|
|
|
|
|
def qualify(predicate: ColumnExpressionArgument[bool]) -> Qualify:
|
|
"""Return a QUALIFY construct
|
|
|
|
E.g.::
|
|
|
|
stmt = select(qt_table).ext(
|
|
qualify(func.row_number().over(order_by=qt_table.c.o))
|
|
)
|
|
|
|
"""
|
|
return Qualify(predicate)
|
|
|
|
|
|
class Qualify(SyntaxExtension, ClauseElement):
|
|
"""Define the QUALIFY class."""
|
|
|
|
predicate: ColumnElement[bool]
|
|
"""A single column expression that is the predicate within the QUALIFY."""
|
|
|
|
_traverse_internals = [
|
|
("predicate", visitors.InternalTraversal.dp_clauseelement)
|
|
]
|
|
"""This structure defines how SQLAlchemy can do a deep traverse of internal
|
|
contents of this structure. This is mostly used for cache key generation.
|
|
If the traversal is not written yet, the ``inherit_cache=False`` class
|
|
level attribute may be used to skip caching for the construct.
|
|
"""
|
|
|
|
def __init__(self, predicate: ColumnExpressionArgument):
|
|
self.predicate = coercions.expect(
|
|
roles.WhereHavingRole, predicate, apply_propagate_attrs=self
|
|
)
|
|
|
|
def apply_to_select(self, select_stmt: Select) -> None:
|
|
"""Called when the :meth:`.Select.ext` method is called.
|
|
|
|
The extension should apply itself to the :class:`.Select`, typically
|
|
using :meth:`.HasStatementExtensions.apply_syntax_extension_point`,
|
|
which receives a callable that receives a list of current elements to
|
|
be concatenated together and then returns a new list of elements to be
|
|
concatenated together in the final structure. The
|
|
:meth:`.SyntaxExtension.append_replacing_same_type` callable is
|
|
usually used for this.
|
|
|
|
"""
|
|
select_stmt.apply_syntax_extension_point(
|
|
self.append_replacing_same_type, "post_criteria"
|
|
)
|
|
|
|
|
|
@compiles(Qualify)
|
|
def _compile_qualify(element, compiler, **kw):
|
|
"""a compiles extension that delivers the SQL text for Qualify"""
|
|
return f"QUALIFY {compiler.process(element.predicate, **kw)}"
|