diff options
| -rw-r--r-- | gn_auth/auth/authorisation/data/genotypes.py | 49 | ||||
| -rw-r--r-- | gn_auth/auth/authorisation/data/mrna.py | 54 |
2 files changed, 54 insertions, 49 deletions
diff --git a/gn_auth/auth/authorisation/data/genotypes.py b/gn_auth/auth/authorisation/data/genotypes.py index d44cbfb..17f3317 100644 --- a/gn_auth/auth/authorisation/data/genotypes.py +++ b/gn_auth/auth/authorisation/data/genotypes.py @@ -32,12 +32,21 @@ def ungrouped_genotype_data(# pylint: disable=[too-many-arguments, too-many-posi search_query: str, selected: tuple[dict, ...] = tuple(), limit: int = 10000, offset: int = 0) -> tuple[ dict, ...]: - """Retrieve genotype data that is not linked to any user group.""" - params = tuple( - (row["SpeciesId"], row["InbredSetId"], row["GenoFreezeId"]) - for row in linked_genotype_data(authconn)) + tuple( - (row["SpeciesId"], row["InbredSetId"], row["GenoFreezeId"]) - for row in selected) + """Retrieve genotype data that is not linked to any user group. + + The set of linked datasets is read from the auth database (SQLite) and the + exclusion happens in Python. This avoids embedding the (ever-growing) list + of linked datasets into the MariaDB query as a giant `NOT IN` list, which + MariaDB executes with an unindexed nested-loop join and which degrades + badly as more datasets get linked. + """ + def __key__(row): + """Normalise a row's dataset identity to a comparable tuple.""" + return (int(row["SpeciesId"]), int(row["InbredSetId"]), + int(row["GenoFreezeId"])) + + excluded = {__key__(row) for row in linked_genotype_data(authconn)} | { + __key__(row) for row in selected} query = ( "SELECT s.SpeciesId, iset.InbredSetId, iset.InbredSetName, " "gf.Id AS GenoFreezeId, gf.Name AS dataset_name, " @@ -46,27 +55,21 @@ def ungrouped_genotype_data(# pylint: disable=[too-many-arguments, too-many-posi "FROM Species AS s INNER JOIN InbredSet AS iset " "ON s.SpeciesId=iset.SpeciesId INNER JOIN GenoFreeze AS gf " "ON iset.InbredSetId=gf.InbredSetId ") - - if len(params) > 0 or bool(search_query): - query = query + "WHERE " - - if len(params) > 0: - paramstr = ", ".join(["(%s, %s, %s)"] * len(params)) - query = query + ( - "(s.SpeciesId, iset.InbredSetId, gf.Id) " - f"NOT IN ({paramstr}) " - ) + ("AND " if bool(search_query) else "") - + params = tuple() if bool(search_query): query = query + ( - "CONCAT(gf.Name, ' ', gf.FullName, ' ', gf.ShortName) LIKE %s ") - params = params + ((f"%{search_query}%",),)# type: ignore[operator] + "WHERE CONCAT(gf.Name, ' ', gf.FullName, ' ', gf.ShortName) " + "LIKE %s ") + params = (f"%{search_query}%",) - query = query + f"LIMIT {int(limit)} OFFSET {int(offset)}" with gn3conn.cursor(DictCursor) as cursor: - cursor.execute( - query, tuple(item for sublist in params for item in sublist)) - return tuple(row for row in cursor.fetchall()) + cursor.execute(query, params) + _rows = tuple(row for row in cursor.fetchall()) + + # Filter out linked/selected datasets and apply pagination in Python. + return tuple( + row for row in _rows + if __key__(row) not in excluded)[offset:offset + limit] @authorised_p( ("system:data:link-to-group",), diff --git a/gn_auth/auth/authorisation/data/mrna.py b/gn_auth/auth/authorisation/data/mrna.py index fcf6ea3..1af0c41 100644 --- a/gn_auth/auth/authorisation/data/mrna.py +++ b/gn_auth/auth/authorisation/data/mrna.py @@ -33,14 +33,21 @@ def ungrouped_mrna_data(# pylint: disable=[too-many-arguments, too-many-position search_query: str, selected: tuple[dict, ...] = tuple(), limit: int = 10000, offset: int = 0) -> tuple[ dict, ...]: - """Retrieve mrna data that is not linked to any user group.""" - params = tuple( - (row["SpeciesId"], row["InbredSetId"], row["ProbeFreezeId"], - row["ProbeSetFreezeId"]) - for row in linked_mrna_data(authconn)) + tuple( - (row["SpeciesId"], row["InbredSetId"], row["ProbeFreezeId"], - row["ProbeSetFreezeId"]) - for row in selected) + """Retrieve mrna data that is not linked to any user group. + + The set of linked datasets is read from the auth database (SQLite) and the + exclusion happens in Python. This avoids embedding the (ever-growing) list + of linked datasets into the MariaDB query as a giant `NOT IN` list, which + MariaDB executes with an unindexed nested-loop join and which degrades + badly as more datasets get linked. + """ + def __key__(row): + """Normalise a row's dataset identity to a comparable tuple.""" + return (int(row["SpeciesId"]), int(row["InbredSetId"]), + int(row["ProbeFreezeId"]), int(row["ProbeSetFreezeId"])) + + excluded = {__key__(row) for row in linked_mrna_data(authconn)} | { + __key__(row) for row in selected} query = ( "SELECT s.SpeciesId, iset.InbredSetId, iset.InbredSetName, " "pf.ProbeFreezeId, pf.Name AS StudyName, psf.Id AS ProbeSetFreezeId, " @@ -49,27 +56,22 @@ def ungrouped_mrna_data(# pylint: disable=[too-many-arguments, too-many-position "FROM Species AS s INNER JOIN InbredSet AS iset " "ON s.SpeciesId=iset.SpeciesId INNER JOIN ProbeFreeze AS pf " "ON iset.InbredSetId=pf.InbredSetId INNER JOIN ProbeSetFreeze AS psf " - "ON pf.ProbeFreezeId=psf.ProbeFreezeId ") + ( - "WHERE " if (len(params) > 0 or bool(search_query)) else "") - - if len(params) > 0: - paramstr = ", ".join(["(%s, %s, %s, %s)"] * len(params)) - query = query + ( - "(s.SpeciesId, iset.InbredSetId, pf.ProbeFreezeId, psf.Id) " - f"NOT IN ({paramstr}) " - ) + ("AND " if bool(search_query) else "") - + "ON pf.ProbeFreezeId=psf.ProbeFreezeId ") + params = tuple() if bool(search_query): query = query + ( - "CONCAT(pf.Name, psf.Name, ' ', psf.FullName, ' ', psf.ShortName) " - "LIKE %s ") - params = params + ((f"%{search_query}%",),)# type: ignore[operator] + "WHERE CONCAT(pf.Name, psf.Name, ' ', psf.FullName, ' ', " + "psf.ShortName) LIKE %s ") + params = (f"%{search_query}%",) - query = query + f"LIMIT {int(limit)} OFFSET {int(offset)}" with gn3conn.cursor(DictCursor) as cursor: - cursor.execute( - query, tuple(item for sublist in params for item in sublist)) - return tuple(row for row in cursor.fetchall()) + cursor.execute(query, params) + _rows = tuple(row for row in cursor.fetchall()) + + # Filter out linked/selected datasets and apply pagination in Python. + return tuple( + row for row in _rows + if __key__(row) not in excluded)[offset:offset + limit] @authorised_p( ("system:data:link-to-group",), @@ -120,7 +122,7 @@ def resources_by_datasets_and_traits( }, dsets_traits, {}) - paramstr = ", ".join(["?"] * len(dsets_traits)) + paramstr = ", ".join(["?"] * len(traits_by_datasets.keys())) query = ( "SELECT r.*, rc.*, lmd.dataset_name FROM linked_mrna_data AS lmd " "INNER JOIN mrna_resources AS mr ON lmd.data_link_id=mr.data_link_id " |