tests/Invoke-DbaSqlQuery.Tests.ps1
|
$CommandName = $MyInvocation.MyCommand.Name.Replace(".Tests.ps1", "") Write-Host -Object "Running $PSCommandpath" -ForegroundColor Cyan . "$PSScriptRoot\constants.ps1" Describe "$CommandName Integration Tests" -Tags "IntegrationTests" { It "supports pipable instances" { $results = $script:instance1, $script:instance2 | Invoke-DbaSqlQuery -Database tempdb -Query "Select 'hello' as TestColumn" foreach ($result in $results) { $result.TestColumn | Should -Be 'hello' } } It "supports parameters" { $sqlParams = @{testvalue = 'hello'} $results = $script:instance1 | Invoke-DbaSqlQuery -Database tempdb -Query "Select @testvalue as TestColumn" -SqlParameters $sqlParams foreach ($result in $results) { $result.TestColumn | Should -Be 'hello' } } It "supports AppendServerInstance" { $conn1 = Connect-DbaInstance $script:instance1 $conn2 = Connect-DbaInstance $script:instance2 $serverInstances = $conn1.Name, $conn2.Name $results = $script:instance1, $script:instance2 | Invoke-DbaSqlQuery -Database tempdb -Query "Select 'hello' as TestColumn" -AppendServerInstance foreach ($result in $results) { $result.ServerInstance | Should -Not -Be Null $result.ServerInstance | Should -BeIn $serverInstances } } It "supports pipable databases" { $dbs = Get-DbaDatabase -SqlInstance $script:instance1, $script:instance2 $results = $dbs | Invoke-DbaSqlQuery -Query "Select 'hello' as TestColumn, DB_NAME() as dbname" foreach ($result in $results) { $result.TestColumn | Should -Be 'hello' } 'tempdb' | Should -Bein $results.dbname } It "stops when piped databases and -Database" { $dbs = Get-DbaDatabase -SqlInstance $script:instance1, $script:instance2 { $dbs | Invoke-DbaSqlQuery -Query "Select 'hello' as TestColumn, DB_NAME() as dbname" -Database tempdb -EnableException } | Should Throw "You can't" } It "supports reading files" { $testPath = "TestDrive:\dbasqlquerytest.txt" Set-Content $testPath -value "Select 'hello' as TestColumn, DB_NAME() as dbname" $results = Invoke-DbaSqlQuery -SqlInstance $script:instance1 -Database tempdb -File $testPath foreach ($result in $results) { $result.TestColumn | Should -Be 'hello' } 'tempdb' | Should -Bein $results.dbname } It "supports reading entire directories, just *.sql" { $testPath = "TestDrive:\" Set-Content "$testPath\dbasqlquerytest.sql" -value "Select 'hello' as TestColumn, DB_NAME() as dbname" Set-Content "$testPath\dbasqlquerytest2.sql" -value "Select 'hello2' as TestColumn, DB_NAME() as dbname" Set-Content "$testPath\dbasqlquerytest2.txt" -value "Select 'hello3' as TestColumn, DB_NAME() as dbname" $pathinfo = Get-Item $testpath $results = Invoke-DbaSqlQuery -SqlInstance $script:instance1 -Database tempdb -File $pathinfo 'hello' | Should -Bein $results.TestColumn 'hello2' | Should -Bein $results.TestColumn 'hello3' | Should -Not -Bein $results.TestColumn 'tempdb' | Should -Bein $results.dbname } It "supports http files" { $cleanup = "IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[CommandLog]') AND type in (N'U')) DROP TABLE [dbo].[CommandLog]" $null = Invoke-DbaSqlQuery -SqlInstance $script:instance1 -Database tempdb -Query $cleanup $CloudQuery = 'https://raw.githubusercontent.com/sqlcollaborative/appveyor-lab/master/sql2016-startup/ola/CommandLog.sql' $null = Invoke-DbaSqlQuery -SqlInstance $script:instance1 -Database tempdb -File $CloudQuery $check = "SELECT name FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[CommandLog]') AND type in (N'U')" $results = Invoke-DbaSqlQuery -SqlInstance $script:instance1 -Database tempdb -Query $check $results.Name | Should -Be 'CommandLog' $null = Invoke-DbaSqlQuery -SqlInstance $script:instance1 -Database tempdb -Query $cleanup } It "supports smo objects" { $cleanup = "IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[CommandLog]') AND type in (N'U')) DROP TABLE [dbo].[CommandLog]" $null = Invoke-DbaSqlQuery -SqlInstance $script:instance1, $script:instance2 -Database tempdb -Query $cleanup $CloudQuery = 'https://raw.githubusercontent.com/sqlcollaborative/appveyor-lab/master/sql2016-startup/ola/CommandLog.sql' $null = Invoke-DbaSqlQuery -SqlInstance $script:instance1 -Database tempdb -File $CloudQuery $smoobj = Get-Dbatable -SqlInstance $script:instance1 -Database tempdb | Where-Object Name -eq 'CommandLog' $null = Invoke-DbaSqlQuery -SqlInstance $script:instance2 -Database tempdb -SqlObject $smoobj $check = "SELECT name FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[CommandLog]') AND type in (N'U')" $results = Invoke-DbaSqlQuery -SqlInstance $script:instance2 -Database tempdb -Query $check $results.Name | Should Be 'CommandLog' $null = Invoke-DbaSqlQuery -SqlInstance $script:instance1, $script:instance2 -Database tempdb -Query $cleanup } <# It "supports loose objects (with SqlInstance and database props)" { $dbs = Get-DbaDatabaseState -SqlInstance $script:instance1, $script:instance2 $results = $dbs | Invoke-DbaSqlQuery -Query "Select 'hello' as TestColumn, DB_NAME() as dbname" foreach ($result in $results) { $result.TestColumn | Should -Be 'hello' } }#> It "supports queries with GO statements" { $Query = @' SELECT DB_NAME() as dbname GO SELECT @@servername as dbname '@ $results = $script:instance1, $script:instance2 | Invoke-DbaSqlQuery -Database tempdb -Query $Query $results.dbname -contains 'tempdb' | Should -Be $true } It "streams correctly 'messages' with Verbose" { $query = @' DECLARE @time char(19) PRINT 'stmt_1|PRINT start|' + CONVERT(VARCHAR(19), GETUTCDATE(), 126) SET @time= CONVERT(VARCHAR(19), GETUTCDATE(), 126) RAISERROR ('stmt_2|RAISERROR before WITHOUT NOWAIT|%s', 0, 1, @time) WAITFOR DELAY '00:00:03' PRINT 'stmt_3|PRINT after the first delay|' + CONVERT(VARCHAR(19), GETUTCDATE(), 126) SET @time= CONVERT(VARCHAR(19), GETUTCDATE(), 126) RAISERROR ('stmt_4|RAISERROR with NOWAIT|%s', 0, 1, @time) WITH NOWAIT WAITFOR DELAY '00:00:03' PRINT 'stmt_5|PRINT after the second delay|' + CONVERT(VARCHAR(19), GETUTCDATE(), 126) SELECT 'hello' AS TestColumn WAITFOR DELAY '00:00:03' PRINT 'stmt_6|PRINT end|' + CONVERT(VARCHAR(19), GETUTCDATE(), 126) '@ $results = @() Invoke-DbaSqlQuery -SqlInstance $script:instance1 -Database tempdb -Query $query -Verbose 4>&1 | ForEach-Object { $results += [pscustomobject]@{ FiredAt = (Get-Date).ToUniversalTime() Out = $_ } } $results.Length | Should -Be 7 # 6 'messages' plus the actual resultset ($results | ForEach-Object { Get-Date -Date $_.FiredAt -f s } | Get-Unique).Count | Should -Not -Be 1 # the first WITH NOWAIT (stmt_4) and after #($results[0..3] | ForEach-Object { Get-Date -Date $_.FiredAt -f s } | Get-Unique).Count | Should -Be 1 # everything before stmt_4 is fired at the same time #$parsedstmt_1 = Get-Date -Date $results[0].Out.Message.split('|')[2] #(Get-Date -Date (Get-Date -Date $parsedstmt_1).AddSeconds(3) -f s) | Should -Be (Get-Date -Date $results[0].FiredAt -f s) # stmt_1 is fired 3 seconds after the logged date #$parsedstmt_4 = Get-Date -Date $results[3].Out.Message.split('|')[2] #(Get-Date -Date (Get-Date -Date $parsedstmt_4) -f s) | Should -Be (Get-Date -Date $results[0].FiredAt -f s) # stmt_4 is fired at the same time the logged date is } It "streams correctly 'messages' with MessagesToOutput" { $query = @' DECLARE @time char(19) PRINT 'stmt_1|PRINT start|' + CONVERT(VARCHAR(19), GETUTCDATE(), 126) SET @time= CONVERT(VARCHAR(19), GETUTCDATE(), 126) RAISERROR ('stmt_2|RAISERROR before WITHOUT NOWAIT|%s', 0, 1, @time) WAITFOR DELAY '00:00:03' PRINT 'stmt_3|PRINT after the first delay|' + CONVERT(VARCHAR(19), GETUTCDATE(), 126) SET @time= CONVERT(VARCHAR(19), GETUTCDATE(), 126) RAISERROR ('stmt_4|RAISERROR with NOWAIT|%s', 0, 1, @time) WITH NOWAIT WAITFOR DELAY '00:00:03' PRINT 'stmt_5|PRINT after the second delay|' + CONVERT(VARCHAR(19), GETUTCDATE(), 126) SELECT 'hello' AS TestColumn WAITFOR DELAY '00:00:03' PRINT 'stmt_6|PRINT end|' + CONVERT(VARCHAR(19), GETUTCDATE(), 126) '@ $results = @() Invoke-DbaSqlQuery -SqlInstance $script:instance1 -Database tempdb -Query $query -MessagesToOutput | ForEach-Object { $results += [pscustomobject]@{ FiredAt = (Get-Date).ToUniversalTime() Out = $_ } } $results.Length | Should -Be 7 # 6 'messages' plus the actual resultset ($results | ForEach-Object { Get-Date -Date $_.FiredAt -f s } | Get-Unique).Count | Should -Not -Be 1 # the first WITH NOWAIT (stmt_4) and after } } |