Android 2.1でのSQLite FTS3テーブル

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";