Documentation Source Text

Check-in [709d5967ce]
Login

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:Clarify and correct details of the OTA documentation. Make it clear that the FTS5 extension is still experimental and subject to file format changes.
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 709d5967ce6611694c254cea70032f5e4509da70
User & Date: drh 2015-07-14 20:48:24
Context
2015-07-16
00:01
Fix typos. check-in: 79a7fb1e3f user: drh tags: trunk
2015-07-14
20:48
Clarify and correct details of the OTA documentation. Make it clear that the FTS5 extension is still experimental and subject to file format changes. check-in: 709d5967ce user: drh tags: trunk
15:59
Call FTS5 and OTA "experimental" on the change log, as they might yet receive incompatible changes. check-in: 7b63ab1de7 user: drh tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to pages/changes.in.

    15     15   proc chng {date desc {options {}}} {
    16     16     global nChng aChng
    17     17     set aChng($nChng) [list $date $desc $options]
    18     18     incr nChng
    19     19   }
    20     20   
    21     21   chng {2015-07-31 (3.8.11)} {
    22         -<li>Added the (still experimental) [FTS5] extension.
    23         -<li>Added the (still experimental) [OTA] extension.
           22  +<li>Added the [OTA] extension.
           23  +<li>Added the experimental [FTS5] extension.  Note that this extension is experimental
           24  +    and so the file format may yet change.
    24     25   <li>Added the [sqlite3_value_dup()] and [sqlite3_value_free()] interfaces.
    25     26   <li>Enhance the [spellfix1] extension to support [ON CONFLICT] clauses.
    26     27   <li>The [IS operator] is now able to drive indexes.
    27     28   <li>Enhance the query planner to permit [automatic indexing] on FROM-clause
    28     29       subqueries that are implemented by co-routine.
    29     30   <li>Disallow the use of "rowid" in [common table expressions].
    30     31   <li>Added the [PRAGMA cell_size_check] command for better and earlier

Changes to pages/compile.in.

   703    703     supply a large chunk of memory from which all memory allocations are
   704    704     taken.
   705    705     The MEMSYS5 module rounds all allocations up to the next power
   706    706     of two and uses a first-fit, buddy-allocator algorithm
   707    707     that provides strong guarantees against fragmentation and breakdown
   708    708     subject to certain operating constraints.
   709    709   }
          710  +
          711  +COMPILE_OPTION {SQLITE_ENABLE_OTA} {
          712  +  Enable the code the implements the [OTA extension].
          713  +}
   710    714   
   711    715   COMPILE_OPTION {SQLITE_ENABLE_RTREE} {
   712    716     This option causes SQLite to include support for the
   713    717     [rtree | R*Tree index extension].
   714    718   }
   715    719   
   716    720   COMPILE_OPTION {SQLITE_ENABLE_STMT_SCANSTATUS} {

Changes to pages/ota.in.

     1      1   <title>The OTA Extension</title>
     2      2   <tcl>
            3  +hd_keywords {OTA} {OTA extension}
     3      4   proc CODE {text} {
     4      5     hd_puts "<blockquote><pre>"
     5      6     hd_puts $text
     6      7     hd_puts "</pre></blockquote>"
     7      8   }
     8         -hd_keywords {OTA}
     9      9   </tcl>
    10     10   <h1 align='center'>The OTA Extension</h1>
    11     11   
    12     12   <p>The OTA extension is an add-on for SQLite that facilitates 
    13     13   rapid bulk updates of large SQLite database files on low-power
    14     14   devices at the edge of a network.
    15     15   
    16     16   <p>The OTA name stands for "Over-the-Air" since its original use-case
    17     17   was updating maps in low-power navigation devices via
    18         -wireless.  However, the name is overly specific, since the change set
           18  +wireless.  However, the name is overly specific, since the changes
    19     19   can be sent to the edge device by any available channel.
    20     20   
    21     21   <p>Updating an SQLite database file on a remote device can normally
    22         -be accomplished simply by send the text of various [INSERT], [DELETE],
           22  +be accomplished simply by sending the text of various [INSERT], [DELETE],
    23     23   and [UPDATE] commands to the device and evaluating them all inside of
    24     24   a transaction.  OTA provides some advantages over this simple approach:
    25     25   
    26     26   <ol>
    27     27   <li><b>OTA runs faster</b>
    28     28   
    29     29   <p>The most efficient way to apply changes to a B-Tree is to make
    30     30   the changes in row order.  But if an SQL table has indexes, the row
    31     31   order for the indexes will all be different from each other and from
    32     32   the row order of the original table.  OTA works around this by applying
    33         -all changes to the table in one pass, then going back in and separately
    34         -applying changes to each index in separate passes, thus updating all
    35         -B-Trees in the optimal sequence.  For a large database file (one that
           33  +all changes to the table in one pass, then
           34  +applying changes to each index in separate passes, thus updating each
           35  +B-Trees in its optimal sequence.  For a large database file (one that
    36     36   does not fit in the OS disk cache) this procedure can result in
    37     37   two orders of magnitude faster updates.
    38     38   
    39     39   <li><b>OTA runs in the background</b>
    40     40   
    41     41   <p>The changes can be applied to the database file by a background
    42     42   process that does not interfere with read access to the database
................................................................................
    59     59       operations only.  CREATE and DROP operations are not
    60     60       supported.</p></li>
    61     61   <li><p>[INSERT] statements may not use default values.</p></li>
    62     62   <li><p>[UPDATE] and [DELETE] statements must identify the target rows
    63     63       by rowid or by non-NULL PRIMARY KEY values.</p></li>
    64     64   <li><p>[UPDATE] statements may not modify PRIMARY KEY or rowid values.
    65     65       </p></li>
           66  +<li><p>OTA updates cannot be applied to any tables that contain a column
           67  +       named "ota_control".</p></li>
    66     68   <li><p>The OTA update will not fire any triggers.</p></li>
    67     69   <li><p>The OTA update will not detect or prevent foreign key or
    68     70          CHECK constraint violations.</p></li>
    69     71   <li><p>All OTA updates us the "OR ROLLBACK" constraint handling mechanism.
    70     72       </p></li>
           73  +<li><p>The target database may not be in [WAL mode].</p></li>
           74  +<li><p>No other writes may occur on the target database while the
           75  +       OTA update is being applied.  A read-lock is held on the target
           76  +       database to prevent this.</p></li>
    71     77   </ul>
    72     78   
    73     79   
    74     80   <h2>Preparing An OTA Update File</h2>
    75     81   
    76     82   <p>All changes to be applied by OTA are stored in a separate SQLite database
    77     83   called the "OTA database".  The database that is to be modifed is called
    78     84   the "target database".
    79     85   
    80     86   <p>
    81     87   For each table in the target database, the OTA database should contain a table
    82         -named "data_<target name>" with the all the same columns as the
           88  +named "data_&lt;<i>target-table-name</i>&gt;" with 
           89  +the all the same columns as the
    83     90   target table, plus one additional column named "ota_control".
    84     91   The data_% table should have no PRIMARY KEY or UNIQUE constraints, but
    85     92   each column should have the same type as the corresponding column in
    86     93   the target database.
    87     94   The ota_control column should have no type at all. For example, if
    88     95   the target database contains:
    89     96   
................................................................................
   219    226   table in "rowid" order is roughly the same as reading them sorted by
   220    227   the PRIMARY KEY of the corresponding target database table. In other 
   221    228   words, rows should be sorted using the destination table PRIMARY KEY 
   222    229   fields before they are inserted into the data_xxx tables.
   223    230   
   224    231   <h2>C/C++ Interface</h2>
   225    232   
   226         -<p>The API declared below allows an application to apply an OTA update 
   227         -stored on disk to an existing target database. Essentially, the 
   228         -application:
          233  +<p>Enable the OTA extension by compiling the [amalgamation] with the
          234  +[SQLITE_ENABLE_OTA] compile-time option.
          235  +
          236  +<p>The OTA extension interface allows an application to apply an OTA update 
          237  +stored in an OTA database to an existing target database.
          238  +The procedures is as follows:
   229    239   
   230    240   <ol>
   231    241   <li><p>
   232         -Opens an OTA handle using the sqlite3ota_open() function.
          242  +Open an OTA handle using the sqlite3ota_open(T,A,S) function.
          243  +
          244  +<p>The T argument is the name of the target database file.
          245  +The A argument is the name of the OTA database file.
          246  +The S argument is the name of a "state database" used to store
          247  +state information needed to resume the update after an interruption.
          248  +The S argument can be NULL in which case the state information
          249  +is stored in the OTA database in various tables whose names all
          250  +begin with "ota_".
          251  +
          252  +<p>The sqlite3ota_open(T,A,S) function returns a pointer to
          253  +an "sqlite3ota" object, which is then passed into the subsequent
          254  +interfaces.
          255  +
   233    256   
   234    257   <li><p>
   235         -Registers any required virtual table modules with the database
   236         -handle returned by sqlite3ota_db(). Also, if required, register
   237         -the ota_delta() implementation.
          258  +Register any required virtual table modules with the database
          259  +handle returned by sqlite3ota_db(X) (where argument X is the sqlite3ota
          260  +pointer returned from sqlite3ota_open()).  Also, if required, register
          261  +the ota_delta() SQL function using 
          262  +[sqlite3_create_function_v2()].
   238    263   
   239    264   <li><p>
   240         -Calls the sqlite3ota_step() function one or more times on
   241         -the new handle. Each call to sqlite3ota_step() performs a single
   242         -b-tree operation, so thousands of calls may be required to apply 
   243         -a complete update.
          265  +Invoke the sqlite3ota_step(X) function one or more times on
          266  +the sqlite3ota object pointer X. Each call to sqlite3ota_step() 
          267  +performs a single b-tree operation, so thousands of calls may be 
          268  +required to apply a complete update.  The sqlite3ota_step() 
          269  +interface will return SQLITE_DONE when the update has been
          270  +completely applied.
   244    271   
   245    272   <li><p>
   246         -Calls sqlite3ota_close() to close the OTA update handle. If
   247         -sqlite3ota_step() has been called enough times to completely
          273  +Call sqlite3ota_close(X) to destroy the sqlite3ota object pointer.
          274  +If sqlite3ota_step(X) has been called enough times to completely
   248    275   apply the update to the target database, then the OTA database
   249    276   is marked as fully applied. Otherwise, the state of the OTA 
   250         -update application is saved in the OTA database for later 
   251         -resumption.
          277  +update application is saved in the state database (or in the OTA
          278  +database if the name of the state database file in sqlite3ota_open()
          279  +is NULL) for later resumption of the update.
   252    280   </ol>
   253    281   
   254    282   <p>If an update is only partially applied to the target database by the
   255    283   time sqlite3ota_close() is called, state information is saved 
   256         -within the OTA database. This allows subsequent processes to automatically
          284  +within the state database if it exists, or otherwise in the OTA database. 
          285  +This allows subsequent processes to automatically
   257    286   resume the OTA update from where it left off.
   258         -
   259         -<p>To remove all OTA extension state information, returning an OTA database 
   260         -to its original contents, it is sufficient to drop all tables that begin
   261         -with the prefix "ota_"
   262         -
   263         -<h2>Locking Constraints</h2>
   264         -
   265         -<p>An OTA update may not be applied to a database in WAL mode. Attempting
   266         -to do so is an error (SQLITE_ERROR).
   267         -
   268         -<p>While an OTA handle is open, a SHARED lock may be held on the target
   269         -database file. This means it is possible for other clients to read the
   270         -database, but not to write it.
   271         -
   272         -<p>If an OTA update is started and then suspended before it is completed,
   273         -then an external client writes to the database, then attempting to resume
   274         -the suspended OTA update is also an error (SQLITE_BUSY).
          287  +If state information is stored in the OTA database, it can be removed
          288  +by dropping all tables whose names begin with "ota_".