SQLite

    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
}
#
# Create a simple OTA database. That expects to write to a table:
#
#   CREATE TABLE t1(c, b, '(a)' INTEGER PRIMARY KEY);
#
# This OTA includes both insert and delete operations.
#
proc create_ota4b {filename} {
  forcedelete $filename
  sqlite3 ota1 $filename  
  ota1 eval {
    CREATE TABLE data_t1(c, b, '(a)', ota_control);
    INSERT INTO data_t1 VALUES(3, 2, 1, 0);
    INSERT INTO data_t1 VALUES(5, NULL, 2, 1);
    INSERT INTO data_t1 VALUES(9, 8, 3, 0);
    INSERT INTO data_t1 VALUES(11, NULL, 4, 1);
  }
  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, d);
#
# This OTA includes update statements.
#

        3 8 9
        6 hello xyz
      }
    
      do_execsql_test $tn3.4.$tn2.$tn.3 { PRAGMA integrity_check } ok
    }
  }

  foreach {tn2 cmd} {1 run_ota 2 step_ota} {
    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');
      }
    
      do_test $tn3.4.$tn2.$tn.1 {
        create_ota4b ota.db
        $cmd test.db ota.db
      } {SQLITE_DONE}
      
      do_execsql_test $tn3.4.$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
    }
  }

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

  #-------------------------------------------------------------------------
  # Test some error cases:
  # 
  #   * A virtual table with no ota_rowid column.
  #   * A no-PK table with no ota_rowid column.
  #   * A PK table with an ota_rowid column.
  #
  #   6: An update string of the wrong length
  #
  ifcapable fts3 {
    foreach {tn schema error} {
       1 {
         CREATE TABLE t1(a, b);
         CREATE TABLE ota.data_t1(a, b, ota_control);
       } {SQLITE_ERROR - table data_t1 requires ota_rowid column}
    

         CREATE TABLE ota.data_t1(a, b, ota_rowid, ota_control);
       } {SQLITE_ERROR - table data_t1 may not have ota_rowid column}
    
       5 {
         CREATE TABLE t1(a, b PRIMARY KEY) WITHOUT ROWID;
         CREATE TABLE ota.data_t1(a, b, ota_rowid, ota_control);
       } {SQLITE_ERROR - table data_t1 may not have ota_rowid column}

       6 {
         CREATE TABLE t1(a, b PRIMARY KEY) WITHOUT ROWID;
         CREATE TABLE ota.data_t1(a, b, ota_control);
         INSERT INTO ota.data_t1 VALUES(1, 2, 'x.x');
       } {SQLITE_ERROR - invalid ota_control value}
    
    } {
      reset_db
      forcedelete ota.db
      execsql { ATTACH 'ota.db' AS ota }
      execsql $schema

  dbo close

  db_restore 
  hexio_write test.db 18 0101
  file exists test.db-wal
} {1}


do_test 2.3 {
  sqlite3ota ota test.db ota.db
  ota step
} {SQLITE_ERROR}

do_test 2.4 {
  list [catch {ota close} msg] $msg
} {1 {SQLITE_ERROR - cannot update wal mode database}}

#--------------------------------------------------------------------
# Test a constraint violation message with an unusual table name. 
# Specifically, one for which the first character is a codepoint
# smaller than 30 (character '0').
#
reset_db
do_execsql_test 3.1 {
  CREATE TABLE "(t1)"(a PRIMARY KEY, b, c);
  INSERT INTO "(t1)" VALUES(1, 2, 3);
  INSERT INTO "(t1)" VALUES(4, 5, 6);
}
db close

do_test 3.2 {
  forcedelete ota.db
  sqlite3 dbo ota.db
  dbo eval {
    CREATE TABLE "data_(t1)"(a, b, c, ota_control);
    INSERT INTO "data_(t1)" VALUES(4, 8, 9, 0);
  }
  dbo close

  sqlite3ota ota test.db ota.db
  ota step
  ota step
} {SQLITE_CONSTRAINT}

