Documentation Source Text

   <ul>
   <li>  [SQLITE_OPEN_MAIN_DB]
   <li>  [SQLITE_OPEN_MAIN_JOURNAL]
   <li>  [SQLITE_OPEN_TEMP_DB]
   <li>  [SQLITE_OPEN_TEMP_JOURNAL]
   <li>  [SQLITE_OPEN_TRANSIENT_DB]
   <li>  [SQLITE_OPEN_SUBJOURNAL]
   <li>  [SQLITE_OPEN_MASTER_JOURNAL]
   </ul>
   The file I/O implementation can use the object type flags to
   changes the way it deals with files.  For example, an application
   that does not care about crash recovery or rollback, might make
   the open of a journal file a no-op.  Writes to this journal are
   also a no-op.  Any attempt to read the journal returns [SQLITE_IOERR].
   Or the implementation might recognize the a database file will
................................................................................
  The argument to [sqlite3_mutex_alloc()] should be 
  [SQLITE_MUTEX_FAST] or [SQLITE_MUTEX_RECURSIVE] for non-recursive
  and recursive mutexes, respectively.  If the underlying system does
  not provide non-recursive mutexes, then a recursive mutex can be
  substituted in that case.  The argument to [sqlite3_mutex_alloc()]
  can also be a constant designating one of several static mutexes:
  <ul>
  <li>  [SQLITE_MUTEX_STATIC_MASTER]
  <li>  [SQLITE_MUTEX_STATIC_MEM]
  <li>  [SQLITE_MUTEX_STATIC_MEM2]
  <li>  [SQLITE_MUTEX_STATIC_PRNG]
  <li>  [SQLITE_MUTEX_STATIC_LRU]
  </ul>
  These static mutexes are reserved for use internally by SQLite
  and should not be used by the application.  The static mutexes

   <ul>
   <li>  [SQLITE_OPEN_MAIN_DB]
   <li>  [SQLITE_OPEN_MAIN_JOURNAL]
   <li>  [SQLITE_OPEN_TEMP_DB]
   <li>  [SQLITE_OPEN_TEMP_JOURNAL]
   <li>  [SQLITE_OPEN_TRANSIENT_DB]
   <li>  [SQLITE_OPEN_SUBJOURNAL]
   <li>  [SQLITE_OPEN_SUPER_JOURNAL]
   </ul>
   The file I/O implementation can use the object type flags to
   changes the way it deals with files.  For example, an application
   that does not care about crash recovery or rollback, might make
   the open of a journal file a no-op.  Writes to this journal are
   also a no-op.  Any attempt to read the journal returns [SQLITE_IOERR].
   Or the implementation might recognize the a database file will
................................................................................
  The argument to [sqlite3_mutex_alloc()] should be 
  [SQLITE_MUTEX_FAST] or [SQLITE_MUTEX_RECURSIVE] for non-recursive
  and recursive mutexes, respectively.  If the underlying system does
  not provide non-recursive mutexes, then a recursive mutex can be
  substituted in that case.  The argument to [sqlite3_mutex_alloc()]
  can also be a constant designating one of several static mutexes:
  <ul>
  <li>  [SQLITE_MUTEX_STATIC_MAIN]
  <li>  [SQLITE_MUTEX_STATIC_MEM]
  <li>  [SQLITE_MUTEX_STATIC_MEM2]
  <li>  [SQLITE_MUTEX_STATIC_PRNG]
  <li>  [SQLITE_MUTEX_STATIC_LRU]
  </ul>
  These static mutexes are reserved for use internally by SQLite
  and should not be used by the application.  The static mutexes

<h2> Acquiring A Read Lock</h2>

<img src="images/ac/commit-1.gif" align="right" hspace="15">

<p>Before SQLite can write to a database, it must first read
the database to see what is there already.  Even if it is just
appending new data, SQLite still has to read in the database
schema from the <b>sqlite_master</b> table so that it can know
how to parse the INSERT statements and discover where in the
database file the new information should be stored.</p>

<p>The first step toward reading from the database file
is obtaining a shared lock on the database file.  A "shared"
lock allows two or more database connections to read from the
database file at the same time.  But a shared lock prevents
................................................................................
<ul>
<li>The rollback journal exists.
<li>The rollback journal is not an empty file.
<li>There is no reserved lock on the main database file.
<li>The header of the rollback journal is well-formed and in particular
    has not been zeroed out.
<li>The rollback journal does not
contain the name of a master journal file (see
<a href="#section_5_5">section 5.5</a> below) or if does
contain the name of a master journal, then that master journal
file exists.
</ul>

<p>The presence of a hot journal is our indication
that a previous process was trying to commit a transaction but
it aborted for some reason prior to the completion of the
commit.  A hot journal means that
................................................................................
we are not making the distinction between information in the
operating system cache and information that is on disk.  All of
these factors still apply in a multi-file commit scenario.  They
just take up a lot of space in the diagrams and they do not add
any new information, so they are omitted here.</p>

<br clear="both">
<tcl>hd_fragment masterjrnl</tcl>
<h2> The Master Journal File</h2>
<img src="images/ac/multi-1.gif" align="right" hspace="15">

<p>The next step in a multi-file commit is the creation of a
"master journal" file.  The name of the master journal file is
the same name as the original database filename (the database
that was opened using the 
<a href="c3ref/open.html">sqlite3_open()</a> interface,
not one of the <a href="lang_attach.html">ATTACHed</a> auxiliary
databases) with the text "<b>-mj</b><i>HHHHHHHH</i>" appended where
<i>HHHHHHHH</i> is a random 32-bit hexadecimal number.  The
random <i>HHHHHHHH</i> suffix changes for every new master journal.</p>

<p><i>(Nota bene: The formula for computing the master journal filename
given in the previous paragraph corresponds to the implementation as
of SQLite version 3.5.0.  But this formula is not part of the SQLite
specification and is subject to change in future releases.)</i></p>

<p>Unlike the rollback journals, the master journal does not contain
any original database page content.  Instead, the master journal contains
the full pathnames for rollback journals for every database that is
participating in the transaction.</p>

<p>After the master journal is constructed, its content is flushed
to disk before any further actions are taken.  On Unix, the directory
that contains the master journal is also synced in order to make sure
the master journal file will appear in the directory following a
power failure.</p>

<p>The purpose of the master journal is to ensure that multi-file
transactions are atomic across a power-loss.  But if the database files
have other settings that compromise integrity across a power-loss event
(such as [PRAGMA synchronous=OFF] or [PRAGMA journal_mode=MEMORY]) then
the creation of the master journal is omitted, as an optimization.

<br clear="both">
<tcl>hd_fragment multijrnlupdate</tcl>
<h2> Updating Rollback Journal Headers</h2>
<img src="images/ac/multi-2.gif" align="right" hspace="15">

<p>The next step is to record the full pathname of the master journal file
in the header of every rollback journal.  Space to hold the master
journal filename was reserved at the beginning of each rollback journal
as the rollback journals were created.</p>

<p>The content of each rollback journal is flushed to disk both before
and after the master journal filename is written into the rollback
journal header.  It is important to do both of these flushes.  Fortunately,
the second flush is usually inexpensive since typically only a single
page of the journal file (the first page) has changed.</p>

<p>This step is analogous to 
<a href="#section_3_7">step 3.7</a> in the single-file commit
scenario described above.</p>
................................................................................


<br clear="both">
<a name="section_5_5"></a>
<h2> Delete The Master Journal File</h2>
<img src="images/ac/multi-4.gif" align="right" hspace="15">

<p>The next step is to delete the master journal file.
This is the point where the multi-file transaction commits.
This step corresponds to 
<a href="#section_3_11">step 3.11</a> in the single-file
commit scenario where the rollback journal is deleted.</p>

<p>If a power failure or operating system crash occurs at this
point, the transaction will not rollback when the system reboots
even though there are rollback journals present.  The
difference is the master journal pathname in the header of the
rollback journal.  Upon restart, SQLite only considers a journal
to be hot and will only playback the journal if there is no
master journal filename in the header (which is the case for
a single-file commit) or if the master journal file still
exists on disk.</p>

<br clear="both">
<tcl>hd_fragment cleanup</tcl>
<h2> Clean Up The Rollback Journals</h2>
<img src="images/ac/multi-5.gif" align="right" hspace="15">

