SQLite

    set rc [ota step]
    ota close
    if {$rc != "SQLITE_OK"} break
  }
  set rc
}

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

      10 { 
        CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c);
        CREATE INDEX i1 ON t1(b DESC);
      }

      11 { 
        CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c);
        CREATE INDEX i1 ON t1(b DESC, a ASC, c DESC);
      }

      12 { 
        CREATE TABLE t1(a INT PRIMARY KEY DESC, b, c) WITHOUT ROWID; 
      }

      13 { 
        CREATE TABLE t1(a INT, b, c, PRIMARY KEY(a DESC)) WITHOUT ROWID; 
      }

      14 { 
        CREATE TABLE t1(a, b, c, PRIMARY KEY(a DESC, c)) WITHOUT ROWID;
        CREATE INDEX i1 ON t1(b);
      }

      15 { 
        CREATE TABLE t1(a, b, c, PRIMARY KEY(a, c DESC)) WITHOUT ROWID;
        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

      do_test $tn3.1.$tn2.$tn.1 {
        create_ota1 ota.db
        breakpoint
        $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
    }
  }

  #-------------------------------------------------------------------------
  # 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.
  #
  # UPDATE 2: Tables without any PRIMARY KEY declaration are now allowed.
  # However the input table must feature an "ota_rowid" column.
  #
  reset_db
  create_ota1 ota.db
  do_execsql_test $tn3.2.1 { CREATE TABLE t1(a, b, c) }
  do_test $tn3.2.2 {
    sqlite3ota ota test.db ota.db
    ota step
  } {SQLITE_ERROR}
  do_test $tn3.2.3 {
    list [catch { ota close } msg] $msg
  } {1 {SQLITE_ERROR - table data_t1 requires ota_rowid column}}
  reset_db
  do_execsql_test $tn3.2.4 { CREATE TABLE t1(a PRIMARY KEY, b, c) }
  do_test $tn3.2.5 {
    sqlite3ota ota test.db ota.db
    ota step
  } {SQLITE_OK}
  do_test $tn3.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 $tn3.3.$tn.1 {
      create_ota1 ota.db
      sqlite3ota ota test.db ota.db
      while {[set res [ota step]]=="SQLITE_OK"} {}
      set res
    } $errcode

    do_test $tn3.3.$tn.2 { ota step } $errcode

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

    do_test $tn3.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);
      }
      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);
      }

      6 {
        CREATE TABLE t1(a INT PRIMARY KEY DESC, b, c);
        CREATE INDEX i1 ON t1(b DESC);
        CREATE INDEX i2 ON t1(c, b);
        CREATE INDEX i3 ON t1(c DESC, b, c);
      }
      7 {
        CREATE TABLE t1(a INT PRIMARY KEY DESC, 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);
      }
    } {
      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 $tn3.4.$tn2.$tn.1 {
        create_ota4 ota.db
        $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
    }
  }

  #-------------------------------------------------------------------------
  #
  foreach {tn2 cmd} {1 run_ota 2 step_ota} {
    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);
        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);
      }
      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);
      }
    } {
      reset_db
      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);
      }
    
      do_test $tn3.5.$tn2.$tn.1 {
        create_ota5 ota.db
        $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.5.$tn2.$tn.3 { PRAGMA integrity_check } ok
    }
  }

  #-------------------------------------------------------------------------
  # 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.
  #
  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}
    
       2 {
         CREATE VIRTUAL TABLE t1 USING fts4(a, b);
         CREATE TABLE ota.data_t1(a, b, ota_control);
       } {SQLITE_ERROR - table data_t1 requires ota_rowid column}
    
       3 {
         CREATE TABLE t1(a PRIMARY KEY, b);
         CREATE TABLE ota.data_t1(a, b, ota_rowid, ota_control);
       } {SQLITE_ERROR - table data_t1 may not have ota_rowid column}
    
       4 {
         CREATE TABLE t1(a INTEGER PRIMARY KEY, b);
         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}
    
    } {
      reset_db
      forcedelete ota.db
      execsql { ATTACH 'ota.db' AS ota }
      execsql $schema

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

  eval $destroy_vfs
}


