예제 #1
0
    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
예제 #2
0
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
예제 #3
0
def test_ands():
    query_part = ands([Value(True), Value(True), Value(False)])

    sql = query_part.render()

    eq_(sql, "true AND true AND false")