benchmark.yaml

on:
  push


jobs:
  benchmark-sqlite:
    name: "⏲️ Benchmark: ${{ matrix.config.backend }}"
    runs-on: ubuntu-latest

    strategy:
      fail-fast: false
      matrix:
        config:
          - backend: SQLite
            id: sqlite
            args: 'list(dbname = file.path(tempdir(), "SQLite.SQLite"))'
            schemas: ''

          - backend: SQLite - w. schemas
            id: sqlite-schemas
            args: 'list(dbname = file.path(tempdir(), "SQLite_schemas.SQLite"))'
            schemas: test_ds,not_test_ds

    env:
      BACKEND: ${{ matrix.config.backend }}
      BACKEND_DRV: RSQLite::SQLite
      BACKEND_ARGS: ${{ matrix.config.args }}

    steps:
      - uses: actions/checkout@v4

      - uses: r-lib/actions/setup-r@v2
        with:
          use-public-rspm: true

      - uses: r-lib/actions/setup-r-dependencies@v2
        with:
          cache-version: '1'
          extra-packages: any::devtools

      - name: Setup BACKEND_POST_CONNECT
        if: ${{ matrix.config.schemas != '' }}
        run: |
          set -o xtrace
          IFS=',' read -ra schemas <<< "${{ matrix.config.schemas }}"

          CONN_POST_CONNECT="list("
          for schema in "${schemas[@]}"; do
            CONN_POST_CONNECT+="paste0(\"ATTACH '\", file.path(tempdir(), \"SQLite_schemas_${schema}.SQLite\"), \"' AS '${schema}'\"), "
          done
          # Trim trailing ", " from CONN_POST_CONNECT
          CONN_POST_CONNECT=${CONN_POST_CONNECT%??}
          CONN_POST_CONNECT+=")"

          echo "BACKEND_POST_CONNECT=$CONN_POST_CONNECT" >> $GITHUB_ENV

      - name: Run benchmark
        run: source("./data-raw/benchmark_update_snapshot.R", echo=TRUE)
        shell: Rscript {0}

      - name: Upload benchmark results
        uses: actions/upload-artifact@v4
        with:
          name: benchmark-${{ matrix.config.id }}
          path: data/update_snapshot_benchmark.rda


  benchmark-duckdb:
    name: "⏲️ Benchmark: DuckDB"
    runs-on: ubuntu-latest

    env:
      BACKEND: DuckDB
      BACKEND_DRV: duckdb::duckdb
      BACKEND_ARGS: 'list(dbdir = file.path(tempdir(), "DuckDB.duckdb"))'

    steps:
      - uses: actions/checkout@v4

      - uses: r-lib/actions/setup-r@v2
        with:
          use-public-rspm: true

      - uses: r-lib/actions/setup-r-dependencies@v2
        with:
          cache-version: ${{ inputs.cache_version }}
          extra-packages: any::devtools

      - name: Setup BACKEND_POST_CONNECT
        run: |
          set -o xtrace
          IFS=',' read -ra schemas <<< "test_ds,not_test_ds"

          CONN_POST_CONNECT="list("
          for schema in "${schemas[@]}"; do
            CONN_POST_CONNECT+="\"CREATE SCHEMA IF NOT EXISTS '${schema}';\", "
          done
          # Trim trailing ", " from CONN_POST_CONNECT
          CONN_POST_CONNECT=${CONN_POST_CONNECT%??}
          CONN_POST_CONNECT+=")"

          echo "BACKEND_POST_CONNECT=$CONN_POST_CONNECT" >> $GITHUB_ENV

      - name: Run benchmark
        run: source("./data-raw/benchmark_update_snapshot.R", echo=TRUE)
        shell: Rscript {0}

      - name: Upload benchmark results
        uses: actions/upload-artifact@v4
        with:
          name: benchmark-duckdb
          path: data/update_snapshot_benchmark.rda


  benchmark-postgres:
    name: "⏲️ Benchmark: PostgreSQL"
    runs-on: ubuntu-latest

    services:
      postgres:
        image: postgres:latest
        env:
          POSTGRES_DB: test
          POSTGRES_USER: postgres
          POSTGRES_PASSWORD: postgres
        ports:
          - 5432:5432
        options: --health-cmd "pg_isready -U postgres" --health-interval 10s --health-timeout 5s --health-retries 5

    env:
      BACKEND: PostgreSQL
      BACKEND_DRV: RPostgres::Postgres
      BACKEND_ARGS: ''

      PGHOST: localhost
      PGPORT: 5432
      PGDATABASE: test
      PGUSER: postgres
      PGPASSWORD: postgres

    steps:
      - name: Setup testing schemata in PostgreSQL
        run: |
          set -o xtrace
          IFS=',' read -ra schemas <<< "test_ds,not_test_ds"
          for schema in "${schemas[@]}"; do
            psql test -c "CREATE SCHEMA \"$schema\";"
          done

      - uses: actions/checkout@v4

      - uses: r-lib/actions/setup-r@v2
        with:
          use-public-rspm: true

      - uses: r-lib/actions/setup-r-dependencies@v2
        with:
          cache-version: '1'
          extra-packages: any::devtools

      - name: Run benchmark
        run: source("./data-raw/benchmark_update_snapshot.R", echo=TRUE)
        shell: Rscript {0}

      - name: Upload benchmark results
        uses: actions/upload-artifact@v4
        with:
          name: benchmark-postgres
          path: data/update_snapshot_benchmark.rda


  benchmark-mssql:
    name: "⏲️ Benchmark: SQL Server 2019"
    runs-on: ubuntu-latest

    env:
      BACKEND: MSSQL
      BACKEND_DRV: odbc::odbc
      BACKEND_ARGS: ''

      CONN_ARGS_JSON: >
        {
          "MSSQL": {
            "driver": "ODBC Driver 17 for SQL Server",
            "server": "localhost",
            "database": "master",
            "UID": "SA",
            "PWD": "dbatools.I0"
          }
        }

    steps:
      - name: Install a SQL Server suite of tools
        uses: potatoqualitee/mssqlsuite@v1.7
        with:
          install: sqlengine, sqlpackage, sqlclient
          show-log: true

      - name: Setup testing schemata in SQL server
        run: |
          set -o xtrace

          sqlcmd -V 10 -S localhost -U SA -P dbatools.I0 -Q "ALTER LOGIN SA WITH DEFAULT_DATABASE = master;"

          IFS=',' read -ra schemas <<< "test_ds,not_test_ds"
          for schema in "${schemas[@]}"; do
            sqlcmd -V 10 -S localhost -U SA -P dbatools.I0 -d master -Q "CREATE SCHEMA [$schema];"
            sqlcmd -V 10 -S localhost -U SA -P dbatools.I0 -d master -Q "GO"
          done

          sqlcmd -V 10 -S localhost -U SA -P dbatools.I0 -d master -Q "SELECT * FROM sys.schemas"

          sqlcmd -V 10 -S localhost -U SA -P dbatools.I0 -d master -Q "SELECT schema_name FROM INFORMATION_SCHEMA.SCHEMATA WHERE schema_name = 'test'"
          sqlcmd -V 10 -S localhost -U SA -P dbatools.I0 -d master -Q "SELECT schema_name FROM INFORMATION_SCHEMA.SCHEMATA WHERE schema_name = 'test.one'"

      - uses: actions/checkout@v4

      - uses: r-lib/actions/setup-r@v2
        with:
          use-public-rspm: true

      - uses: r-lib/actions/setup-r-dependencies@v2
        with:
          cache-version: '1'
          extra-packages: any::devtools

      - name: Run benchmark
        run: source("./data-raw/benchmark_update_snapshot.R", echo=TRUE)
        shell: Rscript {0}

      - name: Upload benchmark results
        uses: actions/upload-artifact@v4
        with:
          name: benchmark-mssql
          path: data/update_snapshot_benchmark.rda


  benchmark-summary:
    name: "⏲️ Benchmark summary"
    needs: [
      benchmark-sqlite,
      benchmark-duckdb,
      benchmark-postgres,
      benchmark-mssql
    ]
    if: ${{ always() && !cancelled() }}
    runs-on: ubuntu-latest

    steps:
      - name: Download artifacts
        uses: actions/download-artifact@v4
        with:
          path: data

      - uses: r-lib/actions/setup-r@v2
        with:
          use-public-rspm: true

      - uses: r-lib/actions/setup-r-dependencies@v2
        with:
          extra-packages: any::purrr, any::microbenchmark

      - name: Display structure of downloaded files
        run: ls -R

      - name: Combine benchmark results
        run: |
          benchmark_files <- list.files(
            "data",
            pattern = "update_snapshot_benchmark.rda",
            full.names = TRUE,
            recursive = TRUE)

          benchmarks <- benchmark_files |>
            purrr::map(readRDS) |>
            purrr::reduce(rbind)

          print(benchmarks)

          pdf("benchmarks.pdf")
          plot(benchmarks)
          dev.off()

        shell: Rscript {0}

      - name: Upload benchmark summary
        uses: actions/upload-artifact@v4
        with:
          name: benchmark-summary
          path: benchmarks.pdf