redgate_build.yml

# For more info about this workflow, check out this great blog post:
# https://octopus.com/blog/database-deployments-with-github-actions-and-redgate
name: Redgate - Build and package SqlServerCentral NuGet and push to Octopus Deploy

on:
  push:
    branches: '*'
    paths:
    - 'Databases/SqlServerCentral/**'
    - '.github/workflows/redgate_build.yml'
  pull_request:
    branches: '*'
    paths:
    - 'Databases/SqlServerCentral/**'
    - '.github/workflows/redgate_build.yml'
  workflow_dispatch:     # Enables manual runs
  schedule:
    - cron:  '0 1 * * *' # runs at 1am every morning
                         # (useful for ensuring outside factors don't break the build)

env:  
  PACKAGES_FOLDER: Modules
  OUTPUT_FOLDER: PackagesOutput
  PACKAGE_NAME: SqlServerCentral
  OCTOPUS_PROJECT_NAME: my_sql_octopus_poc
  OCTOPUS_SPACE_NAME: Default
  ENVIRONMENT_NAME: Dev
  ACTIONS_ALLOW_UNSECURE_COMMANDS: true # Note: This is a bad idea and should be fixed.
                                        # For more information, see:
                                        # https://github.blog/changelog/2020-10-01-github-actions-deprecating-set-env-and-add-path-commands/

jobs:
  build:
    name: Build and Push Database

    runs-on: windows-latest

    steps:
    - uses: actions/checkout@v2

    - name: Validate GitHub secrets
      env:
        OCTOPUS_URL: ${{ secrets.OCTOPUS_URL }}
        OCTOPUS_APIKEY: ${{ secrets.OCTOPUS_APIKEY }}        
      run: |        
        .\.github\workflows\helper_scripts\validate_github_secrets.ps1 -OctoUrl "${env:OCTOPUS_URL}" -OctoApiKey "${env:OCTOPUS_APIKEY}"
      shell: powershell 
      
    - name: Set environment variables      
      run: |        
        $versionFromFile = Get-Content versionprefix.txt 
        Write-Host "Found $versionFromFile in versionprefix.md"
        $versionNumber = "$versionfromFile.${env:GITHUB_RUN_NUMBER}"
        Write-Host "Setting environment version number to: $versionNumber"

        echo "::set-env name=PACKAGE_VERSION::$versionNumber"        
      shell: powershell

    - name: Install Octopus Tooling
      run: choco install octopustools -y
      shell: powershell

    - name: Make Install Modules Folder
      run: New-Item "$PSScriptRoot\${env:PACKAGES_FOLDER}" -ItemType Directory -Force
      shell: powershell

    - name: Make Packages Output Folder
      run: New-Item "${env:OUTPUT_FOLDER}" -ItemType Directory
      shell: powershell

    - name: Install Redgate Tooling
      run: |
        $LocalModules = "$PSScriptRoot\${env:PACKAGES_FOLDER}"

        Get-PackageProvider NuGet -ForceBootstrap | Out-Null
        Import-PackageProvider PowerShellGet 
        Save-Module -Name PowerShellGet -Path $LocalModules -MinimumVersion 1.6 -Force -ErrorAction SilentlyContinue

        Save-Module -Name SqlChangeAutomation -Path $LocalModules -Force -ErrorAction Stop -AcceptLicense        
      shell: powershell    

    - name: Build Redgate Packages      
      run: |
        $LocalModules = "$PSScriptRoot\${env:PACKAGES_FOLDER}"
        $env:PSModulePath = "$LocalModules;$env:PSModulePath"

        Import-Module SqlChangeAutomation

        $project = ".\Databases\SqlServerCentral\state"
        $validatedProject = $project | Invoke-DatabaseBuild        

        $buildArtifact = New-DatabaseBuildArtifact $validatedProject -PackageId "${env:PACKAGE_NAME}" -PackageVersion "${env:PACKAGE_VERSION}"
        Export-DatabaseBuildArtifact $buildArtifact -Path "${env:OUTPUT_FOLDER}" 
      shell: powershell   

    - name: Handoff to Octopus Deploy
      env:
        OCTOPUS_URL: ${{ secrets.OCTOPUS_URL }}
        OCTOPUS_APIKEY: ${{ secrets.OCTOPUS_APIKEY }}       
      run: |        
        octo push --package="${env:OUTPUT_FOLDER}\${env:PACKAGE_NAME}.${env:PACKAGE_VERSION}.nupkg" --server="${env:OCTOPUS_URL}" --apiKey="${env:OCTOPUS_APIKEY}" --space="${env:OCTOPUS_SPACE_NAME}"

        # octo create-release --project="${env:OCTOPUS_PROJECT_NAME}" --packageVersion="${env:PACKAGE_VERSION}" --releaseNumber="${env:PACKAGE_VERSION}" --server="${env:OCTOPUS_URL}" --apiKey="${env:OCTOPUS_APIKEY}" --space="${env:OCTOPUS_SPACE_NAME}" --deployTo="${env:ENVIRONMENT_NAME}"

      shell: powershell