ci.yml

name: CI - dbLinter checks and SQL-based tests

on:
  push:
    branches:
      - '**'
    paths-ignore:
      - 'README.md'
      - 'images/**'
      - '.gitignore'
      - '.vscode/**'
  pull_request:
    branches:
      - '**'
    paths-ignore:
      - 'README.md'
      - 'images/**'
      - '.gitignore'
      - '.vscode/**'
  workflow_dispatch:

jobs:
  ci:
    runs-on: ubuntu-latest
    environment: dblinter
    permissions:
      contents: read
      pages: write
      id-token: write
      security-events: write
      checks: write
      pull-requests: write

    services:
      oracle:
        image: gvenzl/oracle-free:23.26.1-slim-faststart
        ports:
          - 1542:1521
        env:
          ORACLE_PASSWORD: oracle
        options: >-
          --name oracle-db
          --health-cmd healthcheck.sh
          --health-interval 10s
          --health-timeout 5s
          --health-retries 10

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

    - name: Set up JDK 21
      uses: actions/setup-java@v4
      with:
        java-version: '21'
        distribution: 'oracle'

    - name: Download and install SQLcl
      run: |
        wget -O sqlcl.zip https://download.oracle.com/otn_software/java/sqldeveloper/sqlcl-25.4.2.044.1837.zip
        unzip -q sqlcl.zip
        echo "$(pwd)/sqlcl/bin" >> $GITHUB_PATH

    - name: Run database installation script
      run: |
        sql sys/oracle@localhost:1542/FREEPDB1 as sysdba @install.sql

    - name: Prepare directory to be deployed to GitHub Pages
      if: always()
      run: |
        mkdir -p results
        if [ -f "install.log" ]; then
          cp install.log results/
          echo "install.log copied to results/"
        else
          echo "install.log not found, creating empty file"
          echo "install.log was not generated" > results/install.log
        fi

    - name: Download and install dbLinter CLI
      run: |
        wget -O dblinter.zip https://github.com/Grisselbav/dbLinter/releases/download/v1.3.0/dblinter-1.3.0.zip
        unzip dblinter.zip
        mv dblinter-* dblinter
        echo "$(pwd)/dblinter" >> $GITHUB_PATH

    - name: Run checks
      run: |
        echo "Running dbLinter checks..."
        dblinter \
          --tenantName=${{ secrets.DBLINTER_TENANT_NAME }} \
          --userName=${{ secrets.DBLINTER_USER_NAME }} \
          --accessToken=${{ secrets.DBLINTER_ACCESS_TOKEN }} \
          --configName=dbLinter-Demo \
          --connJdbcUrl="jdbc:oracle:thin:@localhost:1542/FREEPDB1" \
          --connUserName="dbl_read" \
          --connPassword="dbl_read" \
          --logLevel=trace \
          --logFile=results/check.log \
          --logFormat=ext3 \
          check \
          --outputFormats=vscode,sonarqube,sarif,checkstyle,github,gitlab \
          --outputName=results/check

    - name: SonarQube Scan
      uses: SonarSource/sonarqube-scan-action@v7.0.0
      env:
        GITHUB_TOKEN: ${{ secrets.GITHUB_TOKEN }}
        SONAR_TOKEN: ${{ secrets.SONAR_TOKEN }}

    - name: Rename partialFingerprints key in SARIF file
      run: |
        # CodeQL is always calculating its own hashes for primaryLocationLineHash.
        # To avoid inconsistency warnings, we rename the key in our SARIF output.
        sed -i 's/"primaryLocationLineHash":/"dbLinterPrimaryLocationLineHash":/g' results/check.sarif.sarif

    - name: Upload SARIF to GitHub Security
      uses: github/codeql-action/upload-sarif@v4
      with:
        sarif_file: results/check.sarif.sarif

    - name: Show dbLinter annotations
      run: |
        cat results/check.github.txt

    - name: Run SQL-based tests
      run: |
        echo "Running dbLinter tests..."
        dblinter \
          --tenantName=${{ secrets.DBLINTER_TENANT_NAME }} \
          --userName=${{ secrets.DBLINTER_USER_NAME }} \
          --accessToken=${{ secrets.DBLINTER_ACCESS_TOKEN }} \
          --configName=dbLinter-Demo \
          --connJdbcUrl="jdbc:oracle:thin:@localhost:1542/FREEPDB1" \
          --connUserName="dbl_read" \
          --connPassword="dbl_read" \
          --logLevel=trace \
          --logFile=results/test.log \
          --logFormat=ext3 \
          test \
          --outputFormats=vscode,junit \
          --outputName=results/test

    - name: Render markdown with GitHub GFM API
      env:
        GH_TOKEN: ${{ secrets.GITHUB_TOKEN }}
      run: |
        render_markdown() {
          local input_md=$1
          local output_html=$2
          local title=$3

          # Render markdown to HTML
          cat "$input_md" | gh api markdown \
            --method POST \
            -H "Accept: text/html" \
            -F mode=gfm \
            -F text=@- \
            > "${output_html}.body"

          # Add user-content- prefix to internal links
          sed -i 's/href="#\([^"]*\)"/href="#user-content-\1"/g' "${output_html}.body"

          # Convert internal links with user-content- prefix to lowercase
          sed -i 's/href="#user-content-\([^"]*\)"/\L&/g' "${output_html}.body"

          # Wrap with GitHub markdown styling
          cat > "$output_html" << 'EOF'
        <!DOCTYPE html>
        <html>
        <head>
          <meta charset="utf-8">
          <meta name="viewport" content="width=device-width, initial-scale=1">
        EOF
          echo "  <title>$title</title>" >> "$output_html"
          cat >> "$output_html" << 'EOF'
          <link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/github-markdown-css/5.8.1/github-markdown.min.css">
          <style>
            .markdown-body { box-sizing: border-box; min-width: 200px; max-width: 1200px; margin: 0 auto; padding: 45px; }
            @media (max-width: 767px) { .markdown-body { padding: 15px; } }
          </style>
        </head>
        <body>
          <article class="markdown-body">
        EOF
          cat "${output_html}.body" >> "$output_html"
          cat >> "$output_html" << 'EOF'
          </article>
        </body>
        </html>
        EOF
          rm "${output_html}.body"
        }

        # Render both reports
        render_markdown "results/check.vscode.md" "results/check-report.html" "dbLinter Check Report"
        render_markdown "results/test.vscode.md" "results/test-report.html" "dbLinter Test Report"

    - name: Publish Test Results (EnricoMi)
      uses: EnricoMi/publish-unit-test-result-action@v2
      if: (!cancelled())
      with:
        files: results/test.junit.xml
        report_suite_logs: any
        compare_to_earlier_commit: false
        fail_on: nothing

    - name: Publish Test Results (dorny)
      uses: dorny/test-reporter@v2
      if: ${{ !cancelled() }}
      with:
        name: dbLinter Tests
        path: results/test.junit.xml
        reporter: java-junit
        fail-on-error: false

    - name: Add dbLinter check report to summary page
      run: |
        echo "<a name=\"dblinter-check-report\"></a>" >> $GITHUB_STEP_SUMMARY
        echo "" >> $GITHUB_STEP_SUMMARY
        cat results/check.vscode.md >> $GITHUB_STEP_SUMMARY
        echo "" >> $GITHUB_STEP_SUMMARY

    - name: Add dbLinter test report to summary page
      run: |
        echo "<a name=\"dblinter-test-report\"></a>" >> $GITHUB_STEP_SUMMARY
        echo "" >> $GITHUB_STEP_SUMMARY
        cat results/test.vscode.md >> $GITHUB_STEP_SUMMARY
        echo "" >> $GITHUB_STEP_SUMMARY

    - name: Upload Results
      uses: actions/upload-artifact@v4
      if: always()
      with:
        name: results
        path: |
          results/*.*
        retention-days: 90

    - name: Setup GitHub Pages
      uses: actions/configure-pages@v5

    - name: Upload to GitHub Pages
      uses: actions/upload-pages-artifact@v4
      with:
        path: results

    - name: Deploy to GitHub Pages
      uses: actions/deploy-pages@v4