SQLite

  $(TOP)/ext/fts3/fts3_write.c
SRC += \
  $(TOP)/ext/icu/sqliteicu.h \
  $(TOP)/ext/icu/icu.c
SRC += \
  $(TOP)/ext/rtree/rtree.h \
  $(TOP)/ext/rtree/rtree.c
SRC += \
  $(TOP)/ext/ota/sqlite3ota.h \
  $(TOP)/ext/ota/sqlite3ota.c


# Generated source code files
#
SRC += \
  keywordhash.h \
  opcodes.c \

  $(TOP)/src/test_superlock.c \
  $(TOP)/src/test_syscall.c \
  $(TOP)/src/test_tclvar.c \
  $(TOP)/src/test_thread.c \
  $(TOP)/src/test_vfs.c \
  $(TOP)/src/test_wsd.c       \
  $(TOP)/ext/fts3/fts3_term.c \
  $(TOP)/ext/fts3/fts3_test.c 
  $(TOP)/ext/fts3/fts3_test.c \
  $(TOP)/ext/ota/test_ota.c 

# Statically linked extensions
#
TESTSRC += \
  $(TOP)/ext/misc/amatch.c \
  $(TOP)/ext/misc/closure.c \
  $(TOP)/ext/misc/eval.c \

/*
** 2014 August 30
**
** The author disclaims copyright to this source code.  In place of
** a legal notice, here is a blessing:
**
**    May you do good and not evil.
**    May you find forgiveness for yourself and forgive others.
**    May you share freely, never taking more than you give.
**
*************************************************************************
**
** This file contains a command-line application that uses the OTA 
** extension. See the usage() function below for an explanation.
*/

#include "sqlite3ota.h"
#include <stdio.h>
#include <stdlib.h>
#include <string.h>

/*
** Print a usage message and exit.
*/
void usage(const char *zArgv0){
  fprintf(stderr, 
"Usage: %s [-step NSTEP] TARGET-DB OTA-DB\n"
"\n"
"  Argument OTA-DB must be an OTA database containing an update suitable for\n"
"  target database TARGET-DB. If NSTEP is set to less than or equal to zero\n"
"  (the default value), this program attempts to apply the entire update to\n"
"  the target database.\n"
"\n"
"  If NSTEP is greater than zero, then a maximum of NSTEP calls are made\n"
"  to sqlite3ota_step(). If the OTA update has not been completely applied\n"
"  after the NSTEP'th call is made, the state is saved in the database OTA-DB\n"
"  and the program exits. Subsequent invocations of this (or any other OTA)\n"
"  application will use this state to resume applying the OTA update to the\n"
"  target db.\n"
"\n"
, zArgv0);
  exit(1);
}

void report_default_vfs(){
  sqlite3_vfs *pVfs = sqlite3_vfs_find(0);
  fprintf(stdout, "default vfs is \"%s\"\n", pVfs->zName);
}

void report_ota_vfs(sqlite3ota *pOta){
  sqlite3 *db = sqlite3ota_db(pOta, 0);
  if( db ){
    char *zName = 0;
    sqlite3_file_control(db, "main", SQLITE_FCNTL_VFSNAME, &zName);
    if( zName ){
      fprintf(stdout, "using vfs \"%s\"\n", zName);
    }else{
      fprintf(stdout, "vfs name not available\n");
    }
    sqlite3_free(zName);
  }
}

int main(int argc, char **argv){
  int i;
  const char *zTarget;            /* Target database to apply OTA to */
  const char *zOta;               /* Database containing OTA */
  char *zErrmsg;                  /* Error message, if any */
  sqlite3ota *pOta;               /* OTA handle */
  int nStep = 0;                  /* Maximum number of step() calls */
  int rc;
  sqlite3_int64 nProgress = 0;

  /* Process command line arguments. Following this block local variables 
  ** zTarget, zOta and nStep are all set. */
  if( argc==5 ){
    int nArg1 = strlen(argv[1]);
    if( nArg1>5 || nArg1<2 || memcmp("-step", argv[1], nArg1) ) usage(argv[0]);
    nStep = atoi(argv[2]);
  }else if( argc!=3 ){
    usage(argv[0]);
  }
  zTarget = argv[argc-2];
  zOta = argv[argc-1];

  report_default_vfs();

  /* Open an OTA handle. If nStep is less than or equal to zero, call
  ** sqlite3ota_step() until either the OTA has been completely applied
  ** or an error occurs. Or, if nStep is greater than zero, call
  ** sqlite3ota_step() a maximum of nStep times.  */
  pOta = sqlite3ota_open(zTarget, zOta);
  report_ota_vfs(pOta);
  for(i=0; (nStep<=0 || i<nStep) && sqlite3ota_step(pOta)==SQLITE_OK; i++);
  nProgress = sqlite3ota_progress(pOta);
  rc = sqlite3ota_close(pOta, &zErrmsg);

  /* Let the user know what happened. */
  switch( rc ){
    case SQLITE_OK:
      fprintf(stdout, 
          "SQLITE_OK: ota update incomplete (%lld operations so far)\n",
          nProgress
      );
      break;

    case SQLITE_DONE:
      fprintf(stdout, 
          "SQLITE_DONE: ota update completed (%lld operations)\n",
          nProgress
      );
      break;

    default:
      fprintf(stderr, "error=%d: %s\n", rc, zErrmsg);
      break;
  }

  sqlite3_free(zErrmsg);
  return (rc==SQLITE_OK || rc==SQLITE_DONE) ? 0 : 1;
}

