A virtual table is an object that is registered with an open SQLite database connection. From the perspective of an SQL statement, the virtual table object looks like any other table or view. But behind the scenes, queries from and updates to a virtual table invoke callback methods on the virtual table object instead of reading and writing to the database file.
The virtual table mechanism allows an application to publish interfaces that are accessible from SQL statements as if they were tables. SQL statements can in general do almost anything to a virtual table that they can do to a real table. There are some exceptions, though.
- You cannot create a trigger on a virtual table
- You cannot create additional indices on a virtual table. (Virtual tables can have indices but that must be built into the virtual table implementation. They cannot be added separately using CREATE INDEX statements.)
- You cannot run ALTER TABLE commands against a virtual table.
- Virtual tables cannot be used in a database that makes use of the shared cache feature.
Particular virtual table implementations might impose additional constraints. For example, some virtual implementations might provide read-only tables. Or some virtual table implementations might allow INSERT or DELETE but not UPDATE.
A virtual table might represent an in-memory data structures. Or it might represent a view of data on disk that is not in the SQLite format. Or the application might compute the content of the virtual table on demand.
Here are some postulated uses for virtual tables:
- Access to SQLite's internal symbol table information.
- Access to variables in the host scripting language.
- A full-text search interface
- Spatial Indices
- Enabling SQL manipulation of data in statistics packages like R
- Constructing a federated group of SQLite engines on a cluster for large scale data mining.
- Make a completely different backing store for SQLite tables: memory mapped files, other databases, etc.
- Create an interface between user interfaces and low-level drivers of an embedded system. User interfaces can use SQLite to control the system.
Usage
Create a virtual table using a CREATE VIRTUAL TABLE statement. This statement creates a table with a particular name and associates the table with a "module".
CREATE VIRTUAL TABLE tablename USING modulename;
You can also provide comma-separated arguments to the module following the module name:
CREATE VIRTUAL TABLE tablename USING modulename(arg1, arg2, ...);
The format of the arguments to the module is very general. Each argument can consist of keywords, string literals, identifiers, numbers, and punctuation. The arguments are passed as written (as text) into a constructor for the virtual table when the virtual table is created and the constructor is responsible for parsing and interpreting the arguments. The argument syntax is sufficiently general that a virtual table implementation can, if it wants to, interpret its arguments as column definitions in an ordinary CREATE TABLE statement. The implementation could also impose some other interpretation on the arguments. The SQLite core does not care.
Once a virtual table has been created, it can be used like any other table with the exceptions noted above and imposed by specific virtual table implementations. A virtual table is destroyed using the ordinary DROP TABLE syntax.
Implementation
Several new data structures are used by the virtual table implementation:
typedef struct sqlite3_vtab sqlite3_vtab; typedef struct sqlite3_index_info sqlite3_index_info; typedef struct sqlite3_vtab_cursor sqlite3_vtab_cursor; typedef struct sqlite3_module sqlite3_module;
The sqlite3_module structure defines a module object used to implement a virtual table. Think of a module as a class from which you can construct multiple virtual tables having similar properties. For example, one might have a module that provides read-only access to comma-separated-value (CSV) files on disk. That one module can then be used to create several virtual tables where each virtual table refers to a different CSV file.
The module structure contains methods that are invoked by SQLite to perform various actions on the virtual table such as creating new instances of a virtual table or destroying old ones, reading and writing data, searching for and deleting, updating, or inserting rows. The module structure is explained in more detail below.
Each virtual table instance is represented by an sqlite3_vtab structure. The sqlite3_vtab structure looks like this:
struct sqlite3_vtab {
const sqlite3_module *pModule;
int nRef;
char *zErrMsg;
};
Virtual table implementations will normally subclass this structure to add additional private and implementation-specific fields. The nRef field is used internally by the SQLite core and should not be altered by the virtual table implementation. The virtual table implementation can pass error message text to the core by putting an error message string obtained from sqlite3_mprintf() in zErrMsg. Prior to assigning a new value to zErrMsg, the virtual table implementation should free any prior content of zErrMsg using sqlite3_free(). Failure to do this might result in a memory leak. The SQLite core will free and zero the content of zErrMsg when it delivers the error message text to the client application or when it destroys the virtual table. The virtual table implementation only needs to worry about freeing the zErrMsg content when it overwrites the content with a new, different error message.
Compatibility Notice: The sqlite3_vtab structure has been modified for SQLite version 3.3.8. Virtual table implementations that are built as loadable extensions for SQLite version 3.3.7 and earlier will need to be recompiled in order to work with SQLite versions 3.3.8 and later. Because the the virtual table mechanism is still considered an experimental API as of version 3.3.8, such changes are allowed without breaking SQLite's policy of not making changes that break legacy code.
The sqlite3_vtab_cursor structure represents a pointer into a virtual table. This is what an sqlite3_vtab_cursor looks like:
struct sqlite3_vtab_cursor {
sqlite3_vtab *pVtab;
};
Once again, practical implementations will likely subclass this structure to add additional private fields.
The sqlite3_index_info structure is used to pass information into out of the xBestIndex method of the module that implements a virtual table. For additional information on this structure, see the VirtualTableBestIndexMethod documentation.
Before a CREATE VIRTUAL TABLE statement can be run, the module specified in that statement must be registered with the database connection. This is accomplished using this routine:
int sqlite3_create_module(
sqlite3 *db, /* SQLite connection to register module with */
const char *zName, /* Name of the module */
const sqlite3_module *, /* Methods for the module */
void * /* Client data for xCreate/xConnect */
);
The sqlite3_create_module() command associates a module name with an sqlite3_module structure and a separate client data that is specific to each module. The module structure looks like this:
struct sqlite3_module {
int iVersion;
int (*xCreate)(sqlite3*, void *pAux,
int argc, char **argv,
sqlite3_vtab **ppVTab,
char **pzErr);
int (*xConnect)(sqlite3*, void *pAux,
int argc, char **argv,
sqlite3_vtab **ppVTab,
char **pzErr);
int (*xBestIndex)(sqlite3_vtab *pVTab, sqlite3_index_info*);
int (*xDisconnect)(sqlite3_vtab *pVTab);
int (*xDestroy)(sqlite3_vtab *pVTab);
int (*xOpen)(sqlite3_vtab *pVTab, sqlite3_vtab_cursor **ppCursor);
int (*xClose)(sqlite3_vtab_cursor*);
int (*xFilter)(sqlite3_vtab_cursor*, int idxNum, const char *idxStr,
int argc, sqlite3_value **argv);
int (*xNext)(sqlite3_vtab_cursor*);
int (*xEof)(sqlite3_vtab_cursor*);
int (*xColumn)(sqlite3_vtab_cursor*, sqlite3_context*, int);
int (*xRowid)(sqlite3_vtab_cursor*, sqlite_int64 *pRowid);
int (*xUpdate)(sqlite3_vtab *, int, sqlite3_value **, sqlite_int64 *);
int (*xBegin)(sqlite3_vtab *pVTab);
int (*xSync)(sqlite3_vtab *pVTab);
int (*xCommit)(sqlite3_vtab *pVTab);
int (*xRollback)(sqlite3_vtab *pVTab);
int (*xFindFunction)(sqlite3_vtab *pVtab, int nArg, const char *zName,
void (**pxFunc)(sqlite3_context*,int,sqlite3_value**),
void **ppArg);
};
The module structure defines all of the methods for each virtual table object. The module structure also contains the iVersion field which defines the particular edition of the module table structure. Currently, iVersion is always 1, but in future releases of SQLite the module structure definition might be extended with additional methods and in that case the iVersion value will be increased.
The rest of the module structure consists of methods used to implement various features of the virtual table. Details on what each of these methods do are provided on the VirtualTableMethods page.
The virtual table mechanism assumes that each database connection keeps its own copy of the database schema. Hence, the virtual table mechanism cannot be used in a database that has shared cache enabled. The sqlite3_create_module() interface will return an error if the shared cache feature is enabled.
Creating New Virtual Tables
Follow these steps to create your own virtual table:
- Write all necessary methods.
- Create an instance of the sqlite3_module structure containing pointers to all the methods from step 1.
- Register your sqlite3_module structure using the sqlite3_create_module() API.
- Run a CREATE VIRTUAL TABLE command that specifies your module in the USING clause.
The only really hard part is step 1. You might want to start with an existing virtual table implementation and modify it to suit your needs. There are several virtual table implementations in the SQLite source tree (for testing purposes). You might use one of those as a guide. Locate these test virtual table implementations by searching for "sqlite3_create_module".
You might also want to implement your new virtual table as a loadable extension.
Create new Virtual Tables in Perl
The Perl module SQLite::VirtualTable available from CPAN allows you to write Virtual Table extensions using Perl as the implementation language.