aboutsummaryrefslogtreecommitdiff
path: root/gn3/auth/authorisation
diff options
context:
space:
mode:
Diffstat (limited to 'gn3/auth/authorisation')
-rw-r--r--gn3/auth/authorisation/groups/views.py93
-rw-r--r--gn3/auth/authorisation/resources/models.py16
2 files changed, 57 insertions, 52 deletions
diff --git a/gn3/auth/authorisation/groups/views.py b/gn3/auth/authorisation/groups/views.py
index 6f1a6df..d7a46c0 100644
--- a/gn3/auth/authorisation/groups/views.py
+++ b/gn3/auth/authorisation/groups/views.py
@@ -14,11 +14,11 @@ from gn3.auth.db_utils import with_db_connection
from .data import link_data_to_group, retrieve_ungrouped_data
from .models import (
- user_group, all_groups, DUMMY_GROUP, GroupRole, group_by_id, join_requests,
- group_role_by_id, GroupCreationError, accept_reject_join_request,
- group_users as _group_users, create_group as _create_group,
- add_privilege_to_group_role, delete_privilege_to_group_role,
- create_group_role as _create_group_role)
+ Group, user_group, all_groups, DUMMY_GROUP, GroupRole, group_by_id,
+ join_requests, group_role_by_id, GroupCreationError,
+ accept_reject_join_request, group_users as _group_users,
+ create_group as _create_group, add_privilege_to_group_role,
+ delete_privilege_to_group_role, create_group_role as _create_group_role)
from ..roles.models import Role
from ..checks import authorised_p
@@ -138,6 +138,42 @@ def reject_join_requests() -> Response:
accept_reject_join_request, request_id=request_id,
user=the_token.user, status="REJECTED")))
+def unlinked_mrna_data(
+ conn: db.DbConnection, group: Group) -> tuple[dict, ...]:
+ """
+ Retrieve all mRNA Assay data linked to a group but not linked to any
+ resource.
+ """
+ query = (
+ "SELECT lmd.* FROM linked_mrna_data lmd "
+ "LEFT JOIN mrna_resources mr ON lmd.data_link_id=mr.data_link_id "
+ "WHERE lmd.group_id=? AND mr.data_link_id IS NULL")
+ with db.cursor(conn) as cursor:
+ cursor.execute(query, (str(group.group_id),))
+ return tuple(dict(row) for row in cursor.fetchall())
+
+def unlinked_genotype_data(
+ conn: db.DbConnection, group: Group) -> tuple[dict, ...]:
+ """
+ Retrieve all genotype data linked to a group but not linked to any resource.
+ """
+ query = (
+ "SELECT lgd.* FROM linked_genotype_data lgd "
+ "LEFT JOIN genotype_resources gr ON lgd.data_link_id=gr.data_link_id "
+ "WHERE lgd.group_id=? AND gr.data_link_id IS NULL")
+ with db.cursor(conn) as cursor:
+ cursor.execute(query, (str(group.group_id),))
+ return tuple(dict(row) for row in cursor.fetchall())
+
+def unlinked_phenotype_data(
+ conn: db.DbConnection, group: Group) -> tuple[dict, ...]:#pylint: disable=[unused-argument]
+ """
+ Retrieve all phenotype data linked to a group but not linked to any
+ resource.
+ """
+ raise NotImplementedError(
+ "The feature to fetch unlinked phenotype data is not implemented yet.")
+
@groups.route("/<string:resource_type>/unlinked-data")
@require_oauth("profile group resource")
def unlinked_data(resource_type: str) -> Response:
@@ -147,48 +183,19 @@ def unlinked_data(resource_type: str) -> Response:
with require_oauth.acquire("profile group resource") as the_token:
db_uri = current_app.config["AUTH_DB"]
- with db.connection(db_uri) as conn, db.cursor(conn) as cursor:
+ with db.connection(db_uri) as conn:
ugroup = user_group(conn, 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 * FROM ("
- "SELECT group_id, dataset_type, dataset_or_trait_id "
- "FROM linked_group_data "
- f"EXCEPT SELECT * FROM ({except_filter})"
- f") {type_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()))
+
+ unlinked_fns = {
+ "mrna": unlinked_mrna_data,
+ "genotype": unlinked_genotype_data,
+ "phenotype": unlinked_phenotype_data
+ }
+ return jsonify(tuple(
+ dict(row) for row in unlinked_fns[resource_type](conn, ugroup)))
return jsonify(tuple())
diff --git a/gn3/auth/authorisation/resources/models.py b/gn3/auth/authorisation/resources/models.py
index afda5e8..4c71ade 100644
--- a/gn3/auth/authorisation/resources/models.py
+++ b/gn3/auth/authorisation/resources/models.py
@@ -217,9 +217,8 @@ 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 "
- "AND mr.dataset_type=lgd.dataset_type) "
+ "SELECT * FROM mrna_resources AS mr INNER JOIN linked_mrna_data AS lmr"
+ " ON mr.data_link_id=lmr.data_link_id "
"WHERE mr.resource_id=?",
(str(resource_id),))
return cursor.fetchall()
@@ -229,9 +228,8 @@ def genotype_resource_data(
"""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 "
- "AND gr.dataset_type=lgd.dataset_type) "
+ "INNER JOIN linked_genotype_data AS lgd "
+ "ON gr.data_link_id=lgd.data_link_id "
"WHERE gr.resource_id=?",
(str(resource_id),))
return cursor.fetchall()
@@ -241,9 +239,9 @@ def phenotype_resource_data(
"""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 "
- "AND pr.dataset_type=lgd.dataset_type) "
+ "INNER JOIN linked_phenotype_data AS lpd "
+ "ON (pr.trait_id=lpd.dataset_or_trait_id "
+ "AND pr.dataset_type=lpd.dataset_type) "
"WHERE pr.resource_id=?",
(str(resource_id),))
return cursor.fetchall()
generated by cgit v1.2.3 (git 2.44.0) at 2024-11-29 20:34:30 +0000