Documentation Source Text

  <li> Added the experimenal [.expert command]
  <li> Added the ".eqp trigger" variant of the ".eqp" command
  <li> Enhance the ".lint fkey-indexes" command so that it works with
       [WITHOUT ROWID] tables.
  <li> If the filename argument to the shell is a ZIP archive rather than
       an SQLite database, then the shell automatically opens that ZIP 
       archive using the [Zipfile virtual table].



  <li> Databases are opened using 
       [https://sqlite.org/src/file/ext/misc/appendvfs.c|Append VFS] when
       the --append flag is used on the command line or with the
       .open command.
</ol>
<li> Enhance the [SQLITE_ENABLE_UPDATE_DELETE_LIMIT] compile-time option so
     that it works for [WITHOUT ROWID] tables.

  <li> Added the experimenal [.expert command]
  <li> Added the ".eqp trigger" variant of the ".eqp" command
  <li> Enhance the ".lint fkey-indexes" command so that it works with
       [WITHOUT ROWID] tables.
  <li> If the filename argument to the shell is a ZIP archive rather than
       an SQLite database, then the shell automatically opens that ZIP 
       archive using the [Zipfile virtual table].
  <li> Added the [edit() SQL function].
  <li> Added the [export to excel|.excel command] to simplify exporting
       database content to a spreadsheet.
  <li> Databases are opened using 
       [https://sqlite.org/src/file/ext/misc/appendvfs.c|Append VFS] when
       the --append flag is used on the command line or with the
       .open command.
</ol>
<li> Enhance the [SQLITE_ENABLE_UPDATE_DELETE_LIMIT] compile-time option so
     that it works for [WITHOUT ROWID] tables.

.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
.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                  Show this message
.import FILE TABLE     Import data from FILE into TABLE
.imposter INDEX TABLE  Create imposter table TABLE on index INDEX
................................................................................
                         insert   SQL insert statements for TABLE
                         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
.scanstats on|off      Turn sqlite3_stmt_scanstatus() metrics on or off
................................................................................
it reads from standard input.  So to see the results of a query
in a text editor, one could type:</p>

<tclscript>DisplayCode {
sqlite3> (((.once '|open -f')))
sqlite3> (((SELECT * FROM bigTable;)))
}</tclscript>





















<tcl>hd_fragment fileio {file I/O functions}</tcl>
<h2>File I/O Functions</h2>

<p>The command-line shell adds two [application-defined SQL functions] that
facilitate reading content from a file into a table column, and writing the
content of a column into a file, respectively.
................................................................................
}</tclscript>

<p>Note that the readfile(X) and writefile(X,Y) functions are extension
functions and are not built into the core SQLite library.  These routines
are available as a [loadable extension] in the
[http://www.sqlite.org/src/artifact?ci=trunk&filename=ext/misc/fileio.c|ext/misc/fileio.c]
source file in the [SQLite source code repositories].








































<tcl>hd_fragment schema</tcl>
<h1>Querying the database schema</h1>

<p>The sqlite3 program provides several convenience commands that
are useful for looking at the schema of the database.  There is
nothing that these commands do that cannot be done by some other
................................................................................
turned on.)

<p>The line ".once <i>FILENAME</i>" causes all query output to go into
the named file instead of being printed on the console.  In the example
above, that line causes the CSV content to be written into a file named
"C:/work/dataout.csv".

<tcl>hd_fragment exexcel* {export to excel}</tcl>
<h2> Export to Excel </h2>

<p>The final line of the example (the ".system c:/work/dataout.csv")
has the same effect as double-clicking on the c:/work/dataout.csv file
in windows.  This will typically bring up a spreadsheet program to display
the CSV file.

<p>That command only works as written on Windows.  
The equivalent line on a Mac would be:
................................................................................
sqlite> (((.system open dataout.csv)))
}</tclscript>

<p>On Linux and other unix systems you will need to enter something like:


<tclscript>DisplayCode {
sqlite> (((.system libreoffice dataout.csv)))
}</tclscript>

































<tcl>hd_fragment dump {.dump}</tcl>
<h1>Converting An Entire Database To An ASCII Text File</h1>

<p>Use the ".dump" command to convert the entire contents of a
database into a single ASCII text file.  This file can be converted
back into a database by piping it back into <b>sqlite3</b>.</p>

.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
.excel                 Display the output of next command in a spreadsheet
.exit                  Exit this program
.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                  Show this message
.import FILE TABLE     Import data from FILE into TABLE
.imposter INDEX TABLE  Create imposter table TABLE on index INDEX
................................................................................
                         insert   SQL insert statements for TABLE
                         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 (-e|-x|FILE)     Output for the next SQL command only to FILE
                         or invoke system text editor (-e) or spreadsheet (-x)
                         on the output.
.open ?OPTIONS? ?FILE? Close existing database and reopen FILE
                         The --new option starts with an empty file

.output ?FILE?         Send output to FILE 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
.scanstats on|off      Turn sqlite3_stmt_scanstatus() metrics on or off
................................................................................
it reads from standard input.  So to see the results of a query
in a text editor, one could type:</p>

<tclscript>DisplayCode {
sqlite3> (((.once '|open -f')))
sqlite3> (((SELECT * FROM bigTable;)))
}</tclscript>

