databaseMigrationChecks.yml

name: Database Migration Checks

on:
  pull_request:
    paths:
      - wres-io/dist/lib/conf/database/*

jobs:
  # Label of the runner job
  database-migration-check:
    runs-on: ubuntu-latest

    services:
      postgres:
        image: postgres
        env:
          POSTGRES_PASSWORD: postgres
          POSTGRES_DB: wres8
          POSTGRES_USER: wres_user

        ports:
          - 5432:5432
        # needed because the postgres container does not provide a healthcheck
        options: >-
          --health-cmd pg_isready 
          --health-interval 10s 
          --health-timeout 5s 
          --health-retries 5
    steps:
      - uses: actions/checkout@v1
        with:
          fetch-depth: 0

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

      - name: Install PostgreSQL client
        run: |
          sudo apt-get update
          sudo apt-get install --yes postgresql-client

      # Create wres schema
      - name: Create wres schema
        run: |
          psql -h localhost -d wres8 -U wres_user -c 'create schema wres authorization wres_user;'
        env:
          # postgress password is required; alternatively, you can run:
          # `PGPASSWORD=postgres_password psql ...`
          PGPASSWORD: postgres

      - name: Create directories for downloads
        run: |
          mkdir upcomingRelease
          mkdir pastRelease

      - name: Download latest release
        uses: robinraju/release-downloader@v1
        with:
          latest: true
          # Download the attached zipball (*.zip)
          tarBall: true
          zipBall: true
          fileName: '*.zip'
          out-file-path: 'pastRelease'

      # Configure Gradle for optimal use in GiHub Actions, including caching of downloaded dependencies.
      # See: https://github.com/gradle/actions/blob/main/setup-gradle/README.md
      - name: Setup Gradle
        uses: gradle/actions/setup-gradle@417ae3ccd767c252f5661f1ace9f835f9654f2b5 # v3.1.0

      # Run and build using gradle
      # Disable aggregateJavaDocs
      - name: Run build
        run: ./gradlew distZip testCodeCoverageReport javadoc

      # moving current files to their correct directory
      - name: move current files
        run: |
          mv build/distributions/wres-*.zip upcomingRelease

      - name: Migrate database from latest release
        run: |
          cd pastRelease
          ls | grep -P 'wres-\d{8}-.*' | xargs chmod 777
          ls | grep -P 'wres-\d{8}-.*' | xargs unzip
          echo $(ls | grep -P 'wres-\d{8}-.{7}((?!zip).)*$')
          cd $(ls | grep -P 'wres-\d{8}-.{7}((?!zip).)*$')
          cd bin
          mkdir smalldata
          cp ../../../systests/smalldata/1985043012_DRRC2FAKE1_forecast.xml smalldata
          cp ../../../systests/smalldata/1985043013_DRRC2FAKE1_forecast.xml smalldata
          cp ../../../systests/smalldata/1985043014_DRRC2FAKE1_forecast.xml smalldata
          cp ../../../systests/smalldata/DRRC2QINE_FAKE_19850430.xml smalldata
          JAVA_OPTS="-Dwres.useDatabase=true -Dwres.useSSL=false -Dwres.username=wres_user -Dwres.databaseHost=localhost -Dwres.databaseName=wres8 -Dwres.password=postgres" ./wres execute ../../../systests/scenario500/evaluation.yml

      - name: Run upcoming release
        run: |
          cd upcomingRelease
          ls | grep -P 'wres-\d{8}-.*' | xargs chmod 777
          ls | grep -P 'wres-\d{8}-.*' | xargs unzip
          echo $(ls | grep -P 'wres-\d{8}-.{7}((?!zip).)*$')
          cd $(ls | grep -P 'wres-\d{8}-.{7}((?!zip).)*$')
          cd bin
          mkdir smalldata
          cp ../../../systests/smalldata/1985043012_DRRC2FAKE1_forecast.xml smalldata
          cp ../../../systests/smalldata/1985043013_DRRC2FAKE1_forecast.xml smalldata
          cp ../../../systests/smalldata/1985043014_DRRC2FAKE1_forecast.xml smalldata
          cp ../../../systests/smalldata/DRRC2QINE_FAKE_19850430.xml smalldata
          JAVA_OPTS="-Dwres.useDatabase=true -Dwres.useSSL=false -Dwres.username=wres_user -Dwres.databaseHost=localhost -Dwres.databaseName=wres8 -Dwres.password=postgres" ./wres execute ../../../systests/scenario500/evaluation.yml

      # drops table and re-create it to test migration from fresh new release
      - name: Drops table to test fresh migration from upcoming
        run: |
          psql -h localhost -d wres8 -U wres_user -c 'drop schema wres CASCADE;'
          psql -h localhost -d postgres -U wres_user -c 'drop database wres8;'
          psql -h localhost -d postgres -U wres_user -c 'create database wres8 owner wres_user;'
          psql -h localhost -d wres8 -U wres_user -c 'create schema wres authorization wres_user;'
        env:
          # postgress password is required; alternatively, you can run:
          # `PGPASSWORD=postgres_password psql ...`
          PGPASSWORD: postgres

      - name: Fresh migration upcoming release
        run: |
          cd upcomingRelease
          cd $(ls | grep -P 'wres-\d{8}-.{7}((?!zip).)*$')
          cd bin
          JAVA_OPTS="-Dwres.useDatabase=true -Dwres.useSSL=false -Dwres.username=wres_user -Dwres.databaseHost=localhost -Dwres.databaseName=wres8 -Dwres.password=postgres" ./wres execute ../../../systests/scenario500/evaluation.yml