def get_sql(self, with_alias=False, subquery=False, **kwargs):
union_template = ' UNION{type} {union}'
kwargs = {'quote_char': self.base_query.quote_char, 'dialect': self.base_query.dialect}
base_querystring = self.base_query.get_sql(subquery=self.base_query.wrap_union_queries, **kwargs)
querystring = base_querystring
for union_type, union_query in self._unions:
union_querystring = union_query.get_sql(subquery=self.base_query.wrap_union_queries, **kwargs)
if len(self.base_query._selects) != len(union_query._selects):
raise UnionException("Queries must have an equal number of select statements in a union."
"\n\nMain Query:\n{query1}\n\nUnion Query:\n{query2}"
.format(query1=base_querystring, query2=union_querystring))
querystring += union_template.format(type=union_type.value,
union=union_querystring)
if self._orderbys:
querystring += self._orderby_sql(**kwargs)
if self._limit:
querystring += self._limit_sql()
if self._offset:
querystring += self._offset_sql()
if subquery:
querystring = '({query})'.format(query=querystring)
if with_alias:
return alias_sql(querystring, self.alias or self._table_name, kwargs.get('quote_char'))
return querystring
def get_sql(self, quote_char=None, **kwargs):
# FIXME escape
table_sql = format_quotes(self._table_name, quote_char)
if self._schema is not None:
table_sql = '{schema}.{table}' \
.format(schema=self._schema.get_sql(quote_char=quote_char, **kwargs),
table=table_sql)
return alias_sql(table_sql, self.alias, quote_char)
def get_sql(self, with_alias=False, with_namespace=False, quote_char=None, **kwargs):
# FIXME escape
function_sql = self.get_function_sql(with_namespace=with_namespace, quote_char=quote_char)
if not with_alias or self.alias is None:
return function_sql
return alias_sql(function_sql, self.alias, quote_char)
def get_sql(self, with_alias=False, with_namespace=False, quote_char=None, **kwargs):
# FIXME escape
function_sql = self.get_function_sql(with_namespace=with_namespace, quote_char=quote_char)
if not with_alias or self.alias is None:
return function_sql
return alias_sql(function_sql, self.alias, quote_char)
def get_sql(self, with_alias=False, with_namespace=False, quote_char=None, **kwargs):
# FIXME escape
function_sql = self.get_function_sql(with_namespace=with_namespace, quote_char=quote_char)
if self.schema is not None:
function_sql = '{schema}.{function}' \
.format(schema=self.schema.get_sql(quote_char=quote_char, **kwargs),
function=function_sql)
if not with_alias or self.alias is None:
return function_sql
return alias_sql(function_sql, self.alias, quote_char)
def get_sql(self, with_alias=False, with_namespace=False, quote_char=None, **kwargs):
# FIXME escape
function_sql = self.get_function_sql(with_namespace=with_namespace, quote_char=quote_char)
if self.schema is not None:
function_sql = '{schema}.{function}' \
.format(schema=self.schema.get_sql(quote_char=quote_char, **kwargs),
function=function_sql)
if not with_alias or self.alias is None:
return function_sql
return alias_sql(function_sql, self.alias, quote_char)
def get_sql(self, quote_char=None, **kwargs):
# FIXME escape
if self._schema:
table_sql = "{quote}{schema}{quote}.{quote}{name}{quote}".format(
schema=self._schema,
name=self._table_name,
quote=quote_char or '')
else:
table_sql = "{quote}{name}{quote}".format(name=self._table_name,
quote=quote_char or '')
return alias_sql(table_sql, self.alias, quote_char)
def get_sql(self, with_alias=False, **kwargs):
is_mul = self.operator in self.mul_order
is_left_add, is_right_add = [getattr(side, 'operator', None) in self.add_order
for side in [self.left, self.right]]
quote_char = kwargs.get('quote_char', None)
arithmatic_sql = '{left}{operator}{right}'.format(
operator=self.operator.value,
left=("({})" if is_mul and is_left_add else "{}").format(self.left.get_sql(**kwargs)),
right=("({})" if is_mul and is_right_add else "{}").format(self.right.get_sql(**kwargs)),
)
if not with_alias or self.alias is None:
return arithmatic_sql
return alias_sql(arithmatic_sql, self.alias, quote_char)
def get_sql(self, with_alias=False, **kwargs):
is_mul = self.operator in self.mul_order
is_left_add, is_right_add = [getattr(side, 'operator', None) in self.add_order
for side in [self.left, self.right]]
quote_char = kwargs.get('quote_char', None)
arithmatic_sql = '{left}{operator}{right}'.format(
operator=self.operator.value,
left=("({})" if is_mul and is_left_add else "{}").format(self.left.get_sql(**kwargs)),
right=("({})" if is_mul and is_right_add else "{}").format(self.right.get_sql(**kwargs)),
)
if not with_alias or self.alias is None:
return arithmatic_sql
return alias_sql(arithmatic_sql, self.alias, quote_char)
def get_sql(self, with_alias=False, **kwargs):
if not self._cases:
raise CaseException(
"At least one 'when' case is required for a CASE statement.")
cases = " ".join('WHEN {when} THEN {then}'.format(
when=criterion.get_sql(**kwargs), then=term.get_sql(**kwargs))
for criterion, term in self._cases)
else_ = (' ELSE {}'.format(self._else.get_sql(
**kwargs)) if self._else else '')
case_sql = 'CASE {cases}{else_} END'.format(cases=cases, else_=else_)
if not with_alias or self.alias is None:
return case_sql
return alias_sql(case_sql, self.alias, kwargs.get('quote_char'))
def get_sql(self, with_alias=False, **kwargs):
if not self._cases:
raise CaseException("At least one 'when' case is required for a CASE statement.")
cases = " ".join('WHEN {when} THEN {then}'.format(
when=criterion.get_sql(**kwargs),
then=term.get_sql(**kwargs)
) for criterion, term in self._cases)
else_ = (' ELSE {}'.format(self._else.get_sql(**kwargs))
if self._else
else '')
case_sql = 'CASE {cases}{else_} END'.format(cases=cases, else_=else_)
if not with_alias or self.alias is None:
return case_sql
return alias_sql(case_sql, self.alias, kwargs.get('quote_char'))
def get_sql(self, with_alias=False, subquery=False, **kwargs):
union_template = ' UNION{type} {union}'
kwargs = {
'dialect': self.base_query.dialect,
# This initializes the quote char based on the base query, which could be a dialect specific query class
# This might be overridden if quote_char is set explicitly in kwargs
'quote_char': self.base_query.quote_char,
**kwargs,
}
base_querystring = self.base_query.get_sql(
subquery=self.base_query.wrap_union_queries, **kwargs)
querystring = base_querystring
for union_type, union_query in self._unions:
union_querystring = union_query.get_sql(
subquery=self.base_query.wrap_union_queries, **kwargs)
if len(self.base_query._selects) != len(union_query._selects):
raise UnionException(
"Queries must have an equal number of select statements in a union."
"\n\nMain Query:\n{query1}\n\nUnion Query:\n{query2}".
format(query1=base_querystring, query2=union_querystring))
querystring += union_template.format(type=union_type.value,
union=union_querystring)
if self._orderbys:
querystring += self._orderby_sql(**kwargs)
if self._limit:
querystring += self._limit_sql()
if self._offset:
querystring += self._offset_sql()
if subquery:
querystring = '({query})'.format(query=querystring, **kwargs)
if with_alias:
return alias_sql(querystring, self.alias or self._table_name,
kwargs.get('quote_char'))
return querystring
def get_sql(self, with_alias=False, with_namespace=False, quote_char=None, **kwargs):
# Need to add namespace if the table has an alias
if self.table and (with_namespace or self.table.alias):
field_sql = "{quote}{namespace}{quote}.{quote}{name}{quote}".format(
namespace=self.table.alias or self.table._table_name,
name=self.name,
quote=quote_char or '',
)
else:
field_sql = "{quote}{name}{quote}".format(
name=self.name,
quote=quote_char or '',
)
field_alias = getattr(self, 'alias', None)
if not with_alias or field_alias is None:
return field_sql
return alias_sql(field_sql, field_alias, quote_char)
def get_sql(self, quote_char=None, **kwargs):
# FIXME escape
if self._schema:
table_sql = "{quote}{schema}{quote}.{quote}{name}{quote}".format(
schema=self._schema,
name=self._table_name,
quote=quote_char or ''
)
else:
table_sql = "{quote}{name}{quote}".format(
name=self._table_name,
quote=quote_char or ''
)
if self.alias is None:
return table_sql
return alias_sql(table_sql, self.alias, quote_char)
def get_sql(self, with_alias=False, with_namespace=False, quote_char=None, **kwargs):
# Need to add namespace if the table has an alias
if self.table and (with_namespace or self.table.alias):
field_sql = "{quote}{namespace}{quote}.{quote}{name}{quote}".format(
namespace=self.table.alias or self.table._table_name,
name=self.name,
quote=quote_char or '',
)
else:
field_sql = "{quote}{name}{quote}".format(
name=self.name,
quote=quote_char or '',
)
field_alias = getattr(self, 'alias', None)
if not with_alias or field_alias is None:
return field_sql
return alias_sql(field_sql, field_alias, quote_char)
def get_sql(self, with_alias=False, subquery=False, **kwargs):
union_template = ' UNION{type} {union}'
kwargs = {
'quote_char': self.base_query.quote_char,
'dialect': self.base_query.dialect
}
base_querystring = self.base_query.get_sql(
subquery=self.base_query.wrap_union_queries, **kwargs)
querystring = base_querystring
for union_type, union_query in self._unions:
union_querystring = union_query.get_sql(
subquery=self.base_query.wrap_union_queries, **kwargs)
if len(self.base_query._selects) != len(union_query._selects):
raise UnionException(
"Queries must have an equal number of select statements in a union."
"\n\nMain Query:\n{query1}\n\nUnion Query:\n{query2}".
format(query1=base_querystring, query2=union_querystring))
querystring += union_template.format(type=union_type.value,
union=union_querystring)
if self._orderbys:
querystring += self._orderby_sql(**kwargs)
if self._limit:
querystring += self._limit_sql()
if self._offset:
querystring += self._offset_sql()
if subquery:
querystring = '({query})'.format(query=querystring)
if with_alias:
return alias_sql(querystring, self.alias or self._table_name,
kwargs.get('quote_char'))
return querystring
def get_sql(self,
with_alias=False,
with_namespace=False,
quote_char=None,
secondary_quote_char="'",
**kwargs):
field_sql = format_quotes(self.name, quote_char)
# Need to add namespace if the table has an alias
if self.table and (with_namespace or self.table.alias):
field_sql = "{namespace}.{name}" \
.format(
namespace=format_quotes(self.table.alias or self.table._table_name, quote_char),
name=field_sql,
)
field_alias = getattr(self, 'alias', None)
if not with_alias or field_alias is None:
return field_sql
return alias_sql(field_sql, field_alias, quote_char)
def get_sql(self, with_alias=False, subquery=False, **kwargs):
if not (self._selects or self._insert_table or self._delete_from or self._update_table):
return ''
if self._insert_table and not (self._selects or self._values):
return ''
if self._update_table and not self._updates:
return ''
has_joins = bool(self._joins)
has_multiple_from_clauses = 1 < len(self._from)
has_subquery_from_clause = 0 < len(self._from) and isinstance(self._from[0], QueryBuilder)
kwargs['with_namespace'] = any((has_joins, has_multiple_from_clauses, has_subquery_from_clause))
if self._update_table:
querystring = self._update_sql(**kwargs)
querystring += self._set_sql(**kwargs)
if self._wheres:
querystring += self._where_sql(**kwargs)
return querystring
elif self._delete_from:
querystring = self._delete_sql(**kwargs)
elif not self._select_into and self._insert_table:
querystring = self._insert_sql(**kwargs)
if self._columns:
querystring += self._columns_sql(**kwargs)
if self._values:
querystring += self._values_sql(**kwargs)
return querystring
else:
querystring += ' ' + self._select_sql(**kwargs)
else:
querystring = self._select_sql(**kwargs)
if self._insert_table:
querystring += self._into_sql(**kwargs)
if self._from:
querystring += self._from_sql(**kwargs)
if self._joins:
querystring += " " + " ".join(join.get_sql(**kwargs)
for join in self._joins)
if self._prewheres:
querystring += self._prewhere_sql(**kwargs)
if self._wheres:
querystring += self._where_sql(**kwargs)
if self._groupbys:
querystring += self._group_sql(**kwargs)
if self._mysql_rollup:
querystring += self._rollup_sql()
if self._havings:
querystring += self._having_sql(**kwargs)
if self._orderbys:
querystring += self._orderby_sql(**kwargs)
if self._limit:
querystring += self._limit_sql()
if self._offset:
querystring += self._offset_sql()
if subquery:
querystring = '({query})'.format(query=querystring)
if with_alias:
return alias_sql(querystring, self.alias or self._table_name, kwargs.get('quote_char'))
return querystring
def get_sql(self, quote_char=None, **kwargs):
kwargs['subcriterion'] = True
sql = "NOT {term}".format(term=self.term.get_sql(quote_char=quote_char,
**kwargs))
return alias_sql(sql, self.alias, quote_char=quote_char)
def get_sql(self, quote_char=None, secondary_quote_char="'", **kwargs):
sql = self.get_value_sql(quote_char=quote_char,
secondary_quote_char=secondary_quote_char,
**kwargs)
return alias_sql(sql, self.alias, quote_char)
def get_sql(self, **kwargs):
return alias_sql(
sql=super(Bracket, self).get_sql(**kwargs),
alias=self.alias,
quote_char=kwargs.get('quote_char', None),
)
def get_sql(self, quote_char=None, **kwargs):
sql = self._get_value_sql(quote_char=quote_char, **kwargs)
return alias_sql(sql, self.alias, quote_char)
def get_sql(self, **kwargs):
return alias_sql(
sql=super(Bracket, self).get_sql(**kwargs),
alias=self.alias,
quote_char=kwargs.get('quote_char', None),
)
def get_sql(self,
with_alias=False,
subquery=False,
with_unions=False,
**kwargs):
if not (self._selects or self._insert_table):
return ''
if self._insert_table and not (self._selects or self._values):
return ''
kwargs['with_namespace'] = bool(self._joins) or 1 < len(self._from)
if not self._select_into and self._insert_table:
querystring = self._insert_sql(**kwargs)
if self._columns:
querystring += self._columns_sql(**kwargs)
if self._values:
return querystring + self._values_sql(**kwargs)
else:
querystring += ' ' + self._select_sql(**kwargs)
else:
querystring = self._select_sql(**kwargs)
if self._insert_table:
querystring += self._into_sql(**kwargs)
if self._from:
querystring += self._from_sql(**kwargs)
if self._joins:
querystring += " " + " ".join(
join.get_sql(**kwargs) for join in self._joins)
if self._wheres:
querystring += self._where_sql(**kwargs)
if self._groupbys:
querystring += self._group_sql(**kwargs)
if self._mysql_rollup:
querystring += self._rollup_sql()
if self._havings:
querystring += self._having_sql(**kwargs)
if self._orderbys:
querystring += self._orderby_sql(**kwargs)
if self._offset:
querystring += self._offset_sql()
if self._limit:
querystring += self._limit_sql()
if subquery:
querystring = '({query})'.format(query=querystring)
if with_alias:
return alias_sql(querystring, self.alias or self.table_name,
kwargs.get('quote_char'))
if with_unions:
querystring = self._union_sql(querystring, **kwargs)
return querystring
def get_sql(self, quote_char=None, **kwargs):
sql = 'NULL'
if self.alias is None:
return sql
return alias_sql(sql, self.alias, quote_char)
def get_sql(self, quote_char=None, **kwargs):
sql = self.get_value_sql(quote_char=quote_char, **kwargs)
return alias_sql(sql, self.alias, quote_char)
def get_sql(self, quote_char=None, **kwargs):
kwargs['subcriterion'] = True
sql = "NOT {term}".format(
term=self.term.get_sql(quote_char=quote_char, **kwargs))
return alias_sql(sql, self.alias, quote_char=quote_char)
def get_sql(self, quote_char=None, **kwargs):
sql = self._get_value_sql(quote_char=quote_char, **kwargs)
if self.alias is None:
return sql
return alias_sql(sql, self.alias, quote_char)
def get_sql(self, with_alias=False, subquery=False, **kwargs):
if not (self._selects or self._insert_table or self._delete_from
or self._update_table):
return ''
if self._insert_table and not (self._selects or self._values):
return ''
if self._update_table and not self._updates:
return ''
has_joins = bool(self._joins)
has_multiple_from_clauses = 1 < len(self._from)
has_subquery_from_clause = 0 < len(self._from) and isinstance(
self._from[0], QueryBuilder)
kwargs['with_namespace'] = any(
(has_joins, has_multiple_from_clauses, has_subquery_from_clause))
if self._update_table:
querystring = self._update_sql(**kwargs)
querystring += self._set_sql(**kwargs)
if self._wheres:
querystring += self._where_sql(**kwargs)
return querystring
elif self._delete_from:
querystring = self._delete_sql(**kwargs)
elif not self._select_into and self._insert_table:
querystring = self._insert_sql(**kwargs)
if self._columns:
querystring += self._columns_sql(**kwargs)
if self._values:
querystring += self._values_sql(**kwargs)
return querystring
else:
querystring += ' ' + self._select_sql(**kwargs)
else:
querystring = self._select_sql(**kwargs)
if self._insert_table:
querystring += self._into_sql(**kwargs)
if self._from:
querystring += self._from_sql(**kwargs)
if self._joins:
querystring += " " + " ".join(
join.get_sql(**kwargs) for join in self._joins)
if self._prewheres:
querystring += self._prewhere_sql(**kwargs)
if self._wheres:
querystring += self._where_sql(**kwargs)
if self._groupbys:
querystring += self._group_sql(**kwargs)
if self._mysql_rollup:
querystring += self._rollup_sql()
if self._havings:
querystring += self._having_sql(**kwargs)
if self._orderbys:
querystring += self._orderby_sql(**kwargs)
if self._limit:
querystring += self._limit_sql()
if self._offset:
querystring += self._offset_sql()
if subquery:
querystring = '({query})'.format(query=querystring)
if with_alias:
return alias_sql(querystring, self.alias or self._table_name,
kwargs.get('quote_char'))
return querystring
def get_sql(self, quote_char=None, **kwargs):
sql = 'NULL'
if self.alias is None:
return sql
return alias_sql(sql, self.alias, quote_char)