IntegrationTests.yml

name: Integration Tests
on: 
  push:
    paths-ignore:
      - '**.md'
      - '.github/workflows/**'
      - '!.github/workflows/IntegrationTests.yml'
  pull_request:
  repository_dispatch:
  workflow_dispatch:
concurrency:
  group: ${{ github.workflow }}-${{ github.ref }}-${{ github.head_ref || '' }}-${{ github.base_ref || '' }}-${{ github.ref != 'refs/heads/main' || github.sha }}
  cancel-in-progress: true

jobs:

  format-check:
    name: Format Check
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v6
        with:
          submodules: 'true'

      - name: Install
        run: |
          pip3 install           \
            black==25.1.0        \
            clang_format==11.0.1 \
            cmake-format==0.6.13

      - name: Format Check
        run: |
          python -m pip show black
          python -m pip show cmake_format
          python -m pip show clang_format
          clang-format --version
          clang-format --dump-config
          make format-check

  linux-tests:
    name: Linux (pg-17)
    needs: format-check
    runs-on: ubuntu-latest

    env:
      GEN: ninja
      CC: 'ccache gcc'
      CXX: 'ccache g++'
      CCACHE_DIR: ${{ github.workspace }}/ccache
      VCPKG_TARGET_TRIPLET: x64-linux-release
      VCPKG_HOST_TRIPLET: x64-linux-release
      VCPKG_TOOLCHAIN_PATH: ${{ github.workspace }}/vcpkg/scripts/buildsystems/vcpkg.cmake

    steps:
    - name: Checkout
      uses: actions/checkout@v6
      with:
        fetch-depth: 0
        submodules: 'true'

    - name: Install Dependencies
      run: |
        sudo apt-get update -y -q -o=Dpkg::Use-Pty=0
        sudo apt-get install -y -q -o=Dpkg::Use-Pty=0 \
          build-essential \
          ccache \
          cmake \
          ninja-build

    - name: Cache Key
      id: cache_key
      working-directory: ./duckdb
      run: |
        DUCKDB_VERSION=$(git rev-parse --short=10 HEAD)
        KEY="${{ runner.os }}-${{ runner.arch }}-$DUCKDB_VERSION"
        echo "value=${KEY}" >> "${GITHUB_OUTPUT}"

    - name: Restore Cache
      uses: actions/cache/restore@v5
      with:
        path: ${{ github.workspace }}/ccache
        key: ${{ steps.cache_key.outputs.value }}

    - name: Setup vcpkg
      uses: lukka/run-vcpkg@v11.1
      with:
        vcpkgGitCommitId: 84bab45d415d22042bd0b9081aea57f362da3f35

    - name: Build extension
      run: |
        make release

    - name: Save Cache
      uses: actions/cache/save@v5
      with:
        path: ${{ github.workspace }}/ccache
        key: ${{ steps.cache_key.outputs.value }}

    - name: Setup Postgres
      uses: ikalnytskyi/action-setup-postgres@v8
      with:
        postgres-version: 17
        username: 'postgres'
        password: 'postgres'
        database: 'postgres'
        port: '5432'

    - name: Run tests
      env:
        PGHOST: localhost
        PGPORT: 5432
        PGUSER: postgres
        PGPASSWORD: postgres
        POSTGRES_TEST_DATABASE_AVAILABLE: 1
        LOCAL_EXTENSION_REPO: 'build/release/repository'
      run: |
        source ./create-postgres-tables.sh
        psql -d postgresscanner -c "SELECT 42"
        ./build/release/test/unittest 'test/sql/*'

  linux-pgbouncer:
    name: Linux PgBouncer (${{ matrix.postgres.name }})
    needs: linux-tests
    runs-on: ubuntu-latest

    strategy:
      matrix:
        postgres:
        - name: pg-14
          version: 14
        - name: pg-18
          version: 18

    env:
      GEN: ninja
      CC: 'ccache gcc'
      CXX: 'ccache g++'
      CCACHE_DIR: ${{ github.workspace }}/ccache
      VCPKG_TARGET_TRIPLET: x64-linux-release
      VCPKG_HOST_TRIPLET: x64-linux-release
      VCPKG_TOOLCHAIN_PATH: ${{ github.workspace }}/vcpkg/scripts/buildsystems/vcpkg.cmake

    steps:
    - name: Checkout
      uses: actions/checkout@v6
      with:
        fetch-depth: 0
        submodules: 'true'

    - name: Install Dependencies
      run: |
        sudo apt-get update -y -q -o=Dpkg::Use-Pty=0
        sudo apt-get install -y -q -o=Dpkg::Use-Pty=0 \
          build-essential \
          ccache \
          cmake \
          ninja-build \
          pgbouncer

    - name: Cache Key
      id: cache_key
      working-directory: ./duckdb
      run: |
        DUCKDB_VERSION=$(git rev-parse --short=10 HEAD)
        KEY="${{ runner.os }}-${{ runner.arch }}-$DUCKDB_VERSION"
        echo "value=${KEY}" >> "${GITHUB_OUTPUT}"

    - name: Restore Cache
      uses: actions/cache/restore@v5
      with:
        path: ${{ github.workspace }}/ccache
        key: ${{ steps.cache_key.outputs.value }}

    - name: Setup vcpkg
      uses: lukka/run-vcpkg@v11.1
      with:
        vcpkgGitCommitId: 84bab45d415d22042bd0b9081aea57f362da3f35

    - name: Build extension
      run: |
        make release

    - name: Setup Postgres
      uses: ikalnytskyi/action-setup-postgres@v8
      with:
        postgres-version: ${{ matrix.postgres.version }}
        username: 'postgres'
        password: 'postgres'
        database: 'postgres'
        port: '5432'
        ssl: true

    - name: Setup PgBouncer
      run: |
        mkdir pgbouncer
        openssl req -new -x509 -days 365 -nodes -text -subj "/CN=localhost" \
          -out ./pgbouncer/pgbouncer.crt \
          -keyout ./pgbouncer/pgbouncer.key
        sudo chown -R postgres:postgres ./pgbouncer
        sudo cp -p ./pgbouncer/* /etc/pgbouncer/
        sudo ls -la /etc/pgbouncer
        sudo sed -i 's|^;bardb = host=localhost dbname=bazdb|postgresscanner = host=localhost dbname=postgresscanner|' /etc/pgbouncer/pgbouncer.ini
        sudo sed -i 's|^;pool_mode = session|pool_mode = transaction|' /etc/pgbouncer/pgbouncer.ini
        sudo sed -i 's|^;max_prepared_statements = 0|max_prepared_statements = 1024|' /etc/pgbouncer/pgbouncer.ini
        sudo sed -i 's|^;client_tls_sslmode = disable|client_tls_sslmode = require|' /etc/pgbouncer/pgbouncer.ini
        sudo sed -i 's|^;client_tls_key_file =|client_tls_key_file = /etc/pgbouncer/pgbouncer.key|' /etc/pgbouncer/pgbouncer.ini
        sudo sed -i 's|^;client_tls_cert_file =|client_tls_cert_file = /etc/pgbouncer/pgbouncer.crt|' /etc/pgbouncer/pgbouncer.ini
        echo '"postgres" "postgres"' | sudo tee /etc/pgbouncer/userlist.txt
        sudo cat /etc/pgbouncer/pgbouncer.ini
        sudo systemctl restart pgbouncer

    - name: Setup test data
      env:
        PGHOST: localhost
        PGPORT: 5432
        PGUSER: postgres
        PGPASSWORD: postgres
      run: |
        source ./create-postgres-tables.sh

    - name: Run tests with PgBouncer
      env:
        PGHOST: localhost
        PGPORT: 6432
        PGUSER: postgres
        PGPASSWORD: postgres
        PGSSLMODE: require
        POSTGRES_TEST_DATABASE_AVAILABLE: 1
        LOCAL_EXTENSION_REPO: 'build/release/repository'
      run: |
        psql -d postgresscanner -c "SELECT * FROM pg_stat_ssl WHERE pid = pg_backend_pid()"
        ./build/release/test/unittest 'test/sql/*'

    - name: Run tests directly
      env:
        PGHOST: localhost
        PGPORT: 5432
        PGUSER: postgres
        PGPASSWORD: postgres
        PGSSLMODE: require
        POSTGRES_TEST_DATABASE_AVAILABLE: 1
        LOCAL_EXTENSION_REPO: 'build/release/repository'
      run: |
        psql -d postgresscanner -c "SELECT * FROM pg_stat_ssl WHERE pid = pg_backend_pid()"
        ./build/release/test/unittest 'test/sql/*'

    - name: Upload log
      if: always()
      uses: actions/upload-artifact@v7
      with:
        name: pgbouncer_${{ matrix.pg_version }}_log
        path: |
          /var/log/postgresql/pgbouncer.log

  linux-sanitized:
    name: Linux ${{ matrix.sanitizer.name }} (pg-17)
    needs: linux-tests
    runs-on: ubuntu-latest

    strategy:
      matrix:
        sanitizer:
        - name: ASan
          threadsan_enabled: 0
        - name: TSan
          threadsan_enabled: 1

    env:
      CMAKE_BUILD_PARALLEL_LEVEL: 2
      CC: 'ccache gcc'
      CXX: 'ccache g++'
      CCACHE_DIR: ${{ github.workspace }}/ccache
      VCPKG_TARGET_TRIPLET: x64-linux-release
      VCPKG_HOST_TRIPLET: x64-linux-release
      VCPKG_TOOLCHAIN_PATH: ${{ github.workspace }}/vcpkg/scripts/buildsystems/vcpkg.cmake

    steps:
    - name: Checkout
      uses: actions/checkout@v6
      with:
        fetch-depth: 0
        submodules: 'true'

    - name: Install Dependencies
      run: |
        sudo apt-get update -y -q -o=Dpkg::Use-Pty=0
        sudo apt-get install -y -q -o=Dpkg::Use-Pty=0 \
          build-essential \
          ccache \
          cmake \
          mold \
          ninja-build

    - name: Cache Key
      id: cache_key
      working-directory: ./duckdb
      run: |
        DUCKDB_VERSION=$(git rev-parse --short=10 HEAD)
        KEY="${{ runner.os }}-${{ runner.arch }}-$DUCKDB_VERSION"-relassert-${{ matrix.threadsan }}
        echo "value=${KEY}" >> "${GITHUB_OUTPUT}"

    - name: Restore Cache
      uses: actions/cache/restore@v5
      with:
        path: ${{ github.workspace }}/ccache
        key: ${{ steps.cache_key.outputs.value }}

    - name: Setup vcpkg
      uses: lukka/run-vcpkg@v11.1
      with:
        vcpkgGitCommitId: 84bab45d415d22042bd0b9081aea57f362da3f35

    - name: Build extension
      env:
        THREADSAN: ${{ matrix.sanitizer.threadsan_enabled }}
      run: |
        make relassert
        cat ./build/relassert/src/CMakeFiles/duckdb.dir/flags.make
        cd build
        ln -s relassert release

    - name: Save Cache
      uses: actions/cache/save@v5
      with:
        path: ${{ github.workspace }}/ccache
        key: ${{ steps.cache_key.outputs.value }}

    - name: Setup Postgres
      uses: ikalnytskyi/action-setup-postgres@v8
      with:
        postgres-version: 17
        username: 'postgres'
        password: 'postgres'
        database: 'postgres'
        port: '5432'
        ssl: true

    - name: Run tests
      env:
        PGHOST: localhost
        PGPORT: 5432
        PGUSER: postgres
        PGPASSWORD: postgres
        PGSSLMODE: require
        POSTGRES_TEST_DATABASE_AVAILABLE: 1
        LOCAL_EXTENSION_REPO: 'build/relassert/repository'
      run: |
        source ./create-postgres-tables.sh
        psql -d postgresscanner -c "SELECT * FROM pg_stat_ssl WHERE pid = pg_backend_pid()"
        ./build/relassert/test/unittest

  windows-tests:
    name: Windows (pg-17)
    needs: linux-tests
    runs-on: windows-latest

    env:
      GEN: ninja
      CC: 'ccache cl'
      CXX: 'ccache cl'
      CCACHE_DIR: ${{ github.workspace }}/ccache
      VCPKG_TARGET_TRIPLET: x64-windows-static-release
      VCPKG_HOST_TRIPLET: x64-windows-static-release
      VCPKG_TOOLCHAIN_PATH: ${{ github.workspace }}/vcpkg/scripts/buildsystems/vcpkg.cmake

    steps:
    - name: Checkout
      uses: actions/checkout@v6
      with:
        fetch-depth: 0
        submodules: 'true'

    - name: Dependencies
      shell: bash
      run: |
        choco install \
          ccache \
          make \
          ninja \
          -y --force --no-progress

    - name: Build Environment
      shell: bash
      run: |
        # bash from Git
        echo 'C:/Program Files/Git/bin' >> "${GITHUB_PATH}"
        # make, ninja and ccache from Chocolatey
        echo 'C:/ProgramData/Chocolatey/bin' >> "${GITHUB_PATH}"

    - name: Build Environment Check
      shell: cmd
      run: |
        where bash
        bash --version
        where make
        make --version
        where ninja
        ninja --version
        where ccache
        ccache --version
        echo %PATH%

    - name: Cache Key
      id: cache_key
      working-directory: ./duckdb
      shell: bash
      run: |
        DUCKDB_VERSION=$(git rev-parse --short=10 HEAD)
        KEY="${{ runner.os }}-${{ runner.arch }}-$DUCKDB_VERSION"
        echo "value=${KEY}" >> "${GITHUB_OUTPUT}"

    - name: Restore Cache
      uses: actions/cache/restore@v5
      with:
        path: ${{ github.workspace }}/ccache
        key: ${{ steps.cache_key.outputs.value }}

    - name: Setup vcpkg
      uses: lukka/run-vcpkg@v11.1
      with:
        vcpkgGitCommitId: 84bab45d415d22042bd0b9081aea57f362da3f35

    - name: Prepare build tree
      shell: bash
      working-directory: duckdb
      run: |
        patch -p1 < ../scripts/win_resources.patch

    - name: Build extension
      shell: cmd
      run: |
          mv "C:\Program Files\Git\usr\bin\link.exe" "C:\Program Files\Git\usr\bin\link-git.exe"
          call "C:\Program Files\Microsoft Visual Studio\2022\Enterprise\VC\Auxiliary\Build\vcvars64.bat"
          bash -c "make release"

    - name: Save Cache
      uses: actions/cache/save@v5
      with:
        path: ${{ github.workspace }}/ccache
        key: ${{ steps.cache_key.outputs.value }}

    - name: Setup Postgres
      uses: ikalnytskyi/action-setup-postgres@v8
      with:
        postgres-version: '17'
        username: 'postgres'
        password: 'postgres'
        database: 'postgres'
        port: '5432'
        ssl: true

    - name: Setup test data
      env:
        PGHOST: localhost
        PGPORT: 5432
        PGUSER: postgres
        PGPASSWORD: postgres
        PGSCANNERTMP_ABS_DIR_PREFIX: ${{ github.workspace }}
      shell: bash
      run: |
        source ./create-postgres-tables.sh

    - name: Run tests
      env:
        PGHOST: localhost
        PGPORT: 5432
        PGUSER: postgres
        PGPASSWORD: postgres
        PGSSLMODE: require
        POSTGRES_TEST_DATABASE_AVAILABLE: 1
        LOCAL_EXTENSION_REPO: 'build/release/repository'
      shell: bash
      run: |
        psql -d postgresscanner -c "SELECT * FROM pg_stat_ssl WHERE pid = pg_backend_pid()"
        ./build/release/test/unittest