do_test 3.3 {
  list [catch {ota close} msg] $msg
} {1 {SQLITE_CONSTRAINT - UNIQUE constraint failed: (t1).a}}

#--------------------------------------------------------------------
# Check that once an OTA update has been applied, attempting to apply
# it a second time is a no-op (as the state stored in the OTA database is
# "all steps completed").
#
reset_db
do_execsql_test 4.1 {
  CREATE TABLE "(t1)"(a, b, c, PRIMARY KEY(c, b, a));
  INSERT INTO "(t1)" VALUES(1, 2, 3);
  INSERT INTO "(t1)" VALUES(4, 5, 6);
}
db close

do_test 4.2 {
  forcedelete ota.db
  sqlite3 dbo ota.db
  dbo eval {
    CREATE TABLE "data_(t1)"(a, b, c, ota_control);
    INSERT INTO "data_(t1)" VALUES(7, 8, 9, 0);
    INSERT INTO "data_(t1)" VALUES(1, 2, 3, 1);
  }
  dbo close

  sqlite3ota ota test.db ota.db
  while {[ota step]=="SQLITE_OK"} { }
  ota close
} {SQLITE_DONE}

do_test 4.3 {
  sqlite3ota ota test.db ota.db
  ota step
} {SQLITE_DONE}

do_test 4.4 {
  ota close
} {SQLITE_DONE}

# Also, check that an invalid state value in the ota_state table is
# detected and reported as corruption.
do_test 4.5 {
  sqlite3 dbo ota.db
  dbo eval { UPDATE ota_state SET v = -1 WHERE k = 1 }
  dbo close
  sqlite3ota ota test.db ota.db
  ota step
} {SQLITE_CORRUPT}
do_test 4.6 {
  list [catch {ota close} msg] $msg
} {1 SQLITE_CORRUPT}

finish_test

source $testdir/tester.tcl
source $testdir/malloc_common.tcl
set ::testprefix otafault

do_test 1.1 {
  forcedelete ota.db
  execsql {
    PRAGMA encoding = utf16;
    CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c);
    CREATE INDEX t1cb ON t1(c, b);
    INSERT INTO t1 VALUES(1, 1, 1);
    INSERT INTO t1 VALUES(2, 2, 2);
    INSERT INTO t1 VALUES(3, 3, 3);
    CREATE TABLE t2(a PRIMARY KEY, b, c) WITHOUT ROWID;
    CREATE INDEX t2cb ON t1(c, b);

  }
  db close

  forcecopy test.db test.db.bak
  forcecopy ota.db ota.db.bak
} {}

sqlite3_shutdown
set lookaside_config [sqlite3_config_lookaside 0 0]
sqlite3_initialize
autoinstall_test_functions

foreach {tn f reslist} {
  1 oom-tra*  {
    {0 SQLITE_DONE} 
    {1 {SQLITE_NOMEM - out of memory}} 
    {1 SQLITE_NOMEM} 
    {1 SQLITE_IOERR_NOMEM} 
    {1 {SQLITE_NOMEM - unable to open a temporary database file for storing temporary tables}}
  }
  2 ioerr-*  {
    {0 SQLITE_DONE} 
    {1 {SQLITE_IOERR - disk I/O error}}
    {1 SQLITE_IOERR}
    {1 SQLITE_IOERR_WRITE}
    {1 SQLITE_IOERR_READ}
    {1 SQLITE_IOERR_FSYNC}
    {1 {SQLITE_ERROR - SQL logic error or missing database}}
    {1 {SQLITE_ERROR - unable to open database: ota.db}}
    {1 {SQLITE_IOERR - unable to open database: ota.db}}
  }
} {
  do_faultsim_test 2 -faults $::f -prep {
    catch { db close }
    forcedelete test.db-journal test.db-wal ota.db-journal ota.db-wal
    forcecopy test.db.bak test.db
    forcecopy ota.db.bak  ota.db
  } -body {
    sqlite3ota ota test.db ota.db
    while {[ota step]=="SQLITE_OK"} {}
    ota close
  } -test {
    faultsim_test_result {*}$::reslist




    if {$testrc==0} {
      sqlite3 db test.db
      faultsim_integrity_check
      set res [db eval {
        SELECT * FROM t1 UNION ALL SELECT * FROM t2;
      }]
      set expected [list {*}{
        1 1 1   3 three 3   4 4 4
        a a a   c see c     d d d
      }]
  
      if {$res != $expected} {
        puts ""
        puts "res: $res"
        puts "exp: $expected"
        error "data not as expected!"
      }
    }
  }
}

