# 2013 Jul 31
#
# 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 SELECT statement.
#

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

set ::testprefix kvstore


do_execsql_test 1.1 { 
  SELECT * FROM sqlite_kvstore ;
  CREATE TABLE t1(x);
} {}

do_execsql_test 1.2 { 
  SELECT quote(key), quote(value) FROM sqlite_kvstore ;
} [list     \
  x'011802' \
  x'052a1e1e035e7461626c657431743102435245415445205441424c45207431287829'
]

do_execsql_test 1.3 { 
  SELECT quote(key), quote(value) FROM sqlite_temp_kvstore ;
} {}


do_execsql_test 2.1 {
  CREATE TABLE t2(x PRIMARY KEY, y UNIQUE);
  INSERT INTO t2 VALUES(1, 2);
  INSERT INTO t2 VALUES(3, 4);
  CREATE INDEX i1 ON t2(y DESC, x DESC);
}
array unset ::data
db eval { SELECT quote(key) AS k, quote(value) AS v FROM sqlite_kvstore } {
  set ::data($k) $v
}
do_execsql_test 2.2 { 
  SELECT quote(key) FROM sqlite_kvstore ORDER BY key DESC;
} [lsort -decreasing [array names ::data]]
do_execsql_test 2.3 { 
  SELECT quote(key) FROM sqlite_kvstore ORDER BY key ASC;
} [lsort -incr [array names ::data]]

set i 0
foreach k [array names ::data] {
  do_execsql_test 2.4.$i "
    SELECT quote(value) FROM sqlite_kvstore WHERE key = $k
  " $::data($k)
  incr i
}

proc key_range {a b} {
  set res [list]
  foreach k [lsort [array names ::data]] {
    if {[string compare $k $a]>=0 && [string compare $k $b]<=0} {
      lappend res $k
    }
  }
  set res
}

set sorted_keys [lsort [array names ::data]]
set nKey [llength $sorted_keys]
for {set i 0} {$i < $nKey} {incr i} {
  for {set i2 $i} {$i2 < $nKey} {incr i2} {
    set k1 [lindex $sorted_keys $i]
    set k2 [lindex $sorted_keys $i2]

    set sql "SELECT quote(key) FROM sqlite_kvstore WHERE key>=$k1 AND key<=$k2"
    set s1 [lsort [key_range $k1 $k2]]
    set s2 [lsort -decr [key_range $k1 $k2]]

    do_execsql_test 2.4.1.$i.$i2.asc " $sql ORDER BY key ASC " $s1
    do_execsql_test 2.4.1.$i.$i2.desc " $sql ORDER BY key DESC " $s2

    set sql "SELECT quote(key) FROM sqlite_kvstore WHERE key>$k1 AND key<=$k2"
    set s1 [lrange [lsort [key_range $k1 $k2]] 1 end]
    set s2 [lsort -decr $s1]

    do_execsql_test 2.4.2.$i.$i2.asc " $sql ORDER BY key ASC " $s1
    do_execsql_test 2.4.2.$i.$i2.desc " $sql ORDER BY key DESC " $s2

    set sql "SELECT quote(key) FROM sqlite_kvstore WHERE key>=$k1 AND key<$k2"
    set s1 [lrange [lsort [key_range $k1 $k2]] 0 end-1]
    set s2 [lsort -decr $s1]

    do_execsql_test 2.4.3.$i.$i2.asc " $sql ORDER BY key ASC " $s1
    do_execsql_test 2.4.3.$i.$i2.desc " $sql ORDER BY key DESC " $s2

    set sql "SELECT quote(key) FROM sqlite_kvstore WHERE key>$k1 AND key<$k2"
    set s1 [lrange [lsort [key_range $k1 $k2]] 1 end-1]
    set s2 [lsort -decr $s1]

    do_execsql_test 2.4.4.$i.$i2.asc " $sql ORDER BY key ASC " $s1
    do_execsql_test 2.4.4.$i.$i2.desc " $sql ORDER BY key DESC " $s2
    do_execsql_test 2.4.4.$i.$i2.asc " $sql ORDER BY key ASC " $s1
    do_execsql_test 2.4.4.$i.$i2.desc " $sql ORDER BY key DESC " $s2

  }
}


 





finish_test

