SQLite Virtual Table

Here we will learn SQLite Virtual table with example and how to create and use SQLite virtual table in SQLite statements with examples.

SQLite Virtual Table

In SQLite Virtual table is a custom extension to SQLite that allows a developer to define the structure and contents of a table through code. 

 

The SQLite virtual table looks like any other table and a virtual table can be queried, updated, and manipulated using the same SQLite commands that are used on other tables.

 

When processing a normal table, the SQLite library access a database file to retrieve row and column values. In case of a virtual table, the SQLite library calls your code to perform these functions and return data values.

 

The SQLite Virtual table is having some exceptions those are as follows:

 

  1. We cannot create an index on the virtual table.
  2. We cannot create a trigger on a virtual table.
  3. Some virtual tables are read-only.
  4. We cannot add a column to the virtual table by using the ALTER command.

Syntax of SQLite Virtual Table

Following is the syntax of creating a virtual table in SQLite.

 

CREATE VIRTUAL TABLE table_name using module_name

The table_name can be any valid literal and the module_name is the name of an object that implements the virtual table. 

 

The module_name must be registered with the SQLite database connection by using sqlite_create_module() or sqlite3_create_module_v2() prior to issuing the CREATE VIRTUAL TABLE statement.

 

There are two general categories of virtual tables in SQLite those are internal and external.

SQLite Internal Virtual Table

The SQLite Internal virtual tables are self-contained within the database. All the data used by the module is still stored within the database file.

SQLite External Virtual Table

The SQLite external virtual tables are the modules that interface with some type of external data source and that data source might be something as simple as an external file. For example, a module could expose a CSV file or Excel file as an SQL table within the database.

 

To drop a virtual table in SQLite, we can use the normal DROP TABLE command.

Previous
 
Next