# 2014 August 30
#
# The author disclaims copyright to this source code.  In place of
# a legal notice, here is a blessing:
#
#    May you do good and not evil.
#    May you find forgiveness for yourself and forgive others.
#    May you share freely, never taking more than you give.
#
#***********************************************************************
#

if {![info exists testdir]} {
  set testdir [file join [file dirname [info script]] .. .. test]
}
source $testdir/tester.tcl
set ::testprefix ota1

db close
sqlite3_shutdown
sqlite3_config_uri 1

# Create a simple OTA database. That expects to write to a table:
#
#   CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c);
#
proc create_ota1 {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, '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);
#
# This OTA includes both insert and delete operations.
#
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
}
#
# 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.
#
proc create_ota5 {filename} {
  forcedelete $filename
  sqlite3 ota5 $filename  
  ota5 eval {
    CREATE TABLE data_t1(a, b, c, d, ota_control);
    INSERT INTO data_t1 VALUES(1, NULL, NULL, 5, '...x');  -- SET d = 5
    INSERT INTO data_t1 VALUES(2, NULL, 10, 5, '..xx');    -- SET c=10, d = 5
    INSERT INTO data_t1 VALUES(3, 11, NULL, NULL, '.x..'); -- SET b=11
  }
  ota5 close
  return $filename
}

# Run the OTA in file $ota on target database $target until completion.
#
proc run_ota {target ota} {
  sqlite3ota ota $target $ota
  while 1 {
    set rc [ota step]
    if {$rc!="SQLITE_OK"} break
  }
  ota close
}

proc step_ota {target ota} {
  while 1 {
    sqlite3ota ota $target $ota
    set rc [ota step]
    ota close
    if {$rc != "SQLITE_OK"} break
  }
  set rc
}

# Same as [step_ota], except using a URI to open the target db.
#
proc step_ota_uri {target ota} {
  while 1 {
    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);
      }
      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
      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
  # 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 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);
      }
      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');
      }

      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);
        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);
      }
    
      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.
  #   * 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}
    
       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}

       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}

       7 {
         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, NULL);
       } {SQLITE_ERROR - invalid ota_control value}

       8 {
         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, 4);
       } {SQLITE_ERROR - invalid ota_control value}

       9 {
         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, 2);
       } {SQLITE_ERROR - invalid ota_control value}

       10 {
         CREATE TABLE t2(a, b);
         CREATE TABLE ota.data_t1(a, b, ota_control);
         INSERT INTO ota.data_t1 VALUES(1, 2, 2);
       } {SQLITE_ERROR - no such table: t1}

       11 {
         CREATE TABLE ota.data_t2(a, b, ota_control);
         INSERT INTO ota.data_t2 VALUES(1, 2, 2);
       } {SQLITE_ERROR - no such table: t2}

    } {
      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

# 2014 August 30
#
# The author disclaims copyright to this source code.  In place of
# a legal notice, here is a blessing:
#
#    May you do good and not evil.
#    May you find forgiveness for yourself and forgive others.
#    May you share freely, never taking more than you give.
#
#***********************************************************************
#

if {![info exists testdir]} {
  set testdir [file join [file dirname [info script]] .. .. test]
}
source $testdir/tester.tcl
set ::testprefix ota10


#--------------------------------------------------------------------
# Test that UPDATE commands work even if the input columns are in a 
# different order to the output columns. 
#
do_execsql_test 1.0 {
  CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c);
  INSERT INTO t1 VALUES(1, 'b', 'c');
}

proc apply_ota {sql} {
  forcedelete ota.db
  sqlite3 db2 ota.db
  db2 eval $sql
  db2 close
  sqlite3ota ota test.db ota.db
  while { [ota step]=="SQLITE_OK" } {}
  ota close
}

do_test 1.1 {
  apply_ota {
    CREATE TABLE data_t1(a, c, b, ota_control);
    INSERT INTO data_t1 VALUES(1, 'xxx', NULL, '.x.');
  }
  db eval { SELECT * FROM t1 }
} {1 b xxx}

#--------------------------------------------------------------------
# Test that the hidden languageid column of an fts4 table can be 
# written.
#
ifcapable fts3 {
  do_execsql_test 2.0 {
    CREATE VIRTUAL TABLE ft USING fts4(a, b, languageid='langid');
  }
  do_test 2.1 {
    apply_ota {
      CREATE TABLE data_ft(a, b, ota_rowid, langid, ota_control);
      INSERT INTO data_ft VALUES('a', 'b', 22, 1, 0);    -- insert
      INSERT INTO data_ft VALUES('a', 'b', 23, 10, 0);   -- insert
      INSERT INTO data_ft VALUES('a', 'b', 24, 100, 0);  -- insert
    }
    db eval { SELECT a, b, rowid, langid FROM ft }
  } [list {*}{
    a b 22 1
    a b 23 10
    a b 24 100
  }]
  
  # Or not - this data_xxx table has no langid column, so langid 
  # defaults to 0.
  #
  do_test 2.2 {
    apply_ota {
      CREATE TABLE data_ft(a, b, ota_rowid, ota_control);
      INSERT INTO data_ft VALUES('a', 'b', 25, 0);    -- insert
    }
    db eval { SELECT a, b, rowid, langid FROM ft }
  } [list {*}{
    a b 22 1
    a b 23 10
    a b 24 100
    a b 25 0
  }]
  
  # Update langid.
  #
  do_test 2.3 {
    apply_ota {
      CREATE TABLE data_ft(a, b, ota_rowid, langid, ota_control);
      INSERT INTO data_ft VALUES(NULL, NULL, 23, 50, '..x');
      INSERT INTO data_ft VALUES(NULL, NULL, 25, 500, '..x');
    }
    db eval { SELECT a, b, rowid, langid FROM ft }
  } [list {*}{
    a b 22 1
    a b 23 50
    a b 24 100
    a b 25 500
  }]
}

