Documentation Source Text

<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

<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 "[sqlite_schema]" 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

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

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

<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

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.
................................................................................
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 ffschema {schema storage}</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]".  This b-tree is known
as the "schema table" since it 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,
................................................................................

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

<h3>Alternative Names For The Schema Table</h3>

<p>The name "sqlite_schema" does not appear anywhere in the file format.
That name is just a convention used by the database implementation.
Due to historical and operational considerations, the
"sqlite_schema" table can also sometimes be called by one of the
following aliases:

<ol>
<li> sqlite_master
<li> sqlite_temp_schema
<li> sqlite_temp_master
</ol>

<p>Because the name of the schema table does not appear anywhere in
the file format, the meaning of the database file is not changed if
the application chooses to refer to the schema table by one of
these alternative names.

<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

         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

         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

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

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

<title>The Schema Table</title>
<tcl>hd_keywords {sqlite_schema} {sqlite_temp_schema} \
                 {sqlite_schema table} {sqlite_master table}</tcl>

<table_of_contents>

<h1>Introduction</h1>

<p>Every SQLite database contains a single "schema table" that stores the
schema for that database.  The schema for a database is a description of
all of the other tables, indexes, triggers, and views that are
contained within the database.  The schema table looks like this:

<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 schema, except there
is no entry for the sqlite_schema table itself.  See the
[schema storage] subsection of the [file format] documentation for
additional information on how SQLite uses the sqlite_schema table
internally.

<h1>Alternative Names</h1>

<p>The schema table can always be referenced using the name "sqlite_schema",
especially if qualifed by the schema name like 
"main.sqlite_schema" or "temp.sqlite_schema".  But for historical
compatibility, some alternative names are also recognized, including:

<ol>
<li> sqlite_master
<li> sqlite_temp_schema
<li> sqlite_temp_master
</ol>

<p>
Alternatives (2) and (3) only work for the TEMP database associated
with each database connection, but alternative (1) works anywhere.
For historical reasons, callbacks from the [sqlite3_set_authorizer()]
interface always refer to the schema table using names (1) or (3).

<h1>Interpretation Of The Schema Table</h1>

<p>The meanings of the fields of the schema table are as follows:

<dl>
<dt><b>type</b></dt>
<dd>
<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>
</dd>

<dt><b>name</b><dt>
<dd>
<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>
</dd>

<dt><b>tbl_name</b></dt>
<dd>
<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>
</dd>

<dt><b>rootpage</b></dt>
<dd>
<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>
</dd>

<dt><b>sql</b></dt>
<dd>
<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:

<ul>
<li>The CREATE, TABLE, VIEW, TRIGGER, and INDEX keywords at the beginning
of the statement are converted to all upper case letters.
<li>The TEMP or TEMPORARY keyword is removed if it occurs after the 
initial CREATE keyword.
<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>
</dd>
</dl>

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

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

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 [sqlite_schema]
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:
  **
................................................................................

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

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

<p>The [sqlite_schema 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>

  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 

  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