Sqlite3 virtual table lifetime

Question

Can someone tell me the lifetime of virtual tables created in sqlite3. I have an Android app with a search function and I want to use the quick text search function in sqlite.

I do not know how long these tables remain in the system or do I need to create tables every time I access the application.

Any help?

Answer 1

The SQLite FTS module creates several “internal” tables for each virtual table that you define. These tables are clearly visible in the database schema, so the FTS virtual tables, as well as their underlying data, are completely contained in the database file.

This may differ from other types of virtual table; for example, the VirtualShape extension allows you to read ESRI shaping files (.shp) as tables; these (naturally) are stored separately from the SQLite database file.

In any case, the definition of the virtual table itself is stored in the database file, like a regular table; so the answer to your question is:

No, there is no need to recreate them every time you open the database.

Answer 2

According to the SQLite3 format specification, virtual table definitions are stored in a schema table, like any other table. Any indexes for the virtual table are also stored in the DB file.

I take all of this as meaning that the virtual table is stored in a DB file and thus is saved. You do not need to recreate it every time you open a connection to the database - in any case, it is not so much.

A simple test using the sqlite3 CLI tool and the FTS3 table confirms the following :-)