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 sqlf(self, compact):
select_clause = [TB('SELECT')]
if self.select_as_type:
select_clause.append(TB(' AS ' + self.select_as_type))
if self.select_type:
select_clause.append(TB(' ' + self.select_type))
lines = []
lines.append(LB(select_clause))
lines.append(TB(' '))
lines.append(LB(with_commas(True, self.fields)))
if self.from_tables:
lines.append(TB(' FROM '))
lines.append(self.from_tables.sqlf(True))
if self.where_expr:
lines.append(TB(' WHERE '))
lines.append(self.where_expr.sqlf(True))
if self.groups:
lines.append(TB(' GROUP BY '))
lines.append(LB(with_commas(True, self.groups)))
if self.having_expr:
lines.append(TB(' HAVING '))
lines.append(self.having_expr.sqlf(True))
if self.qualify_expr:
lines.append(TB(' QUALIFY '))
lines.append(self.qualify_expr.sqlf(True))
compact_sql = LB(lines)
if compact:
return compact_sql
stack = comments_sqlf(self.comments)
stack.append(LB(select_clause))
stack.append(IB(SB(with_commas(compact, self.fields))))
if self.from_tables:
stack.append(TB('FROM'))
stack.append(IB(self.from_tables.sqlf(compact)))
if self.where_expr:
stack.append(TB('WHERE'))
stack.append(IB(self.where_expr.sqlf(compact)))
if self.groups:
stack.append(TB('GROUP BY'))
stack.append(IB(WB(with_commas(compact, self.groups))))
if self.having_expr:
stack.append(TB('HAVING'))
stack.append(IB(self.having_expr.sqlf(compact)))
if self.qualify_expr:
stack.append(TB('QUALIFY'))
stack.append(IB(self.qualify_expr.sqlf(compact)))
return CB([compact_sql, SB(stack)])
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):
stack = []
if self.funcs:
for func in self.funcs:
stack.append(func.sqlf(compact))
stack.append(self.sql.sqlf(compact))
return SB(stack)
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, 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):
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):
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):
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])
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(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):
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):
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):
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):
lines = [TB(self.name), TB(' '), self.col_type.sqlf(True)]
if not self.nullable:
lines.append(TB(' NOT NULL'))
if self.opts:
lines.append(TB(' '))
lines.append(self.opts.sqlf(True))
if compact:
return LB(lines)
# Indented block for non-compact
iblock = [self.col_type.sqlf(False)]
if not self.nullable:
iblock.append(TB('NOT NULL'))
if self.opts:
iblock.append(self.opts.sqlf(False))
return CB([LB(lines), SB([
TB(self.name),
IB(SB(iblock)),
])])
def sqlf(self, compact):
# TODO(scannell) Add compact version
if not self.fields:
return SB([
LB([
TB('INSERT INTO '),
self.table.sqlf(compact=True),
TB(' ('),
]),
IB(self.sql.sqlf(compact)),
TB(');'),
])
return SB([
LB([
TB('INSERT INTO '),
self.table.sqlf(compact=True),
TB(' ('),
]),
IB(SB(with_commas(compact, self.fields, tail=')'))),
self.sql.sqlf(compact),
TB(';'),
])
def sqlf(self, compact):
# TODO(scannell) Wrap in braces if it's not a SQLSelect type
compact_sql = LB([self.query.sqlf(True), self.ordering.sqlf(True)])
if compact:
return compact_sql
return CB([
compact_sql,
SB([
self.query.sqlf(compact),
self.ordering.sqlf(compact),
])
])
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):
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):
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):
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):
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):
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):
lines = [TB(self.clause), TB(' ')]
lines.append(self.table.sqlf(True))
if self.columns:
lines.append(TB(' ('))
lines.extend(with_commas(True, self.columns))
lines.append(TB(')'))
if self.options:
lines.append(TB(' '))
lines.append(self.options.sqlf(True))
if self.query:
lines.append(TB(' AS '))
lines.append(self.query.sqlf(True))
if compact:
return LB(lines)
stack = [LB([TB(self.clause), TB(' '), self.table.sqlf(True)])]
if self.columns:
stack.extend([
TB('('),
IB(WB(with_commas(False, self.columns), )),
TB(')'),
])
if self.options:
stack.append(self.options.sqlf(False))
if self.query:
stack.extend([
TB('AS'),
self.query.sqlf(False),
])
return CB([
LB(lines),
SB(stack),
])
def sqlf(self, compact):
stack = [TB('('), IB(self.query.sqlf(compact)), TB(')')]
if self.alias:
stack.append(LB([TB(' '), self.alias.sqlf(True)]))
return SB(stack)
def sqlf(self, compact):
return SB([cmd.sqlf(compact) for cmd in self.commands])
def sqlf(self, compact):
return SB(
[self.left.sqlf(compact),
TB(self.op),
self.right.sqlf(compact)])
def sqlf(self, compact):
big_block = []
small_block = []
for if_then in zip(self.args[0::2], self.args[1::2]):
if_expr = if_then[0].sqlf(False)
if_expr_compact = if_then[0].sqlf(True)
then_expr = if_then[1].sqlf(False)
then_expr_compact = if_then[1].sqlf(True)
# Most compact form - one line
small_block.append(
LB([
TB(' WHEN '), if_expr_compact, TB(' THEN '),
then_expr_compact
]))
# Choice
big_block.append(
CB([
SB([
TB('WHEN'),
IB(if_expr),
TB('THEN'),
IB(then_expr)
]),
SB([
LB([TB('WHEN '), if_expr_compact, TB(' THEN')]),
IB(then_expr)
])
])
)
if self.else_expr:
else_expr_compact = LB([TB(' ELSE '), self.else_expr.sqlf(True)])
small_block.append(else_expr_compact)
else_expr_norm = self.else_expr.sqlf(False)
big_block.append(
CB([
SB([TB('ELSE '), IB(else_expr_norm)]),
else_expr_compact
])
)
case_block = TB('CASE ')
case_block_compact = TB('CASE')
if self.base_expr:
case_block_compact = LB([
TB('CASE '), self.base_expr.sqlf(True)
])
case_block = LB([
TB('CASE '), self.base_expr.sqlf(False), TB(' ')
])
compact_sql = LB([case_block_compact] + small_block +
[TB(' END')])
if compact:
return compact_sql
return CB([
compact_sql,
SB([case_block, IB(SB(big_block)), TB('END')])
])