+
+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/buildChm.tcl ================================================================== --- Doc/buildChm.tcl +++ Doc/buildChm.tcl @@ -43,10 +43,69 @@ return [string trim [lindex [split $result " "] 0]] } return "" } +# +# NOTE: This procedure unescapes certain HTML tags that are used within the +# documentation for the virtual table methods. +# +proc unescapeHtmlTags { fileName cdata } { + # + # NOTE: Read all the textual data from the file. + # + set data [readFile $fileName] + + # + # NOTE: No replacements made yet. + # + set count 0 + + # + # NOTE: If requested by the caller, unwrap all content contained with XML + # CDATA sections as well. + # + if {$cdata} then { + # + # NOTE: Grab everything within the CDATA tags and use verbatim. + # + incr count [regsub -all -- {} $data {\1} data] + } + + # + # TODO: Handle all the HTML tags we know may be present in the virtual + # table method documentation. This may need adjustments in the + # future. + # + foreach to [list \ + {} {} {} {
- } {
- } {
- } {
- }
ADDED Doc/vtab.tcl
Index: Doc/vtab.tcl
==================================================================
--- /dev/null
+++ Doc/vtab.tcl
@@ -0,0 +1,259 @@
+###############################################################################
+#
+# vtab.tcl --
+#
+# Written by Joe Mistachkin.
+# Released to the public domain, use at your own risk!
+#
+###############################################################################
+
+proc readFile { fileName } {
+ set file_id [open $fileName RDONLY]
+ fconfigure $file_id -encoding binary -translation binary
+ set result [read $file_id]
+ close $file_id
+ return $result
+}
+
+proc writeFile { fileName data } {
+ set file_id [open $fileName {WRONLY CREAT TRUNC}]
+ fconfigure $file_id -encoding binary -translation binary
+ puts -nonewline $file_id $data
+ close $file_id
+ return ""
+}
+
+proc escapeSubSpec { data } {
+ regsub -all -- {&} $data {\\\&} data
+ regsub -all -- {\\(\d+)} $data {\\\\\1} data
+ return $data
+}
+
+proc englishToList { value } {
+ set result [list]
+
+ foreach element [split $value "\t\n ,"] {
+ if {[string tolower $element] ni [list "" and or]} then {
+ lappend result $element
+ }
+ }
+
+ return $result
+}
+
+proc processLine { line prefix } {
+ if {[string length [string trim $line]] == 0 || \
+ [regexp -- {
- } { } {
- } \ + { } {
- } { } {
} {
}] { + regsub -all -- $remove $result "" result + + if {[string length [string trim $result]] == 0} then { + return "" + } + } + + foreach escape [list \ + {} {} {} {
- } {
- } {
- } {
} $result result + regsub -all -- {} $resultresult + regsub -all -- {
} $result} $result result + + return $result +} + +proc extractMethod { name lines pattern prefix indexVarName methodsVarName } { + upvar 1 $indexVarName index + upvar 1 $methodsVarName methods + + array set levels {p 0} + set length [llength $lines] + + while {$index < $length} { + set line [lindex $lines $index] + + if {[regexp -- $pattern $line]} then { + break; # stop on this line for outer loop. + } else { + set trimLine [string trim $line]; set data "" + + if {$levels(p) > 0 && [string length $trimLine] == 0} then { + # blank line, close paragraph. + if {[info exists methods($name)]} then { + # non-first line, leading line separator. + append data \n $prefix + } else { + # first line, no leading line separator. + append data $prefix + } + + incr levels(p) -1 + } elseif {[string range $trimLine 0 2] eq "result + regsub -all -- {
"} then { + # open tag ... maybe one line? + if {[string range $trimLine end-3 end] eq "
"} then { + set newLine [processLine $line $prefix] + + if {[string length $newLine] > 0} then { + # one line tag, wrap. + if {[info exists methods($name)]} then { + # non-first line, leading line separator. + append data \n $prefix2\.\d+ The (.*) Method(?:s)?
$} +set prefix " /// " +unset -nocomplain methods; set start false + +for {set index 0} {$index < [llength $lines]} {} { + set line [lindex $lines $index] + + if {$start} then { + if {[regexp -- $patterns(method) $line dummy capture]} then { + foreach method [englishToList $capture] { + set methodIndex [expr {$index + 1}] + + extractMethod \ + $method $lines $patterns(method) $prefix methodIndex methods + } + + set index $methodIndex + } else { + incr index + } + } elseif {[regexp -- {^2\.0 Virtual Table Methods
$} $line]} then { + set start true; incr index + } else { + incr index + } +} + +set data [string map [list \r\n \n] [readFile $outputFileName]] +set count 0; set start 0 + +# +# NOTE: These method names must be processed in the EXACT order that they +# appear in the output file. +# +foreach name [list \ + xCreate xConnect xBestIndex xDisconnect xDestroy xOpen xClose \ + xFilter xNext xEof xColumn xRowid xUpdate xBegin xSync xCommit \ + xRollback xFindFunction xRename xSavepoint xRelease xRollbackTo] { + # + # HACK: This assumes that a line of 71 forward slashes will be present + # before each method, except for the first one. + # + if {$count > 0} then { + set start [string first [string repeat / 71] $data $start] + } + + set pattern "" + + append pattern ^ {\s{8}} "///
+ /// int (*xCreate)(sqlite3 *db, void *pAux,
+ /// int argc, char **argv,
+ /// sqlite3_vtab **ppVTab,
+ /// char **pzErr);
+ ///
- /// int sqlite3_declare_vtab(sqlite3 *db, const char *zCreateTable)
- ///
- ///
+ /// int sqlite3_declare_vtab(sqlite3 *db, const char *zCreateTable)
+ ///
+ /// CREATE TABLE x(a HIDDEN VARCHAR(12), b INTEGER, c INTEGER Hidden);
+ ///
+ /// 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;
+ ///
+ /// int (*xConnect)(sqlite3*, void *pAux,
+ /// int argc, char **argv,
+ /// sqlite3_vtab **ppVTab,
+ /// char **pzErr);
+ ///
- /// 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 greater than zero, 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 */
- /// };
- ///
+ /// 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:
+ ///
+ /// 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_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 */
+ ///
- /// column OP EXPR
- ///
- ///
- /// a = 5
- ///
- ///
+ /// column OP EXPR
+ ///
+ /// a = 5
+ ///
- /// x BETWEEN 10 AND 100 AND 999>y
- ///
- ///
- /// x >= 10
- /// x <= 100
- /// y < 999
- ///
- ///
+ /// x BETWEEN 10 AND 100 AND 999>y
+ ///
+ /// x >= 10
+ /// x <= 100
+ /// y < 999
+ ///
+ /// 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*);
+ ///
- /// sqlite3_result_blob()
- /// sqlite3_result_double()
- /// sqlite3_result_int()
- /// sqlite3_result_int64()
- /// sqlite3_result_null()
- /// sqlite3_result_text()
- /// sqlite3_result_text16()
- /// sqlite3_result_text16le()
- /// sqlite3_result_text16be()
- /// sqlite3_result_zeroblob()
- ///
- ///
+ /// int (*xColumn)(sqlite3_vtab_cursor*, sqlite3_context*, int N);
+ ///
+ /// int (*xRowid)(sqlite3_vtab_cursor *pCur, sqlite_int64 *pRowid);
+ ///
+ /// int (*xUpdate)(
+ /// sqlite3_vtab *pVTab,
+ /// int argc,
+ /// sqlite3_value **argv,
+ /// sqlite_int64 *pRowid
+ /// );
+ ///
- /// argc = 1
- ///
- ///
- /// argc > 1
- /// argv[0] = NULL
- ///
- ///
- /// argc > 1
- /// argv[0] ? NULL
- /// argv[0] = argv[1]
- ///
- ///
- /// argc > 1
- /// argv[0] ? NULL
- /// argv[0] ? argv[1]
- ///
- ///
- /// UPDATE table SET rowid=rowid+1 WHERE ...;
- ///
+ /// 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.
+ ///
+ /// ]]>
+ /// ]]>]]>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 ...;
+ ///
+ /// 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 (*xRename)(sqlite3_vtab *pVtab, const char *zNew);
+ ///
+ /// int (*xSavepoint)(sqlite3_vtab *pVtab, int);
+ /// int (*xRelease)(sqlite3_vtab *pVtab, int);
+ /// int (*xRollbackTo)(sqlite3_vtab *pVtab, int);
+ ///
+ /// int (*xSavepoint)(sqlite3_vtab *pVtab, int);
+ /// int (*xRelease)(sqlite3_vtab *pVtab, int);
+ /// int (*xRollbackTo)(sqlite3_vtab *pVtab, int);
+ ///
+ /// int (*xSavepoint)(sqlite3_vtab *pVtab, int);
+ /// int (*xRelease)(sqlite3_vtab *pVtab, int);
+ /// int (*xRollbackTo)(sqlite3_vtab *pVtab, int);
+ ///