Documentation Source Text

<p>A [collating function] for each column is necessary in order to compute
the order of text fields.
^(SQLite defines three built-in collating functions:)^
</p>

<blockquote><table border=0 cellspacing=10>
<tr><td valign=top>BINARY
    <td> ^(The built-in BINARY collation compares strings byte by byte
        using the memcmp() function
        from the standard C library.)^
<tr><td valign=top>NOCASE
    <td> ^(The NOCASE collation is like BINARY except that uppercase
        ASCII characters ('A' through 'Z')
        are folded into their lowercase equivalents prior to running the
        comparison.  Note that only ASCII characters are case-folded.)^
        ^(NOCASE
        does not implement a general purpose unicode caseless comparison.)^
<tr><td valign=top>RTRIM
    <td> ^(RTRIM is like BINARY except that spaces at the end of the string
        are elided prior to comparison.)^
</table></blockquote>

<p>^Additional application-specific collating functions can be added to
SQLite using the [sqlite3_create_collation()] interface.</p>

<p>^The default collating function for all strings is BINARY.
^Alternative collating functions for table columns can be specified in the
[CREATE TABLE] statement using the COLLATE clause on the [column definition].
^When a column is indexed, the same collating function specified in the
[CREATE TABLE] statement is used for the column in the index, by default,
though this can be overridden using a COLLATE clause in the 
[CREATE INDEX] statement.

<h3>2.3 Representation Of SQL Tables</h3>

<p> ^Each ordinary SQL table in the database schema is represented on-disk
by a table b-tree.  ^Each entry in the table b-tree corresponds to a row
of the SQL table.  T^he [rowid] of the SQL table is the 64-bit signed
integer key for each entry in the table b-tree.</p>

<p> ^The content of each SQL table row is stored in the database file by
first combining the values in the various columns into a byte array
in the record format, then storing that byte array as the payload in
an entry in the table b-tree.  ^The order of values in the record is
the same as the order of columns in the SQL table definition.
^When an SQL table that includes an
[INTEGER PRIMARY KEY] column (which aliases the [rowid]) then that
column appears in the record as a NULL value.  ^SQLite will always use
the table b-tree key rather than the NULL value when referencing the
[INTEGER PRIMARY KEY] column.</p>

<p> ^If the [affinity] of a column is REAL and that column contains a
value that can be converted to an integer without loss of information
(if the value contains no fractional part and is not too large to be
represented as an integer) then the column may be stored in the record
as an integer.  ^SQLite will convert the value back to floating
point when extracting it from the record.</p>

<h3>2.4 Representation of WITHOUT ROWID Tables</h3>

<p>^If an SQL table is created using the "WITHOUT ROWID" clause at the
end of its CREATE TABLE statement, then that table is a [WITHOUT ROWID]
table and uses a different on-disk representation.  ^A WITHOUT ROWID
table uses an index b-tree rather than a table b-tree for storage.
^The key for each entry in the WITHOUT ROWID b-tree is a record composed
of the columns of the PRIMARY KEY followed by all remaining columns of
the table.  ^The primary key columns appear in the order they they were
declared in the PRIMARY KEY clause and the remaining columns appear in
the order they occur in the CREATE TABLE statement.

<p>^Hence, the content encoding for a WITHOUT ROWID table is the same
as the content encoding for an ordinary rowid table, except that the
order of the columns is rearranged so that PRIMARY KEY columns come
first, and the content is used as the key in an index b-tree rather

CREATE INDEX statement is true.
^Corresponding rows in the index and table b-trees share the same rowid
or primary key values and contain the same value for all indexed columns.</p>

<h4>2.5.1 Suppression of redundant columns in WITHOUT ROWID secondary indexed
</h4>

