diff options
Diffstat (limited to 'test/collate3.test')
| -rw-r--r-- | test/collate3.test | 428 |
1 files changed, 428 insertions, 0 deletions
diff --git a/test/collate3.test b/test/collate3.test new file mode 100644 index 0000000..c4dbfbe --- /dev/null +++ b/test/collate3.test @@ -0,0 +1,428 @@ +# 2001 September 15 +# +# The author disclaims copyright to this source code. In place of +# a legal notice, here is a blessing: +# +# May you do good and not evil. +# May you find forgiveness for yourself and forgive others. +# May you share freely, never taking more than you give. +# +#*********************************************************************** +# This file implements regression tests for SQLite library. The +# focus of this script is page cache subsystem. +# +# $Id: collate3.test,v 1.13 2008/08/20 16:35:10 drh Exp $ + +set testdir [file dirname $argv0] +source $testdir/tester.tcl + +# +# Tests are organised as follows: +# +# collate3.1.* - Errors related to unknown collation sequences. +# collate3.2.* - Errors related to undefined collation sequences. +# collate3.3.* - Writing to a table that has an index with an undefined c.s. +# collate3.4.* - Misc errors. +# collate3.5.* - Collation factory. +# + +# +# These tests ensure that when a user executes a statement with an +# unknown collation sequence an error is returned. +# +do_test collate3-1.0 { + execsql { + CREATE TABLE collate3t1(c1); + } +} {} +do_test collate3-1.1 { + catchsql { + SELECT * FROM collate3t1 ORDER BY 1 collate garbage; + } +} {1 {no such collation sequence: garbage}} +do_test collate3-1.2 { + catchsql { + CREATE TABLE collate3t2(c1 collate garbage); + } +} {1 {no such collation sequence: garbage}} +do_test collate3-1.3 { + catchsql { + CREATE INDEX collate3i1 ON collate3t1(c1 COLLATE garbage); + } +} {1 {no such collation sequence: garbage}} + +execsql { + DROP TABLE collate3t1; +} + +# +# Create a table with a default collation sequence, then close +# and re-open the database without re-registering the collation +# sequence. Then make sure the library stops us from using +# the collation sequence in: +# * an explicitly collated ORDER BY +# * an ORDER BY that uses the default collation sequence +# * an expression (=) +# * a CREATE TABLE statement +# * a CREATE INDEX statement that uses a default collation sequence +# * a GROUP BY that uses the default collation sequence +# * a SELECT DISTINCT that uses the default collation sequence +# * Compound SELECTs that uses the default collation sequence +# * An ORDER BY on a compound SELECT with an explicit ORDER BY. +# +do_test collate3-2.0 { + db collate string_compare {string compare} + execsql { + CREATE TABLE collate3t1(c1 COLLATE string_compare, c2); + } + db close + sqlite3 db test.db + expr 0 +} 0 +do_test collate3-2.1 { + catchsql { + SELECT * FROM collate3t1 ORDER BY 1 COLLATE string_compare; + } +} {1 {no such collation sequence: string_compare}} +do_test collate3-2.2 { + catchsql { + SELECT * FROM collate3t1 ORDER BY c1; + } +} {1 {no such collation sequence: string_compare}} +do_test collate3-2.3 { + catchsql { + SELECT * FROM collate3t1 WHERE c1 = 'xxx'; + } +} {1 {no such collation sequence: string_compare}} +do_test collate3-2.4 { + catchsql { + CREATE TABLE collate3t2(c1 COLLATE string_compare); + } +} {1 {no such collation sequence: string_compare}} +do_test collate3-2.5 { + catchsql { + CREATE INDEX collate3t1_i1 ON collate3t1(c1); + } +} {1 {no such collation sequence: string_compare}} +do_test collate3-2.6 { + catchsql { + SELECT * FROM collate3t1; + } +} {0 {}} +do_test collate3-2.7.1 { + catchsql { + SELECT count(*) FROM collate3t1 GROUP BY c1; + } +} {1 {no such collation sequence: string_compare}} +# do_test collate3-2.7.2 { +# catchsql { +# SELECT * FROM collate3t1 GROUP BY c1; +# } +# } {1 {GROUP BY may only be used on aggregate queries}} +do_test collate3-2.7.2 { + catchsql { + SELECT * FROM collate3t1 GROUP BY c1; + } +} {1 {no such collation sequence: string_compare}} +do_test collate3-2.8 { + catchsql { + SELECT DISTINCT c1 FROM collate3t1; + } +} {1 {no such collation sequence: string_compare}} + +ifcapable compound { + do_test collate3-2.9 { + catchsql { + SELECT c1 FROM collate3t1 UNION SELECT c1 FROM collate3t1; + } + } {1 {no such collation sequence: string_compare}} + do_test collate3-2.10 { + catchsql { + SELECT c1 FROM collate3t1 EXCEPT SELECT c1 FROM collate3t1; + } + } {1 {no such collation sequence: string_compare}} + do_test collate3-2.11 { + catchsql { + SELECT c1 FROM collate3t1 INTERSECT SELECT c1 FROM collate3t1; + } + } {1 {no such collation sequence: string_compare}} + do_test collate3-2.12 { + catchsql { + SELECT c1 FROM collate3t1 UNION ALL SELECT c1 FROM collate3t1; + } + } {0 {}} + do_test collate3-2.13 { + catchsql { + SELECT 10 UNION ALL SELECT 20 ORDER BY 1 COLLATE string_compare; + } + } {1 {no such collation sequence: string_compare}} + do_test collate3-2.14 { + catchsql { + SELECT 10 INTERSECT SELECT 20 ORDER BY 1 COLLATE string_compare; + } + } {1 {no such collation sequence: string_compare}} + do_test collate3-2.15 { + catchsql { + SELECT 10 EXCEPT SELECT 20 ORDER BY 1 COLLATE string_compare; + } + } {1 {no such collation sequence: string_compare}} + do_test collate3-2.16 { + catchsql { + SELECT 10 UNION SELECT 20 ORDER BY 1 COLLATE string_compare; + } + } {1 {no such collation sequence: string_compare}} + do_test collate3-2.17 { + catchsql { + SELECT c1 FROM collate3t1 UNION ALL SELECT c1 FROM collate3t1 ORDER BY 1; + } + } {1 {no such collation sequence: string_compare}} +} ;# ifcapable compound + +# +# Create an index that uses a collation sequence then close and +# re-open the database without re-registering the collation +# sequence. Then check that for the table with the index +# * An INSERT fails, +# * An UPDATE on the column with the index fails, +# * An UPDATE on a different column succeeds. +# * A DELETE with a WHERE clause fails +# * A DELETE without a WHERE clause succeeds +# +# Also, ensure that the restrictions tested by collate3-2.* still +# apply after the index has been created. +# +do_test collate3-3.0 { + db collate string_compare {string compare} + execsql { + CREATE INDEX collate3t1_i1 ON collate3t1(c1); + INSERT INTO collate3t1 VALUES('xxx', 'yyy'); + } + db close + sqlite3 db test.db + expr 0 +} 0 +db eval {select * from collate3t1} +do_test collate3-3.1 { + catchsql { + INSERT INTO collate3t1 VALUES('xxx', 0); + } +} {1 {no such collation sequence: string_compare}} +do_test collate3-3.2 { + catchsql { + UPDATE collate3t1 SET c1 = 'xxx'; + } +} {1 {no such collation sequence: string_compare}} +do_test collate3-3.3 { + catchsql { + UPDATE collate3t1 SET c2 = 'xxx'; + } +} {0 {}} +do_test collate3-3.4 { + catchsql { + DELETE FROM collate3t1 WHERE 1; + } +} {1 {no such collation sequence: string_compare}} +do_test collate3-3.5 { + catchsql { + SELECT * FROM collate3t1; + } +} {0 {xxx xxx}} +do_test collate3-3.6 { + catchsql { + DELETE FROM collate3t1; + } +} {0 {}} +ifcapable {integrityck} { + do_test collate3-3.8 { + catchsql { + PRAGMA integrity_check + } + } {1 {no such collation sequence: string_compare}} +} +do_test collate3-3.9 { + catchsql { + SELECT * FROM collate3t1; + } +} {0 {}} +do_test collate3-3.10 { + catchsql { + SELECT * FROM collate3t1 ORDER BY 1 COLLATE string_compare; + } +} {1 {no such collation sequence: string_compare}} +do_test collate3-3.11 { + catchsql { + SELECT * FROM collate3t1 ORDER BY c1; + } +} {1 {no such collation sequence: string_compare}} +do_test collate3-3.12 { + catchsql { + SELECT * FROM collate3t1 WHERE c1 = 'xxx'; + } +} {1 {no such collation sequence: string_compare}} +do_test collate3-3.13 { + catchsql { + CREATE TABLE collate3t2(c1 COLLATE string_compare); + } +} {1 {no such collation sequence: string_compare}} +do_test collate3-3.14 { + catchsql { + CREATE INDEX collate3t1_i2 ON collate3t1(c1); + } +} {1 {no such collation sequence: string_compare}} +do_test collate3-3.15 { + execsql { + DROP TABLE collate3t1; + } +} {} + +# Check we can create an index that uses an explicit collation +# sequence and then close and re-open the database. +do_test collate3-4.6 { + db collate user_defined "string compare" + execsql { + CREATE TABLE collate3t1(a, b); + INSERT INTO collate3t1 VALUES('hello', NULL); + CREATE INDEX collate3i1 ON collate3t1(a COLLATE user_defined); + } +} {} +do_test collate3-4.7 { + db close + sqlite3 db test.db + catchsql { + SELECT * FROM collate3t1 ORDER BY a COLLATE user_defined; + } +} {1 {no such collation sequence: user_defined}} +do_test collate3-4.8.1 { + db collate user_defined "string compare" + catchsql { + SELECT * FROM collate3t1 ORDER BY a COLLATE user_defined; + } +} {0 {hello {}}} +do_test collate3-4.8.2 { + db close + lindex [catch { + sqlite3 db test.db + }] 0 +} {0} +do_test collate3-4.8.3 { + execsql { + DROP TABLE collate3t1; + } +} {} + +# Compare strings as numbers. +proc numeric_compare {lhs rhs} { + if {$rhs > $lhs} { + set res -1 + } else { + set res [expr ($lhs > $rhs)?1:0] + } + return $res +} + +# Check we can create a view that uses an explicit collation +# sequence and then close and re-open the database. +ifcapable view { +do_test collate3-4.9 { + db collate user_defined numeric_compare + execsql { + CREATE TABLE collate3t1(a, b); + INSERT INTO collate3t1 VALUES('2', NULL); + INSERT INTO collate3t1 VALUES('101', NULL); + INSERT INTO collate3t1 VALUES('12', NULL); + CREATE VIEW collate3v1 AS SELECT * FROM collate3t1 + ORDER BY 1 COLLATE user_defined; + SELECT * FROM collate3v1; + } +} {2 {} 12 {} 101 {}} +do_test collate3-4.10 { + db close + sqlite3 db test.db + catchsql { + SELECT * FROM collate3v1; + } +} {1 {no such collation sequence: user_defined}} +do_test collate3-4.11 { + db collate user_defined numeric_compare + catchsql { + SELECT * FROM collate3v1; + } +} {0 {2 {} 12 {} 101 {}}} +do_test collate3-4.12 { + execsql { + DROP TABLE collate3t1; + } +} {} +} ;# ifcapable view + +# +# Test the collation factory. In the code, the "no such collation sequence" +# message is only generated in two places. So these tests just test that +# the collation factory can be called once from each of those points. +# +do_test collate3-5.0 { + catchsql { + CREATE TABLE collate3t1(a); + INSERT INTO collate3t1 VALUES(10); + SELECT a FROM collate3t1 ORDER BY 1 COLLATE unk; + } +} {1 {no such collation sequence: unk}} +do_test collate3-5.1 { + set ::cfact_cnt 0 + proc cfact {nm} { + db collate $nm {string compare} + incr ::cfact_cnt + } + db collation_needed cfact +} {} +do_test collate3-5.2 { + catchsql { + SELECT a FROM collate3t1 ORDER BY 1 COLLATE unk; + } +} {0 10} +do_test collate3-5.3 { + set ::cfact_cnt +} {1} +do_test collate3-5.4 { + catchsql { + SELECT a FROM collate3t1 ORDER BY 1 COLLATE unk; + } +} {0 10} +do_test collate3-5.5 { + set ::cfact_cnt +} {1} +do_test collate3-5.6 { + catchsql { + SELECT a FROM collate3t1 ORDER BY 1 COLLATE unk; + } +} {0 10} +do_test collate3-5.7 { + execsql { + DROP TABLE collate3t1; + CREATE TABLE collate3t1(a COLLATE unk); + } + db close + sqlite3 db test.db + catchsql { + SELECT a FROM collate3t1 ORDER BY 1; + } +} {1 {no such collation sequence: unk}} +do_test collate3-5.8 { + set ::cfact_cnt 0 + proc cfact {nm} { + db collate $nm {string compare} + incr ::cfact_cnt + } + db collation_needed cfact + catchsql { + SELECT a FROM collate3t1 ORDER BY 1; + } +} {0 {}} + +do_test collate3-5.9 { + execsql { + DROP TABLE collate3t1; + } +} {} + +finish_test |