SQLite3学習 FTS5+MeCabでクエリ構文
細かい条件指定。
成果物
クエリ構文
<phrase> := string [*]
<phrase> := <phrase> + <phrase>
<neargroup> := NEAR ( <phrase> <phrase> ... [, N] )
<query> := [ [-] <colspec> :] [^] <phrase>
<query> := [ [-] <colspec> :] <neargroup>
<query> := [ [-] <colspec> :] ( <query> )
<query> := <query> AND <query>
<query> := <query> OR <query>
<query> := <query> NOT <query>
<colspec> := colname
<colspec> := { colname1 colname2 ... }
また、上記BNFにはないが暗黙的なAND演算子としてスペースが使える。以下のように。
... MATCH 'one two three'
フレーズ
FTSクエリはフレーズで構成されている。フレーズは1つ以上のトークンの順序付きりすとである。
- FTSクエリ Query
- フレーズ Phrases
- トークン Token
- フレーズ Phrases
+でフレーズ同士を連結して大きなフレーズにする。トークナイザがone.two.threeをone,two,threeの3つのトークンに分けるものとしたとき、以下の4つのクエリは同じフレーズを指す。実際のところMeCabは.でトークンを分けたりしないが。
... MATCH '"one two three"' ... MATCH 'one + two + three' ... MATCH '"one two" + three' ... MATCH 'one.two.three'
フレーズとトークンの違いがいまいちわからない。最終的には、書いたトークンと一致するならヒットするのだろう。
テーブル作成
ターミナルで以下コマンド実行。
sqlite3
対話モードになる。
sqlite>
前回作ったMeCab用トークナイザfts5_mecab.soを読み込む。ファイルパスの末尾に.soをつけると以降のSQLコマンドでError: file is not a databaseエラーになるかも。
.load ./fts5_mecab
テーブルとレコードを作成する。
create virtual table posts using fts5(title, content, tokenize = 'mecab'); insert into posts values('タイトル1', '本文1'); insert into posts values('タイトル2', '本文2'); insert into posts values('たいとる3', 'ほんぶん3'); insert into posts values('間違ったタイトルの書き方', '間違った本文の書き方'); insert into posts values('終わったタイトル', '終わった本文');
トークンを確認する。
ターミナルで以下コマンド実行。
mecab
対話モードになるので以下を入力しENTERキー押下。
間違ったタイトルの書き方
以下のように分解結果が出る。
間違っ 動詞,自立,*,*,五段・ワ行促音便,連用タ接続,間違う,マチガッ,マチガッ た 助動詞,*,*,*,特殊・タ,基本形,た,タ,タ タイトル 名詞,一般,*,*,*,*,タイトル,タイトル,タイトル の 助詞,連体化,*,*,*,*,の,ノ,ノ 書き方 名詞,一般,*,*,*,*,書き方,カキカタ,カキカタ
「間違った」ではなく「間違っ」という単位らしい。
これは困りそう。たとえば、もしかしたら「間違いだらけのタイトルの書き方」というタイトルだったかもしれない。そのときは「間違い」がトークンのため「間違っ」ではヒットしない。「間違うためのタイトルの書き方」かもしれない。そのときは「間違う」がトークンとなる。
それらは「間違」でもヒットしない。「間違*」でもヒットしない。*を使ってもダメだった。
*
*はよくわからない。「トークンが書いた順に出てくるならヒットする」かのように読み取れたが、違うようだ。以下のどちらでもヒットする。
select * from posts where posts match '間違っ タイトル 書き方 *'; select * from posts where posts match '間違っ 書き方 タイトル *'; select * from posts where posts match '書き方 タイトル *';
ワイルドカードのような使い方かと思ったが、以下ではヒットしなかった。
select * from posts where posts match '"間違*"'; select * from posts where posts match '間違*';
ドキュメントをマネてみたがダメだった。
select * from posts where posts match '"間違" *'; select * from posts where posts match '間違 *'; select * from posts where posts match '"間違 *"';
色々やったがダメだった。
列を指定してもダメ。
select * from posts where posts match 'title:"間違*"'; select * from posts where posts match 'title:間違*'; select * from posts where posts match 'title:"間違" *'; select * from posts where posts match 'title:間違 *'; select * from posts where posts match 'title:"間違 *"';
select * from posts where posts match 'content:"間違*"'; select * from posts where posts match 'content:間違*'; select * from posts where posts match 'content:"間違" *'; select * from posts where posts match 'content:間違 *'; select * from posts where posts match 'content:"間違 *"';
スペースを入れてもダメ。
select * from posts where posts match 'title: "間違*"'; select * from posts where posts match 'title: 間違*'; select * from posts where posts match 'title: "間違" *'; select * from posts where posts match 'title: 間違 *'; select * from posts where posts match 'title: "間違 *"';
テーブル値関数を使ってもダメ。
select * from posts('"間違*"'); select * from posts('間違*'); select * from posts('"間違" *'); select * from posts('間違 *'); select * from posts('"間違 *"');
=を使ってもダメ。
select * from posts where posts = '"間違*"'; select * from posts where title = '"間違*"'; select * from posts where content = '"間違*"'; select * from posts where posts = '間違*'; select * from posts where title = '間違*'; select * from posts where content = '間違*'; select * from posts where posts = '"間違" *'; select * from posts where title = '"間違" *'; select * from posts where content = '"間違" *'; select * from posts where posts = '間違 *'; select * from posts where title = '間違 *'; select * from posts where content = '間違 *'; select * from posts where posts = '"間違 *"'; select * from posts where title = '"間違 *"'; select * from posts where content = '"間違 *"';
sqlite3 fts5 prefix tokenでググってみると以下のようにヒットしないと嘆く声がある。以下はFTS4だが。
以下では、ワイルドカードのように使えると書いてある。
SELECT * FROM posts WHERE posts = 'search*';はsearch,searching,searchesなどにヒットすると書いてある。なのに以下はヒットせず……。
select * from posts where posts = '間違*'; select * from posts where posts = '^ 間違*';
でも以下はすべてヒットした。なぜだ……。
select * from posts where posts = 'タイト*';
select * from posts where posts = '書*';
select * from posts where posts = '間*';
select * from posts where posts = '間違っ*';
select * from posts where posts = '書き*';
select * from posts where posts = '^ 間*';
いやいや、なら間違*や^ 間違*でヒットしてくれてもいいでしょ！？ なぜヒットしない……。「間違った」「間違う」「間違い」「間違え」「間違おう」などにヒットさせたいのに……。
これができないなら全文検索として使えない。異なる活用形でもヒットしてほしい。
'スペース'
select * from posts where posts match 'タイトル 書き方 間違っ';
間違ったタイトルの書き方|間違った本文の書き方
'"スペース"'
select * from posts where posts match '"タイトル 書き方 間違っ"';
ヒットせず。
以下でヒット。たぶんダブルクォートした文字列が部分一致したらヒットする。
select * from posts where posts match '"間違った"';
select * from posts where posts match '"タイトルの"';
select * from posts where posts match '"書き方"';
select * from posts where posts match '"間違ったタイトルの書き方"';
'スペース+'
select * from posts where posts match 'タイトル + 書き方 + 間違っ';
ヒットせず。あれ、もしかして文字列結合？ "タイトル書き方間違っ"に部分一致でヒットなのかな？
以下でヒット。
select * from posts where posts match 'タイトルの + 書き方';
以下はヒットせず。
select * from posts where posts match 'タイトル + 書き方';
つまり'A + B'は'"AB"'と同義。
^ initial token query
... MATCH '^one' -- first token in any column must be "one" ... MATCH '^ one + two' -- phrase "one two" must appear at start of a column ... MATCH '^ "one two"' -- same as previous ... MATCH 'a : ^two' -- first token of column "a" must be "two" ... MATCH 'NEAR(^one, two)' -- syntax error! ... MATCH 'one + ^two' -- syntax error! ... MATCH '"^one two"' -- May not work as expected!
select * from posts where posts match '^間違っ';
間違ったタイトルの書き方|間違った本文の書き方
select * from posts where posts match '^タイトル';
タイトル1|本文1 タイトル2|本文2
select * from posts where posts match '^タイト*';
タイトル1|本文1 タイトル2|本文2
なのに以下はヒットしない謎。
select * from posts where posts match '^間違*';
'NEAR()'
2つ以上のトークンを含めばヒット。
... MATCH 'NEAR("one two" "three four", 10)' ... MATCH 'NEAR("one two" thr* + four)'
CREATE VIRTUAL TABLE f USING fts5(x); INSERT INTO f(rowid, x) VALUES(1, 'A B C D x x x E F x'); ... MATCH 'NEAR(e d, 4)'; -- Matches! ... MATCH 'NEAR(e d, 3)'; -- Matches! ... MATCH 'NEAR(e d, 2)'; -- Does not match! ... MATCH 'NEAR("c d" "e f", 3)'; -- Matches! ... MATCH 'NEAR("c" "e f", 3)'; -- Does not match! ... MATCH 'NEAR(a d e, 6)'; -- Matches! ... MATCH 'NEAR(a d e, 5)'; -- Does not match! ... MATCH 'NEAR("a b c d" "b c" "e f", 4)'; -- Matches! ... MATCH 'NEAR("a b c d" "b c" "e f", 3)'; -- Does not match!
第二引数の数値が謎。
select * from posts where posts match 'NEAR(タイトル 間違っ, 10)';
ヒット。記述の順序は関係なさそう。
間違ったタイトルの書き方|間違った本文の書き方
以下はヒットしなかった。
select * from posts where posts match 'NEAR(タイトル 間違っ, 0)';
第二引数はNと呼ぶらしい。
クランプって何。さも当然のように謎ワード登場。文脈から察するに、タイトルと間違っの間に別のトークンが0個以下だったらヒットする、ということか？ そしてそれが無かったからヒットしなかったと。「間違ったタイトルの書き方」だから２つの間に「た」という1個のトークンがあるのだろう。なのに0個以下と指定したからヒットしなかったと。
これ、どういうときに使うんだ？ nearは「近い」という意味だから、指定したトークンが近いところにあるときだけヒットさせたいときに使うのか？ それに意味はあるのだろうか。たとえば倒置法などを使えば簡単に変わってしまうのでは？ 位置の近さにどういう意味があるのだろう。これも英語用か？
でも、以下のようにすれば0でもヒットする。
select * from posts where posts match 'NEAR(タイトル "間違った", 0)';
間違ったタイトルの書き方|間違った本文の書き方
間違ったという文字列とタイトルというトークンとの間には他のトークンが0個以下である。よってヒットした。
でもこれ、最初か以下のようにすればいいだけ。
select * from posts where posts match 'NEAR("間違ったタイトル", 0)'; select * from posts where posts match 'NEAR("^間違ったタイトル", 0)'; select * from posts where posts match 'NEAR("^ 間違ったタイトル", 0)';
つまり、Nが1以上のときにNEAR()を使う価値があるはず。3トークン以上あるときに使うものなのだろう。
たとえば以下。
select * from posts where posts match 'NEAR("間違った" "書き方", 2)';
間違った[なんちゃら][の]書き方という間に2個のトークンがあるときだけヒットさせることができる。
間違ったお前のやり方を悪しざまに貶める書き方はヒットしない。間にあるトークンが多すぎるから。
insert into posts values('NEAR確認用','間違ったお前のやり方を悪しざまに貶める書き方');
select * from posts where posts match 'NEAR("間違った" "書き方", 2)';
間違ったタイトルの書き方|間違った本文の書き方
どうでもいいが、「間違った」はどこに係っているのか。「お前」「やり方」「書き方」。この日本語が間違ってる。
では、2から7に変えるとどうだろうか。
select * from posts where posts match 'NEAR("間違った" "書き方", 7)';
間違ったタイトルの書き方|間違った本文の書き方 NEAR確認用|間違ったお前のやり方を悪しざまに貶める書き方
ヒットした。MeCabで何個に分解されるか確認してみる。
mecab
間違ったお前のやり方を悪しざまに貶める書き方
間違っ 動詞,自立,*,*,五段・ワ行促音便,連用タ接続,間違う,マチガッ,マチガッ た 助動詞,*,*,*,特殊・タ,基本形,た,タ,タ お前 名詞,代名詞,一般,*,*,*,お前,オマエ,オマエ の 助詞,連体化,*,*,*,*,の,ノ,ノ やり方 名詞,一般,*,*,*,*,やり方,ヤリカタ,ヤリカタ を 助詞,格助詞,一般,*,*,*,を,ヲ,ヲ 悪しざま 名詞,一般,*,*,*,*,悪しざま,アシザマ,アシザマ に 助詞,格助詞,一般,*,*,*,に,ニ,ニ 貶める 動詞,自立,*,*,一段,基本形,貶める,オトシメル,オトシメル 書き方 名詞,一般,*,*,*,*,書き方,カキカタ,カキカタ
お前,の,やり方,を,悪しざま,に,貶めるの7個。だからN値を7にすると7個以下という条件に合致してヒットした。
やはり用途がよくわからない。完全一致ほど厳選したくはないけど、長文すぎるのは排除したいときとか？
insert into posts values('NEAR確認用','間違った恥ずかしい書き方');
間違っ 動詞,自立,*,*,五段・ワ行促音便,連用タ接続,間違う,マチガッ,マチガッ た 助動詞,*,*,*,特殊・タ,基本形,た,タ,タ 恥ずかしい 形容詞,自立,*,*,形容詞・イ段,基本形,恥ずかしい,ハズカシイ,ハズカシイ 書き方 名詞,一般,*,*,*,*,書き方,カキカタ,カキカタ
短いと意味する範囲が広大になるのかな？ まるで「やばい」という短い語で「危険」と「素敵」を含めるかのように。違うか。
| 文 | N値 |
|---|---|
間違った[恥ずかしい]書き方 |
1 |
間違った[タイトル][の]書き方 |
2 |
間違った[お前][の][やり方][を][悪しざま][に][貶める]書き方 |
7 |
select * from posts where posts match 'NEAR("間違った" "書き方", 2)';
間違ったタイトルの書き方|間違った本文の書き方 NEAR確認用|間違った恥ずかしい書き方
でもこれ、5個「以上」が欲しいときに指示できない。「以下」でしか指示できない。やはり近いものをヒットさせるのが狙いなのだろう。遠いのだけをヒットさせたいときはどうするの？
'列名:フレーズ'
... MATCH 'colname : NEAR("one two" "three four", 10)' ... MATCH '"colname" : one + two + three' ... MATCH '{col1 col2} : NEAR("one two" "three four", 10)' ... MATCH '{col2 col1 col3} : one + two + three'
-- Search for matches in all columns except "colname" ... MATCH '- colname : NEAR("one two" "three four", 10)' -- Search for matches in all columns except "col1", "col2" and "col3" ... MATCH '- {col2 col1 col3} : one + two + three'
select * from posts where posts match 'title:タイトル';
タイトル1|本文1 タイトル2|本文2 間違ったタイトルの書き方|間違った本文の書き方 終わったタイトル|終わった本文
以下は期待値どおりヒットせず。
select * from posts where posts match 'title:本文';
以下は期待値どおりヒットする。
select * from posts where posts match 'content:本文';
タイトル1|本文1 タイトル2|本文2 間違ったタイトルの書き方|間違った本文の書き方 終わったタイトル|終わった本文
{列名 列名}: フレーズ
列名を{}で囲む。書き方がどちらか一方にあればヒットするらしい。両方かと思ったのだが。
select * from posts where posts match '{title content}:書き方';
間違ったタイトルの書き方|間違った本文の書き方 NEAR確認用|間違ったお前のやり方を悪しざまに貶める書き方 NEAR確認用|間違った恥ずかしい書き方
-列名: フレーズ
-を列名の前につけると、その列は検索対象外になる。それ以外の列で検索する。
select * from posts where posts match '-title:本文';
タイトル1|本文1 タイトル2|本文2 間違ったタイトルの書き方|間違った本文の書き方 終わったタイトル|終わった本文
上記はtitle列以外の他の列で本文トークンが存在したレコードのみヒットさせた。title列以外にはcontent列のみ。つまりcontent:本文と同義。
ネスト
これは意味がわからなかった。以下は同じらしい。
-- The following are equivalent: ... MATCH '{a b} : ( {b c} : "hello" AND "world" )' ... MATCH '(b : "hello") AND ({a b} : "world")'
なぜa列はworldだけなのか。まあこんなネストした文なんて書きたくない。何を意味しているのかさっぱり読めない。列ごとにANDでつなげれば済むはず。
MATCH + 列名:
-- Given the following table CREATE VIRTUAL TABLE ft USING fts5(a, b, c); -- The following are equivalent SELECT * FROM ft WHERE b MATCH 'uvw AND xyz'; SELECT * FROM ft WHERE ft MATCH 'b : (uvw AND xyz)'; -- This query cannot match any rows (since all columns are filtered out): SELECT * FROM ft WHERE b MATCH 'a : xyz';
最後に、（通常のテーブル名の代わりに）MATCH演算子のLHSとして列名を使用して、単一列の列フィルターを指定できます。
LHSって何？ 意味不明。
ようするに以下の2つの書き方ができるということか？
where 列名 match 'フレーズ' where 表名 match '列名:フレーズ'
どうみても前者のほうが自然でスマート。これもう列フィルタの意味ないのでは？
論理演算子
優先順位が高い順に一覧する。
NOTANDOR
優先順位は()で変えることができる。
-- Matches documents that contain at least one instance of either "one" -- or "two", but do not contain any instances of token "three". ... MATCH 'one OR two NOT three' -- Match all documents that contain the token "two" but not "three", or -- contain the token "one". ... MATCH 'one OR (two NOT three)'
以下みたいに書く。下2つは構文エラー。
... MATCH 'one two three' -- 'one AND two AND three' ... MATCH 'three "one two"' -- 'three AND "one two"' ... MATCH 'NEAR(one two) three' -- 'NEAR(one two) AND three' ... MATCH 'one OR two three' -- 'one OR two AND three' ... MATCH '(one OR two) three' -- Syntax error! ... MATCH 'func(one two)' -- Syntax error!
select * from posts where posts match 'title:タイトル AND content:書き方';
間違ったタイトルの書き方|間違った本文の書き方
select * from posts where posts match 'title:タイトル OR content:書き方';
タイトル1|本文1 タイトル2|本文2 間違ったタイトルの書き方|間違った本文の書き方 終わったタイトル|終わった本文 NEAR確認用|間違ったお前のやり方を悪しざまに貶める書き方 NEAR確認用|間違った恥ずかしい書き方
まとめ
正直、べつにここにあるクエリ構文を知らなくてもいい気がする。有意義なのはORくらいか？
- 有意義な使い方がいまいちピンとこない
- わざわざFTSクエリでなくともSQLクエリのwhere句で一部実現できるのでは？
問題
間違*でヒットできない
原因不明。タイト*はできたのに。日本語の検索に難がありそう。
これならファイル管理してgrepするほうがいいのでは？ と思ってしまう。grep正規表現で検索できたほうが嬉しい。
対象環境
- Raspbierry pi 3 Model B+
- Raspbian stretch 9.0 2018-11-13
- bash 4.4.12(1)-release
- SQLite 3.29.0
$ uname -a Linux raspberrypi 4.19.42-v7+ #1218 SMP Tue May 14 00:48:17 BST 2019 armv7l GNU/Linux
前回まで
- SQLite3学習をはじめよう
- SQLite3学習 SQLiteについて
- SQLite3学習 SQLiteの適切な用途
- SQLite3学習 SQLiteの特徴
- SQLite3学習 SQLiteのクセ
- SQLite3学習 データ型とアフィニティ
- SQLite3学習 演算子の一覧
- SQLite3学習 よくある質問
- SQLite3学習 SQLiteダウンロード＆コンパイル
- SQLite3学習 Tclで操作する
- SQLite3学習 ビルドオプション動作確認（SQLITE_ALLOW_URI_AUTHORITY）
- SQLite3学習 面白そうなコンパイルオプション
- SQLite3学習 SQLiteの拡張について
- SQLite3学習 JSON拡張
- SQLite3学習 JSON拡張（json_extract）
- SQLite3学習 JSON拡張（json_each）
- SQLite3学習 JSON拡張（json_tree オブジェクト→行）
- SQLite3学習 JSON拡張（json_tree オブジェクトツリー→行）
- SQLite3学習 JSON拡張（json_tree オブジェクト配列→行）
- SQLite3学習 JSON拡張（json_group_array 行→配列）
- SQLite3学習 JSON拡張（json_group_object 行→オブジェクト）
- SQLite3学習 JSON拡張（json_array_length）
- SQLite3学習 JSON拡張（json_type）
- SQLite3学習 JSON拡張（json_valid）
- SQLite3学習 JSON拡張（json_quote）
- SQLite3学習 JSON拡張（json_array）
- SQLite3学習 JSON拡張（json_object）
- SQLite3学習 JSON拡張（json_patch）
- SQLite3学習 JSON拡張（json_insert）
- SQLite3学習 JSON拡張（json_replace）
- SQLite3学習 JSON拡張（json_set）
- SQLite3学習 JSON拡張（json_remove）
- SQLite3学習 全文検索（FTS5）
- SQLite3学習 全文検索FTSを日本語で使う方法を調べてみた
- 形態素解析MeCabをインストールする
- SQLite3学習 全文検索FTS5のMeCab用トークナイザを実装する
