I have written a module SQLDiagAPI for consuming the SQL Server Diagnostics API with PowerShell. I blogged about how I used Pester to develop one of the functions . Whilst writing Get-SQLDiagFix I wrote some Pester Tests to make sure that the output from the code was as expected.

Pester

For those that don’t know. Pester is a PowerShell module for Test Driven Development

Pester provides a framework for running unit tests to execute and validate PowerShell commands from within PowerShell. Pester consists of a simple set of functions that expose a testing domain-specific language (DSL) for isolating, running, evaluating and reporting the results of PowerShell commands

If you have PowerShell version 5 then you will have Pester already installed although you should update it to the latest version. If not you can get Pester from the PowerShell Gallery follow the instructions on that page to install it. This is a good post to start learning about Pester

The Command Get-SQLDiagFix

Get-SQLDiagFix  returns the Product Name, Feature Name/Area, KB Number, Title and URL for the Fixes in the Cumulative Updates returned from the SQL Server Diagnostics Recommendations API. One Fix looks like this

This is how I wrote the Pester tests for that command

Mocking the results

In my describe block for each function I mock Get-SQLDiagRecommendations. This is the command that each of the current available commands in the module use to get the recommendations from the SQL Server Diagnostic Recommendations API. I did this by creating a json file from the API and saving it in a json folder inside the tests folder

I can then mock Get-SQLDiagRecommendations inside a BeforeAll code block using