<p> ^In an index on a WITHOUT ROWID table, if one or more of the columns
of table PRIMARY KEY are also columns of the index, then the
indexed column is not repeated in the table-key suffix on the end of
the index record.  ^(As an example, consider the following SQL:

<blockquote><pre>
CREATE TABLE ex25(a,b,c,d,e,PRIMARY KEY(d,c,a)) WITHOUT rowid;
CREATE INDEX ex25ce ON ex25(c,e);
CREATE INDEX ex25acde ON ex25(a,c,d,e);
</pre></blockquote>

<p>Each row in the ex25ce index of the example above consists of a record
containing the following columns: c, e, d, a.  The first two columns are
the columns being indexed, c and e.  The remaining columns are the primary
key of the corresponding table row.  Normally, the primary key would be
columns d, c, and a, but because column c already appears earlier in the
index, it is omitted from the key suffix.)^</p>

<p>^(In the extreme case where the columns being indexed cover all columns
of the PRIMARY KEY, the index will consist of only the columns being
indexed.  The ex25acde example above demonstrates this.)^  ^Each entry in
the ex25acde index consists of only the columns a, c, d, and e, in that
order.</p>

<p> ^The suppression of redundant columns in the key suffix of an index
entry only occurs in WITHOUT ROWID tables.  ^In an ordinary rowid table,
the index entry always ends with the rowid even if the [INTEGER PRIMARY KEY]
column is one of the columns being indexed.</p>

<tcl>hd_fragment sqlite_master {sqlite_master} {sqlite_master table}</tcl>
<h3>2.6 Storage Of The SQL Database Schema</h3>

<p>^Page 1 of a database file is the root page of a table b-tree that

<h4>2.6.2 The sqlite_sequence table</h4>

<p>^The sqlite_sequence table is an internal table used to help implement
[AUTOINCREMENT].  ^The sqlite_sequence table is created automatically
whenever any ordinary table with an AUTOINCREMENT integer primary
key is created.  ^Once created, the sqlite_sequence table exists in the
sqlite_master table forever; it cannot be dropped.
^(The schema for the sqlite_sequence table is:

<blockquote><pre>
CREATE TABLE sqlite_sequence(name,seq);
</pre></blockquote>)^

<p>^There is a single row in the sqlite_sequence table for each ordinary
table that uses AUTOINCREMENT.  ^(The name of the table (as it appears in
sqlite_master.name) is in the sqlite_sequence.main field and the largest
[INTEGER PRIMARY KEY] ever used by that table is in the sqlite_sequence.seq
field.)^  ^New automatically generated integer primary keys for AUTOINCREMENT
tables are guaranteed to be larger than the sqlite_sequence.seq field for
that table.
^(If the sqlite_sequence.seq field of an AUTOINCREMENT table is already at
the largest integer value (9223372036854775807) then attempts to add new
rows to that table with an automatically generated integer primary will fail
with an [SQLITE_FULL] error.)^
^The sqlite_sequence.seq field is automatically updated if required when

<h4>2.6.3 The sqlite_stat1 table</h4>

<p>^The sqlite_stat1 is an internal table created by the [ANALYZE] command
and used to hold supplemental information about tables and indexes that the
query planner can use to help it find better ways of performing queries.
^Applications can update, delete from, insert into or drop the sqlite_stat1
table, but may not create or alter the sqlite_stat1 table.
^(The schema of the sqlite_stat1 table is as follows:

<blockquote><pre>
CREATE TABLE sqlite_stat1(tbl,idx,stat);
</pre></blockquote>)^

<p> ^(There is normally one row per index, with the index identified by the
name in the sqlite_stat1.idx column.)^  ^(The sqlite_stat1.tbl column is
the name of the table to which the index belongs.)^  ^(In each such row, 
the sqlite_stat.stat column will be
a string consisting of a list of integers followed by zero or more 
arguments.)^  ^The first integer in this
list is the approximate number of rows in the index and in the table.
^The second integer is the approximate average number of rows in the index
that have the same value in the first column of the index.  ^The third 
integer is the approximate average number of rows in the index that have 
the same value for the first two columns.  ^The N-th integer (for N>1) 
is the approximate average number of rows in 
the index which have the same value for the first N-1 columns.  ^For
a K-column index, there will be K+1 integers in the stat column.  ^If
the index is unique, then the last integer will be 1.

