run-integration-tests.yml

name: Run Integration Tests

on:
  pull_request:
    types: [opened, synchronize, reopened, edited]

jobs:
  should-run:
    name: Check if integration tests should run
    runs-on: ubuntu-latest
    outputs:
      skip: ${{ steps.test-parameters.outputs.skip }}
      dialects: ${{ steps.test-parameters.outputs.dialects }}
      integration_tests_sha: ${{ steps.test-parameters.outputs.integration_tests_sha }}

    steps:
      - name: Print debugging info
        run: |
          cat <<'EOF'
            Github event name: ${{ github.event_name }}

            Github event ${{ toJSON(github.event) }}

            Github event comment body: ${{ github.event.comment.body }}

            Github event pr body: ${{ github.event.pull_request.body }}

            Generic Number: ${{ github.event.number }}

            PR number: ${{ github.event.pull_request.number }}

            Issue number: ${{ github.event.issue.number }}

            SHA: ${{ github.sha }}

            Head Ref ${{ github.head_ref }}

            Ref Name: ${{ github.ref_name }}
          EOF

      - name: Checkout Code
        uses: actions/checkout@v5
        with:
          # we need to checkout all refs so we can run `git diff`
          fetch-depth: 0

      - name: Set up Python
        uses: actions/setup-python@v6
        with:
          python-version: '3.13'

      - name: Check if integration tests should be run
        id: test-parameters
        run: |
          python .github/scripts/get_integration_test_params.py

          # Read the submodule commit SHA so the run-integration-tests job can
          # resolve the correct ref in the remote repo
          SUBMODULE_SHA=$(git ls-tree HEAD sqlglot-integration-tests | awk '{print $3}')
          echo "integration_tests_sha=${SUBMODULE_SHA:-}" >> $GITHUB_OUTPUT
          echo "Integration tests submodule SHA: ${SUBMODULE_SHA:-not found}"

  run-integration-tests:
    name: Run Integration Tests
    runs-on: ubuntu-latest
    needs: should-run
    if: github.event.pull_request.head.repo.full_name == github.repository
    steps:
      - name: Acquire credentials
        id: app-token
        uses: actions/create-github-app-token@v2
        with:
          app-id: ${{ vars.INTEGRATION_TEST_CLIENT_ID }}
          private-key: ${{ secrets.INTEGRATION_TEST_PRIVATE_KEY }}
          owner: fivetran
          repositories: sqlglot-integration-tests

      - name: Run integration tests
        id: run-remote
        env:
          GH_TOKEN: ${{ steps.app-token.outputs.token }}
        run: |
          set -e

          CORRELATION_ID=$(python -c 'import uuid;print(uuid.uuid4())')
          REMOTE_REPO="fivetran/sqlglot-integration-tests"

          echo "Triggering remote workflow"

          # Resolve the integration tests ref: use a matching branch if its tip
          # matches the submodule SHA, otherwise fall back to main
          INTEGRATION_REF="main"
          SUBMODULE_SHA="${{ needs.should-run.outputs.integration_tests_sha }}"
          BRANCH_NAME="${{ github.head_ref || github.ref_name }}"

          if [ -n "$SUBMODULE_SHA" ] && [ -n "$BRANCH_NAME" ] && [ "$BRANCH_NAME" != "main" ]; then
            BRANCH_TIP=$(gh api "repos/$REMOTE_REPO/branches/$BRANCH_NAME" --jq '.commit.sha' 2>/dev/null || echo "")
            if [ "$BRANCH_TIP" = "$SUBMODULE_SHA" ]; then
              INTEGRATION_REF="$BRANCH_NAME"
              echo "Branch '$BRANCH_NAME' tip matches submodule SHA ($SUBMODULE_SHA)"
            elif [ -n "$BRANCH_TIP" ]; then
              echo "Branch '$BRANCH_NAME' exists but tip ($BRANCH_TIP) doesn't match submodule SHA ($SUBMODULE_SHA), using main"
            else
              echo "No branch '$BRANCH_NAME' in $REMOTE_REPO, using main"
            fi
          fi
          echo "Using integration tests ref: $INTEGRATION_REF"

          gh workflow run run-tests.yml \
            --repo $REMOTE_REPO \
            --ref "$INTEGRATION_REF" \
            -f sqlglot_ref=${{ github.sha }} \
            -f sqlglot_pr_number=${{ github.event.number || github.event.issue.number }} \
            -f sqlglot_branch_name=${{ github.head_ref || github.ref_name }} \
            -f correlation_id="$CORRELATION_ID" \
            -f dialects="${{ needs.should-run.outputs.dialects }}"

          echo "Triggered workflow using correlation id: $CORRELATION_ID"

          # poll for run id
          RUN_ID=""
          ATTEMPTS=0

          while [ "$RUN_ID" == "" ]; do
            sleep 5
            ATTEMPTS=$((ATTEMPTS + 1))
            if [ $ATTEMPTS -gt 10 ]; then
              echo "Timed out waiting for matching run to start"
              exit 1
            fi

            echo "Checking for run"
            RUN_ID=$(gh run list \
              --repo $REMOTE_REPO \
              --event workflow_dispatch \
              --workflow run-tests.yml \
              --user sqlglot-integration-tests \
              --json displayTitle,databaseId \
              --limit 20 \
              -q '.[] | select(.displayTitle | contains("Correlation ID: '"$CORRELATION_ID"'")) | .databaseId')
          done

          echo "Using Run ID: ${RUN_ID}"
          echo "remote_run_id=$RUN_ID" >> $GITHUB_OUTPUT

          echo "Waiting for completion"
          gh run watch $RUN_ID \
            --repo fivetran/sqlglot-integration-tests \
            --interval 10 \
            --compact \
            --exit-status

          # Fail the workflow on this side if the remote workflow fails
          exit $?

      - name: Fetch outputs
        if: ${{ !cancelled() && steps.run-remote.outputs.remote_run_id }}
        id: fetch-outputs
        uses: actions/download-artifact@v5
        with:
          github-token: ${{ steps.app-token.outputs.token }}
          repository: fivetran/sqlglot-integration-tests
          run-id: ${{ steps.run-remote.outputs.remote_run_id }}
          name: summary

      - name: Write summary as comment
        uses: actions/github-script@v8
        if: ${{ !cancelled() && (github.event_name == 'pull_request' || github.event.issue.pull_request) }}
        with:
          script: |
            // summary.json is downloaded from the remote workflow in the previous step
            const summary = require("./summary.json");

            // Add a unique identifier to find this comment later
            const commentIdentifier = "<!-- integration-test-summary -->";
            const body = `${commentIdentifier}\n${summary.msg}`;

            // Find existing comment
            const { data: comments } = await github.rest.issues.listComments({
              issue_number: context.issue.number,
              owner: context.repo.owner,
              repo: context.repo.repo,
            });

            const existingComment = comments.find(comment =>
              comment.body.includes(commentIdentifier)
            );

            if (existingComment) {
              // Update existing comment
              await github.rest.issues.updateComment({
                comment_id: existingComment.id,
                owner: context.repo.owner,
                repo: context.repo.repo,
                body: body
              });
            } else {
              // Create new comment
              await github.rest.issues.createComment({
                issue_number: context.issue.number,
                owner: context.repo.owner,
                repo: context.repo.repo,
                body: body
              });
            }