SQLite3 (PDO) を使った全文検索 (FTS) 入門

 

ここまでデータベース作成、テーブル追加、データ登録を行ってきたわけですが、そろそろ読むのも面倒になってきたと思いますのでサンプルデータベースを使い動作させてみます

実際にデモサイトのWordPress プラグインアンテナ Yet Another Plugin directory で使っているデータベースをサンプルに手を動かしてみましょう (^^)

ダウンロード

sqlite FTS Sample Database File

Download63 downloads

 

2016/05/18 時点でプラグインデータのデータベースデータです

ダウンロードしたZipファイルを解凍して plugins_fts.db3 データベースファイルを SQLiteSpy 等のツールでオープンすれば、plugins_author, plugins_base, plugins_fts のテーブルと定義されているカラムが確認出来ます

後は、SQL文入力して実行させれば結果が表示されるので、いろいろ試すことが出来ます

SQLite_sample1

 

データ取得

通常の検索(FTS以外)

SELECT

SELECT文を使い指定した条件のデータをテーブルから取得することが出来ます

SQL文を入力して各テーブルのデータを表示させてみてください

SELECT * FROM plugins_author
SELECT * FROM plugins_base
SELECT * FROM plugins_fts

各々のテーブルに登録されているデータを見ることが出来ます
また、最下行のところに実行時間やデータ数が表示されます

SQLite_sample2

条件検索

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

SQLite_sample3

WHEREで設定可能な条件に関しては下記サイトが参考になります

SQLite WHERE – Filter Rows in a Result Set
This tutorial shows you how to use SQLite WHERE clause to filter rows in a result set returned by the SELECT statement.
FTSの検索

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の代わりに接頭辞「 – 」でそのワードを含まない事を指定
  1. “-“
  2. OR
  3. AND
拡張 AND 又はスペース区切り OR NOT
  1. NOT
  2. AND
  3. OR

※デモサイトで使用している 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'

SQLite_sample4

 

OR検索

プラグイン名に google または photo が入っているプラグインを取得します

SELECT docid,* FROM plugins_fts AS f WHERE f.names MATCH 'google OR photos'

SQLite_sample5

 

NOT検索

プラグイン名に google と photo が入っていて picasa が含まれていないプラグインを取得します

SELECT docid,* FROM plugins_fts AS f WHERE f.names MATCH 'google photos -picasa'

※拡張モードの場合は、検索ワードを ‘google photos NOT picasa’ に変更します

SQLite_sample6

AND検索から docid 12558 のデータが picasa が入っていたので除外されたのが分かります

 

PHRASE フレーズ検索

プラグイン名に google photo というフレーズ(並びも含めたトークン文字列)が入っているプラグインを検索して取得します。フレーズはダブルクオーテーション ” で囲みます

SELECT docid,* FROM plugins_fts AS f WHERE f.names MATCH '"google photos"'

SQLite_sample7

AND検索から docid 12558 がフレーズとしてマッチせず(並びの不一致)にデータが除外されたのが分かります

 

接頭辞のみで残りを * により任意も文字にマッチさせる検索

SELECT docid,* FROM plugins_fts AS f WHERE f.names MATCH 'g* photo'

この場合は google や gallery 等に幅広くマッチします

SQLite_sample8

 

最初に現れるトークンにマッチさせる検索

検索ワードの前に ^ を指定すると最初に現れるトークンを限定してマッチさせることが出来ます

SELECT docid,* FROM plugins_fts AS f WHERE f.names MATCH '^google photo'

SQLite_sample9

その他

NEAR を使ってトークン間の距離を指定した検索も出来るようです
また、拡張モードを使えば、括弧も使えるのでさらに複雑な条件で検索することが出来ます

エスケープ

全文検索では、検索ワードをシングルクオテーション( ‘ )で括ります。
しかし、検索文字列中にシングルクオテーション( ‘ )があるとエラーになってしまいますので、シングルクオテーションを2つ続けることで1つのシングルクオテーションとして認識されます

FTSテーブルの検索だけでは、マッチしたレコードデータと docid が取得出来るだけですので、次はテーブルを結合してその他のプラグインデータを合わせて取得してみましょう

次のページに続きます (^^)


まとめ記事紹介

search star user home refresh tag chevron-left chevron-right exclamation-triangle calendar comment folder thumb-tack navicon angle-double-up angle-double-down angle-up angle-down quote-left googleplus facebook instagram twitter rss