/trunk/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 |