def _build_criterion_expression(
self, operator_: str, left: Term,
right: Term) -> Union[BasicCriterion, Not, NullCriterion]:
if operator_ == "equals":
return left.eq(right)
elif operator_ == "not-equals":
return left.ne(right)
elif operator_ == 'empty':
return left.isnull()
elif operator_ == 'not-empty':
return left.notnull()
elif operator_ == "more":
return left.gt(right)
elif operator_ == "more-equals":
return left.gte(right)
elif operator_ == "less":
return left.lt(right)
elif operator_ == "less-equals":
return left.lte(right)
elif operator_ == 'in':
return left.isin(right)
elif operator_ == 'not-in':
return left.notin(right)
else:
# TODO more operator support
raise NotImplementedError("filter operator is not supported")
def constant_handler(self) -> Tuple[CustomFunction, ParameterValueType]:
param = self.param
if param.value.strip().startswith("{&"):
result, value_type = self.match_function()
return result, value_type
else:
return Term.wrap_constant(param.value), ParameterValueType.TEXT
def _validate_returning_term(self, term: Term) -> None:
for field in term.fields_():
if not any(
[self._insert_table, self._update_table, self._delete_from]):
raise QueryException("Returning can't be used in this query")
if (field.table not in {self._insert_table, self._update_table}
and term not in self._from):
raise QueryException("You can't return from other tables")
def _validate_returning_term(self, term: Term) -> None:
for field in term.fields_():
if not any([self._insert_table, self._update_table, self._delete_from]):
raise QueryException("Returning can't be used in this query")
table_is_insert_or_update_table = field.table in {self._insert_table, self._update_table}
join_tables = set(itertools.chain.from_iterable([j.criterion.tables_ for j in self._joins]))
join_and_base_tables = set(self._from) | join_tables
table_not_base_or_join = bool(term.tables_ - join_and_base_tables)
if not table_is_insert_or_update_table and table_not_base_or_join:
raise QueryException("You can't return from other tables")
def resolver_arithmetic_expression(
cls,
model: "Type[Model]",
arithmetic_expression_or_field: Term,
) -> Tuple[Term, Optional[PypikaField]]:
field_object = None
if isinstance(arithmetic_expression_or_field, PypikaField):
name = arithmetic_expression_or_field.name
try:
arithmetic_expression_or_field.name = model._meta.fields_db_projection[
name]
field_object = model._meta.fields_map.get(name, None)
if field_object:
func = field_object.get_for_dialect(
model._meta.db.capabilities.dialect, "function_cast")
if func:
arithmetic_expression_or_field = func(
field_object, arithmetic_expression_or_field)
except KeyError:
raise FieldError(
f"There is no non-virtual field {name} on Model {model.__name__}"
)
elif isinstance(arithmetic_expression_or_field, ArithmeticExpression):
left = arithmetic_expression_or_field.left
right = arithmetic_expression_or_field.right
(
arithmetic_expression_or_field.left,
left_field_object,
) = cls.resolver_arithmetic_expression(model, left)
if left_field_object:
if field_object and type(field_object) != type(
left_field_object):
raise FieldError(
"Cannot use arithmetic expression between different field type"
)
field_object = left_field_object
(
arithmetic_expression_or_field.right,
right_field_object,
) = cls.resolver_arithmetic_expression(model, right)
if right_field_object:
if field_object and type(field_object) != type(
right_field_object):
raise FieldError(
"Cannot use arithmetic expression between different field type"
)
field_object = right_field_object
return arithmetic_expression_or_field, field_object
def resolve(self, model: "Type[Model]", table: Table) -> tuple:
q_objects = self._resolve_q_objects()
modifier = QueryModifier()
for node in q_objects:
modifier &= node.resolve(model, model._meta.basetable)
if isinstance(self.then, Function):
then = self.then.resolve(model, table)["field"]
elif isinstance(self.then, Term):
then = F.resolver_arithmetic_expression(model, self.then)[0]
else:
then = Term.wrap_constant(self.then)
return modifier.where_criterion, then
def _resolve_group_bys(self, *field_names: str):
group_bys = []
for field_name in field_names:
if field_name in self._annotations:
group_bys.append(Term(field_name))
continue
field_split = field_name.split("__")
related_table, related_db_field = self._join_table_with_forwarded_fields(
model=self.model,
table=self.model._meta.basetable,
field=field_split[0],
forwarded_fields="__".join(field_split[1:])
if len(field_split) > 1 else "",
)
field = related_table[related_db_field].as_(field_name)
group_bys.append(field)
return group_bys
def not_in(field: Term, value: Any) -> Criterion:
if value:
return field.notin(value) | field.isnull()
return BasicCriterion(Equality.eq, ValueWrapper(1), ValueWrapper(1))
def is_in(field: Term, value: Any) -> Criterion:
if value:
return field.isin(value)
return BasicCriterion(Equality.eq, ValueWrapper(1), ValueWrapper(0))
def not_in(field: Term, value: Any) -> Criterion:
return field.notin(value) | field.isnull()
def not_null(field: Term, value: Any) -> Criterion:
if value:
return field.notnull()
return field.isnull()
def is_in(field: Term, value: Any) -> Criterion:
if value:
return field.isin(value)
# SQL has no False, so we return 1=0
return BasicCriterion(Equality.eq, ValueWrapper(1), ValueWrapper(0))
UNION ALL
SELECT
0 AS permissions,
allow,
deny
FROM page_permissions
WHERE entity = ANY ($2) OR page_id = $1)
SELECT bit_or(permissions) | bit_or(allow) | (coalesce((SELECT * FROM everyone_perms), $3)) & ~bit_or(deny)
FROM all_permissions
"""
"""pypika attempt:"""
q = (Query.with_(
Query.select(rp.permissions).from_(rp).where(rp.entity == entity_id),
'everyone_perms').with_(
Query.select(rp.permissions,
Term(0).as_('allow'),
Term(0).as_('deny')).from_(rp).where(
rp.entity.isin(role_ids)).union_all(
Query.select(
Term(0).as_('permissions'), pp.allow,
pp.deny).from_(pp).where(
pp.entity.isin(role_ids)
| pp.page_id == entity_id)),
'all_permissions').select(
bit_or('permissions') | bit_or('allow') | bit_or('deny')
| Coalesce(
Query.select(pypika.Table('everyone_perms').star).from_(
pypika.Table('everyone_perms')), default_permissions)
& ~bit_or('deny')).from_('all_permissions'))
print(q)
Case expression.
:param args: When objects
:param default: value for 'CASE WHEN ... THEN ... ELSE <default> END'
"""
def __init__(
self,
*args: When,
default: Union[str, F, ArithmeticExpression,
Function] = None) -> None:
self.args = args
self.default = default
def resolve(self, model: "Type[Model]", table: Table) -> dict:
case = PypikaCase()
for arg in self.args:
if not isinstance(arg, When):
raise TypeError("expected When objects as args")
criterion, term = arg.resolve(model, table)
case = case.when(criterion, term)
if isinstance(self.default, Function):
case = case.else_(self.default.resolve(model, table)["field"])
elif isinstance(self.default, Term):
case = case.else_(
F.resolver_arithmetic_expression(model, self.default)[0])
else:
case = case.else_(Term.wrap_constant(self.default))
return {"joins": [], "field": case}
def between_and(field: Term, value: Tuple[Any, Any]) -> Criterion:
return field.between(value[0], value[1])
def ends_with(field: Term, value: str) -> Criterion:
return Like(Cast(field, SqlTypes.VARCHAR),
field.wrap_constant(f"%{escape_like(value)}"))
def not_equal(field: Term, value: Any) -> Criterion:
return field.ne(value) | field.isnull()
def insensitive_ends_with(field: Term, value: str) -> Criterion:
return Like(Upper(Cast(field, SqlTypes.VARCHAR)),
field.wrap_constant(Upper(f"%{escape_like(value)}")))
def _definition_field_for_data_blending(
target_dataset_definition: Union[Field, terms.Term],
target_dataset_leaf_definition: terms.Field,
definition: terms.Term,
) -> terms.Term:
"""
When using data blending, the dataset table of the set filter needs to be re-mapped to the table in the
target dataset (i.e. primary or secondary). The easiest way to do that is to select the field in the
target dataset directly. Otherwise table not found issues would pop up when resolving the joins.
:param target_dataset_definition: The definition for a field in the target dataset.
:param target_dataset_leaf_definition: The leaf definition for a field in the target dataset.
Given sometimes a fireant's Field might have nested fireant's
Fields.
:param definition: A definition that might have its sub-parts (e.g. term, left, right) replaced.
That's likely the case for Criterion sub-classes and so on.
:return: A term sub-class to be used in place of the provided definition argument, when applicable.
"""
if isinstance(definition, (terms.ValueWrapper, )):
# Constant values can be returned as is.
return definition
if isinstance(
definition,
(terms.Field, )) and definition == target_dataset_leaf_definition:
target_dataset_leaf_definition.replace_table(
target_dataset_leaf_definition.table, definition.table)
return target_dataset_definition
# Function, ...
if hasattr(definition, 'args'):
definition.args = [
_definition_field_for_data_blending(
target_dataset_definition, target_dataset_leaf_definition, arg)
for arg in definition.args
]
# CustomFunction, ...
if hasattr(definition, 'params'):
definition.params = [
_definition_field_for_data_blending(
target_dataset_definition, target_dataset_leaf_definition,
param) for param in definition.params
]
# RangeCriterion, ContainsCriterion, Not, All,
if hasattr(definition, 'term'):
definition.term = _definition_field_for_data_blending(
target_dataset_definition, target_dataset_leaf_definition,
definition.term)
# BasicCriterion, ComplexCriterion, ...
if hasattr(definition, 'left'):
definition.left = _definition_field_for_data_blending(
target_dataset_definition, target_dataset_leaf_definition,
definition.left)
if hasattr(definition, 'right'):
definition.right = _definition_field_for_data_blending(
target_dataset_definition, target_dataset_leaf_definition,
definition.right)
# Case
if hasattr(definition, '_cases'):
definition._cases = [
_definition_field_for_data_blending(
target_dataset_definition, target_dataset_leaf_definition,
case) for case in definition._cases
]
if hasattr(definition, '_else'):
definition._else = _definition_field_for_data_blending(
target_dataset_definition, target_dataset_leaf_definition,
definition._else)
return definition
def is_in(field: Term, value: Any) -> Criterion:
return field.isin(value)
