SQLite とメンテナンスツール
前回の技術記事から半年以上空いてしまいましたが、久々の技術系エントリです。（毎回『久々』って言ってる気がしますが…）前回のエントリ公開から常駐先が変わったり、VS 2022 用の Prism のプロジェクトテンプレートがなかなかリリースされなかったり、10 年ぶりくらいに PC を買い替えたり（Windows 11 になりました！）とか色々あってなかなか手が進みませんでした…
今回は以前に【SQLite ですが?】で書いた SQLite について再度取り上げます。
【SQLite ですが?】は現在連載を休止している『WPF MVVM L@BO シリーズ』のエントリと言う事もありますし、内容的に中途半端だと感じるようになったので前回より多少詳しめに調べて新たな記事として公開することにしました。と言っても、SQLite は概要のみの紹介で、大半は SQLite のメンテナンスツールを紹介するエントリになっています。
SQLite とは
SQLite は以前のエントリでも紹介した通り、サーバとして動作するアプリではなく、データを単一ファイルに格納する事の出来るデータベースエンジンです。（いわゆるファイルベースのデータベースエンジン）
SQLite は名前に Lite が付いていますが、フル機能の SQL を実装した、パブリックドメインライセンスのデータベースエンジンなので、自作アプリに組み込むこともできます。
SQLite 最大の特徴は Windows だけでなく Android、iOS、Linux、Mac 等の多数の OS に対応したクロスプラットフォームなデータベースエンジンで、かつ、バイトオーダーに非依存のアーキテクチャを採用しているため、再利用性が高く、例えば Windows で作成した SQLite のデータベースファイルを iPhone 等に移して読み出すようなこともできるようです。
SQLite の公式サイト には『SQLite の競合相手は MySQL、Oracle、PostgreSQL、SQLServer 等のクライアント/サーバタイプの SQL データベースエンジンではなく ANSI-C で言えば fopen() だ』と書かれているので、アプリのちょっとしたデータストレージに向いていると言えるでしょう。
このサイトで以前に公開した『.NET6 で Generic Host を使った常駐アプリ』で紹介しているサンプルアプリでもデータストレージとして使用しています。
又、SQLite はアプリのデータストレージとして使用する以外にも Appropriate Uses For SQLite に書かれているように色々な場面で使用できるようです。
SQLite のデータ型
以下は、SQLite がサポートしているデータ型です。
- NULL
- INTEGER
- REAL
- TEXT
- BLOB
上で挙げたデータ型に加えて、SQLite ではデータ型を指定しないカラムも作成できますが、管理人のように業務系メインで、Oracle や SQL Server 等のサーバ型 RDBMS に慣れ親しんだ人間的には『データ型を指定しないカラムを作成する』とかバグの温床になる気しかしない… と思います。動的型付け系のスクリプト言語が得意な人なら利点を思い付くのかもしれませんが、管理人には思い付きません…
ここではカラムのデータ型について補足的な内容を簡単に紹介します。
日付・時刻型
SQLite がサポートしている型は上に挙げた 5 種類ですが、ほとんどの用途はカバーできています。できれば【日付・時刻型】があれば良いのに！とは思いますが、SQLite の場合【TEXT 型】や【REAL 型】のカラムに日付・時刻データを格納でき、SQL 文内で計算もできるので、『何となく気持ち悪い』事さえ我慢すれば管理人的には、今まで困る事はありませんでした。
オートナンバー型
SQLite で INTEGER 型のカラムにプライマリキー制約を指定すると、特殊な動きをします。【INTEGER PRIMARY KEY】で定義したカラムに値を指定せず INSERT すると MS Access のオートナンバー型と似た動きをしますが、多少の違いがあるので、詳しくは『SQLite | AUTOINCREMENTを設定する場合としない場合の違い』等を見てください。
MS Access のオートナンバー型と同じ動作にしたい場合は、カラムを【INTEGER PRIMARY KEY AUTOINCREMENT】で定義指定します。【AUTOINCREMENT】を付加したカラムは MS Access でオートナンバー型を指定したカラムと同じ動作になります。（MS Access を使用したことが無い場合は Oracle や SQL Server 等でシーケンスを使用した場合を想像してみてください）
SQLite の管理用アプリ
SQLite は上にも書いた通りフル機能の SQL をサポートしているため、C# 等のコードからテーブル定義やデータをメンテナンスすることも可能ですが、テストデータを追加したり、DB 構造等を修正する度にコードを書くのはさすがに面倒だと思うので、SQLite に対応した DB メンテナンスアプリを使用する方が便利だと思います。
以前に書いた『SQLite ですが?』でも『SQLite/SQL Server Compact Toolbox』や『A5:SQL Mk-2（以降 A5M2）』等を紹介したので、既にそれらのアプリを使用している場合は無理に変える必要は無いと思います。（既に使用しているアプリをそのまま使用する場合、以降を読んでもほとんど意味がないと思います）
特に『A5M2』は仕事でデータベースを扱うなら無くてはならない便利で高機能なアプリですが、GUI でテーブル定義等を操作するような機能は残念ながらサポートされていません。
テーブル定義編集 GUI は、必須の機能とは言えないと思いますが、自作アプリに SQLite を組み込む場合等には、あれば便利な機能だと個人的には思っているので、テーブル定義編集 GUI を備えたアプリを探してみました。
DB Browser for SQLite
まず最初は SQLite 用の管理ツールとしては最もメジャー（想像）と思われる DB Browser for SQLite を紹介します。
DB Browser for SQLite は日本語のブログ記事もいくつか見かけるので、日本では最もメジャーな SQLite 管理アプリだと想像しています。Windows、MacOS、Linux に対応していて、Windows 版は 32/64 bit 版が用意されています。
DB Browser for SQLite のダウンロードとインストール
ダウンロードは DB Browser for SQLite 公式サイトの Download ページ から行います。
Windows 版は 32/64 bit に加えて、インストーラあり/無しの合計 4 パターンがあり、インストーラ無し版は、zip ファイルを任意のフォルダに解凍するだけで使用する事ができます。インストーラあり版を選択しても、画面は一般的なインストールウィザードなので特に迷う所は無いと思いますが、途中で表示される fig. 1 の画面だけ触れておきます。
fig.1 アプリケーションのショートカット画面
赤枠で囲んだ【DB Browser (SQLite)】の方は通常の SQLite なので、ショートカットを登録したい箇所にチェックを入れるだけですが、右の【DB Browser (SQL Cipher)】は『？』と思う人も居ると思うので、簡単に補足しておきます。
SQL Cipher
SQL Cipher は暗号化ファイルに対応した SQLite です。管理人は使用した事がある訳ではなく、サイトを翻訳して読んだだけなので間違えているかもしれませんが、簡単に紹介します。
SQLite は非常に有用な DB ですが、唯一の問題点は Oracle や SQL Server のようにログインユーザを作成してアクセスを制限できない点です。ログインユーザが作成できないと言うことは、ここで紹介している DB Browser for SQLite 等を利用すればデータ参照や編集が簡単に出来てしまいます。
つまり、標準の SQLite では重要なデータは保護されず、ファイル内のデータを取得することも容易ですが、SQL Cipher を使用するとデータベースファイルが暗号化されるため、外部からデータを保護できるようになります。
SQL Cipher は OSS なので GitHub から Clone してコンパイルする事も出来ます（Community Edition）が、コンパイル済みの DB エンジンを入手したい場合は Commercial Edition のライセンスを購入する必要があるようです。自力でコンパイルする場合は GitHub の ReadMe 等を参考にすればできると思いますが、管理人は使用した事は無いので詳細は分かりません。
DB Browser for SQLite の起動
fig. 1 のように DB Browser for SQLite のインストーラあり版で DB Browser (SQLite)、DB Browser (SQL Cipher) の両方とも Program Menu にチェックした場合、スタートメニュー（Windows10）に fig. 2 のようなショートカットが 2 つ登録されます。
fig. 2 DB Browser for SQLite のスタートメニュー
管理人の場合、とりあえず両方ともインストールしましたが、SQL Cipher は使用しません。fig. 2 の赤枠で囲んだ DB Browser (SQLite) を起動すると、fig. 3 の画面が表示されます。
fig.3 DB Browser 画面
インストーラは英語ですが、本体を起動すると設定不要で日本語が表示されます。DB Browser for SQLite は 1 つの Window で 1 つの DB ファイルを扱う形式なので、複数の DB ファイルを使用できるよう実行ファイル（exe）は複数起動が可能になっています。
SQLite DB ファイルの新規作成とオープン
画面の表示言語が日本語なので大して迷う事も無いと思いますが、fig. 4 の赤枠で囲んだボタンをクリックすると、ファイルの保存/開くダイアログが表示されて、任意のファイルを指定する事が出来ます。
fig.4 DB Browser for SQLite で新しい DB ファイルの作成と既存 DB ファイルのオープン
【新しいデータベース】で作成したファイルは作成後、自由に移動やコピーできるので、Oracle や SQL Server 等と比べるとデータファイルを取り扱いが簡単なのはファイルベースデータベースの利点ですね。
DB テーブルの作成
DB Browser for SQLite で新規の SQLite ファイルを作成すると、【新しいデータベース ボタン】からファイルの保存先を指定した後、fig. 5 の【テーブルの定義を編集 ダイアログ】が表示されます。
fig.5 テーブルの定義を編集ダイアログ
テーブルの定義は fig. 6 のようにテーブル名やカラムの名前・データ型等を設定します。
fig.6 カラムの追加
カラムの定義を変更すると fig. 6 のように下ペインに設定内容が反映されます。又、『▼高度な設定』でテーブルの作成先スキーマ等も変更できますが、管理人は使用した事がありません。
fig. 6 のグリッド右側に並ぶ項目は以下の意味になります。
| 列ヘッダ | 内容 |
|---|---|
| NN | チェックするとカラムに NotNull 制約が設定されます。 |
| PK | チェックすると PRIMARY KEY 制約 が設定されます。 |
| AI | チェックすると AUTO INCREMENT 型のカラムに設定されます。 ※ チェックするとデータ型が自動的に【INTEGER】に変更されます。 |
| U | チェックすると UNIQUE 制約が設定されます。 |
| デフォルト | デフォルト値を設定します。（Oracle や SQL Server 等のデフォルト値と同じです） |
| 検査 | Check 制約を設定します。 |
必要なカラムを追加して、【テーブルの定義を編集ダイアログ】を OK ボタンで閉じると、DB Browser for SQLite の画面が fig. 7 のようになり、テーブル作成準備状態になります。
fig. 7 テーブル作成後の DB Browser for SQLite
fig. 7 の状態はテーブル作成用の DDL が作成されただけでまだ DB ファイル自体には反映されていません。そのため、右に見える『データベースのセルを編集』等は操作はできますが、データ入力等はできない状態です。実際にテーブルを作成するには、fig. 7 上部の赤枠で囲んだ【変更を書き込み】をクリックする必要があります。
データの入力と編集
テーブルを DB ファイルに書き込むと fig. 8 のようにデータの入力や編集ができるようになります。
fig.8 データの入力や編集
データの入力や編集は fig. 8 の通り、以下の手順で行います。
- 【データ閲覧 タブ】を開く
- 【テーブル ドロップダウン】で対象のテーブルを選択
- 【新しいレコードを現在のテーブルに挿入 ツールボタン】をクリック
- グリッドに追加された新しいレコードに直接値を入力する。又は、右の【データベースのセルを編集 ペイン】の入力エリアへ入力して【適用 ボタン】をクリックする。
- データを入力し終えたら、【変更を書き込み ツールボタン】をクリックして DB ファイルに反映する。
多少、直感的でない UI な気もしますが、DB Browser for SQLite を使用して何百件ものデータを入力する事は多くないと思うので、あまり気にならないかな…とは思います。
少し驚いたのは、fig. 9 の【テーブルの定義を編集 ダイアログ】でした。
fig. 9 テーブルの定義を編集 ダイアログ
【DB テーブルの作成 の章】でも紹介したテーブルの定義を編集 ダイアログですが、fig. 9 の赤枠で囲んだ所に【上へ、下へ ボタン】があります。
カラムの位置を変更するためのボタンなのは見れば分かると思いますが、このボタン、データが存在する場合でもカラム位置を移動する事が出来ます。管理人が持っている 1,500 件程のレコードが登録されているファイルでも、ボタン操作のみでカラムの順序を変更する事が出来ました。
Oracle や SQL Server 等の場合だと、データを退避して、テーブルを再作成してから、退避したデータを再作成したテーブルへ INSERT する必要があると思いますが、DB Browser for SQLite を使用すると、非常にお手軽な操作でカラムの並び順が変更できました。
SQLiteStudio
ここからは、別の SQLite 管理アプリ SQLiteStudio を紹介します。
SQLiteStudio は DB Browser for SQLite と同じく Windows、Linux、MacOS に対応した GPL ライセンスの OSS で、GitHub の Releases ページからダウンロード できます。
新しいバージョンがリリースされると fig. 10 のようリリースノートが追加され、実行ファイル等は【Assets】からダウンロードできます。
fig.10 GitHub Releases ページ
ダウンロードするファイルはリリースノート内に書かれている通り、対象 OS ごとに以下のファイルを選択します。
| 拡張子 | 対象 OS |
|---|---|
| zip | Windows 版（64 bit） |
| tar.xz | Linux 版（64 bit） |
| dmg | MacOS 版（64 bit） |
管理人は Windows 版しか使った事がないので、他 OS 版については触れません。Windows 版の zip ファイルをダウンロードして、zip 内の【SQLiteStudio フォルダ】を任意の場所に解凍するだけでインストールは完了です。
SQLiteStudio の初回起動
任意の場所に解凍した【SQLiteStudio\SQLiteStudio.exe】をダブルクリック等で実行すると、アプリで使用する言語の選択ダイアログが表示された後、fig. 11 のウィンドウが表示されます。2022/8/14 時点では日本語に未対応なので、管理人は【American English】を選択しました。
fig.11 SQLiteStudio 初回起動後
上にも書いた通り、SQLiteStudio は現時点（2022/8/14 現在）では日本語に未対応なので、日本語 UI しか使いたくない！と言う人にはお勧めできませんが、特に難しい単語は見当たらないので管理人的には大して困ることはありませんでした。
SQLite DB ファイルの取り扱い
上で紹介した DB Browser for SQLite は起動する度に使用するファイルを選択するタイプのアプリですが、SQLiteStudio は使用するファイルを予め登録しておくタイプのアプリなので fig. 12 のように操作して、使用する DB ファイルを登録する必要があります。
fig.12 DBファイルの登録
- 【Add a Database ツールボタン】をクリックして Database ダイアログを開く。
- Database type を選択する。（通常の SQLite DB ファイルの場合は【SQLite 3】を選択する）
※ SQLCipher や WxSQLite3 は暗号化された SQLite DB ファイル - 【File】に SQLite DB ファイルのパスを入力する。
ファイル名入力エリア右横の、緑丸の + ボタンは新規 DB、フォルダボタンは既存 DB を指定するためのファイル保存/開くダイアログが開くので、そこからファイルを指定することもできます。 - 【Name (on the list)】は DB ファイルを識別するための文字列（使用者が区別できれば何でも OK）を入力する。
- 【Options – Permanent (keep it in configuration)】をチェックして OK すると、設定ファイル内に保存され、次回以降も続けて利用できます。チェック Off で登録した場合、設定ファイルには保存されないので、次回使用時も登録が必要になります。
上にも書いた通り必要な項目（必須は【File】と【Name (on the list)】）を入力して Database ダイアログの【OK ボタン】をクリックすると fig. 13 のように画面左ペインの TreeView に SQLite DB ファイルが追加されます。（A5M2に近い画面になります）
fig.13 SQLite DB ファイルの追加
fig. 13 のように画面左の TreeView に DB ファイルが追加されると DB ファイルに接続できるようになり、テーブル、ビュー作成等の操作ができるようになります。
テーブルの作成
テーブルの作成は fig. 14 のように TreeView の【Tables ノード】を右クリックして【Create a table メニュー】をクリックするか、ツールボタンの【Create a table】から行います。メニューの [Structure] – [Create a table] からでも実行できます。
fig.14 テーブル設定 View の表示
テーブル設定 View が表示されると後は fig. 15 のようにテーブル名やカラム定義を追加します。
fig.15 テーブルの作成
fig. 15 の通り、以下の手順でテーブルを作成します。
- テーブル名を入力。
カラム定義をする前でも後でも入力できます（テーブル名未入力（ブランク）でも作成できるようですがどうやって使うのかは分かりません） - 【Add column ツールボタン】をクリックして【Column ダイアログ】を開きます。
- カラム名を入力。
- カラムの【Data type】を選択します。
上で紹介したデータ型より選択肢は多いですが、SQLite がサポートする型に変換されます。 - 【Constraints (制約)】を設定します。（fig. 15 では【Primary Key】のみ設定しています）
- 【OK ボタン】で Column ダイアログを閉じます。
- 以上の手順を繰り返して、必要な数だけカラム定義を追加します。
- 全てのカラムをし終わったら【Commit structure changes ツールボタン】をクリックします。
- 実行確認画面が表示され、設定した定義に基づいた DDL（Create Table）文が表示されます。
- 【OK ボタン】をクリックすると表示された DDL が実行され、TreeView の【Tables ノード】下に作成されたテーブルが追加されます。
設定できる内容は DB Browser for SQLite とほとんど同じなので、前章も合わせて確認してください。
Autoincrement
SQLiteStudio の場合、Autoincrement（オートインクリメント）の設定箇所は、もう 1 段下の階層にあるので別途紹介します。
設定手順は fig. 15 と同じですが、Autoincrement（オートインクリメント）は fig. 16 のように設定します。
fig.16 Autoincrement を設定
DB Browser for SQLite では Primary Key と Autoincrement は同列の設定項目でしたが、SQLiteStudio では Primary Key のオプションとして Autoincrement を設定するようになっています。
テーブル作成 View でのキャンセル
SQLiteStudio のテーブルを作成、編集中の変更をキャンセルしたい場合は、fig. 17 のように操作します。
fig.17 テーブル設定のキャンセル
fig. 17 の通り、変更をキャンセルするには、View 上部の【Rollback structure changes ツールボタン】をクリックするか、画面下部のタブのコンテキストメニューから【Close selected window メニュー】を選択して表示される問い合わせメッセージボックスで【Close anyway ボタン】をクリックします。
カラムの位置（順序）変更
SQLiteStudio でも DB Browser for SQLite の章で試したカラム位置の移動ができそうだったので試してみました。fig. 18 の赤枠で囲んだボタンで変更できます。
fig.18 SQLiteStudio のカラム移動ボタン
実際に操作すると、fig. 19 のように画面が遷移します。
fig.19 SQLiteStudio でのカラム位置変更
fig. 19 の通り、矢印キーでカラムを移動した後、コミットボタンで変更を反映する必要があります。DB Browser for SQLite との違いは実行前に src. 1 のような実行する SQL が表示される点です。
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 | PRAGMA foreign_keys = 0; CREATE TABLE sqlitestudio_temp_table AS SELECT * FROM RecentHistories; DROP TABLE RecentHistories; CREATE TABLE RecentHistories ( FilePath TEXT, AccessTime TEXT, PRIMARY KEY ( AccessTime ) ); INSERT INTO RecentHistories ( FilePath, AccessTime ) SELECT FilePath, AccessTime FROM sqlitestudio_temp_table; DROP TABLE sqlitestudio_temp_table; PRAGMA foreign_keys = 1; |
src. 1 を見ると、データを新規テーブルに退避して元テーブルを Drop – Create しているだけの単純な SQL スクリプトなので、Oracle や SQL Server 等の一般的な DB に慣れていれば特に困ることも無さそうです。（SQLite 独自の PRAGMA 構文は何を意味するかは調べていません…）
SQL エディタ比較
DB Browser for SQLite と SQLiteStudio には当然 SQL を入力して実行するためのエディタが付属していてどちらも Visual Studio の Intellisense のようなコード補完機能もサポートされているので、この章では両方の SQL エディタについて紹介します。
DB Browser for SQLite の SQL エディタ
まず DB Browser for SQLite の SQL エディタで、SQL を入力すると fig. 20 のようになります。
fig.20 DB Browser for SQLite の SQL エディタ
fig. 20 の通り、DB Browser for SQLite の SQL エディタは以下のような特徴を持ちます。
- シンタックスハイライト対応（カラーは設定から変更可能）
- SQL キーワード補完
- データベースオブジェクト（テーブル名・カラム名）の補完にも対応
- 補完候補の確定は Visual Studio 等と同じく TAB キー、Enter キーのどちらでも可能
但し、DB Browser for SQLite のコード補完は Visual Studio の Intellisense のような部分一致ではなく、先頭一致の場合に表示されるようで、Ctrl + Space キーを押して候補リストを表示する事はできなさそうでした。（別に方法があるかもしれませんが、そこまで調べていませんｗ）
加えて、設定でキーワードは大文字で入力する（デフォルト値）ようになっていますが、『LIKE』はキャメルケースで補完されるようです…
SQLiteStudio の SQL エディタ
2 番手は SQLiteStudio の SQL エディタで、fig. 21 のようになります。
fig.21 SQLiteStudio の SQL エディタ
fig. 21 の通り、SQLiteStudio の SQL エディタは以下のような特徴を持ちます。
- シンタックスハイライト対応（カラー設定は無いようです）
- SQL キーワード補完
- データベースオブジェクト名（テーブル名・カラム名）の補完にも対応
- テーブル名のエイリアスから対象テーブルのカラム候補を出すことにも対応
- キーワード、オブジェクト名の補完や候補の表示は Ctrl + Space（Visual Studio 等と同じ）で可能（キーの変更は [Tools] – [Open configuration dialog]から可能）
- 複数候補が存在した場合（fig. 21 で『LIKE』を入力している箇所）の選択は Enter キーのみ可能で TAB では確定されない
fig. 21 だけでは分からないと思いますが【SELECT】等をキーワードを入力する際に、Ctrl + Space で補完入力しています。DB Browser for SQLite とほとんど差がない感じでしたが、定数値に色が設定されないのは少し不満でした。（DB Browser for SQLite 側は赤字になる）
参考：A5:SQL Mk-2 の SQL エディタ
DB Browser for SQLite と SQLiteStudio の SQL エディタを紹介したので、参考のために A5M2 の SQL エディタの動作も紹介しておきます。
fig.22 A5M2 の SQL エディタ
fig. 22 の通り A5M2 の SQL エディタは以下のような特徴があります。
- シンタックスハイライト対応（カラーは設定から変更可能）
- SQL キーワード補完（LIKE や ORDER 等は標準で未登録のようですが、キーワードは編集可能）
- データベースオブジェクト名（テーブル名・カラム名）の補完にも対応
- テーブル名のエイリアス + 区切り文字（ピリオド）の入力で対象テーブルのカラム候補を自動的に表示
- 補完候補は部分一致した候補を表示
- キーワード、オブジェクト名の補完や候補の表示は Ctrl + Space（Visual Studio 等と同じ）で可能
- 補完候補の確定は Visual Studio 等と同じく TAB キー、Enter キーのどちらでも可能
管理人は A5M2 を常用しているからと言う事もありますが、Visual Studio の Intellisense に 1 番近いのは A5M2 だと思いますし、やはり機能的に見ても 3 つの中で最強の SQL エディタは A5M2 だと思いました。
まとめ的な
ここまで、DB Browser for SQLite と SQLiteStudio を簡単に紹介してきましたが、使用した感じとしてはあまり差が感じられない結果になったと思います。最も大きい違いは日本語サポートの有無だと思うので、日本語の UI にこだわる場合は、DB Browser for SQLite 一択になります。
管理人の個人的な好みを言わせてもらうと、UI の仕様的には SQLiteStudio の方が好みなので、管理人の場合は SQLite のメンテナンスに SQLiteStudio を使用する場合が多いです。
ただ、前章の通り SQL エディタは A5M2 が圧倒的に使い易いので、やはり A5M2 最強！！と言ってしまいそうになりますが、A5M2 は汎用 DB 管理アプリなので SQLite に特化したアプリの方が便利な場面もあると思います。
管理人も現状では DB Browser for SQLite と SQLiteStudio の機能は全て確認できたとは言えない状況なので、今後も A5M2 と併用してしばらく使用してみるつもりです。
DB Browser for SQLite と SQLiteStudio を使用して、ブログのネタになりそうな機能等を見つけたら又、エントリを書こうと思います。
