Documentation Source Text

  }
  incr nChng
}

chng {2020-08-22 (3.33.0)} {
<li> [CLI] enhancements:
    <ol type="a">
    <li> Added three new output modes: "table", "json", and "markdown".

    <li> The "column" output mode automatically expands columns to
         contain the longest output row.

    <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]

  }
  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 all 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 a spreadsheet
.exit ?CODE?             Exit this program with return-code CODE
.expert                  EXPERIMENTAL. Suggest indexes for specified queries


.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
.iotrace FILE            Enable I/O diagnostic logging to FILE
.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 (-e|-x|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

.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
.session ?NAME? CMD ...  Create or control sessions
.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'

.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 column widths for "column" mode
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 eight different formats: "csv", "column", "html", "insert",
"line", "list", "quote", "tabs", and "tcl".

















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

<p>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>By default, each column is between 1 and 10 characters wide, depending
on the column header name and the width of the first column of data.
Data that is too wide to fit in a column is truncated.  Use the
".width" dot-command to adjust column widths, like this:</p>



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

<p>The ".width" command in the example above sets the width of the first
column to 12 and the width of the second column to 6.  All other column
widths were unaltered.  You can give as many arguments to ".width" as
necessary to specify the widths of as many columns as are in your
query results.</p>


<p>If you specify a column a width of 0, then the column
width is automatically adjusted to be the maximum of three
numbers: 10, the width of the header, and the width of the
first row of data.  This makes the column width self-adjusting.
The default width setting for every column is this 
auto-adjusting 0 value.</p>

<p>Use a negative column width for right-justified columns.</p>

<p>The column labels that appear on the first two lines of output
can be turned on and off using the ".headers" dot command.  In the
examples above, the column labels are on.  To turn them off you
could do this:</p>

<tclscript>DisplayCode {
sqlite> (((.headers off)))

sqlite> (((select * from tbl1;)))









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>
................................................................................
sqlite> (((select * from tbl1;)))
INSERT INTO "new_table" VALUES('hello',10);
INSERT INTO "new_table" VALUES('goodbye',20);
sqlite>
}</tclscript>


<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 {.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

.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>
................................................................................
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