# 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