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