1.1 --- /dev/null Thu Jan 01 00:00:00 1970 +0000
1.2 +++ b/os/persistentdata/persistentstorage/sqlite3api/TEST/TclScript/collate3.test Fri Jun 15 03:10:57 2012 +0200
1.3 @@ -0,0 +1,428 @@
1.4 +# 2001 September 15
1.5 +#
1.6 +# The author disclaims copyright to this source code. In place of
1.7 +# a legal notice, here is a blessing:
1.8 +#
1.9 +# May you do good and not evil.
1.10 +# May you find forgiveness for yourself and forgive others.
1.11 +# May you share freely, never taking more than you give.
1.12 +#
1.13 +#***********************************************************************
1.14 +# This file implements regression tests for SQLite library. The
1.15 +# focus of this script is page cache subsystem.
1.16 +#
1.17 +# $Id: collate3.test,v 1.13 2008/08/20 16:35:10 drh Exp $
1.18 +
1.19 +set testdir [file dirname $argv0]
1.20 +source $testdir/tester.tcl
1.21 +
1.22 +#
1.23 +# Tests are organised as follows:
1.24 +#
1.25 +# collate3.1.* - Errors related to unknown collation sequences.
1.26 +# collate3.2.* - Errors related to undefined collation sequences.
1.27 +# collate3.3.* - Writing to a table that has an index with an undefined c.s.
1.28 +# collate3.4.* - Misc errors.
1.29 +# collate3.5.* - Collation factory.
1.30 +#
1.31 +
1.32 +#
1.33 +# These tests ensure that when a user executes a statement with an
1.34 +# unknown collation sequence an error is returned.
1.35 +#
1.36 +do_test collate3-1.0 {
1.37 + execsql {
1.38 + CREATE TABLE collate3t1(c1);
1.39 + }
1.40 +} {}
1.41 +do_test collate3-1.1 {
1.42 + catchsql {
1.43 + SELECT * FROM collate3t1 ORDER BY 1 collate garbage;
1.44 + }
1.45 +} {1 {no such collation sequence: garbage}}
1.46 +do_test collate3-1.2 {
1.47 + catchsql {
1.48 + CREATE TABLE collate3t2(c1 collate garbage);
1.49 + }
1.50 +} {1 {no such collation sequence: garbage}}
1.51 +do_test collate3-1.3 {
1.52 + catchsql {
1.53 + CREATE INDEX collate3i1 ON collate3t1(c1 COLLATE garbage);
1.54 + }
1.55 +} {1 {no such collation sequence: garbage}}
1.56 +
1.57 +execsql {
1.58 + DROP TABLE collate3t1;
1.59 +}
1.60 +
1.61 +#
1.62 +# Create a table with a default collation sequence, then close
1.63 +# and re-open the database without re-registering the collation
1.64 +# sequence. Then make sure the library stops us from using
1.65 +# the collation sequence in:
1.66 +# * an explicitly collated ORDER BY
1.67 +# * an ORDER BY that uses the default collation sequence
1.68 +# * an expression (=)
1.69 +# * a CREATE TABLE statement
1.70 +# * a CREATE INDEX statement that uses a default collation sequence
1.71 +# * a GROUP BY that uses the default collation sequence
1.72 +# * a SELECT DISTINCT that uses the default collation sequence
1.73 +# * Compound SELECTs that uses the default collation sequence
1.74 +# * An ORDER BY on a compound SELECT with an explicit ORDER BY.
1.75 +#
1.76 +do_test collate3-2.0 {
1.77 + db collate string_compare {string compare}
1.78 + execsql {
1.79 + CREATE TABLE collate3t1(c1 COLLATE string_compare, c2);
1.80 + }
1.81 + db close
1.82 + sqlite3 db test.db
1.83 + expr 0
1.84 +} 0
1.85 +do_test collate3-2.1 {
1.86 + catchsql {
1.87 + SELECT * FROM collate3t1 ORDER BY 1 COLLATE string_compare;
1.88 + }
1.89 +} {1 {no such collation sequence: string_compare}}
1.90 +do_test collate3-2.2 {
1.91 + catchsql {
1.92 + SELECT * FROM collate3t1 ORDER BY c1;
1.93 + }
1.94 +} {1 {no such collation sequence: string_compare}}
1.95 +do_test collate3-2.3 {
1.96 + catchsql {
1.97 + SELECT * FROM collate3t1 WHERE c1 = 'xxx';
1.98 + }
1.99 +} {1 {no such collation sequence: string_compare}}
1.100 +do_test collate3-2.4 {
1.101 + catchsql {
1.102 + CREATE TABLE collate3t2(c1 COLLATE string_compare);
1.103 + }
1.104 +} {1 {no such collation sequence: string_compare}}
1.105 +do_test collate3-2.5 {
1.106 + catchsql {
1.107 + CREATE INDEX collate3t1_i1 ON collate3t1(c1);
1.108 + }
1.109 +} {1 {no such collation sequence: string_compare}}
1.110 +do_test collate3-2.6 {
1.111 + catchsql {
1.112 + SELECT * FROM collate3t1;
1.113 + }
1.114 +} {0 {}}
1.115 +do_test collate3-2.7.1 {
1.116 + catchsql {
1.117 + SELECT count(*) FROM collate3t1 GROUP BY c1;
1.118 + }
1.119 +} {1 {no such collation sequence: string_compare}}
1.120 +# do_test collate3-2.7.2 {
1.121 +# catchsql {
1.122 +# SELECT * FROM collate3t1 GROUP BY c1;
1.123 +# }
1.124 +# } {1 {GROUP BY may only be used on aggregate queries}}
1.125 +do_test collate3-2.7.2 {
1.126 + catchsql {
1.127 + SELECT * FROM collate3t1 GROUP BY c1;
1.128 + }
1.129 +} {1 {no such collation sequence: string_compare}}
1.130 +do_test collate3-2.8 {
1.131 + catchsql {
1.132 + SELECT DISTINCT c1 FROM collate3t1;
1.133 + }
1.134 +} {1 {no such collation sequence: string_compare}}
1.135 +
1.136 +ifcapable compound {
1.137 + do_test collate3-2.9 {
1.138 + catchsql {
1.139 + SELECT c1 FROM collate3t1 UNION SELECT c1 FROM collate3t1;
1.140 + }
1.141 + } {1 {no such collation sequence: string_compare}}
1.142 + do_test collate3-2.10 {
1.143 + catchsql {
1.144 + SELECT c1 FROM collate3t1 EXCEPT SELECT c1 FROM collate3t1;
1.145 + }
1.146 + } {1 {no such collation sequence: string_compare}}
1.147 + do_test collate3-2.11 {
1.148 + catchsql {
1.149 + SELECT c1 FROM collate3t1 INTERSECT SELECT c1 FROM collate3t1;
1.150 + }
1.151 + } {1 {no such collation sequence: string_compare}}
1.152 + do_test collate3-2.12 {
1.153 + catchsql {
1.154 + SELECT c1 FROM collate3t1 UNION ALL SELECT c1 FROM collate3t1;
1.155 + }
1.156 + } {0 {}}
1.157 + do_test collate3-2.13 {
1.158 + catchsql {
1.159 + SELECT 10 UNION ALL SELECT 20 ORDER BY 1 COLLATE string_compare;
1.160 + }
1.161 + } {1 {no such collation sequence: string_compare}}
1.162 + do_test collate3-2.14 {
1.163 + catchsql {
1.164 + SELECT 10 INTERSECT SELECT 20 ORDER BY 1 COLLATE string_compare;
1.165 + }
1.166 + } {1 {no such collation sequence: string_compare}}
1.167 + do_test collate3-2.15 {
1.168 + catchsql {
1.169 + SELECT 10 EXCEPT SELECT 20 ORDER BY 1 COLLATE string_compare;
1.170 + }
1.171 + } {1 {no such collation sequence: string_compare}}
1.172 + do_test collate3-2.16 {
1.173 + catchsql {
1.174 + SELECT 10 UNION SELECT 20 ORDER BY 1 COLLATE string_compare;
1.175 + }
1.176 + } {1 {no such collation sequence: string_compare}}
1.177 + do_test collate3-2.17 {
1.178 + catchsql {
1.179 + SELECT c1 FROM collate3t1 UNION ALL SELECT c1 FROM collate3t1 ORDER BY 1;
1.180 + }
1.181 + } {1 {no such collation sequence: string_compare}}
1.182 +} ;# ifcapable compound
1.183 +
1.184 +#
1.185 +# Create an index that uses a collation sequence then close and
1.186 +# re-open the database without re-registering the collation
1.187 +# sequence. Then check that for the table with the index
1.188 +# * An INSERT fails,
1.189 +# * An UPDATE on the column with the index fails,
1.190 +# * An UPDATE on a different column succeeds.
1.191 +# * A DELETE with a WHERE clause fails
1.192 +# * A DELETE without a WHERE clause succeeds
1.193 +#
1.194 +# Also, ensure that the restrictions tested by collate3-2.* still
1.195 +# apply after the index has been created.
1.196 +#
1.197 +do_test collate3-3.0 {
1.198 + db collate string_compare {string compare}
1.199 + execsql {
1.200 + CREATE INDEX collate3t1_i1 ON collate3t1(c1);
1.201 + INSERT INTO collate3t1 VALUES('xxx', 'yyy');
1.202 + }
1.203 + db close
1.204 + sqlite3 db test.db
1.205 + expr 0
1.206 +} 0
1.207 +db eval {select * from collate3t1}
1.208 +do_test collate3-3.1 {
1.209 + catchsql {
1.210 + INSERT INTO collate3t1 VALUES('xxx', 0);
1.211 + }
1.212 +} {1 {no such collation sequence: string_compare}}
1.213 +do_test collate3-3.2 {
1.214 + catchsql {
1.215 + UPDATE collate3t1 SET c1 = 'xxx';
1.216 + }
1.217 +} {1 {no such collation sequence: string_compare}}
1.218 +do_test collate3-3.3 {
1.219 + catchsql {
1.220 + UPDATE collate3t1 SET c2 = 'xxx';
1.221 + }
1.222 +} {0 {}}
1.223 +do_test collate3-3.4 {
1.224 + catchsql {
1.225 + DELETE FROM collate3t1 WHERE 1;
1.226 + }
1.227 +} {1 {no such collation sequence: string_compare}}
1.228 +do_test collate3-3.5 {
1.229 + catchsql {
1.230 + SELECT * FROM collate3t1;
1.231 + }
1.232 +} {0 {xxx xxx}}
1.233 +do_test collate3-3.6 {
1.234 + catchsql {
1.235 + DELETE FROM collate3t1;
1.236 + }
1.237 +} {0 {}}
1.238 +ifcapable {integrityck} {
1.239 + do_test collate3-3.8 {
1.240 + catchsql {
1.241 + PRAGMA integrity_check
1.242 + }
1.243 + } {1 {no such collation sequence: string_compare}}
1.244 +}
1.245 +do_test collate3-3.9 {
1.246 + catchsql {
1.247 + SELECT * FROM collate3t1;
1.248 + }
1.249 +} {0 {}}
1.250 +do_test collate3-3.10 {
1.251 + catchsql {
1.252 + SELECT * FROM collate3t1 ORDER BY 1 COLLATE string_compare;
1.253 + }
1.254 +} {1 {no such collation sequence: string_compare}}
1.255 +do_test collate3-3.11 {
1.256 + catchsql {
1.257 + SELECT * FROM collate3t1 ORDER BY c1;
1.258 + }
1.259 +} {1 {no such collation sequence: string_compare}}
1.260 +do_test collate3-3.12 {
1.261 + catchsql {
1.262 + SELECT * FROM collate3t1 WHERE c1 = 'xxx';
1.263 + }
1.264 +} {1 {no such collation sequence: string_compare}}
1.265 +do_test collate3-3.13 {
1.266 + catchsql {
1.267 + CREATE TABLE collate3t2(c1 COLLATE string_compare);
1.268 + }
1.269 +} {1 {no such collation sequence: string_compare}}
1.270 +do_test collate3-3.14 {
1.271 + catchsql {
1.272 + CREATE INDEX collate3t1_i2 ON collate3t1(c1);
1.273 + }
1.274 +} {1 {no such collation sequence: string_compare}}
1.275 +do_test collate3-3.15 {
1.276 + execsql {
1.277 + DROP TABLE collate3t1;
1.278 + }
1.279 +} {}
1.280 +
1.281 +# Check we can create an index that uses an explicit collation
1.282 +# sequence and then close and re-open the database.
1.283 +do_test collate3-4.6 {
1.284 + db collate user_defined "string compare"
1.285 + execsql {
1.286 + CREATE TABLE collate3t1(a, b);
1.287 + INSERT INTO collate3t1 VALUES('hello', NULL);
1.288 + CREATE INDEX collate3i1 ON collate3t1(a COLLATE user_defined);
1.289 + }
1.290 +} {}
1.291 +do_test collate3-4.7 {
1.292 + db close
1.293 + sqlite3 db test.db
1.294 + catchsql {
1.295 + SELECT * FROM collate3t1 ORDER BY a COLLATE user_defined;
1.296 + }
1.297 +} {1 {no such collation sequence: user_defined}}
1.298 +do_test collate3-4.8.1 {
1.299 + db collate user_defined "string compare"
1.300 + catchsql {
1.301 + SELECT * FROM collate3t1 ORDER BY a COLLATE user_defined;
1.302 + }
1.303 +} {0 {hello {}}}
1.304 +do_test collate3-4.8.2 {
1.305 + db close
1.306 + lindex [catch {
1.307 + sqlite3 db test.db
1.308 + }] 0
1.309 +} {0}
1.310 +do_test collate3-4.8.3 {
1.311 + execsql {
1.312 + DROP TABLE collate3t1;
1.313 + }
1.314 +} {}
1.315 +
1.316 +# Compare strings as numbers.
1.317 +proc numeric_compare {lhs rhs} {
1.318 + if {$rhs > $lhs} {
1.319 + set res -1
1.320 + } else {
1.321 + set res [expr ($lhs > $rhs)?1:0]
1.322 + }
1.323 + return $res
1.324 +}
1.325 +
1.326 +# Check we can create a view that uses an explicit collation
1.327 +# sequence and then close and re-open the database.
1.328 +ifcapable view {
1.329 +do_test collate3-4.9 {
1.330 + db collate user_defined numeric_compare
1.331 + execsql {
1.332 + CREATE TABLE collate3t1(a, b);
1.333 + INSERT INTO collate3t1 VALUES('2', NULL);
1.334 + INSERT INTO collate3t1 VALUES('101', NULL);
1.335 + INSERT INTO collate3t1 VALUES('12', NULL);
1.336 + CREATE VIEW collate3v1 AS SELECT * FROM collate3t1
1.337 + ORDER BY 1 COLLATE user_defined;
1.338 + SELECT * FROM collate3v1;
1.339 + }
1.340 +} {2 {} 12 {} 101 {}}
1.341 +do_test collate3-4.10 {
1.342 + db close
1.343 + sqlite3 db test.db
1.344 + catchsql {
1.345 + SELECT * FROM collate3v1;
1.346 + }
1.347 +} {1 {no such collation sequence: user_defined}}
1.348 +do_test collate3-4.11 {
1.349 + db collate user_defined numeric_compare
1.350 + catchsql {
1.351 + SELECT * FROM collate3v1;
1.352 + }
1.353 +} {0 {2 {} 12 {} 101 {}}}
1.354 +do_test collate3-4.12 {
1.355 + execsql {
1.356 + DROP TABLE collate3t1;
1.357 + }
1.358 +} {}
1.359 +} ;# ifcapable view
1.360 +
1.361 +#
1.362 +# Test the collation factory. In the code, the "no such collation sequence"
1.363 +# message is only generated in two places. So these tests just test that
1.364 +# the collation factory can be called once from each of those points.
1.365 +#
1.366 +do_test collate3-5.0 {
1.367 + catchsql {
1.368 + CREATE TABLE collate3t1(a);
1.369 + INSERT INTO collate3t1 VALUES(10);
1.370 + SELECT a FROM collate3t1 ORDER BY 1 COLLATE unk;
1.371 + }
1.372 +} {1 {no such collation sequence: unk}}
1.373 +do_test collate3-5.1 {
1.374 + set ::cfact_cnt 0
1.375 + proc cfact {nm} {
1.376 + db collate $nm {string compare}
1.377 + incr ::cfact_cnt
1.378 + }
1.379 + db collation_needed cfact
1.380 +} {}
1.381 +do_test collate3-5.2 {
1.382 + catchsql {
1.383 + SELECT a FROM collate3t1 ORDER BY 1 COLLATE unk;
1.384 + }
1.385 +} {0 10}
1.386 +do_test collate3-5.3 {
1.387 + set ::cfact_cnt
1.388 +} {1}
1.389 +do_test collate3-5.4 {
1.390 + catchsql {
1.391 + SELECT a FROM collate3t1 ORDER BY 1 COLLATE unk;
1.392 + }
1.393 +} {0 10}
1.394 +do_test collate3-5.5 {
1.395 + set ::cfact_cnt
1.396 +} {1}
1.397 +do_test collate3-5.6 {
1.398 + catchsql {
1.399 + SELECT a FROM collate3t1 ORDER BY 1 COLLATE unk;
1.400 + }
1.401 +} {0 10}
1.402 +do_test collate3-5.7 {
1.403 + execsql {
1.404 + DROP TABLE collate3t1;
1.405 + CREATE TABLE collate3t1(a COLLATE unk);
1.406 + }
1.407 + db close
1.408 + sqlite3 db test.db
1.409 + catchsql {
1.410 + SELECT a FROM collate3t1 ORDER BY 1;
1.411 + }
1.412 +} {1 {no such collation sequence: unk}}
1.413 +do_test collate3-5.8 {
1.414 + set ::cfact_cnt 0
1.415 + proc cfact {nm} {
1.416 + db collate $nm {string compare}
1.417 + incr ::cfact_cnt
1.418 + }
1.419 + db collation_needed cfact
1.420 + catchsql {
1.421 + SELECT a FROM collate3t1 ORDER BY 1;
1.422 + }
1.423 +} {0 {}}
1.424 +
1.425 +do_test collate3-5.9 {
1.426 + execsql {
1.427 + DROP TABLE collate3t1;
1.428 + }
1.429 +} {}
1.430 +
1.431 +finish_test