Microsoft.Data.Sqlite
MicrosoftがメンテナンスするSQLite向けの軽量ADO.NETプロバイダーで、ネイティブのSQLite本体もNuGetパッケージに同梱される。
WALモード(Write-Ahead Logging)
変更をデータベース本体ではなく隣接する-walファイルへ追記していき、読み取りと書き込みを同時に進められるようにするSQLiteのジャーナリングモード。
System.Data.SQLite
SQLite開発チーム系列の老舗ADO.NETプロバイダーで、接続文字列や型の扱いがMicrosoft.Data.Sqliteとは互換性のない別物。
Microsoft.EntityFrameworkCore.Sqlite
内部でMicrosoft.Data.Sqliteを使う、EF Core向けのSQLiteデータプロバイダー。
EF Core SQLiteプロバイダー
新規開発でのSQLiteライブラリ採用
これから作るアプリでC#からSQLiteを扱うためのADO.NETプロバイダーを新規に選定・採用すること。
SQLITE_BUSY(database is locked)
他の接続が書き込みロックを持っているときにSQLiteが返すエラーで、Microsoft.Data.Sqliteはコマンドタイムアウトまで自動リトライする。
書き込み経路の一本化(Channelsによる書き込みキュー)
複数スレッドが直接DBに書かず、System.Threading.Channelsのキューに投げて専任の書き込みループだけがコミットする設計。
System.Threading.Channels書き込みキュー
明示的トランザクションによるINSERTのバッチ化
複数件のINSERTをBeginTransactionで囲みまとめてCommitすることで、コミットごとのfsyncコストを削減する手法。
コミットごとの同期書き込み(fsync)コスト
SQLiteがコミットのたびに行うストレージへの同期書き込みにかかるコストで、1件ずつ暗黙コミットするとSSDでも毎秒数百〜数千件で頭打ちになる。
稼働中DBファイルの単純ファイルコピー
SQLiteのDBファイルが稼働中のまま、OSのファイルコピー機能でそのまま複製するバックアップ方法。
SQLCipher
SQLiteデータベースファイル全体を透過的に暗号化する、標準のSQLiteネイティブライブラリを置き換える形の拡張。
DPAPI
実行中のマシン上でアプリの秘密情報を保護するWindowsのデータ保護API。
データ保護API
Microsoft.Data.Sqliteの接続プーリング
バージョン6.0以降Microsoft.Data.Sqliteで既定有効になっている、接続のOpen/Closeを高速化する仕組み。
プーリングによるDBファイルハンドルの保持
接続をClose/Disposeしてもネイティブの接続がプールに残り、DBファイルのハンドルを掴み続けたままになる状態。
SqliteConnection.ClearPool
指定した接続文字列に対応する接続プールを破棄し、DBファイルのハンドルを解放するMicrosoft.Data.SqliteのAPI。
SQLiteデータベースの破損
ディスク障害や不適切なファイル操作によってSQLiteのファイル構造が壊れ、正しく読み書きできなくなる状態。
VACUUM INTO
元のファイルを変更せず、断片化を解消した一貫性のある最小サイズのコピーを別ファイルへ作成できるSQLiteのSQL文。
稼働中SQLiteデータベースの一貫したバックアップ要件
稼働中のSQLiteデータベースを、トランザクション途中の状態を混ぜずに一貫性のある形で複製したいという要件。
SqliteConnection.BackupDatabase
SQLiteのBackup APIをラップした、稼働中でも接続オブジェクト間で一貫したスナップショットを作成できるMicrosoft.Data.SqliteのAPI。
BackupDatabase完了までの書き込みブロック
Microsoft.Data.SqliteのBackupDatabaseが可能な限り速くコピーする代わりに、完了するまで他の接続からの書き込みをブロックする現在の実装挙動。
PRAGMA quick_check
完全なintegrity_checkより軽量に、SQLiteデータベースの整合性を検証できるPRAGMA文。
SQLiteデータベース全体の暗号化要件
SQLiteのデータベースファイル全体を暗号化して保存したいという要件。
アプリの秘密情報
業務アプリが扱う接続文字列・APIキーなどの秘密情報。ログオン済みマシンではドライブ暗号化では守れない。
接続文字列
APIキー
SQLiteの型マッピング(INTEGER/REAL/TEXT/BLOB)
SQLiteが実際に保存できるINTEGER/REAL/TEXT/BLOBの4つの型に、.NETの型を割り当てるMicrosoft.Data.Sqliteの対応規則。
decimal列のTEXT保存による比較・集計の不整合
decimalがTEXTとして保存されるため、SQLiteの型序列上TEXTが常に数値より大きく扱われ、WHERE比較やSUM等の集計が意図どおりに動かなくなる問題。
金額を最小通貨単位の整数で持つ設計
金額をdecimalではなく、円などの最小通貨単位に換算したINTEGER(long)で保存する設計方針。
接続文字列のキーワードも型の既定の扱い(例: Guidの保存形式)も異なる別物で、互換性がない。
EF CoreのSQLiteプロバイダーは、内部でMicrosoft.Data.Sqliteを使う。
新規開発で使うライブラリは、EF Coreチームがメンテナンスする軽量なMicrosoft.Data.Sqliteが基本とされる。
既存アプリが安定稼働しているなら無理に移行する必要はないが、新規のコードはMicrosoft.Data.Sqliteに寄せるのが原則とされる。
WALモードでは読み取りと書き込みの並行性が上がり、database is lockedの大半が消える。ただし書き込み同士は依然として排他のため、SQLITE_BUSYが完全に無くなるわけではない。
Microsoft.Data.Sqliteはbusy/lockedエラーに対して、コマンドタイムアウト(既定30秒)まで自動でリトライする。
書き込み経路を1本のキューに寄せると、複数スレッドによる書き込みの衝突が構造的に起きなくなる。ただし他の接続が握る長いトランザクションや読み取りから書き込みへの昇格の衝突など、他の原因によるSQLITE_BUSYは残り得る。
1,000件単位の明示的トランザクションにまとめるだけで、コミットごとのfsyncコストが減り毎秒数万〜数十万件のオーダーに届く。
書き込みキューはチャネルに溜まった分を最大500件までまとめて1トランザクションで書くため、経路の一本化とバッチ化を同時に実現する。
WALモードでは変更が-walファイルに追記されるため、本体(.db)だけを単純コピーすると本体未反映の直近のコミットが抜け落ちる。
SQLCipher系バンドルでDB全体を暗号化する場合、その暗号化キー自体の保管には何らかの保護手段が必要になる。DPAPIはWindows環境で選ばれることが多い一例であり、鍵保護の唯一の手段ではない。
Pooling=Falseを指定しない既定構成では、Microsoft.Data.Sqliteはバージョン6.0から接続プーリングが既定で有効になっている。
接続をClose/Disposeしてもネイティブの接続がプールに残り、DBファイルのハンドルを掴み続けることがある。
ファイル操作の直前に対応する接続プールを空にすることで、DBファイルのハンドル保持を解消できる。
稼働中(トランザクション中)のDBファイルコピーは、SQLite公式が破損原因として明記している。
VACUUM INTOはSQL1文で、断片化を解消した一貫性のある最小サイズのコピーを稼働中でも作成できる。
BackupDatabaseは接続オブジェクト間で稼働中でも一貫したスナップショットを作成できるが、完了まで他の接続の書き込みをブロックする点に注意が必要。
稼働中の単純ファイルコピーは、一貫したバックアップの手段としては禁止される。
現在の実装は可能な限り速くコピーする代わりに、完了まで他の接続からの書き込みをブロックする。
起動時にPRAGMA quick_checkを実行することで、SQLiteデータベースが破損していないかを確認できる。
DB全体の暗号化が要件なら、SQLCipher系のネイティブライブラリへの差し替えが必要になる。
接続文字列のPasswordを指定しても、標準のネイティブライブラリは暗号化に対応していないため何も起きない。
少量の機密情報なら、DB全体の暗号化より先にWindowsのDPAPIによる保護を検討することが推奨される。
decimalはREALでは精度が欠損するためTEXTとして保存され、TEXT列に対する比較・集計が文字列規則になり意図どおりに動かないことがある。
金額を最小通貨単位の整数(INTEGER)で持てば、比較も集計も正確かつ高速になり型の罠が消える。
journal_modeを明示的に変更しない既定構成では、EF Coreが作成するSQLiteデータベースは最初からWALモードが既定で有効になっている。
EF Coreを使っても型の事情は消えず、decimal等の等値以外の比較や並び替えはクライアント評価になる。