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
18set testdir [file dirname $argv0]
19source $testdir/tester.tcl
20
21proc reverse_sort {lhs rhs} {
22  return [string compare $rhs $lhs]
23}
24db 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#
33proc 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
49do_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} {}
57do_test collate9-1.2 {
58  execsql { 
59    SELECT x FROM xy ORDER BY x
60  }
61} {two three one}
62do_test collate9-1.3 {
63  execsql { 
64    SELECT y FROM xy ORDER BY y
65  }
66} {one three two}
67do_test collate9-1.4 {
68  cksort { 
69    SELECT x FROM xy ORDER BY x
70  }
71} {two three one sort}
72do_test collate9-1.5 {
73  execsql { 
74    CREATE INDEX xy_i ON xy(x)
75  }
76} {}
77do_test collate9-1.6 {
78  cksort { 
79    SELECT x FROM xy ORDER BY x
80  }
81} {two three one nosort}
82
83do_test collate9-2.1 {
84  execsql { 
85    SELECT x, x < 'seven' FROM xy ORDER BY x
86  }
87} {two 1 three 1 one 0}
88do_test collate9-2.2 {
89  execsql { 
90    SELECT y, y < 'seven' FROM xy ORDER BY x
91  }
92} {two 0 three 0 one 1}
93do_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}
98do_test collate9-2.4 {
99  execsql {
100    SELECT y FROM xy ORDER BY y
101  }
102} {one three two}
103do_test collate9-2.5 {
104  execsql {
105    SELECT y FROM xy ORDER BY y COLLATE "reverse sort"
106  }
107} {two three one}
108do_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
114do_test collate9-3.1 {
115  execsql {
116    CREATE INDEX xy_i2 ON xy(y COLLATE "reverse sort");
117  }
118} {}
119do_test collate9-3.2 {
120  cksort { 
121    SELECT y FROM xy ORDER BY y 
122  }
123} {one three two sort}
124do_test collate9-3.3 {
125  cksort { 
126    SELECT y FROM xy ORDER BY y COLLATE "reverse sort"
127  }
128} {two three one nosort}
129do_test collate9-3.4 {
130  cksort { 
131    SELECT y AS aaa FROM xy ORDER BY aaa
132  }
133} {one three two sort}
134do_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
140ifcapable 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
178finish_test
179