Annotation of embedaddon/sqlite3/test/descidx1.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: descidx1.test,v 1.10 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: db eval {PRAGMA legacy_file_format=OFF}
! 26:
! 27: # This procedure sets the value of the file-format in file 'test.db'
! 28: # to $newval. Also, the schema cookie is incremented.
! 29: #
! 30: proc set_file_format {newval} {
! 31: hexio_write test.db 44 [hexio_render_int32 $newval]
! 32: set schemacookie [hexio_get_int [hexio_read test.db 40 4]]
! 33: incr schemacookie
! 34: hexio_write test.db 40 [hexio_render_int32 $schemacookie]
! 35: return {}
! 36: }
! 37:
! 38: # This procedure returns the value of the file-format in file 'test.db'.
! 39: #
! 40: proc get_file_format {{fname test.db}} {
! 41: return [hexio_get_int [hexio_read $fname 44 4]]
! 42: }
! 43:
! 44:
! 45: # Verify that the file format starts as 4.
! 46: #
! 47: do_test descidx1-1.1 {
! 48: execsql {
! 49: CREATE TABLE t1(a,b);
! 50: CREATE INDEX i1 ON t1(b ASC);
! 51: }
! 52: get_file_format
! 53: } {4}
! 54: do_test descidx1-1.2 {
! 55: execsql {
! 56: CREATE INDEX i2 ON t1(a DESC);
! 57: }
! 58: get_file_format
! 59: } {4}
! 60:
! 61: # Put some information in the table and verify that the descending
! 62: # index actually works.
! 63: #
! 64: do_test descidx1-2.1 {
! 65: execsql {
! 66: INSERT INTO t1 VALUES(1,1);
! 67: INSERT INTO t1 VALUES(2,2);
! 68: INSERT INTO t1 SELECT a+2, a+2 FROM t1;
! 69: INSERT INTO t1 SELECT a+4, a+4 FROM t1;
! 70: SELECT b FROM t1 WHERE a>3 AND a<7;
! 71: }
! 72: } {6 5 4}
! 73: do_test descidx1-2.2 {
! 74: execsql {
! 75: SELECT a FROM t1 WHERE b>3 AND b<7;
! 76: }
! 77: } {4 5 6}
! 78: do_test descidx1-2.3 {
! 79: execsql {
! 80: SELECT b FROM t1 WHERE a>=3 AND a<7;
! 81: }
! 82: } {6 5 4 3}
! 83: do_test descidx1-2.4 {
! 84: execsql {
! 85: SELECT b FROM t1 WHERE a>3 AND a<=7;
! 86: }
! 87: } {7 6 5 4}
! 88: do_test descidx1-2.5 {
! 89: execsql {
! 90: SELECT b FROM t1 WHERE a>=3 AND a<=7;
! 91: }
! 92: } {7 6 5 4 3}
! 93: do_test descidx1-2.6 {
! 94: execsql {
! 95: SELECT a FROM t1 WHERE b>=3 AND b<=7;
! 96: }
! 97: } {3 4 5 6 7}
! 98:
! 99: # This procedure executes the SQL. Then it checks to see if the OP_Sort
! 100: # opcode was executed. If an OP_Sort did occur, then "sort" is appended
! 101: # to the result. If no OP_Sort happened, then "nosort" is appended.
! 102: #
! 103: # This procedure is used to check to make sure sorting is or is not
! 104: # occurring as expected.
! 105: #
! 106: proc cksort {sql} {
! 107: set ::sqlite_sort_count 0
! 108: set data [execsql $sql]
! 109: if {$::sqlite_sort_count} {set x sort} {set x nosort}
! 110: lappend data $x
! 111: return $data
! 112: }
! 113:
! 114: # Test sorting using a descending index.
! 115: #
! 116: do_test descidx1-3.1 {
! 117: cksort {SELECT a FROM t1 ORDER BY a}
! 118: } {1 2 3 4 5 6 7 8 nosort}
! 119: do_test descidx1-3.2 {
! 120: cksort {SELECT a FROM t1 ORDER BY a ASC}
! 121: } {1 2 3 4 5 6 7 8 nosort}
! 122: do_test descidx1-3.3 {
! 123: cksort {SELECT a FROM t1 ORDER BY a DESC}
! 124: } {8 7 6 5 4 3 2 1 nosort}
! 125: do_test descidx1-3.4 {
! 126: cksort {SELECT b FROM t1 ORDER BY a}
! 127: } {1 2 3 4 5 6 7 8 nosort}
! 128: do_test descidx1-3.5 {
! 129: cksort {SELECT b FROM t1 ORDER BY a ASC}
! 130: } {1 2 3 4 5 6 7 8 nosort}
! 131: do_test descidx1-3.6 {
! 132: cksort {SELECT b FROM t1 ORDER BY a DESC}
! 133: } {8 7 6 5 4 3 2 1 nosort}
! 134: do_test descidx1-3.7 {
! 135: cksort {SELECT a FROM t1 ORDER BY b}
! 136: } {1 2 3 4 5 6 7 8 nosort}
! 137: do_test descidx1-3.8 {
! 138: cksort {SELECT a FROM t1 ORDER BY b ASC}
! 139: } {1 2 3 4 5 6 7 8 nosort}
! 140: do_test descidx1-3.9 {
! 141: cksort {SELECT a FROM t1 ORDER BY b DESC}
! 142: } {8 7 6 5 4 3 2 1 nosort}
! 143: do_test descidx1-3.10 {
! 144: cksort {SELECT b FROM t1 ORDER BY b}
! 145: } {1 2 3 4 5 6 7 8 nosort}
! 146: do_test descidx1-3.11 {
! 147: cksort {SELECT b FROM t1 ORDER BY b ASC}
! 148: } {1 2 3 4 5 6 7 8 nosort}
! 149: do_test descidx1-3.12 {
! 150: cksort {SELECT b FROM t1 ORDER BY b DESC}
! 151: } {8 7 6 5 4 3 2 1 nosort}
! 152:
! 153: do_test descidx1-3.21 {
! 154: cksort {SELECT a FROM t1 WHERE a>3 AND a<8 ORDER BY a}
! 155: } {4 5 6 7 nosort}
! 156: do_test descidx1-3.22 {
! 157: cksort {SELECT a FROM t1 WHERE a>3 AND a<8 ORDER BY a ASC}
! 158: } {4 5 6 7 nosort}
! 159: do_test descidx1-3.23 {
! 160: cksort {SELECT a FROM t1 WHERE a>3 AND a<8 ORDER BY a DESC}
! 161: } {7 6 5 4 nosort}
! 162: do_test descidx1-3.24 {
! 163: cksort {SELECT b FROM t1 WHERE a>3 AND a<8 ORDER BY a}
! 164: } {4 5 6 7 nosort}
! 165: do_test descidx1-3.25 {
! 166: cksort {SELECT b FROM t1 WHERE a>3 AND a<8 ORDER BY a ASC}
! 167: } {4 5 6 7 nosort}
! 168: do_test descidx1-3.26 {
! 169: cksort {SELECT b FROM t1 WHERE a>3 AND a<8 ORDER BY a DESC}
! 170: } {7 6 5 4 nosort}
! 171:
! 172: # Create a table with indices that are descending on some terms and
! 173: # ascending on others.
! 174: #
! 175: ifcapable bloblit {
! 176: do_test descidx1-4.1 {
! 177: execsql {
! 178: CREATE TABLE t2(a INT, b TEXT, c BLOB, d REAL);
! 179: CREATE INDEX i3 ON t2(a ASC, b DESC, c ASC);
! 180: CREATE INDEX i4 ON t2(b DESC, a ASC, d DESC);
! 181: INSERT INTO t2 VALUES(1,'one',x'31',1.0);
! 182: INSERT INTO t2 VALUES(2,'two',x'3232',2.0);
! 183: INSERT INTO t2 VALUES(3,'three',x'333333',3.0);
! 184: INSERT INTO t2 VALUES(4,'four',x'34343434',4.0);
! 185: INSERT INTO t2 VALUES(5,'five',x'3535353535',5.0);
! 186: INSERT INTO t2 VALUES(6,'six',x'363636363636',6.0);
! 187: INSERT INTO t2 VALUES(2,'two',x'323232',2.1);
! 188: INSERT INTO t2 VALUES(2,'zwei',x'3232',2.2);
! 189: INSERT INTO t2 VALUES(2,NULL,NULL,2.3);
! 190: SELECT count(*) FROM t2;
! 191: }
! 192: } {9}
! 193: do_test descidx1-4.2 {
! 194: execsql {
! 195: SELECT d FROM t2 ORDER BY a;
! 196: }
! 197: } {1.0 2.2 2.0 2.1 2.3 3.0 4.0 5.0 6.0}
! 198: do_test descidx1-4.3 {
! 199: execsql {
! 200: SELECT d FROM t2 WHERE a>=2;
! 201: }
! 202: } {2.2 2.0 2.1 2.3 3.0 4.0 5.0 6.0}
! 203: do_test descidx1-4.4 {
! 204: execsql {
! 205: SELECT d FROM t2 WHERE a>2;
! 206: }
! 207: } {3.0 4.0 5.0 6.0}
! 208: do_test descidx1-4.5 {
! 209: execsql {
! 210: SELECT d FROM t2 WHERE a=2 AND b>'two';
! 211: }
! 212: } {2.2}
! 213: do_test descidx1-4.6 {
! 214: execsql {
! 215: SELECT d FROM t2 WHERE a=2 AND b>='two';
! 216: }
! 217: } {2.2 2.0 2.1}
! 218: do_test descidx1-4.7 {
! 219: execsql {
! 220: SELECT d FROM t2 WHERE a=2 AND b<'two';
! 221: }
! 222: } {}
! 223: do_test descidx1-4.8 {
! 224: execsql {
! 225: SELECT d FROM t2 WHERE a=2 AND b<='two';
! 226: }
! 227: } {2.0 2.1}
! 228: }
! 229:
! 230: do_test descidx1-5.1 {
! 231: execsql {
! 232: CREATE TABLE t3(a,b,c,d);
! 233: CREATE INDEX t3i1 ON t3(a DESC, b ASC, c DESC, d ASC);
! 234: INSERT INTO t3 VALUES(0,0,0,0);
! 235: INSERT INTO t3 VALUES(0,0,0,1);
! 236: INSERT INTO t3 VALUES(0,0,1,0);
! 237: INSERT INTO t3 VALUES(0,0,1,1);
! 238: INSERT INTO t3 VALUES(0,1,0,0);
! 239: INSERT INTO t3 VALUES(0,1,0,1);
! 240: INSERT INTO t3 VALUES(0,1,1,0);
! 241: INSERT INTO t3 VALUES(0,1,1,1);
! 242: INSERT INTO t3 VALUES(1,0,0,0);
! 243: INSERT INTO t3 VALUES(1,0,0,1);
! 244: INSERT INTO t3 VALUES(1,0,1,0);
! 245: INSERT INTO t3 VALUES(1,0,1,1);
! 246: INSERT INTO t3 VALUES(1,1,0,0);
! 247: INSERT INTO t3 VALUES(1,1,0,1);
! 248: INSERT INTO t3 VALUES(1,1,1,0);
! 249: INSERT INTO t3 VALUES(1,1,1,1);
! 250: SELECT count(*) FROM t3;
! 251: }
! 252: } {16}
! 253: do_test descidx1-5.2 {
! 254: cksort {
! 255: SELECT a||b||c||d FROM t3 ORDER BY a,b,c,d;
! 256: }
! 257: } {0000 0001 0010 0011 0100 0101 0110 0111 1000 1001 1010 1011 1100 1101 1110 1111 sort}
! 258: do_test descidx1-5.3 {
! 259: cksort {
! 260: SELECT a||b||c||d FROM t3 ORDER BY a DESC, b ASC, c DESC, d ASC;
! 261: }
! 262: } {1010 1011 1000 1001 1110 1111 1100 1101 0010 0011 0000 0001 0110 0111 0100 0101 nosort}
! 263: do_test descidx1-5.4 {
! 264: cksort {
! 265: SELECT a||b||c||d FROM t3 ORDER BY a ASC, b DESC, c ASC, d DESC;
! 266: }
! 267: } {0101 0100 0111 0110 0001 0000 0011 0010 1101 1100 1111 1110 1001 1000 1011 1010 nosort}
! 268: do_test descidx1-5.5 {
! 269: cksort {
! 270: SELECT a||b||c FROM t3 WHERE d=0 ORDER BY a DESC, b ASC, c DESC
! 271: }
! 272: } {101 100 111 110 001 000 011 010 nosort}
! 273: do_test descidx1-5.6 {
! 274: cksort {
! 275: SELECT a||b||c FROM t3 WHERE d=0 ORDER BY a ASC, b DESC, c ASC
! 276: }
! 277: } {010 011 000 001 110 111 100 101 nosort}
! 278: do_test descidx1-5.7 {
! 279: cksort {
! 280: SELECT a||b||c FROM t3 WHERE d=0 ORDER BY a ASC, b DESC, c DESC
! 281: }
! 282: } {011 010 001 000 111 110 101 100 sort}
! 283: do_test descidx1-5.8 {
! 284: cksort {
! 285: SELECT a||b||c FROM t3 WHERE d=0 ORDER BY a ASC, b ASC, c ASC
! 286: }
! 287: } {000 001 010 011 100 101 110 111 sort}
! 288: do_test descidx1-5.9 {
! 289: cksort {
! 290: SELECT a||b||c FROM t3 WHERE d=0 ORDER BY a DESC, b DESC, c ASC
! 291: }
! 292: } {110 111 100 101 010 011 000 001 sort}
! 293:
! 294: # Test the legacy_file_format pragma here because we have access to
! 295: # the get_file_format command.
! 296: #
! 297: ifcapable legacyformat {
! 298: do_test descidx1-6.1 {
! 299: db close
! 300: forcedelete test.db test.db-journal
! 301: sqlite3 db test.db
! 302: execsql {PRAGMA legacy_file_format}
! 303: } {1}
! 304: } else {
! 305: do_test descidx1-6.1 {
! 306: db close
! 307: forcedelete test.db test.db-journal
! 308: sqlite3 db test.db
! 309: execsql {PRAGMA legacy_file_format}
! 310: } {0}
! 311: }
! 312: do_test descidx1-6.2 {
! 313: execsql {PRAGMA legacy_file_format=YES}
! 314: execsql {PRAGMA legacy_file_format}
! 315: } {1}
! 316: do_test descidx1-6.3 {
! 317: execsql {
! 318: CREATE TABLE t1(a,b,c);
! 319: }
! 320: get_file_format
! 321: } {1}
! 322: ifcapable vacuum {
! 323: # Verify that the file format is preserved across a vacuum.
! 324: do_test descidx1-6.3.1 {
! 325: execsql {VACUUM}
! 326: get_file_format
! 327: } {1}
! 328: }
! 329: do_test descidx1-6.4 {
! 330: db close
! 331: forcedelete test.db test.db-journal
! 332: sqlite3 db test.db
! 333: execsql {PRAGMA legacy_file_format=NO}
! 334: execsql {PRAGMA legacy_file_format}
! 335: } {0}
! 336: do_test descidx1-6.5 {
! 337: execsql {
! 338: CREATE TABLE t1(a,b,c);
! 339: CREATE INDEX i1 ON t1(a ASC, b DESC, c ASC);
! 340: INSERT INTO t1 VALUES(1,2,3);
! 341: INSERT INTO t1 VALUES(1,1,0);
! 342: INSERT INTO t1 VALUES(1,2,1);
! 343: INSERT INTO t1 VALUES(1,3,4);
! 344: }
! 345: get_file_format
! 346: } {4}
! 347: ifcapable vacuum {
! 348: # Verify that the file format is preserved across a vacuum.
! 349: do_test descidx1-6.6 {
! 350: execsql {VACUUM}
! 351: get_file_format
! 352: } {4}
! 353: do_test descidx1-6.7 {
! 354: execsql {
! 355: PRAGMA legacy_file_format=ON;
! 356: VACUUM;
! 357: }
! 358: get_file_format
! 359: } {4}
! 360: }
! 361:
! 362:
! 363:
! 364: finish_test
FreeBSD-CVSweb <freebsd-cvsweb@FreeBSD.org>
![[BACK]](/icons/cvsweb/back.gif){kind=icon}
Return to descidx1.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