Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | Updates to the change log for 3.8.12. Add documentation for eponymous virtual tables, for table-valued functions, and other new capabilities. |
---|---|
Downloads: | Tarball | ZIP archive |
Timelines: | family | ancestors | descendants | both | trunk |
Files: | files | file ages | folders |
SHA1: |
456508e2ed1ea52581bd378c4be2762a |
User & Date: | drh 2015-09-08 18:41:02.127 |
Context
2015-09-08
| ||
21:33 | Improved documentation for table-valued functions and eponymous virtual tables. (check-in: 9b1f04e2d1 user: drh tags: trunk) | |
18:41 | Updates to the change log for 3.8.12. Add documentation for eponymous virtual tables, for table-valued functions, and other new capabilities. (check-in: 456508e2ed user: drh tags: trunk) | |
16:25 | Update language documentation to begin describing new features: (1) indexes on expressions (2) table-valued functions (3) column names on VIEWs. (check-in: d9a422f858 user: drh tags: trunk) | |
Changes
Changes to pages/changes.in.
︙ | ︙ | |||
15 16 17 18 19 20 21 | proc chng {date desc {options {}}} { global nChng aChng set aChng($nChng) [list $date $desc $options] incr nChng } chng {2015-11-01 (3.8.12)} { | | | > > > > > > > > > > > > > > > > > > > > > | 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 | proc chng {date desc {options {}}} { global nChng aChng set aChng($nChng) [list $date $desc $options] incr nChng } chng {2015-11-01 (3.8.12)} { <p><b>New Features And Enhancements:</b> <li>The [CREATE VIEW] statement now accepts an optional list of column names following the view name. <li>Added support for [indexes on expressions]. <li>Added support for [table-valued functions in the FROM clause] of a [SELECT] statement. <li>Added support for [eponymous virtual tables]. <li>Added the ext/misc/json1.c extension module in the source tree. <li>A [VIEW] may now reference undefined tables and functions when initially created. Missing tables and functions are reported when the VIEW is used in a query. <li>The query planner is now able to use [partial indexes] that contain AND-connected terms in the WHERE clause. <li>The sqlite3_analyzer.exe utility is updated to report the depth of each btree and to show the average fanout for indexes and WITHOUT ROWID tables. <p><b>Other changes:</b> <li>The [sqlite3_memory_alarm()] interface, which has been deprecated and undocumented for 8 years, is changed into a no-op. <p><b>Fixes:</b> <li>Added a memory barrier in the implementation of [sqlite3_initialize()] to help ensure that it is thread-safe. <li>Fix the [OR optimization] so that it always ignores subplans that do not use an index. <li>Do not apply the WHERE-clause pushdown optimization on terms that originate in the ON or USING clause of a LEFT JOIN. Fix for ticket [https://www.sqlite.org/src/info/c2a19d81652f40568c|c2a19d81652f40568c]. } chng {2015-07-29 (3.8.11.1)} { <li>Restore an undocumented side-effect of [PRAGMA cache_size]: force the database schema to be parsed if the database has not been previously accessed. <li>Fix a long-standing problem in [sqlite3_changes()] for [WITHOUT ROWID] tables that was reported a few hours after the 3.8.11 release. |
︙ | ︙ |
Changes to pages/dbstat.in.
1 2 3 4 5 6 7 8 9 10 | <title>The DBSTAT Virtual Table</title> <tcl>hd_keywords dbstat {dbstat virtual table}</tcl> <h2>The DBSTAT Virtual Table</h2> <p> ^The <b>dbstat</b> virtual table is available on all [database connections] when SQLite is built using the [SQLITE_ENABLE_DBSTAT_VTAB] compile-time option. ^The dbstat virtual table provides low-level information about btree and overflow pages in a database file. | > > | > > > > | > > > > | | | > | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 | <title>The DBSTAT Virtual Table</title> <tcl>hd_keywords dbstat {dbstat virtual table}</tcl> <h2>The DBSTAT Virtual Table</h2> <p> ^The <b>dbstat</b> virtual table is available on all [database connections] when SQLite is built using the [SQLITE_ENABLE_DBSTAT_VTAB] compile-time option. ^The dbstat virtual table provides low-level information about btree and overflow pages in a database file. <p> ^The dbstat virtual table is an [eponymous virtual table], meaning that is not necessary to run [CREATE VIRTUAL TABLE] to create an instance of the dbstat virtual table before using it. The "dbstat" module name can be used as if it were a table name to query the dbstat virtual table directly. For example: <blockquote> SELECT * FROM dbstat; </blockquote> <p> If a named virtual table that uses the dbstat module is desired, then the recommanded way to create an instance of the dbstat virtual table is as follows: <blockquote><pre> CREATE VIRTUAL TABLE temp.stat USING dbstat(main); </pre></blockquote> <p> Note the "temp." qualifier before the virtual table name ("stat"). This qualifier causes the virtual table to be temporary - to only exist for the duration of the current database connection. This is the recommended approach. <p> The "main" argument to dbstat is the named of the schema for which information is to be provided. The default is "main", and so the use of "main" in the example above is redundant. When using the eponymous dbstat virtual table, the schema is always "main". <p> The schema for the dbstat virtual table is this: <blockquote><pre> CREATE TABLE dbstat( name STRING, -- Name of table or index path INTEGER, -- Path to page from root |
︙ | ︙ |
Changes to pages/lang.in.
︙ | ︙ | |||
1378 1379 1380 1381 1382 1383 1384 | <p>Failure to specify the schema name on the target table could result in the TEMP trigger being reattached to a table with the same name in another database whenever any schema change occurs.</p> <tcl> ############################################################################### | | | 1378 1379 1380 1381 1382 1383 1384 1385 1386 1387 1388 1389 1390 1391 1392 | <p>Failure to specify the schema name on the target table could result in the TEMP trigger being reattached to a table with the same name in another database whenever any schema change occurs.</p> <tcl> ############################################################################### Section {CREATE VIEW} {createview} {{CREATE VIEW} view VIEW *views} RecursiveBubbleDiagram create-view-stmt </tcl> <p>^The CREATE VIEW command assigns a name to a pre-packaged [SELECT] statement. ^Once the view is created, it can be used in the FROM clause |
︙ | ︙ |
Changes to pages/vtab.in.
1 2 3 4 5 6 7 8 9 10 | <title>The Virtual Table Mechanism Of SQLite</title> <h1 align="center">The Virtual Table Mechanism Of SQLite</h1> <tcl>hd_keywords {virtual table} {virtual tables}</tcl> <h2>1.0 Introduction</h2> <p>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. | | | | | | > > | | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 | <title>The Virtual Table Mechanism Of SQLite</title> <h1 align="center">The Virtual Table Mechanism Of SQLite</h1> <tcl>hd_keywords {virtual table} {virtual tables}</tcl> <h2>1.0 Introduction</h2> <p>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. <p>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: <p> <ul> <li> One cannot create a trigger on a virtual table. <li> 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.) <li> One cannot run [ALTER TABLE | ALTER TABLE ... ADD COLUMN] commands against a virtual table. </ul> <p>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. <p>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. <p>Here are some existing and postulated uses for virtual tables: <ul> <li> A [full-text search] interface <li> Spatial indices using [R-Trees] <li> Introspect the disk content of an SQLite database file (the [dbstat virtual table]) <li> Read and/or write the content of a comma-separated value (CSV) file <li> Access the filesystem of the host computer as if it were a database table <li> Enabling SQL manipulation of data in statistics packages like R </ul> <h3>1.1 Usage</h3> <p>A virtual table is created using a [CREATE VIRTUAL TABLE] statement. This statement creates a table with a particular name and associates the |
︙ | ︙ | |||
81 82 83 84 85 86 87 | arguments. <p>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. | > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | | 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 | arguments. <p>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. <h4>1.1.1 Temporary virtual tables</h4> <p>There is no "CREATE TEMP VIRTUAL TABLE" statement. To create a temporary virtual table, add the "temp" schema name before the tablename: <blockcuqote><pre> CREATE VIRTUAL TABLE <b>temp.</b>tablename USING module(arg1, ...); </pre></blockquote> <tcl>hd_fragment epovtab {eponymous virtual tables} \ {eponymous virtual table}</tcl> <h4>1.1.2 Eponymous virtual tables</h4> <p>Some virtual tables exist automatically in every schema 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. <p>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.) <blockquote><pre> SELECT * FROM dbstat; </pre></blockquote> <p>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. <tcl>hd_fragment epoonlyvtab {eponymous-only virtual table}</tcl> <p> 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 in the FROM clause] of a [SELECT] statement. <p> Note that SQLite versions prior to 3.8.12 did not check the xCreate method for NULL before to 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. <h3>1.2 Implementation</h3> <p>Several new C-level objects are used by the virtual table implementation: <blockquote><pre> typedef struct sqlite3_vtab sqlite3_vtab; typedef struct sqlite3_index_info sqlite3_index_info; typedef struct sqlite3_vtab_cursor sqlite3_vtab_cursor; |
︙ | ︙ | |||
336 337 338 339 340 341 342 | 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. | > > > > > > > > | | > > | | > | 391 392 393 394 395 396 397 398 399 400 401 402 403 404 405 406 407 408 409 410 411 412 413 414 415 416 417 418 419 | 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. <p> 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.8.12 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. <p> 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. <tcl>hd_fragment hiddencol {hidden column} {hidden columns}</tcl> <h4>2.1.1 Hidden columns in virtual tables</h4> <p>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. |
︙ | ︙ | |||
407 408 409 410 411 412 413 | connection to an existing virtual table whereas xCreate is called to create a new virtual table from scratch. <p>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 | | > | 473 474 475 476 477 478 479 480 481 482 483 484 485 486 487 488 | connection to an existing virtual table whereas xCreate is called to create a new virtual table from scratch. <p>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]. <p>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. |
︙ | ︙ |