SQLite ですが?【#3 WPF MVVM L@bo】
前回は .NET Core でコモンダイアログを表示する方法と、MVVM パターンで実装する場合の重要なポイントを紹介しました。
3 回目の今回はファイルベースのデータベースである SQLite をプロトタイプアプリに導入すると言う今までとはかなり毛色の違うエントリになっています。
尚、この記事は Visual Studio 2019 Community Edition で .NET Core 3.1 以上 と C# + Prism 7.2 以降 + ReactiveProperty + Livet + MahApps.Metro + Material Design In XAML Toolkit + SQLite を使用して 、WPF アプリケーションを MVVM パターンで作成するのが目的で、C# での基本的なコーディング知識を持っている人が対象です。
目次
2022/8/15 追記
SQLite の情報を連載から独立したエントリに分離したかったので、新規エントリの『SQLite とメンテナンスツール』を公開しました！
SQLite の概要や SQLite 用のメンテナンスツールについて、このエントリより詳しく書いたと思うので、新しいエントリの方を読んでいただけるとありがたいです！
2022/9/9 追記
.NET 5 以降のプロジェクトで SQLite を使用する方法は、新規エントリの『SQLite の NuGet パッケージ』に、ここよりは詳しく紹介しているので、新しいエントリを見ください！
.NET Core アプリで SQLite を使用する
今回作成しているプロトタイプアプリで zip ファイルの作成設定を入力する画面と、最終的なアウトプットとなる zip ファイルを作成する画面を別 exe に分割した理由は大きく分けて以下の 3 つです。
- zip ファイルの作成は、複数のアーカイブファイルを展開 → アーカイブファイル内の全画像を開く → 横長画像のみ画像分割 → 圧縮と言う手順が必要なため、処理時間はそれなりにかかる想定。
- 複数の zip ファイルを並列で作成したい。
- zip ファイル作成処理中でも別の zip ファイル作成情報を入力したい。
現在は非同期処理も簡単に書けるようになったので、zip ファイルの作成を別スレッドで行えば同じことは可能かもしれませんが、物理的なプロセスが別になっている方が効率が良いんじゃないかと考えて exe を分けることにしました。
別 exe に分割すると、次は exe 間のデータ受け渡しをどうするかと言う新たな課題が出て来ます。
現状ではプロセス間通信や Pipe、昔ながらのファイル経由等色々な選択肢があると思いますが、zip ファイル作成 exe へ渡したデータはアプリ終了以後も保持したいので、やはり管理人が 1 番慣れ親しんでいるデータベースを使用することにしました。
ファイルベースの DB
管理人の本業的に 1 番慣れているのは Oracle や SQL Server 等の RDBMS（Relational DataBase Management System）ですが、たとえ無料版が存在するとしてもアプリ使用前にインストールが必要なものを使う人に強制できませんし、オーバースペック過ぎるのでファイルベースの DB を探しました。
まず最初に候補として考えたのは、Microsoft Access 。
管理人は結構昔（Access 2000 の頃）から Access の mdb ファイルをアプリのストレージとして利用することが多く、マクロ等は一切使わず単なる Database Table が集まったファイルとして使用していました。
それなりに便利でしたが、現状、最新バージョンのライセンスを持っていませんし、そこまで Access に固執する理由も無いので早々に候補から外しました。
2 つ目の候補も再度 Microsoft から SQL Server Compact Edition 。
以前調べた時に開発が止まっていたので今回はほとんど調べていませんが、2012/8/31 に SP1 がリリースされて以降やはり開発は止まっているようです。
.NET Core でもおそらく使えるは思いますが、確認はしていません。
開発が止まっているものを使うと後がどうなるか分からないのでこれも候補から外しました。
３ つ目も三度 Microsoft から SQL Server Express LocalDB 。
実はあまり調べていませんが、開発専用で実行環境への配布不可のように読める情報が多かったことと、実行環境へのインストールが必要？のように読める情報が多かったので、試さずに候補から外しました。
最後に元々本命と考えていた Android 等でも使用されている SQLite 。
使うのは初めてですが、使っている人も多そうでネットからの情報入手も容易そうなことに加えて .NET Core にも正式対応しているので最終的に SQLite に決定しました。
SQLite の導入
SQLite は上記公式サイトからもダウンロードできますが、Visual Studio で開発する場合は Nuget からインストールした方が楽だと思います。
fig. 1 の『ソリューションの Nuget パッケージの管理』から【sqlite】で検索できます。
（最初管理人は SQL Lite だと思っていて検索に出てこなかったのは秘密です）
fig.1 SQLite を Nuget からインストール
検索結果がいくつも表示されますが、作成者に【SQLite Development Team】が表示されているのが公式パッケージで、とりあえず SQLite を使ってみるだけなら fig. 1 の赤枠で囲んだ【System.Data.SQLite.Core】をインストールすれば十分です。
SQLite 本体以外に Entity Framework 等を使用したい場合は Core 無しの【System.Data.SQLite】を選択するとフルパッケージ版がインストールできます。
System.Data.SQLite.Core を参照していればアプリから SQLite のデータベースファイルを作成できますが、アプリから Create Table するのは面倒なので、exe と一緒に配布する予定のデータベースファイルに接続することにします。
SQLite のデータベースファイルを予め用意するために Table の作成やデータの確認ができるツール的なアプリがあると便利だと思うので DB をメンテナンスするためのフリーアプリを紹介します。
SQLite/SQL Server Compact Toolbox
まずは、Visual Studio の拡張機能として使うことができる【SQLite/SQL Server Compact Toolbox】。
インストールするには Visual Studio のメニュー [拡張機能] – [拡張機能の管理] から fig. 2 の『Manage Extensions』ダイアログを開いて、【sqlite】で検索します。
fig.2 SQLite/SQL Server Compact Toolbox のインストール
fig. 2 の赤枠で囲んだ【SQLite/SQL Server Compact Toolbox】のインストール後、fig. 3 のように [表示] – [その他のウィンドウ] – [SQLite/SQL Server Compact Toolbox] メニューを選択するとツールウィンドウが表示されます。
fig.3 SQLite/SQL Server Compact Toolbox の表示
SQLite データベースファイルの新規作成
SQLite データベースファイルを新規作成するには、いくつか方法がありますが、SQLite/SQL Server Compact Toolbox からも行えます。
fig. 3 で表示した SQLite/SQL Server Compact Toolbox ウィンドウのルートノード（Data Connections）を右クリックして fig. 4 のように [Add SQLite Connection] メニューを選択します。
fig.4 Add SQLite Connection メニュー
すると fig. 5 の Add SQLite Connection ダイアログが開きます。
fig.5 Add SQLite Connection ダイアログ
fig. 5 の赤枠で囲んだ【Create】ボタンを Click して表示される名前を付けて保存ダイアログへ保存先を指定して fig. 5 を Close で閉じると、データベースファイルが新規で作成され、作成された DB が fig. 6 のようにデータベースツリーに追加されます。
fig.6 作成した SQLite データベースファイル
尚、Create ボタン下に並んでいる Connection のプロパティは接続を作成した後は変更できない（Connection を作成し直せば変更可能）のでファイルの保存先を指定する前に設定する必要があります。（ここでは接続のプロパティ全項目をデフォルト値で作成しました）
後はデータベースツリーに追加された DB を展開すると SQL 文を流したり Table のデータを表示・編集することができます。
SQLite テーブルの作成
Table を新規作成するには Create Table 文を実行すれば作成されますが、SQLite/SQL Server Compact Toolbox には Table を作成するための UI（beta 版）が付属しています。
既に設計が Fix していて Create Table を一気に流す場合はコマンドラインから実行する方が楽だと思っていますが、管理人個人でアプリを作る場合は DB の Table 設計を後回しにして、とりあえず画面を作りながら DB へ追加したい項目が出て来る度にフィールドを追加するような作り方をする事が多いので、Table 作成用の UI がある方が便利だと思っています。
SQLite/SQL Server Compact Toolbox の GUI から Table を作成するにはデータベースツリーに追加された SQLite 接続を開いて【Tables】ノードを右クリックして [Build Table (beta)] メニューを選択します。
fig.7 Build Table
すると fig. 8 の【Build Table ダイアログ】が開くので、必要な項目を入力すると Table が作成できます。画面の操作は一般的な DataGrid と変わりませんが、3 列目以降を追加する時はちょっと変わった挙動をするので、操作方法は fig. 8 を見てください。
fig.8 Table に 3 列目を追加する場合の操作
一般的な Gird 系のコントロールは フォーカスを受け取ると新規列用の行が自動で追加されることが多いと思いますが、このダイアログは fig. 8 のように入力中とは別の行（fig. 8 では １ 行目）へフォーカスを移動しないと新規入力行が追加されないようです。
そして、fig. 9 のように列に必要な設定を入力後、右下の Script ボタンをクリックします。
fig.9 Table の列定義完了
すると Visual Studio のコードウィンドウへ fig. 10 のような Create Table 文が生成されるので、画面上部の【Script 実行】ボタンを Click すると SQL が実行され、スクリプトの実行結果が画面下部の Message タブへ表示されます。
fig.10 生成された Create Table
他の SQLite を紹介しているブログ等では Create Table 等の実行はコマンドプロンプトから【sqlite3 コマンド】を介して流す例が多いですし、その方が汎用性が高いと思いますが、管理人のように「書く機会が少ない DDL なんて文法覚えてねーんだよ！」と言う人には良いかもしれません…
いかんせん SQLite/SQL Server Compact Toolbox 付属のテーブル作成 GUI は Create Table はできても変更には使えない等、機能的にかなり微妙なので万人にお勧めできないと思っていますが一応紹介します。
SQLite の列
SQLite は管理人が使い慣れている Oracle や SQL Server 等とは違って、列のデータ型指定は元々不要でしたが、SQLite 3 からデータ型が指定できるようになったようで、指定可能なデータ型は以下の ５種類です。
- INTEGER
- REAL
- TEXT
- BLOB
- NULL
詳しくは『SQLiteで利用可能なデータ型 – DBOnline』を見てください。
SQLite についてはプロトタイプアプリに必要な部分しかこの連載では触れない予定なので、SQLite の詳細な仕様が知りたい場合は上の DBOnline 等を見る方が良いと思います。
SQLite のデータ長
更に SQLite は列のデータ長が設定できないと言う違いもあります。fig. 10 の Build Table ダイアログには Length、Precision、Scale 等の設定項目がありますが、これは SQL Server Compact 用の項目なので、SQLite の場合は設定しても無視され、生成される Create Table 文にも反映されませんし、Create Table に『ID TEXT(10)』等と書いても『(10)』の部分は無視されます。
データ長が設定できないと言っても当然無制限ではなく、下表のような最大値が存在します。
| データ型 | 格納サイズ | 最大値 |
|---|---|---|
| INTEGER | 1, 2, 3, 4, 6, or 8 バイト | 9,223,372,036,854,775,807 .NET の long と同等 |
| REAL | 8バイト | 1.7976931348623157E+308 .NET の double と同等 |
| TEXT | 2,147,483,647 バイト（約 2 GB） | .NET の System.String と同等 |
| BLOB | 2,147,483,647 バイト（約 2 GB） | – |
BLOB 型以外は .NET が対応する型と範囲が同じなので、SQLite との I/O 時に桁数オーバーは考慮する必要がないと言えます。
SQLite の AUTOINCREMENT
SQLite は Microsoft Access でもお馴染みのオートナンバー型フィールドもサポートしていて、Create Table 時に src. 1 の 2 行目ように【PRIMARY KEY AUTOINCREMENT キーワード】を付けて実行すると設定できます。
1 2 3 4 5 6 7 8 | CREATE TABLE [ZIP_FILE_SETTINGS] ( [ID] INTEGER PRIMARY KEY AUTOINCREMENT , [EXTRACT_FOLDER] TEXT NOT NULL , FOLDER_NAME_SEQ INTEGER , FOLDER_NAME_TEMPLATE TEXT , FILE_NAME_TEMPLATE TEXT , [INSERT_DATE] TEXT NOT NULL ); |
採番方法は Microsoft Access に似ていて、Insert 文のフィールドリストから AUTOINCREMENT が設定されている項目を省いて実行すると自動採番された値が自動でフィールドにセットされます。
又、採番された値の取得は Oracle や SQL Server 等に似ていて、SQLite が自動的に作成する sqlite_sequence システムテーブルから Insert 直後にテーブル名を指定して src. 2 のように Select を呼び出すと挿入された値が取得できます。
1 2 3 4 5 6 | SELECT SEQ.seq FROM sqlite_sequence SEQ WHERE SEQ.name = 'ZIP_FILE_SETTINGS' |
sqlite_sequence テーブルはデータベースファイルを新規で作成した直後は存在しませんが、AUTOINCREMENT を付加したテーブルが作成されると内部的に自動生成されます。
汎用SQL開発ツール A5:SQL Mk-2
先に紹介した SQLite/SQL Server Compact Toolbox は Visual Studio の拡張機能でしたが、DB 関連のツールは開発用 IDE とは別のアプリになっている方が都合が良い場合も多いと思うので、本章からは単体で実行可能な【汎用 SQL 開発ツール A5:SQL Mk-2（略称： A5M2）】を紹介します。
業務系界隈では結構有名な気がするので使ったことがある（使っている）人も多い気もしますが、A5:SQL Mk-2 は Oracle、SQL Server、DB2 等の商用 DBMS を初めとして接続可能な DB も多く、SQL の実行や ER 図まで作成できる軽量かつ高機能なフリーの SQL 開発ツールです。
fig.11 A5:SQL Mk-2
そして、当然 SQLite にも対応していますが、A5M2 はかなりの高機能アプリなので全機能や使い方の紹介はせず、SQLite への接続方法のみを紹介します。
SQLite への新規接続を追加
A5M2 へ DB の接続を追加するには fig. 12 画面上部の [データベースの追加と削除] ツールボタンを Click して表示される【データベースの追加と削除ダイアログ】左下の【追加】ボタンを Click します。
fig.12 データベースの追加と削除
追加ボタン Click で表示される fig. 13【追加するデータベースの接続タイプを選択ダイアログ】の赤枠で囲んだ【SQLite（直接接続）】を選択します。
fig.13 追加するデータベースの接続タイプを選択
すると fig. 14 の【データベースの内容を登録 – SQLite ダイアログ】が表示されるので、保存先を選択して OK ボタンを Click すると A5M2 へデータベースが登録されます。
fig.14 データベースの内容を登録 – SQLite
但し、この画面にはバグが 1 つあって、データベースファイルのパスを設定する TextBox の下側【データベースファイルがない場合作成する CheckBox】をチェックしてファイルを開くコモンダイアログで新規のデータベースファイル名を入力してもエラーになります。
ファイルを開くコモンダイアログの『CheckFileExists プロパティ』に true をセットしているのが原因だと思いますが、TextBox に直接ファイルパスを入力すればファイルはちゃんと作成されます。
既存ファイルの選択は問題無いので現時点ではバグ報告等は上げていません。
そして、fig. 15 の【A5:SQL Mk-2で識別するデータベース別名を入力ダイアログ】へ任意の名前を入力して OK ボタンを Click すると登録した DB が A5M2 のデータベースツリーへ追加されます。
fig.15 A5:SQL Mk-2で識別するデータベース別名を入力ダイアログ
そして、fig. 16 のようにデータベースツリーを展開すると表示される【データベースログインダイアログ】の接続ボタンからデータベースへ接続できます。
fig.16 データベースへ接続
尚、SQLite 自体はログインパスワードに対応していないため、データベースログインダイアログのユーザ ID とパスワード欄は空のままで接続を続行して構いません。
接続完了後は対象のデータベースに SQL を実行したり登録済みデータの閲覧や編集ができるようになります。
ここで紹介した SQLite/SQL Server Compact Toolbox や A5:SQL Mk-2 は SQLite の使用に必須ではありませんが、あると便利だとは思うので興味があれば使ってみるのも良いと思います。
.NET Core で使用する場合の接続情報の更新
このプロトタイプアプリで使用している SQLite のデータベースファイルは実行ファイルと同じ場所に配置（正確には『Settings』フォルダ直下に配置）していて、接続時に『Assembly.GetExecutingAssembly().Location』プロパティから Path を取得しています。
実行ファイルの Path を取得することは普通のことですが、このプロトタイプアプリは .NET Core 3.0 リリース直後から作成し始め、現在はフレームワークを .NET Core 3.1 に変更しているのがポイントです。
.NET Core では fig. 17 のようにフレームワークのバージョンごとにビルドフォルダを作成するようです。
fig.17 .NET Core のビルドフォルダ
ああ、そうなのか… と言う程度の話ですが、.NET Framework と変わらない感覚でプロトタイプアプリを作成しているとアプリからデータを更新して SQLite/SQL Server Compact Toolbox や A5:SQL Mk-2 から実データを確認しても更新されていないと言う問題が発生して少しハマりました。
ここまで書けば気が付くと思いますが、プロトタイプアプリを Debug 実行して書き込まれるのは【netcoreapp3.1 フォルダ内の SQLite データベースファイル】ですが、SQLite/SQL Server Compact Toolbox や A5:SQL Mk-2 の SQLite の接続情報は【netcoreapp3.0フォルダ】の方で登録されているからです。
解決方法は SQLite/SQL Server Compact Toolbox や A5:SQL Mk-2 に登録している接続情報を【netcoreapp3.1】に変更するだけですが、原因になかなか気が付かなかったのでちょっとした注意情報として書いておきます。
.NET Framework でもソリューション構成を Debug から Release に変更すれば同じ状況が発生しますが、.NET Core の場合はフレームワークのバージョンが変わっても出力先が変わるのは忘れやすいポイントだと思います。
まあ、開発中にフレームワークのバージョンアップがリリースされ、かつアプリの対応バージョンを変更した場合にしか発生しないので仕事のプロジェクトで起こる可能性はかなり低いとは思いますが、個人で作成している場合はフレームワークのバージョンを変更することもあると思うので気を付けておいた方が良い点だと思います。
さて、これで SQLite へ読み書きする準備は整ったので、この連載で紹介しているプロトタイプアプリから SQLite に接続したいと思いますが、長くなってしまったので今回はここまでで一旦終了して、SQLite からデータの登録・取得する方法は次回エントリで紹介します。
今回は珍しく C# のソースコードを 1 行も載せないエントリになりましたが、プロトタイプアプリの内部実装自体は気の向くまま進めているので #2 よりは少し進んでいると思います。
プロトタイプアプリのソースコードはいつもの通り GitHub リポジトリ に上がっています。
次回記事「DB が見えるのは嫌なので 3 階層 に AbstractFactory したいと思います。【#4 WPF MVVM L@bo】」
