test-database-processing.yml

name: Test Database Processing

on:
  push:
  pull_request:
  workflow_dispatch:

jobs:
  test-database-processing:
    runs-on: ubuntu-latest

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

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

      - name: Install dependencies
        run: |
          sudo apt-get update
          sudo apt-get install -y unzip

      - name: Install SQLite from official release
        run: |
          # Ubuntu's SQLite 3.45.1 has a bug causing segfaults with complex views
          # Download official precompiled SQLite 3.50.4 instead
          SQLITE_VERSION=3500400
          SQLITE_YEAR=2025
          wget https://www.sqlite.org/${SQLITE_YEAR}/sqlite-tools-linux-x64-${SQLITE_VERSION}.zip
          unzip sqlite-tools-linux-x64-${SQLITE_VERSION}.zip
          sudo cp sqlite3 /usr/local/bin/
          sudo chmod +x /usr/local/bin/sqlite3
          echo "/usr/local/bin" >> $GITHUB_PATH

      - name: Verify installations
        run: |
          echo "=== System sqlite3 version ==="
          /usr/local/bin/sqlite3 --version
          echo "=== Python sqlite3 module version ==="
          python -c "import sqlite3; print('Python sqlite3 module uses SQLite version:', sqlite3.sqlite_version)"

      - name: Download and extract latest.zip
        run: |
          echo "Downloading latest.zip from HuggingFace..."
          wget -q --show-progress "https://huggingface.co/datasets/cbdb/cbdb-sqlite/resolve/main/latest.zip" -O latest.zip
          echo "Extracting latest.zip..."
          unzip latest.zip
          DB_FILE=$(ls cbdb_*.sqlite3 2>/dev/null | head -1)
          if [ -z "$DB_FILE" ]; then
            echo "Error: no cbdb_*.sqlite3 found after extraction"
            ls -la
            exit 1
          fi
          echo "Found database: $DB_FILE"
          ls -lh "$DB_FILE"
          echo "DB_FILE=$DB_FILE" >> $GITHUB_ENV

      - name: Run create_views.sh
        run: |
          echo "Running create_views.sh..."
          chmod +x scripts/create_views.sh
          scripts/create_views.sh "$DB_FILE"
          echo "Views created successfully."

      - name: Verify database integrity
        run: |
          echo "Checking database integrity..."
          sqlite3 "$DB_FILE" "PRAGMA integrity_check;"
          echo "Verifying views exist..."
          sqlite3 "$DB_FILE" "SELECT name FROM sqlite_master WHERE type='view' ORDER BY name;"
          echo "All checks passed!"

      - name: Upload database for debugging
        if: always()
        uses: actions/upload-artifact@v4
        with:
          name: debug-db
          path: ${{ env.DB_FILE }}
          retention-days: 7