SQLite

    sqlite3ota ota file:$target?xyz=&abc=123 $ota
    set rc [ota step]
    ota close
    if {$rc != "SQLITE_OK"} break
  }
  set rc
}

# Same as [step_ota], except using an external state database - "state.db"
#
proc step_ota_state {target ota} {
  while 1 {
    sqlite3ota ota $target $ota state.db
    set rc [ota step]
    ota close
    if {$rc != "SQLITE_OK"} break
  }
  set rc
}

proc dbfilecksum {file} {
  sqlite3 ck $file
  set cksum [dbcksum ck main]
  ck close
  set cksum
}

foreach {tn3 create_vfs destroy_vfs} {
  1 {} {}
  2 {
    sqlite3ota_create_vfs -default myota ""
  } {
    sqlite3ota_destroy_vfs myota
  }
} {

  eval $create_vfs

  foreach {tn2 cmd} {
      1 run_ota 
      2 step_ota 3 step_ota_uri 4 step_ota_state
  } {
    foreach {tn schema} {
      1 {
        CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c);
      }
      2 { 
        CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c);
        CREATE INDEX i1 ON t1(b);

      16 { 
        CREATE TABLE t1(a, b, c, PRIMARY KEY(c DESC, a)) WITHOUT ROWID;
        CREATE INDEX i1 ON t1(b DESC, c, a);
      }
    } {
      reset_db
      execsql $schema
      create_ota1 ota.db
      set check [dbfilecksum ota.db]
      forcedelete state.db

      do_test $tn3.1.$tn2.$tn.1 {

        $cmd test.db ota.db
      } {SQLITE_DONE}

      do_execsql_test $tn3.1.$tn2.$tn.2 { SELECT * FROM t1 ORDER BY a ASC } {
        1 2 3 
        2 two three 
        3 {} 8.2
      }
      do_execsql_test $tn3.1.$tn2.$tn.3 { SELECT * FROM t1 ORDER BY b ASC } {
        3 {} 8.2
        1 2 3 
        2 two three 
      }
      do_execsql_test $tn3.1.$tn2.$tn.4 { SELECT * FROM t1 ORDER BY c ASC } {
        1 2 3 
        3 {} 8.2
        2 two three 
      }
   
      do_execsql_test $tn3.1.$tn2.$tn.5 { PRAGMA integrity_check } ok

      if {$cmd=="step_ota_state"} {
        do_test $tn3.1.$tn2.$tn.6 { file exists state.db } 1
        do_test $tn3.1.$tn2.$tn.7 { expr {$check == [dbfilecksum ota.db]} } 1
      } else {
        do_test $tn3.1.$tn2.$tn.8 { file exists state.db } 0
        do_test $tn3.1.$tn2.$tn.9 { expr {$check == [dbfilecksum ota.db]} } 0
      }
    }
  }

  #-------------------------------------------------------------------------
  # Check that an OTA cannot be applied to a table that has no PK.
  #
  # UPDATE: At one point OTA required that all tables featured either

    } [list 1 "$errcode - $errmsg"]

    do_test $tn3.3.$tn.4 { dbcksum db main } $cksum
  }

  #-------------------------------------------------------------------------
  #
  foreach {tn2 cmd} {1 run_ota 2 step_ota 3 step_ota_state } {
    foreach {tn schema} {
      1 {
        CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c);
      }
      2 {
        CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c);
        CREATE INDEX i1 ON t1(b);

      execsql $schema
      execsql {
        INSERT INTO t1 VALUES(2, 'hello', 'world');
        INSERT INTO t1 VALUES(4, 'hello', 'planet');
        INSERT INTO t1 VALUES(6, 'hello', 'xyz');
      }
    

        create_ota4 ota.db
      set check [dbfilecksum ota.db]
      forcedelete state.db
    
      do_test $tn3.4.$tn2.$tn.1 {
        $cmd test.db ota.db
      } {SQLITE_DONE}
      
      do_execsql_test $tn3.4.$tn2.$tn.2 {
        SELECT * FROM t1 ORDER BY a ASC;
      } {
        1 2 3 
        3 8 9
        6 hello xyz
      }
    
      do_execsql_test $tn3.4.$tn2.$tn.3 { PRAGMA integrity_check } ok

      if {$cmd=="step_ota_state"} {
        do_test $tn3.4.$tn2.$tn.4 { file exists state.db } 1
        do_test $tn3.4.$tn2.$tn.5 { expr {$check == [dbfilecksum ota.db]} } 1
      } else {
        do_test $tn3.4.$tn2.$tn.6 { file exists state.db } 0
        do_test $tn3.4.$tn2.$tn.7 { expr {$check == [dbfilecksum ota.db]} } 0
      }
    }
  }

  foreach {tn2 cmd} {1 run_ota 2 step_ota 3 step_ota_state} {
    foreach {tn schema} {
      1 {
        CREATE TABLE t1(c, b, '(a)' INTEGER PRIMARY KEY);
        CREATE INDEX i1 ON t1(c, b);
      }
      2 {
        CREATE TABLE t1(c, b, '(a)' PRIMARY KEY);
      }
      3 {
        CREATE TABLE t1(c, b, '(a)' PRIMARY KEY) WITHOUT ROWID;
      }
    } {
      reset_db
      execsql $schema
      execsql {
        INSERT INTO t1('(a)', b, c) VALUES(2, 'hello', 'world');
        INSERT INTO t1('(a)', b, c) VALUES(4, 'hello', 'planet');
        INSERT INTO t1('(a)', b, c) VALUES(6, 'hello', 'xyz');
      }
    

        create_ota4b ota.db
      set check [dbfilecksum ota.db]
      forcedelete state.db
    
      do_test $tn3.5.$tn2.$tn.1 {
        $cmd test.db ota.db
      } {SQLITE_DONE}
      
      do_execsql_test $tn3.5.$tn2.$tn.2 {
        SELECT * FROM t1 ORDER BY "(a)" ASC;
      } {
        3 2 1
        9 8 3
        xyz hello 6
      }
    
      do_execsql_test $tn3.4.$tn2.$tn.3 { PRAGMA integrity_check } ok

      if {$cmd=="step_ota_state"} {
        do_test $tn3.5.$tn2.$tn.4 { file exists state.db } 1
        do_test $tn3.5.$tn2.$tn.5 { expr {$check == [dbfilecksum ota.db]} } 1
      } else {
        do_test $tn3.5.$tn2.$tn.6 { file exists state.db } 0
        do_test $tn3.5.$tn2.$tn.7 { expr {$check == [dbfilecksum ota.db]} } 0
      }
    }
  }

  #-------------------------------------------------------------------------
  #
  foreach {tn2 cmd} {1 run_ota 2 step_ota 3 step_ota_state} {
    foreach {tn schema} {
      1 {
        CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c, d);
      }
      2 {
        CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c, d);
        CREATE INDEX i1 ON t1(d);

      execsql $schema
      execsql {
        INSERT INTO t1 VALUES(1, 2, 3, 4);
        INSERT INTO t1 VALUES(2, 5, 6, 7);
        INSERT INTO t1 VALUES(3, 8, 9, 10);
      }
    

        create_ota5 ota.db
      set check [dbfilecksum ota.db]
      forcedelete state.db

      do_test $tn3.5.$tn2.$tn.1 {
        $cmd test.db ota.db
      } {SQLITE_DONE}
      
      do_execsql_test $tn3.5.$tn2.$tn.2 {
        SELECT * FROM t1 ORDER BY a ASC;
      } {
        1 2 3 5
        2 5 10 5
        3 11 9 10
      }
    
      do_execsql_test $tn3.6.$tn2.$tn.3 { PRAGMA integrity_check } ok

      if {$cmd=="step_ota_state"} {
        do_test $tn3.6.$tn2.$tn.4 { file exists state.db } 1
        do_test $tn3.6.$tn2.$tn.5 { expr {$check == [dbfilecksum ota.db]} } 1
      } else {
        do_test $tn3.6.$tn2.$tn.6 { file exists state.db } 0
        do_test $tn3.6.$tn2.$tn.7 { expr {$check == [dbfilecksum ota.db]} } 0
      }
    }
  }

  #-------------------------------------------------------------------------
  # Test some error cases:
  # 
  #   * A virtual table with no ota_rowid column.

    } {
      reset_db
      forcedelete ota.db
      execsql { ATTACH 'ota.db' AS ota }
      execsql $schema

      do_test $tn3.7.$tn {
        list [catch { run_ota test.db ota.db } msg] $msg
      } [list 1 $error]
    }
  }

  # Test that an OTA database containing no input tables is handled
  # correctly.
  reset_db
  forcedelete ota.db
  do_test $tn3.8 {
    list [catch { run_ota test.db ota.db } msg] $msg
  } {0 SQLITE_DONE}
  
  # Test that OTA can update indexes containing NULL values.
  #
  reset_db
  forcedelete ota.db
  do_execsql_test $tn3.9.1 {
    CREATE TABLE t1(a PRIMARY KEY, b, c);
    CREATE INDEX i1 ON t1(b, c);
    INSERT INTO t1 VALUES(1, 1, NULL);
    INSERT INTO t1 VALUES(2, NULL, 2);
    INSERT INTO t1 VALUES(3, NULL, NULL);

    ATTACH 'ota.db' AS ota;
    CREATE TABLE ota.data_t1(a, b, c, ota_control);
    INSERT INTO data_t1 VALUES(1, NULL, NULL, 1);
    INSERT INTO data_t1 VALUES(3, NULL, NULL, 1);
  } {}

  do_test $tn3.9.2 {
    list [catch { run_ota test.db ota.db } msg] $msg
  } {0 SQLITE_DONE}

  do_execsql_test $tn3.9.3 {
    SELECT * FROM t1
  } {2 {} 2}
  do_execsql_test $tn3.9.4 { PRAGMA integrity_check } {ok}

  catch { db close }
  eval $destroy_vfs
}


