functions/Invoke-PSTGTestGenerator.ps1
|
function Invoke-PSTGTestGenerator { <# .SYNOPSIS Create the basic tests for the database project .DESCRIPTION The script will connect to a database on a SQL Server instance, iterate through objects and create tests for the objects. The script will create the following tests - Test if the database settings (i.e. collation) are correct - Test if an object (Function, Procedure, Table, View etc) exists - Test if an object (Function or Procedure) has the correct parameters - Test if an object (Table or View) has the correct columns Each object and each test will be it's own file. .PARAMETER SqlInstance The target SQL Server instance or instances. Server version must be SQL Server version 2012 or higher. This should be the primary replica. .PARAMETER SqlCredential Login to the target instance using alternative credentials. Accepts PowerShell credentials (Get-Credential). Windows Authentication, SQL Server Authentication, Active Directory - Password, and Active Directory - Integrated are all supported. For MFA support, please use Connect-DbaInstance. .PARAMETER Database The database or databases to add. .PARAMETER OutputPath Folder where the files should be written to .PARAMETER Creator The person that created the tests. By default the command will get the environment username .PARAMETER TemplateFolder The template folder containing all the templates for the tests. By default it will use the internal templates directory .PARAMETER Schema Filter the functions based on schema .PARAMETER Function Filter out specific functions that should only be processed .PARAMETER Procedure Filter out specific procedures that should only be processed .PARAMETER Table Filter out specific tables that should only be processed .PARAMETER Index Filter out specific indexes that should be processed .PARAMETER View Filter out specific views that should only be processed .PARAMETER SkipDatabaseTests Skip the database tests .PARAMETER SkipFunctionTests Skip the function tests .PARAMETER SkipProcedureTests Skip the procedure tests .PARAMETER SkipTableTests Skip the table tests .PARAMETER SkipViewTests Skip the view tests .PARAMETER SkipIndexTests Skip the view tests .PARAMETER TestClass Test class name to use for the test .PARAMETER WhatIf Shows what would happen if the command were to run. No actions are actually performed. .PARAMETER Confirm Prompts you for confirmation before executing any changing operations within the command. .PARAMETER EnableException By default, when something goes wrong we try to catch it, interpret it and give you a friendly warning message. This avoids overwhelming you with "sea of red" exceptions, but is inconvenient because it basically disables advanced scripting. Using this switch turns this "nice by default" feature off and enables you to catch exceptions with your own try/catch. .EXAMPLE PS C:\> Invoke-PSTGTestGenerator -SqlInstance SQLDB1 -Database DB1 -OutputPath c:\projects\DB1\DB1-Tests\TestBasic Iterate through all the objects and output the files to "c:\projects\DB1\DB1-Tests\TestBasic" .EXAMPLE PS C:\> Invoke-PSTGTestGenerator -SqlInstance SQLDB1 -Database DB1 -OutputPath c:\projects\DB1\DB1-Tests\TestBasic -Procedure Proc1, Proc2 Iterate through all the objects but only do "Proc1" and "Proc2" for the procedures. NOTE! All other tests like the table, function and view tests will still be generated .EXAMPLE PS C:\> Invoke-PSTGTestGenerator -SqlInstance SQLDB1 -Database DB1 -OutputPath c:\projects\DB1\DB1-Tests\TestBasic -SkipProcedureTests Iterate through all the objects but do not process the procedures #> [CmdletBinding()] param( [DbaInstanceParameter]$SqlInstance, [pscredential]$SqlCredential, [string]$Database, [string]$OutputPath, [string]$Creator, [string]$TemplateFolder, [string[]]$Schema, [string[]]$Function, [string[]]$Procedure, [string[]]$Table, [string[]]$Index, [string[]]$View, [switch]$SkipDatabaseTests, [switch]$SkipFunctionTests, [switch]$SkipProcedureTests, [switch]$SkipTableTests, [switch]$SkipIndexTests, [switch]$SkipViewTests, [string]$TestClass, [switch]$EnableException ) begin { # Check the parameters if (-not $SqlInstance) { Stop-PSFFunction -Message "Please enter a SQL Server instance" -Target $SqlInstance return } if (-not $Database) { Stop-PSFFunction -Message "Please enter a database" -Target $Database return } if (-not $OutputPath) { Stop-PSFFunction -Message "Please enter path to output the files to" -Target $OutputPath return } if (-not (Test-Path -Path $OutputPath)) { Stop-PSFFunction -Message "Could not access output path" -Category ResourceUnavailable -Target $OutputPath return } if (-not $Creator) { $Creator = $env:username } if (-not $TemplateFolder) { $TemplateFolder = Join-Path -Path ($script:ModuleRoot) -ChildPath "internal\templates" } if (-not (Test-Path -Path $TemplateFolder)) { Stop-PSFFunction -Message "Could not find template folder" -Target $OutputPath return } if (-not $TestClass) { $TestClass = "TestBasic" } # Connect to the server try { $server = Connect-DbaInstance -SqlInstance $Sqlinstance -SqlCredential $SqlCredential } catch { Stop-PSFFunction -Message "Could not connect to '$Sqlinstance'" -Target $Sqlinstance -ErrorRecord $_ -Category ConnectionError return } # Check if the database exists if ($Database -notin $server.Databases.Name) { Stop-PSFFunction -Message "Database cannot be found on '$SqlInstance'" -Target $Database } } process { if (Test-PSFFunctionInterrupt) { return } $db = $server.Databases[$Database] ######################################################################### # Create the database tests ######################################################################### $totalSteps = 7 $currentStep = 1 $task = "Creating Unit Tests" $progressParams = @{ Id = 1 Activity = "Creating tSQLt Unit Tests" Status = 'Progress->' PercentComplete = $null CurrentOperation = $task } if (-not $SkipDatabaseTests) { $progressParams.PercentComplete = $($currentStep / $totalSteps * 100) Write-Progress @progressParams try { # Create the collation test New-PSTGDatabaseCollationTest -SqlInstance $SqlInstance -SqlCredential $SqlCredential -Database $Database -Creator $Creator -TemplateFolder $TemplateFolder -OutputPath $OutputPath -TestClass $TestClass -EnableException } catch { Stop-PSFFunction -Message "Something went wrong creating the database collation test" -Target $Database -ErrorRecord $_ } } ######################################################################### # Create the function tests ######################################################################### $currentStep = 2 if (-not $SkipFunctionTests) { $progressParams.PercentComplete = $($currentStep / $totalSteps * 100) Write-Progress @progressParams $dbObjects = @() if ($Schema) { $dbObjects += $db.UserDefinedFunctions | Where-Object IsSystemObject -eq $false | Where-Object Schema -in $Schema } else { $dbObjects += $db.UserDefinedFunctions | Where-Object IsSystemObject -eq $false } if ($Function) { $dbObjects = $dbObjects | Where-Object Name -in $Function } # Create the function existence tests try { $params = @{ SqlInstance = $SqlInstance SqlCredential = $SqlCredential Database = $Database Schema = @($dbObjects | Select-Object Schema -ExpandProperty Schema -Unique) Object = @($dbObjects | Select-Object Name -ExpandProperty Name -Unique) Creator = $Creator OutputPath = $OutputPath TestClass = $TestClass EnableException = $EnableException } New-PSTGObjectExistenceTest @params } catch { Stop-PSFFunction -Message "Something went wrong creating the function existence tests" -Target $Database -ErrorRecord $_ } # Create the function parameter tests try { $params = @{ SqlInstance = $SqlInstance SqlCredential = $SqlCredential Database = $Database Schema = @($dbObjects | Select-Object Schema -ExpandProperty Schema -Unique) Function = @($dbObjects | Select-Object Name -ExpandProperty Name -Unique) Creator = $Creator OutputPath = $OutputPath TestClass = $TestClass EnableException = $EnableException } New-PSTGFunctionParameterTest @params } catch { Stop-PSFFunction -Message "Something went wrong creating the function parameter tests" -Target $Database -ErrorRecord $_ } } ######################################################################### # Create the procedure tests ######################################################################### $currentStep = 3 if (-not $SkipProcedureTests) { $progressParams.PercentComplete = $($currentStep / $totalSteps * 100) Write-Progress @progressParams $dbObjects = @() $dbObjects += Get-DbaModule -SqlInstance $SqlInstance -SqlCredential $SqlCredential -Database $Database -Type StoredProcedure -ExcludeSystemObjects | Select-Object SchemaName, Name if ($Schema) { $dbObjects = $dbObjects | Where-Object SchemaName -in $Schema } if ($Procedure) { $dbObjects = $dbObjects | Where-Object Name -in $Procedure } # Create the procedure existence tests try { $params = @{ SqlInstance = $SqlInstance SqlCredential = $SqlCredential Database = $Database Schema = @($dbObjects | Select-Object SchemaName -ExpandProperty SchemaName -Unique) Object = @($dbObjects | Select-Object Name -ExpandProperty Name -Unique) Creator = $Creator OutputPath = $OutputPath TestClass = $TestClass EnableException = $EnableException } New-PSTGObjectExistenceTest @params } catch { Stop-PSFFunction -Message "Something went wrong creating the procedure existence tests" -Target $Database -ErrorRecord $_ } # Create the procedure parameter tests try { $params = @{ SqlInstance = $SqlInstance SqlCredential = $SqlCredential Database = $Database Schema = @($dbObjects | Select-Object SchemaName -ExpandProperty SchemaName -Unique) Procedure = @($dbObjects | Select-Object Name -ExpandProperty Name -Unique) Creator = $Creator OutputPath = $OutputPath TestClass = $TestClass EnableException = $EnableException } New-PSTGProcedureParameterTest @params } catch { Stop-PSFFunction -Message "Something went wrong creating the procedure parameter tests" -Target $Database -ErrorRecord $_ } } ######################################################################### # Create the table tests ######################################################################### $currentStep = 4 if (-not $SkipTableTests) { $progressParams.PercentComplete = $($currentStep / $totalSteps * 100) Write-Progress @progressParams $dbObjects = @() if ($Schema) { $dbObjects += $db.Tables | Where-Object IsSystemObject -eq $false | Where-Object Schema -in $Schema } else { $dbObjects += $db.Tables | Where-Object IsSystemObject -eq $false } if ($Table) { $dbObjects = $dbObjects | Where-Object Name -in $Table } # Create the table existence tests try { $params = @{ SqlInstance = $SqlInstance SqlCredential = $SqlCredential Database = $Database Schema = @($dbObjects | Select-Object Schema -ExpandProperty Schema -Unique) Object = @($dbObjects | Select-Object Name -ExpandProperty Name -Unique) Creator = $Creator OutputPath = $OutputPath TestClass = $TestClass EnableException = $EnableException } New-PSTGObjectExistenceTest @params } catch { Stop-PSFFunction -Message "Something went wrong creating the table existence tests" -Target $Database -ErrorRecord $_ } # Create the table column tests try { $params = @{ SqlInstance = $SqlInstance SqlCredential = $SqlCredential Database = $Database Schema = @($dbObjects | Select-Object Schema -ExpandProperty Schema -Unique) Table = @($dbObjects | Select-Object Name -ExpandProperty Name -Unique) Creator = $Creator OutputPath = $OutputPath TestClass = $TestClass EnableException = $EnableException } New-PSTGTableColumnTest @params } catch { Stop-PSFFunction -Message "Something went wrong creating the table column tests" -Target $Database -ErrorRecord $_ } } ######################################################################### # Create the table index tests ######################################################################### $currentStep = 5 if (-not $SkipIndexTests) { $progressParams.PercentComplete = $($currentStep / $totalSteps * 100) Write-Progress @progressParams $dbObjects = @() if ($Schema) { $dbObjects += $db.Tables | Where-Object IsSystemObject -eq $false | Where-Object Schema -in $Schema } else { $dbObjects += $db.Tables | Where-Object IsSystemObject -eq $false } if ($Table) { $dbObjects = $dbObjects | Where-Object Name -in $Table } # Create the table index tests try { $params = @{ SqlInstance = $SqlInstance SqlCredential = $SqlCredential Database = $Database Schema = @($dbObjects | Select-Object Schema -ExpandProperty Schema -Unique) Table = @($dbObjects | Select-Object Name -ExpandProperty Name -Unique) Creator = $Creator OutputPath = $OutputPath TestClass = $TestClass EnableException = $EnableException } New-PSTGTableIndexTest @params } catch { Stop-PSFFunction -Message "Something went wrong creating the table index tests" -Target $Database -ErrorRecord $_ } } ######################################################################### # Create the index tests ######################################################################### $currentStep = 6 if (-not $SkipIndexTests) { $progressParams.PercentComplete = $($currentStep / $totalSteps * 100) Write-Progress @progressParams $dbObjects = @() if ($Schema) { $dbObjects += $db.Tables | Where-Object IsSystemObject -eq $false | Where-Object Schema -in $Schema } else { $dbObjects += $db.Tables | Where-Object IsSystemObject -eq $false } if ($Table) { $dbObjects = $dbObjects | Where-Object Name -in $Table } $indObjects = @() if ($Index) { $indObjects += $dbObjects.Indexes | Where-Object Name -in $Index | Select-Object Name } else { $indObjects += $dbObjects.Indexes | Select-Object Name } # Create the index existence tests try { $params = @{ SqlInstance = $SqlInstance SqlCredential = $SqlCredential Database = $Database Schema = @($dbObjects | Select-Object Schema -ExpandProperty Schema -Unique) Table = @($dbObjects | Select-Object Name -ExpandProperty Name -Unique) Index = @($indObjects | Select-Object Name -ExpandProperty Name -Unique) Creator = $Creator OutputPath = $OutputPath TestClass = $TestClass EnableException = $EnableException } New-PSTGIndexColumnTest @params } catch { Stop-PSFFunction -Message "Something went wrong creating the index column tests" -Target $Database -ErrorRecord $_ } } ######################################################################### # Create the view tests ######################################################################### $currentStep = 7 if (-not $SkipViewTests) { $progressParams.PercentComplete = $($currentStep / $totalSteps * 100) Write-Progress @progressParams $dbObjects = @() if ($Schema) { $dbObjects += $db.Views | Where-Object IsSystemObject -eq $false | Where-Object Schema -in $Schema } else { $dbObjects += $db.Views | Where-Object IsSystemObject -eq $false } if ($View) { $dbObjects = $dbObjects | Where-Object Name -in $View } # Create the view existence tests try { $params = @{ SqlInstance = $SqlInstance SqlCredential = $SqlCredential Database = $Database Schema = @($dbObjects | Select-Object Schema -ExpandProperty Schema -Unique) Object = @($dbObjects | Select-Object Name -ExpandProperty Name -Unique) Creator = $Creator OutputPath = $OutputPath TestClass = $TestClass EnableException = $EnableException } New-PSTGObjectExistenceTest @params } catch { Stop-PSFFunction -Message "Something went wrong creating the view existence tests" -Target $Database -ErrorRecord $_ } # Create the view column tests try { $params = @{ SqlInstance = $SqlInstance SqlCredential = $SqlCredential Database = $Database Schema = @($dbObjects | Select-Object Schema -ExpandProperty Schema -Unique) View = @($dbObjects | Select-Object Name -ExpandProperty Name -Unique) Creator = $Creator OutputPath = $OutputPath TestClass = $TestClass EnableException = $EnableException } New-PSTGViewColumnTest @params } catch { Stop-PSFFunction -Message "Something went wrong creating the view column tests" -Target $Database -ErrorRecord $_ } } } } |