Documentation Source Text

Check-in [5a67b7f178]
Login

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

Overview
Comment:Updates to talk about the auto-explain changes to the shell.
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 5a67b7f1784a00ce8d8b80e8728ed061db4b83b1
User & Date: drh 2016-02-09 22:53:55.323
Context
2016-02-10
03:48
Merge typo fixes from the 3.10 branch. (check-in: f26e661484 user: drh tags: trunk)
2016-02-09
22:53
Updates to talk about the auto-explain changes to the shell. (check-in: 5a67b7f178 user: drh tags: trunk)
14:46
Merge enhancements from the 3.10.0 branch. (check-in: 35017ac57c user: drh tags: trunk)
Changes
Unified Diff Ignore Whitespace Patch
Changes to pages/changes.in.
30
31
32
33
34
35
36


37


38
39
40
41
42
43
44
    for better durability.  The [SQLITE_EXTRA_DURABLE] compile-time option enables
    [PRAGMA synchronous=EXTRA] by default.
<li>Enhanced the [query planner] so that it is able to use
    a [covering index] as part of the [OR optimization].
<li>Many micro-optimizations, resulting in a library that is both smaller
    and faster than the previous release.
<p><b>Enhancements to the [command-line shell]:</b>


<li>Added the ".vfslist" [dot-commands|dot-command].


<p><b>Enhancements to the [TCL Interface]:</b>
<li>If a database connection is opened with the "-uri 1" option, then
    [URI filenames] are honored by the "backup" and "restore" commands.
<li>Added the "-sourceid" option to the "sqlite3" command.
<p><b>Makefile improvements:</b>
<li>Improved pthreads detection in configure scripts.
<li>Add the ability to do MSVC Windows builds from the [amalgamation tarball].







>
>

>
>







30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
    for better durability.  The [SQLITE_EXTRA_DURABLE] compile-time option enables
    [PRAGMA synchronous=EXTRA] by default.
<li>Enhanced the [query planner] so that it is able to use
    a [covering index] as part of the [OR optimization].
<li>Many micro-optimizations, resulting in a library that is both smaller
    and faster than the previous release.
<p><b>Enhancements to the [command-line shell]:</b>
<li>By default, the shell is now in "auto-explain" mode.  The output of
    [EXPLAIN] commands is automatically formatted.
<li>Added the ".vfslist" [dot-commands|dot-command].
<li>The [SQLITE_ENABLE_EXPLAIN_COMMENTS] compile-time option is now turned
    on by default in the standard builds.
<p><b>Enhancements to the [TCL Interface]:</b>
<li>If a database connection is opened with the "-uri 1" option, then
    [URI filenames] are honored by the "backup" and "restore" commands.
<li>Added the "-sourceid" option to the "sqlite3" command.
<p><b>Makefile improvements:</b>
<li>Improved pthreads detection in configure scripts.
<li>Add the ability to do MSVC Windows builds from the [amalgamation tarball].
Changes to pages/cli.in.
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
.dbinfo ?DB?           Show status information about the database
.dump ?TABLE? ...      Dump the database in an SQL text format
                         If TABLE specified, only dump tables matching
                         LIKE pattern TABLE.
.echo on|off           Turn command echo on or off
.eqp on|off            Enable or disable automatic EXPLAIN QUERY PLAN
.exit                  Exit this program
.explain ?on|off?      Turn output mode suitable for EXPLAIN on or off.
                         With no args, it turns EXPLAIN on.
.fullschema            Show schema and the content of sqlite_stat tables
.headers on|off        Turn display of headers on or off
.help                  Show this message
.import FILE TABLE     Import data from FILE into TABLE
.indexes ?TABLE?       Show names of all indexes
                         If TABLE specified, only show indexes for tables
                         matching LIKE pattern TABLE.







|
<







148
149
150
151
152
153
154
155

156
157
158
159
160
161
162
.dbinfo ?DB?           Show status information about the database
.dump ?TABLE? ...      Dump the database in an SQL text format
                         If TABLE specified, only dump tables matching
                         LIKE pattern TABLE.
.echo on|off           Turn command echo on or off
.eqp on|off            Enable or disable automatic EXPLAIN QUERY PLAN
.exit                  Exit this program
.explain ?on|off|auto? Turn EXPLAIN output mode on or off or to automatic

.fullschema            Show schema and the content of sqlite_stat tables
.headers on|off        Turn display of headers on or off
.help                  Show this message
.import FILE TABLE     Import data from FILE into TABLE
.indexes ?TABLE?       Show names of all indexes
                         If TABLE specified, only show indexes for tables
                         matching LIKE pattern TABLE.
197
198
199
200
201
202
203

204
205
206
207
208
209
210
.tables ?TABLE?        List names of tables
                         If TABLE specified, only list tables matching
                         LIKE pattern TABLE.
