def f(cursor): base_table = self.base_table() for column_name, data_type in zip(column_names, data_types): if not column_exists(cursor, base_table, column_name): create_column(cursor, base_table, column_name, data_type) assure_trendstore_trend_link(cursor, self, column_name)
def retrieve_aggregated(cursor, trendstore, column_identifiers, interval, group_by=[], subquery_filter=None, relation_table_name=None): """ Return aggregated data :param cursor: psycopg2 database cursor :param trendstore: TrendStore object :param column_identifiers: e.g. SUM(trend1), MAX(trend2) :param interval: (start, end) tuple with non-naive timestamps :param group_by: list of columns to GROUP BY :param subquery_filter: optional subquery for additional filtering by JOINing on field 'id' = entity_id :param relation_table_name: optional relation table name for converting entity ids to related ones """ start, end = interval source_tables = trendstore.tables(start, end) def get_trend_names(column_identifier): if isinstance(column_identifier, Sql): if isinstance(column_identifier, Column): return [column_identifier.name] else: return [a.name for a in column_identifier.args] else: trend_names_part = re.match(r".*\(([\w, ]+)\)", column_identifier).group(1) return map(str.strip, trend_names_part.split(",")) trend_names = set(chain(*map(get_trend_names, column_identifiers))) args = {"start": start, "end": end} #Deal with 'samples' column if column_exists(cursor, source_tables[-1], "samples"): select_samples_part = "SUM(samples)" select_samples_column = "samples," else: select_samples_part = "COUNT(*)" select_samples_column = "" existing_tables = [table for table in source_tables if table_or_view_exists(cursor, table)] if not existing_tables: return [] def create_select_statement(source_table): join_parts = [] if subquery_filter: join_part = ( "JOIN ({0}) AS filter " "ON filter.id = {1}.entity_id").format( subquery_filter, source_table.render()) join_parts.append(join_part) if relation_table_name: relation_table = Table("relation", relation_table_name) return_id_field = "r.target_id AS entity_id" join_part = ( "LEFT JOIN {0} r " "ON r.source_id = {1}.entity_id").format( relation_table.render(), source_table.render()) join_parts.append(join_part) else: return_id_field = "entity_id" return ( "SELECT {0}, %(end)s, {1} {2} " "FROM {3} {4} " "WHERE timestamp > %(start)s AND timestamp <= %(end)s").format( return_id_field, select_samples_column, ",".join(map(enquote_column_name, trend_names)), source_table.render(), " ".join(join_parts)) select_parts = map(create_select_statement, existing_tables) query = ( "SELECT entity_id, %(end)s, {0}, {1} " "FROM ({2}) AS sources " "GROUP BY {3}").format( select_samples_part, ",".join(map(quote_ident, column_identifiers)), " UNION ALL ".join(select_parts), ",".join(map(enquote_column_name, group_by))) try: cursor.execute(query, args) except psycopg2.ProgrammingError as exc: raise AggregationError("{} with query: {}".format(str(exc), cursor.mogrify(query, args))) else: return cursor.fetchall()