<p>^The list of integers in the stat column can optionally be followed
by arguments, each of which is a sequence of non-space characters.
^All arguments are preceded by a single space.
^Unrecognized arguments are silently ignored.

<p>^If the "unordered" argument is present, then the query planner assumes
that the index is unordered and will not use the index for a range query
or for sorting.


<p>^The "sz=NNN" argument (where NNN represents a sequence of 1 or more digits)

means that the average row size over all records of the table or

index is NNN bytes per row.  ^The SQLite query planner might use the
estimated row size information provided by the "sz=NNN" token
to help it choose smaller tables and indexes that require less disk I/O.

<p>New text tokens may be added to the end of the stat column in future
enhancements to SQLite.  For compatibility, unrecognized tokens at the end
of the stat column are silently ignored.

<p>^(If the sqlite_stat1.idx column is NULL, then the sqlite_stat1.stat
column contains a single integer which is the approximate number of
rows in the table identified by sqlite_stat1.tbl.)^

<tcl>hd_fragment stat2tab {sqlite_stat2}</tcl>
<h4>2.6.4 The sqlite_stat2 table</h4>

<p>The sqlite_stat2 is only created and is only used if SQLite is compiled
with SQLITE_ENABLE_STAT2 and if the SQLite version number is between
3.6.18 and 3.7.8.  The sqlite_stat2 table is neither read nor written by any

<p>The format for sqlite_stat2 is recorded here for legacy reference.  
Recent versions of SQLite no longer support sqlite_stat2 and the
sqlite_stat2 table, it is exists, is simply ignored.

<tcl>hd_fragment stat3tab {sqlite_stat3} SQLITE_STAT3</tcl>
<h4>2.6.5 The sqlite_stat3 table</h4>

<p>The sqlite_stat3 is only used if SQLite is compiled
with [SQLITE_ENABLE_STAT3] or [SQLITE_ENABLE_STAT4]
and if the SQLite version number is 3.7.9 or greater.
The sqlite_stat3 table is neither read nor written by any
version of SQLite before 3.7.9.
If the [SQLITE_ENABLE_STAT4] compile-time option is used and the
SQLite version number is 3.8.1 or greater, then sqlite_stat3 might
be read but not written.
The sqlite_stat3 table contains additional information
about the distribution of keys within an index, information that the
query planner can use to devise better and faster query algorithms.
^(The schema of the sqlite_stat3 table is as follows:

<blockquote><pre>
CREATE TABLE sqlite_stat3(tbl,idx,nEq,nLt,nDLt,sample);
</pre></blockquote>)^

<p>There are usually multiple entries in the sqlite_stat3 table for each index.
^(The sqlite_stat3.sample column holds the value of the left-most field of an
index identified by sqlite_stat3.idx and sqlite_stat3.tbl.)^
^(The sqlite_stat3.nEq column holds the approximate
number of entries in the index whose left-most column exactly matches
the sample.)^
^(The sqlite_stat3.nLt holds the approximate number of entries in the
index whose left-most column is less than the sample.)^
^(The sqlite_stat3.nDLt column holds the approximate
number of distinct left-most entries in the index that are less than
the sample.)^

<p>^There can be an arbitrary number of sqlite_stat3 entries per index.
The [ANALYZE] command will typically generate sqlite_stat3 tables
that contain between 10 and 40 samples that are distributed across
the key space and with large nEq values.

<tcl>hd_fragment stat4tab {sqlite_stat4} SQLITE_STAT4</tcl>
<h4>2.6.6 The sqlite_stat4 table</h4>

<p>The sqlite_stat4 is only created and is only used if SQLite is compiled
with [SQLITE_ENABLE_STAT4] and if the SQLite version number is
3.8.1 or greater.  The sqlite_stat4 table is neither read nor written by any
version of SQLite before 3.8.1.
The sqlite_stat4 table contains additional information
about the distribution of keys within an index or the distribution of
keys in the primary key of a [WITHOUT ROWID] table.
The query planner can sometimes use the additional information in
the sqlite_stat4 table to devise better and faster query algorithms.
^(The schema of the sqlite_stat4 table is as follows:

<blockquote><pre>
CREATE TABLE sqlite_stat4(tbl,idx,nEq,nLt,nDLt,sample);
</pre></blockquote>)^