#--------------------------------------------------------------------
# Test that if writing a hidden virtual table column is an error, 
# attempting to do so via ota is also an error.
#
ifcapable fts3 {
  do_execsql_test 3.0 {
    CREATE VIRTUAL TABLE xt USING fts4(a);
  }
  do_test 3.1 {
    list [catch {
      apply_ota {
        CREATE TABLE data_xt(a, xt, ota_rowid, ota_control);
        INSERT INTO data_xt VALUES('a', 'b', 1, 0);
      }
    } msg] $msg
  } {1 {SQLITE_ERROR - SQL logic error or missing database}}
}

#--------------------------------------------------------------------
# Test that it is not possible to violate a NOT NULL constraint by
# applying an OTA update.
#
do_execsql_test 4.1 {
  CREATE TABLE t2(a INTEGER NOT NULL, b TEXT NOT NULL, c PRIMARY KEY);
  CREATE TABLE t3(a INTEGER NOT NULL, b TEXT NOT NULL, c INTEGER PRIMARY KEY);
  CREATE TABLE t4(a, b, PRIMARY KEY(a, b)) WITHOUT ROWID;

  INSERT INTO t2 VALUES(10, 10, 10);
  INSERT INTO t3 VALUES(10, 10, 10);
  INSERT INTO t4 VALUES(10, 10);
}

foreach {tn error ota} {
  2 {SQLITE_CONSTRAINT - NOT NULL constraint failed: t2.a} {
    INSERT INTO data_t2 VALUES(NULL, 'abc', 1, 0);
  }
  3 {SQLITE_CONSTRAINT - NOT NULL constraint failed: t2.b} {
    INSERT INTO data_t2 VALUES(2, NULL, 1, 0);
  }
  4 {SQLITE_CONSTRAINT - NOT NULL constraint failed: t2.c} {
    INSERT INTO data_t2 VALUES(1, 'abc', NULL, 0);
  }

  5 {SQLITE_MISMATCH - datatype mismatch} {
    INSERT INTO data_t3 VALUES(1, 'abc', NULL, 0);
  }

  6 {SQLITE_CONSTRAINT - NOT NULL constraint failed: t4.b} {
    INSERT INTO data_t4 VALUES('a', NULL, 0);
  }
  7 {SQLITE_CONSTRAINT - NOT NULL constraint failed: t4.a} {
    INSERT INTO data_t4 VALUES(NULL, 'a', 0);
  }
  8  {SQLITE_CONSTRAINT - NOT NULL constraint failed: t2.a} {
    INSERT INTO data_t2 VALUES(NULL, 0, 10, 'x..');
  }
  9  {SQLITE_CONSTRAINT - NOT NULL constraint failed: t3.b} {
    INSERT INTO data_t3 VALUES(10, NULL, 10, '.x.');
  }

  10 {SQLITE_MISMATCH - datatype mismatch} {
    INSERT INTO data_t3 VALUES(1, 'abc', 'text', 0);
  }
} {
  set ota "
    CREATE TABLE data_t2(a, b, c, ota_control);
    CREATE TABLE data_t3(a, b, c, ota_control);
    CREATE TABLE data_t4(a, b, ota_control);
    $ota
  "
  do_test 4.2.$tn {
    list [catch { apply_ota $ota } msg] $msg
  } [list 1 $error]
}

do_test 4.3 {
  set ota {
    CREATE TABLE data_t3(a, b, c, ota_control);
    INSERT INTO data_t3 VALUES(1, 'abc', '5', 0);
    INSERT INTO data_t3 VALUES(1, 'abc', '-6.0', 0);
  }
  list [catch { apply_ota $ota } msg] $msg
} {0 SQLITE_DONE}


finish_test

# 2015 February 16
#
# The author disclaims copyright to this source code.  In place of
# a legal notice, here is a blessing:
#
#    May you do good and not evil.
#    May you find forgiveness for yourself and forgive others.
#    May you share freely, never taking more than you give.
#
#***********************************************************************
#

if {![info exists testdir]} {
  set testdir [file join [file dirname [info script]] .. .. test]
}
source $testdir/tester.tcl
set ::testprefix ota11


#--------------------------------------------------------------------
# Test that the xAccess() method of an ota vfs handles queries other
# than SQLITE_ACCESS_EXISTS correctly. The test code below causes
# SQLite to call xAccess(SQLITE_ACCESS_READWRITE) on the directory
# path argument passed to "PRAGMA temp_store_directory".
#
do_test 1.1 {
  sqlite3ota_create_vfs -default ota ""
  reset_db
  catchsql { PRAGMA temp_store_directory = '/no/such/directory' }
} {1 {not a writable directory}}

do_test 1.2 {
  catchsql " PRAGMA temp_store_directory = '[pwd]' "
} {0 {}}

do_test 1.3 {
  catchsql " PRAGMA temp_store_directory = '' "
} {0 {}}

do_test 1.4 {
  db close
  sqlite3ota_destroy_vfs ota
} {}

#--------------------------------------------------------------------
# Try to trick ota into operating on a database opened in wal mode.
#
reset_db
do_execsql_test 2.1 {
  CREATE TABLE t1(a PRIMARY KEY, b, c);
  INSERT INTO t1 VALUES(1, 2, 3);
  PRAGMA journal_mode = 'wal';
  CREATE TABLE t2(d PRIMARY KEY, e, f);
} {wal}

