Documentation Source Text

Check-in [2d2c9fd38a]
Login

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

Overview
Comment:Updates to the file format documentation.
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 2d2c9fd38a782ec4a093262cc150390397d6b88a
User & Date: drh 2010-07-05 15:06:41
Context
2010-07-06
01:27
Adding first draft of WAL file format. check-in: 05824b6e6a user: drh tags: trunk
2010-07-05
15:06
Updates to the file format documentation. check-in: 2d2c9fd38a user: drh tags: trunk
2010-06-29
16:05
Fix a typo in the date/time function documentation. check-in: cf14a52e82 user: drh tags: trunk
Changes
Hide Diffs Unified Diffs Show Whitespace Changes Patch

Changes to pages/docs.in.

219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
doc {VDBE Opcodes} {opcode.html} {
  This document is an automatically generated description of the various
  opcodes that the VDBE understands.  Programmers can use this document as
  a reference to better understand the output of EXPLAIN listings from
  SQLite.
}

doc {SQLite File Format} {fileformat.html} {
  A description of the format used for SQLite database and journal files, and
  other details required to create software to read and write SQLite 
  databases without using SQLite.
}

doc {File Format Redux} {fileformat2.html} {
  An alternative and independent description of the SQLite file format.  
  This document provides an independent the same information and is 
  compatible with the previous document, just in a different format.
}

doc {Compilation Options} {compile.html} {
  This document describes the compile time options that may be set to 
  modify the default behavior of the library or omit optional features
  in order to reduce binary size.
}







|





|
|
|
|







219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
doc {VDBE Opcodes} {opcode.html} {
  This document is an automatically generated description of the various
  opcodes that the VDBE understands.  Programmers can use this document as
  a reference to better understand the output of EXPLAIN listings from
  SQLite.
}

doc {SQLite File Format} {fileformat2.html} {
  A description of the format used for SQLite database and journal files, and
  other details required to create software to read and write SQLite 
  databases without using SQLite.
}

doc {SQLite File Format (first edition)} {fileformat.html} {
  An older and more verbose description of the SQLite file format.  
  This document provides the same information and is 
  compatible with the previous document.
}

doc {Compilation Options} {compile.html} {
  This document describes the compile time options that may be set to 
  modify the default behavior of the library or omit optional features
  in order to reduce binary size.
}

Changes to pages/fileformat.in.

1
2
3
4
5
6
7
8
9
10
11
..
27
28
29
30
31
32
33






34
35
36
37
38
39
40
...
630
631
632
633
634
635
636














637
638
639
640
641
642
643
...
714
715
716
717
718
719
720













721
722
723
724
725
726
727
728
729
730
731


732
733
734
735
736
737
738
739
<title>SQLite Database File Format</title>
<tcl>

