I'm having some difficulty implementing fts4 into my app. My aim is to build an app entirely in Kotlin using the latest jetpack components including the Room database. However, when I looked at the documentation for fts4, the examples were written in Java!
Besides the android docs for related classes and the sqlite documentation for fts3/4 , I've searched through a few different tutorials on this. Notably, this blog post and these slides. All of the tutorials I've been able to find so far have been written in Java and rely on database migrations to build the virtual table required for fts^(SEE NOTE BELOW). Despite that, I've yet to piece together a working solution.
Here's the latest iteration of my code:
Entity:
@Entity(tableName = "items")
data class Item(
@PrimaryKey
@ColumnInfo(name = "rowid")
val rowId: Int,
val qualities: String,
val itemId: String,
val name: String,
val description: String,
val quantity: Int,
) {
override fun toString() = name
}
FTS Entity:
@Entity(tableName = "items_fts")
@Fts4(contentEntity = Item::class, tokenizer = FtsOptions.TOKENIZER_PORTER)
data class ItemFTS(
@PrimaryKey
@ColumnInfo(name = "rowid")
val id: Int,
val qualities: String
) {
override fun toString() = items
}
Room database:
@Database(entities = [Item::class, ItemFTS::class], version = 1, exportSchema = false)
@TypeConverters(Converters::class)
abstract class AppDatabase : RoomDatabase() {
abstract fun itemDao(): ItemDao
companion object {
// For Singleton instantiation
@Volatile private var instance: AppDatabase? = null
fun getInstance(context: Context): AppDatabase {
return instance ?: synchronized(this) {
instance ?: buildDatabase(context).also { instance = it }
}
}
// Create and pre-populate the database.
private fun buildDatabase(context: Context): AppDatabase {
return Room.databaseBuilder(context, AppDatabase::class.java, DATABASE_NAME)
.addCallback(object : RoomDatabase.Callback() {
override fun onCreate(db: SupportSQLiteDatabase) {
super.onCreate(db)
val request = OneTimeWorkRequestBuilder<SeedDatabaseWorker>().build()
WorkManager.getInstance().enqueue(request)
}
})
// **I tried adding in migrations here based on the blog posts attached to this stack overflow
// question but didn't seem to work, even when I incremented the database version.**
.build()
}
}
}
Seed Database Worker:
class SeedDatabaseWorker(
context: Context,
workerParams: WorkerParameters
) : CoroutineWorker(context, workerParams) {
private val TAG by lazy { SeedDatabaseWorker::class.java.simpleName }
override suspend fun doWork(): Result = coroutineScope {
try {
applicationContext.assets.open(ITEM_DATA_FILENAME).use { inputStream ->
JsonReader(inputStream.reader()).use { jsonReader ->
val itemType = object : TypeToken<List<Item>>() {}.type
val itemList: List<Item> = Gson().fromJson(jsonReader, itemType)
val database = AppDatabase.getInstance(applicationContext)
database.itemDao().insertAll(itemList)
Result.success()
}
}
} catch (ex: Exception) {
Log.e(TAG, "Error seeding database", ex)
Result.failure()
}
}
}
Item DAO:
@Dao
interface ItemDao {
@Query("SELECT * FROM items ORDER BY name")
fun getItems(): LiveData<List<Item>>
@Query("SELECT * FROM items WHERE itemId = :itemId")
fun getItem(itemId: String): LiveData<Item>
@Insert(onConflict = OnConflictStrategy.REPLACE)
fun insertAll(items: List<Item>)
@Query("SELECT * FROM items WHERE rowid IN (SELECT rowid FROM items_fts WHERE qualities MATCH :query) ORDER BY name")
fun searchItems(query: String): LiveData<List<Item>>
}
Also, incase you were wondering, I made sure to change the % query annotation to *.
^(NOTE) This isn't something I have any experience with and could possibly be part of the reason why I'm struggling to come up with a working solution. It's a little unclear to me how much of the work Room will do behind the scenes and how much I need to intervene when it comes to generating, populating, indexing and generally managing the fts side of things. Previously my solution made use of a non-fts search query which required no SQL besides the query itself. I'm not very familiar with SQL and I really don't understand the fundamentals of how migrations work. Is it possible to have a database built and then migrate it to a new version without first installing an app and then migrating the database in updated version? If it isn't and if migration is required to create the virtual fts table, how can you possibly ship a new android app with fts ready to go?! I feel like there are some gaping holes in my knowledge here which are preventing me from making any progress. Any good suggestions or proposed solutions would be greatly appreciated!