迁移到会议室:如何进行全文搜索?(Migrating to Room: How to do Full Text Search?)

12 IT屋

I was looking how to migrate an existing application to Room, from plain Sqlite, and I haven't been able to find how could I migrate a part that uses FTS.

Right now, I have a virtual table that's filled by a trigger every time a row is inserted or updated:

private static final String CREATE_VIRTUAL_TABLE = "CREATE VIRTUAL TABLE " + FTS_VIRTUAL_TABLE +
            " using fts4 (content='" + TABLE_NOTIFICATION + "', " + COLUMN_TITLE + ")";

private static void createVirtualTriggers(SQLiteDatabase database){
      database.execSQL("CREATE TRIGGER virtual_bu BEFORE UPDATE ON " + TABLE_NOTIFICATION + " BEGIN\n" +  "  DELETE FROM " + FTS_VIRTUAL_TABLE + " WHERE docid=old.rowid;\n" +    "END;");
      database.execSQL("CREATE TRIGGER virtual_bd BEFORE DELETE ON " + TABLE_NOTIFICATION + " BEGIN\n" +  "  DELETE FROM " + FTS_VIRTUAL_TABLE + " WHERE docid=old.rowid;\n" +    "END;");
      database.execSQL("CREATE TRIGGER virtual_au AFTER UPDATE ON " + TABLE_NOTIFICATION + " BEGIN\n" +  "  INSERT INTO " + FTS_VIRTUAL_TABLE + "(docid, " + COLUMN_TITLE + ") VALUES(new.rowid, new." + COLUMN_TITLE +");\n" + "END;");
      database.execSQL("CREATE TRIGGER virtual_ai AFTER INSERT ON " + TABLE_NOTIFICATION + " BEGIN\n" +  "  INSERT INTO " + FTS_VIRTUAL_TABLE + "(docid, " + COLUMN_TITLE + ") VALUES(new.rowid, new." + COLUMN_TITLE +");\n" +                   "END;");
  }

Is there any way to achieve this same functionality with Room?

解决方案

Not really. See this issue and this issue where this request is being tracked.

You can always work with the database directly for FTS scenarios, such as creating the table and triggers in your code snippet in the question, by calling getOpenHelper() on the RoomDatabase and using it similar to SQLiteOpenHelper. Room will ignore new tables and stuff that you create behind its back. So, if your FTS work happens to be fairly separate from the rest of your database work, you might be able to go that route.

我一直在寻找如何将现有的应用程序从普通的Sqlite迁移到Room的方法,但我一直无法找到如何迁移使用FTS的部件的方法。



现在,我有一个虚拟表,每次插入或更新一行时,触发器都会填充该表:



  private静态最终字符串CREATE_VIRTUAL_TABLE =" CREATE VIRTUAL TABLE" + FTS_VIRTUAL_TABLE + 
"使用fts4(content ='" + TABLE_NOTIFICATION +"'," + COLUMN_TITLE +")";

私有静态无效createVirtualTriggers(SQLiteDatabase数据库){
database.execSQL(" CREATE TRIGGER virtual_bu在更新之前" + TABLE_NOTIFICATION +" BEGIN\n" +"删除自" + FTS_VIRTUAL_TABLE + " WHERE docid = old.rowid; \n" +" END;");
database.execSQL("删除前创建触发器" );
database.execSQL(" CREATE TRIGGER virtual_au AFTER UPDATE ON" + TABLE_NOTIFICATION +" BEGIN\n" +" INSERT INTO" + FTS_VIRTUAL_TABLE +"(docid," + COLUMN_TITLE +")值(new.rowid,新。" + COLUMN_TITLE +"); \n" +" END;");
database.execSQL(" CREATE TRIGGER virtual_ai AFTER INSERT ON" + TABLE_NOTIFICATION +" BEGIN\n" +" INSERT INTO" + FTS_VIRTUAL_TABLE +"(docid," + COLUMN_TITLE +")VALUES(new.rowid,新。" + COLUMN_TITLE +"); \n" +" END;");
}


使用Room有没有办法实现相同功能?


解决方案

不是。请参见此问题此问题正在跟踪此请求。



您始终可以直接针对FTS场景使用数据库,例如通过在 RoomDatabase 上调用 getOpenHelper(),其用法与 SQLiteOpenHelper 。 Room将忽略在其后面创建的新表和东西。因此,如果您的FTS工作恰好与其余数据库工作分开,那么您也许可以走这条路。


本文地址:IT屋 » 迁移到会议室:如何进行全文搜索?