SQLite

    INSERT INTO data_t1 VALUES(2, 'two', 'three', 0);
    INSERT INTO data_t1 VALUES(3, NULL, 8.2, 0);
  }
  ota1 close
  return $filename
}

# Create a simple OTA database. That expects to write to a table:

#
#   CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c);
#
proc create_ota4 {filename} {
  forcedelete $filename
  sqlite3 ota1 $filename  
  ota1 eval {
    CREATE TABLE data_t1(a, b, c, ota_control);
    INSERT INTO data_t1 VALUES(1, 2, 3, 0);
    INSERT INTO data_t1 VALUES(2, NULL, 5, 1);
    INSERT INTO data_t1 VALUES(3, 8, 9, 0);
    INSERT INTO data_t1 VALUES(4, NULL, 11, 1);
  }
  ota1 close
  return $filename
}

# Run the OTA in file $ota on target database $target until completion.
#
proc run_ota {target ota} {
  sqlite3ota ota $target $ota

  do_test 3.$tn.3 { 
    list [catch { ota close } msg] $msg
  } [list 1 "$errcode - $errmsg"]

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

#-------------------------------------------------------------------------
#
foreach {tn2 cmd} {1 run_ota 2 step_ota} {
  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);
    }
    3 {
      CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c);
      CREATE INDEX i1 ON t1(b);
      CREATE INDEX i2 ON t1(c, b);
      CREATE INDEX i3 ON t1(c, b, c);
    }
  } {
    reset_db
    execsql $schema
    execsql {
      INSERT INTO t1 VALUES(2, 'hello', 'world');
      INSERT INTO t1 VALUES(4, 'hello', 'planet');
      INSERT INTO t1 VALUES(6, 'hello', 'xyz');
    }
  
    do_test 4.$tn2.$tn.1 {
      create_ota4 ota.db
      $cmd test.db ota.db
    } {SQLITE_DONE}
    
    do_execsql_test 4.$tn2.$tn.2 {
      SELECT * FROM t1 ORDER BY a ASC;
    } {
      1 2 3 
      3 8 9
      6 hello xyz
    }
  
    do_execsql_test 4.$tn2.$tn.3 { PRAGMA integrity_check } ok
  }
}


finish_test

  const char *zIdx;               /* Name of target db index (or null) */
  int iVisit;                     /* Number of points visited, incl. current */

  /* Statements created by otaObjIterPrepareAll() */
  int nCol;                       /* Number of columns in current object */
  sqlite3_stmt *pSelect;          /* Source data */
  sqlite3_stmt *pInsert;          /* Statement for INSERT operations */
  sqlite3_stmt *pDelete;          /* Statement for DELETE ops */
};

