Return to collate9.test CVS log

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

sqlite3

    1: #
    2: # 2007 November 12
    3: #
    4: # The author disclaims copyright to this source code.  In place of
    5: # a legal notice, here is a blessing:
    6: #
    7: #    May you do good and not evil.
    8: #    May you find forgiveness for yourself and forgive others.
    9: #    May you share freely, never taking more than you give.
   10: #
   11: #***********************************************************************
   12: # This file implements regression tests for SQLite library.  The
   13: # focus of this script is making sure that the names of collation
   14: # sequences may be quoted using double quotes in SQL statements.
   15: #
   16: # $Id: collate9.test,v 1.1.1.1 2012/02/21 17:04:16 misho Exp $
   17: 
   18: set testdir [file dirname $argv0]
   19: source $testdir/tester.tcl
   20: 
   21: proc reverse_sort {lhs rhs} {
   22:   return [string compare $rhs $lhs]
   23: }
   24: db collate "reverse sort" reverse_sort
   25: 
   26: # This procedure executes the SQL.  Then it checks to see if the OP_Sort
   27: # opcode was executed.  If an OP_Sort did occur, then "sort" is appended
   28: # to the result.  If no OP_Sort happened, then "nosort" is appended.
   29: #
   30: # This procedure is used to check to make sure sorting is or is not
   31: # occurring as expected.
   32: #
   33: proc cksort {sql} {
   34:   set ::sqlite_sort_count 0
   35:   set data [execsql $sql]
   36:   if {$::sqlite_sort_count} {set x sort} {set x nosort}
   37:   lappend data $x
   38:   return $data
   39: }
   40: 
   41: # Test plan:
   42: #
   43: #     collate9-1.* - Test collation sequences attached to table columns
   44: #     collate9-2.* - Test collation sequences attached to expressions
   45: #     collate9-3.* - Test collation sequences attached to an index
   46: #     collate9-4.* - Test collation sequences as an argument to REINDEX
   47: #
   48: 
   49: do_test collate9-1.1 {
   50:   execsql {
   51:     CREATE TABLE xy(x COLLATE "reverse sort", y COLLATE binary);
   52:     INSERT INTO xy VALUES('one', 'one');
   53:     INSERT INTO xy VALUES('two', 'two');
   54:     INSERT INTO xy VALUES('three', 'three');
   55:   }
   56: } {}
   57: do_test collate9-1.2 {
   58:   execsql { 
   59:     SELECT x FROM xy ORDER BY x
   60:   }
   61: } {two three one}
   62: do_test collate9-1.3 {
   63:   execsql { 
   64:     SELECT y FROM xy ORDER BY y
   65:   }
   66: } {one three two}
   67: do_test collate9-1.4 {
   68:   cksort { 
   69:     SELECT x FROM xy ORDER BY x
   70:   }
   71: } {two three one sort}
   72: do_test collate9-1.5 {
   73:   execsql { 
   74:     CREATE INDEX xy_i ON xy(x)
   75:   }
   76: } {}
   77: do_test collate9-1.6 {
   78:   cksort { 
   79:     SELECT x FROM xy ORDER BY x
   80:   }
   81: } {two three one nosort}
   82: 
   83: do_test collate9-2.1 {
   84:   execsql { 
   85:     SELECT x, x < 'seven' FROM xy ORDER BY x
   86:   }
   87: } {two 1 three 1 one 0}
   88: do_test collate9-2.2 {
   89:   execsql { 
   90:     SELECT y, y < 'seven' FROM xy ORDER BY x
   91:   }
   92: } {two 0 three 0 one 1}
   93: do_test collate9-2.3 {
   94:   execsql { 
   95:     SELECT y, y COLLATE "reverse sort" < 'seven' FROM xy ORDER BY x
   96:   }
   97: } {two 1 three 1 one 0}
   98: do_test collate9-2.4 {
   99:   execsql {
  100:     SELECT y FROM xy ORDER BY y
  101:   }
  102: } {one three two}
  103: do_test collate9-2.5 {
  104:   execsql {
  105:     SELECT y FROM xy ORDER BY y COLLATE "reverse sort"
  106:   }
  107: } {two three one}
  108: do_test collate9-2.6 {
  109:   execsql {
  110:     SELECT y COLLATE "reverse sort" AS aaa FROM xy ORDER BY aaa
  111:   }
  112: } {two three one}
  113: 
  114: do_test collate9-3.1 {
  115:   execsql {
  116:     CREATE INDEX xy_i2 ON xy(y COLLATE "reverse sort");
  117:   }
  118: } {}
  119: do_test collate9-3.2 {
  120:   cksort { 
  121:     SELECT y FROM xy ORDER BY y 
  122:   }
  123: } {one three two sort}
  124: do_test collate9-3.3 {
  125:   cksort { 
  126:     SELECT y FROM xy ORDER BY y COLLATE "reverse sort"
  127:   }
  128: } {two three one nosort}
  129: do_test collate9-3.4 {
  130:   cksort { 
  131:     SELECT y AS aaa FROM xy ORDER BY aaa
  132:   }
  133: } {one three two sort}
  134: do_test collate9-3.5 {
  135:   cksort { 
  136:     SELECT y COLLATE "reverse sort" AS aaa FROM xy ORDER BY aaa
  137:   }
  138: } {two three one nosort}
  139: 
  140: ifcapable reindex {
  141:   do_test collate9-4.1 {
  142:     execsql {
  143:       REINDEX "reverse sort"
  144:     }
  145:   } {}
  146: 
  147:   # Modify the "reverse sort" collation so that it now sorts in the same
  148:   # order as binary.
  149:   proc reverse_sort {lhs rhs} {
  150:     return [string compare $lhs $rhs]
  151:   }
  152: 
  153:   # The integrity check should now fail because the indexes created using
  154:   # "reverse sort" are no longer in sync with the collation sequence
  155:   # implementation.
  156:   do_test collate9-4.2 {
  157:     expr {"ok" eq [execsql { PRAGMA integrity_check }]}
  158:   } {0}
  159: 
  160:   do_test collate9-4.3 {
  161:     execsql {
  162:       REINDEX "reverse sort"
  163:     }
  164:   } {}
  165: 
  166:   # Integrity check should now pass.
  167:   do_test collate9-4.4 {
  168:     expr {"ok" eq [execsql { PRAGMA integrity_check }]}
  169:   } {1}
  170: 
  171:   do_test collate9-4.5 {
  172:     cksort {
  173:       SELECT x FROM xy ORDER BY x COLLATE "reverse sort"
  174:     }
  175:   } {one three two nosort}
  176: }
  177: 
  178: finish_test