fwTaskPushCreateCheck.yml

# This is a workflow to check and create a pull request for db changes
 
name: Flyway Check Pipeline (Self-Hosted)
run-name: Check ${{ github.head_ref || github.ref_name }} migration to ${{ github.event.repository.name }}/${{ github.event.repository.default_branch }}

# Controls when the workflow will run
on:
  # Triggers the workflow on push or pull request events but only for branches beginning with 'task' that have changes in the migrations folder.
  push:
    branches:
      - 'task**'
    paths:
      - 'migrations/**'
      - 'migrations-build/**'
      - 'migrations-prod-check/**'
      - 'migrations-prod/**'
      
  # Allows you to run this workflow manually from the Actions tab
  workflow_dispatch:
 
env:
  FLYWAY_LICENSE_KEY: ${{ secrets.FLYWAY_LICENSE_KEY }} #Repository Secret - Create this within the Projects Settings > Secrets > Actions section
  # Enable this for additional debug logging
  ACTIONS_RUNNER_DEBUG: true
  ACTION_EMAIL: "MTPenguinAction@youremail.com"
  ACTION_USER: "MTPenguinAction"
  REPORT_PATH: ${{ github.WORKSPACE }}\reports\
  PUBLISH_PATH: https://mtpenguin.github.io/AdvWorksAuto/
  INFO_FILENAME: "${{ github.RUN_ID }}-Info"

permissions:
  contents: write
  pull-requests: write
  actions: write
  id-token: write

# Allow one concurrent deployment
concurrency:
  group: "${{ github.head_ref || github.ref_name }}_CHECK"
  cancel-in-progress: true

            
