pgcmp.yml

name: Database Migrations Check PR

on:
  pull_request:
    paths:
      - "deployment/hasura/migrations/**"
      - "merlin-server/sql/**"
      - "sequencing-server/sql/**"
      - "scheduler-server/sql/**"
      - "deployment/postgres-init-db/sql/**"
  push:
    paths:
      - "deployment/hasura/migrations/**"
      - "merlin-server/sql/**"
      - "sequencing-server/sql/**"
      - "scheduler-server/sql/**"
      - "deployment/postgres-init-db/sql/**"
    branches:
      - develop
    tags:
      - v*
  workflow_dispatch:

jobs:
  dump-db-base:
    runs-on: ubuntu-latest
    environment: e2e-test
    steps:
      - name: Checkout v1.0.1
        uses: actions/checkout@v3
        with:
          ref: "v1.0.1"
      - name: Clone PGCMP
        uses: actions/checkout@v3
        with:
          repository: cbbrowne/pgcmp
          path: pgcmp
      - name: Setup Python
        uses: actions/setup-python@v4
        with:
          python-version: "3.10"
      - name: Setup Postgres Client (psql)
        run: |
          sudo apt-get update
          sudo apt-get install --yes postgresql-client
      - name: Setup Hasura CLI
        run: sudo curl -L https://github.com/hasura/graphql-engine/raw/stable/cli/get.sh | bash
      - name: Validate Gradle Wrapper
        uses: gradle/wrapper-validation-action@v1
      - name: Distribute SQL and Assemble Java
        uses: gradle/gradle-build-action@v2
        with:
          arguments: distributeSQL
      - name: Start Postgres
        run: |
          docker compose up -d postgres hasura
          docker images
          docker ps -a
        env:
          AERIE_USERNAME: "${{secrets.AERIE_USERNAME}}"
          AERIE_PASSWORD: "${{secrets.AERIE_PASSWORD}}"
          POSTGRES_USER: "${{secrets.POSTGRES_USER}}"
          POSTGRES_PASSWORD: "${{secrets.POSTGRES_PASSWORD}}"
          HASURA_GRAPHQL_ADMIN_SECRET: "${{secrets.HASURA_GRAPHQL_ADMIN_SECRET}}"
          HASURA_GRAPHQL_JWT_SECRET: "${{secrets.HASURA_GRAPHQL_JWT_SECRET}}"
      - name: Sleep for 1 Minute
        run: sleep 60s
        shell: bash
      - name: Dump v1.0.1 Database
        env:
          AERIE_USERNAME: "${{secrets.AERIE_USERNAME}}"
          AERIE_PASSWORD: "${{secrets.AERIE_PASSWORD}}"
        run: |
          mkdir pgdumpv1_0_1
          PGURI=postgres://"${AERIE_USERNAME}":"${AERIE_PASSWORD}"@localhost:5432/aerie_merlin PGCMPOUTPUT=./pgdumpv1_0_1/AerieMerlinV1_0_1 PGCLABEL=AerieMerlinV1_0_1 PGBINDIR=/usr/bin ./pgcmp/pgcmp-dump
          PGURI=postgres://"${AERIE_USERNAME}":"${AERIE_PASSWORD}"@localhost:5432/aerie_scheduler PGCMPOUTPUT=./pgdumpv1_0_1/AerieSchedulerV1_0_1 PGCLABEL=AerieSchedulerV1_0_1 PGBINDIR=/usr/bin ./pgcmp/pgcmp-dump
          PGURI=postgres://"${AERIE_USERNAME}":"${AERIE_PASSWORD}"@localhost:5432/aerie_sequencing PGCMPOUTPUT=./pgdumpv1_0_1/AerieSequencingV1_0_1 PGCLABEL=AerieSequencingV1_0_1 PGBINDIR=/usr/bin ./pgcmp/pgcmp-dump
          PGURI=postgres://"${AERIE_USERNAME}":"${AERIE_PASSWORD}"@localhost:5432/aerie_ui PGCMPOUTPUT=./pgdumpv1_0_1/AerieUIV1_0_1 PGCLABEL=AerieUIV1_0_1 PGBINDIR=/usr/bin ./pgcmp/pgcmp-dump
        shell: bash
      - name: Share Database Dump
        uses: actions/upload-artifact@v3
        with:
          name: v1_0_1-db-dump
          path: pgdumpv1_0_1
          retention-days: 1
      - name: Checkout Latest
        uses: actions/checkout@v3
      - name: Restart Hasura
        run: |
          docker compose down
          docker compose up -d postgres hasura
          docker images
          docker ps -a
        env:
          AERIE_USERNAME: "${{secrets.AERIE_USERNAME}}"
          AERIE_PASSWORD: "${{secrets.AERIE_PASSWORD}}"
          POSTGRES_USER: "${{secrets.POSTGRES_USER}}"
          POSTGRES_PASSWORD: "${{secrets.POSTGRES_PASSWORD}}"
          HASURA_GRAPHQL_ADMIN_SECRET: "${{secrets.HASURA_GRAPHQL_ADMIN_SECRET}}"
          HASURA_GRAPHQL_JWT_SECRET: "${{secrets.HASURA_GRAPHQL_JWT_SECRET}}"
      - name: Sleep for 30 seconds
        run: sleep 30s
        shell: bash
      - name: Migrate Base to Latest
        run: |
          cd deployment
          cat << EOF > .env
          AERIE_USERNAME=${AERIE_USERNAME}
          AERIE_PASSWORD=${AERIE_PASSWORD}
          EOF
          python -m pip install -r requirements.txt
          python aerie_db_migration.py --apply --all
          cd ..
        env:
          AERIE_USERNAME: "${{secrets.AERIE_USERNAME}}"
          AERIE_PASSWORD: "${{secrets.AERIE_PASSWORD}}"
      - name: Clone PGCMP
        uses: actions/checkout@v3
        with:
          repository: cbbrowne/pgcmp
          path: pgcmp
      - name: Dump Databases
        env:
          AERIE_USERNAME: "${{secrets.AERIE_USERNAME}}"
          AERIE_PASSWORD: "${{secrets.AERIE_PASSWORD}}"
        run: |
          mkdir pgdumpmigrated
          PGURI=postgres://"${AERIE_USERNAME}":"${AERIE_PASSWORD}"@localhost:5432/aerie_merlin PGCMPOUTPUT=./pgdumpmigrated/AerieMerlinMigrated PGCLABEL=AerieMerlinMigrated PGBINDIR=/usr/bin ./pgcmp/pgcmp-dump
          PGURI=postgres://"${AERIE_USERNAME}":"${AERIE_PASSWORD}"@localhost:5432/aerie_scheduler PGCMPOUTPUT=./pgdumpmigrated/AerieSchedulerMigrated PGCLABEL=AerieSchedulerMigrated PGBINDIR=/usr/bin ./pgcmp/pgcmp-dump
          PGURI=postgres://"${AERIE_USERNAME}":"${AERIE_PASSWORD}"@localhost:5432/aerie_sequencing PGCMPOUTPUT=./pgdumpmigrated/AerieSequencingMigrated PGCLABEL=AerieSequencingMigrated PGBINDIR=/usr/bin ./pgcmp/pgcmp-dump
          PGURI=postgres://"${AERIE_USERNAME}":"${AERIE_PASSWORD}"@localhost:5432/aerie_ui PGCMPOUTPUT=./pgdumpmigrated/AerieUIMigrated PGCLABEL=AerieUIMigrated PGBINDIR=/usr/bin ./pgcmp/pgcmp-dump
        shell: bash
      - name: Share Database Dump
        uses: actions/upload-artifact@v3
        with:
          name: migrated-db-dump
          path: pgdumpmigrated
          retention-days: 1
      - name: Print Logs for Services
        if: success() || failure()
        run: docker compose logs -t
      - name: Stop Postgres and Hasura
        run: |
          docker ps -a
          docker compose down
          docker ps -a
      - name: Prune Volumes
        run: docker volume prune --force

  dump-current:
    runs-on: ubuntu-latest
    environment: e2e-test
    steps:
      - name: Checkout Repo
        uses: actions/checkout@v3
      - name: Setup Postgres Client (psql)
        run: |
          sudo apt-get update
          sudo apt-get install --yes postgresql-client
      - name: Validate Gradle Wrapper
        uses: gradle/wrapper-validation-action@v1
      - name: Distribute SQL
        uses: gradle/gradle-build-action@v2
        with:
          arguments: distributeSQL
      - name: Setup Python
        uses: actions/setup-python@v4
        with:
          python-version: "3.10"
      - name: Setup Hasura CLI
        run: sudo curl -L https://github.com/hasura/graphql-engine/raw/stable/cli/get.sh | bash
      - name: Start Postgres and Hasura
        run: |
          docker compose up -d postgres hasura
          docker images
          docker ps -a
        env:
          AERIE_USERNAME: "${{secrets.AERIE_USERNAME}}"
          AERIE_PASSWORD: "${{secrets.AERIE_PASSWORD}}"
          POSTGRES_USER: "${{secrets.POSTGRES_USER}}"
          POSTGRES_PASSWORD: "${{secrets.POSTGRES_PASSWORD}}"
          HASURA_GRAPHQL_ADMIN_SECRET: "${{secrets.HASURA_GRAPHQL_ADMIN_SECRET}}"
          HASURA_GRAPHQL_JWT_SECRET: "${{secrets.HASURA_GRAPHQL_JWT_SECRET}}"
      - name: Sleep for 1 Minute
        run: sleep 60s
        shell: bash
      - name: Clone PGCMP
        uses: actions/checkout@v3
        with:
          repository: cbbrowne/pgcmp
          path: pgcmp
      - name: Dump Databases
        env:
          AERIE_USERNAME: "${{secrets.AERIE_USERNAME}}"
          AERIE_PASSWORD: "${{secrets.AERIE_PASSWORD}}"
        run: |
          mkdir pgdumpraw
          PGURI=postgres://"${AERIE_USERNAME}":"${AERIE_PASSWORD}"@localhost:5432/aerie_merlin PGCMPOUTPUT=./pgdumpraw/AerieMerlinRaw PGCLABEL=AerieMerlinRaw PGBINDIR=/usr/bin ./pgcmp/pgcmp-dump
          PGURI=postgres://"${AERIE_USERNAME}":"${AERIE_PASSWORD}"@localhost:5432/aerie_scheduler PGCMPOUTPUT=./pgdumpraw/AerieSchedulerRaw PGCLABEL=AerieSchedulerRaw PGBINDIR=/usr/bin ./pgcmp/pgcmp-dump
          PGURI=postgres://"${AERIE_USERNAME}":"${AERIE_PASSWORD}"@localhost:5432/aerie_sequencing PGCMPOUTPUT=./pgdumpraw/AerieSequencingRaw PGCLABEL=AerieSequencingRaw PGBINDIR=/usr/bin ./pgcmp/pgcmp-dump
          PGURI=postgres://"${AERIE_USERNAME}":"${AERIE_PASSWORD}"@localhost:5432/aerie_ui PGCMPOUTPUT=./pgdumpraw/AerieUIRaw PGCLABEL=AerieUIRaw PGBINDIR=/usr/bin ./pgcmp/pgcmp-dump
        shell: bash
      - name: Share Database Dump
        uses: actions/upload-artifact@v3
        with:
          name: raw-sql-db-dump
          path: pgdumpraw
          retention-days: 1
      - name: Migrate Latest to Base
        run: |
          cd deployment
          cat << EOF > .env
          AERIE_USERNAME=${AERIE_USERNAME}
          AERIE_PASSWORD=${AERIE_PASSWORD}
          EOF
          python -m pip install -r requirements.txt
          python aerie_db_migration.py --revert --all
          cd ..
        env:
          AERIE_USERNAME: "${{secrets.AERIE_USERNAME}}"
          AERIE_PASSWORD: "${{secrets.AERIE_PASSWORD}}"
      - name: Dump Databases
        env:
          AERIE_USERNAME: "${{secrets.AERIE_USERNAME}}"
          AERIE_PASSWORD: "${{secrets.AERIE_PASSWORD}}"
        run: |
          mkdir pgdumpmigrateddown
          PGURI=postgres://"${AERIE_USERNAME}":"${AERIE_PASSWORD}"@localhost:5432/aerie_merlin PGCMPOUTPUT=./pgdumpmigrateddown/AerieMerlinMigratedDown PGCLABEL=AerieMerlinMigratedDown PGBINDIR=/usr/bin ./pgcmp/pgcmp-dump
          PGURI=postgres://"${AERIE_USERNAME}":"${AERIE_PASSWORD}"@localhost:5432/aerie_scheduler PGCMPOUTPUT=./pgdumpmigrateddown/AerieSchedulerMigratedDown PGCLABEL=AerieSchedulerMigratedDown PGBINDIR=/usr/bin ./pgcmp/pgcmp-dump
          PGURI=postgres://"${AERIE_USERNAME}":"${AERIE_PASSWORD}"@localhost:5432/aerie_sequencing PGCMPOUTPUT=./pgdumpmigrateddown/AerieSequencingMigratedDown PGCLABEL=AerieSequencingMigratedDown PGBINDIR=/usr/bin ./pgcmp/pgcmp-dump
          PGURI=postgres://"${AERIE_USERNAME}":"${AERIE_PASSWORD}"@localhost:5432/aerie_ui PGCMPOUTPUT=./pgdumpmigrateddown/AerieUIMigratedDown PGCLABEL=AerieUIMigratedDown PGBINDIR=/usr/bin ./pgcmp/pgcmp-dump
        shell: bash
      - name: Share Database Dump
        uses: actions/upload-artifact@v3
        with:
          name: migrated-down-db-dump
          path: pgdumpmigrateddown
          retention-days: 1
      - name: Print Logs for Services
        run: docker compose logs -t
      - name: Stop Postgres and Hasura
        run: |
          docker ps -a
          docker compose down
          docker ps -a
      - name: Prune Volumes
        run: docker volume prune --force

  pgcmp-up:
    needs: [dump-db-base, dump-current]
    runs-on: ubuntu-latest
    steps:
      - name: Checkout Repo
        uses: actions/checkout@v3
      - name: Clone PGCMP
        uses: actions/checkout@v3
        with:
          repository: cbbrowne/pgcmp
          path: pgcmp
      - name: Setup Postgres Client (psql)
        run: |
          sudo apt-get update
          sudo apt-get install --yes postgresql-client
      - name: Start Postgres
        run: docker run -d -p 5432:5432 -e POSTGRES_HOST_AUTH_METHOD=trust --name=postgres postgres:14.1
      - name: Sleep for 5 Seconds
        run: sleep 5s
        shell: bash
      - name: Download Shared Dumps
        uses: actions/download-artifact@v3
        with:
          name: migrated-db-dump
          path: pgcmp/pgdumpmigrated
      - uses: actions/download-artifact@v3
        with:
          name: raw-sql-db-dump
          path: pgcmp/pgdumpraw
      - name: Compare Databases
        id: dbcmp
        run: |
          cp ./.github/scripts/compareDatabasesUp.sh pgcmp/compareDatabases.sh
          cd pgcmp
          ./compareDatabases.sh
        shell: bash
      - name: Upload Invalid
        if: ${{ failure() && steps.dbcmp.conclusion == 'failure' }}
        uses: actions/upload-artifact@v3
        with:
          name: pgcmpresultsup
          path: "**/results/"
      - name: Print Logs for Services
        if: always()
        run: docker logs -t postgres
      - name: Stop Postgres
        run: |
          docker ps -a
          docker stop postgres
          docker ps -a
      - name: Prune Volumes
        run: docker volume prune --force

  pgcmp-down:
    needs: [dump-db-base, dump-current]
    runs-on: ubuntu-latest
    steps:
      - name: Checkout Repo
        uses: actions/checkout@v3
      - name: Clone PGCMP
        uses: actions/checkout@v3
        with:
          repository: cbbrowne/pgcmp
          path: pgcmp
      - name: Setup Postgres Client (psql)
        run: |
          sudo apt-get update
          sudo apt-get install --yes postgresql-client
      - name: Start Postgres
        run: docker run -d -p 5432:5432 -e POSTGRES_HOST_AUTH_METHOD=trust --name=postgres postgres:14.1
      - name: Sleep for 5 Seconds
        run: sleep 5s
        shell: bash
      - name: Download Shared Dumps
        uses: actions/download-artifact@v3
        with:
          name: v1_0_1-db-dump
          path: pgcmp/pgdumpv1_0_1
      - uses: actions/download-artifact@v3
        with:
          name: migrated-down-db-dump
          path: pgcmp/pgdumpmigrateddown
      - name: Compare Databases
        id: dbcmp
        run: |
          cp ./.github/scripts/compareDatabasesDown.sh pgcmp/compareDatabases.sh
          cp ./.github/scripts/explanations pgcmp/explanations
          cat ./.github/scripts/explanations ./.github/scripts/explanations_merlin_down > pgcmp/explanations_merlin
          cd pgcmp
          ./compareDatabases.sh
        shell: bash
      - name: Upload Invalid
        if: ${{ failure() && steps.dbcmp.conclusion == 'failure' }}
        uses: actions/upload-artifact@v3
        with:
          name: pgcmpresultsdown
          path: "**/results/"
      - name: Print Logs for Services
        if: always()
        run: docker logs -t postgres
      - name: Stop Postgres
        run: |
          docker ps -a
          docker stop postgres
          docker ps -a
      - name: Prune Volumes
        run: docker volume prune --force