SQL Change Automation 3

This set of examples shows how to use SQLCover to enhance automated builds made using SQL Change Automation PowerShell cmdlets with SQL code coverage, generate a code coverage report and set a minimum acceptable code coverage threshold.

Sample SQLCover code coverage output

The following examples show how to use tSQLt to exercise your procedures and functions. However, any other testing framework, such as NUnit or Selenium, can be employed.

In this first example we'll use a database connection as the input object for the Invoke-DatabaseTests cmdlet. Later we'll see how this can be easily adapted when using a NuGet Database Package input.

1. Download SQLCover

Download SQLCover from here.
Unzip the contents to a folder. Both SQLCover.dll and Microsoft.SqlServer.TransactSql.ScriptDom.dll are required.
Right-click SQLCover.dll and select Properties and Unblock the dll

2. Wrap your test statement with SQLCover

Identify where in your existing PowerShell script your tests are executed. As we're using tSQLt, this should look something like:

$testResults = $connectionString | Invoke-DatabaseTests

Wrap code coverage statements around your test execution

# If running your PowerShell script from a build tool, pass in these variables as parameters
$server='yourServer\instance'
$database='yourDatabase'
 
Add-Type -Path .\SQLCover.dll
$connectionString = "server=$server;initial catalog=$database;integrated security=sspi"
$coverage = new-object SQLCover.CodeCoverage($connectionString, $database, $true, $false)

# This starts SQLCover. It uses XEvents to monitor activity on the database.
$coverage.Start()
 
# Between the Start and Stop, the tSQLt tests are run. 
# Any testing process that exercises the code (eg. NUnit, Selenium) can be used instead.
$testResults = $connectionString | Invoke-DatabaseTests 

# We stop SQLCover now that we've run our tests.
$coverageResults = $coverage.Stop()
 
# Generate a basic single-page code coverage report
$coverageResults.Html() | Out-File SQLCoverResults.html

Optional: using the OpenCover coverage reporting format

OpenCover is a code coverage reporting standard supported by some build tools. It provides improved visualization of your coverage results.

Generating Open Cover reports

# The folder name and the xml OpenCover report are specified here
$openCoverSource='OpenCoverSourceFiles'
$openCoverXmlFile='Coverage.opencover.xml'
  
# Create the folder for the SQL source code and OpenCover xml file
New-Item -Type Directory -Force -Path "$openCoverSource" | out-Null
 
$codeCoverageXmlPath = Join-Path -Path "$openCoverSource" -ChildPath $openCoverXmlFile
 
# The opencover report is written out here
$coverageResults.OpenCoverXml() | Out-File -force $codeCoverageXmlPath
 
# And the source code files...
$coverageResults.SaveSourceFiles($openCoverSource)

If your build tool doesn't natively support OpenCover reports, you can still benefit from its elegant output using an open source utility, ReportGenerator.exe.

This must be checked into version control, installed on the build agent, or downloaded with NuGet as demonstrated in the following example:

Using ReportGenerator to convert Open Cover xml to html

# This example uses $outputFolder and $openCoverXmlFile defined in the previous example

 name for the html report
$openCoverFolder='OpenCoverReport'

# Create the folder for the OpenCover HTML report
New-Item -Type Directory -Force -Path "$openCoverFolder" | out-Null
 
$PackagesDir = "packages" # The folder where the NuGet packages are stored
$nuget = "NuGet\nuget.exe" # Specify the location of nuget.exe
 
# Pull down ReportGenerator to the PackagesDir using NuGet
& $nuget install ReportGenerator -Version 2.4.4 -o $PackagesDir
 
# The ReportGenerator executable
$reportGeneratorExe = "$PackagesDir\ReportGenerator.2.4.4.0\tools\ReportGenerator.exe"
 
# Due to a known bug in ReportGenerator.exe, it must be invoked from the source files folder using -WorkingDirectory.
# (Otherwise ReportGenerator will fail to find its source files)
# Set the command line arguments for reportgenerator.exe to use, relative to the working directory
$report = "-reports:$openCoverXmlFile" # This is the opencover xml file, which was put in the source files folder
$targetDir = "-targetDir:..\$openCoverFolder" # This is the output folder for the html report and its supporting files
$args = $report, $targetDir
 
# Execute Report Generator with its arguments relative to the source files working directory
Start-Process -FilePath $reportGeneratorExe -ArgumentList $args -WorkingDirectory $openCoverSource -Wait

Using a NuGet database package input

If the input object to the Invoke-DatabaseTests cmdlet is a NuGet database package, a temporary test database must be specified. This is because Invoke-DatabaseTests will by default run its tests against a temporary, randomly-named database that it creates itself. This behavior is overridden using the -TemporaryDatabase option.

Using a Database Package input

# Specify a pre-created database for the sole purpose of running the tSQLt tests and SQL code coverage
$myTestDatabase = New-DatabaseConnection -ServerInstance $server -Database $database

# Invoke-DatabaseTests is called with the -TemporaryDatabase parameter to force this to be used as the test database
$testResults = $databasePackage | Invoke-DatabaseTests -TemporaryDatabase $myTestDatabase

Setting a minimum coverage threshold

To encourage developers to write tests while adding new functionality, it's possible to specify a baseline threshold of code coverage below which the build will fail.

This can be accomplished as follows:

Fail the build if coverage falls below a threshold

# Ideally, pass this value into your PowerShell from your build tool rather than hardcoding it here
# 60% is specified here as an example.
$minCoveragePercentageThreshold=60
 
# The coverage % is calculated and rounded to one decimal place
$statementCoverage=[decimal]($coverageResults.CoveredStatementCount/$coverageResults.StatementCount)*100
$statementCoverage=[System.Math]::Round($statementCoverage, 1)

# An exception is triggered to fail the build if the actual coverage is lower than the minimum threshold
if ($statementCoverage -lt $minCoveragePercentageThreshold)
{
  throw "SQL coverage is below the required threshold. Expected:$minCoveragePercentageThreshold%, Actual:$statementCoverage%"
}
else # All is okay!
{
  Write-Output "SQL coverage is above the threshold. Expected:$minCoveragePercentageThreshold%, Actual:$statementCoverage%"
}

Note: to fail a PowerShell script execution in TeamCity, set the build step to Format stderr output as error, and set the Failure Conditions to fail build if an error message is logged by build runner.