about summary refs log tree commit diff
diff options
context:
space:
mode:
authorFrederick Muriuki Muriithi <fredmanglis@gmail.com>2026-11-11 17:15:47 +0000
committerFrederick Muriuki Muriithi <fredmanglis@gmail.com>2026-11-11 13:14:58 -0600
commit9a30e5112f252d7aa5a09fd718733a35c7869b4e (patch)
tree5aa9da4962b8356263b505016f2a7f4cf7436436
parent1cb56e96f9d748ff7a18a5e845a246ff4ee53b41 (diff)
downloadgn-libs-9a30e5112f252d7aa5a09fd718733a35c7869b4e.tar.gz
Add jobs_by_metadata to look up jobs by metadata
Fetch jobs whose metadata contains all the given key/value pairs, returning
each job with its metadata, newest first. This supports idempotent stage
transitions without mutating completed jobs.

Co-authored-by: pi coding agent <pi@pi.dev>
-rw-r--r--gn_libs/jobs/__init__.py1
-rw-r--r--gn_libs/jobs/jobs.py39
-rw-r--r--tests/unit/test_jobs.py47
3 files changed, 86 insertions, 1 deletions
diff --git a/gn_libs/jobs/__init__.py b/gn_libs/jobs/__init__.py
index 7927f8d..df474a9 100644
--- a/gn_libs/jobs/__init__.py
+++ b/gn_libs/jobs/__init__.py
@@ -9,6 +9,7 @@ from .jobs import (job,
                    push_to_stream,
                    update_metadata,
                    jobs_by_external_id,
+                   jobs_by_metadata,
                    delete_expired_jobs)
 
 def init_app(flask_app):
diff --git a/gn_libs/jobs/jobs.py b/gn_libs/jobs/jobs.py
index bccddd5..f45819c 100644
--- a/gn_libs/jobs/jobs.py
+++ b/gn_libs/jobs/jobs.py
@@ -7,7 +7,7 @@ import logging
 import subprocess
 from pathlib import Path
 from functools import partial
-from typing import Union, Optional
+from typing import Mapping, Union, Optional
 from datetime import datetime, timezone, timedelta
 
 from gn_libs.sqlite3 import DbCursor, DbConnection, cursor as _cursor
@@ -82,6 +82,43 @@ def jobs_by_external_id(conn: DbConnection, external_id: Union[str, uuid.UUID])
         return tuple(_jobs.values())
 
 
+def jobs_by_metadata(
+        conn: DbConnection,
+        metadata: Mapping[str, str]
+) -> tuple[dict, ...]:
+    """Fetch jobs whose metadata contains all the given key/value pairs."""
+    if not metadata:
+        return tuple()
+
+    _conditions = " OR ".join(
+        "(m.metadata_key=? AND m.metadata_value=?)" for _ in metadata)
+    _params = tuple(
+        part for key, value in metadata.items() for part in (key, value))
+    with _cursor(conn) as cursor:
+        cursor.execute(
+            f"SELECT jobs.* FROM jobs "
+            f"INNER JOIN jobs_metadata AS m ON jobs.job_id=m.job_id "
+            f"WHERE {_conditions} "
+            f"GROUP BY jobs.job_id "
+            f"HAVING COUNT(DISTINCT m.metadata_key)=? "
+            f"ORDER BY jobs.created DESC",
+            _params + (len(metadata),))
+        _jobs = {
+            row["job_id"]: {**dict(row), "metadata": {}}
+            for row in cursor.fetchall()}
+        _job_ids = tuple(_jobs)
+        if not _job_ids:
+            return tuple()
+        _paramstr = ", ".join(["?"] * len(_job_ids))
+        cursor.execute(
+            f"SELECT * FROM jobs_metadata WHERE job_id IN ({_paramstr})",
+            _job_ids)
+        for row in cursor.fetchall():
+            _jobs[row["job_id"]]["metadata"][row["metadata_key"]] = (
+                row["metadata_value"])
+        return tuple(_jobs.values())
+
+
 def __save_job__(
         conn: DbConnection,
         the_job: dict,
diff --git a/tests/unit/test_jobs.py b/tests/unit/test_jobs.py
index c949dcf..f513764 100644
--- a/tests/unit/test_jobs.py
+++ b/tests/unit/test_jobs.py
@@ -35,3 +35,50 @@ def test_jobs_metadata_has_key_value_index(jobsdb):
             "SELECT name FROM sqlite_master WHERE type='index' "
             "AND name='idx_tbl_jobs_metadata_cols_key_value'")
         assert cursor.fetchone() is not None
+
+
+@pytest.mark.unit_test
+def test_jobs_by_metadata_matches_all_pairs(jobsdb):
+    """
+    GIVEN: jobs with various metadata
+    WHEN: jobs are looked up by several metadata pairs
+    THEN: only jobs matching all pairs are returned, with their metadata
+    """
+    with sqlite3.connection(str(jobsdb)) as conn:
+        wanted = _job(conn, "qc", genotype_collector_job_id="c1")
+        _job(conn, "qc", genotype_collector_job_id="c2")
+        _job(conn, "other", genotype_collector_job_id="c1")
+        found = jobs.jobs_by_metadata(
+            conn, {"genotype_collector_job_id": "c1", "job-type": "qc"})
+        assert [job["job_id"] for job in found] == [wanted["job_id"]]
+        assert found[0]["metadata"]["genotype_collector_job_id"] == "c1"
+
+
+@pytest.mark.unit_test
+def test_jobs_by_metadata_returns_newest_first(jobsdb):
+    """
+    GIVEN: two matching jobs created at different times
+    WHEN: jobs are looked up by metadata
+    THEN: the newest job is returned first
+    """
+    with sqlite3.connection(str(jobsdb)) as conn:
+        first = _job(conn, "qc", genotype_collector_job_id="c1")
+        time.sleep(0.01)
+        second = _job(conn, "qc", genotype_collector_job_id="c1")
+        found = jobs.jobs_by_metadata(
+            conn, {"genotype_collector_job_id": "c1"})
+        assert [job["job_id"] for job in found] == [
+            second["job_id"], first["job_id"]]
+
+
+@pytest.mark.unit_test
+def test_jobs_by_metadata_returns_empty_when_no_match(jobsdb):
+    """
+    GIVEN: jobs that do not match the query
+    WHEN: jobs are looked up by metadata
+    THEN: an empty tuple is returned
+    """
+    with sqlite3.connection(str(jobsdb)) as conn:
+        _job(conn, "qc", genotype_collector_job_id="c1")
+        assert jobs.jobs_by_metadata(
+            conn, {"genotype_collector_job_id": "nope"}) == ()