finish_test

}

/*
** File control method. For custom operations on an otaVfs-file.
*/
static int otaVfsFileControl(sqlite3_file *pFile, int op, void *pArg){
  ota_file *p = (ota_file *)pFile;
  int (*xControl)(sqlite3_file*,int,void*) = p->pReal->pMethods->xFileControl;

  if( op==SQLITE_FCNTL_OTA ){
    int rc;
    sqlite3ota *pOta = (sqlite3ota*)pArg;

    /* First try to find another OTA vfs lower down in the vfs stack. If
    ** one is found, this vfs will operate in pass-through mode. The lower
    ** level vfs will do the special OTA handling.  */
    rc = xControl(p->pReal, op, pArg);

    if( rc==SQLITE_NOTFOUND ){
      /* Now search for a zipvfs instance lower down in the VFS stack. If
      ** one is found, this is an error.  */
      void *dummy = 0;
      rc = xControl(p->pReal, SQLITE_FCNTL_ZIPVFS_PAGER, &dummy);
      if( rc==SQLITE_OK ){
        rc = SQLITE_ERROR;
        pOta->zErrmsg = sqlite3_mprintf("ota/zipvfs setup error");
      }else if( rc==SQLITE_NOTFOUND ){
        pOta->pTargetFd = p;
        p->pOta = pOta;
        rc = SQLITE_OK;
      }
    }
    return rc;
  }
  return xControl(p->pReal, op, pArg);
}

