fwDispatchDeploy.yml

# This is the final workflow in a pipeline to run migration scripts on production and merge the PR on success

name: DEP Pipeline (Self-Hosted)
run-name: DEP ${{ github.event.pull_request.head.ref }} -> ${{ github.event.repository.name }}/${{ github.event.pull_request.base.ref }}

# Controls when the workflow will run
on:
  # Triggers the workflow on pull request review.  And then filtered later for approval.
  pull_request_review:
    types: [submitted]
    branches:
      - "[0-9]+-[a-zA-Z]+-[0-9]+-data-**"
      - "[0-9]+-[a-zA-Z]+-[0-9]+-refData-**"
      - "[0-9]+-[a-zA-Z]+-[0-9]+-schema-**"

env:
  # Enable this for additional debug logging
  ACTIONS_RUNNER_DEBUG: true

# Allow one concurrent deployment
concurrency:
  group: "fwDeploy_${{ github.event.pull_request.head.ref }}"
  cancel-in-progress: true

# A workflow run is made up of one or more jobs that can run sequentially or in parallel
jobs:
  prod:
    permissions:
      contents: write
      pull-requests: write
      actions: write

    name: Deploy Production On Approval
    # 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
    if: github.event.review.state == 'approved' #On approval
    env:
      stage: "Prod"
      # Used for environment specific migration scripts
      databaseName: ${{ secrets.databaseName}}
      JDBC: ${{ secrets.JDBC }}
      userName: ${{ secrets.userName }}
      password: ${{ secrets.password }}
      GITHUB_TOKEN: ${{ github.token }}

    # 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
        with:
          ref: ${{ github.event.pull_request.head.ref }}

      - name: Get issue JSON
        id: issue
        run: |
          $branchName = "${{ github.event.pull_request.head.ref }}"
          $issueNum = $branchName.Substring(0, $branchName.indexOf('-'))
          echo "number:$( $issueNum )"
          echo "number=$( $issueNum )" >> $env:GITHUB_OUTPUT
          $issueBodyKV = gh issue view $( $issueNum ) --json body | ConvertFrom-Json
          $bodyStart = $issueBodyKV.body.indexOf('```') + 3
          $bodyEnd = $issueBodyKV.body.lastIndexOf('```')
          $issueBodyValue = $issueBodyKV.body.Substring($bodyStart, $bodyEnd - 3)
          echo "issueBodyValue:$( $issueBodyValue | ConvertFrom-Json )"
          echo "bodyJson:$( $issueBodyValue )"
          echo "bodyJson=$( $issueBodyValue )" >> $env:GITHUB_OUTPUT

      - name: Get version information
        id: versions
        run: |
          $versionObj = '${{ steps.issue.outputs.bodyJson }}' | ConvertFrom-Json
          echo "versionObj:$( $versionObj )"
          echo "versionObj.newVersion:$( $versionObj.newVersion )"
          echo "currentVersion=$( $versionObj.currentVersion )" >> $env:GITHUB_OUTPUT
          echo "newVersion=$( $versionObj.newVersion )" >> $env:GITHUB_OUTPUT
          $filePath = "migrations/V$( $versionObj.newVersion )__${{ github.event.pull_request.head.ref }}.sql"
          echo "filePath:$( $filePath )"
          $content = Get-Content $filePath -Raw
          $expectedVersion = "$( $versionObj.expectedVersion ? $versionObj.expectedVersion : $versionObj.currentVersion.substring(1, $versionObj.currentVersion.length - 1) )"
          echo "expectedVersion:$( $expectedVersion )"
          $prepend = @"
            Declare @version varchar(25);
            SELECT @version= Coalesce(Json_Value(
              (SELECT Convert(NVARCHAR(3760), value) 
              FROM sys.extended_properties AS EP
              WHERE major_id = 0 AND minor_id = 0 
                AND name = 'Database_Info'), '$[0].Version'), 'that was not recorded');
            -- PARSENAME USED TO ONLY COMPARE MAJOR AND MINOR
            IF PARSENAME(@version, 3) + PARSENAME(@version, 2)  <> PARSENAME('$( $expectedVersion )', 3) + PARSENAME('$( $expectedVersion )', 2)
            BEGIN
            RAISERROR ('The Target was at version %s, not the correct version ($( $expectedVersion ))',16,1,@version)
            SET NOEXEC ON;
            END
          "@
          $postpend = @"
            PRINT N'Creating extended properties'
            SET NOEXEC off
            go
            USE AdvWorksComm
            DECLARE @DatabaseInfo NVARCHAR(3750), @version NVARCHAR(20)
            SET @version=N'$( $versionObj.newVersion )'
            PRINT N'New version === ' + @version
            SELECT @DatabaseInfo =
              (
              SELECT 'AdvWorksComm' AS "Name", @version  AS "Version",
              'The AdvWorksComm.' AS "Description",
                GetDate() AS "Modified",
            SUser_Name() AS "by"
              FOR JSON PATH
              );
            
            IF not EXISTS
              (SELECT name, value  FROM fn_listextendedproperty(
                N'Database_Info',default, default, default, default, default, default) )
                EXEC sys.sp_addextendedproperty @name=N'Database_Info', @value=@DatabaseInfo
            ELSE
              EXEC sys.sp_Updateextendedproperty  @name=N'Database_Info', @value=@DatabaseInfo
          "@
          $content = $prepend + "`r`n" + $content + "`r`n" + $postpend
          Set-Content $filePath $content

      - name: Comment Pull Request Pre Deploy
        run: |
          gh pr comment -R ${{ github.event.repository.full_name }} ${{ github.event.pull_request.head.ref }} --body "Starting Flyway migration of ${{ github.event.pull_request.head.ref }} to ${{ env.deployment_environment }}"

      # Runs the Flyway repair to update the checksum for above changes to pass validation
      - name: Repair Flyway history table
        run: |
          flyway -community -user="${{ env.userName }}" -password="${{ env.password }}" -baselineOnMigrate="true" -baselineVersion="${{ vars.BASELINE_VERSION }}" -configFiles="${{ GITHUB.WORKSPACE }}\flyway.conf" -locations="filesystem:${{ github.WORKSPACE }}\\migrations, filesystem:${{ github.WORKSPACE }}\\migrations-${{ env.deployment_environment }}" info repair info -url="${{ env.JDBC }}" -cleanDisabled='false'

      # Runs the Flyway Migrate against the Production database
      - name: Migrate Production DB
        run: |
          flyway -community -user="${{ env.userName }}" -password="${{ env.password }}" -baselineOnMigrate="true" -baselineVersion="${{ vars.BASELINE_VERSION }}" -configFiles="${{ GITHUB.WORKSPACE }}\flyway.conf" -locations="filesystem:${{ github.WORKSPACE }}\\migrations, filesystem:${{ github.WORKSPACE }}\\migrations-${{ env.deployment_environment }}" info migrate info -url="${{ env.JDBC }}" -cleanDisabled='false'

      # Trigger Log Workflow On Failure
      - name: Trigger Run Failed Log Comment On Pull Request
        if: ${{ failure() }}
        run: |
          gh workflow run commentLog.yml -r ${{ github.event.pull_request.head.ref }} -f run_id="${{ github.run_id }}" -f repo_name="${{ github.event.repository.full_name }}" -f branch_name="${{ github.event.pull_request.head.ref }}"

      - name: Comment Pull Request Post Deploy
        if: ${{ success() }}
        continue-on-error: true
        run: |
          git pull
          git commit -am 'Add version pre and post work to migration file'
          git tag "v${{ steps.versions.outputs.newVersion }}"
          git push origin "v${{ steps.versions.outputs.newVersion }}"
          gh pr comment -R ${{ github.event.repository.full_name }} ${{ github.event.pull_request.head.ref }} --body "${{ github.event.pull_request.head.ref }} Successfully Migrated to v${{ steps.versions.outputs.newVersion }}"