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)