FireDAC での SQLite の使用
DBMS の操作（FireDAC） への移動
この参照トピックは、以下のセクションから構成されます。
- SQLite 入門： SQLite の機能、含まれない機能、考えられる適用方法、SQLite に向かない適用方法を概説します。
- SQLite データベースの使用： Delphi アプリケーションで SQLite データベースを作成し、接続し、管理する方法を説明します。
- SQLite 暗号化データベース： データベース暗号化は SQLite の重要な機能の 1 つです。このセクションでは、この機能がどう動くかと、それを制御する方法を説明します。
- SQLite データ型： SQLite のデータ型体系は独特です。その動作を理解しておかなければ、Delphi アプリケーションでデータを効率的に格納し取得することは困難です。
- SQLite の SQL コマンド： Delphi アプリケーション開発者向けに、SQLite SQL ダイアレクトの主な特徴を説明します。
- SQLite のトランザクション、ロック、カーソル： SQLite 環境でのトランザクションの扱い方を説明します。
- SQLite エンジンの拡張： 組み込み DBMS である SQLite エンジンは、Delphi アプリケーション コードで拡張することができます。
- 高度な SQLite の手法： 最後に、更新ログや SQL 承認など、SQLite の高度な概念をいくつか紹介します。
このトピックでは、FireDAC の基礎と主要ライブラリ API に関する知識を前提としています。知識が不足している場合には、まず「ファースト ステップ」のトピックを読み、それから FireDAC\Samples\Getting Started\SQLite のデモをご覧ください。
目次
SQLite 入門
SQLite データベース
SQLite は、SQLite Consortium が開発した組み込み SQL データベース エンジンです。大まかに見積もって 5 億回もインストールされた、世界で最も幅広く配置されている DBMS です。すべての iOS および Android のモバイル デバイスや、Mac OS および Linux のデスクトップで使われています。また、Firefox、Skype、McAfee ウィルス対策ソフトウェアでも使われています。
SQLite の機能
こちらの Web サイトでは、以下が挙げられています。
- トランザクションは、システムがクラッシュして電源が落ちた場合でも、原子性、一貫性、独立性、耐久性という ACID 特性を保ちます。
- ゼロ構成： セットアップや管理は必要ありません。
- SQL92 のほとんどを実装しています。テーブル トリガおよびビューをサポートしています。
- データベース全体が、1 つのクロスプラットフォーム ディスク ファイルに格納されます。
- テラバイト規模のデータベースとギガバイト規模の文字列および BLOB をサポートしています。
- ほとんどの一般的な操作では、よく使われているクライアント/サーバー データベース エンジンよりも高速です。
- 自己完結型： 外部には依存しません。
- マルチデバイス： Windows、Mac OS X、iOS、Android がサポートされていて、特別な設定なしに使用できます。
- ソースはパブリック ドメインです。どのような目的で使用しても無料です。
- API が非常に強力なため、ほとんどすべての領域にエンジンを拡張できます。
- Delphi アプリケーションで使用できるファイル サーバー型組み込みクライアント/サーバー データベース エンジンの中でも、SQLite はデータ アクセスのパフォーマンスが最もよいものの 1 つです。ギガバイト規模のデータベースを複数使用したアプリケーションの成功例が多数あることがわかっています。たとえば、Silwood Technology 社の Saphir は、SQLite と Delphi と FireDAC を使って構築されています。
SQLite に含まれない機能
私たちの経験上、開発者からよく求められる機能のうち、SQLite で提供されていないものには、以下があります。
- ストアド プロシージャ -- FireDAC でカスタム関数 API を提供しています。
- 使用可能な組み込み関数群 -- FireDAC には 50 ほどの標準関数があらかじめインストールされています。
- セキュリティ システム（ユーザーや権限の概念を含む） -- FireDAC で、パスワードによって保護される暗号化データベースと、ユーザー アクションをフィルタリングするための特別なコールバックを用意しています。
- 照合順序（ASCII およびバイナリのみ） -- FireDAC ではカスタム照合順序を定義することができます。
SQLite の適用
こちらの Web サイトでは、以下が挙げられています。
- アプリケーション用ファイル形式 -- SQLite は、デスクトップ プラットフォーム用のオンディスク ファイル形式として、大きな成功を収めています。
- 組み込みデバイス/アプリケーション -- SQLite データベースではほとんど管理が必要ないため、人のサポートがなく無人で動作するデバイスやサービスにとって、SQLite は優れた選択肢となります。
- Web サイト -- 小～中程度のトラフィックの Web サイト（全 Web サイトの 99.9%）のデータベース エンジンとして、SQLite は通常は問題なく動作します。
- アドホック ディスク ファイルの代替 -- SQLite は、アドホック データ ファイルの代替としてまったく問題なく動作します。
- 内部/一時データベース -- データをインメモリ SQLite データベースに読み込んで本格的な SQL コマンドを使用することで、プログラムでさまざまな方法のデータのフィルタリングやソートを簡単に行うことができます。
- デモやテスト時のエンタープライズ データベースの代役。
- データベース教育。
SQLite に向かない適用方法
同じ Web サイトで、以下が挙げられています。
- 高い並行性が求められるもの -- SQLite ではデータベース ファイル全体に対して読み取り/書き込みロックをかけます。つまり、どれかのプロセスがデータベースのどこかの部分を読み取っていると、他のすべてのプロセスはデータベースの他のどの場所にも書き込むことができません。同様に、どれか 1 つのプロセスがデータベースに書き込みを行っていると、他のすべてのプロセスはデータベースの他のどの場所も読み取ることができません。
- クライアント/サーバー アプリケーション -- 多数のクライアント プログラムが共通のデータベースにネットワーク経由でアクセスする場合には、SQLite ではなくクライアント/サーバー データベース エンジンを使用することを検討してください。SQLite はネットワーク ファイル システム上でも動作しますが、ほとんどのネットワーク ファイル システムでは待ち時間が生じるため、理想的なパフォーマンスは得られません。
- 非常に大規模なデータセット（数テラバイト）。
SQLite データベースの使用
Delphi アプリケーションから SQLite データベースへの接続
FireDAC SQLite ドライバを Delphi アプリケーションにリンクするには、フォームまたはデータ モジュールに TFDPhysSQLiteDriverLink を追加します。SQLite データベースに接続するには、最低限、次の SQLite ドライバ パラメータを指定します。
DriverID=SQLite
Database=<path to SQLite database>
すべての SQLite ドライバ設定は、デフォルトで、安定した環境で 1 つの接続だけがデータベースにアクセスする場合にパフォーマンスが高くなるよう設定されています。PRAGMA コマンドを使用すると、SQLite を構成することができます。FireDAC SQLite ドライバ パラメータの多くが、プラグマに対応しています。さらに、SQLiteAdvanced を使用すると、複数のプラグマを ';' で区切ったものを 1 つの接続パラメータとして指定することができます。
その他の SQLite の使用例には次のようなものがあります。
| No | アプリケーションの特徴 | 説明 |
|---|---|---|
| 1 | 大規模な DB の読み取り | CacheSize のページ数（DB データをキャッシュするために使われるもの）を大きな数に設定します。キャッシュ全体のサイズは、CacheSize * <データベース ページ サイズ> になります。 |
| 2 | DB の排他更新 | JournalMode を WALに設定することを検討してください（詳細）。 |
| 3 | 長時間の更新トランザクション | CacheSize のページ数を大きい値に設定します。これにより、メモリ キャッシュにダーティ ページが増えることなく、多数の更新を含むトランザクションを実行できます。 |
| 4 | 少数の同時更新プロセス | LockingMode を Normal に設定して、共有 DB アクセスを有効にします。Synchronous を Normal または Full に設定すると、コミット済みのデータを他から見ることができるようになります。UpdateOptions.LockWait を True に設定すると、ロック待ちが有効になります。BusyTimeout を大きくすると、ロック待ちの時間を長くできます。JournalMode を WAL に設定することを検討してください。 |
| 5 | 少数の同時更新スレッド | （4）を参照。また、SharedCache を False に設定すると、ロック競合を最低限に抑えることができます。 |
| 6 | 少数の同時更新トランザクション | （4）または（5）を参照。また、TxOptions.Isolation を xiSnapshot または xiSerializible に設定すると、トランザクションのデッドロックの可能性を回避することができます。 |
| 7 | 高い安全性 | Synchronous を Full に設定すると、コミット済みのデータが失われないよう DB を保護することができます。また、（3）も参照してください。整合性を保つため、データベースの暗号化を検討してください。 |
| 8 | 高い機密性 | データベースを暗号化して機密性と整合性を向上します。 |
| 9 | 開発時間 | LockingMode を Normal に設定すると、IDE とデバッグ対象プログラムで SQLite DB を同時に使用できます。 |
Delphi アプリケーションでの SQLite データベースの作成
デフォルトで、SQLite データベースは、接続を確立したときにそのデータベースが存在しなければ作成されます。これを明示的に制御するには、Delphi アプリケーションで次のように指定することができます。
OpenMode=CreateUTF8 | CreateUTF16 | ReadWrite | ReadOnly
最初の 2 つの値は作成用で、新規データベースに使われるエンコーディングが異なります。また、複数行テーブルを含むデータベースの場合には、page_size を 4096 以上に設定することをお勧めします。そのためには、作成時に次のように指定します。
SQLiteAdvanced=page_size=4096
SQLiteAdvanced を使って次のパラメータを指定することを検討してください。
作成した直後の時点では、データベース ファイルのサイズはゼロです。そのため、データベースのエンコーディングやページ サイズなどの永続パラメータは、データベースに記録されていません。これらのパラメータを永続化するには、アプリケーションで少なくとも 1 つのテーブルを作成する必要があります。
Delphi アプリケーションでの SQLite インメモリ データベースの使用
次に紹介する SQLite 独特の機能は、純粋なインメモリ データベースを扱える機能です。つまり、データベース オブジェクトを格納するためのファイルは作成されず、すべてがメモリ中に保持されます。そうすることで、Delphi アプリケーションのセキュリティやパフォーマンスが向上し、環境に対する要件が少なくなります。
SQLite インメモリ データベースを作成して開くには、以下のパラメータを使用します。
DriverID=SQLite
Database=:memory:
あるいは、Database パラメータは空のままにしてもかまいません。
DriverID=SQLite
ある FireDAC の顧客は、SQLite データベースを共有ネットワーク リソース上に置いていました。そのデータベースは読み取り専用の製品カタログで、数多くの製品属性が格納されていました。パフォーマンスを根本的に向上するため、顧客は TFDSQLiteBackup を使ってデータベース全体をインメモリ データベースに移動しました。そのコード例を以下に示します。
FDConnection1.DriverName := 'SQLite';
FDConnection1.Open;
FDSQLiteBackup1.Database := '\\srv\db\data.sdb';
FDSQLiteBackup1.DestDatabaseObj := FDConnection1.CliObj;
FDSQLiteBackup1.DestMode := smCreate;
FDSQLiteBackup1.Backup;
Unicode と SQLite データベースの操作
Delphi における FireDAC の Unicode サポート（Delphi 2009 より開始）は、フルサポートです。SQLite の場合、これは以下のことを意味します。
- Delphi 2009 以降を使用している場合、FireDAC はすべてのメタデータを UTF-16 エンコーディングに変換するよう、SQLite データベースを自動的にセットアップします。Delphi 2007 以前では、メタデータは ANSI エンコーディングです。
- データは、「SQLite データ型から FireDAC データ型へのマッピング」のセクションで説明するように定義され変換されます。
Delphi アプリケーションでの複数の SQLite データベースの使用
SQLite では、1 つの接続内で複数のデータベースを使用することができます。Database パラメータで指定された DB がメイン データベースです。それ以外のデータベースをアタッチするには、Delphi アプリケーションで ATTACH コマンドを実行する必要があります。以下に例を示します。
FDConnection1.ExecSQL('ATTACH ''c:\hr.sdb'' AS hr');
FDConnection1.ExecSQL('ATTACH ''c:\cust.sdb'' AS cust');
FDQuery1.Open('select * from "Orders" o ' +
'left join hr."Employees" e on o.EmployeeID = e.EmployeeID ' +
'left join cust."Customers" c on o.CustomerID = c.CustomerID');
メモ： FireDAC はデータベース名をカタログ名と解釈します。
Delphi アプリケーションからの SQLite データベースの管理
優れた Delphi の SQLite（に限りませんが）データベース アプリケーションでは、以下の事実を意識しておく必要があります。
- SQLite データベースは、断片化する可能性があり、"ハードな" レコード更新/削除を何度も行った後では最適でない可能性があります。TFDSQLiteValidate.Sweep メソッドを呼び出すと、データベースが最適化されます。このメソッドは、VACUUM コマンドと PRAGMA auto_vacuum に対応します。以下に例を示します。
FDSQLiteValidate1.Database := 'c:\db.sdb';
FDSQLiteValidate1.Sweep;
- SQLite クエリ オプティマイザでは、最新のデータベース統計があるときの方が優れたクエリ実行計画が作成されます。SQLite ではこの統計が自動的に更新されません。TFDSQLiteValidate.Analyze メソッドを呼び出すと、統計情報が収集されます。このメソッドでは ANALYZE コマンドを使用します。アプリケーションで次のようにすると、データベース全体の統計情報を収集できます。
FDSQLiteValidate1.Database := 'c:\db.sdb';
FDSQLiteValidate1.Analyze;
- SQLite データベースが破損したり形式が不正になる可能性があります。整合性を検証するには、TFDSQLiteValidate.CheckOnly メソッドを使用します。壊れた SQLite データベースを修復するには、Delphi アプリケーションでバックアップから復元する必要があります。CheckOnly メソッドは、OnProgress イベント ハンドラを使用してその問題を通知します。このメソッドは PRAGMA integrity_check コマンドを実行します。
procedure TForm1.FDSQLiteValidate1Progress(ASender: TFDPhysDriverService; const AMessage: String);
begin
Memo1.Lines.Add(AMessage);
end;
FDSQLiteValidate1.Database := 'c:\db.sdb';
FDSQLiteValidate1.OnProgress := Form1Progress;
FDSQLiteValidate1.CheckOnly;
- データ損失を防ぐには、SQLite データベースのバックアップを定期的に実行する必要があります。TFDSQLiteBackup コンポーネントがデータベース バックアップ コピーを行います。次に示すのは、非常に簡単なバックアップ コードです。
FDSQLiteBackup1.Database := 'c:\db.sdb';
FDSQLiteBackup1.DestDatabase := 'c:\db.backup';
FDSQLiteBackup1.DestMode := smCreate;
FDSQLiteBackup1.Backup;
SQLite 暗号化データベース
アプローチ
SQLite の特徴である機能の 1 つに、強度の高い高速のデータベース暗号化があります。これによって、データベース ファイルの内容の機密性を向上し、データベース ファイルの整合性制御を強化することができます。
暗号化されたデータベースの形式は、同様の他の SQLite 暗号化拡張と互換性がありません。つまり、FireDAC 以外のライブラリで暗号化された暗号化データベースは使用できないということです。使用する必要がある場合には、元のツールを使ってデータベースを復号し、FireDAC を使って暗号化しなければなりません。
暗号化は、公式にサポートされた SQLite のアプローチ、つまり、カスタム コーデック コードと SQLITE_HAS_CODEC を定義したコンパイルによって提供されています。暗号化に関するルーチンはすべて、Delphi で実装され、sqlite3 コードに組み込まれています。そのため、以下に関しては暗号化が正しく処理されます。
- ATTACH
- VACUUM
- データベースの回復
- データベースのバックアップ
暗号化モード
| モード | 説明 | 用途 |
|---|---|---|
| AES-NNN | aes-NNN アルゴリズムは、AES-CTR と AES-CBC-MAC を汎用的に組み合わせたものです。それにより、機密性と整合性の両方が保証されます。つまり、正しいパスワードにアクセスできるエンティティだけが、暗号化されたデータベースのページを読んだり変更できるということです。このアルゴリズムにより、暗号化されたデータベースのページごとに 32 バイトの線形のオーバーヘッドが生じます。
このアルゴリズムを使用すると、データベースにデータを挿入しようという悪意ある試みのほとんどを検出できますが、試みを防止することはできず、変更を元に戻すのにも役立ちません。これは基本的に、頻繁なバックアップ作成を補完するためのものですが、攻撃の対象となったときやバックアップからのデータベースの復元が必要なときに警告を発するという点で、他のほとんどのデータベース暗号化スキームよりもずっと優れています。 aes-NNN アルゴリズム自体は、データベース末尾のページ全体の削除を検出できませんし（データベースの中間部分の削除は検出します）、同じパスワードを使って暗号化された旧バージョンにデータベースを戻すという攻撃を検出することもできません。 |
AES-NNN では、トップクラスの強力な機密性と整合性を提供しています。ただしそれには、他の暗号化モードと比べると明らかに大きい、パフォーマンスの一定の低下が伴います。 |
| AES-CTR-NNN | aes-ctr-NNN アルゴリズムは AES-CTR のみです。データベースの変更は検出されませんが、受動的攻撃に対する機密性は提供されます。つまり、攻撃側がパスワードにアクセスできず、変更に対してアプリケーションがどう反応するかを確認するためにデータベースを変更しようとしない限り、データはアプリケーションが許可する範囲内で秘密に保たれます。
もちろん、アルゴリズムによってデータベース ファイルが守られるのは、攻撃側が、たとえばデバッガを使って FireDAC アプリケーションに侵入し、その過程でパスワードを抜き出すことができない場合だけです。同様に、パスワードを構成ファイルに格納していたりソフトウェア自体の定数として持っている場合には、スキルの高くない攻撃者にとってすらパスワードを発見してセキュリティを危険にさらすことは簡単です。 |
AES-CTR-NNN では、トップクラスの強力な機密性を提供していますが、整合性は提供していません。そのため、パフォーマンスは高くなります。 |
| AEC-ECB-NNN | aes-ecb-NNN アルゴリズムは AES-ECB のみです。AES-NNN や AES-CTR-NNN と違って、データベースに対する変更は検出されず、受動的攻撃に対する高い機密性を提供することもありません。 | AES-ECB-NNN で提供している機密性は比較的低く、整合性は提供していません。ただし、暗号化モードの中ではパフォーマンスは最大になります。 |
NNN はキー サイズで、128 ビット、192 ビット、256 ビットのいずれかです。
暗号化のセットアップ
暗号化は以下を使って制御することができます。
- 接続定義パラメータ Encrypt、NewPassword、Password
- TFDSQLiteSecurity サービス コンポーネント
パスワード用接続定義パラメータの形式は以下のとおりです。
[aes-128 | aes-192 | aes-256 | aes-ctr-128 | aes-ctr-192 | aes-ctr-256 |
aes-ecb-128 | aes-ecb-192 | aes-ecb-256:] password
"aes-XXX-NNN:" は任意指定のプレフィックスで、これによって使用する暗号化アルゴリズムを指定します。指定しなければ、以下が使われます。
- Encrypt パラメータで指定されたアルゴリズム
- 何も指定されていなければ aes-256
FireDAC では以下の暗号化操作をサポートしています。
| 操作 | パラメータを使用する場合 | TFDSQLiteSecurity を使用する場合 |
|---|---|---|
| 暗号化されたデータベースを開く | Password=xxxx | --- |
| 暗号化されていないデータベースを暗号化する | NewPassword=xxxx | FDSQLiteSecurity1.Database := '...';
FDSQLiteSecurity1.Password := 'xxxx'; FDSQLiteSecurity1.SetPassword; |
| 暗号化されたデータベースのパスワードを変更する | Password=xxxx
NewPassword=yyyy |
FDSQLiteSecurity1.Database := '...';
FDSQLiteSecurity1.Password := 'xxxx'; FDSQLiteSecurity1.ToPassword := 'yyyy'; FDSQLiteSecurity1.ChangePassword; |
| 暗号化されたデータベースを復号する | Password=xxxx
NewPassword= |
FDSQLiteSecurity1.Database := '...';
FDSQLiteSecurity1.Password := 'xxxx'; FDSQLiteSecurity1.RemovePassword; |
| 暗号化データベースの状態を確認する | ---
NewPassword= |
FDSQLiteSecurity1.Database := '...';
FDSQLiteSecurity1.Password := 'xxxx'; ShowMessage(FDSQLiteSecurity1.CheckEncryption); |
SQL 拡張
ATTACH コマンドは拡張されています。ATTACH の現在の完全な構文は次のとおりです。
ATTACH [DATABASE] 'filename' [AS name] [KEY 'password']
KEY を省略すると、パスワード値はメイン データベースから継承されます。暗号化されていないデータベースをアタッチするために空のパスワードを指定するには、次のようなコマンドを使用します。
ATTACH 'D:\tmp\test.db' AS tst KEY ''
SQLite データ型
SQLite データ型から FireDAC データ型へのマッピング
SQLite のデータ型体系では "型指定が不要" です。これは事実上、任意の識別子を列のデータ型名として使用できるということです。たとえば、"Delphi" も使用することができ、文字列データ型に対応します。この SQLite のアプローチを、他の DBMS や Delphi と互換性が高く、Delphi アプリケーション開発者が馴染んだものにできるよう、FireDAC では以下の表に示すデータ型名を認識します。
| 型名 | 説明 |
|---|---|
| ROWID | _ROWID_ | OID | dtInt64、Attrs = [caSearchable, caAllowNull, caROWID] |
| BIT | BOOL | BOOLEAN | LOGICAL | YESNO | dtBoolean |
| TINYINT | SHORTINT | INT8 [UNSIGNED] | dtSByte/dtByte |
| BYTE | UINT8 | dtByte |
| SMALLINT | INT16 [UNSIGNED] | dtInt16/dtUInt16 |
| WORD | UINT16 | YEAR | dtUInt16 |
| MEDIUMINT | INTEGER | INT | INT32 [UNSIGNED] | dtInt32/dtUInt32 |
| LONGWORD | UINT32 | dtUInt32 |
| BIGINT | INT64 | COUNTER | AUTOINCREMENT | IDENTITY [UNSIGNED] | dtInt64/dtUInt64 |
| LONGLONGWORD | UINT64 | dtUInt64 |
| REAL | FLOAT | DOUBLE | dtDouble |
| SINGLE [PRECISION] [(P, S)] | dtSingle/dtBCD/dtFmtBCD |
| DECIMAL | DEC | NUMERIC | NUMBER [UNSIGNED] [(P, S)] | dtSByte/dtInt16/dtInt32/dtInt64
dtByte/dtUInt16/dtUInt32/dtUInt64 dtBCD/dtFmtBCD |
| MONEY | SMALLMONEY | CURRENCY | FINANCIAL [(P, S)] | dtCurrency |
| DATE | SMALLDATE | dtDate |
| DATETIME | SMALLDATETIME | dtDateTime |
| TIMESTAMP | dtDateTimeStamp |
| TIME | dtTime |
| CHAR | CHARACTER [(L)] | dtAnsiString、Len = L、Attrs = [caFixedLen] |
| VARCHAR | VARCHAR2 | TYNITEXT | CHARACTER VARYING | CHAR VARYING [(L)] | dtAnsiString、Len = L |
| NCHAR | NATIONAL CHAR | NATIONAL CHARACTER [(L)] | dtWideString、Len = L、Attrs = [caFixedLen] |
| NVARCHAR | NVARCHAR2 | NATIONAL CHAR VARYING | STRING [(L)] | dtWideString、Len = L |
| RAW | TYNIBLOB | VARBINARY | BINARY | BINARY VARYING [(L)] | dtByteString、Len = L |
| BLOB | MEDIUMBLOB | IMAGE | LONGBLOB | LONG BINARY | LONG RAW | LONGVARBINARY | GENERAL | OLEOBJECT | TINYBLOB | dtBlob |
| MEDIUMTEXT | LONGTEXT | CLOB | MEMO | NOTE | LONG | LONG TEXT | LONGCHAR | LONGVARCHAR | TINYTEXT | dtMemo |
| TEXT | NTEXT | WTEXT | NCLOB | NMEMO | LONG NTEXT | LONG WTEXT | NATIONAL TEXT | LONGWCHAR | LONGWVARCHAR | HTML | dtWideMemo |
| XMLDATA | XMLTYPE | XML | dtXML |
| GUID | UNIQUEIDENTIFIER | dtGUID |
| その他のデータ型 | dtWideString |
メモ： SQLite では、すべての文字列データ型に対して FormatOptions.StrsTrim を使用できます。
特別な SQLite データ型
テーブルに自動インクリメント列を追加するには、列を INTEGER PRIMARY KEY AUTOINCREMENT として定義します。この型は、dtInt32、Attrs = [caAutoInc] にマッピングされます。自動インクリメント列の扱い方の詳細は、「自動インクリメント フィールド」を参照してください。
型名が ROWID、_ROWID_、または OID の列は、行を識別するための列と見なされます。これらの型は、dtInt64、Attrs = [caSearchable, caAllowNull, caROWID] にマッピングされます。行を識別するための列の扱い方の詳細は、「一意識別フィールド」を参照してください。特定の行にアクセスするには、SQLite ROWID を使用するのが最も速い方法です。
SELECT * FROM Orders WHERE ROWID = :RID
FireDAC のマッピングの調整
SQLite ドライバ パラメータによっては、Delphi アプリケーションでデータ表現を調整することができます。
| パラメータ | 説明 |
|---|---|
| StringFormat = Choose | Unicode | ANSI | Unicode の場合、dtAnsiString および dtMemo はすべて、クライアントに対して dtWideString および dtWideMemo として表現されます。 ANSI の場合、dtAnsiString および dtMemo はすべて、クライアントに対して dtWideString および dtWideMemo として表現されます。 Choose の場合、文字列型は型名を使用して定義されます。 |
| GUIDFormat = String | Binary | Binary の場合、dtGUID はデータベース内に TGUID バイナリ値として格納されます。String の場合、{xxxxxxx} 形式の文字列として格納されます。Binary の方が DB 内で必要な領域が少なく、String の方が可読性が高くなります。 |
| DateTimeFormat = String | Binary | DateTime | Binary の場合、dtDate、dtTime、dtDateTime はユリウス日形式の double 値としてデータベースに格納されます。 String の場合、'yyyy-mm-dd hh24:mi:ss.zzz' 形式の文字列として格納されます。 DateTime の場合には、Delphi 日時形式の double 値としてになります。Binary は、DB ではより少ない領域で済み、String は、SQLite SQL 式でより便利であり、DateTime は、既存の Delphi アプリケーションとの互換性がある可能性が高いです。 |
メモ： データベースが空でないときに GUIDFormat や DateTimeFormat を変更すると、格納された値を FireDAC が読み取ったり解析することができず、エラーが発生する可能性があります。
SELECT リスト内の式に関して、SQLite からは型名の情報が含められません。結果セットが空でなければ、FireDAC は最初のレコードの値のデータ型を使用します。空の場合には、FireDAC はその列を dtWideString とします。列のデータ型を明示的に指定するには、列エイリアスの前に ::<型名> を付加します。
SELECT count(*) as "cnt::INT" FROM mytab
Delphi アプリケーションで SQLite ネイティブのデータ型表現が必要であれば、FireDAC マッピング ルールを使用してください。たとえば、TEXT の列を dtAnsiString に、INT の列を dtInt64 にマッピングします。
with FDQuery1.FormatOptions do begin
OwnMapRules := True;
with MapRules do begin
SourceDataType := dtMemo;
TargetDataType := dtAnsiString;
end;
with MapRules do begin
SourceDataType := dtInt32;
TargetDataType := dtInt64;
end;
end;
高精度の数
SQLite の型体系が原因で、長い数（20 桁以上）は REAL 型に変換できると見なされます。そのため、値は精度が 15 桁の REAL 型（Delphi の double 型）に丸められます。この問題を解決するには、アプリケーションで TEXT または類似のデータ型を使用し、マッピング ルールによって dtAnsiString を dtFmtBCD または類似のデータ型にキャストします。これは FireDAC の問題ではなく、現在のところ、他の解決方法はありません。
SQLite の SQL コマンド
SQL ダイアレクト
SQLite は ANSI SQL 92 にほとんど準拠していますが、一部の機能やコマンドはサポートしておらず、いくつかの強力な機能やコマンドが追加されています。SQLite SQL ダイアレクトの詳細は、以下で確認することができます。
- SQL As Understood By SQLite（SQLite が理解する SQL）
- SQLite / ANSI SQL Commands and Features Not Supported（サポートされていない SQLite/ANSI SQL コマンドおよび機能）
SQLite SQL コマンド バッチ
FireDAC SQLite ドライバでは SQL コマンド バッチをサポートしています。SQL コマンド間は ';' で区切って指定しなければなりません。SQLite では、DDL や DML など、どのようなコマンドでもバッチ内に混在させることができます。以下に例を示します。
with FDQuery1.SQL do begin
SQL.Clear;
SQL.Add('create table dbms (id integer, name varchar(20));');
SQL.Add('insert into tab values (1, ''sqlite'');');
SQL.Add('insert into tab values (2, ''mysql'');');
SQL.Add('insert into tab values (3, ''firebird'');');
SQL.Add('create table langs (id integer, name varchar(20));');
SQL.Add('insert into tab values (1, ''delphi'');');
SQL.Add('insert into tab values (2, ''c'');');
SQL.Add('insert into tab values (3, ''c++'');');
SQL.Add('select * from dbms;');
SQL.Add('select * from langs;');
end;
FDQuery1.Open;
// DBMS リストをここで処理する
FDQuery1.NextRecordSet;
// プログラミング言語のリストをここで処理する
SQL スクリプト ダイアレクト
FireDAC TFDScript では、スクリプト制御コマンドが '.' で始まる SQLite 構文をサポートしていません。
配列 DML
SQLite バージョン 3.7.11 以降では、複数の値を含んだ VALUES 句を持つ INSERT コマンドをサポートしています。FireDAC では、Params.BindMode = pbByNumber の場合に、この機能を使って配列 DML を実装します。それ以外の場合には、FireDAC は配列 DML をエミュレートします。以下に例を示します。
// here ADQuery1.Params collection is filled by 3 parameters
FDQuery1.SQL.Text := 'insert into MyTab values (:p1, :p2, :p3)';
// set "by number" parameter binding mode
FDQuery1.Params.BindMode := pbByNumber;
FDQuery1.Params.ArraySize := 100;
for i := 0 to FDQuery1.Params.ArraySize - 1 do begin
FDQuery1.Params[0].AsIntegers[i] := i;
FDQuery1.Params[1].AsStrings[i] := 'qwe';
FDQuery1.Params[2].Clear(i);
end;
FDQuery1.Execute(FDQuery1.Params.ArraySize);
SQLite のトランザクション、ロック、スレッド、カーソル
ロックと同時更新
SQLite 自体の以下の記事を参照してください。
- BEGIN TRANSACTION
- SQLite Shared-Cache Mode（SQLite 共有キャッシュ モード）
- Corruption Following Busy（ビジーが出た後の破損）
ファイル サーバー型 DBMS である SQLite では、更新時にデータベース テーブルがロックされます。以下の設定が同時アクセスに影響します。
- 複数のスレッドが同じデータベースを更新している場合には、SharedCache 接続パラメータを False に設定します。これにより、いくらかのデッドロックを回避することができます。
- 複数のプロセスまたはスレッドが同じデータベース テーブルを更新している場合には、LockingMode を Normal に設定してテーブルに対する同時アクセスを有効にします。また、Synchronous 接続パラメータを Full または Normal に設定します。そうすることで、SQLite はトランザクションが終了した直後にデータベース ファイルを更新するようになり、他の接続はその更新内容を予測可能な頻度で確認することができます。
- 接続間のロック競合を回避するには、UpdateOptions.LockWait を True に、BusyTimeout を大きい値に設定します。
- 実行に時間のかかる更新トランザクション間のロック競合を回避するには、TADConnection.TxOptions.Isolation を xiSnapshot または xiSerializible に設定します。
トランザクションと分離モード
SQLite では、通常のトランザクションとネストしたトランザクション（チェックポイント）とをサポートしています。複数トランザクションはサポートしていません。以下の表は、SQLite でサポートされている分離モードです。
| モード | 対応するステートメント |
|---|---|
| xiDirtyRead | PRAGMA read_uncommitted = 1 |
| xiReadCommitted | BEGIN TRANSACTION DEFERRED |
| xiRepeatableRead | xiReadCommitted と同じ |
| xiSnapshot | BEGIN TRANSACTION IMMEDIATE |
| xiSerializible | BEGIN TRANSACTION EXCLUSIVE |
トランザクションと DML コマンド
書き込みコマンドをトランザクションで囲むと、SQLite のパフォーマンスが劇的に向上することがあります。これは大規模なデータ変更時に顕著です。同じことが FireDAC の配列 DML 機能にも当てはまります。そのため、次のようにデータ変更コードをトランザクションで囲んで、パフォーマンスを最大限に向上してください。
FDConnection1.StartTransaction;
try
FDQuery1.SQL.Text := 'insert into tab values (:id, :name)';
FDQuery1.Params.ArraySize := 10;
for i := 0 to FDQuery1.Params.ArraySize - 1 do begin
FDQuery1.Params[0].AsIntegers[i] := i;
FDQuery1.Params[0].AsStrings[i] := 'name' + IntTostr(i);
end;
FDQuery1.Execute(FDQuery1.Params.ArraySize, 0);
FDConnection1.Commit;
except
FDConnection1.Rollback;
raise;
end;
トランザクションとカーソル
SQLite では、まだ取得されていない結果セットがコマンドに残っている場合に、トランザクションをロールバックすることができません。これを回避するため、FireDAC では Rollback メソッドの呼び出し時に結果セットに残っているレコードをすべて取得します。FetchOptions.AutoFetchAll を参照してください。
SQLite エンジンの拡張
カスタム関数
SQLite では、ストアド プロシージャや関数の概念をサポートしていません。これは、ホスト言語環境を使ってエンジン機能を拡張できるようにしているためです。SQLite では、ホスト言語の関数を SQLite エンジンに登録して、それを SQL コマンド内で使用することができます。FireDAC では、TFDSQLiteFunction コンポーネントを導入することでその処理を簡略化しています。
関数を作成するには、FunctionName と ArgumentsCount を設定し、OnCalculate イベント ハンドラを作成する必要があります。Active を True に設定すると、カスタム関数が SQLite エンジンに登録されます。以下に例を示します。
procedure TForm1.FDSQLiteFunction1Calculate(AFunc: TSQLiteFunction;
AInputs: TSQLiteInputs; AOutput: TSQLiteOutput; var AUserData: TObject);
begin
AOutput.AsInteger := AInputs[0].AsInteger * AInputs[1].AsInteger;
end;
FDSQLiteFunction1.DriverLink := FDPhysSQLiteDriverLink1;
FDSQLiteFunction1.FunctionName := 'XmY';
FDSQLiteFunction1.ArgumentsCount := 2;
FDSQLiteFunction1.OnCalculate := FDSQLiteFunction1Calculate;
FDSQLiteFunction1.Active := True;
この関数は次のように使用します。
FDQuery1.Open('select RegionID, XmY(RegionID, 10) from "Region"');
関数から FireDAC メソッドを呼び出してデータベースに対するクエリを実行できます。デフォルトまたは異なる数の引数を持つカスタム関数を作成するには、FunctionName を同じにし、異なる数の引数を指定する必要があります。これにより、SQLite エンジンにオーバーロード関数が登録されます。
上記を含む関数のサンプルは、FireDAC\Samples\DBMS Specific\SQLite\UserFunc フォルダにあります。
FireDAC では、50 ほどの関数を実装して SQLite 接続にインストールしています。これらの関数は多くの DBMS にとって事実上の標準であり、FireDAC のローカル式エンジンで実装されています。SQLite 接続を実行時に作成するには、"uses" 句に FireDAC.Stan.ExprFuncs ユニットを追加する必要があります。追加しなければ次の例外が発生します。
[FireDAC][Phys][SQLite] ERROR: no such function: UCASE.
設計時にカスタム関数を使用できるようにするには、データ モジュールを使ってカスタム設計時パッケージを作成し、コンポーネントをそのモジュールにドロップし、適切にセットアップを行います。モジュールは、モジュール ユニットの initialization セクションで作成し、finalization セクションで破棄します。その後、パッケージを Delphi IDE にインストールします。
Ron Grove の動画も参照してください。
カスタム照合順序
SQLite では、OpenMode 接続パラメータに応じて UTF8 または UTF16 ですべての文字データを処理します。文字データを比較またはソートする必要が生じた場合、SQLite はそのためにどのルールを使用するかを知っておかなければなりません。このルールを照合順序といいます。
SQLite にはいくつかの照合順序が組み込まれています。しかし、その中のどれも、ドイツ語やキリル文字やアラビア語の語句を正しくソートすることができません。TFDSQLiteCollation コンポーネントを使って独自の照合順序を作成する必要があります。CollationName、Flags、LocaleName を設定し、それから Active を True に設定して照合順序を SQLite エンジンに登録します。以下に例を示します。
FDSQLiteCollation1.DriverLink := FDPhysSQLiteDriverLink1;
FDSQLiteCollation1.CollationName := 'UTF16NoCase';
FDSQLiteCollation1.Flags := [sfIgnoreCase];
FDSQLiteCollation1.Active := True;
デフォルトの CollationKind=scCompareString を使った上記のコンポーネントのセットアップでは、標準の大文字/小文字を区別しない Unicode 照合順序を実装しています。アプリケーションで、CollationKind=scCustomUTF16 または scCustomUTF8 を使用し、OnCompare イベント ハンドラを実装することで、カスタム照合順序を実装できます。次に示すのは、この照合順序の使い方です。
SELECT * FROM "Employees" ORDER BY LastName COLLATE UTF16NoCase
列のデフォルト照合順序は、次のようにして指定します。
CREATE TABLE IF NOT EXISTS test_col (f1 VARCHAR(10) COLLATE UTF16NoCase)
メモ： 接続やデータベースやテーブルにデフォルト照合順序を指定することはできません。上記の照合順序のサンプルは、FireDAC\Samples\DBMS Specific\SQLite\UserCollation フォルダにあります。
カスタム照合順序を使用しない場合、SQLite ではデフォルトでバイナリ ソート順序が使われます。TFDTable のライブ データ ウィンドウ モードでは、クライアント側とデータベースのソート順序を同じにしておくことが大切です。クライアント側のバイナリ ソート順序を有効にするには、FormatOptions.SortLocale を 0 に設定します。
データベース イベント
FireDAC では、SQLite データベース トリガから何らかのイベント（データ変更など）について Delphi アプリケーションに通知を送ることができます。これをサポートするために、FireDAC では Firebird と似たアプローチを採用し、POST_EVENT カスタム関数を登録しています。これをトリガから呼び出すには次のようにします。
CREATE TRIGGER update_orders UPDATE ON "Orders"
BEGIN
SELECT POST_EVENT('Orders');
END;
イベント通知を受け取るには、Delphi アプリケーションで TFDEventAlerter コンポーネントを使用します。以下に例を示します。
FDEventAlerter1.Names.Text := 'Orders';
FDEventAlerter1.Options.Synchronize := True;
FDEventAlerter1.OnAlter := DoAlert;
FDEventAlerter1.Active := True;
procedure TForm1.DoAlert(ASender: TFDCustomEventAlerter;
const AEventName: String; const AArgument: Variant);
begin
if CompareText(AEventName, 'Orders') = 0 then
qryOrders.Refresh;
end;
カスタム データ ソース
ローカル SQL エンジンの場合は、SQL クエリ内で TDataSet の下位クラスを使用することができます。FireDAC では SQLite 仮想テーブル API を使用してローカル SQL を実装しています。
高度な SQLite の手法
データベース更新のフック
SQLite では、データベースに対するすべての更新を監視するための独自の API を提供しています。この機能を使用して、たとえば DB に対するすべての更新のログを記録するようなことができます。Delphi アプリケーションでこの API を使用するには、TSQLiteDatabase オブジェクト（データベース接続をラップするオブジェクト）の OnUpdate イベント ハンドラを設定する必要があります。データベース接続を開いた後で、このイベントをフックします。以下に例を示します。
procedure TForm1.DoUpdate(ADB: TSQLiteDatabase; AOper: Integer; const ADatabase, ATable: String; ARowid: sqlite3_int64);
begin
Memo1.Lines.Add(Format('%d - %s - %s - %u', [AOper, ADatabase, ATable, ARowid]));
end;
FDConnection1.Connected := True;
TSQLiteDatabase(FDConnection1.ConnectionIntf.CliObj).OnUpdate := DoUpdate;
このサンプルは、FireDAC\Samples\DBMS Specific\SQLite\OnUpdate フォルダにあります。
データベース アクセス権の制御
SQLite は組み込み DBMS です。つまり、シングル ユーザー DBMS なので、ユーザーやアクセス権などの概念は必要ありません。ただし、アプリケーションによってはアクセス権の制御が役に立つことがあります。たとえば次のような場合です。
- アプリケーションでエンド ユーザー ライセンスによって権限を制限する場合。デモ ライセンスであれば権限を少なく、フル ライセンスであればすべての権限を与えるなど。
- 多層データ アクセス フレームワークで、独自のユーザーの概念を使用し、何らかの汎用的な方法でデータのアクセス権を制御する場合。
これに関しても、SQLite には SQL コマンドに権限付与する（またはしない）ための独自の機能が備わっています。Delphi アプリケーションでこの API を使用するには、TSQLiteDatabase オブジェクト（データベース接続をラップするオブジェクト）の OnAutorize イベント ハンドラを設定する必要があります。データベース接続を開いた後で、このイベントをフックします。以下に例を示します。
procedure TForm1.DoAuthorize(ADB: TSQLiteDatabase; ACode: Integer; const AArg1, AArg2, AArg3, AArg4: String; var AResult: Integer);
begin
Memo1.Lines.Add(Format('%d - %s - %s - %s - %s', [ACode, AArg1, AArg2, AArg3, AArg4]));
// Deny any delete operation
if ACode = SQLITE_DELETE then
AResult := SQLITE_DENY
else
AResult := SQLITE_OK;
end;
FDConnection1.Connected := True;
TSQLiteDatabase(FDConnection1.ConnectionIntf.CliObj).OnAutorize := DoAuthorize;
このサンプルは、FireDAC\Samples\DBMS Specific\SQLite\OnAuthorize フォルダにあります。
SQLite の低レベル API の使用
SQLite のデータ アクセスのパフォーマンスを最大限に向上するには、FireDAC SQLite API をラップするクラスを使用することを検討してください。これは、オブジェクト指向の低レベルのシン API で、FireDAC SQLite ドライバで使われているものです。この API のドキュメントは作成されておらず、公式にはサポートされていません。
以下の例では、トランザクションを制御し、パラメータを取る SELECT コマンドを使ってレコードを取得する方法を示します。
uses
FireDAC.Phys.SQLiteWrapper;
procedure TForm1.FormCreate(Sender: TObject);
var
oDB: TSQLiteDatabase;
oTran: TSQLiteStatement;
oStmt: TSQLiteStatement;
i: Integer;
begin
FDConnection1.Connected := True;
oDB := TSQLiteDatabase(FDConnection1.CliObj);
oTran := TSQLiteStatement.Create(oDB);
try
// トランザクションを開始する
oTran.Prepare('BEGIN');
oTran.Execute;
oStmt := TSQLiteStatement.Create(oDB);
try
// ステートメントを準備する
oStmt.Prepare('select * from "Orders" where OrderID > :ID1 and OrderID < :ID2');
// バインド変数（パラメータ）を追加する
for i := 1 to oStmt.ParamDefsCount do
TSQLiteBind.Create(oStmt.Params);
// 列変数（フィールド）を追加する
for i := 1 to oStmt.ColumnDefsCount do
TSQLiteColumn.Create(oStmt.Columns).Index := i - 1;
// パラメータ値を設定して実行する
oStmt.Params[0].AsInteger := 11000;
oStmt.Params[1].AsInteger := 12000;
oStmt.Execute;
// レコードを取得して列を読み取る
while oStmt.Fetch do
Memo1.Lines.Add(Format('OrderID: %d, CustomerID: %s',
[oStmt.Columns[0].AsInteger, oStmt.Columns[1].AsString]));
finally
oStmt.Free;
end;
// トランザクションをコミットする
oTran.Unprepare;
oTran.Prepare('COMMIT');
oTran.Execute;
finally
oTran.Free;
end;
end;
関連項目
- SQLite データベースへの接続
- モバイル チュートリアル：モバイル アプリケーションで FireDAC を使用する（iOS および Android）
- モバイル チュートリアル：FireDAC と SQLite を使用する（iOS および Android）
サンプル
- FireDAC SQLite サンプル