auth: Attach linked data to specific resources.

6 files changed, 57 insertions, 41 deletions

diff --git a/gn3/auth/authorisation/groups/views.py b/gn3/auth/authorisation/groups/views.py
index 202df95..9e717a9 100644
--- a/gn3/auth/authorisation/groups/views.py
+++ b/gn3/auth/authorisation/groups/views.py
@@ -180,12 +180,16 @@ def unlinked_phenotype_data(
             "ON lpd.data_link_id=pr.data_link_id "
             "WHERE lpd.group_id=? AND pr.data_link_id IS NULL",
             (str(group.group_id),))
-        ids = tuple((
-            row["SpeciesId"], row["InbredSetId"], row["PublishFreezeId"],
-            row["PublishXRefId"]) for row in authcur.fetchall())
-        if len(ids) < 1:
+        results = authcur.fetchall()
+        ids: dict[tuple[str, ...], str] = {
+            (
+                row["SpeciesId"], row["InbredSetId"], row["PublishFreezeId"],
+                row["PublishXRefId"]): row["data_link_id"]
+            for row in results
+        }
+        if len(ids.keys()) < 1:
             return tuple()
-        paramstr = ", ".join(["(%s, %s, %s, %s)"] * len(ids))
+        paramstr = ", ".join(["(%s, %s, %s, %s)"] * len(ids.keys()))
         gn3cur.execute(
             "SELECT spc.SpeciesId, spc.SpeciesName, iset.InbredSetId, "
             "iset.InbredSetName, pf.Id AS PublishFreezeId, "
@@ -208,7 +212,7 @@ def unlinked_phenotype_data(
             "ON pxr.PhenotypeId=phen.Id "
             "WHERE (spc.SpeciesId, iset.InbredSetId, pf.Id, pxr.Id) "
             f"IN ({paramstr})",
-            tuple(item for sublist in ids for item in sublist))
+            tuple(item for sublist in ids.keys() for item in sublist))
         return tuple({
             **{key: value for key, value in row.items() if key not in
                ("Post_publication_description", "Pre_publication_description",
@@ -216,7 +220,10 @@ def unlinked_phenotype_data(
             "description": (
                 row["Post_publication_description"] or
                 row["Pre_publication_description"] or
-                row["Original_description"])
+                row["Original_description"]),
+            "data_link_id": ids[tuple(str(row[key]) for key in (
+                "SpeciesId", "InbredSetId", "PublishFreezeId",
+                "PublishXRefId"))]
         } for row in gn3cur.fetchall())
 
 @groups.route("/<string:resource_type>/unlinked-data")
diff --git a/gn3/auth/authorisation/resources/models.py b/gn3/auth/authorisation/resources/models.py
index 2e2ff53..bc88691 100644
--- a/gn3/auth/authorisation/resources/models.py
+++ b/gn3/auth/authorisation/resources/models.py
@@ -269,56 +269,53 @@ def resource_by_id(
     raise NotFoundError(f"Could not find a resource with id '{resource_id}'")
 
 def __link_mrna_data_to_resource__(
-        conn: db.DbConnection, resource: Resource, dataset_id: str) -> dict:
+        conn: db.DbConnection, resource: Resource, data_link_id: UUID) -> dict:
     """Link mRNA Assay data with a resource."""
     with db.cursor(conn) as cursor:
         params = {
             "group_id": str(resource.group.group_id),
             "resource_id": str(resource.resource_id),
-            "dataset_type": "mRNA",
-            "dataset_id": dataset_id
+            "data_link_id": str(data_link_id)
         }
         cursor.execute(
             "INSERT INTO mrna_resources VALUES"
-            "(:group_id, :resource_id, :dataset_type, :dataset_id)",
+            "(:group_id, :resource_id, :data_link_id)",
             params)
         return params
 
 def __link_geno_data_to_resource__(
-        conn: db.DbConnection, resource: Resource, dataset_id: str) -> dict:
+        conn: db.DbConnection, resource: Resource, data_link_id: UUID) -> dict:
     """Link Genotype data with a resource."""
     with db.cursor(conn) as cursor:
         params = {
             "group_id": str(resource.group.group_id),
             "resource_id": str(resource.resource_id),
-            "dataset_type": "Genotype",
-            "trait_id": dataset_id
+            "data_link_id": str(data_link_id)
         }
         cursor.execute(
             "INSERT INTO genotype_resources VALUES"
-            "(:group_id, :resource_id, :dataset_type, :trait_id)",
+            "(:group_id, :resource_id, :data_link_id)",
             params)
         return params
 
 def __link_pheno_data_to_resource__(
-        conn: db.DbConnection, resource: Resource, dataset_id: str) -> dict:
+        conn: db.DbConnection, resource: Resource, data_link_id: UUID) -> dict:
     """Link Phenotype data with a resource."""
     with db.cursor(conn) as cursor:
         params = {
             "group_id": str(resource.group.group_id),
             "resource_id": str(resource.resource_id),
-            "dataset_type": "Phenotype",
-            "trait_id": dataset_id
+            "data_link_id": str(data_link_id)
         }
         cursor.execute(
             "INSERT INTO phenotype_resources VALUES"
-            "(:group_id, :resource_id, :dataset_type, :trait_id)",
+            "(:group_id, :resource_id, :data_link_id)",
             params)
         return params
 
 def link_data_to_resource(
         conn: db.DbConnection, user: User, resource_id: UUID, dataset_type: str,
-        dataset_id: str):
+        data_link_id: UUID) -> dict:
     """Link data to resource."""
     if not authorised_for(
             conn, user, ("group:resource:edit-resource",),
@@ -333,49 +330,49 @@ def link_data_to_resource(
         "mrna": __link_mrna_data_to_resource__,
         "genotype": __link_geno_data_to_resource__,
         "phenotype": __link_pheno_data_to_resource__,
-    }[dataset_type.lower()](conn, resource, dataset_id)
+    }[dataset_type.lower()](conn, resource, data_link_id)
 
 def __unlink_mrna_data_to_resource__(
-        conn: db.DbConnection, resource: Resource, dataset_id: str) -> dict:
+        conn: db.DbConnection, resource: Resource, data_link_id: UUID) -> dict:
     """Unlink data from mRNA Assay resources"""
     with db.cursor(conn) as cursor:
         cursor.execute("DELETE FROM mrna_resources "
-                       "WHERE resource_id=? AND dataset_id=?",
-                       (str(resource.resource_id), dataset_id))
+                       "WHERE resource_id=? AND data_link_id=?",
+                       (str(resource.resource_id), str(data_link_id)))
         return {
             "resource_id": str(resource.resource_id),
             "dataset_type": resource.resource_category.resource_category_key,
-            "dataset_id": dataset_id
+            "data_link_id": data_link_id
         }
 
 def __unlink_geno_data_to_resource__(
-        conn: db.DbConnection, resource: Resource, trait_id: str) -> dict:
+        conn: db.DbConnection, resource: Resource, data_link_id: UUID) -> dict:
     """Unlink data from Genotype resources"""
     with db.cursor(conn) as cursor:
         cursor.execute("DELETE FROM genotype_resources "
-                       "WHERE resource_id=? AND trait_id=?",
-                       (str(resource.resource_id), trait_id))
+                       "WHERE resource_id=? AND data_link_id=?",
+                       (str(resource.resource_id), str(data_link_id)))
         return {
             "resource_id": str(resource.resource_id),
             "dataset_type": resource.resource_category.resource_category_key,
-            "dataset_id": trait_id
+            "data_link_id": data_link_id
         }
 
 def __unlink_pheno_data_to_resource__(
-        conn: db.DbConnection, resource: Resource, trait_id: str) -> dict:
+        conn: db.DbConnection, resource: Resource, data_link_id: UUID) -> dict:
     """Unlink data from Phenotype resources"""
     with db.cursor(conn) as cursor:
         cursor.execute("DELETE FROM phenotype_resources "
-                       "WHERE resource_id=? AND trait_id=?",
-                       (str(resource.resource_id), trait_id))
+                       "WHERE resource_id=? AND data_link_id=?",
+                       (str(resource.resource_id), str(data_link_id)))
         return {
             "resource_id": str(resource.resource_id),
             "dataset_type": resource.resource_category.resource_category_key,
-            "dataset_id": trait_id
+            "data_link_id": str(data_link_id)
         }
 
 def unlink_data_from_resource(
-        conn: db.DbConnection, user: User, resource_id: UUID, dataset_id: str):
+        conn: db.DbConnection, user: User, resource_id: UUID, data_link_id: UUID):
     """Unlink data from resource."""
     if not authorised_for(
             conn, user, ("group:resource:edit-resource",),
@@ -391,7 +388,7 @@ def unlink_data_from_resource(
         "mrna": __unlink_mrna_data_to_resource__,
         "genotype": __unlink_geno_data_to_resource__,
         "phenotype": __unlink_pheno_data_to_resource__,
-    }[dataset_type.lower()](conn, resource, dataset_id)
+    }[dataset_type.lower()](conn, resource, data_link_id)
 
 def organise_resources_by_category(resources: Sequence[Resource]) -> dict[
         ResourceCategory, tuple[Resource]]:
