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.

4 files changed, 150 insertions, 27 deletions

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("/<string:resource_type>/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())
diff --git a/migrations/auth/20230216_01_dgWjv-create-linked-group-data-table.py b/migrations/auth/20230216_01_dgWjv-create-linked-group-data-table.py
index b54942c..890eeef 100644
--- a/migrations/auth/20230216_01_dgWjv-create-linked-group-data-table.py
+++ b/migrations/auth/20230216_01_dgWjv-create-linked-group-data-table.py
@@ -11,13 +11,15 @@ steps = [
         """
         CREATE TABLE IF NOT EXISTS linked_group_data(
           group_id TEXT NOT NULL,
+          dataset_type TEXT NOT NULL,
           dataset_or_trait_id TEXT NOT NULL,
-          name TEXT NOT NULL,
-          type TEXT NOT NULL,
-          PRIMARY KEY(group_id, dataset_or_trait_id),
+          dataset_name TEXT NOT NULL,
+          dataset_fullname TEXT NOT NULL,
+          accession_id TEXT DEFAULT NULL,
+          PRIMARY KEY(group_id, dataset_type, dataset_or_trait_id),
           FOREIGN KEY (group_id) REFERENCES groups(group_id)
             ON UPDATE CASCADE ON DELETE RESTRICT,
-          CHECK (type IN ('mRNA', 'Genotype', 'Phenotype'))
+          CHECK (dataset_type IN ('mRNA', 'Genotype', 'Phenotype'))
         ) WITHOUT ROWID
         """,
         "DROP TABLE IF EXISTS linked_group_data")
diff --git a/migrations/auth/20230216_02_0ZHSl-make-dataset-id-and-trait-id-foreign-keys-in-tables.py b/migrations/auth/20230216_02_0ZHSl-make-dataset-id-and-trait-id-foreign-keys-in-tables.py
index d6dc4a9..b8a57fc 100644
--- a/migrations/auth/20230216_02_0ZHSl-make-dataset-id-and-trait-id-foreign-keys-in-tables.py
+++ b/migrations/auth/20230216_02_0ZHSl-make-dataset-id-and-trait-id-foreign-keys-in-tables.py
@@ -26,20 +26,25 @@ def add_foreign_key_to_mrna_resources(conn):
             CREATE TABLE IF NOT EXISTS mrna_resources(
               group_id TEXT NOT NULL,
               resource_id TEXT PRIMARY KEY,
+              dataset_type TEXT NOT NULL DEFAULT "mRNA"
+                CHECK (dataset_type="mRNA"),
               dataset_id TEXT NOT NULL UNIQUE,
               FOREIGN KEY(group_id, resource_id)
                 REFERENCES resources(group_id, resource_id)
                 ON UPDATE CASCADE ON DELETE RESTRICT,
-              FOREIGN KEY (dataset_id)
-                REFERENCES linked_group_data(dataset_or_trait_id)
+              FOREIGN KEY (group_id, dataset_type, dataset_id)
+                REFERENCES
+                  linked_group_data(group_id, dataset_type, dataset_or_trait_id)
                 ON UPDATE CASCADE ON DELETE CASCADE
               ) WITHOUT ROWID
             """)
         cursor.execute(
             "SELECT group_id, resource_id, dataset_id FROM mrna_resources_bkp")
         rows = ((row[0], row[1], row[2]) for row in cursor.fetchall())
-        cursor.executemany("INSERT INTO mrna_resources VALUES (?, ?, ?)",
-                           rows)
+        cursor.executemany(
+            "INSERT INTO mrna_resources(group_id, resource_id, dataset_id) "
+            "VALUES (?, ?, ?)",
+            rows)
         cursor.execute("DROP TABLE mrna_resources_bkp")
 
 def drop_foreign_key_from_mrna_resources(conn):
@@ -61,8 +66,10 @@ def drop_foreign_key_from_mrna_resources(conn):
         cursor.execute(
             "SELECT group_id, resource_id, dataset_id FROM mrna_resources_bkp")
         rows = ((row[0], row[1], row[2]) for row in cursor.fetchall())
-        cursor.executemany("INSERT INTO mrna_resources VALUES (?, ?, ?)",
-                           rows)
+        cursor.executemany(
+            "INSERT INTO mrna_resources(group_id, resource_id, dataset_id) "
+            "VALUES (?, ?, ?)",
+            rows)
         cursor.execute("DROP TABLE mrna_resources_bkp")
 
 def add_foreign_key_to_geno_resources(conn):
@@ -75,12 +82,15 @@ def add_foreign_key_to_geno_resources(conn):
             CREATE TABLE IF NOT EXISTS genotype_resources(
               group_id TEXT NOT NULL,
               resource_id TEXT PRIMARY KEY,
+              dataset_type TEXT NOT NULL DEFAULT "Genotype"
+                CHECK (dataset_type="Genotype"),
               trait_id TEXT NOT NULL UNIQUE,
               FOREIGN KEY(group_id, resource_id)
                 REFERENCES resources(group_id, resource_id)
                 ON UPDATE CASCADE ON DELETE RESTRICT,
-              FOREIGN KEY (trait_id)
-                REFERENCES linked_group_data(dataset_or_trait_id)
+              FOREIGN KEY (group_id, dataset_type, trait_id)
+                REFERENCES
+                  linked_group_data(group_id, dataset_type, dataset_or_trait_id)
                 ON UPDATE CASCADE ON DELETE CASCADE
               ) WITHOUT ROWID
             """)
