Documentation Source Text

      ** connection pFile to the main database of connection pInMemory.
      ** If something goes wrong, pBackup will be set to NULL and an error
      ** code and  message left in connection pTo.
      **
      ** If the backup object is successfully created, call backup_step()
      ** to copy data from pFile to pInMemory. Then call backup_finish()
      ** to release resources associated with the pBackup object.  If an
      ** error occured, then  an error code and message will be left in
      ** connection pTo. If no error occured, then the error code belonging
      ** to pTo is set to SQLITE_OK.
      */
      pBackup = sqlite3_backup_init(pTo, "main", pFrom, "main");
      if( pBackup ){
        (void)sqlite3_backup_step(pBackup, -1);
        (void)sqlite3_backup_finish(pBackup);
      }

      ** connection pFile to the main database of connection pInMemory.
      ** If something goes wrong, pBackup will be set to NULL and an error
      ** code and  message left in connection pTo.
      **
      ** If the backup object is successfully created, call backup_step()
      ** to copy data from pFile to pInMemory. Then call backup_finish()
      ** to release resources associated with the pBackup object.  If an
      ** error occurred, then  an error code and message will be left in
      ** connection pTo. If no error occurred, then the error code belonging
      ** to pTo is set to SQLITE_OK.
      */
      pBackup = sqlite3_backup_init(pTo, "main", pFrom, "main");
      if( pBackup ){
        (void)sqlite3_backup_step(pBackup, -1);
        (void)sqlite3_backup_finish(pBackup);
      }

        The table B-Tree with root-page 1 used to store database records
        describing the database schema. Accessible as the "sqlite_master" 
        table from within SQLite.

      <tr><td>Schema version            <td>
        A 32-bit integer field stored at byte offset 40 of the database file
        header (see section <cite>database_header</cite>). Normally, SQLite
        increments this value each time it modifies the databas schema.

      <tr><td>Table B-Tree              <td>
        One of two variants on the B-Tree data structure used within SQLite
        database files. A table B-Tree (section <cite>table_btrees</cite>)
        uses 64 bit integers as key values and stores an associated database
        record along with each key value.

................................................................................
    the relationships between various database image substructures, invariants
    that are true for all well-formed database images.

  <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>
................................................................................
      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>
................................................................................
      </table>
      <p>
        When using the full 9 byte representation, the first byte contains
        the 7 most significant bits of the 64-bit value. The final byte of
        the 9 byte representation contains the 8 least significant bits of
        the 64-bit value. When using one of the other representations, the
        final byte contains the 7 least significant bits of the 64-bit value.
        The second last byte, if present, contains the 7 next least signficant
        bits of the value, and so on. The significant bits of the 64-bit
        value for which no storage is provided are assumed to be zero.
      <p>
        When encoding a variable length integer, SQLite usually selects the
        most compact representation that provides enough storage to accomadate
        the most significant set bit of the value. This is not required
        however, using more bytes than is strictly necessary when encoding
        an integer is valid.

      [Table]
        [Tr]<th>Decimal<th>Hexadecimal        <th>Variable Length Integer
        [Tr]<td>43     <td>0x000000000000002B <td>0x2B
................................................................................
          [fancyformat_import_requirement H31130]
          [fancyformat_import_requirement H31140]
        
      <p>
        Most of the requirements specified in section 
        <cite>index_btree_page_format</cite> also apply to table B-Tree 
        pages. The wording of the requirements make it clear when this is
        the case, either by refering to generic "B-Tree pages" or by
        explicitly stating that the statement applies to both "table and
        index B-Tree pages".

      [h4 "Table B-Tree Cell Format" table_btree_cell_format]
        <p>
          Cells stored on internal table B-Tree nodes consist of exactly two 
          fields. The associated child page number, stored as a 4-byte
................................................................................
    The following requirements dictate the way in which the data for each
    page of the database image can be located within the file-system
    by a database reader.

    [fancyformat_import_requirement H32070]
    [fancyformat_import_requirement H32080]

[h1 "SQLite Interoperabilty Requirements" interoperability_requirements]

  <p>
    This section contains requirements that further constrains the behaviour
    of software that accesses (reads and/or writes) SQLite databases stored
    within the file-system. These requirements need only be implemented by
    systems that access databases while other clients may also be doing so.
    More specifically, they need only be implemented by software operating
