Mitgliederverwaltung/.gitea/workflows/database-portability.yml

# CI pipeline for database portability tests.
# Runs PHPUnit tests against MySQL, PostgreSQL, and SQLite.
#
# Part of Issue #192.

name: Database Portability Tests

on:
  push:
    branches: [main]
    paths:
      - 'lib/Db/**'
      - 'lib/Service/QueryService.php'
      - 'lib/Migration/**'
      - 'tests/**'
  pull_request:
    paths:
      - 'lib/Db/**'
      - 'lib/Service/QueryService.php'
      - 'lib/Migration/**'
      - 'tests/**'

jobs:
  unit-tests:
    name: Unit Tests (PlatformHelper)
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v4

      - name: Setup PHP
        uses: shivammathur/setup-php@v2
        with:
          php-version: '8.1'
          tools: composer:v2

      - name: Install dependencies
        run: composer install --no-dev --optimize-autoloader --no-interaction

      - name: Run PlatformHelper unit tests
        run: vendor/bin/phpunit tests/Unit/PlatformHelperTest.php

      - name: Run DatabasePortability tests
        run: vendor/bin/phpunit tests/DatabasePortability/

  portability-matrix:
    name: Integration (${{ matrix.database }})
    runs-on: ubuntu-latest
    needs: unit-tests
    strategy:
      fail-fast: false
      matrix:
        include:
          - database: mysql
            db_image: mariadb:10.11
            db_port: 3306
            nc_db_type: mysql
            nc_db_host: 127.0.0.1
            nc_db_name: nextcloud
            nc_db_user: nextcloud
            nc_db_pass: nextcloud
          - database: postgres
            db_image: postgres:16-alpine
            db_port: 5432
            nc_db_type: pgsql
            nc_db_host: 127.0.0.1
            nc_db_name: nextcloud
            nc_db_user: nextcloud
            nc_db_pass: nextcloud
          - database: sqlite
            db_image: ""
            db_port: 0
            nc_db_type: sqlite
            nc_db_host: ""
            nc_db_name: ""
            nc_db_user: ""
            nc_db_pass: ""

    services:
      db:
        image: ${{ matrix.db_image || 'alpine:3' }}
        env:
          MYSQL_ROOT_PASSWORD: nextcloud
          MYSQL_DATABASE: nextcloud
          MYSQL_USER: nextcloud
          MYSQL_PASSWORD: nextcloud
          POSTGRES_DB: nextcloud
          POSTGRES_USER: nextcloud
          POSTGRES_PASSWORD: nextcloud
        ports:
          - ${{ matrix.db_port && format('{0}:{0}', matrix.db_port) || '9999:9999' }}

    steps:
      - uses: actions/checkout@v4

      - name: Setup PHP
        uses: shivammathur/setup-php@v2
        with:
          php-version: '8.1'
          extensions: pdo, pdo_mysql, pdo_pgsql, pdo_sqlite
          tools: composer:v2

      - name: Install dependencies
        run: composer install --no-dev --optimize-autoloader --no-interaction

      - name: Run portability tests (${{ matrix.database }})
        env:
          DB_PLATFORM: ${{ matrix.nc_db_type }}
        run: |
          echo "Testing database portability for: ${{ matrix.database }}"
          vendor/bin/phpunit tests/Unit/PlatformHelperTest.php
          vendor/bin/phpunit tests/DatabasePortability/

      - name: Verify info.xml declares ${{ matrix.database }}
        run: |
          if [ "${{ matrix.database }}" = "mysql" ]; then
            grep '<database>mysql</database>' appinfo/info.xml
          elif [ "${{ matrix.database }}" = "postgres" ]; then
            grep '<database>pgsql</database>' appinfo/info.xml
          elif [ "${{ matrix.database }}" = "sqlite" ]; then
            grep '<database>sqlite</database>' appinfo/info.xml
          fi

  verify-no-mysql-sql:
    name: Verify no MySQL-specific SQL
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v4

      - name: Check for TIMESTAMPDIFF (should only be in PlatformHelper)
        run: |
          MATCHES=$(grep -rn 'TIMESTAMPDIFF' lib/ --include='*.php' | grep -v 'PlatformHelper.php' || true)
          if [ -n "$MATCHES" ]; then
            echo "ERROR: TIMESTAMPDIFF found outside PlatformHelper:"
            echo "$MATCHES"
            exit 1
          fi
          echo "OK: No TIMESTAMPDIFF outside PlatformHelper"

      - name: Check for CURDATE (should only be in PlatformHelper)
        run: |
          MATCHES=$(grep -rn 'CURDATE' lib/ --include='*.php' | grep -v 'PlatformHelper.php' || true)
          if [ -n "$MATCHES" ]; then
            echo "ERROR: CURDATE found outside PlatformHelper:"
            echo "$MATCHES"
            exit 1
          fi
          echo "OK: No CURDATE outside PlatformHelper"

      - name: Check for LIKE on date columns (should not exist)
        run: |
          # Check for patterns like LIKE '%-MM-%' or LIKE 'YYYY-%' on date columns
          MATCHES=$(grep -rn "like.*geburtsdatum\|like.*startdatum\|like.*eintritt" lib/ --include='*.php' -i || true)
          if [ -n "$MATCHES" ]; then
            echo "ERROR: LIKE on date columns found:"
            echo "$MATCHES"
            exit 1
          fi
          echo "OK: No LIKE on date columns"