***Virtual Tables In SQLite***
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.
*: Versions of Sqlite prior to 3.6.17 cannot use a virtual tables in a database that makes use
of the {link: /capi3ref.html#sqlite3_enable_shared_cache shared cache}
feature. In version 3.6.17 this restriction was removed.
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);
int (*Rename)(sqlite3_vtab *pVtab, const char *zNew);
};
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:
1: Write all necessary methods.
2: Create an instance of the sqlite3_module structure
containing pointers to all the methods from step 1.
3: Register your sqlite3_module structure using the
sqlite3_create_module() API.
4: 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 {link: wiki?p=LoadableExtensions loadable extension}.
*Create new Virtual Tables in Perl*
The Perl module {link: http://search.cpan.org/~salva/SQLite-VirtualTable/ SQLite::VirtualTable} available from CPAN allows you to write Virtual Table extensions using Perl as the implementation language.