/*
** OTA handle.
*/
struct sqlite3ota {
  sqlite3 *db;                    /* "main" -> target db, "ota" -> ota db */

** as the only argument.
*/
static void otaObjIterFinalize(OtaObjIter *pIter){
  sqlite3_finalize(pIter->pTblIter);
  sqlite3_finalize(pIter->pIdxIter);
  sqlite3_finalize(pIter->pSelect);
  sqlite3_finalize(pIter->pInsert);
  sqlite3_finalize(pIter->pDelete);
  otaObjIterFreeCols(pIter);
  memset(pIter, 0, sizeof(OtaObjIter));
}

/*
** Advance the iterator to the next position.
**
** If no error occurs, SQLITE_OK is returned and the iterator is left 
** pointing to the next entry. Otherwise, an error code and message is 
** left in the OTA handle passed as the first argument. A copy of the 
** error code is returned.
*/
static int otaObjIterNext(sqlite3ota *p, OtaObjIter *pIter){
  int rc = p->rc;
  if( rc==SQLITE_OK ){

    /* Free any SQLite statements used while processing the previous object */ 
    sqlite3_finalize(pIter->pSelect);
    sqlite3_finalize(pIter->pInsert);
    sqlite3_finalize(pIter->pDelete);
    pIter->pSelect = 0;
    pIter->pInsert = 0;
    pIter->pDelete = 0;
    pIter->nCol = 0;

    if( pIter->bCleanup ){
      otaObjIterFreeCols(pIter);
      pIter->bCleanup = 0;
      rc = sqlite3_step(pIter->pTblIter);
      if( rc!=SQLITE_ROW ){

      *p++ = zName[i];
    }
    *p++ = '"';
    *p++ = '\0';
  }
  return zRet;
}

/*
** Argument zFmt is a sqlite3_mprintf() style format string. The trailing
** arguments are the usual subsitution values. This function performs
** the printf() style substitutions and executes the result as an SQL
** statement on the OTA handles database.
**
** If an error occurs, an error code and error message is stored in the
** OTA handle. If an error has already occurred when this function is
** called, it is a no-op.
*/
static int otaMPrintfExec(sqlite3ota *p, const char *zFmt, ...){
  va_list ap;
  va_start(ap, zFmt);
  if( p->rc==SQLITE_OK ){
    char *zSql = sqlite3_vmprintf(zFmt, ap);
    if( zSql==0 ){
      p->rc = SQLITE_NOMEM;
    }else{
      p->rc = sqlite3_exec(p->db, zSql, 0, 0, &p->zErrmsg);
      sqlite3_free(zSql);
    }
  }
  va_end(ap);
  return p->rc;
}

/*
** If they are not already populated, populate the pIter->azTblCol[],
** pIter->abTblPk[] and pIter->nTblCol variables according to the table 
** that the iterator currently points to.
**
** Return SQLITE_OK if successful, or an SQLite error code otherwise. If

        p->rc = SQLITE_NOMEM;
        break;
      }
    }
  }
  return zList;
}

static char *otaObjIterGetOldlist(
  sqlite3ota *p, 
  OtaObjIter *pIter
){
  char *zList = 0;
  if( p->rc==SQLITE_OK ){
    const char *zSep = "";
    int i;
    for(i=0; i<pIter->nTblCol; i++){
      zList = sqlite3_mprintf("%z%sold.%s", zList, zSep, pIter->azTblCol[i]);
      zSep = ", ";
      if( zList==0 ){
        p->rc = SQLITE_NOMEM;
        break;
      }
    }
  }
  return zList;
}

static char *otaObjIterGetWhere(
  sqlite3ota *p, 
  OtaObjIter *pIter
){
  char *zList = 0;
  if( p->rc==SQLITE_OK ){
    const char *zSep = "";
    int i;
    for(i=0; i<pIter->nTblCol; i++){
      if( pIter->abTblPk[i] ){
        zList = sqlite3_mprintf("%z%s%s=?", zList, zSep, pIter->azTblCol[i]);
        zSep = " AND ";
        if( zList==0 ){
          p->rc = SQLITE_NOMEM;
          break;
        }
      }
    }
  }
  return zList;
}

static char *otaObjIterGetBindlist(sqlite3ota *p, int nBind){
  char *zRet = 0;
  if( p->rc==SQLITE_OK ){
    int nByte = nBind*2 + 1;
    zRet = sqlite3_malloc(nByte);
    if( zRet==0 ){

      zLimit = sqlite3_mprintf(" LIMIT -1 OFFSET %d", nOffset);
      if( !zLimit ) p->rc = SQLITE_NOMEM;
    }

    if( zIdx ){
      int *aiCol;                 /* Column map */

      /* Create the index writers */
      if( p->rc==SQLITE_OK ){
        p->rc = sqlite3_index_writer(
            p->db, 0, zIdx, &pIter->pInsert, &aiCol, &pIter->nCol
        );
      }
      if( p->rc==SQLITE_OK ){
        p->rc = sqlite3_index_writer(
            p->db, 1, zIdx, &pIter->pDelete, &aiCol, &pIter->nCol
        );
      }

      /* Create the SELECT statement to read keys in sorted order */
      zCollist = otaObjIterGetCollist(p, pIter, pIter->nCol, aiCol);
      if( p->rc==SQLITE_OK ){
        p->rc = prepareFreeAndCollectError(p->db, &pIter->pSelect, pz,
            sqlite3_mprintf(
              "SELECT %s, ota_control FROM ota.'data_%q' "
                "UNION ALL "
              "SELECT %s, ota_control FROM ota.'ota_tmp_%q' "
              "ORDER BY %s%s",
              zCollist, pIter->zTbl, 
              zCollist, pIter->zTbl, 
              zCollist, zLimit
            )
        );
      }
    }else{
      char *zBindings = otaObjIterGetBindlist(p, pIter->nTblCol);
      char *zWhere = otaObjIterGetWhere(p, pIter);
      char *zOldlist = otaObjIterGetOldlist(p, pIter);
      zCollist = otaObjIterGetCollist(p, pIter, pIter->nTblCol, 0);
      pIter->nCol = pIter->nTblCol;

      /* Create the SELECT statement to read keys from data_xxx */
      if( p->rc==SQLITE_OK ){
        p->rc = prepareFreeAndCollectError(p->db, &pIter->pSelect, pz,
            sqlite3_mprintf(
              "SELECT %s, ota_control FROM ota.'data_%q'%s", 
              zCollist, pIter->zTbl, zLimit)
        );
      }

      /* Create the INSERT statement to write to the target PK b-tree */
      if( p->rc==SQLITE_OK ){
        p->rc = prepareFreeAndCollectError(p->db, &pIter->pInsert, pz,
            sqlite3_mprintf(
              "INSERT INTO main.%Q(%s) VALUES(%s)", 
              pIter->zTbl, zCollist, zBindings
            )
        );
      }

      /* Create the DELETE statement to write to the target PK b-tree */
      if( p->rc==SQLITE_OK ){
        p->rc = prepareFreeAndCollectError(p->db, &pIter->pDelete, pz,
            sqlite3_mprintf(
              "DELETE FROM main.%Q WHERE %s", pIter->zTbl, zWhere
            )
        );
      }

      if( p->rc==SQLITE_OK ){
        otaMPrintfExec(p, 
            "CREATE TABLE IF NOT EXISTS ota.'ota_tmp_%q' AS "
            "SELECT * FROM ota.'data_%q' WHERE 0;"
            "CREATE TEMP TRIGGER ota_delete_%q BEFORE DELETE ON main.%Q "
            "BEGIN "
            "  INSERT INTO 'ota_tmp_%q'(ota_control, %s) VALUES(2, %s);"
            "END;"
            , pIter->zTbl, pIter->zTbl, pIter->zTbl, pIter->zTbl, pIter->zTbl,
            zCollist, zOldlist
        );
      }

      sqlite3_free(zWhere);
      sqlite3_free(zOldlist);
      sqlite3_free(zBindings);
    }
    sqlite3_free(zCollist);
    sqlite3_free(zLimit);
  }
  
  return p->rc;
}

