Normal Table vs Virtual Table SQLite DB

Question

Android developer manual seems to prefer FTS3 in SQLite DB when search is needed. I read the FTS3 description and it appears that it creates a virtual table instead of a permanent table. What's the difference between virtual table (FTS3) and normal table in SQLite? Is virtual table permanent and remains in the database even after I quit the app? Thank you.

Answer 1

A virtual table allows the SQLite engine to access the contents of a data store, using (usually) non-SQLite code. This allows the developer to add custom functionality to SQLite.

In the case of FTS, it was originally not a part of the SQLite engine. It was code that was external to SQLite, that allowed the end user to do a full text search on data.

Is a virtual table permanent? That depends on the implementation. For FTS the data is permanent. However, you could create an implementation that uses RAM for storage - this obviously would disappear when the application is terminated.

More on virtual tables: http://www.sqlite.org/vtab.html