diff --git a/gn3/auth/authorisation/resources/views.py b/gn3/auth/authorisation/resources/views.py
index 5615b11..7b647d3 100644
--- a/gn3/auth/authorisation/resources/views.py
+++ b/gn3/auth/authorisation/resources/views.py
@@ -77,7 +77,7 @@ def link_data():
     try:
         form = request.form
         assert "resource_id" in form, "Resource ID not provided."
-        assert "dataset_id" in form, "Dataset ID not provided."
+        assert "data_link_id" in form, "Data Link ID not provided."
         assert "dataset_type" in form, "Dataset type not specified"
         assert form["dataset_type"].lower() in (
             "mrna", "genotype", "phenotype"), "Invalid dataset type provided."
@@ -86,7 +86,7 @@ def link_data():
             def __link__(conn: db.DbConnection):
                 return link_data_to_resource(
                     conn, the_token.user, uuid.UUID(form["resource_id"]),
-                    form["dataset_type"], form["dataset_id"])
+                    form["dataset_type"], uuid.UUID(form["data_link_id"]))
 
             return jsonify(with_db_connection(__link__))
     except AssertionError as aserr:
@@ -101,13 +101,13 @@ def unlink_data():
     try:
         form = request.form
         assert "resource_id" in form, "Resource ID not provided."