catch {db close}
sqlite3_shutdown
sqlite3_config_lookaside {*}$lookaside_config
sqlite3_initialize
autoinstall_test_functions

proc copy_if_exists {src target} {
  if {[file exists $src]} {
    forcecopy $src $target
  } else {
    forcedelete $target
  }

        rc = sqlite3_step(pIter->pTblIter);
        if( rc!=SQLITE_ROW ){
          rc = sqlite3_reset(pIter->pTblIter);
          pIter->zTbl = 0;
        }else{
          pIter->zTbl = (const char*)sqlite3_column_text(pIter->pTblIter, 0);
          pIter->iTnum = sqlite3_column_int(pIter->pTblIter, 1);
          rc = pIter->zTbl ? SQLITE_OK : SQLITE_NOMEM;
        }
      }else{
        if( pIter->zIdx==0 ){
          sqlite3_stmt *pIdx = pIter->pIdxIter;
          rc = sqlite3_bind_text(pIdx, 1, pIter->zTbl, -1, SQLITE_STATIC);
        }
        if( rc==SQLITE_OK ){
          rc = sqlite3_step(pIter->pIdxIter);
          if( rc!=SQLITE_ROW ){
            rc = sqlite3_reset(pIter->pIdxIter);
            pIter->bCleanup = 1;
            pIter->zIdx = 0;
          }else{
            pIter->zIdx = (const char*)sqlite3_column_text(pIter->pIdxIter, 0);
            pIter->iTnum = sqlite3_column_int(pIter->pIdxIter, 1);
            pIter->bUnique = sqlite3_column_int(pIter->pIdxIter, 2);
            rc = pIter->zIdx ? SQLITE_OK : SQLITE_NOMEM;
          }
        }
      }
    }
  }

  if( rc!=SQLITE_OK ){
    otaObjIterFinalize(pIter);

    if( p->rc==SQLITE_OK ){
      p->rc = prepareFreeAndCollectError(p->db, &pStmt, &p->zErrmsg, 
          sqlite3_mprintf("PRAGMA main.table_info(%Q)", pIter->zTbl)
      );
    }
    while( p->rc==SQLITE_OK && SQLITE_ROW==sqlite3_step(pStmt) ){
      const char *zName = (const char*)sqlite3_column_text(pStmt, 1);
      if( zName==0 ) break;  /* An OOM - finalize() below returns S_NOMEM */
      for(i=iOrder; i<pIter->nTblCol; i++){
        if( 0==strcmp(zName, pIter->azTblCol[i]) ) break;
      }
      if( i==pIter->nTblCol ){
        p->rc = SQLITE_ERROR;
        p->zErrmsg = sqlite3_mprintf("column missing from data_%q: %s",
            pIter->zTbl, zName

    const char *zType;

    if( iCid<0 ){
      /* An integer primary key. If the table has an explicit IPK, use
      ** its name. Otherwise, use "ota_rowid".  */
      if( pIter->eType==OTA_PK_IPK ){
        int i;
        for(i=0; pIter->abTblPk[i]==0; i++);
        assert( i<pIter->nTblCol );
        zCol = pIter->azTblCol[i];
      }else{
        zCol = "ota_rowid";
      }
      zType = "INTEGER";
    }else{

** (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");
}


static char *otaObjIterGetSetlist(
  sqlite3ota *p,
  OtaObjIter *pIter,
  const char *zMask

   
    p->rc = prepareFreeAndCollectError(p->db, &pXList, &p->zErrmsg,
        sqlite3_mprintf("PRAGMA main.index_list = %Q", pIter->zTbl)
    );
    while( p->rc==SQLITE_OK && SQLITE_ROW==sqlite3_step(pXList) ){
      const char *zOrig = (const char*)sqlite3_column_text(pXList,3);
      if( zOrig && strcmp(zOrig, "pk")==0 ){
        const char *zIdx = (const char*)sqlite3_column_text(pXList,1);
        if( zIdx ){
          p->rc = prepareFreeAndCollectError(p->db, &pXInfo, &p->zErrmsg,
              sqlite3_mprintf("PRAGMA main.index_xinfo = %Q", zIdx)

          );
        }
        break;
      }
    }
    rc = sqlite3_finalize(pXList);
    if( p->rc==SQLITE_OK ) p->rc = rc;

    while( p->rc==SQLITE_OK && pXInfo && SQLITE_ROW==sqlite3_step(pXInfo) ){
      if( sqlite3_column_int(pXInfo, 5) ){
        /* int iCid = sqlite3_column_int(pXInfo, 0); */
        const char *zCol = (const char*)sqlite3_column_text(pXInfo, 2);
        const char *zDesc = sqlite3_column_int(pXInfo, 3) ? " DESC" : "";
        z = otaMPrintf(p, "%z%s\"%w\"%s", z, zSep, zCol, zDesc);
        zSep = ", ";
      }
    }
    z = otaMPrintf(p, "%z)", z);

    rc = sqlite3_finalize(pXInfo);
    if( p->rc==SQLITE_OK ) p->rc = rc;
  }
  return z;
}

static void otaCreateImposterTable2(sqlite3ota *p, OtaObjIter *pIter){

        res = OTA_IDX_DELETE;
      }else if( iVal==3 ){
        res = OTA_IDX_INSERT;
      }
      break;
    }

    case SQLITE_TEXT: {
      const unsigned char *z = sqlite3_column_text(p->objiter.pSelect, iCol);
      if( z==0 ){
        p->rc = SQLITE_NOMEM;
      }else{
        *pzMask = (const char*)z;
      }
      res = OTA_UPDATE;

      break;
    }

    default:
      break;
  }

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

        }

        if( eType==SQLITE_DELETE && pIter->zIdx==0 && pIter->abTblPk[i]==0 ){
          continue;
        }

        pVal = sqlite3_column_value(pIter->pSelect, i);
        p->rc = sqlite3_bind_value(pWriter, i+1, pVal);
        if( p->rc==SQLITE_RANGE ) p->rc = SQLITE_OK;
        if( p->rc ) goto step_out;
      }
      if( pIter->zIdx==0
       && (pIter->eType==OTA_PK_VTAB || pIter->eType==OTA_PK_NONE) 
      ){
        /* For a virtual table, or a table with no primary key, the 
        ** SELECT statement is:
        **
        **   SELECT <cols>, ota_control, ota_rowid FROM ....
        **
        ** Hence column_value(pIter->nCol+1).
        */
        assertColumnName(pIter->pSelect, pIter->nCol+1, "ota_rowid");
        pVal = sqlite3_column_value(pIter->pSelect, pIter->nCol+1);
        p->rc = sqlite3_bind_value(pWriter, pIter->nCol+1, pVal);
      }
      if( p->rc==SQLITE_OK ){
        sqlite3_step(pWriter);
        p->rc = resetAndCollectError(pWriter, &p->zErrmsg);
      }
    }else if( eType==OTA_UPDATE ){
      sqlite3_value *pVal;
      sqlite3_stmt *pUpdate = 0;
      otaGetUpdateStmt(p, pIter, zMask, &pUpdate);
      if( pUpdate ){
        for(i=0; p->rc==SQLITE_OK && i<pIter->nCol; i++){
          char c = zMask[pIter->aiSrcOrder[i]];
          pVal = sqlite3_column_value(pIter->pSelect, i);
          if( pIter->abTblPk[i] || c=='x' || c=='d' ){
            p->rc = sqlite3_bind_value(pUpdate, i+1, pVal);
          }
        }
        if( p->rc==SQLITE_OK 
         && (pIter->eType==OTA_PK_VTAB || pIter->eType==OTA_PK_NONE) 
        ){
          /* Bind the ota_rowid value to column _rowid_ */
          assertColumnName(pIter->pSelect, pIter->nCol+1, "ota_rowid");
          pVal = sqlite3_column_value(pIter->pSelect, pIter->nCol+1);
          p->rc = sqlite3_bind_value(pUpdate, pIter->nCol+1, pVal);
        }
        if( p->rc==SQLITE_OK ){
          sqlite3_step(pUpdate);
          p->rc = resetAndCollectError(pUpdate, &p->zErrmsg);
        }
      }
    }else{
      /* no-op */
      assert( eType==OTA_DELETE && pIter->zIdx );
    }
  }

    }

    /* If it has not already been created, create the ota_state table */
    if( p->rc==SQLITE_OK ){
      p->rc = sqlite3_exec(p->db, OTA_CREATE_STATE, 0, 0, &p->zErrmsg);
    }













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

        if( pState->eStage==0 ){ 
          otaDeleteOalFile(p);
          p->eStage = OTA_STAGE_OAL;
        }else{
          p->eStage = pState->eStage;
        }
        p->nProgress = pState->nProgress;
      }
    }
    assert( p->rc!=SQLITE_OK || p->eStage!=0 );

    if( p->rc==SQLITE_OK ){
      if( p->eStage==OTA_STAGE_OAL ){

        /* Check that this is not a wal mode database. If it is, it cannot 
        ** be updated.  */
        if( p->pTargetFd->pWalFd ){
          p->rc = SQLITE_ERROR;
          p->zErrmsg = sqlite3_mprintf("cannot update wal mode database");
        }

        /* At this point (pTargetFd->iCookie) contains the value of the
        ** change-counter cookie (the thing that gets incremented when a 

          otaLoadTransactionState(p, pState);
        }
      }else if( p->eStage==OTA_STAGE_CKPT ){
        otaSetupCheckpoint(p, pState);
        p->nStep = pState->nRow;
      }else if( p->eStage==OTA_STAGE_DONE ){
        p->rc = SQLITE_DONE;
      }else{
        p->rc = SQLITE_CORRUPT;
      }
    }

    otaFreeState(pState);
  }

  return p;

  void *zBuf, 
  int iAmt, 
  sqlite_int64 iOfst
){
  ota_file *p = (ota_file*)pFile;
  int rc;


  if( p->pOta && p->pOta->eStage==OTA_STAGE_CAPTURE ){
    assert( p->openFlags & SQLITE_OPEN_WAL );

    rc = otaCaptureWalRead(p->pOta, iOfst, iAmt);
  }else{
    rc = p->pReal->pMethods->xRead(p->pReal, zBuf, iAmt, iOfst);
    if( rc==SQLITE_OK && iOfst==0 && (p->openFlags & SQLITE_OPEN_MAIN_DB) ){
      /* These look like magic numbers. But they are stable, as they are part
       ** of the definition of the SQLite file format, which may not change. */
      u8 *pBuf = (u8*)zBuf;

  sqlite3_file *pFile, 
  const void *zBuf, 
  int iAmt, 
  sqlite_int64 iOfst
){
  ota_file *p = (ota_file*)pFile;
  int rc;

  if( p->pOta && p->pOta->eStage==OTA_STAGE_CAPTURE ){
    assert( p->openFlags & SQLITE_OPEN_MAIN_DB );

    rc = otaCaptureDbWrite(p->pOta, iOfst);
  }else{
    rc = p->pReal->pMethods->xWrite(p->pReal, zBuf, iAmt, iOfst);
    if( rc==SQLITE_OK && iOfst==0 && (p->openFlags & SQLITE_OPEN_MAIN_DB) ){
      /* These look like magic numbers. But they are stable, as they are part
      ** of the definition of the SQLite file format, which may not change. */
      u8 *pBuf = (u8*)zBuf;