Database portability: support PostgreSQL and SQLite alongside MySQL/MariaDB #192

New Issue

2026-04-12T13:29:00+02:00

shahondin1624 commented

2026-04-12 13:29:00 +02:00

Summary

The app currently only works with MySQL/MariaDB (appinfo/info.xml declares only <database>mysql</database>). Nextcloud officially supports PostgreSQL and SQLite as well. This issue covers making the app fully portable across all three database backends.

Current State

The codebase already uses Nextcloud's OCP database abstraction (QBMapper, IQueryBuilder, ISchemaWrapper) for ~98% of database operations. Only 5 specific locations contain MySQL-specific SQL:

MySQL-Specific Code

#	File	Line	Issue
1	`lib/Service/QueryService.php`	300	`TIMESTAMPDIFF(YEAR, m.geburtsdatum, CURDATE())` — MySQL-only function for age calculation
2	`lib/Service/QueryService.php`	318	`TIMESTAMPDIFF(YEAR, m.eintritt, CURDATE())` — MySQL-only function for membership duration
3	`lib/Db/MemberMapper.php`	165	`LIKE` on DATE column — PostgreSQL doesn't support `LIKE` on date-typed columns
4	`lib/Db/MemberMapper.php`	301	Same `LIKE` on DATE column in `findFiltered()`
5	`lib/Db/LagerMapper.php`	70	`LIKE` on DATE column for year filtering

Already Portable (no changes needed)

All 15 mappers extend QBMapper and use IQueryBuilder
All 17 migrations use ISchemaWrapper with OCP\DB\Types constants
100% parameterized queries (no SQL injection risk)
iLike(), escapeLikeParameter(), COUNT(), JOINs — all through query builder
unsigned: true on BIGINT columns — Doctrine DBAL silently ignores this on PostgreSQL/SQLite

Implementation Plan

Phase 1: Core Portability Fixes

Create lib/Db/PlatformHelper.php — a thin utility with 3 methods that use IDBConnection::getDatabaseProvider() to generate platform-specific SQL:

getYearDiffExpression($db, $col) — year difference between date column and today
- MySQL: TIMESTAMPDIFF(YEAR, col, CURDATE())
- PostgreSQL: EXTRACT(YEAR FROM AGE(CURRENT_DATE, col))::integer
- SQLite: strftime-based calculation with birthday correction
getMonthExpression($db, $col) — extract month from date column
- MySQL: MONTH(col) / PostgreSQL: EXTRACT(MONTH FROM col) / SQLite: strftime('%m', col)
getYearExpression($db, $col) — extract year from date column
- MySQL: YEAR(col) / PostgreSQL: EXTRACT(YEAR FROM col) / SQLite: strftime('%Y', col)

Update affected files:

QueryService.php — use getYearDiffExpression() instead of TIMESTAMPDIFF/CURDATE
MemberMapper.php — use getMonthExpression() instead of LIKE '%-MM-%'
LagerMapper.php — use getYearExpression() instead of LIKE 'YYYY-%'
appinfo/info.xml — add <database>pgsql</database> and <database>sqlite</database>

Phase 2: Docker Infrastructure

Create Docker Compose files for each database variant:

docker/
  mysql/docker-compose.yml       (MariaDB 10.11 + Nextcloud 28)
  postgres/docker-compose.yml    (PostgreSQL 16 + Nextcloud 28)
  sqlite/docker-compose.yml      (Nextcloud 28 only, no DB service)

Add Makefile targets: deploy-mysql, deploy-postgres, deploy-sqlite with matching setup-* targets.

Phase 3: Integration Tests

Create tests/DatabasePortability/ test suite that runs against real databases (no mocks):

Test	Verifies
`MigrationTest`	All 17 migrations run cleanly on each DB
`MemberMapperTest`	CRUD + findByBirthdayMonth + findFiltered + fullTextSearch
`QueryServiceTest`	Age filter, membership duration filter, all operators
`LagerMapperTest`	findByYear across DB backends
`FeeRecordMapperTest`	Boolean handling (paid column)
`AuditLogMapperTest`	Date range queries
`UnicodeTest`	German umlauts stored/retrieved correctly
`CountQueryTest`	COUNT(*) returns 0 for empty tables

Phase 4: CI Pipeline

.gitea/workflows/database-portability.yml with matrix strategy over [mysql, postgres, sqlite].

Phase 5: Security Review

Area	Current Status	Action
SQL injection	All queries parameterized	Verify PlatformHelper introduces no regressions
Authorization	Pre-existing gaps	Document as separate issue
Data exposure	Encrypted fields use ICrypto (DB-agnostic)	Verify TEXT column sizes across DBs
Boolean handling	Uses PARAM_BOOL consistently	Verify PG native bool vs MySQL TINYINT
Type coercion	All params correctly typed	Verify PG strict typing
Case sensitivity	Uses iLike() from OCP	Already portable

Acceptance Criteria

App installs and runs on PostgreSQL (all migrations, all CRUD operations)
App installs and runs on SQLite (all migrations, all CRUD operations)
appinfo/info.xml declares all three databases
Age/duration filters in saved queries work on all three backends
Birthday-this-month filter works on all three backends
Lager findByYear works on all three backends
Docker Compose files exist for MySQL, PostgreSQL, and SQLite
Integration test suite passes on all three databases
CI pipeline runs tests against all three databases
Security review completed with no new vulnerabilities introduced
No MySQL-specific SQL remains in the codebase