<h2> Acquiring A Read Lock</h2>

<img src="images/ac/commit-1.gif" align="right" hspace="15">

<p>Before SQLite can write to a database, it must first read
the database to see what is there already.  Even if it is just
appending new data, SQLite still has to read in the database
schema from the <b>sqlite_schema</b> table so that it can know
how to parse the INSERT statements and discover where in the
database file the new information should be stored.</p>

<p>The first step toward reading from the database file
is obtaining a shared lock on the database file.  A "shared"
lock allows two or more database connections to read from the
database file at the same time.  But a shared lock prevents
................................................................................
<ul>
<li>The rollback journal exists.
<li>The rollback journal is not an empty file.
<li>There is no reserved lock on the main database file.
<li>The header of the rollback journal is well-formed and in particular
    has not been zeroed out.
<li>The rollback journal does not
contain the name of a super-journal file (see
<a href="#section_5_5">section 5.5</a> below) or if does
contain the name of a super-journal, then that super-journal
file exists.
</ul>

<p>The presence of a hot journal is our indication
that a previous process was trying to commit a transaction but
it aborted for some reason prior to the completion of the
commit.  A hot journal means that
................................................................................
we are not making the distinction between information in the
operating system cache and information that is on disk.  All of
these factors still apply in a multi-file commit scenario.  They
just take up a lot of space in the diagrams and they do not add
any new information, so they are omitted here.</p>

<br clear="both">
<tcl>hd_fragment sprjrnl</tcl>
<h2> The Super-Journal File</h2>
<img src="images/ac/multi-1.gif" align="right" hspace="15">

<p>The next step in a multi-file commit is the creation of a
"super-journal" file.  The name of the super-journal file is
the same name as the original database filename (the database
that was opened using the 
<a href="c3ref/open.html">sqlite3_open()</a> interface,
not one of the <a href="lang_attach.html">ATTACHed</a> auxiliary
databases) with the text "<b>-mj</b><i>HHHHHHHH</i>" appended where
<i>HHHHHHHH</i> is a random 32-bit hexadecimal number.  The
random <i>HHHHHHHH</i> suffix changes for every new super-journal.</p>

<p><i>(Nota bene: The formula for computing the super-journal filename
given in the previous paragraph corresponds to the implementation as
of SQLite version 3.5.0.  But this formula is not part of the SQLite
specification and is subject to change in future releases.)</i></p>

<p>Unlike the rollback journals, the super-journal does not contain
any original database page content.  Instead, the super-journal contains
the full pathnames for rollback journals for every database that is
participating in the transaction.</p>

<p>After the super-journal is constructed, its content is flushed
to disk before any further actions are taken.  On Unix, the directory
that contains the super-journal is also synced in order to make sure
the super-journal file will appear in the directory following a
power failure.</p>

<p>The purpose of the super-journal is to ensure that multi-file
transactions are atomic across a power-loss.  But if the database files
have other settings that compromise integrity across a power-loss event
(such as [PRAGMA synchronous=OFF] or [PRAGMA journal_mode=MEMORY]) then
the creation of the super-journal is omitted, as an optimization.

<br clear="both">
<tcl>hd_fragment multijrnlupdate</tcl>
<h2> Updating Rollback Journal Headers</h2>
<img src="images/ac/multi-2.gif" align="right" hspace="15">

<p>The next step is to record the full pathname of the super-journal file
in the header of every rollback journal.  Space to hold the 
super-journal filename was reserved at the beginning of each rollback journal
as the rollback journals were created.</p>

<p>The content of each rollback journal is flushed to disk both before
and after the super-journal filename is written into the rollback
journal header.  It is important to do both of these flushes.  Fortunately,
the second flush is usually inexpensive since typically only a single
page of the journal file (the first page) has changed.</p>

<p>This step is analogous to 
<a href="#section_3_7">step 3.7</a> in the single-file commit
scenario described above.</p>
................................................................................


<br clear="both">
<a name="section_5_5"></a>
<h2> Delete The Master Journal File</h2>
<img src="images/ac/multi-4.gif" align="right" hspace="15">

<p>The next step is to delete the super-journal file.
This is the point where the multi-file transaction commits.
This step corresponds to 
<a href="#section_3_11">step 3.11</a> in the single-file
commit scenario where the rollback journal is deleted.</p>

<p>If a power failure or operating system crash occurs at this
point, the transaction will not rollback when the system reboots
even though there are rollback journals present.  The
difference is the super-journal pathname in the header of the
rollback journal.  Upon restart, SQLite only considers a journal
to be hot and will only playback the journal if there is no
super-journal filename in the header (which is the case for
a single-file commit) or if the super-journal file still
exists on disk.</p>

<br clear="both">
<tcl>hd_fragment cleanup</tcl>
<h2> Clean Up The Rollback Journals</h2>
<img src="images/ac/multi-5.gif" align="right" hspace="15">

}</tclscript>


<p>The ".tables" command is similar to setting list mode then
executing the following query:</p>

<tclscript>DisplayCode {
SELECT name FROM sqlite_master 
WHERE type IN ('table','view') AND name NOT LIKE 'sqlite_%'
ORDER BY 1
} </tclscript>

<p>But the ".tables" command does more.  It queries the sqlite_master table
for all [attached] databases, not just the primary database.  And it arranges
its output into neat columns.

<p>The ".indexes" command works in a similar way to list all of
the indexes. If the ".indexes" command is given an argument which is
the name of a table, then it shows just indexes on that table.

................................................................................
}</tclscript>


<p>The ".schema" command is roughly the same as setting
list mode, then entering the following query:</p>

<tclscript>DisplayCode {
SELECT sql FROM sqlite_master
ORDER BY tbl_name, type DESC, name
} </tclscript>

<p>As with ".tables", the ".schema" command shows the schema for
all [attached] databases.  If you only want to see the schema for
a single database (perhaps "main") then you can add an argument
to ".schema" to restrict its output:
................................................................................
not its representation on disk.  This means, for example, that a [VACUUM]
or similar data-preserving transformation does not change the hash.

<p>The ".sha3sum" command supports options "--sha3-224", "--sha3-256", 
"--sha3-384", and "--sha3-512" to define which variety of SHA3 to use
for the hash.  The default is SHA3-256.

<p>The database schema (in the [sqlite_master] table) is not normally
included in the hash, but can be added by the "--schema" option.

<p>The ".sha3sum" command takes a single optional argument which is a
[LIKE] pattern.  If this option is present, only tables whose names match
the [LIKE] pattern will be hashed.

<p>The ".sha3sum" command is implemented with the help of the

}</tclscript>


<p>The ".tables" command is similar to setting list mode then
executing the following query:</p>

<tclscript>DisplayCode {
SELECT name FROM sqlite_schema 
WHERE type IN ('table','view') AND name NOT LIKE 'sqlite_%'
ORDER BY 1
} </tclscript>

<p>But the ".tables" command does more.  It queries the sqlite_schema table
for all [attached] databases, not just the primary database.  And it arranges
its output into neat columns.

<p>The ".indexes" command works in a similar way to list all of
the indexes. If the ".indexes" command is given an argument which is
the name of a table, then it shows just indexes on that table.

................................................................................
}</tclscript>


<p>The ".schema" command is roughly the same as setting
list mode, then entering the following query:</p>

<tclscript>DisplayCode {
SELECT sql FROM sqlite_schema
ORDER BY tbl_name, type DESC, name
} </tclscript>

<p>As with ".tables", the ".schema" command shows the schema for
all [attached] databases.  If you only want to see the schema for
a single database (perhaps "main") then you can add an argument
to ".schema" to restrict its output:
................................................................................
not its representation on disk.  This means, for example, that a [VACUUM]
or similar data-preserving transformation does not change the hash.

<p>The ".sha3sum" command supports options "--sha3-224", "--sha3-256", 
"--sha3-384", and "--sha3-512" to define which variety of SHA3 to use
for the hash.  The default is SHA3-256.

<p>The database schema (in the [sqlite_schema] table) is not normally
included in the hash, but can be added by the "--schema" option.

<p>The ".sha3sum" command takes a single optional argument which is a
[LIKE] pattern.  If this option is present, only tables whose names match
the [LIKE] pattern will be hashed.

