diff options
Diffstat (limited to 'gn3')
| -rw-r--r-- | gn3/auth/authorisation/data/phenotypes.py | 45 |
1 files changed, 38 insertions, 7 deletions
diff --git a/gn3/auth/authorisation/data/phenotypes.py b/gn3/auth/authorisation/data/phenotypes.py index 0d018cf..3ba478a 100644 --- a/gn3/auth/authorisation/data/phenotypes.py +++ b/gn3/auth/authorisation/data/phenotypes.py @@ -1,14 +1,44 @@ """Handle linking of Phenotype data to the Auth(entic|oris)ation system.""" +from typing import Any, Iterable + +from MySQLdb.cursors import DictCursor + import gn3.auth.db as authdb import gn3.db_utils as gn3db from gn3.auth.authorisation.checks import authorised_p -def linked_phenotype_data(conn: authdb.DbConnection) -> tuple[dict, ...]: +def linked_phenotype_data( + authconn: authdb.DbConnection, gn3conn: gn3db.Connection, + species: str = "") -> Iterable[dict[str, Any]]: """Retrieve phenotype data linked to user groups.""" - with authdb.cursor(conn) as cursor: - cursor.execute("SELECT * FROM linked_phenotype_data") - return tuple(dict(row) for row in cursor.fetchall()) - return tuple() + authkeys = ("SpeciesId", "InbredSetId", "PublishFreezeId", "PublishXRefId") + with (authdb.cursor(authconn) as authcursor, + gn3conn.cursor(DictCursor) as gn3cursor): + authcursor.execute("SELECT * FROM linked_phenotype_data") + linked = tuple(tuple(row[key] for key in authkeys) + for row in authcursor.fetchall()) + paramstr = "".join(["(%s, %s, %s, %s)"] * len(linked)) + query = ( + "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 " + "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") + ( + " WHERE" if (len(linked) > 0 or bool(species)) else "") + ( + (" (spc.SpeciesId, iset.InbredSetId, pf.Id, pxr.Id) " + f"NOT IN ({paramstr})") if len(linked) > 0 else "") + ( + " AND"if len(linked) > 0 else "") + ( + " spc.SpeciesName=%s" if bool(species) else "") + params = linked + ((species,) if bool(species) else tuple()) + gn3cursor.execute(query, params) + return (item for item in gn3cursor.fetchall()) @authorised_p(("system:data:link-to-group",), error_description=( @@ -22,10 +52,11 @@ def ungrouped_phenotype_data( params = tuple( (row["SpeciesId"], row["InbredSetId"], row["PublishFreezeId"], row["PublishXRefId"]) - for row in linked_phenotype_data(authconn)) + for row in linked_phenotype_data(authconn, gn3conn)) paramstr = ", ".join(["(?, ?, ?, ?)"] * len(params)) query = ( - "SELECT spc.SpeciesId, iset.InbredSetId, pf.Id AS PublishFreezeId, " + "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 " "FROM " |