Documentation Source Text

  Version 3.3.0 and later supports the ability for two or more
  database connections to share the same page and schema cache.
  This feature is useful for certain specialized applications.
}
doc {Tcl API} {tclsqlite.html} {
  A description of the TCL interface bindings for SQLite.
}









doc {How SQLite Implements Atomic Commit} {atomiccommit.html} {
  A description of the logic within SQLite that implements
  transactions with atomic commit, even in the face of power
  failures.
}
doc {Moving From SQLite 3.4 to 3.5} {34to35.html} {

  Version 3.3.0 and later supports the ability for two or more
  database connections to share the same page and schema cache.
  This feature is useful for certain specialized applications.
}
doc {Tcl API} {tclsqlite.html} {
  A description of the TCL interface bindings for SQLite.
}

doc {Temporary Files Used By SQLite} {tempfiles.html} {
  SQLite can potentially use many different temporary files when
  processing certain SQL statements.  This document describes the
  many kinds of temporary files that SQLite uses and offers suggestions
  for avoiding them on systems where creating a temporary file is an
  expensive operation.
}

doc {How SQLite Implements Atomic Commit} {atomiccommit.html} {
  A description of the logic within SQLite that implements
  transactions with atomic commit, even in the face of power
  failures.
}
doc {Moving From SQLite 3.4 to 3.5} {34to35.html} {

    incremental vacuum may be promoted from a pragma to a separate
    SQL command, or perhaps some variation on the [VACUUM] command.
    Programmers are cautioned to not become enamored with the
    current syntax or functionality as it is likely to change.</p>
</li>

<tcl>Subsection journal_mode</tcl>
<li><p><b>PRAGMA jounal_mode;
       <br>PRAGMA <i>database</i>.journal_mode;
       <br>PRAGMA journal_mode
              = <i>DELETE | PERSIST | OFF</i>
       <br>PRAGMA <i>database</i>.journal_mode
              = <i>DELETE | PERSIST | OFF</i></b></p>

    <p>This pragma queries or sets the journal mode for databases

    incremental vacuum may be promoted from a pragma to a separate
    SQL command, or perhaps some variation on the [VACUUM] command.
    Programmers are cautioned to not become enamored with the
    current syntax or functionality as it is likely to change.</p>
</li>

<tcl>Subsection journal_mode</tcl>
<li><p><b>PRAGMA journal_mode;
       <br>PRAGMA <i>database</i>.journal_mode;
       <br>PRAGMA journal_mode
              = <i>DELETE | PERSIST | OFF</i>
       <br>PRAGMA <i>database</i>.journal_mode
              = <i>DELETE | PERSIST | OFF</i></b></p>

    <p>This pragma queries or sets the journal mode for databases

</p>

<ol>
<li>Rollback journals</li>
<li>Master journals</li>
<li>Statement journals</li>
<li>TEMP databases</li>
<li>Manifestations of views and subqueries</li>
<li>Transient indices</li>
<li>Transient databases used by VACUUM</li>
</ol>

<p>
Additional information about each of these temporary file types
is in the sequel.
................................................................................
</p>

<p>
A statement journal is only created for an UPDATE or INSERT statement
that might change muliple rows of a database and which might hit a
constraint or a RAISE exception within a trigger and thus need to
undo partial results.
If the UPDATE or INSERT is the first or only statement within a

transaction, then no statement journal is created since the ordinary
rollback journal can be used instead.
The statement journal is also omitted if an alternative
<a href="lang_conflict.html">conflict resolution algorithm</a> is
used.  For example:
</p>

<blockquote><pre>
................................................................................

<p>
Recent versions of SQLite (version 3.5.4 and later)
will do this rewrite automatically
if an index exists on the column ex2.b.
</p>
























<p>
Subqueries might also need to be materialized when they appear
in the FROM clause of a SELECT statement.  For example:
</p>

<blockquote><pre>
SELECT * FROM ex1 JOIN (SELECT b FROM ex2) AS t ON t.b=ex1.a;
................................................................................
for the duration of the command itself.  The size of the temporary
file will be no larger than the original database.
</p>

<h2>3.0 The TEMP_STORE Compile-Time Parameter and Pragma</h2>

<p>

The rollback journal and master journal files are always written
to disk.
But the other kinds of temporary files might be stored in memory
only and never written to disk.
Whether or not temporary files other than the rollback
and master journals are written to disk or stored only in memory
depends on the TEMP_STORE compile-time parameter, the
<a href="pragma.html#pragma_temp_store">temp_store</a> [PRAGMA],
and on the size of the temporary file.
</p>

<p>
The TEMP_STORE compile-time parameter is a #define whose value is

</p>

<ol>
<li>Rollback journals</li>
<li>Master journals</li>
<li>Statement journals</li>
<li>TEMP databases</li>
<li>Materializations of views and subqueries</li>
<li>Transient indices</li>
<li>Transient databases used by VACUUM</li>
</ol>

<p>
Additional information about each of these temporary file types
is in the sequel.
................................................................................
</p>

<p>
A statement journal is only created for an UPDATE or INSERT statement
that might change muliple rows of a database and which might hit a
constraint or a RAISE exception within a trigger and thus need to
undo partial results.
If the UPDATE or INSERT is not contained within BEGIN...COMMIT and if
there are no other active statements on the same database connection then
no statement journal is created since the ordinary
rollback journal can be used instead.
The statement journal is also omitted if an alternative
<a href="lang_conflict.html">conflict resolution algorithm</a> is
used.  For example:
</p>

<blockquote><pre>
................................................................................

<p>
Recent versions of SQLite (version 3.5.4 and later)
will do this rewrite automatically
if an index exists on the column ex2.b.
</p>

<p>
If the right-hand side of an IN operator can be list of values
as in the following:
</p>
<blockquote><pre>
SELECT * FROM ex1 WHERE a IN (1,2,3);
</pre></blockquote>
<p>
List values on the right-hand side of IN are treated as a 
subquery that must be materialized.  In other words, the
previous statement acts as if it were:
</p>
<blockquote><pre>
SELECT * FROM ex1 WHERE a IN (SELECT 1 UNION ALL
                              SELECT 2 UNION ALL
                              SELECT 3);
</pre></blockquote>
<p>
A temporary index is always used to hold the values of the
right-hand side of an IN operator when that right-hand side
is a list of values.
</p>

<p>
Subqueries might also need to be materialized when they appear
in the FROM clause of a SELECT statement.  For example:
</p>

<blockquote><pre>
SELECT * FROM ex1 JOIN (SELECT b FROM ex2) AS t ON t.b=ex1.a;
................................................................................
for the duration of the command itself.  The size of the temporary
file will be no larger than the original database.
</p>

<h2>3.0 The TEMP_STORE Compile-Time Parameter and Pragma</h2>

<p>
The rollback journal, master journal,
and statement journal files are always written
to disk.
But the other kinds of temporary files might be stored in memory
only and never written to disk.
Whether or not temporary files other than the rollback,
master, and statement journals are written to disk or stored only in memory
depends on the TEMP_STORE compile-time parameter, the
<a href="pragma.html#pragma_temp_store">temp_store</a> [PRAGMA],
and on the size of the temporary file.
</p>

<p>
The TEMP_STORE compile-time parameter is a #define whose value is