*** DRAFT ***

The OTA Extension

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.

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.

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:

  1. OTA runs faster

    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.

  2. OTA runs in the background

    The changes can be applied to the database file by a background process that does not interfere with read access to the database file.

  3. OTA runs incrementally

    The changes can be applied to the database incrementally, with intervening power outages and/or system resets. And yet the original unmodified data remains visible to the device until the moment that entire change set commits.

Limitations

The following limitations apply to OTA updates:

Preparing An OTA Update File

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

For each table in the target database, the OTA database should contain a table named "data_<target-table-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:

CREATE TABLE t1(a INTEGER PRIMARY KEY, b TEXT, c UNIQUE);

Then the OTA database should contain:

CREATE TABLE data_t1(a INTEGER, b TEXT, c, ota_control);

The order of the columns in the data_% table does not matter.

If the target database table is a virtual table or a table that has no PRIMARY KEY declaration, the data_% table must also contain a column named "ota_rowid". The ota_rowid column is mapped to the tables ROWID. For example, if the target database contains either of the following:

CREATE VIRTUAL TABLE x1 USING fts3(a, b);
CREATE TABLE x1(a, b);

then the OTA database should contain:

CREATE TABLE data_x1(a, b, ota_rowid, ota_control);

Virtual tables for which the "rowid" column does not function like a primary key value cannot be updated using OTA.

All non-hidden columns (i.e. all columns matched by "SELECT *") of the target table must be present in the input table. For virtual tables, hidden columns are optional - they are updated by OTA if present in the input table, or not otherwise. For example, to write to an fts4 table with a hidden languageid column such as:

CREATE VIRTUAL TABLE ft1 USING fts4(a, b, languageid='langid');

Either of the following input table schemas may be used:

CREATE TABLE data_ft1(a, b, langid, ota_rowid, ota_control);
CREATE TABLE data_ft1(a, b, ota_rowid, ota_control);

For each row to INSERT into the target database as part of the OTA update, the corresponding data_% table should contain a single record with the "ota_control" column set to contain integer value 0. The other columns should be set to the values that make up the new record to insert.

If the target database table has an INTEGER PRIMARY KEY, it is not possible to insert a NULL value into the IPK column. Attempting to do so results in an SQLITE_MISMATCH error.

For each row to DELETE from the target database as part of the OTA update, the corresponding data_% table should contain a single record with the "ota_control" column set to contain integer value 1. The real primary key values of the row to delete should be stored in the corresponding columns of the data_% table. The values stored in the other columns are not used.

For each row to UPDATE from the target database as part of the OTA update, the corresponding data_% table should contain a single record with the "ota_control" column set to contain a value of type text. The real primary key values identifying the row to update should be stored in the corresponding columns of the data_% table row, as should the new values of all columns being update. The text value in the "ota_control" column must contain the same number of characters as there are columns in the target database table, and must consist entirely of 'x' and '.' characters (or in some special cases 'd' - see below). For each column that is being updated, the corresponding character is set to 'x'. For those that remain as they are, the corresponding character of the ota_control value should be set to '.'. For example, given the tables above, the update statement:

UPDATE t1 SET c = 'usa' WHERE a = 4;

is represented by the data_t1 row created by:

INSERT INTO data_t1(a, b, c, ota_control) VALUES(4, NULL, 'usa', '..x');

Instead of an 'x' character, characters of the ota_control value specified for UPDATEs may also be set to 'd'. In this case, instead of updating the target table with the value stored in the corresponding data_% column, the user-defined SQL function "ota_delta()" is invoked and the result stored in the target table column. ota_delta() is invoked with two arguments - the original value currently stored in the target table column and the value specified in the data_xxx table.

For example, this row:

INSERT INTO data_t1(a, b, c, ota_control) VALUES(4, NULL, 'usa', '..d');

is similar to an UPDATE statement such as:

UPDATE t1 SET c = ota_delta(c, 'usa') WHERE a = 4;

If the target database table is a virtual table or a table with no PRIMARY KEY, the ota_control value should not include a character corresponding to the ota_rowid value. For example, this:

INSERT INTO data_ft1(a, b, ota_rowid, ota_control) 
  VALUES(NULL, 'usa', 12, '.x');

causes a result similar to:

UPDATE ft1 SET b = 'usa' WHERE rowid = 12;

The data_xxx tables themselves should have no PRIMARY KEY declarations. However, OTA is more efficient if reading the rows in from each data_xxx 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.

C/C++ Interface

Enable the OTA extension by compiling the amalgamation with the SQLITE_ENABLE_OTA compile-time option.

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:

  1. Open an OTA handle using the sqlite3ota_open(T,A,S) function.

    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_".

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

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

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

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

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_".

*** DRAFT ***