Documentation Source Text

Check-in [e792c53939]
Login

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

Overview
Comment:Update to the CLI documentation.
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA3-256: e792c539398b5a2d1a4affda7dc16e9297cf4611182809a6e471bd2f401521e9
User & Date: drh 2017-06-30 00:18:27
Context
2017-06-30
00:47
Add the "fts5ext.so" target for building an SQLite extension that enables the sqlite3 command-line shell to access the website search database. check-in: 4110cb10fc user: drh tags: trunk
00:18
Update to the CLI documentation. check-in: e792c53939 user: drh tags: trunk
2017-06-29
20:54
Update the speed-size graph spreadsheet check-in: 8b7c30639e user: drh tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to pages/cli.in.

143
144
145
146
147
148
149

150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
...
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
...
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
...
499
500
501
502
503
504
505




506
507
508
509
510
511
512
...
532
533
534
535
536
537
538













539
540
541
542
543
544
545

<tclscript>DisplayCode {
sqlite> (((.help)))
.auth ON|OFF           Show authorizer callbacks
.backup ?DB? FILE      Backup DB (default "main") to FILE
.bail on|off           Stop after hitting an error.  Default OFF
.binary on|off         Turn binary output on or off.  Default OFF

.changes on|off        Show number of rows changed by SQL
.check GLOB            Fail if output since .testcase does not match
.clone NEWDB           Clone data into NEWDB from the existing database
.databases             List names and files of attached databases
.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|full       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 ?--indent? 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
.imposter INDEX TABLE  Create imposter table TABLE on index INDEX
.indexes ?TABLE?       Show names of all indexes
                         If TABLE specified, only show indexes for tables
................................................................................
                         line     One value per line
                         list     Values delimited by "|"
                         quote    Escape answers as for SQL
                         tabs     Tab-separated values
                         tcl      TCL list elements
.nullvalue STRING      Use STRING in place of NULL values
.once FILENAME         Output for the next SQL command only to FILENAME
.open ?--new? ?FILE?   Close existing database and reopen FILE
                         The --new starts with an empty file
.output ?FILENAME?     Send output to FILENAME or stdout
.print STRING...       Print literal STRING
.prompt MAIN CONTINUE  Replace the standard prompts
.quit                  Exit this program
.read FILENAME         Execute SQL in FILENAME
.restore ?DB? FILE     Restore content of DB (default "main") from FILE
.save FILE             Write in-memory database into FILE
................................................................................
<p>The last output mode is "html".  In this mode, sqlite3 writes
the results of the query as an XHTML table.  The beginning
&lt;TABLE&gt; and the ending &lt;/TABLE&gt; are not written, but
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] ([dateof: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.

<tcl>hd_fragment dotoutput</tcl>
<h1>Writing results to a file</h1>

<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
................................................................................
executing the following query:</p>

<tclscript>DisplayCode {
SELECT name FROM sqlite_master 
WHERE type IN ('table','view') AND name NOT LIKE 'sqlite_%'
ORDER BY 1
} </tclscript>





<p>The ".indexes" command works in a similar way to list all of
the indexes. If the ".indexes" command is given an argument which is
the name of a table, then it shows just indexes on that table.

<p>The ".schema" command shows the complete schema for the database,
or for a single table if an optional tablename argument is provided:
................................................................................
<p>The ".schema" command is roughly the same as setting
list mode, then entering the following query:</p>

<tclscript>DisplayCode {
SELECT sql FROM sqlite_master
ORDER BY tbl_name, type DESC, name
} </tclscript>














<p>The ".databases" command shows a list of all databases open in
the current connection.  There will always be at least 2.  The first
one is "main", the original database opened.  The second is "temp",
the database used for temporary tables. There may be additional 
databases listed for databases attached using the ATTACH statement.
The first output column is the name the database is attached with, 







>











<







 







|
|







 







<
<
<
<
<
<
<
<
<
<
<







 







>
>
>
>







 







>
>
>
>
>
>
>
>
>
>
>
>
>







143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161

162
163
164
165
166
167
168
...
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
...
393
394
395
396
397
398
399











400
401
402
403
404
405
406
...
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
...
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551

<tclscript>DisplayCode {
sqlite> (((.help)))
.auth ON|OFF           Show authorizer callbacks
.backup ?DB? FILE      Backup DB (default "main") to FILE
.bail on|off           Stop after hitting an error.  Default OFF
.binary on|off         Turn binary output on or off.  Default OFF
.cd DIRECTORY          Change the working directory to DIRECTORY
.changes on|off        Show number of rows changed by SQL
.check GLOB            Fail if output since .testcase does not match
.clone NEWDB           Clone data into NEWDB from the existing database
.databases             List names and files of attached databases
.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|full       Enable or disable automatic EXPLAIN QUERY PLAN
.exit                  Exit this program

.fullschema ?--indent? 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
.imposter INDEX TABLE  Create imposter table TABLE on index INDEX
.indexes ?TABLE?       Show names of all indexes
                         If TABLE specified, only show indexes for tables
................................................................................
                         line     One value per line
                         list     Values delimited by "|"
                         quote    Escape answers as for SQL
                         tabs     Tab-separated values
                         tcl      TCL list elements
.nullvalue STRING      Use STRING in place of NULL values
.once FILENAME         Output for the next SQL command only to FILENAME
.open ?OPTIONS? ?FILE? Close existing database and reopen FILE
                         The --new option starts with an empty file
.output ?FILENAME?     Send output to FILENAME or stdout
.print STRING...       Print literal STRING
.prompt MAIN CONTINUE  Replace the standard prompts
.quit                  Exit this program
.read FILENAME         Execute SQL in FILENAME
.restore ?DB? FILE     Restore content of DB (default "main") from FILE
.save FILE             Write in-memory database into FILE
................................................................................
<p>The last output mode is "html".  In this mode, sqlite3 writes
the results of the query as an XHTML table.  The beginning
&lt;TABLE&gt; and the ending &lt;/TABLE&gt; are not written, but
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 dotoutput</tcl>
<h1>Writing results to a file</h1>

<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
................................................................................
executing the following query:</p>

<tclscript>DisplayCode {
SELECT name FROM sqlite_master 
WHERE type IN ('table','view') AND name NOT LIKE 'sqlite_%'
ORDER BY 1
} </tclscript>

<p>But the ".tables" command does more.  It queries the sqlite_master table
for all [attached] databases, not just the primary database.  And it arranges
its output into neat columns.

<p>The ".indexes" command works in a similar way to list all of
the indexes. If the ".indexes" command is given an argument which is
the name of a table, then it shows just indexes on that table.

<p>The ".schema" command shows the complete schema for the database,
or for a single table if an optional tablename argument is provided:
................................................................................
<p>The ".schema" command is roughly the same as setting
list mode, then entering the following query:</p>

<tclscript>DisplayCode {
SELECT sql FROM sqlite_master
ORDER BY tbl_name, type DESC, name
} </tclscript>

<p>As with ".tables", the ".schema" command shows the schema for
all [attached] databases.  If you only want to see the schema for
a single database (perhaps "main") then you can add an argument
to ".schema" to restrict its output:

<tclscript>DisplayCode {
sqlite> (((.schema main.*)))
}</tclscript>

<p>The ".schema" command can be augmented with the "--indent" option,
in which case it tries to reformat the various CREATE statements of
the schema so that they are more easily readable by humans.

<p>The ".databases" command shows a list of all databases open in
the current connection.  There will always be at least 2.  The first
one is "main", the original database opened.  The second is "temp",
the database used for temporary tables. There may be additional 
databases listed for databases attached using the ATTACH statement.
The first output column is the name the database is attached with,