Documentation Source Text

Check-in [c079123bb9]
Login

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

Overview
Comment:Improve explanation of page_size limitations in WAL mode. Fix a broken link in fileformat2.in.
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: c079123bb9ae3594e3b84babb1014f50e238d1fe
User & Date: drh 2010-06-24 17:51:23.000
Context
2010-06-25
13:45
Fix typos on the wal.html page. (check-in: 3bf3e14239 user: drh tags: trunk)
2010-06-24
17:51
Improve explanation of page_size limitations in WAL mode. Fix a broken link in fileformat2.in. (check-in: c079123bb9 user: drh tags: trunk)
11:49
Add the ability to include development snapshot amalgamations on the download page. (check-in: 3a3f77dd1d user: drh tags: trunk)
Changes
Unified Diff Ignore Whitespace Patch
Changes to pages/fileformat2.in.
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
<tr><td valign=top align=center>60<td valign=top align=center>4<td align=left>
The "user version" as read and set by the [user_version pragma].
<tr><td valign=top align=center>64<td valign=top align=center>4<td align=left>
True (non-zero) for incremental-vacuum mode.  False (zero) otherwise.
<tr><td valign=top align=center>68<td valign=top align=center>24<td align=left>
Reserved for expansion.  Must be zero.
<tr><td valign=top align=center>92<td valign=top align=center>4<td align=left>
The "version-valid-for" integer.
<tr><td valign=top align=center>96<td valign=top align=center>4<td align=left>
[SQLITE_VERSION_NUMBER]
</table></center>

<h4>1.2.1 Magic Header String</h4>

<p>Every SQLite database file begins with the following 16 bytes (in hex):







|







140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
<tr><td valign=top align=center>60<td valign=top align=center>4<td align=left>
The "user version" as read and set by the [user_version pragma].
<tr><td valign=top align=center>64<td valign=top align=center>4<td align=left>
True (non-zero) for incremental-vacuum mode.  False (zero) otherwise.
<tr><td valign=top align=center>68<td valign=top align=center>24<td align=left>
Reserved for expansion.  Must be zero.
<tr><td valign=top align=center>92<td valign=top align=center>4<td align=left>
The [version-valid-for number].
<tr><td valign=top align=center>96<td valign=top align=center>4<td align=left>
[SQLITE_VERSION_NUMBER]
</table></center>

<h4>1.2.1 Magic Header String</h4>

<p>Every SQLite database file begins with the following 16 bytes (in hex):
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
ignored the in-header database size and used the actual file size
exclusively.  Newer versions of SQLite use the in-header database
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 value] at offset 92.
The in-header database size should always be valid 
when the database is only modified using recent versions of SQLite
(versions 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 value 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 integer.</p>

<h4>1.2.7 Free page list</h4>

<p>Unused pages in the database file are stored on a freelist.  The
4-byte big-endian integer at offset 32 stores the page number of
the first page of the freelist, or zero if the freelist is empty.
The 4-byte big-endian integer at offset 36 stores stores the total 







|






|


|







223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
ignored the in-header database size and used the actual file size
exclusively.  Newer versions of SQLite use the in-header database
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 should always be valid 
when the database is only modified using recent versions of SQLite
(versions 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>

<h4>1.2.7 Free page list</h4>

<p>Unused pages in the database file are stored on a freelist.  The
4-byte big-endian integer at offset 32 stores the page number of
the first page of the freelist, or zero if the freelist is empty.
The 4-byte big-endian integer at offset 36 stores stores the total 
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
<tcl>hd_fragment validfor {version-valid-for number}</tcl>
<h4>1.2.14 Write library version number and version-valid-for number</h4>

<p>The 4-byte big-endian integer at offset 96 stores the 
[SQLITE_VERSION_NUMBER] value.  The 4-byte big-ending integer at
offset 92 is the value of the [change counter] when the version number
was stored.  The integer at offset 92 indicates which transaction
the version number is valid for and is sometimes calle the "version-valid-for"
integer.

<h4>1.2.15 Header space reserved for expansion</h4>

<p>All other bytes of the database file header are reserved for
future expansion and must be set to zero.</p>

<h3>1.3 The Lock-Byte Page</h3>







|
|







330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
<tcl>hd_fragment validfor {version-valid-for number}</tcl>
<h4>1.2.14 Write library version number and version-valid-for number</h4>

<p>The 4-byte big-endian integer at offset 96 stores the 
[SQLITE_VERSION_NUMBER] value.  The 4-byte big-ending integer at
offset 92 is the value of the [change counter] when the version number
was stored.  The integer at offset 92 indicates which transaction
the version number is valid for and is sometimes called the
"version-valid-for number".

<h4>1.2.15 Header space reserved for expansion</h4>

<p>All other bytes of the database file header are reserved for
future expansion and must be set to zero.</p>

<h3>1.3 The Lock-Byte Page</h3>
Changes to pages/wal.in.
29
30
31
32
33
34
35
36

37

38
39
40
41
42
43
44
    support this but third-party extension VFSes for custom operating
    systems might not.
<li>All processes using a database must be on the same host computer;
    WAL does not work over a network filesystem.
<li>Transactions that involve changes against multiple [ATTACH | ATTACHed]
    databases are atomic for each individual database, but are not
    atomic across all databases as a set.
<li>With WAL, it is not possible to change the database page sizes 

    using [VACUUM] or when restoring from a backup using the [backup API].

<li>WAL might be very slightly slower (perhaps 1% or 2% slower)
    than the traditional rollback-journal approach
    in applications that do mostly reads and seldom write.
<li>There is an additional persistent "*-wal" file associated with each
    database, which can make SQLite less appealing for use as an 
    [application file-format].
<li>There is the extra operation of [checkpointing] which, though automatic







|
>
|
>







29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
    support this but third-party extension VFSes for custom operating
    systems might not.
<li>All processes using a database must be on the same host computer;
    WAL does not work over a network filesystem.
<li>Transactions that involve changes against multiple [ATTACH | ATTACHed]
    databases are atomic for each individual database, but are not
    atomic across all databases as a set.
<li>It is not possible to change the database page size after entering WAL
    mode, either on an empty databse or by using [VACUUM] or by restoring
    from a backup using the [backup API].  You must be in a rollback journal
    mode to change the page size.
<li>WAL might be very slightly slower (perhaps 1% or 2% slower)
    than the traditional rollback-journal approach
    in applications that do mostly reads and seldom write.
<li>There is an additional persistent "*-wal" file associated with each
    database, which can make SQLite less appealing for use as an 
    [application file-format].
<li>There is the extra operation of [checkpointing] which, though automatic