1.1 --- /dev/null Thu Jan 01 00:00:00 1970 +0000
1.2 +++ b/os/persistentdata/persistentstorage/sqlite3api/TEST/TclScript/descidx2.test Fri Jun 15 03:10:57 2012 +0200
1.3 @@ -0,0 +1,179 @@
1.4 +# 2005 December 21
1.5 +#
1.6 +# The author disclaims copyright to this source code. In place of
1.7 +# a legal notice, here is a blessing:
1.8 +#
1.9 +# May you do good and not evil.
1.10 +# May you find forgiveness for yourself and forgive others.
1.11 +# May you share freely, never taking more than you give.
1.12 +#
1.13 +#*************************************************************************
1.14 +# This file implements regression tests for SQLite library. The
1.15 +# focus of this script is descending indices.
1.16 +#
1.17 +# $Id: descidx2.test,v 1.5 2008/03/19 00:21:31 drh Exp $
1.18 +#
1.19 +
1.20 +set testdir [file dirname $argv0]
1.21 +source $testdir/tester.tcl
1.22 +
1.23 +db eval {PRAGMA legacy_file_format=OFF}
1.24 +
1.25 +# This procedure sets the value of the file-format in file 'test.db'
1.26 +# to $newval. Also, the schema cookie is incremented.
1.27 +#
1.28 +proc set_file_format {newval} {
1.29 + hexio_write test.db 44 [hexio_render_int32 $newval]
1.30 + set schemacookie [hexio_get_int [hexio_read test.db 40 4]]
1.31 + incr schemacookie
1.32 + hexio_write test.db 40 [hexio_render_int32 $schemacookie]
1.33 + return {}
1.34 +}
1.35 +
1.36 +# This procedure returns the value of the file-format in file 'test.db'.
1.37 +#
1.38 +proc get_file_format {{fname test.db}} {
1.39 + return [hexio_get_int [hexio_read $fname 44 4]]
1.40 +}
1.41 +
1.42 +
1.43 +# Verify that the file format starts as 4
1.44 +#
1.45 +do_test descidx2-1.1 {
1.46 + execsql {
1.47 + CREATE TABLE t1(a,b);
1.48 + CREATE INDEX i1 ON t1(b ASC);
1.49 + }
1.50 + get_file_format
1.51 +} {4}
1.52 +do_test descidx2-1.2 {
1.53 + execsql {
1.54 + CREATE INDEX i2 ON t1(a DESC);
1.55 + }
1.56 + get_file_format
1.57 +} {4}
1.58 +
1.59 +# Before adding any information to the database, set the file format
1.60 +# back to three. Then close and reopen the database. With the file
1.61 +# format set to three, SQLite should ignore the DESC argument on the
1.62 +# index.
1.63 +#
1.64 +do_test descidx2-2.0 {
1.65 + set_file_format 3
1.66 + db close
1.67 + sqlite3 db test.db
1.68 + get_file_format
1.69 +} {3}
1.70 +
1.71 +# Put some information in the table and verify that the DESC
1.72 +# on the index is ignored.
1.73 +#
1.74 +do_test descidx2-2.1 {
1.75 + execsql {
1.76 + INSERT INTO t1 VALUES(1,1);
1.77 + INSERT INTO t1 VALUES(2,2);
1.78 + INSERT INTO t1 SELECT a+2, a+2 FROM t1;
1.79 + INSERT INTO t1 SELECT a+4, a+4 FROM t1;
1.80 + SELECT b FROM t1 WHERE a>3 AND a<7;
1.81 + }
1.82 +} {4 5 6}
1.83 +do_test descidx2-2.2 {
1.84 + execsql {
1.85 + SELECT a FROM t1 WHERE b>3 AND b<7;
1.86 + }
1.87 +} {4 5 6}
1.88 +do_test descidx2-2.3 {
1.89 + execsql {
1.90 + SELECT b FROM t1 WHERE a>=3 AND a<7;
1.91 + }
1.92 +} {3 4 5 6}
1.93 +do_test descidx2-2.4 {
1.94 + execsql {
1.95 + SELECT b FROM t1 WHERE a>3 AND a<=7;
1.96 + }
1.97 +} {4 5 6 7}
1.98 +do_test descidx2-2.5 {
1.99 + execsql {
1.100 + SELECT b FROM t1 WHERE a>=3 AND a<=7;
1.101 + }
1.102 +} {3 4 5 6 7}
1.103 +do_test descidx2-2.6 {
1.104 + execsql {
1.105 + SELECT a FROM t1 WHERE b>=3 AND b<=7;
1.106 + }
1.107 +} {3 4 5 6 7}
1.108 +
1.109 +# This procedure executes the SQL. Then it checks to see if the OP_Sort
1.110 +# opcode was executed. If an OP_Sort did occur, then "sort" is appended
1.111 +# to the result. If no OP_Sort happened, then "nosort" is appended.
1.112 +#
1.113 +# This procedure is used to check to make sure sorting is or is not
1.114 +# occurring as expected.
1.115 +#
1.116 +proc cksort {sql} {
1.117 + set ::sqlite_sort_count 0
1.118 + set data [execsql $sql]
1.119 + if {$::sqlite_sort_count} {set x sort} {set x nosort}
1.120 + lappend data $x
1.121 + return $data
1.122 +}
1.123 +
1.124 +# Test sorting using a descending index.
1.125 +#
1.126 +do_test descidx2-3.1 {
1.127 + cksort {SELECT a FROM t1 ORDER BY a}
1.128 +} {1 2 3 4 5 6 7 8 nosort}
1.129 +do_test descidx2-3.2 {
1.130 + cksort {SELECT a FROM t1 ORDER BY a ASC}
1.131 +} {1 2 3 4 5 6 7 8 nosort}
1.132 +do_test descidx2-3.3 {
1.133 + cksort {SELECT a FROM t1 ORDER BY a DESC}
1.134 +} {8 7 6 5 4 3 2 1 nosort}
1.135 +do_test descidx2-3.4 {
1.136 + cksort {SELECT b FROM t1 ORDER BY a}
1.137 +} {1 2 3 4 5 6 7 8 nosort}
1.138 +do_test descidx2-3.5 {
1.139 + cksort {SELECT b FROM t1 ORDER BY a ASC}
1.140 +} {1 2 3 4 5 6 7 8 nosort}
1.141 +do_test descidx2-3.6 {
1.142 + cksort {SELECT b FROM t1 ORDER BY a DESC}
1.143 +} {8 7 6 5 4 3 2 1 nosort}
1.144 +do_test descidx2-3.7 {
1.145 + cksort {SELECT a FROM t1 ORDER BY b}
1.146 +} {1 2 3 4 5 6 7 8 nosort}
1.147 +do_test descidx2-3.8 {
1.148 + cksort {SELECT a FROM t1 ORDER BY b ASC}
1.149 +} {1 2 3 4 5 6 7 8 nosort}
1.150 +do_test descidx2-3.9 {
1.151 + cksort {SELECT a FROM t1 ORDER BY b DESC}
1.152 +} {8 7 6 5 4 3 2 1 nosort}
1.153 +do_test descidx2-3.10 {
1.154 + cksort {SELECT b FROM t1 ORDER BY b}
1.155 +} {1 2 3 4 5 6 7 8 nosort}
1.156 +do_test descidx2-3.11 {
1.157 + cksort {SELECT b FROM t1 ORDER BY b ASC}
1.158 +} {1 2 3 4 5 6 7 8 nosort}
1.159 +do_test descidx2-3.12 {
1.160 + cksort {SELECT b FROM t1 ORDER BY b DESC}
1.161 +} {8 7 6 5 4 3 2 1 nosort}
1.162 +
1.163 +do_test descidx2-3.21 {
1.164 + cksort {SELECT a FROM t1 WHERE a>3 AND a<8 ORDER BY a}
1.165 +} {4 5 6 7 nosort}
1.166 +do_test descidx2-3.22 {
1.167 + cksort {SELECT a FROM t1 WHERE a>3 AND a<8 ORDER BY a ASC}
1.168 +} {4 5 6 7 nosort}
1.169 +do_test descidx2-3.23 {
1.170 + cksort {SELECT a FROM t1 WHERE a>3 AND a<8 ORDER BY a DESC}
1.171 +} {7 6 5 4 nosort}
1.172 +do_test descidx2-3.24 {
1.173 + cksort {SELECT b FROM t1 WHERE a>3 AND a<8 ORDER BY a}
1.174 +} {4 5 6 7 nosort}
1.175 +do_test descidx2-3.25 {
1.176 + cksort {SELECT b FROM t1 WHERE a>3 AND a<8 ORDER BY a ASC}
1.177 +} {4 5 6 7 nosort}
1.178 +do_test descidx2-3.26 {
1.179 + cksort {SELECT b FROM t1 WHERE a>3 AND a<8 ORDER BY a DESC}
1.180 +} {7 6 5 4 nosort}
1.181 +
1.182 +finish_test