TagLine – My goal – Chrissy will appreciate Unit Tests one day 
Chrissy has written about dbachecks the new up and coming community driven open source PowerShell module for SQL DBAs to validate their SQL Server estate. we have taken some of the ideas that we have presented about a way of using dbatools with Pester to validate that everything is how it should be and placed them into a meta data driven framework to make things easy for anyone to use. It is looking really good and I am really excited about it. It will be released very soon.
Chrissy and I will be doing a pre-con at SQLBits where we will talk in detail about how this works. You can find out more and sign up here
The rules were
The Describe title should be in double quotes
The Describe should use the plural Tags parameter
The Tags should be singular
The first Tag should be a unique tag in Get-DbcConfig
The context title should end with $psitem
The code should use Get-SqlInstance or Get-ComputerName
The Code should use the forEach method
The code should not use $_
The code should contain a Context block
She asked me if I could write the Pester Tests for it and this is how I did it. I needed to look at the Tags parameter for the Describe. It occurred to me that this was a job for the Abstract Syntax Tree (AST). I don’t know very much about the this but I sort of remembered reading a blog post by Francois-Xavier Cat about using it with Pester so I went and read that and found an answer on Stack Overflow as well. These looked just like what I needed so I made use of them. Thank you very much to Francois-Xavier and wOxxOm for sharing.
The first thing I did was to get the Pester Tests which we have located in a checks folder and loop through them and get the content of the file with the Raw parameter
Describe "Checking that each dbachecks Pester test is correctly formatted for Power Bi and Coded correctly" {
$Checks =(Get-ChildItem$ModuleBase\checks).Where{$_.Name-ne'HADR.Tests.ps1'}
$Checks.Foreach{
$Check =Get-Content$Psitem.FullName-Raw
Context "$($_.Name) - Checking Describes titles and tags" {$Describes = [Management.Automation.Language.Parser]::ParseInput($check, [ref]$tokens, [ref]$errors).
FindAll([Func[Management.Automation.Language.Ast, bool]] {
param($ast)
$ast.CommandElements -and
$ast.CommandElements[0].Value -eq 'describe'
}, $true) |
ForEach {
$CE = $_.CommandElements
$secondString = ($CE |Where { $_.StaticType.name -eq 'string' })[1]
$tagIdx = $CE.IndexOf(($CE |Where ParameterName -eq'Tags')) + 1
$tags = if ($tagIdx -and $tagIdx -lt $CE.Count) {
$CE[$tagIdx].Extent
}
New-Object PSCustomObject -Property @{
Name = $secondString
Tags = $tags
}
}
@($describes).Foreach{
$title = $PSItem.Name.ToString().Trim('"').Trim('''')
It "$title Should Use a double quote after the Describe" {
$PSItem.Name.ToString().Startswith('"')| Should be $true
$PSItem.Name.ToString().Endswith('"')| Should be $true
}
It "$title should use a plural for tags" {
$PsItem.Tags| Should Not BeNullOrEmpty
}
# a simple test for no esses apart from statistics and Access!!
if ($null -ne $PSItem.Tags) {
$PSItem.Tags.Text.Split(',').Trim().Where{($_ -ne '$filename') -and ($_ -notlike '*statistics*') -and ($_ -notlike '*BackupPathAccess*') }.ForEach{
It "$PsItem Should Be Singular" {
$_.ToString().Endswith('s')| Should Be $False
}
}
It "The first Tag Should Be in the unique Tags returned from Get-DbcCheck" {
$UniqueTags -contains $PSItem.Tags.Text.Split(',')[0].ToString()| Should Be $true
}
}
else {
It "You haven't used the Tags Parameter so we can't check the tags" {
$false| Should be $true
}
}
}The Describes variable is inside @() so that if there is only one the ForEach Method will still work. The unique tags are returned from our command Get-DbcCheck which shows all of the checks. We will have a unique tag for each test so that they can be run individually.
Yes, I have tried to ensure that the tags are singular by ensuring that they do not end with an s (apart from statistics) and so had to not check BackupPathAccess and statistics. Filename is a variable that we add to each Describe Tags so that we can run all of the tests in one file. I added a little if block to the Pester as well so that the error if the Tags parameter was not passed was more obvious
I did the same with the context blocks as well
Context "$($_.Name) - Checking Contexts" {
## Find the Contexts
$Contexts = [Management.Automation.Language.Parser]::ParseInput($check, [ref]$tokens, [ref]$errors).
FindAll([Func[Management.Automation.Language.Ast, bool]] {
param($ast)
$ast.CommandElements -and
$ast.CommandElements[0].Value -eq 'Context'
}, $true) |
ForEach {
$CE = $_.CommandElements
$secondString = ($CE |Where { $_.StaticType.name -eq 'string' })[1]
New-Object PSCustomObject -Property @{
Name = $secondString
}
}
@($Contexts).ForEach{
$title = $PSItem.Name.ToString().Trim('"').Trim('''')
It "$Title Should end with `$psitem So that the PowerBi will work correctly" {
$PSItem.Name.ToString().Endswith('psitem"')| Should Be $true
}
}
}Context "$($_.Name) - Checking Code" {
## This just grabs all the code
$AST = [System.Management.Automation.Language.Parser]::ParseInput($Check, [ref]$null, [ref]$null)
$Statements = $AST.EndBlock.statements.Extent
## Ignore the filename line
@($Statements.Where{$_.StartLineNumber -ne 1}).ForEach{
$title = [regex]::matches($PSItem.text, "Describe(.*)-Tag").groups[1].value.Replace('"', '').Replace('''', '').trim()
It "$title Should Use Get-SqlInstance or Get-ComputerName" {
($PSItem.text -Match 'Get-SqlInstance') -or ($psitem.text -match 'Get-ComputerName')| Should be $true
}
It "$title Should use the ForEach Method" {
($Psitem.text -match 'Get-SqlInstance\).ForEach{') -or ($Psitem.text -match 'Get-ComputerName\).ForEach{')| Should Be $true# use the \ to escape the )
}
It "$title Should not use `$_" {
($Psitem.text -match '$_')| Should Be $false
}
It "$title Should Contain a Context Block" {
$Psitem.text -match 'Context'| Should Be $True
}
}I trim the title from the Describe block so that it is easy to see where the failures (or passes) are with some regex and then loop through each statement apart from the first line to ensure that the code is using our internal commands Get-SQLInstance or Get-ComputerName to get information, that we are looping through each of those arrays using the ForEach method rather than ForEach-Object and using $psitem rather than $_ to reference the “This Item” in the array and that each Describe block has a context block.
This should ensure that any new tests that are added to the module follow the guidance we have set up on the Wiki and ensure that the Power Bi results still look beautiful!
Anyone can run the tests using
Invoke-Pester .\tests\Unit.Tests.ps1 -show Fails
If not they can see quickly that something needs to be fixed. (fail early )
