Documentation Source Text

reading and writing format 2 was added in SQLite version 3.1.3 
on 2005-02-19.</li>
<li value=3>Format 3 adds the ability of extra columns added by
[ALTER TABLE | ALTER TABLE ... ADD COLUMN] to have non-NULL default
values.  This capability was added in SQLite version 3.1.4 
on 2005-03-11.</li>
<li value=4>^Format 4 causes SQLite to respect the
[descending indexes | DESC keyword] on
index declarations.  (^The DESC keyword is ignored in indexes for 
formats 1, 2, and 3.)
^Format 4 also adds two new boolean record type values ([serial types]
8 and 9.)  Support for format 4 was added in SQLite 3.3.0 on
2006-01-10.</li>
</ol>

<p>^New database files created by SQLite use format 4 by default.

b-trees are identified by their root page number.</p>

<p>A b-tree page is either a table b-tree page or an index b-tree page.
All pages within each complete b-tree are of the same type: either table
or index.  There is a one table b-trees in the database file
for each rowid table in the database schema, including system tables
such as sqlite_master.  There is one index b-trees
in the database file for each index in the schema, including implied indexes
created by uniqueness constraints.  There are no b-trees associated with
[virtual tables].  Specific virtual table implementations might make use
of [shadow tables] for storage, but those shadow tables will have separate
entries in the database schema.  [WITHOUT ROWID] tables use index b-trees
rather than a table b-trees, so there is one
index b-tree in the database file for each [WITHOUT ROWID] table.
The b-tree corresponding to the sqlite_master table is always a table

<p>^(The sqlite_master.type column will be one
of the following text strings:  'table', 'index', 'view', or 'trigger'
according to the type of object defined.  The 'table' string is used
for both ordinary and [virtual tables].)^</p>

</p>^(The sqlite_master.name column will hold the name of the object.)^
^([UNIQUE] and [PRIMARY KEY] constraints on tables cause SQLite to create
[internal indexes] with names of the form "sqlite_autoindex_TABLE_N"
where TABLE is replaced by the name of the table that contains the
constraint and N is an integer beginning with 1 and increasing by one
with each constraint seen in the table definition.)^
^(In a [WITHOUT ROWID] table, there is no sqlite_master entry for the
PRIMARY KEY, but the "sqlite_autoindex_TABLE_N" name is set aside
for the PRIMARY KEY as if the sqlite_master entry did exist.  This
will affect the numbering of subsequent UNIQUE constraints.)^
^The "sqlite_autoindex_TABLE_N" name is never allocated for an
[INTEGER PRIMARY KEY], either in rowid tables or WITHOUT ROWID tables.
</p>

<p>The sqlite_master.tbl_name column holds the name of a table or view
that the object is associated with.  ^For a table or view, the
tbl_name column is a copy of the name column.  ^For an index, the tbl_name
is the name of the table that is indexed.  ^For a trigger, the tbl_name
column stores the name of the table or view that causes the trigger 
to fire.</p>

<p>^(The sqlite_master.rootpage column stores the page number of the root
b-tree page for tables and indexes.)^  ^For rows that define views, triggers,
and virtual tables, the rootpage column is 0 or NULL.</p>

<p>^(The sqlite_master.sql column stores SQL text that describes the
object.  This SQL text is a [CREATE TABLE], [CREATE VIRTUAL TABLE],
[CREATE INDEX],
[CREATE VIEW], or [CREATE TRIGGER] statement that if evaluated against
the database file when it is the main database of a [database connection]

<li>^All spaces following the first two keywords are converted into a single
space.
</ul>

<p>^(The text in the sqlite_master.sql column is a copy of the original
CREATE statement text that created the object, except normalized as
described above and as modified by subsequent [ALTER TABLE] statements.)^
^(The sqlite_master.sql is NULL for the [internal indexes] that are
automatically created by [UNIQUE] or [PRIMARY KEY] constraints.)^</p>


<tcl>hd_fragment intschema {internal schema objects} \
{internal schema object} {internal index} {internal indexes} \
{internal table} {internal tables}</tcl>
<h4>2.6.1 Internal Schema Objects</h4>

<p>^In addition to the tables, indexes, views, and triggers created by
the application and/or the developer using CREATE statements SQL, the
sqlite_master table may contain zero or more entries for 
<i>internal schema objects</i> that are created by SQLite for its 
own internal use.  ^The names of internal schema objects
always begin with "sqlite_" and any table, index, view, or trigger
whose name begins with "sqlite_" is an internal schema object.
^SQLite prohibits applications from creating objects whose names begin

^Application code can delete all entries from the sqlite_sequence table,
but application code cannot drop the sqlite_sequence table.

<tcl>hd_fragment stat1tab {sqlite_stat1} SQLITE_STAT1 </tcl>
<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);

<p>^The list of integers in the stat column can also optionally be followed
by a token of the form "sz=NNN" where NNN is an unsigned integer.  The
"sz=NNN" token, if present, must be separated from the last integer by a
single space.  ^The "sz=NNN" token 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>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>

<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, information that the
query planner can use 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 size 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.