# A workflow run is made up of one or more jobs that can run sequentially or in parallel
jobs:
  info:
    name: Migration Info
    # The type of runner that the job will run on
    runs-on: self-hosted
    environment: 'prod' #Ensure this environment name is setup in the projects Settings>Environment area. Ensuring any reviewers are also configured
    outputs:
      pendingMigrations: ${{ steps.checkStates.outputs.pendingMigrations }}
    env:
      stage: 'Info'
      # Environment Secrets - Ensure all of the below have been created as an Environment Secret (Projects Settings > Secrets > Actions section, specially related to the environment in question) #
      databaseName: ${{ secrets.databaseName}} 
      JDBC: ${{ secrets.JDBC }} 
      userName: ${{ secrets.userName }}
      password: ${{ secrets.password }}
      # End of Environment Secrets #
      publishArtifacts: true
 
    # Steps represent a sequence of tasks that will be executed as part of the job
    steps:
      - name: Get/Set deployment environment
        run: |
          $deployments = curl ${{ github.event.repository.deployments_url }} | ConvertFrom-Json
          echo "deployment_environment=$( $deployments[0] | select -expand environment )" >> $env:GITHUB_ENV
          echo "${{ env.deployment_environment }}"
      
      # Checks-out your repository under $GITHUB_WORKSPACE, so your job can access it
      - uses: actions/checkout@v3
      
      # Runs the Flyway info command ([ ` ] "tick" character IS escape character)
      - name: Run Flyway info command
        id: checkStates
        run: |   # Format-Table >> ${{ env.REPORT_PATH }}${{ env.INFO_FILENAME }}.txt
          md -Force ${{ env.REPORT_PATH }}
          flyway -user="${{ env.userName }}" -password="${{ env.password }}" -licenseKey="${{ env.FLYWAY_LICENSE_KEY }}" -configFiles="${{ github.WORKSPACE }}\flyway.conf" -locations="filesystem:${{ github.WORKSPACE }}\\migrations, filesystem:${{ github.WORKSPACE }}\\migrations-${{ env.deployment_environment }}" info -url="${{ env.JDBC }}" -outputType=json > ${{ env.REPORT_PATH }}${{ env.INFO_FILENAME }}.json
          $infoObj = Get-Content -Raw -Path ${{ env.REPORT_PATH }}${{ env.INFO_FILENAME }}.json | ConvertFrom-Json
          cat ${{ env.REPORT_PATH }}${{ env.INFO_FILENAME }}.json
          echo "FLYWAY migration information:" > ${{ env.REPORT_PATH }}${{ env.INFO_FILENAME }}.txt
          echo `````` >> ${{ env.REPORT_PATH }}${{ env.INFO_FILENAME }}.txt
          $infoObj.migrations | Format-Table -AutoSize -Wrap version, description, state, filepath >> ${{ env.REPORT_PATH }}${{ env.INFO_FILENAME }}.txt 
          echo `````` >> ${{ env.REPORT_PATH }}${{ env.INFO_FILENAME }}.txt
          cat ${{ env.REPORT_PATH }}${{ env.INFO_FILENAME }}.txt
          $pending = @($infoObj.migrations | Where-Object -Property state -Match "^Pending")
          echo "$( $pending )"
          echo "$( $pending.count )"
          echo "pendingMigrations=$( $pending.count -ne 0)" > $env:GITHUB_OUTPUT
          
      - name: Add info files to repo
        run: |
          git config --global user.name "${{ env.ACTION_USER }}"
          git config --global user.email "${{ env.ACTION_EMAIL }}"
          git add ${{ env.REPORT_PATH }}${{ env.INFO_FILENAME }}.*
          
      - name: Echo pending migrations
        run: |
          echo "${{ steps.checkStates.outputs.pendingMigrations }}"
                   
      # Publish as an artifact
      - name: Publish migration information
        if: env.publishArtifacts == 'true'
        uses: actions/upload-artifact@v3
        with: 
          name: flyway-info
          path: |
            ${{ env.REPORT_PATH }}${{ env.INFO_FILENAME }}.txt
            ${{ env.REPORT_PATH }}${{ env.INFO_FILENAME }}.json
          
      - name: Commit info files and comment current migrations
        if: ${{ steps.checkStates.outputs.pendingMigrations == 'True' }}
        run: |
          git commit -am "Add migration info files"
          git push
                   
      - name: Commit info files and comment NOTHING to do!
        if: ${{ steps.checkStates.outputs.pendingMigrations == 'False' }}
        run: |
          git commit -am "No pending migrations found"
          git push
          echo "::error::NOTHING to do!"; exit 1
                   
        
  
  build:
    name: Deploy Build
    # The type of runner that the job will run on
    runs-on: self-hosted
    environment: 'build' #Ensure this environment name is setup in the projects Settings>Environment area. Ensuring any reviewers are also configured 
    needs: info
    if: ${{ needs.info.outputs.pendingMigrations == 'True' }}
    env:
      stage: 'Build'
      # Environment Secrets - Ensure all of the below have been created as an Environment Secret (Projects Settings > Secrets > Actions section, specially related to the environment in question) #
      databaseName: ${{ secrets.databaseName}} 
      JDBC: ${{ secrets.JDBC }} 
      userName: ${{ secrets.userName }}
      password: ${{ secrets.password }}
      # End of Environment Secrets #
      executeBuild: true
      publishArtifacts: true
      cleanDisabled: true
 
    # Steps represent a sequence of tasks that will be executed as part of the job
    steps:
      - name: Get/Set deployment environment
        run: |
          $deployments = curl ${{ github.event.repository.deployments_url }} | ConvertFrom-Json
          echo "deployment_environment=$( $deployments[0] | select -expand environment )" >> $env:GITHUB_ENV
          echo "${{ env.deployment_environment }}"
      # Checks-out your repository under $GITHUB_WORKSPACE, so your job can access it
      - uses: actions/checkout@v3
      
      # Runs the Flyway Clean command against the Build database
      - name: Clean build db
        if: env.executeBuild == 'true'
        run: |
          flyway -user="${{ env.userName }}" -password="${{ env.password }}" -baselineOnMigrate="true" -licenseKey="${{ env.FLYWAY_LICENSE_KEY }}" -configFiles="${{ github.WORKSPACE }}\flyway.conf" -locations="filesystem:${{ github.WORKSPACE }}\\migrations, filesystem:${{ github.WORKSPACE }}\\migrations-${{ env.deployment_environment }}" info clean info -url="${{ env.JDBC }}" -cleanDisabled='false'
        
      # Runs the Flyway Migrate command against the Build database
      - name: Migrate build db
        if: env.executeBuild == 'true'
        run: |
          flyway -user="${{ env.userName }}" -password="${{ env.password }}" -baselineOnMigrate="true" -licenseKey="${{ env.FLYWAY_LICENSE_KEY }}" -configFiles="${{ github.WORKSPACE }}\flyway.conf" -locations="filesystem:${{ github.WORKSPACE }}\\migrations, filesystem:${{ github.WORKSPACE }}\\migrations-${{ env.deployment_environment }}" info migrate info -url="${{ env.JDBC }}" -cleanDisabled='${{ env.cleanDisabled }}'
          
      # Runs the Flyway Undo command against the Build database
      # If the first undo script is U002, this will validate all undo scripts up to and including that
      - name: Undo build db
        if: env.executeBuild == 'true'
        run: |
          flyway -user="${{ env.userName }}" -password="${{ env.password }}" -baselineOnMigrate="true" -licenseKey="${{ env.FLYWAY_LICENSE_KEY }}" -configFiles="${{ github.WORKSPACE }}\flyway.conf" -locations="filesystem:${{ github.WORKSPACE }}\\migrations, filesystem:${{ github.WORKSPACE }}\\migrations-${{ env.deployment_environment }}" info undo info -url="${{ env.JDBC }}" -cleanDisabled='${{ env.cleanDisabled }}' -target="${{ vars.FIRST_UNDO_SCRIPT }}"
 
      # Create a directory to stage the artifact files
      - name: Stage files for publishing
        if: env.publishArtifacts == 'true'
        run: |
          cp -R ${{ github.WORKSPACE }}/migrations Artifact_Files/Migration/
          
      # Test for environment specific migrations
      - name: Set Env for environment specific files
        run: |
          echo "env_migrations=$(Test-Path ${{ github.WORKSPACE }}/migrations-${{ env.deployment_environment }} )" >> $env:GITHUB_ENV
      
      # Create a directory to stage the environment specific artifact files
      - name: Stage environment specific files for publishing
        if: env.publishArtifacts == 'true' && env.env_migrations == 'True'
        run: | # || : prevents errors on empty dir
          cp -R ${{ github.WORKSPACE }}/migrations-${{ env.deployment_environment }}/* Artifact_Files/Migration/ || : 
      
      # After migration scripts are validated, publish them as an artifact
      - name: Publish validated migration scripts
        if: env.publishArtifacts == 'true'
        uses: actions/upload-artifact@v3
        with: 
          name: flyway-build
          path: Artifact_Files/Migration/
 
  prod-preparation:
      name: Production deployment preparation - report creation
      # The type of runner that the job will run on
      runs-on: self-hosted
      environment: 'prod-check' #Ensure this environment name is setup in the projects Settings>Environment area. Ensuring any reviewers are also configured
      if: ${{ true }} #Set this variable to false to temporarily disable the job
      needs: build
      env:
        stage: 'Prod'
        branch_name: ${{ github.head_ref || github.ref_name }} 
        # Environment Secrets - Ensure all of the below have been created as an Environment Secret (Projects Settings > Secrets > Actions section, specially related to the environment in question) #
        databaseName: ${{ secrets.databaseName }}
        JDBC: ${{ secrets.JDBC }}
        userName: ${{ secrets.userName }}
        password: ${{ secrets.password }}
        check_JDBC: ${{ secrets.check_JDBC }} 
        check_userName: ${{ secrets.check_userName }}
        check_password: ${{ secrets.check_password }}  
        # End of Environment Secrets #
        generateDriftAndChangeReport: true
        failReleaseIfDriftDetected: false
        publishArtifacts: true
        GITHUB_TOKEN: ${{ github.token }}
        REPORT_FILE: ${{ github.RUN_ID }}-${{ secrets.databaseName }}-Check-Report.html
 
      # Steps represent a sequence of tasks that will be executed as part of the job
      steps:
        - name: Get/Set deployment environment
          run: |
            $deployments = curl ${{ github.event.repository.deployments_url }} | ConvertFrom-Json
            echo "deployment_environment=$( $deployments[0] | select -expand environment )" >> $env:GITHUB_ENV
            echo "${{ env.deployment_environment }}"
        # Checks-out your repository under $GITHUB_WORKSPACE, so your job can access it
        - uses: actions/checkout@v3
          
        # Runs the Flyway Check command, to produce a deployment report, against the production database
        - name: Create check reports
          if: env.generateDriftAndChangeReport == 'true'
          run: |
            flyway -user="${{ env.userName }}" -password="${{ env.password }}" -baselineOnMigrate="true" -licenseKey="${{ env.FLYWAY_LICENSE_KEY }}" -configFiles="${{ github.WORKSPACE }}\flyway.conf" -locations="filesystem:${{ github.WORKSPACE }}\migrations, filesystem:${{ github.WORKSPACE }}\migrations-${{ env.deployment_environment }}" check -dryrun -changes -drift "-check.failOnDrift=${{ env.failReleaseIfDriftDetected }}" "-check.buildUrl=${{ env.check_JDBC }}" "-check.buildUser=${{ env.check_userName }}" "-check.buildPassword=${{ env.check_password }}" -url="${{ env.JDBC }}" "-check.reportFilename=${{ env.REPORT_PATH }}${{ env.REPORT_FILE }}"
          continue-on-error: false
 
        - name: Publish check report
          if: env.publishArtifacts == 'true' && (${{ failure() }} || ${{ success() }})
          uses: actions/upload-artifact@v3
          with: 
            name: flyway-reports
            path: ${{ github.WORKSPACE }}\reports

        # Commit report to GitHub and put pages URL in commit message
        - name: Commit report
          if: ${{ failure() }} || ${{ success() }}
          run: |
            git config --global user.name "${{ env.ACTION_USER }}"
            git config --global user.email "${{ env.ACTION_EMAIL }}"
            git add ${{ env.REPORT_PATH }}
            git commit -am "Flyway check report URL:${{ env.PUBLISH_PATH }}${{ env.REPORT_FILE }}"
            git pull
            git push
        
        - name: Create ${{ env.branch_name }} pull request
          run: |
            gh pr create -B main -H "${{ env.branch_name }}" --title 'Merge ${{ env.branch_name }} into main' --body 'Created by Github action.'
            gh pr merge --auto --merge

        # Trigger workflow to publish report to GitHub pages and try to comment on P.R. (if exists)
        - name: Trigger check report to GitHub pages workflow
          if: ${{ failure() }} || ${{ success() }}
          run: |
            gh workflow run fwCheckReport.yml -r ${{ github.ref }} -f report_path="${{ env.PUBLISH_PATH }}" -f check_file="${{ env.REPORT_FILE }}" -f info_file="${{ env.INFO_FILENAME }}.txt"