Documentation Source Text

Check-in [64af4ea91f]

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

Comment:Add documentation for the UNION virtual table.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA3-256: 64af4ea91f78285b138c6b3a76ff7748e68eb3ffefca74ea624e12bd2bac9013
User & Date: drh 2017-07-21 19:12:49
Add the enhanced ability for date/time functions to be deterministic to the change log and to the "deterministic function" documentation. (check-in: e27224b526 user: drh tags: trunk)
Add documentation for the UNION virtual table. (check-in: 64af4ea91f user: drh tags: trunk)
Update the size and performance graph. (check-in: d35ef36eeb user: drh tags: trunk)
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to pages/

    17     17   proc chng {date desc {options {}}} {
    18     18     global nChng aChng xrefChng
    19     19     set aChng($nChng) [list $date $desc $options]
    20     20     set xrefChng($date) $nChng
    21     21     incr nChng
    22     22   }
    23     23   
    24         -chng {2017-07-20 (3.20.0)} {
           24  +chng {2017-08-01 (3.20.0)} {
    25     25   <li> Update the text of error messages returned by [sqlite3_errmsg()] for some
    26     26        error codes.
    27     27   <li> Add new interfaces [sqlite3_bind_pointer()], [sqlite3_result_pointer()], and
    28     28        [sqlite3_value_pointer()].
    29     29   <li> Backwards-incompatible changes to some extensions in order to take 
    30     30        advantage of the improved security offered by the new [sqlite3_bind_pointer()]
    31     31        interface:
    34     34             the fts5_api pointer.
    35     35        <li> [carray(PTR,N)] &rarr; requires [sqlite3_bind_pointer()] to set the PTR parameter.
    36     36        <li> [|remember(V,PTR)]
    37     37             &rarr; requires [sqlite3_bind_pointer()] to set the PTR parameter.
    38     38        </ul>
    39     39   <li> Added the [SQLITE_STMT virtual table] extension.
    40     40   <li> Added the [COMPLETION extension] - designed to suggest
           41  +<li> Added the [UNION virtual table] extension.
    41     42        tab-completions for interactive user interfaces.  This is a work in progress.
    42     43        Expect further enhancements in future releases.
    43     44   <li> Added the [sqlite3_prepare_v3()] and [sqlite3_prepare16_v3()] interfaces
    44     45        with the extra "prepFlags" parameters.
    45     46   <li> Provide the [SQLITE_PREPARE_PERSISTENT] flag [sqlite3_prepare_v3()] and
    46     47        use it to limit [lookaside memory] misuse by [FTS3], [FTS5], and the
    47     48        [R-Tree extension].

Added pages/

            1  +<title>The UNION Virtual Table</title>
            2  +<tcl>hd_keywords unionvtab {UNION virtual table} {union-vtab}</tcl>
            3  +<fancy_format>
            4  +<!--<h1 align='center'>
            5  +The UNION Virtual Table
            6  +</h1>-->
            7  +
            8  +<ol>
            9  +<li value=100><p>
           10  +The UNION virtual table (hereafter: "union-vtab")
           11  +is a [virtual table] that makes multiple independent
           12  +[rowid tables] tables look like a single large table.
           13  +
           14  +<li value=110><p>
           15  +The tables that participate in a union-vtab can be in the same 
           16  +database file, or they can be in separate databases files that
           17  +are [ATTACH]-ed to the same database connection.
           18  +
           19  +<li value=120><p>
           20  +The union-vtab is not built into SQLite.
           21  +Union-vtab is a [loadable extension].
           22  +The source code for union-vtab is contained in a single file located at
           23  +[|ext/misc/unionvtab.c]
           24  +in the SQLite source tree.
           25  +
           26  +<li value=200><p>
           27  +A new union-vtab instance is created as follows:
           28  +
           29  +<blockquote>
           30  +<b>CREATE VIRTUAL TABLE temp.</b><i>tabname</i> <b>USING unionvtab(</b><i>query</i><b>);</b>
           31  +</blockquote>
           32  +
           33  +<li value=210><p>
           34  +Every union-vtab must be in the TEMP namespace.  Hence, the "<b>temp.</b>"
           35  +prior to <i>tabname</i> is required.  Only the union-vtab itself is required
           36  +to be in the TEMP namespace - the individual tables that are being unioned
           37  +can be any [ATTACH]-ed database.
           38  +
           39  +<li value=220><p>
           40  +The <i>query</i> in the CREATE VIRTUAL TABLE statement for a union-vtab 
           41  +must be a well-formed SQL query that returns four columns and an 
           42  +arbitrary number of rows.  Each row in the result of the <i>query</i>
           43  +represents a single table that is to participate in the union.
           44  +<ol>
           45  +<li value=221'>
           46  +The first column is the schema name for the database that contains
           47  +the tables.  Examples:  "main", "zone512".
           48  +<li value=222'>
           49  +The second column is the name of the table.
           50  +<li value=223'>
           51  +The third column  is the minimum value for any rowid in the table.
           52  +<li value=224'>
           53  +The fourth column is the maximum value of any rowid in the table.
           54  +</ol>
           55  +
           56  +<li value=230><p>
           57  +The <i>query</i> for the CREATE VIRTUAL TABLE statement of a union-vtab
           58  +can be either a [SELECT] statement or a [VALUES clause].
           59  +
           60  +<li value=240><p>
           61  +The <i>query</i> is run once when the CREATE VIRTUAL TABLE statement is
           62  +first encountered and the results of that one run are used for all subsequent
           63  +access to the union-vtab.  If the results of <i>query</i> change, then
           64  +the union-vtab should be [DROP TABLE|DROP]-ed and recreated in order
           65  +to cause the <i>query</i> to be run again.
           66  +
           67  +<li value=250><p>
           68  +There must be no overlap in the bands of rowids for the various tables
           69  +in a union-vtab.
           70  +
           71  +<li value=260><p>
           72  +All tables that participate in a union-vtab must have identical
           73  +CREATE TABLE definitions, except that the names of the tables can be different.
           74  +
           75  +<li value=270><p>
           76  +All tables that participate in a union-vtab must be [rowid tables].
           77  +
           78  +<li value=280><p>
           79  +The column names and definitions for <i>tabname</i> will be the same as
           80  +the underlying tables.  An application can access <i>tabname</i> just like
           81  +it was one of the real underlying tables.
           82  +
           83  +<li value=290><p>
           84  +No table in a union-vtab may contain entries that are outside of the
           85  +rowid bounds established by the <i>query</i> in the CREATE VIRTUAL TABLE
           86  +statement.
           87  +
           88  +<li value=300><p>
           89  +The union-vtab shall optimize access to the underlying real tables
           90  +when the constraints on the query are among forms shown below.
           91  +Other kinds of constraints may be optimized in the future, but only
           92  +these constraints are optimized in the initial implementation.
           93  +<ul>
           94  +<li> <b>rowid=$id</b>
           95  +<li> <b>rowid IN</b> <i>query-or-list</i>
           96  +<li> <b>rowid BETWEEN $lwr AND $upr</b>
           97  +</ul>
           98  +<p>
           99  +Other kinds of constraints may be used and will work, but other
          100  +constraints will be checked individually for each row and will not
          101  +be optimized (at least not initially). 
          102  +All constraint checking is completely automatic regardless of whether
          103  +or not optimization occurs.  The optimization referred to in this bullet point
          104  +is a performance consideration only.  The same result is obtained
          105  +regardless of whether or not the query is optimized.
          106  +
          107  +<li value=310><p>
          108  +The union-vtab is read-only.  Support for writing may be added at a later
          109  +time, but writing is not a part of the initial implementation.
          110  +
          111  +<li value=320><p>
          112  +<i>Nota bene:</i>
          113  +The [sqlite3_blob_open()] interface does <u>not</u> work for a union-vtab.
          114  +BLOB content must be read from the union-vtab using ordinary SQL statements.
          115  +</ol>