do_test 2.2 {
  db_save 
  db close

  forcedelete ota.db
  sqlite3 dbo ota.db
  dbo eval {
    CREATE TABLE data_t1(a, b, c, ota_control);
    INSERT INTO data_t1 VALUES(4, 5, 6, 0);
    INSERT INTO data_t1 VALUES(7, 8, 9, 0);
  }
  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}

do_test 4.5.1 {
  sqlite3 dbo ota.db
  dbo eval { INSERT INTO ota_state VALUES(100, 100) }
  dbo close
  sqlite3ota ota test.db ota.db
  ota step
} {SQLITE_CORRUPT}
do_test 4.5.2 {
  list [catch {ota close} msg] $msg
} {1 SQLITE_CORRUPT}
do_test 4.5.3 {
  sqlite3 dbo ota.db
  dbo eval { DELETE FROM ota_state WHERE k = 100 }
  dbo close 
} {}

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

do_test 4.7.1 {
  sqlite3 dbo ota.db
  dbo eval { UPDATE ota_state SET v = 1 WHERE k = 1 }
  dbo eval { UPDATE ota_state SET v = 'nosuchtable' WHERE k = 2 }
  dbo close
  sqlite3ota ota test.db ota.db
  ota step
} {SQLITE_ERROR}
do_test 4.7.2 {
  list [catch {ota close} msg] $msg
} {1 {SQLITE_ERROR - ota_state mismatch error}}

finish_test

# 2015 February 16
#
# The author disclaims copyright to this source code.  In place of
# a legal notice, here is a blessing:
#
#    May you do good and not evil.
#    May you find forgiveness for yourself and forgive others.
#    May you share freely, never taking more than you give.
#
#***********************************************************************
#

if {![info exists testdir]} {
  set testdir [file join [file dirname [info script]] .. .. test]
}
source $testdir/tester.tcl
source $testdir/lock_common.tcl
set ::testprefix ota12

set setup_sql {
  DROP TABLE IF EXISTS xx;
  DROP TABLE IF EXISTS xy;
  CREATE TABLE xx(a, b, c PRIMARY KEY);
  INSERT INTO xx VALUES(1, 2, 3);
  CREATE TABLE xy(a, b, c PRIMARY KEY);

  ATTACH 'ota.db' AS ota;
    DROP TABLE IF EXISTS data_xx;
    CREATE TABLE ota.data_xx(a, b, c, ota_control);
    INSERT INTO data_xx VALUES(4, 5, 6, 0);
    INSERT INTO data_xx VALUES(7, 8, 9, 0);
    CREATE TABLE ota.data_xy(a, b, c, ota_control);
    INSERT INTO data_xy VALUES(10, 11, 12, 0);
  DETACH ota;
}

do_multiclient_test tn {

  # Initialize a target (test.db) and ota (ota.db) database.
  #
  forcedelete ota.db
  sql1 $setup_sql

  # Using connection 2, open a read transaction on the target database.
  # OTA will still be able to generate "test.db-oal", but it will not be
  # able to rename it to "test.db-wal".
  #
  do_test 1.$tn.1 {
    sql2 { BEGIN; SELECT * FROM xx; }
  } {1 2 3}
  do_test 1.$tn.2 {
    sqlite3ota ota test.db ota.db
    while 1 {
      set res [ota step]
      if {$res!="SQLITE_OK"} break
    }
    set res
  } {SQLITE_BUSY}

  do_test 1.$tn.3 { sql2 { SELECT * FROM xx; } } {1 2 3}
  do_test 1.$tn.4 { sql2 { SELECT * FROM xy; } } {}
  do_test 1.$tn.5 {
    list [file exists test.db-wal] [file exists test.db-oal]
  } {0 1}
  do_test 1.$tn.6 { sql2 COMMIT } {}

  # The ota object that hit the SQLITE_BUSY error above cannot be reused.
  # It is stuck in a permanent SQLITE_BUSY state at this point.
  #
  do_test 1.$tn.7 { ota step } {SQLITE_BUSY}
  do_test 1.$tn.8 { 
    list [catch { ota close } msg] $msg 
  } {1 SQLITE_BUSY}

  do_test 1.$tn.9.1 { sql2 { BEGIN EXCLUSIVE } } {}
  do_test 1.$tn.9.2 {
    sqlite3ota ota test.db ota.db
    ota step
  } {SQLITE_BUSY}
  do_test 1.$tn.9.3 {
    list [catch { ota close } msg] $msg 
  } {1 {SQLITE_BUSY - database is locked}}
  do_test 1.$tn.9.4 { sql2 COMMIT } {}

  sqlite3ota ota test.db ota.db
  do_test 1.$tn.10.1 { sql2 { BEGIN EXCLUSIVE } } {}
  do_test 1.$tn.10.2 {
    ota step
  } {SQLITE_BUSY}
  do_test 1.$tn.10.3 {
    list [catch { ota close } msg] $msg 
  } {1 SQLITE_BUSY}
  do_test 1.$tn.10.4 { sql2 COMMIT } {}

  # A new ota object can finish the work though.
  #
  do_test 1.$tn.11 {
    sqlite3ota ota test.db ota.db
    ota step
  } {SQLITE_OK}
  do_test 1.$tn.12 {
    list [file exists test.db-wal] [file exists test.db-oal]
  } {1 0}
  do_test 1.$tn.13 {
    while 1 {
      set res [ota step]
      if {$res!="SQLITE_OK"} break
    }
    set res
  } {SQLITE_DONE}

  do_test 1.$tn.14 {
    ota close
  } {SQLITE_DONE}
}