<p>There are typically between 10 to 40 entries in the sqlite_stat4 table for
each index for which statistics are available, however these limits are
not hard bounds.
The meanings of the columns in the sqlite_stat4 table are as follows:

<center>
<table border="0" width="100%" cellpadding="10">
<tr><td valign="top" align="right">tbl:</td>
    <td>^(The sqlite_stat4.tbl column holds name of the table that owns
    the index that the row describes)^

<tr><td valign="top" align="right">idx:</td>
    <td>^(The sqlite_stat4.idx column holds name of the index that the
    row describes, or in the case of
    an sqlite_stat4 entry for a [WITHOUT ROWID] table, the
    name of the table itself.)^

<tr><td valign="top" align="right">sample:</td>
    <td>^(The sqlite_stat4.sample column holds a BLOB
    in the [record format] that encodes the indexed columns followed by
    the rowid for a rowid table or by the columns of the primary key 
    for a WITHOUT ROWID table.)^
    ^(The sqlite_stat4.sample BLOB for the WITHOUT ROWID table itself 
    contains just the columns of the primary key.)^
    Let the number of columns encoded by the sqlite_stat4.sample blob be N.
    ^For indexes on an ordinary rowid table, N will be one more than the number 
    of columns indexed.
    ^For indexes on WITHOUT ROWID tables, N will be the number of columns
    indexed plus the number of columns in the primary key.
    ^For a WITHOUT ROWID table, N will be the number of columns in the
    primary key.

<tr><td valign="top" align="right">nEq:</td>
    <td>^(The sqlite_stat4.nEq column holds a list of N integers where 
    the K-th integer is the approximate number of entries in the index
    whose left-most K columns exactly match the K left-most columns
    of the sample.)^

<tr><td valign="top" align="right">nLt:</td>
    <td>^(The sqlite_stat4.nLt column holds a list of N integers where
    the K-th integer is the approximate number of entries in the
    index whose K left-most columns are collectively less than the 
    K left-most columns of the sample.)^

<tr><td valign="top" align="right">nDLt:</td>
    <td>^(The sqlite_stat4.nDLt column holds a list of N integers where
    the K-th integers is the approximate
    number of entries in the index that are distinct in the first K columns and
    that are whose left-most K columns are collectively less than the left-most
    K columns of the sample.)^
</table>
</center>

<p>The sqlite_stat4 is a generalization of the sqlite_stat3 table.  The
sqlite_stat3 table provides information about the left-most column of an
index whereas the sqlite_stat4 table provides information about all columns
of the index.

<p>^There can be an arbitrary number of sqlite_stat4 entries per index.
The [ANALYZE] command will typically generate sqlite_stat4 tables
that contain between 10 and 40 samples that are distributed across
the key space and with large nEq values.

<tcl>hd_fragment rollbackjournal {rollback journal format}</tcl>
<h2>3.0 The Rollback Journal</h2>

<p>A valid rollback journal begins with a header in the following format:</p>

<center>
<i>Rollback Journal Header Format</i><br>
<table width="80%" border=1>
<tr><th>Offset<th>Size<th>Description
<tr><td valign=top align=center>0
    <td valign=top align=center>8
    <td>Header string:  0xd9, 0xd5, 0x05, 0xf9, 0x20, 0xa1, 0x63, 0xd7
<tr><td valign=top align=center>8
    <td valign=top align=center>4
    <td>The "Page Count" - The number of pages in the next segment of the 
        journal, or -1 to
        mean all content to the end of the file
<tr><td valign=top align=center>12
    <td valign=top align=center>4
    <td>A random nonce for the checksum
<tr><td valign=top align=center>16
    <td valign=top align=center>4
    <td>Initial size of the database in pages
<tr><td valign=top align=center>20
    <td valign=top align=center>4
    <td>Size of a disk sector assumed by the process that wrote this
        journal.
