Sqlite Virtual Table vs Normal Table

“Android developer prefers FTS3 in Sqlite database whenever there is a need of search. I went through the description of the Virtual Table (FTS3) and found that table created is a virtual table. Actually, what is the difference between normal Sqlite table and virtual table?”

With the coming up of Sqlite, technology saw many new changes; Smartphone, embedded systems etc. came into. The Sqlite is a lightweight embedded type database engine with many components, used in all embedded systems. However, whenever users come across a Sqlite database, he/she might wonder about the difference between virtual and normal tables. The above discussed scenario is one such example query. Here, in the blog you will get the difference sort out.

What’s the Difference Between Virtual Table and Normal Table in Sqlite?

Virtual Table

A Virtual Table is a characteristic extension to Sqlite and looks like normal table only. One can query, manipulate, update, etc. on the virtual table similar to other tables. Though it looks like table, virtual table enables to structure the table through codes.For updating or manipulating virtual tables, callback methods have to be invoked. Here, you won’t see the mechanism of reading or writing to database.

Here, to create virtual table you need Sqlite module, an advanced feature.

Syntax: CREATE VIRTUAL TABLE table_name USING module_name (arg1, arg2, arg3 …)

Why are these tables used?

Actually, the use of virtual table is for the linking of Sqlite database to other alternative data sources.

Functions Used In Implementing Virtual Tables:

Some of the functions used in the virtual table implementation:

xCreate()

This is called when the table instance is created during executing CREATE VIRTUAL TABLE.

xConnect()

This function is more or less similar to the above function and is called during the loading of the database which has virtual table instances.

xDisconnect()

Used when the database carrying virtual table instance is closed.

xDestroy()

This is called when Sqlite virtual table instance is dropped using the DROP TABLE command.

Similarly, there are many other functions called for various purposes like; for cursor, control etc.

Is there a similar function in creating a normal table? How is virtual table different from normal table?

From the coming sessions you will get all the answers to these queries.

Normal Table

Database contains tables, all the data get stored and managed properly in a table. We can call it as a part of the database. The CREATE TABLE command creates a table in the database. To this created database user adds the values. For creating a normal database table, there are no callback methods to be invoked.

Syntax for creating a table is; CREATE TABLE followed by database_name.table_name.

Example;

CREATE TABLE database_name.table_name(
Column1 datatype PRIMARY KEY(),
......
);

Other commands performed on table are DROP, ALTER, etc.

Constructing a virtual table is not as easy as creating a normal table. By applying the above command one can create normal table, it’s simple. Though both are tables, they are not alike. The SQL statements can be used for a virtual table but, there are some exceptions.

Difference Seen In Sqlite Virtual Table

Some of the differences are:

• Creating triggers on virtual table is not possible
• Creation of extra indices is not possible
• ALTER TABLE, ADD COLUMN etc. commands cannot be used
• Imposes additional constraints i.e. some of the implementation allows read-only tables or some allows INSERT, DELETE commands but, doesn’t allow UPDATE etc.

Apart from these, the main difference lies in the place where the data come from. If we are taking the case of virtual table then, the library calls code to perform and retrieve the data. In case of normal table, the Sqlite library accesses the DB file and returns the value from the row and column.

Note: Both normal table and virtual table are dropped using the DROP command.

It can be said that a virtual table represents an in-memory structure of data. Else, represents view of data on disk that is not available in Sqlite format.

Observation

Virtual table enables Sqlite engine for accessing the data with the help of non-Sqlite codes and supports the addition of custom functionalities to the Sqlite. A Virtual table can be permanent or temporary, it depends on the way of implementation. Virtual table cannot be used like a normal table and while creating virtual table, one has to take care.