/ Check-in [088a41eb]
Login

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

Overview
Comment:Update comments in sqlite3ota.h to remove the "must have PRIMARY KEY" restriction.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | ota-update
Files: files | file ages | folders
SHA1:088a41eb8c18886a260cf53fa0cca3bd1958dc05
User & Date: dan 2014-12-08 07:28:26
Context
2014-12-08
07:50
Update this branch with latest trunk changes. check-in: 69a312ad user: dan tags: ota-update
07:28
Update comments in sqlite3ota.h to remove the "must have PRIMARY KEY" restriction. check-in: 088a41eb user: dan tags: ota-update
07:22
Extra tests for the ota_rowid column. check-in: 46069393 user: dan tags: ota-update
Changes
Hide Diffs Side-by-Side Diffs Show Whitespace Changes Patch

Changes to ext/ota/sqlite3ota.h.

    57     57   ** An "OTA update" transaction is subject to the following limitations:
    58     58   **
    59     59   **   * The transaction must consist of INSERT, UPDATE and DELETE operations
    60     60   **     only.
    61     61   **
    62     62   **   * INSERT statements may not use any default values.
    63     63   **
    64         -**   * Non-virtual tables that do not have declared primary keys may not 
    65         -**     be updated. UPDATE and DELETE statements must identify their target 
    66         -**     rows by PRIMARY KEY values. 
           64  +**   * UPDATE and DELETE statements must identify their target rows by 
           65  +**     PRIMARY KEY values. If the table being written has no PRIMARY KEY
           66  +**     declaration, affected rows must be identified by rowid.
    67     67   **
    68     68   **   * UPDATE statements may not modify PRIMARY KEY columns.
    69     69   **
    70     70   **   * No triggers will be fired.
    71     71   **
    72     72   **   * No foreign key violations are detected or reported.
    73     73   **
................................................................................
    90     90   **
    91     91   ** Then the OTA database should contain:
    92     92   **
    93     93   **   CREATE TABLE data_t1(a INTEGER, b TEXT, c, ota_control);
    94     94   **
    95     95   ** The order of the columns in the data_% table does not matter.
    96     96   **
    97         -** If the target database table is a virtual table, the data_% table should
    98         -** also contain a column named "ota_rowid". This column is mapped to the
    99         -** virtual tables implicit primary key column - "rowid". Virtual tables
   100         -** for which the "rowid" column does not function like a primary key value
   101         -** can not be updated using OTA. For example, if the target db contains:
           97  +** If the target database table is a virtual table or a table that has no
           98  +** PRIMARY KEY declaration, the data_% table must also contain a column 
           99  +** named "ota_rowid". This column is mapped to the tables implicit primary 
          100  +** key column - "rowid". Virtual tables for which the "rowid" column does 
          101  +** not function like a primary key value cannot be updated using OTA. For 
          102  +** example, if the target db contains either of the following:
   102    103   **
   103         -**   CREATE VIRTUAL TABLE ft1 USING fts3(a, b);
          104  +**   CREATE VIRTUAL TABLE x1 USING fts3(a, b);
          105  +**   CREATE TABLE x1(a, b)
   104    106   **
   105    107   ** then the OTA database should contain:
   106    108   **
   107         -**   CREATE TABLE data_ft1(a, b, ota_rowid, ota_control);
          109  +**   CREATE TABLE data_x1(a, b, ota_rowid, ota_control);
   108    110   **
   109    111   ** All non-hidden columns (i.e. all columns matched by "SELECT *") of the
   110    112   ** target table must be present in the input table. For virtual tables,
   111    113   ** hidden columns are optional - they are updated by OTA if present in
   112    114   ** the input table, or not otherwise. For example, to write to an fts4
   113    115   ** table with a hidden languageid column such as:
   114    116   **
................................................................................
   169    171   **
   170    172   **   INSERT INTO data_t1(a, b, c, ota_control) VALUES(4, NULL, 'usa', '..d');
   171    173   **
   172    174   ** is similar to an UPDATE statement such as: 
   173    175   **
   174    176   **   UPDATE t1 SET c = ota_delta(c, 'usa') WHERE a = 4;
   175    177   **
   176         -** If the target database table is a virtual table, the ota_control value
   177         -** should not include a character corresponding to the ota_rowid value.
   178         -** For example, this:
          178  +** If the target database table is a virtual table or a table with no PRIMARY
          179  +** KEY, the ota_control value should not include a character corresponding 
          180  +** to the ota_rowid value. For example, this:
   179    181   **
   180    182   **   INSERT INTO data_ft1(a, b, ota_rowid, ota_control) 
   181         -**       VALUES(NULL, 'usa', 12, '..d');
          183  +**       VALUES(NULL, 'usa', 12, '.x');
   182    184   **
   183    185   ** causes a result similar to:
   184    186   **
   185    187   **   UPDATE ft1 SET b = 'usa' WHERE rowid = 12;
   186    188   **
   187    189   **
   188    190   ** USAGE