def where(self, criterion: Criterion) -> "PostgreQueryBuilder":
with copy_if_immutable(self) as this:
if not this._on_conflict:
return super(PostgreQueryBuilder, this).where(criterion)
if isinstance(criterion, EmptyCriterion):
return this
if this._on_conflict_do_nothing:
raise QueryException('DO NOTHING doest not support WHERE')
if this._on_conflict_fields and this._on_conflict_do_updates:
if this._on_conflict_do_update_wheres:
this._on_conflict_do_update_wheres &= criterion
else:
this._on_conflict_do_update_wheres = criterion
elif this._on_conflict_fields:
if this._on_conflict_wheres:
this._on_conflict_wheres &= criterion
else:
this._on_conflict_wheres = criterion
else:
raise QueryException(
'Can not have fieldless ON CONFLICT WHERE')
return this
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 top(self, value: Union[str, int], percent: bool = False, with_ties: bool = False) -> "MSSQLQueryBuilder":
"""
Implements support for simple TOP clauses.
https://docs.microsoft.com/en-us/sql/t-sql/queries/top-transact-sql?view=sql-server-2017
"""
try:
self._top = int(value)
except ValueError:
raise QueryException("TOP value must be an integer")
if percent and not (0 <= int(value) <= 100):
raise QueryException("TOP value must be between 0 and 100 when `percent`" " is specified")
self._top_percent: bool = percent
self._top_with_ties: bool = with_ties
def on_duplicate_key_ignore(self) -> "MySQLQueryBuilder":
with copy_if_immutable(self) as this:
if this._duplicate_updates:
raise QueryException("Can not have two conflict handlers")
this._ignore_duplicates = True
return this
def do_update(self, update_field: Union[str, Field],
update_value: Any) -> "PostgreQueryBuilder":
with copy_if_immutable(self) as this:
if this._on_conflict_do_nothing:
raise QueryException("Can not have two conflict handlers")
if isinstance(update_field, str):
field = this._conflict_field_str(update_field)
elif isinstance(update_field, Field):
field = update_field
else:
raise QueryException("Unsupported update_field")
this._on_conflict_do_updates.append(
(field, ValueWrapper(update_value)))
return this
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 do_update(self, update_field, update_value):
if self._on_conflict_do_nothing:
raise QueryException('Can not have two conflict handlers')
#assert len(update_fields) == len(update_values), 'number of fields does noth match with number of values'
#for i, f in enumerate(update_fields):
#field = None
if isinstance(update_field, str):
field = self._conflict_field_str(update_field)
elif isinstance(update_field, Field):
field = update_field
self._on_conflict_updates.append((field, ValueWrapper(update_value)))
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 returning(self, *terms: Any) -> "PostgreSQLQueryBuilder":
for term in terms:
if isinstance(term, Field):
self._return_field(term)
elif isinstance(term, str):
self._return_field_str(term)
elif isinstance(term, (Function, ArithmeticExpression)):
if term.is_aggregate:
raise QueryException("Aggregate functions are not allowed in returning")
self._return_other(term)
else:
self._return_other(self.wrap_constant(term, self._wrapper_cls))
def top(self, value: Union[str, int]) -> "MSSQLQueryBuilder":
"""
Implements support for simple TOP clauses.
Does not include support for PERCENT or WITH TIES.
https://docs.microsoft.com/en-us/sql/t-sql/queries/top-transact-sql?view=sql-server-2017
"""
with copy_if_immutable(self) as this:
try:
this._top = int(value)
except ValueError:
raise QueryException("TOP value must be an integer")
return this
def returning(self, *terms: Any) -> "PostgreQueryBuilder":
with copy_if_immutable(self) as this:
for term in terms:
if isinstance(term, Field):
this._return_field(term)
elif isinstance(term, str):
this._return_field_str(term)
elif isinstance(term, ArithmeticExpression):
this._return_other(term)
elif isinstance(term, Function):
raise QueryException(
"Aggregate functions are not allowed in returning")
else:
this._return_other(
this.wrap_constant(term, this._wrapper_cls))
return this
def on_conflict(self,
*target_fields: Union[str, Term]) -> "PostgreQueryBuilder":
with copy_if_immutable(self) as this:
if not this._insert_table:
raise QueryException(
"On conflict only applies to insert query")
this._on_conflict = True
for target_field in target_fields:
if isinstance(target_field, str):
this._on_conflict_fields.append(
this._conflict_field_str(target_field))
elif isinstance(target_field, Term):
this._on_conflict_fields.append(target_field)
return this