diff options
| -rw-r--r-- | doc/build/changelog/changelog_09.rst | 17 | ||||
| -rw-r--r-- | doc/build/changelog/migration_09.rst | 60 | ||||
| -rw-r--r-- | doc/build/core/sqlelement.rst | 12 | ||||
| -rw-r--r-- | lib/sqlalchemy/__init__.py | 2 | ||||
| -rw-r--r-- | lib/sqlalchemy/engine/default.py | 4 | ||||
| -rw-r--r-- | lib/sqlalchemy/orm/query.py | 5 | ||||
| -rw-r--r-- | lib/sqlalchemy/orm/relationships.py | 2 | ||||
| -rw-r--r-- | lib/sqlalchemy/sql/__init__.py | 2 | ||||
| -rw-r--r-- | lib/sqlalchemy/sql/compiler.py | 23 | ||||
| -rw-r--r-- | lib/sqlalchemy/sql/default_comparator.py | 6 | ||||
| -rw-r--r-- | lib/sqlalchemy/sql/elements.py | 296 | ||||
| -rw-r--r-- | lib/sqlalchemy/sql/expression.py | 11 | ||||
| -rw-r--r-- | lib/sqlalchemy/sql/operators.py | 10 | ||||
| -rw-r--r-- | lib/sqlalchemy/sql/selectable.py | 16 | ||||
| -rw-r--r-- | test/orm/inheritance/test_relationship.py | 1 | ||||
| -rw-r--r-- | test/sql/test_compiler.py | 68 | ||||
| -rw-r--r-- | test/sql/test_operators.py | 210 | ||||
| -rw-r--r-- | test/sql/test_types.py | 9 |
18 files changed, 636 insertions, 118 deletions
diff --git a/doc/build/changelog/changelog_09.rst b/doc/build/changelog/changelog_09.rst index 6ac4d1726..24bf6eb09 100644 --- a/doc/build/changelog/changelog_09.rst +++ b/doc/build/changelog/changelog_09.rst @@ -34,6 +34,23 @@ .. change:: :tags: feature, sql + :tickets: 2804, 2823, 2734 + + An overhaul of expression handling for special symbols particularly + with conjunctions, e.g. + ``None`` :func:`.expression.null` :func:`.expression.true` + :func:`.expression.false`, including consistency in rendering NULL + in conjunctions, "short-circuiting" of :func:`.and_` and :func:`.or_` + expressions which contain boolean constants, and rendering of + boolean constants and expressions as compared to "1" or "0" for backends + that don't feature ``true``/``false`` constants. + + .. seealso:: + + :ref:`migration_2804` + + .. change:: + :tags: feature, sql :tickets: 2838 The typing system now handles the task of rendering "literal bind" values, diff --git a/doc/build/changelog/migration_09.rst b/doc/build/changelog/migration_09.rst index f0254c789..1f3f834c9 100644 --- a/doc/build/changelog/migration_09.rst +++ b/doc/build/changelog/migration_09.rst @@ -399,6 +399,66 @@ publically. :ticket:`2812` +.. _migration_2804: + +Improved rendering of Boolean constants, NULL constants, conjunctions +---------------------------------------------------------------------- + +New capabilities have been added to the :func:`.true` and :func:`.false` +constants, in particular in conjunction with :func:`.and_` and :func:`.or_` +functions as well as the behavior of the WHERE/HAVING clauses in conjunction +with these types, boolean types overall, and the :func:`.null` constant. + +Starting with a table such as this:: + + from sqlalchemy import Table, Boolean, Integer, Column, MetaData + + t1 = Table('t', MetaData(), Column('x', Boolean()), Column('y', Integer)) + +A select construct will now render the boolean column as a binary expression +on backends that don't feature ``true``/``false`` constant beahvior:: + + >>> from sqlalchemy import select, and_, false, true + >>> from sqlalchemy.dialects import mysql, postgresql + + >>> print select([t1]).where(t1.c.x).compile(dialect=mysql.dialect()) + SELECT t.x, t.y FROM t WHERE t.x = 1 + +The :func:`.and_` and :func:`.or_` constructs will now exhibit quasi +"short circuit" behavior, that is truncating a rendered expression, when a +:func:`.true` or :func:`.false` constant is present:: + + >>> print select([t1]).where(and_(t1.c.y > 5, false())).compile( + ... dialect=postgresql.dialect()) + SELECT t.x, t.y FROM t WHERE false + +:func:`.true` can be used as the base to build up an expression:: + + >>> expr = true() + >>> expr = expr & (t1.c.y > 5) + >>> print select([t1]).where(expr) + SELECT t.x, t.y FROM t WHERE t.y > :y_1 + +The boolean constants :func:`.true` and :func:`.false` themselves render as +``0 = 1`` and ``1 = 1`` for a backend with no boolean constants:: + + >>> print select([t1]).where(and_(t1.c.y > 5, false())).compile( + ... dialect=mysql.dialect()) + SELECT t.x, t.y FROM t WHERE 0 = 1 + +Interpretation of ``None``, while not particularly valid SQL, is at least +now consistent:: + + >>> print select([t1.c.x]).where(None) + SELECT t.x FROM t WHERE NULL + + >>> print select([t1.c.x]).where(None).where(None) + SELECT t.x FROM t WHERE NULL AND NULL + + >>> print select([t1.c.x]).where(and_(None, None)) + SELECT t.x FROM t WHERE NULL AND NULL + +:ticket:`2804` New Features ============ diff --git a/doc/build/core/sqlelement.rst b/doc/build/core/sqlelement.rst index 0676f18d0..e39c988d4 100644 --- a/doc/build/core/sqlelement.rst +++ b/doc/build/core/sqlelement.rst @@ -66,11 +66,8 @@ used to construct any kind of typed SQL expression. .. autoclass:: BinaryExpression :members: - :inherited-members: - .. autoclass:: BindParameter :members: - :inherited-members: .. autoclass:: ClauseElement :members: @@ -82,7 +79,6 @@ used to construct any kind of typed SQL expression. .. autoclass:: ColumnClause :members: - :inherited-members: .. autoclass:: ColumnCollection :members: @@ -97,6 +93,14 @@ used to construct any kind of typed SQL expression. :special-members: :inherited-members: +.. autoclass:: sqlalchemy.sql.elements.False_ + :members: + +.. autoclass:: sqlalchemy.sql.elements.Null + :members: + +.. autoclass:: sqlalchemy.sql.elements.True_ + :members: .. autoclass:: sqlalchemy.sql.operators.custom_op :members: diff --git a/lib/sqlalchemy/__init__.py b/lib/sqlalchemy/__init__.py index 98156cdc9..c794d949a 100644 --- a/lib/sqlalchemy/__init__.py +++ b/lib/sqlalchemy/__init__.py @@ -21,6 +21,7 @@ from .sql import ( except_all, exists, extract, + false, func, insert, intersect, @@ -38,6 +39,7 @@ from .sql import ( select, subquery, text, + true, tuple_, type_coerce, union, diff --git a/lib/sqlalchemy/engine/default.py b/lib/sqlalchemy/engine/default.py index b2dbccb17..8fb7c3bb8 100644 --- a/lib/sqlalchemy/engine/default.py +++ b/lib/sqlalchemy/engine/default.py @@ -113,6 +113,7 @@ class DefaultDialect(interfaces.Dialect): implicit_returning=None, supports_right_nested_joins=None, case_sensitive=True, + supports_native_boolean=None, label_length=None, **kwargs): if not getattr(self, 'ported_sqla_06', True): @@ -138,7 +139,8 @@ class DefaultDialect(interfaces.Dialect): self.type_compiler = self.type_compiler(self) if supports_right_nested_joins is not None: self.supports_right_nested_joins = supports_right_nested_joins - + if supports_native_boolean is not None: + self.supports_native_boolean = supports_native_boolean self.case_sensitive = case_sensitive if label_length and label_length > self.max_identifier_length: diff --git a/lib/sqlalchemy/orm/query.py b/lib/sqlalchemy/orm/query.py index 17fb32663..3df12c0a7 100644 --- a/lib/sqlalchemy/orm/query.py +++ b/lib/sqlalchemy/orm/query.py @@ -2865,8 +2865,9 @@ class Query(object): if adapter: single_crit = adapter.traverse(single_crit) single_crit = self._adapt_clause(single_crit, False, False) - context.whereclause = sql.and_(context.whereclause, - single_crit) + context.whereclause = sql.and_( + sql.True_._ifnone(context.whereclause), + single_crit) def __str__(self): return str(self._compile_context().statement) diff --git a/lib/sqlalchemy/orm/relationships.py b/lib/sqlalchemy/orm/relationships.py index 9fc67cd38..f0b23111c 100644 --- a/lib/sqlalchemy/orm/relationships.py +++ b/lib/sqlalchemy/orm/relationships.py @@ -900,7 +900,7 @@ class RelationshipProperty(StrategizedProperty): criterion = criterion._annotate( {'no_replacement_traverse': True}) - crit = j & criterion + crit = j & sql.True_._ifnone(criterion) ex = sql.exists([1], crit, from_obj=dest).correlate_except(dest) if secondary is not None: diff --git a/lib/sqlalchemy/sql/__init__.py b/lib/sqlalchemy/sql/__init__.py index e1497e9fa..c6ecb8afd 100644 --- a/lib/sqlalchemy/sql/__init__.py +++ b/lib/sqlalchemy/sql/__init__.py @@ -35,6 +35,7 @@ from .expression import ( exists, extract, false, + False_, func, insert, intersect, @@ -55,6 +56,7 @@ from .expression import ( table, text, true, + True_, tuple_, type_coerce, union, diff --git a/lib/sqlalchemy/sql/compiler.py b/lib/sqlalchemy/sql/compiler.py index f526203ac..2bf7d3f4a 100644 --- a/lib/sqlalchemy/sql/compiler.py +++ b/lib/sqlalchemy/sql/compiler.py @@ -113,6 +113,7 @@ OPERATORS = { operators.asc_op: ' ASC', operators.nullsfirst_op: ' NULLS FIRST', operators.nullslast_op: ' NULLS LAST', + } FUNCTIONS = { @@ -608,10 +609,16 @@ class SQLCompiler(Compiled): return 'NULL' def visit_true(self, expr, **kw): - return 'true' + if self.dialect.supports_native_boolean: + return 'true' + else: + return "1" def visit_false(self, expr, **kw): - return 'false' + if self.dialect.supports_native_boolean: + return 'false' + else: + return "0" def visit_clauselist(self, clauselist, order_by_select=None, **kw): if order_by_select is not None: @@ -783,6 +790,18 @@ class SQLCompiler(Compiled): raise exc.CompileError( "Unary expression has no operator or modifier") + def visit_istrue_unary_operator(self, element, operator, **kw): + if self.dialect.supports_native_boolean: + return self.process(element.element, **kw) + else: + return "%s = 1" % self.process(element.element, **kw) + + def visit_isfalse_unary_operator(self, element, operator, **kw): + if self.dialect.supports_native_boolean: + return "NOT %s" % self.process(element.element, **kw) + else: + return "%s = 0" % self.process(element.element, **kw) + def visit_binary(self, binary, **kw): # don't allow "? = ?" to render if self.ansi_bind_rules and \ diff --git a/lib/sqlalchemy/sql/default_comparator.py b/lib/sqlalchemy/sql/default_comparator.py index 7c803ac4c..423a5b56d 100644 --- a/lib/sqlalchemy/sql/default_comparator.py +++ b/lib/sqlalchemy/sql/default_comparator.py @@ -13,7 +13,7 @@ from . import type_api from .elements import BindParameter, True_, False_, BinaryExpression, \ Null, _const_expr, _clause_element_as_expr, \ ClauseList, ColumnElement, TextClause, UnaryExpression, \ - collate, _is_literal + collate, _is_literal, _literal_as_text from .selectable import SelectBase, Alias, Selectable, ScalarSelect class _DefaultColumnComparator(operators.ColumnOperators): @@ -75,7 +75,7 @@ class _DefaultColumnComparator(operators.ColumnOperators): if op in (operators.eq, operators.ne) and \ isinstance(obj, (bool, True_, False_)): return BinaryExpression(expr, - obj, + _literal_as_text(obj), op, type_=type_api.BOOLEANTYPE, negate=negate, modifiers=kwargs) @@ -209,7 +209,7 @@ class _DefaultColumnComparator(operators.ColumnOperators): self._check_literal(expr, operators.and_, cleft), self._check_literal(expr, operators.and_, cright), operator=operators.and_, - group=False), + group=False, group_contents=False), operators.between_op) def _collate_impl(self, expr, op, other, **kw): diff --git a/lib/sqlalchemy/sql/elements.py b/lib/sqlalchemy/sql/elements.py index 251102d59..e9b995eaa 100644 --- a/lib/sqlalchemy/sql/elements.py +++ b/lib/sqlalchemy/sql/elements.py @@ -149,30 +149,6 @@ def outparam(key, type_=None): key, None, type_=type_, unique=False, isoutparam=True) -def and_(*clauses): - """Join a list of clauses together using the ``AND`` operator. - - The ``&`` operator is also overloaded on all :class:`.ColumnElement` - subclasses to produce the - same result. - - """ - if len(clauses) == 1: - return clauses[0] - return BooleanClauseList(operator=operators.and_, *clauses) - - -def or_(*clauses): - """Join a list of clauses together using the ``OR`` operator. - - The ``|`` operator is also overloaded on all - :class:`.ColumnElement` subclasses to produce the - same result. - - """ - if len(clauses) == 1: - return clauses[0] - return BooleanClauseList(operator=operators.or_, *clauses) def not_(clause): @@ -465,7 +441,10 @@ class ClauseElement(Visitable): return or_(self, other) def __invert__(self): - return self._negate() + if hasattr(self, 'negation_clause'): + return self.negation_clause + else: + return self._negate() def __bool__(self): raise TypeError("Boolean value of this clause is not defined") @@ -473,13 +452,10 @@ class ClauseElement(Visitable): __nonzero__ = __bool__ def _negate(self): - if hasattr(self, 'negation_clause'): - return self.negation_clause - else: - return UnaryExpression( - self.self_group(against=operators.inv), - operator=operators.inv, - negate=None) + return UnaryExpression( + self.self_group(against=operators.inv), + operator=operators.inv, + negate=None) def __repr__(self): friendly = getattr(self, 'description', None) @@ -537,6 +513,19 @@ class ColumnElement(ClauseElement, operators.ColumnOperators): _key_label = None _alt_names = () + def self_group(self, against=None): + if against in (operators.and_, operators.or_, operators._asbool) and \ + self.type._type_affinity is type_api.BOOLEANTYPE._type_affinity: + return AsBoolean(self, operators.istrue, operators.isfalse) + else: + return self + + def _negate(self): + if self.type._type_affinity is type_api.BOOLEANTYPE._type_affinity: + return AsBoolean(self, operators.isfalse, operators.istrue) + else: + return super(ColumnElement, self)._negate() + @util.memoized_property def type(self): return type_api.NULLTYPE @@ -1062,52 +1051,153 @@ class TextClause(Executable, ClauseElement): class Null(ColumnElement): """Represent the NULL keyword in a SQL statement. + :class:`.Null` is accessed as a constant via the + :func:`.null` function. + """ __visit_name__ = 'null' - def __init__(self): - """Return a :class:`Null` object, which compiles to ``NULL``. + @util.memoized_property + def type(self): + return type_api.NULLTYPE - """ - self.type = type_api.NULLTYPE + @classmethod + def _singleton(cls): + """Return a constant :class:`.Null` construct.""" + + return NULL def compare(self, other): return isinstance(other, Null) class False_(ColumnElement): - """Represent the ``false`` keyword in a SQL statement. + """Represent the ``false`` keyword, or equivalent, in a SQL statement. + + :class:`.False_` is accessed as a constant via the + :func:`.false` function. """ __visit_name__ = 'false' - def __init__(self): - """Return a :class:`False_` object. + @util.memoized_property + def type(self): + return type_api.BOOLEANTYPE + + def _negate(self): + return TRUE + + @classmethod + def _singleton(cls): + """Return a constant :class:`.False_` construct. + + E.g.:: + + >>> from sqlalchemy import false + >>> print select([t.c.x]).where(false()) + SELECT x FROM t WHERE false + + A backend which does not support true/false constants will render as + an expression against 1 or 0:: + + >>> print select([t.c.x]).where(false()) + SELECT x FROM t WHERE 0 = 1 + + The :func:`.true` and :func:`.false` constants also feature + "short circuit" operation within an :func:`.and_` or :func:`.or_` + conjunction:: + + >>> print select([t.c.x]).where(or_(t.c.x > 5, true())) + SELECT x FROM t WHERE true + + >>> print select([t.c.x]).where(and_(t.c.x > 5, false())) + SELECT x FROM t WHERE false + + .. versionchanged:: 0.9 :func:`.true` and :func:`.false` feature + better integrated behavior within conjunctions and on dialects + that don't support true/false constants. + + .. seealso:: + + :func:`.true` """ - self.type = type_api.BOOLEANTYPE + + return FALSE def compare(self, other): return isinstance(other, False_) class True_(ColumnElement): - """Represent the ``true`` keyword in a SQL statement. + """Represent the ``true`` keyword, or equivalent, in a SQL statement. + + :class:`.True_` is accessed as a constant via the + :func:`.true` function. """ __visit_name__ = 'true' - def __init__(self): - """Return a :class:`True_` object. + @util.memoized_property + def type(self): + return type_api.BOOLEANTYPE + + def _negate(self): + return FALSE + + @classmethod + def _ifnone(cls, other): + if other is None: + return cls._singleton() + else: + return other + + @classmethod + def _singleton(cls): + """Return a constant :class:`.True_` construct. + + E.g.:: + + >>> from sqlalchemy import true + >>> print select([t.c.x]).where(true()) + SELECT x FROM t WHERE true + + A backend which does not support true/false constants will render as + an expression against 1 or 0:: + + >>> print select([t.c.x]).where(true()) + SELECT x FROM t WHERE 1 = 1 + + The :func:`.true` and :func:`.false` constants also feature + "short circuit" operation within an :func:`.and_` or :func:`.or_` + conjunction:: + + >>> print select([t.c.x]).where(or_(t.c.x > 5, true())) + SELECT x FROM t WHERE true + + >>> print select([t.c.x]).where(and_(t.c.x > 5, false())) + SELECT x FROM t WHERE false + + .. versionchanged:: 0.9 :func:`.true` and :func:`.false` feature + better integrated behavior within conjunctions and on dialects + that don't support true/false constants. + + .. seealso:: + + :func:`.false` """ - self.type = type_api.BOOLEANTYPE + + return TRUE def compare(self, other): return isinstance(other, True_) +NULL = Null() +FALSE = False_() +TRUE = True_() class ClauseList(ClauseElement): """Describe a list of clauses, separated by an operator. @@ -1124,11 +1214,11 @@ class ClauseList(ClauseElement): if self.group_contents: self.clauses = [ _literal_as_text(clause).self_group(against=self.operator) - for clause in clauses if clause is not None] + for clause in clauses] else: self.clauses = [ _literal_as_text(clause) - for clause in clauses if clause is not None] + for clause in clauses] def __iter__(self): return iter(self.clauses) @@ -1141,10 +1231,6 @@ class ClauseList(ClauseElement): return iter(self) def append(self, clause): - # TODO: not sure if i like the 'group_contents' flag. need to - # define the difference between a ClauseList of ClauseLists, - # and a "flattened" ClauseList of ClauseLists. flatten() - # method ? if self.group_contents: self.clauses.append(_literal_as_text(clause).\ self_group(against=self.operator)) @@ -1185,13 +1271,65 @@ class ClauseList(ClauseElement): return False + class BooleanClauseList(ClauseList, ColumnElement): __visit_name__ = 'clauselist' - def __init__(self, *clauses, **kwargs): - super(BooleanClauseList, self).__init__(*clauses, **kwargs) - self.type = type_api.to_instance(kwargs.get('type_', - type_api.BOOLEANTYPE)) + def __init__(self, *arg, **kw): + raise NotImplementedError( + "BooleanClauseList has a private constructor") + + @classmethod + def _construct(cls, operator, continue_on, skip_on, *clauses, **kw): + convert_clauses = [] + + for clause in clauses: + clause = _literal_as_text(clause) + + if isinstance(clause, continue_on): + continue + elif isinstance(clause, skip_on): + return clause.self_group(against=operators._asbool) + + convert_clauses.append(clause) + + if len(convert_clauses) == 1: + return convert_clauses[0].self_group(against=operators._asbool) + elif not convert_clauses and clauses: + return clauses[0].self_group(against=operators._asbool) + + convert_clauses = [c.self_group(against=operator) + for c in convert_clauses] + + self = cls.__new__(cls) + self.clauses = convert_clauses + self.group = True + self.operator = operator + self.group_contents = True + self.type = type_api.BOOLEANTYPE + return self + + @classmethod + def and_(cls, *clauses): + """Join a list of clauses together using the ``AND`` operator. + + The ``&`` operator is also overloaded on all :class:`.ColumnElement` + subclasses to produce the + same result. + + """ + return cls._construct(operators.and_, True_, False_, *clauses) + + @classmethod + def or_(cls, *clauses): + """Join a list of clauses together using the ``OR`` operator. + + The ``|`` operator is also overloaded on all + :class:`.ColumnElement` subclasses to produce the + same result. + + """ + return cls._construct(operators.or_, False_, True_, *clauses) @property def _select_iterable(self): @@ -1203,6 +1341,12 @@ class BooleanClauseList(ClauseList, ColumnElement): else: return super(BooleanClauseList, self).self_group(against=against) + def _negate(self): + return ClauseList._negate(self) + + +and_ = BooleanClauseList.and_ +or_ = BooleanClauseList.or_ class Tuple(ClauseList, ColumnElement): """Represent a SQL tuple.""" @@ -1465,9 +1609,7 @@ class UnaryExpression(ColumnElement): type_=None, negate=None): self.operator = operator self.modifier = modifier - - self.element = _literal_as_text(element).\ - self_group(against=self.operator or self.modifier) + self.element = element.self_group(against=self.operator or self.modifier) self.type = type_api.to_instance(type_) self.negate = negate @@ -1484,7 +1626,8 @@ class UnaryExpression(ColumnElement): ORDER BY mycol DESC NULLS FIRST """ - return UnaryExpression(column, modifier=operators.nullsfirst_op) + return UnaryExpression( + _literal_as_text(column), modifier=operators.nullsfirst_op) @classmethod @@ -1500,7 +1643,8 @@ class UnaryExpression(ColumnElement): ORDER BY mycol DESC NULLS LAST """ - return UnaryExpression(column, modifier=operators.nullslast_op) + return UnaryExpression( + _literal_as_text(column), modifier=operators.nullslast_op) @classmethod @@ -1516,7 +1660,8 @@ class UnaryExpression(ColumnElement): ORDER BY mycol DESC """ - return UnaryExpression(column, modifier=operators.desc_op) + return UnaryExpression( + _literal_as_text(column), modifier=operators.desc_op) @classmethod def _create_asc(cls, column): @@ -1531,7 +1676,8 @@ class UnaryExpression(ColumnElement): ORDER BY mycol ASC """ - return UnaryExpression(column, modifier=operators.asc_op) + return UnaryExpression( + _literal_as_text(column), modifier=operators.asc_op) @classmethod def _create_distinct(cls, expr): @@ -1587,16 +1733,31 @@ class UnaryExpression(ColumnElement): modifier=self.modifier, type_=self.type) else: - return super(UnaryExpression, self)._negate() + return ClauseElement._negate(self) def self_group(self, against=None): - if self.operator and operators.is_precedent(self.operator, - against): + if self.operator and operators.is_precedent(self.operator, against): return Grouping(self) else: return self +class AsBoolean(UnaryExpression): + + def __init__(self, element, operator, negate): + self.element = element + self.type = type_api.BOOLEANTYPE + self.operator = operator + self.negate = negate + self.modifier = None + + def self_group(self, against=None): + return self + + def _negate(self): + return self.element._negate() + + class BinaryExpression(ColumnElement): """Represent an expression that is ``LEFT <operator> RIGHT``. @@ -1620,8 +1781,8 @@ class BinaryExpression(ColumnElement): if isinstance(operator, util.string_types): operator = operators.custom_op(operator) self._orig = (left, right) - self.left = _literal_as_text(left).self_group(against=operator) - self.right = _literal_as_text(right).self_group(against=operator) + self.left = left.self_group(against=operator) + self.right = right.self_group(against=operator) self.operator = operator self.type = type_api.to_instance(type_) self.negate = negate @@ -1702,6 +1863,9 @@ class Grouping(ColumnElement): self.element = element self.type = getattr(element, 'type', type_api.NULLTYPE) + def self_group(self, against=None): + return self + @property def _label(self): return getattr(self.element, '_label', None) or self.anon_label diff --git a/lib/sqlalchemy/sql/expression.py b/lib/sqlalchemy/sql/expression.py index 01091bc0a..6be32f454 100644 --- a/lib/sqlalchemy/sql/expression.py +++ b/lib/sqlalchemy/sql/expression.py @@ -33,7 +33,7 @@ from .elements import ClauseElement, ColumnElement,\ BindParameter, UnaryExpression, BooleanClauseList, \ Label, Cast, Case, ColumnClause, TextClause, Over, Null, \ True_, False_, BinaryExpression, Tuple, TypeClause, Extract, \ - Grouping, and_, or_, not_, \ + Grouping, not_, \ collate, literal_column, between,\ literal, outparam, type_coerce, ClauseList @@ -56,6 +56,8 @@ from .dml import Insert, Update, Delete, UpdateBase, ValuesBase # the functions to be available in the sqlalchemy.sql.* namespace and # to be auto-cross-documenting from the function to the class itself. +and_ = public_factory(BooleanClauseList.and_, ".expression.and_") +or_ = public_factory(BooleanClauseList.or_, ".expression.or_") bindparam = public_factory(BindParameter, ".expression.bindparam") select = public_factory(Select, ".expression.select") text = public_factory(TextClause, ".expression.tet") @@ -79,9 +81,9 @@ nullslast = public_factory(UnaryExpression._create_nullslast, ".expression.nulls asc = public_factory(UnaryExpression._create_asc, ".expression.asc") desc = public_factory(UnaryExpression._create_desc, ".expression.desc") distinct = public_factory(UnaryExpression._create_distinct, ".expression.distinct") -true = public_factory(True_, ".expression.true") -false = public_factory(False_, ".expression.false") -null = public_factory(Null, ".expression.null") +true = public_factory(True_._singleton, ".expression.true") +false = public_factory(False_._singleton, ".expression.false") +null = public_factory(Null._singleton, ".expression.null") join = public_factory(Join._create_join, ".expression.join") outerjoin = public_factory(Join._create_outerjoin, ".expression.outerjoin") insert = public_factory(Insert, ".expression.insert") @@ -89,7 +91,6 @@ update = public_factory(Update, ".expression.update") delete = public_factory(Delete, ".expression.delete") - # internal functions still being called from tests and the ORM, # these might be better off in some other namespace from .base import _from_objects diff --git a/lib/sqlalchemy/sql/operators.py b/lib/sqlalchemy/sql/operators.py index 128442158..e9b904d7c 100644 --- a/lib/sqlalchemy/sql/operators.py +++ b/lib/sqlalchemy/sql/operators.py @@ -654,6 +654,12 @@ def exists(): raise NotImplementedError() +def istrue(a): + raise NotImplementedError() + +def isfalse(a): + raise NotImplementedError() + def is_(a, b): return a.is_(b) @@ -779,6 +785,7 @@ parenthesize (a op b). """ +_asbool = util.symbol('_asbool', canonical=-10) _smallest = util.symbol('_smallest', canonical=-100) _largest = util.symbol('_largest', canonical=100) @@ -816,12 +823,15 @@ _PRECEDENCE = { between_op: 5, distinct_op: 5, inv: 5, + istrue: 5, + isfalse: 5, and_: 3, or_: 2, comma_op: -1, collate: 7, as_: -1, exists: 0, + _asbool: -10, _smallest: _smallest, _largest: _largest } diff --git a/lib/sqlalchemy/sql/selectable.py b/lib/sqlalchemy/sql/selectable.py index 43d5a084c..550e250f1 100644 --- a/lib/sqlalchemy/sql/selectable.py +++ b/lib/sqlalchemy/sql/selectable.py @@ -14,7 +14,7 @@ from .elements import ClauseElement, TextClause, ClauseList, \ from .elements import _clone, \ _literal_as_text, _interpret_as_column_or_from, _expand_cloned,\ _select_iterables, _anonymous_label, _clause_element_as_expr,\ - _cloned_intersection, _cloned_difference + _cloned_intersection, _cloned_difference, True_ from .base import Immutable, Executable, _generative, \ ColumnCollection, ColumnSet, _from_objects, Generative from . import type_api @@ -2519,13 +2519,9 @@ class Select(HasPrefixes, SelectBase): :term:`method chaining`. """ - self._reset_exported() - whereclause = _literal_as_text(whereclause) - if self._whereclause is not None: - self._whereclause = and_(self._whereclause, whereclause) - else: - self._whereclause = whereclause + self._reset_exported() + self._whereclause = and_(True_._ifnone(self._whereclause), whereclause) def append_having(self, having): """append the given expression to this select() construct's HAVING @@ -2538,10 +2534,8 @@ class Select(HasPrefixes, SelectBase): :term:`method chaining`. """ - if self._having is not None: - self._having = and_(self._having, _literal_as_text(having)) - else: - self._having = _literal_as_text(having) + self._reset_exported() + self._having = and_(True_._ifnone(self._having), having) def append_from(self, fromclause): """append the given FromClause expression to this select() construct's diff --git a/test/orm/inheritance/test_relationship.py b/test/orm/inheritance/test_relationship.py index ecb4bf407..a436ca5fc 100644 --- a/test/orm/inheritance/test_relationship.py +++ b/test/orm/inheritance/test_relationship.py @@ -415,7 +415,6 @@ class M2MFilterTest(fixtures.MappedTest): sess = create_session() e1 = sess.query(Person).filter(Engineer.name == 'e1').one() - # this works eq_(sess.query(Organization) .filter(~Organization.engineers .of_type(Engineer) diff --git a/test/sql/test_compiler.py b/test/sql/test_compiler.py index cd8ac2aef..fbb88924d 100644 --- a/test/sql/test_compiler.py +++ b/test/sql/test_compiler.py @@ -18,7 +18,7 @@ from sqlalchemy import Integer, String, MetaData, Table, Column, select, \ literal, and_, null, type_coerce, alias, or_, literal_column,\ Float, TIMESTAMP, Numeric, Date, Text, collate, union, except_,\ intersect, union_all, Boolean, distinct, join, outerjoin, asc, desc,\ - over, subquery, case + over, subquery, case, true import decimal from sqlalchemy.util import u from sqlalchemy import exc, sql, util, types, schema @@ -272,9 +272,10 @@ class SelectTest(fixtures.TestBase, AssertsCompiledSQL): "SELECT foo() AS foo_1" ) + # this is native_boolean=False for default dialect self.assert_compile( select([not_(True)], use_labels=True), - "SELECT NOT :param_1" + "SELECT :param_1 = 0" ) self.assert_compile( @@ -874,6 +875,26 @@ class SelectTest(fixtures.TestBase, AssertsCompiledSQL): "SELECT t.x FROM t WHERE t.x = :x_1 OR t.x = :x_2" ) + def test_true_short_circuit(self): + t = table('t', column('x')) + + self.assert_compile( + select([t]).where(true()), + "SELECT t.x FROM t WHERE 1 = 1", + dialect=default.DefaultDialect(supports_native_boolean=False) + ) + self.assert_compile( + select([t]).where(true()), + "SELECT t.x FROM t WHERE true", + dialect=default.DefaultDialect(supports_native_boolean=True) + ) + + self.assert_compile( + select([t]), + "SELECT t.x FROM t", + dialect=default.DefaultDialect(supports_native_boolean=True) + ) + def test_distinct(self): self.assert_compile( select([table1.c.myid.distinct()]), @@ -2921,6 +2942,7 @@ class SchemaTest(fixtures.TestBase, AssertsCompiledSQL): "(:rem_id, :datatype_id, :value)") + class CorrelateTest(fixtures.TestBase, AssertsCompiledSQL): __dialect__ = 'default' @@ -3250,13 +3272,34 @@ class CorrelateTest(fixtures.TestBase, AssertsCompiledSQL): ) class CoercionTest(fixtures.TestBase, AssertsCompiledSQL): - __dialect__ = 'default' + __dialect__ = default.DefaultDialect(supports_native_boolean=True) def _fixture(self): m = MetaData() return Table('foo', m, Column('id', Integer)) + bool_table = table('t', column('x', Boolean)) + + def test_coerce_bool_where(self): + self.assert_compile( + select([self.bool_table]).where(self.bool_table.c.x), + "SELECT t.x FROM t WHERE t.x" + ) + + def test_coerce_bool_where_non_native(self): + self.assert_compile( + select([self.bool_table]).where(self.bool_table.c.x), + "SELECT t.x FROM t WHERE t.x = 1", + dialect=default.DefaultDialect(supports_native_boolean=False) + ) + + self.assert_compile( + select([self.bool_table]).where(~self.bool_table.c.x), + "SELECT t.x FROM t WHERE t.x = 0", + dialect=default.DefaultDialect(supports_native_boolean=False) + ) + def test_null_constant(self): self.assert_compile(_literal_as_text(None), "NULL") @@ -3269,12 +3312,12 @@ class CoercionTest(fixtures.TestBase, AssertsCompiledSQL): def test_val_and_false(self): t = self._fixture() self.assert_compile(and_(t.c.id == 1, False), - "foo.id = :id_1 AND false") + "false") def test_val_and_true_coerced(self): t = self._fixture() self.assert_compile(and_(t.c.id == 1, True), - "foo.id = :id_1 AND true") + "foo.id = :id_1") def test_val_is_null_coerced(self): t = self._fixture() @@ -3282,26 +3325,21 @@ class CoercionTest(fixtures.TestBase, AssertsCompiledSQL): "foo.id IS NULL") def test_val_and_None(self): - # current convention is None in and_() or - # other clauselist is ignored. May want - # to revise this at some point. t = self._fixture() self.assert_compile(and_(t.c.id == 1, None), - "foo.id = :id_1") + "foo.id = :id_1 AND NULL") def test_None_and_val(self): - # current convention is None in and_() or - # other clauselist is ignored. May want - # to revise this at some point. t = self._fixture() - self.assert_compile(and_(t.c.id == 1, None), - "foo.id = :id_1") + self.assert_compile(and_(None, t.c.id == 1), + "NULL AND foo.id = :id_1") def test_None_and_nothing(self): # current convention is None in and_() # returns None May want # to revise this at some point. - assert and_(None) is None + self.assert_compile( + and_(None), "NULL") def test_val_and_null(self): t = self._fixture() diff --git a/test/sql/test_operators.py b/test/sql/test_operators.py index 97ce3d3dd..0124d85fa 100644 --- a/test/sql/test_operators.py +++ b/test/sql/test_operators.py @@ -9,14 +9,18 @@ from sqlalchemy.sql import operators, table import operator from sqlalchemy import String, Integer from sqlalchemy import exc +from sqlalchemy.engine import default +from sqlalchemy.sql.elements import _literal_as_text from sqlalchemy.schema import Column, Table, MetaData -from sqlalchemy.types import TypeEngine, TypeDecorator, UserDefinedType +from sqlalchemy.types import TypeEngine, TypeDecorator, UserDefinedType, Boolean from sqlalchemy.dialects import mysql, firebird, postgresql, oracle, \ sqlite, mssql from sqlalchemy import util import datetime import collections from sqlalchemy import text, literal_column +from sqlalchemy import and_, not_, between, or_ +from sqlalchemy.sql import true, false, null class LoopOperate(operators.ColumnOperators): def operate(self, op, *other, **kwargs): @@ -35,11 +39,11 @@ class DefaultColumnComparatorTest(fixtures.TestBase): left = column('left') assert left.comparator.operate(operator, right).compare( - BinaryExpression(left, right, operator) + BinaryExpression(_literal_as_text(left), _literal_as_text(right), operator) ) assert operator(left, right).compare( - BinaryExpression(left, right, operator) + BinaryExpression(_literal_as_text(left), _literal_as_text(right), operator) ) self._loop_test(operator, right) @@ -384,7 +388,205 @@ class ExtensionOperatorTest(fixtures.TestBase, testing.AssertsCompiledSQL): "x -> :x_1" ) -from sqlalchemy import and_, not_, between + +class BooleanEvalTest(fixtures.TestBase, testing.AssertsCompiledSQL): + """test standalone booleans being wrapped in an AsBoolean, as well + as true/false compilation.""" + + def _dialect(self, native_boolean): + d = default.DefaultDialect() + d.supports_native_boolean = native_boolean + return d + + def test_one(self): + c = column('x', Boolean) + self.assert_compile( + select([c]).where(c), + "SELECT x WHERE x", + dialect=self._dialect(True) + ) + + def test_two(self): + c = column('x', Boolean) + self.assert_compile( + select([c]).where(c), + "SELECT x WHERE x = 1", + dialect=self._dialect(False) + ) + + def test_three(self): + c = column('x', Boolean) + self.assert_compile( + select([c]).where(~c), + "SELECT x WHERE x = 0", + dialect=self._dialect(False) + ) + + def test_four(self): + c = column('x', Boolean) + self.assert_compile( + select([c]).where(~c), + "SELECT x WHERE NOT x", + dialect=self._dialect(True) + ) + + def test_five(self): + c = column('x', Boolean) + self.assert_compile( + select([c]).having(c), + "SELECT x HAVING x = 1", + dialect=self._dialect(False) + ) + + def test_six(self): + self.assert_compile( + or_(false(), true()), + "1 = 1", + dialect=self._dialect(False) + ) + + def test_eight(self): + self.assert_compile( + and_(false(), true()), + "false", + dialect=self._dialect(True) + ) + + def test_nine(self): + self.assert_compile( + and_(false(), true()), + "0 = 1", + dialect=self._dialect(False) + ) + + def test_ten(self): + c = column('x', Boolean) + self.assert_compile( + c == 1, + "x = :x_1", + dialect=self._dialect(False) + ) + + def test_eleven(self): + c = column('x', Boolean) + self.assert_compile( + c.is_(true()), + "x IS true", + dialect=self._dialect(True) + ) + + def test_twelve(self): + c = column('x', Boolean) + # I don't have a solution for this one yet, + # other than adding some heavy-handed conditionals + # into compiler + self.assert_compile( + c.is_(true()), + "x IS 1", + dialect=self._dialect(False) + ) + + +class ConjunctionTest(fixtures.TestBase, testing.AssertsCompiledSQL): + """test interaction of and_()/or_() with boolean , null constants + """ + __dialect__ = default.DefaultDialect(supports_native_boolean=True) + + def test_one(self): + self.assert_compile(~and_(true()), "false") + + def test_two(self): + self.assert_compile(or_(~and_(true())), "false") + + def test_three(self): + self.assert_compile(or_(and_()), "") + + def test_four(self): + x = column('x') + self.assert_compile( + and_(or_(x == 5), or_(x == 7)), + "x = :x_1 AND x = :x_2") + + + def test_five(self): + x = column("x") + self.assert_compile( + and_(true()._ifnone(None), x == 7), + "x = :x_1" + ) + + def test_six(self): + x = column("x") + self.assert_compile(or_(true(), x == 7), "true") + self.assert_compile(or_(x == 7, true()), "true") + self.assert_compile(~or_(x == 7, true()), "false") + + def test_six_pt_five(self): + x = column("x") + self.assert_compile(select([x]).where(or_(x == 7, true())), + "SELECT x WHERE true") + + self.assert_compile(select([x]).where(or_(x == 7, true())), + "SELECT x WHERE 1 = 1", + dialect=default.DefaultDialect(supports_native_boolean=False)) + + def test_seven(self): + x = column("x") + self.assert_compile( + and_(true(), x == 7, true(), x == 9), + "x = :x_1 AND x = :x_2") + + def test_eight(self): + x = column("x") + self.assert_compile( + or_(false(), x == 7, false(), x == 9), + "x = :x_1 OR x = :x_2") + + def test_nine(self): + x = column("x") + self.assert_compile( + and_(x == 7, x == 9, false(), x == 5), + "false" + ) + self.assert_compile( + ~and_(x == 7, x == 9, false(), x == 5), + "true" + ) + + def test_ten(self): + self.assert_compile( + and_(None, None), + "NULL AND NULL" + ) + + def test_eleven(self): + x = column("x") + self.assert_compile( + select([x]).where(None).where(None), + "SELECT x WHERE NULL AND NULL" + ) + + def test_twelve(self): + x = column("x") + self.assert_compile( + select([x]).where(and_(None, None)), + "SELECT x WHERE NULL AND NULL" + ) + + def test_thirteen(self): + x = column("x") + self.assert_compile( + select([x]).where(~and_(None, None)), + "SELECT x WHERE NOT (NULL AND NULL)" + ) + + def test_fourteen(self): + x = column("x") + self.assert_compile( + select([x]).where(~null()), + "SELECT x WHERE NOT NULL" + ) + class OperatorPrecedenceTest(fixtures.TestBase, testing.AssertsCompiledSQL): __dialect__ = 'default' diff --git a/test/sql/test_types.py b/test/sql/test_types.py index 13d4e378e..30a00ca56 100644 --- a/test/sql/test_types.py +++ b/test/sql/test_types.py @@ -1234,15 +1234,18 @@ class ExpressionTest(fixtures.TestBase, AssertsExecutionResults, AssertsCompiled ) self.assert_compile( and_(c1 == True, c2 == True, c3 == True), - "x = :x_1 AND x = true AND x = :x_2" + "x = :x_1 AND x = true AND x = :x_2", + dialect=default.DefaultDialect(supports_native_boolean=True) ) self.assert_compile( and_(c1 == 3, c2 == 3, c3 == 3), - "x = :x_1 AND x = :x_2 AND x = :x_3" + "x = :x_1 AND x = :x_2 AND x = :x_3", + dialect=default.DefaultDialect(supports_native_boolean=True) ) self.assert_compile( and_(c1.is_(True), c2.is_(True), c3.is_(True)), - "x IS :x_1 AND x IS true AND x IS :x_2" + "x IS :x_1 AND x IS true AND x IS :x_2", + dialect=default.DefaultDialect(supports_native_boolean=True) ) |