aboutsummaryrefslogtreecommitdiff
path: root/gn3
diff options
context:
space:
mode:
Diffstat (limited to 'gn3')
-rw-r--r--gn3/auth/authorisation/groups/views.py68
-rw-r--r--gn3/auth/authorisation/resources/models.py3
2 files changed, 60 insertions, 11 deletions
diff --git a/gn3/auth/authorisation/groups/views.py b/gn3/auth/authorisation/groups/views.py
index 0ff2903..202df95 100644
--- a/gn3/auth/authorisation/groups/views.py
+++ b/gn3/auth/authorisation/groups/views.py
@@ -4,10 +4,11 @@ import datetime
from typing import Iterable
from functools import partial
+from MySQLdb.cursors import DictCursor
from flask import request, jsonify, Response, Blueprint, current_app
from gn3.auth import db
-from gn3 import db_utils as gn3dbutils
+from gn3 import db_utils as gn3db
from gn3.auth.dictify import dictify
from gn3.auth.db_utils import with_db_connection
@@ -166,13 +167,57 @@ def unlinked_genotype_data(
return tuple(dict(row) for row in cursor.fetchall())
def unlinked_phenotype_data(
- conn: db.DbConnection, group: Group) -> tuple[dict, ...]:#pylint: disable=[unused-argument]
+ authconn: db.DbConnection, gn3conn: gn3db.Connection,
+ group: Group) -> tuple[dict, ...]:
"""
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.")
+ with db.cursor(authconn) as authcur, gn3conn.cursor(DictCursor) as gn3cur:
+ authcur.execute(
+ "SELECT lpd.* FROM linked_phenotype_data AS lpd "
+ "LEFT JOIN phenotype_resources AS pr "
+ "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:
+ return tuple()
+ paramstr = ", ".join(["(%s, %s, %s, %s)"] * len(ids))
+ gn3cur.execute(
+ "SELECT spc.SpeciesId, spc.SpeciesName, iset.InbredSetId, "
+ "iset.InbredSetName, pf.Id AS PublishFreezeId, "
+ "pf.Name AS dataset_name, pf.FullName AS dataset_fullname, "
+ "pf.ShortName AS dataset_shortname, pxr.Id AS PublishXRefId, "
+ "pub.PubMed_ID, pub.Title, pub.Year, "
+ "phen.Pre_publication_description, "
+ "phen.Post_publication_description, phen.Original_description "
+ "FROM "
+ "Species AS spc "
+ "INNER JOIN InbredSet AS iset "
+ "ON spc.SpeciesId=iset.SpeciesId "
+ "INNER JOIN PublishFreeze AS pf "
+ "ON iset.InbredSetId=pf.InbredSetId "
+ "INNER JOIN PublishXRef AS pxr "
+ "ON pf.InbredSetId=pxr.InbredSetId "
+ "INNER JOIN Publication AS pub "
+ "ON pxr.PublicationId=pub.Id "
+ "INNER JOIN Phenotype AS phen "
+ "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))
+ return tuple({
+ **{key: value for key, value in row.items() if key not in
+ ("Post_publication_description", "Pre_publication_description",
+ "Original_description")},
+ "description": (
+ row["Post_publication_description"] or
+ row["Pre_publication_description"] or
+ row["Original_description"])
+ } for row in gn3cur.fetchall())
@groups.route("/<string:resource_type>/unlinked-data")
@require_oauth("profile group resource")
@@ -183,8 +228,10 @@ 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:
- ugroup = user_group(conn, the_token.user).maybe(# type: ignore[misc]
+ gn3db_uri = current_app.config["SQL_URI"]
+ with (db.connection(db_uri) as authconn,
+ gn3db.database_connection(gn3db_uri) as gn3conn):
+ ugroup = user_group(authconn, the_token.user).maybe(# type: ignore[misc]
DUMMY_GROUP, lambda grp: grp)
if ugroup == DUMMY_GROUP:
return jsonify(tuple())
@@ -192,10 +239,13 @@ def unlinked_data(resource_type: str) -> Response:
unlinked_fns = {
"mrna": unlinked_mrna_data,
"genotype": unlinked_genotype_data,
- "phenotype": unlinked_phenotype_data
+ "phenotype": lambda conn, grp: partial(
+ unlinked_phenotype_data, gn3conn=gn3conn)(
+ authconn=conn, group=grp)
}
return jsonify(tuple(
- dict(row) for row in unlinked_fns[resource_type](conn, ugroup)))
+ dict(row) for row in unlinked_fns[resource_type](
+ authconn, ugroup)))
return jsonify(tuple())
@@ -212,7 +262,7 @@ def link_data() -> Response:
raise InvalidData("Unexpected dataset type requested!")
def __link__(conn: db.DbConnection):
group = group_by_id(conn, group_id)
- with gn3dbutils.database_connection(current_app.config["SQL_URI"]) as gn3conn:
+ with gn3db.database_connection(current_app.config["SQL_URI"]) as gn3conn:
return link_data_to_group(
conn, gn3conn, dataset_type, dataset_ids, group)
diff --git a/gn3/auth/authorisation/resources/models.py b/gn3/auth/authorisation/resources/models.py
index 4c71ade..2e2ff53 100644
--- a/gn3/auth/authorisation/resources/models.py
+++ b/gn3/auth/authorisation/resources/models.py
@@ -240,8 +240,7 @@ def phenotype_resource_data(
cursor.execute(
"SELECT * FROM phenotype_resources AS pr "
"INNER JOIN linked_phenotype_data AS lpd "
- "ON (pr.trait_id=lpd.dataset_or_trait_id "
- "AND pr.dataset_type=lpd.dataset_type) "
+ "ON pr.data_link_id=lpd.data_link_id "
"WHERE pr.resource_id=?",
(str(resource_id),))
return cursor.fetchall()