AndroidRoomFTS4如何洗掉自動創建的觸發器

我可以控制由 Android Room 自動創建的“插入/更新/洗掉”觸發器，以在內容和虛擬表之間同步資料。我可以洗掉不需要的更新觸發器嗎？

我使用 Android Room 創建了包含外部內容的虛擬表。

內容（普通）表 Person

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

PersonFTS帶有內容表的 FTS虛擬表Person

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

現在，Android的房間創造了4個觸發保持之間同步資料Person和PersonFTS自動。這是PersonDatabase_Impl由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')");
      } 
....
}

virtual table如果content table有任何更新，我想避免更新資料，因此我需要洗掉帶有名稱room_fts_content_sync_person_fts_BEFORE_UPDATE和room_fts_content_sync_person_fts_AFTER_UPDATE. 只需要插入和洗掉操作的觸發器。

Android Room 提供這樣的可能性嗎？

uj5u.com熱心網友回復：

我建議在和方法中添加一個 CallBack 和DROP觸發器以確保您使用（因此不存在的觸發器不會導致失敗）例如onCreateonOpenDROP TRIGGER IF EXISTS

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);
    }
};

在您的PersonDatabase 中，您將包含.addCallback(cb)在 databaseBuilder 中。

您甚至可以使用不需要硬編碼的以下內容：-

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();
    }
};

• note the above HAS NOT been tested on an actual database with FTS. However, it has been tested on a database without FTS (in which case the Cursor will be empty). As such you should obviously test the above.

標籤：安卓 sqlite 触发器 android-room fts4

上一篇：按第一個非空列對sql查詢結果進行排序

下一篇：SQLite：嵌套SELECT陳述句的替代查詢