Documentation Source Text

<p>The SQLite project provides a simple command-line utility named
<b>sqlite3</b> (or <b>sqlite3.exe</b> on windows)
that allows the user to manually enter and execute SQL
statements against an SQLite database.  This document provides a brief
introduction on how to use the <b>sqlite3</b> program.


<h3>Getting Started</h3>

<p>To start the <b>sqlite3</b> program, just type "sqlite3" optionally
followed by the name the file that holds the SQLite database.  If the 
file does not exist, a new database file with the given name will be
created automatically.  If no database file is specified, a temporary 
database is created, then deleted when the "sqlite3" program exits.
................................................................................
   ...> (((  f1 varchar(30) primary key,)))
   ...> (((  f2 text,)))
   ...> (((  f3 real)))
   ...> ((();)))
sqlite> 
}</tcl>


<h3>Double-click Startup On Windows</h3>

<p>Windows users can double-click on the <b>sqlite3.exe</b> icon to cause
the command-line shell to pop-up a terminal window running SQLite.  Note,
however, that by default this SQLite session is using an in-memory database,
not a file on disk, and so all changes will be lost when the session exits.
To use a persistent disk file as the database, enter the ".open" command
................................................................................
.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>

<p>Ordinary SQL statements are free-form, and can be
spread across multiple lines, and can have whitespace and
comments anywhere.  But dot-commands are
more restrictive:

................................................................................
</ul>

<p>And, of course, it is important to remember that the dot-commands
are interpreted by the sqlite3.exe command-line program, not by
SQLite itself.  So none of the dot-commands will work as an argument
to SQLite interfaces like [sqlite3_prepare()] or [sqlite3_exec()].


<h3>Changing Output Formats</h3>

<p>The sqlite3 program is able to show the results of a query
in eight different formats: "csv", "column", "html", "insert",
"line", "list", "tabs", and "tcl".
You can use the ".mode" dot command to switch between these output
formats.</p>
................................................................................
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
instead of .output and output will only be redirected for the single next
................................................................................

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


<h3>Querying the database schema</h3>

<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
means.  These commands are provided purely as a shortcut.</p>

................................................................................


<tcl>DisplayCode {
$ (((createdb ex2)))
$ (((sqlite3 ex1 .dump | psql ex2)))
}</tcl>


<h3>Loading Extensions</h3>

<p>You can add new custom [application-defined SQL functions],
[collating sequences], [virtual tables], and [VFSes] to the command-line
shell at run-time using the ".load" command.  First, convert the
extension in to a DLL or shared library (as described in the
[Run-Time Loadable Extensions] document) then type:
................................................................................

<p>Source code for several useful extensions can be found in the
<a href="http://www.sqlite.org/src/tree?name=ext/misc&ci=trunk">ext/misc</a>
subdirectory of the SQLite source tree.  You can use these extensions
as-is, or as a basis for creating your own custom extensions to address
your own particular needs.


<h3>Other Dot Commands</h3>

<p>There are many other dot-commands available in the command-line
shell.  See the ".help" command for a complete list for any particular
version and build of SQLite.


<h3>Using sqlite3 in a shell script</h3>

<p>
One way to use sqlite3 in a shell script is to use "echo" or
"cat" to generate a sequence of commands in a file, then invoke sqlite3
while redirecting input from the generated command file.  This
works fine and is appropriate in many circumstances.  But as
................................................................................
$ (((sqlite3 ex1 'select * from tbl1' |)))
> ((( awk '{printf "<tr><td>%s<td>%s\n",$1,$2 }')))
<tr><td>hello<td>10
<tr><td>goodbye<td>20
$
}</tcl>


<h3>Ending shell commands</h3>

<p>
SQLite commands are normally terminated by a semicolon.  In the shell 
you can also use the word "GO" (case-insensitive) or a slash character 
"/" on a line by itself to end a command.  These are used by SQL Server 
and Oracle, respectively.  These won't work in <b>sqlite3_exec()</b>, 
because the shell translates these into a semicolon before passing them 
to that function.</p>



<h3>Compiling the sqlite3 program from sources</h3>

<p>
The source code to the sqlite3 command line interface is in a single
file named "shell.c" which you can
<a href="http://www.sqlite.org/src/finfo?name=src/shell.c">
download</a> from the SQLite website.  

