From 133021096a9d21151de16d364ed5864ac6a3aaa1 Mon Sep 17 00:00:00 2001
From: Frederick Muriuki Muriithi
Date: Mon, 20 Mar 2023 12:09:00 +0300
Subject: auth: data: Enable offset in queries

Enable the 'OFFSET' clause in the queries to allow for progressive fetching of
data from the database using the 'Previous' and 'Next' buttons.
---
 gn3/auth/authorisation/groups/data.py  | 19 +++++++++----------
 gn3/auth/authorisation/groups/views.py |  4 ++--
 2 files changed, 11 insertions(+), 12 deletions(-)

(limited to 'gn3')

diff --git a/gn3/auth/authorisation/groups/data.py b/gn3/auth/authorisation/groups/data.py
index 073449c..fd76648 100644
--- a/gn3/auth/authorisation/groups/data.py
+++ b/gn3/auth/authorisation/groups/data.py
@@ -20,8 +20,7 @@ def __fetch_grouped_data__(
         return tuple(dict(row) for row in cursor.fetchall())
 
 def __fetch_ungrouped_mrna_data__(
-        conn: gn3db.Connection, grouped_data,
-        offset: int = 0) -> Sequence[dict]:
+        conn: gn3db.Connection, grouped_data, offset: int) -> Sequence[dict]:
     """Fetch ungrouped mRNA Assay data."""
     query = ("SELECT psf.Id, psf.Name AS dataset_name, "
              "psf.FullName AS dataset_fullname, "
@@ -39,8 +38,7 @@ def __fetch_ungrouped_mrna_data__(
         return tuple(dict(row) for row in cursor.fetchall())
 
 def __fetch_ungrouped_geno_data__(
-        conn: gn3db.Connection, grouped_data,
-        offset: int = 0) -> Sequence[dict]:
+        conn: gn3db.Connection, grouped_data, offset: int) -> Sequence[dict]:
     """Fetch ungrouped Genotype data."""
     query = ("SELECT gf.Id, gf.Name AS dataset_name, "
              "gf.FullName AS dataset_fullname, "
@@ -58,8 +56,7 @@ def __fetch_ungrouped_geno_data__(
         return tuple(dict(row) for row in cursor.fetchall())
 
 def __fetch_ungrouped_pheno_data__(
-        conn: gn3db.Connection, grouped_data,
-        offset: int = 0) -> Sequence[dict]:
+        conn: gn3db.Connection, grouped_data, offset: int) -> Sequence[dict]:
     """Fetch ungrouped Phenotype data."""
     query = ("SELECT "
               "pxf.Id, iset.InbredSetName, pf.Name AS dataset_name, "
@@ -83,14 +80,15 @@ def __fetch_ungrouped_pheno_data__(
 
 def __fetch_ungrouped_data__(
         conn: gn3db.Connection, dataset_type: str,
-        ungrouped: Sequence[dict[str, Any]]) -> Sequence[dict[str, Any]]:
+        ungrouped: Sequence[dict[str, Any]],
+        offset) -> Sequence[dict[str, Any]]:
     """Fetch any ungrouped data."""
     fetch_fns = {
         "mrna": __fetch_ungrouped_mrna_data__,
         "genotype": __fetch_ungrouped_geno_data__,
         "phenotype": __fetch_ungrouped_pheno_data__
     }
-    return fetch_fns[dataset_type](conn, ungrouped)
+    return fetch_fns[dataset_type](conn, ungrouped, offset)
 
 @authorised_p(("system:data:link-to-group",),
               error_description=(
@@ -100,14 +98,15 @@ def __fetch_ungrouped_data__(
 def retrieve_ungrouped_data(
         authconn: authdb.DbConnection,
         gn3conn: gn3db.Connection,
-        dataset_type: str) -> Sequence[dict]:
+        dataset_type: str,
+        offset: int = 0) -> Sequence[dict]:
     """Retrieve any data not linked to any group."""
     if dataset_type not in ("mrna", "genotype", "phenotype"):
         raise InvalidData(
             "Requested dataset type is invalid. Expected one of "
             "'mrna', 'genotype' or 'phenotype'.")
     grouped_data = __fetch_grouped_data__(authconn, dataset_type)
-    return __fetch_ungrouped_data__(gn3conn, dataset_type, grouped_data)
+    return __fetch_ungrouped_data__(gn3conn, dataset_type, grouped_data, offset)
 
 def __fetch_mrna_data_by_id__(conn: gn3db.Connection, dataset_id: str) -> dict:
     """Fetch mRNA Assay data by ID."""
diff --git a/gn3/auth/authorisation/groups/views.py b/gn3/auth/authorisation/groups/views.py
index 7b967d7..0b21800 100644
--- a/gn3/auth/authorisation/groups/views.py
+++ b/gn3/auth/authorisation/groups/views.py
@@ -177,7 +177,6 @@ def unlinked_data(resource_type: str) -> Response:
                 f") {type_filter}")
             cursor.execute(ids_query)
             ids = cursor.fetchall()
-            print(f"THE IDS: {ids} ==> {type_filter}")
 
             if ids:
                 clause = ", ".join(["(?, ?, ?)"] * len(ids))
@@ -204,7 +203,8 @@ def ungrouped_data(dataset_type: str) -> Response:
         with gn3dbutils.database_connection() as gn3conn:
             return jsonify(with_db_connection(partial(
                 retrieve_ungrouped_data, gn3conn=gn3conn,
-                dataset_type=dataset_type)))
+                dataset_type=dataset_type,
+                offset = int(request.args.get("offset", 0)))))
 
 @groups.route("/data/link", methods=["POST"])
 @require_oauth("profile group resource")
-- 
cgit v1.2.3