I'm having trouble switching between FTS3 tables and regular database tables. My application is very simple and allows the user to add contacts to the database, and they can search for contacts matching the search query (why I used the fts table), and the result is then displayed in the list using onItemclicklistener. But when I click on an item, I get an error. I traced this error back to the database (if I use a regular database, it works, but if I use fts, it doesnβt). So I decided to use both types of databases and wondered if anyone could show me how a trigger is created to synchronize databases.
My first database is CONTACT (database name), and the table is called CONTACTS. The second database table is CONTACTS_FTS. All I'm looking for is COL_NAME, so what is all I need in my CONTACTS_FTS table? I was wondering if anyone can check if my trigger is valid?
public static final String DATABASE_NAME = "CONTACT";
public static final String DATABASE_TABLE = "CONTACTS";
private static final String DATABASE_TABLE_FTS = "CONTACTS_FTS";
private static final int DATABASE_VERSION = 20;
private Context ourContext;
private DbHelper DBHelper;
private static SQLiteDatabase db;
private static final String DATABASE_CREATE =
"CREATE TABLE " + DATABASE_TABLE + " (" +
COL_ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
COL_NAME + " TEXT NOT NULL, " +
COL_EMAIL + " TEXT NOT NULL, " +
COL_CELL + " TEXT NOT NULL, " +
COL_ARRIVAL + " TEXT NOT NULL, " +
COL_DEPARTURE + " TEXT NOT NULL, " +
COL_FLIGHT_NUMBER + " TEXT NOT NULL, " +
COL_HOTEL_ROOM_NUMBER + " TEXT NOT NULL, " +
COL_EVENT1 + " TEXT NOT NULL, " +
COL_EVENT2 + " TEXT NOT NULL, " +
COL_EVENT1_ROOM + " TEXT NOT NULL, " +
COL_EVENT2_ROOM + " TEXT NOT NULL);";
private static final String DATABASE_CREATE_FTS =
"CREATE VIRTUAL TABLE " + DATABASE_TABLE_FTS + " USING fts3(" +
"content=" + "\"CONTACTS\", " +
COL_NAME + ");";
private static final String Trigger =
"CREATE TRIGGER contacts_Trigger " +
"AFTER INSERT "+ "ON " + DATABASE_TABLE +
" BEGIN " +
"INSERT " + DATABASE_TABLE_FTS + " SET " + COL_NAME + " = new.COL_NAME WHERE " + COL_ID + " = old.COL_ID;" +
" END;";
source
share