#define OTA_INSERT     1
#define OTA_DELETE     2
#define OTA_IDX_DELETE 3
#define OTA_UPDATE     4

/*
** The SELECT statement iterating through the keys for the current object
** (p->objiter.pSelect) currently points to a valid row. However, there
** is something wrong with the ota_control value in the ota_control value
** stored in the (p->nCol+1)'th column. Set the error code and error message
** of the OTA handle to something reflecting this.
*/
static void otaBadControlError(sqlite3ota *p){
  p->rc = SQLITE_ERROR;
  p->zErrmsg = sqlite3_mprintf("Invalid ota_control value");
}

/*
** The SELECT statement iterating through the keys for the current object
** (p->objiter.pSelect) currently points to a valid row. This function
** determines the type of operation requested by this row and returns
** one of the following values to indicate the result:
**
**     * OTA_INSERT
**     * OTA_DELETE
**     * OTA_IDX_DELETE
**     * OTA_UPDATE
**
** If OTA_UPDATE is returned, then output variable *pzMask is set to
** point to the text value indicating the columns to update.
**
** If the ota_control field contains an invalid value, an error code and
** message are left in the OTA handle and zero returned.
*/
static int otaStepType(sqlite3ota *p, const char **pzMask){
  int iCol = p->objiter.nCol;     /* Index of ota_control column */
  int res = 0;                    /* Return value */

  switch( sqlite3_column_type(p->objiter.pSelect, iCol) ){
    case SQLITE_INTEGER: {
      int iVal = sqlite3_column_int(p->objiter.pSelect, iCol);
      if( iVal==0 ){
        res = OTA_INSERT;
      }else if( iVal==1 ){
        res = OTA_DELETE;
      }else if( iVal==2 ){
        res = OTA_IDX_DELETE;
      }
      break;
    }

    case SQLITE_TEXT:
      *pzMask = (const char*)sqlite3_column_text(p->objiter.pSelect, iCol);
      res = OTA_UPDATE;
      break;

    default:
      break;
  }

  if( res==0 ){
    otaBadControlError(p);
  }
  return res;
}

