Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.
|Comment:||Add new file ext/ota/README.txt, containing notes regarding the implementation of the ota extension.|
|Downloads:||Tarball | ZIP archive | SQL archive|
|Timelines:||family | ancestors | descendants | both | ota-update|
|Files:||files | file ages | folders|
|User & Date:||dan 2014-09-18 15:22:48|
|15:57||Remove some c++isms from sqlite3ota.c. (check-in: 0da1862b user: dan tags: ota-update)|
|15:22||Add new file ext/ota/README.txt, containing notes regarding the implementation of the ota extension. (check-in: 3c6e1cbb user: dan tags: ota-update)|
|14:48||Add an API to query an ota handle for the total number of key/value operations performed so far. (check-in: e3943fa7 user: dan tags: ota-update)|
1 + 2 +This file contains notes regarding the implementation of the OTA extension. 3 +User documentation is in sqlite3ota.h. 4 + 5 +SQLite Hacks 6 +------------ 7 + 8 +1) PRAGMA ota_mode: 9 + 10 + This is a new flag pragma. If the flag is set: 11 + 12 + * INSERT/DELETE/UPDATE commands are prevented from updating any but the main 13 + b-tree for each table (the PK index for WITHOUT ROWID tables or the 14 + rowid b-tree for others). 15 + 16 + * The above statements do not check UNIQUE constraints - except those enforced 17 + by the main b-tree. 18 + 19 + * All non-temporary triggers are disabled. 20 + 21 + 22 +2) PRAGMA pager_ota_mode: 23 + 24 + This pragma sets a flag on the pager associated with the main database only. In 25 + a zipvfs system, this pragma is intercepted by zipvfs and the flag is set on 26 + the lower level pager only. 27 + 28 + The flag can only be set when there is no open transaction and the pager does 29 + not already have an open WAL file. Attempting to do so is an error. 30 + 31 + Once the flag has been set, it is not possible to open a regular WAL file. 32 + If, when the next read-transaction is opened, a *-wal file is found or the 33 + database header flags indicate that it is a wal-mode database, 34 + SQLITE_CANTOPEN is returned. 35 + 36 + Otherwise, if no WAL file or flags are found, the pager opens the *-oal file 37 + and uses it as a write-ahead-log with the *-shm data stored in heap-memory. 38 + 39 + The 8-bytes of "salt" at teh start of an *-oal file is a copy of the 8 bytes 40 + starting at offset 24 of the database file header (the change counter and the 41 + number of pages in the file). If the *-oal file already exists when it is 42 + opened, SQLite checks that the salt still matches the database header fields. 43 + If not, it concludes that the database file has been written by a rollback-mode 44 + client since the *-oal wa created and an SQLITE_BUSY_SNAPSHOT error is 45 + returned. No read-transaction can be opened in this case. 46 + 47 + A pager with the pager_ota_mode flag set never runs a checkpoint. 48 + 49 + Other clients see a rollback-mode database on which the pager_ota_mode client 50 + is holding a SHARED lock. There are no locks to arbitrate between multiple 51 + pager_ota_mode connections. If two or more such connections attempt to write 52 + simultaneously, the results are undefined. 53 + 54 + 55 +3) sqlite3_index_writer() 56 + 57 + This new API function is used to create VMs that can insert or delete entries 58 + from individual index b-trees within the database. The VMs apply affinities 59 + and check that UNIQUE constraints are not violated before updating index 60 + b-trees. 61 + 62 + 63 +The OTA extension 64 +----------------- 65 + 66 +The OTA extension requires that the OTA update be packaged as an SQLite 67 +database. The tables it expects to find are described in sqlite3ota.h. 68 +Essentially, for each table xyz in the target database that the user wishes 69 +to write to, a corresponding data_xyz table is created in the OTA database 70 +and populated with one row for each row to update, insert or delete from 71 +the target table. 72 + 73 +The OTA extension opens the target and OTA update databases using a single 74 +database handle (the target database is "main", and the OTA update database is 75 +attached as "ota"). It executes both the "pager_ota_mode" and "ota_mode" 76 +pragmas described above. For each data_xyz table in then: 77 + 78 + * CREATEs an ota_xyz table in the OTA update database. 79 + 80 + * Loops through the data_xyz table, running the INSERT, UPDATE or DELETE 81 + command on the corresponding target database table. Only the main b-tree 82 + is updated by these statements. Modified pages are appended to the *-oal 83 + file. 84 + 85 + Temporary triggers installed on the target database catch the old.* 86 + values associated with any UPDATEd or DELETEd rows and store them in 87 + the ota_xyz table (in the OTA update database). 88 + 89 + * For each index on the data_xyz table in the target database: 90 + 91 + Loop through a union of the data_xyz and ota_xyz tables in the order 92 + specified by the data_xyz index. In other words, if the index is on 93 + columns (a, b), read rows from the OTA update database using: 94 + 95 + SELECT * FROM data_xyz UNION ALL ota_xyz ORDER BY a, b; 96 + 97 + For each row visited, use an sqlite3_index_writer() VM to update the index 98 + in the target database. 99 + 100 + * DROPs the ota_xyz table. 101 + 102 +At any point in the above, the process may be suspended by the user. In this 103 +case the "ota_state" table is created in the OTA database, containing a single 104 +row indicating the current table/index being processed and the number of updates 105 +already performed on it, and the transaction on the target database is committed 106 +to the *-oal file. The next OTA client will use the contents of the ota_state 107 +table to continue the update from where this one left off. 108 + 109 +Alternatively, if the OTA update is completely applied, the transaction is 110 +committed to the *-oal file and the database connection closed. sqlite3ota.c 111 +then uses a rename() call to move the *-oal file to the corresponding *-wal 112 +path. At that point it is finished - it does not take responsibility for 113 +checkpointing the *-wal file. 114 + 115 + 116 +Problems 117 +-------- 118 + 119 +The rename() call might not be portable. And in theory it is unsafe if some 120 +other client starts writing the db file. 121 + 122 +When state is saved, the commit to the *-oal file and the commit to the OTA 123 +update database are not atomic. So if the power fails at the wrong moment they 124 +might get out of sync. As the main database will be committed before the OTA 125 +update database this will likely either just pass unnoticed, or result in 126 +SQLITE_CONSTRAINT errors (due to UNIQUE constraint violations). 127 + 128 +If some client does modify the target database mid OTA update, or some other 129 +error occurs, the OTA extension will keep throwing errors. It's not really 130 +clear how to get out of this state. The system could just by delete the OTA 131 +update database and *-oal file and have the device download the update again 132 +and start over. 133 + 134 +At present, for an UPDATE, both the new.* and old.* records are collected in 135 +the ota_xyz table. And for both UPDATEs and DELETEs all fields are collected. 136 +This means we're probably writing a lot more data to disk when saving the 137 +state of an ongoing update to the OTA update database than is strictly 138 +necessary. 139 + 140 + 141 + 142 +