def get_result(self):
# Got to unravel the join stack; the nesting order could be
# arbitrary, so we do a depth first search and push the join tokens
# and predicates onto a flat list, then format them
op = self.expr.op()
if isinstance(op, ops.Join):
self._walk_join_tree(op)
else:
self.join_tables.append(self._format_table(self.expr))
# TODO: Now actually format the things
buf = StringIO()
buf.write(self.join_tables[0])
for jtype, table, preds in zip(self.join_types, self.join_tables[1:],
self.join_predicates):
buf.write('\n')
buf.write(util.indent('{0} {1}'.format(jtype, table), self.indent))
if len(preds):
buf.write('\n')
fmt_preds = map(self._format_predicate, preds)
fmt_preds = util.indent('USING ' + ', '.join(fmt_preds),
self.indent * 2)
buf.write(fmt_preds)
return buf.getvalue()
def get_result(self):
# Got to unravel the join stack; the nesting order could be
# arbitrary, so we do a depth first search and push the join tokens
# and predicates onto a flat list, then format them
op = self.expr.op()
if isinstance(op, ops.Join):
self._walk_join_tree(op)
else:
self.join_tables.append(self._format_table(self.expr))
# TODO: Now actually format the things
buf = StringIO()
buf.write(self.join_tables[0])
for jtype, table, preds in zip(self.join_types, self.join_tables[1:],
self.join_predicates):
buf.write('\n')
buf.write(util.indent('{0} {1}'.format(jtype, table), self.indent))
if len(preds):
buf.write('\n')
fmt_preds = [self._translate(pred) for pred in preds]
conj = ' AND\n{0}'.format(' ' * 3)
fmt_preds = util.indent('ON ' + conj.join(fmt_preds),
self.indent * 2)
buf.write(fmt_preds)
return buf.getvalue()
def explain(self, expr, params=None):
"""Explain expression.
Query for and return the query plan associated with the indicated
expression or SQL query.
Returns
-------
plan : string
"""
if isinstance(expr, ir.Expr):
context = self.dialect.make_context(params=params)
query_ast = self._build_ast(expr, context)
if len(query_ast.queries) > 1:
raise Exception('Multi-query expression')
query = query_ast.queries[0].compile()
else:
query = expr
statement = 'EXPLAIN {0}'.format(query)
with self._execute(statement, results=True) as cur:
result = self._get_list(cur)
return 'Query:\n{0}\n\n{1}'.format(util.indent(query, 2),
'\n'.join(result))
def _repr(self):
buf = StringIO()
space = 2 + max(len(x) for x in self.names)
for name, tipo in zip(self.names, self.types):
buf.write('\n{0}{1}'.format(name.ljust(space), str(tipo)))
return "ibis.Schema {{{0}\n}}".format(util.indent(buf.getvalue(), 2))
def fmt_schema(schema: sch.Schema) -> str:
"""Format `schema`.
Parameters
----------
schema
Ibis schema to format
Returns
-------
str
Formatted schema
"""
names = schema.names
maxlen = max(map(len, names))
cols = [f"{name:<{maxlen}} {typ}" for name, typ in schema.items()]
depth = ibis.options.repr.table_columns
if depth is not None and depth < len(cols):
first_column_name = names[0]
raw = fmt_truncated(
cols,
depth=depth,
sep="\n",
ellipsis=util.VERTICAL_ELLIPSIS.center(len(first_column_name)),
)
else:
raw = "\n".join(cols)
return util.indent(raw, spaces=2)
def explain(self, expr, params=None):
"""
Query for and return the query plan associated with the indicated
expression or SQL query.
Returns
-------
plan : string
"""
if isinstance(expr, ir.Expr):
context = self.dialect.make_context(params=params)
query_ast = self._build_ast(expr, context)
if len(query_ast.queries) > 1:
raise Exception('Multi-query expression')
query = query_ast.queries[0].compile()
else:
query = expr
statement = 'EXPLAIN {0}'.format(query)
with self._execute(statement, results=True) as cur:
result = self._get_list(cur)
return 'Query:\n{0}\n\n{1}'.format(
util.indent(query, 2), '\n'.join(result)
)
def __repr__(self):
# Temporary
rows = [
'Sort key:', ' ascending: {0!s}'.format(self.ascending),
util.indent(_safe_repr(self.expr), 2)
]
return '\n'.join(rows)
def explain(self, expr, params=None):
"""Explain expression.
Query for and return the query plan associated with the indicated
expression or SQL query.
Returns
-------
plan : string
"""
if isinstance(expr, ir.Expr):
context = self.compiler.make_context(params=params)
query_ast = self.compiler.to_ast(expr, context)
if len(query_ast.queries) > 1:
raise Exception('Multi-query expression')
query = query_ast.queries[0].compile()
else:
query = expr
statement = f'EXPLAIN {query}'
cur = self.raw_sql(statement)
result = self._get_list(cur)
cur.release()
return '\n'.join(['Query:', util.indent(query, 2), '', *result])
def __repr__(self):
space = 2 + max(map(len, self.names), default=0)
return "ibis.Schema {{{}\n}}".format(
indent(
''.join(f'\n{name.ljust(space)}{str(type)}'
for name, type in zip(self.names, self.types)),
2,
))
def format_subqueries(self):
context = self.context
subqueries = self.subqueries
return ',\n'.join('{} AS (\n{}\n)'.format(
context.get_ref(expr),
util.indent(context.get_compiled_expr(expr), 2),
) for expr in subqueries)
def __repr__(self):
space = 2 + max(map(len, self.names), default=0)
return "ibis.Schema {{{}\n}}".format(
util.indent(
''.join('\n{}{}'.format(name.ljust(space), str(type))
for name, type in zip(self.names, self.types)),
2,
))
def get_result(self):
"""Get a formatted string for the expression.
Got to unravel the join stack; the nesting order could be
arbitrary, so we do a depth first search and push the join tokens
and predicates onto a flat list, then format them
Returns
-------
string
"""
op = self.expr.op()
if isinstance(op, ops.Join):
self._walk_join_tree(op)
else:
self.join_tables.append(self._format_table(self.expr))
buf = StringIO()
buf.write(self.join_tables[0])
for jtype, table, preds in zip(
self.join_types, self.join_tables[1:], self.join_predicates
):
buf.write('\n')
buf.write(util.indent('{} {}'.format(jtype, table), self.indent))
fmt_preds = []
npreds = len(preds)
for pred in preds:
new_pred = self._translate(pred)
if npreds > 1:
new_pred = '({})'.format(new_pred)
fmt_preds.append(new_pred)
if len(fmt_preds):
buf.write('\n')
conj = ' AND\n{}'.format(' ' * 3)
fmt_preds = util.indent(
'ON ' + conj.join(fmt_preds), self.indent * 2
)
buf.write(fmt_preds)
else:
buf.write(util.indent('ON TRUE', self.indent * 2))
return buf.getvalue()
def format_select_set(self):
# TODO:
context = self.context
formatted = []
for expr in self.select_set:
if isinstance(expr, ir.ValueExpr):
expr_str = self._translate(expr, named=True)
elif isinstance(expr, ir.TableExpr):
# A * selection, possibly prefixed
if context.need_aliases(expr):
alias = context.get_ref(expr)
expr_str = f'{alias}.*' if alias else '*'
else:
expr_str = '*'
formatted.append(expr_str)
buf = StringIO()
line_length = 0
max_length = 70
tokens = 0
for i, val in enumerate(formatted):
# always line-break for multi-line expressions
if val.count('\n'):
if i:
buf.write(',')
buf.write('\n')
indented = util.indent(val, self.indent)
buf.write(indented)
# set length of last line
line_length = len(indented.split('\n')[-1])
tokens = 1
elif (
tokens > 0
and line_length
and len(val) + line_length > max_length
):
# There is an expr, and adding this new one will make the line
# too long
buf.write(',\n ') if i else buf.write('\n')
buf.write(val)
line_length = len(val) + 7
tokens = 1
else:
if i:
buf.write(',')
buf.write(' ')
buf.write(val)
tokens += 1
line_length += len(val) + 2
if self.distinct:
select_key = 'SELECT DISTINCT'
else:
select_key = 'SELECT'
return f'{select_key}{buf.getvalue()}'
def format_select_set(self):
# TODO:
context = self.context
formatted = []
for expr in self.select_set:
if isinstance(expr, ir.ValueExpr):
expr_str = self._translate(expr, named=True)
elif isinstance(expr, ir.TableExpr):
# A * selection, possibly prefixed
if context.need_aliases():
alias = context.get_ref(expr)
# materialized join will not have an alias. see #491
expr_str = '{0}.*'.format(alias) if alias else '*'
else:
expr_str = '*'
formatted.append(expr_str)
buf = StringIO()
line_length = 0
max_length = 70
tokens = 0
for i, val in enumerate(formatted):
# always line-break for multi-line expressions
if val.count('\n'):
if i:
buf.write(',')
buf.write('\n')
indented = util.indent(val, self.indent)
buf.write(indented)
# set length of last line
line_length = len(indented.split('\n')[-1])
tokens = 1
elif (tokens > 0 and line_length and
len(val) + line_length > max_length):
# There is an expr, and adding this new one will make the line
# too long
buf.write(',\n ') if i else buf.write('\n')
buf.write(val)
line_length = len(val) + 7
tokens = 1
else:
if i:
buf.write(',')
buf.write(' ')
buf.write(val)
tokens += 1
line_length += len(val) + 2
if self.distinct:
select_key = 'SELECT DISTINCT'
else:
select_key = 'SELECT'
return '{0}{1}'.format(select_key, buf.getvalue())
def visit(what, extra_indents=0):
if isinstance(what, ir.Expr):
result = self._format_subexpr(what)
else:
result = self._indent(str(what))
if extra_indents > 0:
result = util.indent(result, self.indent_size)
formatted_args.append(result)
def format_subqueries(self):
context = self.context
subqueries = self.subqueries
return ',\n'.join([
'{0} AS (\n{1}\n)'.format(
context.get_ref(expr),
util.indent(context.get_compiled_expr(expr), 2)
) for expr in subqueries
])
def visit(what, extra_indents=0):
if isinstance(what, ir.Expr):
result = self._format_subexpr(what)
else:
result = self._indent(str(what))
if extra_indents > 0:
result = util.indent(result, self.indent_size)
formatted_args.append(result)
def format_select_set(self, context):
# TODO:
formatted = []
for expr in self.select_set:
if isinstance(expr, ir.ValueExpr):
expr_str = translate_expr(expr, context=context, named=True)
elif isinstance(expr, ir.TableExpr):
# A * selection, possibly prefixed
if context.need_aliases():
alias = context.get_alias(expr)
# materialized join will not have an alias. see #491
expr_str = '{0}.*'.format(alias) if alias else '*'
else:
expr_str = '*'
formatted.append(expr_str)
buf = BytesIO()
line_length = 0
max_length = 70
tokens = 0
for i, val in enumerate(formatted):
# always line-break for multi-line expressions
if val.count('\n'):
if i:
buf.write(',')
buf.write('\n')
indented = util.indent(val, self.indent)
buf.write(indented)
# set length of last line
line_length = len(indented.split('\n')[-1])
tokens = 1
elif (tokens > 0 and line_length and
len(val) + line_length > max_length):
# There is an expr, and adding this new one will make the line
# too long
buf.write(',\n ') if i else buf.write('\n')
buf.write(val)
line_length = len(val) + 7
tokens = 1
else:
if i:
buf.write(',')
buf.write(' ')
buf.write(val)
tokens += 1
line_length += len(val) + 2
if self.distinct:
select_key = 'SELECT DISTINCT'
else:
select_key = 'SELECT'
return '{0}{1}'.format(select_key, buf.getvalue())
def __repr__(self):
space = 2 + max(map(len, self.names))
return "ibis.Schema {{{0}\n}}".format(
util.indent(
''.join(
'\n{0}{1}'.format(name.ljust(space), str(tipo))
for name, tipo in zip(self.names, self.types)
),
2
)
)
def _fmt_table_op_sql_query_result(op: ops.SQLQueryResult, **_: Any) -> str:
short_query = textwrap.shorten(
op.query,
ibis.options.repr.query_text_length,
placeholder=f" {util.HORIZONTAL_ELLIPSIS}",
)
query = f"query: {short_query!r}"
top = op.__class__.__name__
formatted_schema = fmt_schema(op.schema)
schema_field = util.indent(f"schema:\n{formatted_schema}", spaces=2)
return f"{top}\n{util.indent(query, spaces=2)}\n{schema_field}"
def __repr__(self):
space = 2 + max(map(len, self.names))
return "ibis.Schema {{{0}\n}}".format(
util.indent(
''.join(
'\n{0}{1}'.format(name.ljust(space), str(tipo))
for name, tipo in zip(self.names, self.types)
),
2
)
)
def _fmt_table_op_sql_view(
op: ops.SQLStringView,
*,
aliases: Aliases,
**_: Any,
) -> str:
short_query = textwrap.shorten(
op.query,
ibis.options.repr.query_text_length,
placeholder=f" {util.HORIZONTAL_ELLIPSIS}",
)
query = f"query: {short_query!r}"
top = op.__class__.__name__
formatted_schema = fmt_schema(op.schema)
schema_field = util.indent(f"schema:\n{formatted_schema}", spaces=2)
components = [
f"{top}[{aliases[op.child.op()]}]: {op.name}",
util.indent(query, spaces=2),
schema_field,
]
return "\n".join(components)
def get_result(self):
# Got to unravel the join stack; the nesting order could be
# arbitrary, so we do a depth first search and push the join tokens
# and predicates onto a flat list, then format them
op = self.expr.op()
if isinstance(op, ops.Join):
self._walk_join_tree(op)
else:
self.join_tables.append(self._format_table(self.expr))
# TODO: Now actually format the things
buf = StringIO()
buf.write(self.join_tables[0])
for jtype, table, preds in zip(
self.join_types, self.join_tables[1:], self.join_predicates
):
buf.write('\n')
buf.write(util.indent(f'{jtype} {table}', self.indent))
fmt_preds = []
npreds = len(preds)
for pred in preds:
new_pred = self._translate(pred)
if npreds > 1:
new_pred = f'({new_pred})'
fmt_preds.append(new_pred)
if len(fmt_preds):
buf.write('\n')
conj = ' AND\n{}'.format(' ' * 3)
fmt_preds = util.indent(
'ON ' + conj.join(fmt_preds), self.indent * 2
)
buf.write(fmt_preds)
return buf.getvalue()
def format_subqueries(self):
if not self.subqueries:
return
context = self.context
buf = []
for i, expr in enumerate(self.subqueries):
formatted = util.indent(context.get_compiled_expr(expr), 2)
alias = context.get_ref(expr)
buf.append(f'{alias} AS (\n{formatted}\n)')
return 'WITH {}'.format(',\n'.join(buf))
def format_subqueries(self, context):
if len(self.subqueries) == 0:
return
buf = BytesIO()
buf.write('WITH ')
for i, expr in enumerate(self.subqueries):
if i > 0:
buf.write(',\n')
formatted = util.indent(context.get_formatted_query(expr), 2)
alias = context.get_alias(expr)
buf.write('{0} AS (\n{1}\n)'.format(alias, formatted))
return buf.getvalue()
def _exists_subquery(translator, expr):
op = expr.op()
ctx = translator.context
expr = (op.foreign_table.filter(op.predicates).projection(
[ir.literal(1).name(ir.unnamed)]))
subquery = ctx.get_formatted_query(expr)
if isinstance(op, transforms.ExistsSubquery):
key = 'EXISTS'
elif isinstance(op, transforms.NotExistsSubquery):
key = 'NOT EXISTS'
else:
raise NotImplementedError
return '{0} (\n{1}\n)'.format(key, util.indent(subquery, ctx.indent))
def format_subqueries(self):
if len(self.subqueries) == 0:
return
context = self.context
buf = StringIO()
buf.write('WITH ')
for i, expr in enumerate(self.subqueries):
if i > 0:
buf.write(',\n')
formatted = util.indent(context.get_compiled_expr(expr), 2)
alias = context.get_ref(expr)
buf.write('{0} AS (\n{1}\n)'.format(alias, formatted))
return buf.getvalue()
def format_subqueries(self):
if len(self.subqueries) == 0:
return
context = self.context
buf = StringIO()
buf.write('WITH ')
for i, expr in enumerate(self.subqueries):
if i > 0:
buf.write(',\n')
formatted = util.indent(context.get_compiled_expr(expr), 2)
alias = context.get_ref(expr)
buf.write('{0} AS (\n{1}\n)'.format(alias, formatted))
return buf.getvalue()
def fmt_fields(
op: ops.TableNode,
fields: Mapping[str, Callable[[Any, Aliases], str]],
*,
aliases: Aliases,
) -> str:
parts = []
for field, formatter in fields.items():
if exprs := [
expr for expr in util.promote_list(getattr(op, field))
if expr is not None
]:
field_fmt = [formatter(expr, aliases=aliases) for expr in exprs]
parts.append(f"{field}:")
parts.append(util.indent("\n".join(field_fmt), spaces=2))
def _exists_subquery(translator, expr):
op = expr.op()
ctx = translator.context
expr = (op.foreign_table
.filter(op.predicates)
.projection([ir.literal(1).name(ir.unnamed)]))
subquery = ctx.get_compiled_expr(expr)
if isinstance(op, transforms.ExistsSubquery):
key = 'EXISTS'
elif isinstance(op, transforms.NotExistsSubquery):
key = 'NOT EXISTS'
else:
raise NotImplementedError
return '{0} (\n{1}\n)'.format(key, util.indent(subquery, ctx.indent))
def exists_subquery(translator, expr):
op = expr.op()
ctx = translator.context
dummy = ir.literal(1).name(ir.unnamed)
filtered = op.foreign_table.filter(op.predicates)
expr = filtered.projection([dummy])
subquery = ctx.get_compiled_expr(expr)
if isinstance(op, ops.ExistsSubquery):
key = 'EXISTS'
elif isinstance(op, ops.NotExistsSubquery):
key = 'NOT EXISTS'
else:
raise NotImplementedError
return '{} (\n{}\n)'.format(key, util.indent(subquery, ctx.indent))
def _fmt_join(op: ops.Join, *, aliases: Aliases) -> tuple[str, str]:
# format the operator and its relation inputs
left = aliases[op.left.op()]
right = aliases[op.right.op()]
top = f"{op.__class__.__name__}[{left}, {right}]"
# format the join predicates
# if only one, put it directly after the join on thes same line
# if more than one put each on a separate line
preds = op.predicates
formatted_preds = [fmt_value(pred, aliases=aliases) for pred in preds]
has_one_pred = len(preds) == 1
sep = " " if has_one_pred else "\n"
joined_predicates = util.indent(
"\n".join(formatted_preds),
spaces=2 * (not has_one_pred),
)
trailing_sep = "\n" + "\n" * (not has_one_pred)
return f"{top}{sep}{joined_predicates}", trailing_sep
def _format_table(self, expr):
# TODO: This could probably go in a class and be significantly nicer
ctx = self.context
ref_expr = expr
op = ref_op = expr.op()
if isinstance(op, ops.SelfReference):
ref_expr = op.table
ref_op = ref_expr.op()
if isinstance(ref_op, ops.PhysicalTable):
name = ref_op.name
if name is None:
raise com.RelationError(
'Table did not have a name: {0!r}'.format(expr))
result = quote_identifier(name)
is_subquery = False
else:
# A subquery
if ctx.is_extracted(ref_expr):
# Was put elsewhere, e.g. WITH block, we just need to grab its
# alias
alias = ctx.get_ref(expr)
# HACK: self-references have to be treated more carefully here
if isinstance(op, ops.SelfReference):
return '{0} {1}'.format(ctx.get_ref(ref_expr), alias)
else:
return alias
subquery = ctx.get_compiled_expr(expr)
result = '(\n{0}\n)'.format(util.indent(subquery, self.indent))
is_subquery = True
if is_subquery or ctx.need_aliases():
result += ' {0}'.format(ctx.get_ref(expr))
return result
def _format_table(self, expr):
# TODO: This could probably go in a class and be significantly nicer
ctx = self.context
ref_expr = expr
op = ref_op = expr.op()
if isinstance(op, ops.SelfReference):
ref_expr = op.table
ref_op = ref_expr.op()
if isinstance(ref_op, ops.PhysicalTable):
name = ref_op.name
if name is None:
raise com.RelationError('Table did not have a name: {0!r}'
.format(expr))
result = quote_identifier(name)
is_subquery = False
else:
# A subquery
if ctx.is_extracted(ref_expr):
# Was put elsewhere, e.g. WITH block, we just need to grab its
# alias
alias = ctx.get_ref(expr)
# HACK: self-references have to be treated more carefully here
if isinstance(op, ops.SelfReference):
return '{0} {1}'.format(ctx.get_ref(ref_expr), alias)
else:
return alias
subquery = ctx.get_compiled_expr(expr)
result = '(\n{0}\n)'.format(util.indent(subquery, self.indent))
is_subquery = True
if is_subquery or ctx.need_aliases():
result += ' {0}'.format(ctx.get_ref(expr))
return result
def _indent(self, text, indents=1):
return util.indent(text, self.indent_size * indents)
def __repr__(self):
# Temporary
rows = ["Sort key:", " ascending: {0!s}".format(self.ascending), util.indent(_safe_repr(self.expr), 2)]
return "\n".join(rows)
*,
aliases: Aliases,
) -> str:
parts = []
for field, formatter in fields.items():
if exprs := [
expr for expr in util.promote_list(getattr(op, field))
if expr is not None
]:
field_fmt = [formatter(expr, aliases=aliases) for expr in exprs]
parts.append(f"{field}:")
parts.append(util.indent("\n".join(field_fmt), spaces=2))
return util.indent("\n".join(parts), spaces=2)
@fmt_table_op.register
def _fmt_table_op_selection(op: ops.Selection, *, aliases: Aliases,
**_: Any) -> str:
top = f"{op.__class__.__name__}[{aliases[op.table.op()]}]"
raw_parts = fmt_fields(
op,
dict(
selections=functools.partial(
fmt_selection_column,
maxlen=selection_maxlen(op.selections),
),
predicates=fmt_value,
sort_keys=fmt_value,
def _fmt_table_op_view(op: ops.View, *, aliases: Aliases, **_: Any) -> str:
top = op.__class__.__name__
formatted_schema = fmt_schema(op.schema)
schema_field = util.indent(f"schema:\n{formatted_schema}", spaces=2)
return f"{top}[{aliases[op.child.op()]}]: {op.name}\n{schema_field}"
def _table_array_view(translator, expr):
ctx = translator.context
table = expr.op().table
query = ctx.get_compiled_expr(table)
return '(\n{0}\n)'.format(util.indent(query, ctx.indent))
def __repr__(self):
lines = map('{}={!r},'.format, *zip(*self.items()))
return '{}(\n{}\n)'.format(
type(self).__name__, util.indent('\n'.join(lines), 4))
def __repr__(self):
lines = map('{}={!r},'.format, *zip(*self.items()))
return '{}(\n{}\n)'.format(
type(self).__name__, util.indent('\n'.join(lines), 4)
)
def __repr__(self):
# Temporary
rows = ['Sort key:',
' ascending: {0!s}'.format(self.ascending),
util.indent(_safe_repr(self.expr), 2)]
return '\n'.join(rows)
def _table_array_view(translator, expr):
ctx = translator.context
table = expr.op().table
query = ctx.get_compiled_expr(table)
return '(\n{0}\n)'.format(util.indent(query, ctx.indent))
def _indent(self, text, indents=1):
return util.indent(text, self.indent_size * indents)