................................................................................
    processing. For example, SQLite itself runs in several different modes
    with various levels of guarantees on how failures are handled as follows:

  <ul>
    <li> <b>In-memory journal mode</b> (PRAGMA journal_mode=memory). In this
      mode any failure may cause database file-system corruption, including an
      application crash or unexpected exit.
    <li> <b>Non-synchronous mode</b> (PRAGMA sychronous=off). In this mode 
      an application crash or unexpected exit may not cause database 
      corruption, however an operating system crash or power failure may.
    <li> <b>Synchronous mode</b> (PRAGMA sychronous=full). In this mode 
      neither an application crash, operating system crash or power failure 
      may cause database file-system corruption.
  </ul>

  <p>
    If a process attempts to modify a database so as to replace database
    image A with database image B and a failure occurs while doing so, 
................................................................................
      valid, so the database image consists of the contents of the database
      file, database image B.
  <p>
    Figure <cite>figure_filesystem2</cite> depicts a possible interim state 
    of the database file-system representation used while committing a transaction
    that replaces a four page database image with a three page database image.
    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. The intermim state depicted
    is that reached at the end of step 4 in the above procedure. In this state, 
    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>
    The procedure described above can be onourous to implement, as it requires
    that the data for all modified pages of database image B be available
    (presumably in main memory) at the same time, when step 4 is performed.
    For transactions that write to a large number of database pages, this 
    may be undesirable. A solution is to create a journal-file containing
    two or more journal headers. If, while modifying a database image within
    main-memory, a client wishes to reduce the amount of data held in memory,
    it may perform steps 3 and 4 of the above procedure in order to write

        The table B-Tree with root-page 1 used to store database records
        describing the database schema. Accessible as the "sqlite_master" 
        table from within SQLite.

      <tr><td>Schema version            <td>
        A 32-bit integer field stored at byte offset 40 of the database file
        header (see section <cite>database_header</cite>). Normally, SQLite
        increments this value each time it modifies the database schema.

      <tr><td>Table B-Tree              <td>
        One of two variants on the B-Tree data structure used within SQLite
        database files. A table B-Tree (section <cite>table_btrees</cite>)
        uses 64 bit integers as key values and stores an associated database
        record along with each key value.

................................................................................
    the relationships between various database image substructures, invariants
    that are true for all well-formed database images.

  <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 inter-operate 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>
................................................................................
      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 referred 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>
................................................................................
      </table>
      <p>
        When using the full 9 byte representation, the first byte contains
        the 7 most significant bits of the 64-bit value. The final byte of
        the 9 byte representation contains the 8 least significant bits of
        the 64-bit value. When using one of the other representations, the
        final byte contains the 7 least significant bits of the 64-bit value.
        The second last byte, if present, contains the 7 next least significant
        bits of the value, and so on. The significant bits of the 64-bit
        value for which no storage is provided are assumed to be zero.
      <p>
        When encoding a variable length integer, SQLite usually selects the
        most compact representation that provides enough storage to accommodate
        the most significant set bit of the value. This is not required
        however, using more bytes than is strictly necessary when encoding
        an integer is valid.

      [Table]
        [Tr]<th>Decimal<th>Hexadecimal        <th>Variable Length Integer
        [Tr]<td>43     <td>0x000000000000002B <td>0x2B
................................................................................
          [fancyformat_import_requirement H31130]
          [fancyformat_import_requirement H31140]
        
      <p>
        Most of the requirements specified in section 
        <cite>index_btree_page_format</cite> also apply to table B-Tree 
        pages. The wording of the requirements make it clear when this is
        the case, either by referring to generic "B-Tree pages" or by
        explicitly stating that the statement applies to both "table and
        index B-Tree pages".

      [h4 "Table B-Tree Cell Format" table_btree_cell_format]
        <p>
          Cells stored on internal table B-Tree nodes consist of exactly two 
          fields. The associated child page number, stored as a 4-byte
................................................................................
    The following requirements dictate the way in which the data for each
    page of the database image can be located within the file-system
    by a database reader.

    [fancyformat_import_requirement H32070]
    [fancyformat_import_requirement H32080]

