Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | Readability improvements to the file format documentation. |
---|---|
Downloads: | Tarball | ZIP archive |
Timelines: | family | ancestors | descendants | both | trunk |
Files: | files | file ages | folders |
SHA3-256: |
3b1bef87684702cbd5779c866b8da9c5 |
User & Date: | drh 2017-08-31 00:54:35.587 |
Context
2017-09-01
| ||
20:53 | Updates to the size and performance chart (check-in: 5e4cd244d1 user: drh tags: trunk) | |
2017-08-31
| ||
00:54 | Readability improvements to the file format documentation. (check-in: 3b1bef8768 user: drh tags: trunk) | |
2017-08-30
| ||
04:50 | Update the speed-and-size spreadsheet. (check-in: 402ce78c69 user: drh tags: trunk) | |
Changes
Changes to pages/fileformat2.in.
1 2 3 4 5 6 7 | <title>Database File Format</title> <alt-title>On-Disk Format</alt-title> <tcl>hd_keywords {file format} {second edition file format document}</tcl> <table_of_contents> <p>This document describes and defines the on-disk database file | | > | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | <title>Database File Format</title> <alt-title>On-Disk Format</alt-title> <tcl>hd_keywords {file format} {second edition file format document}</tcl> <table_of_contents> <p>This document describes and defines the on-disk database file format used by all releases of SQLite since version 3.0.0 ([dateof:3.0.0]).</p> <h1>The Database File</h1> <p>The complete state of an SQLite database is usually contained in a single file on disk called the "main database file".</p> <p>During a transaction, SQLite stores additional information |
︙ | ︙ | |||
40 41 42 43 44 45 46 | SQLite database is a single 512-byte page. The maximum size database would be 2147483646 pages at 65536 bytes per page or 140,737,488,224,256 bytes (about 140 terabytes). Usually SQLite will hit the maximum file size limit of the underlying filesystem or disk hardware long before it hits its own internal size limit.</p> <p>In common use, SQLite databases tend to range in size from a few kilobytes | | > | 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 | SQLite database is a single 512-byte page. The maximum size database would be 2147483646 pages at 65536 bytes per page or 140,737,488,224,256 bytes (about 140 terabytes). Usually SQLite will hit the maximum file size limit of the underlying filesystem or disk hardware long before it hits its own internal size limit.</p> <p>In common use, SQLite databases tend to range in size from a few kilobytes to a few gigabytes, though terabyte-size SQLite databases are known to exist in production.</p> <p>At any point in time, every page in the main database has a single use which is one of the following: <ul> <li>The lock-byte page <li>A freelist page <ul> |
︙ | ︙ | |||
151 152 153 154 155 156 157 | (in hex): 53 51 4c 69 74 65 20 66 6f 72 6d 61 74 20 33 00. This byte sequence corresponds to the UTF-8 string "SQLite format 3" including the nul terminator character at the end.</p> <h3>Page Size</h3> <p>The two-byte value beginning at offset 16 determines the page size of | | > | 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 | (in hex): 53 51 4c 69 74 65 20 66 6f 72 6d 61 74 20 33 00. This byte sequence corresponds to the UTF-8 string "SQLite format 3" including the nul terminator character at the end.</p> <h3>Page Size</h3> <p>The two-byte value beginning at offset 16 determines the page size of the database. For SQLite versions 3.7.0.1 ([dateof:3.7.0.1]) and earlier, this value is interpreted as a big-endian integer and must be a power of two between 512 and 32768, inclusive. Beginning with SQLite [version 3.7.1] ([dateof:3.7.1]), a page size of 65536 bytes is supported. The value 65536 will not fit in a two-byte integer, so to specify a 65536-byte page size, the value at offset 16 is 0x00 0x01. This value can be interpreted as a big-endian |
︙ | ︙ | |||
238 239 240 241 242 243 244 | size if it is available but fall back to the actual file size if the in-header database size is not valid.</p> <p>^The in-header database size is only considered to be valid if it is non-zero and if the 4-byte [change counter] at offset 24 exactly matches the 4-byte [version-valid-for number] at offset 92. ^(The in-header database size is always valid | | | | 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 | size if it is available but fall back to the actual file size if the in-header database size is not valid.</p> <p>^The in-header database size is only considered to be valid if it is non-zero and if the 4-byte [change counter] at offset 24 exactly matches the 4-byte [version-valid-for number] at offset 92. ^(The in-header database size is always valid when the database is only modified using recent versions of SQLite, versions 3.7.0 ([dateof:3.7.0]) and later.)^ If a legacy version of SQLite writes to the database, it will not know to update the in-header database size and so the in-header database size could be incorrect. But legacy versions of SQLite will also leave the version-valid-for number at offset 92 unchanged so it will not match the change-counter. Hence, invalid in-header database sizes can be detected (and ignored) by observing when the change-counter does not match the version-valid-for number.</p> |
︙ | ︙ | |||
397 398 399 400 401 402 403 | implementations may choose to read or write bytes on the lock-byte page according to the needs and proclivities of the underlying system. The unix and win32 [VFS] implementations that come built into SQLite do not write to the lock-byte page, but third-party VFS implementations for other operating systems might.</p> | | > | | | | 400 401 402 403 404 405 406 407 408 409 410 411 412 413 414 415 416 417 418 | implementations may choose to read or write bytes on the lock-byte page according to the needs and proclivities of the underlying system. The unix and win32 [VFS] implementations that come built into SQLite do not write to the lock-byte page, but third-party VFS implementations for other operating systems might.</p> <p>The lock-byte page arose from the need to support Win95 which was the predominant operating system when this file format was designed and which only supported mandatory file locking. All modern operating systems that we know of support advisory file locking, and so the lock-byte page is not really needed any more, but is retained for backwards compatibility.</p> <tcl>hd_fragment {freelist} {freelist} {free-page list}</tcl> <h2>The Freelist</h2> <p>A database file might contain one or more pages that are not in active use. Unused pages can come about, for example, when information is deleted from the database. Unused pages are stored on the freelist |
︙ | ︙ | |||
428 429 430 431 432 433 434 | ^(Call the second integer on a freelist trunk page L. If L is greater than zero then integers with array indexes between 2 and L+1 inclusive contain page numbers for freelist leaf pages.)^</p> <p>Freelist leaf pages contain no information. ^SQLite avoids reading or writing freelist leaf pages in order to reduce disk I/O.</p> | | > | 432 433 434 435 436 437 438 439 440 441 442 443 444 445 446 447 | ^(Call the second integer on a freelist trunk page L. If L is greater than zero then integers with array indexes between 2 and L+1 inclusive contain page numbers for freelist leaf pages.)^</p> <p>Freelist leaf pages contain no information. ^SQLite avoids reading or writing freelist leaf pages in order to reduce disk I/O.</p> <p>A bug in SQLite versions prior to 3.6.0 ([dateof:3.6.0]) caused the database to be reported as corrupt if any of the last 6 entries in the freelist trunk page array contained non-zero values. Newer versions of SQLite do not have this problem. ^However, newer versions of SQLite still avoid using the last six entries in the freelist trunk page array in order that database files created by newer versions of SQLite can be read by older versions of SQLite.</p> |
︙ | ︙ | |||
1359 1360 1361 1362 1363 1364 1365 | WITHOUT ROWID table.)^ <tcl>hd_fragment stat2tab {sqlite_stat2}</tcl> <h3>The sqlite_stat2 table</h3> <p>The sqlite_stat2 is only created and is only used if SQLite is compiled with SQLITE_ENABLE_STAT2 and if the SQLite version number is between | > | | 1364 1365 1366 1367 1368 1369 1370 1371 1372 1373 1374 1375 1376 1377 1378 1379 | WITHOUT ROWID table.)^ <tcl>hd_fragment stat2tab {sqlite_stat2}</tcl> <h3>The sqlite_stat2 table</h3> <p>The sqlite_stat2 is only created and is only used if SQLite is compiled with SQLITE_ENABLE_STAT2 and if the SQLite version number is between 3.6.18 ([dateof:3.6.18]) and 3.7.8 ([dateof:3.7.8]). The sqlite_stat2 table is neither read nor written by any version of SQLite before 3.6.18 nor after 3.7.8. The sqlite_stat2 table contains additional information about the distribution of keys within an index. The schema of the sqlite_stat2 table is as follows: <blockquote><pre> CREATE TABLE sqlite_stat2(tbl,idx,sampleno,sample); |
︙ | ︙ | |||
1397 1398 1399 1400 1401 1402 1403 | sqlite_stat2 table, if is exists, is simply ignored. <tcl>hd_fragment stat3tab {sqlite_stat3} SQLITE_STAT3</tcl> <h3>The sqlite_stat3 table</h3> <p>The sqlite_stat3 is only used if SQLite is compiled with [SQLITE_ENABLE_STAT3] or [SQLITE_ENABLE_STAT4] | | | | | 1403 1404 1405 1406 1407 1408 1409 1410 1411 1412 1413 1414 1415 1416 1417 1418 1419 1420 1421 1422 | sqlite_stat2 table, if is exists, is simply ignored. <tcl>hd_fragment stat3tab {sqlite_stat3} SQLITE_STAT3</tcl> <h3>The sqlite_stat3 table</h3> <p>The sqlite_stat3 is only used if SQLite is compiled with [SQLITE_ENABLE_STAT3] or [SQLITE_ENABLE_STAT4] and if the SQLite version number is 3.7.9 ([dateof:3.7.9]) or greater. The sqlite_stat3 table is neither read nor written by any version of SQLite before 3.7.9. If the [SQLITE_ENABLE_STAT4] compile-time option is used and the SQLite version number is 3.8.1 ([dateof:3.8.1]) or greater, then sqlite_stat3 might be read but not written. The sqlite_stat3 table contains additional information about the distribution of keys within an index, information that the query planner can use to devise better and faster query algorithms. ^(The schema of the sqlite_stat3 table is as follows: <blockquote><pre> CREATE TABLE sqlite_stat3(tbl,idx,nEq,nLt,nDLt,sample); |
︙ | ︙ | |||
1441 1442 1443 1444 1445 1446 1447 | sample S1 must have a smaller rowid than sample S2.)^ <tcl>hd_fragment stat4tab {sqlite_stat4} SQLITE_STAT4</tcl> <h3>The sqlite_stat4 table</h3> <p>The sqlite_stat4 is only created and is only used if SQLite is compiled with [SQLITE_ENABLE_STAT4] and if the SQLite version number is | > | | 1447 1448 1449 1450 1451 1452 1453 1454 1455 1456 1457 1458 1459 1460 1461 1462 | sample S1 must have a smaller rowid than sample S2.)^ <tcl>hd_fragment stat4tab {sqlite_stat4} SQLITE_STAT4</tcl> <h3>The sqlite_stat4 table</h3> <p>The sqlite_stat4 is only created and is only used if SQLite is compiled with [SQLITE_ENABLE_STAT4] and if the SQLite version number is 3.8.1 ([dateof:3.8.1]) or greater. The sqlite_stat4 table is neither read nor written by any version of SQLite before 3.8.1. The sqlite_stat4 table contains additional information about the distribution of keys within an index or the distribution of keys in the primary key of a [WITHOUT ROWID] table. The query planner can sometimes use the additional information in the sqlite_stat4 table to devise better and faster query algorithms. ^(The schema of the sqlite_stat4 table is as follows: |
︙ | ︙ |
Changes to pages/formatchng.in.
1 2 3 4 5 6 | <title>File Format Changes in SQLite</title> <h2>File Format Changes in SQLite</h2> <p> The [file format|underlying file format] for SQLite databases does not | | > | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | <title>File Format Changes in SQLite</title> <h2>File Format Changes in SQLite</h2> <p> The [file format|underlying file format] for SQLite databases does not change in incompatible ways. There are literally hundredss of billions, perhaps trillions, of SQLite database files in circulation and the SQLite developers are committing to supporting those files for decades into the future. </p> <p> This document describes incompatibilities that have occurred in SQLite prior to 2004. Since 2004, there have been enhancements to |
︙ | ︙ | |||
33 34 35 36 37 38 39 | <tr> <th>Version Change</th> <th>Approx. Date</th> <th>Description Of File Format Change</th> </tr> <tr> <td valign="top">1.0.32 to 2.0.0</td> | | | | | | | | | | | | 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 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 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 | <tr> <th>Version Change</th> <th>Approx. Date</th> <th>Description Of File Format Change</th> </tr> <tr> <td valign="top">1.0.32 to 2.0.0</td> <td valign="top">2001-09-20</td> <td>Version 1.0.X of SQLite used the GDBM library as its backend interface to the disk. Beginning in version 2.0.0, GDBM was replaced by a custom B-Tree library written especially for SQLite. The new B-Tree backend is twice as fast as GDBM, supports atomic commits and rollback, and stores an entire database in a single disk file instead using a separate file for each table as GDBM does. The two file formats are not even remotely similar.</td> </tr> <tr> <td valign="top">2.0.8 to 2.1.0</td> <td valign="top">2001-10-12</td> <td>The same basic B-Tree format is used but the details of the index keys were changed in order to provide better query optimization opportunities. Some of the headers were also changed in order to increase the maximum size of a row from 64KB to 24MB.<p> This change is an exception to the version number rule described above in that it is neither forwards or backwards compatible. A complete reload of the database is required. This is the only exception.</td> </tr> <tr> <td valign="top">2.1.7 to 2.2.0</td> <td valign="top">2001-12-21</td> <td>Beginning with version 2.2.0, SQLite no longer builds an index for an INTEGER PRIMARY KEY column. Instead, it uses that column as the actual B-Tree key for the main table.<p>Version 2.2.0 and later of the library will automatically detect when it is reading a 2.1.x database and will disable the new INTEGER PRIMARY KEY feature. In other words, version 2.2.x is backwards compatible to version 2.1.x. But version 2.1.x is not forward compatible with version 2.2.x. If you try to open a 2.2.x database with an older 2.1.x library and that database contains an INTEGER PRIMARY KEY, you will likely get a coredump. If the database schema does not contain any INTEGER PRIMARY KEYs, then the version 2.1.x and version 2.2.x database files will be identical and completely interchangeable.</p> </tr> <tr> <td valign="top">2.2.5 to 2.3.0</td> <td valign="top">2002-01-30</td> <td>Beginning with version 2.3.0, SQLite supports some additional syntax (the "ON CONFLICT" clause) in the CREATE TABLE and CREATE INDEX statements that are stored in the SQLITE_MASTER table. If you create a database that contains this new syntax, then try to read that database using version 2.2.5 or earlier, the parser will not understand the new syntax and you will get an error. Otherwise, databases for 2.2.x and 2.3.x are interchangeable.</td> </tr> <tr> <td valign="top">2.3.3 to 2.4.0</td> <td valign="top">2002-03-10</td> <td>Beginning with version 2.4.0, SQLite added support for views. Information about views is stored in the SQLITE_MASTER table. If an older version of SQLite attempts to read a database that contains VIEW information in the SQLITE_MASTER table, the parser will not understand the new syntax and initialization will fail. Also, the way SQLite keeps track of unused disk blocks in the database file changed slightly. If an older version of SQLite attempts to write a database that was previously written by version 2.4.0 or later, then it may leak disk blocks.</td> </tr> <tr> <td valign="top">2.4.12 to 2.5.0</td> <td valign="top">2002-06-17</td> <td>Beginning with version 2.5.0, SQLite added support for triggers. Information about triggers is stored in the SQLITE_MASTER table. If an older version of SQLite attempts to read a database that contains a CREATE TRIGGER in the SQLITE_MASTER table, the parser will not understand the new syntax and initialization will fail. </td> </tr> <tr> <td valign="top">2.5.6 to 2.6.0</td> <td valign="top">2002-07-17</td> <td>A design flaw in the layout of indices required a file format change to correct. This change appeared in version 2.6.0.<p> If you use version 2.6.0 or later of the library to open a database file that was originally created by version 2.5.6 or earlier, an attempt to rebuild the database into the new format will occur automatically. This can take some time for a large database. (Allow 1 or 2 seconds per megabyte of database under Unix - longer under Windows.) This format conversion is irreversible. It is <strong>strongly</strong> suggested that you make a backup copy of older database files prior to opening them with version 2.6.0 or later of the library, in case there are errors in the format conversion logic.<p> Version 2.6.0 or later of the library cannot open read-only database files from version 2.5.6 or earlier, since read-only files cannot be upgraded to the new format.</p> </td> </tr> <tr> <td valign="top">2.6.3 to 2.7.0</td> <td valign="top">2002-08-13</td> <td><p>Beginning with version 2.7.0, SQLite understands two different datatypes: text and numeric. Text data sorts in memcmp() order. Numeric data sorts in numerical order if it looks like a number, or in memcmp() order if it does not.</p> <p>When SQLite version 2.7.0 or later opens a 2.6.3 or earlier database, it assumes all columns of all tables have type "numeric". For 2.7.0 and later databases, columns have type "text" if their datatype string contains the substrings "char" or "clob" or "blob" or "text". Otherwise they are of type "numeric".</p> <p>Because "text" columns have a different sort order from numeric, indices on "text" columns occur in a different order for version 2.7.0 and later database. Hence version 2.6.3 and earlier of SQLite will be unable to read a 2.7.0 or later database. But version 2.7.0 and later of SQLite will read earlier databases.</p> </td> </tr> <tr> <td valign="top">2.7.6 to 2.8.0</td> <td valign="top">2003-02-14</td> <td><p>Version 2.8.0 introduces a change to the format of the rollback journal file. The main database file format is unchanged. Versions 2.7.6 and earlier can read and write 2.8.0 databases and vice versa. Version 2.8.0 can rollback a transaction that was started by version 2.7.6 and earlier. But version 2.7.6 and earlier cannot rollback a transaction started by version 2.8.0 or later.</p> <p>The only time this would ever be an issue is when you have a program using version 2.8.0 or later that crashes with an incomplete transaction, then you try to examine the database using version 2.7.6 or earlier. The 2.7.6 code will not be able to read the journal file and thus will not be able to rollback the incomplete transaction to restore the database.</p> </td> </tr> <tr> <td valign="top">2.8.14 to 3.0.0</td> <td valign="top">2004-06-18</td> <td><p>Version 3.0.0 is a major upgrade for SQLite that incorporates support for UTF-16, BLOBs, and a more compact encoding that results in database files that are typically 25% to 50% smaller. The new file format is very different and is completely incompatible with the version 2 file format.</p> </td> </tr> </table> </blockquote> |