<tr><td valign=top align=center>24
    <td valign=top align=center>4
    <td>Size of pages in this journal.
</table>
</center>

<p>^A rollback journal header is padded with zeros out to the size of a 
single sector (as defined by the sector size integer at offset 20).
The header is in a sector by itself so that if a power loss occurs while
writing the sector, information that follows the header will be

in the journal header) be N.
Then the format of a page record is as follows:</p>

<center>
<i>Rollback Journal Page Record Format</i><br>
<table width="80%" border=1>
<tr><th>Offset<th>Size<th>Description
<tr><td valign=top align=center>0
    <td valign=top align=center>4
    <td>The page number in the database file
<tr><td valign=top align=center>4
    <td valign=top align=center>N
    <td>Original content of the page prior to the start of the transaction
<tr><td valign=top align=center>N+4
    <td valign=top align=center>4
    <td>Checksum
</table>
</center>


<p>^(The checksum is an unsigned 32-bit integer computed as follows:</p>

<ol>

    <i>database</i>, or on all attached databases if <i>database</i>
    is omitted.  ^If [write-ahead log] mode is disabled, this pragma is a
    harmless no-op.</p>

    <p>^Invoking this
    pragma without an argument is equivalent to calling the
    [sqlite3_wal_checkpoint()] C interface.</p>
    ^(Invoking this pragma with an argument is equivalent to calling the
    [sqlite3_wal_checkpoint_v2()] C interface with a 
    [SQLITE_CHECKPOINT_PASSIVE | 3rd parameter]
    corresponding to the argument:)^

    <dl>
    ^(<dt>PASSIVE<dd>
      Checkpoint as many frames as possible without waiting for any database 
      readers or writers to finish. Sync the db file if all frames in the log
      are checkpointed.)^ ^(This mode is the same as calling 
      sqlite3_wal_checkpoint().)^ ^(The
      [sqlite3_busy_handler|busy-handler callback] is never invoked.)^
   
    ^(<dt>FULL<dd>
      This mode blocks 
      (invokes the [sqlite3_busy_handler|busy-handler callback])
      until there is no
      database writer and all readers are reading from the most recent database
      snapshot. It then checkpoints all frames in the log file and syncs the
      database file.)^ ^(FULL blocks database writers while it is
      running, but not database readers.)^
   
    ^(<dt>RESTART<dd>
      This mode works the same way as SQLITE_CHECKPOINT_FULL, except after 
      checkpointing the log file it blocks (calls the 
      [sqlite3_busy_handler|busy-handler callback])
      until all readers are reading from the database file only.)^ This ensures 
      that the next client to write to the database file restarts the log file 
      from the beginning. ^(RESTART blocks database writers while it is
      running, but not database readers.)^
    </dl>


    <p>^The wal_checkpoint pragma returns a single row with three
    integer columns.  ^The first column is usually 0 but will be
    1 if a RESTART or FULL checkpoint was blocked from completing,
    for example because another thread or process was actively

Pragma wal_autocheckpoint {
    <p><b>PRAGMA wal_autocheckpoint;<br>
     PRAGMA wal_autocheckpoint=</b><i>N</i><b>;</b></p>

    <p>^This pragma queries or sets the [write-ahead log] 
    [checkpointing | auto-checkpoint] interval.
    ^When the [write-ahead log] is enabled (via the
    [journal_mode pragma]) a checkpoint will be run automatically whenever
    the write-ahead log equals or exceeds <i>N</i> pages in length.
    ^Setting the auto-checkpoint size to zero or a negative value
    turns auto-checkpointing off.</p>
    
    <p>^This pragma is a wrapper around the
    [sqlite3_wal_autocheckpoint()] C interface.
    ^All automatic checkpoints are [sqlite3_wal_checkpoint_v2|PASSIVE].</p>

    <p>^Autocheckpointing is enabled by default with an interval
    of 1000 or [SQLITE_DEFAULT_WAL_AUTOCHECKPOINT].</p>

}

Pragma ignore_check_constraints {