do_multiclient_test tn {

  # Initialize a target (test.db) and ota (ota.db) database.
  #
  forcedelete ota.db
  sql1 $setup_sql

  do_test 2.$tn.1 {
    sqlite3ota ota test.db ota.db
    while {[file exists test.db-wal]==0} {
      if {[ota step]!="SQLITE_OK"} {error "problem here...."}
    }
    ota close
  } {SQLITE_OK}


  do_test 2.$tn.2 { sql2 { BEGIN IMMEDIATE } } {}

  do_test 2.$tn.3 { 
    sqlite3ota ota test.db ota.db
    ota step 
  } {SQLITE_BUSY}

  do_test 2.$tn.4 { list [catch { ota close } msg] $msg } {1 SQLITE_BUSY}

  do_test 2.$tn.5 { 
    sql2 { SELECT * FROM xx ; COMMIT }
  } {1 2 3 4 5 6 7 8 9}

  do_test 2.$tn.6 {
    sqlite3ota ota test.db ota.db
    ota step
    ota close
  } {SQLITE_OK}

  do_test 2.$tn.7 { sql2 { BEGIN EXCLUSIVE } } {}

  do_test 2.$tn.8 { 
    sqlite3ota ota test.db ota.db
    ota step 
  } {SQLITE_BUSY}
  do_test 2.$tn.9 { list [catch { ota close } msg] $msg } {1 SQLITE_BUSY}
  do_test 2.$tn.10 { 
    sql2 { SELECT * FROM xx ; COMMIT }
  } {1 2 3 4 5 6 7 8 9}

  do_test 2.$tn.11 {
    sqlite3ota ota test.db ota.db
    while {[ota step]=="SQLITE_OK"} {}
    ota close
  } {SQLITE_DONE}

}

finish_test

# 2015 February 16
#
# The author disclaims copyright to this source code.  In place of
# a legal notice, here is a blessing:
#
#    May you do good and not evil.
#    May you find forgiveness for yourself and forgive others.
#    May you share freely, never taking more than you give.
#
#***********************************************************************
#
# Test an OTA update that features lots of different ota_control strings
# for UPDATE statements. This tests OTA's internal UPDATE statement cache.
#

if {![info exists testdir]} {
  set testdir [file join [file dirname [info script]] .. .. test]
}
source $testdir/tester.tcl
source $testdir/lock_common.tcl
set ::testprefix ota13

do_execsql_test 1.0 {
  CREATE TABLE t1(a PRIMARY KEY, b, c, d, e, f, g, h);
  WITH ii(i) AS (SELECT 0 UNION ALL SELECT i+1 FROM ii WHERE i<127)
  INSERT INTO t1 SELECT i, 0, 0, 0, 0, 0, 0, 0 FROM ii;
}

forcedelete ota.db
do_execsql_test 1.1 {
  ATTACH 'ota.db' AS ota;
  CREATE TABLE ota.data_t1(a, b, c, d, e, f, g, h, ota_control);
}

do_test 1.2 {
  for {set i 0} {$i<128} {incr i} {
    set control "."
    for {set bit 6} {$bit>=0} {incr bit -1} {
      if { $i & (1<<$bit) } {
        append control "x"
      } else {
        append control "."
      }
    }
    execsql { INSERT INTO data_t1 VALUES($i, 1, 1, 1, 1, 1, 1, 1, $control) }
  }
} {}

do_test 1.3 {
  sqlite3ota ota test.db ota.db
  while 1 {
    set rc [ota step]
    if {$rc!="SQLITE_OK"} break
  }
  ota close
} {SQLITE_DONE}

do_execsql_test 1.4 {
  SELECT count(*) FROM t1 WHERE
  a == ( (b<<6) + (c<<5) + (d<<4) + (e<<3) + (f<<2) + (g<<1) + (h<<0) )
} {128}


finish_test

# 2014 August 30
#
# The author disclaims copyright to this source code.  In place of
# a legal notice, here is a blessing:
#
#    May you do good and not evil.
#    May you find forgiveness for yourself and forgive others.
#    May you share freely, never taking more than you give.
#
#***********************************************************************
#

if {![info exists testdir]} {
  set testdir [file join [file dirname [info script]] .. .. test]
}
source $testdir/tester.tcl
set ::testprefix ota3


# Run the OTA in file $ota on target database $target until completion.
#
proc run_ota {target ota} {
  sqlite3ota ota $target $ota
  while { [ota step]=="SQLITE_OK" } {}
  ota close
}

forcedelete test.db-oal ota.db
db close
sqlite3_shutdown
sqlite3_config_uri 1
reset_db

#--------------------------------------------------------------------
# Test that for an OTA to be applied, no corruption results if the
# affinities on the source and target table do not match.
#
do_execsql_test 1.0 {
  CREATE TABLE x1(a INTEGER PRIMARY KEY, b TEXT, c REAL);
  CREATE INDEX i1 ON x1(b, c);
} {}

do_test 1.1 {
  sqlite3 db2 ota.db
  db2 eval {
    CREATE TABLE data_x1(a, b, c, ota_control);
    INSERT INTO data_x1 VALUES(1, '123', '123', 0);
    INSERT INTO data_x1 VALUES(2, 123, 123, 0);
  }
  db2 close
  run_ota test.db ota.db
} {SQLITE_DONE}

do_execsql_test 1.2 {
  PRAGMA integrity_check;
} {ok}

#--------------------------------------------------------------------
# Test that NULL values may not be inserted into INTEGER PRIMARY KEY
# columns.
#
forcedelete ota.db
reset_db

do_execsql_test 2.0 {
  CREATE TABLE x1(a INTEGER PRIMARY KEY, b TEXT, c REAL);
  CREATE INDEX i1 ON x1(b, c);
} {}

