Documentation Source Text

    <a href="#pragma_auto_vacuum">auto_vacuum=incremental</a> mode
    or if there are no pages on the freelist.  ^If there are fewer than
    <i>N</i> pages on the freelist, or if <i>N</i> is less than 1, or
    if the "(<i>N</i>)" argument is omitted, then the entire
    freelist is cleared.</p>
}

Pragma journal_mode {
    <p>^(<b>PRAGMA DB.journal_mode;
        <br>PRAGMA DB.journal_mode
              = <i>DELETE | TRUNCATE | PERSIST | MEMORY | WAL | OFF</i></b></p>

    <p>This pragma queries or sets the journal mode for databases
    associated with the current [database connection].</p>)^

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

Pragma journal_size_limit {
    <p><b>
    PRAGMA DB.journal_size_limit<br>
    PRAGMA DB.journal_size_limit = </b><i>N</i> <b>;</b>

  <p>^If a database connection is operating in
  [locking_mode | exclusive locking mode] or in
  [journal_mode | persistent journal mode] 
  (PRAGMA journal_mode=persist) then
  after committing a transaction the [rollback journal] file may remain in
  the file-system. This increases performance for subsequent transactions
  since overwriting an existing file is faster than append to a file,
  but it also consumes
  file-system space. After a large transaction (e.g. a [VACUUM]),
  the rollback journal file may consume a very large amount of space.
................................................................................
    <p>^The legacy_file_format pragma is initialized to OFF when an existing
    database in the newer file format is first opened.</p>

    <p>^The default file format is set by the
    [SQLITE_DEFAULT_FILE_FORMAT] compile-time option.</p>
}

Pragma locking_mode {
    <p>^(<b>PRAGMA DB.locking_mode;
    <br>PRAGMA DB.locking_mode
                = <i>NORMAL | EXCLUSIVE</i></b>)^</p>
    <p>^This pragma sets or queries the database connection locking-mode. 
    ^The locking-mode is either NORMAL or EXCLUSIVE.

    <p>^In NORMAL locking-mode (the default unless overridden at compile-time
................................................................................
<li>These pragmas are only available in builds using non-standard
compile-time options.
<li>These pragmas are used for testing SQLite and are not recommended
for use in application programs.</ol></p>
<tcl>
foreach prag [lsort [array names PragmaBody]] {
  hd_fragment pragma_$prag



  hd_puts "<h _id=pragma_$prag style=\"display:none\"> PRAGMA "
  hd_puts [join $PragmaKeys($prag) ", "]
  hd_puts "</h>"

  foreach x $PragmaKeys($prag) {
    hd_keywords *$x "PRAGMA $x" "$x pragma"
  }
  hd_puts "<hr>"
  hd_resolve $PragmaBody($prag)
}
</tcl>
<hr>

    <a href="#pragma_auto_vacuum">auto_vacuum=incremental</a> mode
    or if there are no pages on the freelist.  ^If there are fewer than
    <i>N</i> pages on the freelist, or if <i>N</i> is less than 1, or
    if the "(<i>N</i>)" argument is omitted, then the entire
    freelist is cleared.</p>
}

Pragma {journal_mode {persistent journal mode}} {
    <p>^(<b>PRAGMA DB.journal_mode;
        <br>PRAGMA DB.journal_mode
              = <i>DELETE | TRUNCATE | PERSIST | MEMORY | WAL | OFF</i></b></p>

    <p>This pragma queries or sets the journal mode for databases
    associated with the current [database connection].</p>)^

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

Pragma journal_size_limit {
    <p><b>
    PRAGMA DB.journal_size_limit<br>
    PRAGMA DB.journal_size_limit = </b><i>N</i> <b>;</b>

  <p>^If a database connection is operating in
  [exclusive locking mode] or in
  [persistent journal mode] 
  (PRAGMA journal_mode=persist) then
  after committing a transaction the [rollback journal] file may remain in
  the file-system. This increases performance for subsequent transactions
  since overwriting an existing file is faster than append to a file,
  but it also consumes
  file-system space. After a large transaction (e.g. a [VACUUM]),
  the rollback journal file may consume a very large amount of space.
................................................................................
    <p>^The legacy_file_format pragma is initialized to OFF when an existing
    database in the newer file format is first opened.</p>

    <p>^The default file format is set by the
    [SQLITE_DEFAULT_FILE_FORMAT] compile-time option.</p>
}

Pragma {locking_mode {exclusive locking mode} {EXCLUSIVE locking mode}} {
    <p>^(<b>PRAGMA DB.locking_mode;
    <br>PRAGMA DB.locking_mode
                = <i>NORMAL | EXCLUSIVE</i></b>)^</p>
    <p>^This pragma sets or queries the database connection locking-mode. 
    ^The locking-mode is either NORMAL or EXCLUSIVE.

    <p>^In NORMAL locking-mode (the default unless overridden at compile-time
................................................................................
<li>These pragmas are only available in builds using non-standard
compile-time options.
<li>These pragmas are used for testing SQLite and are not recommended
for use in application programs.</ol></p>
<tcl>
foreach prag [lsort [array names PragmaBody]] {
  hd_fragment pragma_$prag

  set keys $PragmaKeys($prag)
  set x [lindex $keys 0]
  hd_puts "<h _id=pragma_$prag style=\"display:none\"> PRAGMA $x</h>"




  hd_keywords *$x "PRAGMA $x" "$x pragma" {*}[lrange $keys 1 end]

  hd_puts "<hr>"
  hd_resolve $PragmaBody($prag)
}
</tcl>
<hr>

  within the xShmMap method on the [sqlite3_io_methods] object
  while trying to open a new shared memory segment.
}
RESCODE SQLITE_IOERR_SHMSIZE           {SQLITE_IOERR | (19<<8)} {
  The SQLITE_IOERR_SHMSIZE error code is an [ext-v-prim|extended error code]
  for [SQLITE_IOERR] indicating an I/O error
  within the xShmMap method on the [sqlite3_io_methods] object
  while trying to resize an existing shared memory segment.


}
RESCODE SQLITE_IOERR_SHMLOCK           {SQLITE_IOERR | (20<<8)} {
  The SQLITE_IOERR_SHMLOCK error code is no longer used.
}
RESCODE SQLITE_IOERR_SHMMAP            {SQLITE_IOERR | (21<<8)} {
  The SQLITE_IOERR_SHMMAP error code is an [ext-v-prim|extended error code]
  for [SQLITE_IOERR] indicating an I/O error

  within the xShmMap method on the [sqlite3_io_methods] object
  while trying to open a new shared memory segment.
}
RESCODE SQLITE_IOERR_SHMSIZE           {SQLITE_IOERR | (19<<8)} {
  The SQLITE_IOERR_SHMSIZE error code is an [ext-v-prim|extended error code]
  for [SQLITE_IOERR] indicating an I/O error
  within the xShmMap method on the [sqlite3_io_methods] object
  while trying to enlarge a ["shm" file] as part of
  [WAL mode] transaction processing.  This error may indicate that
  the underlying filesystem volume is out of space.
}
RESCODE SQLITE_IOERR_SHMLOCK           {SQLITE_IOERR | (20<<8)} {
  The SQLITE_IOERR_SHMLOCK error code is no longer used.
}
RESCODE SQLITE_IOERR_SHMMAP            {SQLITE_IOERR | (21<<8)} {
  The SQLITE_IOERR_SHMMAP error code is an [ext-v-prim|extended error code]
  for [SQLITE_IOERR] indicating an I/O error

<title>WAL-mode File Format</title>
<tcl>hd_keywords {wal-index} {WAL-index format} {WAL-index File Format}</tcl>

<table_of_contents>

<p>This document describes low-level details on how [WAL mode] is
implemented on unix and windows.

<p>The separate [file format] description provides details on the
................................................................................
by three separate files:

<ol>
<li> The main database file with an arbitrary name "X".
<li> The write-ahead log file, usually named "X-wal".
<li> The wal-index file, usually named "X-shm".
</ol>



<p>The format of the main database file is as described in the
[file format] document.  The [file format version numbers] at offsets
18 and 19 into the main database must both be 2 to indicate that the
database is in WAL mode.  The main database may have an arbitrary
name allowed by the underlying filesystem.  No special file suffixes
are required, though ".db", ".sqlite", and ".sqlite3" seem to be
popular choices.


<p>The write-ahead log or "wal" file is a roll-forward journal
that records transactions that have been committed but not yet applied
to the main database.  Details on the format of the wal file are
describe in the [WAL format] subsection of the main [file format]
document.  The wal file is named by appending the four characters
"-wal" to the end of the name of the main database file.  Except
on 8+3 filesystems, such names are not allowed, and in that case
the file suffix is changed to ".WAL".  But as 8+3 filesystems are
increasingly rare, that exceptional case can usually be ignored.



<p>The wal-index file or "shm" file is not actually used as a file.
Rather, individual database clients mmap the shm file and use it
as shared memory for coordinating access to the database and as a cache
for quickly locating frame within the wal file.  The name
of the shm file is the main database file name with the four characters
"-shm" appended.  Or, for 8+3 filesystems, the shm file is the main
database file with the suffix changed to ".SHM".
................................................................................
shm file if it exists.  Since the content of the shm file does not need
to be preserved across a crash, the shm file is never fsync()-ed to disk.
In fact, if there were a mechanism by which SQLite could tell the 
operating system to never persist the shm file to disk but always hold
it in cache memory, SQLite would use that mechanism to avoid any
unnecessary disk I/O associated with the shm file.  However, no such
mechanism exists in standard posix.








<p>When a WAL mode database is in active use, all three of the above
files exist.



<p>If the last client using the database shuts down cleanly by
calling [sqlite3_close()], then a [checkpoint] is run automatically
in order to transfer all information from the wal file
over into the main database, and both the shm file
and the wal file are unlinked.  Thus, when the database is not in
use by any client, it is usually the case that only the main
................................................................................
shut down, or if the last client to disconnect was a read-only client,
then the final cleanup operation does not occur and the
shm and wal files may still exist on disk even when the database is
not in use.

<h2>Variations</h2>

<p>When [PRAGMA locking_mode=EXCLUSIVE] is set, only a
single client is allowed to have the database open at one time.  Since
only a single client can use the database, the shm file is omitted.
The single client uses a buffer in heap memory as a substitute for the
memory-mapped shm file.

<p>If a read/write client invokes
[sqlite3_file_control]([SQLITE_FCNTL_PERSIST_WAL]) prior to shutdown,
then at shutdown a checkpoint is still run, but the shm file and wal
file are not deleted.
This allows subsequent read-only clients to connect to and read the
database.


<h1>The WAL-Index File Format</h1>

<p>
The WAL-index or "shm" file is used to coordinate access to the database
by multiple clients, and as a cache to help clients quickly locate frames
within the wal file.

<title>WAL-mode File Format</title>
<tcl>hd_keywords {WAL-mode File Format}</tcl>

<table_of_contents>

<p>This document describes low-level details on how [WAL mode] is
implemented on unix and windows.

<p>The separate [file format] description provides details on the
................................................................................
by three separate files:

<ol>
<li> The main database file with an arbitrary name "X".
<li> The write-ahead log file, usually named "X-wal".
<li> The wal-index file, usually named "X-shm".
</ol>

<h2>The Main Database File</h2>

<p>The format of the main database file is as described in the
[file format] document.  The [file format version numbers] at offsets
18 and 19 into the main database must both be 2 to indicate that the
database is in WAL mode.  The main database may have an arbitrary
name allowed by the underlying filesystem.  No special file suffixes
are required, though ".db", ".sqlite", and ".sqlite3" seem to be
popular choices.

<h2>The Write-Ahead-Log or "-wal" File</h2>
<p>The write-ahead log or "wal" file is a roll-forward journal
that records transactions that have been committed but not yet applied
to the main database.  Details on the format of the wal file are
describe in the [WAL format] subsection of the main [file format]
document.  The wal file is named by appending the four characters
"-wal" to the end of the name of the main database file.  Except
on 8+3 filesystems, such names are not allowed, and in that case
the file suffix is changed to ".WAL".  But as 8+3 filesystems are
increasingly rare, that exceptional case can usually be ignored.

<tcl>hd_fragment shm  {wal-index} {"shm" file}</tcl>
<h2>The Wal-Index or "-shm" file</h2>
<p>The wal-index file or "shm" file is not actually used as a file.
Rather, individual database clients mmap the shm file and use it
as shared memory for coordinating access to the database and as a cache
for quickly locating frame within the wal file.  The name
of the shm file is the main database file name with the four characters
"-shm" appended.  Or, for 8+3 filesystems, the shm file is the main
database file with the suffix changed to ".SHM".
................................................................................
shm file if it exists.  Since the content of the shm file does not need
to be preserved across a crash, the shm file is never fsync()-ed to disk.
In fact, if there were a mechanism by which SQLite could tell the 
operating system to never persist the shm file to disk but always hold
it in cache memory, SQLite would use that mechanism to avoid any
unnecessary disk I/O associated with the shm file.  However, no such
mechanism exists in standard posix.

<p>Because the shm is only used to coordinate access between concurrent
clients, the shm file is omitted if [exclusive locking mode]
is set, as an optimization.  When [exclusive locking mode] is set,
SQLite uses heap memory in place of the memory-mapped shm file.

<h2>File Lifecycles</h2>

<p>When a WAL mode database is in active use, all three of the above

files usually exist.  Except, the Wal-Index file is omitted if
[exclusive locking mode] is set.

<p>If the last client using the database shuts down cleanly by
calling [sqlite3_close()], then a [checkpoint] is run automatically
in order to transfer all information from the wal file
over into the main database, and both the shm file
and the wal file are unlinked.  Thus, when the database is not in
use by any client, it is usually the case that only the main
................................................................................
shut down, or if the last client to disconnect was a read-only client,
then the final cleanup operation does not occur and the
shm and wal files may still exist on disk even when the database is
not in use.

<h2>Variations</h2>

<p>When [PRAGMA locking_mode=EXCLUSIVE] (exclusive locking mode) is set,
only a single client is allowed to have the database open at one time.  Since
only a single client can use the database, the shm file is omitted.
The single client uses a buffer in heap memory as a substitute for the
memory-mapped shm file.

<p>If a read/write client invokes
[sqlite3_file_control]([SQLITE_FCNTL_PERSIST_WAL]) prior to shutdown,
then at shutdown a checkpoint is still run, but the shm file and wal
file are not deleted.
This allows subsequent read-only clients to connect to and read the
database.

<tcl>hd_fragment walidxfmt {WAL-index format} {WAL-index File Format}</tcl>
<h1>The WAL-Index File Format</h1>

<p>
The WAL-index or "shm" file is used to coordinate access to the database
by multiple clients, and as a cache to help clients quickly locate frames
within the wal file.