Documentation Source Text

Check-in [9b1f04e2d1]
Login

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:Improved documentation for table-valued functions and eponymous virtual tables.
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 9b1f04e2d132f42543a2b141586fcccc1efd25e2
User & Date: drh 2015-09-08 21:33:54
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
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to pages/changes.in.

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







|







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
19
20
21
22
23
24
25
26
27
28
..
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
..
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
<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>
................................................................................
<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
................................................................................
  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

);
</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
................................................................................
<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", use:


<blockquote><pre>
CREATE VIRTUAL TABLE IF NOT EXISTS temp.stat USING dbstat;
SELECT count(*) FROM stat 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.  


<blockquote><pre>
CREATE VIRTUAL TABLE IF NOT EXISTS temp.stat USING dbstat;
SELECT sum(pgsize-unused)/sum(pgsize) FROM stat WHERE name='xyz';
</pre></blockquote>

<p>
To find the average fan-out for a table, run:

<blockquote><pre>
CREATE VIRTUAL TABLE IF NOT EXISTS temp.stat USING dbstat;
SELECT avg(ncell) FROM stat 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 VIRTUAL TABLE IF NOT EXISTS temp.stat USING dbstat;
CREATE TEMP TABLE s(rowid INTEGER PRIMARY KEY, pageno INT);
INSERT INTO s(pageno) SELECT pageno FROM stat 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>







|

|







 







|

|
|
>
>
>







 







|
>







 







|
>


<
|






|
>


<
|






<
|











<

|





12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
..
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
...
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
<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>
................................................................................
<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
................................................................................
<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
4392
4393
4394
4395
4396
4397
4398
4399
4400
4401
4402
4403
4404
4405
4406
4407
4408
4409
4410
4411
4412
4413
4414
4415
4416
4417
4418
4419
4420
4421
4422
4423
4424
4425
4426
4427
4428

4429
4430
4431
4432
4433
4434
4435
[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.

<p>For example, the "generate_series" extension (located in the
ext/misc/series.c file in source tree) implements a [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.  For example, to list all integers between 5 and 50:

<blockquote><pre>
SELECT value FROM generate_series(5,50);
</pre></blockquote>

<p>This query is logically 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>
##############################################################################
Section UPDATE update {UPDATE *UPDATEs}

RecursiveBubbleDiagram update-stmt
</tcl>







|

|
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
>







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
...
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
...
439
440
441
442
443
444
445
446

447
448
449
450
451
452
453



































454
455
456
457
458
459
460
<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.)
................................................................................
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.
................................................................................
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}</tcl>

<h4>2.1.2 Hidden columns used to implement table-valued functions</h4>

<p>Hidden columns in virtual tables can also be used to implement
[table-valued functions in the FROM clause] of a [SELECT] statement.
When a virtual table with hidden columns appears in the FROM clause of
a SELECT statement, the table name can be followed by a parenthesized
list of expresssion which become constraints on the hidden columns.





































<tcl>############################################################# xConnect
hd_fragment xconnect {sqlite3_module.xConnect} {xConnect}</tcl>
<h3>2.2 The xConnect Method</h3>

<blockquote><pre>







>
>







 







|
|



|







 







|
>
|

<
|
|
|
|
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>







101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
...
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
...
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
<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.)
................................................................................
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.
................................................................................
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>