def test_column(): column = Column("name") eq_(column.render(), "name") eq_(type(column.as_("other")), As) r = column == 45 eq_(type(r), Eq) eq_(r.render(), "name = 45")
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