user resources: return total with filtered records.

Return a count of the total number of resources that the user has
access to even if we are only interested in a few of the records.

3 files changed, 39 insertions, 16 deletions

diff --git a/gn_auth/auth/authorisation/data/views.py b/gn_auth/auth/authorisation/data/views.py
index 4bf6746..1526070 100644
--- a/gn_auth/auth/authorisation/data/views.py
+++ b/gn_auth/auth/authorisation/data/views.py
@@ -95,7 +95,7 @@ def authorisation() -> Response:
             with require_oauth.acquire("profile group resource") as _token:
                 user = _token.user
                 resources = attach_resources_data(
-                    auth_conn, user_resources(auth_conn, _token.user))
+                    auth_conn, user_resources(auth_conn, _token.user)[0])
                 resources_roles = user_resource_roles(auth_conn, _token.user)
                 privileges = {
                     resource_id: tuple(
diff --git a/gn_auth/auth/authorisation/resources/models.py b/gn_auth/auth/authorisation/resources/models.py
index 4816527..f0647c3 100644
--- a/gn_auth/auth/authorisation/resources/models.py
+++ b/gn_auth/auth/authorisation/resources/models.py
@@ -185,18 +185,36 @@ def group_leader_resources(
     return tuple()
 
 
-def user_resources(conn: db.DbConnection, user: User) -> Sequence[Resource]:
+def user_resources(
+        conn: db.DbConnection,
+        user: User,
+        start_at: int = 0,
+        count: int = 0
+) -> tuple[Sequence[Resource], int]:
     """List the resources available to the user"""
-    with db.cursor(conn) as cursor:
-        cursor.execute(
-            ("SELECT DISTINCT(r.resource_id), r.resource_name,  "
-             "r.resource_category_id, r.public, r.created_by, r.created_at, "
-             "rc.resource_category_key, rc.resource_category_description "
+    query_template = ("SELECT %%COLUMNS%%  "
              "FROM user_roles AS ur "
              "INNER JOIN resources AS r ON ur.resource_id=r.resource_id "
              "INNER JOIN resource_categories AS rc "
              "ON r.resource_category_id=rc.resource_category_id "
-             "WHERE ur.user_id=?"),
+             "WHERE ur.user_id=? %%LIMITS%%")
+    with db.cursor(conn) as cursor:
+        cursor.execute(
+            query_template.replace(
+                "%%COLUMNS%%", "COUNT(DISTINCT(r.resource_id)) AS count"
+            ).replace(
+                "%%LIMITS%%", ""),
+            (str(user.user_id),))
+        _total_records = int(cursor.fetchone()["count"])
+        cursor.execute(
+            query_template.replace(
+                "%%COLUMNS%%",
+                "DISTINCT(r.resource_id), r.resource_name, "
+                "r.resource_category_id, r.public, r.created_by, r.created_at, "
+                "rc.resource_category_key, rc.resource_category_description"
+            ).replace(
+                "%%LIMITS%%",
+                ("" if count <= 0 else f"LIMIT {count} OFFSET {start_at}")),
             (str(user.user_id),))
         rows = cursor.fetchall() or []
 
@@ -208,7 +226,8 @@ def user_resources(conn: db.DbConnection, user: User) -> Sequence[Resource]:
             resource_from_dbrow(row),
             created_by=_creators_[row["created_by"]],
             created_at=datetime.fromtimestamp(row["created_at"])
-        ) for row in rows)
+        ) for row in rows), _total_records
+
 
 
 def resource_data(conn, resource, offset: int = 0, limit: Optional[int] = None) -> tuple[dict, ...]:
diff --git a/tests/unit/auth/test_resources.py b/tests/unit/auth/test_resources.py
index 04da6df..81f967e 100644
--- a/tests/unit/auth/test_resources.py
+++ b/tests/unit/auth/test_resources.py
@@ -114,19 +114,19 @@ def test_public_resources(fxtr_resources):
     "user,expected",
     tuple(zip(
         conftest.TEST_USERS,
-        (sorted(
+        ((sorted(
             {res.resource_id: res for res in
              ((conftest.GROUP_RESOURCES[0],) +
               conftest.TEST_RESOURCES_GROUP_01 +
               conftest.TEST_RESOURCES_PUBLIC)}.values(),
-            key=sort_key_resources),
-         sorted(
+            key=sort_key_resources), 6),
+         (sorted(
              {res.resource_id: res for res in
               ((conftest.TEST_RESOURCES_GROUP_01[1],) +
                conftest.TEST_RESOURCES_PUBLIC)}.values()
              ,
-             key=sort_key_resources),
-         PUBLIC_RESOURCES, PUBLIC_RESOURCES))))
+             key=sort_key_resources), 4),
+         (PUBLIC_RESOURCES, 3), (PUBLIC_RESOURCES, 3)))))
 def test_user_resources(fxtr_resource_user_roles, user, expected):
     """
     GIVEN: some resources in the database
@@ -134,6 +134,10 @@ def test_user_resources(fxtr_resource_user_roles, user, expected):
     THEN: list only the resources for which the user can access
     """
     conn, *_others = fxtr_resource_user_roles
+    uresources, count = user_resources(conn, user)
+    eresources, ecount = expected
+    assert count == ecount
     assert sorted(
-        {res.resource_id: res for res in user_resources(conn, user)
-         }.values(), key=sort_key_resources) == expected
+        {res.resource_id: res for res in  uresources}.values(),
+        key=sort_key_resources
+    ) == eresources