SQLite

    }
    7 { 
      CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c);
      CREATE INDEX i1 ON t1(b, c);
      CREATE INDEX i2 ON t1(c, b);
      CREATE INDEX i3 ON t1(a, b, c, a, b, c);
    }

    8 { 
      CREATE TABLE t1(a PRIMARY KEY, b, c);
      CREATE INDEX i1 ON t1(b, c);
      CREATE INDEX i2 ON t1(c, b);
      CREATE INDEX i3 ON t1(a, b, c, a, b, c);
    }

    9 { 
      CREATE TABLE t1(a, b, c, PRIMARY KEY(a, c));
      CREATE INDEX i1 ON t1(b);
    }
  } {
    reset_db
    execsql $schema

    do_test 1.$tn2.$tn.1 {
      create_ota1 ota.db
      $cmd test.db ota.db

    do_execsql_test 1.$tn2.$tn.3 { PRAGMA integrity_check } ok
  }
}

#-------------------------------------------------------------------------
# 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
# explicit IPK columns or were declared WITHOUT ROWID. This has been
# relaxed so that external PRIMARY KEYs on tables with automatic rowids
# are now allowed.
#
reset_db
create_ota1 ota.db
do_execsql_test 2.1 { CREATE TABLE t1(a, b, c) }
do_test 2.2 {
  sqlite3ota ota test.db ota.db
  ota step
} {SQLITE_ERROR}
do_test 2.3 {
  list [catch { ota close } msg] $msg
} {1 {SQLITE_ERROR - table t1 has no PRIMARY KEY}}

reset_db
do_execsql_test 2.4 { CREATE TABLE t1(a PRIMARY KEY, b, c) }
do_test 2.5 {
  sqlite3ota ota test.db ota.db
  ota step
} {SQLITE_OK}
do_test 2.6 {
  list [catch { ota close } msg] $msg
} {0 SQLITE_OK}

