def to_sql(self, schema, not_null=False, boolean=False): value = SQLang[self.expr].partial_eval() missing_value = value.missing().partial_eval() if not is_op(missing_value, MissingOp): return missing_value.to_sql(schema) value_sql = value.to_sql(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 in "bn": acc.append(ConcatSQL(sql_iso(v), SQL_IS_NULL)) if t == "s": acc.append(ConcatSQL( sql_iso(sql_iso(v), SQL_IS_NULL), SQL_OR, sql_iso(sql_iso(v), SQL_EQ, SQL_EMPTY_STRING) )) if not acc: return wrap([{"name": ".", "sql": {"b": SQL_TRUE}}]) else: return wrap([{"name": ".", "sql": {"b": SQL_AND.join(acc)}}])
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 _nest_column(self, column): new_path, type_ = untyped_column(column.es_column) if type_ != SQL_NESTED_TYPE: Log.error("only nested types can be nested") destination_table = concat_field(self.fact_name, new_path) existing_table = concat_field(self.fact_name, column.nested_path[0]) # FIND THE INNER COLUMNS WE WILL BE MOVING moving_columns = [] for c in self.columns: if destination_table != column.es_index and column.es_column == c.es_column: moving_columns.append(c) c.nested_path = new_path # TODO: IF THERE ARE CHILD TABLES, WE MUST UPDATE THEIR RELATIONS TOO? # LOAD THE COLUMNS data = self.namespace.db.about(destination_table) if not data: # DEFINE A NEW TABLE command = ( SQL_CREATE + quote_column(destination_table) + sql_iso(sql_list([ quoted_UID + "INTEGER", quoted_PARENT + "INTEGER", quoted_ORDER + "INTEGER", "PRIMARY KEY " + sql_iso(quoted_UID), "FOREIGN KEY " + sql_iso(quoted_PARENT) + " REFERENCES " + quote_column(existing_table) + sql_iso(quoted_UID) ])) ) with self.namespace.db.transaction() as t: t.execute(command) self.add_table([new_path]+column.nested_path) # TEST IF THERE IS ANY DATA IN THE NEW NESTED ARRAY if not moving_columns: return column.es_index = destination_table with self.namespace.db.transaction() as t: t.execute( "ALTER TABLE " + quote_column(destination_table) + " ADD COLUMN " + quote_column(column.es_column) + " " + column.es_type ) # Deleting parent columns for col in moving_columns: column = col.es_column tmp_table = "tmp_" + existing_table columns = list(map(text, t.query(SQL_SELECT + SQL_STAR + SQL_FROM + quote_column(existing_table) + SQL_LIMIT + SQL_ZERO).header)) t.execute( "ALTER TABLE " + quote_column(existing_table) + " RENAME TO " + quote_column(tmp_table) ) t.execute( SQL_CREATE + quote_column(existing_table) + SQL_AS + SQL_SELECT + sql_list([quote_column(c) for c in columns if c != column]) + SQL_FROM + quote_column(tmp_table) ) t.execute("DROP TABLE " + quote_column(tmp_table))
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 _inequality_to_sql(self, schema, not_null=False, boolean=False, many=True): op, identity = _sql_operators[self.op] lhs = NumberOp(self.lhs).partial_eval().to_sql(schema, not_null=True)[0].sql.n rhs = NumberOp(self.rhs).partial_eval().to_sql(schema, not_null=True)[0].sql.n sql = sql_iso(lhs) + op + sql_iso(rhs) output = SQLScript( data_type=BOOLEAN, expr=sql, frum=self, miss=OrOp([self.lhs.missing(), self.rhs.missing()]), schema=schema, ) return output
def to_sql(self, schema, not_null=False, boolean=False): acc = [] for term in self.terms: sqls = SQLang[term].to_sql(schema) if len(sqls) > 1: acc.append(SQL_TRUE) else: for t, v in sqls[0].sql.items(): if t in ["b", "s", "n"]: acc.append( ConcatSQL( SQL_CASE, SQL_WHEN, sql_iso(v), SQL_IS_NULL, SQL_THEN, SQL_ZERO, SQL_ELSE, SQL_ONE, SQL_END, )) else: acc.append(SQL_TRUE) if not acc: return wrap([{}]) else: return wrap([{"nanme": ".", "sql": {"n": SQL("+").join(acc)}}])
def to_sql(self, schema, not_null=False, boolean=False): v = self.value.to_sql(schema)[0].sql return wrap([{ "name": ".", "sql": { "n": "UNIX_TIMESTAMP" + sql_iso(v.n) } }])
def _window_op(self, query, window): # http://www2.sqlite.org/cvstrac/wiki?p=UnsupportedSqlAnalyticalFunctions if window.value == "rownum": return ( "ROW_NUMBER()-1 OVER (" + " PARTITION BY " + sql_iso(sql_list(window.edges.values)) + SQL_ORDERBY + sql_iso(sql_list(window.edges.sort)) + ") AS " + quote_column(window.name) ) range_min = text(coalesce(window.range.min, "UNBOUNDED")) range_max = text(coalesce(window.range.max, "UNBOUNDED")) return ( sql_aggs[window.aggregate] + sql_iso(window.value.to_sql(schema)) + " OVER (" + " PARTITION BY " + sql_iso(sql_list(window.edges.values)) + SQL_ORDERBY + sql_iso(sql_list(window.edges.sort)) + " ROWS BETWEEN " + range_min + " PRECEDING AND " + range_max + " FOLLOWING " + ") AS " + quote_column(window.name) )
def to_sql(self, schema, not_null=False, boolean=False): field = self.field.to_sql(schema)[0].sql acc = [] for t, v in field.items(): if t in "bns": acc.append(sql_iso(v + SQL_IS_NOT_NULL)) if not acc: return wrap([{"name": ".", "sql": {"b": SQL_FALSE}}]) else: return wrap([{"name": ".", "sql": {"b": SQL_OR.join(acc)}}])
def to_sql(self, schema, not_null=False, boolean=False): terms = [ SQLang[t].partial_eval().to_sql(schema)[0].sql.n for t in self.terms ] return wrap([{ "name": ".", "sql": { "n": "min" + sql_iso((sql_list(terms))) } }])
def to_sql(self, schema, not_null=False, boolean=False): lhs = SQLang[self.lhs].to_sql(schema) rhs = SQLang[self.rhs].to_sql(schema) acc = [] if len(lhs) != len(rhs): Log.error("lhs and rhs have different dimensionality!?") for l, r in zip(lhs, rhs): for t in "bsnj": if l.sql[t] == None: if r.sql[t] == None: pass else: acc.append(sql_iso(r.sql[t]) + SQL_IS_NULL) elif l.sql[t] is ZERO: if r.sql[t] == None: acc.append(SQL_FALSE) elif r.sql[t] is ZERO: Log.error( "Expecting expression to have been simplified already" ) else: acc.append(r.sql[t]) else: if r.sql[t] == None: acc.append(sql_iso(l.sql[t]) + SQL_IS_NULL) elif r.sql[t] is ZERO: acc.append(l.sql[t]) else: acc.append( sql_iso(l.sql[t]) + SQL_EQ + sql_iso(r.sql[t])) if not acc: return FALSE.to_sql(schema) else: return SQLScript( expr=SQL_OR.join(acc), frum=self, data_type=BOOLEAN, miss=FALSE, schema=schema, )
def to_sql(self, schema, not_null=False, boolean=False): not_expr = NotOp(BooleanOp(self.term)).partial_eval() if is_op(not_expr, NotOp): return wrap([{ "name": ".", "sql": { "b": "NOT " + sql_iso(not_expr.term.to_sql(schema)[0].sql.b) }, }]) else: return not_expr.to_sql(schema)
def to_sql(self, schema, not_null=False, boolean=False): if not is_op(self.superset, Literal): Log.error("Not supported") j_value = json2value(self.superset.json) if j_value: var = SQLang[self.value].to_sql(schema) sql = SQL_OR.join( sql_iso(v, SQL_IN, quote_list(j_value)) for t, v in var[0].sql.items()) else: sql = SQL_FALSE return wrap([{"name": ".", "sql": {"b": sql}}])
def _binaryop_to_sql(self, schema, not_null=False, boolean=False, many=True): op, identity = _sql_operators[self.op] lhs = NumberOp(self.lhs).partial_eval().to_sql(schema, not_null=True)[0].sql.n rhs = NumberOp(self.rhs).partial_eval().to_sql(schema, not_null=True)[0].sql.n script = sql_iso(lhs) + op + sql_iso(rhs) if not_null: sql = script else: missing = OrOp([self.lhs.missing(), self.rhs.missing()]).partial_eval() if missing is FALSE: sql = script else: sql = ( "CASE WHEN " + missing.to_sql(schema, boolean=True)[0].sql.b + " THEN NULL ELSE " + script + " END" ) return wrap([{"name": ".", "sql": {"n": sql}}])
def to_sql(self, schema, not_null=False, boolean=False): test = SQLang[self.term].missing().to_sql(schema, boolean=True)[0].sql.b value = SQLang[self.term].to_sql(schema, not_null=True)[0].sql acc = [] for t, v in value.items(): if t == "b": acc.append(SQL_CASE + SQL_WHEN + sql_iso(test) + SQL_THEN + SQL_NULL + SQL_WHEN + sql_iso(v) + SQL_THEN + "'true'" + SQL_ELSE + "'false'" + SQL_END) elif t == "s": acc.append(v) else: acc.append("RTRIM(RTRIM(CAST" + sql_iso(v + " as TEXT), " + quote_value("0")) + ", " + quote_value(".") + ")") if not acc: return wrap([{}]) elif len(acc) == 1: return wrap([{"name": ".", "sql": {"s": acc[0]}}]) else: return wrap([{"name": ".", "sql": {"s": sql_coalesce(acc)}}])
def to_sql(self, schema, not_null=False, boolean=False): term = SQLang[self.term].partial_eval() if is_literal(term): val = term.value if isinstance(val, text): sql = quote_value(len(val)) elif isinstance(val, (float, int)): sql = quote_value(len(value2json(val))) else: return Null else: value = term.to_sql(schema, not_null=not_null)[0].sql.s sql = ConcatSQL(SQL("LENGTH"), sql_iso(value)) return wrap([{"name": ".", "sql": {"n": sql}}])
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 to_sql(self, schema, not_null=False, boolean=False): lhs = SQLang[self.lhs].to_sql(schema)[0].sql.n rhs = SQLang[self.rhs].to_sql(schema)[0].sql.n d = SQLang[self.default].to_sql(schema)[0].sql.n if lhs and rhs: if d == None: return wrap([{ "name": ".", "sql": { "n": sql_iso(lhs) + " / " + sql_iso(rhs) } }]) else: return wrap([{ "name": ".", "sql": { "n": sql_coalesce([sql_iso(lhs) + " / " + sql_iso(rhs), d]) }, }]) else: return Null
def to_sql(self, schema, not_null=False, boolean=False): lhs = SQLang[self.lhs].to_sql(schema)[0].sql.n rhs = SQLang[self.rhs].to_sql(schema)[0].sql.n modifier = lhs + " < 0 " if text(rhs).strip() != "1": floor = "CAST" + sql_iso(lhs + "/" + rhs + " AS INTEGER") sql = sql_iso(sql_iso(floor) + "-" + sql_iso(modifier)) + "*" + rhs else: floor = "CAST" + sql_iso(lhs + " AS INTEGER") sql = sql_iso(floor) + "-" + sql_iso(modifier) return wrap([{"name": ".", "sql": {"n": sql}}])
def _insert(self, collection): for nested_path, details in collection.items(): active_columns = wrap(list(details.active_columns)) rows = details.rows num_rows = len(rows) table_name = concat_field(self.name, nested_path) if table_name == self.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 # ONLY THE PRIMITIVE VALUE COLUMNS command = ConcatSQL( SQL_INSERT, quote_column(table_name), sql_iso(sql_list(map(quote_column, all_columns))), SQL_VALUES, sql_list( sql_iso( 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(command)
def _make_range_domain(self, domain, column_name): width = (domain.max - domain.min) / domain.interval digits = mo_math.floor(mo_math.log10(width - 1)) if digits == 0: value = quote_column("a", "value") else: value = SQL_PLUS.join("1" + ("0" * j) + SQL_STAR + text(chr(ord(b'a') + j)) + ".value" for j in range(digits + 1)) if domain.interval == 1: if domain.min == 0: domain = ( SQL_SELECT + sql_alias(value, column_name) + SQL_FROM + sql_alias(quote_column(DIGITS_TABLE), "a") ) else: domain = ( SQL_SELECT + sql_alias(sql_iso(value) + SQL_PLUS + quote_value(domain.min), column_name) + SQL_FROM + sql_alias(quote_column(DIGITS_TABLE), "a") ) else: if domain.min == 0: domain = ConcatSQL( SQL_SELECT, sql_alias(value + SQL_STAR + quote_value(domain.interval), column_name), SQL_FROM, sql_alias(quote_column(DIGITS_TABLE), "a") ) else: domain = ConcatSQL( SQL_SELECT, sql_alias( sql_iso(value, SQL_STAR, quote_value(domain.interval)) + SQL_PLUS + quote_value(domain.min), column_name), SQL_FROM, sql_alias(quote_column(DIGITS_TABLE), "a") ) for j in range(digits): domain += SQL_INNER_JOIN + sql_alias(quote_column(DIGITS_TABLE), text(chr(ord(b'a') + j + 1))) + SQL_ON + SQL_TRUE domain += SQL_WHERE + value + " < " + quote_value(width) return domain
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): 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(SQLang[t].to_sql(schema, boolean=True)[0].sql.b) for t in self.terms ]) }, }]) else: return wrap([{"name": ".", "sql": {"b": SQL_FALSE}}])
def sql(self): self.miss = self.miss.partial_eval() if self.miss is TRUE: return wrap({json_type_to_sql_type[self.data_type]: SQL_NULL}) elif self.miss is FALSE: return wrap({json_type_to_sql_type[self.data_type]: self.expr}) else: return wrap( { json_type_to_sql_type[self.data_type]: ConcatSQL( SQL_CASE, SQL_WHEN, SQL_NOT, sql_iso(SQLang[self.miss].to_sql(self.schema)[0].sql.b), SQL_THEN, self.expr, SQL_END, ) } )
def to_sql(self, schema, not_null=False, boolean=False, many=False): return sql_iso(SQLang[self.rhs].to_sql(schema)) + SQL_EQ + sql_iso( +SQLang[self.lhs].to_sql(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
def basic_multiop_to_sql(self, schema, not_null=False, boolean=False, many=False): op, identity = _sql_operators[self.op.split("basic.")[1]] sql = op.join(sql_iso(SQLang[t].to_sql(schema)[0].sql.n) for t in self.terms) return wrap([{"name": ".", "sql": {"n": sql}}])
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)
def _set_op(self, query): # GET LIST OF SELECTED COLUMNS vars_ = UNION([ v.var for select in listwrap(query.select) for v in select.value.vars() ]) schema = self.schema known_vars = schema.keys() active_columns = {".": set()} for v in vars_: for c in schema.leaves(v): nest = c.nested_path[0] active_columns.setdefault(nest, set()).add(c) # ANY VARS MENTIONED WITH NO COLUMNS? for v in vars_: if not any(startswith_field(cname, v) for cname in known_vars): active_columns["."].add( Column(name=v, jx_type=IS_NULL, es_column=".", es_index=".", es_type='NULL', nested_path=["."], last_updated=Date.now())) # EVERY COLUMN, AND THE INDEX IT TAKES UP index_to_column = {} # MAP FROM INDEX TO COLUMN (OR SELECT CLAUSE) index_to_uid = {} # FROM NESTED PATH TO THE INDEX OF UID sql_selects = [ ] # EVERY SELECT CLAUSE (NOT TO BE USED ON ALL TABLES, OF COURSE) nest_to_alias = { nested_path[0]: "__" + unichr(ord('a') + i) + "__" for i, nested_path in enumerate(self.snowflake.query_paths) } sorts = [] if query.sort: for select in query.sort: col = SQLang[select.value].to_sql(schema)[0] for t, sql in col.sql.items(): json_type = sql_type_to_json_type[t] if json_type in STRUCT: continue column_number = len(sql_selects) # SQL HAS ABS TABLE REFERENCE column_alias = _make_column_name(column_number) sql_selects.append(sql_alias(sql, column_alias)) if select.sort == -1: sorts.append(quote_column(column_alias) + SQL_IS_NULL) sorts.append(quote_column(column_alias) + " DESC") else: sorts.append(quote_column(column_alias) + SQL_IS_NULL) sorts.append(quote_column(column_alias)) primary_doc_details = Data() # EVERY SELECT STATEMENT THAT WILL BE REQUIRED, NO MATTER THE DEPTH # WE WILL CREATE THEM ACCORDING TO THE DEPTH REQUIRED nested_path = [] for step, sub_table in self.snowflake.tables: nested_path.insert(0, step) nested_doc_details = { "sub_table": sub_table, "children": [], "index_to_column": {}, "nested_path": nested_path } # INSERT INTO TREE if not primary_doc_details: primary_doc_details = nested_doc_details else: def place(parent_doc_details): if startswith_field(step, parent_doc_details['nested_path'][0]): for c in parent_doc_details['children']: if place(c): return True parent_doc_details['children'].append( nested_doc_details) place(primary_doc_details) alias = nested_doc_details['alias'] = nest_to_alias[step] # WE ALWAYS ADD THE UID column_number = index_to_uid[step] = nested_doc_details[ 'id_coord'] = len(sql_selects) sql_select = quote_column(alias, UID) sql_selects.append( sql_alias(sql_select, _make_column_name(column_number))) if step != ".": # ID AND ORDER FOR CHILD TABLES index_to_column[column_number] = ColumnMapping( sql=sql_select, type="number", nested_path=nested_path, column_alias=_make_column_name(column_number)) column_number = len(sql_selects) sql_select = quote_column(alias, ORDER) sql_selects.append( sql_alias(sql_select, _make_column_name(column_number))) index_to_column[column_number] = ColumnMapping( sql=sql_select, type="number", nested_path=nested_path, column_alias=_make_column_name(column_number)) # WE DO NOT NEED DATA FROM TABLES WE REQUEST NOTHING FROM if step not in active_columns: continue # ADD SQL SELECT COLUMNS FOR EACH jx SELECT CLAUSE si = 0 for select in listwrap(query.select): try: column_number = len(sql_selects) select.pull = get_column(column_number) db_columns = SQLang[select.value].partial_eval().to_sql( schema) for column in db_columns: for t, unsorted_sql in column.sql.items(): json_type = sql_type_to_json_type[t] if json_type in STRUCT: continue column_number = len(sql_selects) column_alias = _make_column_name(column_number) sql_selects.append( sql_alias(unsorted_sql, column_alias)) if startswith_field(schema.path, step) and is_op( select.value, LeavesOp): # ONLY FLATTEN primary_nested_path AND PARENTS, NOT CHILDREN index_to_column[ column_number] = nested_doc_details[ 'index_to_column'][ column_number] = ColumnMapping( push_name=literal_field( get_property_name( concat_field( select.name, column.name))), push_child=".", push_column_name= get_property_name( concat_field( select.name, column.name)), push_column=si, pull=get_column(column_number), sql=unsorted_sql, type=json_type, column_alias=column_alias, nested_path=nested_path) si += 1 else: index_to_column[ column_number] = nested_doc_details[ 'index_to_column'][ column_number] = ColumnMapping( push_name=select.name, push_child=column.name, push_column_name=select.name, push_column=si, pull=get_column(column_number), sql=unsorted_sql, type=json_type, column_alias=column_alias, nested_path=nested_path) finally: si += 1 where_clause = BooleanOp(query.where).partial_eval().to_sql( schema, boolean=True)[0].sql.b unsorted_sql = self._make_sql_for_one_nest_in_set_op( ".", sql_selects, where_clause, active_columns, index_to_column) for n, _ in self.snowflake.tables: sorts.append(quote_column(COLUMN + text(index_to_uid[n]))) ordered_sql = ConcatSQL(SQL_SELECT, SQL_STAR, SQL_FROM, sql_iso(unsorted_sql), SQL_ORDERBY, sql_list(sorts), SQL_LIMIT, quote_value(query.limit)) result = self.db.query(ordered_sql) def _accumulate_nested(rows, row, nested_doc_details, parent_doc_id, parent_id_coord): """ :param rows: REVERSED STACK OF ROWS (WITH push() AND pop()) :param row: CURRENT ROW BEING EXTRACTED :param nested_doc_details: { "nested_path": wrap_nested_path(nested_path), "index_to_column": map from column number to column details "children": all possible direct decedents' nested_doc_details } :param parent_doc_id: the id of the parent doc (for detecting when to step out of loop) :param parent_id_coord: the column number for the parent id (so we ca extract from each row) :return: the nested property (usually an array) """ previous_doc_id = None doc = Null output = [] id_coord = nested_doc_details['id_coord'] while True: doc_id = row[id_coord] if doc_id == None or (parent_id_coord is not None and row[parent_id_coord] != parent_doc_id): rows.append( row ) # UNDO PREVIOUS POP (RECORD IS NOT A NESTED RECORD OF parent_doc) return output if doc_id != previous_doc_id: previous_doc_id = doc_id doc = Null curr_nested_path = nested_doc_details['nested_path'][0] index_to_column = nested_doc_details[ 'index_to_column'].items() for i, c in index_to_column: value = row[i] if is_list(query.select) or is_op( query.select.value, LeavesOp): # ASSIGN INNER PROPERTIES relative_field = concat_field( c.push_name, c.push_child) else: # FACT IS EXPECTED TO BE A SINGLE VALUE, NOT AN OBJECT relative_field = c.push_child if relative_field == ".": if exists(value): doc = value elif exists(value): if doc is Null: doc = Data() doc[relative_field] = value for child_details in nested_doc_details['children']: # EACH NESTED TABLE MUST BE ASSEMBLED INTO A LIST OF OBJECTS child_id = row[child_details['id_coord']] if child_id is not None: nested_value = _accumulate_nested( rows, row, child_details, doc_id, id_coord) if nested_value != None: push_name = child_details['nested_path'][0] if is_list(query.select) or is_op( query.select.value, LeavesOp): # ASSIGN INNER PROPERTIES relative_field = relative_field( push_name, curr_nested_path) else: # FACT IS EXPECTED TO BE A SINGLE VALUE, NOT AN OBJECT relative_field = "." if relative_field == ".": doc = unwraplist(nested_value) else: doc[relative_field] = unwraplist(nested_value) output.append(doc) try: row = rows.pop() except IndexError: return output cols = tuple( [i for i in index_to_column.values() if i.push_name != None]) rows = list(reversed(unwrap(result.data))) if rows: row = rows.pop() data = _accumulate_nested(rows, row, primary_doc_details, None, None) else: data = result.data if query.format == "cube": # for f, full_name in self.snowflake.tables: # if f != '.' or (test_dots(cols) and is_list(query.select)): # num_rows = len(result.data) # num_cols = MAX([c.push_column for c in cols]) + 1 if len(cols) else 0 # map_index_to_name = {c.push_column: c.push_column_name for c in cols} # temp_data = [[None] * num_rows for _ in range(num_cols)] # for rownum, d in enumerate(result.data): # for c in cols: # if c.push_child == ".": # temp_data[c.push_column][rownum] = c.pull(d) # else: # column = temp_data[c.push_column][rownum] # if column is None: # column = temp_data[c.push_column][rownum] = {} # column[c.push_child] = c.pull(d) # output = Data( # meta={"format": "cube"}, # data={n: temp_data[c] for c, n in map_index_to_name.items()}, # edges=[{ # "name": "rownum", # "domain": { # "type": "rownum", # "min": 0, # "max": num_rows, # "interval": 1 # } # }] # ) # return output if is_list(query.select) or is_op(query.select.value, LeavesOp): num_rows = len(data) temp_data = { c.push_column_name: [None] * num_rows for c in cols } for rownum, d in enumerate(data): for c in cols: temp_data[c.push_column_name][rownum] = d[c.push_name] return Data(meta={"format": "cube"}, data=temp_data, edges=[{ "name": "rownum", "domain": { "type": "rownum", "min": 0, "max": num_rows, "interval": 1 } }]) else: num_rows = len(data) map_index_to_name = { c.push_column: c.push_column_name for c in cols } temp_data = [data] return Data(meta={"format": "cube"}, data={ n: temp_data[c] for c, n in map_index_to_name.items() }, edges=[{ "name": "rownum", "domain": { "type": "rownum", "min": 0, "max": num_rows, "interval": 1 } }]) elif query.format == "table": # for f, _ in self.snowflake.tables: # if frum.endswith(f): # num_column = MAX([c.push_column for c in cols]) + 1 # header = [None] * num_column # for c in cols: # header[c.push_column] = c.push_column_name # # output_data = [] # for d in result.data: # row = [None] * num_column # for c in cols: # set_column(row, c.push_column, c.push_child, c.pull(d)) # output_data.append(row) # # return Data( # meta={"format": "table"}, # header=header, # data=output_data # ) if is_list(query.select) or is_op(query.select.value, LeavesOp): column_names = [None] * (max(c.push_column for c in cols) + 1) for c in cols: column_names[c.push_column] = c.push_column_name temp_data = [] for rownum, d in enumerate(data): row = [None] * len(column_names) for c in cols: row[c.push_column] = d[c.push_name] temp_data.append(row) return Data(meta={"format": "table"}, header=column_names, data=temp_data) else: column_names = listwrap(query.select).name return Data(meta={"format": "table"}, header=column_names, data=[[d] for d in data]) else: # for f, _ in self.snowflake.tables: # if frum.endswith(f) or (test_dots(cols) and is_list(query.select)): # data = [] # for d in result.data: # row = Data() # for c in cols: # if c.push_child == ".": # row[c.push_name] = c.pull(d) # elif c.num_push_columns: # tuple_value = row[c.push_name] # if not tuple_value: # tuple_value = row[c.push_name] = [None] * c.num_push_columns # tuple_value[c.push_child] = c.pull(d) # else: # row[c.push_name][c.push_child] = c.pull(d) # # data.append(row) # # return Data( # meta={"format": "list"}, # data=data # ) if is_list(query.select) or is_op(query.select.value, LeavesOp): temp_data = [] for rownum, d in enumerate(data): row = {} for c in cols: row[c.push_column_name] = d[c.push_name] temp_data.append(row) return Data(meta={"format": "list"}, data=temp_data) else: return Data(meta={"format": "list"}, data=data)
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.append(SQL_FROM) from_clause.append( sql_alias(quote_column(self.snowflake.fact_name), alias)) else: from_clause.append(SQL_LEFT_JOIN) from_clause.append( sql_alias( quote_column(self.snowflake.fact_name, sub_table.name), alias)) from_clause.append(SQL_ON) from_clause.append(quote_column(alias, PARENT)) from_clause.append(SQL_EQ) from_clause.append(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.append(SQL_FROM) from_clause.append( sql_alias(quote_column(self.snowflake.fact_name), alias)) else: parent_alias = alias = unichr(ord('a') + i - 1) from_clause.append(SQL_LEFT_JOIN) from_clause.append( sql_alias( quote_column(self.snowflake.fact_name, sub_table.name), alias)) from_clause.append(SQL_ON) from_clause.append(quote_column(alias, PARENT)) from_clause.append(SQL_EQ) from_clause.append(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.append(SQL_LEFT_JOIN) from_clause.append( sql_alias( quote_column(self.snowflake.fact_name, sub_table.name), alias)) from_clause.append(SQL_ON) from_clause.append(quote_column(alias, PARENT)) from_clause.append(SQL_EQ) from_clause.append(quote_column(parent_alias, UID)) from_clause.append(SQL_AND) from_clause.append(quote_column(alias, ORDER)) from_clause.append(SQL_EQ) from_clause.append(SQL_ZERO) # 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([ ConcatSQL(SQL_SELECT, sql_list(select_clause), ConcatSQL(*from_clause), SQL_WHERE, where_clause) ], *children_sql) return sql