[h1 "SQLite Interoperability Requirements" interoperability_requirements]

  <p>
    This section contains requirements that further constrains the behaviour
    of software that accesses (reads and/or writes) SQLite databases stored
    within the file-system. These requirements need only be implemented by
    systems that access databases while other clients may also be doing so.
    More specifically, they need only be implemented by software operating
................................................................................
    processing. For example, SQLite itself runs in several different modes
    with various levels of guarantees on how failures are handled as follows:

  <ul>
    <li> <b>In-memory journal mode</b> (PRAGMA journal_mode=memory). In this
      mode any failure may cause database file-system corruption, including an
      application crash or unexpected exit.
    <li> <b>Non-synchronous mode</b> (PRAGMA synchronous=off). In this mode 
      an application crash or unexpected exit may not cause database 
      corruption, however an operating system crash or power failure may.
    <li> <b>Synchronous mode</b> (PRAGMA synchronous=full). In this mode 
      neither an application crash, operating system crash or power failure 
      may cause database file-system corruption.
  </ul>

  <p>
    If a process attempts to modify a database so as to replace database
    image A with database image B and a failure occurs while doing so, 
................................................................................
      valid, so the database image consists of the contents of the database
      file, database image B.
  <p>
    Figure <cite>figure_filesystem2</cite> depicts a possible interim state 
    of the database file-system representation used while committing a transaction
    that replaces a four page database image with a three page database image.
    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. The interim state depicted
    is that reached at the end of step 4 in the above procedure. In this state, 
    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>
    The procedure described above can be onerous to implement, as it requires
    that the data for all modified pages of database image B be available
    (presumably in main memory) at the same time, when step 4 is performed.
    For transactions that write to a large number of database pages, this 
    may be undesirable. A solution is to create a journal-file containing
    two or more journal headers. If, while modifying a database image within
    main-memory, a client wishes to reduce the amount of data held in memory,
    it may perform steps 3 and 4 of the above procedure in order to write

<p>There are numerous test cases that verify that SQLite is able to
deal with malformed database files.
These tests first build a well-formed database file, then add
corruption by changing one or more bytes in the file by some means
other than SQLite.  Then SQLite is used to read the database.
In some cases, the bytes changes are in the middle of data.
This causes the content of the databse to change while keeping the
database well-formed.
In other cases, unused bytes of the file are modified, which as
no effect on the integrity of the database.
The interesting cases are when bytes of the file that
define database structure get changed.  The malformed database tests
verify that SQLite finds the file format errors and reports them
using the SQLITE_CORRUPT return code without overflowing
................................................................................
should yield exactly the same result.  There is a run-time test using
the [sqlite3_test_control]([SQLITE_TESTCTRL_ALWAYS], ...) interface that
can be used to verify that the macros are correctly set to the first
form (the pass-through form) for deployment.</p>

<h3>7.3 Forcing coverage of boundary values and boolean vector tests</h3>

<p>Another macro used in conjuction with test coverage measurement is
the <tt>testcase()</tt> macro.  The argument is a condition for which
we want test cases that evaluate to both true and false.
In non-coverage builds (that is to so, in release builds) the testcase()
macro is a no-op:</p>

<blockquote><pre>
#define testcase(X)
................................................................................
<li> Each decision tries every possible outcome.
<li> Each condition in a decision takes on every possible outcome.
<li> Each entry and exit point is invoked.
<li> Each condition in a decision is shown to independently
     affect the outcome of the decision.
</ul>

<p>In the C programming langauge 
where the <tt>&amp;&amp;</tt> and <tt>||</tt> operator
are "short-circuit" operators, MC/DC and branch coverage are very nearly
the same thing.  The primary difference is in boolean vector tests.
One can test for any of several bits in bit-vector and still obtain
100% branch test coverage even though the second element of MC/DC - the
requirement that each condition in a decision take on every possible outcome -
might not be satisfied.</p>
................................................................................
in addition to 100% branch coverage.</p>

<h3>7.5 Experience with full test coverage</h3>

