def groupby(self, *terms):
for term in terms:
if isinstance(term, str):
term = Field(term, table=self._from[0])
elif isinstance(term, int):
term = Field(str(term), table=self._from[0]).wrap_constant(term)
self._groupbys.append(term)
def on_field(self, *fields):
if not fields:
raise JoinException("Parameter 'fields' is required for a "
"{type} JOIN but was not supplied.".format(type=self.type_label))
criterion = None
for field in fields:
consituent = Field(field, table=self.query._from[0]) == Field(field, table=self.item)
criterion = consituent if criterion is None else criterion & consituent
self.query.do_join(JoinOn(self.item, self.how, criterion))
return self.query
def _return_field_str(self, term):
if term == "*":
self._set_returns_for_star()
self._returns.append(Star())
return
if self._insert_table:
self._return_field(Field(term, table=self._insert_table))
elif self._update_table:
self._return_field(Field(term, table=self._update_table))
elif self._delete_from:
self._return_field(Field(term, table=self._from[0]))
else:
raise QueryException("Returning can't be used in this query")
def on_duplicate_key_update(self, field: Union[Field, str],
value: Any) -> "MySQLQueryBuilder":
if self._ignore_duplicates:
raise QueryException("Can not have two conflict handlers")
field = Field(field) if not isinstance(field, Field) else field
self._duplicate_updates.append((field, ValueWrapper(value)))
def orderby(self, *fields, **kwargs):
for field in fields:
field = (Field(
field, table=self.base_query._from[0]) if isinstance(
field, str) else self.base_query.wrap_constant(field))
self._orderbys.append((field, kwargs.get("order")))
def process_date_diff_args(
self, args: str) -> Tuple[CustomFunction, CustomFunction]:
arg_list = args.split(",")
if len(arg_list) != 2:
raise ValueError("Date_diff have invalid args", arg_list)
result = []
for arg in arg_list:
if arg == "now":
date = current_date()
else:
date_fnc = CustomFunction("date", ["col1"])
if "." in arg:
items = arg.split(".")
topic_name = items[0].strip()
topic = get_topic_by_name(topic_name, None)
table = None
if self.topic_space_filter:
if self.topic_space_filter(self.param.topicId):
alias_ = self.topic_space_filter(
self.param.topicId)["alias"]
table = AliasedQuery(alias_)
if table is None:
table = build_table_by_topic_id(topic.topicId)
factor_name = items[1].strip()
date = date_fnc(Field(factor_name, None, table))
else:
date = date_fnc(arg)
result.append(date)
return tuple(result)
def distinct_on(self, *fields: Union[str,
Term]) -> "PostgreSQLQueryBuilder":
for field in fields:
if isinstance(field, str):
self._distinct_on.append(Field(field))
elif isinstance(field, Term):
self._distinct_on.append(field)
def test_tuple_is_aggregate(self):
with self.subTest(
"None if single argument returns None for is_aggregate"):
self.assertEqual(None, Tuple(0).is_aggregate)
self.assertEqual(None, Tuple(Coalesce("col")).is_aggregate)
with self.subTest(
"None if multiple arguments all return None for is_aggregate"):
self.assertEqual(None, Tuple(0, "a").is_aggregate)
self.assertEqual(
None,
Tuple(Coalesce("col"), NullIf("col2", 0)).is_aggregate)
with self.subTest(
"True if single argument returns True for is_aggregate"):
self.assertEqual(True, Tuple(Sum("col")).is_aggregate)
with self.subTest(
"True if multiple arguments return True for is_aggregate"):
self.assertEqual(True, Tuple(Sum("col"), Sum("col2")).is_aggregate)
with self.subTest(
"True when mix of arguments returning None and True for is_aggregate"
):
self.assertEqual(
True,
Tuple(Coalesce("col"), Coalesce("col2", 0),
Sum("col3")).is_aggregate)
with self.subTest(
"False when one of the arguments returns False for is_aggregate"
):
self.assertEqual(False,
Tuple(Field("col1"), Sum("col2")).is_aggregate)
def using(self, *fields):
if not fields:
raise JoinException("Parameter 'fields' is required when joining with "
"a using clause but was not supplied.".format(type=self.type_label))
self.query.do_join(JoinUsing(self.item, self.how, [Field(field) for field in fields]))
return self.query
def test_tuple_is_aggregate(self):
with self.subTest(
'None if single argument returns None for is_aggregate'):
self.assertEqual(None, Tuple(0).is_aggregate)
self.assertEqual(None, Tuple(Coalesce('col')).is_aggregate)
with self.subTest(
'None if multiple arguments all return None for is_aggregate'):
self.assertEqual(None, Tuple(0, 'a').is_aggregate)
self.assertEqual(
None,
Tuple(Coalesce('col'), NullIf('col2', 0)).is_aggregate)
with self.subTest(
'True if single argument returns True for is_aggregate'):
self.assertEqual(True, Tuple(Sum('col')).is_aggregate)
with self.subTest(
'True if multiple arguments return True for is_aggregate'):
self.assertEqual(True, Tuple(Sum('col'), Sum('col2')).is_aggregate)
with self.subTest(
'True when mix of arguments returning None and True for is_aggregate'
):
self.assertEqual(
True,
Tuple(Coalesce('col'), Coalesce('col2', 0),
Sum('col3')).is_aggregate)
with self.subTest(
'False when one of the arguments returns False for is_aggregate'
):
self.assertEqual(False,
Tuple(Field('col1'), Sum('col2')).is_aggregate)
def distinct_on(self, *fields: Union[str, Term]) -> "PostgreQueryBuilder":
with copy_if_immutable(self) as this:
for field in fields:
if isinstance(field, str):
this._distinct_on.append(Field(field))
elif isinstance(field, Term):
this._distinct_on.append(field)
return this
def columns(self, *terms):
if self._insert_table is None:
raise AttributeError("'Query' object has no attribute '%s'" % "insert")
for term in terms:
if isinstance(term, str):
term = Field(term, table=self._insert_table)
self._columns.append(term)
def on_duplicate_key_update(self, field: Union[Field, str],
value: Any) -> "MySQLQueryBuilder":
with copy_if_immutable(self) as this:
if this._ignore_duplicates:
raise QueryException("Can not have two conflict handlers")
field = Field(field) if not isinstance(field, Field) else field
this._duplicate_updates.append((field, ValueWrapper(value)))
return this
def test_with_column_recursive(self):
sub_query = (Query.from_(self.table_efg).select("fizz").union(
Query.from_("an_alias").select("fizz")))
test_query = (Query.with_(sub_query, "an_alias", Field("fizz")).from_(
AliasedQuery("an_alias")).select("*"))
self.assertEqual(
'WITH RECURSIVE an_alias("fizz") AS ((SELECT "fizz" FROM "efg") UNION (SELECT "fizz" FROM "an_alias")) SELECT * FROM an_alias',
str(test_query),
)
def _select_field_str(self, term):
if 0 == len(self._from):
raise QueryException('Cannot select {term}, no FROM table specified.'
.format(term=term))
if term == '*':
self._select_star = True
self._selects = [Star()]
return
self._select_field(Field(term, table=self._from[0]))
def topic_handler_in_sub_query(self) -> Tuple[Field, ParameterValueType]:
param = self.param
for column in self.dataset_columns:
if column.columnId == param.factorId:
parser_ = ConsoleParameterParser(column.parameter,
self.topic_space_filter, None,
[])
parse_result = parser_.parse_parameter()
table = AliasedQuery(self.dataset_query_alias)
field = Field(column.alias, None, table)
value_type = parse_result.value_type
return field, value_type
def topic_handler_in_dataset(
self) -> Tuple[Union[Field, CustomFunction], ParameterValueType]:
param = self.param
topic_id = param.topicId
factor_id = param.factorId
topic = get_topic_by_id(topic_id)
table = None
if self.topic_space_filter:
if self.topic_space_filter(param.topicId):
alias_ = self.topic_space_filter(param.topicId)["alias"]
table = AliasedQuery(alias_)
if table is None:
table = build_table_by_topic_id(topic_id)
factor = get_factor(factor_id, topic)
result = Field(factor.name, None, table)
value_type = factor.type
return result, value_type
def set(self, field, value):
field = Field(field) if not isinstance(field, Field) else field
self._updates.append((field, ValueWrapper(value)))
def on_duplicate_key_update(self, field, value):
field = Field(field) if not isinstance(field, Field) else field
self._duplicate_updates.append((field, ValueWrapper(value)))
def field(self, name):
return Field(name, table=self)
def on_duplicate_key_update(self, field, value):
if self._ignore_duplicates:
raise QueryException("Can not have two conflict handlers")
field = Field(field) if not isinstance(field, Field) else field
self._duplicate_updates.append((field, ValueWrapper(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 _conflict_field_str(self, term):
if self._insert_table:
return Field(term, table=self._insert_table)
def distinct_on(self, *fields):
for field in fields:
if isinstance(field, str):
self._distinct_on.append(Field(field))
elif isinstance(field, Term):
self._distinct_on.append(field)
def _conflict_field_str(self, term: str) -> Optional[Field]:
if self._insert_table:
return Field(term, table=self._insert_table)
