sqlite - Virtual Tables

Not logged in

• Honeypot
• Browse
• Home
• Login
• Reports
• Search
• Ticket
• Timeline
• Wiki

• Contents
• Diff
• Text

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 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.

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 Indexes
• 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.

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 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. Or the implementation could 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;
  };

Virtual table implementations will normally subclass this structure to add additional private and implementation-specific fields.

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;
    const char *zName;
    int (*xCreate)(sqlite3*, void *pAux,
                 int argc, char **argv,
                 sqlite3_vtab **ppVTab);
    int (*xConnect)(sqlite3*, void *pAux,
                 int argc, char **argv,
                 sqlite3_vtab **ppVTab);
    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);
  };

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 module structure also contains the zName field which is the name of the module. The zName field used for error messages.

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.

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 loadable extension.