@@ -88,8 +98,10 @@ def add_foreign_key_to_geno_resources(conn):
             "SELECT group_id, resource_id, trait_id "
             "FROM genotype_resources_bkp")
         rows = ((row[0], row[1], row[2]) for row in cursor.fetchall())
-        cursor.executemany("INSERT INTO genotype_resources VALUES (?, ?, ?)",
-                           rows)
+        cursor.executemany(
+            "INSERT INTO genotype_resources(group_id, resource_id, trait_id) "
+            "VALUES (?, ?, ?)",
+            rows)
         cursor.execute("DROP TABLE genotype_resources_bkp")
 
 def drop_foreign_key_from_geno_resources(conn):
@@ -112,8 +124,10 @@ def drop_foreign_key_from_geno_resources(conn):
             "SELECT group_id, resource_id, trait_id "
             "FROM genotype_resources_bkp")
         rows = ((row[0], row[1], row[2]) for row in cursor.fetchall())
-        cursor.executemany("INSERT INTO genotype_resources VALUES (?, ?, ?)",
-                           rows)
+        cursor.executemany(
+            "INSERT INTO genotype_resources(group_id, resource_id, trait_id) "
+            "VALUES (?, ?, ?)",
+            rows)
         cursor.execute("DROP TABLE genotype_resources_bkp")
 
 def add_foreign_key_to_pheno_resources(conn):
@@ -126,12 +140,15 @@ def add_foreign_key_to_pheno_resources(conn):
             CREATE TABLE IF NOT EXISTS phenotype_resources(
               group_id TEXT NOT NULL,
               resource_id TEXT PRIMARY KEY,
+              dataset_type TEXT NOT NULL DEFAULT "Phenotype"
+                CHECK (dataset_type="Phenotype"),
               trait_id TEXT NOT NULL UNIQUE,
               FOREIGN KEY(group_id, resource_id)
                 REFERENCES resources(group_id, resource_id)
                 ON UPDATE CASCADE ON DELETE RESTRICT,
-              FOREIGN KEY (trait_id)
-                REFERENCES linked_group_data(dataset_or_trait_id)
+              FOREIGN KEY (group_id, dataset_type, trait_id)
+                REFERENCES
+                  linked_group_data(group_id, dataset_type, dataset_or_trait_id)
                 ON UPDATE CASCADE ON DELETE CASCADE
               ) WITHOUT ROWID
             """)
@@ -139,8 +156,10 @@ def add_foreign_key_to_pheno_resources(conn):
             "SELECT group_id, resource_id, trait_id "
             "FROM phenotype_resources_bkp")
         rows = ((row[0], row[1], row[2]) for row in cursor.fetchall())
-        cursor.executemany("INSERT INTO phenotype_resources VALUES (?, ?, ?)",
-                           rows)
+        cursor.executemany(
+            "INSERT INTO phenotype_resources(group_id, resource_id, trait_id) "
+            "VALUES (?, ?, ?)",
+            rows)
         cursor.execute("DROP TABLE phenotype_resources_bkp")
 
 def drop_foreign_key_from_pheno_resources(conn):
@@ -163,8 +182,10 @@ def drop_foreign_key_from_pheno_resources(conn):
             "SELECT group_id, resource_id, trait_id "
             "FROM phenotype_resources_bkp")
         rows = ((row[0], row[1], row[2]) for row in cursor.fetchall())
-        cursor.executemany("INSERT INTO phenotype_resources VALUES (?, ?, ?)",
-                           rows)
+        cursor.executemany(
+            "INSERT INTO phenotype_resources(group_id, resource_id, trait_id) "
+            "VALUES (?, ?, ?)",
+            rows)
         cursor.execute("DROP TABLE phenotype_resources_bkp")
 
 from yoyo import step