Tests/SqlTemplate.Tests.ps1
if (Get-Module -Name SqlTemplate) {Remove-Module -Name SqlTemplate}; Import-Module .\SqlTemplate.psd1 Describe "Invoke-SqlTemplate" { Context "invoked without wrappers" { It "doesn't invoke EPS on files without .eps1.sql extension" { Invoke-SqlTemplate -Path ".\Tests\Files\NotTemplate.sql" | Should -Not -Match "^\s*$" } It "processes trivial template files and trims trailing whitespace" { Invoke-SqlTemplate -Path ".\Tests\Files\Trivial.eps1.sql" | Should -Be "SELECT 'abc' AS abc" } It "raises errors with nonexistent template files" { { @{Server='ORA'} | Invoke-SqlTemplate -Path '.\NonExistentTemplate.eps1.sql' } | Should -Throw } It -Skip "can handle null binding" { $Null | Invoke-SqlTemplate -Path ".\Tests\Files\Trivial.eps1.sql" | Should -Match "^SELECT 'abc' AS abc\s*$" } It "can process simple template files" { @{Columns=@('a','b','c')} | Invoke-SqlTemplate -Path ".\Tests\Files\Simple.eps1.sql" | Should -Match "^SELECT 'a' AS a, 'b' AS b, 'c' AS c, 4 AS x\s*$" } It "trims trailing whitespace in string templates" { Invoke-SqlTemplate -Template "SELECT 'a' `r `n `r`n `t " | Should -Be "SELECT 'a'" } } Context "invoked with cross-platform helper wrappers" { It "raises errors with nonexistent wrappers" { { @{Server='ORA'} | Invoke-SqlTemplate -Template 'abc' -Wrapper 'NonExistentWrapper' } | Should -Throw } It "processes Concatenate wrapper OK for Oracle" { @{Server='ORA'} | Invoke-SqlTemplate -Template "'a'`r`n'b'`r`n'c'" -Wrapper 'Concatenate' | Should -Be "'a' || 'b' || 'c'" } It "processes Concatenate wrapper OK for SQL Server" { @{Server='SS13'} | Invoke-SqlTemplate -Template "'a' 'b' 'c'" -Wrapper 'Concatenate' | Should -Be "'a' + 'b' + 'c'" } It "trims trailing whitespace in wrappers" { @{Server='ORA'} | Invoke-SqlTemplate -Template "'a'`r`n'b'`r`n'c'`r`n`r`n" -Wrapper 'Concatenate' | Should -Be "'a' || 'b' || 'c'" } It "processes DateDiff wrapper OK for Oracle" { @{Server='ORA'} | Invoke-SqlTemplate -Template "S`r`nE" -Wrapper 'DateDiff' | Should -Be "E - S" } It "processes DateDiff wrapper OK for SQL Server" { @{Server='SS13'} | Invoke-SqlTemplate -Template "S E" -Wrapper 'DateDiff' | Should -Be "DATEDIFF(day, S, E)" } It "processes DateToString wrapper OK for Oracle" { @{Server='ORA'; ToFormat='YYYYMMDD'} | Invoke-SqlTemplate -Template "'01/02/2003'" -Wrapper 'DateToString' | Should -Be "TO_CHAR('01/02/2003', 'YYYYMMDD')" } It "processes DateToString wrapper OK for SQL Server" { @{Server='SS13'; ToFormat='YYYYMMDD'} | Invoke-SqlTemplate -Template "'01/02/2003'" -Wrapper 'DateToString' | Should -Be "CONVERT(char(8), '01/02/2003', 112)" @{Server='SS13'; ToFormat='MM/DD/YYYY'} | Invoke-SqlTemplate -Template "'01/02/2003'" -Wrapper 'DateToString' | Should -Be "CONVERT(char(10), '01/02/2003', 101)" } It "processes QuotedId wrapper OK for Oracle" { @{Server='ORA'} | Invoke-SqlTemplate -Template "abcd" -Wrapper 'QuotedId' | Should -Be '"abcd"' } It "processes QuotedId wrapper OK for SQL Server" { @{Server='SS13'} | Invoke-SqlTemplate -Template "abcd" -Wrapper 'QuotedId' | Should -Be '[abcd]' } It "processes Sanitize wrapper OK for Oracle" { @{Server='ORA'} | Invoke-SqlTemplate -Template "'abcd'" -Wrapper 'Sanitize' | Should -Be "REGEXP_REPLACE('abcd', '[[:cntrl:]]')" } It "processes Sanitize wrapper OK for SQL Server" { @{Server='SS13'} | Invoke-SqlTemplate -Template "'abcd'" -Wrapper 'Sanitize' | Should -Be "STRING_ESCAPE('abcd', 'json')" } It "processes SelectSingle wrapper OK for Oracle" { @{Server='ORA'} | Invoke-SqlTemplate -Template "'abcd' AS x" -Wrapper 'SelectSingle' | Should -Be "SELECT 'abcd' AS x FROM dual" } It "processes SelectSingle wrapper OK for SQL Server" { @{Server='SS13'} | Invoke-SqlTemplate -Template "'abcd' AS x" -Wrapper 'SelectSingle' | Should -Be "SELECT 'abcd' AS x" } It "processes Aggregate wrapper OK for Oracle" { @{Server='ORA'; Separator='; '; Order='y DESC'} | Invoke-SqlTemplate -Template "y" -Wrapper 'Aggregate' | Should -Be "LISTAGG(y, '; ') WITHIN GROUP (ORDER BY y DESC)" } It "processes Aggregate wrapper OK for SQL Server 13" { $Body = @{Server='SS13'; Separator='; '; Order='y DESC'; GroupField='t.y'; Filter='y > 3'} | Invoke-SqlTemplate -Template "y" -Wrapper 'Aggregate' $Body | Should -Match "N'; ' \+ y" $Body | Should -Match "WHERE t.y = t2.y\s*AND y > 3" $Body | Should -Match "ORDER BY y DESC" $Body | Should -Match "1, 2, N''" } It "processes StringLength wrapper OK for Oracle" { @{Server='ORA'} | Invoke-SqlTemplate -Template "'abcd'" -Wrapper 'StringLength' | Should -Be "LENGTH('abcd')" } It "processes StringLength wrapper OK for SQL Server" { @{Server='SS13'} | Invoke-SqlTemplate -Template "'abcd'" -Wrapper 'StringLength' | Should -Be "LEN('abcd')" } It "processes Substring wrapper OK for Oracle" { @{Server='ORA'; Position=3; Length=5} | Invoke-SqlTemplate -Template "'abcdefghijk'" -Wrapper 'Substring' | Should -Be "SUBSTR('abcdefghijk', 3, 5)" } It "processes Substring wrapper OK for SQL Server" { @{Server='SS13'; Position=3; Length=5} | Invoke-SqlTemplate -Template "'abcdefghijk'" -Wrapper 'Substring' | Should -Be "SUBSTRING('abcdefghijk', 3, 5)" } It "defaults position to 1 in Substring wraper" { @{Server='SS13'; Length=5} | Invoke-SqlTemplate -Template "'abcdefghijk'" -Wrapper 'Substring' | Should -Be "SUBSTRING('abcdefghijk', 1, 5)" } It "defaults length to length of string - position + 1 in Substring wraper" { @{Server='SS13'; Position=5} | Invoke-SqlTemplate -Template "'abcdefghijk'" -Wrapper 'Substring' | Should -Be "SUBSTRING('abcdefghijk', 5, LEN('abcdefghijk')-(5)+1)" } It "processes SubstringIndex wrapper OK for Oracle" { @{Server='ORA'; Substring="'def'"} | Invoke-SqlTemplate -Template "'abcdefghijk'" -Wrapper 'SubstringIndex' | Should -Be "INSTR('abcdefghijk', 'def')" } It "processes SubstringIndex wrapper OK for SQL Server" { @{Server='SS13'; Substring="'def'"} | Invoke-SqlTemplate -Template "'abcdefghijk'" -Wrapper 'SubstringIndex' | Should -Be "CHARINDEX('def', 'abcdefghijk')" } It "processes SystemDate wrapper OK for Oracle" { @{Server='ORA'} | Invoke-SqlTemplate -Wrapper 'SystemDate' | Should -Be "SYSDATE" } It "processes SystemDate wrapper OK for SQL Server" { @{Server='SS13'} | Invoke-SqlTemplate -Wrapper 'SystemDate' | Should -Be "CAST(SYSDATETIME() AS date)" } It "processes StringToDate wrapper OK for Oracle" { @{Server='ORA'; FromFormat='MM/DD/YYYY'} | Invoke-SqlTemplate -Template "'01/02/2003'" -Wrapper 'StringToDate' | Should -Be "TO_DATE('01/02/2003', 'MM/DD/YYYY')" } It "processes StringToDate wrapper OK for SQL Server" { @{Server='SS13'; FromFormat='MM/DD/YYYY'} | Invoke-SqlTemplate -Template "'01/02/2003'" -Wrapper 'StringToDate' | Should -Be "CONVERT(DATETIME, '01/02/2003', 101)" } It "processes string to date to string in a different format" { @{Server='SS13'; FromFormat='MM/DD/YYYY'; ToFormat='YYYYMMDD'} | Invoke-SqlTemplate -Template "'01/02/2003'" -Wrapper 'StringToDate','DateToString' | Should -Be "CONVERT(char(8), CONVERT(DATETIME, '01/02/2003', 101), 112)" } It "processes StringToInt wrapper OK for Oracle" { @{Server='ORA'} | Invoke-SqlTemplate -Template "'42'" -Wrapper 'StringToInt' | Should -Be "TO_NUMBER('42')" } It "processes StringToInt wrapper OK for SQL Server" { @{Server='SS13'} | Invoke-SqlTemplate -Template "'42'" -Wrapper 'StringToInt' | Should -Be "CAST('42' AS int)" } It "processes DateToString + StringToInt combination OK for Oracle" { @{Server='ORA'; ToFormat='YYYYMMDD'} | Invoke-SqlTemplate -Template "'01/02/2003'" -Wrapper 'DateToString','StringToInt' | Should -Be "TO_NUMBER(TO_CHAR('01/02/2003', 'YYYYMMDD'))" } It "processes DateToString + StringToInt combination OK for SQL Server" { @{Server='SS13'; ToFormat='YYYYMMDD'} | Invoke-SqlTemplate -Template "'01/02/2003'" -Wrapper 'DateToString','StringToInt' | Should -Be "CAST(CONVERT(char(8), '01/02/2003', 112) AS int)" } It "scrubs single values from aggregated strings OK" { @{Separator='; '; RemoveList=@("'a'")} | Invoke-SqlTemplate -Template 's' -Wrapper 'RemoveAggregated' | Should -Be "REPLACE(REPLACE(REPLACE(s, '; ' + 'a', ''), 'a' + '; ', ''), 'a', '')" } It "scrubs multiple values from aggregated strings OK" { @{Separator='; '; RemoveList=@("'abcd'",'efg')} | Invoke-SqlTemplate -Template 'string' -Wrapper 'RemoveAggregated' | Should -Be ( "REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(string, '; ' + 'abcd', ''), '; ' + efg, ''), " + "'abcd' + '; ', ''), efg + '; ', ''), 'abcd', ''), efg, '')") } It "processes example file OK for Oracle" { @{Server='ORA'} | Invoke-SqlTemplate -Path .\Tests\Files\example.eps1.sql | Should -Be ` "SELECT Customers.key, TO_NUMBER(TO_CHAR(Purchases.date, 'YYYYMMDD')) AS PurchaseDateKey, SUBSTR(Purchases.fullname, 1, INSTR(Purchases.fullname, ' ')) AS PurchaseCode FROM Purchases LEFT JOIN Customers ON Purchases.customer_key = Customers.key" } It "processes example file OK for SQL Server" { @{Server='SS13'} | Invoke-SqlTemplate -Path .\Tests\Files\example.eps1.sql | Should -Be ` "SELECT Customers.key, CAST(CONVERT(char(8), Purchases.date, 112) AS int) AS PurchaseDateKey, SUBSTRING(Purchases.fullname, 1, CHARINDEX(' ', Purchases.fullname)) AS PurchaseCode FROM Purchases LEFT JOIN Customers ON Purchases.customer_key = Customers.key" } } It "processes CTEs and inline queries OK in a file" { Invoke-SqlTemplate -Path .\Tests\Files\MainQuery.eps1.sql | Should -Be ` "WITH subquery1 AS ( SELECT 'This is the first subquery' AS var1 ), subquery2 AS ( SELECT 'This is the second subquery' AS var2 ) SELECT subquery1.var1, subquery2.var2, subquery3.var3 FROM subquery1 LEFT JOIN subquery2 ON 2=2 LEFT JOIN ( SELECT 'This is the third subquery' AS var3 ) subquery3 ON 3=3" } Context "invoked with feature wrappers" { It "processes the CTE wrapper OK" { Invoke-SqlTemplate -Path ".\Tests\Files\Trivial.eps1.sql" -Wrapper 'CTE' | Should -Be "Trivial AS (`r`n SELECT 'abc' AS abc`r`n)" } It "processes the inline wrapper OK" { Invoke-SqlTemplate -Path ".\Tests\Files\Trivial.eps1.sql" -Wrapper 'Inline' | Should -Be "(`r`n SELECT 'abc' AS abc`r`n) Trivial" } It "processes simple JUnit wrapper OK for SQL Server" { $Body = @{Server='SS13'} | Invoke-SqlTemplate -Path ".\Tests\Files\Trivial.eps1.sql" -Wrapper 'JUnit' $Body | Should -Match "xml" $Body | Should -Match "AS test_result\s+UNION ALL" } It "processes JUnit wrapper with CTES OK for SQL Server" { $Body = @{Server='SS13'} | Invoke-SqlTemplate -Path ".\Tests\Files\Complex.eps1.sql" -Wrapper 'JUnit' $Body | Should -Match "\)`r`n\s*SELECT '<\?xml" } It "processes individual JUnit wrapper OK for SQL Server" { @{Server='SS13'; TestName='my test'} | Invoke-SqlTemplate -Template 'x=1' -Wrapper 'JUnitTest' | Should -Be ("'<testcase name=`"my test`">' + CASE WHEN x=1 THEN '' ELSE '<failure/>' END + " + "'</testcase>' AS test_result") } It "processes individual NUnit wrapper OK for SQL Server" { @{Server='SS13'; TableName='dbo.my_table'; TestName='my test. with.periods'} | Invoke-SqlTemplate -Template 'x=1' -Wrapper 'NUnitTest' | Should -Be ("'<test-case name=`"sqltest.dbomy_table.my test withperiods`" executed=`"True`" success=`"' + " + "CASE WHEN x=1 THEN 'True' ELSE 'False' END + '`"/>' AS test_result") } It "processes nonsensical Mocha wrapper OK for SQL Server" { @{Server='SS13'} | Invoke-SqlTemplate -Template 'x' -Wrapper 'Mocha' } It "strips trailing comma in Mocha wrapper OK for SQL Server" { $Body = @{Server='SS13'} | Invoke-SqlTemplate -Template ` "SELECT 'x,' AS test_result`nUNION ALL`nSELECT 'y,' AS test_result" -Wrapper 'Mocha' $Body | Should -Match "SELECT 'x,' AS test_result" $Body | Should -Match "SELECT 'y' AS test_result" } It "works with nested wrappers" { $Body = @{Server='SS13'; ProcedurePrefix='dbo.'; ViewPrefix='dbo.' } | Invoke-SqlTemplate -Path ".\Tests\Files\Trivial.eps1.sql" -Wrapper @('View','Procedure') $Body | Should -Match "^CREATE OR ALTER PROCEDURE dbo.Trivial AS\s*BEGIN" $Body | Should -Match "CREATE OR ALTER VIEW dbo.Trivial AS" $Body | Should -Match "SELECT" } It "gives correct basenames to single-extension files" { Invoke-SqlTemplate -Path ".\Tests\Files\NotTemplate.sql" -Wrapper 'Inline' | Should -match 'NotTemplate$' } It "gives correct basenames to multi-extension files" { Invoke-SqlTemplate -Path ".\Tests\Files\Trivial.eps1.sql" -Wrapper 'Inline' | Should -match 'Trivial$' } It "materializes correctly in SQL Server" { $Body = @{Server='SS13'; TablePrefix='dbo.'} | Invoke-SqlTemplate -Path ".\Tests\Files\Complex.eps1.sql" ` -Wrapper 'Materialize' $Body | Should -Match 'DROP TABLE dbo\.Complex[^\r\n]*\r\n\s*WITH' $Body | Should -Match 'INTO dbo\.Complex\r\n\s*FROM NonRootFruits' $Body | Should -Not -Match 'INTO [^\r\n]*\r\n\s*INTO' } It "inserts DROP TABLEs, INTOs, and variable declarations as expected in nested materialization" { $Body = @{Server='SS13'; TablePrefix='dbo.'} | Invoke-SqlTemplate -Path ` ".\Tests\Files\MaterializeSelect.eps1.sql" ` -Wrapper 'Materialize' $Body | Should -Match 'DROP TABLE dbo\.Complex[^\r\n]*\r\n\s*WITH' $Body | Should -Match 'INTO dbo\.Complex\r\n\s*FROM NonRootFruits' $Body | Should -Not -Match 'INTO [^\r\n]*\r\n\s*INTO' $Body | Should -Not -Match 'UNION ALL\r\nIF OBJECT_ID' } It "conditionally executes correctly in SQL Server" { $Body = @{Server='SS13'} | Invoke-SqlTemplate -Template 'dbo.sp_stuff' -Wrapper 'ExecuteIfExists' $Body | Should -Be "IF OBJECT_ID('dbo.sp_stuff', 'P') IS NOT NULL`r`n EXEC dbo.sp_stuff;" } It "appends (NOLOCK) hints correctly in SQL Server" { @{Server='SS13'} | Invoke-SqlTemplate -Template '' -Wrapper 'NoLock' | Should -Be '' @{Server='SS13'} | Invoke-SqlTemplate -Template 'a.b' -Wrapper 'NoLock' | Should -Be 'a.b' @{Server='SS13'; AppendNoLock='DB_NAME.dbo'} | Invoke-SqlTemplate -Template ` 'SELECT * FROM DB_NAME.dbo.table_name' -Wrapper 'NoLock' | Should -Be 'SELECT * FROM DB_NAME.dbo.table_name (NOLOCK)' @{Server='SS13'; AppendNoLock='DB_NAME.dbo'} | Invoke-SqlTemplate -Template ` 'SELECT * FROM DB_NAME.dbo.table_name WHERE x=1' -Wrapper 'NoLock' | Should -Be 'SELECT * FROM DB_NAME.dbo.table_name (NOLOCK) WHERE x=1' @{Server='SS13'; AppendNoLock='DB_NAME.dbo'} | Invoke-SqlTemplate -Template ` 'WHERE DB_NAME.dbo.table_name.column = 1' -Wrapper 'NoLock' | Should -Be 'WHERE DB_NAME.dbo.table_name.column = 1' @{Server='SS13'; AppendNoLock=@('DB_NAME.dbo','DB2.sch')} | Invoke-SqlTemplate -Template ` "SELECT`n*`nFROM`nDB_NAME.dbo.table_name`nLEFT JOIN DB2.sch.t2 ON 1=1" -Wrapper 'NoLock' | Should -Be "SELECT`n*`nFROM`nDB_NAME.dbo.table_name (NOLOCK)`nLEFT JOIN DB2.sch.t2 (NOLOCK) ON 1=1" } } } |