Search the entire SQLite database using FTS3/FTS4?

Question

I have a sqlite database which contains lots of html content which I display using a webview. Now I want to add search functionality into my app. I have two tables one for categories and the other for subcategories. Followings are the create queries.

  String CREATE_SUBCATEGORY_TABLE = "CREATE VIRTUAL TABLE " + TABLE_SUBCATEGORY_LIST + " USING fts3("
        + KEY_ID + " INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL," + KEY_NAME + " TEXT,"
        + KEY_DESC + " TEXT," + KEY_CATEGORY_ID + " INTEGER,"
        + KEY_CONTENT1 + " TEXT," + KEY_CONTENTTYPE1 + " TEXT,"
        + KEY_CONTENT2 + " TEXT," + KEY_CONTENTTYPE2 + " TEXT,"
        + KEY_CONTENT3 + " TEXT," + KEY_CONTENTTYPE3 + " TEXT,"
        + KEY_CONTENT4 + " TEXT," + KEY_CONTENTTYPE4 + " TEXT,"
        + KEY_CONTENT5 + " TEXT," + KEY_CONTENTTYPE5 + " TEXT,"
        + KEY_CONTENT6 + " TEXT," + KEY_CONTENTTYPE6 + " TEXT,"
        + KEY_CONTENT7 + " TEXT," + KEY_CONTENTTYPE7 + " TEXT,"
        + KEY_CONTENT8 + " TEXT," + KEY_CONTENTTYPE8 + " TEXT,"
        + KEY_CONTENT9 + " TEXT," + KEY_CONTENTTYPE9 + " TEXT,"
        + KEY_CONTENT10 + " TEXT," + KEY_CONTENTTYPE10 + " TEXT,"
        + KEY_CONTENT11 + " TEXT," + KEY_CONTENTTYPE11 + " TEXT,"
        + KEY_CONTENT12 + " TEXT," + KEY_CONTENTTYPE12 + " TEXT,"
        + KEY_CONTENT13 + " TEXT," + KEY_CONTENTTYPE13 + " TEXT,"
        + KEY_CONTENT14 + " TEXT," + KEY_CONTENTTYPE14 + " TEXT,"
        + KEY_CONTENT15 + " TEXT," + KEY_CONTENTTYPE15 + " TEXT,"
        + KEY_ORDERID + " INTEGER," + KEY_STATUS + " TEXT,"
        + KEY_UPDATED + " TEXT, FOREIGN KEY (" + KEY_CATEGORY_ID + ") REFERENCES " + CAT_TABLE + "(" + KEY_CATEGORY_ID + ")" + ")";

String CREATE_CATEGORYLIST_TABLE = "CREATE VIRTUAL TABLE " + CAT_TABLE + " USING fts3("
        + KEY_CATEGORY_ID + " INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL," + KEY_CATNAME + " TEXT,"
        + KEY_CATDESC + " TEXT," + KEY_STATUS + " TEXT," + KEY_ORDERID + " INTEGER " + ")";

So now all my tables a virtual table's.Now what should be my search query like.I have created the following search query but I am not sure if it will work.

public Cursor searchByInputText(String inputText) throws SQLException {

SQLiteDatabase myDb = this.getReadableDatabase();
String[] selectionArgs = { inputText };

String query = "SELECT docid as _id, " +
          KEY_CATNAME +" ,"+KEY_CATDESC + " from " + CAT_TABLE +
        " where " + CAT_TABLE + " MATCH ? '," + selectionArgs + "';";

Cursor mCursor = myDb.rawQuery(query,null);

if (mCursor != null) {
    mCursor.moveToFirst();
}
return mCursor;

}

I want to search first inside the categories table for the term being searched else search subcategory table.

What particular part of the [documentation](http://www.sqlite.org/fts3.html) do you not understand? — CL., Sep 16 '16 at 07:19
@CL. It says I need to create virtual tables how many virtual tables should I create and how will I search the HTML content? Cant I just use the existing tables instead is there a way to convert the existing tables into virtual tables. — AndroidNewBee, Sep 16 '16 at 07:21
1. As many as you need. 2. By querying the FTS table(s). 3. No. 4. Yes. — CL., Sep 16 '16 at 07:27
@CL. now I have made my tables virtual.Now should I search the entire table for the matching term or is there a better way? — AndroidNewBee, Sep 19 '16 at 06:00
Thanks for the edit @cricket_007.Can you give a more detailed answer to my question? — AndroidNewBee, Sep 19 '16 at 08:30
FTS tables store only text, and doing normal queries on FTS tables is inefficient. Is doing full-text searches the only purpose of these tables? — CL., Oct 04 '16 at 17:41

Search the entire SQLite database using FTS3/FTS4?

0 Answers0