<p>The developers of SQLite have found that full coverage testing is an
extremely productive method for preventing the introduction of new bugs
as the system evolves.  Because every single branch
insteruction in SQLite core code is covered by test cases, the developers
can be confident that changes they make in one part of the code
do not have unintended consequences in other parts of the code.
It would extremely difficult maintain the quality of SQLite without such
assurances.</p>

<h2>8.0 Dynamic Analysis</h2>

<p>There are numerous test cases that verify that SQLite is able to
deal with malformed database files.
These tests first build a well-formed database file, then add
corruption by changing one or more bytes in the file by some means
other than SQLite.  Then SQLite is used to read the database.
In some cases, the bytes changes are in the middle of data.
This causes the content of the database to change while keeping the
database well-formed.
In other cases, unused bytes of the file are modified, which as
no effect on the integrity of the database.
The interesting cases are when bytes of the file that
define database structure get changed.  The malformed database tests
verify that SQLite finds the file format errors and reports them
using the SQLITE_CORRUPT return code without overflowing
................................................................................
should yield exactly the same result.  There is a run-time test using
the [sqlite3_test_control]([SQLITE_TESTCTRL_ALWAYS], ...) interface that
can be used to verify that the macros are correctly set to the first
form (the pass-through form) for deployment.</p>

<h3>7.3 Forcing coverage of boundary values and boolean vector tests</h3>

<p>Another macro used in conjunction with test coverage measurement is
the <tt>testcase()</tt> macro.  The argument is a condition for which
we want test cases that evaluate to both true and false.
In non-coverage builds (that is to so, in release builds) the testcase()
macro is a no-op:</p>

<blockquote><pre>
#define testcase(X)
................................................................................
<li> Each decision tries every possible outcome.
<li> Each condition in a decision takes on every possible outcome.
<li> Each entry and exit point is invoked.
<li> Each condition in a decision is shown to independently
     affect the outcome of the decision.
</ul>

<p>In the C programming language 
where the <tt>&amp;&amp;</tt> and <tt>||</tt> operator
are "short-circuit" operators, MC/DC and branch coverage are very nearly
the same thing.  The primary difference is in boolean vector tests.
One can test for any of several bits in bit-vector and still obtain
100% branch test coverage even though the second element of MC/DC - the
requirement that each condition in a decision take on every possible outcome -
might not be satisfied.</p>
................................................................................
in addition to 100% branch coverage.</p>

<h3>7.5 Experience with full test coverage</h3>

<p>The developers of SQLite have found that full coverage testing is an
extremely productive method for preventing the introduction of new bugs
as the system evolves.  Because every single branch
instruction in SQLite core code is covered by test cases, the developers
can be confident that changes they make in one part of the code
do not have unintended consequences in other parts of the code.
It would extremely difficult maintain the quality of SQLite without such
assurances.</p>

<h2>8.0 Dynamic Analysis</h2>

      wait_for_unlock_notify(), it is possible that the blocking connection
      that prevented the sqlite3_step() or sqlite3_prepare_v2() call from 
      succeeding has already finished its transaction. In this case, the 
      unlock-notify callback is invoked immediately, before 
      [sqlite3_unlock_notify()] returns. Or, it is possible that the 
      unlock-notify callback is invoked by a second thread after 
      [sqlite3_unlock_notify()] is called but before the thread starts waiting 
      to be asynchronously signalled. 

  <p> Exactly how such a potential race-condition is handled depends on the
      threads and synchronization primitives interface used by the application.
      This example uses pthreads, the interface provided by modern UNIX-like 
      systems, including Linux.

  <p> The pthreads interface provides the pthread_cond_wait() function. 
