The android [FTS4 documentation][1] for android states "This class will have a mapping SQLite FTS4 table in the database". Should I interpret that as a mapping SQLite FTS4 table will be generated simply by adding the annotation, or does it mean I need to provide some sort of mapping table to 'wire up' the FTS4 entity class to a virtual FTS4 table?
I'm currently assuming the former. If that's correct, then how can I make use of this mapping table in practical terms? I think it's right to say that when you create an entity with the FTS4 annotation it generates an ordinary table and a mapping table, neither of which is actually a virtual FTS4 table which can be queried with the MATCH function. So what exactly does the the mapping table map to?! Some hypothetical virtual FTS4 external content table that's implemented at runtime, possibly during a database callback or migration? In that case how am I supposed to reference the searchable FTS4 table in my DAO without getting a compiler error?
SOLVED:
Turns out the former interpretation was correct and that the mapping table generated includes the virtual table required for a full text search. I do still think the documentation could be clearer on this, but in the end the real underlying issue was that my room dependency wasn't up to date. D'oh!
With FTS 3 or 4 you define the core table and then the VIRTUAL table with the USING keyword and the appropriate parameters (module such as FTS3 or FTS4 and the parameters that the module expects). The FTS module then generates the tables.
E.g. A database has a standard bible table, with columns book TEXT, chapter INTEGER, verse INTEGER and content TEXT. For FTS you create the virtual table using something like
CREATE VIRTUAL TABLE bible_fts USING fts3(book,chapter INTEGER, verse INTEGER, content TEXT)
When the CREATE statement is executed then table bible_fts_content, bible_fts_segments and the bible_fts_segdir are created. (may be a little different for FTS4). The FTS ready version of SQLite will then be able to handle things such as MATCH.
In short, it's all dependant upon the special handling of the defined module.
In case you couldn't tell, it's very unclear to me what my responsibilties are here!
My understanding is that you have to define the core tables.
You code the relevant @FTS (3 or 4) annontation Room generates the vritual table e.g. you get something along the lines of :-
_db.execSQL("CREATE TABLE IF NOT EXISTS `device_item` (`id` INTEGER, `initial` TEXT, `added1` INTEGER NOT NULL DEFAULT 0, `added2` TEXT DEFAULT '', PRIMARY KEY(`id`))");
_db.execSQL("CREATE VIRTUAL TABLE IF NOT EXISTS `table1` USING FTS3(`name` TEXT, `mapToTable2` INTEGER NOT NULL, `mapToTable3` INTEGER NOT NULL)");
_db.execSQL("CREATE TABLE IF NOT EXISTS `table2` (`id` INTEGER, `nameOfT2` TEXT, `anotherNameOfT2` TEXT, PRIMARY KEY(`id`))");
_db.execSQL("CREATE TABLE IF NOT EXISTS `table3` (`id` INTEGER, `nameOfT3` TEXT, `anotherNameOfT3` TEXT, PRIMARY KEY(`id`))");
_db.execSQL("CREATE TABLE IF NOT EXISTS room_master_table (id INTEGER PRIMARY KEY,identity_hash TEXT)");
_db.execSQL("INSERT OR REPLACE INTO room_master_table (id,identity_hash) VALUES(42, '91a23aea1ab6e684828fad82668cb9a5')");
Then I believe that because of the annotations that Dao's etc reference what they need. However, I suspect that you will have to be coding @Ignore frequently. (I've never actually used FTS, my experience was with a tool that I was playing with a tool convert non-room databases to room and got the conversion working with FTS i.e. to the stage where Room accepted the database schema wise).
You may also find Enabling SQLite FTS in Room 2.1 of use.
Entity Bible.java
@Entity(tableName = "bible")
public class Bible {
@PrimaryKey
Long id;
String book;
Long chapter;
Long verse;
String content;
public Bible(){}
@Ignore
public Bible(String bookName, Long chapter, Long verse, String content) {
this.book = bookName;
this.chapter = chapter;
this.verse = verse;
this.content = content;
}
// ...... getters and setters nothing special
}
As book and content are the textual columns these will be defined for FTS
BibleFTS.java
@Fts4(contentEntity = Bible.class) //<<<<<<<<<< the table to FTSalise
@Entity(tableName = "bible_fts")
public class BibleFTS {
@PrimaryKey
Long rowid; //<<<<<<<<<< MUST HAVE
String book;
String content;
}
BibleDao.java (covers Bible and BibleFTS)
@Dao
public interface BibleDao {
@Insert
Long insertBibleRow(Bible bible);
@Insert
Long[] insertBibleRows(Bible... bibles);
@Query("SELECT * FROM bible")
List<Bible> getAllBibleRows();
//<<<<<<<<<< USED TO REBUILD (Room might do this anyway think I saw it somewhere) >>>>>>>>>>
@Query("INSERT INTO bible_fts(bible_fts) VALUES('rebuild')")
void rebuild();
//<<<<<<<<<< SEARCH The searchable columns >>>>>>>>>
@Query("SELECT bible.book,bible.chapter,bible.verse,bible.content FROM bible_fts JOIN bible ON id = docid WHERE bible_fts MATCH :searchFor")
List<Bible> searchFor(String searchFor);
}
Database.java
@androidx.room.Database(
version = 1,
entities = {
Bible.class, //<<<<<<<<<<
BibleFTS.class //<<<<<<<<<<
})
abstract class Database extends RoomDatabase {
abstract BibleDao bibleDao(); //<<<<<<<<<<
}
MainActivity.java (onCreate method)
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);
mDatabase = Room.databaseBuilder(this,Database.class,"devicitems")
.allowMainThreadQueries()
.build();
mBibleDao = mDatabase.bibleDao();
Bible b = new Bible("Geneisis",1L,1L,"In the begining some bright spark made a thinngy called Earth and said I shall rule over that little speck.");
mBibleDao.insertBibleRow(b);
mBibleDao.rebuild();
List<Bible> found = mBibleDao.searchFor("in");
for (Bible currentBible: found) {
Log.d(
"FOUNDIN",
"Found in in :- Book" + currentBible.getBook() +
" - Chapter " + currentBible.getChapter() +
" - Verse " + currentBible.getVerse() +
"\n\t" + currentBible.getContent()
);
}
}
2019-10-12 21:37:00.584 30255-30255/? D/FOUNDIN: Found in in :- BookGeneisis - Chapter 1 - Verse 1
In the begining some bright spark made a thinngy called Earth and said I shall rule over that little speck.