Documentation Source Text

          {line ADD {optx COLUMN} column-def}
       }
  }
  analyze-stmt {
     line ANALYZE {optx /database-name .} /table-name
  }
  attach-stmt {
     line ATTACH {or DATABASE nil} /filename AS /database-name
  }
  begin-stmt {
     line BEGIN {or nil DEFERRED IMMEDIATE EXCLUSIVE}
          {optx TRANSACTION}
  }
  commit-stmt {
     line {or COMMIT END} {optx TRANSACTION}

set syntax_linkage(alter-table-stmt) {column-def sql-stmt}
set syntax_linkage(analyze-stmt) {{} sql-stmt}
set syntax_linkage(attach-stmt) {{} sql-stmt}
set syntax_linkage(begin-stmt) {{} sql-stmt}
set syntax_linkage(column-constraint) {{conflict-clause expr foreign-key-clause literal-value signed-number} column-def}
set syntax_linkage(column-def) {{column-constraint type-name} {alter-table-stmt create-table-stmt}}
set syntax_linkage(comment-syntax) {{} {}}
set syntax_linkage(commit-stmt) {{} sql-stmt}
set syntax_linkage(compound-operator) {{} select-stmt}
set syntax_linkage(conflict-clause) {{} {column-constraint table-constraint}}
set syntax_linkage(create-index-stmt) {indexed-column sql-stmt}
set syntax_linkage(create-table-stmt) {{column-def select-stmt table-constraint} sql-stmt}
set syntax_linkage(create-trigger-stmt) {{delete-stmt expr insert-stmt select-stmt update-stmt} sql-stmt}
set syntax_linkage(create-view-stmt) {select-stmt sql-stmt}
set syntax_linkage(create-virtual-table-stmt) {{} sql-stmt}
set syntax_linkage(delete-stmt) {{expr qualified-table-name} {create-trigger-stmt sql-stmt}}
set syntax_linkage(delete-stmt-limited) {{expr ordering-term qualified-table-name} sql-stmt}
set syntax_linkage(detach-stmt) {{} sql-stmt}
set syntax_linkage(drop-index-stmt) {{} sql-stmt}
set syntax_linkage(drop-table-stmt) {{} sql-stmt}
set syntax_linkage(drop-trigger-stmt) {{} sql-stmt}
set syntax_linkage(drop-view-stmt) {{} sql-stmt}
set syntax_linkage(expr) {{literal-value raise-function select-stmt type-name} {column-constraint create-trigger-stmt delete-stmt delete-stmt-limited insert-stmt join-constraint ordering-term result-column select-stmt table-constraint update-stmt update-stmt-limited}}
set syntax_linkage(foreign-key-clause) {{} {column-constraint table-constraint}}
set syntax_linkage(indexed-column) {{} {create-index-stmt table-constraint}}
set syntax_linkage(insert-stmt) {{expr select-stmt} {create-trigger-stmt sql-stmt}}
set syntax_linkage(join-constraint) {expr join-source}
set syntax_linkage(join-op) {{} join-source}
set syntax_linkage(join-source) {{join-constraint join-op single-source} {select-stmt single-source}}
set syntax_linkage(literal-value) {{} {column-constraint expr}}

  functions.  This option is sometimes useful when trying to compile
  the date and time functions on a platform that does not support the
  concept of local time.
}

COMPILE_OPTION {SQLITE_OMIT_MEMORYDB} {
  When this is defined, the library does not respect the special database
  name ":memory:" (normally used to create an [in-memory database]). If 
  ":memory:" is passed to [sqlite3_open()], [sqlite3_open16()], or
  [sqlite3_open_v2()], a file with this name will be 
  opened or created.
}

