[EPIC:FEATURE] SQLite Support

[dkd-kaehm]

[EPIC:FEATURE] SQLite Support

This is the epic issue and collection point for SQLite database compatibility.

Summary

EXT:solr integration tests fail when using SQLite instead of MySQL. While the PHP code uses Doctrine DBAL (database-agnostic), there are fundamental incompatibilities at the SQL/data type level.

Related: #2845 PostgreSQL Support (similar multi-database compatibility issues)

Test Results

• MySQL with Paratest: ✅ 408/408 tests PASS
• SQLite with Paratest: ❌ 376/408 tests PASS, 32 failures

Open Issues

• #TBD [BUG] Data type formatting differences (StatisticsRepository)
• #TBD [BUG] Index Queue initialization not working with SQLite
• #TBD [TASK] Abstract data type formatting for multi-DB support
• #TBD [TASK] Add SQLite test job to GitHub Actions (allowed to fail)

Identified Problems

1. Data Type Formatting Differences

• MySQL: Returns '5.0000' (string with 4 decimals)
• SQLite: Returns 5.0 (float)
• Affected tests: StatisticsRepositoryTest (2 failures)

2. Index Queue Initialization Failures

• Queue items not created/retrieved correctly in SQLite
• Root cause: Foreign Key constraints, transaction handling, triggers
• Affected tests: QueueTest, GarbageCollectorTest (~8-10 failures)

Current Workaround

Use MySQL with Paratest (recommended):

composer tests:solr:integration

Lessons from PostgreSQL Support (#2845)

• Multi-database support requires more than ORM abstraction
• Need platform-aware SQL generation (backticks, type casting)
• Test jobs for alternative databases should be "allowed to fail" initially