/*
** This function does the work for an sqlite3ota_step() call.
**
** The object-iterator (p->objiter) currently points to a valid object,
** and the input cursor (p->objiter.pSelect) currently points to a valid
** input row. Perform whatever processing is required and return.
**
** If no  error occurs, SQLITE_OK is returned. Otherwise, an error code
** and message is left in the OTA handle and a copy of the error code
** returned.
*/
static int otaStep(sqlite3ota *p){
  OtaObjIter *pIter = &p->objiter;
  const char *zMask = 0;
  int i;
  int eType = otaStepType(p, &zMask);

  if( eType ){
    assert( eType!=OTA_UPDATE || pIter->zIdx==0 );

    if( pIter->zIdx==0 && eType==OTA_IDX_DELETE ){
      otaBadControlError(p);
    }
    else if( eType==OTA_INSERT || eType==OTA_IDX_DELETE ){
      sqlite3_stmt *pWriter;
      assert( eType!=OTA_UPDATE );

      pWriter = (eType==OTA_INSERT)?pIter->pInsert:pIter->pDelete;
      for(i=0; i<pIter->nCol; i++){
        sqlite3_value *pVal = sqlite3_column_value(pIter->pSelect, i);
        sqlite3_bind_value(pWriter, i+1, pVal);
      }
      sqlite3_step(pWriter);
      p->rc = resetAndCollectError(pWriter, &p->zErrmsg);
    }
    else if( eType==OTA_DELETE && pIter->zIdx==0 ){
      int iVar = 1;
      assert( pIter->zIdx==0 );
      assert( pIter->nCol==pIter->nTblCol );
      for(i=0; i<pIter->nCol; i++){
        if( pIter->abTblPk[i] ){
          sqlite3_value *pVal = sqlite3_column_value(pIter->pSelect, i);
          sqlite3_bind_value(pIter->pDelete, iVar++, pVal);
        }
      }
      sqlite3_step(pIter->pDelete);
      p->rc = resetAndCollectError(pIter->pDelete, &p->zErrmsg);
    }else if( eType==OTA_UPDATE ){
      p->rc = SQLITE_ERROR;
      p->zErrmsg = sqlite3_mprintf("not yet");
    }else{
      /* no-op */
      assert( eType==OTA_DELETE && pIter->zIdx );
    }
  }

  return p->rc;
}

/*
** Step the OTA object.
*/
int sqlite3ota_step(sqlite3ota *p){
  if( p ){
    OtaObjIter *pIter = &p->objiter;
    while( p && 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.  */
        otaMPrintfExec(p, "DELETE FROM ota.'ota_tmp_%q'", 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);
          if( rc==SQLITE_ROW ){

      otaObjIterNext(p, pIter);
    }

    if( p->rc==SQLITE_OK && pIter->zTbl==0 ){
      p->rc = SQLITE_DONE;
    }
  }


























  return p->rc;
}

static void otaSaveTransactionState(sqlite3ota *p){
  otaMPrintfExec(p, 
    "INSERT OR REPLACE INTO ota.ota_state(rowid, tbl, idx, row, progress)"
    "VALUES(1, %Q, %Q, %d, NULL)",

  int r1 = -1;       /* Register holding an index key */
  int iPartIdxLabel; /* Jump destination for skipping partial index entries */
  Index *pIdx;       /* Current index */
  Index *pPrior = 0; /* Prior index */
  Vdbe *v;           /* The prepared statement under construction */
  Index *pPk;        /* PRIMARY KEY index, or NULL for rowid tables */

  /* Skip this if we are in OTA mode */
  if( pParse->db->flags & SQLITE_OtaMode ) return;

  v = pParse->pVdbe;
  pPk = HasRowid(pTab) ? 0 : sqlite3PrimaryKeyIndex(pTab);
  for(i=0, pIdx=pTab->pIndex; pIdx; i++, pIdx=pIdx->pNext){
    assert( iIdxCur+i!=iDataCur || pPk==pIdx );
    if( aRegIdx!=0 && aRegIdx[i]==0 ) continue;
    if( pIdx==pPk ) continue;
    VdbeModuleComment((v, "GenRowIdxDel for %s", pIdx->zName));

  /* Create the record to insert into the index. Store it in register regRec. */
  pParse->nVar = pIdx->nColumn;
  pParse->nMem = pIdx->nColumn;
  for(i=1; i<=pIdx->nColumn; i++){
    sqlite3VdbeAddOp2(v, OP_Variable, i, i);
  }
  regRec = ++pParse->nMem;

  if( bDelete==0 ){
    sqlite3VdbeAddOp3(v, OP_MakeRecord, 1, pIdx->nColumn, regRec);

    /* If this is a UNIQUE index, check the constraint. */
    if( pIdx->onError ){
      int addr = sqlite3VdbeAddOp4Int(v, OP_NoConflict, 0, 0, 1, pIdx->nKeyCol);
      sqlite3UniqueConstraint(pParse, SQLITE_ABORT, pIdx);
      sqlite3VdbeJumpHere(v, addr);
    }

    /* Code the IdxInsert to write to the b-tree index. */
    sqlite3VdbeAddOp2(v, OP_IdxInsert, 0, regRec);
  }else{
    /* Code the IdxDelete to remove the entry from the b-tree index. */
    sqlite3VdbeAddOp3(v, OP_IdxDelete, 0, 1, pIdx->nColumn);
  }
  sqlite3FinishCoding(pParse);

index_writer_out:
  if( rc==SQLITE_OK && db->mallocFailed==0 ){
    *ppStmt = (sqlite3_stmt*)v;
  }else{
    *ppStmt = 0;