<p>The ".sha3sum" command is implemented with the help of the

  you can type "<b>.tables</b>" to get a list of all tables.  Or you
  can type "<b>.schema</b>" to see the complete database schema including
  all tables and indices.  Either of these commands can be followed by
  a LIKE pattern that will restrict the tables that are displayed.</p>

  <p>From within a C/C++ program (or a script using Tcl/Ruby/Perl/Python
  bindings) you can get access to table and index names by doing a SELECT
  on a special table named "<b>SQLITE_MASTER</b>".  Every SQLite database
  has an SQLITE_MASTER table that defines the schema for the database.
  The SQLITE_MASTER table looks like this:</p>
<blockquote><pre>
CREATE TABLE sqlite_master (
  type TEXT,
  name TEXT,
  tbl_name TEXT,
  rootpage INTEGER,
  sql TEXT
);
</pre></blockquote>
  <p>For tables, the <b>type</b> field will always be <b>'table'</b> and the
  <b>name</b> field will be the name of the table.  So to get a list of
  all tables in the database, use the following SELECT command:</p>
<blockquote><pre>
SELECT name FROM sqlite_master
WHERE type='table'
ORDER BY name;
</pre></blockquote>
  <p>For indices, <b>type</b> is equal to <b>'index'</b>, <b>name</b> is the
  name of the index and <b>tbl_name</b> is the name of the table to which
  the index belongs.  For both tables and indices, the <b>sql</b> field is
  the text of the original CREATE TABLE or CREATE INDEX statement that
  created the table or index.  For automatically created indices (used
  to implement the PRIMARY KEY or UNIQUE constraints) the <b>sql</b> field
  is NULL.</p>

  <p>The SQLITE_MASTER table cannot be modified using UPDATE, INSERT, 
  or DELETE (except under
  [PRAGMA writable_schema|extraordinary conditions]).  
  The SQLITE_MASTER table is automatically updated by commands like
  CREATE TABLE, CREATE INDEX, DROP TABLE, and DROP INDEX.</p>

  <p>Temporary tables do not appear in the SQLITE_MASTER table.  Temporary
  tables and their indices and triggers occur in another special table
  named SQLITE_TEMP_MASTER.  SQLITE_TEMP_MASTER works just like SQLITE_MASTER
  except that it is only visible to the application that created the 
  temporary tables.  To get a list of all tables, both permanent and
  temporary, one can use a command similar to the following:
<blockquote><pre>
SELECT name FROM 
   (SELECT * FROM sqlite_master UNION ALL
    SELECT * FROM sqlite_temp_master)
WHERE type='table'
ORDER BY name
</pre></blockquote>
}

faq {
  Are there any known size limits to SQLite databases?

  you can type "<b>.tables</b>" to get a list of all tables.  Or you
  can type "<b>.schema</b>" to see the complete database schema including
  all tables and indices.  Either of these commands can be followed by
  a LIKE pattern that will restrict the tables that are displayed.</p>

  <p>From within a C/C++ program (or a script using Tcl/Ruby/Perl/Python
  bindings) you can get access to table and index names by doing a SELECT
  on a special table named "<b>SQLITE_SCHEMA</b>".  Every SQLite database
  has an SQLITE_SCHEMA table that defines the schema for the database.
  The SQLITE_SCHEMA table looks like this:</p>
<blockquote><pre>
CREATE TABLE sqlite_schema (
  type TEXT,
  name TEXT,
  tbl_name TEXT,
  rootpage INTEGER,
  sql TEXT
);
</pre></blockquote>
  <p>For tables, the <b>type</b> field will always be <b>'table'</b> and the
  <b>name</b> field will be the name of the table.  So to get a list of
  all tables in the database, use the following SELECT command:</p>
<blockquote><pre>
SELECT name FROM sqlite_schema
WHERE type='table'
ORDER BY name;
</pre></blockquote>
  <p>For indices, <b>type</b> is equal to <b>'index'</b>, <b>name</b> is the
  name of the index and <b>tbl_name</b> is the name of the table to which
  the index belongs.  For both tables and indices, the <b>sql</b> field is
  the text of the original CREATE TABLE or CREATE INDEX statement that
  created the table or index.  For automatically created indices (used
  to implement the PRIMARY KEY or UNIQUE constraints) the <b>sql</b> field
  is NULL.</p>

  <p>The SQLITE_SCHEMA table cannot be modified using UPDATE, INSERT, 
  or DELETE (except under
  [PRAGMA writable_schema|extraordinary conditions]).  
  The SQLITE_SCHEMA table is automatically updated by commands like
  CREATE TABLE, CREATE INDEX, DROP TABLE, and DROP INDEX.</p>

  <p>Temporary tables do not appear in the SQLITE_SCHEMA table.  Temporary
  tables and their indices and triggers occur in another special table
  named SQLITE_TEMP_MASTER.  SQLITE_TEMP_MASTER works just like SQLITE_SCHEMA
  except that it is only visible to the application that created the 
  temporary tables.  To get a list of all tables, both permanent and
  temporary, one can use a command similar to the following:
<blockquote><pre>
SELECT name FROM 
   (SELECT * FROM sqlite_schema UNION ALL
    SELECT * FROM sqlite_temp_schema)
WHERE type='table'
ORDER BY name
</pre></blockquote>
}

faq {
  Are there any known size limits to SQLite databases?

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

<tcl>hd_fragment {btypes} {index b-tree} {table b-tree}</tcl>
<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 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-tree
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
b-tree and always has a root page of 1.
The sqlite_master table contains the root page number for every other 
table and index in the database file.</p>

<p>Each entry in a table b-tree consists of a 64-bit signed integer key
and up to 2147483647 bytes of arbitrary data.  (The key of a table b-tree
corresponds to the [rowid] of the SQL table that the b-tree implements.)
Interior table b-trees hold only keys and pointers to children.
All data is contained in the table b-tree leaves.</p>
................................................................................
page number is the parent b-tree page.
</ol>

<p>^In any database file that contains ptrmap pages, all b-tree root pages 
must come before any non-root b-tree page, cell payload overflow page, or
freelist page.  This restriction ensures that a root page will never
be moved during an auto-vacuum or incremental-vacuum.  The auto-vacuum
logic does not know how to update the root_page field of the sqlite_master
table and so it is necessary to prevent root pages from being moved
during an auto-vacuum in order to preserve the integrity of the
sqlite_master table.  ^Root pages are moved to the beginning of the
database file by the CREATE TABLE, CREATE INDEX, DROP TABLE, and
DROP INDEX operations.</p>

<h1>Schema Layer</h1>

<p>The foregoing text describes low-level aspects of the SQLite file
format.  The b-tree mechanism provides a powerful and efficient means of
................................................................................
and the index.

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

<h2>Storage Of The SQL Database Schema</h2>

<p>^Page 1 of a database file is the root page of a table b-tree that
holds a special table named "sqlite_master" (or "sqlite_temp_master" in
the case of a TEMP database) which stores the complete
database schema.  ^(The structure of the sqlite_master table is as
if it had been created using the following SQL:</p>

<blockquote><pre>
CREATE TABLE sqlite_master(
  type text,
  name text,
  tbl_name text,
  rootpage integer,
  sql text
);
</pre></blockquote>)^

<p>^The sqlite_master table contains one row for each table, index, view,
and trigger (collectively "objects") in the database schema, except there
is no entry for the sqlite_master table itself.  ^The sqlite_master table
contains entries for [internal schema objects] in addition to application-
and programmer-defined objects.


<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]
would recreate the object.)^  The text is usually a copy of the original
statement used to create the object but with normalizations applied so
that the text conforms to the following rules:
................................................................................
<li>^Any database name qualifier that occurs prior to the name of the
object being created is removed.
<li>^Leading spaces are removed.
<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>
<h3>Internal Schema Objects</h3>

<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
with "sqlite_".  

................................................................................
<tcl>hd_fragment seqtab {sqlite_sequence}</tcl>
<h3>The sqlite_sequence table</h3>

<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 inserted into 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

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

<tcl>hd_fragment {btypes} {index b-tree} {table b-tree}</tcl>
<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 one table b-trees in the database file
for each rowid table in the database schema, including system tables
such as sqlite_schema.  There is one index b-tree
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_schema table is always a table
b-tree and always has a root page of 1.
The sqlite_schema table contains the root page number for every other 
table and index in the database file.</p>