<p>If the ".output" or ".once" commands have an argument of "-e" then
output is collected into a temporary file and the system text editor is
invoked on that text file.  Thus, the command ".once -e" achieves the
same result as ".once '|open -f'" but with the benefit of being portable
across all systems.

<p>If the ".output" or ".once" commands have a "-x" argument, that causes
them to accumulate output as Comma-Separated-Values (CSV) in a temporary
file, then invoke the default system utility for viewing CSV files
(usually a spreadsheet program) on the result.  This is a quick way of
sending the result of a query to a spreadsheet for easy viewing:

<tclscript>DisplayCode {
sqlite3> (((.once -x)))
sqlite3> (((SELECT * FROM bigTable;)))
}</tclscript>

<p>The ".excel" command is an alias for ".once -x".  It does exactly the same
thing.

<tcl>hd_fragment fileio {file I/O functions}</tcl>
<h2>File I/O Functions</h2>

<p>The command-line shell adds two [application-defined SQL functions] that
facilitate reading content from a file into a table column, and writing the
content of a column into a file, respectively.
................................................................................
}</tclscript>

<p>Note that the readfile(X) and writefile(X,Y) functions are extension
functions and are not built into the core SQLite library.  These routines
are available as a [loadable extension] in the
[http://www.sqlite.org/src/artifact?ci=trunk&filename=ext/misc/fileio.c|ext/misc/fileio.c]
source file in the [SQLite source code repositories].

<tcl>hd_fragment editfunc {edit() SQL function}</tcl>
<h2>The edit() SQL funtion</h2>

<p>The CLI has another build-in SQL function named edit().  Edit() takes
one or two arguments.  The first argument is a value - usually a large
multi-line string to be edited.  The second argument is the name of a
text editor.  If the second argument is omitted, the VISUAL environment
variable is used.  The edit() function writes its first argument into a
temporary file, invokes the editor on the temporary file, rereads the file
back into memory after the editor is done, then returns the edited text.

<p>The edit() function can be used to make changes to large text
values.  For example:

<tclscript>DisplayCode {
sqlite> (((UPDATE docs SET body=edit(body) WHERE name='report-15';)))
}</tclscript>

<p>In this example, the content of the docs.body field for the entry where
docs.name is "report-15" will be sent to the editor.  After the editor returns,
the result will be written back into the docs.body field.

<p>The default operation of edit() is to invoke a text editor.  But by using
an alternative edit program in the second argument, you can also get it to edit
images or other non-text resources.  For example, if you want to modify a JPEG
image that happens to be stored in a field of a table, you could run:

<tclscript>DisplayCode {
sqlite> (((UPDATE pics SET img=edit(img,'gimp') WHERE id='pic-1542';)))
}</tclscript>

<p>The edit program can also be used as a viewer, by simply ignoring the
return value.  For example, to merely look at the image above, you might run:

<tclscript>DisplayCode {
sqlite> (((SELECT length(edit(img,'gimp')) WHERE id='pic-1542';)))
}</tclscript>


<tcl>hd_fragment schema</tcl>
<h1>Querying the database schema</h1>

<p>The sqlite3 program provides several convenience commands that
are useful for looking at the schema of the database.  There is
nothing that these commands do that cannot be done by some other
................................................................................
turned on.)

<p>The line ".once <i>FILENAME</i>" causes all query output to go into
the named file instead of being printed on the console.  In the example
above, that line causes the CSV content to be written into a file named
"C:/work/dataout.csv".




<p>The final line of the example (the ".system c:/work/dataout.csv")
has the same effect as double-clicking on the c:/work/dataout.csv file
in windows.  This will typically bring up a spreadsheet program to display
the CSV file.

<p>That command only works as written on Windows.  
The equivalent line on a Mac would be:
................................................................................
sqlite> (((.system open dataout.csv)))
}</tclscript>

<p>On Linux and other unix systems you will need to enter something like:


<tclscript>DisplayCode {
sqlite> (((.system xdg-open dataout.csv)))
}</tclscript>

<tcl>hd_fragment exexcel* {export to excel}</tcl>
<h2> Export to Excel </h2>

<p>To make it easier to export to a spreadsheet, the CLI provides the
".excel" command, which capture the output of a single query and sends
that result to the default spreadsheet program on the host computer.
Use it like this:

<tclscript>DisplayCode {
sqlite> (((.excel)))
sqlite> (((SELECT * FROM tab;)))
}</tclscript>

<p>
The code above writes the output of the query as CSV into a temporary
file, invokes the default handler for CSV files (usually the preferred
spreadsheet program such as Excel or LibreOffice), then deletes the
temporary file.  This is essentially a short-hand method of doing
the sequence of ".csv", ".once", and ".system" commands described above.

<p>
The ".excel" command is really an alias for ".once -x".  The -x option
to .once causes it to writes results as CSV into a temporary file that
is named with a ".csv" suffix, then invoke the systems default handler
for CSV files.

<p>
There is also a ".once -e" command which works similarly, except that
it names the temporary file with a ".txt" suffix so that the default
text editor for the system will be invoked, instead of the default
spreadsheet.

<tcl>hd_fragment dump {.dump}</tcl>
<h1>Converting An Entire Database To An ASCII Text File</h1>

<p>Use the ".dump" command to convert the entire contents of a
database into a single ASCII text file.  This file can be converted
back into a database by piping it back into <b>sqlite3</b>.</p>