Merge pull request #199 from tSQLt-org/fix-tSQLt-CI-Nightly-Cleanup #316
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| # 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@v2 | |
| 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@v2 | |
| with: | |
| name: tSQLtBuildArtifact | |
| path: "${{ github.workspace }}\\tSQLt\\Build\\output\\tSQLtBuild" | |
| - name: download tSQLt dacpac artifact(s) | |
| uses: actions/download-artifact@v2 | |
| 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@v2 | |
| with: | |
| name: tSQLtPublic | |
| path: "${{ github.workspace }}\\tSQLt\\Build\\output\\tSQLt\\public" | |
| - name: download validation tSQLt artifact | |
| uses: actions/download-artifact@v2 | |
| 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 |