<p>Each entry in a table b-tree consists of a 64-bit signed integer key
and up to 2147483647 bytes of arbitrary data.  (The key of a table b-tree
corresponds to the [rowid] of the SQL table that the b-tree implements.)
Interior table b-trees hold only keys and pointers to children.
All data is contained in the table b-tree leaves.</p>
................................................................................
page number is the parent b-tree page.
</ol>

<p>^In any database file that contains ptrmap pages, all b-tree root pages 
must come before any non-root b-tree page, cell payload overflow page, or
freelist page.  This restriction ensures that a root page will never
be moved during an auto-vacuum or incremental-vacuum.  The auto-vacuum
logic does not know how to update the root_page field of the sqlite_schema
table and so it is necessary to prevent root pages from being moved
during an auto-vacuum in order to preserve the integrity of the
sqlite_schema table.  ^Root pages are moved to the beginning of the
database file by the CREATE TABLE, CREATE INDEX, DROP TABLE, and
DROP INDEX operations.</p>

<h1>Schema Layer</h1>

<p>The foregoing text describes low-level aspects of the SQLite file
format.  The b-tree mechanism provides a powerful and efficient means of
................................................................................
and the index.

<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_schema {sqlite_master} {sqlite_master table} \
     {sqlite_schema table} {sqlite_schema}</tcl>
<h2>Storage Of The SQL Database Schema</h2>

<p>^Page 1 of a database file is the root page of a table b-tree that
holds a special table named "sqlite_schema" (or "sqlite_temp_schema" in
the case of a TEMP database) which stores the complete
database schema.  ^(The structure of the sqlite_schema table is as
if it had been created using the following SQL:</p>

<blockquote><pre>
CREATE TABLE sqlite_schema(
  type text,
  name text,
  tbl_name text,
  rootpage integer,
  sql text
);
</pre></blockquote>)^

<p>^The sqlite_schema table contains one row for each table, index, view,
and trigger (collectively "objects") in the database schema, except there
is no entry for the sqlite_schema table itself.  ^The sqlite_schema table
contains entries for [internal schema objects] in addition to application-
and programmer-defined objects.


<p>^(The sqlite_schema.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_schema.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_schema entry for the
PRIMARY KEY, but the "sqlite_autoindex_TABLE_N" name is set aside
for the PRIMARY KEY as if the sqlite_schema 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_schema.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_schema.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_schema.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]
would recreate the object.)^  The text is usually a copy of the original
statement used to create the object but with normalizations applied so
that the text conforms to the following rules:
................................................................................
<li>^Any database name qualifier that occurs prior to the name of the
object being created is removed.
<li>^Leading spaces are removed.
<li>^All spaces following the first two keywords are converted into a single
space.
</ul>

<p>^(The text in the sqlite_schema.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_schema.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>
<h3>Internal Schema Objects</h3>

<p>^In addition to the tables, indexes, views, and triggers created by
the application and/or the developer using CREATE statements SQL, the
sqlite_schema 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
with "sqlite_".  

................................................................................
<tcl>hd_fragment seqtab {sqlite_sequence}</tcl>
<h3>The sqlite_sequence table</h3>

<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_schema 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_schema.name) is in the sqlite_sequence.main field and the largest
[INTEGER PRIMARY KEY] ever inserted into 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

         error.)^

    <li><p> 
         ^(If an "ALTER TABLE ... RENAME TO" command is used to rename a table
         that is the parent table of one or more foreign key constraints, the
         definitions of the foreign key constraints are modified to refer to
         the parent table by its new name)^. ^The text of the child CREATE
         TABLE statement or statements stored in the sqlite_master table are 
         modified to reflect the new parent table name.
  </ul>

  <p>
    ^If foreign key constraints are enabled when it is prepared, the 
    [DROP TABLE] command performs an implicit [DELETE] to remove all
    rows from the table before dropping it. ^The implicit DELETE does not cause

         error.)^

    <li><p> 
         ^(If an "ALTER TABLE ... RENAME TO" command is used to rename a table
         that is the parent table of one or more foreign key constraints, the
         definitions of the foreign key constraints are modified to refer to
         the parent table by its new name)^. ^The text of the child CREATE
         TABLE statement or statements stored in the sqlite_schema table are 
         modified to reflect the new parent table name.
  </ul>

  <p>
    ^If foreign key constraints are enabled when it is prepared, the 
    [DROP TABLE] command performs an implicit [DELETE] to remove all
    rows from the table before dropping it. ^The implicit DELETE does not cause

<h1>Details</h1>

<p>
Each table and each index in SQLite is stored in a separate b-tree
in the database file.  Each b-tree is identified by its root page
number.  The root page number for any index or table can be found
by querying the "rootpage" column of the [sqlite_master table].
See the [indexing tutorial] and the [file format] documentation
for further background on this design.
</p>

<p>
Usually the b-trees for tables and indexes are slightly different.
A table b-tree contains a 64-bit integer key and arbitrary data.
................................................................................
as an index b-tree.  Thus, an index b-tree can be accessed as if it
were a WITHOUT ROWID table.
</p>

<h2>Manually Created Imposter Tables</h2>

<p>
One way to create an imposter table is to directly edit the sqlite_master
table to insert a new row that describes the table.
For example, suppose the schema is like this:
</p>

<codeblock>
CREATE TABLE t1(a INTEGER PRIMARY KEY,b TEXT,c INT, d INT);
CREATE INDEX t1bc ON t1(b,c);
................................................................................

<codeblock>
CREATE TABLE t2(b TEXT,c INT,a INT, PRIMARY KEY(b,c,a)) WITHOUT ROWID;
</codeblock>

<p>
To create a permanent imposter table "t2" against index "t1bc" one
should first enable editing of the sqlite_master table by running
"[PRAGMA writable_schema=ON]".
(Be careful to observe the warnings that accompany this PRAGMA.
A mistake can cause severe database corruption.)
Then insert a new entry into the sqlite_master table like this:
</p>

<codeblock>
INSERT INTO sqlite_master(type,name,tbl_name,rootpage,sql)
 SELECT 'table','t2','t2',rootpage,
   'CREATE TABLE t2(b,c,a,PRIMARY KEY(b,c,a))WITHOUT ROWID'
   FROM sqlite_master
  WHERE name='t1bc';
</codeblock>

<p>
The INSERT statement above adds a new row to the sqlite_master table that
defines a table "t2" having the same on-disk format as index "t1bc" and 
pointing to the same b-tree.
After adding this sqlite_master table entry, it is necessary to close and
reopen the database in order to get SQLite to reread the schema.
Then the "t2" table can be queried to see the content of the "t1bc" index.
</p>

<h3>Corrupted Database</h3>

<p>
A serious problem with the manual imposter table approach described above
is that after adding the new "t2" entry to the "sqlite_master" table, the
database file will technically be corrupt.  Both the "t1bc" index and the
"t2" table will point to the same b-tree.  This will not cause
any immediate problems, though one should avoid running [VACUUM].
</p>

<p>
It is possible to write into the "t2" table, thus changing the content
................................................................................
</p>

<h2>Transient Imposter Tables</h2>

<p>
Another (safer) approach to creating an imposter table is to add an
entry for the imposter table to SQLite's internal symbol table without
updating the "sqlite_master" table on disk.
That way, the imposter table exists in only a single database connection
and is automatically removed whenever the schema is reloaded.
</p>

<p>
Creation of a transient imposter table involves a special
[sqlite3_test_control()] call.  Unlike all other SQLite APIs,

<h1>Details</h1>

<p>
Each table and each index in SQLite is stored in a separate b-tree
in the database file.  Each b-tree is identified by its root page
number.  The root page number for any index or table can be found
by querying the "rootpage" column of the [sqlite_schema table].
See the [indexing tutorial] and the [file format] documentation
for further background on this design.
</p>

<p>
Usually the b-trees for tables and indexes are slightly different.
A table b-tree contains a 64-bit integer key and arbitrary data.
................................................................................
as an index b-tree.  Thus, an index b-tree can be accessed as if it
were a WITHOUT ROWID table.
</p>

