diff options
Diffstat (limited to 'lib/sqlalchemy/sql')
| -rw-r--r-- | lib/sqlalchemy/sql/compiler.py | 3 | ||||
| -rw-r--r-- | lib/sqlalchemy/sql/functions.py | 105 | ||||
| -rw-r--r-- | lib/sqlalchemy/sql/operators.py | 8 |
3 files changed, 114 insertions, 2 deletions
diff --git a/lib/sqlalchemy/sql/compiler.py b/lib/sqlalchemy/sql/compiler.py index 75827b34e..ae1dd2c7c 100644 --- a/lib/sqlalchemy/sql/compiler.py +++ b/lib/sqlalchemy/sql/compiler.py @@ -1079,6 +1079,9 @@ class SQLCompiler(Compiled): else: return self._generate_generic_binary(binary, opstring, **kw) + def visit_function_as_comparison_op_binary(self, element, operator, **kw): + return self.process(element.sql_function, **kw) + def visit_mod_binary(self, binary, operator, **kw): if self.preparer._double_percents: return self.process(binary.left, **kw) + " %% " + \ diff --git a/lib/sqlalchemy/sql/functions.py b/lib/sqlalchemy/sql/functions.py index 78aeb3a01..27d030d4f 100644 --- a/lib/sqlalchemy/sql/functions.py +++ b/lib/sqlalchemy/sql/functions.py @@ -12,7 +12,8 @@ from . import sqltypes, schema from .base import Executable, ColumnCollection from .elements import ClauseList, Cast, Extract, _literal_as_binds, \ literal_column, _type_from_args, ColumnElement, _clone,\ - Over, BindParameter, FunctionFilter, Grouping, WithinGroup + Over, BindParameter, FunctionFilter, Grouping, WithinGroup, \ + BinaryExpression from .selectable import FromClause, Select, Alias from . import util as sqlutil from . import operators @@ -166,6 +167,73 @@ class FunctionElement(Executable, ColumnElement, FromClause): return self return FunctionFilter(self, *criterion) + def as_comparison(self, left_index, right_index): + """Interpret this expression as a boolean comparison between two values. + + A hypothetical SQL function "is_equal()" which compares to values + for equality would be written in the Core expression language as:: + + expr = func.is_equal("a", "b") + + If "is_equal()" above is comparing "a" and "b" for equality, the + :meth:`.FunctionElement.as_comparison` method would be invoked as:: + + expr = func.is_equal("a", "b").as_comparison(1, 2) + + Where above, the integer value "1" refers to the first argument of the + "is_equal()" function and the integer value "2" refers to the second. + + This would create a :class:`.BinaryExpression` that is equivalent to:: + + BinaryExpression("a", "b", operator=op.eq) + + However, at the SQL level it would still render as + "is_equal('a', 'b')". + + The ORM, when it loads a related object or collection, needs to be able + to manipulate the "left" and "right" sides of the ON clause of a JOIN + expression. The purpose of this method is to provide a SQL function + construct that can also supply this information to the ORM, when used + with the :paramref:`.relationship.primaryjoin` parameter. The return + value is a containment object called :class:`.FunctionAsBinary`. + + An ORM example is as follows:: + + class Venue(Base): + __tablename__ = 'venue' + id = Column(Integer, primary_key=True) + name = Column(String) + + descendants = relationship( + "Venue", + primaryjoin=func.instr( + remote(foreign(name)), name + "/" + ).as_comparison(1, 2) == 1, + viewonly=True, + order_by=name + ) + + Above, the "Venue" class can load descendant "Venue" objects by + determining if the name of the parent Venue is contained within the + start of the hypothetical descendant value's name, e.g. "parent1" would + match up to "parent1/child1", but not to "parent2/child1". + + Possible use cases include the "materialized path" example given above, + as well as making use of special SQL functions such as geometric + functions to create join conditions. + + :param left_index: the integer 1-based index of the function argument + that serves as the "left" side of the expression. + :param right_index: the integer 1-based index of the function argument + that serves as the "right" side of the expression. + + .. versionadded:: 1.3 + + """ + return FunctionAsBinary( + self, left_index, right_index + ) + @property def _from_objects(self): return self.clauses._from_objects @@ -281,6 +349,41 @@ class FunctionElement(Executable, ColumnElement, FromClause): return super(FunctionElement, self).self_group(against=against) +class FunctionAsBinary(BinaryExpression): + + def __init__(self, fn, left_index, right_index): + left = fn.clauses.clauses[left_index - 1] + right = fn.clauses.clauses[right_index - 1] + self.sql_function = fn + self.left_index = left_index + self.right_index = right_index + + super(FunctionAsBinary, self).__init__( + left, right, operators.function_as_comparison_op, + type_=sqltypes.BOOLEANTYPE) + + @property + def left(self): + return self.sql_function.clauses.clauses[self.left_index - 1] + + @left.setter + def left(self, value): + self.sql_function.clauses.clauses[self.left_index - 1] = value + + @property + def right(self): + return self.sql_function.clauses.clauses[self.right_index - 1] + + @right.setter + def right(self, value): + self.sql_function.clauses.clauses[self.right_index - 1] = value + + def _copy_internals(self, **kw): + clone = kw.pop('clone') + self.sql_function = clone(self.sql_function, **kw) + super(FunctionAsBinary, self)._copy_internals(**kw) + + class _FunctionGenerator(object): """Generate :class:`.Function` objects based on getattr calls.""" diff --git a/lib/sqlalchemy/sql/operators.py b/lib/sqlalchemy/sql/operators.py index 11d195455..bda9a0c86 100644 --- a/lib/sqlalchemy/sql/operators.py +++ b/lib/sqlalchemy/sql/operators.py @@ -1079,6 +1079,10 @@ def from_(): raise NotImplementedError() +def function_as_comparison_op(): + raise NotImplementedError() + + def as_(): raise NotImplementedError() @@ -1260,7 +1264,8 @@ def json_path_getitem_op(a, b): _commutative = {eq, ne, add, mul} _comparison = {eq, ne, lt, gt, ge, le, between_op, like_op, is_, - isnot, is_distinct_from, isnot_distinct_from} + isnot, is_distinct_from, isnot_distinct_from, + function_as_comparison_op} def is_comparison(op): @@ -1314,6 +1319,7 @@ _largest = util.symbol('_largest', canonical=100) _PRECEDENCE = { from_: 15, + function_as_comparison_op: 15, any_op: 15, all_op: 15, getitem: 15, |