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)
- this is the only special table as such, the following tables are normal tables other than that the FTS module creates them.
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')");
- P.S. with Room the generated code is virtually the room bible.
- The above wouldn't work because I just put @FTS3 and any old table (there are restrictions). I just did it to demo.
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.
Example
The core table
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
}
The FTS Entity
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;
}
- Unsure about the need for getters and setters, first attempt worked without
- Unsure about the affect autogenerate (but Long doesn't need @NonNull not autogenerate)
Dao
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
Database.java
@androidx.room.Database(
version = 1,
entities = {
Bible.class, //<<<<<<<<<<
BibleFTS.class //<<<<<<<<<<
})
abstract class Database extends RoomDatabase {
abstract BibleDao bibleDao(); //<<<<<<<<<<
}
Test Activity
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()
);
}
}
Result
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.
