Build-Database-Run-tSQLt-Tests.yml

name: Build-Database-Run-tSQLt-Tests

on:
  push:
    branches: 
      - master
      - feature/*
      - cicd/*

    paths-ignore: 
      - '**/*.md'
      - '*.md'
      
jobs:
  create-build-deploy-test-tSQLcron:
    runs-on: ubuntu-latest

    # Use an Environment Variable to store the SQL SA_Password
    # this ENV variable can be referenced in the Various, step
    
    env:
      SA_PASSWORD: P@ssw0rd! # A secret is not necessary here as this is only a BuildTime SQL instance

    # Create a Service Named db running SQL Server Container running on 14333
    services:
      db: 
         image: mcr.microsoft.com/mssql/server
         env:
             SA_PASSWORD: ${{ env.SA_PASSWORD }}
             ACCEPT_EULA: Y
         ports:
             - '14333:1433'

    # Steps to Build/Deploy tsql code
    steps:

    - uses: actions/checkout@v2
      name: Checkout Source Code
    
    # Using aletasystems/tsqlrunner to Initialize the SQL Server 
    #  - Create a Database
    #  - Along with other scripts located in .\tsqlrunner.initscripts
    - shell: pwsh
      name: Initialize the SQL Database using aletasystems/tsqlrunner
      run: |
        # Get the DockerNetwork of the db service
        $dockerContainer = (docker ps -a --format '{{ json . }}' | ConvertFrom-Json | %{ $_  } | Where-Object -Property Image -EQ 'mcr.microsoft.com/mssql/server' | Select-Object networks).Networks

        # Attach and Execute aletasystems/tsqlrunner
        docker run --network=$dockerContainer --rm --name tsqlrunner -e SQLCMDSERVER=db -e SQLCMDUSER=sa -e SQLCMDPASSWORD=$env:SA_PASSWORD  -v  $env:GITHUB_WORKSPACE/tsqlrunner.initscripts:/tsqlscripts aletasystems/tsqlrunner /tooling/execute-sql-scripts.sh
    
    # Deploy SQL code using Flyway
    # - We attach the flyway container to the SQL Server Network
    # - Configure the SQL and Config Paths
    - shell: pwsh
      name: Build-Deploy tSQL using flyway/flyway 
      run: |
        # Get the DockerNetwork of the db service
        $dockerContainer = (docker ps -a --format '{{ json . }}' | ConvertFrom-Json | %{ $_  } | Where-Object -Property Image -EQ 'mcr.microsoft.com/mssql/server' | Select-Object networks).Networks

        # Execute Flyway Build/Deploy
        docker run --network=$dockerContainer --rm --name flyway -v  $env:GITHUB_WORKSPACE/flyway.tSQLcron/sql:/flyway/sql -v $env:GITHUB_WORKSPACE/flyway.tSQLcron/conf:/flyway/conf flyway/flyway -user=sa -password="$($env:SA_PASSWORD)" -url="jdbc:sqlserver://db;databaseName=tSQLcron" -connectRetries=60 migrate 

    # Install SQL Server PS Module
    # - Set PSGallery to Trusted so Install-Module runs fine
    - shell: pwsh
      name: Install and Import SqlServer PS1 Module
      run : |
        # We need to trust PSGallery or Install-Module will ask for Trust
        Get-PSRepository | Set-PSRepository -InstallationPolicy Trusted

        # Install SqlServer PS Module if it doesn't exist
        if (!(Get-Module -Name SqlServer -ListAvailable)) {
            $null = Install-Module -Name SqlServer -Scope CurrentUser -AllowClobber
        }

        # Import the Module
        $null = Import-Module -Name SqlServer

      # Execute the Utility Script that runs tSQLt
      # - Runs tsqlt.Runall but ignores failures
      # - Formats the result of the test cases to console and EXITS with the correct exit code.
    - shell: pwsh
      name: Execute tSQLt Tests
      run: | 
        .\Utility\invoke-tSQLt.ps1