wasCSharpSQLite – Blame information for rev 4

Subversion Repositories:
Rev:
Rev Author Line No. Line
1 office 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