gitlab.com/CoiaPrant/sqlite3@v1.19.1/testdata/tcl/descidx2.test (about) 1 # 2005 December 21 2 # 3 # The author disclaims copyright to this source code. In place of 4 # a legal notice, here is a blessing: 5 # 6 # May you do good and not evil. 7 # May you find forgiveness for yourself and forgive others. 8 # May you share freely, never taking more than you give. 9 # 10 #************************************************************************* 11 # This file implements regression tests for SQLite library. The 12 # focus of this script is descending indices. 13 # 14 # $Id: descidx2.test,v 1.5 2008/03/19 00:21:31 drh Exp $ 15 # 16 17 set testdir [file dirname $argv0] 18 source $testdir/tester.tcl 19 20 # Do not use a codec for tests in this file, as the database file is 21 # manipulated directly using tcl scripts (using the [hexio_write] command). 22 # 23 do_not_use_codec 24 25 26 #db eval {PRAGMA legacy_file_format=OFF} 27 sqlite3_db_config db LEGACY_FILE_FORMAT 0 28 29 # This procedure sets the value of the file-format in file 'test.db' 30 # to $newval. Also, the schema cookie is incremented. 31 # 32 proc set_file_format {newval} { 33 hexio_write test.db 44 [hexio_render_int32 $newval] 34 set schemacookie [hexio_get_int [hexio_read test.db 40 4]] 35 incr schemacookie 36 hexio_write test.db 40 [hexio_render_int32 $schemacookie] 37 return {} 38 } 39 40 # This procedure returns the value of the file-format in file 'test.db'. 41 # 42 proc get_file_format {{fname test.db}} { 43 return [hexio_get_int [hexio_read $fname 44 4]] 44 } 45 46 47 # Verify that the file format starts as 4 48 # 49 do_test descidx2-1.1 { 50 execsql { 51 CREATE TABLE t1(a,b); 52 CREATE INDEX i1 ON t1(b ASC); 53 } 54 get_file_format 55 } {4} 56 do_test descidx2-1.2 { 57 execsql { 58 CREATE INDEX i2 ON t1(a DESC); 59 } 60 get_file_format 61 } {4} 62 63 # Before adding any information to the database, set the file format 64 # back to three. Then close and reopen the database. With the file 65 # format set to three, SQLite should ignore the DESC argument on the 66 # index. 67 # 68 do_test descidx2-2.0 { 69 set_file_format 3 70 db close 71 sqlite3 db test.db 72 get_file_format 73 } {3} 74 75 # Put some information in the table and verify that the DESC 76 # on the index is ignored. 77 # 78 do_test descidx2-2.1 { 79 execsql { 80 INSERT INTO t1 VALUES(1,1); 81 INSERT INTO t1 VALUES(2,2); 82 INSERT INTO t1 SELECT a+2, a+2 FROM t1; 83 INSERT INTO t1 SELECT a+4, a+4 FROM t1; 84 SELECT b FROM t1 WHERE a>3 AND a<7; 85 } 86 } {4 5 6} 87 do_test descidx2-2.2 { 88 execsql { 89 SELECT a FROM t1 WHERE b>3 AND b<7; 90 } 91 } {4 5 6} 92 do_test descidx2-2.3 { 93 execsql { 94 SELECT b FROM t1 WHERE a>=3 AND a<7; 95 } 96 } {3 4 5 6} 97 do_test descidx2-2.4 { 98 execsql { 99 SELECT b FROM t1 WHERE a>3 AND a<=7; 100 } 101 } {4 5 6 7} 102 do_test descidx2-2.5 { 103 execsql { 104 SELECT b FROM t1 WHERE a>=3 AND a<=7; 105 } 106 } {3 4 5 6 7} 107 do_test descidx2-2.6 { 108 execsql { 109 SELECT a FROM t1 WHERE b>=3 AND b<=7; 110 } 111 } {3 4 5 6 7} 112 113 # This procedure executes the SQL. Then it checks to see if the OP_Sort 114 # opcode was executed. If an OP_Sort did occur, then "sort" is appended 115 # to the result. If no OP_Sort happened, then "nosort" is appended. 116 # 117 # This procedure is used to check to make sure sorting is or is not 118 # occurring as expected. 119 # 120 proc cksort {sql} { 121 set ::sqlite_sort_count 0 122 set data [execsql $sql] 123 if {$::sqlite_sort_count} {set x sort} {set x nosort} 124 lappend data $x 125 return $data 126 } 127 128 # Test sorting using a descending index. 129 # 130 do_test descidx2-3.1 { 131 cksort {SELECT a FROM t1 ORDER BY a} 132 } {1 2 3 4 5 6 7 8 nosort} 133 do_test descidx2-3.2 { 134 cksort {SELECT a FROM t1 ORDER BY a ASC} 135 } {1 2 3 4 5 6 7 8 nosort} 136 do_test descidx2-3.3 { 137 cksort {SELECT a FROM t1 ORDER BY a DESC} 138 } {8 7 6 5 4 3 2 1 nosort} 139 do_test descidx2-3.4 { 140 cksort {SELECT b FROM t1 ORDER BY a} 141 } {1 2 3 4 5 6 7 8 nosort} 142 do_test descidx2-3.5 { 143 cksort {SELECT b FROM t1 ORDER BY a ASC} 144 } {1 2 3 4 5 6 7 8 nosort} 145 do_test descidx2-3.6 { 146 cksort {SELECT b FROM t1 ORDER BY a DESC} 147 } {8 7 6 5 4 3 2 1 nosort} 148 do_test descidx2-3.7 { 149 cksort {SELECT a FROM t1 ORDER BY b} 150 } {1 2 3 4 5 6 7 8 nosort} 151 do_test descidx2-3.8 { 152 cksort {SELECT a FROM t1 ORDER BY b ASC} 153 } {1 2 3 4 5 6 7 8 nosort} 154 do_test descidx2-3.9 { 155 cksort {SELECT a FROM t1 ORDER BY b DESC} 156 } {8 7 6 5 4 3 2 1 nosort} 157 do_test descidx2-3.10 { 158 cksort {SELECT b FROM t1 ORDER BY b} 159 } {1 2 3 4 5 6 7 8 nosort} 160 do_test descidx2-3.11 { 161 cksort {SELECT b FROM t1 ORDER BY b ASC} 162 } {1 2 3 4 5 6 7 8 nosort} 163 do_test descidx2-3.12 { 164 cksort {SELECT b FROM t1 ORDER BY b DESC} 165 } {8 7 6 5 4 3 2 1 nosort} 166 167 do_test descidx2-3.21 { 168 cksort {SELECT a FROM t1 WHERE a>3 AND a<8 ORDER BY a} 169 } {4 5 6 7 nosort} 170 do_test descidx2-3.22 { 171 cksort {SELECT a FROM t1 WHERE a>3 AND a<8 ORDER BY a ASC} 172 } {4 5 6 7 nosort} 173 do_test descidx2-3.23 { 174 cksort {SELECT a FROM t1 WHERE a>3 AND a<8 ORDER BY a DESC} 175 } {7 6 5 4 nosort} 176 do_test descidx2-3.24 { 177 cksort {SELECT b FROM t1 WHERE a>3 AND a<8 ORDER BY a} 178 } {4 5 6 7 nosort} 179 do_test descidx2-3.25 { 180 cksort {SELECT b FROM t1 WHERE a>3 AND a<8 ORDER BY a ASC} 181 } {4 5 6 7 nosort} 182 do_test descidx2-3.26 { 183 cksort {SELECT b FROM t1 WHERE a>3 AND a<8 ORDER BY a DESC} 184 } {7 6 5 4 nosort} 185 186 finish_test