def to_sql(self, schema, not_null=False, boolean=False):
lhs = SQLang[self.lhs].partial_eval()
rhs = SQLang[self.rhs].partial_eval()
lhs_sql = lhs.to_sql(schema, not_null=True)
rhs_sql = rhs.to_sql(schema, not_null=True)
if is_literal(rhs) and lhs_sql[0].sql.b != None and rhs.value in ('T',
'F'):
rhs_sql = BooleanOp(rhs).to_sql(schema)
if is_literal(lhs) and rhs_sql[0].sql.b != None and lhs.value in ('T',
'F'):
lhs_sql = BooleanOp(lhs).to_sql(schema)
if len(lhs_sql) != len(rhs_sql):
Log.error("lhs and rhs have different dimensionality!?")
acc = []
for l, r in zip(lhs_sql, rhs_sql):
for t in "bsnj":
if r.sql[t] == None:
if l.sql[t] == None:
pass
else:
acc.append(ConcatSQL((l.sql[t], SQL_IS_NULL)))
elif l.sql[t] == None:
acc.append(ConcatSQL((r.sql[t], SQL_IS_NULL)))
else:
acc.append(
ConcatSQL(
(sql_iso(l.sql[t]), SQL_EQ, sql_iso(r.sql[t]))))
if not acc:
return FALSE.to_sql(schema)
else:
return wrap([{"name": ".", "sql": {"b": JoinSQL(SQL_OR, acc)}}])
def quote_column(*path):
if not path:
Log.error("expecting a name")
if any(not is_text(p) for p in path):
Log.error("expecting strings, not SQL")
try:
return ConcatSQL((SQL_SPACE, JoinSQL(SQL_DOT, [SQL(quote(p)) for p in path]), SQL_SPACE))
except Exception as e:
Log.error("Not expacted", cause=e)
def to_sql(self, schema, not_null=False, boolean=False):
return wrap([{
"name": ".",
"sql": {
"b":
JoinSQL(SQL_OR, [
sql_iso(SQLang[t].to_sql(schema, boolean=True)[0].sql.b)
for t in self.terms
])
},
}])
def sql_query(command):
"""
VERY BASIC QUERY EXPRESSION TO SQL
:param command: jx-expression
:return: SQL
"""
command = wrap(command)
acc = [SQL_SELECT]
if command.select:
acc.append(JoinSQL(SQL_COMMA, map(quote_column, listwrap(command.select))))
else:
acc.append(SQL_STAR)
acc.append(SQL_FROM)
acc.append(quote_column(command["from"]))
if command.where.eq:
acc.append(SQL_WHERE)
acc.append(sql_eq(**command.where.eq))
if command.orderby:
acc.append(SQL_ORDERBY)
acc.append(JoinSQL(SQL_COMMA, map(quote_column, listwrap(command.orderby))))
return ConcatSQL(acc)
def where(self, filter):
"""
WILL NOT PULL WHOLE OBJECT, JUST TOP-LEVEL PROPERTIES
:param filter: jx_expression filter
:return: list of objects that match
"""
select = []
column_names = []
for c in self.schema.columns:
if c.jx_type in STRUCT:
continue
if len(c.nested_path) != 1:
continue
column_names.append(c.name)
select.append(sql_alias(quote_column(c.es_column), c.name))
where_sql = SQLang[jx_expression(filter)].to_sql(self.schema)[0].sql.b
result = self.db.query(ConcatSQL((
SQL_SELECT, JoinSQL(SQL_COMMA, select),
SQL_FROM, quote_column(self.snowflake.fact_name),
SQL_WHERE, where_sql
)))
return wrap([{c: v for c, v in zip(column_names, r)} for r in result.data])
def quote_column(*path):
if not path:
Log.error("missing column_name")
if len(path) == 1:
return SQL("`" + path[0].replace('`', '``') + "`")
return JoinSQL(SQL_DOT, map(quote_column, path))