.timeout MS            Try opening locked tables for MS milliseconds
.timer on|off          Turn SQL timer on or off
.trace FILE|off        Output each SQL statement as it is run
.vfsinfo ?AUX?         Information about the top-level VFS

.vfsname ?AUX?         Print the name of the VFS stack
.width NUM1 NUM2 ...   Set column widths for "column" mode
                         Negative values right-justify
sqlite> 
}</tcl>

<h3>Rules for "dot-commands"</h3>







>







196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
.tables ?TABLE?        List names of tables
                         If TABLE specified, only list tables matching
                         LIKE pattern TABLE.
.timeout MS            Try opening locked tables for MS milliseconds
.timer on|off          Turn SQL timer on or off
.trace FILE|off        Output each SQL statement as it is run
.vfsinfo ?AUX?         Information about the top-level VFS
.vfslist               List all available VFSes
.vfsname ?AUX?         Print the name of the VFS stack
.width NUM1 NUM2 ...   Set column widths for "column" mode
                         Negative values right-justify
sqlite> 
}</tcl>

<h3>Rules for "dot-commands"</h3>
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375

376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
all of the intervening &lt;TR&gt;s, &lt;TH&gt;s, and &lt;TD&gt;s
are.  The html output mode is envisioned as being useful for
CGI.</p>

<tcl>hd_fragment explain {explain dot-command}</tcl>
<p>The ".explain" dot command can be used to set the output mode
to "column" and to set the column widths to values that are reasonable
for looking at the output of an [EXPLAIN] command.  The EXPLAIN command
is an SQLite-specific SQL extension that is useful for debugging.  If any
regular SQL is prefaced by EXPLAIN, then the SQL command is parsed and
analyzed but is not executed.  Instead, the sequence of virtual machine
instructions that would have been used to execute the SQL command are
returned like a query result.  For example:</p>

<tcl>DisplayCode {

sqlite> (((.explain)))
sqlite> (((explain delete from tbl1 where two<20;)))
addr  opcode         p1    p2    p3    p4             p5  comment      
----  -------------  ----  ----  ----  -------------  --  -------------
0     Trace          0     0     0                    00               
1     Goto           0     18    0                    00               
2     Null           0     1     0                    00  r[1]=NULL    
3     OpenRead       0     2     0     2              00  root=2 iDb=0; tbl1
4     Rewind         0     10    0                    00               
5       Column         0     1     2                    00  r[2]=tbl1.two
6       Ge             3     9     2     (BINARY)       6a  if r[3]>=r[2] goto 10
7       Rowid          0     4     0                    00  r[4]=rowid   
8       RowSetAdd      1     4     0                    00  rowset(1)=r[4]
9     Next           0     7     0                    01               
10    Close          0     0     0                    00               
11    OpenWrite      0     2     0     2              00  root=2 iDb=0; tbl1
12      RowSetRead     1     16    4                    00  r[4]=rowset(1)
13      NotExists      0     12    4     1              00  intkey=r[4]  
14      Delete         0     1     0     tbl1           00               
15    Goto           0     12    0                    00               
16    Close          0     0     0                    00               
17    Halt           0     0     0                    00               
18    Transaction    0     1     0                    00               
19    VerifyCookie   0     1     0                    00               
20    TableLock      0     2     1     tbl1           00  iDb=0 root=2 write=1
21    Integer        20    3     0                    00  r[3]=20      
22    Goto           0     2     0                    00               
}</tcl>

<p>Notice how the shell changes the indentation of some opcodes to
help show the loop structure of the [VDBE] program.

<h3>Writing results to a file</h3>

<p>By default, sqlite3 sends query results to standard output.  You
can change this using the ".output" and ".once" commands.  Just put 
the name of an output file as an argument to .output and all subsequent
query results will be written to that file.  Or use the .once command







|
<
<
<
<
<

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







361
362
363
364
365
366
367
368





369

370
371
372


























373


374
375
376
377
378
379
380
all of the intervening &lt;TR&gt;s, &lt;TH&gt;s, and &lt;TD&gt;s
are.  The html output mode is envisioned as being useful for
CGI.</p>

<tcl>hd_fragment explain {explain dot-command}</tcl>
<p>The ".explain" dot command can be used to set the output mode
to "column" and to set the column widths to values that are reasonable
for looking at the output of an [EXPLAIN] command.







<p>Beginning with [Version 3.11.0], the command-line shell defaults to
"auto-explain" mode, in which the EXPLAIN commands are automatically
detected and the output is automatically formatted.  So the ".explain"


























command has become superfluous.



<h3>Writing results to a file</h3>

<p>By default, sqlite3 sends query results to standard output.  You
can change this using the ".output" and ".once" commands.  Just put 
the name of an output file as an argument to .output and all subsequent
query results will be written to that file.  Or use the .once command