1# sql/default_comparator.py 2# Copyright (C) 2005-2018 the SQLAlchemy authors and contributors 3# <see AUTHORS file> 4# 5# This module is part of SQLAlchemy and is released under 6# the MIT License: http://www.opensource.org/licenses/mit-license.php 7 8"""Default implementation of SQL comparison operations. 9""" 10 11from .. import exc, util 12from . import type_api 13from . import operators 14from .elements import BindParameter, True_, False_, BinaryExpression, \ 15 Null, _const_expr, _clause_element_as_expr, \ 16 ClauseList, ColumnElement, TextClause, UnaryExpression, \ 17 collate, _is_literal, _literal_as_text, ClauseElement, and_, or_, \ 18 Slice, Visitable, _literal_as_binds, CollectionAggregate 19from .selectable import SelectBase, Alias, Selectable, ScalarSelect 20 21 22def _boolean_compare(expr, op, obj, negate=None, reverse=False, 23 _python_is_types=(util.NoneType, bool), 24 result_type = None, 25 **kwargs): 26 27 if result_type is None: 28 result_type = type_api.BOOLEANTYPE 29 30 if isinstance(obj, _python_is_types + (Null, True_, False_)): 31 32 # allow x ==/!= True/False to be treated as a literal. 33 # this comes out to "== / != true/false" or "1/0" if those 34 # constants aren't supported and works on all platforms 35 if op in (operators.eq, operators.ne) and \ 36 isinstance(obj, (bool, True_, False_)): 37 return BinaryExpression(expr, 38 _literal_as_text(obj), 39 op, 40 type_=result_type, 41 negate=negate, modifiers=kwargs) 42 elif op in (operators.is_distinct_from, operators.isnot_distinct_from): 43 return BinaryExpression(expr, 44 _literal_as_text(obj), 45 op, 46 type_=result_type, 47 negate=negate, modifiers=kwargs) 48 else: 49 # all other None/True/False uses IS, IS NOT 50 if op in (operators.eq, operators.is_): 51 return BinaryExpression(expr, _const_expr(obj), 52 operators.is_, 53 negate=operators.isnot) 54 elif op in (operators.ne, operators.isnot): 55 return BinaryExpression(expr, _const_expr(obj), 56 operators.isnot, 57 negate=operators.is_) 58 else: 59 raise exc.ArgumentError( 60 "Only '=', '!=', 'is_()', 'isnot()', " 61 "'is_distinct_from()', 'isnot_distinct_from()' " 62 "operators can be used with None/True/False") 63 else: 64 obj = _check_literal(expr, op, obj) 65 66 if reverse: 67 return BinaryExpression(obj, 68 expr, 69 op, 70 type_=result_type, 71 negate=negate, modifiers=kwargs) 72 else: 73 return BinaryExpression(expr, 74 obj, 75 op, 76 type_=result_type, 77 negate=negate, modifiers=kwargs) 78 79 80def _binary_operate(expr, op, obj, reverse=False, result_type=None, 81 **kw): 82 obj = _check_literal(expr, op, obj) 83 84 if reverse: 85 left, right = obj, expr 86 else: 87 left, right = expr, obj 88 89 if result_type is None: 90 op, result_type = left.comparator._adapt_expression( 91 op, right.comparator) 92 93 return BinaryExpression( 94 left, right, op, type_=result_type, modifiers=kw) 95 96 97def _conjunction_operate(expr, op, other, **kw): 98 if op is operators.and_: 99 return and_(expr, other) 100 elif op is operators.or_: 101 return or_(expr, other) 102 else: 103 raise NotImplementedError() 104 105 106def _scalar(expr, op, fn, **kw): 107 return fn(expr) 108 109 110def _in_impl(expr, op, seq_or_selectable, negate_op, **kw): 111 seq_or_selectable = _clause_element_as_expr(seq_or_selectable) 112 113 if isinstance(seq_or_selectable, ScalarSelect): 114 return _boolean_compare(expr, op, seq_or_selectable, 115 negate=negate_op) 116 elif isinstance(seq_or_selectable, SelectBase): 117 118 # TODO: if we ever want to support (x, y, z) IN (select x, 119 # y, z from table), we would need a multi-column version of 120 # as_scalar() to produce a multi- column selectable that 121 # does not export itself as a FROM clause 122 123 return _boolean_compare( 124 expr, op, seq_or_selectable.as_scalar(), 125 negate=negate_op, **kw) 126 elif isinstance(seq_or_selectable, (Selectable, TextClause)): 127 return _boolean_compare(expr, op, seq_or_selectable, 128 negate=negate_op, **kw) 129 elif isinstance(seq_or_selectable, ClauseElement): 130 raise exc.InvalidRequestError( 131 'in_() accepts' 132 ' either a list of expressions ' 133 'or a selectable: %r' % seq_or_selectable) 134 135 # Handle non selectable arguments as sequences 136 args = [] 137 for o in seq_or_selectable: 138 if not _is_literal(o): 139 if not isinstance(o, operators.ColumnOperators): 140 raise exc.InvalidRequestError( 141 'in_() accepts' 142 ' either a list of expressions ' 143 'or a selectable: %r' % o) 144 elif o is None: 145 o = Null() 146 else: 147 o = expr._bind_param(op, o) 148 args.append(o) 149 if len(args) == 0: 150 151 # Special case handling for empty IN's, behave like 152 # comparison against zero row selectable. We use != to 153 # build the contradiction as it handles NULL values 154 # appropriately, i.e. "not (x IN ())" should not return NULL 155 # values for x. 156 157 util.warn('The IN-predicate on "%s" was invoked with an ' 158 'empty sequence. This results in a ' 159 'contradiction, which nonetheless can be ' 160 'expensive to evaluate. Consider alternative ' 161 'strategies for improved performance.' % expr) 162 if op is operators.in_op: 163 return expr != expr 164 else: 165 return expr == expr 166 167 return _boolean_compare(expr, op, 168 ClauseList(*args).self_group(against=op), 169 negate=negate_op) 170 171 172def _getitem_impl(expr, op, other, **kw): 173 if isinstance(expr.type, type_api.INDEXABLE): 174 other = _check_literal(expr, op, other) 175 return _binary_operate(expr, op, other, **kw) 176 else: 177 _unsupported_impl(expr, op, other, **kw) 178 179 180def _unsupported_impl(expr, op, *arg, **kw): 181 raise NotImplementedError("Operator '%s' is not supported on " 182 "this expression" % op.__name__) 183 184 185def _inv_impl(expr, op, **kw): 186 """See :meth:`.ColumnOperators.__inv__`.""" 187 if hasattr(expr, 'negation_clause'): 188 return expr.negation_clause 189 else: 190 return expr._negate() 191 192 193def _neg_impl(expr, op, **kw): 194 """See :meth:`.ColumnOperators.__neg__`.""" 195 return UnaryExpression(expr, operator=operators.neg, type_=expr.type) 196 197 198def _match_impl(expr, op, other, **kw): 199 """See :meth:`.ColumnOperators.match`.""" 200 201 return _boolean_compare( 202 expr, operators.match_op, 203 _check_literal( 204 expr, operators.match_op, other), 205 result_type=type_api.MATCHTYPE, 206 negate=operators.notmatch_op 207 if op is operators.match_op else operators.match_op, 208 **kw 209 ) 210 211 212def _distinct_impl(expr, op, **kw): 213 """See :meth:`.ColumnOperators.distinct`.""" 214 return UnaryExpression(expr, operator=operators.distinct_op, 215 type_=expr.type) 216 217 218def _between_impl(expr, op, cleft, cright, **kw): 219 """See :meth:`.ColumnOperators.between`.""" 220 return BinaryExpression( 221 expr, 222 ClauseList( 223 _check_literal(expr, operators.and_, cleft), 224 _check_literal(expr, operators.and_, cright), 225 operator=operators.and_, 226 group=False, group_contents=False), 227 op, 228 negate=operators.notbetween_op 229 if op is operators.between_op 230 else operators.between_op, 231 modifiers=kw) 232 233 234def _collate_impl(expr, op, other, **kw): 235 return collate(expr, other) 236 237# a mapping of operators with the method they use, along with 238# their negated operator for comparison operators 239operator_lookup = { 240 "and_": (_conjunction_operate,), 241 "or_": (_conjunction_operate,), 242 "inv": (_inv_impl,), 243 "add": (_binary_operate,), 244 "mul": (_binary_operate,), 245 "sub": (_binary_operate,), 246 "div": (_binary_operate,), 247 "mod": (_binary_operate,), 248 "truediv": (_binary_operate,), 249 "custom_op": (_binary_operate,), 250 "json_path_getitem_op": (_binary_operate, ), 251 "json_getitem_op": (_binary_operate, ), 252 "concat_op": (_binary_operate,), 253 "any_op": (_scalar, CollectionAggregate._create_any), 254 "all_op": (_scalar, CollectionAggregate._create_all), 255 "lt": (_boolean_compare, operators.ge), 256 "le": (_boolean_compare, operators.gt), 257 "ne": (_boolean_compare, operators.eq), 258 "gt": (_boolean_compare, operators.le), 259 "ge": (_boolean_compare, operators.lt), 260 "eq": (_boolean_compare, operators.ne), 261 "is_distinct_from": (_boolean_compare, operators.isnot_distinct_from), 262 "isnot_distinct_from": (_boolean_compare, operators.is_distinct_from), 263 "like_op": (_boolean_compare, operators.notlike_op), 264 "ilike_op": (_boolean_compare, operators.notilike_op), 265 "notlike_op": (_boolean_compare, operators.like_op), 266 "notilike_op": (_boolean_compare, operators.ilike_op), 267 "contains_op": (_boolean_compare, operators.notcontains_op), 268 "startswith_op": (_boolean_compare, operators.notstartswith_op), 269 "endswith_op": (_boolean_compare, operators.notendswith_op), 270 "desc_op": (_scalar, UnaryExpression._create_desc), 271 "asc_op": (_scalar, UnaryExpression._create_asc), 272 "nullsfirst_op": (_scalar, UnaryExpression._create_nullsfirst), 273 "nullslast_op": (_scalar, UnaryExpression._create_nullslast), 274 "in_op": (_in_impl, operators.notin_op), 275 "notin_op": (_in_impl, operators.in_op), 276 "is_": (_boolean_compare, operators.is_), 277 "isnot": (_boolean_compare, operators.isnot), 278 "collate": (_collate_impl,), 279 "match_op": (_match_impl,), 280 "notmatch_op": (_match_impl,), 281 "distinct_op": (_distinct_impl,), 282 "between_op": (_between_impl, ), 283 "notbetween_op": (_between_impl, ), 284 "neg": (_neg_impl,), 285 "getitem": (_getitem_impl,), 286 "lshift": (_unsupported_impl,), 287 "rshift": (_unsupported_impl,), 288 "contains": (_unsupported_impl,), 289} 290 291 292def _check_literal(expr, operator, other, bindparam_type=None): 293 if isinstance(other, (ColumnElement, TextClause)): 294 if isinstance(other, BindParameter) and \ 295 other.type._isnull: 296 other = other._clone() 297 other.type = expr.type 298 return other 299 elif hasattr(other, '__clause_element__'): 300 other = other.__clause_element__() 301 elif isinstance(other, type_api.TypeEngine.Comparator): 302 other = other.expr 303 304 if isinstance(other, (SelectBase, Alias)): 305 return other.as_scalar() 306 elif not isinstance(other, Visitable): 307 return expr._bind_param(operator, other, type_=bindparam_type) 308 else: 309 return other 310 311