/*
** Return the sector-size in bytes for an otaVfs-file.
*/
static int otaVfsSectorSize(sqlite3_file *pFile){
  ota_file *p = (ota_file *)pFile;

  pOta = sqlite3ota_open(zTarget, zOta);
  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
*/
static int test_sqlite3ota_create_vfs(
  ClientData clientData,
  Tcl_Interp *interp,
  int objc,
  Tcl_Obj *CONST objv[]
){
  const char *zName;
  const char *zParent;
  int rc;

  if( objc!=3 && objc!=4 ){
    Tcl_WrongNumArgs(interp, 1, objv, "?-default? NAME PARENT");
    return TCL_ERROR;
  }

  zName = Tcl_GetString(objv[objc-2]);
  zParent = Tcl_GetString(objv[objc-1]);
  if( zParent[0]=='\0' ) zParent = 0;

  rc = sqlite3ota_create_vfs(zName, zParent);
  if( rc!=SQLITE_OK ){
    Tcl_SetObjResult(interp, Tcl_NewStringObj(sqlite3ErrName(rc), -1));
    return TCL_ERROR;
  }else if( objc==4 ){
    sqlite3_vfs *pVfs = sqlite3_vfs_find(zName);
    sqlite3_vfs_register(pVfs, 1);
  }

  Tcl_ResetResult(interp);
  return TCL_OK;
}

/*
** Tclcmd: sqlite3ota_destroy_vfs NAME
*/
static int test_sqlite3ota_destroy_vfs(
  ClientData clientData,
  Tcl_Interp *interp,
  int objc,
  Tcl_Obj *CONST objv[]
){
  const char *zName;

  if( objc!=2 ){
    Tcl_WrongNumArgs(interp, 1, objv, "NAME");
    return TCL_ERROR;
  }

  zName = Tcl_GetString(objv[1]);
  sqlite3ota_destroy_vfs(zName);
  return TCL_OK;
}


int SqliteOta_Init(Tcl_Interp *interp){ 
  static struct {
     char *zName;
     Tcl_ObjCmdProc *xProc;
  } aObjCmd[] = {
    { "sqlite3ota", test_sqlite3ota },
    { "sqlite3ota_create_vfs", test_sqlite3ota_create_vfs },
    { "sqlite3ota_destroy_vfs", test_sqlite3ota_destroy_vfs },
  };
  int i;
  for(i=0; i<sizeof(aObjCmd)/sizeof(aObjCmd[0]); i++){
    Tcl_CreateObjCommand(interp, aObjCmd[i].zName, aObjCmd[i].xProc, 0, 0);
  }
  return TCL_OK;
}
#endif                  /* ifdef SQLITE_TEST */
#else   /* !SQLITE_CORE || SQLITE_ENABLE_OTA */
# ifdef SQLITE_TEST
#include <tcl.h>
int SqliteOta_Init(Tcl_Interp *interp){ return TCL_OK; }
# endif
#endif

/*
** Open an OTA handle.
**
** Argument zTarget is the path to the target database. Argument zOta is
** the path to the OTA database. Each call to this function must be matched
** by a call to sqlite3ota_close().
**
** By default, OTA uses the default VFS to access the files on disk. To
** use a VFS other than the default, an SQLite "file:" URI containing a
** "vfs=..." option may be passed as the zTarget option.
**
** 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);

/*
** Obtain the underlying database handle used by the OTA extension.
**
** The only argument passed to this function must be a valid, open, OTA

** that immediately return the same value.
*/
int sqlite3ota_step(sqlite3ota *pOta);

/*
** Close an OTA handle. 
**
** If the OTA update has been completely applied, commit it to the target
** database. Otherwise, assuming no error has occurred, save the current
** state of the OTA update appliation to the OTA database.
**
** If an error has already occurred as part of an sqlite3ota_step()
** or sqlite3ota_open() call, or if one occurs within this function, an
** SQLite error code is returned. Additionally, *pzErrmsg may be set to
** point to a buffer containing a utf-8 formatted English language error
** message. It is the responsibility of the caller to eventually free any 

** Return the total number of key-value operations (inserts, deletes or 
** updates) that have been performed on the target database since the
** current OTA update was started.
*/
sqlite3_int64 sqlite3ota_progress(sqlite3ota *pOta);

/*
** Part of the OTA implementation uses a custom VFS object. Usually, this
** object is created and deleted automatically by OTA. 
**
** The exception is for applications that also use zipvfs. In this case,
** the custom VFS must be explicitly created by the user before the OTA
** handle is opened. The OTA VFS should be installed so that the zipvfs
** VFS uses the OTA VFS, which in turn uses any other VFS layers in use 
** (for example multiplexor) to access the file-system. For example,
** to assemble an OTA enabled VFS stack that uses both zipvfs and 
** multiplexor (error checking omitted):
**
**     // Create a VFS named "multiplexor" (not the default).
**     sqlite3_multiplex_initialize(zVfsName, 0);
**
**     // Create an ota VFS named "ota" that uses multiplexor.
**     sqlite3ota_create_vfs("ota", "multiplexor");
**
**     // Create a zipvfs VFS named "zipvfs" that uses ota. 
**     zipvfs_create_vfs_v3("zipvfs", "ota", 0, xCompressorAlgorithmDetector);
**
**     // Make zipvfs the default VFS.
**     sqlite3_vfs_register(sqlite3_vfs_find("zipvfs"), 1);
**
** Because the default VFS created above includes a OTA functionality, it
** may be used by OTA clients. Attempting to use OTA with a zipvfs VFS stack
** that does not include the OTA layer results in an error.
**
** The overhead of adding the "ota" VFS to the system is negligible for 
** non-OTA users. There is no harm in an application accessing the 
** file-system via "ota" all the time, even if it only uses OTA functionality 
** occasionally.
*/
int sqlite3ota_create_vfs(const char *zName, const char *zParent);

/*
** Deregister and destroy an OTA vfs created by an earlier call to
** sqlite3ota_create_vfs().
**
** VFS objects are not reference counted. If a VFS object is destroyed
** before all database handles that use it have been closed, the results
** are undefined.
*/
void sqlite3ota_destroy_vfs(const char *zName);

#endif /* _SQLITE3OTA_H */