................................................................................
  <p> When the wait_for_unlock_notify() thread is ready to begin waiting for
      the unlock-notify callback to arrive, it:

    <ol>
      <li> Obtains the mutex.
      <li> Checks if the "fired" flag has been set. If so, the unlock-notify
           callback has already been invoked. Release the mutex and continue.
      <li> Atomicly releases the mutex and begins waiting for the
           asynchronous signal. When the signal arrives, continue.
    </ol>

  <p> This way, it doesn't matter if the unlock-notify callback has already
      been invoked, or is being invoked, when the wait_for_unlock_notify() 
      thread begins blocking.

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

  <p>
    Even though the [sqlite3_unlock_notify()] function only allows the caller
    to specify a single user-context pointer, an unlock-notify callback 
    is passed an array of such context pointers. This is because if when
    a blocking connection concludes its transaction, if there is more
    than one unlock-notify registered to call the same C function, the
    context-pointers are marshalled into an array and a single callback
    issued. If each thread were assigned a priority, then instead of just
    signalling the threads in arbitrary order as this implementation does,
    higher priority threads could be signalled before lower priority threads.

  <p>
    If a "DROP TABLE" or "DROP INDEX" SQL command is executed, and the
    associated database connection currently has one or more actively
    executing SELECT statements, then SQLITE_LOCKED is returned. If
    [sqlite3_unlock_notify()] is called in this case, then the specified
    callback will be invoked immediately. Unless the other running SELECT 

      wait_for_unlock_notify(), it is possible that the blocking connection
      that prevented the sqlite3_step() or sqlite3_prepare_v2() call from 
      succeeding has already finished its transaction. In this case, the 
      unlock-notify callback is invoked immediately, before 
      [sqlite3_unlock_notify()] returns. Or, it is possible that the 
      unlock-notify callback is invoked by a second thread after 
      [sqlite3_unlock_notify()] is called but before the thread starts waiting 
      to be asynchronously signaled. 

  <p> Exactly how such a potential race-condition is handled depends on the
      threads and synchronization primitives interface used by the application.
      This example uses pthreads, the interface provided by modern UNIX-like 
      systems, including Linux.

  <p> The pthreads interface provides the pthread_cond_wait() function. 
................................................................................
  <p> When the wait_for_unlock_notify() thread is ready to begin waiting for
      the unlock-notify callback to arrive, it:

    <ol>
      <li> Obtains the mutex.
      <li> Checks if the "fired" flag has been set. If so, the unlock-notify
           callback has already been invoked. Release the mutex and continue.
      <li> aAtomically releases the mutex and begins waiting for the
           asynchronous signal. When the signal arrives, continue.
    </ol>

  <p> This way, it doesn't matter if the unlock-notify callback has already
      been invoked, or is being invoked, when the wait_for_unlock_notify() 
      thread begins blocking.

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

  <p>
    Even though the [sqlite3_unlock_notify()] function only allows the caller
    to specify a single user-context pointer, an unlock-notify callback 
    is passed an array of such context pointers. This is because if when
    a blocking connection concludes its transaction, if there is more
    than one unlock-notify registered to call the same C function, the
    context-pointers are marshaled into an array and a single callback
    issued. If each thread were assigned a priority, then instead of just
    signaling the threads in arbitrary order as this implementation does,
    higher priority threads could be signaled before lower priority threads.

  <p>
    If a "DROP TABLE" or "DROP INDEX" SQL command is executed, and the
    associated database connection currently has one or more actively
    executing SELECT statements, then SQLITE_LOCKED is returned. If
    [sqlite3_unlock_notify()] is called in this case, then the specified
    callback will be invoked immediately. Unless the other running SELECT 

<p>With the default page size of 1024 bytes, an SQLite database is
limited in size to 2 tebibytes (2<sup><small>41</small></sup> bytes).
And even if it could handle larger databases, SQLite stores the entire
database in a single disk file and many filesystems limit the maximum
size of files to something less than this.  So if you are contemplating
databases of this magnitude, you would do well to consider using a
client/server database engine that spreads its content across multiple
disk files, and prehaps across multiple volumes.
</p>
</li>

<li><p><b>High Concurrency</b></p>

<p>
SQLite uses reader/writer locks on the entire database file.  That means

<p>With the default page size of 1024 bytes, an SQLite database is
limited in size to 2 tebibytes (2<sup><small>41</small></sup> bytes).
And even if it could handle larger databases, SQLite stores the entire
database in a single disk file and many filesystems limit the maximum
size of files to something less than this.  So if you are contemplating
databases of this magnitude, you would do well to consider using a
client/server database engine that spreads its content across multiple
disk files, and perhaps across multiple volumes.
</p>
</li>

<li><p><b>High Concurrency</b></p>

<p>
SQLite uses reader/writer locks on the entire database file.  That means