From 54e8769a8cfbf12967c26598b97ce0e88d891e86 Mon Sep 17 00:00:00 2001 From: Frederick Muriuki Muriithi Date: Mon, 10 Apr 2023 12:57:08 +0300 Subject: Add search and link for mRNA Assay datasets. --- gn3/auth/authorisation/data/mrna.py | 100 +++++++++++++++++++++++++++++++++++ gn3/auth/authorisation/data/views.py | 36 ++++++++++++- 2 files changed, 135 insertions(+), 1 deletion(-) create mode 100644 gn3/auth/authorisation/data/mrna.py (limited to 'gn3') diff --git a/gn3/auth/authorisation/data/mrna.py b/gn3/auth/authorisation/data/mrna.py new file mode 100644 index 0000000..bdfc5c1 --- /dev/null +++ b/gn3/auth/authorisation/data/mrna.py @@ -0,0 +1,100 @@ +"""Handle linking of mRNA Assay data to the Auth(entic|oris)ation system.""" +import uuid +from typing import Iterable +from MySQLdb.cursors import DictCursor + +import gn3.auth.db as authdb +import gn3.db_utils as gn3db +from gn3.auth.dictify import dictify +from gn3.auth.authorisation.checks import authorised_p +from gn3.auth.authorisation.groups.models import Group + +def linked_mrna_data(conn: authdb.DbConnection) -> Iterable[dict]: + """Retrieve mRNA Assay data that is linked to user groups.""" + with authdb.cursor(conn) as cursor: + cursor.execute("SELECT * FROM linked_mrna_data") + return (dict(row) for row in cursor.fetchall()) + +@authorised_p(("system:data:link-to-group",), + error_description=( + "You do not have sufficient privileges to link data to (a) " + "group(s)."), + oauth2_scope="profile group resource") +def ungrouped_mrna_data(# pylint: disable=[too-many-arguments] + authconn: authdb.DbConnection, gn3conn: gn3db.Connection, + search_query: str, selected: tuple[dict, ...] = tuple(), + limit: int = 10000, offset: int = 0) -> tuple[ + dict, ...]: + """Retrieve mrna data that is not linked to any user group.""" + params = tuple( + (row["SpeciesId"], row["InbredSetId"], row["ProbeFreezeId"], + row["ProbeSetFreezeId"]) + for row in linked_mrna_data(authconn)) + tuple( + (row["SpeciesId"], row["InbredSetId"], row["ProbeFreezeId"], + row["ProbeSetFreezeId"]) + for row in selected) + query = ( + "SELECT s.SpeciesId, iset.InbredSetId, iset.InbredSetName, " + "pf.ProbeFreezeId, pf.Name AS StudyName, psf.Id AS ProbeSetFreezeId, " + "psf.Name AS dataset_name, psf.FullName AS dataset_fullname, " + "psf.ShortName AS dataset_shortname " + "FROM Species AS s INNER JOIN InbredSet AS iset " + "ON s.SpeciesId=iset.SpeciesId INNER JOIN ProbeFreeze AS pf " + "ON iset.InbredSetId=pf.InbredSetId INNER JOIN ProbeSetFreeze AS psf " + "ON pf.ProbeFreezeId=psf.ProbeFreezeId ") + ( + "WHERE " if (len(params) > 0 or bool(search_query)) else "") + + if len(params) > 0: + paramstr = ", ".join(["(%s, %s, %s, %s)"] * len(params)) + query = query + ( + "(s.SpeciesId, iset.InbredSetId, pf.ProbeFreezeId, psf.Id) " + f"NOT IN ({paramstr}) " + ) + ("AND " if bool(search_query) else "") + + if bool(search_query): + query = query + ( + "CONCAT(pf.Name, psf.Name, ' ', psf.FullName, ' ', psf.ShortName) " + "LIKE %s ") + params = params + ((f"%{search_query}%",),)# type: ignore[operator] + + query = query + f"LIMIT {int(limit)} OFFSET {int(offset)}" + with gn3conn.cursor(DictCursor) as cursor: + cursor.execute( + query, tuple(item for sublist in params for item in sublist)) + return tuple(row for row in cursor.fetchall()) + +@authorised_p( + ("system:data:link-to-group",), + error_description=( + "You do not have sufficient privileges to link data to (a) " + "group(s)."), + oauth2_scope="profile group resource") +def link_mrna_data( + conn: authdb.DbConnection, group: Group, datasets: dict) -> dict: + """Link genotye `datasets` to `group`.""" + with authdb.cursor(conn) as cursor: + cursor.executemany( + "INSERT INTO linked_mrna_data VALUES " + "(:data_link_id, :group_id, :SpeciesId, :InbredSetId, " + ":ProbeFreezeId, :ProbeSetFreezeId, :dataset_name, " + ":dataset_fullname, :dataset_shortname) " + "ON CONFLICT " + "(SpeciesId, InbredSetId, ProbeFreezeId, ProbeSetFreezeId) " + "DO NOTHING", + tuple({ + "data_link_id": str(uuid.uuid4()), + "group_id": str(group.group_id), + **{ + key: value for key,value in dataset.items() if key in ( + "SpeciesId", "InbredSetId", "ProbeFreezeId", + "ProbeSetFreezeId", "dataset_fullname", "dataset_name", + "dataset_shortname") + } + } for dataset in datasets)) + return { + "description": ( + f"Successfully linked {len(datasets)} to group " + f"'{group.group_name}'."), + "group": dictify(group), + "datasets": datasets + } diff --git a/gn3/auth/authorisation/data/views.py b/gn3/auth/authorisation/data/views.py index a30096d..1a4c031 100644 --- a/gn3/auth/authorisation/data/views.py +++ b/gn3/auth/authorisation/data/views.py @@ -40,6 +40,7 @@ from gn3.auth.authorisation.errors import ForbiddenAccess from gn3.auth.authentication.oauth2.resource_server import require_oauth from gn3.auth.authentication.users import User, user_by_email, set_user_password +from gn3.auth.authorisation.data.mrna import link_mrna_data, ungrouped_mrna_data from gn3.auth.authorisation.data.genotypes import ( link_genotype_data, ungrouped_genotype_data) @@ -311,7 +312,15 @@ def migrate_users_data() -> Response: status=500, mimetype="application/json") def __search_mrna__(): - pass + query = __request_key__("query", "") + limit = int(__request_key__("limit", 10000)) + offset = int(__request_key__("offset", 0)) + with gn3db.database_connection() as gn3conn: + __ungrouped__ = partial( + ungrouped_mrna_data, gn3conn=gn3conn, search_query=query, + selected=__request_key_list__("selected"), + limit=limit, offset=offset) + return jsonify(with_db_connection(__ungrouped__)) def __request_key__(key: str, default: Any = ""): if bool(request.json): @@ -377,3 +386,28 @@ def link_genotypes() -> Response: return jsonify(with_db_connection( partial(__link__, **__values__(request.json)))) + +@data.route("/link/mrna", methods=["POST"]) +def link_mrna() -> Response: + """Link mrna data to group.""" + def __values__(form) -> dict[str, Any]: + if not bool(form.get("species_name", "").strip()): + raise InvalidData("Expected 'species_name' not provided.") + if not bool(form.get("group_id")): + raise InvalidData("Expected 'group_id' not provided.",) + try: + _group_id = uuid.UUID(form.get("group_id")) + except TypeError as terr: + raise InvalidData("Expected a UUID for 'group_id' value.") from terr + if not bool(form.get("selected")): + raise InvalidData("Expected at least one dataset to be provided.") + return { + "group_id": uuid.UUID(form.get("group_id")), + "datasets": form.get("selected") + } + + def __link__(conn: db.DbConnection, group_id: uuid.UUID, datasets: dict): + return link_mrna_data(conn, group_by_id(conn, group_id), datasets) + + return jsonify(with_db_connection( + partial(__link__, **__values__(request.json)))) -- cgit v1.2.3