1# This file is part of python-sql. The COPYRIGHT file at the top level of 2# this repository contains the full copyright notices and license terms. 3import warnings 4from array import array 5 6from sql import Expression, Select, CombiningQuery, Flavor, Null 7 8__all__ = ['And', 'Or', 'Not', 'Less', 'Greater', 'LessEqual', 'GreaterEqual', 9 'Equal', 'NotEqual', 'Between', 'NotBetween', 'IsDistinct', 10 'IsNotDistinct', 'Is', 'IsNot', 'Add', 'Sub', 'Mul', 'Div', 'FloorDiv', 11 'Mod', 'Pow', 'SquareRoot', 'CubeRoot', 'Factorial', 'Abs', 'BAnd', 'BOr', 12 'BXor', 'BNot', 'LShift', 'RShift', 'Concat', 'Like', 'NotLike', 'ILike', 13 'NotILike', 'In', 'NotIn', 'Exists', 'Any', 'Some', 'All'] 14 15 16class Operator(Expression): 17 __slots__ = () 18 19 @property 20 def table(self): 21 return '' 22 23 @property 24 def name(self): 25 return '' 26 27 @property 28 def _operands(self): 29 return () 30 31 @property 32 def params(self): 33 34 def convert(operands): 35 params = [] 36 for operand in operands: 37 if isinstance(operand, (Expression, Select, CombiningQuery)): 38 params.extend(operand.params) 39 elif isinstance(operand, (list, tuple)): 40 params.extend(convert(operand)) 41 elif isinstance(operand, array): 42 params.extend(operand) 43 else: 44 params.append(operand) 45 return params 46 return tuple(convert(self._operands)) 47 48 def _format(self, operand, param=None): 49 if param is None: 50 param = Flavor.get().param 51 if isinstance(operand, Expression): 52 return str(operand) 53 elif isinstance(operand, (Select, CombiningQuery)): 54 return '(%s)' % operand 55 elif isinstance(operand, (list, tuple)): 56 return '(' + ', '.join(self._format(o, param) 57 for o in operand) + ')' 58 elif isinstance(operand, array): 59 return '(' + ', '.join((param,) * len(operand)) + ')' 60 else: 61 return param 62 63 def __str__(self): 64 raise NotImplementedError 65 66 def __and__(self, other): 67 if isinstance(other, And): 68 return And([self] + other) 69 else: 70 return And((self, other)) 71 72 def __or__(self, other): 73 if isinstance(other, Or): 74 return Or([self] + other) 75 else: 76 return Or((self, other)) 77 78 79class UnaryOperator(Operator): 80 __slots__ = 'operand' 81 _operator = '' 82 83 def __init__(self, operand): 84 self.operand = operand 85 86 @property 87 def _operands(self): 88 return (self.operand,) 89 90 def __str__(self): 91 return '(%s %s)' % (self._operator, self._format(self.operand)) 92 93 94class BinaryOperator(Operator): 95 __slots__ = ('left', 'right') 96 _operator = '' 97 98 def __init__(self, left, right): 99 self.left = left 100 self.right = right 101 102 @property 103 def _operands(self): 104 return (self.left, self.right) 105 106 def __str__(self): 107 left, right = self._operands 108 return '(%s %s %s)' % (self._format(left), self._operator, 109 self._format(right)) 110 111 def __invert__(self): 112 return _INVERT[self.__class__](self.left, self.right) 113 114 115class NaryOperator(list, Operator): 116 __slots__ = () 117 _operator = '' 118 119 @property 120 def _operands(self): 121 return self 122 123 def __str__(self): 124 return '(' + (' %s ' % self._operator).join(map(str, self)) + ')' 125 126 127class And(NaryOperator): 128 __slots__ = () 129 _operator = 'AND' 130 131 132class Or(NaryOperator): 133 __slots__ = () 134 _operator = 'OR' 135 136 137class Not(UnaryOperator): 138 __slots__ = () 139 _operator = 'NOT' 140 141 142class Neg(UnaryOperator): 143 __slots__ = () 144 _operator = '-' 145 146 147class Pos(UnaryOperator): 148 __slots__ = () 149 _operator = '+' 150 151 152class Less(BinaryOperator): 153 __slots__ = () 154 _operator = '<' 155 156 157class Greater(BinaryOperator): 158 __slots__ = () 159 _operator = '>' 160 161 162class LessEqual(BinaryOperator): 163 __slots__ = () 164 _operator = '<=' 165 166 167class GreaterEqual(BinaryOperator): 168 __slots__ = () 169 _operator = '>=' 170 171 172class Equal(BinaryOperator): 173 __slots__ = () 174 _operator = '=' 175 176 @property 177 def _operands(self): 178 if self.left is Null: 179 return (self.right,) 180 elif self.right is Null: 181 return (self.left,) 182 return super(Equal, self)._operands 183 184 def __str__(self): 185 if self.left is Null: 186 return '(%s IS NULL)' % self.right 187 elif self.right is Null: 188 return '(%s IS NULL)' % self.left 189 return super(Equal, self).__str__() 190 191 192class NotEqual(Equal): 193 __slots__ = () 194 _operator = '!=' 195 196 def __str__(self): 197 if self.left is Null: 198 return '(%s IS NOT NULL)' % self.right 199 elif self.right is Null: 200 return '(%s IS NOT NULL)' % self.left 201 return super(Equal, self).__str__() 202 203 204class Between(Operator): 205 __slots__ = ('operand', 'left', 'right', 'symmetric') 206 _operator = 'BETWEEN' 207 208 def __init__(self, operand, left, right, symmetric=False): 209 self.operand = operand 210 self.left = left 211 self.right = right 212 self.symmetric = symmetric 213 214 @property 215 def _operands(self): 216 return (self.operand, self.left, self.right) 217 218 def __str__(self): 219 operator = self._operator 220 if self.symmetric: 221 operator += ' SYMMETRIC' 222 return '(%s %s %s AND %s)' % ( 223 self._format(self.operand), operator, 224 self._format(self.left), self._format(self.right)) 225 226 def __invert__(self): 227 return _INVERT[self.__class__]( 228 self.operand, self.left, self.right, self.symmetric) 229 230 231class NotBetween(Between): 232 __slots__ = () 233 _operator = 'NOT BETWEEN' 234 235 236class IsDistinct(BinaryOperator): 237 __slots__ = () 238 _operator = 'IS DISTINCT FROM' 239 240 241class IsNotDistinct(IsDistinct): 242 __slots__ = () 243 _operator = 'IS NOT DISTINCT FROM' 244 245 246class Is(BinaryOperator): 247 __slots__ = () 248 _operator = 'IS' 249 250 def __init__(self, left, right): 251 assert right in [None, True, False] 252 super(Is, self).__init__(left, right) 253 254 @property 255 def _operands(self): 256 return (self.left,) 257 258 def __str__(self): 259 if self.right is None: 260 return '(%s %s UNKNOWN)' % ( 261 self._format(self.left), self._operator) 262 elif self.right is True: 263 return '(%s %s TRUE)' % (self._format(self.left), self._operator) 264 elif self.right is False: 265 return '(%s %s FALSE)' % (self._format(self.left), self._operator) 266 267 268class IsNot(Is): 269 __slots__ = () 270 _operator = 'IS NOT' 271 272 273class Add(BinaryOperator): 274 __slots__ = () 275 _operator = '+' 276 277 278class Sub(BinaryOperator): 279 __slots__ = () 280 _operator = '-' 281 282 283class Mul(BinaryOperator): 284 __slots__ = () 285 _operator = '*' 286 287 288class Div(BinaryOperator): 289 __slots__ = () 290 _operator = '/' 291 292 293# For backward compatibility 294class FloorDiv(BinaryOperator): 295 __slots__ = () 296 _operator = '/' 297 298 def __init__(self, left, right): 299 warnings.warn('FloorDiv operator is deprecated, use Div function', 300 DeprecationWarning, stacklevel=2) 301 super(FloorDiv, self).__init__(left, right) 302 303 304class Mod(BinaryOperator): 305 __slots__ = () 306 307 @property 308 def _operator(self): 309 # '%' must be escaped with format paramstyle 310 if Flavor.get().paramstyle == 'format': 311 return '%%' 312 else: 313 return '%' 314 315 316class Pow(BinaryOperator): 317 __slots__ = () 318 _operator = '^' 319 320 321class SquareRoot(UnaryOperator): 322 __slots__ = () 323 _operator = '|/' 324 325 326class CubeRoot(UnaryOperator): 327 __slots__ = () 328 _operator = '||/' 329 330 331class Factorial(UnaryOperator): 332 __slots__ = () 333 _operator = '!!' 334 335 336class Abs(UnaryOperator): 337 __slots__ = () 338 _operator = '@' 339 340 341class BAnd(BinaryOperator): 342 __slots__ = () 343 _operator = '&' 344 345 346class BOr(BinaryOperator): 347 __slots__ = () 348 _operator = '|' 349 350 351class BXor(BinaryOperator): 352 __slots__ = () 353 _operator = '#' 354 355 356class BNot(UnaryOperator): 357 __slots__ = () 358 _operator = '~' 359 360 361class LShift(BinaryOperator): 362 __slots__ = () 363 _operator = '<<' 364 365 366class RShift(BinaryOperator): 367 __slots__ = () 368 _operator = '>>' 369 370 371class Concat(BinaryOperator): 372 __slots__ = () 373 _operator = '||' 374 375 376class Like(BinaryOperator): 377 __slots__ = () 378 _operator = 'LIKE' 379 380 381class NotLike(BinaryOperator): 382 __slots__ = () 383 _operator = 'NOT LIKE' 384 385 386class ILike(BinaryOperator): 387 __slots__ = () 388 389 @property 390 def _operator(self): 391 if Flavor.get().ilike: 392 return 'ILIKE' 393 else: 394 return 'LIKE' 395 396 @property 397 def _operands(self): 398 operands = super(ILike, self)._operands 399 if not Flavor.get().ilike: 400 from .functions import Upper 401 operands = tuple(Upper(o) for o in operands) 402 return operands 403 404 405class NotILike(ILike): 406 __slots__ = () 407 408 @property 409 def _operator(self): 410 if Flavor.get().ilike: 411 return 'NOT ILIKE' 412 else: 413 return 'NOT LIKE' 414 415# TODO SIMILAR 416 417 418class In(BinaryOperator): 419 __slots__ = () 420 _operator = 'IN' 421 422 423class NotIn(BinaryOperator): 424 __slots__ = () 425 _operator = 'NOT IN' 426 427 428class Exists(UnaryOperator): 429 __slots__ = () 430 _operator = 'EXISTS' 431 432 433class Any(UnaryOperator): 434 __slots__ = () 435 _operator = 'ANY' 436 437 438Some = Any 439 440 441class All(UnaryOperator): 442 __slots__ = () 443 _operator = 'ALL' 444 445 446_INVERT = { 447 Less: GreaterEqual, 448 Greater: LessEqual, 449 LessEqual: Greater, 450 GreaterEqual: Less, 451 Equal: NotEqual, 452 NotEqual: Equal, 453 Between: NotBetween, 454 NotBetween: Between, 455 IsDistinct: IsNotDistinct, 456 IsNotDistinct: IsDistinct, 457 Is: IsNot, 458 IsNot: Is, 459 Like: NotLike, 460 NotLike: Like, 461 ILike: NotILike, 462 NotILike: ILike, 463 In: NotIn, 464 NotIn: In, 465 } 466