PowerShell with SQLite3 サンプル集


自分用のメモとして。
PowerShellよりSQLite3を呼び出す関数を作成し、PowerShellを機能強化してみる。
内容は随時追加予定。

## 事前準備

１．SQLite3のインストール
 ※詳細は[「SQLite 3.8.7.1のダウンロードとインストール」](http://www.dbonline.jp/sqliteinstall/install/index1.html)を参考
　ちなみに本記事では、SQLite3は「version 3.9.2」を使用

２．sqlite3.exeの格納フォルダを環境変数PATHに登録

## ヘルパー関数
【InvokeSQLite3Script関数（自作関数）】
・SQLite3スクリプトを実行する
・実行はファイル経由（コマンド直接実行でINSERTすると文字化けした為）
・後述のサンプルで使う為、宣言しておく必要がある

```ps1:関数定義
# string -> string
filter InvokeSQLite3Script($DatabasePath = ''){
  $readfile   = [System.IO.Path]::GetTempFileName()
  $outputfile = [System.IO.Path]::GetTempFileName()
  ;[System.IO.File]::WriteAllText($readfile,$_) #UTF-8

@"
.mode csv
.header on
.output $($outputfile.replace('\','/'))
.read $($readfile.replace('\','/'))
"@ | sqlite3 $DatabasePath

  ;[System.IO.File]::ReadAllText($outputfile) #UTF-8
  del $readfile
  del $outputfile
}
```

```ps1:使用例
# 例１：test.dbにて実行
$testdata = 'temp\test.db'
"create table test(id,name);"           | InvokeSQLite3Script $testdata | Out-Null
"insert into test values(1,'太郎');"    | InvokeSQLite3Script $testdata | Out-Null
"insert into test values(2,'花子');"    | InvokeSQLite3Script $testdata | Out-Null
"insert into test values(10,'ジョン');" | InvokeSQLite3Script $testdata | Out-Null
"select * from test where id < 10;"     | InvokeSQLite3Script $testdata

# -> id,name
#    1,"太郎"
#    2,"花子"

# 例２：オンメモリで実行
@"
create table test(id,name);
insert into test values(1,'太郎さん');
insert into test values(2,'花子さん');
insert into test values(10,'ジョンさん');
select * from test where id < 10;
"@ | InvokeSQLite3Script

# -> id,name
#    1,"太郎さん"
#    2,"花子さん"
```


【SplitLine関数（自作関数）】
・行を分割し配列で返す
・改行コードがCR+LF(Windows)とLF(Linux)の両方の場合を考慮
・Firstスイッチ：1行目とそれ以降で分ける
・他のスイッチは、今後、必要に応じて追加する予定
・後述のサンプルで使う為、宣言しておく必要がある

```ps1:関数定義
# string -> string,string
filter SplitLine{
  param(
    [switch]$First
  )

  if($First.IsPresent){
    $_.Substring(0,$_.IndexOfAny("`r`n")),$_.Substring($_.IndexOf("`n") + 1)
  }
}
```

```ps1:使用例
$head,$tail = [System.IO.File]::ReadAllText((Resolve-Path filelist.csv)) | SplitLine -First

$head
# -> "Name","LastWriteTime","Extension"

$tail
# -> "cmd","2015/11/16 0:21:02",""
#    "html","2015/11/08 8:57:54",""
#    "php","2015/11/14 21:32:57",""
#    "powershell","2015/11/23 14:52:29",""
#    "svn","2015/11/08 12:27:15",""
#    "vba","2015/11/18 22:51:29",""
#    "wsh","2015/11/17 22:20:53",""
#    "data_win.csv","2015/11/23 1:26:15",".csv"
#    "filelist.csv","2015/11/23 17:53:32",".csv"
#    "utf8text.txt","2015/11/23 13:47:40",".txt"
```

```txt:テストデータ（filelist.csv）
"Name","LastWriteTime","Extension"
"cmd","2015/11/16 0:21:02",""
"html","2015/11/08 8:57:54",""
"php","2015/11/14 21:32:57",""
"powershell","2015/11/23 14:52:29",""
"svn","2015/11/08 12:27:15",""
"vba","2015/11/18 22:51:29",""
"wsh","2015/11/17 22:20:53",""
"data_win.csv","2015/11/23 1:26:15",".csv"
"filelist.csv","2015/11/23 17:53:32",".csv"
"utf8text.txt","2015/11/23 13:47:40",".txt"
```

【MeasureCsv関数（自作関数）】
・CSV形式の文字列から、行数や列数の情報を取得する
・「"」（ダブルクオーテーション）で囲まれている「,」（カンマ）や改行コード等も考慮
・列数は1行目の列数を取得（処理速度を優先する為）
・後述のサンプルで使う為、宣言しておく必要がある

```ps1:関数定義
# string -> object
filter MeasureCsv{
  $rows = ($_.Trim() -replace '"(([^"]|"")*)"','"X"') -split "`n"
  @{
    RowsCount    = $rows.Length;
    ColumnsCount = ($rows[0] -split ',').Length;
  }
}
```

```ps1:使用例
$testdata = 'data\KEN_ALL.CSV'
$encoding = [System.Text.Encoding]::GetEncoding("shift_jis")
;[System.IO.File]::ReadAllText((Resolve-Path $testdata),$encoding) | MeasureCsv
# -> Name                           Value
#    ----                           -----
#    ColumnsCount                   15
#    RowsCount                      123864

