diff options
Diffstat (limited to 'lib/sqlalchemy/sql')
| -rw-r--r-- | lib/sqlalchemy/sql/coercions.py | 6 | ||||
| -rw-r--r-- | lib/sqlalchemy/sql/compiler.py | 125 | ||||
| -rw-r--r-- | lib/sqlalchemy/sql/default_comparator.py | 12 | ||||
| -rw-r--r-- | lib/sqlalchemy/sql/elements.py | 2 | ||||
| -rw-r--r-- | lib/sqlalchemy/sql/operators.py | 288 |
5 files changed, 412 insertions, 21 deletions
diff --git a/lib/sqlalchemy/sql/coercions.py b/lib/sqlalchemy/sql/coercions.py index 0b250a28e..8074bcf8b 100644 --- a/lib/sqlalchemy/sql/coercions.py +++ b/lib/sqlalchemy/sql/coercions.py @@ -354,7 +354,11 @@ def expect( if not isinstance( element, - (elements.ClauseElement, schema.SchemaItem, schema.FetchedValue), + ( + elements.CompilerElement, + schema.SchemaItem, + schema.FetchedValue, + ), ): resolved = None diff --git a/lib/sqlalchemy/sql/compiler.py b/lib/sqlalchemy/sql/compiler.py index 8c2699879..45b5eab56 100644 --- a/lib/sqlalchemy/sql/compiler.py +++ b/lib/sqlalchemy/sql/compiler.py @@ -59,6 +59,7 @@ from . import crud from . import elements from . import functions from . import operators +from . import roles from . import schema from . import selectable from . import sqltypes @@ -686,7 +687,9 @@ class TypeCompiler(util.EnsureKWArg): # this was a Visitable, but to allow accurate detection of # column elements this is actually a column element -class _CompileLabel(elements.CompilerColumnElement): +class _CompileLabel( + roles.BinaryElementRole[Any], elements.CompilerColumnElement +): """lightweight label object which acts as an expression.Label.""" @@ -710,6 +713,44 @@ class _CompileLabel(elements.CompilerColumnElement): return self +class ilike_case_insensitive( + roles.BinaryElementRole[Any], elements.CompilerColumnElement +): + """produce a wrapping element for a case-insensitive portion of + an ILIKE construct. + + The construct usually renders the ``lower()`` function, but on + PostgreSQL will pass silently with the assumption that "ILIKE" + is being used. + + .. versionadded:: 2.0 + + """ + + __visit_name__ = "ilike_case_insensitive_operand" + __slots__ = "element", "comparator" + + def __init__(self, element): + self.element = element + self.comparator = element.comparator + + @property + def proxy_set(self): + return self.element.proxy_set + + @property + def type(self): + return self.element.type + + def self_group(self, **kw): + return self + + def _with_binary_element_type(self, type_): + return ilike_case_insensitive( + self.element._with_binary_element_type(type_) + ) + + class SQLCompiler(Compiled): """Default implementation of :class:`.Compiled`. @@ -2688,6 +2729,9 @@ class SQLCompiler(Compiled): def _like_percent_literal(self): return elements.literal_column("'%'", type_=sqltypes.STRINGTYPE) + def visit_ilike_case_insensitive_operand(self, element, **kw): + return f"lower({element.element._compiler_dispatch(self, **kw)})" + def visit_contains_op_binary(self, binary, operator, **kw): binary = binary._clone() percent = self._like_percent_literal @@ -2700,6 +2744,24 @@ class SQLCompiler(Compiled): binary.right = percent.concat(binary.right).concat(percent) return self.visit_not_like_op_binary(binary, operator, **kw) + def visit_icontains_op_binary(self, binary, operator, **kw): + binary = binary._clone() + percent = self._like_percent_literal + binary.left = ilike_case_insensitive(binary.left) + binary.right = percent.concat( + ilike_case_insensitive(binary.right) + ).concat(percent) + return self.visit_ilike_op_binary(binary, operator, **kw) + + def visit_not_icontains_op_binary(self, binary, operator, **kw): + binary = binary._clone() + percent = self._like_percent_literal + binary.left = ilike_case_insensitive(binary.left) + binary.right = percent.concat( + ilike_case_insensitive(binary.right) + ).concat(percent) + return self.visit_not_ilike_op_binary(binary, operator, **kw) + def visit_startswith_op_binary(self, binary, operator, **kw): binary = binary._clone() percent = self._like_percent_literal @@ -2712,6 +2774,20 @@ class SQLCompiler(Compiled): binary.right = percent._rconcat(binary.right) return self.visit_not_like_op_binary(binary, operator, **kw) + def visit_istartswith_op_binary(self, binary, operator, **kw): + binary = binary._clone() + percent = self._like_percent_literal + binary.left = ilike_case_insensitive(binary.left) + binary.right = percent._rconcat(ilike_case_insensitive(binary.right)) + return self.visit_ilike_op_binary(binary, operator, **kw) + + def visit_not_istartswith_op_binary(self, binary, operator, **kw): + binary = binary._clone() + percent = self._like_percent_literal + binary.left = ilike_case_insensitive(binary.left) + binary.right = percent._rconcat(ilike_case_insensitive(binary.right)) + return self.visit_not_ilike_op_binary(binary, operator, **kw) + def visit_endswith_op_binary(self, binary, operator, **kw): binary = binary._clone() percent = self._like_percent_literal @@ -2724,10 +2800,23 @@ class SQLCompiler(Compiled): binary.right = percent.concat(binary.right) return self.visit_not_like_op_binary(binary, operator, **kw) + def visit_iendswith_op_binary(self, binary, operator, **kw): + binary = binary._clone() + percent = self._like_percent_literal + binary.left = ilike_case_insensitive(binary.left) + binary.right = percent.concat(ilike_case_insensitive(binary.right)) + return self.visit_ilike_op_binary(binary, operator, **kw) + + def visit_not_iendswith_op_binary(self, binary, operator, **kw): + binary = binary._clone() + percent = self._like_percent_literal + binary.left = ilike_case_insensitive(binary.left) + binary.right = percent.concat(ilike_case_insensitive(binary.right)) + return self.visit_not_ilike_op_binary(binary, operator, **kw) + def visit_like_op_binary(self, binary, operator, **kw): escape = binary.modifiers.get("escape", None) - # TODO: use ternary here, not "and"/ "or" return "%s LIKE %s" % ( binary.left._compiler_dispatch(self, **kw), binary.right._compiler_dispatch(self, **kw), @@ -2749,26 +2838,22 @@ class SQLCompiler(Compiled): ) def visit_ilike_op_binary(self, binary, operator, **kw): - escape = binary.modifiers.get("escape", None) - return "lower(%s) LIKE lower(%s)" % ( - binary.left._compiler_dispatch(self, **kw), - binary.right._compiler_dispatch(self, **kw), - ) + ( - " ESCAPE " + self.render_literal_value(escape, sqltypes.STRINGTYPE) - if escape - else "" - ) + if operator is operators.ilike_op: + binary = binary._clone() + binary.left = ilike_case_insensitive(binary.left) + binary.right = ilike_case_insensitive(binary.right) + # else we assume ilower() has been applied + + return self.visit_like_op_binary(binary, operator, **kw) def visit_not_ilike_op_binary(self, binary, operator, **kw): - escape = binary.modifiers.get("escape", None) - return "lower(%s) NOT LIKE lower(%s)" % ( - binary.left._compiler_dispatch(self, **kw), - binary.right._compiler_dispatch(self, **kw), - ) + ( - " ESCAPE " + self.render_literal_value(escape, sqltypes.STRINGTYPE) - if escape - else "" - ) + if operator is operators.not_ilike_op: + binary = binary._clone() + binary.left = ilike_case_insensitive(binary.left) + binary.right = ilike_case_insensitive(binary.right) + # else we assume ilower() has been applied + + return self.visit_not_like_op_binary(binary, operator, **kw) def visit_between_op_binary(self, binary, operator, **kw): symmetric = binary.modifiers.get("symmetric", False) diff --git a/lib/sqlalchemy/sql/default_comparator.py b/lib/sqlalchemy/sql/default_comparator.py index 619be2cd1..49ca05dad 100644 --- a/lib/sqlalchemy/sql/default_comparator.py +++ b/lib/sqlalchemy/sql/default_comparator.py @@ -468,14 +468,26 @@ operator_lookup: Dict[ _boolean_compare, util.immutabledict({"negate_op": operators.not_contains_op}), ), + "icontains_op": ( + _boolean_compare, + util.immutabledict({"negate_op": operators.not_icontains_op}), + ), "startswith_op": ( _boolean_compare, util.immutabledict({"negate_op": operators.not_startswith_op}), ), + "istartswith_op": ( + _boolean_compare, + util.immutabledict({"negate_op": operators.not_istartswith_op}), + ), "endswith_op": ( _boolean_compare, util.immutabledict({"negate_op": operators.not_endswith_op}), ), + "iendswith_op": ( + _boolean_compare, + util.immutabledict({"negate_op": operators.not_iendswith_op}), + ), "desc_op": ( _scalar, util.immutabledict({"fn": UnaryExpression._create_desc}), diff --git a/lib/sqlalchemy/sql/elements.py b/lib/sqlalchemy/sql/elements.py index 29dfdc20c..cfbf24f3c 100644 --- a/lib/sqlalchemy/sql/elements.py +++ b/lib/sqlalchemy/sql/elements.py @@ -747,6 +747,8 @@ class CompilerColumnElement( __slots__ = () + _propagate_attrs = util.EMPTY_DICT + # SQLCoreOperations should be suiting the ExpressionElementRole # and ColumnsClauseRole. however the MRO issues become too elaborate diff --git a/lib/sqlalchemy/sql/operators.py b/lib/sqlalchemy/sql/operators.py index 44d63b398..49cf05f8d 100644 --- a/lib/sqlalchemy/sql/operators.py +++ b/lib/sqlalchemy/sql/operators.py @@ -966,6 +966,88 @@ class ColumnOperators(Operators): startswith_op, other, escape=escape, autoescape=autoescape ) + def istartswith( + self, + other: Any, + escape: Optional[str] = None, + autoescape: bool = False, + ) -> ColumnOperators: + r"""Implement the ``istartswith`` operator, e.g. case insensitive + version of :meth:`.ColumnOperators.startswith`. + + Produces a LIKE expression that tests against an insensitive + match for the start of a string value:: + + lower(column) LIKE lower(<other>) || '%' + + E.g.:: + + stmt = select(sometable).\ + where(sometable.c.column.istartswith("foobar")) + + Since the operator uses ``LIKE``, wildcard characters + ``"%"`` and ``"_"`` that are present inside the <other> expression + will behave like wildcards as well. For literal string + values, the :paramref:`.ColumnOperators.istartswith.autoescape` flag + may be set to ``True`` to apply escaping to occurrences of these + characters within the string value so that they match as themselves + and not as wildcard characters. Alternatively, the + :paramref:`.ColumnOperators.istartswith.escape` parameter will + establish a given character as an escape character which can be of + use when the target expression is not a literal string. + + :param other: expression to be compared. This is usually a plain + string value, but can also be an arbitrary SQL expression. LIKE + wildcard characters ``%`` and ``_`` are not escaped by default unless + the :paramref:`.ColumnOperators.istartswith.autoescape` flag is + set to True. + + :param autoescape: boolean; when True, establishes an escape character + within the LIKE expression, then applies it to all occurrences of + ``"%"``, ``"_"`` and the escape character itself within the + comparison value, which is assumed to be a literal string and not a + SQL expression. + + An expression such as:: + + somecolumn.istartswith("foo%bar", autoescape=True) + + Will render as:: + + lower(somecolumn) LIKE lower(:param) || '%' ESCAPE '/' + + With the value of ``:param`` as ``"foo/%bar"``. + + :param escape: a character which when given will render with the + ``ESCAPE`` keyword to establish that character as the escape + character. This character can then be placed preceding occurrences + of ``%`` and ``_`` to allow them to act as themselves and not + wildcard characters. + + An expression such as:: + + somecolumn.istartswith("foo/%bar", escape="^") + + Will render as:: + + lower(somecolumn) LIKE lower(:param) || '%' ESCAPE '^' + + The parameter may also be combined with + :paramref:`.ColumnOperators.istartswith.autoescape`:: + + somecolumn.istartswith("foo%bar^bat", escape="^", autoescape=True) + + Where above, the given literal parameter will be converted to + ``"foo^%bar^^bat"`` before being passed to the database. + + .. seealso:: + + :meth:`.ColumnOperators.startswith` + """ + return self.operate( + istartswith_op, other, escape=escape, autoescape=autoescape + ) + def endswith( self, other: Any, @@ -1052,6 +1134,88 @@ class ColumnOperators(Operators): endswith_op, other, escape=escape, autoescape=autoescape ) + def iendswith( + self, + other: Any, + escape: Optional[str] = None, + autoescape: bool = False, + ) -> ColumnOperators: + r"""Implement the ``iendswith`` operator, e.g. case insensitive + version of :meth:`.ColumnOperators.endswith`. + + Produces a LIKE expression that tests against an insensitive match + for the end of a string value:: + + lower(column) LIKE '%' || lower(<other>) + + E.g.:: + + stmt = select(sometable).\ + where(sometable.c.column.iendswith("foobar")) + + Since the operator uses ``LIKE``, wildcard characters + ``"%"`` and ``"_"`` that are present inside the <other> expression + will behave like wildcards as well. For literal string + values, the :paramref:`.ColumnOperators.iendswith.autoescape` flag + may be set to ``True`` to apply escaping to occurrences of these + characters within the string value so that they match as themselves + and not as wildcard characters. Alternatively, the + :paramref:`.ColumnOperators.iendswith.escape` parameter will establish + a given character as an escape character which can be of use when + the target expression is not a literal string. + + :param other: expression to be compared. This is usually a plain + string value, but can also be an arbitrary SQL expression. LIKE + wildcard characters ``%`` and ``_`` are not escaped by default unless + the :paramref:`.ColumnOperators.iendswith.autoescape` flag is + set to True. + + :param autoescape: boolean; when True, establishes an escape character + within the LIKE expression, then applies it to all occurrences of + ``"%"``, ``"_"`` and the escape character itself within the + comparison value, which is assumed to be a literal string and not a + SQL expression. + + An expression such as:: + + somecolumn.iendswith("foo%bar", autoescape=True) + + Will render as:: + + lower(somecolumn) LIKE '%' || lower(:param) ESCAPE '/' + + With the value of ``:param`` as ``"foo/%bar"``. + + :param escape: a character which when given will render with the + ``ESCAPE`` keyword to establish that character as the escape + character. This character can then be placed preceding occurrences + of ``%`` and ``_`` to allow them to act as themselves and not + wildcard characters. + + An expression such as:: + + somecolumn.iendswith("foo/%bar", escape="^") + + Will render as:: + + lower(somecolumn) LIKE '%' || lower(:param) ESCAPE '^' + + The parameter may also be combined with + :paramref:`.ColumnOperators.iendswith.autoescape`:: + + somecolumn.endswith("foo%bar^bat", escape="^", autoescape=True) + + Where above, the given literal parameter will be converted to + ``"foo^%bar^^bat"`` before being passed to the database. + + .. seealso:: + + :meth:`.ColumnOperators.endswith` + """ + return self.operate( + iendswith_op, other, escape=escape, autoescape=autoescape + ) + def contains(self, other: Any, **kw: Any) -> ColumnOperators: r"""Implement the 'contains' operator. @@ -1132,6 +1296,82 @@ class ColumnOperators(Operators): """ return self.operate(contains_op, other, **kw) + def icontains(self, other: Any, **kw: Any) -> ColumnOperators: + r"""Implement the ``icontains`` operator, e.g. case insensitive + version of :meth:`.ColumnOperators.contains`. + + Produces a LIKE expression that tests against an insensitive match + for the middle of a string value:: + + lower(column) LIKE '%' || lower(<other>) || '%' + + E.g.:: + + stmt = select(sometable).\ + where(sometable.c.column.icontains("foobar")) + + Since the operator uses ``LIKE``, wildcard characters + ``"%"`` and ``"_"`` that are present inside the <other> expression + will behave like wildcards as well. For literal string + values, the :paramref:`.ColumnOperators.icontains.autoescape` flag + may be set to ``True`` to apply escaping to occurrences of these + characters within the string value so that they match as themselves + and not as wildcard characters. Alternatively, the + :paramref:`.ColumnOperators.icontains.escape` parameter will establish + a given character as an escape character which can be of use when + the target expression is not a literal string. + + :param other: expression to be compared. This is usually a plain + string value, but can also be an arbitrary SQL expression. LIKE + wildcard characters ``%`` and ``_`` are not escaped by default unless + the :paramref:`.ColumnOperators.icontains.autoescape` flag is + set to True. + + :param autoescape: boolean; when True, establishes an escape character + within the LIKE expression, then applies it to all occurrences of + ``"%"``, ``"_"`` and the escape character itself within the + comparison value, which is assumed to be a literal string and not a + SQL expression. + + An expression such as:: + + somecolumn.icontains("foo%bar", autoescape=True) + + Will render as:: + + lower(somecolumn) LIKE '%' || lower(:param) || '%' ESCAPE '/' + + With the value of ``:param`` as ``"foo/%bar"``. + + :param escape: a character which when given will render with the + ``ESCAPE`` keyword to establish that character as the escape + character. This character can then be placed preceding occurrences + of ``%`` and ``_`` to allow them to act as themselves and not + wildcard characters. + + An expression such as:: + + somecolumn.icontains("foo/%bar", escape="^") + + Will render as:: + + lower(somecolumn) LIKE '%' || lower(:param) || '%' ESCAPE '^' + + The parameter may also be combined with + :paramref:`.ColumnOperators.contains.autoescape`:: + + somecolumn.icontains("foo%bar^bat", escape="^", autoescape=True) + + Where above, the given literal parameter will be converted to + ``"foo^%bar^^bat"`` before being passed to the database. + + .. seealso:: + + :meth:`.ColumnOperators.contains` + + """ + return self.operate(icontains_op, other, **kw) + def match(self, other: Any, **kwargs: Any) -> ColumnOperators: """Implements a database-specific 'match' operator. @@ -1689,6 +1929,22 @@ notstartswith_op = not_startswith_op @comparison_op @_operator_fn +def istartswith_op( + a: Any, b: Any, escape: Optional[str] = None, autoescape: bool = False +) -> Any: + return _escaped_like_impl(a.istartswith, b, escape, autoescape) + + +@comparison_op +@_operator_fn +def not_istartswith_op( + a: Any, b: Any, escape: Optional[str] = None, autoescape: bool = False +) -> Any: + return ~_escaped_like_impl(a.istartswith, b, escape, autoescape) + + +@comparison_op +@_operator_fn def endswith_op( a: Any, b: Any, escape: Optional[str] = None, autoescape: bool = False ) -> Any: @@ -1709,6 +1965,22 @@ notendswith_op = not_endswith_op @comparison_op @_operator_fn +def iendswith_op( + a: Any, b: Any, escape: Optional[str] = None, autoescape: bool = False +) -> Any: + return _escaped_like_impl(a.iendswith, b, escape, autoescape) + + +@comparison_op +@_operator_fn +def not_iendswith_op( + a: Any, b: Any, escape: Optional[str] = None, autoescape: bool = False +) -> Any: + return ~_escaped_like_impl(a.iendswith, b, escape, autoescape) + + +@comparison_op +@_operator_fn def contains_op( a: Any, b: Any, escape: Optional[str] = None, autoescape: bool = False ) -> Any: @@ -1729,6 +2001,22 @@ notcontains_op = not_contains_op @comparison_op @_operator_fn +def icontains_op( + a: Any, b: Any, escape: Optional[str] = None, autoescape: bool = False +) -> Any: + return _escaped_like_impl(a.icontains, b, escape, autoescape) + + +@comparison_op +@_operator_fn +def not_icontains_op( + a: Any, b: Any, escape: Optional[str] = None, autoescape: bool = False +) -> Any: + return ~_escaped_like_impl(a.icontains, b, escape, autoescape) + + +@comparison_op +@_operator_fn def match_op(a: Any, b: Any, **kw: Any) -> Any: return a.match(b, **kw) |