#-------------------------------------------------------------------------
# Check that if a UNIQUE constraint is violated the current and all 
# subsequent [ota step] calls return SQLITE_CONSTRAINT. And that the OTA 
# transaction is rolled back by the [ota close] that deletes the ota 
# handle.
#
foreach {tn errcode errmsg schema} {
  1 SQLITE_CONSTRAINT "UNIQUE constraint failed: t1.a" {
    CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c);
    INSERT INTO t1 VALUES(3, 2, 1);
  } 

  2 SQLITE_CONSTRAINT "UNIQUE constraint failed: t1.c" {
    CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c UNIQUE);
    INSERT INTO t1 VALUES(4, 2, 'three');
  } 

  3 SQLITE_CONSTRAINT "UNIQUE constraint failed: t1.a" {
    CREATE TABLE t1(a PRIMARY KEY, b, c);
    INSERT INTO t1 VALUES(3, 2, 1);
  } 

  4 SQLITE_CONSTRAINT "UNIQUE constraint failed: t1.c" {
    CREATE TABLE t1(a PRIMARY KEY, b, c UNIQUE);
    INSERT INTO t1 VALUES(4, 2, 'three');
  } 

} {
  reset_db
  execsql $schema
  set cksum [dbcksum db main]

  do_test 3.$tn.1 {

      CREATE INDEX i1 ON t1(b);
      CREATE INDEX i2 ON t1(c, b);
      CREATE INDEX i3 ON t1(c, b, c);
    }
    4 {
      CREATE TABLE t1(a INT PRIMARY KEY, b, c) WITHOUT ROWID;
      CREATE INDEX i1 ON t1(b);
      CREATE INDEX i2 ON t1(c, b);
      CREATE INDEX i3 ON t1(c, b, c);
    }
    5 {
      CREATE TABLE t1(a INT 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 {

      CREATE INDEX i4 ON t1(b);
      CREATE INDEX i5 ON t1(c);
      CREATE INDEX i6 ON t1(c, b);
    }
    3 {
      CREATE TABLE t1(a PRIMARY KEY, b, c, d) WITHOUT ROWID;
      CREATE INDEX i1 ON t1(d);
      CREATE INDEX i2 ON t1(d, c);
      CREATE INDEX i3 ON t1(d, c, b);
      CREATE INDEX i4 ON t1(b);
      CREATE INDEX i5 ON t1(c);
      CREATE INDEX i6 ON t1(c, b);
    }
    4 {
      CREATE TABLE t1(a PRIMARY KEY, b, c, d);
      CREATE INDEX i1 ON t1(d);
      CREATE INDEX i2 ON t1(d, c);
      CREATE INDEX i3 ON t1(d, c, b);
      CREATE INDEX i4 ON t1(b);
      CREATE INDEX i5 ON t1(c);
      CREATE INDEX i6 ON t1(c, b);
    }
  } {

  SELECT * FROM t1
} {2 def}

#-------------------------------------------------------------------------
#
foreach {tn tbl} {
  1 { CREATE TABLE t1(a, b, c, PRIMARY KEY(a, b)) WITHOUT ROWID }
  2 { CREATE TABLE t1(a, b, c, PRIMARY KEY(a, b)) }
} {
  reset_db

  execsql $tbl
  do_execsql_test 2.$tn.1 {
    CREATE INDEX t1c ON t1(c);
    INSERT INTO t1 VALUES(1, 1, 'a');

*/
struct OtaObjIter {
  sqlite3_stmt *pTblIter;         /* Iterate through tables */
  sqlite3_stmt *pIdxIter;         /* Index iterator */
  int nTblCol;                    /* Size of azTblCol[] array */
  char **azTblCol;                /* Array of quoted column names */
  unsigned char *abTblPk;         /* Array of flags - true for PK columns */
  unsigned char bRowid;           /* True for implicit IPK tables */

  /* Output variables. zTbl==0 implies EOF. */
  int bCleanup;                   /* True in "cleanup" state */
  const char *zTbl;               /* Name of target db table */
  const char *zIdx;               /* Name of target db index (or null) */
  int iVisit;                     /* Number of points visited, incl. current */

  sqlite3_free(pIter->azTblCol);
  sqlite3_free(pIter->abTblPk);
  pIter->azTblCol = 0;
  pIter->abTblPk = 0;
  pIter->nTblCol = 0;
  sqlite3_free(pIter->zMask);
  pIter->zMask = 0;
  pIter->bRowid = 0;
}

/*
** Finalize all statements and free all allocations that are specific to
** the current object (table/index pair).
*/
static void otaObjIterClearStatements(OtaObjIter *pIter){

  }
  va_end(ap);
  return p->rc;
}

/*
** If they are not already populated, populate the pIter->azTblCol[],
** pIter->abTblPk[], pIter->nTblCol and pIter->bRowid variables according to
** the table that the iterator currently points to.
**
** Return SQLITE_OK if successful, or an SQLite error code otherwise. If
** an error does occur, an error code and error message are also left in 
** the OTA handle.
*/
static int otaObjIterGetCols(sqlite3ota *p, OtaObjIter *pIter){
  if( pIter->azTblCol==0 ){
    sqlite3_stmt *pStmt;
    char *zSql;
    int nCol = 0;
    int bSeenPk = 0;
    int rc2;                      /* sqlite3_finalize() return value */

    assert( pIter->bRowid==0 );
    zSql = sqlite3_mprintf("PRAGMA main.table_info(%Q)", pIter->zTbl);
    p->rc = prepareFreeAndCollectError(p->db, &pStmt, &p->zErrmsg, zSql);
    while( p->rc==SQLITE_OK && SQLITE_ROW==sqlite3_step(pStmt) ){
      if( (nCol % 8)==0 ){
        unsigned char *abNew;
        int nByte = sizeof(char*) * (nCol+8);
        char **azNew = (char**)sqlite3_realloc(pIter->azTblCol, nByte);
        abNew = (unsigned char*)sqlite3_realloc(pIter->abTblPk, nCol+8);

        if( azNew ) pIter->azTblCol = azNew;
        if( abNew ) pIter->abTblPk = abNew;
        if( azNew==0 || abNew==0 ) p->rc = SQLITE_NOMEM;
      }

      if( p->rc==SQLITE_OK ){
        const char *zName = (const char*)sqlite3_column_text(pStmt, 1);
        int iPk = sqlite3_column_int(pStmt, 5);
        pIter->abTblPk[nCol] = (iPk!=0);
        if( iPk ) bSeenPk = 1;
        if( iPk<0 ) pIter->bRowid = 1;
        pIter->azTblCol[nCol] = otaQuoteName(zName);
        if( pIter->azTblCol[nCol]==0 ) p->rc = SQLITE_NOMEM;
        nCol++;
      }
    }
    pIter->nTblCol = nCol;
    rc2 = sqlite3_finalize(pStmt);

){
  char *zList = 0;
  if( p->rc==SQLITE_OK ){
    const char *zSep = "";
    int i;
    for(i=0; i<nCol; i++){
      int iCol = aiCol ? aiCol[i] : i;
      char *zCol = (iCol>=0 ? pIter->azTblCol[iCol] : "ota_rowid");
      zList = sqlite3_mprintf("%z%s%s", zList, zSep, zCol);
      if( zList && azCollate ){
        zList = sqlite3_mprintf("%z COLLATE %Q", zList, azCollate[i]);
      }
      zSep = ", ";
      if( zList==0 ){
        p->rc = SQLITE_NOMEM;
        break;

      zList = sqlite3_mprintf("%z%s%s.%s", zList, zS, zObj, pIter->azTblCol[i]);
      zS = ", ";
      if( zList==0 ){
        p->rc = SQLITE_NOMEM;
        break;
      }
    }

    /* For a table with implicit rowids, append "old._rowid_" to the list. */
    if( pIter->bRowid ){
      zList = sqlite3_mprintf("%z, %s._rowid_", zList, zObj);
    }
  }
  return zList;
}

static char *otaObjIterGetWhere(
  sqlite3ota *p, 
  OtaObjIter *pIter

            p->db, 1, zIdx, &pIter->pDelete, &azColl, &aiCol, &pIter->nCol
        );
      }

      /* Create the SELECT statement to read keys in sorted order */
      zCollist = otaObjIterGetCollist(p, pIter, pIter->nCol, aiCol, azColl);
      if( p->rc==SQLITE_OK ){
        char *zSql;
        if( pIter->bRowid ){
          zSql = sqlite3_mprintf(
              "SELECT %s, ota_control FROM ota.'ota_tmp_%q' ORDER BY %s%s",
              zCollist, pIter->zTbl, 
              zCollist, zLimit
          );
        }else{
          zSql = sqlite3_mprintf(
              "SELECT %s, ota_control FROM ota.'data_%q' "
              "WHERE typeof(ota_control)='integer' AND ota_control!=1 "
              "UNION ALL "
              "SELECT %s, ota_control FROM ota.'ota_tmp_%q' "
              "ORDER BY %s%s",
              zCollist, pIter->zTbl, 
              zCollist, pIter->zTbl, 
              zCollist, zLimit

          );
        }
        p->rc = prepareFreeAndCollectError(p->db, &pIter->pSelect, pz, zSql);
      }
    }else{
      const char *zOtaRowid = (pIter->bRowid ? ", ota_rowid" : "");
      char *zBindings = otaObjIterGetBindlist(p, pIter->nTblCol);
      char *zWhere = otaObjIterGetWhere(p, pIter);
      char *zOldlist = otaObjIterGetOldlist(p, pIter, "old");
      char *zNewlist = otaObjIterGetOldlist(p, pIter, "new");
      zCollist = otaObjIterGetCollist(p, pIter, pIter->nTblCol, 0, 0);
      pIter->nCol = pIter->nTblCol;

        p->rc = prepareFreeAndCollectError(p->db, &pIter->pDelete, pz,
            sqlite3_mprintf(
              "DELETE FROM main.%Q WHERE %s", pIter->zTbl, zWhere
            )
        );
      }

      /* Create the ota_tmp_xxx table and the triggers to populate it. */
      otaMPrintfExec(p, 
          "CREATE TABLE IF NOT EXISTS ota.'ota_tmp_%q' AS "
          "SELECT *%s 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%s) VALUES(2, %s);"
          "END;"

          "CREATE TEMP TRIGGER ota_update1_%q BEFORE UPDATE ON main.%Q "
          "BEGIN "
          "  INSERT INTO 'ota_tmp_%q'(ota_control, %s%s) VALUES(2, %s);"
          "END;"

          "CREATE TEMP TRIGGER ota_update2_%q AFTER UPDATE ON main.%Q "
          "BEGIN "
          "  INSERT INTO 'ota_tmp_%q'(ota_control, %s%s) VALUES(3, %s);"
          "END;"
          , pIter->zTbl, (pIter->bRowid ? ", 0 AS ota_rowid" : ""),
          pIter->zTbl, 
          pIter->zTbl, pIter->zTbl, pIter->zTbl, zCollist, zOtaRowid, zOldlist,
          pIter->zTbl, pIter->zTbl, pIter->zTbl, zCollist, zOtaRowid, zOldlist,
          pIter->zTbl, pIter->zTbl, pIter->zTbl, zCollist, zOtaRowid, zNewlist
      );
      if( pIter->bRowid ){
        otaMPrintfExec(p, 
            "CREATE TEMP TRIGGER ota_insert_%q AFTER INSERT ON main.%Q "
            "BEGIN "
            "  INSERT INTO 'ota_tmp_%q'(ota_control, %s, ota_rowid)"
            "  VALUES(0, %s);"
            "END;"
            , pIter->zTbl, pIter->zTbl, pIter->zTbl, zCollist, zNewlist
        );
      }



      /* Allocate space required for the zMask field. */
      if( p->rc==SQLITE_OK ){
        int nMask = pIter->nTblCol+1;
        pIter->zMask = (char*)sqlite3_malloc(nMask);
        if( pIter->zMask==0 ){
          p->rc = SQLITE_NOMEM;

          sqlite3VdbeAddOp2(v, OP_Null, 0, 5);
        }
        if( (pCol->colFlags & COLFLAG_PRIMKEY)==0 ){
          k = 0;
        }else if( pPk==0 ){
          k = 1;
        }else{
          for(k=1; ALWAYS(k<=pTab->nCol) && pPk->aiColumn[k-1]!=i; k++){}
          if( (db->flags & SQLITE_OtaMode) && HasRowid(pTab) ){
            k = -1 * k;


          }
        }
        sqlite3VdbeAddOp2(v, OP_Integer, k, 6);
        sqlite3VdbeAddOp2(v, OP_ResultRow, 1, 6);
      }
    }
  }