# 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.9 2005/01/26 03:58:36 danielk1977 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 { db collate user_defined "string compare" catchsql { SELECT * FROM collate3t1 ORDER BY a COLLATE user_defined; } } {0 {hello {}}} do_test collate3-4.8 { db close lindex [catch { sqlite3 db test.db }] 0 } {0} do_test collate3-4.8 { 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