Documentation Source Text

Check-in [46434fb185]
Login

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

Overview
Comment:fileformat.html updates.
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1:46434fb185ae27caf018717d2c374dc7ded4c8dd
User & Date: dan 2009-04-13 09:49:03
Context
2009-04-13
14:24
Modifications to fileformat.html. check-in: 491737c7cf user: dan tags: trunk
09:49
fileformat.html updates. check-in: 46434fb185 user: dan tags: trunk
2009-04-11
06:09
Update diagrams in fileformat.html. check-in: 7540fed845 user: dan tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to images/fileformat/indexpage.odg.

cannot compute difference between binary files

Added images/fileformat/journal_format.gif.

cannot compute difference between binary files

Changes to images/fileformat/master_journal_ptr.gif.

cannot compute difference between binary files

Changes to images/fileformat/rtdocs.css.

53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
  **     <span class=todo>Fix this bit!</span>
  **     <p class=todo>Longer todo note.</p>
  */
  .todo        { color: #AA3333 ; font-style : italic }
  .todo:before { content: 'TODO:' }
  p.todo       { border: solid #AA3333 1px; padding: 1ex }

  cite         { font-style: normal; font-weight: normal }
  cite a       { color: inherit; text-decoration: none }
  .defnlink    { 
    color: inherit; 
    text-decoration: none;
    font-style: italic;
  }
  .defnlink:visited    { color: inherit }
  h1 .defnlink, h2 .defnlink, h3 .defnlink, h4 .defnlink, .defn .defnlink{ font-style: inherit }
  :link:hover,:visited:hover { background: wheat }

  img {
    display:block;
  }








<
|













53
54
55
56
57
58
59

60
61
62
63
64
65
66
67
68
69
70
71
72
73
  **     <span class=todo>Fix this bit!</span>
  **     <p class=todo>Longer todo note.</p>
  */
  .todo        { color: #AA3333 ; font-style : italic }
  .todo:before { content: 'TODO:' }
  p.todo       { border: solid #AA3333 1px; padding: 1ex }


  cite a, cite a:visited { color: inherit; text-decoration: none ; font-style: normal; }
  .defnlink    { 
    color: inherit; 
    text-decoration: none;
    font-style: italic;
  }
  .defnlink:visited    { color: inherit }
  h1 .defnlink, h2 .defnlink, h3 .defnlink, h4 .defnlink, .defn .defnlink{ font-style: inherit }
  :link:hover,:visited:hover { background: wheat }

  img {
    display:block;
  }

Changes to pages/fileformat.in.

81
82
83
84
85
86
87
88

89
90
91
92
93
94
95
...
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
...
380
381
382
383
384
385
386

387
388
389
390
391
392
393
394
...
398
399
400
401
402
403
404

405
406
407
408
409
410
411
....
2148
2149
2150
2151
2152
2153
2154






2155
2156
2157
2158
2159
2160
2161
2162
2163


2164
2165
2166
2167
2168
2169
2170
2171
2172
2173
2174
2175









2176













2177
2178
2179
2180
2181
2182
2183
      <p><i>Figure $::SectionNumbers(fig) - $zCaption</i>
      </center>
  }
}

proc FixReferences {body} {
  foreach {term anchor} $::Glossary {
    set re [string map {" " [[:space:]]+} $term]

    set body [regsub -all -nocase $re $body "<a class=defnlink href=\"#$anchor\">\\0</a>"]
  }

  foreach {key value} [array get ::References] {
    foreach {zNumber zTitle} $value {}
    lappend l <cite>$key</cite> "<cite><a href=\"#$key\" title=\"$zTitle\">$zNumber</a></cite>"
  }
................................................................................
set body [subst -novariables {

[h1 "Document Overview"]

  [h2 "Scope and Purpose"]

  <p>
    This document is designed to serve two purposes:
  <ul>
    <li>to provide an engineering guide to the file format used by SQLite, and



    <li>to provide system requirements specifying the behaviour of the SQLite
        software modules responsible for creating and manipulating the
        formatted database files.

  </ul>

  <p>
    Exactly how the database file is created and safely updated on the 
    persistent media is outside the scope of this document. As such no
    mention of journal or statement files is made. Database transactions
    are referred to only with respect to those file manipulation operations 
    (i.e. change-counter management and database reorganization in auto-vacuum
    mode) that occur once per transaction. Here we are concerned solely with
    the arrangement of bytes in the database file, not the interactions between
    the SQLite library and the VFS (Virtual File System) interface.  

  <p>

    Similarly, the various interfaces and SQL language features that may
    be used to manipulate the contents of a database are not dealt with
    here. This document describes the effect of various operations
    on the database, such as creating a table or inserting a new record.
    The myriad of ways that these operations or sets of these operations
    may be achieved using SQLite are dealt with elsewhere.
  <p class=todo>
    Add references to the documents that do describe these things. One other
    document will concentrate on the pager module and the way it uses the VFS
    interface to safely create and update database files.  The other will be

    the document that describes the supported SQL language and capabilities.






  [h2 "Document and Requirements Organization"]

    <p>
      Section <cite>sqlite_database_files</cite> contains simple 
      requirements describing the relationship between SQLite and the
      definition of a <i>well-formed SQLite database file</i>.


    <p>
      Section <cite>database_file_format</cite> describes the various fields
      and sub-structures that make up the SQLite database file format.
<!--



    <p>


































      Section <cite>database_file_manipulation</cite> describes the way in
      which these fields and data structures are created, initialized and
      updated.  
-->












  [h2 "Glossary"]
    <table id=glossary>
      <tr><td>Auto-vacuum last root-page<td>
        A page number stored as 32-bit integer at byte offset 52 of the
        database file header (see section <cite>file_header</cite>). In
        an auto-vacuum database, this is the numerically largest 
................................................................................
      [Glossary "Journal Section" {<span class=todo>This.</span>}]
      [Glossary "Journal Header" {<span class=todo>This.</span>}]
      [Glossary "Journal Record" {<span class=todo>This.</span>}]
      [Glossary "Master Journal Pointer" {<span class=todo>This.</span>}]

    </table>


[h1 "SQLite Database Files" sqlite_database_files]
 
  <p>
    The bulk of this document, section <cite>database_file_format</cite>,
    contains the definition of a <i>well-formed SQLite database file</i>.
    SQLite is required to create database files that meet this definition.

  <p class=req id=H30010>
................................................................................
    Additionally, the database file should contain a serialized version
    of the logical database produced by the transaction. For all but the
    most trivial logical databases, there are many possible serial 
    representations.

  <p class=req id=H30020>
          [fileformat_import_requirement H30020]


<!--
  <p>
    Section <cite>database_file_manipulation</cite> contains requirements
    describing in more detail the way in which SQLite manipulates the
    fields and data structures described in section
    <cite>database_file_format</cite> under various circumstances. These
................................................................................
      contents of an SQLite database within the file-system. These techniques
      allow a complex set of modifications to be made to the database image
      atomically, eliminating the risk of database corruption due to 
      application, Operating System (OS) or power failure while updating the
      database.

[h2 "Journal File Formats" journal_file_formats]







[h3 "Journal File Details" journal_file_format]

    <p>
      This section describes the format used by an SQLite journal file.

    <p>
      A journal file consists of one or more journal sections, optionally
      followed by a master journal pointer field.



    <p>
      A journal file consists of one or more <i>journal headers</i>, zero
      or more <i>journal records</i> and optionally a <i>master journal
      pointer</i>. Each journal file always begins with a
      <i>journal header</i>, followed by zero or more <i>journal records</i>.
      Following this may be a second <i>journal header</i> followed by a
      second set of zero or more <i>journal records</i> and so on. There
      is no limit to the number of <i>journal headers</i> a journal file
      may contain. Following the <i>journal headers</i> and their accompanying
      sets of <i>journal records</i> may be the optional <i>master journal
      pointer</i>. Or, the file may simply end following the final <i>journal









      record</i>.














    [h4 "Journal Header Format" journal_header_format]

    <p>
      A <i>journal header</i> is <i>sector-size</i> bytes in size, where <i>
      sector-size</i> is the value returned by the xSectorSize method of
      the file handle opened on the database file. Only the first 28 bytes







|
>







 







<
<
|
>

>
|
|
|
>
|
>

<
<
|
|
|
|
<
<
>

>
|
|
|
|
|
|
|
|
|
<
>
|
>
>
>
>
>


>

<
<
<
>
>

|
<
<
>
>
>

>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
|
<
<
<
>
>
>
>
>
>
>
>
>
>
>







 







>
|







 







>







 







>
>
>
>
>
>








|
>
>


<
<
|
|
|
|
|
|
|
|
>
>
>
>
>
>
>
>
>
|
>
>
>
>
>
>
>
>
>
>
>
>
>







81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
...
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
184
185
186
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
...
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
...
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
....
2199
2200
2201
2202
2203
2204
2205
2206
2207
2208
2209
2210
2211
2212
2213
2214
2215
2216
2217
2218
2219
2220
2221
2222
2223
2224


2225
2226
2227
2228
2229
2230
2231
2232
2233
2234
2235
2236
2237
2238
2239
2240
2241
2242
2243
2244
2245
2246
2247
2248
2249
2250
2251
2252
2253
2254
2255
2256
2257
2258
2259
2260
2261
2262
      <p><i>Figure $::SectionNumbers(fig) - $zCaption</i>
      </center>
  }
}

proc FixReferences {body} {
  foreach {term anchor} $::Glossary {
    set re [string map {" " [-[:space:]]+} $term]
    set re "${re}s?"
    set body [regsub -all -nocase $re $body "<a class=defnlink href=\"#$anchor\">\\0</a>"]
  }

  foreach {key value} [array get ::References] {
    foreach {zNumber zTitle} $value {}
    lappend l <cite>$key</cite> "<cite><a href=\"#$key\" title=\"$zTitle\">$zNumber</a></cite>"
  }
................................................................................
set body [subst -novariables {

[h1 "Document Overview"]

  [h2 "Scope and Purpose"]

  <p>


    This document provides an engineering guide to the file formats used by 
    SQLite to store databases on disk. There are two purposes for this:

  <ol>
    <li><p> to make it easier to continue to maintain, test and improve the SQLite 
        software library, and
    <li><p> to facilitate the development of external (non-SQLite) software that may 
        operate directly on SQLite databases stored within a file-system.
  </ol>

  <p>


    None of the information contained in this document is required by programmers
    wishing to use the SQLite library in applications. The intended audience is
    engineers working on SQLite itself or those interested in creating alternative
    methods of accessing SQLite databases (without using SQLite).



  <p>
    It is intended that this document shall provide all the information required to
    create an system that reads and writes SQLite databases in a way that is
    completely compatible with SQLite itself. Or, put another way, this document
    defines the protocols that all SQLite database users (including SQLite) are
    required to follow. The availability of this information makes an SQLite
    database an even safer choice for long-term data storage. If, at some point
    in the future, the SQLite software library cannot be used to access an
    SQLite database that contains useful data, a procedure or software module
    may be developed based on the content of this document to extract the
    required data.


  <p>
    As well as file format descriptions, this document also describes the way in
    which SQLite compatible clients are required to lock database files when
    reading and/or writing them. It also contains other requirements that must
    be adhered to by all writers in order to allow the implementation of in-memory 
    schema and data caches by database clients.

  [h2 "Document and Requirements Organization"]

    <p>



      The content of this document is divided into three sections.

    <p>
      <b>Section <cite>database_file_format</cite></b> describes the format 


      of a database image. A database image is the serialized form of an 
      SQLite database that is stored on disk.

    <p>
      Usually, an SQLite database image is stored in a single file on disk, 
      an SQLite database file. However, while the database image as stored 
      on disk is being modified, it may be temporarily stored in a more
      convoluted format, distributed between two files, the database file
      and a journal file. If a failure occurs while modifying a database image
      in this fashion, then the database image must be extracted from the
      database and journal files found in the file-system following recovery
      (other documentation refers to this as "hot journal rollback"). <b>Section 
      <cite>file_system_usage</cite></b> describes the format used by the
      journal file and the rules for correctly reading a database image from
      the combination of a database file and journal file. The same section
      also includes guidelines and requirements describing the intended method
      for atomically updating the database image within the file-system without
      risking database corruption.

    <p>
      <b>Section <cite>locking_protocol</cite></b> contains a description of
      and software requirements related to:

    <ul>
      <li>The locking protocol used by SQLite to manage read and write access
          to the database and journal files within the file-system, and
      <li>the change-counter and schema-cookie protocols that must be followed
          by all database writers to facilitate the implementation of
          efficient in-memory caches of the database schema and content by
          readers and writers.
    </ul>

    <p>
      The information in this document may be used to create non-SQLite
      software that reads or writes SQLite databases. Each section contains
      information that may be used to make external software increasingly 
      compatible with SQLite. All data required by an software module that 
      is required to interpret or create SQLite database images may be found
      in section <cite>database_file_format</cite>. Requirements and guidelines



      for software required to safely read and write database images to disk
      using an SQLite compatible system failure recovery mechanism to prevent
      database corruption or data loss are found in section 
      <cite>file_system_usage</cite>. Finally, rules and requirements for software 
      systems required to read and write live SQLite databases within a system that
      includes real SQLite clients that may also read and write to databases
      are presented in section <cite>locking_protocol</cite>.



      

  [h2 "Glossary"]
    <table id=glossary>
      <tr><td>Auto-vacuum last root-page<td>
        A page number stored as 32-bit integer at byte offset 52 of the
        database file header (see section <cite>file_header</cite>). In
        an auto-vacuum database, this is the numerically largest 
................................................................................
      [Glossary "Journal Section" {<span class=todo>This.</span>}]
      [Glossary "Journal Header" {<span class=todo>This.</span>}]
      [Glossary "Journal Record" {<span class=todo>This.</span>}]
      [Glossary "Master Journal Pointer" {<span class=todo>This.</span>}]

    </table>

<!--
h1 "SQLite Database Files" sqlite_database_files
 
  <p>
    The bulk of this document, section <cite>database_file_format</cite>,
    contains the definition of a <i>well-formed SQLite database file</i>.
    SQLite is required to create database files that meet this definition.

  <p class=req id=H30010>
................................................................................
    Additionally, the database file should contain a serialized version
    of the logical database produced by the transaction. For all but the
    most trivial logical databases, there are many possible serial 
    representations.

  <p class=req id=H30020>
          [fileformat_import_requirement H30020]
-->

<!--
  <p>
    Section <cite>database_file_manipulation</cite> contains requirements
    describing in more detail the way in which SQLite manipulates the
    fields and data structures described in section
    <cite>database_file_format</cite> under various circumstances. These
................................................................................
      contents of an SQLite database within the file-system. These techniques
      allow a complex set of modifications to be made to the database image
      atomically, eliminating the risk of database corruption due to 
      application, Operating System (OS) or power failure while updating the
      database.

[h2 "Journal File Formats" journal_file_formats]

  <p>
    The following sub-sections describe the formats used by SQLite journal
    files (section <cite>journal_file_format</cite>) and master journal files
    (section <cite>masterjournal_file_format</cite>).


[h3 "Journal File Details" journal_file_format]

    <p>
      This section describes the format used by an SQLite journal file.

    <p>
      A journal file consists of one or more journal sections, optionally
      followed by a master journal pointer field. The first journal section
      starts at the beginning of the journal file. There is no limit to the
      number of journal sections that may be present in a single journal file.

    <p>


      Each journal section consists of a journal header immediately followed
      by zero or more journal records. The format of journal header and journal
      records are described in sections <cite>journal_header_format</cite> and
      <cite>journal_record_format</cite> respectively. One of the numeric fields 
      stored in a journal header is the sector size field. Each journal section 
      in a journal file must be an integer multiple of the sector size stored
      in the first journal header of the journal file (the value of the sector
      size field in the second and subsequent journal headers is not used). If
      the sum of the sizes of the journal header and journal records in a journal
      section is not an integer multiple of the sector size, then up to 
      (sector-size - 1) bytes of unused space (padding) follow the end of the
      last journal record to make up the required length.

    <p>
      Figure <cite>figure_journal_format</cite> illustrates a journal file that 
      contains <i>N</i> journal sections and a master journal pointer. The first
      journal section in the file is depicted as containing <i>M</i> journal
      records.

    [Figure journal_format.gif figure_journal_format "Journal File Format"]

    <p>
      The following requirements define a well-formed journal section. This concept
      is used in section <cite>reading_from_files</cite>. Note that a journal 
      section that is not strictly speaking a well-formed journal section often
      contains important data. See section <cite>reading_from_files</cite> for details.

        [fileformat_import_requirement2 H35210]
        [fileformat_import_requirement2 H35220]
        [fileformat_import_requirement2 H35230]
        [fileformat_import_requirement2 H35240]

    [h4 "Journal Header Format" journal_header_format]

    <p>
      A <i>journal header</i> is <i>sector-size</i> bytes in size, where <i>
      sector-size</i> is the value returned by the xSectorSize method of
      the file handle opened on the database file. Only the first 28 bytes

Changes to req/hlr30000.txt.

975
976
977
978
979
980
981























A buffer shall only be considered to be a well-formed master journal pointer
if the value stored as a 4-byte big-endian integer starting 12 bytes before
the end of the buffer is equal to the sum of all bytes, each interpreted
as an 8-bit unsigned integer, starting at offset 4 of the buffer and continuing
until offset (buffer-size - 16) (the 17th last byte of the buffer).
































>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
975
976
977
978
979
980
981
982
983
984
985
986
987
988
989
990
991
992
993
994
995
996
997
998
999
1000
1001
1002
1003
1004
A buffer shall only be considered to be a well-formed master journal pointer
if the value stored as a 4-byte big-endian integer starting 12 bytes before
the end of the buffer is equal to the sum of all bytes, each interpreted
as an 8-bit unsigned integer, starting at offset 4 of the buffer and continuing
until offset (buffer-size - 16) (the 17th last byte of the buffer).



HLR H35210
A buffer shall be considered to contain a well-formed journal section 
record if it is not excluded from this category by requirements H35220,
H35230 or H35240.

HLR H35220
A buffer shall only be considered to contain a well-formed journal section 
if the first 28 bytes of it contain a well-formed journal header.

HLR H35230
A buffer shall only be considered to contain a well-formed journal section 
if, beginning at byte offset sector-size, it contains a sequence of 
record-count well-formed journal records. In this case sector-size and
record-count are the integer values stored in the sector size and record
count fields of the journal section's journal header.

HLR H35240
A buffer shall only be considered to contain a well-formed journal section 
if it is an integer multiple of sector-size bytes in size, where sector-size 
is the value stored in the sector size field of the journal section's journal
header.