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 |
Timelines: | family | ancestors | descendants | both | trunk |
Files: | files | file ages | folders |
SHA3-256: |
29b01bac87dc0f3bd9bdba7d480349a8 |
User & Date: | drh 2020-06-19 13:05:24.473 |
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
Changes to pages/atomiccommit.in.
︙ | ︙ | |||
207 208 209 210 211 212 213 | <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 | | | 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 | <tclscript>DisplayCode { SELECT name FROM sqlite_schema WHERE type IN ('table','view') AND name NOT LIKE 'sqlite_%' ORDER BY 1 } </tclscript> | | | 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 | 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 | | | 516 517 518 519 520 521 522 523 524 525 526 527 528 529 530 | 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. |
︙ | ︙ | |||
1179 1180 1181 1182 1183 1184 1185 | 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> | | < | | | 1179 1180 1181 1182 1183 1184 1185 1186 1187 1188 1189 1190 1191 1192 1193 1194 1195 1196 1197 1198 | 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, |
︙ | ︙ | |||
1263 1264 1265 1266 1267 1268 1269 1270 1271 1272 1273 1274 1275 1276 | <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 | > > > > > > > > > > > > > > > > > > | 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 | <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 | 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 | | | 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.
1 2 3 4 5 6 7 8 9 10 11 | <title>DROP TRIGGER</title> <tcl> hd_keywords *droptrigger {DROP TRIGGER} </tcl> <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 | | | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | <title>DROP TRIGGER</title> <tcl> hd_keywords *droptrigger {DROP TRIGGER} </tcl> <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 | 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). | | | | 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 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: ** ** True -> Set the connection to read-uncommitted mode. ** False -> Set the connection to serialized (the default) mode. */ PRAGMA read_uncommitted = <boolean>; /* 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 | 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 | | | 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 |
︙ | ︙ |