sqlServer.tests.ps1
|
# This was written to test against databases keeping call data records for Skype for business. # At some point I will replace it with a more generic SQL database test. Describe "Connect to and query SQL Server " { BeforeAll { $tableName = "CallType" $fieldName1 = "CallType" #Must be a name used to test wild card $fieldName2 = "CallTypeId" #Test for values 2,3,4 $dbName = "LcsCDR" $sessionName = "LcsCDR" $sqlconn = "bp1xeucc023" $End = [datetime]::Now ; $Start = $End.AddHours(-1) $ArbitrarySQL = "exec dbo.CdrP2PSessionList @_StartTime ='" + $Start.ToString("yyyy-MM-dd HH:mm") + "', @_EndTime ='" + $End.ToString("yyyy-MM-dd HH:mm") + "'" $session = Get-SQL -MSSqlServer -Connection $sqlconn -use $dbName -Session $sessionName -ForceNew } It "Creates a PowerShell alias, matching the session name '$sessionName'" { {Get-Alias -Name $sessionName} | Should -not -throw (invoke-command -ScriptBlock ([scriptblock]::Create("$sessionname")) ).database | Should -Be $sessionName } It "Creates an open session in `$DBSessions, named '$sessionName'" { $DbSessions["$sessionName"].State | Should -Be "Open" } It "Can select a database using the -USE Alias" { $DbSessions["$sessionName"].database | Should -Be $dbName } It "Can show tables in the database" { (Get-SQL -Session $sessionName -ShowTables ).Count | Should -BeGreaterThan 0 } It "Can describe the fields in the table [$tableName]" { (Get-SQL -Session $sessionName -Describe $tableName ).Count | Should -BeGreaterThan 0 } It "Can return the [whole] table [$tableName]" { (Get-SQL -Session $sessionName -Quiet -Table $tableName ).Count | Should -BeGreaterThan 0 } It "Can run abritrary SQL as passed as via the pipe" { ($ArbitrarySQL | Get-SQL -Session $sessionName -Quiet ).Count | Should -BeGreaterThan 0 } It "Can run abritrary SQL as passed as a parameter" { (Get-SQL -Session $sessionName -Quiet $ArbitrarySQL ).Count | Should -BeGreaterThan 0 } It "Can run a SELECT query with -Select, -Distinct, -OrderBy and -Where parameters" { (Get-SQL -Session $sessionName -Quiet -Table $tableName -Select $fieldName1 ` -Distinct -OrderBy $fieldName1 -Where $fieldName2 -gt 0 ).Count | Should -BeGreaterThan 0 } It "Can run a SELECT query with -Select, -Distinct, -OrderBy and -Where parameters, and values for where condition Piped " { (2,3,4 | Get-SQL -Session $sessionName -Quiet -Table $tableName -Select $fieldName1 ` -Distinct -OrderBy $fieldName1 -Where $fieldName2 -eq ).Count | Should -BeGreaterThan 0 } It "Can run a SELECT query with -Select, -Distinct, -OrderBy and -Where parameters and where condition piped " { ("=2","=3",">=4" | Get-SQL -Session $sessionName -Quiet -Table $tableName -Select $fieldName1 ` -Distinct -OrderBy $fieldName1 -Where $fieldName2 ).Count | Should -BeGreaterThan 0 } It "Can run a SELECT query with -Select, -Distinct and -OrderBy parameters and WHERE... clause piped " { ("Where $fieldName2 =2","Where $fieldName2 =3","Where $fieldName2 >=4" | Get-SQL -Session $sessionName -Quiet -Table $tableName -Select $fieldName1 ` -Distinct -OrderBy $fieldName1 ).Count | Should -BeGreaterThan 0 } It "Can run a SELECT query with the WHERE... clause piped but no -Select, -Distinct or -OrderBy " { ("Where $fieldName2 =2","Where $fieldName2 =3","Where $fieldName2 >=4" | Get-SQL -Session $sessionName -Quiet -Table $tableName ).Count | Should -BeGreaterThan 0 } It "Can run a SELECT query with multiple fields in -Select and -OrderBy" { ( Get-SQL -Session $sessionName -Quiet -Table $tableName -Select $fieldName1, $fieldName2 -OrderBy $fieldName1, $fieldName2 ).Count | Should -BeGreaterThan 0 } It "Can run a SELECT query with -Select holding a 'Top' clause " { ( Get-SQL -Session $sessionName -Quiet -Table $tableName -Select "Top 5 *" ` -OrderBy $fieldName1,$fieldName2 ).Count | Should -BeGreaterThan 0 } It "Can run a SELECT query with a different final clause (e.g. 'order by') as a parameter " { ( Get-SQL -Session $sessionName -Quiet ` -Table $tableName "order by $fieldName1 " ).Count | Should -BeGreaterThan 0 } It "Can run a SELECT query with a different final clause piped " { ("order by $fieldName1 " | Get-SQL -Session $sessionName -Quiet -Table $tableName ).Count | Should -BeGreaterThan 0 } It "Can run a SELECT ... WHERE ... LIKE query with 'naked' syntax and translate * as a wildcard" { ( SQL -Session $sessionName -Quiet -Select CallType,CallTypeId ` -From CallType -Where CallType -Like audio* ).Count | Should -BeGreaterThan 0 } It "Can run a SELECT query with a date object as a value for where, -GroupBy and both fieldName & aggreate function in -Select " { ( Get-SQL -Session $sessionname -Quiet -Table "Registration" -Select RegistrarId, "Count(*) As total" -Where "RegisterTime" -GT ([datetime]::Today) ` -GroupBy "RegistrarId" ).Count | Should -BeGreaterThan 0 } It "Can add a row to a table" {} -Pending It "Can Delete a row from a table" {} -Pending It "Can Change a row in a table" {} -Pending AfterAll {Get-Sql -Session $sessionName -Close } } |