xCreate
int (*xCreate)(sqlite3 *db, void *pAux,
int argc, char **argv,
sqlite3_vtab **ppVTab,
char **pzErr);
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 argv parameter is an array of argc pointers to null terminated strings. The first string, argv[0], is the name of the module being invoked. The second, argv[1], is the name of the database in which the new virtual table is being created. The third element of the array, argv[2], is the name of the new virtual table. If present, the fourth and subsquent strings in the argv[] array 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.
If a column datatype contains the special keyword HIDDEN (detected case insensitively) then it is removed from the column datatype and the column marked as a hidden column internally. A hidden column differs from a normal column in three respects:
- It is not listed in the dataset returned by "PRAGMA table_info",
- It is not included in the expansion of a "*" expression in the result set of a SELECT, and
- It is not included in the implicit column-list used by an INSERT statement without an explicity column-list.
For example, if the following SQL is passed to sqlite3_declare_vtab():
CREATE TABLE x(a HIDDEN VARCHAR(12), b INTEGER, c INTEGER Hidden);
then the virtual table would be created with two hidden columns, declaration types "VARCHAR(12)" and "INTEGER".
The xCreate method should return SQLITE_OK if it is successful in creating the new virtual table, or SQLITE_ERROR if it is not successful. If not successful, no sqlite3_vtab structure should be allocated. An error message may optionally be returned in *pzErr if unsuccessful. The text of the error message should be obtained from sqlite3_mprintf().
Compatibility Note: The pzErr parameter was added to this method in the transition from SQLite version 3.3.7 to 3.3.8.
xConnect
int (*xConnect)(sqlite3*, void *pAux,
int argc, char **argv,
sqlite3_vtab **ppVTab,
char **pzErr);
The xConnect method is very similar to xCreate. It has the same parameters and constructs a new sqlite3_vtab structure just like xCreate. 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.
The xConnect method should return SQLITE_OK if it is successful in creating the new virtual table, or SQLITE_ERROR if it is not successful. If not successful, no sqlite3_vtab structure should be allocated. An error message may optionally be returned in *pzErr if unsuccessful. The text of the error message should be obtained from sqlite3_mprintf().
Compatibility Note: The pzErr parameter was added to this method in the transition from SQLite version 3.3.7 to 3.3.8.
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. You should allocate the memory for the sqlite3_vtab_cursor (or a subclass) and return it in *ppCursor. Do not allocate a cursor if you do not return SQLITE_OK.
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.
You should free the cursor which you allocated in xOpen, even if you will return an error. The routine will not be called again if you return an error.
xEof
int (*xEof)(sqlite3_vtab_cursor*);
This method must return false (zero) if the specified cursor currently points to a valid row of data, or true (non-zero) otherwise. It is called by the SQL engine immediately after each xFilter and xNext invocation.
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.
If the filtering constraints configured by idxNum and idxStr do not match any rows of the virtual table (i.e. no rows of data will be returned), then a subsequent call to the xEof method of the same table should return non-zero. Otherwise, xEof should return zero and the cursor should be left pointing at a row of the 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.
This method should return SQLITE_OK if successful, or an sqlite error code if an error occurs.
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 when this routine is called, then the cursor no longer points to valid data and a subsequent call to the xEof method should return non-zero. Otherwise, the xEof method returns zero.
This method should return SQLITE_OK if successful, or an sqlite error code if an error occurs.
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 *pVtab,
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.
argc specifies the number of entries in the argv array. Every argv entry will have a non-NULL value in C (but may contain the SQL value NULL).
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. If argv[1] is NULL, then the implementation must choose a rowid for the newly inserted row. Subsequent 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.
When doing an insert without a rowid (argc>1, argv[1]==NULL), the implementation must set *pRowid to the id of the newly inserted row; this will become the value returned by the last_insert_rowid() function. Setting this value in all the other cases has no effect, and cannot be used to change the rowid of an existing row or if you don't like what was in argv[1].
Each call to xUpdate will fall into one of the following cases:
- argc == 1: the single row argv[0] is deleted; no insert occurs
- argc > 1 && argv[0] == NULL: a new row is inserted with rowid argv[1]
- argc > 1 && argv[0] != NULL && argv[0] == argv[1]: the row with rowid argv[0] is updated with new values in argv[2] and following parameters
- argc > 1 && argv[0] != NULL && argv[0] != argv[1]: the row with rowid argv[0] is updated with rowid argv[1] and new values in argv[2] and following parameters. This will occur when an SQL statement updates a rowid, as in the statement UPDATE table SET rowid=rowid+1 WHERE ...;
It is currently undocumented what effect changes should have on existing cursors.
xFindFunction
int (*xFindFunction)(
sqlite3_vtab *pVtab,
int nArg,
const char *zName,
void (**pxFunc)(sqlite3_context*,int,sqlite3_value**),
void **ppArg
);
This method is called during sqlite3_prepare to give the virtual table implementation an opportunity to overload functions. This method may be set to NULL in which case no overloading occurs.
When a function uses a column from a virtual table as its first argument, this method is called to see if the virtual table would like to overload the function. The first three parameters are inputs: the virtual table, the number of arguments to the function, and the name of the function. If no overloading is desired, return 0. To overload the function, write the new function implementation into *pxFunc and user data into *ppArg and return 1.
Note that infix functions (LIKE, GLOB, REGEXP, and MATCH) reverse the order of their arguments. So "like(A,B)" is equivalent to "B like A". For the form "B like A" the B term is considered the first argument to the function. But for "like(A,B)" the A term is considered the first argument.
xBegin
int (*xBegin)(sqlite3_vtab *pVTab);
Undocumented
xSync
int (*xSync)(sqlite3_vtab *pVTab);
Undocumented
xCommit
int (*xCommit)(sqlite3_vtab *pVTab);
Undocumented
xRollback
int (*xRollback)(sqlite3_vtab *pVTab);
Undocumented
Reader Comments
The documentation for the virtual function methods is above. The remainder of this file is devoted to user feedback and commands.
A Comment On Rowids:
Not all DBMS use and support the concept of integer rowids. The current xRowID implementation would therefore exclude those databases from being accessed as virtual tables from SQLite. Detailed argumentation follows:
- Using a signed 64 bit integer type works fine for SQLite, but might be insufficient for other Virtual Table implementations. Imagine a Virtual Table which maps the files of a disk/directoy. In Windows, for example, a unique ITEMIDLIST can be generated for a file or folder. However, this ITEMIDLIST is not a signed 64 bit integer, so mapping an ITEMIDLIST to the current implementation is not easily possible. I can even imaginge other scenarios where the virtual table implementation must allocate a special memory structure larger than 64 bit to uniquely identify a row/item.
- In case the virtual table implementation needs to allocate memory in order to uniquely describe a row/item, this memory needs to be freed when no longer used. As I see it, there is no such method in the Virtual Table implementation.
Propsals:
- Maintain the current xRowID interface, but make sure that the sqlite_int64 * can be used by the virtual table implementation in any way it likes, meaning that it is not used internally by SQLite. However, this would not allow counts on the virtual table RowIDs.
- Add a new xFreeRowID merhod, which allows the virtual table implementation to deallocate / free memory which was previously allocated in order to properly describe a row/item.
- Optionally choose a more telling name for xRowID and xFreeRowID to imply a behaviour similar but not equal to SQLite, for example xGetBookmark and xFreeBookmark.
- Optionally offer a second set of methods as proposed in C in addition to the existing xRowID. If xRowID is NULL, there is no RowID available for the given Virtual Table and xGetBookmark and xFreeBookmark are being triggered. if xRowID is not NULL, xGetBookmark and xFreeBookmark are never being used. This would allow virtual tables which do not support RowIDs, for which SQLite would return the usual 'No such solumn: rowid'.
I would very much like to see the idea of proposal 4 turn into reality. Even if the above are just suggestions, I feel that the Virtual Table idea is currently limited by the 64 bit RowID. If this limit is relaxed, this great concept could be applied to a far wider range of usages.