ci_test_ws_db.yml

name: CI Test WS database

on:
  push:
    branches: [main, release/**, feature/**]
    tags: [v*]
  pull_request:
    branches: [main, release/**, feature/**]
  workflow_dispatch:
    inputs:
      tests:
        description: "Tests to run"
        required: false
        default: "all"
      build_image:
        description: "Build/push DB image"
        required: false
        default: "false"

permissions:
  contents: read
  packages: write

jobs:
  ci_test_ws_db:
    runs-on: ubuntu-latest
    strategy:
      fail-fast: false
      matrix:
        pg_version: [16, 17]

    env:
      # PGPASSWORD: ${{ secrets.POSTGRES_PASSWORD }}
      PGPASSWORD: postgres

    steps:
      - name: Checkout repository
        uses: actions/checkout@v2

      - name: Show which PG version we're testing
        run: echo "Testing on PostgreSQL version ${{ matrix.pg_version }}"

      - name: Set up Python
        uses: actions/setup-python@v2
        with:
          python-version: "3.x"

      - name: Install Python dependencies
        run: |
          python -m pip install --upgrade pip
          pip install -r test/requirements.txt

      - name: Add PostgreSQL repository
        run: |
          sudo sh -c 'echo "deb https://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'
          wget -qO - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -

      - name: Update apt-get & install PostgreSQL
        run: |
          sudo apt-get update
          sudo apt-get install -y \
            postgresql-${{ matrix.pg_version }} \
            postgresql-${{ matrix.pg_version }}-postgis-3 \
            postgresql-${{ matrix.pg_version }}-pgrouting \
            postgresql-${{ matrix.pg_version }}-pgtap \
            postgis

      - name: Remove default Postgres clusters
        run: |
          for cluster in $(pg_lsclusters --no-header | awk '{print $1 ":" $2}'); do
            ver=$(echo $cluster | cut -d':' -f1)
            name=$(echo $cluster | cut -d':' -f2)
            sudo pg_dropcluster --stop $ver $name || true
          done

      - name: Create & start PG cluster
        run: |
          # Choose a port based on matrix.pg_version and workflow type (WS)
          if [ "${{ matrix.pg_version }}" = "16" ]; then
            PORT=55432
          else
            PORT=55433
          fi

          # Ensure any existing cluster is stopped before creating new one
          sudo pg_dropcluster --stop ${{ matrix.pg_version }} main || true

          sudo pg_createcluster ${{ matrix.pg_version }} main --port=$PORT
          sudo pg_ctlcluster ${{ matrix.pg_version }} main start

          # Make the port visible to subsequent steps
          echo "PORT=$PORT" >> $GITHUB_ENV

      - name: Show running clusters
        run: sudo pg_lsclusters

      # Use the secret password here
      - name: Set postgres password
        run: |
          sudo -u postgres psql -c "ALTER USER postgres PASSWORD 'postgres';"

      - name: Create PostgreSQL Database
        run: |
          psql -h localhost -p ${{ env.PORT }} -U postgres -c "CREATE DATABASE gw_db;"

      - name: Setup PostgreSQL extensions
        run: |
          psql -h localhost -p ${{ env.PORT }} -U postgres -d gw_db -c 'CREATE EXTENSION postgis;'
          psql -h localhost -p ${{ env.PORT }} -U postgres -d gw_db -c 'CREATE EXTENSION pgrouting;'
          psql -h localhost -p ${{ env.PORT }} -U postgres -d gw_db -c 'CREATE EXTENSION postgis_raster;'
          psql -h localhost -p ${{ env.PORT }} -U postgres -d gw_db -c 'CREATE EXTENSION postgis_topology;'
          psql -h localhost -p ${{ env.PORT }} -U postgres -d gw_db -c 'CREATE EXTENSION pgtap;'

      - name: Replace variables in SQL files
        run: python test/replace_vars.py ws

      - name: Create Sample Schema
        run: python test/execute_sql_files.py ws

      - name: Run all SQL tests in sequence
        run: |
          TEST_GROUPS="${{ github.event.inputs.tests || 'all' }}"
          EXIT_CODE=0
          if [[ "$TEST_GROUPS" == "all" || "$TEST_GROUPS" == *"schema"* ]]; then
            echo "Running SCHEMA tests..."
            pg_prove -h localhost -p ${{ env.PORT }} -U postgres -d gw_db test/ws/schema/{tables,views}/*.sql || EXIT_CODE=1
          fi

          if [[ "$TEST_GROUPS" == "all" || "$TEST_GROUPS" == *"security"* ]]; then
            echo "Running SECURITY tests..."
            pg_prove -h localhost -p ${{ env.PORT }} -U postgres -d gw_db test/ws/security/*.sql || EXIT_CODE=1
          fi

          if [[ "$TEST_GROUPS" == "all" || "$TEST_GROUPS" == *"function"* ]]; then
            echo "Running FUNCTION tests..."
            pg_prove -h localhost -p ${{ env.PORT }} -U postgres -d gw_db test/ws/function/*.sql || EXIT_CODE=1
          fi

          if [[ "$TEST_GROUPS" == "all" || "$TEST_GROUPS" == *"data"* ]]; then
            echo "Running DATA tests..."
            pg_prove -h localhost -p ${{ env.PORT }} -U postgres -d gw_db test/ws/data/*.sql || EXIT_CODE=1
          fi

          if [[ "$TEST_GROUPS" == "all" || "$TEST_GROUPS" == *"performance"* ]]; then
            echo "Running PERFORMANCE tests..."
            pg_prove -h localhost -p ${{ env.PORT }} -U postgres -d gw_db test/ws/performance/*.sql || EXIT_CODE=1
          fi

          exit $EXIT_CODE

      - name: Set image tag
        run: |
          if [ "${{ github.ref }}" = "refs/heads/main" ]; then
            echo "IMAGE_TAG=main" >> $GITHUB_ENV
          else
            echo "IMAGE_TAG=${GITHUB_REF_NAME//\//-}" >> $GITHUB_ENV
          fi

      - name: Dump DB for image
        if: github.ref == 'refs/heads/main' || startsWith(github.ref, 'refs/tags/v') || github.event.inputs.build_image == 'true'
        run: |
          /usr/lib/postgresql/${{ matrix.pg_version }}/bin/pg_dump \
            -h localhost -p ${{ env.PORT }} -U postgres -n ws_40 -Fc -f gw_ws.dump gw_db
      - name: Log in to GHCR
        if: github.ref == 'refs/heads/main' || startsWith(github.ref, 'refs/tags/v') || github.event.inputs.build_image == 'true'
        uses: docker/login-action@v3
        with:
          registry: ghcr.io
          username: ${{ github.actor }}
          password: ${{ secrets.GITHUB_TOKEN }}

      - name: Build and push DB image
        if: github.ref == 'refs/heads/main' || startsWith(github.ref, 'refs/tags/v') || github.event.inputs.build_image == 'true'
        uses: docker/build-push-action@v6
        with:
          context: .
          file: docker/gw-db/Dockerfile
          push: true
          tags: ghcr.io/giswater/gw-db:${{ env.IMAGE_TAG }}-pg${{ matrix.pg_version }}-ws
          build-args: |
            GW_DUMP=gw_ws.dump
            PG_MAJOR=${{ matrix.pg_version }}