def test_select_fq():
test_schema = Schema("test")
dummy_table = Table(test_schema, "dummy")
columns = [Column(dummy_table, "id"), Column(dummy_table, "name")]
query = Select(columns, from_=[dummy_table])
sql = query.render()
eq_(sql, "SELECT test.dummy.id, test.dummy.name FROM test.dummy")
def test_select_with_where_arg():
dummy_table = Table("dummy")
columns = [Column("id"), Column("name")]
query = Select(columns, from_=[dummy_table], where_=Eq(Column("id"), Argument()))
sql = query.render()
eq_(sql, "SELECT id, name FROM dummy WHERE id = %s")
def test_select():
dummy_table = Table("dummy")
columns = [Column("id"), Column("name")]
query = Select(columns, from_=[dummy_table])
sql = query.render()
eq_(sql, "SELECT id, name FROM dummy")
def test_select_with_alias():
dummy_table = Table("dummy")
table_alias = As(dummy_table, "d")
columns = [Column(table_alias, "id"), Column(table_alias, "name")]
query = Select(columns, from_=[table_alias])
sql = query.render()
eq_(sql, "SELECT d.id, d.name FROM dummy AS d")
def test_arguments():
dummy_table = Table("dummy")
col_id = Column("id")
col_name = Column("name")
columns = [col_id, col_name]
query = Select(columns).from_([dummy_table]).where_(Eq(col_name, Argument())).group_by_([col_name])
arguments = query.arguments()
eq_(len(arguments), 1)
def test_extract_references():
dummy_table = Table("dummy")
col_id = Column("id")
col_name = Column("name")
columns = [col_id, col_name]
query = Select(columns).from_([dummy_table]).where_(Eq(col_name, Argument())).group_by_([col_name])
references = query.references()
eq_(len(references), 4)
def test_select_chained():
dummy_table = Table("dummy")
col_id = Column("id")
col_name = Column("name")
columns = [col_id, col_name]
query = Select(columns).from_([dummy_table]).where_(Eq(col_name, Argument())).group_by_([col_name])
sql = query.render()
eq_(sql, "SELECT id, name FROM dummy WHERE name = %s GROUP BY name")
def test_select_with_group_by():
dummy_table = Table("dummy")
col_id = Column("id")
col_name = Column("name")
columns = [col_id, col_name]
query = Select(columns, from_=[dummy_table], where_=Eq(col_name, Argument()), group_by_=[col_name])
sql = query.render()
eq_(sql, "SELECT id, name FROM dummy WHERE name = %s GROUP BY name")
def add_job(cursor, *args):
table = Table("system", "job")
col_names = "id", "type", "description", "size", "created", "started", "finished", "success", "job_source_id", "state"
columns = map(Column, col_names)
column_id = columns[0]
select = Select(1, from_=table, where_=Eq(column_id))
select.execute(cursor, (args[0], ))
if cursor.rowcount == 0:
table.insert(columns).execute(cursor, args)
def add_job_source(cursor, *args):
table = Table("system", "job_source")
col_names = "id", "name", "job_type", "config"
columns = map(Column, col_names)
column_id = columns[0]
select = Select(1, from_=table, where_=Eq(column_id))
select.execute(cursor, (args[0], ))
if cursor.rowcount == 0:
table.insert(columns).execute(cursor, args)
def table_or_view_exists(cursor, table):
relkind_column = Column("relkind")
criterion = And(
Eq(Column("relname")),
Parenthesis(Or(Eq(relkind_column), Eq(relkind_column))))
query = Select(1, from_=Table("pg_class"), where_=criterion)
args = table.name, "r", "v"
query.execute(cursor, args)
return cursor.rowcount > 0
def test_filter_tables():
dummy_table = Table("dummy")
col_id = Column("id")
col_name = Column("name")
columns = [col_id, col_name]
query = Select(columns).from_([dummy_table]).where_(Eq(col_name, Argument())).group_by_([col_name])
tables = filter_tables(query.references())
eq_(len(tables), 1)
eq_(tables[0].name, "dummy")
def test_select_with_left_join():
dummy_table_a = Table("dummy_a")
col_id_a = Column(dummy_table_a, "id")
col_name = Column(dummy_table_a, "name")
col_ref = Column(dummy_table_a, "ref_b")
dummy_table_b = Table("dummy_b")
col_id_b = Column(dummy_table_b, "id")
col_amount = Column(dummy_table_b, "amount")
columns = [col_name, col_amount]
from_part = FromItem(dummy_table_a).left_join(dummy_table_b, Eq(col_id_a, col_id_b))
query = Select(columns).from_(from_part)
sql = query.render()
eq_(sql, "SELECT dummy_a.name, dummy_b.amount FROM dummy_a LEFT JOIN dummy_b ON dummy_a.id = dummy_b.id")
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
