software/tests/test_job_state.py

1"""Tests for SQLite job state persistence."""23from __future__ import annotations45import sqlite36import tempfile7from datetime import datetime8from pathlib import Path910import pytest1112from src.orchestration.models import JobStatus13from src.orchestration.state import JobStateStore141516@pytest.fixture17def temp_db() -> Path:18    """Create a temporary database path."""19    with tempfile.TemporaryDirectory() as tmpdir:20        yield Path(tmpdir) / "jobs" / "test.db"212223@pytest.fixture24def state_store(temp_db: Path) -> JobStateStore:25    """Create a JobStateStore with a temporary database."""26    return JobStateStore(temp_db)272829class TestJobStateStoreInit:30    """Tests for JobStateStore initialization."""3132    def test_creates_directory(self, temp_db: Path) -> None:33        """Test that state store creates parent directories."""34        store = JobStateStore(temp_db)35        assert temp_db.parent.exists()3637    def test_creates_tables(self, temp_db: Path) -> None:38        """Test that state store creates required tables."""39        store = JobStateStore(temp_db)4041        # Check tables exist42        conn = sqlite3.connect(str(temp_db))43        cursor = conn.execute(44            "SELECT name FROM sqlite_master WHERE type='table'"45        )46        tables = {row[0] for row in cursor.fetchall()}47        conn.close()4849        assert "bible_jobs" in tables50        assert "book_jobs" in tables51        assert "verse_jobs" in tables5253    def test_idempotent_init(self, temp_db: Path) -> None:54        """Test that multiple initializations don't fail."""55        store1 = JobStateStore(temp_db)56        store2 = JobStateStore(temp_db)  # Should not raise575859class TestBibleJobManagement:60    """Tests for Bible job management."""6162    def test_create_bible_job(self, state_store: JobStateStore) -> None:63        """Test creating a Bible job."""64        job = state_store.create_bible_job(65            books=["john", "matthew"],66            config={"model": "test"},67        )6869        assert job.job_id is not None70        assert job.books == ["john", "matthew"]71        assert job.status == JobStatus.PENDING72        assert job.config_snapshot == {"model": "test"}7374    def test_create_job_with_specific_id(self, state_store: JobStateStore) -> None:75        """Test creating a job with a specific ID."""76        job = state_store.create_bible_job(77            books=["john"],78            config={},79            job_id="my-custom-id",80        )81        assert job.job_id == "my-custom-id"8283    def test_get_bible_job(self, state_store: JobStateStore) -> None:84        """Test getting a Bible job by ID."""85        created = state_store.create_bible_job(books=["john"], config={})86        retrieved = state_store.get_bible_job(created.job_id)8788        assert retrieved is not None89        assert retrieved.job_id == created.job_id90        assert retrieved.books == ["john"]9192    def test_get_nonexistent_job(self, state_store: JobStateStore) -> None:93        """Test getting a non-existent job returns None."""94        result = state_store.get_bible_job("nonexistent-id")95        assert result is None9697    def test_get_latest_incomplete_job(self, state_store: JobStateStore) -> None:98        """Test getting the latest incomplete job."""99        # Create a completed job100        job1 = state_store.create_bible_job(books=["john"], config={})101        state_store.update_job_status(job1.job_id, JobStatus.COMPLETED)102103        # Create an incomplete job104        job2 = state_store.create_bible_job(books=["matthew"], config={})105106        latest = state_store.get_latest_incomplete_job()107        assert latest is not None108        assert latest.job_id == job2.job_id109110    def test_get_latest_incomplete_no_jobs(self, state_store: JobStateStore) -> None:111        """Test getting latest incomplete when no jobs exist."""112        result = state_store.get_latest_incomplete_job()113        assert result is None114115    def test_update_job_status(self, state_store: JobStateStore) -> None:116        """Test updating job status."""117        job = state_store.create_bible_job(books=["john"], config={})118119        started = datetime.utcnow()120        state_store.update_job_status(121            job.job_id,122            JobStatus.IN_PROGRESS,123            started_at=started,124        )125126        updated = state_store.get_bible_job(job.job_id)127        assert updated is not None128        assert updated.status == JobStatus.IN_PROGRESS129        assert updated.started_at is not None130131    def test_update_job_verse_counts(self, state_store: JobStateStore) -> None:132        """Test updating job verse counts."""133        job = state_store.create_bible_job(books=["john"], config={})134135        state_store.update_job_verse_counts(136            job.job_id,137            total=100,138            completed=50,139            failed=5,140        )141142        updated = state_store.get_bible_job(job.job_id)143        assert updated is not None144        assert updated.total_verses == 100145        assert updated.completed_verses == 50146        assert updated.failed_verses == 5147148149class TestBookJobManagement:150    """Tests for book job management."""151152    def test_book_jobs_created_with_bible_job(153        self, state_store: JobStateStore154    ) -> None:155        """Test that book jobs are created when Bible job is created."""156        job = state_store.create_bible_job(157            books=["john", "matthew", "luke"],158            config={},159        )160161        book_jobs = state_store.get_book_jobs(job.job_id)162        assert len(book_jobs) == 3163        book_names = [bj.book for bj in book_jobs]164        assert "john" in book_names165        assert "matthew" in book_names166        assert "luke" in book_names167168    def test_get_book_job(self, state_store: JobStateStore) -> None:169        """Test getting a specific book job."""170        job = state_store.create_bible_job(books=["john"], config={})171        book_job = state_store.get_book_job(job.job_id, "john")172173        assert book_job is not None174        assert book_job.book == "john"175        assert book_job.status == JobStatus.PENDING176177    def test_get_nonexistent_book_job(self, state_store: JobStateStore) -> None:178        """Test getting a non-existent book job."""179        job = state_store.create_bible_job(books=["john"], config={})180        result = state_store.get_book_job(job.job_id, "matthew")181        assert result is None182183    def test_update_book_status(self, state_store: JobStateStore) -> None:184        """Test updating book status."""185        job = state_store.create_bible_job(books=["john"], config={})186187        state_store.update_book_status(188            job.job_id,189            "john",190            JobStatus.IN_PROGRESS,191            worker_pid=12345,192            started_at=datetime.utcnow(),193        )194195        book_job = state_store.get_book_job(job.job_id, "john")196        assert book_job is not None197        assert book_job.status == JobStatus.IN_PROGRESS198        assert book_job.worker_pid == 12345199200    def test_update_book_verse_counts(self, state_store: JobStateStore) -> None:201        """Test updating book verse counts."""202        job = state_store.create_bible_job(books=["john"], config={})203204        state_store.update_book_verse_counts(205            job.job_id,206            "john",207            total=879,208            completed=100,209            failed=2,210        )211212        book_job = state_store.get_book_job(job.job_id, "john")213        assert book_job is not None214        assert book_job.total_verses == 879215        assert book_job.completed_verses == 100216        assert book_job.failed_verses == 2217218219class TestVerseJobManagement:220    """Tests for verse job management."""221222    def test_initialize_verse_jobs(self, state_store: JobStateStore) -> None:223        """Test initializing verse jobs for a book."""224        job = state_store.create_bible_job(books=["john"], config={})225226        verses = [(1, 1), (1, 2), (1, 3), (2, 1)]227        state_store.initialize_verse_jobs(job.job_id, "john", verses)228229        pending = state_store.get_pending_verses(job.job_id, "john")230        assert len(pending) == 4231232    def test_atomic_claim_verse_success(self, state_store: JobStateStore) -> None:233        """Test successfully claiming a verse."""234        job = state_store.create_bible_job(books=["john"], config={})235        state_store.initialize_verse_jobs(job.job_id, "john", [(1, 1)])236237        claimed = state_store.atomic_claim_verse(238            job.job_id, "john", 1, 1, worker_pid=12345239        )240        assert claimed is True241242        # Verify verse is now in_progress243        pending = state_store.get_pending_verses(job.job_id, "john")244        assert len(pending) == 0245246    def test_atomic_claim_verse_already_claimed(247        self, state_store: JobStateStore248    ) -> None:249        """Test that claiming an already claimed verse fails."""250        job = state_store.create_bible_job(books=["john"], config={})251        state_store.initialize_verse_jobs(job.job_id, "john", [(1, 1)])252253        # First claim succeeds254        claim1 = state_store.atomic_claim_verse(255            job.job_id, "john", 1, 1, worker_pid=12345256        )257        assert claim1 is True258259        # Second claim fails260        claim2 = state_store.atomic_claim_verse(261            job.job_id, "john", 1, 1, worker_pid=54321262        )263        assert claim2 is False264265    def test_update_verse_status_completed(266        self, state_store: JobStateStore267    ) -> None:268        """Test marking a verse as completed."""269        job = state_store.create_bible_job(books=["john"], config={})270        state_store.initialize_verse_jobs(job.job_id, "john", [(1, 1)])271        state_store.atomic_claim_verse(job.job_id, "john", 1, 1, 12345)272273        state_store.update_verse_status(274            job.job_id, "john", 1, 1, JobStatus.COMPLETED275        )276277        # Verify book counts updated278        book_job = state_store.get_book_job(job.job_id, "john")279        assert book_job is not None280        assert book_job.completed_verses == 1281282    def test_update_verse_status_failed(self, state_store: JobStateStore) -> None:283        """Test marking a verse as failed."""284        job = state_store.create_bible_job(books=["john"], config={})285        state_store.initialize_verse_jobs(job.job_id, "john", [(1, 1)])286        state_store.atomic_claim_verse(job.job_id, "john", 1, 1, 12345)287288        state_store.update_verse_status(289            job.job_id,290            "john",291            1,292            1,293            JobStatus.FAILED,294            error="Test error",295        )296297        # Verify book counts updated298        book_job = state_store.get_book_job(job.job_id, "john")299        assert book_job is not None300        assert book_job.failed_verses == 1301302        # Verify failed verses can be retrieved303        failed = state_store.get_failed_verses(job.job_id, "john")304        assert len(failed) == 1305        assert failed[0].last_error == "Test error"306307    def test_get_book_progress(self, state_store: JobStateStore) -> None:308        """Test getting book progress."""309        job = state_store.create_bible_job(books=["john"], config={})310        state_store.initialize_verse_jobs(311            job.job_id, "john", [(1, 1), (1, 2), (1, 3), (1, 4)]312        )313314        # Complete 2 verses, fail 1315        for i in range(3):316            state_store.atomic_claim_verse(job.job_id, "john", 1, i + 1, 12345)317318        state_store.update_verse_status(319            job.job_id, "john", 1, 1, JobStatus.COMPLETED320        )321        state_store.update_verse_status(322            job.job_id, "john", 1, 2, JobStatus.COMPLETED323        )324        state_store.update_verse_status(325            job.job_id, "john", 1, 3, JobStatus.FAILED326        )327328        progress = state_store.get_book_progress(job.job_id, "john")329        assert progress["total_verses"] == 4330        assert progress["completed_verses"] == 2331        assert progress["failed_verses"] == 1332        assert progress["pending_verses"] == 1333        assert progress["progress_percentage"] == 50.0334335    def test_reset_orphaned_verses(self, state_store: JobStateStore) -> None:336        """Test resetting orphaned (in_progress) verses."""337        job = state_store.create_bible_job(books=["john"], config={})338        state_store.initialize_verse_jobs(job.job_id, "john", [(1, 1), (1, 2)])339340        # Claim both verses341        state_store.atomic_claim_verse(job.job_id, "john", 1, 1, 12345)342        state_store.atomic_claim_verse(job.job_id, "john", 1, 2, 12345)343344        # Reset orphaned345        reset_count = state_store.reset_orphaned_verses(job.job_id)346        assert reset_count == 2347348        # Verify verses are pending again349        pending = state_store.get_pending_verses(job.job_id, "john")350        assert len(pending) == 2351352    def test_reset_failed_verses(self, state_store: JobStateStore) -> None:353        """Test resetting failed verses for retry."""354        job = state_store.create_bible_job(books=["john"], config={})355        state_store.initialize_verse_jobs(job.job_id, "john", [(1, 1)])356        state_store.atomic_claim_verse(job.job_id, "john", 1, 1, 12345)357        state_store.update_verse_status(358            job.job_id, "john", 1, 1, JobStatus.FAILED, error="Error"359        )360361        reset_count = state_store.reset_failed_verses(job.job_id, "john")362        assert reset_count == 1363364        # Verify verse is pending again365        pending = state_store.get_pending_verses(job.job_id, "john")366        assert len(pending) == 1367368        # Verify book counts updated369        book_job = state_store.get_book_job(job.job_id, "john")370        assert book_job is not None371        assert book_job.failed_verses == 0372373374class TestConcurrency:375    """Tests for concurrent access safety."""376377    def test_wal_mode_enabled(self, temp_db: Path) -> None:378        """Test that WAL mode is enabled for better concurrency."""379        store = JobStateStore(temp_db)380381        conn = sqlite3.connect(str(temp_db))382        cursor = conn.execute("PRAGMA journal_mode")383        mode = cursor.fetchone()[0]384        conn.close()385386        assert mode.lower() == "wal"387388389class TestCleanupMethods:390    """Tests for job cleanup methods."""391392    def test_delete_job(self, state_store: JobStateStore) -> None:393        """Test deleting a specific job."""394        job = state_store.create_bible_job(books=["john"], config={})395        state_store.initialize_verse_jobs(job.job_id, "john", [(1, 1), (1, 2)])396397        result = state_store.delete_job(job.job_id)398        assert result is True399400        # Verify job is gone401        assert state_store.get_bible_job(job.job_id) is None402        assert state_store.get_book_job(job.job_id, "john") is None403        assert state_store.get_pending_verses(job.job_id, "john") == []404405    def test_delete_nonexistent_job(self, state_store: JobStateStore) -> None:406        """Test deleting a job that doesn't exist."""407        result = state_store.delete_job("nonexistent-id")408        assert result is False409410    def test_delete_completed_jobs_keeps_latest(411        self, state_store: JobStateStore412    ) -> None:413        """Test deleting completed jobs while keeping latest."""414        # Create 3 completed jobs415        job1 = state_store.create_bible_job(books=["john"], config={})416        state_store.update_job_status(417            job1.job_id,418            JobStatus.COMPLETED,419            completed_at=datetime(2024, 1, 1),420        )421422        job2 = state_store.create_bible_job(books=["matthew"], config={})423        state_store.update_job_status(424            job2.job_id,425            JobStatus.COMPLETED,426            completed_at=datetime(2024, 1, 2),427        )428429        job3 = state_store.create_bible_job(books=["luke"], config={})430        state_store.update_job_status(431            job3.job_id,432            JobStatus.COMPLETED,433            completed_at=datetime(2024, 1, 3),434        )435436        # Delete all but 1 latest437        deleted = state_store.delete_completed_jobs(keep_latest=1)438        assert deleted == 2439440        # Only most recent should remain441        assert state_store.get_bible_job(job1.job_id) is None442        assert state_store.get_bible_job(job2.job_id) is None443        assert state_store.get_bible_job(job3.job_id) is not None444445    def test_delete_completed_jobs_keeps_multiple(446        self, state_store: JobStateStore447    ) -> None:448        """Test keeping multiple recent completed jobs."""449        jobs = []450        for i in range(5):451            job = state_store.create_bible_job(books=["john"], config={})452            state_store.update_job_status(453                job.job_id,454                JobStatus.COMPLETED,455                completed_at=datetime(2024, 1, i + 1),456            )457            jobs.append(job)458459        deleted = state_store.delete_completed_jobs(keep_latest=2)460        assert deleted == 3461462        # Verify only last 2 remain463        remaining = state_store.list_jobs()464        assert len(remaining) == 2465466    def test_delete_completed_jobs_no_completed(467        self, state_store: JobStateStore468    ) -> None:469        """Test deleting completed jobs when none are completed."""470        job = state_store.create_bible_job(books=["john"], config={})471        # Job stays pending472473        deleted = state_store.delete_completed_jobs(keep_latest=1)474        assert deleted == 0475        assert state_store.get_bible_job(job.job_id) is not None476477    def test_delete_jobs_older_than(self, state_store: JobStateStore) -> None:478        """Test deleting jobs older than N days."""479        from datetime import timedelta480481        # Create an old job (manually set created_at)482        job1 = state_store.create_bible_job(books=["john"], config={})483484        # Manually update the created_at to be old485        old_date = (datetime.utcnow() - timedelta(days=10)).isoformat()486        with state_store._connection() as conn:487            conn.execute(488                "UPDATE bible_jobs SET created_at = ? WHERE job_id = ?",489                (old_date, job1.job_id),490            )491492        # Create a recent job493        job2 = state_store.create_bible_job(books=["matthew"], config={})494495        # Delete jobs older than 5 days496        deleted = state_store.delete_jobs_older_than(5)497        assert deleted == 1498499        assert state_store.get_bible_job(job1.job_id) is None500        assert state_store.get_bible_job(job2.job_id) is not None501502    def test_delete_jobs_older_than_none_old(503        self, state_store: JobStateStore504    ) -> None:505        """Test deleting old jobs when none are old enough."""506        job = state_store.create_bible_job(books=["john"], config={})507508        deleted = state_store.delete_jobs_older_than(30)509        assert deleted == 0510        assert state_store.get_bible_job(job.job_id) is not None511512    def test_list_jobs(self, state_store: JobStateStore) -> None:513        """Test listing all jobs."""514        job1 = state_store.create_bible_job(books=["john"], config={})515        job2 = state_store.create_bible_job(books=["matthew"], config={})516        job3 = state_store.create_bible_job(books=["luke"], config={})517518        jobs = state_store.list_jobs()519        assert len(jobs) == 3520521        # Should be ordered by created_at desc522        job_ids = [j.job_id for j in jobs]523        assert job3.job_id == job_ids[0]524        assert job2.job_id == job_ids[1]525        assert job1.job_id == job_ids[2]526527    def test_list_jobs_with_limit(self, state_store: JobStateStore) -> None:528        """Test listing jobs with a limit."""529        for i in range(10):530            state_store.create_bible_job(books=["john"], config={})531532        jobs = state_store.list_jobs(limit=5)533        assert len(jobs) == 5534535    def test_get_database_stats(self, state_store: JobStateStore) -> None:536        """Test getting database statistics."""537        # Create jobs in various states538        job1 = state_store.create_bible_job(books=["john"], config={})539        state_store.initialize_verse_jobs(job1.job_id, "john", [(1, 1), (1, 2)])540541        job2 = state_store.create_bible_job(books=["matthew"], config={})542        state_store.update_job_status(job2.job_id, JobStatus.IN_PROGRESS)543544        job3 = state_store.create_bible_job(books=["luke"], config={})545        state_store.update_job_status(job3.job_id, JobStatus.COMPLETED)546547        job4 = state_store.create_bible_job(books=["mark"], config={})548        state_store.update_job_status(job4.job_id, JobStatus.FAILED)549550        stats = state_store.get_database_stats()551552        assert stats["total_jobs"] == 4553        assert stats["pending_jobs"] == 1554        assert stats["in_progress_jobs"] == 1555        assert stats["completed_jobs"] == 1556        assert stats["failed_jobs"] == 1557        assert stats["total_verse_records"] == 2558        assert stats["database_size_mb"] >= 0559