SQLiteで全文検索

検索機能付きのAndroidアプリを作るために、ドキュメントを読んでいるとCreating a Search Interface  |  Android Developersという項目があり、SQLiteに全文検索モジュール:SQLite FTS3 and FTS4 Extensionsがあることを知った。

Androidアプリに組み込む前に、FTS3を使ってみた。

$ sqlite3 database.sqlite
SQLite version 3.7.6
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> CREATE VIRTUAL TABLE AddressBook USING fts3(name TEXT, address TEXT);

-- FTS3を使う表はVIRTUAL TABLEにする必要がある

sqlite> INSERT INTO AddressBook VALUES("Alice", "Albany, Crossgates");
sqlite> INSERT INTO AddressBook VALUES("Bob", "Buffalo, Walden Galleria");
sqlite> INSERT INTO AddressBook VALUES("Carol", "Garden City, Roosevelt Field");
sqlite> INSERT INTO AddressBook VALUES("Dave", "Huntington Station, Walt Whitman");
sqlite> INSERT INTO AddressBook VALUES("Eve", "Lake Grove, Smith Haven");
sqlite> INSERT INTO AddressBook VALUES("Mallory", "Manhasset, Manhasset");
sqlite> INSERT INTO AddressBook VALUES("Peggy", "New York City, Fifth Avenue");
sqlite> INSERT INTO AddressBook VALUES("Victor", "New York City, SoHo");
sqlite> INSERT INTO AddressBook VALUES("Trent", "New York City, Upper West Side");
sqlite> INSERT INTO AddressBook VALUES("Walter", "New York City, West 14th Street");
sqlite> INSERT INTO AddressBook VALUES("Arthur", "Staten Island, Staten Island");
sqlite> INSERT INTO AddressBook VALUES("Merlin", "Syracuse, Carousel");
sqlite> INSERT INTO AddressBook VALUES("Paul", "Victor, Eastview");
sqlite> INSERT INTO AddressBook VALUES("Carole", "West Nyack, Palisades");
sqlite> INSERT INTO AddressBook VALUES("Chuck", "White Plains, The Westchester");

sqlite> .tables
AddressBook           AddressBook_segdir  
AddressBook_content   AddressBook_segments

表の指定桁に対して全文検索をするには、MATCH queryを使う。

sqlite> SELECT docid, * FROM AddressBook WHERE address MATCH 'City';
3|Carol|Garden City, Roosevelt Field
7|Peggy|New York City, Fifth Avenue
8|Victor|New York City, SoHo
9|Trent|New York City, Upper West Side
10|Walter|New York City, West 14th Street

-- LIKEを使っても同じ結果が得られるが、MATCHを使った方が速い。

sqlite> SELECT docid , * FROM AddressBook WHERE address LIKE '%City%';
3|Carol|Garden City, Roosevelt Field
7|Peggy|New York City, Fifth Avenue
8|Victor|New York City, SoHo
9|Trent|New York City, Upper West Side
10|Walter|New York City, West 14th Street

Google検索をするように、AND/OR/NOT検索、PHRASE検索が出来る。

-- AND
sqlite> SELECT * FROM AddressBook WHERE address MATCH 'West City';
Trent|New York City, Upper West Side
Walter|New York City, West 14th Street

-- OR
sqlite> SELECT * FROM AddressBook WHERE address MATCH 'West OR City';
Carol|Garden City, Roosevelt Field
Peggy|New York City, Fifth Avenue
Victor|New York City, SoHo
Trent|New York City, Upper West Side
Walter|New York City, West 14th Street
Carole|West Nyack, Palisades

-- NOT
sqlite> SELECT * FROM AddressBook WHERE address MATCH 'New -West';
Peggy|New York City, Fifth Avenue
Victor|New York City, SoHo

-- PHRASE
sqlite> SELECT * FROM AddressBook WHERE address MATCH '"New City"';

また、ワイルドカードマッチングも出来る。

sqlite> SELECT * FROM AddressBook WHERE address MATCH 'West S*';
Trent|New York City, Upper West Side
Walter|New York City, West 14th Street

sqlite> SELECT * FROM AddressBook WHERE address MATCH '"N* Y*"';
Peggy|New York City, Fifth Avenue
Victor|New York City, SoHo
Trent|New York City, Upper West Side
Walter|New York City, West 14th Street

AとBの単語を指定し、その単語間の距離を指定して検索することも出来る。

sqlite> SELECT * FROM AddressBook WHERE address MATCH 'New NEAR West';
Trent|New York City, Upper West Side
Walter|New York City, West 14th Street

-- default: NEAR/10

sqlite> SELECT * FROM AddressBook WHERE address MATCH 'New NEAR/2 West';
Walter|New York City, West 14th Street

マッチした単語を強調表示するために、snippet functionがある。

sqlite> SELECT snippet(AddressBook) FROM AddressBook WHERE address MATCH 'City';
Garden <b>City</b>, Roosevelt Field
New York <b>City</b>, Fifth Avenue
New York <b>City</b>, SoHo
New York <b>City</b>, Upper West Side
New York <b>City</b>, West 14th Street

-- default: <b> and </b>

sqlite> SELECT snippet(AddressBook, "[", "]") FROM AddressBook WHERE address MATCH 'York';
New [York] City, Fifth Avenue
New [York] City, SoHo
New [York] City, Upper West Side
New [York] City, West 14th Street