2

I am using sqlite FTS3 database, with my Android application.

   public Cursor getWordMatches(String query, String[] columns) {
        String selection = KEY_WORD + " MATCH ?";
        String[] selectionArgs = new String[] {query+"*"};
        return query(selection, selectionArgs, columns);
   }

.....

private Cursor query(String selection, String[] selectionArgs, String[] columns) {
    SQLiteQueryBuilder builder = new SQLiteQueryBuilder();
    builder.setTables(FTS_VIRTUAL_TABLE);
    builder.setProjectionMap(mColumnMap);     

    Cursor cursor = builder.query(mDatabaseOpenHelper.getReadableDatabase(),
            columns, selection, selectionArgs, null, null, null);

    if (cursor == null) {
        return null;
    } else if (!cursor.moveToFirst()) {
        cursor.close();
        return null;
    }
    return cursor;
}

If I make a query and search for 'house' I will get:

household 
house 
apartment house 
big house

(I am getting everything that has house in it, in regex house)

How can I get this kind of result for my query?

household
house

(So i only want results that start with house, in regex house*)

My android application FORCE CLOSES if I use LIKE statement(maybe because db has 200000 rows). So should I somehow combine MATCH and LIKE statement? Is it even possible to do this on Android, if it is not maybe I should try to run some regex on cursor after I get it from this query?

stratocaster
  • 21
  • 2
  • 5
  • Please post the full stacktrace of the force close. – Cheryl Simon Dec 13 '10 at 19:39
  • when i use LIKE: String selection = KEY_WORD + " LIKE "; String[] selectionArgs = new String[] {"'"+query+"%'"}; this is the error(if you need full stacktrace let me know and ill upload it somewhere): 12-13 21:11:50.756: ERROR/AndroidRuntime(267): android.database.sqlite.SQLiteException: near ")": syntax error: , while compiling: SELECT rowid AS _id, suggest_text_1, suggest_text_2 FROM FTSdictionary WHERE (suggest_text_1 LIKE ) – stratocaster Dec 13 '10 at 20:18

1 Answers1

0

Full text search tables should always use MATCH and not LIKE, because a LIKE query performs a full scan of the table which defeats the purpose of creating the FTS table in the first place.

To solve your problem, trying using a tokenizer other than the default when creating your fts table:

-- Create a table using the simple tokenizer.
CREATE VIRTUAL TABLE simple USING fts3(tokenize=porter);

By default, SQLite uses a simple tokenizer which may be generating the matches you don't want. Switch to the porter tokenizer and see what happens. Worst case, you can implement your own custom tokenizer. See the SQLite documentation for further info.

Jerry Brady
  • 3,050
  • 24
  • 30