$testdata = 'data\KEN_ALL_dummy.CSV'
$encoding = [System.Text.Encoding]::GetEncoding("shift_jis")
;[System.IO.File]::ReadAllText((Resolve-Path $testdata),$encoding) | MeasureCsv
# -> Name                           Value
#    ----                           -----
#    ColumnsCount                   15
#    RowsCount                      4
```

テストデータその１
（[日本郵便株式会社のサイト](http://www.post.japanpost.jp/zipcode/dl/kogaki/zip/ken_all.zip)より入手）

```txt:KEN_ALL.CSV
01101,"060  ","0600000","ﾎｯｶｲﾄﾞｳ","ｻｯﾎﾟﾛｼﾁｭｳｵｳｸ","ｲｶﾆｹｲｻｲｶﾞﾅｲﾊﾞｱｲ","北海道","札幌市中央区","以下に掲載がない場合",0,0,0,0,0,0
01101,"064  ","0640941","ﾎｯｶｲﾄﾞｳ","ｻｯﾎﾟﾛｼﾁｭｳｵｳｸ","ｱｻﾋｶﾞｵｶ","北海道","札幌市中央区","旭ケ丘",0,0,1,0,0,0

～　中略　～

47382,"90718","9071800","ｵｷﾅﾜｹﾝ","ﾔｴﾔﾏｸﾞﾝﾖﾅｸﾞﾆﾁｮｳ","ｲｶﾆｹｲｻｲｶﾞﾅｲﾊﾞｱｲ","沖縄県","八重山郡与那国町","以下に掲載がない場合",0,0,0,0,0,0
47382,"90718","9071801","ｵｷﾅﾜｹﾝ","ﾔｴﾔﾏｸﾞﾝﾖﾅｸﾞﾆﾁｮｳ","ﾖﾅｸﾞﾆ","沖縄県","八重山郡与那国町","与那国",0,0,0,0,0,0
```

テストデータその２
（その１より抜粋し、「"」（ダブルクオーテーション）内に「,」（カンマ）や改行コード等を混ぜてみた）

```txt:KEN_ALL_dummy.CSV
01101,"060  ","0600000","ﾎｯｶｲﾄﾞｳ","ｻｯﾎﾟﾛｼﾁｭｳｵｳｸ","ｲｶﾆｹｲｻｲｶﾞﾅｲﾊﾞｱｲ","北海道","札幌市中央区","以下に掲載がない場合",0,0,0,0,0,0
DUMMY1,"060  ","0600000","ﾎｯｶｲﾄﾞｳ","ｻｯﾎﾟﾛｼﾁｭｳ
ｵｳｸ","ｲｶﾆｹｲｻｲｶﾞ
ﾅｲﾊﾞｱｲ","北海道""","札幌市,中央区","以下に掲載がない場合",0,0,0,0,0,0
DUMMY2,"060  ","0600000","ﾎｯｶｲﾄﾞｳ","ｻｯﾎ
ﾟﾛｼﾁｭｳｵｳｸ","ｲｶﾆｹｲｻｲｶﾞﾅｲﾊﾞｱｲ","北海道","札幌市中央区","以下に掲載がない場合",0,0,0,0,0,0
01101,"060  ","0600031","ﾎｯｶｲﾄﾞｳ","ｻｯﾎﾟﾛｼﾁｭｳｵｳｸ","ｷﾀ1ｼﾞｮｳﾋｶﾞｼ","北海道","札幌市中央区","北一条東",0,0,1,0,0,0
```


## ファイル操作系

【ReadCsv関数（自作関数）】
ReadCsv関数は少し仕様が濃いので、詳しくは後日、別記事にて解説予定。
今回は一部のみ解説。

・CSVファイルをSQLite3データベース（DatabasePathで指定）に取り込む
・DatabasePath未指定の場合は、オンメモリ実行
※CSV取り込み後の実行クエリをQuery引数にて指定することで結果取得可能

・CSVファイルは複数指定可能
・テーブル名は自動取得（ファイル名の「.」（ドット）より前部分）
・列名については以下のように取得／生成される
　・ヘッダありモード（デフォルト）→ CSVファイルの1行目より取得
　・ヘッダなしモード → 自動採番（_1,_2 ... ）

・CSV取り込み後の実行クエリをQuery引数にて複数指定可能
・CSVファイルの文字コードを指定可能（ファイル毎に指定可能）
　省略時はUTF-8が指定される

```ps1:関数定義
# string[] -> string
function ReadCsv{
  param(
    $DatabasePath = '', #単一指定
    $Query        = '', #複数指定可能
    $Encoding     = '', #複数指定可能
    [switch]$NoHeader
  )
  $defaultEncoding = 'utf-8'
  $tempFileList    = @()
  $tableNameList   = @()
  $createSql       = ''
  $importCmd       = ''

  # SQL文生成
  $csv_i = 0
  $input | foreach{
    # CSVファイルの文字コードの指定
    $objEncoding = `
      switch($Encoding.GetType().Name){
        # 個別指定
        'Object[]' {
          if($csv_i -lt $Encoding.Length){
            [System.Text.Encoding]::GetEncoding($Encoding[$csv_i])
          } else {
            [System.Text.Encoding]::GetEncoding($defaultEncoding)
          }

        # 一括指定
        } 'String' {
          if($Encoding -eq ''){
            [System.Text.Encoding]::GetEncoding($defaultEncoding)
          } else {
            [System.Text.Encoding]::GetEncoding($Encoding)
          }
        }
      }

    # .NetクラスのファイルI/Oではフルパスの指定が必要
    $absolutePath = Resolve-Path $_

    $tableName    = (Split-Path $_ -Leaf).split('.')[0]

    # ヘッダなしモード
    if($NoHeader.IsPresent){
      # CSVファイルの読み込み
      $data     = [System.IO.File]::ReadAllText($absolutePath,$objEncoding)

      # 列名の自動採番
      $col_n    = ($data | MeasureCsv).ColumnsCount
      $colNames = (1..$col_n | foreach{'_' + $_}) -join ','

    # ヘッダありモード
    } else {
      # CSVファイルの内容を列名とデータに分割
      $colNames,$data = [System.IO.File]::ReadAllText($absolutePath,$objEncoding) | SplitLine -First
    }

    # インポートファイル生成
    $importfile    = [System.IO.Path]::GetTempFileName()
    $tempFileList += $importfile
    [System.IO.File]::WriteAllText($importFile,$data) #UTF-8

    # CREATE文生成
    # テーブル名が被った場合は先勝ち（初めのCSVの定義を優先する）
    if(!$tableNameList.Contains($tableName)){
      $tableNameList += $tableName
      $createSql     += "CREATE TABLE $tableName ($($colNames.Replace('"','')));" + "`n"
    }

    # importコマンド生成
    $importCmd += ".import $($importFile.replace('\','/')) $tableName" + "`n"

    $csv_i++
  }

  # SQLite3の実行
@"
$createSql
$importCmd
$($Query -join "`n")
"@ | InvokeSQLite3Script $DatabasePath

  # 一時ファイルの削除
  $tempFileList | foreach{del $_}
}
```

```ps1:使用例
$testdata1_1 = 'data\KEN_ALL_dummy.CSV'
$testdata1_2 = 'data\KEN_ALL_dummy_euc.CSV'
$testdata2 = 'temp\test.db'

#実行
$result = & {
  $testdata1_1,$testdata1_2 | ReadCsv -DatabasePath $testdata2 `
                              -NoHeader `
                              -Encoding @('shift_jis','euc-jp') `
                              -Query @'
select 
  euc._1, 
  euc._3, 
  euc._5 as euc_5, 
  euc._7 as euc_7,
  sjis._5 as sjis_5, 
  sjis._7 as sjis_7
from KEN_ALL_dummy_euc as euc,
     KEN_ALL_dummy as sjis
where euc._1 = sjis._1
  and euc._3 = sjis._3
;
'@
}

#確認
$result | ConvertFrom-Csv | Format-List
# -> _1     : 01101
#    _3     : 0600000
#    euc_5  : ｻｯﾎﾟﾛｼﾁｭｳｵｳｸ
#    euc_7  : 北海道
#    sjis_5 : ｻｯﾎﾟﾛｼﾁｭｳｵｳｸ
#    sjis_7 : 北海道
#    
#    _1     : DUMMY1
#    _3     : 0600000
#    euc_5  : ｻｯﾎﾟﾛｼﾁｭｳ
#             ｵｳｸ
#    euc_7  : 北海道"
#    sjis_5 : ｻｯﾎﾟﾛｼﾁｭｳ
#             ｵｳｸ
#    sjis_7 : 北海道"
#    
#    _1     : DUMMY2
#    _3     : 0600000
#    euc_5  : ｻｯﾎ
#             ﾟﾛｼﾁｭｳｵｳｸ
#    euc_7  : 北海道
#    sjis_5 : ｻｯﾎ
#             ﾟﾛｼﾁｭｳｵｳｸ
#    sjis_7 : 北海道
#    
#    _1     : 01101
#    _3     : 0600031
#    euc_5  : ｻｯﾎﾟﾛｼﾁｭｳｵｳｸ
#    euc_7  : 北海道
#    sjis_5 : ｻｯﾎﾟﾛｼﾁｭｳｵｳｸ
#    sjis_7 : 北海道
```

テストデータ

```txt:KEN_ALL_dummy.CSV（文字コード：Shift_JIS）
01101,"060  ","0600000","ﾎｯｶｲﾄﾞｳ","ｻｯﾎﾟﾛｼﾁｭｳｵｳｸ","ｲｶﾆｹｲｻｲｶﾞﾅｲﾊﾞｱｲ","北海道","札幌市中央区","以下に掲載がない場合",0,0,0,0,0,0
DUMMY1,"060  ","0600000","ﾎｯｶｲﾄﾞｳ","ｻｯﾎﾟﾛｼﾁｭｳ
ｵｳｸ","ｲｶﾆｹｲｻｲｶﾞ
ﾅｲﾊﾞｱｲ","北海道""","札幌市,中央区","以下に掲載がない場合",0,0,0,0,0,0
DUMMY2,"060  ","0600000","ﾎｯｶｲﾄﾞｳ","ｻｯﾎ
ﾟﾛｼﾁｭｳｵｳｸ","ｲｶﾆｹｲｻｲｶﾞﾅｲﾊﾞｱｲ","北海道","札幌市中央区","以下に掲載がない場合",0,0,0,0,0,0
01101,"060  ","0600031","ﾎｯｶｲﾄﾞｳ","ｻｯﾎﾟﾛｼﾁｭｳｵｳｸ","ｷﾀ1ｼﾞｮｳﾋｶﾞｼ","北海道","札幌市中央区","北一条東",0,0,1,0,0,0
```

```txt:KEN_ALL_dummy_euc.CSV（文字コード：EUC_JP）
01101,"060  ","0600000","ﾎｯｶｲﾄﾞｳ","ｻｯﾎﾟﾛｼﾁｭｳｵｳｸ","ｲｶﾆｹｲｻｲｶﾞﾅｲﾊﾞｱｲ","北海道","札幌市中央区","以下に掲載がない場合",0,0,0,0,0,0
DUMMY1,"060  ","0600000","ﾎｯｶｲﾄﾞｳ","ｻｯﾎﾟﾛｼﾁｭｳ
ｵｳｸ","ｲｶﾆｹｲｻｲｶﾞ
ﾅｲﾊﾞｱｲ","北海道""","札幌市,中央区","以下に掲載がない場合",0,0,0,0,0,0
DUMMY2,"060  ","0600000","ﾎｯｶｲﾄﾞｳ","ｻｯﾎ
ﾟﾛｼﾁｭｳｵｳｸ","ｲｶﾆｹｲｻｲｶﾞﾅｲﾊﾞｱｲ","北海道","札幌市中央区","以下に掲載がない場合",0,0,0,0,0,0
01101,"060  ","0600031","ﾎｯｶｲﾄﾞｳ","ｻｯﾎﾟﾛｼﾁｭｳｵｳｸ","ｷﾀ1ｼﾞｮｳﾋｶﾞｼ","北海道","札幌市中央区","北一条東",0,0,1,0,0,0
```