aboutsummaryrefslogtreecommitdiff
diff options
context:
space:
mode:
authorFrederick Muriuki Muriithi2023-02-21 16:21:23 +0300
committerFrederick Muriuki Muriithi2023-02-21 16:24:30 +0300
commita5f83724d3075680e6d200086e3400ac938cc525 (patch)
tree4bc983fef56554708b333dcbbafe78ea7a6dcd1e
parent08e8e36e256d1893967c98b366395279b39c1e72 (diff)
downloadgenenetwork3-a5f83724d3075680e6d200086e3400ac938cc525.tar.gz
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.
-rw-r--r--gn3/auth/authorisation/resources/models.py59
-rw-r--r--gn3/auth/authorisation/resources/views.py51
-rw-r--r--migrations/auth/20230216_01_dgWjv-create-linked-group-data-table.py10
-rw-r--r--migrations/auth/20230216_02_0ZHSl-make-dataset-id-and-trait-id-foreign-keys-in-tables.py57
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