SQLiteでFTS3テーブルを構築する際のポイントがいくつかあるようなのでまとめ。
構築済みのテーブルから全文検索用にFTS3テーブルへ移行/FTS3テーブルを追加する場合の注意点リストにも。
▼SQLite FTS3テーブルの特徴
・サイズが膨張するためテキスト検索が不要な情報はテーブルを分ける
・FTS3テーブルの値は文字列扱いになり条件に不等号が使えない
・_id もTEXTになりPRIMARY KEYですらなくなる → 代わりに docid が自動的に作られる
▼設計
1エントリにテキストと数値が混在し、検索しないテキストが多い場合や数値の条件検索をSQLiteで行う場合はテーブルを切り分ける。
1 2 3 4 5 | // 通常のテーブル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などの操作をする場合は両方のテーブルで行う。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | 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処理できる。
1 2 3 4 | // 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"; |
