Menu
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.
950
A virtual table is an object that presents an SQL table interface but which is not stored in the database file, at least not directly. The virtual table mechanism is a feature of SQLite that allows SQLite to access and manipulate resources other than bits in the database file using the powerful SQL query language.
It helps us to provide an abstraction to various users or hide the complexity for users who are accessing data from the table. For example, a user has permission to access particular columns of data rather than the whole table. It can help us to simplify complex queries into a simpler one.
Views in SQL are the virtual tables that do not really exist like the tables of the database. These Views are created by SQL statements that join one or more tables. The views contain rows and columns.
A view is a single table that is derived from other tables. These other tables can be base tables or previously defined views. A view does not necessarily exist in physical form it is considered as virtual table in contrast to base tables whose tuples are actually stored in the database.
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
67
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
