AryaWu/sqlite
0
1# 2001 September 152#3# The author disclaims copyright to this source code. In place of4# a legal notice, here is a blessing:5#6# May you do good and not evil.7# May you find forgiveness for yourself and forgive others.8# May you share freely, never taking more than you give.9#10#***********************************************************************11# This file implements regression tests for SQLite library. The12# focus of this script is page cache subsystem.13#14# $Id: collate3.test,v 1.13 2008/08/20 16:35:10 drh Exp $15 16set testdir [file dirname $argv0]17source $testdir/tester.tcl18 19#20# Tests are organised as follows:21#22# collate3.1.* - Errors related to unknown collation sequences.23# collate3.2.* - Errors related to undefined collation sequences.24# collate3.3.* - Writing to a table that has an index with an undefined c.s.25# collate3.4.* - Misc errors.26# collate3.5.* - Collation factory.27#28 29#30# These tests ensure that when a user executes a statement with an 31# unknown collation sequence an error is returned.32#33do_test collate3-1.0 {34 execsql {35 CREATE TABLE collate3t1(c1 UNIQUE);36 }37} {}38do_test collate3-1.1 {39 catchsql {40 SELECT * FROM collate3t1 ORDER BY 1 collate garbage;41 }42} {1 {no such collation sequence: garbage}}43do_test collate3-1.1.2 {44 catchsql {45 SELECT DISTINCT c1 COLLATE garbage FROM collate3t1;46 }47} {1 {no such collation sequence: garbage}}48do_test collate3-1.2 {49 catchsql {50 CREATE TABLE collate3t2(c1 collate garbage);51 }52} {1 {no such collation sequence: garbage}}53do_test collate3-1.3 {54 catchsql {55 CREATE INDEX collate3i1 ON collate3t1(c1 COLLATE garbage);56 }57} {1 {no such collation sequence: garbage}}58 59execsql {60 DROP TABLE collate3t1;61}62 63proc caseless {a b} { string compare -nocase $a $b }64do_test collate3-1.4 {65 db collate caseless caseless66 execsql { 67 CREATE TABLE t1(a COLLATE caseless); 68 INSERT INTO t1 VALUES('Abc2');69 INSERT INTO t1 VALUES('abc1');70 INSERT INTO t1 VALUES('aBc3');71 }72 execsql { SELECT * FROM t1 ORDER BY a }73} {abc1 Abc2 aBc3}74 75do_test collate3-1.5 {76 db close77 sqlite3 db test.db78 catchsql { SELECT * FROM t1 ORDER BY a }79} {1 {no such collation sequence: caseless}}80 81do_test collate3-1.6.1 {82 db collate caseless caseless83 execsql { CREATE INDEX i1 ON t1(a) }84 execsql { SELECT * FROM t1 ORDER BY a }85} {abc1 Abc2 aBc3}86 87do_test collate3-1.6.2 {88 db close89 sqlite3 db test.db90 catchsql { SELECT * FROM t1 ORDER BY a }91} {1 {no such collation sequence: caseless}}92 93do_test collate3-1.6.3 {94 db close95 sqlite3 db test.db96 catchsql { PRAGMA integrity_check }97} {1 {no such collation sequence: caseless}}98 99do_test collate3-1.6.4 {100 db close101 sqlite3 db test.db102 catchsql { REINDEX }103} {1 {no such collation sequence: caseless}}104 105do_test collate3-1.7.1 {106 db collate caseless caseless107 execsql {108 DROP TABLE t1;109 CREATE TABLE t1(a);110 CREATE INDEX i1 ON t1(a COLLATE caseless);111 INSERT INTO t1 VALUES('Abc2');112 INSERT INTO t1 VALUES('abc1');113 INSERT INTO t1 VALUES('aBc3');114 SELECT * FROM t1 ORDER BY a COLLATE caseless;115 }116} {abc1 Abc2 aBc3}117 118do_test collate3-1.7.2 {119 db close120 sqlite3 db test.db121 catchsql { SELECT * FROM t1 ORDER BY a COLLATE caseless}122} {1 {no such collation sequence: caseless}}123 124do_test collate3-1.7.4 {125 db close126 sqlite3 db test.db127 catchsql { REINDEX }128} {1 {no such collation sequence: caseless}}129 130do_test collate3-1.7.3 {131 db close132 sqlite3 db test.db133 catchsql { PRAGMA integrity_check }134} {1 {no such collation sequence: caseless}}135 136do_test collate3-1.7.4 {137 db close138 sqlite3 db test.db139 catchsql { REINDEX }140} {1 {no such collation sequence: caseless}}141 142do_test collate3-1.7.5 {143 db close144 sqlite3 db test.db145 db collate caseless caseless146 catchsql { PRAGMA integrity_check }147} {0 ok}148 149proc needed {nm} { db collate caseless caseless }150do_test collate3-1.7.6 {151 db close152 sqlite3 db test.db153 db collation_needed needed154 catchsql { PRAGMA integrity_check }155} {0 ok}156 157do_test collate3-1.8 {158 execsql { DROP TABLE t1 }159} {}160 161#162# Create a table with a default collation sequence, then close163# and re-open the database without re-registering the collation164# sequence. Then make sure the library stops us from using165# the collation sequence in:166# * an explicitly collated ORDER BY167# * an ORDER BY that uses the default collation sequence168# * an expression (=)169# * a CREATE TABLE statement170# * a CREATE INDEX statement that uses a default collation sequence171# * a GROUP BY that uses the default collation sequence172# * a SELECT DISTINCT that uses the default collation sequence173# * Compound SELECTs that uses the default collation sequence174# * An ORDER BY on a compound SELECT with an explicit ORDER BY.175#176do_test collate3-2.0 {177 db collate string_compare {string compare}178 execsql {179 CREATE TABLE collate3t1(c1 COLLATE string_compare, c2);180 }181 db close182 sqlite3 db test.db183 expr 0184} 0185do_test collate3-2.1 {186 catchsql {187 SELECT * FROM collate3t1 ORDER BY 1 COLLATE string_compare;188 }189} {1 {no such collation sequence: string_compare}} 190do_test collate3-2.2 {191 catchsql {192 SELECT * FROM collate3t1 ORDER BY c1;193 }194} {1 {no such collation sequence: string_compare}} 195do_test collate3-2.3 {196 catchsql {197 SELECT * FROM collate3t1 WHERE c1 = 'xxx';198 }199} {1 {no such collation sequence: string_compare}} 200do_test collate3-2.4 {201 catchsql {202 CREATE TABLE collate3t2(c1 COLLATE string_compare);203 }204} {1 {no such collation sequence: string_compare}} 205do_test collate3-2.5 {206 catchsql {207 CREATE INDEX collate3t1_i1 ON collate3t1(c1);208 }209} {1 {no such collation sequence: string_compare}}210do_test collate3-2.6 {211 catchsql {212 SELECT * FROM collate3t1;213 }214} {0 {}}215do_test collate3-2.7.1 {216 catchsql {217 SELECT count(*) FROM collate3t1 GROUP BY c1;218 }219} {1 {no such collation sequence: string_compare}} 220# do_test collate3-2.7.2 {221# catchsql {222# SELECT * FROM collate3t1 GROUP BY c1;223# }224# } {1 {GROUP BY may only be used on aggregate queries}}225do_test collate3-2.7.2 {226 catchsql {227 SELECT * FROM collate3t1 GROUP BY c1;228 }229} {1 {no such collation sequence: string_compare}} 230do_test collate3-2.8 {231 catchsql {232 SELECT DISTINCT c1 FROM collate3t1;233 }234} {1 {no such collation sequence: string_compare}} 235 236ifcapable compound {237 do_test collate3-2.9 {238 catchsql {239 SELECT c1 FROM collate3t1 UNION SELECT c1 FROM collate3t1;240 }241 } {1 {no such collation sequence: string_compare}} 242 do_test collate3-2.10 {243 catchsql {244 SELECT c1 FROM collate3t1 EXCEPT SELECT c1 FROM collate3t1;245 }246 } {1 {no such collation sequence: string_compare}} 247 do_test collate3-2.11 {248 catchsql {249 SELECT c1 FROM collate3t1 INTERSECT SELECT c1 FROM collate3t1;250 }251 } {1 {no such collation sequence: string_compare}} 252 do_test collate3-2.12 {253 catchsql {254 SELECT c1 FROM collate3t1 UNION ALL SELECT c1 FROM collate3t1;255 }256 } {0 {}}257 do_test collate3-2.13 {258 catchsql {259 SELECT 10 UNION ALL SELECT 20 ORDER BY 1 COLLATE string_compare;260 }261 } {1 {no such collation sequence: string_compare}} 262 do_test collate3-2.14 {263 catchsql {264 SELECT 10 INTERSECT SELECT 20 ORDER BY 1 COLLATE string_compare;265 }266 } {1 {no such collation sequence: string_compare}} 267 do_test collate3-2.15 {268 catchsql {269 SELECT 10 EXCEPT SELECT 20 ORDER BY 1 COLLATE string_compare;270 }271 } {1 {no such collation sequence: string_compare}} 272 do_test collate3-2.16 {273 catchsql {274 SELECT 10 UNION SELECT 20 ORDER BY 1 COLLATE string_compare;275 }276 } {1 {no such collation sequence: string_compare}} 277 do_test collate3-2.17 {278 catchsql {279 SELECT c1 FROM collate3t1 UNION ALL SELECT c1 FROM collate3t1 ORDER BY 1;280 }281 } {1 {no such collation sequence: string_compare}} 282} ;# ifcapable compound283 284#285# Create an index that uses a collation sequence then close and286# re-open the database without re-registering the collation287# sequence. Then check that for the table with the index 288# * An INSERT fails,289# * An UPDATE on the column with the index fails,290# * An UPDATE on a different column succeeds.291# * A DELETE with a WHERE clause fails292# * A DELETE without a WHERE clause succeeds293#294# Also, ensure that the restrictions tested by collate3-2.* still295# apply after the index has been created.296#297do_test collate3-3.0 {298 db collate string_compare {string compare}299 execsql {300 CREATE INDEX collate3t1_i1 ON collate3t1(c1);301 INSERT INTO collate3t1 VALUES('xxx', 'yyy');302 }303 db close304 sqlite3 db test.db305 expr 0306} 0307db eval {select * from collate3t1}308do_test collate3-3.1 {309 catchsql {310 INSERT INTO collate3t1 VALUES('xxx', 0);311 }312} {1 {no such collation sequence: string_compare}} 313do_test collate3-3.2 {314 catchsql {315 UPDATE collate3t1 SET c1 = 'xxx';316 }317} {1 {no such collation sequence: string_compare}} 318do_test collate3-3.3 {319 catchsql {320 UPDATE collate3t1 SET c2 = 'xxx';321 }322} {0 {}}323do_test collate3-3.4 {324 catchsql {325 DELETE FROM collate3t1 WHERE 1;326 }327} {1 {no such collation sequence: string_compare}} 328do_test collate3-3.5 {329 catchsql {330 SELECT * FROM collate3t1;331 }332} {0 {xxx xxx}}333do_test collate3-3.6 {334 catchsql {335 DELETE FROM collate3t1;336 }337} {0 {}}338ifcapable {integrityck} {339 do_test collate3-3.8 {340 catchsql {341 PRAGMA integrity_check342 }343 } {1 {no such collation sequence: string_compare}}344}345do_test collate3-3.9 {346 catchsql {347 SELECT * FROM collate3t1;348 }349} {0 {}}350do_test collate3-3.10 {351 catchsql {352 SELECT * FROM collate3t1 ORDER BY 1 COLLATE string_compare;353 }354} {1 {no such collation sequence: string_compare}} 355do_test collate3-3.11 {356 catchsql {357 SELECT * FROM collate3t1 ORDER BY c1;358 }359} {1 {no such collation sequence: string_compare}} 360do_test collate3-3.12 {361 catchsql {362 SELECT * FROM collate3t1 WHERE c1 = 'xxx';363 }364} {1 {no such collation sequence: string_compare}} 365do_test collate3-3.13 {366 catchsql {367 CREATE TABLE collate3t2(c1 COLLATE string_compare);368 }369} {1 {no such collation sequence: string_compare}} 370do_test collate3-3.14 {371 catchsql {372 CREATE INDEX collate3t1_i2 ON collate3t1(c1);373 }374} {1 {no such collation sequence: string_compare}} 375do_test collate3-3.15 {376 execsql {377 DROP TABLE collate3t1;378 }379} {}380 381# Check we can create an index that uses an explicit collation 382# sequence and then close and re-open the database.383do_test collate3-4.6 {384 db collate user_defined "string compare"385 execsql {386 CREATE TABLE collate3t1(a, b);387 INSERT INTO collate3t1 VALUES('hello', NULL);388 CREATE INDEX collate3i1 ON collate3t1(a COLLATE user_defined);389 }390} {}391do_test collate3-4.7 {392 db close393 sqlite3 db test.db394 catchsql {395 SELECT * FROM collate3t1 ORDER BY a COLLATE user_defined;396 }397} {1 {no such collation sequence: user_defined}}398do_test collate3-4.8.1 {399 db collate user_defined "string compare"400 catchsql {401 SELECT * FROM collate3t1 ORDER BY a COLLATE user_defined;402 }403} {0 {hello {}}}404do_test collate3-4.8.2 {405 db close406 lindex [catch {407 sqlite3 db test.db408 }] 0409} {0}410do_test collate3-4.8.3 {411 execsql {412 DROP TABLE collate3t1;413 }414} {}415 416# Compare strings as numbers.417proc numeric_compare {lhs rhs} {418 if {$rhs > $lhs} {419 set res -1420 } else {421 set res [expr ($lhs > $rhs)?1:0]422 }423 return $res424}425 426# Check we can create a view that uses an explicit collation 427# sequence and then close and re-open the database.428ifcapable view {429do_test collate3-4.9 {430 db collate user_defined numeric_compare431 execsql {432 CREATE TABLE collate3t1(a, b);433 INSERT INTO collate3t1 VALUES('2', NULL);434 INSERT INTO collate3t1 VALUES('101', NULL);435 INSERT INTO collate3t1 VALUES('12', NULL);436 CREATE VIEW collate3v1 AS SELECT * FROM collate3t1 437 ORDER BY 1 COLLATE user_defined;438 SELECT * FROM collate3v1;439 }440} {2 {} 12 {} 101 {}}441do_test collate3-4.10 {442 db close443 sqlite3 db test.db444 catchsql {445 SELECT * FROM collate3v1;446 }447} {1 {no such collation sequence: user_defined}}448do_test collate3-4.11 {449 db collate user_defined numeric_compare450 catchsql {451 SELECT * FROM collate3v1;452 }453} {0 {2 {} 12 {} 101 {}}}454do_test collate3-4.12 {455 execsql {456 DROP TABLE collate3t1;457 }458} {}459} ;# ifcapable view460 461#462# Test the collation factory. In the code, the "no such collation sequence"463# message is only generated in two places. So these tests just test that464# the collation factory can be called once from each of those points.465#466do_test collate3-5.0 {467 catchsql {468 CREATE TABLE collate3t1(a);469 INSERT INTO collate3t1 VALUES(10);470 SELECT a FROM collate3t1 ORDER BY 1 COLLATE unk;471 }472} {1 {no such collation sequence: unk}}473do_test collate3-5.1 {474 set ::cfact_cnt 0475 proc cfact {nm} {476 db collate $nm {string compare}477 incr ::cfact_cnt478 }479 db collation_needed cfact480} {}481do_test collate3-5.2 {482 catchsql {483 SELECT a FROM collate3t1 ORDER BY 1 COLLATE unk;484 }485} {0 10}486do_test collate3-5.3 {487 set ::cfact_cnt488} {1}489do_test collate3-5.4 {490 catchsql {491 SELECT a FROM collate3t1 ORDER BY 1 COLLATE unk;492 }493} {0 10}494do_test collate3-5.5 {495 set ::cfact_cnt496} {1}497do_test collate3-5.6 {498 catchsql {499 SELECT a FROM collate3t1 ORDER BY 1 COLLATE unk;500 }501} {0 10}502do_test collate3-5.7 {503 execsql {504 DROP TABLE collate3t1;505 CREATE TABLE collate3t1(a COLLATE unk);506 }507 db close508 sqlite3 db test.db509 catchsql {510 SELECT a FROM collate3t1 ORDER BY 1;511 }512} {1 {no such collation sequence: unk}}513do_test collate3-5.8 {514 set ::cfact_cnt 0515 proc cfact {nm} {516 db collate $nm {string compare}517 incr ::cfact_cnt518 }519 db collation_needed cfact520 catchsql {521 SELECT a FROM collate3t1 ORDER BY 1;522 }523} {0 {}}524 525do_test collate3-5.9 {526 execsql {527 DROP TABLE collate3t1;528 }529} {}530 531finish_test532