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: |
709d5967ce6611694c254cea70032f5e |
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
Changes to pages/changes.in.
︙ | ︙ | |||
15 16 17 18 19 20 21 | proc chng {date desc {options {}}} { global nChng aChng set aChng($nChng) [list $date $desc $options] incr nChng } chng {2015-07-31 (3.8.11)} { | | | > | 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 | <title>The OTA Extension</title> <tcl> proc CODE {text} { hd_puts "<blockquote><pre>" hd_puts $text hd_puts "</pre></blockquote>" } | > < | | | | | | 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 | 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 | > > > > > > > | | 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_<<i>target-table-name</i>>" 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 | 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> | > > > | | < > | > > > > > > > > > > > > > | | > | > | | | | > > | | | > | > | | < < < | < < < < < < < < < < < < | 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_". |