I need to maintain a data table of string columns with full text searching enabled for all the columns.
I understand from Android documentation:
A virtual table behaves similarly to a SQLite table, but reads and writes to an object in memory via callbacks, instead of to a database file.
Would it be safe to use a SQLite virtual table for the purpose and access the table from main UI thread on android without getting an ANR?
Or should I go for one of the third party options like:
From The Virtual Table Mechanism Of SQLite:
From the perspective of an SQL statement, the virtual table object looks like any other table or view.
But behind the scenes, queries and updates on a virtual table invoke callback methods of the virtual table object instead of reading and writing to the database file.
So, from the Android perspective, SQLite Virtual tables behave exactly as any other table or view, and according to the documentation about ANRs:
(...)
In any situation in which your app performs a potentially lengthy operation, you should not perform the work on the UI thread, but instead create a worker thread and do most of the work there. This keeps the UI thread (which drives the user interface event loop) running and prevents the system from concluding that your code has frozen. Because such threading usually is accomplished at the class level, you can think of responsiveness as a class problem. (Compare this with basic code performance, which is a method-level concern.)
In Android, application responsiveness is monitored by the Activity Manager and Window Manager system services. Android will display the ANR dialog for a particular application when it detects one of the following conditions:
No response to an input event (such as key press or screen touch events) within 5 seconds.
A BroadcastReceiver hasn't finished executing within 10 seconds.
So, the answer is no. It's not safe to access a SQLite virtual table from main thread if you want to avoid ANRs. You can use AsyncTasks to access your database.
Update from the comments:
Although the entire table is in memory from the SQLite perspective, from the Android perspective the system needs to access a database and it can cause performance issues for example if the table is large enough, or depending on the implementation,
Because they can be long-running, be sure that you call getWritableDatabase() or getReadableDatabase() in a background thread, such as with AsyncTask or IntentService
(from Saving Data in SQL Databases)
@Chebyr is using a table of 5000 rows and 15 String columns formed through join of 5 cursors to act as an in memory cache for fast access, indexed for filtering operations. So, assuming that the strings are 7 characters long medium, it would be about 500KB, not very memory consuming (depending on the implementation of the indexed solution) and I would not expect to be very time consuming accessing it using indexes. If the data is static I would try using one of the solutions @Chebyr proposed and measuring time and memory (I have just tested CQEngine using a plain Java example and looks very promising).
Also, Best practice for keeping data in memory and database at same time on Android can be helpful.
