AryaWu/sqlite

1# 2006 January 022#3# The author disclaims copyright to this source code.  In place of4# 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.  The12# focus of this script is descending indices.13#14# $Id: descidx3.test,v 1.6 2008/03/19 00:21:31 drh Exp $15#16 17set testdir [file dirname $argv0]18source $testdir/tester.tcl19 20# Do not use a codec for tests in this file, as the database file is21# manipulated directly using tcl scripts (using the [hexio_write] command).22#23do_not_use_codec24 25ifcapable !bloblit {26  finish_test27  return28}29#db eval {PRAGMA legacy_file_format=OFF}30sqlite3_db_config db LEGACY_FILE_FORMAT 031 32# This procedure sets the value of the file-format in file 'test.db'33# to $newval. Also, the schema cookie is incremented.34# 35proc set_file_format {newval} {36  hexio_write test.db 44 [hexio_render_int32 $newval]37  set schemacookie [hexio_get_int [hexio_read test.db 40 4]]38  incr schemacookie39  hexio_write test.db 40 [hexio_render_int32 $schemacookie]40  return {}41}42 43# This procedure returns the value of the file-format in file 'test.db'.44# 45proc get_file_format {{fname test.db}} {46  return [hexio_get_int [hexio_read $fname 44 4]]47}48 49# Verify that the file format starts as 4.50#51do_test descidx3-1.1 {52  execsql {53    CREATE TABLE t1(i INTEGER PRIMARY KEY,a,b,c,d);54    CREATE INDEX t1i1 ON t1(a DESC, b ASC, c DESC);55    CREATE INDEX t1i2 ON t1(b DESC, c ASC, d DESC);56  }57  get_file_format58} {4}59 60# Put some information in the table and verify that the descending61# index actually works.62#63do_test descidx3-2.1 {64  execsql {65    INSERT INTO t1 VALUES(1, NULL, NULL, NULL, NULL);66    INSERT INTO t1 VALUES(2, 2, 2, 2, 2);67    INSERT INTO t1 VALUES(3, 3, 3, 3, 3);68    INSERT INTO t1 VALUES(4, 2.5, 2.5, 2.5, 2.5);69    INSERT INTO t1 VALUES(5, -5, -5, -5, -5);70    INSERT INTO t1 VALUES(6, 'six', 'six', 'six', 'six');71    INSERT INTO t1 VALUES(7, x'77', x'77', x'77', x'77');72    INSERT INTO t1 VALUES(8, 'eight', 'eight', 'eight', 'eight');73    INSERT INTO t1 VALUES(9, x'7979', x'7979', x'7979', x'7979');74    SELECT count(*) FROM t1;75  }76} 977do_test descidx3-2.2 {78  execsql {79    SELECT i FROM t1 ORDER BY a;80  }81} {1 5 2 4 3 8 6 7 9}82do_test descidx3-2.3 {83  execsql {84    SELECT i FROM t1 ORDER BY a DESC;85  }86} {9 7 6 8 3 4 2 5 1}87 88# The "natural" order for the index is decreasing89do_test descidx3-2.4 {90  execsql {91    SELECT i FROM t1 WHERE a<=x'7979';92  }93} {9 7 6 8 3 4 2 5}94do_test descidx3-2.5 {95  execsql {96    SELECT i FROM t1 WHERE a>-99;97  }98} {9 7 6 8 3 4 2 5}99 100# Even when all values of t1.a are the same, sorting by A returns101# the rows in reverse order because this the natural order of the102# index.103#104do_test descidx3-3.1 {105  execsql {106    UPDATE t1 SET a=1;107    SELECT i FROM t1 ORDER BY a;108  }109} {9 7 6 8 3 4 2 5 1}110do_test descidx3-3.2 {111  execsql {112    SELECT i FROM t1 WHERE a=1 AND b>0 AND b<'zzz'113  }114} {2 4 3 8 6}115do_test descidx3-3.3 {116  execsql {117    SELECT i FROM t1 WHERE b>0 AND b<'zzz'118  }119} {6 8 3 4 2}120do_test descidx3-3.4 {121  execsql {122    SELECT i FROM t1 WHERE a=1 AND b>-9999 AND b<x'ffffffff'123  }124} {5 2 4 3 8 6 7 9}125do_test descidx3-3.5 {126  execsql {127    SELECT i FROM t1 WHERE b>-9999 AND b<x'ffffffff'128  }129} {9 7 6 8 3 4 2 5}130 131ifcapable subquery {132  # If the subquery capability is not compiled in to the binary, then133  # the IN(...) operator is not available. Hence these tests cannot be 134  # run.135  do_test descidx3-4.1 {136    lsort [execsql {137      UPDATE t1 SET a=2 WHERE i<6;138      SELECT i FROM t1 WHERE a IN (1,2) AND b>0 AND b<'zzz';139    }]140  } {2 3 4 6 8}141  do_test descidx3-4.2 {142    execsql {143      UPDATE t1 SET a=1;144      SELECT i FROM t1 WHERE a IN (1,2) AND b>0 AND b<'zzz';145    }146  } {2 4 3 8 6}147  do_test descidx3-4.3 {148    execsql {149      UPDATE t1 SET b=2;150      SELECT i FROM t1 WHERE a IN (1,2) AND b>0 AND b<'zzz';151    }152  } {9 7 6 8 3 4 2 5 1}153}154 155finish_test156