Describe "Get-SQLDiagFix" -Tags Build , Unit, Fix {
BeforeAll {
    $Recommendations = (Get-Content $PSScriptRoot\json\recommendations.JSON) -join "`n" | ConvertFrom-Json
    Mock Get-SQLDiagRecommendations {$Recommendations}
}

Context "Output" {
BeforeAll {
    $Fixes = (Get-Content $PSScriptRoot\json\fix.JSON) -join "`n" | ConvertFrom-Json
    $Products = Get-SQLDiagProduct
    $Features = Get-SQLDiagFeature
}

The fixes.json is a file which was created from the recommendations.json and only contains the properties returned by GetSQLDiagFix which is what we are testing here. I can set variables for Products and Features using the commands from the module as these will call Get-SQLDiagRecommendations which we have already mocked.

Test All of the Fixes

I can now test that the code I have written for Get-SQLDiagFix returns the correct data without any parameters using this test with Compare-Object.

It "returns all of the fixes with no parameter" {
    Compare-Object (Get-SQLDiagFix) $Fixes | Should BeNullOrEmpty
}

Test Cases

There are the following products in the Recommendation API

• SQL Server 2012 SP3
• SQL Server 2016 SP1
• SQL Server 2016 RTM
• SQL Server 2014 SP1
• SQL Server 2014 SP2

$TestCases = @{ ProductName = 'SQL Server 2012 SP3'},
@{ ProductName = 'SQL Server 2016 SP1'},
@{ ProductName = 'SQL Server 2016 RTM'},
@{ ProductName ='SQL Server 2014 SP1'},
@{ ProductName = 'SQL Server 2014 SP2'}
It "Returns the correct results with a single product parameter <ProductName>" -TestCases $TestCases {
    param($productname)
    $results = $fixes.Where{$_.Product -in $ProductName}
    Compare-Object (Get-SQLDiagFix -Product $productname) $results | Should BeNullOrEmpty
}

The $TestCases variable holds an array of hashtables, one for each product with a Name that matches the parameter that I use in the test and a value of the product name.

I wrote one test, one It code block.  I refer to the product in the title inside <> using the same name as the name in the hashtable. The test (It) needs a parameter of -TestCases with a value (in this example) of the $TestCases variable we have just defined. It also needs a param block with a parameter that matches the Name value from the hashtables.

The expected test results are placed in a $results variable by filtering the $Fixes variable (defined in the BeforeAll code block above) by the parameter $Productname

The test will then run for each of the test cases in the $TestCases variable comparing the results of Get-SQLDiagFix -Product $Productname with the expected results from the $fixes variable

Here are the test results

Multiple Products in Test Cases

I also want to test that Get-SQLDiagFix will work for multiple Products. I need to create TestCases for those too. I do that in exactly the same way

$TestCases = @{ ProductName = 'SQL Server 2012 SP3', 'SQL Server 2016 SP1'},
@{ ProductName = 'SQL Server 2012 SP3', 'SQL Server 2016 SP1', 'SQL Server 2016 RTM'},
@{ ProductName = 'SQL Server 2012 SP3', 'SQL Server 2016 SP1', 'SQL Server 2016 RTM', 'SQL Server 2014 SP1'},
@{ ProductName = 'SQL Server 2012 SP3', 'SQL Server 2016 SP1', 'SQL Server 2016 RTM', 'SQL Server 2014 SP1', 'SQL Server 2014 SP2'}
    It "Returns the correct results with multiple product parameter <ProductName>" -TestCases $TestCases {
        param($productname)
        $results = $fixes.Where{$_.Product -in $ProductName}
        Compare-Object (Get-SQLDiagFix -Product $productname) $results | Should BeNullOrEmpty
}

Single Feature Dynamic Test Cases

Get-SQLDiagFix can also filter the fixes by feature area. The features are returned from Get-SQLDiagFeature. This means that I can create a test for each of the features by using the $features variable which was defined in the BeforeAll block as

$Features = Get-SQLDiagFeature

Then I can dynamically create test cases using

$TestCases = @()
$Features | Foreach-Object {$TestCases += @{Feature = $_}}
It "Returns the correct results with a single feature <Feature>" -TestCases $TestCases {
    param($Feature)
    $results = $fixes.Where{$_.Feature -in $Feature}
    Compare-Object (Get-SQLDiagFix -Feature $Feature) $results | Should BeNullOrEmpty
}

and the results look like

Random Dynamic Multiple Feature Test Cases

I also need to test that Get-SQLDiagFix returns the correct results for multiple features and whilst I could create those by hand like the products example above why not let PowerShell do that for me?

I created 10 test cases. Each one has a random number of features between 2 and the number of features.  I can then write one test to make use of those test cases. This is how I do that

## Generate 10 TestCases of a random number of Features
$TestCases = @()
$x = 10
While ($x -gt 0) {
    ## We are testing multiples so we need at least 2
    $Number = Get-Random -Maximum $Features.Count -Minimum 2
    $Test = @()
    While ($Number -gt 0) {
        $Test += Get-Random $Features
        $Number --
    }
## Need unique values
$Test = $test | Select-Object -Unique
$TestCases += @{Feature = $Test}
$X --
}
It "Returns the correct results with multiple features <Feature>" -TestCases $TestCases {
 param($Feature)
 $results = $fixes.Where{$_.Feature -in $Feature}
 Compare-Object (Get-SQLDiagFix -Feature $Feature) $results | Should BeNullOrEmpty
 }

Now there are 10 tests each with a random number of features and the results look like this. Each time the test is run it will use a different set of features for each of the 10 tests but I will know that I am testing that the code will return the correct results for multiple features

Two Sets of Test Cases?

It is also possible for Get-SQLDiagFix to have one or more products and one or more features passed as parameters, which obviously also need to be tested to ensure the code is returning the correct results. As Pester is just PowerShell we can use normal PowerShell code. This means that I can test for a single product and a single feature using a foreach loop and Test Cases like this

foreach ($Product in $Products) {
    $TestCases = @()
    $Features = Get-SQLDiagFeature -Product $Product
    $Features | Foreach-Object {$TestCases += @{Feature = $_}}
    It "Returns the correct results for a single product parameter $Product with a single feature <Feature>" -TestCases $TestCases {
        param($Feature)
        $results = $fixes.Where{$_.Product -eq $product -and $_.Feature -in $Feature}
        Compare-Object (Get-SQLDiagFix -Product $Product -Feature $Feature) $results | Should BeNullOrEmpty
    }
}

To test for a single product and multiple features I use this code

 foreach ($Product in $Products) {
    ## Generate 10 TestCases of a random number of Features
    $TestCases = @()
    $x = 10
    While ($x -gt 0) {
        ## We are testing multiples so we need at least 2
        $Number = Get-Random -Maximum $Features.Count -Minimum 2
        $Test = @()
        While ($Number -gt 0) {
            $Test += Get-Random $Features
            $Number --
        }
        ## Need unique values
        $Test = $test | Select-Object -Unique
        $TestCases += @{Feature = $Test}
        $X --
    }
    It "Returns the correct results for a single product parameter $Product with a multiple features <Feature>" -TestCases $TestCases {
        param($Feature)
        $Test = (Get-SQLDiagFix -Product $Product -Feature $Feature)
        ## If there are no results Compare-Object bombs out even though it is correct
        ## This is a risky fix for that
        if ($Test) {
        $results = $fixes.Where{$_.Product -eq $product -and $_.Feature -in $Feature}
        Compare-Object $test $results | Should BeNullOrEmpty
        }
    }
 } 

Because it is dynamically creating the values for the two parameters, I have to check that there are some results to test on line 23 as Compare-Object will throw an error if the object to be compared is empty. I need to do this because it is possible for the test to pick products and features in a combination that there are no fixes in the results.

The reason I have commented it as a risky fix is because if someone changes the code and Get-SQLDiagFix does not return any results then the test would not run and therefore there would be no information from this test that the code had a bug. However, in this suite of tests there are many tests that would fail in that scenario but be careful in your own usage.

I test for multiple products with a single feature and multiple products with multiple features like this

 $Products = @('SQL Server 2012 SP3', 'SQL Server 2016 SP1'),
 @('SQL Server 2012 SP3', 'SQL Server 2016 SP1', 'SQL Server 2016 RTM'),
 @('SQL Server 2012 SP3', 'SQL Server 2016 SP1', 'SQL Server 2016 RTM', 'SQL Server 2014 SP1'),
 @('SQL Server 2012 SP3', 'SQL Server 2016 SP1', 'SQL Server 2016 RTM', 'SQL Server 2014 SP1', 'SQL Server 2014 SP2')
 foreach ($Product in $Products) {
     $TestCases = @()
     $Features = Get-SQLDiagFeature -Product $Product
     $Features | Foreach-Object {$TestCases += @{Feature = $_}}
     It "Returns the correct results for multiple products parameter $Product with a single feature <Feature>" -TestCases $TestCases {
         param($Feature)
         $results = $fixes.Where{$_.Product -in $product -and $_.Feature -in $Feature}
         Compare-Object (Get-SQLDiagFix -Product $Product -Feature $Feature) $results | Should BeNullOrEmpty
     }
}
 $Products = @('SQL Server 2012 SP3', 'SQL Server 2016 SP1'),
 @('SQL Server 2012 SP3', 'SQL Server 2016 SP1', 'SQL Server 2016 RTM'),
 @('SQL Server 2012 SP3', 'SQL Server 2016 SP1', 'SQL Server 2016 RTM', 'SQL Server 2014 SP1'),
 @('SQL Server 2012 SP3', 'SQL Server 2016 SP1', 'SQL Server 2016 RTM', 'SQL Server 2014 SP1', 'SQL Server 2014 SP2')
 foreach ($Product in $Products) {
     ## Generate 10 TestCases of a random number of Features
     $TestCases = @()
     $x = 10
     While ($x -gt 0) {
         ## We are testing multiples so we need at least 2
         $Number = Get-Random -Maximum $Features.Count -Minimum 2
         $Test = @()
         While ($Number -gt 0) {
             $Test += Get-Random $Features
             $Number --
         }
     ## Need unique values
     $Test = $test | Select-Object -Unique
     $TestCases += @{Feature = $Test}
     $X --
 }
 It "Returns the correct results for multiple products parameter $Product with a multiple feature <Feature>" -TestCases $TestCases {
     param($Feature)
     $Test = (Get-SQLDiagFix -Product $Product -Feature $Feature)
     ## Annoyingly if there are no results Compare-Object bombs out even though it is correct
     ## This is a risky fix for that
     if ($Test) {
         $results = $fixes.Where{$_.Product -in $product -and $_.Feature -in $Feature}
         Compare-Object $test $results | Should BeNullOrEmpty
        }
    }
 } 

You can see all of the unit tests for the SQLDiagAPI module in my GitHub repository

The module is available on the PowerShell Gallery which means that you can install it using

Install-Module SQLDiagAPI