codekingpro/portable-devtools
114k
1from __future__ import annotations2 3import inspect4import re5import sys6import uuid7from collections.abc import Callable, Iterable, Iterator, Sequence8from datetime import date, datetime, time9from enum import Enum10from typing import TYPE_CHECKING, Any, TypeVar, overload11 12from pypika.enums import Arithmetic, Boolean, Comparator, Dialects, Equality, JSONOperators, Matching, Order13from pypika.utils import (14 CaseException,15 FunctionException,16 builder,17 format_alias_sql,18 format_quotes,19 ignore_copy,20 resolve_is_aggregate,21)22 23if TYPE_CHECKING:24 if sys.version_info < (3, 11):25 from typing_extensions import Self26 else:27 from typing import Self28 from pypika.queries import QueryBuilder, Selectable, Table29 30 31__author__ = "Timothy Heys"32__email__ = "theys@kayak.com"33 34 35NodeT = TypeVar("NodeT", bound="Node")36 37 38class Node:39 is_aggregate = None40 41 def nodes_(self) -> Iterator[NodeT]:42 yield self43 44 def find_(self, type: type[NodeT]) -> list[NodeT]:45 return [node for node in self.nodes_() if isinstance(node, type)]46 47 48class Term(Node):49 is_aggregate = False50 51 def __init__(self, alias: str | None = None) -> None:52 self.alias = alias53 54 @builder55 def as_(self, alias: str) -> None:56 self.alias = alias57 58 @property59 def tables_(self) -> set[Table]:60 from pypika import Table61 62 return set(self.find_(Table))63 64 def fields_(self) -> set[Field]:65 return set(self.find_(Field))66 67 @staticmethod68 def wrap_constant(69 val, wrapper_cls: type[Term] | None = None70 ) -> ValueError | NodeT | LiteralValue | Array | Tuple | ValueWrapper:71 """72 Used for wrapping raw inputs such as numbers in Criterions and Operator.73 74 For example, the expression F('abc')+1 stores the integer part in a ValueWrapper object.75 76 :param val:77 Any value.78 :param wrapper_cls:79 A pypika class which wraps a constant value so it can be handled as a component of the query.80 :return:81 Raw string, number, or decimal values will be returned in a ValueWrapper. Fields and other parts of the82 querybuilder will be returned as inputted.83 84 """85 86 if isinstance(val, Node):87 return val88 if val is None:89 return NullValue()90 if isinstance(val, list):91 return Array(*val)92 if isinstance(val, tuple):93 return Tuple(*val)94 95 # Need to default here to avoid the recursion. ValueWrapper extends this class.96 wrapper_cls = wrapper_cls or ValueWrapper97 return wrapper_cls(val)98 99 @staticmethod100 def wrap_json(101 val: Term | QueryBuilder | None | str | int | bool, wrapper_cls=None102 ) -> Term | QueryBuilder | NullValue | ValueWrapper | JSON:103 from .queries import QueryBuilder104 105 if isinstance(val, (Term, QueryBuilder)):106 return val107 if val is None:108 return NullValue()109 if isinstance(val, (str, int, bool)):110 wrapper_cls = wrapper_cls or ValueWrapper111 return wrapper_cls(val)112 113 return JSON(val)114 115 def replace_table(self, current_table: Table | None, new_table: Table | None) -> Term:116 """117 Replaces all occurrences of the specified table with the new table. Useful when reusing fields across queries.118 The base implementation returns self because not all terms have a table property.119 120 :param current_table:121 The table to be replaced.122 :param new_table:123 The table to replace with.124 :return:125 Self.126 """127 return self128 129 def eq(self, other: Any) -> BasicCriterion:130 return self == other131 132 def isnull(self) -> NullCriterion:133 return NullCriterion(self)134 135 def notnull(self) -> Not:136 return self.isnull().negate()137 138 def isnotnull(self) -> NotNullCriterion:139 return NotNullCriterion(self)140 141 def bitwiseand(self, value: int) -> BitwiseAndCriterion:142 return BitwiseAndCriterion(self, self.wrap_constant(value))143 144 def bitwiseor(self, value: int) -> BitwiseOrCriterion:145 return BitwiseOrCriterion(self, self.wrap_constant(value))146 147 def gt(self, other: Any) -> BasicCriterion:148 return self > other149 150 def gte(self, other: Any) -> BasicCriterion:151 return self >= other152 153 def lt(self, other: Any) -> BasicCriterion:154 return self < other155 156 def lte(self, other: Any) -> BasicCriterion:157 return self <= other158 159 def ne(self, other: Any) -> BasicCriterion:160 return self != other161 162 def glob(self, expr: str) -> BasicCriterion:163 return BasicCriterion(Matching.glob, self, self.wrap_constant(expr))164 165 def like(self, expr: str) -> BasicCriterion:166 return BasicCriterion(Matching.like, self, self.wrap_constant(expr))167 168 def not_like(self, expr: str) -> BasicCriterion:169 return BasicCriterion(Matching.not_like, self, self.wrap_constant(expr))170 171 def ilike(self, expr: str) -> BasicCriterion:172 return BasicCriterion(Matching.ilike, self, self.wrap_constant(expr))173 174 def not_ilike(self, expr: str) -> BasicCriterion:175 return BasicCriterion(Matching.not_ilike, self, self.wrap_constant(expr))176 177 def rlike(self, expr: str) -> BasicCriterion:178 return BasicCriterion(Matching.rlike, self, self.wrap_constant(expr))179 180 def regex(self, pattern: str) -> BasicCriterion:181 return BasicCriterion(Matching.regex, self, self.wrap_constant(pattern))182 183 def regexp(self, pattern: str) -> BasicCriterion:184 return BasicCriterion(Matching.regexp, self, self.wrap_constant(pattern))185 186 def between(self, lower: Any, upper: Any) -> BetweenCriterion:187 return BetweenCriterion(self, self.wrap_constant(lower), self.wrap_constant(upper))188 189 def from_to(self, start: Any, end: Any) -> PeriodCriterion:190 return PeriodCriterion(self, self.wrap_constant(start), self.wrap_constant(end))191 192 def as_of(self, expr: str) -> BasicCriterion:193 return BasicCriterion(Matching.as_of, self, self.wrap_constant(expr))194 195 def all_(self) -> All:196 return All(self)197 198 def isin(self, arg: list | tuple | set | frozenset | Term) -> ContainsCriterion:199 if isinstance(arg, (list, tuple, set, frozenset)):200 return ContainsCriterion(self, Tuple(*[self.wrap_constant(value) for value in arg]))201 return ContainsCriterion(self, arg)202 203 def notin(self, arg: list | tuple | set | frozenset | Term) -> ContainsCriterion:204 return self.isin(arg).negate()205 206 def bin_regex(self, pattern: str) -> BasicCriterion:207 return BasicCriterion(Matching.bin_regex, self, self.wrap_constant(pattern))208 209 def negate(self) -> Not:210 return Not(self)211 212 def lshift(self, other: Any) -> ArithmeticExpression:213 return self << other214 215 def rshift(self, other: Any) -> ArithmeticExpression:216 return self >> other217 218 def __invert__(self) -> Not:219 return Not(self)220 221 def __pos__(self) -> Term:222 return self223 224 def __neg__(self) -> Negative:225 return Negative(self)226 227 def __add__(self, other: Any) -> ArithmeticExpression:228 return ArithmeticExpression(Arithmetic.add, self, self.wrap_constant(other))229 230 def __sub__(self, other: Any) -> ArithmeticExpression:231 return ArithmeticExpression(Arithmetic.sub, self, self.wrap_constant(other))232 233 def __mul__(self, other: Any) -> ArithmeticExpression:234 return ArithmeticExpression(Arithmetic.mul, self, self.wrap_constant(other))235 236 def __truediv__(self, other: Any) -> ArithmeticExpression:237 return ArithmeticExpression(Arithmetic.div, self, self.wrap_constant(other))238 239 def __pow__(self, other: Any) -> Pow:240 return Pow(self, other)241 242 def __mod__(self, other: Any) -> Mod:243 return Mod(self, other)244 245 def __radd__(self, other: Any) -> ArithmeticExpression:246 return ArithmeticExpression(Arithmetic.add, self.wrap_constant(other), self)247 248 def __rsub__(self, other: Any) -> ArithmeticExpression:249 return ArithmeticExpression(Arithmetic.sub, self.wrap_constant(other), self)250 251 def __rmul__(self, other: Any) -> ArithmeticExpression:252 return ArithmeticExpression(Arithmetic.mul, self.wrap_constant(other), self)253 254 def __rtruediv__(self, other: Any) -> ArithmeticExpression:255 return ArithmeticExpression(Arithmetic.div, self.wrap_constant(other), self)256 257 def __lshift__(self, other: Any) -> ArithmeticExpression:258 return ArithmeticExpression(Arithmetic.lshift, self, self.wrap_constant(other))259 260 def __rshift__(self, other: Any) -> ArithmeticExpression:261 return ArithmeticExpression(Arithmetic.rshift, self, self.wrap_constant(other))262 263 def __rlshift__(self, other: Any) -> ArithmeticExpression:264 return ArithmeticExpression(Arithmetic.lshift, self.wrap_constant(other), self)265 266 def __rrshift__(self, other: Any) -> ArithmeticExpression:267 return ArithmeticExpression(Arithmetic.rshift, self.wrap_constant(other), self)268 269 def __eq__(self, other: Any) -> BasicCriterion:270 return BasicCriterion(Equality.eq, self, self.wrap_constant(other))271 272 def __ne__(self, other: Any) -> BasicCriterion:273 return BasicCriterion(Equality.ne, self, self.wrap_constant(other))274 275 def __gt__(self, other: Any) -> BasicCriterion:276 return BasicCriterion(Equality.gt, self, self.wrap_constant(other))277 278 def __ge__(self, other: Any) -> BasicCriterion:279 return BasicCriterion(Equality.gte, self, self.wrap_constant(other))280 281 def __lt__(self, other: Any) -> BasicCriterion:282 return BasicCriterion(Equality.lt, self, self.wrap_constant(other))283 284 def __le__(self, other: Any) -> BasicCriterion:285 return BasicCriterion(Equality.lte, self, self.wrap_constant(other))286 287 def __getitem__(self, item: slice) -> BetweenCriterion:288 if not isinstance(item, slice):289 raise TypeError("Field' object is not subscriptable")290 return self.between(item.start, item.stop)291 292 def __str__(self) -> str:293 return self.get_sql(quote_char='"', secondary_quote_char="'")294 295 def __hash__(self) -> int:296 return hash(self.get_sql(with_alias=True, with_namespace=True))297 298 def get_sql(self, **kwargs: Any) -> str:299 raise NotImplementedError()300 301 302def idx_placeholder_gen(idx: int) -> str:303 return str(idx + 1)304 305 306def named_placeholder_gen(idx: int) -> str:307 return f'param{idx + 1}'308 309 310class Parameter(Term):311 is_aggregate = None312 313 def __init__(self, placeholder: str | int) -> None:314 super().__init__()315 self._placeholder = placeholder316 317 @property318 def placeholder(self):319 return self._placeholder320 321 def get_sql(self, **kwargs: Any) -> str:322 return str(self.placeholder)323 324 def update_parameters(self, param_key: Any, param_value: Any, **kwargs):325 pass326 327 def get_param_key(self, placeholder: Any, **kwargs):328 return placeholder329 330 331class ListParameter(Parameter):332 def __init__(self, placeholder: str | int | Callable[[int], str] = idx_placeholder_gen) -> None:333 super().__init__(placeholder=placeholder)334 self._parameters = list()335 336 @property337 def placeholder(self) -> str:338 if callable(self._placeholder):339 return self._placeholder(len(self._parameters))340 341 return str(self._placeholder)342 343 def get_parameters(self, **kwargs):344 return self._parameters345 346 def update_parameters(self, value: Any, **kwargs):347 self._parameters.append(value)348 349 350class DictParameter(Parameter):351 def __init__(self, placeholder: str | int | Callable[[int], str] = named_placeholder_gen) -> None:352 super().__init__(placeholder=placeholder)353 self._parameters = dict()354 355 @property356 def placeholder(self) -> str:357 if callable(self._placeholder):358 return self._placeholder(len(self._parameters))359 360 return str(self._placeholder)361 362 def get_parameters(self, **kwargs):363 return self._parameters364 365 def get_param_key(self, placeholder: Any, **kwargs):366 return placeholder[1:]367 368 def update_parameters(self, param_key: Any, value: Any, **kwargs):369 self._parameters[param_key] = value370 371 372class QmarkParameter(ListParameter):373 def get_sql(self, **kwargs):374 return '?'375 376 377class NumericParameter(ListParameter):378 """Numeric, positional style, e.g. ...WHERE name=:1"""379 380 def get_sql(self, **kwargs: Any) -> str:381 return ":{placeholder}".format(placeholder=self.placeholder)382 383 384class FormatParameter(ListParameter):385 """ANSI C printf format codes, e.g. ...WHERE name=%s"""386 387 def get_sql(self, **kwargs: Any) -> str:388 return "%s"389 390 391class NamedParameter(DictParameter):392 """Named style, e.g. ...WHERE name=:name"""393 394 def get_sql(self, **kwargs: Any) -> str:395 return ":{placeholder}".format(placeholder=self.placeholder)396 397 398class PyformatParameter(DictParameter):399 """Python extended format codes, e.g. ...WHERE name=%(name)s"""400 401 def get_sql(self, **kwargs: Any) -> str:402 return "%({placeholder})s".format(placeholder=self.placeholder)403 404 def get_param_key(self, placeholder: Any, **kwargs):405 return placeholder[2:-2]406 407 408class Negative(Term):409 def __init__(self, term: Term) -> None:410 super().__init__()411 self.term = term412 413 @property414 def is_aggregate(self) -> bool | None:415 return self.term.is_aggregate416 417 def get_sql(self, **kwargs: Any) -> str:418 return "-{term}".format(term=self.term.get_sql(**kwargs))419 420 421class ValueWrapper(Term):422 is_aggregate = None423 424 def __init__(self, value: Any, alias: str | None = None) -> None:425 super().__init__(alias)426 self.value = value427 428 def get_value_sql(self, **kwargs: Any) -> str:429 return self.get_formatted_value(self.value, **kwargs)430 431 @classmethod432 def get_formatted_value(cls, value: Any, **kwargs):433 quote_char = kwargs.get("secondary_quote_char") or ""434 435 if isinstance(value, Term):436 return value.get_sql(**kwargs)437 if isinstance(value, Enum):438 return cls.get_formatted_value(value.value, **kwargs)439 if isinstance(value, (date, datetime, time)):440 return cls.get_formatted_value(value.isoformat(), **kwargs)441 if isinstance(value, str):442 return format_quotes(value, quote_char)443 if isinstance(value, bool):444 return str.lower(str(value))445 if isinstance(value, uuid.UUID):446 return cls.get_formatted_value(str(value), **kwargs)447 if value is None:448 return "null"449 return str(value)450 451 def _get_param_data(self, parameter: Parameter, **kwargs) -> tuple[str, str]:452 param_sql = parameter.get_sql(**kwargs)453 param_key = parameter.get_param_key(placeholder=param_sql)454 455 return param_sql, param_key456 457 def get_sql(458 self,459 quote_char: str | None = None,460 secondary_quote_char: str = "'",461 parameter: Parameter = None,462 **kwargs: Any,463 ) -> str:464 if parameter is None:465 sql = self.get_value_sql(quote_char=quote_char, secondary_quote_char=secondary_quote_char, **kwargs)466 return format_alias_sql(sql, self.alias, quote_char=quote_char, **kwargs)467 468 # Don't stringify numbers when using a parameter469 if isinstance(self.value, (int, float)):470 value_sql = self.value471 else:472 value_sql = self.get_value_sql(quote_char=quote_char, **kwargs)473 param_sql, param_key = self._get_param_data(parameter, **kwargs)474 parameter.update_parameters(param_key=param_key, value=value_sql, **kwargs)475 476 return format_alias_sql(param_sql, self.alias, quote_char=quote_char, **kwargs)477 478 479class ParameterValueWrapper(ValueWrapper):480 def __init__(self, parameter: Parameter, value: Any, alias: str | None = None) -> None:481 super().__init__(value, alias)482 self._parameter = parameter483 484 def _get_param_data(self, parameter: Parameter, **kwargs) -> tuple[str, str]:485 param_sql = self._parameter.get_sql(**kwargs)486 param_key = self._parameter.get_param_key(placeholder=param_sql)487 488 return param_sql, param_key489 490 491class JSON(Term):492 table: str | Selectable | None = None493 494 def __init__(self, value: Any = None, alias: str | None = None) -> None:495 super().__init__(alias)496 self.value = value497 498 def _recursive_get_sql(self, value: Any, **kwargs: Any) -> str:499 if isinstance(value, dict):500 return self._get_dict_sql(value, **kwargs)501 if isinstance(value, list):502 return self._get_list_sql(value, **kwargs)503 if isinstance(value, str):504 return self._get_str_sql(value, **kwargs)505 return str(value)506 507 def _get_dict_sql(self, value: dict, **kwargs: Any) -> str:508 pairs = [509 "{key}:{value}".format(key=self._recursive_get_sql(k, **kwargs), value=self._recursive_get_sql(v, **kwargs))510 for k, v in value.items()511 ]512 return "".join(["{", ",".join(pairs), "}"])513 514 def _get_list_sql(self, value: list, **kwargs: Any) -> str:515 pairs = [self._recursive_get_sql(v, **kwargs) for v in value]516 return "".join(["[", ",".join(pairs), "]"])517 518 @staticmethod519 def _get_str_sql(value: str, quote_char: str = '"', **kwargs: Any) -> str:520 return format_quotes(value, quote_char)521 522 def get_sql(self, secondary_quote_char: str = "'", **kwargs: Any) -> str:523 sql = format_quotes(self._recursive_get_sql(self.value), secondary_quote_char)524 return format_alias_sql(sql, self.alias, **kwargs)525 526 def get_json_value(self, key_or_index: str | int) -> BasicCriterion:527 return BasicCriterion(JSONOperators.GET_JSON_VALUE, self, self.wrap_constant(key_or_index))528 529 def get_text_value(self, key_or_index: str | int) -> BasicCriterion:530 return BasicCriterion(JSONOperators.GET_TEXT_VALUE, self, self.wrap_constant(key_or_index))531 532 def get_path_json_value(self, path_json: str) -> BasicCriterion:533 return BasicCriterion(JSONOperators.GET_PATH_JSON_VALUE, self, self.wrap_json(path_json))534 535 def get_path_text_value(self, path_json: str) -> BasicCriterion:536 return BasicCriterion(JSONOperators.GET_PATH_TEXT_VALUE, self, self.wrap_json(path_json))537 538 def has_key(self, other: Any) -> BasicCriterion:539 return BasicCriterion(JSONOperators.HAS_KEY, self, self.wrap_json(other))540 541 def contains(self, other: Any) -> BasicCriterion:542 return BasicCriterion(JSONOperators.CONTAINS, self, self.wrap_json(other))543 544 def contained_by(self, other: Any) -> BasicCriterion:545 return BasicCriterion(JSONOperators.CONTAINED_BY, self, self.wrap_json(other))546 547 def has_keys(self, other: Iterable) -> BasicCriterion:548 return BasicCriterion(JSONOperators.HAS_KEYS, self, Array(*other))549 550 def has_any_keys(self, other: Iterable) -> BasicCriterion:551 return BasicCriterion(JSONOperators.HAS_ANY_KEYS, self, Array(*other))552 553 554class Values(Term):555 def __init__(self, field: str | Field) -> None:556 super().__init__(None)557 self.field = Field(field) if not isinstance(field, Field) else field558 559 def get_sql(self, quote_char: str | None = None, **kwargs: Any) -> str:560 return "VALUES({value})".format(value=self.field.get_sql(quote_char=quote_char, **kwargs))561 562 563class LiteralValue(Term):564 def __init__(self, value, alias: str | None = None) -> None:565 super().__init__(alias)566 self._value = value567 568 def get_sql(self, **kwargs: Any) -> str:569 return format_alias_sql(self._value, self.alias, **kwargs)570 571 572class NullValue(LiteralValue):573 def __init__(self, alias: str | None = None) -> None:574 super().__init__("null", alias)575 576 577class SystemTimeValue(LiteralValue):578 def __init__(self, alias: str | None = None) -> None:579 super().__init__("SYSTEM_TIME", alias)580 581 582class Criterion(Term):583 @overload584 def _compare(self, comparator: Comparator, other: EmptyCriterion) -> Self: ...585 586 @overload587 def _compare(self, comparator: Comparator, other: Any) -> ComplexCriterion: ...588 589 def _compare(self, comparator: Comparator, other: Any) -> Self | ComplexCriterion:590 if isinstance(other, EmptyCriterion):591 return self592 return ComplexCriterion(comparator, self, other)593 594 def __and__(self, other: Any) -> Self | ComplexCriterion:595 return self._compare(Boolean.and_, other)596 597 def __or__(self, other: Any) -> Self | ComplexCriterion:598 return self._compare(Boolean.or_, other)599 600 def __xor__(self, other: Any) -> Self | ComplexCriterion:601 return self._compare(Boolean.xor_, other)602 603 @staticmethod604 def any(terms: Iterable[Term] = ()) -> EmptyCriterion | Term | ComplexCriterion:605 crit = EmptyCriterion()606 607 for term in terms:608 crit |= term609 610 return crit611 612 @staticmethod613 def all(terms: Iterable[Any] = ()) -> EmptyCriterion | Any | ComplexCriterion:614 crit = EmptyCriterion()615 616 for term in terms:617 crit &= term618 619 return crit620 621 def get_sql(self) -> str:622 raise NotImplementedError()623 624 625class EmptyCriterion(Criterion):626 is_aggregate = None627 tables_ = set()628 629 def fields_(self) -> set[Field]:630 return set()631 632 def __and__(self, other: Any) -> Any:633 return other634 635 def __or__(self, other: Any) -> Any:636 return other637 638 def __xor__(self, other: Any) -> Any:639 return other640 641 def __invert__(self) -> Any:642 return self643 644 645class Field(Criterion, JSON):646 def __init__(self, name: str, alias: str | None = None, table: str | Selectable | None = None) -> None:647 super().__init__(alias=alias)648 self.name = name649 if isinstance(table, str):650 # avoid circular import at load time651 from pypika.queries import Table652 653 table = Table(table)654 self.table: str | Selectable | None = table655 656 def nodes_(self) -> Iterator[NodeT]:657 yield self658 if self.table is not None:659 yield from self.table.nodes_()660 661 @builder662 def replace_table(self, current_table: Table | None, new_table: Table | None) -> None:663 """664 Replaces all occurrences of the specified table with the new table. Useful when reusing fields across queries.665 666 :param current_table:667 The table to be replaced.668 :param new_table:669 The table to replace with.670 :return:671 A copy of the field with the tables replaced.672 """673 self.table = new_table if self.table == current_table else self.table674 675 def get_sql(self, **kwargs: Any) -> str:676 with_alias = kwargs.pop("with_alias", False)677 with_namespace = kwargs.pop("with_namespace", False)678 quote_char = kwargs.pop("quote_char", None)679 680 field_sql = format_quotes(self.name, quote_char)681 682 # Need to add namespace if the table has an alias683 if self.table and (with_namespace or self.table.alias):684 table_name = self.table.get_table_name()685 field_sql = "{namespace}.{name}".format(686 namespace=format_quotes(table_name, quote_char),687 name=field_sql,688 )689 690 field_alias = getattr(self, "alias", None)691 if with_alias:692 return format_alias_sql(field_sql, field_alias, quote_char=quote_char, **kwargs)693 return field_sql694 695 696class Index(Term):697 def __init__(self, name: str, alias: str | None = None) -> None:698 super().__init__(alias)699 self.name = name700 701 def get_sql(self, quote_char: str | None = None, **kwargs: Any) -> str:702 return format_quotes(self.name, quote_char)703 704 705class Star(Field):706 def __init__(self, table: str | Selectable | None = None) -> None:707 super().__init__("*", table=table)708 709 def nodes_(self) -> Iterator[NodeT]:710 yield self711 if self.table is not None:712 yield from self.table.nodes_()713 714 def get_sql(715 self, with_alias: bool = False, with_namespace: bool = False, quote_char: str | None = None, **kwargs: Any716 ) -> str:717 if self.table and (with_namespace or self.table.alias):718 namespace = self.table.alias or getattr(self.table, "_table_name")719 return "{}.*".format(format_quotes(namespace, quote_char))720 721 return "*"722 723 724class Tuple(Criterion):725 def __init__(self, *values: Any) -> None:726 super().__init__()727 self.values = [self.wrap_constant(value) for value in values]728 729 def nodes_(self) -> Iterator[NodeT]:730 yield self731 for value in self.values:732 yield from value.nodes_()733 734 def get_sql(self, **kwargs: Any) -> str:735 sql = "({})".format(",".join(term.get_sql(**kwargs) for term in self.values))736 return format_alias_sql(sql, self.alias, **kwargs)737 738 @property739 def is_aggregate(self) -> bool:740 return resolve_is_aggregate([val.is_aggregate for val in self.values])741 742 @builder743 def replace_table(self, current_table: Table | None, new_table: Table | None) -> None:744 """745 Replaces all occurrences of the specified table with the new table. Useful when reusing fields across queries.746 747 :param current_table:748 The table to be replaced.749 :param new_table:750 The table to replace with.751 :return:752 A copy of the field with the tables replaced.753 """754 self.values = [value.replace_table(current_table, new_table) for value in self.values]755 756 757class Array(Tuple):758 def get_sql(self, **kwargs: Any) -> str:759 dialect = kwargs.get("dialect")760 values = ",".join(term.get_sql(**kwargs) for term in self.values)761 762 sql = "[{}]".format(values)763 if dialect in (Dialects.POSTGRESQL, Dialects.REDSHIFT):764 sql = "ARRAY[{}]".format(values) if len(values) > 0 else "'{}'"765 766 return format_alias_sql(sql, self.alias, **kwargs)767 768 769class Bracket(Tuple):770 def __init__(self, term: Any) -> None:771 super().__init__(term)772 773 774class NestedCriterion(Criterion):775 def __init__(776 self,777 comparator: Comparator,778 nested_comparator: ComplexCriterion,779 left: Any,780 right: Any,781 nested: Any,782 alias: str | None = None,783 ) -> None:784 super().__init__(alias)785 self.left = left786 self.comparator = comparator787 self.nested_comparator = nested_comparator788 self.right = right789 self.nested = nested790 791 def nodes_(self) -> Iterator[NodeT]:792 yield self793 yield from self.right.nodes_()794 yield from self.left.nodes_()795 yield from self.nested.nodes_()796 797 @property798 def is_aggregate(self) -> bool | None:799 return resolve_is_aggregate([term.is_aggregate for term in [self.left, self.right, self.nested]])800 801 @builder802 def replace_table(self, current_table: Table | None, new_table: Table | None) -> None:803 """804 Replaces all occurrences of the specified table with the new table. Useful when reusing fields across queries.805 806 :param current_table:807 The table to be replaced.808 :param new_table:809 The table to replace with.810 :return:811 A copy of the criterion with the tables replaced.812 """813 self.left = self.left.replace_table(current_table, new_table)814 self.right = self.right.replace_table(current_table, new_table)815 self.nested = self.right.replace_table(current_table, new_table)816 817 def get_sql(self, with_alias: bool = False, **kwargs: Any) -> str:818 sql = "{left}{comparator}{right}{nested_comparator}{nested}".format(819 left=self.left.get_sql(**kwargs),820 comparator=self.comparator.value,821 right=self.right.get_sql(**kwargs),822 nested_comparator=self.nested_comparator.value,823 nested=self.nested.get_sql(**kwargs),824 )825 826 if with_alias:827 return format_alias_sql(sql=sql, alias=self.alias, **kwargs)828 829 return sql830 831 832class BasicCriterion(Criterion):833 def __init__(self, comparator: Comparator, left: Term, right: Term, alias: str | None = None) -> None:834 """835 A wrapper for a basic criterion such as equality or inequality. This wraps three parts, a left and right term836 and a comparator which defines the type of comparison.837 838 839 :param comparator:840 Type: Comparator841 This defines the type of comparison, such as {quote}={quote} or {quote}>{quote}.842 :param left:843 The term on the left side of the expression.844 :param right:845 The term on the right side of the expression.846 """847 super().__init__(alias)848 self.comparator = comparator849 self.left = left850 self.right = right851 852 def nodes_(self) -> Iterator[NodeT]:853 yield self854 yield from self.right.nodes_()855 yield from self.left.nodes_()856 857 @property858 def is_aggregate(self) -> bool | None:859 return resolve_is_aggregate([term.is_aggregate for term in [self.left, self.right]])860 861 @builder862 def replace_table(self, current_table: Table | None, new_table: Table | None) -> None:863 """864 Replaces all occurrences of the specified table with the new table. Useful when reusing fields across queries.865 866 :param current_table:867 The table to be replaced.868 :param new_table:869 The table to replace with.870 :return:871 A copy of the criterion with the tables replaced.872 """873 self.left = self.left.replace_table(current_table, new_table)874 self.right = self.right.replace_table(current_table, new_table)875 876 def get_sql(self, quote_char: str = '"', with_alias: bool = False, **kwargs: Any) -> str:877 sql = "{left}{comparator}{right}".format(878 comparator=self.comparator.value,879 left=self.left.get_sql(quote_char=quote_char, **kwargs),880 right=self.right.get_sql(quote_char=quote_char, **kwargs),881 )882 if with_alias:883 return format_alias_sql(sql, self.alias, **kwargs)884 return sql885 886 887class ContainsCriterion(Criterion):888 def __init__(self, term: Any, container: Term, alias: str | None = None) -> None:889 """890 A wrapper for a "IN" criterion. This wraps two parts, a term and a container. The term is the part of the891 expression that is checked for membership in the container. The container can either be a list or a subquery.892 893 894 :param term:895 The term to assert membership for within the container.896 :param container:897 A list or subquery.898 """899 super().__init__(alias)900 self.term = term901 self.container = container902 self._is_negated = False903 904 def nodes_(self) -> Iterator[NodeT]:905 yield self906 yield from self.term.nodes_()907 yield from self.container.nodes_()908 909 @property910 def is_aggregate(self) -> bool | None:911 return self.term.is_aggregate912 913 @builder914 def replace_table(self, current_table: Table | None, new_table: Table | None) -> None:915 """916 Replaces all occurrences of the specified table with the new table. Useful when reusing fields across queries.917 918 :param current_table:919 The table to be replaced.920 :param new_table:921 The table to replace with.922 :return:923 A copy of the criterion with the tables replaced.924 """925 self.term = self.term.replace_table(current_table, new_table)926 927 def get_sql(self, subquery: Any = None, **kwargs: Any) -> str:928 sql = "{term} {not_}IN {container}".format(929 term=self.term.get_sql(**kwargs),930 container=self.container.get_sql(subquery=True, **kwargs),931 not_="NOT " if self._is_negated else "",932 )933 return format_alias_sql(sql, self.alias, **kwargs)934 935 @builder936 def negate(self) -> ContainsCriterion:937 self._is_negated = True938 939 940class ExistsCriterion(Criterion):941 def __init__(self, container, alias=None):942 super().__init__(alias)943 self.container = container944 self._is_negated = False945 946 def get_sql(self, **kwargs):947 return "{not_}EXISTS {container}".format(948 container=self.container.get_sql(**kwargs), not_='NOT ' if self._is_negated else ''949 )950 951 def negate(self):952 self._is_negated = True953 return self954 955 956class RangeCriterion(Criterion):957 def __init__(self, term: Term, start: Any, end: Any, alias: str | None = None) -> str:958 super().__init__(alias)959 self.term = term960 self.start = start961 self.end = end962 963 def nodes_(self) -> Iterator[NodeT]:964 yield self965 yield from self.term.nodes_()966 yield from self.start.nodes_()967 yield from self.end.nodes_()968 969 @property970 def is_aggregate(self) -> bool | None:971 return self.term.is_aggregate972 973 974class BetweenCriterion(RangeCriterion):975 @builder976 def replace_table(self, current_table: Table | None, new_table: Table | None) -> None:977 """978 Replaces all occurrences of the specified table with the new table. Useful when reusing fields across queries.979 980 :param current_table:981 The table to be replaced.982 :param new_table:983 The table to replace with.984 :return:985 A copy of the criterion with the tables replaced.986 """987 self.term = self.term.replace_table(current_table, new_table)988 989 def get_sql(self, **kwargs: Any) -> str:990 sql = "{term} BETWEEN {start} AND {end}".format(991 term=self.term.get_sql(**kwargs),992 start=self.start.get_sql(**kwargs),993 end=self.end.get_sql(**kwargs),994 )995 return format_alias_sql(sql, self.alias, **kwargs)996 997 998class PeriodCriterion(RangeCriterion):999 def get_sql(self, **kwargs: Any) -> str:1000 sql = "{term} FROM {start} TO {end}".format(1001 term=self.term.get_sql(**kwargs),1002 start=self.start.get_sql(**kwargs),1003 end=self.end.get_sql(**kwargs),1004 )1005 return format_alias_sql(sql, self.alias, **kwargs)1006 1007 1008class BitwiseAndCriterion(Criterion):1009 def __init__(self, term: Term, value: Any, alias: str | None = None) -> None:1010 super().__init__(alias)1011 self.term = term1012 self.value = value1013 1014 def nodes_(self) -> Iterator[NodeT]:1015 yield self1016 yield from self.term.nodes_()1017 yield from self.value.nodes_()1018 1019 @builder1020 def replace_table(self, current_table: Table | None, new_table: Table | None) -> None:1021 """1022 Replaces all occurrences of the specified table with the new table. Useful when reusing fields across queries.1023 1024 :param current_table:1025 The table to be replaced.1026 :param new_table:1027 The table to replace with.1028 :return:1029 A copy of the criterion with the tables replaced.1030 """1031 self.term = self.term.replace_table(current_table, new_table)1032 1033 def get_sql(self, **kwargs: Any) -> str:1034 sql = "({term} & {value})".format(1035 term=self.term.get_sql(**kwargs),1036 value=self.value,1037 )1038 return format_alias_sql(sql, self.alias, **kwargs)1039 1040 1041class BitwiseOrCriterion(Criterion):1042 def __init__(self, term: Term, value: Any, alias: str | None = None) -> None:1043 super().__init__(alias)1044 self.term = term1045 self.value = value1046 1047 def nodes_(self) -> Iterator[NodeT]:1048 yield self1049 yield from self.term.nodes_()1050 yield from self.value.nodes_()1051 1052 @builder1053 def replace_table(self, current_table: Table | None, new_table: Table | None) -> None:1054 """1055 Replaces all occurrences of the specified table with the new table. Useful when reusing fields across queries.1056 1057 :param current_table:1058 The table to be replaced.1059 :param new_table:1060 The table to replace with.1061 :return:1062 A copy of the criterion with the tables replaced.1063 """1064 self.term = self.term.replace_table(current_table, new_table)1065 1066 def get_sql(self, **kwargs: Any) -> str:1067 sql = "({term} | {value})".format(1068 term=self.term.get_sql(**kwargs),1069 value=self.value,1070 )1071 return format_alias_sql(sql, self.alias, **kwargs)1072 1073 1074class NullCriterion(Criterion):1075 def __init__(self, term: Term, alias: str | None = None) -> None:1076 super().__init__(alias)1077 self.term = term1078 1079 def nodes_(self) -> Iterator[NodeT]:1080 yield self1081 yield from self.term.nodes_()1082 1083 @builder1084 def replace_table(self, current_table: Table | None, new_table: Table | None) -> None:1085 """1086 Replaces all occurrences of the specified table with the new table. Useful when reusing fields across queries.1087 1088 :param current_table:1089 The table to be replaced.1090 :param new_table:1091 The table to replace with.1092 :return:1093 A copy of the criterion with the tables replaced.1094 """1095 self.term = self.term.replace_table(current_table, new_table)1096 1097 def get_sql(self, with_alias: bool = False, **kwargs: Any) -> str:1098 sql = "{term} IS NULL".format(1099 term=self.term.get_sql(**kwargs),1100 )1101 return format_alias_sql(sql, self.alias, **kwargs)1102 1103 1104class NotNullCriterion(NullCriterion):1105 def get_sql(self, with_alias: bool = False, **kwargs: Any) -> str:1106 sql = "{term} IS NOT NULL".format(1107 term=self.term.get_sql(**kwargs),1108 )1109 return format_alias_sql(sql, self.alias, **kwargs)1110 1111 1112class ComplexCriterion(BasicCriterion):1113 def get_sql(self, subcriterion: bool = False, **kwargs: Any) -> str:1114 sql = "{left} {comparator} {right}".format(1115 comparator=self.comparator.value,1116 left=self.left.get_sql(subcriterion=self.needs_brackets(self.left), **kwargs),1117 right=self.right.get_sql(subcriterion=self.needs_brackets(self.right), **kwargs),1118 )1119 1120 if subcriterion:1121 return "({criterion})".format(criterion=sql)1122 1123 return sql1124 1125 def needs_brackets(self, term: Term) -> bool:1126 return isinstance(term, ComplexCriterion) and not term.comparator == self.comparator1127 1128 1129class ArithmeticExpression(Term):1130 """1131 Wrapper for an arithmetic function. Can be simple with two terms or complex with nested terms. Order of operations1132 are also preserved.1133 """1134 1135 add_order = [Arithmetic.add, Arithmetic.sub]1136 1137 def __init__(self, operator: Arithmetic, left: Any, right: Any, alias: str | None = None) -> None:1138 """1139 Wrapper for an arithmetic expression.1140 1141 :param operator:1142 Type: Arithmetic1143 An operator for the expression such as {quote}+{quote} or {quote}/{quote}1144 1145 :param left:1146 The term on the left side of the expression.1147 :param right:1148 The term on the right side of the expression.1149 :param alias:1150 (Optional) an alias for the term which can be used inside a select statement.1151 :return:1152 """1153 super().__init__(alias)1154 self.operator = operator1155 self.left = left1156 self.right = right1157 1158 def nodes_(self) -> Iterator[NodeT]:1159 yield self1160 yield from self.left.nodes_()1161 yield from self.right.nodes_()1162 1163 @property1164 def is_aggregate(self) -> bool | None:1165 # True if both left and right terms are True or None. None if both terms are None. Otherwise, False1166 return resolve_is_aggregate([self.left.is_aggregate, self.right.is_aggregate])1167 1168 @builder1169 def replace_table(self, current_table: Table | None, new_table: Table | None) -> None:1170 """1171 Replaces all occurrences of the specified table with the new table. Useful when reusing fields across queries.1172 1173 :param current_table:1174 The table to be replaced.1175 :param new_table:1176 The table to replace with.1177 :return:1178 A copy of the term with the tables replaced.1179 """1180 self.left = self.left.replace_table(current_table, new_table)1181 self.right = self.right.replace_table(current_table, new_table)1182 1183 def left_needs_parens(self, curr_op, left_op) -> bool:1184 """1185 Returns true if the expression on the left of the current operator needs to be enclosed in parentheses.1186 1187 :param current_op:1188 The current operator.1189 :param left_op:1190 The highest level operator of the left expression.1191 """1192 if left_op is None:1193 # If the left expression is a single item.1194 return False1195 if curr_op in self.add_order:1196 # If the current operator is '+' or '-'.1197 return False1198 # The current operator is '*' or '/'. If the left operator is '+' or '-', we need to add parentheses:1199 # e.g. (A + B) / ..., (A - B) / ...1200 # Otherwise, no parentheses are necessary: