build_and_test.yml

name: Build_And_Test

on:
  push:  # Triggered on every commit pushed to any branch
  pull_request:  # Triggered when a pull request is created or updated
  workflow_dispatch:  # Allows the workflow to be manually triggered from the GitHub UI
  create:  # Triggered when a new branch is created
  schedule:
    - cron: '0 * * * *'  # Runs once every hour at the beginning of the hour

env:
  SPAWNDATAIMAGE: "mssql-empty:v2019"
  TARGETDATABASE: "tSQLtVSCTemplate"
  # If this SQLPACKAGEPATH starts failing, use the "Find SqlPackage.exe" workflow to find the new path.
  SQLPACKAGEPATH: "C:\\Program Files\\Microsoft Visual Studio\\2022\\Enterprise\\Common7\\IDE\\Extensions\\Microsoft\\SQLDB\\DAC\\SqlPackage.exe"

  DACPACS_PATH: ./dacpac_output  
  DACPAC_FILES: 'Demo.dacpac, tSQLt.2019.dacpac, Tests.dacpac' #In deployment order


jobs:

  build:
    name: Build Database Project
    runs-on: windows-latest
    steps:
    - name: Checkout code
      uses: actions/checkout@v3

    - name: Setup MSBuild path
      uses: microsoft/setup-msbuild@v1.1

    - name: Build .sqlproj
      run: msbuild ./Tests/Tests.sqlproj /p:Configuration=Release /p:OutDir=../output/

    - name: Upload DACPAC as Artifact
      uses: actions/upload-artifact@v3
      with:
        name: dacpac
        path: ./output/*.dacpac

  checkartifact:
    name: Inspect Artifact
    needs: [build]
    runs-on: ubuntu-latest

    steps:
    - name: Checkout code
      uses: actions/checkout@v3

    - name: Download DACPAC Artifact
      uses: actions/download-artifact@v3
      with:
        name: dacpac
        path: ${{ env.DACPACS_PATH }}

    - name: List File In Artifact
      shell: pwsh
      run: |
        # Convert the comma-separated list of expected filenames into an array
        $expectedDacpacs = $env:DACPAC_FILES -split "," | ForEach-Object { $_.Trim() }
        
        # Get the list of actual .dacpac files from the directory
        $actualDacpacs = Get-ChildItem -Path "$env:DACPACS_PATH" -Filter "*.dacpac" | ForEach-Object { $_.Name }
        
        $results = @{}
        $nonMatchingFilesExist = $false
        
        foreach ($expected in $expectedDacpacs) {
          if ($actualDacpacs -contains $expected) {
            $results[$expected] = 'match'
          } else {
            $results[$expected] = 'missing'
            $nonMatchingFilesExist = $true
          }
        }
        foreach ($actual in $actualDacpacs) {
          if ($expectedDacpacs -notcontains $actual) {
            $results[$actual] = 'unexpected'
            $nonMatchingFilesExist = $true
          }
        }

        $results.GetEnumerator() | Format-Table -AutoSize | Out-String | Write-Host
        
        if ($nonMatchingFilesExist) {
          Write-Error "There are missing or unexpected .dacpac files. Failing the job."
          exit 1
        }

  create_ci_mssql_instance:
    name: Create CI MSSQL Instance
    runs-on: ubuntu-latest
    
    steps:
    - name: Create Data Container
      id: create-container
      uses: red-gate/create-spawn-data-container/@v1.1
      with:
        dataImage: ${{ env.SPAWNDATAIMAGE }}
        lifetime: '15m'
        useMasking: 'false'
      env:
        SPAWNCTL_ACCESS_TOKEN: ${{ secrets.SPAWNCTL_ACCESS_TOKEN }}

    - name: Create Container Variables
      id: create-container-vars
      shell: pwsh
      run: |
        $containerFQDN =$env:containerHost+", "+$env:containerPort;
        $containerUser = $env:containerUsername;
        $containerPassword = $env:containerPassword;

        Write-Host "✨   ✨   ✨   ✨   ✨   ✨   ✨   ✨   ✨   ✨   ";
        Write-Host "Container Name: "$env:containerName;
        Write-Host "Container FQDN: "$containerFQDN;
        Write-Host "Container Username: "$containerUser;
        Write-Host "Container Password: "$containerPassword;
        Write-Host "✨   ✨   ✨   ✨   ✨   ✨   ✨   ✨   ✨   ✨   ";

        echo "containerFQDN=$containerFQDN" | Out-File -Append -Encoding utf8 -FilePath $env:GITHUB_ENV
        echo "containerFQDN=$containerFQDN" | Out-File -Append -Encoding utf8 -FilePath $env:GITHUB_OUTPUT
      env:
        containerName: ${{ steps.create-container.outputs.dataContainerName }}
        containerHost: ${{ steps.create-container.outputs.dataContainerHost }}
        containerPort: ${{ steps.create-container.outputs.dataContainerPort }}
        containerUsername: ${{ steps.create-container.outputs.dataContainerUsername }}
        containerPassword: ${{ steps.create-container.outputs.dataContainerPassword }}
    outputs:
      containerFQDN: ${{ steps.create-container-vars.outputs.containerFQDN }}
      containerUser: ${{ steps.create-container.outputs.dataContainerUsername }}
      containerPassword: ${{ steps.create-container.outputs.dataContainerPassword }} 


  deploy:
    name: Deploy Database Project
    needs: [build, create_ci_mssql_instance]
    runs-on: windows-latest

    env:
      containerFQDN: ${{ needs.create_ci_mssql_instance.outputs.containerFQDN }}
      containerUser: ${{ needs.create_ci_mssql_instance.outputs.containerUser }}
      containerPassword: ${{ needs.create_ci_mssql_instance.outputs.containerPassword }}

    steps:
    - name: Checkout code
      uses: actions/checkout@v3

    - name: Download DACPAC Artifact
      uses: actions/download-artifact@v3
      with:
        name: dacpac
        path: ${{ env.DACPACS_PATH }}

    - name: Prepare SQL Server Instance
      shell: pwsh
      run: |

        Write-Host "✨   ✨   ✨   ✨   ✨   ✨   ✨   ✨   ✨   ✨   ";
        Write-Host "";

        $DS = Invoke-Sqlcmd -ConnectionTimeout 600 -Query "SELECT SUSER_NAME() U,SYSDATETIME() T,@@VERSION V;" -ServerInstance $env:containerFQDN -Username $env:containerUser -Password $env:containerPassword -As DataSet
        $DS.Tables[0].Rows | %{ echo "{ $($_['U']), $($_['T']), $($_['V']) }" };

        Write-Host "";
        Write-Host "✨   ✨   ✨   ✨   ✨   ✨   ✨   ✨   ✨   ✨   ";

        Invoke-Sqlcmd -InputFile "./tSQLt/PrepareServer.sql" -ServerInstance $env:containerFQDN -Username $env:containerUser -Password $env:containerPassword
        
    - name: Deploy DACPACs
      shell: pwsh
      run: |
        $dacpacFiles = $env:DACPAC_FILES -split "," | ForEach-Object { $_.Trim() }

        $createNewDatabase = $true
        foreach ($dacpacFile in $dacpacFiles) {
          $dacpacPath = Join-Path $env:DACPACS_PATH $dacpacFile
          $databaseName = $env:TARGETDATABASE

          # Call SqlPackage to deploy the .dacpac
  
          $arguments = @(
            '/Action:Publish',
            "/TargetServerName:`"$env:containerFQDN`"",
            "/TargetDatabaseName:`"$databaseName`"",
            "/TargetUser:`"$env:containerUser`"",
            "/TargetPassword:`"$env:containerPassword`"",
            "/SourceFile:`"$dacpacPath`"",
            "/p:CreateNewDatabase=$createNewDatabase"
          )
          & "$env:SQLPACKAGEPATH" $arguments

          # Check if SqlPackage was successful
          if ($LASTEXITCODE -ne 0) {
              Write-Error "Deployment failed for $dacpacFile in $databaseName"
              # Exit the script on failure
              exit $LASTEXITCODE
          } else {
              Write-Host "Deployment succeeded for $dacpacFile in $databaseName"
          }
          $createNewDatabase = $false
        }
        
  validate:
    name: Validate Database Solution
    needs: [deploy, create_ci_mssql_instance]
    runs-on: windows-latest

    env:
      containerFQDN: ${{ needs.create_ci_mssql_instance.outputs.containerFQDN }}
      containerUser: ${{ needs.create_ci_mssql_instance.outputs.containerUser }}
      containerPassword: ${{ needs.create_ci_mssql_instance.outputs.containerPassword }}

    steps:
    - name: Run Tests
      shell: pwsh
      run: |
        $query = "EXEC tSQLt.SetSummaryError @SummaryError=0;EXEC tSQLt.SetVerbose @Verbose = 1;EXEC tSQLt.RunAll;";

        Invoke-Sqlcmd -Query $query `
                      -ServerInstance $env:containerFQDN -Database $env:targetDatabase `
                      -Username $env:containerUser -Password $env:containerPassword `
                      -OutputSqlErrors $true -AbortOnError -verbose;

        $dataSet = Invoke-Sqlcmd -Query "EXEC tSQLt.XMLResultFormatter;" `
                            -ServerInstance $env:containerFQDN -Database $env:targetDatabase `
                            -Username $env:containerUser -Password $env:containerPassword `
                            -As DataSet -OutputSqlErrors $true -AbortOnError -verbose

        New-Item -Path "." -Name "TestResults" -ItemType "directory"
        $dataSet.Tables[0].Item(0)|Out-File "./TestResults/TestResults.xml";                

    - name: Upload Test Results Artifact
      uses: actions/upload-artifact@v3
      with:
        if-no-files-found: error
        name: TestResults
        path: ".\\TestResults\\"
        retention-days: 90

    - name: Check for Failures
      shell: pwsh
      run: |
        $query = "EXEC tSQLt.SetSummaryError @SummaryError=1;EXEC tSQLt.DefaultResultFormatter;";

        Invoke-Sqlcmd -Query $query `
                      -ServerInstance $env:containerFQDN -Database $env:targetDatabase `
                      -Username $env:containerUser -Password $env:containerPassword `
                      -OutputSqlErrors $true -AbortOnError -verbose;