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.
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: c079123bb9ae3594e3b84babb1014f50e238d1fe
User & Date: drh 2010-06-24 17:51:23
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
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to pages/fileformat2.in.

140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
...
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
...
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
<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):
................................................................................
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 
................................................................................
<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>







|







 







|






|


|







 







|
|







140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
...
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
...
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
<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):
................................................................................
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 
................................................................................
<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