androidsqlitetriggersandroid-roomfts4

Android Room FTS4 how to drop triggers created automatically

Can I have control over 'insert/update/delete' triggers that are created automatically by Android Room to sync data between content and virtual tables. Can I remove update triggers that I don't need?

I've created virtual table with external content using Android Room.

Content (normal) table Person

@Entity(tableName = "person")
data class Person(
    @ColumnInfo(name = "name")
    @PrimaryKey
    val name: String,
    @ColumnInfo(name = "phone")
    val phone: String
)

Virtual table PersonFTS for FTS with content table Person

@Entity(tableName = "person_fts")
@Fts4(contentEntity = Person::class)
data class PersonFTS(
    @ColumnInfo(name = "name")
    val name: String,
    @ColumnInfo(name = "phone")
    val phone: String
)

Now, Android Room has created 4 triggers to keep data synced between Person and PersonFTS automatically. Here is PersonDatabase_Impl generated by Android:

public final class PersonDatabase_Impl extends PersonDatabase {
  private volatile PersonDao _personDao;

  @Override
  protected SupportSQLiteOpenHelper createOpenHelper(DatabaseConfiguration configuration) {
    final SupportSQLiteOpenHelper.Callback _openCallback = new RoomOpenHelper(configuration, new RoomOpenHelper.Delegate(3) {
      @Override
      public void createAllTables(SupportSQLiteDatabase _db) {
        _db.execSQL("CREATE TABLE IF NOT EXISTS `person` (`name` TEXT NOT NULL, `phone` TEXT NOT NULL, PRIMARY KEY(`name`))");
        _db.execSQL("CREATE VIRTUAL TABLE IF NOT EXISTS `person_fts` USING FTS4(`name` TEXT NOT NULL, `phone` TEXT NOT NULL, content=`person`)");
        _db.execSQL("CREATE TRIGGER IF NOT EXISTS room_fts_content_sync_person_fts_BEFORE_UPDATE BEFORE UPDATE ON `person` BEGIN DELETE FROM `person_fts` WHERE `docid`=OLD.`rowid`; END");
        _db.execSQL("CREATE TRIGGER IF NOT EXISTS room_fts_content_sync_person_fts_BEFORE_DELETE BEFORE DELETE ON `person` BEGIN DELETE FROM `person_fts` WHERE `docid`=OLD.`rowid`; END");
        _db.execSQL("CREATE TRIGGER IF NOT EXISTS room_fts_content_sync_person_fts_AFTER_UPDATE AFTER UPDATE ON `person` BEGIN INSERT INTO `person_fts`(`docid`, `name`, `phone`) VALUES (NEW.`rowid`, NEW.`name`, NEW.`phone`); END");
        _db.execSQL("CREATE TRIGGER IF NOT EXISTS room_fts_content_sync_person_fts_AFTER_INSERT AFTER INSERT ON `person` BEGIN INSERT INTO `person_fts`(`docid`, `name`, `phone`) VALUES (NEW.`rowid`, NEW.`name`, NEW.`phone`); END");
        _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, 'd18709b6afedf9b9ff14af93e6c8157e')");
      } 
....
}

I want to avoid updating data in virtual table if content table got any updates, so I need to remove triggers with names room_fts_content_sync_person_fts_BEFORE_UPDATE and room_fts_content_sync_person_fts_AFTER_UPDATE. Triggers for INSERT and DELETE operations are needed only.

Does Android Room provide such possibility?

Solution

  • I'd suggest adding a CallBack and DROPing the triggers in the onCreate and onOpen methods ensuring that you use DROP TRIGGER IF EXISTS (so a non-existant trigger doesn't result in a failure) e.g.

    public static Callback cb = new Callback() {
    @Override
    public void onCreate(@NonNull SupportSQLiteDatabase db) {
        super.onCreate(db);
        db.execSQL("DROP TRIGGER IF EXISTS room_fts_content_sync_person_fts_BEFORE_UPDATE");
        db.execSQL("DROP TRIGGER IF EXISTS room_fts_content_sync_person_fts_AFTER_UPDATE");
    }

    @Override
    public void onOpen(@NonNull SupportSQLiteDatabase db) {
        super.onOpen(db);
        db.execSQL("DROP TRIGGER IF EXISTS room_fts_content_sync_person_fts_BEFORE_UPDATE");
        db.execSQL("DROP TRIGGER IF EXISTS room_fts_content_sync_person_fts_AFTER_UPDATE");
    }

    @Override
    public void onDestructiveMigration(@NonNull SupportSQLiteDatabase db) {
        super.onDestructiveMigration(db);
    }
};

    In your PersonDatabase you would include .addCallback(cb) in the databaseBuilder.

    You could even use the following that requires no hard coding:-

    public static Callback cb = new Callback() {
    @Override
    public void onCreate(@NonNull SupportSQLiteDatabase db) {
        super.onCreate(db);
        getAndDeleteFTSTriggers(db);
    }

    @Override
    public void onOpen(@NonNull SupportSQLiteDatabase db) {
        super.onOpen(db);
        getAndDeleteFTSTriggers(db);
    }

    @Override
    public void onDestructiveMigration(@NonNull SupportSQLiteDatabase db) {
        super.onDestructiveMigration(db);
    }

    public void getAndDeleteFTSTriggers(SupportSQLiteDatabase db) {
        Cursor csr = db.query("SELECT * FROM sqlite_master WHERE type  = 'trigger' AND instr(sql,'room_fts') > 0 AND instr(sql,'_UPDATE') > 0");
        int sqlidx = csr.getColumnIndex("sql");
        while (csr.moveToNext()) {
            db.execSQL(csr.getString(sqlidx).replace("CREATE TRIGGER IF NOT EXISTS","DROP TRIGGER IF EXISTS"));
        }
        csr.close();
    }
};