SQLiteModule.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
function Invoke-SQLiteQuery {
  <#
      .SYNOPSIS
      With "Invoke-SQLiteQuery" you can invoke a SQLite query to your SQLite database.
 
      .DESCRIPTION
      Add a more complete description of what the function does.
 
      .PARAMETER Database
      Path to your SQLite database -Database
 
      .PARAMETER Query
      Your SQLite Quer -Query
 
      .EXAMPLE
      Invoke-SQLiteQuery -Database ":memory:" -Query "SELECT 35 + 7;"
      Using an In-Memory database
       
      .EXAMPLE
      Invoke-SQLiteQuery -Database "C:\Users\Public\Downloads\Chinook_Sqlite.sqlite" -Query "SELECT AlbumId, Title, ArtistId FROM Album ORDER BY title LIMIT 10;" | ForEach-Object { $_ -join ','} | Out-File .\myfile.csv
      Save result of a query to a CSV file
 
      .EXAMPLE
      Invoke-SQLiteQuery -Database "C:\Users\Public\Downloads\Chinook_Sqlite.sqlite" -Query "SELECT sql FROM sqlite_master WHERE type = 'table'"
      Show all columns in a SQLite table
 
      .EXAMPLE
      Invoke-SQLiteQuery -Database "$HOME/SampleDB.sqlite" -Query (Get-Content "$HOME/SampleDB.sql" | Out-String)
      Invoke a SQLite query from a SQL file
 
      .NOTES
      Module for my PSConfEU 2019 talk about PowerShell and SQLite
 
      .LINK
      https://github.com/datenteiler/SQLiteModule
 
  #>


  param
  (
    [String]
    [Parameter(
        Mandatory, 
        ValueFromPipeline,
        ValueFromPipelineByPropertyName,
        HelpMessage='Path to your database')
    ]
    [Alias('FullName', 'Name', 'FilePath')]
    $Database,
  
    [String]
    [Parameter(
        Mandatory, 
        ValueFromPipeline, 
        HelpMessage='Your SQL query')]
    $Query,
    
    [hashtable]
    [Parameter(
        ValueFromPipeline
    )]
    $SqlParameters 
  )
  
    #region BEGIN
    begin {
        $SystemDataSQLiteDll = Join-Path -Path $PSScriptRoot -ChildPath 'System.Data.SQLite.dll'
        
        if (!(Test-Path -Path (Join-Path -Path $PSScriptRoot -ChildPath 'SQLite.Interop.dll')))
        {
          if ($IsLinux) {
            if ($(uname -m) -like 'x86_64')
            {
              $SQLiteInteropdllLin = Join-Path -Path $PSScriptRoot -ChildPath 'SQLite.Interop.dll_lin'
              $SQLiteInteropdll =  Join-Path -Path $PSScriptRoot -ChildPath 'SQLite.Interop.dll'

              Copy-Item -Path $SQLiteInteropdllLin -Destination $SQLiteInteropdll -Force
            }
            else
            {
              $SQLiteInteropdllLin = Join-Path -Path $PSScriptRoot -ChildPath 'SQLite.Interop.dll_arm'
              $SQLiteInteropdll =  Join-Path -Path $PSScriptRoot -ChildPath 'SQLite.Interop.dll'

              Copy-Item -Path $SQLiteInteropdllLin -Destination $SQLiteInteropdll -Force
            }
            
          }
          elseif ($IsMacOS) {
            $SQLiteInteropdllOsx = Join-Path -Path $PSScriptRoot -ChildPath 'SQLite.Interop.dll_osx'
            $SQLiteInteropdll =  Join-Path -Path $PSScriptRoot -ChildPath 'SQLite.Interop.dll'

            Copy-Item -Path $SQLiteInteropdllOsx -Destination $SQLiteInteropdll -Force
          }
          else {
            $SQLiteInteropdllX64 = Join-Path -Path $PSScriptRoot -ChildPath 'SQLite.Interop.dll_x64'
            $SQLiteInteropdll =  Join-Path -Path $PSScriptRoot -ChildPath 'SQLite.Interop.dll'

            Copy-Item -Path $SQLiteInteropdllX64 -Destination $SQLiteInteropdll -Force
          }
        }
        Add-Type -Path $SystemDataSQLiteDll
    }
    #endregion
    
    #region PROCESS
    process {
        $con = New-Object -TypeName System.Data.SQLite.SQLiteConnection
        $con.ConnectionString = (('Data Source={0}' -f $Database))
        $con.Open()
        $sql = $con.CreateCommand()
        $sql.CommandText = $Query
        
        if ($SqlParameters -ne $null)
        {
          $SqlParameters.GetEnumerator() |
          ForEach-Object {
            if ($_.Value -ne $null)
            {
              if($_.Value -is [datetime]) { $_.Value = $_.Value.ToString("yyyy-MM-dd HH:mm:ss") }
                $sql.Parameters.AddWithValue("@$($_.Key)", $_.Value)
            }
            else
            {
              $sql.Parameters.AddWithValue("@$($_.Key)", [DBNull]::Value)        
            }
            
            
          }
        }
        
        
        $reader = $sql.ExecuteReader()
                
        while ($reader.Read())
        {
          
          $values = @()
          for ([int]$i = 0; $i -lt $reader.FieldCount; $i++) 
          {
            $values += $reader.GetValue($i)           
          }
          $output = @()
          $output += , $values  # append an array to an array
                                # the comma operator makes an array
                                # with a single item and avoids unrolling
          $output
        }      
    }
    #endregion

    #region END
    end {
        $reader.Close()
        $sql.Dispose()
        $con.Close()
    }
    #endregion
}

Export-ModuleMember -Function 'Invoke-SQLiteQuery'