hd_keywords {file format}
source [file join $::DOC pages fancyformat.tcl]
fancyformat_document "SQLite Database File Format" hlr30000.txt {

[h1 "Document Overview"]

  [h2 "Scope and Purpose"]

................................................................................

    <li><p> To facilitate the development of external (non-SQLite) software that may 
            operate directly on SQLite databases stored within a file-system. For
	    example a database space analysis utility or a competing database
	    client implementation.
  </ul>







  <p>
    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.

................................................................................
            is unlocked, another process may use SQLite to modify the 
            contents of the file, invalidating the internal cache of the
            first process. When the file is relocked, the first process can
            check if the value of the file change counter has been modified
            since the file was unlocked. If it has not, then the internal
            cache may be assumed to be valid and may be reused.
            <td>H33040















        [Tr]<td>32..35 <td>4<td>
            Page number of first freelist trunk page. 
            For more details, refer to section <cite>free_page_list</cite>.
            <td>H31320

        [Tr]<td>36..39 <td>4<td>
................................................................................
            so that it contains no free pages (section
            <cite>free_page_list</cite>) at the end of each database
            transaction. If incremental vacuum mode is enabled, then the
            reorganization is not performed until explicitly requested
            by the user.
            <td>H30171














      </table>

      <p>
        The four byte block beginning at offset 28 stores a big-endian integer
        which is the number of pages in the database.  Older versions of
        SQLite set this integer to zero.  For compatibility, SQLite database
        readers should be able to deal with either value.
      </p>

      <p>
        The 32 byte block beginning at offset 68 is currently unused.


        This might change in a future release of SQLite.
      </p>

      <p>
        The following requirements state that certain database header
        fields must contain defined constant values, even though the sqlite 
        database file format is designed to allow various values. These fields
        were intended to be flexible when the SQLite database image format



|







 







>
>
>
>
>
>







 







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







 







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










|
>
>
|







1
2
3
4
5
6
7
8
9
10
11
..
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
...
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
...
734
735
736
737
738
739
740
741
742
743
744
745
746
747
748
749
750
751
752
753
754
755
756
757
758
759
760
761
762
763
764
765
766
767
768
769
770
771
772
773
774
<title>SQLite Database File Format</title>
<tcl>

hd_keywords {first edition file format document}
source [file join $::DOC pages fancyformat.tcl]
fancyformat_document "SQLite Database File Format" hlr30000.txt {

[h1 "Document Overview"]

  [h2 "Scope and Purpose"]

................................................................................

    <li><p> To facilitate the development of external (non-SQLite) software that may 
            operate directly on SQLite databases stored within a file-system. For
	    example a database space analysis utility or a competing database
	    client implementation.
  </ul>

  <p>
    A shorter and more recent \[second edition file format document\]
    also available.  The two file format descriptions are independently
    written and hence serve as cross-checks of one another.  Any
    incompatibilities between the two documents should be considered a bug.

  <p>
    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.

................................................................................
            is unlocked, another process may use SQLite to modify the 
            contents of the file, invalidating the internal cache of the
            first process. When the file is relocked, the first process can
            check if the value of the file change counter has been modified
            since the file was unlocked. If it has not, then the internal
            cache may be assumed to be valid and may be reused.
            <td>H33040

        [Tr]<td>28..31 <td>4<td>
            <p style="margin-top:0">
            The in-header database size.  This field holds the logical size
            of the database file in pages.  This field is only valid if it
            is nonzero and if the file change counter at offset 24 exactly
            matches the version-valid-for at offset 92.  The in-header database
            size will only be valid when the database was last written by
            SQLite version 3.7.0 or later.  If the in-header database size
            is valid, then it is used as the logical size of the database.
            If the in-header database size is not valid, then the actual
            database file size is examined to determine the logical database
            size.
            <td>

        [Tr]<td>32..35 <td>4<td>
            Page number of first freelist trunk page. 
            For more details, refer to section <cite>free_page_list</cite>.
            <td>H31320

        [Tr]<td>36..39 <td>4<td>
................................................................................
            so that it contains no free pages (section
            <cite>free_page_list</cite>) at the end of each database
            transaction. If incremental vacuum mode is enabled, then the
            reorganization is not performed until explicitly requested
            by the user.
            <td>H30171

        [Tr]<td>92..95 <td>4<td>
            The version-valid-for integer.  This is a copy of the
            file change counter (offset 24) for when the SQLite version number
            in offset 96 was written.  This integer is also used to determine
            if the in-header database size (offset 28) is valid.
            <td>

        [Tr]<td>99..99 <td>4<td>
            The SQLite version number (obtained from \[SQLITE_VERSION_NUMBER\])
            for the instance of SQLite that last wrote to the database file.
            Only SQLite versions 3.7.0 and later will update this number.
            <td>

      </table>

      <p>
        The four byte block beginning at offset 28 stores a big-endian integer
        which is the number of pages in the database.  Older versions of
        SQLite set this integer to zero.  For compatibility, SQLite database
        readers should be able to deal with either value.
      </p>

      <p>
        The 32 byte block beginning at offset 68 is unused in SQLite versions
        up to and including 3.6.23.1.  The 8 bytes at offset 92 came into use
        beginning with SQLite version 3.7.0.  The 24 bytes between offset 68
        and offset 91 might come into use in a future release of SQLite.
      </p>

      <p>
        The following requirements state that certain database header
        fields must contain defined constant values, even though the sqlite 
        database file format is designed to allow various values. These fields
        were intended to be flexible when the SQLite database image format

Changes to pages/fileformat2.in.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
<title>File Format For SQLite Databases</title>
<tcl>hd_keywords {file format redux}</tcl>

<h1 align=center>
The SQLite Database File Format
</h1>

<p>This document describes and defines the on-disk database file
format used by SQLite.</p>

<p>This is the second document to define the SQLite database file format.
The [file format| first document] differs from this one in that it is
written in a formal style whereas this document attempts to be more
conversational.  The two documents were independently written (they have
different authors) and hence serve as cross-checks for one another.
Both documents should describe exactly the same file format.  
Any discrepencies between the two documents should be considered a bug.</p>

<h2>1.0 The Database File</h2>

<p>Most of the time the complete state of an SQLite database 
is contained a single file on disk called the "main database file".</p>

<p>While performing a transaction, SQLite stores some temporary information

|









|




|







1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
<title>File Format For SQLite Databases</title>
<tcl>hd_keywords {file format} {second edition file format document}</tcl>

<h1 align=center>
The SQLite Database File Format
</h1>

<p>This document describes and defines the on-disk database file
format used by SQLite.</p>

<p>This is the second document to define the SQLite database file format.
The [first edition file format document] differs from this one in that it is
written in a formal style whereas this document attempts to be more
conversational.  The two documents were independently written (they have
different authors) and hence serve as cross-checks for one another.
Both documents should describe exactly the same file format.  
Any incompatibilities between the two documents should be considered a bug.</p>

<h2>1.0 The Database File</h2>

<p>Most of the time the complete state of an SQLite database 
is contained a single file on disk called the "main database file".</p>

<p>While performing a transaction, SQLite stores some temporary information