Documentation Source Text

proc chng {date desc {options {}}} {
  global nChng aChng
  set aChng($nChng) [list $date $desc $options]
  incr nChng
}

chng {2015-07-31 (3.8.11)} {
<li>Added the (still experimental) [FTS5] extension.
<li>Added the (still experimental) [OTA] extension.

<li>Added the [sqlite3_value_dup()] and [sqlite3_value_free()] interfaces.
<li>Enhance the [spellfix1] extension to support [ON CONFLICT] clauses.
<li>The [IS operator] is now able to drive indexes.
<li>Enhance the query planner to permit [automatic indexing] on FROM-clause
    subqueries that are implemented by co-routine.
<li>Disallow the use of "rowid" in [common table expressions].
<li>Added the [PRAGMA cell_size_check] command for better and earlier

proc chng {date desc {options {}}} {
  global nChng aChng
  set aChng($nChng) [list $date $desc $options]
  incr nChng
}

chng {2015-07-31 (3.8.11)} {
<li>Added the [OTA] extension.
<li>Added the experimental [FTS5] extension.  Note that this extension is experimental
    and so the file format may yet change.
<li>Added the [sqlite3_value_dup()] and [sqlite3_value_free()] interfaces.
<li>Enhance the [spellfix1] extension to support [ON CONFLICT] clauses.
<li>The [IS operator] is now able to drive indexes.
<li>Enhance the query planner to permit [automatic indexing] on FROM-clause
    subqueries that are implemented by co-routine.
<li>Disallow the use of "rowid" in [common table expressions].
<li>Added the [PRAGMA cell_size_check] command for better and earlier

  supply a large chunk of memory from which all memory allocations are
  taken.
  The MEMSYS5 module rounds all allocations up to the next power
  of two and uses a first-fit, buddy-allocator algorithm
  that provides strong guarantees against fragmentation and breakdown
  subject to certain operating constraints.
}





COMPILE_OPTION {SQLITE_ENABLE_RTREE} {
  This option causes SQLite to include support for the
  [rtree | R*Tree index extension].
}

COMPILE_OPTION {SQLITE_ENABLE_STMT_SCANSTATUS} {

  supply a large chunk of memory from which all memory allocations are
  taken.
  The MEMSYS5 module rounds all allocations up to the next power
  of two and uses a first-fit, buddy-allocator algorithm
  that provides strong guarantees against fragmentation and breakdown
  subject to certain operating constraints.
}

COMPILE_OPTION {SQLITE_ENABLE_OTA} {
  Enable the code the implements the [OTA extension].
}

COMPILE_OPTION {SQLITE_ENABLE_RTREE} {
  This option causes SQLite to include support for the
  [rtree | R*Tree index extension].
}

COMPILE_OPTION {SQLITE_ENABLE_STMT_SCANSTATUS} {

<title>The OTA Extension</title>
<tcl>

proc CODE {text} {
  hd_puts "<blockquote><pre>"
  hd_puts $text
  hd_puts "</pre></blockquote>"
}
hd_keywords {OTA}
</tcl>
<h1 align='center'>The OTA Extension</h1>

<p>The OTA extension is an add-on for SQLite that facilitates 
rapid bulk updates of large SQLite database files on low-power
devices at the edge of a network.

<p>The OTA name stands for "Over-the-Air" since its original use-case
was updating maps in low-power navigation devices via
wireless.  However, the name is overly specific, since the change set
can be sent to the edge device by any available channel.

<p>Updating an SQLite database file on a remote device can normally
be accomplished simply by send the text of various [INSERT], [DELETE],
and [UPDATE] commands to the device and evaluating them all inside of
a transaction.  OTA provides some advantages over this simple approach:

<ol>
<li><b>OTA runs faster</b>

<p>The most efficient way to apply changes to a B-Tree is to make
the changes in row order.  But if an SQL table has indexes, the row
order for the indexes will all be different from each other and from
the row order of the original table.  OTA works around this by applying
all changes to the table in one pass, then going back in and separately
applying changes to each index in separate passes, thus updating all
B-Trees in the optimal sequence.  For a large database file (one that
does not fit in the OS disk cache) this procedure can result in
two orders of magnitude faster updates.

<li><b>OTA runs in the background</b>

<p>The changes can be applied to the database file by a background
process that does not interfere with read access to the database
................................................................................
    operations only.  CREATE and DROP operations are not
    supported.</p></li>
<li><p>[INSERT] statements may not use default values.</p></li>
<li><p>[UPDATE] and [DELETE] statements must identify the target rows
    by rowid or by non-NULL PRIMARY KEY values.</p></li>
<li><p>[UPDATE] statements may not modify PRIMARY KEY or rowid values.
    </p></li>


<li><p>The OTA update will not fire any triggers.</p></li>
<li><p>The OTA update will not detect or prevent foreign key or
       CHECK constraint violations.</p></li>
<li><p>All OTA updates us the "OR ROLLBACK" constraint handling mechanism.
    </p></li>




</ul>


<h2>Preparing An OTA Update File</h2>

<p>All changes to be applied by OTA are stored in a separate SQLite database
called the "OTA database".  The database that is to be modifed is called
the "target database".

<p>
For each table in the target database, the OTA database should contain a table

named "data_<target name>" with the all the same columns as the
target table, plus one additional column named "ota_control".
The data_% table should have no PRIMARY KEY or UNIQUE constraints, but
each column should have the same type as the corresponding column in
the target database.
The ota_control column should have no type at all. For example, if
the target database contains:

................................................................................
table in "rowid" order is roughly the same as reading them sorted by
the PRIMARY KEY of the corresponding target database table. In other 
words, rows should be sorted using the destination table PRIMARY KEY 
fields before they are inserted into the data_xxx tables.

<h2>C/C++ Interface</h2>




<p>The API declared below allows an application to apply an OTA update 
stored on disk to an existing target database. Essentially, the 
application:


<ol>
<li><p>
Opens an OTA handle using the sqlite3ota_open() function.














<li><p>
Registers any required virtual table modules with the database
handle returned by sqlite3ota_db(). Also, if required, register

the ota_delta() implementation.


<li><p>
Calls the sqlite3ota_step() function one or more times on
the new handle. Each call to sqlite3ota_step() performs a single
b-tree operation, so thousands of calls may be required to apply 
a complete update.



<li><p>
Calls sqlite3ota_close() to close the OTA update handle. If

sqlite3ota_step() has been called enough times to completely
apply the update to the target database, then the OTA database
is marked as fully applied. Otherwise, the state of the OTA 
update application is saved in the OTA database for later 

resumption.
</ol>

<p>If an update is only partially applied to the target database by the
time sqlite3ota_close() is called, state information is saved 

within the OTA database. This allows subsequent processes to automatically
resume the OTA update from where it left off.

<p>To remove all OTA extension state information, returning an OTA database 
to its original contents, it is sufficient to drop all tables that begin
with the prefix "ota_"

<h2>Locking Constraints</h2>

<p>An OTA update may not be applied to a database in WAL mode. Attempting
to do so is an error (SQLITE_ERROR).

<p>While an OTA handle is open, a SHARED lock may be held on the target
database file. This means it is possible for other clients to read the
database, but not to write it.

<p>If an OTA update is started and then suspended before it is completed,
then an external client writes to the database, then attempting to resume
the suspended OTA update is also an error (SQLITE_BUSY).

<title>The OTA Extension</title>
<tcl>
hd_keywords {OTA} {OTA extension}
proc CODE {text} {
  hd_puts "<blockquote><pre>"
  hd_puts $text
  hd_puts "</pre></blockquote>"
}

</tcl>
<h1 align='center'>The OTA Extension</h1>

<p>The OTA extension is an add-on for SQLite that facilitates 
rapid bulk updates of large SQLite database files on low-power
devices at the edge of a network.

<p>The OTA name stands for "Over-the-Air" since its original use-case
was updating maps in low-power navigation devices via
wireless.  However, the name is overly specific, since the changes
can be sent to the edge device by any available channel.

<p>Updating an SQLite database file on a remote device can normally
be accomplished simply by sending the text of various [INSERT], [DELETE],
and [UPDATE] commands to the device and evaluating them all inside of
a transaction.  OTA provides some advantages over this simple approach:

<ol>
<li><b>OTA runs faster</b>

<p>The most efficient way to apply changes to a B-Tree is to make
the changes in row order.  But if an SQL table has indexes, the row
order for the indexes will all be different from each other and from
the row order of the original table.  OTA works around this by applying
all changes to the table in one pass, then
applying changes to each index in separate passes, thus updating each
B-Trees in its optimal sequence.  For a large database file (one that
does not fit in the OS disk cache) this procedure can result in
two orders of magnitude faster updates.

<li><b>OTA runs in the background</b>

<p>The changes can be applied to the database file by a background
process that does not interfere with read access to the database
................................................................................
    operations only.  CREATE and DROP operations are not
    supported.</p></li>
<li><p>[INSERT] statements may not use default values.</p></li>
<li><p>[UPDATE] and [DELETE] statements must identify the target rows
    by rowid or by non-NULL PRIMARY KEY values.</p></li>
<li><p>[UPDATE] statements may not modify PRIMARY KEY or rowid values.
    </p></li>
<li><p>OTA updates cannot be applied to any tables that contain a column
       named "ota_control".</p></li>
<li><p>The OTA update will not fire any triggers.</p></li>
<li><p>The OTA update will not detect or prevent foreign key or
       CHECK constraint violations.</p></li>
<li><p>All OTA updates us the "OR ROLLBACK" constraint handling mechanism.
    </p></li>
<li><p>The target database may not be in [WAL mode].</p></li>
<li><p>No other writes may occur on the target database while the
       OTA update is being applied.  A read-lock is held on the target
       database to prevent this.</p></li>
</ul>


<h2>Preparing An OTA Update File</h2>

<p>All changes to be applied by OTA are stored in a separate SQLite database
called the "OTA database".  The database that is to be modifed is called
the "target database".

<p>
For each table in the target database, the OTA database should contain a table
named "data_&lt;<i>target-table-name</i>&gt;" with 
the all the same columns as the
target table, plus one additional column named "ota_control".
The data_% table should have no PRIMARY KEY or UNIQUE constraints, but
each column should have the same type as the corresponding column in
the target database.
The ota_control column should have no type at all. For example, if
the target database contains:

................................................................................
table in "rowid" order is roughly the same as reading them sorted by
the PRIMARY KEY of the corresponding target database table. In other 
words, rows should be sorted using the destination table PRIMARY KEY 
fields before they are inserted into the data_xxx tables.

<h2>C/C++ Interface</h2>

<p>Enable the OTA extension by compiling the [amalgamation] with the
[SQLITE_ENABLE_OTA] compile-time option.

<p>The OTA extension interface allows an application to apply an OTA update 
stored in an OTA database to an existing target database.

The procedures is as follows:

<ol>
<li><p>
Open an OTA handle using the sqlite3ota_open(T,A,S) function.

<p>The T argument is the name of the target database file.
The A argument is the name of the OTA database file.
The S argument is the name of a "state database" used to store
state information needed to resume the update after an interruption.
The S argument can be NULL in which case the state information
is stored in the OTA database in various tables whose names all
begin with "ota_".

<p>The sqlite3ota_open(T,A,S) function returns a pointer to
an "sqlite3ota" object, which is then passed into the subsequent
interfaces.


<li><p>
Register any required virtual table modules with the database
handle returned by sqlite3ota_db(X) (where argument X is the sqlite3ota
pointer returned from sqlite3ota_open()).  Also, if required, register
the ota_delta() SQL function using 
[sqlite3_create_function_v2()].

<li><p>
Invoke the sqlite3ota_step(X) function one or more times on
the sqlite3ota object pointer X. Each call to sqlite3ota_step() 
performs a single b-tree operation, so thousands of calls may be 
required to apply a complete update.  The sqlite3ota_step() 
interface will return SQLITE_DONE when the update has been
completely applied.

<li><p>

Call sqlite3ota_close(X) to destroy the sqlite3ota object pointer.
If sqlite3ota_step(X) has been called enough times to completely
apply the update to the target database, then the OTA database
is marked as fully applied. Otherwise, the state of the OTA 
update application is saved in the state database (or in the OTA
database if the name of the state database file in sqlite3ota_open()
is NULL) for later resumption of the update.
</ol>

<p>If an update is only partially applied to the target database by the
time sqlite3ota_close() is called, state information is saved 
within the state database if it exists, or otherwise in the OTA database. 
This allows subsequent processes to automatically
resume the OTA update from where it left off.

If state information is stored in the OTA database, it can be removed
by dropping all tables whose names begin with "ota_".