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
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 _aggop(self, query): """ SINGLE ROW RETURNED WITH AGGREGATES """ if isinstance(query.select, list): # RETURN SINGLE OBJECT WITH AGGREGATES for s in query.select: if s.aggregate not in aggregates: Log.error( "Expecting all columns to have an aggregate: {{select}}", select=s) selects = FlatList() for s in query.select: selects.append( sql_alias( aggregates[s.aggregate].replace("{{code}}", s.value), quote_column(s.name))) sql = expand_template( """ SELECT {{selects}} FROM {{table}} {{where}} """, { "selects": SQL(",\n".join(selects)), "table": self._subquery(query["from"])[0], "where": self._where2sql(query.filter) }) return sql, lambda sql: self.db.column(sql)[ 0] # RETURNING SINGLE OBJECT WITH AGGREGATE VALUES else: # RETURN SINGLE VALUE s0 = query.select if s0.aggregate not in aggregates: Log.error( "Expecting all columns to have an aggregate: {{select}}", select=s0) select = sql_alias( aggregates[s0.aggregate].replace("{{code}}", s0.value), quote_column(s0.name)) sql = expand_template( """ SELECT {{selects}} FROM {{table}} {{where}} """, { "selects": SQL(select), "table": self._subquery(query["from"])[0], "where": self._where2sql(query.where) }) def post(sql): result = self.db.column_query(sql) return result[0][0] return sql, post # RETURN SINGLE VALUE
def _setop(self, query): """ NO AGGREGATION, SIMPLE LIST COMPREHENSION """ if isinstance(query.select, list): # RETURN BORING RESULT SET selects = FlatList() for s in listwrap(query.select): if isinstance(s.value, Mapping): for k, v in s.value.items: selects.append( sql_alias(v, quote_column(s.name + "." + k))) if isinstance(s.value, list): for i, ss in enumerate(s.value): selects.append( sql_alias(s.value, quote_column(s.name + "," + str(i)))) else: selects.append(sql_alias(s.value, quote_column(s.name))) sql = expand_template( """ SELECT {{selects}} FROM {{table}} {{where}} {{sort}} {{limit}} """, { "selects": SQL(",\n".join(selects)), "table": self._subquery(query["from"])[0], "where": self._where2sql(query.where), "limit": self._limit2sql(query.limit), "sort": self._sort2sql(query.sort) }) def post_process(sql): result = self.db.query(sql) for s in listwrap(query.select): if isinstance(s.value, Mapping): for r in result: r[s.name] = {} for k, v in s.value: r[s.name][k] = r[s.name + "." + k] r[s.name + "." + k] = None if isinstance(s.value, list): # REWRITE AS TUPLE for r in result: r[s.name] = tuple(r[s.name + "," + str(i)] for i, ss in enumerate(s.value)) for i, ss in enumerate(s.value): r[s.name + "," + str(i)] = None expand_json(result) return result return sql, post_process # RETURN BORING RESULT SET else: # RETURN LIST OF VALUES if query.select.value == ".": select = "*" else: name = query.select.name select = sql_alias(query.select.value, quote_column(name)) sql = expand_template( """ SELECT {{selects}} FROM {{table}} {{where}} {{sort}} {{limit}} """, { "selects": SQL(select), "table": self._subquery(query["from"])[0], "where": self._where2sql(query.where), "limit": self._limit2sql(query.limit), "sort": self._sort2sql(query.sort) }) if query.select.value == ".": def post(sql): result = self.db.query(sql) expand_json(result) return result return sql, post else: return sql, lambda sql: [r[name] for r in self.db.query(sql) ] # RETURNING LIST OF VALUES
def _grouped(self, query, stacked=False): select = listwrap(query.select) # RETURN SINGLE OBJECT WITH AGGREGATES for s in select: if s.aggregate not in aggregates: Log.error( "Expecting all columns to have an aggregate: {{select}}", select=s) selects = FlatList() groups = FlatList() edges = query.edges for e in edges: if e.domain.type != "default": Log.error("domain of type {{type}} not supported, yet", type=e.domain.type) groups.append(e.value) selects.append(sql_alias(e.value, quote_column(e.name))) for s in select: selects.append( sql_alias(aggregates[s.aggregate].replace("{{code}}", s.value), quote_column(s.name))) sql = expand_template( """ SELECT {{selects}} FROM {{table}} {{where}} GROUP BY {{groups}} """, { "selects": SQL(",\n".join(selects)), "groups": SQL(",\n".join(groups)), "table": self._subquery(query["from"])[0], "where": self._where2sql(query.where) }) def post_stacked(sql): # RETURN IN THE USUAL DATABASE RESULT SET FORMAT return self.db.query(sql) def post(sql): # FIND OUT THE default DOMAIN SIZES result = self.db.column_query(sql) num_edges = len(edges) for e, edge in enumerate(edges): domain = edge.domain if domain.type == "default": domain.type = "set" parts = set(result[e]) domain.partitions = [{ "index": i, "value": p } for i, p in enumerate(parts)] domain.map = {p: i for i, p in enumerate(parts)} else: Log.error("Do not know what to do here, yet") # FILL THE DATA CUBE maps = [(unwrap(e.domain.map), result[i]) for i, e in enumerate(edges)] cubes = FlatList() for c, s in enumerate(select): data = Matrix(*[ len(e.domain.partitions) + (1 if e.allow_nulls else 0) for e in edges ]) for rownum, value in enumerate(result[c + num_edges]): coord = [m[r[rownum]] for m, r in maps] data[coord] = value cubes.append(data) if isinstance(query.select, list): return cubes else: return cubes[0] return sql, post if not stacked else post_stacked