COMPILE_OPTION {SQLITE_OMIT_OR_OPTIMIZATION} {
  This option disables the ability of SQLite to use an index together

<title>In-Memory Databases</title>
<tcl>hd_keywords {in-memory database} {in-memory databases} {memory}</tcl>

<h1 align="center">In-Memory Databases</h1>

<p>An SQLite database is normally stored in a single ordinary disk
file. However, in certain circumstances, the database might be stored in
memory.</p>

<p>The most common way to force an SQLite database to exist purely 
in memory is to open the database using the special filename
"<b>:memory:</b>".  In other words, instead of passing the name of
a real disk file into one of the [sqlite3_open()], [sqlite3_open16()], or
[sqlite3_open_v2()] functions, pass in the string ":memory:".  For
example:</p>

<blockquote><pre>
rc = sqlite3_open(":memory:", &db);
</pre></blockquote>

<p>When this is done, no disk file is opened.  
Instead, a new database is created
purely in memory.  The database ceases to exist as soon as the database
connection is closed.  Every :memory: database is distinct from every
other.  So, opening two database connections each with the filename
":memory:" will create two independent in-memory databases.</p>

<p>The special filename ":memory:" can be used anywhere that a database
filename is permitted.  For example, it can be used as the
<i>filename</i> in an [ATTACH] command:</p>

<blockquote>
<b>ATTACH DATABASE ':memory:' AS aux1;</b>
</blockquote>

<p>Note that in order for the special ":memory:" name to apply and to
create a pure in-memory database, there must be no additional text in the
filename.  Thus, a disk-based database can be created in a file by prepending
a pathname, like this:  "./:memory:".</p>

<tcl>hd_fragment temp_db {temporary tables} {temporary databases}</tcl>
<h2>Temporary Databases</h2>

<p>When the name of the database file handed to [sqlite3_open()] or to
[ATTACH] is an empty string, then a new temporary file is created to hold
the database.</p>

<blockquote><pre>
rc = sqlite3_open("", &db);
</pre></blockquote>

<blockquote><b>
ATTACH DATABASE '' AS aux2;
</b></blockquote>

<p>A different temporary file is created each time, so that just like as
with the special ":memory:" string, two database connections to temporary
databases each have their own private database.  Temporary databases are
automatically deleted when the connection that created them closes.</p>

<p>Even though a disk file is allocated for each temporary database, in
practice the temporary database usually resides in the in-memory pager
cache and hence is very little difference between a pure in-memory database
created by ":memory:" and a temporary database created by an empty filename.
The sole difference is that a ":memory:" database must remain in memory
at all times whereas parts of a temporary database might be flushed to
disk if database becomes large or if SQLite comes under memory pressure.</p>

<p>The previous paragraphs describe the behavior of temporary databases
under the default SQLite configuration.  An application can use the
[temp_store pragma] and the [SQLITE_TEMP_STORE] compile-time parameter to
force temporary databases to behave as pure in-memory databases, if desired.
</p>

Section {ATTACH DATABASE} attach ATTACH

BubbleDiagram attach-stmt 1
</tcl>

<p>The ATTACH DATABASE statement adds another database 
file to the current database connection.  If the filename contains 
punctuation characters it must be quoted.  The database-names 'main' and 
'temp' refer to the main database and the database used for 
temporary tables.  These cannot be detached.  Attached databases 
are removed using the [DETACH] statement.</p>

<p>You cannot create a new table with the same name as a table in 
an attached database, but you can attach a database which contains
tables whose names are duplicates of tables in the main database.  It is 
also permissible to attach the same database file multiple times.</p>

<p>Tables in an attached database can be referred to using the syntax 
<i>database-name.table-name</i>.  If an attached table doesn't have 
a duplicate table name in the main database, it does not require a 
<i>database-name</i> prefix.  When a database is attached, all of its 
tables which don't have duplicate names become the default table
of that name.  Any tables of that name attached afterwards require the
database prefix. If the default table of a given name is detached, then
the last table of that name attached becomes the new default.</p>

<p>
Transactions involving multiple attached databases are atomic,
assuming that the main database is not "[:memory:]".  If the main
database is ":memory:" then 
transactions continue to be atomic within each individual
database file. But if the host computer crashes in the middle
of a [COMMIT] where two or more database files are updated,
some of those files might get the changes where others
might not.
</p>

the ROLLBACK TO command restarts the transaction again at the beginning.
All intervening SAVEPOINTs are cancelled, however.</p>

<p>The RELEASE is like a [COMMIT] for a SAVEPOINT.
The RELEASE command causes all savepoints back to and including the first
savepoint with a matching name to be removed from the transaction stack.  The
RELEASE of an inner transaction
does not cause any changes to be written to the database file; it merely
removes savepoints from the transaction stack such that it is
no longer possible to ROLLBACK TO those savepoints.
If a RELEASE command releases the outermost savepoint, so
that the transaction stack becomes empty, then RELEASE is the same
as [COMMIT].
The [COMMIT] command may used to release all savepoints and
commit the transaction even if the transaction was originally started

<p>There are no arbitrary limits on the number of indices that can be
attached to a single table.  The number of columns in an index is 
limited to [SQLITE_MAX_COLUMN].</p>

<p>If the UNIQUE keyword appears between CREATE and INDEX then duplicate
index entries are not allowed.  Any attempt to insert a duplicate entry
will result in an error.  For the purposes of unique indices, all NULL values
are considered to different from all other NULL values and are thus unique.
This is one of the two possible interpretations of the SQL-92 standard
(the language in the standard is ambiguious) and is the interpretation
followed by PostgreSQL, MySQL, Firebird, and Oracle.  Informix and
Microsoft SQL Server follow the other interpretation of the standard.</p>

<p>The text
of each CREATE INDEX statement is stored in the <b>sqlite_master</b>
or <b>sqlite_temp_master</b> table, depending on whether the table
being indexed is temporary.  Every time the database is opened,
all CREATE INDEX statements
are read from the <b>sqlite_master</b> table and used to regenerate

##############################################################################
Section VACUUM vacuum VACUUM

BubbleDiagram vacuum-stmt 1
</tcl>

<p>When an object (table, index, or trigger) is dropped from the 
database, it leaves behind empty space. 
This empty space will be reused the next time new information is
added to the database.  But in the meantime, the database file might
be larger than strictly necessary.  Also, frequent inserts, updates,
and deletes can cause the information in the database to become
fragmented - scrattered out all across the database file rather
than clustered together in one place.</p>

<p>The VACUUM command cleans
the main database by copying its contents to a temporary database file and 
reloading the original database file from the copy.  This eliminates 
free pages,  aligns table data to be contiguous, and otherwise cleans 
up the database file structure.</p>

<p>As of SQLite version 3.1, an alternative to using the VACUUM command
is auto-vacuum mode, enabled using the 
[auto_vacuum] pragma.  When [auto_vacuum] is enabled for a database, 
large deletes cause
the size of the database file to shrink.  However, [auto_vacuum]
also causes excess fragmentation of the database file.  And [auto_vacuum]
does not compact partially filled pages of the database as VACUUM
does.</p>

<p>The [page_size] and/or [auto_vacuum] mode of a database can be changed
by invoking the [page_size pragma] and/or [auto_vacuum pragma] and then
immediately VACUUMing the database.</p>

<tcl>
##############################################################################
Section {INDEXED BY} indexedby {{INDEXED BY} {NOT INDEXED}}

</tcl>
<p>The INDEXED BY phrase is a SQL extension found only in SQLite which can
be used to verify that the correct indices are being used on a [DELETE],

to change.  The INDEXED BY clause is designed to help detect
undesirable query plan changes during regression testing.
Developers are admonished to omit all use of INDEXED BY during
application design, implementation, testing, and tuning.  If
INDEXED BY is to be used at all, it should be inserted at the very
end of the development process when "locking down" a design.</p>

<h3>See Also:</h3>

<p>The [sqlite3_stmt_status()] C/C++ interface together with the
[SQLITE_STMTSTATUS_FULLSCAN_STEP] and [SQLITE_STMTSTATUS_SORT] verbs
can be used to detect at run-time when an SQL statement is not
making effective use of indices.  Many applications may prefer to
use the [sqlite3_stmt_status()] interface to detect index misuse
rather than the INDEXED BY phrase described here.</p>

<tcl>
#############################################################################
# A list of keywords.  A asterisk occurs after the keyword if it is on
# the fallback list.
#
set keyword_list [lsort {
   ABORT*

       <br>PRAGMA temp_store = DEFAULT;</b> (0)<b>
       <br>PRAGMA temp_store = FILE;</b> (1)<b>
       <br>PRAGMA temp_store = MEMORY;</b> (2)</p>
    <p>Query or change the setting of the "<b>temp_store</b>" parameter.
    When temp_store is DEFAULT (0), the compile-time C preprocessor macro
    [SQLITE_TEMP_STORE] is used to determine where temporary tables and indices
    are stored.  When
    temp_store is MEMORY (2) [temporary tables] and indices are kept in
    as if they were pure [in-memory databases] memory.
    When temp_store is FILE (1) [temporary tables] and indices are stored
    in a file.  The <a href="#pragma_temp_store_directory">
    temp_store_directory</a> pragma can be used to specify the directory
    containing this file.
    <b>FILE</b> is specified. When the temp_store setting is changed,
    all existing temporary tables, indices, triggers, and views are
    immediately deleted.</p>

    </li>
    <br>

<tcl>Subsection temp_store_directory</tcl>
<li><p><b>PRAGMA temp_store_directory;
       <br>PRAGMA temp_store_directory = 'directory-name';</b></p>
    <p>Query or change the setting of the "temp_store_directory" - the
    directory where files used for storing [temporary tables] and indices
    are kept.  This setting lasts for the duration of the current connection
    only and resets to its default value for each new connection opened.

    <p>When the temp_store_directory setting is changed, all existing temporary
    tables, indices, triggers, and viewers are immediately deleted.  In
    practice, temp_store_directory should be set immediately after the 
    database is opened.  </p>

    <p>The value <i>directory-name</i> should be enclosed in single quotes.
    To revert the directory to the default, set the <i>directory-name</i> to
    an empty string, e.g., <i>PRAGMA temp_store_directory = ''</i>.  An
    error is raised if <i>directory-name</i> is not found or is not
    writable. </p>

    <p>The default directory for temporary files depends on the OS.  For
    Unix (Linux and Mac OS X), the default is the is the first
    writable directory found
    in the list of: <b>/var/tmp, /usr/tmp, /tmp,</b> and <b>
    <i>current-directory</i></b>.  For Windows, the default 
    directory is determined by the O/S, generally
    <b>C:\Documents and Settings\<i>user-name</i>\Local Settings\Temp\</b>. 
    Temporary files created by SQLite are unlinked immediately after
    opening, so that the operating system can automatically delete the
    files when the SQLite process exits.  Thus, temporary files are not

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/cvstrac/rlog?f=sqlite/src/shell.c">
download</a> from the SQLite website.  Compile this file (together

with the [amalgamation | sqlite3 library source code] to generate
the executable.  For example:</p>

<blockquote><pre>
gcc -o sqlite3 shell.c sqlite3.c -ldl -lpthread
</pre></blockquote>

command to control the database.  The name of the new Tcl command
is given by the first argument.  This approach is similar to the
way widgets are created in Tk.
</p>

<p>
The name of the database is just the name of a disk file in which
the database is stored.  If the name of the database is 
the special name "[:memory:]" then a new database is created
in memory.  If the name of the database is an empty string, then
the database is created in an empty file that is automatically deleted
when the database connection closes.
</p>

<p>
Once an SQLite database is open, it can be controlled using 
methods of the <i>dbcmd</i>.  There are currently 22 methods
defined.</p>