ci-postgresql.yml

name: CI PostgreSQL

on:
  push:
  pull_request:
  schedule:
    # Weekly on Monday at 3:07 AM UTC
    - cron: '7 3 * * 1'
  workflow_dispatch:

jobs:
  employees-postgresql:
    name: Employees DB (PostgreSQL ${{ matrix.pg-version }})
    runs-on: ubuntu-22.04
    strategy:
      fail-fast: false
      matrix:
        pg-version:
          - '16'
          - '17'
    env:
      PG_VERSION: ${{ matrix.pg-version }}

    steps:
      - uses: actions/checkout@v4

      - name: Install dbdeployer
        run: |
          curl -s https://raw.githubusercontent.com/ProxySQL/dbdeployer/master/scripts/dbdeployer-install.sh | bash
          sudo mv dbdeployer /usr/local/bin/dbdeployer

      - name: Install PostgreSQL
        run: |
          sudo apt-get update
          sudo apt-get install -y curl ca-certificates
          sudo install -d /usr/share/postgresql-common/pgdg
          sudo curl -o /usr/share/postgresql-common/pgdg/apt.postgresql.org.asc \
            --fail https://www.postgresql.org/media/keys/ACCC4CF8.asc
          sudo sh -c 'echo "deb [signed-by=/usr/share/postgresql-common/pgdg/apt.postgresql.org.asc] \
            http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" \
            > /etc/apt/sources.list.d/pgdg.list'
          sudo apt-get update
          sudo apt-get install -y postgresql-${PG_VERSION} postgresql-client-${PG_VERSION}

      - name: Set up PostgreSQL binaries for dbdeployer
        run: |
          sudo systemctl stop postgresql || true
          PG_FULL=$(dpkg -s postgresql-${PG_VERSION} | grep '^Version:' | sed 's/Version: //' | cut -d'-' -f1)
          echo "PostgreSQL version: ${PG_FULL}"
          mkdir -p ~/opt/postgresql/${PG_FULL}/{bin,lib,share}
          cp -a /usr/lib/postgresql/${PG_VERSION}/bin/. ~/opt/postgresql/${PG_FULL}/bin/
          cp -a /usr/lib/postgresql/${PG_VERSION}/lib/. ~/opt/postgresql/${PG_FULL}/lib/
          cp -a /usr/share/postgresql/${PG_VERSION}/. ~/opt/postgresql/${PG_FULL}/share/

      - name: Deploy sandbox
        run: |
          PG_FULL=$(ls ~/opt/postgresql/ | head -1)
          echo "Deploying PostgreSQL ${PG_FULL}..."
          dbdeployer deploy postgresql "${PG_FULL}"

      - name: Verify sandbox
        run: |
          SBDIR=$(ls -d ~/sandboxes/pg_sandbox_*)
          $SBDIR/use -c "SELECT version();"
          echo "OK: PostgreSQL sandbox running"

      - name: Load employees database
        run: |
          SBDIR=$(ls -d ~/sandboxes/pg_sandbox_*)
          export PSQL="$SBDIR/use"
          bash postgresql/load_employees_db.sh

      - name: Test MD5 integrity
        run: |
          SBDIR=$(ls -d ~/sandboxes/pg_sandbox_*)
          $SBDIR/use < postgresql/test_employees_md5.sql > /tmp/test_md5.txt 2>&1
          cat /tmp/test_md5.txt
          md5_ok=$(grep -iw ok /tmp/test_md5.txt | wc -l | tr -d ' \t')
          if [ "$md5_ok" != "8" ]; then
            echo "MD5 FAIL - expected 8 OK - found $md5_ok"
            exit 1
          fi
          echo "MD5 OK ($md5_ok matches)"

      - name: Test SHA integrity
        run: |
          SBDIR=$(ls -d ~/sandboxes/pg_sandbox_*)
          $SBDIR/use < postgresql/test_employees_sha.sql > /tmp/test_sha.txt 2>&1
          cat /tmp/test_sha.txt
          sha_ok=$(grep -iw ok /tmp/test_sha.txt | wc -l | tr -d ' \t')
          if [ "$sha_ok" != "8" ]; then
            echo "SHA FAIL - expected 8 OK - found $sha_ok"
            exit 1
          fi
          echo "SHA OK ($sha_ok matches)"

      - name: Test SHA2 integrity
        run: |
          SBDIR=$(ls -d ~/sandboxes/pg_sandbox_*)
          $SBDIR/use < postgresql/test_employees_sha2.sql > /tmp/test_sha2.txt 2>&1
          cat /tmp/test_sha2.txt
          sha2_ok=$(grep -iw ok /tmp/test_sha2.txt | wc -l | tr -d ' \t')
          if [ "$sha2_ok" != "8" ]; then
            echo "SHA2 FAIL - expected 8 OK - found $sha2_ok"
            exit 1
          fi
          echo "SHA2 OK ($sha2_ok matches)"

      - name: Load objects (stored procedures/functions)
        run: |
          SBDIR=$(ls -d ~/sandboxes/pg_sandbox_*)
          $SBDIR/use < postgresql/objects.sql

      - name: Test stored procedures
        run: |
          SBDIR=$(ls -d ~/sandboxes/pg_sandbox_*)
          $SBDIR/use -d employees -t -c "SELECT emp_name(10001);"
          $SBDIR/use -d employees -t -c "SELECT emp_dept_name(10001);"
          $SBDIR/use -d employees -t -c "SELECT current_manager('d001');"
          $SBDIR/use -d employees -c "SELECT * FROM show_departments();"
          $SBDIR/use -d employees -t -c "SELECT COUNT(*) FROM v_full_employees;"
          $SBDIR/use -d employees -t -c "SELECT COUNT(*) FROM v_full_departments;"

      - name: Cleanup
        if: always()
        run: |
          dbdeployer delete all --skip-confirm 2>/dev/null || true
          pkill -9 -u "$USER" postgres 2>/dev/null || true