foreach {tn otadb} {
  1 {
    CREATE TABLE data_x1(a, b, c, ota_control);
    INSERT INTO data_x1 VALUES(NULL, 'a', 'b', 0);
  }

  2 {
    CREATE TABLE data_x1(c, b, a, ota_control);
    INSERT INTO data_x1 VALUES('b', 'a', NULL, 0);
  }
} {
  do_test 2.$tn.1 {
    forcedelete ota.db
    sqlite3 db2 ota.db
    db2 eval $otadb
    db2 close
    list [catch { run_ota test.db ota.db } msg] $msg
  } {1 {SQLITE_MISMATCH - datatype mismatch}}

  do_execsql_test 2.1.2 {
    PRAGMA integrity_check;
  } {ok}
}

#--------------------------------------------------------------------
# Test that missing columns are detected.
#
forcedelete ota.db
reset_db

do_execsql_test 2.0 {
  CREATE TABLE x1(a INTEGER PRIMARY KEY, b, c);
  CREATE INDEX i1 ON x1(b, c);
} {}

do_test 2.1 {
  sqlite3 db2 ota.db
  db2 eval {
    CREATE TABLE data_x1(a, b, ota_control);
    INSERT INTO data_x1 VALUES(1, 'a', 0);
  }
  db2 close
  list [catch { run_ota test.db ota.db } msg] $msg
} {1 {SQLITE_ERROR - column missing from data_x1: c}}

do_execsql_test 2.2 {
  PRAGMA integrity_check;
} {ok}

# Also extra columns.
#
do_execsql_test 2.3 {
  CREATE TABLE x2(a INTEGER PRIMARY KEY, b, c);
  CREATE INDEX i2 ON x2(b, c);
} {}

do_test 2.4 {
  forcedelete ota.db
  sqlite3 db2 ota.db
  db2 eval {
    CREATE TABLE data_x2(a, b, c, d, ota_control);
    INSERT INTO data_x2 VALUES(1, 'a', 2, 3, 0);
  }
  db2 close
  list [catch { run_ota test.db ota.db } msg] $msg
} {1 SQLITE_ERROR}

do_execsql_test 2.5 {
  PRAGMA integrity_check;
} {ok}


#-------------------------------------------------------------------------
# Test that sqlite3ota_create_vfs() returns an error if the requested 
# parent VFS is unknown.
#
# And that nothing disasterous happens if a VFS name passed to
# sqlite3ota_destroy_vfs() is unknown or not an OTA vfs.
#
do_test 3.1 {
  list [catch {sqlite3ota_create_vfs xyz nosuchparent} msg] $msg
} {1 SQLITE_NOTFOUND}

do_test 3.2 {
  sqlite3ota_destroy_vfs nosuchvfs
  sqlite3ota_destroy_vfs unix
  sqlite3ota_destroy_vfs win32
} {}

#-------------------------------------------------------------------------
# Test that it is an error to specify an explicit VFS that does not 
# include ota VFS functionality.
#
do_test 4.1 {
  testvfs tvfs
  sqlite3ota ota file:test.db?vfs=tvfs ota.db 
  list [catch { ota step } msg] $msg
} {0 SQLITE_ERROR}
do_test 4.2 {
  list [catch { ota close } msg] $msg
} {1 {SQLITE_ERROR - ota vfs not found}}
tvfs delete

#-------------------------------------------------------------------------
# Test a large ota update to ensure that wal_autocheckpoint does not get
# in the way.
#
forcedelete ota.db
reset_db
do_execsql_test 5.1 {
  CREATE TABLE x1(a, b, c, PRIMARY KEY(a)) WITHOUT ROWID;
  CREATE INDEX i1 ON x1(a);

  ATTACH 'ota.db' AS ota;
  CREATE TABLE ota.data_x1(a, b, c, ota_control);
  WITH s(a, b, c) AS (
    SELECT randomblob(300), randomblob(300), 1
    UNION ALL
    SELECT randomblob(300), randomblob(300), c+1 FROM s WHERE c<2000
  )
  INSERT INTO data_x1 SELECT a, b, c, 0 FROM s;
}

do_test 5.2 {
  sqlite3ota ota test.db ota.db
  while {[ota step]=="SQLITE_OK" && [file exists test.db-wal]==0} {}
  ota close
} {SQLITE_OK}

do_test 5.3 {
  expr {[file size test.db-wal] > (1024 * 1200)}
} 1

do_test 6.1 { sqlite3ota_internal_test } {}

finish_test

# 2014 August 30
#
# The author disclaims copyright to this source code.  In place of
# a legal notice, here is a blessing:
#
#    May you do good and not evil.
#    May you find forgiveness for yourself and forgive others.
#    May you share freely, never taking more than you give.
#
#***********************************************************************
#
# Test some properties of the pager_ota_mode and ota_mode pragmas.
#

if {![info exists testdir]} {
  set testdir [file join [file dirname [info script]] .. .. test]
}
source $testdir/tester.tcl
set ::testprefix ota5


# Run the OTA in file $ota on target database $target until completion.
#
proc run_ota {target ota} {
  sqlite3ota ota $target $ota
  while { [ota step]=="SQLITE_OK" } {}
  ota close
}


# Run the OTA in file $ota on target database $target one step at a
# time until completion.
#
proc step_ota {target ota} {
  while 1 {
    sqlite3ota ota $target $ota
    set rc [ota step]
    ota close
    if {$rc != "SQLITE_OK"} break
  }
  set rc
}

