-
-Choose any three.
| - - - |
-
-
-
- |
The Virtual Table Mechanism Of SQLite
- - -1.0 Introduction
- -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 and updates on a virtual table -invoke callback methods of 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 almost anything to a -virtual table that they can do to a real table, with the following -exceptions: - -
-
-
-
- One cannot create a trigger on a virtual table. -
- One cannot create additional indices on a virtual table. - (Virtual tables can have indices but that must be built into - the virtual table implementation. Indices cannot be added - separately using CREATE INDEX statements.) -
- One cannot run ALTER TABLE ... ADD COLUMN - commands against a virtual table. -
Individual virtual table implementations might impose additional -constraints. For example, some virtual implementations might provide -read-only tables. Or some virtual table implementations might allow -INSERT or DELETE but not UPDATE. Or some virtual table implementations -might limit the kinds of UPDATEs that can be made. - -
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. - -
Here are some existing and postulated uses for virtual tables: - -
-
-
- A full-text search interface -
- Spatial indices using R-Trees -
- Introspect the disk content of an SQLite database file - (the dbstat virtual table) -
- Read and/or write the content of a comma-separated value (CSV) - file -
- Access the filesystem of the host computer as if it were a database table -
- Enabling SQL manipulation of data in statistics packages like R -
1.1 Usage
- -A virtual table is created using a CREATE VIRTUAL TABLE statement. - -
--- - --
The CREATE VIRTUAL TABLE statement creates a new table -called table-name derived from the class -class module-name. The module-name -is the name that is registered for the virtual table by -the sqlite3_create_module() interface. - -
- -- CREATE VIRTUAL TABLE tablename USING modulename; -
One can also provide comma-separated arguments to the module following -the module name: - -
- -- CREATE VIRTUAL TABLE tablename USING modulename(arg1, arg2, ...); -
The format of the arguments to the module is very general. Each -module-argument -may contain keywords, string literals, identifiers, numbers, and -punctuation. Each module-argument is passed as -written (as text) into the -constructor method of the virtual table implementation -when the virtual -table is created and that constructor is responsible for parsing and -interpreting the arguments. The argument syntax is sufficiently general -that a virtual table implementation can, if it wants to, interpret its -arguments as column definitions in an ordinary CREATE TABLE statement. -The implementation could also impose some other interpretation on the -arguments. - -
Once a virtual table has been created, it can be used like any other -table with the exceptions noted above and imposed by specific virtual -table implementations. A virtual table is destroyed using the ordinary -DROP TABLE syntax. - -
1.1.1 Temporary virtual tables
- -There is no "CREATE TEMP VIRTUAL TABLE" statement. To create a
-temporary virtual table, add the "temp" schema
-before the virtual table name.
-
- Some virtual tables exist automatically in the "main" schema of
-every database connection in which their
-module is registered, even without a CREATE VIRTUAL TABLE statement.
-Such virtual tables are called "eponymous virtual tables".
-To use an eponymous virtual table, simple use the
-module name as if it were a table.
-Eponymous virtual tables exist in the "main" schema only, so they will
-not work if prefixed with a different schema name.
-
- An example of an eponymous virtual table is the dbstat virtual table.
-To use the dbstat virtual table as an eponymous virtual table,
-simply query against the "dbstat"
-module name, as if it were an ordinary table. (Note that SQLite
-must be compiled with the SQLITE_ENABLE_DBSTAT_VTAB option to include
-the dbstat virtual table in the build.)
-
- A virtual table is eponymous if its xCreate method is the exact same
-function as the xConnect method, or if the xCreate method is NULL.
-The xCreate method is called when a virtual table is first created
-using the CREATE VIRTUAL TABLE statement. The xConnect method whenever
-a database connection attaches to or reparses a schema. When these two methods
-are the same, that indicates that the virtual table has no persistent
-state that needs to be created and destroyed.
-
-
-
- If the xCreate method is NULL, then
-CREATE VIRTUAL TABLE statements are prohibited for that virtual table,
-and the virtual table is an "eponymous-only virtual table".
-Eponymous-only virtual tables are useful as
-table-valued functions.
-
-
-Note that SQLite versions prior to 3.9.0 did not check the xCreate method
-for NULL before invoking it. So if an eponymous-only virtual table is
-registered with SQLite version 3.8.11.1 or earlier and a CREATE VIRTUAL TABLE
-command is attempted against that virtual table module, a jump to a NULL
-pointer will occur, resulting in a crash.
-
- Several new C-level objects are used by the virtual table implementation:
-
- The sqlite3_module structure defines a module object used to implement
-a virtual table. Think of a module as a class from which one can
-construct multiple virtual tables having similar properties. For example,
-one might have a module that provides read-only access to
-comma-separated-value (CSV) files on disk. That one module can then be
-used to create several virtual tables where each virtual table refers
-to a different CSV file.
-
- The module structure contains methods that are invoked by SQLite to
-perform various actions on the virtual table such as creating new
-instances of a virtual table or destroying old ones, reading and
-writing data, searching for and deleting, updating, or inserting rows.
-The module structure is explained in more detail below.
-
- Each virtual table instance is represented by an sqlite3_vtab structure.
-The sqlite3_vtab structure looks like this:
-
- Virtual table implementations will normally subclass this structure
-to add additional private and implementation-specific fields.
-The nRef field is used internally by the SQLite core and should not
-be altered by the virtual table implementation. The virtual table
-implementation may pass error message text to the core by putting
-an error message string in zErrMsg.
-Space to hold this error message string must be obtained from an
-SQLite memory allocation function such as sqlite3_mprintf() or
-sqlite3_malloc().
-Prior to assigning a new value to zErrMsg, the virtual table
-implementation must free any preexisting content of zErrMsg using
-sqlite3_free(). Failure to do this will result in a memory leak.
-The SQLite core will free and zero the content of zErrMsg when it
-delivers the error message text to the client application or when
-it destroys the virtual table. The virtual table implementation only
-needs to worry about freeing the zErrMsg content when it overwrites
-the content with a new, different error message.
-
- The sqlite3_vtab_cursor structure represents a pointer to a specific
-row of a virtual table. This is what an sqlite3_vtab_cursor looks like:
-
- Once again, practical implementations will likely subclass this
-structure to add additional private fields.
-
- The sqlite3_index_info structure is used to pass information into
-and out of the xBestIndex method of the module that implements a
-virtual table.
-
- Before a CREATE VIRTUAL TABLE statement can be run, the module
-specified in that statement must be registered with the database
-connection. This is accomplished using either of the sqlite3_create_module()
-or sqlite3_create_module_v2() interfaces:
-
- The sqlite3_create_module() and sqlite3_create_module_v2()
-routines associates a module name with
-an sqlite3_module structure and a separate client data that is specific
-to each module. The only difference between the two create_module methods
-is that the _v2 method includes an extra parameter that specifies a
-destructor for client data pointer. The module structure is what defines
-the behavior of a virtual table. The module structure looks like this:
-
- The module structure defines all of the methods for each virtual
-table object. The module structure also contains the iVersion field which
-defines the particular edition of the module table structure. Currently,
-iVersion is always 1, but in future releases of SQLite the module structure
-definition might be extended with additional methods and in that case
-the iVersion value will be increased.
-
- The rest of the module structure consists of methods used to implement
-various features of the virtual table. Details on what each of these
-methods do are provided in the sequel.
-
- Prior to SQLite version 3.6.17, the virtual table mechanism assumes
-that each database connection kept
-its own copy of the database schema. Hence, the virtual table mechanism
-could not be used in a database that has shared cache mode enabled.
-The sqlite3_create_module() interface would return an error if
-shared cache mode is enabled. That restriction was relaxed
-beginning with SQLite version 3.6.17.
-
- Follow these steps to create your own virtual table:
-
-
- The only really hard part is step 1. You might want to start with an
-existing virtual table implementation and modify it to suit your needs.
-There are several virtual table implementations in the SQLite source tree
-(for testing purposes). You might use one of those as a guide. Locate
-these test virtual table implementations by searching
-for "sqlite3_create_module".
-
- You might also want to implement your new virtual table as a
-loadable extension.
-
- 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() or
-sqlite3_create_module_v2() call that registered the
-virtual table 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
-module name is the name provided as the second argument to
-sqlite3_create_module() and as the argument to the USING clause of the
-CREATE VIRTUAL TABLE statement that is running.
-The second, argv[1], is the name of the database in which the new virtual table is being created. The database name is "main" for the primary database, or
-"temp" for TEMP database, or the name given at the end of the ATTACH
-statement for attached databases. The third element of the array, argv[2],
-is the name of the new virtual table, as specified following the TABLE
-keyword in the CREATE VIRTUAL TABLE statement.
-If present, the fourth and subsequent 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 object
-(an sqlite3_vtab object) 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:
-
- The first argument to sqlite3_declare_vtab() must be the same
-database connection pointer as the first parameter to this method.
-The second argument to sqlite3_declare_vtab() must 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.
-The CREATE TABLE statement string need not to be
-held in persistent memory. The string can be
-deallocated and/or reused as soon as the sqlite3_declare_vtab()
-routine returns.
-
- The xCreate method need not initialize the pModule, nRef, and zErrMsg
-fields of the sqlite3_vtab object. The SQLite core will take care of
-that chore.
-
- The xCreate 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, the sqlite3_vtab structure must not be allocated.
-An error message may optionally be returned in *pzErr if unsuccessful.
-Space to hold the error message string must be allocated using
-an SQLite memory allocation function like
-sqlite3_malloc() or sqlite3_mprintf() as the SQLite core will
-attempt to free the space using sqlite3_free() after the error has
-been reported up to the application.
-
-
-If the xCreate method is omitted (left as a NULL pointer) then the
-virtual table is an eponymous-only virtual table. New instances of
-the virtual table cannot be created using CREATE VIRTUAL TABLE and the
-virtual table can only be used via its module name.
-Note that SQLite versions prior to 3.9.0 do not understand
-eponymous-only virtual tables and will segfault if an attempt is made
-to CREATE VIRTUAL TABLE on an eponymous-only virtual table because
-the xCreate method was not checked for null.
-
-
-If the xCreate method is the exact same pointer as the xConnect method,
-that indicates that the virtual table does not need to initialize backing
-store. Such a virtual table can be used as an eponymous virtual table
-or as a named virtual table using CREATE VIRTUAL TABLE or both.
-
-
-
- If a column datatype contains the special keyword "HIDDEN"
-(in any combination of upper and lower case letters) then that keyword
-it is omitted from the column datatype name and the column is marked
-as a hidden column internally.
-A hidden column differs from a normal column in three respects:
-
-
- For example, if the following SQL is passed to sqlite3_declare_vtab():
-
- Then the virtual table would be created with two hidden columns,
-and with datatypes of "VARCHAR(12)" and "INTEGER".
-
- An example use of hidden columns can be seen in the FTS3 virtual
-table implementation, where every FTS virtual table
-contains an FTS hidden column that is used to pass information from the
-virtual table into FTS auxiliary functions and to the FTS MATCH operator.
-
-
-
- A virtual table that contains hidden columns can be used like
-a table-valued function in the FROM clause of a SELECT statement.
-The arguments to the table-valued function become constraints on
-the HIDDEN columns of the virtual table.
-
- For example, the "generate_series" extension (located in the
-ext/misc/series.c
-file in the source tree)
-implements an eponymous virtual table with the following schema:
-
- The sqlite3_module.xBestIndex method in the implementation of this
-table checks for equality constraints against the HIDDEN columns, and uses
-those as input parameters to determine the range of integer "value" outputs
-to generate. Reasonable defaults are used for any unconstrained columns.
-For example, to list all integers between 5 and 50:
-
- The previous query is equivalent to the following:
-
- Arguments on the virtual table name are matched to hidden columns
-in order. The number of arguments can be less than the
-number of hidden columns, in which case the latter hidden columns are
-unconstrained. However, an error results if there are more arguments
-than there are hidden columns in the virtual table.
-
-
-
-
- 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. When xCreate and xConnect are the same,
-the table is an eponymous virtual table.
-
- 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 has to locate and use an existing dictionary
-and posting lists that were created by a prior xCreate call.
-
- The xConnect method must return SQLITE_OK if it is successful
-in creating the new virtual table, or SQLITE_ERROR if it is not
-successful. If not successful, the sqlite3_vtab structure must not be
-allocated. An error message may optionally be returned in *pzErr if
-unsuccessful.
-Space to hold the error message string must be allocated using
-an SQLite memory allocation function like
-sqlite3_malloc() or sqlite3_mprintf() as the SQLite core will
-attempt to free the space using sqlite3_free() after the error has
-been reported up to the application.
-
- The xConnect method is required for every virtual table implementation,
-though the xCreate and xConnect pointers of the sqlite3_module object
-may point to the same function if the virtual table does not need to
-initialize backing store.
-
-
-
- SQLite uses the xBestIndex method of a virtual table module to determine
-the best way to access the virtual table.
-The xBestIndex method has a prototype like this:
-
- The SQLite core communicates with the xBestIndex method by filling
-in certain fields of the sqlite3_index_info structure and passing a
-pointer to that structure into xBestIndex as the second parameter.
-The xBestIndex method fills out other fields of this structure which
-forms the reply. The sqlite3_index_info structure looks like this:
-
- Please note the warnings on the "estimatedRows" and "idxFlags" field.
-These fields were added with SQLite versions 3.8.2 and 3.9.0, respectively.
-Any extension that reads or writes these fields must first check that the
-version of the SQLite library in use is greater than or equal to 3.8.2 or
-3.9.0 - perhaps using a call to sqlite3_version(). The result of attempting
-to access these fields in an sqlite3_index_info structure created by an
-older version of SQLite are undefined.
-
- In addition, there are some defined constants:
-
- The SQLite core calls the xBestIndex method when it is compiling a query
-that involves a virtual table. In other words, SQLite calls this method
-when it is running sqlite3_prepare() or the equivalent.
-By calling this method, the
-SQLite core is saying to the virtual table that it needs to access
-some subset of the rows in the virtual table and it wants to know the
-most efficient way to do that access. The xBestIndex method replies
-with information that the SQLite core can then use to conduct an
-efficient search of the virtual table.
-
- While compiling a single SQL query, the SQLite core might call
-xBestIndex multiple times with different settings in sqlite3_index_info.
-The SQLite core will then select the combination that appears to
-give the best performance.
-
- Before calling this method, the SQLite core initializes an instance
-of the sqlite3_index_info structure with information about the
-query that it is currently trying to process. This information
-derives mainly from the WHERE clause and ORDER BY or GROUP BY clauses
-of the query, but also from any ON or USING clauses if the query is a
-join. The information that the SQLite core provides to the xBestIndex
-method is held in the part of the structure that is marked as "Inputs".
-The "Outputs" section is initialized to zero.
-
- The information in the sqlite3_index_info structure is ephemeral
-and may be overwritten or deallocated as soon as the xBestIndex method
-returns. If the xBestIndex method needs to remember any part of the
-sqlite3_index_info structure, it should make a copy. Care must be
-take to store the copy in a place where it will be deallocated, such
-as in the idxStr field with needToFreeIdxStr set to 1.
-
- Note that xBestIndex will always be called before xFilter, since
-the idxNum and idxStr outputs from xBestIndex are required inputs to
-xFilter. However, there is no guarantee that xFilter will be called
-following a successful xBestIndex.
-
- The xBestIndex method is required for every virtual table implementation.
-
- The main thing that the SQLite core is trying to communicate to
-the virtual table is the constraints that are available to limit
-the number of rows that need to be searched. The aConstraint[] array
-contains one entry for each constraint. There will be exactly
-nConstraint entries in that array.
-
- Each constraint will correspond to a term in the WHERE clause
-or in a USING or ON clause that is of the form
-
- Where "column" is a column in the virtual table, OP is an operator
-like "=" or "<", and EXPR is an arbitrary expression. So, for example,
-if the WHERE clause contained a term like this:
-
- Then one of the constraints would be on the "a" column with
-operator "=" and an expression of "5". Constraints need not have a
-literal representation of the WHERE clause. The query optimizer might
-make transformations to the
-WHERE clause in order to extract as many constraints
-as it can. So, for example, if the WHERE clause contained something
-like this:
-
- The query optimizer might translate this into three separate constraints:
-
- For each constraint, the aConstraint[].iColumn field indicates which
-column appears on the left-hand side of the constraint.
-The first column of the virtual table is column 0.
-The rowid of the virtual table is column -1.
-The aConstraint[].op field indicates which operator is used.
-The SQLITE_INDEX_CONSTRAINT_* constants map integer constants
-into operator values.
-Columns occur in the order they were defined by the call to
-sqlite3_declare_vtab() in the xCreate or xConnect method.
-Hidden columns are counted when determining the column index.
-
- The aConstraint[] array contains information about all constraints
-that apply to the virtual table. But some of the constraints might
-not be usable because of the way tables are ordered in a join.
-The xBestIndex method must therefore only consider constraints
-that have an aConstraint[].usable flag which is true.
-
- In addition to WHERE clause constraints, the SQLite core also
-tells the xBestIndex method about the ORDER BY clause.
-(In an aggregate query, the SQLite core might put in GROUP BY clause
-information in place of the ORDER BY clause information, but this fact
-should not make any difference to the xBestIndex method.)
-If all terms of the ORDER BY clause are columns in the virtual table,
-then nOrderBy will be the number of terms in the ORDER BY clause
-and the aOrderBy[] array will identify the column for each term
-in the order by clause and whether or not that column is ASC or DESC.
-
- Given all of the information above, the job of the xBestIndex
-method it to figure out the best way to search the virtual table.
-
- The xBestIndex method fills the idxNum and idxStr fields with
-information that communicates an indexing strategy to the xFilter
-method. The information in idxNum and idxStr is arbitrary as far
-as the SQLite core is concerned. The SQLite core just copies the
-information through to the xFilter method. Any desired meaning can
-be assigned to idxNum and idxStr as long as xBestIndex and xFilter
-agree on what that meaning is.
-
- The idxStr value may be a string obtained from an SQLite
-memory allocation function such as sqlite3_mprintf().
-If this is the case, then the needToFreeIdxStr flag must be set to
-true so that the SQLite core will know to call sqlite3_free() on
-that string when it has finished with it, and thus avoid a memory leak.
-
- If the virtual table will output rows in the order specified by
-the ORDER BY clause, then the orderByConsumed flag may be set to
-true. If the output is not automatically in the correct order
-then orderByConsumed must be left in its default false setting.
-This will indicate to the SQLite core that it will need to do a
-separate sorting pass over the data after it comes out of the virtual table.
-
- The estimatedCost field should be set to the estimated number
-of disk access operations required to execute this query against
-the virtual table. The SQLite core will often call xBestIndex
-multiple times with different constraints, obtain multiple cost
-estimates, then choose the query plan that gives the lowest estimate.
-
- If the current version of SQLite is 3.8.2 or greater, the estimatedRows
-field may be set to an estimate of the number of rows returned by the
-proposed query plan. If this value is not explicitly set, the default
-estimate of 25 rows is used.
-
- If the current version of SQLite is 3.9.0 or greater, the idxFlags field
-may be set to SQLITE_INDEX_SCAN_UNIQUE to indicate that the virtual table
-will return only zero or one rows given the input constraints. Additional
-bits of the idxFlags field might be understood in later versions of SQLite.
-
- The aConstraintUsage[] array contains one element for each of
-the nConstraint constraints in the inputs section of the
-sqlite3_index_info structure.
-The aConstraintUsage[] array is used by xBestIndex to tell the
-core how it is using the constraints.
-
- The xBestIndex method may set aConstraintUsage[].argvIndex
-entries to values greater than zero.
-Exactly one entry should be set to 1, another to 2, another to 3,
-and so forth up to as many or as few as the xBestIndex method wants.
-The EXPR of the corresponding constraints will then be passed
-in as the argv[] parameters to xFilter.
-
- For example, if the aConstraint[3].argvIndex is set to 1, then
-when xFilter is called, the argv[0] passed to xFilter will have
-the EXPR value of the aConstraint[3] constraint.
-
- By default, the SQLite core double checks all constraints on
-each row of the virtual table that it receives. If such a check
-is redundant, the xBestFilter method can suppress that double-check by
-setting aConstraintUsage[].omit.
-
-
-
- This method releases a connection to a virtual table.
-Only the sqlite3_vtab object is destroyed.
-The virtual table is not destroyed and any backing store
-associated with the virtual table persists.
-
-This method undoes the work of xConnect.
-
- This method is a destructor for a connection to the virtual table.
-Contrast this method with xDestroy. The xDestroy is a destructor
-for the entire virtual table.
-
- The xDisconnect method is required for every virtual table implementation,
-though it is acceptable for the xDisconnect and xDestroy methods to be
-the same function if that makes sense for the particular virtual table.
-
-
-
- This method releases a connection to a virtual table, just like
-the xDisconnect method, and it also destroys the underlying
-table implementation. This method undoes the work 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.
-
- The xDestroy method is required for every virtual table implementation,
-though it is acceptable for the xDisconnect and xDestroy methods to be
-the same function if that makes sense for the particular virtual table.
-
-
-
- The xOpen method creates a new cursor used for accessing (read and/or
-writing) a virtual table. A successful invocation of this method
-will allocate the memory for the sqlite3_vtab_cursor (or a subclass),
-initialize the new object, and make *ppCursor point to the new object.
-The successful call then returns SQLITE_OK.
-
- For every successful call to this method, the SQLite core will
-later invoke the xClose method to destroy
-the allocated cursor.
-
- The xOpen method need not initialize the pVtab field of the
-sqlite3_vtab_cursor structure. The SQLite core will take care
-of that chore automatically.
-
- A virtual table implementation must be able to support an arbitrary
-number of simultaneously open cursors.
-
- When initially opened, the cursor is in an undefined state.
-The SQLite core will invoke the xFilter method
-on the cursor prior to any attempt to position or read from the cursor.
-
- The xOpen method is required for every virtual table implementation.
-
-
-
- The xClose method closes a cursor previously opened by
-xOpen.
-The SQLite core will always call xClose once for each cursor opened
-using xOpen.
-
- This method must release all resources allocated by the
-corresponding xOpen call. The routine will not be called again even if it
-returns an error. The SQLite core will not use the
-sqlite3_vtab_cursor again after it has been closed.
-
- The xClose method is required for every virtual table implementation.
-
-
-
- The xEof method must return false (zero) if the specified cursor
-currently points to a valid row of data, or true (non-zero) otherwise.
-This method is called by the SQL engine immediately after each
-xFilter and xNext invocation.
-
- The xEof method is required for every virtual table implementation.
-
-
-
- This method begins a search of a virtual table.
-The first argument is a cursor opened by xOpen.
-The next two arguments define a particular search index previously
-chosen 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 virtual table contains one or more rows that match the
-search criteria, then the cursor must be left point at the first row.
-Subsequent calls to xEof must return false (zero).
-If there are no rows match, then the cursor must be left in a state
-that will cause the xEof to return true (non-zero).
-The SQLite engine will use
-the xColumn and xRowid methods to access that row content.
-The xNext method will be used to advance to the next row.
-
- This method must return SQLITE_OK if successful, or an sqlite
-error code if an error occurs.
-
- The xFilter method is required for every virtual table implementation.
-
-
-
- The xNext 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 must return true (non-zero).
-If the cursor is successfully advanced to another row of content, then
-subsequent calls to xEof must return false (zero).
-
- This method must return SQLITE_OK if successful, or an sqlite
-error code if an error occurs.
-
- The xNext method is required for every virtual table implementation.
-
-
-
- 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 may return its result back to SQLite using one of the
-following interface:
-
-
-
- CREATE VIRTUAL TABLE temp.tablename USING module(arg1, ...);
-
-
-
-
-1.1.2 Eponymous virtual tables
-
-
-
-
- SELECT * FROM dbstat;
-
1.1.2.1 Eponymous-only virtual tables
-1.2 Implementation
-
-
-
-
- typedef struct sqlite3_vtab sqlite3_vtab;
- typedef struct sqlite3_index_info sqlite3_index_info;
- typedef struct sqlite3_vtab_cursor sqlite3_vtab_cursor;
- typedef struct sqlite3_module sqlite3_module;
-
-
-
- struct sqlite3_vtab {
- const sqlite3_module *pModule;
- int nRef;
- char *zErrMsg;
- };
-
-
-
- struct sqlite3_vtab_cursor {
- sqlite3_vtab *pVtab;
- };
-
-
-
- int sqlite3_create_module(
- sqlite3 *db, /* SQLite connection to register module with */
- const char *zName, /* Name of the module */
- const sqlite3_module *, /* Methods for the module */
- void * /* Client data for xCreate/xConnect */
- );
- int sqlite3_create_module_v2(
- sqlite3 *db, /* SQLite connection to register module with */
- const char *zName, /* Name of the module */
- const sqlite3_module *, /* Methods for the module */
- void *, /* Client data for xCreate/xConnect */
- void(*xDestroy)(void*) /* Client data destructor function */
- );
-
-
-
- struct sqlite3_module {
- int iVersion;
- int (*xCreate)(sqlite3*, void *pAux,
- int argc, char **argv,
- sqlite3_vtab **ppVTab,
- char **pzErr);
- int (*xConnect)(sqlite3*, void *pAux,
- int argc, char **argv,
- sqlite3_vtab **ppVTab,
- char **pzErr);
- int (*xBestIndex)(sqlite3_vtab *pVTab, sqlite3_index_info*);
- int (*xDisconnect)(sqlite3_vtab *pVTab);
- int (*xDestroy)(sqlite3_vtab *pVTab);
- int (*xOpen)(sqlite3_vtab *pVTab, sqlite3_vtab_cursor **ppCursor);
- int (*xClose)(sqlite3_vtab_cursor*);
- int (*xFilter)(sqlite3_vtab_cursor*, int idxNum, const char *idxStr,
- int argc, sqlite3_value **argv);
- int (*xNext)(sqlite3_vtab_cursor*);
- int (*xEof)(sqlite3_vtab_cursor*);
- int (*xColumn)(sqlite3_vtab_cursor*, sqlite3_context*, int);
- int (*xRowid)(sqlite3_vtab_cursor*, sqlite_int64 *pRowid);
- int (*xUpdate)(sqlite3_vtab *, int, sqlite3_value **, sqlite_int64 *);
- int (*xBegin)(sqlite3_vtab *pVTab);
- int (*xSync)(sqlite3_vtab *pVTab);
- int (*xCommit)(sqlite3_vtab *pVTab);
- int (*xRollback)(sqlite3_vtab *pVTab);
- int (*xFindFunction)(sqlite3_vtab *pVtab, int nArg, const char *zName,
- void (**pxFunc)(sqlite3_context*,int,sqlite3_value**),
- void **ppArg);
- int (*Rename)(sqlite3_vtab *pVtab, const char *zNew);
- /* The methods above are in version 1 of the sqlite_module object. Those
- ** below are for version 2 and greater. */
- int (*xSavepoint)(sqlite3_vtab *pVTab, int);
- int (*xRelease)(sqlite3_vtab *pVTab, int);
- int (*xRollbackTo)(sqlite3_vtab *pVTab, int);
- };
-1.3 Virtual Tables And Shared Cache
-
-1.4 Creating New Virtual Table Implementations
-
-
-
-
-2.0 Virtual Table Methods
-
-
-
-2.1 The xCreate Method
-
-
-
-
- int (*xCreate)(sqlite3 *db, void *pAux,
- int argc, char **argv,
- sqlite3_vtab **ppVTab,
- char **pzErr);
-
-
-
- int sqlite3_declare_vtab(sqlite3 *db, const char *zCreateTable)
-
2.1.1 Hidden columns in virtual tables
-
-
-
-
-
-
- CREATE TABLE x(a HIDDEN VARCHAR(12), b INTEGER, c INTEGER Hidden);
-
2.1.2 Table-valued functions
-
-
-
-
-CREATE TABLE generate_series(
- value,
- start HIDDEN,
- stop HIDDEN,
- step HIDDEN
-);
-
-
-
-SELECT value FROM generate_series(5,50);
-
-
-
-SELECT value FROM generate_series WHERE start=5 AND stop=50;
-
2.2 The xConnect Method
-
-
-
-
- int (*xConnect)(sqlite3*, void *pAux,
- int argc, char **argv,
- sqlite3_vtab **ppVTab,
- char **pzErr);
-
2.3 The xBestIndex Method
-
-
-
-
- int (*xBestIndex)(sqlite3_vtab *pVTab, sqlite3_index_info*);
-
-
-
- struct sqlite3_index_info {
- /* Inputs */
- const int nConstraint; /* Number of entries in aConstraint */
- const struct sqlite3_index_constraint {
- int iColumn; /* Column on left-hand side of constraint */
- unsigned char op; /* Constraint operator */
- unsigned char usable; /* True if this constraint is usable */
- int iTermOffset; /* Used internally - xBestIndex should ignore */
- } *const aConstraint; /* Table of WHERE clause constraints */
- const int nOrderBy; /* Number of terms in the ORDER BY clause */
- const struct sqlite3_index_orderby {
- int iColumn; /* Column number */
- unsigned char desc; /* True for DESC. False for ASC. */
- } *const aOrderBy; /* The ORDER BY clause */
-
- /* Outputs */
- struct sqlite3_index_constraint_usage {
- int argvIndex; /* if >0, constraint is part of argv to xFilter */
- unsigned char omit; /* Do not code a test for this constraint */
- } *const aConstraintUsage;
- int idxNum; /* Number used to identify the index */
- char *idxStr; /* String, possibly obtained from sqlite3_malloc */
- int needToFreeIdxStr; /* Free idxStr using sqlite3_free() if true */
- int orderByConsumed; /* True if output is already ordered */
- double estimatedCost; /* Estimated cost of using this index */
- /* Fields below are only available in SQLite 3.8.2 and later */
- sqlite3_int64 estimatedRows; /* Estimated number of rows returned */
- /* Fields below are only available in SQLite 3.9.0 and later */
- int idxFlags; /* Mask of SQLITE_INDEX_SCAN_* flags */
- };
-
-
-
- #define SQLITE_INDEX_CONSTRAINT_EQ 2
- #define SQLITE_INDEX_CONSTRAINT_GT 4
- #define SQLITE_INDEX_CONSTRAINT_LE 8
- #define SQLITE_INDEX_CONSTRAINT_LT 16
- #define SQLITE_INDEX_CONSTRAINT_GE 32
- #define SQLITE_INDEX_CONSTRAINT_MATCH 64
- #define SQLITE_INDEX_SCAN_UNIQUE 1 /* Scan visits at most 1 row */
-
2.3.1 Inputs
-
-
- column OP EXPR
-
-
-
-
-
- a = 5
-
-
-
- x BETWEEN 10 AND 100 AND 999>y
-
-
-
- x >= 10
- x <= 100
- y < 999
-
2.3.2 Outputs
-
-2.4 The xDisconnect Method
-
-
-
-
- int (*xDisconnect)(sqlite3_vtab *pVTab);
-
2.5 The xDestroy Method
-
-
-
-
- int (*xDestroy)(sqlite3_vtab *pVTab);
-
2.6 The xOpen Method
-
-
-
-
- int (*xOpen)(sqlite3_vtab *pVTab, sqlite3_vtab_cursor **ppCursor);
-
2.7 The xClose Method
-
-
-
-
- int (*xClose)(sqlite3_vtab_cursor*);
-
2.8 The xEof Method
-
-
-
-
- int (*xEof)(sqlite3_vtab_cursor*);
-
2.9 The xFilter Method
-
-
-
-
- int (*xFilter)(sqlite3_vtab_cursor*, int idxNum, const char *idxStr,
- int argc, sqlite3_value **argv);
-
2.10 The xNext Method
-
-
-
-
- int (*xNext)(sqlite3_vtab_cursor*);
-
2.11 The xColumn Method
-
-
-
-
- int (*xColumn)(sqlite3_vtab_cursor*, sqlite3_context*, int N);
-
If the xColumn method implementation calls none of the functions above, -then the value of the column defaults to an SQL NULL. - -
To raise an error, the xColumn method should use one of the result_text() -methods to set the error message text, then return an appropriate -error code. The xColumn method must return SQLITE_OK on success. - -
The xColumn method is required for every virtual table implementation. - - - -
2.12 The xRowid Method
- -- -- int (*xRowid)(sqlite3_vtab_cursor *pCur, sqlite_int64 *pRowid); -
A successful invocation of this method will cause *pRowid to be -filled with the rowid of row that the -virtual table cursor pCur is currently pointing at. -This method returns SQLITE_OK on success. -It returns an appropriate error code on failure.
- -The xRowid method is required for every virtual table implementation. - - - -
2.13 The xUpdate Method
- -- -- 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. - -
The argc parameter specifies the number of entries in the argv array. -The value of argc will be 1 for a pure delete operation or N+2 for an insert -or replace or update where N is the number of columns in the table. -In the previous sentence, N includes any hidden columns. - -
Every argv entry will have a non-NULL value in C but may contain the -SQL value NULL. In other words, it is always true that -argv[i]!=0 for i between 0 and argc-1. -However, it might be the case that -sqlite3_value_type(argv[i])==SQLITE_NULL. - -
The argv[0] parameter is the rowid of a row in the virtual table -to be deleted. If argv[0] is an SQL 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 an SQL 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. All hidden columns are included. - -
When doing an insert without a rowid (argc>1, argv[1] is an SQL NULL), the -implementation must set *pRowid to the rowid of the newly inserted row; -this will become the value returned by the sqlite3_last_insert_rowid() -function. Setting this value in all the other cases is a harmless no-op; -the SQLite engine ignores the *pRowid return value if argc==1 or -argv[1] is not an SQL NULL. - -
Each call to xUpdate will fall into one of cases shown below. -Not that references to argv[i] mean the SQL value -held within the argv[i] object, not the argv[i] -object itself. - -
-- --
-- argc = 1 -
The single row with rowid equal to argv[0] is deleted. No insert occurs. - -
- argc > 1
argv[0] = NULL -A new row is inserted with a rowid argv[1] and column values in - argv[2] and following. If argv[1] is an SQL NULL, - the a new unique rowid is generated automatically. - -
- 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 ...; --
The xUpdate method must return SQLITE_OK if and only if it is -successful. If a failure occurs, the xUpdate must return an appropriate -error code. On a failure, the pVTab->zErrMsg element may optionally -be replaced with error message text stored in memory allocated from SQLite -using functions such as sqlite3_mprintf() or sqlite3_malloc(). - -
If the xUpdate method violates some constraint of the virtual table -(including, but not limited to, attempting to store a value of the wrong -datatype, attempting to store a value that is too -large or too small, or attempting to change a read-only value) then the -xUpdate must fail with an appropriate error code. - -
There might be one or more sqlite3_vtab_cursor objects open and in use -on the virtual table instance and perhaps even on the row of the virtual -table when the xUpdate method is invoked. The implementation of -xUpdate must be prepared for attempts to delete or modify rows of the table -out from other existing cursors. If the virtual table cannot accommodate -such changes, the xUpdate method must return an error code. - -
The xUpdate method is optional. -If the xUpdate pointer in the sqlite3_module for a virtual table -is a NULL pointer, then the virtual table is read-only. - - - - -
2.14 The xFindFunction Method
- -- -- 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, this method -returns 0. To overload the function, this method writes the new -function implementation into *pxFunc and writes user data into *ppArg -and returns 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. - -
The function pointer returned by this routine must be valid for -the lifetime of the sqlite3_vtab object given in the first parameter. - - - -
2.15 The xBegin Method
- -- -- int (*xBegin)(sqlite3_vtab *pVTab); -
This method begins a transaction on a virtual table. -This is method is optional. The xBegin pointer of sqlite3_module -may be NULL. - -
This method is always followed by one call to either the -xCommit or xRollback method. Virtual table transactions do -not nest, so the xBegin method will not be invoked more than once -on a single virtual table -without an intervening call to either xCommit or xRollback. -Multiple calls to other methods can and likely will occur in between -the xBegin and the corresponding xCommit or xRollback. - - - -
2.16 The xSync Method
- -- - -- int (*xSync)(sqlite3_vtab *pVTab); -
This method signals the start of a two-phase commit on a virtual -table. -This is method is optional. The xSync pointer of sqlite3_module -may be NULL. - -
This method is only invoked after call to the xBegin method and -prior to an xCommit or xRollback. In order to implement two-phase -commit, the xSync method on all virtual tables is invoked prior to -invoking the xCommit method on any virtual table. If any of the -xSync methods fail, the entire transaction is rolled back. - - - -
2.17 The xCommit Method
- -- -- int (*xCommit)(sqlite3_vtab *pVTab); -
This method causes a virtual table transaction to commit. -This is method is optional. The xCommit pointer of sqlite3_module -may be NULL. - -
A call to this method always follows a prior call to xBegin and -xSync. - - - - -
2.18 The xRollback Method
- -- -- int (*xRollback)(sqlite3_vtab *pVTab); -
This method causes a virtual table transaction to rollback. -This is method is optional. The xRollback pointer of sqlite3_module -may be NULL. - -
A call to this method always follows a prior call to xBegin. - - - - -
2.19 The xRename Method
- -- -- int (*xRename)(sqlite3_vtab *pVtab, const char *zNew); -
This method provides notification that the virtual table implementation -that the virtual table will be given a new name. -If this method returns SQLITE_OK then SQLite renames the table. -If this method returns an error code then the renaming is prevented. - -
The xRename method is required for every virtual table implementation. - - - -
2.20 The xSavepoint, xRelease, and xRollbackTo Methods
- -- -- int (*xSavepoint)(sqlite3_vtab *pVtab, int); - int (*xRelease)(sqlite3_vtab *pVtab, int); - int (*xRollbackTo)(sqlite3_vtab *pVtab, int); -
-These methods provide the virtual table implementation an opportunity to -implement nested transactions. They are always optional and will only be -called in SQLite version 3.7.7 and later. -
- --When xSavepoint(X,N) is invoked, that is a signal to the virtual table X -that it should save its current state as savepoint N. -A subsequent call -to xRollbackTo(X,R) means that the state of the virtual table should return -to what it was when xSavepoint(X,R) was last called. -The call -to xRollbackTo(X,R) will invalidate all savepoints with N>R; none of the -invalided savepoints will be rolled back or released without first -being reinitialized by a call to xSavepoint(). -A call to xRelease(X,M) invalidates all savepoints where N>=M. -
- --None of the xSavepoint(), xRelease(), or xRollbackTo() methods will ever -be called except in between calls to xBegin() and -either xCommit() or xRollback(). -
- ADDED Doc/Special/Core/vtab.html Index: Doc/Special/Core/vtab.html ================================================================== --- /dev/null +++ Doc/Special/Core/vtab.html @@ -0,0 +1,1421 @@ + + + +
+
+Choose any three.
| + + + |
+
+
+
+ |
The Virtual Table Mechanism Of SQLite
+ + +1.0 Introduction
+ +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 and updates on a virtual table +invoke callback methods of 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 almost anything to a +virtual table that they can do to a real table, with the following +exceptions: + +
+
-
+
- One cannot create a trigger on a virtual table. +
- One cannot create additional indices on a virtual table. + (Virtual tables can have indices but that must be built into + the virtual table implementation. Indices cannot be added + separately using CREATE INDEX statements.) +
- One cannot run ALTER TABLE ... ADD COLUMN + commands against a virtual table. +
Individual virtual table implementations might impose additional +constraints. For example, some virtual implementations might provide +read-only tables. Or some virtual table implementations might allow +INSERT or DELETE but not UPDATE. Or some virtual table implementations +might limit the kinds of UPDATEs that can be made. + +
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. + +
Here are some existing and postulated uses for virtual tables: + +
-
+
- A full-text search interface +
- Spatial indices using R-Trees +
- Introspect the disk content of an SQLite database file + (the dbstat virtual table) +
- Read and/or write the content of a comma-separated value (CSV) + file +
- Access the filesystem of the host computer as if it were a database table +
- Enabling SQL manipulation of data in statistics packages like R +
1.1 Usage
+ +A virtual table is created using a CREATE VIRTUAL TABLE statement. + +
+++ + +
The CREATE VIRTUAL TABLE statement creates a new table +called table-name derived from the class +class module-name. The module-name +is the name that is registered for the virtual table by +the sqlite3_create_module() interface. + +
+ ++ CREATE VIRTUAL TABLE tablename USING modulename; +
One can also provide comma-separated arguments to the module following +the module name: + +
+ ++ CREATE VIRTUAL TABLE tablename USING modulename(arg1, arg2, ...); +
The format of the arguments to the module is very general. Each +module-argument +may contain keywords, string literals, identifiers, numbers, and +punctuation. Each module-argument is passed as +written (as text) into the +constructor method of the virtual table implementation +when the virtual +table is created and that constructor is responsible for parsing and +interpreting the arguments. The argument syntax is sufficiently general +that a virtual table implementation can, if it wants to, interpret its +arguments as column definitions in an ordinary CREATE TABLE statement. +The implementation could also impose some other interpretation on the +arguments. + +
Once a virtual table has been created, it can be used like any other +table with the exceptions noted above and imposed by specific virtual +table implementations. A virtual table is destroyed using the ordinary +DROP TABLE syntax. + +
1.1.1 Temporary virtual tables
+ +There is no "CREATE TEMP VIRTUAL TABLE" statement. To create a
+temporary virtual table, add the "temp" schema
+before the virtual table name.
+
+ Some virtual tables exist automatically in the "main" schema of
+every database connection in which their
+module is registered, even without a CREATE VIRTUAL TABLE statement.
+Such virtual tables are called "eponymous virtual tables".
+To use an eponymous virtual table, simple use the
+module name as if it were a table.
+Eponymous virtual tables exist in the "main" schema only, so they will
+not work if prefixed with a different schema name.
+
+ An example of an eponymous virtual table is the dbstat virtual table.
+To use the dbstat virtual table as an eponymous virtual table,
+simply query against the "dbstat"
+module name, as if it were an ordinary table. (Note that SQLite
+must be compiled with the SQLITE_ENABLE_DBSTAT_VTAB option to include
+the dbstat virtual table in the build.)
+
+ A virtual table is eponymous if its xCreate method is the exact same
+function as the xConnect method, or if the xCreate method is NULL.
+The xCreate method is called when a virtual table is first created
+using the CREATE VIRTUAL TABLE statement. The xConnect method whenever
+a database connection attaches to or reparses a schema. When these two methods
+are the same, that indicates that the virtual table has no persistent
+state that needs to be created and destroyed.
+
+
+
+ If the xCreate method is NULL, then
+CREATE VIRTUAL TABLE statements are prohibited for that virtual table,
+and the virtual table is an "eponymous-only virtual table".
+Eponymous-only virtual tables are useful as
+table-valued functions.
+
+
+Note that SQLite versions prior to 3.9.0 did not check the xCreate method
+for NULL before invoking it. So if an eponymous-only virtual table is
+registered with SQLite version 3.8.11.1 or earlier and a CREATE VIRTUAL TABLE
+command is attempted against that virtual table module, a jump to a NULL
+pointer will occur, resulting in a crash.
+
+ Several new C-level objects are used by the virtual table implementation:
+
+ The sqlite3_module structure defines a module object used to implement
+a virtual table. Think of a module as a class from which one can
+construct multiple virtual tables having similar properties. For example,
+one might have a module that provides read-only access to
+comma-separated-value (CSV) files on disk. That one module can then be
+used to create several virtual tables where each virtual table refers
+to a different CSV file.
+
+ The module structure contains methods that are invoked by SQLite to
+perform various actions on the virtual table such as creating new
+instances of a virtual table or destroying old ones, reading and
+writing data, searching for and deleting, updating, or inserting rows.
+The module structure is explained in more detail below.
+
+ Each virtual table instance is represented by an sqlite3_vtab structure.
+The sqlite3_vtab structure looks like this:
+
+ Virtual table implementations will normally subclass this structure
+to add additional private and implementation-specific fields.
+The nRef field is used internally by the SQLite core and should not
+be altered by the virtual table implementation. The virtual table
+implementation may pass error message text to the core by putting
+an error message string in zErrMsg.
+Space to hold this error message string must be obtained from an
+SQLite memory allocation function such as sqlite3_mprintf() or
+sqlite3_malloc().
+Prior to assigning a new value to zErrMsg, the virtual table
+implementation must free any preexisting content of zErrMsg using
+sqlite3_free(). Failure to do this will result in a memory leak.
+The SQLite core will free and zero the content of zErrMsg when it
+delivers the error message text to the client application or when
+it destroys the virtual table. The virtual table implementation only
+needs to worry about freeing the zErrMsg content when it overwrites
+the content with a new, different error message.
+
+ The sqlite3_vtab_cursor structure represents a pointer to a specific
+row of a virtual table. This is what an sqlite3_vtab_cursor looks like:
+
+ Once again, practical implementations will likely subclass this
+structure to add additional private fields.
+
+ The sqlite3_index_info structure is used to pass information into
+and out of the xBestIndex method of the module that implements a
+virtual table.
+
+ Before a CREATE VIRTUAL TABLE statement can be run, the module
+specified in that statement must be registered with the database
+connection. This is accomplished using either of the sqlite3_create_module()
+or sqlite3_create_module_v2() interfaces:
+
+ The sqlite3_create_module() and sqlite3_create_module_v2()
+routines associates a module name with
+an sqlite3_module structure and a separate client data that is specific
+to each module. The only difference between the two create_module methods
+is that the _v2 method includes an extra parameter that specifies a
+destructor for client data pointer. The module structure is what defines
+the behavior of a virtual table. The module structure looks like this:
+
+ The module structure defines all of the methods for each virtual
+table object. The module structure also contains the iVersion field which
+defines the particular edition of the module table structure. Currently,
+iVersion is always 1, but in future releases of SQLite the module structure
+definition might be extended with additional methods and in that case
+the iVersion value will be increased.
+
+ The rest of the module structure consists of methods used to implement
+various features of the virtual table. Details on what each of these
+methods do are provided in the sequel.
+
+ Prior to SQLite version 3.6.17, the virtual table mechanism assumes
+that each database connection kept
+its own copy of the database schema. Hence, the virtual table mechanism
+could not be used in a database that has shared cache mode enabled.
+The sqlite3_create_module() interface would return an error if
+shared cache mode is enabled. That restriction was relaxed
+beginning with SQLite version 3.6.17.
+
+ Follow these steps to create your own virtual table:
+
+
+ The only really hard part is step 1. You might want to start with an
+existing virtual table implementation and modify it to suit your needs.
+There are several virtual table implementations in the SQLite source tree
+(for testing purposes). You might use one of those as a guide. Locate
+these test virtual table implementations by searching
+for "sqlite3_create_module".
+
+ You might also want to implement your new virtual table as a
+loadable extension.
+
+ 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() or
+sqlite3_create_module_v2() call that registered the
+virtual table 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
+module name is the name provided as the second argument to
+sqlite3_create_module() and as the argument to the USING clause of the
+CREATE VIRTUAL TABLE statement that is running.
+The second, argv[1], is the name of the database in which the new virtual table is being created. The database name is "main" for the primary database, or
+"temp" for TEMP database, or the name given at the end of the ATTACH
+statement for attached databases. The third element of the array, argv[2],
+is the name of the new virtual table, as specified following the TABLE
+keyword in the CREATE VIRTUAL TABLE statement.
+If present, the fourth and subsequent 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 object
+(an sqlite3_vtab object) 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:
+
+ The first argument to sqlite3_declare_vtab() must be the same
+database connection pointer as the first parameter to this method.
+The second argument to sqlite3_declare_vtab() must 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.
+The CREATE TABLE statement string need not to be
+held in persistent memory. The string can be
+deallocated and/or reused as soon as the sqlite3_declare_vtab()
+routine returns.
+
+ The xCreate method need not initialize the pModule, nRef, and zErrMsg
+fields of the sqlite3_vtab object. The SQLite core will take care of
+that chore.
+
+ The xCreate 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, the sqlite3_vtab structure must not be allocated.
+An error message may optionally be returned in *pzErr if unsuccessful.
+Space to hold the error message string must be allocated using
+an SQLite memory allocation function like
+sqlite3_malloc() or sqlite3_mprintf() as the SQLite core will
+attempt to free the space using sqlite3_free() after the error has
+been reported up to the application.
+
+
+If the xCreate method is omitted (left as a NULL pointer) then the
+virtual table is an eponymous-only virtual table. New instances of
+the virtual table cannot be created using CREATE VIRTUAL TABLE and the
+virtual table can only be used via its module name.
+Note that SQLite versions prior to 3.9.0 do not understand
+eponymous-only virtual tables and will segfault if an attempt is made
+to CREATE VIRTUAL TABLE on an eponymous-only virtual table because
+the xCreate method was not checked for null.
+
+
+If the xCreate method is the exact same pointer as the xConnect method,
+that indicates that the virtual table does not need to initialize backing
+store. Such a virtual table can be used as an eponymous virtual table
+or as a named virtual table using CREATE VIRTUAL TABLE or both.
+
+
+
+ If a column datatype contains the special keyword "HIDDEN"
+(in any combination of upper and lower case letters) then that keyword
+it is omitted from the column datatype name and the column is marked
+as a hidden column internally.
+A hidden column differs from a normal column in three respects:
+
+
+ For example, if the following SQL is passed to sqlite3_declare_vtab():
+
+ Then the virtual table would be created with two hidden columns,
+and with datatypes of "VARCHAR(12)" and "INTEGER".
+
+ An example use of hidden columns can be seen in the FTS3 virtual
+table implementation, where every FTS virtual table
+contains an FTS hidden column that is used to pass information from the
+virtual table into FTS auxiliary functions and to the FTS MATCH operator.
+
+
+
+ A virtual table that contains hidden columns can be used like
+a table-valued function in the FROM clause of a SELECT statement.
+The arguments to the table-valued function become constraints on
+the HIDDEN columns of the virtual table.
+
+ For example, the "generate_series" extension (located in the
+ext/misc/series.c
+file in the source tree)
+implements an eponymous virtual table with the following schema:
+
+ The sqlite3_module.xBestIndex method in the implementation of this
+table checks for equality constraints against the HIDDEN columns, and uses
+those as input parameters to determine the range of integer "value" outputs
+to generate. Reasonable defaults are used for any unconstrained columns.
+For example, to list all integers between 5 and 50:
+
+ The previous query is equivalent to the following:
+
+ Arguments on the virtual table name are matched to hidden columns
+in order. The number of arguments can be less than the
+number of hidden columns, in which case the latter hidden columns are
+unconstrained. However, an error results if there are more arguments
+than there are hidden columns in the virtual table.
+
+
+
+
+ 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. When xCreate and xConnect are the same,
+the table is an eponymous virtual table.
+
+ 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 has to locate and use an existing dictionary
+and posting lists that were created by a prior xCreate call.
+
+ The xConnect method must return SQLITE_OK if it is successful
+in creating the new virtual table, or SQLITE_ERROR if it is not
+successful. If not successful, the sqlite3_vtab structure must not be
+allocated. An error message may optionally be returned in *pzErr if
+unsuccessful.
+Space to hold the error message string must be allocated using
+an SQLite memory allocation function like
+sqlite3_malloc() or sqlite3_mprintf() as the SQLite core will
+attempt to free the space using sqlite3_free() after the error has
+been reported up to the application.
+
+ The xConnect method is required for every virtual table implementation,
+though the xCreate and xConnect pointers of the sqlite3_module object
+may point to the same function if the virtual table does not need to
+initialize backing store.
+
+
+
+ SQLite uses the xBestIndex method of a virtual table module to determine
+the best way to access the virtual table.
+The xBestIndex method has a prototype like this:
+
+ The SQLite core communicates with the xBestIndex method by filling
+in certain fields of the sqlite3_index_info structure and passing a
+pointer to that structure into xBestIndex as the second parameter.
+The xBestIndex method fills out other fields of this structure which
+forms the reply. The sqlite3_index_info structure looks like this:
+
+ Please note the warnings on the "estimatedRows" and "idxFlags" field.
+These fields were added with SQLite versions 3.8.2 and 3.9.0, respectively.
+Any extension that reads or writes these fields must first check that the
+version of the SQLite library in use is greater than or equal to 3.8.2 or
+3.9.0 - perhaps using a call to sqlite3_version(). The result of attempting
+to access these fields in an sqlite3_index_info structure created by an
+older version of SQLite are undefined.
+
+ In addition, there are some defined constants:
+
+ The SQLite core calls the xBestIndex method when it is compiling a query
+that involves a virtual table. In other words, SQLite calls this method
+when it is running sqlite3_prepare() or the equivalent.
+By calling this method, the
+SQLite core is saying to the virtual table that it needs to access
+some subset of the rows in the virtual table and it wants to know the
+most efficient way to do that access. The xBestIndex method replies
+with information that the SQLite core can then use to conduct an
+efficient search of the virtual table.
+
+ While compiling a single SQL query, the SQLite core might call
+xBestIndex multiple times with different settings in sqlite3_index_info.
+The SQLite core will then select the combination that appears to
+give the best performance.
+
+ Before calling this method, the SQLite core initializes an instance
+of the sqlite3_index_info structure with information about the
+query that it is currently trying to process. This information
+derives mainly from the WHERE clause and ORDER BY or GROUP BY clauses
+of the query, but also from any ON or USING clauses if the query is a
+join. The information that the SQLite core provides to the xBestIndex
+method is held in the part of the structure that is marked as "Inputs".
+The "Outputs" section is initialized to zero.
+
+ The information in the sqlite3_index_info structure is ephemeral
+and may be overwritten or deallocated as soon as the xBestIndex method
+returns. If the xBestIndex method needs to remember any part of the
+sqlite3_index_info structure, it should make a copy. Care must be
+take to store the copy in a place where it will be deallocated, such
+as in the idxStr field with needToFreeIdxStr set to 1.
+
+ Note that xBestIndex will always be called before xFilter, since
+the idxNum and idxStr outputs from xBestIndex are required inputs to
+xFilter. However, there is no guarantee that xFilter will be called
+following a successful xBestIndex.
+
+ The xBestIndex method is required for every virtual table implementation.
+
+ The main thing that the SQLite core is trying to communicate to
+the virtual table is the constraints that are available to limit
+the number of rows that need to be searched. The aConstraint[] array
+contains one entry for each constraint. There will be exactly
+nConstraint entries in that array.
+
+ Each constraint will correspond to a term in the WHERE clause
+or in a USING or ON clause that is of the form
+
+ Where "column" is a column in the virtual table, OP is an operator
+like "=" or "<", and EXPR is an arbitrary expression. So, for example,
+if the WHERE clause contained a term like this:
+
+ Then one of the constraints would be on the "a" column with
+operator "=" and an expression of "5". Constraints need not have a
+literal representation of the WHERE clause. The query optimizer might
+make transformations to the
+WHERE clause in order to extract as many constraints
+as it can. So, for example, if the WHERE clause contained something
+like this:
+
+ The query optimizer might translate this into three separate constraints:
+
+ For each constraint, the aConstraint[].iColumn field indicates which
+column appears on the left-hand side of the constraint.
+The first column of the virtual table is column 0.
+The rowid of the virtual table is column -1.
+The aConstraint[].op field indicates which operator is used.
+The SQLITE_INDEX_CONSTRAINT_* constants map integer constants
+into operator values.
+Columns occur in the order they were defined by the call to
+sqlite3_declare_vtab() in the xCreate or xConnect method.
+Hidden columns are counted when determining the column index.
+
+ The aConstraint[] array contains information about all constraints
+that apply to the virtual table. But some of the constraints might
+not be usable because of the way tables are ordered in a join.
+The xBestIndex method must therefore only consider constraints
+that have an aConstraint[].usable flag which is true.
+
+ In addition to WHERE clause constraints, the SQLite core also
+tells the xBestIndex method about the ORDER BY clause.
+(In an aggregate query, the SQLite core might put in GROUP BY clause
+information in place of the ORDER BY clause information, but this fact
+should not make any difference to the xBestIndex method.)
+If all terms of the ORDER BY clause are columns in the virtual table,
+then nOrderBy will be the number of terms in the ORDER BY clause
+and the aOrderBy[] array will identify the column for each term
+in the order by clause and whether or not that column is ASC or DESC.
+
+ Given all of the information above, the job of the xBestIndex
+method it to figure out the best way to search the virtual table.
+
+ The xBestIndex method fills the idxNum and idxStr fields with
+information that communicates an indexing strategy to the xFilter
+method. The information in idxNum and idxStr is arbitrary as far
+as the SQLite core is concerned. The SQLite core just copies the
+information through to the xFilter method. Any desired meaning can
+be assigned to idxNum and idxStr as long as xBestIndex and xFilter
+agree on what that meaning is.
+
+ The idxStr value may be a string obtained from an SQLite
+memory allocation function such as sqlite3_mprintf().
+If this is the case, then the needToFreeIdxStr flag must be set to
+true so that the SQLite core will know to call sqlite3_free() on
+that string when it has finished with it, and thus avoid a memory leak.
+
+ If the virtual table will output rows in the order specified by
+the ORDER BY clause, then the orderByConsumed flag may be set to
+true. If the output is not automatically in the correct order
+then orderByConsumed must be left in its default false setting.
+This will indicate to the SQLite core that it will need to do a
+separate sorting pass over the data after it comes out of the virtual table.
+
+ The estimatedCost field should be set to the estimated number
+of disk access operations required to execute this query against
+the virtual table. The SQLite core will often call xBestIndex
+multiple times with different constraints, obtain multiple cost
+estimates, then choose the query plan that gives the lowest estimate.
+
+ If the current version of SQLite is 3.8.2 or greater, the estimatedRows
+field may be set to an estimate of the number of rows returned by the
+proposed query plan. If this value is not explicitly set, the default
+estimate of 25 rows is used.
+
+ If the current version of SQLite is 3.9.0 or greater, the idxFlags field
+may be set to SQLITE_INDEX_SCAN_UNIQUE to indicate that the virtual table
+will return only zero or one rows given the input constraints. Additional
+bits of the idxFlags field might be understood in later versions of SQLite.
+
+ The aConstraintUsage[] array contains one element for each of
+the nConstraint constraints in the inputs section of the
+sqlite3_index_info structure.
+The aConstraintUsage[] array is used by xBestIndex to tell the
+core how it is using the constraints.
+
+ The xBestIndex method may set aConstraintUsage[].argvIndex
+entries to values greater than zero.
+Exactly one entry should be set to 1, another to 2, another to 3,
+and so forth up to as many or as few as the xBestIndex method wants.
+The EXPR of the corresponding constraints will then be passed
+in as the argv[] parameters to xFilter.
+
+ For example, if the aConstraint[3].argvIndex is set to 1, then
+when xFilter is called, the argv[0] passed to xFilter will have
+the EXPR value of the aConstraint[3] constraint.
+
+ By default, the SQLite core double checks all constraints on
+each row of the virtual table that it receives. If such a check
+is redundant, the xBestFilter method can suppress that double-check by
+setting aConstraintUsage[].omit.
+
+
+
+ This method releases a connection to a virtual table.
+Only the sqlite3_vtab object is destroyed.
+The virtual table is not destroyed and any backing store
+associated with the virtual table persists.
+
+This method undoes the work of xConnect.
+
+ This method is a destructor for a connection to the virtual table.
+Contrast this method with xDestroy. The xDestroy is a destructor
+for the entire virtual table.
+
+ The xDisconnect method is required for every virtual table implementation,
+though it is acceptable for the xDisconnect and xDestroy methods to be
+the same function if that makes sense for the particular virtual table.
+
+
+
+ This method releases a connection to a virtual table, just like
+the xDisconnect method, and it also destroys the underlying
+table implementation. This method undoes the work 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.
+
+ The xDestroy method is required for every virtual table implementation,
+though it is acceptable for the xDisconnect and xDestroy methods to be
+the same function if that makes sense for the particular virtual table.
+
+
+
+ The xOpen method creates a new cursor used for accessing (read and/or
+writing) a virtual table. A successful invocation of this method
+will allocate the memory for the sqlite3_vtab_cursor (or a subclass),
+initialize the new object, and make *ppCursor point to the new object.
+The successful call then returns SQLITE_OK.
+
+ For every successful call to this method, the SQLite core will
+later invoke the xClose method to destroy
+the allocated cursor.
+
+ The xOpen method need not initialize the pVtab field of the
+sqlite3_vtab_cursor structure. The SQLite core will take care
+of that chore automatically.
+
+ A virtual table implementation must be able to support an arbitrary
+number of simultaneously open cursors.
+
+ When initially opened, the cursor is in an undefined state.
+The SQLite core will invoke the xFilter method
+on the cursor prior to any attempt to position or read from the cursor.
+
+ The xOpen method is required for every virtual table implementation.
+
+
+
+ The xClose method closes a cursor previously opened by
+xOpen.
+The SQLite core will always call xClose once for each cursor opened
+using xOpen.
+
+ This method must release all resources allocated by the
+corresponding xOpen call. The routine will not be called again even if it
+returns an error. The SQLite core will not use the
+sqlite3_vtab_cursor again after it has been closed.
+
+ The xClose method is required for every virtual table implementation.
+
+
+
+ The xEof method must return false (zero) if the specified cursor
+currently points to a valid row of data, or true (non-zero) otherwise.
+This method is called by the SQL engine immediately after each
+xFilter and xNext invocation.
+
+ The xEof method is required for every virtual table implementation.
+
+
+
+ This method begins a search of a virtual table.
+The first argument is a cursor opened by xOpen.
+The next two arguments define a particular search index previously
+chosen 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 virtual table contains one or more rows that match the
+search criteria, then the cursor must be left point at the first row.
+Subsequent calls to xEof must return false (zero).
+If there are no rows match, then the cursor must be left in a state
+that will cause the xEof to return true (non-zero).
+The SQLite engine will use
+the xColumn and xRowid methods to access that row content.
+The xNext method will be used to advance to the next row.
+
+ This method must return SQLITE_OK if successful, or an sqlite
+error code if an error occurs.
+
+ The xFilter method is required for every virtual table implementation.
+
+
+
+ The xNext 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 must return true (non-zero).
+If the cursor is successfully advanced to another row of content, then
+subsequent calls to xEof must return false (zero).
+
+ This method must return SQLITE_OK if successful, or an sqlite
+error code if an error occurs.
+
+ The xNext method is required for every virtual table implementation.
+
+
+
+ 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 may return its result back to SQLite using one of the
+following interface:
+
+
+
+ CREATE VIRTUAL TABLE temp.tablename USING module(arg1, ...);
+
+
+
+
+1.1.2 Eponymous virtual tables
+
+
+
+
+ SELECT * FROM dbstat;
+
1.1.2.1 Eponymous-only virtual tables
+1.2 Implementation
+
+
+
+
+ typedef struct sqlite3_vtab sqlite3_vtab;
+ typedef struct sqlite3_index_info sqlite3_index_info;
+ typedef struct sqlite3_vtab_cursor sqlite3_vtab_cursor;
+ typedef struct sqlite3_module sqlite3_module;
+
+
+
+ struct sqlite3_vtab {
+ const sqlite3_module *pModule;
+ int nRef;
+ char *zErrMsg;
+ };
+
+
+
+ struct sqlite3_vtab_cursor {
+ sqlite3_vtab *pVtab;
+ };
+
+
+
+ int sqlite3_create_module(
+ sqlite3 *db, /* SQLite connection to register module with */
+ const char *zName, /* Name of the module */
+ const sqlite3_module *, /* Methods for the module */
+ void * /* Client data for xCreate/xConnect */
+ );
+ int sqlite3_create_module_v2(
+ sqlite3 *db, /* SQLite connection to register module with */
+ const char *zName, /* Name of the module */
+ const sqlite3_module *, /* Methods for the module */
+ void *, /* Client data for xCreate/xConnect */
+ void(*xDestroy)(void*) /* Client data destructor function */
+ );
+
+
+
+ struct sqlite3_module {
+ int iVersion;
+ int (*xCreate)(sqlite3*, void *pAux,
+ int argc, char **argv,
+ sqlite3_vtab **ppVTab,
+ char **pzErr);
+ int (*xConnect)(sqlite3*, void *pAux,
+ int argc, char **argv,
+ sqlite3_vtab **ppVTab,
+ char **pzErr);
+ int (*xBestIndex)(sqlite3_vtab *pVTab, sqlite3_index_info*);
+ int (*xDisconnect)(sqlite3_vtab *pVTab);
+ int (*xDestroy)(sqlite3_vtab *pVTab);
+ int (*xOpen)(sqlite3_vtab *pVTab, sqlite3_vtab_cursor **ppCursor);
+ int (*xClose)(sqlite3_vtab_cursor*);
+ int (*xFilter)(sqlite3_vtab_cursor*, int idxNum, const char *idxStr,
+ int argc, sqlite3_value **argv);
+ int (*xNext)(sqlite3_vtab_cursor*);
+ int (*xEof)(sqlite3_vtab_cursor*);
+ int (*xColumn)(sqlite3_vtab_cursor*, sqlite3_context*, int);
+ int (*xRowid)(sqlite3_vtab_cursor*, sqlite_int64 *pRowid);
+ int (*xUpdate)(sqlite3_vtab *, int, sqlite3_value **, sqlite_int64 *);
+ int (*xBegin)(sqlite3_vtab *pVTab);
+ int (*xSync)(sqlite3_vtab *pVTab);
+ int (*xCommit)(sqlite3_vtab *pVTab);
+ int (*xRollback)(sqlite3_vtab *pVTab);
+ int (*xFindFunction)(sqlite3_vtab *pVtab, int nArg, const char *zName,
+ void (**pxFunc)(sqlite3_context*,int,sqlite3_value**),
+ void **ppArg);
+ int (*Rename)(sqlite3_vtab *pVtab, const char *zNew);
+ /* The methods above are in version 1 of the sqlite_module object. Those
+ ** below are for version 2 and greater. */
+ int (*xSavepoint)(sqlite3_vtab *pVTab, int);
+ int (*xRelease)(sqlite3_vtab *pVTab, int);
+ int (*xRollbackTo)(sqlite3_vtab *pVTab, int);
+ };
+1.3 Virtual Tables And Shared Cache
+
+1.4 Creating New Virtual Table Implementations
+
+
+
+
+2.0 Virtual Table Methods
+
+
+
+2.1 The xCreate Method
+
+
+
+
+ int (*xCreate)(sqlite3 *db, void *pAux,
+ int argc, char **argv,
+ sqlite3_vtab **ppVTab,
+ char **pzErr);
+
+
+
+ int sqlite3_declare_vtab(sqlite3 *db, const char *zCreateTable)
+
2.1.1 Hidden columns in virtual tables
+
+
+
+
+
+
+ CREATE TABLE x(a HIDDEN VARCHAR(12), b INTEGER, c INTEGER Hidden);
+
2.1.2 Table-valued functions
+
+
+
+
+CREATE TABLE generate_series(
+ value,
+ start HIDDEN,
+ stop HIDDEN,
+ step HIDDEN
+);
+
+
+
+SELECT value FROM generate_series(5,50);
+
+
+
+SELECT value FROM generate_series WHERE start=5 AND stop=50;
+
2.2 The xConnect Method
+
+
+
+
+ int (*xConnect)(sqlite3*, void *pAux,
+ int argc, char **argv,
+ sqlite3_vtab **ppVTab,
+ char **pzErr);
+
2.3 The xBestIndex Method
+
+
+
+
+ int (*xBestIndex)(sqlite3_vtab *pVTab, sqlite3_index_info*);
+
+
+
+ struct sqlite3_index_info {
+ /* Inputs */
+ const int nConstraint; /* Number of entries in aConstraint */
+ const struct sqlite3_index_constraint {
+ int iColumn; /* Column on left-hand side of constraint */
+ unsigned char op; /* Constraint operator */
+ unsigned char usable; /* True if this constraint is usable */
+ int iTermOffset; /* Used internally - xBestIndex should ignore */
+ } *const aConstraint; /* Table of WHERE clause constraints */
+ const int nOrderBy; /* Number of terms in the ORDER BY clause */
+ const struct sqlite3_index_orderby {
+ int iColumn; /* Column number */
+ unsigned char desc; /* True for DESC. False for ASC. */
+ } *const aOrderBy; /* The ORDER BY clause */
+
+ /* Outputs */
+ struct sqlite3_index_constraint_usage {
+ int argvIndex; /* if >0, constraint is part of argv to xFilter */
+ unsigned char omit; /* Do not code a test for this constraint */
+ } *const aConstraintUsage;
+ int idxNum; /* Number used to identify the index */
+ char *idxStr; /* String, possibly obtained from sqlite3_malloc */
+ int needToFreeIdxStr; /* Free idxStr using sqlite3_free() if true */
+ int orderByConsumed; /* True if output is already ordered */
+ double estimatedCost; /* Estimated cost of using this index */
+ /* Fields below are only available in SQLite 3.8.2 and later */
+ sqlite3_int64 estimatedRows; /* Estimated number of rows returned */
+ /* Fields below are only available in SQLite 3.9.0 and later */
+ int idxFlags; /* Mask of SQLITE_INDEX_SCAN_* flags */
+ };
+
+
+
+ #define SQLITE_INDEX_CONSTRAINT_EQ 2
+ #define SQLITE_INDEX_CONSTRAINT_GT 4
+ #define SQLITE_INDEX_CONSTRAINT_LE 8
+ #define SQLITE_INDEX_CONSTRAINT_LT 16
+ #define SQLITE_INDEX_CONSTRAINT_GE 32
+ #define SQLITE_INDEX_CONSTRAINT_MATCH 64
+ #define SQLITE_INDEX_SCAN_UNIQUE 1 /* Scan visits at most 1 row */
+
2.3.1 Inputs
+
+
+ column OP EXPR
+
+
+
+
+
+ a = 5
+
+
+
+ x BETWEEN 10 AND 100 AND 999>y
+
+
+
+ x >= 10
+ x <= 100
+ y < 999
+
2.3.2 Outputs
+
+2.4 The xDisconnect Method
+
+
+
+
+ int (*xDisconnect)(sqlite3_vtab *pVTab);
+
2.5 The xDestroy Method
+
+
+
+
+ int (*xDestroy)(sqlite3_vtab *pVTab);
+
2.6 The xOpen Method
+
+
+
+
+ int (*xOpen)(sqlite3_vtab *pVTab, sqlite3_vtab_cursor **ppCursor);
+
2.7 The xClose Method
+
+
+
+
+ int (*xClose)(sqlite3_vtab_cursor*);
+
2.8 The xEof Method
+
+
+
+
+ int (*xEof)(sqlite3_vtab_cursor*);
+
2.9 The xFilter Method
+
+
+
+
+ int (*xFilter)(sqlite3_vtab_cursor*, int idxNum, const char *idxStr,
+ int argc, sqlite3_value **argv);
+
2.10 The xNext Method
+
+
+
+
+ int (*xNext)(sqlite3_vtab_cursor*);
+
2.11 The xColumn Method
+
+
+
+
+ int (*xColumn)(sqlite3_vtab_cursor*, sqlite3_context*, int N);
+
If the xColumn method implementation calls none of the functions above, +then the value of the column defaults to an SQL NULL. + +
To raise an error, the xColumn method should use one of the result_text() +methods to set the error message text, then return an appropriate +error code. The xColumn method must return SQLITE_OK on success. + +
The xColumn method is required for every virtual table implementation. + + + +
2.12 The xRowid Method
+ ++ ++ int (*xRowid)(sqlite3_vtab_cursor *pCur, sqlite_int64 *pRowid); +
A successful invocation of this method will cause *pRowid to be +filled with the rowid of row that the +virtual table cursor pCur is currently pointing at. +This method returns SQLITE_OK on success. +It returns an appropriate error code on failure.
+ +The xRowid method is required for every virtual table implementation. + + + +
2.13 The xUpdate Method
+ ++ ++ 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. + +
The argc parameter specifies the number of entries in the argv array. +The value of argc will be 1 for a pure delete operation or N+2 for an insert +or replace or update where N is the number of columns in the table. +In the previous sentence, N includes any hidden columns. + +
Every argv entry will have a non-NULL value in C but may contain the +SQL value NULL. In other words, it is always true that +argv[i]!=0 for i between 0 and argc-1. +However, it might be the case that +sqlite3_value_type(argv[i])==SQLITE_NULL. + +
The argv[0] parameter is the rowid of a row in the virtual table +to be deleted. If argv[0] is an SQL 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 an SQL 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. All hidden columns are included. + +
When doing an insert without a rowid (argc>1, argv[1] is an SQL NULL), the +implementation must set *pRowid to the rowid of the newly inserted row; +this will become the value returned by the sqlite3_last_insert_rowid() +function. Setting this value in all the other cases is a harmless no-op; +the SQLite engine ignores the *pRowid return value if argc==1 or +argv[1] is not an SQL NULL. + +
Each call to xUpdate will fall into one of cases shown below. +Not that references to argv[i] mean the SQL value +held within the argv[i] object, not the argv[i] +object itself. + +
++ ++
+- argc = 1 +
The single row with rowid equal to argv[0] is deleted. No insert occurs. + +
- argc > 1
argv[0] = NULL +A new row is inserted with a rowid argv[1] and column values in + argv[2] and following. If argv[1] is an SQL NULL, + the a new unique rowid is generated automatically. + +
- 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 ...; ++
The xUpdate method must return SQLITE_OK if and only if it is +successful. If a failure occurs, the xUpdate must return an appropriate +error code. On a failure, the pVTab->zErrMsg element may optionally +be replaced with error message text stored in memory allocated from SQLite +using functions such as sqlite3_mprintf() or sqlite3_malloc(). + +
If the xUpdate method violates some constraint of the virtual table +(including, but not limited to, attempting to store a value of the wrong +datatype, attempting to store a value that is too +large or too small, or attempting to change a read-only value) then the +xUpdate must fail with an appropriate error code. + +
There might be one or more sqlite3_vtab_cursor objects open and in use +on the virtual table instance and perhaps even on the row of the virtual +table when the xUpdate method is invoked. The implementation of +xUpdate must be prepared for attempts to delete or modify rows of the table +out from other existing cursors. If the virtual table cannot accommodate +such changes, the xUpdate method must return an error code. + +
The xUpdate method is optional. +If the xUpdate pointer in the sqlite3_module for a virtual table +is a NULL pointer, then the virtual table is read-only. + + + + +
2.14 The xFindFunction Method
+ ++ ++ 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, this method +returns 0. To overload the function, this method writes the new +function implementation into *pxFunc and writes user data into *ppArg +and returns 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. + +
The function pointer returned by this routine must be valid for +the lifetime of the sqlite3_vtab object given in the first parameter. + + + +
2.15 The xBegin Method
+ ++ ++ int (*xBegin)(sqlite3_vtab *pVTab); +
This method begins a transaction on a virtual table. +This is method is optional. The xBegin pointer of sqlite3_module +may be NULL. + +
This method is always followed by one call to either the +xCommit or xRollback method. Virtual table transactions do +not nest, so the xBegin method will not be invoked more than once +on a single virtual table +without an intervening call to either xCommit or xRollback. +Multiple calls to other methods can and likely will occur in between +the xBegin and the corresponding xCommit or xRollback. + + + +
2.16 The xSync Method
+ ++ + ++ int (*xSync)(sqlite3_vtab *pVTab); +
This method signals the start of a two-phase commit on a virtual +table. +This is method is optional. The xSync pointer of sqlite3_module +may be NULL. + +
This method is only invoked after call to the xBegin method and +prior to an xCommit or xRollback. In order to implement two-phase +commit, the xSync method on all virtual tables is invoked prior to +invoking the xCommit method on any virtual table. If any of the +xSync methods fail, the entire transaction is rolled back. + + + +
2.17 The xCommit Method
+ ++ ++ int (*xCommit)(sqlite3_vtab *pVTab); +
This method causes a virtual table transaction to commit. +This is method is optional. The xCommit pointer of sqlite3_module +may be NULL. + +
A call to this method always follows a prior call to xBegin and +xSync. + + + + +
2.18 The xRollback Method
+ ++ ++ int (*xRollback)(sqlite3_vtab *pVTab); +
This method causes a virtual table transaction to rollback. +This is method is optional. The xRollback pointer of sqlite3_module +may be NULL. + +
A call to this method always follows a prior call to xBegin. + + + + +
2.19 The xRename Method
+ ++ ++ int (*xRename)(sqlite3_vtab *pVtab, const char *zNew); +
This method provides notification that the virtual table implementation +that the virtual table will be given a new name. +If this method returns SQLITE_OK then SQLite renames the table. +If this method returns an error code then the renaming is prevented. + +
The xRename method is required for every virtual table implementation. + + + +
2.20 The xSavepoint, xRelease, and xRollbackTo Methods
+ ++ ++ int (*xSavepoint)(sqlite3_vtab *pVtab, int); + int (*xRelease)(sqlite3_vtab *pVtab, int); + int (*xRollbackTo)(sqlite3_vtab *pVtab, int); +
+These methods provide the virtual table implementation an opportunity to +implement nested transactions. They are always optional and will only be +called in SQLite version 3.7.7 and later. +
+ ++When xSavepoint(X,N) is invoked, that is a signal to the virtual table X +that it should save its current state as savepoint N. +A subsequent call +to xRollbackTo(X,R) means that the state of the virtual table should return +to what it was when xSavepoint(X,R) was last called. +The call +to xRollbackTo(X,R) will invalidate all savepoints with N>R; none of the +invalided savepoints will be rolled back or released without first +being reinitialized by a call to xSavepoint(). +A call to xRelease(X,M) invalidates all savepoints where N>=M. +
+ ++None of the xSavepoint(), xRelease(), or xRollbackTo() methods will ever +be called except in between calls to xBegin() and +either xCommit() or xRollback(). +
+ Index: Doc/vtab.tcl ================================================================== --- Doc/vtab.tcl +++ Doc/vtab.tcl @@ -161,11 +161,11 @@ # # NOTE: This is the entry point for this script. # set path [file normalize [file dirname [info script]]] -set coreDocPath [file join $path Extra Core] +set coreDocPath [file join $path Special Core] set interfacePath [file join [file dirname $path] System.Data.SQLite] set inputFileName [file join $coreDocPath vtab.html] if {![file exists $inputFileName]} then { puts "input file \"$inputFileName\" does not exist"