Excel.tests.ps1
|
Describe "Connect to and query Excel Spreadsheet " { BeforeAll { $sessionName = "XL" $xlconn = ".\dbtest.xlsx " $tableName = "[TestData$]" $ArbitrarySQL = "SELECT * from $tableName" $fieldname1 = "Extension" $fieldname2 = "Length" $session = Get-SQL -Excel -Connection $xlconn -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 (Resolve-Path $xlconn).Path.Trim() } It "Creates an open session in `$DBSessions, named '$sessionName'" { $DbSessions["$sessionName"].State | Should -be "Open" } 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 return the [whole] table $tableName and capture the data table in a variable " { [void](Get-Sql -Session $sessionName -Quiet -Table $tableName -OutputVariable Table ) $table.GetType().fullname | Should -be "System.Data.DataTable" } 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 500 ).Count | Should -beGreaterThan 0 } It "Can run a SELECT query with -Select, -Distinct, -OrderBy and -Where parameters, and values for where condition Piped " { (500,1000 , 10000 | Get-SQL -Session $sessionName -Quiet -Table $tableName -Select $fieldname1 -Distinct -OrderBy $fieldname1 -Where $fieldname2 -GT ).Count | Should -beGreaterThan 0 } It "Can run a SELECT query with -Select, -Distinct, -OrderBy and -Where parameters and where condition piped " { ("> 500","> 1000",">= 10000" | 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 Length >500 ","Where Length >1000","Where Length >= 10000" | 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 Length >500 ","Where Length >1000","Where Length >= 10000" | 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 "Name",$fieldname1 -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 -Select Name,Length,LastWriteTime -from [TestData$] -Where Extension -like ".ps*" -Quiet ).Count | Should -beGreaterThan 0 } It "Can run a SELECT Query with a date object as a parameter, -GroupBy and both fieldName & aggreate function in -Select " { (Get-SQL -Session $sessionName -Quiet -Table $tableName -Where "CreationTime" -LT ([datetime]::Now).AddDays(-3) ` -select $fieldname1,"Count(*) as total" -GroupBy $fieldname1 ).Count | Should -beGreaterThan 0 } It "Can INSERT rows into a table via the pipeline or a parameter" { $dirEntry = Get-Item (Get-Command -name powershell).Source | Select-Object -Property * -ExcludeProperty mod* $dirEntry, $dirEntry | Get-sql -Session $sessionName -Insert $tableName Get-sql -Session $sessionName -Insert $tableName $dirEntry (Get-sql -Session $sessionName -table $tableName -where "PSPath" -eq $dirEntry.PSPath -Quiet ).Count | Should -beGreaterThan 0 } # Excel Driver doens not support "Can Delete a row from a table" It "Can SET new values in a row in a table" { $old = Get-SQL -Session $sessionName -Table $tableName -Select "top 1 *" -Quiet Get-SQL -Session $sessionName -Table $tableName -WHERE "Format(LastWriteTimeUtc)" -eq $old.LastWriteTimeUtc ` -set "Attributes" -Values "Modified" -Confirm:$false $new = Get-SQL -Session $sessionName -Table $tableName -WHERE "Format(LastWriteTimeUtc)" -eq $old.LastWriteTimeUtc -Quiet $new.PSPath | Should -be $old.PSPath $new.Attributes | Should -be "Modified" Get-SQL -Session $sessionName -Table $tableName -WHERE "Format(LastWriteTimeUtc)" -eq $old.LastWriteTimeUtc ` -set "Attributes" -Values $old.Attributes -Confirm:$false $end = Get-SQL -Session $sessionName -Table $tableName -WHERE "Format(LastWriteTimeUtc)" -eq $old.LastWriteTimeUtc -Quiet $end.attributes | Should -be $old.Attributes } AfterAll {Get-Sql -Session $sessionName -Close } } |