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 must 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 zero-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);

The method creates a new cursor used for reading information out of a virtual table. The cursor is returned in *ppCursor.

The cursor is not immediately usable. It must first be positioned using xFilter. Then elements of a row of the virtual table can be accessed using xColumn and xRowid.

The xClose method is used to close the cursor.

xClose

  int (*xClose)(sqlite3_vtab_cursor*);

This method closes a cursor previously opened by xOpen. The SQLite core will always call xClose once for each cursor opened using xOpen.

xFilter

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

This method to begin a search of a virtual table. The first argument is a cursor opened by xOpen. The next two argument define a particular search index previously choosen by xBestIndex The specific meanings of idxNum and idxStr are unimportant as long as xFilter and xBestIndex agree on what that meaning is.

The xBestIndex function may have requested the values of certain expressions using the aConstraintUsage[].argvIndex values of the sqlite3_index_info structure. Those values are passed to xFilter using the argc and argv parameters.

After this method completes, the cursor should be left pointing at a row of virtual table. The xColumn and xRowid methods can be used to access that row. The xNext method can be used to advance to the next row.

xNext

  int (*xNext)(sqlite3_vtab_cursor*);

This method advances a virtual table cursor to the next row of a result set initiated by xFilter. If the cursor is already pointing at the last row this routine returns 0 to indicate that no rows remain. If the cursor was successfully advanced, this routine returns 1.

xColumn

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

The SQLite core invokes this method in order to find the value for the N-th column of the current row. N is zero-based so the first column is numbered 0. The xColumn method uses one of the sqlite3_result_*() APIs to return the result. This method can invoke sqlite3_result_error() to raise an exception, if desired.

xRowid

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

This method writes the 64-bit rowid of the current row into *pRowid.

xUpdate

  int (*xUpdate)(
    sqlite3_vtab *pCursor,
    int argc,
    sqlite3_value **argv,
    sqlite_int64 *pRowid
  );

All changes to a virtual table are made using the xUpdate method. This one method can be used to insert, delete, or update.

The argv[0] parameter is the rowid of a row in the virtual table to be deleted. If argv[0] is NULL, then no deletion occurs.

The argv[1] parameter is the rowid of a new row to be inserted into the virtual table. Subsequence argv[] entries contain values of the columns of the virtual table, in the order that the columns were declared. The number of columns will match the table declaration that the xConnect or xCreate method made using the sqlite3_declare_vtab() call.

If argc==1, then no insert occurs.

If argc>1 and argv[0]==argv[1] then the specified row is updated with new values contained in argv[2] and the following parameters.

sqlite - Virtual Table Methods

Page History