Small. Fast. Reliable.
Choose any three.
About Sitemap Documentation Download License News Developers Support
Proposed Virtual Table Mechanism For 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 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 do anything to a virtual table that they can do to a real table (subject to the read-only or write-only constraint) except that one cannot DROP a virtual table and the virtual table does not appear in the SQLITE_MASTER table.

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.

Two data structures are used by the virtual table implementation: 

  typedef struct sqlite3_vtab_cursor sqlite3_vtab_cursor;
  typedef struct sqlite3_vtab sqlite3_vtab;

The sqlite3_vtab_cursor structure is an opaque type that represents a pointer into a virtual table. The sqlite3_vtab structure is the method table for the virtual table object. A read-only instance of this structure defines the format of the virtual table and the callback methods that are invoked when SQL is executed against the virtual table. The sqlite3_vtab structure looks like this: 

  struct sqlite3_vtab {
    /* The first group of fields define the format of the virtual table
    */
    int nCol;                 /* Number of columns in the virtual table */
    const char **azColName;   /* Name of each column */
    const char **azDeclType;  /* Declared data type for each column */
    int intPrimKey;           /* The INTEGER PRIMARY KEY column.  -1 for none */
    int nIdx;                 /* Number of indices */
    const struct _vtab_idx {  /* For each index */
      int nCol;                    /* Number of columns in this index */
      const struct _vtab_idx_col { /* For each column in this index */
        int iCol;                      /* The column in table that is indexed */
        const char *zColl;             /* Name of the collating sequence */
      } *aCol;
    } *aIdx;


    /* The rest of this structure define callbacks for various operations
    ** against the virtual table
    */
    int (*xConnect)(void *pArg);
    int (*xOpenCursor)(void *pArg, sqlite3_vtab_cursor**, int idx);
    int (*xInsert)(sqlite3_vtab_cursor*, sqlite3_int64 rowid, int nCol,
                   sqlite3_value **apCol);
    sqlite3_int64 (*xNewRowid)(sqlite3_vtab_cursor*);
    int (*xDelete)(sqlite3_vtab_cursor*);
    int (*xSeek)(sqlite3_vtab_cursor*, int, sqlite3_value**, int*);
    int (*xFirst)(sqlite3_vtab_cursor*);
    int (*xLast)(sqlite3_vtab_cursor*);
    int (*xNext)(sqlite3_vtab_cursor*);
    int (*xPrevious)(sqlite3_vtab_cursor*);
    int (*xColumn)(sqlite3_vtab_cursor*, sqlite3_context*, int);
    int (*xCloseCursor)(sqlite3_vtab_cursor*);
    int (*xBegin)(void *pArg);
    int (*xSync)(void *pArg);
    int (*xCommit)(void *pArg);
    int (*xRollback)(void *pArg);
    int (*xIsInTrans)(void *pArg);
    void (*xDisconnect)(void *pArg);
  };

A virtual table implementation must define and initialize an instance of the sqlite_vtab structure and pass a pointer to that structure into the sqlite3_create_virtual_table API in order to register the virtual table object with an SQLite database connection. The details of how a sqlite3_vtab object must be initialized are documented separately.

The sqlite3_create_virtual_table API used to create a new virtual table is as follows: 

  int sqlite3_create_virtual_table(
    sqlite3 *db,
    const char *zTableName,
    const sqlite3_vtab *pVTab,
    void *pArg
  );

The zTableName paramater is the name of the table to be created. The pVTab parameter defines the characteristics of the table. The pArg parameter can have additional information needed by the virtual table instance. The pArg parameter is passed unaltered into many of the callback methods defined in the sqlite3_vtab structure.

The latest thoughts on virtual tables as of 2006-06-01:

The concept of a virtual table has expanded to include a virtual view. A virtual table is an entity unto itself. A virtual view provides an alternative access mechanism to an existing real table. Full-text search will likely use virtual views instead of virtual tables.

A virtual table or view is created using SQL as follows: 

   CREATE TABLE vtabname USING c_module(arg-list);
   CREATE VIEW vview ON table(column-list) USING c_module(arg-list);

For example, suppose we have a table containing the content of email messages: 

   CREATE TABLE email(xto TEXT, xfrom TEXT, subj TEXT, body TEXT);

To create a full-text index on the subject and body of this table, one might do the following: 

   CREATE VIEW emailfts ON email(subj,body) USING fulltextsearch1;

The "fulltextsearch1" identifier is associated with a C-language module that is registered separately, in a mannery similar to the say collating sequences and user-defined functions are registered. The argument-list to the module is optional. No arguments are shown in the example above, though in practice we would probably add arguments to specify various options to the full text search engine, such as what tokenizer and stemmer algorithms to use and whether or not token positional information should be stored in order to help with phrase queries.

The CREATE TABLE and CREATE VIEW statements for virtual tables are stored in the SQLITE_MASTER table and are reread when a new connection is created or after a schema change. The implementation modules (the "fulltextsearch1") need not be registered at the time the virtual table or view is created. But the virtual table or view cannot be used until after the module has been registered.

A full-text index would require additional real tables and/or indices to store auxiliary information needed to help speed the full-text search. These additional tables and indices would be created automatically, if they do not already exist, when the CREATE VIEW statement is first executed, or when the fulltextsearch1 module is first registered - whichever comes second. Triggers would also be created to cause changes to the email table to make corresponding changes to the real tables and indices that back the virtual view. A new C-language API will be developed that allows the module to mark these triggers as protected so that they cannot be accidently dropped. These triggers would also provent other processes from modifying the email table unless they had the fulltestsearch1 module registered.

Question: Should we allow multiple tables to be associated to a virtual view?

Virtual tables and view can be used in the FROM clause of a SELECT statement just like regular tables and views. But in addition, virtual tables and views in the FROM clause can take arguments. These arguments are passed to the open-cursor method of the C-language implementation. The arguments are used, for example, to specify the search string: 

   SELECT id FROM emailfts('subj:features virtual table')
    ORDER BY date DESC LIMIT 30;

Potential uses: