Documentation Source Text

<tcl>hd_keywords *appformat {application file-format} \
     {Application File Format} {application file format}</tcl>
<title>SQLite As An Application File Format</title>

<h1 align="center">
SQLite As An Application File Format
</h1>

<h2>Executive Summary</h2>

  order to access two or more databases.  A single [database connection]
  can be made to access two or more databases at one time using the
  [ATTACH] SQL command.
</p>

<p>
  Many applications destroy their [database connections] using calls to
  [sqlite3_close()] at shutdown.  Or, for example, an application that
  uses SQLite as its [application file format] might
  open [database connections] in response to a File/Open menu action
  and then destroy the corresponding [database connection] in response
  to the File/Close menu.
</p>

<p>
  To run an SQL statement, the application follows these steps:
</p>

<p><ol>

<title>Features Of SQLite</title>

<h1 align=center>Features Of SQLite</h1>

<p><ul>
<li><a href="transactional.html">Transactions</a>
    are atomic, consistent, isolated, and durable (ACID)
    even after system crashes and power failures.</li>
<li><a href="zeroconf.html">Zero-configuration</a>
     - no setup or administration needed.</li>
<li>Implements most of SQL92.
    (<a href="omitted.html">Features not supported</a>)</li>
<li>A complete database is stored in a 
    <a href="onefile.html">single cross-platform disk file</a>.
    Great for use as an [application file format].</li>
<li>Supports terabyte-sized databases and gigabyte-sized strings
    and blobs.  (See <a href="limits.html">limits.html</a>.)
<li>Small code [footprint]: 
    less than 500KiB fully configured or much less
    with optional features omitted.</li>


<li>Simple, easy to use <a href="cintro.html">API</a>.</li>
<li>Written in ANSI-C.  <a href="tclsqlite.html">TCL bindings</a> included.
    Bindings for dozens of other languages available separately.</li>
<li>Well-commented source code with
    [test coverage |100% branch test coverage].</li>
<li>Available as a 
    <a href="amalgamation.html">single ANSI-C source-code file</a> 
    that you can easily drop into another project.

  */
  return rc;
}
</pre></blockquote>

<p>Many examples of complete and working loadable extensions can be seen in the
SQLite source tree in the
<a href="http://www.sqlite.org/src/tree?name=ext/misc&ci=trunk">ext/misc</a>
subdirectory.</p>

<h2>Statically Linking A Run-Time Loadable Extension</h2>

<p>The exact same source code can be used for both a run-time loadable
shared library or DLL and as a module that is statically linked with your
application.  This provides flexibility and allows you to reuse the same

<title>Command Line Shell For SQLite</title>
<tcl>hd_keywords {CLI} {Command Line Interface} {command-line shell}</tcl>

<h1 align=center>
Command Line Shell For SQLite
</h1>

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

proc Code {body} {
  hd_puts {<blockquote><pre>}
  regsub -all {&} [string trim $body] {\&amp;} body
  regsub -all {>} $body {\&gt;} body
  regsub -all {<} $body {\&lt;} body
  regsub -all {\(\(\(} $body {<b>} body
  regsub -all {\)\)\)} $body {</b>} body
  regsub -all {\[\[\[} $body {<i>} body
  regsub -all {\]\]\]} $body {</i>} body
  #regsub -all { } $body {\&nbsp;} body
  #regsub -all \n $body <br>\n body
  hd_puts $body
  hd_puts {</pre></blockquote>}
}

Code {
$ (((sqlite3 ex1)))
SQLite version 3.8.4 2014-02-11 16:24:34
Enter ".help" for usage hints.

sqlite> (((create table tbl1(one varchar(10), two smallint);)))
sqlite> (((insert into tbl1 values('hello!',10);)))
sqlite> (((insert into tbl1 values('goodbye', 20);)))
sqlite> (((select * from tbl1;)))
hello!|10
goodbye|20
sqlite>

sqlite> (((CREATE TABLE tbl2 ()))
   ...> (((  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 exist.
To use a persistent disk file as the database, use the ".open" command
immediately after the terminal window starts up:

<tcl>Code {
SQLite version 3.8.4 2014-02-11 16:24:34
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> (((.open ex1)))
sqlite> 
}</tcl>

<p>Alternatively, you can create a new database using the default in-memory
storage, then save that database into a disk file using the ".save" command:

<tcl>Code {
SQLite version 3.8.4 2014-02-11 16:24:34
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> [[[... many SQL commands omitted ...]]]
sqlite> (((.save ex1)))
sqlite> 
}</tcl>

<p>Be careful when using the ".save" command as it will overwrite any
preexisting database files having the same name without prompting for
confirmation.

<h3>Special commands to sqlite3</h3>

<p>
Most of the time, sqlite3 just reads lines of input and passes them
on to the SQLite library for execution.
But if an input line begins with a dot ("."), then

at any time.  For example:
</p>

<tcl>Code {
sqlite> (((.help)))
.backup ?DB? FILE      Backup DB (default "main") to FILE
.bail ON|OFF           Stop after hitting an error.  Default OFF
.clone NEWDB           Clone data into NEWDB from the existing database
.databases             List names and files of attached databases
.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
.exit                  Exit this program
.explain ?ON|OFF?      Turn output mode suitable for EXPLAIN on or off.

.output FILENAME       Send output to FILENAME
.output stdout         Send output to the screen
.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
.schema ?TABLE?        Show the CREATE statements
                         If TABLE specified, only show tables matching
                         LIKE pattern TABLE.
.separator STRING      Change separator used by output mode and .import
.show                  Show the current values for various settings
.stats ON|OFF          Turn stats on or off
.tables ?TABLE?        List names of tables
                         If TABLE specified, only list tables matching
                         LIKE pattern TABLE.
.timeout MS            Try opening locked tables for MS milliseconds
.trace FILE|off        Output each SQL statement as it is run
.vfsname ?AUX?         Print the name of the VFS stack
.width NUM1 NUM2 ...   Set column widths for "column" mode
.timer ON|OFF          Turn the CPU timer measurement on or off
sqlite> 
}</tcl>

<h3>Rules for "dot-commands"</h3>

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

<ul>

<title>SQLite Version 3 Overview</title>

<p>
<center><table border="1" cellpadding="10" width="75%">
<tr><td bgcolor="#ffffbb">
<b>Editorial Note:</b>
This document was written in 2004 as a guide to programmers who were
transitioning from SQLite2 to SQLite3.
It is retained as part of the historical record of SQLite.
Modern programmers should refer to
more up-to-date documentation on SQLite is available elsewhere
on this website.
</table></center>

<h2>SQLite Version 3 Overview</h2>

<p>
SQLite version 3.0 introduces important changes to the library, including:
</p>

<ul>