2 files changed, 135 insertions, 1 deletions
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))))