Documentation Source Text

Check-in [3b1bef8768]
Login

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: 3b1bef87684702cbd5779c866b8da9c5a6a9bc5e3ddd8bd29338d834d7f00fce
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
Side-by-Side Diff Ignore Whitespace Patch
Changes to pages/fileformat2.in.
1
2
3
4
5
6
7
8


9
10
11
12
13
14
15
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 SQLite.</p>
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
47


48
49
50
51
52
53
54
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.</p>
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
158


159
160
161
162
163
164
165
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 and earlier, this value is 
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
245
246


247
248
249
250
251
252
253
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 and later).)^
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
404
405
406
407





408
409
410
411
412
413
414
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 had
only 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>
<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
435


436
437
438
439
440
441
442
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 caused the database to be
<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

1366

1367
1368
1369
1370
1371
1372
1373
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]).
3.6.18 and 3.7.8.  The sqlite_stat2 table is neither read nor written by any
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
1404

1405
1406
1407
1408
1409


1410
1411
1412
1413
1414
1415
1416
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 or greater.
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 or greater, then sqlite_stat3 might
be read but not written.
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

1448

1449
1450
1451
1452
1453
1454
1455
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.
3.8.1 or greater.  The sqlite_stat4 table is neither read nor written by any
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
7


8
9
10
11
12
13
14
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 tens of billions of
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
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
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-Sep-20</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-Nov-12</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-Dec-21</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-Jan-30</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-Mar-10</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-Jun-17</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-July-17</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-Aug-13</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-Feb-14</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-Jun-18</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>