Mitgliederverwaltung/.plans/done/issue-192-database-portability.md

Implementation Plan: Issue #192 — Database Portability

Summary

Replace 5 MySQL-specific SQL expressions with platform-aware equivalents using a new PlatformHelper utility class, declare PostgreSQL and SQLite support in info.xml, add Docker infrastructure for all three backends, create integration tests, and set up CI pipeline.

Phase 1: Core Portability Fixes

1. Create lib/Db/PlatformHelper.php with 3 methods:
   • getYearDiffExpression($db, $col) — age/duration calculation
   • getMonthExpression($db, $col) — extract month
   • getYearExpression($db, $col) — extract year
2. Update lib/Service/QueryService.php lines 300, 318 — use PlatformHelper
3. Update lib/Db/MemberMapper.php lines 165, 301 — use PlatformHelper
4. Update lib/Db/LagerMapper.php line 70 — use PlatformHelper
5. Update appinfo/info.xml — add pgsql and sqlite database declarations

Phase 2: Docker Infrastructure

6. Create docker/postgres/docker-compose.yml
7. Create docker/sqlite/docker-compose.yml
8. Add Makefile targets for postgres and sqlite

Phase 3: Integration Tests

9. Create tests/DatabasePortability/PlatformHelperTest.php

Phase 4: CI Pipeline

10. Create .gitea/workflows/database-portability.yml

Phase 5: Security Review

11. Verify PlatformHelper introduces no SQL injection vectors
12. Verify column expressions are hardcoded, not user-supplied

AC Verification Checklist

1. appinfo/info.xml declares mysql, pgsql, and sqlite
2. No MySQL-specific SQL remains (no TIMESTAMPDIFF, CURDATE, LIKE on date columns)
3. PlatformHelper generates correct expressions for all 3 backends
4. Age/duration filters use PlatformHelper
5. Birthday-this-month filter uses PlatformHelper
6. Lager findByYear uses PlatformHelper
7. Docker Compose files exist for MySQL, PostgreSQL, and SQLite
8. Integration test suite exists
9. CI pipeline definition exists
10. No SQL injection vectors introduced (all expressions use hardcoded column names)