/ Check-in [5af8db56af]
Login

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

Overview
Comment:Allow OTA update state data to be stored in a database separate from the OTA update database.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | ota-update
Files: files | file ages | folders
SHA1: 5af8db56af457d60ea030d84666ca7fffb6821fe
User & Date: dan 2015-05-19 16:22:58
Context
2015-05-19
16:26
Add a comment for SQLITE_FCNTL_OTA to sqlite.h.in. Closed-Leaf check-in: efa20f8e41 user: dan tags: ota-update
16:22
Allow OTA update state data to be stored in a database separate from the OTA update database. check-in: 5af8db56af user: dan tags: ota-update
14:14
Merge latest trunk changes with this branch. check-in: 6055a6725c user: dan tags: ota-update
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to ext/ota/ota1.test.

   124    124       sqlite3ota ota file:$target?xyz=&abc=123 $ota
   125    125       set rc [ota step]
   126    126       ota close
   127    127       if {$rc != "SQLITE_OK"} break
   128    128     }
   129    129     set rc
   130    130   }
          131  +
          132  +# Same as [step_ota], except using an external state database - "state.db"
          133  +#
          134  +proc step_ota_state {target ota} {
          135  +  while 1 {
          136  +    sqlite3ota ota $target $ota state.db
          137  +    set rc [ota step]
          138  +    ota close
          139  +    if {$rc != "SQLITE_OK"} break
          140  +  }
          141  +  set rc
          142  +}
          143  +
          144  +proc dbfilecksum {file} {
          145  +  sqlite3 ck $file
          146  +  set cksum [dbcksum ck main]
          147  +  ck close
          148  +  set cksum
          149  +}
   131    150   
   132    151   foreach {tn3 create_vfs destroy_vfs} {
   133    152     1 {} {}
   134    153     2 {
   135    154       sqlite3ota_create_vfs -default myota ""
   136    155     } {
   137    156       sqlite3ota_destroy_vfs myota
   138    157     }
   139    158   } {
   140    159   
   141    160     eval $create_vfs
   142    161   
   143         -  foreach {tn2 cmd} {1 run_ota 2 step_ota 3 step_ota_uri} {
          162  +  foreach {tn2 cmd} {
          163  +      1 run_ota 
          164  +      2 step_ota 3 step_ota_uri 4 step_ota_state
          165  +  } {
   144    166       foreach {tn schema} {
   145    167         1 {
   146    168           CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c);
   147    169         }
   148    170         2 { 
   149    171           CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c);
   150    172           CREATE INDEX i1 ON t1(b);
................................................................................
   214    236         16 { 
   215    237           CREATE TABLE t1(a, b, c, PRIMARY KEY(c DESC, a)) WITHOUT ROWID;
   216    238           CREATE INDEX i1 ON t1(b DESC, c, a);
   217    239         }
   218    240       } {
   219    241         reset_db
   220    242         execsql $schema
          243  +      create_ota1 ota.db
          244  +      set check [dbfilecksum ota.db]
          245  +      forcedelete state.db
   221    246   
   222    247         do_test $tn3.1.$tn2.$tn.1 {
   223         -        create_ota1 ota.db
   224    248           $cmd test.db ota.db
   225    249         } {SQLITE_DONE}
   226    250   
   227    251         do_execsql_test $tn3.1.$tn2.$tn.2 { SELECT * FROM t1 ORDER BY a ASC } {
   228    252           1 2 3 
   229    253           2 two three 
   230    254           3 {} 8.2
................................................................................
   237    261         do_execsql_test $tn3.1.$tn2.$tn.4 { SELECT * FROM t1 ORDER BY c ASC } {
   238    262           1 2 3 
   239    263           3 {} 8.2
   240    264           2 two three 
   241    265         }
   242    266      
   243    267         do_execsql_test $tn3.1.$tn2.$tn.5 { PRAGMA integrity_check } ok
          268  +
          269  +      if {$cmd=="step_ota_state"} {
          270  +        do_test $tn3.1.$tn2.$tn.6 { file exists state.db } 1
          271  +        do_test $tn3.1.$tn2.$tn.7 { expr {$check == [dbfilecksum ota.db]} } 1
          272  +      } else {
          273  +        do_test $tn3.1.$tn2.$tn.8 { file exists state.db } 0
          274  +        do_test $tn3.1.$tn2.$tn.9 { expr {$check == [dbfilecksum ota.db]} } 0
          275  +      }
   244    276       }
   245    277     }
   246    278   
   247    279     #-------------------------------------------------------------------------
   248    280     # Check that an OTA cannot be applied to a table that has no PK.
   249    281     #
   250    282     # UPDATE: At one point OTA required that all tables featured either
................................................................................
   321    353       } [list 1 "$errcode - $errmsg"]
   322    354   
   323    355       do_test $tn3.3.$tn.4 { dbcksum db main } $cksum
   324    356     }
   325    357   
   326    358     #-------------------------------------------------------------------------
   327    359     #
   328         -  foreach {tn2 cmd} {1 run_ota 2 step_ota} {
          360  +  foreach {tn2 cmd} {1 run_ota 2 step_ota 3 step_ota_state } {
   329    361       foreach {tn schema} {
   330    362         1 {
   331    363           CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c);
   332    364         }
   333    365         2 {
   334    366           CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c);
   335    367           CREATE INDEX i1 ON t1(b);
................................................................................
   369    401         reset_db
   370    402         execsql $schema
   371    403         execsql {
   372    404           INSERT INTO t1 VALUES(2, 'hello', 'world');
   373    405           INSERT INTO t1 VALUES(4, 'hello', 'planet');
   374    406           INSERT INTO t1 VALUES(6, 'hello', 'xyz');
   375    407         }
          408  +
          409  +      create_ota4 ota.db
          410  +      set check [dbfilecksum ota.db]
          411  +      forcedelete state.db
   376    412       
   377    413         do_test $tn3.4.$tn2.$tn.1 {
   378         -        create_ota4 ota.db
   379    414           $cmd test.db ota.db
   380    415         } {SQLITE_DONE}
   381    416         
   382    417         do_execsql_test $tn3.4.$tn2.$tn.2 {
   383    418           SELECT * FROM t1 ORDER BY a ASC;
   384    419         } {
   385    420           1 2 3 
   386    421           3 8 9
   387    422           6 hello xyz
   388    423         }
   389    424       
   390    425         do_execsql_test $tn3.4.$tn2.$tn.3 { PRAGMA integrity_check } ok
          426  +
          427  +      if {$cmd=="step_ota_state"} {
          428  +        do_test $tn3.4.$tn2.$tn.4 { file exists state.db } 1
          429  +        do_test $tn3.4.$tn2.$tn.5 { expr {$check == [dbfilecksum ota.db]} } 1
          430  +      } else {
          431  +        do_test $tn3.4.$tn2.$tn.6 { file exists state.db } 0
          432  +        do_test $tn3.4.$tn2.$tn.7 { expr {$check == [dbfilecksum ota.db]} } 0
          433  +      }
   391    434       }
   392    435     }
   393    436   
   394         -  foreach {tn2 cmd} {1 run_ota 2 step_ota} {
          437  +  foreach {tn2 cmd} {1 run_ota 2 step_ota 3 step_ota_state} {
   395    438       foreach {tn schema} {
   396    439         1 {
   397    440           CREATE TABLE t1(c, b, '(a)' INTEGER PRIMARY KEY);
   398    441           CREATE INDEX i1 ON t1(c, b);
   399    442         }
   400    443         2 {
   401    444           CREATE TABLE t1(c, b, '(a)' PRIMARY KEY);
................................................................................
   407    450         reset_db
   408    451         execsql $schema
   409    452         execsql {
   410    453           INSERT INTO t1('(a)', b, c) VALUES(2, 'hello', 'world');
   411    454           INSERT INTO t1('(a)', b, c) VALUES(4, 'hello', 'planet');
   412    455           INSERT INTO t1('(a)', b, c) VALUES(6, 'hello', 'xyz');
   413    456         }
          457  +
          458  +      create_ota4b ota.db
          459  +      set check [dbfilecksum ota.db]
          460  +      forcedelete state.db
   414    461       
   415         -      do_test $tn3.4.$tn2.$tn.1 {
   416         -        create_ota4b ota.db
          462  +      do_test $tn3.5.$tn2.$tn.1 {
   417    463           $cmd test.db ota.db
   418    464         } {SQLITE_DONE}
   419    465         
   420         -      do_execsql_test $tn3.4.$tn2.$tn.2 {
          466  +      do_execsql_test $tn3.5.$tn2.$tn.2 {
   421    467           SELECT * FROM t1 ORDER BY "(a)" ASC;
   422    468         } {
   423    469           3 2 1
   424    470           9 8 3
   425    471           xyz hello 6
   426    472         }
   427    473       
   428    474         do_execsql_test $tn3.4.$tn2.$tn.3 { PRAGMA integrity_check } ok
          475  +
          476  +      if {$cmd=="step_ota_state"} {
          477  +        do_test $tn3.5.$tn2.$tn.4 { file exists state.db } 1
          478  +        do_test $tn3.5.$tn2.$tn.5 { expr {$check == [dbfilecksum ota.db]} } 1
          479  +      } else {
          480  +        do_test $tn3.5.$tn2.$tn.6 { file exists state.db } 0
          481  +        do_test $tn3.5.$tn2.$tn.7 { expr {$check == [dbfilecksum ota.db]} } 0
          482  +      }
   429    483       }
   430    484     }
   431    485   
   432    486     #-------------------------------------------------------------------------
   433    487     #
   434         -  foreach {tn2 cmd} {1 run_ota 2 step_ota} {
          488  +  foreach {tn2 cmd} {1 run_ota 2 step_ota 3 step_ota_state} {
   435    489       foreach {tn schema} {
   436    490         1 {
   437    491           CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c, d);
   438    492         }
   439    493         2 {
   440    494           CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c, d);
   441    495           CREATE INDEX i1 ON t1(d);
................................................................................
   468    522         execsql $schema
   469    523         execsql {
   470    524           INSERT INTO t1 VALUES(1, 2, 3, 4);
   471    525           INSERT INTO t1 VALUES(2, 5, 6, 7);
   472    526           INSERT INTO t1 VALUES(3, 8, 9, 10);
   473    527         }
   474    528       
          529  +      create_ota5 ota.db
          530  +      set check [dbfilecksum ota.db]
          531  +      forcedelete state.db
          532  +
   475    533         do_test $tn3.5.$tn2.$tn.1 {
   476         -        create_ota5 ota.db
   477    534           $cmd test.db ota.db
   478    535         } {SQLITE_DONE}
   479    536         
   480    537         do_execsql_test $tn3.5.$tn2.$tn.2 {
   481    538           SELECT * FROM t1 ORDER BY a ASC;
   482    539         } {
   483    540           1 2 3 5
   484    541           2 5 10 5
   485    542           3 11 9 10
   486    543         }
   487    544       
   488         -      do_execsql_test $tn3.5.$tn2.$tn.3 { PRAGMA integrity_check } ok
          545  +      do_execsql_test $tn3.6.$tn2.$tn.3 { PRAGMA integrity_check } ok
          546  +
          547  +      if {$cmd=="step_ota_state"} {
          548  +        do_test $tn3.6.$tn2.$tn.4 { file exists state.db } 1
          549  +        do_test $tn3.6.$tn2.$tn.5 { expr {$check == [dbfilecksum ota.db]} } 1
          550  +      } else {
          551  +        do_test $tn3.6.$tn2.$tn.6 { file exists state.db } 0
          552  +        do_test $tn3.6.$tn2.$tn.7 { expr {$check == [dbfilecksum ota.db]} } 0
          553  +      }
   489    554       }
   490    555     }
   491    556   
   492    557     #-------------------------------------------------------------------------
   493    558     # Test some error cases:
   494    559     # 
   495    560     #   * A virtual table with no ota_rowid column.
................................................................................
   562    627   
   563    628       } {
   564    629         reset_db
   565    630         forcedelete ota.db
   566    631         execsql { ATTACH 'ota.db' AS ota }
   567    632         execsql $schema
   568    633   
   569         -      do_test $tn3.6.$tn {
          634  +      do_test $tn3.7.$tn {
   570    635           list [catch { run_ota test.db ota.db } msg] $msg
   571    636         } [list 1 $error]
   572    637       }
   573    638     }
   574    639   
   575    640     # Test that an OTA database containing no input tables is handled
   576    641     # correctly.
   577    642     reset_db
   578    643     forcedelete ota.db
   579         -  do_test $tn3.7 {
          644  +  do_test $tn3.8 {
   580    645       list [catch { run_ota test.db ota.db } msg] $msg
   581    646     } {0 SQLITE_DONE}
   582    647     
   583    648     # Test that OTA can update indexes containing NULL values.
   584    649     #
   585    650     reset_db
   586    651     forcedelete ota.db
   587         -  do_execsql_test $tn3.8.1 {
          652  +  do_execsql_test $tn3.9.1 {
   588    653       CREATE TABLE t1(a PRIMARY KEY, b, c);
   589    654       CREATE INDEX i1 ON t1(b, c);
   590    655       INSERT INTO t1 VALUES(1, 1, NULL);
   591    656       INSERT INTO t1 VALUES(2, NULL, 2);
   592    657       INSERT INTO t1 VALUES(3, NULL, NULL);
   593    658   
   594    659       ATTACH 'ota.db' AS ota;
   595    660       CREATE TABLE ota.data_t1(a, b, c, ota_control);
   596    661       INSERT INTO data_t1 VALUES(1, NULL, NULL, 1);
   597    662       INSERT INTO data_t1 VALUES(3, NULL, NULL, 1);
   598    663     } {}
   599    664   
   600         -  do_test $tn3.8.2 {
          665  +  do_test $tn3.9.2 {
   601    666       list [catch { run_ota test.db ota.db } msg] $msg
   602    667     } {0 SQLITE_DONE}
   603    668   
   604         -  do_execsql_test $tn3.8.3 {
          669  +  do_execsql_test $tn3.9.3 {
   605    670       SELECT * FROM t1
   606    671     } {2 {} 2}
   607         -  do_execsql_test $tn3.8.4 { PRAGMA integrity_check } {ok}
          672  +  do_execsql_test $tn3.9.4 { PRAGMA integrity_check } {ok}
   608    673   
   609    674     catch { db close }
   610    675     eval $destroy_vfs
   611    676   }
   612    677   
   613    678   
   614    679   finish_test
   615    680   

Changes to ext/ota/sqlite3ota.c.

   156    156   #define OTA_STAGE_OAL         1
   157    157   #define OTA_STAGE_MOVE        2
   158    158   #define OTA_STAGE_CAPTURE     3
   159    159   #define OTA_STAGE_CKPT        4
   160    160   #define OTA_STAGE_DONE        5
   161    161   
   162    162   
   163         -#define OTA_CREATE_STATE "CREATE TABLE IF NOT EXISTS ota_state"        \
   164         -                             "(k INTEGER PRIMARY KEY, v)"
          163  +#define OTA_CREATE_STATE \
          164  +  "CREATE TABLE IF NOT EXISTS %s.ota_state(k INTEGER PRIMARY KEY, v)"
   165    165   
   166    166   typedef struct OtaFrame OtaFrame;
   167    167   typedef struct OtaObjIter OtaObjIter;
   168    168   typedef struct OtaState OtaState;
   169    169   typedef struct ota_vfs ota_vfs;
   170    170   typedef struct ota_file ota_file;
   171    171   typedef struct OtaUpdateStmt OtaUpdateStmt;
................................................................................
   295    295   */
   296    296   struct sqlite3ota {
   297    297     int eStage;                     /* Value of OTA_STATE_STAGE field */
   298    298     sqlite3 *dbMain;                /* target database handle */
   299    299     sqlite3 *dbOta;                 /* ota database handle */
   300    300     char *zTarget;                  /* Path to target db */
   301    301     char *zOta;                     /* Path to ota db */
          302  +  char *zState;                   /* Path to state db (or NULL if zOta) */
          303  +  char zStateDb[5];               /* Db name for state ("stat" or "main") */
   302    304     int rc;                         /* Value returned by last ota_step() call */
   303    305     char *zErrmsg;                  /* Error message if rc!=SQLITE_OK */
   304    306     int nStep;                      /* Rows processed for current object */
   305    307     int nProgress;                  /* Rows processed for all objects */
   306    308     OtaObjIter objiter;             /* Iterator for skipping through tbl/idx */
   307    309     const char *zVfsName;           /* Name of automatically created ota vfs */
   308    310     ota_file *pTargetFd;            /* File handle open on target db */
................................................................................
  1508   1510   ){
  1509   1511     int bOtaRowid = (pIter->eType==OTA_PK_EXTERNAL || pIter->eType==OTA_PK_NONE);
  1510   1512     char *zBind = otaObjIterGetBindlist(p, pIter->nTblCol + 1 + bOtaRowid);
  1511   1513     if( zBind ){
  1512   1514       assert( pIter->pTmpInsert==0 );
  1513   1515       p->rc = prepareFreeAndCollectError(
  1514   1516           p->dbOta, &pIter->pTmpInsert, &p->zErrmsg, sqlite3_mprintf(
  1515         -          "INSERT INTO 'ota_tmp_%q'(ota_control,%s%s) VALUES(%z)", 
  1516         -          pIter->zTbl, zCollist, zOtaRowid, zBind
         1517  +          "INSERT INTO %s.'ota_tmp_%q'(ota_control,%s%s) VALUES(%z)", 
         1518  +          p->zStateDb, pIter->zTbl, zCollist, zOtaRowid, zBind
  1517   1519       ));
  1518   1520     }
  1519   1521   }
  1520   1522   
  1521   1523   static void otaTmpInsertFunc(
  1522   1524     sqlite3_context *pCtx, 
  1523   1525     int nVal,
................................................................................
  1604   1606         }
  1605   1607   
  1606   1608         /* Create the SELECT statement to read keys in sorted order */
  1607   1609         if( p->rc==SQLITE_OK ){
  1608   1610           char *zSql;
  1609   1611           if( pIter->eType==OTA_PK_EXTERNAL || pIter->eType==OTA_PK_NONE ){
  1610   1612             zSql = sqlite3_mprintf(
  1611         -              "SELECT %s, ota_control FROM 'ota_tmp_%q' ORDER BY %s%s",
  1612         -              zCollist, pIter->zTbl,
         1613  +              "SELECT %s, ota_control FROM %s.'ota_tmp_%q' ORDER BY %s%s",
         1614  +              zCollist, p->zStateDb, pIter->zTbl,
  1613   1615                 zCollist, zLimit
  1614   1616             );
  1615   1617           }else{
  1616   1618             zSql = sqlite3_mprintf(
  1617   1619                 "SELECT %s, ota_control FROM 'data_%q' "
  1618   1620                 "WHERE typeof(ota_control)='integer' AND ota_control!=1 "
  1619   1621                 "UNION ALL "
  1620         -              "SELECT %s, ota_control FROM 'ota_tmp_%q' "
         1622  +              "SELECT %s, ota_control FROM %s.'ota_tmp_%q' "
  1621   1623                 "ORDER BY %s%s",
  1622   1624                 zCollist, pIter->zTbl, 
  1623         -              zCollist, pIter->zTbl, 
         1625  +              zCollist, p->zStateDb, pIter->zTbl, 
  1624   1626                 zCollist, zLimit
  1625   1627             );
  1626   1628           }
  1627   1629           p->rc = prepareFreeAndCollectError(p->dbOta, &pIter->pSelect, pz, zSql);
  1628   1630         }
  1629   1631   
  1630   1632         sqlite3_free(zImposterCols);
................................................................................
  1682   1684           const char *zOtaRowid = "";
  1683   1685           if( pIter->eType==OTA_PK_EXTERNAL || pIter->eType==OTA_PK_NONE ){
  1684   1686             zOtaRowid = ", ota_rowid";
  1685   1687           }
  1686   1688   
  1687   1689           /* Create the ota_tmp_xxx table and the triggers to populate it. */
  1688   1690           otaMPrintfExec(p, p->dbOta,
  1689         -            "CREATE TABLE IF NOT EXISTS 'ota_tmp_%q' AS "
         1691  +            "CREATE TABLE IF NOT EXISTS %s.'ota_tmp_%q' AS "
  1690   1692               "SELECT *%s FROM 'data_%q' WHERE 0;"
         1693  +            , p->zStateDb
  1691   1694               , zTbl, (pIter->eType==OTA_PK_EXTERNAL ? ", 0 AS ota_rowid" : "")
  1692   1695               , zTbl
  1693   1696           );
  1694   1697   
  1695   1698           otaMPrintfExec(p, p->dbMain,
  1696   1699               "CREATE TEMP TRIGGER ota_delete_tr BEFORE DELETE ON \"%s%w\" "
  1697   1700               "BEGIN "
................................................................................
  1835   1838   static void otaOpenDatabase(sqlite3ota *p){
  1836   1839     assert( p->rc==SQLITE_OK );
  1837   1840     assert( p->dbMain==0 && p->dbOta==0 );
  1838   1841   
  1839   1842     p->eStage = 0;
  1840   1843     p->dbMain = otaOpenDbhandle(p, p->zTarget);
  1841   1844     p->dbOta = otaOpenDbhandle(p, p->zOta);
         1845  +
         1846  +  /* If using separate OTA and state databases, attach the state database to
         1847  +  ** the OTA db handle now.  */
         1848  +  if( p->zState ){
         1849  +    otaMPrintfExec(p, p->dbOta, "ATTACH %Q AS stat", p->zState);
         1850  +    memcpy(p->zStateDb, "stat", 4);
         1851  +  }else{
         1852  +    memcpy(p->zStateDb, "main", 4);
         1853  +  }
  1842   1854   
  1843   1855     if( p->rc==SQLITE_OK ){
  1844   1856       p->rc = sqlite3_create_function(p->dbMain, 
  1845   1857           "ota_tmp_insert", -1, SQLITE_UTF8, (void*)p, otaTmpInsertFunc, 0, 0
  1846   1858       );
  1847   1859     }
  1848   1860   
................................................................................
  2336   2348     if( p->rc==SQLITE_OK || p->rc==SQLITE_DONE ){
  2337   2349       sqlite3_stmt *pInsert = 0;
  2338   2350       int rc;
  2339   2351   
  2340   2352       assert( p->zErrmsg==0 );
  2341   2353       rc = prepareFreeAndCollectError(p->dbOta, &pInsert, &p->zErrmsg, 
  2342   2354           sqlite3_mprintf(
  2343         -          "INSERT OR REPLACE INTO ota_state(k, v) VALUES "
         2355  +          "INSERT OR REPLACE INTO %s.ota_state(k, v) VALUES "
  2344   2356             "(%d, %d), "
  2345   2357             "(%d, %Q), "
  2346   2358             "(%d, %Q), "
  2347   2359             "(%d, %d), "
  2348   2360             "(%d, %d), "
  2349   2361             "(%d, %lld), "
  2350   2362             "(%d, %lld), "
  2351   2363             "(%d, %lld) ",
         2364  +          p->zStateDb,
  2352   2365             OTA_STATE_STAGE, eStage,
  2353   2366             OTA_STATE_TBL, p->objiter.zTbl, 
  2354   2367             OTA_STATE_IDX, p->objiter.zIdx, 
  2355   2368             OTA_STATE_ROW, p->nStep, 
  2356   2369             OTA_STATE_PROGRESS, p->nProgress,
  2357   2370             OTA_STATE_CKPT, p->iWalCksum,
  2358   2371             OTA_STATE_COOKIE, (i64)p->pTargetFd->iCookie,
................................................................................
  2381   2394           while( p->rc==SQLITE_OK && pIter->zTbl ){
  2382   2395   
  2383   2396             if( pIter->bCleanup ){
  2384   2397               /* Clean up the ota_tmp_xxx table for the previous table. It 
  2385   2398               ** cannot be dropped as there are currently active SQL statements.
  2386   2399               ** But the contents can be deleted.  */
  2387   2400               if( pIter->abIndexed ){
  2388         -              const char *zTbl = pIter->zTbl;
  2389         -              otaMPrintfExec(p, p->dbOta, "DELETE FROM 'ota_tmp_%q'", zTbl);
         2401  +              otaMPrintfExec(p, p->dbOta, 
         2402  +                  "DELETE FROM %s.'ota_tmp_%q'", p->zStateDb, pIter->zTbl
         2403  +              );
  2390   2404               }
  2391   2405             }else{
  2392   2406               otaObjIterPrepareAll(p, pIter, 0);
  2393   2407   
  2394   2408               /* Advance to the next row to process. */
  2395   2409               if( p->rc==SQLITE_OK ){
  2396   2410                 int rc = sqlite3_step(pIter->pSelect);
................................................................................
  2487   2501   ** responsibility of the caller to eventually free the object using
  2488   2502   ** sqlite3_free().
  2489   2503   **
  2490   2504   ** If an error occurs, leave an error code and message in the ota handle
  2491   2505   ** and return NULL.
  2492   2506   */
  2493   2507   static OtaState *otaLoadState(sqlite3ota *p){
  2494         -  const char *zSelect = "SELECT k, v FROM ota_state";
  2495   2508     OtaState *pRet = 0;
  2496   2509     sqlite3_stmt *pStmt = 0;
  2497   2510     int rc;
  2498   2511     int rc2;
  2499   2512   
  2500   2513     pRet = (OtaState*)otaMalloc(p, sizeof(OtaState));
  2501   2514     if( pRet==0 ) return 0;
  2502   2515   
  2503         -  rc = prepareAndCollectError(p->dbOta, &pStmt, &p->zErrmsg, zSelect);
         2516  +  rc = prepareFreeAndCollectError(p->dbOta, &pStmt, &p->zErrmsg, 
         2517  +      sqlite3_mprintf("SELECT k, v FROM %s.ota_state", p->zStateDb)
         2518  +  );
  2504   2519     while( rc==SQLITE_OK && SQLITE_ROW==sqlite3_step(pStmt) ){
  2505   2520       switch( sqlite3_column_int(pStmt, 0) ){
  2506   2521         case OTA_STATE_STAGE:
  2507   2522           pRet->eStage = sqlite3_column_int(pStmt, 1);
  2508   2523           if( pRet->eStage!=OTA_STAGE_OAL
  2509   2524            && pRet->eStage!=OTA_STAGE_MOVE
  2510   2525            && pRet->eStage!=OTA_STAGE_CKPT
................................................................................
  2641   2656   static void otaDeleteVfs(sqlite3ota *p){
  2642   2657     if( p->zVfsName ){
  2643   2658       sqlite3ota_destroy_vfs(p->zVfsName);
  2644   2659       p->zVfsName = 0;
  2645   2660     }
  2646   2661   }
  2647   2662   
  2648         -/*
  2649         -** Open and return a new OTA handle. 
  2650         -*/
  2651         -sqlite3ota *sqlite3ota_open(const char *zTarget, const char *zOta){
         2663  +static sqlite3ota *otaOpen(
         2664  +  const char *zTarget, 
         2665  +  const char *zOta,
         2666  +  const char *zState
         2667  +){
  2652   2668     sqlite3ota *p;
  2653   2669     int nTarget = strlen(zTarget);
  2654   2670     int nOta = strlen(zOta);
         2671  +  int nState = zState ? strlen(zState) : 0;
  2655   2672   
  2656         -  p = (sqlite3ota*)sqlite3_malloc(sizeof(sqlite3ota)+nTarget+1+nOta+1);
         2673  +  p = (sqlite3ota*)sqlite3_malloc(sizeof(sqlite3ota)+nTarget+1+nOta+1+nState+1);
  2657   2674     if( p ){
  2658   2675       OtaState *pState = 0;
  2659   2676   
  2660   2677       /* Create the custom VFS. */
  2661   2678       memset(p, 0, sizeof(sqlite3ota));
  2662   2679       otaCreateVfs(p);
  2663   2680   
  2664   2681       /* Open the target database */
  2665   2682       if( p->rc==SQLITE_OK ){
  2666   2683         p->zTarget = (char*)&p[1];
  2667   2684         memcpy(p->zTarget, zTarget, nTarget+1);
  2668   2685         p->zOta = &p->zTarget[nTarget+1];
  2669   2686         memcpy(p->zOta, zOta, nOta+1);
         2687  +      if( zState ){
         2688  +        p->zState = &p->zOta[nOta+1];
         2689  +        memcpy(p->zState, zState, nState+1);
         2690  +      }
  2670   2691         otaOpenDatabase(p);
  2671   2692       }
  2672   2693   
  2673   2694       /* If it has not already been created, create the ota_state table */
  2674         -    if( p->rc==SQLITE_OK ){
  2675         -      p->rc = sqlite3_exec(p->dbOta, OTA_CREATE_STATE, 0, 0, &p->zErrmsg);
  2676         -    }
         2695  +    otaMPrintfExec(p, p->dbOta, OTA_CREATE_STATE, p->zStateDb);
  2677   2696   
  2678   2697       if( p->rc==SQLITE_OK ){
  2679   2698         pState = otaLoadState(p);
  2680   2699         assert( pState || p->rc!=SQLITE_OK );
  2681   2700         if( p->rc==SQLITE_OK ){
  2682   2701   
  2683   2702           if( pState->eStage==0 ){ 
................................................................................
  2752   2771   
  2753   2772       otaFreeState(pState);
  2754   2773     }
  2755   2774   
  2756   2775     return p;
  2757   2776   }
  2758   2777   
         2778  +
         2779  +/*
         2780  +** Open and return a new OTA handle. 
         2781  +*/
         2782  +sqlite3ota *sqlite3ota_open_v2(
         2783  +  const char *zDb, 
         2784  +  const char *zOta, 
         2785  +  const char *zState
         2786  +){
         2787  +  return otaOpen(zDb, zOta, zState);
         2788  +}
         2789  +
         2790  +/*
         2791  +** Open and return a new OTA handle. 
         2792  +*/
         2793  +sqlite3ota *sqlite3ota_open(
         2794  +  const char *zDb, 
         2795  +  const char *zOta 
         2796  +){
         2797  +  return otaOpen(zDb, zOta, 0);
         2798  +}
         2799  +
  2759   2800   /*
  2760   2801   ** Return the database handle used by pOta.
  2761   2802   */
  2762   2803   sqlite3 *sqlite3ota_db(sqlite3ota *pOta, int bOta){
  2763   2804     sqlite3 *db = 0;
  2764   2805     if( pOta ){
  2765   2806       db = (bOta ? pOta->dbOta : pOta->dbMain);

Changes to ext/ota/sqlite3ota.h.

   267    267   **
   268    268   ** IMPORTANT NOTE FOR ZIPVFS USERS: The OTA extension works with all of
   269    269   ** SQLite's built-in VFSs, including the multiplexor VFS. However it does
   270    270   ** not work out of the box with zipvfs. Refer to the comment describing
   271    271   ** the zipvfs_create_vfs() API below for details on using OTA with zipvfs.
   272    272   */
   273    273   sqlite3ota *sqlite3ota_open(const char *zTarget, const char *zOta);
          274  +
          275  +/*
          276  +** Open an OTA handle with an auxiliary state file.
          277  +**
          278  +** This API is similar to sqlite3ota_open(), except that it allows the user 
          279  +** to specify a separate SQLite database in which to store the OTA update 
          280  +** state.
          281  +**
          282  +** While executing, the OTA extension usually stores the current state 
          283  +** of the update (how many rows have been updated, which indexes are yet
          284  +** to be updated etc.) within the OTA database itself. This can be 
          285  +** convenient, as it means that the OTA application does not need to
          286  +** organize removing a separate state file after the update is concluded.
          287  +** However, it can also be inconvenient - for example if the OTA update
          288  +** database is sto be stored on a read-only media.
          289  +**
          290  +** If an OTA update started using a handle opened with this function is
          291  +** suspended, the application must use this function to resume it, and 
          292  +** must pass the same zState argument each time the update is resumed.
          293  +** Attempting to resume an sqlite3ota_open_v2() update using sqlite3ota_open(),
          294  +** or with a call to sqlite3ota_open_v2() specifying a different zState
          295  +** argument leads to undefined behaviour.
          296  +**
          297  +** Once the OTA update is finished, the OTA extension does not 
          298  +** automatically remove the zState database file, even if it created it.
          299  +*/
          300  +sqlite3ota *sqlite3ota_open_v2(
          301  +  const char *zTarget,
          302  +  const char *zOta,
          303  +  const char *zState
          304  +);
   274    305   
   275    306   /*
   276    307   ** Internally, each OTA connection uses a separate SQLite database 
   277    308   ** connection to access the target and ota update databases. This
   278    309   ** API allows the application direct access to these database handles.
   279    310   **
   280    311   ** The first argument passed to this function must be a valid, open, OTA

Changes to ext/ota/test_ota.c.

   108    108         break;
   109    109     }
   110    110   
   111    111     return ret;
   112    112   }
   113    113   
   114    114   /*
   115         -** Tclcmd: sqlite3ota CMD <target-db> <ota-db>
          115  +** Tclcmd: sqlite3ota CMD <target-db> <ota-db> ?<state-db>?
   116    116   */
   117    117   static int test_sqlite3ota(
   118    118     ClientData clientData,
   119    119     Tcl_Interp *interp,
   120    120     int objc,
   121    121     Tcl_Obj *CONST objv[]
   122    122   ){
   123    123     sqlite3ota *pOta = 0;
   124    124     const char *zCmd;
   125    125     const char *zTarget;
   126    126     const char *zOta;
   127    127   
   128         -  if( objc!=4 ){
   129         -    Tcl_WrongNumArgs(interp, 1, objv, "NAME TARGET-DB OTA-DB");
          128  +  if( objc!=4 && objc!=5 ){
          129  +    Tcl_WrongNumArgs(interp, 1, objv, "NAME TARGET-DB OTA-DB ?STATE-DB?");
   130    130       return TCL_ERROR;
   131    131     }
   132    132     zCmd = Tcl_GetString(objv[1]);
   133    133     zTarget = Tcl_GetString(objv[2]);
   134    134     zOta = Tcl_GetString(objv[3]);
   135    135   
   136         -  pOta = sqlite3ota_open(zTarget, zOta);
          136  +  if( objc==4 ){
          137  +    pOta = sqlite3ota_open(zTarget, zOta);
          138  +  }else{
          139  +    const char *zStateDb = Tcl_GetString(objv[4]);
          140  +    pOta = sqlite3ota_open_v2(zTarget, zOta, zStateDb);
          141  +  }
   137    142     Tcl_CreateObjCommand(interp, zCmd, test_sqlite3ota_cmd, (ClientData)pOta, 0);
   138    143     Tcl_SetObjResult(interp, objv[1]);
   139    144     return TCL_OK;
   140    145   }
   141    146   
   142    147   /*
   143    148   ** Tclcmd: sqlite3ota_create_vfs ?-default? NAME PARENT