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 = ConcatSQL( SQL_SELECT, SQL_STAR, SQL_FROM, sql_alias(sql_iso(union_all_sql), "a"), SQL_ORDERBY, sql_list(sort), ) if DEBUG: Log.note("{{sql}}", sql=union_all_sql) return union_all_sql
push_child=push_child, # CAN NOT HANDLE TUPLES IN COLUMN pull=pull, sql=sql, type=sql_type_to_json_type[sql_type], column_alias=sql_name) vals = [v for t, v in edge_values] if query_edge.domain.type == "set": domain_name = "d" + text(edge_index) + "c" + text(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)), "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)), "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 +
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 _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