Documentation Source Text

-->
  

[h1 "Database Image Format Details" database_file_format]

  <p>
    This section describes the various fields and sub-structures that make up
    the format used by SQLite to serialize a logical SQL database. A serialized
    logical database is referred to as a database image. Section
    <cite>file_system_usage</cite> describes the way a database image is stored
    in the file-system. Most of the time a database image is stored in a single
    file, the database file. So while reading this section, the term database 
    image may be understood to mean "contents of the database file". However,
    it is important to remember that there are exceptions to this.

  <p>
    This section does not contain requirements governing the behaviour of any
    software system. Instead, along with the plain language description of the
    file format are a series of succinct, testable statements describing the
    properties of "well-formed SQLite database files".  Some of these
    statements describe the contents of the database file in terms of the
    contents of the logical SQL database that it is a serialization of. e.g.
    "For each SQL table in the database, the database file shall...". The 
    contents of a logical database consist of:

  <ul>
    <li>The database schema: The set of database tables, virtual tables, 
        indexes, triggers and views stored in the database.

    <li>The database contents: The set of tuples (rows) stored in
	each database table.

    <li>Other database properties, as follows:
      <ul>
	<li>The page-size of the database.
	<li>The text-encoding of the database.
	<li>A flag indicating whether or not the database is an auto-vacuum 
	    database.
	<li>The value of the database user-cookie.
	<li>If the database is an auto-vacuum database, a flag indicating 
	    whether or not the database is in incremental vacuum mode or not.
	<li>The default page cache size in pages to use with the database (an 
	    integer field).
      </ul>
  </ul>

  <p class=todo>
    The concept of a logical database and its contents should be defined
    properly in some requirements document so that it can be referenced from
    here and other places. The definition will be something like the list of
    bullet points above.

  <p>
    A well-formed SQLite database image is defined as an image for which
    all of the statements itemized as requirements within this section
    are true. <span class=todo>mention the requirements numbering scheme
    here.</span> A software system that wishes to interoperate with other
    systems using the SQLite database image format should only ever
    output well-formed SQLite databases. In the case of SQLite itself,
    the system should ensure that the database file contains a well-formed 
    database image the conclusion of each transaction.

  [h2 "Image Format Overview" "fileformat_overview"]
    <p>
      A B-Tree is a data structure designed for offline storage of a set of
      unique key values. It is structured so as to support fast querying 
      for a single key or range of keys. As implemented in SQLite, each
      entry may be associated with a blob of data that is not part of the
      key. For the canonical introduction to the B-Tree and its variants, 
      refer to reference <cite>ref_comer_btree</cite>. The B-Tree 
      implementation in SQLite also adopts some of the enhancements 
      suggested in <cite>ref_knuth_btree</cite>.
    <p>
      An SQLite database file contains one or more B-Tree structures. Each
      B-Tree structure stores the data for a single database table or 
      index. Hence each database file contains a single B-Tree to store
      the contents of the <i>sqlite_master</i> table, and one B-Tree
      for each database table or index created by the user. If the database
      uses auto-increment integer primary keys, then the database file
      also contains a B-Tree to store the contents of the automatically 
      created <i>sqlite_sequence</i> table.
    <p>
      SQLite uses two distinct variants of the B-Tree structure. One variant,
      hereafter refered to as a "table B-Tree" uses signed 64-bit integer
      values as keys. Each entry has an associated variable length blob of 
      data used to store a database record (see section
      <cite>record_format</cite>). Each SQLite database file contains one 
      table B-Tree for the schema table and one table B-Tree for each
      additional database table created by the user. If it is present, the
      sqlite_sequence table is also stored as a table B-Tree.
    <p>
      A database record is a blob of data containing an ordered list of
      SQL values (integers, real numbers, NULL values, blobs or strings).
      For each row in each table in the logical database, there is an 
      entry in the corresponding table B-Tree structure in the database 
      image. The entry's integer key value is same as the SQL "rowid" or 
      "integer primary key" field of the table row. The associated database 
      record is made up of the row's column values, in declaration (CREATE 
      TABLE) order.
    <p>
      The other B-Tree variant used by SQLite, hereafter an "index B-Tree"
      uses database records (section <cite>record_format</cite>) as keys.
      For this kind of B-Tree, there is no additional data associated with
      each entry. SQLite databases contain an index B-Tree for each database
      index created by the user. Database indexes may be created by CREATE
      INDEX statements, or by UNIQUE or PRIMARY KEY (but not INTEGER PRIMARY
      KEY) clauses added to CREATE TABLE statements. 
    <p>
      Index B-Tree structures contain one entry for each row in the 
      associated table in the logical SQL database. The database record used 
      as the key consists of the row's value for each of the indexed columns in
      declaration (CREATE INDEX) order, followed by the row's "rowid" or
      "integer primary key" column value.
    <p>
      For example, the following SQL script:
    <pre>
      CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c, d);
      CREATE INDEX i1 ON t1(d, c);

      INSERT INTO t1 VALUES(1, 'triangle', 3, 180, 'green');
      INSERT INTO t1 VALUES(2, 'square',   4, 360, 'gold');
      INSERT INTO t1 VALUES(3, 'pentagon', 5, 540, 'grey');
      ...</pre>
    <p>
      Creates a database image containing three B-Tree structures: one table
      B-Tree to store the <i>sqlite_master</i> table, one table B-Tree to 
      store table "t1", and one index B-Tree to store index "i1". The
      B-Tree structures created for the user table and index are populated
      as shown in figure <cite>figure_examplepop</cite>.

      [Figure examplepop.gif figure_examplepop "Example B-Tree Data"]


    <p>
      The following sections and sub-sections describe precisely the format
      used to serialize the B-Tree structures within an SQLite database image.

  [h2 "Global Structure"]

    [h3 "File Header" "file_header"]
      <p>
        Each SQLite database file begins with a 100-byte header. The header
        file consists of a well known 16-byte sequence followed by a series of
        1, 2 and 4 byte unsigned integers. All integers in the file header (as
        well as the rest of the database file) are stored in big-endian format.

    that can be used. So long as the above requirements (and
    those in sections <cite>locking_protocol</cite> and 
    <cite>database_header_cookies_protocol</cite>) are honoured, any method may
    be used by an SQLite database writer to update the database file-system
    representation. Sections <cite>rollback_journal_method</cite> and 
    <cite>atomic_write_method</cite> do not contain formal requirements. Formal
    requirements governing the way in which SQLite safely updates database
    file-system representations may be found in <span class=todo>Not available yet!</span>.
    An informal description is available in <cite>atomic_commit_page</cite>.


  [h3 "The Rollback-Journal Method" rollback_journal_method]

    <p>
      This section describes the method usually used by SQLite to update a database 
      image within a database file-system representation. This is one way
      to modify a database image in accordance with the requirements in the

    [fileformat_import_requirement2 H33040]
    [fileformat_import_requirement2 H33050]
    [fileformat_import_requirement2 H33070]


[h1 References]

[proc Ref {no id details} {
  set ::References($id) "\[$no\]"
  return "<tr><td style=\"width:5ex ; vertical-align:top\" id=\"$id\">\[$no\]<td>$details"
}]

  <table id="refs" style="width:auto; margin: 1em 5ex">
  [Ref 1 ref_comer_btree {
     Douglas Comer, <u>Ubiquitous B-Tree</u>, ACM Computing Surveys (CSUR),
     v.11 n.2, pages 121-137, June 1979.
  }]
  [Ref 2 ref_knuth_btree {
     Donald E. Knuth, <u>The Art Of Computer Programming, Volume 3:
     "Sorting And Searching"</u>, pages 473-480. Addison-Wesley
     Publishing Company, Reading, Massachusetts.

  }]
  [Ref 3 atomic_commit_page {

    SQLite Online Documentation,<u>How SQLite Implements Atomic Commit</u>,


    <a href="http://www.sqlite.org/atomiccommit.html">http://www.sqlite.org/atomiccommit.html</a>.
  }]
  </table>

}]
</tcl>

<div id=toc>
<tcl>hd_puts $TOC</tcl>
</div id=toc>
<tcl>hd_puts [FixReferences $body]</tcl>