<p>The SQLite project provides a simple command-line utility named
<b>sqlite3</b> (or <b>sqlite3.exe</b> on windows)
that allows the user to manually enter and execute SQL
statements against an SQLite database.  This document provides a brief
introduction on how to use the <b>sqlite3</b> program.

<tcl>hd_fragment intro</tcl>
<h3>Getting Started</h3>

<p>To start the <b>sqlite3</b> program, just type "sqlite3" optionally
followed by the name the file that holds the SQLite database.  If the 
file does not exist, a new database file with the given name will be
created automatically.  If no database file is specified, a temporary 
database is created, then deleted when the "sqlite3" program exits.
................................................................................
   ...> (((  f1 varchar(30) primary key,)))
   ...> (((  f2 text,)))
   ...> (((  f3 real)))
   ...> ((();)))
sqlite> 
}</tcl>

<tcl>hd_fragment dblclick</tcl>
<h3>Double-click Startup On Windows</h3>

<p>Windows users can double-click on the <b>sqlite3.exe</b> icon to cause
the command-line shell to pop-up a terminal window running SQLite.  Note,
however, that by default this SQLite session is using an in-memory database,
not a file on disk, and so all changes will be lost when the session exits.
To use a persistent disk file as the database, enter the ".open" command
................................................................................
.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>

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

<p>Ordinary SQL statements are free-form, and can be
spread across multiple lines, and can have whitespace and
comments anywhere.  But dot-commands are
more restrictive:

................................................................................
</ul>

<p>And, of course, it is important to remember that the dot-commands
are interpreted by the sqlite3.exe command-line program, not by
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</tcl>
<h3>Changing Output Formats</h3>

<p>The sqlite3 program is able to show the results of a query
in eight different formats: "csv", "column", "html", "insert",
"line", "list", "tabs", and "tcl".
You can use the ".mode" dot command to switch between these output
formats.</p>
................................................................................
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.

<tcl>hd_fragment dotoutput</tcl>
<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
instead of .output and output will only be redirected for the single next
................................................................................

<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>
<h3>Querying the database schema</h3>

<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
means.  These commands are provided purely as a shortcut.</p>

................................................................................


<tcl>DisplayCode {
$ (((createdb ex2)))
$ (((sqlite3 ex1 .dump | psql ex2)))
}</tcl>

<tcl>hd_fragment dotload</tcl>
<h3>Loading Extensions</h3>

<p>You can add new custom [application-defined SQL functions],
[collating sequences], [virtual tables], and [VFSes] to the command-line
shell at run-time using the ".load" command.  First, convert the
extension in to a DLL or shared library (as described in the
[Run-Time Loadable Extensions] document) then type:
................................................................................

<p>Source code for several useful extensions can be found in the
<a href="http://www.sqlite.org/src/tree?name=ext/misc&ci=trunk">ext/misc</a>
subdirectory of the SQLite source tree.  You can use these extensions
as-is, or as a basis for creating your own custom extensions to address
your own particular needs.

<tcl>hd_fragment dotother</tcl>
<h3>Other Dot Commands</h3>

<p>There are many other dot-commands available in the command-line
shell.  See the ".help" command for a complete list for any particular
version and build of SQLite.

<tcl>hd_fragment insh</tcl>
<h3>Using sqlite3 in a shell script</h3>

<p>
One way to use sqlite3 in a shell script is to use "echo" or
"cat" to generate a sequence of commands in a file, then invoke sqlite3
while redirecting input from the generated command file.  This
works fine and is appropriate in many circumstances.  But as
................................................................................
$ (((sqlite3 ex1 'select * from tbl1' |)))
> ((( awk '{printf "<tr><td>%s<td>%s\n",$1,$2 }')))
<tr><td>hello<td>10
<tr><td>goodbye<td>20
$
}</tcl>

<tcl>hd_fragment endsh</tcl>
<h3>Ending shell commands</h3>

<p>
SQLite commands are normally terminated by a semicolon.  In the shell 
you can also use the word "GO" (case-insensitive) or a slash character 
"/" on a line by itself to end a command.  These are used by SQL Server 
and Oracle, respectively.  These won't work in <b>sqlite3_exec()</b>, 
because the shell translates these into a semicolon before passing them 
to that function.</p>


<tcl>hd_fragment compiling</tcl>
<h3>Compiling the sqlite3 program from sources</h3>

<p>
The source code to the sqlite3 command line interface is in a single
file named "shell.c" which you can
<a href="http://www.sqlite.org/src/finfo?name=src/shell.c">
download</a> from the SQLite website.