build_and_test_on_spawn.yml

# Builds and tests a database project (in this case tSQLt) 
# 
# Originally from: https://github.com/lizbaron/db-cicd-with-github-actions/blob/9a514ed0c41029daa231db22e30de7806636160c/.github/workflows/build_and_test_on_linux.yml

name: Build and Test tSQLt on Spawn

on:
  push:
    branches:
      - main
  workflow_dispatch: 
    inputs:
      spawnDataImages:
        description: 'names of the data-images on spawn (e.g. all is currently equivalent to [["SQL2017","mssql-empty:v2017"],["SQL2019","mssql-empty:v2019"]] 2021-09-26)'
        required: true
        default: 'all'

env:
  MATRIX_NAME: 0
  MATRIX_SPAWNIMAGE: 1
  SPAWNDATAIMAGES_DEFAULT: 
    '[["SQL2017","mssql-empty:v2017"],["SQL2019","mssql-empty:v2019"]]'
  SQLPACKAGE_HOME: 'C:\Program Files\Microsoft SQL Server\160\DAC\bin\'

jobs:
  
  set-up-env-vars:
    name: set up matrix variables
    #if: ${{ always() }}

    # More types found here, https://docs.github.com/en/free-pro-team@latest/actions/reference/specifications-for-github-hosted-runners
    runs-on: windows-latest

    env:
      TSQLT_ARTIFACT_DIR: ${{ github.workspace }}\tSQLt\Build\output\tSQLt

    steps:

      - name: create variables
        id: create-vars
        shell: pwsh
        run: |
          $spawnDataImages = '${{ github.event.inputs.spawnDataImages }}';
          Write-Host "✨   ✨   ✨   ✨   ✨   ✨   ✨   ✨   ✨   ✨   ";
          Write-Host "";
          Write-Host ("input spawnDataImages: {0}" -f $spawnDataImages);
          if ([string]::IsNullOrEmpty($spawnDataImages) -or ("all" -eq $spawnDataImages)) {
            $spawnDataImages = '${{ env.spawndataimages_default }}';
          }
          Write-Host ("final spawnDataImages value: {0}" -f $spawnDataImages);
          $matrixValues = '{{"data-image":{0} }}' -f $spawnDataImages;
          Write-Host ("matrixValues value: {0}" -f $matrixValues);
          Write-Host "";
          Write-Host "✨   ✨   ✨   ✨   ✨   ✨   ✨   ✨   ✨   ✨   ";

          Write-Host ('::set-output name=matrixValues::' + $matrixValues);

    outputs:
      matrixValues: ${{ steps.create-vars.outputs.matrixValues }}


