ADDED Doc/Extra/Core/vtab.html Index: Doc/Extra/Core/vtab.html ================================================================== --- Doc/Extra/Core/vtab.html +++ Doc/Extra/Core/vtab.html @@ -0,0 +1,1421 @@ + + + +The Virtual Table Mechanism Of SQLite + + + + +
+ + + +
+
Small. Fast. Reliable.
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: + +

+

+ +

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: + +

+ +

1.1 Usage

+ +

A virtual table is created using a CREATE VIRTUAL TABLE statement. + +

create-virtual-table-stmt: +

+
+ syntax diagram create-virtual-table-stmt +
+ + +

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. + +

+   CREATE VIRTUAL TABLE temp.tablename USING module(arg1, ...);
+
+ + + +

1.1.2 Eponymous virtual tables

+ +

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.) + +

+   SELECT * FROM dbstat;
+
+ +

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. + + + +

1.1.2.1 Eponymous-only virtual tables
+

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. + +

1.2 Implementation

+ +

Several new C-level objects are used by the virtual table 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;
+
+ +

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: + +

+  struct sqlite3_vtab {
+    const sqlite3_module *pModule;
+    int nRef;
+    char *zErrMsg;
+  };
+
+ +

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: + +

+  struct sqlite3_vtab_cursor {
+    sqlite3_vtab *pVtab;
+  };
+
+ +

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: + +

+  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 */
+  );
+
+ +

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: + +

  
+  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);
+  };
+
+ +

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. + +

1.3 Virtual Tables And Shared Cache

+ +

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. + +

1.4 Creating New Virtual Table Implementations

+ +

Follow these steps to create your own virtual table: + +

+

    +
  1. Write all necessary methods. +
  2. Create an instance of the sqlite3_module structure containing pointers + to all the methods from step 1. +
  3. Register your sqlite3_module structure using one of the + sqlite3_create_module() or sqlite3_create_module_v2() interfaces. +
  4. Run a CREATE VIRTUAL TABLE command that specifies the new module in + the USING clause. +
+ +

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. + +

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);
+
+ +

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: + +

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

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. + + + +

2.1.1 Hidden columns in virtual tables

+

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(): + +

+   CREATE TABLE x(a HIDDEN VARCHAR(12), b INTEGER, c INTEGER Hidden);
+
+ +

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. + + + +

2.1.2 Table-valued functions

+ +

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: + +

+CREATE TABLE generate_series(
+  value,
+  start HIDDEN,
+  stop HIDDEN,
+  step HIDDEN
+);
+
+ +

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: + +

+SELECT value FROM generate_series(5,50);
+
+ +

The previous query is equivalent to the following: + +

+SELECT value FROM generate_series WHERE start=5 AND stop=50;
+
+ +

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. + + + + +

2.2 The xConnect Method

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

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

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

The xCreate and xConnect methods are only different when the +virtual table has some kind of backing store that must be initialized +the first time the virtual table is created. The xCreate method creates +and initializes the backing store. The xConnect method just connects +to an existing backing store. 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. + + + +

2.3 The xBestIndex Method

+ +

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*);
+
+ +

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: + +

+  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 */
+  };
+
+ +

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: + +

+  #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 */
+
+ +

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. + +

2.3.1 Inputs

+ +

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 + +

+ column OP EXPR +
+ +

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: + +

+     a = 5
+
+ +

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: + +

+     x BETWEEN 10 AND 100 AND 999>y
+
+ +

The query optimizer might translate this into three separate constraints: + +

+     x >= 10
+     x <= 100
+     y < 999
+
+ +

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. + +

2.3.2 Outputs

+ +

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. + + + +

2.4 The xDisconnect Method

+ +
+  int (*xDisconnect)(sqlite3_vtab *pVTab);
+
+ +

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. + + + +

2.5 The xDestroy Method

+ +
+  int (*xDestroy)(sqlite3_vtab *pVTab);
+
+ +

This method releases a connection to a virtual table, just like +the xDisconnect method, and it also destroys the underlying +table implementation. This method 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. + + + +

