SqlTemplate.psm1

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
<#
.Synopsis
    Processes a SQL Embedded PowerShell template (https://github.com/panasenco/sqltemplate).
.Description
    SqlTemplate is a templating tool based on Embedded PowerShell that aims to resolve the following frequent SQL pain
    points:
     * Reusing subqueries and CTEs across multiple queries
     * Writing queries meant to run on different SQL platforms
     * Generating many similar columns
    Read the guide and browse the source code at https://github.com/panasenco/sqltemplate
.Parameter Binding
    Hashtable containing value bindings to pass on to Invoke-EpsTemplate. There are some reserved bindings:
     - Server: The type of server to compile the query for. The server binding allows the use of platform-agnostic
           cmdlets like New-Concat, New-StringAgg, New-ToDate, and more in your EPS template files.
     - Body: The body of the nested template for wrapper templates.
     - ChildPath: The path to the nested template file for wrapper templates.
     - Basename: The nested template's name (e.g. basename of ChildPath) for wrapper templates.
.Parameter Path
    The path to the .eps1.sql template file to apply (does not modify the file, just goes to stdout).
    NOTE The file is only processed with EPS templating if the path ends in .eps1.sql!
.Parameter Template
    The string template to apply.
.Parameter Wrapper
    Array of names of standard wrapper templates (in the Wrappers directory of the SqlTemplate module) to apply, in
    order from innermost to outermost.
.Example
    Reusing subqueries and CTEs across multiple queries
    ---------------------------------------------------
    In the worlds of reporting and data warehousing, situations arise where creating views is more trouble than it's worth:
     * You may want to be able to run the same complex query on multiple different servers. Keeping those views in sync
       would become a synchronization nightmare.
     * You may have requirements to redo QC and end-user validation for each change to a reporting view, no matter how
       small. Then each change to a view that 10 reports depend on would trigger 10 rounds of QC.
     * You may want to create a report that you can send to a colleague at a different institution who's using the same
       type of system.
     * You may not have the security access to create views on the server you want to query.
    This is equivalent to static linking in the software development world.
 
    With SqlTemplate, reusing subqueries and CTEs is very easy. Suppose you have the following 3 files:
 
    **subquery1.eps1.sql**
    ```
    SELECT 'This is the first subquery' AS var1
    ```
 
    **subquery2.eps1.sql**
    ```
    SELECT 'This is the second subquery' AS var2
    ```
 
 
    **subquery3.eps1.sql**
    ```
    SELECT 'This is the third subquery' AS var3
    ```
 
    Then you can create the following file that can use them without having to copy-and-paste their contents:
 
    **mainquery.eps1.sql**
    ```
    WITH <%= Invoke-SqlTemplate -Path .\subquery1.eps1.sql -Wrapper 'CTE' %>,
    <%= Invoke-SqlTemplate -Path .\subquery2.eps1.sql -Wrapper 'CTE' %>
    SELECT
      subquery1.var1,
      subquery2.var2,
      subquery3.var3
    FROM subquery1
    LEFT JOIN subquery2 ON 2=2
    LEFT JOIN <%= Invoke-SqlTemplate -Path .\subquery3.eps1.sql -Wrapper 'Inline' %> ON 3=3
    ```
    Note that the two special wrappers 'CTE' and 'Inline' above allow you to easily include CTEs and subqueries without
    having to worry about the indentation and formatting:
 
    Invoking the template shows that it successfully included the subqueries:
    ```
    > Invoke-SqlTemplate -Path .\mainquery.eps1.sql
    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
    ```
.Example
    Writing queries meant to run on different SQL platforms
    -------------------------------------------------------
    In a data warehousing setting, you will frequently want to run the same query on data in the source system as well as
    data in the data warehouse to identify possible ETL issues. SqlTemplate allows you to write one file that will
    'compile' to the syntax of a particular SQL implementation.
 
    Here's an example that shows how to generate a substring command on Oracle and SQL Server:
    ```
    > @{Server='ORA'; Substring="'def'"} | Invoke-SqlTemplate -Template "'abcdefghijk'" -Wrapper 'SubstringIndex'
    INSTR('abcdefghijk', 'def')
    > @{Server='SS13'; Substring="'def'"} | Invoke-SqlTemplate -Template "'abcdefghijk'" -Wrapper 'SubstringIndex'
    CHARINDEX('def', 'abcdefghijk')
    ```
 
    Wrappers can be nested. Suppose you want to select the above substring as a single row. Single-row selection is
    implemented differently in [Oracle and SQL Server](https://stackoverflow.com/a/35254602/12981893):
    ```
    > @{Server='ORA'; Substring="'def'"} | Invoke-SqlTemplate -Template "'abcdefghijk'" -Wrapper 'SubstringIndex','SelectSingle'
    SELECT INSTR('abcdefghijk', 'def') FROM dual
    > @{Server='SS13'; Substring="'def'"} | Invoke-SqlTemplate -Template "'abcdefghijk'" -Wrapper 'SubstringIndex','SelectSingle'
    SELECT CHARINDEX('def', 'abcdefghijk')
    ```
 
    Finally, wrappers can of course be included as parts of larger queries using standard
    [EPS templating](https://github.com/straightdave/eps). Suppose you have a file example.eps1.sql (the extension is
    important - Invoke-SqlTemplate won't attempt template processing without it) with the following contents:
 
    **example.eps1.sql**
    ```
    SELECT
      Customers.key,
      <%= ($Binding + @{ToFormat='YYYYMMDD'}) | Invoke-SqlTemplate -Template 'Purchases.date' `
          -Wrapper 'DateToString','StringToInt' %> AS PurchaseDateKey,
      <%= ($Binding +
              @{Length=($Binding + @{Substring="' '"}) |
                  Invoke-SqlTemplate -Template 'Purchases.fullname' -Wrapper 'SubstringIndex'
              }) | Invoke-SqlTemplate -Template 'Purchases.fullname' -Wrapper 'Substring'
      %> AS PurchaseCode
    FROM Purchases
    LEFT JOIN Customers ON Purchases.customer_key = Customers.key
    ```
    Note that the special variable $Binding above is a copy of the hash table that was piped to the top-level call to
    Invoke-SqlTemplate. It must be explicitly passed to sub-templates to allow the variables to propagate.
 
    This one template file can produce different queries for different server implementations:
    ```
    > @{Server='ORA'} | Invoke-SqlTemplate -Path .\example.eps1.sql
    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
    ```
    ```
    > @{Server='SS13'} | Invoke-SqlTemplate -Path .\example.eps1.sql
    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
    ```
.Example
    Generating many similar columns
    -------------------------------
    Pivot tables are evil, incomprehensible, and impossible to optimize. With SqlTemplate you can take full advantage of
    [Embedded PowerShell](https://github.com/straightdave/eps) and generate view columns using scripting. For example:
 
    ```
    > @{Columns=@('a','b','c')} | Invoke-SqlTemplate -Template 'SELECT <% $Columns | Each { %><%= $_ %> AS <%= $_ %>, <% } %>4 AS x'
    SELECT a AS a, b AS b, c AS c, 4 AS x
    ```
#>

function Invoke-SqlTemplate {
    [CmdletBinding()]
    param (
        [Parameter(ValueFromPipeline=$true)]
        [Hashtable] $Binding = @{},
        [string] $Path,
        [string] $Template,
        [string[]] $Wrapper
    )
    
    if (-not $Path) {
        # Invoke the template
        $Body = $Binding.Clone() | Invoke-EpsTemplate -Template $Template
    } elseif ($Path -match '.*\.eps1\.sql\s*$') {
        # Invoke the template only if the extension is .eps1.sql
        $Body = $Binding.Clone() | Invoke-EpsTemplate -Path $Path
    } else {
        # Return the raw file contents if no name and the file extension is not .eps1.sql
        $Body = Get-Content -Raw -Path $Path
    }
    
    # Trim trailing whitespace
    $Body = $Body -replace '\s*$'
    
    if ($Wrapper) {
        # Create a clean copy of the binding
        $BindingCopy = $Binding.Clone()
        if ($Path -and -not $Binding.Basename) {
            $BindingCopy.Add('Basename', ((Get-Item -Path $Path).BaseName -split '\.')[0])
        }
        $BindingCopy.Remove('Body')
        $BindingCopy.Remove('ChildPath')
        $BindingCopy.Add('ChildPath', $Path)
        # Apply the wrappers in order from innermost to outermost
        $ModuleFileList = Get-Item (Get-Module -Name SqlTemplate).FileList
        foreach ($WrapperName in $Wrapper) {
            $WrapperFile = $ModuleFileList | where {$_.Basename -eq "$WrapperName.eps1"}
            if (-not $WrapperFile) {
                throw ("Can't find wrapper $WrapperName. Please ensure it's in the Wrappers directory and listed " +
                    "in FileList in SqlTemplate.psd1.")
            }
            $Body = ($BindingCopy + @{Body=$Body}) | Invoke-SqlTemplate -Path $WrapperFile
        }
    }
    
    $Body
}