SQLiteでFTS3テーブルを構築する際のポイントがいくつかあるようなのでまとめ。
構築済みのテーブルから全文検索用にFTS3テーブルへ移行/FTS3テーブルを追加する場合の注意点リストにも。
▼SQLite FTS3テーブルの特徴
・サイズが膨張するためテキスト検索が不要な情報はテーブルを分ける
・FTS3テーブルの値は文字列扱いになり条件に不等号が使えない
・_id もTEXTになりPRIMARY KEYですらなくなる → 代わりに docid が自動的に作られる
▼設計
1エントリにテキストと数値が混在し、検索しないテキストが多い場合や数値の条件検索をSQLiteで行う場合はテーブルを切り分ける。
// 通常のテーブル create table normaltable ( _id integer primary key autoincrement, i_dat0 integer, ... ); // FTS3テーブル (自動的にdocid integer primary key autoincrementが追加される) create virtual table ftstable USING fts3( stext text, ... );
▼データ管理
設計にもよるが1つのデータを通常のテーブル+FTS3テーブルに分けて登録する場合は、下記の検索方法を使用するため、insert、deleteなどの操作をする場合は両方のテーブルで行う。
public long registerEntry(ContentValues cv) {
// ContentValues を通常テーブル用+FTS3テーブル用に分割
ContentValues cv_n=new ContentValues(cv);
ContentValues cv_fts=new ContentValues();
cv_fts.put("stext", cv.getAsString("stext")); cv_n.remove("stext");
cv_fts.put("s_dat0", cv.getAsString("s_dat0")); cv_n.remove("s_dat0");
cv_fts.put("s_dat1", cv.getAsString("s_dat1")); cv_n.remove("s_dat1");
return registerEntry(cv_n, cv_fts);
}
public long registerEntry(ContentValues cv_n, ContentValues cv_fts) {
mDb.insert("ftstable", null, cv_fts);
return mDb.insert("normaltable", null, cv_n);
}
▼検索
通常のテーブルとFTS3テーブルを _id と docid で参照して両方のデータを含むCursorを取得/Adapter処理できる。
// mDb.rawQuery用検索クエリ例. MATCH部分は*ワイルドカード可 select dt._id, dt.i_dat0, vt.stext from normaltable dt, ftstable, vt where dt._id=vt.docid AND vt.stext MATCH "Keyword";
