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.
-
There is no "CREATE TEMP VIRTUAL TABLE" statement. To create a
temporary virtual table, add the "temp" schema
before the virtual table name.
@@ -247,11 +258,11 @@
CREATE VIRTUAL TABLE temp.tablename USING module(arg1, ...);
-
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".
@@ -279,11 +290,11 @@
are the same, that indicates that the virtual table has no persistent
state that needs to be created and destroyed.
-
typedef struct sqlite3_vtab sqlite3_vtab;
@@ -442,21 +453,21 @@
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
+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
+1.4. Creating New Virtual Table Implementations
Follow these steps to create your own virtual table:
@@ -477,26 +488,32 @@
for "sqlite3_create_module".
You might also want to implement your new virtual table as a
loadable extension.
-
2.0 Virtual Table Methods
+2. Virtual Table Methods
-2.1 The xCreate Method
+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
+
The xCreate method is called to create a new instance of a virtual table
+in response to a CREATE VIRTUAL TABLE statement.
+If the xCreate method is the same pointer as the xConnect method, then the
+virtual table is an eponymous virtual table.
+If the xCreate method is omitted (if it is a NULL pointer) then the virtual
+table is an eponymous-only virtual table.
+
+
+
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.
@@ -567,11 +584,11 @@
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
+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:
@@ -600,11 +617,11 @@
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
+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.
@@ -643,14 +660,39 @@
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.1.3. WITHOUT ROWID Virtual Tables
+
+Beginning with SQLite version 3.14.0, the CREATE TABLE statement that
+is passed into sqlite3_declare_vtab() may contain a WITHOUT ROWID clause.
+This is useful for cases where the virtual table rows
+cannot easily be mapped into unique integers. A CREATE TABLE
+statement that includes WITHOUT ROWID must define one or more columns as
+the PRIMARY KEY. Every column of the PRIMARY KEY must individually be
+NOT NULL and all columns for each row must be collectively unique.
+
+
Note that SQLite does not enforce the PRIMARY KEY for a WITHOUT ROWID
+virtual table. Enforcement is the responsibility of the underlying
+virtual table implementation. But SQLite does assume that the PRIMARY KEY
+constraint is valid - that the identified columns really are UNIQUE and
+NOT NULL - and it uses that assumption to optimize queries against the
+virtual table.
+
+
The rowid column is not accessible on a
+WITHOUT ROWID virtual table (of course). Furthermore, since the
+xUpdate method depends on having a valid rowid, the xUpdate method
+must be NULL for a WITHOUT ROWID virtual table. That in turn means that
+WITHOUT ROWID virtual tables must be read-only.
+
-
2.2 The xConnect Method
+2.2. The xConnect Method
int (*xConnect)(sqlite3*, void *pAux,
int argc, char **argv,
sqlite3_vtab **ppVTab,
@@ -702,11 +744,11 @@
may point to the same function if the virtual table does not need to
initialize backing store.
-2.3 The xBestIndex Method
+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:
@@ -815,11 +857,11 @@
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
+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
@@ -897,11 +939,11 @@
colUsed is set, that means that one or more columns other than the
first 63 columns are used. If column usage information is needed by the
xFilter method, then the required bits must be encoded into either
the idxNum or idxStr output fields.
-
2.3.2 Outputs
+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
@@ -963,11 +1005,11 @@
is redundant, the xBestFilter method can suppress that double-check by
setting aConstraintUsage[].omit.
-
2.4 The xDisconnect Method
+2.4. The xDisconnect Method
int (*xDisconnect)(sqlite3_vtab *pVTab);
@@ -986,11 +1028,11 @@
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
+2.5. The xDestroy Method
int (*xDestroy)(sqlite3_vtab *pVTab);
@@ -1006,11 +1048,11 @@
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
+2.6. The xOpen Method
int (*xOpen)(sqlite3_vtab *pVTab, sqlite3_vtab_cursor **ppCursor);
@@ -1037,11 +1079,11 @@
The xOpen method is required for every virtual table implementation.
-
2.7 The xClose Method
+2.7. The xClose Method
int (*xClose)(sqlite3_vtab_cursor*);
@@ -1057,11 +1099,11 @@
The xClose method is required for every virtual table implementation.
-
2.8 The xEof Method
+2.8. The xEof Method
int (*xEof)(sqlite3_vtab_cursor*);
@@ -1072,11 +1114,11 @@
The xEof method is required for every virtual table implementation.
-
2.9 The xFilter Method
+2.9. The xFilter Method
int (*xFilter)(sqlite3_vtab_cursor*, int idxNum, const char *idxStr,
int argc, sqlite3_value **argv);
@@ -1107,11 +1149,11 @@
The xFilter method is required for every virtual table implementation.
-
2.10 The xNext Method
+2.10. The xNext Method
int (*xNext)(sqlite3_vtab_cursor*);
@@ -1128,11 +1170,11 @@
The xNext method is required for every virtual table implementation.
-
2.11 The xColumn Method
+2.11. The xColumn Method
int (*xColumn)(sqlite3_vtab_cursor*, sqlite3_context*, int N);
@@ -1166,11 +1208,11 @@
The xColumn method is required for every virtual table implementation.
-
2.12 The xRowid Method
+2.12. The xRowid Method
int (*xRowid)(sqlite3_vtab_cursor *pCur, sqlite_int64 *pRowid);
@@ -1182,11 +1224,11 @@
The xRowid method is required for every virtual table implementation.
-
2.13 The xUpdate Method
+2.13. The xUpdate Method
int (*xUpdate)(
sqlite3_vtab *pVTab,
int argc,
@@ -1280,11 +1322,11 @@
is a NULL pointer, then the virtual table is read-only.
-2.14 The xFindFunction Method
+2.14. The xFindFunction Method
int (*xFindFunction)(
sqlite3_vtab *pVtab,
int nArg,
@@ -1316,11 +1358,11 @@
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
+2.15. The xBegin Method
int (*xBegin)(sqlite3_vtab *pVTab);
@@ -1336,11 +1378,11 @@
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
+2.16. The xSync Method
int (*xSync)(sqlite3_vtab *pVTab);
@@ -1356,11 +1398,11 @@
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
+2.17. The xCommit Method
int (*xCommit)(sqlite3_vtab *pVTab);
@@ -1372,11 +1414,11 @@
xSync.
-2.18 The xRollback Method
+2.18. The xRollback Method
int (*xRollback)(sqlite3_vtab *pVTab);
@@ -1387,11 +1429,11 @@
A call to this method always follows a prior call to xBegin.
-
2.19 The xRename Method
+2.19. The xRename Method
int (*xRename)(sqlite3_vtab *pVtab, const char *zNew);
@@ -1402,11 +1444,11 @@
The xRename method is required for every virtual table implementation.
-
2.20 The xSavepoint, xRelease, and xRollbackTo Methods
+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);
Index: Doc/vtab.tcl
==================================================================
--- Doc/vtab.tcl
+++ Doc/vtab.tcl
@@ -26,10 +26,15 @@
proc escapeSubSpec { data } {
regsub -all -- {&} $data {\\\&} data
regsub -all -- {\\(\d+)} $data {\\\\\1} data
return $data
}
+
+proc removeSectionId { value } {
+ regsub -- { id="section(?:_\d+)+"} $value "" value
+ return $value
+}
proc englishToList { value } {
set result [list]
foreach element [split $value "\t\n ,"] {
@@ -84,11 +89,11 @@
array set levels {p 0}
set length [llength $lines]
while {$index < $length} {
- set line [lindex $lines $index]
+ set line [removeSectionId [lindex $lines $index]]
if {[regexp -- $pattern $line]} then {
break; # stop on this line for outer loop.
} else {
set trimLine [string trim $line]; set data ""
@@ -189,16 +194,17 @@
{*** DRAFT ***} ""] $inputData]
set inputData [string map [list {} ""] $inputData]
set lines [split [string map [list \r\n \n] $inputData] \n]
-set patterns(method) {^2\.\d+ The (.*) Method(?:s)?
$}
+
+set patterns(method) {^2\.\d+\. The (.*) Method(?:s)?
$}
set prefix " /// "
unset -nocomplain methods; set start false
for {set index 0} {$index < [llength $lines]} {} {
- set line [lindex $lines $index]
+ set line [removeSectionId [lindex $lines $index]]
if {$start} then {
if {[regexp -- $patterns(method) $line dummy capture]} then {
foreach method [englishToList $capture] {
set methodIndex [expr {$index + 1}]
@@ -209,11 +215,11 @@
set index $methodIndex
} else {
incr index
}
- } elseif {[regexp -- {^2\.0 Virtual Table Methods
$} $line]} then {
+ } elseif {[regexp -- {^2\. Virtual Table Methods
$} $line]} then {
set start true; incr index
} else {
incr index
}
}
Index: System.Data.SQLite/ISQLiteNativeModule.cs
==================================================================
--- System.Data.SQLite/ISQLiteNativeModule.cs
+++ System.Data.SQLite/ISQLiteNativeModule.cs
@@ -20,12 +20,18 @@
/// 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 xCreate method is called to create a new instance of a virtual table
+ /// in response to a CREATE VIRTUAL TABLE statement.
+ /// If the xCreate method is the same pointer as the xConnect method, then the
+ /// virtual table is an eponymous virtual table.
+ /// If the xCreate method is omitted (if it is a NULL pointer) then the virtual
+ /// table is an eponymous-only virtual table.
+ ///
+ ///
/// 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
@@ -175,10 +181,34 @@
/// 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.
///
+ ///
+ /// Beginning with SQLite version 3.14.0, the CREATE TABLE statement that
+ /// is passed into sqlite3_declare_vtab() may contain a WITHOUT ROWID clause.
+ /// This is useful for cases where the virtual table rows
+ /// cannot easily be mapped into unique integers. A CREATE TABLE
+ /// statement that includes WITHOUT ROWID must define one or more columns as
+ /// the PRIMARY KEY. Every column of the PRIMARY KEY must individually be
+ /// NOT NULL and all columns for each row must be collectively unique.
+ ///
+ ///
+ /// Note that SQLite does not enforce the PRIMARY KEY for a WITHOUT ROWID
+ /// virtual table. Enforcement is the responsibility of the underlying
+ /// virtual table implementation. But SQLite does assume that the PRIMARY KEY
+ /// constraint is valid - that the identified columns really are UNIQUE and
+ /// NOT NULL - and it uses that assumption to optimize queries against the
+ /// virtual table.
+ ///
+ ///
+ /// The rowid column is not accessible on a
+ /// WITHOUT ROWID virtual table (of course). Furthermore, since the
+ /// xUpdate method depends on having a valid rowid, the xUpdate method
+ /// must be NULL for a WITHOUT ROWID virtual table. That in turn means that
+ /// WITHOUT ROWID virtual tables must be read-only.
+ ///
///
///
/// The native database connection handle.
///
///