-        assert "dataset_id" in form, "Dataset ID not provided."
+        assert "data_link_id" in form, "Data Link ID not provided."
 
         with require_oauth.acquire("profile group resource") as the_token:
             def __unlink__(conn: db.DbConnection):
                 return unlink_data_from_resource(
                     conn, the_token.user, uuid.UUID(form["resource_id"]),
-                    form["dataset_id"])
+                    uuid.UUID(form["data_link_id"]))
             return jsonify(with_db_connection(__unlink__))
     except AssertionError as aserr:
         raise InvalidData(aserr.args[0]) from aserr
diff --git a/migrations/auth/20230322_02_Ll854-create-phenotype-resources-table.py b/migrations/auth/20230322_02_Ll854-create-phenotype-resources-table.py
index 19ee885..7c9e986 100644
--- a/migrations/auth/20230322_02_Ll854-create-phenotype-resources-table.py
+++ b/migrations/auth/20230322_02_Ll854-create-phenotype-resources-table.py
@@ -12,10 +12,14 @@ steps = [
         CREATE TABLE IF NOT EXISTS phenotype_resources
         -- Link phenotype data to specific resources
         (
+          group_id TEXT NOT NULL,
           resource_id TEXT NOT NULL, -- A resource can have multiple data items
           data_link_id TEXT NOT NULL,
-          PRIMARY KEY(resource_id, data_link_id),
+          PRIMARY KEY(group_id, resource_id, data_link_id),
           UNIQUE (data_link_id), -- ensure data is linked to only one resource
+          FOREIGN KEY (group_id, resource_id)
+            REFERENCES resources(group_id, resource_id)
+            ON UPDATE CASCADE ON DELETE RESTRICT,
           FOREIGN KEY (data_link_id)
             REFERENCES linked_phenotype_data(data_link_id)
             ON UPDATE CASCADE ON DELETE RESTRICT
diff --git a/migrations/auth/20230404_02_la33P-create-genotype-resources-table.py b/migrations/auth/20230404_02_la33P-create-genotype-resources-table.py
index e967ce6..1a865e0 100644
--- a/migrations/auth/20230404_02_la33P-create-genotype-resources-table.py
+++ b/migrations/auth/20230404_02_la33P-create-genotype-resources-table.py
@@ -12,10 +12,14 @@ steps = [
         CREATE TABLE IF NOT EXISTS genotype_resources
         -- Link genotype data to specific resource
         (
+          group_id TEXT NOT NULL,
           resource_id TEXT NOT NULL, -- A resource can have multiple items
           data_link_id TEXT NOT NULL,
-          PRIMARY KEY (resource_id, data_link_id),
+          PRIMARY KEY (group_id, resource_id, data_link_id),
           UNIQUE (data_link_id) -- ensure data is linked to single resource
+          FOREIGN KEY (group_id, resource_id)
+            REFERENCES resources(group_id, resource_id)
+            ON UPDATE CASCADE ON DELETE RESTRICT,
           FOREIGN KEY (data_link_id)
             REFERENCES linked_genotype_data(data_link_id)
             ON UPDATE CASCADE ON DELETE RESTRICT
diff --git a/migrations/auth/20230410_02_WZqSf-create-mrna-resources-table.py b/migrations/auth/20230410_02_WZqSf-create-mrna-resources-table.py
index de348c0..2ad1056 100644
--- a/migrations/auth/20230410_02_WZqSf-create-mrna-resources-table.py
+++ b/migrations/auth/20230410_02_WZqSf-create-mrna-resources-table.py
@@ -12,10 +12,14 @@ steps = [
         CREATE TABLE IF NOT EXISTS mrna_resources
         -- Link mRNA data to specific resource
         (
+          group_id TEXT NOT NULL,
           resource_id TEXT NOT NULL, -- A resource can have multiple items
           data_link_id TEXT NOT NULL,
           PRIMARY KEY (resource_id, data_link_id),
           UNIQUE (data_link_id) -- ensure data is linked to single resource
+          FOREIGN KEY (group_id, resource_id)
+            REFERENCES resources(group_id, resource_id)
+            ON UPDATE CASCADE ON DELETE RESTRICT,
           FOREIGN KEY (data_link_id) REFERENCES linked_mrna_data(data_link_id)
             ON UPDATE CASCADE ON DELETE RESTRICT
         ) WITHOUT ROWID