maven.yml

# Template: https://help.github.com/actions/language-and-framework-guides/building-and-testing-java-with-maven
#
# Useful links
# - GitHub Actions: https://docs.github.com/en/actions/learn-github-actions/introduction-to-github-actions
# - Service containers: https://docs.github.com/en/actions/guides/creating-postgresql-service-containers
#
# The CI jobs are set up as follows:
# - One job to build and upload artifacts.
# - One job per DBMS test suite.
# - One job to build/test the docker images.
# - One job to publish the docker image.

name: BenchBase (Java with Maven)

on:
  push:
    branches: [ main ]
    # Generate new docker images on release tags.
    tags:
      - 'v*'
  pull_request:
    branches: [ main ]
  # Run these workflows on a schedule so that docker images are regularly updated for security patches.
  schedule:
    - cron: "1 0 * * *"
  # Give us a button to allow running the workflow on demand for testing.
  workflow_dispatch:
    inputs:
      tags:
        description: 'Manual Workflow Run'
        required: false
        type: string

env:
  POM_VERSION: 2021-SNAPSHOT
  JAVA_VERSION: 17
  ERRORS_THRESHOLD: 0.01

jobs:
  compile-and-test:
    runs-on: ubuntu-latest
    steps:
      - name: Checkout repo
        uses: actions/checkout@v4

      - name: Set up JDK
        uses: actions/setup-java@v3
        with:
          java-version: ${{env.JAVA_VERSION}}
          cache: 'maven'
          distribution: 'temurin'

      - name: Test with Maven
        run: mvn -B test --file pom.xml

  package-and-upload:
    needs: compile-and-test
    runs-on: ubuntu-latest
    strategy:
      matrix:
        profile: [ 'cockroachdb', 'mariadb', 'mysql', 'postgres', 'spanner', 'phoenix', 'sqlserver', 'sqlite' ]
    steps:
    - name: Checkout repo
      uses: actions/checkout@v4

    - name: Set up JDK
      uses: actions/setup-java@v3
      with:
        java-version: ${{env.JAVA_VERSION}}
        cache: 'maven'
        distribution: 'temurin'

    - name: Package with Maven
      run: mvn -B package -P ${{matrix.profile}} --file pom.xml -DskipTests -D descriptors=src/main/assembly/tgz.xml

    - name: Upload TGZ artifact
      uses: actions/upload-artifact@v3
      with:
        name: benchbase-${{matrix.profile}}
        path: target/benchbase-${{matrix.profile}}.tgz

  ## ----------------------------------------------------------------------------------
  ## SQLITE
  ## ----------------------------------------------------------------------------------
  sqlite:
    needs: package-and-upload
    runs-on: ubuntu-latest
    strategy:
      fail-fast: false
      matrix:
        # BROKEN: tpch
        benchmark: [ 'epinions', 'hyadapt', 'noop', 'otmetrics', 'resourcestresser', 'seats', 'sibench', 'smallbank', 'tatp', 'templated', 'tpcc', 'twitter', 'voter', 'wikipedia', 'ycsb' ]
    steps:
      - name: Download artifact
        uses: actions/download-artifact@v3
        with:
          name: benchbase-sqlite

      - name: Extract artifact
        run: |
          tar xvzf benchbase-sqlite.tgz --strip-components=1

      - name: Delete artifact
        run: |
          rm -rf benchbase-sqlite.tgz

      - name: Set up JDK
        uses: actions/setup-java@v3
        with:
          java-version: ${{env.JAVA_VERSION}}
          distribution: 'temurin'

      - name: Run benchmark
        run: |
          # For templated benchmarks, we need to preload some data for the test since by design, templated benchmarks do not support the 'load' operation
          # In this case, we load the tpcc data.
          if [[ ${{matrix.benchmark}} == templated ]]; then
            # Disable synchronous mode for sqlite tpcc data loading to save some time.
            java -jar benchbase.jar -b tpcc -c config/sqlite/sample_tpcc_nosync_config.xml --create=true --load=true --execute=false --json-histograms results/histograms.json
            # Run the templated benchmark.
            java -jar benchbase.jar -b ${{matrix.benchmark}} -c config/sqlite/sample_${{matrix.benchmark}}_config.xml --create=false --load=false --execute=true --json-histograms results/histograms.json
          else
            java -jar benchbase.jar -b ${{matrix.benchmark}} -c config/sqlite/sample_${{matrix.benchmark}}_config.xml --create=true --load=true --execute=true --json-histograms results/histograms.json
          fi
          # FIXME: Reduce the error rate so we don't need these overrides.
          if [ ${{matrix.benchmark}} == auctionmark ]; then
              ERRORS_THRESHOLD=0.02
          elif [ ${{matrix.benchmark}} == resourcestresser ]; then
              ERRORS_THRESHOLD=0.05
          elif [ ${{matrix.benchmark}} == smallbank ]; then
              ERRORS_THRESHOLD=0.03
          elif [ ${{matrix.benchmark}} == tatp ]; then
              ERRORS_THRESHOLD=0.05
          fi
          ./scripts/check_histogram_results.sh results/histograms.json $ERRORS_THRESHOLD

  ## ----------------------------------------------------------------------------------
  ## MARIADB
  ## ----------------------------------------------------------------------------------
  mariadb:
    needs: package-and-upload
    runs-on: ubuntu-latest
    strategy:
      fail-fast: false
      matrix:
        benchmark: [ 'auctionmark', 'epinions', 'hyadapt', 'noop', 'otmetrics', 'resourcestresser', 'seats', 'sibench', 'smallbank', 'tatp', 'templated', 'tpcc', 'twitter', 'voter', 'wikipedia', 'ycsb' ]
    services:
      mariadb: # https://hub.docker.com/_/mariadb
        image: mariadb:latest
        env:
          MARIADB_ROOT_PASSWORD: rootyMcRooty
          MARIADB_DATABASE: benchbase
          MARIADB_USER: admin
          MARIADB_PASSWORD: password
        # Use status instead of ping due to exit code issues: https://jira.mariadb.org/browse/MDEV-31550
        options: >-
          --health-cmd "mariadb-admin status -uroot -prootyMcRooty"
          --health-interval 10s
          --health-timeout 5s
          --health-retries 5
        ports:
          - 3306:3306
    steps:
      - name: Download artifact
        uses: actions/download-artifact@v3
        with:
          name: benchbase-mariadb

      - name: Extract artifact
        run: |
          tar xvzf benchbase-mariadb.tgz --strip-components=1

      - name: Delete artifact
        run: |
          rm -rf benchbase-mariadb.tgz

      - name: Set up JDK
        uses: actions/setup-java@v3
        with:
          java-version: ${{env.JAVA_VERSION}}
          distribution: 'temurin'

      - name: Run benchmark
        env:
          MARIADB_PORT: ${{ job.services.mariadb.ports[3306] }}
        run: |
          mysql -h127.0.0.1 -P$MARIADB_PORT -uadmin -ppassword -e "DROP DATABASE IF EXISTS benchbase; CREATE DATABASE benchbase"

          # For templated benchmarks, we need to preload some data for the test since by design, templated benchmarks do not support the 'load' operation
          # In this case, we load the tpcc data.
          if [[ ${{matrix.benchmark}} == templated ]]; then
            java -jar benchbase.jar -b tpcc -c config/mariadb/sample_tpcc_config.xml --create=true --load=true --execute=false --json-histograms results/histograms.json
            java -jar benchbase.jar -b ${{matrix.benchmark}} -c config/mariadb/sample_${{matrix.benchmark}}_config.xml --create=false --load=false --execute=true --json-histograms results/histograms.json
          else
            java -jar benchbase.jar -b ${{matrix.benchmark}} -c config/mariadb/sample_${{matrix.benchmark}}_config.xml --create=true --load=true --execute=true --json-histograms results/histograms.json
          fi

          # FIXME: Reduce the error rate so we don't need these overrides.
          if [ ${{matrix.benchmark}} == auctionmark ]; then
              ERRORS_THRESHOLD=0.02
          elif [ ${{matrix.benchmark}} == tatp ]; then
              ERRORS_THRESHOLD=0.05
          fi
          ./scripts/check_histogram_results.sh results/histograms.json $ERRORS_THRESHOLD

  ## ----------------------------------------------------------------------------------
  ## MYSQL
  ## ----------------------------------------------------------------------------------
  mysql:
    needs: package-and-upload
    runs-on: ubuntu-latest
    strategy:
      fail-fast: false
      matrix:
        benchmark: [ 'auctionmark', 'epinions', 'hyadapt', 'noop', 'otmetrics', 'resourcestresser', 'seats', 'sibench', 'smallbank', 'tatp', 'templated', 'tpcc', 'twitter', 'voter', 'wikipedia', 'ycsb' ]
    services:
      mysql: # https://hub.docker.com/_/mysql
        image: mysql:latest
        env:
          MYSQL_ROOT_PASSWORD: rootyMcRooty
          MYSQL_DATABASE: benchbase
          MYSQL_USER: admin
          MYSQL_PASSWORD: password
        options: >-
          --health-cmd "mysqladmin ping"
          --health-interval 10s
          --health-timeout 5s
          --health-retries 5
        ports:
          - 3306:3306
    steps:
      - name: Download artifact
        uses: actions/download-artifact@v3
        with:
          name: benchbase-mysql

      - name: Extract artifact
        run: |
          tar xvzf benchbase-mysql.tgz --strip-components=1

      - name: Delete artifact
        run: |
          rm -rf benchbase-mysql.tgz

      - name: Set up JDK
        uses: actions/setup-java@v3
        with:
          java-version: ${{env.JAVA_VERSION}}
          distribution: 'temurin'

      - name: Run benchmark
        env:
          MYSQL_PORT: ${{ job.services.mysql.ports[3306] }}
        run: |
          mysql -h127.0.0.1 -P$MYSQL_PORT -uadmin -ppassword -e "DROP DATABASE IF EXISTS benchbase; CREATE DATABASE benchbase"

          # For templated benchmarks, we need to preload some data for the test since by design, templated benchmarks do not support the 'load' operation
          # In this case, we load the tpcc data.
          if [[ ${{matrix.benchmark}} == templated ]]; then
            java -jar benchbase.jar -b tpcc -c config/mysql/sample_tpcc_config.xml --create=true --load=true --execute=false --json-histograms results/histograms.json
            java -jar benchbase.jar -b ${{matrix.benchmark}} -c config/mysql/sample_${{matrix.benchmark}}_config.xml --create=false --load=false --execute=true --json-histograms results/histograms.json
          else
            java -jar benchbase.jar -b ${{matrix.benchmark}} -c config/mysql/sample_${{matrix.benchmark}}_config.xml --create=true --load=true --execute=true --json-histograms results/histograms.json
          fi

          # FIXME: Reduce the error rate so we don't need these overrides.
          if [ ${{matrix.benchmark}} == auctionmark ]; then
              ERRORS_THRESHOLD=0.02
          elif [ ${{matrix.benchmark}} == tatp ]; then
              ERRORS_THRESHOLD=0.05
          fi
          ./scripts/check_histogram_results.sh results/histograms.json $ERRORS_THRESHOLD

  ## ----------------------------------------------------------------------------------
  ## POSTGRESQL
  ## ----------------------------------------------------------------------------------
  postgresql:
    needs: package-and-upload
    runs-on: ubuntu-latest
    strategy:
      fail-fast: false
      matrix:
        benchmark: [ 'auctionmark', 'epinions', 'hyadapt', 'noop', 'otmetrics', 'resourcestresser', 'seats', 'sibench', 'smallbank', 'tatp', 'templated', 'tpcc', 'tpch', 'twitter', 'voter', 'wikipedia', 'ycsb' ]
    services:
      postgres: # https://hub.docker.com/_/postgres
        image: postgres:latest
        env:
          POSTGRES_DB: benchbase
          POSTGRES_USER: admin
          POSTGRES_PASSWORD: password
        options: >-
          --health-cmd pg_isready
          --health-interval 10s
          --health-timeout 5s
          --health-retries 5
        ports:
          - 5432:5432
    steps:
      - name: Download artifact
        uses: actions/download-artifact@v3
        with:
          name: benchbase-postgres

      - name: Extract artifact
        run: |
          tar xvzf benchbase-postgres.tgz --strip-components=1

      - name: Delete artifact
        run: |
          rm -rf benchbase-postgres.tgz

      - name: Set up JDK
        uses: actions/setup-java@v3
        with:
          java-version: ${{env.JAVA_VERSION}}
          distribution: 'temurin'

      - name: Run benchmark
        run: |
          PGPASSWORD=password dropdb -h localhost -U admin benchbase --if-exists
          PGPASSWORD=password createdb -h localhost -U admin benchbase

          # For templated benchmarks, we need to preload some data for the test since by design, templated benchmarks do not support the 'load' operation
          # In this case, we load the tpcc data.
          if [[ ${{matrix.benchmark}} == templated ]]; then
            java -jar benchbase.jar -b tpcc -c config/postgres/sample_tpcc_config.xml --create=true --load=true --execute=false --json-histograms results/histograms.json
            java -jar benchbase.jar -b ${{matrix.benchmark}} -c config/postgres/sample_${{matrix.benchmark}}_config.xml --create=false --load=false --execute=true --json-histograms results/histograms.json
          else
            java -jar benchbase.jar -b ${{matrix.benchmark}} -c config/postgres/sample_${{matrix.benchmark}}_config.xml --create=true --load=true --execute=true --json-histograms results/histograms.json
          fi

          # FIXME: Reduce the error rate so we don't need these overrides.
          if [ ${{matrix.benchmark}} == auctionmark ]; then
              ERRORS_THRESHOLD=0.02
          elif [ ${{matrix.benchmark}} == tatp ]; then
              ERRORS_THRESHOLD=0.05
          fi
          ./scripts/check_histogram_results.sh results/histograms.json $ERRORS_THRESHOLD

  ## ----------------------------------------------------------------------------------
  ## COCKROACHDB
  ## ----------------------------------------------------------------------------------
  cockroachdb:
    needs: package-and-upload
    runs-on: ubuntu-latest
    strategy:
      fail-fast: false
      matrix:
        benchmark: [ 'auctionmark', 'epinions', 'hyadapt', 'noop', 'otmetrics', 'resourcestresser', 'seats', 'sibench', 'smallbank', 'tatp', 'tpcc', 'tpch', 'twitter', 'voter', 'wikipedia', 'ycsb' ]
    services:
      cockroach: # https://hub.docker.com/repository/docker/timveil/cockroachdb-single-node
        image: timveil/cockroachdb-single-node:latest
        env:
          DATABASE_NAME: benchbase
          MEMORY_SIZE: .75
        ports:
          - 26257:26257
    steps:
      - name: Download artifact
        uses: actions/download-artifact@v3
        with:
          name: benchbase-cockroachdb

      - name: Extract artifact
        run: |
          tar xvzf benchbase-cockroachdb.tgz --strip-components=1

      - name: Delete artifact
        run: |
          rm -rf benchbase-cockroachdb.tgz

      - name: Set up JDK
        uses: actions/setup-java@v3
        with:
          java-version: ${{env.JAVA_VERSION}}
          distribution: 'temurin'

      - name: Run benchmark
        run: |
          java -jar benchbase.jar -b ${{matrix.benchmark}} -c config/cockroachdb/sample_${{matrix.benchmark}}_config.xml --create=true --load=true --execute=true --json-histograms results/histograms.json
          # FIXME: Reduce the error rate so we don't need these overrides.
          # FIXME: Reduce the error rate so we don't need these overrides.
          if [ ${{matrix.benchmark}} == auctionmark ]; then
              ERRORS_THRESHOLD=0.02
          elif [ ${{matrix.benchmark}} == tatp ]; then
              ERRORS_THRESHOLD=0.05
          fi
          ./scripts/check_histogram_results.sh results/histograms.json $ERRORS_THRESHOLD

  ## ----------------------------------------------------------------------------------
  ## MSSQL
  ## ----------------------------------------------------------------------------------
  sqlserver:
    needs: package-and-upload
    runs-on: ubuntu-latest
    strategy:
      fail-fast: false
      matrix:
        # TODO: add more benchmarks
        #benchmark: [ 'auctionmark', 'epinions', 'hyadapt', 'noop', 'otmetrics', 'resourcestresser', 'seats', 'sibench', 'smallbank', 'tatp', 'tpcc', 'tpch', 'twitter', 'voter', 'wikipedia', 'ycsb' ]
        benchmark: [ 'epinions', 'hyadapt', 'noop', 'otmetrics', 'resourcestresser', 'sibench', 'smallbank', 'tatp', 'tpcc', 'templated', 'tpch', 'twitter', 'voter', 'wikipedia', 'ycsb' ]
    services:
      sqlserver:
        image: mcr.microsoft.com/mssql/server:latest
        env:
          ACCEPT_EULA: Y
          SA_PASSWORD: SApassword1
        options: >-
          --health-cmd "/opt/mssql-tools/bin/sqlcmd -S localhost -U sa -P SApassword1 -b -Q 'SELECT 1;'"
          --health-interval 10s
          --health-timeout 5s
          --health-retries 5
        ports:
          - 1433:1433
    steps:
      - name: Download artifact
        uses: actions/download-artifact@v3
        with:
          name: benchbase-sqlserver

      - name: Extract artifact
        run: |
          tar xvzf benchbase-sqlserver.tgz --strip-components=1

      - name: Delete artifact
        run: |
          rm -rf benchbase-sqlserver.tgz

      - name: Set up JDK
        uses: actions/setup-java@v3
        with:
          java-version: ${{env.JAVA_VERSION}}
          distribution: 'temurin'

      - name: Cleanup database
        uses: docker://mcr.microsoft.com/mssql-tools:latest
        with:
          entrypoint: /opt/mssql-tools/bin/sqlcmd
          args: -U sa -P SApassword1 -S sqlserver -b -Q "DROP DATABASE IF EXISTS benchbase;"

      - name: Setup database
        uses: docker://mcr.microsoft.com/mssql-tools:latest
        with:
          entrypoint: /opt/mssql-tools/bin/sqlcmd
          args: -U sa -P SApassword1 -S sqlserver -b -Q "CREATE DATABASE benchbase;"

      - name: Setup login
        uses: docker://mcr.microsoft.com/mssql-tools:latest
        with:
          entrypoint: /opt/mssql-tools/bin/sqlcmd
          args: -U sa -P SApassword1 -S sqlserver -Q "CREATE LOGIN benchuser01 WITH PASSWORD='P@ssw0rd';"

      - name: Setup access
        uses: docker://mcr.microsoft.com/mssql-tools:latest
        with:
          entrypoint: /opt/mssql-tools/bin/sqlcmd
          args: -U sa -P SApassword1 -S sqlserver -b -Q "USE benchbase; CREATE USER benchuser01 FROM LOGIN benchuser01; EXEC sp_addrolemember 'db_owner', 'benchuser01';"

      - name: Run benchmark
        # Note: user/pass should match those used in sample configs.
        run: |
          # For templated benchmarks, we need to preload some data for the test since by design, templated benchmarks do not support the 'load' operation
          # In this case, we load the tpcc data.
          if [[ ${{matrix.benchmark}} == templated ]]; then
            java -jar benchbase.jar -b tpcc -c config/sqlserver/sample_tpcc_config.xml --create=true --load=true --execute=false --json-histograms results/histograms.json
            java -jar benchbase.jar -b ${{matrix.benchmark}} -c config/sqlserver/sample_${{matrix.benchmark}}_config.xml --create=false --load=false --execute=true --json-histograms results/histograms.json
          else
            java -jar benchbase.jar -b ${{matrix.benchmark}} -c config/sqlserver/sample_${{matrix.benchmark}}_config.xml --create=true --load=true --execute=true --json-histograms results/histograms.json
          fi

          # FIXME: Reduce the error rate so we don't need these overrides.
          if [ ${{matrix.benchmark}} == tatp ]; then
              ERRORS_THRESHOLD=0.05
          fi
          ./scripts/check_histogram_results.sh results/histograms.json $ERRORS_THRESHOLD

  ## ----------------------------------------------------------------------------------
  ## Docker Build Test Publish
  ## ----------------------------------------------------------------------------------
  docker-build-test-publish:
    runs-on: ubuntu-latest
    env:
      DOCKER_BUILDKIT: 1
      BENCHBASE_PROFILES: 'cockroachdb mariadb mysql postgres spanner phoenix sqlserver sqlite'
      CONTAINER_REGISTRY_NAME: ${{ secrets.ACR_LOGINURL }}
    services:
      postgres: # https://hub.docker.com/_/postgres
        image: postgres:latest
        env:
          POSTGRES_DB: benchbase
          POSTGRES_USER: admin
          POSTGRES_PASSWORD: password
        options: >-
          --health-cmd pg_isready
          --health-interval 10s
          --health-timeout 5s
          --health-retries 5
        ports:
          - 5432:5432
    steps:
      - name: Setup postgres test DB
        run: |
          PGPASSWORD=password dropdb -h localhost -U admin benchbase --if-exists
          PGPASSWORD=password createdb -h localhost -U admin benchbase
      - name: Checkout repo
        uses: actions/checkout@v4
      # https://github.com/actions/cache/blob/master/examples.md#java---maven
      - name: Cache local Maven repository
        uses: actions/cache@v3
        with:
          path: ~/.m2/repository
          key: setup-java-${{ runner.os }}-docker-maven-${{ hashFiles('**/pom.xml') }}
          restore-keys: |
            setup-java-${{ runner.os }}-docker-maven-
      - name: Pull base image caches for PR builds
        if: ${{ github.ref != 'refs/heads/main' }}
        run: |
          docker pull benchbase.azurecr.io/benchbase-dev:latest || true
          docker pull benchbase.azurecr.io/benchbase:latest || true
      - name: Set NO_CACHE env var for main branch builds
        if: ${{ github.ref == 'refs/heads/main' }}
        run: |
          echo "NO_CACHE=true" >> $GITHUB_ENV
      - name: Build benchbase-dev image
        run: |
          ./docker/benchbase/build-dev-image.sh
      # Note: this script maps the local .m2 cache into the container.
      - name: Build the benchbase docker image with all profiles using the dev image
        env:
          SKIP_TESTS: 'false'
        run: |
          ./docker/benchbase/build-full-image.sh
      - name: Run a basic test from the docker image against postgres test DB
        env:
            benchmark: noop
        run: |
          for image in benchbase benchbase-postgres; do
            # Adjust the sample config to talk to the container service instead of localhost.
            cat "config/postgres/sample_${benchmark}_config.xml" | sed -e 's/localhost:5432/postgres:5432/g' > /tmp/config.xml
            # Lookup the service container's docker network so we can place the benchbase container in it too.
            docker_network="$(docker ps --filter expose=5432 --format '{{.Networks}}')"
            # Map the adjusted config into the container and use it to run the test.
            rm -rf results
            mkdir -p results
            docker run --rm --name benchbase-postgres --network "$docker_network" \
              --env BENCHBASE_PROFILE=postgres -v /tmp/config.xml:/tmp/config.xml -v "$PWD/results:/benchbase/results" \
              "$image" -b "$benchmark" -c /tmp/config.xml --create=true --load=true --execute=true --json-histograms results/histograms.json
            # Test that the results files were produced.
            ls results/${benchmark}_*.csv
            ./scripts/check_histogram_results.sh results/histograms.json $ERRORS_THRESHOLD
          done
      # Publish the docker image if the build/test was successful.
      # Only do this with approved PRs and if the login secrets are available.
      # Typically we expect to publish to benchbase.azurecr.io,
      # but setting ACR_LOGINURL to something else allows us to do testing on forks.
      - name: Log in to Docker Hub
        if: ${{ (github.ref == 'refs/heads/main' || startsWith(github.ref, 'refs/tags/v')) && env.CONTAINER_REGISTRY_NAME != '' }}
        uses: docker/login-action@v3
        with:
          registry: ${{ secrets.ACR_LOGINURL }}
          username: ${{ secrets.ACR_USERNAME }}
          password: ${{ secrets.ACR_PASSWORD }}
      - name: Push Docker image
        if: ${{ (github.ref == 'refs/heads/main' || startsWith(github.ref, 'refs/tags/v')) && env.CONTAINER_REGISTRY_NAME != '' }}
        run: |
          docker push -a ${{ secrets.ACR_LOGINURL}}/benchbase-dev
          docker push -a ${{ secrets.ACR_LOGINURL}}/benchbase
          for profile in $BENCHBASE_PROFILES; do
            docker push -a ${{ secrets.ACR_LOGINURL }}/benchbase-$profile
          done