# Return a list of the primary key columns for table $tbl in the database
# opened by database handle $db.
#
proc pkcols {db tbl} {
  set ret [list]
  $db eval "PRAGMA table_info = '$tbl'" {
    if {$pk} { lappend ret $name }
  }
  return $ret
}

# Return a list of all columns for table $tbl in the database opened by 
# database handle $db.
#
proc allcols {db tbl} {
  set ret [list]
  $db eval "PRAGMA table_info = '$tbl'" {
    lappend ret $name
  }
  return $ret
}

# Return a checksum on all tables and data in the main database attached
# to database handle $db. It is possible to add indexes without changing
# the checksum.
#
proc datacksum {db} {

  $db eval { SELECT name FROM sqlite_master WHERE type='table' } {
    append txt $name
    set cols [list]
    set order [list]
    set cnt 0
    $db eval "PRAGMA table_info = $name" x {
      lappend cols "quote($x(name))"
      lappend order [incr cnt]
    }
    set cols [join $cols ,]
    set order [join $order ,]
    append txt [$db eval "SELECT $cols FROM $name ORDER BY $order"]
  }
  return "[string length $txt]-[md5 $txt]"
}

proc ucontrol {args} {
  set ret ""
  foreach a $args {
    if {$a} {
      append ret .
    } else {
      append ret x
    }
  }
  return $ret
}

# Argument $target is the name of an SQLite database file. $sql is an SQL
# script containing INSERT, UPDATE and DELETE statements to execute against
# it. This command creates an OTA update database in file $ota that has
# the same effect as the script. The target database is not modified by
# this command.
#
proc generate_ota_db {target ota sql} {

  forcedelete $ota
  forcecopy $target copy.db

  # Evaluate the SQL script to modify the contents of copy.db.
  #
  sqlite3 dbOta copy.db
  dbOta eval $sql

  dbOta function ucontrol ucontrol
  
  # Evaluate the SQL script to modify the contents of copy.db.
  set ret [datacksum dbOta]

  dbOta eval { ATTACH $ota AS ota }
  dbOta eval { ATTACH $target AS orig }

  dbOta eval { SELECT name AS tbl FROM sqlite_master WHERE type = 'table' } {
    set pk [pkcols dbOta $tbl]
    set cols [allcols dbOta $tbl]

    # A WHERE clause to test that the PK columns match.
    #
    set where [list]
    foreach c $pk { lappend where "main.$tbl.$c IS orig.$tbl.$c" }
    set where [join $where " AND "]
    
    # A WHERE clause to test that all columns match.
    #
    set where2 [list]
    foreach c $cols { lappend where2 "main.$tbl.$c IS orig.$tbl.$c" }
    set ucontrol "ucontrol([join $where2 ,])"
    set where2 [join $where2 " AND "]

    # Create a data_xxx table in the OTA update database.
    dbOta eval "
      CREATE TABLE ota.data_$tbl AS SELECT *, '' AS ota_control 
      FROM main.$tbl LIMIT 0
    "

    # Find all new rows INSERTed by the script.
    dbOta eval "
      INSERT INTO ota.data_$tbl 
          SELECT *, 0 AS ota_control FROM main.$tbl
          WHERE NOT EXISTS (
            SELECT 1 FROM orig.$tbl WHERE $where
          )
    "
    
    # Find all old rows DELETEd by the script.
    dbOta eval "
      INSERT INTO ota.data_$tbl 
          SELECT *, 1 AS ota_control FROM orig.$tbl
          WHERE NOT EXISTS (
            SELECT 1 FROM main.$tbl WHERE $where
          )
    "
    
    # Find all rows UPDATEd by the script.
    set origcols [list]
    foreach c $cols { lappend origcols "main.$tbl.$c" }
    set origcols [join $origcols ,]
    dbOta eval "
      INSERT INTO ota.data_$tbl
          SELECT $origcols, $ucontrol AS ota_control 
          FROM orig.$tbl, main.$tbl
          WHERE $where AND NOT ($where2)
    "

  }

  dbOta close
  forcedelete copy.db

  return $ret
}

#-------------------------------------------------------------------------
#
do_execsql_test 1.0 {
  CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c);
  CREATE TABLE t2(x, y, z, PRIMARY KEY(y, z)) WITHOUT ROWID;

  INSERT INTO t1 VALUES(1, 2, 3);
  INSERT INTO t1 VALUES(2, 4, 6);
  INSERT INTO t1 VALUES(3, 6, 9);

  INSERT INTO t2 VALUES(1, 2, 3);
  INSERT INTO t2 VALUES(2, 4, 6);
  INSERT INTO t2 VALUES(3, 6, 9);
}
db close

set cksum [generate_ota_db test.db ota.db {
  INSERT INTO t1 VALUES(4, 8, 12);
  DELETE FROM t1 WHERE a = 2;
  UPDATE t1 SET c = 15 WHERE a=3;

  INSERT INTO t2 VALUES(4, 8, 12);
  DELETE FROM t2 WHERE x = 2;
  UPDATE t2 SET x = 15 WHERE z=9;
}]

foreach {tn idx} {
  1 {
  }
  2 {
    CREATE INDEX i1 ON t1(a, b, c);
    CREATE INDEX i2 ON t2(x, y, z);
  }
} {
  foreach cmd {run step} {
    forcecopy test.db test.db2
    forcecopy ota.db ota.db2

    sqlite3 db test.db2
    db eval $idx

    do_test 1.$tn.$cmd.1 {
      ${cmd}_ota test.db2 ota.db2
      datacksum db
    } $cksum

    do_test 1.$tn.$cmd.2 {
      db eval { PRAGMA integrity_check } 
    } {ok}

    db close
  }
}

