diff options
| author | Frederick Muriuki Muriithi <fredmanglis@gmail.com> | 2026-11-11 17:14:45 +0000 |
|---|---|---|
| committer | Frederick Muriuki Muriithi <fredmanglis@gmail.com> | 2026-11-11 13:12:20 -0600 |
| commit | 1cb56e96f9d748ff7a18a5e845a246ff4ee53b41 (patch) | |
| tree | 908fee52ef724c752dffe838d7ce8458aab5dc39 | |
| parent | cf930d1335d894b468e8e0d8f600a50515ecee36 (diff) | |
| download | gn-libs-1cb56e96f9d748ff7a18a5e845a246ff4ee53b41.tar.gz | |
Index the jobs metadata by key and value
Add a `(metadata_key, metadata_value)` index to `jobs_metadata` so jobs can be looked up by their metadata without scanning the table. Co-authored-by: pi coding agent <pi@pi.dev>
| -rw-r--r-- | gn_libs/jobs/migrations.py | 5 | ||||
| -rw-r--r-- | tests/unit/test_jobs.py | 37 |
2 files changed, 42 insertions, 0 deletions
diff --git a/gn_libs/jobs/migrations.py b/gn_libs/jobs/migrations.py index 2af16ae..a30a3fd 100644 --- a/gn_libs/jobs/migrations.py +++ b/gn_libs/jobs/migrations.py @@ -32,6 +32,11 @@ def __create_table_jobs_metadata__(cursor: DbCursor): CREATE INDEX IF NOT EXISTS idx_tbl_jobs_metadata_cols_job_id ON jobs_metadata(job_id) """) + cursor.execute( + """ + CREATE INDEX IF NOT EXISTS idx_tbl_jobs_metadata_cols_key_value + ON jobs_metadata(metadata_key, metadata_value) + """) def __create_table_jobs_output_streams__(cursor: DbCursor): diff --git a/tests/unit/test_jobs.py b/tests/unit/test_jobs.py new file mode 100644 index 0000000..c949dcf --- /dev/null +++ b/tests/unit/test_jobs.py @@ -0,0 +1,37 @@ +"""Tests for background jobs storage helpers.""" +import time +import uuid + +import pytest + +from gn_libs import sqlite3 +from gn_libs.jobs import jobs +from gn_libs.jobs.migrations import run_migrations + + +@pytest.fixture() +def jobsdb(tmp_path): + """Create a migrated jobs database.""" + db = tmp_path / "jobs.db" + run_migrations(str(db)) + return db + + +def _job(conn, job_type, **metadata): + """Create a job with the given type and metadata.""" + return jobs.initialise_job( + conn, str(uuid.uuid4()), ["true"], job_type, extra_meta=metadata) + + +@pytest.mark.unit_test +def test_jobs_metadata_has_key_value_index(jobsdb): + """ + GIVEN: a migrated jobs database + WHEN: the schema is inspected + THEN: the metadata key/value index exists + """ + with sqlite3.connection(str(jobsdb)) as conn, sqlite3.cursor(conn) as cursor: + cursor.execute( + "SELECT name FROM sqlite_master WHERE type='index' " + "AND name='idx_tbl_jobs_metadata_cols_key_value'") + assert cursor.fetchone() is not None |
