sqlite3 virtual tables lifetime

Question

Can someone please tell me the lifetime of the virtual tables created in sqlite3. I have an android application with a search feature, and i want to use the fast text search feature of sqlite.

I do not know how long these tables stay in the system or if i need to create the tables each time i access the application.

Any help?

Answer 1

The SQLite FTS module creates several 'internal' tables for every virtual table you define. Those tables are plainly visible in the database schema, so FTS virtual tables as well as their underlying data are completely contained in the database file.

This might be different with other types of virtual table; e.g. the VirtualShape extension allows ESRI shapefiles (.shp) files to be read as tables; those are (naturally) stored separately from the SQLite database file.

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

No, there's no need to re-create them every time you open the database.

Answer 2

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

I take all this to mean that a virtual table is stored in the DB file and thus persistent. You should not have to recreate it each time you open a DB connection - it wouldn't make much sense like that, anyway.

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