I'm trying to use FTS in my existing SQLite database, so I'm using an external source for the virtual table- but I'm getting confusing results for an FTS3 table, which I'll end up having to use if I plan on supporting < 11 Android API
In this static function from my DB Contract class I create the initialization string for SQL to create my virtual table.
Db Contract:
public static String getVirtualCreate(){
if (VIRTUAL_TABLE_NAME == "")
return "";
String createstring = "CREATE VIRTUAL TABLE IF NOT EXISTS " + VIRTUAL_TABLE_NAME + " USING fts4(content=\"" + TABLE_NAME +"\",";
for (int a = 0; a < virtualCount(); a++){
createstring += getVirtualColumn(a).getName();
if (a < count() - 1){
createstring += ",";
}
}
createstring += ")";
return createstring;
}
And this code to create the triggers
public static String getVirtualCreateTriggers(){
String rowstring = "";
String postrowstring = "";
for(int a = 0; a < virtualCount(); a++){
rowstring += getVirtualColumn(a);
postrowstring += "new." + getVirtualColumn(a);
if (a < virtualCount() - 1){
rowstring += ",";
postrowstring += ",";
}
}
String ret =
"CREATE TRIGGER IF NOT EXISTS TABLE_NAME_bu BEFORE UPDATE ON TABLE_NAME BEGIN\n" +
" DELETE FROM VIRTUAL_TABLE_NAME WHERE docid=old."+COLUMN_NAME_ID+";\n" +
"END;\n" +
"CREATE TRIGGER TABLE_NAME_bd BEFORE DELETE ON TABLE_NAME BEGIN\n" +
" DELETE FROM VIRTUAL_TABLE_NAME WHERE docid=old."+COLUMN_NAME_ID+";\n" +
"END;\n" +
"CREATE TRIGGER TABLE_NAME_au AFTER UPDATE ON TABLE_NAME BEGIN\n" +
" INSERT INTO VIRTUAL_TABLE_NAME(docid,"+rowstring+") VALUES("+postrowstring+");\n" +
"END;\n" +
"CREATE TRIGGER TABLE_NAME_ai AFTER INSERT ON TABLE_NAME BEGIN\n" +
" INSERT INTO VIRTUAL_TABLE_NAME(docid, "+rowstring+") VALUES("+postrowstring+");\n" +
"END;";
return ret;
}
DB Helper:
The DBContract creation strings seem to work fine in the implementation. If I call something like:
Cursor mCursor = db.rawQuery("SELECT * FROM " + DbContract.PartEntry.VIRTUAL_TABLE_NAME ,null);
It will return all of the values of the original table.
However if I call that exact same code, but change the CreateString to FTS3
String createstring = "CREATE VIRTUAL TABLE IF NOT EXISTS " + VIRTUAL_TABLE_NAME + " USING fts3(content=\"" + TABLE_NAME +"\",";
Even after adding additional rows after the virtual table changed The SELECT * returns 0 rows.
Upon changing it back, everything functions normally.
Secondarily, and probably related, if I use a function like Cursor mCursor = db.rawQuery("SELECT * FROM " + DbContract.PartEntry.VIRTUAL_TABLE_NAME + " WHERE " + DbContract.PartEntry.VIRTUAL_TABLE_NAME + " match '" + search + "'",null);
Where search is an (unescaped on purpose for now) String that is most definitely found in the original AND virtual table (verified by a select *) it returns 0 rows for FTS4, currently un-testable in FTS3 because of the previously mentioned issue with no data appearing.
Question:
Am I doing something incorrectly in my table definitions? Or perhaps is there something else that could cause a wildcard search in FTS3 to return no results, seemingly without any error messages thrown? Unfortunately this is my first experience with anything outside of vanilla SQL, so it's likely I'm doing something wrong somewhere.
If I don't have enough code, or a good enough example, is there a better example or more relevant code I can add to help give a better understanding of the situation?
