Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | Terminology adjustments. |
---|---|
Downloads: | Tarball | ZIP archive |
Timelines: | family | ancestors | descendants | both | trunk |
Files: | files | file ages | folders |
SHA3-256: |
e01d8c61448dd8a0401c66cdd7bc0633 |
User & Date: | drh 2020-06-18 21:18:56.805 |
Context
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) | |
20:30 | Merge fixes from the 3.32 branch. (check-in: b48705423b user: drh tags: trunk) | |
Changes
Changes to pages/34to35.in.
︙ | ︙ | |||
351 352 353 354 355 356 357 | <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] | | | 351 352 353 354 355 356 357 358 359 360 361 362 363 364 365 | <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 |
︙ | ︙ | |||
866 867 868 869 870 871 872 | 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> | | | 866 867 868 869 870 871 872 873 874 875 876 877 878 879 880 | 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 |
︙ | ︙ |
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 <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 |
︙ | ︙ | |||
539 540 541 542 543 544 545 | <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 | | | | 539 540 541 542 543 544 545 546 547 548 549 550 551 552 553 554 555 | <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 |
︙ | ︙ | |||
653 654 655 656 657 658 659 | 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"> | | | | | | | | | | | | | | | | | | 653 654 655 656 657 658 659 660 661 662 663 664 665 666 667 668 669 670 671 672 673 674 675 676 677 678 679 680 681 682 683 684 685 686 687 688 689 690 691 692 693 694 695 696 697 698 699 700 701 702 703 704 705 706 707 708 709 710 711 712 713 714 | 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> |
︙ | ︙ | |||
733 734 735 736 737 738 739 | <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"> | | | | | | 733 734 735 736 737 738 739 740 741 742 743 744 745 746 747 748 749 750 751 752 753 754 755 756 757 758 759 760 | <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"> |
︙ | ︙ |
Changes to pages/cli.in.
︙ | ︙ | |||
563 564 565 566 567 568 569 | }</tclscript> <p>The ".tables" command is similar to setting list mode then executing the following query:</p> <tclscript>DisplayCode { | | | | 563 564 565 566 567 568 569 570 571 572 573 574 575 576 577 578 579 580 581 582 | }</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. |
︙ | ︙ | |||
602 603 604 605 606 607 608 | }</tclscript> <p>The ".schema" command is roughly the same as setting list mode, then entering the following query:</p> <tclscript>DisplayCode { | | | 602 603 604 605 606 607 608 609 610 611 612 613 614 615 616 | }</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: |
︙ | ︙ | |||
975 976 977 978 979 980 981 | 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. | | | 975 976 977 978 979 980 981 982 983 984 985 986 987 988 989 | 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 |
︙ | ︙ |
Changes to pages/faq.in.
︙ | ︙ | |||
187 188 189 190 191 192 193 | 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 | | | | | | | | | | | | | 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 | 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? |
︙ | ︙ |
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 531 532 533 534 535 536 537 538 539 540 | 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> |
︙ | ︙ | |||
889 890 891 892 893 894 895 | 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 | | | | 889 890 891 892 893 894 895 896 897 898 899 900 901 902 903 904 905 906 | 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 |
︙ | ︙ | |||
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 1199 1200 1201 1202 1203 1204 1205 1206 1207 1208 1209 1210 1211 1212 1213 1214 1215 1216 1217 1218 1219 1220 1221 1222 1223 1224 1225 1226 1227 1228 1229 1230 1231 1232 1233 1234 1235 1236 1237 1238 1239 1240 1241 1242 1243 1244 1245 1246 1247 1248 1249 1250 1251 1252 1253 1254 1255 1256 1257 1258 1259 1260 1261 1262 1263 1264 1265 1266 1267 1268 1269 1270 1271 1272 1273 1274 1275 1276 1277 1278 1279 1280 1281 1282 1283 1284 1285 | 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: <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> <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_". |
︙ | ︙ | |||
1305 1306 1307 1308 1309 1310 1311 | <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 | | | | 1306 1307 1308 1309 1310 1311 1312 1313 1314 1315 1316 1317 1318 1319 1320 1321 1322 1323 1324 1325 1326 1327 1328 1329 | <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 |
︙ | ︙ |
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/imposter.in.
︙ | ︙ | |||
25 26 27 28 29 30 31 | <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 | | | 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 | <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. |
︙ | ︙ | |||
47 48 49 50 51 52 53 | 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> | | | | | | | | | | 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 | 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> <p> The WITHOUT ROWID table that has the same structure as the t1bc index would look like this: </p> <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 |
︙ | ︙ | |||
123 124 125 126 127 128 129 | </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 | | | 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 | </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, |
︙ | ︙ |
Changes to pages/lang_altertable.in.
︙ | ︙ | |||
109 110 111 112 113 114 115 | <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 | | | 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 | <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> |
︙ | ︙ | |||
142 143 144 145 146 147 148 | <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: | | | 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 | <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> |
︙ | ︙ | |||
246 247 248 249 250 251 252 | <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 | | | | | | 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 | <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 |
︙ | ︙ | |||
291 292 293 294 295 296 297 | <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 | | | 291 292 293 294 295 296 297 298 299 300 301 302 303 304 305 | <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 |
︙ | ︙ |
Changes to pages/lang_analyze.in.
︙ | ︙ | |||
79 80 81 82 83 84 85 | 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 | | | 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 | 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 |
︙ | ︙ |
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. |
Changes to pages/lemon.in.
︙ | ︙ | |||
47 48 49 50 51 52 53 | 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 | | | 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 | 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> |
︙ | ︙ |
Changes to pages/np1queryprob.in.
︙ | ︙ | |||
112 113 114 115 116 117 118 | <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; | | | 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 | <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'; |
︙ | ︙ |
Changes to pages/pragma.in.
︙ | ︙ | |||
200 201 202 203 204 205 206 | 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' | | | 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 | 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> |
︙ | ︙ | |||
1666 1667 1668 1669 1670 1671 1672 | 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" | | | 1666 1667 1668 1669 1670 1671 1672 1673 1674 1675 1676 1677 1678 1679 1680 | 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} { |
︙ | ︙ | |||
1902 1903 1904 1905 1906 1907 1908 | 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 | | | 1902 1903 1904 1905 1906 1907 1908 1909 1910 1911 1912 1913 1914 1915 1916 | 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 { |
︙ | ︙ |
Changes to pages/queryplanner-ng.in.
︙ | ︙ | |||
657 658 659 660 661 662 663 | 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 | | | 657 658 659 660 661 662 663 664 665 666 667 668 669 670 671 | 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 |
︙ | ︙ |
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 149 150 151 152 153 154 155 156 157 158 159 160 161 | 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 = <boolean>; /* 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, |
︙ | ︙ |
Changes to pages/sqlanalyze.in.
︙ | ︙ | |||
71 72 73 74 75 76 77 | 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% | | | 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 | 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% |
︙ | ︙ | |||
111 112 113 114 115 116 117 | 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% | | | 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 | 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 |
︙ | ︙ | |||
1113 1114 1115 1116 1117 1118 1119 | 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% | | | 1113 1114 1115 1116 1117 1118 1119 1120 1121 1122 1123 1124 1125 1126 1127 | 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 |
︙ | ︙ | |||
1198 1199 1200 1201 1202 1203 1204 | 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 | | | 1198 1199 1200 1201 1202 1203 1204 1205 1206 1207 1208 1209 1210 1211 1212 | 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 |
︙ | ︙ | |||
1221 1222 1223 1224 1225 1226 1227 | 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 | | | 1221 1222 1223 1224 1225 1226 1227 1228 1229 1230 1231 1232 1233 1234 1235 | 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. |
︙ | ︙ | |||
1345 1346 1347 1348 1349 1350 1351 | 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 ); | | | 1345 1346 1347 1348 1349 1350 1351 1352 1353 1354 1355 1356 1357 1358 1359 | 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); |
︙ | ︙ |
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 77 | 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 |
︙ | ︙ |