<h2>Manually Created Imposter Tables</h2>

<p>
One way to create an imposter table is to directly edit the sqlite_schema
table to insert a new row that describes the table.
For example, suppose the schema is like this:
</p>

<codeblock>
CREATE TABLE t1(a INTEGER PRIMARY KEY,b TEXT,c INT, d INT);
CREATE INDEX t1bc ON t1(b,c);
................................................................................

<codeblock>
CREATE TABLE t2(b TEXT,c INT,a INT, PRIMARY KEY(b,c,a)) WITHOUT ROWID;
</codeblock>

<p>
To create a permanent imposter table "t2" against index "t1bc" one
should first enable editing of the sqlite_schema table by running
"[PRAGMA writable_schema=ON]".
(Be careful to observe the warnings that accompany this PRAGMA.
A mistake can cause severe database corruption.)
Then insert a new entry into the sqlite_schema table like this:
</p>

<codeblock>
INSERT INTO sqlite_schema(type,name,tbl_name,rootpage,sql)
 SELECT 'table','t2','t2',rootpage,
   'CREATE TABLE t2(b,c,a,PRIMARY KEY(b,c,a))WITHOUT ROWID'
   FROM sqlite_schema
  WHERE name='t1bc';
</codeblock>

<p>
The INSERT statement above adds a new row to the sqlite_schema table that
defines a table "t2" having the same on-disk format as index "t1bc" and 
pointing to the same b-tree.
After adding this sqlite_schema table entry, it is necessary to close and
reopen the database in order to get SQLite to reread the schema.
Then the "t2" table can be queried to see the content of the "t1bc" index.
</p>

<h3>Corrupted Database</h3>

<p>
A serious problem with the manual imposter table approach described above
is that after adding the new "t2" entry to the "sqlite_schema" table, the
database file will technically be corrupt.  Both the "t1bc" index and the
"t2" table will point to the same b-tree.  This will not cause
any immediate problems, though one should avoid running [VACUUM].
</p>

<p>
It is possible to write into the "t2" table, thus changing the content
................................................................................
</p>

<h2>Transient Imposter Tables</h2>

<p>
Another (safer) approach to creating an imposter table is to add an
entry for the imposter table to SQLite's internal symbol table without
updating the "sqlite_schema" table on disk.
That way, the imposter table exists in only a single database connection
and is automatically removed whenever the schema is reloaded.
</p>

<p>
Creation of a transient imposter table involves a special
[sqlite3_test_control()] call.  Unlike all other SQLite APIs,

<p>^Note also that when adding a [CHECK constraint], the CHECK constraint
is not tested against preexisting rows of the table.
^This can result in a table that contains data that
is in violation of the CHECK constraint.  Future versions of SQLite might
change to validate CHECK constraints as they are added.</p>

<p>The ALTER TABLE command works by modifying the SQL text of the schema
stored in the [sqlite_master table].
No changes are made to table content.
Because of this,
the execution time of the ALTER TABLE command is independent of
the amount of data in the table.  The ALTER TABLE command runs as quickly
on a table with 10 million rows as it does on a table with 1 row.
</p>

................................................................................
<li><p>
Start a transaction.

<li><p>
Remember the format of all indexes, triggers, and views associated with table X.
This information will be needed in step 8 below.  One way to do this is
to run a query like the following:
SELECT type, sql FROM sqlite_master WHERE tbl_name='X'.

<li><p>
Use [CREATE TABLE] to construct a new table "new_X" that is in the desired
revised format of table X.  Make sure that the name "new_X" does not collide
with any existing table name, of course.

<li><p>
................................................................................
<li><p> Run [PRAGMA schema_version] to determine the current schema
version number.  This number will be needed for step 6 below.

<li><p> Activate schema editing using 
[PRAGMA writable_schema | PRAGMA writable_schema=ON].

<li><p> Run an [UPDATE] statement to change the definition of table X
in the [sqlite_master table]: 
UPDATE sqlite_master SET sql=... WHERE type='table' AND name='X';
<p><em>Caution:</em>  Making a change to the sqlite_master table like this will
render the database corrupt and unreadable if the change contains
a syntax error.  It is suggested that careful testing of the UPDATE
statement be done on a separate blank database prior to using it on
a database containing important data.

<li><p> If the change to table X also affects other tables or indexes or
triggers are views within schema, then run [UPDATE] statements to modify
those other tables indexes and views too.  For example, if the name of
a column changes, all FOREIGN KEY constraints, triggers, indexes, and
views that refer to that column must be modified.
<p><em>Caution:</em>  Once again, making changes to the sqlite_master 
table like this will render the database corrupt and unreadable if the 
change contains an error.  Carefully test this entire procedure
on a separate test database prior to using it on
a database containing important data and/or make backup copies of
important databases prior to running this procedure.

<li><p> Increment the schema version number using
................................................................................
<h1>Why ALTER TABLE is such a problem for SQLite</h1>

<p>Most SQL database engines store the schema already parsed into
various system tables.  On those database engines, ALTER TABLE merely 
has to make modifications to the corresponding system tables.

<p>SQLite is different in that it stores the schema
in the [sqlite_master] table as the original text of the CREATE
statements that define the schema.  Hence ALTER TABLE needs
to revise the text of the CREATE statement.  Doing
so can be tricky for certain "creative" schema designs.

<p>The SQLite approach of storing the schema as text has advantages
for an embedded relational database.  For one, it means that the
schema takes up less space in the database file.  This is important

<p>^Note also that when adding a [CHECK constraint], the CHECK constraint
is not tested against preexisting rows of the table.
^This can result in a table that contains data that
is in violation of the CHECK constraint.  Future versions of SQLite might
change to validate CHECK constraints as they are added.</p>

<p>The ALTER TABLE command works by modifying the SQL text of the schema
stored in the [sqlite_schema table].
No changes are made to table content.
Because of this,
the execution time of the ALTER TABLE command is independent of
the amount of data in the table.  The ALTER TABLE command runs as quickly
on a table with 10 million rows as it does on a table with 1 row.
</p>

................................................................................
<li><p>
Start a transaction.

<li><p>
Remember the format of all indexes, triggers, and views associated with table X.
This information will be needed in step 8 below.  One way to do this is
to run a query like the following:
SELECT type, sql FROM sqlite_schema WHERE tbl_name='X'.

<li><p>
Use [CREATE TABLE] to construct a new table "new_X" that is in the desired
revised format of table X.  Make sure that the name "new_X" does not collide
with any existing table name, of course.

<li><p>
................................................................................
<li><p> Run [PRAGMA schema_version] to determine the current schema
version number.  This number will be needed for step 6 below.

<li><p> Activate schema editing using 
[PRAGMA writable_schema | PRAGMA writable_schema=ON].

<li><p> Run an [UPDATE] statement to change the definition of table X
in the [sqlite_schema table]: 
UPDATE sqlite_schema SET sql=... WHERE type='table' AND name='X';
<p><em>Caution:</em>  Making a change to the sqlite_schema table like this will
render the database corrupt and unreadable if the change contains
a syntax error.  It is suggested that careful testing of the UPDATE
statement be done on a separate blank database prior to using it on
a database containing important data.

<li><p> If the change to table X also affects other tables or indexes or
triggers are views within schema, then run [UPDATE] statements to modify
those other tables indexes and views too.  For example, if the name of
a column changes, all FOREIGN KEY constraints, triggers, indexes, and
views that refer to that column must be modified.
<p><em>Caution:</em>  Once again, making changes to the sqlite_schema 
table like this will render the database corrupt and unreadable if the 
change contains an error.  Carefully test this entire procedure
on a separate test database prior to using it on
a database containing important data and/or make backup copies of
important databases prior to running this procedure.

<li><p> Increment the schema version number using
................................................................................
<h1>Why ALTER TABLE is such a problem for SQLite</h1>

<p>Most SQL database engines store the schema already parsed into
various system tables.  On those database engines, ALTER TABLE merely 
has to make modifications to the corresponding system tables.

<p>SQLite is different in that it stores the schema
in the [sqlite_schema] table as the original text of the CREATE
statements that define the schema.  Hence ALTER TABLE needs
to revise the text of the CREATE statement.  Doing
so can be tricky for certain "creative" schema designs.

