def to_sql(self, schema, not_null=False, boolean=False):
v = self.value.to_sql(schema, not_null=True)[0].sql.s
r = self.length.to_sql(schema, not_null=True)[0].sql.n
l = sql_call(
"MAX", SQL_ZERO,
sql_call("length", v) + SQL(" - ") + sql_call("MAX", SQL_ZERO, r))
sql = sql_call("SUBSTR", v, SQL_ONE, l)
return wrap([{"name": ".", "sql": {"s": sql}}])
def to_sql(self, schema, not_null=False, boolean=False):
value = SQLang[self.value].to_sql(schema, not_null=True)[0].sql.s
start = SQLang[self.start].to_sql(schema, not_null=True)[0].sql.n
if self.length is NULL:
sql = sql_call("SUBSTR", value, start)
else:
length = SQLang[self.length].to_sql(schema, not_null=True)[0].sql.n
sql = sql_call("SUBSTR", value, start, length)
return wrap([{"name": ".", "sql": {"s": sql}}])
def multiop_to_sql(self, schema, not_null=False, boolean=False, many=False):
sign, zero = _sql_operators[self.op]
if len(self.terms) == 0:
return SQLang[self.default].to_sql(schema)
elif self.default is NULL:
return sign.join(
sql_call("COALESCE", SQLang[t].to_sql(schema), zero)
for t in self.terms)
else:
return sql_call(
"COALESCE",
sign.join(sql_iso(SQLang[t].to_sql(schema)) for t in self.terms),
SQLang[self.default].to_sql(schema))
def to_sql(self, schema, not_null=False, boolean=False):
value = SQLang[self.value].partial_eval().to_sql(schema)[0].sql.s
find = SQLang[self.find].partial_eval().to_sql(schema)[0].sql.s
start = SQLang[self.start].partial_eval().to_sql(schema)[0].sql.n
default = coalesce(
SQLang[self.default].partial_eval().to_sql(schema)[0].sql.n, SQL_NULL
)
if start.sql != SQL_ZERO.sql:
value = NotRightOp([self.value, self.start]).to_sql(schema)[0].sql.s
index = sql_call("INSTR", value, find)
i = quote_column("i")
sql = with_var(
i,
index,
ConcatSQL(
SQL_CASE,
SQL_WHEN,
i,
SQL_THEN,
i,
SQL(" - "),
SQL_ONE,
SQL_PLUS,
start,
SQL_ELSE,
default,
SQL_END,
),
)
return wrap([{"name": ".", "sql": {"n": sql}}])
def to_sql(self, schema, not_null=False, boolean=False):
default = self.default.to_sql(schema)
if len(self.terms) == 0:
return default
len_sep = LengthOp(self.separator).partial_eval()
no_sep = is_literal(len_sep) and len_sep.value == 0
sep = SQLang[self.separator].to_sql(schema)[0].sql.s
acc = []
for t in self.terms:
t = SQLang[t]
missing = t.missing().partial_eval()
term = t.to_sql(schema, not_null=True)[0].sql
if term.s:
term_sql = term.s
elif term.n:
term_sql = "cast(" + term.n + " as text)"
else:
term_sql = (SQL_CASE + SQL_WHEN + term.b + SQL_THEN +
quote_value("true") + SQL_ELSE +
quote_value("false") + SQL_END)
if no_sep:
sep_term = term_sql
else:
sep_term = sql_iso(sql_concat_text([sep, term_sql]))
if isinstance(missing, TrueOp):
acc.append(SQL_EMPTY_STRING)
elif missing:
acc.append(
SQL_CASE + SQL_WHEN +
sql_iso(missing.to_sql(schema, boolean=True)[0].sql.b) +
SQL_THEN + SQL_EMPTY_STRING + SQL_ELSE + sep_term +
SQL_END)
else:
acc.append(sep_term)
if no_sep:
expr_ = sql_concat_text(acc)
else:
expr_ = sql_call(
"SUBSTR", sql_concat_text(acc),
ConcatSQL(
LengthOp(self.separator).to_sql(schema)[0].sql.n, SQL_PLUS,
SQL_ONE))
return SQLScript(
expr=expr_,
data_type=STRING,
frum=self,
miss=self.missing(),
many=False,
schema=schema,
)
def to_sql(self, schema, not_null=False, boolean=False):
value = SQLang[self.value].to_sql(schema, not_null=True)[0].sql.s
start = (AddOp([self.start, Literal(1)
]).partial_eval().to_sql(schema,
not_null=True)[0].sql.n)
length = (SubOp([self.end, self.start
]).partial_eval().to_sql(schema,
not_null=True)[0].sql.n)
sql = sql_call("SUBSTR", value, start, length)
return wrap([{"name": ".", "sql": {"s": sql}}])
def to_sql(self, schema, not_null=False, boolean=False):
value = self.value.to_sql(schema, not_null=True)[0].sql.s
find = self.find.to_sql(schema, not_null=True)[0].sql.s
return wrap([{
"name": ".",
"sql": {
"n": sql_call("INSTR", value, find)
}
}])
def to_sql(self, schema, not_null=False, boolean=False):
value = self.value.to_sql(schema)[0].sql.s
find = self.find.to_sql(schema)[0].sql.s
start = self.start
if isinstance(start, Literal) and start.value == 0:
return wrap(
[
{
"name": ".",
"sql": {"n": sql_call("INSTR", value, find) + "-1"},
}
]
)
else:
start_index = start.to_sql(schema)[0].sql.n
found = sql_call("INSTR", sql_call("SUBSTR", value, start_index), SQL_ONE, find)
return wrap(
[
{
"name": ".",
"sql": {
"n": (
SQL_CASE
+ SQL_WHEN
+ found
+ SQL_THEN
+ found
+ "+"
+ start_index
+ "-1"
+ SQL_ELSE
+ "-1"
+ SQL_END
)
},
}
]
)
def to_sql(self, schema, not_null=False, boolean=False):
if not self.expr:
return wrap([{"name": ".", "sql": {"b": SQL_TRUE}}])
else:
sql = ConcatSQL(
sql_call(
"INSTR",
SQLang[self.expr].to_sql(schema)[0].sql.s,
SQLang[self.prefix].to_sql(schema)[0].sql.s,
),
SQL_EQ,
SQL_ONE,
)
return wrap([{"name": ".", "sql": {"b": sql}}])
def to_sql(self, schema, not_null=False, boolean=False):
expr = SQLang[self.term].partial_eval().to_sql(schema)[0].sql.n
if not expr:
return SQLScript(
expr=SQL_NULL,
data_type=IS_NULL,
frum=self,
miss=TRUE,
schema=schema,
)
return SQLScript(
expr=sql_call("ABS", expr),
data_type=NUMBER,
frum=self,
miss=self.missing(),
schema=schema,
)
def _groupby_op(self, query, schema):
base_table = schema.snowflake.fact_name
path = schema.nested_path
# base_table, path = tail_field(frum)
# schema = self.snowflake.tables[path].schema
index_to_column = {}
nest_to_alias = {
nested_path: "__" + unichr(ord('a') + i) + "__"
for i, nested_path in enumerate(self.schema.snowflake.query_paths)
}
tables = []
for n, a in nest_to_alias.items():
if startswith_field(path, n):
tables.append({"nest": n, "alias": a})
tables = jx.sort(tables, {"value": {"length": "nest"}})
from_sql = join_field(
[base_table] + split_field(tables[0].nest)) + " " + tables[0].alias
previous = tables[0]
for t in tables[1::]:
from_sql += (SQL_LEFT_JOIN +
quote_column(concat_field(base_table, t.nest)) + " " +
t.alias + SQL_ON + quote_column(t.alias, PARENT) +
SQL_EQ + quote_column(previous.alias, UID))
selects = []
groupby = []
for i, e in enumerate(query.groupby):
for edge_sql in SQLang[e.value].to_sql(schema):
column_number = len(selects)
sql_type, sql = edge_sql.sql.items()[0]
if sql is SQL_NULL and not e.value.var in schema.keys():
Log.error("No such column {{var}}", var=e.value.var)
column_alias = _make_column_name(column_number)
groupby.append(sql)
selects.append(sql_alias(sql, column_alias))
if edge_sql.nested_path == ".":
select_name = edge_sql.name
else:
select_name = "."
index_to_column[column_number] = ColumnMapping(
is_edge=True,
push_name=e.name,
push_column_name=e.name.replace("\\.", "."),
push_column=i,
push_child=select_name,
pull=get_column(column_number),
sql=sql,
column_alias=column_alias,
type=sql_type_to_json_type[sql_type])
for i, select in enumerate(listwrap(query.select)):
column_number = len(selects)
sql_type, sql = SQLang[select.value].to_sql(
schema)[0].sql.items()[0]
if sql == 'NULL' and not select.value.var in schema.keys():
Log.error("No such column {{var}}", var=select.value.var)
# AGGREGATE
if select.value == "." and select.aggregate == "count":
sql = sql_count(SQL_ONE)
else:
sql = sql_call(sql_aggs[select.aggregate], sql)
if select.default != None:
sql = sql_coalesce([sql, quote_value(select.default)])
selects.append(sql_alias(sql, select.name))
index_to_column[column_number] = ColumnMapping(
push_name=select.name,
push_column_name=select.name,
push_column=i + len(query.groupby),
push_child=".",
pull=get_column(column_number),
sql=sql,
column_alias=quote_column(select.name),
type=sql_type_to_json_type[sql_type])
for w in query.window:
selects.append(self._window_op(self, query, w))
where = SQLang[query.where].to_sql(schema)[0].sql.b
command = (SQL_SELECT + (sql_list(selects)) + SQL_FROM + from_sql +
SQL_WHERE + where + SQL_GROUPBY + sql_list(groupby))
if query.sort:
command += SQL_ORDERBY + sql_list(
sql_iso(sql[t]) + SQL_IS_NULL + "," + sql[t] +
(" DESC" if s.sort == -1 else "")
for s, sql in [(s, SQLang[s.value].to_sql(schema)[0].sql)
for s in query.sort] for t in "bns" if sql[t])
return command, index_to_column