Home | History | Annotate | Download | only in test 
      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.2 2008/07/10 00:32:42 drh 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
    179