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.
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 709d5967ce6611694c254cea70032f5e4509da70
User & Date: drh 2015-07-14 20:48:24.910
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
Unified Diff Ignore Whitespace Patch
Changes to pages/changes.in.
15
16
17
18
19
20
21
22
23

24
25
26
27
28
29
30
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







|
|
>







15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
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
Changes to pages/compile.in.
703
704
705
706
707
708
709




710
711
712
713
714
715
716
  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} {







>
>
>
>







703
704
705
706
707
708
709
710
711
712
713
714
715
716
717
718
719
720
  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} {
Changes to pages/ota.in.
1
2

3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
<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


>





<









|



|










|
|
|







1
2
3
4
5
6
7
8

9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
<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
59
60
61
62
63
64
65


66
67
68
69
70




71
72
73
74
75
76
77
78
79
80
81

82
83
84
85
86
87
88
89
    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:








>
>





>
>
>
>











>
|







59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
    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:

219
220
221
222
223
224
225



226
227
228

229
230
231
232
233













234
235
236

237

238
239
240
241
242
243


244
245
246
247
248
249
250

251
252
253
254
255

256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
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).







>
>
>
|
|
<
>



|

>
>
>
>
>
>
>
>
>
>
>
>
>

|
|
>
|
>


|
|
|
|
>
>


|
|


|
>
|




>
|

|
<
<
<
|
<
<
<
<
<
<
<
<
<
<
<
<
226
227
228
229
230
231
232
233
234
235
236
237

238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287



288












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