## Summary The app currently only works with MySQL/MariaDB (`appinfo/info.xml` declares only `<database>mysql</database>`). Nextcloud officially supports PostgreSQL and SQLite as well. This issue covers making the app fully portable across all three database backends. ## Current State The codebase already uses Nextcloud's OCP database abstraction (QBMapper, IQueryBuilder, ISchemaWrapper) for ~98% of database operations. Only **5 specific locations** contain MySQL-specific SQL: ### MySQL-Specific Code | # | File | Line | Issue | |---|------|------|-------| | 1 | `lib/Service/QueryService.php` | 300 | `TIMESTAMPDIFF(YEAR, m.geburtsdatum, CURDATE())` — MySQL-only function for age calculation | | 2 | `lib/Service/QueryService.php` | 318 | `TIMESTAMPDIFF(YEAR, m.eintritt, CURDATE())` — MySQL-only function for membership duration | | 3 | `lib/Db/MemberMapper.php` | 165 | `LIKE` on DATE column — PostgreSQL doesn't support `LIKE` on date-typed columns | | 4 | `lib/Db/MemberMapper.php` | 301 | Same `LIKE` on DATE column in `findFiltered()` | | 5 | `lib/Db/LagerMapper.php` | 70 | `LIKE` on DATE column for year filtering | ### Already Portable (no changes needed) - All 15 mappers extend QBMapper and use IQueryBuilder - All 17 migrations use ISchemaWrapper with `OCP\DB\Types` constants - 100% parameterized queries (no SQL injection risk) - `iLike()`, `escapeLikeParameter()`, COUNT(), JOINs — all through query builder - `unsigned: true` on BIGINT columns — Doctrine DBAL silently ignores this on PostgreSQL/SQLite ## Implementation Plan ### Phase 1: Core Portability Fixes **Create `lib/Db/PlatformHelper.php`** — a thin utility with 3 methods that use `IDBConnection::getDatabaseProvider()` to generate platform-specific SQL: - `getYearDiffExpression($db, $col)` — year difference between date column and today - MySQL: `TIMESTAMPDIFF(YEAR, col, CURDATE())` - PostgreSQL: `EXTRACT(YEAR FROM AGE(CURRENT_DATE, col))::integer` - SQLite: strftime-based calculation with birthday correction - `getMonthExpression($db, $col)` — extract month from date column - MySQL: `MONTH(col)` / PostgreSQL: `EXTRACT(MONTH FROM col)` / SQLite: `strftime('%m', col)` - `getYearExpression($db, $col)` — extract year from date column - MySQL: `YEAR(col)` / PostgreSQL: `EXTRACT(YEAR FROM col)` / SQLite: `strftime('%Y', col)` **Update affected files:** - `QueryService.php` — use `getYearDiffExpression()` instead of `TIMESTAMPDIFF`/`CURDATE` - `MemberMapper.php` — use `getMonthExpression()` instead of `LIKE '%-MM-%'` - `LagerMapper.php` — use `getYearExpression()` instead of `LIKE 'YYYY-%'` - `appinfo/info.xml` — add `<database>pgsql</database>` and `<database>sqlite</database>` ### Phase 2: Docker Infrastructure Create Docker Compose files for each database variant: ``` docker/ mysql/docker-compose.yml (MariaDB 10.11 + Nextcloud 28) postgres/docker-compose.yml (PostgreSQL 16 + Nextcloud 28) sqlite/docker-compose.yml (Nextcloud 28 only, no DB service) ``` Add Makefile targets: `deploy-mysql`, `deploy-postgres`, `deploy-sqlite` with matching `setup-*` targets. ### Phase 3: Integration Tests Create `tests/DatabasePortability/` test suite that runs against real databases (no mocks): | Test | Verifies | |------|----------| | `MigrationTest` | All 17 migrations run cleanly on each DB | | `MemberMapperTest` | CRUD + findByBirthdayMonth + findFiltered + fullTextSearch | | `QueryServiceTest` | Age filter, membership duration filter, all operators | | `LagerMapperTest` | findByYear across DB backends | | `FeeRecordMapperTest` | Boolean handling (paid column) | | `AuditLogMapperTest` | Date range queries | | `UnicodeTest` | German umlauts stored/retrieved correctly | | `CountQueryTest` | COUNT(*) returns 0 for empty tables | ### Phase 4: CI Pipeline `.gitea/workflows/database-portability.yml` with matrix strategy over `[mysql, postgres, sqlite]`. ### Phase 5: Security Review | Area | Current Status | Action | |------|---------------|--------| | SQL injection | All queries parameterized | Verify PlatformHelper introduces no regressions | | Authorization | Pre-existing gaps | Document as separate issue | | Data exposure | Encrypted fields use ICrypto (DB-agnostic) | Verify TEXT column sizes across DBs | | Boolean handling | Uses PARAM_BOOL consistently | Verify PG native bool vs MySQL TINYINT | | Type coercion | All params correctly typed | Verify PG strict typing | | Case sensitivity | Uses iLike() from OCP | Already portable | ## Acceptance Criteria - [ ] App installs and runs on PostgreSQL (all migrations, all CRUD operations) - [ ] App installs and runs on SQLite (all migrations, all CRUD operations) - [ ] `appinfo/info.xml` declares all three databases - [ ] Age/duration filters in saved queries work on all three backends - [ ] Birthday-this-month filter works on all three backends - [ ] Lager findByYear works on all three backends - [ ] Docker Compose files exist for MySQL, PostgreSQL, and SQLite - [ ] Integration test suite passes on all three databases - [ ] CI pipeline runs tests against all three databases - [ ] Security review completed with no new vulnerabilities introduced - [ ] No MySQL-specific SQL remains in the codebase