diff options
Diffstat (limited to 'gn_auth/auth/authorisation/data')
| -rw-r--r-- | gn_auth/auth/authorisation/data/genotypes.py | 99 | ||||
| -rw-r--r-- | gn_auth/auth/authorisation/data/mrna.py | 102 | ||||
| -rw-r--r-- | gn_auth/auth/authorisation/data/phenotypes.py | 245 | ||||
| -rw-r--r-- | gn_auth/auth/authorisation/data/views.py | 319 |
4 files changed, 517 insertions, 248 deletions
diff --git a/gn_auth/auth/authorisation/data/genotypes.py b/gn_auth/auth/authorisation/data/genotypes.py index bdab8fa..d5af3ae 100644 --- a/gn_auth/auth/authorisation/data/genotypes.py +++ b/gn_auth/auth/authorisation/data/genotypes.py @@ -1,16 +1,20 @@ """Handle linking of Genotype data to the Auth(entic|oris)ation system.""" import uuid -from dataclasses import asdict +import logging from typing import Iterable +from functools import reduce +from dataclasses import asdict +from gn_libs import mysqldb as gn3db +from gn_libs import sqlite3 as authdb from MySQLdb.cursors import DictCursor -from gn_auth.auth.db import mariadb as gn3db -from gn_auth.auth.db import sqlite3 as authdb - from gn_auth.auth.authorisation.checks import authorised_p from gn_auth.auth.authorisation.resources.groups.models import Group + +logger = logging.getLogger(__name__) + def linked_genotype_data(conn: authdb.DbConnection) -> Iterable[dict]: """Retrieve genotype data that is linked to user groups.""" with authdb.cursor(conn) as cursor: @@ -22,17 +26,26 @@ def linked_genotype_data(conn: authdb.DbConnection) -> Iterable[dict]: "You do not have sufficient privileges to link data to (a) " "group(s)."), oauth2_scope="profile group resource") -def ungrouped_genotype_data(# pylint: disable=[too-many-arguments] - authconn: authdb.DbConnection, gn3conn: gn3db.DbConnection, +def ungrouped_genotype_data(# pylint: disable=[too-many-arguments, too-many-positional-arguments] + authconn: authdb.DbConnection, gn3conn: gn3db.Connection, search_query: str, selected: tuple[dict, ...] = tuple(), limit: int = 10000, offset: int = 0) -> tuple[ dict, ...]: - """Retrieve genotype data that is not linked to any user group.""" - params = tuple( - (row["SpeciesId"], row["InbredSetId"], row["GenoFreezeId"]) - for row in linked_genotype_data(authconn)) + tuple( - (row["SpeciesId"], row["InbredSetId"], row["GenoFreezeId"]) - for row in selected) + """Retrieve genotype data that is not linked to any user group. + + The set of linked datasets is read from the auth database (SQLite) and the + exclusion happens in Python. This avoids embedding the (ever-growing) list + of linked datasets into the MariaDB query as a giant `NOT IN` list, which + MariaDB executes with an unindexed nested-loop join and which degrades + badly as more datasets get linked. + """ + def __key__(row): + """Normalise a row's dataset identity to a comparable tuple.""" + return (int(row["SpeciesId"]), int(row["InbredSetId"]), + int(row["GenoFreezeId"])) + + excluded = {__key__(row) for row in linked_genotype_data(authconn)} | { + __key__(row) for row in selected} query = ( "SELECT s.SpeciesId, iset.InbredSetId, iset.InbredSetName, " "gf.Id AS GenoFreezeId, gf.Name AS dataset_name, " @@ -41,27 +54,21 @@ def ungrouped_genotype_data(# pylint: disable=[too-many-arguments] "FROM Species AS s INNER JOIN InbredSet AS iset " "ON s.SpeciesId=iset.SpeciesId INNER JOIN GenoFreeze AS gf " "ON iset.InbredSetId=gf.InbredSetId ") - - if len(params) > 0 or bool(search_query): - query = query + "WHERE " - - if len(params) > 0: - paramstr = ", ".join(["(%s, %s, %s)"] * len(params)) - query = query + ( - "(s.SpeciesId, iset.InbredSetId, gf.Id) " - f"NOT IN ({paramstr}) " - ) + ("AND " if bool(search_query) else "") - + params: tuple[str, ...] = tuple() if bool(search_query): query = query + ( - "CONCAT(gf.Name, ' ', gf.FullName, ' ', gf.ShortName) LIKE %s ") - params = params + ((f"%{search_query}%",),)# type: ignore[operator] + "WHERE CONCAT(gf.Name, ' ', gf.FullName, ' ', gf.ShortName) " + "LIKE %s ") + params = (f"%{search_query}%",) - 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()) + cursor.execute(query, params) + _rows = tuple(row for row in cursor.fetchall()) + + # Filter out linked/selected datasets and apply pagination in Python. + return tuple( + row for row in _rows + if __key__(row) not in excluded)[offset:offset + limit] @authorised_p( ("system:data:link-to-group",), @@ -95,3 +102,37 @@ def link_genotype_data( "group": asdict(group), "datasets": datasets } + + +def resources_by_datasets_and_traits( + authconn: authdb.DbConnection, + dsets_traits: tuple[tuple[str, str], ...] +) -> tuple[dict, ...]: + """Fetch resources by their attached datasets and traits.""" + traits_by_datasets: dict[str, tuple[str, ...]] = reduce( + lambda acc, curr: { + **acc, + curr[0]: acc.get(curr[0], tuple()) + (curr[1],) + }, + dsets_traits, + {}) + paramstr = ", ".join(["?"] * len(dsets_traits)) + query = ( + "SELECT r.*, rc.*, lgd.dataset_name FROM linked_genotype_data AS lgd " + "INNER JOIN genotype_resources AS mr ON lgd.data_link_id=mr.data_link_id " + "INNER JOIN resources AS r ON mr.resource_id=r.resource_id " + "INNER JOIN resource_categories AS rc " + "ON r.resource_category_id=rc.resource_category_id " + "WHERE lgd.dataset_name " + f"IN ({paramstr})") + logger.debug("QUERY: %s", query) + with authdb.cursor(authconn) as cursor: + params = tuple(traits_by_datasets.keys()) + logger.debug("QUERY PARAMS: %s", params) + cursor.execute(query, tuple(traits_by_datasets.keys())) + return tuple({ + "resource_id": row["resource_id"], + "resource_data": tuple( + f'{row["dataset_name"]}::{trait_id}' + for trait_id in traits_by_datasets[row["dataset_name"]]) + } for row in cursor.fetchall()) diff --git a/gn_auth/auth/authorisation/data/mrna.py b/gn_auth/auth/authorisation/data/mrna.py index 60470a7..dd589e0 100644 --- a/gn_auth/auth/authorisation/data/mrna.py +++ b/gn_auth/auth/authorisation/data/mrna.py @@ -1,15 +1,21 @@ """Handle linking of mRNA Assay data to the Auth(entic|oris)ation system.""" import uuid -from dataclasses import asdict +import logging from typing import Iterable -from MySQLdb.cursors import DictCursor +from functools import reduce +from dataclasses import asdict -from gn_auth.auth.db import sqlite3 as authdb -from gn_auth.auth.db import mariadb as gn3db +from gn_libs import mysqldb as gn3db +from gn_libs import sqlite3 as authdb +from MySQLdb.cursors import DictCursor from gn_auth.auth.authorisation.checks import authorised_p from gn_auth.auth.authorisation.resources.groups.models import Group + +logger = logging.getLogger(__name__) + + 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: @@ -21,19 +27,26 @@ def linked_mrna_data(conn: authdb.DbConnection) -> Iterable[dict]: "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.DbConnection, +def ungrouped_mrna_data(# pylint: disable=[too-many-arguments, too-many-positional-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) + """Retrieve mrna data that is not linked to any user group. + + The set of linked datasets is read from the auth database (SQLite) and the + exclusion happens in Python. This avoids embedding the (ever-growing) list + of linked datasets into the MariaDB query as a giant `NOT IN` list, which + MariaDB executes with an unindexed nested-loop join and which degrades + badly as more datasets get linked. + """ + def __key__(row): + """Normalise a row's dataset identity to a comparable tuple.""" + return (int(row["SpeciesId"]), int(row["InbredSetId"]), + int(row["ProbeFreezeId"]), int(row["ProbeSetFreezeId"])) + + excluded = {__key__(row) for row in linked_mrna_data(authconn)} | { + __key__(row) for row in selected} query = ( "SELECT s.SpeciesId, iset.InbredSetId, iset.InbredSetName, " "pf.ProbeFreezeId, pf.Name AS StudyName, psf.Id AS ProbeSetFreezeId, " @@ -42,27 +55,22 @@ def ungrouped_mrna_data(# pylint: disable=[too-many-arguments] "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 "") - + "ON pf.ProbeFreezeId=psf.ProbeFreezeId ") + params: tuple[str, ...] = tuple() 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] + "WHERE CONCAT(pf.Name, psf.Name, ' ', psf.FullName, ' ', " + "psf.ShortName) LIKE %s ") + params = (f"%{search_query}%",) - 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()) + cursor.execute(query, params) + _rows = tuple(row for row in cursor.fetchall()) + + # Filter out linked/selected datasets and apply pagination in Python. + return tuple( + row for row in _rows + if __key__(row) not in excluded)[offset:offset + limit] @authorised_p( ("system:data:link-to-group",), @@ -99,3 +107,35 @@ def link_mrna_data( "group": asdict(group), "datasets": datasets } + + +def resources_by_datasets_and_traits( + authconn: authdb.DbConnection, + dsets_traits: tuple[tuple[str, str], ...] +) -> tuple[dict, ...]: + """Fetch resources by their attached datasets and traits.""" + traits_by_datasets: dict[str, tuple[str, ...]] = reduce( + lambda acc, curr: { + **acc, + curr[0]: acc.get(curr[0], tuple()) + (curr[1],) + }, + dsets_traits, + {}) + paramstr = ", ".join(["?"] * len(traits_by_datasets.keys())) + query = ( + "SELECT r.*, rc.*, lmd.dataset_name FROM linked_mrna_data AS lmd " + "INNER JOIN mrna_resources AS mr ON lmd.data_link_id=mr.data_link_id " + "INNER JOIN resources AS r ON mr.resource_id=r.resource_id " + "INNER JOIN resource_categories AS rc " + "ON r.resource_category_id=rc.resource_category_id " + "WHERE lmd.dataset_name " + f"IN ({paramstr})") + logger.debug("QUERY: %s", query) + with authdb.cursor(authconn) as cursor: + cursor.execute(query, tuple(traits_by_datasets.keys())) + return tuple({ + "resource_id": row["resource_id"], + "resource_data": tuple( + f'{row["dataset_name"]}::{trait_id}' + for trait_id in traits_by_datasets[row["dataset_name"]]) + } for row in cursor.fetchall()) diff --git a/gn_auth/auth/authorisation/data/phenotypes.py b/gn_auth/auth/authorisation/data/phenotypes.py index 0a76237..bc9a6f7 100644 --- a/gn_auth/auth/authorisation/data/phenotypes.py +++ b/gn_auth/auth/authorisation/data/phenotypes.py @@ -1,18 +1,33 @@ """Handle linking of Phenotype data to the Auth(entic|oris)ation system.""" import uuid +import logging +from functools import reduce from dataclasses import asdict from typing import Any, Iterable +from gn_libs import mysqldb as gn3db +from gn_libs import sqlite3 as authdb from MySQLdb.cursors import DictCursor +from flask import request, jsonify, Response, Blueprint, current_app as app -from gn_auth.auth.db import sqlite3 as authdb -from gn_auth.auth.db import mariadb as gn3db +from gn_auth.auth.authentication.oauth2.resource_server import require_oauth + +from gn_auth.auth.errors import AuthorisationError +from gn_auth.auth.authorisation.resources.checks import can_delete +from gn_auth.auth.authorisation.resources.system.models import system_resource +from gn_auth.auth.authorisation.resources.groups.models import Group, group_resource + + +from gn_auth.auth.authentication.users import User +from gn_auth.auth.authorisation.checks import require_json +from gn_auth.auth.authorisation.resources.checks import authorised_for_spec + +logger = logging.getLogger(__name__) +phenosbp = Blueprint("phenotypes", __name__) -from gn_auth.auth.authorisation.checks import authorised_p -from gn_auth.auth.authorisation.resources.groups.models import Group def linked_phenotype_data( - authconn: authdb.DbConnection, gn3conn: gn3db.DbConnection, + authconn: authdb.DbConnection, gn3conn: gn3db.Connection, species: str = "") -> Iterable[dict[str, Any]]: """Retrieve phenotype data linked to user groups.""" authkeys = ("SpeciesId", "InbredSetId", "PublishFreezeId", "PublishXRefId") @@ -47,43 +62,8 @@ def linked_phenotype_data( gn3cursor.execute(query, params) return (item for item in gn3cursor.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_phenotype_data( - authconn: authdb.DbConnection, gn3conn: gn3db.DbConnection): - """Retrieve phenotype data that is not linked to any user group.""" - with gn3conn.cursor() as cursor: - params = tuple( - (row["SpeciesId"], row["InbredSetId"], row["PublishFreezeId"], - row["PublishXRefId"]) - for row in linked_phenotype_data(authconn, gn3conn)) - paramstr = ", ".join(["(?, ?, ?, ?)"] * len(params)) - query = ( - "SELECT spc.SpeciesId, spc.SpeciesName, iset.InbredSetId, " - "iset.InbredSetName, pf.Id AS PublishFreezeId, " - "pf.Name AS dataset_name, pf.FullName AS dataset_fullname, " - "pf.ShortName AS dataset_shortname, pxr.Id AS PublishXRefId " - "FROM " - "Species AS spc " - "INNER JOIN InbredSet AS iset " - "ON spc.SpeciesId=iset.SpeciesId " - "INNER JOIN PublishFreeze AS pf " - "ON iset.InbredSetId=pf.InbredSetId " - "INNER JOIN PublishXRef AS pxr " - "ON pf.InbredSetId=pxr.InbredSetId") - if len(params) > 0: - query = query + ( - f" WHERE (iset.InbredSetId, pf.Id, pxr.Id) NOT IN ({paramstr})") - - cursor.execute(query, params) - return tuple(dict(row) for row in cursor.fetchall()) - - return tuple() -def __traits__(gn3conn: gn3db.DbConnection, params: tuple[dict, ...]) -> tuple[dict, ...]: +def pheno_traits_from_db(gn3conn: gn3db.Connection, params: tuple[dict, ...]) -> tuple[dict, ...]: """An internal utility function. Don't use outside of this module.""" if len(params) < 1: return tuple() @@ -110,21 +90,35 @@ def __traits__(gn3conn: gn3db.DbConnection, params: tuple[dict, ...]) -> tuple[d for itm in sublist)) return 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_phenotype_data( - authconn:authdb.DbConnection, gn3conn: gn3db.DbConnection, group: Group, - traits: tuple[dict, ...]) -> dict: + authconn: authdb.DbConnection, + user: User, + group: Group, + traits: tuple[dict, ...] +) -> dict: """Link phenotype traits to a user group.""" + if not (authorised_for_spec( + authconn, + user.user_id, + system_resource(authconn).resource_id, + "(AND system:data:link-to-group)") + or + authorised_for_spec( + authconn, + user.user_id, + group_resource(authconn, group.group_id).resource_id, + "(AND group:data:link-to-group)") + ): + raise AuthorisationError( + "You do not have sufficient privileges to link data to group " + f"'{group.group_name}'.") with authdb.cursor(authconn) as cursor: params = tuple({ "data_link_id": str(uuid.uuid4()), "group_id": str(group.group_id), **item - } for item in __traits__(gn3conn, traits)) + } for item in traits) cursor.executemany( "INSERT INTO linked_phenotype_data " "VALUES (" @@ -139,3 +133,156 @@ def link_phenotype_data( "group": asdict(group), "traits": params } + + +def unlink_from_resources( + cursor: authdb.DbCursor, + data_link_ids: tuple[uuid.UUID, ...] +) -> tuple[uuid.UUID, ...]: + """Unlink phenotypes from resources.""" + # TODO: Delete in batches + cursor.executemany("DELETE FROM phenotype_resources " + "WHERE data_link_id=? RETURNING resource_id", + tuple((str(_id),) for _id in data_link_ids)) + return tuple(uuid.UUID(row["resource_id"]) for row in cursor.fetchall()) + + +def delete_resources( + cursor: authdb.DbCursor, + resource_ids: tuple[uuid.UUID, ...] +) -> tuple[uuid.UUID, ...]: + """Delete the specified phenotype resources.""" + # TODO: Delete in batches + cursor.executemany("DELETE FROM resources " + "WHERE resource_id=? RETURNING resource_id", + tuple((str(_id),) for _id in resource_ids)) + return tuple(uuid.UUID(row["resource_id"]) for row in cursor.fetchall()) + + +def fetch_data_link_ids( + cursor: authdb.DbCursor, + species_id: int, + population_id: int, + dataset_id: int, + xref_ids: tuple[int, ...] +) -> tuple[uuid.UUID, ...]: + """Fetch `data_link_id` values for phenotypes.""" + paramstr = ", ".join(["(?, ?, ?, ?)"] * len(xref_ids)) + cursor.execute( + "SELECT data_link_id FROM linked_phenotype_data " + "WHERE (SpeciesId, InbredSetId, PublishFreezeId, PublishXRefId) IN " + f"({paramstr})", + tuple(str(field) for arow in + ((species_id, population_id, dataset_id, xref_id) + for xref_id in xref_ids) + for field in arow)) + return tuple(uuid.UUID(row["data_link_id"]) for row in cursor.fetchall()) + + +def fetch_resource_id(cursor: authdb.DbCursor, + data_link_ids: tuple[uuid.UUID, ...]) -> uuid.UUID: + """Retrieve the ID of the resource where the data is linked to. + + RAISES: InvalidResourceError in the case where more the data_link_ids belong + to more than one resource.""" + _paramstr = ", ".join(["?"] * len(data_link_ids)) + cursor.execute( + "SELECT DISTINCT(resource_id) FROM phenotype_resources " + f"WHERE data_link_id IN ({_paramstr})", + tuple(str(_id) for _id in data_link_ids)) + _ids = tuple(uuid.UUID(row['resource_id']) for row in cursor.fetchall()) + if len(_ids) != 1: + raise AuthorisationError( + f"Expected data from 1 resource, got {len(_ids)} resources.") + return _ids[0] + + +def delete_linked_data( + cursor: authdb.DbCursor, + data_link_ids: tuple[uuid.UUID, ...] +) -> int: + """Delete the actual linked data.""" + # TODO: Delete in batches + cursor.executemany("DELETE FROM linked_phenotype_data " + "WHERE data_link_id=?", + tuple((str(_id),) for _id in data_link_ids)) + return cursor.rowcount + + +@phenosbp.route("/<int:species_id>/<int:population_id>/<int:dataset_id>/delete", + methods=["POST"]) +@require_json +def delete_linked_phenotypes_data( + species_id: int, + population_id: int, + dataset_id: int +) -> Response: + """Delete the linked phenotypes data from the database.""" + db_uri = app.config["AUTH_DB"] + with (require_oauth.acquire("profile group resource") as _token, + authdb.connection(db_uri) as auth_conn, + authdb.cursor(auth_conn) as cursor): + _deleted = 0 + xref_ids = tuple(request.json.get("xref_ids", []))#type: ignore[union-attr] + if len(xref_ids) > 0: + # TODO: Use background job, for huge number of xref_ids + data_link_ids = fetch_data_link_ids( + cursor, species_id, population_id, dataset_id, xref_ids) + resource_id = fetch_resource_id(cursor, data_link_ids) + # - Does user have DELETE privilege on the data + if not can_delete(auth_conn, _token.user.user_id, resource_id): + # - No: Raise `AuthorisationError` and bail! + raise AuthorisationError( + "You are not allowed to delete this resource's data.") + # - YES: go ahead and delete data as below. + _resources_ids = unlink_from_resources(cursor, data_link_ids) + delete_resources(cursor, _resources_ids) + _deleted = delete_linked_data(cursor, data_link_ids) + + return jsonify({ + # TODO: "status": "sent-to-background"/"completed"/"failed" + # TODO: "status-url": <status-check-uri> + "requested": len(xref_ids), + "deleted": _deleted + }) + + +def __organise_resources_data__(acc, curr) -> dict: + logger.debug("ORGANISING... %s", dict(curr)) + resource_row = acc.get(curr["resource_id"], { + "resource_id": curr["resource_id"], + "resource_data": tuple(), + }) + return { + **acc, + curr["resource_id"]: { + **resource_row, + "resource_data": resource_row["resource_data"] + ( + f'{curr["dataset_name"]}::{curr["trait_id"]}',) + } + } + + +def resources_by_datasets_and_traits( + authconn: authdb.DbConnection, + dsets_traits: tuple[tuple[str, str], ...] +) -> tuple[dict, ...]: + """Fetch resources by their attached datasets and traits.""" + paramstr = ", ".join(["(?, ?)"] * len(dsets_traits)) + query = ( + "SELECT r.*, rc.*, lpd.dataset_name, lpd.PublishXRefId AS trait_id " + "FROM linked_phenotype_data AS lpd " + "INNER JOIN phenotype_resources AS pr " + "ON lpd.data_link_id=pr.data_link_id " + "INNER JOIN resources AS r ON pr.resource_id=r.resource_id " + "INNER JOIN resource_categories AS rc " + "ON r.resource_category_id=rc.resource_category_id " + "WHERE (lpd.dataset_name, lpd.PublishXRefId) " + f"IN ({paramstr})") + with authdb.cursor(authconn) as cursor: + cursor.execute( + query, tuple(item for row in dsets_traits for item in row)) + return tuple(reduce( + __organise_resources_data__, + cursor.fetchall(), + {}).values()) diff --git a/gn_auth/auth/authorisation/data/views.py b/gn_auth/auth/authorisation/data/views.py index 7ed69e3..0ffc08e 100644 --- a/gn_auth/auth/authorisation/data/views.py +++ b/gn_auth/auth/authorisation/data/views.py @@ -2,15 +2,20 @@ import sys import uuid import json -from dataclasses import asdict +import logging from typing import Any -from functools import partial +from functools import reduce, partial import redis from MySQLdb.cursors import DictCursor from authlib.integrations.flask_oauth2.errors import _HTTPException from flask import request, jsonify, Response, Blueprint, current_app as app + +from gn_libs import mysqldb as gn3db +from gn_libs import sqlite3 as db +from gn_libs.sqlite3 import with_db_connection + from gn_auth import jobs from gn_auth.commands import run_async_cmd @@ -18,54 +23,30 @@ from gn_auth.auth.requests import request_json from gn_auth.auth.errors import InvalidData, NotFoundError from gn_auth.auth.authorisation.resources.groups.models import group_by_id -from ...db import sqlite3 as db -from ...db import mariadb as gn3db -from ...db.sqlite3 import with_db_connection - from ..checks import require_json -from ..users.models import user_resource_roles - -from ..resources.checks import authorised_for -from ..resources.models import ( - user_resources, public_resources, attach_resources_data) - from ...authentication.users import User from ...authentication.oauth2.resource_server import require_oauth -from ..data.phenotypes import link_phenotype_data -from ..data.mrna import link_mrna_data, ungrouped_mrna_data -from ..data.genotypes import link_genotype_data, ungrouped_genotype_data - +from .mrna import ( + link_mrna_data, + ungrouped_mrna_data, + resources_by_datasets_and_traits as mrna_resources_by_datasets_and_traits) +from .genotypes import ( + link_genotype_data, + ungrouped_genotype_data, + resources_by_datasets_and_traits as geno_resources_by_datasets_and_traits) +from .phenotypes import ( + phenosbp, + link_phenotype_data, + pheno_traits_from_db, + resources_by_datasets_and_traits as pheno_resources_by_datasets_and_traits) + + +logger = logging.getLogger(__name__) data = Blueprint("data", __name__) +data.register_blueprint(phenosbp, url_prefix="/phenotypes") -def build_trait_name(trait_fullname): - """ - Initialises the trait's name, and other values from the search data provided - - This is a copy of `gn3.db.traits.build_trait_name` function. - """ - def dataset_type(dset_name): - if dset_name.find('Temp') >= 0: - return "Temp" - if dset_name.find('Geno') >= 0: - return "Geno" - if dset_name.find('Publish') >= 0: - return "Publish" - return "ProbeSet" - - name_parts = trait_fullname.split("::") - assert len(name_parts) >= 2, f"Name format error: '{trait_fullname}'" - dataset_name = name_parts[0] - dataset_type = dataset_type(dataset_name) - return { - "db": { - "dataset_name": dataset_name, - "dataset_type": dataset_type}, - "trait_fullname": trait_fullname, - "trait_name": name_parts[1], - "cellid": name_parts[2] if len(name_parts) == 3 else "" - } @data.route("species") def list_species() -> Response: @@ -77,101 +58,144 @@ def list_species() -> Response: @data.route("/authorisation", methods=["POST"]) @require_json -def authorisation() -> Response: +def authorisation() -> Response:# pylint: disable=[too-many-locals] """Retrieve the authorisation level for datasets/traits for the user.""" # Access endpoint with something like: - # curl -X POST http://127.0.0.1:8080/api/oauth2/data/authorisation \ + # curl -X POST http://127.0.0.1:8081/auth/data/authorisation \ # -H "Content-Type: application/json" \ # -d '{"traits": ["HC_M2_0606_P::1442370_at", "BXDGeno::01.001.695", # "BXDPublish::10001"]}' + def __organise_traits__(acc, curr): + dset, _trt = curr + key = "ProbeSet" + if dset.endswith("Publish"): + key = "Publish" + elif dset.endswith("Geno"): + key="Geno" + elif dset.endswith("Temp"): + key = "Temp" + else: + key = "ProbeSet" + + return { + **acc, + key: acc.get(key, tuple()) + (curr,) + } + _dset_traits: dict[str, tuple[tuple[str, str], ...]] = reduce( + __organise_traits__, + ( + (dset.strip(), trt.strip()) for dset, trt in + (trtstr.split("::") for trtstr in + request_json().get("traits", []))), + {key: tuple() for key in ("Publish", "ProbeSet", "Geno", "Temp")}) + db_uri = app.config["AUTH_DB"] - privileges = {} user = User(uuid.uuid4(), "anon@ymous.user", "Anonymous User") - with db.connection(db_uri) as auth_conn: - try: - with require_oauth.acquire("profile group resource") as _token: - user = _token.user - resources = attach_resources_data( - auth_conn, user_resources(auth_conn, _token.user)) - resources_roles = user_resource_roles(auth_conn, _token.user) - privileges = { - resource_id: tuple( - privilege.privilege_id - for roles in resources_roles[resource_id] - for privilege in roles.privileges)#("group:resource:view-resource",) - for resource_id, is_authorised - in authorised_for( - auth_conn, _token.user, - ("group:resource:view-resource",), tuple( - resource.resource_id for resource in resources)).items() - if is_authorised - } - except _HTTPException as exc: - err_msg = json.loads(exc.body) - if err_msg["error"] == "missing_authorization": - resources = attach_resources_data( - auth_conn, public_resources(auth_conn)) - else: - raise exc from None - - def __gen_key__(resource, data_item): - if resource.resource_category.resource_category_key.lower() == "phenotype": - return ( - f"{resource.resource_category.resource_category_key.lower()}::" - f"{data_item['dataset_name']}::{data_item['PublishXRefId']}") - return ( - f"{resource.resource_category.resource_category_key.lower()}::" - f"{data_item['dataset_name']}") - - data_to_resource_map = { - __gen_key__(resource, data_item): resource.resource_id - for resource in resources - for data_item in resource.resource_data + with (db.connection(db_uri) as authconn, db.cursor(authconn) as cursor): + _all_resources = { + _rrow["resource_id"]: _rrow + for _rtypes in ( + pheno_resources_by_datasets_and_traits( + authconn, _dset_traits["Publish"]), + geno_resources_by_datasets_and_traits( + authconn, _dset_traits["Geno"]), + mrna_resources_by_datasets_and_traits( + authconn, _dset_traits["ProbeSet"])) + for _rrow in _rtypes } - privileges = { - **{ - resource.resource_id: ("system:resource:public-read",) - for resource in resources if resource.public - }, - **privileges} - - args = request.get_json() - traits_names = args["traits"] # type: ignore[index] - def __translate__(val): + if (len(_all_resources.keys()) == 0 and + len(_dset_traits.get("Temp", tuple())) == 0): + raise NotFoundError( + "No resource(s) found for specified trait(s). Do(es) the " + "trait(s) actually exist?") + + # Handle Temp traits specially - they should be public/anonymous resources + if len(_dset_traits.get("Temp", tuple())) > 0: + # Create a synthetic public resource for Temp traits + # Use a predictable ID to identify synthetic temp resources + temp_resource_id = "gn-auth-temp-traits" + _all_resources[temp_resource_id] = { + "resource_id": temp_resource_id, + "resource_data": tuple(f"{dset}::{trait}" for dset, trait in _dset_traits["Temp"]) + } + + _resource_ids = tuple(_all_resources.keys()) + + + def __explode_resource_data__(trait_fullname): + _dset, _trt = trait_fullname.split("::") return { - "Temp": "Temp", - "ProbeSet": "mRNA", - "Geno": "Genotype", - "Publish": "Phenotype" - }[val] - - def __trait_key__(trait): - dataset_type = __translate__(trait['db']['dataset_type']).lower() - dataset_name = trait["db"]["dataset_name"] - if dataset_type == "phenotype": - return f"{dataset_type}::{dataset_name}::{trait['trait_name']}" - return f"{dataset_type}::{dataset_name}" - - return jsonify(tuple( - { - "user": asdict(user), - **{key:trait[key] for key in ("trait_fullname", "trait_name")}, - "dataset_name": trait["db"]["dataset_name"], - "dataset_type": __translate__(trait["db"]["dataset_type"]), - "resource_id": data_to_resource_map.get(__trait_key__(trait)), - "privileges": privileges.get( - data_to_resource_map.get( - __trait_key__(trait), - uuid.UUID("4afa415e-94cb-4189-b2c6-f9ce2b6a878d")), - tuple()) + ( - # Temporary traits do not exist in db: Set them - # as public-read - ("system:resource:public-read",) - if trait["db"]["dataset_type"] == "Temp" - else tuple()) - } for trait in - (build_trait_name(trait_fullname) - for trait_fullname in traits_names))) + "dataset_name": _dset, + "dataset_type": ( + "Phenotype" if _dset.endswith("Publish") + else ("Genotype" if _dset.endswith("Geno") + else ("Temporary" if _dset.endswith("Temp") + else "mRNA"))), + "trait_name": _trt, + "trait_fullname": trait_fullname + } + + _paramstr = ", ".join(["?"] * len(_resource_ids)) + _privileges_by_resource: dict[str, tuple[str, ...]] = {} + + # Separate synthetic temp resources from real resources + temp_resource_id = "gn-auth-temp-traits" + real_resource_ids = tuple(rid for rid in _resource_ids if rid != temp_resource_id) + + # Query privileges only for real resources + if len(real_resource_ids) > 0: + real_paramstr = ", ".join(["?"] * len(real_resource_ids)) + try: + with require_oauth.acquire("profile group resource") as _token: + user = _token.user + cursor.execute( + "SELECT ur.resource_id, r.role_id, rp.privilege_id " + "FROM user_roles AS ur " + "INNER JOIN roles AS r ON ur.role_id=r.role_id " + "INNER JOIN role_privileges AS rp ON r.role_id=rp.role_id " + "WHERE ur.user_id = ? " + f"AND ur.resource_id IN ({real_paramstr})", + (str(user.user_id),) + real_resource_ids + ) + _privileges_by_resource = reduce( + lambda acc, curr: { + **acc, + curr["resource_id"]: ( + acc.get(curr["resource_id"], tuple()) + + (curr["privilege_id"],)) + }, + cursor.fetchall(), + {}) + except _HTTPException as exc: + err_msg = json.loads(exc.body) + if err_msg["error"] == "missing_authorization": + cursor.execute( + "SELECT rsc.resource_id " + "FROM resources AS rsc " + "WHERE rsc.public = '1' " + f"AND rsc.resource_id IN ({real_paramstr}) ", + real_resource_ids) + _privileges_by_resource = { + row["resource_id"]: ('group:resource:view-resource',) + for row in cursor.fetchall() + } + else: + raise exc from None + + # Temp resources are always publicly viewable + if temp_resource_id in _resource_ids: + _privileges_by_resource[temp_resource_id] = ('group:resource:view-resource',) + + return jsonify({ + "authorisation": [{ + **resource, + "resource_data": [ + __explode_resource_data__(item) + for item in resource["resource_data"]], + "privileges": _privileges_by_resource.get(resource["resource_id"], tuple()) + } for resource in _all_resources.values()] + }) + def __search_mrna__(): query = __request_key__("query", "") @@ -182,12 +206,12 @@ def __search_mrna__(): ungrouped_mrna_data, gn3conn=gn3conn, search_query=query, selected=__request_key_list__("selected"), limit=limit, offset=offset) - return jsonify(with_db_connection(__ungrouped__)) + return jsonify(with_db_connection(app.config["SQL_URI"], __ungrouped__)) def __request_key__(key: str, default: Any = ""): if bool(request_json()): return request_json().get(#type: ignore[union-attr] - key, request.args.get(key, request_json().get(key, default))) + key, request.args.get(key, default)) return request.args.get(key, request_json().get(key, default)) def __request_key_list__(key: str, default: tuple[Any, ...] = tuple()): @@ -207,7 +231,7 @@ def __search_genotypes__(): ungrouped_genotype_data, gn3conn=gn3conn, search_query=query, selected=__request_key_list__("selected"), limit=limit, offset=offset) - return jsonify(with_db_connection(__ungrouped__)) + return jsonify(with_db_connection(app.config["SQL_URI"], __ungrouped__)) def __search_phenotypes__(): # launch the external process to search for phenotypes @@ -216,7 +240,7 @@ def __search_phenotypes__(): job_id = uuid.uuid4() selected = __request_key__("selected_traits", []) command =[ - sys.executable, "-m", "scripts.search_phenotypes", + sys.executable, "-m", "gn_auth.scripts.search_phenotypes", __request_key__("species_name"), __request_key__("query"), str(job_id), @@ -282,6 +306,7 @@ def link_genotypes() -> Response: return link_genotype_data(conn, group_by_id(conn, group_id), datasets) return jsonify(with_db_connection( + app.config["SQL_URI"], partial(__link__, **__values__(request_json())))) @data.route("/link/mrna", methods=["POST"]) @@ -307,9 +332,11 @@ def link_mrna() -> Response: return link_mrna_data(conn, group_by_id(conn, group_id), datasets) return jsonify(with_db_connection( + app.config["SQL_URI"], partial(__link__, **__values__(request_json())))) @data.route("/link/phenotype", methods=["POST"]) +@require_oauth("profile group resource") def link_phenotype() -> Response: """Link phenotype data to group.""" def __values__(form): @@ -325,14 +352,28 @@ def link_phenotype() -> Response: raise InvalidData("Expected at least one dataset to be provided.") return { "group_id": uuid.UUID(form["group_id"]), - "traits": form["selected"] + "traits": form["selected"], + "using_raw_ids": bool(form.get("using-raw-ids") == "on") } - with gn3db.database_connection(app.config["SQL_URI"]) as gn3conn: - def __link__(conn: db.DbConnection, group_id: uuid.UUID, - traits: tuple[dict, ...]) -> dict: - return link_phenotype_data( - conn, gn3conn, group_by_id(conn, group_id), traits) + with (require_oauth.acquire("profile group resource") as token, + gn3db.database_connection(app.config["SQL_URI"]) as gn3conn): + def __link__( + conn: db.DbConnection, + group_id: uuid.UUID, + traits: tuple[dict, ...], + using_raw_ids: bool = False + ) -> dict: + if using_raw_ids: + return link_phenotype_data(conn, + token.user, + group_by_id(conn, group_id), + traits) + return link_phenotype_data(conn, + token.user, + group_by_id(conn, group_id), + pheno_traits_from_db(gn3conn, traits)) return jsonify(with_db_connection( + app.config["SQL_URI"], partial(__link__, **__values__(request_json())))) |