1.1 --- /dev/null Thu Jan 01 00:00:00 1970 +0000
1.2 +++ b/os/persistentdata/persistentstorage/sqlite3api/TEST/TclScript/descidx1.test Fri Jun 15 03:10:57 2012 +0200
1.3 @@ -0,0 +1,359 @@
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: descidx1.test,v 1.10 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 descidx1-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 descidx1-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 +# Put some information in the table and verify that the descending
1.60 +# index actually works.
1.61 +#
1.62 +do_test descidx1-2.1 {
1.63 + execsql {
1.64 + INSERT INTO t1 VALUES(1,1);
1.65 + INSERT INTO t1 VALUES(2,2);
1.66 + INSERT INTO t1 SELECT a+2, a+2 FROM t1;
1.67 + INSERT INTO t1 SELECT a+4, a+4 FROM t1;
1.68 + SELECT b FROM t1 WHERE a>3 AND a<7;
1.69 + }
1.70 +} {6 5 4}
1.71 +do_test descidx1-2.2 {
1.72 + execsql {
1.73 + SELECT a FROM t1 WHERE b>3 AND b<7;
1.74 + }
1.75 +} {4 5 6}
1.76 +do_test descidx1-2.3 {
1.77 + execsql {
1.78 + SELECT b FROM t1 WHERE a>=3 AND a<7;
1.79 + }
1.80 +} {6 5 4 3}
1.81 +do_test descidx1-2.4 {
1.82 + execsql {
1.83 + SELECT b FROM t1 WHERE a>3 AND a<=7;
1.84 + }
1.85 +} {7 6 5 4}
1.86 +do_test descidx1-2.5 {
1.87 + execsql {
1.88 + SELECT b FROM t1 WHERE a>=3 AND a<=7;
1.89 + }
1.90 +} {7 6 5 4 3}
1.91 +do_test descidx1-2.6 {
1.92 + execsql {
1.93 + SELECT a FROM t1 WHERE b>=3 AND b<=7;
1.94 + }
1.95 +} {3 4 5 6 7}
1.96 +
1.97 +# This procedure executes the SQL. Then it checks to see if the OP_Sort
1.98 +# opcode was executed. If an OP_Sort did occur, then "sort" is appended
1.99 +# to the result. If no OP_Sort happened, then "nosort" is appended.
1.100 +#
1.101 +# This procedure is used to check to make sure sorting is or is not
1.102 +# occurring as expected.
1.103 +#
1.104 +proc cksort {sql} {
1.105 + set ::sqlite_sort_count 0
1.106 + set data [execsql $sql]
1.107 + if {$::sqlite_sort_count} {set x sort} {set x nosort}
1.108 + lappend data $x
1.109 + return $data
1.110 +}
1.111 +
1.112 +# Test sorting using a descending index.
1.113 +#
1.114 +do_test descidx1-3.1 {
1.115 + cksort {SELECT a FROM t1 ORDER BY a}
1.116 +} {1 2 3 4 5 6 7 8 nosort}
1.117 +do_test descidx1-3.2 {
1.118 + cksort {SELECT a FROM t1 ORDER BY a ASC}
1.119 +} {1 2 3 4 5 6 7 8 nosort}
1.120 +do_test descidx1-3.3 {
1.121 + cksort {SELECT a FROM t1 ORDER BY a DESC}
1.122 +} {8 7 6 5 4 3 2 1 nosort}
1.123 +do_test descidx1-3.4 {
1.124 + cksort {SELECT b FROM t1 ORDER BY a}
1.125 +} {1 2 3 4 5 6 7 8 nosort}
1.126 +do_test descidx1-3.5 {
1.127 + cksort {SELECT b FROM t1 ORDER BY a ASC}
1.128 +} {1 2 3 4 5 6 7 8 nosort}
1.129 +do_test descidx1-3.6 {
1.130 + cksort {SELECT b FROM t1 ORDER BY a DESC}
1.131 +} {8 7 6 5 4 3 2 1 nosort}
1.132 +do_test descidx1-3.7 {
1.133 + cksort {SELECT a FROM t1 ORDER BY b}
1.134 +} {1 2 3 4 5 6 7 8 nosort}
1.135 +do_test descidx1-3.8 {
1.136 + cksort {SELECT a FROM t1 ORDER BY b ASC}
1.137 +} {1 2 3 4 5 6 7 8 nosort}
1.138 +do_test descidx1-3.9 {
1.139 + cksort {SELECT a FROM t1 ORDER BY b DESC}
1.140 +} {8 7 6 5 4 3 2 1 nosort}
1.141 +do_test descidx1-3.10 {
1.142 + cksort {SELECT b FROM t1 ORDER BY b}
1.143 +} {1 2 3 4 5 6 7 8 nosort}
1.144 +do_test descidx1-3.11 {
1.145 + cksort {SELECT b FROM t1 ORDER BY b ASC}
1.146 +} {1 2 3 4 5 6 7 8 nosort}
1.147 +do_test descidx1-3.12 {
1.148 + cksort {SELECT b FROM t1 ORDER BY b DESC}
1.149 +} {8 7 6 5 4 3 2 1 nosort}
1.150 +
1.151 +do_test descidx1-3.21 {
1.152 + cksort {SELECT a FROM t1 WHERE a>3 AND a<8 ORDER BY a}
1.153 +} {4 5 6 7 nosort}
1.154 +do_test descidx1-3.22 {
1.155 + cksort {SELECT a FROM t1 WHERE a>3 AND a<8 ORDER BY a ASC}
1.156 +} {4 5 6 7 nosort}
1.157 +do_test descidx1-3.23 {
1.158 + cksort {SELECT a FROM t1 WHERE a>3 AND a<8 ORDER BY a DESC}
1.159 +} {7 6 5 4 nosort}
1.160 +do_test descidx1-3.24 {
1.161 + cksort {SELECT b FROM t1 WHERE a>3 AND a<8 ORDER BY a}
1.162 +} {4 5 6 7 nosort}
1.163 +do_test descidx1-3.25 {
1.164 + cksort {SELECT b FROM t1 WHERE a>3 AND a<8 ORDER BY a ASC}
1.165 +} {4 5 6 7 nosort}
1.166 +do_test descidx1-3.26 {
1.167 + cksort {SELECT b FROM t1 WHERE a>3 AND a<8 ORDER BY a DESC}
1.168 +} {7 6 5 4 nosort}
1.169 +
1.170 +# Create a table with indices that are descending on some terms and
1.171 +# ascending on others.
1.172 +#
1.173 +ifcapable bloblit {
1.174 + do_test descidx1-4.1 {
1.175 + execsql {
1.176 + CREATE TABLE t2(a INT, b TEXT, c BLOB, d REAL);
1.177 + CREATE INDEX i3 ON t2(a ASC, b DESC, c ASC);
1.178 + CREATE INDEX i4 ON t2(b DESC, a ASC, d DESC);
1.179 + INSERT INTO t2 VALUES(1,'one',x'31',1.0);
1.180 + INSERT INTO t2 VALUES(2,'two',x'3232',2.0);
1.181 + INSERT INTO t2 VALUES(3,'three',x'333333',3.0);
1.182 + INSERT INTO t2 VALUES(4,'four',x'34343434',4.0);
1.183 + INSERT INTO t2 VALUES(5,'five',x'3535353535',5.0);
1.184 + INSERT INTO t2 VALUES(6,'six',x'363636363636',6.0);
1.185 + INSERT INTO t2 VALUES(2,'two',x'323232',2.1);
1.186 + INSERT INTO t2 VALUES(2,'zwei',x'3232',2.2);
1.187 + INSERT INTO t2 VALUES(2,NULL,NULL,2.3);
1.188 + SELECT count(*) FROM t2;
1.189 + }
1.190 + } {9}
1.191 + do_test descidx1-4.2 {
1.192 + execsql {
1.193 + SELECT d FROM t2 ORDER BY a;
1.194 + }
1.195 + } {1.0 2.2 2.0 2.1 2.3 3.0 4.0 5.0 6.0}
1.196 + do_test descidx1-4.3 {
1.197 + execsql {
1.198 + SELECT d FROM t2 WHERE a>=2;
1.199 + }
1.200 + } {2.2 2.0 2.1 2.3 3.0 4.0 5.0 6.0}
1.201 + do_test descidx1-4.4 {
1.202 + execsql {
1.203 + SELECT d FROM t2 WHERE a>2;
1.204 + }
1.205 + } {3.0 4.0 5.0 6.0}
1.206 + do_test descidx1-4.5 {
1.207 + execsql {
1.208 + SELECT d FROM t2 WHERE a=2 AND b>'two';
1.209 + }
1.210 + } {2.2}
1.211 + do_test descidx1-4.6 {
1.212 + execsql {
1.213 + SELECT d FROM t2 WHERE a=2 AND b>='two';
1.214 + }
1.215 + } {2.2 2.0 2.1}
1.216 + do_test descidx1-4.7 {
1.217 + execsql {
1.218 + SELECT d FROM t2 WHERE a=2 AND b<'two';
1.219 + }
1.220 + } {}
1.221 + do_test descidx1-4.8 {
1.222 + execsql {
1.223 + SELECT d FROM t2 WHERE a=2 AND b<='two';
1.224 + }
1.225 + } {2.0 2.1}
1.226 +}
1.227 +
1.228 +do_test descidx1-5.1 {
1.229 + execsql {
1.230 + CREATE TABLE t3(a,b,c,d);
1.231 + CREATE INDEX t3i1 ON t3(a DESC, b ASC, c DESC, d ASC);
1.232 + INSERT INTO t3 VALUES(0,0,0,0);
1.233 + INSERT INTO t3 VALUES(0,0,0,1);
1.234 + INSERT INTO t3 VALUES(0,0,1,0);
1.235 + INSERT INTO t3 VALUES(0,0,1,1);
1.236 + INSERT INTO t3 VALUES(0,1,0,0);
1.237 + INSERT INTO t3 VALUES(0,1,0,1);
1.238 + INSERT INTO t3 VALUES(0,1,1,0);
1.239 + INSERT INTO t3 VALUES(0,1,1,1);
1.240 + INSERT INTO t3 VALUES(1,0,0,0);
1.241 + INSERT INTO t3 VALUES(1,0,0,1);
1.242 + INSERT INTO t3 VALUES(1,0,1,0);
1.243 + INSERT INTO t3 VALUES(1,0,1,1);
1.244 + INSERT INTO t3 VALUES(1,1,0,0);
1.245 + INSERT INTO t3 VALUES(1,1,0,1);
1.246 + INSERT INTO t3 VALUES(1,1,1,0);
1.247 + INSERT INTO t3 VALUES(1,1,1,1);
1.248 + SELECT count(*) FROM t3;
1.249 + }
1.250 +} {16}
1.251 +do_test descidx1-5.2 {
1.252 + cksort {
1.253 + SELECT a||b||c||d FROM t3 ORDER BY a,b,c,d;
1.254 + }
1.255 +} {0000 0001 0010 0011 0100 0101 0110 0111 1000 1001 1010 1011 1100 1101 1110 1111 sort}
1.256 +do_test descidx1-5.3 {
1.257 + cksort {
1.258 + SELECT a||b||c||d FROM t3 ORDER BY a DESC, b ASC, c DESC, d ASC;
1.259 + }
1.260 +} {1010 1011 1000 1001 1110 1111 1100 1101 0010 0011 0000 0001 0110 0111 0100 0101 nosort}
1.261 +do_test descidx1-5.4 {
1.262 + cksort {
1.263 + SELECT a||b||c||d FROM t3 ORDER BY a ASC, b DESC, c ASC, d DESC;
1.264 + }
1.265 +} {0101 0100 0111 0110 0001 0000 0011 0010 1101 1100 1111 1110 1001 1000 1011 1010 nosort}
1.266 +do_test descidx1-5.5 {
1.267 + cksort {
1.268 + SELECT a||b||c FROM t3 WHERE d=0 ORDER BY a DESC, b ASC, c DESC
1.269 + }
1.270 +} {101 100 111 110 001 000 011 010 nosort}
1.271 +do_test descidx1-5.6 {
1.272 + cksort {
1.273 + SELECT a||b||c FROM t3 WHERE d=0 ORDER BY a ASC, b DESC, c ASC
1.274 + }
1.275 +} {010 011 000 001 110 111 100 101 nosort}
1.276 +do_test descidx1-5.7 {
1.277 + cksort {
1.278 + SELECT a||b||c FROM t3 WHERE d=0 ORDER BY a ASC, b DESC, c DESC
1.279 + }
1.280 +} {011 010 001 000 111 110 101 100 sort}
1.281 +do_test descidx1-5.8 {
1.282 + cksort {
1.283 + SELECT a||b||c FROM t3 WHERE d=0 ORDER BY a ASC, b ASC, c ASC
1.284 + }
1.285 +} {000 001 010 011 100 101 110 111 sort}
1.286 +do_test descidx1-5.9 {
1.287 + cksort {
1.288 + SELECT a||b||c FROM t3 WHERE d=0 ORDER BY a DESC, b DESC, c ASC
1.289 + }
1.290 +} {110 111 100 101 010 011 000 001 sort}
1.291 +
1.292 +# Test the legacy_file_format pragma here because we have access to
1.293 +# the get_file_format command.
1.294 +#
1.295 +ifcapable legacyformat {
1.296 + do_test descidx1-6.1 {
1.297 + db close
1.298 + file delete -force test.db test.db-journal
1.299 + sqlite3 db test.db
1.300 + execsql {PRAGMA legacy_file_format}
1.301 + } {1}
1.302 +} else {
1.303 + do_test descidx1-6.1 {
1.304 + db close
1.305 + file delete -force test.db test.db-journal
1.306 + sqlite3 db test.db
1.307 + execsql {PRAGMA legacy_file_format}
1.308 + } {0}
1.309 +}
1.310 +do_test descidx1-6.2 {
1.311 + execsql {PRAGMA legacy_file_format=YES}
1.312 + execsql {PRAGMA legacy_file_format}
1.313 +} {1}
1.314 +do_test descidx1-6.3 {
1.315 + execsql {
1.316 + CREATE TABLE t1(a,b,c);
1.317 + }
1.318 + get_file_format
1.319 +} {1}
1.320 +ifcapable vacuum {
1.321 + # Verify that the file format is preserved across a vacuum.
1.322 + do_test descidx1-6.3.1 {
1.323 + execsql {VACUUM}
1.324 + get_file_format
1.325 + } {1}
1.326 +}
1.327 +do_test descidx1-6.4 {
1.328 + db close
1.329 + file delete -force test.db test.db-journal
1.330 + sqlite3 db test.db
1.331 + execsql {PRAGMA legacy_file_format=NO}
1.332 + execsql {PRAGMA legacy_file_format}
1.333 +} {0}
1.334 +do_test descidx1-6.5 {
1.335 + execsql {
1.336 + CREATE TABLE t1(a,b,c);
1.337 + CREATE INDEX i1 ON t1(a ASC, b DESC, c ASC);
1.338 + INSERT INTO t1 VALUES(1,2,3);
1.339 + INSERT INTO t1 VALUES(1,1,0);
1.340 + INSERT INTO t1 VALUES(1,2,1);
1.341 + INSERT INTO t1 VALUES(1,3,4);
1.342 + }
1.343 + get_file_format
1.344 +} {4}
1.345 +ifcapable vacuum {
1.346 + # Verify that the file format is preserved across a vacuum.
1.347 + do_test descidx1-6.6 {
1.348 + execsql {VACUUM}
1.349 + get_file_format
1.350 + } {4}
1.351 + do_test descidx1-6.7 {
1.352 + execsql {
1.353 + PRAGMA legacy_file_format=ON;
1.354 + VACUUM;
1.355 + }
1.356 + get_file_format
1.357 + } {4}
1.358 +}
1.359 +
1.360 +
1.361 +
1.362 +finish_test