def sqlf(self, compact):
# Start the stack with comments
stack = comments_sqlf(self.comments)
# Get params as a list of sqlf
paramf = []
for param in self.params[:-1]:
paramf.append(LB([param.sqlf(compact), TB(',')]))
if self.params:
paramf.append(self.params[-1].sqlf(compact))
stack.append(
LB([
TB('CREATE TEMPORARY FUNCTION '),
self.name.sqlf(True),
TB('('),
WB(paramf, sep=' '),
TB(')')
]))
if self.retval:
stack.append(LB([TB('RETURNS '), self.retval.sqlf(compact)]))
if isinstance(self.impl, SQLString):
stack.append(TB('LANGUAGE js AS'))
stack.append(IB(LB([self.impl.sqlf(compact), TB(';')])))
else:
stack.append(TB('AS'))
stack.append(IB(LB([self.impl.sqlf(compact), TB(';')])))
stack.append(TB(''))
return SB(stack)
def sqlf(self, compact):
lines = []
if self.order:
lines.append(TB(' ORDER BY '))
lines.append(LB(with_commas(compact, self.order)))
if self.limit:
lines.append(TB(' LIMIT '))
lines.append(TB(str(self.limit)))
if self.offset:
lines.append(TB(' OFFSET '))
lines.append(TB(str(self.offset)))
compact_sql = LB(lines)
if compact:
return compact_sql
stack = []
if self.order:
stack.append(TB(' ORDER BY'))
stack.append(IB(WB(with_commas(compact, self.order))))
if self.limit:
stack.append(TB(' LIMIT'))
stack.append(IB(TB(str(self.limit))))
if self.offset:
stack.append(TB(' OFFSET'))
stack.append(IB(TB(str(self.offset))))
return CB([compact_sql, SB(stack)])
def sqlf(self, compact):
compact_sql = LB([TB('['), LB(with_commas(True, self.args)), TB(']')])
if compact:
return compact_sql
return CB([
compact_sql,
LB([TB('['),
WB(with_commas(compact, self.args, tail=']'))]),
])
def sqlf(self, compact):
if self.sql_op.startswith('IS'):
return LB(
[self.arg.sqlf(compact), TB(' '), TB(self.sql_op)])
if self.sql_op.isalpha():
return LB(
[TB(self.sql_op), TB(' '), self.arg.sqlf(compact)])
return LB([TB(self.sql_op), self.arg.sqlf(compact)])
def sqlf(self, compact):
delete = [TB('DELETE FROM '), self.table.sqlf(compact)]
return SB([
LB(delete),
LB([
TB('WHERE '),
self.where_expr.sqlf(compact),
]),
TB(';'),
])
def sqlf(self, compact):
if len(self.args) == 1:
return LB([
TB(self.name + '('), self.args[0].sqlf(compact),
TB(self.opt + ')')
])
return LB([
TB(self.name + '('), self.args[0].sqlf(compact),
TB(', '), self.args[1].sqlf(compact),
TB(self.opt + ')')
])
def sqlf(self, compact):
compact_sql = LB([TB(key), TB('='), TB(self.val.sqlf(True))])
if compact:
return compact_sql
return CB([
compact_sql,
SB([
LB([TB(key), TB(' =')]),
IB(self.val.sqlf(False)),
])
])
def sqlf(self, compact):
compact_sql = LB([TB('(')] +
with_commas(True, self.exprs, tail=')'))
if compact:
return compact_sql
return CB([
compact_sql,
SB([
TB('('),
WB(with_commas(False, self.exprs, tail=')')),
])
])
def sqlf(self, compact):
compact_sql = LB([TB('ARRAY('), self.query.sqlf(True), TB(')')])
if compact:
return compact_sql
return CB([
compact_sql,
SB([TB('ARRAY('),
IB(self.query.sqlf(compact)),
TB(')')])
])
def sqlf(self, compact):
sep = ','
if compact:
sep += ' '
fields = []
for field in self.vals[0:-1]:
fields.append(LB([field.sqlf(True), TB(sep)]))
fields.append(self.vals[-1].sqlf(True))
if compact:
return LB([TB('STRUCT<')] + fields + [TB('>')])
return LB([TB('STRUCT<'), WB(fields), TB('>')])
def sqlf(self, compact):
lines = [TB('ARRAY_AGG(')]
if self.is_distinct:
lines.append(TB('DISTINCT '))
lines.append(self.expr.sqlf(True))
if self.nulls:
lines.append(TB(self.nulls) + ' NULLS')
if self.order_limit_offset:
lines.append(self.order_limit_offset.sqlf(True))
if self.analytic:
lines.append(self.analytic.sqlf(True))
lines.append(TB(')'))
compact_sql = LB(lines)
if compact:
return compact_sql
stack = [TB('ARRAY_AGG(')]
indent = []
if self.is_distinct:
indent.append(LB([TB('DISTINCT '), self.expr.sqlf(compact)]))
else:
indent.append(self.expr.sqlf(compact))
if self.nulls:
indent.append(TB(self.nulls) + ' NULLS')
if self.order_limit_offset:
indent.append(self.order_limit_offset.sqlf(compact))
if self.analytic:
indent.append(self.analytic.sqlf(compact))
stack.append(IB(SB(indent)))
stack.append(TB(')'))
return CB([compact_sql, SB(stack)])
def with_commas(compact: bool, args, sep=',', tail=None):
vargs = [a for a in args if isinstance(a, SQLNode)]
if not vargs:
return []
with_commas = []
for a in vargs[:-1]:
with_commas.append(LB([a.sqlf(compact), TB(sep)]))
if tail:
with_commas.append(LB([vargs[-1].sqlf(compact), TB(tail)]))
else:
with_commas.append(vargs[-1].sqlf(compact))
return with_commas
def sqlf(self, compact):
sql_op = TB(' ' + self.sql_op + ' (')
compact_sql = LB([self.expr.sqlf(True), sql_op] +
[self.sql.sqlf(True), TB(')')])
if compact:
return compact_sql
return CB([
compact_sql,
SB([
LB([self.expr.sqlf(False), sql_op]),
IB(self.sql.sqlf(True)),
TB(')')
]),
])
def sqlf(self, compact):
if not self.tables:
return self.select.sqlf(compact)
stack = [TB('WITH')]
for i, table in enumerate(self.tables):
iblock = [LB([table.sqlf(True), TB(' AS (')])]
iblock.append(IB(self.sqls[i].sqlf(compact)))
if i == len(self.tables) - 1:
iblock.append(TB(')'))
else:
iblock.append(TB('),'))
stack.append(IB(SB(iblock)))
stack.append(self.select.sqlf(compact))
return SB(stack)
def sqlf(self):
lines = [TB('OPTIONS(')]
lines.extend(with_commas(True, self.options))
lines.append(TB(')'))
if compact:
return LB(lines)
stack = [TB('OPTIONS(')]
stack.extend(IB(WB(with_commas(False, self.options))))
stack.append(TB(')'))
return CB([
LB(lines),
SB(stack),
])
def sqlf(self, compact):
del compact # Unused
words = []
for i in range(len(self.names)):
words.append(self.names[i].sqlf(True))
if i < (len(self.names) - 1):
words.append(TB('.'))
return LB(words)
def sqlf(self, compact):
tname = [TB('UPDATE '), self.table_name.sqlf(compact)]
updatestmt = [
LB(tname),
TB('SET'),
]
for i in range(len(self.update_fields)):
field = self.update_fields[i]
expr = self.update_exprs[i]
if i == len(self.update_fields) - 1:
updatestmt.append(
LB([field.sqlf(False),
TB(' = '),
expr.sqlf(False)]))
else:
updatestmt.append(
LB([
field.sqlf(False),
TB(' = '),
expr.sqlf(False),
TB(',')
]))
if self.from_tables:
updatestmt.append(LB([TB(' FROM '), self.from_tables.sqlf(False)]))
if self.where_expr:
updatestmt.append(LB([TB(' WHERE '), self.where_expr.sqlf(False)]))
return SB(updatestmt)
def sqlf(self, compact):
name = LB([x.sqlf(True) for x in self.names])
compact_sql = LB([name, TB('(')] + with_commas(True, self.args) +
[TB(')')])
if compact:
return compact_sql
if not self.args:
return compact_sql
return CB([
compact_sql,
LB([
LB([name, TB('(')]),
WB(with_commas(False, self.args)),
TB(')')
])
])
def sqlf(self, compact):
in_vals = []
for arg in self.args[:-1]:
in_vals.append(LB([arg.sqlf(True), TB(',')]))
in_vals.append(self.args[-1].sqlf(True))
sql_op = TB(' ' + self.sql_op + ' (')
compact_sql = LB([self.iexpr.sqlf(True), sql_op] + in_vals +
[TB(')')])
if compact:
return compact_sql
# TODO(scannell): Bug with separator - incorrect usage
return CB([
compact_sql,
SB([
LB([self.iexpr.sqlf(False), sql_op]),
IB(WB(in_vals, sep=' ')),
TB(')')
]),
])
def sqlf(self, compact):
join_type = TB(self.join_type + ' JOIN ')
if self.join_expr is None:
return LB([join_type, self.table.sqlf(compact)])
compact_sql = LB([
join_type,
self.table.sqlf(True),
TB(' ON '),
self.join_expr.sqlf(True)
])
if compact:
return compact_sql
return CB([
compact_sql,
SB([
LB([join_type, self.table.sqlf(False),
TB(' ON')]),
IB(self.join_expr.sqlf(False))
])
])
def sqlf(self, compact):
return LB([
TB(self.name),
TB('('), self.args[0].sqlf(True),
TB(', '),
TB('INTERVAL'),
TB(' '), self.args[1].sqlf(True),
TB(' '), self.args[2].sqlf(True),
TB(')')
])
def sqlf(self, compact):
right_expr_compact = self.right.sqlf(True)
left_expr_compact = self.left.sqlf(True)
compact_sql = LB([
left_expr_compact, TB(' '), TB(self.sql_op),
TB(' '),
right_expr_compact
])
if compact:
return compact_sql
right_expr = self.right.sqlf(False)
left_expr = self.left.sqlf(False)
return CB([
compact_sql, SB([
LB([left_expr, TB(' '), TB(self.sql_op)]),
right_expr
])
])
def sqlf(self, compact):
fields = [self.base.sqlf(compact)]
for j in self.joins:
if compact:
fields.append(TB(' '))
fields.append(j.sqlf(compact))
if compact:
return LB(fields)
return SB(fields)
def sqlf(self, compact):
del compact # Unused
# Escape characters
out_string = self.value
for escape_char in SQLString.ESCAPE_CHARS:
out_string = out_string.replace(escape_char, "\\" + escape_char)
return TB('{}\'{}\''.format(
self.flag or '',
out_string,
))
def sqlf(self, compact):
lines = []
lines.append(TB('OVER ('))
if self.partition_by:
lines.append(TB('PARTITION BY '))
lines.extend(with_commas(True, self.partition_by, ' '))
if self.order_by:
lines.append(TB('ORDER BY '))
lines.extend(with_commas(True, self.order_by, ' '))
if self.range_desc:
lines.append(TB(self.range_desc))
lines.append(TB(')'))
if compact:
return LB(lines)
full_sql = [
TB('OVER ('),
]
if self.partition_by:
full_sql.append(
IB(
SB([
TB('PARTITION BY'),
IB(WB(with_commas(True, self.partition_by)))
])))
if self.order_by:
full_sql.append(
IB(
SB([
TB('ORDER BY'),
IB(WB(with_commas(True, self.order_by)))
])))
if self.range_desc:
full_sql.append(IB(TB(self.range_desc)))
full_sql.append(TB(')'))
r = CB([LB(lines), SB(full_sql)])
return r
def sqlf(self, compact):
compact_sqlf = LB([
self.function.sqlf(compact),
TB(' '),
self.analytic.sqlf(compact)
])
if compact:
return compact_sqlf
return CB([
compact_sqlf,
SB([self.function.sqlf(compact),
self.analytic.sqlf(compact)])
])
def sqlf(self, compact):
words = []
if self.names:
words.append(TB('.'.join(self.names) + '.*'))
else:
words.append(TB('*'))
if self.except_ids:
words.append(TB(' '))
words.append(TB('EXCEPT('))
except_list = [TB(x + ',') for x in self.except_ids[:-1]]
except_list.append(TB(self.except_ids[-1]))
words.append(WB(except_list))
words.append(TB(')'))
return LB(words)
def sqlf(self, compact):
return LB([
TB(self.name),
TB('('),
self.part.sqlf(compact),
TB(' '),
TB('FROM'),
TB(' '),
self.expr.sqlf(compact),
TB(')')
])
def sqlf(self, compact):
return LB([
TB(self.name),
TB('('),
self.expr.sqlf(compact),
TB(' '),
TB('AS'),
TB(' '),
self.type.sqlf(compact),
TB(')')
])
def sqlf(self, compact):
comments_sql = comments_sqlf(self.comments)
compact_sql = self.expr.sqlf(True)
if self.alias:
compact_sql = LB([compact_sql, TB(' '), self.alias.sqlf(True)])
if comments_sql:
compact_sql = SB(comments_sql + [compact_sql])
if compact:
return compact_sql
full_sql = self.expr.sqlf(False)
if self.alias:
full_sql = SB(comments_sql + [full_sql, IB(self.alias.sqlf(True))])
elif comments_sql:
full_sql = SB(comments_sql + [full_sql])
return CB([compact_sql, full_sql])
