Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | Improved documentation for table-valued functions and eponymous virtual tables. |
---|---|
Downloads: | Tarball | ZIP archive |
Timelines: | family | ancestors | descendants | both | trunk |
Files: | files | file ages | folders |
SHA1: |
9b1f04e2d132f42543a2b141586fcccc |
User & Date: | drh 2015-09-08 21:33:54.954 |
Context
2015-09-09
| ||
02:04 | First draft of json1 documentation. (check-in: ea6f64590b user: drh tags: trunk) | |
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) | |
Changes
Changes to pages/changes.in.
︙ | ︙ | |||
19 20 21 22 23 24 25 | } 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]. | | | 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 | } 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 |
︙ | ︙ |
Changes to pages/dbstat.in.
︙ | ︙ | |||
12 13 14 15 16 17 18 | <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: | | | | | | > > > | > | 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 60 61 62 63 64 65 66 67 68 | <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><pre> SELECT * FROM dbstat; </pre></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 default schema for which information is to be provided. The default is "main", and so the use of "main" in the example above is redundant. For any particular query, the schema can be changed by specifying the alternative schema as a function argument to the virtual table name in the FROM clause of the query. (See further discussion of [table-valued functions in the FROM clause] for more details.) <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 pageno INTEGER, -- Page number pagetype STRING, -- 'internal', 'leaf' or 'overflow' ncell INTEGER, -- Cells on page (0 for overflow) payload INTEGER, -- Bytes of payload on this page unused INTEGER, -- Bytes of unused space on this page mx_payload INTEGER, -- Largest payload size of all cells on this page pgoffset INTEGER, -- Offset of page in file pgsize INTEGER, -- Size of the page schema TEXT HIDDEN -- The database being analyzed ); </pre></blockquote> <p> There is a single row of the dbstat table for each page in the database file. Freelist pages, the lock page, and pointer-map pages of the database file do not appear in the |
︙ | ︙ | |||
98 99 100 101 102 103 104 | <blockquote><pre> '/1c2/000/' // Left-most child of 451st child of root </pre></blockquote> <h3>Example uses of the dbstat virtual table</h3> <p> | | > < | | > < | < | < | | 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 | <blockquote><pre> '/1c2/000/' // Left-most child of 451st child of root </pre></blockquote> <h3>Example uses of the dbstat virtual table</h3> <p> To find the total number of pages used to store table "xyz" in schema "aux1", use: <blockquote><pre> SELECT count(*) FROM dbstat('aux1') WHERE name='xyz'; </pre></blockquote> <p> To see how efficiently the content of a table is stored on disk, compute the amount of space used to hold actual content divided by the total amount of disk space used. The closer this number is to 100%, the more efficient the packing. (In this example, the 'xyz' table is assumed to be in the 'main' schema.) <blockquote><pre> SELECT sum(pgsize-unused)/sum(pgsize) FROM dbstat WHERE name='xyz'; </pre></blockquote> <p> To find the average fan-out for a table, run: <blockquote><pre> SELECT avg(ncell) FROM dbstat WHERE name='xyz' AND pagetype='internal'; </pre></blockquote> <p> Modern filesystems operate faster when disk accesses are sequential. Hence, SQLite will run faster if the content of the database file is on sequential pages. To find out what fraction of the pages in a database are sequential (and thus obtain a measurement that might be useful in determining when to [VACUUM]), run a query like the following: <blockquote><pre> CREATE TEMP TABLE s(rowid INTEGER PRIMARY KEY, pageno INT); INSERT INTO s(pageno) SELECT pageno FROM dbstat ORDER BY path; SELECT sum(s1.pageno+1==s2.pageno)*1.0/count(*) FROM s AS s1, s AS s2 WHERE s1.rowid+1=s2.rowid; DROP TABLE s; </pre></blockquote> |
Changes to pages/lang.in.
︙ | ︙ | |||
4385 4386 4387 4388 4389 4390 4391 | [WITH clause] that defines one or more [common table expressions] for use within the SELECT statement. <tcl>hd_fragment tabfunc1 {table-valued functions in the FROM clause}</tcl> <h3>Table-valued Functions In The FROM Clause</h3> <p>A [virtual table] that contains [hidden columns] can be used like | | | | < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < | 4385 4386 4387 4388 4389 4390 4391 4392 4393 4394 4395 4396 4397 4398 4399 4400 4401 4402 | [WITH clause] that defines one or more [common table expressions] for use within the SELECT statement. <tcl>hd_fragment tabfunc1 {table-valued functions in the FROM clause}</tcl> <h3>Table-valued Functions In The FROM Clause</h3> <p>A [virtual table] that contains [hidden columns] can be used like a [table-valued function] in the FROM clause. The arguments to the table-valued function become constraints on the HIDDEN columns of the virtual table. Additional information can be found in the [table-valued functions|virtual table documentation]. <tcl> ############################################################################## Section UPDATE update {UPDATE *UPDATEs} RecursiveBubbleDiagram update-stmt </tcl> |
︙ | ︙ |
Changes to pages/vtab.in.
︙ | ︙ | |||
101 102 103 104 105 106 107 108 109 110 111 112 113 114 | <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.) | > > | 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 | <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. Eponymous virtual tables exist in the "main" schema only, so they will not work if prefixed with a different schema name. <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.) |
︙ | ︙ | |||
122 123 124 125 126 127 128 | 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> | | | | | 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 | 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> <h5>1.1.2.1 Eponymous-only virtual tables</h5> <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]. <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. |
︙ | ︙ | |||
439 440 441 442 443 444 445 | and with datatypes of "VARCHAR(12)" and "INTEGER". <p>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. | | > | > > > > | > > > | > > > > > > > > | > > > > > | > > > | > > > > > > > > > > > | 441 442 443 444 445 446 447 448 449 450 451 452 453 454 455 456 457 458 459 460 461 462 463 464 465 466 467 468 469 470 471 472 473 474 475 476 477 478 479 480 481 482 483 484 485 486 487 488 489 490 491 492 493 494 495 496 497 | and with datatypes of "VARCHAR(12)" and "INTEGER". <p>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. <tcl>hd_fragment tabfunc2 {table-valued functions} \ {table-valued function}</tcl> <h4>2.1.2 Table-valued functions</h4> <p>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. <p>For example, the "generate_series" extension (located in the ext/misc/series.c file in source tree) implements an [eponymous virtual table] with the following schema: <blockquote><pre> CREATE TABLE generate_series( value, start HIDDEN, stop HIDDEN, step HIDDEN ); </pre></blockquote> <p>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 unconstrainted columns. For example, to list all integers between 5 and 50: <blockquote><pre> SELECT value FROM generate_series(5,50); </pre></blockquote> <p>The previous query is equivalent to the following: <blockquote><pre> SELECT value FROM generate_series WHERE start=5 AND stop=50; </pre></blockquote> <p>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. <tcl>############################################################# xConnect hd_fragment xconnect {sqlite3_module.xConnect} {xConnect}</tcl> <h3>2.2 The xConnect Method</h3> <blockquote><pre> |
︙ | ︙ |