sl@0: #
sl@0: # 2007 November 12
sl@0: #
sl@0: # The author disclaims copyright to this source code.  In place of
sl@0: # a legal notice, here is a blessing:
sl@0: #
sl@0: #    May you do good and not evil.
sl@0: #    May you find forgiveness for yourself and forgive others.
sl@0: #    May you share freely, never taking more than you give.
sl@0: #
sl@0: #***********************************************************************
sl@0: # This file implements regression tests for SQLite library.  The
sl@0: # focus of this script is making sure that the names of collation
sl@0: # sequences may be quoted using double quotes in SQL statements.
sl@0: #
sl@0: # $Id: collate9.test,v 1.2 2008/07/10 00:32:42 drh Exp $
sl@0: 
sl@0: set testdir [file dirname $argv0]
sl@0: source $testdir/tester.tcl
sl@0: 
sl@0: proc reverse_sort {lhs rhs} {
sl@0:   return [string compare $rhs $lhs]
sl@0: }
sl@0: db collate "reverse sort" reverse_sort
sl@0: 
sl@0: # This procedure executes the SQL.  Then it checks to see if the OP_Sort
sl@0: # opcode was executed.  If an OP_Sort did occur, then "sort" is appended
sl@0: # to the result.  If no OP_Sort happened, then "nosort" is appended.
sl@0: #
sl@0: # This procedure is used to check to make sure sorting is or is not
sl@0: # occurring as expected.
sl@0: #
sl@0: proc cksort {sql} {
sl@0:   set ::sqlite_sort_count 0
sl@0:   set data [execsql $sql]
sl@0:   if {$::sqlite_sort_count} {set x sort} {set x nosort}
sl@0:   lappend data $x
sl@0:   return $data
sl@0: }
sl@0: 
sl@0: # Test plan:
sl@0: #
sl@0: #     collate9-1.* - Test collation sequences attached to table columns
sl@0: #     collate9-2.* - Test collation sequences attached to expressions
sl@0: #     collate9-3.* - Test collation sequences attached to an index
sl@0: #     collate9-4.* - Test collation sequences as an argument to REINDEX
sl@0: #
sl@0: 
sl@0: do_test collate9-1.1 {
sl@0:   execsql {
sl@0:     CREATE TABLE xy(x COLLATE "reverse sort", y COLLATE binary);
sl@0:     INSERT INTO xy VALUES('one', 'one');
sl@0:     INSERT INTO xy VALUES('two', 'two');
sl@0:     INSERT INTO xy VALUES('three', 'three');
sl@0:   }
sl@0: } {}
sl@0: do_test collate9-1.2 {
sl@0:   execsql { 
sl@0:     SELECT x FROM xy ORDER BY x
sl@0:   }
sl@0: } {two three one}
sl@0: do_test collate9-1.3 {
sl@0:   execsql { 
sl@0:     SELECT y FROM xy ORDER BY y
sl@0:   }
sl@0: } {one three two}
sl@0: do_test collate9-1.4 {
sl@0:   cksort { 
sl@0:     SELECT x FROM xy ORDER BY x
sl@0:   }
sl@0: } {two three one sort}
sl@0: do_test collate9-1.5 {
sl@0:   execsql { 
sl@0:     CREATE INDEX xy_i ON xy(x)
sl@0:   }
sl@0: } {}
sl@0: do_test collate9-1.6 {
sl@0:   cksort { 
sl@0:     SELECT x FROM xy ORDER BY x
sl@0:   }
sl@0: } {two three one nosort}
sl@0: 
sl@0: do_test collate9-2.1 {
sl@0:   execsql { 
sl@0:     SELECT x, x < 'seven' FROM xy ORDER BY x
sl@0:   }
sl@0: } {two 1 three 1 one 0}
sl@0: do_test collate9-2.2 {
sl@0:   execsql { 
sl@0:     SELECT y, y < 'seven' FROM xy ORDER BY x
sl@0:   }
sl@0: } {two 0 three 0 one 1}
sl@0: do_test collate9-2.3 {
sl@0:   execsql { 
sl@0:     SELECT y, y COLLATE "reverse sort" < 'seven' FROM xy ORDER BY x
sl@0:   }
sl@0: } {two 1 three 1 one 0}
sl@0: do_test collate9-2.4 {
sl@0:   execsql {
sl@0:     SELECT y FROM xy ORDER BY y
sl@0:   }
sl@0: } {one three two}
sl@0: do_test collate9-2.5 {
sl@0:   execsql {
sl@0:     SELECT y FROM xy ORDER BY y COLLATE "reverse sort"
sl@0:   }
sl@0: } {two three one}
sl@0: do_test collate9-2.6 {
sl@0:   execsql {
sl@0:     SELECT y COLLATE "reverse sort" AS aaa FROM xy ORDER BY aaa
sl@0:   }
sl@0: } {two three one}
sl@0: 
sl@0: do_test collate9-3.1 {
sl@0:   execsql {
sl@0:     CREATE INDEX xy_i2 ON xy(y COLLATE "reverse sort");
sl@0:   }
sl@0: } {}
sl@0: do_test collate9-3.2 {
sl@0:   cksort { 
sl@0:     SELECT y FROM xy ORDER BY y 
sl@0:   }
sl@0: } {one three two sort}
sl@0: do_test collate9-3.3 {
sl@0:   cksort { 
sl@0:     SELECT y FROM xy ORDER BY y COLLATE "reverse sort"
sl@0:   }
sl@0: } {two three one nosort}
sl@0: do_test collate9-3.4 {
sl@0:   cksort { 
sl@0:     SELECT y AS aaa FROM xy ORDER BY aaa
sl@0:   }
sl@0: } {one three two sort}
sl@0: do_test collate9-3.5 {
sl@0:   cksort { 
sl@0:     SELECT y COLLATE "reverse sort" AS aaa FROM xy ORDER BY aaa
sl@0:   }
sl@0: } {two three one nosort}
sl@0: 
sl@0: ifcapable reindex {
sl@0:   do_test collate9-4.1 {
sl@0:     execsql {
sl@0:       REINDEX "reverse sort"
sl@0:     }
sl@0:   } {}
sl@0: 
sl@0:   # Modify the "reverse sort" collation so that it now sorts in the same
sl@0:   # order as binary.
sl@0:   proc reverse_sort {lhs rhs} {
sl@0:     return [string compare $lhs $rhs]
sl@0:   }
sl@0: 
sl@0:   # The integrity check should now fail because the indexes created using
sl@0:   # "reverse sort" are no longer in sync with the collation sequence
sl@0:   # implementation.
sl@0:   do_test collate9-4.2 {
sl@0:     expr {"ok" eq [execsql { PRAGMA integrity_check }]}
sl@0:   } {0}
sl@0: 
sl@0:   do_test collate9-4.3 {
sl@0:     execsql {
sl@0:       REINDEX "reverse sort"
sl@0:     }
sl@0:   } {}
sl@0: 
sl@0:   # Integrity check should now pass.
sl@0:   do_test collate9-4.4 {
sl@0:     expr {"ok" eq [execsql { PRAGMA integrity_check }]}
sl@0:   } {1}
sl@0: 
sl@0:   do_test collate9-4.5 {
sl@0:     cksort {
sl@0:       SELECT x FROM xy ORDER BY x COLLATE "reverse sort"
sl@0:     }
sl@0:   } {one three two nosort}
sl@0: }
sl@0: 
sl@0: finish_test