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()