finish_test

#define OTA_STAGE_OAL         1
#define OTA_STAGE_MOVE        2
#define OTA_STAGE_CAPTURE     3
#define OTA_STAGE_CKPT        4
#define OTA_STAGE_DONE        5


#define OTA_CREATE_STATE \
  "CREATE TABLE IF NOT EXISTS %s.ota_state(k INTEGER PRIMARY KEY, v)"

typedef struct OtaFrame OtaFrame;
typedef struct OtaObjIter OtaObjIter;
typedef struct OtaState OtaState;
typedef struct ota_vfs ota_vfs;
typedef struct ota_file ota_file;
typedef struct OtaUpdateStmt OtaUpdateStmt;

*/
struct sqlite3ota {
  int eStage;                     /* Value of OTA_STATE_STAGE field */
  sqlite3 *dbMain;                /* target database handle */
  sqlite3 *dbOta;                 /* ota database handle */
  char *zTarget;                  /* Path to target db */
  char *zOta;                     /* Path to ota db */
  char *zState;                   /* Path to state db (or NULL if zOta) */
  char zStateDb[5];               /* Db name for state ("stat" or "main") */
  int rc;                         /* Value returned by last ota_step() call */
  char *zErrmsg;                  /* Error message if rc!=SQLITE_OK */
  int nStep;                      /* Rows processed for current object */
  int nProgress;                  /* Rows processed for all objects */
  OtaObjIter objiter;             /* Iterator for skipping through tbl/idx */
  const char *zVfsName;           /* Name of automatically created ota vfs */
  ota_file *pTargetFd;            /* File handle open on target db */

){
  int bOtaRowid = (pIter->eType==OTA_PK_EXTERNAL || pIter->eType==OTA_PK_NONE);
  char *zBind = otaObjIterGetBindlist(p, pIter->nTblCol + 1 + bOtaRowid);
  if( zBind ){
    assert( pIter->pTmpInsert==0 );
    p->rc = prepareFreeAndCollectError(
        p->dbOta, &pIter->pTmpInsert, &p->zErrmsg, sqlite3_mprintf(
          "INSERT INTO %s.'ota_tmp_%q'(ota_control,%s%s) VALUES(%z)", 
          p->zStateDb, pIter->zTbl, zCollist, zOtaRowid, zBind
    ));
  }
}

