[Integration Test] WorkService

[adnanvaldes]

Overview

Add comprehensive integration tests for WorkService to verify database behavior, constraints, and data persistence beyond what unit tests cover.

Context

WorkService has specific SQLite constraints that need testing:

• CHECK(is_read IN (0,1)) (to mark boolean values)
• UNIQUE(title, author, year)

Test Scenarios to Implement

1. Database Constraints Enforcement

• Test UNIQUE constraint prevents duplicate (title, author, year) at DB level
• Test CHECK constraint enforces [0,1] values
• Test constraint violations even when bypassing service validation

2. Data Type Conversion & Serialization

• Test empty fields (NULL vs "")

3. Transaction Behavior

• Test rollback when duplicate work added
• Test successful commit for valid work
• Test multiple operations in single transaction

4. Query Behavior

• Test find() with title filter
• Test find() with author filter
• Test find() with year filter
• Test find() with multiple filters combined
• Test case handling (service uppercases input)

5. Schema & ID Management

• Verify travel table created with all constraints
• Test AUTOINCREMENT assigns sequential IDs
• Test ID doesn't reuse after deletion

6. Edge Cases

• Test notes field with special characters and Unicode

Files to Create/Modify

• tests/integration/test_work_service_integration.py

Acceptance Criteria

• All 20+ test scenarios implemented
• Tests use fake_db fixture
• All tests pass consistently

Notes

Existing integration tests in tests/integration/test_work_service_integration.py cover basic scenarios. This issue tracks expansion to comprehensive database behavior testing.