2.6 The xOpen Method

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

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. + + + +

2.7 The xClose Method

+ +
+  int (*xClose)(sqlite3_vtab_cursor*);
+
+ +

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. + + + +

2.8 The xEof Method

+ +
+  int (*xEof)(sqlite3_vtab_cursor*);
+
+ +

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. + + + +

2.9 The xFilter Method

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

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. + + + +

2.10 The xNext Method

+ +
+  int (*xNext)(sqlite3_vtab_cursor*);
+
+ +

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. + + + +

2.11 The xColumn Method

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

The SQLite core invokes this method in order to find the value for +the N-th column of the current row. N is zero-based so the first column +is numbered 0. +The xColumn method may return its result back to SQLite using one of the +following interface: + +

+

+

+ +

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/vtab.tcl Index: Doc/vtab.tcl ================================================================== --- Doc/vtab.tcl +++ Doc/vtab.tcl @@ -0,0 +1,184 @@ +############################################################################### +# +# 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 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 } { + if {[string length [string trim $line]] == 0 || \ + [regexp -- {)} [string range $line 0 3]]} then { + return "" + } + + set result $line + + foreach remove [list \ + {} {} {} {} {} \ + {
} {
} {
} {
} {
} {
} {
  • } \ + {
  • } {
      } {
    } {

    } {

    } {}] { + regsub -all -- $remove $result "" result + + if {[string length [string trim $result]] == 0} then { + puts "STOP with no content, original line = $line" + return "" + } + } + + regsub -all -- {
    } $result \n result + regsub -all -- {≠} $result {\≠} result + regsub -all -- {[(?:;)?} $result {[} result + regsub -all -- {](?:;)?} $result {]} result + regsub -all -- {<( |\"|\d|=)} $result {\<\1} result + regsub -all -- {( |\"|\d|=)>} $result {\1\>} result + regsub -all -- {
    } $result  result
    +  regsub -all -- {
    } $result result + regsub -all -- {
    } $result result + regsub -all -- {
    } $result result + + return $result +} + +proc extractMethod { name lines pattern indexVarName methodsVarName } { + upvar 1 $indexVarName index + upvar 1 $methodsVarName methods + + set paragraph 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 {$paragraph > 0 && [string length $trimLine] == 0} then { + # blank line, close paragraph. + append data \n ; incr paragraph -1 + } elseif {[string range $trimLine 0 2] eq "

    "} then { + # open paragraph ... maybe one line? + if {[string range $trimLine end-3 end] eq "

    "} then { + set newLine [processLine $line] + + if {[string length $newLine] > 0} then { + # one line paragraph, wrap. + if {[info exists methods($name)]} then { + append data \n + } + + append data \n $newLine \n + } + } else { + if {[info exists methods($name)]} then { + append data \n + } + + append data + + set newLine [processLine $line] + + if {[string length $newLine] > 0} then { + # open paragraph, add line to it. + append data $newLine + } + + incr paragraph + } + } else { + set newLine [processLine $line] + + if {[string length $newLine] > 0} then { + if {[info exists methods($name)]} then { + append data \n + } + + append data $newLine + } + } + + if {[string length $data] > 0} then { + append methods($name) $data + } + + incr index; # consume this line for outer loop. + } + } +} + +# +# 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 interfacePath [file join [file dirname $path] System.Data.SQLite] + +set inputFileName [file join $coreDocPath vtab.html] +set outputFileName [file join $interfacePath ISQLiteNativeModule.cs] + +set methodPattern {^

    2\.\d+ The (.*) Method(?:s)?

    $} +set start false +array set methods {} +set lines [split [readFile $inputFileName] \n] +set length [llength $lines] + +for {set index 0} {$index < $length} {} { + set line [lindex $lines $index] + + if {$start} then { + if {[regexp -- {^$} $line]} then { + incr index; continue + } + + if {[regexp -- $methodPattern $line dummy capture]} then { + foreach method [englishToList $capture] { + set methodIndex [expr {$index + 1}] + extractMethod $method $lines $methodPattern 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 + } +} + +# " /// " +# " /// " +# exit 0