<p>The SQLite approach of storing the schema as text has advantages
for an embedded relational database.  For one, it means that the
schema takes up less space in the database file.  This is important

consider rerunning the ANALYZE command in order to update the statistics.</p>

<p> The query planner loads the content of the statistics tables
into memory when the schema is read.  ^Hence, when an application
changes the statistics tables directly, SQLite will not immediately
notice the changes. ^An application
can force the query planner to reread the statistics tables by running
<b>ANALYZE sqlite_master</b>. </p>

<tcl>hd_fragment autoanalyze {automatically running ANALYZE}</tcl>
<h1>Automatically Running ANALYZE</h1>

<p>The [PRAGMA optimize] command will automatically run ANALYZE on individual
tables on an as-needed basis.  The recommended practice is for applications
to invoke the [PRAGMA optimize] statement just before closing each database

consider rerunning the ANALYZE command in order to update the statistics.</p>

<p> The query planner loads the content of the statistics tables
into memory when the schema is read.  ^Hence, when an application
changes the statistics tables directly, SQLite will not immediately
notice the changes. ^An application
can force the query planner to reread the statistics tables by running
<b>ANALYZE sqlite_schema</b>. </p>

<tcl>hd_fragment autoanalyze {automatically running ANALYZE}</tcl>
<h1>Automatically Running ANALYZE</h1>

<p>The [PRAGMA optimize] command will automatically run ANALYZE on individual
tables on an as-needed basis.  The recommended practice is for applications
to invoke the [PRAGMA optimize] statement just before closing each database

<tcl>
RecursiveBubbleDiagram drop-trigger-stmt
</tcl>

<p>^The DROP TRIGGER statement removes a trigger created by the 
[CREATE TRIGGER] statement. ^Once removed, the trigger definition is no
longer present in the sqlite_master (or sqlite_temp_master) table and is
not fired by any subsequent INSERT, UPDATE or DELETE statements.

<p>^Note that triggers are automatically dropped when the associated table is
dropped.

<tcl>
RecursiveBubbleDiagram drop-trigger-stmt
</tcl>

<p>^The DROP TRIGGER statement removes a trigger created by the 
[CREATE TRIGGER] statement. ^Once removed, the trigger definition is no
longer present in the sqlite_schema (or sqlite_temp_schema) table and is
not fired by any subsequent INSERT, UPDATE or DELETE statements.

<p>^Note that triggers are automatically dropped when the associated table is
dropped.

     In Lemon, the tokenizer calls the parser.  Yacc operates the other
     way around, with the parser calling the tokenizer.  The Lemon
     approach is reentrant and threadsafe, whereas Yacc uses global 
     variables and is therefore neither.  Reentrancy is especially
     important for SQLite since some SQL statements make recursive calls
     to the parser.  For example, when parsing a CREATE TABLE statement,
     SQLite invokes the parser recursively to generate an INSERT statement
     to make a new entry in the [sqlite_master] table.
<li><p>
     Lemon has the concept of a non-terminal destructor that can be
     used to reclaim memory or other resources following a syntax error
     or other aborted parse.
</ul>

<h2>Use of Lemon Within SQLite</h2>

     In Lemon, the tokenizer calls the parser.  Yacc operates the other
     way around, with the parser calling the tokenizer.  The Lemon
     approach is reentrant and threadsafe, whereas Yacc uses global 
     variables and is therefore neither.  Reentrancy is especially
     important for SQLite since some SQL statements make recursive calls
     to the parser.  For example, when parsing a CREATE TABLE statement,
     SQLite invokes the parser recursively to generate an INSERT statement
     to make a new entry in the [sqlite_schema] table.
<li><p>
     Lemon has the concept of a non-terminal destructor that can be
     used to reclaim memory or other resources following a syntax error
     or other aborted parse.
</ul>

<h2>Use of Lemon Within SQLite</h2>

<p>
The following is a log of all SQL used to generate one particular
timeline (captured on 2016-09-16):

<codeblock>
-- sqlite3_open: /home/drh/sqlite/sqlite/.fslckout
PRAGMA foreign_keys=OFF;
SELECT sql FROM localdb.sqlite_master WHERE name=='vfile';
-- sqlite3_open: /home/drh/.fossil
PRAGMA foreign_keys=OFF;
SELECT value FROM vvar WHERE name='repository';
ATTACH DATABASE '/home/drh/www/repos/sqlite.fossil' AS 'repository' KEY '';
SELECT value FROM config WHERE name='allow-symlinks';
SELECT value FROM global_config WHERE name='allow-symlinks';
SELECT value FROM config WHERE name='aux-schema';

<p>
The following is a log of all SQL used to generate one particular
timeline (captured on 2016-09-16):

<codeblock>
-- sqlite3_open: /home/drh/sqlite/sqlite/.fslckout
PRAGMA foreign_keys=OFF;
SELECT sql FROM localdb.sqlite_schema WHERE name=='vfile';
-- sqlite3_open: /home/drh/.fossil
PRAGMA foreign_keys=OFF;
SELECT value FROM vvar WHERE name='repository';
ATTACH DATABASE '/home/drh/www/repos/sqlite.fossil' AS 'repository' KEY '';
SELECT value FROM config WHERE name='allow-symlinks';
SELECT value FROM global_config WHERE name='allow-symlinks';
SELECT value FROM config WHERE name='aux-schema';

can use aggregate functions, and the table-valued function can be just
one of several data sources in a join.
For example, to get a list of all indexed columns in a schema, one
could query:

<blockquote><pre>
SELECT DISTINCT m.name || '.' || ii.name AS 'indexed-columns'
  FROM sqlite_master AS m,
       pragma_index_list(m.name) AS il,
       pragma_index_info(il.name) AS ii
 WHERE m.type='table'
 ORDER BY 1;
</pre></blockquote>

<p>
................................................................................
       which can lead to incorrect answers and/or
       [cfgerrors|database corruption].

       DISCLAIMER

<p>    ^For the purposes of this pragma, the [VACUUM] command is considered
       a schema change, since [VACUUM] will usual alter the "rootpage"
       values for entries in the [sqlite_master table].


<p>    See also the [application_id pragma] and [user_version pragma].
}


Pragma {user_version} {
................................................................................
    enforced by default.</p>
}

DangerousPragma writable_schema {
    <p>^(<b>PRAGMA writable_schema  = </b><i>boolean</i><b>;</b></p>

    <p>When this pragma is on, and the [SQLITE_DBCONFIG_DEFENSIVE] flag
    is off, then the [sqlite_master] table
    can be changed using ordinary [UPDATE], [INSERT], and [DELETE]
    statements.)^  ^<warning><b>Warning:</b>
    misuse of this pragma can easily result in
    a [cfgerrors|corrupt database file].</warning>
}

Pragma function_list {

can use aggregate functions, and the table-valued function can be just
one of several data sources in a join.
For example, to get a list of all indexed columns in a schema, one
could query:

<blockquote><pre>
SELECT DISTINCT m.name || '.' || ii.name AS 'indexed-columns'
  FROM sqlite_schema AS m,
       pragma_index_list(m.name) AS il,
       pragma_index_info(il.name) AS ii
 WHERE m.type='table'
 ORDER BY 1;
</pre></blockquote>

<p>
................................................................................
       which can lead to incorrect answers and/or
       [cfgerrors|database corruption].

       DISCLAIMER

<p>    ^For the purposes of this pragma, the [VACUUM] command is considered
       a schema change, since [VACUUM] will usual alter the "rootpage"
       values for entries in the [sqlite_schema table].


<p>    See also the [application_id pragma] and [user_version pragma].
}


Pragma {user_version} {
................................................................................
    enforced by default.</p>
}

DangerousPragma writable_schema {
    <p>^(<b>PRAGMA writable_schema  = </b><i>boolean</i><b>;</b></p>

    <p>When this pragma is on, and the [SQLITE_DBCONFIG_DEFENSIVE] flag
    is off, then the [sqlite_schema] table
    can be changed using ordinary [UPDATE], [INSERT], and [DELETE]
    statements.)^  ^<warning><b>Warning:</b>
    misuse of this pragma can easily result in
    a [cfgerrors|corrupt database file].</warning>
}

