Documentation Source Text

Check-in [29b01bac87]
Login

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:Create a new documentation page devoted to describing the use and purpose of the sqlite_schema table. Work-in-progress.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA3-256: 29b01bac87dc0f3bd9bdba7d480349a8588918c67f7faa3e84b9ed7bf95ecec1
User & Date: drh 2020-06-19 13:05:24
Context
2020-06-28
16:36
Correction to how Knuth names B-Tree algorithm variants in the file format document. (check-in: ea334221a0 user: drh tags: trunk)
2020-06-19
13:05
Create a new documentation page devoted to describing the use and purpose of the sqlite_schema table. Work-in-progress. (check-in: 29b01bac87 user: drh tags: trunk)
2020-06-18
21:18
Terminology adjustments. (check-in: e01d8c6144 user: drh tags: trunk)
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to pages/atomiccommit.in.

207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
<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







|







207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
<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

Changes to pages/cli.in.

568
569
570
571
572
573
574
575
576
577
578
579
580
581
582

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








|







568
569
570
571
572
573
574
575
576
577
578
579
580
581
582

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

Changes to pages/fileformat2.in.

516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
....
1179
1180
1181
1182
1183
1184
1185
1186
1187
1188
1189
1190
1191
1192
1193
1194
1195
1196
1197
1198
1199
....
1263
1264
1265
1266
1267
1268
1269


















1270
1271
1272
1273
1274
1275
1276
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







|







 







|
<



|
|







 







>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>







516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
....
1179
1180
1181
1182
1183
1184
1185
1186

1187
1188
1189
1190
1191
1192
1193
1194
1195
1196
1197
1198
....
1262
1263
1264
1265
1266
1267
1268
1269
1270
1271
1272
1273
1274
1275
1276
1277
1278
1279
1280
1281
1282
1283
1284
1285
1286
1287
1288
1289
1290
1291
1292
1293
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

Changes to pages/foreignkeys.in.

814
815
816
817
818
819
820
821
822
823
824
825
826
827
828
         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







|







814
815
816
817
818
819
820
821
822
823
824
825
826
827
828
         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

Changes to pages/lang_droptrigger.in.

5
6
7
8
9
10
11
12
13
14
15
16

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







|




5
6
7
8
9
10
11
12
13
14
15
16

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

Added pages/schematab.in.





























































































































































































































































>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
<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>

Changes to pages/sharedcache.in.

115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
...
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
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>







|







 







|







115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
...
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
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>

Changes to pages/unlock_notify.in.

62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
  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 







|







62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
  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