def to_bq(self, schema, not_null=False, boolean=False):
value = BQLang[self.expr].partial_eval()
missing_value = value.missing().partial_eval()
if not is_op(missing_value, MissingOp):
return missing_value.to_bq(schema)
value_sql = value.to_bq(schema)
if len(value_sql) > 1:
return wrap([{"name": ".", "sql": {"b": SQL_FALSE}}])
acc = []
for c in value_sql:
for t, v in c.sql.items():
if t == "b":
acc.append(sql_iso(v) + SQL_IS_NULL)
if t == "s":
acc.append(
sql_iso(sql_iso(v) + SQL_IS_NULL) + SQL_OR +
sql_iso(sql_iso(v) + "=" + SQL_EMPTY_STRING))
if t == "n":
acc.append(sql_iso(v) + SQL_IS_NULL)
if not acc:
return wrap([{"name": ".", "sql": {"b": SQL_TRUE}}])
else:
return wrap([{"name": ".", "sql": {"b": SQL_AND.join(acc)}}])
def sql_eq(**item):
"""
RETURN SQL FOR COMPARING VARIABLES TO VALUES (AND'ED TOGETHER)
:param item: keyword parameters representing variable and value
:return: SQL
"""
return SQL_AND.join([
ConcatSQL(quote_column(text(k)), SQL_EQ, quote_value(v))
if v != None else ConcatSQL(quote_column(text(k)), SQL_IS_NULL)
for k, v in item.items()
])
def single(col, r):
min = coalesce(r["gte"], r[">="])
max = coalesce(r["lte"], r["<="])
if min != None and max != None:
# SPECIAL CASE (BETWEEN)
sql = quote_column(col) + SQL(" BETWEEN ") + quote_value(
min) + SQL_AND + quote_value(max)
else:
sql = SQL_AND.join(
quote_column(col) + name2sign[sign] + quote_value(value)
for sign, value in r.items())
return sql
def to_bq(self, schema, not_null=False, boolean=False):
if not self.terms:
return wrap([{"name": ".", "sql": {"b": SQL_TRUE}}])
elif all(self.terms):
return wrap([{
"name": ".",
"sql": {
"b":
SQL_AND.join([
sql_iso(BQLang[t].to_bq(schema, boolean=True)[0].sql.b)
for t in self.terms
])
},
}])
else:
return wrap([{"name": ".", "sql": {"b": SQL_FALSE}}])
sql_alias(quote_value(p.value), domain_name)
for i, p in enumerate(query_edge.domain.partitions))
if query_edge.allowNulls:
domain += (SQL_UNION_ALL + SQL_SELECT + sql_alias(
quote_value(len(query_edge.domain.partitions)),
"rownum") + SQL_COMMA +
sql_alias(SQL_NULL, domain_name))
where = None
join_type = SQL_LEFT_JOIN if query_edge.allowNulls else SQL_INNER_JOIN
on_clause = (SQL_OR.join(
quote_column(edge_alias, k) + SQL_EQ + v
for k, v in zip(domain_names, vals)) + SQL_OR +
sql_iso(
quote_column(edge_alias, domain_name) +
SQL_IS_NULL + SQL_AND +
SQL_AND.join(v + SQL_IS_NULL
for v in vals)))
null_on_clause = None
else:
domain = SQL_UNION_ALL.join(
SQL_SELECT + sql_alias(quote_value(pp), domain_name)
for pp, p in enumerate(query_edge.domain.partitions))
where = None
join_type = SQL_LEFT_JOIN if query_edge.allowNulls else SQL_INNER_JOIN
on_clause = SQL_AND.join(
quote_column(edge_alias, k) + SQL_EQ + sql
for k, (t, sql) in zip(domain_names, edge_values))
null_on_clause = None
elif query_edge.domain.type == "range":
domain_name = "d" + text(edge_index) + "c0"
domain_names = [
domain_name
def _compose_sql(self, get_ids):
"""
:param get_ids: SQL to get the ids, and used to select the documents returned
:return:
"""
if not isinstance(get_ids, SQL):
Log.error("Expecting SQL to get some primary ids")
sql = []
for nested_path in self.all_nested_paths:
# MAKE THE REQUIRED JOINS
sql_joins = []
for i, curr_join in enumerate(
self.nested_path_to_join[nested_path[0]]):
curr_join = wrap(curr_join)
rel = curr_join.join_columns[0]
if i == 0:
sql_joins.append(
ConcatSQL(
SQL_FROM,
sql_alias(sql_iso(get_ids),
rel.referenced.table.alias),
))
elif curr_join.children:
full_name = quote_column(rel.table.schema, rel.table.name)
sql_joins.append(
ConcatSQL(
SQL_INNER_JOIN,
sql_alias(full_name, rel.table.alias),
SQL_ON,
SQL_AND.join(
ConcatSQL(
quote_column(rel.table.alias,
const_col.column.name),
SQL_EQ,
quote_column(
rel.referenced.table.alias,
const_col.referenced.column.name,
),
) for const_col in curr_join.join_columns),
))
else:
full_name = quote_column(rel.referenced.table.schema,
rel.referenced.table.name)
sql_joins.append(
ConcatSQL(
SQL_LEFT_JOIN,
sql_alias(full_name, rel.referenced.table.alias),
SQL_ON,
SQL_AND.join(
ConcatSQL(
quote_column(
rel.referenced.table.alias,
const_col.referenced.column.name,
),
SQL_EQ,
quote_column(rel.table.alias,
const_col.column.name),
) for const_col in curr_join.join_columns),
))
# ONLY SELECT WHAT WE NEED, NULL THE REST
selects = []
not_null_column_seen = False
for c in self.columns:
if (
c.column.table.name,
c.column.column.name,
) in self.settings.exclude_columns:
selects.append(sql_alias(SQL_NULL, c.column_alias))
elif c.nested_path[0] == nested_path[0]:
s = sql_alias(
quote_column(c.table_alias, c.column.column.name),
c.column_alias,
)
selects.append(s)
not_null_column_seen = True
elif startswith_field(nested_path[0], c.path):
# PARENT ID REFERENCES
if c.column.is_id:
s = sql_alias(
quote_column(c.table_alias, c.column.column.name),
c.column_alias,
)
selects.append(s)
not_null_column_seen = True
else:
selects.append(sql_alias(SQL_NULL, c.column_alias))
else:
selects.append(sql_alias(SQL_NULL, c.column_alias))
if not_null_column_seen:
sql.append(SQL_SELECT + sql_list(selects) +
SQL("").join(sql_joins))
return sql
def _esfilter2sqlwhere(esfilter):
"""
CONVERT ElassticSearch FILTER TO SQL FILTER
db - REQUIRED TO PROPERLY QUOTE VALUES AND COLUMN NAMES
"""
esfilter = wrap(esfilter)
if esfilter is True:
return SQL_TRUE
elif esfilter["and"]:
return sql_iso(
SQL_AND.join([esfilter2sqlwhere(a) for a in esfilter["and"]]))
elif esfilter["or"]:
return sql_iso(
SQL_OR.join([esfilter2sqlwhere(a) for a in esfilter["or"]]))
elif esfilter["not"]:
return SQL_NOT + sql_iso(esfilter2sqlwhere(esfilter["not"]))
elif esfilter.term:
return sql_iso(
SQL_AND.join([
quote_column(col) + SQL("=") + quote_value(val)
for col, val in esfilter.term.items()
]))
elif esfilter.eq:
col, val = first(esfilter.eq.items())
return ConcatSQL(quote_column(col), SQL_EQ, quote_value(val))
elif esfilter.terms:
for col, v in esfilter.terms.items():
if len(v) == 0:
return "FALSE"
try:
int_list = convert.value2intlist(v)
has_null = any(vv == None for vv in v)
if int_list:
filter = int_list_packer(col, int_list)
if has_null:
return esfilter2sqlwhere(
{"or": [{
"missing": col
}, filter]})
elif 'terms' in filter and set(filter['terms'].get(
col, [])) == set(int_list):
return quote_column(col) + " in " + quote_list(
int_list)
else:
return esfilter2sqlwhere(filter)
else:
if has_null:
return esfilter2sqlwhere({"missing": col})
else:
return "false"
except Exception as e:
e = Except.wrap(e)
pass
return quote_column(col) + " in " + quote_list(v)
elif esfilter.script:
return sql_iso(esfilter.script)
elif esfilter.gt:
k, v = first(esfilter.gt.items())
return ConcatSQL(quote_column(k), SQL_GT, quote_value(v))
elif esfilter.range:
name2sign = {
"gt": SQL(">"),
"gte": SQL(">="),
"lte": SQL("<="),
"lt": SQL("<")
}
def single(col, r):
min = coalesce(r["gte"], r[">="])
max = coalesce(r["lte"], r["<="])
if min != None and max != None:
# SPECIAL CASE (BETWEEN)
sql = quote_column(col) + SQL(" BETWEEN ") + quote_value(
min) + SQL_AND + quote_value(max)
else:
sql = SQL_AND.join(
quote_column(col) + name2sign[sign] + quote_value(value)
for sign, value in r.items())
return sql
terms = [single(col, ranges) for col, ranges in esfilter.range.items()]
if len(terms) == 1:
output = terms[0]
else:
output = sql_iso(SQL_AND.join(terms))
return output
elif esfilter.missing:
if isinstance(esfilter.missing, text):
return sql_iso(quote_column(esfilter.missing) + SQL_IS_NULL)
else:
return sql_iso(quote_column(esfilter.missing.field) + SQL_IS_NULL)
elif esfilter.exists:
if isinstance(esfilter.exists, text):
return sql_iso(quote_column(esfilter.exists) + SQL_IS_NOT_NULL)
else:
return sql_iso(
quote_column(esfilter.exists.field) + SQL_IS_NOT_NULL)
elif esfilter.match_all:
return SQL_TRUE
elif esfilter.instr:
return sql_iso(
SQL_AND.join([
"instr" +
sql_iso(quote_column(col) + ", " + quote_value(val)) + ">0"
for col, val in esfilter.instr.items()
]))
else:
Log.error("Can not convert esfilter to SQL: {{esfilter}}",
esfilter=esfilter)
def update(self, command):
"""
:param command: EXPECTING dict WITH {"set": s, "clear": c, "where": w} FORMAT
"""
command = wrap(command)
clear_columns = set(listwrap(command['clear']))
# REJECT DEEP UPDATES
touched_columns = command.set.keys() | clear_columns
for c in self.schema.columns:
if c.name in touched_columns and len(c.nested_path) > 1:
Log.error("Deep update not supported")
# ADD NEW COLUMNS
where = jx_expression(command.where) or TRUE
_vars = where.vars()
_map = {
v: c.es_column
for v in _vars for c in self.columns.get(v, Null)
if c.jx_type not in STRUCT
}
where_sql = where.map(_map).to_sql(self.schema)[0].sql.b
new_columns = set(command.set.keys()) - set(
c.name for c in self.schema.columns)
for new_column_name in new_columns:
nested_value = command.set[new_column_name]
ctype = get_jx_type(nested_value)
column = Column(name=new_column_name,
jx_type=ctype,
es_index=self.name,
es_type=json_type_to_sqlite_type(ctype),
es_column=typed_column(new_column_name, ctype),
last_updated=Date.now())
self.add_column(column)
# UPDATE THE NESTED VALUES
for nested_column_name, nested_value in command.set.items():
if get_jx_type(nested_value) == "nested":
nested_table_name = concat_field(self.name, nested_column_name)
nested_table = nested_tables[nested_column_name]
self_primary_key = sql_list(
quote_column(c.es_column) for u in self.uid
for c in self.columns[u])
extra_key_name = UID + text(len(self.uid))
extra_key = [e
for e in nested_table.columns[extra_key_name]][0]
sql_command = (
SQL_DELETE + SQL_FROM + quote_column(nested_table.name) +
SQL_WHERE + "EXISTS" +
sql_iso(SQL_SELECT + SQL_ONE + SQL_FROM +
sql_alias(quote_column(nested_table.name), "n") +
SQL_INNER_JOIN +
sql_iso(SQL_SELECT + self_primary_key + SQL_FROM +
quote_column(abs_schema.fact) + SQL_WHERE +
where_sql) + " t ON " +
SQL_AND.join(
quote_column("t", c.es_column) + SQL_EQ +
quote_column("n", c.es_column)
for u in self.uid for c in self.columns[u])))
self.db.execute(sql_command)
# INSERT NEW RECORDS
if not nested_value:
continue
doc_collection = {}
for d in listwrap(nested_value):
nested_table.flatten(d,
Data(),
doc_collection,
path=nested_column_name)
prefix = SQL_INSERT + quote_column(nested_table.name) + sql_iso(
sql_list([self_primary_key] + [quote_column(extra_key)] + [
quote_column(c.es_column)
for c in doc_collection.get(".", Null).active_columns
]))
# BUILD THE PARENT TABLES
parent = (SQL_SELECT + self_primary_key + SQL_FROM +
quote_column(abs_schema.fact) + SQL_WHERE +
jx_expression(command.where).to_sql(schema))
# BUILD THE RECORDS
children = SQL_UNION_ALL.join(
SQL_SELECT +
sql_alias(quote_value(i), extra_key.es_column) +
SQL_COMMA + sql_list(
sql_alias(quote_value(row[c.name]),
quote_column(c.es_column))
for c in doc_collection.get(".", Null).active_columns)
for i, row in enumerate(
doc_collection.get(".", Null).rows))
sql_command = (prefix + SQL_SELECT + sql_list([
quote_column("p", c.es_column) for u in self.uid
for c in self.columns[u]
] + [quote_column("c", extra_key)] + [
quote_column("c", c.es_column)
for c in doc_collection.get(".", Null).active_columns
]) + SQL_FROM + sql_iso(parent) + " p" + SQL_INNER_JOIN +
sql_iso(children) + " c" + SQL_ON + SQL_TRUE)
self.db.execute(sql_command)
# THE CHILD COLUMNS COULD HAVE EXPANDED
# ADD COLUMNS TO SELF
for n, cs in nested_table.columns.items():
for c in cs:
column = Column(name=c.name,
jx_type=c.jx_type,
es_type=c.es_type,
es_index=c.es_index,
es_column=c.es_column,
nested_path=[nested_column_name] +
c.nested_path,
last_updated=Date.now())
if c.name not in self.columns:
self.columns[column.name] = {column}
elif c.jx_type not in [
c.jx_type for c in self.columns[c.name]
]:
self.columns[column.name].add(column)
command = ConcatSQL(
SQL_UPDATE, quote_column(self.name), SQL_SET,
sql_list([
quote_column(c.es_column) + SQL_EQ +
quote_value(get_if_type(v, c.jx_type))
for c in self.schema.columns
if c.jx_type != NESTED and len(c.nested_path) == 1
for v in [command.set[c.name]] if v != None
] + [
quote_column(c.es_column) + SQL_EQ + SQL_NULL
for c in self.schema.columns
if (c.name in clear_columns and command.set[c.name] != None
and c.jx_type != NESTED and len(c.nested_path) == 1)
]), SQL_WHERE, where_sql)
with self.db.transaction() as t:
t.execute(command)