Annotation of embedaddon/sqlite3/test/descidx2.test, revision 1.1
1.1 ! misho 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:
! 28: # This procedure sets the value of the file-format in file 'test.db'
! 29: # to $newval. Also, the schema cookie is incremented.
! 30: #
! 31: proc set_file_format {newval} {
! 32: hexio_write test.db 44 [hexio_render_int32 $newval]
! 33: set schemacookie [hexio_get_int [hexio_read test.db 40 4]]
! 34: incr schemacookie
! 35: hexio_write test.db 40 [hexio_render_int32 $schemacookie]
! 36: return {}
! 37: }
! 38:
! 39: # This procedure returns the value of the file-format in file 'test.db'.
! 40: #
! 41: proc get_file_format {{fname test.db}} {
! 42: return [hexio_get_int [hexio_read $fname 44 4]]
! 43: }
! 44:
! 45:
! 46: # Verify that the file format starts as 4
! 47: #
! 48: do_test descidx2-1.1 {
! 49: execsql {
! 50: CREATE TABLE t1(a,b);
! 51: CREATE INDEX i1 ON t1(b ASC);
! 52: }
! 53: get_file_format
! 54: } {4}
! 55: do_test descidx2-1.2 {
! 56: execsql {
! 57: CREATE INDEX i2 ON t1(a DESC);
! 58: }
! 59: get_file_format
! 60: } {4}
! 61:
! 62: # Before adding any information to the database, set the file format
! 63: # back to three. Then close and reopen the database. With the file
! 64: # format set to three, SQLite should ignore the DESC argument on the
! 65: # index.
! 66: #
! 67: do_test descidx2-2.0 {
! 68: set_file_format 3
! 69: db close
! 70: sqlite3 db test.db
! 71: get_file_format
! 72: } {3}
! 73:
! 74: # Put some information in the table and verify that the DESC
! 75: # on the index is ignored.
! 76: #
! 77: do_test descidx2-2.1 {
! 78: execsql {
! 79: INSERT INTO t1 VALUES(1,1);
! 80: INSERT INTO t1 VALUES(2,2);
! 81: INSERT INTO t1 SELECT a+2, a+2 FROM t1;
! 82: INSERT INTO t1 SELECT a+4, a+4 FROM t1;
! 83: SELECT b FROM t1 WHERE a>3 AND a<7;
! 84: }
! 85: } {4 5 6}
! 86: do_test descidx2-2.2 {
! 87: execsql {
! 88: SELECT a FROM t1 WHERE b>3 AND b<7;
! 89: }
! 90: } {4 5 6}
! 91: do_test descidx2-2.3 {
! 92: execsql {
! 93: SELECT b FROM t1 WHERE a>=3 AND a<7;
! 94: }
! 95: } {3 4 5 6}
! 96: do_test descidx2-2.4 {
! 97: execsql {
! 98: SELECT b FROM t1 WHERE a>3 AND a<=7;
! 99: }
! 100: } {4 5 6 7}
! 101: do_test descidx2-2.5 {
! 102: execsql {
! 103: SELECT b FROM t1 WHERE a>=3 AND a<=7;
! 104: }
! 105: } {3 4 5 6 7}
! 106: do_test descidx2-2.6 {
! 107: execsql {
! 108: SELECT a FROM t1 WHERE b>=3 AND b<=7;
! 109: }
! 110: } {3 4 5 6 7}
! 111:
! 112: # This procedure executes the SQL. Then it checks to see if the OP_Sort
! 113: # opcode was executed. If an OP_Sort did occur, then "sort" is appended
! 114: # to the result. If no OP_Sort happened, then "nosort" is appended.
! 115: #
! 116: # This procedure is used to check to make sure sorting is or is not
! 117: # occurring as expected.
! 118: #
! 119: proc cksort {sql} {
! 120: set ::sqlite_sort_count 0
! 121: set data [execsql $sql]
! 122: if {$::sqlite_sort_count} {set x sort} {set x nosort}
! 123: lappend data $x
! 124: return $data
! 125: }
! 126:
! 127: # Test sorting using a descending index.
! 128: #
! 129: do_test descidx2-3.1 {
! 130: cksort {SELECT a FROM t1 ORDER BY a}
! 131: } {1 2 3 4 5 6 7 8 nosort}
! 132: do_test descidx2-3.2 {
! 133: cksort {SELECT a FROM t1 ORDER BY a ASC}
! 134: } {1 2 3 4 5 6 7 8 nosort}
! 135: do_test descidx2-3.3 {
! 136: cksort {SELECT a FROM t1 ORDER BY a DESC}
! 137: } {8 7 6 5 4 3 2 1 nosort}
! 138: do_test descidx2-3.4 {
! 139: cksort {SELECT b FROM t1 ORDER BY a}
! 140: } {1 2 3 4 5 6 7 8 nosort}
! 141: do_test descidx2-3.5 {
! 142: cksort {SELECT b FROM t1 ORDER BY a ASC}
! 143: } {1 2 3 4 5 6 7 8 nosort}
! 144: do_test descidx2-3.6 {
! 145: cksort {SELECT b FROM t1 ORDER BY a DESC}
! 146: } {8 7 6 5 4 3 2 1 nosort}
! 147: do_test descidx2-3.7 {
! 148: cksort {SELECT a FROM t1 ORDER BY b}
! 149: } {1 2 3 4 5 6 7 8 nosort}
! 150: do_test descidx2-3.8 {
! 151: cksort {SELECT a FROM t1 ORDER BY b ASC}
! 152: } {1 2 3 4 5 6 7 8 nosort}
! 153: do_test descidx2-3.9 {
! 154: cksort {SELECT a FROM t1 ORDER BY b DESC}
! 155: } {8 7 6 5 4 3 2 1 nosort}
! 156: do_test descidx2-3.10 {
! 157: cksort {SELECT b FROM t1 ORDER BY b}
! 158: } {1 2 3 4 5 6 7 8 nosort}
! 159: do_test descidx2-3.11 {
! 160: cksort {SELECT b FROM t1 ORDER BY b ASC}
! 161: } {1 2 3 4 5 6 7 8 nosort}
! 162: do_test descidx2-3.12 {
! 163: cksort {SELECT b FROM t1 ORDER BY b DESC}
! 164: } {8 7 6 5 4 3 2 1 nosort}
! 165:
! 166: do_test descidx2-3.21 {
! 167: cksort {SELECT a FROM t1 WHERE a>3 AND a<8 ORDER BY a}
! 168: } {4 5 6 7 nosort}
! 169: do_test descidx2-3.22 {
! 170: cksort {SELECT a FROM t1 WHERE a>3 AND a<8 ORDER BY a ASC}
! 171: } {4 5 6 7 nosort}
! 172: do_test descidx2-3.23 {
! 173: cksort {SELECT a FROM t1 WHERE a>3 AND a<8 ORDER BY a DESC}
! 174: } {7 6 5 4 nosort}
! 175: do_test descidx2-3.24 {
! 176: cksort {SELECT b FROM t1 WHERE a>3 AND a<8 ORDER BY a}
! 177: } {4 5 6 7 nosort}
! 178: do_test descidx2-3.25 {
! 179: cksort {SELECT b FROM t1 WHERE a>3 AND a<8 ORDER BY a ASC}
! 180: } {4 5 6 7 nosort}
! 181: do_test descidx2-3.26 {
! 182: cksort {SELECT b FROM t1 WHERE a>3 AND a<8 ORDER BY a DESC}
! 183: } {7 6 5 4 nosort}
! 184:
! 185: finish_test
FreeBSD-CVSweb <freebsd-cvsweb@FreeBSD.org>
![[BACK]](/icons/cvsweb/back.gif){kind=icon}
Return to descidx2.test CVS log ![[TXT]](/icons/cvsweb/text.gif){kind=icon}
![[DIR]](/icons/cvsweb/dir.gif){kind=icon}
Up to [ELWIX - Embedded LightWeight unIX -] / embedaddon / sqlite3 / test