From a5f83724d3075680e6d200086e3400ac938cc525 Mon Sep 17 00:00:00 2001 From: Frederick Muriuki Muriithi Date: Tue, 21 Feb 2023 16:21:23 +0300 Subject: auth: resources: Attach resource data and list unlinked data. Load the data that is attached to a particular resource together with the resource. List any unlinked data: useful when linking data to resources. --- gn3/auth/authorisation/resources/models.py | 59 +++++++++++++++++++++++++++--- gn3/auth/authorisation/resources/views.py | 51 ++++++++++++++++++++++++++ 2 files changed, 105 insertions(+), 5 deletions(-) (limited to 'gn3/auth/authorisation/resources') diff --git a/gn3/auth/authorisation/resources/models.py b/gn3/auth/authorisation/resources/models.py index 368ac1b..c15463c 100644 --- a/gn3/auth/authorisation/resources/models.py +++ b/gn3/auth/authorisation/resources/models.py @@ -1,5 +1,6 @@ """Handle the management of resources.""" import json +import sqlite3 from uuid import UUID, uuid4 from typing import Any, Dict, Sequence, NamedTuple @@ -37,6 +38,7 @@ class Resource(NamedTuple): resource_name: str resource_category: ResourceCategory public: bool + resource_data: Sequence[dict[str, Any]] = tuple() def dictify(self) -> dict[str, Any]: """Return a dict representation of `Resource` objects.""" @@ -44,7 +46,8 @@ class Resource(NamedTuple): "group": dictify(self.group), "resource_id": self.resource_id, "resource_name": self.resource_name, "resource_category": dictify(self.resource_category), - "public": self.public + "public": self.public, + "resource_data": self.resource_data } def __assign_resource_owner_role__(cursor, resource, user): @@ -190,6 +193,53 @@ def user_resources(conn: db.DbConnection, user: User) -> Sequence[Resource]: return user_group(cursor, user).map(__all_resources__).maybe(# type: ignore[arg-type,misc] public_resources(conn), lambda res: res)# type: ignore[arg-type,return-value] +def attach_resource_data(cursor: db.DbCursor, resource: Resource) -> Resource: + """Attach the linked data to the resource""" + resource_data_function = { + "mrna": mrna_resource_data, + "genotype": genotype_resource_data, + "phenotype": phenotype_resource_data + } + category = resource.resource_category + data_rows = tuple( + dict(data_row) for data_row in + resource_data_function[category.resource_category_key]( + cursor, resource.resource_id)) + return Resource( + resource.group, resource.resource_id, resource.resource_name, + resource.resource_category, resource.public, data_rows) + +def mrna_resource_data( + cursor: db.DbCursor, resource_id: UUID) -> Sequence[sqlite3.Row]: + """Fetch data linked to a mRNA resource""" + cursor.execute( + "SELECT * FROM mrna_resources AS mr INNER JOIN linked_group_data AS lgd" + " ON mr.dataset_id=lgd.dataset_or_trait_id WHERE mr.resource_id=?", + (str(resource_id),)) + return cursor.fetchall() + +def genotype_resource_data( + cursor: db.DbCursor, resource_id: UUID) -> Sequence[sqlite3.Row]: + """Fetch data linked to a Genotype resource""" + cursor.execute( + "SELECT * FROM genotype_resources AS gr " + "INNER JOIN linked_group_data AS lgd " + "ON gr.trait_id=lgd.dataset_or_trait_id " + "WHERE gr.resource_id=?", + (str(resource_id),)) + return cursor.fetchall() + +def phenotype_resource_data( + cursor: db.DbCursor, resource_id: UUID) -> Sequence[sqlite3.Row]: + """Fetch data linked to a Phenotype resource""" + cursor.execute( + "SELECT * FROM phenotype_resources AS pr " + "INNER JOIN linked_group_data AS lgd " + "ON pr.trait_id=lgd.dataset_or_trait_id " + "WHERE pr.resource_id=?", + (str(resource_id),)) + return cursor.fetchall() + def resource_by_id( conn: db.DbConnection, user: User, resource_id: UUID) -> Resource: """Retrieve a resource by its ID.""" @@ -205,11 +255,10 @@ def resource_by_id( {"id": str(resource_id)}) row = cursor.fetchone() if row: - return Resource( + return attach_resource_data(cursor, Resource( group_by_id(conn, UUID(row["group_id"])), - UUID(row["resource_id"]), - row["resource_name"], + UUID(row["resource_id"]), row["resource_name"], resource_category_by_id(conn, row["resource_category_id"]), - bool(int(row["public"]))) + bool(int(row["public"])))) raise NotFoundError(f"Could not find a resource with id '{resource_id}'") diff --git a/gn3/auth/authorisation/resources/views.py b/gn3/auth/authorisation/resources/views.py index b45a9fc..ad39df7 100644 --- a/gn3/auth/authorisation/resources/views.py +++ b/gn3/auth/authorisation/resources/views.py @@ -6,6 +6,8 @@ from .models import ( resource_by_id, resource_categories, resource_category_by_id, create_resource as _create_resource) +from ..groups.models import user_group, DUMMY_GROUP + from ... import db from ...dictify import dictify from ...authentication.oauth2.resource_server import require_oauth @@ -46,3 +48,52 @@ def view_resource(resource_id: uuid.UUID) -> Response: with db.connection(db_uri) as conn: return jsonify(dictify(resource_by_id( conn, the_token.user, resource_id))) + +@resources.route("//unlinked-data") +@require_oauth("profile group resource") +def unlinked_data(resource_type: str) -> Response: + """View unlinked data""" + with require_oauth.acquire("profile group resource") as the_token: + db_uri = app.config["AUTH_DB"] + with db.connection(db_uri) as conn, db.cursor(conn) as cursor: + ugroup = user_group(cursor, the_token.user).maybe(# type: ignore[misc] + DUMMY_GROUP, lambda grp: grp) + if ugroup == DUMMY_GROUP: + return jsonify(tuple()) + type_filter = { + "all": "", + "mrna": 'WHERE dataset_type="mRNA"', + "genotype": 'WHERE dataset_type="Genotype"', + "phenotype": 'WHERE dataset_type="Phenotype"' + }[resource_type] + + except_filter = ( + "SELECT group_id, dataset_type, " + "dataset_id AS dataset_or_trait_id FROM mrna_resources " + "UNION " + "SELECT group_id, dataset_type, " + "trait_id AS dataset_or_trait_id FROM genotype_resources " + "UNION " + "SELECT group_id, dataset_type, " + "trait_id AS dataset_or_trait_id FROM phenotype_resources") + + ids_query = ("SELECT group_id, dataset_type, dataset_or_trait_id " + "FROM linked_group_data " + f"{type_filter} " + f"EXCEPT {except_filter} ") + cursor.execute(ids_query) + ids = cursor.fetchall() + + if ids: + clause = ", ".join(["(?, ?, ?)"] * len(ids)) + data_query = ( + "SELECT * FROM linked_group_data " + "WHERE (group_id, dataset_type, dataset_or_trait_id) " + f"IN (VALUES {clause})") + params = tuple(item for sublist in + ((row[0], row[1], row[2]) for row in ids) + for item in sublist) + cursor.execute(data_query, params) + return jsonify(tuple(dict(row) for row in cursor.fetchall())) + + return jsonify(tuple()) -- cgit v1.2.3