def select_cmd(
conn: sqlite3.Connection,
fields=("chr", "pos", "ref", "alt"),
source="variants",
filters={},
order_by=None,
order_desc=True,
group_by=[],
having={}, # {"op":">", "value": 3 }
limit=50,
offset=0,
**kwargs,
):
"""Select query Command
This following VQL command:
`SELECT chr,pos FROM variants WHERE pos > 3`
will execute :
`select_cmd(conn, ["chr", "pos"], variants", {"AND": [{"pos","=",3}]}))`
Args:
conn (sqlite3.Connection): sqlite3 connection
fields (list, optional): list of fields
filters (dict, optional): nested tree of condition
source (str, optional): virtual table source
order_by (list, optional): order by field name
order_desc (bool, optional): Descending or Ascending Order
limit (int, optional): record count
offset (int, optional): record count per page
Yields:
variants (dict)
"""
query = build_full_sql_query(
conn,
fields=fields,
source=source,
filters=filters,
order_by=order_by,
order_desc=order_desc,
limit=limit,
offset=offset,
group_by=group_by,
having=having,
**kwargs,
)
LOGGER.debug("command:select_cmd:: %s", query)
for i in conn.execute(query):
# THIS IS INSANE... SQLITE DOESNT RETURN ALIAS NAME WITH SQUARE BRACKET....
# I HAVE TO replace [] by () and go back after...
# TODO : Change VQL Syntax from [] to () would be a good alternative
# @See QUERYBUILDER
# See : https://stackoverflow.com/questions/41538952/issue-cursor-description-never-returns-square-bracket-in-column-name-python-2-7-sqlite3-alias
yield {
k.replace("(", "[").replace(")", "]"): v
for k, v in dict(i).items()
}
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 count_cmd(
conn: sqlite3.Connection,
fields=["chr", "pos", "ref", "alt"],
source="variants",
filters={},
group_by=[],
having={},
**kwargs,
):
"""Count command
This following VQL command:
`COUNT FROM variants WHERE pos > 3`
will execute :
`count_cmd(conn, "variants", {"AND": [{"pos","=",3}]}))`
Args:
conn (sqlite3.Connection): sqlite3 connection
source (str, optional): virtual source table
filters (dict, optional): nested tree of condition
Returns:
dict: Count of variants wgstith "count" as a key
"""
# See #177: Check if fields has annotations
# If an annotation field is selected, the variant count stored in the selection
# table (count without taking account of annotations) is different.
# This leads to a fault in the pagination hiding the latest variants if
# more than 50 must be displayed.
variants_fields = set(
field["name"] for field in sql.get_field_by_category(conn, "variants"))
if set(fields).issubset(variants_fields) and not filters and not group_by:
# All fields are in variants table
# Returned stored cache variant
LOGGER.debug("command:count_cmd:: cached from selections table")
return {
"count":
conn.execute(
f"SELECT count FROM selections WHERE name = '{source}'").
fetchone()[0]
}
query = build_full_sql_query(
conn,
fields=fields,
source=source,
filters=filters,
limit=None,
offset=None,
order_by=None,
group_by=group_by,
having=having,
**kwargs,
)
# THIS IS INSANE... SQLITE DOESNT RETURN ALIAS NAME WITH SQUARE BRACKET....
# I HAVE TO replace [] by () and go back after...
# TODO : Change VQL Syntax from [] to () would be a good alternative
# @See QUERYBUILDER
# See : https://stackoverflow.com/questions/41538952/issue-cursor-description-never-returns-square-bracket-in-column-name-python-2-7-sqlite3-alias
LOGGER.debug("command:count_cmd:: %s", query)
return {"count": sql.count_query(conn, query)}
def load(self):
"""Start async queries to get variants and variant count
Called by:
- on_change_query() from the view.
- sort() and setPage() by the model.
See Also:
:meth:`loaded`
"""
if self.conn is None:
return
self._set_loading(True)
LOGGER.debug("Start loading")
offset = (self.page - 1) * self.limit
# Add fields from group by
# self.clear() # Assume variant = []
self.total = 0
# LOGGER.debug("Page queried: %s", self.page)
# Store SQL query for debugging purpose
self.debug_sql = build_full_sql_query(
self.conn,
fields=self.fields,
source=self.source,
filters=self.filters,
limit=self.limit,
offset=offset,
order_desc=self.order_desc,
order_by=self.order_by,
group_by=self.group_by,
having=self.having,
)
# Create load_func to run asynchronously: load variants
load_func = functools.partial(
cmd.select_cmd,
fields=self.fields,
source=self.source,
filters=self.filters,
limit=self.limit,
offset=offset,
order_desc=self.order_desc,
order_by=self.order_by,
group_by=self.group_by,
having=self.having,
)
# Create count_func to run asynchronously: count variants
count_function = functools.partial(
cmd.count_cmd,
fields=self.fields,
source=self.source,
filters=self.filters,
group_by=self.group_by,
)
# Assign async functions to runnables
self.variant_runnable.function = lambda conn: list(load_func(conn))
self.count_runnable.function = count_function
# Assign unique ID for this run
self.query_number = next(self.query_counter)
self.variant_runnable.query_number = self.query_number
self.count_runnable.query_number = self.query_number
# Start the run
LOGGER.debug("Start pools; query number %s", self.query_number)
self.pool.start(self.variant_runnable)
self.pool.start(self.count_runnable)