PStSQLtTestGenerator.psm1
|
$script:ModuleRoot = $PSScriptRoot $script:ModuleVersion = (Import-PowerShellDataFile -Path "$($script:ModuleRoot)\PStSQLtTestGenerator.psd1").ModuleVersion # Detect whether at some level dotsourcing was enforced $script:doDotSource = Get-PSFConfigValue -FullName PStSQLtTestGenerator.Import.DoDotSource -Fallback $false if ($PStSQLtTestGenerator_dotsourcemodule) { $script:doDotSource = $true } <# Note on Resolve-Path: All paths are sent through Resolve-Path/Resolve-PSFPath in order to convert them to the correct path separator. This allows ignoring path separators throughout the import sequence, which could otherwise cause trouble depending on OS. Resolve-Path can only be used for paths that already exist, Resolve-PSFPath can accept that the last leaf my not exist. This is important when testing for paths. #> # Detect whether at some level loading individual module files, rather than the compiled module was enforced $importIndividualFiles = Get-PSFConfigValue -FullName PStSQLtTestGenerator.Import.IndividualFiles -Fallback $false if ($PStSQLtTestGenerator_importIndividualFiles) { $importIndividualFiles = $true } if (Test-Path (Resolve-PSFPath -Path "$($script:ModuleRoot)\..\.git" -SingleItem -NewChild)) { $importIndividualFiles = $true } if ("<was compiled>" -eq '<was not compiled>') { $importIndividualFiles = $true } function Import-ModuleFile { <# .SYNOPSIS Loads files into the module on module import. .DESCRIPTION This helper function is used during module initialization. It should always be dotsourced itself, in order to proper function. This provides a central location to react to files being imported, if later desired .PARAMETER Path The path to the file to load .EXAMPLE PS C:\> . Import-ModuleFile -File $function.FullName Imports the file stored in $function according to import policy #> [CmdletBinding()] Param ( [string] $Path ) $resolvedPath = $ExecutionContext.SessionState.Path.GetResolvedPSPathFromPSPath($Path).ProviderPath if ($doDotSource) { . $resolvedPath } else { $ExecutionContext.InvokeCommand.InvokeScript($false, ([scriptblock]::Create([io.file]::ReadAllText($resolvedPath))), $null, $null) } } #region Load individual files if ($importIndividualFiles) { # Execute Preimport actions . Import-ModuleFile -Path "$ModuleRoot\internal\scripts\preimport.ps1" # Import all internal functions foreach ($function in (Get-ChildItem "$ModuleRoot\internal\functions" -Filter "*.ps1" -Recurse -ErrorAction Ignore)) { . Import-ModuleFile -Path $function.FullName } # Import all public functions foreach ($function in (Get-ChildItem "$ModuleRoot\functions" -Filter "*.ps1" -Recurse -ErrorAction Ignore)) { . Import-ModuleFile -Path $function.FullName } # Execute Postimport actions . Import-ModuleFile -Path "$ModuleRoot\internal\scripts\postimport.ps1" # End it here, do not load compiled code below return } #endregion Load individual files #region Load compiled code 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 -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 $_ } } } } function New-PSTGDatabaseCollationTest { <# .SYNOPSIS Function to create a collation test .DESCRIPTION The function will lookup the current collation of the database and create a test with that value .PARAMETER SqlInstance The target SQL Server instance or instances. Server version must be SQL Server version 2012 or higher. .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 Path to output the test to .PARAMETER Creator The person that created the tests. By default the command will get the environment username .PARAMETER TemplateFolder Path to template folder. By default the internal templates folder will be used .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 New-PSTGDatabaseCollationTest -Database DB1 -OutputPath "C:\Projects\DB1\TestBasic\" Create a new database collation test #> [CmdletBinding(SupportsShouldProcess)] param( [DbaInstanceParameter]$SqlInstance, [pscredential]$SqlCredential, [string]$Database, [Parameter(Mandatory)][string]$OutputPath, [string]$Creator, [string]$TemplateFolder, [string]$TestClass, [switch]$EnableException ) begin { # Check 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 } # Test if the name of the test does not become too long if ($testName.Length -gt 128) { Stop-PSFFunction -Message "Name of the test is too long" -Target $testName } if (-not $TestClass) { $TestClass = "TestBasic" } $date = Get-Date -Format (Get-culture).DateTimeFormat.ShortDatePattern if (-not $Creator) { $Creator = $env:username } if (-not $TemplateFolder) { $TemplateFolder = Join-Path -Path ($script:ModuleRoot) -ChildPath "internal\templates" } if (-not (Test-Path -Path $TemplateFolder)) { try { $null = New-Item -Path $OutputPath -ItemType Directory } catch { Stop-PSFFunction -Message "Something went wrong creating the output directory" -Target $OutputPath -ErrorRecord $_ } } # 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 } $testName = "test If database has correct collation" $fileName = Join-Path -Path $OutputPath -ChildPath "$($testName).sql" # Import the template try { $script = Get-Content -Path (Join-Path -Path $TemplateFolder -ChildPath "DatabaseCollationTest.template") } catch { Stop-PSFFunction -Message "Could not import test template 'DatabaseCollationTest.template'" -Target $testName -ErrorRecord $_ } # Replace the markers with the content $script = $script.Replace("___TESTCLASS___", $TestClass) $script = $script.Replace("___TESTNAME___", $testName) $script = $script.Replace("___DATABASE___", $Database) $script = $script.Replace("___COLLATION___", $server.Databases[$Database].Collation) $script = $script.Replace("___CREATOR___", $creator) $script = $script.Replace("___DATE___", $date) # Write the test if ($PSCmdlet.ShouldProcess("$Database", "Writing Database Collation Test")) { try { Write-PSFMessage -Message "Creating collation test for '$Database'" $script | Out-File -FilePath $fileName [PSCustomObject]@{ TestName = $testName Category = "DatabaseCollation" Creator = $creator FileName = $fileName } } catch { Stop-PSFFunction -Message "Something went wrong writing the test" -Target $testName -ErrorRecord $_ } } } } function New-PSTGFunctionParameterTest { <# .SYNOPSIS Function to create parameter tests .DESCRIPTION The function will retrieve the current parameters for a function and create a test for it .PARAMETER SqlInstance The target SQL Server instance or instances. Server version must be SQL Server version 2012 or higher. .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 Schema Filter the functions based on schema .PARAMETER Function Function(s) to create tests for .PARAMETER OutputPath Path to output the test to .PARAMETER Creator The person that created the tests. By default the command will get the environment username .PARAMETER TemplateFolder Path to template folder. By default the internal templates folder will be used .PARAMETER TestClass Test class name to use for the test .PARAMETER InputObject Takes the parameters required from a Function object that has been piped into the command .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 New-PSTGFunctionParameterTest -Function $function -OutputPath $OutputPath Create a new function parameter test .EXAMPLE $functions | New-PSTGFunctionParameterTest -OutputPath $OutputPath Create the tests using pipelines #> [CmdletBinding(SupportsShouldProcess)] param( [DbaInstanceParameter]$SqlInstance, [pscredential]$SqlCredential, [string]$Database, [string[]]$Schema, [string[]]$Function, [string]$OutputPath, [string]$Creator, [string]$TemplateFolder, [string]$TestClass, [parameter(ParameterSetName = "InputObject", ValueFromPipeline)] [Microsoft.SqlServer.Management.Smo.UserDefinedFunction[]]$InputObject, [switch]$EnableException ) begin { # Check 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 } # Check the output path if (-not $OutputPath) { Stop-PSFFunction -Message "Please enter an output path" return } if (-not (Test-Path -Path $OutputPath)) { try { $null = New-Item -Path $OutputPath -ItemType Directory } catch { Stop-PSFFunction -Message "Something went wrong creating the output directory" -Target $OutputPath -ErrorRecord $_ } } # Check the template folder 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 } if (-not $TestClass) { $TestClass = "TestBasic" } $date = Get-Date -Format (Get-culture).DateTimeFormat.ShortDatePattern if (-not $Creator) { $Creator = $env:username } # 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 } $task = "Collecting objects" Write-Progress -ParentId 1 -Activity " Function Parameters" -Status 'Progress->' -CurrentOperation $task -Id 2 } process { if (Test-PSFFunctionInterrupt) { return } if (-not $InputObject -and -not $Function -and -not $SqlInstance) { Stop-PSFFunction -Message "You must pipe in an object or specify a Function" return } $objects = @() if ($InputObject) { $objects += $server.Databases[$Database].UserDefinedFunctions | Where-Object Name -in $InputObject | Select-Object Schema, Name, Parameters } else { $objects += $server.Databases[$Database].UserDefinedFunctions | Where-Object IsSystemObject -eq $false | Select-Object Schema, Name, Parameters } if ($Schema) { $objects = $objects | Where-Object Schema -in $Schema } if ($Function) { $objects = $objects | Where-Object Name -in $Function } $objectCount = $objects.Count $objectStep = 1 if ($objectCount -ge 1) { foreach ($functionObject in $objects) { $task = "Creating function test $($objectStep) of $($objectCount)" Write-Progress -ParentId 1 -Activity "Creating..." -Status 'Progress->' -PercentComplete ($objectStep / $objectCount * 100) -CurrentOperation $task -Id 2 $testName = "test If function $($functionObject.Schema).$($functionObject.Name) has the correct parameters" # Test if the name of the test does not become too long if ($testName.Length -gt 128) { Stop-PSFFunction -Message "Name of the test is too long" -Target $testName } $fileName = Join-Path -Path $OutputPath -ChildPath "$($testName).sql" # Get the parameters $query = "SELECT pm.name AS ParameterName, t.name AS DataType, pm.max_length AS MaxLength, pm.precision AS [Precision], pm.scale AS Scale FROM sys.parameters AS pm INNER JOIN sys.sql_modules AS sm ON sm.object_id = pm.object_id INNER JOIN sys.objects AS o ON sm.object_id = o.object_id INNER JOIN sys.schemas AS s ON s.schema_id = o.schema_id INNER JOIN sys.types AS t ON pm.system_type_id = t.system_type_id AND pm.user_type_id = t.user_type_id WHERE s.name = '$($functionObject.Schema)' AND o.name = '$($functionObject.Name)' AND pm.name <> '';" try { $parameters = @() $parameters += Invoke-DbaQuery -SqlInstance $SqlInstance -SqlCredential $SqlCredential -Database $Database -Query $query } catch { Stop-PSFFunction -Message "Could not retrieve parameters for [$($functionObject.Schema)].[$($functionObject.Name)]" -Target $functionObject -Continue } if ($parameters.Count -ge 1) { # Import the template try { $script = Get-Content -Path (Join-Path -Path $TemplateFolder -ChildPath "FunctionParameterTest.template") } catch { Stop-PSFFunction -Message "Could not import test template 'FunctionParameterTest.template'" -Target $testName -ErrorRecord $_ } $paramTextCollection = @() # Loop through the parameters foreach ($parameter in $parameters) { $paramText = "`t('$($parameter.ParameterName)', '$($parameter.DataType)', $($parameter.MaxLength), $($parameter.Precision), $($parameter.Scale))" $paramTextCollection += $paramText } # Replace the markers with the content $script = $script.Replace("___TESTCLASS___", $TestClass) $script = $script.Replace("___TESTNAME___", $testName) $script = $script.Replace("___SCHEMA___", $functionObject.Schema) $script = $script.Replace("___NAME___", $functionObject.Name) $script = $script.Replace("___CREATOR___", $creator) $script = $script.Replace("___DATE___", $date) $script = $script.Replace("___PARAMETERS___", ($paramTextCollection -join ",`n") + ";") # Write the test if ($PSCmdlet.ShouldProcess("$($functionObject.Schema).$($functionObject.Name)", "Writing Function Parameter Test")) { try { Write-PSFMessage -Message "Creating function parameter test for function '$($functionObject.Schema).$($functionObject.Name)'" $script | Out-File -FilePath $fileName [PSCustomObject]@{ TestName = $testName Category = "FunctionParameter" Creator = $creator FileName = $fileName } } catch { Stop-PSFFunction -Message "Something went wrong writing the test" -Target $testName -ErrorRecord $_ } } } else { Write-PSFMessage -Message "Function $($functionObject.Schema).$($functionObject.Name) does not have any parameters. Skipping..." } $functionStep++ } } } } function New-PSTGIndexColumnTest { <# .SYNOPSIS Function to test the columns for an index .DESCRIPTION The function will retrieve the current columns for an index and create a test for it .PARAMETER SqlInstance The target SQL Server instance or instances. Server version must be SQL Server version 2012 or higher. .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 Schema Filter the tables based on schema .PARAMETER Table Table(s) to create tests for .PARAMETER Index Index(es) to create tests for .PARAMETER OutputPath Path to output the test to .PARAMETER Creator The person that created the tests. By default the command will get the environment username .PARAMETER TemplateFolder Path to template folder. By default the internal templates folder will be used .PARAMETER TestClass Test class name to use for the test .PARAMETER InputObject Takes the parameters required from a Table object that has been piped into the command .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 New-PSTGIndexColumnTest -Table $table -OutputPath $OutputPath Create a new index column test .EXAMPLE $tables | New-PSTGIndexColumnTest -OutputPath $OutputPath Create the tests using pipelines #> [CmdletBinding(SupportsShouldProcess)] param( [DbaInstanceParameter]$SqlInstance, [pscredential]$SqlCredential, [string]$Database, [string[]]$Schema, [string[]]$Table, [string[]]$Index, [string]$OutputPath, [string]$Creator, [string]$TemplateFolder, [string]$TestClass, [parameter(ParameterSetName = "InputObject", ValueFromPipeline)] [Microsoft.SqlServer.Management.Smo.IndexedColumn[]]$InputObject, [switch]$EnableException ) begin { # Check 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 } # Check the output path if (-not $OutputPath) { Stop-PSFFunction -Message "Please enter an output path" return } if (-not (Test-Path -Path $OutputPath)) { try { $null = New-Item -Path $OutputPath -ItemType Directory } catch { Stop-PSFFunction -Message "Something went wrong creating the output directory" -Target $OutputPath -ErrorRecord $_ } } # Check the template folder 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 } if (-not $TestClass) { $TestClass = "TestBasic" } $date = Get-Date -Format (Get-culture).DateTimeFormat.ShortDatePattern if (-not $Creator) { $Creator = $env:username } # 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 '$Database' cannot be found on '$SqlInstance'" -Target $Database } $task = "Collecting objects" Write-Progress -ParentId 1 -Activity " Index Columns" -Status 'Progress->' -CurrentOperation $task -Id 2 $tables = @() if ($Schema) { $tables += $server.Databases[$Database].Tables | Where-Object { $_.IsSystemObject -eq $false -and $_.Schema -in $Schema } | Select-Object Schema, Name, Indexes } else { $tables += $server.Databases[$Database].Tables | Where-Object { $_.IsSystemObject -eq $false } | Select-Object Schema, Name, Indexes } if ($Table) { $tables = $tables | Where-Object Name -in $Table } } process { if (Test-PSFFunctionInterrupt) { return } if (-not $InputObject -and -not $Table -and -not $SqlInstance) { Stop-PSFFunction -Message "You must pipe in an object or specify a Table" return } $objects = @() if ($InputObject) { $objects += $tables.Indexes | Where-Object Name -in $InputObject | Select-Object Name, IndexedColumns } else { $objects += $tables.Indexes | Select-Object Name, IndexedColumns } if ($Index) { $objects = $objects | Where-Object Name -in $Index } $objectCount = $objects.Count $objectStep = 1 if ($objectCount -ge 1) { foreach ($indexObject in $objects) { $task = "Creating index column test $($objectStep) of $($objectCount)" Write-Progress -ParentId 1 -Activity "Creating..." -Status 'Progress->' -PercentComplete ($objectStep / $objectCount * 100) -CurrentOperation $task -Id 2 $testName = "test If index $($indexObject.Name) has the correct columns" # Test if the name of the test does not become too long if ($testName.Length -gt 128) { Stop-PSFFunction -Message "Name of the test is too long" -Target $testName } $fileName = Join-Path -Path $OutputPath -ChildPath "$($testName).sql" $date = Get-Date -Format (Get-culture).DateTimeFormat.ShortDatePattern $creator = $env:username # Import the template try { $script = Get-Content -Path (Join-Path -Path $TemplateFolder -ChildPath "IndexColumnTest.template") } catch { Stop-PSFFunction -Message "Could not import test template 'IndexColumnTest.template'" -Target $testName -ErrorRecord $_ } # Get the columns $query = "SELECT col.name AS ColumnName, st.name AS DataType, col.max_length AS MaxLength, col.precision AS [Precision], col.scale AS Scale FROM sys.indexes AS ind INNER JOIN sys.index_columns AS ic ON ind.object_id = ic.object_id AND ind.index_id = ic.index_id INNER JOIN sys.columns AS col ON ic.object_id = col.object_id AND ic.column_id = col.column_id INNER JOIN sys.tables AS t ON ind.object_id = t.object_id LEFT JOIN sys.types AS st ON st.user_type_id = col.user_type_id WHERE ind.name = '$($indexObject.Name)';" try { $columns = Invoke-DbaQuery -SqlInstance $SqlInstance -SqlCredential $SqlCredential -Database $Database -Query $query } catch { Stop-PSFFunction -Message "Could not retrieve columns for [$($indexObject.Schema)].[$($indexObject.Name)]" -Target $indexObject -Continue } $columnTextCollection = @() # Loop through the columns foreach ($column in $columns) { $columnText = "`t('$($column.ColumnName)', '$($column.DataType)', $($column.MaxLength), $($column.Precision), $($column.Scale))" $columnTextCollection += $columnText } # Replace the markers with the content $script = $script.Replace("___TESTCLASS___", $TestClass) $script = $script.Replace("___TESTNAME___", $testName) $script = $script.Replace("___NAME___", $indexObject.Name) $script = $script.Replace("___CREATOR___", $creator) $script = $script.Replace("___DATE___", $date) $script = $script.Replace("___COLUMNS___", ($columnTextCollection -join ",`n") + ";") # Write the test if ($PSCmdlet.ShouldProcess("$($indexObject.Schema).$($indexObject.Name)", "Writing Index Column Test")) { try { Write-PSFMessage -Message "Creating index column test for index '$($indexObject.Name)'" $script | Out-File -FilePath $fileName [PSCustomObject]@{ TestName = $testName Category = "IndexColumn" Creator = $creator FileName = $fileName } } catch { Stop-PSFFunction -Message "Something went wrong writing the test" -Target $testName -ErrorRecord $_ } } $objectStep++ } } } } function New-PSTGObjectExistenceTest { <# .SYNOPSIS Function to check if an object exists .DESCRIPTION The function will create a test to check for the existence of an object .PARAMETER SqlInstance The target SQL Server instance or instances. Server version must be SQL Server version 2012 or higher. .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 Schema Filter the objects based on schema .PARAMETER Object The object(s) to create the tests for .PARAMETER OutputPath Path to output the test to .PARAMETER Creator The person that created the tests. By default the command will get the environment username .PARAMETER TemplateFolder Path to template folder. By default the internal templates folder will be used .PARAMETER TestClass Test class name to use for the test .PARAMETER InputObject Takes the parameters required from a Login object that has been piped into the command .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 New-PSTGObjectExistenceTest -Object $object -OutputPath $OutputPath Create a new object existence test .EXAMPLE $objects | New-PSTGObjectExistenceTest -OutputPath $OutputPath Create the tests using pipelines #> [CmdletBinding(SupportsShouldProcess)] param( [DbaInstanceParameter]$SqlInstance, [pscredential]$SqlCredential, [string]$Database, [string[]]$Schema, [string[]]$Object, [string]$OutputPath, [string]$Creator, [string]$TemplateFolder, [string]$TestClass, [parameter(ParameterSetName = "InputObject", ValueFromPipeline)] [object[]]$InputObject, [switch]$EnableException ) begin { # Check 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 } # Check the output path if (-not $OutputPath) { Stop-PSFFunction -Message "Please enter an output path" return } if (-not (Test-Path -Path $OutputPath)) { try { $null = New-Item -Path $OutputPath -ItemType Directory } catch { Stop-PSFFunction -Message "Something went wrong creating the output directory" -Target $OutputPath -ErrorRecord $_ } } # Check the template folder 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 } $date = Get-Date -Format (Get-culture).DateTimeFormat.ShortDatePattern if (-not $Creator) { $Creator = $env:username } 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 } $task = "Collecting objects" Write-Progress -ParentId 1 -Activity " Object Existence" -Status 'Progress->' -CurrentOperation $task -Id 2 } process { if (Test-PSFFunctionInterrupt) { return } $objects = @() if ($InputObject) { # Get the extended stored procedures $objects += $server.Databases[$Database].ExtendedStoredProcedures | Where-Object { $_.Name -in $InputObject -and $_.IsSystemObject -eq $false } | Select-Object Schema, Name, @{Name = "ObjectType"; Expression = { "Extended Stored Procedure" } } # Get the sequences $objects += $server.Databases[$Database].Sequences | Where-Object Name -in $InputObject | Select-Object Schema, Name, @{Name = "ObjectType"; Expression = { "Sequence" } } # Get stored procedures $params = @{ SqlInstance = $SqlInstance SqlCredential = $SqlCredential Database = $Database Type = "StoredProcedure" ExcludeSystemObjects = $true } $objects += Get-DbaModule @params | Where-Object Name -in $InputObject | Select-Object @{Name = "Schema"; Expression = { $_.SchemaName } }, Name, @{Name = "ObjectType"; Expression = { "Stored Procedure" } } # Get tables $objects += $server.Databases[$Database].Tables | Where-Object Name -in $InputObject | Select-Object Schema, Name, @{Name = "ObjectType"; Expression = { "Table" } } # Get user defined data types $objects += $server.Databases[$Database].UserDefinedDataTypes | Where-Object { $_.Name -in $InputObject -and $_.IsSystemObject -eq $false } | Select-Object Schema, Name, @{Name = "ObjectType"; Expression = { "User Defined Data Type" } } # Get user defined functions $objects += $server.Databases[$Database].UserDefinedFunctions | Where-Object { $_.Name -in $InputObject -and $_.IsSystemObject -eq $false } | Select-Object Schema, Name, @{Name = "ObjectType"; Expression = { "User Defined Function" } } # Get user defined table types $objects += $server.Databases[$Database].UserDefinedTableTypes | Where-Object { $_.Name -in $InputObject -and $_.IsSystemObject -eq $false } | Select-Object Schema, Name, @{Name = "ObjectType"; Expression = { "User Defined Table Type" } } # Get views $params = @{ SqlInstance = $SqlInstance SqlCredential = $SqlCredential Database = $Database Type = "View" ExcludeSystemObjects = $true } $objects += Get-DbaModule @params | Where-Object Name -in $InputObject | Select-Object @{Name = "Schema"; Expression = { $_.SchemaName } }, Name, @{Name = "ObjectType"; Expression = { "View" } } } else { # Get the extended stored procedures $objects += $server.Databases[$Database].ExtendedStoredProcedures | Where-Object { $_.IsSystemObject -eq $false } | Select-Object Schema, Name, @{Name = "ObjectType"; Expression = { "Extended Stored Procedure" } } # Get sequences $objects += $server.Databases[$Database].Sequences | Select-Object Schema, Name, @{Name = "ObjectType"; Expression = { "Sequence" } } # Get stored procedures $params = @{ SqlInstance = $SqlInstance SqlCredential = $SqlCredential Database = $Database Type = "StoredProcedure" ExcludeSystemObjects = $true } $objects += Get-DbaModule @params | Select-Object @{Name = "Schema"; Expression = { $_.SchemaName } }, Name, @{Name = "ObjectType"; Expression = { "Stored Procedure" } } # Get tables $objects += $server.Databases[$Database].Tables | Select-Object Schema, Name, @{Name = "ObjectType"; Expression = { "Table" } } # Get user defined data types $objects += $server.Databases[$Database].UserDefinedDataTypes | Where-Object { $_.IsSystemObject -eq $false } | Select-Object Schema, Name, @{Name = "ObjectType"; Expression = { "User Defined Data Type" } } # Get user defined functions $objects += $server.Databases[$Database].UserDefinedFunctions | Where-Object { $_.IsSystemObject -eq $false } | Select-Object Schema, Name, @{Name = "ObjectType"; Expression = { "User Defined Function" } } # Get user defined table types $objects += $server.Databases[$Database].UserDefinedTableTypes | Where-Object { $_.IsSystemObject -eq $false } | Select-Object Schema, Name, @{Name = "ObjectType"; Expression = { "User Defined Table Type" } } # Get views $params = @{ SqlInstance = $SqlInstance SqlCredential = $SqlCredential Database = $Database Type = "View" ExcludeSystemObjects = $true } $objects += Get-DbaModule @params | Select-Object @{Name = "Schema"; Expression = { $_.SchemaName } }, Name, @{Name = "ObjectType"; Expression = { "View" } } } if ($Schema) { $objects = $objects | Where-Object Schema -in $Schema } if ($Object) { $objects = $objects | Where-Object Name -in $Object } $objectCount = $objects.Count $objectStep = 1 if ($objectCount -ge 1) { foreach ($obj in $objects) { $task = "Creating object existence test $($objectStep) of $($objectCount)" Write-Progress -ParentId 1 -Activity "Creating..." -Status 'Progress->' -PercentComplete ($objectStep / $objectCount * 100) -CurrentOperation $task -Id 2 if ($null -eq $obj.Schema) { $testName = "test If $(($obj.ObjectType).ToLower()) $($obj.Name) exists" } else { $testName = "test If $(($obj.ObjectType).ToLower()) $($obj.Schema)`.$($obj.Name) exists" } # Test if the name of the test does not become too long if ($testName.Length -gt 128) { Stop-PSFFunction -Message "Name of the test is too long" -Target $testName } $fileName = Join-Path -Path $OutputPath -ChildPath "$($testName).sql" # Import the template try { $script = Get-Content -Path (Join-Path -Path $TemplateFolder -ChildPath "ObjectExistence.template") } catch { Stop-PSFFunction -Message "Could not import test template 'ObjectExistence.template'" -Target $testName -ErrorRecord $_ } # Replace the markers with the content $script = $script.Replace("___TESTCLASS___", $TestClass) $script = $script.Replace("___TESTNAME___", $testName) $script = $script.Replace("___OBJECTTYPE___", $($obj.ObjectType).ToLower()) $script = $script.Replace("___SCHEMA___", $obj.Schema) $script = $script.Replace("___NAME___", $obj.Name) $script = $script.Replace("___CREATOR___", $creator) $script = $script.Replace("___DATE___", $date) # Write the test if ($PSCmdlet.ShouldProcess("$testName", "Writing Object Existence Test")) { try { Write-PSFMessage -Message "Creating existence test for $(($obj.ObjectType).ToLower()) '$($obj.Schema).$($obj.Name)'" -Level Verbose $script | Out-File -FilePath $fileName [PSCustomObject]@{ TestName = $testName Category = "ObjectExistence" Creator = $creator FileName = $fileName } } catch { Stop-PSFFunction -Message "Something went wrong writing the test" -Target $testName -ErrorRecord $_ } } $objectStep++ } } } } function New-PSTGProcedureParameterTest { <# .SYNOPSIS Function to create procedure tests .DESCRIPTION The function will collect the parameter(s) of the procedure(s) and create the test .PARAMETER SqlInstance The target SQL Server instance or instances. Server version must be SQL Server version 2012 or higher. .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 Schema Filter the stored procedures based on schema .PARAMETER Procedure Procedure(s) to create tests for .PARAMETER OutputPath Path to output the test to .PARAMETER Creator The person that created the tests. By default the command will get the environment username .PARAMETER TemplateFolder Path to template folder. By default the internal templates folder will be used .PARAMETER TestClass Test class name to use for the test .PARAMETER InputObject Takes the parameters required from a Procedure object that has been piped into the command .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 New-PSTGProcedureParameterTest -Procedure $procedure -OutputPath $OutputPath Create a new procedure parameter test .EXAMPLE $procedures | New-PSTGProcedureParameterTest -OutputPath $OutputPath Create the tests using pipelines #> [CmdletBinding(SupportsShouldProcess)] param( [DbaInstanceParameter]$SqlInstance, [pscredential]$SqlCredential, [string]$Database, [string[]]$Schema, [string[]]$Procedure, [string]$OutputPath, [string]$Creator, [string]$TemplateFolder, [string]$TestClass, [parameter(ParameterSetName = "InputObject", ValueFromPipeline)] [Microsoft.SqlServer.Management.Smo.StoredProcedure[]]$InputObject, [switch]$EnableException ) begin { # Check 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 } # Check the output path if (-not $OutputPath) { Stop-PSFFunction -Message "Please enter an output path" return } if (-not (Test-Path -Path $OutputPath)) { Stop-PSFFunction -Message "Could not access output path" -Category ResourceUnavailable -Target $OutputPath } # Check the template folder if (-not $TemplateFolder) { $TemplateFolder = Join-Path -Path ($script:ModuleRoot) -ChildPath "internal\templates" } if (-not (Test-Path -Path $TemplateFolder)) { try { $null = New-Item -Path $OutputPath -ItemType Directory } catch { Stop-PSFFunction -Message "Something went wrong creating the output directory" -Target $OutputPath -ErrorRecord $_ } } if (-not $TestClass) { $TestClass = "TestBasic" } $date = Get-Date -Format (Get-culture).DateTimeFormat.ShortDatePattern if (-not $Creator) { $Creator = $env:username } # 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 } $task = "Collecting objects" Write-Progress -ParentId 1 -Activity " Stored Procedure Parameters" -Status 'Progress->' -CurrentOperation $task -Id 2 } process { if (Test-PSFFunctionInterrupt) { return } if (-not $InputObject -and -not $Procedure -and -not $SqlInstance) { Stop-PSFFunction -Message "You must pipe in an object or specify a Procedure" return } $objects = @() if ($InputObject) { $objects += Get-DbaModule -SqlInstance $SqlInstance -Database $Database -Type StoredProcedure -ExcludeSystemObjects | Where-Object Name -in $InputObject | Select-Object SchemaName, Name } else { $objects += Get-DbaModule -SqlInstance $SqlInstance -Database $Database -Type StoredProcedure -ExcludeSystemObjects | Select-Object SchemaName, Name } if ($Schema) { $objects = $objects | Where-Object SchemaName -in $Schema } if ($Procedure) { $objects = $objects | Where-Object Name -in $Procedure } $objectCount = $objects.Count $objectStep = 1 if ($objectCount -ge 1) { foreach ($procedureObject in $objects) { $procedureObject = $server.Databases[$Database].StoredProcedures | Where-Object { $_.Schema -eq $procedureObject.SchemaName -and $_.Name -eq $procedureObject.Name } $task = "Creating procedure test $($objectStep) of $($objectCount)" Write-Progress -ParentId 1 -Activity "Creating..." -Status 'Progress->' -PercentComplete ($objectStep / $objectCount * 100) -CurrentOperation $task -Id 2 $testName = "test If stored procedure $($procedureObject.Schema).$($procedureObject.Name) has the correct parameters" # Test if the name of the test does not become too long if ($testName.Length -gt 128) { Stop-PSFFunction -Message "Name of the test is too long" -Target $testName } $fileName = Join-Path -Path $OutputPath -ChildPath "$($testName).sql" $date = Get-Date -Format (Get-culture).DateTimeFormat.ShortDatePattern $creator = $env:username # Get the parameters $query = "SELECT pm.name AS ParameterName, t.name AS DataType, pm.max_length AS MaxLength, pm.precision AS [Precision], pm.scale AS Scale FROM sys.parameters AS pm INNER JOIN sys.procedures AS ps ON pm.object_id = ps.object_id INNER JOIN sys.schemas AS s ON s.schema_id = ps.schema_id INNER JOIN sys.types AS t ON pm.system_type_id = t.system_type_id AND pm.user_type_id = t.user_type_id WHERE s.name = '$($procedureObject.Schema)' AND ps.name = '$($procedureObject.Name)';" try { $parameters = @() $parameters += Invoke-DbaQuery -SqlInstance $SqlInstance -SqlCredential $SqlCredential -Database $Database -Query $query } catch { Stop-PSFFunction -Message "Could not retrieve parameters for [$($procedureObject.Schema)].[$($procedureObject.Name)]" -Target $procedureObject -Continue } if ($parameters.Count -ge 1) { # Import the template try { $script = Get-Content -Path (Join-Path -Path $TemplateFolder -ChildPath "ProcedureParameterTest.template") } catch { Stop-PSFFunction -Message "Could not import test template 'ProcedureParameterTest.template'" -Target $testName -ErrorRecord $_ } $paramTextCollection = @() # Loop through the parameters foreach ($parameter in $parameters) { $paramText = "`t('$($parameter.ParameterName)', '$($parameter.DataType)', $($parameter.MaxLength), $($parameter.Precision), $($parameter.Scale))" $paramTextCollection += $paramText } # Replace the markers with the content $script = $script.Replace("___TESTCLASS___", $TestClass) $script = $script.Replace("___TESTNAME___", $testName) $script = $script.Replace("___SCHEMA___", $procedureObject.Schema) $script = $script.Replace("___NAME___", $procedureObject.Name) $script = $script.Replace("___CREATOR___", $creator) $script = $script.Replace("___DATE___", $date) $script = $script.Replace("___PARAMETERS___", ($paramTextCollection -join ",`n") + ";") # Write the test if ($PSCmdlet.ShouldProcess("$($procedureObject.Schema).$($procedureObject.Name)", "Writing Procedure Parameter Test")) { try { Write-PSFMessage -Message "Creating procedure parameter test for procedure '$($procedureObject.Schema).$($procedureObject.Name)'" $script | Out-File -FilePath $fileName [PSCustomObject]@{ TestName = $testName Category = "ProcedureParameter" Creator = $creator FileName = $fileName } } catch { Stop-PSFFunction -Message "Something went wrong writing the test" -Target $testName -ErrorRecord $_ } } } else { Write-PSFMessage -Message "Procedure $($procedureObject.Schema).$($procedureObject.Name) does not have any parameters. Skipping..." } $objectStep++ } } } } function New-PSTGTableColumnTest { <# .SYNOPSIS Function to test thee columns for a table .DESCRIPTION The function will retrieve the current columns for a table and create a test for it .PARAMETER SqlInstance The target SQL Server instance or instances. Server version must be SQL Server version 2012 or higher. .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 Schema Filter the tables based on schema .PARAMETER Table Table(s) to create tests for .PARAMETER OutputPath Path to output the test to .PARAMETER Creator The person that created the tests. By default the command will get the environment username .PARAMETER TemplateFolder Path to template folder. By default the internal templates folder will be used .PARAMETER TestClass Test class name to use for the test .PARAMETER InputObject Takes the parameters required from a Table object that has been piped into the command .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 New-PSTGTableColumnTest -Table $table -OutputPath $OutputPath Create a new table column test .EXAMPLE $tables | New-PSTGTableColumnTest -OutputPath $OutputPath Create the tests using pipelines #> [CmdletBinding(SupportsShouldProcess)] param( [DbaInstanceParameter]$SqlInstance, [pscredential]$SqlCredential, [string]$Database, [string[]]$Schema, [string[]]$Table, [string]$OutputPath, [string]$Creator, [string]$TemplateFolder, [string]$TestClass, [parameter(ParameterSetName = "InputObject", ValueFromPipeline)] [Microsoft.SqlServer.Management.Smo.Table[]]$InputObject, [switch]$EnableException ) begin { # Check 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 } # Check the output path if (-not $OutputPath) { Stop-PSFFunction -Message "Please enter an output path" return } if (-not (Test-Path -Path $OutputPath)) { try { $null = New-Item -Path $OutputPath -ItemType Directory } catch { Stop-PSFFunction -Message "Something went wrong creating the output directory" -Target $OutputPath -ErrorRecord $_ } } # Check the template folder 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 } if (-not $TestClass) { $TestClass = "TestBasic" } $date = Get-Date -Format (Get-culture).DateTimeFormat.ShortDatePattern if (-not $Creator) { $Creator = $env:username } # 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 '$Database' cannot be found on '$SqlInstance'" -Target $Database } $task = "Collecting objects" Write-Progress -ParentId 1 -Activity " Table Columns" -Status 'Progress->' -CurrentOperation $task -Id 2 } process { if (Test-PSFFunctionInterrupt) { return } if (-not $InputObject -and -not $Table -and -not $SqlInstance) { Stop-PSFFunction -Message "You must pipe in an object or specify a Table" return } $objects = @() if ($InputObject) { $objects += $server.Databases[$Database].Tables | Where-Object { $_.IsSystemObject -eq $false -and $_.Name -in $InputObject } | Select-Object Schema, Name, Columns } else { $objects += $server.Databases[$Database].Tables | Where-Object { $_.IsSystemObject -eq $false } | Select-Object Schema, Name, Columns } if ($Schema) { $objects = $objects | Where-Object Schema -in $Schema } if ($Table) { $objects = $objects | Where-Object Name -in $Table } $objectCount = $objects.Count $objectStep = 1 if ($objectCount -ge 1) { foreach ($tableObject in $objects) { $task = "Creating table column test $($objectStep) of $($objectCount)" Write-Progress -ParentId 1 -Activity "Creating..." -Status 'Progress->' -PercentComplete ($objectStep / $objectCount * 100) -CurrentOperation $task -Id 2 $testName = "test If table $($tableObject.Schema).$($tableObject.Name) has the correct columns" # Test if the name of the test does not become too long if ($testName.Length -gt 128) { Stop-PSFFunction -Message "Name of the test is too long" -Target $testName } $fileName = Join-Path -Path $OutputPath -ChildPath "$($testName).sql" $date = Get-Date -Format (Get-culture).DateTimeFormat.ShortDatePattern $creator = $env:username # Import the template try { $script = Get-Content -Path (Join-Path -Path $TemplateFolder -ChildPath "TableColumnTest.template") } catch { Stop-PSFFunction -Message "Could not import test template 'TableColumnTest.template'" -Target $testName -ErrorRecord $_ } # Get the columns $query = "SELECT c.name AS ColumnName, st.name AS DataType, c.max_length AS MaxLength, c.precision AS [Precision], c.scale AS Scale FROM sys.columns AS c INNER JOIN sys.tables AS t ON t.object_id = c.object_id INNER JOIN sys.schemas AS s ON s.schema_id = t.schema_id LEFT JOIN sys.types AS st ON st.user_type_id = c.user_type_id WHERE t.type = 'U' AND s.name = '$($tableObject.Schema)' AND t.name = '$($tableObject.Name)' ORDER BY t.name, c.name;" try { $columns = Invoke-DbaQuery -SqlInstance $SqlInstance -SqlCredential $SqlCredential -Database $Database -Query $query } catch { Stop-PSFFunction -Message "Could not retrieve columns for [$($tableObject.Schema)].[$($tableObject.Name)]" -Target $tableObject -Continue } $columnTextCollection = @() # Loop through the columns foreach ($column in $columns) { $columnText = "`t('$($column.ColumnName)', '$($column.DataType)', $($column.MaxLength), $($column.Precision), $($column.Scale))" $columnTextCollection += $columnText } # Replace the markers with the content $script = $script.Replace("___TESTCLASS___", $TestClass) $script = $script.Replace("___TESTNAME___", $testName) $script = $script.Replace("___SCHEMA___", $tableObject.Schema) $script = $script.Replace("___NAME___", $tableObject.Name) $script = $script.Replace("___CREATOR___", $creator) $script = $script.Replace("___DATE___", $date) $script = $script.Replace("___COLUMNS___", ($columnTextCollection -join ",`n") + ";") # Write the test if ($PSCmdlet.ShouldProcess("$($tableObject.Schema).$($tableObject.Name)", "Writing Table Column Test")) { try { Write-PSFMessage -Message "Creating table column test for table '$($tableObject.Schema).$($tableObject.Name)'" $script | Out-File -FilePath $fileName [PSCustomObject]@{ TestName = $testName Category = "TableColumn" Creator = $creator FileName = $fileName } } catch { Stop-PSFFunction -Message "Something went wrong writing the test" -Target $testName -ErrorRecord $_ } } $objectStep++ } } } } function New-PSTGTableConstraintTest { <# .SYNOPSIS Function to test the constraints in a table .DESCRIPTION The function will retrieve the constraints for a table and create a test for it .PARAMETER SqlInstance The target SQL Server instance or instances. Server version must be SQL Server version 2012 or higher. .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 Schema Filter the tables based on schema .PARAMETER Table Table(s) to create tests for .PARAMETER OutputPath Path to output the test to .PARAMETER Creator The person that created the tests. By default the command will get the environment username .PARAMETER TemplateFolder Path to template folder. By default the internal templates folder will be used .PARAMETER TestClass Test class name to use for the test .PARAMETER InputObject Takes the parameters required from a Table object that has been piped into the command .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 New-PSTGTableConstraintTest -Table $table -OutputPath $OutputPath Create a new constraint test .EXAMPLE $tables | New-PSTGTableConstraintTest -OutputPath $OutputPath Create the tests using pipelines #> [CmdletBinding(SupportsShouldProcess)] param( [DbaInstanceParameter]$SqlInstance, [pscredential]$SqlCredential, [string]$Database, [string[]]$Schema, [string[]]$Table, [string]$OutputPath, [string]$Creator, [string]$TemplateFolder, [string]$TestClass, [parameter(ParameterSetName = "InputObject", ValueFromPipeline)] [Microsoft.SqlServer.Management.Smo.Table[]]$InputObject, [switch]$EnableException ) begin { # Check 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 } # Check the output path if (-not $OutputPath) { Stop-PSFFunction -Message "Please enter an output path" return } if (-not (Test-Path -Path $OutputPath)) { try { $null = New-Item -Path $OutputPath -ItemType Directory } catch { Stop-PSFFunction -Message "Something went wrong creating the output directory" -Target $OutputPath -ErrorRecord $_ } } # Check the template folder 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 } if (-not $TestClass) { $TestClass = "TestBasic" } $date = Get-Date -Format (Get-culture).DateTimeFormat.ShortDatePattern if (-not $Creator) { $Creator = $env:username } # 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 '$Database' cannot be found on '$SqlInstance'" -Target $Database } $task = "Collecting objects" Write-Progress -ParentId 1 -Activity " Table Columns" -Status 'Progress->' -CurrentOperation $task -Id 2 } process { if (Test-PSFFunctionInterrupt) { return } if (-not $InputObject -and -not $Table -and -not $SqlInstance) { Stop-PSFFunction -Message "You must pipe in an object or specify a Table" return } $objects = @() if ($InputObject) { $objects += $server.Databases[$Database].Tables | Where-Object { $_.IsSystemObject -eq $false -and $_.Name -in $InputObject } | Select-Object Schema, Name, Columns } else { $objects += $server.Databases[$Database].Tables | Where-Object { $_.IsSystemObject -eq $false } | Select-Object Schema, Name, Columns } if ($Schema) { [array]$objects = $objects | Where-Object Schema -in $Schema } if ($Table) { [array]$objects = $objects | Where-Object Name -in $Table } $objectCount = $objects.Count $objectStep = 1 if ($objectCount -ge 1) { foreach ($object in $objects) { $task = "Creating table constraint test $($objectStep) of $($objectCount)" Write-Progress -ParentId 1 -Activity "Creating..." -Status 'Progress->' -PercentComplete ($objectStep / $objectCount * 100) -CurrentOperation $task -Id 2 $testName = "test If table $($object.Schema).$($object.Name) has the correct constraints" # Test if the name of the test does not become too long if ($testName.Length -gt 128) { Stop-PSFFunction -Message "Name of the test is too long" -Target $testName } $fileName = Join-Path -Path $OutputPath -ChildPath "$($testName).sql" $date = Get-Date -Format (Get-culture).DateTimeFormat.ShortDatePattern $creator = $env:username # Import the template try { $script = Get-Content -Path (Join-Path -Path $TemplateFolder -ChildPath "TableConstraintTest.template") } catch { Stop-PSFFunction -Message "Could not import test template 'TableConstraintTest.template'" -Target $testName -ErrorRecord $_ } # Get the columns $query = "SELECT s.name AS [SchemaName], t.name AS [TableName], OBJECT_NAME(o.OBJECT_ID) AS [ConstraintName], o.type_desc AS ConstraintType FROM sys.objects as o inner join sys.schemas as s on s.schema_id = o.schema_id INNER JOIN sys.tables as t on t.object_id = o.parent_object_id WHERE o.type_desc LIKE '%CONSTRAINT' AND s.name = '$($object.Schema)' AND t.name = '$($object.Name)' ORDER BY ConstraintName" try { $constraints = Invoke-DbaQuery -SqlInstance $SqlInstance -SqlCredential $SqlCredential -Database $Database -Query $query } catch { Stop-PSFFunction -Message "Could not retrieve columns for [$($object.Schema)].[$($object.Name)]" -Target $object -Continue } $constraintTextCollection = @() # Loop through the columns foreach ($constr in $constraints) { $constraintText = "`t('$($constr.SchemaName)', '$($constr.TableName)', '$($constr.ConstraintName)', '$($constr.ConstraintType)')" $constraintTextCollection += $constraintText } # Replace the markers with the content $script = $script.Replace("___TESTCLASS___", $TestClass) $script = $script.Replace("___TESTNAME___", $testName) $script = $script.Replace("___SCHEMA___", $object.Schema) $script = $script.Replace("___NAME___", $object.Name) $script = $script.Replace("___CREATOR___", $creator) $script = $script.Replace("___DATE___", $date) $script = $script.Replace("___COLUMNS___", ($constraintTextCollection -join ",`n") + ";") # Write the test if ($PSCmdlet.ShouldProcess("$($object.Schema).$($object.Name)", "Writing Table Column Test")) { try { Write-PSFMessage -Message "Creating table constraint test for table '$($object.Schema).$($object.Name)'" $script | Out-File -FilePath $fileName [PSCustomObject]@{ TestName = $testName Category = "TableColumn" Creator = $creator FileName = $fileName } } catch { Stop-PSFFunction -Message "Something went wrong writing the test" -Target $testName -ErrorRecord $_ } } $objectStep++ } } } } function New-PSTGTableIndexTest { <# .SYNOPSIS Function to test the indexes for a table .DESCRIPTION The function will retrieve the current indexes for a table and create a test for it .PARAMETER SqlInstance The target SQL Server instance or instances. Server version must be SQL Server version 2012 or higher. .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 Schema Filter the tables based on schema .PARAMETER Table Table(s) to create tests for .PARAMETER OutputPath Path to output the test to .PARAMETER Creator The person that created the tests. By default the command will get the environment username .PARAMETER TemplateFolder Path to template folder. By default the internal templates folder will be used .PARAMETER TestClass Test class name to use for the test .PARAMETER InputObject Takes the parameters required from a Table object that has been piped into the command .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 New-PSTGTableIndexTest -Table $table -OutputPath $OutputPath Create a new table column test .EXAMPLE $tables | New-PSTGTableIndexTest -OutputPath $OutputPath Create the tests using pipelines #> [CmdletBinding(SupportsShouldProcess)] param( [DbaInstanceParameter]$SqlInstance, [pscredential]$SqlCredential, [string]$Database, [string[]]$Schema, [string[]]$Table, [string]$OutputPath, [string]$Creator, [string]$TemplateFolder, [string]$TestClass, [parameter(ParameterSetName = "InputObject", ValueFromPipeline)] [Microsoft.SqlServer.Management.Smo.Table[]]$InputObject, [switch]$EnableException ) begin { # Check 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 } # Check the output path if (-not $OutputPath) { Stop-PSFFunction -Message "Please enter an output path" return } if (-not (Test-Path -Path $OutputPath)) { try { $null = New-Item -Path $OutputPath -ItemType Directory } catch { Stop-PSFFunction -Message "Something went wrong creating the output directory" -Target $OutputPath -ErrorRecord $_ } } # Check the template folder 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 } if (-not $TestClass) { $TestClass = "TestBasic" } $date = Get-Date -Format (Get-culture).DateTimeFormat.ShortDatePattern if (-not $Creator) { $Creator = $env:username } # 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 '$Database' cannot be found on '$SqlInstance'" -Target $Database } $task = "Collecting objects" Write-Progress -ParentId 1 -Activity " Table Columns" -Status 'Progress->' -CurrentOperation $task -Id 2 } process { if (Test-PSFFunctionInterrupt) { return } if (-not $InputObject -and -not $Table -and -not $SqlInstance) { Stop-PSFFunction -Message "You must pipe in an object or specify a Table" return } $objects = @() if ($InputObject) { $objects += $server.Databases[$Database].Tables | Where-Object { $_.IsSystemObject -eq $false -and $_.Name -in $InputObject } | Select-Object Schema, Name, Indexes } else { $objects += $server.Databases[$Database].Tables | Where-Object { $_.IsSystemObject -eq $false } | Select-Object Schema, Name, Indexes } if ($Schema) { $objects = $objects | Where-Object Schema -in $Schema } if ($Table) { $objects = $objects | Where-Object Name -in $Table } $objectCount = $objects.Count $objectStep = 1 if ($objectCount -ge 1) { foreach ($tableObject in $objects) { if ($tableObject.Indexes.Count -ge 1) { $task = "Creating index column test $($objectStep) of $($objectCount)" Write-Progress -ParentId 1 -Activity "Creating..." -Status 'Progress->' -PercentComplete ($objectStep / $objectCount * 100) -CurrentOperation $task -Id 2 $testName = "test If table $($tableObject.Schema).$($tableObject.Name) has the correct indexes" # Test if the name of the test does not become too long if ($testName.Length -gt 128) { Stop-PSFFunction -Message "Name of the test is too long" -Target $testName } $fileName = Join-Path -Path $OutputPath -ChildPath "$($testName).sql" $date = Get-Date -Format (Get-culture).DateTimeFormat.ShortDatePattern $creator = $env:username # Import the template try { $script = Get-Content -Path (Join-Path -Path $TemplateFolder -ChildPath "TableIndexTest.template") } catch { Stop-PSFFunction -Message "Could not import test template 'TableIndexTest.template'" -Target $testName -ErrorRecord $_ } # Get the columns $query = "SELECT ind.name AS Name FROM sys.indexes ind INNER JOIN sys.tables t ON ind.object_id = t.object_id INNER JOIN sys.schemas AS s ON s.schema_id = t.schema_id WHERE s.name = '$($tableObject.Schema)' AND t.name = '$($tableObject.Name)' AND ind.Name IS NOT NULL;" try { $indexes = Invoke-DbaQuery -SqlInstance $SqlInstance -SqlCredential $SqlCredential -Database $Database -Query $query } catch { Stop-PSFFunction -Message "Could not retrieve indexes for [$($tableObject.Schema)].[$($tableObject.Name)]" -Target $tableObject -Continue } $indexTextCollection = @() # Loop through the columns foreach ($index in $indexes) { $indexText = "`t('$($index.Name)')" $indexTextCollection += $indexText } # Replace the markers with the content $script = $script.Replace("___TESTCLASS___", $TestClass) $script = $script.Replace("___TESTNAME___", $testName) $script = $script.Replace("___SCHEMA___", $tableObject.Schema) $script = $script.Replace("___NAME___", $tableObject.Name) $script = $script.Replace("___CREATOR___", $creator) $script = $script.Replace("___DATE___", $date) $script = $script.Replace("___INDEXES___", ($indexTextCollection -join ",`n") + ";") # Write the test if ($PSCmdlet.ShouldProcess("$($tableObject.Schema).$($tableObject.Name)", "Writing Table Index Test")) { try { Write-PSFMessage -Message "Creating table index test for table '$($tableObject.Schema).$($tableObject.Name)'" $script | Out-File -FilePath $fileName [PSCustomObject]@{ TestName = $testName Category = "TableIndex" Creator = $creator FileName = $fileName } } catch { Stop-PSFFunction -Message "Something went wrong writing the test" -Target $testName -ErrorRecord $_ } } } $objectStep++ } } } } function New-PSTGViewColumnTest { <# .SYNOPSIS Function to create view column tests .DESCRIPTION The function will retrieve the columns for a view and create a test for it .PARAMETER SqlInstance The target SQL Server instance or instances. Server version must be SQL Server version 2012 or higher. .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 Schema Filter the views based on schema .PARAMETER View View(s) to create tests forr .PARAMETER OutputPath Path to output the test to .PARAMETER Creator The person that created the tests. By default the command will get the environment username .PARAMETER TemplateFolder Path to template folder. By default the internal templates folder will be used .PARAMETER TestClass Test class name to use for the test .PARAMETER InputObject Takes the parameters required from a View object that has been piped into the command .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 New-PSTGViewColumnTest -View $view -OutputPath $OutputPath Create a new view column test .EXAMPLE $views | New-PSTGViewColumnTest -OutputPath $OutputPath Create the tests using pipelines #> [CmdletBinding(SupportsShouldProcess)] param( [DbaInstanceParameter]$SqlInstance, [pscredential]$SqlCredential, [string]$Database, [string[]]$Schema, [string[]]$View, [string]$OutputPath, [string]$Creator, [string]$TemplateFolder, [string]$TestClass, [parameter(ParameterSetName = "InputObject", ValueFromPipeline)] [object[]]$InputObject, [switch]$EnableException ) begin { # Check 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 } # Check the output path if (-not $OutputPath) { Stop-PSFFunction -Message "Please enter an output path" return } if (-not (Test-Path -Path $OutputPath)) { try { $null = New-Item -Path $OutputPath -ItemType Directory } catch { Stop-PSFFunction -Message "Something went wrong creating the output directory" -Target $OutputPath -ErrorRecord $_ } } # Check the template folder 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 } if (-not $TestClass) { $TestClass = "TestBasic" } $date = Get-Date -Format (Get-culture).DateTimeFormat.ShortDatePattern if (-not $Creator) { $Creator = $env:username } # 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 } $task = "Collecting objects" Write-Progress -ParentId 1 -Activity " View Columns" -Status 'Progress->' -CurrentOperation $task -Id 2 } process { if (Test-PSFFunctionInterrupt) { return } if (-not $InputObject -and -not $View -and -not $SqlInstance) { Stop-PSFFunction -Message "You must pipe in an object or specify a View" return } $objects = @() if ($InputObject) { $objects += $server.Databases[$Database].Views | Where-Object Name -in $InputObject | Select-Object Schema, Name, Columns } else { $objects += $server.Databases[$Database].Views | Where-Object IsSystemObject -eq $false | Select-Object Schema, Name, Columns } if ($Schema) { $objects = $objects | Where-Object Schema -in $Schema } if ($View) { $objects = $objects | Where-Object Name -in $View } $objectCount = $objects.Count $objectStep = 1 if ($objectCount -ge 1) { foreach ($viewObject in $objects) { $task = "Creating view test $($objectStep) of $($objectCount)" Write-Progress -ParentId 1 -Activity "Creating..." -Status 'Progress->' -PercentComplete ($objectStep / $objectCount * 100) -CurrentOperation $task -Id 2 $testName = "test If view $($viewObject.Schema).$($viewObject.Name) has the correct columns" # Test if the name of the test does not become too long if ($testName.Length -gt 128) { Stop-PSFFunction -Message "Name of the test is too long" -Target $testName } $fileName = Join-Path -Path $OutputPath -ChildPath "$($testName).sql" $date = Get-Date -Format (Get-culture).DateTimeFormat.ShortDatePattern $creator = $env:username # Import the template try { $script = Get-Content -Path (Join-Path -Path $TemplateFolder -ChildPath "ViewColumnTest.template") } catch { Stop-PSFFunction -Message "Could not import test template 'ViewColumnTest.template'" -Target $testName -ErrorRecord $_ } # Get the columns $query = "SELECT c.name AS ColumnName, st.name AS DataType, c.max_length AS MaxLength, c.precision AS [Precision], c.scale AS Scale FROM sys.columns AS c INNER JOIN sys.views AS v ON v.object_id = c.object_id INNER JOIN sys.schemas AS s ON s.schema_id = v.schema_id LEFT JOIN sys.types AS st ON st.user_type_id = c.user_type_id WHERE v.type = 'V' AND s.name = '$($viewObject.Schema)' AND v.name = '$($viewObject.Name)' ORDER BY v.name, c.name;" try { $columns = Invoke-DbaQuery -SqlInstance $SqlInstance -SqlCredential $SqlCredential -Database $Database -Query $query } catch { Stop-PSFFunction -Message "Could not retrieve columns for [$($viewObject.Schema)].[$($viewObject.Name)]" -Target $viewObject -Continue } $columnTextCollection = @() # Loop through the columns foreach ($column in $columns) { $columnText = "`t('$($column.ColumnName)', '$($column.DataType)', $($column.MaxLength), $($column.Precision), $($column.Scale))" $columnTextCollection += $columnText } # Replace the markers with the content $script = $script.Replace("___TESTCLASS___", $TestClass) $script = $script.Replace("___TESTNAME___", $testName) $script = $script.Replace("___SCHEMA___", $viewObject.Schema) $script = $script.Replace("___NAME___", $viewObject.Name) $script = $script.Replace("___CREATOR___", $creator) $script = $script.Replace("___DATE___", $date) $script = $script.Replace("___COLUMNS___", ($columnTextCollection -join ",`n") + ";") # Write the test if ($PSCmdlet.ShouldProcess("$($viewObject.Schema).$($viewObject.Name)", "Writing View Column Test")) { try { Write-PSFMessage -Message "Creating view column test for table '$($viewObject.Schema).$($viewObject.Name)'" $script | Out-File -FilePath $fileName [PSCustomObject]@{ TestName = $testName Category = "ViewColumn" Creator = $creator FileName = $fileName } } catch { Stop-PSFFunction -Message "Something went wrong writing the test" -Target $testName -ErrorRecord $_ } } $objectStep++ } } } } <# This is an example configuration file By default, it is enough to have a single one of them, however if you have enough configuration settings to justify having multiple copies of it, feel totally free to split them into multiple files. #> <# # Example Configuration Set-PSFConfig -Module 'PStSQLtTestGenerator' -Name 'Example.Setting' -Value 10 -Initialize -Validation 'integer' -Handler { } -Description "Example configuration setting. Your module can then use the setting using 'Get-PSFConfigValue'" #> Set-PSFConfig -Module 'PStSQLtTestGenerator' -Name 'Import.DoDotSource' -Value $false -Initialize -Validation 'bool' -Description "Whether the module files should be dotsourced on import. By default, the files of this module are read as string value and invoked, which is faster but worse on debugging." Set-PSFConfig -Module 'PStSQLtTestGenerator' -Name 'Import.IndividualFiles' -Value $false -Initialize -Validation 'bool' -Description "Whether the module files should be imported individually. During the module build, all module code is compiled into few files, which are imported instead by default. Loading the compiled versions is faster, using the individual files is easier for debugging and testing out adjustments." <# # Example: Register-PSFTeppScriptblock -Name "PStSQLtTestGenerator.alcohol" -ScriptBlock { 'Beer','Mead','Whiskey','Wine','Vodka','Rum (3y)', 'Rum (5y)', 'Rum (7y)' } #> <# # Example: Register-PSFTeppArgumentCompleter -Command Get-Alcohol -Parameter Type -Name PStSQLtTestGenerator.alcohol #> New-PSFLicense -Product 'PStSQLtTestGenerator' -Manufacturer 'sstad' -ProductVersion $script:ModuleVersion -ProductType Module -Name MIT -Version "1.0.0.0" -Date (Get-Date "2019-09-18") -Text @" Copyright (c) 2019 sstad Permission is hereby granted, free of charge, to any person obtaining a copy of this software and associated documentation files (the "Software"), to deal in the Software without restriction, including without limitation the rights to use, copy, modify, merge, publish, distribute, sublicense, and/or sell copies of the Software, and to permit persons to whom the Software is furnished to do so, subject to the following conditions: The above copyright notice and this permission notice shall be included in all copies or substantial portions of the Software. THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE. "@ #endregion Load compiled code |