static void otaTmpInsertFunc(
  sqlite3_context *pCtx, 
  int nVal,

      }

      /* Create the SELECT statement to read keys in sorted order */
      if( p->rc==SQLITE_OK ){
        char *zSql;
        if( pIter->eType==OTA_PK_EXTERNAL || pIter->eType==OTA_PK_NONE ){
          zSql = sqlite3_mprintf(
              "SELECT %s, ota_control FROM %s.'ota_tmp_%q' ORDER BY %s%s",
              zCollist, p->zStateDb, pIter->zTbl,
              zCollist, zLimit
          );
        }else{
          zSql = sqlite3_mprintf(
              "SELECT %s, ota_control FROM 'data_%q' "
              "WHERE typeof(ota_control)='integer' AND ota_control!=1 "
              "UNION ALL "
              "SELECT %s, ota_control FROM %s.'ota_tmp_%q' "
              "ORDER BY %s%s",
              zCollist, pIter->zTbl, 
              zCollist, p->zStateDb, pIter->zTbl, 
              zCollist, zLimit
          );
        }
        p->rc = prepareFreeAndCollectError(p->dbOta, &pIter->pSelect, pz, zSql);
      }

      sqlite3_free(zImposterCols);

        const char *zOtaRowid = "";
        if( pIter->eType==OTA_PK_EXTERNAL || pIter->eType==OTA_PK_NONE ){
          zOtaRowid = ", ota_rowid";
        }

        /* Create the ota_tmp_xxx table and the triggers to populate it. */
        otaMPrintfExec(p, p->dbOta,
            "CREATE TABLE IF NOT EXISTS %s.'ota_tmp_%q' AS "
            "SELECT *%s FROM 'data_%q' WHERE 0;"
            , p->zStateDb
            , zTbl, (pIter->eType==OTA_PK_EXTERNAL ? ", 0 AS ota_rowid" : "")
            , zTbl
        );

        otaMPrintfExec(p, p->dbMain,
            "CREATE TEMP TRIGGER ota_delete_tr BEFORE DELETE ON \"%s%w\" "
            "BEGIN "

static void otaOpenDatabase(sqlite3ota *p){
  assert( p->rc==SQLITE_OK );
  assert( p->dbMain==0 && p->dbOta==0 );

  p->eStage = 0;
  p->dbMain = otaOpenDbhandle(p, p->zTarget);
  p->dbOta = otaOpenDbhandle(p, p->zOta);

  /* If using separate OTA and state databases, attach the state database to
  ** the OTA db handle now.  */
  if( p->zState ){
    otaMPrintfExec(p, p->dbOta, "ATTACH %Q AS stat", p->zState);
    memcpy(p->zStateDb, "stat", 4);
  }else{
    memcpy(p->zStateDb, "main", 4);
  }

  if( p->rc==SQLITE_OK ){
    p->rc = sqlite3_create_function(p->dbMain, 
        "ota_tmp_insert", -1, SQLITE_UTF8, (void*)p, otaTmpInsertFunc, 0, 0
    );
  }

  if( p->rc==SQLITE_OK || p->rc==SQLITE_DONE ){
    sqlite3_stmt *pInsert = 0;
    int rc;

    assert( p->zErrmsg==0 );
    rc = prepareFreeAndCollectError(p->dbOta, &pInsert, &p->zErrmsg, 
        sqlite3_mprintf(
          "INSERT OR REPLACE INTO %s.ota_state(k, v) VALUES "
          "(%d, %d), "
          "(%d, %Q), "
          "(%d, %Q), "
          "(%d, %d), "
          "(%d, %d), "
          "(%d, %lld), "
          "(%d, %lld), "
          "(%d, %lld) ",
          p->zStateDb,
          OTA_STATE_STAGE, eStage,
          OTA_STATE_TBL, p->objiter.zTbl, 
          OTA_STATE_IDX, p->objiter.zIdx, 
          OTA_STATE_ROW, p->nStep, 
          OTA_STATE_PROGRESS, p->nProgress,
          OTA_STATE_CKPT, p->iWalCksum,
          OTA_STATE_COOKIE, (i64)p->pTargetFd->iCookie,

        while( p->rc==SQLITE_OK && pIter->zTbl ){

          if( pIter->bCleanup ){
            /* Clean up the ota_tmp_xxx table for the previous table. It 
            ** cannot be dropped as there are currently active SQL statements.
            ** But the contents can be deleted.  */
            if( pIter->abIndexed ){

              otaMPrintfExec(p, p->dbOta, 
                  "DELETE FROM %s.'ota_tmp_%q'", p->zStateDb, pIter->zTbl
              );
            }
          }else{
            otaObjIterPrepareAll(p, pIter, 0);

            /* Advance to the next row to process. */
            if( p->rc==SQLITE_OK ){
              int rc = sqlite3_step(pIter->pSelect);

** responsibility of the caller to eventually free the object using
** sqlite3_free().
**
** If an error occurs, leave an error code and message in the ota handle
** and return NULL.
*/
static OtaState *otaLoadState(sqlite3ota *p){

  OtaState *pRet = 0;
  sqlite3_stmt *pStmt = 0;
  int rc;
  int rc2;

  pRet = (OtaState*)otaMalloc(p, sizeof(OtaState));
  if( pRet==0 ) return 0;

  rc = prepareFreeAndCollectError(p->dbOta, &pStmt, &p->zErrmsg, 
      sqlite3_mprintf("SELECT k, v FROM %s.ota_state", p->zStateDb)
  );
  while( rc==SQLITE_OK && SQLITE_ROW==sqlite3_step(pStmt) ){
    switch( sqlite3_column_int(pStmt, 0) ){
      case OTA_STATE_STAGE:
        pRet->eStage = sqlite3_column_int(pStmt, 1);
        if( pRet->eStage!=OTA_STAGE_OAL
         && pRet->eStage!=OTA_STAGE_MOVE
         && pRet->eStage!=OTA_STAGE_CKPT

static void otaDeleteVfs(sqlite3ota *p){
  if( p->zVfsName ){
    sqlite3ota_destroy_vfs(p->zVfsName);
    p->zVfsName = 0;
  }
}


static sqlite3ota *otaOpen(

  const char *zTarget, 
  const char *zOta,
  const char *zState
){
  sqlite3ota *p;
  int nTarget = strlen(zTarget);
  int nOta = strlen(zOta);
  int nState = zState ? strlen(zState) : 0;

  p = (sqlite3ota*)sqlite3_malloc(sizeof(sqlite3ota)+nTarget+1+nOta+1+nState+1);
  if( p ){
    OtaState *pState = 0;

    /* Create the custom VFS. */
    memset(p, 0, sizeof(sqlite3ota));
    otaCreateVfs(p);

    /* Open the target database */
    if( p->rc==SQLITE_OK ){
      p->zTarget = (char*)&p[1];
      memcpy(p->zTarget, zTarget, nTarget+1);
      p->zOta = &p->zTarget[nTarget+1];
      memcpy(p->zOta, zOta, nOta+1);
      if( zState ){
        p->zState = &p->zOta[nOta+1];
        memcpy(p->zState, zState, nState+1);
      }
      otaOpenDatabase(p);
    }

    /* If it has not already been created, create the ota_state table */

    otaMPrintfExec(p, p->dbOta, OTA_CREATE_STATE, p->zStateDb);


    if( p->rc==SQLITE_OK ){
      pState = otaLoadState(p);
      assert( pState || p->rc!=SQLITE_OK );
      if( p->rc==SQLITE_OK ){

        if( pState->eStage==0 ){ 

    otaFreeState(pState);
  }

  return p;
}


/*
** Open and return a new OTA handle. 
*/
sqlite3ota *sqlite3ota_open_v2(
  const char *zDb, 
  const char *zOta, 
  const char *zState
){
  return otaOpen(zDb, zOta, zState);
}

/*
** Open and return a new OTA handle. 
*/
sqlite3ota *sqlite3ota_open(
  const char *zDb, 
  const char *zOta 
){
  return otaOpen(zDb, zOta, 0);
}

/*
** Return the database handle used by pOta.
*/
sqlite3 *sqlite3ota_db(sqlite3ota *pOta, int bOta){
  sqlite3 *db = 0;
  if( pOta ){
    db = (bOta ? pOta->dbOta : pOta->dbMain);

**
** IMPORTANT NOTE FOR ZIPVFS USERS: The OTA extension works with all of
** SQLite's built-in VFSs, including the multiplexor VFS. However it does
** not work out of the box with zipvfs. Refer to the comment describing
** the zipvfs_create_vfs() API below for details on using OTA with zipvfs.
*/
sqlite3ota *sqlite3ota_open(const char *zTarget, const char *zOta);

/*
** Open an OTA handle with an auxiliary state file.
**
** This API is similar to sqlite3ota_open(), except that it allows the user 
** to specify a separate SQLite database in which to store the OTA update 
** state.
**
** While executing, the OTA extension usually stores the current state 
** of the update (how many rows have been updated, which indexes are yet
** to be updated etc.) within the OTA database itself. This can be 
** convenient, as it means that the OTA application does not need to
** organize removing a separate state file after the update is concluded.
** However, it can also be inconvenient - for example if the OTA update
** database is sto be stored on a read-only media.
**
** If an OTA update started using a handle opened with this function is
** suspended, the application must use this function to resume it, and 
** must pass the same zState argument each time the update is resumed.
** Attempting to resume an sqlite3ota_open_v2() update using sqlite3ota_open(),
** or with a call to sqlite3ota_open_v2() specifying a different zState
** argument leads to undefined behaviour.
**
** Once the OTA update is finished, the OTA extension does not 
** automatically remove the zState database file, even if it created it.
*/
sqlite3ota *sqlite3ota_open_v2(
  const char *zTarget,
  const char *zOta,
  const char *zState
);

/*
** Internally, each OTA connection uses a separate SQLite database 
** connection to access the target and ota update databases. This
** API allows the application direct access to these database handles.
**
** The first argument passed to this function must be a valid, open, OTA

      break;
  }

  return ret;
}

/*
** Tclcmd: sqlite3ota CMD <target-db> <ota-db> ?<state-db>?
*/
static int test_sqlite3ota(
  ClientData clientData,
  Tcl_Interp *interp,
  int objc,
  Tcl_Obj *CONST objv[]
){
  sqlite3ota *pOta = 0;
  const char *zCmd;
  const char *zTarget;
  const char *zOta;

  if( objc!=4 && objc!=5 ){
    Tcl_WrongNumArgs(interp, 1, objv, "NAME TARGET-DB OTA-DB ?STATE-DB?");
    return TCL_ERROR;
  }
  zCmd = Tcl_GetString(objv[1]);
  zTarget = Tcl_GetString(objv[2]);
  zOta = Tcl_GetString(objv[3]);

  if( objc==4 ){
  pOta = sqlite3ota_open(zTarget, zOta);
  }else{
    const char *zStateDb = Tcl_GetString(objv[4]);
    pOta = sqlite3ota_open_v2(zTarget, zOta, zStateDb);
  }
  Tcl_CreateObjCommand(interp, zCmd, test_sqlite3ota_cmd, (ClientData)pOta, 0);
  Tcl_SetObjResult(interp, objv[1]);
  return TCL_OK;
}

/*
** Tclcmd: sqlite3ota_create_vfs ?-default? NAME PARENT