PowerShellからSQLiteでファイルのコメントを管理するテーブルを作成する。

ファイルに紐づくコメントやサムネイル画像などを管理する方法を模索していて、サフィックスをつけた隠しファイルを作成したり、NTFSのADSを使ってコメントを埋め込んだりしてみました。前者は隠しファイルが意外と邪魔だったり、後者は同一ファイルシステム内でファイルの移動は問題なのですが、別のファイルシステムへの移動でコメントなどの情報が失われてしまいます。

C#「NTFSのADSでファイルにコメントをつける」

PowerShellのSet-ContentとGet-Contentでファイルにコメントをつける方法を試しました。 C#(.NET)でも、それが出来ないか調べてみましたが見つけることが出来ませんでした。仕方がないのでWindowsAPIを...

C#リストビューで画像ファイルのサムネイル表示4「サムネイルをファイルに保存」

大きなファイルのサムネイルを表示しようとすると描画まで時間がかかるので、サムネイル画像をファイルとして保存して２回目以降はそちら使うようにして高速化してみます。プロジェクトの作成 mkdir プロジェクト名 cd プロジェクト名 dotn...

今回はファイルに紐づくコメントをデータベースのテーブルに保存する方法を試してみたいと思います。

テーブルを作成する場合主キーを設定する必要がありますが、今回はファイルのパスを主キーとします。
また、管理する項目はとりあえずコメントだけにします。

久々にSQLiteを触るので過去の記事から使い方を思い出してみます。

Powershellでsqliteを試してみた話【テーブルの作成、更新、追加、削除など】

Powershellから呼び出せるライブラリは標準の.Netのものだけでも膨大にありますが、それでも別途欲しいライブラリがあったりもします。今回はsqliteを導入してみたいと思います。管理権限でpowershellを起動ファイル名を指...

基本このブログの記事は個人用のメモのつもりで書いていますが、時間が経過書いた当人が理解不能な文章になっていました。
これから文章能力が向上するとも考えにくいのですが、万人といわずとも自分ぐらいは理解できるような文章になるよう心掛けたいと思います。

閑話休題

モジュールのインポート
データベースファイルのパスを定数で定義
PowerSehllでinclude
データベースとの接続
テーブルの作成
ファイルにコメントをセット
登録されたコメントの確認
スクリプト

モジュールのインポート

SQLiteがインストールされていない環境を想定し、スクリプト内でモジュールのインポートを行います。

Import-Module SQLite

スクリプトを実行するたびにモジュールのインストールが実行されるのはパフォーマンスが低下しそうですし、インターネットに接続できない環境だとエラーになりそうなので、データベースの作成などと合わせてセットアップ用のスクリプトを別途用意した方が良さそうです。追記、コンソールをログアウトするとモジュールが消えるようなので？毎回読み込む必要あり要調査

データベースファイルのパスを定数で定義

PowerShellでスクリプトを書く場合、値が変更されない場合でも変数を使っていました。
定数の書き方を知らなかったので調べてみたところ以下の書式で定義することが出来るようです。

Set-Variable -name 定数名 -value 値 -option Constant

optionのConstantで定数として定義されるようです。構文が長いのでちょっと使いずらい感じがします。

PowerSehllでinclude

操作方法は作りながら考えているのですが、スクリプトが複数になりそうです。
そうなると、データベースファイルのパスの定義を複数のスクリプトから参照できるようにすると、データベースファイルを移動しても修正が一か所で済むので良いです。
PowerShellでincludeをする方法を調べてみたところ、以下の方法が見つかりました。

. "スクリプトのパス.ps1"

“.”を使うこの書き方は、外部スクリプトを実行する方法だと認識していたのですが、よくよく考えるとincludeと同じような振る舞いになることに気が付きました。

データベースとの接続

データベースを操作する場合、データベースに接続する作法があり、接続している状態コネクションを表すオブジェクトを生成する必要があります。
過去の記事を見たところ以下のようなコードを書いていました。

# コネクションオブジェクトの生成
$con = [SQLiteConnection]::new() | % {
    $_.ConnectionString = ("Data Source = {0}"-f "データベースファイルのパス.db")
    $_.Open()
    $_
}

%{}はForEach-Object{}の省略系で繰り返しとはかけ離れた、逆に複数実行すると具合が悪いオブジェクトの生成に使っています。
{}でコネクションを生成に関する処理は、ここから、ここまでですよと表現しているだと思われます。

テーブルの作成

SELECT以外のSQL文を実行する場合、コマンドオブジェクトを作成しデータベースに接続、実行したいSQLをセットし実行する手順になります。

# テーブル作成
# コマンドオブジェクト
$cmd = [SQLiteCommand]::new()
# コマンドオブジェクトとDBを接続
$cmd.Connection = $con
# 実行するSQLをセット
$cmd.CommandText = @"
CREATE TABLE IF NOT EXISTS file_comment (
    full_name text,
    comment text,
    primary key(full_name)
)
"@
$cmd.ExecuteNonQuery() | Out-Null