Pragma function_list {

query planner knows that the indexes are of low quality.  And the way
the query planner knows this is by the content of the [SQLITE_STAT1] table,
which is computed by the ANALYZE command.</p>

<p>Of course, ANALYZE only works effectively if you have a significant 
amount of content in your database in the first place.  When creating a 
new database that you expect to accumulate a lot of data, you can run 
the command "ANALYZE sqlite_master" to create the SQLITE_STAT1 table,
then prepopulate the SQLITE_STAT1 table (using ordinary INSERT statements)
with content that describes a typical
database for your application - perhaps content that you extracted after
running ANALYZE on a well-populated template database in the lab.</p>

<li><p><b>Instrument your code.</b>
Add logic that lets you know quickly and easily which queries are taking

query planner knows that the indexes are of low quality.  And the way
the query planner knows this is by the content of the [SQLITE_STAT1] table,
which is computed by the ANALYZE command.</p>

<p>Of course, ANALYZE only works effectively if you have a significant 
amount of content in your database in the first place.  When creating a 
new database that you expect to accumulate a lot of data, you can run 
the command "ANALYZE sqlite_schema" to create the SQLITE_STAT1 table,
then prepopulate the SQLITE_STAT1 table (using ordinary INSERT statements)
with content that describes a typical
database for your application - perhaps content that you extracted after
running ANALYZE on a well-populated template database in the lab.</p>

<li><p><b>Instrument your code.</b>
Add logic that lets you know quickly and easily which queries are taking

connection modifies a table while it is being read, but it also means that
a read-transaction opened by a connection in read-uncommitted mode can
neither block nor be blocked by any other connection.</p>

<p>Read-uncommitted mode has no effect on the locks required to write to
database tables (i.e. read-uncommitted connections must still obtain 
write-locks and hence database writes may still block or be blocked). 
Also, read-uncommitted mode has no effect on the <i>sqlite_master</i> 
locks required by the rules enumerated below (see section 
"Schema (sqlite_master) Level Locking").
</p>

<blockquote><pre>
  /* Set the value of the read-uncommitted flag:
  **
  **   True  -> Set the connection to read-uncommitted mode.
  **   False -> Set the connection to serialized (the default) mode.
................................................................................
  */
  PRAGMA read_uncommitted = &lt;boolean&gt;;

  /* Retrieve the current value of the read-uncommitted flag */
  PRAGMA read_uncommitted;
</pre></blockquote>

<h2>Schema (sqlite_master) Level Locking</h2>

<p>The <i>sqlite_master</i> table supports shared-cache read and write 
locks in the same way as all other database tables (see description 
above). The following special rules also apply:
</p>

<ul>
<li>A connection must obtain a read-lock on <i>sqlite_master</i> before 
accessing any database tables or obtaining any other read or write locks.</li>
<li>Before executing a statement that modifies the database schema (i.e. 
a CREATE or DROP TABLE statement), a connection must obtain a write-lock on 
<i>sqlite_master</i>.
</li>
<li>A connection may not compile an SQL statement if any other connection
is holding a write-lock on the <i>sqlite_master</i> table of any attached
database (including the default database, "main"). 
</li>
</ul>

<h1>Thread Related Issues</h1>

<p>In SQLite versions 3.3.0 through 3.4.2 when shared-cache mode is enabled, 

connection modifies a table while it is being read, but it also means that
a read-transaction opened by a connection in read-uncommitted mode can
neither block nor be blocked by any other connection.</p>

<p>Read-uncommitted mode has no effect on the locks required to write to
database tables (i.e. read-uncommitted connections must still obtain 
write-locks and hence database writes may still block or be blocked). 
Also, read-uncommitted mode has no effect on the <i>sqlite_schema</i> 
locks required by the rules enumerated below (see section 
"Schema (sqlite_schema) Level Locking").
</p>

<blockquote><pre>
  /* Set the value of the read-uncommitted flag:
  **
  **   True  -> Set the connection to read-uncommitted mode.
  **   False -> Set the connection to serialized (the default) mode.
................................................................................
  */
  PRAGMA read_uncommitted = &lt;boolean&gt;;

  /* Retrieve the current value of the read-uncommitted flag */
  PRAGMA read_uncommitted;
</pre></blockquote>

<h2>Schema (sqlite_schema) Level Locking</h2>

<p>The <i>sqlite_schema</i> table supports shared-cache read and write 
locks in the same way as all other database tables (see description 
above). The following special rules also apply:
</p>

<ul>
<li>A connection must obtain a read-lock on <i>sqlite_schema</i> before 
accessing any database tables or obtaining any other read or write locks.</li>
<li>Before executing a statement that modifies the database schema (i.e. 
a CREATE or DROP TABLE statement), a connection must obtain a write-lock on 
<i>sqlite_schema</i>.
</li>
<li>A connection may not compile an SQL statement if any other connection
is holding a write-lock on the <i>sqlite_schema</i> table of any attached
database (including the default database, "main"). 
</li>
</ul>

<h1>Thread Related Issues</h1>

<p>In SQLite versions 3.3.0 through 3.4.2 when shared-cache mode is enabled, 

MOZ_KEYWORDS...................................... 3            1.4% 
MOZ_ANNO_ATTRIBUTES............................... 2            0.90% 
MOZ_ANNOS......................................... 2            0.90% 
MOZ_BOOKMARKS_ROOTS............................... 2            0.90% 
MOZ_HOSTS......................................... 2            0.90% 
MOZ_INPUTHISTORY.................................. 2            0.90% 
MOZ_ITEMS_ANNOS................................... 2            0.90% 
SQLITE_MASTER..................................... 1            0.45% 
SQLITE_SEQUENCE................................... 1            0.45% 
SQLITE_STAT1...................................... 1            0.45% 

*** Page counts for all tables and indices separately *************************

MOZ_PLACES........................................ 63          28.5% 
MOZ_PLACES_URL_UNIQUEINDEX........................ 37          16.7% 
................................................................................
MOZ_KEYWORDS...................................... 1            0.45% 
MOZ_KEYWORDS_PLACEPOSTDATA_UNIQUEINDEX............ 1            0.45% 
SQLITE_AUTOINDEX_MOZ_ANNO_ATTRIBUTES_1............ 1            0.45% 
SQLITE_AUTOINDEX_MOZ_BOOKMARKS_ROOTS_1............ 1            0.45% 
SQLITE_AUTOINDEX_MOZ_HOSTS_1...................... 1            0.45% 
SQLITE_AUTOINDEX_MOZ_INPUTHISTORY_1............... 1            0.45% 
SQLITE_AUTOINDEX_MOZ_KEYWORDS_1................... 1            0.45% 
SQLITE_MASTER..................................... 1            0.45% 
SQLITE_SEQUENCE................................... 1            0.45% 
SQLITE_STAT1...................................... 1            0.45% 

*** All tables and indices ****************************************************

Percentage of total database...................... 100.0%    
Number of entries................................. 154969    
................................................................................
Overflow pages used............................... 0         
Total pages used.................................. 5         
Unused bytes on index pages....................... 32717       99.84% 
Unused bytes on primary pages..................... 32256       24.6% 
Unused bytes on overflow pages.................... 0         
Unused bytes on all pages......................... 64973       39.7% 

*** Table SQLITE_MASTER *******************************************************

Percentage of total database......................   0.45%   
Number of entries................................. 36        
Bytes of storage consumed......................... 32768     
Bytes of payload.................................. 5188        15.8% 
B-tree depth...................................... 1         
Average payload per entry......................... 144.11    
................................................................................
Pages of auto-vacuum overhead

    The number of pages that store data used by the database to facilitate
    auto-vacuum. This is zero for databases that do not support auto-vacuum.

Number of tables in the database

    The number of tables in the database, including the SQLITE_MASTER table
    used to store schema information.

Number of indices

    The total number of indices in the database.

Number of defined indices
................................................................................
Size of the file in bytes

    The total amount of disk space used by the entire database files.

Bytes of user payload stored

    The total number of bytes of user payload stored in the database. The
    schema information in the SQLITE_MASTER table is not counted when
    computing this number.  The percentage at the right shows the payload
    divided by the total file size.

Percentage of total database

    The amount of the complete database file that is devoted to storing
    information described by this category.
................................................................................
   ovfl_pages int,   -- Number of overflow pages used
   int_unused int,   -- Number of unused bytes on interior pages
   leaf_unused int,  -- Number of unused bytes on primary pages
   ovfl_unused int,  -- Number of unused bytes on overflow pages
   gap_cnt int,      -- Number of gaps in the page layout
   compressed_size int  -- Total bytes stored on disk
);
INSERT INTO space_used VALUES('sqlite_master','sqlite_master',0,36,36,1,5188,0,0,379,0,1,0,0,27309,0,0,32768);
INSERT INTO space_used VALUES('moz_places','moz_places',0,10955,10894,2,1838131,0,0,1867,1,62,0,32207,121406,0,30,2064384);
INSERT INTO space_used VALUES('moz_historyvisits','moz_historyvisits',0,15884,15873,2,308447,0,0,21,1,12,0,32668,5435,0,8,425984);
INSERT INTO space_used VALUES('moz_inputhistory','moz_inputhistory',0,8,8,1,341,0,0,71,0,1,0,0,32379,0,0,32768);
INSERT INTO space_used VALUES('sqlite_autoindex_moz_inputhistory_1','moz_inputhistory',1,8,8,1,301,0,0,65,0,1,0,0,32435,0,0,32768);
INSERT INTO space_used VALUES('moz_hosts','moz_hosts',0,628,628,1,14640,0,0,49,0,1,0,0,15012,0,0,32768);
INSERT INTO space_used VALUES('sqlite_autoindex_moz_hosts_1','moz_hosts',1,628,628,1,13000,0,0,47,0,1,0,0,17876,0,0,32768);
INSERT INTO space_used VALUES('moz_bookmarks','moz_bookmarks',0,313,313,1,21937,0,0,518,0,1,0,0,9358,0,0,32768);

