SQLiteDatabase.insert() returns incorrect rowid for virtual tables

Question

When I insert a row into a full-text search database declared like:

CREATE VIRTUAL TABLE foo USING fts3 (bar);

the SQLiteDatabase.insert() method returns an incorrect rowid. If the table is declared like:

CREATE TABLE foo (bar VARCHAR(10));

it returns the correct rowid value. The problem is when I query the database soon after the insert using the rowid returned from the method, the returned Cursor has no records. It works correctly for the first insert into the database only. For subsequent inserts, an incorrect rowid is returned.

Is there anything I need to do to get the correct rowid from the SQLiteDatabase.insert() method?

I'm using Android SDK version 2.1update1.

Thanks, Dan

Update: I ended up using a hack to get the last row id using the following code:

private int getLastRowId(SQLiteDatabase db, String table) {
    Cursor cursor = null;
    try {
        cursor = db
                .rawQuery(String.format(Locale.US, "SELECT MAX(rowid) FROM %s", table), null);
        if (cursor.moveToFirst()) {
            return cursor.getInt(0);
        } else {
            return 0;
        }
    } finally {
        if (cursor != null) {
            cursor.close();
        }
    }
}

In my case, it's safe because only a single user has access to the app. For service apps, this may not work depending on how it is implemented/used.

I believe we have this problem because when performing an insert in fts3 tables, more than one row is inserted. A row is inserted in the subject table and in the fts3 management tables as well.

Answer 1

From SQLite Full-Text Search:

Your table must contain at least 1 TEXT field.

PS: +1: I didn't know about Virtual Tables. Thanks.