1.1 --- /dev/null Thu Jan 01 00:00:00 1970 +0000
1.2 +++ b/os/persistentdata/persistentstorage/sqlite3api/TEST/TclScript/collate1.test Fri Jun 15 03:10:57 2012 +0200
1.3 @@ -0,0 +1,307 @@
1.4 +#
1.5 +# 2001 September 15
1.6 +#
1.7 +# The author disclaims copyright to this source code. In place of
1.8 +# a legal notice, here is a blessing:
1.9 +#
1.10 +# May you do good and not evil.
1.11 +# May you find forgiveness for yourself and forgive others.
1.12 +# May you share freely, never taking more than you give.
1.13 +#
1.14 +#***********************************************************************
1.15 +# This file implements regression tests for SQLite library. The
1.16 +# focus of this script is page cache subsystem.
1.17 +#
1.18 +# $Id: collate1.test,v 1.5 2007/02/01 23:02:46 drh Exp $
1.19 +
1.20 +set testdir [file dirname $argv0]
1.21 +source $testdir/tester.tcl
1.22 +
1.23 +#
1.24 +# Tests are roughly organised as follows:
1.25 +#
1.26 +# collate1-1.* - Single-field ORDER BY with an explicit COLLATE clause.
1.27 +# collate1-2.* - Multi-field ORDER BY with an explicit COLLATE clause.
1.28 +# collate1-3.* - ORDER BY using a default collation type. Also that an
1.29 +# explict collate type overrides a default collate type.
1.30 +# collate1-4.* - ORDER BY using a data type.
1.31 +#
1.32 +
1.33 +#
1.34 +# Collation type 'HEX'. If an argument can be interpreted as a hexadecimal
1.35 +# number, then it is converted to one before the comparison is performed.
1.36 +# Numbers are less than other strings. If neither argument is a number,
1.37 +# [string compare] is used.
1.38 +#
1.39 +db collate HEX hex_collate
1.40 +proc hex_collate {lhs rhs} {
1.41 + set lhs_ishex [regexp {^(0x|)[1234567890abcdefABCDEF]+$} $lhs]
1.42 + set rhs_ishex [regexp {^(0x|)[1234567890abcdefABCDEF]+$} $rhs]
1.43 + if {$lhs_ishex && $rhs_ishex} {
1.44 + set lhsx [scan $lhs %x]
1.45 + set rhsx [scan $rhs %x]
1.46 + if {$lhs < $rhs} {return -1}
1.47 + if {$lhs == $rhs} {return 0}
1.48 + if {$lhs > $rhs} {return 1}
1.49 + }
1.50 + if {$lhs_ishex} {
1.51 + return -1;
1.52 + }
1.53 + if {$rhs_ishex} {
1.54 + return 1;
1.55 + }
1.56 + return [string compare $lhs $rhs]
1.57 +}
1.58 +db function hex {format 0x%X}
1.59 +
1.60 +# Mimic the SQLite 2 collation type NUMERIC.
1.61 +db collate numeric numeric_collate
1.62 +proc numeric_collate {lhs rhs} {
1.63 + if {$lhs == $rhs} {return 0}
1.64 + return [expr ($lhs>$rhs)?1:-1]
1.65 +}
1.66 +
1.67 +do_test collate1-1.0 {
1.68 + execsql {
1.69 + CREATE TABLE collate1t1(c1, c2);
1.70 + INSERT INTO collate1t1 VALUES(45, hex(45));
1.71 + INSERT INTO collate1t1 VALUES(NULL, NULL);
1.72 + INSERT INTO collate1t1 VALUES(281, hex(281));
1.73 + }
1.74 +} {}
1.75 +do_test collate1-1.1 {
1.76 + execsql {
1.77 + SELECT c2 FROM collate1t1 ORDER BY 1;
1.78 + }
1.79 +} {{} 0x119 0x2D}
1.80 +do_test collate1-1.2 {
1.81 + execsql {
1.82 + SELECT c2 FROM collate1t1 ORDER BY 1 COLLATE hex;
1.83 + }
1.84 +} {{} 0x2D 0x119}
1.85 +do_test collate1-1.3 {
1.86 + execsql {
1.87 + SELECT c2 FROM collate1t1 ORDER BY 1 COLLATE hex DESC;
1.88 + }
1.89 +} {0x119 0x2D {}}
1.90 +do_test collate1-1.4 {
1.91 + execsql {
1.92 + SELECT c2 FROM collate1t1 ORDER BY 1 COLLATE hex ASC;
1.93 + }
1.94 +} {{} 0x2D 0x119}
1.95 +do_test collate1-1.5 {
1.96 + execsql {
1.97 + SELECT c2 COLLATE hex FROM collate1t1 ORDER BY 1
1.98 + }
1.99 +} {{} 0x2D 0x119}
1.100 +do_test collate1-1.6 {
1.101 + execsql {
1.102 + SELECT c2 COLLATE hex FROM collate1t1 ORDER BY 1 ASC
1.103 + }
1.104 +} {{} 0x2D 0x119}
1.105 +do_test collate1-1.7 {
1.106 + execsql {
1.107 + SELECT c2 COLLATE hex FROM collate1t1 ORDER BY 1 DESC
1.108 + }
1.109 +} {0x119 0x2D {}}
1.110 +do_test collate1-1.99 {
1.111 + execsql {
1.112 + DROP TABLE collate1t1;
1.113 + }
1.114 +} {}
1.115 +
1.116 +do_test collate1-2.0 {
1.117 + execsql {
1.118 + CREATE TABLE collate1t1(c1, c2);
1.119 + INSERT INTO collate1t1 VALUES('5', '0x11');
1.120 + INSERT INTO collate1t1 VALUES('5', '0xA');
1.121 + INSERT INTO collate1t1 VALUES(NULL, NULL);
1.122 + INSERT INTO collate1t1 VALUES('7', '0xA');
1.123 + INSERT INTO collate1t1 VALUES('11', '0x11');
1.124 + INSERT INTO collate1t1 VALUES('11', '0x101');
1.125 + }
1.126 +} {}
1.127 +do_test collate1-2.2 {
1.128 + execsql {
1.129 + SELECT c1, c2 FROM collate1t1 ORDER BY 1 COLLATE numeric, 2 COLLATE hex;
1.130 + }
1.131 +} {{} {} 5 0xA 5 0x11 7 0xA 11 0x11 11 0x101}
1.132 +do_test collate1-2.3 {
1.133 + execsql {
1.134 + SELECT c1, c2 FROM collate1t1 ORDER BY 1 COLLATE binary, 2 COLLATE hex;
1.135 + }
1.136 +} {{} {} 11 0x11 11 0x101 5 0xA 5 0x11 7 0xA}
1.137 +do_test collate1-2.4 {
1.138 + execsql {
1.139 + SELECT c1, c2 FROM collate1t1 ORDER BY 1 COLLATE binary DESC, 2 COLLATE hex;
1.140 + }
1.141 +} {7 0xA 5 0xA 5 0x11 11 0x11 11 0x101 {} {}}
1.142 +do_test collate1-2.5 {
1.143 + execsql {
1.144 + SELECT c1, c2 FROM collate1t1
1.145 + ORDER BY 1 COLLATE binary DESC, 2 COLLATE hex DESC;
1.146 + }
1.147 +} {7 0xA 5 0x11 5 0xA 11 0x101 11 0x11 {} {}}
1.148 +do_test collate1-2.6 {
1.149 + execsql {
1.150 + SELECT c1, c2 FROM collate1t1
1.151 + ORDER BY 1 COLLATE binary ASC, 2 COLLATE hex ASC;
1.152 + }
1.153 +} {{} {} 11 0x11 11 0x101 5 0xA 5 0x11 7 0xA}
1.154 +do_test collate1-2.12.1 {
1.155 + execsql {
1.156 + SELECT c1 COLLATE numeric, c2 FROM collate1t1
1.157 + ORDER BY 1, 2 COLLATE hex;
1.158 + }
1.159 +} {{} {} 5 0xA 5 0x11 7 0xA 11 0x11 11 0x101}
1.160 +do_test collate1-2.12.2 {
1.161 + execsql {
1.162 + SELECT c1 COLLATE hex, c2 FROM collate1t1
1.163 + ORDER BY 1 COLLATE numeric, 2 COLLATE hex;
1.164 + }
1.165 +} {{} {} 5 0xA 5 0x11 7 0xA 11 0x11 11 0x101}
1.166 +do_test collate1-2.12.3 {
1.167 + execsql {
1.168 + SELECT c1, c2 COLLATE hex FROM collate1t1
1.169 + ORDER BY 1 COLLATE numeric, 2;
1.170 + }
1.171 +} {{} {} 5 0xA 5 0x11 7 0xA 11 0x11 11 0x101}
1.172 +do_test collate1-2.12.4 {
1.173 + execsql {
1.174 + SELECT c1 COLLATE numeric, c2 COLLATE hex
1.175 + FROM collate1t1
1.176 + ORDER BY 1, 2;
1.177 + }
1.178 +} {{} {} 5 0xA 5 0x11 7 0xA 11 0x11 11 0x101}
1.179 +do_test collate1-2.13 {
1.180 + execsql {
1.181 + SELECT c1 COLLATE binary, c2 COLLATE hex
1.182 + FROM collate1t1
1.183 + ORDER BY 1, 2;
1.184 + }
1.185 +} {{} {} 11 0x11 11 0x101 5 0xA 5 0x11 7 0xA}
1.186 +do_test collate1-2.14 {
1.187 + execsql {
1.188 + SELECT c1, c2
1.189 + FROM collate1t1 ORDER BY 1 COLLATE binary DESC, 2 COLLATE hex;
1.190 + }
1.191 +} {7 0xA 5 0xA 5 0x11 11 0x11 11 0x101 {} {}}
1.192 +do_test collate1-2.15 {
1.193 + execsql {
1.194 + SELECT c1 COLLATE binary, c2 COLLATE hex
1.195 + FROM collate1t1
1.196 + ORDER BY 1 DESC, 2 DESC;
1.197 + }
1.198 +} {7 0xA 5 0x11 5 0xA 11 0x101 11 0x11 {} {}}
1.199 +do_test collate1-2.16 {
1.200 + execsql {
1.201 + SELECT c1 COLLATE hex, c2 COLLATE binary
1.202 + FROM collate1t1
1.203 + ORDER BY 1 COLLATE binary ASC, 2 COLLATE hex ASC;
1.204 + }
1.205 +} {{} {} 11 0x11 11 0x101 5 0xA 5 0x11 7 0xA}
1.206 +do_test collate1-2.99 {
1.207 + execsql {
1.208 + DROP TABLE collate1t1;
1.209 + }
1.210 +} {}
1.211 +
1.212 +#
1.213 +# These tests ensure that the default collation type for a column is used
1.214 +# by an ORDER BY clause correctly. The focus is all the different ways
1.215 +# the column can be referenced. i.e. a, collate2t1.a, main.collate2t1.a etc.
1.216 +#
1.217 +do_test collate1-3.0 {
1.218 + execsql {
1.219 + CREATE TABLE collate1t1(a COLLATE hex, b);
1.220 + INSERT INTO collate1t1 VALUES( '0x5', 5 );
1.221 + INSERT INTO collate1t1 VALUES( '1', 1 );
1.222 + INSERT INTO collate1t1 VALUES( '0x45', 69 );
1.223 + INSERT INTO collate1t1 VALUES( NULL, NULL );
1.224 + SELECT * FROM collate1t1 ORDER BY a;
1.225 + }
1.226 +} {{} {} 1 1 0x5 5 0x45 69}
1.227 +
1.228 +do_test collate1-3.1 {
1.229 + execsql {
1.230 + SELECT * FROM collate1t1 ORDER BY 1;
1.231 + }
1.232 +} {{} {} 1 1 0x5 5 0x45 69}
1.233 +do_test collate1-3.2 {
1.234 + execsql {
1.235 + SELECT * FROM collate1t1 ORDER BY collate1t1.a;
1.236 + }
1.237 +} {{} {} 1 1 0x5 5 0x45 69}
1.238 +do_test collate1-3.3 {
1.239 + execsql {
1.240 + SELECT * FROM collate1t1 ORDER BY main.collate1t1.a;
1.241 + }
1.242 +} {{} {} 1 1 0x5 5 0x45 69}
1.243 +do_test collate1-3.4 {
1.244 + execsql {
1.245 + SELECT a as c1, b as c2 FROM collate1t1 ORDER BY c1;
1.246 + }
1.247 +} {{} {} 1 1 0x5 5 0x45 69}
1.248 +do_test collate1-3.5 {
1.249 + execsql {
1.250 + SELECT a as c1, b as c2 FROM collate1t1 ORDER BY c1 COLLATE binary;
1.251 + }
1.252 +} {{} {} 0x45 69 0x5 5 1 1}
1.253 +do_test collate1-3.5.1 {
1.254 + execsql {
1.255 + SELECT a COLLATE binary as c1, b as c2
1.256 + FROM collate1t1 ORDER BY c1;
1.257 + }
1.258 +} {{} {} 0x45 69 0x5 5 1 1}
1.259 +do_test collate1-3.6 {
1.260 + execsql {
1.261 + DROP TABLE collate1t1;
1.262 + }
1.263 +} {}
1.264 +
1.265 +# Update for SQLite version 3. The collate1-4.* test cases were written
1.266 +# before manifest types were introduced. The following test cases still
1.267 +# work, due to the 'affinity' mechanism, but they don't prove anything
1.268 +# about collation sequences.
1.269 +#
1.270 +do_test collate1-4.0 {
1.271 + execsql {
1.272 + CREATE TABLE collate1t1(c1 numeric, c2 text);
1.273 + INSERT INTO collate1t1 VALUES(1, 1);
1.274 + INSERT INTO collate1t1 VALUES(12, 12);
1.275 + INSERT INTO collate1t1 VALUES(NULL, NULL);
1.276 + INSERT INTO collate1t1 VALUES(101, 101);
1.277 + }
1.278 +} {}
1.279 +do_test collate1-4.1 {
1.280 + execsql {
1.281 + SELECT c1 FROM collate1t1 ORDER BY 1;
1.282 + }
1.283 +} {{} 1 12 101}
1.284 +do_test collate1-4.2 {
1.285 + execsql {
1.286 + SELECT c2 FROM collate1t1 ORDER BY 1;
1.287 + }
1.288 +} {{} 1 101 12}
1.289 +do_test collate1-4.3 {
1.290 + execsql {
1.291 + SELECT c2+0 FROM collate1t1 ORDER BY 1;
1.292 + }
1.293 +} {{} 1 12 101}
1.294 +do_test collate1-4.4 {
1.295 + execsql {
1.296 + SELECT c1||'' FROM collate1t1 ORDER BY 1;
1.297 + }
1.298 +} {{} 1 101 12}
1.299 +do_test collate1-4.4.1 {
1.300 + execsql {
1.301 + SELECT (c1||'') COLLATE numeric FROM collate1t1 ORDER BY 1;
1.302 + }
1.303 +} {{} 1 12 101}
1.304 +do_test collate1-4.5 {
1.305 + execsql {
1.306 + DROP TABLE collate1t1;
1.307 + }
1.308 +} {}
1.309 +
1.310 +finish_test