def compile(self):
args = []
criteria = []
from_item = None
for level, part in enumerate(self.parts):
if isinstance(part, Context):
entitytags_table = As(Table("directory", "entitytags"),
"etags_{}".format(level))
if not from_item:
from_item = FromItem(entitytags_table)
col_id = Column("id")
columns = [Call("array_agg", col_id)]
tag_name_criterion = Eq(Call("lower", Column("name")), Any())
subselect = Select(columns).from_(
[Table("directory", "tag")]).where_(tag_name_criterion)
criterion = ArrayContains(
Column(entitytags_table.alias, "tag_ids"),
Parenthesis(subselect))
entity_id_column = Column(entitytags_table, "entity_id")
criteria.append(criterion)
tag_names = map(str.lower, part.tag_names())
context_args = (tag_names,)
args.extend(context_args)
elif isinstance(part, Alias):
alias_table = As(Table("directory", "alias"),
"alias_{}".format(level))
specifier_criterion = Eq(Column(alias_table, "name"),
part.name)
entity_id_criterion = Eq(entity_id_column, Column(alias_table,
"entity_id"))
criterion = And(entity_id_criterion, specifier_criterion)
from_item = from_item.join(alias_table, criterion)
return Select([entity_id_column]).from_(from_item).where_(
ands(criteria)), args
def retrieve(cursor, tables, columns, entities, start, end,
subquery_filter=None, relation_table_name=None, limit=None, entitytype=None):
"""
Retrieve data.
:param cursor: Minerva database cursor
:param columns: A list of column identifiers (possibly for different
datasources)
:param entities: List of entity Ids
:param start: The start timestamp of the range of trend values
:param end: The end timestamp of the range of trend values
: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
"""
all_rows = []
if entities is not None and len(entities) == 0:
return []
columns = map(ensure_column, columns)
# group tables by partition size signature to be able to JOIN them later
tables_by_partition_signature = {}
for table in tables:
signature = table.name.split("_")[-1]
tables_by_partition_signature.setdefault(signature, []).append(table)
for tables in tables_by_partition_signature.values():
params = []
if start == end and start is not None and len(tables) > 1:
cols = [As(Argument(), "timestamp"), Column("dn"),
As(Column("id"), "entity_id")]
q = Select(cols,
from_=Table("directory", "entity"),
where_=Eq(Column("entitytype_id"), Value(entitytype.id)))
with_query = WithQuery("t", query=q)
params.append(start)
base_timestamp_column = Column("t", "timestamp")
base_entity_id_column = Column("t", "entity_id")
from_item = FromItem(Table("t"))
data_tables = tables
else:
with_query = None
base_tbl = first(tables)
base_timestamp_column = Column(base_tbl, "timestamp")
base_entity_id_column = Column(base_tbl, "entity_id")
from_item = FromItem(base_tbl)
data_tables = tables[1:]
for tbl in data_tables:
timestamp_comparison = Eq(Column(tbl, "timestamp"), base_timestamp_column)
entity_id_comparison = Eq(Column(tbl, "entity_id"), base_entity_id_column)
join_condition = And(timestamp_comparison, entity_id_comparison)
from_item = from_item.join(tbl, on=join_condition, join_type="LEFT")
if subquery_filter:
filter_tbl = Literal("({0}) AS filter".format(subquery_filter))
from_item = from_item.join(filter_tbl,
on=Eq(Column("filter", "id"), base_entity_id_column))
if relation_table_name:
relation_table = Table("relation", relation_table_name)
join_condition = Eq(Column("r", "source_id"), base_entity_id_column)
from_item = from_item.left_join(As(relation_table, "r"), on=join_condition)
entity_id_column = Column("r", "target_id")
else:
entity_id_column = base_entity_id_column
partition_columns = [entity_id_column, base_timestamp_column] + columns
where_parts = []
if not with_query:
if start == end and start is not None:
condition = Eq(base_timestamp_column, Argument())
where_parts.append(condition)
params.append(start)
else:
if not start is None:
condition = Gt(base_timestamp_column, Argument())
where_parts.append(condition)
params.append(start)
if not end is None:
condition = LtEq(base_timestamp_column, Argument())
where_parts.append(condition)
params.append(end)
if not entities is None:
condition = Literal("{0} IN ({1:s})".format(base_entity_id_column.render(),
",".join(str(entity_id) for entity_id in entities)))
where_parts.append(condition)
if where_parts:
where_clause = ands(where_parts)
else:
where_clause = None
select = Select(partition_columns, with_query=with_query, from_=from_item,
where_=where_clause, limit=limit)
query = select.render()
try:
cursor.execute(query, params)
except psycopg2.ProgrammingError as exc:
msg = "{} in query: {}".format(exc, cursor.mogrify(query, params))
raise Exception(msg)
else:
all_rows.extend(cursor.fetchall())
return all_rows
def test_ands():
query_part = ands([Value(True), Value(True), Value(False)])
sql = query_part.render()
eq_(sql, "true AND true AND false")