# Read and run TCL commands from standard input.  Used to implement
# the --tclsh option.
# This TCL script is the main driver script for the sqlite3_checker utility
# program.
#

# Special case:
#
#      sqlite3_checker --test FILENAME ARGS
#
# uses FILENAME in place of this script.
#
if {[lindex $argv 0]=="--test" && [llength $argv]>2} {
  set file [lindex $argv 1]
  set argv [lrange $argv 2 end]
  source $file
  exit 0
}

# Emulate a TCL shell
#
proc tclsh {} {
  set line {}

# This TCL script is the main driver script for the sqlite3_checker utility
# program.
#

# Special case:
#
#      sqlite3_checker --test FILENAME ARGS
#
# uses FILENAME in place of this script.
#
if {[lindex $argv 0]=="--test" && [llength $argv]>1} {
  set ::argv0 [lindex $argv 1]
  set argv [lrange $argv 2 end]
  source $argv0
  exit 0
}

# Emulate a TCL shell
#
proc tclsh {} {
  set line {}

To run these tests, first build sqlite3_checker:


>     make sqlite3_checker


Then run the "test.tcl" script using:


>     ./sqlite3_checker --test $path/test.tcl


Optionally add the full pathnames of individual *.test modules

# 2017-10-11

set testprefix checkfreelist

do_execsql_test 1.0 {
  PRAGMA page_size=1024;
  CREATE TABLE t1(a, b);
}

do_execsql_test 1.2 { SELECT checkfreelist('main') } {ok}
do_execsql_test 1.3 {
  WITH s(i) AS (
    SELECT 1 UNION ALL SELECT i+1 FROM s WHERE i<10000
  )
  INSERT INTO t1 SELECT randomblob(400), randomblob(400) FROM s;
  DELETE FROM t1 WHERE rowid%3;
  PRAGMA freelist_count;
} {6726}

do_execsql_test 1.4 { SELECT checkfreelist('main') } {ok}
do_execsql_test 1.5 {
  WITH freelist_trunk(i, d, n) AS (
    SELECT 1, NULL, sqlite_readint32(data, 32) FROM sqlite_dbpage WHERE pgno=1
      UNION ALL
    SELECT n, data, sqlite_readint32(data) 
    FROM freelist_trunk, sqlite_dbpage WHERE pgno=n
  )
  SELECT i FROM freelist_trunk WHERE i!=1;
} {
  10010 9716 9344 8970 8596 8223 7848 7475 7103 6728 6355 5983 5609 5235
  4861 4488 4113 3741 3368 2993 2620 2248 1873 1500 1126 753 378 5
}

do_execsql_test 1.6 { SELECT checkfreelist('main') } {ok}

proc set_int {blob idx newval} {
  binary scan $blob I* ints
  lset ints $idx $newval
  binary format I* $ints
}
db func set_int set_int

proc get_int {blob idx} {
  binary scan $blob I* ints
  lindex $ints $idx
}
db func get_int get_int

do_execsql_test 1.7 {
  BEGIN;
    UPDATE sqlite_dbpage 
      SET data = set_int(data, 1, get_int(data, 1)-1) 
      WHERE pgno=4861;
    SELECT checkfreelist('main');
  ROLLBACK;
} {{free-list count mismatch: actual=6725 header=6726}}

do_execsql_test 1.8 {
  BEGIN;
    UPDATE sqlite_dbpage 
      SET data = set_int(data, 5, (SELECT * FROM pragma_page_count)+1)
      WHERE pgno=4861;
    SELECT checkfreelist('main');
  ROLLBACK;
} {{leaf page 10093 is out of range (child 3 of trunk page 4861)}}

do_execsql_test 1.9 {
  BEGIN;
    UPDATE sqlite_dbpage 
      SET data = set_int(data, 5, 0)
      WHERE pgno=4861;
    SELECT checkfreelist('main');
  ROLLBACK;
} {{leaf page 0 is out of range (child 3 of trunk page 4861)}}

do_execsql_test 1.10 {
  BEGIN;
    UPDATE sqlite_dbpage 
      SET data = set_int(data, get_int(data, 1)+1, 0)
      WHERE pgno=5;
    SELECT checkfreelist('main');
  ROLLBACK;
} {{leaf page 0 is out of range (child 247 of trunk page 5)}}

do_execsql_test 1.11 {
  BEGIN;
    UPDATE sqlite_dbpage 
      SET data = set_int(data, 1, 249)
      WHERE pgno=5;
    SELECT checkfreelist('main');
  ROLLBACK;
} {{leaf count out of range (249) on trunk page 5}}

# 2017-10-11
#
set testprefix checkindex

do_execsql_test 1.0 {
  CREATE TABLE t1(a, b);
  CREATE INDEX i1 ON t1(a);
  INSERT INTO t1 VALUES('one', 2);
  INSERT INTO t1 VALUES('two', 4);
  INSERT INTO t1 VALUES('three', 6);
  INSERT INTO t1 VALUES('four', 8);
  INSERT INTO t1 VALUES('five', 10);

  CREATE INDEX i2 ON t1(a DESC);
} {}

proc incr_index_check {idx nStep} {
  set Q {
    SELECT errmsg, current_key FROM incremental_index_check($idx, $after)
    LIMIT $nStep
  }

  set res [list]
  while {1} {
    unset -nocomplain current_key
    set res1 [db eval $Q]
    if {[llength $res1]==0} break
    set res [concat $res $res1]
    set after [lindex $res end]
  }

  return $res
}

proc do_index_check_test {tn idx res} {
  uplevel [list do_execsql_test $tn.1 "
    SELECT errmsg, current_key FROM incremental_index_check('$idx');
  " $res]

  uplevel [list do_test $tn.2 "incr_index_check $idx 1" [list {*}$res]]
  uplevel [list do_test $tn.3 "incr_index_check $idx 2" [list {*}$res]]
  uplevel [list do_test $tn.4 "incr_index_check $idx 5" [list {*}$res]]
}


do_execsql_test 1.2 {
  SELECT errmsg IS NULL, current_key FROM incremental_index_check('i1');
} {
  1 'five',5
  1 'four',4
  1 'one',1
  1 'three',3
  1 'two',2
}

do_index_check_test 1.3 i1 {
  {} 'five',5
  {} 'four',4
  {} 'one',1
  {} 'three',3
  {} 'two',2
}

do_index_check_test 1.4 i2 {
  {} 'two',2
  {} 'three',3
  {} 'one',1
  {} 'four',4
  {} 'five',5
}

do_test 1.5 {
  set tblroot [db one { SELECT rootpage FROM sqlite_master WHERE name='t1' }]
  sqlite3_test_control SQLITE_TESTCTRL_IMPOSTER db main 1 $tblroot
  db eval {CREATE TABLE xt1(a, b)}
  sqlite3_test_control SQLITE_TESTCTRL_IMPOSTER db main 0 0

  execsql {
    UPDATE xt1 SET a='six' WHERE rowid=3;
    DELETE FROM xt1 WHERE rowid = 5;
  }

  sqlite3_test_control SQLITE_TESTCTRL_IMPOSTER db main 0 1
} {}

do_index_check_test 1.6 i1 {
  {row missing} 'five',5
  {} 'four',4
  {} 'one',1
  {row data mismatch} 'three',3
  {} 'two',2
}

do_index_check_test 1.7 i2 {
  {} 'two',2
  {row data mismatch} 'three',3
  {} 'one',1
  {} 'four',4
  {row missing} 'five',5
}

#--------------------------------------------------------------------------
do_execsql_test 2.0 {

  CREATE TABLE t2(a INTEGER PRIMARY KEY, b, c, d);

  INSERT INTO t2 VALUES(1, NULL, 1, 1);
  INSERT INTO t2 VALUES(2, 1, NULL, 1);
  INSERT INTO t2 VALUES(3, 1, 1, NULL);

  INSERT INTO t2 VALUES(4, 2, 2, 1);
  INSERT INTO t2 VALUES(5, 2, 2, 2);
  INSERT INTO t2 VALUES(6, 2, 2, 3);

  INSERT INTO t2 VALUES(7, 2, 2, 1);
  INSERT INTO t2 VALUES(8, 2, 2, 2);
  INSERT INTO t2 VALUES(9, 2, 2, 3);

  CREATE INDEX i3 ON t2(b, c, d);
  CREATE INDEX i4 ON t2(b DESC, c DESC, d DESC);
  CREATE INDEX i5 ON t2(d, c DESC, b);
} {}

do_index_check_test 2.1 i3 {
  {} NULL,1,1,1 
  {} 1,NULL,1,2 
  {} 1,1,NULL,3 
  {} 2,2,1,4 
  {} 2,2,1,7 
  {} 2,2,2,5
  {} 2,2,2,8 
  {} 2,2,3,6 
  {} 2,2,3,9
}

do_index_check_test 2.2 i4 {
  {} 2,2,3,6 
  {} 2,2,3,9
  {} 2,2,2,5
  {} 2,2,2,8 
  {} 2,2,1,4 
  {} 2,2,1,7 
  {} 1,1,NULL,3 
  {} 1,NULL,1,2 
  {} NULL,1,1,1 
}

do_index_check_test 2.3 i5 {
  {} NULL,1,1,3 
  {} 1,2,2,4 
  {} 1,2,2,7 
  {} 1,1,NULL,1 
  {} 1,NULL,1,2 
  {} 2,2,2,5 
  {} 2,2,2,8 
  {} 3,2,2,6 
  {} 3,2,2,9
}

#--------------------------------------------------------------------------
do_execsql_test 3.0 {

  CREATE TABLE t3(w, x, y, z PRIMARY KEY) WITHOUT ROWID;
  CREATE INDEX t3wxy ON t3(w, x, y);
  CREATE INDEX t3wxy2 ON t3(w DESC, x DESC, y DESC);

  INSERT INTO t3 VALUES(NULL, NULL, NULL, 1);
  INSERT INTO t3 VALUES(NULL, NULL, NULL, 2);
  INSERT INTO t3 VALUES(NULL, NULL, NULL, 3);

  INSERT INTO t3 VALUES('a', NULL, NULL, 4);
  INSERT INTO t3 VALUES('a', NULL, NULL, 5);
  INSERT INTO t3 VALUES('a', NULL, NULL, 6);

  INSERT INTO t3 VALUES('a', 'b', NULL, 7);
  INSERT INTO t3 VALUES('a', 'b', NULL, 8);
  INSERT INTO t3 VALUES('a', 'b', NULL, 9);

} {}

do_index_check_test 3.1 t3wxy {
  {} NULL,NULL,NULL,1 {} NULL,NULL,NULL,2 {} NULL,NULL,NULL,3 
  {} 'a',NULL,NULL,4  {} 'a',NULL,NULL,5  {} 'a',NULL,NULL,6 
  {} 'a','b',NULL,7   {} 'a','b',NULL,8   {} 'a','b',NULL,9 
}
do_index_check_test 3.2 t3wxy2 {
  {} 'a','b',NULL,7   {} 'a','b',NULL,8   {} 'a','b',NULL,9 
  {} 'a',NULL,NULL,4  {} 'a',NULL,NULL,5  {} 'a',NULL,NULL,6 
  {} NULL,NULL,NULL,1 {} NULL,NULL,NULL,2 {} NULL,NULL,NULL,3 
}

#--------------------------------------------------------------------------
# Test with an index that uses non-default collation sequences.
#
do_execsql_test 4.0 {
  CREATE TABLE t4(a INTEGER PRIMARY KEY, c1 TEXT, c2 TEXT);
  INSERT INTO t4 VALUES(1, 'aaa', 'bbb');
  INSERT INTO t4 VALUES(2, 'AAA', 'CCC');
  INSERT INTO t4 VALUES(3, 'aab', 'ddd');
  INSERT INTO t4 VALUES(4, 'AAB', 'EEE');

  CREATE INDEX t4cc ON t4(c1 COLLATE nocase, c2 COLLATE nocase);
}

do_index_check_test 4.1 t4cc {
  {} 'aaa','bbb',1 
  {} 'AAA','CCC',2 
  {} 'aab','ddd',3 
  {} 'AAB','EEE',4
}

do_test 4.2 {
  set tblroot [db one { SELECT rootpage FROM sqlite_master WHERE name='t4' }]
  sqlite3_test_control SQLITE_TESTCTRL_IMPOSTER db main 1 $tblroot
  db eval {CREATE TABLE xt4(a INTEGER PRIMARY KEY, c1 TEXT, c2 TEXT)}
  sqlite3_test_control SQLITE_TESTCTRL_IMPOSTER db main 0 0

  execsql {
    UPDATE xt4 SET c1='hello' WHERE rowid=2;
    DELETE FROM xt4 WHERE rowid = 3;
  }

  sqlite3_test_control SQLITE_TESTCTRL_IMPOSTER db main 0 1
} {}

do_index_check_test 4.3 t4cc {
  {} 'aaa','bbb',1 
  {row data mismatch} 'AAA','CCC',2 
  {row missing} 'aab','ddd',3 
  {} 'AAB','EEE',4
}

#--------------------------------------------------------------------------
# Test an index on an expression.
#
do_execsql_test 5.0 {
  CREATE TABLE t5(x INTEGER PRIMARY KEY, y TEXT, UNIQUE(y));
  INSERT INTO t5 VALUES(1, '{"x":1, "y":1}');
  INSERT INTO t5 VALUES(2, '{"x":2, "y":2}');
  INSERT INTO t5 VALUES(3, '{"x":3, "y":3}');
  INSERT INTO t5 VALUES(4, '{"w":4, "z":4}');
  INSERT INTO t5 VALUES(5, '{"x":5, "y":5}');

  CREATE INDEX t5x ON t5( json_extract(y, '$.x') );
  CREATE INDEX t5y ON t5( json_extract(y, '$.y') DESC );
}

do_index_check_test 5.1.1 t5x {
  {} NULL,4 {} 1,1 {} 2,2 {} 3,3 {} 5,5
}

do_index_check_test 5.1.2 t5y {
  {} 5,5 {} 3,3 {} 2,2 {} 1,1 {} NULL,4
}

do_index_check_test 5.1.3 sqlite_autoindex_t5_1 {
  {} {'{"w":4, "z":4}',4} 
  {} {'{"x":1, "y":1}',1} 
  {} {'{"x":2, "y":2}',2} 
  {} {'{"x":3, "y":3}',3} 
  {} {'{"x":5, "y":5}',5}
}

do_test 5.2 {
  set tblroot [db one { SELECT rootpage FROM sqlite_master WHERE name='t5' }]
  sqlite3_test_control SQLITE_TESTCTRL_IMPOSTER db main 1 $tblroot
  db eval {CREATE TABLE xt5(a INTEGER PRIMARY KEY, c1 TEXT);}
  sqlite3_test_control SQLITE_TESTCTRL_IMPOSTER db main 0 0
  execsql {
    UPDATE xt5 SET c1='{"x":22, "y":11}' WHERE rowid=1;
    DELETE FROM xt5 WHERE rowid = 4;
  }
  sqlite3_test_control SQLITE_TESTCTRL_IMPOSTER db main 0 1
} {}

do_index_check_test 5.3.1 t5x {
  {row missing} NULL,4 
  {row data mismatch} 1,1 
  {} 2,2 
  {} 3,3 
  {} 5,5
}

do_index_check_test 5.3.2 sqlite_autoindex_t5_1 {
  {row missing} {'{"w":4, "z":4}',4} 
  {row data mismatch} {'{"x":1, "y":1}',1} 
  {} {'{"x":2, "y":2}',2} 
  {} {'{"x":3, "y":3}',3} 
  {} {'{"x":5, "y":5}',5}
}

#-------------------------------------------------------------------------
#
do_execsql_test 6.0 {
  CREATE TABLE t6(x INTEGER PRIMARY KEY, y, z);
  CREATE INDEX t6x1 ON t6(y, /* one,two,three */ z);
  CREATE INDEX t6x2 ON t6(z, -- hello,world,
  y);

  CREATE INDEX t6x3 ON t6(z -- hello,world
  , y);

  INSERT INTO t6 VALUES(1, 2, 3);
  INSERT INTO t6 VALUES(4, 5, 6);
}

do_index_check_test 6.1 t6x1 {
  {} 2,3,1 
  {} 5,6,4
}
do_index_check_test 6.2 t6x2 {
  {} 3,2,1 
  {} 6,5,4
}
do_index_check_test 6.2 t6x3 {
  {} 3,2,1 
  {} 6,5,4
}

# Run this script using
#
#       sqlite3_checker --test $thisscript $testscripts
#
# The $testscripts argument is optional.  If omitted, all *.test files
# in the same directory as $thisscript are run.
#
set NTEST 0
set NERR  0


# Invoke the do_test procedure to run a single test
#
# The $expected parameter is the expected result.  The result is the return
# value from the last TCL command in $cmd.
#
# Normally, $expected must match exactly.  But if $expected is of the form
# "/regexp/" then regular expression matching is used.  If $expected is
# "~/regexp/" then the regular expression must NOT match.  If $expected is
# of the form "#/value-list/" then each term in value-list must be numeric
# and must approximately match the corresponding numeric term in $result.
# Values must match within 10%.  Or if the $expected term is A..B then the
# $result term must be in between A and B.
#
proc do_test {name cmd expected} {
  if {[info exists ::testprefix]} {
    set name "$::testprefix$name"
  }

  incr ::NTEST
  puts -nonewline $name...
  flush stdout

  if {[catch {uplevel #0 "$cmd;\n"} result]} {
    puts -nonewline $name...
    puts "\nError: $result"
    incr ::NERR
  } else {
    set ok [expr {[string compare $result $expected]==0}]
    if {!$ok} {
      puts "\n!  $name expected: \[$expected\]\n! $name got:      \[$result\]"
      incr ::NERR
    } else {
      puts " Ok"
    }
  }
  flush stdout
}

#
#   do_execsql_test TESTNAME SQL RES
#
proc do_execsql_test {testname sql {result {}}} {
  uplevel [list do_test $testname [list db eval $sql] [list {*}$result]]
}

if {[llength $argv]==0} {
  set dir [file dirname $argv0]
  set argv [glob -nocomplain $dir/*.test]
}
foreach testfile $argv {
  file delete -force test.db
  sqlite3 db test.db
  source $testfile
  catch {db close}
}
puts "$NERR errors out of $NTEST tests"

# 2017-10-11
#
# 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 implements regression tests for SQLite library.  The
# focus of this file is testing the checkfreelist extension.
#

set testdir [file dirname $argv0]
source $testdir/tester.tcl
set testprefix checkfreelist

ifcapable !vtab||!compound {
  finish_test
  return
}

if {[file exists ../checkfreelist.so]==0} {
  finish_test
  return
}

do_execsql_test 1.0 {
  CREATE TABLE t1(a, b);
}

db enable_load_extension 1
do_execsql_test 1.1 {
  SELECT load_extension('../checkfreelist.so');
} {{}}

do_execsql_test 1.2 { SELECT checkfreelist('main') } {ok}
do_execsql_test 1.3 {
  WITH s(i) AS (
    SELECT 1 UNION ALL SELECT i+1 FROM s WHERE i<10000
  )
  INSERT INTO t1 SELECT randomblob(400), randomblob(400) FROM s;
  DELETE FROM t1 WHERE rowid%3;
  PRAGMA freelist_count;
} {6726}

do_execsql_test 1.4 { SELECT checkfreelist('main') } {ok}
do_execsql_test 1.5 {
  WITH freelist_trunk(i, d, n) AS (
    SELECT 1, NULL, sqlite_readint32(data, 32) FROM sqlite_dbpage WHERE pgno=1
      UNION ALL
    SELECT n, data, sqlite_readint32(data) 
    FROM freelist_trunk, sqlite_dbpage WHERE pgno=n
  )
  SELECT i FROM freelist_trunk WHERE i!=1;
} {
  10010 9716 9344 8970 8596 8223 7848 7475 7103 6728 6355 5983 5609 5235
  4861 4488 4113 3741 3368 2993 2620 2248 1873 1500 1126 753 378 5
}

do_execsql_test 1.6 { SELECT checkfreelist('main') } {ok}

proc set_int {blob idx newval} {
  binary scan $blob I* ints
  lset ints $idx $newval
  binary format I* $ints
}
db func set_int set_int

proc get_int {blob idx} {
  binary scan $blob I* ints
  lindex $ints $idx
}
db func get_int get_int

do_execsql_test 1.7 {
  BEGIN;
    UPDATE sqlite_dbpage 
      SET data = set_int(data, 1, get_int(data, 1)-1) 
      WHERE pgno=4861;
    SELECT checkfreelist('main');
  ROLLBACK;
} {{free-list count mismatch: actual=6725 header=6726}}

do_execsql_test 1.8 {
  BEGIN;
    UPDATE sqlite_dbpage 
      SET data = set_int(data, 5, (SELECT * FROM pragma_page_count)+1)
      WHERE pgno=4861;
    SELECT checkfreelist('main');
  ROLLBACK;
} {{leaf page 10093 is out of range (child 3 of trunk page 4861)}}

do_execsql_test 1.9 {
  BEGIN;
    UPDATE sqlite_dbpage 
      SET data = set_int(data, 5, 0)
      WHERE pgno=4861;
    SELECT checkfreelist('main');
  ROLLBACK;
} {{leaf page 0 is out of range (child 3 of trunk page 4861)}}

do_execsql_test 1.10 {
  BEGIN;
    UPDATE sqlite_dbpage 
      SET data = set_int(data, get_int(data, 1)+1, 0)
      WHERE pgno=5;
    SELECT checkfreelist('main');
  ROLLBACK;
} {{leaf page 0 is out of range (child 247 of trunk page 5)}}

do_execsql_test 1.11 {
  BEGIN;
    UPDATE sqlite_dbpage 
      SET data = set_int(data, 1, 249)
      WHERE pgno=5;
    SELECT checkfreelist('main');
  ROLLBACK;
} {{leaf count out of range (249) on trunk page 5}}

finish_test

# 2017-10-11
#
# 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 implements regression tests for SQLite library.  The
# focus of this file is testing the checkindex extension.
#

set testdir [file dirname $argv0]
source $testdir/tester.tcl
set testprefix checkindex

ifcapable !vtab||!compound {
  finish_test
  return
}

if {[file exists ../checkindex.so]==0} {
  finish_test
  return
}

do_execsql_test 1.0 {
  CREATE TABLE t1(a, b);
  CREATE INDEX i1 ON t1(a);
  INSERT INTO t1 VALUES('one', 2);
  INSERT INTO t1 VALUES('two', 4);
  INSERT INTO t1 VALUES('three', 6);
  INSERT INTO t1 VALUES('four', 8);
  INSERT INTO t1 VALUES('five', 10);

  CREATE INDEX i2 ON t1(a DESC);
}

db enable_load_extension 1
do_execsql_test 1.1 {
  SELECT load_extension('../checkindex.so');
} {{}}

proc incr_index_check {idx nStep} {
  set Q {
    SELECT errmsg, current_key FROM incremental_index_check($idx, $after)
    LIMIT $nStep
  }

  set res [list]
  while {1} {
    unset -nocomplain current_key
    set res1 [db eval $Q]
    if {[llength $res1]==0} break
    set res [concat $res $res1]
    set after [lindex $res end]
  }

  return $res
}

proc do_index_check_test {tn idx res} {
  uplevel [list do_execsql_test $tn.1 "
    SELECT errmsg, current_key FROM incremental_index_check('$idx');
  " $res]

  uplevel [list do_test $tn.2 "incr_index_check $idx 1" [list {*}$res]]
  uplevel [list do_test $tn.3 "incr_index_check $idx 2" [list {*}$res]]
  uplevel [list do_test $tn.4 "incr_index_check $idx 5" [list {*}$res]]
}


do_execsql_test 1.2 {
  SELECT errmsg IS NULL, current_key FROM incremental_index_check('i1');
} {
  1 'five',5
  1 'four',4
  1 'one',1
  1 'three',3
  1 'two',2
}

do_index_check_test 1.3 i1 {
  {} 'five',5
  {} 'four',4
  {} 'one',1
  {} 'three',3
  {} 'two',2
}

do_index_check_test 1.4 i2 {
  {} 'two',2
  {} 'three',3
  {} 'one',1
  {} 'four',4
  {} 'five',5
}

do_test 1.5 {
  set tblroot [db one { SELECT rootpage FROM sqlite_master WHERE name='t1' }]
  sqlite3_test_control SQLITE_TESTCTRL_IMPOSTER db main 1 $tblroot
  db eval {CREATE TABLE xt1(a, b)}
  sqlite3_test_control SQLITE_TESTCTRL_IMPOSTER db main 0 0

  execsql {
    UPDATE xt1 SET a='six' WHERE rowid=3;
    DELETE FROM xt1 WHERE rowid = 5;
  }

  sqlite3_test_control SQLITE_TESTCTRL_IMPOSTER db main 0 1
} {}

do_index_check_test 1.6 i1 {
  {row missing} 'five',5
  {} 'four',4
  {} 'one',1
  {row data mismatch} 'three',3
  {} 'two',2
}

do_index_check_test 1.7 i2 {
  {} 'two',2
  {row data mismatch} 'three',3
  {} 'one',1
  {} 'four',4
  {row missing} 'five',5
}

#--------------------------------------------------------------------------
do_execsql_test 2.0 {

  CREATE TABLE t2(a INTEGER PRIMARY KEY, b, c, d);

  INSERT INTO t2 VALUES(1, NULL, 1, 1);
  INSERT INTO t2 VALUES(2, 1, NULL, 1);
  INSERT INTO t2 VALUES(3, 1, 1, NULL);

  INSERT INTO t2 VALUES(4, 2, 2, 1);
  INSERT INTO t2 VALUES(5, 2, 2, 2);
  INSERT INTO t2 VALUES(6, 2, 2, 3);

  INSERT INTO t2 VALUES(7, 2, 2, 1);
  INSERT INTO t2 VALUES(8, 2, 2, 2);
  INSERT INTO t2 VALUES(9, 2, 2, 3);

  CREATE INDEX i3 ON t2(b, c, d);
  CREATE INDEX i4 ON t2(b DESC, c DESC, d DESC);
  CREATE INDEX i5 ON t2(d, c DESC, b);
}

do_index_check_test 2.1 i3 {
  {} NULL,1,1,1 
  {} 1,NULL,1,2 
  {} 1,1,NULL,3 
  {} 2,2,1,4 
  {} 2,2,1,7 
  {} 2,2,2,5
  {} 2,2,2,8 
  {} 2,2,3,6 
  {} 2,2,3,9
}

do_index_check_test 2.2 i4 {
  {} 2,2,3,6 
  {} 2,2,3,9
  {} 2,2,2,5
  {} 2,2,2,8 
  {} 2,2,1,4 
  {} 2,2,1,7 
  {} 1,1,NULL,3 
  {} 1,NULL,1,2 
  {} NULL,1,1,1 
}

do_index_check_test 2.3 i5 {
  {} NULL,1,1,3 
  {} 1,2,2,4 
  {} 1,2,2,7 
  {} 1,1,NULL,1 
  {} 1,NULL,1,2 
  {} 2,2,2,5 
  {} 2,2,2,8 
  {} 3,2,2,6 
  {} 3,2,2,9
}

#--------------------------------------------------------------------------
do_execsql_test 3.0 {

  CREATE TABLE t3(w, x, y, z PRIMARY KEY) WITHOUT ROWID;
  CREATE INDEX t3wxy ON t3(w, x, y);
  CREATE INDEX t3wxy2 ON t3(w DESC, x DESC, y DESC);

  INSERT INTO t3 VALUES(NULL, NULL, NULL, 1);
  INSERT INTO t3 VALUES(NULL, NULL, NULL, 2);
  INSERT INTO t3 VALUES(NULL, NULL, NULL, 3);

  INSERT INTO t3 VALUES('a', NULL, NULL, 4);
  INSERT INTO t3 VALUES('a', NULL, NULL, 5);
  INSERT INTO t3 VALUES('a', NULL, NULL, 6);

  INSERT INTO t3 VALUES('a', 'b', NULL, 7);
  INSERT INTO t3 VALUES('a', 'b', NULL, 8);
  INSERT INTO t3 VALUES('a', 'b', NULL, 9);

}

do_index_check_test 3.1 t3wxy {
  {} NULL,NULL,NULL,1 {} NULL,NULL,NULL,2 {} NULL,NULL,NULL,3 
  {} 'a',NULL,NULL,4  {} 'a',NULL,NULL,5  {} 'a',NULL,NULL,6 
  {} 'a','b',NULL,7   {} 'a','b',NULL,8   {} 'a','b',NULL,9 
}
do_index_check_test 3.2 t3wxy2 {
  {} 'a','b',NULL,7   {} 'a','b',NULL,8   {} 'a','b',NULL,9 
  {} 'a',NULL,NULL,4  {} 'a',NULL,NULL,5  {} 'a',NULL,NULL,6 
  {} NULL,NULL,NULL,1 {} NULL,NULL,NULL,2 {} NULL,NULL,NULL,3 
}

#--------------------------------------------------------------------------
# Test with an index that uses non-default collation sequences.
#
do_execsql_test 4.0 {
  CREATE TABLE t4(a INTEGER PRIMARY KEY, c1 TEXT, c2 TEXT);
  INSERT INTO t4 VALUES(1, 'aaa', 'bbb');
  INSERT INTO t4 VALUES(2, 'AAA', 'CCC');
  INSERT INTO t4 VALUES(3, 'aab', 'ddd');
  INSERT INTO t4 VALUES(4, 'AAB', 'EEE');

  CREATE INDEX t4cc ON t4(c1 COLLATE nocase, c2 COLLATE nocase);
}

do_index_check_test 4.1 t4cc {
  {} 'aaa','bbb',1 
  {} 'AAA','CCC',2 
  {} 'aab','ddd',3 
  {} 'AAB','EEE',4
}

do_test 4.2 {
  set tblroot [db one { SELECT rootpage FROM sqlite_master WHERE name='t4' }]
  sqlite3_test_control SQLITE_TESTCTRL_IMPOSTER db main 1 $tblroot
  db eval {CREATE TABLE xt4(a INTEGER PRIMARY KEY, c1 TEXT, c2 TEXT)}
  sqlite3_test_control SQLITE_TESTCTRL_IMPOSTER db main 0 0

  execsql {
    UPDATE xt4 SET c1='hello' WHERE rowid=2;
    DELETE FROM xt4 WHERE rowid = 3;
  }

  sqlite3_test_control SQLITE_TESTCTRL_IMPOSTER db main 0 1
} {}

do_index_check_test 4.3 t4cc {
  {} 'aaa','bbb',1 
  {row data mismatch} 'AAA','CCC',2 
  {row missing} 'aab','ddd',3 
  {} 'AAB','EEE',4
}

#--------------------------------------------------------------------------
# Test an index on an expression.
#
do_execsql_test 5.0 {
  CREATE TABLE t5(x INTEGER PRIMARY KEY, y TEXT, UNIQUE(y));
  INSERT INTO t5 VALUES(1, '{"x":1, "y":1}');
  INSERT INTO t5 VALUES(2, '{"x":2, "y":2}');
  INSERT INTO t5 VALUES(3, '{"x":3, "y":3}');
  INSERT INTO t5 VALUES(4, '{"w":4, "z":4}');
  INSERT INTO t5 VALUES(5, '{"x":5, "y":5}');

  CREATE INDEX t5x ON t5( json_extract(y, '$.x') );
  CREATE INDEX t5y ON t5( json_extract(y, '$.y') DESC );
}

do_index_check_test 5.1.1 t5x {
  {} NULL,4 {} 1,1 {} 2,2 {} 3,3 {} 5,5
}

do_index_check_test 5.1.2 t5y {
  {} 5,5 {} 3,3 {} 2,2 {} 1,1 {} NULL,4
}

do_index_check_test 5.1.3 sqlite_autoindex_t5_1 {
  {} {'{"w":4, "z":4}',4} 
  {} {'{"x":1, "y":1}',1} 
  {} {'{"x":2, "y":2}',2} 
  {} {'{"x":3, "y":3}',3} 
  {} {'{"x":5, "y":5}',5}
}

do_test 5.2 {
  set tblroot [db one { SELECT rootpage FROM sqlite_master WHERE name='t5' }]
  sqlite3_test_control SQLITE_TESTCTRL_IMPOSTER db main 1 $tblroot
  db eval {CREATE TABLE xt5(a INTEGER PRIMARY KEY, c1 TEXT);}
  sqlite3_test_control SQLITE_TESTCTRL_IMPOSTER db main 0 0
  execsql {
    UPDATE xt5 SET c1='{"x":22, "y":11}' WHERE rowid=1;
    DELETE FROM xt5 WHERE rowid = 4;
  }
  sqlite3_test_control SQLITE_TESTCTRL_IMPOSTER db main 0 1
} {}

do_index_check_test 5.3.1 t5x {
  {row missing} NULL,4 
  {row data mismatch} 1,1 
  {} 2,2 
  {} 3,3 
  {} 5,5
}

do_index_check_test 5.3.2 sqlite_autoindex_t5_1 {
  {row missing} {'{"w":4, "z":4}',4} 
  {row data mismatch} {'{"x":1, "y":1}',1} 
  {} {'{"x":2, "y":2}',2} 
  {} {'{"x":3, "y":3}',3} 
  {} {'{"x":5, "y":5}',5}
}

#-------------------------------------------------------------------------
#
do_execsql_test 6.0 {
  CREATE TABLE t6(x INTEGER PRIMARY KEY, y, z);
  CREATE INDEX t6x1 ON t6(y, /* one,two,three */ z);
  CREATE INDEX t6x2 ON t6(z, -- hello,world,
  y);

  CREATE INDEX t6x3 ON t6(z -- hello,world
  , y);

  INSERT INTO t6 VALUES(1, 2, 3);
  INSERT INTO t6 VALUES(4, 5, 6);
}

do_index_check_test 6.1 t6x1 {
  {} 2,3,1 
  {} 5,6,4
}
do_index_check_test 6.2 t6x2 {
  {} 3,2,1 
  {} 6,5,4
}
do_index_check_test 6.2 t6x3 {
  {} 3,2,1 
  {} 6,5,4
}

finish_test