Documentation Source Text

  **     <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;
  }

  **     <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;
  }

      <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

      <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

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

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.