How to create table using sqlite fts5 in android?

Question

I want to create a table using fts5 extension

CREATE VIRTUAL TABLE TABLE_FTS_FOOD_ITEM USING fts5 (content='food_items', Shrt_Desc, Energy_Kcal);

but I am getting this error.

android.database.sqlite.SQLiteException: no such module: fts5

Earlier I was using fts4 that was working fine. Can I use fts5 with sqLitedatabase in android?

Corticosterone

Answer 1

FTS5 is not available before SQLite 3.9.0, and even then disabled by default, so it is unlikely to be part of any SQLite library shipped with Android.

Rammish

Okay, Actually I want to query a column order by rank. Can I do the same thing with FTS4? – Corticosterone

Currently, I am using this query to get the desired result but I am not sure this is the right way or not, SELECT Shrt_Desc FROM table WHERE Shrt_Desc MATCH "apple*" ORDER BY (CASE WHEN Shrt_Desc= "apple" THEN 1 WHEN Shrt_Desc LIKE "apple%" THEN 2 ELSE 3 END), Shrt_Desc asc; – Corticosterone

To ask a different question, click "Ask Question". (Or read the documentation.) – Rammish

This is still true, as of Android 14 / API 31 bundling SQLite 3.39.2 does not have FTS5 support. – Mattison

Answer 2

Download this project: https://github.com/requery/sqlite-android Then add -DSQLITE_ENABLE_FTS5 to sqlite_flags in sqlite-android\src\main\jni\sqlite\Android.mk and rebuild (gradlew assemble in project root dir). And Voila! You have Android libs with FTS5 enabled.

Montgomery

FTS5 is enabled from 3.30.1 of the library. See the related issue. – Mattison