MOZ_KEYWORDS...................................... 3            1.4% 
MOZ_ANNO_ATTRIBUTES............................... 2            0.90% 
MOZ_ANNOS......................................... 2            0.90% 
MOZ_BOOKMARKS_ROOTS............................... 2            0.90% 
MOZ_HOSTS......................................... 2            0.90% 
MOZ_INPUTHISTORY.................................. 2            0.90% 
MOZ_ITEMS_ANNOS................................... 2            0.90% 
SQLITE_SCHEMA..................................... 1            0.45% 
SQLITE_SEQUENCE................................... 1            0.45% 
SQLITE_STAT1...................................... 1            0.45% 

*** Page counts for all tables and indices separately *************************

MOZ_PLACES........................................ 63          28.5% 
MOZ_PLACES_URL_UNIQUEINDEX........................ 37          16.7% 
................................................................................
MOZ_KEYWORDS...................................... 1            0.45% 
MOZ_KEYWORDS_PLACEPOSTDATA_UNIQUEINDEX............ 1            0.45% 
SQLITE_AUTOINDEX_MOZ_ANNO_ATTRIBUTES_1............ 1            0.45% 
SQLITE_AUTOINDEX_MOZ_BOOKMARKS_ROOTS_1............ 1            0.45% 
SQLITE_AUTOINDEX_MOZ_HOSTS_1...................... 1            0.45% 
SQLITE_AUTOINDEX_MOZ_INPUTHISTORY_1............... 1            0.45% 
SQLITE_AUTOINDEX_MOZ_KEYWORDS_1................... 1            0.45% 
SQLITE_SCHEMA..................................... 1            0.45% 
SQLITE_SEQUENCE................................... 1            0.45% 
SQLITE_STAT1...................................... 1            0.45% 

*** All tables and indices ****************************************************

Percentage of total database...................... 100.0%    
Number of entries................................. 154969    
................................................................................
Overflow pages used............................... 0         
Total pages used.................................. 5         
Unused bytes on index pages....................... 32717       99.84% 
Unused bytes on primary pages..................... 32256       24.6% 
Unused bytes on overflow pages.................... 0         
Unused bytes on all pages......................... 64973       39.7% 

*** Table SQLITE_SCHEMA *******************************************************

Percentage of total database......................   0.45%   
Number of entries................................. 36        
Bytes of storage consumed......................... 32768     
Bytes of payload.................................. 5188        15.8% 
B-tree depth...................................... 1         
Average payload per entry......................... 144.11    
................................................................................
Pages of auto-vacuum overhead

    The number of pages that store data used by the database to facilitate
    auto-vacuum. This is zero for databases that do not support auto-vacuum.

Number of tables in the database

    The number of tables in the database, including the SQLITE_SCHEMA table
    used to store schema information.

Number of indices

    The total number of indices in the database.

Number of defined indices
................................................................................
Size of the file in bytes

    The total amount of disk space used by the entire database files.

Bytes of user payload stored

    The total number of bytes of user payload stored in the database. The
    schema information in the SQLITE_SCHEMA table is not counted when
    computing this number.  The percentage at the right shows the payload
    divided by the total file size.

Percentage of total database

    The amount of the complete database file that is devoted to storing
    information described by this category.
................................................................................
   ovfl_pages int,   -- Number of overflow pages used
   int_unused int,   -- Number of unused bytes on interior pages
   leaf_unused int,  -- Number of unused bytes on primary pages
   ovfl_unused int,  -- Number of unused bytes on overflow pages
   gap_cnt int,      -- Number of gaps in the page layout
   compressed_size int  -- Total bytes stored on disk
);
INSERT INTO space_used VALUES('sqlite_schema','sqlite_schema',0,36,36,1,5188,0,0,379,0,1,0,0,27309,0,0,32768);
INSERT INTO space_used VALUES('moz_places','moz_places',0,10955,10894,2,1838131,0,0,1867,1,62,0,32207,121406,0,30,2064384);
INSERT INTO space_used VALUES('moz_historyvisits','moz_historyvisits',0,15884,15873,2,308447,0,0,21,1,12,0,32668,5435,0,8,425984);
INSERT INTO space_used VALUES('moz_inputhistory','moz_inputhistory',0,8,8,1,341,0,0,71,0,1,0,0,32379,0,0,32768);
INSERT INTO space_used VALUES('sqlite_autoindex_moz_inputhistory_1','moz_inputhistory',1,8,8,1,301,0,0,65,0,1,0,0,32435,0,0,32768);
INSERT INTO space_used VALUES('moz_hosts','moz_hosts',0,628,628,1,14640,0,0,49,0,1,0,0,15012,0,0,32768);
INSERT INTO space_used VALUES('sqlite_autoindex_moz_hosts_1','moz_hosts',1,628,628,1,13000,0,0,47,0,1,0,0,17876,0,0,32768);
INSERT INTO space_used VALUES('moz_bookmarks','moz_bookmarks',0,313,313,1,21937,0,0,518,0,1,0,0,9358,0,0,32768);

  obtained. A connection releases all held table locks when it concludes
  its transaction. If a connection cannot obtain a required lock, then
  the call to [sqlite3_step()] returns SQLITE_LOCKED.

<p>
  Although it is less common, a call to [sqlite3_prepare()] or
  [sqlite3_prepare_v2()] may also return SQLITE_LOCKED if it cannot obtain
  a read-lock on the sqlite_master table of each attached database. These
  APIs need to read the schema data contained in the sqlite_master table
  in order to compile SQL statements to [sqlite3_stmt*] objects.

<p>
  This article presents a technique using the SQLite [sqlite3_unlock_notify()]
  interface such that calls to [sqlite3_step()] and [sqlite3_prepare_v2()]
  block until the required locks are available instead of returning 
  SQLITE_LOCKED immediately. If the

  obtained. A connection releases all held table locks when it concludes
  its transaction. If a connection cannot obtain a required lock, then
  the call to [sqlite3_step()] returns SQLITE_LOCKED.

<p>
  Although it is less common, a call to [sqlite3_prepare()] or
  [sqlite3_prepare_v2()] may also return SQLITE_LOCKED if it cannot obtain
  a read-lock on the sqlite_schema table of each attached database. These
  APIs need to read the schema data contained in the sqlite_schema table
  in order to compile SQL statements to [sqlite3_stmt*] objects.

<p>
  This article presents a technique using the SQLite [sqlite3_unlock_notify()]
  interface such that calls to [sqlite3_step()] and [sqlite3_prepare_v2()]
  block until the required locks are available instead of returning 
  SQLITE_LOCKED immediately. If the