Documentation Source Text

  }
  incr nChng
}

chng {2020-08-22 (3.33.0)} {
<li> [CLI] enhancements:
    <ol type="a">
    <li> Added four new [.mode|output modes]: "box", "json", "markdown",
         and "table".
    <li> The "column" output mode automatically expands columns to
         contain the longest output row and automatically turns
         ".header" on if it has not been previously set.
    <li> The "quote" output mode honors ".separator"
    </ol>
<li> Improve the query planner so that it is able to find a
     full-index-scan query plan for queries using [INDEXED BY]
     which previously would fail with "no query solution".
<li> Improve the query planner so that it does a better job of
     detecting missing, incomplete, and/or dodgy [sqlite_stat1]

.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
.dbconfig ?op? ?val?     List or change sqlite3_db_config() options
.dbinfo ?DB?             Show status information about the database
.dump ?TABLE?            Render database content as SQL
.echo on|off             Turn command echo on or off
.eqp on|off|full|...     Enable or disable automatic EXPLAIN QUERY PLAN
.excel                   Display the output of next command in spreadsheet
.exit ?CODE?             Exit this program with return-code CODE
.expert                  EXPERIMENTAL. Suggest indexes for queries
.explain ?on|off|auto?   Change the EXPLAIN formatting mode.  Default: auto
.filectrl CMD ...        Run various sqlite3_file_control() operations
.fullschema ?--indent?   Show schema and the content of sqlite_stat tables
.headers on|off          Turn display of headers on or off
.help ?-all? ?PATTERN?   Show help text for PATTERN
.import FILE TABLE       Import data from FILE into TABLE
.imposter INDEX TABLE    Create imposter table TABLE on index INDEX
.indexes ?TABLE?         Show names of indexes

.limit ?LIMIT? ?VAL?     Display or change the value of an SQLITE_LIMIT
.lint OPTIONS            Report potential schema issues.
.load FILE ?ENTRY?       Load an extension library
.log FILE|off            Turn logging on or off.  FILE can be stderr/stdout
.mode MODE ?TABLE?       Set output mode
.nullvalue STRING        Use STRING in place of NULL values
.once ?OPTIONS? ?FILE?   Output for the next SQL command only to FILE
.open ?OPTIONS? ?FILE?   Close existing database and reopen FILE
.output ?FILE?           Send output to FILE or stdout if FILE is omitted
.parameter CMD ...       Manage SQL parameter bindings
.print STRING...         Print literal STRING
.progress N              Invoke progress handler after every N opcodes
.prompt MAIN CONTINUE    Replace the standard prompts
.quit                    Exit this program
.read FILE               Read input from FILE
.recover                 Recover as much data as possible from corrupt db.
.restore ?DB? FILE       Restore content of DB (default "main") from FILE
.save FILE               Write in-memory database into FILE
.scanstats on|off        Turn sqlite3_stmt_scanstatus() metrics on or off
.schema ?PATTERN?        Show the CREATE statements matching PATTERN
.selftest ?OPTIONS?      Run tests defined in the SELFTEST table
.separator COL ?ROW?     Change the column and row separators

.sha3sum ...             Compute a SHA3 hash of database content
.shell CMD ARGS...       Run CMD ARGS... in a system shell
.show                    Show the current values for various settings
.stats ?on|off?          Show stats or turn stats on or off
.system CMD ARGS...      Run CMD ARGS... in a system shell
.tables ?TABLE?          List names of tables matching LIKE pattern TABLE
.testcase NAME           Begin redirecting output to 'testcase-out.txt'
.testctrl CMD ...        Run various sqlite3_test_control() operations
.timeout MS              Try opening locked tables for MS milliseconds
.timer on|off            Turn SQL timer on or off
.trace ?OPTIONS?         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 minimum column widths for columnar output
sqlite> 
}</tclscript>

<tcl>hd_fragment dotrules</tcl>
<h1>Rules for "dot-commands"</h1>

<p>Ordinary SQL statements are free-form, and can be

SQLite itself.  So none of the dot-commands will work as an argument
to SQLite interfaces like [sqlite3_prepare()] or [sqlite3_exec()].

<tcl>hd_fragment dotmode {.mode}</tcl>
<h1>Changing Output Formats</h1>

<p>The sqlite3 program is able to show the results of a query
in 14 different formats:
<div class="columns" style="columns:8em auto;">
<ul style="padding-top:0;padding-left:4em;">
<li> ascii
<li> box
<li> csv
<li> column
<li> html
<li> insert
<li> json
<li> line
<li> list
<li> markdown
<li> quote
<li> table
<li> tabs
<li> tcl
</ul></div>

<p>You can use the ".mode" dot command to switch between these output
formats.

>The default output mode is "list".  In
list mode, each row of a query result is written on one line of
output and each column within that row is separated by a specific
separator string.  The default separator is a pipe symbol ("|").
List mode is especially useful when you are going to send the output
of a query to another program (such as AWK) for additional processing.</p>

<tclscript>DisplayCode {

<p>In column mode, each record is shown on a separate line with the
data aligned in columns.  For example:</p>

<tclscript>DisplayCode {
sqlite> (((.mode column)))
sqlite> (((select * from tbl1;)))
one       two       
--------  ---
hello     10        
goodbye   20        
sqlite>
}</tclscript>

<p>In "column" mode (and also in "box", "table", and "markdown" modes)
the width of columns adjusts automatically.  But you can override this,
providing a minimum width for each column using the ".width" command.
The arguments to ".width" are integers which are the minimum number of
spaces to devote to each column.  Negative numbers mean right-justify.
Thus:</p>

<tclscript>DisplayCode {
sqlite> (((.width 12 -6)))
sqlite> (((select * from tbl1;)))
one              two
------------  ------
hello!            10
goodbye           20
sqlite>
}</tclscript>

<p>A width of 0 means the column width is choosen automatically.
Unspecified columns widths to go zero.  Hence, the command
".width" with no arguments sets all columns widths to zero and
hence causes all column widths to be determine automatically.






<p>The "column" mode is a tabular output format.  Other






tabular output formats as "box", "markdown", and "table":







<tclscript>DisplayCode {
sqlite> (((.width)))
sqlite> (((.mode markdown)))
sqlite> (((select * from tbl1;)))
|   one   | two |
|---------|-----|
| hello!  | 10  |
| goodbye | 20  |
sqlite> (((.mode table)))
sqlite> (((select * from tbl1;)))
+---------+-----+
|   one   | two |
+---------+-----+
| hello!  | 10  |
| goodbye | 20  |
+---------+-----+
sqlite> (((.mode box)))
sqlite> (((select * from tbl1;)))
┌─────────┬─────┐
│   one   │ two │
├─────────┼─────┤
│ hello!  │ 10  │
│ goodbye │ 20  │
└─────────┴─────┘
sqlite>
}</tclscript>

<p>Another useful output mode is "insert".  In insert mode, the output
is formatted to look like SQL INSERT statements.  Use insert
mode to generate text that can later be used to input data into a 
different database.</p>

<p>When specifying insert mode, you have to give an extra argument
which is the name of the table to be inserted into.  For example:</p>

<tclscript>DisplayCode {
sqlite> (((.mode insert new_table)))
sqlite> (((select * from tbl1;)))
INSERT INTO "new_table" VALUES('hello',10);
INSERT INTO "new_table" VALUES('goodbye',20);
sqlite>
}</tclscript>


<p>Other output modes include "html", "json", and "tcl".  Try these
yourself to see what they do.



</p>

<tcl>hd_fragment dotoutput {.output} {.once}</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