1.1 --- /dev/null Thu Jan 01 00:00:00 1970 +0000
1.2 +++ b/os/persistentdata/persistentstorage/sqlite3api/TEST/TclScript/collate5.test Fri Jun 15 03:10:57 2012 +0200
1.3 @@ -0,0 +1,270 @@
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 file is testing DISTINCT, UNION, INTERSECT and EXCEPT
1.17 +# SELECT statements that use user-defined collation sequences. Also
1.18 +# GROUP BY clauses that use user-defined collation sequences.
1.19 +#
1.20 +# $Id: collate5.test,v 1.6 2007/11/12 15:29:19 danielk1977 Exp $
1.21 +
1.22 +set testdir [file dirname $argv0]
1.23 +source $testdir/tester.tcl
1.24 +
1.25 +
1.26 +#
1.27 +# Tests are organised as follows:
1.28 +# collate5-1.* - DISTINCT
1.29 +# collate5-2.* - Compound SELECT
1.30 +# collate5-3.* - ORDER BY on compound SELECT
1.31 +# collate5-4.* - GROUP BY
1.32 +
1.33 +# Create the collation sequence 'TEXT', purely for asthetic reasons. The
1.34 +# test cases in this script could just as easily use BINARY.
1.35 +db collate TEXT [list string compare]
1.36 +
1.37 +# Mimic the SQLite 2 collation type NUMERIC.
1.38 +db collate numeric numeric_collate
1.39 +proc numeric_collate {lhs rhs} {
1.40 + if {$lhs == $rhs} {return 0}
1.41 + return [expr ($lhs>$rhs)?1:-1]
1.42 +}
1.43 +
1.44 +#
1.45 +# These tests - collate5-1.* - focus on the DISTINCT keyword.
1.46 +#
1.47 +do_test collate5-1.0 {
1.48 + execsql {
1.49 + CREATE TABLE collate5t1(a COLLATE nocase, b COLLATE text);
1.50 +
1.51 + INSERT INTO collate5t1 VALUES('a', 'apple');
1.52 + INSERT INTO collate5t1 VALUES('A', 'Apple');
1.53 + INSERT INTO collate5t1 VALUES('b', 'banana');
1.54 + INSERT INTO collate5t1 VALUES('B', 'banana');
1.55 + INSERT INTO collate5t1 VALUES('n', NULL);
1.56 + INSERT INTO collate5t1 VALUES('N', NULL);
1.57 + }
1.58 +} {}
1.59 +do_test collate5-1.1 {
1.60 + execsql {
1.61 + SELECT DISTINCT a FROM collate5t1;
1.62 + }
1.63 +} {A B N}
1.64 +do_test collate5-1.2 {
1.65 + execsql {
1.66 + SELECT DISTINCT b FROM collate5t1;
1.67 + }
1.68 +} {{} Apple apple banana}
1.69 +do_test collate5-1.3 {
1.70 + execsql {
1.71 + SELECT DISTINCT a, b FROM collate5t1;
1.72 + }
1.73 +} {A Apple a apple B banana N {}}
1.74 +
1.75 +# The remainder of this file tests compound SELECT statements.
1.76 +# Omit it if the library is compiled such that they are omitted.
1.77 +#
1.78 +ifcapable !compound {
1.79 + finish_test
1.80 + return
1.81 +}
1.82 +
1.83 +#
1.84 +# Tests named collate5-2.* focus on UNION, EXCEPT and INTERSECT
1.85 +# queries that use user-defined collation sequences.
1.86 +#
1.87 +# collate5-2.1.* - UNION
1.88 +# collate5-2.2.* - INTERSECT
1.89 +# collate5-2.3.* - EXCEPT
1.90 +#
1.91 +do_test collate5-2.0 {
1.92 + execsql {
1.93 + CREATE TABLE collate5t2(a COLLATE text, b COLLATE nocase);
1.94 +
1.95 + INSERT INTO collate5t2 VALUES('a', 'apple');
1.96 + INSERT INTO collate5t2 VALUES('A', 'apple');
1.97 + INSERT INTO collate5t2 VALUES('b', 'banana');
1.98 + INSERT INTO collate5t2 VALUES('B', 'Banana');
1.99 + }
1.100 +} {}
1.101 +
1.102 +do_test collate5-2.1.1 {
1.103 + execsql {
1.104 + SELECT a FROM collate5t1 UNION select a FROM collate5t2;
1.105 + }
1.106 +} {A B N}
1.107 +do_test collate5-2.1.2 {
1.108 + execsql {
1.109 + SELECT a FROM collate5t2 UNION select a FROM collate5t1;
1.110 + }
1.111 +} {A B N a b n}
1.112 +do_test collate5-2.1.3 {
1.113 + execsql {
1.114 + SELECT a, b FROM collate5t1 UNION select a, b FROM collate5t2;
1.115 + }
1.116 +} {A Apple A apple B Banana b banana N {}}
1.117 +do_test collate5-2.1.4 {
1.118 + execsql {
1.119 + SELECT a, b FROM collate5t2 UNION select a, b FROM collate5t1;
1.120 + }
1.121 +} {A Apple B banana N {} a apple b banana n {}}
1.122 +
1.123 +do_test collate5-2.2.1 {
1.124 + execsql {
1.125 + SELECT a FROM collate5t1 EXCEPT select a FROM collate5t2;
1.126 + }
1.127 +} {N}
1.128 +do_test collate5-2.2.2 {
1.129 + execsql {
1.130 + SELECT a FROM collate5t2 EXCEPT select a FROM collate5t1 WHERE a != 'a';
1.131 + }
1.132 +} {A a}
1.133 +do_test collate5-2.2.3 {
1.134 + execsql {
1.135 + SELECT a, b FROM collate5t1 EXCEPT select a, b FROM collate5t2;
1.136 + }
1.137 +} {A Apple N {}}
1.138 +do_test collate5-2.2.4 {
1.139 + execsql {
1.140 + SELECT a, b FROM collate5t2 EXCEPT select a, b FROM collate5t1
1.141 + where a != 'a';
1.142 + }
1.143 +} {A apple a apple}
1.144 +
1.145 +do_test collate5-2.3.1 {
1.146 + execsql {
1.147 + SELECT a FROM collate5t1 INTERSECT select a FROM collate5t2;
1.148 + }
1.149 +} {A B}
1.150 +do_test collate5-2.3.2 {
1.151 + execsql {
1.152 + SELECT a FROM collate5t2 INTERSECT select a FROM collate5t1 WHERE a != 'a';
1.153 + }
1.154 +} {B b}
1.155 +do_test collate5-2.3.3 {
1.156 + execsql {
1.157 + SELECT a, b FROM collate5t1 INTERSECT select a, b FROM collate5t2;
1.158 + }
1.159 +} {a apple B banana}
1.160 +do_test collate5-2.3.4 {
1.161 + execsql {
1.162 + SELECT a, b FROM collate5t2 INTERSECT select a, b FROM collate5t1;
1.163 + }
1.164 +} {A apple B Banana a apple b banana}
1.165 +
1.166 +#
1.167 +# This test ensures performs a UNION operation with a bunch of different
1.168 +# length records. The goal is to test that the logic that compares records
1.169 +# for the compound SELECT operators works with record lengths that lie
1.170 +# either side of the troublesome 256 and 65536 byte marks.
1.171 +#
1.172 +set ::lens [list \
1.173 + 0 1 2 3 4 5 6 7 8 9 \
1.174 + 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 \
1.175 + 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 \
1.176 + 65520 65521 65522 65523 65524 65525 65526 65527 65528 65529 65530 \
1.177 + 65531 65532 65533 65534 65535 65536 65537 65538 65539 65540 65541 \
1.178 + 65542 65543 65544 65545 65546 65547 65548 65549 65550 65551 ]
1.179 +do_test collate5-2.4.0 {
1.180 + execsql {
1.181 + BEGIN;
1.182 + CREATE TABLE collate5t3(a, b);
1.183 + }
1.184 + foreach ii $::lens {
1.185 + execsql "INSERT INTO collate5t3 VALUES($ii, '[string repeat a $ii]');"
1.186 + }
1.187 + expr [llength [execsql {
1.188 + COMMIT;
1.189 + SELECT * FROM collate5t3 UNION SELECT * FROM collate5t3;
1.190 + }]] / 2
1.191 +} [llength $::lens]
1.192 +do_test collate5-2.4.1 {
1.193 + execsql {DROP TABLE collate5t3;}
1.194 +} {}
1.195 +unset ::lens
1.196 +
1.197 +#
1.198 +# These tests - collate5-3.* - focus on compound SELECT queries that
1.199 +# feature ORDER BY clauses.
1.200 +#
1.201 +do_test collate5-3.0 {
1.202 + execsql {
1.203 + SELECT a FROM collate5t1 UNION ALL SELECT a FROM collate5t2 ORDER BY 1;
1.204 + }
1.205 +} {a A a A b B b B n N}
1.206 +do_test collate5-3.1 {
1.207 + execsql {
1.208 + SELECT a FROM collate5t2 UNION ALL SELECT a FROM collate5t1 ORDER BY 1;
1.209 + }
1.210 +} {A A B B N a a b b n}
1.211 +do_test collate5-3.2 {
1.212 + execsql {
1.213 + SELECT a FROM collate5t1 UNION ALL SELECT a FROM collate5t2
1.214 + ORDER BY 1 COLLATE TEXT;
1.215 + }
1.216 +} {A A B B N a a b b n}
1.217 +
1.218 +do_test collate5-3.3 {
1.219 + execsql {
1.220 + CREATE TABLE collate5t_cn(a COLLATE NUMERIC);
1.221 + CREATE TABLE collate5t_ct(a COLLATE TEXT);
1.222 + INSERT INTO collate5t_cn VALUES('1');
1.223 + INSERT INTO collate5t_cn VALUES('11');
1.224 + INSERT INTO collate5t_cn VALUES('101');
1.225 + INSERT INTO collate5t_ct SELECT * FROM collate5t_cn;
1.226 + }
1.227 +} {}
1.228 +do_test collate5-3.4 {
1.229 + execsql {
1.230 + SELECT a FROM collate5t_cn INTERSECT SELECT a FROM collate5t_ct ORDER BY 1;
1.231 + }
1.232 +} {1 11 101}
1.233 +do_test collate5-3.5 {
1.234 + execsql {
1.235 + SELECT a FROM collate5t_ct INTERSECT SELECT a FROM collate5t_cn ORDER BY 1;
1.236 + }
1.237 +} {1 101 11}
1.238 +
1.239 +do_test collate5-3.20 {
1.240 + execsql {
1.241 + DROP TABLE collate5t_cn;
1.242 + DROP TABLE collate5t_ct;
1.243 + DROP TABLE collate5t1;
1.244 + DROP TABLE collate5t2;
1.245 + }
1.246 +} {}
1.247 +
1.248 +do_test collate5-4.0 {
1.249 + execsql {
1.250 + CREATE TABLE collate5t1(a COLLATE NOCASE, b COLLATE NUMERIC);
1.251 + INSERT INTO collate5t1 VALUES('a', '1');
1.252 + INSERT INTO collate5t1 VALUES('A', '1.0');
1.253 + INSERT INTO collate5t1 VALUES('b', '2');
1.254 + INSERT INTO collate5t1 VALUES('B', '3');
1.255 + }
1.256 +} {}
1.257 +do_test collate5-4.1 {
1.258 + string tolower [execsql {
1.259 + SELECT a, count(*) FROM collate5t1 GROUP BY a;
1.260 + }]
1.261 +} {a 2 b 2}
1.262 +do_test collate5-4.2 {
1.263 + execsql {
1.264 + SELECT a, b, count(*) FROM collate5t1 GROUP BY a, b ORDER BY a, b;
1.265 + }
1.266 +} {A 1.0 2 b 2 1 B 3 1}
1.267 +do_test collate5-4.3 {
1.268 + execsql {
1.269 + DROP TABLE collate5t1;
1.270 + }
1.271 +} {}
1.272 +
1.273 +finish_test