ここまでデータベース作成、テーブル追加、データ登録を行ってきたわけですが、そろそろ読むのも面倒になってきたと思いますのでサンプルデータベースを使い動作させてみます
実際にデモサイトのWordPress プラグインアンテナ Yet Another Plugin directory で使っているデータベースをサンプルに手を動かしてみましょう (^^)
ダウンロード
[wpdm_file id=47]
2016/05/18 時点でプラグインデータのデータベースデータです
ダウンロードしたZipファイルを解凍して plugins_fts.db3 データベースファイルを SQLiteSpy 等のツールでオープンすれば、plugins_author, plugins_base, plugins_fts のテーブルと定義されているカラムが確認出来ます
後は、SQL文入力して実行させれば結果が表示されるので、いろいろ試すことが出来ます
データ取得
通常の検索（FTS以外）
SELECT文を使い指定した条件のデータをテーブルから取得することが出来ます
SQL文を入力して各テーブルのデータを表示させてみてください
SELECT * FROM plugins_author SELECT * FROM plugins_base SELECT * FROM plugins_fts
各々のテーブルに登録されているデータを見ることが出来ます
また、最下行のところに実行時間やデータ数が表示されます
条件検索
WHERE：SELECT文でデータを取得するときにWHERE句等で条件を付けて必要なデータだけを取得することが出来ます
例えば、4/1以降に登録されたプラグインをダウンロード数が多い順に取得する場合
SELECT * FROM plugins_base As p WHERE p.added > '2016-04-01 00:00:00' ORDER BY p.downloaded DESC LIMIT 0, 10
WHEREで設定可能な条件に関しては下記サイトが参考になります
SQLite WHERE – Filter Rows in a Result Set
SQLite TutorialFTSの検索
FTS検索では、SELECTクエリーに MATCH演算子を使い検索します。
また、DOCID（ROWID列の別名）が明示/暗黙的に付けられているので、それを使いことも出来ます
FTS検索は大文字と小文字を区別せずに行われます（トークナイザ依存なので変更は可能）
※FTSの検索はテーブル内の指定カラムに対して通常行いますが、FTSテーブルにはテーブル名と同じ名前の仮想的な非表示のカラムが設けられているので、テーブル名と同じ仮想カラムを指定することでテーブル全体から検索することが出来ます
※FTSテーブルの検索は、MATCH演算子を使った検索が高速に行われるように実装されていますので、検索条件の指定によっては時間がかかります
1.4. Simple FTS Queries に条件設定による実行時間の違いが紹介されています
— The examples in this block assume the following FTS table:
CREATE VIRTUAL TABLE mail USING fts3(subject, body);
SELECT * FROM mail WHERE rowid = 15; — Fast. Rowid lookup.
SELECT * FROM mail WHERE body MATCH ‘sqlite’; — Fast. Full-text query.
SELECT * FROM mail WHERE mail MATCH ‘search’; — Fast. Full-text query.
SELECT * FROM mail WHERE rowid BETWEEN 15 AND 20; — Slow. Linear scan.
SELECT * FROM mail WHERE subject = ‘database’; — Slow. Linear scan.
SELECT * FROM mail WHERE subject MATCH ‘database’; — Fast. Full-text query.
条件検索
MATCH演算子で行う検索キーワードは AND, OR, NOT 等の条件を組み合わせて指定することが可能です。
※検索に 括弧、AND, OR, NOT が使えるかは、SQLiteコンパイル時のFTS機能のオプション
DSQLITE_ENABLE_FTS3_PARENTHESIS により決まります。このオプションが指定されていない場合は標準モードの検索となり指定方法や演算の優先度が変わるので注意です
| モード | 括弧 | AND条件 | OR条件 | NOT条件 | 演算優先度 |
| 標準 | ☓ | スペース区切り | OR | NOTの代わりに接頭辞「 – 」でそのワードを含まない事を指定 |
|
| 拡張 | ○ | AND 又はスペース区切り | OR | NOT |
|
※デモサイトで使用している pdo_sqlite モジュールは拡張モードでコンパイルしてあるので、AND, OR, NOT, 括弧 を検索を行えますが、 SqliteBrowser や SQLiteSpy 等のツールを試したところ標準モードでコンパイルされたライブラリが使用されているようです
以下画像は 標準モードを使った場合 の結果画面です
AND検索
プラグイン名に google と photo が入っているプラグインを取得します
検索ワードの順番は関係ないので ‘google photos’ と ‘photos google’ は同じ結果となります
SELECT docid,* FROM plugins_fts AS f WHERE f.names MATCH 'google photos'
OR検索
プラグイン名に google または photo が入っているプラグインを取得します
SELECT docid,* FROM plugins_fts AS f WHERE f.names MATCH 'google OR photos'
NOT検索
プラグイン名に google と photo が入っていて picasa が含まれていないプラグインを取得します
SELECT docid,* FROM plugins_fts AS f WHERE f.names MATCH 'google photos -picasa'
※拡張モードの場合は、検索ワードを ‘google photos NOT picasa’ に変更します
AND検索から docid 12558 のデータが picasa が入っていたので除外されたのが分かります
PHRASE フレーズ検索
プラグイン名に google photo というフレーズ（並びも含めたトークン文字列）が入っているプラグインを検索して取得します。フレーズはダブルクオーテーション ” で囲みます
SELECT docid,* FROM plugins_fts AS f WHERE f.names MATCH '"google photos"'
AND検索から docid 12558 がフレーズとしてマッチせず（並びの不一致）にデータが除外されたのが分かります
接頭辞のみで残りを * により任意も文字にマッチさせる検索
SELECT docid,* FROM plugins_fts AS f WHERE f.names MATCH 'g* photo'
この場合は google や gallery 等に幅広くマッチします
最初に現れるトークンにマッチさせる検索
検索ワードの前に ^ を指定すると最初に現れるトークンを限定してマッチさせることが出来ます
SELECT docid,* FROM plugins_fts AS f WHERE f.names MATCH '^google photo'
その他
NEAR を使ってトークン間の距離を指定した検索も出来るようです
また、拡張モードを使えば、括弧も使えるのでさらに複雑な条件で検索することが出来ます
エスケープ
全文検索では、検索ワードをシングルクオテーション( ‘ )で括ります。
しかし、検索文字列中にシングルクオテーション( ‘ )があるとエラーになってしまいますので、シングルクオテーションを２つ続けることで１つのシングルクオテーションとして認識されます
FTSテーブルの検索だけでは、マッチしたレコードデータと docid が取得出来るだけですので、次はテーブルを結合してその他のプラグインデータを合わせて取得してみましょう
次のページに続きます (^^)