# 💖  💖  💖  💖  💖  💖  💖  💖  💖  💖  💖  💖  💖  💖   
  build-tSQLt:
    name: build tSQLt
    #if: ${{ false }}

    runs-on: windows-latest

    env:
      PFX_PATH: ${{ github.workspace }}\tSQLt\Build\tSQLtOfficialSigningKey.pfx
      ARTIFACT_DIR: ${{ github.workspace }}\artifacts
      MSBUILD_HOME: C:\Program Files (x86)\Microsoft Visual Studio\2019\Enterprise\MSBuild\Current\Bin

    steps:
      # Checks-out your repository under $GITHUB_WORKSPACE, so your job can access it. 
      # But this is misleading, because this variable is only really helpful as ${{ github.workspace }}
      - name: Checkout tSQLt
        uses: actions/checkout@v2
        with:
          path: tSQLt

      # documentation: This is just what you need to do...
      - name: create and install unofficial pfx file for signing dlls
        shell: pwsh
        run: |
          $superSecurePassword = "ExtraSuperSecurePassword1!";
          $cert = New-SelfSignedCertificate -FriendlyName "tSQLt_testKey" -Type CodeSigningCert -Subject "CN=localhost" -KeyAlgorithm RSA -HashAlgorithm SHA1 -KeyLength 2048 -SmimeCapabilities -CertStoreLocation "cert:/LocalMachine" -Provider "Microsoft Strong Cryptographic Provider"
          $pfxFile = Export-PfxCertificate -cert ("Cert:\LocalMachine\My\" + $cert.thumbprint) -FilePath $env:PFX_PATH -Password (ConvertTo-SecureString -String $superSecurePassword -Force -AsPlainText)
          &"${{ github.workspace }}\tSQLt\Build\SnInstallPfx" "$env:PFX_PATH" $superSecurePassword tSQLt_OfficialSigningKey

      - name: ant Build\tSQLt.buildCLR.xml --> Build/output/CLRBuild/
        run: ant -buildfile "${{ github.workspace }}\tSQLt\Build\tSQLt.buildCLR.xml" -D"msbuild.path"="$env:MSBUILD_HOME" all
 
      - name: ant Build/tSQLt.build.xml --> Build/output/tSQLtBuild
        run: ant -buildfile "${{ github.workspace }}\tSQLt\Build\tSQLt.build.xml" all -D"commit.id"="${{ github.sha }}"

      - name: copy tSQLt artifacts to ${{ env.artifact_dir }}
        shell: pwsh
        run: |
          New-Item -ItemType Directory -Path "$env:ARTIFACT_DIR"
          $basePath = "${{ github.workspace }}\tSQLt\Build\output\tSQLtBuild\";
          $artifactFiles = @("ReadMe.txt","CommitId.txt","CreateBuildLog.sql","GetFriendlySQLServerVersion.sql","tSQLt.tests.zip","tSQLtFacade.zip","tSQLtFiles.zip","tSQLtSnippets(SQLPrompt).zip","Version.txt");
          Get-ChildItem -Path ($basePath + "*")  -Include $artifactFiles | Copy-Item -Destination "$env:ARTIFACT_DIR";

      - name: Archive production artifact
        uses: actions/upload-artifact@v2
        with:
          if-no-files-found: error
          name: tSQLtBuildArtifact
          path: "${{ env.artifact_dir }}\\"
          retention-days: 5


# 💖  💖  💖  💖  💖  💖  💖  💖  💖  💖  💖  💖  💖  💖   
  build-facades:
    name: build facade dacpacs
    needs: [build-tSQLt,set-up-env-vars]
    #if: ${{ always() }}

    env:
      DACPAC_ARTIFACT_DIR: ${{ github.workspace }}\tSQLt\Build\output\DacpacBuild

    strategy:
      matrix: ${{ fromJson(needs.set-up-env-vars.outputs.matrixValues) }}
      fail-fast: true

    # More types found here, https://docs.github.com/en/free-pro-team@latest/actions/reference/specifications-for-github-hosted-runners
    runs-on: windows-latest

    steps:
      - name: Checkout tSQLt
        uses: actions/checkout@v2
        with:
          path: tSQLt

      - name: load namespace variables into env
        id: create-vars
        shell: pwsh
        run: |
          $sqlcmdPath = (Get-Command sqlcmd).Path.TrimEnd("\\SQLCMD.EXE");
          Write-Host ('::set-output name=sqlcmdPath::'+$sqlcmdPath);

      - name: Install spawnctl
        shell: pwsh
        run: |
          echo "Downloading and installing spawnctl..."
          Invoke-WebRequest -Uri "https://run.spawn.cc/spawnctl.exe" -UseBasicParsing -OutFile "${{ github.workspace }}\spawnctl.exe"
          & "${{ github.workspace }}\spawnctl.exe" version

      - name: Create Container
        id: create-container
        shell: pwsh
        run: |
          & "${{ github.workspace }}\spawnctl.exe" get data-images

          $dataImageName = "${{ matrix.data-image[ env.MATRIX_SPAWNIMAGE ] }}";

          echo "Creating 'tSQLtOnLinux' Spawn data container from image '$dataImageName'";
          $containerName= & "${{ github.workspace }}\spawnctl.exe" create data-container --image $dataImageName --lifetime 30m -q
          $containerJSON= & "${{ github.workspace }}\spawnctl.exe" get data-container $containerName -o json
          $containerInfo = ConvertFrom-JSON -inputObject $containerJSON;
          $containerFQDN=$containerInfo.host+", "+$containerInfo.port;
          $containerUser=$containerInfo.user;
          $containerPassword=$containerInfo.password;
          echo "Successfully created Spawn data container '$containerName'"

          Write-Host ('::set-output name=containerFQDN::'+$containerFQDN);
          Write-Host ('::set-output name=containerUser::'+$containerUser);
          Write-Host ('::set-output name=containerPassword::'+$containerPassword);

        env:
          SPAWNCTL_ACCESS_TOKEN: ${{ secrets.SPAWNCTL_ACCESS_TOKEN }} 

      - name: print sql version info
        shell: pwsh
        run: |
          Write-Host "✨   ✨   ✨   ✨   ✨   ✨   ✨   ✨   ✨   ✨   ";
          Write-Host "";
          # Write-Host Invoke-Sqlcmd -ConnectionTimeout 600 -Query "SELECT SUSER_NAME() U,SYSDATETIME() T,@@VERSION V;" -ServerInstance "${{ steps.create-container.outputs.containerFQDN }}" -Username "${{ steps.create-container.outputs.containerUser }}" -Password "${{ steps.create-container.outputs.containerPassword }}" -As DataSet
          $DS = Invoke-Sqlcmd -ConnectionTimeout 600 -Query "SELECT SUSER_NAME() U,SYSDATETIME() T,@@VERSION V;" -ServerInstance "${{ steps.create-container.outputs.containerFQDN }}" -Username "${{ steps.create-container.outputs.containerUser }}" -Password "${{ steps.create-container.outputs.containerPassword }}" -As DataSet

          $DS.Tables[0].Rows | %{ echo "{ $($_['U']), $($_['T']), $($_['V']) }" }    
          Write-Host "";
          Write-Host "✨   ✨   ✨   ✨   ✨   ✨   ✨   ✨   ✨   ✨   ";

      - name: download tSQLtBuildArtifact artifact
        uses: actions/download-artifact@v4.1.7
        with:
          name: tSQLtBuildArtifact
          path: "${{ github.workspace }}\\tSQLt\\Build\\output\\tSQLtBuild"

      - name: build tSQLt and Facade Dacpacs --> Build/output/DacpacBuild/
        shell: pwsh
        working-directory: ${{ github.workspace }}\tSQLt\
        run: |
          .\Build\SetupDacpacBuild.ps1 -ErrorAction Stop 
          .\Build\FacadeBuildDacpac.ps1 -ErrorAction Stop -ServerName "${{ steps.create-container.outputs.containerFQDN }}" -DatabaseName "tSQLt_dev" -Login "-U ${{ steps.create-container.outputs.containerUser }} -P ${{ steps.create-container.outputs.containerPassword }}" -SqlCmdPath "${{ steps.create-vars.outputs.sqlcmdPath }}" -SqlPackagePath "$env:SQLPACKAGE_HOME"
          .\Build\BuildtSQLtDacpac.ps1 -ErrorAction Stop -ServerName "${{ steps.create-container.outputs.containerFQDN }}" -DatabaseName "tSQLt_dev_dacpac_src" -Login "-U ${{ steps.create-container.outputs.containerUser }} -P ${{ steps.create-container.outputs.containerPassword }}" -SqlCmdPath "${{ steps.create-vars.outputs.sqlcmdPath }}" -SqlPackagePath "$env:SQLPACKAGE_HOME"

      - name: create dacpacs artifact
        uses: actions/upload-artifact@v2
        with:
          if-no-files-found: error
          name: dacpacs
          path: "${{ env.dacpac_artifact_dir }}\\"
          retention-days: 5


# 💖  💖  💖  💖  💖  💖  💖  💖  💖  💖  💖  💖  💖  💖   
  create-artifact:
    name: create artifact
    needs: [build-facades]
    #if: ${{ always() }}

    # More types found here, https://docs.github.com/en/free-pro-team@latest/actions/reference/specifications-for-github-hosted-runners
    runs-on: windows-latest

    env:
      TSQLT_PUBLIC_ARTIFACT_DIR: ${{ github.workspace }}\tSQLt\Build\output\tSQLt\public
      TSQLT_VALIDATION_ARTIFACT_DIR: ${{ github.workspace }}\tSQLt\Build\output\tSQLt\validation

    steps:
      - name: Checkout tSQLt
        uses: actions/checkout@v2
        with:
          path: tSQLt

      - name: download tSQLtBuildArtifact artifact
        uses: actions/download-artifact@v4.1.7
        with:
          name: tSQLtBuildArtifact
          path: "${{ github.workspace }}\\tSQLt\\Build\\output\\tSQLtBuild"

      - name: download tSQLt dacpac artifact(s)
        uses: actions/download-artifact@v4.1.7
        with:
          name: dacpacs
          path: "${{ github.workspace }}\\tSQLt\\Build\\output\\DacpacBuild"

      - name: inspect CreateArtifact directory
        shell: pwsh
        working-directory: ${{ github.workspace }}\tSQLt\Build\output
        run: |
          Get-ChildItem . -Recurse

      - name: package Build/output/tSQLt /public and /validation files
        shell: pwsh
        working-directory: ${{ github.workspace }}\tSQLt\
        run: |
          .\Build\BuildtSQLtZip.ps1 -ErrorAction Stop

      - name: create tSQLt public artifact
        uses: actions/upload-artifact@v2
        with:
          if-no-files-found: error
          name: tSQLtPublic
          path: "${{ env.tsqlt_public_artifact_dir }}\\"
          retention-days: 90

      - name: create tSQLt validation artifact
        uses: actions/upload-artifact@v2
        with:
          if-no-files-found: error
          name: tSQLtValidation
          path: "${{ env.tsqlt_validation_artifact_dir }}\\"
          retention-days: 5

# 💖  💖  💖  💖  💖  💖  💖  💖  💖  💖  💖  💖  💖  💖   
  test-tSQLt-and-facade:
    name: test build artifact
    needs: [create-artifact,set-up-env-vars]
    #if: ${{ always() }}

    env:
      TEST_ARTIFACT_DIR: ${{ github.workspace }}\tSQLt\Build\temp\Validate\TestResults
      DACPAC_ARTIFACT_DIR: ${{ github.workspace }}\tSQLt\Build\output\DacpacBuild
      MATRIX_VERSIONS: ${{ format('{{"data-image":{0} }}',github.event.inputs.spawnDataImages) }}

    strategy:
      matrix: ${{ fromJson(needs.set-up-env-vars.outputs.matrixValues) }}
      fail-fast: false

    # More types found here, https://docs.github.com/en/free-pro-team@latest/actions/reference/specifications-for-github-hosted-runners
    runs-on: windows-latest

    steps:
      - name: Checkout tSQLt
        uses: actions/checkout@v2
        with:
          path: tSQLt

      - name: download public tSQLt artifact
        uses: actions/download-artifact@v4.1.7
        with:
          name: tSQLtPublic
          path: "${{ github.workspace }}\\tSQLt\\Build\\output\\tSQLt\\public"

      - name: download validation tSQLt artifact
        uses: actions/download-artifact@v4.1.7
        with:
          name: tSQLtValidation
          path: "${{ github.workspace }}\\tSQLt\\Build\\output\\tSQLt\\validation"

      - name: load namespace variables into env
        id: create-vars
        shell: pwsh
        run: |
          $sqlcmdPath = (Get-Command sqlcmd).Path.TrimEnd("\\SQLCMD.EXE");
          Write-Host ('::set-output name=sqlcmdPath::'+$sqlcmdPath);

      - name: Install spawnctl
        shell: pwsh
        run: |
          echo "Downloading and installing spawnctl..."
          Invoke-WebRequest -Uri "https://run.spawn.cc/spawnctl.exe" -UseBasicParsing -OutFile "${{ github.workspace }}\spawnctl.exe"
          & "${{ github.workspace }}\spawnctl.exe" version

      - name: Create Container
        id: create-container
        shell: pwsh
        run: |
          & "${{ github.workspace }}\spawnctl.exe" get data-images

          $dataImageName = "${{ matrix.data-image[ env.MATRIX_SPAWNIMAGE] }}";

          echo "Creating 'tSQLtOnLinux' Spawn data container from image '$dataImageName'";
          $containerName= & "${{ github.workspace }}\spawnctl.exe" create data-container --image $dataImageName --lifetime 30m -q
          $containerJSON= & "${{ github.workspace }}\spawnctl.exe" get data-container $containerName -o json
          $containerInfo = ConvertFrom-JSON -inputObject $containerJSON;
          $containerFQDN=$containerInfo.host+", "+$containerInfo.port;
          $containerUser=$containerInfo.user;
          $containerPassword=$containerInfo.password;
          echo "Successfully created Spawn data container '$containerName'"

          Write-Host ('::set-output name=containerFQDN::'+$containerFQDN);
          Write-Host ('::set-output name=containerUser::'+$containerUser);
          Write-Host ('::set-output name=containerPassword::'+$containerPassword);

        env:
          SPAWNCTL_ACCESS_TOKEN: ${{ secrets.SPAWNCTL_ACCESS_TOKEN }} 

      - name: print sql version info
        shell: pwsh
        run: |
          Write-Host "✨   ✨   ✨   ✨   ✨   ✨   ✨   ✨   ✨   ✨   ";
          Write-Host "";
          # Write-Host Invoke-Sqlcmd -ConnectionTimeout 600 -Query "SELECT SUSER_NAME() U,SYSDATETIME() T,@@VERSION V;" -ServerInstance "${{ steps.create-container.outputs.containerFQDN }}" -Username "${{ steps.create-container.outputs.containerUser }}" -Password "${{ steps.create-container.outputs.containerPassword }}" -As DataSet
          $DS = Invoke-Sqlcmd -ConnectionTimeout 600 -Query "SELECT SUSER_NAME() U,SYSDATETIME() T,@@VERSION V;" -ServerInstance "${{ steps.create-container.outputs.containerFQDN }}" -Username "${{ steps.create-container.outputs.containerUser }}" -Password "${{ steps.create-container.outputs.containerPassword }}" -As DataSet

          $DS.Tables[0].Rows | %{ echo "{ $($_['U']), $($_['T']), $($_['V']) }" }    
          Write-Host "";
          Write-Host "✨   ✨   ✨   ✨   ✨   ✨   ✨   ✨   ✨   ✨   ";

      - name: Setup SQL Users
        shell: pwsh
        run: |
          $DS = Invoke-Sqlcmd -ConnectionTimeout 600 -InputFile "${{ github.workspace }}\tSQLt\Build\CreateLogin(tSQLt.Build).sql"  -ServerInstance "${{ steps.create-container.outputs.containerFQDN }}" -Username "${{ steps.create-container.outputs.containerUser }}" -Password "${{ steps.create-container.outputs.containerPassword }}"

      # @SM --> TODO: jUnit results
      - name: ant Build/tSQLt.validatebuild.xml
        working-directory: ${{ github.workspace }}\tSQLt\Build
        run: ant -buildfile tSQLt.validatebuild.xml all -D"db.server"="${{ steps.create-container.outputs.containerFQDN }}" -D"db.name"=tSQLt_dev -D"db.login"="-U ${{ steps.create-container.outputs.containerUser }} -P ${{ steps.create-container.outputs.containerPassword }}" -D"sqlcmd.path"="${{ steps.create-vars.outputs.sqlcmdPath }}"  -D"sqlpackage.path"="$env:SQLPACKAGE_HOME" -D"logtable.name"="tempdb.dbo.[Full Build Log]"

      - name: create junit artifact
        shell: pwsh
        run: |
          $ArtifactPath = "$env:TEST_ARTIFACT_DIR\";
          (Get-ChildItem $basePath -recurse -include "TestResults*.xml").FullName; 

      - name: archive junit test artifact
        uses: actions/upload-artifact@v2
        with:
          if-no-files-found: error
          name: jUnitTests-${{ matrix.data-image[ env.MATRIX_NAME ] }}
          path: "${{ env.test_artifact_dir }}\\"
          retention-days: 90


# # 💖  💖  💖  💖  💖  💖  💖  💖  💖  💖  💖  💖  💖  💖   
#   drop-data-container:
#     name: drop the data container
#     needs: test-tSQLt
#     if: ${{ always() }}
#     runs-on: ubuntu-latest
#     env:
#     steps:
#       - name: decode az sp cred
#         id: cred-decode
#         shell: pwsh
#         run: |
#           Write-Host ('#TODO');
#       # documentation: https://github.com/azure/login#configure-azure-credentials