/ Check-in [3c6e1cbb]

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
SHA1: 3c6e1cbb4baaebc5958ab7276f27ff0ba14f4fa2
User & Date: dan 2014-09-18 15:22:48
Remove some c++isms from sqlite3ota.c. (check-in: 0da1862b user: dan tags: ota-update)
Add new file ext/ota/README.txt, containing notes regarding the implementation of the ota extension. (check-in: 3c6e1cbb user: dan tags: ota-update)
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)
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Added ext/ota/README.txt.

            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  +