diff options
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 |