Annotation of embedaddon/sqlite3/test/collate9.test, revision 1.1.1.1
1.1 misho 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
FreeBSD-CVSweb <freebsd-cvsweb@FreeBSD.org>
![[BACK]](/icons/cvsweb/back.gif){kind=icon}
Return to collate9.test CVS log ![[TXT]](/icons/cvsweb/text.gif){kind=icon}
![[DIR]](/icons/cvsweb/dir.gif){kind=icon}
Up to [ELWIX - Embedded LightWeight unIX -] / embedaddon / sqlite3 / test