def test_selection_from_bedfile_and_subselection(conn):
"""Test the creation of a selection based on BED data
.. note:: Please note that the bedreader **is not** tested here!
"""
larger_string = """
chr1 1 10 feature1 0 +
chr1 50 60 feature2 0 -
chr1 51 59 another_feature 0 +
"""
# According to VARIANTS global variable with 3 variants (pos 10, 50 and 45)
# 1: chr1, pos 1 to 10 => 1 variant concerned (pos 10)
# 2: chr1, pos 50 to 60 => 1 variant concerned (pos 50)
# 3: chr1, pos 51 to 59 => 0 variants
bedtool = BedReader(larger_string)
# Create now a sub selection => 2 variants (pos 10, 45)
query = "SELECT variants.id,chr,pos,ref,alt FROM variants WHERE ref='G'"
set_A_id = sql.create_selection_from_sql(conn, query, "setA", count=None)
# 2nd selection (1st is the default "variants")
assert set_A_id == 2
assert "setA" in list(s["name"] for s in sql.get_selections(conn))
# 1: chr1, pos 1 to 10 => 1 remaining variant
# 2: chr1, pos 50 to 60 => 0 variant
# 3: chr1, pos 51 to 59 => 0 variant
ret = sql.create_selection_from_bed(conn, "setA", "sub_bedname", bedtool)
# id of selection
assert ret == 3
data = conn.execute(
"SELECT * FROM selection_has_variant WHERE selection_id = ?", (ret, ))
expected = ((1, ret), )
record = tuple([tuple(i) for i in data])
assert record == expected
def create_cmd(
conn: sqlite3.Connection,
target: str,
source="variants",
filters=dict(),
count=None,
**kwargs,
):
"""Create a selection from the given source, filtered by filters
This following VQL command:
`CREATE boby FROM variants WHERE pos > 3`
will execute :
`create_cmd(conn, "boby", "variants", {"AND":[{"pos",">",3}]})`
Args:
conn (sqlite3.Connection): sqlite3 connection
target (str): target selection name
source (str): source selection name
filters (dict): filters used to select the variants from source
count (int): precomputed variant count
Returns:
dict: {"id": selection_id} if lines have been inserted,
or empty dict in case of error
"""
if target is None:
return {}
sql_query = build_full_sql_query(
conn,
fields=["id"],
source=source,
filters=filters,
limit=None,
**kwargs,
)
LOGGER.debug("command:create_cmd:: %s", sql_query)
selection_id = sql.create_selection_from_sql(conn,
sql_query,
target,
count=count,
from_selection=False)
return dict() if selection_id is None else {"id": selection_id}
def test_selections(conn):
"""Test the creation of a full selection in "selection_has_variant"
and "selections" tables; Test also the ON CASCADE deletion of rows in
"selection_has_variant" when a selection is deleted.
"""
# Create a selection that contains all 8 variants in the DB
# (no filter on this list, via annotation table because this table is not
# initialized here)
query = """SELECT variants.id,chr,pos,ref,alt FROM variants
LEFT JOIN annotations
ON annotations.variant_id = variants.rowid"""
# Create a new selection (a second one, since there is a default one during DB creation)
ret = sql.create_selection_from_sql(conn,
query,
"selection_name",
count=None)
assert ret == 2
# Query the association table (variant_id, selection_id)
data = conn.execute("SELECT * FROM selection_has_variant")
expected = ((1, ret), (2, ret), (3, ret))
record = tuple([tuple(i) for i in data])
# Is the association table 'selection_has_variant' ok ?
assert record == expected
# Test ON CASCADE deletion
cursor = conn.cursor()
cursor.execute("DELETE FROM selections WHERE rowid = ?", str(ret))
assert cursor.rowcount == 1
# Now the table must be empty
data = conn.execute("SELECT * FROM selection_has_variant")
expected = tuple()
record = tuple([tuple(i) for i in data])
assert record == expected
# Extra tests on transactions states
assert conn.in_transaction
conn.commit()
assert not conn.in_transaction
def set_cmd(conn: sqlite3.Connection, target: str, first: str, second: str,
operator, **kwargs):
"""Perform set operation like intersection, union and difference between two table selection
This following VQL command:
`CREATE boby = raymond & charles`
will execute :
`set_cmd(conn, "boby", "raymond", "charles", "&")`
Args:
conn (sqlite3.Connection): sqlite3 connection
target (str): table selection target
first (str): first selection in operation
second (str): second selection in operation
operator (str): + (union), - (difference), & (intersection) Set operators
Returns:
dict: {"id": selection_id} if lines have been inserted,
or empty dict in case of error
Examples:
{"id": 2}: 2 lines inserted
"""
if target is None or first is None or second is None or operator is None:
return {}
query_first = build_sql_query(["id"], first, limit=None)
query_second = build_sql_query(["id"], second, limit=None)
func_query = {
"|": sql.union_variants,
"-": sql.subtract_variants,
"&": sql.intersect_variants,
}
sql_query = func_query[operator](query_first, query_second)
LOGGER.debug("command:set_cmd:: %s", sql_query)
selection_id = sql.create_selection_from_sql(conn,
sql_query,
target,
from_selection=False)
return dict() if selection_id is None else {"id": selection_id}
def test_sql_selection_operation(conn):
"""Test set operations on selections using SQL API
.. Todo:: Only union is tested here test intersect and expect
(intersect is tested in test_command)
"""
cursor = conn.cursor()
# Query the first default selection
all_selection = cursor.execute("SELECT * FROM selections").fetchone()
# {'id': 1, 'name': 'variants', 'count': 3, 'query': ''}
print("all", dict(all_selection))
# index 0: id in db
assert all_selection[1] == "variants"
assert all_selection[2] == len(VARIANTS)
# Create a selection from sql
query = "SELECT id, chr, pos FROM variants where alt = 'A' "
sql.create_selection_from_sql(conn, query, "test")
# check if selection has been created
assert "test" in [record["name"] for record in sql.get_selections(conn)]
# Check if selection of variants returns same data than selection query
selection_id = 2
insert_data = cursor.execute(query).fetchall()
read_data = cursor.execute(f"""
SELECT variants.id, variants.chr, variants.pos FROM variants
INNER JOIN selection_has_variant sv ON variants.rowid = sv.variant_id AND sv.selection_id = {selection_id}
""").fetchall()
# set because, it can contains duplicate variants
assert set(read_data) == set(insert_data)
# TEST Unions
query1 = "SELECT id, chr, pos FROM variants where alt = 'A' " # 2 variants
query2 = "SELECT id, chr, pos FROM variants where alt = 'C' " # 1 variant
union_query = sql.union_variants(query1, query2)
print(union_query)
selection_id = sql.create_selection_from_sql(conn, union_query, "union_GT")
print("union_GT selection id: ", selection_id)
assert selection_id is not None
record = cursor.execute(
f"SELECT id, name FROM selections WHERE name = 'union_GT'").fetchone()
print("Found record:", dict(record))
selection_id = record[0]
selection_name = record[1]
assert selection_id == 3 # test if selection id equal 2 ( the first is "variants")
assert selection_name == "union_GT"
# Select statement from union_GT selection must contains only variant.alt G or T
records = cursor.execute(f"""
SELECT variants.chr, variants.pos, variants.ref, variants.alt FROM variants
INNER JOIN selection_has_variant sv ON variants.rowid = sv.variant_id AND sv.selection_id = {selection_id}
""").fetchall()
# {'chr': 'chr1', 'pos': 10, 'ref': 'G', 'alt': 'A'}
# {'chr': 'chr1', 'pos': 50, 'ref': 'C', 'alt': 'C'}
# {'chr': 'chr1', 'pos': 45, 'ref': 'G', 'alt': 'A'}
for found_variants, record in enumerate(records, 1):
print(dict(record))
assert record["alt"] in ("A", "C")
assert found_variants == 3
