Return to descidx2.test CVS log

Up to [ELWIX - Embedded LightWeight unIX -] / embedaddon / sqlite3 / test

sqlite3

    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.1.1.1 2012/02/21 17:04:16 misho 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