From a2cb41430eb9d66f50b83c5b724172cbf90c89a2 Mon Sep 17 00:00:00 2001 From: Frederick Muriuki Muriithi Date: Thu, 23 Feb 2023 12:37:19 +0300 Subject: auth: Link data in MySQL to the groups (in SQLite) In order for the group leaders (and other resource creators) to be able to link data to the resources, the data needs to be first linked to the relevant group. This commit enables the system admin to link the data in MySQL to the groups. --- gn3/auth/authorisation/resources/data.py | 96 +++++++++++++++++++++++++++++-- gn3/auth/authorisation/resources/views.py | 34 ++++++++--- gn3/db_utils.py | 2 +- 3 files changed, 119 insertions(+), 13 deletions(-) (limited to 'gn3') diff --git a/gn3/auth/authorisation/resources/data.py b/gn3/auth/authorisation/resources/data.py index 7598f34..93b8e1d 100644 --- a/gn3/auth/authorisation/resources/data.py +++ b/gn3/auth/authorisation/resources/data.py @@ -5,7 +5,9 @@ from MySQLdb.cursors import DictCursor from gn3 import db_utils as gn3db from gn3.auth import db as authdb -from gn3.auth.authorisation.errors import InvalidData +from gn3.auth.authorisation.groups import Group +from gn3.auth.authorisation.checks import authorised_p +from gn3.auth.authorisation.errors import InvalidData, NotFoundError def __fetch_grouped_data__( conn: authdb.DbConnection, dataset_type: str) -> Sequence[dict[str, Any]]: @@ -31,7 +33,7 @@ def __fetch_ungrouped_mrna_data__( params = tuple(item["dataset_or_trait_id"] for item in grouped_data) query = f"{query} LIMIT 100 OFFSET %s" - with conn.cursor(cursorclass=DictCursor) as cursor:# type: ignore[call-arg] + with conn.cursor(DictCursor) as cursor: cursor.execute(query, (params + (offset,))) return tuple(dict(row) for row in cursor.fetchall()) @@ -49,7 +51,7 @@ def __fetch_ungrouped_geno_data__( params = tuple(item["dataset_or_trait_id"] for item in grouped_data) query = f"{query} LIMIT 100 OFFSET %s" - with conn.cursor(cursorclass=DictCursor) as cursor:# type: ignore[call-arg] + with conn.cursor(DictCursor) as cursor: cursor.execute(query, (params + (offset,))) return tuple(dict(row) for row in cursor.fetchall()) @@ -67,7 +69,7 @@ def __fetch_ungrouped_pheno_data__( params = tuple(item["dataset_or_trait_id"] for item in grouped_data) query = f"{query} LIMIT 100 OFFSET %s" - with conn.cursor(cursorclass=DictCursor) as cursor:# type: ignore[call-arg] + with conn.cursor(DictCursor) as cursor: cursor.execute(query, (params + (offset,))) return tuple(dict(row) for row in cursor.fetchall()) @@ -82,6 +84,11 @@ def __fetch_ungrouped_data__( } return fetch_fns[dataset_type](conn, ungrouped) +@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 retrieve_ungrouped_data( authconn: authdb.DbConnection, gn3conn: gn3db.Connection, @@ -92,5 +99,84 @@ def retrieve_ungrouped_data( "Requested dataset type is invalid. Expected one of " "'mrna', 'genotype' or 'phenotype'.") grouped_data = __fetch_grouped_data__(authconn, dataset_type) - print(f"GROUPED DATA: {grouped_data}") return __fetch_ungrouped_data__(gn3conn, dataset_type, grouped_data) + +def __fetch_mrna_data_by_id__(conn: gn3db.Connection, dataset_id: str) -> dict: + """Fetch mRNA Assay data by ID.""" + with conn.cursor(DictCursor) as cursor: + cursor.execute( + "SELECT psf.Id, psf.Name, psf.FullName, " + "ifiles.GN_AccesionId AS accession_id FROM ProbeSetFreeze AS psf " + "INNER JOIN InfoFiles AS ifiles ON psf.Name=ifiles.InfoPageName " + "WHERE psf.Id=%s", + (dataset_id,)) + res = cursor.fetchone() + if res: + return dict(res) + raise NotFoundError("Could not find mRNA Assay data with the given ID.") + +def __fetch_geno_data_by_id__(conn: gn3db.Connection, dataset_id: str) -> dict: + """Fetch genotype data by ID.""" + with conn.cursor(DictCursor) as cursor: + cursor.execute( + "SELECT gf.Id, gf.Name, gf.FullName, " + "ifiles.GN_AccesionId AS accession_id FROM GenoFreeze AS gf " + "INNER JOIN InfoFiles AS ifiles ON gf.Name=ifiles.InfoPageName " + "WHERE gf.Id=%s", + (dataset_id,)) + res = cursor.fetchone() + if res: + return dict(res) + raise NotFoundError("Could not find Genotype data with the given ID.") + +def __fetch_pheno_data_by_id__(conn: gn3db.Connection, dataset_id: str) -> dict: + """Fetch phenotype data by ID.""" + with conn.cursor(DictCursor) as cursor: + cursor.execute( + "SELECT pf.Id, pf.Name, pf.FullName, " + "ifiles.GN_AccesionId AS accession_id FROM PublishFreeze AS pf " + "INNER JOIN InfoFiles AS ifiles ON pf.Name=ifiles.InfoPageName " + "WHERE pf.Id=%s", + (dataset_id,)) + res = cursor.fetchone() + if res: + return dict(res) + raise NotFoundError( + "Could not find Phenotype/Publish data with the given ID.") + +def __fetch_data_by_id( + conn: gn3db.Connection, dataset_type: str, dataset_id: str) -> dict: + """Fetch data from MySQL by ID.""" + fetch_fns = { + "mrna": __fetch_mrna_data_by_id__, + "genotype": __fetch_geno_data_by_id__, + "phenotype": __fetch_pheno_data_by_id__ + } + return fetch_fns[dataset_type](conn, dataset_id) + +@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_data_to_group( + authconn: authdb.DbConnection, gn3conn: gn3db.Connection, + dataset_type: str, dataset_id: str, group: Group) -> dict: + """Link the given data to the specified group.""" + the_data = __fetch_data_by_id(gn3conn, dataset_type, dataset_id) + with authdb.cursor(authconn) as cursor: + params = { + "group_id": str(group.group_id), "dataset_type": { + "mrna": "mRNA", "genotype": "Genotype", + "phenotype": "Phenotype" + }[dataset_type], + "dataset_or_trait_id": dataset_id, "dataset_name": the_data["Name"], + "dataset_fullname": the_data["FullName"], + "accession_id": the_data["accession_id"] + } + cursor.execute( + "INSERT INTO linked_group_data VALUES" + "(:group_id, :dataset_type, :dataset_or_trait_id, :dataset_name, " + ":dataset_fullname, :accession_id)", + params) + return params diff --git a/gn3/auth/authorisation/resources/views.py b/gn3/auth/authorisation/resources/views.py index 44e2c4e..e373182 100644 --- a/gn3/auth/authorisation/resources/views.py +++ b/gn3/auth/authorisation/resources/views.py @@ -7,13 +7,13 @@ from flask import request, jsonify, Response, Blueprint, current_app as app from gn3 import db_utils as gn3dbutils from gn3.auth.db_utils import with_db_connection -from .data import retrieve_ungrouped_data +from .data import link_data_to_group, retrieve_ungrouped_data from .models import ( resource_by_id, resource_categories, resource_category_by_id, create_resource as _create_resource) -from ..errors import AuthorisationError -from ..groups.models import user_group, DUMMY_GROUP +from ..errors import InvalidData, AuthorisationError +from ..groups.models import user_group, DUMMY_GROUP, group_by_id from ... import db from ...dictify import dictify @@ -115,7 +115,27 @@ def ungrouped_data(dataset_type: str) -> Response: if dataset_type not in ("all", "mrna", "genotype", "phenotype"): raise AuthorisationError(f"Invalid dataset type {dataset_type}") - with gn3dbutils.database_connection() as gn3conn: - return jsonify(with_db_connection(partial( - retrieve_ungrouped_data, gn3conn=gn3conn, - dataset_type=dataset_type))) + with require_oauth.acquire("profile group resource") as _the_token: + with gn3dbutils.database_connection() as gn3conn: + return jsonify(with_db_connection(partial( + retrieve_ungrouped_data, gn3conn=gn3conn, + dataset_type=dataset_type))) + +@resources.route("/data/link", methods=["POST"]) +@require_oauth("profile group resource") +def link_data() -> Response: + """Link selected data to specified group.""" + with require_oauth.acquire("profile group resource") as _the_token: + form = request.form + group_id = uuid.UUID(form["group_id"]) + dataset_id = form["dataset_id"] + dataset_type = form.get("dataset_type") + if dataset_type not in ("mrna", "genotype", "phenotype"): + raise InvalidData("Unexpected dataset type requested!") + def __link__(conn: db.DbConnection): + group = group_by_id(conn, group_id) + with gn3dbutils.database_connection() as gn3conn: + return link_data_to_group( + conn, gn3conn, dataset_type, dataset_id, group) + + return jsonify(with_db_connection(__link__)) diff --git a/gn3/db_utils.py b/gn3/db_utils.py index 23a570b..ad934d7 100644 --- a/gn3/db_utils.py +++ b/gn3/db_utils.py @@ -28,7 +28,7 @@ def database_connector() -> mdb.Connection: # pylint: disable=missing-class-docstring, missing-function-docstring, too-few-public-methods class Connection(Protocol): """Type Annotation for MySQLdb's connection object""" - def cursor(self) -> Any: + def cursor(self, *args) -> Any: """A cursor in which queries may be performed""" ... -- cgit v1.2.3