def _make_digits_table(self):
existence = self.db.query("PRAGMA table_info(__digits__)")
if not existence.data:
self.db.execute("CREATE TABLE __digits__(value INTEGER)")
self.db.execute("INSERT INTO __digits__ " +
SQL_UNION_ALL.join(SQL_SELECT + SQL(quote_value(i))
for i in range(10)))
def _insert(self, collection):
for nested_path, details in collection.items():
active_columns = wrap(list(details.active_columns))
rows = details.rows
table_name = concat_field(self.facts.snowflake.fact_name,
nested_path)
if table_name == self.facts.snowflake.fact_name:
# DO NOT REQUIRE PARENT OR ORDER COLUMNS
meta_columns = [GUID, UID]
else:
meta_columns = [UID, PARENT, ORDER]
all_columns = meta_columns + active_columns.es_column
prefix = ("INSERT INTO " + quote_column(table_name) +
sql_iso(sql_list(map(quote_column, all_columns))))
# BUILD THE RECORDS
records = SQL_UNION_ALL.join(
SQL_SELECT +
sql_list(quote_value(row.get(c)) for c in all_columns)
for row in unwrap(rows))
with self.db.transaction() as t:
t.execute(prefix + records)
def _make_digits_table(self):
existence = self.db.query("PRAGMA table_info(__digits__)")
if not existence.data:
with self.db.transaction() as t:
t.execute(
"CREATE TABLE" + quote_column(DIGITS_TABLE) + "(value INTEGER)"
)
t.execute(
"INSERT INTO"
+ quote_column(DIGITS_TABLE)
+ SQL_UNION_ALL.join(SQL_SELECT + quote_value(i) for i in range(10))
)
def get_sql(self, get_ids):
sql = self._compose_sql(get_ids)
# ORDERING
sort = []
ordering = []
for ci, c in enumerate(self.columns):
if c.sort:
sort.append(quote_column(c.column_alias) + SQL_IS_NOT_NULL)
sort.append(quote_column(c.column_alias))
ordering.append(ci)
union_all_sql = SQL_UNION_ALL.join(sql)
union_all_sql = (SQL_SELECT + SQL_STAR + SQL_FROM +
sql_alias(sql_iso(union_all_sql), quote_column('a')) +
SQL_ORDERBY + sql_list(sort))
return union_all_sql
pull=pull,
sql=sql,
type=sql_type_to_json_type[json_type],
column_alias=sql_name)
vals = [v for t, v in edge_values]
if query_edge.domain.type == "set":
domain_name = quote_column("d" + text_type(edge_index) + "c" +
text_type(column_index))
domain_names = [domain_name]
if len(edge_names) > 1:
Log.error("Do not know how to handle")
if query_edge.value:
domain = SQL_UNION_ALL.join(
SQL_SELECT +
sql_alias(quote_value(coalesce(p.dataIndex, i)),
quote_column("rownum")) + SQL_COMMA +
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)),
quote_column("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(
join_column(edge_alias, k) + " = " + v
for k, v in zip(domain_names, vals)) + SQL_OR +
sql_iso(
join_column(edge_alias, domain_name) +
SQL_IS_NULL + SQL_AND +
def _make_sql_for_one_nest_in_set_op(
self,
primary_nested_path,
selects, # EVERY SELECT CLAUSE (NOT TO BE USED ON ALL TABLES, OF COURSE
where_clause,
active_columns,
index_to_sql_select # MAP FROM INDEX TO COLUMN (OR SELECT CLAUSE)
):
"""
FOR EACH NESTED LEVEL, WE MAKE A QUERY THAT PULLS THE VALUES/COLUMNS REQUIRED
WE `UNION ALL` THEM WHEN DONE
:param primary_nested_path:
:param selects:
:param where_clause:
:param active_columns:
:param index_to_sql_select:
:return: SQL FOR ONE NESTED LEVEL
"""
parent_alias = "a"
from_clause = ""
select_clause = []
children_sql = []
done = []
if not where_clause:
where_clause = SQL_TRUE
# STATEMENT FOR EACH NESTED PATH
for i, (nested_path, sub_table) in enumerate(self.snowflake.tables):
if any(startswith_field(nested_path, d) for d in done):
continue
alias = "__" + unichr(ord('a') + i) + "__"
if primary_nested_path == nested_path:
select_clause = []
# ADD SELECT CLAUSE HERE
for select_index, s in enumerate(selects):
sql_select = index_to_sql_select.get(select_index)
if not sql_select:
select_clause.append(selects[select_index])
continue
if startswith_field(sql_select.nested_path[0],
nested_path):
select_clause.append(
sql_alias(sql_select.sql, sql_select.column_alias))
else:
# DO NOT INCLUDE DEEP STUFF AT THIS LEVEL
select_clause.append(
sql_alias(SQL_NULL, sql_select.column_alias))
if nested_path == ".":
from_clause += SQL_FROM + sql_alias(
quote_column(self.snowflake.fact_name), alias)
else:
from_clause += (SQL_LEFT_JOIN + sql_alias(
quote_column(
concat_field(self.snowflake.fact_name,
sub_table.name)), alias) + SQL_ON +
quote_column(alias, PARENT) + " = " +
quote_column(parent_alias, UID))
where_clause = sql_iso(
where_clause) + SQL_AND + quote_column(alias,
ORDER) + " > 0"
parent_alias = alias
elif startswith_field(primary_nested_path, nested_path):
# PARENT TABLE
# NO NEED TO INCLUDE COLUMNS, BUT WILL INCLUDE ID AND ORDER
if nested_path == ".":
from_clause += SQL_FROM + quote_column(
self.snowflake.fact_name + " " + alias)
else:
parent_alias = alias = unichr(ord('a') + i - 1)
from_clause += (SQL_LEFT_JOIN + quote_column(
concat_field(self.snowflake.fact_name,
sub_table.name)) + " " + alias + SQL_ON +
quote_column(alias, PARENT) + " = " +
quote_column(parent_alias, UID))
where_clause = sql_iso(
where_clause) + SQL_AND + quote_column(
parent_alias, ORDER) + " > 0"
parent_alias = alias
elif startswith_field(nested_path, primary_nested_path):
# CHILD TABLE
# GET FIRST ROW FOR EACH NESTED TABLE
from_clause += (SQL_LEFT_JOIN + sql_alias(
quote_column(
concat_field(self.snowflake.fact_name,
sub_table.name)), alias) + SQL_ON +
quote_column(alias, PARENT) + " = " +
quote_column(parent_alias, UID) + SQL_AND +
quote_column(alias, ORDER) + " = 0")
# IMMEDIATE CHILDREN ONLY
done.append(nested_path)
# NESTED TABLES WILL USE RECURSION
children_sql.append(
self._make_sql_for_one_nest_in_set_op(
nested_path,
selects, # EVERY SELECT CLAUSE (NOT TO BE USED ON ALL TABLES, OF COURSE
where_clause,
active_columns,
index_to_sql_select # MAP FROM INDEX TO COLUMN (OR SELECT CLAUSE)
))
else:
# SIBLING PATHS ARE IGNORED
continue
sql = SQL_UNION_ALL.join([
SQL_SELECT + sql_list(select_clause) + from_clause + SQL_WHERE +
where_clause
] + children_sql)
return sql
def update(self, command):
"""
:param command: EXPECTING dict WITH {"set": s, "clear": c, "where": w} FORMAT
"""
command = wrap(command)
# REJECT DEEP UPDATES
touched_columns = command.set.keys() | set(listwrap(command['clear']))
for c in self.get_leaves():
if c.name in touched_columns and c.nested_path and len(
c.name) > len(c.nested_path[0]):
Log.error("Deep update not supported")
# ADD NEW COLUMNS
where = jx_expression(command.where)
_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()
new_columns = set(command.set.keys()) - set(self.columns.keys())
for new_column_name in new_columns:
nested_value = command.set[new_column_name]
ctype = get_type(nested_value)
column = Column(name=new_column_name,
jx_type=ctype,
es_index=self.sf.fact,
es_column=typed_column(new_column_name, ctype))
self.add_column(column)
# UPDATE THE NESTED VALUES
for nested_column_name, nested_value in command.set.items():
if get_type(nested_value) == "nested":
nested_table_name = concat_field(self.sf.fact,
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_PREFIX + "id" + text_type(len(self.uid))
extra_key = [e
for e in nested_table.columns[extra_key_name]][0]
sql_command = ("DELETE" + SQL_FROM +
quote_column(nested_table.name) + SQL_WHERE +
"EXISTS (" + "\nSELECT 1 " + SQL_FROM +
quote_column(nested_table.name) + " n" +
SQL_INNER_JOIN + "(" + SQL_SELECT +
self_primary_key + SQL_FROM +
quote_column(self.sf.fact) + SQL_WHERE +
where_sql + "\n) t ON " +
SQL_AND.join("t." + quote_column(c.es_column) +
" = n." + quote_column(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 = "INSERT INTO " + 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(self.sf.fact) + SQL_WHERE +
jx_expression(command.where).to_sql())
# BUILD THE RECORDS
children = SQL_UNION_ALL.join(
SQL_SELECT + quote_value(i) + " " +
quote_column(extra_key.es_column) + "," + sql_list(
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([
join_column("p", c.es_column) for u in self.uid
for c in self.columns[u]
] + [join_column("c", extra_key)] + [
join_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" + " 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)
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 = (
"UPDATE " + quote_column(self.sf.fact) + " SET " + sql_list([
quote_column(c) + "=" + quote_value(get_if_type(v, c.jx_type))
for k, v in command.set.items() if get_type(v) != "nested"
for c in self.columns[k]
if c.jx_type != "nested" and len(c.nested_path) == 1
] + [
quote_column(c) + "=" + SQL_NULL
for k in listwrap(command['clear']) if k in self.columns
for c in self.columns[k]
if c.jx_type != "nested" and len(c.nested_path) == 1
]) + SQL_WHERE + where_sql)
self.db.execute(command)