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 indices | DESC keyword] on
index declarations.  (^The DESC keyword is ignored in indices 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 indices
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 indices] 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.)^
................................................................................
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 indices.)^  ^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 indices] that are
automatically created by [UNIQUE] or [PRIMARY KEY] constraints.)^</p>


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

<p>^In addition to the tables, indices, 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 indices 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 indices 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, 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 usually multiple entries in the sqlite_stat4 table for each index.

















The sqlite_stat4.sample column holds the content of an index entry in
the [record format].  The index entry stored is taken from the
index identified by sqlite_stat4.idx and sqlite_stat4.tbl.














The sqlite_stat4.nEq column holds a list of 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.
The sqlite_stat4.nLt holds a list of 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.


The sqlite_stat4.nDLt column holds a list of 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.



<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.

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.)^
................................................................................
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.