Querying a Full Text Search Table in Android Room Database

Question

I am trying to create a search functionality in my app using Android's Room library fts4 . For some reason the queries always return an empty result. And I can't seem to figure out what the problem is. Below is my DAO and Entity data classes. Am I doing something wrong here?

@Dao
interface HymnDao {
    @Query("SELECT * FROM hymns_table")
    suspend fun getAllHymns(): List<HymnEntity>

    @Query("SELECT * FROM hymns_table WHERE :id = _id ")
    suspend fun getHymn(id: Int): HymnEntity

    @Query(
        """SELECT hymns_table.* 
                 FROM hymns_fts 
                 JOIN hymns_table ON (hymns_fts.rowid = _id )
                 WHERE hymns_fts MATCH :query """
    )
    suspend fun search(query: String): List<HymnEntity>
}

@Entity(tableName = "hymns_table")
data class HymnEntity(
    @PrimaryKey
    @ColumnInfo(name = "_id")
    val id: Int,
    val title: String,
    val author: String,
    val lyrics: String,
)

@Entity(tableName = "hymns_fts")
@Fts4(contentEntity = HymnEntity::class)
data class HymnFts(
    val title: String,
    val lyrics: String
)

Answer 1

Additional

Considering the comment:-

It turns out, it was because I was using a prepopulated database.

Here's an example based upon the answer above BUT with a suitable pre-populated database.

By suitable, one that is created based upon what Room expects which is itself based upon the Entities.

• Note as the original answer was used to provide another answer the database contains additional tables and indexes.

Creating a suitable database is made relatively easy as if you compile the project (CTRL+F9) with the Entities and the @Database (referring to the appropriate Entities), then Room generates java (Android View shows this). The file named the same as the @Database class suffixed with _Impl has a method called createAllTables which is the SQL that can be used quite easily in whatever SQLite tool (assuming the tool supports FTS).

Creating the suitable Pre-Populated Database

1. In Android Studio locate generated java file TheDatabase_Impl and the createAllTables method therein :-

2. using you SQLite tool basically copy the SQL from the generated java e.g.

:-

CREATE TABLE IF NOT EXISTS `hymns_table` (`_id` INTEGER, `title` TEXT NOT NULL, `author` TEXT NOT NULL, `lyrics` TEXT NOT NULL, PRIMARY KEY(`_id`));
CREATE VIRTUAL TABLE IF NOT EXISTS `hymns_fts` USING FTS4(`title` TEXT NOT NULL, `lyrics` TEXT NOT NULL, content=`hymns_table`);
CREATE TRIGGER IF NOT EXISTS room_fts_content_sync_hymns_fts_BEFORE_UPDATE BEFORE UPDATE ON `hymns_table` BEGIN DELETE FROM `hymns_fts` WHERE `docid`=OLD.`rowid`; END;
CREATE TRIGGER IF NOT EXISTS room_fts_content_sync_hymns_fts_BEFORE_DELETE BEFORE DELETE ON `hymns_table` BEGIN DELETE FROM `hymns_fts` WHERE `docid`=OLD.`rowid`; END;
CREATE TRIGGER IF NOT EXISTS room_fts_content_sync_hymns_fts_AFTER_UPDATE AFTER UPDATE ON `hymns_table` BEGIN INSERT INTO `hymns_fts`(`docid`, `title`, `lyrics`) VALUES (NEW.`rowid`, NEW.`title`, NEW.`lyrics`); END;
CREATE TRIGGER IF NOT EXISTS room_fts_content_sync_hymns_fts_AFTER_INSERT AFTER INSERT ON `hymns_table` BEGIN INSERT INTO `hymns_fts`(`docid`, `title`, `lyrics`) VALUES (NEW.`rowid`, NEW.`title`, NEW.`lyrics`); END;