#-------------------------------------------------------------------------
#
reset_db
do_execsql_test 2.0 {
  CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c, d, e);
  INSERT INTO t1 VALUES(-750250,'fyetckfaagjkzqjx',-185831,X'FEAD',444258.29);
  INSERT INTO t1 VALUES(649081,NULL,X'7DF25BF78778',-342324.63,'akvspktocwozo');
  INSERT INTO t1 VALUES(-133045,-44822.31,X'',287935,NULL);
  INSERT INTO t1 VALUES(202132,NULL,X'5399','cujsjtspryqeyovcdpz','m');
  INSERT INTO t1 VALUES(302910,NULL,'dvdhivtfkaedzhdcnn',-717113.41,688487);
  INSERT INTO t1 VALUES(-582327,X'7A267A',X'7E6B3CFE5CB9','zacuzilrok',-196478);
  INSERT INTO t1 VALUES(-190462,X'D1A087E7D68D9578','lsmleti',NULL,-928094);
  INSERT INTO t1 VALUES(-467665,176344.57,-536684.23,828876.22,X'903E');
  INSERT INTO t1 VALUES(-629138,632630.29,X'28D6',-774501,X'819BBBFC65');
  INSERT INTO t1 VALUES(-828110,-54379.24,-881121.44,X'',X'8D5A894F0D');

  CREATE TABLE t2(a PRIMARY KEY, b, c, d, e) WITHOUT ROWID;
  INSERT INTO t2 VALUES(-65174,X'AC1DBFFE27310F',-194471.08,347988,X'84041BA6F9BDDE86A8');
  INSERT INTO t2 VALUES('bzbpi',-952693.69,811628.25,NULL,-817434);
  INSERT INTO t2 VALUES(-643830,NULL,'n',NULL,'dio');
  INSERT INTO t2 VALUES('rovoenxxj',NULL,'owupbtdcoxxnvg',-119676,X'55431DFA');
  INSERT INTO t2 VALUES(899770,'jlygdl',X'DBCA4D1A',NULL,-631773);
  INSERT INTO t2 VALUES(334698.80,NULL,-697585.58,-89277,-817352);
  INSERT INTO t2 VALUES(X'1A9EB7547A4AAF38','aiprdhkpzdz','anw','szvjbwdvzucybpwwqjt',X'53');
  INSERT INTO t2 VALUES(713220,NULL,'hfcqhqzjuqplvkum',X'20B076075649DE','fthgpvqdyy');
  INSERT INTO t2 VALUES(763908,NULL,'xgslzcpvwfknbr',X'75',X'668146');
  INSERT INTO t2 VALUES(X'E1BA2B6BA27278','wjbpd',NULL,139341,-290086.15);
}
db close

set cksum [generate_ota_db test.db ota.db {
INSERT INTO t2 VALUES(222916.23,'idh',X'472C517405',X'E3',X'7C4F31824669');
INSERT INTO t2 VALUES('xcndjwafcoxwxizoktd',-319567.21,NULL,-720906.43,-577170);
INSERT INTO t2 VALUES(376369.99,-536058,'yoaiurfqupdscwc',X'29EC8A2542EC3953E9',-740485.22);
INSERT INTO t2 VALUES(X'0EFB4DC50693',-175590.83,X'1779E253CAB5B1789E',X'BC6903',NULL);
INSERT INTO t2 VALUES(-288299,'hfrp',NULL,528477,730676.77);
DELETE FROM t2 WHERE a < -60000;

UPDATE t2 SET b = 'pgnnaaoflnw' WHERE a = 'bzbpi';
UPDATE t2 SET c = -675583 WHERE a = 'rovoenxxj';
UPDATE t2 SET d = X'09CDF2B2C241' WHERE a = 713220;

INSERT INTO t1 VALUES(224938,'bmruycvfznhhnfmgqys','fr',854381,789143);
INSERT INTO t1 VALUES(-863931,-1386.26,X'2A058540C2FB5C',NULL,X'F9D5990A');
INSERT INTO t1 VALUES(673696,X'97301F0AC5735F44B5',X'440C',227999.92,-709599.79);
INSERT INTO t1 VALUES(-243640,NULL,-71718.11,X'1EEFEB38',X'8CC7C55D95E142FBA5');
INSERT INTO t1 VALUES(275893,X'',375606.30,X'0AF9EC334711FB',-468194);
DELETE FROM t1 WHERE a > 200000;

UPDATE t1 SET b = 'pgnnaaoflnw' WHERE a = -190462;
UPDATE t1 SET c = -675583 WHERE a = -467665;
UPDATE t1 SET d = X'09CDF2B2C241' WHERE a = -133045;

}]

foreach {tn idx} {
  1 {
  }
  2 {
    CREATE UNIQUE INDEX i1 ON t1(b, c, d);
    CREATE UNIQUE INDEX i2 ON t1(d, e, a);
    CREATE UNIQUE INDEX i3 ON t1(e, d, c, b);

    CREATE UNIQUE INDEX i4 ON t2(b, c, d);
    CREATE UNIQUE INDEX i5 ON t2(d, e, a);
    CREATE UNIQUE INDEX i6 ON t2(e, d, c, b);
  }
} {
  foreach cmd {run step} {
    forcecopy test.db test.db2
    forcecopy ota.db ota.db2

    sqlite3 db test.db2
    db eval $idx

    do_test 2.$tn.$cmd.1 {
      ${cmd}_ota test.db2 ota.db2
      datacksum db
    } $cksum

    do_test 2.$tn.$cmd.2 {
      db eval { PRAGMA integrity_check } 
    } {ok}

    db close
  }
}


finish_test