Lite.tests.ps1
|
Describe "Connect to and query Excel Spreadsheet " { BeforeAll { $sessionName = "lite" $liteconn = ".\TestData.sqlite" $tableName = "F1Results" $ArbitrarySQL = "SELECT * from $tableName" $fieldname1 = "Driver" $fieldname2 = "Points" $session = Get-SQL -Lite -Connection $liteConn -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 'main' } 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 20 ).Count | Should -beGreaterThan 0 } It "Can run a SELECT query with -Select, -Distinct, -OrderBy and -Where parameters, and values for where condition Piped " { (5,10 , 20 | 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 " { ("> 5","> 10",">= 20" | 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 Points >5 ","Where Points >10","Where Points >= 20" | 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 Points >5 ","Where Points >10","Where Points >= 20" | 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 "Race",$fieldname1 -OrderBy $fieldname2,"GridPosition" ).Count | Should -beGreaterThan 0 } It "Can run a SELECT query with -Select holding a date formula" { #SQlite doesn't support "Top" ( Get-SQL -Session $sessionName -Quiet -Table $tableName -Select "datetime(date, 'unixepoch') as RaceDate","*" -OrderBy $fieldname1 ).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 Race,GridPosition,Points -from "F1Results" -Where Driver -like "Lewis*" -Quiet ).Count | Should -beGreaterThan 0 } It "Can run a SELECT Query with -GroupBy and both fieldName & aggreate function in -Select " { ( Get-SQL -Session $sessionName -Quiet -Table $tableName -select $fieldname1,"Count(*) as total" -GroupBy $fieldname1 ).Count | Should -beGreaterThan 0 } It "Can INSERT rows into a table via the pipeline or a parameter and translate dates" { $raceResult = @{Race="Portugese"; Date=([datetime]"2020-10-25"); Driver="Lewis Hamilton"; Team="Mercedes";FinishPosition=1;GridPosition=1;Points=26} $raceResult | Get-sql -Session $sessionName -Insert $tableName Get-sql -Session $sessionName -Insert $tableName $raceResult (Get-sql -Session $sessionName -table $tableName -where "date" -eq $raceResult.Date.Subtract([datetime]::UnixEpoch).totalseconds -Quiet).Count | Should -be 2 } # Excel Driver doens not support "Can Delete a row from a table" It "Can SET new values in a row in a table" { Get-SQL -Session $sessionName -Table $tableName -WHERE "Race" -eq "Portugese"` -set "Race" -Values "Portugal" -Confirm:$false $new = Get-SQL -Session $sessionName -Table $tableName -WHERE "Race" -eq "Portugal" -Quiet $New.count | Should -be 2 $new[0].Points | Should -be 26 } It "Can Delete rows from a table" { Get-SQL -Session $sessionName -Table $tableName -WHERE "Race" -eq "Portugal" -Delete -Confirm:$false $new = Get-SQL -Session $sessionName -Table $tableName -WHERE "Race" -eq "Portugal" -Quiet $new | Should -BeNullOrEmpty } AfterAll {Get-Sql -Session $sessionName -Close } } |