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.

3 files changed, 119 insertions, 13 deletions

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"""
         ...