Documentation Source Text

Check-in [7f154daae5]
Login

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

Overview
Comment:Update of fileformat.html.
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 7f154daae5e5da78740afe22f04757bef787fa81
User & Date: dan 2009-04-10 18:18:15.000
Context
2009-04-11
06:09
Update diagrams in fileformat.html. (check-in: 7540fed845 user: dan tags: trunk)
2009-04-10
18:18
Update of fileformat.html. (check-in: 7f154daae5 user: dan tags: trunk)
2009-04-09
14:55
Begin adding the details of how databases are safely updated to fileformat.html. (check-in: aece6e4e5f user: dan tags: trunk)
Changes
Unified Diff Ignore Whitespace Patch
Changes to pages/fileformat.in.
370
371
372
373
374
375
376







377
378
379
380
381
382
383

      [Glossary "Database image" {
        A serialized blob of data representing an SQLite database. The
        contents of a database file are usually a valid database image.
      }]
      [Glossary "Database file" {<span class=todo>This.</span>}]
      [Glossary "Journal file" {<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>,







>
>
>
>
>
>
>







370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390

      [Glossary "Database image" {
        A serialized blob of data representing an SQLite database. The
        contents of a database file are usually a valid database image.
      }]
      [Glossary "Database file" {<span class=todo>This.</span>}]
      [Glossary "Journal file" {<span class=todo>This.</span>}]
      [Glossary "Page size" {<span class=todo>This.</span>}]
      [Glossary "Sector size" {<span class=todo>This.</span>}]

      [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>,
2147
2148
2149
2150
2151
2152
2153




2154
2155
2156
2157
2158
2159
2160
[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 <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







>
>
>
>







2154
2155
2156
2157
2158
2159
2160
2161
2162
2163
2164
2165
2166
2167
2168
2169
2170
2171
[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
2214
2215
2216
2217
2218
2219
2220














2221
2222
2223
2224
2225
2226
2227
    <p>
      All <i>journal headers</i> are positioned in the file so that they 
      start at a <i>sector size</i> aligned offset. To achieve this, unused
      space may be left between the start of the second and subsequent
      <i>journal headers</i> and the end of the <i>journal records</i>
      associated with the previous header.















  [h4 "Journal Record Format" journal_record_format]

    <p>
      Each <i>journal record</i> contains the original data for a database page
      modified by the <i>write transaction</i>. If a rollback is required, then
      this data may be used to restore the contents of the database page to the
      state it was in before the <i>write transaction</i> was started.







>
>
>
>
>
>
>
>
>
>
>
>
>
>







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
    <p>
      All <i>journal headers</i> are positioned in the file so that they 
      start at a <i>sector size</i> aligned offset. To achieve this, unused
      space may be left between the start of the second and subsequent
      <i>journal headers</i> and the end of the <i>journal records</i>
      associated with the previous header.

    <p>
      The following requirement defines a "well-formed journal header". This concept
      is used in the following sections. A well-formed journal header is defined as
      a blob of 28 bytes for which the journal magic field is set correctly and for
      which both the page size and sector size fields are set to power of two values
      greater than 512. Because there are no restrictions on the values that may be
      stored in the record count, checksum initializer or database page count fields,
      they do not enter into the definition of a well-formed journal header.

      [fileformat_import_requirement2 H35090]
      [fileformat_import_requirement2 H35180]
      [fileformat_import_requirement2 H35190]
      [fileformat_import_requirement2 H35200]

  [h4 "Journal Record Format" journal_record_format]

    <p>
      Each <i>journal record</i> contains the original data for a database page
      modified by the <i>write transaction</i>. If a rollback is required, then
      this data may be used to restore the contents of the database page to the
      state it was in before the <i>write transaction</i> was started.
2246
2247
2248
2249
2250
2251
2252







































2253
2254
2255
2256
2257
2258





2259
2260
2261
2262
2263
2264
2265
      [Tr]<td style="white-space: nowrap">4 + <i>page-size</i><td>4<td>
                        This field contains a checksum value, calculated based
                        on the contents of the journaled database page data
                        (the previous field) and the values stored in the
                        <i>checksum initializer</i> field of the preceding
                        <i>journal header</i>.
    </table>








































    <p>
      The set of <i>journal records</i> that follow a <i>journal header</i>
      in a journal file are packed tightly together. There are no
      alignment requirements for <i>journal records</i> as there are for
      <i>journal headers</i>.






  [h4 "Master Journal Pointer" master_journal_ptr]

    <p>
      To support <i>atomic</i> transactions that modify more than one 
      database file, SQLite sometimes includes a <i>master journal pointer</i>
      record in a journal file. A <i>master journal pointer</i>







>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>






>
>
>
>
>







2271
2272
2273
2274
2275
2276
2277
2278
2279
2280
2281
2282
2283
2284
2285
2286
2287
2288
2289
2290
2291
2292
2293
2294
2295
2296
2297
2298
2299
2300
2301
2302
2303
2304
2305
2306
2307
2308
2309
2310
2311
2312
2313
2314
2315
2316
2317
2318
2319
2320
2321
2322
2323
2324
2325
2326
2327
2328
2329
2330
2331
2332
2333
2334
      [Tr]<td style="white-space: nowrap">4 + <i>page-size</i><td>4<td>
                        This field contains a checksum value, calculated based
                        on the contents of the journaled database page data
                        (the previous field) and the values stored in the
                        <i>checksum initializer</i> field of the preceding
                        <i>journal header</i>.
    </table>

    <p>
      The checksum value stored in each journal record is calculated based
      on the contents of the page data field of the record and the value
      stored in the checksum initializer field of the journal header that
      occurs immediately before the journal record. The checksum initializer
      field is interpreted as a 32-bit unsigned integer. To this value is
      added the value stored in every 200th byte of the page data field,
      interpreted as an 8-bit unsigned integer, beginning with the byte
      at offset (page-size % 200). The sum is accumulated in a 32-bit 
      unsigned integer. Overflow is handled by wrapping around to zero.

      <div style="padding: 0 1ex; float:right">
      <div style="padding: 0 1ex; border:1px solid black">
      Example Checksum Calculation:
      <pre>
  Sum of values:
       0xFFFFFFE1 + 
       0x00000023 +
       0x00000032 +
       0x0000009E +
       0x00000062 +
       0x0000001F
      -----------
      0x100000155

  Truncated to 32-bits: 
       0x00000155</pre>
      </div></div>

    <p>
      For example, if the page-size is 1024 bytes, then the offsets within
      the page of the bytes added to the checksum initializer value are
      24, 224, 424, 624 and 824 (the first byte of the page is offset 0, the
      last byte is offset 1023). If the values of the bytes at these offsets
      are 0x23, 0x32, 0x9E, 0x62 and 0x1F, and the value of the checksum
      initializer field is 0xFFFFFFE1, then the value stored in the checksum
      field of the journal record is 0x00000155.


    <p>
      The set of <i>journal records</i> that follow a <i>journal header</i>
      in a journal file are packed tightly together. There are no
      alignment requirements for <i>journal records</i> as there are for
      <i>journal headers</i>.

      [fileformat_import_requirement2 H35100]
      [fileformat_import_requirement2 H35110]
      [fileformat_import_requirement2 H35120]


  [h4 "Master Journal Pointer" master_journal_ptr]

    <p>
      To support <i>atomic</i> transactions that modify more than one 
      database file, SQLite sometimes includes a <i>master journal pointer</i>
      record in a journal file. A <i>master journal pointer</i>
2313
2314
2315
2316
2317
2318
2319





2320
2321
2322
2323
2324
2325
2326
      [Tr]<td style="white-space: nowrap">12 + <i>name-length</i><td><i>8<td>
               Finally, the <b>journal magic</b> field always contains a
               well-known 8-byte string value; the same value stored in the
               first 8 bytes of a <i>journal header</i>. The well-known
               sequence of bytes is:
                 <pre>0xd9 0xd5 0x05 0xf9 0x20 0xa1 0x63 0xd7</pre>
    </table>






[h3 "Master-Journal File Details" masterjournal_file_format]

  <p>
    A <i>master-journal file</i> contains the full paths to two or more
    <i>journal files</i>, each encoded using UTF-8 encoding and terminated
    by a single nul character (byte value 0x00). There is no padding 







>
>
>
>
>







2382
2383
2384
2385
2386
2387
2388
2389
2390
2391
2392
2393
2394
2395
2396
2397
2398
2399
2400
      [Tr]<td style="white-space: nowrap">12 + <i>name-length</i><td><i>8<td>
               Finally, the <b>journal magic</b> field always contains a
               well-known 8-byte string value; the same value stored in the
               first 8 bytes of a <i>journal header</i>. The well-known
               sequence of bytes is:
                 <pre>0xd9 0xd5 0x05 0xf9 0x20 0xa1 0x63 0xd7</pre>
    </table>

      [fileformat_import_requirement2 H35140]
      [fileformat_import_requirement2 H35150]
      [fileformat_import_requirement2 H35160]
      [fileformat_import_requirement2 H35170]

[h3 "Master-Journal File Details" masterjournal_file_format]

  <p>
    A <i>master-journal file</i> contains the full paths to two or more
    <i>journal files</i>, each encoded using UTF-8 encoding and terminated
    by a single nul character (byte value 0x00). There is no padding 
2556
2557
2558
2559
2560
2561
2562
2563



2564
2565
2566
2567



2568
2569
2570







2571
2572
2573
2574
2575
2576
2577
    with a database image that consists of 3 pages, also of page-size bytes
    each. The contents of the initial database image pages are A, B, C and
    D respectively. The final database image content is A, E and C. As 
    depicted, the file-system contains the initial database image, ABCD.
    However, if the journal file were to be somehow invalidated, then the 
    file-system would contain the final database image, AEC.

    [Figure filesystem2.gif figure_filesystem2 "Interim file-system state"]





  [h3 "Multiple Database Transactions" multi_db_transactions]





[h1 "SQLite Interoperabilty Requirements" locking_protocol]








[h1 References]

  <table id="refs" style="width:auto; margin: 1em 5ex">
    <tr><td style="width:5ex" id="ref_comer_btree">\[1\]<td>
     Douglas Comer, <u>Ubiquitous B-Tree</u>, ACM Computing Surveys (CSUR),
     v.11 n.2, pages 121-137, June 1979.
    <tr><td style="width:5ex" id="ref_knuth_btree">\[2\]<td>







|
>
>
>




>
>
>



>
>
>
>
>
>
>







2630
2631
2632
2633
2634
2635
2636
2637
2638
2639
2640
2641
2642
2643
2644
2645
2646
2647
2648
2649
2650
2651
2652
2653
2654
2655
2656
2657
2658
2659
2660
2661
2662
2663
2664
    with a database image that consists of 3 pages, also of page-size bytes
    each. The contents of the initial database image pages are A, B, C and
    D respectively. The final database image content is A, E and C. As 
    depicted, the file-system contains the initial database image, ABCD.
    However, if the journal file were to be somehow invalidated, then the 
    file-system would contain the final database image, AEC.

    [Figure filesystem2.gif figure_filesystem2 "Interim file-system state used to atomically overwrite database image ABCD with AEC"]

  <p class=todo>
    The exception for free-list leaves.


  [h3 "Multiple Database Transactions" multi_db_transactions]

  <p class=todo>
    Deleting the master-journal is used as the atomic operation.


[h1 "SQLite Interoperabilty Requirements" locking_protocol]

  <p class=todo>
    This section will describe the things that an SQLite compatible database
    client has to do in order to safely operate on a database at the same
    time as regular SQLite clients. Specifically, implementing the the 
    locking protocol and updating the change-counter in the database image
    header each time the database is modified.

[h1 References]

  <table id="refs" style="width:auto; margin: 1em 5ex">
    <tr><td style="width:5ex" id="ref_comer_btree">\[1\]<td>
     Douglas Comer, <u>Ubiquitous B-Tree</u>, ACM Computing Surveys (CSUR),
     v.11 n.2, pages 121-137, June 1979.
    <tr><td style="width:5ex" id="ref_knuth_btree">\[2\]<td>
Changes to req/hlr30000.txt.
902
903
904
905
906
907
908








































































909
<i>journal record</i> in the <i>journal file</i> shall be read from the
corresponding journal record.

HLR H35080
The contents of all <i>database image</i> pages for which there is no valid
<i>journal record</i> shall be read from the database file.

















































































>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>

902
903
904
905
906
907
908
909
910
911
912
913
914
915
916
917
918
919
920
921
922
923
924
925
926
927
928
929
930
931
932
933
934
935
936
937
938
939
940
941
942
943
944
945
946
947
948
949
950
951
952
953
954
955
956
957
958
959
960
961
962
963
964
965
966
967
968
969
970
971
972
973
974
975
976
977
978
979
980
981
<i>journal record</i> in the <i>journal file</i> shall be read from the
corresponding journal record.

HLR H35080
The contents of all <i>database image</i> pages for which there is no valid
<i>journal record</i> shall be read from the database file.


HLR H35090
A buffer of 28 bytes shall be considered a well-formed journal 
header if it is not excluded by requirements H35180, H35190 or H35200.

HLR H35180
A buffer of 28 bytes shall only be considered a well-formed journal
header if the first eight bytes of the buffer contain the values 0xd9, 
0xd5, 0x05, 0xf9, 0x20, 0xa1, 0x63, and 0xd7, respectively.

HLR H35190
A buffer of 28 bytes shall only be considered a well-formed journal
header if the value stored in the sector size field (the 4-byte big-endian 
unsigned integer at offset 20 of the buffer) contains a value that
is an integer power of two greater than 512.

HLR H35200
A buffer of 28 bytes shall only be considered a well-formed journal
header if the value stored in the page size field (the 4-byte big-endian 
unsigned integer at offset 24 of the buffer) contains a value that
is an integer power of two greater than 512.




HLR H35100
A buffer of (8 + page size) bytes shall be considered a well-formed journal 
record if it is not excluded by requirements H35110 or H35120.

HLR H35110
A journal record shall only be considered to be well-formed if the page number
field contains a value other than zero and the locking-page number, calculated
using the page size found in the first journal header of the journal file that
contains the journal record.

HLR H35120
A journal record shall only be considered to be well-formed if the checksum 
field contains a value equal to the sum of the value stored in the 
checksum-initializer field of the journal header that precedes the record
and the value stored in every 200th byte of the page data field, interpreted
as an 8-bit unsigned integer), starting with byte offset (page-size % 200) and
ending with the byte at byte offset (page-size - 200).

HLR H35130
A buffer shall be considered to contain a well-formed master journal pointer 
record if it is not excluded from this category by requirements H35140,
H35150, H35160 or H35170.

HLR H35140
A buffer shall only be considered to be a well-formed master journal pointer
if the final eight bytes of the buffer contain the values 0xd9, 0xd5, 0x05, 
0xf9, 0x20, 0xa1, 0x63, and 0xd7, respectively.

HLR H35150
A buffer shall only be considered to be a well-formed master journal pointer
if the size of the buffer in bytes is equal to the value stored as a 4-byte 
big-endian unsigned integer starting 16 bytes before the end of the buffer.

HLR H35160
A buffer shall only be considered to be a well-formed master journal pointer
if the first four bytes of the buffer, interpreted as a big-endian unsigned
integer, contain the page number of the locking page (the value
(1 + 2<sup>30</sup> / page-size), where page-size is the value stored in
the page-size field of the first journal header of the journal file).

HLR H35170
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).