CREATE TABLE IF NOT EXISTS `application_table` (`id` INTEGER, `name` TEXT NOT NULL, PRIMARY KEY(`id`));
CREATE UNIQUE INDEX IF NOT EXISTS `index_application_table_name` ON `application_table` (`name`);
CREATE TABLE IF NOT EXISTS `brand_table` (`id` INTEGER, `path` TEXT NOT NULL, `code` TEXT NOT NULL, `value` TEXT NOT NULL, PRIMARY KEY(`id`));
CREATE TABLE IF NOT EXISTS `Model` (`id` INTEGER, `path` TEXT NOT NULL, `code` TEXT NOT NULL, `value` TEXT NOT NULL, `brandCreatorId` INTEGER NOT NULL, PRIMARY KEY(`id`), FOREIGN KEY(`brandCreatorId`) REFERENCES `brand_table`(`id`) ON UPDATE CASCADE ON DELETE CASCADE );
CREATE INDEX IF NOT EXISTS `index_Model_brandCreatorId` ON `Model` (`brandCreatorId`);
CREATE TABLE IF NOT EXISTS `ApplicationBrandCrossRef` (`appId` INTEGER NOT NULL, `brandId` INTEGER NOT NULL, PRIMARY KEY(`appId`, `brandId`), FOREIGN KEY(`appId`) REFERENCES `application_table`(`id`) ON UPDATE CASCADE ON DELETE CASCADE , FOREIGN KEY(`brandId`) REFERENCES `brand_table`(`id`) ON UPDATE CASCADE ON DELETE CASCADE );
CREATE INDEX IF NOT EXISTS `index_ApplicationBrandCrossRef_brandId` ON `ApplicationBrandCrossRef` (`brandId`);

• Note don't include the SQL that creates the room_master_table or inserts the row into the table.

3. populate the database in the SQLite tool (to do this for the example 2 rows were inserted) e.g.

:-

INSERT INTO `hymns_table` (title,author,lyrics) VALUES
    ('All things bright and beautiful','Fred','All things bright and beautiful,\nAll creatures great and small,\nAll things wise and wonderful:\nThe Lord God made them all.\nEach little flower that opens,\nEach little bird that sings,\nHe made their glowing colors,\nHe made their tiny wings.\n'),
    ('Onward Christian Soldiers','Mary','Onward, Christian soldiers, marching as to war,\nWith the cross of Jesus going on before.\nChrist, the royal Master, leads against the foe;\nForward into battle see His banners go!\nblah the great')
;

• 2 rows added All things bright and beautiful and Onward Christian Soldiers (the latter having the extra line blah the great, so both have a common word)

4. Save/Close the database, open it again and save to make sure that it has been saved.

5. In the project create the assets folder and copy the database file(s) (if the -wal and -shm files exists (they shouldn't if the database has been closed)) into the assets folder.

• In the example the file is named soanswers.db as that's the connection I used.

e.g. :-

6. Amend the Room.databaseBuilder invocation to include the `.createFromAsset("the_filename_copied_into_the_assets_folder") method call.

e.g.

        instance = Room.databaseBuilder(context, TheDatabase::class.java,"hymn.db")
                .createFromAsset("soanswers.db") //<<<<<< ADDED
                .allowMainThreadQueries()
                .build()

7. All should be OK now.

---

In the example from the previous answer and after the steps above were taken, the code used in the activity was changed to :-

    db = TheDatabase.getInstance(this)
    dao = db.getHymnDao()

    for(hymn: HymnEntity in dao.search("small")) {
        Log.d("HYMNINFOR1","Hymn is ${hymn.title}")
    }
    for(hymn: HymnEntity in dao.search("on")) {
        Log.d("HYMNINFOR2","Hymn is ${hymn.title}")
    }
    for(hymn: HymnEntity in dao.search("great")) {
        Log.d("HYMNINFOR3","Hymn is ${hymn.title}")
    }

The Result output to the log :-

2021-08-11 11:08:44.691 D/HYMNINFOR1: Hymn is All things bright and beautiful

2021-08-11 11:08:44.693 D/HYMNINFOR2: Hymn is Onward Christian Soldiers

2021-08-11 11:08:44.694 D/HYMNINFOR3: Hymn is All things bright and beautiful
2021-08-11 11:08:44.694 D/HYMNINFOR3: Hymn is Onward Christian Soldiers

i.e. The first two invocations of the query find the match that is unique to the hymn, the third matches both hymns (hence why great wass added to Onward Christian Soldiers).