3 files changed, 90 insertions, 2 deletions
diff --git a/gn3/auth/authorisation/data/genotypes.py b/gn3/auth/authorisation/data/genotypes.py
new file mode 100644
index 0000000..690293c
--- /dev/null
+++ b/gn3/auth/authorisation/data/genotypes.py
@@ -0,0 +1,57 @@
+"""Handle linking of Genotype data to the Auth(entic|oris)ation system."""
+from typing import Iterable
+
+from MySQLdb.cursors import DictCursor
+
+import gn3.auth.db as authdb
+import gn3.db_utils as gn3db
+from gn3.auth.authorisation.checks import authorised_p
+
+def linked_genotype_data(conn: authdb.DbConnection) -> Iterable[dict]:
+    """Retrive genotype data that is linked to user groups."""
+    with authdb.cursor(conn) as cursor:
+        cursor.execute("SELECT * FROM linked_genotype_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_genotype_data(
+        authconn: authdb.DbConnection, gn3conn: gn3db.Connection,
+        search_query: str, 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))
+    query = (
+        "SELECT s.SpeciesId, iset.InbredSetId, iset.InbredSetName, "
+        "gf.Id AS GenoFreezeId, gf.Name AS dataset_name, "
+        "gf.FullName AS dataset_fullname, "
+        "gf.ShortName AS dataset_shortname "
+        "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(["(?, ?, ?)"] * len(params))
+        query = query + (
+            "(s.SpeciesId, iset.InbredSetId, GenoFreezeId) "
+            f"NOT IN ({paramstr}) "
+            "AND ")
+
+    if bool(search_query):
+        query = query + (
+            "CONCAT(gf.Name, ' ', gf.FullName, ' ', gf.ShortName) LIKE '%%?%%' ")
+        params = params + ((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())
diff --git a/gn3/auth/authorisation/data/phenotypes.py b/gn3/auth/authorisation/data/phenotypes.py
index da1e2f2..0d018cf 100644
--- a/gn3/auth/authorisation/data/phenotypes.py
+++ b/gn3/auth/authorisation/data/phenotypes.py
@@ -23,7 +23,7 @@ def ungrouped_phenotype_data(
             (row["SpeciesId"], row["InbredSetId"], row["PublishFreezeId"],
              row["PublishXRefId"])
             for row in linked_phenotype_data(authconn))
-        paramstr = ", ".join(["(?, ?, ? ?)"] * len(params))
+        paramstr = ", ".join(["(?, ?, ?, ?)"] * len(params))
         query = (
             "SELECT spc.SpeciesId, iset.InbredSetId, pf.Id AS PublishFreezeId, "
             "pf.Name AS dataset_name, pf.FullName AS dataset_fullname, "
diff --git a/gn3/auth/authorisation/data/views.py b/gn3/auth/authorisation/data/views.py
index b1550d7..70b14d7 100644
--- a/gn3/auth/authorisation/data/views.py
+++ b/gn3/auth/authorisation/data/views.py
@@ -5,7 +5,7 @@ import json
 import random
 import string
 import datetime
-from functools import reduce
+from functools import reduce, partial
 from typing import Sequence, Iterable
 
 import redis
@@ -19,6 +19,7 @@ from gn3.db.traits import build_trait_name
 
 from gn3.auth import db
 from gn3.auth.dictify import dictify
+from gn3.auth.db_utils import with_db_connection
 
 from gn3.auth.authorisation.errors import NotFoundError
 
@@ -39,6 +40,8 @@ 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.genotypes import ungrouped_genotype_data
+
 data = Blueprint("data", __name__)
 
 @data.route("species")
@@ -305,3 +308,31 @@ def migrate_users_data() -> Response:
                 "The data migration service is currently unavailable.")
         }),
         status=500, mimetype="application/json")
+
+def __search_mrna__():
+    pass
+
+def __search_genotypes__():
+    query = request.form.get("query", request.args.get("query", ""))
+    limit = int(request.form.get("limit", request.args.get("limit", 10000)))
+    offset = int(request.form.get("offset", request.args.get("offset", 0)))
+    with gn3db.database_connection() as gn3conn:
+        __ungrouped__ = partial(
+            ungrouped_genotype_data, gn3conn=gn3conn, search_query=query,
+            limit=limit, offset=offset)
+        return jsonify(with_db_connection(__ungrouped__))
+
+def __search_phenotypes__():
+    pass
+
+@data.route("/search", methods=["GET"])
+@require_oauth("profile group resource")
+def search_unlinked_data():
+    """Search for various unlinked data."""
+    dataset_type = request.form["dataset_type"]
+    search_fns = {
+        "mrna": __search_mrna__,
+        "genotype": __search_genotypes__,
+        "phenotype": __search_phenotypes__
+    }
+    return search_fns[dataset_type]()