SQLite CVSTrac

Page History

This page describes the methods in the sqlite3_module structure used by VirtualTables. You should have already read and understood VirtualTables before reading this page.

xCreate

  int (*xCreate)(sqlite3 *db, void *pAux,
               int argc, char **argv,
               sqlite3_vtab **ppVTab);

This method is called to create a new instance of a virtual table in response to a CREATE VIRTUAL TABLE statement. The db parameter is a pointer to the SQLite database connection that is executing the CREATE VIRTUAL TABLE statement. The pAux argument is the copy of the client data pointer that was the fourth argument to the sqlite3_create_module() call that registered the current module. The argc and argv parameters report the arguments to the module name in the CREATE VIRTUAL TABLE statement.

The job of this method is to construct the new virtual table and return a pointer to it in *ppVTab.

As part of the task of creating a new sqlite3_vtab structure, this method most invoke sqlite3_declare_vtab() to tell the SQLite core about the columns and datatypes in the virtual table. The sqlite3_declare_vtab() API has the following prototype:

    int sqlite3_declare_vtab(sqlite3 *db, const char *zCreateTable)

The first argument to sqlite3_declare_vtab() is the pointer to the sqlite database connection. The second argument is a '\000'-terminated UTF-8 string that contains a well-formed CREATE TABLE statement that defines the columns in the virtual table and their data types. The name of the table in this CREATE TABLE statement is ignored, as are all constraints. Only the column names and datatypes matter.

xCreate method should return SQLITE_OK if it is successful in creating the new virtual table, or SQLITE_ERROR if it is not successful.

xConnect

  int (*xConnect)(sqlite3*, void *pAux,
               int argc, char **argv,
               sqlite3_vtab **ppVTab);

The xConnect method is very similar to xCreate. It has the same parameters and constructs a new sqlite3_vtab structure just like xConnect. And it must also call sqlite3_declare_vtab() like xCreate.

The difference is that xConnect is called to establish a new connection to an existing virtual table whereas xCreate is called to create a new virtual table from scratch.

The xCreate and xConnect methods are only different when the virtual table has some kind of backing store that must be initialized the first time the virtual table is created. The xCreate method creates and initializes the backing store. The xConnect method just connects to an existing backing store.

As an example, consider a virtual table implementation that provides read-only access to existing comma-separated-value (CSV) files on disk. There is no backing store that needs to be created or initialized for such a virtual table (since the CSV files already exist on disk) so the xCreate and xConnect methods will be identical for that module.

Another example is a virtual table that implements a full-text index. The xCreate method must create and initialize data structures to hold the dictionary and posting lists for that index. The xConnect method, on the other hand, only had to locate and use an existing dictionary and posting lists that were created by a prior xCreate call.

xBestIndex

This method is documented separately.

xDisconnect

  int (*xDisconnect)(sqlite3_vtab *pVTab);

This method releases a connection to a virtual table. The virtual table is not destroyed and any backing store associated with the virtual table persists. This method is the opposite of xConnect.

xDestroy

  int (*xDestroy)(sqlite3_vtab *pVTab);

This method releases a connection to a virtual table, just like the xDisconnect method, and it also destroys the underlying table implementation. This method is the opposite of xCreate.

The xDisconnect method is called whenever a database connection that uses a virtual table is closed. The xDestroy method is only called when a DROP TABLE statement is executed against the virtual table.

xOpen

  int (*xOpen)(sqlite3_vtab *pVTab, sqlite3_vtab_cursor **ppCursor);

xClose

  int (*xClose)(sqlite3_vtab_cursor*);

xFilter

  int (*xFilter)(sqlite3_vtab_cursor*, int idxNum, const char *idxStr,
                int argc, sqlite3_value **argv);

xNext

  int (*xNext)(sqlite3_vtab_cursor*);

xColumn

  int (*xColumn)(sqlite3_vtab_cursor*, sqlite3_context*, int);

*xRowid

  int (*xRowid)(sqlite3_vtab_cursor*, sqlite_int64 *pRowid);

xUpdate

  int (*xUpdate)(sqlite3_vtab *, int, sqlite3_value **, sqlite_int64 *);

sqlite - Virtual Table Methods

Page History