$cmd.ExecuteNonQuery()の結果をOut-Nullに流し込んで握りつぶしています。これで良いのでしょうか？
SQLのCREATE TABLEでIF NOT EXISTSという構文があり、これはテーブルが存在しない場合実行（テーブルを作成する）するという振る舞いになります。このような書き方をすれば何回実行してもエラーにはなりませんが、テーブルの作成は基本的に１回すればよいので、こちらもセットアップ用のスクリプトに組み込むと良さそうです。

ファイルにコメントをセット

これまでは概ねセットアップ処理でしたが、これからがメインの処理になります。
テーブル内にファイルが存在しない場合INSERT文でレコードを追加、ファイルが存在している場合UPDATE分でレコードを更新する処理になります。よくある処理なので便利な方法が無いかと調べたところUPSERTという造語ヒットし、INSRTでキーが重複したらUPDATEをする仕組みのようです。素敵な機能なのですが、自分の実行環境ではバージョンが古いため残念ながら実行できませんでした。
仕方が無いので、レコードの件数を取得し1件以上の場合UPDATE、そうで無い場合INSERTするようにします。

# レコードの件数を取得
$cmd.CommandText = @"
SELECT comment FROM file_comment WHERE full_name = '$FileName' 
"@
$rec = $cmd.ExecuteReader()
$count = $rec.Count
$rec.Close()

if ($count -ge 1) {
    # 更新
    $cmd.CommandText = @"
UPDATE file_comment SET comment = '$Comment' WHERE full_name = '$FileName'
"@
} else {
    # 追加
    $cmd.CommandText = @"
INSERT INTO 
    file_comment (
        full_name
        , comment
    )
VALUES (
        '$FileName'
        , '$Comment'
    )
"@
}
$cmd.ExecuteNonQuery() | Out-Null

登録されたコメントの確認

コメントを検索する機能を設けたいところですが、とりあえずデータが登録されていることを確認するだけにしたいと思います。

# レコードの参照
$cmd.CommandText = @"
SELECT full_name, comment FROM file_comment
"@
$rec = $cmd.ExecuteReader()
while ($rec.Read()) {
    Write-Host ("full_name:{0} comment:{1}" -f $rec['full_name'], $rec['comment'])
}
$rec.Close()

スクリプト

ファイル名:Init-FileComment.ps1

# モジュールのインポート
Import-Module SQLite

# データベースファイルのパス
Set-Variable -Name "DBPath" -Value "H:\ps1\FileComment.db" -Option Constant

# コネクションオブジェクトの生成
$con = [SQLiteConnection]::new() | % {
    $_.ConnectionString = ("Data Source = {0}"-f $DBPath)
    $_.Open()
    $_
}

ファイル名:Setup-FileComment.ps1

using namespace System.Data.SQLite

# モジュールのインポート
Import-Module SQLite

# データベース接続の初期処理
. "h:\ps1\Init-FileComment.ps1"

# テーブル作成

# コマンドオブジェクト
$cmd = [SQLiteCommand]::new()
# コマンドオブジェクトとDBを接続
$cmd.Connection = $con
# 実行するSQLをセット
$cmd.CommandText = @"
CREATE TABLE IF NOT EXISTS file_comment (
    full_name text,
    comment text,
    primary key(full_name)
)
"@
$cmd.ExecuteNonQuery() | Out-Null

ファイル名:Add-FileComment.ps1

using namespace System.Data.SQLite

# コメントを追加

param(
    [string]
    $FileName,
    [string]
    $Comment
)

# データベース接続の初期処理
. ".\Init-FileComment.ps1"

function Usage
{
    Write-Host ".\Add-FileComment.ps1 -FileName ""ファイル名"" -Comment ""コメント"" "
}

if ( $FileName -eq "" ) {
    Usage
    Exit
}
# 相対パス ⇒ 絶対パス
$FileName = (Resolve-Path $FileName).Path

# コマンドオブジェクト
$cmd = [SQLiteCommand]::new()
# コマンドオブジェクトとDBを接続
$cmd.Connection = $con

# レコードの件数を取得
$cmd.CommandText = @"
SELECT comment FROM file_comment WHERE full_name = '$FileName' 
"@
$rec = $cmd.ExecuteReader()
$count = $rec.Count
$rec.Close()

if ($count -ge 1) {
    # 更新
    $cmd.CommandText = @"
UPDATE file_comment SET comment = '$Comment' WHERE full_name = '$FileName'
"@
} else {
    # 追加
    $cmd.CommandText = @"
INSERT INTO 
    file_comment (
        full_name
        , comment
    )
VALUES (
        '$FileName'
        , '$Comment'
    )
"@
}
$cmd.ExecuteNonQuery() | Out-Null

ファイル名:ListAll-FileComment.ps1

using namespace System.Data.SQLite

# テーブルの一覧を表示（テスト用）

# データベース接続の初期処理
. ".\Init-FileComment.ps1"

# コマンドオブジェクト
$cmd = [SQLiteCommand]::new()
# コマンドオブジェクトとDBを接続
$cmd.Connection = $con

# レコードの件数を取得
$cmd.CommandText = @"
SELECT full_name, comment FROM file_comment 
"@
$rec = $cmd.ExecuteReader()
while ($rec.Read()) {
    Write-Host ("full_name:{0} comment:{1}" -f $rec['full_name'], $rec['comment'])
}
$rec.Close()