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.

Potential uses: